Mysql

库

CREATE DATABASE myschool; #创建数据库
SHOW DATABASES; #查看数据库

表

# 创建表
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptid` int(11) NOT NULL AUTO_INCREMENT,
  `deptname` varchar(50) DEFAULT NULL,
  `deptnum` int(11) DEFAULT NULL,
  `deptdesc` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`deptid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 删除表
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empid` int(11) NOT NULL AUTO_INCREMENT,
  `empname` varchar(255) DEFAULT NULL,
  `empsex` varchar(255) DEFAULT NULL,
  `empage` int(255) DEFAULT NULL,
  `empphone` varchar(255) DEFAULT NULL,
  `empaddress` varchar(255) DEFAULT NULL,
  `empdate` datetime DEFAULT NULL,
  `deptid` int(11) DEFAULT NULL,
  PRIMARY KEY (`empid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
# 修改表
# ALTER TABLE 表名称 RENAME [TO] 新表名称

#判断表是否存在
DROP TABLE IF EXISTS demo01;
CREATE TABLE demo01(
   id INT(4) Not NULL AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(10) NOt NULL
);
ALTER TABLE demo01 RENAME demo02

#添加字段
# ALTER TABLE 表名称 ADD 字段名 数据类型 [属性]
ALTER TABLE demo02 ADD `password` VARCHAR(20) NOT NULL;

#修改字段
# ALTER TABLE 表名称 CHANGE 原字段名称 新字段名称 数据类型【属性】
ALTER TABLE demo02 CHANGE `name` username VARCHAR(10) NOT NULL

#删除字段
# ALTER TABLE 表名称 DROP 字段名称
ALTER TABLE demo02 DROP `password` 

#添加主键
# ALTER TABLE 表名称 ADD CONSTRAINT 主键名 PRIMARY KEY(主键字段名称)
ALTER TABLE demo02 ADD CONSTRAINT PK_id PRIMARY KEY(id)

#添加外键
# ALTER TABLE 表名称 ADD CONSTRAINT 外键名 FOREIGN KEY(外键字段) REFERENCES 关联表名称(关联字段)
ALTER TABLE emp ADD CONSISTENT FK_deptid FOREIGN KEY(deptid) REFERENCES dept(deptid)

新增

# 新增
# INSERT INTO 表名(列名) values(字段值)

INSERT INTO dept
   ( `deptname`, `deptnum`, `deptdesc`)
VALUES
   (‘财务部‘, 3, ‘财务‘);

INSERT INTO dept
VALUES
(null, ‘行政部‘, 2, ‘行政‘);

INSERT INTO emp
   (`empid`, `empname`, `empsex`, `empage`, `empphone`, `empaddress`, `empdate`, `deptid`)
VALUES
   (null, ‘曾小贤‘, ‘男‘, 22, ‘133346122834‘, ‘上海‘, "2012-5-1 16:15:32", 2);
INSERT INTO emp
   (`empid`, `empname`, `empsex`, `empage`, `empphone`, `empaddress`, `empdate`, `deptid`)
VALUES
   (null, ‘吕子乔‘, ‘男‘, 24, ‘13973627234‘, ‘上海‘, "2012-4-1 16:16:53", 2);

INSERT INTO emp
   (`empid`, `empname`, `empsex`, `empage`, `empphone`, `empaddress`, `empdate`, `deptid`)
VALUES
   (null, ‘美嘉‘, ‘女‘, 21, ‘13323456734‘, ‘杭州‘, "2012-6-5 12:48:40", 1);


INSERT INTO emp
   (`empid`, `empname`, `empsex`, `empage`, `empphone`, `empaddress`, `empdate`, `deptid`)
VALUES
   (null, ‘胡一菲‘, ‘女‘, 20, ‘13823452346‘, ‘杭州‘, "2012-6-19 12:49:37", 1),
   (null, ‘李婉钰‘, ‘女‘, 23, ‘13823458761‘, ‘上海‘, "2012-6-5 14:19:24",null),
   (null, ‘展博‘, ‘男‘, 23, ‘13627348238‘, ‘南京‘, "2012-4-8 17:06:46", 3),
   (null, ‘关谷神奇‘, ‘男‘, 23, ‘138234?15672‘, ‘北京‘, "2012-4-1 17:08:28", 4);

修改

# 修改
# UPDATE 表名 SET 字段名称 = 字段值, .. where ...

简单查询

#简单查询
# SELECT */列名 别名 FROM  表名

#查询全部
SELECT * FROM dept;

#查询返回指定的列
SELECT deptid, deptname FROM dept

#查询带别名
SELECT deptid 部门编号, deptname 部门名称 FROM dept;

SELECT deptid AS ‘部门编号‘, deptname AS ‘部门名称‘ FROM dept;

# 查询去掉重复项
SELECT DISTINCT deptnum FROM dept;

限定查询

#限定查询 
#查询员工年龄大于23岁的雇员
SELECT * FROM emp where empage > 23

#查询没有员工地址的雇员信息
SELECT *  FROM emp where empaddress = ‘‘; #曾经存在后来删除，存储的是empty
UPDATE emp set empaddress = null where empid = 4;
SELECT *  FROM emp where empaddress = NULL; # 查询不出来，需要使用is
SELECT *  FROM emp where empaddress is NULL

#查询年龄大于20，同时员工地址为上海的雇员信息
SELECT * FROM emp where empage > 20 and empaddress = ‘上海‘

#查询年龄不大于20，且员工地址不为上海的雇员信息
SELECT * FROM emp where empage <= 20 and empaddress != ‘上海‘
SELECT * FROM emp where NOT (empage > 20 OR empaddress = ‘上海‘)

#查询年龄大于24，或员工地址为上海的雇员信息
SELECT * FROM emp where empage >= 24 OR empaddress = ‘上海‘

#查询年龄在20岁到23岁之间的雇员信息
SELECT * FROM emp where empage BETWEEN 20 AND 23
SELECT * FROM emp where empage >= 20 and empage <= 23

#查询入职日期在2012-3-1日之后的元素
SELECT * FROM emp where empdate >= ‘2012-4-8‘

#查询员工编号为1,2,3号的员工
SELECT * FROM emp where empid  in(1,2,3)

#查询员工编号不为1,2,3号的员工
SELECT * FROM emp where empid not in(1,2,3)

#查询员工姓名为曾小贤，胡一菲的两名员工信息
SELECT * FROM emp where empname in(‘曾小贤‘,‘胡一菲‘)

#查询所有员工中姓名带“小”的
SELECT *  from emp where empname like ‘%小‘ #以小结尾
SELECT *  from emp where empname like ‘曾%‘ #以曾开头
SELECT *  from emp where empname like ‘%小%‘ #含小就行

#查询姓名中第二个字为“一”的员工信息
SELECT *  from emp where empname like ‘_一%‘

#查询2012年入职的员工
SELECT *  from emp where empdate like ‘2012%‘

#在操作条件中还可以使用 >、>=、<、<= 等条件符号
#不等于在sql中可有两种形式:”<>”、“!=”
select * from emp where empid != 1

查询结果排序

#对查询结果进行排序

#要求员工年龄由低到高排序
SELECT * FROM emp ORDER BY empage
SELECT * FROM emp ORDER BY empage ASC

#要求员工年龄由高到低排序
SELECT * FROM emp ORDER BY empage DESC

#要求查询出2012-04之后入职的员工，
#查询信息按员工年龄由高到低，如年龄相等，按雇员编号的降序排列
SELECT * FROM emp
where empdate > ‘2012-04-08‘
ORDER BY empage DESC, empid DESC

关联查询

#表之间的关联关系	
/**
  学生表： student   商品 --- 订单  --- 会员
  课程表： subject   员工(多) --- 部门(一)
  成绩表： result
  年级表   grade
  
  关系型数据中的3种关系 
  一对一：学生表 --- 学生详情
  一对多/多对一： grade(一) --- student(多)  / 一（会员） --- 多（订单）
  多对多 ： student(多) --- subject(多) / 多(商品) --- 多(订单)

  表中如何确定唯一条记录？
    通过主键的设计

  表如何表达关联关系？
   1.通过表中设计外键，也就是说外键就是表达关联关系的字段.
   2.一般在多方设置外键。在多方表添加一个字段，表达关联关系。
   3.外键列名称 = 关联表主键列名称
   4. 一个表可以有多个外键，也可以有多个主键（双主键）
   
   
   作用：
    关系是为了保证数据库中数据的完整性
  
*/
 # 多表查询
 #同时查询部门和员工表
SELECT * FROM dept, emp; #24   4部门 * 6员工

#查询dept/emp数量
SELECT count(*) FROM dept; #4
select count(*) from emp; #6

#掉笛卡尔乘积的查询
select * from dept, emp where dept.deptid = emp.deptid;

#查询员工编号，员工姓名，员工性别，员工年龄，员工地址，所属部门名称
select B.empid as 员工编号, B.empname as 员工姓名, B.empsex as 员工性别, B.empage as 员工年龄, B.empaddress as 员工地址,A.deptname as 所属部门名称
from dept as A, emp as B
where  A.deptid = B.deptid

#内连接、左连接、右连接
#内连接
select emp.*, dept.deptname
from dept INNER JOIN emp ON dept.deptid = emp.deptid;

#左连接 --- 以左边表为主，关联与否都查
select *
from dept LEFT JOIN emp ON dept.deptid = emp.deptid;

#右连接  --- 以右边表为主，关联与否都查
select *
from dept RIGHT JOIN emp ON dept.deptid = emp.deptid;

#子查询
#子查询是一个嵌套在select insert update delete语句中的查询语句
#查询技术部下所有员工信息
select *
from dept, emp
where dept.deptid = emp.deptid and dept.deptname = ‘技术部‘
#使用子查询
select * from emp where deptid = (select deptid from dept where deptname = ‘技术部‘)
#=号下不能出现多个值
select * from emp where deptid = (select deptid from dept where deptname like ‘%部‘)

#查询已经分配部门信息的所有员工
#使用关联
select * from dept INNER JOIN emp on dept.deptid = emp.deptid
#使用子查询
select * from emp where true;
select * from emp where 1=1;
#使用exists
select * from emp where EXISTS(select deptid from dept where dept.deptid = emp.deptid)

#查询未分配部门信息的所有员工
select * from emp where NOT EXISTS(select deptid from dept where dept.deptid = emp.deptid)

#分组，统计
#查询“男”,"女"员工的个数

#以性别为标准进行分组 --- 以什么进行分组，查询就需要展示什么
select empsex from emp GROUP BY empsex

#全部记录数
select count(*) from emp;
select count(1) from emp;

#max最大值
select MAX(empage) from emp;

#min最小值
select MIN(empage) from emp;

#avg平均值
select avg(empage) from emp;

#sum 总
select sum(empage) from emp;

#查询“男”,"女"员工的个数
select empsex as 性别, count(1) as 人数 from emp GROUP BY empsex;

#查询每个部门下的员工人数
select deptid as 部门编号, count(1) as 部门人数
from emp GROUP BY deptid;

select dept.deptname, count(emp.empid)
from dept LEFT JOIN emp on dept.deptid = emp.deptid
GROUP BY dept.deptname;

#查询每个部门下的平均年龄
select dept.deptname, avg(emp.empage)
from dept LEFT JOIN emp on dept.deptid = emp.deptid
GROUP BY dept.deptname;

#HAVING ---在分组的基础上过滤


#查询员工平均年龄在23之上的所有部门
select dept.deptname, avg(emp.empage)
from dept LEFT JOIN emp on dept.deptid = emp.deptid
GROUP BY dept.deptname HAVING avg(emp.empage) >= 23

#分页 --- limit 排除的记录数, 一次显示的记录数
select * from emp ORDER BY empid LIMIT 0, 2 #1 (1-1)*2，2
select * from emp ORDER BY empid LIMIT 2, 2 #2 (2-1)*2, 2
select * from emp ORDER BY empid LIMIT 4, 2 #3 (3-1)*2, 2
# LIMIT (当前页 - 1) * 分页标准， 分页标准

子查询

#子查询
#子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其他子查询中的查询
#一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询在外面的查询语句，称为主查询或外查询
#语法：SELECT … FROM 表1 WHERE 字段1  比较运算符(子查询)

子查询特点

1、子查询都放在小括号内
2、子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
3、子查询优先于主查询执行，主查询使用了子查询的执行结果
4、子查询根据查询结果的行数不同分为以下两类：
	① 单行子查询
	  结果集只有一行
	  一般搭配单行操作符使用：> < = <> >= <=
	非法使用子查询的情况：
       a、子查询的结果为一组值
       b、子查询的结果为空
    ② 多行子查询
      结果集有多行
      一般搭配多行操作符使用：any、all、in、not in
      in： 属于子查询结果中的任意一个就行
      any和all往往可以用其他查询代替

#查询技术部下的所有员工信息
SELECT emp.*,dept.deptname
FROM emp, dept
WHERE emp.deptid = dept.deptid and dept.deptname = ‘技术部‘
#使用子查询
SELECT * FROM emp where deptid = (select deptid from dept where deptname = ‘技术部‘)
#=号下不能出现多个值
SELECT * FROM emp where deptid = (select deptid from dept where deptname like ‘%部‘) #不能执行

#查询已经分配部门信息系的所有员工
#使用关联查询
SELECT emp.* from dept INNER JOIN emp on dept.deptid = emp.deptid
#使用子查询
SELECT * from emp where true;
SELECT * from emp where 1=1
SELECT * from emp where false;
#使用exists
SELECT * from emp where EXISTS(select deptid from dept WHERE dept.deptid = emp.deptid);

#查询没有分配部门信息系的员工
SELECT * from emp where not EXISTS(select deptid from dept WHERE dept.deptid = emp.deptid);


#any 是任意一个, any表示有任何一个满足就返回true
#查询技术部中，年龄大于销售部下任意一位的员工(比最小的还要大)
select * FROM emp WHERE deptid = 1 
and empage > ANY(select empage from emp where deptid = 2)
#相当于
select * from emp where deptid = 1 and empage >
 ANY(select MIN(empage) from emp where deptid = 2)

# all 是所有, all表示全部都满足才返回true 

#查询技术部中，年龄大于销售部下所有人的员工（比最大的还要大）
select * from emp where deptid = 1 and empage >
 all(select empage from emp where deptid = 2)
#相当于：
select * from emp where deptid = 1 and empage >
 all(select max(empage) from emp where deptid = 2)

视图

# 视图
  # 含义：可以理解为一张虚拟的表
  # 区别：
      # 1. 视图不占用物理空间，仅仅保存的是sql语句（逻辑）
  # 相同点：
      # 使用方式相同 select * from 视图名
  # 好处：
      # 1.sql语句提高重用性，效率高
      # 2.提高安全性

#视图创建
 #语法 CREATE VIEW 视图名
 #      AS
 #      查询语句;

#演示 --- 查询员工信息和隶属部门名称
CREATE VIEW my_v1
AS
  SELECT emp.*, dept.deptname
  FROM dept, emp 
  where dept.deptid = emp.deptid

#使用视图
select * from my_v1;

#删除视图
  #DROP VIEW 视图名
#查看视图
  # DESC 视图名
  # SHOW CREATE VIEW 视图名

DESC my_v1;

事务

#事务:数据库事务(Transaction)
#事务：通过一组逻辑操作单元（一组DML语句），将数据从一种状态切换到另一种状态
#目的：保证数据的完整性
/**
    案例：转账 张三1000 转账给 李四 200

    步骤1： update 表名 set  张三余额 = 张三余额-200 where name = ‘张三‘
    出错
    步骤2： update 表名 set  李四余额 = 李四余额+200 where name = ‘李四‘
    
 事务的特点：
    1、原子性：不可分割，要么都执行，要么都回滚
    2、一致性：保证数据的操作状态在前后一致
    3、隔离型：多个事务同时操作数据库中同一个表的数据，一个事务的执行不受另一事务的影响或干扰。
    4、持久性：一个事务一旦被提交 ，则数据库被持久化到本地，除非其他事务进行修改

  mysql使用事务 ---》jdbc操作事务 ---》持久化框架（hibernate、mybatis，JPA）操作事务 --->spring操作事务（AOP\oop）
  使用事务步骤：
    1、开启事务
    2、编写事务的一组逻辑执行单元.（多条SQL语句insert、update、delete）
    3、提交或回滚事务

  事务分类：
    1、隐式事务：没有明显的开启和结束事务标志
         insert、update、delete本身就是一个事务
    2、显示事务：
          |- 开启事务： set autocomit = 0 （取消自动提交的功能）
          |- 编写事务的一组逻辑操作单元： SQL（增、删、改）
          |- 提交或回滚： COMMIT 、ROLLBACK

**/
 #演示事务操作步骤
  #1、开启事务
   SET AUTOCOMMIT = 0;
  #2、编写SQL语句
  DELETE from emp where empid in (3,4,6);
  UPDATE dept set deptnum = deptnum - 2 where deptid = 1;
  UPDATE dep set deptnum = deptnum - 1 where deptid = 3;
  #3、提交或回滚
  #ROLLBACK;
  COMMIT; #一组逻辑操作单元中，只有全部执行成功，才会commit，否则回滚 
 
 #演示操作2
 BEGIN;
DELETE FROM emp where empid = 7;
update dept set deptnum = deptnum - 1 where deptid = 4;
#ROLLBACK;
#commit;
  
set autocommit=0;
start transaction;
commit;
rollback;
savepoint 断点
commit to 断点
rollback to 断点

事务隔离

# 事务的隔离级别

# 事务并发问题如何发生？
   # 多个事务同时操作同一个数据库的相同数据

# 事务的并发问题有哪些？
   # 脏读： 一个事务读取了另一事务未提交的数据
   # 不可重复读：同一事务中，多次读取的数据不一致
   # 幻读：一个事务读取数据时，另一个事务进行更新，导致第一个事务读取到了没有更新的数据

# 如何避免事务并发问题？
   # 通过设置事务的隔离级别
    # 1. READ UNCOMMITTED
    # 2. READ COMMITTED :可以避免脏读
    # 3. REPEATABLE READ : 可以避免脏读，不可重复读和一部分幻读
    # 4. SERIALIZABLE : 可以避免脏读，不可重复读，幻读
# 设置隔离级别
   # set session | GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别名
# 查看隔离级别
   # SELECT @@tx_isolation;

 # 后期学习spring事务用到

变量、存储过程&函数

#变量
/**
  1、系统变量：系统提供的，有服务器提供
   -- 全局变量、会话变量
  2、自定义变量
   -- 用户变量、局部变量
  
**/
#查看系统变量
 #  语法：show GLOBAL | SESSION VARIABLES
SHOW GLOBAL VARIABLES;

#查询满足条件的部分系统变量
#语法：show GLOBAL | SESSION VARIABLES LIKE ‘%char%‘
SHOW GLOBAL VARIABLES LIKE ‘%char%‘

#自定义变量
/**
 用户变量：(针对当前连接/会话有效)
 使用步骤：1、声明 2、赋值 3、使用
 
 赋值操作符：=或:=

 1、声明并初始化：
   SET @用户变量名=值;
   SET @用户变量名:=值;
   SELECT @用户变量名:=值;
 2、赋值（更改初始值）
   方式一：
     SET @用户变量名=值;
     SET @用户变量名:=值;
     SELECT @用户变量名:=值;
   方式二:
    SELECT 字段 into @变量名 FROM 表名

 3、查看变量值
   SELECT @变量名

**/
#赋值、方式一：
SET @name = ‘admin‘; #声明并初始化
SET @name = 100; #(类型不限制)

#方式二:
SELECT count(1) into @count
from emp;

#查看变量值
SELECT @name;
SELECT @count;

#局部变量 : 仅仅在BEGIN END中有效,并且是第一句话
#声明：限定类型
 # DECLARE 变量名 类型
 # DECLARE 变量名 类型 DEFAULT 值

#赋值
 # 方式一：
   # SET 变量名=值
   # SET 变量名:=值
   # SELECT @用户变量名:=值
 # 方式二：
   # SELECT 字段 into @变量名 FROM 表名
#使用
 # SELECT 变量名


#存储过程(Stored PROCEDURE)
# 存储过程是数据库中存储复杂程序，以便外部程序调用的一种数据库对象
# 存储过程就是具有名字的一段代码，用来完成特定的功能（类型与java中方法）

#1、创建存储过程
# CREATE PROCEDURE 存储过程名称(参数列表)
# BEGIN
   #存储过程体(一组合法的SQL语句)
# END;

#参数说明
# 1、参数由三部分构造
   # 参数模式、参数名称、参数类型
   # 举例: IN stuName VARCHAR(20)
  #参数模式说明：
    #IN: 输入模式--- 该参数可以作为输入，调用时需要传入值
    #OUT: 输出模式 --- 该参数可以作为输出，参数可以作为返回值
    #INOUT: 该参数可以作为输入也可以作为输出，即可以输入值，也返回值
  # 如果存储过程仅仅有一句话，则BEGIN END可以省略
  # 存储过程的每一条SQL语句的结尾必须添加分号
  

 #2、调用存储过程
# CALL 存储过程名称(实参列表)

#演示：
# 1、空参列表(添加多条记录)
CREATE PROCEDURE my_f1()
BEGIN
  INSERT INTO dept values(null,‘人事部‘,20,‘为人民服务‘);
  INSERT INTO dept values(null,‘中组部‘,20,‘为人民服务‘);
END;

#调用
CALL my_f1();

#2、创建带IN参数的存储过程(根据员工姓名查询员工信息)
CREATE PROCEDURE my_p2(IN emp_name VARCHAR(20))
BEGIN
 SELECT * FROM emp where empname = emp_name;
END;

#调用
CALL my_p2(‘曾小贤‘)

# 3、创建带OUT的存储过程(根据部门名称返回员工个数)
CREATE PROCEDURE my_p3(IN dept_name VARCHAR(20), OUT empCount INT)
BEGIN
 SELECT count(1) INTO empCount
 FROM dept INNER JOIN emp 
 WHERE dept.deptid = emp.deptid AND dept.deptname = dept_name;
END;

#调用
SET @emp_name = ‘财务部‘;
SET @empCount = 0;
CALL my_p3(@emp_name, @empCount);
#输出
SELECT @empCount;

#4、带INOUT模式的存储过程(输入A和B两个值，最后A和B翻倍并返回)
CREATE PROCEDURE my_p4(INOUT a INT, INOUT b INT)
BEGIN
  SET a = a * 2;
  SET b = b * 2;
END;

#使用
SET @n = 10;
SET @m = 20;
CALL my_p4(@n,@m);
SELECT @n, @m

# 3、删除存储过程
# DROP PROCEDURE 存储过程名
DROP PROCEDURE my_p4

#4、查看存储过程
# SHOW CREATE PROCEDURE 存储过程名称
SHOW CREATE PROCEDURE my_p3

#函数
 # 函数和存储过程基本类似，区别为：
 #  1、存储过程可以有0个或多个返回值，适合做批量的更新和添加
 #  2、函数有且仅有一个返回值

#1、创建函数
# CREATE FUNCTION 函数名(参数列表) RETURNS 返回值类型
# BEGIN
#   
# END; 
#说明：参数列表包含两部分：参数名、参数类型
     # 函数体一定有return语句，没有则报错
 
# 2、调用函数
#  SELECT 函数名(参数列表)

#1、创建没有返回值的函数(返回员工人数)
CREATE FUNCTION my_f1()RETURNS INT
BEGIN
 #定义变量
 DECLARE c INT DEFAULT 0;
 #赋值
 SELECT count(1) INTO c
 FROM emp;
 #返回
 RETURN c;
END;

#调用
SELECT my_f1();

#2、有参数由返回值(根据员工姓名返回员工年龄)
CREATE FUNCTION my_f2(emp_name VARCHAR(20))RETURNS INT
BEGIN
  #定义变量
  SET @empage = 18;
  #赋值
  SELECT empage INTO @empage
  FROM emp 
  WHERE empname = emp_name;
  #返回
  RETURN @empage;
END;

#调用
SELECT my_f2(‘吕子乔‘)

#查看函数
SHOW CREATE FUNCTION 函数名

#删除函数
DROP FUNCTION 函数名

流程控制

# 流程控制
# 顺序结构、选择结构、循环结构

#选择结构（分支结构）
#一、if函数
 #语法： `IF`(条件,值1,值2)： 特点：可以在任意位置
SELECT IF(5 < 3,‘true‘,‘false‘);

#二、case语句
#语法：
 #情况一： 类似于switch
 # CASE 表达式
 #  WHEN 值1 THEN 语句1;
 #  WHEN 值2 THEN 语句2;
 #  ...
 #  ELSE 语句n;
 # END CASE;
 
 # 情况二: 类似于多重if
 # CASE 
 #  WHEN 条件1 THEN 语句1;
 #  WHEN 条件2 THEN 语句2;
 #  ...
 #  ELSE 语句n
 # END CASE;

#演示：创建存储过程，根据传入的成绩，来显示等级90-100 A 80-90 B 60-80 C 否则 D
CREATE PROCEDURE test_case(IN score INT)
BEGIN
  CASE
    WHEN score >= 90 AND score <= 100 THEN SELECT ‘A‘;
    WHEN score >= 80 AND score < 90 THEN SELECT ‘B‘;
    WHEN score >= 60 AND score < 80 THEN SELECT ‘C‘;
    ELSE SELECT ‘D‘;
  END CASE;
END;

#调用
CALL test_case(100);

#三、 if ELSEIF 语句
#语法：
# IF 条件1 THEN 语句1;
#  ELSEIF 条件2 THEN 语句2;
#  ...
#  ELSE 语句n
# END IF;

#演示： 创建函数过程，根据传入的值，返回等级90-100 A 80-90 B 60-80 C 否则 D
CREATE FUNCTION test_if(score INT)RETURNS CHAR
BEGIN
  IF score >= 90 AND score <= 100 THEN RETURN ‘A‘;
    ELSEIF score >= 80 AND score < 90 THEN RETURN ‘B‘;
    ELSEIF score >= 60 AND score < 80 THEN RETURN ‘C‘;
    ELSE RETURN ‘D‘;
  END IF;
END;

#DROP FUNCTION test_if;

#调用
SELECT test_if(70)

# 循环 
# WHILE
# 语法：
# [标签:] WHILE 循环条件 DO
#           循环体
#         END WHILE[标签];

# 循环控制：
   # ITERATE 类似于 CONTINUE 结束本次循环，继续下一次循环
   # LEAVE 类似于 break; 跳出循环，结束当前循环 

# 演示：创建存储过程，根据次数循环添加记录（没有添加循环控制）
CREATE PROCEDURE pro_while(IN insert_count INT)
BEGIN
  #声明变量
  DECLARE i INT DEFAULT 1;
  #循环添加
  WHILE i <= insert_count DO #循环结束条件
    INSERT INTO dept values(null, CONCAT(‘测试部‘,i), 10, ‘测试‘); #循环体
    SET i = i + 1;  #累加
  END WHILE; #循环结束
END;

#调用
CALL pro_while(3);

#演示：创建存储过程，根据次数添加记录，如果次数大于10 则停止（循环控制）
CREATE PROCEDURE pro_while2(IN insert_count INT)
BEGIN
  #声明变量
  DECLARE i INT DEFAULT 1;
  #循环添加
  a:WHILE i <= insert_count DO #循环结束条件
    INSERT INTO dept values(null, CONCAT(‘测试部‘,i), 10, ‘测试‘); #循环体
    IF i >= 10 THEN LEAVE a; #判断结束循环
    END IF;
    SET i = i + 1;  #累加
  END WHILE a; #循环结束
END;

#调动
CALL pro_while2(100);

#演示循环控制（）根据次数插入到dept表中的多条记录，直插入偶数数据
#Int I = 0；
#While(i< insertCount){
#i++;
#if(i % 2 == 0){
#   continue;
# }
#  插入
# }
 
CREATE PROCEDURE pro_while3(IN insert_count INT)
BEGIN
 DECLARE i INT DEFAULT 0;
 a:WHILE i<=insert_count DO
   SET i=i+1;
   #IF MOD(i,2)!=0 THEN ITERATE a;
   IF i%2=0 THEN ITERATE a;
   END IF;
   INSERT INTO dept values(null, CONCAT("测试部",i), i, "测试");
 END WHILE a;
END

CALL pro_while3(10)