sql
sql_english
show:显示
database:数据库
use:使用
tables:表
from:从
create:创建
desc:【describe】描述
select:选择
where:哪里
as:作为
VERSION:版本
distinct:不同的
escape:逃跑
between:之间
order by:排序依据
ASC:【ascent】升序
DESC:【desc】降序
concat:使连接
length:【函数】长度
upper:上面的 - 大小写转换
lower:下面的 - 大小写转换
substr/substring:截取子串
instr:子字符串检索函数
trim:除去
lpad:【left padding】左填充
rpad:【right padding】右填充
round:四舍五入
ceil:向上取整,返回>=该参数的最小整数
floor:向下取整,返回<=该参数的最大整数
truncate:截断
now():返回当前系统时间和日期
curdate():返回当前系统日期,不包含时间
group by:分组依据
having:有
last:最后的
first:第一、首先
email:邮箱
employees:员工
salary:薪水、工资
department:部门
commission_pct:奖金百分比
job_id:职位编号
hire_date:入职日期、聘用日期
Year:年
month:月
day:日
hour:时
minute:分
second:秒
location_id:位置代号
sql的常见命令
1.查看当前所有的数据库
Show databases;
2.打开指定的库
use 库名;
3.查看当前库的所有表
show tables;
4.查看其它库的所有表
show tables from 库名;
5.创建表
create table 表名(列名列类型,列名列类型, ... );
6.查看表结构
desc 表名;
7.查看服务器的版本
i.登录到mysq1服务端 -> select version();
ii.没有登录到mysq1服务端 -> sql --version | sql --V
sql的语法规范
1.不区分大小写,但建议关键字大写,表名、列名小写
2.每条命令最好用分号结尾
3.每条命令根据需要,可以进行缩进或换行
4.注释
i.【#注释文字;】 | 【-- 注释文字】
ii./* 多行注释 */
DQL
进阶一:基础查询
select 查询列表 from 表名;
查询列表可以是:表中的字段、常量、表达式、函数;查询的结果是一个虚拟的表格
1.1查询表中的单个字段
select last_name from employees;
1.2查询表中的多个字段
select last_name,salary,email from employees;
1.3查询表中所有字段
select * from employees;
1.4查询常量值
select 100; | select ‘john’;
1.5查询表达式
select 100*2;
1.6查询函数
select VERSION();
1.7起别名
作用:便于理解; 如果要查询的字段有重名的情况,使用别名可以区分开来
-
select 100*2 as 结果; select last_name as 姓,first_name as 名 from employees;
-
select last_name 姓,first_name 名 from employees;
案例:查询salary,需求的显示结果为out put
select salary as "out put" from employees;
1.8去重
查询员工表中涉及到的所有的部门编号
select distinct department_id from employees;
1.9“+”号的作用
查询员工名和姓连按成-一个字段,并显示为姓名
select last_name + first_name as 姓名 from employees; //没有作用
java中的 + :i.运算符 ii.连接符
sql中的 + :运算符
select 10+10; //20
select '10'+10; //20
select 'abc'+10; //10
select null+10; //null
1.10comcat - 实现连接
select concat(last_name,first_name) from employees;
1.11ifnull - 判断值是否为null
作用:避免 - 【 select null+10; //null 】
SELECT
CONCAT(`first_name`,",",`last_name`,",",`email`,",",IFNULL(`commission_pct`,0),",",`manager_id`)AS out_put
FROM
employees;
进阶二:条件查询
2.1语法
select 查询列表 from 表名 where 筛选条件;
2.2分类
2.2.1按条件表达式筛选
条件运算符:> < = =! <> >= <=
1:查询工资>12000的员工信息
select * from employees where salary>12000;
2:查询部门编号不等于90号的员工名和部门编号
select last_name,departement_id from employees where departement_id<>90;
2.2.2按逻辑表达式筛选
逻辑运算符:&& || ! and or not
1.查询工资z在10000到20000之间的员工名、工资以及奖金
SELECT
last_name,
`salary`,
`commission_pct`
FROM
`employees`
WHERE
`salary`>=10000
AND
`salary`<=20000;
2.查询部门编号不是在90到110之间,或者工资商于15000的员工信息
SELECT
*
FROM
employees
WHERE
`department_id`<90 OR `department_id`>110 OR salary>15000;
2.2.3模糊查询
like between and in is null | is not null
i:通配符
%:任意多个字符,包含0个字符
_ :任意单个字符
ii:转义符
/
关键字指定转义符:’ %' escape ' ’;
like
一般和通配符搭配使用
-
1.查询员工名中包含字符a的员工信息 SELECT * FROM employees WHERE last_name LIKE '%a%'; 2.查询员工名中第4个字符为 a,第6个字符为 r的员工名和工资 select last_name, salary from employees where last_name like '___a__r%'; 3.查询员工名中第二个字符为_的员工名 SELECT * FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';
between and
作用:可提高语句的简洁度,包含边界值,不能颠倒。
-
1.查询员工编号在100到120之间的员工信息 select * from employees where employee_id between 100 and 120; 【等价于: employee_id >=100 and employee_id <= 120;】
in
含义:判断某字段的值是否属于in列表中的某一项
特点:1.使用 in提高语句简洁度 2.in列表的值类型必须-致或兼容
-
查询员工的工种编号是IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号 SELECT * FROM employees WHERE job_id IN ('IT_PROG','AD_VP','AD_PRES');
is null | is not null
-
查询没有奖金的员工名和奖金率 SELECT last_name, commission_pct FROM employees WHERE commission_pct IS NULL; 安全等于 : <=> SELECT last_name, salary FROM employees WHERE salary <=> 12000;
进阶三:排序查询
3.1语法
select 查询列表 from 表 【where 筛选条件】 order by 排序列表 asc | desc;
3.2特点
1、asc代表的是升序,desc代表的是降序如果不写,默认是升序
2、order by子句中可以支持单个字段、多个字段、表达式、函数、别名
3、order by子句一般是放在查询语句的最后面,limit子句除外
3.3案例
案例1.查询员工信息,要求工资从高到低排序
SELECT * FROM employees ORDER BY salary DESC; //降序
SELECT * FROM employees ORDER BY salary asc; //升序 | 默认升序
案例2.查询部门编号>=90的员工信息,按入职时间的先后进行排序
select *
from employees
where department_id >= 90
order by hiredate;
案例3:按年薪的高低显示员工的信息和年薪[按表达式排序]
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪;
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) desc;
案例4.按姓名的长度显示员工的姓名和工资[按函数排序]
select length(last_name) as 字节长度,last_name,salary
from employees
order by 字节长度 | length(last_name);
案例5.查询员工信息,要求先按工资排序,再按员工编号排序[按多个字段排序]
SELECT employee_id,salary
FROM employees
ORDER BY salary,employee_id ASC;
进阶四:常见函数
4.1概念
类似于java的方法,将一组逻辑语句封装在方法体中,对外暴露方法名
4.2好处
1、隐藏了实现细节 2、提高代码的重用性
4.3调用
select 函数名(实参列表) 【from 表】;
4.4特点
1.叫什么(函数名) 2.干什么(函数功能)
4.5分类
4.5.1单行函数 - concat,length,ifnull等
a.字符函数
-
//length - 获取参数值的字节个数 select length('john'); //concat - 拼接字符串 select concat(last_name,first_name) as 姓名 from employees; //upper、lower - 大小写转换 select upper('hello'); //HELLO select lower('HTLLO'); //hello 示例:将姓变大写,名变小写,然后拼接 select concat(upper(last_name),lower(first_name)) from employees; //substr、substring - 索引从1开始 select substr('我的名字是王家航',6) out_put; //'王家航' <--截取从指定索引处指定字符长度的字符--> select substr('我的名字是王家航',3,2) as out_put; //'名字' 案例:姓名中首字符大写,其他字符小写然后用拼接,显示出来 select concat(upper(substr(last_name,1,1)),lower(substr(last_name,2))) from employees; //instr - 返回子串第一次出现的素引,如果找不到返回0 select instr('阿珍爱上了阿强','阿强') as out_put; //trim select trim(' 王家航 ') as out_put; //王家航 select trim('a' from 'aaaaaaaaaaaa王家aaaa航aaaaa') as out_put; //王家aaaa航 //lpad - 用指定的字符实现左填充指定长度 select lpad('王家航',10,'*') as out_put; //rpad - 用指定的字符实现右填充指定长度 select rpad('王家航',10,'*') as out_put; //replace - 替换 select replace('my name is 王王王','王','忘') as out_put; //my name is 忘忘忘
b.字符函数
-
//round - 四舍五入 select round(1.65); //2 select round(1.565,2) //1.57 - 小数点后保留两位 //ceil - 向上取整,返回>=该参数的最小整数 select ceil(1.0003); //2 //floor - 向下取整,返回<=该参数的最大整数 select floor(9.55); //9 //truncate - 截断:小数点后保留 x 位 select truncate(1.2222225,2); //1.22 //mod - 取余 select mod(10,3); //1 = 10%3
c.日期函数
-
//now - 返回当前系统时间和日期 select now(); //curdate - 返回当前系统日期,不包含时间 select curdate(); //curtime - 返回当前时间,不包含日期 select curtime(); //可以获取指定的部分,年、月、日、小时、分钟、秒 select yrar(now()) as 年; Year:年 month:月 day:日 hour:时 minute:分 second:秒 //str_to_date - 将字符通过指定的格式转换成日期 STR_TO_DATE('9-13-1999','%m- %d- %Y') 1999-09-13 //date_format;将日期转换成字符 DATE FORMAT(2018/6/6','%Y年%m月%d日) 2018年06月06日 select date_format(now(),'%y年%m月%d日') as out_put; 查询有奖金的员工名和入职日期(xx月/xx日 xx年) SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年'),commission_pct FROM employees WHERE commission_pct IS NOT NULL;
d.其他函数
-
SELECT VERSION(); SELECT DATABASE(); SELECT USER();
e.流程控制函数
if函数 - if else 的效果
-
select if(10>5,'大','小'); //大 //查询是否有奖金 SELECT last_name,commission_pct, IF(commission_pct IS NULL,'没有奖金奥 垃圾','有奖金,恭喜') AS out_put FROM employees;
case函数_使用一 - switch case 的效果
-
case 要判断的字段或表达式 when 常量1 then 要显示的值1或语句1; when 常量2 then 要显示的值2或语句2; ... else 要显示的值n或语句n; end 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;
case 函数的使用二 - 类似于多重if
-
case when 条件1 then 要显示的值1或语句1 when 条件2 then 要显示的值2或语句2 else 要显示的值n或语句n end SELECT salary, CASE WHEN salary>20000 THEN 'A' WHEN salary>15000 THEN 'B' WHEN salary>10000 THEN 'C' ELSE 'D' END AS 工资级别 FROM employees ORDER BY salary DESC;
4.5.2分组函数
功能:用作统计使用,又称为聚合函数或統计函数或组函数
分类:sum求和、avg平均值、max最大值、min最小值、count计算个数
特点:1.sum、avg-般用于处理数值型 ,max、min、count可以处理任何类型
2.以上分组函数都忽略nu11值
3.可以和distinct搭配实现去重的运算
4.count 函数的单独介绍 - 一般使用count(*)用作统计行数
5.和分组函数一同查询的字段要求是group by后的字段
简单使用
-
select sum(salary) from employees; select avg(salary) from employees; select max(salary) from employees; select min(salary) from employees; select count(salary) from employees; //和distinct搭配 select sum(distinct salary),sum(salary) from employees; select concat(*) from employees; //查询行数
进阶五:分组查询
5.1语法
select 分组函数,列(要求出现在group by的后面)
from
【where 筛选条件】
group by 分组的列表
【group by 字句】
#查询每个工种的最高工资
select job_id,max(salary)
from employees
group by job_id;
#查询邮箱中包含a字符的,每个部门的平均工资
SELECT email,department_id,ROUND(AVG(salary),2) AS 平均工资
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;
#查询有奖金的每个领导手下员工的最高工资
select max(salary),manager_id
from employees
where commission_pct Is NOT NULL
group by manager_id;
#添加复杂的筛选条件
#查询哪个部门的员工个数>2
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;
#查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
select max(salary) as 最高工资,job_id,commission_pct
from employees
where commission_pct is not null
group by job_id
having 最高工资 > 12000;
#查询领导編号>102的每个领导手下的最低工资>5000的领导编号是哪个,以及其最低工资
SELECT manager_id,MIN(salary) AS 最低工资
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING 最低工资>5000;
5.2总结
1、分组查询中的筛选条件分为两类
数据源 位置 关键字 分组前筛选 原始表 group by子句的前面 where 分组后筛选 分组后的结果集 group by子句的后面 having i:分组函数做条件肯定是放在having子句中 ii:能用分组前筛选的,就优先考虑使用分组前筛选
2、group by子句支持单个字段分组,多个字段分组(多个字段之间用逗号隔开没有顺序要求),表达式或函数(用得较少)
3、也可以添加排序(排序放在整个)
#按表达式或函数分组
#案例:按员工姓名的长度分组,查询每一组的员工个数,筛选员工姓名长度个>5的长度的个数
SELECT COUNT(*),LENGTH(last_name)
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*) > 5;
#按多个字段分组
#查询每个部门每个工种的员工的平均工资
select avg(salary) a,department_id,job_id
from employees
group by department_id,job_id;
#添加排序
#查询每个部门每个工种的员工的平均工资,并且按平均工资的高低显示
select avg(salary) a,department_id,job_id
from employees
group by department_id,job_id
order by a;
5.3课后习题
-
#查询各job_id的员工工资的最大值,最小值,平均值,总和,并按job__id升序 SELECT job_id,MAX(salary) AS 最大值,MIN(salary) AS 最小值,AVG(salary) AS 平均值,SUM(salary) AS 和 FROM employees GROUP BY job_id ORDER BY 和 ASC; #查询员工最高工资和最低工资的差距(DIFFERENCE) SELECT MAX(salary)-MIN(salary) AS difference FROM employees; #查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内 SELECT MIN(salary),manager_id FROM employees WHERE manager_id IS NOT NULL GROUP BY manager_id HAVING MIN(salary) >= 6000; #查询所有部门的编号,员工数量和工资平均值,并按平均工资降序 SELECT department_id,COUNT(*),ROUND(AVG(salary),2) AS 平均工资 FROM employees GROUP BY department_id ORDER BY 平均工资 DESC; #选择具有各个job_id的员工人数 SELECT job_id,COUNT(*) FROM employees GROUP BY job_id;
进阶六:连接查询
6.1含义
又称多表查询,当查询的字段来自于多个表时,就会用到连接查询
6.2笛卡尔乘积现象
表现:表1有m行,表2有n行,结果=m*n行
发生原因:没有有效的连接条件
如何避免:添加有效的连按条件
语法:select name,boyName from beauty,boys;
-
SELECT NAME,boyname FROM beauty,boys WHERE beauty.`boyfriend_id` = boys.id;
6.3分类
6.3.1按年代分类
a.sql92标准
仅仅支持内连接
语法
select 查询列表
from 表1,表2
where 连接条件
and 筛选条件;
b.sql99标准
支持内连接+外连接(左外和右外)+交叉连接
语法
select 查询列表
from 表1 别名【连接类型】
join 表2别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
添加排序、分组、筛选
inner可以省略
筛选条件放在where后面,连按条件放在on后面,提高分高性,便于阅读
/*
连接类型:
内连接:inner
左外:left 【outer】
右外:right 【outer】
全外:full 【outer】
交叉:cross 【outer】
*/
6.3.2按功能分类
a.内连接
等值连接
- sql92写法
-
#案例: #等值连接 #案例1:查询女神名和对应的男神名 SELECT name,boyname FROM beauty,boys WHERE beauty.boyfriend_id = boys.id; #案例2:查询员工名和对应的部门名 SELECT last_name,department_name FROM employees,departments WHERE employees.department_id = departments.department_id; #为表起别名 - 提高语句的简洁度,区分多个重名的字段 #注意:如果为表起了别名,则查询的字段就不能使用原来的表名去限定 #查询员工名、工种号、工种名 SELECT last_name,e.job_id,job_title FROM employees AS e,jobs AS j WHERE e.`job_id` = j.`job_id`; #查询城市名中第二个字符为o的部门名和城市名 select department_name,city from departments d,locations l where d.location_id = l.`location_id` and city like '_o%'; #查询每个城市的部门个数 SELECT city,COUNT(*) AS 部门个数 FROM departments d,locations l WHERE d.`location_id` = l.`location_id` GROUP BY city; #查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资 SELECT MIN(salary) AS 最低工资,d.manager_id,department_name FROM employees e,departments d WHERE e.`department_id` = d.`department_id` AND commission_pct IS NOT NULL GROUP BY department_name,d.manager_id; #查询每个工种的工种名和员工的个数,并且按员工个数降序 SELECT COUNT(*) 个数,job_title FROM employees e,jobs j WHERE e.`job_id` = j.`job_id` GROUP BY job_title ORDER BY 个数 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` ORDER BY city;
总结:
1.多表等值连接的结果为多表的交集部分
2.n表连接,至少需要n-1个连接条件
3.多表的顺序没有要求
4.一般需要为表起别名
5.可以搭配前面介紹的所有子句
-
sql99写法
#查询员工名、部门名 select last_name,department_name from employees e inner join deparments d on e.deparment_id = d.deparment_id; #查询部门个数>3的城市名和部门个数,(添加分组+筛选) select city,count(*) 个数 from locations l inner join deparments d on l.location_id = d.location_id group by deparment_id having count(*) >3;
非等值连接
-
sql92
#查询员工的工资和工资級别 select salary,grade_level from employees e,job_grades j where e.`salary` between j.`lowest_sal` and j.`highest_sal` order by j.`grade_level`;
-
sql99
#查询员工的工资级别 select salary,grade_level from employees e inner join job_grades j on e.salary between j.lowest_sal and j.highest_sal; #查询每个工资級别的个数,并且按工资级别排序 SELECT 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;
自连接
-
sql92
#询员工名和上级的名称 SELECT e.`last_name`,e.`manager_id`,m.`last_name`,m.`manager_id` FROM employees e,employees m WHERE m.`manager_id` = e.`employee_id`;
-
sql99
#询员工名和上级的名称 SELECT e.`last_name`,e.`manager_id`,m.`last_name`,m.`manager_id`
b.外连接
特点:
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的,则显示匹配的值
如果从表中没有和它匹配的,则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接,left join左边的是主表
右外连接,right join右边的是主表
3、左外和右外交换两个表的顺序,可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
左外连接
sql99
-
#查询哪个部门没有员工 SELECT d.*,e.employee_id FROM departments d LEFT OUTER JOIN employees e ON d.`department_id` = e.`department_id` WHERE employee_id IS NULL;
右外连接
sql99
-
#查询哪个部门没有员工 SELECT d.*,e.employee_id FROM employees e right OUTER JOIN departments d ON d.`department_id` = e.`department_id` WHERE employee_id IS NULL;
c.交叉连接
sql99
-
select b.*,bo.* from beauty b cross join boys bo;
6.4.3总结
功能:sq199支持的较多
可读性:sq199实现连接条件和筛选条件的分离,可读性较高
进阶七:子查询
7.1含义
出现在其他语句中的select语句,称为子查询或内查询
外部的查询语句,称为主查询或外查询
7.2分类
7.2.1按子查询出现的位置
a.select后面:仅仅支持标量子查询
#查询每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*) FROM employees e
WHERE d.`department_id` = e.department_id
) 个数
FROM departments d;
#查询员工号=102的部门名
select (
select department_name
from departments d
inner join employees e
on d.department_id = e.department_id
where e.employee_id = 102
) 部门名;
b.from后面:支持表子查询
将子查询结果充当一张表,要求必须起别名
#查询每个部门的平均工资的工资等级
SELECT a.*,`grade_level`
FROM (
SELECT department_id,AVG(salary) p
FROM employees e
GROUP BY department_id
) a
INNER JOIN job_grades j
ON a.p BETWEEN j.`lowest_sal` AND j.`highest_sal`;
c.where (or) having后面:标量子查询 列子查询 行子查询(少)
特点
子查询放在小括号内
子查询一般放在条件的右侧
标量子查询,一般搭配着单行操作符使用 – < > <= >= <> =
列子查询,一般搭配多行操作符使用 – in、any/some、all
案例
-
标量子查询(单行子查询)
#谁的工资比Able高 #1.查询Able的工资 select salary from employees where salary = 'Able'; #2.查询员工的信息,满足salary的要求 select * from employees where salary > ( select salary from employees where salary = 'Abel' ); #返回job_id与141号员工相同,salary比143号员工多的员工姓名,job_id和工资 SELECT 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 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 MIN(salary),department_id FROM employees GROUP BY department_id SELECT MIN(salary) m,department_id FROM employees GROUP BY department_id HAVING m>( SELECT MIN(salary) FROM employees WHERE department_id = 50 );
-
列子查询(多行子查询)
#返回location id是1400或1700的部门中的所有员工姓名 select last_name from employees where department_id IN (=any)( SELECT department_id FROM departments WHERE location_id IN (1400,1700) ); #返回其它工种中比job_id为'IT_PROG'工种任一工资 低的员工的:工号、姓名、job_id以及salary SELECT employee_id,last_name,job_id,salary FROM employees WHERE job_id <> 'IT_PROG' AND salary < ( SELECT MAX(salary) FROM employees WHERE job_id = 'IT_PROG' ); #返回其它工种中比job_id为'IT_PROG'工种所有工资低的员工的:工号、姓名、job_id以及salary SELECT employee_id,last_name,job_id,salary FROM employees WHERE job_id <> 'IT_PROG' AND salary < all( SELECT salary FROM employees WHERE job_id = 'IT_PROG' );
-
行子查询(结果集)
#查询员工编号最小并且工资最高的员工信息 SELECT MIN(employee_id) FROM employees WHERE salary = ( SELECT MAX(salary) FROM employees ); #等于 SELECT * FROM employees WHERE employee_id = ( SELECT MIN(employee_id) FROM employees WHERE salary = ( SELECT MAX(salary) FROM employees ) );
d.exists后面(相关子查询)
语法
exists(完整的查询语句)
结果1或0 \ true or false
#查询有员工的部门名
select department_name
from departments d
where exists(
select *
from employees e
where d.department_id = e.department_id
);
select department_name
from departments d
where in(
select *
from employees e
where d.department_id = e.department_id
);
7.2.2按结果集的行列数不同
标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果集有一行多列)
表子查询(结果集一般为多行多列)
进阶八:分页查询
8.1应用场景
当要显示的数据,-页显示不全,需要分页提交sq1请求
8.2语法
select 查询列表
from 表
【join_type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit 【 offset】,size;
#offset要显示条目的起始索引(起始索引从0开始)
#size要显示的条目个数
8.3案例
#查询前五条员工信息
select * from employees limit 5,0;
#查询第11条一第25条
select * from employees limit 14,10;
#有奖金的员工信息,并且工资较高的前10名
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY commission_pct DESC
LIMIT 10;
8.4特点
limit语句放在查询语句的最后
公式:
要显示的页数page,每页的条目数size
select 查询列表
from 表
limit (page-1)*size,size;
8.5案例
#查询平均工资最低的部门信息
SELECT *
FROM departments
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1
);
#查询平均工资最低的部门信息和该部门的平均工资
SELECT d.*,aa.a
FROM departments d
JOIN (
SELECT AVG(salary) a,department_id
FROM employees
GROUP BY department_id
ORDER BY a
LIMIT 1
) aa
ON d.`department_id` = aa.department_id;
#查询平均工资高于公司平均工资的部门有哪些
SELECT AVG(salary) aa,department_id
FROM employees
GROUP BY department_id
HAVING aa>(
SELECT AVG(salary)
FROM employees
);
#查询出公司中所有manager 的详细信息.
select *
from employees
where employee_id in (
select manager_id
from employees
);
#各个部门中最高工资中最低的那个部门的最低工资是多少
select min(salary) from employees e
where e.department_id =(
SELECT department_id d_id FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1
);
#查询平均工资最高的部门的manager的详细信息:last_name,department__id,email,salary
SELECT last_name,department_id,email,salary
FROM employees
WHERE employee_id IN (
SELECT e.manager_id
FROM employees e
JOIN(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary) DESC
LIMIT 1
) m
ON e.department_id = m.department_id
);
-----------------------------OR------------------------------------
SELECT last_name,d.department_id,email,salary
FROM employees e
JOIN departments d
ON d.`manager_id` = e.`employee_id`
WHERE d.`department_id` = (
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary) DESC
LIMIT 1
);
进阶九:联合查询
9.1语法
union:联合 #合并:将多条查询语句的结果合并成一个结果
查询语句1
union
查询语句2
union
查询语句3
union
...
#应用场景:
#要查询的结果来自于多个表,且多个表没有直按的连接关系,但查询的信息一致时
要求多条查询语句的查询列数是一致的!
要求多条查询语句的查询的每一列的类型和顺序最好一致
union关键字默认去重,如果使用 union all可以包含重复项
#查询部门编号>90或邮箱包含a的员工信息
select * from employees where employee_is > 90 or email like '%a%';
------------------------OR-----------------------------
select * from employees where employee_is > 90
union
select * from employees where email like '%a%';
DML
数据操作语言
插入:insert
修改:update
删除:delete
插入语句
方式一:经典插入
1.1语法
insert into 表名(列名, ... ) value (值1, ... );
1.2案例
#插入的值的类型要与列的类型一致或兼容
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'wang家航','女','2000-04-23','18582800945',NULL,2);
#不可以为nul1的列必须插入值
#列的顺序可以调换
#列数和值的个数必须一-致
#可以省略列名,默认所有列,而且列的顺序和表中列的顺序--致
方式二:其他插入
2.1语法
insert into 表名
set 列名=值,列名=值, ...
2.2案例
insert into beauty
set id=1,name='王家航',sex='男',borndate='2000-10-12',phone='18582800945';
对比
1.方式一支持插入多行,方式二不支持
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'wang家航','女','2000-04-23','18582800945',NULL,2),
VALUES(13,'wang家航','女','2000-04-23','18582800945',NULL,2);
2.方式一支持子查询,方式二不支持
INSERT INTO beauty(id,NAME,phone)
SELECT 26,'宋苦','11809866';
修改语句
方式一.修改单表的记录
1.1语法
update 表名
set 列 = 新值,列 = 新值,列 = 新值 ...
where 筛选条件
1.1案例
UPDATE beauty SET phone = '16608040945'
WHERE id = 13;
UPDATE boys SET boyName = '王家航',userCP = 1000
WHERE id = 1;
方式二.修改多表的记录【补充】
2.1语法
sql92:
update 表1 别名,表2 别名
set 列=值, ...
where 连接条件
and 筛选条件;
sql99:
update 表1 别名F
inner | left | right join 表2 别名
on 连接条件
set 列=值, ...
where 筛选条件
2.2案例
#修改张无忌的女朋友的手机号为114
UPDATE beauty be
INNER JOIN boys bo
ON be.`boyfriend_id` = bo.`id`
SET be.phone = 144
WHERE be.boName = '王家航';
删除语句
方式一:delete
1.1语法
#1.单表的删除
delete from 表名 where 筛选条件;
#2.多表的删除【补充】
sql92:
delete 别名(要删除表的别名)
from 表1 别名,表2 别名
where 连接条件
and 筛选条件;
sql99:
delete 别名(要删除表的别名)
from 表1 别名
inner | left | right join 表2 别名
on 连接条件
where 筛选条件;
2.2案例
#删除手机号以9结尾的女神信息
DELETE FROM beauty WHERE phone LIKE '%9';
#删除鹿晗的女朋友的信息
delete be
from boys b
join beauty be
on b.`id` = be.`boyfriend_id`
where b.`boyName` = '鹿晗';
#删除黄晓明的信息以及他女朋友的信息
DELETE b,be
FROM beauty be
INNER JOIN boys b
ON be.`boyfriend_id` = b.`id`
WHERE b.`boyName` = '黄晓明';
方式二:truncate
2.1语法
truncate table 表名;
-----删除全部表数据--------
对比
delete可以加 where条件, truncate不能加
truncate删除,效率高一丢丢
假如要删除的表中有自增长列,如果用 delete删除后,再插入数据,自增长列的值从断点开始,
而 truncate删除后,再插入数据,自增长列的值从1开始
truncate删除没有返回值, delete删除有返回值
truncate删除不能回滚, delete删除可以回滚。
DDL
创建:create
修改:alter
删除:drop
库的管理
1.1语法
#创建库
create database 【 if not exists】 库名;
#修改库名
rename database 旧库名 to 新库名
#更改库的字符集
alter database 库名 character set 【gbk】;
#库的删除、
drop database 【 if exists】库名;
1.2案例
#创建库 books
create database if not exists books;
表的管理
2.1表的创建
#创建表
create table 表名(
列名 列的长度【(长度) 约束】,
列名 列的长度【(长度) 约束】,
列名 列的长度【(长度) 约束】,
...
列名 列的长度【(长度) 约束】
)
#创建图书表
create table book(
id int,#编号
bName varchar(20),#图书名
purice double,#价格
authorId int,#作者编号
publishDate datetime#出版日期
);
#创建作者表
CREATE TABLE author(
id INT,
au_name VARCHAR(20),
nation VARCHAR(20)
);
2.2表的修改
2.2.1语法
alter table 表名 add | drop | modify | change column 列名 【列类型 约束】;
-
修改列名
#改变 表 书 更改 列 旧名 新名 修改类型 alter table book change column publishdate pubDate datetime;
-
修改列的类型或约束
#改变 表 书 修改 列 字段名 时间戳 alter table book modify column pubDate timestamp;
-
添加新列
# 作者 添加 列 年度 类型 alter table author add column annual double; alter table 表名add colunn 列名类型[first | after 字段名];
-
删除列
# 作者 删除 列 年度 alter table author drop column annual;
-
修改表名
# 作者 改名 至 alter table author rename to book_author;
-
表的删除
drop table if exists book_author; #查看当前库下的表 show tables;
2.3通用写法
drop database if exists 旧库名;
create database 新库名;
drop table if exists 旧表名;
create table 表名(
列名 列的长度【(长度) 约束】,
列名 列的长度【(长度) 约束】,
...
列名 列的长度【(长度) 约束】
);
2.4表的复制
#仅仅复制表的结构
CREATE TABLE copy LIKE author;
#复制表的结构和数据
CREATE TABLE copy2
SELECT * FROM author;
#复制部分数据
CREATE TABLE copy3
SELECT * FROM author
WHERE nation = '中国';
#仅仅复制某些字段
CREATE TABLE copy4
SELECT id,au_name
FROM aution
WHERE 0;#(1=2)
2.5常见的数据类型
所选择的类型越简单越好,能保存最值的类型越小越好
2.5.1数值型
-
整形
整形类型 tinyint smallint mediumint int/integer bigint 字节 1 2 3 4 8 特点:
-
如果不设置无符号还是有符号,默认是有符号,如果想设置无符号,需要添加tunsigned关键字
-
如果插入的数值超出 了整型的范围,年报out of range异常,并且插入临界值
-
如果不设置长度,会有默认的长度
长度代表了显示的最大宽度,如果不够会用0在左边填充,但必须搭配zerofill使用
-
-
小数
定点数 dec(M,D) decimal(M,D) 浮点数 float(M,D) double(M,D)
特点:
- M:整数部位+小数部位
D:小数部位
如果超过范围,则插入临界值 - M和D都可以省略
如果是decimal,则M默认为10,D默认为0
如果是float和double,则会根据插入的数值的精度来决定精度 - 定点型的精确度较高,如果要求插入数值的精度较高如货币运算等则考虑使用
- M:整数部位+小数部位
2.5.2字符型
- 较短的文本:char、varchar
- 较长的文本:text、blob(较长的二进制数据)
- binary和varbinary用于保存较短的二进制
enum用于保存枚举
set用于保存集合
特点:
写法 | M的意思 | 特点 | 空间的耗费 | 效率 | |
---|---|---|---|---|---|
char | char(M) | 最大的字符数 | 固定长度的字符 | 比较耗费 | 高 |
varchar | varchar(M) | 最大的字符数 | 可变长度的字符 | 比较节省 | 低 |
2.5.3日期型
日期和时间类型 最小值 最大值
日期和时间类型 | 字节 | 最小值 | 最大值 |
---|---|---|---|
date | 4 | 1000-01-01 | 9999-12-31 |
datetime | 8 | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
timestamp | 4 | 19700101080001 | 2038年某个时刻 |
time | 3 | -838:59:59 | 838:59:59 |
year | 1 | 1901 | 2155 |
2.6约束
2.6.1含义
一种限制,用于限制表中的数据,为了最终保证表中的数据的准确性和可靠性
2.6.2分类
not null:非空,用于保证该字段的值不能为空
default:默认,用于保证该字段有默认值
PRIMARY KEY:主键,用于保证该字段的值具有唯一性,并且非空
unique:唯一,用于保证该字段的值具有唯-一性,可以为空
CHECK:检查约束[sql 中不支特]
FOREIGN KEY:外键,用于限制两个表的关系,用于保证该字段的值必须肤自于主表的关联列的值
show index from 表名;
2.6.3其他
添加约束的时机:
1.创建表时2.修改表时
约束的添加分类:
列级约束:
六大约束语法,上都支持,但外键约束没有效果
表级约束:
随了非空、默认,其他的都支持
2.6.4创建表时
-
创建表时添加列级约束
# 1.添加列级约束 create table stuinfo( id int primary key, stuName varchar(20) not null, gender char check(gender in ('男','女')), seat int unique, age int default 18, majorId int REFERENCES major(id) ); create table major( id int primary key, majorName varchar(20) not null unique ); # 直接在字段名和类型后面追加约束类型即可
-
创建表时添加表级约束
create table stuinfo( id int, stuName varchar(20), gender char, 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); ); # 语法:在各个字段的最下面 【 constraint 约束名】 约束类型(字段名)
-
通用
create table stuinfo( id int primary key, stuName varchar(20) not null, gender char check(gender in ('男','女')), seat int unique, age int default 18, constraint fk_stuinfo_major foreign key(majorId) REFERENCES major(id); );
-
主键和唯一的大对比
保证唯一性 是否允许为空 一个表中可以有几个 是否允许组合 主键 是 否 至多一个 是,不推荐 唯一 是 是 可以有多个 是,不推荐 -
外键
1、要求在从表设置外键关系
2、从表的外键列的类型和主表的关联列的类型要求一致或兼容,名称无要求
3、主表的关联列必须是一个key(一般是主键或唯一)
4、插入数据时,先插入主表,在插入从表 删除数据时,先删陈从表,再删除主表
2.6.5修改表时添加约束
-
语法
# 添加列级约束 alter table 表名 modify column 字段名 字段类型 新约束; # 添加表级约束 alter table 表名 add 【 constraint 约束名】 约束类型(字段名) 【外键的引用】;
-
案例
#改变 表 stuinfo 修改 列 stuname varchar(20) not null; 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 add foreign key(majorId) REFERENCES major(id);
2.6.6修改表时删除约束
-
案例
# 删除非空的束 ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR (20) NULL; # 删除默认约束 ALTER TABLE stuinfo MODIFY COLUMN age INT; # 删除主键 ALTER TABLE stuinfo DROP PRIMARY KEY; # 删除唯一 ALTER TABLE stuinfo DROP INDEX seat; # 删除外键 ALTER TABLE stuinfo DROP FOREIGN KEY majorid:
2.6.7标识列
-
含义
又称为自增长列
可以不用手动插入值,系统提供默认的序列值
-
关键字
AUTO_ INCREMENT - auto_increment
-
特点
- 标识列必须和主键搭配吗?不一定,但要求是一个key
- 一个表可以有几个标识列?至多一个!
- 标识列的类型只能是数值型
- 标识列可以通过SET auto_increment_increment=值;设置步长
-
修改表时设置标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
-
修改表时删除标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY;