数据库管理及应用SQL Server 2019（太原理工大学）:第四章

一、对数据库的操作

建立数据库的两种方法

（1）菜单页面建立：启动SQL server 2019并连接后，如下图
（2）SQL语句：在菜单栏上点击“新建查询”，输入语句 CREATE DATABASE <数据库名> [AUTHORIZATION<用户名>] [ON [PRIMARY] (路径/文件大小）]

• AUTHORIZATION选项是用于设定数据库拥有者，默认（首次新建无指定）为登录的注册人
• [ON [PRIMARY] (路径/文件大小）] 用于设定数据库存放的位置和文件初始容量，路径默认为当前盘及当前路径

CREATE DATABASE 学生选课记录
ON PRIMARY(
NAME = 学生选课记录,

打开数据库

（1）菜单页面

（2）SQL语句：USE <数据库名>

USE 学生选课记录

关闭/切换数据库

USE[<数据库名>]

• 切换是指在已经打开某个数据库的情况下，切换到另一个数据库，同时关闭原数据库的过程。

修改数据库

（1）修改数据库名

• ALTER DATABASE<数据库名>
  MODIFY NAME = <新数据库名>

ALTER DATABASE 学生选课纪律
MODIFY NAME = 学生选课记录

（2）修改数据库容量

ALTER DATABASE <数据库名>
MODIFY FILE
(...
...
)

• …可以表示的参数
  NAME = “逻辑名”
  SIZE = 修改后的大小
  MAXSIZE= 修改后的最大容量
  FILEGROWTH = 新的增长方式
• 只有建立数据库权限者，才能执行修改命令
• 为了防止文件中信息被损坏，文件容量只能增加。

删除数据库

（1）界面菜单直接删除

（2）SQL语句：DROP DATABASE<数据库名>

• DROP语句删除数据库的前提条件是该数据库处于正常关闭状态。
• 删除数据库后，数据库及数据表、视图等对象也将会被全部删除。

USE MASTER     /**先切换到另一个数据库**/
DROP DATABASE 学生选课记录  /**再进行删除**/

二、对数据表的操作

数据表的建立（不同的数据库可以有相同名称的表名）

（1）界面菜单直接操作：

• 点击新建表

• 开始输入
  
• 输入完成后，选择一个作为主键

（2）SQL语句建立：

CREATE TABLE <表名>(
<列名1><列数据类型>[列完整性约束],
<列名2><列数据类型><列完整性约束>,…
[表级完整性约束])

常用的列完整性约束有5种：

• PRIMARY KEY约束：指定主键
• UNIQUE约束：表示该列只存放唯一（不重复）的属性值
• NOT NULL和NULL约束：分别确定该列值不为空或为空
• DEFAULT约束：该列未定义时的默认取值
• CHECK约束：通过约束表达式设置其列值应当满足的具体条件并检查约束。
• FOREIGN KEY约束：指定外键，语法格式为：
  FOREIGN KEY (属性名) REFERENCES 被参照表名 (属性名)，设置外键的前提是被参照表（或者叫父表）设置了主键或具有UNIQUE约束的列。
• SQL中，PK表示主键，FK表示外键。

USE 学生选课记录
CREATE TABLE STUDENT (
姓名 VARCHAR(10) NOT NULL,
性别 VARCHAR(5) NOT NULL, CHECK (性别 IN ('男','女')),
学号 VARCHAR(10) PRIMARY KEY(学号),
专业 VARCHAR(10) NOT NULL,
班级 VARCHAR(10) NOT NULL,)

CREATE TABLE COURSE (
课程号 VARCHAR(10) PRIMARY KEY(课程号),
课程名 VARCHAR(50) NOT NULL,
任课教师 VARCHAR(10) NOT NULL,
学分 INT NOT NULL,)

CREATE TABLE SC (
学号 VARCHAR(10) NOT NULL,
课程号 VARCHAR(10) NOT NULL,
成绩 INT NULL,
FOREIGN KEY(学号) REFERENCES STUDENT(学号) /**将学号设置成外键，被参照表是STUDENT表中的学号列**/
)
--创建了三个表

一些对数据类型的说明：
（1）INT：可以表示从-2³¹ ~ 2³¹-1（-2147483648，2147483647）内的整数，每个数据按4个字节存储，其中1位表示正负，剩下31位表示数据大小。
（2）SMALLINT：可以表示从-2¹⁵ ~ 2¹⁵-1（-32768，32767）内的整数，每个数据占用2个字节的存储空间。其中1位表示正负，15位表示大小。
（3）TINYINT：表示0~255之内的整数，每个数据占用1个字节存储。
（4）BIGINT：表示从-2⁶³ ~ 2⁶³-1内的整数。每个数据占用8个字节的存储空间。
（5）FLOAT可以精确到15位，REAL可以精确到7位。
（6）CHAR(N)： 每个字符和符号占一个字节的存储空间。n 表示所有字符所占的存储空间，取值为1 到8000，系统默认值为1。若输入数据的字符数小于n，则系统自动在其后添加空格来填满设定好的空间。若输入的数据过长，将会截掉其超出部分。
（7）NCHAR(N)：n 的取值为1 到4000，每个字符占两个字节的存储空间，所以可以将全世界的语言文字放在一个数据列中而不会出现编码冲突。
（8）VARCHAR(N)：n 的取值为1 到8000，若输入的数据过长，将会截掉其超出部分。与CHAR不同的是，VARCHAR具有变动长度的特性，因为存储长度为实际数值长度（意思是若输入数据的字符数小于n ，系统不会在其后添加空格来填满设定好的空间。）

数据表的修改

（1）新增列：（注意新增列的属性不可定义为NOT NULL。不管原来数据表中是否有数据，新增列一定为空值NULL）

ALTER TABLE<表名>
ADD <新列名><列数据类型><列完整性约束>

ALTER TABLE STUDENT
ADD 出生日期 DATATIME

（2）增加表级完整性约束（主要是说主键、外键等）

ALTER TABKE<表名>
ADD <表级完整性约束>

ALTER TABLE SC
ADD PRIMARY KEY("学号", "课程号")

（3）删除列：

ALTER TABLE <表名>
DROP COLUMN <列名> [CASCADE | RESTRICT]

ALTER TABLE STUDENT
DROP COLUMN 出生日期

（4）删除完整性约束

ALTER TABLE <表名>
DROP CONSTRAINT<完整性约束名>[ RESTRICT | CASCADE ]

（5）修改原有列（注意一定要慎重修改原有列，因为修改可能改变原有约束条件或者是破坏原有数据）

ALTER TABLE<表名>
ALTER COLUMN <列名>

ALTER TABLE STUDENT
ALTER COLUMN 性别 NCHAR(2)   /**将性别这一列的数据类型修改为NCHAR(2)**/

注意这里的RESTRICT和CASCADE!

• 若选择RESTRICT，则代表如果该列被其他对象引用，关系数据库管理系统将拒绝删除该列
• 若选择CASCADE，则代表则自动删除引用了该列的其他对象

数据表的删除

DROP TABLE<数据表名>

• 删除数据表的时候，表中的所有数据及建立的索引将会一起被删除，系统将保留该表上的视图定义，但不可使用。
• 这里也是同样道理的RESTRICT和CASCADE。RESTRICT：如果存在依赖该表的对象，则此表不能被删除；CASCADE：在删除基本表的同时，相关的依赖对象一起删除。即RESTRICT有限制，CASCADE无限制。

DROP TABLE STUDENT

三、对数据进行的操作

数据的插入

（1）单个记录的插入

INSERT INTO<数据表名> [(<列名1>,<列名2>,…<列名n>)]
VALUES[(<列值1>,<列值2>,…<列值n>),
(<列值1>,<列值2>,…<列值n>),…]

INSERT STUDENT ("姓名","性别","学号","专业","班级")/**指定了要插入的列名，若是有属性列在INTO子句中没有出现，那么记录在对应列上取空值**/
VALUES ('钱二','女',01116085,'经济学','1班')/**上下顺序一定要一致**/

INSERT INTO STUDENT VALUES('孙三','男','26838495','法学','1班')/**默认按顺序插入值**/
INSERT INTO STUDENT VALUES('李四','女','15285009','计算机科学','1班')

注意：在列名序列中应包含所有不可取空值的列，以免操作受限。

（2）插入成批查询结果：与SELECT语句结合

INSERT INTO 学生赵一
SELECT *
FROM SC
WHERE 学号 = 54228126 /**将学号为54228126的学生的SC表中的信息插入到学生赵一表中**/

数据的删除

DELETE
FROM表名
WHERE 条件表达式

DELETE
FROM SC
WHERE 学号 = 37161435 AND 课程号 = '#006'  /**从SC表中删除学号为37161435并且课程号为'006'的学生记录**/

数据的修改

UPDATE<数据表名>
SET<列名> = <表达式>
WHERE 条件表达式

UPDATE SC
SET 成绩 = 88
WHERE 学号 = 54228126 AND 课程号 = '#004'/**更新SC表中学号为54228126并且课程号为'#004'的学号的学生的成绩**/

数据查询

一些常用语句及关键字：

（1）DISTINCT可以使查询的结果只含不同的记录，取消后面其他相同的记录。
（2）*选项将显示数据表全部字段中满足查询条件的所有查询记录。
（3）WHERE子句后跟条件表达式。
（4）FROM后跟要查询的表或试图。
（5）SELECT后跟要选择的对象。
（6）GRUOP BY子句将查询结果按指定选取列值分组，该属性列值相同的记录为一组，各组产生结果表中的一条记录。
（7）HAVING子句出现在GRUOP BY后，作用是将分组后查询结果中不满足“组条件表达式”的记录去掉。常用聚合函数来完成任务。
（8）ORDER BY子句，使查询结果对指定的列值按一定次序排列。默认为ASC（升序），DESC为降序。

a.聚合函数的使用

• AVG（列名）函数：求平均值，此列必须是数值型。

SELECT 学号, AVG(成绩) AS 平均成绩
FROM SC
GROUP BY 学号  /**按学号将学生分组，计算每个学生所有选修课的平均成绩**/

结果如下：

• SUM（列名）函数：求总和，要求此列必须为数值型。

SELECT 学号, SUM(成绩) AS 总成绩
FROM SC
GROUP BY 学号/**按学号将学生分组，求每个学生所有选修课的总成绩**/

结果如下：

• COUNT(*)函数：计算记录的个数、人数等

SELECT 学号, COUNT(成绩) AS 总数
FROM SC
GROUP BY 学号/**统计每名学生选修的课程个数**/

结果如下：

• MAX(列名)函数：求最大值

SELECT 学号, MAX(成绩) AS 最高成绩
FROM SC
GROUP BY 学号/**求每名学生所选的课程中的最高成绩**/

结果如下：

• MIN(列名)函数：求最小值

SELECT 学号, MIN(成绩) AS 最低成绩
FROM SC
GROUP BY 学号

结果如下：

b.条件查询

（1）比较条件查询：比较运算符< > >=等

SELECT 学号
FROM SC
WHERE 成绩 < 60 /**查询成绩不及格的学生学号**/

结果如下：

（2）谓词条件查询：

• 指定范围：(NOT)BETWEEN…AND…

SELECT 学号,成绩
FROM SC
WHERE 成绩 BETWEEN 90 AND 100 /**在SC表中查询课程成绩在90~100之间的学生学号及成绩**/

结果如下：

• 包含查询：IN，判断属性值是否属于某个集合
• 空值查询：IS NULL,IS NOT NULL

SELECT *
FROM SC
WHERE 成绩 IS NULL

• 统计查询：GROUP BY后跟HAVING语句

c.模糊查询
在条件表达式中，(NOT)LIKE字符串常数

• %用于表示任意长度。比如a%b表示以a开头且以b结尾的任意长度的字符串。
• _表示任意一个单字符。比如a_b表示以a开头，以b结尾的长度为3的任意字符串。

SELECT *
FROM STUDENT
WHERE 姓名 LIKE '%十%'

结果如下：

d.排序查询：ORDER BY

SELECT *
FROM SC
ORDER BY 成绩/**将学生按成绩大小排序，默认为从小到大**/

结果如下：

d.连接查询：同时涉及了两个表以上的表的查询，分为自身连接查询和多表连接查询。

• 在FROM中，多张表用“，”隔开
• 在WHERE中，要指定连接的“表名.列名”和连接条件。

（1）自身连接查询（要对表起名加以区分）

如：查询机械工业出版社出版的图书《数据库系统》作者的其它图书名称和出版社信息。

SELECT TWO.书名, TWO.出版社,ONE.作者
FROM 图书 ONE,图书 TWO
WHERE ONE.作者 = TWO.作者 AND ONE.书名 = '数据库系统'
AND ONE.出版社 = '机械工业出版社'

（2）多表连接查询

SELECT 姓名,成绩
FROM STUDENT, SC   /**连接STUDENT和SC表**/
WHERE STUDENT.学号 = SC.学号 AND SC.成绩 > 90 /**查询成绩大于90的学生姓名**/

结果如下：

注意： 如果两个查询的查询结果完全一致时，可以进行并（UNION）、交（INTERSECT）、差（EXCPT）操作。

e.嵌套查询：外层为父查询，内层为子查询。

• SOME 表示“某一个”，ALL表示“每个”或“所有”。
• EXISTS表示存在，返回的是“真”或“假”

FROM STUDENT
WHERE EXISTS(SELECT*
FROM SC
WHERE STUDENT.学号 = SC.学号 AND 成绩= 100)/**查询存在成绩为100的学生的姓名**/

结果如下：