mysql(一) DQL查询语言

employees

进阶一基础查询（去重、拼接）

语法
SELECT 要查询的东西 【FROM 表名】;
类似于Java中 :System.out.println(要打印的东西);
特点：
①通过select查询完的结果 ，是一个虚拟的表格，不是真实存在
② 要查询的东西 可以是常量值、可以是表达式、可以是字段、可以是函数

#进阶一 基础查询
#注意查询的时候写上数据库
USE myemployees
#1、查询表中单个字段
SELECT last_name FROM employees;
#2、查询表中多个字段
SELECT last_name,salary FROM employees;
#3、查询表中所有字段 f12格式化
SELECT * FROM employees;
#4、查询常量池、查询表达式
SELECT 'john';
#5、起别名 (1)as
SELECT last_name AS 姓名 FROM employees;
#(2)用 空格 当别名有特殊符号时，别名加上双引号
SELECT last_name 姓名 FROM employees;
#6、去重
#案例查询员工表中涉及到所有的部门编号
SELECT DISTINCT department_id FROM departments;
#7、+号作用 运算符没有拼接的作用  100+90=190
    /* '123'+90* 其中一个为字符型将他转换成数值型 
    如果转换成功，则继续做加法运算 
    如果转换失败则将字符型数值转换为0
    select null+10;一个为null 结果为null*/
   #sql中的拼接用concat()函数
SELECT CONCAT(last_name,first_name )AS 姓名 FROM employees;
#显示表的结构
DESC `departments`;
#8、ifnull 
SELECT IFNULL(commission_pct,0) AS 奖金率, `commission_pct`
FROM employees;

进阶二条件查询（模糊查询）

格式：

select 
    要查询的字段|表达式|常量值|函数
from 
    表
where 
    条件 ;

分类：
条件表达式：

#按照条件表达式筛选    > < >= <= = != <>(不等于多用这个)
#1：
SELECT * 
FROM employees
WHERE salary >12000

SELECT last_name,department_id
FROM employees
WHERE department_id<>90;

逻辑表达式

2、按照逻辑表达式筛选  and（&&） or(||)  not(!)
/* and（&&）:两个条件如果同时成立，结果为true，否则为false
    or(||)：两个条件只要有一个成立，结果为true，否则为false
    not(!)：如果条件成立，则not后为false，否则为true*/
#作用:用于连接条件表达式
SELECT last_name,salary,`commission_pct`
FROM`employees`
WHERE salry>=10000 AND salary <=200000
#案例：部门编号不在90到100之间 或者工作大于15000的员工
SELECT *
FROM `employees`
WHERE `department_id`<90 OR `department_id`>100 OR `salary`>15000

模糊查询
like（%,-,escape转义字符）|between and| in | is null is not null

1、like 和通配符使用   
  /*% 任意多个字符 包含0个字符
    -
    \转义字符 escape转义
  */
#查询员工名包含字符a的员工信息
SELECT *
FROM employees
WHERE last_name LIKE '%a%';
#查询员工名中第3个字符为n 第5个字符为l的员工名和工资
SELECT last_name,salary 
FROM employees
WHERE  last_name LIKE '__n_l%';
#查询员工中第二个字符为_的员工名
SELECT last_name,salary 
FROM employees
WHERE  last_name LIKE '_$_%'ESCAPE '$';
#2、between and 
  /*注意事项  包含临界值 两个数字的顺序不呢个调换
  */
  
#案例1 查询员工编号在100到200之间的员工信息
SELECT *
FROM employees
WHERE `employee_id` BETWEEN 100 AND 200;
  #两者等价
SELECT *
FROM employees
WHERE `employee_id` >=100 AND `employee_id`<=200;
#３、in  用于去判断某字段的值是否属于in列表中的一项 里面的值统一　不知此通配符
#查询员工的工种编号是 IT_prog
SELECT *
FROM employees
WHERE `job_id` IN('IT_PROG','PU＿CLERK')
#4、is null
  /* =或<>不能判断null值  is null或者 is not null来判断null值*/
#查询没有奖金的员工名和奖金率
SELECT `last_name`,`commission_pct`
FROM employees
WHERE`commission_pct` IS NULL;
SELECT `last_name`,`commission_pct`
FROM employees
WHERE`commission_pct` IS NOT NULL;
#安全等于 即可以判断null值也可以判断数值
SELECT `last_name`,`commission_pct`
FROM employees
WHERE`commission_pct` <=> NULL;

SELECT `last_name`,`commission_pct`
FROM employees
WHERE  salary <=> 12000

进阶三排序查询 order by

语法：
select
    要查询的东西
from
    表
where 
    条件

order by 排序的字段|表达式|函数|别名 【asc|desc】

单个字段

#案例1：查询员工信息按照工资排序  desc降序 asc升序 不写默认升序
# 降序
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 ASC;

表达式

#案例3：按照年薪的高低 显示员工的信息和年薪
SELECT *,salary*12*(1+IFNULL(`commission_pct`,0)) AS 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(`commission_pct`,0)) DESC

函数

#按照函数排序
#案例四按照姓名的长度显示员工的姓名和工资
SELECT LENGTH(last_name) 字节长度,last_name,salary
FROM employees
ORDER BY LENGTH(last_name) DESC

多个字段

#案例五 多个字段排序先按照工资排序 再按照员工号排
SELECT *
FROM employees
ORDER BY salary ASC,employee_id DESC;

进阶四常见函数查询

单行函数
字符函数
concat拼接
substr截取子串
upper转换成大写
lower转换成小写
trim去前后指定的空格和字符
ltrim去左边空格
rtrim去右边空格
replace替换
lpad左填充
rpad右填充
instr返回子串第一次出现的索引
length 获取字节个数

#length 获取参数值的字节个数 utf-g一个汉字3个字节
SELECT LENGTH('john')
SELECT LENGTH('张丽丽lng') 
#concat 拼接字符串
SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees
#upper lower
#案例 将姓变大写，名变小写 拼接
SELECT CONCAT(UPPER(last_name),'_',LOWER(first_name)) FROM employees
# substr 截取字符串 索引从1开始
   #截取指定索引后的所有字符
SELECT SUBSTR('李莫愁爱上了陆展元',7)
   #截取索引处指定长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3)
   #将姓名中首字符大写 其他字符小写
 SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2)))
 FROM employees
#instr返回字符串的第一次出现的索引
SELECT INSTR('杨不悔爱上了殷六侠殷六侠','殷六侠') AS output
#trim 去前后空格 以及指定的字母
SELECT TRIM(' 范冰冰 ') AS output
SELECT TRIM('aaa' FROM 'aaa范冰aaa冰saaa')AS output
#lpad 用指定的字符实现左填充 填充指定长度  rpad 右填充
SELECT LPAD('张山峰',12,'*');
#replace 替换
SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') AS output

数学函数

#1、四舍五入
SELECT ROUND(-1.555)  结果：-2
SELECT ROUND(-1.555,2) 保留两位小数四舍五入 -1.56
#2向上取整 返回>=该参数的最小整数
SELECT CEIL(1.03) 结果2
SELECT CEIL(-1.03) 结果-1
#3、向下取整 返回<=该参数的最小整数 
SELECT FLOOR(-9.9) -10
#4、truncate 截断  小数点后截断
SELECT TRUNCATE(1.6999,1) 1.6
#mod取余 mod(a,b)=a-a/b*b
SELECT MOD(10,3)
SELECT MOD(-10,3)  结果-1

日期函数 now() str_to_data date_format datadiff(‘2007-10-1’,‘1998-03-2’)相差多少天

#now返回当前系统日期+时间
SELECT NOW()
#curdate当前系统日期，不包含时间
SELECT CURDATE()
#curtime当前系统时间
SELECT CURTIME
  #获取指定的时间
  SELECT YEAR(NOW()) 年
  SELECT MONTH(NOW())
  SELECT DAY(noe())
#str_to_date 将字符转换成日期 用的多
SELECT STR_TO_DATE('1990-4-2','%Y-%c-%d')

SELECT *
FROM employees
WHERE hiredate=STR_TO_DATE('4-3-1992','%c-%d-%Y')
#date_format将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y 年%m月%d日')
查询有奖金的员工名和入职日期（**年/xx日/xx月）
SELECT last_name,DATE_FORMAT(`hiredate`,'%y年%d日%m月')
FROM employees
WHERE `commission_pct` IS NOT NULL

其他函数

SELECT VERSION() 当前服务器的版本号i 
SELECT DATABASE() 当前打开的数据库
SELECT USER()当前用户

流程控制函数 if case（两种情况 ）

SELECT IF(10>5,'大','小')

SELECT last_name,`commission_pct`,IF(`commission_pct` IS NULL,'a','b') 备注
FROM employees

case
情况一相当于switch case

#case switch case
CASE 变量或表达式或字段
WHEN 常量1 THEN 值1
WHEN 常量2 THEN 值2
...
ELSE 值n
END

案例

部门号30 显示工资1,1
部门号40 显示工资1.2
可以搭配select用
SELECT salary,`department_id`,
CASE `department_id`
WHEN 30 THEN `salary`*1.1
WHEN 40 THEN `salary`*1.2
WHEN 50 THEN `salary`*1.3
WHEN 60 THEN `salary`*1.4
ELSE  salary 
END AS 新工资
FROM employees

情况二相当于多重if

case 
when 条件1 then 值1或语句1
when 条件2 then 值2或语句2
...
else 值n
end

#查询员工的新工资  工资大于10000A级别
SELECT salary,
CASE
WHEN salary>20000 THEN 'a'
WHEN salary>30000 THEN 'b'
ELSE 'd'
END AS 工资级别
FROM employees

分组函数

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

特点

#分组函数的特点
1、sum和avg一般用于处理数值型
2、max、min、count可以处理任何数据类型
3、以上分组函数都忽略null
4、都可以搭配distinct使用，实现去重的统计
5、count函数常用count(*)
SELECT COUNT(*) 统计结果集的行数 
和分组函数一同查询的字段
有限制  要求是group by后出现的字段

//查询最大入职时间和最小入职时间相差的天数
SELECT DATEDIFF(MAX(`hiredate`),MIN(`hiredate`))
FROM employees

进阶五：分组查询

语法:

在select 分组函数，分组后的字段
from 表
【where 筛选条件】
group by 分组的字段
【having 分组后的筛选】
【order by 排序列表】

注意：查询列表比较特殊 必须是分组函数和group by后出现的字段

简单的分组查询

分组前的筛选
#案例1查询每个工种的最高工资
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id
#案例2查询每个位置上的部门个数
SELECT COUNT(*),`location_id`
FROM`departments`
GROUP BY `location_id`
#案例3 
查询邮箱中包含a字符的每个部门的平均工资
SELECT AVG(salary),`department_id`
FROM`employees`
WHERE `email` LIKE'%a%'
GROUP BY `department_id`

查询有奖金的每个领导手下员工的工资
SELECT 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

案例2 查询每个工种有奖金的员工的最高工资
SELECT  MAX(salary),`job_id`
FROM`employees`
WHERE `commission_pct` IS NOT NULL 原始表就有
GROUP BY `job_id`
HAVING  MAX(salary)>2
查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪一个以及最低工资
SELECT MIN(salary),`manager_id`
FROM`employees`
WHERE `manager_id`>102
GROUP BY`manager_id`
HAVING MIN(salary)>5000

表达式 多个字段

#按照表达式或函数分组
按照员工的姓名长度分组，查询每一组的员工个数  要求员工个数>5
SELECT COUNT(*),LENGTH(last_name)
FROM `employees`
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5

#按照多个字段分组
#查询每个部门每个工种的员工的平均工资 `salary`,`department_id 连个一样的分组

SELECT AVG(salary),`department_id`,`job_id`
FROM`employees`
GROUP BY `job_id`,`department_id`

添加排序条件

SELECT AVG(salary),`department_id`,`job_id`
FROM`employees`
GROUP BY `job_id`,`department_id`
ORDER BY AVG(salary)DESC

总结

1、分组查询中的筛选条件总结
		使用关键字	     筛选的表	            位置
分组前筛选	WHERE		      原始表		GROUP by的前面
分组后筛选	HAVING		    分组后的结果	GROUP BY 的后面
一般分组函数做条件肯定是放在having子句中
能用分组前筛选的，优先考虑使用
2、group bY支持单个字段分组 也支持多个字段分组
多个字段用那个逗号隔开没有顺序要求
3、可以添加顺序排序 排序放在整个分组查询的最后

进阶六 连接查询

笛卡尔乘积现象 ：表1有m 行 表2有n行 结果 m*n 行、
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件
（一）sql92

等值连接

语法：

语法：
	select 查询列表
	from 表1 别名,表2 别名
	where 表1.key=表2.key
	【and 筛选条件】
	【group by 分组字段】
	【having 分组后的筛选】
	【order by 排序字段】

#案例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`

#案例3、为表起别名 起别名之后不能使用原来的表名
#查询员工名 工种名
SELECT e.`last_name`,e.`job_id`
FROM `employees` e,`jobs` j
WHERE e.`job_id`=j.`job_id`

#4、可以加筛选条件
查询有奖金的员工名、部门名
SELECT last_name,department_name
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id` AND e.`commission_pct` IS NOT NULL
#5、可以加分组
查询每个城市的部门数
SELECT COUNT(*),city
FROM `departments` d,`locations` l
WHERE d.`location_id`=l.`location_id`
GROUP BY city
#6、可以加排序
查询每个工种的工种名和员工的个数，并且按照员工个数降序
SELECT `job_title`,COUNT(*)
FROM `jobs` j,`employees` e
WHERE e.`job_id`=j.`job_id`
GROUP BY`job_title`
ORDER BY  COUNT(*)DESC
#7、可以实现三表连接
查询员工名、部门名及所在的城市
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`

总结：

多表等值连接的结果为多表的交集部分
n表连接，至少需要n-1个连接条件
多表的连接顺序没有要求
一般需要为表起别名

非等值连接

查询员工的工资和工资级别
SELECT `salary`,`grade_level`
FROM `employees` e,`job_grades` j
WHERE salary BETWEEN j.`lowest_sal` AND j.`highest_sal`

自连接

查询员工名和员工上级的姓名
SELECT e.`last_name`,m.`last_name`
FROM `employees` e,`employees` m
WHERE e.`manager_id`=m.`employee_id`

sql199

语法

语法
SELECT 查询列表
FROM 表1 别名 【连接类型】
JOIN 表2 别名
ON 连接条件
WHERE 筛选条件
GROUP BY 分组列表
HAVING 分组后的筛选
ORDER BY 排序列表

分类
内连接：inner 常用
左外LEFT 【outer】 常用
右外RIGHT【outer】 常用
全外full【outer】 
交叉连接cross

等值连接
#查询员工名 部门名
SELECT last_name,`department_name`
FROM `employees` e
INNER JOIN `departments` d
ON e.`department_id`=d.`department_id`
添加查询部门个数>3的城市名和部门个数
SELECT city,COUNT(*) as城市名
FROM `departments` d
INNER JOIN`locations` l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>3
添加分组删选
查询部门员工个数>3的的部门名和员工个数
SELECT `department_name`,COUNT(*)
FROM `employees` e
INNER JOIN `departments` d
ON e.`department_id`=d.`department_id`
GROUP BY e.`department_id`
HAVING COUNT(*)>3
三表连接
SELECT `department_name`,`job_title`
FROM `employees` e
INNER JOIN `departments`  d ON  e.`department_id`=d.`department_id`
INNER JOIN `jobs` j ON j.`job_id`=e.`job_id`

#非等值连接
SELECT`salary`,`grade_level`
FROM `employees` e
INNER JOIN `job_grades` j
ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`
#自连接
查询员工的名字和上级的名字
SELECT e.`first_name`,m.`last_name`
FROM `employees` e
INNER JOIN `employees` m
ON e.`manager_id`=m.`employee_id`

外连接

1、外连接的查询结果为主表中的所有记录
如果主表中有和它匹配的则显示匹配的值
如果主表中没有和它匹配的则显示null
外连接查询结果=内连接查询结果+主表中有而从表中没有的记录
2、left JOIN 左边的就是主表，right JOIN 右边的就是主表
  FULL JOIN 两边都是主表
3、左外右外交换实现同样的效果
查询男朋友不在男神表的的女神
应用场景：用于查询一个表中有，另一个表中没有的这种情况
引入：查询男朋友不在男神表的女神名
SELECT b.`name`,bo.*
FROM `beauty` b
LEFT OUTER JOIN `boys` bo
ON b.`id`=bo.`id`
WHERE bo.`id` IS NULL

子查询

出现在其他语句中的select语句 称为子查询或内查询
外部的查询语句称为主查询或外查询

分类
按子查询出现的位置
select后面 仅仅支持标量子查询
from后面
where或having后面  标量子查询（单行）列子查询（多行）行子查询
exists后面（相关子查询）
               标量子查询
		列子查询
		行子查询
		表子查询

#where或having后面
常用
标量子查询（单行子查询）：结果集为一行一列
列子查询（多行子查询）：结果集为多行一列
特点：
(1)子查询放在小括号内
(2)子查询一般放在条件的右侧
(3)标量子查询，一般搭配着单行操作符使用> < = >= <= <>
列子查询一般搭配着多行操操作符使用
(4)执行优先于主查询执行
标量子查询
#谁的工资比Abel高
SELECT *
FROM`employees`
WHERE salary >(
      SELECT `salary`
      FROM `employees`
      WHERE `last_name`='Abel'
);
#job_id与141号员工相同，salary比143号员工多的员工
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
);
#案例3
查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT MIN(salary)
FROM `employees`
WHERE `department_id`=50
查询每个部门的最低工资
SELECT `department_id`,MIN(salary)
FROM `employees`
GROUP BY`department_id`

SELECT `department_id`,MIN(salary)
FROM `employees`
GROUP BY`department_id`
HAVING MIN(salary)>(
	SELECT MIN(salary)
	FROM `employees`
	WHERE `department_id`=50
)

列子查询(多行子查询)
多行操作符
IN/NOT IN 等于列表中的任意一个（用的多）
ANY/SOME 和子查询返回的某一个值比较（用得少）
ALL 子查询返回的所有值比较

返回`location_id`是1400或1700的部门的所有员工的姓名
SELECT `last_name`
FROM `employees`
WHERE `department_id` IN(
	SELECT DISTINCT `department_id`
	FROM `departments`
	WHERE `location_id`IN(1400,1700)
);
ANY
返回其他工种比job_id为IT_PROG 任一工资都低的信息
SELECT *
FROM `employees`
WHERE salary < ANY(
	SELECT DISTINCT salary
	FROM `employees`
	WHERE `job_id`='IT_PROG'
)AND job_id <>'IT_PROG';

行子查询多行多列 多个字段都是= 了解
员工编号最小并且工资最高 
SELECT *
FROM employees
WHERE (`employee_id`,salary)=(
	SELECT MIN(`employee_id`),MAX(`salary`)
	FROM `employees`
)

exists
#exists
查询员工名和部门名
SELECT `department_name`
FROM `departments` D
WHERE EXISTS(
	SELECT *
	FROM `employees` E
	WHERE E.`department_id`=D.`department_id`
)

进阶七分页查询

应用场景：当要查询的条目数太多，一页显示不全
语法

select 查询列表
from 表
limit 【offset，】size;
注意：
offset代表的是起始的条目索引，默认从0卡死
size代表的是显示的条目数

查询前5条员工信息
SELECT *
FROM `employees` LIMIT 0,5
SELECT *
FROM `employees` LIMIT 5
查询11到25条的员工信息
SELECT *
FROM `employees` LIMIT 10,15
有奖金的员工信息 并且员工工资

```sql
在这里插入代码片

进阶八联合查询

union：合并、联合，将多次查询结果合并成一个结果
应用场景：要查询的结果来自多个表且多个表中没有直接的连接关系，但查询的信息一致时
联合查询的特点：
（1）要求多条查询语句的查询列数相同
（2）要求多条查询语句的每一列的类型和顺序一致
（3）union关键字默认去重，使用union all可以包含重复

SELECT *
FROM employees 
WHERE email LIKE '%a%' OR `department_id`>90;
等价
SELECT * FROM employees WHERE email LIKE '%a%' 
UNION
SELECT * FROM employees WHERE `department_id`>90

总结

总结：查询语句中涉及到的所有关键字

select 查询列表    ⑦
from 表1 别名       ①
连接类型 join 表2   ②
on 连接条件         ③
where 筛选          ④
group by 分组列表   ⑤
having 筛选         ⑥
order by排序列表    ⑧
limit 起始条目索引，条目数;  ⑨