常用SQL语句培训.ppt

《常用SQL语句培训.ppt》由会员分享，可在线阅读，更多相关《常用SQL语句培训.ppt（27页珍藏版）》请在三一办公上搜索。

1、常用SQL语句,2007-12-4,1,查询语句,select*from class;查询表class中的所有列.select class,teacher from class;查询表class中的列class,teacherSelect count(*)as count_all from class;返回表class中的总行数给结果集.select sum(studentnumber)as all_student_number from class:返回class表中studentnumber的总数给结果集select avg(studentnumber)as avg_student_num

2、ber from class:返回class中studentnumber的平均值给结果集Select max(studentnumber)as max_studentnumber from class:求某个字段的最大值,最小值.min是最小值.select*from class where studentnumber=(Select max(studentnumber)from class):求的最大值可以作为条件被引用.Select*from class where studentnumber=50(50,=50,50):返回studentnumber=50(50,=50,50 and t

3、eacher=li 两个查询条件用and表示与,or表示或.Select*from class where studentnumber in(44,55):in表示studentnumber为括号中所列的可能值.Select*from class where class in(select class from student):in中的内容可以是另一个查询语句的结果.Select distinct class from student:查询字段的值不重复select*from class order by studentnumber(asc,desc):对查询结果排序,可以按升序,也可以按降

4、序.,2,查询语句(续),select class,count(*)from student group by class:查询结果根据group by 分组.select class,count(*)from student group by class having count(*)=5:对分组的结果再用条件过滤select*from student where id19:UNION:将两个查询语句的查询结果放在一起,如果有重复的行,就删除,如果是UNION ALL:则重复的行不删除.模糊匹配查询:select*from student where name like%ang%整型,日期类

5、型的字段可以指定范围.用betweenselect*from student where born between 1980-05-04 and 2983-10-18select*,12 from student 返回结果中增加一列,且值均为12.select RTRIM(class)+RTRIM(teacher)AS name1,studentnumber from class:将两个字段先删除尾部的空格,再连接起来返回.其中:连接起来的字段名称返回时为name1Select class.*,student.*from class,student where class.class=stud

6、ent.class:两个表的内容联合起来查询,字段也可以用JOIN子句实现:select*from class JOIN student on class.class=student.classJOIN又分为内连接,外连接,左外连接,右外连接等,具体请查看相关的数据库的手册.,3,插入,修改,删除语句,插入:指定字段名称及值的插入Insert into class(class,studentnumber,teacher)values(gaoer,55,abc);不指定字段名称的插入Insert into class values(chuyi,abc,55);一次插入多条记录:只能执行多条ins

7、ert语句.从另一个表中读出数据插入当前的表先创建一个新表:select*into class_bak from class where 1=2insert into class_bak select*from class where class=gaoer修改:Update class set class=gaoerer where class=gaoer删除:Delete from class where class=gaoerer,4,创建新表,create table dbo.class_bak(Name nvarchar(25)NOT NULL DEFAULT abc primary

8、 key,studentnumber int DEFAULT 0);insert into.class_bak(studentnumber)values(23):则插入的值col1的默认值就是abc.复制一个表的结构到一个新表.select*into class_bak from class where 1=2:复制一个表为另一个表,如果不导数据,就将条件置为1=2即可.显示某表的建表语句:ORACLE:desc table_nameINFORMIX:dbschmea SQLSERVER:,5,修改表结构,删除表,Alter table class_bak add monitor varcha

9、r(10);:增加一列Alter table class_bak alter COLUMN montior nvarchar(10):修改一列Alter table class_bak drop COLUMN monitor;:删除一列删除表:Drop table class_bak,6,索引,用于在查询数据时,加快查询速度,create UNIQUE index idx1_class on class(class)在字段class上创建唯一索引,即插入时,该字段不允许重复.名称为idx_classinsert into class(class,teacher)values(gaoer,lis

10、i);此插入语句执行第二次将会失败.删除索引:drop index idx1_class on class索引的重新生成,重新组织:当一个表经过多次修改,插入,删除后,需要重建索引,以便以后可提高查询效率.但重建的过程是较为消耗资源的.在SCP等业务繁忙的机器上,一定要选择合适的时间段才能重建索引.ALTER INDEX idx_class on class REBUILD重新生成索引将会删除并重新创建索引。这将根据指定的或现有的填充因子设置压缩页来删除碎片、回收磁盘空间，然后对连续页中的索引行重新排序。ALTER INDEX idx_class on class REORGANIZE 使用最

11、少系统资源重新组织索引。,7,索引使用技巧与数据库性能调优.,查询语句中,对于字符串型的字段,一定要用单引号括起来,对于整型不要用引号,对于日期型的字段,目前暂无定论,是否会对性能有影响.可进一步跟踪.尽量不要用in子句,那样的话无法使用索引,导致查询效率低下,可以改用UNION来连接多个查询.这样可以提高效率.多个查询条件,应该将过滤多的带索引的字段的条件放在前面.查询条件中尽量用等号,不要用大于,小于等,那样可能会用不上索引.在全部查询出所有结果时,要求带上条件1=1,可以避免使用索引,提高效率.,8,创建存储过程,set ANSI_NULLS ONset QUOTED_IDENTIFIE

12、R ONgoCREATE PROCEDURE dbo.get_avage_age-Add the parameters for the stored procedure hereclass_name nchar(10),CheckDate datetimeASBEGIN-SET NOCOUNT ON added to prevent extra result sets from-interfering with SELECT statements.SET NOCOUNT ON;select avg(age)from student where class=class_name and born

13、CheckDate-Insert statements for procedure hereEND,9,执行存储过程,USE gaotestGODECLAREreturn_value intEXECreturn_value=dbo.get_avage_ageclass_name=gaosan,CheckDate=2009-01-01SELECTReturn Value=return_valueGO删除存储过程:Drop procedure get_avage_age;,10,视图的创建,删除与使用,创建:create view view_1 as SELECT,AS Expr1 FROM db

14、o.class INNER JOIN dbo.student ON=使用:select*from view_1删除:drop view view_1,11,创建触发器,set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoCREATE TRIGGER trigger_1 ON dbo.class AFTER DELETEAS BEGIN-SET NOCOUNT ON added to prevent extra result sets from-interfering with SELECT statements.SET NOCOUNT ON;delete fr

15、om dbo.student where dbo.student.class not in(select class from dbo.class)-Insert statements for trigger hereEND,此触发器的功能是:当class表有删除操作时,同步student表中的记录,将不存在的班级的记录全部删除.,12,使用触发器,alter table student drop CONSTRAINT FK_CLASSselect*From student where class=chuyiselect*From class where class=chuyidelete f

16、rom class where class=chuyiselect*From student where class=chuyiselect*From class where class=chuyi可以看到student表中的记录也被更新了.,13,修改触发器,set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER TRIGGER trigger_1 ON dbo.class AFTER DELETEAS BEGIN-SET NOCOUNT ON added to prevent extra result sets from-interfering w

17、ith SELECT statements.SET NOCOUNT ON;delete from dbo.student where dbo.student.class not in(select class from dbo.class)-Insert statements for trigger hereEND,删除触发器:Drop trigger trigger_1,14,约束,约束是在修改一个表,或增加一个表记录时,规定表的各个字段之间必须满足的关系.ALTER TABLE studentADD CONSTRAINT age_negative CHECK(student.age=0);

18、约束是表结构的一部分内容,此例的约束是检查年龄字段不要为负值.Update student set age=-1;/将会报错.删除一个约束:alter table student drop constraint age_negative;,15,主键,主键:表通常具有包含唯一标识表中每一行的值的一列或一组列。这样的一列或多列称为表的主键(PK)，用于强制表的实体完整性。在创建或修改表时，您可以通过定义 PRIMARY KEY 约束来创建主键。一个表只能有一个 PRIMARY KEY 约束，并且 PRIMARY KEY 约束中的列不能接受空值。由于 PRIMARY KEY 约束可保证数据的唯一性

19、，因此经常对标识列定义这种约束。create table dbo.test6(col1 nvarchar(25)NOT NULL DEFAULT abc,col2 int DEFAULT 23 primary key);首先将原来的主键删除,再建联合主键alter table test6 ADD CONSTRAINT constraint_1 PRIMARY KEY(col1,col2);在两个字段上建联合主键.,16,外键:,外键(FK)是用于建立和加强两个表数据之间的链接的一列或多列。当创建或修改表时可通过定义 FOREIGN KEY 约束来创建外键。在外键引用中，当一个表的列被引用作为另

20、一个表的主键值的列时，就在两表之间创建了链接。这个列就成为第二个表的外键。ALTER TABLE student ADD CONSTRAINT FK_class FOREIGN KEY(class)REFERENCES class(class);删除外键:alter table student drop CONSTRAINT FK_CLASS建立外键时,要注意几点:外键在外表中要是主键或索引.两个表的此字段名称最好一样.其他注意事项,项目参考相关数据库的联机帮助文档.例如:外键在外表中重新生成,或重新组织后,或外键的索引被删除后,引起的后果.需要仔细调试.,17,使用游标,set ANSI_N

21、ULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE dbo.get_avage_age-Add the parameters for the stored procedure hereclass_name nchar(10),CheckDate datetimeASBEGIN-SET NOCOUNT ON added to prevent extra result sets from-interfering with SELECT statements.SET NOCOUNT ON;select avg(age)from student where

22、 class=class_name and timeCheckDate-Insert statements for procedure here,18,使用游标(续),DECLARE cur_age as int DECLARE avg_age as numeric(8)DECLARE count as intDECLARE curObject CURSOR FOR select age from student where class=class_name and bornCheckDate order by ageset avg_age=0set count=0OPEN curObject

23、FETCH NEXT FROM curObject INTO cur_ageWHILE(FETCH_STATUS=0)BEGIN set avg_age=avg_age+cur_age set count=count+1 FETCH NEXT FROM curObject INTO cur_ageENDset avg_age=avg_age/countSELECTAVAGE AGE=avg_age,Student Sum=countclose curObjectdeallocate curObjectEND在此存储过程中,使用游标来读取结果集中每个记录的字段值,再求平均值,看看与avg函数的功

24、能是否一样的.,19,使用游标逐条更新表的记录,DECLARE cur_age as int DECLARE avg_age as numeric(8)DECLARE count as intDECLARE curObject CURSOR FOR select age from student where class=class_name and bornCheckDate order by age FOR UPDATE OF age,englishset avg_age=0set count=0OPEN curObjectFETCH NEXT FROM curObject INTO cur

25、_ageWHILE(FETCH_STATUS=0)BEGIN set avg_age=avg_age+cur_age set count=count+1 update student set english=count where current of curObject FETCH NEXT FROM curObject INTO cur_ageENDset avg_age=avg_age/countSELECTAVAGE AGE=avg_age,Student Sum=countclose curObjectdeallocate curObject根据游标更新表,还有很多选项,涉及到的细节

26、请具体参考相关数据库的联机帮助!,20,常用函数:日期,DAY(date_expression)-函数返回date_expression 中的日期值 select DAY(getdate()MONTH(date_expression)-函数返回date_expression 中的月份值 select month(getdate()YEAR(date_expression)-函数返回date_expression 中的年份值 select YEAR(getdate()DATEADD(,)select DATEADD(yy,1,getdate()-函数返回指定日期date 加上指定的额外日期间隔n

27、umber 产生的新日期GETDATE()-函数以DATETIME 的缺省格式返回系统当前的日期和时间Datepart的含义及缩写:年份 yy、yyyy 季度 qq、q 月份 mm、m 每年的某一日 dy、y 日期 dd、d 星期 wk、ww 小时 hh 分钟 mi、n 秒 ss、s 毫秒 ms,21,函数的创建,CREATE FUNCTION CheckFnctn()RETURNS intAS BEGIN DECLARE retval int SELECT retval=COUNT(*)FROM class RETURN retvalEND;删除一个函数:drop function dbo.

28、CheckFnctn,22,函数的修改,set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER FUNCTION dbo.CheckFnctn()RETURNS intAS BEGIN DECLARE retval int SELECT retval=COUNT(*)FROM class RETURN retvalEND;,23,函数的使用,可以在增,删,改查,视图,触发器,存储过程,约束,等几乎所有地方使用函数.以下是在查询语句中使用函数.select dbo.CheckFnctn();,24,事务,一个完整的事务就是其中的每个操作要么都完成,要么都

29、不做.回到事务开始前的状态.begin TRANSACTION;ALTER INDEX idx_class on class REBUILD;select*from class;commit TRANSACTION;需要注意的是,不同的数据库,事务的开始,结束语法可能不一样.例如:在informix中是:begin work和commit;,25,锁,常见的情况是:分为表锁和行锁加锁与解锁.排它锁(独占锁)与共享锁.锁加在 SQL Server 资源上（如在一个事务中读取或修改的行），以防止各种事务并发使用资源。例如，如果一个排它(X)锁被一个事务加在某一表的某一行上，在这个锁被释放前，其他事

30、务都不可以修改这一行。,26,在事务内加锁,USE gaotestGO-此句的含义解释起来较麻烦,请参考SQLSERVER的文档.SET TRANSACTION ISOLATION LEVEL REPEATABLE READGOBEGIN TRANSACTION;GOselect*from student with(rowlock)where id=1waitfor delay 00:00:10GOCOMMIT TRANSACTION;select*from student with(rowlock)where id=1GO在另一个终端上运行:update student set name=efgh where id=1运行结果显示:两次查询的内容将会不同.而更新语句会等到事务结束时,才会得到提交.update student set name=efgh where id=4:此条更新语句会立即得到执行,因为被锁的行不是id=4的记录.如果rowlock换成TABLOCK或 HOLDLOCK,则第二条语句也会等待.TABLOCKX:是排他锁,其他锁的类型还有,请参考SQLSERVER文档.,