MySQL basics -- 003 SQL DDL, DML and DCL usage: creating and managing tables, adding, deleting and modifying data processing, MySQL data types, constraints

Cool breeze AAA 2022-02-13 07:35:00 阅读数:492

mysql basics sql ddl dml

The first 10 Chapter _ Create and manage tables

lecturer : Silicon Valley - Song Hongkang ( Jianghu people : a brand of instant noodles )

Official website :http://www.atguigu.com


1. Basic knowledge of

1.1 A procedure for storing data

Storing data is the first step in processing data . Only by storing the data correctly , We can carry out effective processing and Analysis . otherwise , It can only be a mess , Do not know how to start .

that , How can we put all kinds of business-related 、 Complex data , Orderly 、 Store it efficiently ? stay MySQL in , A complete data stored procedure has a total of 4 Step , Create a database 、 Acknowledgement field 、 Create data table 、 insert data .

 Insert picture description here

We need to create a database first , Instead of creating a data table directly ?

Because from the level of system architecture ,MySQL The order of database system from large to small is database server database Data sheet 、 Data table Rows and columns .

MySQL The database server was previously installed . therefore , Let's start by creating a database .

1.2 Identifier naming rules

  • Database name 、 The table name must not exceed 30 Characters , Variable names are limited to 29 individual
  • Must contain only A–Z, a–z, 0–9, _ common 63 Characters
  • Database name 、 Table name 、 Do not include spaces in object names such as field names
  • The same MySQL In software , The database cannot have the same name ; In the same library , A watch cannot have the same name ; In the same table , The field cannot have the same name
  • You must ensure that your field has no and reserved words 、 Database systems or common methods conflict . If you insist on using , Please be there. SQL Use in statement `( mark of emphasis ) Lead up
  • Keep field names and types consistent : When naming fields and specifying data types for them, be sure to ensure consistency , If the data type is an integer in a table , Then don't turn into character in another table

1.3 MySQL Data types in

type Examples of types
Integer types TINYINT、SMALLINT、MEDIUMINT、INT( or INTEGER)、BIGINT
Floating point type FLOAT、DOUBLE
Fixed point number type DECIMAL
A type of BIT
Date time type YEAR、TIME、DATE、DATETIME、TIMESTAMP
Text string type CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
Enumeration type ENUM
Collection types SET
Binary string type BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JSON type JSON object 、JSON Array
Spatial data types Single value :GEOMETRY、POINT、LINESTRING、POLYGON;
aggregate :MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION

among , Several common types are introduced as follows :

data type describe
INT from -231 To 231-1 The integer data of . The storage size is 4 Bytes
CHAR(size) Fixed length character data . If not specified , The default is 1 Characters , Maximum length 255
VARCHAR(size) Variable length character data , Save according to the actual length of the string , Length... Must be specified
FLOAT(M,D) Single precision , Occupy 4 Bytes ,M= Integer bit + Decimal places ,D= Decimal places . D<=M<=255,0<=D<=30, Default M+D<=6
DOUBLE(M,D) Double precision , Occupy 8 Bytes ,D<=M<=255,0<=D<=30, Default M+D<=15
DECIMAL(M,D) High precision decimal , Occupy M+2 Bytes ,D<=M<=65,0<=D<=30, The maximum value range is related to DOUBLE identical .
DATE Date data , Format ’YYYY-MM-DD’
BLOB Long text data in binary form , Up to 4G
TEXT Long text data , Up to 4G

2. Create and manage databases

2.1 Create database

  • The way 1: Create database
CREATE DATABASE Database name ;
example :
CREATE DATABASE mytest1; # This database was created using the default character set mysql8.0 The default is utf8,mysql5.7 If not modified, the default is Latin 
  • The way 2: Create database and specify character set
CREATE DATABASE Database name CHARACTER SET Character set ;
# Explicitly specifies the character set of the database to be created 
CREATE DATABASE mytest2 CHARACTER SET 'gbk';
  • The way 3: Determine whether the database already exists , If it does not exist, create the database ( recommend
CREATE DATABASE IF NOT EXISTS Database name ;
#( recommend ): If the database to be created already exists , The creation is not successful , But there is no error .
CREATE DATABASE IF NOT EXISTS mytest2 CHARACTER SET 'utf8';
# If the database to be created does not exist , Then the creation is successful 
CREATE DATABASE IF NOT EXISTS mytest3 CHARACTER SET 'utf8';

If MySQL The relevant database already exists , The create statement is ignored , No more database creation .

Be careful :DATABASE You can't change your name . Some visualization tools can be renamed , It's building a new library , Copy all tables to the new library , Delete the old library again .

2.2 Using a database

  • View all current databases
SHOW DATABASES; # There is one S, Representing multiple databases 
  • View the database currently in use
SELECT DATABASE(); # One used mysql Global functions in 
Equivalent to :
SELECT DATABASE() FROM DUAL;# View currently used databases 
  • View all tables under the specified library
SHOW TABLES;# View the data table saved in the current database , If in the current database from It can be omitted later .
SHOW TABLES FROM Database name ;# View the data table saved in the specified database 
  • View database creation information
SHOW CREATE DATABASE Database name ;
perhaps :
SHOW CREATE DATABASE Database name \G
  • Use / Switch database
USE Database name ;

Be careful : Before operating tables and data, you must first explain which database you are operating on , Otherwise, add... To all objects “ Database name .”.

2.3 modify the database

  • Change the database character set
ALTER DATABASE Database name CHARACTER SET Character set ; # such as :gbk、utf8 etc. 

2.4 Delete database

  • The way 1: Delete the specified database
DROP DATABASE Database name ;# If the database to be deleted exists , Delete successfully . If it doesn't exist , False report 
  • The way 2: Delete the specified database ( recommend
DROP DATABASE IF EXISTS Database name ;# If the database to be deleted exists , Delete successfully . If it doesn't exist , It ends silently , No mistake. .

3. Create table

3.1 How it was created 1:" A self-made man " The way

  • Must have :
    • CREATE TABLE jurisdiction
    • Storage space
  • Grammar format :
CREATE TABLE [IF NOT EXISTS] Table name (
Field 1, data type [ constraint condition ] [ The default value is ],
Field 2, data type [ constraint condition ] [ The default value is ],
Field 3, data type [ constraint condition ] [ The default value is ],
……
[ Table constraints ]
);

Combined with the IF NOT EXISTS keyword , said : If the data table to be created does not exist in the current database , Then create a data table ; If the data table to be created already exists in the current database , The table creation statement is ignored , No longer create data table .

  • Must specify :

    • Table name
    • Name ( Or field name ), data type , length
  • Optional designation :

    • constraint condition
    • The default value is
  • Create table example 1:

 # The way 1:" A self-made man " The way 
CREATE TABLE IF NOT EXISTS myemp1( # Users are required to have permission to create tables .
id INT,
emp_name VARCHAR(15), # Use VARCHAR To define a string , Must be in use VARCHAR Indicate its length when .
hire_date DATE
);
# View table structure 
DESC myemp1;
# View the statement structure of creating a table 
SHOW CREATE TABLE myemp1; # If the character set used is not specified when creating the table , The character set of the database where the table is located is used by default ,
If the database does not indicate, look at the character set of the database configuration file .
# View table data 
SELECT * FROM myemp1;
  • Create table example 2:
-- Create table 
CREATE TABLE emp (
-- int type 
emp_id INT,
-- Save at most 20 Chinese and English characters 
emp_name VARCHAR(20),
-- The total number of digits shall not exceed 15 position 
salary DOUBLE,
-- The date type 
birthday DATE
);
DESC emp;# View table structure 

 Insert picture description here

MySQL When executing the table creation statement , take id The type of the field is set to int(11), there 11 It's actually int Type specifies the display width , The default display width is 11. You can also specify the display width of data when creating a data table .

  • Create table example 3:
CREATE TABLE dept(
-- int type , Self increasing 
deptno INT(2) AUTO_INCREMENT,
dname VARCHAR(14),
loc VARCHAR(13),
-- Primary key 
PRIMARY KEY (deptno)
);
DESCRIBE dept;

 Insert picture description here

stay MySQL 8.x In the version , No longer recommended as INT Type specifies the display length , This syntax may be removed in future versions .

3.2 How it was created 2: Create on an existing table

  • Use AS subquery Options , Combine creating tables with inserting data

 Insert picture description here

  • The specified column should correspond to the column in the subquery one by one

  • Define columns by column names and default values

CREATE TABLE emp1
AS SELECT * FROM employees;
CREATE TABLE emp2
AS SELECT * FROM employees WHERE 1=2; -- Created emp2 Is an empty table 
CREATE TABLE dept80
AS
SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM employees
WHERE department_id = 80;
DESCRIBE dept80;

 Insert picture description here
 Insert picture description here

# The way 2: Based on existing tables , Import data at the same time 
CREATE TABLE myemp2
AS
SELECT employee_id,last_name,salary
FROM employees;
DESC myemp2;
DESC employees;
SELECT *
FROM myemp2;
# explain 1: The alias of the field in the query statement , Can be used as the name of the field of the newly created table .
# explain 2: At this time, the query statement can have rich structure , Use the various SELECT
CREATE TABLE myemp3
AS
SELECT e.employee_id emp_id,e.last_name lname,d.department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;
SELECT *
FROM myemp3;
DESC myemp3;
# practice 1: Create a table employees_copy, Realize to employees Replication of tables , Include table data 
CREATE TABLE employees_copy
AS
SELECT *
FROM employees;
SELECT * FROM employees_copy;
# practice 2: Create a table employees_blank, Realize to employees Replication of tables , Excluding table data 
CREATE TABLE employees_blank
AS
SELECT *
FROM employees
#where department_id > 10000;
WHERE 1 = 2; # Mountain without Mausoleum , Harmony , But I dare to fight with you .1 It can't be equal to 2, Therefore, it is impossible to query the results , That is, there is no data .
SELECT * FROM employees_blank;

3.3 Look at the data table structure

stay MySQL After creating the data table in , You can view the structure of the data table .MySQL Support use DESCRIBE/DESC Statement to view the data table structure , Also supports the use of SHOW CREATE TABLE Statement to view the data table structure .

The syntax is as follows :

SHOW CREATE TABLE Table name \G

Use SHOW CREATE TABLE Statement can not only view the detailed statement when the table is created , You can also view the storage engine and character encoding .

4. Modify table

Modifying a table means modifying the structure of a data table that already exists in the database .

Use ALTER TABLE Statement can realize :

  • Add columns to existing tables

  • Modify columns in existing tables

  • Delete columns from existing tables

  • Rename columns in an existing table

4.1 Append a column

The syntax is as follows :

ALTER TABLE Table name ADDCOLUMN】 Field name Field type 【FIRST|AFTER Field name 】;

give an example :

ALTER TABLE dept80
ADD job_id varchar(15);
ALTER TABLE myemp1
ADD salary DOUBLE(10,2); # By default, it is added to the position of the last field in the table 
ALTER TABLE myemp1
ADD phone_number VARCHAR(20) FIRST;# The location of the first field added to the table 
ALTER TABLE myemp1
ADD email VARCHAR(45) AFTER emp_name;# Added to the table emp_name The position after the field 

 Insert picture description here

4.2 Modify a column

  • You can modify the data type of the column , length 、 Default values and locations

  • Modify field data type 、 length 、 The default value is 、 The syntax format of the location is as follows :

ALTER TABLE Table name MODIFYCOLUMN】 Field name 1 Field type 【DEFAULT The default value is 】【FIRST|AFTER Field name 2;
  • give an example :
ALTER TABLE dept80
MODIFY last_name VARCHAR(30);
ALTER TABLE dept80
MODIFY salary double(9,2) default 1000;
# Modify a field : data type 、 length 、 The default value is ( A little )
# Generally, the field length is modified , The type is usually correct .
ALTER TABLE myemp1
MODIFY emp_name VARCHAR(25) ; # Modify the length 
ALTER TABLE myemp1
MODIFY emp_name VARCHAR(35) DEFAULT 'aaa'; # Modify the default 
  • Changes to the default value only affect future changes to the table
  • Besides , You can also modify column constraints in this way . Let's not talk about .

4.3 Rename a column

Use CHANGE old_column new_column dataType Clause renames the column . The syntax is as follows :

ALTER TABLE Table name CHANGE 【column】 Name New column names New data types ;

give an example :

ALTER TABLE dept80
CHANGE department_name dept_name varchar(15);
ALTER TABLE myemp1
MODIFY emp_name VARCHAR(35) DEFAULT 'aaa';
# Rename a field 
ALTER TABLE myemp1
CHANGE salary monthly_salary DOUBLE(10,2);
ALTER TABLE myemp1
CHANGE email my_email VARCHAR(50);# Rename while modifying the scope 

4.4 Delete a column

The syntax format of deleting a field in the table is as follows :

ALTER TABLE Table name DROPCOLUMN】 Field name

give an example :

ALTER TABLE dept80
DROP COLUMN job_id;
# Delete a field 
ALTER TABLE myemp1
DROP COLUMN my_email;

5. rename table

  • Mode one : Use RENAME
RENAME TABLE emp
TO myemp; # The front is the original table name , Followed by the new table name 
  • Mode two :
ALTER table dept # The front is the original table name , Followed by the new table name 
RENAME [TO] detail_dept; -- [TO] It can be omitted 
  • Must be the owner of the object

6. Delete table

  • stay MySQL in , When a data sheet No association with any other data table when , You can delete the current data table directly .

  • Data and structures are deleted

  • All running related transactions are committed

  • All relevant indexes are deleted

  • Grammar format :

DROP TABLE [IF EXISTS] Data sheet 1 [, Data sheet 2,, Data sheet n];

IF EXISTS Means : If the corresponding data table exists in the current database , Delete the data table ; If the corresponding data table does not exist in the current database , Delete statement is ignored , No longer delete the data table .

  • give an example :
DROP TABLE dept80;
# Delete table 
# Not only delete the table structure , At the same time, the data in the table is deleted , Free table space 
DROP TABLE IF EXISTS myemp2;
DROP TABLE IF EXISTS myemp12;
  • DROP TABLE Statement cannot be rolled back

7. Clear the table

  • TRUNCATE TABLE sentence :

    • Delete all data in the table
    • Free the storage space of the table
  • give an example :

TRUNCATE TABLE detail_dept;
# Clear the table , Indicates that all data in the table is cleared , But the table structure remains .
SELECT * FROM employees_copy;
TRUNCATE TABLE employees_copy;
SELECT * FROM employees_copy;
DESC employees_copy;
  • TRUNCATE sentence Cannot be rolled back , While using DELETE Statement delete data , You can roll back

  • contrast :

#7. DCL in COMMIT and ROLLBACK
# COMMIT: Submit data . Once executed COMMIT, The data is permanently stored in the database , This means that data cannot be rolled back .
# ROLLBACK: Undo Data . Once executed ROLLBACK, Data rollback can be realized . Roll back to the most recent COMMIT after .
#8. contrast TRUNCATE TABLE and DELETE FROM 
# The same thing : All data in the table can be deleted , While preserving the table structure .
# Difference :
# TRUNCATE TABLE: Once you do this , Clear all table data . meanwhile , Data cannot be rolled back .
# DELETE FROM: Once you do this , All table data can be cleared ( No WHERE). meanwhile , Data can be rolled back .
/* 9. DDL and DML Explanation ① DDL Once the operation is performed , You can't roll back . Instructions SET autocommit = FALSE Yes DDL Operation failure .( Because at the end of execution DDL After the operation , It must be done once COMMIT. And this COMMIT The operation is not affected by SET autocommit = FALSE Affected .) ② DML By default , Once executed , It is also non rollback . however , If in execution DML Before , Yes SET autocommit = FALSE, Then the execution of DML Operation can realize rollback . */
# demonstration :DELETE FROM 
#1)
COMMIT;
#2)
SELECT *
FROM myemp3;
#3)
SET autocommit = FALSE;
#4)
DELETE FROM myemp3;
#5)
SELECT *
FROM myemp3;
#6)
ROLLBACK;
#7)
SELECT *
FROM myemp3;
# demonstration :TRUNCATE TABLE
#1)
COMMIT;
#2)
SELECT *
FROM myemp3;
#3)
SET autocommit = FALSE;
#4)
TRUNCATE TABLE myemp3;
#5)
SELECT *
FROM myemp3;
#6)
ROLLBACK;
#7)
SELECT *
FROM myemp3;
/*
DDL: Data definition language .CREATE \ ALTER \ DROP \ RENAME \ TRUNCATE
DML: Data operation language .INSERT \ DELETE \ UPDATE \ SELECT ( A top priority )
DCL: Data control language .COMMIT \ ROLLBACK \ SAVEPOINT \ GRANT \ REVOKE

Ali Development Specification :

【 Reference resources 】TRUNCATE TABLE Than DELETE Fast , It also uses less system and transaction log resources , but TRUNCATE No transaction and no trigger TRIGGER, Possible accidents , It is not recommended to use this statement in development code .

explain :TRUNCATE TABLE In function and without WHERE Clause DELETE Same statement .

8. Content expansion

expand 1: Alibaba 《Java Development Manual 》 And MySQL Field naming

  • mandatory 】 Table name 、 Field names must be lowercase letters or numbers , No beginning of number , Forbid only numbers between two underscores . The modification of database field names costs a lot , Because pre release is not possible , So field names need to be carefully considered .

    • Example :aliyun_admin,rdc_config,level3_name
    • Counter example :AliyunAdmin,rdcConfig,level_3_name
  • mandatory 】 Disable reserved words , Such as desc、range、match、delayed etc. , Please refer to MySQL Official reserved word .

  • mandatory 】 Table required three fields :id, gmt_create, gmt_modified.

    • explain : among id Primary key required , The type is BIGINT UNSIGNED、 Self increasing in single table 、 In steps of 1.gmt_create, gmt_modified The types of are DATETIME type , The former now stands for active creation , The former participle means passive renewal
  • recommend 】 The best way to name a table is to follow “ Business name _ Function of table ”.

    • Example :alipay_task 、 force_project、 trade_config
  • recommend 】 The library name and application name should be consistent as much as possible .

  • 【 Reference resources 】 Appropriate character storage length , Not only save database table space 、 Save index storage , More importantly, improve the retrieval speed .

    • Example : Unsigned values can avoid false negative numbers , And expand the scope of representation .

 Insert picture description here

expand 2: How to understand empty table 、 Be careful when deleting tables and other operations ?!

Table delete The operation will delete the definition of the table and the data in the table , also MySQL When deleting , There will be no confirmation message , Therefore, you should be careful when deleting . Before deleting the table , It is best to compare the data in the table Backup , In this way, the data can be recovered in case of misoperation , So as not to cause irreparable consequences .

alike , In the use of ALTER TABLE During the basic modification operation of the table , Before performing the operation process , You should also ensure that the data is completely Backup , Because the database changes are Can't undo Of , If you add an unnecessary field , It can be deleted ; same , If you delete a required column , All data below this column will be lost .

expand 3:MySQL8 New characteristics —DDL Atomization of

stay MySQL 8.0 In the version ,InnoDB Tabular DDL Support transaction integrity , namely DDL The operation either succeeds or rolls back .DDL Operation rollback log write to data dictionary Data dictionary table mysql.innodb_ddl_log( The table is a hidden table , adopt show tables Can't see ) in , For rollback operations . By setting parameters , Can be DDL Print and output operation log to MySQL In the error log .

Respectively in MySQL 5.7 Version and MySQL 8.0 Create databases and data tables in version , give the result as follows :

CREATE DATABASE mytest;
USE mytest;
CREATE TABLE book1(
book_id INT ,
book_name VARCHAR(255)
);
SHOW TABLES;

(1) stay MySQL 5.7 In the version , The test steps are as follows :
Delete data table book1 Data tables book2, give the result as follows :

mysql> DROP TABLE book1,book2;
ERROR 1051 (42S02): Unknown table 'mytest.book2'

Query the data table name in the database again , give the result as follows :

mysql> SHOW TABLES;
Empty set (0.00 sec)

It can be seen from the results , Although the deletion operation is wrong , But the data table is still deleted book1.

(2) stay MySQL 8.0 In the version , The test steps are as follows :
Delete data table book1 Data tables book2, give the result as follows :

mysql> DROP TABLE book1,book2;
ERROR 1051 (42S02): Unknown table 'mytest.book2'

Query the data table name in the database again , give the result as follows :

mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| book1 |
+------------------+
1 row in set (0.00 sec)

It can be seen from the results , Data sheet book1 Not deleted .

The first 11 Chapter _ Addition, deletion and modification of data processing ( Every note at the beginning of this article ...)

lecturer : Silicon Valley - Song Hongkang ( Jianghu people : a brand of instant noodles )

Official website :http://www.atguigu.com


1. insert data

1.1 Practical problems

 Insert picture description here

Solution : Use INSERT Statement inserts data into a table .

1.2 The way 1:VALUES The way to add

Using this syntax, you can only insert into a table at a time One data .

situation 1: Inserts data in the default order for all fields of the table

INSERT INTO Table name
VALUES (value1,value2,....);

In the value list, you need to specify a value for each field of the table , And the order of values must be the same as that of fields in the data table .

give an example :

INSERT INTO departments
VALUES (70, 'Pub', 100, 1700);
INSERT INTO departments
VALUES (100, 'Finance', NULL, NULL);
# The way 1: Add data one by one Insert a row by default 
# ① The added field is not indicated 
# Correct 
INSERT INTO emp1
VALUES (1,'Tom','2000-12-21',3400); # Be careful : Be sure to add... In the order of the fields declared in the table 
# FALSE 
INSERT INTO emp1
VALUES (2,3400,'2000-12-21','Jerry');

situation 2: Insert data for the specified fields of the table

INSERT INTO Table name (column1 [, column2,, columnn])
VALUES (value1 [,value2,, valuen]);

Insert data for the specified fields of the table , Is in the INSERT Statement inserts values into only some fields , The values of other fields are the default values when the table is defined .

stay INSERT Column names are arbitrarily listed in the clause , But once listed ,VALUES To insert value1,…valuen Need and column1,…columnn The columns correspond one by one . If the type is different , Will not be able to insert , also MySQL There will be mistakes .

give an example :

INSERT INTO departments(department_id, department_name)
VALUES (80, 'IT');
# ② Indicate the field to add ( recommend )
INSERT INTO emp1(id,hire_date,salary,`name`)
VALUES(2,'1999-09-09',4000,'Jerry'); # Add... In the order of the fields you write 
# explain : Not assigned hire_date The value of is null
INSERT INTO emp1(id,salary,`name`) # You can insert some field data , Fields not inserted are displayed as null, If there are no constraints .
VALUES(3,4500,'shk');

situation 3: Insert multiple records at the same time

INSERT Statement can insert multiple records into the data table at the same time , Specify multiple value lists when inserting , Each value list is separated by commas , The basic syntax is as follows :

INSERT INTO table_name
VALUES
(value1 [,value2,, valuen]),
(value1 [,value2,, valuen]),
……
(value1 [,value2,, valuen]);

perhaps

INSERT INTO table_name(column1 [, column2,, columnn])
VALUES
(value1 [,value2,, valuen]),
(value1 [,value2,, valuen]),
……
(value1 [,value2,, valuen]);

give an example :

mysql> INSERT INTO emp(emp_id,emp_name)
-> VALUES (1001,'shkstart'),
-> (1002,'atguigu'),
-> (1003,'Tom');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
# ③ Insert multiple records at the same time ( recommend )
INSERT INTO emp1(id,NAME,salary)
VALUES
(4,'Jim',5000), # More efficient than separate writing 
(5,' Zhang Junjie ',5500);

Use INSERT When inserting multiple records at the same time ,MySQL It will return some additional information that is not available when performing single line insertion , The meaning of this information is as follows :
● Records: Indicates the number of records inserted .
● Duplicates: Indicates the records that were ignored during insertion , The reason may be that these records contain duplicate primary key values .
● Warnings: Data values indicating a problem , For example, data type conversion occurs .

A that inserts multiple rows of records at the same time INSERT Statement is equivalent to multiple single line inserts INSERT sentence , But multi line INSERT Statement during processing More efficient . because MySQL Execute a single article INSERT Statement inserts more rows of data than using multiple INSERT Fast sentence , Therefore, when inserting multiple records, it is best to choose to use a single record INSERT Insert... In the form of a statement .

Summary :

  • VALUES Or you could write it as VALUE, however VALUES It's standard writing .

  • Character and date data should be enclosed in single quotes .

1.3 The way 2: Insert the query results into the table

INSERT Can also be SELECT Insert the result of the statement query into the table , At this time, it is not necessary to input the values of each record one by one , Just use one INSERT Statement and a line SELECT Statements can quickly insert multiple rows from one or more tables into a table .

The basic syntax is as follows :

INSERT INTO Target table name
(tar_column1 [, tar_column2,, tar_columnn])
SELECT
(src_column1 [, src_column2,, src_columnn])
FROM Source table name
[WHERE condition]
  • stay INSERT Add a subquery to the statement .
  • There's no need to write VALUES Clause .
  • The list of values in the subquery should be the same as INSERT The column name in Clause corresponds to .

give an example :

INSERT INTO emp2
SELECT *
FROM employees
WHERE department_id = 90;
INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';
# The way 2: Insert the query results into the table 
SELECT * FROM emp1;
INSERT INTO emp1(id,NAME,salary,hire_date)
# Query statement 
SELECT employee_id,last_name,salary,hire_date # The fields to be queried must correspond to the fields added to the table one by one 
FROM employees
WHERE department_id IN (70,60);
DESC emp1;
DESC employees;
# explain :emp1 surface ( New table ) The length of the field to add data in cannot be less than employees surface ( Old table ) The length of the field in the query .
# If emp1 The length of the field in the table to add data is less than employees The length of the query field in the table , There is a risk of adding failure .

2. Update data

 Insert picture description here

  • Use UPDATE Statement update data . The grammar is as follows :
UPDATE table_name
SET column1=value1, column2=value2,, column=valuen
[WHERE condition]
  • It can be updated at one time multiple data .

  • If you need to roll back data , It needs to be guaranteed in DML front , Set it up :SET AUTOCOMMIT = FALSE;


  • Use WHERE Clause specifies the data to be updated .
UPDATE employees
SET department_id = 70
WHERE employee_id = 113;
  • If omitted WHERE Clause , Then all data in the table will be updated .
UPDATE copy_emp
SET department_id = 110;
  • Data integrity error in update
UPDATE employees
SET department_id = 55
WHERE department_id = 110;

 Insert picture description here

explain : non-existent 55 Department No

3. Delete data

 Insert picture description here

  • Use DELETE Statement to delete data from a table

 Insert picture description here

DELETE FROM table_name [WHERE <condition>];

table_name Specify the table to delete ;“[WHERE ]” Is an optional parameter , Specify deletion criteria , without WHERE Clause ,DELETE Statement will delete all records in the table .

  • Use WHERE Clause deletes the specified record .
DELETE FROM departments
WHERE department_name = 'Finance';
  • If omitted WHERE Clause , All data in the table will be deleted
DELETE FROM copy_emp;
#3. Delete data DELETE FROM .... WHERE....
# No addition where The condition is to delete all data 
DELETE FROM emp1
WHERE id = 1;
# When deleting data , It may also be due to the influence of constraints , Result in deletion failure 
DELETE FROM departments
WHERE department_id = 50;
# Summary :DML Operation by default , After execution, the data will be submitted automatically .
# If you want to not automatically submit data after execution , You need to use SET autocommit = FALSE.
  • Data integrity error in delete
DELETE FROM departments
WHERE department_id = 60;

 Insert picture description here

explain :You cannot delete a row that contains a primary key that is used as a foreign key in another table.

4. MySQL8 New characteristics : Calculated column

What is a calculated column ? Simply put, the value of a column is calculated from other columns . for example ,a The column value is 1、b The column value is 2,c Columns do not need to be inserted manually , Definition a+b As the result of the c Value , that c Is the calculation column , It is calculated from other columns .

stay MySQL 8.0 in ,CREATE TABLE ( Create table ) and ALTER TABLE ( Modify table ) Both support adding calculation Columns . Let's say CREATE TABLE For example .

scene : If a table has many fields , Just now, the statement field column of the query comes from the column calculated by some of the fields , If you have to calculate yourself every time you write a query statement , So much trouble , Using calculated columns can simplify code .

give an example : Define data table tb1, Then define the fields id、 Field a、 Field b And field c, Which field c Is a calculated column , Used to calculate a+b Value .
First create a test table tb1, The statement is as follows :

CREATE TABLE tb1(
id INT,
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL
);

Insert demo data , The statement is as follows :

INSERT INTO tb1(a,b) VALUES (100,200);

Query data table tb1 Data in , give the result as follows :

mysql> SELECT * FROM tb1;
+------+------+------+------+
| id | a | b | c |
+------+------+------+------+
| NULL | 100 | 200 | 300 |
+------+------+------+------+
1 row in set (0.00 sec)

Update the data in the data , The statement is as follows :

mysql> UPDATE tb1 SET a = 500;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
#4. MySQL8 New features : Calculated column 
USE atguigudb;
CREATE TABLE test1(
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL # Field c This is the calculated column 
);
INSERT INTO test1(a,b)
VALUES(10,20); # The original c No insert value is null, Now is the calculated value .
SELECT * FROM test1;
UPDATE test1
SET a = 100;

5. Comprehensive case

# 1、 Create database test01_library
# 2、 Create table books, The table structure is as follows :
Field name Field description data type
id Book number INT
name Title VARCHAR(50)
authors author VARCHAR(100)
price Price FLOAT
pubdate Publication date YEAR
note explain VARCHAR(100)
num stock INT
# 3、 towards books Insert records in the table 
# 1) Do not specify field name , Insert the first record 
# 2) Specify all field names , Insert second record 
# 3) Insert multiple records at the same time ( All the remaining records )
id name authors price pubdate note num
1 Tal of AAA Dickes 23 1995 novel 11
2 EmmaT Jane lura 35 1993 joke 22
3 Story of Jane Jane Tim 40 2001 novel 0
4 Lovey Day George Byron 20 2005 novel 30
5 Old land Honore Blade 30 2010 law 0
6 The Battle Upton Sara 30 1999 medicine 40
7 Rose Hood Richard haggard 28 2008 cartoon 28
# 4、 Type the novel (novel) The prices of all the books have increased 5.
# 5、 Name as EmmaT Change the price of your book to 40, And change the description to drama.
# 6、 Delete inventory as 0 The record of .
# 7、 Included in the title of the statistical book a A Book of letters
# 8、 Included in the title of the statistical book a The number of alphabetic books and the total stock
# 9、 find “novel” Type of book , In descending order of price
# 10、 Search for book information , In descending order of inventory , If the inventory is the same, follow note Ascending order
# 11、 according to note Number of classified statistical books
# 12、 according to note Inventory of classified statistics , Show that the inventory exceeds 30 Ben's
# 13、 Search all books , Each page shows 5 Ben , Show second page
# 14、 according to note Inventory of classified statistics , Displays the most in stock
# 15、 Query the title of the book to 10 A one character book , Not including the space inside
# 16、 Check the title and type of the book , among note The value is novel Show novel ,law Show legal ,medicine Show medicine ,cartoon Show cartoon ,joke Show jokes
# 17、 Check the title of the book 、 stock , among num Value exceeds 30 Ben's , Show unsalable , Greater than 0 And below 10 Of , Show best sellers , by 0 The display needs to be out of stock
# 18、 Count each note The stock of , And total
# 19、 Count each note The number of , And total
# 20、 Count the top three books in stock
# 21、 Find the first book published
# 22、 find novel The most expensive book in
# 23、 Find the book with the most words in the title , No spaces

answer :

#1、 Create database test01_library
CREATE DATABASE IF NOT EXISTS test01_library CHARACTER SET 'utf8';
# Specify which database to use 
USE test01_library;
#2、 Create table books
CREATE TABLE books(
id INT,
name VARCHAR(50),
`authors` VARCHAR(100) ,
price FLOAT,
pubdate YEAR ,
note VARCHAR(100),
num INT
);
#3、 towards books Insert records in the table 
# 1) Do not specify field name , Insert the first record 
INSERT INTO books
VALUES(1,'Tal of AAA','Dickes',23,1995,'novel',11);
# 2) Specify all field names , Insert second record 
INSERT INTO books (id,name,`authors`,price,pubdate,note,num)
VALUES(2,'EmmaT','Jane lura',35,1993,'Joke',22);
# 3) Insert multiple records at the same time ( All the remaining records )
INSERT INTO books (id,name,`authors`,price,pubdate,note,num) VALUES
(3,'Story of Jane','Jane Tim',40,2001,'novel',0),
(4,'Lovey Day','George Byron',20,2005,'novel',30),
(5,'Old land','Honore Blade',30,2010,'Law',0),
(6,'The Battle','Upton Sara',30,1999,'medicine',40),
(7,'Rose Hood','Richard haggard',28,2008,'cartoon',28);
# 4、 Type the novel (novel) The prices of all the books have increased 5.
UPDATE books SET price=price+5 WHERE note = 'novel';
# 5、 Name as EmmaT Change the price of your book to 40, And change the description to drama.
UPDATE books SET price=40,note='drama' WHERE name='EmmaT';
# 6、 Delete inventory as 0 The record of .
DELETE FROM books WHERE num=0;
# 7、 Included in the title of the statistical book a A Book of letters
SELECT * FROM books WHERE name LIKE '%a%';
# 8、 Included in the title of the statistical book a The number of alphabetic books and the total stock
SELECT COUNT(*),SUM(num) FROM books WHERE name LIKE '%a%';
# 9、 find “novel” Type of book , In descending order of price
SELECT * FROM books WHERE note = 'novel' ORDER BY price DESC;
# 10、 Search for book information , In descending order of inventory , If the inventory is the same, follow note Ascending order
SELECT * FROM books ORDER BY num DESC,note ASC;
# 11、 according to note Number of classified statistical books
SELECT note,COUNT(*) FROM books GROUP BY note;
# 12、 according to note Inventory of classified statistics , Show that the inventory exceeds 30 Ben's
SELECT note,SUM(num) FROM books GROUP BY note HAVING SUM(num)>30;
# 13、 Search all books , Each page shows 5 Ben , Show second page
SELECT * FROM books LIMIT 5,5;
# 14、 according to note Inventory of classified statistics , Displays the most in stock
SELECT note,SUM(num) sum_num FROM books GROUP BY note ORDER BY sum_num DESC LIMIT 0,1;
# 15、 Query the title of the book to 10 A one character book , Not including the space inside
SELECT * FROM books WHERE CHAR_LENGTH(REPLACE(name,' ',''))>=10;
/*
16、 Check the title and type of the book ,
among note The value is novel Show novel ,law Show legal ,medicine Show medicine ,cartoon Show cartoon ,joke Show jokes
*/
SELECT name AS " Title " ,note, CASE note
WHEN 'novel' THEN ' A novel '
WHEN 'law' THEN ' law '
WHEN 'medicine' THEN ' medicine '
WHEN 'cartoon' THEN ' cartoon '
WHEN 'joke' THEN ' joke '
END AS " type "
FROM books;
# 17、 Check the title of the book 、 stock , among num Value exceeds 30 Ben's , Show unsalable , Greater than 0 And below 10 Of , Show best sellers , by 0 The display needs to be out of stock
SELECT name,num,CASE
WHEN num>30 THEN ' Unsalable '
WHEN num>0 AND num<10 THEN ' Sell well '
WHEN num=0 THEN ' No goods '
ELSE ' normal '
END AS " Inventory status "
FROM books;
# 18、 Count each note The stock of , And total
SELECT IFNULL(note,' Total inventory ') AS note,SUM(num) FROM books GROUP BY note WITH ROLLUP;
# 19、 Count each note The number of , And total
SELECT IFNULL(note,' Total ') AS note,COUNT(*) FROM books GROUP BY note WITH ROLLUP;
# 20、 Count the top three books in stock
SELECT * FROM books ORDER BY num DESC LIMIT 0,3;
# 21、 Find the first book published
SELECT * FROM books ORDER BY pubdate ASC LIMIT 0,1;
# 22、 find novel The most expensive book in
SELECT * FROM books WHERE note = 'novel' ORDER BY price DESC LIMIT 0,1;
# 23、 Find the book with the most words in the title , No spaces
SELECT * FROM books ORDER BY CHAR_LENGTH(REPLACE(name,' ','')) DESC LIMIT 0,1;

The first 12 Chapter _MySQL Data type refinement

lecturer : Silicon Valley - Song Hongkang ( Jianghu people : a brand of instant noodles )

Official website :http://www.atguigu.com


1. MySQL Data types in

type Examples of types
Integer types TINYINT、SMALLINT、MEDIUMINT、INT( or INTEGER)、BIGINT
Floating point type FLOAT、DOUBLE
Fixed point number type DECIMAL
A type of BIT
Date time type YEAR、TIME、DATE、DATETIME、TIMESTAMP
Text string type CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
Enumeration type ENUM
Collection types SET
Binary string type BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JSON type JSON object 、JSON Array
Spatial data types Single value type :GEOMETRY、POINT、LINESTRING、POLYGON;
Collection types :MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION

Properties of common data types , as follows :

MySQL keyword meaning
NULL The data column can contain NULL value
NOT NULL Data columns are not allowed to contain NULL value
DEFAULT The default value is
PRIMARY KEY Primary key
AUTO_INCREMENT Auto increment , For integer types
UNSIGNED Unsigned
CHARACTER SET name Specify a character set
#1. About attributes :character set name
# explain : The character set can be in Create database , Create table , Specify... When creating table fields .
If the field does not specify the character set when creating the table, the character set specified when creating the table will be followed , If it is not specified to create a table, follow the instructions when creating a database
The specified character set , If it is not specified when creating the database, it will be based on the character set of the configuration file of the database ,mysql The configuration file character set can be changed ,
If you haven't changed the default mysql5.7 For Latin ,mysql8.0 by utf8 Character set .
SHOW VARIABLES LIKE 'character_%';
# Name character set when creating database 
CREATE DATABASE IF NOT EXISTS dbtest12 CHARACTER SET 'utf8';
SHOW CREATE DATABASE dbtest12;
# When creating a table , Character set of naming table 
CREATE TABLE temp(
id INT
) CHARACTER SET 'utf8';
SHOW CREATE TABLE temp;
# Create table , When naming a field in a table , You can specify the character set of the field 
CREATE TABLE temp1(
id INT,
NAME VARCHAR(15) CHARACTER SET 'gbk'
);
SHOW CREATE TABLE temp1;

2. Integer types

2.1 Type introduction

Integer types have a total of 5 Kind of , Include TINYINT、SMALLINT、MEDIUMINT、INT(INTEGER) and BIGINT.

The differences between them are shown in the table below :

Integer types byte The value range of signed number The value range of the unsigned number
TINYINT 1 -128~127 0~255
SMALLINT 2 -32768~32767 0~65535
MEDIUMINT 3 -8388608~8388607 0~16777215
INT、INTEGER 4 -2147483648~2147483647 0~4294967295
BIGINT 8 -9223372036854775808~9223372036854775807 0~18446744073709551615

2.2 Optional attribute

There are three optional properties of integer type :

2.2.1 M

M: Express Show width ,M The range of phi is zero (0, 255). for example ,int(5): When the data width is less than 5 When the number is a bit, you need to fill the width with characters in front of the number . This function needs to cooperate with “ZEROFILL” Use , To express with “0” Fill the width , Otherwise, the specified display width is invalid .

If the display width is set , Then the inserted data width exceeds the display width limit , Will truncation or insertion fail ?

answer : There will be no impact on the inserted data , Or is it saved according to the actual width of the type , namely The display width is independent of the range of values that the type can store . from MySQL 8.0.17 Start , The display width property is not recommended for integer data types .

The integer data type can specify the required display width when defining the table structure , If you don't specify , The system specifies the default width value for each type .

give an example :

CREATE TABLE test_int1 (
x TINYINT,
y SMALLINT,
z MEDIUMINT,
m INT,
n BIGINT
);

View table structure (MySQL5.7 The explicit expression is as follows ,MySQL8 The scope is no longer explicit in )

 Insert picture description here

# Indicates the display width of the field , stay mysql8.0 Will not show , stay 5.7 Will be displayed .
# This width can also be specified by yourself .
# If this width is not specified when creating a table, the default value is as follows :
/*tinyint(4)--->-128~127 128, yes 3 The width of the bit plus the symbol is equal to 4 smallint(6)--->-32768~32767 32768,5 Plus the sign equals 6 mediumint(9)--->-8388608~8388607 Its unsigned range is 0~16777215,8 Plus the sign equals 9 int(11)--->... bigint(20)--->...*/
mysql> desc test_int1;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| x | tinyint(4) | YES | | NULL | |
| y | smallint(6) | YES | | NULL | |
| z | mediumint(9) | YES | | NULL | |
| m | int(11) | YES | | NULL | |
| n | bigint(20) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

TINYINT The value ranges of signed and unsigned numbers are -128127 and 0255, Because the minus sign occupies a digit , therefore TINYINT The default display width is 4. Empathy , The default display width of other integer types is the same as the minimum value of the signed number .

give an example :

CREATE TABLE test_int2(
f1 INT, # No default width added , by 11.
f2 INT(5),# The display width does not affect the range of the field itself ,int The scope is 11, When creating a table, the field with the display width is the same as that without . If you want to use it, you must cooperate with ZEROFILL.
f3 INT(5) ZEROFILL #① The display width is 5. When insert Insufficient value for 5 When a , Use 0 fill . 
#② If the width of the inserted data is greater than the specified display width, it is within the actual range , The normal display is the same as that without .
#③ When using ZEROFILL when , Automatically add UNSIGNED, Indicates that the unsigned data is positive .
)
DESC test_int2;
INSERT INTO test_int2(f1,f2,f3)
VALUES(1,123,123);# The result is : 1,123,00123, added ZEROFILL, Not enough 0 fill .
INSERT INTO test_int2(f1,f2)
VALUES(123456,123456);
INSERT INTO test_int2(f1,f2,f3)
VALUES(123456,123456,123456);
mysql> SELECT * FROM test_int2;
+--------+--------+--------+
| f1 | f2 | f3 |
+--------+--------+--------+
| 1 | 123 | 00123 |
| 123456 | 123456 | NULL |
| 123456 | 123456 | 123456 |
+--------+--------+--------+
3 rows in set (0.00 sec)

2.2.2 UNSIGNED

UNSIGNED: Unsigned type ( non-negative ), All integer types have an optional property UNSIGNED( Unsigned attribute ), The minimum value of unsigned integer type is 0. therefore , If you need to MySQL When saving non negative integer values in the database , You can set the integer type to unsigned .

int The default display width of the type is int(11), Unsigned int The default display width of the type is int(10).

CREATE TABLE test_int3(
f1 INT UNSIGNED #-2147483648~2147483647---》0~4294967295--》int The width of the unsigned range is 10, It used to be 11.
# Indicates that this field inserted now can only be 0~4294967295 Positive number of .
);
mysql> desc test_int3;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| f1 | int(10) unsigned | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

2.2.3 ZEROFILL

ZEROFILL: 0 fill ,( If a column is ZEROFILL, that MySQL Will automatically add... For the current column UNSIGNED attribute ), If you specify ZEROFILL It just means it's not enough M When a , use 0 Fill in on the left , If exceeded M position , As long as it does not exceed the data storage range .

original , stay int(M) in ,M It's worth it int(M) It doesn't matter how much storage space it takes . int(3)、int(4)、int(8) It's all on disk 4 bytes Storage space . in other words ,**int(M), It has to be with UNSIGNED ZEROFILL It makes sense to use them together .** If the integer value exceeds M position , It is stored according to the actual number of bits . Just no more characters 0 Fill in .

2.3 Applicable scenario

TINYINT: Generally used to enumerate data , For example, the system sets a small and fixed value range .

SMALLINT: It can be used for a small range of Statistics , For example, count the fixed asset inventory of the factory .

MEDIUMINT: Used for the calculation of larger integers , For example, the daily passenger flow of the station .

INT、INTEGER: The value range is large enough , In general, the problem of overrun does not need to be considered , Most used . Such as item number .

BIGINT: Only when you deal with very large integers will you use . For example, the trading volume of double 11 、 Large portal hits 、 Derivatives positions of securities companies, etc .

2.4 How to choose ?

When evaluating which integer type to use , You need to think about Storage space and reliability The balance of : a party Noodles , Using integer types that take up less bytes can save storage space ; On the other hand , In order to save storage space , The value range of integer type used is too small , In case of exceeding the value range , It could cause System error , Affect reliability .

for instance , The data type used for the item number is INT. And the reason is that , There are many kinds of goods circulating in customer stores , and , Old goods are off the shelves every day , New products on the shelves , So keep iterating , Accumulate over a long period .

If you use SMALLINT type , Although it takes up more bytes than INT There are fewer integers of type , But there is no guarantee that the data will not go out of range 65535. contrary , Use INT, You can ensure that there is a large enough value range , Don't worry about data out of range affecting reliability .

What you should pay attention to is , In practice , The cost of system failure far exceeds the cost of adding storage space for several fields . therefore , I suggest you first make sure that the data does not exceed the value range , Under this premise , Then consider how to save storage space .

3. Floating point type

3.1 Type introduction

Floating point number and fixed-point number types are characterized by Deal with decimals , You can think of integers as a special case of decimals . therefore , Usage scenarios of floating-point numbers and fixed-point numbers , Much larger than integers . MySQL Supported floating point number types , Namely FLOAT、DOUBLE、REAL.

  • FLOAT Represents a single precision floating-point number ;
  • DOUBLE Represents a double precision floating-point number ;

 Insert picture description here

  • REAL The default is DOUBLE. If you put SQL The mode is set to enable “REAL_AS_FLOAT”, that Well ,MySQL I think REAL yes FLOAT. If you want to enable “REAL_AS_FLOAT”, You can use the following SQL Statements for :

    SET sql_mode = “REAL_AS_FLOAT”;
    

** problem 1:**FLOAT and DOUBLE What is the difference between these two data types ?

FLOAT Bytes occupied ( Space ) Less , Value range ( precision ) Small ;DOUBLE Bytes occupied ( Space ) many , Value range ( precision ) Also big .

** problem 2:** Why is the value range of unsigned number of floating-point number type , It is only half of the value range of signed numbers , That is, it is only equivalent to the part of the signed number whose value range is greater than or equal to zero ?

MySQL The format for storing floating-point numbers is : Symbol (S) mantissa (M) and Order code (E). therefore , With or without symbols ,MySQL All floating-point numbers will store the part representing the symbol . therefore , The so-called unsigned number value range , In fact, it is the part of the signed number whose value range is greater than or equal to zero .

3.2 Data accuracy description

For floating point types , stay MySQL Single precision values in use 4 Bytes , Double values use 8 Bytes .

  • MySQL Allow to use Nonstandard grammar ( Other databases may not support , So if it comes to data migration , You'd better not use it like this ):FLOAT(M,D) or DOUBLE(M,D). here ,M be called precision ,D be called scale .(M,D) in M= Integer bit + Decimal places ,D= Decimal places . D<=M<=255,0<=D<=30.

    for example , Defined as FLOAT(5,2) A column of can be displayed as -999.99-999.99.(2 For decimal places 1 position , The whole number is 5-2=3 position ) If you exceed this range, you will report an error .

  • FLOAT and DOUBLE Type is not specified (M,D) when , By default, it will follow the actual precision ( It depends on the actual hardware and operating system ) To display .

  • explain : Floating point type , You can also add UNSIGNED, However, the data range will not be changed , for example :FLOAT(3,2) UNSIGNED Still can only mean 0-9.99 The scope of the .

  • Whether or not the precision is explicitly set (M,D), here MySQL The treatment plan is as follows :

    • If in storage , The integer part is out of range ,MySQL You're going to report a mistake , It is not allowed to store such value

    • If in storage , If the decimal part is out of range , In the following cases :

      • If after rounding , The integer part is not out of range , Just a warning , But it can operate successfully and delete the extra decimal places and save it . For example, in FLOAT(5,2) Insert... In the column 999.009, The approximate result is 999.01.
      • If after rounding , The integer part is out of range , be MySQL Report errors , And refuse to deal with . Such as FLOAT(5,2) Insert... In the column 999.995 and -999.995 All will report wrong. .
  • from MySQL 8.0.17 Start ,FLOAT(M,D) and DOUBLE(M,D) Usage is clearly not recommended in the official documents , May be removed in the future . in addition , About floating point FLOAT and DOUBLE Of UNSIGNED It is not recommended to use , It may also be removed in the future .

  • give an example

    CREATE TABLE test_double1(
    f1 FLOAT,
    f2 FLOAT(5,2),
    f3 DOUBLE,
    f4 DOUBLE(5,2)
    );
    DESC test_double1;
    INSERT INTO test_double1(f1,f2)
    VALUES(123.45,123.45);
    SELECT * FROM test_double1;
    INSERT INTO test_double1(f3,f4)
    VALUES(123.45,123.456); # Decimal out of range , There is rounding 
    #Out of range value for column 'f4' at row 1
    INSERT INTO test_double1(f3,f4)
    VALUES(123.45,1234.456);# An error is reported when the integer is out of range 
    #Out of range value for column 'f4' at row 1
    INSERT INTO test_double1(f3,f4)
    VALUES(123.45,999.995);# Decimal out of range , Round to the whole number , An error is reported when the integer is out of range .
    

3.3 Accuracy error description

Floating point type has a flaw , It's just not accurate . Let me explain why MySQL The floating point number is not accurate enough . such as , Let's design a watch , Yes f1 This field , The insertion values are 0.47,0.44,0.19, The result we expect is :0.47 + 0.44 + 0.19 = 1.1. While using sum Query after :

CREATE TABLE test_double2(
f1 DOUBLE
);
INSERT INTO test_double2
VALUES(0.47),(0.44),(0.19); # It was originally 1.1 Now it is 1.0999999999999999
mysql> SELECT SUM(f1)
-> FROM test_double2;
+--------------------+
| SUM(f1) |
+--------------------+
| 1.0999999999999999 |
+--------------------+
1 row in set (0.00 sec)
mysql> SELECT SUM(f1) = 1.1,1.1 = 1.1
-> FROM test_double2;
+---------------+-----------+
| SUM(f1) = 1.1 | 1.1 = 1.1 |
+---------------+-----------+
| 0 | 1 |
+---------------+-----------+
1 row in set (0.00 sec)

The result is 1.0999999999999999. Did you see? ? Although the error is small , But there are errors . You can also try Change the data type to FLOAT, Then run the summation query , Get is , 1.0999999940395355. obviously , The error is even greater .

that , Why is there such an error ? The problem is MySQL The storage method of floating-point data .

MySQL use 4 Byte store FLOAT Type data , use 8 Bytes to store DOUBLE Type data . No matter which , They are stored in binary mode . such as 9.625, Express in binary , Namely 1001.101, Or expressed as 1.001101×2^3. If the mantissa is not 0 or 5( such as 9.624), You can't use a binary number to express . , in turn, , It has to be rounded within the allowable range .

In programming , If floating-point numbers are used , Pay special attention to the error ,** Because floating point numbers are inaccurate , So we should avoid using “=” To determine whether two numbers are equal .** meanwhile , In some projects that require high accuracy , Never use floating point numbers , Otherwise, the result will be wrong , Even cause irreparable losses . that ,MySQL Is there a precise data type ? Of course. , This is the fixed-point number type DECIMAL.

4. Fixed point number type

4.1 Type introduction

  • MySQL The fixed-point number type in is only DECIMAL One type .

    data type Number of bytes meaning
    DECIMAL(M,D),DEC,NUMERIC M+2 byte The valid range consists of M and D decision

    Use DECIMAL(M,D) High precision decimals are represented in the form of . among ,M It is called precision ,D It's called scaling .0<=M<=65,0<=D<=30,D<M. for example , Definition DECIMAL(5,2) The type of , Indicates that the value range of this column is -999.99~999.99.

  • DECIMAL(M,D) The maximum value range of is the same as DOUBLE Same type , But the valid data range is determined by M and D Decisive .DECIMAL The storage space is not fixed , By the precision value M decision , The total storage space occupied is M+2 Bytes . in other words , In some scenes with low accuracy requirements , Compared with the fixed-point number occupying the same byte length , The range of values expressed by floating-point numbers can be larger .

  • The fixed point number is in MySQL The interior is made up of character string Storage in the form of , This determines that it must be accurate .

  • When DECIMAL When the type does not specify precision and scale , His tacit view is that DECIMAL(10,0). When the precision of the data exceeds the precision range of the fixed-point number type , be MySQL Rounding is also performed .

  • Floating point numbers vs Fixed-point number

    • The advantage of floating-point numbers over fixed-point numbers is that when the length is fixed , Floating point type has a wide range of values , But it's not accurate , It is suitable for large range of values , It can also tolerate small errors in scientific computing scenarios ( Like computational chemistry 、 Molecular modeling 、 Fluid dynamics, etc )
    • The value range of fixed-point number type is relatively small , But accuracy , There is no error , It is suitable for scenes with high precision requirements ( For example, scenarios involving amount calculation )
  • give an example

    # Unlike integer and floating point types ,mysql8.0 The range will also be displayed, such as (5,2)
    CREATE TABLE test_decimal1(
    f1 DECIMAL,
    f2 DECIMAL(5,2)
    );
    DESC test_decimal1;
    INSERT INTO test_decimal1(f1,f2)
    VALUES(123.123,123.456);# The decimal places exceed the specified range and are rounded off 
    #Out of range value for column 'f2' at row 1
    INSERT INTO test_decimal1(f2)
    VALUES(1234.34); # An error is reported when the integer bit exceeds the specified range 
    
    mysql> SELECT * FROM test_decimal1;
    +------+--------+
    | f1 | f2 |
    +------+--------+
    | 123 | 123.46 |
    +------+--------+
    1 row in set (0.00 sec)
    
  • give an example

    Let's run the following statement , hold test_double2 Fields in the table “f1” The data type of is changed to DECIMAL(5,2):

    ALTER TABLE test_double2
    MODIFY f1 DECIMAL(5,2);
    

    then , Let's run the summation statement again :

    mysql> SELECT SUM(f1)
    -> FROM test_double2; # The calculation is accurate 
    +---------+
    | SUM(f1) |
    +---------+
    | 1.10 |
    +---------+
    1 row in set (0.00 sec)
    
    mysql> SELECT SUM(f1) = 1.1
    -> FROM test_double2;
    +---------------+
    | SUM(f1) = 1.1 |
    +---------------+
    | 1 |
    +---------------+
    1 row in set (0.00 sec)
    

4.2 Experience in development

“ because DECIMAL Accuracy of data types , In our project , Except for a few ( Such as item number ) Use an integer type other than , The other values are DECIMAL, The reason is that the retail industry of this project , Accuracy is required , You can't miss a penny . ” —— From a project manager

5. A type of :BIT

BIT Type stores binary values , similar 010110.

Binary string type length Length range Occupancy space
BIT(M) M 1 <= M <= 64 about (M + 7)/8 Bytes

BIT type , If not specified (M), The default is 1 position . This 1 position , Indicates that only 1 Binary value of bit . here (M) Is the number of bits representing binary , The minimum number of digits is 1, The maximum value is 64.

CREATE TABLE test_bit1(
f1 BIT,
f2 BIT(5),
f3 BIT(64)
);
INSERT INTO test_bit1(f1)
VALUES(1);
#Data too long for column 'f1' at row 1
INSERT INTO test_bit1(f1)
VALUES(2);
INSERT INTO test_bit1(f2)
VALUES(23);

Be careful : In the BIT When inserting data into a field of type , Be sure to insert data in BIT Within the scope of type support .

Use SELECT When the command queries a bit field , It can be used BIN() or HEX() Function to read .

mysql> SELECT * FROM test_bit1;
+------------+------------+------------+
| f1 | f2 | f3 |
+------------+------------+------------+
| 0x01 | NULL | NULL |
| NULL | 0x17 | NULL |
+------------+------------+------------+
2 rows in set (0.00 sec)
mysql> SELECT BIN(f2),HEX(f2)
-> FROM test_bit1;
+---------+---------+
| BIN(f2) | HEX(f2) |
+---------+---------+
| NULL | NULL |
| 10111 | 17 |
+---------+---------+
2 rows in set (0.00 sec)
mysql> SELECT f2 + 0 # here +0 in the future , Data can be displayed in decimal 
-> FROM test_bit1;
+--------+
| f2 + 0 |
+--------+
| NULL |
| 23 |
+--------+
2 rows in set (0.00 sec)

You can see , Use b+0 When querying data , The decimal value can be directly queried .

6. Date and time type

Date and time are important information , In our system , Almost all data sheets can be used . The reason is that the customer needs to know the time stamp of the data , So as to query the data 、 Statistics and processing .

MySQL There are many data types that represent dates and times , Different versions may be different ,MySQL8.0 The date and time types supported by the version mainly include :YEAR type 、TIME type 、DATE type 、DATETIME The type and TIMESTAMP type .

  • YEAR Type is usually used to represent year
  • DATE Type is usually used to represent year 、 month 、 Japan
  • TIME Types are often used to indicate when 、 branch 、 second
  • DATETIME Type is usually used to represent year 、 month 、 Japan 、 when 、 branch 、 second
  • TIMESTAMP Type is usually used to represent a year with a time zone 、 month 、 Japan 、 when 、 branch 、 second
type name byte Date format minimum value Maximum
YEAR year 1 YYYY or YY 1901 2155
TIME Time 3 HH:MM:SS -838:59:59 838:59:59
DATE date 3 YYYY-MM-DD 1000-01-01 9999-12-03
DATETIME Date time 8 YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 9999-12-31 23:59:59
TIMESTAMP Date time 4 YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:00 UTC 2038-01-19 03:14:07UTC

You can see , Different data types represent different time contents 、 The value range is different , And the number of bytes occupied is also different , You should choose flexibly according to your actual needs .

Why time type TIME The value range of is not -23:59:59~23:59:59 Well ? as a result of MySQL The design of the TIME type , It doesn't just mean the time of the day , And it can be used to represent a time interval , This interval can exceed 24 Hours .

6.1 YEAR type

YEAR Type is used to indicate year , Of all date time types, the storage space occupied is the smallest , It only needs 1 Bytes Storage space .

stay MySQL in ,YEAR There are several storage formats :

  • With 4 Bit string or number format to represent YEAR type , The format for YYYY, The minimum value is 1901 year , The maximum value is 2155 year .
  • With 2 Bit string format represents YEAR type , The minimum value is 00, The maximum value is 99.
    • When the value is 01 To 69 when , Express 2001 To 2069;
    • When the value is 70 To 99 when , Express 1970 To 1999;
    • When taking an integer 0 or 00 Add words , So 0000 year ;
    • When the value is date / A string of ’0’ Add words , yes 2000 year .

from MySQL5.5.27 Start ,2 Bit format YEAR Not recommended anymore .YEAR The default format is “YYYY”, There is no need to write YEAR(4), from MySQL 8.0.19 Start , Specifying the display width is not recommended YEAR(4) data type .

CREATE TABLE test_year(
f1 YEAR,
f2 YEAR(4)
);
mysql> DESC test_year;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| f1 | year(4) | YES | | NULL | |
| f2 | year(4) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
INSERT INTO test_year
VALUES('2020','2021');# If you don't add quotation marks, you won't report an error , There will be an implicit transformation , It is recommended to add .
mysql> SELECT * FROM test_year;
+------+------+
| f1 | f2 |
+------+------+
| 2020 | 2021 |
+------+------+
1 rows in set (0.00 sec)
INSERT INTO test_year
VALUES('45','71');
INSERT INTO test_year
VALUES(0,'0');
mysql> SELECT * FROM test_year;
+------+------+
| f1 | f2 |
+------+------+
| 2020 | 2021 |
| 2045 | 1971 |
| 0000 | 2000 |
+------+------+
3 rows in set (0.00 sec)

6.2 DATE type

DATE Type indicates date , There is no time part , The format is YYYY-MM-DD, among ,YYYY Indicates the year ,MM Represents the month ,DD Indicates the date . need 3 Bytes Storage space . In the DATE When inserting data into a field of type , It also needs to meet certain format conditions .

  • With YYYY-MM-DD Format or YYYYMMDD String date in format , The minimum value is 1000-01-01, The maximum value is 9999-12-03.YYYYMMDD The format will be converted to YYYY-MM-DD Format .
  • With YY-MM-DD Format or YYMMDD String date in format , In this format , The year is a two digit value or a string that satisfies YEAR The format condition of type is : When the year is 00 To 69 when , Will be converted to 2000 To 2069; When the year is 70 To 99 when , Will be converted to 1970 To 1999.
  • Use CURRENT_DATE() perhaps NOW() function , The date of the current system will be inserted .

give an example :

Create data table , The table contains only one DATE Type field f1.

CREATE TABLE test_date1(
f1 DATE
);
Query OK, 0 rows affected (0.13 sec)

insert data :

INSERT INTO test_date1
VALUES ('2020-10-01'), ('20201001'),(20201001);
INSERT INTO test_date1
VALUES ('00-01-01'), ('000101'), ('69-10-01'), ('691001'), ('70-01-01'), ('700101'), ('99-01-01'), ('990101');
INSERT INTO test_date1
VALUES (000301), (690301), (700301), (990301);
INSERT INTO test_date1
VALUES (CURRENT_DATE()), (NOW());
SELECT *
FROM test_date1;

6.3 TIME type

TIME Type is used to represent time , The date part is not included . stay MySQL in , need 3 Bytes To store TIME Data of type , have access to “HH:MM:SS” Format to express TIME type , among ,HH For hours ,MM Represents minutes ,SS For seconds .

stay MySQL in , towards TIME When inserting data into a field of type , Several different formats can also be used .
(1) You can use a string with a colon , such as ’D HH:MM:SS'、’HH:MM:SS’、’HH:MM’、’D HH:MM’、’D HH‘ or ’SS‘ Format , Can be inserted correctly TIME Type field . among D Represents days , Its minimum value is 0, The maximum value is 34. If using with D Insert a string in the format TIME When fields of type ,D Will be converted into hours , The calculation format is D*24+HH. When using with colon and without D When the string represents time , Indicates the time of the day , such as 12:10 Express 12:10:00, instead of 00:12:10.
(2) You can use strings or numbers without colons , The format is ’HHMMSS' perhaps HHMMSS. If you insert an illegal string or number ,MySQL When storing data , Will automatically convert it to 00:00:00 For storage . such as 1210,MySQL The rightmost two digits will be parsed into seconds , Express 00:12:10, instead of 12:10:00.
(3) Use CURRENT_TIME() perhaps NOW(), The time of the current system will be inserted .

give an example :

Create data table , The table contains a TIME Type field f1.

CREATE TABLE test_time1(
f1 TIME
);
Query OK, 0 rows affected (0.02 sec)
INSERT INTO test_time1
VALUES('2 12:30:29'), ('12:35:29'), ('12:40'), ('2 12:40'),('1 05'), ('45');
INSERT INTO test_time1
VALUES ('123520'), (124011),(1210);
INSERT INTO test_time1
VALUES (NOW()), (CURRENT_TIME());
SELECT * FROM test_time1;

6.4 DATETIME type

DATETIME Type takes up the largest storage space among all date time types , All in all 8 Bytes of storage space . The format is DATE The type and TIME A combination of types , It can be expressed as YYYY-MM-DD HH:MM:SS, among YYYY Indicates the year ,MM Represents the month ,DD Indicates the date ,HH For hours ,MM Represents minutes ,SS For seconds .

In the DATETIME When inserting data into a field of type , It also needs to meet certain format conditions .

  • With YYYY-MM-DD HH:MM:SS Format or YYYYMMDDHHMMSS Insert a string in the format DATETIME When fields of type , The minimum value is 1000-01-01 00:00:00, The maximum value is 9999-12-03 23:59:59.
    • With YYYYMMDDHHMMSS Insert numbers in the format DATETIME When fields of type , Will be converted to YYYY-MM-DD HH:MM:SS Format .
  • With YY-MM-DD HH:MM:SS Format or YYMMDDHHMMSS Insert a string in the format DATETIME When fields of type , The two digit year rule complies with YEAR Type of rules ,00 To 69 Express 2000 To 2069;70 To 99 Express 1970 To 1999.
  • Using functions CURRENT_TIMESTAMP() and NOW(), Can be directed to DATETIME The field of type is inserted into the current date and time of the system .

give an example :

Create data table , The table contains a DATETIME Type field dt.

CREATE TABLE test_datetime1(
dt DATETIME
);
Query OK, 0 rows affected (0.02 sec)

insert data :

INSERT INTO test_datetime1
VALUES ('2021-01-01 06:50:30'), ('20210101065030');
INSERT INTO test_datetime1
VALUES ('99-01-01 00:00:00'), ('990101000000'), ('20-01-01 00:00:00'), ('200101000000');
INSERT INTO test_datetime1
VALUES (20200101000000), (200101000000), (19990101000000), (990101000000);
INSERT INTO test_datetime1
VALUES (CURRENT_TIMESTAMP()), (NOW());

6.5 TIMESTAMP type

TIMESTAMP Type can also represent date time , Its display format is the same as DATETIME The same type , All are YYYY-MM-DD HH:MM:SS, need 4 Bytes of storage space . however TIMESTAMP The stored time range is larger than DATETIME It's a lot smaller , Can only store “1970-01-01 00:00:01 UTC” To “2038-01-19 03:14:07 UTC” Time between . among ,UTC Represents the world unified time , Also known as world standard time .

  • When storing data, you need to convert the time zone of the current time , When querying data, convert the time back to the current time zone . therefore , Use TIMESTAMP The same time value stored , Different time zones will be displayed when querying in different time zones .

towards TIMESTAMP When inserting data into a field of type , When the inserted data format meets YY-MM-DD HH:MM:SS and YYMMDDHHMMSS when , The year of two digit values also conforms to YEAR Type of rule condition , It's just that the time range is much smaller .

If to TIMESTAMP The insertion time of a field of type exceeds TIMESTAMP The range of types , be MySQL Will throw an error message .

give an example :

Create data table , The table contains a TIMESTAMP Type field ts.

CREATE TABLE test_timestamp1(
ts TIMESTAMP
);

insert data :

INSERT INTO test_timestamp1
VALUES ('1999-01-01 03:04:50'), ('19990101030405'), ('99-01-01 03:04:05'), ('990101030405');
INSERT INTO test_timestamp1
VALUES ('[email protected]@[email protected]@[email protected]'), ('[email protected]@[email protected]@[email protected]');
INSERT INTO test_timestamp1
VALUES (CURRENT_TIMESTAMP()), (NOW());
#Incorrect datetime value
INSERT INTO test_timestamp1
VALUES ('2038-01-20 03:14:07');

TIMESTAMP and DATETIME The difference between :

  • TIMESTAMP The storage space is small , The date and time range represented is also relatively small

  • The underlying storage methods are different ,TIMESTAMP The underlying storage is the millisecond value , distance 1970-1-1 0:0:0 0 The millisecond value of milliseconds .

  • When two dates are compared in size or date calculation ,TIMESTAMP More convenient 、 faster .

  • TIMESTAMP It's about time zone .TIMESTAMP It will vary according to the user's time zone , Show different results . and DATETIME It can only reflect the local time zone at the time of insertion , People in other time zones are bound to have errors when viewing data .

    CREATE TABLE temp_time(
    d1 DATETIME,
    d2 TIMESTAMP
    );
    
    INSERT INTO temp_time VALUES('2021-9-2 14:45:52','2021-9-2 14:45:52');
    INSERT INTO temp_time VALUES(NOW(),NOW());
    
    mysql> SELECT * FROM temp_time;
    +---------------------+---------------------+
    | d1 | d2 |
    +---------------------+---------------------+
    | 2021-09-02 14:45:52 | 2021-09-02 14:45:52 |
    | 2021-11-03 17:38:17 | 2021-11-03 17:38:17 |
    +---------------------+---------------------+
    2 rows in set (0.00 sec)
    
    # Modify the current time zone 
    SET time_zone = '+9:00';
    
    mysql> SELECT * FROM temp_time;
    +---------------------+---------------------+
    | d1 | d2 |
    +---------------------+---------------------+
    | 2021-09-02 14:45:52 | 2021-09-02 15:45:52 |
    | 2021-11-03 17:38:17 | 2021-11-03 18:38:17 |
    +---------------------+---------------------+
    2 rows in set (0.00 sec)
    

6.6 Experience in development

The most used date time type , Namely DATETIME. although MySQL Also support YEAR( year )、 TIME( Time )、DATE( date ), as well as TIMESTAMP type , But in the actual project , As far as possible with DATETIME type . Because this data type includes complete date and time information , The range of values is also the largest , Easy to use . After all , If the date and time information is scattered in several fields , It's not easy to remember , And when you query ,SQL Statements will also be more complex .

Besides , General deposit and registration time 、 Product release time, etc , Not recommended DATETIME Storage , But use Time stamp , because DATETIME Although intuitive , But it's not easy to calculate .

mysql> SELECT UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
| 1635932762 |
+------------------+
1 row in set (0.00 sec)

7. Text string type

In the actual project , We also often encounter a kind of data , String data .

MySQL in , Text strings are generally divided into CHARVARCHARTINYTEXTTEXTMEDIUMTEXTLONGTEXTENUMSET Other types .

 Insert picture description here

7.1 CHAR And VARCHAR type

CHAR and VARCHAR Types can store shorter strings .

character string ( Text ) type characteristic length Length range Occupied storage space
CHAR(M) Fixed length M 0 <= M <= 255 M Bytes
VARCHAR(M) Variable length M 0 <= M <= 65535 Bytes ( Actual length + 1) Bytes

CHAR type :

  • CHAR(M) Type generally requires a predefined string length . If you don't specify (M), The default length is 1 Characters .
  • If you save , The actual length ratio of the data CHAR The length of the type declaration is small , Will be in Right side filling Space to reach the specified length . When MySQL retrieval CHAR Type of data ,CHAR Fields of type remove trailing spaces . Error if out of range .
  • Definition CHAR Type field , The declared field length is CHAR Number of bytes of storage space occupied by type field .
CREATE TABLE test_char1(
c1 CHAR, # One character by default 
c2 CHAR(5)
);
DESC test_char1;
INSERT INTO test_char1
VALUES('a','Tom');
SELECT c1,CONCAT(c2,'***') FROM test_char1;
INSERT INTO test_char1(c2)
VALUES('a ');
SELECT CHAR_LENGTH(c2)
FROM test_char1;

VARCHAR type :

  • VARCHAR(M) Definition time , Must specify length M, Otherwise, the report will be wrong .
  • MySQL4.0 Version below ,varchar(20): refer to 20 byte , If stored UTF8 Chinese character time , Can only save 6 individual ( Every Chinese character 3 byte ) ;MySQL5.0 Above version ,varchar(20): refer to 20 character .
  • retrieval VARCHAR Type of field data , The space at the end of the data will be retained .VARCHAR The storage space occupied by the field of type is the actual length of the string plus 1 Bytes .
CREATE TABLE test_varchar1(
NAME VARCHAR # error 
);
#Column length too big for column 'NAME' (max = 21845);
CREATE TABLE test_varchar2(
NAME VARCHAR(65535) # error 
);
CREATE TABLE test_varchar3(
NAME VARCHAR(5)
);
INSERT INTO test_varchar3
VALUES(' Silicon Valley '),(' Silicon Valley education ');
#Data too long for column 'NAME' at row 1
INSERT INTO test_varchar3
VALUES(' Silicon Valley IT education ');

In which cases CHAR or VARCHAR Better

type characteristic In space On the time Applicable scenario
CHAR(M) Fixed length Waste storage space Efficient Not much storage , High speed requirement
VARCHAR(M) Variable length Save storage space Low efficiency Not CHAR The situation of

situation 1: Store very short messages . Like the house number 101,201…… Such a short message should be char, because varchar And take a byte Used to store the length of information , Originally intended to save storage , The result is not worth the loss .

situation 2: Fixed length . For example, use uuid A primary key , The use char It should be more appropriate . Because he fixed the length ,varchar The dynamics disappear according to the characteristics of length , And it takes a length message .

situation 3: It changes very frequently column. because varchar Every time you store it, there's extra computation , And so on , If a very frequent change , It takes a lot of energy to calculate , And these char I don't need it for you .

situation 4: Specific conditions in the storage engine :

  • MyISAM Data storage engine and data columns :MyISAM Data sheet , It is better to use fixed length (CHAR) Instead of variable length (VARCHAR) Data columns for . This makes the entire table static , So that Data retrieval is faster , Trade space for time .

  • MEMORY Storage engine and data columns :MEMORY Data tables currently use fixed length data row storage , So whatever you use CHAR or VARCHAR It doesn't matter , Both are for CHAR Type processed .

  • InnoDB Storage engine , It is recommended to use VARCHAR type . Because for InnoDB Data sheet , The internal row storage format does not distinguish between fixed length and variable length columns ( All data rows use header pointers to data column values ), and The main factor affecting performance is the total amount of storage used by data rows , because char Take up more space on average than varchar, So except for short and fixed length , Other considerations varchar. This saves space , Diskette I/O And the total amount of data storage is better .

7.2 TEXT type

stay MySQL in ,TEXT A string used to hold text types , A total of 4 Types , Respectively TINYTEXT、TEXT、MEDIUMTEXT and LONGTEXT type .

In the TEXT Type of field when saving and querying data , The system automatically stores the data according to the actual length , There's no need to predefine the length . This and VARCHAR The same type .

Each of these TEXT The data length saved by the type is different from the storage space occupied , as follows :

Text string type characteristic length Length range Occupied storage space
TINYTEXT Small text 、 Variable length L 0 <= L <= 255 L + 2 Bytes
TEXT Text 、 Variable length L 0 <= L <= 65535 L + 2 Bytes
MEDIUMTEXT Medium text 、 Variable length L 0 <= L <= 16777215 L + 3 Bytes
LONGTEXT Large text 、 Variable length L 0 <= L<= 4294967295( amount to 4GB) L + 4 Bytes

Because the length of the actual storage is uncertain ,MySQL Don't allow TEXT Type fields as primary keys . In this case , You can only use CHAR(M), perhaps VARCHAR(M).

give an example :

Create data table :

CREATE TABLE test_text(
tx TEXT
);
INSERT INTO test_text
VALUES('atguigu ');
SELECT CHAR_LENGTH(tx)
FROM test_text; #10 Spaces will not be deleted 

When saving and querying data , It didn't delete TEXT The space at the end of the data of type .

Experience in development :

TEXT Text type , You can save large text segments , The search speed is a little slow , So if it's not particularly big , It is recommended to use CHAR,VARCHAR Instead of . also TEXT Type without default value , It's no use adding it . and text and blob Types of data are easy to be deleted “ empty ”, Make the file fragment more , Therefore, frequently used tables are not recommended TEXT The type field , It is recommended to separate out , Use a separate table .

8. ENUM type

ENUM Types are also called enumeration types ,ENUM The value range of type needs to be specified when defining the field . When setting field values ,ENUM Type allows only a single value to be selected from a member , You cannot select more than one value at a time .

The required storage space is defined by ENUM Type is determined by the number of members specified .

Text string type length Length range Occupied storage space
ENUM L 1 <= L <= 65535 1 or 2 Bytes
  • When ENUM The type contains 1~255 When a member , need 1 Bytes of storage space ;

  • When ENUM The type contains 256~65535 When a member , need 2 Bytes of storage space .

  • ENUM The maximum number of members of type is 65535 individual .

give an example :

Create the following table :

CREATE TABLE test_enum(
season ENUM(' In the spring ',' In the summer ',' autumn ',' In the winter ','unknow') # Choose one more 
);

Add data :

INSERT INTO test_enum
VALUES(' In the spring '),(' autumn ');
# Ignore case 
INSERT INTO test_enum
VALUES('UNKNOW');
# Allows you to obtain the enumeration value of the specified index position in the form of a corner sign 
INSERT INTO test_enum
VALUES('1'),(3);
# Data truncated for column 'season' at row 1
INSERT INTO test_enum
VALUES('ab');
# When ENUM A field of type is not declared as NOT NULL when , Insert NULL It also works 
INSERT INTO test_enum
VALUES(NULL);

9. SET type

SET Represents a string object , Can contain 0 One or more members , But the maximum number of members is 64. When setting field values , You can take... Within the value range 0 One or more values .

When SET Type contains different number of members , The storage space it occupies is also different , As follows :

Number range of members (L Indicates the actual number of members ) Occupied storage space
1 <= L <= 8 1 Bytes
9 <= L <= 16 2 Bytes
17 <= L <= 24 3 Bytes
25 <= L <= 32 4 Bytes
33 <= L <= 64 8 Bytes

SET The more members a type has when storing data , The larger the storage space it takes . Be careful :SET Type when selecting members , You can select more than one member at a time , This is related to ENUM Different types .

give an example :

Create table :

CREATE TABLE test_set(
s SET ('A', 'B', 'C') # Support multiple choices 
);

Insert data into table :

INSERT INTO test_set (s) VALUES ('A'), ('A,B');
# Insert repeated SET Type member ,MySQL Duplicate members are automatically deleted 
INSERT INTO test_set (s) VALUES ('A,B,C,A');
# towards SET Type of field insert SET When a value does not exist in a member ,MySQL Will throw an error .
INSERT INTO test_set (s) VALUES ('A,B,C,D');
SELECT *
FROM test_set;

give an example :

CREATE TABLE temp_mul(
gender ENUM(' male ',' Woman '),
hobby SET(' having dinner ',' sleep ',' Doudou ',' Write code ')
);
INSERT INTO temp_mul VALUES(' male ',' sleep , Doudou '); # success 
# Data truncated for column 'gender' at row 1
INSERT INTO temp_mul VALUES(' male , Woman ',' sleep , Write code '); # Failure 
# Data truncated for column 'gender' at row 1
INSERT INTO temp_mul VALUES(' demon ',' sleep , Write code ');# Failure 
INSERT INTO temp_mul VALUES(' male ',' sleep , Write code , having dinner '); # success 

10. Binary string type

MySQL The binary string type in mainly stores some binary data , For example, you can store pictures 、 Binary data such as audio and video .

MySQL Binary string types supported in mainly include BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB and LONGBLOB type .

BINARY And VARBINARY type

BINARY and VARBINARY Be similar to CHAR and VARCHAR, They just store binary strings .

BINARY (M) Is a fixed length binary string ,M Indicates the maximum number of bytes that can be stored , The value range is 0~255 Characters . If not specified (M), Indicates that only 1 Bytes . for example BINARY (8), Indicates that it can store up to 8 Bytes , If the field value is insufficient (M) Bytes , Will be filled in on the right ’\0’ To supplement the specified length .

VARBINARY (M) Is a variable length binary string ,M Indicates the maximum number of bytes that can be stored , The total number of bytes cannot exceed the byte length limit of the line 65535, Also consider extra byte overhead ,VARBINARY Type of data in addition to storing the data itself , It also needs to be 1 or 2 Number of bytes to store data .VARBINARY type Must specify (M), Otherwise, the report will be wrong .

Binary string type characteristic Length of value Occupancy space
BINARY(M) Fixed length M (0 <= M <= 255) M Bytes
VARBINARY(M) Variable length M(0 <= M <= 65535) M+1 Bytes

give an example :

Create table :

CREATE TABLE test_binary1(
f1 BINARY,
f2 BINARY(3),
# f3 VARBINARY,
f4 VARBINARY(10)
);

Add data :

INSERT INTO test_binary1(f1,f2)
VALUES('a','a');
INSERT INTO test_binary1(f1,f2)
VALUES(' still ',' still ');# Failure 
INSERT INTO test_binary1(f2,f4)
VALUES('ab','ab');
mysql> SELECT LENGTH(f2),LENGTH(f4)
-> FROM test_binary1;
+------------+------------+
| LENGTH(f2) | LENGTH(f4) |
+------------+------------+
| 3 | NULL |
| 3 | 2 |
+------------+------------+
2 rows in set (0.00 sec)

BLOB type

BLOB It's a Binary big object , Can hold a variable amount of data .

MySQL Medium BLOB Types include TINYBLOB、BLOB、MEDIUMBLOB and LONGBLOB 4 Types , The maximum length they can accommodate values is different . Can store a large binary object , such as picture Audio and video etc. .

It should be noted that , In practice , Often not in MySQL Use in the database BLOB Type stores large object data , Usually the picture 、 Audio and video files are stored in On the server's disk , And put the picture 、 The access paths of audio and video are stored in MySQL in .

Binary string type Length of value Length range Occupancy space
TINYBLOB L 0 <= L <= 255 L + 1 Bytes
BLOB L 0 <= L <= 65535( amount to 64KB) L + 2 Bytes
MEDIUMBLOB L 0 <= L <= 16777215 ( amount to 16MB) L + 3 Bytes
LONGBLOB L 0 <= L <= 4294967295( amount to 4GB) L + 4 Bytes

give an example :

CREATE TABLE test_blob1(
id INT,
img MEDIUMBLOB
);

TEXT and BLOB Precautions for use of :

In the use of text and blob Pay attention to the following points when field type , In order to better play the database performance .

① BLOB and TEXT Value also causes some problems of its own , Especially when there are a lot of delete or update operations . Deleting this value leaves a lot of... In the data table " empty ", Fill in these later " empty " The length of the records may be different . To improve performance , It is recommended to use... Regularly OPTIMIZE TABLE This function can be used to modify this kind of table Defragmentation .

② If you need to fuzzy query large text fields ,MySQL Provides Prefix index . But it's still important to avoid retrieving large BLOB or TEXT value . for example ,SELECT * Query is not a good idea , Unless you can be sure of WHERE Clause will only find the required data row . otherwise , You may be able to transfer a large number of values on the network without any destination .

③ hold BLOB or TEXT Column Separate into separate tables in . In some circumstances , If you move these data columns to the second data table , It allows you to convert data columns in the original data table into fixed length data row format , So it makes sense . at present Reduce fragmentation in the main table , Give you the performance advantage of fixed length data rows . It also allows you to run... On the main data table SELECT * When querying, it will not transmit a large number of BLOB or TEXT value .

11. JSON type

JSON(JavaScript Object Notation) It's a lightweight Data exchange format . A simple and clear hierarchy makes JSON Become the ideal data exchange language . It is easy to read and write , At the same time, it is also easy for machine analysis and generation , And effectively improve the network transmission efficiency .JSON Can be JavaScript Object into a string , Then you can easily pass this string between networks or programs , And restore it to the data format supported by each programming language when necessary .

stay MySQL 5.7 in , They have already supported JSON data type . stay MySQL 8.x In the version ,JSON Types provide a way for automatic validation JSON Documents and optimized storage structure , Make in MySQL Storage and reading in JSON Types of data are more convenient and efficient .
Create data table , The table contains a JSON Type field js .

CREATE TABLE test_json(
js json
);

Insert... Into the table JSON data .

INSERT INTO test_json (js)
VALUES ('{"name":"songhk", "age":18, "address":{"province":"beijing", "city":"beijing"}}');

Inquire about t19 Table data .

mysql> SELECT *
-> FROM test_json;

 Insert picture description here

When you need to retrieve JSON When a specific value of data in a field of type , have access to “->” and “->>” Symbol .

mysql> SELECT js -> '$.name' AS NAME,js -> '$.age' AS age ,js -> '$.address.province' AS province, js -> '$.address.city' AS city
-> FROM test_json;
+----------+------+-----------+-----------+
| NAME | age | province | city |
+----------+------+-----------+-----------+
| "songhk" | 18 | "beijing" | "beijing" |
+----------+------+-----------+-----------+
1 row in set (0.00 sec)

adopt “->” and “->>” Symbol , from JSON The specified... Is correctly queried in the field JSON The value of the data .

12. Type of space

MySQL Spatial type extension supports the generation of geographical features 、 Storage and Analysis . The geographical features here represent anything with a location in the world , It can be an entity , For example, a mountain ; It can be space , For example, an office building ; It can also be a definable location , For example, a crossroads and so on .MySQL Use in Geometry( The geometric ) To represent all geographical features .Geometry Of a point or collection of points , Represents anything with a position in the world .

MySQL Spatial data type (Spatial Data Type) Corresponding to OpenGIS class , Including single value types :GEOMETRY、POINT、LINESTRING、POLYGON And set type :MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION .

  • Geometry Is the base class of all spatial collection types , Other types are POINT、LINESTRING、POLYGON All are Geometry Subclasses of .
    • Point, As the name suggests, it's a point , There is a coordinate value . for example POINT(121.213342 31.234532),POINT(30 10), Coordinate values support DECIMAL type , longitude (longitude) before , dimension (latitude) After , Separate... With spaces .
    • LineString, Line , Connected by a series of points . If the line does not cross from beginning to end , That's simple (simple); If the start and end points overlap , That's closed (closed). for example LINESTRING(30 10,10 30,40 40), Points are separated by commas , The longitude and latitude in a point are separated by spaces , And POINT The format is consistent .
    • Polygon, polygon . It can be a solid plane , That is, there is no internal boundary , There can also be holes , Similar button . The simplest is the case where there is only one outer boundary , for example POLYGON((0 0,10 0,10 10, 0 10)).

Several common geometric elements are shown below :

 Insert picture description here

  • MultiPoint、MultiLineString、MultiPolygon、GeometryCollection this 4 Both types are collection classes , There are many Point、LineString or Polygon It's a combination of .

The following shows a combination of multiple geometric elements of the same or different kind :

 Insert picture description here

13. Summary and selection suggestions

When defining data types , If it is Integers , Just use INT; If it is decimal , Be sure to use the fixed-point number type DECIMAL(M,D); If it's a date and time , Just use DATETIME.

The advantage of this is , First, make sure your system doesn't make mistakes because of data type definition . however , Everything has two sides , Good reliability , It doesn't mean efficient . such as ,TEXT Although easy to use , But not as efficient as CHAR(M) and VARCHAR(M).

About the choice of string , It is recommended to refer to Alibaba's 《Java Development Manual 》 standard :

Alibaba 《Java Development Manual 》 And MySQL database :

  • If any field is non negative , Must be UNSIGNED
  • mandatory 】 Decimal type is DECIMAL, No use FLOAT and DOUBLE.
    • explain : At the time of storage ,FLOAT and DOUBLE There is a problem of precision loss , Most likely when comparing values , Get the wrong result . If the range of data stored exceeds DECIMAL The scope of the , It is recommended to divide the data into integers and decimals and store them separately .
  • mandatory 】 If the stored string length is almost equal , Use CHAR Fixed length string type .
  • mandatory 】VARCHAR Is a variable length string , Do not pre allocate storage space , Do not exceed the length 5000. If the storage length is greater than this value , Define field type as TEXT, Come up with a table , Match with primary key , Avoid affecting the index efficiency of other fields .

The first 13 Chapter _ constraint

lecturer : Silicon Valley - Song Hongkang ( Jianghu people : a brand of instant noodles )

Official website :http://www.atguigu.com


1. constraint (constraint) summary

1.1 Why do we need constraints

Data integrity (Data Integrity) The accuracy of data (Accuracy) And reliability (Reliability). It is proposed to prevent the existence of data that does not comply with the semantic provisions in the database and to prevent invalid operation or error information caused by the input and output of error information .

In order to ensure the integrity of the data ,SQL The specification restricts Additional conditional restrictions on table data . Consider from the following four aspects :

  • Entity integrity (Entity Integrity): for example , In the same table , There can't be two identical records that can't be distinguished
  • Domain integrity (Domain Integrity): for example : Age range 0-120, Gender range “ male / Woman ”
  • Citation integrity (Referential Integrity): for example : Employee's Department , Find this department in the Department list
  • User-defined integrity (User-defined Integrity): for example : Unique user name 、 The password cannot be empty , The salary of the manager of the Department shall not be higher than the average salary of the employees of the Department 5 times .

1.2 What are constraints

Constraints are mandatory at the table level .

Can be in Specify constraints when creating tables ( adopt CREATE TABLE sentence ), Or in After the table is created, you can use ALTER TABLE Statement specifies a constraint .

1.3 Classification of constraints

  • ** angle 1: According to the restrictions of constraint data columns ,** Constraints can be divided into :
    • Single column constraints : Each constraint constrains only one column
    • Multi column constraint : Each constraint can constrain multiple columns of data
  • angle 2: According to the scope of the constraint , Constraints can be divided into :
    • Column level constraints : Can only act on one column , Follow the definition of the column , Declare this constraint after the corresponding field
    • Table level constraints : All fields in the table are declared , Constraints declared after all fields
 Location Supported constraint types Whether the constraint name can be used
Column level constraints : After column Grammar supports , But foreign keys have no effect Can not be
Table level constraints : Below all columns Default and non empty do not support , Other support Sure ( The primary key has no effect )
  • angle 3: According to the role of constraints , Constraints can be divided into :
    • NOT NULL Non empty constraint , Specifies that a field cannot be empty
    • UNIQUE Unique constraint , Specify that a field is unique in the whole table
    • PRIMARY KEY Primary key ( Non empty and unique ) constraint
    • FOREIGN KEY Foreign key constraints
    • CHECK Check constraint
    • DEFAULT Default constraint

Be careful : MySQL I won't support it check constraint , But you can use check constraint , Without any effect

1.4 View the existing constraints of a table

#information_schema Database name ( System libraries )
#table_constraints The name of the table ( Store constraints for individual tables )
SELECT * FROM information_schema.table_constraints
WHERE table_name = ' The name of the table ';

 Insert picture description here

1.5 How to add / Delete constraints ?

CREATE TABLE # Add constraints when creating tables 
ALTER TABLE # Add constraints when modifying tables 、 Delete constraints 

2. Non empty constraint

2.1 effect

Limit a field / The value of a column cannot be empty

 Insert picture description here

2.2 keyword

NOT NULL

2.3 characteristic

  • Default , All types of values can be NULL, Include INT、FLOAT And so on

  • Non empty constraints can only appear on columns of table objects , Only one column can be qualified as non empty , Cannot combine non empty , Cannot be used in table level constraints

  • A table can have many columns, which are limited to non empty

  • An empty string ’' It's not equal to NULL,0 Also is not equal to NULL

2.4 Add non empty constraints

(1) Build table

CREATE TABLE The name of the table (
Field name data type ,
Field name data type NOT NULL,
Field name data type NOT NULL
);

give an example :

CREATE TABLE test1(
id INT NOT NULL,
last_name VARCHAR(15) NOT NULL,
email VARCHAR(25),
salary DECIMAL(10,2)
);
DESC test1;
# success 
INSERT INTO test1(id,last_name,email,salary)
VALUES(1,'Tom','[email protected]',3400);
# error :Column 'last_name' cannot be null
INSERT INTO test1(id,last_name,email,salary)
VALUES(2,NULL,'[email protected]',3400);
# error :Column 'id' cannot be null
INSERT INTO test1(id,last_name,email,salary)
VALUES(NULL,'Jerry','[email protected]',3400);
# error :Field 'last_name' doesn't have a default value
/* explain : When inserting data , Because the field without assignment is not set to be empty , He first looks for whether this field has a default value , If yes, use the default value , If there is no indication of The default value is assigned to null, Therefore, there is an error that there is no default value .*/
INSERT INTO test1(id,email)
VALUES(2,'[email protected]');
# error 
UPDATE test1
SET last_name = NULL
WHERE id = 1;
# success 
UPDATE test1
SET email = '[email protected]'
WHERE id = 1;

 Insert picture description here

(2) After the establishment of the table

alter table The name of the table modify Field name data type not null;

give an example :

# example 1: stay ALTER TABLE Add constraints when 
SELECT * FROM test1;
DESC test1;
# Premise is email The value of the field is not null, If this is null Will report a mistake , It needs to be modified first. It cannot be empty .
ALTER TABLE test1
MODIFY email VARCHAR(25) NOT NULL;
# example 2: stay ALTER TABLE Delete constraint when 
ALTER TABLE test1
MODIFY email VARCHAR(25) NULL;

2.5 Delete non empty constraints

alter table The name of the table modify Field name data type NULL;# Get rid of not null, It is equivalent to modifying a non annotation field , This field can be empty 
or
alter table The name of the table modify Field name data type ;# Get rid of not null, It is equivalent to modifying a non annotation field , This field can be empty 

give an example :

ALTER TABLE emp
MODIFY sex VARCHAR(30) NULL;
ALTER TABLE emp
MODIFY NAME VARCHAR(15) DEFAULT 'abc' NULL;

3. Uniqueness constraint

3.1 effect

Used to restrict a field / The value of a column cannot be repeated .

 Insert picture description here

3.2 keyword

UNIQUE

3.3 characteristic

  • The same table can have multiple unique constraints .
  • A unique constraint can be a unique value for a column , You can also combine multiple columns to have unique values .
  • The uniqueness constraint allows column values to be empty .
  • When creating unique constraints , If you don't name a unique constraint , The default is the same as the column name .
  • MySQL A unique index will be created by default for columns with unique constraints .

3.4 Add unique constraints

(1) Build table

create table The name of the table (
Field name data type ,
Field name data type unique,
Field name data type unique key, # Column constraints ,key It can be omitted 
Field name data type
);
create table The name of the table (
Field name data type ,
Field name data type ,
Field name data type ,
[constraint Constraint name ] unique key( Field name ) # Table constraints , Constraint name 
perhaps :
unique key( Field name ) # Cannot afford constraint name , Default and fields / Keep the column names consistent .
);

give an example :

create table student(
sid int,
sname varchar(20),
tel char(11) unique,
cardid char(18) unique key
);
CREATE TABLE t_course(
cid INT UNIQUE,
cname VARCHAR(100) UNIQUE,
description VARCHAR(200)
);
# stay CREATE TABLE Add constraints when 
# commonly id email The only thing that cannot be repeated , Suitable for addition 
CREATE TABLE test2(
id INT UNIQUE, # Column level constraints 
last_name VARCHAR(15) ,
email VARCHAR(25),
salary DECIMAL(10,2),
# Table level constraints , The effect is the same for one field , It's just that the writing position is different 
#uk_test2_email Constraint name : uk by unquie Abbreviation ,test2 To show ,email For field 
CONSTRAINT uk_test2_email UNIQUE(email)
);
DESC test2;

 Insert picture description here

SELECT * FROM information_schema.table_constraints
WHERE table_name = 'test2';
# When creating unique constraints , If you don't name a unique constraint , The default is the same as the column name .
# success 
INSERT INTO test2(id,last_name,email,salary)
VALUES(1,'Tom','[email protected]',4500);
# error :Duplicate entry '1' for key 'test2.id'
INSERT INTO test2(id,last_name,email,salary)
VALUES(1,'Tom1','[email protected]',4600);
# error :Duplicate entry '[email protected]' for key 'test2.uk_test2_email'
INSERT INTO test2(id,last_name,email,salary)
VALUES(2,'Tom1','[email protected]',4600);
# You can declare to unique Add... To the field of null value . And you can add more than once null
INSERT INTO test2(id,last_name,email,salary)
VALUES(2,'Tom1',NULL,4600);
# success :null Values can be added repeatedly 
INSERT INTO test2(id,last_name,email,salary)
VALUES(3,'Tom2',NULL,4600);
SELECT * FROM test2;

(2) Specify the only key constraint after creating the table

# If it is a field in the field list , Indicates that the value of this column is unique . If there are two or more fields , So the compound is unique , That is, the combination of multiple fields is unique 
# The way 1:
alter table The name of the table add unique key( Field list );
# The way 2:
alter table The name of the table modify Field name Field type unique;

give an example :

DESC test2;
UPDATE test2
SET salary = 5000
WHERE id = 3;
# The way 1:
ALTER TABLE test2
ADD CONSTRAINT uk_test2_sal UNIQUE(salary);
# The way 2:
ALTER TABLE test2
MODIFY last_name VARCHAR(15) UNIQUE;

3.5 About compound unique constraints

create table The name of the table (
Field name data type ,
Field name data type ,
Field name data type ,
unique key( Field list ) # Multiple field names are written in the field list , Multiple field names are separated by commas , It means that it is compound and unique , That is, the combination of multiple fields is unique 
);
CREATE TABLE USER(
id INT,
`name` VARCHAR(15),
`password` VARCHAR(25),
# Table level constraints 
CONSTRAINT uk_user_name_pwd UNIQUE(`name`,`password`)
);
INSERT INTO USER
VALUES(1,'Tom','abc');
# It can work : As long as the fields of constraints are not exactly the same 
INSERT INTO USER
VALUES(1,'Tom1','abc');
SELECT *
FROM USER;
# Student list 
create table student(
sid int, # Student number 
sname varchar(20), # full name 
tel char(11) unique key, # Telephone 
cardid char(18) unique key # ID number 
);
# The curriculum 
create table course(
cid int, # Course number 
cname varchar(20) # Course name 
);
# Course selection table 
create table student_course(
id int,
sid int,
cid int,
score int,
unique key(sid,cid) # Compound the only 
);
insert into student values(1,' Zhang San ','13710011002','101223199012015623');# success 
insert into student values(2,' Li Si ','13710011003','101223199012015624');# success 
insert into course values(1001,'Java'),(1002,'MySQL');# success 
mysql> select * from student;
+-----+-------+-------------+--------------------+
| sid | sname | tel | cardid |
+-----+-------+-------------+--------------------+
| 1 | Zhang San | 13710011002 | 101223199012015623 |
| 2 | Li Si | 13710011003 | 101223199012015624 |
+-----+-------+-------------+--------------------+
2 rows in set (0.00 sec)
mysql> select * from course;
+------+-------+
| cid | cname |
+------+-------+
| 1001 | Java |
| 1002 | MySQL |
+------+-------+
2 rows in set (0.00 sec)
insert into student_course values
(1, 1, 1001, 89),
(2, 1, 1002, 90),
(3, 2, 1001, 88),
(4, 2, 1002, 56);# success 
mysql> select * from student_course;
+----+------+------+-------+
| id | sid | cid | score |
+----+------+------+-------+
| 1 | 1 | 1001 | 89 |
| 2 | 1 | 1002 | 90 |
| 3 | 2 | 1001 | 88 |
| 4 | 2 | 1002 | 56 |
+----+------+------+-------+
4 rows in set (0.00 sec)
insert into student_course values (5, 1, 1001, 88);# Failure 
#ERROR 1062 (23000): Duplicate entry '1-1001' for key 'sid' In violation of the sid-cid The compound is unique 

3.5 Delete unique constraint

  • A unique index will also be automatically created on the column to which the uniqueness constraint is added .
  • Deleting a unique constraint can only be deleted by deleting a unique index .
  • You need to specify a unique index name when deleting , The unique index name is the same as the unique constraint name .
  • If you do not specify a name when creating a unique constraint , If it's a single column , The default is the same as the column name ; If it's a composite column , Then default and () The first column in the list has the same name . You can also customize the uniqueness constraint name .
SELECT * FROM information_schema.table_constraints WHERE table_name = ' Table name '; # See what constraints are 
SELECT * FROM information_schema.table_constraints
WHERE table_name = 'student_course';
SELECT * FROM information_schema.table_constraints
WHERE table_name = 'test2';
DESC test2;
# How to delete a unique index 
ALTER TABLE test2
DROP INDEX last_name;
ALTER TABLE test2
DROP INDEX uk_test2_sal;

Be careful : Can pass show index from The name of the table ; View the index of the table

4. PRIMARY KEY constraint

4.1 effect

Used to uniquely identify a row of records in the table .

4.2 keyword

primary key

4.3 characteristic

  • The primary key constraint is equivalent to Unique constraint + A combination of nonnull constraints , Primary key constraint columns are not allowed to be duplicate , Null values are not allowed .

 Insert picture description here

  • A table can have at most one primary key constraint , You can create a primary key constraint at the column level , You can also create... At the table level .

  • The primary key constraint corresponds to one or more columns in the table ( Composite primary key )

  • If it is a multi column composite primary key constraint , Then none of these columns are allowed to be null , And the combined value is not allowed to repeat .

  • MySQL The primary key name of is always PRIMARY, Even if you name the primary key constraint name, it's useless .

  • When creating a primary key constraint , By default, the system will establish a corresponding column on the column or column combination primary key ( That can be queried according to the primary key , Query according to the primary key , More efficient ). If the primary key constraint is deleted , The index corresponding to the primary key constraint is automatically deleted .

  • One thing to note is that , Do not modify the value of the primary key field . Because the primary key is the unique identification of the data record , If you modify the value of the primary key , It may destroy the integrity of the data .

4.4 Add primary key constraint

(1) Specify the primary key constraint when creating the table

create table The name of the table (
Field name data type primary key, # Column level mode 
Field name data type ,
Field name data type
);
create table The name of the table (
Field name data type ,
Field name data type ,
Field name data type ,
[constraint Constraint name ] primary key( Field name ) # Table level mode 
);

give an example :

create table temp(
id int primary key,
name varchar(20)
);
mysql> desc temp;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
insert into temp values(1,' Zhang San ');# success 
insert into temp values(2,' Li Si ');# success 
mysql> select * from temp;
+----+------+
| id | name |
+----+------+
| 1 | Zhang San |
| 2 | Li Si |
+----+------+
2 rows in set (0.00 sec)
insert into temp values(1,' Zhang San ');# Failure 
ERROR 1062 (23000): Duplicate( repeat ) entry( type , Input ) '1' for key 'PRIMARY'
insert into temp values(1,' Wang Wu ');# Failure 
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
insert into temp values(3,' Zhang San ');# success 
mysql> select * from temp;
+----+------+
| id | name |
+----+------+
| 1 | Zhang San |
| 2 | Li Si |
| 3 | Zhang San |
+----+------+
3 rows in set (0.00 sec)
insert into temp values(4,null);# success 
insert into temp values(null,' Li Qi ');# Failure 
ERROR 1048 (23000): Column 'id' cannot be null
mysql> select * from temp;
+----+------+
| id | name |
+----+------+
| 1 | Zhang San |
| 2 | Li Si |
| 3 | Zhang San |
| 4 | NULL |
+----+------+
4 rows in set (0.00 sec)
# Demonstrate how to establish two primary key constraints for a table 
create table temp(
id int primary key,
name varchar(20) primary key
);
ERROR 1068 (42000): Multiple( Multiple ) primary key defined( Definition )

Another example :

  • Column level constraints
CREATE TABLE emp4(
id INT PRIMARY KEY AUTO_INCREMENT ,
NAME VARCHAR(20)
);
  • Table level constraints
#MySQL The primary key name of is always PRIMARY, Even if you name the primary key constraint name, it's useless .
CREATE TABLE emp5(
id INT NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20),
pwd VARCHAR(15),
CONSTRAINT emp5_id_pk PRIMARY KEY(id) # There's no need to name .
);

(2) Add a primary key constraint after creating a table

ALTER TABLE The name of the table ADD PRIMARY KEY( Field list ); # The field list can be a field , It can also be multiple fields , If there are multiple fields , The primary key is compound 
ALTER TABLE student ADD PRIMARY KEY (sid);
ALTER TABLE emp5 ADD PRIMARY KEY(NAME,pwd);

4.5 About composite primary keys

create table The name of the table (
Field name data type ,
Field name data type ,
Field name data type ,
primary key( Field name 1, Field name 2) # Represents the field 1 And field 2 The combination is unique , You can also have more fields 
);
# Student list 
create table student(
sid int primary key, # Student number 
sname varchar(20) # The student's name 
);
# The curriculum 
create table course(
cid int primary key, # Course number 
cname varchar(20) # Course name 
);
# Course selection table 
create table student_course(
sid int,
cid int,
score int,
primary key(sid,cid) # Composite primary key 
);
insert into student values(1,' Zhang San '),(2,' Li Si ');
insert into course values(1001,'Java'),(1002,'MySQL');
mysql> select * from student;
+-----+-------+
| sid | sname |
+-----+-------+
| 1 | Zhang San |
| 2 | Li Si |
+-----+-------+
2 rows in set (0.00 sec)
mysql> select * from course;
+------+-------+
| cid | cname |
+------+-------+
| 1001 | Java |
| 1002 | MySQL |
+------+-------+
2 rows in set (0.00 sec)
insert into student_course values(1, 1001, 89),(1,1002,90),(2,1001,88),(2,1002,56);
mysql> select * from student_course;
+-----+------+-------+
| sid | cid | score |
+-----+------+-------+
| 1 | 1001 | 89 |
| 1 | 1002 | 90 |
| 2 | 1001 | 88 |
| 2 | 1002 | 56 |
+-----+------+-------+
4 rows in set (0.00 sec)
insert into student_course values(1, 1001, 100);
ERROR 1062 (23000): Duplicate entry '1-1001' for key 'PRIMARY'
mysql> desc student_course;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| sid | int(11) | NO | PRI | NULL | |
| cid | int(11) | NO | PRI | NULL | |
| score | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)
  • Another example
CREATE TABLE user1(
id INT,
NAME VARCHAR(15),
PASSWORD VARCHAR(25),
PRIMARY KEY (NAME,PASSWORD)
);
# If it is a multi column composite primary key constraint , Then none of these columns are allowed to be null , And the combined value is not allowed to repeat .
INSERT INTO user1
VALUES(1,'Tom','abc');
INSERT INTO user1
VALUES(1,'Tom1','abc');
# error :Column 'name' cannot be null
INSERT INTO user1
VALUES(1,NULL,'abc');
SELECT * FROM user1;

4.6 Delete primary key constraint

( In actual development , The primary key constraint in the table will not be deleted !)

alter table The name of the table drop primary key;

give an example :

ALTER TABLE student DROP PRIMARY KEY;
ALTER TABLE emp5 DROP PRIMARY KEY;

explain : Delete primary key constraint , You do not need to specify the primary key name , Because a table has only one primary key , After deleting the primary key constraint , Non empty still exists .

5. On the column :AUTO_INCREMENT

5.1 effect

The value of a field increases automatically

5.2 keyword

auto_increment

5.3 Features and requirements

(1) A table can only have at most one self growing column

(2) When a unique identifier or sequential value needs to be generated , Self growth can be set

(3) The column of the self growing column constraint must be a key column ( Primary key column , Unique key column

(4) The data type of a column with a self increasing constraint must be an integer type

(5) If the auto increment column specifies 0 and null, It will automatically increase based on the current maximum ; If the auto increment column manually specifies a specific value , Direct assignment to a specific value .

Error demonstration :

create table employee(
eid int auto_increment,
ename varchar(20)
);
# ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key 
create table employee(
eid int primary key,
ename varchar(20) unique key auto_increment
);
# ERROR 1063 (42000): Incorrect column specifier for column 'ename' because ename It's not an integer type 

5.4 How to specify self increasing constraints

(1) Build table

create table The name of the table (
Field name data type primary key auto_increment,
Field name data type unique key not null,
Field name data type unique key,
Field name data type not null default The default value is ,
);
create table The name of the table (
Field name data type default The default value is ,
Field name data type unique key auto_increment,
Field name data type not null default The default value is ,,
primary key( Field name )
);
CREATE TABLE test7(
id INT PRIMARY KEY AUTO_INCREMENT,
last_name VARCHAR(15)
);
# In development , Once the primary key field is declared to have AUTO_INCREMENT, When we add data , Don't give it to me 
# The corresponding field is assigned .
INSERT INTO test7(last_name)
VALUES('Tom');
SELECT * FROM test7;
# When we go to the primary key ( contain AUTO_INCREMENT) Add... To the field of 0 or null when , In fact, the value of the specified field will be automatically added up 
INSERT INTO test7(id,last_name)
VALUES(0,'Tom');
INSERT INTO test7(id,last_name)
VALUES(NULL,'Tom');
# No 0 or Null The primary key inserted when id What is what , Because it is obviously inappropriate to set self increment .
INSERT INTO test7(id,last_name)
VALUES(10,'Tom');
INSERT INTO test7(id,last_name)
VALUES(-10,'Tom');

(2) After the establishment of the table

alter table The name of the table modify Field name data type auto_increment;

for example :

create table employee(
eid int primary key ,
ename varchar(20)
);
alter table employee modify eid int auto_increment;
mysql> desc employee;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| eid | int(11) | NO | PRI | NULL | auto_increment |
| ename | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

5.5 How to delete auto increment constraints

#alter table The name of the table modify Field name data type auto_increment;# Add a self increasing constraint to this field 
alter table The name of the table modify Field name data type ; # Get rid of auto_increment It is equivalent to deleting 
alter table employee modify eid int;
mysql> desc employee;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| eid | int(11) | NO | PRI | NULL | |
| ename | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

5.6 MySQL 8.0 New characteristics — Persistence of self increasing variables

stay MySQL 8.0 Before , Since the primary key AUTO_INCREMENT If the value of is greater than max(primary key)+1, stay MySQL After restart , Reset AUTO_INCREMENT=max(primary key)+1, In some cases, this phenomenon may lead to business primary key conflict or other difficult to find problems .
The following is a case to compare whether self increasing variables in different versions are persistent .
stay MySQL 5.7 In the version , The test steps are as follows :
The created data table contains self incrementing primary keys id Field , The statement is as follows :

CREATE TABLE test1(
id INT PRIMARY KEY AUTO_INCREMENT
);

Insert 4 A null value , The implementation is as follows :

INSERT INTO test1
VALUES(0),(0),(0),(0);

Query data table test1 Data in , give the result as follows :

mysql> SELECT * FROM test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
4 rows in set (0.00 sec)

Delete id by 4 The record of , The statement is as follows :

DELETE FROM test1 WHERE id = 4;

Insert a null value again , The statement is as follows :

INSERT INTO test1 VALUES(0);

Query the data table test1 Data in , give the result as follows :

mysql> SELECT * FROM test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 5 |
+----+
4 rows in set (0.00 sec)

It can be seen from the results , Although deleted id by 4 The record of , But when you insert a null value again , No reuse of deleted 4, It's the distribution of 5.
Delete id by 5 The record of , give the result as follows :

DELETE FROM test1 where id=5;

Restart the database , Re insert a null value .

INSERT INTO test1 values(0);

Query the data table again test1 Data in , give the result as follows :

mysql> SELECT * FROM test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
4 rows in set (0.00 sec)

It can be seen from the results , Newly inserted 0 The value assigned is 4, Follow the operation logic before restart , Should be assigned here 6. The main reason for the above results is that the self incrementing primary key is not persistent .
stay MySQL 5.7 In the system , For the allocation rule of self increasing primary key , By InnoDB Data dictionary internal one Counter To decide , And the counter is only in In memory maintenance , It doesn't persist to disk . When the database restarts , The counter is initialized .

stay MySQL 8.0 In the version , The results of the last step of the above test steps are as follows :

mysql> SELECT * FROM test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 6 |
+----+
4 rows in set (0.00 sec)
# summary :mysql5.7 When the primary key auto increment is set, data will be inserted, such as id( 1, 2, 3, 4), If you delete a piece of data 4 Then insert the data again id by 5 instead of 4, Then if mysql It will resume after restart , Delete 4 After inserting data, it is still 4.
# mysql 8.0 When the primary key auto increment is set, data will be inserted, such as id( 1, 2, 3, 4), If you delete a piece of data 4 Then insert the data again id by 5 instead of 4, After that, even if mysql It will not recover after restart , Delete 4 After inserting data, it is still 5.

It can be seen from the results , Self increasing variables have been persistent .

MySQL 8.0 Persist the counter of self incrementing primary key to Redo log in . Every time the counter changes , Will be written to the redo log . If the database restarts ,InnoDB The memory value of the counter will be initialized according to the information in the redo log .

6. FOREIGN KEY constraint

6.1 effect

Limit the referential integrity of a field in a table .

such as : The selection of the employee's department in the employee table , The corresponding part must be found in the Department table .
 Insert picture description here

6.2 keyword

FOREIGN KEY

6.3 Master and slave tables / Parent and child tables

Main table ( Parent table ): The cited table , The referenced table

From the table ( Sub table ): Quote someone else's table , Refer to other people's tables

for example : The value of the employee's Department field in the employee table should refer to the Department table : The Department table is the main table , The employee table is from table .

for example : Student list 、 The curriculum 、 Course selection table : Students and courses in the course schedule should refer to the student schedule and course schedule respectively , The student schedule and curriculum are the main schedule , The course selection table is from the table .

6.4 characteristic

(1) From the foreign key column of the table , Must quote / Columns that refer to the primary key or unique constraint of the main table

​ Why? ? Because of being dependent / The referenced value must be unique

(2) When creating a foreign key constraint , If you don't name the foreign key constraint , The default name is not a column name , Instead, it automatically generates a foreign key name ( for example student_ibfk_1;), You can also specify a foreign key constraint name .

(3) establish (CREATE) If you specify a foreign key constraint when using the table , Create the main table first , Then create the slave table

(4) When deleting a table , Delete from the table first ( Or delete the foreign key constraint first ), Delete the main table

(5) When the records of the master table are referenced from the slave table , Records in the main table will not be allowed to be deleted , If you want to delete data , You need to delete the data that depends on this record from the table first , Then you can delete the data of the main table

(6) stay “ From the table ” Specify foreign key constraints in , And a table can establish multiple foreign key constraints

(7) The primary and secondary columns of the referenced table Names can be different , however The data type must be the same , The logical meaning is consistent . If the types are different , When creating a child table , There will be errors “ERROR 1005 (HY000): Can’t create table’database.tablename’(errno: 150)”.

​ for example : They all represent the department number , All are int type .

(8) When creating a foreign key constraint , By default, the system will establish the corresponding common index on the column . But the index name is the constraint name of the foreign key .( It is very efficient to query according to foreign keys )

(9) After deleting the foreign key constraint , must Manual Delete the corresponding index

6.5 Add a foreign key constraint

(1) Build table

create table Main table name (
Field 1 data type primary key,
Field 2 data type
);
create table From table name (
Field 1 data type primary key,
Field 2 data type ,
[CONSTRAINT < Foreign key constraint name >] FOREIGN KEY( From a field in the table ) references Main table name ( Referenced field )
);
#( From a field in the table ) The data type of must be the same as the main table name ( Referenced field ) The data types of are consistent , The logical meaning is the same 
#( From a field in the table ) The field name can be the same as the main table name ( Referenced field ) The field name of is the same , It can be different 
-- FOREIGN KEY: Specify the columns in the child table at the table level 
-- REFERENCES: Columns marked in the parent table 
create table dept( # Main table 
did int primary key, # Department number 
dname varchar(50) # Department name 
);
create table emp(# From the table 
eid int primary key, # Employee number 
ename varchar(5), # Employee name 
deptid int, # The employee's Department , Names can be different 
# Table level constraints 
foreign key (deptid) references dept(did) # Specify the foreign key constraint from the table 
#emp Tabular deptid And dept Tabular did The data types of are consistent , The meaning is the number of the Department 
);
explain :
(1) Main table dept You must create it successfully first , Then you can create emp surface , Foreign key specified successfully .
(2) When deleting a table , First delete from table emp, Delete the main table dept

 Insert picture description here

 Insert picture description here

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-Ov3sD701-1640265592391)(C:\Users\Think\Desktop\QQ Screenshot 20211223180723.png)]

(2) After the establishment of the table

In general , Table and table associations are designed in advance , therefore , The foreign key constraint will be defined when creating the table . however , If you need to modify the design of the table ( For example, add a new field , Add new relationships ), But there are no predefined foreign key constraints , that , We need to modify the table to supplement the definition .

Format :

ALTER TABLE From the table name ADD [CONSTRAINT Constraint name ] FOREIGN KEY ( From the fields of the table ) REFERENCES Main table name ( Referenced fields ) [on update xx][on delete xx];

give an example :

ALTER TABLE emp1
ADD [CONSTRAINT emp_dept_id_fk] FOREIGN KEY(dept_id) REFERENCES dept(dept_id);

give an example :

create table dept(
did int primary key, # Department number 
dname varchar(50) # Department name 
);
create table emp(
eid int primary key, # Employee number 
ename varchar(5), # Employee name 
deptid int # The employee's Department 
);
# When these two tables are created , If no foreign key is specified , Then the order of creation is random 
alter table emp add foreign key (deptid) references dept(did);

6.6 Demo questions

(1) Failure : Not a key column

create table dept(
did int , # Department number 
dname varchar(50) # Department name 
);
create table emp(
eid int primary key, # Employee number 
ename varchar(5), # Employee name 
deptid int, # The employee's Department 
foreign key (deptid) references dept(did)
);
#ERROR 1215 (HY000): Cannot add foreign key constraint as a result of dept Of did Not a key column 

(2) Failure : Inconsistent data types

create table dept(
did int primary key, # Department number 
dname varchar(50) # Department name 
);
create table emp(
eid int primary key, # Employee number 
ename varchar(5), # Employee name 
deptid char, # The employee's Department 
foreign key (deptid) references dept(did)
);
#ERROR 1215 (HY000): Cannot add foreign key constraint The reason is from the... Of the table deptid Field and main table did The data type of the field is inconsistent , And they should have the same logical meaning 

(3) success , The field names of the two tables are the same

create table dept(
did int primary key, # Department number 
dname varchar(50) # Department name 
);
create table emp(
eid int primary key, # Employee number 
ename varchar(5), # Employee name 
did int, # The employee's Department 
foreign key (did) references dept(did)
#emp Tabular deptid And dept Tabular did The data types of are consistent , The meaning is the number of the Department 
# It's no problem if you have the same name , Because the two one. did In different tables 
);

(4) add to 、 Delete 、 Revise the question

create table dept(
did int primary key, # Department number 
dname varchar(50) # Department name 
);
create table emp(
eid int primary key, # Employee number 
ename varchar(5), # Employee name 
deptid int, # The employee's Department 
foreign key (deptid) references dept(did)
#emp Tabular deptid And dept Tabular did The data types of are consistent , The meaning is the number of the Department 
);
# Add... To the main table : No impact 
insert into dept values(1001,' Teaching department ');
insert into dept values(1003, ' Finance Department ');
# Add... From table : If the foreign key constraint is set from the table, the added data is affected by the main table , Adding data from a table requires that the associated column has the corresponding primary key value of the primary table , If not, the addition fails 
insert into emp values(1,' Zhang San ',1001); # Add record from table succeeded , When adding this record , Required department table 1001 department 
insert into emp values(2,' Li Si ',1005);# Failed to add record from table , No master table id by 1005 Primary key of 
ERROR 1452 (23000): Cannot add( add to ) or update( modify ) a child row: a foreign key constraint fails (`atguigudb`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptid`) REFERENCES `dept` (`did`)) From the table emp Failed to add record , Because the main table dept No, 1005 department
mysql> select * from dept;
+------+--------+
| did | dname |
+------+--------+
| 1001 | Teaching department |
| 1003 | Finance Department |
+------+--------+
2 rows in set (0.00 sec)
mysql> select * from emp;
+-----+-------+--------+
| eid | ename | deptid |
+-----+-------+--------+
| 1 | Zhang San | 1001 |
+-----+-------+--------+
1 row in set (0.00 sec)
# Modify from table : Affected by the main table 
update emp set deptid = 1002 where eid = 1;# Failed to modify slave table , The main table doesn't have 1002
ERROR 1452 (23000): Cannot add( add to ) or update( modify ) a child row( Record of sub table ): a foreign key constraint fails( Foreign key constraint failed ) (`atguigudb`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptid`) REFERENCES `dept` (`did`)) # Departmental table did There are no fields now 1002 Value , Therefore, the employee's department cannot be modified in the employee table deptid by 1002
# Main table modification : See if the data is referenced from the table 
update dept set did = 1002 where did = 1001;# Failed to modify the main table ,
ERROR 1451 (23000): Cannot delete( Delete ) or update( modify ) a parent row( Record of parent table ): a foreign key constraint fails (`atguigudb`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptid`) REFERENCES `dept` (`did`)) # Departmental table did Of 1001 The field has been emp Refer to the , So the Department table 1001 The field cannot be modified .
update dept set did = 1002 where did = 1003;# Modification of main table succeeded Because of the Department table 1003 The Department has not been emp Table references , So you can modify 
# Empathy : Delete the master table data to see if it is referenced from the slave table , Deleting data from the table is not affected 
delete from dept where did=1001; # Failed to delete main table 
ERROR 1451 (23000): Cannot delete( Delete ) or update( modify ) a parent row( Parent table record ): a foreign key constraint fails (`atguigudb`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptid`) REFERENCES `dept` (`did`)) # Because the Department table did Of 1001 The field has been emp Refer to the , So the Department table 1001 The record corresponding to the field cannot be deleted 

summary : The binding relationship is for both parties

  • After adding the foreign key constraint , The modification and deletion data of the main table are constrained , Adding does not affect

  • After adding the foreign key constraint , Adding and modifying data from a table is constrained , Deletion does not affect

  • To create a foreign key from a table , The main table must exist

  • When deleting the main table , It is required to delete... From the table first , Or delete the relationship that the foreign key refers to the main table from the table

6.7 Constraint level ( cascade )

  • Cascade The way : On parent table update/delete When recording , Sync update/delete Drop matching records of child tables

  • Set null The way : On parent table update/delete When recording , Set the column of the matching record on the child table to null, Note that the foreign key column of the child table cannot be not null

  • No action The way : If there are matching records in the sub table , The parent table is not allowed to correspond to the candidate key update/delete operation

  • Restrict The way : Same as no action, Check foreign key constraints immediately

  • Set default The way ( In visualization tools SQLyog May be blank in ): When the parent table changes , The sub table sets the foreign key column to a default value , but Innodb Can't identify

If no level is specified , Equivalent to Restrict The way .

For foreign key constraints , It's better to use : ON UPDATE CASCADE ON DELETE RESTRICT The way .

(1) demonstration 1:on update cascade on delete set null

create table dept(
did int primary key, # Department number 
dname varchar(50) # Department name 
);
create table emp(
eid int primary key, # Employee number 
ename varchar(5), # Employee name 
deptid int, # The employee's Department 
foreign key (deptid) references dept(did) on update cascade on delete set null
# Set the modification operation to cascade modification level , Set the delete operation to set null Grade 
);
insert into dept values(1001,' Teaching department ');
insert into dept values(1002, ' Finance Department ');
insert into dept values(1003, ' Consulting ');
insert into emp values(1,' Zhang San ',1001); # When adding this record , Required department table 1001 department 
insert into emp values(2,' Li Si ',1001);
insert into emp values(3,' Wang Wu ',1002);
mysql> select * from dept;
+------+--------+
| did | dname |
+------+--------+
| 1001 | Teaching department |
| 1002 | Finance Department |
| 1003 | Consulting |
+------+--------+
mysql> select * from emp;
+-----+-------+--------+
| eid | ename | deptid |
+-----+-------+--------+
| 1 | Zhang San | 1001 |
| 2 | Li Si | 1001 |
| 3 | Wang Wu | 1002 |
+-----+-------+--------+
# Modification of main table succeeded , From the table is also modified , Modified the fields referenced in the main table 1002 by 1004, The reference field from the table is changed to 1004 了 
mysql> update dept set did = 1004 where did = 1002;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from dept;
+------+--------+
| did | dname |
+------+--------+
| 1001 | Teaching department |
| 1003 | Consulting |
| 1004 | Finance Department | # Turned out to be 1002, It is amended as follows 1004
+------+--------+
3 rows in set (0.00 sec)
mysql> select * from emp;
+-----+-------+--------+
| eid | ename | deptid |
+-----+-------+--------+
| 1 | Zhang San | 1001 |
| 2 | Li Si | 1001 |
| 3 | Wang Wu | 1004 | # Turned out to be 1002, Then change it to 1004
+-----+-------+--------+
3 rows in set (0.00 sec)
# Delete record of main table succeeded , The value of the corresponding field from the table is modified to null
mysql> delete from dept where did = 1001;
Query OK, 1 row affected (0.01 sec)
mysql> select * from dept;
+------+--------+
| did | dname | # Record 1001 The Department has been deleted 
+------+--------+
| 1003 | Consulting |
| 1004 | Finance Department |
+------+--------+
2 rows in set (0.00 sec)
mysql> select * from emp;
+-----+-------+--------+
| eid | ename | deptid |
+-----+-------+--------+
| 1 | Zhang San | NULL | # Original quote 1001 Employees of the Department ,deptid The field changes to null
| 2 | Li Si | NULL |
| 3 | Wang Wu | 1004 |
+-----+-------+--------+
3 rows in set (0.00 sec)

(2) demonstration 2:on update set null on delete cascade

create table dept(
did int primary key, # Department number 
dname varchar(50) # Department name 
);
create table emp(
eid int primary key, # Employee number 
ename varchar(5), # Employee name 
deptid int, # The employee's Department 
foreign key (deptid) references dept(did) on update set null on delete cascade
# Set the modification operation to set null Grade , Set the deletion operation to cascade deletion level 
);
insert into dept values(1001,' Teaching department ');
insert into dept values(1002, ' Finance Department ');
insert into dept values(1003, ' Consulting ');
insert into emp values(1,' Zhang San ',1001); # When adding this record , Required department table 1001 department 
insert into emp values(2,' Li Si ',1001);
insert into emp values(3,' Wang Wu ',1002);
mysql> select * from dept;
+------+--------+
| did | dname |
+------+--------+
| 1001 | Teaching department |
| 1002 | Finance Department |
| 1003 | Consulting |
+------+--------+
3 rows in set (0.00 sec)
mysql> select * from emp;
+-----+-------+--------+
| eid | ename | deptid |
+-----+-------+--------+
| 1 | Zhang San | 1001 |
| 2 | Li Si | 1001 |
| 3 | Wang Wu | 1002 |
+-----+-------+--------+
3 rows in set (0.00 sec)
# Modify the main table , Set the corresponding field from the table to null
mysql> update dept set did = 1004 where did = 1002;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from dept;
+------+--------+
| did | dname |
+------+--------+
| 1001 | Teaching department |
| 1003 | Consulting |
| 1004 | Finance Department | # original did yes 1002
+------+--------+
3 rows in set (0.00 sec)
mysql> select * from emp;
+-----+-------+--------+
| eid | ename | deptid |
+-----+-------+--------+
| 1 | Zhang San | 1001 |
| 2 | Li Si | 1001 |
| 3 | Wang Wu | NULL | # original deptid yes 1002, Because the Department table 1002 It was modified ,1002 There is no corresponding , Set it to null
+-----+-------+--------+
3 rows in set (0.00 sec)
# Delete record of main table succeeded , The main table of 1001 The line was deleted , The corresponding records from the table have also been deleted 
mysql> delete from dept where did=1001;
Query OK, 1 row affected (0.00 sec)
mysql> select * from dept;
+------+--------+
| did | dname | # In the Department list 1001 The Department was deleted 
+------+--------+
| 1003 | Consulting |
| 1004 | Finance Department |
+------+--------+
2 rows in set (0.00 sec)
mysql> select * from emp;
+-----+-------+--------+
| eid | ename | deptid |# original 1001 Employees in the Department have also been deleted 
+-----+-------+--------+
| 3 | Wang Wu | NULL |
+-----+-------+--------+
1 row in set (0.00 sec)

(3) demonstration :on update cascade on delete cascade

create table dept(
did int primary key, # Department number 
dname varchar(50) # Department name 
);
create table emp(
eid int primary key, # Employee number 
ename varchar(5), # Employee name 
deptid int, # The employee's Department 
foreign key (deptid) references dept(did) on update cascade on delete cascade
# Set the modification operation to cascade modification level , Set the deletion operation to cascade deletion level 
);
insert into dept values(1001,' Teaching department ');
insert into dept values(1002, ' Finance Department ');
insert into dept values(1003, ' Consulting ');
insert into emp values(1,' Zhang San ',1001); # When adding this record , Required department table 1001 department 
insert into emp values(2,' Li Si ',1001);
insert into emp values(3,' Wang Wu ',1002);
mysql> select * from dept;
+------+--------+
| did | dname |
+------+--------+
| 1001 | Teaching department |
| 1002 | Finance Department |
| 1003 | Consulting |
+------+--------+
3 rows in set (0.00 sec)
mysql> select * from emp;
+-----+-------+--------+
| eid | ename | deptid |
+-----+-------+--------+
| 1 | Zhang San | 1001 |
| 2 | Li Si | 1001 |
| 3 | Wang Wu | 1002 |
+-----+-------+--------+
3 rows in set (0.00 sec)
# Modify the main table , Automatically modify from the field corresponding to the table 
mysql> update dept set did = 1004 where did = 1002;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from dept;
+------+--------+
| did | dname |
+------+--------+
| 1001 | Teaching department |
| 1003 | Consulting |
| 1004 | Finance Department | # department 1002 It is amended as follows 1004
+------+--------+
3 rows in set (0.00 sec)
mysql> select * from emp;
+-----+-------+--------+
| eid | ename | deptid |
+-----+-------+--------+
| 1 | Zhang San | 1001 |
| 2 | Li Si | 1001 |
| 3 | Wang Wu | 1004 | # Cascade modification 
+-----+-------+--------+
3 rows in set (0.00 sec)
# Delete record of main table succeeded , The main table of 1001 The line was deleted , The corresponding records from the table have also been deleted 
mysql> delete from dept where did=1001;
Query OK, 1 row affected (0.00 sec)
mysql> select * from dept;
+------+--------+
| did | dname | #1001 The Department has been deleted 
+------+--------+
| 1003 | Consulting |
| 1004 | Finance Department |
+------+--------+
2 rows in set (0.00 sec)
mysql> select * from emp;
+-----+-------+--------+
| eid | ename | deptid | #1001 Employees in the Department have also been deleted 
+-----+-------+--------+
| 3 | Wang Wu | 1004 |
+-----+-------+--------+
1 row in set (0.00 sec)

6.8 Delete foreign key constraint

The process is as follows :

(1) The first step is to view the constraint name and delete the foreign key constraint
SELECT * FROM information_schema.table_constraints WHERE table_name = ' The name of the table ';# View the constraint name of a table 
ALTER TABLE From the table name DROP FOREIGN KEY Foreign key constraint name ;2) The second step is to view the index name and delete the index .( Be careful , You can only manually delete )
SHOW INDEX FROM The name of the table ; # View the index name of a table 
ALTER TABLE From the table name DROP INDEX Index name / From the foreign key constraint name of the table ;# Note the constraint name for the foreign key , Instead of the relationship field name 

give an example :

mysql> SELECT * FROM information_schema.table_constraints WHERE table_name = 'emp';
mysql> alter table emp drop foreign key emp_ibfk_1;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from emp;
mysql> alter table emp drop index deptid;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from emp;

6.9 Development scenarios

problem 1: If there is a relationship between two tables ( one-on-one 、 One to many ), such as : Employee table and department table ( One to many ), Whether foreign key constraints must be established between them ?

answer : No, it isn't

problem 2: What is the difference between building and not building foreign key constraints ?

answer : Foreign key constraint , Your operation ( Create table 、 Delete table 、 add to 、 modify 、 Delete ) Will be limited , Restricted from the grammatical level . for example : It is impossible to add an employee information in the employee table , The value of its department cannot be found in the Department table .

No foreign key constraints , Your operation ( Create table 、 Delete table 、 add to 、 modify 、 Delete ) There is no limit on the , To ensure the accuracy of the data Citation integrity , Only by Rely on the programmer's consciousness , Or is it stay Java Define in the program . for example : In the employee list , You can add an employee's information , Its department is designated as a completely non-existent Department .

problem 3: So is there a relationship between building or not building foreign key constraints and queries ?

answer : No,

stay MySQL in , Foreign key constraints are costly , Need to consume system resources . For large concurrent SQL operation , It may not be suitable for . For example, the central database of large websites , May be Because of the overhead of foreign key constraints . therefore , MySQL Allows you not to use the system's own foreign key constraints , stay Application level Complete the logic of checking data consistency . in other words , Even if you don't use foreign key constraints , We should also find ways to use additional logic at the application level , To implement the function of foreign key constraints , Ensure data consistency .

6.10 Ali Development Specification

mandatory 】 Do not use foreign keys and cascades , All foreign key concepts must be solved in the application layer .

explain :( Conceptual explanation ) In the student list student_id It's the primary key , So the student_id Foreign key . If you update the student_id, Also trigger the student_id to update , That's cascading updates . Foreign keys and cascading updates apply to Single machine low concurrency , Not suitable for Distributed High concurrency cluster ; Cascading updates are strong blocking , There is a database Update storm The risk of ; Foreign keys affect the of the database Insertion speed .

7. CHECK constraint

7.1 effect

Check whether the value of a field is a symbol xx requirement , Generally refers to the range of values

2、 keyword

CHECK

3、 explain :MySQL 5.7 I won't support it

MySQL5.7 have access to check constraint , but check Constraints have no effect on Data Validation . When adding data , There are no errors or warnings

however MySQL 8.0 Can be used in check Constrained .

create table employee(
eid int primary key,
ename varchar(5),
gender char check (' male ' or ' Woman ')
);
insert into employee values(1,' Zhang San ',' demon ');
mysql> select * from employee;
+-----+-------+--------+
| eid | ename | gender |
+-----+-------+--------+
| 1 | Zhang San | demon |
+-----+-------+--------+
1 row in set (0.00 sec)
  • Another example
CREATE TABLE temp(
id INT AUTO_INCREMENT,
NAME VARCHAR(20),
age INT CHECK(age > 20),
PRIMARY KEY(id)
);
  • Another example
age tinyint check(age >20) or sex char(2) check(sex in(‘ male ’,’ Woman ’))
  • Another example
CHECK(height>=0 AND height<3)

8. DEFAULT constraint

8.1 effect

Give a field / Specify a default value for a column , Once the default value is set , When inserting data , If this field has no explicit assignment , The default value is assigned .

8.2 keyword

DEFAULT

8.3 How to add the default value to the field

(1) Build table

create table The name of the table (
Field name data type primary key,
Field name data type unique key not null,
Field name data type unique key,
Field name data type not null default The default value is ,
);
create table The name of the table (
Field name data type default The default value is ,
Field name data type not null default The default value is ,
Field name data type not null default The default value is ,
primary key( Field name ),
unique key( Field name )
);
explain : The default value constraint is generally not added to the unique key and primary key columns
create table employee(
eid int primary key,
ename varchar(20) not null,
gender char default ' male ',
tel char(11) not null default '' # The default is an empty string 
);
mysql> desc employee;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| eid | int(11) | NO | PRI | NULL | |
| ename | varchar(20) | NO | | NULL | |
| gender | char(1) | YES | | male | |
| tel | char(11) | NO | | | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
insert into employee values(1,' Wang Fei ',' male ','13700102535'); # success 
mysql> select * from employee;
+-----+-------+--------+-------------+
| eid | ename | gender | tel |
+-----+-------+--------+-------------+
| 1 | Wang Fei | male | 13700102535 |
+-----+-------+--------+-------------+
1 row in set (0.00 sec)
insert into employee(eid,ename) values(2,' Tianqi '); # success 
mysql> select * from employee;
+-----+-------+--------+-------------+
| eid | ename | gender | tel |
+-----+-------+--------+-------------+
| 1 | Wang Fei | male | 13700102535 |
| 2 | Tianqi | male | |
+-----+-------+--------+-------------+
2 rows in set (0.00 sec)
insert into employee(eid,ename) values(3,' careless ');
#ERROR 1062 (23000): Duplicate entry '' for key 'tel' 
# If tel If there is a uniqueness constraint, an error will be reported , If tel There is no uniqueness constraint , I can add success 

Another example :

CREATE TABLE myemp(
id INT AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(15),
salary DOUBLE(10,2) DEFAULT 2000
);

(2) After the establishment of the table

alter table The name of the table modify Field name data type default The default value is ;
# If this field had a non NULL constraint , You also keep non empty constraints , Then when adding the default value constraint , You have to keep the non NULL constraint , Otherwise, the non empty constraint will be deleted 
# Empathy , The same is true when you add a non NULL constraint to a field , If this field had a default value constraint , You want to keep , Also in modify Keep the default value constraint in the statement , Otherwise, it will be deleted 
alter table The name of the table modify Field name data type default The default value is not null;
create table employee(
eid int primary key,
ename varchar(20),
gender char,
tel char(11) not null
);
mysql> desc employee;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| eid | int(11) | NO | PRI | NULL | |
| ename | varchar(20) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| tel | char(11) | NO | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
alter table employee modify gender char default ' male '; # to gender Add a default value constraint to the field 
alter table employee modify tel char(11) default ''; # to tel Add a default value constraint to the field 
mysql> desc employee;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| eid | int(11) | NO | PRI | NULL | |
| ename | varchar(20) | YES | | NULL | |
| gender | char(1) | YES | | male | |
| tel | char(11) | YES | | | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
alter table employee modify tel char(11) default '' not null;# to tel Add a default value constraint to the field , And keep non null constraints 
mysql> desc employee;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| eid | int(11) | NO | PRI | NULL | |
| ename | varchar(20) | YES | | NULL | |
| gender | char(1) | YES | | male | |
| tel | char(11) | NO | | | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

8.4 How to delete a default value constraint

alter table The name of the table modify Field name data type ;# Delete default constraint , Non null constraints are not retained 
alter table The name of the table modify Field name data type not null; # Delete default constraint , Keep non empty constraints 
alter table employee modify gender char; # Delete gender Field default value constraint , If there are non empty constraints , Also delete 
alter table employee modify tel char(11) not null;# Delete tel Field default value constraint , Keep non empty constraints 
mysql> desc employee;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| eid | int(11) | NO | PRI | NULL | |
| ename | varchar(20) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| tel | char(11) | NO | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

9. interview

interview 1、 Why is it that when creating tables , Add not null default ‘’ or default 0 '' It's a pair of single quotes

answer : Don't want to appear in the table null value .

interview 2、 Why don't you want null Value

answer :(1) Bad comparison .null Is a special value , You can only use special is null and is not null To compare . Encountered operator , Usually back to null.

​ (2) The efficiency is not high . Affect and improve the index effect . therefore , When we are building tables not null default ‘’ or default 0

interview 3、 belt AUTO_INCREMENT The field value of the constraint is from 1 Did you start ?
stay MySQL in , Default AUTO_INCREMENT The initial value of 1, Every new record , The field value is automatically added 1. Set auto increment properties (AUTO_INCREMENT) When , You can also specify the value of the auto increment field of the first inserted record , In this way, the auto increment field value of the newly inserted record increases from the initial value , If you insert the first record in the table , At the same time specified id The value is 5, Then the record to be inserted later id The value will change from 6 Begin to increase . When adding a primary key constraint , You often need to set fields to automatically add attributes .

interview 4、 Not every table can choose any storage engine ?
Foreign key constraints (FOREIGN KEY) Can't be used across engines .

MySQL Support for multiple storage engines , Each table can specify a different storage engine , It should be noted that : Foreign key constraints are used to ensure the referential integrity of data , If you need to associate foreign keys between tables , Different storage engines are specified , Then foreign key constraints cannot be created between these tables . So , The choice of storage engine is not completely arbitrary .

copyright:author[Cool breeze AAA],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/02/202202130734466370.html