数据表的操作

1 表的操作

-- 表结构操作
-- 查看所使用的数据库的所有数据表
show tables;

-- 创建数据表  约束中符号默认为有符号
create table `数据表名` (字段 类型 约束[, 字段 类型 约束]); 

create table `students`(
    id int unsigned unique primary key not null auto_increment,
    name varchar(30) not null, -- varchar表示可变长字符串 ,char表示不变字符串(会补空格)。例如char(3) 如果为'ab'则为 'ab '
    age tinyint unsigned,
    high decimal(5,2),
    gender enum("男","女","中性","保密") default "保密",
    cls_id int unsigned -- 最后一行不要加逗号,
);

-- 查看表的创建语句
show create table `数据表名`
>>>
CREATE TABLE `students` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `age` tinyint unsigned DEFAULT NULL,
  `high` decimal(5,2) DEFAULT NULL,
  `gender` enum('男','女','中性','保密') DEFAULT '保密',
  `cls_id` int unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

-- 查看数据表的结构
desc `数据表名`; 或者 describe `数据表名`;

-- 删除数据表
drop table `数据表名`;
drop table `students`;

-- 添加字段
alter table `数据表名` add `字段` `数据类型`;
alter table `students` add `brithday` `datetime`;

-- 修改字段-不重命名版
alter table `students` modify `brithday` date;

-- 修改字段-重命名版
alter table `students` change `brithday` `brith` date default "2000-01-01";

-- 删除字段
-- 会同时删除改字段所对应的数据。在实际中,一般不会删除数据,多做加法,少做减法
alter table `students` drop `brith`; 

2 表数据的增删改

-- 表的数据操作 --
数据的增(C:Create)、删(D:delete)、改(U:update)、查(R:Retrieve)
-- 增
-- 可以一次插入多行数据,不同行的数据用逗号隔开就可以。
-- 数据表中插入数据 --- 全部值
-- 这种情况要与数据库创建的字段 一 一对应
insert into `数据表名` values (0, "老王", 23, 187, "男", 2);
-- 数据表中插入数据 --- 部分值
insert into `数据表名` (`字段`[, `字段`]) values (值1, [值2]) [,(值1, [值2])];
-- 若数据是枚举类型的话,则可以用数字 1,2,3 ··· 代替。


-- 删
delete from `数据表名`
where 条件; -- 条件可以不要(会散出整个数据表的所有数据)
-- 删除表中的数据 不删除表
delete from `students` where `id` > 3;
-- 逻辑删除 加一个 is_delete 字段
alter table `数据表名` add `is_delete` bit default 0;
update `数据表名` set `is_delete` = 1 where 条件;

-- 改
update `数据表名` [as `自定义名称`] [, `数据表名` [as `自定义名称`] ]
set `要更新的字段` = 值 [, `要更新的字段`] -- 可以一次更新多个值
WHERE 条件; -- where 按需求可以去掉
-- 大于: > 、等于: =、小于: <:、大于等于: >=、小于等于: <=、不等于: <> / !=
-- 与 AND、或 OR、非
-- 增加 外键
alter table `数据表名` add foreign key(`数据表中想要设置为外键的字段名`) references `参考表`(`参考表的主键`);
-- 在增加外键时,设立删除后的默认值(这里以空为例)
alter table `数据表名` add foreign key(`数据表中想要设置为外键的字段名`) references `参考表`(`参考表的主键`) on delete set null;

-- 查 ---- 详细用法在后面
select `字段名`  -- * 代表全部都取出来
from `数据表名` 
where 条件;-- where 加条件不是必须的。
-- 查询所有字段数据
select * from `数据表名`;
-- 查询指定字段数据
select `列1字段名`, `列2字段名`, `列3字段名` from `数据表名`;

3 表数据的查

3.1 数据准备工作

-- 创建数据库
create database `python_test` charset=utf8; -- 存入中文需要指定 utf8

-- 查看数据库
show databases;

-- 使用数据库
use `python_test`;

-- 显示当前使用那个数据库
select database();

-- 创建一个数据表  存0 1时使用bit 就行  bit(2)可以存4种 tinyint = bit(8)  2^8 = 256
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 '保密', -- 默认从 1 开始
    `cls_id` int unsigned default 0,
    `is_delete` bit default 0
);

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

-- 向表中插入数据
insert into `students` values
    (0,'小明',18,168.00,2,1,0),
    (0,'小黄',17,175.00,1,2,0),
    (0,'小红',14,177.00,2,3,0),
    (0,'小汉',11,180.00,3,4,0),
    (0,'小八',12,187.00,3,5,0),
    (0,'小九',13,182.00,4,6,0),
    (0,'小十',18,188.00,3,7,0),
    (0,'小之',17,186.00,2,8,0),
    (0,'小一',10,188.00,2,9,0),
    (0,'小二',15,182.00,3,9,0),
    (0,'小三',18,184.00,2,6,0),
    (0,'小四',19,185.00,4,4,0),
    (0,'小五',13,190.00,2,3,0),
    (0,'小六',14,189.00,2,4,0),
    (0,'小七',15,178.00,2,5,0),
    (0,'十一',15,167.00,1,7,0),
    (0,'十二',18,176.00,1,2,0);

-- 向表中插入数据
insert into `classes` values
    (0, "python01期"),
    (0, "python02期"),
    (0, "python04期");

-- 查看数据表
show tables;

-- 查看数据表的结构
desc `classes`;

3.2 基本查询语句

-- 查询所有字段
select * from `数据表名`;
select * from `students`;  --  `students` 
select * from `classes`;  --  `classes`

-- 查询指定字段
select `列1字段名`, `列2字段名` [, `列3字段名`] from `数据表名`;
select id, name from `students`;

-- 使用 as 给字段起别名
select `字段名` as `别名`, `字段名` as `别名` [, `字段名` as `别名`] from `数据表名`;
select name as 姓名, age as 年龄 from `students`;

-- select 表名.字段名 ..... from 表名;
select `students`.name, `students`.age from `students`;

-- 可以通过as给表其别名
-- select 别名.字段 .... from 表名 as 别名;
select `students`.name, `students`.age from `students`;
select s.name, s.age from `students` as s;

-- 消除重复行
-- distinct 字段
select distinct `gender` from `students`;

3.3 条件查询语句

-- 比较运算符
-- 大于: > 、等于: =、小于: <:、大于等于: >=、小于等于: <=、不等于: <> / != 
-- <> 在编程语言里不通用
-- 18 岁以上的所有人
select * from `students` where age > 18;

-- 逻辑运算符
-- 与(and)、或(or)、非(not)
-- 包含 18 岁以下的所有女性
select `id`, `name`, `gender` from `students` where age <= 18 and gender = 2;

-- where 和 having 的区别:where是从源表里面查,having是从结果里面查

3.4 模糊查询

-- 模糊查询(要进行匹配,效率不高)
-- like (接 '%'、'_')
-- '%' 替换零个或者多个
-- '_' 替换一个

-- 查询姓名以小开头的信息
select * from `students` where `name` like "小%";
-- 查询姓名当中有小的名字
select * from `students` where `name` like "%小%";

-- 查询有两个字的名字
select * from `students` where `name` like "__";

-- 查询两个字以上的名字数据信息
select * from `students` where `name` like "__%";

-- rlike (正则表达式)
-- 查询以小开始的信息
select * from `students` where `name` rlike "^小.*";

-- 查询已小开始 九结束的信息
select * from `students` where `name` rlike "^小*九$";
select * from `students` where `name` regexp "^小*九$";

select * from `students` where `name` regexp "一";

3.5 范围查询

-- in (1,3,8)表示在一个非连续的范围内

-- 查询年龄为 18,34 的人信息
select `name`, `age` from `students` where `age` = 18 or `age` = 34;
select `name`, `age` from `students` where `age` = 18 or `age` = 34 or `age` = 12;
select `name`, `age`  from `students` where `age` in (12, 18, 34);

-- not in不非连续的范围内
-- 不是年龄为 12, 18,34 的人信息
select `name`, `age` from `students` where `age` not in (12, 18, 34);


-- between ... and ...表示在什么范围之内  属于连续问题查询
-- 在什么范围
select `name`, `age` from `students` where `age` between 12 and 34;

-- 不再范围
select `name`, `age` from `students` where `age` not between 12 and 34;
select `name`, `age` from `students` where not `age` between 12 and 34;  -- not ... between ... and 是一种语法

select `name`, `age` from `students` where `age` not (between 12 and 34);  -- 语法错误


-- 判断空 is null
-- 注意 null 和 '' 的区别,'' 的指针有数据。
-- 判断身高为空
select `name`, `age` from `students` where `age` is null;

-- 判断非空 is not null
select `name`, `age` from `students` where `age` is not null;

3.6 排序查询

-- 排序
-- order by 字段
-- asc 从小到达排序 即升序
-- desc 从大到小排序 即将序
-- 在排序过程中遇到相同值时会按照主键进行排序

-- 查询年龄到 18~34 之间的男性,按照年龄从小到达排序
select * from `students` where (`age` between 12 and 23) and `gender` = 1 order by `age` asc;


-- order by 多个字段;第一相同会按照第二个,第二个相同会按照第三个,以此类推
select * from `students` where (`age` between 12 and 23) and `gender` = 1 order by `age` desc, `gender` desc, `height` desc; -- 在年龄相同的情况下按照性别按照升序的方式排序,如果前面排序字段都不相同则后面的字段不执行
    

3.7 聚合函数和分组

-- 聚合函数:一些普通的函数得出的一些结论,不允许与其他字段混合使用, 想要用就要使用分组, 分组和聚合函数一起使用
-- 聚合函数的输入可以是表达式,其结果也可以作为表达式的一部分
-- 求总数
-- count
-- 查询男性有多少人
select count(`name`), avg(`height`) from `students`;
select * from `students` where `gender` = 1;
select count(*) from `students` where `gender` = 1;
select count(*) as `男性人数` from `students` where `gender` = 1;

-- 求最大值
-- max
-- 查询最大年龄
select * from `students`;
select max(`age`) as 最大值 from `students`;

-- 最小值
-- min 求女性年龄最小值
select min(`age`) as 最小值 from `students` where gender = 2;

-- 求和
-- sum
-- 求所有年龄总和
select sum(`age`) as 和 from `students`;

-- 求平均值
-- avg
select sum(`age`)/count(*) as 均值 from `students`;
select avg(`age`) as 最大值 from `students`;

-- 四舍五入
-- round(123.232221133, 4);  4 为保留的小数点位数
select round(avg(`age`), 2) as 四舍五入 from `students`;

-- 经验:语言里面小数是由误差的,例如银行当中,但是整数没有
--  举例:3.14 我们乘以100变成整数。

-- 分组  分组和聚合函数一起使用,否则分组就没啥意义
-- GROUP BY 关键字通常和集合函数一起使用,比如MAX()、MIN()、COUNT()、SUM()、AVG()。
-- 例如,要返回每个水果供应商提供的水果种类,这时就要在分组过程中用到COUNT()函数,把数据分为多个逻辑组,并对每个组进行集合计算。

-- 按照性别分组,查询所有的性别
select `gender`, count(`gender`) from `students` group by `gender`;   -- 先分组,再从组内取数据

-- 计算每种性别的人数
select `gender`, count(*) from `students` group by `gender`;  -- count(*)表示对分组的计算个数  count(*)指从分组内计算个数

desc `students`;
-- 按照性别分组
select `gender`, count(`gender`), group_concat(`name`, ' ', `id`, ' ', `height`, ' ', `cls_id`) from `students` group by gender;

-- 计算男性的人数
select `gender`, count(*) from `students` where `gender` = 1;
select `gender`, count(*) from `students` where `gender` = 1 group by `gender`;
        +--------+----------+
        | gender | count(*) |
        +--------+----------+
        | 男     |        3 |
        +--------+----------+
        1 row in set (0.00 sec)

-- 计算男性和女性的人数
select `gender`, count(*) from `students` where `gender` = 1 or `gender` = 2 group by `gender`;

-- group_concat(...) 显示的内容
-- 在MySQL中,可以在GROUP BY子句中使用GROUP_CONCAT()函数,将每个分组中各个字段的值显示出来。
select `gender`, group_concat(`name`, `age`, `id`), count(*) from `students` where `gender` = 1 group by `gender`;
        +--------+---------------------------------+----------+
        | gender | group_concat(name, age, id)     | count(*) |
        +--------+---------------------------------+----------+
        | 男     | 小黄172,十一1516,十二1817       |        3 |
        +--------+---------------------------------+----------+
        1 row in set (0.00 sec)

select `gender`, group_concat(`name`, "_", `age`, " ", `id`), count(*) from `students` where `gender` = 1 group by `gender`;
        +--------+---------------------------------------+----------+
        | gender | group_concat(name, "_", age, " ", id) | count(*) |
        +--------+---------------------------------------+----------+
        | 男     | 小黄_17 2,十一_15 16,十二_18 17       |        3 |
        +--------+---------------------------------------+----------+
        1 row in set (0.00 sec)

-- where 是对原表的数据进行判断
-- having 表示对分组进行条件判断,是进行分组之后在判断(注意 having 和 where 在使用时语句的位置)
-- 查询平均年年龄超过18岁的性别以及姓名  hanving avg(age)>18
select `gender`, group_concat(`name`) from `students` group by `gender` having avg(`age`)>18;
select `gender`, group_concat(`name`), avg(`age`), count(*) from `students` group by `gender` having count(*) > 3;
select `gender`, group_concat(`name`), avg(`age`) from `students` group by `gender` having avg(`age`) > 11;
-- 查询每种性别中的人数多于 2 个的信息
select `gender`, count(`gender`), group_concat(`name`) from `students` group by `gender` having count(*) > 2;

-- 在GROUP BY子句中使用WITH ROLLUP
-- 使用WITH ROLLUP关键字之后,在所有查询出的分组记录之后增加一条记录,该记录计算查询出的所有记录的总和,即统计记录数量。
select `gender`, group_concat(`name`), count(`name`) from `students` group by `gender` with rollup having avg(`age`)>8;
-- 由结果可以看到,通过GROUP BY分组之后,在显示结果的最后面新添加了一行,该行Total列的值正好是上面所有数值之和。

-- 多字段分组
-- 使用GROUP BY可以对多个字段进行分组,GROUP BY关键字后面跟需要分组的字段,MySQL根据多字段的值来进行层次分组,分组层次从左到右,即先按第1个字段分组,
-- 然后在第1个字段值相同的记录中再根据第2个字段的值进行分组,以此类推。

select `gender`, `cls_id`, group_concat(`name`, ' ', `cls_id`) from `students` group by `gender`, `cls_id`;


-- GROUP BY和ORDER BY一起使用
-- 某些情况下需要对分组进行排序,在前面的介绍中,ORDER BY用来对查询的记录排序,如果和GROUP BY一起使用可以完成对分组的排序。
select `gender`, group_concat(`name`, `age`, `height`) from `students` group by `gender` having avg(`age`)>8 order by count(`height`);
-- GROUP BY子句按订单号对数据进行分组,SUM()函数便可以返回总的订单价格,HAVING子句对分组数据进行过滤,使得只返回总价格大于100的订单,最后使用ORDER BY子句排序输出。
-- 当使用ROLLUP时,不能同时使用ORDER BY子句进行结果排序,即ROLLUP和ORDER BY是互相排斥的。

3.8 分页

-- limit start, count --直接限制查询出来的个数
-- 带一个参数的LIMIT指定从查询结果的首行开始,唯一的参数表示返回的行数,即“LIMIT n”与“LIMIT 0,n”等价。带两个参数的LIMIT可以返回从任何一个位置开始的指定的行数。返回第一行时,位置偏移量是0。因此,“LIMIT 1, 1”将返回第二行,而不是第一行。
-- MySQL 8.0中可以使用“LIMIT 4 OFFSET 3”,意思是获取从第5条记录开始后面的3条记录,和“LIMIT 4,3;”返回的结果相同。

-- 查询前五个数据
select * from `students` limit 5;  # 只显示前两个
select * from `students` order by age desc limit 5;

-- 限制查询的个数
select * from `students` limit 7, 4;  # 开始第一个为0   规律:limit (第N页-1)*每页的个数, 每页的个数;
--          +----+--------+------+--------+--------+--------+-----------+
--          | id | name   | age  | height | gender | cls_id | is_delete |
--          +----+--------+------+--------+--------+--------+-----------+
--          |  9 | 小一   |   10 | 188.00 | 女     |      9 |           |
--          | 10 | 小二   |   15 | 182.00 | 中性   |      9 |           |
--          | 11 | 小三   |   18 | 184.00 | 女     |      6 |           |
--          | 12 | 小四   |   19 | 185.00 | 保密   |      4 |           |
--          +----+--------+------+--------+--------+--------+-----------+
--          4 rows in set (0.00 sec)

-- 注意:limit必须放在最后。
-- 顺序为where order by limit
select * from `students` where  gender = 2 order by height desc limit  0, 2

3.9 连接查询

-- 连接查询:即多个表之间进行关联查询
-- 若对分组后的数据进行进一步的筛选,要使用连接,子查询的方法不严谨。
-- 注意:能用连接查询的话就尽量用连接查询,不要用子查询。

-- 1.内连接查询  即取交集
-- inner join ... on(其中 on 后面接条件)
select * from `students` inner join `classes`;
-- 将 `students`.`cls_id` 与 `classes`.`id` 连接起来
select * from `students` inner join `classes` on `students`.`cls_id` = `classes`.`id`;  -- 将相应的信息显示
--          +----+--------+------+--------+--------+--------+-----------+----+-------------+
--          | id | name   | age  | height | gender | cls_id | is_delete | id | name        |
--          +----+--------+------+--------+--------+--------+-----------+----+-------------+
--          |  1 | 小明   |   18 | 168.00 | 女     |      1 |           |  1 | python01期  |
--          |  2 | 小黄   |   17 | 175.00 | 男     |      2 |           |  2 | python02期  |
--          |  3 | 小红   |   14 | 177.00 | 女     |      3 |           |  3 | python04期  |
--          | 13 | 小五   |   13 | 190.00 | 女     |      3 |           |  3 | python04期  |
--          | 17 | 十二   |   18 | 176.00 | 男     |      2 |           |  2 | python02期  |
--          +----+--------+------+--------+--------+--------+-----------+----+-------------+
--          5 rows in set (0.00 sec)


-- 按照要求现实姓名和年级
select `students`.* from `students`;
select classes.* from `classes`;
select `students`.*, `classes`.`name`  from `students` inner join `classes` on `students`.`cls_id` = `classes`.`id`;
--          +----+--------+------+--------+--------+--------+-----------+-------------+
--          | id | name   | age  | height | gender | cls_id | is_delete | name        |
--          +----+--------+------+--------+--------+--------+-----------+-------------+
--          |  1 | 小明   |   18 | 168.00 | 女     |      1 |           | python01期  |
--          |  2 | 小黄   |   17 | 175.00 | 男     |      2 |           | python02期  |
--          |  3 | 小红   |   14 | 177.00 | 女     |      3 |           | python04期  |
--          | 13 | 小五   |   13 | 190.00 | 女     |      3 |           | python04期  |
--          | 17 | 十二   |   18 | 176.00 | 男     |      2 |           | python02期  |
--          +----+--------+------+--------+--------+--------+-----------+-------------+
--          5 rows in set (0.00 sec)

-- 简写重命名
select s.*, c.name  from `students` as s inner join `classes` as c on s.cls_id = c.id;
--          +----+--------+------+--------+--------+--------+-----------+-------------+
--          | id | name   | age  | height | gender | cls_id | is_delete | name        |
--          +----+--------+------+--------+--------+--------+-----------+-------------+
--          |  1 | 小明   |   18 | 168.00 | 女     |      1 |           | python01期  |
--          |  2 | 小黄   |   17 | 175.00 | 男     |      2 |           | python02期  |
--          |  3 | 小红   |   14 | 177.00 | 女     |      3 |           | python04期  |
--          | 13 | 小五   |   13 | 190.00 | 女     |      3 |           | python04期  |
--          | 17 | 十二   |   18 | 176.00 | 男     |      2 |           | python02期  |
--          +----+--------+------+--------+--------+--------+-----------+-------------+
--          5 rows in set (0.00 sec)


-- 加上排序
select s.*, c.name  from `students` as s inner join `classes` as c on s.`cls_id` = c.`id` order by c.`name`, s.`id`;
--          +----+--------+------+--------+--------+--------+-----------+-------------+
--          | id | name   | age  | height | gender | cls_id | is_delete | name        |
--          +----+--------+------+--------+--------+--------+-----------+-------------+
--          |  1 | 小明   |   18 | 168.00 | 女     |      1 |           | python01期  |
--          | 17 | 十二   |   18 | 176.00 | 男     |      2 |           | python02期  |
--          |  2 | 小黄   |   17 | 175.00 | 男     |      2 |           | python02期  |
--          | 13 | 小五   |   13 | 190.00 | 女     |      3 |           | python04期  |
--          |  3 | 小红   |   14 | 177.00 | 女     |      3 |           | python04期  |
--          +----+--------+------+--------+--------+--------+-----------+-------------+
--          5 rows in set (0.00 sec)


-- 更改显示顺序
select c.name, s.* from `students` as s inner join `classes` as c on s.`cls_id` = c.id order by c.`name`, s.`id`;
--          +-------------+----+--------+------+--------+--------+--------+-----------+
--          | name        | id | name   | age  | height | gender | cls_id | is_delete |
--          +-------------+----+--------+------+--------+--------+--------+-----------+
--          | python01期  |  1 | 小明   |   18 | 168.00 | 女     |      1 |           |
--          | python02期  | 17 | 十二   |   18 | 176.00 | 男     |      2 |           |
--          | python02期  |  2 | 小黄   |   17 | 175.00 | 男     |      2 |           |
--          | python04期  | 13 | 小五   |   13 | 190.00 | 女     |      3 |           |
--          | python04期  |  3 | 小红   |   14 | 177.00 | 女     |      3 |           |
--          +-------------+----+--------+------+--------+--------+--------+-----------+
--          5 rows in set (0.00 sec)

-- 多条件排序
select c.`name`, s.* from `students` as s inner join `classes` as c on s.`cls_id` = c.`id` order by c.`name`, s.`id`, s.`age`;
--          +-------------+----+--------+------+--------+--------+--------+-----------+
--          | name        | id | name   | age  | height | gender | cls_id | is_delete |
--         +-------------+----+--------+------+--------+--------+--------+-----------+
--          | python01期  |  1 | 小明   |   18 | 168.00 | 女     |      1 |           |
--          | python02期  |  2 | 小黄   |   17 | 175.00 | 男     |      2 |           |
--          | python02期  | 17 | 十二   |   18 | 176.00 | 男     |      2 |           |
--          | python04期  |  3 | 小红   |   14 | 177.00 | 女     |      3 |           |
--          | python04期  | 13 | 小五   |   13 | 190.00 | 女     |      3 |           |
--          +-------------+----+--------+------+--------+--------+--------+-----------+
--          5 rows in set (0.00 sec)


--  2. 外连接查询(分为左连接右连接)
-- 右连接查询
-- 
-- 左连接查询(那个表在左边,以这个表为基准取查询的信息,取不出来莫认为Null)
-- 注意,如果要使用 null 为条件的话,请用 字段 is null,而不要用 字段 = null;
select * from `students` as s left join `classes` as c on s.`cls_id` = c.`id`;
--          +----+--------+------+--------+--------+--------+-----------+------+-------------+
--          | id | name   | age  | height | gender | cls_id | is_delete | id   | name        |
--          +----+--------+------+--------+--------+--------+-----------+------+-------------+
--          |  1 | 小明   |   18 | 168.00 | 女     |      1 |           |    1 | python01期  |
--          |  2 | 小黄   |   17 | 175.00 | 男     |      2 |           |    2 | python02期  |
--          | 17 | 十二   |   18 | 176.00 | 男     |      2 |           |    2 | python02期  |
--          |  3 | 小红   |   14 | 177.00 | 女     |      3 |           |    3 | python04期  |
--          | 13 | 小五   |   13 | 190.00 | 女     |      3 |           |    3 | python04期  |
--          |  4 | 小汉   |   11 | 180.00 | 中性   |      4 |           | NULL | NULL        |
--          |  5 | 小八   |   12 | 187.00 | 中性   |      5 |           | NULL | NULL        |
--          |  6 | 小九   |   13 | 182.00 | 保密   |      6 |           | NULL | NULL        |
--          |  7 | 小十   |   18 | 188.00 | 中性   |      7 |           | NULL | NULL        |
--          |  8 | 小之   |   17 | 186.00 | 女     |      8 |           | NULL | NULL        |
--          |  9 | 小一   |   10 | 188.00 | 女     |      9 |           | NULL | NULL        |
--          | 10 | 小二   |   15 | 182.00 | 中性   |      9 |           | NULL | NULL        |
--          | 11 | 小三   |   18 | 184.00 | 女     |      6 |           | NULL | NULL        |
--          | 12 | 小四   |   19 | 185.00 | 保密   |      4 |           | NULL | NULL        |
--          | 14 | 小六   |   14 | 189.00 | 女     |      4 |           | NULL | NULL        |
--          | 15 | 小七   |   15 | 178.00 | 女     |      5 |           | NULL | NULL        |
--          | 16 | 十一   |   15 | 167.00 | 男     |      7 |           | NULL | NULL        |
--          +----+--------+------+--------+--------+--------+-----------+------+-------------+
--          17 rows in set (0.00 sec)
-- 


-- 与inner join ... on比较
select * from `students` as s  inner join `classes` as c on s.`cls_id` = c.`id`;
--          +----+--------+------+--------+--------+--------+-----------+----+-------------+
--          | id | name   | age  | height | gender | cls_id | is_delete | id | name        |
--          +----+--------+------+--------+--------+--------+-----------+----+-------------+
--          |  1 | 小明   |   18 | 168.00 | 女     |      1 |           |  1 | python01期  |
--          |  2 | 小黄   |   17 | 175.00 | 男     |      2 |           |  2 | python02期  |
--          |  3 | 小红   |   14 | 177.00 | 女     |      3 |           |  3 | python04期  |
--          | 13 | 小五   |   13 | 190.00 | 女     |      3 |           |  3 | python04期  |
--          | 17 | 十二   |   18 | 176.00 | 男     |      2 |           |  2 | python02期  |
--          +----+--------+------+--------+--------+--------+-----------+----+-------------+
--          5 rows in set (0.01 sec)
-- 
-- 
-- left是指left左边(等号左边)的数据表
select * from `students`;
select * from `classes` as c left join `students` as s on c.`id` = s.`cls_id`;
--          +----+-------------+------+--------+------+--------+--------+--------+-----------+
--          | id | name        | id   | name   | age  | height | gender | cls_id | is_delete |
--          +----+-------------+------+--------+------+--------+--------+--------+-----------+
--          |  1 | python01期  |    1 | 小明   |   18 | 168.00 | 女     |      1 |           |
--          |  2 | python02期  |    2 | 小黄   |   17 | 175.00 | 男     |      2 |           |
--          |  3 | python04期  |    3 | 小红   |   14 | 177.00 | 女     |      3 |           |
--          |  3 | python04期  |   13 | 小五   |   13 | 190.00 | 女     |      3 |           |
--          |  2 | python02期  |   17 | 十二   |   18 | 176.00 | 男     |      2 |           |
--          +----+-------------+------+--------+------+--------+--------+--------+-----------+
--          5 rows in set (0.00 sec)

    
-- right join ... on 一般用的比较少  使用左连接调换两个数据表的顺序即可
-- 
-- 查询没有对应班级信息的学生
-- 思路:对于查询出的结果我们可以将其看作一个新的表,然后在此新表的基础上进行再次操作查询
-- 如果在原表里面进行查询使用where
-- 在查询的基础上进行再次操作使用having
select * from `students` as s left join `classes` as c on c.id = s.`cls_id` having c.id is NULL;
select * from `students` as s left join `classes` as c on c.id = s.`cls_id` where c.id is NULL;-- 这样也行
--          +----+--------+------+--------+--------+--------+-----------+------+------+
--          | id | name   | age  | height | gender | cls_id | is_delete | id   | name |
--          +----+--------+------+--------+--------+--------+-----------+------+------+
--          |  4 | 小汉   |   11 | 180.00 | 中性   |      4 |           | NULL | NULL |
--          |  5 | 小八   |   12 | 187.00 | 中性   |      5 |           | NULL | NULL |
--          |  6 | 小九   |   13 | 182.00 | 保密   |      6 |           | NULL | NULL |
--          |  7 | 小十   |   18 | 188.00 | 中性   |      7 |           | NULL | NULL |
--          |  8 | 小之   |   17 | 186.00 | 女     |      8 |           | NULL | NULL |
--          |  9 | 小一   |   10 | 188.00 | 女     |      9 |           | NULL | NULL |
--          | 10 | 小二   |   15 | 182.00 | 中性   |      9 |           | NULL | NULL |
--          | 11 | 小三   |   18 | 184.00 | 女     |      6 |           | NULL | NULL |
--          | 12 | 小四   |   19 | 185.00 | 保密   |      4 |           | NULL | NULL |
--          | 14 | 小六   |   14 | 189.00 | 女     |      4 |           | NULL | NULL |
--          | 15 | 小七   |   15 | 178.00 | 女     |      5 |           | NULL | NULL |
--          | 16 | 十一   |   15 | 167.00 | 男     |      7 |           | NULL | NULL |
--          +----+--------+------+--------+--------+--------+-----------+------+------+
--          12 rows in set (0.00 sec)

3.10 自关联

-- 导入 areas.sql 文件中的数据
-- 从 areas.sql 文件目录下登录数据库,使用 source areas.sql 导入数据

-- 一个表中的一列关联到该表中的另一列:自关联
-- 使用:省市关联、行政关系等

create table `areas`(
    `aid` int primary key,
    `pid` int,
    `atitle` varchar(20),
    `types` tinyint(1) default "2"
);


# 查看省份
select * from `areas` where pid = 1;


# 查看省份市的个数
select * from areas where atitle = '陕西';
    +-----+------+--------+-------+
    | aid | pid  | atitle | types |
    +-----+------+--------+-------+
    |  24 |    1 | 陕西   |     1 |
    +-----+------+--------+-------+
    1 row in set (0.00 sec)

select * from areas where pid = 24;
    +-----+------+--------+-------+
    | aid | pid  | atitle | types |
    +-----+------+--------+-------+
    | 311 |   24 | 西安   |     2 |
    | 312 |   24 | 安康   |     2 |
    | 313 |   24 | 宝鸡   |     2 |
    | 314 |   24 | 汉中   |     2 |
    | 315 |   24 | 商洛   |     2 |
    | 316 |   24 | 铜川   |     2 |
    | 317 |   24 | 渭南   |     2 |
    | 318 |   24 | 咸阳   |     2 |
    | 319 |   24 | 延安   |     2 |
    | 320 |   24 | 榆林   |     2 |
    +-----+------+--------+-------+
    10 rows in set (0.00 sec)

select * from areas where pid = 311;
    +------+------+-----------+-------+
    | aid  | pid  | atitle    | types |
    +------+------+-----------+-------+
    | 2596 |  311 | 莲湖区    |     3 |
    | 2597 |  311 | 新城区    |     3 |
    | 2598 |  311 | 碑林区    |     3 |
    | 2599 |  311 | 雁塔区    |     3 |
    | 2600 |  311 | 灞桥区    |     3 |
    | 2601 |  311 | 未央区    |     3 |
    | 2602 |  311 | 阎良区    |     3 |
    | 2603 |  311 | 临潼区    |     3 |
    | 2604 |  311 | 长安区    |     3 |
    | 2605 |  311 | 蓝田县    |     3 |
    | 2606 |  311 | 周至县    |     3 |
    | 2607 |  311 | 户县      |     3 |
    | 2608 |  311 | 高陵县    |     3 |
    +------+------+-----------+-------+
    13 rows in set (0.01 sec)


# 另一种查询方法(假如有两章表的情况下,一张省份表,一张地级市表的情况下,地级市的pid = 省份表的aid)
# 思路:可以通过as生成新的表
select * from areas as province inner join areas as city on province.aid = city.pid having province.atitle = "陕西";
select province.atitle, city.atitle from areas as province inner join areas as city on city.pid = province.aid having province.atitle = "陕西";

    +-----+------+--------+-------+-----+------+--------+-------+
    | aid | pid  | atitle | types | aid | pid  | atitle | types |
    +-----+------+--------+-------+-----+------+--------+-------+
    |  24 |    1 | 陕西   |     1 | 311 |   24 | 西安   |     2 |
    |  24 |    1 | 陕西   |     1 | 312 |   24 | 安康   |     2 |
    |  24 |    1 | 陕西   |     1 | 313 |   24 | 宝鸡   |     2 |
    |  24 |    1 | 陕西   |     1 | 314 |   24 | 汉中   |     2 |
    |  24 |    1 | 陕西   |     1 | 315 |   24 | 商洛   |     2 |
    |  24 |    1 | 陕西   |     1 | 316 |   24 | 铜川   |     2 |
    |  24 |    1 | 陕西   |     1 | 317 |   24 | 渭南   |     2 |
    |  24 |    1 | 陕西   |     1 | 318 |   24 | 咸阳   |     2 |
    |  24 |    1 | 陕西   |     1 | 319 |   24 | 延安   |     2 |
    |  24 |    1 | 陕西   |     1 | 320 |   24 | 榆林   |     2 |
    +-----+------+--------+-------+-----+------+--------+-------+
    10 rows in set (0.01 sec)


select * from areas as province inner join areas as city on city.pid = province.aid having province.atitle = "西安";
    +-----+------+--------+-------+------+------+-----------+-------+
    | aid | pid  | atitle | types | aid  | pid  | atitle    | types |
    +-----+------+--------+-------+------+------+-----------+-------+
    | 311 |   24 | 西安   |     2 | 2596 |  311 | 莲湖区    |     3 |
    | 311 |   24 | 西安   |     2 | 2597 |  311 | 新城区    |     3 |
    | 311 |   24 | 西安   |     2 | 2598 |  311 | 碑林区    |     3 |
    | 311 |   24 | 西安   |     2 | 2599 |  311 | 雁塔区    |     3 |
    | 311 |   24 | 西安   |     2 | 2600 |  311 | 灞桥区    |     3 |
    | 311 |   24 | 西安   |     2 | 2601 |  311 | 未央区    |     3 |
    | 311 |   24 | 西安   |     2 | 2602 |  311 | 阎良区    |     3 |
    | 311 |   24 | 西安   |     2 | 2603 |  311 | 临潼区    |     3 |
    | 311 |   24 | 西安   |     2 | 2604 |  311 | 长安区    |     3 |
    | 311 |   24 | 西安   |     2 | 2605 |  311 | 蓝田县    |     3 |
    | 311 |   24 | 西安   |     2 | 2606 |  311 | 周至县    |     3 |
    | 311 |   24 | 西安   |     2 | 2607 |  311 | 户县      |     3 |
    | 311 |   24 | 西安   |     2 | 2608 |  311 | 高陵县    |     3 |
    +-----+------+--------+-------+------+------+-----------+-------+
    13 rows in set (0.01 sec)

3.11 子查询

-- 简单来讲,子查询就是一个 select 中嵌套了一个 select
-- 子查询效率比较低
-- 标量子查询
-- 查询身高最高的男生
select max(`height`) from `students`;
        +-------------+
        | max(height) |
        +-------------+
        |      190.00 |
        +-------------+
        1 row in set (0.00 sec)

select * from `students` where `height` = (select max(`height`) from `students`);
        +----+--------+------+--------+--------+--------+-----------+
        | id | name   | age  | height | gender | cls_id | is_delete |
        +----+--------+------+--------+--------+--------+-----------+
        | 13 | 小五   |   13 | 190.00 | 女     |      3 |           |
        +----+--------+------+--------+--------+--------+-----------+
        1 row in set (0.00 sec)

-- 利用子查询解决自关联查询中的问题
select * from `areas` where `pid` = (select `aid` from `areas` where `atitle` = "陕西");
            +-----+------+--------+-------+
            | aid | pid  | atitle | types |
            +-----+------+--------+-------+
            | 311 |   24 | 西安   |     2 |
            | 312 |   24 | 安康   |     2 |
            | 313 |   24 | 宝鸡   |     2 |
            | 314 |   24 | 汉中   |     2 |
            | 315 |   24 | 商洛   |     2 |
            | 316 |   24 | 铜川   |     2 |
            | 317 |   24 | 渭南   |     2 |
            | 318 |   24 | 咸阳   |     2 |
            | 319 |   24 | 延安   |     2 |
            | 320 |   24 | 榆林   |     2 |
            +-----+------+--------+-------+
            10 rows in set (0.00 sec)


-- 列级子查询
-- 查询学生的班级号能够对应的学生信息
select * from students where cls_id in (select id from classes);

3.12 Tips

  • 可以将 select 的结果当成一个新表
  • 字段和表明可以自定义名称防止冲突
大部分内容由 https://github.com/wmh02240/MySQL 总结,本文只进行了适量的修改。
最后修改:2022 年 11 月 23 日 10 : 25 AM
如果觉得我的文章对你有用,请随意赞赏