数据库原理与应用

1. 数据库原理与应用 06 – SQL语言 ② 郑捷

2. SQL语言 • SQL概述 • 数据定义☆ • 数据查询★ • 数据更新☆ • 视图 数据库原理与应用

3. SQL语言特点 • 综合统一 • 集数据定义语言、数据操纵语言、数据控制语言功能、风格统一 • 高度非过程化 • 声明式地提出‘做什么’，无须指明‘怎么做’ • 面向集合的操作方式 • 以同一种语法结构提供多种使用方式 • SQL既是独立的语言，又是嵌入式语言 • 语言简洁、易学易用 数据库原理与应用

4. 各物体之间的关系 主机（硬件、操作系统平台） 数据库服务器实例 其他实例 数据库 其他 表 视图 数据库 程序 …… …… 数据库原理与应用

5. 创建数据库 • 创建新数据库时，需要确定的参数有： • 数据库名称：必须的！而且不能和当前服务器实例中其他数据库同名 • 主数据文件以及细节：可以不指定，不指定时按照默认位置、名称等进行创建 • 日志文件以及细节：可以不指定，基本同上 • 次数据文件：一个数据库可以将数据存放在不同文件中，如果不指定次数据文件，系统不会自动建立 数据库原理与应用

6. 创建数据库 • CREATE DATABASE <数据名> [ON [PRIMARY] [<文件格式>]] [LOG ON <文件格式>] • <文件格式>::= ( [NAME=逻辑文件名,] FILENAME=‘操作系统下的逻辑和文件名’ [,SIZE= 文件初始大小] [,MAXSIZE={最大文件大小|UMLIMITED}] [,FILEGROWTH =递增值]) 数据库原理与应用

7. 数据库管理工具与数据库 数据库原理与应用

8. 基本表的定义 • 基本表对象是数据库的核心对象 • 是实际保存数据的对象，基本上所有对象都与其有关 • 表的定义，主要是针对其结构，也就是列（又称为属性、字段等）进行定义 • 附加的，还包括对约束的定义 数据库原理与应用

9. 表的创建 • CREATE TABLE <表名> （ <列名> <数据类型> [列级约束] [, <列名> <数据类型> [列级约束] ] … [, <表级约束>]） • 如果完整性约束条件涉及到表的多个属性列时，则必须定义在表级，否则可以定义在列级也可以定义在表级 数据库原理与应用

10. SQL Server中的数据类型 • 数值类型（精度、范围、表示法） • 整数：TinyInt、SmallInt、Int、BigInt • 浮点数：Real、Float • 精确数：Decimal、Numeric • 货币：SmallMoney、Money • 字符串类型（长短、国际化、可变长） • 常规字符串：Char、NChar、VarChar、NVarChar • 长字符串：Text、NText • 二进制类型（长短、可变长） • 二进制类型：Binary、VarBinary、Image • 时间日期类型（精度、范围） • 时间日期：SmallDateTime、DateTime • 其他类型 • 位：Bit • 时间戳：TimeStamp (RowVersion) • 世界唯一编号：UniqueIdentifier (GUID) 数据库原理与应用

11. 创建表（列） • CREATE TABLE Student( StuID Char(12), StuName NVarChar(5), StuSex Nchar(1), StuAge TinyInt, CitizenID Char(18)) 数据库原理与应用

12. 编程规范：命名规则、书写规则 • 尽可能用英文或者通用的英文缩写 • 不建议用拼音，禁止用拼音声母缩写！！ • 可以用汉字，但是不建议使用 • 选择的单词必须如实其含义，不同含义的应选择不同单词，以不造成歧义为准 • 书写注意要有缩进 • 通过大小写或者下划线的形式分割单词 • 保留空格、空行等使代码便于阅读，适当进行对齐 数据库原理与应用

13. 约束 • PRIMARY KEY：主键约束，等价非空、唯一 • PK_表名 • FOREIGN KEY：外键约束 • FK_表名_被参照表名 • UNIQUE：唯一值约束（允许空值） • UQ_表名_字段名 • CHECK (逻辑表达式)：检查约束 • CK_表名_字段名 • DEFAULT：默认值约束 • DE_表名_字段名 • NULL/NOT NULL：空值/非空值约束 • NN_表名_字段名，往往不显式命名 数据库原理与应用

14. SQL语言 • SQL概述 • 数据定义☆ • 数据查询★ • 数据更新☆ • 视图 数据库原理与应用

15. 带约束的表创建 • CREATE TABLE Student( StuID Char(12) NOT NULL CONSTRAINT PK_Student PRIMARY KEY, StuName NVarChar(5) NOT NULL, StuSex Nchar(1) NOT NULL CONSTRAINT CK_Student_StuSex CHECK (StuSex = '男' OR StuSex = '女'), StuAge TinyInt NULL CONSTRAINT CK_Student_StuAge CHECK (StuAge BETWEEN 16 AND 40), CitizenID Char(18) NULL CONSTRAINT UQ_Student_CitizenID UNIQUE) 数据库原理与应用

16. 改写为表级约束 • CREATE TABLE Student( StuID Char(12) NOT NULL, StuName NVarChar(5) NOT NULL, StuSex Nchar(1) NOT NULL, StuAge TinyInt NULL, CitizenID Char(18) NULL, CONSTRAINT PK_Student PRIMARY KEY (StuID), CONSTRAINT CK_Student_StuSex CHECK (StuSex = ‘男’ OR StuSex = ‘女’), CONSTRAINT CK_Student_StuAge CHECK (StuAge BETWEEN 16 AND 40), CONSTRAINT UQ_Student_CitizenID UNIQUE (CitizenID)) 数据库原理与应用

17. 索引 • 索引是对数据进行检索的一种辅助数据结构 • 生活中的案例：《新华字典》的部首、拼音检字法 • 索引占据一定的空间，并且依附于特定的表，索引的内容与基本表（正文）保持绝对一致 • 索引用于加快检索速度 数据库原理与应用

18. 索引的分类 • 单一索引与复合索引 • 索引内部包含的字段是单一的还是多个 • 唯一索引与不唯一索引 • 是否在索引中记录重复值 • 聚集索引与非聚集索引 • 索引是否影响基本表记录的存储顺序 数据库原理与应用

19. 索引的创建 • CREATE INDEX <索引名>ON <表>(<字段> [ASC|DESC][{,<字段>} [ASC|DESC]])[<索引参数>] • 索引名一般命名规则为：IX_表_字段 数据库原理与应用

20. 索引的优缺点 • 优点（益处） • 加快对信息的检索 • 缺点（开销、代价） • 占用数据库空间（磁盘空间） • 加大信息维护开销 • 索引建立的原则等暂不讨论，在研究数据库设计时进行分析 数据库原理与应用

21. SQL语言 • SQL概述 • 数据定义☆ • 数据查询★ • 数据更新☆ • 视图 数据库原理与应用

22. 数据查询 • 单表简单查询 • 分组查询 • 连接查询 • 嵌套查询（子查询） • 集合查询 数据库原理与应用

23. 数据查询语言 • 数据查询语言（DQL）只有一个语句：SELECT • 但是它是编程中最重要的语句，也是变化最复杂的语句 • 完整的SELECT语句由若干子句构成，格式如下： • SELECT <字段>FROM <表、连接>[WHERE <条件>][GROUP BY <分组字段>][HAVING <条件>][ORDER BY <排序规则>] 数据库原理与应用

24. 案例数据库 • 案例数据库是一个工程管理的数据库 • 涉及公司、工程人员、工程项目、人员参与项目等等 数据库原理与应用

25. 简单查询 • 简单查询就是从表中直接查询出信息 • 可以用*指代全部字段，也可以自己指定是哪些字段，还可以对字段进行计算，或者给字段添加别名 • 简单查询的语法如下： • SELECT <字段> FROM <表>字段：=<字段>|<计算结果>[AS <别名>] 数据库原理与应用

26. 简单查询 • 1. 查出全部工程人员的全部信息 • 2. 查出全部工程人员的姓名和工资 • 3. 查出全部工程人员的工号和日平均工资（取整） • 备注：按照规定，每月算22.5个工作日 数据库原理与应用

27. 选择查询 • 选择查询是对查询的记录进行筛选，只保留满足条件的记录 • 选择查询是在FROM子句后添加WHERE子句 • SELECT <字段>FROM <表>WHERE <条件> 数据库原理与应用

28. 选择查询 • 4. 找出参加工作信息中在工地超过15个月的信息 • 5. 找出编号为“A01”的公司信息 • 6. 找出在重庆施工的工程项目名称 数据库原理与应用

29. 逻辑运算 • 在条件之间可以用逻辑运算连接多个条件 • 条件运算优先级是各种比较NOTAND  OR • 必要时添加括号调整运算顺序 数据库原理与应用

30. 字符串匹配 • 对于字符串而言，除了直接进行比较，还可以进行模式匹配，或者叫做模糊查找 • 模糊查找使用LIKE 谓词 • LIKE左侧为要被检索的字段，右侧为匹配的模式，即模型字符串 • 模式(Pattern)中的特殊字符： • %代表任意个字符（0个、1个或多个） • _(下划线)代表任意一个字符 • [ ]：指定范围 ([a-d]) 或集合 ([abcd]) 中的任何单个字符 • [^]：不属于指定范围 ([a-d]) 或集合 ([abcd]) 的任何单个字符 数据库原理与应用

31. 字符串匹配 • 7. 查找姓名以“陈”开头的工程师 • 8. 查找姓名以“言”结尾的工程师 • 9. 查找姓名以“张”开头以“言”结尾的工程师 • 10. 查找名称中带有“海”字的公司 • 11. 查找编号第2-3位是01，最后一位是2，其余位数不限的工程师 • 12. 查找姓傅、赵的工程师 • 13. 查找不姓傅、赵的工程师 数据库原理与应用

32. IN和BETWEEN • IN和BETWEEN都是用于范围（区间）比较的谓词 • 它们的左侧都是用来比较的字段，右侧是条件 • IN，右侧是用括号包围的一个列表，左侧字段的值等于列表中的任意一个，则比较成立 • BETWEEN，右侧是形如XX AND YY形式的区间，当左侧的值大于等于XX，小于等于YY，则比较成立 • IN相当于左侧的字段逐个写=，并用OR连接 • BETWEEN相当于 左侧>=XX AND 左侧<=YY 数据库原理与应用

33. IN和BETWEEN • 14. 找出地点在上海或北京的公司 • 两种方法 • 15. 找出年龄在25到30岁之间的工程师 • 两种方法 • 16. 找出14和15的相反结果 • NOT的位置 数据库原理与应用

34. 空值的判定 • 空值的运算特殊性 • 空值与任何值进行任何运算结果均为空值 • 空值与任何值进行比较，结果均为FALSE，哪怕是两个空值进行比较 • 因此，判定某个字段是否空值，要用专门的命令： • IS NULL • IS NOT NULL 数据库原理与应用

35. 空值的判定 • 17. 分别找出年龄明确和不明确的工程师 • 18. 找出年龄在30岁以上，或者年龄未知的工程师 数据库原理与应用

36. 查询结果的排序 • 之前的查询结果，都是按照其自然顺序排列的 • 自然顺序是数据在数据库中保存的物理顺序，通常为其主键顺序 • 我们可以在查询中通过ORDER BY子句指定查询结果的顺序 数据库原理与应用

37. 结果排序 • 19. 列出全部工程师信息，按工资排序 • 20. 列出全部工程师的姓名和工资，按工资由高到低排序 • 21. 列出全部工程师的姓名和工号，按照公司编号排序，同一个公司的按照工资从高到低排列 • 22. 找出年龄最小的工程师 数据库原理与应用

38. 消除重复结果 • 当进行列选择的时候，可能会出现重复的结果，这个时候可以用DISTINCT关键字消除重复信息 • 23. 找出工程师所在的公司编号，要求不能重复 数据库原理与应用

39. 数据查询 • 单表简单查询 • 分组查询 • 连接查询 • 嵌套查询（子查询） • 集合查询 数据库原理与应用

40. 预习 • 分组查询 • 连接查询 数据库原理与应用