三类完整性规则mysql下规则实现短语_第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/学生-课程.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 方式