Play with Mysql series - day 4: summary of common DDL operations

Posted by staggman on Tue, 25 Jan 2022 21:36:24 +0100

If you are interested in mysql, add my wechat itsoku to pull you into the group communication technology.

This is the fourth in the Mysql series.

Environment: mysql5 7.25, cmd command.

DDL: data definition language, which is mainly used to manage databases and tables.

For example, create database, delete database, create table, modify table, delete table, add, delete and modify columns, etc.

The syntax involved in this article is optional, which is described in detail below.

Library management

Create Library

create database [if not exists] Library name;

Delete Library

drop databases [if exists] Library name;

Common writing method for building database

drop database if exists Old library name;
create database New library 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)] [constraint condition] [comment 'Field description'],
    Field name 2 type[(width)] [constraint condition] [comment 'Field description'],
    Field name 3 type[(width)] [constraint condition] [comment 'Field description']
)[Some settings of table];

be careful:

  1. Field names cannot be the same in the same table
  2. Width and constraints are optional parameters, and field name and type are required
  3. The last field cannot be followed by a comma
  4. Type is used to limit the data type in which a field must store records
  5. Type is actually a constraint on the field (the record under the constraint field must be of type XX)
  6. Constraints written after a type are additional constraints added outside the type

Constraint description

not null: the identification 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 the 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)

If the value of b is not set during the above insertion, the default value is 0 automatically

Primary key: indicates that this field is the primary key of the table. It can uniquely identify records. Inserting duplicate records will result in an error

It can be written in two ways, 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 after all column definitions, 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 report illegal primary key constraints

Method 2 supports multiple fields as primary keys separated by commas. Syntax: 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 foreign keys for fields in the table

Syntax: foreign key (column name of the current table) references the 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 ts5 in test6_ The value of field a comes from field a in table test5.

Notes:

  • The field types in the two tables that need to establish foreign key relationships need to be consistent
  • The field to set the foreign key cannot be a primary key
  • The referenced field needs to be a primary key
  • The inserted value must exist in the foreign key table, such as inserting ts5 into test6 above_ An error is reported when a is 2. Reason: the value of 2 does not exist in the test5 table

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

One or more fields are supported. Inserting duplicate values will report violation of unique constraints and insertion failure.

There are two ways to define.

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. Syntax: 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 will grow automatically (integer type and 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 increased. The default value starts from 1 and is + 1 each time

The initial value and step size of the automatic growth field can be set in mysql. For example, set the initial value to 10000 and increase by 10 each time

be careful:

The current value of the self increasing column is stored in memory. After each restart of the database, the maximum value of the self increasing column in the current table will be queried as the current value. If the database restarts after the table data is cleared, the value of the self increasing column will start from the initial value

Let's demonstrate:

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)

Delete the test11 data above, and then insert one. The value of a is 3. Perform the following operations:

Delete 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 The service is stopping..
mysql Service stopped successfully.


C:\Windows\system32>net start mysql
mysql The service is starting .
mysql The 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 table

drop table [if exists] Table name;

Modify table name

alter table Table name rename [to] New table name;

Table setting remarks

alter table Table name comment 'Remark information';

Copy table

Copy table structure only

create table Table name like Replicated table name;

For example:

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 Copied table [where condition];

For example:

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.

Management of columns in the table

Add column

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 column

alter table Table name modify column New type of column name [constraint];
perhaps
alter table Table name change column Column name new column name new type [constraint];

Difference between the two methods: modify cannot modify the column name, while 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 will modify the field c name and type as follows:

mysql> alter table test14 change column c d 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)

Delete column

alter table Table name drop column Listing;

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)

Hot Topics

©2025 Programmer Think