MySQL learning summary - DDL (data definition language summary)


All database learning summary articles are summarized according to the shangsilicon Valley video of station B, and the video links are as follows:
[https://www.bilibili.com/video/BV12b411K7Zu]

Data definition language is mainly used for Library and table management.
CREATE keyword: CREATE
Modify keyword: ALTER
Delete keyword: DROP

1, Library management

1. Library creation

Syntax:
CREATE DATABASE [IF NOT EXISTS] Library name;  # Data in [] optional

Case: creating a library books
CREATE DATABASE IF NOT EXISTS books;

2. Library modification

Only the character set of the library can be changed.

Syntax:
CREATE DATABASE CHARACTER SET Required character set;

3. Library deletion

Syntax:
DROP DATABASE  IF EXISTS Library name;

2, Table management

1. Table creation

① Syntax:

#The Department in [] is optional. Adding this part can avoid the problem of error reporting when creating a table.
CREATE TABLES 	[IF NOT EXISTS] Table name (
	Column name the type of the column[(length) Constraints],
	Column name the type of the column[(length) Constraints],
	·····
)

② Case
Case 1: create a book table in the books library.

CREATE TABLE IF NOT EXISTS book(
	id INT,    #number
	bName VARCHAR(20),    #Type and length of book name
	price DOUBLE,      #Price
	author INT,       #Author number
	publishDate DATETIME    #Publication date 
);

2. Table modification

① Syntax:

ALTER TABLE Table name
 Change column name: CHANGE COLUMN Old column name new column type [constraint];
To modify the type or constraint of a column: MODIFY COLUMN Column name column type [constraint];
Add column: ADD COLUMN Column name column type [constraint];
Delete column: DROP COLUMN Listing;
Change table name: RENAME TO New table name;

② Case:

Case 1: change the publishDate column name in the book table to pubDate.

ALTER TABLE book 
CHANGE COLUMN publishDate pubDate INT;

Case 2: change the pubDate column type of the book table to TIMESTAMP.

ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP;

Case 3: add a column with annual type of double in the book table.

ALTER TABLE book ADD COLUMN annual DOUBLE;

Case 4: delete the annual column in the book table.

ALTER TABLE book DROP COLUMN annual;

Case 5: change the name of the book table to mybook

ALTER TABLE book RENAME TO mybook;

3. Table deletion

Syntax:

DROP TABLE IF EXISTS Table name;

4. Replication of tables

① Grammar

Copy table structure only:
CREATE TABLE New table name LIKE Table name to copy;

Copy all the structure and data of the table:
CREATE TABLE New table name SELECT * FROM Table name to copy;

Copy part of the structure and data of the table:
CREATE TABLE New table name 
SELECT Column 1, column 2 FROM Table name to copy
WHERE Screening conditions;

Copy only part of the table structure:
CREATE TABLE New table name 
SELECT Column 1, column 2 FROM Table name to copy WHERE 0;

② Case

Case 1: create a copy table with the same structure as the book table.

CREATE TABLE copy LIKE book;

Case 2: create a copy1 table with the same structure and data as the book table.

CREATE TABLE copy1 
SELECT * FROM BOOK;

Case 3: create a copy2 table. The fields in the table are required to have id, bName, and the data is consistent with the first five items in the book table (i.e. id 1-5).

CREATE TABLE copy2 
SELECT id,bName FROM book
WHERE id < 6;

Case 4: create a copy3 table. The fields in the table are required to have id and bName.

CREATE TABLE copy3 
SELECT id,bName FROM book WHERE 0;

3, Common data types

Numeric: integer, decimal (fixed-point and floating-point).
Character type: short text: char,varchar; Long text: text, Bibo (long binary number); enum - enumeration type requires that the inserted value can only be the value specified in the enumeration, and the characters are not case sensitive; Set – the value inserted by the set is any value in the set and is not case sensitive.
Date type

1. Numerical type

① Integer

Classification: tinyint(1 byte), samllint(2 bytes), mediumint(3 bytes), int/integer(4 bytes), bigint(8 bytes).

characteristic:
(1) The default is signed integer. If you need to set unsigned integer, add "INT UNSIGNED" after the field;
(2) If the inserted value exceeds the range that can be represented, "out of range" exception will be reported, and the critical value will be inserted into the table;
(3) If the length is not set, there will be a default length. The length represents the number of digits displayed. If the inserted value is not enough for the set digits, add 0 to fill the digits, but it can be displayed only with "INT ZEROFILL".

② Decimals

Classification:
Floating point: float (m, d) --- 4 bytes, double (m, d) --- 8 bytes;
Fixed point type: dec(M,D) or decimal(M,D).

characteristic:
(1)M – represents the total length of integer part plus decimal part; D – represents the length of the decimal part. If the end number to be discarded is greater than 5, it will be rounded to the previous digit; If the number of inserts exceeds the maximum range, the critical value is inserted.
(2) Both M and D can be omitted. If it is a fixed-point type, m defaults to 10 and D defaults to 0. The floating point is determined according to the inserted data.
(3) The precision of fixed-point type is high. If the precision of the inserted value is required to be high, it can be used. For example: currency.

2. Character type

① Features:
(1)char(M)/varchar(M) - m in represents the maximum number of characters. M after char can be omitted. The default is 1. Varchar cannot be omitted.
(2) char – for fixed length characters, varchar – for variable length characters. (that is, varchar opens up the storage space according to the actual input length, and char opens up the specified storage space).
(3) char consumes more storage space, but r execution efficiency is slightly higher than varchar.

3. Date type

① Classification:
data – save date only
Time – save only time
year – save for years only
datatime - save date + time
timestamp - save date + time

② Comparison between datatime and timestamp
datatime, accounting for 8 bytes, indicates that the year range is 1000-9999 and is not affected by the time zone; timestamp, accounting for 4 bytes, indicates that the year range is 1970-2038, which is affected by the time zone.

4, Common constraints

1. Constraint definition

Constraint is used to limit the data of rows or columns in a table to ensure the accuracy and reliability of the data in the table.

2. Constraint classification

① NOT NULL ---- non NULL constraint, which is used to ensure that the value inserted in this field is not empty, such as name and student number.
② DEFAULT ---- DEFAULT constraint, which is used to add the DEFAULT initial value to this field, such as class.
③ PRIMARY KEY ---- PRIMARY KEY constraint, which is used to ensure that the field is unique and non empty, such as student number and employee number.
④ UNIQUE ---- UNIQUE constraint, which is used to ensure that the field is UNIQUE and can be empty.
⑤ CHECK ---- CHECK constraints, which MYSQL does not support.
⑥ FOREIGN KEY ---- FOREIGN KEY constraint, which is used to restrict the relationship between two tables and ensure that the value of this field must come from a table. When adding a FOREIGN KEY constraint from a table, it is used to reference a column value of the main table. For example: employee type of work number.

3. Primary key and unique difference (interview questions)

Both primary key and unique can ensure the uniqueness of the inserted data, and both allow the two columns to be displayed together. However, the primary key cannot be empty, and there can only be one primary key in a table, and the unique key can be empty, and there can be multiple primary keys in a table.

4. Add constraints when creating tables

Comparison of column level constraints and table level constraints:

① Add column level constraints. You can add constraint types directly after field names and types. You can add multiple constraints. Only default, non empty, primary key and unique are supported.

CREATE TABLE stuinfo(
	id INT PRIMARY KEY,               #Primary key constraint
	stuName VARCHAR(20) NOT NULL,                #Non empty
	gender CHAR(1) CHECK(gender='male' OR gender ='female'),          #Checking constraints is not supported by MYSQL. It can be ignored
	seat INT UNIQUE ,                #only
	age INT DEFAULT 18              #Default constraint
);
#View all indexes in stuinfo, including primary key, foreign key and unique key.
SHOW INDEX FROM stuinfo;

② Add a table level constraint. At the bottom of each field, the values in "[constraint constraint name] constraint type (segment name)" [] may not be written

CREATE TABLE stuinfo(
	id INT ,
	stuName VARCHAR(20) ,            
	gender CHAR(1), 
	seat INT ,             
	age INT ,              
	majorid INT ,
	CONSTRAINT pk PRIMARY KEY(id),#Primary key
	CONSTRAINT uq UNIQUE(seat),#Unique key
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#Foreign key
);

③ General writing method of two kinds of constraints

CREATE TABLE stuinfo(
	id INT PRIMARY KEY,       #Primary key constraint
	stuName VARCHAR(20) NOT NULL,     #Non empty
	gender CHAR(1) ,                 #Check constraints
	seat INT UNIQUE ,                #only
	age INT DEFAULT 18,              #Default constraint
	majorid INT ,
	CONSTRAINT fk_stuinfo_major    #Alias table level constraints
	FOREIGN KEY(majorid) REFERENCES major(id)   #Foreign key
);

5. Add constraints when modifying tables

① Grammar

#Add column level constraints
ALTER TABLE Table name
MODIFY COLUMN Field name field type new constraint;
#Add table level constraints
ALTER TABLE Table name ADD 
[CONSTRAINT Constraint name] constraint type(Field name) [Foreign key reference];

② Use case

#1. Add a non empty constraint
ALTER TABLE stuinfo 
MODIFY COLUMN stuName VARCHAR(20) NOT NULL;

#2. Add default constraint
ALTER TABLE stuinfo 
MODIFY COLUMN age INT DEFAULT 20;

#3. Add primary key
#① Column level constraint
ALTER TABLE stuinfo 
MODIFY COLUMN id INT PRIMARY KEY;
#② Table level constraints
ALTER TABLE stuinfo ADD PRIMARY KEY(id);

#4. Add unique key
#① Column level constraint
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#② Table level constraints
ALTER TABLE stuinfo ADD UNIQUE(seat);

#5. Add foreign key
ALTER TABLE stuinfo ADD 
FOREIGN KEY(majorid) REFERENCES major(id);

6. Delete constraints when deleting tables

① Use case

#1. Delete non empty constraints
ALTER TABLE stuinfo 
MODIFY COLUMN stuName VARCHAR(20) ;

#2. Delete default constraint
ALTER TABLE stuinfo MODIFY COLUMN age INT ;

#3. Delete primary key
#① Column level constraint
ALTER TABLE stuinfo MODIFY COLUMN id INT ;
#② Table level constraints
ALTER TABLE stuinfo DROP PRIMARY KEY;

#4. Delete unique
ALTER TABLE stuinfo DROP INDEX seat;
SHOW INDEX FROM stuinfo;#View index

#5. Delete foreign key
ALTER TABLE stuinfo DROP FOREIGN KEY major;

7. Foreign key characteristics

① Requires a foreign key relationship to be set on the slave table.
② The type of the foreign key column of the slave table and the type of the associated column of the master table are required to be consistent or compatible, and the name is not required.
③ The key column of the main table must be a key (usually primary key or unique).
④ When inserting data, insert the master table first and then the slave table; When deleting data, delete the secondary table first, and then the primary table.

5, Identity column

1. Identification column definition

The identification column is also called self growing column, which means that you do not need to insert values manually. The system provides default sequence values.

2. Characteristics of identification column

① The identification column must be used with key (key, unique, default, etc.).
② A table can have at most one self growing column.
③ The type of identity column can only be numeric.
④ The identification column can be displayed through SET auto_increment =3; To set the step size, you can set the starting value by manually inserting a value.

3. Identify column use cases

#Set identity column when creating table
DROP TABLE IF EXISTS tab_identity;     #Delete table
CREATE TABLE tab_identity(
	id INT PRIMARY KEY AUTO_INCREMENT ,
	stu_name VARCHAR(20)
);
INSERT INTO tab_identity VALUES(NULL,'hhaha');
SELECT * FROM tab_identity;

#Set identity column when modifying table
ALTER TABLE tab_identity 
MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

#Delete identifier when modifying table
ALTER TABLE tab_identity MODIFY COLUMN id INT;

The use effect of adding self growth columns is as follows:

Tags: Database MySQL SQL

Posted on Wed, 06 Oct 2021 22:27:06 -0400 by miniu