sql-DQL语句

#---------------------------------------------------------------------------------------------------------
#进阶1：基础查询

/*
语法：select
select 查询列表
from 表名

特点：
1、查询的列表可以是：表中的字段、常量值、表达式、函数
2、查询的结果是一个虚拟表格
*/
#前置条件：启用指定的数据库
USE myemployees;
#1、查询表中的单个字段
SELECT last_name 
FROM employees;
#2、查询表中的多个字段(查询的字段顺序和个数可以自己决定)
SELECT 
last_name,
salary,
email 
FROM employees;
#3、查询表中的所有字段(直接双击表名插入,逗号隔开；或者直接全部查看）
/*
select 
`employee_id`,
`first_name`,
。。
from employees;（根据插入的顺序，可以决定列的顺序）
或者
select*from employees;(*星号代表所有字段)，此方法所有列的顺序和原表一样
*/
SELECT*
FROM employees;

#4.查询常量值
SELECT 100;
SELECT 'john';
#5.查询表达式
SELECT 100*98;
#6.查询函数/方法
SELECT VERSION();

#7.为字段起别名(提高可读性；如果要查询的字段有重名的情况，使用别名可以区分开来)
SELECT 100%98 AS 结果;
SELECT last_name AS 姓,first_name AS 名 FROM employees;
#或者
SELECT last_name 姓,first_name 名 FROM employees;
#若想查询salary，显示别名结果为 out put(有空格等特殊符号，加上双引号)
SELECT salary "out put" FROM employees;

#8、去重
#案例：查询员工表中涉及到的所有部门编号
SELECT department_id FROM employees;         
#把显示的部门编号去重(加上distinct即可)
SELECT DISTINCT department_id FROM employees;

#9、+号的作用：查询员工名和姓，将其连接成一个字段,并显示为 姓名
/*在mysql中，+号只有一个功能：运算符；
在Java中既可以运算符，也可以连接符
MySQL中，select 100+90;  两个操作数都为数值型，则做加法运算
         select "123"+90; 若其中一个为字符型，试图将字符型数值转换成数值型
                           转换成功，则继续加法运算
         select "Jane"+90; 转换失败，则将字符型数值转换成0
         select null+100;  只要其中一方为null，则结果一定为null
*/
#所以在mysql中的连接符为：concat(一般连接中间没空隙，可以用空格或其他字符加上)
SELECT 
CONCAT(last_name," ",first_name) AS 姓名 
FROM employees;

DESC departments;
SELECT* FROM departments;
SELECT DISTINCT job_id 
FROM employees;

#案例：显示employees所有列，用逗号连接，设置列名为out_put     use myemployees;  
SELECT 
CONCAT(employee_id,',',first_name,',',last_name,',',email,',',phone_number,',',job_id,',',salary,',',`commission_pct`,',',manager_id,',',department_id,',',
        hiredate) AS "out_put"
  FROM 
  employees;
#因为其中有一项`commission_pct`即存在null值，又有有效值，因此会让结果全为null；如何解决：select ifnull
#select ifnull(`commission_pct`,0) as 奖金率,`commission_pct` from `employees`;
SELECT IFNULL(`commission_pct`,0) AS 奖金率,`commission_pct` 
FROM `employees`;
#所以可以将此函数套用进原代码中，即：
SELECT 
CONCAT(employee_id,',',
        first_name,',',last_name,',',
        email,',',
        phone_number,',',
        job_id,',',salary,',',
        IFNULL (commission_pct,0),',',
        manager_id,',',
        department_id,',',
        hiredate) AS "out_put"
  FROM 
  employees;
  
  
  
  
  
 #------------------------------------------------------------------------------------------------------
#进阶2：条件查询 

/*
语法：
select 查询列表
from 表名
where 筛选条件；

执行顺序：
先执行from子句
再执行where子句
最后select子句
 select last_name,first_name from employees where salary>20000;
 
 特点：
 1、按条件（关系）表达式筛选：由关系运算符组成的式子
 关系运算符：<； >； =；<>或者！=(不等于)；
 2、按逻辑表达式筛选:由逻辑运算符组成的式子
 逻辑运算符：and or not 。也可以使用：&&；||;！；但不建议
 3、模糊查询：
 （not）like ;（not）in;（not）between and（闭区间）;is （not） null
 
*/
#------------------------------------------------------
#一、按关系表达式筛选：<； >； =；<>或者！=(不等于)
#案例1：查询部门编号不是100的员工信息   select department_id from employees where department_id <>100;
SELECT * 
FROM employees 
WHERE `department_id`<>100;
#查询工资小于15000的员工姓名和工资情况
SELECT `first_name`,`last_name`,salary 
FROM employees 
WHERE salary<15000;

#-------------------------------------------------------
#二、按逻辑表达式筛选：and or not 。也可以使用：&&；||;！；但不建议
#案例1：查询部门编号不是50-100之间的员工姓名、部门编号、邮箱
#方式一：不推荐
SELECT First_name,department_id,email FROM employees WHERE department_id<50 OR department_id>100;
#方式二：推荐
SELECT First_name,department_id,email 
FROM employees 
WHERE NOT(department_id>=50 AND department_id<=100);
#案例2：查询奖金率>0.03或员工编号在60-110之间的员工信息
SELECT *
FROM employees
WHERE `commission_pct`>0.03 OR (`employee_id`>=60 AND`employee_id`<=110);


#-------------------------------------------------------
#三、模糊查询：like ;in;between and;is null

#like：
/*功能：一般和通配符搭配使用，对字符型数据进行部分匹配查询
        常见通配符：_(任意单个字符)；%（任意多个，包括0个字符）
 like/not like
  */    
#查询姓名中第三个字符为a(__a%)的员工信息;倒数第二个字符为a（%a_）
SELECT *
FROM employees
WHERE last_name LIKE '__a%'
#查询姓名中第二个字符为_(_\_%)的员工信息:使用转义字符\（Java中）或者自己创建转义字符：escape
SELECT *
FROM employees
WHERE last_name LIKE '_\_%'
#或者:用escape把某个字符设置为转义字符，选择的字符最好方便辨认
SELECT *
FROM employees
WHERE last_name LIKE '_$_%'ESCAPE '$';


#in
/*
功能：查询某个字段的值是否属于指定列表之类
a in（常量值1，常量值2，常量值3,....）
a not in（常量值1，常量值2，常量值3,....）  
in/not in 
*/
#模糊查询部门编号是30/50/90的员工名、部门编号（推荐）
SELECT last_name,department_id
FROM employees
WHERE department_id IN(30,50,90);
#或者使用逻辑表达式查询
SELECT last_name,department_id
FROM employees
WHERE department_id=30 
OR department_id=50 
OR department_id=90);
#查询工种编号不是SH_CLERK或IT_PROG的员工信息(在sql中，数值型的常量值不要单引号，非数值型的常量值需要单引号)
SELECT *
FROM employees
WHERE job_id NOT IN('SH_CLERK','IT_PROG');


#between and
/*
功能：判断某个字段的值是否介于XXX之间
between and/not between and
*/
#查询部门编号是30-90之间的部门编号和员工姓名(较小的数字写在前面)
SELECT last_name,department_id
FROM employees
WHERE department_id BETWEEN 30 AND 90;
#查询年薪不是在10万到20万之间的员工姓名，工资，年薪：月薪*12*（1+奖金率）:ifnull之间不要空格
SELECT last_name,salary,salary*12*(1+IFNULL(`commission_pct`,0))AS 年薪
FROM employees
WHERE salary*12*(1+IFNULL(`commission_pct`,0)) NOT BETWEEN 100000 AND 200000;
#或者
SELECT last_name,salary,salary*12*(1+IFNULL(`commission_pct`,0))AS 年薪
FROM employees
HAVING 年薪 NOT BETWEEN 100000 AND 200000;

#is null/is not null
#补充：不能用=null，=等号只能判断普通的内容；is只能判断null值；<=>安全等于，两者都可以判
#查询有奖金的员工信息
SELECT*
FROM employees
WHERE `commission_pct`IS NOT NULL;
#查询没有奖金的员工信息：使用安全等于<=>（缺点是阅读性差）
SELECT*
FROM employees
WHERE `commission_pct`<=> NULL;

#查询姓名中有a和e的员工姓名
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%'AND last_name LIKE '%e%';
#或者
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';





#------------------------------------------
#练习题
SELECT last_name,department_id,salary*12*(1+IFNULL(`commission_pct`,0))年薪
FROM employees
ORDER BY 年薪 DESC,last_name;

SELECT * ,LENGTH(email)邮箱长度
FROM employees
WHERE email LIKE '%e%'
ORDER BY 邮箱长度 DESC ,department_id;#先通过邮箱长度降序，在邮箱长度一样的范围中，用部门编号升序


SELECT * ,LENGTH(email)邮箱长度
FROM employees
WHERE email LIKE '%e%'
ORDER BY department_id,邮箱长度 DESC;#先通过部门编号升序，在部门编号一样时，用邮箱长度降序



#------------------------------------------------------------------------------------------------------
#进阶3：排序查询

/*
语法：
select查询列表
from 表名
【where 筛选条件】可选择
order by 排序列表（可以多个）

执行顺序：
from子句
where子句
select子句
order by子句（列表查出来之后，再进行排序）

特点：
排序列表可以是单个字段，多个字段、表达式、函数、列数，等
升序：通过asc（默认行为）
降序：通过desc
*/

#二、按表达式排序（有奖金的员工，按年薪降序）-->可以在select后面再加列，让年薪列也显示出来
#此时因为where已经排除奖金率为空的情况，所以不需要在select行的salary再用ifnull；或者直接不要where，则添加ifnull
SELECT*,salary*12*(1+commission_pct)AS 年薪
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary*12*(1+commission_pct)DESC;
#或者：直接按别名排序,(不能在where处直接使用别名，因为代码执行顺序是先where再select再order by)
SELECT*,salary*12*(1+commission_pct)AS 年薪
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY 年薪 DESC;

#三、按函数的结果排序
#根据姓名的字符个数进行倒叙:length();此函数获取字符长度
SELECT LENGTH(last_name)AS 姓名长度,last_name
FROM employees
ORDER BY 姓名长度 DESC;

#补充；按列数排序（例如：所有信息按第二例数据排序）
SELECT*
FROM  employees
ORDER BY 2 DESC;  #2 表示第二列；实际中用得较少




#--------------------------------------------------------------------------------------------------------------
#进阶4、常见函数 （主要是单行函数）
/*
函数：类似Java中的方法，即为了解决某个问题，将编写的一系列的命令集合封装在一起，对外仅仅暴露方法名，供外部调用

1、自定义方法（函数）
2、调用方法（函数）：明确函数名和函数功能  

常见函数：单行函数（对单行操作）和分组（聚合、统计）函数（对整个一列、一组的数据操作)
单行函数：
       字符函数
       数学函数
       日期函数
       流程控制函数
*/

#一、字符函数  --------------------------------------------------------

#concat（）拼接字符；length（）获取字节长度；char_lengh()获取字符长度
SELECT LENGTH('hello,郭襄');  #此处结果为12。若采用UTF-8编码，1汉字（包括中文状态下的符号）为3字节；若采用GBK编码，1汉字为2字节
SELECT CHAR_LENGTH('hello,郭襄'); #此处结果为8。即字符的个数

# substring 截取子串（与起始索引有关）格式为：SUBSTR(字符串str,起始索引index,截取的字符长度数) 或 SUBSTR（str，index）默认截取起始索引后所有字符
SELECT SUBSTR('孔雀东南飞',1,3); #SQL中，起始索引从1开始；括号中1代表从字符串开头截取，3代表截取的字符个数（而不代表结束索引）
SELECT SUBSTR('孔雀东南飞五里一徘徊',7);   #默认截取起始索引开始的之后所有字符

# instring 获取字符第一次出现的索引
SELECT INSTR('邻家有女初长成aaa初长成a初长成','初长成'); 

#INSERT(str, index , len, instr) 将字符串str从第index位置开始，len个字符长的子串替换为字符串instr
SELECT INSERT('we are the champion',12,8,'winner'); #一个空格也算一个字符

# trim 除去字符串前后指定的字符，默认是除去空格：把字符串看作一个整体，不会去除字符串中间的格式
SELECT TRIM('君  王 侧     ') 诗名 ; #去除前后空格
SELECT TRIM('唐代'FROM'唐代诗人李白来自唐代') 诗人; #去除前后指定字符

# LPAD/RPAD：LPAD/RPAD (str, len, pad) 表示：用字符串pad对str最左/右进行填充，直到str的字符长度为len字符
SELECT LPAD('秦始皇',10,'千古一帝');
SELECT RPAD('秦始皇',10,'千古一帝');
SELECT LPAD('秦始皇',1,'千古一帝'); #控制了字符长度为1，因此只显示一个
SELECT LPAD('秦始皇',4,'千古一帝'); #控制了字符长度为4，显示一个“千”，优先显示原字符串
#upper/lower 大小写
#案例：查询员工表中的姓名，格式为：姓的首字符大写，其他小写，名的所有字符大写，并且姓和名之间用下划线 分割，最后起别名”OUTPUT“
#先列出单个条件
SELECT UPPER(SUBSTR(last_name,1,1)) 
SELECT LOWER(SUBSTR(last_name,1)) 
SELECT UPPER(first_name) 
#再组合
SELECT CONCAT(UPPER(first_name),'_',UPPER(SUBSTR(last_name,1,1)),LOWER(SUBSTR(last_name,1))) 'OUTPUT'
FROM employees
ORDER BY first_name;
#或者
SELECT CONCAT(UPPER(first_name),'_',UPPER(LEFT(last_name,1)),LOWER(SUBSTR(last_name,1))) 'OUTPUT'
FROM employees
ORDER BY first_name;

#strcmp 比较两个字符大小:
SELECT STRCMP('abc','aaa'); #比较顺序：一个一个比，第一个一样才会执行下一个；b在字典中靠后，b>a,返回+1
SELECT STRCMP('abc','abe'); #返回-1；若两边相等，返回0

#left/right 从左边/右边截取字串   left/right(str,len)
SELECT LEFT('自挂东南枝',4); #自挂东南
SELECT RIGHT('自挂东南枝',4); #挂东南枝

#二、数学函数  -------------------------------------------------------------

#abs() 返回数值的绝对值
SELECT ABS(-101);

#CEIL（x）向上取整：返回大于等于x的最小整数
SELECT CEIL(-10.2);

#floor（x）向下取整：返回小于等于x的最大整数
SELECT FLOOR(10.9);

#ROUND(x,y) 四舍五入：返回参数x的四舍五入的有y位小数的值
SELECT ROUND(2.952324);
SELECT ROUND(3.325,2);
SELECT ROUND(5.32,-1);
#TRUNCATE(x,y) 截断取整：返回数字x截断为y位小数的结果
SELECT TRUNCATE(1.87653728,2);

#mod 取余    语法：MOD(x,y)  返回x除以y的余数（模）
SELECT MOD(-20,3);
SELECT MOD(10,-3);

# a%b =a - (int)a/b*b  取余，被除数的正负决定结果的正负    例如：-10%3= -10-(-10)/3*3= -1
SELECT -10%3;  #-1
SELECT 10%3;   #1
SELECT 10%-3;  #1
SELECT -10%-3; #-1

#三、日期函数  -------------------------------------------------------------

#now 查询系统当前时间 :日期+具体时间
SELECT NOW();

#CURDATE() 或 CURRENT_DATE() 返回当前日期：只返回日期 ；返回当前时间：CURTIME() 或 CURRENT_TIME()
SELECT CURDATE();  #返回当前日期
SELECT CURTIME();  #返回当前时间

#DATEDIFF(date1,date2) 返回date1 - date2的日期间隔(天数) ; TIMEDIFF(time1, time2) 返回time1 - time2的时间间隔
SELECT DATEDIFF('1997-2-13',CURDATE());  #日期间隔(天数) 
SELECT TIMEDIFF('7:30:23',CURTIME());    #时间间隔(小时)

#DATE_FORMAT(datetime ,fmt) 按照字符串fmt的格式转换日期datetime值 （具体格式在笔记中找）
SELECT DATE_FORMAT('1998-7-16','%Y年%m月%d日 %H小时%i分钟%s秒') 出生日期; #1998年07月16日 00小时00分钟00秒
#查询员工入职日期，格式如上
SELECT hiredate,DATE_FORMAT(hiredate,'%Y年%m月%d日 %H小时%i分钟%s秒') 入职日期
FROM employees;

#STR_TO_DATE(str, fmt)   按照fmt格式解析string字符串为日期类型
SELECT STR_TO_DATE('3/15 1998','%m/%d %Y');
#查询入职日期小于（早于）该日期的员工信息
SELECT * 
FROM employees
WHERE hiredate < STR_TO_DATE('3/15 1998','%m/%d %Y');



#四、流程控制函数  -------------------------------------------------------------

#if函数    ：if (设置条件，成立时返回值，不成立时返回值)
#查询员工奖金情况：有奖金，显示奖金数，没奖金，显示无
SELECT IF(commission_pct IS NOT NULL,salary*12*`commission_pct`,'无') 奖金数
FROM employees;

#case ：
/*
结构1：实现等值判断
case 表达式
when 值1 then 结果1
when 值2 then 结果2
....
else 结果n  （若无else，可以省略）
end
*/
#部门编号是30，工资3倍；部门编号是50，工资为2倍；部门编号是60，工资4倍；否则不变。查询：部门编号、新工资、旧工资
SELECT `department_id`,salary 旧工资,
CASE `department_id`
WHEN 30 THEN salary*3
WHEN 50 THEN salary*2
WHEN 60 THEN salary*4
ELSE salary
END AS 新工资
FROM employees;
/*
结构2：实现区间判断
case 
when 条件1 then 结果1
when 条件2 then 结果2
....
else 结果n   
end
*/
#若工资>2万，显示级别为A，工资大于1.5万，显示级别B；工资大于1万，显示级别C，否则显示D
SELECT salary,
CASE 
WHEN salary>20000 THEN 'A'  #A在这里为常量，字符型需要加单引号；常量值除了数值都要加引号；
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END 级别
FROM employees;




#--------------------------------------------------------------------------------------------------------------
#进阶5、常见函数 （主要是分组函数) : 分组函数也称为：聚合函数、统计函数
/*
聚合函数是用来做纵向运算的函数：分组函数一般搭配着group by 使用
  sum(字段名):数值相加的和
  avg（字段名）
  max（字段名）
  min（字段名）
  count（字段名）计算非空字段值的个数
*/
#查询所有员工的工资总和，平均值，最大值，最小值，有工资的个数
SELECT SUM(`salary`) 工资总数,AVG(salary)工资平均值,MAX(salary)最高工资,MIN(salary)最低工资,COUNT(salary)有工资的人的总数
FROM employees;
# 查询emp表中月薪大于2500的人数：select count(salary) FROM employees where salary>2500 ;
#有领导的人数：select count(`manager_id`)from employees;

#count补充介绍：
#统计结果的行数，推荐使用count（*）
SELECT COUNT(*)FROM employees; #*用于查询表的总行数，只要改行所有列存在至少一个值，都统计进去；若不存在一个值，则是无效记录(设计表时一般不会让全部为null)
SELECT COUNT(*)FROM employees WHERE department_id=30;
SELECT COUNT(1)FROM employees; #放”常量值（1、2、任意字符）“效果和count（*）一样；1是常量值，相当于在表格中加入一个全是1的常量列，然后统计有多少个1
#搭配distinct实现去重的统计 ：查询有员工的部门个数
SELECT COUNT( DISTINCT department_id)
FROM employees;

#查询每个部门的平均月工资,总工资
SELECT department_id, AVG(salary),SUM(salary)
FROM employees
GROUP BY department_id; #先把表中按部门编号分组，再进行操作


#--------------------------------------------------------------------------------------------------------------
#进阶6、分组查询：主要为了实现分组后的统计
/*
语法：
select 查询列表
from 表名
where 分组前筛选条件
group by 分组的字段/列表
having 分组后筛选
order by 最后排序
特点：
'查询列表'往往是'分组函数'和'被分组的字段'；放其他的意义不大


*/

2）可以实现分组前的筛选
案例1：查询邮箱中包含a字符的 每个部门的最高工资
SELECT MAX(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;
案例2：查询有奖金的每个领导手下员工的平均工资
SELECT manager_id, AVG(salary)
FROM employees
WHERE `commission_pct` IS NOT NULL
GROUP BY manager_id;

3）可以实现分组后的筛选 ：使用having函数
案例1：查询哪个部门的员工个数>5
SELECT COUNT(*)员工个数,department_id
FROM employees
GROUP BY department_id #先把每个部门员工个数算出来
HAVING COUNT(*)>5;     #算出来之后，使用having函数实现分组后的筛选 （where函数只在分组前筛选，不支持分组函数）
#分组前筛选指放在group by前面，使用where关键词
#分组后筛选在group by后面，使用having关键词 :分组函数做条件只可能放在having后面！！
案例2：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT job_id,MAX(salary)最高工资
FROM employees
WHERE `commission_pct`IS NOT NULL
GROUP BY job_id
HAVING 最高工资>12000;
案例3：领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资
SELECT manager_id,MIN(salary)最低工资
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING 最低工资>5000;

4）可以实现排序
案例：每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序
SELECT job_id,MAX(salary)最高工资
FROM employees
WHERE `commission_pct`IS NOT NULL
GROUP BY job_id
HAVING 最高工资>6000
ORDER BY 最高工资 ASC;

5）按多个字段分组：即多个字段的值完全一样则分为一组
# 案例：查询每个工种每个部门的最低工资,并按最低工资降序:工种和部门都一样，才是一组
SELECT job_id,department_id,MIN(salary)
FROM employees
GROUP BY job_id,department_id
ORDER BY MIN(salary);


#练习--------------------------------------------------------------------------
查询员工最高工资和最低工资的差距：difference
 SELECT MAX(salary)-MIN(salary) difference
 FROM employees;
 
 

#-----------------------------------------------------------------------------------------------------------------
#进阶7、连接查询：多表查询
/*
当查询的字段涉及到多个表时，用连接查询

笛卡尔乘积：假如表1有m行，表2有n行，输出结果则为m*n行
如何发生的：没有有效的连接条件
解决办法：先加上有效的连接条件 where

连接查询分类：
   1、按年代分类：
        sql92标准：在MySQL中，仅仅支持内连接
        sql99标准（推荐）：在MySQL中，支持内连接+外连接（左外、右外）+交叉连接
   2、按功能分类
        内连接：等值连接、非等值连接、自连接
        外连接：左外连接、右外连接、全外连接
        交叉连接
   


*/
#若采用如下查询，表示使用第一张表的每一条记录，去分别匹配第二张表的每一条记录
SELECT NAME,boyname FROM boys,beauty; #结果为12女name*4boyname，输出48行
此现象称为笛卡尔乘积现象；导致所有行实现完全连接
 
 #加上连接条件：
 SELECT NAME,boyname 
 FROM boys,beauty
 WHERE beauty.boyfriend_id=boys.id;
 

 # sql92连接 语法---------------------------------------------------------------
 
 #一、内连接：为了解决多表中的字段名重名问题，往往为表起别名，提高语义性；表的顺序无要求;在select的字段也要用别名限定

#1、等值连接：表1关联列=表2关联列 
/*
语法：
select 查询列表
from 表名1 别名1，表名2 别名2，。。。
where 等值连接的连接条件 
and 筛选条件
group by 分组条件
order by 排序条件

特点：
多表等值连接的结果为多表的交集部分
n表连接，至少需要n-1 个连接条件
多表的from顺序没有要求
一般需要为表起别名
可以搭配其他子查询使用：排序、分组等
*/
#查询部门编号>100的部门名和所在城市名（等值连接+筛选）
SELECT d.department_name,l.city
FROM `departments` d,`locations` l
WHERE d.location_id=l.location_id
AND d.department_id>100;
#查询每个城市的部门个数（等值连接+分组）
SELECT l.city,COUNT(*)
FROM locations l,departments d
WHERE l.`location_id`=d.`location_id` #1、先将两张表连接成一张，再进行其他操作
GROUP BY l.city;                      #2、将表分组
#????查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资 ????
SELECT d.department_name 部门名,d.manager_id,MIN(salary)
FROM `departments`d,`employees`e
WHERE d.`department_id`=e.`department_id`
AND `commission_pct`IS NOT NULL 
GROUP BY d.department_id;
#查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT j.job_title,COUNT(*)个数
FROM `jobs`j,`employees`e
WHERE j.`job_id`=e.`job_id`
GROUP BY j.`job_title`
ORDER BY 个数 DESC;
#查询哪些部门的员工个数大于5，并按个数进行降序
SELECT department_name,COUNT(*)
FROM `employees`e,`departments`d
WHERE e.`department_id`=d.`department_id`
GROUP BY d.`department_name`
HAVING COUNT(*)>5
ORDER BY COUNT(*)DESC;

#查询员工名、部门名和以s开头的所在城市（三表联建）,按部门名倒序。
SELECT e.last_name,d.department_name,l.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%'
ORDER BY d.department_name DESC;


#2、非等值连接
/*
在一个范围中进行表与表之间的匹配
可以搭配其他子查询使用
*/
#(先创建一个等级表job_grades;创建在当前myemployees中)
CREATE TABLE job_grades(
grade_level VARCHAR(3),
lowest_sal INT,
highest_sal INT);

INSERT INTO job_grades
VALUES('a',1000,2999);
INSERT INTO job_grades
VALUES('b',3000,5999);
INSERT INTO job_grades
VALUES('c',6000,9999);
INSERT INTO job_grades
VALUES('d',10000,14999);
INSERT INTO job_grades
VALUES('e',15000,24999);
INSERT INTO job_grades
VALUES('f',25000,40000);

SELECT*FROM job_grades;

#创建成功后，查询员工的工资和工资级别
SELECT e.`salary`,g.grade_level
FROM employees e,job_grades g
WHERE e.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 m,employees e #将一张表按别名分成两个，m为manager
WHERE m.employee_id=e.manager_id;


# sql99连接 语法---------------------------------------------------------------
#一、内连接：等值、非等值、自连接
语法：
SELECT 查询列表
FROM 表名1 别名1 【inner 】join 表名2 别名2 #99语法中，不用逗号连接表名。使用inner join 或者join连接
ON 连接条件  #99语法中，使用on代替where
WHERE 筛选条件 #99语法中，使用where代替and，
GROUP BY
HAVING
ORDER  BY
#sql 92和 sql 99除了关键字不同，其他使用方法完全一样
#查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT(*)
FROM `jobs` j INNER JOIN `employees`e 
ON e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*)DESC;

 #练习---------------------------------------------------------------------
 92语法
 SELECT NAME,majorname,score
 FROM stuifo s,major m,result r
 WHERE s.stuID=r.stuid
 AND s.majorid=m.id
 AND s.gender='男';
 99语法
 SELECT NAME,majorname,score
 FROM stuifo s JOIN major m  ON s.majorid=m.id #存在连接关系的，99语法可以之间在后面追加连接条件
 JOIN result r ON s.stuID=r.stuid 
 WHERE s.gender='男';

#二、外连接（只存在于99语法中）：左外连接、右外连接、全外连接（mysql中无效果，存在于Oracle，sqlsever中）
#分主从表：主表每一个记录与从表进行匹配，有匹配项则显示匹配值，没有匹配项则显示null值（查询结果为主表中的所有记录都会显示）

应用场景：一般用于查询主表有，而从表中没有的记录
特点：
外连接分主从表:所以两表的顺序不能任意调换
左连接：左边为主表  主表信息全出来
右连接：右边为主表    主表信息全出来
全外连接：  主从表的信息都能出来
语法：
SELECT 查询列表
FROM 表1 别名1
LEFT/RIGHT/FULL 【outer】 JOIN 表2 别名2
ON 连接条件
WHERE 筛选条件
#查询所有女神的记录以及对应的男神名，若没有对应男神，则显示为null
SELECT g.*,b.*
FROM beauty g 
LEFT OUTER JOIN boys b
ON g.boyfriend_id=b.id;
#查询哪个女神没有男朋友 ：在外连接中，筛选无匹配项的主表列时，此时where的筛选条件一般放从表的主键列
左连接
SELECT g.name
FROM beauty g 
LEFT OUTER JOIN boys b
ON g.boyfriend_id=b.id
WHERE b.`id` IS NULL;
右连接
SELECT g.name
FROM boys b
RIGHT OUTER JOIN beauty g 
ON g.boyfriend_id=b.id
WHERE b.`id` IS NULL;
#查询哪个部门没有员工，并显示其部门编号和部门名
USE myemployees;

SELECT d.`department_id`,d.`department_name`,e.`employee_id`
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE e.`employee_id`IS NULL;
#查询编号>3的女神的男朋友信息，若有，列出详细，若无，null填充
 USE girls;
 
 SELECT *
 FROM `beauty`g
 LEFT OUTER JOIN boys b ON g.`boyfriend_id`=b.`id`
 WHERE g.`id`>3;

#查询部门名为sal或it的员工信息
SELECT d.*,e.*
FROM employees e 
RIGHT OUTER JOIN `departments` d ON d.`department_id`=e.`department_id`
WHERE d.`department_name`='sal' OR d.`department_name`='it';

#全外连接（不可执行，仅示例）:相当于内连接+左外连接+右外连接的数据
SELECT b*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.`boyfriend_id`= bo.id;

#交叉连接：相当于在99语法中使用笛卡尔乘积(不能加outer)
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;


#-----------------------------------------------------------------------------------------------------------------

#进阶8、子查询
/*
当一个语句中又嵌套了另一个完整的select语句，则称被嵌套的select语句，则称为子查询/内查询
外面的select语句或其他语句则称为主查询/外查询

分类：
按子查询出现的位置进行分类
     select后面：仅支持标量子查询
     from后面：表子查询
     where或者having后面：标量子查询（单行）**、列子查询（多行）**、行子查询（单行多列或者多行多列）
     exists后面：表子查询
     
按结果集的行列数不同
        标量子查询/单行子查询 ：结果集只有单行单列
        列子查询/多行子查询：结果只有一列多行
        行子查询：结果集只有单行多列，或者多行多列
        表子查询：结果集一般为多行多列
        
     
特点：
子查询放在条件中，要求必须放在条件的右侧
子查询一般放在小括号中
子查询的执行优先于主查询
单行子查询对应了  单行操作符：> < >= <= <>
多行子查询对应了  多行操作符：any/some in all 
*/

一、where和having后面
1、标量子查询（单行子查询）：单行操作符：> < >= <= <>

#谁的工资比Abel高？
SELECT *
FROM employees 
WHERE salary>(
	SELECT salary 
	FROM employees 
	WHERE last_name='abel' 
);
#查询job_id与141号员工相同，salary比143号员工多的员工 姓名，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
);
#查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT MIN(salary) 
	FROM employees
	WHERE department_id=50
);
#非法使用标量子查询，即子查询结果非单行单列/子查询结果为无效结果


2、列子查询（多行子查询）：多行比较操作符：in/NOT IN ；any/some； ALL
 IN/NOT IN （等于/不等于列表中任意一个）
 #返回地址id是1400或1700的部门中所有员工姓名 in/not in 也可以换成 =any/<>all
 SELECT last_name
 FROM employees
 WHERE department_id IN(
	 SELECT DISTINCT department_id
	 FROM departments
	 WHERE location_id IN(1400,1700)
 );
 ANY/SOME （和子查询返回的某一个值进行比较）
#返回其他工种中比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(salary)
	FROM employees
	WHERE job_id='it_prog'
)AND job_id<>'it_prog';

 ALL（和子查询返回的所有值比较）
#返回其他工种中比job_id为‘it_prog’部门所有工资都低的员工信息
SELECT *
FROM employees
WHERE salary <ALL(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id='it_prog'
)AND job_id<>'it_prog';
#或者：理解为小于最小值
SELECT *
FROM employees
WHERE salary <(
	SELECT MIN(salary)
	FROM employees
	WHERE job_id='it_prog'
)AND job_id<>'it_prog';

3、行子查询（单行多列或者多行多列）——了解即可
#查询员工编号最小，工资最高的员工信息
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  #where条件表示：让员工表的id正好等于外查询部门表的id，再计算个数
) 个数
FROM departments d;
#查询员工号=102的部门名
内连接方法
SELECT d.department_name,e.`employee_id`
FROM departments d INNER JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE e.`employee_id`=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 s.*,j.grade_level
FROM (                      
	SELECT AVG(salary) ag, department_id
	FROM employees
	GROUP BY department_id
)s JOIN job_grades j
ON s.ag BETWEEN j.`lowest_sal`AND j.`highest_sal`;

四、exists后面：（相关子查询）表示是否存在，查看子查询是否有值，有就1，无就0;也可以用in代替
#查询有员工的部门名 :先执行外查询，再根据子查询过滤
SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE d.`department_id`=e.`department_id`
);
#查询没有女朋友的男生信息
SELECT bo.*
FROM boys bo
WHERE bo.`id` NOT IN(SELECT `boyfriend_id` FROM beauty );
或者
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
	SELECT*
	FROM beauty b 
	WHERE b.`boyfriend_id`=bo.`id`
);

#练习题------------------------------------------------------------------
#查询和zlotkey相同部门的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE department_id=(
	SELECT department_id
	FROM employees
	WHERE last_name='zlotkey'
);

#查询工资比公司平均工资高的员工的员工号，姓名和工资
SELECT employee_id,last_name,salary 
FROM employees
WHERE salary>(
	SELECT AVG(salary)
	FROM employees
);

#查询各部门中工资比本部门平均工资高的员工的员工号，姓名，工资
SELECT employee_id,e.last_name,e.salary,e.department_id
FROM employees e 
INNER JOIN (
	SELECT AVG(salary),department_id
	FROM employees
	GROUP BY department_id
)各部门平均工资
ON e.department_id=各部门平均工资.department_id
WHERE salary>'各部门平均工资';


#-----------------------------------------------------------------------------------------------------------------

#进阶9、分页查询
/*应用场景
当需要显示的数据要在多页显示时，需要分页提交sql请求
语法
select 查询列表
from 表名
join 表2
on
where
group by
having
order by
limit 【offset要显示条目的起始索引（从0开始）】，条目数size

特点：
limit语句放在查询语句最后，也是最后执行
公式：
要显示的页数page，每页的条目数size
select
from
limit （page-1）*size,size;

*/
#查询前5条员工信息
SELECT * FROM employees LIMIT 0,5; #若offset从1开始，可以省略
SELECT * FROM employees LIMIT 5;
#查询第11条到第25条员工信息
SELECT* FROM employees LIMIT 10,15;


#练习---------------------
#获取所有学员的邮箱的用户名（即：邮箱@前面的字符）
SELECT SUBSTR(emai,1,INSTR(email,'@')-1) 用户名
FROM stuinfo;




#-----------------------------------------------------------------------------------------------------------------

#进阶10、联合查询：union

/*
union:将多条独立的查询语句的结果合并成一个结果

语法：
查询语句1
union
查询语句2
union
查询语句3
。
。

应用场景：
当查询的结果来自多个没有明显联系的表格，但查询的信息一致，需要合并到一个结果时，可以用union合并到一起

注意事项：
要求多条查询语句的查询列数一致
多条查询的列名的类型最好一致，顺序对应
union查询时会自动去重，可以使用union all解决该问题




*/
#查询部门编号>90或者邮箱中包含a的员工信息
SELECT *
FROM employees
WHERE email LIKE '%a%'
UNION 
SELECT *
FROM employees
WHERE department_id>90;
或者
SELECT *
FROM employees
WHERE email LIKE '%a%'
OR department_id>90;