SQL整理
1.创数据库
create table Tteacher
(
t_id int not null ,
t_name char(10) not null,
t_classesID int not null,
t_age int ,
t_birth datetime,
primary key (t_id), /*主键约束*/
/*
外键约束
constraint 约束名称
foreign key (外键)
references 对应表
*/
constraint t_classes_teacher
foreign key (t_classesID)
references Tclasses,
)
create table Tclasses
(
t_id int primary key not null,
t_name char(10) not null ,
t_school char(10) default '牛津大学',
)
create table Tstudents
(
t_id int not null,
t_name char(10) not null,
t_classesID int not null,
t_motto varchar(200) not null, /*格言*/
t_age int,
/*
也可以这样写：
t_id int not null primary key,
t_motto varcher(200) not null unique,
*/
primary key (t_id),
constraint t_classes_students
foreign key (t_classesID)
references Tclasses,
unique (t_motto), /*唯一约束*/
/*
检验约束:年龄在20到25岁之间
*/
constraint t_check_age
check(t_age <=25 and t_age >=20),
)
create table Tteacher
(
t_id int not null ,
t_name char(10) not null,
t_classesID int not null,
t_age int ,
t_birth datetime,
primary key (t_id), /*主键约束*/
/*
外键约束
constraint 约束名称
foreign key (外键)
references 对应表
*/
constraint t_classes_teacher
foreign key (t_classesID)
references Tclasses,
)
create table Tclasses
(
t_id int primary key not null,
t_name char(10) not null ,
t_school char(10) default '牛津大学',
)
create table Tstudents
(
t_id int not null,
t_name char(10) not null,
t_classesID int not null,
t_motto varchar(200) not null, /*格言*/
t_age int,
/*
也可以这样写：
t_id int not null primary key,
t_motto varcher(200) not null unique,
*/
primary key (t_id),
constraint t_classes_students
foreign key (t_classesID)
references Tclasses,
unique (t_motto), /*唯一约束*/
/*
检验约束:年龄在20到25岁之间
*/
constraint t_check_age
check(t_age <=25 and t_age >=20),
)
2.去除重复字段
select distinct t_age from Tteacher ;
select distinct t_age from Tteacher ;
3.增加数据
insert into Tclasses(t_id,t_name)values(1,'网络一班');
insert into Tteacher values(1,'刘教授',1,23,'1988-12-12');
insert into Tteacher values(2,'赖教授',1,23,'1987-12-12');
insert into Tteacher values(3,'邓教授',1,22,'1986-12-12');
insert into Tteacher values(4,'黄教授',1,22,'1985-12-12');
insert into tstudents values(1,'刘XX',1,'没有格言'，22);
insert into tstudents values(2,'XXX',1,'也没有格言',20);
4.更新数据
update Tteacher set t_name = '刘教授' where t_id =1;
update tstudents set t_age = convert(int,rand()*10+20)
5.修改表属性
a.增加字段
alter table tstudents
add t_birthday datetime
alter table tstudents
add t_sex char(2)
b.删除字段
c.修改约束
alter table tstudents
drop constraint t_check_age
alter table tstudents
add constraint t_check_age
check(t_age <=30 and t_age >=20)
6.创建索引
索引优点：如果要查询的字段设有索引，那么会加快查询速率。
索引缺点：如果update或者insert设有索引的字段，也要更新索引，这样对update或者insert有着额外的开销。
另外还消耗额外的磁盘空间。
create index t_age_index
on tstudents (t_age)
drop tstudents.index t_age_index
insert into Tclasses(t_id,t_name)values(1,'网络一班');
insert into Tteacher values(1,'刘教授',1,23,'1988-12-12');
insert into Tteacher values(2,'赖教授',1,23,'1987-12-12');
insert into Tteacher values(3,'邓教授',1,22,'1986-12-12');
insert into Tteacher values(4,'黄教授',1,22,'1985-12-12');
insert into tstudents values(1,'刘XX',1,'没有格言'，22);
insert into tstudents values(2,'XXX',1,'也没有格言',20);
4.更新数据
update Tteacher set t_name = '刘教授' where t_id =1;
update tstudents set t_age = convert(int,rand()*10+20)
5.修改表属性
a.增加字段
alter table tstudents
add t_birthday datetime
alter table tstudents
add t_sex char(2)
b.删除字段
c.修改约束
alter table tstudents
drop constraint t_check_age
alter table tstudents
add constraint t_check_age
check(t_age <=30 and t_age >=20)
6.创建索引
索引优点：如果要查询的字段设有索引，那么会加快查询速率。
索引缺点：如果update或者insert设有索引的字段，也要更新索引，这样对update或者insert有着额外的开销。
另外还消耗额外的磁盘空间。
create index t_age_index
on tstudents (t_age)
drop tstudents.index t_age_index
7.批量插入
declare @i int ;/*定义变量*/
declare @t1 datetime;
declare @t2 datetime;
set @i =8; /*设置初始值*/
set @t1 = getdate();
while @i<=10000 /*循环插入*/
begin
insert into Tstudents values(@i,@i,1,@i,20,'1988-11-11','男');
set @i =@i+1;
end
set @t2 = getdate();
select @t1 as t1,@t2 as t2;
8.取随机，四舍五入
rand(): 0到1之间的小数
round(rand()*10+20,0): 介于20到30之间的整数。
9.类型转换
cast(23.0 as int) => 23
cast(23.8 as int) => 23
convert(int,23.0) => 23
convert(int,23.8) => 23
declare @i int ;/*定义变量*/
declare @t1 datetime;
declare @t2 datetime;
set @i =8; /*设置初始值*/
set @t1 = getdate();
while @i<=10000 /*循环插入*/
begin
insert into Tstudents values(@i,@i,1,@i,20,'1988-11-11','男');
set @i =@i+1;
end
set @t2 = getdate();
select @t1 as t1,@t2 as t2;
8.取随机，四舍五入
rand(): 0到1之间的小数
round(rand()*10+20,0): 介于20到30之间的整数。
9.类型转换
cast(23.0 as int) => 23
cast(23.8 as int) => 23
convert(int,23.0) => 23
convert(int,23.8) => 23
10.视图
视图优点：
(1)安全性：每个用户仅可以通过一组少量的视图来访问数据库，因此限制了用户访问存储的数据。
(2)简化查询：视图能够从许多不同的表中提取数据，并且用单个表呈现提取的结果，这样就把多表查询变成了针对视图的单表查询了。
(3)简化结构：视图为用户提供了个性化的数据库结构的视觉，将数据库呈现为用户感兴趣的虚表。
(4)隔离变化：视图能表示数据库结构一致的、不变的映像，即使底层的数据源表已经拆分、重新构造或者重新命名也是如此。
(5)数据完整性：如果通过视图来访问和输入数据，DBMS会自动地检验该数据，以确保数据满足所规定的完整性约束。
视图缺点：
(1)性能：DBMS必须将基于视图的查询转换成对底层源表的查询。如果视图由复杂多表查询所定义，那么即使是一个基于视图的简单查询，也编程了复杂的联接，可能要花很长时间来完成。
(2)更新限制：复杂的视图是只读的，无法更新。
create view view_tstudents as
select * from tstudents where t_age = 20;
视图优点：
(1)安全性：每个用户仅可以通过一组少量的视图来访问数据库，因此限制了用户访问存储的数据。
(2)简化查询：视图能够从许多不同的表中提取数据，并且用单个表呈现提取的结果，这样就把多表查询变成了针对视图的单表查询了。
(3)简化结构：视图为用户提供了个性化的数据库结构的视觉，将数据库呈现为用户感兴趣的虚表。
(4)隔离变化：视图能表示数据库结构一致的、不变的映像，即使底层的数据源表已经拆分、重新构造或者重新命名也是如此。
(5)数据完整性：如果通过视图来访问和输入数据，DBMS会自动地检验该数据，以确保数据满足所规定的完整性约束。
视图缺点：
(1)性能：DBMS必须将基于视图的查询转换成对底层源表的查询。如果视图由复杂多表查询所定义，那么即使是一个基于视图的简单查询，也编程了复杂的联接，可能要花很长时间来完成。
(2)更新限制：复杂的视图是只读的，无法更新。
create view view_tstudents as
select * from tstudents where t_age = 20;
11.分组查询
select count(*),t_classesID from tstudents group by t_classesID
select count(*),t_classesID from tstudents group by t_classesID having count(*) >=50000
select count(*),t_classesID from tstudents group by t_classesID
select count(*),t_classesID from tstudents group by t_classesID having count(*) >=50000
12.union(或)查询
使用限制
(1)两个表必须包含同样数目的字段
(2)第一个表中的数据类型必须与第二个表中对应数据类型相同
(3)两个表都不能用order by 子句排列。然而，组合后的查询结果可以排序
查询ID为10或者ID为100的学生：
select * from tstudents where t_id =10
union
select * from tstudents where t_id =100
使用限制
(1)两个表必须包含同样数目的字段
(2)第一个表中的数据类型必须与第二个表中对应数据类型相同
(3)两个表都不能用order by 子句排列。然而，组合后的查询结果可以排序
查询ID为10或者ID为100的学生：
select * from tstudents where t_id =10
union
select * from tstudents where t_id =100
13.多表查询
select * from Tteacher where t_classesID =(select t_id from tclasses where t_id =(select t_classesID from Tstudents where t_motto = '1234'))
select * from tstudents where t_classesID =(select t_classesID from tteacher where t_name = '聂教授')
select * from Tteacher where t_classesID =(select t_id from tclasses where t_id =(select t_classesID from Tstudents where t_motto = '1234'))
select * from tstudents where t_classesID =(select t_classesID from tteacher where t_name = '聂教授')
14.汇总查询
avg():求平均值
sum():求总和
min():最小值
max():最大值
count():计算字段中值的数目
count(*): 总记录数
avg():求平均值
sum():求总和
min():最小值
max():最大值
count():计算字段中值的数目
count(*): 总记录数
15.存储过程
sql语句执行的时候要先编译，然后执行。存储过程就是编译好了的一些sql语句。应用程序需要用的时候直接调用就可以了，所以效率会高。
存储过程介绍
存储过程是由流控制和SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，
应用程序使用时只要调用即可。在ORACLE中，若干个有联系的过程可以组合在一起构成程序包。
使用存储过程有以下的优点：
* 存储过程的能力大大增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的 运算。
* 可保证数据的安全性和完整性。
* 通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全。
* 通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。
* 再运行存储过程前，数据库已对其进行了语法和句法分析，并给出了优化执行方案。
这种已经编译好的过程可极大地改善SQL语句的性能。由于执行SQL语句的大部分工作已经完成，所以存储过程能以极快的速度执行。
* 可以降低网络的通信量。
* 使体现企业规则的运算程序放入数据库服务器中，以便：
* 集中控制。
* 当企业规则发生变化时在服务器中改变存储过程即可，无须修改任何应用程序。
企业规则的特点是要经常变化，如果把体现企业规则的运算程序放入应用程序中，
则当企业规则发生变化时，就需要修改应用程序工作量非常之大（修改、发行和安装应用程序）。
如果把体现企业规则的 运算放入存储过程中，则当企业规则发生变化时，只要修改存储过程就可以了，
应用程序无须任何变化。
sql语句执行的时候要先编译，然后执行。存储过程就是编译好了的一些sql语句。应用程序需要用的时候直接调用就可以了，所以效率会高。
存储过程介绍
存储过程是由流控制和SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，
应用程序使用时只要调用即可。在ORACLE中，若干个有联系的过程可以组合在一起构成程序包。
使用存储过程有以下的优点：
* 存储过程的能力大大增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的 运算。
* 可保证数据的安全性和完整性。
* 通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全。
* 通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。
* 再运行存储过程前，数据库已对其进行了语法和句法分析，并给出了优化执行方案。
这种已经编译好的过程可极大地改善SQL语句的性能。由于执行SQL语句的大部分工作已经完成，所以存储过程能以极快的速度执行。
* 可以降低网络的通信量。
* 使体现企业规则的运算程序放入数据库服务器中，以便：
* 集中控制。
* 当企业规则发生变化时在服务器中改变存储过程即可，无须修改任何应用程序。
企业规则的特点是要经常变化，如果把体现企业规则的运算程序放入应用程序中，
则当企业规则发生变化时，就需要修改应用程序工作量非常之大（修改、发行和安装应用程序）。
如果把体现企业规则的 运算放入存储过程中，则当企业规则发生变化时，只要修改存储过程就可以了，
应用程序无须任何变化。
创建存储过程：
(1)
create procedure select_tstudents
as
begin
select * from tstudents
end
(2)
create procedure select_tteacher(
@t_id int,
@t_name char(20)
)as
begin
select * from tteacher where t_id = @t_id or t_name = @t_name
end
注:括号可要可不要
Transact-SQL中执行存储过程：
(1)不待参数的存储过程：exec select_tstudents
(2)带参数的存储过程：exec select_tteacher 4,'刘教授'
查看存储过程：exec sp_helptext @objname = select_tstudents
重命名存储过程：exec sp_rename 源存储过程名，新存储过程名
删除存储过程：drop procedure select_id_tstudents
修改存储过程：
alter procedure select_tstudents
as
begin
select t_id from tstudents
end
(1)
create procedure select_tstudents
as
begin
select * from tstudents
end
(2)
create procedure select_tteacher(
@t_id int,
@t_name char(20)
)as
begin
select * from tteacher where t_id = @t_id or t_name = @t_name
end
注:括号可要可不要
Transact-SQL中执行存储过程：
(1)不待参数的存储过程：exec select_tstudents
(2)带参数的存储过程：exec select_tteacher 4,'刘教授'
查看存储过程：exec sp_helptext @objname = select_tstudents
重命名存储过程：exec sp_rename 源存储过程名，新存储过程名
删除存储过程：drop procedure select_id_tstudents
修改存储过程：
alter procedure select_tstudents
as
begin
select t_id from tstudents
end
16.触发器
(1)创建触发器
a.create trigger trigger_tteacher_tclasses
on tclasses
for delete
as
delete tteacher from tteacher t,deleted d where t.t_classesID = d.t_id
b.create trigger trigger_update_tteacher_tclasses
on tclasses
for update
as
update t set t.t_id = i.t_id from tteacher t,deleted d ,inserted i where t.t_id =d.t_id
注：如果有约束行为约束触发器要做的事情，那么起效果的是约束。要想达到触发器的效果，就要把相关约束给删除掉。比如要当删除班级后触发器再执行删除该班级所有教师。但是如果设置了“班级如果存在教师，那么无法删除班级”的约束，触发器则不起作用。
(2)查看触发器：exec sp_helptext 触发器名称
(1)创建触发器
a.create trigger trigger_tteacher_tclasses
on tclasses
for delete
as
delete tteacher from tteacher t,deleted d where t.t_classesID = d.t_id
b.create trigger trigger_update_tteacher_tclasses
on tclasses
for update
as
update t set t.t_id = i.t_id from tteacher t,deleted d ,inserted i where t.t_id =d.t_id
注：如果有约束行为约束触发器要做的事情，那么起效果的是约束。要想达到触发器的效果，就要把相关约束给删除掉。比如要当删除班级后触发器再执行删除该班级所有教师。但是如果设置了“班级如果存在教师，那么无法删除班级”的约束，触发器则不起作用。
(2)查看触发器：exec sp_helptext 触发器名称
