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 [=, > ,6580843315dd7804e35fd3743df832ea=,<=,!=] (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!