SQL

SQL的发展

1974年，由Boyce和Chamberlin提出
1975~1979，IBM San Jose Research Lab的关系数据库管理系统原型System R实施了这种语言
SQL-86是第一个SQL标准
SQL-89、SQL-92(SQL2)、SQL-99(SQL3)
最初叫做 Sequel（Structured English Query Language）结构化查询语言
DB 数据库 Datebase
DBS 数据库系统 Datebase System
DBMS 数据库管理系统 Database Management System
DBA 数据库管理人员 Datebase Administrator

主流产品
oracle（银行 大型企业）
oracle公司产品
产品免费，服务收费
SQL sever（Microsoft）
​ 针对不同用户群体的多个版本
易用性好
DB2（IBM）
IBM公司产品
支持多操作系统，多种类型的硬件和设备
MySQL（免费 成本最低）
开放源代码
网站应用广泛

非过程化语言

SQL语言进行数据库操作时，只需要提出“做什么”，不需要指明“怎么做”。“怎么做”是由DBMS来完成

SQL的形式

交互式SQL

• 一般DBMS都提供联机交互工具
• 用户可直接键入SQL命令对数据库进行操作
• 由DBMS来进行解释

  嵌入式SQL

• 能将SQL语句嵌入到高级语言（宿主语言）
• 使应用程序充分利用SQL访问数据库的能力、宿主语言的过程处理能力
• 一般需要预编译，将嵌入的SQL语句转化为宿主语言编译器能处理的语句

SQL语言主要组成部分

数据定义语言（DDL，Data Definition Language）

CREATE（创建） ALTER（更改） DROP（删除（群体））

数据定义语言是指用来定义和管理数据库以及数据库中的各种对象的语句，这些语句包括CREATE、ALTER和DROP等语句。在SQL Server中，数据库对象包括表、视图、触发器、存储过程、规则、缺省、用户自定义的数据类型等。这些对象的创建、修改和删除等都可以通过使用CREATE、ALTER、DROP等语句来完成。

常见的数据类型

• 字符型

SQL Server	SQL99	说明
char(n)	character	固定长度的字符串类型，n表示字符串的最大长度，不足则末尾空格补齐，取值范围为1～8000
varchar(n)	character varying	可变长度的字符串类型，n表示字符串的最大长度，取值范围为1～8000
nchar(n)	national characte	固定长度的Unicode数据，n表示字符串的最大长度，取值范围为1～4000
nvarchar(n)	national character varying	可变长度的 Unicode 数据，n表示字符串的最大长度，取值范围为1～4000
ntext		最多可存储2^30-1 (1,073,741,823)个字符的统一字符编码文本
binary(n)	binary	固定长度的二进制字符数据，n表示最大长度，取值范围为1～8000
varbinary(n)	binary varying	可变长度的二进制字符数据，n的取值范围为1～8000

• 数值型

SQL Server	SQL99	说明
Bigint		8字节，存储从–2^63 (– 9223372036854775808)到2^63-1 (9223372036854775807) 范围的整数。
Int	Integer	4字节，存储从–2^31(–2,147,483,648 ) 到2^31-1 (2,147,483,647 ) 范围的整数
Smallint	Smallint	2字节，存储从–2^15(–32,768 ) 到2^15-1 (32,767 )范围的整数
Tinyint		存储从0 到 255 之间的整数
Bit	Bit	存储1或0
numeric(p,q)或decimal(p,q)	decimal	定点精度和小数位数。使用最大精度时，有效值从 –10^38 +1 到 10^38 -1。其中，p为精度，指定小数点左边和右边可以存储的十进制数字的最大个数。q为小数位数，指定小数点右边可以存储的十进制数字的最大个数,0 <= q <= p。q的默认值为0
float	float	8字节，存储从–1.79E + 308到 1.79E + 308范围的浮点型数
real		4字节，存储从 –3.40E + 38到 3.40E + 38 范围的浮点型数

• 日期／时间型

SQL Server	说明
Datetime	占用8字节空间，存储从1753年1月1日到9999年12月31日的日期和时间数据，精确到百分之三秒（或3.33 毫秒）
Smalldatetime	占用4字节空间，存储从1900年1月1日到2079年6月6日的日期和时间数据，精确到分钟

• 货币型

  货币数据存在的精度固定为四位小数，用四位小数的DECIMAL类型

SQL Server	说明
money	8字节，存储的货币数据值介于–263 (–922,337,203,685,477.5808) 与 263-1 (+922,337,203,685,477.5807) 之间，精确到货币单位的千分之十。最多可以包含19位数字
Smallmoney	4字节，存储的货币数据值介于–214,748.3648 与 +214,748.3647 之间，精确到货币单位的千分之十

• 文本和图像型

SQL Server	说明
image	大容量的、可变长度的二进制字符数据，可以存储多种格式的文件，最大约为2GB
text	可存储231-1 (2,147,483,647)个字符的大文本

数据操纵语言（DML，Data Manipulation Language）

**INSERT DELETE UPDATE SELECT(增删改查) **

数据操纵语言是指用来查询、添加、修改和删除数据库中数据的语句，这些语句包括SELECT、INSERT、UPDATE、DELETE等。在默认情况下，只有sysadmin、dbcreator、db_owner或db_datawriter等角色的成员才有权利执行数据操纵语言。

数据控制语言（DCL，Data Control Language）

GRANT（授权） REVOKE（回收权限） COMMIT（提交） ROLLBACK（回滚）

数据控制语言（DCL）是用来设置或者更改数据库用户或角色权限的语句，这些语句包括GRANT、REVOKE 、DENY等语句，在默认状态下，只有sysadmin、dbcreator、db_owner或db_securityadmin等角色的成员才有权利执行数据控制语言。

SQL语句

建立表结构 Create

语法：CREATE DATABASE 数据库名

• 定义基本表的语句格式：
• CREATE TABLE <表名>(<列定义>[{,<列定义>,<表约束>}])
• 表名：
• 列定义：列名、列数据类型、长度、是否允许空值等。
• 定义完整性约束：列约束和表约束
• [CONSTRAINT<约束名>] <约束定义>

约束

• 主码 primary key
• 非空 not null
• 检查 check()
• 唯一 unique
• 默认值 default
• 补码 foreign key
• 外键 reference

例

Student表要求

列名	数据类型	约束	说明
学号	CHAR(8)	主码	每个学生学号应当非空且唯一
姓名	CHAR(10)	非空	姓名信息必须要保存
性别	CHAR(2)	检查	取“男”或者“女”
年龄	INT	检查	小于10，大于60 的学生年龄无意义
系别	VARCHAR(26)	default	默认值为“计算机系”
身份证号	CHAR(18)	唯一值	每个人的身份证号是唯一

CREATE TABLE Studet{
   s_number char(10) primary key,
   s_name char(10) not null,
   s_sex  char(2) check(s_sex='男' or s_sex='女'),
   s_age  int  check(s_age>10 and s_age<60),
   s_classity varchar(26) default '计算机系',
   s_ID   char(18)  unique,
&#125;

删除表结构 Drop

语法：DROP DATABASE 数据库名 [ , … n ]

• 用SQL删除关系（表）

  • 将整个关系模式（表结构）彻底删除
  • 表中的数据也将被删除

    修改表结构 Alter

• 增加表中的属性

  语法：alter table 表名 add 增加内容 数据类型

  • 向已经存在的表中添加属性
  • allow null （新添加的属性要允许为空）
  • 已有的元组中该属性的值被置为Null

• 修改表中的某属性(某列)

  语法：alter table 表名 alter column 增加内容 修改的数据类型

  • 修改属性类型或精度

• 删除表中的某属性(某列)

  • 去除属性及相应的数据

向表中添加数据(Insert)

语法：INSERT INTO table_name VALUES (value1,value2,value3,…)

• 数据添加
  • 用SQL的插入语句，向数据库表中添加数据
  • 按关系模式的属性顺序
  • 按指定的属性顺序，也可以只添加部分属性（非Null属性为必需）

数据删除（Delete）

语法：DELETE FROM table_name WHERE some_column=some_value

• 只能对整个元组操作，不能只删除某些属性上的值
• 只能对一个关系起作用，若要从多个关系中删除元组，则必须对每个关系分别执行删除命令
• 删除单个元组
• 删除多个元组
• 删除整个关系中的所有数据

数据更新（Update）

语法：UPDATE table_name SET column1=value1,column2=value2,… WHERE some_column=some_value

改变符合条件的某个（某些）元组的属性值

视图 (VIEW)

视图是从一个或者多个表或视图中导出的表，其结构和数据是建立在对表的查询基础上的。和真实的表一样，视图也包括几个被定义的数据列和多个数据行，但从本质上讲，这些数据列和数据行来源于其所引用的表。因此，视图不是真实存在的基础表而是一个虚拟表，视图所对应的数据并不实际地以视图结构存储在数据库中，而是存储在视图所引用的表中。

创建视图

语法：

CREATE VIEW <视图名> [(视图列名表)]

AS

SELECT查询子句

[WITH CHECK OPTION]

CREATE VIEW BookView1
AS
SELECT book_ id, name，author, publish， price
FROM Book
WHERE price>30

视图的更新

数据查询（Select）

数据查询是数据库应用的核心功能

SELECT 的基本结构

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

​ from <数据源> –>来自那些表

​ [where<检索0条件表达式>] –>根据什么条件

​ [group by<分组依据列>]

​ [having<组提取条件>]

​ [order by<排序依据列>]

Select子句——重复元组

• SQL具有包的特性

• Select 子句的缺省情况是保留重复元组（ ALL ），可用 Distinct 去除重复元组

• 去除重复元组:费时

• 需要临时表的支持

  select distinct publish from BOOK -->去重查询
  

  Select子句—— *与属性列表

• 星号 * 表示所有属性

  • 星号 * ：按关系模式中属性的顺序排列
  • 显式列出属性名：按用户顺序排列

    Select *  from Book
    

    Select子句——更名

    select name as 姓名 from BOOK        -->改名
    

• 为结果集中的某个属性改名

• 使结果集更具可读性

  Where 子句

  Select  * from Book where price>30
  select name from BOOK where name like '%数学'  -->模糊查询
  Select  * from Book where publish in('清华大学出版社'，'高等教育出版社','机械工业出版社')        
  

• 查询满足指定条件的元组可以通过Where子句来实现

• 使where子句中的逻辑表达式返回True值的元组，是符合要求的元组，将被选择出来

• Where 子句——运算符

  • 比较：<、<=、>、>=、=、<> 等
  • 确定范围：
    • Between A and B、Not Between A and B
  • 确定集合：IN、NOT IN
  • 字符匹配：LIKE，NOT LIKE
  • 空值：IS NULL、IS NOT NULL
  • 多重条件：AND、OR、NOT

• Where 子句——Like

  • 字符匹配：Like、Not Like
  • 通配符
  • % —— 匹配任意字符串
  • _ —— 匹配任意一个字符
  • 大小写敏感

• Where 子句——转义符 escape

  From 子句

• 列出将被查询的关系（表）

• From 子句——元组变量

  • 为 From 子句中的关系定义元组变量
  • 方便关系名的引用

• 连接子句

  • 内连接
    • 内连接是指包括符合条件的每个表的记录，也称之为全记录操作。
  • 外连接
    • 外连接是指把两个表分为左右两个表。右外连接是指连接满足条件右侧表的全部记录。左外连接是指连接满足条件左侧表的全部记录。全外连接是指连接满足条件表的全部记录。
    • 左外连接
    • 右外连接
    • 全外连接

      -->多表连接查询
      select Student_Course.* 
      from Student_Course join Student on
      Student_Course.student_number = Student.student_number
      where student_name='张三'
      

Order By子句

select price from BOOK order by price ASC -->排序 ASC 升序 不写默认为升序  DESE 降序

• 指定结果集中元组的排列次序
• 耗时
• ASC升序（缺省）、DESC降序

子查询（Subquery ）

select sc_score from Student_Course
where student_number in(
     select student_number from Student
     where student_name='张三'
     )
     and
     course_name in(
     select course_name from Course
     where course_name='近代史'
 )

• 子查询是嵌套在另一查询中的 Select-From-Where 表达式（Where/Having）
• SQL允许多层嵌套，由内而外地进行分析，子查询的结果作为父查询的查找条件
• 可以用多个简单查询来构成复杂查询，以增强SQL的查询能力
• 子查询中不使用 Order By 子句，Order By子句只能对最终查询结果进行排序
• 子查询——单值比较
  • 返回单值的子查询，只返回一行一列
  • 父查询与单值子查询之间用比较运算符进行连接
    • 运算符：>、>=、=、<=、<、 <>
• 子查询——多值
  • 子查询返回多行一列
  • 运算符：In、All、Some(或Any)、Exists
    • 子查询——多值成员In
      • 若值与子查询返回集中的某一个相等，则返回true
        • IN 被用来测试多值中的成员
    • 子查询——多值比较 ALL
      • 父查询与多值子查询之间的比较用All来连接
      • 值s比子查询返回集R中的每个都大时，s>All R 为True
      • All表示所有
      • all、< all、<=all、>=all、<> all
      • <> all 等价于 not in
    • 子查询——多值比较Some/Any
      • 父查询与多值子查询之间的比较需用Some/Any来连接
      • 值s比子查询返回集R中的某一个都大时返回 Ture
        • s > Some R为True 或
        • s > Any R为True
      • Some(早期用Any)表示某一个（任意一个）
      • some、< some、<=some、>=some、<> some
      • = some 等价于 in、<> some 不等价于 not in
• 子查询——存在判断Exists
  • Exists + 子查询用来判断该子查询是否返回元组
  • 当子查询的结果集非空时，Exists为True
  • 当子查询的结果集为空时，Exists为False
  • 不关心子查询的具体内容，因此用 Select *
  • 具有外部引用的子查询，称为相关子查询（Correlated Queries）
  • 外层元组的属性作为内层子查询的条件

    聚合函数

• 把一列中的值进行聚合运算，返回单值的函数
• 五个预定义的聚合函数
  • 平均值：Avg( )
  • 总和： Sum( )
  • 最小值：Min( )
  • 最大值：Max( )
  • 计数： Count( ) 返回所选列中不为NULL的数
• Group By
  • 将查询结果集按某一列或多列的值分组，值相等的为一组，一个分组以一个元组的形式出现
  • 只有出现在Group By子句中的属性，才可出现在Select子句中
• Having
  • 针对聚合函数的结果值进行筛选（选择），它作用于分组计算结果集
  • 跟在Group By子句的后面。
• Having 与 Where的区别
  • Where 决定哪些元组被选择参加运算，作用于关系中的元组
  • Having 决定哪些分组符合要求，作用于分组
  • 聚合函数的条件关系必须用Having，Where中不应出现聚合函数
• 聚合函数对Null的处理
  • Count：不计
  • Sum：不将其计入
  • Avg：具有 Null 的元组不参与
  • Max / Min：不参与

    索引

    数据库中的索引与书籍中的索引类似，在一本书中，利用索引可以快速查找所需信息，无须阅读整本书。在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。书中的索引是一个词语列表，其中注明了包含各个词的页码。而数据库中的索引是某个表中一列或者若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单

索引的作用

• 通过创建唯一索引，可以保证数据记录的唯一性。
• 可以大大加快数据检索速度。
• 可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。
• 在使用ORDER BY和GROUP BY子句中进行检索数据时，可以显著减少查询中分组和排序的时间。
• 使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能

  聚集索引与非聚集索引

• 聚集索引对表的物理数据页中的数据按列进行排序，然后再重新存储到磁盘上，即聚集索引与数据是混为一体的，它的叶节点中存储的是实际的数据
• 非聚集索引具有完全独立于数据行的结构，使用非聚集索引不用将物理数据页中的数据按列排序。非聚集索引的叶节点存储了组成非聚集索引的关键字值和行定位器

  创建索引

  约束

• 主键约束（primary key constraint）
• 唯一性约束（unique constraint）
• 检查约束（check constraint）
• 缺省约束（default constraint）
• 外部键约束（foreign key constraint）

SQL SERVER权限管理

SQL Server权限管理策略

• 安全帐户认证
  • 安全帐户认证是用来确认登录SQL Server的用户的登录帐号和密码的正确性，由此来验证其是否具有连接SQL Server的权限。 SQL Server 2000提供了两种确认用户的认证模式：
    • （一）Windows NT认证模式。
      • SQL Server数据库系统通常运行在Windows NT服务器平台上，而NT作为网络操作系统，本身就具备管理登录、验证用户合法性的能力，因此Windows NT认证模式正是利用了这一用户安全性和帐号管理的机制，允许SQL Server也可以使用NT的用户名和口令。在这种模式下，用户只需要通过Windows NT的认证，就可以连接到SQL Server，而SQL Server本身也就不需要管理一套登录数据。
    • （二）混合认证模式。
      • 混合认证模式允许用户使用Windows NT安全性或SQL Server安全性连接到SQL Server，这就意味着用户可以使用他的帐号登录到Windows NT，或者使用他的登录名登录到SQL Server系统。NT的用户既可以使用NT认证，也可以使用SQL Server认证
• 访问许可确认
  • 但是通过认证阶段并不代表用户能够访问SQL Server中的数据，同时他还必须通过许可确认。用户只有在具有访问数据库的权限之后，才能够对服务器上的数据库进行权限许可下的各种操作，这种用户访问数据库权限的设置是通过用户帐号来实现的。

    用户权限管理

• 服务器登录帐号和用户帐号管理
  • 1.利用企业管理器创建、管理SQL Server登录帐号
    • （１）打开企业管理器，单击需要登录的服务器左边的“+”号，然后展开安全性文件夹。
    • （２）用右键单击登录（login）图标，从快捷菜单中选择新建登录（new login）选项，则出现SQL Server登录属性—新建登录对话框，如图6-2所示。
    • （3）在名称编辑框中输入登录名，在身份验证选项栏中选择新建的用户帐号是Windows NT认证模式，还是SQL Server认证模式。
    • （４）选择服务器角色页框。在服务器角色列表框中，列出了系统的固定服务器角色。
    • （５）选择用户映射页框。上面的列表框列出了该帐号可以访问的数据库，单击数据库左边的复选框，表示该用户可以访问相应的数据库以及该帐号在数据库中的用户名。
    • （６）设置完成后，单击“确定”按钮即可完成登录帐号的创建。
  • 使用SQL 语句创建登录帐号
  • 2.用户帐号管理
    • 在数据库中，一个用户或工作组取得合法的登录帐号，只表明该帐号通过了Windows NT认证或者SQL Server认证，但不能表明其可以对数据库数据和数据库对象进行某种或者某些操作，只有当他同时拥有了用户权限后，才能够访问数据库。
    • 利用企业管理器可以授予SQL Server登录访问数据库的许可权限。使用它可创建一个新数据库用户帐号
• 许可（权限）管理
  • 许可用来指定授权用户可以使用的数据库对象和这些授权用户可以对这些数据库对象执行的操作。用户在登录到SQL Server之后，其用户帐号所归属的NT组或角色所被赋予的许可（权限）决定了该用户能够对哪些数据库对象执行哪种操作以及能够访问、修改哪些数据。在每个数据库中用户的许可独立于用户帐号和用户在数据库中的角色，每个数据库都有自己独立的许可系统，在SQL Server中包括三种类型的许可：即对象许可、语句许可和预定义许可。
    • 三种许可类型
      • 1、对象许可
        • 表示对特定的数据库对象，即表、视图、字段和存储过程的操作许可，它决定了能对表、视图等数据库对象执行哪些操作。
      • 2、语句许可
        • 表示对数据库的操作许可，也就是说，创建数据库或者创建数据库中的其它内容所需要的许可类型称为语句许可。
      • 3、预定义许可
        • 是指系统安装以后有些用户和角色不必授权就有的许可。
• 角色管理
  • 角色是SQL Server 7.0版本引进的新概念，它代替了以前版本中组的概念。利用角色，SQL Server管理者可以将某些用户设置为某一角色，这样只对角色进行权限设置便可以实现对所有用户权限的设置，大大减少了管理员的工作量。SQL Server提供了用户通常管理工作的预定义服务器角色和数据库角色。
    • 1、服务器角色
      • 服务器角色是指根据SQL Server的管理任务，以及这些任务相对的重要性等级来把具有SQL Server管理职能的用户划分为不同的用户组，每一组所具有的管理SQL Server的权限都是SQL Server内置的，即不能对其进行添加、修改和删除，只能向其中加入用户或者其他角色。
      • 几种常用的固定服务器角色
        • 系统管理员：拥有SQL Server所有的权限许可。
        • 服务器管理员：管理SQL Server服务器端的设置。
        • 磁盘管理员：管理磁盘文件。
        • 进程管理员：管理SQL Server系统进程。
        • 安全管理员：管理和审核SQL Server系统登录。
        • 安装管理员：增加、删除连接服务器，建立数据库复制以及管理扩展存储过程。
        • 数据库创建者：创建数据库，并对数据库进行修改。
    • 2、数据库角色
      • 数据库角色是为某一用户或某一组用户授予不同级别的管理或访问数据库以及数据库对象的权限，这些权限是数据库专有的，并且还可以使一个用户具有属于同一数据库的多个角色。SQL Server提供了两种类型的数据库角色：即固定的数据库角色和用户自定义的数据库角色。
      • （１）固定的数据库角色
        • public：维护全部默认许可。
        • db_owner：数据库的所有者，可以对所拥有的数据库执行任何操作。
        • db_accessadmin：可以增加或者删除数据库用户、工作组和角色。
        • db_addladmin：可以增加、删除和修改数据库中的任何对象。
        • db_securityadmin：执行语句许可和对象许可。
        • db_backupoperator：可以备份和恢复数据库。
        • db_datareader：能且仅能对数据库中的任何表执行select操作，从而读取所有表的信息。
        • db_datawriter：能够增加、修改和删除表中的数据，但不能进行select操作。
        • db_denydatareader：不能读取数据库中任何表中的数据。
        • db_denydatawriter：不能对数据库中的任何表执行增加、修改和删除数据操作。
      • （２）用户自定义角色
        • 创建用户定义的数据库角色就是创建一组用户，这些用户具有相同的一组许可。如果一组用户需要执行在SQL Server中指定的一组操作并且不存在对应的Windows NT组，或者没有管理Windows NT用户帐号的许可，就可以在数据库中建立一个用户自定义的数据库角色。用户自定义的数据库角色有两种类型：即标准角色和应用程序角色。

          Transaction_SQL 语句

• 赋权语句——Grant
• 收回权限——Revoke
• 收回权限——Deny