Mysql入门练习题

Mysql入门练习题

记录自己学习的

Mysql入门练习题

一：建库建表

首先先建立一个数据库，在该数据库种建立三个表，一个学生表，一个课程表，还有一个学习表。

CREATE DATABASES 练习库;
USE 练习库
CREATE TABLE student(
	sid VARCHAR(8) PRIMARY KEY,
	sname VARCHAR(8),
	ssex VARCHAR(2),
	sbirthday INT,
	sorigin VARCHAR(20),
	scollege VARCHAR(15)
);
CREATE TABLE course(
	cid VARCHAR(10) NOT NULL UNIQUE PRIMARY KEY,
	cname VARCHAR(15),
	ctime INT,
	cstartday VARCHAR(15),
	cproperties VARCHAR(15)
);
CREATE TABLE learn(
	sid VARCHAR(8),
	cid VARCHAR(10),
	score INT,
	PRIMARY KEY(sid,cid),
	FOREIGN KEY(sid) REFERENCES student(sid),
	FOREIGN KEY(cid) REFERENCES course(cid)
);

二：基本表的修改和删除

1.修改基本表

例一：将student表中的sbirthday的类型修改为date类型

-- 这里使用的是alter table 表名 modify 属性 数据类型 约束条件(比如:主键、唯一、非空、默认等等)
ALTER TABLE student MODIFY sbirthday DATE;
-- desc 表名        查看表结构
DESC student;

例二：将student表中增加年龄"sage"属性,类型为int类型，没有约束条件

-- 语法：alter table 表名 add 属性 数据类型 约束条件
ALTER TABLE student ADD sage INT;
DESC student;

例三：将student表中的sage删除

-- 语法：alter table 表名 drop 属性/约束条件
ALTER TABLE student DROP sage;
DESC student;

2.基本表的删除

例四：将某某表删除（这里就不拿这几个表演示了哈）

DROP TABLE 表名;

如果你在这里想要删除student表是不会成功的，你知道为什么吗？

因为student表中的sid作为了learn表中的外键，你要想删除成功，就必须先把learn表给删除掉后再删student表就可以啦。

三：CRUD

1.插入数据

例五：将一个新学生记录插入到学生表中

-- 语法：insert into 表名(属性1,属性2....) values (要插入的数据)
INSERT 
INTO student
VALUES ('099999','夏雨','女','2000-10-10','海南','计算机');

insert语法要注意，表名后面跟的属性列可写可不写，但是建议大家写上。若是插入所有的属性可以省略不写，但是values后插入必须要写全部的属性常量，若有的根本就没值，那就写null，但千万不能漏掉任何一个属性。

例六：插入多条数据

INSERT 
INTO student(sid,sname,ssex,sbirthday,sorigin,scollege)
VALUES 
('100000','夏禾','女','2001-10-10','湖南','计算机'),
('100001','楚河','男','1999-8-10','北京','物联网'),
('100002','春幕',NULL,'2001-10-10','湖南','计算机');

2.删除数据

语法：delete from 表名 条件

例七：删除学号为100000的学生

DELETE
FROM student
WHERE sid=100000;

例八：删除姓名为春幕的学生

DELETE 
FROM student
WHERE sname='春幕';

例九：删除专业为物联网而且学号是100001的学生

DELETE 
FROM student
WHERE scollege='物联网'
AND sid='100001'; 

3.修改数据

语法：update 表名 set 列名=？ where 条件

例10：把春幕的性别修改为女

UPDATE student
SET ssex='女'
WHERE sname='春幕';

例11：把春幕的籍贯改成江苏

UPDATE student
SET sorigin='江苏'
WHERE sname='春幕';

4.查询数据

语法：

select <属性列表>
from <表名/视图名>,<表名>
[where <条件>]
[group by <列名>]
[having <条件>]
[order by <列名> asc/desc]

在进行查询前，我们先插入几条数据

-- student表数据
INSERT 
INTO student
VALUE 
('091501','王英','女','1997-11-03','河北','计算机'),
('091502','王小梅','女','2000-11-03','江苏','信电'),
('091503','张小飞','男','1996-11-13','江西','计算机'),
('091504','孙志鹏','男','1998-1-03','海南','计算机'),
('091505','徐颖','女','1997-11-03','江苏','外文'),
('091506','钱易蒙','男','2000-1-13','河北','信电');

-- course表数据
INSERT
INTO course
VALUE
('180101','C++程序设计',56,NULL,'必修'),
('180102','数据结构',48,180101,'必修'),
('180103','操作系统',48,180102,'必修'),
('180104','数据库原理',48,180103,'必修'),
('180105','DB_Design',32,180104,'选修');

-- learn表数据
INSERT
INTO learn
VALUE
('091501','180101',78),
('091501','180102',80),
('091501','180103',77),
('091503','180101',89),
('091503','180102',78),
('091503','180103',70),
('091503','180104',90),
('091504','180101',59),
('091504','180102',50),
('091504','180103',NULL);

<1>单表查询

(1)选择表中的若干列

例12：查询学生表所有学生的姓名和籍贯

SELECT sname,sorigin
FROM student;

例13：查询学生表中所有学生的籍贯（要求去重）

-- distinct去重关键字
SELECT DISTINCT sorigin
FROM student;

例14：查出学生表的所有信息

SELECT *FROM student;

(2)给列定义别名

使用AS来定义别名，其中AS可以省略

例15：查询学生的姓名和性别

SELECT sname 姓名,ssex 性别
FROM student;

(3)选择查询

使用where子句实现

例16：查询有不及格课程的学生的学号、课程号以及成绩

SELECT sid,cid,score
FROM learn
WHERE score<60;

例17：查询有不及格课程的学生的学号

SELECT DISTINCT sid
FROM learn
WHERE score<60;

例18：在student表中找出信电学院2000年后出生的学生记录

SELECT *
FROM student
WHERE scollege='信电' AND sbirthday>2000;

(4)确定范围

确定范围使用了一个关键字：属性 between xx and xx（包含xx,相当于 大于等于xx and 小于等于xx）

例19：查询学号在091503~091505的学生的姓名、性别、学院和出生日期。

SELECT sname,ssex,scollege,sbirthday
FROM student
WHERE sid BETWEEN '091503' AND '091505';

(5)确定集合

确定集合使用IN关键字

例20：查询信电学院、物联网学院和计算机学院的学生的学号、姓名和学院

SELECT sid,sname,scollege
FROM student
WHERE scollege IN('信电','计算机','物联网');

(6)字符匹配

字符匹配关键词like

例21：查询所有姓王的学生的姓名、学号和性别

SELECT sname,sid,ssex
FROM student
WHERE sname LIKE '王%';

例22：查找名字中第二个字为“小”字的学生的姓名和学号

SELECT sname,sid
FROM student
WHERE sname LIKE '%小%';

例23：查找课程名是DB_Design课程的课程号、课程性质

SELECT cid,cproperties
FROM course
WHERE cname LIKE 'DB_Design';

(7)空值

查询空值的关键字is null

例24：查询缺少成绩的学生的学号和相应的课程号

SELECT sid,score
FROM learn
WHERE score IS NULL;

(8)对查询结果进行分组

分组的关键词：group by

例25：查询各个课程号相应的选课人数

SELECT cid,COUNT(sid)
FROM learn
GROUP BY cid;

例26：查询学号在091501~091506至少选修了三门课程的学生的学号和选修课程的课程数

having短语用来指定筛选条件

SELECT sid,COUNT(cid)
FROM learn
WHERE sid BETWSELECT sid,score 
FROM learn
WHERE cid='180102'
ORDER BY score DESC;EEN '091501' AND '091506'
GROUP BY sid
HAVING COUNT(cid)>=3;

(9)使用集函数

例27：查询学生总人数

SELECT COUNT(sid) AS 总人数
FROM student;

例28：查询学习180101课程学生的平均成绩

SELECT AVG(score)
FROM learn
WHERE cid='180101';

例29：查询学习180101号课程的学生的最高分数

SELECT MAX(score)
FROM learn
WHERE cid='180101';

例30：查询选修了180102的课程的学生学号和成绩，查询结果按成绩从高到低排列

SELECT sid,score 
FROM learn
WHERE cid='180102'
ORDER BY score DESC;

例31：查询全体学生情况，查询结果按所在学院的名称升序排列，对同一个学院的学生按学号降序排列

SELECT *
FROM student
ORDER BY scollege ASC,sid DESC;

<2>连接查询

连接查询就慢慢的变难了，连接查询必须要掌握透彻！连接查询就是指，一个数据库中的多个表之间一般都存在某种内在联系，他们共同提供有用的信息，若查询同时涉及两个或多个表就是连接查询。前面的查询都是针对一个表进行的，较为简单。这后面的都是针对多表进行的，加油呀！

（1）ANSI方式

该方式可通过“表1 [inner] join 表2 on 连接条件”来实现两个表的连接。

例32：查询每个学生及其选修课程的情况

SELECT learn.sid,course.cname
FROM learn JOIN course ON learn.cid=course.cid
ORDER BY sid;

（2）theta方式

该方式通过where子句指定条件进行连接

例33：查询每个学生及其选修课程的情况

SELECT	l.sid 学号,c.cname 课程
FROM learn l,course c
WHERE l.cid=c.cid
ORDER BY l.sid;

（3）等值与非等值连接查询

1）什么是等值查询？

用来连接两个表的条件称为连接条件或连接谓词

一般形式:表名1.列名 比较运算符 表名2.列名

其中比较运算符主要有：=,>,<,>=,<=,!=

当比较运算符为=时，为等值连接，否则为非等值连接

注意：连接条件中的各连接字段类型必须是可比的，类型需要相同。

例：查询学生的姓名以及他们选的课程号，和课程名称

SELECT s.sname,l.cid,c.cname
FROM student s,learn l,course c
WHERE s.sid=l.sid AND l.cid=c.cid;

2）什么是自然连接？

自然连接是等值连接运算的一种特殊情况，即按照两个表中的相同属性进行等值连接，且目标列中去掉了重复的属性列，但保留了所有不重复的属性列。

例34：自然连接student和learn表

SELECT s.sid,sname,ssex,sbirthday,sorigin,scollege,cid,score
FROM student s,learn l
WHERE s.sid=l.sid;

（4）自身连接查询

例35：求一门课程的间接先选修

难题：遗留问题

（5）外连接查询

1）左外连接

左外连接什么时候用呢？

当规定所有记录都应该从连接语句左侧的表中返回。当右侧表中并没有匹配的记录时，左表中该记录依然会返回，而对应的右侧表中的列值将自动填充null值

例36：查询所有学生的姓名以及他们选修课程的课程号和成绩

SELECT s.sname,l.cid,l.score
FROM student s LEFT OUTER JOIN learn l ON s.sid=l.sid;

2）右外连接

右外连接什么时候使用呢？

当规定所有的记录都应该从连接语句右侧的表中返回。当左侧表中没有匹配的记录时，右表中的值依然返回，而对应的左表中的列值将自动填充null值

例37：查询所有的课程信息及选修该课程的学生的学号及成绩

SELECT course.cid,learn.sid,score
FROM course 
RIGHT OUTER JOIN learn ON learn.cid=course.cid;

（6）复合条件连接查询

例38：查询选修180101号课程且成绩在80分以上的学生的学号、姓名和成绩

SELECT s.sid,sname,l.score
FROM student s,learn l
WHERE s.sid=l.sid 
AND cid='180101'
AND score>80;

例39：查询每个学生及其选修的课程名及其成绩

SELECT s.sname,c.cname,l.score
FROM student s,learn l,course c
WHERE s.sid=l.sid
AND l.cid=c.cid;

（7）集合运算连接查询

SQL查询可以利用关系代数中的集合运算来组合关系，union代表了并集，intersect代表了交集，except代表了差集。但，注意：mysql中只支持union（∪）

例40：查询选修了180101号或180102号课程或二者都选修了的学生的学号、课程号和成绩

(SELECT s.sid,l.cid,l.score
FROM student s,learn l
WHERE s.sid=l.sid
AND l.cid='180101')
UNION
(SELECT s.sid,l.cid,l.score
FROM student s,learn l
WHERE s.sid=l.sid
AND l.cid='180102')

例41：查询同时选修了180101和180102号课程的学生的学号、课程号和成绩

mysql不支持,你可以试试

例42：查询选修了180101号课程的学生中没有选修180102号课程的学生学号、课程号和成绩

mysql不支持

<3>嵌套查询

在SQL中，一个select…from…where语句称为一个查询块，将一个查询块嵌套在另一个查询块的where子句或having条件中的查询成为嵌套查询。

使用方法：由里向外一层层的处理，根据子查询是否独立于父查询，可分为两类：

• 不相关子查询，该类中的子查询独立于上层的父查询，每个子查询在夫查询处理之前就可以完成求解，子查询的结果作为父查询的查找条件。
• 相关子查询，该类中的子查询依赖于父查询，最内层的子查询的执行需要用到父查询中的某些属性值。

例43：查询选修了180101号课程的学生姓名

-- 1.采用连接查询
SELECT s.sname
FROM student s,learn l
WHERE s.sid=l.sid
AND l.cid='180101';
-- 2.使用嵌套查询
SELECT sname
FROM student
WHERE sid IN
(SELECT sid
FROM learn
WHERE cid='180101');

（1）带有IN谓词的子查询

带有in谓词的子查询是指父查询与子查询之间用in进行连接，判断某个属性列值是否在子查询的结果中。

例44：查询与王英在同一个学院学习的学生学号和姓名

SELECT sid,sname
FROM student
WHERE scollege IN
(SELECT scollege
FROM student
WHERE sname='王英');

例45：查询选修了“数据库原理”课程的学生学号和姓名

-- 嵌套查询
SELECT sid,sname
FROM student
WHERE sid IN
(SELECT sid
FROM learn
WHERE cid IN
(SELECT cid
FROM course
WHERE cname='数据库原理'));
-- 连接查询
select s.sid,s.sname 
from student s,learn l,course c
where s.sid=l.sid
and l.cid=c.cid
and c.cname='数据库原理';

（2）带有比较运算符的子查询

子查询返回单值的时候可以使用比较运算符，可以使用ALL或ANY谓词来实现比较操作。

例46：查询其他学院中比计算机学院某个学生学号小的学生名单

难题：遗留问题

（3）带有exists谓词的子查询

exists代表存在量词，带有exists的子查询不返回任何数据，只产生逻辑真值“true”或者逻辑值“false”

例47：查询选修了180102号课程的学生学号和姓名

-- 第一种嵌套查询
select sname,sid
from student
where sid in
(select sid
from learn
where cid='180102');
-- 第二种
select sname,sid
from student
where exists(
select *
from learn
where student.sid=learn.sid
and learn.cid='180101');
-- 连接查询
select s.sname,s.sid
from student s,learn l
where s.sid=l.sid
and l.cid='180102';

例48：查询没有选修180102号课程的学生学号和姓名

select sname,sid
from student s
where not exists
(select *
from learn l
where s.sid=l.sid
and l.cid='180102');

例49：查询至少选修了091501号学生选修的全部课程的学生学号

难题：遗留问题

例50：查询选修了全部课程的学生的姓名

难题：遗留问题

例51：查询被所有学生都选修的课程

难题：遗留问题