第二章-SQL

目录

• 一、概述
  • SQL的特点
• 二、数据定义
  • 模式定义与删除
  • 基本表的定义、删除与修改
  • 索引的建立和删除
• 三、数据查询
  • SQL查询的基本形式
  • 单表查询
  • 条件查询
  • 聚合函数
  • GROUP BY子句
  • 连接查询
  • 嵌套查询
  • 集合查询
  • 基于派生表的查询
• 四、数据更i性能
  • 插入数据
  • 修改数据
  • 删除数据
• 五、空值的处理
  • 空值的判断
• 六、视图
  • 定义视图
  • 删除视图
  • 查询视图
  • 视图的作用

一、概述

SQL的特点

1. 综合统一：集模式数据定义语言(DDL)、外模式数据定义语言(SDDL)、数据存储描述语言(DSDL)、数据操纵语言(DML)于一体。

2. 高度非过程化：只需提出“做什么”，而不需给出“怎么做”。

3. 面向集合的操作方式：操作的对象与结果均是集合。

4. 以同一种语法提供多种使用方式：直接使用(SQL)以及嵌入式的(SQL)。

5. 语言简洁，简单易学。

下列SQL操作使用mysql数据库来执行。

二、数据定义

模式定义与删除

# 建立模式（数据库）
create database learn_sql;
# 删除模式（数据库） 
drop database learn_sql;

基本表的定义、删除与修改

创建基本表

# 创建学生表
create table Student(sno VARCHAR(20) 
                     PRIMARY KEY, 
                     Sname VARCHAR(20) UNIQUE, S
                     sex CHAR(2), Sage TinyINT, 
                     Sdept CHAR(20));
# 创建课程表                     
create table course(cno char(4) primary key, 
                    cname char(40) NOT null, 
                    cpno CHAR(4), 
                    Ccredit smallint, 
                    foreign key (cpno) references course(cno));
                    
# 创建成绩表
create table sc (sno char(9), 
                 cno char(4), 
                 grade tinyint, 
                 primary key(sno, cno), 
                 foreign key(sno) references student(sno), 
                 foreign key(cno) references course(cno));

每一个基本表都属于某一个模式，一个模式包含多个基本表。

修改基本表

# 向Student表中增加“入学时间”列
alter table Student add S_entrance date;

# 增加课程名必须取唯一值的约束条件
alter table course add unique(cname);

删除基本表

# 删除基本表、

# 级联删除
DROP TABLE student CASCADE
# 条件删除
DROP TABLE student CASCADE

索引的建立和删除

# 建立索引
create UNIQUE INDEX Stuno ON Student(sno);
create UNIQUE INDEX coucno ON Course(cno);
create UNIQUE INDEX Scno ON SC(Sno ASC, Cno DESC);

# 删除索引
DROP INDEX Coucno ON Course;

三、数据查询

SQL查询的基本形式

SELECT <列名> 
FROM <表名>
WHERE <条件>
GROUP BY <列名>
HAVING <条件>
ORDER BY <列名> <ASC|DESC>

单表查询

# DISTINCT用于消除重复行
SELECT DISTINCT Sno
FROM SC

# ALL用于保留重复行
SELECT DISTINCT Sno
FROM SC

条件查询

SELECT Sname 
FROM Student
WHERE Sdept='SC'

• BEETWEEN value1 AND value2范围查询语句。

• IS NULL, NOT NULL判空条件

聚合函数

• COUNT( )：统计数据的行数
• MIN( )：找出column中最小的一行
• MAX( )：找出column中最大的一行
• AVG( )：对column所有的行取平均值
• SUM( )：对column所有的行求和

GROUP BY子句

GROUP BY子句用根据指定属性分组，分组后聚合函数将作用于每一个组。

SELECT Cno, COUNT(Sno)
FROM SC
GROUP BY Cno

连接查询

SELECT S.*, SC.*
FROM Student AS s, SC
WHERE s.Sno = SC.Sno

嵌套查询

SELECT Sname
FROM Student
WHERE Sno IN
		(SELECT Sno
         FROM Student
         WHERE Cno = '2')

在嵌套查询中，外层查询称父查询，内层查询称子查询；

可使用ANY或ALL谓词来比较子查询返回的值：参考博客

SELECT Sname, Sage
FROM Student 
WHERE Sage < ANY(SELECT Sage
                 FROM Student
				 WHERE Sdept = 'CS')

还可使用EXISTS谓语来判断子查询是否返回数据：[参考博客

集合查询

集合查询操作：参考博客

• UNION
• INTERSECT
• EXCEPT

基于派生表的查询

在FROM语子句中嵌入一个查询：

SELECT Sno, Cno
FROM SC, (SELECT Sno, Avg(Grade) 
		  FROM SC
          GROUP BY Sno
         )AS Avg_sc(avg_sno, avg_grade)

四、数据更i性能

插入数据

# 两种插入方式
INSERT INTO Sutdent(Sno, Sname, Ssex, Sage) 
		VALUES('201215128', '张三', 'IS', '18')
		
INSERT INTO Sutdent	VALUES('201215128', '张三', 'IS', '18')

# 插入子查询的结果
ISNERT INTO S1 
		SELECT * 
		FROM S2;

修改数据

# 修改满足条件的元组的值
UPDATE Student
SET Sage = 22
WHERE Sno = '14182401708'

# 修改表中所有元组的值
UPDATE Student
SET Sage = Sage + 1 

删除数据

# 删除满足条件的元组的值
DELETE 
FROM Student
WHERE Sno = '14182401708'

# 删除表中所有元组的值
DELETE 
FROM Student

五、空值的处理

空值（NULL）即不存在的、无意义的值。

空值的判断

空值可以使用IS NULL 或IS NOT NULL来表示，

SELECT *
FROM Student
WHERE Sname IS NULL OR Ssex IS NULL

六、视图

视图是从一个或几个基本表（或视图）导出的表。它与基本表不同，是一个虚表。数据库中只存放视图的定义，而不存放视图对应的数据。

定义视图

# 创建视图
CREATE VIEW IS_Student
AS 
SELECT Sno, Sname, Sage
FROM Student
WHERE Sdept = 'IS'
WITH CHECK OPTION

CREATE VIEW只是把视图的定义存入数据字典，并不执行其中的SELECT语句。

删除视图

DROP VIEW IS_Student

查询视图

在查询视图时，首先进行有效性检查，例如涉及的表、视图是否存在。若存在，则从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后执行修正了的查询。这一转换称为视图消解。

更新视图与查询视图是同样的原理，将对视图的更新转换为对

视图的作用

1. 视图能够简化用户的操作。
2. 视图使用户能以多种角度看待同一数据。
3. 视图对重构数据库提供了一定程度的逻辑独立性。
4. 视图能够对机密数据提供安全保护。
5. 适当利用视图可以更清晰地表达查询。