数据库笔记

数据库的组成：

数据类文件：

管理数据

1. 主数据文件：有且唯一 .mdf

2. 辅助数据文件：可以有，可以没有，可以有多个 .ndf

3. 日志类文件：备份数据 至少一个 .ldf

文件属性

每一个文件有五个属性：

1. name：逻辑名称,字符型数据
2. filename：路径
3. size：文件初始大小 size=5mb,
4. maxsize：最大大小 size=20mb,size=unlimited,
5. filegrowth：文件增长方式

eg：数据库叫xslg假设他由3个文件组成1个主数据1个辅助文件1个日志文件create database xslg

create database xslg_12
on primary
(name='xslg_dat1',
filename='d:\数据库文件\xslg_dat1.mdf',
size=5mb,
maxsize=50mb,
filegrowth=10%),
(name='xslg_dat2',
filename='d:\数据库文件\xslg_dat2.ndf',
size=5mb,
maxsize=50mb,
filegrowth=10%)
log on
(name='xslg_log1',
filename='d:\数据库文件\xslg_log1.ldf',
size=2mb,
maxsize=20mb,
filegrowth=1mb)
go
----------------------------------------
drop database test1
 
alter database xslg
add file
(name='xslg_dat3',
filename='d：\xslg_dat3.ndf',
size=5mb,
maxsize=50mb,
filegrowth=10%)


alter database xslg
add log file
(name='xslg_log2',
filename='d：\xslg_log2.ldf',

size=2mb,
maxsize=20mb,
filegrowth=1mb)

alter database xslg
remove file xslg_dat3

alter database xslg
remove file xslg_log2

数据库是容器

数据可不可以直接放入容器？不可以

加入对象概念

定义基本表结构

1.创建表

CREATE TABLE <表名>
   （<列名> <数据类型>[ <列级完整性约束条件> ]
   [,<列名> <数据类型>[ <列级完整性约束条件>] ] …
   [,<表级完整性约束条件> ] ）；

列级完整性约束条件：只依赖于一个属性 Sno CHAR(9) PRIMARY KEY，

表级完整性约束条件：依赖于多个属性 PRIMARY KEY (Sno，Cno)，

1定义 表名字，属性名称，数据类型，（数据长度）

CHAR(N)：适用于上下限区别不大的字符数据 学号，身份证号，姓名

varCHAR(N)：适用于上下限区别较大的字符数据 姓名

......

约束 constraint

1.主码约束：Primary key = unique + not null

Tno char(6) primary key,

2.唯一约束：Unique

Pno char(6) UNIQUE,

3.非空约束：NULL

1. Ssex char(4) NOT NULL,
   

4.外码约束：Foreign key

1.表间外码
FOREIGN KEY (Sno) REFERENCES Student(Sno),

2.表内外码 
FOREIGN KEY (Cpno) REFERENCES Course(Cno)

5.检查约束：Check

6.默认约束：Default

约束对数据输入的限制

1. 主码约束
2. 外码约束（重难点）先加主表（码），先删从表（码）

2.修改表

1.修改数据表名

ALTER TABLE OLD_TABLE_NAME RENAME TO NEW_TABLE_NAME;

2.修改列名

ALTER TABLE TABLE_NAME 
RENAME COLUMN OLD_COLUMN_NAME TO NEW_COLUMN_NAME;

3.修改列的数据类型

ALTER TABLE TABLE_NAME
MODIFY COLUMN_NAME NEW_DATATYPE;

4.插入列

ALTER TABLE TABLE_NAME 
ADD COLUMN_NAME DATATYPE;不能填加not null约束

5.删除列

ALTER TABLE TABLE_NAME 
DROP COLUMN COLUMN_NAME;

6.插入数据

insert into Student
values('201230402101','张三','男',20,'计算机')

insert into Student
values('201230402102','张五','男',21,'艺术系'),('201230402103','刘丽','女',20,'艺术系')

7.添加外键

eg：在SC表中添加Sno外键约束

alter table SC
add constraint SC_Sno foreign key (Sno) references Student(Sno)

查询

单表查询：

查询的结果和条件都在同一表中

1、Select 输出列

1.指定列

select sno,sname
from Student

2.全部列

select *
from Student 

3.计算列

SELECT Sname,出生年份=2020-Sage
FROM Student 

等价为

4.换名称

Select sname,2020-Sage as  ‘出生年份’ 
FROM Student 

5.指定显示元组

Distinct top n,top n percent

2、where 查询

1.比较：=, >, <, >=, <=, !=, !>, !<

SELECT Sname,Sdept,sage
FROM Student
WHERE Sage<21

2.确定范围：BETWEEN AND, NOT BETWEEN AND

eg：年龄在20岁和21岁之间

select *
from Student
where Sage between 20 and 21

3.确定集合：IN,not IN

select *
from Student
where Sdept in ('艺术系','机械制造')

（空值不是值，是一种状态）

4.字符匹配：LIKE, NOT LIKE

• %：匹配任意个（可以为0）任意字符 *适合大范围匹配

eg：模糊值：姓张的同学

select *
from Student
where Sname like '张%'

• _：一个__匹配一个任意字符* 用于准确定位

使用换码字符将通配符转义为普通字符

取消匹配符定义，使其恢复为普通字符

eg：假设有一个同学就叫张%

• 换码字符： \ # *

SELECT Cno, Ccredit
FROM Course
WHERE Cname LIKE 'DB_Design' ESCAPE '\‘
ESCAPE '\‘：只取消\后面第一个字符的匹配功能

3、Order by 排序

eg：按性别升序，学号降序

select *
from Student
order by sage asc,Sno desc

4、聚集函数

eg：对原始列（属性）进行简单的数理统计

select COUNT(distinct sdept) as 'xxx'
from Student

eg2：最大成绩，最小成绩

select max(grade) as '最大成绩',min(grade) as '最小成绩'
from SC

*聚集函数在使用时推荐和换名输出合用

COUNT(*) ：统计表中元组的数量

select COUNT(*) as '课程总门数'

from sc

5、Group by 分组

eg：按性别，所在系分组且和select语句原始列保持一致

select ssex
from student
group by ssex

eg2：按性别和姓名分组

select ssex,sname
from Student
group by ssex,sname

eg3：课程总门数

select COUNT(*) as '课程总门数'
from sc

*只有聚集函数时，可以没有group by语句

高级用法

当select语句中既有普通原始列又有聚集函数时，必须和group by合用。其中group by后接普通列

同时聚集函数的含义会发生变化：

既有普通列又有聚集函数时：必须有group by且聚集函数的含义会发生变化

eg：每位学生选课的门数

select sno,COUNT(*) as '每位学生选课的门数'
from sc
group by sno

eg2：每门课程所选的人数

select cno,COUNT(*) as '每门课程所选的人数'
from sc
group by cno

HAVING筛选短语：对聚集函数做进一步筛选

Where语句：对原始列进行筛选

eg：查询选修课程数大于2门同学的学号

select sno,COUNT(*) as '每位学生选课的门数'
from sc
group by sno
Having COUNT(*)>2

连接查询

查询的结果或条件同时涉及多个表的查询

连接条件或连接谓词：用来连接两个表的条件

1、连接谓词法

定义：用where(自然连接=要求有公共属性,要求属性输出不重复的等值连接)适用于初学者*

表1.公共属性=表2.公共属性

eg：查询选修了3号课程的同学的学号和姓名

select student.sno,sname
from SC,student
where student.sno=sc.sno and Cno='3'

eg2：查询选修了3号课程的同学的成绩和课程名称

select grade,cname
from sc,course
where sc.cno=course.cno and course.cno='3'

eg3：查询选修了数据结构课程的同学学号和姓名

select student.sno,sname
from SC,student,course
Where Student.Sno=SC.Sno and Course.Cno=SC.Cno and Cname='数据结构'

2、内连接

定义：表连接条件有on语句(一个ON语句负责一个条件)负责;from中表和表连接用 join

缺点：讲语法

A.from中中间表sc要在中间*

B.注意嵌套结构* 先内后外

eg：查询选修了3号课程的同学的学号和姓名

select student.sno,sname
from SC join student
On student.sno=sc.sno
where Cno='3'

eg2：查询选修了数据结构课程的同学学号和姓名

select student.sno,sname
from student join SC join course
on Course.Cno=SC.cno
On student.sno=sc.sno
where Cname='数据结构'

3、自身连接：

定义：一个表与其自己进行连接

需要给表起别名以示区别，由于所有属性名都是同名属性，因此必须使用别名前缀

eg：求先修课的先修课

select a.cno,b.cpno,a.Cname,b.cname
from Course a,Course b
where a.Cpno=b.cno  

eg2：查询和张三在同一个系的同学学号和性别

select b.sname,b.ssex
from student a,student b
where a.sdept=b.sdept and a.sname='张三' and b.sname!='张三'

作业：查询和张三不在同一个系的同学学号和性别

外连接与普通连接的区别

普通连接操作只输出满足连接条件的元组

外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出，但只能两个表进行连接*

4、内连接

select *
from Student join SC
on Student.Sno=SC.sno

5、左外，右外，完全

左外：只取左边的表

select *
from Student left join SC
on Student.Sno=SC.sno

右外：只取右边的表

select *
from Student right join SC
on Student.Sno=SC.sno

6、交叉连接

特点：没有连接条件(只有两行)

select *
from Student cross join SC

嵌套查询

一个SELECT-FROM-WHERE语句称为一个查询块

将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询

1、用in 或=做查询的连接

Where 公共列 in (select 公共列

In 可以无限替代=，反之则不成立

子查询的结果有且唯一， in可以换成=，反之则不成立

eg：选修了2号课程同学姓名

select sname
from student
where sno in
(select sno
from sc
where cno='2')

eg2：选修了数据库课程同学姓名

select sname
from student
where sno in
(select sno
from sc
where cno in
(select cno
from Course
where Cname='数据库'))

eg3：没有选修数据库课程同学姓名

select sname
from student
where sno not in
(select sno
from sc
where cno in
(select cno
from Course
where Cname='数据库'))

eg4：至少选修了一门不是数据库课程的同学

select sname
from student
where sno in
(select sno
from sc
where cno not in
(select cno
from Course
where Cname='数据库'))

2、带有ANY或ALL谓词的比较类子查询

eg：选修1号课程且比选修3号课程最低分成绩要高的同学学号和姓名

select Student.Sno,sname
from SC join student
on Student.Sno=SC.sno
where Cno='1' and Grade>any
(select grade
from SC
where Cno='3')

eg2：查询比计算系年龄都大的艺术系同学的学号和姓名

select sno ,sname
from student 
where sdept = '艺术系' and sage >all
(select sage
from student 
where sdept = '计算机')

3、EXISTS和NOT EXISTS

EXISTS谓词

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。

• 若内层查询结果非空，则外层的WHERE子句返回真值

• 若内层查询结果为空，则外层的WHERE子句返回假值

由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。

NOT EXISTS谓词

带有NOT EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。

• 若内层查询结果非空，则外层的WHERE子句返回假值

• 若内层查询结果为空，则外层的WHERE子句返回真值

Exists 连接：

1. 不找公共列
2. 在最下层子查询块中写表之间的连接条件
3. 中间表要放最下层子查询块中

eg1：没有选修1号课程的同学学号和姓名

select sno,sname
from Student
where not exists
(select *
from SC
where SC.Sno=Student.Sno and Cno='1')

eg2：选修了数据库课程的同学学号和姓名

select sno,sname
from Student
where exists
(select *
from Course
where exists
(select *
from SC
where SC.Sno=Student.Sno and SC.Cno=Course.Cno and Cname='数据库'))

eg3：和张三同学在同一个系的同学学号和性别

select sname,ssex
from student a
where exists
(select *
from Student b
where a.sdept=b.sdept and b.sname='张三' and a.sname!='张三')

eg4：选修了全部课程的同学学号和姓名

select sno,sname
from Student
where not exists
(select *
from Course
where not exists
(select *
from SC
where SC.Sno=Student.Sno and SC.Cno=Course.Cno ))

或

select student.sno,student.sname ,count(cno) as"选修课程总数"
from student join sc
on sc.sno=student.sno
group by student.sname,student.sno
having count(cno)=(
	select count(distinct cno)
	from course)

这段代码是求一共有几门课

select count(distinct cno)
from course)

集合查询

并操作：UNION

eg：查询选修了1号课程或者2号课程的学生。

select sno
from SC
where Cno = '1'
union
select sno
from SC
where Cno = '2';

交操作：INTERSECT

eg：查询计算机科学系的学生与年龄不大于19岁的学生的交集。

select *
from Student
where Sdept = 'CS'
intersect
select *
from Student
where Sage <= 19;

差操作：EXCEPT

eg：查询计算机科学系的学生与年龄不大于19岁的学生的差集。

select *
from Student
where Sdept = 'CS'
except
select *
from Student
where Sage <= 19;

索引

建立索引的目的：加快查询速度
谁可以建立索引
1、DBA或表的属主(即建立表的人)
2、DBMS一般会自动建立以下列上的索引
①PRIMARY KEY
②UNIQLUE
谁维护索引
1、DBMS自动完成
使用索引
1、DBMS自动选择是否使用索引以及使用哪些索引

建立索引

CREATE [UNIQUE] [CLUSTER] [NONCLUSTER]INDEX <索引名> 
ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]…);

• CLUSTER INDEX（唯一索引）：一个表中有且唯一；必须现建立聚集后建立非聚集；一般在主码上

• CLUSTER INDEX（聚簇索引）：一个表里可以建立多个

eg：为学生-课程数据库中的Student，Course，SC三个表建立唯一索引。

CREATE UNIQUE INDEX Stusno ON Student(Sno);
CREATE UNIQUE INDEX Coucno ON Course(Cno);
CREATE UNIQUE INDEX SCno ON SC(Sno ASC, Cno DESC);

​ Student表按学号升序建唯一索引

​ Course表按课程号升序建唯一索引

​ SC表按学号升序和课程号降序建唯一索引

修改索引

语句格式

ALTER INDEX<旧索引名>RENAME TO<新索引名>

eg：将SC表的SCno索引名改为SCSno

alter index SCno rename to SCSno;

删除索引

语句格式

DROP INDEX <索引名>

eg：删除Student表的Stusname索引

dorp index Stusname

视图

视图view的特点：虚表，是从一个或几个基本表（或视图）导出的表，只存放视图的定义，不存放视图对应的数据

基表中的数据发生变化，从视图中查询出的数据自动也随之改变。反之，若视图数据发生变化且该视图是可更新视图，则其来源的基本表也会自动变动。如果是不可更新视图，则其来源的基本表不会自动变动，但可通过触发器技术来实现变动。

语句格式

CREATE VIEW 
<视图名> [(<列名> [,<列名>]…)]
AS <子查询>
[WITH CHECK OPTION];

组成视图的属性列名：全部省略或全部指定

子查询不允许含有ORDER BY子句和DISTINCT短语

可更新视图

eg1：建立计算机学生的视图。

CREATE VIEW CS_Student
AS 
SELECT *
FROM  Student
WHERE Sdept= '计算机'

eg2：建立计算机学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生 。

CREATE VIEW CS2_Student
AS 
SELECT *
FROM  Student
WHERE Sdept= '计算机'
with check option //  二次检查

例1例2区别在于是否二次检查

eg1：先往基本表内加入一个计算机系同学，看基本表和两个视图内是否有他的数据

insert into student
values('201230402120','刘芬','女',18,'计算机')
select *
from CS_Student
select *
from CS2_Student
select *
from Student 

结果为：三者完全一样，都有刘芬

eg2：再往基本表内加入一个非计算机系同学，看基本表和两个视图内是否有他的数据

insert into student
values('201230402120','姜小丽','女',18,'艺术') 

select *
from CS_Student //没有姜小丽

 select *
 from CS2_Student 也没有姜小丽
 
 select *
 from Student  有姜小丽

eg3：分别往两个视图内添加非计算机学生

 insert into cs_student
 values('201230402122','高新华','男',18,'艺术') 

结果：成功

 insert into cs2_student
 values('201230402123','谢峰','男',18,'艺术')

结果：失败

分析：加了二次检查的视图必须满足WHERE语句的限制条件

不可更新视图

eg：建立一视图view_grade，视图查询的结果包括学生、课程、教师和成绩等信息。

CREATE VIEW view_grade
AS 
SELECT Student.Sno,Student.Sname,Teacher.tno,Teacher.tname,Course.Cno,Course.Cname,SC.Grade
FROM  Student,Teacher,Course,SC
where Student.sno = SC.sno and 
	Teacher.tno = SC.tno and 
	Course.Cno = SC.cno

存储过程

存储过程的增删改查（注意形式参数和EXEC调用）

增

eg：在SC表插入数据

create proc insert_sc @sno char(12),@cno char(4),@grade smallint
as
insert into SC 
values(@sno,@cno,@grade)

exec insert_sc @sno='201230402101',@cno='7',@grade=100

删

eg：根据学号删除SC表的学生

create proc delete_sc @sno char(12)
as
delete from sc
where Sno=@sno

exec delete_sc @sno='201230402109'

改

eg：根据学号修改Student表的性别

create proc update_student @sno char(12), @ssex char(4)
as
update student
set
ssex = @ssex
where Sno=@sno

exec update_student @sno='S01',@ssex = '男'

查

eg：根据学号查找Student表的学生

create proc select_student @sno char(12)
as
select *
from Student
where Sno=@sno

exec select_student @sno='201230402101'

触发器

触发器：没有形参没有EXEC调用，满足条件自动触发，独有临时缓存表，只在触发器中其作用

缓存区：

1. deleted 执行删除操作或修改前值保存在该表

2. inserted 执行插入操作或修改后值保存在该表

后触发器

增

eg：备份表Student_back

create trigger back_insert
on Student after insert
as
declare @Sno char(12), @Sname char(12)
select @Sno = Sno from inserted
select @Sname = Sname from inserted
insert into Student(Sno,Sname)
values(@Sno, @sname)

insert into Student(Sno, Sname)
values('S01', 'rever')

删

eg：当在SC表中删除某个同学数据时，自动删除他在STUDENT表中数据

create trigger sc_delete
on sc after delete
as
declare @sno char(12)
select @sno=sno from deleted
delete from student where sno=@sno

delete from sc where sno='201230402107'

红色触发 将删除的数据送入 deleted 表中，select @sno=sno from deleted语句再从deleted 表获取@sno的值

通用格式 select @xxx=xxx from deleted(inserted)

最后delete from student where sno=@sno自动完成操作

改

eg：注意 改比删复杂，因为如果将S01学号改为S11。（那就有两个值 S01是改前属于deleted 表，S11是改后 属于Inserted）

create trigger SC_update
on SC after update
as
declare @old_sno char(12),@new_sno char(12)
select @old_sno = sno from deleted
select @new_sno = sno from inserted
update Student 
set sno=@new_sno
where sno=@old_sno

update SC
set
sno='S11'
where sno='S01'

前触发器

增

eg：创建一个合适的instead of触发器，实现在一个不可更新视图中插入数据时，所依赖的多个基本表会自动更新。

//创建一个不可更新视图
create view student_sc
as
select student.sno,sname,grade
from student,sc
where student.sno=sc.sno

//在视图中插入数据
insert into student_sc
values('200215168','张三阿',90)

//创建前触发器
create trigger alter_student
on student_sc instead of insert
as
declare @sno char(10),@sname char(8),@grade smallint
select @sno=sno, @sname=sname,@grade=grade from inserted
insert into student values(@sno,@sname,23,'男','cs','2002-8-9')
insert into sc values(@sno,'1',@grade)

eg：创建一个合适的instead of触发器，实现在一个不可更新视图中（要求是聚集函数类型）插入数据时，所依赖的多个基本表会自动更新。

//创建一个聚集函数类型的不可更新视图
create view jjhs
as
select *
from goods
where Gprice > (select min(gprice)
				from goods)

//创建前触发器
create trigger alter_goods
on jjhs instead of insert
as
declare @gno char(12), @gname char(12)
select @gno=gno, @gname = gname from inserted
insert into goods(gno, gname) values(@gno,@gname)

//在视图中插入数据
insert into goods(gno,gname) values('010','faiz') 

select *
from goods

create view view_count
as
select Bprice,count(Bprice) as Count
from Book
group by Bprice

select * from view_count

create trigger alter_price
on view_count instead of insert
as
declare @Bprice char(10),@count int
select @Bprice=Bprice,@count=count
from inserted
insert into Book
values('B11','101','Book10','xx','xxx',@Bprice)

insert into view_count
values(10,1)

删

eg：创建一个合适的instead of触发器，实现在一个不可更新视图中删除数据时，所依赖的多个基本表会自动更新。

create trigger del_Student
on student_sc instead of delete
as
declare @sno char(10)
select @sno=sno from deleted
delete from student 
where Sno = @Sno

delete from student 
where Sno = '12'