常用SQL语句培训.ppt

常用SQL语句,2007-12-4,1,查询语句,select * from class;查询表class中的所有列. select class,teacher from class;查询表class中的列class,teacher Select 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 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 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 class order by studentnumber (asc,desc) :对查询结果排序,可以按升序,也可以按降序.,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% 整型,日期类型的字段可以指定范围.用between select * from student where born between 1980-05-04 and 2983-10-18 select * ,12 from student 返回结果中增加一列,且值均为12. select RTRIM(class)+RTRIM(teacher) AS name1,studentnumber from class :将两个字段先删除尾部的空格,再连接起来返回. 其中:连接起来的字段名称返回时为name1 Select class.*,student.* from class,student where class.class=student.class :两个表的内容联合起来查询,字段 也可以用JOIN子句实现:select * from class JOIN student on class.class=student.class JOIN又分为内连接,外连接,左外连接,右外连接等,具体请查看相关的数据库的手册.,3,插入,修改,删除语句,插入: 指定字段名称及值的插入 Insert into class (class,studentnumber,teacher) values(gaoer,55,abc); 不指定字段名称的插入Insert into class values(chuyi,abc,55); 一次插入多条记录:只能执行多条insert语句. 从另一个表中读出数据插入当前的表先创建一个新表:select * into class_bak from class where 1=2 insert 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 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_name INFORMIX: dbschmea SQLSERVER:,5,修改表结构,删除表,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_bak,6,索引,用于在查询数据时,加快查询速度, create UNIQUE index idx1_class on class(class) 在字段class上创建唯一索引,即插入时,该字段不允许重复.名称为idx_class insert into class (class,teacher) values(gaoer,lisi); 此插入语句执行第二次将会失败. 删除索引: drop index idx1_class on class 索引的重新生成,重新组织:当一个表经过多次修改,插入,删除后,需要重建索引,以便以后可提高查询效率.但重建的过程是较为消耗资源的.在SCP等业务繁忙的机器上,一定要选择合适的时间段才能重建索引. ALTER INDEX idx_class on class REBUILD 重新生成索引将会删除并重新创建索引。这将根据指定的或现有的填充因子设置压缩页来删除碎片、回收磁盘空间，然后对连续页中的索引行重新排序。 ALTER INDEX idx_class on class REORGANIZE 使用最少系统资源重新组织索引。,7,索引使用技巧与数据库性能调优.,查询语句中,对于字符串型的字段,一定要用单引号括起来,对于整型不要用引号,对于日期型的字段,目前暂无定论,是否会对性能有影响.可进一步跟踪. 尽量不要用in子句,那样的话无法使用索引,导致查询效率低下,可以改用UNION来连接多个查询.这样可以提高效率. 多个查询条件,应该将过滤多的带索引的字段的条件放在前面. 查询条件中尽量用等号,不要用大于,小于等,那样可能会用不上索引. 在全部查询出所有结果时,要求带上条件1=1,可以避免使用索引,提高效率.,8,创建存储过程,set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go CREATE PROCEDURE dbo.get_avage_age - Add the parameters for the stored procedure here class_name nchar(10), CheckDate datetime AS BEGIN - 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 bornCheckDate - Insert statements for procedure here END,9,执行存储过程,USE gaotest GO DECLAREreturn_value int EXECreturn_value = dbo.get_avage_age class_name = gaosan, CheckDate = 2009-01-01 SELECTReturn Value = return_value GO 删除存储过程: Drop procedure get_avage_age;,10,视图的创建,删除与使用,创建: create view view_1 as SELECT dbo.class.class, dbo.class.studentnumber, dbo.class.teacher, dbo.student.id, dbo.student.name, dbo.student.age, dbo.student.english, dbo.student.born, dbo.student.class AS Expr1 FROM dbo.class INNER JOIN dbo.student ON dbo.student.class = dbo.class.class 使用: select * from view_1 删除:drop view view_1,11,创建触发器,set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go CREATE TRIGGER trigger_1 ON dbo.class AFTER DELETE AS BEGIN - SET NOCOUNT ON added to prevent extra result sets from - interfering with SELECT statements. SET NOCOUNT ON; delete from dbo.student where dbo.student.class not in (select class from dbo.class) - Insert statements for trigger here END,此触发器的功能是:当class表有删除操作时,同步student表中的记录,将不存在的班级的记录全部删除.,12,使用触发器,alter table student drop CONSTRAINT FK_CLASS select * From student where class=chuyi select * From class where class=chuyi delete from class where class=chuyi select * From student where class=chuyi select * From class where class=chuyi 可以看到student表中的记录也被更新了.,13,修改触发器,set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER TRIGGER trigger_1 ON dbo.class AFTER DELETE AS BEGIN - SET NOCOUNT ON added to prevent extra result sets from - interfering with SELECT statements. SET NOCOUNT ON; delete from dbo.student where dbo.student.class not in (select class from dbo.class) - Insert statements for trigger here END,删除触发器:Drop trigger trigger_1,14,约束,约束是在修改一个表,或增加一个表记录时,规定表的各个字段之间必须满足的关系. ALTER TABLE student ADD CONSTRAINT age_negative CHECK (student.age = 0 ); 约束是表结构的一部分内容,此例的约束是检查年龄字段不要为负值. Update student set age=-1;/将会报错. 删除一个约束:alter table student drop constraint age_negative;,15,主键,主键: 表通常具有包含唯一标识表中每一行的值的一列或一组列。这样的一列或多列称为表的主键 (PK)，用于强制表的实体完整性。在创建或修改表时，您可以通过定义 PRIMARY KEY 约束来创建主键。 一个表只能有一个 PRIMARY KEY 约束，并且 PRIMARY KEY 约束中的列不能接受空值。由于 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);在两个字段上建联合主键.,16,外键:,外键 (FK) 是用于建立和加强两个表数据之间的链接的一列或多列。当创建或修改表时可通过定义 FOREIGN KEY 约束来创建外键。 在外键引用中，当一个表的列被引用作为另一个表的主键值的列时，就在两表之间创建了链接。这个列就成为第二个表的外键。 ALTER TABLE student ADD CONSTRAINT FK_class FOREIGN KEY (class) REFERENCES class (class) ; 删除外键:alter table student drop CONSTRAINT FK_CLASS 建立外键时,要注意几点:外键在外表中要是主键或索引. 两个表的此字段名称最好一样.其他注意事项,项目参考相关数据库的联机帮助文档.例如:外键在外表中重新生成,或重新组织后,或外键的索引被删除后,引起的后果.需要仔细调试.,17,使用游标,set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE dbo.get_avage_age - Add the parameters for the stored procedure here class_name nchar(10), CheckDate datetime AS BEGIN - 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 timeCheckDate - Insert statements for procedure here,18,使用游标(续),DECLARE cur_age as int DECLARE avg_age as numeric(8) DECLARE count as int DECLARE curObject CURSOR FOR select age from student where class=class_name and bornCheckDate order by age set avg_age=0 set count=0 OPEN curObject FETCH NEXT FROM curObject INTO cur_age WHILE(FETCH_STATUS=0) BEGIN set avg_age=avg_age+cur_age set count=count+1 FETCH NEXT FROM curObject INTO cur_age END set avg_age=avg_age/count SELECTAVAGE AGE = avg_age,Student Sum = count close curObject deallocate curObject END 在此存储过程中, 使用游标来读取结果集中每个记录的字段值,再求平均值,看看与avg函数的功能是否一样的.,19,使用游标逐条更新表的记录,DECLARE cur_age as int DECLARE avg_age as numeric(8) DECLARE count as int DECLARE curObject CURSOR FOR select age from student where class=class_name and bornCheckDate order by age FOR UPDATE OF age,english set avg_age=0 set count=0 OPEN curObject FETCH NEXT FROM curObject INTO cur_age WHILE(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_age END set avg_age=avg_age/count SELECTAVAGE AGE = avg_age,Student Sum = count close curObject deallocate curObject 根据游标更新表,还有很多选项, 涉及到的细节请具体参考相关数据库的联机帮助!,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 加上指定的额外日期间隔number 产生的新日期 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 int AS BEGIN DECLARE retval int SELECT retval = COUNT(*) FROM class RETURN retval END; 删除一个函数: drop function dbo.CheckFnctn,22,函数的修改,set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER FUNCTION dbo.CheckFnctn() RETURNS int AS BEGIN DECLARE retval int SELECT retval = COUNT(*) FROM class RETURN retval END;,23,函数的使用,可以在增,删,改查,视图,触发器,存储过程,约束,等几乎所有地方使用函数. 以下是在查询语句中使用函数. select dbo.CheckFnctn();,24,事务,一个完整的事务就是其中的每个操作要么都完成,要么都不做.回到事务开始前的状态. begin TRANSACTION ; ALTER INDEX idx_class on class REBUILD; select * from class; commit TRANSACTION ; 需要注意的是,不同的数据库,事务的开始,结束语法可能不一样.例如:在informix中是:begin work和commit;,25,锁,常见的情况是:分为表锁和行锁 加锁与解锁. 排它锁(独占锁)与共享锁. 锁加在 SQL Server 资源上（如在一个事务中读取或修改的行），以防止各种事务并发使用资源。例如，如果一个排它 (X) 锁被一个事务加在某一表的某一行上，在这个锁被释放前，其他事务都不可以修改这一行。,26,在事务内加锁,USE gaotest GO -此句的含义解释起来较麻烦,请参考SQLSERVER的文档. SET TRANSACTION ISOLATION LEVEL REPEATABLE READ GO BEGIN TRANSACTION; GO select * from student with (rowlock) where id=1 waitfor delay 00:00:10 GO COMMIT TRANSACTION; select * from student with (rowlock) where id=1 GO在另一个终端上运行: update student set name=efgh where id=1 运行结果显示:两次查询的内容将会不同. 而更新语句会等到事务结束时,才会得到提交. update student set name=efgh where id=4:此条更新语句会立即得到执行,因为被锁的行不是id=4的记录. 如果rowlock换成TABLOCK或 HOLDLOCK,则第二条语句也会等待. TABLOCKX:是排他锁,其他锁的类型还有,请参考SQLSERVER文档.,