MySQL学习日志--DQL语言的学习

MySQL学习日志–DQL语言的学习

初识数据库

数据库的好处

1.持久化数据到本地
2.可以实现结构化查询，方便管理

数据库存储数据的特点

​ 1、将数据放到表中，表再放到库中
​ 2、一个数据库中可以有多个表，每个表都有一个的名字，用来标识自己。表名具有唯一性。
​ 3、表具有一些特性，这些特性定义了数据在表中如何存储，类似java中 “类”的设计。
​ 4、表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似java 中的”属性”
​ 5、表中的数据是按行存储的，每一行类似于java中的“对象”。

MySQL常见的命令

1.查看当前所有的数据库
show databases;
2.打开指定的库
use 库名
3.查看当前库的所有表
show tables;
4.查看其它库的所有表
show tables from 库名;
5.创建表
create table 表名(

	列名 列类型,
	列名 列类型，
	。。。
);
6.查看表结构
desc 表名;
7.查看服务器的版本
方式一：登录到mysql服务端
select version();
方式二：没有登录到mysql服务端
mysql --version
或
mysql --V

MySQL语法规范

1.不区分大小写,但建议关键字大写，表名、列名小写
	2.每条命令最好用分号结尾
	3.每条命令根据需要，可以进行缩进 或换行
	4.注释
		单行注释：#注释文字
		单行注释：-- 注释文字
		多行注释：/* 注释文字  */

SQL 的语言分类

DQL（Data Query Language）：数据查询语言  select 
DML(Data Manipulate Language):数据操作语言  insert 、update、delete
DDL（Data Define Languge）：数据定义语言  create、drop、alter
TCL（Transaction Control Language）：事务控制语言  commit、rollback

DQL语言的学习

进阶1.基础查询

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

显示结构：
DESC  表名

取名：
SELECT 字段名 as 名字 FROME 表名 
去重：
SELECT DISTINCT 字段名 FROM 表名
+号的作用：
运算符
1.都为数值型做加法运算，
2.一方为字符，一个数值
      先试图字符型转换成数值型  '123'+95
      无法转换数值为0  'job'+35
3.null加任何都是null

显示全部列 
SELECT CONCAT(字段名1,字段名2,..) FROM 表名

判断是否为null.如果为null设置为0
SELECT IFNULL(字段名，0)

进阶2.条件查询

条件查询：根据条件过滤原始表的数据，查询到想要的数据
语法：
	select 
		要查询的字段|表达式|常量值|函数
	from 
		表
	where 
		条件 ;

分类：
一、条件表达式
	示例：salary>10000
	条件运算符：
	> < >= <= = != <>

二、逻辑表达式
示例：salary>10000 && salary<20000

逻辑运算符：

	and（&&）:两个条件如果同时成立，结果为true，否则为false
	or(||)：两个条件只要有一个成立，结果为true，否则为false
	not(!)：如果条件成立，则not后为false，否则为true

三、模糊查询
1.% 任意多个字符
示例：last_name like 'a%'
2.BETWEEN AND  包含临界值
3.in
  含义：判断某个字段的值是否属于in列表中的某一项
  特点：
  	1.使用in提高语句简洁度
  	2.in列表的值类型必须一致或兼容

4.is null 或 is not null 可以判断null值
5.安全等于 <=>  就是=  可以判断null值和普通的数值

进阶3.排序查询

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

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

1.asc 代表升序，desc代表降序 (默认是升序)

2.按函数长度排序
SELECT LENGTH(字段)

进阶4.常见函数

单行函数

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

2、数学函数
	round 四舍五入
	rand 随机数
	floor向下取整
	ceil向上取整
	mod取余
	truncate截断
3、日期函数
	now当前系统日期+时间
	curdate当前系统日期
	curtime当前系统时间
	str_to_date 将字符转换成日期
	date_format将日期转换成字符
4、流程控制函数
	if 处理双分支
	case语句 处理多分支
		情况1：处理等值判断
		情况2：处理条件判断
5、其他函数
	version版本
	database当前库
	user当前连接用户
	
6.流程控制函数
   1.if函数： if else  类似三元运算符
   2.case函数的使用一:  switch case
		case 要判断的字段或者表达式
		when 常量1 then 要显示的值1或者语句2;
		when 常量2 then 要显示的值2或者语句2;
		....
		else 要显示的值n或者语句n
		end
      case函数的使用二：类似于多重if
      	 case
      	 when 条件1 then 要显示的值1或语句1
      	 when 条件2 then 要显示的值2或语句2
      	 ....
      	 else 要显示的值n 或者语句n
      	 end

分组函数（做统计)

	sum 求和
	max 最大值
	min 最小值
	avg 平均值
	count 计数

特点：
  1、以上五个分组函数都忽略null值，除了count(*)
  2、sum和avg一般用于处理数值型
	max、min、count可以处理任何数据类型
  3、都可以搭配distinct使用，用于统计去重后的结果
  4、count的参数可以支持：
	字段、*、常量值，一般放1
  
建议使用 count(*)

进阶5.分组查询(每个后面要分组)

语法：
	select 查询的字段，分组函数，列(要求出现在group by的后面)
	from 表
	[where 筛选条件]
	group by 分组的字段
	[order by 子句]
注意：
	查询列表必须特殊，要求是分组函数和group by 后出现的字段
	
特点：
1、可以按单个字段分组
2、和分组函数一同查询的字段最好是分组后的字段
3、分组筛选
		  针对的表	       位置			  关键字
分组前筛选：	原始表		  group by的前面	  where 
分组后筛选：	分组后的结果集	group by的后面		having
   1.分组函数做条件肯定是放在having子句中
   2.能用分组前筛选的，就优先考虑使用分组前筛选

4、可以按多个字段分组，字段之间用逗号隔开
 
5、可以支持排序
6、having后可以支持别名

进阶6.多表连接查询

当查询的字段来自与多个表时，就会用到连接查询。
笛卡尔乘积：如果连接条件省略或无效则会出现
解决办法：添加上连接条件

传统模式下的连接 ：等值连接——非等值连接

1.等值连接的结果 = 多个表的交集
2.n表连接，至少需要n-1个连接条件
3.多个表不分主次，没有顺序要求
4.一般为表起别名，提高阅读性和性能

sql92语法：
		#案例2：查询员工名和对应的部门名
		SELECT last_name,department_name
		FROM employees,departments
		WHERE employees.`department_id`=departments.`department_id`;
		#1.可以加筛选
		#案例：查询有奖金的员工名、部门名
		SELECT last_name,department_name,commission_pct
		FROM employees e,departments d
		WHERE e.`department_id`=d.`department_id`
		AND e.`commission_pct` IS NOT NULL;
		#2.可以加分组
		#案例1：查询每个城市的部门个数
		SELECT COUNT(*) 个数,city
		FROM departments d,locations l
		WHERE d.`location_id`=l.`location_id`
		GROUP BY city;
		#3.可以加排序
		#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
		SELECT job_title,COUNT(*)
		FROM employees e,jobs j
		WHERE e.`job_id`=j.`job_id`
		GROUP BY job_title
		ORDER BY COUNT(*) DESC;
		#4.可以实现三表连接？
		#案例：查询员工名、部门名和所在的城市
		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%'
		ORDER BY department_name DESC;
		
		1.多表等值连接的结果为多表的交集部分
		2.n表连接，至少需要n-1个连接条件
		3.多表的顺序没有要求
		4.一般需要为表起别名
		5.可以搭配前面介绍的所有子句使用，比如 排序，筛选，分组

sql99语法：通过join关键字实现连接(推荐)

含义：1999年推出的sql语法
支持：
内连接：
语法：
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;
分类：
等值
非等值
自连接
特点：
①添加排序、分组、筛选
②inner可以省略
③ 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集

	等值连接
		#案例2.查询名字中包含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. 查询部门个数>3的城市名和部门个数，（添加分组+筛选）
			#①查询每个城市的部门个数
			#②在①结果上筛选满足条件的
		SELECT city,COUNT(*) 部门个数
		FROM departments d
		INNER JOIN locations l
		ON d.`location_id`=l.`location_id`
		GROUP BY city
		HAVING COUNT(*)>3;
	非等值连接
		 #查询工资级别的个数>20的个数，并且按工资级别降序
 		SELECT COUNT(*),grade_level
		FROM employees e
		JOIN job_grades g
		ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
		GROUP BY grade_level
 		HAVING COUNT(*)>20
 		ORDER BY grade_level DESC;
    自连接
     	#查询姓名中包含字符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没有的

	左外连接
		 #案例1：查询哪个部门没有员工
 		 #左外
 		 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 d.`department_id` = e.`department_id`
 	 WHERE e.`employee_id` IS NULL;
 	 
	全连接
交叉连接

语法：

select 字段，...
from 表1
【inner|left outer|right outer|cross】join 表2 on  连接条件
【inner|left outer|right outer|cross】join 表3 on  连接条件
【where 筛选条件】
【group by 分组字段】
【having 分组后的筛选条件】
【order by 排序的字段或表达式】

好处：语句上，连接条件和筛选条件实现了分离，简洁明了！

语法：
	select 查询列表
	from 表1 别名 【连接类型】
	join 表2 别名 
	on 连接条件
	【where 筛选条件】
	【group by 分组】
	【having 筛选条件】
	【order by 排序列表】
分类：
内连接（★）：inner
外连接
	左外(★):left 【outer】
	右外(★)：right 【outer】
	全外：full【outer】
交叉连接：cross 

2、为表起别名
①提高语句的简洁度
②区分多个重名的字段
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
#查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,j.job_title
FROM employees  e,jobs j
WHERE e.`job_id`=j.`job_id`;

自联接

案例：查询员工名和直接上级的名称
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m ON e.`manager_id`=m.`employee_id`;

进阶7.子查询

一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询
在外面的查询语句，称为主查询或外查询

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

分类：
按子查询出现的位置：
	1.select后面：
		仅仅支持标量子查询
		 #案例2：查询员工号=102的部门名
		SELECT (
		SELECT department_name,e.department_id
		FROM departments d
		INNER JOIN employees e
		ON d.department_id=e.department_id
		WHERE e.employee_id=102
		) 部门名;
	2.from后面：
		支持表子查询
		#案例：查询每个部门的平均工资的工资等级
		#①查询每个部门的平均工资
		SELECT AVG(salary),department_id
		FROM employees
		GROUP BY department_id
		SELECT * FROM job_grades;
		#②连接①的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal
		SELECT  ag_dep.*,g.`grade_level`
		FROM (
		SELECT AVG(salary) ag,department_id
		FROM employees
		GROUP BY department_id
		) ag_dep
		INNER JOIN job_grades g
		ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
	3.where或having后面：★
		1、标量子查询（单行子查询）
			#案例1：谁的工资比 Abel 高?
			#①查询Abel的工资
			SELECT salary
			FROM employees
			WHERE last_name = 'Abel'
			#②查询员工的信息，满足 salary>①结果
			SELECT *
			FROM employees
			WHERE salary>(
			SELECT salary
			FROM employees
			WHERE last_name = 'Abel'
			);
			#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
			#①查询50号部门的最低工资
			SELECT  MIN(salary)
			FROM employees
			WHERE department_id = 50

			#②查询每个部门的最低工资
			SELECT MIN(salary),department_id
			FROM employees
			GROUP BY department_id
			#③ 在②基础上筛选，满足min(salary)>①
			SELECT MIN(salary),department_id
			FROM employees
			GROUP BY department_id
			HAVING MIN(salary)>(
			SELECT  MIN(salary)
			FROM employees
			WHERE department_id = 50
			);
		2、列子查询（多行子查询）
		#案例3：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工 的员工号、姓名、job_id 以及salary

		SELECT last_name,employee_id,job_id,salary
		FROM employees
		WHERE salary<ALL(
		SELECT DISTINCT salary
		FROM employees
		WHERE job_id = 'IT_PROG'
		) AND job_id<>'IT_PROG';
		#或
		SELECT last_name,employee_id,job_id,salary
		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 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
		);
	4.exists后面（相关子查询
		语法：
		exists(完整的查询语句)
		结果：
		1或0
		#案例1：查询有员工的部门名
		#in
		SELECT department_name
		FROM departments d
		WHERE d.`department_id` IN(
		SELECT department_id
		FROM employees	
		)

		#exists
		SELECT department_name
		FROM departments d
		WHERE EXISTS(
		SELECT *
		FROM employees e
		WHERE d.`department_id`=e.`department_id`
		);
特点：
	①子查询放在小括号内
	②子查询一般放在条件的右侧
	③标量子查询，一般搭配着单行操作符使用
	> < >= <= = <>
	列子查询，一般搭配着多行操作符使用
	in、any/some、all
	④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
		
	exists后面（相关子查询）
		表子查询
按结果集的行列数不同：
	标量子查询（结果集只有一行一列）
	列子查询（结果集只有一列多行）
	行子查询（结果集有一行多列）
	表子查询（结果集一般为多行多列）

1、子查询都放在小括号内
2、子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
3、子查询优先于主查询执行，主查询使用了子查询的执行结果
4、子查询根据查询结果的行数不同分为以下两类：
① 单行子查询
	结果集只有一行
	一般搭配单行操作符使用：> < = <> >= <= 
	非法使用子查询的情况：
	a、子查询的结果为一组值
	b、子查询的结果为空
② 多行子查询
	结果集有多行
	一般搭配多行操作符使用：any、all、in、not in
	in： 属于子查询结果中的任意一个就行
	any和all往往可以用其他查询代替

进阶8.分页查询

实际的web项目中需要根据用户的需求提交对应的分页查询的sql语句
应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
	select 查询列表
	from 表
	【join type join 表2
	on 连接条件
	where 筛选条件
	group by 分组字段
	having 分组后的筛选
	order by 排序的字段】
	limit 【offset,】size;
	
	offset要显示条目的起始索引（起始索引从0开始）
	size 要显示的条目个数
特点：
	①limit语句放在查询语句的最后
	②公式
	要显示的页数 page，每页的条目数size
	
	select 查询列表
	from 表
	limit (page-1)*size,size;
	
	size=10
	page  
	1	0
	2  	10
	3	20
	#案例3：有奖金的员工信息，并且工资较高的前10名显示出来
	SELECT  * 
	FROM
    employees 
	WHERE commission_pct IS NOT NULL 
	ORDER BY salary DESC 
	LIMIT 10 ;

进阶9.联合查询

引入：
	union 联合、合并
	
语法：
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union  【all】
.....
select 字段|常量|表达式|函数 【from 表】 【where 条件】

应用场景：
要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时

特点：★
1、要求多条查询语句的查询列数是一致的！
2、要求多条查询语句的查询的每一列的类型和顺序最好一致
3、union关键字默认去重，如果使用union all 可以包含重复项

特点：
1、多条查询语句的查询的列数必须是一致的
2、多条查询语句的查询的列的类型几乎相同
3、union代表去重，union all代表不去重

#引入的案例：查询部门编号>90或邮箱包含a的员工信息
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;