SQL 数据库知识学习总结

一、基本概念
1、每一个二维表称为一个关系；表中的每一行数据称为一个元组，也称为记录；表格中的每一列称为一个属性，有时也称为字段。
2、如果每一个属性集能够唯一地标识一个关系中的元组而又不含有多余的属性，则称该属性集为该关系的候选键。
3、候选键被选用为主键后，便称为关系的主键，也叫做键或者主码。
4、如果关系模式R中的某属性子集不是R的主键，而是另一关系R1的主键，则该属性集是关系R的外键。
5、包含在任何一个候选键中的属性称为主属性，否则称为非主属性。

二、SQL语言—–表的基本操作
1、表的定义：
表的定义语句格式为：
create table <表名>
(<列名1><数据类型>[<列1的完整性约束>][,
<列名2><数据类型>[<列2的完整性约束>],
…,
<列名n><数据类型>[<列n的完整性约束>],
[<表级完整性约束>]]);
其中：
表级完整性约束是一个可选项，用于给出所在表的数据约束条件，有以下几个主要的约束条件。
a、表的主键约束字句，格式如下：
primary key (<主键列名1>)[,<主键列名2>,…,<主键列名n>])
例如大学教学管理数据库中的学习关系SC，可以如下定义：
create table SC
( S# char(9),
C# char(7),
grade smallint default(0),–表示成绩的默认值为0
primary key(S#,C#));
b、表的外键约束字句，格式为：
foreign key (<列名1>) references <表名> (<列名2>)
c、check约束字句。表约束字句中的<值约束条件>不仅可以是一个条件表达式，而且还可以是一个包含select的SQL语句。
2、表的修改
a、修改表名
rename<原表名>to<新表名>
b、增加列
alter table <表名>add<增加的列名><数据类型>；
其中，新增加的列后不能有可选项“[NOT NULL]”,表在增加了一个列后，原来的元组在新增加的属性上的值被定义为空值。
例如给专业表SS增加一个新属性nouse_column，设其数据类型为decimal(8,1)。
alter table SS add nouse_column decimal(8,1);
c、删除列
alter table <表名>drop <删除的列名>[cascade|restrict];
其中，cascade表示所有引用到该列的视图或有关约束一并被删除；restrict表示在没有视图或关系约束引用该列时，它才会被删除，否则拒绝该删除操作。
d、修改列的定义
修改属性列的定义语句只用于修改列的类型和长度，列的名称不能修改。当表中已有数据时，不能缩短列的长度，但可以增加列的长度。语句格式如下：
alter table <表名>modify<列名><新的数据类型及其长度>;
例如：alter table SS modify ssname varchar(40);
e、表的撤销
drop table<表名>[cascade|restrict];
3、数据的插入、修改、删除
a、数据插入
insert into <表名>
[(<列名>)]
values(<值表>)；
其中如果插入的元组中的列个数、顺序与关系R的结构完全相同，可以略写省略项。
b、数据修改
update <表名>
set <列名1>=<表达式1>[,<列名2>=<表达式2>,…,<列名n>=<表达式n>]
[where<条件>]；
其中当无条件时，修改的是所有元组中相应的数据。
c、删除数据
delete from <表名>
[where <条件>]；

三、SQL语言—–数据查询
1、简单查询
select <列名表>
from <表名表>
[where<条件>]
2、聚合函数
count(*) 计算元组的个数
count(列名) 计算某个列中数据的个数
count(distinct 列名) 计算某一列中不同数据值的个数
sum（列名） 计算某一数据列中值的总和
avg（列名） 计算某一列数据值的平均值
min（列名） 求最大
max（列名） 求最小
3、分组查询
select <列名表>
from <表明表>
[where<条件>]
[group by<列名表>
[having <分组条件>]];
例如计算各个学生的平均分数
select S#,avg(grade) as 平均分数
from SC
group by S#;
4、字符串的匹配
下划线_:匹配一个字符
百分号%：匹配长度大于等于零的子字符串
例如查询学生关系S中行李的学生的学号和姓名
select s#,sname
from S
where sname like ‘李%’;
5、高级查询技术
a、多表连接查询
例如查询所有学习了数据结构课（课程号C401001）的学生的学号和姓名。
select s.s#,sname
from s,sc
where s.s#=sc.s# and c#=’C401001’;
b、嵌套查询
在SQL中，如果在一个select语句的where字句中嵌入另外一个select语句，则这种查询称为嵌套查询。where字句中的select语句称为子查询，可以多层嵌套。
例如查询张华同学（学号200401001）所在班级的女同学的基本信息
select *
from s
where class=(select class
from s
where s#=’200401001’) and sex=’女’；
由于嵌套查询是逐层求解，避开了连接查询的笛卡儿运算，所以执行效率较高。
c、查询中的表更名
在嵌套查询中，当同一个关系既出现在父查询中，又出现在子查询中，且子查询的条件涉及父查询的属性时，需要对关系更名。
例如检索考试成绩比该课程平均成绩低的学生的成绩。
select s#,c#,grade
from sc
where grade<(select avg(grade)
from sc as xx
where sc.c#=xx.c#);
d、谓词演算查询
between A and B 某列的数值区间是[A,B]
like 部分字符相等
in 某列的某个值属于集合成员中的一个成员
not in
any 列值满足一个条件即可
all 列值满足子查询中的所有记录

四、视图操作
1、视图的定义
create view<视图名>[<视图列名表>]
as

use jxgl --使用该数据库
go
declare @sex varchar(2)
set @sex='女'
--根据局部变量值进行查询
select s# as 学号,sname as 姓名,sbirthin as 出生年月,placeofb as 籍贯,scode# as 专业编号
from s
where ssex=@sex
go

用select语句多个变量赋值，例如：

declare @name nvarchar(10),@id int  
select @name='张三', @int=1  
update set name=@name from student where id=@id  
---------------------------------------------------
declare @name nvarchar(10),@id int   
select @name='zhangsan',@id=10  
select @name as [name],@id as [id]  
--select语句输出使用是以表格的形式输出  


declare @name nvarchar(10),@id int   
select @name='zhangsan',@id=10  
print @name  
print @id  
--print数据时则是以文本的形式输出，且一条语句只能输出一个变量

2、全局变量
在SQL Server中，全局变量是一组特定的无参函数，其名称以@@开头，由SQL Server系统提供；用户不能定义和赋值全局变量；一般是把全局变量的值赋给局部变量。
3、自定义函数
a、标量函数
create function function_name
(@parameter_name parameter_data_type)
returns scalar_return_data_type
[as]
begin
function_body
return scalar_expression
end
例如创建一个返回今天是一周的第几天的用户自定义标量函数：

create function get_weekday
(@date datetime)
returns int 
as
begin
return datepart(weekday,@date)
end
根据上述自定义函数，可以如下使用：
select dbo.get_weekday(cconvert(datetime,'20110901',101)

b、单语句表值函数
用于返回一个select语句查询结果的表（即以表的形式返回值），相当于一个参数化的视图
create function function_name
(@parameter_name parameter_data_type)
returns table
[as]
return(select sentence)
例如创建一个自定义函数，返回某班的学生关系表

use jxgl
go
create function s_table
(@class1 varchar(7))
returns table
as
return (select * from s where class=@class1)
go

4、存储过程
存储过程被视为独立的数据库对象保存在数据库中，由SQL Server服务器通过过程名来调用它们。存储过程实质是可以多次调用的、实现某个特定任务的代码段，其功能特点如下：
（1）存储过程可以接受输入参数，并以输出参数的形式向调用过程或批处理返回多个值。
（2）存储过程包含T-SQl语言，可以调用其他存储过程或嵌套调用。
（3）可以返回一个状态值，表示成功或者失败，及失败的原因。
存储过程的优点：
（1）增强的代码的复用率和共享性。
（2）减少了网络中数据的流量。因为调用者不需要发送整段T-sql代码。
（3）执行速度高。存储过程只需要编译一次，下次执行时不需要在编译了。
（4）提高系统安全性。
a、创建存储过程
create proc[edure] procedure_name
[@parameter data_type]
as sql_statement
例如创建一个存储过程，根据某学生的学号返回学生的姓名、所学课程的课程名字及成绩

create procedure scg_name
@s_number varchar(9)
as
select sname,cname,grade
from s,sc,c
where s#=@s_number and s.s#=sc.s# and sc.c#=c.c#
go

b、执行存储过程
[execute]
[@return_status=]
procedure_name[ [@parameter=value] [output] ]
其中@parameter是参数，如果该参数是输出参数，需要在后面加上output
例如调用刚刚创建的存储过程如下

use jxgl
execute scg_name '200401003'