SQL查询

查询前准备

创建数据库、数据表

-- 创建数据库
create database python_test_1 charset=utf8;

-- 使用数据库
use python_test_1;

-- students表
create table students(
    id int unsigned primary key auto_increment not null,
    name varchar(20) default '',
    age tinyint unsigned default 0,
    height decimal(5,2),
    gender enum('男','女','中性','保密') default '保密',
    cls_id int unsigned default 0,
    is_delete bit default 0
);

-- classes表
create table classes (
    id int unsigned auto_increment primary key not null,
    name varchar(30) not null
);

准备数据

-- 向students表中插入数据
insert into students values
(0,'小明',18,180.00,2,1,0),
(0,'小月月',18,180.00,2,2,1),
(0,'彭于晏',29,185.00,1,1,0),
(0,'刘德华',59,175.00,1,2,1),
(0,'黄蓉',38,160.00,2,1,0),
(0,'凤姐',28,150.00,4,2,1),
(0,'王祖贤',18,172.00,2,1,1),
(0,'周杰伦',36,NULL,1,1,0),
(0,'程坤',27,181.00,1,2,0),
(0,'刘亦菲',25,166.00,2,2,0),
(0,'金星',33,162.00,3,3,1),
(0,'静香',12,180.00,2,4,0),
(0,'郭靖',12,170.00,1,4,0),
(0,'周杰',34,176.00,2,5,0);

-- 向classes表中插入数据
insert into classes values (0, "python_01期"), (0, "python_02期");

常规查询操作

• 查询所有字段

  select * from 表名;
  例：
  select * from students;

• 查询指定字段

  select 列1,列2,... from 表名;
  例:
  select name from students;

• 使用 as 给字段起别名

  例如：
  select id as 序号, name as 名字, gender as 性别 from students;

• 可以通过 as 给表起别名

  -- 如果是单表查询 可以省略表明
  select id, name, gender from students;
  
  -- 表名.字段名
  select students.id,students.name,students.gender from students;
  
  -- 可以通过 as 给表起别名 
  select s.id,s.name,s.gender from students as s;

消除重复行

• 在select后面列前使用distinct可以消除重复的行

  select distinct 列1,... from 表名;
  例：
  select distinct gender from students;

条件（where）

使用where子句对表中的数据筛选，结果为true的行会出现在结果集中

• 语法如下：

  select * from 表名 where 条件;
  例：
  select * from students where id=1;

• where后面支持多种运算符，进行条件的处理
  • 比较运算符
  • 逻辑运算符
  • 模糊查询
  • 范围查询
  • 空判断

比较运算符

• 等于: =
• 大于: >
• 大于等于: >=
• 小于: <
• 小于等于: <=
• 不等于: != 或 <>

例1：查询编号大于3的学生

select * from students where id > 3;

例2：查询编号不大于4的学生

select * from students where id <= 4;

例3：查询姓名不是“黄蓉”的学生

select * from students where name != '黄蓉';

例4：查询没被删除的学生

select * from students where is_delete=0;

逻辑运算符

• and
• or
• not

例5：查询编号大于3的女同学

select * from students where id > 3 and gender=0;

例6：查询编号小于4或没被删除的学生

select * from students where id < 4 or is_delete=0;

模糊查询

• like
• %表示任意多个任意字符
• _表示一个任意字符

例7：查询姓黄的学生

select * from students where name like '黄%';

例8：查询姓黄并且“名”是一个字的学生

select * from students where name like '黄_';

例9：查询姓黄或叫靖的学生

select * from students where name like '黄%' or name like '%靖';

范围查询

• in表示在一个非连续的范围内
  例10：查询编号是1或3或8的学生

  select * from students where id in(1,3,8);

• between ... and ...表示在一个连续的范围内
  例11：查询编号为3至8的学生

  select * from students where id between 3 and 8;

  例12：查询编号是3至8的男生

  select * from students where (id between 3 and 8) and gender=1;

空判断

• 注意：null与’’是不同的
• 判空is null
  例13：查询没有填写身高的学生

  select * from students where height is null;

• 判非空is not null
  例14：查询填写了身高的学生

  select * from students where height is not null;

  例15：查询填写了身高的男生

  select * from students where height is not null and gender=1;

优先级说明

• 优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符
• and比or先运算，如果同时出现并希望先算or，需要结合()使用

排序（order by）

为了方便查看数据，可以对数据进行排序

语法：

select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]

说明：

• 将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推
• 默认按照列值从小到大排列（asc）
• asc从小到大排列，即升序
• desc从大到小排序，即降序

例1：查询未删除男生信息，按学号降序

select * from students where gender=1 and is_delete=0 order by id desc;

例2：查询未删除学生信息，按名称升序

select * from students where is_delete=0 order by name;

例3：显示所有的学生信息，先按照年龄从大–>小排序，当年龄相同时 按照身高从高–>矮排序

select * from students  order by age desc,height desc;

常用的聚合函数

为了快速得到统计数据，经常会用到如下5个聚合函数。
特点：多个数据输入，返回的是单个数据。

总数

• count(*)表示计算总行数，括号中写星与列名，结果是相同的
  例1：查询学生总数

  select count(*) from students;

最大值

• max(列)表示求此列的最大值
  例2：查询女生的编号最大值

  select max(id) from students where gender=2;

最小值

• min(列)表示求此列的最小值
  例3：查询未删除的学生最小编号

  select min(id) from students where is_delete=0;

求和

• sum(列)表示求此列的和
  例4：查询男生的总年龄

  select sum(age) from students where gender=1;
  
  -- 平均年龄
  select sum(age)/count(*) from students where gender=1;

平均值

• avg(列)表示求此列的平均值
  例5：查询未删除女生的编号平均值

  select avg(id) from students where is_delete=0 and gender=2;

集合查询

union all用于把两个或者多个select查询的结果集合并成一个（前提是列相同）

当使用union 时，mysql 会把结果集中重复的记录删掉，而使用union all ，mysql 会把所有的记录返回，且效率高于union

例子：一条语句，返回老师与学生的姓名与id信息

select uname,uid from student
union all
select uname,uid from teacher

分页 （limit）

分页的出现是为了当数据量过大时，在一页中查看数据是一件非常麻烦的事情

• 语法

  select * from 表名 limit start,count;
  
  从start开始，获取count条数据

• 例1：查询前3行男生信息

  select * from students where gender=1 limit 0,3;

• 算法
  pageIndex：当前页
  size：获取的数据量

  limit (pageIndex - 1) * size , size;
  
  第1页:0 3
  第2页:3 3
  第3页:6 3
  第4页:9 3

分组 （group by）

1. group by的含义:将查询结果按照1个或多个字段进行分组，字段值相同的为一组
2. group by可用于单个字段分组，也可用于多个字段分组

例子：

select * from students;
+----+-----------+------+--------+--------+--------+-----------+
| id | name      | age  | height | gender | cls_id | is_delete |
+----+-----------+------+--------+--------+--------+-----------+
|  1 | 小明      |   18 | 180.00 | 女     |      1 |           |
|  2 | 小月月    |   18 | 180.00 | 女     |      2 | 
         |
|  3 | 彭于晏    |   29 | 185.00 | 男     |      1 |           |
|  4 | 刘德华    |   59 | 175.00 | 男     |      2 | 
         |
|  5 | 黄蓉      |   38 | 160.00 | 女     |      1 |           |
|  6 | 凤姐      |   28 | 150.00 | 保密   |      2 | 
         |
|  7 | 王祖贤    |   18 | 172.00 | 女     |      1 | 
         |
|  8 | 周杰伦    |   36 |   NULL | 男     |      1 |           |
|  9 | 程坤      |   27 | 181.00 | 男     |      2 |           |
| 10 | 刘亦菲    |   25 | 166.00 | 女     |      2 |           |
| 11 | 金星      |   33 | 162.00 | 中性   |      3 | 
         |
| 12 | 静香      |   12 | 180.00 | 女     |      4 |           |
| 13 | 周杰      |   34 | 176.00 | 女     |      5 |           |
| 14 | 郭靖      |   12 | 170.00 | 男     |      4 |           |
+----+-----------+------+--------+--------+--------+-----------+



select gender from students group by gender;
+--------+
| gender |
+--------+
| 男     |
| 女     |
| 中性   |
| 保密   |
+--------+

根据gender字段来分组，gender字段的全部值有4个’男’,’女’,’中性’,’保密’，所以分为了4组 当group by单独使用时，只显示出每组的第一条记录, 所以group by单独使用时的实际意义不大

group by + group_concat()

1. group_concat(字段名)可以作为一个输出字段来使用，
2. 表示分组之后，根据分组结果，使用group_concat()来放置每一组的某字段的值的集合

select gender from students group by gender;
+--------+
| gender |
+--------+
| 男     |
| 女     |
| 中性   |
| 保密   |
+--------+

select gender,group_concat(name) from students group by gender;
+--------+-----------------------------------------------------------+
| gender | group_concat(name)                                        |
+--------+-----------------------------------------------------------+
| 男     | 彭于晏,刘德华,周杰伦,程坤,郭靖                                 |
| 女     | 小明,小月月,黄蓉,王祖贤,刘亦菲,静香,周杰                        |
| 中性   | 金星                                                       |
| 保密   | 凤姐                                                       |
+--------+-----------------------------------------------------------+


select gender,group_concat(id) from students group by gender;
+--------+------------------+
| gender | group_concat(id) |
+--------+------------------+
| 男     | 3,4,8,9,14       |
| 女     | 1,2,5,7,10,12,13 |
| 中性   | 11               |
| 保密   | 6                |
+--------+------------------+

group by + 集合函数

1. 通过group_concat()的启发，我们既然可以统计出每个分组的某字段的值的集合，那么我们也可以通过集合函数来对这个值的集合做一些操作

   select gender,group_concat(age) from students group by gender;
   +--------+----------------------+
   | gender | group_concat(age)    |
   +--------+----------------------+
   | 男     | 29,59,36,27,12       |
   | 女     | 18,18,38,18,25,12,34 |
   | 中性   | 33                   |
   | 保密   | 28                   |
   +--------+----------------------+
   
   
   分别统计性别为男/女的人年龄平均值
   select gender,avg(age) from students group by gender;
   +--------+----------+
   | gender | avg(age) |
   +--------+----------+
   | 男     |  32.6000 |
   | 女     |  23.2857 |
   | 中性   |  33.0000 |
   | 保密   |  28.0000 |
   +--------+----------+
   
   分别统计性别为男/女的人的个数
   select gender,count(*) from students group by gender;
   +--------+----------+
   | gender | count(*) |
   +--------+----------+
   | 男     |        5 |
   | 女     |        7 |
   | 中性   |        1 |
   | 保密   |        1 |
   +--------+----------+

group by + having

1. having 条件表达式：用来分组查询后指定一些条件来输出查询结果
2. having作用和where一样，但having只能用于group by

   select gender,count(*) from students group by gender having count(*)>2;
   +--------+----------+
   | gender | count(*) |
   +--------+----------+
   | 男     |        5 |
   | 女     |        7 |
   +--------+----------+

group by + with rollup

1. with rollup的作用是：在最后新增一行，来记录当前列里所有记录的总和

   select gender,count(*) from students group by gender with rollup;
   +--------+----------+
   | gender | count(*) |
   +--------+----------+
   | 男     |        5 |
   | 女     |        7 |
   | 中性   |        1 |
   | 保密   |        1 |
   | NULL   |       14 |
   +--------+----------+
   
   
   select gender,group_concat(age) from students group by gender with rollup;
   +--------+-------------------------------------------+
   | gender | group_concat(age)                         |
   +--------+-------------------------------------------+
   | 男     | 29,59,36,27,12                            |
   | 女     | 18,18,38,18,25,12,34                      |
   | 中性   | 33                                        |
   | 保密   | 28                                        |
   | NULL   | 29,59,36,27,12,18,18,38,18,25,12,34,33,28 |
   +--------+-------------------------------------------+

连接查询 （inner \ left \ right join … on）

当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回类似集合

• 例1：使用内连接查询班级表与学生表
  将两张表都作为主表，然后将两张表相等列的数据进行合并。
只返回两个表中联结字段相等的行

  select * from 
      students inner join classes 
  on
      students.cls_id = classes.id;

• 例2：使用左连接查询班级表与学生表
  此处使用了as为表起别名，目的是编写简单
  以左边的表为主表，右边的表为从表，查询出的结果集始终会将左边的表的所有数据都查出来
返回包括左表中的所有记录和右表中联结字段相等的记录

  select * from 
      students as s left join classes as c 
  on 
      s.cls_id = c.id;

• 例3：使用右连接查询班级表与学生表
  以右边的表为主表，左边的表为从表，查询出的结果集始终会将右边的表的所有数据都查出来
返回包括右表中的所有记录和左表中联结字段相等的记录

  select * from
      students as s right join classes as c 
  on 
      s.cls_id = c.id;

• 例4：查询学生姓名及班级名称

  select s.name,c.name from 
      students as s inner join classes as c 
  on
      s.cls_id = c.id;

子查询

在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句

-- 语法：
select (子查询)
from (子查询)
where (子查询)
group by
having (子查询)

• 主查询

  • 主要查询的对象,第一条 select 语句

• 主查询和子查询的关系

  • 子查询是嵌入到主查询中
  • 子查询是辅助主查询的,要么充当条件,要么充当数据源
  • 子查询是可以独立存在的语句,是一条完整的 select 语句

• 子查询分类

  • 标量子查询: 子查询返回的结果是一个数据(一行一列)使用率最高的。
  • 列子查询: 返回的结果是一列(一列多行)
  • 行子查询: 返回的结果是一行(一行多列)

• 标量子查询

  • 查询班级学生平均年龄
  • 查询大于平均年龄的学生

查询班级学生的平均身高

select * from students where age > (select avg(age) from students);

• 列级子查询

  • 查询还有学生在班的所有班级名字
    • 找出学生表中所有的班级 id
    • 找出班级表中对应的名字

      select name from classes where id in (select cls_id from students);

• 行级子查询

  • 需求: 查找班级年龄最大,身高最高的学生
  • 行元素: 将多个字段合成一个行元素,在行级子查询中会使用到行元素

    select * from students where (height,age) = (select max(height),max(age) from students);

• 子查询中特定关键字使用

  • in 范围
    • 格式: 主查询 where 条件 in (列子查询)

字符串函数

concat()：字符串拼接
例子：select concat("名字：",name) from students;

---

lower()：转小写
    select lower('HELLO') from dual;

---

upper()：转大写
    select upper('hello') from dual;

---

rpad()：向右字符串补充
    select rpad(name,10,"#") from students;

---

lpad()：向左字符串补充
    select lpad(name,10,"#") from students;

---

length()：字符串长度
    select length(name) from students; //名字的字符长度

---

reverse()：字符串反转
    select reverse("hello") from dual; //olleh

---

trim()：去除前后两端的空格
    select length('   hello    ') from dual;

---

rtrim()：去除右边的空格
    select length(rtrim('   hello    ')) from dual;

ltrim()：去除左边的空格
    select length(ltrim('   hello    ')) from dual;

---
replace()：替换字符串
    select replace(name,'S','*') from students; //name字段的"s"全部替换成"*"


substr()：截取字符串 下标从1开始
    select substr('hello',2,3) from dual; // ell

数字函数

ceil(n)：返回大于n的最小值。
    select ceil(10.1) from dual; //11

---

floor(n)：返回小于n的最大值
    select floor(10.9) from dual; //10

round(n,m)：保留n的指定的小数位,有四舍五入
    select round(3.14159265357,4) from dual; //3.1416

truncate(n,m):保留n的指定的小数位，没有四舍五入
    select truncate(3.14159265357,4) from dual;  // 3.1415

rand()：随机产生0到1的随机数
    select rand() from dual;

日期函数

now()：返回当前时间
    select now() from dual;

curdate():返回当前日期
    select curdate() from dual;   //2021-09-03

curtime():返回当前日期
    select curtime() from dual;  // 17:59:16

year(date)：返回date时间指定的年份
    select year(hiredate) from emp;

month(date)：返回date时间指定的月份
    select month(hiredate) from emp;

day(date)：返回date时间指定的日
    select day(hiredate) from emp;

---

timestempdiff(interval,date1,date2)：计算两个日期之间相隔的日期

interval可以是：year month day hour minute second

    select timestampdiff(day,'1999-10-02',now()) from dual;   // 8007

---

date_format(date,parrent)：设置日期的格式。
    select date_format(now(),'%Y年%m月%d日 %H时%i分%s秒') from dual;  //2021年09月03日 18时01分10秒

其他函数

空判断
    ifnull(v1,v2)：如果v1为空则返回v2

if判断
if(f,v1,v2)：如果f为真，返回v1 否则返回v2


select database()：查看当前操作的数据库;

select user();查看当前使用的账户

select version(); 查看当前数据库的版本;

总结

完整的select语句

select distinct *
from 表名
where ....
group by ... having ...
order by ...
limit start,count

执行顺序为：

from 表名
where ....
group by ...
select distinct *
having ...
order by ...
limit start,count

实际使用中，只是语句中某些部分的组合，而不是全部

逗号分割行转列

转载自https://www.cnblogs.com/jackszc/p/7448322.html

由于很多业务表因为历史原因或者性能原因，都使用了违反第一范式的设计模式。即同一个列中存储了多个属性值（具体结构见下表）。

这种模式下，应用常常需要将这个列依据分隔符进行分割，并得到列转行的结果。

ID	Value
1	tiny,small,big
2	small,medium
3	tiny,big

期望得到结果：

ID	Value
1	tiny
1	small
1	big
2	small
2	medium
3	tiny
3	big

#需要处理的表
create table tbl_name (ID int ,mSize varchar(100));
insert into tbl_name values (1,'tiny,small,big');
insert into tbl_name values (2,'small,medium');
insert into tbl_name values (3,'tiny,big');

#用于循环的自增表
create table incre_table (AutoIncreID int);
insert into incre_table values (1);
insert into incre_table values (2);
insert into incre_table values (3);

select a.ID,substring_index(substring_index(a.mSize,',',b.AutoIncreID),',',-1) 
from 
tbl_name a
join
incre_table b
on b.AutoIncreID <= (length(a.mSize) - length(replace(a.mSize,',',''))+1)
order by a.ID;

总结：
这种方法的缺点在于，我们需要一个拥有连续数列的独立表（这里是incre_table)。并且连续数列的最大值一定要大于符合分割的值的个数。

例如有一行的mSize 有100个逗号分割的值，那么我们的incre_table 就需要有至少100个连续行。

当然，mysql内部也有现成的连续数列表可用。如mysql.help_topic： help_topic_id 共有504个数值，一般能满足于大部分需求了。