第四章 SQL语言

4.1   SQL语言概述

前言：

SQL语言全程是结构化查询语言，它是一种在关系型数据库中定义和操纵数据的标准语言。它实际上包含数据定义，数据查询，数据操作和数据控制等与数据库有关的全部功能。

SQL语言的特点

1）一体化。

2）高度非过程化。

3）面向集合的操作方式。

4）提供多种方式使用。

5）语言简洁。

SQL语言按其功能可分为4大部分：数据定义、数据查询、数据操纵、数据控制。

数据定义：用于定义，删除和修改数据库中的对象，数据库，关系表，视图，索引等都是数据库对象。

数据查询：用于实现查询数据的功能，数据查询是数据库中使用最多的操作。

数据操纵：用于添加，删除和修改数据库数据。

数据控制：用于控制用户对数据的操作权限。

SQL语言的主要功能

SQL功能	谓词
数据定义	CREATE,DROP,ALTER
数据查询	SELECT
数据操纵	INSERT,UPDATE,DELETE
数据控制	GRANT,REVOKE,DENY

4.2   SQL支持的数据类型2

关系数据库的表由不同的属性组成，属性是由名称，类型和长度等来描述的。因此，在定义表结构时，应该为每个属性指定一个确定的数据类型。

SQL支持的数据类型：

1）数值型：

精确数字：精确数字类型数值数指在计算机中能够精确存储的数据，比如整型数，定点小数等都是精确型数据。如tinyint,smallint,int,smallmoney等。

近似数字：近似数字类型用于存储浮点型数据，表示在其数据类型范围内的所有数据在计算机中不一定都能精确地表示。如：float,real等。

2）日期时间型：datetime,date，time等。

3）字符串型：

字符串型数据由汉字，英文字母，数字，各种符号或二进制串组成。目前字符的编码方式有两种：一种是普通字符编码，另一种是统一字符编码。普通字符编码指的是不同国家或地区的编码长度不一样，比如：英文字母的编码是1字节（8位），中文汉字的编码是2个字节（16位）。统一字符编码是对所有语言中的字符均采用双字节（16）编码。

普通字符编码串，如char,text等；

统一字符编码串，如nchar,ntext等；

二进制串，如bit等。

4）其他类型：如xml,table等。

4.3   数据定义功能

SQLserver 2012 中数据定义功能主要由create，drop，alter3个动词组成，它们分别完成对数据库对象的创建，删除和修改。这些数据库对象包括：数据库（database），架构（schema），表（table），视图（view）以及索引（index）等。

4.3.1 数据库的定义：

数据库创建    create datebase<数据库名称>
数据库修改    alter datebase<数据库名称>
数据库删除    drop datebase<数据库名称>
4.3.2 架构的定义：

架构（schema，也称模式）是数据库下的一个逻辑命名空间，可以存放表，视图等数据库对象，它是一个数据库对象的容器。

架构创建  create schema <架构名>   

表的修改    alter schema(架构名)    
表的删除    drop schema(架构名)  

4.3.3 表的定义

表的创建：create table（表名）

1. <列名>是表中所包含的属性列的名字。
2. <表名>是所要定义的基本表的名字。
3. 在定义表的同时还可以定义与表有关的完整性约束条件，这些完整性约束条件都会存储在系统的数据字典中，某些涉及表中多个属性列的约束，必须在“表级完整性约束定义”处定义。

在列举完整性约束定义处可以定义如下约束：

not null:非空约束
primary key:指定本列为主键
foreign key:定义本列为引用其他表的外键
unique:限制列的值不能重复
default:指定列的默认值
check:约束列取值范围
表级完整性约束定义(约束涉及多个属性列)    

主键约束：primary key

外键约束：foreign key

唯一值约束：unique

1. 有unique约束的列允许有一个空值。
2. 在一个表中可以定义多个unique约束。
3. 可以在一个列或多个列上定义unique约束。

默认值约束：default

一个默认值约束只能为一个列提供默认值，且默认值约束必须是列级约束。

  默认值约束的定义有两种形式：一种是在定义表时指定默认值约束；另一种是修改表结构时添加默认值约束。

1. 在创建表时定义default约束：

                Default 常量表达式

1. 为已创建好的表添加default约束：

                Default 常量表达式 for 列名

列取值范围约束：check

 注意，如果check约束是定义多列之间的取值约束，则只能在“表级完整性约束定义”处定义。

表的修改：alter table （表名）

表的删除：drop table （表名）

4.4  数据查询功能

查询功能是SQL语言的核心功能，是数据库中使用最多的操作，查询语句也是SQL语句中比较复杂的一个语句。

1）单表查询：

查询语句的基本结构：

1）select用于指定输出的字段；

2）from用于指定数据来源；

3）where用于指定数据的行选择条件；

4）group by用于对检索到的纪录进行分组；

5）having用于指定分组后的选择条件；

6）order by用于对查询的结果进行排序。

选中表中若干列：1.指定列select <目标列名序号>；2.全部列：用"*"代替。

选中表中若干行：1.查询满足条件的元组：比较大小(=,>,<,==,!=等)；确定范围(between 下限值 and 上限值)；确定集合(in,not in)；字符串匹配(like,not like,_,%,[])；涉及空值查询(空值不能运算,is null，not is null,count函数)。

多重条件查询：在where后面使用逻辑运算符and or来组成多条件查询。

消除取值相同行：distinct。

对查询结果进行排序：order by；默认升序 asc；降序 desc。

聚合函数：

1）count(*)统计元组个数；

2）count(<列名>)本列列值个数；

3）sum()计算列的和值；

4）avg()计算列的平均值；

5）max()计算列的最大值；

6）min()计算列的最小值。

2.多表连接查询：

内连接：

1）from 表1 inner join 表2 on <连接条件>；

2）from 表1,表2 where <连接条件>

外连接：from 表1 left/right/outer join 表2 on <连接条件>

3）将查询结构保存到表中：select 列表序列 into <表名> from 数据源

4）子查询：

定义：select ... from ... where ... 为一个查询块。

用子查询基于集合测试：in或者not in进行结果比较。

用子查询基于比较测试：通过比较运算符。

带有some或all的子查询

带exists的子查询：exists，not exists(当有满足条件的数据时，返回假值，双重否定表示肯定)。

5）查询的集合运算：并 union；交 intersect；差 except。

4.5   视图

视图是数据库中的一个对象，它是数据库管理系统提供给用户的以多种角度观察数据库中数据的一种重要机制。它对应于三级模式中的外模式，当不同的用户需要基本表中不同的数据时，可以为每类这样的用户建立一个视图，视图中的内容来自基本表，这些内容可以是某个基本表的部分数据或多个基本表组合的数据。

概述：视图是基于SQL语句的结果集的可视化表，视图包含行和列，就像一个真实的表，视图中的

字段就是来自一个或多个数据库中的真实的表中的字段。它与基本表不同的是，视图是一个虚表，数据库中只存储视图的定义，而不存储视图所包含的数据，这些数据始终仍存放在原来的基本表中，这种模式有两个好处：

（1）视图数据始终与基本表数据保持一致，当基本表中的数据发生变化时，从视图中查询出的数据也会随之变化，

（2)节省存储空间。

合理使用视图带来的好处：；

（1）简化数据查询语句

（2）使用户能从多角度看待同一数据

  (3)提高了数据的安全性

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

视图定义：create view （视图名）

视图不仅可用于查询数据，也可以通过视图修改基本表中的数据，但并不是所有的视图都可以用于修改数据，比如,经过统计或表达式计算得到的视图，就不能用于修改数据的操作。是否通过视图修改数据的基本原则是：如果这个操作能够正确落实到基本表上，则可以通过视图修改数据，否则不行。

修改与删除：修改 alter view （视图名）

删除 drop view （视图名）

物化视图

4.6   数据更改的功能
 

数据插入

数据插入操作可分为两种：单行插入和多行插入。

单行插入 ：insert into <表名> values（值列表）

多表插入：inset into <表名> select 语句

数据更新

update <表名> set <列名>={表达式````}

更新语句可分为：有条件更新和无条件更新。

无条件更新：将书上4.60创建的student_cs 表中学生的年龄加1。

SQL语句为：

update student_cs set sage=sage+1

有条件更新：当用where自己指定更改数据的条件时，可以分两种情况：

一种是基于本表条件的更新，即要更新的记录和更新的记录条件在同一张表中，

另一种是基于其他表条件的更新，即要更新的记录在一张表中，而更新的条件来自另一张表。

基于其他表条件的更新可以用两种方法实现：一种是使用多表连接，另一种是使用子查询。

数据删除：delete from<表名> where <删除条件>

无条件删除：将书上4.59中创建的student_cs表中的数据删除。

SQL语句为：

delete from student_cs

有条件删除：当用where子句指定要删除记录的条件时，同update语句一样，也分为两种情况:

一种是基于本表条件的删除。例如，删除所有不及格学生的选课记录，要删除的记录与删除的条件都在sc表中。

另一种是基于其他表条件的删除。例如，删除计算机系学生的选课记录，要删除的记录在sc表中，而删除的条件（计算机系）在student表中。基于其他表条件的删除同样可以用两种方法实现：一种是使用多表连接，另一种是使用子查询。

注意：

删除数据时，如果表之间有外键引用约束，则在删除被引用表数据时，系统会自动检查所删除的数据是否被外键表引用，如果是，则默认情况下不允许删除被；引用表数据。

4.7   数据控制功能

  

SQL语言的数据控制功能主要通过grant（授权）语句，revoke（回收）语句和deny（拒权）语句来实现。

授权

数据库对象的创建者通常具有与该对象相关的所有权限，创建者可以通过grant 语句向其他用户授予操作该对象的某些权限。grant语句的格式如下：

grant <权限>，【，<权限>】···

【on <对象类型><对象名>】 

to <用户>【<用户>】

【with grant option】【as 用户】；

回收授权

与授权操作相对应的是回收授权。它是指回收指定用户对某个数据库对象的某种权限。回收操作可以有数据库管理员或其他授权者使用revoke 语句收回，revoke语句的格式如下：

revoke 【grant option for】<权限>，【，<权限>】···

【on <对象类型><对象名>】

from <用户>【，<用户>】···

【cascade】[as 用户]；

其中，cascade选项是指示当前正在撤销的权限也将从其他被该主体授权的主体中撤销，使用cascade参数时，还必须同时指定grant option for 参数，表示对授予with grant option 权限的权限执行级联撤销，将同时撤销该权限的grant 和 deny权限。

拒权

拒权是指拒绝为指定用户对某个数据库对象使用某种权限。防止该主体通过组或角色成员身份继承权限。dent语句用于拒绝权限，其格式如下：

deny <权限>[,<权限>]···

【on<对象类型><对象名>】

to<用户>【，<用户>】···；