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
#声明一个整型变量和两个字符变量。
#局部变量只能在BEGIN…END语句块中声明
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