数据库基础温习笔记

例子表:学生信息表Student(Sno,Sname,Ssex,Sage,Sdept),主键Sno

          课程信息表Course(Cno,Cname,Cpno,Ccredit),主键Cno

          选课表SC(Sno,Cno,Grade),主键(Sno,Cno)         

★范式基础：

 1.第一范式:属性列不可再分，如属性列“住址”值为"XX市XX路XX号",该列可以再分为“市”、"街道";

 2.第二范式:非主属性完全依赖于主码，如成绩表SC（学生Id，课程Id，成绩，学生姓名）,“学生姓名”部分函数依赖于主码(学生Id,课程Id)，所以SC不满足第二范式

 3.第三范式:不存在传递依赖

 

 

创建数据库:create database "test001"

一、基本表的操作

   1.创建表：

    学生信息表:

//创建

create table Student(
Sno int primary key not null,
Sname nvarchar(10) UNIQUE,
Sage int,
Ssex nchar(2) CHECK(Ssex in ('男','女')),
Ssdept nvarchar(20)
);

//修改属性

alter table Student alter column Ssdept nvarchar(50)

  课程信息表:创建:

create table Course(
Cno int primary key identity(1,1),
Cname nvarchar(50),
Cpno int ,
Ccredit int ,
foreign key (Cpno) references Course(Cno)
)

  选课表:创建:

create table SC(
Sno int ,
Cno int ,
Grade int ,
primary key (Sno,Cno),
foreign key (Sno) references Student(Sno),
foreign key (Cno) references Course(Cno)
)

小贴士:属性列的类型char[n]、varchar[n]、nvarchar[n]区别:

 ①.带var的是可变长度类型,如varchar、nvarchar,这种类型存数据时会根据数据的实际大小分配适合的存储空间，而不可变长度的char类型，每次存储数据时都会分配固定的长度n的存储空间,实际长度小于n时会用空格补齐（所有读取该类型的属性列数据时多用Trim()）

 ②.如果存储中文最好用带n的nvarchar类型(其中n表示Unicode常量，可以解决多语言字符集之间的转换问题
)，nvarchar无论是对一个字母还是对一个中文，都是分配两个字节(所以nvarchar类型最多存储4000个字符，无论中文还是字母),而varchar类型对一个字母是分配一个字节，对一个中文是分配两个字节(所以varchar类型最多存储4000个汉字，8000个字母),varchar也可以存储中文字符，但是如果遇到操作系统是英文操作系统并且对中文字体的支持不全面时, 在SQL Server存储中文字符为varchar就会出现乱码(显示为??)

2.录入数据

insert into Student(Sname,Sage,Ssex,Ssdept) values('kine',15,'女','国际')

3.修改数据

update Student set Sage=20 where Sname='kine'

4.删除数据

delete from Student where Sname='kine'

5.T-SQL中的循环操作

declare @n int --定义变量
select @n=1   --给变量赋初始值
while @n<=100 --循环条件
begin        --开始执行循环体
delete from Table1
where num=@n
select @n=@n+1--变量自增
end     --循环体结束

6.存储过程中使用T-SQL循环

create procedure add100
@a int ,@b int
as
declare @n int
select @n=@a
while @n<=@b
begin
insert Table1(num) values(@n)
select @n=@n+1
end

--执行该存储过程

execute add100 1,100

 二、修改表

     alter table 表名

     add 新列名 数据类型 数据约束

     drop 完整性约束名

     alter column 列名 数据类型

 ①.向Student表增加“入学时间”列，其值类型为日期类型

    alter table  Student add S_entertime DateTime

 ②.将年龄的数据类型由字符型改为整型

    alter table Student alter column Sage int

 ③.增加课程表名称必须取唯一值的约束条件

    alter table Course add UNIQUE(Cname)

三、数据库完整性

  1.实体完整性:主键

  2.参照完整性:外键

  3.用户自定义完整性:列值不为空、列值唯一、列值值域范围、完整性约束

    如:约束属性列值唯一：

     Alter table Studen t  add constraint  c1 unique(Sname)

 

四、数据查询

  1.范围查询:

①查询年龄在20~23岁之间的学生姓名、系别、年龄

Select Sname,Sdept,Sage

 From Student 

Where between 20 and 23

②查询计算机系(CS)、数学系(MA)、信息系(IS)学生的姓名和性别

  Select Sname,Ssex from Student where Sdept in (‘CS’,’’MA,’IS’)

   2.字符匹配：

     [NOT] LIKE ‘匹配串’  ESCAPE ’换码字符’

   ①.查询学号为20222的学生信息

     Select * from Student where Sno like ’20222’

   ②.查询所有姓刘的学生信息

      Select * from Student where Sname like ‘刘%’

   ③.查询姓”欧阳”且全名为3个汉字的学生信息

     Select * from Student where Sname like ‘欧阳_ _’

     小贴士:数据库字符集为ASCII时一个汉字需要两个_，当字符集为GBK时只需要一个_

   ④如果查询字符串本身包含通配符%或 _ ，这时就要用ESCAPE

     如:select * from Course where Cname like ‘DB\_Design’ ESCAPE ‘\’

 3.涉及空值查询

   如:select Sno from SC where Grade IS NULL

   小贴士：这类的’IS’不能用(=)替换

 4. 多重条件查询

用 AND 和 OR可用来联结多个查询条件，AND优先级高于OR，但可以用括号改变优先级

 

5.order by 属性列   ：按照一个或多个属性列的升序(ASC,默认的) 或降序(DESC) 排列，缺省值为升序，对于空值，排序时显示次序依系统而定，可能是升序时最后显示，降序时最先显示。

6.聚集函数：

          Count(dstinct |all 属性列)   统计该属性列的值的个数

          SUM(dstinct |all 属性列)    计算一数值型列值的总和

          AVG(dstinct |all 属性列)    计算一数值型列值的平均值

          MAX(dstinct |all 属性列)    求一数值型列的最大值

          MIN(dstinct |all 属性列)     求一数值型列的最小值

 小贴士：在聚集函数遇到空值时，除了COUNT(*),都跳过空值而只处理非空值，WHERE子句中是不能用聚集函数作为条件表达式。

7.GROUP BY 分组查询

 查询选修了3门以上课程的学生学号

Select Sno from SC group by Sno HAVING COUNT(*) >3

 

五、连接查询

1.等值连接:

 查询每个学生及其选修课的情况

 Select Student.* ,SC.*

From Student,SC

Where Student.Sno=SC.Sno

可能的执行过程:嵌套循环算法的思想：首先在表Student中找到第一个记录，然后从头开始扫描SC表，逐一查找与Student第一条记录的Sno相等的SC记录，找到后就将Student中第一条记录与该记录拼接起来行成结果表中一个元组，SC表查完后，再找Student中第二条记录，然后再从头开始扫描SC，重复上面的操作，知道Student中全部元组处理完毕。

如果在SC表Sno上建立了索引的话，就不用每次全表扫描SC表了，而是根据Sno值通过索引找到相应的SC元组，查找速度更快。

自然连接:(去掉重复列的等值连接)

2.自身连接

  查询每一门课的间接先行课

  Select T1.Cno,T2.Cpno from Course T1,Course T2 Where T1.Cpno=T2.Cno

3.外连接查询：如果把舍去的元组也保留关系中，而在其他属性列上填NULL，那么这种连接就叫外连接

①.左外连接：只保留左边关系表中要舍弃的元组保留。

   查询每个学生及其选修课程的情况:

   select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade

   from Student

   LEFT OUT JOIN SC ON (Student.Sno=SC.Sno) 

 

②.右外连接：类似左外连接,只保留右边关系表中要舍弃的元组保留。

       

六、视图：视图是从基本表导出的虚表，数据库只存放视图的定义，不存放视图对应的数据，这些数据仍然存放在原来的基本表中。

1.建立视图：

 Create View 视图名称

 as

 select子查询

 

2.例子：

create view is_Isstudent

as

select Sno,Sname,Sage

from Student

where Sdept='计算机'

3.视图的更新、删除、插入数据等操作

 

4.视图的用处和优点：

①.降低多表连接查询的逻辑复杂性，简化应用程序：比如两张间接相连的表可能要涉及到第三张表的三表连接查询，如果构造一张视图封装这种复杂关系，对于程序员来说就像在操作两张直接相连的表一样。

②.实现一定的权限控制：可以根据需要，对表中的一部分内容做一个视图，以供一定的角色使用。

③.可以对 UNION 后的记录集排序

    create view A_B

    AS

    select a.id from a

    union

    select b.id from b

   

创建好视图A_B后，可以对该视图进行排序

   select id from A_B order by id

参考http://zhanghao007008.blog.163.com/blog/static/44874122200932293642494/

 

 

七、索引：索引是对数据库表中一列或多列的值进行排序的一种结构，是该列值的集合和相应的指向表中相关记录的存储地址的清单，查询数据时，根据索引快速定位到目标记录，而不必一条一条记录进行检索匹配。

分类：①唯一(Unique)索引:此索引的每一个索引值只对应唯一的数据记录

      ②聚簇(Cluster)索引:该索引项的顺序与表中的记录的物理顺序一致

1.创建索引:Create Unique或Cluster Index  索引名 ON 表名 (列名 ASC或DESC)

2.数据库索引的作用和优点缺点,参考http://blog.csdn.net/pang040328/article/details/4164874

  

 

八、存储过程：

存储过程就是将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来，并且这样的语句是放在数据库中的，还可以根据条件执行不同SQL语句， 那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时，只需调用execute,即可自动完成命令。

1.存储过程的创建:

 create procedure 存储过程名

[参数名称  数据类型及精度 [输入参数][输出参数],参数名称  数据类型及精度  [输入参数][输出参数]……]

as

  SQL语句

2.存储过程的执行：

   execute 存储过程名 参数1、参数2。。。。。。

3.例子：

小贴士: 存储过程的3种传回值: 　　

1.以Return传回整数;2.以output格式传回参数;3.Recordset　　

传回值的区别:output和return都可在批次程式中用变量接收,而recordset则传回到执行批次的客户端中。

 

 ①不带参数的查询功能的存储过程
 create procedure pr_stu
 as
 select * from Student
 go
 execute pr_stu
   
 ②带输入参数的查询功能的存储过程
 create procedure prstu2
 @stuname nvarchar(10)
 as
 select * from Student where Student.Sname=@stuname
 execute prstu2 'lucy'--执行该存储过程
 
 ③带输出参数的查询功能的存储过程
 create procedure prstu3
 @sex varchar(20),@stuSum int output
 as
 select @stuSum=COUNT(*) from Student
 where Ssex='男'
 
 执行该函数
 declare @sexSum int
 execute prstu3 '女',@sexSum output
 print @sexSum
 
 
 ④带输入参数的插入功能的存储过程
   create procedure prstu4
   @name varchar(10),@sex char(2),@age int,@sdept nvarchar(10)
   as
   insert into Student(Sname,Ssex,Sage,Ssdept)
   values(@name,@sex,@age,@sdept)
    
  执行该存储过程
    execute prstu4 'mike','女',16,'艺术'
   
 4.存储过程的优势：

①. 存储过程只在创造时进行编译即可，以后每次执行存储过程都不需再重新编译，而我们通常使用的SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

②. 经常会遇到复杂的业务逻辑和对数据库的操作，这个时候就会用SP来封装数据库操作。当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。可以极大的提高数据库的使用效率，减少程序的执行时间，这一点在较大数据量的数据库的操作中是非常重要的。在代码上看，SQL语句和程序代码语句的分离，可以提高程序代码的可读性。

③.存储过程可以设置参数，可以根据传入参数的不同重复使用同一个存储过程，从而高效的提高代码的优化率和可读性。

④. 安全性高,可设定只有某此用户才具有对指定存储过程的使用权

 九、触发器:触发器是用户定义在数据表上的一类由事件驱动的特殊过程,SQL里面的事件处理，这里的事件主要是指Insert、Update、Delete

1.触发器是与表事件相关的特殊的存储过程，但又不同于存储过程:触发器与存储过程非常相似，触发器也是SQL语句集，两者唯一的区别是触发器不能用EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发（激活）执行。

触发器不同于存储过程，触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称名字而直接调用。

2.创建触发器的语法:

  Create TRIGGER 触发器名称

  before|after 触发的事件

  on 表名

  for each row|statement

  [when 触发条件]

  触发动作体

 

小贴士：①触发事件可以是Insert、DELETE、UPDATE，也可以是这几个事件的组合，如insert or update 。uodate后面还可以有of<触发列,...> ,即进一步指明修改拿些列时会激活触发器。

           ②触发器类型：Ⅰ.行级触发器(for each row),执行SQL时每影响一条记录，触发动作就执行一次。Ⅱ语句级触发器(for each statement),执行完所有语句，影响最终记录后才总共发生一次触发动作。

           ③触发条件:如果定义触发条件，触发器被激活时，只有当触发条件为真时才会执行触发动作体，如果省略when触发条件则触发动作体在触发器激活后立即执行。

           ④触发动作体:可以是PL/SQL过程块，也可以是存储过程的调用

3.例子:为老师表Teacher定义完整性规则"教授的工资不低于10000，如果低于10000自动改为10000"

   create trigger tr_001

    before insert or update

    on  teacher

    for each row

    as begin

    IF(new.Job='教授') AND (new.Sal<10000)

       then new.sal=10000

      END IF

    END;

 

     参考: http://blog.csdn.net/madforg/article/details/7300265

4.触发器的用处:

  ①. 可以监控某些特殊的属性列的值

  ②. 自动执行。触发器在对表的数据作了任何修改（比如手工输入或者应用程序的操作）之后立即被激活。
  ③. 级联更新。触发器可以通过数据库中的相关表进行层叠更改，这比直接把代码写在前台的做法更安全合理。
  ④. 强化约束。触发器可以引用其它表中的列，能够实现比CHECK约束更为复杂的约束。
  ⑤. 跟踪变化。触发器可以阻止数据库中未经许可的指定更新和变化。
5. 强制业务逻辑。触发器可用于执行管理任务，并强制影响数据库的复杂业务规则。

十、事务：事务（Transaction）是并发控制的单位，是用户定义的一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位。就

是多个语句并发执行，要成功一起成功，要失败就全失败（失败后会回滚到执行前的状态），比如银行转帐：SQL1：用户A扣帐，SQL2：用户B加帐，像这样的就要用事物，不能说钱扣掉了，但没转过去，保持双向更改数据。

数据库事务是指由一个或多个SQL语句组成的工作单元，这个工作单元中的SQL语句相互依赖，如果有一个SQL语句执行失败，就必须撤销整个工作单元

1.事务4大属性：
①   原子性(Atomicity):事务是一个完整的操作。
②   一致性（Consistency）：当事务完成时，数据必须处于一致状态。
③   隔离性(Isolation):对数据进行修改的所有并发事务是彼此隔离的。
④   持久性(Durability):事务完成后，它对于系统的影响是永久性的。

如何理解这四大属性，参考http://blog.csdn.net/chosen0ne/article/details/10036775

事务具有原子性，要么不执行，要么全执行

2使用方法：

①开始事务: begin transaction
②提交事务：commit transaction
③回滚事务: rollback transaction

 

3.例子：

create procedure exchangemoney
@idA int ,@idB int ,@num int
as
begin transaction
update bank set moneySum=moneySum-@num where Id=@idA
update bank set moneySum=moneySum+@num where Id=@idB
if @@ERROR<>0
begin rollback transaction
return 0
end
else
begin commit transaction
return 1
end

 

执行该存储过程:
execute exchangemoney 1,2,1000

参考http://www.cnblogs.com/zhuifengnianshao/archive/2010/11/24/1886939.html

 小贴士：全局变量@@ERROR是返回与它 最近的SQL语句的错误码，局限于DML语句和select语句，如果执行他们出现错误，则返回一个不等于0的错误码，如果没有出错，则返回0。

十一、游标:游标（cursor）是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。每个游标区都有一个名字。用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理.

1.游标操作:使用游标有四种基本的步骤:声明游标、打开游标、提取数据、关闭游标。
当用OPEN语句打开了游标并在数据库中执行了查询后,您不能立即利用在查询结果集中的数据。您必须用FETCH语句来取得数据。一条FETCH语句一次可以将一条记录放入程序员指定的变量中。事实上,FETCH语句是游标使用的核心

2.例子:
table1结构如下
id    int
name  varchar(50)

declare @id int
declare @name varchar(50)
declare cursor1 cursor for         --定义游标cursor1
select * from table1               --使用游标的对象(跟据需要填入select文)
open cursor1                       --打开游标

fetch next from cursor1 into @id,@name  --将游标向下移1行，获取的数据放入之前定义的变量@id,@name中

while @@fetch_status=0           --判断是否成功获取数据
begin
update table1 set name=name+'1'
where id=@id                           --进行相应处理(跟据需要填入SQL文)

fetch next from cursor1 into @id,@name  --将游标向下移1行
end

close cursor1                   --关闭游标
deallocate cursor1

参考:http://www.cnblogs.com/moss_tan_jun/archive/2011/11/26/2263988.html