MySQL-查询

 基本查询

select 
  [all|distinct]
  <目标列的表达式1> [别名],
  <目标列的表达式2> [别名]...
from <表名或视图名> [别名],<表名或视图名> [别名]...
[where<条件表达式>]
[group by <列名> 
[having <条件表达式>]]
[order by <列名> [asc|desc]]
[limit <数字或者列表>];

分组查询 -group by

分组查询是指使用group by字句对查询信息进行分组。

格式：

 select 字段1,字段2… from 表名 group by 分组字段 having 分组条件;

-- 1 统计各个分类商品的个数
select category_id ,count(*) from product group by category_id ;

1、count函数对null值的处理

如果count函数的参数为星号（*），则统计所有记录的个数。而如果参数为某字段，不统计含null值的记录个数。

2、sum和avg函数,max和min函数对null值的处理

这几个函数忽略null值的存在，就好象该条记录不存在一样。

• 分组之后的条件筛选 -having

分组之后对统计结果进行筛选的话必须使用 having ，不能使用 where

where 子句用来筛选 FROM 子句中指定的操作所产生的行

group  by  子句用来分组 WHERE 子句的输出。

having 子句用来从分组的结果中筛选行

格式

select 字段1,字段2… from 表名 group by 分组字段 having 分组条件;

-- 2.统计各个分类商品的个数,且只显示个数大于4的信息

select category_id ,count(*) from product group by category_id having count(*) > 1;

分页查询 -limit

格式

-- 方式1-显示前n条

select 字段1，字段2... from 表明 limit n

-- 方式2-分页显示

select 字段1，字段2... from 表明 limit m,n

m: 整数，表示从第几条索引开始，计算方式 （当前页-1）*每页显示条数

n: 整数，表示查询多少条数据

-- 查询product表的前5条记录

select * from product limit 5

-- 从第4条开始显示，显示5条

select * from product limit 3,5

多表查询

多表查询就是同时查询两个或两个以上的表，因为有的时候用户在查看数据的时候,需要显示的数据来自多张表.

交叉连接查询 [ 产生笛卡尔积，了解 ]

      语法：select * from A,B; 

• 交叉连接查询返回被连接的两个表所有数据行的笛卡尔积

• 笛卡尔积 可以理解为一张表的每一行去和另外一张表的任意一行进行匹配

• 假如 A 表有 m 行数据， B 表有 n 行数据，则返回 m*n 行数据

• 笛卡尔积会产生很多冗余的数据，后期的其他查询可以在该集合的基础上进行条件筛选

内连接查询 ( 使用的关键字 inner join  -- inner 可以省略 )

隐式内连接（SQL92标准）：select * from A,B where 条件;

显示内连接（SQL99标准）：select * from A inner join B on 条件;

外连接查询 ( 使用的关键字 outer join -- outer 可以省略 )

        左外连接：left outer join

            select * from A left outer join B on 条件;

        右外连接：right outer join

            select * from A right outer join B on 条件;

        满外连接: full outer join

             select * from A full outer join B on 条件;

子查询

       select的嵌套

子查询就是指的在一个完整的查询语句之中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式，通俗一点就是包含select嵌套的查询。

1.ALL 关键字

select …from …where c > all(查询语句)

--等价于：

select ...from ... where c > result1 and c > result2 and c > result3

-- 查询年龄大于‘1003’部门所有年龄的员工信息

select * from emp3 where age > all(select age from emp3 where dept_id = '1003’);

-- 查询不属于任何一个部门的员工信息

select * from emp3 where dept_id != all(select deptno from dept3);

•ALL表示指定列中的值必须要大于子查询集的每一个值，即必须要大于子查询集的最大值；如果是小于号即小于子查询集的最小值。同理可以推出其它的比较运算符的情况。

2.ANY/SOME关键字

select …from …where c > any(查询语句)

--等价于：

select ...from ... where c > result1 or c > result2 or c > result3

• 表示制定列中的值要大于子查询中的任意一个值，即必须要大于子查询集中的最小值。同理可以推出其它的比较运算符的情况。

• SOME 和 ANY 的作用一样， SOME 可以理解为 ANY 的别名

-- 查询年龄大于‘1003’部门任意一个员工年龄的员工信息

select * from emp3 where age > any(select age from emp3 where dept_id = '1003’);

4.IN关键字

select …from …where c in(查询语句)

--等价于：

select ...from ... where c = result1 or c = result2 or c = result3

• IN 关键字，用于判断某个记录的值，是否在指定的集合中

• 在 IN 关键字前边加上 not 可以将条件反过来

-- 查询研发部和销售部的员工信息，包含员工号、员工名字

select eid,ename,t.name from emp3 where dept_id in (select deptno from dept3 where name = '研发部' or name = '销售部') ;

5.EXISTS关键字

select …from …where exists(查询语句)

•

• 该子查询如果“有数据结果” ( 至少返回一行数据 ) ， 则该 EXISTS () 的结果为“ true” ，外层 查询 执行

• 该子查询如果“没有数据结果”（没有任何数据返回），则该 EXISTS() 的结果为“ false” ，外层查询不执行

• EXISTS 后面的子查询不返回任何实际数据，只返回真或假，当返回真时 where 条件成立

• 注意， EXISTS 关键字，比 IN 关键字的运算效率高，因此，在实际开发中，特别是大数据量时，推荐使用 EXISTS 关键字

•

-- 查询公司是否有大于60岁的员工，有则输出

select * from emp3 a where exists(select * from emp3 b where a.age > 60);

-- 查询有所属部门的员工信息

select * from emp3 a where exists(select * from dept3 b where a.dept_id = b.deptno);

自关联查询

MySQL有时在信息查询时需要进行对表自身进行关联查询，即一张表自己和自己关联，一张表当成多张表来用。注意自关联时表必须给表起别名。

select 字段列表 from 表1 a , 表1 b where 条件;

或者

select 字段列表 from 表1 a [left] join 表1 b on 条件;

-- 创建表,并建立自关联约束

create table t_sanguo(

    eid int primary key ,

    ename varchar(20),

    manager_id int,

 foreign key (manager_id) references t_sanguo (eid)  -- 添加自关联约束

);

-- 添加数据

insert into t_sanguo values(1,'刘协',NULL);

insert into t_sanguo values(2,'刘备',1);

insert into t_sanguo values(3,'关羽',2);

insert into t_sanguo values(4,'张飞',2);

insert into t_sanguo values(5,'曹操',1);

insert into t_sanguo values(6,'许褚',5);

insert into t_sanguo values(7,'典韦',5);

insert into t_sanguo values(8,'孙权',1);

insert into t_sanguo values(9,'周瑜',8);

insert into t_sanguo values(10,'鲁肃',8);

-- 进行关联查询

-- 1.查询每个三国人物及他的上级信息，如:  关羽  刘备

select * from t_sanguo a, t_sanguo b where a.manager_id = b.eid;