Goodbye database

Keywords: Database Stored Procedure MySQL SQL

Data constraints

1. Data constraints: Adding some data constraints to the table can achieve the effect of constraining users to manipulate data.

2. default constraints
When no value is added to the field, a default value is given. If the value added to the field constrained by the default value is null, its field value is null.
Create a stu table

CREATE TABLE stu(
id INT,
NAME VARCHAR(20),

- Add a default value constraint to the gender gender field

gender VARCHAR(2) DEFAULT 'male'
);

- Add a few data to the stu table

INSERT INTO stu VALUES(1,'Zhang Ba','male');
INSERT INTO stu(id,NAME) VALUES(2,'Zhao 9');

Insert data into the stu table, gender null

INSERT INTO stu VALUES(3,'Liu Shi',NULL);

3. Not null: The field inserted is not null, and data must be inserted.

CREATE TABLE stu(
-- to stu In the table id Field adds a non-null constraint
id INT NOT NULL,
NAME VARCHAR(20),
gender VARCHAR(2)
);

- Add an element to the table without inserting the value of the id field

INSERT INTO stu(NAME,gender) VALUES('Guo Degang','male');

- Add a data to the table, and the value of the id field is given directly to null, which is not inserted.

4. Unique Constraints
- Add a unique constraint to the stu table

CREATE TABLE stu(
-- to stu In the table id Field Adding Unique Constraints
id INT UNIQUE,
NAME VARCHAR(20),
gender VARCHAR(2)
);

Insert two id entical IDS into the table

INSERT INTO stu VALUES(1,'Lau Andy','male');
INSERT INTO stu VALUES(1,'Zhang Xueyou','male');-- Duplicate entry '1' for key 'id',Unable to insert duplicate id value

- insert a null data into the table, and when a unique constraint is added to the id, multiple null values can still be inserted into it without duplication.

INSERT INTO stu VALUES(NULL,'Wu Qilong','male');
INSERT INTO stu VALUES(NULL,'Liu Shishi','female');

5. Primary key
After our analysis, we determined that our id field (unique + non-empty) - primary key
Note:
(1) Generally speaking, we need to set a primary key field (non-empty + unique) for each table to indicate the uniqueness of an information;
(2) Generally, we do not set business fields as primary key fields, such as name fields. Generally, we add an id field to each table as primary key fields.
(3) It is suggested to add a primary key field to each table to indicate the uniqueness of each data.

CREATE TABLE stu(
-- to stu In the table id Fields are set as primary keys (unique)+Non-empty)
id INT PRIMARY KEY,
NAME VARCHAR(20),
gender VARCHAR(2)
);

Insert two student information with id 1 into the table

INSERT INTO stu VALUES(1,'Andy Lau','male');
INSERT INTO stu VALUES(1,'Huajian','male');-- Duplicate entry '1' for key 'PRIMARY'

Insert null-id elements into the table

INSERT INTO stu VALUES(NULL,'Jielun','male');-- Column 'id' cannot be null

From the above experiments, we can conclude that when we set primary key constraints on the id field, the id field is not empty + unique.

6. Auto_increment
- Add a primary key self-growth constraint to the id field in the stu table

CREATE TABLE stu(
-- to stu In the table id Fields set primary key self-growth constraints, we will actually id This field is handed over to the database itself for maintenance. We don't need to touch it ourselves.
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
gender VARCHAR(2)
);

- Add two data to the stu table

INSERT INTO stu(NAME,gender) VALUES('Andy Lau','male');
INSERT INTO stu(NAME,gender) VALUES('Jay Chou','male');
INSERT INTO stu(NAME,gender) VALUES('Jay Chou','male');
INSERT INTO stu(NAME,gender) VALUES('Jay Chou','male');

- Delete data with id 4

DELETE FROM stu WHERE id=4;

- Add a data to the table

INSERT INTO stu(NAME,gender) VALUES('Zhang Xueyou','male');

delete from this way of deleting data, you can't reset the self-growing primary key

- Delete stu's full table data

DELETE FROM stu;

- Add a data

INSERT INTO stu(NAME,gender) VALUES('Zhang Xueyou','male');

- Delete the truncate table name of the full table data, delete the full table data, this way of deleting the full table data can reset the primary key

TRUNCATE TABLE stu;
-- Add a data to the table
INSERT INTO stu(NAME,gender) VALUES('Andy Lau','male');

7. Foreign key constraints
(1) Foreign key constraints: constraints on two types of tables, such as redundant fields of department names in the employee table.

(2) How to solve the problem of redundancy of department names?
Answer: Design a department table independently and put the Department name in the Department table. This is the employee table only needs the id of the relevant department.

(3) When inserting the Department ID of the employee table, there may be a non-existent Department id, which is illegal data.

(4) How to prevent the insertion of illegal data?
Answer: This is a way to set the Department id of the employee table as a foreign key constraint.

(5) When there are foreign key constraints, the order of operation data is as follows:
Insert data: First insert data from the main table, then insert data from the secondary table.
Modify data: first modify the main table data, then modify the secondary table data.
Delete data: First delete the secondary table data, then delete the main table data

Employee Statement

CREATE TABLE employee(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    deptName VARCHAR(20)
)


INSERT INTO employee(NAME,deptName) VALUES('Zhang San','Software Development Department');
INSERT INTO employee(NAME,deptName) VALUES('Li Si','Software Maintenance Department');
INSERT INTO employee(NAME,deptName) VALUES('Wang Wu','Software Development Department');
INSERT INTO employee(NAME,deptName) VALUES('Chen Liu','Software Development Department');

Problem: Every time you insert employee data, the Department name data will be duplicated (redundant), and if the data is redundant, it will waste database storage space.
How to solve the problem of redundancy of department name data? Then we can design an independent department table and put the Department name in the Department table.

Employee Statement

CREATE TABLE employee(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    deptId INT
)

Departmental Tables ___________

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

SELECT * FROM employee;
SELECT * FROM dept;

INSERT INTO dept(NAME) VALUES('Software Development Department');
INSERT INTO dept(NAME) VALUES('Software Maintenance Department');
INSERT INTO employee(NAME,deptId) VALUES('Chen Liu',1);
INSERT INTO employee(NAME,deptId) VALUES('Wang Wu',1);

INSERT INTO employee(NAME,deptId) VALUES('Zhang San',3);

Question: When inserting the Department ID of the employee table, insert the non-existent Department id, how to prevent the illegal data insertion, then add foreign key constraints.

- Adding foreign key constraints
Requirement: The value of the deptId field comes from the value of the id field of the dept table, at which point foreign key constraints can be added to the deptId field.
Employee Schedule (sub-table: Constrained by other tables). The foreign key is set in the secondary table.

CREATE TABLE employee(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    deptId INT,
    CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)
    --          Foreign key name                     Foreign key field reference          
)

Departmental Tables (Main Tables: Tables Constraining Others)

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

SELECT * FROM employee;
SELECT * FROM dept;

INSERT INTO employee(NAME,deptId) VALUES('Chen Liu',1);
INSERT INTO employee(NAME,deptId) VALUES('Wang Wu',1);

Insert data

INSERT INTO employee(NAME,deptId) VALUES('Zhang San', 3); -- Violation of foreign key constraints: a foreign key constraint fails

- Modify data

UPDATE employee SET deptId=5 WHERE id=2; -- a foreign key constraint fails

- Delete data

DELETE FROM dept WHERE id=1; -- a foreign key constraint fails

(6) How do you manage data when you have foreign keys?
Insert data: first insert data from the main table, then insert data from the secondary table.
Modify data: First modify the main table data, then modify the secondary table data

UPDATE employee SET deptId=3 WHERE id=5; 
UPDATE dept SET id=3 WHERE id=2;

Delete data: first delete the secondary table data, then delete the main table data

DELETE FROM dept WHERE id=3;
DELETE FROM employee WHERE deptId=3;

8. Cascade Technology
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.

CREATE TABLE employee(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    deptId INT,
    -- Add cascading modifications: ON UPDATE CASCADE
    -- Add cascade deletion: ON DELETE CASCADE 
    CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE 
    --          Foreign key name                     Foreign key field reference          
)

Database Design (A Brief Introduction)

1. Introduction

Demand analysis: Original Requirements - > Business requirements

Customer Business Model: Customer (Name, Registration Time Mailbox, Microsignal)

Demand design:

Business Model (Customer Business Model): -> Extract Entity Model: class Customer{name email weixin}

Business Model (customer Business Model) - > Data Model: Design Database Table (customer Table: Field)

2. Three paradigms of database

The first paradigm: 1NF is an atomic constraint on attributes, which requires attributes to be atomic and can not be decomposed again.
The second paradigm: 2NF is a constraint on the uniqueness of records, requiring that records have a unique identity, that is, the uniqueness of entities;
The third paradigm: 3NF is a constraint on field redundancy, that is, no field can be derived from other fields, it requires no field redundancy.

First paradigm: Require each field in a table to be a separate, non-separable field
 -- student table
 - id name (used name | current name) age
 - 1 Zhang Xiang | Zhang Han30
 - 2 Wang Yong | Zhang Gang 40
 Demand: Inquire about students who used to use Zhang's name
 - select * from student where name like'Zhang%';
To follow the first paradigm, we can modify the student table above.
-- id   oldName   nowName    age
 - Zhang Xiang, Zhang Hao 30
 - 2 Wang Yongzhang Gang 40

The second paradigm: a table can only express one meaning
-- student
 - ID name age commodity ID (commodity id)
- 1 Guo Degang 401

In order to satisfy the second paradigm, we have to create a new commodity list here.
-- product table
 Commodity id commodity name
 - 1 paper diapers
 - 2 razor

-- student table
-- id    name    age

- The third paradigm: Require that each field in a table has only a decisive relationship with the primary key
 The third paradigm is not satisfied with employee schedules.
- employee id name department id department name
 - 1 Guo Degang 1 Software Development Department
 - 2 Yue Yunpeng 2 Software Maintenance Department
 - 3 Lau Dehua 3 Logistics Department

The table structure above does not satisfy the third paradigm, so we reformed it and divided it into two tables.
- Employee Statement
-- id    name  deptId

- Department chart
-- id   deptName

When we reduce data redundancy, we will form multiple tables. When we query data, are we a table to query data fast?
Or is it faster to query data from multiple tables?
- Conclusion: There is a contradiction between storage space and your query efficiency. When you reduce the redundancy of data, your query efficiency will decrease.
When the storage space of data is large, we don't care about the problem of data redundancy, but the query efficiency is high.

Multi-table query

1. Cross-join query

 Requirements: Query the name of each employee and the corresponding department name
 - Expected results:
- Name Department Name
 Huazi Software Development Department
 Guo Degang Software Maintenance Department
SELECT NAME,deptName 
FROM employee,dept;

Analysis: The actual result is different from what we expected. The actual result is that each member of our team will appear corresponding to the name of each department. This is because there is no join condition for adding multiple tables.
The result is commonly known as Cartesian product. The formula is that the name field of each member is multiplied by the deptName field of the Department table.

1. 1: Conditions for multi-table queries

(1): Determine which tables need to be queried
(2): Determine which fields need to be queried
(3): Adequate connection conditions are required (data volume of connection conditions - 1)

2. Internal join query (most used): When querying, only the results satisfying the link conditions can be displayed.

Requirements: Query the corresponding department names of employees

SELECT e.name,d.deptName
-- Table to be queried
FROM employee e,dept d
-- Connection conditions
WHERE e.deptId=d.id;


-- Another way of writing inner links
SELECT NAME,deptName
FROM employee e
INNER JOIN dept d
ON e.deptId=d.id;

3. Left Outer Link: Display the left table first, show it when the connection condition is satisfied, and show the result as null if not.

The table on the left of LEFT OUTER JOIN is called the left table, and the table on the right is called the right table.
Requirements: Query the corresponding department names of employees

SELECT deptName,NAME
FROM dept d
LEFT OUTER JOIN employee e
ON e.deptId=d.id;

4. Right External Connection: Priority is given to the right table. If the condition is satisfied, the result is satisfied. If the condition is not satisfied, null is displayed.

RIGHTER OUTER has left table on the left and right table on the right.
Requirements: Query the corresponding department names of employees

SELECT deptName,NAME
FROM employee e
RIGHT OUTER JOIN dept d
ON e.deptId=d.id;

5. Self-Connecting Query

Insert the bossId field into the employee table. Each employee corresponds to a boss.
Requirements: Query the name of each employee's boss

ALTER TABLE employee ADD COLUMN bossId INT;

SELECT e.name,b.name   
FROM employee e,employee b
WHERE e.bossId=b.id;
Only the results satisfying the conditions can be queried.

When using an inner join: We essentially use a table, and in order to satisfy the link conditions, we will virtual out a table, employee b. of the above code.
Revamping the above code by using the left outer link

SELECT e.name,b.name
FROM employee e
LEFT OUTER JOIN employee b
ON e.bossId=b.id;

Stored procedures (similar to Java The method in the Chinese version of ____________

Stored procedure: A logical statement consisting of multiple sql statements, namely sql programming.
The characteristics of stored procedures:
1. Save in MySQL The server side of the database
2. We can send instructions directly to the client to call our stored procedures on the server side.
3. The portability of stored procedures is very poor

When we need to program an employee's information, we can

SELECT * FROM employee WHERE id=2;
SELECT * FROM employee WHERE id=3;
SELECT * FROM employee WHERE id=4;

This is very cumbersome, so we can extract the above procedure into a stored procedure and then call it.

(1) Grammar of stored procedures

-- delimiter $-- Declares a terminator
 - create procedure pro_Test (input or output parameters)
-- begin
 - sql statements with logic
-- end$
The above is the basic format.

- Call stored procedures:
- call pro_Test (argument);
How do I represent the input and output parameters of stored procedures?
-- in input parameter variable name type
 -- Name type of out output parameter
 -- Input and output parameter name type of inout

(2) Create a stored procedure with output parameters and call it

DELIMITER $
CREATE PROCEDURE pro_QueryEmpById(IN eId INT)
BEGIN
SELECT * FROM employee WHERE id=eId;
END$

-- Call the above stored procedure with input parameters
-- Requirements, queries id by4Employee Information
CALL pro_QueryEmpById(4);

(3) When we call a stored procedure for an output parameter, there will always be a return value. What should we use to accept the return value and what variables in the database can be used by us?

-- 1.global variable(mysql System variables of database): along with mysql The database starts and exists, along with mysql Closing of database 2 disappears
-- See mysql Global variables of database
SHOW VARIABLES;
-- See mysql All variables in the database are character-related
SHOW VARIABLES LIKE 'character%';
-- How to View mysql Global variables of database
-- select @@+Global variable name
-- How to change the global variables of database
-- set @@+global variable=value
SELECT @@character_set_client;
SET @@character_set_client='utf8';

-- 2.Conversation variables: They exist in a conversation and disappear with the end of the conversation
-- How to view a session variable
-- select @+Variable name 
-- Set values for session variables
-- set @Variable name=value
SET @n='hello';
SELECT @n;


-- 3.Local variable: Located in the stored procedure, it exists with the stored procedure and disappears with the call of the stored procedure.
-- Set values for local variables
-- set local variable=value
-- View local variables
-- select local variable

(4) Create a stored procedure with output parameters

DELIMITER $
CREATE PROCEDURE pro_TestOut(OUT str VARCHAR(20))
BEGIN
 - assign values to output parameters
 SET str='I am the output parameter';
END$
When this stored procedure is called, it returns a string value
 We should define a call variable to accept this value.
CALL pro_TestOut(@nn)
SELECT @nn;

(5) Create a stored procedure with judgment

-- Requirements: Enter one num Integer, num=1 ,output'Monday',num=2,output'Tuesday'，
-- num=3,output'Wednesday'，Otherwise, output'bad parameter'
DELIMITER $
CREATE PROCEDURE pro_TestDay(IN num INT,OUT d VARCHAR(20))
BEGIN
    IF num=1 THEN
        SET d='Monday';
    ELSEIF num=2 THEN
        SET d='Tuesday';
    ELSEIF num=3 THEN
        SET d='Wednesday';
    ELSE 
        SET d='Parameter error';
    END IF;
END$

-- Call the above stored procedure with input and output parameters
CALL pro_TestDay(4,@d);
SELECT @d;

(6) Create a stored procedure with a loop procedure

-- Requirements: Enter one num，Computation from1reach num Total.
DELIMITER $
CREATE PROCEDURE pro_TestSum(IN num INT,OUT score INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE res INT DEFAULT 0;
    WHILE i<=num DO
    SET res=res+i;
    SET i=i+1;
    END WHILE;
    -- Local variables res Value assignment score
    SET score=res;
END$

-- Call the stored procedure above
CALL pro_TestSum(200,@score);
SELECT @score;

(7) To carry data from the database to the output parameter is essentially to assign one parameter to another.

-- Requirements: Importing staff id，Query the corresponding employee and output the employee's name
DELIMITER $
CREATE PROCEDURE pro_QueryNameById(IN eId INT,OUT eName VARCHAR(20))
BEGIN
SELECT NAME INTO eName FROM employee WHERE id=eId;
END$
-- Call this stored procedure
CALL pro_QueryNameById(2,@eName);
SELECT @eName;

(8) Delete stored procedures
drop + stored procedure name

DROP PROCEDURE pro_QueryEmpById;

Trigger

When we insert, delete and modify data into a table, we can record these operations on a table, which requires triggers.

-- Create a log table
CREATE TABLE empLog(
id INT PRIMARY KEY AUTO_INCREMENT,
content VARCHAR(20)
);
-- Create a trigger that automatically adds an inserted record to the log table when inserting a data into the employee table
CREATE TRIGGER tri_empInsert AFTER INSERT ON employee FOR EACH ROW 
INSERT INTO empLog(content) VALUES('Insert a data into the employee table');
-- Insert a data into the employee table
INSERT INTO employee(NAME) VALUES('Zhou Huajian');

-- Create a trigger to add a record to past metrics when modifying a data in the employee table
CREATE TRIGGER tri_empUpdate AFTER UPDATE ON employee FOR EACH ROW 
INSERT INTO empLog(content) VALUES('A modified data in the employee table');
-- Modify a piece of data in the employee table
UPDATE employee SET NAME='Jackie Chan' WHERE id=7;


-- Create a trigger that adds a day's deleted record to the log table when deleting a piece of data from the employee table
CREATE TRIGGER tri_empDelete AFTER DELETE ON employee FOR EACH ROW 
INSERT INTO empLog(content) VALUES('A data deleted from the employee table');
-- Delete Zhou Huajian
DELETE FROM employee WHERE id=7;

SELECT * FROM empLog;
SELECT * FROM employee;

Note: The functions of each trigger are different. We must record different operations with different triggers.

Question of database privileges

1. root User: Our Super Administrator, has all the permission management, it can add, delete and check our (database, table, data).
Query user information of mysql database

SELECT * FROM USER;

When we look at our user password, we find that it is not our original password. This is because the database uses the MD5 one-way encryption algorithm for our password.
The encryption function is password();

SELECT PASSWORD('root');

How to modify our password information

-- change root Password for this username'123456'
UPDATE USER SET PASSWORD=PASSWORD('root') WHERE USER='root';

Note: The first password is a variable defined, and the second password is our password function.
4. We can also assign different privileges to some users to facilitate the management of our database.

-- What about creating a new user and giving new users some unique permissions?
GRANT SELECT ON day20.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';

Posted by duk on Mon, 24 Jun 2019 17:49:33 -0700

Programmer Group