Summary of common Mysql DDL operations

Library Management

Create a library

create database [if not exists] library name;

Delete a library

drop databases [if exists] library name;

Common writing method for building a database

drop database if exists old database name;
create database new database name;

Example

mysql> show databases like 'javacode2018';
+-------------------------+
| Database (javacode2018) |
+-------------------------+
|javacode2018|
+-------------------------+
1 row in set (0.00 sec)

mysql> drop database if exists javacode2018;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases like 'javacode2018';
Empty set (0.00 sec)

mysql> create database javacode2018;
Query OK, 1 row affected (0.00 sec)

show databases like 'javacode2018'; lists the javacode2018 library information.

Table Management

Create Table

create table table name(
  Field name 1 type [(width)] [constraints] [comment 'field description'],
  Field name 2 type [(width)] [constraints] [comment 'field description'],
  Field name 3 Type [(width)] [Constraints] [comment 'Field description']
)[some settings of the table];

Notice:

In the same table, field names cannot be the same
Width and constraints are optional parameters, field name and type are required
No comma after the last field
Type is used to restrict the data type of the field to store records.
The type is actually a constraint on the field (the records under the constraint field must be of type XX)
The constraints written after the type are additional constraints added outside the type.

Constraint Description

not null: indicates that the field cannot be empty

mysql> create table test1(a int not null comment 'field a');
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test1 values (null);
ERROR 1048 (23000): Column 'a' cannot be null
mysql> insert into test1 values (1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

**default value:** Set a default value for this field. The default value is value

mysql> drop table IF EXISTS test2;
Query OK, 0 rows affected (0.01 sec)

mysql> create table test2(
  -> a int not null comment 'field a',
  -> b int not null default 0 comment 'field b'
  -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test2(a) values (1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test2;
+---+---+
| a | b |
+---+---+
| 1 | 0 |
+---+---+
1 row in set (0.00 sec)

The value of b is not set when inserting above, and the default value 0 is automatically taken

**primary key:** This field is identified as the primary key of the table. It can uniquely identify records. Inserting duplicate records will result in an error.

There are two ways to write it, as follows:

Method 1: Follow the column, as follows:

mysql> drop table IF EXISTS test3;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table test3(
  -> a int not null comment 'field a' primary key
  -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test3 (a) values (1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test3 (a) values (1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

Method 2: Define it after all columns are defined, as follows:

mysql> drop table IF EXISTS test4;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table test4(
  -> a int not null comment 'field a',
  -> b int not null default 0 comment 'field b',
  -> primary key (a)
  -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test4(a,b) values (1,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test4(a,b) values (1,2);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

Inserting duplicate values will result in a violation of the primary key constraint.

Method 2 supports multiple fields as primary keys. Multiple fields are separated by commas. The syntax is: primary key (field 1, field 2, field n). Example:

mysql> drop table IF EXISTS test7;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> create table test7(
  -> a int not null comment 'field a',
  -> b int not null comment 'field b',
  -> PRIMARY KEY (a,b)
  -> );
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> insert into test7(a,b) VALUES (1,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test7(a,b) VALUES (1,1);
ERROR 1062 (23000): Duplicate entry '1-1' for key 'PRIMARY'

foreign key: Set a foreign key for a field in a table

Syntax: foreign key (column name of the current table) references foreign key table (field name in the foreign key table)

mysql> drop table IF EXISTS test6;
Query OK, 0 rows affected (0.01 sec)

mysql> drop table IF EXISTS test5;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> create table test5(
  -> a int not null comment 'field a' primary key
  -> );
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> create table test6(
  -> b int not null comment 'field b',
  -> ts5_a int not null,
  -> foreign key(ts5_a) references test5(a)
  -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test5 (a) values (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test6 (b,test6.ts5_a) values (1,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test6 (b,test6.ts5_a) values (2,2);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`javacode2018`.`test6`, CONSTRAINT `test6_ibfk_1` FOREIGN KEY (`ts5_a`) REFERENCES `test5` (`a`))

Note: Indicates that the value of the ts5_a field in test6 comes from field a in table test5.

A few points to note:

The field types that need to establish a foreign key relationship in the two tables need to be consistent
The field to be set as a foreign key cannot be the primary key
The referenced field needs to be the primary key
The value to be inserted must exist in the foreign key table. For example, when inserting ts5_a as 2 into test6, an error is reported. The reason is that the value 2 does not exist in the test5 table.

unique key (uq): indicates that the value of this field is unique

Supports one to multiple fields. Inserting duplicate values will violate the unique constraint and the insertion will fail.

There are 2 ways to define it.

Method 1: Follow the field, as follows:

mysql> drop table IF EXISTS test8;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> create table test8(
  -> a int not null comment 'field a' unique key
  -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> insert into test8(a) VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test8(a) VALUES (1);
ERROR 1062 (23000): Duplicate entry '1' for key 'a'

Method 2: Define after all columns are defined, as follows:

mysql> drop table IF EXISTS test9;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> create table test9(
  -> a int not null comment 'field a',
  -> unique key(a)
  -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> insert into test9(a) VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test9(a) VALUES (1);
ERROR 1062 (23000): Duplicate entry '1' for key 'a'

Method 2 supports multiple fields, separated by commas. The syntax is: primary key (field 1, field 2, field n). Example:

mysql> drop table IF EXISTS test10;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> create table test10(
  -> a int not null comment 'field a',
  -> b int not null comment 'field b',
  -> unique key(a,b)
  -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> insert into test10(a,b) VALUES (1,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test10(a,b) VALUES (1,1);
ERROR 1062 (23000): Duplicate entry '1-1' for key 'a'

auto_increment: Indicates that the value of this field increases automatically (integer type and is the primary key)

mysql> drop table IF EXISTS test11;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> create table test11(
  -> a int not null AUTO_INCREMENT PRIMARY KEY comment 'field a',
  -> b int not null comment 'field b'
  -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> insert into test11(b) VALUES (10);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test11(b) VALUES (20);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test11;
+---+----+
| a | b |
+---+----+
| 1 | 10 |
| 2 | 20 |
+---+----+
2 rows in set (0.00 sec)

Field a is automatically incremented, with a default value starting at 1 and increasing by 1 each time.

The initial value and step size of the auto-increment field can be set in MySQL. For example, the initial value is set to 10,000 and the increment is 10.

Notice:
The current value of the auto-increment column is stored in memory. After each restart of the database, the maximum value of the auto-increment column in the current table will be queried as the current value. If the table data is cleared and the database is restarted, the value of the auto-increment column will start from the initial value.

Let's demonstrate this:

mysql> delete from test11;
Query OK, 2 rows affected (0.00 sec)

mysql> insert into test11(b) VALUES (10);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test11;
+---+----+
| a | b |
+---+----+
| 3 | 10 |
+---+----+
1 row in set (0.00 sec)

The test11 data is deleted above, and then a record is inserted. The value of a is 3. Perform the following operations:

Delete the test11 data, restart MySQL, insert data, and then see if the value of a is initialized? as follows:

mysql> delete from test11;
Query OK, 1 row affected (0.00 sec)

mysql> select * from test11;
Empty set (0.00 sec)

mysql> exit
Bye

C:\Windows\system32>net stop mysql
mysql service is stopping..
The mysql service has been stopped successfully.


C:\Windows\system32>net start mysql
The mysql service is starting.
The mysql service has been started successfully.


C:\Windows\system32>mysql -uroot -p
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.25-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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.

mysql> use javacode2018;
Database changed
mysql> select * from test11;
Empty set (0.01 sec)

mysql> insert into test11 (b) value (100);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test11;
+---+-----+
| a | b |
+---+-----+
| 1 | 100 |
+---+-----+
1 row in set (0.00 sec)

Delete a table

drop table [if exists] table name;

Modify table name

alter table table name rename [to] new table name;

Table Setting Notes

alter table table name comment 'Comment information';

Copy Table

create table table name like the table name to be copied;

like:

mysql> create table test12 like test11;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test12;
Empty set (0.00 sec)

mysql> show create table test12;
+--------+-------+
| Table | Create Table                                                                      
+--------+-------+
| test12 | CREATE TABLE `test12` (
 `a` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Field a',
 `b` int(11) NOT NULL COMMENT 'Field b',
 PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-------+
1 row in set (0.00 sec)

Copy table structure + data

create table table name [as] select field,... from table to be copied [where condition];

like:

mysql> create table test13 as select * from test11;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from test13;
+---+-----+
| a | b |
+---+-----+
| 1 | 100 |
+---+-----+
1 row in set (0.00 sec)

The table structure and data are here.

Managing columns in a table

Add columns

alter table table name add column column name type [column constraint];

Example:

mysql> drop table IF EXISTS test14;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> create table test14(
  -> a int not null AUTO_INCREMENT PRIMARY KEY comment 'field a'
  -> );
Query OK, 0 rows affected (0.02 sec)

mysql> alter table test14 add column b int not null default 0 comment 'Field b';
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table test14 add column c int not null default 0 comment 'Field c';
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into test14(b) values (10);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test14; c
+---+----+---+
| a | b | c |
+---+----+---+
| 1 | 10 | 0 |
+---+----+---+
1 row in set (0.00 sec)

Modify columns

alter table table name modify column column name new type [constraint];
Or alter table table name change column column name new column name new type [constraint];

The difference between the two methods: modify cannot modify the column name, change can modify the column name

Let's take a look at the table structure of test14:

mysql> show create table test14;
+--------+--------+
| Table | Create Table |
+--------+--------+
| test14 | CREATE TABLE `test14` (
 `a` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Field a',
 `b` int(11) NOT NULL DEFAULT '0' COMMENT 'Field b',
 `c` int(11) NOT NULL DEFAULT '0' COMMENT 'Field c',
 PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
+--------+--------+
1 row in set (0.00 sec)

We modify the name and type of field c as follows:

mysql> alter table test14 change column cd varchar(10) not null default '' comment 'Field d';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table test14; ;;
+--------+--------+
| Table | Create Table |
+--------+--------+
| test14 | CREATE TABLE `test14` (
 `a` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Field a',
 `b` int(11) NOT NULL DEFAULT '0' COMMENT 'Field b',
 `d` varchar(10) NOT NULL DEFAULT '' COMMENT 'Field d',
 PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
+--------+--------+
1 row in set (0.00 sec)

Deleting a column

alter table table name drop column column name;

Example:

mysql> alter table test14 drop column d;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table test14;
+--------+--------+
| Table | Create Table |
+--------+--------+
| test14 | CREATE TABLE `test14` (
 `a` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Field a',
 `b` int(11) NOT NULL DEFAULT '0' COMMENT 'Field b',
 PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
+--------+--------+
1 row in set (0.00 sec)

This is the end of this article about the summary of common MySQL DDL operations. For more relevant MySQL DDL operations, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in: