MySql

对于正常数据库操作来说，查询的数据的频率，远远超过其他操作。本文会由渐入深的介绍常用的查询方法。
语法为：

SELECT
    字段列表
FROM
    表名字段
WHERE
    条件列表
GROUP BY
    分组字段列表
HAVING
    分组后的条件列表
ORDER BY
    排序字段列表
LIMIT
    分页参数

执行顺序为： FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORGDER BY -> LIMIT

查询

先造数据

CREATE TABLE `tb_user` (
                           `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                           `name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',
                           `email` VARCHAR(50) COMMENT'用户邮箱',
                           `phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',
                           `gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT '性别（0：男;1:女）',
                           `password` VARCHAR(100) COMMENT '密码',
                           `age` TINYINT(4) DEFAULT'0'  COMMENT '年龄',
                           `class` TINYINT(4) DEFAULT'0'  COMMENT '班级',
                           `card` bigint(20) DEFAULT'0'  COMMENT '身份证',
                           `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
                           `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                           PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表';

来个十万条数据

-- 插入10万数据.
DELIMITER $$
-- 写函数之前必须要写，标志
CREATE FUNCTION mock_data4 ()
    RETURNS INT
BEGIN
    DECLARE num INT DEFAULT 100000;
    DECLARE i INT DEFAULT 0;
    WHILE i<num DO
            INSERT INTO `tb_user`(`name`,`email`,`phone`, `gender`,`password`, `age`, `class`,`card`)
            VALUES(
                       CONCAT('用户',i+1),
                       CONCAT(FLOOR(RAND()*999999999),"@qq.com"),
                       CONCAT('13',CEILING(RAND()*900000000+100000000)),
                       FLOOR(RAND()*2),
                       CONCAT('pwd',FLOOR(RAND()*999999999)),
                       FLOOR(RAND()*50),
                       FLOOR(RAND()*100),
                       CONCAT('4310281990',FLOOR(RAND()*99999999))
                   );
            SET i=i+1;
        END WHILE;
    RETURN i;
END;

SELECT mock_data4() -- 执行此函数 生成十万条数据

基本查询

• 查询多个字段

  select name,email,age from tb_user;

• 查询全部字段

  select * from tb_user;

  注意：在实际开发中，不要查询全部字段，按照自己的业务逻辑去查询对应的字段，因为查询全部字段会影响效率，哪怕你的表结构只有几个字段，但是无法保证后续迭代中不会加，这样你就把多余字段查询出来，浪费性能。

• 别名 as

  select name as '姓名' from tb_user;

  也可以不带as，建议带as

  select name '姓名',age '年龄' from tb_user;

• 去重查询

  select distinct name '姓名' from tb_user;

条件查询

SELECT 字段列表 FROM 表名 WHERE 条件列表;

-- 年龄等于30
select * from tb_user where age = 30;

-- 没有身份证
select * from tb_user where card is null;

-- 有身份证
select * from tb_user where card is not null;

-- 年龄在20到30之间
select * from tb_user where age  >=20 and age <=30;
select * from tb_user  where age between 20 and 30;

-- 年龄等于25或30或35,且性别为男
select * from tb_user where age in (20,30,40) and gender = 0;

-- 姓名为三个字
select * from tb_user where name like '___';

-- 身份证最后为1
select * from tb_user where card like '%1';
select * from tb_user where card like '________________1'; # 这个有点蠢了

聚合函数

聚合函数，都是作用于某一列，将一列数据作为一个整体，进行纵向计算。

语法： SELECT 聚合函数(字段列表) FROM 表名;

注意：所有的null值，不参与聚合函数的运算

常见的如下：

分组查询

语法：SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后的过滤条件 ];

where 和 having 的区别：

• 执行时机不同：where是分组之前进行过滤，不满足where条件不参与分组；having是分组后对结果进行过滤。
• 判断条件不同：where不能对聚合函数进行判断，而having可以。

注意：

• 执行顺序 ： where > 聚合函数 > having
• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义，且有可能会报错

# 根据性别分组，统计男和女用户的数量
select gender,count(*) from  tb_user group by gender;

# 根据性别分组，统计男、女用户的平均年龄
select gender,avg(age) from tb_user group by gender;

# 查询小于年龄45的用户，并根据班级分组，并获取班级序号小于10数据,且班级的人生要大于900人。
select class,count(*) as classNum from tb_user where age < 45 group by class having class<10 and classNum>900;

排序查询

日常开发中，这个比较常见的

语法：SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;
如果多字段排序，当第一个字段值相同时，才会根据第二字段排序。
排序方式：ASC: 升序（默认，可以省略）,DESC: 降序

# 查询用户年龄大于20，且根据年龄进行升序
select name,age from tb_user where age >20 order by age asc;

# 查询女性用户，并根据创建时间倒序
select name,gender,create_time from tb_user where gender = 0 order by create_time desc ;

# 根据年龄进行升序，年龄相同再按创建时间倒序。
select name,age,create_time from tb_user order by age asc , create_time desc;

分页查询

这个，在开发中也是常用的，虽然都是使用框架，但有必要了解下。
语法：SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;
注意：

• 起始索引从0开始，起始索引算法为： (查询页码-1) * 每页记录数 ，如每页显示10条，那么第二页的起始索引就是 (2-1) * 10,则索引为10。
• 查询是从第一页开始，起始索引可以省略。

# 查询第一页用户数据，每页展示30条记录，
select * from tb_user limit 0,10;
#或者
select * from tb_user limit 10;

# 查询第10也用户数据，每页展示20条记录
select * from tb_user limit 180,20;

函数

如同刚刚的聚合函数一样，也是函数：一段可以直接被另一段程序调用的程序或代码。
mysql的函数，常用的包含为：字符串函数、数值函数、日期函数、流程函数。

字符串函数

常用的如下，如果有java或者其他语言的基础，这些函数名应该不陌生：

除了查询，在更新的时候也可以利用函数，比如把所有的手机号码前面都改为+86。

update tb_user set phone = lpad(phone,14,'+86')

数值函数

常见函数：

使用函数生成六位的随机码

select lpad(round(rand()*1000000,0),6,0);

日期函数

常用函数：

查询所有用户的注册天数，并且安装天数倒序

select name,datediff(curdate(),create_time) as entry_days from tb_user order by entry_days;

流程函数

常见的一种函数，在sql语句中实现条件筛选，从而提高语句的效率。

常用函数：

举例

• if

select if (false,'ok','error');// error
                               
select if(true,'ok','error');//ok

• if null

select ifnull('ok','defalut'); // ok

select ifnull(null,'default');// default

select ifnull('','defalut');// ''，因为空字符串非null

• case
  查询用户名称、手机号，如果手机号是138开头,则为移动，如果是139或者137开头 则为联通，其他为电信

select
    name,
    phone,
    case
        when phone like '+86138%' then '移动'
        when phone like '+86139%' or phone like '+86137' then '联通'
        else '电信' end as service
from tb_user;

• case expr
  查询用户表，如果gender为0显示男，1显示女，且age如果<15为少年，15<age<20为青少年，age>21为青年

select
    name,
   case gender when 0 then '男' when 1 then '女' end as 'sex',
   case when age<15 then '少年' when 15<age or age<20 then '青少年' else '青年' end as 'group',
   age
from tb_user;

多表查询

常用的查询，一般分为三种：一对一、一对多、多对多。

• 一对多

  一般是在多的一方建立外键，指向一的一方的主键。

• 多对多

  多对多，需要建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。

• 一对一

  一对一，通常用户做单表的拆分，如用户和用户详情的关系。
  任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)，一般都是在旁支表，比如用户表User，和用户扩展表extend，则在extend中加入user_id。

表结构为：

create table tb_person
(
    id      bigint auto_increment
        primary key,
    name    varchar(255)  not null,
    age     int           null,
    email   varchar(255)  not null,
    status  int default 1 null,
    gender  int           null,
    dept_id int           null,
    leader_id int           null comment '领导id',
    constraint email
        unique (email),
    constraint tb_person_dept__fk
        foreign key (dept_id) references tb_dept (id)
)
    comment '人员表' charset = utf8;
    
create table tb_dept
(
    id   int auto_increment
        primary key,
    name varchar(50) not null
)
    comment '部门' charset = utf8;

笛卡尔积

如我们查询select * from tb_person,tb_dept;，则会查出来的数据是 tb_person表的所有数据N和tb_dept的所有数据M的乘积。NxM
笛卡尔积：两个集合N集合和M集合的所有组合情况（在多表查询时，需要消除无效的笛卡尔积）
消除无效的笛卡尔积，则只是需要把关联关系执行起来：
select * from tb_person, tb_dept where tb_person.dept_id = tb_dept.id;

内连接

内连接查询的是两张表交集的部分。包含了两种类型链接：

• 隐式内连接

  SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;

  select * from tb_person, tb_dept where tb_person.dept_id = tb_dept.id;
  

• 显示内连接

  显示内连接性能较高，在工作用也比较常用。

  SELECT 字段列表 FROM 表1 JOIN 表2 ON 连接条件 ...;

    select p.name,d.name from tb_person p join tb_dept d on p.dept_id=d.id;
  

外连接

• 左外连接

  相当于查询表1(左表)的所有数据以及包含 表1和表2的交集部分数据。

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

  -- 查询person表的所有数据以及对应的部门信息。
  select p.*,d.name from tb_person p left join tb_dept d on p.dept_id = d.id;
  

  在实际开发中，左外连接会使用的更多一点。

• 右外连接

  相当于查询表2(右表)的所有数据以及包含 表1和表2的交集部分数据。

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

  -- 查询人员和部门的所有信息
  select d.*,p.* from tb_person p right join tb_dept d on p.dept_id = d.id;
  

自连接

自连接查询，可以是内连接查询，也可以是外连接查询。
自连接，看成两张表更加容易理解。

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;

```sql
-- 查询员工表以及员工的leader信息，如果员工没有leader，则不需要查询。(如果没有leader也需要查询出来，使用外连接left join即可)
select p1.id,p1.name,p1.leader_id,p2.name leadname from tb_person p1 join tb_person p2 on p1.leader_id=p2.id;
```

联合查询 union

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

SELECT 字段列表 FROM 表A ... UNION [ALL] SELECT 字段列表 FROM 表B ...

-- 查询年龄大于40和性别为男的人员,并且去重
select * from tb_person where age >=40
union -- 如果不去重则使用union all， 这样可能会有重复的数据
select * from tb_person where gender = 1;

注意：使用union 必须查询的字段要完全一致，如一个是*,一个只是查询name，这两个合并则会报错。

嵌套查询(子查询)

sql语句中嵌套select语句，成为嵌套查询，也成为子查询。

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2);
子查询的外部语句可以是 insert、update、delete、select中的任何一个。一般常见的就是select。
根据子查询的结果不同，可以分为：

• 标量子查询(子查询的结果为单个值)

  常用的操作符：=、<>、>、>=、<、<=

  -- 查询部门为研发部的人员
  select * from tb_person where tb_person.dept_id =  (select id from tb_dept where tb_dept.name='研发部');
  

• 列子查询(子查询的结果为一列)

  返回的结果是一列，可能是多行。这种子查询成为列子查询。
  常用的操作符:

  操作符	描述
  IN	在指定的集合范围内，多选一
  NOT IN	不在指定的集合范围内
  ANY	子查询返回列表中，有任意一个满足即可
  SOME	与ANY等同，使用SOME的地方都可以使用ANY
  ALL	子查询返回列表的所有值都必须满足

  -- 查询部门为销售部和研发部的所有员工信息
  select * from tb_person where tb_person.dept_id in (select id from tb_dept where tb_dept.name='研发部' or tb_dept.name='市场部');
  

  -- 查询比研发部所有人年龄都大的人员（如果是任意一人，则把all换成any）
  select * from tb_person where age > all(select age from tb_person where tb_person.dept_id =  (select id from tb_dept where tb_dept.name='研发部'));
  

• 行子查询(子查询的结果为一行)

  子查询返回的结果是一行，可以是多列，这种子查询为行子查询.
  常用的操作符为 =、<>、IN、NOT IN

  -- 查询和zhangsan的age和status一样的人员
  -- where后面可以加括号括起来如：
  select *
  from tb_person
  where (age, status) = (1,1);
  
  -- 那么子查询就是
  select *
  from tb_person
  where (age, status) = (select age, status from tb_person where name = 'zhangsan');
  

• 表子查询(子查询结果为多行多列)

  子查询返回的结果为多行多列，为表子查询
  常用的操作符:IN

  -- 查询与zhangsan/lisi的age和status一样的人员信息
  
  select *
  from tb_person
  where (age, status) in (select age, status from tb_person where name = 'zhangsan' or name='lisi');
  

  -- 查询年龄大于30员工，以及部门信息
  -- 可以先把年龄大于30的查询后作为一个新的表，再新的表里面再做查询
  select * from (select * from tb_person where age > 30) as t left join tb_dept as d on t.dept_id=d.id;