MYSQL basic operation-data constraints and associated queries, mysql basic constraints Association

MYSQL basic operation-data constraints and associated queries, mysql basic constraints Association

I. MYSQL Constraints

1. Default Value constraints. When a field does not have a value inserted, mysql automatically assigns a default value to the field.
The default field can be blank.
You can also insert null for the default field.

1 create table student (2 id int, 3 name varchar (20), 4 address varchar (20) DEFAULT 'jingkou district '5); 6 insert into student (ID, NAME) VALUES (1, 'zhang san'); 7 insert into student (ID, NAME, ADDRESS) VALUES (2, 'Li si', NULL );

 

2. Non-empty Constraint

1 CREATE TABLE STUDENT(2     ID INT,3     NAME VARCHAR(20),4     GENDER VARCHAR(2) NOT NULL5 );

 

2.1 non-empty fields must be assigned values (error display)

Insert into student (ID, NAME) VALUES (1, 'Lee 4 ');

 

2.2 null cannot be inserted (error display)

Insert into student (ID, NAME) VALUES (1, 'zhang san', NULL );

 

 

3. unique constraints

1 create table student (2 id int unique, 3 name varchar (20) 4); 5 insert into student (ID, NAME) VALUES (1, 'zhang san ');

 

The following statement execution error occurs.

Insert into student (ID, NAME) VALUES (1, 'Lee 4 ');

 

Error Message

4. primary key constraint (non-empty + unique). Generally, a primary key field is set for each table. Used to indicate the uniqueness of table records.
Primary keys generally have no business meaning.

1 create table student (2 id int primary key, 3 name varchar (20) 4); 5 6 insert into student (ID, NAME) VALUES (1, 'zhang san ');

The following statement execution shows an error.

Insert into student (ID, NAME) VALUES (NULL, 'Lee 4 ');

Error Message

The following error is also an error.

Insert into student (ID, NAME) VALUES (1, 'Lee 4 ');

5. Self-growth constraint
Auto Increment

Create table student (id int primary key AUTO_INCREMENT, name varchar (20); insert into student (NAME) VALUES ('zhang san'); insert into student (NAME) VALUES ('Li si ');

Delete the entire table without affecting the auto-increment deletion statement.

DELETE FROM STUDENT;

After the table is deleted, add the insert statement again (no more tables are created ).

This statement is also used to delete all data in the table.

DELETE FROM STUDENT;

Insert data again

6. Foreign key constraints

The bound table is a secondary table, and the foreign key is set on the secondary table.

Eg: Create an employee table (secondary table), create a department table (primary table), create a primary table, and then create a secondary table

Create table dept (id int primary key, deptid varchar (20); insert into dept (ID, DEPTNAME) VALUES (1, 'Software design authorization '); insert into dept (ID, DEPTNAME) VALUES (2, 'hr authorization'); insert into dept (ID, DEPTNAME) VALUES (3, 'Finance authorization'); insert into dept (ID, DEPTNAME) VALUES (4, 'operation'); create table employee (id int primary key AUTO_INCREMENT, empname varchar (20), deptid int, CONSTRAINT EMPLOYEE_DEPT_FK foreign key (DEPTID) references dept (ID); insert into employee (EMPNAME, DEPTID) VALUES ('zhang san', 1); insert into employee (EMPNAME, DEPTID) VALUES ('Li si ', 2); insert into employee (EMPNAME, DEPTID) VALUES ('wang wu', 3); insert into employee (EMPNAME, DEPTID) VALUES ('zhao liu', 4 );

Query two tables

7. Perform cascade join below, which means that when the primary table is modified, the secondary table is also modified.

Modify the statement for creating the preceding table and insert a new statement again.

1 drop table employee; 2 3 create table employee (4 id int primary key AUTO_INCREMENT, 5 empname varchar (20), 6 deptid int, 7 CONSTRAINT employee_dept_forfk foreign key (DEPTID) references dept (ID) on update cascade on delete cascade 8); 9 10 insert into employee (EMPNAME, DEPTID) VALUES ('zhang san', 1); 11 insert into employee (EMPNAME, DEPTID) VALUES ('Li si', 2); 12 insert into employee (EMPNAME, DEPTID) VALUES ('wang wu', 3); 13 insert into employee (EMPNAME, DEPTID) VALUES ('zhao liu', 4 );

Query

Modify a record of the master table

UPDATE DEPT SET ID=5 WHERE ID=4;

Main table running result

Sub-table running result

Bytes --------------------------------------------------------------------------------------

2. join query

1. Cross Query

SELECT * FROM EMPLOYEE,DEPT;

2. query internal connections

Multi-Table query rules: 1) which tables are queried; 2) which fields are determined; 3) query conditions between tables (number of connected tables-1)

1 SELECT EMPNAME , dept.DEPTNAME2         FROM EMPLOYEE3         INNER JOIN DEPT4         ON EMPLOYEE.DEPTID = DEPT.ID;

3. Use aliases

1 select empname as 'employee name', D. deptname as 'department '-- pay attention to D2 from employee as E3 inner join dept as D4 on e. DEPTID = D. ID;

4. For the left outer join query, the data on the left will be displayed completely. Write the left table before query.

First, modify the sub-table.

UPDATE EMPLOYEE SET DEPTID = NULL WHERE  ID= 4;

 

 

 

If it is an internal connection query, it is displayed

If the left join query

1 select d. DEPTNAME, E. EMPNAME2 from dept as d -- LEFT Table 3 left outer join employee as e -- right table 4 on d. ID = E. DEPTID;

 

The right join is actually the same as the left join, but the position of the query table is different. The right join is shown below, which is the same as the left join.

1 select d. DEPTNAME, E. EMPNAME2 from employee as e -- left Table 3 right join dept as d -- RIGHT table 4 on e. ID = D. ID;

 

5. Self-join query: The self-join query is generally applied to the Tree Structure of table data.

First, create a table and query it.

1 create table person (2 id int primary key AUTO_INCREMENT, 3 name varchar (20), 4 bossid int 5); 6 7 insert into person (NAME, BOSSID) VALUES ('zhang san', NULL); 8 insert into person (NAME, BOSSID) VALUES ('Li si', 1); 9 insert into person (NAME, BOSSID) VALUES ('wang wu', 2); 10 insert into person (NAME, BOSSID) VALUES ('zhao liu', 3); 11 insert into person (NAME, BOSSID) VALUES ('Lee 7', 3); 12 13 select p. name as 'boss', B. name as 'employee '14 from person as P15 right join person as B6 on p. ID = B. BOSSID;

 

Display result

Note the BOSSID structure of the table created above.