#基础查询/*
select 查询列表 from 表名
类似于java中的打印东西
特点:查询列表可以是字段,常量值,表达式,函数
结果是一个虚拟的东西(和java中的输出一样,用完就没有了)
*/#一个字段,多个字段,全部SELECT last_name FROM employees;SELECT last_name,salary,email FROM employees;SELECT*FROM employees;#查询常量值SELECT100;SELECT'join';#查询表达式SELECT100%98;#查询函数SELECT VERSION()#这里用的是查询版本号#起别名 1便于理解,2如果有重名的旧可以用别名来区分---用AS或者用空格SELECT last_name AS 姓,first_name 名 FROM employees;#案例:查询salary 显示结果为out putSELECT salary AS'out put'FROM employees;#去重 destinctSELECTDISTINCT department_id FROM employees;#‘+’的作用 和java(运算符,拼接符)中有区别,只能当运算符/*
select 100+90;-------190
select '123'+90------213
select 'join'+90-----90
select null+90-------0
*/#追加两个字符串concatSELECT CONCAT(last_name,first_name)AS 姓名
FROM employees;#条件查询/*
select 查询列表 from 表名 where 筛选条件(相当于java中的if语句)
条件表达式 > < = != <> >= <=
逻辑表达式 && || ! and or not
模糊查询 like ,between and,in ,is null
*/#工资大于12000SELECT salary FROM employees WHERE salary>12000;#部门编号不等于90 的员工名和部门编号SELECT last_name,department_id FROM employees WHERE department_id<>90#查询工资在10000到20000之间的员工名,工资以及奖金SELECT
last_name,salary,commission_pct
FROM
employees
WHERE
salary>10000AND salary<20000;#查询部门编号不是在90~110之间,或者工资高于15000的员工信息SELECT*FROM employees
WHERENOT(department_id>=90AND department_id<=100)OR salary>=15000/*模糊查询 员工名中含有字母a的员工信息--第三个字母为e 第五个字母为a的
like 一般和通配符搭配使用 通配符有_(任意单个字符) % (任意多个字符)
*/SELECT*FROM employees
WHERE last_name LIKE'__n_a%';#查询第二个字母为 _ 的员工名(这里不太明白)SELECT*FROM employees
WHERE last_name LIKE'_$_%'ESCAPE'$';#查询员工编号在100到200之间的员工信息 between and 相当于是>=100 and <=120SELECT*FROM employees
WHERE employee_id BETWEEN100AND200;#查询某字段编号是It_PROG, AD_VP中的一个员工名和工种编号 inSELECT last_name,job_id
FROM employees
WHERE job_id IN('it_prog','ad_vp');#查询没有奖金的员工名和奖金率 is null,is not null 可以判断null值SELECT last_name,commission_pct
FROM employees
WHERE commission_pct ISNOTNULL;#安全等于 <=>SELECT last_name,commission_pct,salary
FROM employees
WHERE commission_pct <=>NULLOR salary<=>12000;/*
is null pk <=>
is null 仅仅可以判断null值,可读性较高 没有返回0,有返回1
select ifnull(commission_pct,0) from employees; 没有,返回指定值,有,返回特定值
<=> 既可以判断null值 可以判断数值 可读性较低
*/#表中有多个列,列又称为‘字段’,相当于java中的‘属性’#表中有多个行,相当于Java中的’对象‘
排序查询
#排序查序/*
select 查询列表 3
from 表 1
[where 删选条件] 2
order by 排序列表 asc|desc() 不写默认升序4
(order by)---支持单字段,多字段,表达式,函数,别名
*/#员工信息,工资从高到低SELECT*FROM employees ORDERBY salary;SELECT*FROM employees ORDERBY salary DESC;#查询部门编号>=90的员工信息,按入职时间的先后进行排序[添加筛选条件]SELECT*FROM employees
WHERE department_id>=90ORDERBY hiredate ASC;#按年薪的高低显示员工的信息和年薪[按表达式排序,按别名排序]SELECT*,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDERBY 年薪 DESC;#按姓名的长度显示员工的信息和工资[按函数排序]SELECT LENGTH(last_name) 字节长度,last_name,salary
FROM employees
ORDERBY 字节长度;#查询员工信息,要求按工资升序,再按员工编号降序[按多个字段排序]SELECT*FROM employees
ORDERBY salary,employee_id DESC;#练习#1查询员工姓名和部门号和年薪,按年薪降序,按姓名升序SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDERBY last_name,年薪 DESC;#2选择工资不在8000到17000的员工姓名和工资,按工资降序SELECT last_name,salary
FROM employees
WHERE salary NOTBETWEEN8000AND17000ORDERBY salary DESC;#3查询邮箱中包含e的员工信息,并先按照邮箱的字节数降序,再按部门号升序SELECT*,LENGTH(email) 字节数
FROM employees
WHERE email LIKE'%e%'ORDERBY 字节数 DESC,department_id;
/*
分组查询
语法: select 分组函数,列(要求出现在group by的后面)
from 表
【where 筛选条件】
group by 分组的列表
【order by 子句】
注意: 查询列表必须特殊,要求是分组函数和group by后出现的字段
特点: 1分组查询中的筛选条件分为两类
数据源 位置 关键字
分组前筛选 原始表 group by子句的前面 where
分组后筛选 分组后的结果集 group by子句的后面 having
分组函数做条件肯定是放在having子句中
能用分组前筛选的优先使用
2,group by 子句支撑单个字段分组,多个字段分组(内有顺序要求),表达式
3,也可以添加排序(排序放在整个分组查询的最后)
*/#引入:查询'每个部门'的平均工资(下面的方法使不上)SELECTAVG(salary)FROM employees;#1:查询每个工种的最高工资SELECTMAX(salary),job_id
FROM employees
GROUPBY job_id;#2:查询每个位置上的部门个数SELECTCOUNT(*),location_id
FROM departments
GROUPBY location_id;#1:查询邮箱中包含a字符的,每个部门的平均工资SELECTAVG(salary),department_id,email
FROM employees
WHERE email LIKE'%a%'GROUPBY department_id
#2:查询有奖金的每个领导手下员工的最高工资SELECTMAX(salary),manager_id
FROM employees
WHERE commission_pct ISNOTNULLGROUPBY manager_id
#3:查询那个部门的员工个数>2SELECTCOUNT(*),department_id
FROM employees
GROUPBY department_id;SELECTCOUNT(*),department_id
FROM employees
GROUPBY department_id
HAVINGCOUNT(*)>2;#4查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资SELECTMAX(salary),job_id
FROM employees
WHERE commission_pct ISNOTNULLGROUPBY job_id
HAVINGMAX(salary)>12000;#5 查询领导编号>102的每一个领导手下的最低工资>5000的领导编号是那个,以及其最低工资SELECTMIN(salary),manager_id
FROM employees
WHERE manager_id>102GROUPBY manager_id
HAVINGMIN(salary)>5000;#6按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数>5的有哪些SELECTCOUNT(*) c,LENGTH(last_name) l
FROM employees
GROUPBY l
HAVING c>5;#按多个字段分组#1:查询每个部门,每个工种的员工的平均工资SELECTAVG(salary),department_id,job_id
FROM employees
GROUPBY job_id,department_id;#其中添加排序#查询每个部门每个工种的员工的平均工资,并且按平均工资的高低显示SELECTAVG(salary),job_id,department_id
FROM employees
GROUPBY job_id,department_id
HAVINGAVG(salary)>10000ORDERBYAVG(salary)DESC;#练习#查询job_id的员工工资的最大值,最小值,平均值,总和,并按job_id升序SELECTMAX(salary),MIN(salary),AVG(salary),SUM(salary),job_id
FROM employees
GROUPBY job_id
ORDERBY job_id;#查询员工最高工资,最低工资的差距SELECTMAX(salary)-MIN(salary) difference
FROM employees
#查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内SELECTMIN(salary) manager_id
FROM employees
WHERE manager_id ISNOTNULLGROUPBY manager_id
HAVINGMIN(salary)>=6000;#4查询所有部门的编号,员工数量和工资平均值,并按平均工资降序SELECT department_id,COUNT(*),AVG(salary) a
FROM employees
GROUPBY department_id
ORDERBY a DESC#5选择具有各个job_id 的员工人数SELECTCOUNT(*),job_id
FROM employees
GROUPBY job_id;
连接查询
#连接查询/*
含义:又称多表查询,当查询的字段来自于多个表时,就会用到连接查询
因为没有添加有效的连接条件:笛卡尔乘积现象:表一:m行,表二:n行,结果m*n行
分类:按年代分类:
sq192标准:仅仅支持内连接
sq199标准(推荐):支持内连接+外连接(左外和右外)+交叉连接
按功能分类:
内连接:
等值连接
非等值连接
自连接
外连接:
左外连接
右外连接
全外连接
交叉连接
sq192标准 等值连接
1,多表连接的结果为多表的交集部分
2,n表连接,至少需要n-1个连接条件
3,多表的顺序没有要求
4,一般要给表起别名
5,可以搭配之前学的分组,排序,筛选
*/SELECT*FROM beauty;SELECT*FROM boys;#查询女神对应的男神名字SELECT NAME,boyName FROM boys,beauty
WHERE beauty.`boyfriend_id`=boys.`id`;#查询员工名和对应的部门名SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;#为表起别名:查询员工名,工种号,工种名(这道题里面有起别名的)/*
提高语句的简洁度
去们多个重名的字段
*/SELECT e.last_name,e.job_id,job_title
FROM employees e,jobs
WHERE e.`job_id`=jobs.`job_id`#查询有奖金的员工名,部门名SELECT last_name,department_name,commission_pct
FROM employees e,departments d
WHERE e.department_id=d.department_id
AND e.`commission_pct`ISNOTNULL#查询城市名中第二个字符为o的部门名和城市名SELECT department_name,city
FROM departments d,locations l
WHERE d.location_id=l.location_id
AND city LIKE'_o%'#可以添加分组#查询每个城市的部门个数SELECTCOUNT(*) 个数,city
FROM departments d,locations l
WHERE d.location_id=l.location_id
GROUPBY city;#添加分组#查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资(回头再想)SELECT department_name,d.manager_id,MIN(salary)FROM departments d,employees e
WHERE d.`department_id`=e.`department_id`AND commission_pct ISNOTNULLGROUPBY department_name,d.`manager_id`;#添加排序#查询每个工种的工种名和员工的个数,并且按员工个数降序SELECT job_title,COUNT(*)FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`GROUPBY job_title
ORDERBYCOUNT(*)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`AND city LIKE's%'ORDERBY department_name DESC;#2非等值连接#案例一:查询员工的工资和工资级别(没有执行出来)SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.`lowest_sal`AND g.`lowest_sal`AND g.`grade_level`='A';CREATETABLE job_grades
(grade_level VARCHAR(3),
lowest_sal INT,
highest_sal INT);INSERTINTO job_grades
VALUES('A',1000,2999);INSERTINTO job_grades
VALUES('B',3000,5999);INSERTINTO job_grades
VALUES('C',6000,9999);INSERTINTO job_grades
VALUES('D',10000,14999);INSERTINTO job_grades
VALUES('E',15000,24999);INSERTINTO job_grades
VALUES('F',25000,40000);#自链接#查询 员工名和上级的名称SELECT e.`last_name`,m.`manager_id`,m.`last_name`FROM employees e,employees m
WHERE e.`manager_id`=m.`manager_id`#练习#1显示员工表的最大工资,工资平均值SELECTMAX(salary),AVG(salary)FROM employees
#2查询员工表的employee_id,job_id,last_name,按department_id降序,salary升序SELECT employee_id,job_id,last_name
FROM employees
ORDERBY department_id DESC,salary;#3查询员工表的job_id中包含 a和e的,并且a在e的前面SELECT job_id
FROM employees
WHERE job_id LIKE'%a%e%'#4学生身份的关联SELECT s.name,g.grade,r.score
FROM student s,grade g,result r
WHERE s.id=r.studentNo
AND g.id=s.gradeid;#5当前日期,以及去前空格,截取子字符串函数SELECTNOW()SELECT TRIM(' 字符 a ') a;SELECT SUBSTR(str,startIndex)SELECT SUBSTR(str,startIndex,LENGTH)#6显示所有员工的姓名,部门号和部门名称 SELECT last_name,d.department_id,department_name
FROM employees e,departments d
WHERE e.department_id=d.department_id
#7查询90号部门员工的job_id 和90号部门的location_idSELECT job_id,location_id
FROM employees e,departments d
WHERE e.department_id=d.department_id
AND e.department_id=90#8选择所有有奖金的员工的last_name,location_id,citySELECT last_name,department_name,l.location_id,city
FROM employees e,departments d,locations l
WHERE e.department_id=d.department_id
AND d.location_id=l.location_id
AND commission_pct ISNOTNULL#9选择city在Toronto工作的员工的 last_name,job_id,department_id,department_nameSELECT last_name,job_id,d.department_id,department_name
FROM employees e,locations l,departments d
WHERE e.department_id=d.department_id
AND d.location_id=l.location_id
AND city LIKE'Toronto';#10查询每个工种,每个部门的部门名,工种名和最低工资SELECT department_name,job_title,MIN(salary)FROM employees e,departments d,jobs j
WHERE d.`department_id`=e.`department_id`AND e.`job_id`=j.`job_id`GROUPBY department_name,job_title
#11查询每个国家下的部门个数大于2的国家编号SELECTCOUNT(*),country_id
FROM departments d,locations l
WHERE d.`location_id`=l.location_id
GROUPBY country_id
HAVINGCOUNT(*)>2
sql199连接查询
/*
sql199语法
语法: select 查询列表
from 表1 别名【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序】
分类:
内连接:inner
外连接: 左外left【outer】
右外right【outer】
全外full【outer】
交叉连接:cross
内连接 select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件,
分类:等值,非等值,自连接
特点:
inner可以省略
筛选条件放在where后面,连接条件放在on后面,条理清晰
可以添加分组,排序,筛选
inner join连接和sql192中的等值连接效果是一样的,都是查询交集
*/#练习等值连接#1查询员工名,部门名SELECT last_name,department_name
FROM employees e
INNERJOIN departments d
ON e.`department_id`=d.`department_id`#2查询名字中包含e的员工名和工种名SELECT last_name,job_title
FROM employees e
INNERJOIN jobs j
ON e.`job_id`=j.job_id
WHERE e.last_name LIKE'%e%'#3查询部门个数>3的城市名和部门个数SELECTCOUNT(*),city
FROM locations l
INNERJOIN departments d
ON d.`location_id`=l.location_id
GROUPBY city
HAVINGCOUNT(*)>3#4查询那个部门的员工个数>3的部门名和员工个数,并按个数降序SELECTCOUNT(*),department_name
FROM employees e
INNERJOIN departments d
ON e.`department_id`=d.`department_id`GROUPBY department_name
HAVINGCOUNT(*)>3ORDERBYCOUNT(*)DESC;#5查询员工名,部门名,工种名,并按部门名降序SELECT last_name,department_name,job_title
FROM employees e
INNERJOIN departments d ON d.`department_id`=e.`department_id`INNERJOIN jobs j ON j.`job_id`=e.`job_id`ORDERBY department_name DESC;#外连接 和连接查询一样用between and#自连接SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.`manager_id`=m.`employee_id`#查询姓名中包含字符k的员工的名字,上级的名字SELECT e.`last_name`,m.`last_name`FROM employees e
JOIN employees m
ON e.`manager_id`=m.`employee_id`WHERE e.`last_name`LIKE'%k%'#外连接/*
应用场景:用于查询一个表中有,另一个表中没有
特点:
1.外连接的查询结果为主表中的所有记录
如果从表中有和他匹配的,则显示匹配的值,没有显示null
外连接查询结果=内连接结果+主表中有 而从表中没有的记录
2 左外连接:left join 左边的是主表
右外连接:right join 右边的是主表
3 左外和右外减缓两个表单顺序,可以实现同样的效果
4 全外连接=内连接的结果+表1中有但表2中么有的+表2中有但表1中没
*/#查询没有男朋友的女神名#左外SELECT b.name,bo.*FROM beauty b
LEFTOUTERJOIN boys bo
ON b.boyfriend_id=bo.id
WHERE bo.id ISNULL;#右外SELECT b.name,bo.*FROM boys bo
RIGHTOUTERJOIN beauty b
ON b.boyfriend_id=bo.id
WHERE bo.id ISNULL;#查询那个部门没有员工(没有员工的部门名)#左外SELECT d.*,e.employee_id
FROM departments d
LEFTOUTERJOIN employees e
ON e.department_id=d.department_id
WHERE e.employee_id ISNULL;#交叉连接 相当于笛卡尔积SELECT b.*,bo.*FROM beauty b
CROSSJOIN boys bo;#练习#查询编号>3的女神的男朋友信息,如果有则列出,没有用null填充SELECT b.id,b.name,bo.*FROM beauty b
LEFTOUTERJOIN boys bo
ON b.`boyfriend_id`=bo.`id`WHERE b.`id`>3;#查询那个城市没有部门SELECT city,d.*FROM departments d
RIGHTOUTERJOIN locations l
ON d.`location_id`=l.`location_id`WHERE d.`department_id`ISNULL#3查询部门名为SAL或IT的员工信息SELECT e.*,d.`department_name`FROM departments d
LEFTJOIN employees e
ON d.`department_id`=e.department_id
WHERE d.department_name IN('SAL','IT');SELECT*FROM departments
WHERE department_name IN('SAL','IT');
子查询
#子查询/*
出现在其他语句中的select语句,称之为子查询或内查询
分类:按子查询出现的位置
select后面:标量子查询
from 后面:表子查询
where或having后面:标量子查询,列子查询,行子查询(这里重点讲)
exists后面(相关子查询):表子查询
按结果集的行列数不同
标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果集有一行多列)
表子查询(结果集一般为多行多列)
*/#一,where或having后面:(重点讲)/*
标量子查询
列子查询
行子查询
特点: 子查询放在小括号内
子查询一般放在条件的右侧
标量子查询,一般搭配着单行操作符使用
< > = <= >= <> !=
列子查询,一般搭配着多行操作符使用
in,any/some,all
*/#谁的工资比Abel高#查询Abel的工资SELECT salary
FROM employees
WHERE last_name='Abel';SELECT*FROM employees
WHERE salary>(SELECT salary
FROM employees
WHERE last_name='Abel');#返回job_id与141号员工相同,salary比143号员工多的员工 姓名,job_id和工资SELECT salary
FROM employees
WHERE employee_id=143;SELECT job_id
FROM employees
WHERE employee_id=141;SELECT last_name,job_id,salary
FROM employees
WHERE salary>(SELECT salary
FROM employees
WHERE employee_id=143)AND job_id=(SELECT job_id
FROM employees
WHERE employee_id=141);#返回公司工资最少的员工的last_name,job_id,salarySELECTMIN(salary)FROM employees;SELECT last_name,job_id,salary
FROM employees
WHERE salary=(SELECTMIN(salary)FROM employees
);#查询最低工资大于50号部门最低工资的部门id和其最低工资#50号部门的最低工资SELECTMIN(salary)FROM employees
WHERE department_id=50;#其他部门的最低工资SELECTMIN(salary),department_id
FROM employees
GROUPBY department_id
#最后总结SELECTMIN(salary),department_id
FROM employees
GROUPBY department_id
HAVINGMIN(salary)>(SELECTMIN(salary)FROM employees
WHERE department_id=50);#多行子查询 IN/NOT IN,ANY/SOME,ALL#返回location_id是1400或1700的部门中的所有员工姓名SELECT department_id
FROM departments
WHERE location_id IN(1400,1700);SELECT last_name
FROM employees
WHERE department_id IN(# =ANY / <>ALLSELECT department_id
FROM departments
WHERE location_id IN(1400,1700));#返回其他部门中比job_id为’IT_PROG’部门任意工资低的员工的员工号,姓名,job_id,salarySELECTDISTINCT salary
FROM employees
WHERE job_id='IT_PROG'SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ALL(SELECTDISTINCT salary
FROM employees
WHERE job_id='IT_PROG')AND job_id<>'IT_PROG';#查询员工编号最小并且工资最高的员工信息(行子查询)SELECT*FROM employees
WHERE(employee_id,salary)=(SELECTMIN(employee_id),MAX(salary)FROM employees
);#二,select后面#查询每个部门的员工个数(不太懂后面那个方法)SELECTCOUNT(*),department_id
FROM employees
GROUPBY department_id
SELECT d.*,(SELECTCOUNT(*)FROM employees e
WHERE e.`department_id`=d.department_id
)个数
FROM departments d;#2,查询员工号=102的部门名SELECT department_name
FROM departments d,employees e
WHERE d.`department_id`=e.`department_id`AND employee_id=102;SELECT(SELECT department_name
FROM departments d
INNERJOIN employees e
ON d.department_id=e.department_id
WHERE e.employee_id=102)部门名;SELECT*FROM job_grades;#这里是在from后面展开的#四 exists后面(相关子查询) 结果只有0或1SELECTEXISTS(SELECT employee_id FROM employees);#查询有员工的部门名SELECT department_name
FROM departments d
WHEREEXISTS(SELECT*FROM employees e
WHERE d.`department_id`=e.`department_id`);SELECT department_name
FROM departments d
WHERE d.`department_id`IN(SELECT department_id
FROM employees
);#查询没有女朋友的男神信息#inSELECT bo.*FROM boys bo
WHERE bo.id NOTIN(SELECT boyfriend_id
FROM beauty
)#existsSELECT bo.*FROM boys bo
WHERENOTEXISTS(SELECT boyfriend_id
FROM beauty b
WHERE bo.id=b.`boyfriend_id`);#总练习#1查询和Zlotkey相同部门的员工姓名和工资#查询Zlotkey的部门SELECT department_id
FROM employees
WHERE last_name='Zlotkey';#下一步查询相同部门的员工姓名和工资SELECT last_name,salary
FROM employees
WHERE department_id=(SELECT department_id
FROM employees
WHERE last_name='Zlotkey');#2查询工资比公司平均工资高的员工的员工号,姓名,工资#工资平均工资SELECTAVG(salary)FROM employees
#比平均工资高的员工的~~~SELECT last_name,employee_id,salary
FROM employees
WHERE salary>(SELECTAVG(salary)FROM employees
)#查询各部门中工资比本部门平均工资高的员工的员工号,姓名,工资(不太明白待会看看视频)SELECTAVG(salary),department_id
FROM employees
GROUPBY department_id;#连接上面的结果集和employees表,进行筛选SELECT e.department_id,last_name,salary
FROM employees e
INNERJOIN(SELECTAVG(salary) ag,department_id
FROM employees
GROUPBY department_id
)ag_dep
ON e.`department_id`=ag_dep.department_id
WHERE salary>ag_dep.ag;#查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名SELECTDISTINCT department_id
FROM employees
WHERE last_name LIKE'%u%'#查询部门号=上面中的任意一个的员工号和姓名SELECT last_name,employee_id
FROM employees
WHERE department_id IN(SELECTDISTINCT department_id
FROM employees
WHERE last_name LIKE'%u%')#查询在部门的location_id为1700的部门工作的员工的员工号#查询location_id为1700的部门SELECT department_id
FROM departments
WHERE location_id=1700#查询部门号=上面中的任意一个的员工号SELECT employee_id
FROM employees
WHERE department_id=ANY(SELECT department_id
FROM departments
WHERE location_id=1700)#查询管理者是K_ing的员工姓名和工资SELECT employee_id
FROM employees
WHERE last_name='K_ing'SELECT last_name,salary
FROM employees
WHERE manager_id IN(# =ANYSELECT employee_id
FROM employees
WHERE last_name='K_ing')#查询工资最高的员工姓名,要求first_name和last_name显示为一列,列名为姓.名SELECTMAX(salary)FROM employees
SELECT CONCAT(first_name,last_name)'姓.名'FROM employees
WHERE salary=(SELECTMAX(salary)FROM employees
)#子查询经典案例#1查询工资最低的员工信息:last_name,salarySELECTMIN(salary)FROM employees
SELECT last_name,salary
FROM employees
WHERE salary=(SELECTMIN(salary)FROM employees
)#2查询平均工资最低的部门信息#方式一#1各部门平均工资SELECTAVG(salary),department_id
FROM employees
GROUPBY department_id
#2平均工资中最低的工资SELECTMIN(ag)FROM(SELECTAVG(salary) ag,department_id
FROM employees
GROUPBY department_id
)ag_dep
#3那个部门平均工资=2SELECTAVG(salary),department_id
FROM employees
GROUPBY department_id
HAVINGAVG(salary)=(SELECTMIN(ag)FROM(SELECTAVG(salary) ag,department_id
FROM employees
GROUPBY department_id
)ag_dep
)#4打印全部信息SELECT*FROM departments
WHERE department_id=(SELECT department_id
FROM employees
GROUPBY department_id
HAVINGAVG(salary)=(SELECTMIN(ag)FROM(SELECTAVG(salary) ag,department_id
FROM employees
GROUPBY department_id
)ag_dep
))#方式二 (利用排序,和限制limit解决更方便)SELECTAVG(salary),department_id
FROM employees
GROUPBY department_id
ORDERBYAVG(salary)LIMIT1;SELECT*FROM departments
WHERE department_id=(SELECT department_id
FROM employees
GROUPBY department_id
ORDERBYAVG(salary)LIMIT1)#3查询平均工资最低的部门信息和改部门的平均工资SELECTAVG(salary),department_id
FROM employees
GROUPBY department_id
ORDERBYAVG(salary)LIMIT1;#内连接(连接起来就有本部门没有的信息了)SELECT d.*,ag
FROM departments d
JOIN(SELECTAVG(salary) ag,department_id
FROM employees
GROUPBY department_id
ORDERBYAVG(salary)LIMIT1)ag_dep
ON d.department_id=ag_dep.department_id
#4查询平均工资最高的job信息SELECTAVG(salary),job_id
FROM employees
GROUPBY job_id
ORDERBYAVG(salary)DESCLIMIT1;SELECT*FROM jobs
WHERE job_id=(SELECT job_id
FROM employees
GROUPBY job_id
ORDERBYAVG(salary)DESCLIMIT1)#5查询平均工资高于公司平均工资的部门有那些SELECT department_name
FROM employees e
JOIN departments d
ON e.department_id=d.department_id
GROUPBY e.department_id
HAVINGAVG(salary)>(SELECTAVG(salary)FROM employees
)#6查询出公司中所有manager的详细信息SELECTDISTINCT manager_id
FROM employees
SELECT*FROM employees
WHERE employee_id IN(#这里不可以用manage_idSELECT manager_id
FROM employees
)#7各个部门中 最高工资中最低的那个部门的最低工资是多少#部门找到了SELECTMAX(salary)FROM employees
GROUPBY department_id
ORDERBYMAX(salary)LIMIT1#该部门中的最低工资SELECTMIN(salary)FROM employees
WHERE department_id=(SELECT department_id FROM employees
GROUPBY department_id
ORDERBYMAX(salary)LIMIT1)#8查询平均工资最高的部门的 manager 的详细信息:last_name,department_id,email,salarySELECTAVG(salary),department_id
FROM employees
GROUPBY department_id
ORDERBYAVG(salary)DESCLIMIT1;SELECT e.last_name,e.department_id,e.email,e.salary
FROM employees e
JOIN departments d
ON d.`manager_id`=e.`employee_id`#从这里连接--整出来manager的信息WHERE e.department_id=(SELECT department_id
FROM employees
GROUPBY department_id
ORDERBYAVG(salary)DESCLIMIT1)
分页查询
#分页查询/*
应用场景:当要显示的数据一页放不下的时候,就需要分页提交sql请求
语法: select 查询列表 7
from 表一 1
join type join 表二 2
on 连接条件 3
where 筛选条件 4
group by 分组条件 5
having 分组后的筛选 6
order by 排序的字段 8
limit 【offset】,size; 9
offset 要显示条目的起始索引(起始索引从0开始)
size(要显示的条目数)
特点:
limit 放在查询语句最后
公式:要显示的页数page 每页的条目数size
select 查询列表
from 表
limit (page-1)*size,size;
size=10
page
1 0
2 10
3 20
*/#案例:查询前五条员工的信息(下面这两条都一样)SELECT*FROM employees LIMIT0,5;SELECT*FROM employees LIMIT5;#查询第11条--第25条SELECT*FROM employees LIMIT10,15;#有奖金的员工信息,并且工资较高的前10名显示出来SELECT*FROM employees
WHERE commission_pct ISNOTNULLORDERBY salary DESCLIMIT10;
union联合查询
#联合查询/*
union 联合 合并,将多条查询语句的结果合并成一个结果
语法:
查询语句1
union
查询语句2
union
.....
应用场景
要查询的结果来自于多个表,且多个表没有直接的连接关系,但查询的信息要求一致
特点: 1要求多条查询的语句的查询列数是一致的!
2要求多条查询语句每一列 类型一一对应
3union 关键字默认(有去重功能) 如果使用union all 可以包含重复项
*/#引入案例:查询部门编号>90或邮箱包含a的员工信息SELECT*FROM employees WHERE department_id>90OR`email`LIKE'%a%'#应用联合查询SELECT*FROM employees WHERE department_id>90UNIONSELECT*FROM employees WHERE`email`LIKE'%a%';#联合的应用SELECT id,cname,csex FROM t_ca WHERE csex='男'UNIONSELECT t_id,tname,tGender FROM t_ca WHERE csex='male';
查询的最终练习
#1查询每个专业的学生人数SELECTCOUNT(*),majorid
FROM student
GROUPBY majorid;#2查询参加考试的学生中,每个学生的平均分,最高分SELECTMAX(score),AVG(score),studentno
FROM result
GROUPBY studentno;#3查询姓张的每个学生的最低分大于60的学号,姓名SELECT s.studentno,s.studentname,MIN(score)FROM student s
JOIN result r
ON s.`studentno`=r.`studentno`WHERE s.`studentname`LIKE'张%'GROUPBY s.`studentno`HAVINGMIN(score)>60;#4查询生日在’1998-1-1‘后的学生姓名,专业名称SELECT studentname,majorname
FROM student s
JOIN major m
ON s.`majorid`=m.`majorid`WHERE DATEDIFF(borndate,'1998-1-1')>0;#5查询每个专业的男生人数和女生人数分别是多少#方式一SELECTCOUNT(*),sex,majorid
FROM student s
GROUPBY sex,majorid;#方式二(回头再看看)SELECT majorid,(SELECTCOUNT(*)FROM student WHERE sex='男'AND majorid=s.majorid) 男,(SELECTCOUNT(*)FROM student WHERE sex='女'AND majorid=s.majorid) 女
FROM student s
GROUPBY majorid;#6查询专业和张翠山一样的学生的最低分#获得张翠山的专业SELECT majorid
FROM student
WHERE studentname='张翠山'#专业一样(我的简便方法)SELECTMIN(score)FROM student s
JOIN result r
ON s.studentno=r.studentno
WHERE majorid=(SELECT majorid
FROM student
WHERE studentname='张翠山')#老师的笨方法SELECT studentno
FROM student
WHERE majorid=(SELECT majorid
FROM student
WHERE studentname='张翠山')SELECTMIN(score)FROM result
WHERE studentno IN(SELECT studentno
FROM student
WHERE majorid=(SELECT majorid
FROM student
WHERE studentname='张翠山'))#7查询大于60分的学生的姓名,密码,专业名SELECT studentname,loginpwd,majorname
FROM student s
JOIN major m ON s.majorid=m.majorid
JOIN result r ON r.studentno=s.studentno
WHERE score>60;#8按邮箱的位数分组,查询每组的学生人数SELECTCOUNT(*),LENGTH(email) l
FROM student
GROUPBY l
#9查询学生名,专业名,分数SELECT studentname,majorname,score
FROM student s
JOIN result r ON s.studentno=r.studentno
JOIN major m ON m.majorid=s.majorid
#10查询那个专业没有学生,分别用左右链接实现SELECT m.majorid,m.majorname,s.studentno
FROM major m
LEFTJOIN student s ON m.majorid=s.majorid
WHERE s.studentno ISNULL#查询没有成绩单学生人数SELECTCOUNT(*)FROM student s
LEFTJOIN result r ON r.studentno=s.studentno
WHERE r.id ISNULL
基本查询#基础查询/*select 查询列表 from 表名类似于java中的打印东西特点:查询列表可以是字段,常量值,表达式,函数 结果是一个虚拟的东西(和java中的输出一样,用完就没有了)*/#一个字段,多个字段,全部SELECT last_name FROM employees;SELECT last_name,salary,email FROM employees;SELECT * FROM employees;#查询常量值SELECT 100;SELECT 'join