DDL (data definition language)

DDL is the operating language for creating, deleting, modifying, and other objects within the database. The biggest difference between it and the DML language is that DML is just an operation on the data inside the table, not the definition of the table, the modification of the structure, and no other objects involved.

1. Connect to the database:
Note: 1.mysql represents the Client command,-U is followed by the connected database user,-p indicates the need to enter a password.
2. Terminator of the order; or \g end.

2. Create a database:
Format: Create database dbname;

Query OK executes successfully
1 row affected only affects records in a row in the database.
0.13 SEC records the time the operation was executed

If the database already exists, the system will prompt.

See which databases exist on the system: show databases;
:

INFORMATION_SCHEMA: It mainly stores some database object information in the system. such as user table information, Lie Xin information, permission information, character set information, partition information and so on.
Cluster: The cluster information of the system is stored.
MySQL: Stores user rights information for the system.
Test: The system automatically creates a testing database that can be used by any user.

Select the remote operation database:
Use dbname;

Next, look at all the data tables for the database: show tables;

3. Delete database: (all table data will be deleted)
Format: Drop database dbname;

After the prompt operation is successful, "0 rows affected" is displayed. Feel puzzled, do not care about it, in MySQL, the result of the drop statement operation is "0 rows affected"

4. Create a table
Format: CREATE TABLE tablename (column_name_1 column_type_1 constraints,column_name_2 column_type_2 constraints ...)
Note: column_name is the name of the column, Column_type is the data type of the column, and contraints is the constraint for this column.
For example, create an EMP table.
CREATE TABLE EMP (ename varchar), HireDate date,sal Decimal (10,2), Deptno Int (2));

View the definition of the EMP table, you can use the DESC tablename;

Although the DESC command can view the table definition, its output is incomplete, and in order to see more comprehensive table definition information, it is sometimes necessary to view the SQL statement that created the table, which can be implemented using the following command:

From the Create SQL statement in the table above, you can see the engine (storage engine) of the table in addition to the table definition.
and CharSet (character set) and other information. The "\g" option means that records can be arranged vertically in a field,
Longer records are easier to display.

5. Delete the table:
drop table emp;

6. Modify the table: (In most cases, modify the table structure with the ALTER TABLE statement)

1) 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 in the table emp to change varchar (10) to varchar (20);
: ALTER TABLE EMP modify ENAME varchar (20);

2) Add table field:
ALTER TABLE tablename ADD [COLUMN] column_definition [First | After Col_name]
For example, the table EMP adds a new field of age, type int (3):
: ALTER TABLE EMP add column age int (3);

3) Delete table field:
ALTER TABLE tablename DROP [COLUMN] Col_name
For example, delete the field age:
: ALTER TABLE EMP drop column age;

4) Rename the field:
ALTER TABLE tablename Change [COLUMN] Old_col_name column_definition
[first| After Col_name]
For example, rename age to Age1 and modify the field type to int (4):
: ALTER TABLE EMP change age Age1 int (4);

5) Modify the order in which the fields are arranged:
(in the field additions and modifications syntax described earlier (add/cnahge/modify), there is an option First|after column_name, which can be used to modify the position of the field in the table, and the default add adds new fields to 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 to the ename:
: ALTER TABLE EMP add birth date after ename;

For example: Modify the field age to put it first:
: ALTER TABLE EMP modify age int (3) first;

Note: change/first| After COLUMN these keywords are mysql, and extensions on standard SQL do not necessarily apply on other databases.

6) Table name change:
ALTER TABLE tablename RENAME [to] New_tablename
For example, rename the table emp to EMP1:
: ALTER TABLE EMP rename EMP1;

DDL (data definition language)