常用SQL语句培训.ppt

《常用SQL语句培训.ppt》由会员分享，可在线阅读，更多相关《常用SQL语句培训.ppt（27页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、常用SQL语句2007-12-4查询语句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_number f

2、rom 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,=50,50):返回studentnumber=50(50,=50,50,=50

3、,不等于50):的记录Select*from class where studentnumber50 and teacher=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 cl

4、ass order by studentnumber(asc,desc):对查询结果排序,可以按升序,也可以按降序.1查询语句(续)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:则重复的行不删除.

5、模糊匹配查询:select*from student where name like%ang%整型,日期类型的字段可以指定范围.用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

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

7、into class values(chuyi,abc,55);一次插入多条记录:只能执行多条insert语句.从另一个表中读出数据插入当前的表先创建一个新表: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=gaoerer3创建新表create table dbo.class_ba

8、k(Name nvarchar(25)NOT NULL DEFAULT abc primary 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:4修改表结

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

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

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

12、et ANSI_NULLS ONset QUOTED_IDENTIFIER 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 stu

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

14、束中的列不能接受空值。由于 PRIMARY KEY 约束可保证数据的唯一性，因此经常对标识列定义这种约束。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);在两个字段上建联合主键.15外键:外键(FK)是用于建立和加强两个表数据之间的链接的一列或多列。当创建或修改表时可通过定义 FOREI

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

16、后,引起的后果.需要仔细调试.16使用游标set ANSI_NULLS 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;se

17、lect avg(age)from student where class=class_name and timeCheckDate -Insert statements for procedure here17使用游标(续)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_a

18、ge=0set count=0OPEN curObjectFETCH 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 curObject END在此存储过程中,使用游标来读

19、取结果集中每个记录的字段值,再求平均值,看看与avg函数的功能是否一样的.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 age FOR UPDATE OF age,englishset avg_age=0set count=0OPEN curObjectFETCH N

20、EXT FROM curObject INTO cur_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 cur

21、Object根据游标更新表,还有很多选项,涉及到的细节请具体参考相关数据库的联机帮助!19常用函数:日期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()-函

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

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

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

25、个锁被释放前，其他事务都不可以修改这一行。25在事务内加锁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文档.26