Database and table building

view the database

sentence

show datebases;
-- View all databases

effect

[take mine as an example, the following are]

mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| sqlfortextbook |
| studb |
| sys |
±-------------------+
6 rows in set (0.02 sec)

explain

Only sqlfortextbook and studb are databases built by me, and others are systems.

Create database

Statement [create studb database]

create database studb
-- In your sql Create this database in, provided that there is no database with the same name
using studb
-- Enter database

Build table

The table to be built is

Create three tables BankT, LET and LoanT to be used in the studb database. The corresponding columns are their attributes.

Create table statement

CREATE TABLE BankT(
Bno CHAR(12) PRIMARY KEY ,
Bname VARCHAR(100),
Tel VARCHAR(20)
);
CREATE TABLE LET(
Eno CHAR(7) PRIMARY KEY ,
Ename VARCHAR(100),
Enature VARCHAR(10),
Ecapital INT,
Erep VARCHAR(10)
);
CREATE TABLE LoanT(
Eno CHAR(7),
Bno CHAR(12),
Ldate DATE,
Lamount INT,
Lterm INT,
FOREIGN KEY (Eno) REFERENCES LET(Eno),
FOREIGN KEY (Bno) REFERENCES BankT(Bno),
PRIMARY KEY LoanT(Bno,Eno,Ldate)
);

Check whether the built table is correct

show tables;
-- View all tables under the current database
DESC loant;
-- see loant Table structure[ SQL [statements regardless of case]

Contents of the table to be inserted:

BankT table

LET table

Load table

Insert content statement

Insert into BankT table

insert into
bankt(Bno,Bname,Tel)
value
(' 102100000634 ',' Industrial and Commercial Bank of China Limited Beijing Huixin sub branch',' 010-66383723 '),
(' 102100011308 ' ,'Industrial and Commercial Bank of China Limited Argentina RMB clearing bank',' 010-69025556 '),
(' 102100015171 ' ,'Industrial and Commercial Bank of China Limited Beijing Xinfadi sub branch',' 010-66287547 '),
(' 102100020655 ' ,'Industrial and Commercial Bank of China Limited Beijing Dashanzi sub branch',' 010-66413881 '),
(' 102100020778 ', 'Industrial and Commercial Bank of China Limited Beijing West Fourth Ring sub branch',' 010-68153318 '),
(' 102100024914 ', 'Industrial and Commercial Bank of China Limited Beijing Fuxi Avenue sub branch',' 010-67749117 '),
(' 102100028292 ', 'Industrial and Commercial Bank of China Limited Beijing Xueyuan South Road sub branch',' 010-69662555 '),
(' 102100028469 ', 'Industrial and Commercial Bank of China Limited Beijing Wangjing Xincheng sub branch',' 010-68303695 '),
(' 102100077776 ', 'Industrial and Commercial Bank of China Limited Russian RMB business clearing bank ',' 010-68887973 '),
(' 103100009023 ', 'Agricultural Bank of China Limited Beijing Xinhua Street sub branch',' 010-69541044 '),
(' 103100009031 ', 'Agricultural Bank of China Limited Beijing fuheyuan sub branch',' 010-69399485 '),
(' 103100009040 ', 'Agricultural Bank of China Limited Beijing Yongledian sub branch',' 010-68365688 '),
(' 103100009111 ', 'Agricultural Bank of China Limited Beijing Tongzhou Dongguan sub branch',' 010-69903089 '),
(' 103100009138 ', 'Agricultural Bank of China Limited Beijing Wuyi garden sub branch',' 010-68246637 '),
(' 103100009146 ', 'Agricultural Bank of China Limited Beijing Songzhuang sub branch',' 010-67039559 '),
(' 103100009162 ', 'Agricultural Bank of China Limited Beijing Xiji sub branch',' 010-66709805 '),
(' 103100009179 ', 'Agricultural Bank of China Limited Beijing Zhangjiawan sub branch',' 010-68937684 '),
(' 103100009187 ', 'Agricultural Bank of China Limited Beijing Jinhai Street sub branch',' 010-69518636 '),
(' 103100009211 ', 'Agricultural Bank of China Limited Beijing A Sub branch',' NULL         '),
(' 103100040103 ', 'Agricultural Bank of China Limited Beijing Dajiaoting sub branch','010-66268599 ');

Insert into let table

insert into
let(Eno, Ename, Enature, Ecapital)
values
(' M052001 ',' Anhui Zhixi Information Technology Co., Ltd         ',' private enterprise     ','      200 ',' Xu Xin'an '),
(' M052002 ', 'Shanghai Siqiang Technology Co., Ltd             ',' private enterprise     ','      150 ',' Zhu Tingyi '),
(' M052003 ',' Chongqing Wangda Automobile Service Co., Ltd         ','private enterprise     ','      240 ',' Jin Jing   '),
(' M052004 ',' Dalian Johnson Investment Service Co., Ltd         ',' private enterprise     ', '     300 ',' Cai chenjie '),
(' M052005 ',' Qingdao Boyi electromechanical Co., Ltd             ',' Collective enterprise     ','      200 ',' Mo Jingyuan '),
(' M052006 ',' Guangzhou Minolta Electric Appliance Co., Ltd           ',' private enterprise     ','      150 ',' remote   '),
(' M052007 ',' Chengdu deep Vision Technology Co., Ltd         ',' private enterprise     ', '     180 ',' Lechao   '),
(' M052008 ',' Shanghai Rongsheng Advertising Co., Ltd            ', ' private enterprise     ,''      200 ',' Xu Guanzhong '),
(' M052009 ',' Chongqing Xuchen Electronic Technology Co., Ltd         ',' private enterprise     ', '     210 ',' Deng Yue   '),
(' M052010 ',' Dalian Hanhui vegetable market management Co., Ltd   ' ,'Collective enterprise     ',  '     70 ',' Jing Yun Shen '),
(' M052011 ', 'Qingdao tus warm Ecological Technology Co., Ltd   ',' private enterprise     ',  '    300 ',' Tang Yunqing '),
(' M052012 ',' Anhui Kaidi Mining Co., Ltd         ',' private enterprise     ', '     500 ',' Lin Shan   '),
(' M052013 ',' Beijing Minfu Investment Co., Ltd            ', ' private enterprise     ','      300', ' Wang zheyan '),
(' M052014 ',' Shenzhen wanshunyun Information Technology Co., Ltd       ',' private enterprise     ', '     100 ',' Wang Yinghui '),
(' M052015 ',' Hangzhou Jiayuan Hardware Building Materials Co., Ltd         ',' Sole proprietorship ', '     220 ',' Strong and prosperous   '),
(' M052016 ',' Guangzhou Minze agricultural reclamation Group Co., Ltd         ',' Collective enterprise     ','      310', ' Zhang Cheng   '),
(' M052017 ',' Chengdu wanconglu Agricultural Development Co., Ltd       ',' private enterprise     ','      230 ',' Zhou Yuan   '),
(' M052018 ',' Shanghai sangyuan Biotechnology Co., Ltd         ',' private enterprise     ','      100 ',' Lu Congfan '),
(' M052019 ',' Chongqing Maier Management Consulting Co., Ltd         ',' private enterprise     ','      100 ',' Liu Zhi   '),
(' M052020 ',' Dalian jiujiajiu Trading Co., Ltd           ',' private enterprise     ', '     120 ',' Xiao Bingjun '),
(' M052021 ',' Qingdao Yijia Auto Parts Co., Ltd     ',' Sole proprietorship ','      300 ',' Chen Hao   '),
(' M052022 ', 'Guangzhou Xintai Shoes Co., Ltd             ',' private enterprise     ','      180 ',' Wang Qiusheng '),
(' M052023 ',' Chengdu guangguangniao shoes and Clothing Co., Ltd           ',' Sole proprietorship ',  '     80 ',' Weng Dongxu '),
(' M052024 ',' Beijing Jinkai training school                ', ' private enterprise     ','      500', ' Jin ruizhang '),
(' M052025 ',' Shanghai Zhonggong Education Technology Co., Ltd         ',' private enterprise     ','      100 ',' Jin Wenchao '),
(' M052026 ','Minshen e-commerce Holding Co., Ltd             ',' private enterprise     ','      100 ',' Zhang Zili '),
(' M052027 ',' Hangzhou Tiantu Venture Capital Co., Ltd         ',' private enterprise     ', '     500 ',' Ying Baolong '),
(' M052028 ',' Shenzhen Guoxin Technology Co., Ltd         ',' private enterprise     ','      100 ',' Jiang Zilin '),
(' M052029 ',' Shanghai soul Technology Co., Ltd             ',' private enterprise     ','       50 ',' Li Lujun '),
(' M052030 ', 'Chongqing Kaiyin Technology Co., Ltd         ',' joint-stock association     ', '     200 ',' Wang Chensheng '),
(' M052031 ',' Beijing Jiutian Machinery Co., Ltd             ',' private enterprise     ','      380 ',' Yang Jue '),
(' M052032 ',' Shenzhen Hexun Technology Co., Ltd         ',' joint-stock association     ','      100 ',' Shi Chao   '),
(' M052033 ',' Hangzhou Xiangyu Machinery Co., Ltd             ',' private enterprise     ','      100 ',' Ning Zhang   '),
(' M052034 ',' Guangzhou dejucai Investment Co., Ltd           ',' private enterprise     ','     1000 ',' Lu Ziqing '),
(' M052035 ',' Chengdu wenxinyuan Trading Co., Ltd           ',' private enterprise     ','       70 ',' Zhu Chenzhou '),
(' M052036 ', 'Shanghai Wenjun Shoes Co., Ltd             ', 'Sole proprietorship ', '      80 ',' Yu Zhongjun '),
(' M052037 ',' Chongqing Wenmao catering Co., Ltd         ',' Sole proprietorship ','       50 ', 'Liu Renzhi '),
(' M052038 ',' Dalian Weisi Technology Co., Ltd             ',' private enterprise     ','       80 ',' Liu Xiaoye '),
(' M052039 ',' Qingdao YOUPIN Vision Advertising Co., Ltd         ',' private enterprise     ', '     120 ',' Gong Mo   '),
(' M052040 ',' Guangzhou Tianjiu happiness Business Consulting Group Co., Ltd ',' private enterprise     ', '      50 ', 'Zhang Yifan '),
(' M052041 ',' Chengdu boutique Fenghua Advertising Co., Ltd         ',' private enterprise     ','       60 ',' Zhu Qian '),
(' M052042 ',' Shanghai Juye Information Technology Co., Ltd         ',' private enterprise     ','      100 ',' Chen Chen Wang '),
(' M052043 ',' Chongqing Xinlin Business Consulting Group Co., Ltd     ',' private enterprise     ', '     100 ',' Cui Zhenglin '),
(' M052044 ',' Dalian Lianda Technology Co., Ltd         ',' joint-stock association     ','      160 ',' Jian Zhang   '),
(' M052045 ',' Qingdao modern drug development Co., Ltd         ',' private enterprise     ','      300 ', 'Dai Guangchen ');

Insert into the loan table

insert into
loant(Eno, Bno, Ldate, Lamount, Lterm)
values
(' M052028 ',' 102100000634 ',' 2014-11-01 ','      31 ','     3 '),(
' M052032 ',' 102100011308 ',' 2028-06-01 ','      42 ','     5 '),(
' M052015 ',' 102100015171 ',' 2020-01-01 ','      40 ','    14 '),(
' M052027 ',' 102100015171 ',' 2029-12-01 ','      73 ','     6 '),(
' M052042 ',' 102100015171 ',' 2011-05-01 ','      22 ','     8 '),(
' M052007 ',' 102100020655 ',' 2016-06-01 ','      76 ','    10 '),(
' M052012 ',' 102100020655 ',' 2020-01-01 ','      41 ','    17 '),(
' M052030 ',' 102100020655 ',' 2022-12-01 ','      37 ','     8 '),(
' M052014 ',' 102100020778 ',' 2019-01-01 ','      55 ','    13 '),(
' M052024 ',' 102100020778 ',' 2027-05-01 ','      95 ','    14 '),(
' M052003 ',' 102100022525 ',' 2016-09-01 ','      58 ','    13 '),(
' M052005 ',' 102100022525 ',' 2021-09-01 ','      78 ','    11 '),(
' M052009 ',' 102100022525 ',' 2013-04-01 ','      42 ','    18 '),(
' M052021 ',' 102100022525 ',' 2024-11-01 ','      83 ','    13 '),(
' M052033 ',' 102100024914 ',' 2013-09-01 ','      48 ','    11 '),(
' M052011 ',' 102100028292 ',' 2016-06-01 ','      66 ','     6 '),(
' M052028 ',' 102100028292 ',' 2022-02-01 ','      57 ','     5 '),(
' M052038 ',' 102100028292 ',' 2026-02-01 ','      26 ','    12 '),(
' M052031 ',' 102100028469 ',' 2022-01-01 ','      64 ','    10 '),(
' M052033 ',' 102100028469 ',' 2013-04-01 ','      24 ','    10 '),(
' M052002 ',' 102100077776 ',' 2013-04-01 ','      77 ','    10 '),(
' M052022 ',' 102100077776 ',' 2015-02-01 ','      60 ','     6 '),(
' M052044 ',' 102100077776 ',' 2014-04-01 ','      69 ','    15 '),(
' M052001 ',' 103100009023 ',' 2011-12-01 ','      44 ','    12 '),(
' M052010 ',' 103100009023 ',' 2023-10-01 ','      67 ','    12 '),(
' M052016 ',' 103100009023 ',' 2021-11-01 ','      85 ','     3 '),(
' M052037 ',' 103100009023 ',' 2017-08-01 ','      25 ','     3 '),(
' M052038 ',' 103100009023 ',' 2020-11-01 ','      20 ','    13 '),(
' M052045 ',' 103100009023 ',' 2017-11-01 ','      22 ','     7 '),(
' M052030 ',' 103100009031 ',' 2020-02-01 ','      39 ','    14 '),(
' M052028 ',' 103100009040 ',' 2021-02-01 ','      88 ','     8 '),(
' M052037 ',' 103100009040 ',' 2027-08-01 ','      72 ','    12 '),(
' M052003 ',' 103100009111 ',' 2011-05-01 ','      25 ','     8 '),(
' M052004 ',' 103100009111 ',' 2017-06-01 ','      81 ','    11 '),(
' M052038 ',' 103100009111 ',' 2020-01-01 ','      64 ','    17 '),(
' M052019 ',' 103100009138 ',' 2012-11-01 ','      47 ','     6 '),(
' M052023 ',' 103100009138 ',' 2019-09-01 ','      74 ','    12 '),(
' M052019 ',' 103100009146 ',' 2011-06-01 ','      96 ','    16 '),(
' M052038 ',' 103100009162 ',' 2029-03-01 ','      62 ','     9 '),(
' M052006 ',' 103100009179 ',' 2024-07-01 ','      78 ','     8 '),(
' M052014 ',' 103100009179 ',' 2023-09-01 ','      60 ','     8 '),(
' M052029 ',' 103100009179 ',' 2015-09-01 ','      99 ','     8 '),(
' M052034 ',' 103100009179 ',' 2015-09-01 ','      80 ','    17 '),(
' M052017 ',' 103100009187 ',' 2020-07-01 ','      54 ','     8 '),(
' M052025 ',' 103100009187 ',' 2018-10-01 ','      84 ','    12 '),(
' M052027 ',' 103100009187 ',' 2013-12-01 ','      37 ','     9 '),(
' M052008 ',' 103100040103 ',' 2012-08-01 ','      97 ','     7 '),(
' M052020 ',' 103100040103 ',' 2022-09-01 ','      76 ','    11 ');

Introduction to SQL stored procedures

sql is a highly non procedural statement. Procedural sql needs to provide functions such as process space and variable definition.
Procedural sql is declared and called differently in different databases and database management software.
Here are the statements I use MySQL 8 on Navicat 15.

-- Sequential structure
begin
	...
end
-- Branching structure
IF search_condition THEN
	statement_list
ELSE
	statement_list
END IF;
-- Cyclic structure
WHILE search_condition DO
	statement_list
END WHILE;

Stored procedure experiment

[purpose of experiment]
1. Master the definition of stored procedure
2. Master the use of stored procedures
[experimental nature] confirmatory

[experiment content]

(1) Delete the loan records of the designated legal person before the designated date. Legal person name and date are required as input parameters.
(2) Query the legal person name, loan date and loan amount of the specified bank whose loan amount is higher than the average loan amount of this bank. The bank name is required as the input parameter. If the specified bank does not exist, the "specified bank does not exist" will be displayed.
(3) Query the total loan amount of the specified legal person. The legal person name is required to be the input parameter, the total loan amount is the output parameter, and the default value of the legal person name is "Chongqing Xuchen Electronic Technology Co., Ltd.".

-- (1) 
-- Delete the loan records of the designated legal person before the designated date.
-- Legal person name and date are required as input parameters.

delimiter $$

CREATE PROCEDURE p1(in fr_name char(10), in dk_date char(20))
begin 
	SET @frname = fr_name;
	SET @dkdate = dk_date;
	delete 
	loant
	from loant	,let 
	WHERE loant.Eno = let.Eno
	and Ename = @frname and Ldate < @dkdate ;
end $$

call p1('New legal person',2028-06-01) $$

explain:

1. Delimiter means delimiter. In sql, when multiple sql statements are allowed, you should use; An identifier that serves as the end of a statement. If begin... End... Etc. are not sql statements, you need to declare an ID as the end ID first.
   2. CREATE PROCEDURE p1(in fr_name char(10), in dk_date char(20)) is a statement that creates a stored procedure, fr_ name，dk_name is the parameter of P1, and in indicates that they are all input parameters, and the actual parameters need to be passed in when calling.
   3.SET @frname = fr_name; SET is an assignment statement, where the parameter fr will be entered_ Pass the value of name to @ frname@ Frname is a user-defined variable, which is the same as fr_ The difference of the name local variable is that you can use it outside of the procedure sql statement. For example, use show tables; You can see it and use select frname; You can see its value.
   4.call p1('New legal person ', 2028-06-01) is a statement that calls a stored procedure. If executed correctly, it will return OK
   5. The deletion statement is dorp procedure p1;

-- (2) Query the legal person name, loan date and loan amount of the specified bank whose loan amount is higher than the average loan amount of this bank. 
-- The bank name is required as the input parameter. If the specified bank does not exist, the "specified bank does not exist" will be displayed.
delimiter $

CREATE PROCEDURE p2
(in bank_name varchar(32))
begin 

	IF bank_name in (SELECT Bname FROM bankt) THEN
	
	SELECT Ename Name of legal person, Ldate Loan date, Lamount Loan amount
	
	FROM loant LEFT JOIN let 
	ON loant.Eno = let.Eno
	LEFT JOIN bankt
	ON loant.Bno = bankt.Bno
	
	WHERE Bname = bank_name
	AND Lamount > 
	(SELECT AVG(Lamount) FROM loant LEFT JOIN bankt ON loant.Bno = bankt.Bno
	WHERE Bname = bank_name);
	
ELSE
	SELECT 'The specified bank does not exist';
	
END IF;

end $
CALL p2('Industrial and Commercial Bank of China Limited Beijing Xinfadi sub branch')$

-- (3) Query the total loan amount of the specified legal person. The legal person name is required to be the input parameter, the total loan amount is the output parameter, and the default value of the legal person name is "Chongqing Xuchen Electronic Technology Co., Ltd.".

delimiter $
CREATE PROCEDURE p3(in fr_name VARCHAR(32), out sum_amount INT)
BEGIN
	-- mysql Default parameters are not supported!!!
	IF fr_name in (SELECT Ename FROM let) THEN -- Input method name does not exist
	SELECT SUM(Lamount) into sum_amount
	FROM loant 
		LEFT JOIN let ON loant.Eno = let.Eno 
	WHERE 
		Ename = fr_name;

	ELSE
	SET fr_name = 'Chongqing Xuchen Electronic Technology Co., Ltd'; -- If the input legal person does not exist, the default value will be used
	SELECT SUM(Lamount) into sum_amount
	FROM loant 
		LEFT JOIN let ON loant.Eno = let.Eno 
	WHERE 
		Ename = fr_name;
	
	END IF;

END $

CALL p3('Hangzhou Tiantu Venture Capital Co., Ltd',@sum_amount);
SELECT @sum_amount Loan amount;

It's not easy to create. Put more money in it