SQL第五讲--即将第七周

 

一、数据库的分离和附加

 

二、 用户自定义类型

 

三、完整性约束

 

 

       1、实体完整性，主表中的主码为空，在这个表上插入数据，不插入主码列，最后错误，因为主码列是不能为空的。这就是实体完整性。

       2、参照完整性，主表userID：1、2、3。外表插入userID：3、4。（错误，不能插入）在主表删除userID=3的行（错误，除非设置级联删除） 

       3、约束完整性，grade>1 00&grade<1000, 能插入10000吗？--用户自定义约束。

 

 

四、unique约束 要加约束名称吗？

 

五、alter你前几次作业中的表，

      alter  数据类型、列名……

六、sql查询语句，

      实验三、实验四。（最好用自己的数据库）

      嵌套查询、order by 、老师说越复杂越好，多嵌套select

 

七、明天主要讲T-SQL编程

 

参考：

 

一、

二、

--1、数据库的分离和附加 --分离数据库 --分离数据库需要对数据库有独占访问权限。如果要分离的数据库正在使用当中，则必须先将该数据库设置为 SINGLE_USER 模式以获取独占访问权限，然后才能对其进行分离。 --例如，下面的 ALTER DATABASE 语句将在所有当前用户断开与 AdventureWorks 数据库的连接后获取对该数据库的独占访问权限。 USE master; ALTER DATABASE kuaipan SET SINGLE_USER; GO exec sp_detach_db 'kuaipan','true' --附加数据库 --1 --CREATE DATABASE (Transact-SQL) --如何附加数据库 (SQL Server Management Studio) --sp_attach_db (Transact-SQL) --sp_attach_single_file_db (Transact-SQL) create database kuaipan on ( filename='C:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/DATA/kuaipan_Data.mdf' ) for attach --2（不推荐） --[ @dbname= ] 'dbnam ' --要附加到该服务器的数据库的名称。该名称必须是唯一的。dbname 的数据类型为 sysname，默认值为 NULL。 --[ @filename1= ] 'filename_n' --数据库文件的物理名称，包括路径。filename_n 的数据类型为 nvarchar(260)，默认值为 NULL。最多可以指定 16 个文件名。参数名称从 @filename1 开始，一直增加<mce:script type="text/javascript" src="http://hi.images.csdn.net/js/blog/tiny_mce/themes/advanced/langs/zh.js" mce_src="http://hi.images.csdn.net/js/blog/tiny_mce/themes/advanced/langs/zh.js"></mce:script><mce:script type="text/javascript" src="http://hi.images.csdn.net/js/blog/tiny_mce/plugins/syntaxhl/langs/zh.js" mce_src="http://hi.images.csdn.net/js/blog/tiny_mce/plugins/syntaxhl/langs/zh.js"></mce:script>到 @filename16。文件名列表至少必须包括主文件。主文件中包含指向数据库中其他文件的系统表。该列表还必须包括在数据库分离之后移动的所有文件。 USE master; ALTER DATABASE baby SET SINGLE_USER; GO EXEC sp_attach_db @dbname = N'AdventureWorks', @filename1 = N'C:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/Data/AdventureWorks_Data.mdf', @filename2 = N'C:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/Data/AdventureWorks_log.ldf'; --自定义数据类型 use kuaipan CREATE TYPE SSN FROM varchar(11) NOT NULL ; --重命名列 --以下示例将 SalesTerritory 表中的 TerritoryID 列重命名为 TerrID。 USE kuaipan; EXEC sp_rename 'test.id', 'test','column' GO use kuaipan select * from test select * from [file] select * from [sharedFiles] --实体完整性 insert into [sharedFiles](fileId,readEmail,editEmail,[des]) values ('1','read emails','edit emails','hello,this is description');

 

三、

下面是完整性的验证：

1： 实体完整性

新建表file，主键为fileid。

插入数据的时候不插入fileid。

出现错误，因为主键不能为空。

（实体完整性验证完毕）（实体完整性实体完整性要求每一个表中的主键字段都不能为空或者重复的值。）

2：参照完整性

 

student   主表     主键是id

score      外表     主键是id

主外键的关系FK_score_student

 

现在我向score表中插入数据

（3,90）

插入（5,90）出现错误

原因就是主表中没有学生5。

 

 

现在删除student表中的3号，也出现错误。

原因就是外表中有引用到学生3，先删除score表中的3号才可以删除student表中的3号。

 

上面这两个错误就是不满足参照约束性。

 

 

代码如下：

--参照完整性 use kuaipan create table student ( id int identity(1,1) primary key, name varchar(50) ) use kuaipan create table score ( s_id int primary key, socre decimal(5,2), constraint FK_score_student foreign key (s_id) references student(id) ) insert into student values('hello'); insert into student values('hello'); insert into student values('hello'); insert into student values('hello'); select * from student insert into score values('3','90'); select * from score insert into score values('4','90'); select * from score insert into score values('5','90'); select * from score delete from student where id='3'

 

 

 

3：约束完整性

新建数据类型chengji

新建表score

约束成绩在0到100之间，有两位小数，有效位数是5位。

插入数据90，正确。

插入数据101错误。

 

这个错误就是不满足约束完整性。

代码：

--约束完整性 drop table score drop table student --新建一个数据类型叫做chengji,有效位是5位，小数位是2位 create type chengji from decimal(5,2) create table score ( id int identity(1,1) primary key, point chengji check (point>0 and point<100) ) insert into score values('90'); select * from score insert into score values('101')  

 

四、

create table student ( id int identity(1,1) primary key, ident varchar(20) unique, name varchar(50) constraint unique_name unique (name) ) alter table student drop constraint unique_name insert into student values('130','zhaokai') insert into student values('131','zhaokai') delete from student where ident='131' alter table student add constraint unique_name unique (name) insert into student values('131','zhaokai') delete from student where ident='131' --alter table student --add constraint unique (name)

上面代码的最后的两行是错误的。

 

综上所述：create table 时可以直接上unique，不写名字。

其他时候，只要有constraint就必须有名字。

 

五、

alter自己的表

--alter应用 drop table score drop table student create table student ( id varchar(10) primary key not null, identcard varchar(20), name varchar(50) ) --修改数据类型 alter table student alter column name varchar(40) select * from student --修改列的名字 --1 EXEC sp_rename 'student.id', 's_id','column' select * from student --添加列 alter table student add new varchar(20) select * from student --添加default约束 alter table student add constraint default_name default 'lovers' for name insert into student(s_id,identcard) values('0825122034','130') select * from student --修改表的名字 exec sp_rename 'student','students' select * from students 

 

 

实验三、

实验四、

--1．SELECT语句的基本使用 drop table student create table student ( id int identity(1,1) primary key, name varchar(50) default 'lovers' ) create table score ( s_id int primary key foreign key references student(id), point decimal(5,2) ) insert into student values('lover1') insert into student values('lover2') insert into student values('lover3') insert into student values('lover4') insert into student values('lover5') select * from student insert into score values('1','67.83') insert into score values('2','63.43') insert into score values('3','67.89') insert into score values('4','99.83') insert into score values('5','87.83') select * from score --left join select * from student stu left join score sco on stu.id=sco.s_id select stu.*,sco.point from student stu left join score sco on stu.id=sco.s_id --right join select * from student stu right join score sco on stu.id=sco.s_id select stu.*,sco.point from student stu right join score sco on stu.id=sco.s_id --full join select * from student stu full join score sco on stu.id=sco.s_id select stu.*,sco.point from student stu full join score sco on stu.id=sco.s_id --inner join select * from student stu inner join score sco on stu.id=sco.s_id select stu.*,sco.point from student stu inner join score sco on stu.id=sco.s_id --union select id from student union select s_id from score select id from student union all select s_id from score --嵌套查询 select point from score where s_id=(select id from student where name='lover1') --查询平均成绩 select AVG(point)as '平均成绩' from score