1 编程基础

1-1 用户变量

SQL代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#创建用户变量name并赋值为“王林”。
SET @NAME='王林';

#创建用户变量user1并赋值为1,user2赋值为2,user3赋值为3
SET @user1=1, @user2=2, @user3=3;

#创建用户变量user4,它的值为user3的值加1
SET @user4=@user3+1;

#在一个用户变量被创建后,它可以以一种特殊形式的表达式用于其他SQL语句中。变量名前面也必须加上符号@。
#创建并查询用户变量name的值。
SET @NAME='王林';
SELECT @NAME;

#变量应用
#查询Book表中图书编号为TP302/057的书名,并存储在变量b_name中。
SET @b_name=
(SELECT bookname FROM book WHERE bookcode = 'TP302/057');

#查询Book表中名字等于中b_name值的图书信息。
SELECT * FROM Book WHERE bookname=@b_name;

##1-2 系统变量

SQL参考

1
2
3
4
5
6
# 获得现在使用的MySQL版本。
SELECT @@VERSION ;

#获得系统当前时间。
SELECT CURRENT_TIME;

2 内置函数

2-1 数学函数

SQL参考

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
#数学函数
#最大值与最小值函数
SET @max_num=GREATEST(1,2,3);
SELECT @max_num;

SET @min_num=LEAST(1,2,3);
SELECT @min_num;


#随机函数
SET @num1 = RAND()*100;
SELECT @num1 ;

#圆周率
SET @num2 = PI();
SELECT @num2 ;

#绝对值
SELECT ABS(-1) AS NUM

2-2 字符串函数

SQL代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#字符串函数
#字符串拼接
SELECT CONCAT('中国','必胜') AS str
#字符串截取
SELECT LEFT('hello',2) AS str
SELECT RIGHT('hello',2) AS str
SELECT SUBSTRING('hello',2) AS str
SELECT SUBSTRING('hello',2,2) AS str
#案例
SET @STR='ABCDEFG';
SELECT LEFT(@STR,2),RIGHT(@STR,3),SUBSTRING(@STR,2,4);
#字符串格式化
SELECT FORMAT(RAND()*100,2) AS num

2-3 日期函数

SQL代码

1
2
3
4
5
6
7
8
9
#日期函数
#当前时间
SELECT NOW() AS str

#获取当前天数,小时等
SELECT DAY(NOW()),DAYOFMONTH(NOW()),HOUR(NOW())

#时间相加
SELECT ADDTIME(NOW(),'2:00:00') AS str

3 存储过程

3-1 简单存储过程

创建存储过程

SQL代码

1
2
3
4
5
6
7
8
#编写一个存储过程,实现的功能是删除一个指定用户姓名的用户信息。
DELIMITER $$
CREATE PROCEDURE sp_del_member(IN xm CHAR(10))
BEGIN
DELETE FROM Members WHERE 姓名=xm;
END
$$
DELIMITER ;

关于结束符

1
2
3
4
5
6
7
DELIMITER //
CREATE PROCEDURE sp_del_member(IN xm CHAR(10))
BEGIN
DELETE FROM Members WHERE 姓名=xm;
END
//
DELIMITER ;

查看存储过程

1
2
3
4
#查看所有的存储过程状态
SHOW PROCEDURE STATUS
#查看某个存储过程的详细信息
SHOW CREATE PROCEDURE sp_del_member

调用存储过程

1
2
#调用sp_del_member存储过程,删除“李强”的用户信息。
CALL sp_del_member('李强');

删除存储过程

1
2
#删除存储过程sp_del_member
DROP PROCEDURE sp_del_member;

3-2 复杂存储过程

3-2-1 局部变量

SQL代码

1
2
3
4
5
6
7
#声明一个整型变量和两个字符变量。
#局部变量只能在BEGINEND语句块中声明
DECLARE num INT(4);
DECLARE str1, str2 VARCHAR(6);
#局部变量赋值
SET num=1, str1= 'hello';

案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#在存储过程体中将Book表中的书名为“计算机应用基础”的作者姓名和出版社的值
#分别赋给变量name和publish,并输出出版社变量值
DELIMITER $$
CREATE PROCEDURE sp_vtest (OUT p VARCHAR(20))
BEGIN
DECLARE v_name,v_publish VARCHAR(20);
SELECT author,publisher INTO v_name, v_publish FROM Book WHERE bookname= '计算机应用基础';
SET p = v_publish;
END
$$
DELIMITER ;

CALL sp_vtest(@p);
SELECT @p;

3-2-2 语句结构

3-2-2-1 判断结构赏析

案例1-两数相比

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DELIMITER $$
CREATE PROCEDURE sp_compare
(IN K1 INTEGER, IN K2 INTEGER, OUT K3 CHAR(6) )
BEGIN
IF K1>K2 THEN
SET K3= '大于';
ELSEIF K1=K2 THEN
SET K3= '等于';
ELSE
SET K3= '小于';
END IF;
END
$$
DELIMITER ;

CALL sp_compare(3, 6, @K);
SELECT @K;

案例2-

形态1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DELIMITER $$
CREATE PROCEDURE sp_updown1(IN str VARCHAR(1), OUT direct VARCHAR(4) )
BEGIN
CASE str
WHEN 'U' THEN SET direct ='上升';
WHEN 'D' THEN SET direct ='下降';
ELSE SET direct ='不变';
END CASE;
END
$$
DELIMITER;

CALL sp_updown1('s',@d);
SELECT @d;

形态2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DELIMITER $$
CREATE PROCEDURE sp_updown2(IN str VARCHAR(1), OUT direct VARCHAR(4) )
BEGIN
CASE
WHEN str = 'U' THEN SET direct ='上升';
WHEN str = 'D' THEN SET direct ='下降';
ELSE SET direct ='不变';
END CASE;
END
$$
DELIMITER;

CALL sp_updown2('s',@d);
SELECT @d;

3-2-2-2 循环结构

案例1- WHILE …DO

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#WHILE ...DO循环结构
DELIMITER $$
CREATE PROCEDURE sp_while()
BEGIN
DECLARE v1 INT DEFAULT 5;
WHILE v1 > 0 DO
SET v1 = v1-1;
SELECT v1;
END WHILE;
END
$$
DELIMITER ;

CALL sp_while()

案例2-REPEAT…UNTIL - 类似do…while结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#REPEAT...UNTIL - 类似do...while结构
DELIMITER $$
CREATE PROCEDURE sp_repeat()
BEGIN
DECLARE v1 INT DEFAULT 5;
REPEAT
SET v1=v1-1;
SELECT v1;
UNTIL v1<1
END REPEAT;
END
$$
DELIMITER ;

CALL sp_repeat();

案例3 数据库存储结构独特结构:LOOP…LEAVE 类似 for…break…continue

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
#数据库存储结构独特结构:LOOP...LEAVE 类似 for...break...continue
DELIMITER $$
CREATE PROCEDURE sp_sum(IN n INT)
BEGIN
DECLARE v_sum INT DEFAULT 0;
#label是做一个标识,写法是必须在后面带冒号“label:”方便后面写leave,iterate;
label:LOOP
IF n <= 0 THEN
LEAVE label;
END IF;
SET v_sum = v_sum + n;
SET n = n - 1;
END LOOP label;
SELECT v_sum;
END
$$
DELIMITER ;

CALL sp_sum(100)

iterate 可以当做continue来使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#数据库存储结构独特结构:LOOP...LEAVE 类似 for...break...continue
DELIMITER $$
CREATE PROCEDURE sp_sum(IN n INT)
BEGIN
DECLARE v_sum INT DEFAULT 0;
#label是做一个标识,写法是必须在后面带冒号“label:”方便后面写leave,iterate;
label:LOOP
IF n <= 0 THEN
LEAVE label;
END IF;
IF n%2=1 THEN
SET n = n-1;
ITERATE label;
END IF;
SET v_sum = v_sum + n;
SET n = n - 1;
END LOOP label;
SELECT v_sum;
END
$$
DELIMITER ;

3-2-3 业务案例赏析

案例1-修改销售价格

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
#根据用户姓名和书名查询订单,如果订购册数小于5本不打折,
#订购册数在5-10本之间,订购单价打九折,订购册数大于10本,订购单价打八折。
DELIMITER $$
CREATE PROCEDURE
sp_updateSellPrice(IN m_name CHAR(10), IN b_name VARCHAR(40))
BEGIN
DECLARE v_bookcode CHAR(20);
DECLARE v_mcode CHAR(10);
DECLARE v_amount TINYINT;
SELECT mcode INTO v_mcode FROM member
WHERE NAME=m_name;
SELECT bookcode INTO v_bookcode FROM Book WHERE bookname=b_name;
SELECT amount INTO v_amount FROM sell
WHERE mcode=v_mcode AND bookcode=v_bookcode;
SELECT v_bookcode,v_mcode,v_amount;

IF v_amount>=5 AND v_amount<=10 THEN
UPDATE sell SET price=price*0.9
WHERE mcode=v_mcode AND bookcode=v_bookcode;
ELSEIF v_amount>10 THEN
UPDATE sell SET price=price*0.8
WHERE mcode=v_mcode AND bookcode=v_bookcode;

END IF;
END$$
DELIMITER ;

3-2-4 中级考试案例

案例1:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
DELIMITER //
CREATE PROCEDURE insertProc(IN p INT)
BEGIN
DECLARE var INT;
SET var = p+1;
CASE var
WHEN 0 THEN
INSERT INTO B VALUES(20);
WHEN 1 THEN
INSERT INTO B VALUES(21);
ELSE
INSERT INTO B VALUES(22);
END CASE;
END;
//
DELIMITER;

#有存储过程创建如上,语句CALL insertProc(0)插入B表中的值是?
CALL insertProc(0);

案例2:

1
2
3
4
5
6
7
8
9
10
11
12
13
DELIMITER //
CREATE PROCEDURE proc()
BEGIN
DECLARE var INT;
SET var = 0;
WHILE var < 4 DO
INSERT INTO B VALUES(var);
SET var = var + 1;
END WHILE;
END;
//
DELIMITER;
#关于上述存储过程,执行insert语句几次?

4 存储函数

创建

参考代码1

1
2
3
4
5
6
7
8
#创建一个存储函数,它返回Book表中图书数目作为结果。
DELIMITER $$
CREATE FUNCTION fun_bookCount()
RETURNS INTEGER
BEGIN
RETURN (SELECT COUNT(*) FROM Book);
END$$
DELIMITER ;

参考代码2

1
2
3
4
5
6
7
8
9
10
11
#创建一个存储函数,返回Book表中某本书的作者姓名。
DELIMITER $$
CREATE FUNCTION fun_bookAuthor(b_name VARCHAR(40))
RETURNS CHAR(10)
BEGIN
DECLARE res_author CHAR(10);
SELECT author INTO res_author FROM book WHERE bookname= b_name;
RETURN res_author;
END$$
DELIMITER ;

查看

参考代码

1
SHOW FUNCTION STATUS;

调用

参考代码

1
2
SELECT fun_bookCount();
SELECT fun_bookAuthor('网页设计');

删除

参考代码

1
DROP FUNCTION fun_bookCount;

5 触发器

创建

参考代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#当删除表book表中某图书的信息时,同时将sell表中与该图书有关的数据全部删除
DELIMITER $$
CREATE TRIGGER t_book_del AFTER DELETE
ON Book FOR EACH ROW
BEGIN
DELETE FROM sell WHERE bookcode=old.bookcode;
END$$
DELIMITER ;

#现在验证一下触发器的功能:
DELETE FROM book WHERE bookcode = 'TP23/6625';

#使用SELECT语句查看Sell表中的情况:
SELECT * FROM sell WHERE bookcode = 'TP23/6625';

触发

  • 触发时间: AFTER BEFORE
  • 触发事件: insert update delete

查看

参考代码

1
SHOW TRIGGERS

删除

参考代码

1
DROP TRIGGER trigger_name