Detailed explanation of constraints, multi-table queries and subqueries in MySQL

1. Primary key constraints of constraints

Constraints: Constraints are added to columns to constrain columns.

1. Primary key constraint (unique identification): non-null, unique, referenced

When a column of the table is designated as the primary key, the class cannot be empty or have duplicate values. There are two ways to specify the primary key when creating a table:

CREATE TABLE stu(
    sid  CHAR(6) PRIMARY KEY,
    sname  VARCHAR(20),
    age  INT,
    sex  VARCHEAR(10)
);

CREATE TABLE stu(
    sid  CHAR(6) ,
    sname  VARCHAR(20),
    age  INT,
    sex  VARCHEAR(10),
    PRIMARY KEY(sid)
);

Specify the sid column as the primary key column, that is, add a primary key constraint to the sid column.

Specify the primary key when modifying the table:

ALTER TABLE stu ADD PRIMARY KEY(sid);

Delete primary key:

ALTER TABLE stu DROP PRIMARY KEY;

2. Primary key auto-increment

Because the characteristics of the primary key column are: it must be unique and cannot be empty, so we usually specify the primary key as an integer type, and then set its automatic growth to ensure the unique and non-null characteristics of the primary key column when inserting data.

Specify the primary key auto-increment when creating a table

CREATE TABLE stu(
    sid  INT PRIMARY KEY AUTO_INCREMENT,
    sname  VARCHAR(20),
    age  INT,
    sex  VARCHEAR(10)
);

Set the primary key auto-increment when modifying the table:

ALTER TABLE stu CHANGE sid sid INT AUTO_INCREMENT;

Delete the primary key auto-increment when modifying the table:

ALTER TABLE stu CHANGE sid sid INT ;

Test the primary key auto-increment:

INSERT INTO stu VALUES(NULL,'zhangsan',23,'man');
INSERT INTO stu(sname,age,sex) VALUES(NULL,'zhangsan',23,'man');

3. Non-null constraints

Because some columns cannot be set to null values, you can add non-null constraints.

For example:

CREATE TABLE stu (
　　 sid INT PRIMARY KEY AUTO_INCREMENT,
　　 sname  VARCHAR(20) NOT NULL,
　　 age   INT,
　　 sex  VARCHAR(10)
);

A non-null constraint is set on the sname column.

4. Unique constraints

Some columns in the garage cannot have repeated values, so you can add unique constraints to the columns.

For example:

CREATE TABLE stu (
　　 sid INT PRIMARY KEY AUTO_INCREMENT,
　　 sname  VARCHAR(20) NOT NULL UNIQUE,
　　 age   INT,
　　 sex  VARCHAR(10)
);

2. Concept model

1. Object model: in Java it is domain, for example: User, Student.

2. Relational model: tables in the database, 1-to-many, 1-to-1, many-to-many.

3. Foreign key constraints

The foreign key must be the value of the primary key of another table (the foreign key must reference the primary key.)

Foreign keys can be repeated

Foreign keys can be empty

1. Add foreign key constraints when creating

CREATE TABLE dept (
    deptno INT PRIMARY KEY AUTO_INCREMENT,
    dname VARCHAR(50)
);
insert into dept values(10,'研发部');
insert into dept values(20,'人力部');
insert into dept values(30,'财务部');

CREATE TABLE emp (
    empno INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(50),
    deptno INT,
    CONSTRAINT fk_emp_dept FOREIGN KEY(dno) REFERENCES dept(deptno) 
);

CREATE TABLE dept (
    deptno INT PRIMARY KEY AUTO_INCREMENT,
    dname VARCHAR(50)
);
INSERT INTO dept VALUES(10,'研发部');
INSERT INTO dept VALUES(20,'人力部');
INSERT INTO dept VALUES(30,'财务部');

INSERT INTO emp(empno,ename) VALUES(null,'zhangsan');
INSERT INTO emp(empno,ename,deptno) VALUES(null,'lisi',10);


INSERT INTO emp(empno,ename,deptno) VALUES(null,'zhangsan',80);
/* Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails 
(`mydb2`.`emp`, CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`))
*/

2. Add foreign key constraints when modifying the table:

ALTER TABLE emp ADD CONSTRAINT fk_emp_dept FOREIGN KEY(dno) REFERNCES dept(deptno);

4. Database relational model

1. One-to-one relationship

It is special to establish a one-to-one relationship in a table. It is necessary to make the primary key of one of the tables both a primary key and a foreign key.

CREATE TABLE hasband (
    hid INT PRIMARY KEY AUTO_INCREMENT,
    hname VARCHAR(50)
);

CREATE TABLE wife (
    wid INT PRIMARY KEY AUTO_INCREMENT,
    wname VARCHAR(50),
    CONSTRAINT fk_wife_hasband FOREIGN KEY (wid)  REFERENCES hasband(hid) 
);

2. Many-to-many relationship

To establish a many-to-many relationship in a table, you need to use an intermediate table, that is, you need three tables, and use two foreign keys in the intermediate table to reference them respectively. The primary keys of the other two tables.

CREATE TABLE student (
    sid INT PRIMARY KEY ,
    ......
);

CREATE TABLE teacher(
    tid INT PRIMARY KEY ,
    ......
);

CREATE TABLE stu_tea (
    sid INT,
    tid INT,
    ADD CONSTRAINT fk_stu_tea_sid FOREIGN KEY (sid)  REFERENCES student(sid) ,
    ADD CONSTRAINT fk_stu_tea_tid FOREIGN KEY (tid)  REFERENCES teacher(tid) 
);

Establish a relationship in the intermediate table, such as:

INSERT INTO stu_tea VALUES(5,1);
INSERT INTO stu_tea VALUES(2,2);
INSERT INTO stu_tea VALUES(3,2);

5. Multi-table query

1, Classification

Merge result set

Connection query

Subquery

2. Merge result query

Requires that the type and number of result set columns in the merged table are the same

UNION, remove duplicate rows

UNION ALL, do not remove duplicate rows

SELECT * FROM 表1名
UNION ALL
SELECT * FROM 表2名;

3. Connection query

①Category

Inner join

Outer join

Left outer join

Right outer join

Full outer join (mysql does not support it)

Natural join (a simplified method)

②Inner join

Dialect: SELECT * FROM table 1 alias 1, table 2 alias 2 WHERE alias 1.xx=alias 2.xx;

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;
SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno=d.deptno;

Filter by conditions Remove useless information from Cartesian product.

Standard: SELECT * FROM table 1 alias 1 INNER JOIN table 2 alias 2 ON alias 1.xx=alias 2.xx;

SELECT e.ename, e.sal , d.dname  FROM emp e INNER JOIN dept d ON  e.deptno=d.deptno;

Natural: SELECT * FROM table 1 alias 1 NATURAL JOIN Table 2 Alias ​​2;

SELECT e.ename, e.sal , d.dname  FROM emp e NATURAL JOIN dept d;

All records queried by the inner join meet the conditions

③Outer join

Left outer: SELECT * FROM Table 1 Alias ​​1 LEFT OUTER JOIN Table 2 Alias ​​2 ON Alias ​​1.xx = Alias ​​2.xx;

The records in the left table will be queried regardless of whether they meet the conditions, while the records in the right table can be retrieved only if they meet the conditions. Records in the left table that do not meet the conditions will be null in the right table.

SELECT e.ename, e.sal , IFNULL(d.dname,'无部门') AS dname  FROM emp e LEFT OUTER JOIN dept d ON  e.deptno=d.deptno;

Left outer natural: SELECT * FROM table 1 Alias ​​1 NATURAL LEFT OUTER JOIN Table 2 Alias ​​2 ON Alias ​​1.xx=alias 2.xx;

Right outer: SELECT * FROM table 1 Alias ​​1 RIGHT OUTER JOIN Table 2 Alias ​​2 ON Alias ​​1.xx = Alias ​​2.xx;

The records in the right table will be queried regardless of whether they meet the conditions, while the records in the left table can be retrieved only if they meet the conditions. Records in the right table that do not meet the conditions will be null in the left table.

Right outer natural: SELECT * FROM Table 1 Alias ​​1 NATURAL RIGHT OUTER JOIN Table 2 Alias ​​2 ON Alias ​​1.xx = Alias ​​2.xx;

Full link: You can use UNION to complete Fully connected.

SELECT e.ename, e.sal , d.dname 
FROM emp e LEFT OUTER JOIN dept d 
ON e.deptno=d.deptno
UNION
SELECT e.ename, e.sal , d.dname 
FROM emp e RIGHT OUTER JOIN dept d 
ON e.deptno=d.deptno;

4. Subquery

There is a query in the query (check the number of select keywords)

①The position where it appears

is used as a condition after WHERE Exists as a table after

FROM (multiple rows and multiple columns)

②Condition

Single row and single column: SELECT * FROM table 1 alias 1 WHERE column 1 [=, > ,<,>=,<=,!=] (SELECT column FROM table 2 alias 2 WHERE condition);

SELECT * FROM emp WHERE sal=(SELECT MAX(sal) FROM emp);

Multiple rows and single column: SELECT * FROM table 1 alias 1 WHERE column 1 [IN ,ALL,ANY] (SELECT column FROM table 2 alias 2 WHERE condition);

SELECT * FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE job='经理') ;

Single row and multiple columns: SELECT * FROM table 1 alias 1 WHERE (column 1, column 2) IN (SELECT column 1, column 2 FROM table 2 alias 2 WHERE condition);

SELECT * FROM emp WHERE (job,deptno) IN (SELECT job,deptno from emp WHERE deptno=30) ;

Multiple rows and multiple columns: SELECT * FROM table 1 alias 1, (SELECT...) Table 2 alias 2 WHERE condition;

The above is the detailed content of Detailed explanation of constraints, multi-table queries and subqueries in MySQL. For more information, please follow other related articles on the PHP Chinese website!