第三章：MySQL（中篇）

最新推荐文章于 2023-03-05 20:55:30 发布

miao蛙种子

最新推荐文章于 2023-03-05 20:55:30 发布

阅读量207

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/xiangchi7/article/details/85011253

版权

数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

文章目录

第一节：查询中国区域数据库
第二节：金庸武侠数据库（表关系管理）
- - ==创建数据库和表==
  - ==插入数据==
第三节：表关系管理

第一节：查询中国区域数据库

复杂查询命令

limit、order by与group by

功能	命令
查询前N条记录	*`select from 表名 limit N;`**
按xx升序	*`select from 表名 order by 字段名 asc;`**
按xx降序	*`select from 表名 order by 字段名 desc;`**
按xx分组	`select 字段名1 from 表名 group by 字段名2;`

模糊查询

功能	命令
"像"	*`select from 表名 where 字段名 like "%内容";`**
"不像"	*`select from 表名 where 字段名 not like "%内容";`**
判断是否在组内	*`select from 表名 where 字段名 in ('A','B','C');`**
判断是否在某区间	*`select from 表名 where 字段名 between 'A' and 'B';`**

统计

功能	命令
统计个数	`select count(字段名) from 表名;`
求最大值	`select max(字段名) from 表名;`
求平均值	`select avg(字段名) from 表名;`

having与where的区别

having子句：对分组后的结果（二手数据）进行条件筛选；
where是对分组前的数据（一手数据）进行条件筛选

例：

select 字段名1 from 表名 group by 字段名2 having 字段名3 > value

单表查询一

1、查询前5个省、区、市

select * from t_province limit 5;
select * from t_city limit 5;
select * from t_district limit 5;

如图：
在这里插入图片描述

2、查询前10个省份，按名称升序排列

select * from t_province order by ProName asc limit 10 ;

如图：
在这里插入图片描述

3、查询所有不叫“xx省”的省级行政区

select * from t_province where ProName not like "%省"

4、查询我国所有市，哪个市在下列5大城市中

select * from t_city where CityName in ('悉尼市','北京市','大阪市','多伦多市','巴黎市');

5、统计省份表中有多少个不一样的省份ID，最大的省份ID是多少，求省份ID的平均值

# 统计省份表中有多少个不一样的省份ID
select count(ProID) from t_province;

#统计省份表中最大的省份ID
select max(ProID) from t_province;

#统计省份表中省份ID的平均值
select avg (ProID) from t_province;

如图：
在这里插入图片描述

6、按省份分组，查询每个省分别有多少地级市，降序取前10；

select proid,count(proid) from t_city group by proid order by proid desc limit 10;

7、罗列有20个以上地级市的省份

# 这里cities是分组后的结果，因此对它进行筛选时，不能用where，只能用having
select ProID,count(cityID) as cities from t_city group by proID having cities > 20;

执行结果：
在这里插入图片描述

多表查询

union —— 将查询结果“字段数相同”的两个表进行强制合并
join —— 将字段名和字段值都相的两张表联合起来进行查询
两张不同的表可能会有字段名和字段值都相等的部分，根据这部分，可以将两张表联合起来进行查询：
使用join或inner join联合起来的两张表，查询结果为两表交集的部分除此之外，还有left join 和 right join；

使用 join 进行联合查询

在这里插入图片描述
单表查询第6题，我们想知道的是哪个省有多个市，并且取表中的前5个。但是结果只能给出省ID。此时就可以使用join 联合两张表
例：

'''
省表我们需要省名+省ID
select proName,proid from t_province 

市表我们需要省id +城市数量
select proid,count(cityid) from t_city group by proid
'''

# 合并省表和市表的查询结果成一张表再进行查行，注意字段要写是原来谁的字段
select t_province.proName,count(t_city.cityid)   # 查询省表的proName和市表的city数量
from t_province join t_city                      # 以省表join市表进行合并成的新表
on t_province.proid = t_city.proid               #合并条件是两表相同的proid字段
group by t_province.proid                        #从新表查询后进行分组
order by t_province.proid desc limit 10;         #按照省表的proid字段进行降序取前十

执行结果：
在这里插入图片描述

使用 union 进行强制合并

在这里插入图片描述

例：

select proname from t_province union select cityid from t_city where proid >30;

执行结果：
在这里插入图片描述

第二节：金庸武侠数据库（表关系管理）

创建金庸武侠数据库，建立掌门、徒弟、门派、绝招四张表，为下节讲表关系准备数据；
其中每个门派一个掌门，每个掌门管理一个门派，因此门派和掌门是一对一的关系；
每个门派有多名徒弟，而每个徒弟只属于一个门派，因此门派和徒弟是一对多的关系；
每名徒弟可以偷学多种绝招，而每种绝招可以被多名徒弟学习，因此徒弟和绝招是多对多的关系；
下一节将讲述以上表关系的管理策略；

创建数据库和表

创建金庸武侠数据库

create database wulin charset=utf8;

创建掌门表字段：自增长主键id、姓名（非空）、性别（非空）、绝招id（默认0）、是否闭关（默认0）；

create table master(
  id integer primary key auto_increment,
  name varchar(10) not null,-- 非空约束
  gender varchar(10) not null,
  skillid integer default 0,--默认约束
  isClosing integer default 0
);

创建徒弟表字段：自增长主键id、姓名（非空）、性别（非空）、门派id（默认0）；

create table students(
  id integer primary key auto_increment,
  name varchar(10) not null,
  gender varchar(10) not null,
  classid integer default 0
);

创建门派表字段：自增长主键id、名称（非空）、掌门id（默认0）；

create table clazz(
  id integer primary key auto_increment,
  name varchar(20) not null,
  masterid integer default 0
);

创建绝招表字段：自增长主键id、名称（非空）；

create table skillname(
  id integer primary key auto_increment,
  name varchar(10) not null
);

插入数据

插入徒弟数据

insert into students(name,gender) values ("莫生古",“男”);
insert into students(name,gender) values ("张脆三",“男”);
insert into students(name,gender) values ("宋演瞧",“男”);
insert into students(name,gender) values ("灭绝老泥",“女”);
insert into students(name,gender) values ("周纸弱",“女”);
insert into students(name,gender) values ("袁紫衣",“女”);
insert into students(name,gender) values ("郭静",“男”);
insert into students(name,gender) values ("黄茸",“女”);
insert into students(name,gender) values ("陈有亮",“男”);
insert into students(name,gender) values ("玄慈和尚",“男”);
insert into students(name,gender) values ("扫地僧",“男”);
insert into students(name,gender) values ("方正大师",“男”);

插入掌门数据：

insert into master(name,gender) values ("张三疯",“男”);
insert into master(name,gender) values ("哒摸祖师",“男”);
insert into master(name,gender) values ("洪痴公",“男”);
insert into master(name,gender) values ("郭香",“女”);

插入门派数据

insert into clazz(name) values ("丐帮");
insert into clazz(name) values ("笑林派");
insert into clazz(name) values ("五党派");
insert into clazz(name) values ("饿没派");

插入绝招数据

insert into skillname(name) values ("九阴白凤爪");
insert into skillname(name) values ("打狗棒法");
insert into skillname(name) values ("易筋经");
insert into skillname(name) values ("太极拳");

在这里插入图片描述

第三节：表关系管理

关系型数据库中的表与表不是彼此独立，而是相互关联的；
表与表之间的级联关系使得整个数据库成为一个有机关联的系统；
表关系可以分为一对一、一对多、多对多三种关系，它们的维护方式各不相同；
表关系的管理是关系型数据库的重要组成部分

一对一关系

如果A表记录与B表记录有双向的一一对应关系，我们就称它们之间有一对一的关系；
如金庸武侠数据库中的门派与掌门，门派有唯一的掌门，掌门管理唯一的门派，它们之间的关系就是一对一关系；
一对一关系的维护，由A、B中相对次要的一方来维护（这里假设是B），维护的方式是在B中插入一个指向A表主键的外键；
在本例中，门派表有一个masterid字段，指向掌门表中的主键id；
在一对一关系中，本来外键放在任意一方都是可行的，之所以要选择相对次要的一方，是因为万一不得以必须删除一个表时，我们会选择删除相对次要的表，此时由他所维护的关联关系也被一并删除，不会形成脏数据；

例：查询某门派的掌门，查询某位掌门任管理的门派

现在表与表是相互独立的，并不存在关系，首先可以将门派表“丐帮”的masterid设置为掌门表 '洪痴公’的id，

update clazz set masterid = (select id from master where master.name = '洪痴公') where name='丐帮';

此时，两表的一对一关系已经形成，通过查询门派表中的masterid可以知道该门派的掌门便是掌门表中的’洪痴公’

#查询“丐帮”的掌门
select * from master where id = (select masterid from clazz where name='丐帮');

# 查询'洪痴公'所管理的门派
select * from clazz where masterid = (select id from master where name = '洪痴公');

查询结果：
在这里插入图片描述

一对多关系

如果A表中的一条数据对应B表中的多条数据，而B表中的每条数据都对应一条唯一的A表数据，就称A表和B表是一对多的关系；
如金庸武侠数据库中的门派与徒弟，每个门派有多名徒弟，而每个徒弟只属于一个唯一特定的门派，所以门派表和徒弟表是一对多的关系；
一对多关系的维护，由多方进行维护，维护方式是多方在表中添加指向一方的外键；
本例中，徒弟表中有classid来指向徒弟所对应的门派；

例：查询一个门派的所有徒弟

#修改徒弟表信息，为每个徒弟指定处键classid，建立与门派表的关系
update students set classid=(select id from clazz where clazz.name = '五党派') where students.name='莫生古';
update students set classid=(select id from clazz where clazz.name = '五党派') where students.name='张脆三';
update students set classid=(select id from clazz where clazz.name = '五党派') where students.name='宋演瞧';
update students set classid=(select id from clazz where clazz.name = '饿没派') where students.name='灭绝老泥';
update students set classid=(select id from clazz where clazz.name = '饿没派') where students.name='周纸弱';
update students set classid=(select id from clazz where clazz.name = '饿没派') where students.name='袁紫衣';
update students set classid=(select id from clazz where clazz.name = '丐帮') where students.name='郭静';
update students set classid=(select id from clazz where clazz.name = '丐帮') where students.name='黄茸';
update students set classid=(select id from clazz where clazz.name = '丐帮') where students.name='陈有亮';
update students set classid=(select id from clazz where clazz.name = '笑林派') where students.name='玄慈和尚';
update students set classid=(select id from clazz where clazz.name = '笑林派') where students.name='扫地僧';
update students set classid=(select id from clazz where clazz.name = '笑林派') where students.name='方正大师';

# 通过查询徒弟表中所有classid指向“五党派”的记录，来查询属于“五党派”的所有徒弟
select * from students where classid = (select id from clazz where clazz.name='五党派');

# 查询徒弟“扫地僧”所属的门派信息
select * from clazz where id = (select classid from students where name = "扫地僧");

查询结果：
在这里插入图片描述

多对多关系

如果A表中的一条记录对应B表中的多条记录，B表中的一条记录也对应A表中的多条记录，就称A表和B表是多对多的关系；
如金庸武侠数据库中的徒弟与绝招，每个徒弟可以学得多门绝招，每门绝招也可以被多名徒弟偷学，所以徒弟表与绝招表是多对多的关系；
多对多关系的维护，要通过建立中间表来维护，一个徒弟学得一门绝招，就将徒弟的id和绝招的id同时存入一条记录中进行记录，中间表的主键是【徒弟id和绝招id的联合主键】，即同一个徒弟不能对同一门绝招学会两次；

例：查询某徒弟所学的全部绝招，查询学会某绝招的全部徒弟

首先创建【徒弟_绝招中间表】

create table students_skillname(
  stid integer not null,          # 徒弟id
  skid integer not null,          # 绝招id
  primary key (stid,skid)         # 主键是【学生id和课程id的联合主键】
);

# 如果没有设置主键，可以通过修改表字段的方式来添加【联合主键】
alter table student_course add constraint s_c primary key (sid,cid);

中间表插入一些数据

# "莫生古"学会了"九阴白凤爪"
insert into students_skillname(stid,skid) values (
  (SELECT id from students where students.name="莫生古"),
  (select id from skillname where skillname.name="九阴白凤爪")
);

# "莫生古"学会了"太极拳"
insert into students_skillname(stid,skid) values (
  (SELECT id from students where students.name="莫生古"),
  (select id from skillname where skillname.name="太极拳")
);

# "张脆三"学会了"太极拳"
insert into students_skillname(stid,skid) values (
  (SELECT id from students where students.name="张脆三"),
  (select id from skillname where skillname.name="太极拳")
);

# "张脆三"学会了"打狗棒法"
insert into students_skillname(stid,skid) values (
  (SELECT id from students where students.name="张脆三"),
  (select id from skillname where skillname.name="打狗棒法")
);

# "周纸弱"学会了"九阴白凤爪"
insert into students_skillname(stid,skid) values (
  (SELECT id from students where students.name="周纸弱"),
  (select id from skillname where skillname.name="九阴白凤爪")
);

# "周纸弱"学会了"太极拳"
insert into students_skillname(stid,skid) values (
  (SELECT id from students where students.name="周纸弱"),
  (select id from skillname where skillname.name="太极拳")
);

...
...（此处省略一部份，自己设计即可）

# "扫地僧"学会了"太极拳"
insert into students_skillname(stid,skid) values (
  (SELECT id from students where students.name="扫地僧"),
  (select id from skillname where skillname.name="太极拳")
);

# "方正大师"学会了"太极拳"
insert into students_skillname(stid,skid) values (
  (SELECT id from students where students.name="方正大师"),
  (select id from skillname where skillname.name="太极拳")
);

# "方正大师"学会了"易筋经"
insert into students_skillname(stid,skid) values (
  (SELECT id from students where students.name="方正大师"),
  (select id from skillname where skillname.name="易筋经")
);

查询

# 查询"方正大师"学会的绝招信息
select * from skillname where id in (
  select skid from students_skillname where stid=(
  select id from students where name="方正大师")
);

查询学会了"太极拳"的徒弟信息
select * from students where id in (
  select stid from students_skillname where skid=(
  select id from skillname where name="太极拳")
);

查询结果：
在这里插入图片描述

miao蛙种子

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
第三章：MySQL（中篇）

文章目录第一节：查询中国区域数据库第一节：查询中国区域数据库1、查询前5个省、区、市select * from t_province limit 5;select * from t_city limit 5;select * from t_district limit 5;如图：2、查询前10个省份，按名称升序排列select * from t_province order b...
复制链接

扫一扫