关系数据库标准语言SQL

2024-02-23 18:46:22

3、关系数据库标准语言SQL

`SQL` 基本语法

数据查询（DQL）：SELECT
数据定义（DDL）：CREATE,DROP,ALTER
数据操纵（DML）：INSERT,UPDATE,DELETE
数据控制（DCL）：GRANT,REVOKE

3.1数据查询

①单表查询

3.1.1 查询列：

查询指定列：

# 例：查询全体学生的学号与姓名
SELECT Sno,Sname
FROM Student;

查询所有列：（将<目标列表达式>指定为 *）

# 例：查询全体学生的详细记录。
SELECT *
FROM Student as Stu;  	# as 取别名，可省略

查询结果计算的值：

# 例：查全体学生的姓名及其出生年份
SELECT Sname,2012-Sage /*假定当年的年份为 2012 年，减去年龄即得出生年份*/
FROM Student;

3.1.2 查询行（元组）

取消取值重复的行：（指定 DISTINCT 关键词，去掉表中重复的行）

#  例：查询选修了课程的学生号码
SELECT DISTINCT Sno     #（使用 DISTINCT 关键词取消了重复学号的行）
FROM SC;

查询满足条件的行：（WHERE 语句）

 # 例：查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。*（确定集合）*
 SELECT Sname,Ssex
 FROM Student
 WHERE Sdept IN ( 'IS','MA','CS' );

# 例：查询学号为 200215121 的学生的详细情况。*（字符匹配）*
SELECT *  
 FROM Student 
 WHERE Sno LIKE '200215121'; 	# % 可以代替多个字符, 但是_只能替换⼀个字符

# 例：某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。*（空值）*
SELECT Sno,Cno
 FROM SC
 WHERE Grade IS NULL    /*不能用'='代替 IS*/

3.1.3 GROUP BY子句

# GROUP BY分组 ,如果要进⾏筛选,请使⽤Having

3.1.4 ORDER BY子句

 #例：查询选修了 3 号课程的学生的学号及其成绩，查询结果按分数降序排列。
 SELECT Sno，Grade
 FROM SC
 WHERE Cno= ' 3 '
 ORDER BY Grade DESC	# 可以按一个或多个属性列排序。升序：ASC；降序：DESC；缺省值为升序

3.1.4 聚集函数

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列之和

# 例：计算 1 号课程的学生平均成绩。
SELECT AVG(Grade)
FROM SC
WHERE Cno= ' 1 ';

②多表查询.

3.1.5等值查询

# 例：查询每个学生及其选修课程的情况
SELECT Student.*,SC.*
FROM Student,SC
WHERE Student.Sno = SC.Sno;

3.1.6 嵌套查询

IN 谓词的子查询：

# 例：查询与“刘晨”在同一个系学习的学生
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN
	(SELECT Sdept
	FROM Student
	WHERE Sname='刘晨' );

比较运算符的子查询：（当内查询的结果是一个值时，可以用=代替 IN）

#例：查询与“刘晨”在同一个系学习的学生
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept =
 	(SELECT Sdept
 	FROM Student
	WHERE Sname='刘晨');

有 ANY 或 ALL 谓词的子查询

#例：查询其他系中比计算机科学某一学生年龄小的学生姓名和年龄
SELECT Sname,Sage
FROM Student
WHERE Sage < ANY (SELECT Sage
 			FROM Student
 			WHERE Sdept= ' CS ')
 	 AND Sdept <> 'CS ' ; /*这句是父查询块中的条件 */

有 EXISTS 谓词的子查询：

相当于存在量词∃，有 EXISTS 谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。

# 例：查询所有选修了 1 号课程的学生姓名。
SELECT Sname
FROM Student
WHERE EXISTS	# exists 如果后⾯的⼦查询有值,返回1 否则为0;not exists 相反
	(SELECT *
 	FROM SC
 	WHERE Sno=Student.Sno AND Cno= ' 1 ');

3.1.7集合查询

集合操作的种类：并操作 UNION，交操作 INTERSECT，差操作 EXCEPT

#例：查询计算机科学系的学生及年龄不大于 19 岁的学生。（并）
SELECT *
 FROM Student
 WHERE Sdept= 'CS'
 UNION	# 并操作
 SELECT *
 FROM Student
 WHERE Sage<=19;

3.2 数据定义

操作方式	创建	删除	修改
模式	CREATE SCHEMA	DROP SCHEMA
表	CREATE TABLE	DROP TABLE	ALTER TABLE
视图	CREATE VIEW	DROP VIEW
索引	CREATE INDEX	DROP INDEX	ALTER INDEX

3.2.1模式

CREATE SCHEMA <模式名> AUTHORIZATION <用户名>;
DROP SCHEMA<模式名>< CASCADE| RESTRICT>;

删除模式, 其中 CASCADE 和RESTRICT 必须⼆选⼀,
CASCADE是(级联) 删除模式的同时也把该模式的所有数据库对象删除.
RESTRICT(限制) 如果该模式下有下属对象,⽐如表视图就拒绝这个删除语句的执⾏

3.2.2 表

约束

常用完整性约束	值
主码约束	PRIMARY KEY
用户定义	CHECK
默认值	DEFAULT
唯一性约束	UNIQUE
非空值约束	NOT NULL
参照完整性约束	FOREIGN KEY

1定义

# 创建表 
create table 表名( 字段名 类型 字段约束(列级完整性约束条件), 
                  字段名 类型 字段约束, 
                  字段名 类型 字段约束,
                 <表级完整性约束条件>)
# 例： 
CREATE TABLE  SC
       	(Sno  CHAR(9),
       	Cno  CHAR(4),  
       	Grade  SMALLINT check(Grade>=0 and Grade<=100),,
       	PRIMARY KEY (Sno,Cno),     -- 主码由两个属性构成，必须作为表级完整性进行定义
       	FOREIGN KEY (Sno) REFERENCES Student(Sno), -- 表级完整性约束条件，Sno是外码，被参照表是Student 
       	FOREIGN KEY (Cno) REFERENCES Course(Cno) -- 表级完整性约束条件， Cno是外码，被参照表是Course
		); 
		
CREATE TABLE Ss
(
Sno INT PRIMARY KEY AUTO_INCREMENT,	#  AUTO_INCREMENT实现自动编号
Sname CHAR(20) UNIQUE
);

2 删除

DROP TABLE<表名> [RESTRICT| CASCADE];
# 例： 
DROP TABLE Student CASCADE

CASCADE呢是如果表有外键,视图触发器的话,也会强⾏删除, RESTRICT恰恰相反哦

3 修改

ALTER TABLE<表名>
[ADD [COLUMN] <新列名><数据类型>[完整性约束] #增
[MODIFY [COLUMN] <列名> <数据类型> [完整性约束] #改
[DROP [COLUMN] <列名>] #删
# 例： 
ALTER TABLE Student ADD COLUMN Etime date;
ALTER TABLE Student MODIFY COLUMN Etime int default 0;  # default将属性的默认值改为0
ALTER TABLE Student DROP COLUMN Etime;

4 修改表名

ALTER TABLE <旧表名> RENAME [TO] <新表名>；

3.2.3 视图

1 建立

视图(VIEW)也被称作虚表，即虚拟的表，是⼀组数据的逻辑表示,其本质是对应于⼀条SELECT语句
结果集被赋予⼀个名字，即视图名字。
视图本身并不包含任何数据，它只包含映射到基表的⼀个查询语句，当基表数据发⽣变化，视图数据也会发生变化

CREATE VIEW<视图名>[(<列名>[,<列名>]…)]
AS<子查询>
[WITH CHECK OPTION]    # 加了WITH CHECK OPTION 可以防⽌⽤户对 不属于试图范围的操作 进⾏拒绝

2 删除

DROP VIEW <视图名> [CASCADE]

3 查询

  select * from view <视图名>

4 更新

视图是一个虚表，即视图所对应的数据不进行实际存储，数据库中只存储视图的定义，在对视图的数据进行操作时，系统根据视图的定义去操作与视图相关联的基本表。

UPDATE IS_Student #<视图名>
SET Sname='刘辰'
WHERE Sno='201215122';
# 转换后的更新语句为
UPDATE Student #<表名>
SET Sname='刘辰'
WHERE Sno='201215122' AND Sdept='IS';

3.2.4索引

创建索引,可以在查询的过程中,提高系统的性能，索引可以建⽴在⼀列或者多列上