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