SQL Basic Content 2

--DAY16 Course Content--
CREATE DATABASE DAY16;
Use DAY16;

--*************, data constraint ********************----
--1.1 Default value
CREATE TABLE Student (
ID INT,
NAME VARCHAR (20),
Address VARCHAR Default ' Guangzhou Tianhe '--defaults
)

DROP TABLE student;
--MySQL automatically assigns a default value to the field when no value is inserted
INSERT into student (Id,name) VALUES (1, ' Zhang San ');

--NOTE: The default value of the field is allowed to be null
INSERT into student (id,name,address) VALUE (2, ' John Doe ', NULL);
INSERT into student (id,name,address) VALUE (3, ' Harry ', ' Guangzhou Panyu ');

SELECT * from student;

--1.2 Non-empty
--Requirements: Gender field must have a value (not NULL)
CREATE TABLE Student (
ID INT,
NAME VARCHAR (20),
Gender VARCHAR (2) NOT null--non-null
)

--Non-empty fields must be assigned values
INSERT into student (Id,name) VALUES (1, ' John Doe ');
--Non-null characters cannot insert null
INSERT into student (Id,name,gender) VALUES (1, ' John Doe ', NULL);

SELECT * from student;

--1.3 Unique
CREATE TABLE Student (
ID INT Unique,--the only unique
NAME VARCHAR (20)
)

INSERT into student (Id,name) VALUES (1, ' ZS ');
INSERT into student (Id,name) VALUES (1, ' Lisi '); --ERROR 1062 (23000): Duplicate entry ' 1 ' for key ' ID '

INSERT into student (Id,name) VALUES (2, ' Lisi ');

SELECT * from student;

--1.4 Primary KEY (non-null + unique)
DROP TABLE student;

CREATE TABLE Student (
ID INT PRIMARY Key,--primary key
NAME VARCHAR (20)
)

INSERT into student (Id,name) VALUES (1, ' Zhang San ');
INSERT into student (Id,name) VALUES (2, ' Zhang San ');
--INSERT into student (Id,name) VALUES (1, ' John Doe '); --Violation of the UNIQUE constraint: Duplicate entry ' 1 ' for key ' PRIMARY '

--INSERT into student (name) value (' John Doe '); --violation of non-null constraint: ERROR 1048 (23000): Column ' id ' cannot be null

--1.5 self-growth
CREATE TABLE Student (
ID INT (4) Zerofill PRIMARY KEY auto_increment,--self-growing, starting from 0 Zerofill 0 padding
NAME VARCHAR (20)
)

--Self-growth fields can be unassigned, automatically incremented
INSERT into student (NAME) VALUES (' Zhang San ');
INSERT into student (NAME) VALUES (' John Doe ');
INSERT into student (NAME) VALUES (' Harry ');

SELECT * from student;
--cannot affect self-growth constraints
DELETE from student;
--can affect self-growth constraints
TRUNCATE TABLE student;

--1.6 FOREIGN KEY constraints
--Employee table
CREATE TABLE Employee (
ID INT PRIMARY KEY,
EmpName VARCHAR (20),
Deptname VARCHAR (20)--Department name
)

INSERT into employee VALUES (1, ' Zhang San ', ' Software Development Department ');
INSERT into Employee VALUES (2, ' John Doe ', ' Software Development Department ');
INSERT into Employee VALUES (3, ' Harry ', ' Application Maintenance Department ');

SELECT * from employee;

--Add employee, department name data redundancy is high
INSERT into Employee VALUES (4, ' Chen VI ', ' Software Development Department ');

--solving the problem of high data redundancy: placing redundant fields in a separate table
--independent design of a departmental table
CREATE TABLE Dept (
ID INT PRIMARY KEY,
Deptname VARCHAR (20)
)

DROP TABLE employee;

--Modify Employee table
CREATE TABLE Employee (
ID INT PRIMARY KEY,
EmpName VARCHAR (20),
DeptID INT,--Change Department name to Department ID
--Declaring a FOREIGN KEY constraint
CONSTRAINT emlyee_dept_fk FOREIGN KEY (deptid) REFERENCES Dept (ID) on update CASCADE on DELETE CASCADE--on CASCADE UPDATE : Cascade Modification
--FOREIGN Key name foreign key reference table (reference field)
)

INSERT into Dept (id,deptname) VALUES (1, ' Software Development Department ');
INSERT into Dept (Id,deptname) VALUES (2, ' Application Maintenance Department ');
INSERT into Dept (Id,deptname) VALUES (3, ' secretary ');

INSERT into employee VALUES (1, ' Zhang San ', 1);
INSERT into Employee VALUES (2, ' John Doe ', 1);
INSERT into Employee VALUES (3, ' Harry ', 2);
INSERT into Employee VALUES (4, ' Chen VI ', 3);

--Problem: The record is not legal in business, employees insert a non-existent department data
INSERT into employee VALUES (5, ' Chen VI ', 4); --Violation of FOREIGN KEY constraint: Cannot add or update a child row:a FOREIGN KEY constraint fails (' DAY16 '. ' Employee ', constraint ' Emlyee_dept_f K ' FOREIGN KEY (' deptid ') REFERENCES ' dept ' (' ID '))

--1) When you have a FOREIGN key constraint, add the order of the data: Add the primary table first, and then add the secondary table data
--2) When you have a FOREIGN key constraint, modify the order of the data: Modify the secondary table first, and then modify the main table data
--3) When you have a foreign key constraint, delete the order of the data: delete the secondary table before deleting the main table data
--Modify the department (cannot directly modify the main table)
UPDATE Dept SET id=4 WHERE id=3;
--Modify the employee table first
UPDATE employee SET deptid=2 WHERE id=4;

--Delete Department
DELETE from dept WHERE id=2;

--Delete the employee table first
DELETE from employee WHERE deptid=2;

SELECT * FROM Dept;
SELECT * from employee;

--Cascade Modification (Modification)
--Direct modification of the department
UPDATE Dept SET id=5 WHERE id=4;

--Cascade Delete
--Delete the department directly
DELETE from dept WHERE Id=1;

--************** second, related query (multi-table query) ****************----
--Demand: query employees and their departments (Show employee name, department name)
--2.1 Cross-connect queries (not recommended.) Produce a Cartesian product: 4 * 4=16, some repeating records)
SELECT empname,deptname from Employee,dept;

--Demand: query employees and their departments (Show employee name, department name)
--Multi-table query rule: 1) determine which table (s) of the Query 2) determine which fields 3) The connection condition between table and table (rule: Number of join conditions is number of tables-1)
--2.2 Internal connection query: Only the results that meet the criteria are displayed (most frequently used)
SELECT Empname,deptname--2) determine which fields
From Employee,dept--1) determining which tables to query
WHERE Employee.deptid=dept.id--3) Join condition between table and table

--Another syntax for inner joins
SELECT Empname,deptname
From employee
INNER JOIN Dept
On Employee.deptid=dept.id;

--Using aliases
SELECT E.empname,d.deptname
From Employee E
INNER JOIN Dept D
On E.deptid=d.id;

--Requirements: Query employees in each department
-Expected results:
--Software development Department Zhang San
--Software Development Department John Doe
--Application and maintenance department Harry
--Chen Liu, Secretary department
--Total handling null
--2.2 left [outer] connection query: Use data from the table on the left to match the data on the right side of the table, if the results of matching join conditions are displayed, NULL if the connection condition is not met
--(Note: Left outer connection: The data of the left table is sure to complete the display!) ）
SELECT D.deptname,e.empname
From Dept D
Left OUTER JOIN employee E
On D.id=e.deptid;

--2.3 Right [outer] connection query: Use the data on the right table to match the data on the left table, if the results of matching join conditions are displayed, NULL is displayed if the join condition is not met
--(Note: Right outer connection: the data of the right table must be displayed!) ）
SELECT D.deptname,e.empname
From Employee E
Right OUTER JOIN Dept D
On D.id=e.deptid;

--2.4 self-connected queries
--Demand: query employees and their superiors
-Expected results:
--Zhang San null
--Li Shizhang
--Harry John Doe
--Chen Liu Harry
SELECT E.empname,b.empname
From Employee E
Left OUTER JOIN Employee b
On E.bossid=b.id;

SELECT * from employee;
SELECT * FROM Dept;
--Add Boss ID
ALTER TABLE employee ADD bossid INT;
UPDATE employee SET bossid=1 WHERE id=2;
UPDATE employee SET bossid=2 WHERE id=3;
UPDATE employee SET bossid=3 WHERE id=4;

--************** Three, stored procedure *******************-
--declaration Terminator
--Create a stored procedure
DELIMITER $
CREATE PROCEDURE pro_test ()
BEGIN
--can write multiple SQL statements;
SELECT * from employee;
END $

--Execute Stored procedure
Call Pro_test ();

--3.1 stored procedure with input parameters
--Requirements: Pass in an employee ID, query employee information
DELIMITER $
CREATE PROCEDURE Pro_findbyid (in Eid Int.)--In: Input parameters
BEGIN
SELECT * FROM employee WHERE Id=eid;
END $

--Call
Call Pro_findbyid (4);

--3.2 stored procedure with output parameters
DELIMITER $
CREATE PROCEDURE pro_testout (out str VARCHAR) – Out: Output parameters
BEGIN
--Assigning values to parameters
SET str= ' Helljava ';
END $

--Delete stored procedures
DROP PROCEDURE pro_testout;
--Call
--How to accept the value of the return parameter??
--***mysql variable ******
--Global variables (built-in variables): MySQL database built-in variables (all connections work)
--View All global variables: show variables
--View a global variable: select @@ 变量 Name
--Modify global variables: Set Variable name = new value
--Encoding of the received data of the CHARACTER_SET_CLIENT:MYSQL server
--Encoding of CHARACTER_SET_RESULTS:MYSQL server output data

--session variable: exists only in one connection between the current client and the database server side. If the connection is broken, then all session variables are lost!
--Define Session variables: SET @ variable = value
--View Session variables: SELECT @ variable

--Local variables: variables used in stored procedures are called local variables. Local variables are lost as long as the stored procedure is executed!!

--1) Define a Session variable name, 2) use the name session variable to receive the return value of the stored procedure
Call Pro_testout (@NAME);
--View variable values
SELECT @NAME;

--3.3 stored procedure with input and output parameters
DELIMITER $
CREATE PROCEDURE pro_testinout (INOUT n INT)--INOUT: input and output parameters
BEGIN
--View variables
SELECT N;
SET n = 500;
END $

--Call
SET @n=10;

Call Pro_testinout (@n);

SELECT @n;

--3.4 stored procedures with conditional judgment
--Demand: Enter an integer if 1, then return "Monday" if 2, return "Tuesday", if 3, return "Wednesday". Other numbers, return "error input";
DELIMITER $
CREATE PROCEDURE pro_testif (in num int,out str VARCHAR (20))
BEGIN
IF Num=1 Then
SET str= ' Monday ';
ELSEIF num=2 Then
SET str= ' Tuesday ';
ELSEIF Num=3 Then
SET str= ' Wednesday ';
ELSE
SET str= ' input error ';
END IF;
END $

Call Pro_testif (4, @str);

SELECT @str;

--3.5 stored procedures with loop function
--Requirements: Enter an integer and sum. For example, enter 100, statistic 1-100, and
DELIMITER $
CREATE PROCEDURE pro_testwhile (in num int,out result INT)
BEGIN
--Define a local variable
DECLARE i INT DEFAULT 1;
DECLARE vsum INT DEFAULT 0;
While I<=num do
SET vsum = vsum+i;
SET i=i+1;
END while;
SET result=vsum;
END $

DROP PROCEDURE Pro_testwhile;

Call Pro_testwhile (@result);

SELECT @result;

Use DAY16;

--3.6 Assigning a value to a variable using the query's result (into)
DELIMITER $
CREATE PROCEDURE Pro_findbyid2 (in Eid int,out vname VARCHAR (20))
BEGIN
SELECT EmpName to VName from employee WHERE Id=eid;
END $

Call Pro_findbyid2 (1, @NAME);

SELECT @NAME;

Use DAY15;

SELECT * from Student2;

--Exercise: writing a stored procedure
If the average student's English score is less than or equal to 70, the output is ' general '
If the average student's English score is greater than 70 and less than or equal to 90, the output is ' good '
If the average student's English score is greater than 90, the output is ' excellent '

DELIMITER $
CREATE PROCEDURE Pro_testavg (out str VARCHAR (20))
BEGIN
--Define local variables, receive average points
DECLARE Savg DOUBLE;
--Calculation of English square
SELECT AVG (中文版) to Savg from Student2;
IF Savg<=70 Then
SET str= ' General ';
ELSEIF savg>70 and Savg<=90 Then
SET str= ' good ';
ELSE
SET str= ' excellent ';
END IF;
END $

Call Pro_testavg (@str);

SELECT @str;

--************ IV, Trigger *****************
SELECT * from employee;

--Log table
CREATE TABLE Test_log (
ID INT PRIMARY KEY auto_increment,
Content VARCHAR (100)
)

--Requirements: when inserting a record into an employee table, I want MySQL to automatically insert data into the log table at the same time
--Create a trigger (add)
CREATE TRIGGER Tri_empadd After insert on employee for each ROW--when a record is inserted into the employee table
Insert into Test_log (content) VALUES (' Employee table insert a record ');

--Inserting data
INSERT into employee (Id,empname,deptid) VALUES (7, ' Zangus ', 1);
INSERT into employee (Id,empname,deptid) VALUES (8, ' Staples 2 ', 1);

--Create a trigger (modify)
CREATE TRIGGER tri_empupd after UPDATE on employee for each ROW--when a record is modified to the employee table
INSERT into Test_log (content) VALUES (' Employee table modified a record ');

--Modification
UPDATE employee SET empname= ' Eric ' WHERE id=7;

--Create TRIGGER (delete)
CREATE TRIGGER Tri_empdel After delete on employee for each ROW--when a record is deleted to the employee table
INSERT into Test_log (content) VALUES (' Employee table delete a record ');

--Delete
DELETE from employee WHERE id=7;

SELECT * from employee;
SELECT * from Test_log;

--***********, MySQL permissions problem ****************
--MySQL Database permissions problem: Root: Have all permissions (can do anything)
--rights account, only partial permissions (curd) For example, you can only manipulate a table of a database
--How do I change the MySQL user password?
--PASSWORD:MD5 encryption function (one-way encryption)
SELECT PASSWORD (' root '); --*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B

--MySQL database, User configuration: Users table
Use MySQL;

SELECT * from USER;

--Change Password
UPDATE USER SET password=password (' 123456 ') WHERE user= ' root ';

--Assigning rights to accounts
GRANT SELECT on Day16.employee to ' Eric ' @ ' localhost ' identified by ' 123456 ';
GRANT DELETE on Day16.employee to ' Eric ' @ ' localhost ' identified by ' 123456 ';

--****** six, MySQL backup and restore ********

SQL Basic Content 2