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:
