数据库的组成

1)数据类文件：管理数据

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

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

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

 

 

每一个文件有五个属性

1 name:逻辑名称,字符型数据 name=’aaaa111’

标准化命名：数据库名字+_+data(log)+序号（类型）

2 filename:路径

标准化命名：盘符（例如d:\）+逻辑名称+文件类型名

3 size:文件初始大小 size=5mb,

4 maxsize:最大大小 maxsize=20mb或maxsize=unlimited

5 filegrowth：文件增长方式filegrowth=5%或filegrowth=2mb

 

作业1

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

create database xslg
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

数据库是容器

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

加入对象概念

 

 

一、定义基本表结构

CREATE TABLE <表名>

（<列名> <数据类型>[ <列级完整性约束条件> ]

[，<列名> <数据类型>[ <列级完整性约束条件>] ] …

[，<表级完整性约束条件> ] ）；

 

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

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

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

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

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

......

 

2定义合理的约束 constraint

Primary key=unique+ not null 主码约束

Unique 唯一约束

tno char(6) primary key,

pno char(6) unique,

Not null 非空约束

Foreign key 外码约束

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

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

Check约束*

课间作业;写student,course,sc表结构代码

 

修改表结构举例

alter table student

add sbirthday date

* 不要加not null 约束

alter table student

drop column sbirthday



alter table student

alter column sage int



alter table student

alter column sname char(30)  改大不改小



drop table student 删除表结构 慎用

约束对数据输入的限制

1 主码约束

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

3 insert语句的使用

 

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

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

单表查询

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

Select :输出列或属性值

1 指定列 select sno,sname

 

select sno,sname,sage

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

6 带where 限制条件的查询

SELECT Sname,Sdept,sage

FROM Student

WHERE Sage<21

确定范围:要求有明显的上限和下限

* 年龄在20岁和21岁之间

select *

from Student

where Sage between 20 and 21

确定集合:没有明显的上限和下限

select *

from Student

where Sdept in ('艺术系','机械制造')

*空值不是值，是一种状态

匹配串为含通配符的字符串

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

模糊值：姓张的同学

select *

from Student

where Sname like '张%'

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

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

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

假设有一个同学就叫张%

换码字符: \  #  *

SELECT Cno，Ccredit

FROM Course

WHERE Cname LIKE 'DB\_Design' ESCAPE '\‘

ESCAPE '\‘:只取消\后面第一个字符的匹配功能

Order by 排序

select *

from Student

order by sage desc,Sno desc

 

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

select COUNT(distinct sdept) as 'xxx'

from Student



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

from SC

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

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

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

from sc

Group by 分组，常见的按性别，所在系分组且和select语句原始列保持一致

select ssex

from student

group by ssex

 

select ssex,sname

from Student

group by ssex,sname

 

2

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

from sc

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

 

 

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

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

 

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

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

from sc

group by sno

 

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

from sc

group by cno

 

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

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

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

from sc

group by sno

Having COUNT(*)>2  / 查询选修课程数大于2门同学的学号

 

 

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

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

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

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

Where student.sno=sc.sno

 

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

select student.sno,sname

from SC,student

where student.sno=sc.sno and Cno='3'

 

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

select grade,cname

from sc,course

where sc.cno=course.cno and course.cno='3'

 

 

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

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

缺点：讲语法

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

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

 

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

select student.sno,sname

from SC join student

On student.sno=sc.sno

where Cno='3'

 

查询选修了数据结构课程的同学学号和姓名(要求用内连接完成)

select student.sno,sname

from  student join SC join course

on Course.Cno=SC.cno

On student.sno=sc.sno

where Cname='数据结构'

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

1 需要给表起别名以示区别

由于所有属性名都是同名属性，因此必须使用别名前缀

1 求先修课的先修课

select a.cno,b.cpno,a.Cname,b.cname

from Course a,Course b

where a.Cpno=b.cno

 

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

select b.sname,b.ssex

from student a,student b

where a.sdept=b.sdept and a.sname='张三' and b.sname!='张三'

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

外连接与普通连接的区别

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

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

1 内连接

select *

from Student join SC

on Student.Sno=SC.sno

2 左外，右外，完全

1 select *

from Student left join SC

on Student.Sno=SC.sno

2 select *

from Student right join SC

on Student.Sno=SC.sno

3 交叉连接 ，没有连接条件(只有两行)

select *

from Student cross join SC

嵌套查询概述（拆分*）

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

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

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

Where 公共列 in (select 公共列

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

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

1 选修了2号课程同学姓名

select sname

from student

where sno  in

(select sno

from sc

where cno='2')

 

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

select sname

from student

where sno  in

(select sno

from sc

where cno in

(select cno

from Course

where Cname='数据库'))

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

select sname

from student

where sno not in

(select sno

from sc

where cno  in

(select cno

from Course

where Cname='数据库'))

 

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

select sname

from student

where sno in

(select sno

from sc

where cno not in

(select cno

from Course

where Cname='数据库'))

 

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

选修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')

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

select sno ,sname

from student

where sdept = '艺术系' and sage >all

(

select sage

from student

where sdept  = '计算机')

EXISTS谓词：行（元组）的连接存在量词

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

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

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

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

2. NOT EXISTS谓词

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

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

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

Where  (not)  exists ( select  *

1选修了1号课程的同学学号和姓名

select sno,sname

from Student

where exists

(select *

from SC

where SC.Sno=Student.Sno and Cno='1')

 

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

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='数据库'))

 

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

select sname,ssex

from student a

where exists

(select *

from Student b

where a.sdept=b.sdept and b.sname='张三' and a.sname!='张三')

 

 

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

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)

其中红颜色代码是求一共有几门课，在老师环境下就等于7，having语句做进一步筛选，必须选满7门课即全部课程

索引

• 建立索引的目的，加快查询速度
• 谁可以建立索引
  • DBA或 表的属主（即建立表的人）
  • DBMS一般会自动建立以下列上的索引
    • Primary key
    • UNIQUE
• 谁 维护索引
  • DBMS自动完成
• 使用索引
  • DBMS自动选择是否使用索引以及使用哪些索引

语句格式

CREATE [UNIQUE] [CLUSTER] [NONCLUSTER]INDEX <索引名>

ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]…)；

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

nonCLUSTER INDEX 一个表里可以建立多个

 

[例14]为学生-课程数据库中的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表按学号升序和课程号降序建唯一索引

视图view的特点

虚表，是从一个或几个基本表（或视图）导出的表

只存放视图的定义，不存放视图对应的数据

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

语句格式

CREATE  VIEW

<视图名>  [(<列名>  [，<列名>]…)]

AS  <子查询>

[WITH  CHECK  OPTION]；

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

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

[例1]  建立计算机学生的视图。

CREATE VIEW CS_Student

AS

SELECT *

FROM    Student

WHERE  Sdept= '计算机'

 

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

CREATE VIEW CS2_Student

AS

SELECT *

FROM    Student

WHERE  Sdept= '计算机'

with check option  //  二次检查

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

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

insert into  student

values('201230402120','刘芬','女',18,'计算机')

select *

from CS_Student

select *

from CS2_Student

select *

from Student

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

 

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

 

insert into  student

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

select *

from CS_Student 没有姜小丽

select *

from CS2_Student 也没有姜小丽

select *

from Student  有姜小丽

结果

 

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

insert into  cs_student

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

结果：成功

 

insert into  cs2_student

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

结果：失败

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

 

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

1 查

create proc select_student @sno char(12)

as

select *

from Student

where Sno=@sno

exec select_student @sno='201230402101'

2 增

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

 

3 删

create proc delete_sc @sno char(12)

as

delete from sc

where Sno=@sno

exec delete_sc @sno='201230402109'

4 改

create proc update_sc @sno char(12)

as

update  sc

set

Grade=Grade+10

where Sno=@sno

exec update_sc @sno='201230402108'

 

触发器（没有形参没有EXEC调用，满足条件自动触发）

独有临时缓存表，只在触发器中其作用

 

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

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

例1：当在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 fromdeleted语句再从deleted 表获取@sno的值

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

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

 

2 修改sc中学号时，自动修改Student表相应数据

改

注意 改比删复杂，因为如果将03学号改为93 那就有两个值 03是改前属于deleted 表，93是改后 属于

Inserted

 

创建一个合适的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)