Nature of database:

• A data storage method that can permanently save and query fast data storage.

Three connection methods of database server:

1. Open through management service

2. Open through the graphical user interface (navicat).

3. Open through the upper triangle of the taskbar.

  Diagram:

Database:

 table:

1. Each row is called a record

2. Each column is called a field

3. Generally, you need to specify how many columns there are, and you don't need to specify how many rows there are

  Basic commands for database operation:

1. Display all database files on the database server: show databases database names

2. Create database: create database database name

3. Delete the database; drop database database name

4. Switch database: use database name

  Graphical user interface creation:

  SQL syntax:

1. Keywords are case insensitive and generally lowercase

2. sql statements cannot be run repeatedly

Classification of SQL syntax:

1. Data Definition Language (DDL data definition language) such as: building databases and tables.

2. Data Manipulation Language(DML), such as adding, deleting and modifying records in a table.

3. Data Query Language(DQL), such as query operations on tables.

4. Data Control Language(DCL), such as setting of user permissions.

Data type:

DDL:

  Create table:

be careful:

1. The data type of date can be passed to string. When comparing dates, you can compare strings.
2. For fields, multiple constraints can exist at the same time, such as stu_id .

Copy table contents:

View table structure (header):

desc table name;

Delete table:

drop table if exists table name;

  Modify table structure (alter):

  Note: This is a column operation.

DDM: (modify record)

Add: (insert into---values)

After specifying the primary key sequence, when adding it again, it will start from the specified primary key sequence  .

  Modify: (update---set)

  Delete: (delete from---where)

  Note: if the where filter condition is not added during deletion, the entire table will be deleted, and the deletion preserves the primary key sequence (that is, if a new row is added, the primary key will not start from the beginning, but follow the original sequence)

truncate does not retain the primary key sequence:

  Logical deletion:

  Data constraints:

  Note: null means that there is no value and there is no duplication

 Add constraints when creating tables: (multiple constraints can decorate one)

For columns with self incrementing primary key (only integer int), when all elements are inserted, the primary key is null.

Operate primary key in existing table:

--Delete the primary key of st5 table

alter table st5 drop primary key;

--Add primary key

alter table st5 add primary key(id);

  Foreign key constraints:

1. What it is: a foreign key is a way to create a direct relationship between a table and a table. A column of a foreign key connected slave table can only be one of the primary key values of the primary table. There can only be one master table and multiple slave tables. Foreign keys are the bridge between master tables and slave tables.
2. Create foreign key when creating table: [CONSTRAINT] [foreign key CONSTRAINT name] foreign key REFERENCES   Primary table name (primary key field name)
3. Create FOREIGN KEY in existing table: ALTER TABLE   From the table ADD [CONSTRAINT] [FOREIGN KEY constraint name] FOREIGN KEY REFERENCES   Primary table (primary key field name);
4. Cascade operation: when modifying and deleting the primary key of the primary table, the foreign key value of the secondary table is updated or deleted at the same time, which is called cascade operation

Cascading operation syntax	describe
ON UPDATE CASCADE	Cascading updates can only create cascading relationships when creating tables. Update the primary key in the main table and remove the foreign key from the table Columns are also automatically updated synchronously
ON DELETE CASCADE	cascading deletion
ON DELETE SET NULL	Set the foreign key field from the table to NULL when deleting

  Foreign key deletion: ALTER TABLE   drop foreign key from table   Foreign key constraint name;

Example: (foreign keys are created from above the table)

Create master table B:

  Create from table A: (cascade delete and cascade update are set)

  Insert value from table:

  Delete:

DQL  : (query)

  Basic syntax:

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
  [left | right | inner join table_name2]  -- Joint query
  [WHERE ...]  -- Specify the conditions to be met for the results
  [GROUP BY ...]  -- Specify which fields the results are grouped by
  [HAVING]  -- Secondary conditions that must be met to filter grouped records
  [ORDER BY ...]  -- Specifies that query records are sorted by one or more criteria
  [LIMIT {[offset,]row_count | row_countOFFSET offset}];
   -- Specify which records to query from
 be careful : [ ] Brackets represent optional , { }Parentheses are required

  Difference between where and having: where is to filter according to conditions before splitting calculation. Having is to filter according to the table requirements during consolidation.

 -- Query the average score of different courses,Highest score,Lowest score
 -- premise:Group according to different courses
 
 SELECT subjectname,AVG(studentresult) AS average,MAX(StudentResult) AS Highest score,MIN(StudentResult) AS Lowest score
 FROM result AS r
 INNER JOIN `subject` AS s
 ON r.subjectno = s.subjectno
 GROUP BY r.subjectno
 HAVING average>80;
 
 /*
 where Write before group by
 If the filter is placed after the group
 To use HAVING
 Because having filters from the previously filtered fields, and where filters directly from the fields in the data table
 */

  Distinct keyword (de duplication) select distinct sex from emp; (those with the same gender will be deleted)

  like keyword: (fuzzy query)

SELECT * FROM   Table name WHERE   Field name LIKE 'wildcard string';

%	Match multiple characters
_	Match one character

Descending and ascending: (similar to prepositional phrases)

• asc: ascending order;
• desc: descending order;

  Combined sort: sort the items in parallel according to the former first. If the former is the same, then sort according to the latter.

desc: (another meaning is to view the table structure)

  Aggregate function query: (vertical query = = > used to count the number)

Five aggregate functions:

Aggregate functions in SQL	effect
Max (column name)	Find the maximum value of this column
Min (column name)	Find the minimum value of this column
AVG (column name)	Find the average of this column
Count (column name)	Count how many records there are in this column
Sum (column name)	Sum this column

  Note: if a column in the statistics has NULL (empty), the function will not be included in the statistics. For statistical count (*), only if all of a row are empty, it will not be counted.

About null operation: the result of null and all other data calculation is still null, so in some cases, it is necessary to convert null into 0 or 1. Use ifnull (a, b) statement. When false, this is a, and when true, this is B;

  group by - (similar prepositional phrase)

  Note: where precedes group by.

Paging query: limit x, y (where X represents the starting page of this page and Y represents the number of records on this page) (similar to prepositional phrases)

 ？？？？？？ Note: the index starts at 0.

  The following is to arrange the whole table in descending order of hieredate, and then page it.

Index: (speed up query) look at this: Crazy God says MySQL learning notes_ Straw hat Luffy caused by interest - CSDN blog_ Crazy God said MySQL noteshttps://blog.csdn.net/weixin_43591980/article/details/109526825

effect:

• Improve query speed
• Ensure data uniqueness
• The connection between tables can be accelerated to realize the referential integrity between tables
• When using grouping and sorting clauses for data retrieval, the time of grouping and sorting can be significantly reduced
• Full text search field for search optimization

  Create index manually:

 DCL:

We now use the root user and super administrator by default, with all permissions. However, the database server in a company

A database that may run many projects at the same time. Therefore, we should be able to establish different users according to different projects and assign different permissions to manage and maintain the database.

• Syntax:

CREATE USER 'username' @ 'hostname' IDENTIFIED BY 'password';

• Keyword Description:

keyword	explain
'user name'	User name to be created
'host name'	Specify the host on which the user can log in. If it is a local user, you can use localhost. If you want the user to log in from any remote host, you can use the wildcard%
'password'	The login password of the user. The password can be blank. If it is blank, the user can log in to the server without a password

demonstration:

#To create a user1 user, you can only log in to the mysql server on the localhost server with a password of 123 create user 'user1'@'localhost' identified by '123'; #Create user2. The user can log in to the mysql server on any computer with a password of 123 create user 'user2'@'%' identified by '123';

Note: the created user names can be viewed in the user table in the mysql database, and the password is encrypted.

• Delete user syntax:

DROP USER 'username' @ 'hostname';

demonstration:

#Delete user user2 drop user 'user2'@'%';

1. to grant authorization

• After the user is created, there is no permission! User authorization is required

GRANT   Permission 1,   Permission 2... ON   Database name. Table name TO 'user name' @ 'host name';

• Keyword Description:

keyword	explain
GRANT...ON...TO	Authorization keyword
jurisdiction	Permissions granted to users, such as CREATE, ALTER, SELECT, INSERT, UPDATE, etc. If you want to grant If ALL permissions are granted, use ALL
Database name. Table name	Which tables in which database can the user operate. If you want to grant the user corresponding operation permissions on all databases and tables, it can be represented by *, such as **
'user name' @ 'host name'	Which user is authorized? Note: there are 2 pairs of single quotes

demonstration:

#User user1 is assigned permissions for demo database operations: create tables, modify tables, insert records, update records, and query grant create,alter,insert,update,select on demo.* to 'user1'@'localhost'; #Assign all permissions to user2 user to all tables of all databases grant all on *.* to 'user2'@'%';

• Revoke authorization syntax:

REVOKE permission 1,   Permission 2... ON   Database. Table name   revoke all on test.* from 'user1'@'localhost'; ' User name '@' host name ';

keyword	explain
REVOKE...ON...FROM	Keywords to revoke authorization
jurisdiction	The user's permissions, such as CREATE, ALTER, SELECT, INSERT, UPDATE, etc., all rights ALL only
Database name. Table name	For which databases and tables, if you want to cancel the user's operation permission on all databases and tables, it can be represented by *, such as **
'user name' @ 'host name'	To which user

demonstration:

#Revoke user user1's permission to operate on all tables in the demo database revoke all on demo.* from 'user1'@'localhost';

• View permission syntax:

SHOW GRANTS FOR 'user name' @ 'host name';

demonstration:

#View user1 user's permissions show grants for 'user1'@'localhost';

1. Change Password

• Syntax:

ALTER   user 'username' @ 'hostname' IDENTIFIED by 'new password';

demonstration:

#Change the password of the root user to root alter user 'root'@'localhost' IDENTIFIED by 'root';

Under MYSQL8 version   The password encryption rules of the account need to be changed

alter user 'root'@'localhost' IDENTIFIED with mysql_native_password by 'root';

We use the root account   To create and authorize users,   Authorization itself is a privilege If we want to grant this permission to ordinary users, Grant.......................................... to user @ host address with grant option

  Multi table query:

give an example:

  1. Inner connection: use the records of the left table to match the records of the right table. If the conditions are met, it will be displayed (each row of the left table should scan all rows on the right). For example: slave table. Foreign key = primary table. Primary key (the above query is queried through implicit inner connection)

1. Implicit inner connection: SELECT   Field name FROM   Left watch,   Right table WHERE conditions
2. Show internal connections: SELECT   Field name   FROM   Left table   [INNER] JOIN   ON condition and where condition in the right table; (where or and can be used for multiple conditions)
3. The two are equivalent.

   select * from emp e inner join dept d on e.dept_id = d.id where e.name='Tang Monk';

As like as two peas, what is the same?

2. External connection:

Left outer connection: SELECT   Field name FROM   LEFT [OUTER] JOIN   Right table ON condition

Right outer connection:   SELECT   Field name FROM   Left table right [outer] join   Right table ON condition

  Summary:

Compared with the internal connection, the external connection increases the ability to display all the information of a table without matching. The left outer connection ensures that all of the left table are displayed, and the right outer connection ensures that all of the right table are displayed.

  Note: if there is no foreign key constraint between two tables, multi table queries can still be performed.

Subquery: (create a value | set | table before the main query)

According to the results of sub query:

1. Single column: SELECT   Query field FROM   Table WHERE   Field = (subquery); (as value)
2. Multi column single row: SELECT   Query field FROM   Table WHERE   Field IN (sub query); (as a collection)
3. Multiple columns and rows:     SELECT   Query field FROM   (subquery)   Table alias WHERE   Conditions; (as a table)

Query employees and their corresponding departments after January 1, 2011: (the results of the three queries are the same)

  Note: when a multi row and multi column subquery is used as a table, it must take an alias.

  practice:

Table emp:

  Table dept:

  Code: (and symbol is better not used together)

-- 1: Query name is three words and salary sal And bonuses comm Employee information and the name and address of his / her department
SELECT
	emp.*, dept.dname,
	dept.loc
FROM
	emp
LEFT JOIN dept ON (sal + IFNULL(comm, 0)) > 30000
WHERE
	emp.deptno = dept.deptno
AND ename LIKE '___';

-- 2: query emp Each department number in the,Salary and salary of all employees in the Department,Department name of the Department ,Salary and ascending order.
SELECT
	h.deptno,
	h.dname,
	IFNULL(h.summ, 0) AS 'Wages and'
FROM
	(
		SELECT
			dept.deptno,
			dname,
			sum(sal) summ
		FROM
			dept
		LEFT JOIN emp ON emp.deptno = dept.deptno
		GROUP BY
			emp.deptno
	) h
ORDER BY
	h.summ ASC;

-- Or:
SELECT
	dept.deptno,
	dname,
	sum(sal) summ
FROM
	dept
LEFT JOIN emp ON emp.deptno = dept.deptno
GROUP BY
	emp.deptno
ORDER BY
	summ DESC;

-- 3: query emp Department number in,Number of Chinese staff in the Department,Department location and department name.
SELECT
	dept.deptno,
	dname,
	count(job) 'Number of clerks'
FROM
	dept
LEFT JOIN emp ON emp.deptno = dept.deptno
AND job = 'Clerk'
GROUP BY
	emp.deptno;

-- 4: query emp Department number in,All department salaries and,Location of Department,Only the information of salary and the highest two departments is displayed.
SELECT
	dept.deptno,
	loc,
	dname,
	sum(sal) 'Wages and'
FROM
	dept
LEFT JOIN emp ON emp.deptno = dept.deptno
GROUP BY
	dept.deptno
ORDER BY
	'Wages and' DESC
LIMIT 0,
 2 -- 5: query emp Name of each employee in the,Employee entry date,Employee salary,The name of his leader,Leader's entry date,Leader's salary
SELECT
	ee.ename,
	ee.hiredate,
	ee.sal,
	eer.ename,
	eer.hiredate,
	eer.sal
FROM
	emp ee
LEFT JOIN emp eer ON ee.mgr = eer.empno;

-- 6: On the basis of question 5, find out the information of employees whose entry date is earlier than his leader and whose salary is lower than his leader.
SELECT
	ee.ename,
	ee.hiredate,
	ee.sal
FROM
	emp ee
LEFT JOIN emp eer ON ee.mgr = eer.empno
WHERE
	ee.hiredate > eer.hiredate
AND ee.sal < eer.sal;

-- 7: On the basis of question 6, continue to query the employee's department name and department location.
SELECT
	e.*, dept.dname,
	dept.loc
FROM
	(
		SELECT
			ee.deptno,
			ee.ename,
			ee.hiredate,
			ee.sal
		FROM
			emp ee
		LEFT JOIN emp eer ON ee.mgr = eer.empno
		WHERE
			ee.hiredate > eer.hiredate
		AND ee.sal < eer.sal
	) e
LEFT JOIN dept ON dept.deptno = e.deptno;

-- Or: three meter connection: Note where The statement is written last
SELECT
	ee.ename,
	ee.hiredate,
	ee.sal,
	dept.dname,
	dept.loc
FROM
	emp ee
LEFT JOIN emp eer ON ee.mgr = eer.empno
LEFT JOIN dept ON ee.deptno = dept.deptno
WHERE
	ee.hiredate > eer.hiredate
AND ee.sal < eer.sal;

-- 8: query emp Information of employees whose salary is higher than that of all clerks.
SELECT
	*
FROM
	emp
WHERE
	sal > (
		SELECT
			MAX(emp.sal)
		FROM
			emp
		WHERE
			job = 'Clerk'
	);

-- Or:
SELECT
	*
FROM
	emp
WHERE
	sal > ALL (
		SELECT
			sal
		FROM
			emp
		WHERE
			job = 'Clerk'
	);

-- 09:Find employment date before 2007-05-01 Employee information for
SELECT
	*
FROM
	emp
WHERE
	hiredate < '2007-05-01';

-- 10:query emp Among the occupations in, the entry date is earlier than 2007-05-01 Number of employees
SELECT
	job,
	COUNT(*)
FROM
	emp
WHERE
	hiredate < '2007-05-01'
GROUP BY
	job;

-- 11:query emp The maximum wage and minimum wage in each department in the, and are arranged in descending order according to the maximum wage and minimum wage difference
SELECT
	emp.deptno,
	dept.dname,
	max(sal) 'Maximum wage',
	MIN(sal) 'Minimum wage',
	MAX(sal) - MIN(sal) 'Wage difference'
FROM
	dept
LEFT JOIN emp ON emp.deptno = dept.deptno
GROUP BY
	emp.deptno
ORDER BY
	(max(sal) - min(sal)) DESC;

-- 12:query emp The name, salary, bonus, department name and department location of the middle manager and salesperson. Only the monthly salary, bonus and the information of the lowest three people can be queried 
SELECT
	emp.ename,
	emp.sal,
	emp.comm,
	dept.dname,
	dept.loc,
	sal + IFNULL(comm, 0) 'bonus+wages'
FROM
	emp
LEFT JOIN dept ON job = 'manager'
OR 'Clerk'
AND emp.deptno = dept.deptno
ORDER BY
	(sal + IFNULL(comm, 0)) ASC
LIMIT 0,
 3;

  Summary: on --- where --- group by --- order by----

View: (to place query results)

Create: create   or   replace   View view name as   Query result (with check option)??????

create or replace view ggg0 
as SELECT ee.ename 'employee',ee.hiredate 'hiredate01',ee.sal 'sal01',eer.ename 'employer',eer.hiredate 'hiredate02',
eer.sal 'sal02' 
FROM emp ee LEFT join emp eer on ee.mgr = eer.empno;

Delete: drop view view name;

View: show   tables;

  be careful:

1. The field names of a view are not allowed to be repeated, but we can when querying (the fields of query results will be modified)
2. The content of the view will change according to the content of the queried table.
3. Views, like ordinary tables, can be queried by rows.

   SELECT * FROM ggg0 ORDER BY sal01 DESC;

Database functions:

1. Function to handle NULL:

ifnull(exper1,exper2): if exper1 is null, exper2 is returned; otherwise, exper1 is returned

nullif(exper1,exper2): if exper1 and exper2 are equal, null is returned; otherwise, exper1 is returned

if(exper1,exper2,exper3): similar to?: For ternary operator, if exper1 is true, not equal to 0 and not equal to null, exper2 is returned; otherwise, exper3 is returned

isnull(exper1): judge whether exper1 is null. If it is null, it returns 1 as the query result, and null returns 0

SELECT NULLIF(111,111);-- NULL
SELECT IFNULL(NULL,5);-- 5
-- 0 perhaps null representative false，1 representative true
SELECT IF(NULL,2,3);-- 3
SELECT IF(0,2,3);-- 3
SELECT IF(1,2,3);-- 2
-- be careful: sql Statement, the Boolean operator equal sign is'=';
SELECT if(CHAR_LENGTH('123456') = 6,'111','222');--111
SELECT if(CHAR_LENGTH('123456') != 6,'111','222');--222
SELECT ISNULL(NULL);--1

2.case statement: (multiple branches)

First usage:

case value

when compare_value1 then result1

when compare_value2 then result2

else result3

end
--compare_value1 and compare_value2 All values,
If value Equal to who, it will be executed then

Second usage:

case
when condition1 then result1
when condition2 then result2
........
else result
end
condition1，2 They are Boolean expressions, so they are more flexible than the first method

Example:

  3. Common single line functions:

-- 01 char_length(str):Calculated character length:
SELECT
	ename,
	CHAR_LENGTH(ename) 'length'
FROM
	emp;

#Character length, not bytes
-- 02 date_add:Add a date after a certain period of time
-- use date_add The function requires two parameters, date and numeric type, with unit
-- select date_add('1998-01-02',interval 12 month); -- year month day,
-- Find the date after the employee has worked for ten years: 
SELECT
	ename,
	hiredate,
	DATE_ADD(hiredate, INTERVAL 10 DAY)
FROM
	emp;

-- 03 now() Represents the current year, month, day, hour, minute and second
-- Find employees who have been employed for more than ten years 
SELECT
	ename hiredate,
	DATE_ADD(hiredate, INTERVAL 10 YEAR) 'Time after ten years of employment'
FROM
	emp
WHERE
	DATE_ADD(hiredate, INTERVAL 10 YEAR) < NOW();

-- 04 Virtual table dual,It can only appear this time. Of course, it can not be written
SELECT
	CURDATE(),
	CURTIME(),
	NOW()
FROM
	DUAL;

-- 2021-09-07****12:05:21*****2021-09-07 12:05:21
SELECT
	CURDATE(),
	CURTIME(),
	NOW();

-- The above virtual table may not be written

Other functions:

  Uuid(): uuid() in the same column will not be repeated;

ALTER TABLE emp ADD sss VARCHAR(40);
UPDATE emp SET sss = UUID();

  Transaction:

Properties: (ACID)

1. atomicity: success or failure;
2. consistency: the integrity of data before and after a transaction should be consistent
3. Persistence: once a transaction is committed, it is irreversibly persisted to the database.
4. isolation: when a user operates a transaction, the database starts a transaction for each user. The transaction cannot be disturbed by the data operated by other transactions.

  Some problems caused by poor isolation:

1. Dirty read: one transaction reads data that is not committed by another transaction.
2. Non repeatable reading: in a transaction, the contents of a row are read multiple times, but the reading results are different.
3. Phantom reading (virtual reading): a transaction reads the data inserted by another transaction, resulting in different readings before and after.

  Basic syntax of transaction commit

-- use set Statement to change the auto submit mode
SET autocommit = 0;   /*close*/
SET autocommit = 1;   /*open*/

-- be careful:
--- 1.MySQL The default is auto submit
--- 2.Auto commit should be turned off first when using transactions

-- Start a transaction,Mark the starting point of the transaction
START TRANSACTION  

-- Commit a transaction to the database
COMMIT

-- Rollback transaction,The data returns to the initial state of this transaction
ROLLBACK

-- reduction MySQL Automatic submission of database
SET autocommit =1;

-- Save point
SAVEPOINT Save point name -- Set a transaction savepoint
ROLLBACK TO SAVEPOINT Save point name -- Rollback to savepoint
RELEASE SAVEPOINT Save point name -- Delete savepoint

  Transfer realization:

CREATE TABLE if not EXISTS shiwu (
	id INT PRIMARY KEY auto_increment,-- Constrained by two conditions
	name VARCHAR(3) NOT NULL,
	cash DECIMAL(7,2) not NULL
);
INSERT INTO shiwu (name,cash)
VALUES('A',2000.00),('B',10000.00);
-- Realize transfer:
set autocommit = 0;
START TRANSACTION;
UPDATE shiwu SET cash=cash+500 WHERE name= 'A';
UPDATE shiwu SET cash=cash-500 WHERE name='B';
COMMIT;
ROLLBACK;
SET autocommit = 1;

  Stored procedure:

Purpose: a collection of one or more MySQL statements saved for future use. (it can be understood as a method in java)

Format:

drop procedure if exists Stored procedure name;

create procedure Stored procedure name(pram[parameter list])
begin
sql sentence
end;

Used in parameter list in Keywords represent input parameters ,use out Keyword represents the return value
 format:  in Parameter type        out Parameter type

Call:
call Stored procedure name(Input reference 1,Input reference 2,....@Return value);
select @Return value;

-- delete
drop procedure Stored procedure name;

Example: creating a stored procedure:

drop PROCEDURE IF EXISTS foo;
-- Create function:
CREATE PROCEDURE foo (in newname VARCHAR(20),in dis VARCHAR(100),OUT sum INT)
BEGIN
INSERT into tsinger(sname,display) VALUES (newname,dis);
SET sum = (SELECT COUNT(*) FROM tsinger);-- Assignment must be in parentheses
END;
-- Call function
call foo('lll','cekhhkb',@cou);
SELECT @cou;-- Query return value

Note: not all sql statements (ordinary sql statements) in storage functions end with semicolons, but sql statements in stored procedures end with semicolons in navicat. Other software may not!!!

Declare variables:

1. Declare variables with declare

declare variable name type default initial value;

2. Use set to declare variables. The values of variables can come from subqueries

set @ variable = (query); (Note: use this variable with @)

Assignment:

set variable = value;

Branch:

If condition then operation end if;

If condition then operation else operation end if;

Note: there are no if... if... else or if... Else or if... Else molecular statements in sql branch statements, only the above two. If you need more branches, you need to use the case...when statement.

Cycle:

1. while statement

      While condition do operation end while;

      2.repeat statement     Similar to do while in java, it is executed first whether the condition is true or not

      Repeat operation until condition end repeat;

# Create the stored procedure pro2, specify the name and description, add a new singer, and return the number of singers whose salary is null
drop PROCEDURE if EXISTS pro2;

create procedure pro2(in spc_name varchar(20),in spc_display varchar(100),out count int)
BEGIN
# When the specified name is null, it is set to system test + random string
if ISNULL(spc_name) 
	then  set spc_name = concat('System test',SUBSTR(UUID(),1,8));
end if;
insert into tsinger (sname , display) values (spc_name,spc_display);
set count = (select count(*) from tsinger where salary is null);
end;

# Stored procedure 3: modify the salary with null salary to the smallest salary in the table
drop procedure if exists pro3;
create procedure pro3()
BEGIN
set @min_sal = (select min(salary) from tsinger);
update tsinger set salary = @min_sal where salary is null;
end;
call pro3();

# Stored procedure 4: add a singer according to the specified gender
# If the gender is' female ', set salary to 10000
# If the gender is male, set salary to the smallest salary in the table
drop PROCEDURE if EXISTS pro4;
CREATE PROCEDURE pro4(in sex char(1))
BEGIN 
	set @minsalary = (SELECT min(salary) from tsinger);
	if(sex = 'male') THEN
		INSERT tsinger(sex,salary) VALUES ('male',@minsalary);
	else 
		INSERT tsinger(sex,salary) VALUES ('female',10000) ;
	end if;
end;
call pro5('male');
call pro4('female');
-- use case when sentence
drop PROCEDURE if EXISTS pro4;
create procedure pro5(in sex1 char(1))
BEGIN 
	DECLARE salary1 int DEFAULT 0;
	set @minsalary = (SELECT min(salary) from tsinger);
	case 
		when  sex1 = 'male' then set salary1 = @minsalary;
		when sex1 = 'female' then SET salary1 = 10000;
		ELSE set salary1 = null;
	end case;-- Conflict with Terminator
	INSERT into tsinger(sex,salary) VALUES (sex1,salary1);
end;

# Stored procedure 5: insert a specified number of records into the tsinger table. The name and description are uuid random, and the gender is' female 'and' male '
drop PROCEDURE IF EXISTS pro5;
create PROCEDURE pro5(in count int)
BEGIN
declare a int default 1;
declare gender char(1) default 'female';
while a<=count DO
	if a%2!=0 then set gender = 'female';
	else set gender = 'male';
	end if;
	insert into tsinger (sname,sex,display) values (SUBSTR(UUID(),1,8),gender,UUID());
	set a = a+1;
end while;
end;

call pro5(4);
# Stored procedure 6: insert a specified number of records into the tsinger table, and sname takes random
drop PROCEDURE if exists pro6;
create procedure pro6(in count int)
BEGIN
repeat 
	insert into tsinger (sname) values (SUBSTR(UUID(),1,8));
	set count = count +1;
	until count>=10 end repeat;
end;
# 100 has met the condition of > = 10 to stop the cycle, but it is still executed once
call pro6(100);

Summary:

1. When writing sql functions, variables are usually declared first through declare or set. During this period, most operations are on these variables (and formal parameters declared in begin and end), and finally on the specific fields and records of the specific table.
2. The formal parameters in sql stored procedures and the values in begin...end can be the same as the field names in the operation table, but it is better not to be the same

Stored procedures with transactions:  

Design a stored procedure with transaction processing.
-- modify tsinger Table assignment sid The record of display Specified for the new display，--Errors may occur, so a transaction needs to be established
-- modify tsinger If the salary is less than or equal to 10000, multiply it by 3,If the salary is more than 10000, multiply it by 0.9
-- Return the total number of singers whose monthly salary is more than 10000 in the table.
drop PROCEDURE if EXISTS pro10;
CREATE PROCEDURE pro10 (in sidp INT,in newdisplay VARCHAR(20),OUT sum INT)
BEGIN
-- Declare state variables:
	DECLARE flag int DEFAULT 1;
-- statement sql Exception handler, error sql In case of abnormality, set flag  = 0；
	DECLARE CONTINUE HANDLER for SQLEXCEPTION SET flag = 0;
-- 	Transaction: modification may violate unique constraints
	START TRANSACTION;
	UPDATE tsinger SET display = newdisplay WHERE sid = sidp;
	UPDATE tsinger set salary = salary *3 WHERE salary < 10000;
	UPDATE tsinger set salary = salary*0.9 WHERE salary >10000;
	if flag = 1 THEN 
		COMMIT;
	ELSE 
		ROLLBACK;
	end IF;
	
	set sum = (SELECT COUNT(*) FROM tsinger WHERE salary>10000);
end;

call pro10(1002,'kkjg',@sum);
SELECT @sum;

Cursor:

# Use cursors in stored procedures:
# Calculate the sum of salary in tsinger and return it. When display in the record is null, it is set to the random string uuid
drop procedure if exists pro_cursor;
create procedure pro_cursor(out sum decimal(11,2))
BEGIN
declare flag int default 1;
declare singer_id int default 0;
declare singer_sal decimal(7,2) default 0;
declare singer_dis varchar(100) default '';-- ''be equal to null
# Declare cursor cur1
declare cur1 cursor for select sid,salary,display from tsinger;-- Cursor based on a query
declare continue handler for not found set flag = 0;-- Set a processor when it cannot be found
set sum = 0;
# Open cursor
open cur1;
# Fetch data from cursor
fetch cur1 into singer_id,singer_sal,singer_dis;
# Cyclic operation
while flag = 1 do
	if singer_sal  is not null then 
		set sum = sum + singer_sal;
	end if;
	if singer_dis is null then 
		update tsinger set display = UUID() where sid = singer_id;-- Don't write where The fields are all updated
	end if;
	# The cursor continues down
	fetch cur1 into singer_id,singer_sal,singer_dis;
end while;
# Close cursor
close cur1;
end;
CALL pro_cursor(@summm);
SELECT @summm;

Trigger: (similar to the listener in GUI)

Create: create trigger trigger_name trigger_time trigger_event on table_name for each row

Triggers are database objects related to table operations, so the names of triggers are related to tables. When creating triggers, you need to specify the table name table_name, the table is permanent, so the trigger cannot be associated with temporary tables and views.

Trigger: key to create trigger and define trigger name.

trigger_name: the name of the trigger.

trigger_time: the execution time of the trigger. Its value is before or after, which refers to whether it is executed before or after the statement that activates it. Generally, the trigger is executed after the statement that activates it is completed.

trigger_event: trigger event. Its value can make insert, update and delete. For these three actions, we need to understand the two temporary virtual tables in the database: deleted and inserted, which are represented by the old and new keywords in mysql. (dialect) (the names of the fields in the new table and the old table are the same as those in the table with triggers).

for   each   Row: add a trigger action for each row. It will be triggered as long as it is changed.

For each line, first sense whether there is a modification operation = = > trigger = = > before? Update to table after.

action	deleted table (old)	inserted table (new)
insert	Do not store records	Store newly inserted records
update	Records before storage	Store updated records
delete	Store deleted records	Do not store records

Delete: drop trigger name;

View: show triggers;

drop table if exists trecord;
create table trecord(
rid varchar(32) primary key, -- Primary key
sid int , -- singer id
sname varchar(20), -- Singer name
display varchar(100), -- Singer description
newdisplay varchar(100), -- New description of singer
updatetime datetime -- Modification time
);


DROP TRIGGER update_display_record;

CREATE TRIGGER update_display_record AFTER UPDATE ON tsinger FOR EACH ROW
BEGIN

DECLARE rid VARCHAR (32) DEFAULT '';


SET rid = REPLACE (UUID(), '-', '');

INSERT INTO trecord
VALUES
	(
		rid,
		old.sid,-- These are the values of the corresponding rows
		old.sname,
		old.display,
		new.display,
		now()
	);


END;

Example:  

Design a trigger , In the direction tsinger When inserting data into a table,If salary No value given or less than 500,It is assigned 1000.01,
-- If birthday No value given,It is assigned as the current date
DROP TRIGGER if EXISTS pro9;
CREATE TRIGGER pro9 BEFORE INSERT ON tsinger FOR EACH row
BEGIN
	if new.salary < 500 or new.salary is null THEN 
		SET new.salary = 1000.01;
	end if;
	if new.birthday is NULL then 
		set new.birthday = NOW();-- birthday Is a string, so it can be followed by a date;
	end if;
END;
INSERT into tsinger(salary,birthday) VALUES (500,NULL);
INSERT into tsinger(salary,birthday) VALUES (400,NULL);