MySQL服务的启动和停止
方式一：计算机 - 右击管理 - 服务
方式二：以管理员身份运行命令行
net start 服务名(启动服务)
net stop 服务名(停止服务)
MySQL服务的登陆和退出
方式一：通过MySQL自带的客户端(有局限性，只限于root用户)
方式二：通过windows自带的客户端(以管理员身份运行命令行)
登陆：
mysql [-h主机名 -P端口号] -u用户名 -p密码
(注意：如果是本机，-h主机名 -P端口号可以省略，-p密码之间不允许有空格，其它可以有空格，也可无空格)
退出：
exit 或 ctrl + c（我尝试ctrl+c不管用） 或 \q 或 quit
注意：如果执行 mysql [-h主机名 -P端口号] -u用户名 -p密码 命令，结果显示：
【 "mysql" 不是内部或外部命令，也不是可运行的程序或批处理文件】
则需要把MySQL的安装的bin目录配置到path环境变量中.
MySQL的常见命令
1、查看当前所有的数据库
show databases;
2、打开指定的库
use 库名；
3、查看当前库的所有表
show tables;
4、查看其它库的所有表
show tables from 库名；
(注意：此命令是在当前库查看其它库的所有表)
5、查看当前位于哪个库
select database();
6、 创建表
create table 表名(
列名 列类型,
列名 列类型,
.......
);
7、查看表结构
desc 表名;
8、查看服务器的版本
方式一：登陆到mysql 服务端
select version();
方式二：没有登陆mysql服务端(以管理员身份运行命令行)
mysql --version
或
mysql -V
MySQL的语法规范
1、不区分大小写,但建议关键字大写，表名、列名小写
2、每条命令最好用分号结尾
3、每条命令根据需要，可以进行缩进 或换行
4、MySQL中可以使用单引号也可以使用双引号
5、注释
单行注释：#注释文字 或者 -- 空格 注释文字
多行注释：/* 注释文字 */
SQL的语言分类
DQL (Data Query Language)：数据查询语言 【select】
DML (Data Manipulate Language)：数据操作语言 【insert 、update、delete】
DDL (Data Define Language)：数据定义语言 【create、drop、alter】
TCL (Transaction Control Language)：事务控制语言 【commit、rollback】
DQL语言的学习
进阶一：基础查询
特点：
①通过select查询完的结果 ，是一个虚拟的表格，不是真实存在
② 要查询的东西 可以是常量值、可以是表达式、可以是字段、可以是函数
1、查询单个字段、多个字段、所有字段；
2、查询常量值： select 100; select 'join';
注意：字符型和日期型的常量值必须用单引号括起来，数值型不需要；
3、查询表达式： select 90 * 100;
4、查询函数(必须要有返回值)： select version();
5、起别名：AS 或者 空格
例如： select 100 % 98 AS 结果；
select 100 % 98 结果；
select 100 % 98 AS "OUT PUT"；
6、DISTINCT ：去重
7、MYSQL中 + 的作用：仅仅只有一个功能，即运算符
①、两个操作数都为数值，则做加法运算；
select 100 + 90;
②、如果其中一方为字符型，试图将字符型数值转换成数值型，
如果转换成功，则继续做加法运算； select '123' + 90; ---> 213
如果转换失败，则将字符型数值转换为0进行加法运算； select 'join' + 90; ---> 90
③、如果其中一方为null，则结果肯定为null ； select null + 10; ---> null
8、使用CONCAT函数实现字符连接：select CONCAT('a','b','c') as 结果； ---> abc
select CONCAT('a','null','c') as 结果； ---> anullc
select CONCAT('a',null,'c') as 结果； ---> null 只要有一个值为null,则结果为null
9、ifnull 函数功能：判断某字段或表达式的值是否为null，如果为null，返回指定的值，否则返回原本的值
select ifnull(commission_pct, 0) from employees; -->如果将近率commission_pct为null，则返回0，如果不为null，则返回原本的值
10、isnull 函数功能：判断某字段或表达式的值是否为null，如果为null，则返回1(表示true)，否则返回0(表示false)
select isnull(commission_pct) from employees;
进阶二：条件查询
1、条件查询：根据条件过滤原始表的数据，查询到想要的数据
2、语法：
select 要查询的字段|表达式|常量值|函数
from 表
where 条件 ;
3、分类：
一、条件表达式 示例：salary>10000
条件运算符： >、 <、 >=、 <=、 =、 !=、 <>
二、逻辑表达式 示例：salary>10000 && salary<20000
逻辑运算符：
and（&&）:两个条件如果同时成立，结果为true，否则为false
or(||)：两个条件只要有一个成立，结果为true，否则为false
not(!)：如果条件成立，则not后为false，否则为true
三、模糊查询 ：like、between and 、in 、is null 或 is not null
* like特点：
①、一般和通配符搭配使用，可以判断字符型或者数值型
通配符：
% ：任意多个字符，包含0个字符
_ ： 任意单个字符
示例：last_name like 'a%';
或者
department_id like '2%';
或者
last_name like '_$_%' ESCAPE '$'; 转义字符，第二个字符为_
或者
last_name like '_\_%' ; 转义字符，第二个字符为_
* between and 特点：
①、包含临界值，等价于 >= and <= ，所以两个临界值之间顺序不可以互换
* is null 或 is not null 特点：
①、= 或 <> 不能用于判断 null 值；
②、is null 或 is not null 只能用于判断 null 值；
* <=> 表示安全等于，既可以用于判断null值，也可以用于判断普通的数值，可读性较低
示例：1、查询没有奖金的员工
select * from employees where commission_pct is null;
或
select * from employees where commission_pct <=> null;
2、查询工资为12000的员工
select * from employees where salary<=> 12000;
或
select * from employees where salary = 12000;
案例：
1、查询员工号为176的员工的姓名和部门号以及年薪
select employee_id, department_id, salary*12*(1 + IFNULL(commission,0)) from employees where employee_id = 176
2、经典面试题
①、试问 select * from employees； 和 select * from employees where commission_pct like '%%' and last_name like '%%'；结果是否一样，请说明原因
不一样，因为commission_pct 可能为null
②、 试问 select * from employees； 和 select * from employees where commission_pct like '%%' or last_name like '%%' or employee_id like '%%'；结果是否一样，请说明原因
一样，因为是用or 连接，总有一个字段是没有null值的
进阶三：排序查询
语法：
select 查询列表
from 表名
where 条件
order by 排序的字段(单个或者多个字段) | 表达式 | 函数 | 别名 【asc|desc】
特点：
1、asc 代表升序，desc代表降序，如果不写，默认为升序
2、order by 子句一般放在查询语句的最后面，limit 子句除外
进阶四：常见函数
一、单行函数
1、字符函数
concat(str1, str2, str3, str4......)：拼接字符
substr / substring：截取字符 【 注意：索引是从1开始的】
upper(str)：把参数值转换成大写
lower(str)：把参数值转换成小写
trim：去除前后指定的空格和字符
例如：
select trim(' 张三 '); ---> 去除前后空格
select trim('a' from 'aaaaa张aaaa三aaaaaa'); ---> 去除前后指定字符a，结果为：张aaaa三
ltrim：去左边空格
rtrim：去右边空格
replace(str, from_str, to_str)：替换，把 str 中所有的 from_str 替换成 to_str
lpad(str, len, padstr)：用指定的字符padstr 左填充 str 到指定的长度 len【注意是字符个数，不是字节个数】，如果str 超过了指定的长度len，则对str 进行右截取
rpad(str, len, padstr)：用指定的字符padstr 右填充 str 到指定的长度 len【注意是字符个数，不是字节个数】，如果str 超过了指定的长度len，则对str 进行右截取
instr(str, substr)：返回子串 substr 在 str 中第一次出现的索引，如果没有找到，则返回0
length(str)： 获取参数值的字节个数(对于中文，utf8 -> 一个汉字3个字节，GBK -> 一个汉字2个字节)
2、数学函数
round ：四舍五入
例如：select round(1.46); ---> 1
select round(-1.56); ---> -2 （如果是负数，先取绝对值进行四舍五入，然后再加上负号）
select round(1.678, 2); ---> 小数点后保留2位，结果为：1.68
rand ：rand函数用于产生0（包含）到1（不包含）的随机数
rand有两种形式：
1、rand(),即无参数的，此时产生的随机数是随机的，不可重复的；
2、rand(n),即有参数的，如rand(2),相当于指定随机数生产的种子，那么这种情况产生的随机数是可重复的。
floor向下取整，返回小于等于该参数的最大整数
例如： select floor(1.99); ---> 1
select floor(-9.99); ---> -10
ceil(x)：向上取整，返回大于等于该参数的最小整数
例如： select ceil(1.002); ---> 2
select ceil(1.00); ---> 1
select ceil(-1.002); ---> -1
mod(n, m)：取余(n - n / m * m) 【注意：如果n 为负数，则结果为负数，如果n为正数，则结果为正数】
例如：select mod(10, 3); ---> 1
select mod(-10, -3); ---> -1
select mod(10, -3); ---> 1
truncate(X, D)：截断
例如： select truncate(1.6999, 1); --->表示小数点后保留1位，其余截断，即：1.6
3、日期函数
now：返回当前系统的日期+时间 select now();
curdate：返回当前系统的日期，不包含时间 select curdate();
curtime：返回当前系统的时间，不包含日期 select curtime();
可以获取指定的部分，比如：年、月、日、时、分、秒
select year(now()) 年,month(now()) 月 ,day(now()) 日,hour(now()) 时 ,minute(now()) 分 ,second(now()) 秒;
select monthname(now()) 月; --->获取月份的英文名称
str_to_date： 将日期格式的字符转换成指定格式的日期
例如：select str_to_date('4-3 1993', '%c-%d %y');
格式符 功能
%Y 四位的年份
%y 2位的年份
%m 月份(01, 02, ... 11, 12)
%c 月份(1, 2, ... 11, 12)
%d 日(01, 02, ...)
%H 小时(24小时制)
%h 小时(12小时制)
%i 分钟(00, 01, ... 59)
%s 秒(00, 01, ... 59)
date_format(date, format)：将日期转换成字符
例如：select date_format(now(), '%y年%m月%d日');
datediff(expr,expr2)：求两个日期之间相差的天数
4、流程控制函数
①、if(expr1, expr2, expr3) ： 处理双分支，相当于if else 的效果，对于表达式expr1,如果表达式成立为true，则返回expr2的值，否则返回expr3的值；
例如：select if(10 > 5, '正确', '错误');
②、case语句 ：处理多分支
情况1：处理等值判断
语法：
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1【如果是语句，要加分号，如果是值，则不加分号】
when 常量2 then 要显示的值2或语句2
.....
else 要显示的值n 或 语句n
end
例如：查询员工的工资，要求，如果部门号=30，显示的工资为1.1倍，
如果部门号=40，显示的工资为1.2倍，
如果部门号=50，显示的工资为1.3倍，
其他部门，显示的工资为原工资
select salary 原始工资, department_id,
case department_id
when 30 then salary * 1.1
when 40 then salary * 1.2
when 50 then salary * 1.3
else salary
end as 新工资
from employees;
情况2：处理条件判断
语法：
case
when 条件1 then 要显示的值1或语句1【如果是语句，要加分号，如果是值，则不加分号】
when 条件2 then 要显示的值2或语句2
.....
else 要显示的值n 或 语句n
end
例如：查询员工的工资情况，要求，如果工资 > 20000，显示A级别，
如果工资 > 15000，显示B级别，
如果工资 > 10000，显示C级别，
否则显示D级别
select salary，
case
when salary > 20000 then 'A'
when salary > 15000 then 'B'
when salary > 10000 then 'C'
else 'D'
end as 工资级别
from employees;
5、其他函数
version：版本 select version();
database：当前库 select database();
user：当前连接用户 select user();
password('字符')：返回该字符的加密形式
md5('字符')：返回该字符的md5加密形式
二、分组函数,用做统计使用，又称为 统计函数 或 聚合函数 或 组函数
sum ：求和
max ：最大值
min ：最小值
avg ：平均值
count ：计数
特点：
1、以上五个分组函数都忽略null值，除了count(*)
2、sum 和 avg 一般用于处理数值型, max、min、count 可以处理任何数据类型
3、都可以搭配distinct使用，用于统计去重后的结果
4、count的参数可以支持： 字段、*、常量值，一般放1
5、和分组函数一同查询的字段要求是group by 后的字段
建议使用 count(*)
进阶五：分组查询
语法：
select 分组函数, 查询的字段(要求必须出现才group by 后面的)
from 表
【where 筛选条件】
group by 分组的字段
【order by 子句】
注意： 查询列表比较特殊，要求是分组函数和 group by 后面的出现的字段
特点：
1、和分组函数一同查询的字段最好是分组后的字段
2、分组查询中的筛选条件分为两类
针对的表 位置 关键字
分组前筛选： 原始表 group by子句的前面 where
分组后筛选： 分组后的结果集 group by子句的后面 having
①、分组函数做条件肯定是放在having子句中
②、能用分组前筛选的就优先考虑使用分组前筛选的
3、group by 子句可以支持按单个字段分组, 也可以支持按多个字段分组, 字段之间用逗号隔开, 没有顺序要求，也可以支持表达式或函数分组
4、可以支持排序(排序放在整个分组查询的最后)
5、having 、order by 后可以支持别名
进阶六：连接查询
1、连接查询又称为多表查询，当查询的字段来自于多个表时，就会用到连接查询
2、笛卡尔乘积现象：表1 有 m 行，表2有 n 行，结果有 m*n 行
* 发生的原因：没有有效的连接条件
* 如何避免：添加有效的连接条件
3、分类
* 按年代分类：
①、sql 92标准：在MySQL中仅仅支持内连接
* 内连接
等值连接：
- 多表等值连接的结果为多表的交集部分
- n 表连接，至少需要 n - 1 个连接条件
- 多表的顺序没有要求, 多表没有主次
- 一般需要为表起别名, 提高阅读性和性能
- 可以搭配比如：排序、分组、筛选等子句使用
语法：
select 查询列表
from 表1 别名， 表2 别名
where 表1.key = 表2.key
【and 筛选条件】
【group by 筛选条件】
【having 筛选条件】
【order by 排序字段】
例如：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT(*)
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;
非等值连接：
语法：
select 查询列表
from 表1 别名， 表2 别名
where 非等值的连接条件
【and 筛选条件】
【group by 筛选条件】
【having 筛选条件】
【order by 排序字段】
例如：查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
AND g.`grade_level`='A';
自连接：
语法：
select 查询列表
from 表 别名1， 表 别名2
where 等值的连接条件
【and 筛选条件】
【group by 筛选条件】
【having 筛选条件】
【order by 排序字段】
案例：查询员工名和上级的名称
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;
②、sql99 标准(推荐)：支持内连接、外连接(左外连接+右外连接，不支持全外连接)、交叉连接
语法：
select 查询列表
from 表1 别名
【连接类型 inner|left outer|right outer|cross】 join 表2 别名 on 连接条件
【连接类型 inner|left outer|right outer|cross】 join 表3 别名 on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
* 内连接 ： inner
特点：
1、 可以添加排序、分组和筛选等；
2、inner 可以省略；
3、筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读；
4、inner join 连接和sql92 语法中的等值连接效果是一样的，都是查询多表的交集部分
- 等值连接：
举例：
1、查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
SELECT COUNT(*) 个数,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;
2、三表连接：查询员工名、部门名、工种名，并按部门名降序
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON e.`department_id`=d.`department_id`
INNER JOIN jobs j ON e.`job_id` = j.`job_id`
ORDER BY department_name DESC;
- 非等值连接：
举例：查询工资级别的个数>20的个数，并且按工资级别降序
SELECT COUNT(*),grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;
- 自连接：
举例：查询姓名中包含字符k的员工的名字、上级的名字
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.`manager_id`= m.`employee_id`
WHERE e.`last_name` LIKE '%k%';
* 外连接
应用场景：用于查询一个表中有，另一个表没有的记录
特点：
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接，left join左边的是主表
右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接=内连接的结果 + 表1中有但表2没有的 + 表2中有但表1没有的
左外连接 : left 【outer】
右外连接 : right 【outer】
案例：查询哪个部门没有员工
#左外
SELECT d.*,e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;
#右外
SELECT d.*,e.employee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;
全外连接 : full 【outer】
* 交叉连接 ：cross (sql 99语法中的笛卡尔乘积)
举例 :
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;
* 按功能分类
①、内连接
②、外连接
左外连接
右外连接
全外连接
③、交叉连接
进阶七：子查询
1、含义：出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询
2、分类：
* 按子查询出现的位置：
select后面：仅仅支持标量子查询
#案例：查询每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id = d.`department_id`
) 个数
FROM departments d;
from后面：支持表子查询, 将子查询结果充当一张表，要求必须起别名
#案例：查询每个部门的平均工资的工资等级
#①查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#②连接①的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal
SELECT ag_dep.*,g.`grade_level`
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
where或having后面：
标量子查询（单行子查询） √
列子查询 （多行子查询） √
行子查询 （结果集为一行多列 或 多行多列）
特点：
①、子查询放在小括号内
②、子查询一般放在条件的右侧
③、标量子查询，一般搭配着单行操作符使用 >、 < 、>=、 <=、 =、 <>
列子查询，一般搭配着多行操作符使用 in、any/some、all
例如：> any/some(子查询); 意思是大于子查询中的最小值即可【可以使用MIN()函数代替】
> all(子查询); 意思是大于子查询中的最大值即可【可以使用MAX()函数代替】
④、子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
#1.标量子查询(单行子查询)
案例1：返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资
#①查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141
#②查询143号员工的salary
SELECT salary
FROM employees
WHERE employee_id = 143
#③查询员工的姓名，job_id 和工资，要求job_id=①并且salary>②
SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
SELECT job_id
FROM employees
WHERE employee_id = 141
) AND salary>(
SELECT salary
FROM employees
WHERE employee_id = 143
);
案例2：查询最低工资大于50号部门最低工资的部门id和其最低工资
①查询50号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
②查询每个部门的最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
③ 在②基础上筛选，满足min(salary)>①
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
);
#2、列子查询(多行子查询)
案例1：返回location_id是1400或1700的部门中的所有员工姓名
①查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700);
②查询员工姓名，要求部门号是①列表中的某一个
SELECT last_name
FROM employees
WHERE department_id in (
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
);
# 等价于
SELECT last_name
FROM employees
WHERE department_id =any (
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
);
#注意：in 等价于 =any 、 not in 等价于 <>all、<any 等价于 < max()、<all 等价于 <min()、>any 等价于 > min()、>all 等价于 >max()
案例2：返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary
#①查询job_id为‘IT_PROG’部门任一工资
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
#②查询员工号、姓名、job_id 以及salary，salary<(①)的任意一个
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary <ANY(
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
# 等价于
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary < (
SELECT max(salary)
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
案例3：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工 的员工号、姓名、job_id 以及salary
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ALL(
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
# 等价于
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary < (
SELECT min(salary)
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
#3、行子查询(结果集为 一行多列 或者 多行多列)
案例：查询员工编号最小并且工资最高的员工信息
①查询最小的员工编号
SELECT MIN(employee_id)
FROM employees
②查询最高工资
SELECT MAX(salary)
FROM employees
③查询员工信息
SELECT *
FROM employees
WHERE employee_id=(
SELECT MIN(employee_id)
FROM employees
)AND salary=(
SELECT MAX(salary)
FROM employees
);
# 采用行子查询：
SELECT *
FROM employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees
);
exists后面（相关子查询）：支持表子查询
语法：
exists(完整的查询语句)
结果：1或0
#案例1：查询有员工的部门名
#in
SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
SELECT department_id
FROM employees
);
#exists
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.`department_id`=e.`department_id`
);
#案例2：查询没有女朋友的男神信息
#in
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN(
SELECT boyfriend_id
FROM beauty
);
#exists
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
SELECT boyfriend_id
FROM beauty b
WHERE bo.`id`=b.`boyfriend_id`
);
* 按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）
进阶八：分页查询
1、 应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
2、 语法：
select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit 【offset,】size;
* offset要显示条目的起始索引（起始索引从0开始）
* size 要显示的条目个数
3、特点：
①limit语句放在查询语句的最后
②公式： limit (page-1)*size,size; 【要显示的页数 page，每页的条目数size】
select 查询列表
from 表
limit (page-1)*size,size;
#案例1：查询前五条员工信息
SELECT * FROM employees LIMIT 0,5;
SELECT * FROM employees LIMIT 5;
#案例2：查询第11条——第25条
SELECT * FROM employees LIMIT 10,15;
#案例3：有奖金的员工信息，并且工资较高的前10名显示出来
SELECT * FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10 ;
进阶九：联合查询
1、union 联合 合并：将多条查询语句的结果合并成一个结果
2、语法：
查询语句1
union
查询语句2
union
...
3、应用场景：
要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时
4、特点：
① 、要求多条查询语句的查询列数是一致的！
② 、要求多条查询语句的查询的每一列的类型和顺序最好一致
③ 、union关键字默认去重，如果使用union all 可以包含重复项
#引入的案例：查询部门编号>90或邮箱包含a的员工信息
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;;
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;
#案例：查询中国用户中男性的信息以及外国用户中年男性的用户信息
SELECT id,cname FROM t_ca WHERE csex='男'
UNION ALL
SELECT t_id,tname FROM t_ua WHERE tGender='male';
DML语言的学习
DML (数据操作语言)：
插入：insert
修改：update
删除：delete
一、插入语句
方式一：经典的插入
1、 语法：
insert into 表名(列名,...) values(值1,...);
2、特点：
①、字段类型和值类型必须一致或兼容，而且要一一对应
②、可以为空的字段，可以不用插入值，或用null填充；不可以为空的字段，必须插入值
③、列的顺序可以调换，但必须一一对应
④、字段个数和值的个数必须一致
⑤、列名可以省略，但默认所有列名的顺序和表中的存储顺序一致
方式二：
1、 语法：
insert into 表名 set 列名=值,列名=值,... ;
两种方式大pk
#1、方式一支持插入多行,方式二不支持
INSERT INTO beauty
VALUES(23,'唐艺昕1','女','1990-4-23','1898888888',NULL,2),
(24,'唐艺昕2','女','1990-4-23','1898888888',NULL,2),
(25,'唐艺昕3','女','1990-4-23','1898888888',NULL,2);
#2、方式一支持子查询，方式二不支持
INSERT INTO beauty(id,NAME,phone)
SELECT 26,'宋茜','11809866';
或
INSERT INTO beauty(id,NAME,phone)
SELECT id,boyname,'1234567'
FROM boys WHERE id<3;
二、修改语句
1、修改单表的记录
语法：
update 表名
set 列=新值,列=新值,...
where 筛选条件;
2、修改多表的记录【补充】
sql92语法：
update 表1 别名,表2 别名
set 列=值,...
where 连接条件
and 筛选条件;
sql99语法：
update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列=值,...
where 筛选条件;
#案例2：修改没有男朋友的女神的男朋友编号都为2号
UPDATE boys bo
RIGHT JOIN beauty b ON bo.`id`=b.`boyfriend_id`
SET b.`boyfriend_id`=2
WHERE bo.`id` IS NULL;
三、删除语句
方式一：delete
语法：
1、单表的删除【】
delete from 表名 where 筛选条件 【limit 条目数】
2、多表的删除【补充】
sql92语法：
delete 表1的别名,表2的别名
from 表1 别名,表2 别名
where 连接条件
and 筛选条件;
sql99语法：
delete 表1的别名,表2的别名【要删除哪个表，就写哪个表的别名】
from 表1 别名
inner|left|right join 表2 别名 on 连接条件
where 筛选条件;
#案例：删除张无忌的女朋友的信息
DELETE b
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id` = bo.`id`
WHERE bo.`boyName`='张无忌';
#案例：删除黄晓明的信息以及他女朋友的信息
DELETE b,bo
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`='黄晓明';
方式二：truncate
语法：
truncate table 表名; 【不可以加where 条件】
* delete pk truncate【面试题】
1.delete 可以加筛选[where] 条件，truncate不能加where 条件
2.truncate删除，效率高一丢丢
3.假如要删除的表中有自增长列，
如果用delete删除后，再插入数据，自增长列的值从断点开始，
而truncate删除后，再插入数据，自增长列的值从1开始。
4.truncate删除没有返回值，delete删除有返回值,返回受影响的行数
5.truncate删除不能回滚，delete删除可以回滚.
DDL语言的学习
DDL(数据定义语言)：用于库和表的管理
* 库的管理：创建(create)、修改(alter)、删除(drop)
* 表的管理：创建(create)、修改(alter)、删除(drop)
一、库的管理
1、库的创建
(1)、第一种写法：
create [if not exists] database 数据库名;
(2)、第二种写法：
create [if not exists] database 数据库名 default character set 编码集;
(3)、第三种写法：
create [if not exists] database 数据库名 default character set 编码集 COLLATE 校验集;
例如：
create database mysql1;
create database mysql2 character set utf8; //指定字符集
create database mys1l3 character set utf8 collate utf8_general_ci; //指定字符集和校验集
2、库名的修改【以前可以使用，现在不可以，因为可能导致库中数据的丢失】
RENAME DATABASE books TO 新库名;
3、修改数据库(了解)：
(1)、修改数据库的编码与校验集：
alter database 数据库名 character set 字符编码集 collate 校验集;
(2)、切换数据库：
use 数据库名;
(3)、查看当前所使用的数据库：
select database();
4、库的删除
DROP DATABASE IF EXISTS 数据库名;
5、查看数据库：
show databases; //查看数据库服务器下的所有数据库
6、显示数据库的创建语句：
show create database 数据库名; //可以看到数据库创建时指定的相关信息
二、表的管理
1.表的创建
语法：
create table 表名(
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
...
列名 列的类型【(长度) 约束】
)engine=引擎名 default charset=字符集;
#案例：创建表user
create table user( #规范的创建SQL语句
id int(3) primary key auto_increment, #int(3)中的3和tinyint(1)中的1代表的宽度而不是长度
account varchar(22) not null default '',
password varchar(100) not null default '',
create_time datetime not null default current_timestamp,
update_time datetime not null default current_timestamp on update current_timestamp,
record_status tinyint(1) not null default 1
) engine=innoDB default charset=utf8; #创建引擎和默认编码
2.表的修改
语法：
alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;
①、修改列名
ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 列类型;
②、修改列的类型或约束
ALTER TABLE 表名 MODIFY COLUMN 列名 列类型;
③、添加新列
ALTER TABLE 表名 ADD COLUMN 列名 列类型 【first | after 字段名】; 【添加到表的前面第一个字段或者指定字段名的后面 】
④、删除列
ALTER TABLE 表名 DROP COLUMN 列名 ;
⑤、修改表名
ALTER TABLE 表名 RENAME 【TO】 新表名;
⑥、修改表的字符集
ALTER TABLE 表名 CONVERT TO CHARACTER SET 字符集 COLLATE 校验集;
⑦、修改字段的字符集
ALTER TABLE 表名 CHANGE 字段名 字段名 该字段原来的数据类型 CHARACTER SET 字符集 COLLATE 校验集;
3.表的删除
语法：
DROP TABLE 【IF EXISTS 】 表名;
#通用的写法：
DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;
DROP TABLE IF EXISTS 旧表名;
CREATE TABLE 表名();
4.表的复制
①、仅仅复制表的结构
CREATE TABLE 要创建的表名 LIKE 已存在的表名;
②、复制表的结构+数据
CREATE TABLE 要创建的表名 SELECT * FROM 已存在的表名;
③、只复制部分数据
CREATE TABLE 要创建的表名
SELECT 要复制的字段 FROM 已存在的表名 WHERE 筛选条件;
④、仅仅复制某些字段，不要数据
CREATE TABLE 要创建的表名
SELECT 要复制的字段 FROM 已存在的表名 WHERE 筛选条件; 【如果只想复制某些字段而不想要数据，则筛选条件可以随便写一个不成立的即可，比如 0 或者 1=2 等】
三、MySQL 创建用户并授权
1、创建用户:
命令:CREATE USER 'username'@'host' IDENTIFIED BY 'password';
说明:username - 你将创建的用户名,
host - 指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost, 如果想让该用户可以从任意远程主机登陆,可以使用通配符%.
password - 该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器.
例子: CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '';
CREATE USER 'pig'@'%';
2、授权:
命令:GRANT privileges ON databasename.tablename TO 'username'@'host'
说明: privileges - 用户的操作权限,如SELECT , INSERT , UPDATE 等.如果要授予所的权限则使用ALL.;
databasename - 数据库名,
tablename-表名,如果要授予该用户对所有数据库和表的相应操作权限则可用*表示, 如*.*.
例子: GRANT SELECT, INSERT ON test.user TO 'pig'@'%';
GRANT ALL ON *.* TO 'pig'@'%';
【注意:】用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令:
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
* 授权的数据库不要用mysql自带的数据库，否则不起作用。
3、设置与更改用户密码
命令:SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');
如果是当前登陆用户用SET PASSWORD = PASSWORD("newpassword");
例子: SET PASSWORD FOR 'pig'@'%' = PASSWORD("123456");
4、撤销用户权限
命令: REVOKE privilege ON databasename.tablename FROM 'username'@'host';
例子: REVOKE SELECT ON *.* FROM 'pig'@'%';
【注意】: 假如你在给用户'pig'@'%'授权的时候是这样的(或类似的):
GRANT SELECT ON test.user TO 'pig'@'%', 则在使用REVOKE SELECT ON *.* FROM 'pig'@'%';命令并不能撤销该用户对test数据库中user表的SELECT 操作.
相反,如果授权使用的是GRANT SELECT ON *.* TO 'pig'@'%';则REVOKE SELECT ON test.user FROM 'pig'@'%';命令也不能撤销该用户对test数据库中user表的Select 权限.
具体信息可以用命令SHOW GRANTS FOR 'pig'@'%'; 查看.
5、删除用户
命令: DROP USER 'username'@'host';
MySQL中支持emoji表情的存储
正常使用uft-8方式存储是不能存储emoji表情文字的，主要原因是utf8编码的一个字符最多3个字节，但是一个emoji表情为4个字节，所以utf8不支持存储emoji表情。
但是utf8的超集utf8mb4一个字符最多能有4字节，所以能支持emoji表情的存储。下面说说具体操作步骤：
1、需要你的mysql数据库版本在5.5以上；
2、更改你的数据库，表，以及需要存储emoji列的编码方式为utf8mb4 ；
# 对每一个数据库:
ALTER DATABASE 数据库名字 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
# 对每一个表:
ALTER TABLE 表名字 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# 对存储emoji表情的字段:
ALTER TABLE 表名字 CHANGE 字段名字 字段名字 该字段的类型 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
【注意】：utf8mb4完全向后兼容utf8，无乱码或其他数据丢失的形式出现。理论上是可以放心修改，如果不放心修改，可以备份数据
3、修改my.ini数据库配置【Linux系统中的配置文件为my.cnf，Winows中的配置文件为my.ini】
[client]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect=’SET NAMES utf8mb4'
[mysql]
default-character-set = utf8mb4
修改完成成，wq保存并退出
4、重启mysql
如果使用 service mysql restart,最后发现这条命令并不管用，重启没有成功，导致后面查看字符集的时候，并没有达到想要的字符集的状态时，可以采用下面的方法才可以正确的重启mysql：
①、停止msql的运行
通过/etc/init.d/mysql执行stop命令
②、启动mysql
通过/etc/init.d/mysql执行start命令
5、检查字符集：
进入mysql中，用
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' ；
OR
Variable_name LIKE 'collation%'; 命令查看字符集的情况
6、确认mysql驱动、连接串：
①、确保mysql connection版本高于5.1.13，否则仍然不能使用utf8mb4
②、确认服务器端的db配置文件中的字符集是否修改过来？
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://主机名:端口/数据库?useUnicode=true&characterEncoding=utf8mb4&autoReconnect=true&rewriteBatchedStatements=TRUE
7、带有emoji表情的sql文件的导入与导出（主要为windows中的导出）
①、导出
在进行把后缀名为.sql的文件，且文件数据中包含emoji表情的数据，进行备份导出的时候，此时不要使用第三方软件进行导出，而是使用命令行的形式执行该导出动作，
其主要原因是使用第三方导出该文件时，由于其默认的导出的编码格式为utf-8,该编码格式最多只支持3个字节，而一个emoji表情有4个字节，这将导致emoji表情的数据变成乱码。
所以在本地导出的时候具体操作步骤如下：
* 打开cmd，先找到mysqldump这个执行文件所在的路径【即mysql安装的bin目录】；
* 在路径后输入mysqldump –default-character-set-utf8mb4 –u 用户名 –p 数据库名 > 导出该文件的物理路径，按“Enter”，即可完成导出功能，在导出的物理路径中即可找到已被导出的该文件。
②、导入
* 执行 mysql -u 用户名 -h 主机名 -P 端口号 -p密码 进入mysql数据库；
* 创建要导入sql的数据库 create database 数据库名；
* use 上一步创建号的数据库名；
* source 要导入的sql文件全路径
常见的数据类型
一、数值型：
* 整型：
①、分类：
类型 tinyint、smallint、mediumint、int/integer、bigint
字节数 1 2 3 4 8
②、特点：
1、如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
2、如果插入的数值超出了整型的范围,会报out of range异常，并且插入的是临界值
3、如果不设置长度，会有默认的长度，长度代表了显示结果中该列的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用才会使用0在左边填充
【对于整型，它的范围是由前面类型决定的】
4、如果使用了zerofill 关键字，则默认就是无符号的，所以无需添加unsigned 关键字
* 小数：
1、分类
①、 定点数
dec(M，D)
decimal(M,D)
②、浮点数
float(M,D)
double(M,D)
2、特点：
①、M：整数部位+小数部位 一共有M位
D：小数部位的位数
如果超过范围，则插入临界值
②、M和D都可以省略
如果是decimal，则M默认为10，D默认为0
如果是float和double，则会根据插入的数值的精度来决定精度
③、定点型的精确度较高，如果要求插入数值的精度较高，如货币运算等则考虑使用
#原则：
所选择的类型越简单越好，能保存数值的类型越小越好
二、字符型：
1、分类：
较短的文本：char、varchar
其它还有：
binary和varbinary用于保存较短的二进制
enum用于保存枚举
set用于保存集合
较长的文本：text、blob（较长的二进制数据）
举例：
①、枚举：
CREATE TABLE tab_char(
c1 ENUM('a','b','c')
);
INSERT INTO tab_char VALUES('a'); 正确
INSERT INTO tab_char VALUES('b'); 正确
INSERT INTO tab_char VALUES('c'); 正确
INSERT INTO tab_char VALUES('m'); 为空【因为不包含在枚举中，所以为空】
INSERT INTO tab_char VALUES('A'); 正确 【插入的为a，不区分大小写】
②、集合：
CREATE TABLE tab_set(
s1 SET('a','b','c','d')
);
INSERT INTO tab_set VALUES('a,A'); 显示 a 【唯一，不重复】
INSERT INTO tab_set VALUES('d,A,B'); 不区分大小写，显示 a,b,d 【排序】
INSERT INTO tab_set VALUES('g'); 集合中没有，显示空
INSERT INTO tab_set VALUES('b,c,g'); 显示 b,c 【集合中有则显示，没有则不显示】
2、特点：
写法 M的意思 特点 空间的耗费 效率
char char(M) 最大的字符数，可以省略，默认为1 固定长度的字符 比较耗费 高
varchar varchar(M) 最大的字符数，不可以省略 可变长度的字符 比较节省 低
3、说明：char(M) 和 varchar(M) 中的 M 表示可以存储的最大字符数，如果超过了这个数，则会从左边进行截取，截取长度为M。【因为表示的是字符数，所以一个汉字，一个字母都算一个字符】
三、日期型：
1、分类：
date(4个字节)：只保存日期
time (3个字节)：只保存时间
year(1个字节)：只保存年
datetime：保存日期+时间
timestamp：保存日期+时间
2、特点：
字节 范围 时区等的影响
datetime 8 1000——9999 不受
timestamp 4 1970-2038 受
示例：
查看时区：
SHOW VARIABLES LIKE 'time_zone';
设置时区：
SET time_zone='+9:00';
常见约束
一、含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性
二、分类：六大约束
1、NOT NULL：非空，用于保证该字段的值不能为空
比如姓名、学号等
2、DEFAULT: 默认，用于保证该字段有默认值
比如性别
3、PRIMARY KEY: 主键，用于保证该字段的值具有唯一性，并且非空
比如学号、员工编号等
4、UNIQUE: 唯一，用于保证该字段的值具有唯一性，可以为空
比如座位号
5、CHECK: 检查约束【mysql中不支持，即添加上不报错，但是没有效果】
比如年龄、性别
6、FOREIGN KEY: 外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值 【在从表添加外键约束，用于引用主表中某列的值】
比如学生表的专业编号，员工表的部门编号，员工表的工种编号
三、添加约束的时机：
1.创建表时添加约束
2.修改表时添加约束
四、约束的添加分类：
1、列级约束：
六大约束语法上都支持，但外键约束和检查约束不支持，即添加上也没有效果
①、语法：
直接在字段名和类型后面追加 约束类型即可。列级约束可以在一个字段后面添加多个，中间用空格隔开，没有顺序要求。
②、只支持：默认、非空、主键、唯一
【注意：语法上六大约束都支持，但是外键和检查不支持，即添加上没有效果】
CREATE TABLE stuinfo(
id INT PRIMARY KEY, #主键
stuName VARCHAR(20) NOT NULL, #非空
gender CHAR(1) CHECK(gender='男' OR gender ='女'), #检查
seat INT UNIQUE, #唯一
age INT DEFAULT 18, #默认约束
majorId INT REFERENCES major(id) #外键
);
CREATE TABLE major(
id INT PRIMARY KEY,
majorName VARCHAR(20)
);
#查看stuinfo中的所有索引，包括主键、外键、唯一以及自己创建的索引
SHOW INDEX FROM stuinfo;
2、表级约束：
语法上不支持非空、默认，其他的都支持，但是检查语法支持，实际添加上也没有作用
①、语法：在各个字段的最下面添加 【constraint 约束名】 约束类型(字段名)
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT,
CONSTRAINT pk PRIMARY KEY(id), #主键
# 【1、对于主键，就算起了名字pk，也是没有效果的，因为mysql默认主键的名字为primary
# 2、列级约束是没有办法给约束起名字的，只有表级约束才可以】
CONSTRAINT uq UNIQUE(seat), #唯一键
CONSTRAINT ck CHECK(gender ='男' OR gender = '女'), #检查
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) #外键
);
#通用的写法：
CREATE TABLE IF NOT EXISTS stuinfo(
id INT PRIMARY KEY, #主键
stuname VARCHAR(20) NOT NULL, #非空
sex CHAR(1),
age INT DEFAULT 18, #默认约束
seat INT UNIQUE, #唯一键
majorid INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) #外键
);
#【注意】
支持类型 可以起约束名
列级约束 除了外键 不可以
表级约束 除了非空和默认 可以
五、主键和唯一的大对比：
保证唯一性 是否允许为空 一个表中可以有多少个 是否允许组合
主键 √ × 至多有1个 √，但不推荐
唯一 √ √ 可以有多个 √，但不推荐
【注意】
①、因为主键和唯一是保证唯一性的，所以设置了主键和唯一约束的字段值只能有一个，不能重复，包括null值，也只允许出现一个；
②、主键和唯一约束都允许组合使用，意思是可以设置多个字段组合为主键，如果设置了之后，只有设置的多个字段同时值相同才会看作是重复，才会起作用，单个字段是可以重复的，但是不可以为空
CONSTRAINT pk PRIMARY KEY(id, stuname...), #主键
CONSTRAINT uq UNIQUE(seat, seat2,....), #唯一键
六、外键：
1、要求在从表设置外键关系
2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
3、主表的关联列必须是一个key（一般是主键或唯一）
4、插入数据时，先插入主表，再插入从表
删除数据时，先删除从表，再删除主表
5、一个字段可以设置多个约束类型，中间用空格隔开
例如：stuname VARCHAR(20) NOT NULL UNIQUE ... , #非空 #唯一 ...
# 可以通过以下两种方式删除主表的记录：
方式一：级联删除
ALTER TABLE 表名 ADD constraint 外键约束名 foreign key(从表字段) references 主表(字段) ON DELETE CASCADE;
方式二：级联置空
ALTER TABLE 表名 ADD constraint 外键约束名 foreign key(从表字段) references 主表(字段) ON DELETE set null;
七、修改表时添加约束
1、添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;
2、添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;
举例：
#1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
#2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#3.添加主键
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
#4.添加唯一
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#②表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);
#5.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);
八、修改表时删除约束
#1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
#2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;
#3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;
#4.删除唯一
ALTER TABLE stuinfo DROP INDEX seat;
#5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
位置 支持的约束类型 (检查约束除外) 是否可以起约束名
列级约束： 列的后面 语法都支持，但外键没有效果 不可以
表级约束： 所有列的下面 默认和非空不支持，其他支持 可以（主键没有效果）
标识列 [AUTO_INCREMENT]
1、标识列：又称为自增长列, 可以不用手动的插入值, 系统提供默认的序列值, 初始默认值为1, 即从1开始自增
2、特点：
①、标识列必须和主键搭配吗？ 不一定，但要求是一个key
②、一个表可以有几个标识列？ 至多一个！
③、标识列的类型只能是数值型（整型、浮点型）
④、标识列可以通过 SET auto_increment_increment=3;设置步长
MySQL 不可以通过： SET auto_increment_offset=3; 修改起始值，即使设置了，也不会有效果，但是可以通过 手动插入值，设置起始值【第一条数据可以手动插入你想要的起始值，后面无需再添加，就会自动从你插入的起始值自增】
#查看自增长列相关的设置【有起始值和步长，默认都是1】
SHOW VARIABLES LIKE '%auto_increment%';
3、修改表时设置自增长列
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 约束 AUTO_INCREMENT;
删除自增长列
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 ;
事务
1、TCL语言：Transaction Control Language 事务控制语言
2、事务：一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。
案例：转账
3、事务的特性：ACID
原子性：一个事务不可再分割，要么都执行要么都不执行
一致性：一个事务执行会使数据从一个一致状态切换到另外一个一致状态
隔离性：一个事务的执行不受其他事务的干扰
持久性：一个事务一旦提交，则会永久的改变数据库的数据.
4、事务的创建
①、隐式事务：事务没有明显的开启和结束的标记
比如insert、update、delete语句
②、显式事务：事务具有明显的开启和结束的标记
前提：必须先设置自动提交功能为禁用 set autocommit=0;
查看变量: SHOW VARIABLES LIKE 'autocommit'; MySQL默认为ON，开启自动提交(即隐式事务)
5、使用事务的步骤：
步骤1：开启事务
set autocommit=0;
start transaction;可选的
步骤2：编写事务中的sql语句(select insert update delete)
语句1;
语句2;
...
步骤3：结束事务
commit;提交事务
rollback;回滚事务
6、事务的隔离级别：
对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:
脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段之后, 若 T2 回滚, T1读取的内容就是临时且无效的.
不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段. 之后, T1再次读取同一个字段, 值就不同了.
幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行
脏读 不可重复读 幻读
read uncommitted： √ √ √
read committed： × √ √
repeatable read： × × √
serializable： × × ×
①、 Mysql 支持 4 种事务隔离级别.mysql中默认的隔离级别是 repeatable read
Oracle 支持2 种事务隔离级别：READ COMMITED, SERIALIZABLE。默认的隔离级别是 read committed
②、查看隔离级别
select @@tx_isolation;
③、设置当前MYSQL的连接 / 数据库系统的全局 隔离级别
set session|global transaction isolation level 隔离级别;
7、savepoint 节点名：设置保存点， 只能搭配rollback 使用
示例：只删除了id为25的，id为28的回滚了，没有删除成功
SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a; #设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a; #回滚到保存点
视图
一、含义：mysql5.1版本出现的新特性，一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，
只保存了sql逻辑，不保存查询结果
* 应用场景：
– 多个地方用到同样的查询结果
– 该查询结果使用的sql语句较复杂
* 使用视图的好处：
• 重用sql语句
• 简化复杂的sql操作，不必知道它的查询细节
• 保护数据，提高安全性
二、视图与表的对比：
创建语法的关键字 是否实际占用物理空间 使用
视图 create view 只是保存了sql逻辑 增删改查，只是一般不能增删改，主要用于查
表 create table 保存了数据 增删改查
三、创建视图
1、语法：
create view 视图名
as
查询语句;
案例：#1、查询各部门的平均工资级别
#①创建视图查看每个部门的平均工资
CREATE VIEW myv2
AS
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id;
#②使用
SELECT myv2.`ag`,g.grade_level
FROM myv2
JOIN job_grades g
ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
#2、查询平均工资最低的部门名和工资
CREATE VIEW myv3
AS
SELECT * FROM myv2 ORDER BY ag LIMIT 1;
SELECT d.*,m.ag
FROM myv3 m
JOIN departments d
ON m.`department_id`=d.`department_id`;
四、修改视图
方式一：
create or replace view 视图名
as
查询语句;
示例： CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;
方式二：
alter view 视图名
as
查询语句;
示例： ALTER VIEW myv3
AS
SELECT * FROM employees;
五、删除视图
语法：drop view 视图名,视图名,...;
DROP VIEW emp_v1,emp_v2,myv3;
六、查看视图结构
DESC myv3;
SHOW CREATE VIEW myv3; #可以查看更加详细的信息，如创建的语句，字符集等
七、视图的更新
1.插入
INSERT INTO myv1 VALUES('张飞','zf@qq.com');
2.修改
UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';
3.删除
DELETE FROM myv1 WHERE last_name = '张无忌';
#具备以下特点的视图不允许更新
①包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
②常量视图
CREATE OR REPLACE VIEW myv2
AS
SELECT 'john' NAME;
#查看
SELECT * FROM myv2;
#更新
UPDATE myv2 SET NAME='lucy'; #不能更新
③Select中包含子查询
④join
⑤from一个不能更新的视图
CREATE OR REPLACE VIEW myv5
AS
SELECT * FROM myv3; #myv3视图不能更新
⑥where子句的子查询引用了from子句中的表
CREATE OR REPLACE VIEW myv6
AS
SELECT last_name,email,salary
FROM employees
WHERE employee_id IN(
SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL
);
变量
一、分类：
* 系统变量：
全局变量
会话变量
* 自定义变量：
用户变量
局部变量
二、系统变量
说明：变量由系统定义，不是用户定义，属于服务器层面
注意：全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，默认会话级别
①、使用步骤：
1、查看所有系统变量
show global|【session】variables;
2、查看满足条件的部分系统变量
show global|【session】 variables like '%char%';
3、查看指定的系统变量的值
select @@global.系统变量名; --->全局的
select @@系统变量名; --->会话的
4、为某个系统变量赋值
方式一：
set global|【session】 系统变量名 = 值;
方式二：
set @@global|session.系统变量名 = 值;
、全局变量
作用域：针对于所有会话（连接）有效，但不能跨重启
①、查看所有全局变量
SHOW GLOBAL VARIABLES;
②、查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';
③、查看指定的系统变量的值
SELECT @@global.autocommit;
④、为某个系统变量赋值
SET @@global.autocommit=0; 或 SET GLOBAL autocommit=0;
、会话变量
作用域：针对于当前会话（连接）有效
①、查看所有会话变量
SHOW SESSION VARIABLES;
②、查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%char%';
③、查看指定的会话变量的值
SELECT @@autocommit; 或 SELECT @@session.tx_isolation;
④、为某个会话变量赋值
SET @@session.tx_isolation='read-uncommitted'; 或 SET SESSION tx_isolation='read-committed';
三、自定义变量
说明：变量由用户自定义，而不是系统提供的
①、使用步骤：
1、声明
2、赋值
3、使用（查看、比较、运算等）
、用户变量
1、作用域：针对于当前会话（连接）有效，作用域同于会话变量，可以放在任何地方【begin end里面或者外面都可以】。
2、使用步骤：【赋值操作符： = 或 :=】
①声明并初始化
SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;
②赋值（更新变量的值）
#方式一：通过set 或 select
SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;
#方式二：通过select into
SELECT 字段 INTO @变量名
FROM 表;
③使用（查看变量的值）
SELECT @变量名;
、局部变量
1、作用域：仅仅在定义它的begin end块中有效， 必须应用在 begin end中的第一句话
2、使用步骤：
①声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 【DEFAULT 值】;
②赋值（更新变量的值）
#方式一：
SET 局部变量名=值;
SET 局部变量名:=值;
SELECT @局部变量名:=值;
#方式二：
SELECT 字段 INTO 局部变量名
FROM 表;
③使用（查看变量的值）
SELECT 局部变量名;
#案例：声明两个变量，求和并打印
#用户变量
SET @m=1;
SET @n=1;
SET @sum=@m+@n;
SELECT @sum;
#局部变量
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 1;
DECLARE SUM INT;
SET SUM=m+n;
SELECT SUM;
、 用户变量和局部变量的对比
作用域 定义位置 语法
用户变量 当前会话 会话的任何地方 必须加@符号，不用指定类型
局部变量 定义它的BEGIN END中 BEGIN END中的第一句话 一般不用加@,需要指定类型
存储过程和函数
一、存储过程和函数：类似于java中的方法
二、好处：
1、提高代码的重用性
2、简化操作
、存储过程
一、含义：一组预先编译好的SQL语句的集合，理解成批处理语句
二、好处：
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
三、创建语法
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体（一组合法的SQL语句）
END
#注意：
1、参数列表包含三部分：参数模式 参数名 参数类型
举例：
in stuname varchar(20)
参数模式：
in：该参数可以作为输入，也就是该参数需要调用方传入值
out：该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值
2、如果存储过程体仅仅只有一句话，begin end可以省略
存储过程体中的每条sql语句的结尾要求必须加分号。
存储过程的结尾可以使用 delimiter 重新设置
语法：
delimiter 结束标记
案例：
delimiter $
四、调用语法
CALL 存储过程名(实参列表);
#--------------------------------案例演示-----------------------------------
* 1、空参列表
#案例：插入到admin表中五条记录
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username,`password`)
VALUES('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000');
END $
#调用
CALL myp1()$
* 2、创建带in模式参数的存储过程
#案例1：创建存储过程实现 根据女神名，查询对应的男神信息
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
WHERE b.name=beautyName;
END $
#调用
CALL myp2('柳岩')$
#案例2 ：创建存储过程实现，用户是否登录成功
CREATE PROCEDURE myp4(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
DECLARE result INT DEFAULT 0; #声明并初始化
SELECT COUNT(*) INTO result #赋值
FROM admin
WHERE admin.username = username
AND admin.password = PASSWORD;
SELECT IF(result>0,'成功','失败'); #使用
END $
#调用
CALL myp3('张飞','8888')$
* 3、创建out 模式参数的存储过程
#案例1：根据输入的女神名，返回对应的男神名
CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
SELECT bo.boyname INTO boyname
FROM boys bo
RIGHT JOIN
beauty b ON b.boyfriend_id = bo.id
WHERE b.name=beautyName ;
END $
#案例2：根据输入的女神名，返回对应的男神名和魅力值
CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT)
BEGIN
SELECT boys.boyname ,boys.usercp INTO boyname,usercp
FROM boys
RIGHT JOIN
beauty b ON b.boyfriend_id = boys.id
WHERE b.name=beautyName ;
END $
#调用
CALL myp7('小昭',@name,@cp)$
SELECT @name,@cp$
* 4、创建带inout模式参数的存储过程
#案例1：传入a和b两个值，最终a和b都翻倍并返回
CREATE PROCEDURE myp8(INOUT a INT ,INOUT b INT)
BEGIN
SET a=a*2;
SET b=b*2;
END $
#调用
SET @m=10$
SET @n=20$
CALL myp8(@m,@n)$
SELECT @m,@n$
、总结：
①、空参列表：调用方法 ---> call 存储过程名() 结束符
例如: CALL myp3()$
②、带in模式参数的存储过程：调用方法 ---> call 存储过程名(直接输入需要的参数) 结束符
例如: CALL myp3('张飞','8888')$
③、带out 模式参数的存储过程：调用方法 ---> call 存储过程名(自定义用户变量) 结束符
例如: CALL myp7('小昭',@name,@cp)$ ----> 调用
SELECT @name,@cp$ ---->查看
④、带inout模式参数的存储过程：调用方法 ---> call 存储过程名(定义用户变量并赋值) 结束符
例如: SET @m=10$ ----> 定义用户变量并赋值
SET @n=20$ ----> 定义用户变量并赋值
CALL myp8(@m,@n)$ ----> 调用
SELECT @m,@n$ ---->查看
五、删除存储过程
语法：drop procedure 存储过程名
示例： DROP PROCEDURE p1;
DROP PROCEDURE p2,p3; #这种写法错误，一次只可以删除一个存储过程
六、查看存储过程的信息
DESC myp2; #这种写法错误，不可以使用desc 查看
SHOW CREATE PROCEDURE myp2;
、函数
一、含义：一组预先编译好的SQL语句的集合，理解成批处理语句
二、好处：
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
三、区别：
存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数：有且仅有1 个返回，适合做处理数据后返回一个结果
四、创建语法
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END
#注意：
1.参数列表包含两部分：
参数名 参数类型
2.函数体：肯定会有return语句，如果没有会报错
如果return语句没有放在函数体的最后也不报错，但不建议
写法：return 值;
3.函数体中仅有一句话，则可以省略begin end
4.使用 delimiter语句设置结束标记
五、调用语法
SELECT 函数名(参数列表)
#------------------------------案例演示----------------------------
* 1、无参有返回
#案例：返回公司的员工个数
CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0; #定义局部变量
SELECT COUNT(*) INTO c #赋值
FROM employees;
RETURN c;
END $
SELECT myf1()$
* 2、有参有返回
#案例1：根据员工名，返回它的工资
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
SET @sal=0; #定义用户变量
SELECT salary INTO @sal #赋值
FROM employees
WHERE last_name = empName;
RETURN @sal;
END $
SELECT myf2('k_ing') $
#案例2：根据部门名，返回该部门的平均工资
CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
DECLARE sal DOUBLE ; #定义局部变量
SELECT AVG(salary) INTO sal
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name=deptName;
RETURN sal;
END $
SELECT myf3('IT')$
六、查看函数
SHOW CREATE FUNCTION myf3;
七、删除函数
DROP FUNCTION myf3;
#案例
#一、创建函数，实现传入两个float，返回二者之和
CREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
DECLARE SUM FLOAT DEFAULT 0;
SET SUM=num1+num2;
RETURN SUM;
END $
SELECT test_fun1(1,2)$
流程控制结构 【顺序、分支、循环】
流程控制结构
顺序结构：程序从上往下依次执行
分支结构：程序从两条或多条路径中选择一条去执行
循环结构：程序在满足一定条件的基础上，重复执行一段代码
一、分支结构
1、IF 函数
①、功能：实现简单的双分支
②、语法：
if(表达式1，表达式2，表达式3)
执行顺序：
如果表达式1成立，则if 函数返回表达式2的值，否则返回表达式3的值
③、应用：
任何地方(可以放在begin end 中，也可以放在begin end 外)
2.case结构
①、情况1：类似于Java中的switch语句，一般用于实现等值判断
语法：
如果作为表达式使用，则语法如下： 【注意：此情况下，可以放在begin end 中，也可以放在begin end 外】
case 变量|表达式|字段
when 要判断的值1 then 返回的值1
when 要判断的值2 then 返回的值2
...
else 要返回的值n
end;
如果作为独立的语句执行，则语法如下： 【注意：此情况下，只可以放在begin end 中】
case 变量|表达式|字段
when 要判断的值1 then 语句1;
when 要判断的值2 then 语句2;
...
else 要返回的语句n;
end case;
②、情况2：类似于Java中的多重if语句，一般用于实现区间判断
语法：
如果作为表达式使用，则语法如下： 【注意：此情况下，可以放在begin end 中，也可以放在begin end 外】
case
when 要判断的条件1 then 返回的值1
when 要判断的条件2 then 返回的值2
...
else 要返回的值n
end;
如果作为独立的语句执行，则语法如下： 【注意：此情况下，只可以放在begin end 中】
case
when 要判断的条件1 then 语句1;
when 要判断的条件2 then 语句2;
...
else 要返回的语句n;
end case;
特点：
①、可以作为表达式，嵌套在其它语句中使用，可以放在任何地方，begin end 中或begin end 的外面
可以作为独立的语句使用，只能放在begin end 中
②、如果when 中的值满足或条件成立，则执行对应的then 后面的语句，并且结束case
如果都不满足，则执行else 中的语句或值
③、else 可以省略，如果else 省略了，并且所有的when条件都不满足，则返回null
#案例：创建存储过程，根据传入的成绩，来显示等级，如果传入的成绩：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 THEN SELECT 'B';
WHEN score >= 60 THEN SELECT 'C';
ELSE SELECT 'D';
END CASE;
END $
CALL test_case(90)$
3.if结构
①、语法：
if 条件1 then 语句1;
elseif 条件2 then 语句2;
....
else 语句n; 【注意，else 语句可以省略】
end if;
②、功能：类似于多重if
③、应用场景：只能应用在begin end 中
#案例1：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D
CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
DECLARE ch CHAR DEFAULT 'A';
IF score>90 THEN SET ch='A';
ELSEIF score>80 THEN SET ch='B';
ELSEIF score>60 THEN SET ch='C';
ELSE SET ch='D';
END IF;
RETURN ch;
END $
SELECT test_if(87)$
#案例2：创建存储过程，如果工资<2000,则删除，如果5000>工资>2000,则涨工资1000，否则涨工资500
CREATE PROCEDURE test_if_pro(IN sal DOUBLE)
BEGIN
IF sal<2000 THEN DELETE FROM employees WHERE employees.salary=sal;
ELSEIF sal>=2000 AND sal<5000 THEN UPDATE employees SET salary=salary+1000 WHERE employees.`salary`=sal;
ELSE UPDATE employees SET salary=salary+500 WHERE employees.`salary`=sal;
END IF;
END $
CALL test_if_pro(2100)$
#案例1：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D
CREATE FUNCTION test_case(score FLOAT) RETURNS CHAR
BEGIN
DECLARE ch CHAR DEFAULT 'A';
CASE
WHEN score>90 THEN SET ch='A';
WHEN score>80 THEN SET ch='B';
WHEN score>60 THEN SET ch='C';
LSE SET ch='D';
END CASE;
RETURN ch;
END $
SELECT test_case(56)$
二、循环结构
分类：
while、loop、repeat
循环控制：
iterate类似于 continue，继续，结束本次循环，继续下一次
leave 类似于 break，跳出，结束当前所在的循环
1、while
①、语法：
【标签:】while 循环条件 do
循环体;
end while【 标签】;
②、特点：先判断后执行
③、位置：begin end 中
2、loop
①、 语法：
【标签:】loop
循环体;
end loop 【标签】;
②、特点：没有条件的死循环(可以模拟简单的死循环)
③、位置：begin end 中
3、repeat
①、 语法：
【标签：】repeat
循环体;
until 结束循环的条件
end repeat 【标签】;
②、特点：先执行后判断
③、位置：begin end 中
#1.没有添加循环控制语句
#案例：批量插入，根据次数插入到admin表中多条记录
DROP PROCEDURE pro_while1$
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=insertCount DO
INSERT INTO admin(username,`password`) VALUES(CONCAT('Rose',i),'666');
SET i=i+1;
END WHILE;
END $
CALL pro_while1(100)$
/*
int i=1;
while(i<=insertcount){
//插入
i++;
}
*/
#2.添加leave语句
#案例：批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i<=insertCount DO
INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
IF i>=20 THEN LEAVE a;
END IF;
SET i=i+1;
END WHILE a;
END $
CALL test_while1(100)$
#3.添加iterate语句
#案例：批量插入，根据次数插入到admin表中多条记录，只插入偶数次
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 0;
a:WHILE i<=insertCount DO
SET i=i+1;
IF MOD(i,2)!=0 THEN ITERATE a;
END IF;
INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
END WHILE a;
END $
CALL test_while1(100)$
/*
int i=0;
while(i<=insertCount){
i++;
if(i%2==0){
continue;
}
插入
}
*/
流程控制经典案例：
一、已知表stringcontent
其中字段：
id 自增长
content varchar(20)
向该表插入指定个数的，随机的字符串
DROP TABLE IF EXISTS stringcontent;
CREATE TABLE stringcontent(
id INT PRIMARY KEY AUTO_INCREMENT,
content VARCHAR(20)
);
DELIMITER $
CREATE PROCEDURE test_randstr_insert(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
DECLARE startIndex INT; #代表初始索引
DECLARE len INT; #代表截取的字符长度
WHILE i<=insertcount DO
SET startIndex=FLOOR(RAND()*26+1); #代表初始索引，随机范围1-26
SET len=FLOOR(RAND()*(20-startIndex+1)+1); #代表截取长度，随机范围1-（26-startIndex+1） 【 content 要求长度为20，所以26取不到，因此最终结果：1 -（20-startIndex+1）】
INSERT INTO stringcontent(content) VALUES(SUBSTR(str,startIndex,len));
SET i=i+1;
END WHILE;
END $
CALL test_randstr_insert(10)$
网友评论