mysql学习第一弹——查询基础

一.SQL的分类

1.DDL(data definition language)数据定义语言

2.DML(data manipulation language) 数据操纵语言

3.DCL(Data Control Language)数据控制语言

SQL在windows环境下对大小写不敏感

还有分类为四种或五种的都大同小异

二.注释

1. 单行注释

• #
• --

2. 多行注释 /**/

多行注释可以嵌套单行注释，但不可以嵌套多行注释

三.数据的导入

• 命令行的方式

source sql文件的绝对路径 

• 图形化界面以DBeaver为例

ctrl+shift+alt+o快速导入sql脚本

四.基本SELECT语句

1. SELECT...FROM

# 良好的书写规范
SELECT 字段1，字段2…
FROM 表名; 

2. 别名

# AS是alias的缩写 
# 别名是多个单词且没有连字符一定要用"",如 "full name"
SELECT 字段1 别名,字段2 AS 别名…
FROM 表名

3. 去重

关键字 DISTINCT  用在 SELECT后

4. 空值参与运算

# 空值与任何值运算的结果还是空值(null)
解决方法：IFNULL(字段，空值替换的值) 

5. 显示表的结构

DESCRIBE 表名 或
DES 表名  

6. 过滤数据

WHERE 

五.运算符

算术运算符

+ - * /(div) %(mod) 

• 注意SQL的+没有连接符的作用
• /(div)得到的结果肯定是浮点型或null
• 优先级为：先乘除取模再加减

比较运算符

常规：>、<、=、<=、>=

比较结果为真返回1，为假返回0，其余返回null

值得注意的是：

• 安全等于 <=> 可以对null进行判断，两边都是null返回1，只有一个null返回0
• 不等于运算符 <>或！=

字符串与不同类型比较的时候，会发生隐式转换，转换不成功返回0

如：

select 1='a', 0 = 'a'
结果为  0 1
# 安全等于
select 1<=>'1', null <=> null , null <=> 0

其他与null有关的操作符：

is null
is not null
isNull() #函数

模糊查询：

• % 不确定个数的字符
• _ 单个字符
• \ 默认转义字符

# 查询last_name中包含字符'a'的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%';
 
# 查询last_name中第二个字符是'a'的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '_a%';
 
# 查询last_name中第二个字符是'_'的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '_\_%';
# 当然，我们也可以更换默认转义字符
WHERE last_name LIKE '_$_%' ESCAPE '$';

逻辑运算符

• 逻辑与 &&(and)
• 逻辑或 ||(or)
• 逻辑非 !(not)
• 逻辑异或 xor

位运算符

~ & | ^ >> <<

和c语言一样

六.排序和分页

排序

用order by实现排序，注：没有指定排序方式默认升序

指定排序方式：

• ascend 升序
• descend 降序

# 按工资降序排列
select last_name,salary,department_id
from employees
order by salary asc

二级排序

在上面例子中会出现一个问题，工资会有相同的情况，那就会自动按照插入先后排序

当然我们也可以多重排序:

# 按工资降序，部门号升序排列
order by salary asc, department_id

分页

limit (pageNum - 1) * size , size #计算与页码的关系

# 每页显示二十条数据 这是第1页
select  last_name ,salary 
from employees
limit 0,20
 
# 第三页
select  last_name ,salary 
from employees
limit 40,20

关键字声明顺序要求:

where …
order by …
limit …

从第n条数据后开始查询:

limit 跳过的条目  offset 偏移量
 
#从30条数据后开始查询
select employee_id ,salary 
from employees 
order by employee_id desc #降序
limit 30 offset 2;

七. 多表查询

为什么要多表查询？

这里有个前置问题就是为什么要分表？
其实还是性能和效率问题，总会有不常使用的字段和可整合的字段，分库分表可以降低数据量，优化储存。

1. 可以降低网络远程交互次数
2. 减少加载冗余数据

多表查询典型错误——笛卡尔积

# 出现笛卡尔积错误
# 需求：通过员工表，查询工作地址id
/*
 * employees 107条数据
* departments 27条数据
 * 查询结果 2889条数据 (28889 = 107 * 27)
 */
select employee_id ,location_id
from employees ,departments

可以看到上述错误的结果集是employee与departments做笛卡尔积得到的集合
即： result = employees × departments

原因 没有链接条件或链接不正确

添加链接条件后：

select employee_id ,location_id
from employees ,departments
where employees.department_id =departments.department_id

异表存有相同字段查询不明确的问题

select department_id 
from employees ,departments
where employees.department_id =departments.department_id 

解决方法：指定表名 (为了便于多表查询，我们通常会给表起别名)

select dp.department_id 
from employees ep ,departments dp 
where ep.department_id = dp.department_id 

如果有n表实现多表查询，至少需要n-1个链接条件

多表查询的分类

• 等值链接和不等值链接
• 自链接和非自链接
• 外链接和内链接

1. 不等值链接

# 需求：查询员工的工资属于哪个等级
select e.last_name ,e.salary ,jg.grade_level 
from employees e ,job_grades jg 
where e.salary between jg.lowest_sal and jg.highest_sal 
order by  jg.grade_level asc  

2. 自链接

# 需求：查询员工的姓名，员工id，管理者姓名，管理者的员工id
select  e.last_name ,e.employee_id,mag.last_name ,mag.employee_id 
from employees e,employees mag
where e.manager_id = mag.employee_id 

3. 内链接与外链接

以下例子均以员工表 (A表)和 部门表 (B表) 为例

1. inner join，即A ∩ B ，内链接还分为：

• 隐式内链接
• 显式内链接

# 隐式内链接
select e.last_name ,d.location_id 
from employees e , departments d 
where e.department_id = d.department_id 
 
# 显式内链接 
select e.last_name ,d.location_id 
from employees e inner join departments d  
on e.department_id = d.department_id 

inner可以省略

2. left outer join，即( A ∩ B ) ∪ A

select e.last_name ,d.location_id 
from employees e left outer join departments d 
on e.department_id = d.department_id 

outer可以省略

3. right outer join，即( A ∩ B ) ∪ B

select e.last_name ,d.location_id 
from employees e right outer join departments d 
on e.department_id = d.department_id 

4. left excluding join，即A - ( A ∩ B )

select e.last_name ,d.location_id 
from employees e left outer join departments d 
on e.department_id = d.department_id 
where d.department_id is null

5. right excluding join，即B - ( A ∩ B )

select e.last_name ,d.location_id 
from employees e right outer join departments d 
on e.department_id = d.department_id 
where e.department_id is null 

6. full outer join， 即A ∪ B

select e.last_name ,d.location_id 
from employees e left join departments d
on e.department_id =d.department_id 
union all 
select e.last_name ,d.location_id 
from employees e right join departments d
on e.department_id =d.department_id
where e.department_id is null 

7. outer excluding join，即(A ∪ B) - ( A ∩ B )

select e.last_name ,d.location_id 
from employees e left join departments d
on e.department_id =d.department_id 
where d.department_id is null 
union all 
select e.last_name ,d.location_id 
from employees e right join departments d
on e.department_id =d.department_id
where e.department_id is null

上面的代码出现了union all，我们来介绍其作用：

union all操作符合并两个或两个以上 SELECT 语句的结果集，相当于 ∪

注意必须保持每条 SELECT 语句中的列的顺序和列的数量相同，也必须拥有相似的数据类型。

如以下使用是错误的：

# 两条 SELECT 语句列顺序不一致
select e.last_name ,d.location_id 
from employees e left join departments d
on e.department_id =d.department_id 
where d.department_id is null 
union all 
select ,d.location_id，e.last_name 
from employees e right join departments d
on e.department_id =d.department_id
where e.department_id is null

union操作符合并两个或两个以上 SELECT 语句并进行去重处理

UNION ALL 命令和 UNION 命令几乎是等效的,我们要尽可能的使用UNION ALL，效率更高

用using函数简化等值链接

使用条件：

1. 查询必须是等值连接
2. 等值连接中的列必须具有相同的名称和数据类型

select  e.last_name ,d.department_name 
from employees e join departments d
using(department_id,manager_id)

表atguigu的表，文中的练习和答案可以找我拿（不只文中）