MySQL数据库练习，常用SQL语句

最新推荐文章于 2024-04-14 11:10:08 发布

i夏夕

最新推荐文章于 2024-04-14 11:10:08 发布

阅读量3.6k

点赞数 8

分类专栏： MySQL数据库文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/weixin_43919058/article/details/113877674

版权

MySQL数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MySQL数据库

创建表格
查询练习
SQL的四种连接查询
MySQL事务

创建表格

##创建数据库``
CREATE DATABASE school;

##创建学生表（student）

create table student(
	sno varchar(20) primary key,
	sname varchar(20) not null,
	ssex varchar(10) not null,
	sbirthday datetime,
	class varchar(20)
);

##添加学生信息

INSERT INTO `student` VALUES 
('101', '曾华', '男', '1977-09-01 00:00:00', '95033'),
('102', '匡明', '男', '1957-10-02 00:00:00', '95031'),
('103', '王丽', '女', '1976-02-23 00:00:00', '95033'),
('104', '李军', '男', '1976-02-20 00:00:00', '95032'),
('105', '王芳', '女', '1975-02-21 00:00:00', '95033'),
('106', '陆军', '男', '1976-12-23 00:00:00', '95033'),
('107', '王妮', '男', '1998-07-09 00:00:00', '95031'),
('108', '张全', '男', '1989-05-05 00:00:00', '95033'),
('109', '赵珠', '男', '1967-01-31 00:00:00', '95033');

##创建教师表（Teacher）
##教师编号
##教师名字
##教师姓别
##出生年月日
##职称
##所在部门

create table teacher(
	tno varchar(20) primary key,
	tname varchar(20) not null,
	tsex varchar(10) not null,
	tbirthday datetime,
	prof varchar(20) not null,
	depart varchar(20) not null
);

添加教师信息

insert into teacher (tno,tname,tsex,tbirthday,prof,depart) values
('804','李诚','男','1958-12-02','副教授','计算机系'),
('856','李旭','男','1969-03-12','讲师','电子工程系'),
('825','王萍','女','1972-05-05','助教','计算机系'),
('831','刘冰','女','1977-08-14','助教','电子工程系');

##创建课程表（Course）
##课程号
##课程名称
##教师编号

create table course(
	cno varchar(20) primary key,
	cname varchar(20) not null,
	tno varchar(20) not null,
	foreign key(tno) references teacher(tno)
);

添加课程信息

INSERT INTO `course` VALUES ('3-105', '计算机导论', '825');
INSERT INTO `course` VALUES ('3-245', '操作系统', '804');
INSERT INTO `course` VALUES ('6-166', '数字电路', '856');
INSERT INTO `course` VALUES ('9-888', '高等数学', '831');

##创建成绩表（Score）
##学号
##课程号
##成绩

create table score(
	sno varchar(20) primary key,
	cno varchar(20) not null,
	degree decimal,
	foreign key(sno) references student(sno),
	foreign key(cno) references course(cno)
);

添加成绩信息

INSERT INTO `score` VALUES ('103', '3-105', 92);
INSERT INTO `score` VALUES ('103', '3-245', 86);
INSERT INTO `score` VALUES ('103', '6-166', 85);
INSERT INTO `score` VALUES ('105', '3-105', 88);
INSERT INTO `score` VALUES ('105', '3-245', 75);
INSERT INTO `score` VALUES ('105', '6-166', 79);
INSERT INTO `score` VALUES ('109', '3-105', 75);
INSERT INTO `score` VALUES ('109', '3-245', 68);
INSERT INTO `score` VALUES ('109', '6-166', 81);

查询练习

1、查询student表的所有记录

SELECT	* FROM student;

在这里插入图片描述

2、查询student表中的所有记录的sname、ssex和class列

SELECT sname,ssex,class FROM student;

在这里插入图片描述

3、查询教师所有的单位即不重复（distinct）的depart列
distinct 排除重复

SELECT distinct depart FROM teacher;

在这里插入图片描述

4、查询score表中成绩在60到80之间
查询区间 between … and … 或直接使用运算符比较

SELECT * FROM score WHERE degree BETWEEN 60 AND 80;
或
SELECT * FROM score WHERE degree>60 and degree<80;

5、查询score表中成绩为85,86或88的记录
in 表示或者关系的查询，包含关系

SELECT * FROM score WHERE degree in(85,86,88);

在这里插入图片描述

6、查询student表中班级为“95031”班或性别为“女”的同学记录
or 表示或者

SELECT * FROM student WHERE class='95031' or ssex='女';

在这里插入图片描述

7、以class降序查询student表的所有记录
desc 降序 asc 升序

SELECT * FROM student ORDER BY class desc;

在这里插入图片描述

8、以cno升序、degree降序查询score表的所有记录

SELECT * FROM score ORDER BY cno asc,degree desc;

在这里插入图片描述

9、查询“95031”班的学生人数
count 统计

SELECT count(*) FROM student WHERE class='95031';

在这里插入图片描述

10、查询score表中的最高分的学生学号和课程号（子查询或者排序）

SELECT sno,cno FROM score WHERE degree=(SELECT max(degree) FROM score);
或
limit 第一个数字表示从第几条开始查，第二个数字表示查询几条数据
SELECT sno,cno FROM score ORDER BY degree desc LIMIT 0,1;

在这里插入图片描述
11、查询每门课的平均成绩
group by 表示分组查询

SELECT cno, avg(degree) FROM score GROUP BY cno;

在这里插入图片描述
12、查询score表中至少有2名学生选修的并以3开头的课程的平均分数
group by 分组
group by 字段 having 条件判断

SELECT *,count(cno),avg(degree) FROM score 
GROUP BY cno 
HAVING count(cno)>=2 
and cno LIKE '3%';

在这里插入图片描述

13、查询分数大于70，小于90的sno列

SELECT sno,degree,cno FROM score WHERE degree>70 and degree <90;
或
SELECT * FROM score WHERE degree BETWEEN 70 AND 90;

在这里插入图片描述

14、查询所有学生的sname、cno、和degree列
两表连接查询

先查出来两个表的所有数据
SELECT * FROM student;
SELECT * FROM score;
再查出来所要的数据
SELECT sname,cno,degree FROM student st,score sc WHERE st.sno=sc.sno;

在这里插入图片描述

15、查询所有学生的sno、cname和degree列
同上，两表连接查询

SELECT * FROM score;
SELECT * FROM course;
SELECT sno,cname,degree FROM score sc,course co WHERE sc.cno=co.cno;

在这里插入图片描述

16、查询所有学生的sname、cname和degree列
三表联立查询，as 可以设置别名

先查询出三表所有数据
SELECT * FROM student;
SELECT * FROM score;
SELECT * FROM course;
再联立查询出我们所需的列，as 设置别名
SELECT sname,cname,degree,st.sno as stu_sno,co.cno as cou_cno
FROM student st,score sc,course co 
WHERE st.sno=sc.sno and sc.cno=co.cno;

在这里插入图片描述

17、查询“95031”班学生每门课的平均分
用到的有avg 求平均数，子查询，group by分组，in

先分析
SELECT * FROM student WHERE class='95031';
SELECT * FROM score;
再结合在一起
SELECT cno,sno, avg(degree) 
FROM score 
WHERE sno in (SELECT sno FROM student WHERE class='95031') 
GROUP BY cno;

在这里插入图片描述

18、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录

SELECT * FROM score 
WHERE cno='3-105' and 
degree>(SELECT degree FROM score WHERE sno='109' and cno='3-105');

在这里插入图片描述
19、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录

##先分析
SELECT * FROM score WHERE sno='109' and cno='3-105';
##再结合
SELECT * FROM score WHERE degree>(SELECT degree FROM score WHERE sno='109' and cno='3-105');

在这里插入图片描述
20、查询和学号为108、101的同学同年出生的所有学生的sno、sname和sbirthday列

##先查出来学号为108,101的同学的出生年份
SELECT * FROM student WHERE sno in(108,101);
##然后再查出来同年出生的同学
SELECT sno,sname,sbirthday 
FROM student WHERE YEAR(sbirthday) 
in(SELECT YEAR(sbirthday) FROM student WHERE sno in(108,101));

在这里插入图片描述
22、查询“张旭”教师任课的学生成绩

##先找到张旭教师
SELECT tno FROM teacher WHERE tname='张旭';
##再找到张旭教师对应的课程号
SELECT cno FROM course WHERE tno=(SELECT tno FROM teacher WHERE tname='张旭');
##最后再查出来我们需要的数据
SELECT sno,cno,degree FROM score 
WHERE cno=(SELECT cno FROM course WHERE tno=(SELECT tno FROM teacher WHERE tname='张旭'));

在这里插入图片描述
22、查询选修某课程的同学人数多于3人的教师姓名

##先通过课程分组查询出选修课程的同学多于3人的课程号
SELECT cno FROM score GROUP BY cno HAVING count(sno)>3;
##再根据通过查询出符合要求的的课程号查询出教师编号
SELECT tno FROM course WHERE cno in (SELECT cno FROM score GROUP BY cno HAVING count(sno)>3);
##有了教师编号再查教师姓名就简单了，最后合在一起的SQL语句就是下面这个。
SELECT tname 
FROM teacher 
WHERE tno in 
(SELECT tno FROM course WHERE cno in (SELECT cno FROM score GROUP BY cno HAVING count(sno)>3));

在这里插入图片描述
23、查询“95033”和“95031”班全体学生的记录

SELECT * FROM student WHERE class in(95033,95031);

在这里插入图片描述
24、查询存在有85分以上成绩的课程cno

SELECT * FROM score WHERE degree>85;

在这里插入图片描述
25、查询出“计算机系”教师所教课程的成绩表

##1、先查询出计算机系的教师编号；
SELECT tno FROM teacher WHERE depart='计算机系';
##2、然后有了教师编号，可以在课程表中查到课程编号；
SELECT cno FROM course WHERE tno in(SELECT tno FROM teacher WHERE depart='计算机系');
##3、有了课程编号，就可以在成绩表中查到相应的成绩
SELECT * FROM score WHERE cno in (SELECT cno FROM course WHERE tno in(SELECT tno FROM teacher WHERE depart='计算机系'));

在这里插入图片描述
26、查询“计算机系”与“电子工程系”不同职称的教师的tname和prof
–union 求并集
–not in 不包含

SELECT * FROM teacher WHERE depart ='计算机系' and prof not in (SELECT prof FROM teacher WHERE depart='电子工程系')
union
SELECT * FROM teacher WHERE depart ='电子工程系' and prof not in (SELECT prof FROM teacher WHERE depart='计算机系');

在这里插入图片描述
27、查询选修编号为“3-105”课程且成绩至少高于选修编号为“3-245”的同学cno、sno和degree，并按degree从高到底依次排序
–any 表示至少一个

##先查询出两个课程的所有成绩
SELECT degree FROM score WHERE cno='3-105';
SELECT degree FROM score WHERE cno='3-245';
##再查询出我们要的数据
SELECT cno,sno,degree FROM score 
WHERE cno='3-105' 
and degree>any(SELECT degree FROM score WHERE cno='3-245') ORDER BY degree desc;

在这里插入图片描述
28、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的cno、sno和degree
–all表示所有

SELECT cno,sno,degree FROM score WHERE cno='3-105' and degree>all(SELECT degree FROM score WHERE cno='3-245');

在这里插入图片描述
29、查询所有教师和同学的name、sex和birthday
–union 并集 as 取别名

##union  并集    as  取别名
SELECT tname as name,tsex as sex,tbirthday as tbirthday FROM teacher
union
SELECT sname as name,ssex as sex, sbirthday as tbirthday FROM student;

30、查询所有“女”教师和“女”同学的name、sex和birthday
–union并集

SELECT tname as name,tsex as sex,tbirthday as tbirthday FROM teacher WHERE tsex='女'
union
SELECT sname as name,ssex as sex, sbirthday as tbirthday FROM student WHERE ssex='女';

在这里插入图片描述
31、查询成绩比该课程平均成绩低的同学的成绩表
–复制表数据做条件查询

##求各个课程的平均成绩
SELECT *,avg(degree) FROM score GROUP BY cno;
##复制表数据做条件查询
SELECT * FROM score a WHERE degree<(SELECT avg(degree) FROM score b WHERE a.cno=b.cno);

在这里插入图片描述
32、查询所有任课教师的tname和depart
–课程表中安排了课程

##先查出课程
SELECT * FROM course;
##再查出课程表中安排了课程的教师
SELECT tname,depart FROM teacher WHERE tno in (SELECT tno FROM course);

在这里插入图片描述
33、查询出至少有2名男生的班号
条件加分组筛选

SELECT class FROM student WHERE ssex='男' GROUP BY class HAVING count(*)>=2;

在这里插入图片描述
34、查询student表中不姓“王”的同学记录
not like 模糊查询取反

SELECT * FROM student WHERE sname not like '王%';

在这里插入图片描述
35、查询student表中每个学生的姓名和年龄
根据出生年月计算年龄

##year函数与now函数
SELECT sname,YEAR(NOW())-YEAR(sbirthday) as age FROM student ;

在这里插入图片描述
36、查询student表中最大和最小的sbirthday日期值
max 最大 min 最小

SELECT max(sbirthday),min(sbirthday) FROM student;

在这里插入图片描述
37、以班号和年龄从大到小的顺序查询student表中的全部记录
多字段排序

SELECT * FROM student ORDER BY class desc, sbirthday desc;

在这里插入图片描述
38、查询“男”教师及其所上的课程
子查询练习

##
SELECT * FROM teacher WHERE tsex='男';
##
SELECT * FROM course WHERE tno in(SELECT tno FROM teacher WHERE tsex='男');

在这里插入图片描述
39、查询最高分同学的sno、cno和degree列
max函数与子查询

SELECT * FROM score WHERE degree=(SELECT max(degree) FROM score);

在这里插入图片描述
40、查询和“李军”同性别的所有同学的sname

SELECT * FROM student WHERE ssex=(SELECT ssex FROM student WHERE sname='李军');

在这里插入图片描述
41、查询和“李军”同性别并同班的所有同学的sname

SELECT * FROM student 
WHERE ssex=(SELECT ssex FROM student WHERE sname='李军')
and class=(SELECT class FROM student WHERE sname='李军');

42、查询所有选修“计算机导论”课程的“男”同学的成绩表

##先通过课程表中查询出“计算机导论”课程的课程号，和学生表中“男”同学的学生号
SELECT cno FROM course WHERE cname='计算机导论';
SELECT sno FROM student WHERE ssex='男';
##再在成绩表中查询出我们要的
SELECT * FROM score WHERE cno=(SELECT cno FROM course WHERE cname='计算机导论') and sno in (SELECT sno FROM student WHERE ssex='男');

在这里插入图片描述
43、假设使用如下命令建立了一个grade表：

CREATE table grade(
	low int(3),
	upp int(3),
	grade char(1)

INSERT INTO `grade` VALUES (90, 100, 'A');
INSERT INTO `grade` VALUES (80, 89, 'B');
INSERT INTO `grade` VALUES (70, 79, 'C');
INSERT INTO `grade` VALUES (60, 69, 'D');
INSERT INTO `grade` VALUES (0, 59, 'E');

–现查询所有同学的sno、cno和grade列

SELECT sno,cno,grade FROM score,grade WHERE degree BETWEEN low and upp;

在这里插入图片描述

SQL的四种连接查询

CREATE DATABASE testJoin;
##创建两个表
--person 表
CREATE TABLE person(
	id int,
	name VARCHAR(20),
	cardId int
);
INSERT INTO `person`(`id`, `name`, `cardId`) VALUES (1, '张三', 1);
INSERT INTO `person`(`id`, `name`, `cardId`) VALUES (2, '李四', 3);
INSERT INTO `person`(`id`, `name`, `cardId`) VALUES (3, '王五', 6);
--card 表
CREATE TABLE card(
	id int,
	name VARCHAR(20)
);
INSERT INTO `card`(`id`, `name`) VALUES (1, '饭卡');
INSERT INTO `card`(`id`, `name`) VALUES (2, '建行卡');
INSERT INTO `card`(`id`, `name`) VALUES (3, '农行卡');
INSERT INTO `card`(`id`, `name`) VALUES (4, '工商卡');
INSERT INTO `card`(`id`, `name`) VALUES (5, '邮政卡');
--并没有创建外键

1、内连接 inner join 或者join
inner join 查询（内连接）

--内联查询，其实就是两张表中的数据，通过某个字段相对，查询出相关记录数据
SELECT * FROM person INNER join card on person.cardId=card.id;

在这里插入图片描述

2、外连接

1）、左连接 left join 或者 left outer join

--left join (左外连接)
--左外连接，会把左边表里面的所有数据取出来，而右边表中的数据，如果有相等的，就显示出来，如果没有，就会补NULL
SELECT * FROM person left join card on person.cardId=card.id;

在这里插入图片描述

2）、右连接 right join 或者 right outer join

--right join (右外连接)
--右外连接，会把右边表里面的所有数据取出来，而左边表中的数据，如果有相等的，就显示出来，如果没有，就会补NULL
SELECT * FROM person right join card on person.cardId=card.id;

在这里插入图片描述

3）、完全外连接 full join 或者 full outer join

##全外连接
SELECT * FROM person full join card on person.cardId=card.id;
执行出来会报错，因为MySQL不支持full join
--其实就跟下面的道理一样，就是两个表的合集，所有数据都查询出来，如果没有的就补NULL	
SELECT * FROM person left join card on person.cardId=card.id
union
SELECT * FROM person right join card on person.cardId=card.id;

在这里插入图片描述

MySQL事务

i夏夕

关注

8
点赞
踩
26

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据库练习，常用SQL语句

MySQL数据库创建表格查询练习创建表格##创建数据库``CREATE DATABASE school;##创建学生表（student）create table student( sno varchar(20) primary key, sname varchar(20) not null, ssex varchar(10) not null, sbirthday datetime, class varchar(20));##添加学生信息INSERT INTO `student`
复制链接

扫一扫