MySQL基础
00概述
0-1MySQL初步理解
0-2数据库存储数据的特点
0-3MySQL服务的停止与启动
0-4MySQL服务端的登陆与退出
0-5查看MySQL服务端的版本
0-6MySQL的常见指令
0-7MySQL语法规范
01DQL(Data Query Language)数据查询语言【具体看代码部分】
1-1基础查询
代码如下:
/*
语法:select 查询列表 from 表名;
特点:
1、查询列表可以是:表中的字段、常量值、表达式、函数
2、查询的结果是一个虚拟的表格
*/
USE myemployees;
#1、查询表中的单个字段
SELECT employee_id FROM employees;
#查询表中的多个字段
SELECT last_name,first_name FROM employees;
#查询表中的所有字段
SELECT * FROM employees;
#2、查询常量值
SELECT 'abcdefghijk',100;
#3、查询表达式
SELECT 100%99;
#4、查询函数
SELECT VERSION();
/*
5、起别名:
①便于理解
②如果要查询的字段有重名的情况,使用别名可以区分开来
*/
#方式一:使用as
SELECT last_name AS 姓,first_name AS 名 FROM employees;
#方式二:使用空格
SELECT last_name 姓,first_name 名 FROM employees;
SELECT 100*50 AS "out put";
#6、去重
SELECT DISTINCT department_id FROM employees;
/*
7、+ 的作用(mysql中的+,只有一个功能:作运算符)
①只要有一方为字符型,那么试图将字符型转换为数值型,再做运算
转换成功:继续做加法运算。 select '90'+10;结果是100
转换失败:将字符值默认为0,再作运算。select 10+'jhon'+90;结果是100
②只要有一方为null,做运算后的结果是null。
select 10+90+null;结果是null
*/
#8、拼接:应用函数concat()
SELECT CONCAT(last_name,first_name) AS '姓名' FROM employees;
#9、ifnull()函数
SELECT IFNULL(commission_pct,0) FROM employees;#如果commission_pct是null,就将其更换为0。
SELECT CONCAT(`first_name`,`last_name`,`job_id`,IFNULL(commission_pct,0)) AS "out put" FROM employees;
1-2条件查询
代码如下:
/*
语法:
select
查询列表③
from
表名①
where
筛选条件;②
*/
/*
分类:
一、按条件表达式筛选
简单的条件运算符:> ,< ,<>(!=) ,= ,>= ,<=
二、模糊查询(复杂的条件运算符)
like
between and
in
is null/is not null
三、按逻辑表达式筛选
逻辑运算符(用于连接条件表达式):and(&&) ,or(||) ,not(!)
*/
#【一、按条件表达式查询:(查询部门编号不等于80的员工信息)】
SELECT * FROM employees WHERE department_id<>80;
#【二、按逻辑表达式筛选:(查询部门编号不属于90-110的员工信息)】
SELECT * FROM employees WHERE NOT(90<=department_id && department_id<=110);
/*
【三、模糊查询】
通配符:
%:表示任意多个字符
_:表示任意一个字符
*/
#▲▲1、like
#查询员工姓名中包含字符a的员工信息
SELECT * FROM employees WHERE first_name LIKE '%a%' AND last_name LIKE '%a%';
#转义字符的使用(查询员工姓中第二个字母是下划线_的员工信息)
SELECT * FROM employees WHERE last_name LIKE '_\_%';
SELECT * FROM employees WHERE last_name LIKE '_*_%' ESCAPE '*';
#▲▲2、between and的使用(①包含临界值②between and涉及的两个值不可以更换顺序[因为相当于>=前者且<=后者])
#查询员工编号在100-120之间的员工信息
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;
#▲▲3、in关键字的使用(判断某字段的值是否属于列表中的某一项)
#等价于job_id='AD_PRES' or job_id='AD_VP'所以in列表中不可以用通配符
SELECT * FROM employees WHERE job_id IN ('AD_PRES','AD_VP');
#▲▲4、is null 和 is not null的使用(判断某一字段的值是否为null不可以用=和<>)
#①安全等于<=>既可以判断null值又可以判断数值,但是可读性差
#查询奖金率为null的员工信息
SELECT * FROM employees WHERE commission_pct IS NULL;
SELECT * FROM employees WHERE commission_pct <=> NULL;
#▲▲5、isnull()函数(如果为null返回1,非null返回0)
SELECT commission_pct,ISNULL(commission_pct) FROM employees;
1-3排序查询
代码如下:
/*
语法:
select
查询列表
from
表
[where 筛选条件]
order by 排序列表 [asc(升序)/desc(降序)]
特点:
①如果不写asc或者desc默认是asc
②可以按照单个字段、多个字段、函数、表达式、别名排序
③order by子句一般是放在查询语句的最后面,limit子句除外
*/
#【按照单个字段排列】查询员工信息,按照工资从高到低顺序排列
SELECT * FROM employees ORDER BY salary DESC;
#【按照表达式排列】查询员工信息,按照年薪降序排列
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
#【按照别名排列】order by后面的年薪不可以用引号引起来
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees ORDER BY 年薪 DESC;
#【按照函数排列】按照姓氏长度显示员工信息
SELECT *,LENGTH(last_name) FROM employees ORDER BY LENGTH(last_name) DESC;
#【按照多个字段排列】查询员工信息,先【主】按照工资排序(降序),再【次】按照员工编号排序(升序)
SELECT * FROM employees ORDER BY salary DESC,employee_id ASC;
#查询员工信息,先【主】按照员工编号排序(升序),再【次】按照工资排序(降序)
SELECT * FROM employees ORDER BY employee_id ASC,salary DESC;
1-4常见函数
1-4-1单行函数之字符函数
1-4-2单行函数之数学函数
1-4-3单行函数之日期函数
1-4-4单行函数之其它函数
1-4-5单行函数之流程控制函数
1-4-6分组函数(聚合函数、统计函数、组函数)
1-4-7代码展示
/*
常见函数:
调用:select 函数名(实参列表)[from 表名]
分类:
1、单行函数:(字符函数、数学函数、日期函数、其他函数、流程控制函数)
例如:concat、length、ifnull、isnull
2、分组函数(功能:做统计使用,又称为统计函数、聚合函数、组函数)
*/
#▲▲▲▲▲▲▲▲▲▲▲▲▲【单行函数】▲▲▲▲▲▲▲▲▲▲▲▲▲
#【字符函数】
#1、length 获取参数值所占的字节个数
SELECT LENGTH('周逸PM');#8个字节
#2、concat 拼接字符串
SELECT CONCAT(last_name,'_',first_name) AS 姓名 FROM employees;
#3、upper、lower
SELECT UPPER('jhon');
SELECT LOWER('JHon');
#4、substr、substring(SQL语句的索引都是从1开始)
#两个参数:1:字符串 2:起始位置(截取从起始位置及其之后的所有字符串)
SELECT SUBSTR("一日难再晨",3);#难再晨
#三个参数:1:字符串2:起始位置3:要截取的长度
SELECT SUBSTR("一日难再晨",3,2);#难再
#5、instr(两个参数:1:字符串2:子串)[返回子串在原串中第一次出现的索引,未找到则返回0]
SELECT INSTR("一日难再晨","难再晨") AS out_put;#3
#6、trim
SELECT TRIM(' 难再晨 ');
SELECT TRIM('a' FROM 'aaaaaaaaa难再晨aaaaaaaaaa') AS result;
#7、lpad、rpad(三个参数:1:字符串2:填充后的总长度3:填充字符)
#[如果填充的总长度小于原字符串长度,则按参数2的值,从左向右按指定填充后的总长度截取原串并返回]
SELECT LPAD('难再晨',5,'l');
SELECT RPAD('难再晨',5,'r');
#8、replace 替换(三个参数1:原串2:将被替换的串3:替换串)
SELECT REPLACE('及时难再晨,难再晨,难再晨,难再晨','难再晨','当勉励') AS result;
#【数学函数】
#1、round 四舍五入
SELECT ROUND(1.445656);#1
#两个参数2:小数点后保留的位数
SELECT ROUND(1.445656,3)#1.446
#2、ceil 向上取整
SELECT CEIL(-1.2);#-1
#3、floor 向下取整
SELECT FLOOR(-1.2);#-2
#4、truncate 截断(2:小数点后保留的位数,在其后的截去)
SELECT TRUNCATE(-1.2435232,3);#-1.243
#5、mod 取余数(相当于计算a % b)
SELECT MOD(-10,-3);#-1
#【日期函数】
#1、now 返回当前系统的日期+时间
SELECT NOW();
#2、curdate 返回当前系统日期,不返回时间
SELECT CURDATE();
#3、curtime 返回当前系统时间,不返回日期
SELECT CURTIME();
#4、可以获取指定的部分(年、月、日、时、分、秒)
SELECT YEAR(CURDATE());
SELECT MONTH('1999-2-5');
SELECT YEAR(hiredate) FROM employees;
SELECT MONTHNAME('1999-2-5');#February
/*
5、str_to_date 将日期格式的字符转换成指定格式的日期
%Y-四位的年份;%y-两位的年份;%m-月份(01,02...);%c-月份(1,2...);%d-日(01,02...)
%H-小时(24小时制);%h-小时(12小时制);%i-分钟(00,01...59);%s-秒(00,01...59)
*/
SELECT STR_TO_DATE('1998-02-3-13','%Y-%m-%d-%H');
#查询入职日期为1992-4-3的员工信息
SELECT * FROM employees WHERE hiredate='1992-4-3';
SELECT * FROM employees WHERE hiredate=STR_TO_DATE('3-04++1992','%d-%m++%Y');
#6、date_format 将日期转换成字符
SELECT DATE_FORMAT(hiredate,'(%c月%d日)-[%Y年]') FROM employees;
#【其他函数】
SELECT VERSION();
SELECT USER();
SELECT DATABASE();
#【流程控制函数】
#1、if函数:实现if-else效果,(类似于三元运算符)
SELECT commission_pct,IF(commission_pct IS NULL,'没有奖金','有') AS 备注 FROM employees;
/*
2、case函数
语法:
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;
/*
case函数的使用二:
case
when 条件1 then 要显示的值1或者语句1
...
else 要显示的值n或者语句n
end
*/
/*
查询员工的工资情况
工资>20000.A
工资>15000,B
工资>10000,C
否则,D
*/
SELECT salary AS 工资,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 级别
FROM employees;
#▲▲▲▲▲▲▲▲▲▲▲▲▲【分组函数】▲▲▲▲▲▲▲▲▲▲▲▲▲
/*分类:sum 求和、avg 平均、max 最大、min 最小、count 计算个数(计算的字段非null的行数)
1、sum avg 一般用来处理数值型
max min count 可以用来处理任何类型
2、以上分组函数都忽略null值
*/
SELECT SUM(salary) 总工资,ROUND(AVG(salary),2) 平均工资,MAX(salary) 最高工资,
COUNT(commission_pct) 奖金率非null的人数
FROM employees;
#与distinct搭配实现去重的运算
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;
#count的介绍(一般用作统计行数)
SELECT COUNT(*),COUNT(1),COUNT(2),COUNT(salary) FROM employees;
/*
效率:
MYISAM存储引擎:count(*)的效率高
INNODB存储引擎:count(*),count(1)效率差不多,比count(字段)效率高一些
*/
#和分组函数一同查询的字段要求是group by后的字段
#datediff函数(返回两个date类型日期相差的天数)
SELECT DATEDIFF(NOW(),'1999-1-22');
1-5分组查询
代码如下:
/*
【分组查询:】
语法:
select 分组函数,列(要求出现在group by后面)
from 表名
[where 筛选条件]
group by 分组的列表
[order by 子句]
注意:
分组的列表必须是group by后面出现的字段
*/
#简单的分组查询
#案例1:查询每个工种的最高工资
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;
#案例2:统计每个位置的部门个数
SELECT location_id,COUNT(*)
FROM departments
GROUP BY location_id;
#复杂的分组查询
#▲▲▲▲▲▲添加分组前的筛选【where】
#案例3:查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary),manager_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id;
#▲▲▲▲▲▲添加分组后的筛选条件【having】
/*
案例4:查询哪个部门的员工个数>2
①查出每个部门的员工个数
②根据①中查询出的员工个数,判断哪个>2
注:不能用where count(*)>2判断,因为employees表中没有count(*)这个字段
*/
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;
#案例5:查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;
#案例6:查询领导编号>102的每个领导手下的最低工资>5000的领导编号,以及其最低工资
SELECT manager_id,MIN(salary)
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;
#按照函数或者表达式分组
#案例7:按员工姓名长度分组,查询每一组的员工个数,筛选员工个数>5的有哪些
#mysql中支持group by和having后面放别名,但是oracle等不支持
SELECT LENGTH(last_name),COUNT(*)
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5;
#按照多个字段分组
#案例8:查询每个部门每个工种的员工的平均工资
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id;
#添加排序的分组查询
#案例9:查询每个部门每个工种的员工的平均工资,并且按平均工资由高到低显示
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id
ORDER BY AVG(salary) DESC;
#特点:
/*
1、分组查询中的筛选条件分为两类:
数据源 位置 关键字
分组前筛选 原始表 group by子句前面 where
分组后筛选 分组后的结果集 group by子句后面 having
①分组函数做条件肯定放在having子句中
②能用分组前筛选的就用它,这样效率高
2、group by子句支持单个字段分组,多个字段分组(多个字段之间用逗号隔开,无顺序要求),表达式或函数(用得较少)
3、也可以添加排序(排序放在整个查询语句的最后)
*/
1-6连接查询(多表查询)
1-6-1sql92标准
代码如下:
/*
连接查询:
连接查询又称多表查询,当查询的字段来自多个表时,使用该查询
笛卡尔乘积现象:表1 m行;表2 n行;查询结果 m*n行
发生原因:没有连接条件
如何避免:添加连接条件
分类:
①按年代分类:
sql92标准:仅仅支持内连接
sql99标准:支持内连接+外连接(左外、右外)+交叉连接
②按功能分类:
内连接:等值连接、非等值连接、自连接
外连接:左外、右外、全外
交叉连接
*/
#【一、sql92】:内连接:等值、非等值、自
#▲▲▲▲▲▲等值连接
/*
①多表等值连接的结果为多表的交集部分
②n表连接,至少需要n-1个连接条件
③多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面的所有子句一起使用(如:排序、分组、筛选)
*/
#案例1:查询女生名和对应的男生名
SELECT `name`,boyName
FROM beauty,boys
WHERE boyfriend_id=boys.`id`;
#案例2:查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;
#2、为表起别名
/*
①提高语言简洁度②区分多个重名字段
注:如果起了别名,就不可以用原来的表名去限定
*/
#案例3:查询员工名、工种号、工种名
SELECT last_name,j.job_id,job_title
FROM employees e,jobs AS j
WHERE e.`job_id`=j.`job_id`;
#加筛选
#查询有奖金的员工名、部门名
SELECT last_name,department_name
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND commission_pct IS NOT NULL;
#查询城市中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
AND city LIKE '_o%';
#加分组
#查询每个城市的部门个数
SELECT city,COUNT(*)
FROM locations l,departments d
WHERE l.`location_id`=d.`location_id`
GROUP BY city;
#查询有奖金的每个部门的部门名、部门的领导编号和该部门的最低工资
SELECT department_name,d.manager_id,MIN(e.salary)
FROM departments d,employees e
WHERE d.`department_id`=e.`department_id`
AND e.`commission_pct` IS NOT NULL
GROUP BY d.department_id;
#加排序
#查询每个工种的工种名和员工的个数,按照员工个数降序
SELECT job_title,COUNT(*)
FROM jobs j,employees e
WHERE j.`job_id`=e.`job_id`
GROUP BY e.`job_id`
ORDER BY COUNT(*) DESC;
#查询员工名、部门名和所在城市
SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`;
#▲▲▲▲▲▲非等值连接
#查询员工的工资等级为A的员工的工资和级别
SELECT salary,grade_level
FROM employees e,job_grades j
WHERE salary BETWEEN j.`lowest_sal` AND j.`highest_sal`
AND grade_level='A';
#▲▲▲▲▲▲自连接
#查询员工名和其领导的名称
SELECT e.last_name AS 员工,m.last_name AS 领导
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;
1-6-2sql99标准
代码如下:
#sql99语法
/*
语法:
select 查询列表
from 表1 别名 [连接类型]
join 表2 别名
on 连接条件
[where 筛选条件]
[group by 分组]
[having 筛选条件]
[order by 排序列表]
分类:
内连接:inner
外连接:左外(left[outer])、右外(right[outer])、全外(full[outer])
交叉连接:cross
*/
#【内连接】:等值连接、非等值连接、自连接
/*
特点:
①添加排序、分组、筛选
②inner可以省略
③筛选条件放在where后面,连接条件放在on后面,提高分离性,便于阅读
④inner join连接和sql92语法中的等值连接效果一样,都是查询多表的交集
*/
#▲▲▲▲▲等值连接
#案例1:查询员工名、部门名
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`;
#案例2:查询名字中包含e的员工名和工种名(添加筛选)
SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.`job_id`=j.`job_id`
WHERE last_name LIKE '%e%';
#案例3:查询每个城市中部门个数大于3的城市(添加分组+筛选)
SELECT city,COUNT(*)
FROM departments d
INNER JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*) > 3;
#案例4.查询哪个部门的员工个数>3的部门名和员工个数,并按个数降序(添加排序)
SELECT department_name,COUNT(*)
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY d.department_id
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;
#5.查询员工名、部门名、工种名,并按部门名降序
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d
INNER JOIN jobs j
ON e.`department_id`=d.`department_id` AND e.`job_id`=j.`job_id`
ORDER BY department_name DESC;
#▲▲▲▲▲▲非等值连接
#案例1:查询员工工资级别
SELECT salary,grade_level
FROM employees e
INNER JOIN job_grades j
ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;
#案例2:查询工资级别数量>20的个数,并且按工资级别降序
SELECT salary,grade_level,COUNT(*) 级别个数
FROM employees e
INNER JOIN job_grades j
ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;
#▲▲▲▲▲▲自连接
#案例3:查询员工名和其上级名称
SELECT e.last_name 员工,m.last_name 经理
FROM employees e
INNER JOIN employees m
ON e.`manager_id`=m.`employee_id`;
#【外连接】
/*
特点:
①外连接的查询结果为主表中的所有记录
如果有匹配的,则显示匹配的
如果没有匹配的,则显示null
②左外连接:left join左边的是主表
右外连接:right join右边是主表
③一般用不可能为null的值(主键)去作为筛选条件
*/
#查询没有男朋友的女生名称(左外连接)
SELECT `name`,boyName
FROM beauty be
LEFT OUTER JOIN boys bo
ON be.boyfriend_id = bo.id
WHERE bo.id IS NULL;
#右外连接
SELECT `name`,boyName
FROM boys bo
RIGHT OUTER JOIN beauty be
ON bo.`id`=be.`boyfriend_id`
WHERE bo.`id` IS NULL;
#查询哪个部门没有员工
SELECT department_name
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE e.`employee_id` IS NULL;
#【交叉连接】使用cross join 其实就是99语法实现的笛卡尔乘积
SELECT bo.*,be.*
FROM beauty be
CROSS JOIN boys bo;
1-7子查询
代码如下:
/*
含义:
出现在其他语句中的select语句,称为子查询或内查询
外部的查询语句,称为主查询或外查询
分类:
一、按子查询出现的位置:
select后面:
仅仅支持标量子查询
from后面:
支持表子查询
where或having后面:★
标量子查询(单行)
列子查询(多行)
行子查询
exists后面(相关子查询)
表子查询
二、按结果集的行列数不同:
标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果集有一行多列)
表子查询(结果集一般为多行多列)
*/
/*一、where或having后面
1、标量子查询(单行子查询)
2、列子查询(多行子查询)
3、行子查询
特点:
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询,一般搭配着单行操作符使用
> < >= <= = <>
列子查询,一般搭配着多行操作符使用
in、any/some、 all
④子查询的执行优先于主查询执行,主查询的条件用到了子查询的结果
*/
#【一、标量子查询】
#案例1:谁的工资比Abel高
SELECT last_name,salary
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name='Abel'
);
#案例2:返回job_ id与141号 员工相同,salary比143号员工多的员工姓名,job_ id和工资
SELECT last_name
FROM employees
WHERE job_id=(
SELECT job_id
FROM employees
WHERE employee_id=141
)
AND salary>(
SELECT salary
FROM employees
WHERE employee_id=143
);
#案例3:返回公司工资最少的员工的last_ name, job_ id和salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees
);
#案例4:查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id=50
);
#【列子查询(多行子查询)】(使用in、some/any、all)
#案例1:返回location_ id是1400或1700的部门中的所有员工姓名
SELECT last_name
FROM employees
WHERE department_id IN(
SELECT department_id
FROM departments
WHERE location_id IN(1400,1700)
);
#案例2:返回其它部门中比job_ id为‘IT_ PROG' 工种任一工资低的员工的员工号、姓名、job__id 以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND
salary<ANY(
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
);
#【行子查询】
#案例3:查询员工编号最小并且工资最高的员工信息
#利用行子查询(where括号后内容,应与select查询的一致)
SELECT *
FROM employees
WHERE(employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees
);
#不利用行子查询
SELECT *
FROM employees
WHERE employee_id=(
SELECT MIN(employee_id)
FROM employees
)
AND
salary=(
SELECT MAX(salary)
FROM employees
);
#select后面
SELECT(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id=d.department_id
)
FROM departments d;
#from后面(将子查询结果充当一张表,要求必须起别名)
#查询每个部门平均工资的工资等级
SELECT avg_tab.*,grade_level
FROM(
SELECT AVG(salary) AS avg_sa
FROM employees
GROUP BY department_id
) AS avg_tab
INNER JOIN job_grades j
ON avg_tab.avg_sa BETWEEN j.`lowest_sal` AND j.`highest_sal`;
#exists后面(相关子查询)
#查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT * FROM employees e
WHERE e.`department_id`=d.`department_id`
);
#查询没有女朋友的男生信息
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN (
SELECT boyfriend_id
FROM beauty
);
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
SELECT * FROM beauty be
WHERE bo.`id`=be.`boyfriend_id`
);
SELECT bo.*,be.*
FROM boys bo
LEFT OUTER JOIN beauty be
ON bo.`id`=be.`boyfriend_id`
WHERE be.`boyfriend_id` IS NULL;
1-8分页查询
代码如下:
/*分页登询
应用场景:当要显示的数据,一页显示不全,需要分页提交sql请求
语法:
select查询列表
from表
join type join 表2
on连接条件
where
筛选条件
group by分组字段
having分组后的筛选
order by排序的字段]
limit offset,size;
offset要显示条目的起始索引(起始索引从);size要显示的条目个数
特点:
①索引从0开始
②limit子句放在查询语句最后
③公式:
要查询的页数page,每页的条目数size
select * from 表名 limit (page-1)*size,size;
*/
#案例1:查询前5条员工的信息
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-9联合查询
代码如下:
/*联合查询
union(联合、合并):将多条查询语句的结果合并成一个结果
语法:
查询语句1
union
查询语句2
union
...
查询语句n;
应用场景:
要查询的结果来自多个表,且多个表之间没有直接的连接关系,但查询的信息一致时
特点:
1、要求多条查询语句的列数是一致的
2、要求多条查询语句查询的每一列的类型和顺序最好一致
3、union关键字默认去重,如果使用union all 可以包含重复项
*/
#查询部门编号>90或者邮箱包含a的员工信息(用联合查询)
SELECT * FROM employees WHERE department_id>90
UNION
SELECT * FROM employees WHERE email LIKE '%a%';
#查询部门编号>90或者邮箱包含a的员工信息(不使用联合查询)
SELECT * FROM employees WHERE department_id>90 OR email LIKE '%a%';
02DML(Data Manipulation Language)数据操作语言【具体看代码部分】
2-1插入语句
代码如下:
/*DML语言(数据操作语言)
插入:insert
修改:update
删除:delete
*/
/*一、插入语句
语法:
insert into 表名(字段1,字段2...字段n) values(值1,值2,...值n);
*/
#【一、经典的插入】
SELECT * FROM beauty;
#1、插入值的类型要与列的类型一直或兼容
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐艺昕','女','1990-4-23','18832778764',NULL,2);
#2、非Nullable的列必须插入值,Nullable的列如何插入值?
#方式一:显示输入null
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(14,'紫琪','女','1990-4-23','18988888723',NULL,9);
#方式二:不输入null
INSERT INTO beauty(id,NAME,phone) VALUES(15,'娜扎','18347146352');
#3、列的顺序可以调换
#4、列的个数和值的个数必须一致
#5、可以省略列名,默认是所有列,而且列的顺序和表中列的顺序一致
#【二、方式二】
/*
语法:
insert into表名
set 列名=值,列名=值,...
*/
INSERT INTO beauty
SET id=16,NAME='刘涛',phone='999';
#【两种方式的对比】
#1、方式一支持插入多行,方式二不支持
INSERT INTO beauty
VALUES(23,'唐艺昕1','女','1990-4-23','187222',NULL,2)
,(24,'唐艺昕1','女','1990-4-23','187222',NULL,2)
,(25,'唐艺昕1','女','1990-4-23','187222',NULL,2)
#2、方式一支持子查询,方式二不支持
INSERT INTO beauty(id,NAME,phone)
SELECT 26,'宋茜','12123';
#
INSERT INTO beauty(id,NAME,phone)
SELECT id,boyName,'123123'
FROM boys WHERE id<3;
#
INSERT INTO beauty(id,NAME,phone)
SELECT 1,'a','123' UNION
SELECT 2,'b','123' UNION
SELECT 3,'c','123' UNION
SELECT 2,'d','123';
2-2修改语句
代码如下:
#修改语句
/*
1、修改单表的记录
语法:update 表名
set 列=新值,列=新值...
where 筛选条件;
2、修改多表的记录
92语法:
update 表1 别名,表2 别名
set 列=值,...
where 连接条件
and 筛选条件;
99语法:
update 表1 别名
inner|left|right join on 表2 别名
on 连接条件
set 列=值,...
where 筛选条件;
*/
#【修改单表的记录】
#案例1:修改beauty表中姓唐的女生的电话为13899888899
UPDATE beauty
SET phone='13899888899'
WHERE NAME LIKE '唐%';
#案例2:修改boys表中id为2的名称为张飞,魅力值10
UPDATE boys
SET NAME='张飞',userCP=10
WHERE id=2;
#【修改多表的记录】
#案例1:修改张无忌的女朋友的手机号为114
UPDATE boys bo
INNER JOIN beauty be
ON bo.`id`=be.`boyfriend_id`
SET be.`phone`='114'
WHERE bo.`boyName`='张无忌';
#案例2:修改没有男朋友的女神的男朋友编号都为2号
UPDATE beauty be
LEFT OUTER JOIN boys bo
ON be.`boyfriend_id`=bo.`id`
SET be.`boyfriend_id`=2
WHERE bo.`id` IS NULL;
2-3删除语句
代码如下:
#删除语句
/*
方式一:delete
语法:
1、单表的删除
delete from 表名 where 筛选条件;
2、多表的删除
sql92语法:
delete 表1别名,表2别名
from 表1 别名,表2 别名
where 连接条件
and 筛选条件
sql99语法:
delete 表1别名,表2别名
from 表1 别名
inner|left outer|right outer join 表2 别名
on 连接条件
where 筛选条件
方式二:truncate
语法:truncate table 表名;
*/
#【delete】
SELECT * FROM beauty;
#单表的删除
DELETE FROM beauty WHERE id BETWEEN 13 AND 16;
#多表的删除:删除张无忌的女朋友的信息
DELETE be
FROM boys bo
INNER JOIN beauty be
ON bo.`id`=be.`boyfriend_id`
WHERE bo.`boyName`='张无忌';
#案例2:删除黄晓明和其女朋友的信息(级联删除)
DELETE bo,be
FROM boys bo
INNER JOIN beauty be
ON bo.`id`=be.`boyfriend_id`
WHERE bo.`boyName`='黄晓明';
SELECT * FROM beauty;
#【truncate】
#案例1:将魅力值>100的男生信息删除(truncate后面不可以加where筛选条件,删除的是整个表)
TRUNCATE TABLE boys;
#【delete VS truncate】
/*
1、delete可以加where条件,truncate不可以加where条件
2、truncate删除的效率稍高
3、加入要删除的表中有自增长列,
使用delete删除后,再插入数据,自增长列的值从断点处开始
使用truncate删除后,再插入数据,自增长列的值从1开始
4、truncate删除没有返回值,delete删除有返回值
5、truncate删除不能回滚,delete删除可以回滚
*/
03DDL(Data Definition Language)数据定义语言【具体看代码部分】
由于库和表的管理所用关键字相同,所以需要在关键字后面标识DATABASE或TABLE以便区分
3-1库的管理
代码如下:
#DDL(数据定义语言)
/*
数据定义语言
库和表的管理
一、库的管理
创建、修改、删除
二、表的管理
创建、修改、删除
创建:create
修改:alter
删除:drop
*/
#【库的管理】
/*
1、库的创建:
语法:
create database [if not exists] 库名;
*/
#1、库的创建
CREATE DATABASE IF NOT EXISTS book;
#2、库的修改
#更改字符集
ALTER DATABASE book CHARACTER SET gbk;
#3、库的删除DROP DATABASE [if exists] 库名;
DROP DATABASE book;
3-2表的管理
代码如下:
#【1、表的创建】
/*
create table [if not exists]表名(
列名 列类型[(长度) 列的约束],
列名 列类型[(长度) 列的约束],
...
列名 列类型[(长度) 列的约束],
);
*/
#创建book表
CREATE TABLE book(
id INT,
bName VARCHAR(20),
price DOUBLE,
authorId INT,
publishDate DATETIME
);
#创建author表
CREATE TABLE author(
id INT,
au_name VARCHAR(20),
nation VARCHAR(20)
);
#【2、表的修改】
/*
①修改表名
②修改列名
③修改列的类型或约束
④添加列
⑤删除列
*/
#①修改列名alter table 表名 change column 列名 新列名 新类型;[change]
ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME;
#②修改列类型或约束alter table 表名 modify column 列名 新类型;[modify]
ALTER TABLE book MODIFY COLUMN pubDate DATETIME;
#③添加新列alter table 表名 add column 新列名 类型 [first|after 列名];
ALTER TABLE author ADD COLUMN annual DOUBLE;
DESC book;
DESC book_author;
#④删除列alter table 表名 drop column 列名;
ALTER TABLE author DROP COLUMN annual;
#⑤修改表名alter table 表名 rename to 新表名;
ALTER TABLE author RENAME TO book_author;
#【3、表的删除】drop table [if exists]表名;
/*
通用写法:
创建库
drop database if exists 库名;
create database 库名;
创建表
drop table if exists 表名;
create table 表名;
*/
#【4、表的复制】
INSERT INTO author
VALUES(1,'村上春树','日本'),
(2,'莫言','中国'),
(3,'冯唐','中国'),
(4,'金庸','中国');
SELECT * FROM author;
#1、仅复制表的结构create table 新表名 like 表名;
CREATE TABLE copy LIKE author;
#2、复制表的结构+数据
CREATE TABLE copy1
SELECT * FROM author;
#复制部分数据
CREATE TABLE copy2
SELECT * FROM author
WHERE nation='日本';
#复制某些字段(没有数据)
CREATE TABLE copy3
SELECT id
FROM author
WHERE 1=2;#或者where 0;
3-3常见的数据类型和约束
3-3-1数值型
代码如下:
#【整型】
/*
分类:
tinyint(1B)、smallint(2B)、mediumint(3B)、int/integer(4B)、bigint(8B)
特点:
①如果不设置无符号或者有符号,默认是有符号,如果想设置无符号,后面需要添加unsigned关键字
②如果不设置长度,会有默认的长度
长度代表的是显示的最小宽度,如果插入的数据不够该长度,会在左边填充n个0使其达到指定的长度
但是后面需要追加zerofill.例如:t2 int(1) zerofill(一旦指定了zerofill,代表其一定是无符号的数值)
*/
#1、如何设置有符号和无符号
CREATE TABLE IF NOT EXISTS math(
t1 INT(1),#默认是有符号,在t1字段插入负数能成功
t2 INT(10) ZEROFILL#设置为无符号,不可插入负数,如果插入的为负数会报异常,并且插入临界值
);
INSERT INTO math VALUES(-1,-1);
ALTER TABLE math MODIFY t2 INT(5) ZEROFILL;
INSERT INTO math VALUES(123123,2);
SELECT * FROM math;
DROP TABLE math;
#【小数】
/*
一、分类:
1、浮点型
float(M,D)
double(M,D)
2、定点型
dec(M,D)
decimal(M,D)(最大取值范围与double相同)
二、特点:
①M:整数+小数部分的位数
D:小数部分的位数
②M和D都可以省略
如果是decimal,则M默认为10,D默认为0,意味着插入的数据小数点后有位数,则插入失败
如果是float和double,则会根据插入的数值的精度来决定精度
③定点型的精确度较高,如果要求插入数值的精度较高(如货币运算等)则考虑使用
*/
CREATE TABLE IF NOT EXISTS fudian(
f1 FLOAT(5,2),
f2 DOUBLE(5,2),
f3 DECIMAL(5,2),#都代表整数部分3位,小数部分2位
f4 DEC(5,2)#默认是f4 decimal(10,0)
);
3-3-2日期型
代码如下:
#日期型
/*
分类:
date只保存日期
time只保存时间
year只保存年
datetime保存日期+时间
timestamp保存日期+时间
特点:
字节 范围 时区的影响
datetime 8 1000-9999 不受
timestamp 4 1970-2038 受
*/
CREATE TABLE riqi(
`date` DATETIME,
`timestamp` TIMESTAMP
);
INSERT INTO riqi VALUES(NOW(),NOW());
#显示当前的时区
SHOW VARIABLES LIKE 'time_zone';
#设置当前的时区
SET time_zone='+9:00';#东九区比东八区晚一个小时
SELECT * FROM riqi;
3-3-3字符型
代码如下:
#【字符型】
/*
较短的文本:char、varchar
较长的文本:text、blob(较长的二进制)
其它:binary、varbinary(较短的二进制)
enum(枚举)
set(集合)
特点:char(M)【M:0~255】 VS varchar(M)【M:0~65535】
写法 M的意思 特点 空间耗费 效率
char char(M) 最大的字符数,可以省略,默认为1 固定长度的字符 比较耗费 高
varchar varchar(M) 最大的字符数,不可以省略 可变长度的字符 比较节省 低
*/
#【enum】
CREATE TABLE zifu(
c1 ENUM('a','b','c'),
c2 SET('a','b','c')
);
INSERT INTO zifu VALUES('A'),#大写的也可以插入,插入后也是小写的
('m');#m没在enum中,插入失败
#【set】
INSERT INTO zifu(c2) VALUES('a,B,c');#插入成功!
3-3-4常见约束
一、概述
约束:一种限制,用于限制表中的数据,为了保证表中数据的准确性和可靠性
二、六大约束
三、添加约束的时机【一定是在添加数据之前】
四、约束的添加分类
1、创建表时【图片可以忽略不看,直接看代码部分】
2、修改表时【图片可以忽略不看,直接看代码部分】
五、标识列【图片可以忽略不看,直接看代码部分】
六、注意点【图片可以忽略不看,直接看代码部分】
代码如下:
#常见约束
/*
【含义:】一种限制,用于限制表中的数据,为了保证表中数据的准确性和可靠性
【分类:】六大约束
①NOT NULL:非空,用于保证该字段的值不能为空(比如:姓名、学号)
②DEFAULT:默认,用于保证字段有默认值(比如:性别)
③PRIMARY KEY:主键,用于保证该字段的值具有唯一性,并且非空(比如:学号、员工编号)
④UNIQUE:唯一,用于保证该字段的值具有唯一性,可以为空(比如:座位号)
⑤CHECK:检查约束【mysql中不支持】(比如:年龄、性别)
⑥FOREIGN KEY:外键,用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联列的值。
(在从表中添加外键约束,用于引用主表中某列的值)(比如:学生表的专业编号,员工表部门编号)
【添加约束的时机】(添加数据之前)
1、创建表时 2、修改表时
【约束的添加分类】
列级约束:6种约束语法上都支持,但外键约束没有效果
表级约束:除了 非空、默认 其它的都支持
*/
CREATE TABLE 表名(
字段1 列类型 列级约束,
字段2 列类型 列级约束,
...
字段n 列类型 列级约束,
表级约束
);
#【创建表时添加约束】
#1、添加列级约束
/*
语法:直接在字段名和类型后面追加 约束类型 即可
只支持:默认、非空、主键、唯一
*/
CREATE TABLE stuinfo(
id INT PRIMARY KEY,#主键
stuName VARCHAR(20) NOT NULL,#非空
gender CHAR(1) CHECK(gender='男' OR gender='女'),#检查(mysql中不支持CHECK)
seat INT UNIQUE,#唯一
ange INT DEFAULT 18,#默认
majorid INT REFERENCES major(id)#外键(对于列级约束,外键无效果)
);
CREATE TABLE major(
id INT PRIMARY KEY,
major_name VARCHAR(20)
);
#查看stuinfo中的所有索引(对于约束为主键、外键、唯一的字段默认创建索引)
SHOW INDEX FROM stuinfo;
#2、添加表级约束
/*
语法:
[constraint 约束名] 约束类型(字段名)
通过show index from 表名,如果不写约束名,默认是字段名,主键约束仍然是PRIMARY
*/
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE IF NOT EXISTS stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT,
CONSTRAINT pk PRIMARY KEY(id),#主键(对于主键来说,改名无效,默认是PRIMARY)
CONSTRAINT uq UNIQUE(seat),#唯一键
CONSTRAINT ck CHECK(gender='男' OR gender='女'),#检查(mysql中不支持)
CONSTRAINT fk FOREIGN KEY(majorid) REFERENCES major(id)#外键
);
#通用的写法
CREATE TABLE IF NOT EXISTS stuinfo(
id INT PRIMARY KEY,
stuname VARCHAR(20) NOT NULL,
gender CHAR(1),
seat INT UNIQUE,
age INT DEFAULT 19,
majorid INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);
DROP TABLE stuinfo
SHOW INDEX FROM stuinfo
/*【修改表时添加约束】
添加列级约束
alter table 表名 modify column 列名 列类型 新约束;
添加表级约束
alter table 表名 add [constraint 约束名] 约束类型(字段名) [外键引用][主表名(字段名)]
*/
#添加非空约束(列级约束)
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
#添加默认约束(列级约束)
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#添加外键约束(表级约束)
ALTER TABLE stuinfo ADD CONSTRAINT fk FOREIGN KEY(majorid) REFERENCES major(id);
#添加主键约束
#①列级
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#②表级
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
#添加唯一约束
#①列级
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#②表级
ALTER TABLE stuinfo ADD UNIQUE(seat);
#【修改表时删除约束】
#删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;
#删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20);
#删除外键约束(创建表时候需要用constraint 指定约束名,否者删除时候引用默认的约束名删除失败)
ALTER TABLE stuinfo DROP FOREIGN KEY fk;
#删除主键约束
ALTER TABLE stuinfo DROP PRIMARY KEY;
#删除唯一
ALTER TABLE stuinfo DROP INDEX uq;
#【标识列】
#修改表时添加标识列
ALTER TABLE stuinfo MODIFY COLUMN id INT AUTO_INCREMENT;
#修改表时删除标识列
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
04TCL(Transaction Control Language)事务控制语言【具体看代码部分】
4-1概述
4-2案例
4-3存储引擎
4-4事务的特点
4-5事务的创建
4-5-1隐式事务
4-5-2显式事务【图片可以忽略不看,直接看代码部分】
4-6演示delete和truncate区别【图片可以忽略不看,直接看代码部分】
4-7设置保存点(类似于设置断点)常搭配rollback使用【图片可以忽略不看,直接看代码部分】
4-8事务的并发问题【图片可以忽略不看,直接看代码部分】
4-9代码展示
/*TCL(Transaction Control Language 事务控制语言)
一个或一组sq1语句组成一个执行单元,这个执行单元要么全部执行,要么全部不执行。
【事务】:事务由单独单元的一个或多个SQL语句组成,在这个单元中,每个MySQL语句是相互依赖的,
整个单独单元作为一个不可分割的整体.
如果单元中某条SQL语句一旦执行失败或产生错误,整个单元将会回滚,所有受到影响的数据将返回到事物开始以前的状态;
如果单元中的所有SQL语句均执行成功,则事物被顺利执行。
【案例】:转账
update 表 set A的余额=1000 where name='A'
update 表 set B的余额=500 where name='B'
A如果转账给B,如果A转账完毕后中途出错,则会导致A的钱减少了但是B的钱却没有增加
*/
/*【存储引擎】
1、概念:在mysq1中的数据用各种不同的技术存储在文件(或内存)中
2、通过show engines; 来查看mysql支持的存储引擎
3、在mysql中用的最多的存储引擎有: innodb,myisam,memory等。
其中innodb支持事务,而myisam、memory等不支持事务
*/
SHOW ENGINES;
/*
【事务的特点】
事务的ACID属性
A(Atomicity):原子性 【事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生】
C(Consistency):一致性 【事务必须使数据库从一个致性状态变换到另外一个致性状态】(例如转账前后总金额不变)
I(Isolation):隔离性 【事务的隔离性是指一个事务的执行不能被其他事务干扰】,即一个
事务内部的操作及使用的数据对并发的其他事务是隔离的,并发
执行的各个事务之,间不能互相干扰.(同样与隔离级别有关系)
D(Durability):持久性 【持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的】,
接下来的其他操作和数据库故障不应该对其有任何影响
*/
/*
【事务的创建】
【隐式事务】:事务没有明显的开启和结束标记
如insert、update、delete(默认开启自动提交即:autocommit=ON)
【显式事务】:事务具有明显的开启和结束标记
前提:必须设置自动提交功能为禁用(set autocommit=0)
【步骤】
步骤1:开启事务
set autocommit = 0;
start transaction;可选的,因为设置autocommit禁用后,也默认开启了事务
步骤2:编写事务中的sql语句
语句1
语句2
...
步骤3:结束事务
commit;提交事务
rollback;回滚事务
*/
SHOW VARIABLES LIKE 'autocommit';#Value=ON
#步骤一:开启事务
SET autocommit = 0;
START TRANSACTION;
#步骤二:编写事务中的sql语句
UPDATE author SET salary = 1500 WHERE `name` = 'A';
UPDATE author SET salary = 1000 WHERE `name` = 'B';
#步骤三:结束事务
ROLLBACK;
SELECT * FROM author;
#【演示truncate、delete】
#演示truncate不可以回滚【数据已经被删除】
SET autocommit=0;
START TRANSACTION;
TRUNCATE TABLE author;
ROLLBACK;
#演示delete可以回滚【数据依旧存在】
SET autocommit=0;
START TRANSACTION;
DELETE FROM author;
ROLLBACK;
#【设置保存点】格式savepoint 节点名(类似于设置断点)
SET autocommit=0;
START TRANSACTION;
DELETE FROM author WHERE id = 100;
DELETE FROM author WHERE id = 101;
SAVEPOINT a;#设置保存点
DELETE FROM author WHERE id IN(102,103,104);
ROLLBACK TO a;#回滚到保存点
SELECT * FROM author;
05视图【具体看代码部分】
5-1概述
5-2视图的好处
5-3案例
5-4视图的创建
5-4-1语法
5-4-2案例
5-5视图的修改
5-6视图的删除
5-7查看视图
5-8视图的更新【不常用】
5-9视图与表的对比
5-10代码展示
#视图
/*
①虚拟表,和普通的表一样使用
②mysql5.1版本出现的新特性
③行和列的数据来自定义视图的查询中使用的表,并且是在使用视图时动态生成的,只保存了sq|逻辑,不
保存查询结果
【应用场景】
①多个地方用到同样的查询结果
②该查询结果使用的sql语句较复杂
*/
#案例:查询姓张的学生名和专业名
CREATE VIEW v1
AS
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.majorid=m.id;
#利用视图实现案例
SELECT * FROM v1 WHERE stuname LIKE '张%';
#【视图的创建】
/*
语法:
create view 视图名
AS
查询语句;(一般是比较复杂的查询语句)
*/
#【案例1】:查询姓名中包含a字符的员工名、部门名和工种信息
#①创建
CREATE VIEW myv1
AS
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;
#②使用
SELECT last_name,department_name,job_title
FROM myv1
WHERE last_name LIKE '%a%';
#【案例2】:查询各部门的平均工资级别
#创建平均工资视图
CREATE VIEW myv2
AS
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id;
#使用
SELECT ag,grade_level
FROM myv2
INNER JOIN job_grades j
ON myv2.`ag` BETWEEN j.`lowest_sal` AND j.`highest_sal`;
#【案例3】:查询平均工资最低的部门信息
SELECT * FROM myv2
ORDER BY myv2.`ag`
LIMIT 1;
#【视图的修改】
/*
方式一:
create or replace view 视图名
as
查询语句;
*/
CREATE VIEW testv1
AS
SELECT * FROM employees;
CREATE OR REPLACE VIEW testv1
AS
SELECT last_name FROM employees;
SELECT * FROM testv1;
/*
方式二:
alter view 视图名
as
查询语句;
*/
ALTER VIEW testv1
AS
SELECT * FROM employees;
#【视图的删除】语法:drop view 视图名1,,视图名2...,视图名n;
DROP VIEW myv1,myv2,testv1;
06变量【具体看代码部分】
6-1系统变量(按照作用范围分)
6-1-1全局变量
6-1-2会话变量
6-2自定义变量(按照作用范围分)
变量是用户自定义的,不是由系统提供的
6-2-1使用步骤
6-2-2用户变量
6-2-3局部变量
6-2-4用户变量和局部变量对比
6-3代码展示
/*
变量:
系统变量:(按照作用范围不同)
全局变量
会话变量
自定义变量:(按照作用范围不同)
用户变量
局部变量
*/
#【系统变量】
/*
注意:如果是全局级别,则需要加GLOBAL,如果是会话级别,则需要加SESSION,如果不写,则默认SESSION
说明:变量由系统提供,不是用户定义,属于服务器层面
使用的语法:
1、查看所有的系统变量:(如果不声明global或session,默认是session)
show global|[session] variables;
2、查看满足条件的部分系统变量:(如下)
show global|[session] variables like '%char%';
3、查看指定的某个系统变量的值
select @@global|[session].系统变量名
4、为某个系统变量赋值
方式一:
set @@global|[session].系统变量名 = 值;
方式二:
set global|[session] 系统变量名 = 值;
*/
#【全局变量】
#查看所有的全局变量
SHOW GLOBAL VARIABLES;
#查看部分的全局变量
SHOW GLOBAL VARIABLES LIKE '%char%';
#查看指定的全局变量的值
SELECT @@global.autocommit;
#为某个指定的全局变量赋值
SET @@global.autocommit = 0;
#【会话变量】
#作用域:仅仅针对于当前会话(连接)有效
#查看所有的会话变量
SHOW VARIABLES;
SHOW SESSION VARIABLES;
#查看部分的会话变量
SHOW VARIABLES LIKE '%char%';
SHOW SESSION VARIABLES LIKE '%char%';
#查看指定的会话变量
SELECT @@tx_isolation;
SELECT @@session.tx_isolation;
#为某个会话变量赋值
SET @@session.tx_isolation = 'read-committed';
SET SESSION tx_isolation = 'read-committed';
#【自定义变量】
/*
说明:变量是用户自定义的,不是由系统提供的
使用步骤:
声明
赋值
使用(查看、比较、运算等)
*/
#【用户变量】作用域:针对于当前会话(连接)有效,同于会话变量的作用域
#应用在任何地方,也就是begin end里面或begin end外面
/*
1、声明并初始化:(三种方式)
set @用户变量名=值
set @用户变量名:=值
select @用户变量名:=值
2、赋值(更新用户变量的值)
方式一:
set @用户变量名=值
set @用户变量名:=值
select @用户变量名:=值
方式二:通过select into
select 字段 into @变量名
from 表;
3、使用(查看用户变量的值)
select @用户变量名
*/
#【局部变量】
#作用域:仅仅在定义它的begin end中有效
#应用在begin end中的第一句话
/*
1、声明:
declare 变量名 类型;
declare 变量名 类型 default 值;
2、赋值
方式一:
set 局部变量名=值
set 局部变量名:=值
select @局部变量名:=值
方式二:通过select into
select 字段 into 局部变量名
from 表;
3、使用
select 局部变量名;
*/
/*
用户变量 vs 局部变量
作用域 定义和使用的位置 语法
用户变量 当前会话 会话中的任何地方 必须加@符号,不用限定类型
局部变量 begin end中 在begin end中,且为第一句话 一般不用加@符号,需要限定类型
*/
07存储过程【具体看代码部分】
7-1概述
7-2注意点
7-3调用语法,具体看代码部分
call 存储过程名(实参列表);
一、空参列表
二、创建带in模式参数的存储过程
1、一个in模式参数
2、多个in模式参数
三、创建带out模式参数的存储过程
1、一个out模式参数
2、多个out模式参数
四、创建带inout模式参数的存储过程
7-4存储过程的删除
7-5查看存储过程的信息
7-6代码展示
#存储过程和函数
/*
存储过程和函数:类似于java中的方法
方法的好处:
1、提高了代码的重用性
2、简化操作
*/
/*
存储过程:
含义:一组预先编译好的SQL语句的集合,理解成批处理语句
好处:
1、提高了代码的重用性
2、简化操作
3、减少了编译次数,并减少了和数据库服务器的连接次数,提高了效率
*/
/*创建语法:
create procedure 存储过程名(参数列表)
begin
存储过程体(一组合法的sql语句)
end
【注意】参数列表包含三部分
【1】参数模式 参数名 参数类型
【举例】
in stuname varchar(20)
【2】 【参数模式】
in:该参数可以作为输入,也就是该参数需要调用方传入值
out:该参数可以作为输出,也就是该参数可以作为返回值
inout:该参数既可以作为输入又可以作为输出.也就是该参数既需要传入值,又可以返回值
【3】
如果存储过程体仅仅只有一句话,BEGIN END可以省略。
存储过程体中的每条sQL语句的结尾要求必须加分号。
存储过程的结尾可以使用DELIMITER重新设置。
语法:
delimiter 结束标记
【调用语法】
call 存储过程名(实参列表);
*/
#【空参列表】
#案例:向t1中插入3条记录
DELIMITER $
CREATE PROCEDURE p1()
BEGIN
INSERT INTO t1(NAME,salary) VALUES('a',100),
('b',100),('c',100);
END $
#调用
CALL p1()$
#【创建带in模式参数的存储过程】
#案例:创建存储过程实现根据女神名,查询对应的男神信息
CREATE PROCEDURE p2(IN beautyName VARCHAR(20))
BEGIN
SELECT bo.*
FROM boys bo
INNER JOIN beauty b ON bo.id = b.boyfriend_id
WHERE b.name = beautyName;
END $
#调用
CALL p2('王昭君')$
#案例2:创建存储过程实现,用户是否登录成功
CREATE PROCEDURE p3(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 p3('admin','password')$
#【创建带out模式参数的存储过程】
#案例1:根据女神名,返回对应的男神名
CREATE PROCEDURE p4(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
SELECT bo.boyName INTO boyName
FROM boys bo
INNER JOIN beauty b ON bo.id = b.boyfriend_id
WHERE b.name = beautyName;
END $
#调用(可以在调用前声明一个用户变量:set @bName$)
CALL p4('王昭君',@bName)$
#案例2:根据女神名,返回对应的男神名和男神魅力值
CREATE PROCEDURE p5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT)
BEGIN
SELECT bo.boyName,bo.userCP INTO boyName,userCP
FROM boys bo
INNER JOIN beauty b ON bo.id = b.boyfriend_id
WHERE b.name = beautyName;
END $
#调用
CALL p5('王昭君',@bName,@usercp)$
#【创建带inout模式参数的存储过程】
CREATE PROCEDURE p6(INOUT m INT,INOUT n INT)
BEGIN
SET m=m*2;
SET n=n*2;
END $
#调用
SET @a = 10$
SET @b = 20$
CALL p6(@a,@b)$
SELECT @a,@b$
08函数【具体看代码部分】
8-1含义
一组预先编译好的SQL语句的集合,理解成批处理语句
8-2好处
8-3创建语法
8-4注意点
8-5存储过程 vs 函数
8-6调用语法
8-7查看函数
8-8删除函数
8-9代码展示
/*
函数(同存储过程的定义一样):
含义:一组预先编译好的SQL语句的集合,理解成批处理语句
好处:
1、提高了代码的重用性
2、简化操作
3、减少了编译次数,并减少了和数据库服务器的连接次数,提高了效率
区别:
存储过程:可以有0个返回,也可以有多个返回,适合做批量插入、批量更新
函数:有且仅有1个返回,适合做处理数据后返回一个结果
*/
/*
【创建语法】:
create function 函数名(参数列表) returns 返回类型
begin
函数体
end
【注意】:
1.参数列表包含两部分: 参数名、参数类型
2.函数体:肯定会有return语句,如果没有会报错
如果return语句没有放在函数体的最后也不报错,但不建议
return 值;
3.函数体中仅有一句话,则可以省略begin end
4.使用delimiter语句设置结束标记
【调用语法】
select 函数名(参数列表);
*/
#1、【无参有返回值】
#案例:返回公司员工个数
DELIMITER $
CREATE FUNCTION fun1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0;#定义局部变量
SELECT COUNT(*) INTO c#为局部变量赋值
FROM employees;
RETURN c;
END $
#调用
SELECT fun1()$
#【有参,有返回】
#案例:根据员工名,返回其工资
CREATE FUNCTION fun2(e_name VARCHAR(20)) RETURNS DOUBLE
BEGIN
SET @salary = 0;#定义用户变量
SELECT employees.salary INTO @salary
FROM employees
WHERE last_name = e_name;
RETURN @salary;
END $
#调用
SELECT fun2('张三')$
#【查看函数】 show create function 函数名;
#【删除函数】 drop function 函数名;
CREATE FUNCTION fun2(m INT,n INT) RETURNS INT
BEGIN
RETURN m+n;
END $
09流程控制【具体看代码部分】
9-1顺序结构
程序从上往下依次执行
9-2分支结构
9-2-1if函数
9-2-2case结构
一、作为表达式
二、作为独立语句
三、案例
9-2-3if结构
9-3循环结构
案例:
9-4经典案例
9-5代码展示
/*
流程控制结构
顺序结构:程序从上往下依次执行
分支结构:程序从两条或多条路径中选择一条去执行
循环结构:程序在满足一定条件的基础上,重复执行一段代码
*/
/*
【分支结构】:
if函数:
功能:实现简单的双分支
语法:select if(表达式1,表达式2,表达式3)
执行顺序:如果表达式1成立,那么返回表达式2的值,反之返回表达式3的值
应用:任何位置
case结构:
情况1:类似于java中的switch语句,一般用于实现等值判断
语法:
case 表达式|变量|字段
when 要判断的值 then 返回的值1或语句1;
when 要判断的值 then 返回的值2或语句2;
...
else 要返回的值n或语句n;
end case;
情况2:类似于java中的多重IF语句, 一般用于实现区间判断
语法:
case
when 要判断的条件1 then 返回值1或语句1;
when 要判断的条件2 then 返回值1或语句2;
...
else 要返回值n或语句n;
end case;
if结构:
功能:实现多重分支
语法:if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
[else 语句n;]
end if;
应用:
只能在begin end中
*/
/*
案例:
创建存储过程,根据传入的成绩,来显示等级,比如传入的成绩: 90-100显示A,80-90显示B,60-80显示c,否则D
*/
CREATE PROCEDURE p1(IN score FLOAT)
BEGIN
CASE
WHEN score BETWEEN 90 AND 100 THEN SELECT 'a';
WHEN score>80 THEN SELECT 'b';
WHEN score>60 THEN SELECT 'c';
ELSE SELECT 'd';
END CASE;
END $
CALL p1(90)$
#创建函数,根据传入的成绩,来显示等级,比如传入的成绩: 90-100返回A,80-90返回B,60-80返回c,否则返回D
CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
IF score BETWEEN 90 AND 100 THEN RETURN 'a';
ELSEIF score>80 THEN RETURN 'b';
ELSEIF score>60 THEN RETURN 'c';
ELSE RETURN 'd';
END IF;
END $
/*
【循环结构】
分类:while、loop、repeat
循环控制:
iterate 类似于continue继续,结束本次循环,继续下一次
leave 类似于break,跳出,结束当前所在的循环
【语法】:
1、while
[标签]:while 循环条件 do
循环体;
end while [标签];
2、loop
[标签]:loop
循环体;
end loop [标签];
可以用来模拟简单的死循环
3、repeat
[标签]:repeat
循环体;
until 结束循环的条件
end repeat [标签];
*/