投稿
  1. 极客之音首页
  2. 技术随笔

MySQL 实训一周，我一天搞完

飞熊 • • 技术随笔 • 阅读 254

勤奋不是嘴上说说而已，而是实际的行动，在勤奋的苦度中持之以恒，永不退却。业精于勤，荒于嬉；行成于思，毁于随。在人生的仕途上，我们毫不迟疑地选择勤奋，她是几乎于世界上一切成就的催产婆。只要我们拥着勤奋去思考，拥着勤奋的手去耕耘，用抱勤奋的心去对待工作，浪迹红尘而坚韧不拔，那么，我们的生命就会绽放火花，让人生的时光更加的闪亮而精彩。

导读：本篇文章讲解 MySQL 实训一周，我一天搞完，希望对大家有帮助，欢迎收藏，转发！站点地址：www.bmabk.com，来源：原文

数据库建表插入语句

# 项目一创建库、表、准备数据
# 创建库
create database stumis charset=utf8;
show databases;
use stumis;
#创建department表
create table department(
departid int primary key,
departname varchar(20),
office varchar(20),
tel varchar(14),
chairman varchar(20));

#创建class表
create table class(
classid varchar(60) primary key,
departid int,
classname varchar(40),
monitor varchar(20),
foreign key(departid) references department (departid));

#创建课程表
create table course(
cid varchar(10) primary key,
cname varchar(40) not null,
ctype varchar(20) not null,
ctime varchar(30) not null,
teacher varchar(10),
smallnum int not null,
registernum int not null);

#创建学生表
create table student(
stuid varchar(10) primary key,
stuname varchar(10) not null,
stusex varchar(2) not null,
stupwd varchar(7) not null,
stuage int,
classid varchar(10),
address varchar(100) not null,
foreign key (classid) references class (classid));

#创建成绩表
create table score(
stuid varchar(10),
cid varchar(10),
score int,
primary key(stuid,cid),
foreign key (stuid) references student (stuid),
foreign key (cid) references course (cid)
);

desc department;
#准备数据
insert into department values(1,'计算机系','A502','4040','孙丰伟'),
(2,'经济管理系','A305','4024','李红艳'),
(3,'水晶石','A218','4012','吴可鹏'),
(4,'通信系','A308','4089','张楠'),
(5,'机电系','A216','4033','吴宝宝'),
(6,'建筑系','A212','4036','包伟丽');
select * from department;
desc class;
insert into class values
('15111',1,'软件中软一班','王波'),
('15112',1,'软件中软二班','江河'),
('15113',1,'软件中软三班','任永琼'),
('15114',1,'软件普软四班','张泽星'),
('15121',1,'网络一班','张丽'),
('15221',1,'电商一班','李世群'),
('15231',2,'物流一班','张松'),
('15321',3,'通信一班','沈佳萍'),
('15411',4,'动漫一班','谢嘉'),
('15511',5,'汽修一班','李平'),
('15611',6,'桥梁一班','张敏芳');
select * from class;
insert into course values
('10101','工程测量','工程技术','周一3-4节','孙瑞晨',20,11),
('10103','桥梁建筑','工程技术','周一1-2节','黄金晓',15,12),
('10107','道路建筑材料','工程技术','周二3-4节','陈婷婷',20,17),
('20103','仓储与配送管理','管理','周二5-6节','陈科',15,26),
('20106','物流管理','管理','周一3-4节','严莉莉',30,36),
('30106','计算机应用基础','计算机','周三7-8节','胡灵',20,31),
('30107','计算机组装与维护','计算机','周三3-4节','盛利',30,36),
('30108','电子电工技术','计算机','周四1-2节','吴晓红',20,28),
('30214','数据库技术及应用','计算机','周四3-4节','曾飞燕',30,33),
('40103','通信设备管理','通信','周五1-2节','丁亮',30,37),
('51204','动画设计','动漫','周五3-4节','李明华',20,28),
('61008','机械制图','建筑','周五3-4节','张世清',20,28);
select * from class;

#向学生表中插入数据
insert into student values
('1511101','何英国','女','123456',17,'15111','荆门'),
('1511102','方振','男','123456',16,'15111','荆门'),
('1511103','雷英飞','男','abc123',18,'15111','武汉'),
('1511104','金丹','女','765123',20,'15111','武汉'),
('1511105','秦淼英','女','123',21,'15111','黄冈'),
('1511201','奉雷刚','男','1511201',19,'15112','武汉'),
('1511202','杨伟红','女','1511202',17,'15112','黄石'),
('1511203','周晓影','女','1511203',18,'15112','黄石'),
('1511204','张义军','男','1511204',20,'15112','黄石'),
('1511205','朱寒松','男','1511205',21,'15112','宜昌'),
('1522101','黄泽琼','女','123456',20,'15221','荆门'),
('1522102','任海文','男','123456',17,'15221','武汉'),
('1522103','孙雪琴','女','abc123',20,'15221','荆门'),
('1522104','李爱华','男','765321',18,'15221','武汉'),
('1522105','何茂林','男','123',18,'15221','黄石'),
('1523101','罗峰','男','1523101',17,'15231','黄石'),
('1523102','宁梦涵','女','1523102',18,'15231','宜昌'),
('1523103','谭倩倩','女','1523103',18,'15231','宜昌'),
('1523104','谭倩倩','女','1523104',21,'15231','黄石'),
('1523105','谭倩倩','女','1523105',18,'15231','荆门'),
('1551101','蔡诗川','女','1511201',18,'15511','武汉'),
('1551102','陈露玲','女','1511202',18,'15511','黄石'),
('1551103','程英','女','1511203',18,'15511','黄石'),
('1551104','王作杰','男','1511204',19,'15511','黄石'),
('1551105','艾青','男','1511205',19,'15511','黄石');

insert into score values
('1511101','30106',89),
('1511101','30214',89),
('1511102','30106',96),
('1511102','30214',96),
('1511103','30106',59),
('1511103','30214',59),
('1511104','30106',65),
('1511104','30214',65),
('1511105','30106',88),
('1511105','30214',88),
('1511201','30106',99),
('1511201','30214',79),
('1511202','30106',55),
('1511202','30214',45),
('1511203','30106',67),
('1511203','30214',87),
('1511204','30106',65),
('1511204','30214',95),
('1511205','30106',78),
('1522101','20103',63),
('1522102','20103',20),
('1522103','20103',98),
('1522104','20103',85),
('1522105','20103',65),
('1523101','20106',88),
('1523102','20106',90),
('1523103','20106',95),
('1523104','20106',66),
('1523105','20106',53);

select * from score;

实训题目及答案

①修改表中数据。
#修改分数表中学号为1511101的学生的总学分为55分。
UPDATE score set score = 55 WHERE stuid = "1511101";
#修改学生表中学号为1511102的学生的密码为abc123，年龄为20 (一次改变多字段的值)
UPDATE student set stupwd = "abc123", stuage = 20 WHERE stuid = "1511102";
#修改学生表李爱华的性别为女。
UPDATE student set stusex = "女" WHERE stuname = "李爱华";
②查询单表中部分字段。
#查询所有学生的学号和姓名。
SELECT stuid, stuname FROM student;
#查询所有学生的姓名和家庭住址。
SELECT stuname, address FROM student;
#查询所有系部的名称和联系电话。
SELECT departname, tel FROM department;
#查询所有课程名称和该课程授课教师信息。
SELECT cname, teacher FROM course;
#查询所有班级名称和班长信息。
SELECT classname, monitor FROM class;
③重命名检索得到的字段:要求用汉字作为字段名。
#查询所有学生的姓名和性别。
select stuname as "名字", stusex as "性别" from student;
#查询所有学生的姓名、年龄和家庭住址。
select stuname as "姓名", stuage as "性别", address as "家庭住址" from student;
#查询所有系部的名称和办公室信息。
SELECT departname as "名称", office as "办公室" from department;
#查询所有课程名称、授课时间和该课程授课教师信息。
SELECT cname as "课程名称", ctime as "授课时间", teacher as "授课教师" from course;
#查询所有班级名称和班长信息。
SELECT classname as "班级", monitor as "班长" from class;
④返回结果集中的部分行。
#返回学生成绩表中前10条数据。
select * from score LIMIT 0, 10;
#返回课程表中前8条数据的课程名称和选课人数。
select cname, registernum from course LIMIT 0,8;
#返回系部表中的第1条数据。
SELECT * from department LIMIT 0, 1;
#返回课程表表中的第5~8条数据。
SELECT * from course LIMIT 4, 4;
⑤消除重复行。
#返回学生表所有不重复的班级编号，字段名使用汉字显示。
SELECT DISTINCT classid as "班级" from student;
#返回班级表中所有不重复的家庭住址，字段名使用汉字显示。
SELECT DISTINCT address as "家庭住址" from student;
#返回成绩表中不重复的课程编号，字段名使用汉字显示。
SELECT DISTINCT  cid as "课程编号" from score;
#返回课程表中不重复的授课时间，字段名使用汉字显示。
SELECT DISTINCT ctime as "授课时间" from course;
#返回课程表中不重复的课程所属类别，字段名使用汉字显示。
SELECT DISTINCT ctype as "类型" from course;
#返回学生表中每个班级的年龄信息。
SELECT DISTINCT stuage from student;
⑥条件查询。
#班级编号为15221的所有学生信息。.
SELECT * from student where classid = "15221";
#生源地为‘武汉’的所有学生信息。
SELECT * from student where address = "武汉";
#生源地为荆门的学生的姓名、性别、年龄和生源地。
SELECT stuname, stusex, stuage, address from student WHERE address = "荆门";
#性别为女的所有学生信息。
SELECT * from student where stusex = "女";
#课程类别为‘工程技术’的所有课程信息。
SELECT * from course where ctype = "工程技术";
#学生密码和学号相同的学生信息。
SELECT * from student where stupwd = stuid;
#班级编号为15221且生源地为. 武汉’的学生姓名、生源地信息。
SELECT stuname, address from student where classid = "15221" and address = "武汉";
#性别为女的生源地为“荆门”的所有学生信息。
SELECT * from student where stusex = "女" and address = "荆门";
#20岁的男生班级编号、姓名和生源地。
SELECT classid, stuname, address from student where stuage = 20 and stusex = "男";
#周四1 ~2节上课的计算机类的课程信息。
SELECT * from course where ctime = "周四1-2节" and ctype = "计算机";
#报名人数下线为30的计算机类课程信息。
SELECT * from course where ctype = "计算机" and smallnum >= 30;
#班级编号为15221或者生源地为‘ 武汉’的学生全部信息。
SELECT * FROM student where classid = "15221" or address = "武汉" ;
#工程技术类和管理类的所有课程。
SELECT * from course where ctype in("工程技术", "管理");
#15112和15221所有班级学生信息。
SELECT * from student where classid in("15112", "15221");
#生源地为黄石和武汉的所有信息。
select * from student where address in("黄石","武汉");
#院系编号为2和3的所有班级信息。
SELECT * from department where departid in(2, 3);
#显示所有大于18岁的学生信息。
SELECT * from student where stuage > 18;
#显示所有不是18和20岁的学生信息。
SELECT * from student where stuage not in(18, 20);
#显示所有不能正常开课的课程信息。
SELECT * from course where smallnum > registernum;
#显示所有不及格的学生成绩。
SELECT * from score where score < 60;
#报名人数大于等于30人，并且小于等于36人的课程信息。
SELECT * from course where registernum between 30 and 36;
#年龄为17、19、 21的所有学生信息。
SELECT * from student where stuage in (17, 19, 21);
#黄冈、黄石、宜昌所有18岁女学生的学号、姓名、年龄和家庭住址。
SELECT stuid, stuname, stuage, address from student where stuage = 18 and stusex = "女" and address in ('黄冈','黄石','宜昌');
#计算机和工程技术类的所有课程信息。
SELECT * from course where ctype in("计算机", "工程技术");
#年龄在18~20之间的学生的班级编号和姓名。
SELECT classid, stuname from student where stuage between 18 and 20;
#年龄在18 ~20之间的黄石所有学生信息。
SELECT * from student where address = "黄石" and stuage between 18 and 20;
#年龄在18~20之间的所有学生信息和武汉的所有学生信息。
SELECT * from student where stuage between 18 and 20;
SELECT * from student where address = "武汉";
#年龄不是18 ~ 20之间的所有学生信息(2 种方法)。
SELECT * from student where stuage not in (18, 19, 20);
SELECT * from student where stuage not in (stuage between 18 and 20);
⑦模糊查询like。
#院系表中电话带有03两个连续数字的院系信息。
SELECT * from department where tel  like "%03%";
#院系表中主任中姓李的院系信息
SELECT * from department where chairman like "李%";
#课程表中课程名中带有“计算机”三个字的所有课程信息。
SELECT * from course where cname like "%计算机%";
#课程表中课程编号以3开头的所有课程信息。
SELECT * from course where cid like "3%";
#所有3~4节上课的课程信息。
SELECT * from course where ctime like "%3-4节%";
#学生表中学生班级编号中有31的所有学生信息
SELECT * from student where classid like "%31%";
#课程表中所有周二上课的课程名称、上课教师和详细的上课时间。
SELECT cname, teacher, ctime from course where ctime like "周二%";
#学生表中姓周的女生信息。
SELECT * from student where stuname like "周%" and stusex = '女';
#课程表中1-2节上课的计算机类课程信息。
SELECT * from course where ctype = "计算机" and ctime like "%1-2节";
#姓名中带有“军”字的学生信息。
SELECT * from student where stuname like "%军%";
#查询姓孙的授课教师的开课信息。(如果老师要开课、那么注册人数要大于最小人数）
SELECT * from course where teacher like "孙%" and smallnum < registernum;
#所有周一开课的课程信息。
SELECT * from course where ctime like "周一%";
#所有名字是2个字的学生信息。(两种方法)(提示字符串函数、字符串匹配)
SELECT * from student where stuname like "__" ;
#姓李和姓王的所有学生信息。
SELECT * from student where stuname not in ("李%", "王%");
#系主任不姓张和李的所有系部信息。
SELECT * from department where chairman not in ("张%","李%");
#办公室office中没有1和2 的所有系部信息。
SELECT * from department where office not in (1, 2);
#学生姓名第三个字是“英”的所有学生的姓名和班级编号。
SELECT stuname, classid from student where stuname like "__英";
#姓名的第二个字不是“文”的所有学生的学号和姓名信息。
 SELECT stuid, stuname from student where stuname not like "_文%";
#密码中带有字母b或数字7的所有学生信息。
SELECT * from student where stupwd like "%b%" or stupwd like "%7%";
⑧排序练习。
#检索course表的课程名称、授课教师、最低限制开班人数和报名人数，要求检索结果按照最低限制开班人数的升序排列;最低限制开班人数相同时，则按照报名人数的降序排列。
SELECT cname, teacher, smallnum, registernum from course ORDER BY smallnum, registernum desc;
#检索student表的学号、姓名、性别、年龄，要求检索结果按照年龄升序排列。年龄相同的，女生在前男生在后排列(降序)。
SELECT stuid, stuname, stusex, stuage from student ORDER BY stuage, stusex;
#检索sc表的信息，要求按照课程编号升序排列，课程编号相同的，按照成绩降序排列。
SELECT * from score ORDER BY cid, score desc;
#检索student表的信息，要求按照班级升序排列，班级相同的，按照学号升序排列。
SELECT * from student ORDER BY classid, stuid;
#检索成绩表的信息，要求按照课程编号升序排列，课程编号相同的，按照成绩降序排
SELECT * from score ORDER BY cid, stuid;
#课程表中所有信息，要求按照报名人数从多到少的顺序显示。
SELECT * from course ORDER BY registernum desc;
⑨聚合函数。
#检索student表中最小的年龄。
SELECT MIN(stuage) from student;
#检索student表中最大的年龄。
SELECT MAX(stuage) from student;
#检索student表中平均年龄。
SELECT AVG(stuage) from student;
#检索score表中的总成绩。
SELECT SUM(score) from score;
#检索class表中一共有多少个班级。
SELECT COUNT(classname) from class;
#查询15112班级有多少个学生。
SELECT COUNT(stuid) from student where classid = "15112";
#查询成绩表中有多少个学生选择课程编号为30214课程。
SELECT COUNT(stuid) from score where cid = "30214";
#最低限制开班人数和报名人数之差最大的课程信息。
SELECT * from course where registernum - smallnum = (SELECT abs(MAX(registernum - smallnum)) from course);
#检索student表中最小的年龄、
SELECT min(stuage) from student;
#十、group by having 分组查询。
#统计各个系部班级数量。
SELECT COUNT(classname) from class GROUP BY departid;
#统计各个班级男女生人数。
SELECT classid, stusex, COUNT(stusex) from student GROUP BY  classid, stusex;
#统计课程表中各类课程的数量。
SELECT cname, COUNT(cname) from course GROUP BY cname;
#统计成绩表中各科每一科的平均成绩。
SELECT cid, AVG(score) from score GROUP BY cid;
#按家庭住址分类统计各个家庭住址学生的平均年龄。
select address, AVG(stuage) from student GROUP BY address;
#按班级分类统计各个班级的学生的平均年龄。
SELECT classid, AVG(stuage) from student GROUP BY classid;
#查询15112班级男生与女生人数。
SELECT stusex, COUNT(stuid) from student where classid = "15221" GROUP BY stusex;
#查询每门课的平均成绩大于70的课程
SELECT cid, AVG(score) from score GROUP BY cid HAVING AVG(score) > 70;
#统计各个班级男女生人数，返回人数大于3人的。
SELECT classid, stusex, COUNT(stusex) from student GROUP BY  classid, stusex HAVING COUNT(stusex) > 3;
#在学生表中按照班级分组、并且显示每个班级的姓名
SELECT classid from student GROUP BY classid;
#case when。
#显示学生成绩表中所有信息，要求使用大于等于60小于70及格、大于等于70小于80为良好、80以上包括80是优秀显示成绩，
SELECT stuid, cid, CASE 
	WHEN score >= 80 THEN
		"优秀"
		WHEN score >= 70 and score <= 80 THEN
		"良好"
		WHEN score >= 60 and score <= 70 THEN
		"合格"
		ELSE
		"不合格"
END as "得分"
from score;
#显示所有学生姓名和年龄，要求使用.成年’或者‘未成年’显示年龄。
SELECT stuname, CASE 
	WHEN stuage >= 18 THEN
		"成年"
	ELSE
		"未成年"
END  as age
from student;
#12 in的应用(子查询)。
#查询计算机系所有班级信息。
SELECT * from class where departid in (SELECT departid from department where departname = "计算机系");
#查询计算机系和经济管系所有班级。
SELECT * from class where departid in (SELECT departid from department where departname in ("计算机系","经济管理系"));
#查询“汽修一班”所有学生信息。
SELECT * from student where classid in (SELECT classid from class where classname = "汽修一班");
#系部主任是“孙丰伟”的所有班级信息。
SELECT * from class where departid in (SELECT departid from department where chairman = "孙丰伟");
#查询系主任姓吴的系部的所有班级。来自武汉的同学信息。
SELECT * from student where classid in (SELECT classid from class where departid in(SELECT departid from department where chairman like "吴%")) and address = "武汉";
#“管理”类课程的成绩。
SELECT * from score where cid in (SELECT cid from course where ctype = "管理");
#查询来自武汉和荆门的学生的考试成绩信息。
SELECT score from score where stuid  in (SELECT stuid from student where address in ("武汉", "荆门"));
#查询参加“计算机应用基础”和“数据库技术及应用"考试的学生成绩信息。
SELECT score from score where cid in (SELECT cid from course where cname in ("计算机应用基础", "数据库技术及应用"));
#十三多表联合查询。
#显示系部编号，系部名称和班级名称、班长。
SELECT a.departid, a.departname, b.classname, b.monitor from department as a LEFT JOIN class as b on a.departid = b.departid;
#显示所有学生的班级编号、班级名称、学生姓名。
SELECT a.classid, a.classname, b.stuname from class as a LEFT JOIN student as b on a.classid = b.classid;
#14通过计算获得新列。
#将成绩表中100分制使用满分为120分的成绩。显示所有字段。
SELECT stuid, cid, score * 1.2  as score from score;
#删除所有性别为女的学生信息。
SET FOREIGN_KEY_CHECKS = 0;
DELETE from student where stusex = "女";
#删除经济管理系男生的信息
SET FOREIGN_KEY_CHECKS = 0;
DELETE from student where stusex = "男" and classid in (SELECT classid from class where departid in (SELECT departid from department where departname = "经济管理系"));
#上面的删除操作会失败、应为在score表中的学生id和student做了外检关联

#查看外检关联的状态、可以看到现在是为1、我们把它修改为0

#修改的方法

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/141663.html

赞 (0)
飞熊的头像飞熊bm
0 0

相关推荐

发表回复

登录后才能评论
极客之音——专业性很强的中文编程技术网站，欢迎收藏到浏览器，订阅我们！