MySQL constraints, multi-table queries, subqueries, mysql constraint table queries

MySQL constraints, multi-table queries, subqueries, mysql constraint table queries

1. Primary Key constraints

Constraint: A constraint is added to a column to constrain the column.

1. primary key constraint (unique identifier): non-empty, unique, and referenced

• When a column in the table is specified as the primary key, the class cannot be blank and duplicate values cannot appear.
• You can 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 to 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. Self-Growth of primary keys

• Because primary key columns must be unique and cannot be empty, we usually specify the primary key as an integer and set it to automatically increase, this ensures the unique and non-empty features of the primary key column when inserting data.
• Specify 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));

• When modifying a table, Set primary key auto-increment:

ALTER TABLE stu CHANGE sid sid INT AUTO_INCREMENT;

• When you modify a table, delete the primary key auto-increment:

ALTER TABLE stu CHANGE sid sid INT ;

• Test auto-increment of primary keys:

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

3. Non-empty Constraint

Because some Columns cannot be set as 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 for the sname column.

4. unique constraints

Duplicate values cannot be set for certain columns in the garage. Therefore, you can add unique constraints to columns.

For example:

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

Ii. Conceptual Model

1. Object Model: domain in Java, such as User and Student.

2. Relational Model: in a database, tables are one-to-many, one-to-one, and many-to-many.

Iii. Foreign key constraints

• The foreign key must be the value of the primary key of the other table (the foreign key must reference the primary key .)
• Foreign keys can be repeated.
• Foreign key can be blank

1. Add a foreign key constraint during creation

Create table dept (deptno int primary key AUTO_INCREMENT, dname VARCHAR (50); insert into dept values (10, 'r & D author'); insert into dept values (20, 'hr Department '); insert into dept values (30, 'Finance Department'); 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, 'r & D department '); insert into dept VALUES (20, 'hr Department'); insert into dept VALUES (30, 'Finance Department '); 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 ('mydb '. 'emp', constraint' fk _ emp_dept 'foreign key ('deptno') REFERENCES 'dept' ('deptno '))*/

2. Add a foreign key constraint when modifying the table:

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

Iv. Database Relationship Model

1. One-to-one relationship

Creating a one-to-one relationship in a table is special. You need to make the primary key of one of the tables, that is, the primary key and the 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 relationships

To establish multiple-to-multiple relationships in a table, you need to use an intermediate table, that is, three tables are required. Two foreign keys are used in the intermediate table to reference 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) );

Create a link in the intermediate table, for example:

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

• The type and number of columns of the result set must be the same in the merged table.
• UNION to remove duplicate rows
• Union all, removing duplicate rows

SELECT * FROM table 1
UNION ALL
SELECT * FROM table 2;

3. Connection Query

① Category

• Internal Connection
• External Connection
  • Left Outer Join
  • Outer right connection
  • All external connections (not supported by mysql)
  • Natural connection (simplified)

② Internal connection

• 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 condition to Remove useless information from Cartesian points.

• 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;

• Naturally: 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 internal connection meet the conditions.

③ External connection

• Left outer: SELECT * FROM table 1 alias 1 left outer join Table 2 alias 2 ON Alias 1.xx= alias 2.xx;
  • Records in the left table are queried no matter whether the conditions are met or not, and only the right table can be queried if the conditions are met. Records in the left table that do not meet the conditions are null in the right table.

SELECT e. ename, e. sal, IFNULL (d. dname, 'no departments') 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;
• Outside RIGHT: SELECT * FROM table 1 alias 1 right outer join Table 2 alias 2 ON Alias 1.xx= alias 2.xx;
  • Records in the right table are queried no matter whether the conditions are met or not, and only the left table can be queried if the conditions are met. For records that do not meet the conditions in the right table, the left table is null.
• Right outer nature: 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 the full link.

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

Query in (view the number of select keywords)

① Location

• The WHERE clause exists as a condition.
• Exist as a table after FROM (multiple rows and multiple columns)

② Condition

• Single Row 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 one 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 = 'manager ');

• 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;