Classification of SQL statements
- DDL (Data Definition Languages) statement: Data Definition Language.These statements define different data segments,
Definition of database objects such as databases, tables, columns, indexes, etc.Common statement keywords include create, drop, alter
Wait. - DML (Data Manipulation Language) statements: Data manipulation statements for adding, deleting, updating, and checking
Query database records and check data integrity. Common statement keywords include insert, delete, udpate, and
select, etc. - DCL (Data Control Language) statement: A data control statement used to control the direct permission of different data segments and
Access level statement.These statements define the database, tables, fields, user access rights, and security levels.Major
Statement keywords include grant, revoke, and so on.
DDL statement
DDL is an abbreviation of Data Definition Language. Simply put, it means creating, deleting, modifying objects inside a database
Operational language.The biggest difference between DML and DML languages is that DML only operates on the internal data of tables and does not involve table determination.
The modification of meaning and structure will not involve other objects.DDL statements are used more by database administrators (DBA s),
Common developers rarely use it.
Here are some examples of how to use common DDL statements in MySQL.
-
Create a database
After starting the MySQL service, type the following command to connect to the MySQL server:
[root@mysql ~]# mysql -uroot -p -S /application/mysql5.6/data/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.37 Source distribution Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
In the above command line, mysql represents the client command, -u is followed by the connected database user, -p is required to enter the secret
Code.
If the database is set up properly and you enter the correct password, you will see the above welcome screen and a MySQL > prompt.
The following sections are described in the welcome screen.- End of command, with; or \g
- The connection ID of the client, which records the number of connections MySQL service has made so far, with each new connection
Add 1 automatically, in this case 7344941. - Version of MySQL server, in this case "5.6.37", which means version 5.6
- Display the help content through the'help;'or'\h' command, and clear the command line buffer through the'\c'command.
Enter the SQL statement to be executed after the MySQL > prompt, and each SQL statement ends with a semicolon or \g, press back
Key execution.
Since all the data is stored in the database, the first command you need to learn is to create the database with the following syntax
Indicates:CREATE DATABASE dbname
For example, create the database test1, and the command executes as follows:
mysql> create database test1; Query OK, 1 row affected (0.27 sec)
You can see that after executing the create command, there is a line prompting "Query OK, 1 row affected (0.00 sec)", this section
Tips can be divided into three parts, "Query OK" indicates that the above command was executed successfully, and the reader may be surprised, but not executed
Query operation, why display query success?In fact, this is a feature of MySQL, all DDL and DML (not packaged)
Include SELECT) Query OK is displayed after successful execution of the operation, which is understood as successful execution; "1 row
affected indicates that the operation affects only one row of records in the database, while 0.00 sec records when the operation was executed.
If the database already exists, you will be prompted:mysql> create database test1; ERROR 1007 (HY000): Can't create database 'test1'; database exists
At this point, if you need to know which databases exist in your system, you can view them with the following commands:
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | plf | | test | | test1 | +--------------------+ 6 rows in set (0.00 sec)
You can see that in the list above, in addition to the test1 you just created, there are four other databases, all of which are installed
MySQL is created automatically by the system with the following functions.- information_schema: Stores mainly some database object information in the system.For example, user table information, column letters
Information, permission information, character set information, partition information, etc. - performance_schema: Used primarily to collect database server performance parameters
- mysql: Stores user rights information for the system.
- Test: A test database that is automatically created by the system and can be used by any user.
After viewing the existing databases in the system, you can select the database you want to operate on with the following commands:
USE dbname
For example, select database test1:
mysql> use test1 Database changed
Then use the following command to view all the data tables created in the test1 database:
mysql> show tables; Empty set (0.00 sec)
Since test1 is a newly created database and has no tables, it appears empty."Empty set" below the command line indicates
The result set of the operation is empty.If you look at the tables in the mysql database, you can get the following information:mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | employee | | event | | func | | general_log | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 29 rows in set (0.00 sec)
-
Delete database
The syntax for deleting a database is simple, as follows:
drop database dbname;
For example, to delete a test1 database, you can use the following statement:
mysql> drop database test1; Query OK, 0 rows affected (0.00 sec)
You can find that after the prompt operation succeeds, a "0 rows affected" is displayed, which can be ignored.
In MySQL, the result of the drop statement operation is "0 rows affected".PS: All table data below will be deleted after the database is deleted, so check carefully and make a corresponding backup before deleting
-
Create Table
The basic syntax for creating a table in a database is as follows:
CREATE TABLE tablename (column_name_1 column_type_1 constraints， column_name_2 column_type_2 constraints ， ......column_name_n column_type_n constraints)
Because MySQL's table name exists on disk as a directory, the characters of the table name can be allowed with any directory name
Character of.Colum_name is the name of the column, column_type is the data type of the column, and contraints is the approximation of this column
Beam conditions are described in more detail in later chapters.
For example, create a table named emp.The table contains three fields, ename, hiredate, and
sal (salary), field types varchar (10), date, int (2) (about field types will be in the next chapter)
Introduction:mysql> create table emp( ename varchar(10), hiredate date, sal decimal(10,2), deptno int(2)); Query OK, 0 rows affected (0.02 sec)
Once the table has been created, if you need to look at its definition, you can use the following commands:
DESC tablename
For example, looking at the emp table, the following information will be output:
mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
Although the desc command can view table definitions, the information it outputs is not comprehensive enough to see a more comprehensive table definition
Meaning information, sometimes obtained by looking at the SQL statement that created the table, can be achieved using the following commands:mysql> show create table emp\G *************************** 1. row *************************** Table: emp Create Table: CREATE TABLE `emp` ( `ename` varchar(20) DEFAULT NULL, `hiredate` date DEFAULT NULL, `sal` decimal(10,2) DEFAULT NULL, `deptno` int(2) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)From the create SQL statement for the table above, you can see the engine of the table in addition to the table definition.
And charset.The'\G'option means that records can be arranged vertically by field, for
Records with longer capacities are easier to display. -
Delete Table
The table deletion commands are as follows:
DROP TABLE tablename
For example, to delete a database emp, you can use the following commands:
mysql> drop table emp; Query OK, 0 rows affected (0.00 sec)
-
Modify Table
For tables that have been created, especially those that already have a lot of data, if you need to make some structural changes to the table, I
They can drop the table before rebuilding it according to the new table definition.It's OK, but it must be
Do some extra work, such as reloading the data.Also, if a service is accessing a table, it will affect the service
Sound.
Therefore, in most cases, alter table statements are used to alter the table structure. Here are some common commands.-
Modify the table type with the following syntax:
ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
For example, modify the ename field definition of table emp to change varchar(10) to varchar(20):
mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(10) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> alter table emp modify ename varchar(20); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
-
Add table fields with the following syntax:
ALTER TABLE tablename ADD [COLUMN] column_definition [FIRST | AFTER col_name]
For example, a new field age is added to the table emp, of type int(3):
mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> alter table emp add column age int(3); Query OK, 0 rows affected (0.27 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | | age | int(3) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
-
Delete table fields with the following syntax:
ALTER TABLE tablename DROP [COLUMN] col_name
For example, delete the field age:
mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | | age | int(3) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> alter table emp drop age; Query OK, 0 rows affected (0.26 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
-
The field is renamed with the following syntax:
ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTER col_name]
For example, rename deptno to hehe and modify the field type to int(4):
mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> alter table emp change deptno hehe int(4); Query OK, 0 rows affected (0.26 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | hehe | int(4) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
PS: Both change and modify can modify the definition of a table, except that it is inconvenient to write column names twice after change.The advantage of change, however, is that you can modify column names, but modify cannot.
-
-
Modify field ordering
In the field addition and modification syntax (ADD/CNAHGE/MODIFY) described earlier, there is an optional first|after
column_name, which can be used to modify the position of the field in the table. The new field added by default ADD is added in
The last position of the table, and CHANGE/MODIFY does not change the position of the field by default.
For example, add the new field birth date after ename:mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | hehe | int(4) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) # Specify after adding to ename mysql> alter table emp add birth date after ename; Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | NULL | | | birth | date | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | hehe | int(4) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) # Add Default to Last mysql> alter table emp add hello int(100); Query OK, 0 rows affected (0.26 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | NULL | | | birth | date | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | hehe | int(4) | YES | | NULL | | | hello | int(100) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)
Modify the field hehe and put it first
mysql> alter table emp modify hehe int(2) first; Query OK, 0 rows affected (0.26 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | hehe | int(2) | YES | | NULL | | | ename | varchar(20) | YES | | NULL | | | birth | date | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | hello | int(100) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)
These keywords CHANGE/FIRST|AFTER COLUMN are extensions of MySQL to standard SQL and may not be applicable to other databases.
-
Change the table name with the following syntax:
ALTER TABLE tablename RENAME [TO] new_tablename
For example, rename the table emp to emp1 with the following commands:
mysql> alter table emp rename emp1; Query OK, 0 rows affected (0.00 sec) mysql> show tables -> ; +---------------+ | Tables_in_plf | +---------------+ | emp1 | | hk_info | | log_info | | user_info | +---------------+ 4 rows in set (0.00 sec) mysql> desc emp; ERROR 1146 (42S02): Table 'plf.emp' doesn't exist mysql> desc emp1; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | hehe | int(2) | YES | | NULL | | | ename | varchar(20) | YES | | NULL | | | birth | date | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | hello | int(100) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)