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),
 )

2.去除重复字段
 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

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

10.视图
 视图优点：
  (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

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

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 = '聂教授')

14.汇总查询
 avg():求平均值
 sum():求总和
 min():最小值
 max():最大值
 count():计算字段中值的数目
 count(*): 总记录数

15.存储过程
 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

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 触发器名称