数据库----mysql查询

淡淡的烟草味﹌ 2022-03-19 05:57 262阅读 0赞

# 查询 #

## 创建数据库、数据表 ##

--创建数据库
    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.gender,s.name from students as s;

*  在select后面列前面加入distinct可以消除重复的行

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

# 条件 #

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

*  语法如下

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

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

## 比较运算符 ##

*  等于：=
 *  大于：>
 *  小于：<
 *  大于等于：>=
 *  小于等于：<=
 *  不等于：！=或者<>

--查询编号大于3的学生
    select * from students where id>3;
    
    
    --查询编号不大于4的学生
    select * from students where id<=4;
    
    
    --查询姓名不是黄蓉的学生
    select * from students where name!='黄蓉';
    
    
    --查询没有被删除的学生
    select * from students where is_delete=0;

## 逻辑运算符 ##

*  and
 *  or
 *  not

--查询编号大于3的女同学
    select * from students where id>3 and gender='女';
    
    
    --查询编号小于4或者没被删除的学生
    select * from students where id<4 or is_delete=0;

## 模糊查询 ##

*  like
 *  %表示任意多个字符串
 *  \_表示一个任意字符

--查询姓黄的学生
    select * from students where name like '黄%';
    
    
    --查询姓黄并且‘名’是一个字的学生
    select * from students where name like '黄_';
    
    
    --查询姓黄或叫靖的的学生
    select * from students where name like '黄%' or name like '%靖';

## 范围查询 ##

*  in表示在一个非连续的范围内

--查询编号是1或者3或者8的学生
    select * from students where id in(1,3,8)

*  between...and...表示在一个连续的范围内

--查询编号为3至8的学生
    select * from students where id between 3 and 8;
    
    
    --查询编号是3至8的男生
    select * from students where (id between 3 and 8) and gender='男';

## 空判断 ##

*  注意：null与‘’是不同的
 *  判断is null

*  查询没有填写身高的男生

select * from students where height is null and gender='男';

*  查询填写身高的学生

select * from students where height is not null;

## 优先级 ##

*  优先级由高到低的顺序为：小括号、not、比较运算符、逻辑运算符
 *  and比or先运算，如果再计算过程中希望先计算or，需要结合括号使用

# 排序 #

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

语法：

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

说明：

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

*  查看未删除男生信息，按学号降序

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

*  查看未删除男生信息，按名称升序

select * from students where gender='男' and is_delete=0 order by name;

*  显示所有的学生信息，先按照年龄从大到小排列，如果年龄相同，那么就按身高从高到矮排序

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

## 聚合函数 ##

为了快速得到统计数据，经常会用到下面的5个聚合函数

## 总数 ##

*  count(\*)表示计算总行数，括号中写星与列名，结果是相同的

--查询学生总数
    select count(*) from students;

## 最大值 ##

*  max(列名)表示求此列的最大值

--查询女生的编号的最大值
    select max(id) from students where gender='女';

## 最小值 ##

*  min(列名)表示求此列的最小值

--查询未删除的学生最小编号
    select min(*) from students where is_delete=0;

## 求和 ##

*  sum(列名)表示求此列的和

--查询男生的总年龄
    select sum(age) from students where gender='男';
    
    
    --平均年龄
    select sum(age)/count(*) from students where gender='男';

## 平均值 ##

*  avg(列名)表示求此列的平均值

--查询未删除女生的编号平均值
    select avg(id) from students where gender='女' and is_delete=0;

# 分组 #

## group by ##

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

select gender from students group by gender;

*  根据gender字段来进行分组，gender字段的全部值有4个，所以分为四组，当group  
    by单独使用的时候，只显示出每组的第一条记录，所以group by单独使用时意义不大，group by只有和其他的聚合户函数一起用才有意义，当使用select name from students group by gender;会报错，证明并不是输出每一组的第一条记录

## group by + group\_concat() ##

*  group\_concat(字段名)可以作为一个输出字段来使用，字段名中可以有一个或者几个字段，也可以包含一些字符
 *  表示分组之后，根据分组结果，使用group\_concat()来放置每一组的某字段的值的集合

--按照性别分组，然后输出每个分组中的所有的人的名字
    select gender,group_concat(name) from students group by gender;
    
    
    
    --按照性别分组，然后输出每个分组中的人的编号
    select gender,group_concat(id) from students group by gender;
    
    
    
    --按照性别进行分类，输出姓名和年龄
    select gender,group_concat(name,age) from students where gender=1 group by gender;

## group by + 聚合函数 ##

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

--按照性别进行分类，然后求分组的年龄的平均值
    select gender,avg(age) from students group by gender;

## group by + having ##

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

select gender,count(*) from students group by gender having count(*)>2;

## group by + with rollup ##

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

select gender,count(*) from students group by gender with rollup;

# 获取部分行 #

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

## 语法 ##

select * from 表名 limit start,count;

*  说明：从start开始，获取count条数据

--查询前三行的学生
    select * from students where gender=1 limit 0,3;

## 示例：分页 ##

*  已知：每页显示M条数据，当前显示第n页
 *  求总页数：此段逻辑后面会在Python中实现
    
     *  查询总条数P1
     *  使用P1除以m得到P2
     *  如果整除则P2为总数页
     *  如果不整除则P2+1为总数页
 *  求第n页的数据

select * from students where is_delete=0 limit (n-1)*m,m;

# 连接查询 #

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

mysql支持三种类型的连接查询，分别为：

*  内连接查询：查询的结果为两个表匹配到的数据，也就是取交集
    
    ![Image 1][]
 *  右连接查询：查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用null填充
    
    ![Image 1][]
 *  左连接查询：查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用null填充。右连接就是左连接的一种扩展，只要把表的名字进行互换即可
    
    ![Image 1][]

## 语法 ##

select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列;

*  使用内连接查询班级表与学生表

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

*  使用左连接查询班级表与学生表
 *  使用as为表起别名，目的是编写简单

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

*  使用右连接查询班级表与学生表

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

*  查询学生姓名及班级名称并按照班级的名字进行排序

select s.name,c.name from students as s inner join classes as c on s.cld_id = c.id order by c.nme;

*  查询没有对应班级信息的学生

select * from students left join classes on students.cls_id = classes.id where classes.id is null;

# 自关联 #

*  设计省份信息的表结构provinces
    
     *  id
     *  ptitle
 *  设计城市信息的表结构citys
    
     *  id
     *  ctitle
     *  proid
 *  citys表的proid表示城市所属的省，对应着provinces的表的id值

**问题**：能不能将两张表合成一张表？

**思考**：观察两张表发现，citys表比provinces表多一个列proid，其他列的类型都是相同的

**意义**：存储的都是地区信息，而且每种信息的数据量有限，没必要增加一个新表，或者将来还要存储区、乡镇信息，都增加新表的开销太大

**答案**：定义表areas，结构入下：

*  id
 *  atitle
 *  pid

说明：

*  因为省没有所属的省份，所以可以填写null
 *  城市所属的省份pid，填写省所对应的编号id
 *  这就是自关联。表中的某一列，关联了这个表中的另外一列，但是他们的业务逻辑含义是不一样的，城市信息的pid引用的是省信息的id
 *  在这个表中，结构不变，可以添加区县、乡镇街道、村社区等信息、

## 创建areas表的语句入下： ##

create table areas(
        aid int primary key auto_increment not null,
        atitle varchar(20),
        pid int
    );

*  从sql文件中导入数据

source areas.sql;   # sql文件是之前准备好的

*  查询一共有多少省

select count(*) from areas where pid is null;

*  查询省的名称为山西省的所有城市

select city.* from areas as city inner join areas as province on city.pid=province.id where province.atitle='山西省';

# 子查询 #

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

## 主查询 ##

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