第3章：关系数据库标准语言 SQL

第3章：关系数据库标准语言 SQL

基于SQLServer学习使用，与MySQL有略微差别！

3.1、SQL概述

3.1.1、历史

• 1974年，由Boyce和Chamber提出。
• 1975-1979年，在由IBM的San Jose研究室研制的System R上实现，称为Sequel，现在称为SQL (Struceured Query Languang)。
• 1986年10月，美国国家标准局（American National Standard Institute，简称ANSI）的数据库委员会批准了SQL作为关系数据库语言的美国标准。
• 1987年，国际标准组织（International Organization for Standardization，简称ISO）也通过了这一标准。

3.3.2、SQL语言的功能

SQL是一个综合的、通用的、功能极强的关系数据库语言，它具有四个方面的功能：

• 数据定义（Data Definition）
• 数据查询（Data Query）
• 数据操纵（Data Manipulation）
• 数据控制（Data Contro1）

3.3.3、SQL的特点

1、综合统一

• 集DDL、DML、DCL功能于一体。
• 可以独立完成数据库生命周期中的全部活动
  • 建立数据库，定义关系模式，插入数据；
  • 对数据库中的数据进行查询和更新；
  • 数据库重构和维护
  • 数据库安全性、完整性控制等

2、高度非过程化

• 用户只需提出“做什么”，无须告诉“怎么做”，不必了解存取路径。
  存取路径的选择由系统自动完成。

3、面向集合的操作方式

• 一次一集合。

4、以同一种语法结构提供两种使用方式

• SQL是自含式语言
• SQL是嵌入式语言

5、语言简洁，易学易用

• SQL语言完成核心功能只用9个动词，语法接近英语口语。

SQL语言支持数据库的三级模式结构：

• 所有基本表←→模式
• 部分视图和部分基本表←→外模式，
• 所有存储文件←→内模式

3.3.4、基本概念

• 基本表(Base Table)是本身独立存在的表，每个（多个）基本表对应一个存储文件，一个表可以带若干索引。
• **视图(View)**是从一个或多个基本表中导出的表，数据库中只存放视图的定义而不存放视图对应的数据，可以将其理解为一个虚表。用户可以在视图上再定义视图
• 存储文件的逻辑结构组成了关系数据库的内模式。

3.2、学生-课程数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0IcP4N9Z-1588936213936)(http://cdn.rainszj.com/%E5%AD%A6%E7%94%9F-%E8%AF%BE%E7%A8%8B.png)]

student(sno, sname, ssex, sage, sdept)
course(cno, cname, cpno, ccredit)
cpno为外码
sc(sno, cno, grade)
sno为外码，cno为外码

3.3、数据定义

3.3.1、使用数据库

• 创建数据库：create database <数据库名>

CREATE DATABASE test;

• 使用数据库：use <数据库名>

use test;

• 删除数据库：drop database <数据库名>

drop database test;

在SQLServer中，一个正在使用的数据库是不能删除的；

在MySQL中，一个正在使用的数据库是可以删除的；

SQLServer数据库中有哪几种文件组成？

1、主数据文件：*.mdf

• 每个数据库有且只有一个主数据文件，它是数据库和其他数据文件的起点，扩展名一般为 .mdf

2、次数据文件：*.ndf

• 用于存储主数据文件中未存储的剩余资料和数据库对象；
• 一个数据库可以没有次要数据文件，但也可以同时拥有多个次要数据文件；
• 次要数据文件的多少主要根据数据库的大小、磁盘存储情况和存储性能要求而设置；
• 扩展名一般为：.ndf

3、日志文件：*.ldf

• 存储数据库的事务日志信息，当数据库损坏时，管理员使用事务日志恢复数据库；
• 扩展名一般为：.ldf

每个数据库中至少有两个文件：主数据文件、日志文件

3.3.2、SQLServer中的数据类型

• TINYINT：

  • 1byte
  • 0 ~ 255 之间的整型数据

• SMALLINT：

  • 2byte
  • -215~215-1之间的整型数据

• INT：

  • 4byte
  • -231~231-1之间的整型数据

• BIGINT：

  • 8byte
  • -263~263-1之间的整型数据

• REAL ：单精度浮点型

  • 4byte
  • -3.40E+38 ~ 3.40E+38。

• FLOAT：双精度浮点型

  • 8byte
  • -1.79E+308 ~ 1.79E+308。

• DECIMAL( p, s )：固定精度和小数位的数字数据

  • p表示可以存储的十进制数字的最大个数 ，s指表示小数点右边可以存储的十进制数字的个数 。0<=s<=p
  • DECIMAL( 5, 2 )，例如：-999.99 ~ 999.99

• CHAR( n )：长度固定为n个字符的字符串类型

  • n byte， n最大为8000。
  • CHAR( 5 )，例如：‘abcde’, ‘12345’
    • 如果表中的某列的数据类型定义为CHAR( 5 )，用户给的一个值是’abc’，则系统默认在字符串右边以空格填补剩余的位置， 'abc ’ 。

• VARCHAR( n )：长度最大为n个字符的变长字符串类型

  • 占用字节数不固定，n最大为8000。
  • VARCHAR( 5 )，例如：‘abcde’，’123’

• SMALLDATETIME:

  • 4byte
  • 从 1900 年 1 月 1 日到 2079 年 6 月 6 日的日期和时间数据，精确到分钟。

• DATETIME:

  • 8byte
  • 从 1753 年 1 月 1 日到 9999 年 12 月 31 日的日期和时间数据，精确到百分之三秒（或 3.33 毫秒）

• getdate()函数，返回当前日期时间

  • select getdate();

用户定义数据类型：

-- 取值0到100之间的int类型，值不能为空
create rule myrule1
as @value >=0 and @value<=100
create type mytype1 from int not null
exec sp_bindrule myrule, mytype1

-- 取值1980-1-1到1980-12-31之间的datetime类型
create rule myrule2
	as @value >='1980-1-1' and @value<='1980-12-31‘
create type mytype2 from datetime
exec sp_bindrule myrule2, mytype2 

3.3.3、基本表的建立（create）

语法：

create table <表名> (
		<列名> <数据类型>,
    <列名> <数据类型>,
  	……
    <列名> <数据类型>
);

注意：建表时，最后一个列不能加 *逗号（,）*

以下代码在SQLServer中执行可以，列级完整性约束中的 constraint 在MySQL中无法执行！

列级完整性约束：

CREATE TABLE course(
	cno CHAR(1) CONSTRAINT PK_course_cno PRIMARY KEY,
	cname varchar(20) CONSTRAINT UQ_course_cname UNIQUE,
	cpno CHAR(1),
	ccredit SMALLINT
);

表级完整性约束：

CREATE TABLE course (
	cno CHAR(1),
	cname VARCHAR(20),
	cpno CHAR(1),
	ccredit SMALLINT,
	CONSTRAINT PK_course_cno PRIMARY KEY(cno),
	CONSTRAINT UQ_course_cname UNIQUE(cname)
);

• 如果完整性约束条件涉及到该表的多个属性列，完整性约束条件必须定义在表级上
• 如果完整性约束条件只涉及到该表的一个属性列，完整性约束条件既可以定义在列级上也可以定义在表级上。

约束名：

• 每一个约束都有一个名字，称为约束名。
• 约束名要全数据库唯一。
• 在定义约束的时候，如果没有指定名字，系统默认给定一个名字。
• 在定义约束的时候，用户可以显式指定约束名，方法：

constraint <约束名> <具体约束>

常用约束：

• 主码约束：PK_
• 唯一性约束：UQ_（某一列有了唯一性约束之后，在该列只允许一个值为null）
• 默认值约束：DF_（只能定义在列级上）
• 参照完整性约束：FK_
• CHECK约束：CK_

默认值约束举例：

create table student(
	sno char(5),
	sname varchar(20) not null,
	sage smallint constraint DF_student_sage default(20),
	constraint PK_student_sno primary key(sno)
);

参照完整性（外码）约束举例：

CREATE TABLE course (
	cno CHAR(1),
	cname VARCHAR(20),
	cpno CHAR(1),
	ccredit SMALLINT,
	CONSTRAINT PK_course_cno PRIMARY KEY(cno),
	CONSTRAINT UQ_course_cname UNIQUE(cname),
	constraint CK_course_ccredit check(ccredit >0),
	constraint FK_course_cpno foreign key(cpno) references course(cno)
);

注意：外码的数据类型必须和相应的主码的数据类型保持一致

CHECK约束举例：

CREATE TABLE course (
	cno CHAR(1),
	cname VARCHAR(20),
	cpno CHAR(1),
	ccredit SMALLINT,
	CONSTRAINT PK_course_cno PRIMARY KEY(cno),
	CONSTRAINT UQ_course_cname UNIQUE(cname),
	constraint CK_course_ccredit check(ccredit > 0)
);

3.3.4、基本表的修改（add、alter、drop）

add方式：用于增加新的列和完整性约束

语法：

alter table <表名> add <列定义> | <完整性约束定义>

• <列定义> 格式为：<列名> <数据类型> [ null | not null ]
• <完整性约束定义>格式为：constraint <约束名> <具体约束>

添加属性scardid到student，scardid取值不重复

-- 方式一
alter table student add scardid char(18);
alter table stuent add constraint UQ_student_scardid unique(scardid);
-- 方式二
alter table student add scardid char(18) constraint UQ_student_scardid unique;

在sc表中增加完整性约束定义，使grade在0~100之间。

-- 方式一
ALTER TABLE sc ADD CONSTRAINT CK_sc_grade CHECK(grade >= 0 AND grade <= 100);
-- 方式二
ALTER TABLE sc ADD CHECK(grade BETWEEN 0 AND 100);

在course表中为ccredit增加默认约束，约束值为2。

-- SQLServer中
ALTER TABLE course ADD CONSTRAINT DF_course_ccredit DEFAULT(2) FOR ccredit;
-- MYSQL中
ALTER TABLE course MODIFY ccredit SMALLINT DEFAULT 2;

alter方式：只能用于修改某些列定义

语法：

alter table <表名> alter column <列定义>;

• <列定义> 格式为：<列名> <数据类型> [ null | not null ]
• alter 方式只能修改列的定义，不能修改约束，约束只能增加或删除

注意：

• 使用 alter 方式修改列的定义时，不能将含有空值的列的定义修改为 not null

• 不能修改列名

drop方式：用于删除某些列以及某些约束

语法：

alter table <表名> drop column <列名>;
alter table <表名> drop constraint <约束名>;

举例：

-- 根据约束名删除约束
alter table student drop constraint UQ_student_scardid;
-- 删除列
alter table student drop column sdept;

3.3.5、基本表的删除（drop）

语法：

drop table <表名>

注意：

• 在sqlserver中删除基本表后，基本表的定义、表中数据、索引、约束都将被删除。
• 在sqlserver中删除基本表后，由此表导出的视图仍然保留，但用户引用会出错。
• 在sqlserver中，如果有另外一个表的外码参照该表的主码，则不允许删除该基本表。

3.3.6、索引的建立和删除

索引建立的目的：提高查询速度，但会降低插入数据和修改数据的速度

索引的种类：

1、聚集索引

• 表数据按照索引的顺序来存储，也就是说索引项的顺序与表中记录的物理顺序一致；
• 对于聚集索引，节点即存储了真实的数据行，不再有另外单独的数据页；
• 在一张表上最多只能创建一个聚集索引，因为真实数据的物理顺序只能有一种；

2、非聚集索引

• 表数据存储顺序与索引顺序无关；
• 对于非聚集索引，节点包含索引字段值及指向数据页数据行的逻辑指针；

3、唯一索引

• 每一个索引值只对应唯一的数据记录

创建索引：

语法：

create [unique] [clustered] index <索引名> 
	     on <表名>(<列名>[<次序>][,<列名>[<次序>]]…);

注意：

• 一个表中的索引名需要唯一，多个表中的索引名可以重名；
• 一个索引可以建立在该表的一列或者多列上，各列名之间用逗号分隔；
• 用次序指定索引值的排列次序，升序：ASC，降序：DESC，默认是ASC

举例：

create table t2(
	c1 int constraint PK_t2_c1 primary key, -- 自动创建聚集唯一性索引
	c2 int constraint UQ_t2_c2 unique  			-- 自动创建非聚集的唯一性索引
);

-- 在SQLServer中执行失败，因为SQLServer会根据表中的主码默认自动创建一个聚集索引
create clustered index ID_c2 on t2(c2);

删除索引

语法：

drop index <表名>.<索引名>

举例：

create table t1(
	c1 int,
	c2 int
);
-- 创建索引
create clustered index ID_t1_c1 on t1(c1);
-- 删除索引
drop index t1.ID_t1_c1;

索引的有关说明：

• 一个表中可以有多个索引，索引可以提高查询效率，但索引过多耗费空间，且降低了插入、删除、更新的效率。
• 应该在使用频率高的、经常用于连接的列上建索引。
• 可以动态地定义索引，即可以随