MySQL constraints, multi-table queries, sub-queries

First, the constraint of the primary KEY constraints

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

1. PRIMARY KEY constraint (unique identifier): non-null, unique, referenced

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

CREATE TABLE Stu (    Sid  CHAR (6) PRIMARY KEY,    sname  VARCHAR, age  INT,    sex  Varchear (ten)); CREATE TABLE Stu (    Sid  CHAR (6),    sname  VARCHAR), age INT, Sex varchear (Ten), PRIMARY KEY (SID));     

Specifies that the SID column is a primary key column, that is, the primary KEY constraint is added for SID columns

• To specify a primary key when modifying a table:

ALTER TABLE Stu ADD PRIMARY KEY (SID);

• To delete a primary key:

ALTER TABLE Stu DROP PRIMARY KEY;

2, the primary key self-growth

• Because the attributes of the primary key column are: Must be unique and cannot be null, we typically specify an integer for the primary key, and then set its autogrow, which guarantees the unique and non-empty characteristics of the primary key column when the data is inserted.
• Specify the primary key self-growth when creating a table

CREATE TABLE Stu (    sid  Int PRIMARY KEY auto_increment,    sname  VARCHAR (), age  int,    Sex  Varchear (ten));  

• To set the primary key self-growth when modifying a table:

ALTER TABLE stu change Sid Sid INT Auto_increment;

• Delete primary key self-growth when modifying table:

ALTER TABLE stu change Sid Sid INT;

• Test primary key self-growth:

INSERT into Stu VALUES (NULL, ' Zhangsan ', +, ' man ');

INSERT into Stu (sname,age,sex) VALUES (NULL, ' Zhangsan ', +, ' man ');

3, non-null constraints

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

For example:

CREATE TABLE stu (sid int PRIMARY KEY auto_increment, sname  VARCHAR () not NULL, age   INT, sex  VA Rchar (ten)); 

A non-null constraint was set on the Sname column.

4. Unique Constraint

Garage Some columns cannot set duplicate values, so you can add a unique constraint to the column.

For example:

CREATE TABLE stu (sid int PRIMARY KEY auto_increment, sname  VARCHAR) notNULL UNIQUE, age   int, sex  VARCHAR (ten)); 

Second, the conceptual model

1, the object model: In Java is domain, for example: User, Student.

2, relational Model: In the database table, 1 to many, 1 to 1, many to many.

Third, FOREIGN KEY constraints

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

1. Add foreign KEY constraint when creating

CREATE TABLE Dept (deptno INT PRIMARY KEY auto_increment, Dname VARCHAR (50) INSERT INTO Dept values (10, ' research and Development ' ), insert INTO Dept values (20, ' HR ' ), insert INTO Dept values (30, ' Finance Department '  '); CREATE TABLE emp (empno int PRIMARY KEY auto_increment, ename VARCHAR (), Deptno INT , CONSTRAINT fk_em P_dept FOREIGN KEY (DNO) REFERENCES Dept (DEPTNO)); CREATE TABLE Dept (deptno INT PRIMARY KEY auto_increment, dname VARCHAR ()); INSERT into Dept VALUES (10, ' research and development '  INSERT INTO Dept values (20, ' human 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 ', ten ); 
 
  INSERT into EMP (EMPNO,ENAME,DEPTNO) VALUES (null, ' Zhangsan ', + );/* Error code:1452. Cannot add or update a child row:a FOREIGN KEY constraint fails (' MYDB2 '. ' emp ', constraint ' fk_emp_dept ' foreign key (' De Ptno ') REFERENCES ' dept ' (' Deptno ')) */          

2. Add a foreign KEY constraint when modifying a table:

ALTER TABLE emp ADD CONSTRAINT fk_emp_dept FOREIGN KEY (DNO) refernces dept (DEPTNO);

Four, database relationship model

1. One-to-one relationship

It is special to establish a one-to-two relationship in a table, which requires that the primary key, which is the primary key, is 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 (),    CONSTRAINT Fk_wife_hasband FOREIGN KEY (WID)  REFERENCES Hasband (HID));

2. Many-to-many relationships

Establishing a many-to-many relationship in a table requires the use of an intermediate table, which requires three tables, and two foreign keys in the intermediate table, referencing the primary keys of the other two tables, respectively.

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 stude NT (SID),    ADD CONSTRAINT fk_stu_tea_tid FOREIGN KEY (tid)  REFERENCES Teacher (TID));

Establish relationships in the intermediate tables, such as:

INSERT into Stu_tea VALUES (5,1);

INSERT into Stu_tea VALUES (2,2);

INSERT into Stu_tea VALUES (3,2);

Five, multi-table query

1. Classification

• Merging result sets
• Connection Query
• Sub-query

2. Merge Results Query

• Required to be merged table, result set column has the same type and number of columns
• UNION, removing duplicate rows
• UNION all, do not remove duplicate rows

SELECT * FROM table 1

UNION All

SELECT * from table 2;

3. Connection Query

① classification

• Internal connection
• External connection
  • Left outer connection
  • Right outer connection
  • Full external connection (MySQL not supported)
  • Natural connections (in a simplified way)

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

Remove useless information from the Cartesian product with conditional filtering.

• 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 joins dept D on E.deptno=d.deptno;

• Nature: 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 within a connection query meet the criteria

③ External Connection

• Left outer: SELECT * FROM table 1 alias 1 left OUTER JOIN table 2 alias 2 on alias 1.xx= alias 2.xx;
  • The left table record will be queried whether or not it satisfies the condition, and the right table will only come out if it satisfies the criteria. The left table does not satisfy the condition of the record, and the right table section is null.

SELECT E.ename, E.sal, Ifnull (d.dname, ' no department ') as dname from emp E to OUTER JOIN dept D on E.deptno=d.deptno;

• Left outer nature: SELECT * FROM table 1 alias 1 NATURAL OUTER JOIN table 2 alias 2 on alias 1.xx= alias 2.xx;
• Right outside: SELECT * FROM table 1 alias 1 right OUTER JOIN table 2 alias 2 on alias 1.xx= alias 2.xx;
  • The right table record will be queried whether or not it satisfies the condition, and the left table will only come out if it satisfies the criteria. A record in the right table that does not meet the criteria, and the left table section is null.
• Right outside nature: SELECT * FROM table 1 alias 1 NATURAL OUTER JOIN table 2 alias 2 on alias 1.xx= alias 2.xx;
• Full link: You can use union to complete a full connection.

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, sub-query

Query in query (see Number of select keywords)

Where the ① appears

• Where after as condition exists
• exists as a table after from (multiple rows and columns)

② conditions

• Single-line: 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);

• Multi-row 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 > No (select Sal from emp where job= ' manager ');

• Single-row multi-column: SELECT * FROM table 1 aliases 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);

• Multiline Multi-column: SELECT * FROM table 1 alias 1, (SELECT ... Table 2 alias 2 WHERE condition;

MySQL constraints, multi-table queries, sub-queries