2.1基本SQL语句ppt课件

1、基本SQL语句SQL语言概述SQL是Structured Query Language结构化查询语言的缩写，它是目前使用最广泛的数据库语言，SQL是由IBM发展起来的，后来被许多数据库软件公司接受而成为了业内的一个标准。包括数据定义、数据操纵、数据查询和数据控制功能SQL语言的分类 SQL语言共分为四大类：数据定义语言DDL 用来创建数据库中的各种对象-表、视图、 索引、同义词、聚簇等如： CREATE TABLE/VIEW/INDEX/SYN/CLUSTER 表 视图 索引 同义词 簇 数据控制语言DCL 用来授予或回收访问数据库的某种特权，并控制 数据库操纵事务发生的时间及效果，对数据库实

2、行监视等。如：grant,rollback,commit数据操纵语言DML数据操纵语言DML主要有三种形式：1) 插入：INSERT 2) 更新：UPDATE 3) 删除：DELETE 数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE 子句组成的查询块： SQL核心语句SQL功能动词数据查询SELECT数据定义CREATE、DROP、ALTER数据操纵INSERT、UPDATE、DELETE数据控制GRANT、REVOKEDDL数据定义语言建立数据库的第一步就是定义一些基本表。SQL语言使用CREATE TABLE命令定义基本表。1定义基本表CREATE (列级完整性约

3、束 ,列级完整性约束. ,)如果完整性约束涉及到改表的多个字段，则必须定义在表的级上，否则可以定义在列级上也可以定义在表级上新建表例子例如建立一个学员表student,包括学号sno,姓名sname,性别ssex,年龄sage,所在系sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名的取值也唯一。CREATE TABLE student ( sno char(5) not null unique, sname char(20) unique, ssex char(1) default 男, sage int, sdept char(15);字段常用数据类型定义表的各各属性需要指明数

4、据类型及长度。不同的数据库系统支持的数据类型不安全相同，常用的类型有：Charn） 长度为n的定长字符串；Varcharn最大长度为n的变长字符串；INT 整形Double 浮点型DATETIME 日期型text 可变长度的Unicode数据，最大长度 230 - 1 image 可变长度的二进制数据，最大长度为 230 - 1修改基本表2修改基本表Alter Table ADD 完整性约束DROPMODIFY ;ADD：增加新列和新的完整性约束条件DROP：删除制定的完整性条件MODIFY：用于修改原有的列的定义，包括列名和数据类型修改表的例子例：向student表增加入学时间列，类型为日期

5、型ALTER TABLE student ADD scome DATETIME例：将年龄的数据类型改为字符型；ALTER TABLE student MODIFY Sage char(4)例：删除学生姓名必须唯一值的约束ALTER TABLE student DROP UNIQUE(sname)删除基本表DROP TABLE student 基本表定义一旦删除，表中的数据、此表上建立的索引和视图都将自动被删除掉。因此执行基本表的删除一定要格外小心。创建索引建立索引是加快查询速度的有效手段，在基本表上建立一个或多个索引，以提供多种存取路径，加快查询速度。1建立索引在SQL语言中，建立索引使用CR

6、EATE INDEX语句，其一般格式为：CREATE UNIQUE CLUSTER INDEX ON (,.);UNIQUE：唯一索引CLUSTER：聚簇索引。所谓聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织。由于一般表只能有一个物理顺序，所以一个表上只能建立一个聚簇索引。创建索引用户可以在最常查询的列上建立聚簇索引以提高查询效率，建立聚簇索引后，更新索引列数据时，往往会导致表中记录的物理顺序的变更，代价较大，因此对于经常更新的列不宜建立聚簇索引。例：建立下面的聚簇索引Create CLUSTER INDEX stusname on student(sname)创建索引例，建立下

7、面三个索引：CREATE UNIQUE INDEX stusno ON student(sno);CREATE UNIQUE INDEX Coucon on student(cno);CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC)删除索引索引一旦建立，就由系统使用和维护它，不需用户干预。建立索引是为了减少查询操作的时间，但如果数据增加、删除频繁，系统会花很多时间来维护索引。这时可以删除一些不必要的索引。DROP INDEX 例 删除Student 表的Stusname 索引DROP INDEX Stusname；使用索引问题一：在关系clien

8、tdepart，id，name，tel中分别标识客户的分区、ID、姓名和电话，假如分区固定为武昌、汉口、汉阳、江夏、青山、黄陂6个分区，系统已对depart建立索引，查询所有的客户信息。查询一， select * from client没有使用任何索引列，系统按逐条扫描的方式返回数据，执行效率低特别是在返回数据在1w条以上时）查询二， select * from client where depart=武昌 or depart=汉口.正确使用索引列depart，SQL分析器将对此SQL语句正确优化，查询效率将大大优于前1个语句，在同样访问的结果集情况下，这种写法将比前一种写法快615倍。使用索

9、引问题二：在关系studentid，name，birth中，查询年龄在20岁以上的所有学生信息，假设对birth建立索引。【查询一】select * from student where year(now)-year(birth)20【分析】由于对索引列birth进行了函数处理year，所以系统没法对该sql语句进行任何优化，将执行逐条扫描比较，执行效率低。索引列的使用还有一个基本的原则，即不要对索引列进行任何函数或者运算符的处理。【查询二】select * from student where birthdateadd(year,-20,getdate()由于直接对索引进行比较，系统将能正确

10、优化该SQL语句，执行效率高创建索引的一般原则创建索引的一般原则情况情况是否创建索引是否创建索引列定义了一个唯一主键是的，索引将实现唯一性列定义了一个主键并用在查询的连接条件中是的，用来减少在连接中所读的纪录列定义了一个经常被用来连接另一张基表的外键是的，因为列被用在连接中列定义了一个通过关联完整性实现的外键是的，用来提高有效操作的性能列经常被用在一个返回不到百分之五的行的查询的WHERE子句中是的，只有不到百分之五的行被返回创建索引基本原则创建索引基本原则DML-数据操纵语言对基本表进行插入、修改、删除INSERT INTOINSERT INTO 语句用于向表格中插入新的行。语法完全插入IN

11、SERT INTO 表名称 VALUES (值1, 值2,.) 指定所要插入数据的列：INSERT INTO table_name (列1, 列2,.) VALUES (值1, 值2,.) UPDATEUpdate 替换表中列/字段的数值 语法Update table SET col = expression , . FROM fromlist Where condition 输入table 现存表的名称 column 表 table 中列/字段的名 expression 赋予列/字段的一个有效的值或表达式 fromlist Postgres 的一个非标准的扩展，允许别的表中的列/字段出现在

12、Where 条件里 condition DELETEDELETE 删除基本表里的记录 语法delete from table_name Where condition 输入table_name 现存表的名称 condition 删除条件DQL-数据查询语言对基本表里的记录进行检索A.基本查询B.高级查询基本查询语法数据库查询是数据库的核心操作。SQL语言提供了SELECT语句进行数据库的查询，该语句的语法如下：SELECT 属性名表或库函数FROM 基本表 （ 或视图 ） WHERE 条件表达式 GROUP BY 属性名 HAVING 库函数表达式 ORDER BY 属性名 ASC OR DE

13、SC基本查询语法整个SELECT语句的含义是，根据Where子句的条件表达式，从From子句指定的基本表或视图中找到满足条件的元组，再按SELECT子句中的目标列表达式，选出元组中的属性值形成结果表。如果有GRoup自己，则将结果按Group by后的属性值进行分组，该属性列值相等的元组为1个组。通常会在每组中作用集函数。如果Group by 子句带有Having 短语，则只有满足条件的组才予输出。如果有Order by 子句，则还需排序列的值升序或降序排序。基本查询示例数据库说明：以学生课程数据库为例说明SELECT 语句的各种用法：Student( Sno,Sname,Ssex,Sage,

14、sdept,City);学生学号、姓名、性别、年龄、所在系、出生城市）Course (Cno,Cname);课程课程编号、课程名称）SC(Sno,Cno,Score)学生选修学号、课程编号、成果）基本查询查询表中若干列选择表中的若干列选择表中的若干列1查询指定列查询指定列例：查询全体学生的学号与姓名例：查询全体学生的学号与姓名select sno,sname from student2查询全部列查询全部列例：查询全体学生的详细记录例：查询全体学生的详细记录Select * from studentSelect sno,sname,ssex,sage,sdno from student3)查询经

15、过计算后的列查询经过计算后的列例：例：select sage 2 from student4取别名取别名例例:select sname 姓名姓名 from student基本查询过滤1)使用distinct 关键字过滤重复行例： select distinct sno From SC2)使用where过滤条件例：查询所有的女学生子类select * from student where ssex=女例：查询考试成绩有不及格的学生的学号SELECT distinct sno From sc where score，=，=，!=，NOT加上述确定范围BETWEEN AND，NOT BETWEEN

16、AND确定集合IN，NOT IN字符匹配LIKE，NOT LIKE空值IS NULL，IS NOT NULL多重条件AND ，OR基本查询排序使用Order by 子句对查询结果进行升序默认 ASC或降序DESC排列，默认为升序。例：查询选修了3号课程的学生的学号及成绩，查询结果按分数的简序排列。Select sno，score from sc where cno=3 order by score desc例：查询全体学生的情况，查询结果按所在系的序号进行升序排列，同一系中的学生按年龄降序排列select * from student order by sdept,sage DESC基本查询-

17、集函数为了进一步方便用户，增强检索功能,SQL提供了许多集函数，主要有：COUNT（DISTINCT|ALL *）统计元组的个数COUNT（DISTINCT|ALL ）统计一列中值的个数SUM（DISTINCT|ALL ）计算一列值的总和（数值型）AVG（DISTINCT|ALL ）计算一列值的平均值MAX（DISTINCT|ALL ）计算一列值的最大值MIN（DISTINCT|ALL ）计算一列值的最小值思考题：ALL和Distinct 有什么区别？基本查询使用集函数student(sno,sname,score)SnoSnamescore20BNull20BNull30Cnull40D89

18、60D90基本查询使用集函数例：查询学生的总人数SELECT Count(*) from student例：查询选修了课程的学生人数SELECT count(distinct sno) from sc;例：计算1号课程的学生平均成绩select avg(score) from sc where cno=1例：查询选修了1号课程的学生最高分数select MAX(score) from sc where cno=1基本查询分组查询对查询结果进行分组对查询结果进行分组Group by 子句将查询结果按某一列或多列值分组，值相等子句将查询结果按某一列或多列值分组，值相等的为一组。的为一组。对查询结果分组的目的是为了细化集函数的作用对象。如果对查询结果分组的目的是为了细化集函数的作用对象。如果未对查询的结果进行分组，集函数将作用于整个查询结果。未对查询的结果进行分组，集函数将作用于整个查询结果。分组后集函数将作用于每一个组，即每一组都有一个函数值。分组后集函数将作用于每一个组，即每一组都有一个函数值。例：求各个课程号及相应的选课人数例：求各个课程号及相应的选课人数select cno,count(sno) from sc group by cno;se