Use of MySQL DDL statements

Preface

The language classification of SQL mainly includes the following types:

DDL	Data Definition Language	create, drop, alter	Data Definition Language create, drop, alter statements.
DML	Data Manipulation Language	insert, delete, update	Define the operations of adding, deleting, and modifying database records.
DQL	Database query language	select	Defines query operations on database records.
DCL	Database Control Language	grant, remove	Define access rights and security levels for databases, tables, fields, users. (grant, revoke, etc.).
TCL	Transaction Control Language	set autocommit=0, start transaction, savepoint, commit, rollback	Defines transaction operations on the database.

This section mainly introduces the data definition language DDL (Data Define Language). We use it to perform some management operations (create, delete, modify, etc.) on databases and tables, such as: creating a database, deleting a database, creating a table, modifying a table, deleting a table, adding, deleting, and modifying fields, etc., and managing the database and table structure.

Next we will explain them one by one (the contents in [] below are optional).

Database Management

Create a database

create database [if not exists] dbname;

Deleting a Database

drop databases [if exists] dbname;

The complete writing is as follows:

drop databases [if exists] o_dbname;
create database n_dbname;

o_dbname represents the old database name, and n_dbname represents the new database name.

Test it out:

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| buyerparty |
| buyerparty1 |
|git_jeeshop|
| jz |
| kdmy |
| kdmygf |
| localsdk |
| mgrcentercontrol |
|mysql |
| performance_schema |
| stroke_data |
| test |
+--------------------+
13 rows in set

mysql> drop database if exists test1;
Query OK, 0 rows affected

mysql> create database test1;
Query OK, 1 row affected

mysql> create database test1;
1007 - Can't create database 'test1'; database exists

From the above test, we can know that: before deleting, we must first determine whether the database exists, otherwise an exception will be reported; at the same time, before creating, we must also determine whether it exists, if it exists, it will prompt that it already exists.

Table Management

Create Table

The basic syntax format of a table in the database is as follows:

 create table tbname(
 column_name_1 column_type_1[(n)] [constraints] [comment 'comment1'], 
 column_name_2 column_type_2[(n)] [constraints] [comment 'comment2'],
 column_name_3 column_type_3[(n)] [constraints] [comment 'comment3']
 )[table_options];

Syntax

1. column_name refers to the field name; column_type refers to the field type (CHAR, INT, etc.); n represents the field width, which is optional; constraints are constraints, which are optional; comment is the field comment, which can describe the field in detail.

2. In the same table, column_name cannot be the same

3. Field name and type are required, and the others are optional parameters

4. The type limits the storage format of the field. It must be stored in a given data type and additional constraints can be added.

Constraint Description

not null: non-null constraint

mysql> use test;
Database changed

mysql> create table if not exists `user1`(age int comment 'age',name char(5) comment 'name' not null);
Query OK, 0 rows affected

mysql> insert into user1 values(8,null);
1048 - Column 'name' cannot be null

When creating the table, a non-null constraint is set for the name field. If the value passed in is null, an error message will be displayed. So the purpose of the non-empty constraint is to ensure that the field is not empty.

default value: Provides a default value for the field

mysql> use test;
Database changed

mysql> create table if not exists `user2`(age int not null default 0 comment 'age',name char(50) comment 'name' not null);
Query OK, 0 rows affected

mysql> insert into user2(name) values('brand');
Query OK, 1 row affected
mysql> select * from user2;
+-----+-------+
| age | name |
+-----+-------+
| 0 | brand |
+-----+-------+
1 row in set

After setting the default value, if you do not specify a value when writing data, it will automatically take the default value of 0.

primary key: Identifies the primary key constraint and sets this field as the primary key of the table. It is a globally unique identifier and an error will be reported if a duplicate is inserted.

There are two ways to express it: one is to follow it directly in the field constraint; the other is to add it at the end after all fields are declared, separated from the previous field by a comma.

mysql> use test;
Database changed

mysql> create table if not exists `user3`(id int primary key,age int not null default 0 comment 'age',name char(50) comment 'name' not null);
Query OK, 0 rows affected

mysql> insert into user3 values(1,20,'brand');
Query OK, 1 row affected

mysql> insert into user3 values(1,22,'sol');
1062 - Duplicate entry '1' for key 'PRIMARY'

mysql> insert into user3 values(2,22,'sol');
Query OK, 1 row affected

mysql> select * from user3;
+----+-----+-------+
| id | age | name |
+----+-----+-------+
| 1 | 20 | brand |
| 2 | 22 | sol |
+----+-----+-------+
2 rows in set

As mentioned above, the primary key must maintain the uniqueness of the value. If a duplicate value is inserted, it will prompt that the primary key constraint is violated.

Another way is to support multiple primary keys at the end of the field declaration, separated by commas and not repeatable, in the format of primary key (field 1, field 2, field n). This is called a composite primary key (or compound primary key). For example:

create table if not exists `user4`(id int,age int not null default 0 comment 'age',name char(50) comment 'name' not null,primary key(id,name));

foreign key: identifies a foreign key constraint. Syntax: foreign key(t1_columnname) references t2(columnname), t1 is the current table, t2 is the foreign key table, and the current table and the foreign key table have a field constrained to be a foreign key.

mysql> create table if not exists `class`(classid int primary key,classname varchar(50));
Query OK, 0 rows affected

mysql> create table if not exists `user4`(id int primary key,age int comment 'age',name char(50) comment 'name',cid int not null,foreign key(cid) references class(classid));
Query OK, 0 rows affected

mysql> insert into `user4` values(1,20,'brand',1);
1452 - Cannot add or update a child row: a foreign key constraint fails (`test`.`user4`, CONSTRAINT `user4_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `class` (`classid`))

mysql> insert into `class` values(1,'grad 3');
Query OK, 1 row affected

mysql> insert into `user4` values(1,20,'brand',1);
Query OK, 1 row affected

mysql> select a.age as 'age',a.name as 'student name',b.classname as 'class' from user4 a left join class b on a.cid = b.classid;
+------+----------+--------+
| Age| Student Name| Class|
+------+----------+--------+
| 20 | brand | grad 3 |
+------+----------+--------+
1 row in set

A few notes:

1. When inserting into the user4 table, it will check whether the value of the associated foreign key classid exists. If it does not exist, an error will be reported. As shown in the third paragraph of the above code, the value of classid=1 does not exist in the class table.

2. The corresponding fields of the two tables that establish a foreign key relationship must have the same type.

3. The field set as a foreign key cannot be the primary key of this table, but the field of the associated table needs to be the primary key. (So the foreign key cid is associated to the classid field of the class table as the primary key).

unique key: The unique value constraint can be set to one or more fields. Duplicate values are not allowed. Duplicate values will violate the unique constraint and cause insertion failure.

There are also two ways to define it. One is to set it directly after the field, and the other is to set it after all fields are defined. The following example:

mysql> create table `user5` (id int primary key,name varchar(50),ident char(18) unique key);
Query OK, 0 rows affected

mysql> create table `user6` (id int primary key,name varchar(50),ident char(18) not null,sex int not null,unique key(ident,sex));
Query OK, 0 rows affected

mysql> insert into `user5` values(1,'brand','012345678901234567');
Query OK, 1 row affected
mysql> insert into `user5` values(2,'sol','012345678901234567');
1062 - Duplicate entry '012345678901234567' for key 'ident'

The second paragraph demonstrates support for multiple fields, separated by commas, with the syntax format: unique key (field 1, field 2, field n);

In the third paragraph, the value of ident is entered repeatedly, and he is prompted to enter it repeatedly.

auto_inc: identifies automatic growth

mysql> create table `user7` (id int auto_increment primary key,name varchar(50));
Query OK, 0 rows affected

mysql> insert into `user7`(name) values ('brand'),('sol'),('helen');
Query OK, 3 rows affected

Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from `user7`;
+----+-------+
| id | name |
+----+-------+
| 1 | brand |
| 2 | sol |
| 3 | helen |
+----+-------+
3 rows in set

auto_increment Description:

1. The auto_increacement field is automatically increased, the default value starts from 1 and increases by 1 each time.

2. 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 step size increases by 10 each time.

3. The current value of the auto-increment column is stored in the memory. After the database is restarted, the current value of the auto-increment column max in the current table will be queried.

4. If the table data is cleared and the database is restarted, the auto-increment column will start from the initial value.

Delete a table

drop table [if exists] tname;

Modify table name and comments

 alter table o_tname rename [to] n_tname;
 alter table tname comment 'memo';

Copy Table

Copy only the schema

create table tname like from_tname;

mysql> select * from `user7`;
+----+-------+
| id | name |
+----+-------+
| 1 | brand |
| 2 | sol |
| 3 | helen |
+----+-------+
3 rows in set

mysql> create table `user8` like `user7`;
Query OK, 0 rows affected

mysql> select * from `user8`;
Empty set

Copy schema + data

create table tname [as] select column1,column2,... from from_tname [where condition];

mysql> select * from `user7`;
+----+-------+
| id | name |
+----+-------+
| 1 | brand |
| 2 | sol |
| 3 | helen |
+----+-------+
3 rows in set

mysql> create table `user9` select id,name from `user7`;
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from `user9`;
+----+-------+
| id | name |
+----+-------+
| 1 | brand |
| 2 | sol |
| 3 | helen |
+----+-------+
3 rows in set

Both the data and the architecture are copied over, which is super practical.

Manage fields

Add fields

alter table tname add column column_name column_type [constraints];

mysql> select * from `user9`;
+----+-------+
| id | name |
+----+-------+
| 1 | brand |
| 2 | sol |
| 3 | helen |
+----+-------+
3 rows in set

mysql> alter table `user9` add column newcolumn int not null default 0;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from `user9`;
+----+-------+-----------+
| id | name | newcolumn |
+----+-------+-----------+
| 1 | brand | 0 |
| 2 | sol | 0 |
| 3 | helen | 0 |
+----+-------+-----------+
3 rows in set

Modify Fields

alter table tname modify column col_name new_col_type [constraints]; -- Modify the type and constraints, but not the field name alter table tname change column col_name new_col_name new_col_type [constraints]; -- Modify the field name, type, and constraints

The following are examples of modify and change respectively:

mysql> desc `user9`;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | 0 | |
| name | varchar(50) | YES | | NULL | |
| newcolumn | int(11) | NO | | 0 | |
+-----------+-------------+------+-----+---------+-------+
3 rows in set

mysql> alter table `user9` modify column name varchar(100);
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> desc `user9`;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | 0 | |
| name | varchar(100) | YES | | NULL | |
| newcolumn | int(11) | NO | | 0 | |
+-----------+--------------+------+-----+---------+-------+
3 rows in set

mysql> desc `user9`;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | 0 | |
| name | varchar(100) | YES | | NULL | |
| newcolumn | int(11) | NO | | 0 | |
+-----------+--------------+------+-----+---------+-------+
3 rows in set

mysql> alter table `user9` change column name name1 varchar(100);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc `user9`;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | 0 | |
| name1 | varchar(100) | YES | | NULL | |
| newcolumn | int(11) | NO | | 0 | |
+-----------+--------------+------+-----+---------+-------+
3 rows in set

Delete a field

alter table tname drop column col_name;

mysql> desc `user9`;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | 0 | |
| name1 | varchar(100) | YES | | NULL | |
| newcolumn | int(11) | NO | | 0 | |
+-----------+--------------+------+-----+---------+-------+
3 rows in set

mysql> alter table `user9` drop column newcolumn;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc `user9`;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | 0 | |
| name1 | varchar(100) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
2 rows in set

The above is the detailed content of the use of MySQL DDL statements. For more information about MySQL DDL statements, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:

Summary of common Mysql DDL operations
Detailed explanation of MySQL 8.0 atomic DDL syntax
Detailed explanation of the four SQL languages: DDL DML DCL TCL
Basic statements of MySQL data definition language DDL
MySQL tutorial data definition language DDL example detailed explanation

<<: The advantages and disadvantages of nginx and lvs and their suitable usage environment

>>: JavaScript to achieve uniform animation effect

Issues installing Python3 and Pip in ubuntu in Docker

Use of MySQL DDL statements

Issues installing Python3 and Pip in ubuntu in Docker

Detailed explanation of how to use Nginx + consul + upsync to achieve dynamic load balancing

mysql-5.7.21-winx64 free installation version installation--Windows tutorial detailed explanation

React tips teach you how to get rid of hooks dependency troubles

Implementing search box function with search icon based on html css

Detailed analysis of MySQL optimization of like and = performance

How to define data examples in Vue

Various correct postures for using environment variables in Webpack

MySQL date functions and date conversion and formatting functions

ftp remotely connect to Linux via SSH

Recommend

JS operation object array to achieve add, delete, modify and query example code

Analysis and explanation of the differences between DIV, Table and XHTML website building

Docker nginx example method to deploy multiple projects

XHTML Getting Started Tutorial: What is XHTML?

Analysis of MySQL's planned tasks and event scheduling examples

Three steps to solve the IE address bar ICON display problem

A screenshot demo based on canvas in html

Detailed explanation of Docker common commands Study03

How to add a column to a large MySQL table

mysql5.7.20 installation and configuration method graphic tutorial (mac)

Installation tutorial of mysql 8.0.11 compressed version under win10

MySQL tutorial DML data manipulation language example detailed explanation

Steps to set up and mount shared folders on Windows host and Docker container

Implement 24+ array methods in JavaScript by hand