关系数据库标准语言——SQL

一、SQL概述

SQL是结构化查询语言（Structured Query  Language）的缩写，其功能包括数据定义、数据查询、数据操作和数据控制，SQL简介方便、功能齐全，是目前应用最广泛的关系数据库语言。

二、SQL语句的分类

SQL语句的分类：

DDL（Data Definition Language）：数据定义语言，主要操作表的结构，而不是表中的数据，例如带有create、drop、alter关键字的都是DDL。

DML（Data Manipulation Language）：数据操作语言，对表中的数据进行增删改的都是DML（

insert 增、delete 删、updata 改 ）

DQL（Data Query Language）：数据查询语言，凡是带有select关键字的都是查询语句

DCL（Data Control Language）：数据控制语言，例如:授权grant、销权限revoke....

TCL：事物控制语言，包括事务提交:commit、事务回滚: rollback

一、SQL的基本概念和特点

SQL的基本概念

基本表：一个关系对应一个基本表

视图：从基本表导出的表（虚表）（数据库中只存放视图的定义，而不存放视图对应的数据，视图的数据存放在导出该视图的基本表中）

SQL支持数据库的三级模式结构，其中外模式对应视图和部分基本表，模式对应基本表，内模式对应存储文件

SQL的主要特点

1.类似与英语的自然语言，语法简单，简洁易用

2.是一种一体化的语言 

3.是一种非过程化的语言 

4.是一种面向集合的语言，每个命令的操作对象是一个或多个关系，结果也是一个关系

6.SQL具有数据定义，数据查询，数据操纵，数据控制四种功能

三、数据库的创建和使用

数据库的结构：一个数据库至少包含一个数据文件和一个事务日志文件

①数据文件：存放数据库数据和数据库对象的文件。（一个数据库可以有一个或多个数据文件）（当有多个数据文件时有一个被定义为主数据文件，后缀为.mdf，用来存放数据库的启动信息和部分或全部数据）//其他数据文件为次数据文件，后缀为.ndf，存储主数据文件未存储的数据

②事务日志文件：保存用来恢复数据库的日志信息，后缀名.ldf

③文件组：是多个数据文件的集合（一个数据文件只能存在一个文件组中，一个文件组也只能被一个数据库使用）；日志文件不属于任何文件组

1、创建用户数据库CREATE DATABASE

create database 数据库名
[ON                                        //ON的小括号里设置数据文件的参数
[FILEGROUP 文件组名称]
(                                 
    NAME=数据文件的逻辑名称,
    FILENAME='路径+数据文件名',
    SIZE=数据文件初始大小,
    MAXSIZE=数据文件最大容量,
    FILEGROWTH=数据文件自动增长容量,
)]

[LOG ON                                    //LOG ON的小括号里设置日志文件的参数
(
    NAME=日志文件逻辑名称,
    FILENAME='路径+日志文件名',
    SIZE=日志文件初始大小,
    MAXSIZE=日志文件最大容量,
    FILEGROWTH=日志文件自动增长容量,
)]
[COLLATE 数据库校验方式名]
[FOR ATTACH]                        //表示将已经存在的数据库文件附加到新数据库中
    

（1）[ ]表示可选。

（2）用（）括起来的语句，除了最后一行命令外，其余命令都要用逗号分隔。

（3）如果只用create database 数据库名创建数据库，DBMS会按照默认的属性创建数据库

【例3-1】用SQL命令创建一个教学数据库Teach，数据文件的逻辑名称为Teach_Data，数据文件存放在E盘根目录下，文件名为TeachData.mdf，数据文件的初始存储空间大小为10MB，最大存储空间为500MB，存储空间自动增长量为10MB；日志文件的逻辑名称为Teach_Log，日志文件物理地存放在E盘根目录下，文件名为TeachData.ldf，初始存储空间大小为5MB，最大存储空间为500MB，存储空间自动增长量为5MB。

CREATE DATABASE Teach
ON
(
    NAME=Teach_Data,
    FILENAME='D:\TeachData.mdf',
    SIZE=10,
    MAXSIZE=500,
    FILEGROWTH=10
)
LOG ON
(
    NAME=Teach_Log,
    FILENAME='D:\TeachData.ldf',
    SIZE=5,
    MAXSIZE=500,
    FILEGROWTH=5
)

2、修改用户数据库（这里不做详细说明）

可以使用ALTER DATABASE 数据库名来修改数据库，只有数据库管理员（DBA）或具有CREATE DATABASE权限的人才能执行此命令

3、删除用户数据库DROP DATABASE

只有数据库管理员（DBA）和具有此权限的人才能执行此命令，一次可以删除一个或多个数据库

DROP DATABASE 数据库名 [,...n]

例子：删除Teach数据库

DROP DATABASE Teach

四、数据表的创建和使用

将数据表的创建和使用之前，先来介绍一下SQL Server中的数据类型

4.1 数据类型

关系表的每一个字段都是同一数据类型，都来自同一个域

 

上表的数据类型说明：

 （1）整数型：按照取值范围从大到小，包括bigint、int、smallint、tinyint、bit。在实际应用中，可以根据属性的具体取值范围选择适合的整数型。例如，第1章图1-21中的学生关系表（S）中的属性“年龄（Age）”的数据类型可以设置为int。

 （2）数值型：包括精确数值型numeric、decimal和近似数值型float、real。以numeric为例，格式为numeric（p，s），其中p表示数据长度，s表示小数位数。

（3）货币型：按照取值范围从大到小，包括money和smallmoney，它们可以精确到所代表的货币单位的万分之一，也就是小数点后面4位。通常情况下，货币型可以转换为精确数值型。

（4）日期型：按照时间范围从大到小，包括datetime和smalldatetime，可以精确到秒，smalldatetime比datetime多占用4个字节。此外，还有一个常用的日期型是date，这种数据类型只显示日期，不显示时间。

（5）字符型：包括char、varchar、nchar、nvarchar、text、ntext。

其中：

char、varchar存放非Unicode字符（即ASCII字符），一个字符占1个字节，char是定长的，varchar是非定长的。如学生关系表（S）中的属性“学号（SNo）”可以设置为char（6），表示学号最多可以包含6个非Unicode字符，即使小于6个，在内存中也要分配6个字节的空间；如果设置为varchar（6），则学号实际包含多少非Unicode字符，在内存中就分配多少字节，例如，学号“S1”包含2个字符，内存中删除尾端空白的4字节，分配2字节的空间。

nchar、nvarchar存放Unicode字符，一个字符占2个字节，nchar是定长的，nvarchar是非定长的。nchar、nvarchar的用法与char、varchar相同，只是占用内存空间不同。

注意：当字符串中包含非ASCII字符时，最好不要使用char、varchar。当某个字符型属性需要描述的字符数比较多时，可以将其设置为text、ntext。其中，text存放非Unicode字符，定长，最大可存储2GB；ntext存放Unicode字符，非定长，最大可存储2GB。

（6）二进制数据型：包括binary、varbinary、image。其中，binary是定长的二进制数据型，varbinary是非定长的二进制数据型，两者最多可以表示8000个字节。任何类型的数据都可存储在这种类型的字段中，不需数据转换。image类型可以存储图片本身，这时需要事先将图片转换成二进制流的形式；也可以存储图片路径。此外，由于图片路径是字符串的形式，也可以使用字符型。

（7）特殊类型：包括timestamp、uniqueidentifier、sql_variant、table。timestamp数据类型提供数据库范围内的唯一值。此类型相当于binary（8）或varbinary（8），但当它所定义的列更新或添加数据行时，此列的值会被自动更新，一个计数值将自动地添加到此timestamp数据列中。每个数据库表中只能有一个 timestamp 数据列。如果建立一个名为“timestamp”的列，则该列的类型将被自动设为timestamp 数据类型。uniqueidentifier 数据类型称为全球唯一标识符（Globally Unique Identifier， GUID），可用NEWID（ ）函数产生。sql_variant数据类型可以存储除文本、图形数据和timestamp类型数据外的其他任何合法的SQL Server数据，此数据类型大大方便了SQL Server的开发工作。table数据类型用于存储对表或视图处理后的结果集，这一类型使得变量可以存储一个表，从而使函数或过程返回查询结果更加方便、快捷。

4.2 创建数据表CREATE TABLE

对于数据表的说明：

数据类型：表示字段可存储的数据类型

字段的长度：字段能容纳的最大数据量。

• 对于字符型与Unicode字符类型，长度代表字段能容纳的字符数目。
• 整数型的字段长度是固定的，用户不需要输入长度，系统根据相应整数类型的不同自动给出字段长度。
• 对binary、varbinary和image数据类型而言，长度代表字段能容纳的字节数。
•  对数值型类型而言，长度则代表字段使用多少个字节来存放数字，由精度决定，精度越高，字段的长度就越大。精度是指数据中数字的位数，包括小数点左侧的整数部分和小数点右侧的小数部分。例如，数字12345.678，其精度为8，小数位数为3。只有数值类型才有必要指定精度和小数位数。

“允许空”：当某个字段的“允许空”列打上勾时，表示该字段的值允许为 NULL值。这样，在向数据表中输入数据时，如果没有给该字段输入数据，系统将自动取NULL值，否则，必须给该字段提供数据。

“默认值”：表示该字段的默认值（即DEFAULT值）。如果提供了默认值，向数据表中输入数据时，如果没有给该字段输入数据，系统自动将默认值写入该字段。

创建数据表就是定义数据表每一列的过程

create table 表名
(    列名 数据类型 [default] [列约束],  #在小括号中写每一列的定义
     列名 数据类型 [default] [列约束],
     ...
)

（1）表名最多可有128个字符，不能重名

（2）列定义的语法：列名 数据类型 [default] [列约束]

（3）如果某字段设置了默认值，则该字段未输入数据时，将自动填充默认值

（4）在SQL中，数据类型后跟一个小括号来表示数据类型及采用的长度、精度和小数位数，N代表长度，P代表精度，S代表小数位数。

binary(N)     -     binary(10)

char(N)       -      char(20)

numeric(P,[S])       -       numeric(8,3)

note：有些数据类型的精度与小数位数是固定的，所以不需设置精度与小数位数。例如，如果某字段采用INT数据类型，其长度固定是4，精度固定是10，小数位数则固定是0，这表示该字段能存放10位没有小数点的整数，存储大小则是4个字节。

【例3-6】用SQL命令建立一个学生表S。

CREATE TABLE S
(   SNo VARCHAR（6）,
    SN NVARCHAR（10）,
    Sex NCHAR（1） DEFAULT ’男’,
    Age INT,
    Dept NVARCHAR(20))

4.3 定义数据表的约束

数据的完整性指保护数据库中数据的正确性、有效性和相容性，防止错误的数据进入数据库，造成无效操作。

SQL Server提供的数据完整性机制主要有：

• 约束（本节只介绍约束）
• 默认
• 规则
• 触发器
• 存储过程

在SQL Server中，数据表的约束分为列约束和表约束：

• 列约束：为单个字段定义约束，写在列定义中
• 表约束：为多个字段定义约束，在列定义的最后用逗号分隔定义表约束，需要指出要约束的字段

完整性约束的基本语法格式：

[CONSTRAINT 约束名] 约束类型

说明：

• constraint  约束名：当不指定约束名时，系统会自动给定一个名称。
• 约束类型：指定约束的类型

4.3.1 约束类型

在此介绍SQL Server中的五种约束类型：

NOT NULL约束

NULL表示不确定或没有数据，所有的类型的值都可以是NULL，包括INT、FLOAT等数据类型

作用：规定某个字段不能为空 

[CONSTRAINT 约束名] NOT NULL

特点：

• 只能用于定义列约束，也就是只能给某个列单独限定非空，不能组合非空

• 一个表可以有很多列分别定义非空约束

• 空字符串''不等于NULL，0也不等于NULL

【例3-7】建立一个S表，对SNo字段定义NOT NULL约束。

CREATE TABLE S(
    SNo VARCHAR(6) CONSTRAINT S_CONS NOT NULL,
    SN NVARCHAR(10),
    Sex NCHAR(1),
    Age INT,
    Dept NVARCHAR(20))

UNIQUE约束(唯一约束)

作用：用来限定基本表在某列或多个列的组合上取值必须唯一

特点：

• 系统会给唯一约束的列上自动创建唯一索引，从而保证其唯一性。
• 唯一键（定义的unique的字段）可以为空，但最多只允许有一个NULL值
• 一个表中可以定义多个UNIQUE约束
• UNIQUE约束可以列约束，也可以表约束

UNIQUE定义列约束的格式：

[CONSTRAINT 约束名] UNIQUE

【例3-8】建立一个S表，定义SN为唯一键。

CREATE TABLE S(SNo VARCHAR(6),
    SN NVARCHAR(10) CONSTRAINT SN_UNIQ UNIQUE,
    Sex NCHAR(1),
    Age INT,
    Dept NVARCHAR(20))

UNIQUE定义表约束的语法： 

[CONSTRAINT 约束名] UNIQUE(列名[,列名..])

【例3-9】建立一个S表，定义SN+Sex为唯一键，此约束为表约束。

CREATE TABLE S
(   SNo VARCHAR(6),
    SN NVARCHAR(10)UNIQUE,Sex NCHAR(1),
    Age INT,
    Dept NVARCHAR(20),
    CONSTRAINT S_UNIQ UNIQUE(SN, Sex))

PRIMARY KEY约束（主键约束）

定义基本表的主键，用于唯一标识一个元组。其值不能为null，也不能重复，可以保证实体的完整性

特点：

• 一个表最多只能有一个主键约束
• 可用于列约束，表约束

• 如果是多列组合的主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。

• 不能为同一列或同一组列既定义primary key约束又定义unique约束
• 主键约束相当于唯一约束+非空约束的组合

• 当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据主键查询的，就根据主键查询，效率更高）。如果删除主键约束了，主键约束对应的索引就自动删除了。

• 需要注意的一点是，不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性。

定义列约束的语法格式：

[CONSTRAINT 约束名] PRIMARY KEY

定义表约束的格式：

• 将某些列的组合定义为主键

[CONSTRAINT 约束名] PRIMARY KEY(列名[,列名...])

案例：

FOREIGN KEY约束（外键约束）

 作用：指定某列或一组列为外键。

• 包含外键的表为从表
• 包含外键所引用的主键或唯一键的表为主表，（用于列约束和表约束都可以）
• 从表中外部键的取值是主表中某一个主键的值或取NULL值，以此可以保证两表的参照完整性

特点：

• 可用于列约束，也可用于表约束
• 从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。

列约束，表约束都用这个语法格式，因为都要指定参考主表的主键列：

[CONSTRAINT 约束名] FOREIGN KEY REFERENCE 主表名(列名[,列名..])

案例：

CHECK约束

作用：检查(限定)字段取值所允许的范围

特点：

• 每个字段只能定义一个check约束，不会然混乱
• 一个表中可以有多个check约束
• 在多个字段上定义的check约束必须为表约束
• 可列约束也可表约束

语法格式：

[CONTRAINT 约束名] CHECK(约束条件)

【例3-13】建立一个SC表，定义Score的取值范围为0～100之间。

CREATE TABLE SC(
    SNo VARCHAR(6),
    CNo VARCHAR(6),
    Score NUMERIC(4,1) CONSTRAINT Score_Chk CHECK(Score>=0 AND Score <=100))

4.4 修改数据表

ALTER TABLE 表名命令修改表结构，如增加新的列和完整性约束、修改原有的列定义和完整性约束

(1)增加新列和完整性约束

注意：使用此方式增加的新列自动填充NULL值，所以不能为增加的新列指定NOT NULL约束。

语法格式：

ALTER TABLE 表名
ADD
每一列的定义|完整性约束     #和CREATE TABLE语句中的定义方式相同

【例3-15】在S表中增加一个班号列和住址列。

ALTER TABLE S
ADD 
Class_No VARCHAR(6),
Address NVARCHAR(20)

【例3-16】在SC表中增加完整性约束定义，使Score在0～100之间。

ALTER TABLE SC
ADD
CONSTRAINT Score_Chk CHECK(Score BETWEEN 0 AND 100)

(2)ALTER方式

功能：修改某些列

注意：

• 使用此方式不能改变列名
• 不能将含有空值的列的定义修改为NOT NULL约束
• 列中已有数据，不能减少该列的宽度，也不能改变其数据类型
• 只能修改为NULL/NOT NULL约束，其他类型的约束修改前必须先将约束删除，然后再重新添加修改过的约束定义

语法：

ALTER TABLE 表名
ALTER COLUMN 列名 数据类型 [NULL|NOT NULL]


-- 把S表中的SN列加宽到12个字符。
ALTER TABLE S
ALTER COLUMN SN NVARCHAR(12)

(3)DROP方式

删除列和完整性约束

删除完整性约束：

ALTER TABLE 表名
DROP CONSTRAINT 约束名


-- 删除S表中的主键。
ALTER TABLE S
DROP CONSTRAINT S_Prim

删除列：

ALTER TABLE 表名 DROP 列名;


-- 将stu表中的addr字段 删除
alter table stu drop addr;

修改表名：

ALTER TABLE 表名 RENAME TO 新的表名;

-- 将表名student修改为stu
alter table student rename to stu;

修改某列的数据类型：

ALTER TABLE 表名 MODIFY 列名 新数据类型;

-- 将stu表中的address字段的类型改为 char(50)
alter table stu modify address char(50);

修改列名和数据类型：

ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;

-- 将stu表中的address字段名改为 addr，类型改为varchar(50)
alter table stu change address addr varchar(50);

4.5 删除基本表DROP TABLE 表名

删除基本表后，表中的数据和此表上的索引都会被删除，但建立在该表上的视图不会删除，但已经无法使用，如果重新恢复此表，这些视图又可重新使用。

删除表：

DROP TABLE 表名

 删除表的时候判断表是否存在：

DROP TABLE IF EXISTS 表名;

五、单关系(表)的数据查询

5.1 单表的数据查询结构

SQL的SELECT语句，通过查询操作可以获得所需信息，查询结果还是一个表，SELECT语句的一般格式为：

SELECT 要投影的列             #列名间用，逗号分隔
FROM 要查询的表 [AS 表的别名]    
[WHERE 查询条件]
[GROUP BY 列名 [HAVING 条件表达式]]
[ORDER BY 列名 [ASC|DESC]] 

SELECT语句的执行过程：

•  先执行FROM，然后根据where子句指定的查询条件，从from指定的表中选取满足条件的元组，再用select子句投影出指定的列，得到查询结果表。
• 如果还有GROUP BY子句，则将查询结果按其指定的列进行分组
• 如果还有HAVING短语，则只输出满足HAVING条件的元组
• 如果还有ORDER子句，查询结果还要按照ORDER子句指定的列进行排序

SELECT 语句的执行顺序（在 MySQL 和 Oracle 中，SELECT 执行顺序基本相同）：

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

关键字的顺序是不能颠倒的：
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...

 

5.2 无条件查询

概述：只包含SELECT...FROM的查询，相当于只对关系进行投影操作

案例：查询S表中全体学生的学号、姓名、年龄

SELECT SNo,SN,AGE
FROM S

案例：查询学生的全部信息

• * 号表示表的全部列名，不必依依列出

SELECT *
FROM S

5.3 列名的别名

AS关键字

• 作用：重命名一个列
• 语法：紧跟列名，在列名和别名之间加入关键字AS。别名使用双引号，可以方便在别名中包含空格或特殊的字符并区分大小写。
• 特点：AS 可以省略

案例：

SELECT last_name AS name, commission_pct comm
FROM   employees;

SELECT last_name "Name", salary*12 "Annual Salary"
FROM   employees;

5.4 去除重复的行

默认情况下，查询会返回全部行，包括重复行。

SELECT department_id
FROM   employees;

在SELECT语句中使用关键字DISTINCT去除重复行

SELECT DISTINCT department_id
FROM   employees;

SELECT DISTINCT department_id,salary 
FROM employees;

这里有两点需要注意：

1. DISTINCT需要放到所有列名的前面，如果写成SELECT salary, DISTINCT department_id FROM employees会报错。

2. DISTINCT 其实是对列名的组合进行去重，你能看到最后的结果是 74 条，因为这 74 个部门id不同，都有 salary 这个属性值。如果你想要看都有哪些不同的部门（department_id），只需要写DISTINCT department_id即可，后面不需要再加其他的列名了。

5.5 条件查询

使用WHERE子句指定查询条件，将不满足条件的行过滤掉

在WHERE子句中，查询条件通常用三部分来描述

• 数据表的列名
• 比较运算符
• 列名、常数

接下来介绍比较运算符

5.5.1 比较运算符

比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回1，比较的结果为假则返回0，其他情况则返回NULL。

比较运算符经常被用作SELECT查询语句的查询条件来使用，返回符合条件的结果记录。

1．等号运算符（=）

• 判断等号两边的值、字符串或表达式是否相等，如果相等则返回1，不相等则返回0。

• 在使用等号运算符时，遵循如下规则：

  • 如果等号两边的值、字符串或表达式都为字符串，则MySQL会按照字符串进行比较，其比较的是每个字符串中字符的ANSI编码是否相等。

  • 如果等号两边的值都是整数，则MySQL会按照整数来比较两个值的大小。

  • 如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较。

  • 如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL。

• 对比：SQL中赋值符号使用 :=

 2．安全等于运算符 可以用来对NULL进行判断。在两个操作数均为NULL时，其返回值为1，而不为NULL；当一个操作数为NULL时，其返回值为0，而不为NULL。

 3．不等于运算符 不等于运算符（<>和!=）用于判断两边的数字、字符串或者表达式的值是否不相等，如果不相等则返回1，相等则返回0。不等于运算符不能判断NULL值。如果两边的值有任意一个为NULL，或两边都为NULL，则结果为NULL。

4. 空运算符（IS NULL或者ISNULL） 判断一个值是否为NULL，如果为NULL则返回1，否则返回0。  

5. 非空运算符（IS NOT NULL） 判断一个值是否不为NULL，如果不为NULL则返回1，否则返回0。  

 6. 最小值运算符 语法格式为：LEAST(值1，值2，...，值n)。其中，“值n”表示参数列表中有n个值。作用：返回参数列表中的最小值。

mysql> SELECT LEAST (1,0,2), LEAST('b','a','c'), LEAST(1,NULL,2);
+---------------+--------------------+-----------------+
| LEAST (1,0,2) | LEAST('b','a','c') | LEAST(1,NULL,2) |
+---------------+--------------------+-----------------+
|       0       |        a           |      NULL       |
+---------------+--------------------+-----------------+
1 row in set (0.00 sec)

由结果可以看到，当参数是整数或者浮点数时，LEAST将返回其中最小的值；

当参数为字符串时，返回字母表中顺序最靠前的字符；

当比较值列表中有NULL时，不能判断大小，返回值为NULL。

7. 最大值运算符 语法格式为：GREATEST(值1，值2，...，值n)。其中，n表示参数列表中有n个值。作用：当有两个或多个参数时，返回值参数列表中的最大值。假如任意一个自变量为NULL，则GREATEST()的返回值为NULL。

mysql> SELECT GREATEST(1,0,2), GREATEST('b','a','c'), GREATEST(1,NULL,2);
+-----------------+-----------------------+--------------------+
| GREATEST(1,0,2) | GREATEST('b','a','c') | GREATEST(1,NULL,2) |
+-----------------+-----------------------+--------------------+
|               2 | c                     |               NULL |
+-----------------+-----------------------+--------------------+
1 row in set (0.00 sec)

由结果可以看到，当参数中是整数或者浮点数时，GREATEST将返回其中最大的值；当参数为字符串时，返回字母表中顺序最靠后的字符；当比较值列表中有NULL时，不能判断大小，返回值为NULL。

5.5 进行条件查询

1.比较大小

案例：查询选修课程号为C1的学生的学号和成绩

SELECT SNo，Score
FROM SC
WHERE CNo = 'C1'

2.多重条件查询

使用逻辑运算符AND、OR、和NOT可以连接多个条件。优先级由高到低为：NOT、AND、OR，用户可以使用括号改变优先级

案例：查询选修C1或C2且分数大于等于85的学生的学号、课程号、成绩

SELECT SNo,CNo,Score
FROM SC
WHERE (CNo = 'C1' OR CNo = 'C2') AND (Score >= 85)

3.范围查询

案例：查询工资在1000-1500之间的教师的教师号、姓名、职称

SELECT TNo,TN,Prof
FROM T
WHERE Sal BETWEEN 1000 AND 1500

SQL Server中，BETWEEN AND包含等号，有些DBMS中不包含等号

案例：查询工资不在1000-1500之间的教师的教师号、姓名、职称

SELECT TNo,TN,Prof
FROM T
WHERE Sal NOT BETWEEN 1000 AND 1500

4.IN

查询出给定值在 IN列表中 的记录。

5. 模糊查询

使用LIKE或NOT LIKE进行模糊查询

LIKE运算符通常使用如下通配符：

• “%”：匹配0个或多个字符。
• “_”：只能匹配一个字符。
• []：表示在某一范围的字符
• [^ ]：表示不在某一范围的字符

案例：查询所有姓张的教师的教师号和姓名

SELECT TNo,TN
FROM T
WHERE TN LIKE '张%'

案例：查询姓名中第二个汉字是“力”的教师号和姓名

SELECT TNo,TN
FROM T
WHERE TN LINE '_力%'

6.空值查询

查询出某个字段值为NULL的元组

SELECT SNo,CNo
FROM SC
WHERE Score IN NULL

5.6 常用内置函数

内置函数的分类：

两种SQL函数

单行函数

特点：

• 操作数据对象

• 接受参数返回一个结果

• 只对一行进行变换

• 每行返回一个结果

• 可以嵌套

• 参数可以是一列或一个值

聚合函数

重点介绍聚合函数

概述：聚合函数作用于一组数据，然后返回一个值。

聚合函数的类型：

注意：聚合函数不能嵌套调用

• 比如不能出现类似“AVG(SUM(字段名称))”形式的调用。

AVG和SUM函数

只能对数值型数据使用AVG 和 SUM 函数。

案例：求学号为S1的学生的总分和平均分

SELECT SUM(Score) AS TotalScore,AVG(Score) AS AvgScore
FROM SC
WHERE SNo = 'S1'

结果：

使用库函数查询时，需要给查询的每一项内容起别名，否则查询结果中不显示列名

MIN和MAX函数

可以对任意数据类型的数据使用 MIN 和 MAX 函数。

COUNT函数

适用于任意数据类型。

特点：COUNT函数不计算空值，但会计算0

案例：求计算机系学生的总数

SELECT COUNT(SNo)
FROM S
WHERE Dept = '计算机'

案例：求学校中共有多少个系别

SELECT COUNT(DISTINCT Dept) AS DeptNum
FROM S

关键字DISTINCT表示去除重复的行，可计算字段Dept不同值的数目

注意：COUNT(*)可以用来统计元组的个数，不消除重复的行，不允许使用DISTINCT关键字

5.7 分组查询

基本使用

GROUP BY子句会将查询结果按列名进行分组，相同的值元组会被分为一组，并且在WHERE子句后执行

案例：查询每个教师的教师号和上课门数

SELECT TNo,COUNT(*) AS C_Num
FROM TC
GROUP BY TNo

GROUP BY子句会按TNo的值进行分组，TNo值相同的元组分为一组，然后最后对每一组使用COUNT函数计算，统计出哥教师的上课门数

结果：

按多个列进行分组

• 按多个列进行分组是先按前面的列进行分组，然后再对后面的列进行分组

• 先按department_id进行分组，再按job_id进行分组

SELECT   department_id dept_id, job_id, SUM(salary)
FROM     employees
GROUP BY department_id, job_id;

案例：

stu表：

SQL语句：

SELECT sex,address,AVG(math) AS '数学成绩平均值' 
FROM stu 
GROUP BY sex,address;

执行结果：

先按sex进行分组，再按address进行分组

HAVING

使用GROUP  BY 子句分组后，还可以使用HAVING子句按照一定条件过滤分组

使用条件：

• 行已经被分组
• 使用了聚合函数
• HAVING不能单独使用，必须和GROUP BY一起使用

案例：查询选修两门以上(含两门)课程的学生的学号和选课门数

SELECT SNo,COUNT(*) AS SC_Num
FROM SC
GROUP BY SNo
HAVING (COUNT(*) >= 2)

 按SNo的值分组后，对每一组使用COUNT函数统计出每个学生选课的门数，再用HAVING子句筛选选课大于等于2的组

WHERE、GROUP BY、HAVING同时存在SQL语句中的执行顺序是：WHERE、GROUP BY、HAVING

WHERE和HAVING的对比

• WHERE用于筛选基本表或视图中满足条件的元组
• 不能在WHERE中使用聚合函数
• HAVING用于筛选分组后满足指定条件的组
• HAVING中可以使用聚合函数

5.8 排序查询结果ORDER BY

作用：使用ORDER BY 子句指定按哪一列来进行排序

语法：ORDER BY后面跟上列名，在列名后面又跟上排序规则

• 排序规则

  • ASC: 升序（默认）

  • DESC:降序

• ORDER BY子句必须在其他子句之后

案例：查询选修C1的学生学号和成绩，按成绩降序排序（单列排序）

SELECT SNo,Score
FROM SC
WHERE CNo = 'C1'
ORDER BY Score DESC

案例：查询选修C2、C3、C4或C5课程的学号、课程号和成绩，查询结果按学号升序排序，学号相同则按成绩降序排序（多列排序）

SELECT SNo,CNo,Score
FROM SC
WHERE CNo IN ('C2','C3','C4','C5')
ORDER BY SNo,Score DESC

• 可以使用不在SELECT列表中的列排序。

6. 分页查询

6.1 背景

背景1：查询返回的记录太多了，查看起来很不方便，怎么样能够实现分页查询呢？

背景2：表里有 4 条数据，我们只想要显示第 2、3 条数据

6.2 实现规则

• 分页原理

  分页显示就是：将数据库中的查询结果集，一段一段的显示出来。

• MySQL中使用 LIMIT 实现分页

• 格式：

LIMIT [位置偏移量,] 行数

• LIMIT [位置偏移量,] 行数

  • 第一个“位置偏移量”参数：从第几行记录开始显示(不包括指定行)

    • 第一条记录的位置偏移量是0，第二条记录的位置偏移量是1，以此类推。

    • 如果不指定“位置偏移量”，则从表中的第一条记录开始。

  • 第二个参数“行数”：每页显示的记录条数。

• 注意：LIMIT 子句必须放在整个SELECT语句的最后！

• 举例

--前10条记录：
SELECT * FROM 表名 LIMIT 0,10;
或者
SELECT * FROM 表名 LIMIT 10;

--第11至20条记录：
SELECT * FROM 表名 LIMIT 10,10;

--第21至30条记录： 
SELECT * FROM 表名 LIMIT 20,10;

MySQL 8.0中可以使用“LIMIT 3 OFFSET 4”，意思是获取从第5条记录开始后面的3条记录，和“LIMIT 4,3;”返回的结果相同。

• 动态分页显式的公式：（当前页数-1）*每页条数，每页条数

SELECT * FROM table 
LIMIT(PageNo - 1)*PageSize,PageSize;

• 使用 LIMIT 的好处

  • 约束返回结果的数量可以减少数据表的网络传输量，也可以提升查询效率。如果我们知道返回结果只有 1 条，就可以使用LIMIT 1，告诉 SELECT 语句只需要返回一条记录即可。这样的好处就是 SELECT 不需要扫描完整的表，只需要检索到一条符合条件的记录即可返回。

7. 多表的连接查询

概述：当查询涉及两张或以上的表时，称为连接查询

7.1 连接查询的分类

• 根据表连接的方式分类

  • 内连接：参与连接的表没有主次表之分，只将满足连接条件的元组查询出来。

    • 等值连接

    • 非等值连接

    • 自连接

  • 外连接： 除了返回满足连接条件的行，还会返回主表中不满足连接条件的行，当没有匹配的行时, 结果表中相应的列为空(NULL) ，这种连接称为左（或右） 外连接。

    • 左外连接（左连接）：如果是左外连接，则连接条件左边的表也称为主表，右边的表称为从表。

    • 右外连接（右连接）：如果是右外连接，则连接条件右边的表也称为主表，左边的表称为从表。

  • 全连

7.2 笛卡尔积现象

笛卡尔积（或交叉连接）

笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 所有可能的组合（将两张表的每行记录都组合），也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。

其实就是：当两张表进行连接查询时，不加任何条件限制会出现笛卡尔积现象

笛卡尔积的错误会在下面条件下产生：

• 省略多个表的连接条件

• 连接条件无效

• 所有表中的所有行互相连接

此时会做笛卡尔积，一张表的每一条记录会和另一张表的每条记录匹配，即两张表记录条数的乘积

如何避免笛卡尔积现象

• 加连接条件，在笛卡尔中把满足这个连接条件的记录筛选出来（为了避免笛卡尔积， 可以在 WHERE子句中写连接条件。）

• 注意：通过笛卡尔积可以看出，表的连接次数越多效率越低，要尽量降低表的连接次数

• 加入连接条件后，查询语法：

SELECT	table1.column, table2.column
FROM	table1, table2
WHERE	table1.column1 = table2.column2;  #连接条件

#案例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

• 在表中有相同列时，在列名之前加上表名前缀。

• 多表连接的规律：n张表连接，一定会有n-1个连接条件

7.3 实现多表的连接查询

内连接的WHERE实现

select
	...
from 
	表1,表2      #要连接的表写在FROM子句中并用逗号分隔，同样可以取别名
where 
	连接条件 and 查询条件
...

• from子句指明进行连接的表

• where子句写连接条件和查询条件

  • 连接条件：将满足连接条件的元组按行连接起来

• 语法：除了FROM和WHERE子句不同，其余都和单表查询的结构一样

缺点：结构不清晰，表的连接条件和查询条件都放在了where子句中

内连接(INNER JOIN)的实现

这种语法和用WHERE实现内连接的功能一样

• 做内连接时，JOIN前的INNER可以省略，是默认值

• 语法：

SELECT 字段列表
FROM A表 INNER JOIN B表
ON 连接条件
WHERE 等其他子句;

---------------------------------

连接n张表就有n-1个JOIN ON
SELECT table1.column, table2.column,table3.column
FROM table1
JOIN table2 ON table1和table2 的连接条件
JOIN table3 ON table2和table3 的连接条件

语法说明：

• 使用 ON 子句指定连接条件，查询条件还是放在where子句中。

• ON 子句使语句具有更高的易读性。

• 关键字 JOIN、INNER JOIN、CROSS JOIN 的含义一样，都表示内连接

案例：查询“刘伟”老师讲授的课程，要求列出教师号、教师名、课程号

SELECT TNo,Tn,CNo
FROM T INNER JOIN TC
ON T.TNo = TC.TNo
WHERE TN = '刘伟'

 CROSS JOIN交叉连接

自连接查询

概述：一个表自己和自己进行连接操作，给一张表分别取两个别名，将一张表看成两张表

案例：

外连接(OUTER JOIN)的实现

做外连接时，OUTER可以省略，带着可读性强

左外连接(LEFT OUTER JOIN)

• 语法：

#实现查询结果是A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 连接条件
WHERE 等其他子句;

• 举例：

SELECT e.last_name, e.department_id, d.department_name
FROM   employees e
LEFT OUTER JOIN departments d
ON   (e.department_id = d.department_id) ;

右外连接(RIGHT OUTER JOIN)

• 语法：

#实现查询结果是B
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;

• 举例：

SELECT e.last_name, e.department_id, d.department_name
FROM   employees e
RIGHT OUTER JOIN departments d
ON    (e.department_id = d.department_id) ;

需要注意的是，LEFT JOIN 和 RIGHT JOIN 只存在于 SQL99 及以后的标准中，在 SQL92 中不存在，只能用 (+) 表示。

满外连接(FULL OUTER JOIN)

• 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。

• SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。

• 需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。

8. 子查询

概述：子查询指在一个查询语句中嵌套另一个查询语句，包含子查询得语句为父查询或外部查询，子查询的嵌套最多可达255层

SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。

8.1 普通子查询

普通子查询的基本语法结构：

SELECT 要投影的列
FROM 表名
WHERE 列名 操作符 (另一个查询);

注意事项

• 子查询要包含在括号内

• 将子查询放到WHERE子句中，将子查询放在比较条件的右侧

• 单行操作符对应单行子查询，多行操作符对应多行子查询

普通子查询的执行顺序：先执行子查询，得到子查询的结果，然后再执行父查询，把父查询中表的每一个元组都与子查询的结果进行比较

• 普通子查询只执行一次
• 将子查询的结果作为父查询的查询条件

8.2 子查询的分类

单行子查询：子查询的返回值只有一行

多行子查询：子查询会返回多行数据，也称为集合比较子查询

 8.3 单行子查询

单行子查询可以使用以下比较运算符，将父查询和子查询连接起来

案例：

子查询还可以写多个，通过逻辑运算符连接多个子查询

• 题目：查询job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资

SELECT last_name, job_id, salary
FROM   employees
WHERE  job_id =  
                (SELECT job_id
                 FROM   employees
                 WHERE  employee_id = 141)
AND    salary >
                (SELECT salary
                 FROM   employees
                 WHERE  employee_id = 143);

8.4 HAVING 中的子查询

• 首先执行子查询。

• 向主查询中的HAVING 子句返回结果。

题目：查询最低工资大于50号部门最低工资的部门id和其最低工资

SELECT   department_id, MIN(salary)
FROM     employees
GROUP BY department_id
HAVING   MIN(salary) >
                       (SELECT MIN(salary)
                        FROM   employees
                        WHERE  department_id = 50);

8.5 CASE中的子查询

stu表：

SELECT
	id,name,
	CASE
		WHEN math > 90 THEN 'A'
		WHEN math > 80 AND math <= 90 THEN 'B'
		WHEN math > 70 AND math <= 80 THEN 'C'
		WHEN math > 60 AND math <= 70 THEN 'D'
		WHEN math < 60 THEN 'F'
	END AS grade
FROM
	stu;
	
SELECT 
	id,name,
	CASE
		WHEN math >= (SELECT math FROM stu WHERE name = '柳白') THEN 'SUCCESS'
		WHEN math < (SELECT math FROM stu WHERE name = '柳白') THEN 'FAIL'
		END as grade
FROM stu;

CASE中的子查询代码运行结果：

8.6 非法使用子查询

当子查询的结果为多行时，在外层查询中使用单行操作符会发生错误

SELECT employee_id, last_name
FROM   employees
WHERE  salary = (SELECT   MIN(salary)
                 FROM     employees
                 GROUP BY department_id);

8.7 多行子查询

• ：返回多行数据的子查询，也称为集合比较子查询，此时不能直接使用比较运算符

• 语法：在 比较运算符 和 子查询 之间插入以下多行比较操作符

使用ANY

案例：查询讲授课程号为C5的教师名

SELECT TN 
FROM T
WHERE (TNo = ANY (SELECT TNo
                  FROM TC
                  WHERE CNo = 'C5'))

先执行子查询，找到讲授课程号为C5的教师号，子查询结果为多个值(T2,T3,T4)；再执行父查询，查询教师号为T2,T3,T4任意一个值的教师的姓名

使用IN

使用IN可以代替ANY

案例：查询讲授课程号为C5的那些教师姓名

SELECT TN
FROM T
WHERE TNo IN (SELECT TNo
              FROM TC
              WHERE CNo = 'C5')

使用ALL

案例：

8.8 相关子查询

概述：子查询的查询条件需要引用父查询表中的属性值

相关子查询的执行流程：依次选取父查询表中的每行记录，内部的子查询再中利用这行中的某个字段进行查询，如果满足子查询的条件，将该行放入父查询的查询结果集中

特点：

• 相关子查询按照一行接一行的顺序执行，主查询的每一行都要执行一次子查询。

• 相关子查询的执行次数由父查询表的行数决定

案例：

相关子查询中的EXISTS合NOT EXISTS

它们两个表示存在量词，相关子查询通常和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。

特点：带有存在EXISTS和NOT EXISTS的相关子查询不返回数据，只会返回逻辑值“真”或“假”

• 如果在子查询中不存在满足条件的行：

  • 返回 FALSE

  • 继续在子查询中查找

• 如果在子查询中存在满足条件的行：

  • 返回TRUE

  • 不在子查询中继续查找

• NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

案例：

stu表：

SQL代码：

SELECT
	*
FROM stu
WHERE 
	EXISTS (SELECT * FROM stu WHERE name = '柳白')

子查询中存在name为柳白的记录，所以EXISTS会返回真，也相当于只执行了SELECT * FROM stu

结果：

当子查询中不存在满足条件的记录时：

SELECT
	*
FROM stu
WHERE 
	EXISTS (SELECT * FROM stu WHERE name = '张三')

查询结果为空：

8.9 合并查询

UNION

利用UNION关键字，可以将多个不同的SELECT语句的查询结果，合并成单个结果集。

注意：

• 合并时，两个表对应的列数和数据类型必须相同，并且相互对应。

• UNION操作会自动将重复的行去除

语法格式：

SELECT column,... 
FROM table1
UNION [ALL]
SELECT column,... 
FROM table2

• 各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

案例：

• stu表：

teacher表：

SQL代码：

SELECT 
	id,name
FROM
	stu
	
UNION

SELECT
 id,name
FROM
	teacher

 执行结果：

UNION ALL

和UNION一样，但UNION ALL不会去除重复的行

• 注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

8.10 将查询结果插入到表中

INTO语句可以将查询结果插入到一个新建的数据库表或临时表中

• INTO语句写到SELECT的下面

SELECT SNo AS 学号,SUM(Score) AS 总分
INTO Cal_Table
FROM SC
GROUP BY SNo

将查询结果存到临时表中，临时表只存储在内存中，不存储到数据库中：

SELECT SNo AS 学号,SUM(Score) AS 总分
INTO #Cal_Table
FROM SC
GROUP BY SNo

9. 表中数据的操纵

SQL提供的数据操纵语言DML主要包括增、删、改数据

9.1 插入数据到表中

使用INSERT INTO命令

添加一行记录到表中

语法：INSERT INTO 表名 [(列名1,列名2...)] VALUES(值,...)

插入到某个表中的某些列中，再用VALUES指定要添加的值

• 表名：要添加记录到哪个表中
• (列名)：可选项，给哪些列添加数据
• VALUES子句：指定要添加的数据
• 如果不指定要添加的列，默认给一行记录的全部列添加数据，每列都必须指定要添加的值
  • VALUES子句中值的顺序要和表中的列顺序一致

注意：列名的排序顺序不一定和表的列顺序一致；但指定列名时，VALUES子句中值的顺序必须和指定的列顺序一致，个数一样，数据类型相同

案例：给S表中的全部列插入一条学生记录

INSERT INTO S VALUES('S7','张三',21,'女','计算机')

案例：添加一行记录的部分值

INSERT INTO SC(SNo,CNo) VALUES('S7','C1')

• 对于INTO中没有出现的列，会自动赋值为NULL

添加多行记录

方式一：

在VALUES子句后面写多行记录，每行记录用小括号括起来，并用逗号(,)分隔

INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…),…;


INSERT INTO 表名 VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;

方式二：

用子查询将一个表中的数据抽取数行插入到另一个表中

语法：INSERT INTO 表名 [(列名1,列名2)] 

           子查询

案例：

9.2 修改数据表中的数据

用UPDATE语句修改表中一行或多行记录的某些列值

语法：更新某个表 设置它的哪些列为哪些值 还可以用WHERE子句指定更新条件

UPDATE 表名 SET 列名1=值1,列名2=值2,… 
[WHERE 条件]

• UPDATE后面为要修改的表
• SET子句：指定要修改哪些列为哪些值
• WHERE子句：指定要修改的记录应满足的条件，省略时修改表中的所有记录

案例：修改一行

UPDATE T
SET Dept='信息'
WHERE TN='刘伟'

案例：修改多行

UPDATE T
SET Sal = 1.2 * Sal
WHERE (Prof = '讲师') AND (Sal <= 1000)

案例：用子查询指定修改条件

用子查询查询到讲授C5课程的教师号

UPDATE T
SET Comm = Comm + 100
WHERE (TNo IN (SELECT TNo FROM T,TC WHERET.TNo = TC.TNo AND TC.CNo = 'C5'))

案例：用子查询提供具体值

子查询查询到所有教师的平均工资的1.2倍

UPDATE T
SET Sal= (SELECT 1.2 * AVG(Sal) FROM T)

9.3 删除表中的数据

SQL的DELTETE FROM 语句可以删除表中的一行或多行记录

语法：

DELETE FROM 表名 [WHERE 删除条件]

• 表名：要删除数据的表
• WHERE子句：指定要删除的记录应满足什么条件，省略时删除表中的所有记录

删除一行：

DELETE FROM T WHRE TN='刘伟'

删除多行：

DELETE FROM TC

用子查询选择要删除的行：

DELETE
FROM TC
WHERE (TNo = (SELECT TNo FROM T WHERE TN = '刘伟'))

事务

索引