SQL实操小记

最新推荐文章于 2024-04-01 13:05:38 发布

xor0ne_10_01

最新推荐文章于 2024-04-01 13:05:38 发布

阅读量890

点赞数 1

分类专栏：书中不求甚解

本文链接：https://blog.csdn.net/weixin_43901038/article/details/105186907

版权

书中不求甚解专栏收录该内容

35 篇文章 3 订阅

订阅专栏

（1）、首先我的环境是： xmapp + Navicat.
（额，我们老师比较推荐sqlever，但是我的 sqlever2008 问题太多了，所以才用这个的，假设你用的是sqlever的话，可以跳过0。做好准备这部分，因为不大一样！）
（2）、参考书籍：《数据库系统概论》(第五版) 王珊萨师煊编著。
（3）、课程链接：
https://www.icourse163.org/spoc/learn/ECJTU-1450285308?tid=1450721591#/learn/announce

PS：本文的内容比较适合小白，所以还请大佬勿喷啦！>^<, 嘻嘻 (●’◡’●)！由于文章分的有点细，在看文章的时候可能会觉得有点小乱啦，所以觉得有点乱的时候看看目录啦，这样对于自己记忆会更好哦！嘻嘻(●’◡’●)，接下来让我们一起开始SQL之旅吧！

文章目录

0、做好准备

首先连接好相应的主机和IP，然后对需要建库的主机，单机右键 --> 命令列界面 --> 输入相应的命令

在这里插入图片描述

在这里我先给出需要录入完成数据的表格，这样对于下面的查询语句及其对应的查询结果更加了解。

在这里插入图片描述

1、建数据库

CREATE DATABASE ST;

2、建表：建表头

常见数据类型：
int,flost,real,numeric,datetime,money

CREATE TABLE Student
(
Sno	CHAR(9)	PRIMARY KEY,	/*列级完整性约束条件*/
Sname	 CHAR(20) CHAR(20) UNIQUE,	/*Sname取唯一值*/
Ssex	CHAR(2),
Sage	SMALLINT,
Sdept	CHAR(20)
);

CREATE TABLE Course
    -> (Cno CHAR(4) PRIMARY KEY,
    -> Cname CHAR(40) UNIQUE,
    -> Cpno CHAR(4),
    -> Ccredit SMALLINT,
    -> FOREIGN KEY(Cpno) REFERENCES Course(Cno));

CREATE TABLE SC
    -> (Sno CHAR(9),
    -> Cno CHAR(4),
    -> Grade SMALLINT,
    -> PRIMARY KEY(Sno, Cno),
    -> FOREIGN KEY(Sno) REFERENCES Student(Sno),
    -> FOREIGN KEY(Cno) REFERENCES Course(Cno));

3、录入数据

INSERT 
INTO	Student(Sno,Sname,Ssex,Sdept,Sage)
VALUES	('201815121','李勇','男','CS',20);

4、单表查询

在这里插入图片描述

（1）、查询表中的若干列

SELECT Sno
FROM	sc;
#DISTINCT 消除查询结果中重复的元组
SELECT DISTINCT Sno
FROM	sc;

（2）、查询满足条件的元组

通过WHERE子句实现

在这里插入图片描述

A、比较大小

例4：查询所年龄超过18岁的学生姓名及其年龄

SELECT Sname,Sage
FROM	student
WHERE Sage>18;
#查询考试成绩有不及格的学生的学号
SELECT DISTINCT Sno
FROM	sc
WHERE Grade<60;

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

B、多表查询

逻辑运算符的优先级是NOT > AND > OR

例6：查询选修了1号或2号课程并且成绩大于等于90分的学生、课程号和成绩
SELECT Sno,Cno,Grade
FROM	sc
WHERE (Cno='1'OR Cno='2') AND Grade>=90;

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

C、确定范围

例7：查询年龄在20~30岁（包括20和30）之间的学生的姓名、所在系和年龄

SELECT Sname,Sdept,Sage
FROM student
WHERE Sage BETWEEN 20 AND 23;
#WHERE Sage >=20 AND Sage <= 30;

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

D、确定集合

例8：查询选修了1号或2号课程的学生的学号、课程号和成绩

SELECT Sno,Cno,Grade
FROM sc
WHERE Cno in ('1','2');
#WHERE Cno='1' or Cno='2';
注意不要在括号里面加 OR 了哦，这样查询出来的只是 Cno = '1'的结果
#WHERE Cno in ('1' OR '2');

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

E、字符匹配（模糊匹配）

在这里插入图片描述

例9：查询所有姓“刘”的学生的姓名和性别

SELECT Sname,Ssex
FROM student
WHERE Sname LIKE '刘%';

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

在这里插入图片描述

F、控制查询

通常一个列没有输入值时，该列就是空值
空值不同于0和空格，他不占任何存储空间

例11：查询没有考试成绩的学生的学号和相应的课程号

SELECT Sno,Cno
FROM sc
WHERE Grade IS NULL;

 #WHERE Grade = NULL 在上课时老师强调这个不能改为 Grade = NULL
 #但是我在navicat建表时，由于无法不输入，因此对于空值我输入的都是NULL，所以在此，我可以也可以使用 Grade = NULL。

（3）、使用聚集函数的查询

在这里插入图片描述

例1、查询学生总人数
SELECT COUNT(Sno)
#SELECT COUNT(*)
FROM student;

例2：查询选修了课程的学生人数
# DISTINCT 避免重复计算学生人数
SELECT COUNT(DISTINCT Sno)
FROM sc;

例3、查询学号为”201815122“的学生选修的课程的总分和平均分
SELECT SUM(Grade),AVG(Grade)
FROM sc
WHERE Sno = '201815122';

例4、查询选修2号课程的最高分和最低分
SELECT MAX(Grade),MIN(Grade)
FROM sc
WHERE Cno='2';

查询结果：

在这里插入图片描述

（4）、对查询结果分组

通过 GROUP BY 子句实现
分组方法：按指定的一列或多列值分组，值相等的为一组。
SELECT子句的目标列表达式列表中只能出现分组属性和聚集函数
GROUP BY 子句细化了聚集函数的作用对象：

未对查询结果分组，聚集函数将作用于整个查询结果
对查询结果分组后，聚集函数将分别作用于每个组

例5、查询每个课程号及其选课人数
SELECT Cno,COUNT(Sno)
FROM sc
GROUP BY Cno;
#利用GROUP BY以Cno进行分组

例6、查询至少有人选修课程号及其选课人数
SELECT Cno,COUNT(Sno)
FROM sc
GROUP BY Cno
HAVING COUNT(Sno)>=2;
#HAVING 对分组后的查询结果进行筛选，输出满足HAVING指定条件的组

WHERE 和 HAVING区别：作用对象不同
WHERE 作用于基本表或试图，从中选择满足条件的元组
HAVING 作用于分组，从中选择满足条件的分组

WHERE 和 HAVING区别：作用对象不同

WHERE 作用于基本表或试图，从中选择满足条件的元组
HAVING 作用于分组，从中选择满足条件的分组

在这里插入图片描述

（5）、对查询结果排序

通过 ORDER BY 子句实现，可以按一个或多个属性列排序；

升序：ASC；
降序：DESC；
缺省值为ASC
当排序列含空值时，
ASC：排序列为空值的元组最后显示。
DESC：排序列为空值的元组最先显示

例7、查询选修3号课程的学生学号及其成绩，查询结果按分数降序排列

SELECT Sno,Grade 
FROM sc
WHERE Cno = "3" 
ORDER BY Grade DESC;

例7、查询选修1号、2号或3号课程的学生学号、课程号和成绩，
查询结果按学号升序排列，学号相同再按成绩降序排列

SELECT Sno,Grade 
FROM sc
WHERE Cno IN ('1','2','3')
ORDER BY Sno, Grade DESC;
#Sno后面的升序ACS可以省略

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

（6）、单表查询综合题目：

查询3门以上选修课程成绩超过90分的学生学号及超过90分的课程数，查询结果按课程数降序排列。

查询3门以上选修课程成绩超过90分的学生学号及超过90分的课程数，查询结果按课程数降序排列。

SELECT Sno,COUNT(Cno)
FROM sc
WHERE Grade >= 90
GROUP BY Sno 
HAVING COUNT(Cno) >= 3
ORDER BY COUNT(Cno) DESC;

5、连接查询

查询时涉及了好几张表。

（1）、等值连接

可以利用 AND 相等的属性将几个的表连接起来

例1、查询选修了”数据库“课程的学生姓名。

SELECT Sname
FROM student,course,sc
WHERE course.Cname = "数据库" 
AND course.Cno = sc.Cno
And student.Sno = sc.Sno;

例2、查询选修2号课程且成绩在90分以上的所有学生的学号、姓名

SELECT student.Sno,Sname
FROM student,sc
WHERE sc.Cno= "2" 
AND Grade >=90
And student.Sno = sc.Sno;

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

（2）、自身连接：取别名

例3、查询每一门课的间接先修课（即先修课的先修课）

SELECT FIRST.Cno,SECOND.Cpno
FROM course FIRST,course SECOND
WHERE FIRST.Cpno = SECOND.Cno;

6、嵌套查询

查询块： SELECT-FROM-WHERE
嵌套查询：将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING 短语的条件中查询。

(1)、带有谓词IN的子查询

例1、查询选修了课程名为”信息系统“的学生学号和姓名

方法一：连接查询：数据量大，查询效率慢

例1、查询选修了课程名为”信息系统“的学生学号和姓名

SELECT student.Sno,Sname
FROM student,sc,course
where student.Sno = sc.Sno
AND course.Cno = sc.Cno
AND Cname = "信息系统";

方法二：嵌套查询：数据量小，查询效率快

分析过程：
在这里插入图片描述


SELECT Sno,Sname
FROM student
where Sno IN(
	SELECT Sno 
	FROM sc
	WHERE Cno IN (
		SELECT Cno
		FROM course
		WHERE Cname = '信息系统'
		)
	)

(2)、带有谓词ANY 或 OR的子查询

在这里插入图片描述

例2、查询其他系中比计算机科学系（CS）所有学生年龄都小的学生姓名及年龄 。

方法一：

SELECT Sname,Sage
FROM student
WHERE Sage < ALL
	(SELECT Sage
		FROM student
		WHERE Sdept = 'CS'
	)
	AND Sdept != 'CS';

方法二：“比所有都小” 相当于小于最小值MIN

SELECT Sname,Sage
FROM student
WHERE Sage < 
	(SELECT MIN(Sage)
		FROM student
		WHERE Sdept = 'CS'
	)
	AND Sdept != 'CS';

ANY和ALL与聚集函数（效率高）的对应关系

在这里插入图片描述

(3)、带有谓词exists的子查询

A、EXISTS : 存在量词

带有谓词 EXISTS 的子查询不返回任何数据，只产生逻辑真值”true“ 或逻辑假值 ”False“
若子查询结果非空，则返回真值；反之，假值。
由 EXISTS 引出的子查询，其目标列表表达式通常都用 *,因为带 EXISTS 的子查询只返回真值或假值，给出其列名无意义。

例1、查询所有选修了1号课程的学生姓名。

方法一：

SELECT Sname
FROM student
WHERE EXISTS
	(SELECT *
		FROM sc
		WHERE sc.Sno = student.Sno 
		AND Cno = '1'
	);

方法二：

SELECT Sname
FROM student,sc
WHERE sc.Sno = student.Sno 
		AND sc.Cno = '1';

B、用 EXISTS/NOT EXISTS 实现全称量词

SQL语言中没有全称量词可以把带有全称量词的谓词转换为带有存在量词的谓词：

在这里插入图片描述

例2、查询选修了全部可能的学生姓名。
即不存在课程没有被该名学生选修。

SELECT Sname
FROM student
WHERE NOT EXISTS
	(SELECT *
		FROM course
		WHERE NOT EXISTS
			(
				SELECT *
				FROM sc
				WHERE Sno = student.Sno AND Cno = course.Cno
			)
	);

C、用 EXISTS/NOT EXISTS 实现逻辑蕴涵

SQL语言中没有蕴含 -->
可以利用谓词演算将逻辑蕴涵谓词等价转换为：

在这里插入图片描述

例3、查询至少选修了学号为 ”201815122“ 选修的全部课程的学生学号
等价于：任意一门课程，如果”201815122“选了，则查询结果也选了
等价于：不存在课程，”201815122“选了该课程，并且查询结果没有选。


SELECT DISTINCT Sno
FROM sc SCX
#sc SCX 中间没有逗号，即 SCX是 sc的一个别名
WHERE NOT EXISTS
	(
	select *
	FROM sc SCY
	WHERE SCY.Sno = '201815122' 
	AND NOT EXISTS
		(SELECT *
			FROM sc SCZ
			WHERE SCZ.Sno = SCX.Sno AND SCZ.Cno = SCY.Cno
		)
	);

在这里插入图片描述

7、数据更新

（1）、插入数据：INSERT

A、插入单个元组

在这里插入图片描述

#INSERT

例1、在Student表插入一条新的学生记录

INSERT
INTO student
VALUES('201815128','陈冬','男',18,'IS');

例2、在SC表插入一条选修记录

INSERT
INTO sc(Sno,Cno)
VALUES('201815122','1')

B、插入子查询结果

在这里插入图片描述

例3、将选课学生各自的选课总分存入新表

CREATE TABLE STcredit
(Sno CHAR(9) PRIMARY KEY,
Tcredit SMALLINT,
FOREIGN KEY(Sno) REFERENCES sc(Sno));

INSERT
INTO stcredit
SELECT Cno,SUM(Ccredit)
FROM SC,course
WHERE sc.Cno = course.Cno
GROUP BY Sno;

建立的新表 STCredit 和查询结果：

在这里插入图片描述

（2）、修改数据：UPDATE

在这里插入图片描述

例4、将学号“201815121”的学生年龄改为22岁
UPDATE student
SET Sage = 22
WHERE Sno = "201815121";

例5、将信息系（IS）所有学生的年龄增加1岁
UPDATE student
SET Sage = Sage+1
WHERE Sdept = 'IS';

例6、将计算机科学系（CS）全体学生的成绩置0
UPDATE sc
SET Grade = 0
WHERE Sno IN 
	(SELECT Sno
		FROM student
		WHERE Sdept = 'CS'
	);

（3）、删除数据：DELETE

例7、删除2号课程的所有选课记录

DELETE 
FROM sc
WHERE Cno = '2';


例8、删除计算机科学系（CS）全体学生的选修记录

DELETE 
FROM sc
WHERE Sno IN
	(	SELECT Sno
		FROM student
		WHERE Sdept = 'CS'
	);

（4）、数据更新的完整性检查

在这里插入图片描述

8、视图

（1）、视图的特点

虚表，是从一个或几个基本表（或视图）导出的表
只存放视图的定义，不存放视图对应的数据
基表中的数据发送变化，从视图中查询出的数据也随之改变

（2）、视图的操作

在这里插入图片描述

A、定义视图——create 创建

在这里插入图片描述

例1、创新信息系（IS）学生的视图

CREATE VIEW IS_Student
AS
	SELECT Sno,Sname,Sage
	FROM student
	WHERE Sdept = 'IS';

创建的视图和查询的结果是一样的：
在这里插入图片描述

例1、创新信息系（IS）学生的视图

CREATE VIEW IS_S1(Sno,Sname,Grade)
AS
#子查询有一个同属的属性列 Sno (这里用student.Sn引用了)，
#所以创建视图时，IS_S1()括号中需要指定属性名

	SELECT student.Sno,Sname,Grade
	FROM student,sc
	WHERE student.Sno = sc.Sno 
		AND Cno = '1' AND Sdept = 'IS;

在这里插入图片描述

例3、建立一个学生的学号及其平均分的视图

CREATE VIEW S_G(Sno, Gavg)
AS
#子查询聚集函数AVG(Grade)，
#所以创建视图时，S_G()括号中需要指定属性名

	SELECT Sno,AVG(Grade)
	FROM sc
	GROUP BY Sno;

在这里插入图片描述

B、定义视图——删除DROP

例4、删除信息系（IS）选修了1号课程学生成绩的视图IS_S1

DROP VIEW is_s1;

C、查询视图——SELECT

例5、在信息系学生的视图IS_Student中找出年龄小于20岁的学生。

#方法一：在视图中查找
SELECT Sno,Sage
FROM is_student
WHERE Sage < 20;

#方法二：在原表中查找
SELECT Sno,Sage
FROM student
WHERE Sage < 20 AND Sdept = 'IS';

D、更新视图——INSERT

如下，对视图 IS_Student 更新INSERT时，原表student也发生了更新。

例6、向信息系学生的视图IS_Student新增一条学生记录
（学号：21815129，姓名：赵新，年龄：20岁）

INSERT
INTO is_student
VALUES('201815129','赵新',20);

E、更新视图——UPDATE

例6、向信息系学生的视图IS_Student中学号为“201815122”的学生姓名改为“刘辰”。

UPDATE is_student
SET Sname = '刘辰'
WHERE Sno = '201815122';

F、更新视图——DELETE

例6、删除信息系学生的视图IS_Student中学号为“201815122”的学生记录

DELETE
FROM is_student
WHERE Sno = '201815129';

（3）、视图的作用:

（1）、能够简化用户的操作
（2）、使用户能以多种角度看待同一数据
（3）、对重构数据库提供了一定程度的逻辑独立性

xor0ne_10_01

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录

SQL实操小记

文章目录

0、做好准备

1、建数据库

2、建表：建表头

3、录入数据

4、单表查询

（1）、查询表中的若干列

（2）、查询满足条件的元组

A、比较大小

B、多表查询

C、确定范围

D、确定集合

E、字符匹配（模糊匹配）

F、控制查询

（3）、 使用聚集函数的查询

（4）、对查询结果分组

WHERE 和 HAVING区别：作用对象不同

（5）、对查询结果排序

（6）、 单表查询综合题目：

5、连接查询

（1）、等值连接

（2）、自身连接：取别名

6、嵌套查询

(1)、带有谓词IN的子查询

(2)、带有谓词ANY 或 OR的子查询

ANY和ALL与聚集函数（效率高）的对应关系

(3)、带有谓词exists的子查询

A、EXISTS : 存在量词

B、用 EXISTS/NOT EXISTS 实现全称量词

C、用 EXISTS/NOT EXISTS 实现逻辑蕴涵

7、数据更新

（1）、插入数据：INSERT

A、插入单个元组

B、插入子查询结果

（2）、修改数据：UPDATE

（3）、删除数据：DELETE

（4）、数据更新的完整性检查

8、视图

（1）、视图的特点

（2）、视图的操作

A、定义视图——create 创建

B、定义视图——删除DROP

C、查询视图——SELECT

D、更新视图——INSERT

E、更新视图——UPDATE

F、更新视图——DELETE

（3）、视图的作用:

（3）、使用聚集函数的查询

（6）、单表查询综合题目：