Shulou(Shulou.com)05/31 Report--

Editor to share with you what are the DDL data definition statements in MySQL, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

I. DDL data definition statement

Management of the library

Create: create database [if not exists] library name

Modify: if you need to change the library name, modify the folder directly

Delete: DROP DATABASE IF EXISTS library name

Management of tables

Create

Create table IF NOT EXISTS table name (

Type of column name column [length, constraint]

Type of column name column [length, constraint]

Type of column name column [length, constraint]

...

)

Modify

Alter table table name add | drop | modify | change column column name [column type constraint]

Delete

Drop table table name

[library management] # create library CREATE DATABASE IF NOT EXISTS books;# modify the character set of the library ALTER DATABASE books CHARACTER SET gbk;# delete library DROP DATABASE IF EXISTS books; [table management] # 1. Create tables CREATE TABLE IF NOT EXISTS book (id INT, bName VARCHAR (20), authorID INT, publishDate DATETIME); DESC book;CREATE TABLE author (id INT, au_name VARCHAR (20), nation VARCHAR (10)); DESC author;# 2. Modify table # modify column name ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME;# modify column type or constraint ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;# add new column ALTER TABLE author ADD COLUMN annual DOUBLE; # add new column as the first field ALTER TABLE author ADD COLUMN newT1 INT FIRST;# add new column after the specified column ALTER TABLE author ADD COLUMN newT2 INT AFTER newT1;# delete column ALTER TABLE author DROP COLUMN annual;# modify table name ALTER TABLE author RENAME TO book_author;# 3. Table deletion DROP TABLE IF EXISTS book_author;SHOW TABLES;# 4. Table replication INSERT INTO author VALUES (1Jing 'Sakura', 'Japan'), (2 'Wukong', 'China'), (3 'Hulk', 'USA'), (4 'Nezhu', 'China'); # structure of replicated table only CREATE TABLE author2 LIKE author;# replication table structure + data CREATE TABLE author3 SELECT * FROM author;# only replicates part of data CREATE TABLE author4 SELECT id,au_name FROM author WHERE nation=' China' # copy only certain fields CREATE TABLE author5 SELECT id,au_name FROM author WHERE 1: 2

2. Data type

2.1 Integer

Integer type alias byte unsigned range Tinint Mini Integer 10255-128~127Smallin Mini Integer 2065535-8388608~8388607Int or Integer Integer 404294967295-2147483648~2147483647Bigint large Integer 809223372036854775807801-92233720368547758089223372036854775807 [Integer] # 1. Default is signed, and unsigned can be set to unsigned CREATE TABLE IF NOT EXISTS tab_int (T1 INT, # signed T2 INT UNSIGNED, # unsigned T3 INT ZEROFILL # automatically changed to unsigned integer after adding zerofill, the number of digits is not 0 enough to fill.); DESC tab_int;SELECT * FROM tab_int;# 2. If the inserted value is outside the range of the integer, it will report the out of range exception INSERT INTO tab_int VALUES (2147483648pl); # 3. If you do not set the length, there will be a default length. If the number of digits is not enough, it will be filled with 0 (provided the field has ZEROFILL).

2.2 Decimal

Floating point type byte range 4-2 ^ 128double (float) 8-2 ^ 1024 ~ + 2 ^ 1024 fixed point type\\ DEC (MMagneD) maximum range of double is the same as that of double. The valid range of a given decimal is determined by M and D.

Note:

M: the total length of integer part + decimal part

D: decimal part

When both D and M are omitted:

1. If it is a decimal type, M defaults to 10 and D defaults to 0

2. In the case of floact and double, the accuracy will be determined according to the accuracy of the inserted value.

3. The accuracy of the fixed point type is higher, if the accuracy of the insertion value is required to be higher, such as monetary operation, then consider to use.

2.3 character type

Whether the string type M can be omitted the characteristic space consumption efficiency range char (M) M can be omitted, the default is 1 fixed length comparison, the integer varchar (M) M with high cost M between 0,255 and 255 cannot be omitted, the integer with variable length can be omitted and the integer between 0,65535 with low M can be saved.

Binary and varbinary types, similar to char and varchar, except that they contain binary characters rather than non-binary characters, that is, shorter binaries.

Bit (M) type, byte 1x8, range from Bit (1) to Bit (8).

The Enum type, that is, the enumerated type, requires that the inserted value must belong to one of the values specified in the list, and 1 byte storage is required if the column member is 1-byte 255; if the column member is 255-65535, 2 bytes storage is required, with a maximum of 65535 members.

The Set type, similar to Enum, can hold 64 members. The biggest difference between Set and Enum is that Set can select more than one member at a time, while Enum can only choose one, and the storage takes up different bytes according to the number of members.

The number of member sections 1 "819" 16217 "24325" 32433 "648 [enumerated] CREATE TABLE tab_set (S1 SET ('a')); INSERT INTO tab_set VALUES ('c'); INSERT INTO tab_set VALUES ('c'); INSERT INTO tab_set VALUES ('a'); # after insertion, it will be sorted internally. For example, inserting c will become a line.

2.4 date Typ

Date and time type characteristics byte minimum date save date only 41000-01-019999-12-31datetime save date + time 81000-01-00 23:59:59timestamp 009999-12-31 23:59:59timestamp (used more) save date + time 41870010800012038 sometime time only save time 3-838:59:59838:59:59year only save year 119012155

Timestamp is related to the actual time zone and better reflects the actual date, while datetime can only reflect the local time zone at the time of insertion.

The properties of timestamp are greatly influenced by the Mysql version and SQLMode.

[date type] CREATE TABLE tab_date (T1 DATETIME, # not affected by time zone # T2 TIMESTAMP # affected by time zone); INSERT INTO tab_date VALUES (NOW (), NOW ()); SET time_zon='+8:00';# sets time zone SHOW VARIABLES LIKE 'time_zone'; # displays the current time zone

Second, six major constraints

Meaning: a restriction used to restrict the data in a table in order to ensure the accuracy and reliability of the data in the table.

NOT NULL is a non-null constraint, which states that a field cannot be an empty UNIQUE unique constraint, and that a field is the only PRIMARY KEY primary key (unique and non-empty) in the table sink. FOREIGN KEY foreign key CHECK check constraint (not supported in mysql) DEFAULT default value, ensuring that the field has a default value

Column-level constraints: all six constraints are syntactically supported, but foreign key constraints have no effect.

Table-level constraints: all are supported except for non-null and default.

It is a common practice that other constraints are written at the column level and foreign key constraints are written at the table level.

Whether the primary key and unique comparison guarantee uniqueness or not allow several combinations (not recommended) primary key √ × at most one primary key allows combination unique √√ (allows one null) can have multiple unique combinations unique

Characteristics of foreign keys:

Requires that the foreign key relationship be set in the slave table.

The type of the foreign key column of the slave table and the associated column of the master table are required to be consistent or compatible, and the name is not required.

The associated column of the primary table must be a key (usually a primary key or unique)

When inserting data, insert the master table first and then the slave table; when deleting the data, delete the slave table first and then delete the master table.

Column-level constraints and table-level constraints compare whether the constraint types supported by column-level constraints can be aliased from the later syntax of column-level constraint columns, but foreign keys have no effect. Table-level constraints are not supported by default and non-null below. Other support can (primary key has no effect) [column-level constraints] directly append constraint types to field names and types. Note: only default, non-null, primary key, unique, foreign key constraint CREATE DATABASE students;USE students;CREATE TABLE major (id INT PRIMARY KEY,# primary key majorName VARCHAR (20)) is supported. CREATE TABLE stuinfo (id INT PRIMARY KEY,# primary key stuName VARCHAR (20) NOT NULL, # non-empty gender CHAR (1) CHECK (gender=' male'OR gender=' female'), # check seat INT UNIQUE,# unique age INT DEFAULT 18, # default constraint majorId INT REFERENCES major (id) # foreign key); DESC stuinfo;SHOW INDEX FROM stuinfo # View all indexes in the table, foreign keys, unique [table-level constraints] # syntax: [CONSTRAINT constraint name] constraint type (field name) DROP TABLE IF EXISTS stuinfo CREATE TABLE stuinfo (id INT, stuname VARCHAR (20), gender CHAR (1), seat INT, age INT, majorid INT, CONSTRAINT competes for PRIMARY KEY (id), # primary key CONSTRAINT uq UNIQUE (seat), # unique CONSTRAINT ck CHECK (gender=' male'OR gender=' female'), # check (no error report) But invalid) CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major (id) # Foreign key) SHOW INDEX FROM stuinfo; [add constraints when modifying tables] # add non-empty constraints ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR (20) NOT NULL;# add default constraints ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18position # add primary key ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;# add unique key ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;# add foreign key ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major (id); DESC stuinfo;SHOW INDEX FROM stuinfo [delete constraint when modifying table] # Delete non-empty constraint ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR (20) NULL;# delete default constraint ALTER TABLE stuinfo MODIFY COLUMN age INT;# delete primary key ALTER TABLE stuinfo DROP PRIMARY KEY;# delete unique ALTER TABLE stuinfo DROP INDEX seat;# delete foreign key ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major

Learned the constraints and tried to complete the test questions of indica rice.

1. Column-level constraints:

ALTER TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY;# column constraints do not support naming

Table-level constraints:

ALTER TABLE emp2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY (id); # actually the primary key has no effect even if it is named

2. Similar to 1

3 、

ALTER TABLE emp2 ADD COLUMN dept_id INT

ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_dept2 FOREIGN KEY (dept_id) REFERENCES dept2 (id)

IV. Identification column

Identity columns, also known as self-growing columns, can also be included in the scope of constraints.

Meaning: you don't have to insert values manually, the system provides default sequence values.

Features:

1. The identity column does not have to be paired with the primary key, but it requires a key.

two。 A table can have at most one representation column.

3. Indicates that the type of column can only be numeric.

4. The identity column can either set the step size through SET auto_increment_increment=3; or set the starting value by manually inserting the value.

[set identity column when creating table] DROP TABLE IF EXISTS tab_identity;CREATE TABLE tab_identity (id INT PRIMARY KEY AUTO_INCREMENT, # set auto-increment NAME VARCHAR (20)); INSERT INTO tab_identity VALUE (NULL,' flower'); INSERT INTO tab_identity (NAME) VALUE ('Hudie'); SELECT * FROM tab_identity;SHOW VARIABLES LIKE'% auto_increment%';SET auto_increment_increment=3 # you can change TRUNCATE TABLE tab_identity; by changing the value of the first record [setting identity column] CREATE TABLE tab_identity when modifying the table (id INT, # setting auto-increment NAME VARCHAR (20)); # setting the primary key and identity column ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT; to delete the identity column [] ALTER TABLE tab_identity MODIFY COLUMN id INT when modifying the table

5. Cascade deletion and emptying

Cascade deletion:

ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY (majorid) REFERENCES major (id) ON DELETE CASCADE

Cascade null:

ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY (majorid) REFERENCES major (id) ON DELETE SET NULL

[demonstrate cascading deletion, cascading null] DROP TABLE major,stuinfoCREATE TABLE IF NOT EXISTS major (id INT PRIMARY KEY, majorName VARCHAR (20)); INSERT INTO majorVALUES (1recordable Java'), (2recoveryPython'), (3recorder Go'); CREATE TABLE IF NOT EXISTS stuinfo (id INT, stuname VARCHAR (20), gender CHAR (1), seat INT, age INT, majorid INT) INSERT INTO stuinfoSELECT 1, NULL,NULL,2 UNION ALLSELECT 1, NULL,NULL,1 UNION ALLSELECT 2, NULL,NULL,1 UNION ALLSELECT 3, NULL,NULL,2 UNION ALLSELECT 4, NULL,NULL,2 UNION ALLSELECT 5, NULL,NULL,1 UNION ALLSELECT 6, NULL,NULL,3 UNION ALLSELECT 7, NULL,NULL,3 UNION ALLSELECT 8, NULL,NULL,3 UNION ALLSELECT 8 NULL,NULL,1) SELECT * FROM major;SELECT * FROM stuinfo;# traditional way to add foreign key ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY (majorid) REFERENCES major (id); # delete major table No. 3 professional # method 1: cascade delete # first delete foreign key ALTER TABLE stuinfo DROP FOREIGN KEY fk_stu_major;# add cascade delete ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY (majorid) REFERENCES major (id) ON DELETE CASCADE;DELETE FROM major WHERE id = 3 # method 2: cascading null # first delete the foreign key ALTER TABLE stuinfo DROP FOREIGN KEY fk_stu_major;# and add the cascade blank ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY (majorid) REFERENCES major (id) ON DELETE SET NULL;DELETE FROM major WHERE id = 2; the above is all the contents of the article "what are the DDL data definition statements in MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.