Database Basis (II)

1. Data constraints

Data constraints are constraints added to fields when creating tables. These constraints will restrict the addition, modification and deletion of data in this subsection. Different data constraints can be added according to different business requirements.

Default Value Constraints

Format: Field + Data Type + DEFAULT + Default Value
Add a default value to the field. When a row of data is inserted, if the field's value is not marked (null counts), the value is automatically initialized to the default value of the field.

-- Create tables and add default value constraints( name Default is'Zhang Xueyou')
CREATE TABLE test(
id INT,
NAME VARCHAR(20) DEFAULT 'Zhang Xueyou'
);
-- insert data
INSERT INTO test(id) VALUES (1);
INSERT INTO test(id,name) VALUES (2,null);
INSERT INTO test(id,NAME) VALUES (3,'Lau Andy');
-- Display data
SELECT * FROM test;

The results are as follows:

Non-empty constraints

Format: field name + data type + NOT NULL
When a non-null constraint is added to a field, the corresponding value cannot be null. If the value is not defined, the default is 0; if it is forcibly defined as null, the system will report an error when inserting data.

-- Create tables and insert non-null constraints( NOT NULL)
create table test(
id int not null,
name varchar(20)
);
-- insert data
insert into test(name) values('Lau Andy');
INSERT INTO test(id,NAME) VALUES(null,'Lau Andy');
-- Display data
select * from test;

Unique constraint

Format: Field + Data Type + UNIQUE
The data must be unique after the field is added with unique constraints. If the same data is added or a data is modified to the existing data in the table, the system will report an error.

-- Create tables and add unique constraints
CREATE TABLE test(
id INT UNIQUE,
NAME VARCHAR(20)
);
-- Insert data (normal insert)
INSERT INTO test(id,NAME) VALUES(1,'Lau Andy');
-- Report errors
INSERT INTO test VALUES(1,'Zhang Xueyou');
-- -------------------------------------
--Error reporting information:
//Error code: 1062
Duplicate entry '1' for key 'id'

Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:000

Primary key constraint

Format: Field + Data Type + PRIMARY KEY
Fields with primary key constraints have uniqueness and non-nullity. The uniqueness constraints mentioned above can have more than one empty data.

CREATE TABLE test(
id INT PRIMARY KEY,
NAME VARCHAR(20)
);
INSERT INTO test VALUES(1,'Nitrates');
INSERT INTO test VALUES(1,'nadeshiko');
-- Error code: 1062
-- Duplicate entry '1' for key 'PRIMARY'
-- -----------------------
INSERT INTO test VALUES(NULL,'Dumpling');
-- Error code: 1048
-- Column 'id' cannot be null

Self-growth Constraints

After adding the primary key constraint, a self-growth constraint can be added.
Format: Field + Data Type + PRIMARY KEY + AUTO_INCREMENT
Each time a row is added after the self-growth constraint is added, the database automatically assigns the field (which must be an integer type) with an initial value of 0 and an increment of 1 at a time.

CREATE TABLE test(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);

INSERT INTO test(NAME) VALUES('Nitrates');
INSERT INTO test(NAME) VALUES('nadeshiko');
INSERT INTO test(NAME) VALUES('Dumpling');
SELECT * FROM test;

Output results:

When DELETE is used to DELETE data, the self-increasing value will not be eliminated, and the corresponding field of the next added tuple will be + 1 on the basis of the previous assignment.
When truncate table is used to delete data, the initial value of self-growth can be set to 0.

TRUNCATE TABLE test;
INSERT INTO test(NAME) VALUES('Pancake');
SELECT * FROM test;

Output results:

Foreign key constraints

In order to reduce data redundancy, we usually try to abstract some data in the form of multiple tables. When there are constraints between tables, we need foreign key constraints to express such constraints.
Format: CONSTRAINT foreign key name FOREIGN KEY (foreign key field) REFERENCES main table (field)

CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,
deptname VARCHAR(20)
);

INSERT INTO dept(deptname) VALUES ('Xingxin');
INSERT INTO dept(deptname) VALUES ('Blue Rain');
INSERT INTO dept(deptname) VALUES ('Weeds');

CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
deptid INT,
CONSTRAINT emp_dept_fk FOREIGN KEY(deptid) REFERENCES dept(id)
);

INSERT INTO employee(NAME,deptid) VALUES ('Ye Xie',1);
INSERT INTO employee(NAME,deptid) VALUES ('Sumu Orange',1);
INSERT INTO employee(NAME,deptid) VALUES ('Huang Shaotian',2);
INSERT INTO employee(NAME,deptid) VALUES ('Yu Wenzhou',2);
INSERT INTO employee(NAME,deptid) VALUES ('Wang Jiexi',3);

SELECT * FROM employee;

insert into employee(name,deptid) values ('Sun Xiang',4);
-----------------------------
//Error code: 1452
Cannot add or update a child row: a foreign key constraint fails (`day20`.`employee`, CONSTRAINT `emp_dept_fk` FOREIGN KEY (`deptid`) REFERENCES `dept` (`id`))

Cascade constraints

Cascade: When we have foreign keys, we want to modify or delete data, modify or delete the data of the main table, while affecting the data of the secondary table, then we can use cascade.
Format: Foreign key constraint format + ON UPDATE CASCADE ON DELETE CASCADE
- Add cascading modifications: ON UPDATE CASCADE
Add Cascade Delete: ON DELETE CASCADE

-- Will the above employee Change the table
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
deptid INT,
CONSTRAINT emp_dept_fk FOREIGN KEY(deptid) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE
);

At this time, the query results are as follows:

DELETE FROM dept WHERE id=3;

update dept set id=3 where id=2;

2. Multi-table query

Step of multi-table query:
1) Determine which tables to query
2) Determine which fields to query
3) Determine the connection conditions (rules, number of tables)

Internal Connection Query

SELECT e.name,d.deptname
    FROM employee e,dept d 
    WHERE e.deptid=d.id;

Left External Connection Query

select e.name,d.deptname
    from dept d
    left outer join employee e
    on e.deptid=d.id;

Right External Connection Query

select e.name,d.deptname
    from dept d
    right outer join employee e
    on e.deptid=d.id;

3. MySQL stored procedures

Stored procedures in databases are similar to methods in programming languages

No parameters

delimiter $
create procedure test()
begin
select * from employee where id=2;
end$

With input and output parameters

CREATE PROCEDURE pro_testByInOut(INOUT n VARCHAR(20))
BEGIN
    -- See n variable
    SELECT n;
    -- modify n variable
    SET n = '500';
END $

-- Define session variables to call stored procedures
SET @n='100';
CALL pro_testByInOut(@n);
-- See n
SELECT @n;

4. Triggers

When inserting/modifying/deleting a data into the employee table and recording it in the log table, triggers are used.

create table emp_log(
    id int primary key auto_increment,
    content varchar(20)
);

create trigger tri_empAdd after insert on employee for each row
    insert into emp_log(content) values('The employee was inserted into a record.');

create trigger tri_empdel after delete on employee for each row
    insert into emp_log(content) values ('The employee was deleted from a record');