数据库原理笔记（一）

2021-04-14 06:47 462人阅读评论(0)

文章目录

1. SQL语言基础

1. SQL语言基础

1.1 SQL语言的组成

1. 命令（函数)

说明：建立，增删，查询，更新等动作

例：创建——create

修改——alter

删除——drop

查询——select

2. 子句

说明：搭配命令使用

例如：from——指定数据源

where——设定条件

group by——分组

having——与group by连用，设定条件

order by——排序

3. 运算符

说明：加在句子中，使其多样化

（1）逻辑运算符

与、或、非

（2）比较运算符

大于、小于、等于

4. 统计函数

说明：通过现成的函数来完成一系列基础运算

例如：SUM——求和

AVG——求平均值

5. 通配符

通配符

说明：模糊设定条件使用

%，_ ，[ ] ，[^ ]

1.2 数据语言定义

1.2.1 数据库简介

数据库由主要数据文件（.mdf），次要数据文件（.ndf）和日志文件（.ldf）组成。

1.2.2 数据库创建

两种方式：1. SQL语言 2. 图形界面

1.2.2.1 SQL语言创建数据库

file子句

file子句分析

1.2.3 数据库表的操作

1.2.3.1 数据表的创建

语法结构：

create table 表名

（列名数据类型完整性约束条件，

…

)

完整性约束条件

PRIMARY KEY：主键，每张表只有一个主键，不允许空值。
UNIQUE：约束用于限制在一个列中不能有重复的值。
DEFAULT：提供列的默认值，一个列只能有一个默认值约束。
CHECK：对列的取值范围进行限制。
NOT NULL：不能为空值。

1.2.3.2 表的修改

添加新的列

例：

为SC表添加“修课类别”列，此列的定义为：

Type NCHAR（1），允许空。

ALTER TABLE SC

ADD Type NCHAR(1) NULL
添加新的数据类型

例：

将新添加的Type列的数据类型改为 NCHAR（2）

ALTER TABLE SC

ALTER COLUMN Type NCHAR(2)
添加约束条件

例：

为Type列添加限定取值范围为{必修、选修}的约束。

ALTER TABLE SC

ADD CHECK（ Type IN（必修，"选修））
删除某一列

例：

删除SC表的“type”列

alter table SC

drop column type
表的删除

drop table <表名>

注意事项：

表删除时，该表中不能有外键引用约束，不然无法删除，需要先将所引用的外键表进行删除，才可以对其进行操作。
数据库删除

drop database <数据库名>

1.3. 数据查询

1.3.1 简单数据查询

SELECT<目标列名序列>–需要哪些列

FROM<数据源>–来自于哪些表

[WHERE<检索条件>]–根据什么条件

[GROUP BY<分组依据列>]

[HAVING<组提取条件>]

[ORDER BY<排序依据列>]

1.3.1.1 select 语句基本

distinct：用于消除重复项

用法：在select语句中，直接置于列名前面

*：用于查询满足条件的所有列

例：Select from student where ssex=‘男’

1.3.1.2 where子句

查询条件	谓词
比较运算	=，>，<，not +等
确定范围	between…and，not between…and
确定集合	IN，NOT IN
字符匹配	LIKE，NOT　LIKE
空值	IS NULL，IS NOT NULL
逻辑谓词	AND， OR

字符匹配

匹配中可包含如下四种通配符：

＿：匹配任意一个字符

％：匹配0个或多个字符；

［　］：匹配［　］中的任意一个字符（若要比较的字符是连续的，则可以用连字符“－”表达）；

［＾　］：不匹配［　］中的任意一个字符。

1.3.2 统计函数查询

用于select语句中

常用函数：

count( ):

count（【all】*）【as 列别名】返回表中的记录数

COUNT(列名) 函数返回指定列的值的数目（NULL 值不计入）

COUNT(distinct 列名) 返回指定列的不同值的数目（NULL 值不计入）

sum( )：计算某一列数值总和
max( )：求某一列最大值
min( )：求某一列最小值
avg( )：求某一列值的平均值
FIRST( )：返回指定字段的第一个记录的值
ROUND( )：用于把数值字段舍入为指定的小数位数。（四舍五入规则）
mid( )：用于从文本字段中提取字符。
语法：SELECT MID(column_name,start[,length]) FROM table_name
len( )：返回文本字段中值的长度

1.3.3 分组查询

一般形式：

[GROUP BY<分组列名>]

[HAVING<条件设置>]

注意

GROUP BY子句中的分组依据列必须是表中存在的列名，不能使用AS子句指派的结果集列的别名。
带有 GROUP BY子句的 SELECT语句的查询列表中只能出现分组依据列或统计函数，因为分组后每个组只返回一行结果。
Having子句后面可以使用统计函数。

例：

说明

WHERE子句用来筛选FROM子句中指定的数据源所产生的行数据。

GROUP BY子句用来对经 WHERE子句筛选后的结果数据进行分组。

HAⅥNG子句用来对分组后的结果数据再进行筛选。

1.3.4 排序查询/top关键词

理念：对查询结果进行排序。
排序子句为：

ORDER BY<列名>[ASC|DESC]

[，<列名>…]

说明：按<列名>进行升序（ASC）或降序（DESC）排序。

注意：若不标明则默认按升序（ASC）排序。

1.3.4.1 top关键词

在进行查询时有时只希望列出结果集中的前几个结果，而不是全部结果。

例如，竞赛时可能只取成绩最高的前三名可以使用T○P谓词限制输出的结果。格式如下：
TOP n [percent] [WITH TIES ]
n为非负整数。
ToPn：表示取查询结果的前n行;
TOP n percnet：表示取查询结果前n%行；
WITH TIES：表示包括并列的结果。

例：

查询年龄最大的三个学生的姓名及出所在的系。
SELECT TOP 3 Sname, Sdept 

FROM Student 

ORDER BY（getdate()-sbirthday）DESC

若包括年龄并列第3名的学生，则：

SELECT TOP 3 **WITH TIES** Sname, Sdept 

FROM Student 

ORDER BY （getdateO-sbirthday） DESC

1.3.5 多表连接查询

若一个查询同时涉及两个或两个以上的表，则称之为连接查询。

连接查询包括内连接、外连接。

1.3.5.1 内连接

内连接语法：

select…

from 表名

[INNER] JOIN 被链接表

ON 连接条件

连接条件：表1.列名=表2.列名

例1：查询每个学生及其选课的详细信息。

SELECT Student.sno,Sname,Ssex,Sbirthday,Sdept,Cno,Grade

FROM Student 

INNTER JOIN SC 

ON Student.Sno SC.Sno

为避免出现重复的学号列，在select中指定了选用学生表的学号信息

例2（带条件的连接）查询D01系学生的修课情况，要求列出学生的名字、所修课的课程号和成绩。

SELECT Sname, Cno, Grade

FROM Student S

JOIN SC

ON s.Sno SC.Sno

WHERE Sdept=‘D01’（先列出满足条件的情况再筛选）

1.3.5.2 自连接

是一种特殊的内连接。
相互连接的表物理上为同一张表。
过为两个表取别名，使之在逻辑上成为两个表。

例：查询与高天在同一个系学习的学生的姓名和所在的系。

SELECT S2.Sname, S2.Sdept–选用作为结果表中的姓名和系名

FROM Student S1 JOIN Student S2–取两个别名，自己连接自己

ON S1.Sdept=S2.Sdept–是同一个系的学生

WHERE S1.Sname=‘高天’–S1表作为查询条件表

AND S2.Sname !=‘高天’–S2表作为结果表

1.3.5.3 外连接

左外连接即保留左边表中的所有数据，右表中没有的数据就是NULL。
右外连接则相反。
全外连接则保留所有数据。

例：查询学生的选课情况，包括选修了课程的学生和没有选修课程的学生。

SELECT Student.Sno,Sname,Cno,Grade

FROM Student LEFT OUTER JOIN SC

ON Student.Sno SC.Sno

1.3.5.4 子查询

在原来的查询语句中嵌套一个查询，以方便将一些查询条件写出。

例1：查询与高天在同一个系的学生

SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN--Sdept满足子查询的条件
	(SELECT Sdept 
     FROM Student 
     WHERE Sname='高天′
     ）--找出高天所在系
AND Sname！='高天'--排除高天本人

例2：查询选修了“计算机应用基础”课程的学生的学号。
姓名。

SELECT Sno,Sname
FROM Student
WHERE Sno In
	(SELECT Sno 
     FROM SC 
     WHERE Cno IN
		(SELECT Cno FROM Course
     	WHERE Cname='计算机应用基础'))

1.4 数据增删改

1.4.1 插入数据

插入完整数据（顺序插入）
语法一：
INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);

语法二：
INSERT INTO 表名 VALUES (值1,值2,值3…值n);
指定字段插入数据
语法：
INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);
插入多条记录
语法：
INSERT INTO 表名 VALUES
(值1,值2,值3…值n),
(值1,值2,值3…值n),
(值1,值2,值3…值n);
插入查询结果
语法：
INSERT INTO 表名(字段1,字段2,字段3…字段n)
SELECT (字段1,字段2,字段3…字段n) FROM 表2
WHERE …;

1.4.2 删除数据

语法：

DELETE FROM <表名>

WHERE <条件>

1.4.3 更改数据

语法：

UPDATE <表名>

SET

字段1=值1,

字段2=值2,

WHERE <条件>

1.5 视图和索引

1.5.1 视图

视图是一张虚拟表，可以从多个实体表或其他视图中提取数据。

将多表查询转换成单表查询

简化查询语句的复杂度

提高了数据的安全性

提供了一定程度的逻辑独立性

创建视图的语法：

create view <视图的名称> 
AS--指定视图要执行的操作
select --选择要创建视图的数据
from --数据来源
where--筛选条件

1.5.2 索引

索引有两种，聚集索引和非聚集索引，索引主要目的是提高了SQL Server系统的性能，加快数据的查询速度与减少系统的响应时间

什么情况下设置索引

动作描述	使用聚集索引	使用非聚集索引
外键列	√	√
主键列	√	√
列经常被分组排序(order by)	√	√
返回某范围内的数据	√	×
小数目的不同值	√	×
大数目的不同值	×	√
频繁更新的列	×	√
频繁修改索引列	×	√
一个或极少不同值	×	×

创建索引：

CREATE [UNIQUE][CLUSTERED | NONCLUSTERED]  INDEX  index_name  

ON {table_name | view_name} [WITH [index_property [,....n]]

UNIQUE: 建立唯一索引。

CLUSTERED: 建立聚集索引。

NONCLUSTERED: 建立非聚集索引。

index_name: 索引的名称

Index_property: 可选字段，可以选择加入索引属性。

删除索引：

同值 | √ | × |
| 大数目的不同值 | × | √ |
| 频繁更新的列 | × | √ |
| 频繁修改索引列 | × | √ |
| 一个或极少不同值 | × | × |