【数据库】-----01初识MySQL(DQL语言)

一、什么是数据库？

概念

  数据库(DB)指的是长期存在计算机内、有组织、可共享的、大量数据的集合。数据是按照特定的数据模型来组织、存储在数据库中的。

  简单地说数据库（Database）就是按照数据结构来组织、存储和管理数据的仓库。
  每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。

  我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理大数据量。

相关概念

• DB（database）：数据库，保存一组有组织数据的容器
• DBMS(Database Management System)：数据库管理系统，又称为数据库软件（产品），用于管理DB中的数据
  • 常见DBMS：MySQL、Oracle、DB2、SqlServer等
• SQL:结构化查询语言，用于和数据库通信的语言

特点

1. 数据存放到表中，然后再放到库中
2. 一个库可以有多张表，每张表具有唯一的表名用来标识自己
3. 表中有一个或者多个列，列又称“字段”，相当于java中的属性
4. 表中每一行数据，相当于java中的对象

二、SQL的语言分类

• DML(Data Manipulate Language):数据操纵语言，用于添加、删除、修改数据库记录，并检查数据完整性

  关键字：insert, delete, update 等

• DDL（Data Define Languge）：数据定义语言，用于库和表的创建、修改、删除

  关键字：create, drop,alter等

• TCL（Data Control Language）：数据事务语言

• DQL（Data Query Language）：数据查询语言，用来查询数据库中表的记录(数据)

  关键字：select, where 等

三、MySQL是什么？

  MySQL是一种关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

3.1、MySQL的优点

  ①、MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

  ②、MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低。

  ③、MySQL 使用标准的 SQL 数据语言形式。

  ④、MySQL使用 C和 C++编写，并使用了多种编译器进行测试，保证了源代码的可移植性。
  ⑤、支持多种存储引擎。

3.2、MySQL服务的启动和停止

方式一：计算机——右击管理——服务——MySQL

​方式二：通过管理员身份运行cmd命令提示行

​	net start mysql（启动服务）
​	net stop mysql（停止服务）

3.3、MySQL服务的登录和退出

方式一：通过MySQL自带的客户端
​	只限于安装时的用户

​	方式二：通过cmd命令提示行关闭，未配置MySQL环境变量，需在MySQL安装的bin下启动cmd

​	登录：
​		mysql 【-h主机名 -P端口号 】-u用户名 -p密码

​	退出：
​		exit或ctrl+C

3.4、MySQL语法规范

1、不区分大小写
2、使用“；”结尾
3、各子句一般分行写
4、关键字不能缩写也不能分行
5、合理使用缩进

注释：
单行注释：#注释文字
单行注释：-- 注释文字
多行注释：/* 注释文字 */

四、DQL语言

4.1、常见函数

类似于java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1、隐藏了实现细节
  2、提高代码的复用性
调用：

select 函数名（实参列表）

【frome 表】(用到表中的字段加frome,反之不加);

特点：
1、查询列表可以是字段、常量、表达式、函数也可以是多个
2、查询结果是一个虚拟表

4.1.1 单行函数

字符型函数

函数	描述
length（）	获取参数值的字节个数
concat	拼接字符串
upper()	将参数变大写
lower()	将参数变小写
substr(字段，索引，截取位数)	截取字符（注意：索引从1开始）
instr(A,B)	返回B字符在A字符中第一次出现的索引位置
trim()	去掉前后空格
lpad(字段，填充长度，填充符号)	用指定的字符实现左填充指定长度
rpad(字段，填充长度，填充符号)	用指定的字符实现右填充指定长度
replace（‘abc’,‘b’,‘d’）	用d替换abc中的b

数学函数

函数	描述
SELECT ROUND（数值）	四舍五入
SELECT CEIL(数值)	向上取整,返回>=该参数的最小整数
select floor();	向下取整
SELECT TRUNCATE(数值,位数);	截断,小数点后保留位数
SELECT MOD(10,3);	取余

日期函数

函数	描述
SELECT NOW();	返回当前系统日期+时间
SELECT CURDATE();	只返回当前系统日期，不包含时间
SELECT CURTIME();	返回当前的时间，不包含日期
SELECT TRUNCATE(数值,位数);	截断,小数点后保留位数
SELECT YEAR(NOW()) 年;	获取指定的部分，年、月、日、时、分、秒
str_to_data(日期，格式)	将日期格式的字符转换成指定格式的日期
data_format(日期，格式)	将日期转换成字符

其他函数

函数	描述
SELECT VERSION();	查询版本号
SELECT DATABASE();	查看当前的库
SELECT USER();	当前用户

流程控制函数

1、if 函数：实现java中if else的效果

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

2、case函数的使用一：switch case的效果

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2then 要显示的值2或语句2;
……

else 要显示的值n或语句n;
end

案例：查询员工的工资，要求
部门编号=30，显示的工资为1.1倍
部门编号=40，显示的工资为1.2倍
部门编号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资

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
ELSE salary
END AS 新工资
FROM `employees`;

case函数的使用二：类似于多重if

case 
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
……
else  要显示的值n或语句n;
end

案例：查询员工的工资情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000,显示C级别
否则显示D级别

SELECT last_name,salary,
CASE 
WHEN(salary>20000) THEN 'A'
WHEN(salary>15000) THEN 'B'
WHEN(salary>10000) THEN 'C'
ELSE 'D'
END AS 级别
FROM `employees`;

4.1.2 分组函数

sum 求和、 avg平均值、 max最大值、 min最小值、count计算个数
特点：

1、sum、avg一般用于处理数值型
  max、min、count可以处理任何类型
2、以上分组函数都忽略null值
3、可以和distinct搭配实现去重运算
4、count 函数的单独介绍
5、和分组函数一同查询的字段要求是group by后的字段

count函数的详细介绍

 SELECT COUNT(salary) FROM employees;
 
 /*统计行数*/
 SELECT COUNT(*)FROM employees;一般使用这个
  SELECT COUNT(1)FROM employees;
#效率：
MYISAM 存储引擎下， COUNT(*) 的效率高 
INNODB 存储引擎下， COUNT(*) 和 COUNT(1) 的效率差不多，比 COUNT(字段) 要高 
#和分组函数一同查询的字段要求是group by后的字段

4.2、查询

4.2.1 基础查询语法

语法	描述
SELECT * FROM 表名;	查询表中所有记录
select 字段名1,字段名2... from 表名;	多个字段的查询
select distinct 字段名 from 表名；	去重
select 函数名(实参列表)	查询函数
select常量值；	查询常量 （注意：字符型和日期型的常量必须用单引号引起来，数值型不需要）
as(也可省略)	起别名
select concat(字符1，字符2，……)；	拼接字符
ifnull	判断某个字段或表达式是否为null,如果为null返回指定的值，否则返回原本的值
+	只做加法运算

注意：

select 字符+数值；先试图将字符转换成数值，如果转换成功，则继续运算；否则转换成0，再做运算

select  null+值；结果都为null

4.2.2 条件查询

语法：

select
	查询列表
from
	表名
where
	筛选条件;

分类

1、按条件表达式筛选

条运算符：>  <  =  !=(<>)  >=  <=  

2、按逻辑表达式筛选

逻辑运算符：
作用：用于链接条件表达式
       &&  ||  ！ 
       and  or  not

3、模糊查询
①、like
特点：

1、一般和通配符搭配使用,可以判断字符型或数值型
通配符：
%任意多个字符，包含0个字符

_任意单个字符

#案例1、查询员工名中包含字母a的员工信息
SELECT 
  * 
FROM
  employees 
WHERE last_name LIKE '%a%' ;

②、between…… and……
特点：

1、可以提高语句的简洁度
2、包含临界值
3、两个临界值不要调换位置

#案例1：查询员工编号在100到120之间的员工信息
SELECT 
  * 
FROM
  employees 
WHERE department_id BETWEEN 100 
  AND 120 ;

③、in

含义：判断某字段的值是否属于in列表中的某一项
特点：
1、使用in比提高语句简洁度
2、in列表的值类型必须一致或兼容
3、不支持通配符

#案例：查询员工的工种编号是IT_PROG、AD_VP、AD_PRES中的一个的员工名和工种编号
SELECT 
  last_name,
  job_id 
FROM
  employees 
WHERE job_id IN ('IT_PROG', 'AD_VP', 'AD_PRES');

④、is null

is null/is not null:仅仅key 不能判断普通类型的数值 判断null值(可读性较高)

<=>：既可以判断null值，又可以判断普通的数值(可读性较低)

=或<>不能用于判断null值

#案例1：查询没有奖金的员工名和奖金率
SELECT 
  last_name,commission_pct 
FROM
  employees 
//无奖金where commission_pct is not null;
WHERE commission_pct IS  NULL;

⑤、ifnull

案例3：查询员工号为176的员工的姓名和部门号和年薪
SELECT 
  last_name,
  department_id,
  salary * 12 * (1+ IFNULL(commission_pct, 0)) AS 年薪 
FROM
  employees;
  

⑥、安全等于 <=>

#案例2：查询工资为12000的员工信息
SELECT 
  last_name,salary 
FROM
  employees 

WHERE salary <=> 12000;

4.2.3 排序查询

语法：

SELECT  	查询列表
FROM  表名
	【where 筛选条件】
order by 排序列表 【asc/desc】(升降序),不写默认升序状态

特点：

1、ORDER BY 子句中可以支持单个字段、多个字段、表达式、函数、别名

2、ORDER BY 子句一般放在查询语句的最后面,LIMIT子句除外

4.2.4 分组查询

语法：

select  分组函数，列（要求出现在group by的后面）
from 表
【where 筛选条件】
group by 分组的列表
【order by 子句】

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

group by(分组依据) 子句语法，可以使用group by子句将表中的数据分成若干组
特点：
1、分组查询中的筛选条件分为两类，数据源不一样，位置不一样

	数据源	位置	关键字
分组前筛选	原始表	group by 子句的前面	where
分组后筛选	分组后的结果集	group by 子句的后面	having

①分组函数作条件肯定是放在having子句中
②能用分组前筛选的，优先考虑使用分组前筛选

2、group by 子句支持单个字段分组，多个字段分组（多个字段用逗号隔开没有顺序要求），表达式或函数（用得较少）
3、也可以添加排序（排序放在整个分组最后）

添加筛选条件查询👇

案例：查询邮箱中包含a字符的，每个部门的平均工资
SELECT 
  AVG(salary),
  department_id 
FROM
  employees 
WHERE email LIKE '%a%' 
GROUP BY department_id ;

添加复杂的筛选条件👇

案例：查询哪个部门的员工个数>2
#①查询每个部门的员工个数
SELECT 
  COUNT(*),
  `department_id` 
FROM
  `employees` 
GROUP BY `department_id` ;

#②根据①的结果进行筛选，查询哪个部门的员工个数>2
SELECT 
  COUNT(*),
  `department_id` 
FROM
  `employees` 
GROUP BY `department_id` 
HAVING COUNT(*)>2;

按表达式或函数分组👇

按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
SELECT 
  COUNT(*) 员工个数,LENGTH(last_name) 长度
FROM
  `employees` 
GROUP BY LENGTH(last_name) 
HAVING 员工个数 > 5 ;

按多个字段分组👇

案例：查询每个部门每个工种的员工的平均工资
SELECT 
  AVG(`salary`),
  `job_id`,
  `department_id` 
FROM
  `employees` 
GROUP BY `job_id`,
  department_id ;
  

4.2.5 多表连接查询

多表连接查询又称多表查询，多表连接，当查询的字段来自于多个表时，就会用到

特点：

一般都要为表起别名
 	多表顺序可以调换
 		n表连接至少需要n-1个连接条件
 			等值连接的结果是多表的交集部分

❗注意：使用多链表查询如果没有添加有效的连接条件，会出现迪卡尔乘积现象

迪卡尔乘积现象:当查询多个表时，没有添加有效的连接条件，导致多个表所有行实现完全连接，表1 有m行，表2 有n行，结果=m*n行，添加上有效的连接条件就会避免这种情况

分类：

内连接

语法：

select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件

图解：

内连接又分为等值连接、非等值连接、自连接

等值连接

语法：

select 查询列表			
from 表1 别名1，表2 别名2 
where 别名1.XXX=别名2.XXX

案例：

#案例：查询员工名、部门名、工种名，并按部门名降序(多表连接)
SELECT 
  `last_name`,
  `department_name`,
  `job_title` 
FROM
  `employees` e 
  INNER JOIN `departments` d
  ON e.`department_id` = d.`department_id` 
  INNER JOIN `jobs` j
  ON e.`job_id` = j.`job_id` 
ORDER BY `department_name` DESC ;

非等值连接

案例：

#查询员工的工资级别
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.`last_name`,c.`last_name`
 FROM `employees` e
 INNER JOIN `employees` c
 ON e.`manager_id`=c.`employee_id`;

外连接

应用场景：用于一个表中有，另一个表中没有的记录
特点：

 1、外连接的查询结果为主表中的所有记录
   	如果从表中有和它匹配的，则显示匹配的值
   			如果从表中没有和它匹配的，则显示null
   				

2、左外连接，left左边的是主表
  右外连接，reght join 右边的是主表


3、左外和右外交换两个表的顺序，可以实现同样的效果
4、一般用于查询除了交集部分的剩余不匹配的内容
5、要查询的信息主要来自哪个表，哪个表就是主表

外连接查询结果=内连接结果+主表中有而从表中没有的记录

图解：
左外

右外

交叉连接

语法：

select 查询列表
from 表1 别名
cross join 表2 别名；

就是用sql99实现的一个笛卡尔乘积

4.2.6 子查询

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

内部嵌套其他select语句的查询，称为外查询或主查询

#案例：返回公司工资最少的员工的姓名，job_id和工资
	//外查询：
SELECT `last_name`,`job_id`,`salary`
FROM `employees`
WHERE `salary`=(
		//子查询：
		SELECT MIN(`salary`)
		FROM `employees`
);

特点

子查询放在小括号内
	子查询一般放在条件的右侧
		标量子查询，一般搭配着单行操作符使用,> < >= <= = < >(单行操作符)
			子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

select 后面
仅仅支持标量子查询

/*案例：查询每个部门的员工个数*/
SELECT d.*,(
	SELECT COUNT(*)
	FROM `employees` e
	WHERE e.`department_id`=d.`department_id`
)个数
FROM `departments` d;

from后面
支持表子查询，将子查询结果充当一张表，要求必须起别名

/*案例：查询每个部门的平均工资的工资等级*/
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`;

where或having后面
支持：标量子查询、列子查询、行子查询(较少使用)
这里就会用到多行比较操作符：

操作符	含义
IN\NOT IN	等于列表中的任意一个
ANY\SOME	和子查询返回的某一个值作比较
ALL	和子查询返回的所有值作比较

列子查询(多行子查询)：

//案例1：返回`location_id`是1400或1700的部门中的所有员工姓名
SELECT `last_name`
FROM `employees` e
WHERE e.`department_id` IN( 
#①查询location_id是1400或1700的部门编号
	SELECT DISTINCT `department_id`
	FROM `departments`
	WHERE `location_id` IN (1400,1700)
);

行子查询：

#案例：查询编号最小工资最高的员工信息
//原始方法
SELECT *
FROM `employees`
WHERE `employee_id`=(
	SELECT MIN(`employee_id`)
	FROM `employees`
)AND `salary`=(
	SELECT MAX(`salary`)
	FROM `employees`
);

//用行子查询做
SELECT *
FROM `employees`
WHERE (`employee_id`,`salary`)=(
	SELECT MIN(`employee_id`),MAX(`salary`)
	FROM `employees`
);

exists后面(相关子查询)
语法：

exists(完整的查询语句)

结果：1/0

#案例：查询有员工的部门名
SELECT `department_name`
FROM `departments` d
WHERE EXISTS(
SELECT *
FROM `employees` e
WHERE d.`department_id`=e.`department_id`
);

#使用in
SELECT `department_name`
FROM `departments` d
WHERE d.`department_id` IN(
	SELECT `department_id`
	FROM `employees`
)

能用exists实现的，都可以用in来实现

4.2.7 分页查询(limit)

应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求

#案例：查询第11条到25条
SELECT *
FROM `employees`
LIMIT 10,15;

4.2.8 union联合查询

  将多条查询语句的结果合并成一个结果

语法：

 查询语句1
 UNION
 查询语句2
 UNION
 ……

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

特点：

要求多条查询语句查询的每一列的类型和顺序最好一致

使用union默认是去重的，如果使用union all 可以包含重复项

案例：查询部门编号>90或邮箱包含a的员工信息
SELECT 
  * 
FROM
  `employees` 
WHERE `department_id` > 90 
UNION
SELECT 
  * 
FROM
  `employees` 
WHERE `email` LIKE '%a%' ;