数据表的操作
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, 23.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 总结，本文只进行了适量的修改。
