MySQL练习题分享——学习从建表到查询的相关命令

于 2021-01-30 15:11:02 发布
阅读量170 收藏 1
点赞数
分类专栏： MySQL 文章标签： 数据库 mysql java sql oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/LJBXE/article/details/113429303
版权

学生选课系统练习题

利用DDL（数据定义语言）来建库、建表、删库、删表、修改表：create 、drop、alter

利用DML（数据操作语言）来对数据进行插入、删除、更新：Insert 、 delete 、 update

利用DQL（数据查询语言）来对数据进行查询：select

先判断是否存在school的数据库，如果存在就先把它删除

drop database if exists school;

创建名为school的数据库并设置默认的字符集

create database school default charset utf8mb4;

切换到school数据库上下文环境

use school;

创建学院表

create table tb_college
(
collid 		int auto_increment comment '编号',
collname 	varchar(50) not null comment '名称',
collintro 	varchar(500) default '' comment '介绍',
primary key (collid)
);
  • not null ----> 非空约束，保持数据的完整性；
  • varchar(50) -----> 变长字符串
  • char (20) ----> 定长字符串
  • default 1 ------> 默认值约束,即当性别没有被输入时，默认值是’ ’ 空字符串
  • 主键（primary key）列：能够唯一确定一条记录的列，
  • comment 是添加注释，让别人能直观的看到的你的表格里面每个代表什么意思

修改学院表给学院名称列添加唯一性约束

  • 添加唯一性约束：add constraint 约束的名称 unique(需要添加唯一性约束的列)
alter table tb_college add constraint un_college_name unique (collname);

创建学生表

create table tb_student
(
stuid 		int not null comment '学号',
stuname 	varchar(20) not null comment '姓名',
stusex 		boolean default 1 comment '性别',
stubirth 	date not null comment '出生日期',
stuaddr 	varchar(255) default '' comment '籍贯',
collid 		int not null comment '所属学院',
primary key (stuid),
foreign key (collid) references tb_college (collid)
);
  • stusex char(1), or stusex boolean default 1, ----> 性别用这两种方法都可
  • foreign key 是添加的外键，把其他表里的主键添加到这里成为外键，建立两个表之间的关系

创建教师表

create table tb_teacher
(
teaid 		int not null comment '工号',
teaname 	varchar(20) not null comment '姓名',
teatitle 	varchar(10) default '讲师' comment '职称',
collid 		int not null comment '所属学院',
primary key (teaid),
foreign key (collid) references tb_college (collid)
);

创建课程表

create table tb_course
(
couid 		int not null comment '编号',
couname 	varchar(50) not null comment '名称',
coucredit 	int not null comment '学分',
teaid 		int not null comment '授课老师',
primary key (couid),
foreign key (teaid) references tb_teacher (teaid)
);

创建选课记录表

create table tb_record
(
recid 		int auto_increment comment '选课记录编号',
sid 		int not null comment '选课学生',
cid 		int not null comment '所选课程',
seldate 	datetime not null comment '选课时间日期',
score 		decimal(4,1) comment '考试成绩',
primary key (recid),
foreign key (sid) references tb_student (stuid),
foreign key (cid) references tb_course (couid),
unique (sid, cid)
);
  • 建立一个选课记录表来将课程和学生之间的多对多关系转变为两个多对一关系，实体表为学生表和课程表，关系表为选课记录表，其中选课记录表采用联合主键的方式(由学生表主键和课程表主键组成)建表。

插入学院数据

insert into tb_college (collname, collintro) values 
('计算机学院', '计算机学院1958年设立计算机专业，1981年建立计算机科学系，1998年设立计算机学院，2005年5月，为了进一步整合教学和科研资源，学校决定，计算机学院和软件学院行政班子合并统一运作、实行教学和学生管理独立运行的模式。 学院下设三个系：计算机科学与技术系、物联网工程系、计算金融系；两个研究所：图象图形研究所、网络空间安全研究院（2015年成立）；三个教学实验中心：计算机基础教学实验中心、IBM技术中心和计算机专业实验中心。'),
('外国语学院', '外国语学院设有7个教学单位，6个文理兼收的本科专业；拥有1个一级学科博士授予点，3个二级学科博士授予点，5个一级学科硕士学位授权点，5个二级学科硕士学位授权点，5个硕士专业授权领域，同时还有2个硕士专业学位（MTI）专业；有教职员工210余人，其中教授、副教授80余人，教师中获得中国国内外名校博士学位和正在职攻读博士学位的教师比例占专任教师的60%以上。'),
('经济管理学院', '经济学院前身是创办于1905年的经济科；已故经济学家彭迪先、张与九、蒋学模、胡寄窗、陶大镛、胡代光，以及当代学者刘诗白等曾先后在此任教或学习。');

插入学生数据

insert into tb_student (stuid, stuname, stusex, stubirth, stuaddr, collid) 
values
    (1001, '杨逍', 1, '1990-3-4', '四川成都', 1),
    (1002, '任我行', 1, '1992-2-2', '湖南长沙', 1),
    (1033, '王语嫣', 0, '1989-12-3', '四川成都', 1),
    (1572, '岳不群', 1, '1993-7-19', '陕西咸阳', 1),
    (1378, '纪嫣然', 0, '1995-8-12', '四川绵阳', 1),
    (1954, '林平之', 1, '1994-9-20', '福建莆田', 1),
    (2035, '东方不败', 1, '1988-6-30', null, 2),
    (3011, '林震南', 1, '1985-12-12', '福建莆田', 3),
    (3755, '项少龙', 1, '1993-1-25', null, 3),
    (3923, '杨不悔', 0, '1985-4-17', '四川成都', 3),
    (4040, '炼腰的隔壁老王', 1, '1989-1-1', '四川成都', 2);

删除学生数据

delete from tb_student where stuid=4040;

更新学生数据

update tb_student set stuname='杨过', stuaddr='湖南长沙' where stuid=1001;

插入老师数据

insert into tb_teacher (teaid, teaname, teatitle, collid) values 
(1122, '张三丰', '教授', 1),
(1133, '宋远桥', '副教授', 1),
(1144, '杨逍', '副教授', 1),
(2255, '范遥', '副教授', 2),
(3366, '韦一笑', default, 3);

插入课程数据

insert into tb_course (couid, couname, coucredit, teaid) values 
(1111, 'Python程序设计', 3, 1122),
(2222, 'Web前端开发', 2, 1122),
(3333, '操作系统', 4, 1122),
(4444, '计算机网络', 2, 1133),
(5555, '编译原理', 4, 1144),
(6666, '算法和数据结构', 3, 1144),
(7777, '经贸法语', 3, 2255),
(8888, '成本会计', 2, 3366),
(9999, '审计学', 3, 3366);

插入选课数据

insert into tb_record (sid, cid, seldate, score) values 
(1001, 1111, '2017-09-01', 95),
(1001, 2222, '2017-09-01', 87.5),
(1001, 3333, '2017-09-01', 100),
(1001, 4444, '2018-09-03', null),
(1001, 6666, '2017-09-02', 100),
(1002, 1111, '2017-09-03', 65),
(1002, 5555, '2017-09-01', 42),
(1033, 1111, '2017-09-03', 92.5),
(1033, 4444, '2017-09-01', 78),
(1033, 5555, '2017-09-01', 82.5),
(1572, 1111, '2017-09-02', 78),
(1378, 1111, '2017-09-05', 82),
(1378, 7777, '2017-09-02', 65.5),
(2035, 7777, '2018-09-03', 88),
(2035, 9999, curdate(), null),
(3755, 1111, curdate(), null),
(3755, 8888, curdate(), null),
(3755, 9999, '2017-09-01', 92);

查询所有学生的信息

select * from tb_student;
select stuid, stuname, stusex, stubirth, stuaddr, collid from tb_student;

查询所有课程名称及学分(投影和别名)

select couname, coucredit from tb_course;
select couname as 课程名称, coucredit as 学分 from tb_course;

查询所有学生的姓名和性别

select stuname as 姓名, stusex as 性别 from tb_student;
select stuname as 姓名, stusex+20 as 性别 from tb_student;
select stuname as 姓名, case stusex when 1 then '男' else '女' end as 性别 from tb_student;
select stuname as 姓名, if(stusex, '男', '女') as 性别 from tb_student;

查询所有老师的姓名和职称

select teaname as 姓名, teatitle as 职称 from tb_teacher;

查询所有女学生的姓名和出生日期(筛选)

select stuname, stubirth from tb_student where stusex = 0;

查询所有80后学生的姓名、性别和出生日期(筛选)

  • 不能连写，即 绝对不能这样子写z <= x <= y
  • 在执行筛选操作时，如果有多个条件，可以通过and和or条件进行组合
  • 也可以使用not将条件变成其对立面（逻辑变反）
select stuname, stubirth from tb_student where stubirth >= '1980-1-1' and stubirth <= '1989-12-31';
select stuname, stubirth from tb_student where stubirth between '1980-1-1' and '1989-12-31';

查询姓”杨“的学生姓名和性别(模糊)

select stuname, stusex from tb_student where stuname like '杨%';

查询姓”杨“名字两个字的学生姓名和性别(模糊)

select stuname, stusex from tb_student where stuname like '杨_';

查询姓”杨“名字三个字的学生姓名和性别(模糊)


select stuname, stusex from tb_student where stuname like '杨__';

select stuname, stusex from tb_student where stuname regexp '杨.';

查询名字中有”不“字或“嫣”字的学生的姓名(模糊)

select stuname, stusex from tb_student where stuname like '%不%' or stuname like '%嫣%';
  • 用union比用or性能要好很多
  • 求并集的时候union会自动去重
  • union all不会自动去重,会重复出现
select stuname, stusex from tb_student where stuname like '%不%' 
union
select stuname, stusex from tb_student where stuname like '%嫣%';

select stuname, stusex from tb_student where stuname like '%不%' 
union all
select stuname, stusex from tb_student where stuname like '%嫣%';

查询没有录入家庭住址的学生姓名(空值)

  • 在SQL中，用空值即null值跟任何数据做任何运算结果都是null，而null值相当于布尔值的假（不成立）
  • 不能写select stuname from tb_student where stuaddr=null;该条件啥也查不到
select stuname from tb_student where stuaddr<=>null;
select stuname from tb_student where stuaddr is null;

查询录入了家庭住址的学生姓名(空值)

  • 不能写select stuname from tb_student where stuaddr <> null;该条件啥也查不到
select stuname from tb_student where stuaddr is not null;

查询学生选课的所有日期(去重)

select distinct seldate from tb_record;

查询学生的家庭住址(去重)

select distinct stuaddr from tb_student 
union
select stuaddr from tb_student where stuaddr is not null;

select distinct stuaddr from tb_student where stuaddr is not null;

查询男学生的姓名和生日按年龄从大到小排列(排序)

  • order by 可以实现根据指定的字段排序，默认是升序，asc - 升序， desc - 降序
  • 排序的时候可以跟多个字段,用逗号隔开
select stuname, stubirth from tb_student where stusex = 1 order by stubirth;

select stuname, stubirth from tb_student where stusex = 1 order by stubirth desc;

select stuname, stubirth from tb_student where stusex = 1 order by stubirth desc, stuid asc;
  • 这种方法不是通用的SQL语句
  • curdate（）：获取当前的日期
  • datediff（）： 计算两个日期相差多少天
  • floor() : 下取整（取不超过指定数值的最大的整数）
select stuname, floor(datediff(curdate(), stubirth)/365) as age from tb_student where stusex = 1 order by age desc;

查询年龄最大的学生的出生日期(聚合函数)

select stuname, stubirth from tb_student order by stubirth limit 1;

select min(stubirth) from tb_student;

查询年龄最小的学生的出生日期(聚合函数)

select stuname, stubirth from tb_student order by stubirth desc limit 1;

select max(stubirth) from tb_student;

查询男女学生的人数(分组和聚合函数)

select stusex, count(stuid) as total from tb_student group by stusex order by total desc;

查询课程编号为1111的课程的平均成绩(筛选和聚合函数)

  • 在使用聚合函数时，null值是自动被忽略的
select avg(score) from tb_record where cid = 1111;

查询有多少个学生选了编号为1111的课程

select count(recid) from tb_record where cid = 1111;

查询有多少个学生参加了编号为1111的课程的考试并获得了成绩

select count(score) from tb_record where cid = 1111;

查询编号为1111课程的总分

select sum(score) from tb_record where cid = 1111;

查询学号为1001的学生所有课程的平均分(筛选和聚合函数)

select avg(score) from tb_record where sid = 1001;

查询每个学生的学号和平均成绩(分组和聚合函数)

  • round 四舍五入的函数，后面的1是指保留一位小数
select sid, round(avg(score), 1) as average from tb_record group by sid order by average desc;

查询平均成绩大于等于90分的学生的学号和平均成绩

  • 分组之前的筛选就要用where筛选
  • 分组之后的筛选要用having来进行筛选
select sid, avg(score) as average from tb_record group by sid having average >= 90;

查询年龄最大的学生的姓名(子查询)

  • 嵌套查询：把一个查询的结果作为另外一个查询的一部分来使用，也叫作子查询
select stuname from tb_student where stubirth = (select min(stubirth) from tb_student);

查询年龄最小的学生姓名和年龄(子查询+运算)

select stuname, floor(datediff(curdate(), stubirth)/365) as age from tb_student where stubirth = (select max(stubirth) from tb_student);

查询年龄最大的女学生的姓名

  • 条件要加在里面
  • 在外面再加一个stusex=1是为了防止里面的那个条件查出来的年龄一样的男学生和女学生各一名
select stuname from tb_student where stubirth = (select min(stubirth) from tb_student where stusex=0) and stusex=1;

查询年龄最大的男学生的姓名

select stuname from tb_student where stubirth = (select min(stubirth) from tb_student where stusex=1);

查询选了两门以上的课程的学生姓名(子查询/分组条件/集合运算)

  • 先写里面嵌套的查询，在往外面加东西
  • 先查学号,对学号进行分组再进行查询条件
  • in / not in 判断元素在不在集合中的成员运算
select stuname from tb_student where stuid in (select sid from tb_record group by sid having count(sid)>2);

查询每门课程的名称、学分和授课老师的姓名（连接查询/ 连表查询）

  • 连接两张表的时候如果没有连接条件就会形成笛卡尔乘积（9*5= 45）
  • 如果两张表有同名的列，在使用这个列的时候，需要使用完全限定名（表名。列名）
select couname, coucredit, teaname from tb_course, tb_teacher where tb_course.teaid = tb_teacher.teaid;

select couname, coucredit, teaname from tb_course t1 inner join tb_teacher t2 on t1.teaid = t2.teaid;

查询每个学生的姓名和平均成绩(嵌套查询+连接查询）

select stuname, avgscore from tb_student t1, (select sid, round(avg(score), 1) as avgscore from tb_record group by sid) t2 where stuid =sid;

select stuname, avgscore from tb_student t1 inner join (select sid, round(avg(score), 1) as avgscore from tb_record group by sid) t2 on t1.stuid = t2.sid;

查询学生姓名、课程名称以及成绩(连接查询)

select stuname, couname, score from tb_student, tb_course, tb_record where stuid = sid and couid = cid and score is not null;
select stuname, couname, score from tb_student inner join tb_record on stuid = sid inner join tb_course on couid=cid where score is not null;

按成绩排降序，取前5条记录 — 分页查询

  • 限制查询结果的数量，也叫作分页查询
select stuname, couname, score from tb_student, tb_course, tb_record where stuid = sid and couid = cid and score is not null order by score desc limit 5;

select stuname, couname, score from tb_student, tb_course, tb_record where stuid = sid and couid = cid and score is not null order by score desc limit 6, 5;

select stuname, couname, score from tb_student, tb_course, tb_record where stuid = sid and couid = cid and score is not null order by score desc limit 5 offset 6;

select + from字句 + where字句 + group by子句 + having子句 + order by 子句 + limit子句

查询选课学生的姓名和平均成绩(子查询和连接查询)

select stuname, avgscore from tb_student t1, (select sid, round(avg(score), 1) as avgscore from tb_record group by sid) t2 where stuid =sid;

查询每个学生的姓名和选课数量(左外连接和子查询)

  • 外连接：左外、右外、全外（MYSQL不支持）
  • 内连接：只能查出满足连表条件的记录
  • 左外连接：保证左表的记录要完整的查出来，即便他不满足连表条件,不满足连表条件的地方补null
  • 查询语句出现在前面的表叫左表，出现在后面的表叫右表
select stuname, ifnull(total, 0) from tb_student t1 left outer join (select sid, count(sid) as total from tb_record group by sid) t2 on t1.stuid=t2.sid;

知识点总结和补充

补充修改表的相关知识
  • 添加列：alter table tb_student add column stutel char(11) not null;
  • 删除列：alter table tb_student drop column stutel;
  • 修改列：
    • 要是名字没有修改，只是修改了数据类型可以就用modify
    • 要是名字和数据类型都修改了，只能使用change
    • alter table tb_student change column stuaddr stuaddr varchar(250);
    • alter table tb_student modify column stuaddr varchar(250);
    • alter table tb_student change column stusex stugender boolean default 1;
varchar
  • 用varchar尽量不要超过1024，要是需要上传很多内容，就可以使用longtext
  • integer: 4字节的整数
  • bigint （2的63次方） > integer (2的31次方)> smallint（2的15次方）> tinyint(人数没有超过100人)
数据库表之间的关系：一对多、多对一、多对多关系

例如在本练习题中，一个学院里面可以有很多个学生，一个学生通常只属于一个学院，所以他们之间的关系是：一对多（学院对学生），多对一（学生对学院），而在选课系统里面，一个学生可以选择多门课，一门课可以被多个学生选择，他们之间是多对多关系。

对于一对多和多对一关系
  • 在多的一方加一个外键列，维持多对一关系，保持‘一’的那方不懂，‘多’的那方建外键来保持联系
  • 在学生的表里面添加一个列，叫学院编号
    • alter table tb_student add column cid(新增列的名字，随便起) integer not null（约束条件必须和学院编号约束条件一样的）;
    • 再添加一个约束：学生标的学院编号一定要和学院表的学院编号保持一致，即添加一个外键约束
    • 该键在别的表里面是一个主键，在这个表里面不是主键
    • alter table tb_student add constraint fk_student_collid(给外键起一个名字) foreign key (collid) （学生表里面的哪个键被约束成外键） references tb_college(collid)(引用了学院表里面的collid);
    • 合并成一个动作：
    • alter table tb_student add column collid integer not null foreign key(collid) references tb_college(collid);
对于多对多关系

这种方式可以在二者之间建立一个新的中间表来时间将多对多关系转变为两个多对一关系，实体表为学生表和课程表，关系表为选课记录表，其中选课记录表采用联合主键的方式(由学生表主键和课程表主键组成)建表。

李家半仙儿
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值