1. MySql基础
1. DQL语言
1. 基础查询
#进阶1:基础查询
/*
语法:select 查询列表
from 表名;
特点:
1. 查询列表可以是:表中的字段、常量值、表达式、函数
2. 查询的结果是一个虚拟的表格
*/
USE myemployees;
#显示表`departments`的结构
DESCRIBE `departments`;
#1. 查询表中单个字段
SELECT last_name FROM employees;
#2. 查询表中多个字段,选中然后执行
SELECT last_name,salary,email FROM employees;
#3. 查询表中所有字段 一定要有FROM
SELECT `salary`,`job_id`,`phone_number`,`department_id`,`hiredate` FROM employees;
SELECT * FROM employees;
#4.查询常量值
SELECT 100;
SELECT 'jion'; #不区分字符和字符串
#5.查询表达式
SELECT 100*13;
#6.查询函数
SELECT VERSION();
#7. 起别名
/*
便于查询
如果要查询的字段有重名的情况,可以别名区分开来
*/
#方式一 使用AS
SELECT 100%98 AS 结果;
SELECT last_name AS 姓,first_name AS 名 FROM employees;
#方式二 使用空格
SELECT last_name 姓,first_name 名 FROM employees;
#特殊情况,别名有特殊符号,可以给别名添加引号
SELECT salary AS "out put" FROM employees;
#8.去重
#案例:去重查询员工表中涉及到的所有部门编号
SELECT DISTINCT department_id FROM employees;
#9.+号的作用
/*mysql中 + 仅仅表示运算符功能
select 100+90; 可执行
select '123'+90;其中一方为字符型,试图将字符型转化成数值型
select 'jion'+90;如果转化失败,则将字符型数值转化为0
select null+90; 只要一方为null,结果为null
*/
#案例:查询员工名和姓组成一个字段,并显示为 姓名
SELECT CONCAT('a',' ','b') AS 结果;
SELECT CONCAT(last_name,' ',first_name)
AS
姓名
FROM
employees;
小题:
#显示出`employees`的全部列,各个列之间用逗号隔开,列头显示为OUT_PUT;
SELECT
IFNULL(commission_pct,0) AS 奖金率,
commission_pct
FROM
`employees`;
SELECT
CONCAT(`first_name`,',',`last_name`,',',`job_id`,',',IFNULL(commission_pct,0))
AS
'OUT_PUT'
FROM
`employees`;
2. 条件查询
#进阶2:条件查询
/*
语法:
select
查询列表
from
表名
where
筛选条件;
分类:
一.按照条件表达式筛选
条件运算符:> > = != <> >= <=
二.按照逻辑表达式筛选
逻辑运算符:&& || !
and or not
&&和and:两个条件都为true,结果为true,反之为false
||和or: 只要有一个条件为真则为真
!和not:
三.模糊查询
like
between
in
is null
*/
#一.按照条件表达式筛选
#案例1:查询工资>12000的员工信息
SELECT *
FROM
employees
WHERE
`salary`>12000;
#案例2:查询部门编号不等于90号的员工名和部门编号
SELECT
`first_name`,
`department_id`
FROM
employees
WHERE
`department_id`!=90;
#等价
`department_id`<>90;
#二.按照逻辑表达式筛选
#案例1:查询工资在10000和20000之间的员工名、工资和奖金
SELECT
last_name,
salary,
commission_pct
FROM
employees
WHERE
salary>=10000 AND salary<=20000;
#案例2:查询部门编号不是在90到110之间,或者工资高于15000的员工信息、
SELECT
*
FROM
employees
WHERE
NOT (`department_id`>=90 AND `department_id`<=110)
OR `salary`>=15000;
#三.模糊查询
/* like
between
in
is null
*/
/*1.like
一般和通配符搭配使用
通配符:% 任意多个字符(包含0个字符)
_ 任意单个字符
*/
#案例1:查询员工名中包含字符a的员工信息
SELECT
*
FROM
employees
WHERE
`last_name` LIKE '%a%'; #%表示通配符
#案例2:查询员工名中第三个字符为n,第五个字符为l的员工名和工资
SELECT
`last_name`,
`salary`
FROM
employees
WHERE
`last_name` LIKE'__n_l%';
#案例3:查询员工名中第二个字符为_的员工名
SELECT
last_name
FROM
employees
WHERE
last_name LIKE'_\_%'; #\转义字符
last_name LIKE'_$_%' ESCAPE '$'; #ESCAPE 定义转义字符
#2.between and
/*
包含临界值
临界值不可以颠倒顺序
*/
#案例1:查询员工编号在100到120之间的员工信息
SELECT
*
FROM
employees
WHERE
`employee_id` BETWEEN 100 AND 120;
#in
/*
特点:判断某个字段的值是否属于in列表中的某一项
使用in比使用or提高简洁度
in列表的值必须一致
不支持通配符
*/
#案例:查询员工的工种编号是 AD_PRES IT_PROG 中的一个员工名和工种编号
SELECT
`last_name`,
`job_id`
FROM
employees
WHERE
job_id IN('AD_PRES','IT_PROG' );
#4. is null
/*
=或者<>不能判断null值
is null 或者is not null 可以判断null值
*/
# 案例:查询没有奖金的员工名和奖金率
SELECT
`last_name`,
`commission_pct`
FROM
employees
WHERE
`commission_pct` IS NULL;
`commission_pct` IS NOT NULL;
#安全等于 <=>
#案例:查询没有奖金的员工名和奖金率
SELECT
`last_name`,
`commission_pct`
FROM
employees
WHERE
commission_pct<=>NULL;
#案例:查询工资为12000的员工信息
SELECT
`last_name`,
`salary`
FROM
employees
WHERE
salary <=> 12000;
#is null 和 <=>
/*
is null:仅仅可以判断null值,可读性较高,推荐使用
<=>:既可以判断null值,也可以判读普通的数值,可读性较低
*/
3. 排序查询
/*
语法
select 查询条件
from 表
where 筛选条件
order by 排序列表 【asc|desc】
特点:
1. asc 升序 默认
desc 降序
2. order by 子句支持单个字段,多个字段,表达式,函数,别名
3. order by 子句一般放在查询语句的最后(limit子句除外)
*/
#案例:查询员工信息,要求工资从高到低排序
SELECT
*
FROM
employees
ORDER BY
`salary` DESC;
#案例:查询员工信息,要求工资从高到低排序
SELECT
*
FROM
employees
ORDER BY
`salary` ASC;
`salary`;
# 案例:查询部门编号>=90 的员工信息,按照入职时间的先后进行【添加筛选条件】
SELECT
*
FROM
employees
WHERE
`department_id`>=90
ORDER BY
`hiredate` ASC;
#案例:按年薪的高低显示员工的信息和年薪【按照表达式排序】
SELECT
*,
salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM
employees
ORDER BY
`salary`*12*(1+IFNULL(`commission_pct`,0)) DESC;
#案例:按年薪的高低显示员工的信息和年薪【按照别名排序】
SELECT
*,
salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM
employees
ORDER BY
年薪 DESC;
#案例:按姓名的长度显示员工的姓名和工资【按函数排序】
SELECT
LENGTH(`last_name`) AS 字节长度, #函数LENGTH 统计字符长度
`last_name`,
`salary`
FROM
employees
ORDER BY
字节长度 ASC;
#案例:查询员工信息,要求先按照工资排序升序,再按照员工编号降序排序【按照多个字段排序】
SELECT
*
FROM
employees
ORDER BY
`salary` ASC , `employee_id` DESC;
小题:
# 查询员工的姓名和部门号和年薪,按照年薪降序,按照姓名升序
SELECT
CONCAT(`first_name`,`last_name`) AS 姓名,
`department_id`,
`salary`*12*(1+IFNULL(`commission_pct`,0)) AS 年薪
FROM
employees
ORDER BY
年薪 DESC,姓名 ASC;
# 选择工资不在8000到17000的员工的姓名和工资。按工资降序
SELECT
`last_name`,
`salary`
FROM
employees
WHERE
`salary` NOT BETWEEN 8000 AND 17000
ORDER BY
salary DESC;
# 查询邮箱中包含e的员工信息,先按照邮箱的字节数降序,再按照部门号升序
SELECT
*
FROM
employees
WHERE
`email`LIKE"%a%"
ORDER BY
LENGTH(`email`)DESC,`department_id` ASC;
4. 常见函数
#进阶4. 常见函数
/*
概念:类似于java的方法,将一组逻辑语句封装再方法体中,对外暴露方法名
好处:1. 隐藏了实现细节 2. 提高代码的重用性
调用:select 函数名(实参列表) from 表;
特点:
1. 函数名
2. 函数功能
分类:
1. 单行函数
concat,length,ifnull
2. 分组函数
做统计使用,又称为聚合函数,统计函数和组函数
常见函数
字符函数:
length
concat
substr
instr
trim
upper
lower
lpad
rpad
raplace
数学函数:
round
ceil
floor
truncate
mod
日期函数:
now
curdate
curtime
year
month
monthname
day
hour
minute
second
str_to_date
date_format
其他函数:
version
database
user
控制函数:
if
case
*/
# 一. 字符参数
# 1. length 获取参数值的字节个数
SELECT LENGTH('qqqq'); //一个字符占1个字节
SELECT LENGTH('王'); //一个汉字占3个字节
# 2. concat 拼接字符串
SELECT CONCAT('Harry',' ','Peter');
/* group_concat() 函数介绍:
参数类型
group_concat(X)
group_concat(X,Y)
group_concat()函数返回X的非null值的连接后的字符串。如果给出了参数Y,将会在每个X之间用Y作为分隔符。如果省略了Y,“,”将作为默认的分隔符。每个元素连接的顺序是随机的。
*/
SELECT
group_concat(emp_no)
from
dept_emp
group by
dept_no;
# 3. upper lower
# 大写 小写
SELECT UPPER('herry');
SELECT LOWER('Peter');
#案例:将姓大写,名变小写,然后拼接
SELECT CONCAT(UPPER(`last_name`),'_',LOWER(`first_name`)) AS 姓名
FROM
employees;
# 4. substr substring
# 索引从1开始
# 截取从指定索引处后面所有字符
SELECT SUBSTR('斯内普爱莉莉',5) AS out_put;
# 截取从指定索引处指定字符长度的字符
SELECT SUBSTR('斯内普爱莉莉',1,3) AS out_put;
# 案例:姓名中首字符大写,其他字符小写然后用_拼接,显示出来
SELECT CONCAT(UPPER(SUBSTR(`last_name`,1,1)),'_',LOWER(SUBSTR(`last_name`,2,LENGTH(`last_name`)))) AS out_put
FROM
employees;
# 5. instr 返回子串第一次出现的索引,如果找不到返回0
SELECT INSTR('马尔福是铂金贵族','贵族') AS 下标值;
# 6. trim 去掉前后空格
SELECT TRIM(' 德拉科 ')AS out_put;
SELECT LENGTH(TRIM(' 德拉科 '))AS out_put;
SELECT TRIM('a' FROM 'aaaaaaaa德aa拉科aaaaaaaaaaaa')AS out_put;
# 7. lpad 用指定的字符实现左填充长度 ,最终字符长度和中间数字相同
SELECT LPAD('德拉科 马尔福',10,'&') AS out_put;
SELECT LPAD('德拉科 马尔福',3,'&') AS out_put;
# 8. rpad 用指定的字符实现右填充长度 ,最终字符长度和中间数字相同
SELECT RPAD('德拉科 马尔福',10,'&') AS out_put;
SELECT RPAD('德拉科 马尔福',3,'&') AS out_put;
# 9. replace 替换
SELECT REPLACE('哈利破特爱上了张秋','张秋','金妮') AS OP;
SELECT REPLACE('哈利破特爱上了张秋张秋张秋张秋','张秋','金妮') AS OP;
# 二. 数学函数
# round 四舍五入
SELECT ROUND(1.65); #向上取整
SELECT ROUND(-1.65); #绝对值向上取整
SELECT ROUND(1.657,2); # 小数点后两位
# ceil 向上取整 返回大于等于该参数的最小整数
SELECT CEIL(1.3543);
SELECT CEIL(-1.24);
# floor 向下取整
SELECT FLOOR(1.3543);
SELECT FLOOR(-1.24);
# truncate 截断 保留几位,不进行四舍五入
SELECT TRUNCATE(1.6734367,1);
# mod 取余
#MOD(a,b);a-a/b*b
#结果符号和被除数一致
SELECT MOD(10,3);
SELECT MOD(-10,3); #-1
SELECT MOD(10,-3); #1
# 三. 日期函数
# now 返回当前系统日期加时间
SELECT NOW();
# curdate 返回当前系统日期不包括时间
SELECT CURDATE();
# curtime 返回当前系统时间不包括日期
SELECT CURTIME();
# 可以获取指定的部分,年,月,日,小时,分,秒
SELECT YEAR(NOW()) AS 年;
SELECT MONTH(NOW()) AS 月;
SELECT DAY(NOW()) AS 日;
SELECT MONTHNAME(NOW()) AS 月; #返回1-12月的英文名
SELECT YEAR('2000-10-31') AS 年;
SELECT MONTH('2000-10-31') AS 月;
SELECT DAY('2000-10-31') AS 日;
SELECT DISTINCT YEAR(`hiredate`) AS 入职时间
FROM
employees;
# str_to_date:将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE('2000-10-5','%Y-%c-%d');
#查询入职日期位1992-4-3的员工信息
SELECT
*
FROM
employees
WHERE
#`hiredate`=str_to_date('1992-4-3','%Y-%c-%d');
`hiredate`=STR_TO_DATE('4-3-1992','%c-%d-%Y');
# date_format:将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') AS 现在;
# 查询有奖金的员工名和入职日期
SELECT
`last_name`,
DATE_FORMAT(`hiredate`,'%m月/%d日 %y年') AS 入职日期
FROM
employees
WHERE
`commission_pct` IS NOT NULL;
# 四. 其他函数
SELECT VERSION(); # 查看当前版本
SELECT DATABASE(); # 查看当前数据库
SELECT USER(); # 查看当前用户
# 五. 流程控制函数
# 1. if函数:if else 的效果
SELECT IF(10>5,'大','小');
#查询是否有奖金,有就提示有,没有就提示没有
SELECT
`last_name`,
IF(commission_pct IS NULL,'没有','有') AS 备注
FROM
employees;
# 2. case 函数的使用一:switch case 的效果
/*
java中
switch(变量或表达式)
{
case 常量1:语句1;
break;
...
dafault:语句n;
break;
}
mysql中
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量1 then 要显示的值2或语句2;
when 常量1 then 要显示的值3或语句3;
......
else 要显示的值n或语句n;
end
*/
/*
案例:查询员工的工资,要求
部门号=30,显示的工资为1.1倍
部门号=40,显示的工资为1.2倍
部门号=50,显示的工资为1.3倍
其他部门显示的是原工资
*/
SELECT
`salary` AS 原始工资,
`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;
# 3. case 函数的使用二:类似于 多重if
/*
java 中
if(条件2)
{
语句1;
}
else if(条件2)
{
语句2;
}
...
else (条件n)
{
语句n;
}
mysql 中
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>2000 THEN 'A'
WHEN salary>2000 THEN 'B'
WHEN salary>2000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM
employees;
序号 | 格式符 | 功能 |
---|---|---|
1 | %Y | 四位的年份 |
2 | %y | 2位的年份 |
3 | %m | 月份(01,02,…11,12) |
4 | %c | 月份(1,2,3,4) |
5 | %d | 日(01,02…) |
6 | %H | 小时(24小时制) |
7 | %h | 小时(12小时制) |
8 | %i | 分钟(00,01,02…,59) |
9 | %s | 秒(00,01,02…,59) |
# 二. 分组函数
/*
功能:用作统计使用,又称为聚合函数或者统计函数或组函数
分类:
sum 求和
avg 平均值
max 最大值
min 最小值
count 计算个数
特点:
1. sum,avg 一般用于处理数值型
max,min,count 可以处理任何类型
2. sun,avg,max,min,count 都忽略null值
3. 可以和distinct 搭配使用实现去重
4. 一般使用count(*)用作统计行数
5. 和分组函数一同查询的字段要求是 group by 后的字段
*/
# 1. 简单使用
SELECT SUM(`salary`) AS 工资之和
FROM
employees;
SELECT AVG(`salary`) AS 平均工资
FROM
employees;
SELECT MAX(`salary`) AS 最高工资
FROM
employees;
SELECT MIN(`salary`) AS 最低工资
FROM
employees;
SELECT COUNT(`salary`) AS 非空工资
FROM
employees;
SELECT
SUM(`salary`) AS 工资之和,
ROUND(AVG(`salary`),2)AS 平均工资,
MAX(`salary`) AS 最高工资,
MIN(`salary`) AS 最低工资,
COUNT(`salary`) AS 非空工资
FROM
employees;
# 2. 和distinct搭配
SELECT
SUM(DISTINCT salary) AS 去重,
SUM(salary) AS 原
FROM
employees;
SELECT
COUNT(DISTINCT(salary)),
COUNT(salary)
FROM
employees;
# 3. count 函数的详细介绍
SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees; #统计行数
SELECT COUNT(1) FROM employees; #统计行数
# 6. 和分组函数一同查询的字段有限制
小题:
# 1. 显示系统时间
SELECT NOW() AS 系统时间;
# 2. 查询员工号,姓名,工资,以及工资提高百分值20后的结果(new salary)
SELECT
`employee_id`,
`last_name`,
`salary`,
`salary`*(1+0.2) AS 'new salary'
FROM
employees;
# 3. 将员工的姓名按首字符排序,并写出姓名的长度(length)
SELECT
`last_name`,
LENGTH(`last_name`) AS 长度,
SUBSTR(`last_name`,1,1) AS 首字符
FROM
employees
ORDER BY
首字符 ASC;
# 4. 使用case-when 按照下列条件:
/*
job grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
*/
SELECT
`job_id` AS job,
CASE `job_id`
# 字符必须加引号
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'SA_REP ' THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
END AS Grade
FROM
employees;
# 查询员工表中最大入职时间和最小入职时间的相差天数(DIFFRENCE)
SELECT
DATEDIFF(MAX(`hiredate`),MIN(`hiredate`)) AS DIFFRENCE
FROM
employees;
# 查询部门编号为90的员工个数
SELECT
COUNT(*)
FROM
employees
WHERE
`department_id`=90;
5. 分组查询
#进阶5:分组查询
/*
select 分组函数,列(要求出现在group by 的后面)
from 表
where 查询条件
group by 分组的列表
order by 子句
注意:
查询列表必须特殊,要求是分组函数和group by 后出现的字段
特点:
1. 分组查询中的筛选条件可以分为两类(数据源不同)
数据源 位置
分组前筛选: 原始表 group by 子句前,使用where关键字
分组后筛选:分组后的结果集 group by 子句后,使用having关键字
分组函数做条件肯定放在having 子句中
能用分组前筛选优先使用分组前筛选
2. group by子句支持单个字段分组,多个字段分组(多个字段之间没有先后顺序要求),表达式和函数用的较少
3. 也可添加排序(排序放在整个查询的最后)
*/
#简单的分组查询
# 案例1:查询每个工种的最高工资
SELECT
MAX(salary),
`job_id`
FROM
employees
GROUP BY
job_id;
# 案例2:查询每个位置上的部门个数
SELECT
COUNT(*),
`location_id`
FROM
`departments`
GROUP BY
`location_id`;
#添加分组前的筛选条件
# 案例1:查询邮箱中包含a字母的,每个部门的平均工资
SELECT
AVG(salary),
`department_id`
FROM
employees
WHERE
email LIKE '%a%'
GROUP BY
`department_id`
# 案例2:查询有奖金的每个领导手下员工的最高工资
SELECT
MAX(salary),
`manager_id`
FROM
employees
WHERE
`commission_pct`IS NOT NULL
GROUP BY
manager_id;
#添加分组后的筛选条件
# 案例1:查询那个部门的员工个数>2
/*
1. 查询每个部门的员工个数
2. 根据1的结果进行筛选,查询那个部门的员工个数>2
*/
SELECT
COUNT(*),
`department_id`
FROM
employees
GROUP BY
`department_id`
HAVING COUNT(*)>2;
# 案例2:查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT
MAX(salary),
`job_id`
FROM
employees
WHERE
commission_pct IS NOT NULL #分组前筛选
GROUP BY
`job_id`
HAVING
MAX(salary)>12000; #分组后筛选
# 案例3:查询领导编号>102的每个领导手下的最低工资>5000的领导编号是那个,以及最低工资
SELECT
`manager_id`,
MIN(salary)
FROM
employees
WHERE
manager_id>102 #分组前筛选
GROUP BY
`manager_id`
HAVING
MIN(salary)>5000; #分组后筛选
#按表达式或者函数进行分组
# 案例:按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数>5有哪些
SELECT
COUNT(*),
LENGTH(`last_name`) 姓名长度
FROM
employees
GROUP BY
姓名长度 #支持别名
HAVING
COUNT(*)>5;
#按多个字段分组
# 案例:查询每个部门每个工种的员工的平均工资
SELECT
AVG(salary),
`department_id`,
`job_id`
FROM
employees
GROUP BY
department_id,
job_id;
#添加排序
# 案例:查询每个部门每个工种的员工的平均工资,并且按照平均工资的高低显示
SELECT
AVG(salary) AS 平均工资,
`department_id`,
`job_id`
FROM
employees
GROUP BY
`department_id`,
`job_id`
ORDER BY
平均工资 DESC;
小题:
# 1. 查询各个job-id 的员工工资的最大值,最小值和平均值,总和,并按照job-id 升序
SELECT
MAX(salary),
MIN(salary),
AVG(salary),
SUM(salary),
job_id
FROM
employees
GROUP BY
job_id
ORDER BY
job_id ASC;
# 2. 查询员工最高工资和最低工资的差距
SELECT
MAX(salary)-MIN(salary)
FROM
employees;
# 3. 查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内
SELECT
MIN(salary),
`manager_id`
FROM
employees
WHERE
`manager_id` IS NOT NULL
GROUP BY
manager_id
HAVING
MIN(salary)>=6000;
# 4. 查询所有部门的编号,员工数量和工资平均值,并按平均工资降序
SELECT
`department_id`,
COUNT(*),
AVG(salary)
FROM
employees
GROUP BY
`department_id`
ORDER BY
AVG(salary) DESC;
# 5. 选择具有各个job_id的员工人数
SELECT
COUNT(*) AS 个数,
job_id
FROM
employees
GROUP BY
job_id;
6. 连接查询
#进阶6:连接查询
/*
含义:又称多表查询,当查询的字段来自于多个表时,就会用到多表查询
笛卡尔乘积现象:表1有m 行,表2 有 n 行,结果有mn行
发生原因:没有有效的连接条件
解决方法:添加有效的连接条件
分类
按年代分类:
sq192标准:仅仅支持内连接
sql99标准:支持内连接,外连接(不包括全外)加交叉连接
按功能分类:
内连接:
等值连接
非等值连接
自连接
外连接:
左外连接
右外连接
全外连接
交叉连接
*/
#一.sql92标准
#1. 等值连接
/*
特点:
多表连接的结果为多表的交集部分
n表连接至少需要n-1个条件
多表连接顺序没有要求
一般需要为表起别名
可以搭配所有子句使用
*/
# 案例1:查询女孩名和对应的男友名
SELECT
NAME,
`boyName`
FROM
boys,
beauty
WHERE
beauty.boyfriend_id= boys.id;
# 案例2:查询员工名和对应的部门名
SELECT
`last_name`,
`department_name`
FROM
`departments`,
`employees`
WHERE
departments.`department_id`=employees.`department_id`;
#2. 给表起别名
/*
提高语句的简洁度
区分多个重名的字段
取完别名在用原名查询报错
*/
# 案例:查询员工名,工种号,工种名
SELECT
`last_name`,
e.`job_id`,
`job_title`
FROM
`employees` AS e,
`jobs` AS j
WHERE
e.`job_id`=j.`job_id`;
#3. 两个表的顺序可以调换
#4. 可以加筛选
# 案例:查询有奖金的员工名和部门名
SELECT
e.`last_name`,
d.`department_name`,
commission_pct
FROM
`departments` AS d,
`employees`AS e
WHERE
d.`department_id`=e.`department_id`
AND
`commission_pct` IS NOT NULL;
# 案例:查询城市名中第二个字符为o的部门名和城市名
SELECT
`department_name`,
`city`
FROM
`departments` AS d,
`locations` AS l
WHERE
#substr(`city`,2,1) = 'o'
city LIKE '_o%'
AND
d.`location_id`=l.`location_id`;
#5. 可以加分组
# 案例1:查询每个城市的部门个数
SELECT
COUNT(*) AS 个数,
`city`
FROM
`departments` d,
`locations` l
WHERE
d.`location_id`=l.`location_id`
GROUP BY
`city`;
# 案例2:查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT
`department_name`,
d.`manager_id`,
MIN(salary),
`commission_pct`
FROM
`departments` d,
`employees` e
WHERE
d.`department_id`=e.`department_id`
AND
`commission_pct` IS NOT NULL
ORDER BY
`department_name`,
d.`manager_id`
#6. 可以加排序
# 案例:查询每个工种的工种名和员工的个数,并且按照员工个数降序
SELECT
`job_title`,
COUNT(*) AS 个数
FROM
`employees` AS e,
`jobs` AS j
WHERE
e.`job_id`= j.`job_id`
GROUP BY
`job_title`
ORDER BY
COUNT(*)DESC;
#7. 多表连接
# 案例:查询员工名,部门名和所在的城市
SELECT
`last_name`,
`department_name`,
`city`
FROM
`departments` d,
`employees` e,
`locations` l
WHERE
d.`department_id`=e.`department_id`
AND
d.`location_id`=l.`location_id`
#2. 非等值连接
# 案例1:查询员工的工资和工资级别
SELECT
`salary`,
`grade_level`
FROM
`employees` e,
`job_grades` g
WHERE
salary BETWEEN g.`lowest_sal` AND g.`highest_sal`;
#3. 自连接
# 案例:查询 员工名和上级的名称
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`;
7.sql 99语法
#二、sql99语法
/*
语法:
select 查询列表
from 表1 别名 【连接类型】
jion 表2 别名
on 连接条件
where 筛选条件
gruop by 分组
having 筛选条件
order by 排序列表
分类:
内连接:inner
外连接
左外连接:left【outer】
右外连接:right【outer】
全外连接:full【outer】
交叉连接:cross
*/
#一、内连接
/*
select 查询列表
from 表1 别名 【连接类型】
inner jion 表2 别名
on 连接条件
分类:
等值
非等值
自连接
特点:
1.添加排序、分组、筛选
2.inner可以省略
3.筛选条件放在where后面,连接条件放在on后面,提高分离性
4.inner join 连接和sql92 语法中的等值连接效果是一样的,都是查询多表的交集
*/
#1、等值连接
# 案例:查询员工名、部门名
SELECT
`last_name`,
`department_name`
FROM
`employees` e
INNER JOIN `departments` d
ON e.`department_id`=d.`department_id`;
# 案例:查询名字中包含e的员工名和工种名
SELECT
`last_name`,
`job_title`
FROM
`employees` e
INNER JOIN
`jobs` j
ON e.`job_id`=j.`job_id`
WHERE
e.`last_name` LIKE '%e%';
# 案例:查询部门个数>3的城市名和部门个数
SELECT
`city`,
COUNT(*) 部门个数
FROM
`locations` l
INNER JOIN
`departments` d
ON l.`location_id`=d.`location_id`
GROUP BY `city` #按照城市名分类
HAVING COUNT(*)>3;
# 案例:查询哪个部门的部门员工数>3的部门名和员工个数,并按个数降序
SELECT
COUNT(*) 员工个数,
`department_name`
FROM
`departments` d
INNER JOIN
`employees` e
ON
d.`department_id`=e.`department_id`
GROUP BY
`department_name`
HAVING
COUNT(*)>3
ORDER BY
员工个数 DESC;
# 案例:查询员工名、部门名、工种名、并按照部门名降序
SELECT
`last_name`,
`department_name`,
`job_title`
FROM
`employees` e #保证后面多个表均可进行连接
INNER JOIN `departments` ON e.`department_id`=d.`department_id`
INNER JOIN `jobs` j ON e.`job_id`=j.`job_id`
ORDER BY
`department_name` DESC;
#2、非等值连接
# 案例:查询员工的工资级别
SELECT
`grade_level`,
`salary`
FROM
`employees` e
INNER JOIN
`job_grades` g
ON
e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
# 案例:查询每个工资级别的个数>20,并且按工资级别降序
SELECT
COUNT(*) 个数,
`grade_level`
FROM
`employees` e
INNER JOIN
`job_grades` g
ON
e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY
`grade_level`
HAVING
个数 >20
ORDER BY
`grade_level` DESC;
#3、自连接
# 案例:查询姓名中包含字符k的员工的名字,上级的名字
SELECT
e.`last_name` 员工名,
b.`last_name` 上级名
FROM
`employees` e
INNER JOIN
`employees` b
ON
e.`manager_id`=b.`employee_id`
WHERE e.`last_name` LIKE '%k%';
#二、外连接
/*
应用场景:用于查询一个表中有,另一个表中没有的记录
特点:
1. 外查询的结果为主表中的所有记录
如果从表中有和它匹配的,则显示匹配的值
如果从表中没有和它匹配的,则显示null值
外连接查询结果=内连接结果+主表中有但从表中没有的记录
2. 左外连接,left join 左边的是主表
右外连接,right join 右边的是主表
3. 左外和右外交换两表的顺序,可以实现相同的效果
4. 全外连接=内连接结果+表1中有但表2中没有的+表2中有但表1中没有的
*/
# 案例:查询男友不在库中的女生名
SELECT
b.`name`,bo.*
FROM
`beauty` b
LEFT OUTER JOIN
`boys` bo
ON
b.`boyfriend_id`=bo.`id`
WHERE
bo.`id` IS NULL;
# 案例:查询哪个部门没有员工
#左外
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
e.`department_id`=d.`department_id`
WHERE
e.`employee_id` IS NULL;
#3、sql99不支持全连接
#三、交叉连接 实现笛卡尔乘积
SELECT
b.*,
bo.*
FROM
`beauty` b
CROSS JOIN
`boys` bo;
小题:
#案例:查询编号>3 的女神的男朋友信息,如果有则列出详细,如果没有,用null填充
SELECT
b.`id`,
b.`name`,
bo.*
FROM
`beauty` b
LEFT OUTER JOIN
`boys` bo
ON
b.`boyfriend_id`=bo.`id`
WHERE
b.`id`>3;
# 案例:查询哪个城市没有部门
SELECT
l.`city`
FROM
`departments` d
RIGHT OUTER JOIN
`locations` l
ON
l.`location_id`=d.`location_id`
WHERE
d.`department_id`IS NULL;
# 案例:查询部门名为SAL或者IT的员工信息
SELECT
e.*,
`department_name`
FROM
`departments` d
LEFT OUTER JOIN
`employees` e
ON
e.`department_id`=d.`department_id`
WHERE
`department_name` IN ('SAL','IT') ; #条件查询,模糊查询
8. 子查询
#进阶7:子查询
/*
含义:
出现在其他语句中的select 语句,称为子查询或者内查询
外部的查询语句,称为主查询或外查询
分类:
按子查询出现的位置:
select后面
仅仅支持标量子查询
from后面
支持表子查询
where或者having后面
支持标量子查询
列子查询
行子查询(用的较少)
exists后面(相关子查询)
支持表子查询
按结果集的行列数不同
标量子查询(结果集只有一行一列)
列子查询(结果集有一列多行) 多行子查询
行子查询(结果集中有一行多列)
表子查询(一般)
*/
#一、where 或者 having后面
/*
1、标量子查询(单行子查询)
2、列子查询(多行子查询)
3、行子查询(多列多行)
特点:
子查询都可以放在小括号内
子查询一般放在条件右侧
标量子查询,一般搭配着单行操作符使用
> < >= <= = <>
列子查询,一般搭配着多行操作符使用
in,any,some,all
子查询执行优先于主查询的执行
*/
#1、标量子查询
# 案例:谁的工资比Abel高
#查询Abel的工资
SELECT
`salary`
FROM
`employees`
WHERE
employees.`last_name`='Abel';
#查询员工的信息,满足 salary>上一步的结果
SELECT
*
FROM
`employees`
WHERE
`salary`>
(
SELECT
`salary`
FROM
`employees`
WHERE
employees.`last_name`='Abel'
);
# 案例:查询job_id与141号员工相同,salary比143号员工多的员工的姓名,job_id 和工资
#查询141号员工的的job_id
SELECT
`job_id`
FROM
`employees`
WHERE
`employee_id`=141;
#查询要求的员工的姓名,job_id 和工资
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
);
# 案例:返回公司工资最少的员工的last_name,job_id和salary
#找到公司中工资最少的员工
SELECT
MIN(salary)
FROM
`employees`;
#找到员工信息
SELECT
`last_name`,
`job_id`,
`salary`
FROM
employees
WHERE
`salary`=(
SELECT
MIN(salary)
FROM
`employees`
);
# 案例:查询最低工资大于50号部门最低工资的部门id和其最低工资
#找到50号部门的最低工资
SELECT
MIN(salary)
FROM
`employees`
WHERE
`department_id`=50;
#查询要求的部门id和其最低工资
SELECT
`department_id`,
MIN(`salary`) AS 最低工资
FROM
`employees`
GROUP BY
`department_id`
HAVING
最低工资>(
SELECT
MIN(salary)
FROM
`employees`
WHERE
`department_id`=50
);
#2、列子查询(多行子查询) 结果集有一列多行
# 案例:返回`location_id`是1400或者1700的部门中的所有员工姓名
#找出`location_id`是1400或者1700的部门`department_id`
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)
);
# 案例:返回其他工种中比job_id为IT_PROG部门任一工资低员工的:工号、姓名、job_id以及salary
#找出job_id为IT_PROG部门的工资
SELECT
DISTINCT`salary`
FROM
`employees`
WHERE
`job_id`='IT_PROG';
#查询要求的工号、姓名、job_id以及salary
SELECT
`employee_id`,
`last_name`,
`job_id`,
`salary`
FROM
`employees`
WHERE
`salary`<ANY(
SELECT
DISTINCT`salary`
FROM
`employees`
WHERE
`job_id`='IT_PROG'
)
AND
`job_id` <>'IT_PROG';
#或
SELECT
`employee_id`,
`last_name`,
`job_id`,
`salary`
FROM
`employees`
WHERE
`salary`<(
SELECT
MAX(DISTINCT`salary`)
FROM
`employees`
WHERE
`job_id`='IT_PROG'
)
AND
`job_id` <>'IT_PROG';
# 案例:# 案例:返回其他工种中比job_id为IT_PROG部门所有工资低员工的:工号、姓名、job_id以及salar
SELECT
`employee_id`,
`last_name`,
`job_id`,
`salary`
FROM
`employees`
WHERE
`salary`<ALL(
SELECT
DISTINCT`salary`
FROM
`employees`
WHERE
`job_id`='IT_PROG'
)
AND
`job_id` <>'IT_PROG';
#或
SELECT
`employee_id`,
`last_name`,
`job_id`,
`salary`
FROM
`employees`
WHERE
`salary`<(
SELECT
MIN(DISTINCT`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`
);
#二、select 后面
/*
只支持标量子查询
*/
# 案例:查询每个部门的员工个数
SELECT
d.*,
(SELECT
COUNT(*)
FROM
employees e
WHERE
e.`department_id`=d.`department_id`
) 个数
FROM
`departments` d;
# 案例:查询员工号=102的部门名
SELECT (SELECT
d.`department_name`
FROM
`departments` d
INNER JOIN
`employees` e
ON
d.`department_id`=e.`department_id`
WHERE
e.`employee_id`=102);
#三、from 后面
/*
将子查询结果充当一张表,要求必须起别名
*/
# 案例:查询每个部门的平均工资的工资等级
#查询部门平均工资
SELECT
AVG(`salary`),
`department_id`
FROM
`employees`
GROUP BY
`department_id`;
#连接上一步的结果集和`job_grades`表,筛选条件是平均工资 between and
SELECT
ag_dep.*,
g.`grade_level`
FROM
(
SELECT
AVG(`salary`) ag,
`department_id`
FROM
`employees`
GROUP BY
`department_id`
) AS ag_dep
INNER JOIN
`job_grades` g
ON
ag_dep.ag BETWEEN g.`lowest_sal` AND g.`highest_sal`;
#四、exists子查询(相关子查询)
/*
语法:
select exists(完整的查询语句)
结果:1或者0
特点
*/
SELECT EXISTS(SELECT `employee_id` FROM `employees`); #1
SELECT EXISTS(SELECT `employee_id` FROM `employees` WHERE salary=300000); #0
# 案例:查询有员工的部门名
#相关子查询
SELECT
`department_name`
FROM
`departments` d
WHERE EXISTS(
SELECT *
FROM
employees e
WHERE
e.`department_id`=d.`department_id`
);
#in
SELECT
`department_name`
FROM
`departments` d
WHERE
d.`department_id`IN(
SELECT
`department_id`
FROM
`employees`
);
# 查询没有女朋友的男神信息
#exists
SELECT
bo.*
FROM
`boys` bo
WHERE NOT EXISTS(
SELECT
b.*
FROM
`beauty` b
WHERE
b.`boyfriend_id`=bo.`id`
);
#in
SELECT
bo.*
FROM
`boys` bo
WHERE bo.`id` NOT IN
(
SELECT
b.`boyfriend_id`
FROM
`beauty` b
);
小题
#查询和Zlotkey相同部门的员工姓名和工资
#查询 Z的部门id
SELECT
`department_id`
FROM
`employees`
WHERE
`last_name` LIKE 'Zlotkey';
#查询要求的员工姓名和工资
SELECT
`last_name`,
`salary`
FROM
`employees`
WHERE
`department_id` =
(SELECT
`department_id`
FROM
`employees`
WHERE
`last_name` LIKE 'Zlotkey'
);
#查询工资比公司平均工资高的员工的员工号,姓名和工资
#查询平均工资
SELECT
AVG(`salary`)
FROM
`employees`;
#查询要求的员工号,姓名和工资
SELECT
`employee_id`,
`last_name`,
`salary`
FROM
`employees`
WHERE
`salary`>(
SELECT
AVG(`salary`)
FROM
`employees`
);
#查询各个部门中工资比本部门平均工资高的员工的员工号,姓名和工资
#查询每个部门的平均工资
SELECT
AVG(salary),
`department_id`
FROM
`employees`
GROUP BY
`department_id`;
#连接上一步的结果集和`employees`查询要求的员工号,姓名和工资
SELECT
`employee_id`,
`last_name`,
e.`salary`
FROM
`employees` e
INNER JOIN
(
SELECT
AVG(salary) salary,
`department_id`
FROM
`employees`
GROUP BY
`department_id`
) ag_dep
ON
ag_dep.`department_id`=e.`department_id`
WHERE
e.salary>ag_dep.salary;
#查询和姓名中包含字母u的员工在相同部门的员工的员工名和姓名
#查询姓名中包含字母u的员工的部门
SELECT
DISTINCT`department_id`
FROM
`employees`
WHERE
`last_name`LIKE'%u%';
#查询要求的员工的员工名和姓名
SELECT
`employee_id`,
`last_name`
FROM
`employees`
WHERE
`department_id`IN
(SELECT
DISTINCT`department_id`
FROM
`employees`
WHERE
`last_name`LIKE'%u%');
#查询在部门的location_id为1700的部门工作的员工的员工号
SELECT
e.`employee_id`
FROM
`employees` e
INNER JOIN
`departments` d
ON
e.`department_id`=d.`department_id`
WHERE
d.`location_id`=1700;
#或者用子查询
SELECT
`employee_id`
FROM
`employees`
WHERE
`department_id`=ANY
(
SELECT
`department_id`
FROM
`departments`
WHERE
`location_id`=1700
);
#查询管理者是K_ing的员工姓名和工资
#查询k_ing的员工编号
SELECT
`employee_id`
FROM
`employees`
WHERE
`last_name`='K_ing';
#查询哪些员工的`manager_id`是上一步的结果集
SELECT
`last_name`,
`salary`
FROM
`employees`
WHERE
`manager_id` IN (
SELECT
`employee_id`
FROM
`employees`
WHERE
`last_name`='K_ing');
#查询工资最高的员工的姓名,要求`first_name`和`last_name`显示为一列,列名为姓.名
#查询最高工资
SELECT
MAX(salary)
FROM
`employees`;
#查询要求的
SELECT
CONCAT(`first_name`,'.',`last_name`) AS '姓.名'
FROM
`employees`
WHERE
`salary`=(
SELECT
MAX(salary)
FROM
`employees`
);
9. 分页查询
#进阶8:分页查询
/*
应用场景:当要显示的数据,一页显示不全,需要分页提交sql请求
语法:
select 查询列表
from 表
join type 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序后的字段
limit offset,size;
offset要显示条目的起始索引(起始索引从0开始)
size要显示的条目个数
特点:
limit语句放在查询语句的最后,执行也是在最后
公示:
要显示的页数 page ,每页的条目数 size
select 查询列表
from 表
limit (pagr-1)*size,size
*/
# 案例:查询前五条员工信息
SELECT
*
FROM
`employees`
LIMIT
0,5;
SELECT
*
FROM
`employees`
LIMIT
5;
# 案例:查询第11条-25条员工信息
SELECT
*
FROM
`employees`
LIMIT
10,15;
# 案例:查询有奖金员工信息,并且工资较高的前10名显示出来
SELECT
*
FROM
`employees`
WHERE
`commission_pct` IS NOT NULL
ORDER BY
`salary` DESC
LIMIT
10;
小题
#查询和Zlotkey相同部门的员工姓名和工资
#查询 Z的部门id
SELECT
`department_id`
FROM
`employees`
WHERE
`last_name` LIKE 'Zlotkey';
#查询要求的员工姓名和工资
SELECT
`last_name`,
`salary`
FROM
`employees`
WHERE
`department_id` =
(SELECT
`department_id`
FROM
`employees`
WHERE
`last_name` LIKE 'Zlotkey'
);
#查询工资比公司平均工资高的员工的员工号,姓名和工资
#查询平均工资
SELECT
AVG(`salary`)
FROM
`employees`;
#查询要求的员工号,姓名和工资
SELECT
`employee_id`,
`last_name`,
`salary`
FROM
`employees`
WHERE
`salary`>(
SELECT
AVG(`salary`)
FROM
`employees`
);
#查询各个部门中工资比本部门平均工资高的员工的员工号,姓名和工资
#查询每个部门的平均工资
SELECT
AVG(salary),
`department_id`
FROM
`employees`
GROUP BY
`department_id`;
#连接上一步的结果集和`employees`查询要求的员工号,姓名和工资
SELECT
`employee_id`,
`last_name`,
e.`salary`
FROM
`employees` e
INNER JOIN
(
SELECT
AVG(salary) salary,
`department_id`
FROM
`employees`
GROUP BY
`department_id`
) ag_dep
ON
ag_dep.`department_id`=e.`department_id`
WHERE
e.salary>ag_dep.salary;
#查询和姓名中包含字母u的员工在相同部门的员工的员工名和姓名
#查询姓名中包含字母u的员工的部门
SELECT
DISTINCT`department_id`
FROM
`employees`
WHERE
`last_name`LIKE'%u%';
#查询要求的员工的员工名和姓名
SELECT
`employee_id`,
`last_name`
FROM
`employees`
WHERE
`department_id`IN
(SELECT
DISTINCT`department_id`
FROM
`employees`
WHERE
`last_name`LIKE'%u%');
#查询在部门的location_id为1700的部门工作的员工的员工号
SELECT
e.`employee_id`
FROM
`employees` e
INNER JOIN
`departments` d
ON
e.`department_id`=d.`department_id`
WHERE
d.`location_id`=1700;
#或者用子查询
SELECT
`employee_id`
FROM
`employees`
WHERE
`department_id`=ANY
(
SELECT
`department_id`
FROM
`departments`
WHERE
`location_id`=1700
);
#查询管理者是K_ing的员工姓名和工资
#查询k_ing的员工编号
SELECT
`employee_id`
FROM
`employees`
WHERE
`last_name`='K_ing';
#查询哪些员工的`manager_id`是上一步的结果集
SELECT
`last_name`,
`salary`
FROM
`employees`
WHERE
`manager_id` IN (
SELECT
`employee_id`
FROM
`employees`
WHERE
`last_name`='K_ing');
#查询工资最高的员工的姓名,要求`first_name`和`last_name`显示为一列,列名为姓.名
#查询最高工资
SELECT
MAX(salary)
FROM
`employees`;
#查询要求的
SELECT
CONCAT(`first_name`,'.',`last_name`) AS '姓.名'
FROM
`employees`
WHERE
`salary`=(
SELECT
MAX(salary)
FROM
`employees`
);
# 案例:查询工资最低的员工信息:`last_name`,`salary`
#1.查询最低工资
SELECT
MIN(salary)
FROM
`employees`;
#2.查询要求信息
SELECT
`last_name`,
`salary`
FROM
`employees`
WHERE
`salary` =(
SELECT
MIN(salary)
FROM
`employees`
);
#查询平均工资最低的部门信息
#1.查询各部门的平均工资
SELECT
AVG(`salary`)
FROM
`employees`
GROUP BY
`department_id`;
#2.求出最低平均工资的部门编号
SELECT
`department_id`
FROM
employees
GROUP BY
`department_id`
ORDER BY
AVG(salary)
LIMIT
1;
#3.查询部门信息
SELECT
*
FROM
`departments`
WHERE
`department_id`=(
SELECT
`department_id`
FROM
employees
GROUP BY
`department_id`
ORDER BY
AVG(salary)
LIMIT
1
);
#查询平均工资最低的部门信息和该部门的平均工资
#1.查询部门平均工资
SELECT
AVG(`salary`),
`department_id`
FROM
`employees`
GROUP BY
`department_id`;
#2.求出最低平均工资的部门编号
SELECT
`department_id`
FROM
`employees`
GROUP BY
`department_id`
ORDER BY
AVG(`salary`)
LIMIT
1;
#3.求出要求的部门信息和该部门的平均工资
SELECT
d.*,
AVG(salary)
FROM
`departments` d
INNER JOIN
`employees` e
ON
d.`department_id`=e.`department_id`
WHERE
e.`department_id` =
(
SELECT
`department_id`
FROM
`employees`
GROUP BY
`department_id`
ORDER BY
AVG(`salary`)
LIMIT
1
)
#或者:
SELECT
d.*,
ag
FROM
`departments` d
INNER JOIN
(
SELECT
AVG(salary) ag,
`department_id`
FROM
`employees`
GROUP BY
`department_id`
ORDER BY
AVG(`salary`)
LIMIT
1
)ag_dep
ON
ag_dep.`department_id`=d.`department_id`;
#查询平均工资最高的job信息
#1.查询最高的平均工资
SELECT
AVG(salary)
FROM
employees
GROUP BY
`job_id`
ORDER BY
AVG(salary) DESC
LIMIT
1;
#查询job信息
SELECT
*
FROM
`jobs` j
INNER JOIN
(SELECT
AVG(salary),
`job_id`
FROM
employees
GROUP BY
`job_id`
ORDER BY
AVG(salary) DESC
LIMIT
1) ag_emp
ON
j.`job_id`=ag_emp.`job_id`;
#查询平均工资高于公司平均工资的部门有哪些
#1.查询公司平均工资
SELECT
AVG(salary),
FROM
`employees`;
#2.查询要求部门
SELECT
AVG(salary),
`department_id`
FROM
`employees`
GROUP BY
`department_id`
HAVING
AVG(salary)>
(
SELECT
AVG(salary)
FROM
`employees`);
#查询公司中所有`manager的详细信息
#1.查询所有`manager的员工编号
SELECT
DISTINCT`manager_id`
FROM
`employees`;
#2.查询要求的信息
SELECT
*
FROM
`employees`
WHERE
`employee_id` IN(
SELECT
DISTINCT`manager_id`
FROM
`employees`
);
#各个部门中,最高工资中最低那个部门的最低工资是多少
#1.查询各个部门的最高工资中最低的工资
SELECT
MAX(salary)
FROM
`employees`
GROUP BY
`department_id`
ORDER BY
MAX(salary)
LIMIT
1;
#2.查询那个部门中最低工资等于1的结果集
SELECT
`department_id`,
MIN(salary)
FROM
`employees`
GROUP BY
`department_id`
HAVING
MIN(salary)=(
SELECT
MAX(salary)
FROM
`employees`
GROUP BY
`department_id`
ORDER BY
MAX(salary)
LIMIT
1
);
#查询平均工资最高的部门的 `manager的详细信息:`last_name`,`department_id`,`email`,`salary`
#1.查询平均工资最高的部门id
SELECT
`department_id`
FROM
`employees`
GROUP BY
`department_id`
ORDER BY
AVG(`salary`)DESC
LIMIT 1;
#将`departments`和`employees`连接查询,筛选条件为1的结果集
SELECT
`last_name`,
d.`department_id`,
`email`,
`salary`
FROM
`employees` e
INNER JOIN
`departments` d
ON
e.`employee_id`=d.`manager_id`
WHERE
d.`department_id`=
(
SELECT
`department_id`
FROM
`employees`
GROUP BY
`department_id`
ORDER BY
AVG(`salary`)DESC
LIMIT 1
)
小题
#查询每个专业的学生人数
SELECT
COUNT(*) 人数,
`majorid`
FROM
`student`
GROUP BY
`majorid`;
#查询参加考试的学生中,每个学生的平均分,最高分
SELECT
`studentno`,
AVG(`score`),
MAX(`score`)
FROM
`result`
GROUP BY
`studentno`;
#查询姓张的每个学生的最低分大于60的学号、姓名
#1.查询最低分>60的学生学号
SELECT
`studentno`
FROM
`result`
GROUP BY
`studentno`
HAVING
MIN(`score`)>60
#2.查询要求的学号、姓名
SELECT
`studentno`,
`studentname`
FROM `student`
WHERE
`studentno` IN(
SELECT
`studentno`
FROM
`result`
GROUP BY
`studentno`
HAVING
MIN(`score`)>60
)
AND
`studentname`LIKE'张%';
#查询专业生日在“1988—1-1”后的学生姓名,专业名称
SELECT
`studentno`,
`borndate`,
`majorname`
FROM
`student`
WHERE
`borndate`>'1988-1-1';
#2.查询要求的学生姓名,专业名称
SELECT
s.`studentname`,
`majorname`
FROM
`student` s
INNER JOIN
`major` m
ON
s.`majorid`=m.`majorid`
WHERE
`studentno` IN
(
SELECT
`studentno`
FROM
`student`
WHERE
`borndate`>'1988-1-1'
);
#查询每个专业的男生人数和女生人数分别是多少
SELECT
COUNT(*) 个数,
sex,
`majorid`
FROM
`student`
GROUP BY
`sex`,
`majorid`
//方式二
SELECT
`majorid`,
(SELECT COUNT(*) FROM `student` WHERE sex='男' AND `majorid`=s.`majorid`)男,
(SELECT COUNT(*) FROM `student` WHERE sex='女' AND `majorid`=s.`majorid`)女
FROM
`student` s
GROUP BY
`majorid`;
#查询专业和张翠山一样的学生的最低分
#1.查询张翠山的专业
SELECT
`majorid`
FROM
`student`
WHERE
`studentname`='张翠山';
#2.查询要求的
SELECT
MIN(`score`)
FROM
`result` r
INNER JOIN
`student` s
ON
r.`studentno`=s.`studentno`
WHERE
`majorid`=(
SELECT
`majorid`
FROM
`student`
WHERE
`studentname`='张翠山'
);
#查询大于60分是学生的姓名,密码,专业名
#1.查询大于60分是学生号
SELECT
`studentno`
FROM
`result`
WHERE
`score`>60;
#2.查询要求数据
SELECT
s.`studentname`,
s.`loginpwd`,
m.`majorname`,
FROM
`student` s
INNER JOIN
`major` m
ON
s.`majorid`=m.`majorid`
WHERE
s.`studentno`IN(
SELECT
`studentno`
FROM
`result`
WHERE
`score`>60
);
#按照邮箱位数分组,查询每组的学生个数
#1.查询每组邮箱位数
SELECT
COUNT(*),
LENGTH(email)
FROM
`student`
GROUP BY
LENGTH(email);
#查询学生名,专业名,分数
SELECT
s.`studentname`,
m.`majorname`,
r.`score`
FROM
`student` s
LEFT JOIN
`result` r
ON
s.`studentno`=r.`studentno`
INNER JOIN
`major` m
ON
s.`majorid`=m.`majorid`;
#查询哪个专业没有学生,分别用左连接和右连接实现
#1.查询没有学生的专业号
SELECT
m.`majorid`,
m.`majorname`,
s.`studentno`
FROM
`major` m
LEFT JOIN
`student` s
ON
m.`majorid`=s.`majorid`
WHERE
s.`studentno` IS NULL;
#右
SELECT
m.`majorid`,
m.`majorname`,
s.`studentno`
FROM
`student` s
RIGHT JOIN
`major` m
ON
m.`majorid`=s.`majorid`
WHERE
s.`studentno` IS NULL;
#查询没有成绩的学生人数
#1.查询没有成绩的学生号
SELECT
COUNT(*)
FROM
`student` s
LEFT JOIN
`result` r
ON
s.`studentno`=r.`studentno`
WHERE
r.`id` IS NULL;
9. union联合查询
#9.联合查询
/*
union 联合,合并:将多条查询语句的结果合并成一个结果
语法:
查询语句1
union
查询语句2
union
查询语句3
......
应用场景:
要查询的结果来自多个表,且多个表没有直接的连接关系,但查询的信息一致时
特点:
要求多条查询语句的查询列数是一致的
要求多条查询语句的查询的每一列的类型和顺序最好一致
union关键字默认去重,如果使用union all 可以包含重复项
*/
# 案例:查询部门编号>90 或者邮箱中包含a的员工信息
SELECT
*
FROM
`employees`
WHERE
`department_id`>90
UNION
SELECT
*
FROM
`employees`
WHERE
`email`LIKE'%a%';
2. DML语言
数据操作语言:
- 插入:insert
- 修改:update
- 删除:delete
1. 插入语句
/*
语法: 方式一:
insert into 表名(列名,...)
value(值1,...);
方式二:
insert into 表名
set 列名=值,列名=值,....
*/
SELECT
*
FROM
`beauty`;
#1.插入的值的类型要与列的类型一致或者兼容
INSERT INTO `beauty`(`id`,`name`,`sex`,`borndate`,`phone`,`photo`,`boyfriend_id`)
VALUES(13,'小王','女','2000-10-31','188888888',NULL,2);
#2.不可以为null的列必须插入值,可以为null的列如何插入值
#方式1: 直接写null
INSERT INTO `beauty`(`id`,`name`,`sex`,`borndate`,`phone`,`photo`,`boyfriend_id`)
VALUES(13,'小王','女','2000-10-31','188888888',NULL,2);
#方式2: 直接省略列名
INSERT INTO `beauty`(`id`,`name`,`sex`,`phone`,`photo`,`boyfriend_id`)
VALUES(14,'小困','女','188888888',NULL,2);
#3.列的顺序可以调换
INSERT INTO beauty(`name`,`sex`,id,phone)
VALUES('小不','女',15,'1245436');
#4.列数和值的个数必须一致
#5.可以省略列名,默认所有列,而且列的顺序和表中列的顺序一致
INSERT INTO beauty
VALUES(16,'小我','女','2000-10-05','1898888888',NULL,9);
#方式二:
/*
语法:
insert into 表名
set 列名=值,列名=值,....
*/
INSERT INTO beauty
SET id=17,NAME='昆凌',phone='12436';
#两种方式大pk
#1、方式一支持插入多行
INSERT INTO beauty
VALUES
(18,'小李','女','2000-10-05','1898844888',NULL,9),
(19,'小张','女','2000-10-05','1898866888',NULL,9);
#2、方式一支持子查询,方式二不支持
INSERT INTO beauty(id,NAME,phone)
SELECT 26,'宋茜','14325346';
2. 修改语句
/*
1、修改单表的记录
语法:
update 表名
set 列=新值,列=新值
where 筛选条件;
2、修改多表的记录
语法:(sql92)(只支持内连)
update 表1 别名,表2 别名
set 列=值
where 筛选条件
and 筛选条件;
语法:(sql99)(支持内外)
update 表1 别名
inner|left|right| join 表2 别名
on 连接条件
set 列=值,....
where 筛选条件
*/
SELECT
*
FROM
`beauty`;
#1.修改单表的记录
#案例:修改beauty中姓小的电话为1111111
UPDATE `beauty`
SET `phone`='11111111'
WHERE `name`LIKE '小%%'
#案例:修改boys表中id号为2的名称为张飞,魅力值为10
UPDATE boys
SET `boyName`='张飞',`userCP`=10
WHERE id=2;
SELECT
*
FROM
`boys`;
#修改多表的记录
#案例:修改张无忌的女朋友是手机号为114
UPDATE `beauty` b
INNER JOIN `boys` bo
ON b.`boyfriend_id`=bo.`id`
SET b.`phone`='114'
WHERE bo.`boyName`='张无忌';
#案例:修改没有男盆友的女神的男盆友编号都为2号
UPDATE `beauty` b
LEFT JOIN `boys` bo
ON b.`boyfriend_id`=bo.`id`
SET b.`boyfriend_id`=2
WHERE bo.`id`IS NULL;
3. 删除语句
/*
方式一:delete
语法:
1、单表的删除
delete from 表名 where 筛选条件
2、多表的删除
sql92语法:
delete 别名
from 表1 别名,表2 别名
where 连接条件
and 筛选条件
sql99语法
delete 别名
from 表1 别名
inner|left|right join 表2 别名
on 连接条件
where 筛选条件
方式二:truncate
语法:
truncate table 表;
*/
#方式一:delete
SELECT
*
FROM
`beauty`;
#1.单表的删除
# 案例:删除手机号以9结尾的女神信息
DELETE FROM `beauty`
WHERE `phone`LIKE'%9';
#2.多表的删除
# 案例:删除张无忌的女盆友的信息
DELETE b
FROM `beauty` b
INNER JOIN `boys` bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`='张无忌';
# 案例:删除黄晓明的信息以及他女朋友的信息
DELETE b,bo
FROM `boys` bo
INNER JOIN `beauty` b
ON b.`boyfriend_id`=bo.`id`
WHERE `boyName`='黄晓明';
#方式二:truncate语句
#delete和truncate
/*
1.delete 可以加where条件,truncate不能加
2.turncate删除效率更高
3.假如要删除表中有自增长列,
如果delete删除后,再插入数据,自增长列的值从断点开始
如果truncate删除,再插入数据,自增长列的值从1开始
4.truncate删除没有返回值,delete删除有返回值
5.truncate删除不能回滚,delete删除可以回滚
*/
3. DDL语言
数据定义语言,涉及库和表的管理
创建:create
修改:alter
删除:drop
1. 库的管理
创建、修改、删除
#1、库的创建
/*
语法:
create database [IF NOT EXISTS]库名;
*/
#案例:创建库Books
CREATE DATABASE IF NOT EXISTS Books; #IF NOT EXISTS 如果存在则不建,不存在建,提高容错性
#2、库的修改
#更该库的字符集
ALTER DATABASE books CHARACTER SET gbk;
#3、库的删除
DROP DATABASE IF EXISTS books;
2. 表的管理
#1.表的创建
/*
create table 表名(
列名 列的类型 【(列的长度) 列的约束】,
列名 列的类型 【(列的长度) 列的约束】,
列名 列的类型 【(列的长度) 列的约束】,
...
列名 列的类型 【(列的长度) 列的约束】
)
*/
#案例:创建表book
CREATE TABLE book(
id INT,#编号
bName VARCHAR(20), #图书名
price DOUBLE,#价格
authorId INT,#作者编号
publishDate DATETIME #出版日期
);
DESC book; #查询表book结构
#案例:创建表author
CREATE TABLE auther(
id INT,
au_name VARCHAR(20),
nation VARCHAR(10)
);
#2. 表的修改
/*
alter table 表名 add|drop|modify|change column 列名【列类型,约束】;
*/
#①.修改列名 CHANGE COLUMN
ALTER TABLE book CHANGE COLUMN `publishDate` pubDate DATETIME;
#②.修改列的类型或者约束 MODIFY COLUMN
ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP;
#③.添加新列 ADD COLUMN
ALTER TABLE book ADD COLUMN annual DOUBLE;
#④.删除列 DROP COLUMN
ALTER TABLE book DROP COLUMN annual;
#⑤.修改表名
ALTER TABLE auther RENAME TO book_author;
ALTER TABLE auther RENAME TO author;
#3. 表的删除
#drop table 表名;
DROP TABLE IF EXISTS`book_author`;
SHOW TABLES; #查询当前库的所有表
#通用的写法
DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;
DROP TABLE IF EXISTS 旧表名;
CREATE TABLE 表名();
INSERT INTO `author`
VALUES
(1,'江南','中国'),
(2,'罗琳','英国'),
(3,'村上春树','日本');
SELECT
*
FROM
`author`;
#4. 表的复制
# 仅仅复制表的结构
CREATE TABLE copy LIKE `author`;
# 复制表的结构+数据
CREATE TABLE copy2
SELECT * FROM `author`;
SELECT
*
FROM
`copy2`;
#只复制部分
CREATE TABLE copy3
SELECT `au_name` FROM `author`;
#仅仅复制某些字段
CREATE TABLE copy4
SELECT id,`au_name`
FROM `author`
WHERE 0;
小题:
CREATE DATABASE test;
#1、创建表dept1
dr
CREATE TABLE dept1(
id INT(7),
NAME VARCHAR(25)
);
#2、将表`departments`中的数据插入新表dept2中
CREATE TABLE dept2
SELECT *
FROM `myemployees`.`departments`;
#3、创建表emp5
CREATE TABLE emp5
(
id INT(7),
first_name VARCHAR(25),
last_name VARCHAR(25),
dept_id INT(7)
);
#4、将last_name 的长度增加到50
ALTER TABLE `emp5` MODIFY COLUMN `last_name` INT(50);
#5、根据表`employees`创建`employees`2
CREATE TABLE employees2 LIKE `myemployees`.`employees`;
#6、删除表emp5
DROP TABLE IF EXISTS emp5;
#7、把表`employees2`重命名为emp5
ALTER TABLE `employees2` RENAME TO emp5;
#8、在表emp5中添加新列test_column,并检查所作的操作
ALTER TABLE `emp5` ADD COLUMN test_column INT;
3. 常见的数据类型
#常见的数据类型
/*
数值型:
整型
小数:
定点数
浮点数
字符型:
较长的文本:text,blod(较长的二进制文本)
较短的文本:char,varchar
日期型:
*/
#一、整型
/*
分类:
tinyint、smallint、mediumint、int\integer、bigint
1 2 3 4 8
特点:
如果不设置无符号还是有符号,默认是有符号,如果想设置无符号,需要添加unsigned
如果插入的数值超出整型的范围,会报错
*/
#1、如何设置无符号和有符号
DROP TABLE IF EXISTS tab_int;
CREATE TABLE tab_int(
t1 INT,
t2 INT UNSIGNED #设置无符号
);
INSERT INTO tab_int VALUES(-11); #能插入,所以默认情况下为有符号
INSERT INTO tab_int VALUES(-11,-11); #插入失败
INSERT INTO tab_int VALUES(2147483648,11);
INSERT INTO tab_int VALUES(11,11);
SELECT
*
FROM
tab_int;
#二、小数
/*
分类:
1、浮点型
float(M,D)
double(M,D)
2、定点型
dec(M,D)
decimal(M,D)
特点:
M和D可以省略
如果是decimal ,则M默认是10,D默认是0
M:整数部位+小数部位
D:小数部位
定点型的精度较高,如果要求要求插入数值的精度较高,则考虑使用
*/
/*
原则:
所选择的类型越简单越好
*/
#测试M和D
CREATE TABLE tab_float(
f1 FLOAT(5,2),
f2 DOUBLE(5,2),
f3 DEC(5,2)
);
SELECT * FROM `tab_float`;
INSERT INTO tab_float VALUES(123.45,123.45,123.45);
INSERT INTO tab_float VALUES(123.456,123.456,123.456); #报错
INSERT INTO tab_float VALUES(123.4,123.4,123.4);
INSERT INTO tab_float VALUES(1223.45,1223.45,1223.45);
#三、字符型
/*
较短的文本:
char(M):M表示最多字符数, 固定
varchar 可变
其他:
binary 和 varbinary 用于保存较短的二进制
enum用于保存枚举
set用于保存集合
较长的文本
text,
blob(较大的二进制)
特点: 写法 M的意思 特点 空间的耗费 效率
char char(M) 最大字符数 固定长度的字符 比较耗费 高
varchar varchar(M) 最大字符数 可变长度的字符 比较节省 低
*/
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');
SELECT *
FROM
`tab_char`;
#四、日期型
/*
分类:
data只保存日期
time只保存时间
year只保存年
datetime保存日期和时间
timestamp保存日期和时间
特点: 字节 范围 时区的影响
datetime 8 1000-9999 不受
timestamp 4 1970-2038 受
*/
CREATE TABLE tab_date(
t1 DATETIME,
t2 TIMESTAMP
);
INSERT INTO tab_date VALUES(NOW(),NOW());
SELECT
*
FROM
`tab_date`;
#更改时区为东九区
SET time_zone='+9:00'
4. 常见的约束
#常见约束
/*
含义:一种限制,用于限制表中的数据,为了保证数据的一致性
语法:
CREATE TABLE 表名(
字段名 字段内型 约束
);
分类:六大约束
NOT NULL:非空,用于保证该字段的值不能为空
比如姓名、学号等
DEFAULT:默认,用于保证该字段有默认值
比如性别
PRIMARY KEY:主键,用于保证该字段的值具有唯一性,并且非空
比如学号,员工编号等
UNIQUE:唯一,用于保证该字段的值具有唯一性,可以为空
比如座位号等
CHECK:检查约束【mysql中不支持,orical支持】
FOREING KEY:外键,用于限制两个表的关系,用于保证该字段的值必须来自与主表的关联列的值
在从表中添加外键约束,用于引用主表中某列的值
比如学生表中的专业编号,员工表中的部门编号,员工表中的工种编号
添加约束的时机:
创建表时;
修改表时
约束的添加分类:
列级约束
外籍约束不支持
表记约束
除了非空,默认 其他都支持
CREATE TABLE 表名(
字段名 字段内型 约束,
字段名 字段内型 约束,
表级约束
);
主键和唯一的对比:
保证唯一性 是否允许为空 一个表中可以有多少个 是否允许组合
主键 √ × 至多1个 √
唯一 √ √ 可以多个 √
外键:
要求在从表设置外键关系
从表的外键列的类型和主表的关联列的类型要求一致或者兼容,名称无所谓
主表的关联列必须要是一个key(一般是主键或唯一)
要求插入数据时,先插入主表,再插入从表
删除数据时,先删除从表,再删除主表
*/
CREATE DATABASE students;
DROP DATABASE students
#一、创建表时添加约束
#1. 添加列级约束
/*
语法:
直接在字段名和类型后追加 约束类型即可
只支持:
默认,主键,非空,唯一
*/
CREATE TABLE stuinfo(
id INT PRIMARY KEY,#主键
stuName VARCHAR(20) NOT NULL,#非空
seat INT UNIQUE,#唯一
age INT DEFAULT 18#默认约束
);
CREATE TABLE major(
id INT PRIMARY KEY,
majorName VARCHAR(20)
);
#查看`stuinfo`表中的所以索引,包括主键,外键,唯一
SHOW INDEX FROM `stuinfo`;
#2. 添加表级约束
/*
语法:在各个字段的最下面添加
【constraint 约束名】 约束类型(字段名)
*/
DROP TABLE IF EXISTS `stuinfo`;
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT,
CONSTRAINT pk PRIMARY KEY(id),#主键
CONSTRAINT uq UNIQUE(seat),#唯一键
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
);
SHOW INDEX FROM `stuinfo`;
#通用的写法
CREATE TABLE IF NOT EXISTS stuinfo(
id INT PRIMARY KEY,
stuname VARCHAR(20) NOT NULL,
gender CHAR(1),
age INT DEFAULT 18,
seat INT UNIQUE,
majorid INT
CONSTRAINT fk_`stuinfo`_`major` FOREIGN KEY(majorid) REFERENCES major(id)
);
#二、修改表时添加约束
/*
添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;
添加表级约束
alter table 表名 add constraint 约束名 约束类型(字段名)【外键的引用】
*/
#1、添加非空约束
DROP TABLE IF EXISTS `stuinfo`;
CREATE TABLE IF NOT EXISTS stuinfo(
id INT ,
stuname VARCHAR(20) ,
gender CHAR(1),
age INT ,
seat INT ,
majorid INT
);
DESC stuinfo;
#1、添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
#2、添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 22;
#3、添加主键
#1.列级约束
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_marjor FOREIGN KEY(majorid) REFERENCES major(id);
#三、修改表时删除约束
DESC stuinfo;
#1、删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
#2、删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT NULL;
#3、删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;
#4、删除唯一键
ALTER TABLE stuinfo DROP INDEX seat;
SHOW INDEX FROM stuinfo;
#5、删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_marjor;
5.标识
#标识列
/*
又称自增长列
含义:可以不用手动的插入值,系统提供默认的序列值
特点:
标识列不一定要和primary key主键搭配,但必须和key搭配
一个表中至多一个自增长列
标识列的类型只能是数值型
标识列可以通过 set auto_incream_increament=步长; 设置步长
*/
#一、创建表时设置标识列
CREATE TABLE tab_identity(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
INSERT INTO tab_identity VALUES( NULL,'jion');
SELECT * FROM `tab_identity`;
#二、修改表时设置标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
#三、修改表时删除标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT NULL;
4. TCL语言
-
事务控制语言
-
事务:一个或者一组sql语句组成一个执行单元,这个执行单元要么全部执行要么全部不执行
-
事务的ACID属性
-
原子性(Actomicity)
原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生
-
一致性(Consistency)
事务必须使数据库从一个一致性状态变换到另外一个一致性状态
-
隔离性(Isolation)
事务的隔离性是指一个事务的执行不能被其他事务干扰,即一个事务内部的操作及使用的数据对其并发的其他事务是隔离的,并发执行的各个事务之间不能相互干扰
-
持久性(Durability)
持久性是指一个事务一旦被提交,它对数据库中其他数据的改变就是永久的,接下来的其他操作和数据库障碍不应该对其有任何影响
-
-
对于同时运行的多个事务,当这些事务访问数据库中相同的数据时,如果没有采取必要的隔离机制,就会导致各种并发问题:
- 脏读 针对更新
- 不可重复读
- 幻读 针对插入
-
事务的隔离性:数据库系统必须具有隔离并发运行各个事务的能力,使他们不会相互影响,避免各种并发问题
-
一个事务与其他事务隔离的程度称为隔离级别,数据库规定了多种事务的隔离界别,不同隔离级别对应不同的干扰程度,隔离级别越高,数据一致性越好,但并发性就越弱
隔离级别 描述 read uncommitted 读未提交数据 允许事务读取未被其他事务提交的变更、脏读、不可重复读和幻读的问题都会出现 read commited 读已提交数据 只允许事务读取已经被其他事务提交的变更,可以避免脏读,但是不可避重复度和幻读 repeatable read 可重复读(mysql默认) 确保事务可以多次从一个字段中读取相同的值,在这个事务持续期间,禁止其他事务对这个字段进行更新,可以避免脏读和不可重复读,但幻读的问题任然存在 serializable 串行化 确保事务可以从一个表中读取相同的行,在这个事务持续期间,禁止其他事务对该表执行插入,更新和删除操作,所有并发问题都可以避免,但性能低下 #设置当前隔离级别 set transaction isolation level read committed; #设置数据库系统的全局的隔离级别 set global transation isolation level read committed;
#TCL语言 /* 事务控制语言 事务:一个或者一组sql语句组成一个执行单元,这个执行单元要么全部执行要么全部不执行 事务的创建: 隐式事务:事务没有明显的开启和结束的标记 insert\updata\delete语句 显示事务:事务具有明显的开启和结束的标记 前提:必须先设置自动提交功能为禁用 set autocommit=0; 步骤1:开启事务 set autocommit=0; start transation; 步骤2:编写事务中的sql语句(selete insert updata delete) 语句1; 语句2; .... 步骤3:结束事务 commit;提交事务 rollback; 回滚事务 savepoint 节点名; 设置保存点 只能搭配rollback 节点名 使用 */ DROP TABLE IF EXISTS account; CREATE TABLE account( id INT PRIMARY KEY AUTO_INCREMENT, usename VARCHAR(20), balance DOUBLE); INSERT INTO account(`usename`,`balance`) VALUES ('哈利',1000),('赫敏',1000); SELECT * FROM `account`; #演示事务的使用步骤 #开启事务 SET autocommit=0; START TRANSACTION; #编写一组事务的语句 UPDATE account SET balance=500 WHERE `usename`='哈利'; UPDATE account SET balance=1500 WHERE `usename`='赫敏'; #结束事务 COMMIT; #演示savepoint 的使用 SET autocommit=0; START TRANSACTION; DELETE FROM account WHERE id=1; SAVEPOINT a;#设置保存点 DELETE FROM account WHERE id=2; ROLLBACK TO a;#回滚到保存点 SELECT * FROM account; #delete和truncate在事务使用中的区别 #演示delete 支持回滚 SET autocommit=0; START TRANSACTION; DELETE FROM account; ROLLBACK; SELECT * FROM account; #演示truncate 不支持回滚 SET autocommit=0; START TRANSACTION; TRUNCATE TABLE account; ROLLBACK;
5. 视图
-
视图:一种虚拟存在的表,行和列的数据来自定义视图查询中使用的表,并且是在使用视图时动态生成的,只保存了sql逻辑,不保存查询结果
-
应用场景:
多个地方用到相同的查询结果
该查询结果使用的sql语句较为复杂
-
优点
- 实现了sql语句的重用
- 简化复杂的sql语句,不必知道具体的查询细节
- 保护数据,提高了安全性
-
视图和表的区别
名称 | 创建 | 是否占用物理空间 | 使用 |
---|---|---|---|
视图 | create view | 没有很小一部分(保存逻辑) | 增删改查,一般不能增删改 |
表 | create table | 占用 | 增删改查 |
/*
含义:虚拟表,和普通表一样使用
通过表动态生成的数据,具备临时性
*/
#案例:查询姓张的学生名和专业名
SELECT
s.`studentname`,
m.`majorname`
FROM
`student` s
INNER JOIN
`major` m
ON s.`majorid`=m.`majorid`
WHERE
s.`studentname`LIKE '张%';
#使用视图进行封装
CREATE VIEW v1
AS
SELECT
s.`studentname`,
m.`majorname`
FROM
`student` s
INNER JOIN
`major` m
ON s.`majorid`=m.`majorid`;
SELECT *
FROM v1
WHERE
`studentname`LIKE '张%';
#一、创建视图
/*
语法:
create view 视图名
as
查询语句;
*/
#查询邮箱中包含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
*
FROM
`myv1`
WHERE
`last_name`LIKE'%a%';
#查询各个部门的平均工资级别
#创建视图查看每个部门的平均工资
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`;
#查询平均工资最低的部门信息
SELECT
*
FROM
myv2
ORDER BY
ag
LIMIT 1;
#查询平均工资最低的部门名和工资
CREATE VIEW myv3
AS
SELECT *
FROM
myv2
ORDER BY
ag
LIMIT 1;
SELECT
d.*,
m.ag
FROM myv3 m
INNER JOIN `departments` d
ON m.`department_id`=d.`department_id`;
#二、视图的修改
/*
方式一:
create or replace view 视图名
as
查询语句;
方式二:
alter view 视图名
as
查询语句;
*/
#三、删除视图
/*
语法:
drop view 视图名,视图名.....;
*/
DROP VIEW myv2;
#四、查看视图
DESC myv3;
#或者
SHOW CREATE VIEW myv3; #在命令
#五、视图的更新
CREATE OR REPLACE VIEW myv1
AS
SELECT
`last_name`,
`email`
FROM
`employees`;
SELECT
*
FROM
myv1;
#1.插入
INSERT INTO myv1 VALUES('张飞','12234');
#2.修改
UPDATE myv1 SET last_name ='张无忌'
WHERE
last_name='张飞';
#3.删除
DELETE FROM
myv1
WHERE
last_name='张无忌';
#具备一下特点的视图不允许更新
/*
包含一下关键字的sql语句:
分组函数,distinct,group by,having,union,union all
常量视图,
select中包含子查询的,
from一个不能更新的视图,
where子句的子查询引用了from子句中的表
*/
小题:
#一、创建视图emp_v1,要求查询电话号码以‘001‘开头的员工姓名和工资邮箱
DROP VIEW emp_v1;
CREATE VIEW emp_v1
AS
SELECT
`last_name`,
`salary`,
`email`
FROM
`employees`
WHERE
`phone_number`LIKE'001%';
SELECT
*
FROM
emp_v1;
#二、创建视图emp_v2,要求查询部门的最高工资高于12000的部门信息
CREATE OR REPLACE VIEW emp_v2
AS
SELECT
MAX(salary),
d.*
FROM
`employees` e
INNER JOIN
`departments` d
ON
d.`department_id`=e.`department_id`
GROUP BY
d.`department_id`;
SELECT
*
FROM
emp_v2 v2
WHERE
v2.`max(salary)`>12000;
小题
#1.创建book表,字段如下
/*
bid 整型,要求主键
bname 字符型,要求唯一键,并且非空
price 浮点型,要求有默认值 10
btypeId 类型编号,要求引用bookType表的id字段
已知bookType
id
name
*/
CREATE DATABASE test2;
CREATE TABLE bookType (
id INT,
NAME VARCHAR(20)
);
CREATE TABLE book(
bid INT PRIMARY KEY,
bname VARCHAR(20) UNIQUE NOT NULL,
price FLOAT DEFAULT 10,
btypeId INT
FOREIGN KEY(btypeId) REFERENCES `booktype`(id)
);
#2.开启事务,向表中插入一行数据,并结束
SET autocommit=0;
INSERT INTO `book`(`bid`,`bname`,`price`,`btypeId`)
VALUES (1,'哈利波特',100,1);
COMMIT;
#3.创建视图,查询价格大于100的书名和类型名
CREATE VIEW myv1
AS
SELECT
b.`bname`,
bt.`name`
FROM
`book` b
JOIN
`booktype` bt
ON
b.`btypeId`=bt.`id`;
WHERE
price>100;
6. 变量
- 对比用户变量和局部变量
名称 | 作用域 | 定义和使用的位置 | 语法 |
---|---|---|---|
用户变量 | 当前会话 | 会话的任何地方 | 必须加@符号,不用限定类型 |
局部变量 | begin end 中 | 只能在begin end 中,且为 第一句话 | 一般不用加@符号,除非使用select,需要限定类型 |
#变量
/*
系统变量:
全局变量
会话变量
自定义变量:
用户变量
局部变量
*/
#一、系统变量
/*
说明:
变量由系统提供,不是用户定义,属于服务器层面
注意:
如果是全局级别,需要加gloal,如果是会话级别,需要加session,默认session
使用的语法:
1、查看所有的系统变量(默认情况下是会话 session)
show global | session variables;
2、查看满足条件的部分系统变量
show global | session variables like '%char%';
3、查看指定的某个系统变量的值
select @@global[session].系统变量名;
4、为某个系统变量赋值
方式一:
set global【session】系统变量名=值;
方式二:
set @@global|【session】.系统变量名=值;
*/
#1.全局变量
/*
作用域:
服务器每次启动将为所有的全局变量赋初始值,针对于所有的会话(连接)有效,但不能跨重启
*/
#①.查看所有全局变量
SHOW GLOBAL VARIABLES;
#②.查看部分全局变量
SHOW GLOBAL VARIABLES LIKE'%char%';
#③.查看指定的全局变量的值
SELECT @@global.autocommit;
#1.会话变量
/*
作用域:
针对于当前的会话(连接)有效
*/
#1、查看所有的会话变量
SHOW SESSION VARIABLES;
#2、查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%char%';
#3、查看指定的某个会话变量的值
SELECT @@session.系统变量名;
#4、为某个会话变量赋值
#方式一:
SET SESSION 系统变量名=值;
#方式二:
SET @@session.系统变量名=值;
#二、自定义变量
/*
说明:变量是用户定义的,不是由系统的
使用:
声明
赋值
使用(查看、比较、运算等)
*/
#1、用户变量
/*
作用域:
针对当前会话(连接)有效,同于会话变量的作用域
应用在任何地方
*/
#①.声明并初始化
/*
set @用户变量名=值;
set @用户变量名:=值;
select @用户变量名:=值;
*/
SET @count=1;
#②.赋值(更新用户变量的值)
/*
方式一:通过select 或 set
set @用户变量名=值;
set @用户变量名:=值;
select @用户变量名:=值;
方式二:通过select into
select 字段 into 变量名
from 表;
*/
SELECT COUNT(*) INTO @count
FROM
`employees`;
#③.使用(查看用户变量的值)
#select @用户变量值;
SELECT @count;
#二、自定义变量
/*
作用域:
仅仅在定义它的begin end中有效
应用在begin end 中的第一句话
*/
#①.声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;
#②.赋值
方式一:通过select 或 SET
SET 局部变量名=值;
SET 局部变量名:=值;
SELECT @局部变量名:=值;
方式二:通过select INTO
SELECT 字段 INTO 变量名
FROM 表;
#③.使用
SELECT 局部变量名;
#案例:声明两个变量并赋值
#1、用户变量
SET @m=1;
SET @n=2;
SET @sum=@m+@n;
SELECT @sum;