mysql（二）:DQL、多表查询、视图

1 查询语句（DQL）

1.0 完整查询语句

一个完整的select语句内容是很丰富的。下面看一下select的执行过程：

(5)SELECT DISTINCT <select_list>                     

(1)FROM <left_table> <join_type> JOIN <right_table> ON <on_predicate>

(2)WHERE <where_predicate>

(3)GROUP BY <group_by_specification>

(4)HAVING <having_predicate>

(6)ORDER BY <order_by_list>

(7)LIMIT <limit_number>

1.1 基本查询(select)

select * from 表名 [where 条件];
select 字段1,字段2 from 表名 [where 条件];

e.g. 
select * from class_1;
select name,age from class_1;

1.1.1 字段别名（as）

在sql语句中as用于给字段或者表重命名

select name as 姓名,age as 年龄 from class_1;
select * from class_1 as c where c.age > 17;

1.1.2 聚合函数

方法	功能
avg(字段名)	该字段的平均值
max(字段名)	该字段的最大值
min(字段名)	该字段的最小值
sum(字段名)	该字段所有记录的和
count(字段名)	统计该字段记录的个数

eg1 : 找出表中的最大攻击力的值？

select max(attack) from sanguo;

eg2 : 表中共有多少个英雄？

select count(name) as number from sanguo;

eg3 : 蜀国英雄中攻击值大于200的英雄的数量

select count(*) from sanguo where attack > 200; 

注意： 此时select 后只能写聚合函数，无法查找其他字段。

1.1.3 聚合运算

• 查询表记录时做数学运算

运算符 ： + - * / %

eg1: 查询时显示攻击力翻倍
  select name,attack*2 from sanguo;
eg2: 更新蜀国所有英雄攻击力 * 2
  update sanguo set attack=attack*2 where country='蜀国';

1.1.4 去重语句

• distinct语句

不显示字段重复值

eg1 : 表中都有哪些国家
  select distinct name,country from sanguo;
eg2 : 计算一共有多少个国家
  select count(distinct country) from sanguo;

注意: distinct和from之间所有字段都相同才会去重

1.2 条件查询(where)

where子句在sql语句中扮演了重要角色，主要通过一定的运算条件进行数据的筛选，在查询，删除，修改中都有使用。

1.2.1 算数运算符

e.g.
select * from class_1 where age % 2 = 0;

1.2.3 比较运算符

e.g.
select * from class_1 where age > 8;
select * from class_1 where between 8 and 10;
select * from class_1 where age in (8,9);

1.2.3 逻辑运算符

sql e.g. select * from class_1 where sex='m' and age>9;

1.2.4 模糊查询(like)

LIKE用于在where子句中进行模糊查询，SQL LIKE 子句中使用百分号%来表示任意0个或多个字符，下划线_表示任意一个字符。

 SELECT field1, field2,...fieldN 
 FROM table_name
 WHERE field1 LIKE condition1

 e.g. 
 mysql> select * from class_1 where name like 'A%';

1.2.5 正则查询（regexp）

mysql中对正则表达式的支持有限，只支持部分正则元字符:

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 REGEXP condition1

e.g. 
select * from class_1 where name regexp '^B.+';

聚合操作

聚合操作指的是在数据查找基础上对数据的进一步整理筛选行为，实际上聚合操作也属于数据的查询筛选范围。

1.3 聚合分组（group by）

给查询的结果进行分组

e.g. : 计算每个国家的平均攻击力

select country,avg(attack) from sanguo 
group by country;

e.g. : 对多个字段创建索引，此时多个字段都相同时为一组

select age,sex,count(*) from class1 group by age,sex;

e.g. : 所有国家的男英雄中 英雄数量最多的前2名的 国家名称及英雄数量

select country,count(id) as number from sanguo 
where gender='M' group by country
order by number DESC
limit 2;

注意： 使用分组时select 后的字段为group by分组的字段和聚合函数，不能包含其他内容。group by也可以同时依照多个字段分组，如group by A，B 此时必须A,B两个字段值均相同才算一组。

1.4 聚合筛选（having）

对分组聚合后的结果进行进一步筛选

eg1 : 找出平均攻击力大于105的国家的前2名,显示国家名称和平均攻击力

select country,avg(attack) from sanguo 
group by country
having avg(attack)>105
order by avg(attack) DESC
limit 2;

注意

1. having语句必须与group by联合使用。
2. having语句存在弥补了where关键字不能与聚合函数联合使用的不足,where只能操作表中实际存在的字段。
3. where在分组前进行条件过滤，having在分组后进行条件过滤。
4. 使用where的地方都可以用having替换。但是having可以使用分组函数，而where后不可以使用。

1.5 排序（order by）

ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。

使用 ORDER BY 子句将查询数据排序后再返回数据：

SELECT field1, field2,...fieldN from table_name1 where field1
ORDER BY field1 [ASC [DESC]]

默认情况ASC表示升序，DESC表示降序

select * from class_1 where sex='m' order by age desc;

复合排序：对多个字段排序，即当第一排序项相同时按照第二排序项排序

select * from class_1 order by score desc,age;

1.6 限制（limit）

LIMIT 子句用于限制由 SELECT 语句返回的数据数量 或者 UPDATE,DELETE语句的操作数量

带有 LIMIT 子句的 SELECT 语句的基本语法如下：

SELECT column1, column2, columnN 
FROM table_name
WHERE field
LIMIT [num]

2 多表查询

2.1 联合查询（union）

UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

UNION 操作符语法格式：

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

== 默认UNION后卫 DISTINCT==表示删除结果集中重复的数据。如果使用ALL则返回所有结果集， 包含重复数据。

select * from class_1 where sex='m' UNION ALL select * from class_1 where age > 9;

2.2 子查询

• 定义 ： 当一个select语句中包含另一个select 查询语句，则称之为有子查询的语句

• 子查询出现的位置：

1. from 之后 ，此时子查询的内容作为一个新的表内容，再进行外层select查询

   select name from (select * from class_1 where sex='m') as s where s.score > 90;
   

    >注意：  需要将子查询结果集重命名一下，方便where子句中的引用操作
   

2. where字句中，此时select查询到的内容作为外层查询的条件值

   select *  from class_1 where age = (select age from class_1 where name='Tom');
   

   注意：

   1. 子句结果作为一个值使用时，返回的结果需要一个明确值，不能是多行或者多列。
   2. 如果子句结果作为一个集合使用，即where子句中是in操作，则结果可以是一个字段的多个记录。

2.3 表连接

如果多个表存在一定关联关系，可以多表在一起进行查询操作，其实表的关联整理与外键约束之间并没有必然联系，但是基于外键约束设计的具有关联性的表往往会更多使用关联查询查找数据。

• 简单多表查询

多个表数据可以联合查询，语法格式如下：

select  字段1,字段2... from 表1,表2... [where 条件]

e.g.
select * from dept,person where dept.id = person.dept_id;

• 内连接
  内连接查询只会查找到符合条件的记录，其实结果和表关联查询是一样的,官方更推荐使用内连接查询。

SELECT 字段列表
    FROM 表1  INNER JOIN  表2
ON 表1.字段 = 表2.字段;

![在这里插入图片描述](https://img-blog.csdnimg.cn/bd709a71ad654dc6986f57d7b0347c91.png)

select * from person inner join  dept  on  person.dept_id =dept.id;

• 笛卡尔积

笛卡尔积就是将A表的每一条记录与B表的每一条记录强行拼在一起。所以，如果A表有n条记录，B表有m条记录，笛卡尔积产生的结果就会产生n*m条记录。

select * from person inner join  dept;

• 左连接 : 左表为主表，显示右表中与左表匹配的项
  

SELECT 字段列表
    FROM 表1  LEFT JOIN  表2
ON 表1.字段 = 表2.字段;

select * from person left join  dept  on  person.dept_id =dept.id;

# 查询每个部门员工人数
select dname,count(name) from dept left join person on dept.id=person.dept_id group by dname;

• 右连接 ：右表为主表，显示左表中与右表匹配的项
  

SELECT 字段列表
    FROM 表1  RIGHT JOIN  表2
ON 表1.字段 = 表2.字段;

select * from person right join  dept  on  person.dept_id =dept.id;

注意：我们尽量使用数据量大的表作为基准表，即左表

3 视图

3.1 视图概念

视图是存储的查询语句,当调用的时候,产生结果集,视图充当的是虚拟表的角色。其实视图可以理解为一个表或多个表中导出来的表，作用和真实表一样，包含一系列带有行和列的数据 视图中，用户可以使用SELECT语句查询数据，也可以使用INSERT，UPDATE，DELETE修改记录，视图可以使用户操作方便，并保障数据库系统安全，如果原表改名或者删除则视图也失效。

3.2 视图作用与缺点

• 作用

1. 是对数据的一种重构，不影响原数据表的使用。

2. 简化高频复杂操作的过程，就像一种对复杂操作的封装。

3. 提高安全性，可以给不同用户提供不同的视图。

4. 让数据更加清晰。

• 缺点

1. 视图的性能相对较差，从数据库视图查询数据可能会很慢。

3.3 视图表的增删改查操作

视图的增删改查操作与一般表的操作相同，使用insert update delete select即可，但是原数据表的约束条件仍然对视图产生作用。

3.3.1 创建视图

语法结构：

CREATE [OR REPLACE] VIEW [view_name] AS [SELECT_STATEMENT];

释义：

CREATE VIEW： 创建视图
OR REPLACE : 可选，如果添加原来有同名视图的情况下会覆盖掉原有视图
view_name ： 视图名称
SELECT_STATEMENT ：SELECT语句

e.g.
create view good_stu as select name,age,score from class where score>85;

3.3.2 删除视图

drop view [IF EXISTS] 视图名；

IF EXISTS 表示如果存在，这样即使没有指定视图也不会报错。

drop view if exists hobby_stu;

3.2.3 修改视图

参考创建视图，将create关键字改为alter

alter view  good_stu as select name,age,score from class;

3.3.4 查看现有视图

show full tables in stu where table_type like 'VIEW';