第3章SQL数据库.

四川农业大学潘勇浩2016SQL简介SQL（StructuredQueryLanguage）译为结构化查询语言，是标准的关系数据操作语言。1974年由Boyce和Chamberlain提出。1975～1979在IBM的SystemR上实现。1986年ANSI公布了SQL－86标准，次年被ISO通过。1989年ISO推出了SQL－89标准。1992年ISO推出了SQL－92标准，也称为SQL2。SQL－99/SQL3，增加了对面向对象模型的支持。SQL－2006，与XML关联应用四川农业大学潘勇浩2016SQL的特点SQL集数据定义(Definition)、数据查询(Query)、数据操纵(Manipulation)和数据控制(Control)功能于一体，语言简捷自然，易学易用。SQL既是自主式语言，又是嵌入式语言。GRANT，REVOKE数据控制INSERT，UPDATE，DELETE数据操纵CREATE，DROP，ALTER数据定义SELECT数据查询动词功能四川农业大学潘勇浩2016数据定义（DDL）定义数据库结构、表结构、视图、索引等数据实际都放在这些物理文件当中逻辑结构对应关系是由数据库管理员设计和定义的四川农业大学潘勇浩2016数据库的定义与删除CREATEDATABASE数据库名[其它参数]定义数据库例如命令：CreateDatabasetickets可建立一个名为“tickets”的数据库当然可以加参数来指定数据库的文件名称、存储位置和空间大小等。DROPDATABASE数据库名[其它参数]删除数据库例如：DropDatabasetickets四川农业大学潘勇浩2016定义基本表CREATETABLE表名(列名1数据类型[列级完整性约束][，列名2数据类型[列级完整性约束]┆[，列名n数据类型[列级完整性约束]][，表级完整性约束])CREATETABLETeacher(工号CHAR(4),姓名CHAR(20),年龄INT,性别CHAR(2))创建一个没有任何约束的表四川农业大学潘勇浩2016定义基本表CREATETABLETeacher(工号CHAR(4)PRIMARYKEY,姓名CHAR(20)NOTNULL,年龄SMALLINTCHECK(年龄0),性别CHAR(2)DEFAULT'男',UNIQUE(姓名))创建一个约束丰富的表:工号为主键，姓名不能为空，年龄必须大于0，性别的默认值为‘男’，且姓名必须是唯一的。四川农业大学潘勇浩2016修改基本表ALTERTABLE表名[ADD新列名数据类型[列级完整性约束]][ADD行级完整性约束[DROPCOLUMN列名][DROP[CONSTRAINT]完整性约束名][ALTERCOLUMN列名新数据类型]为Teacher表增加一个工龄属性列，取值为整型：ALTERTABLETeacherADD工龄INT删除Teacher表的职称列：ALTERTABLETeacherDROPCOLUMN职称四川农业大学潘勇浩2016删除基本表DROPTABLE表名例如删除名称为“Teacher”的表：DROPTABLETeacher基本表被删除后，该基本表的结构定义、表中数据、表的完整性约束以及表的索引，即一切与该基本表相关的元素都被会一并删除。当然，某些约束也会阻止表被删除警告四川农业大学潘勇浩2016索引的概念┆┆┆1000100110021003310031033104┆数据页（数据记录不一定有序）1001#职工记录1002#职工记录1000#职工记录3100#职工记录1003#职工记录3104#职工记录┆┆┆3103#职工记录┆索引类似于书的目录，可实现按某些属性值对数据记录进行快速定位，从而提高数据查询效率。四川农业大学潘勇浩2016建立索引CREATE[UNIQUE][CLUSTERED]INDEX索引名ON表名(列名1[次序][,列名2[次序]]…)•CLUSTERED表示建立聚簇索引，即表按照索引的列重新排列记录。一个表只能有一个聚簇索引。•UNIQUE表明建立唯一索引，即每一个索引值只对应唯一的数据记录。•次序可取ASC(升序)或DESC(降序)，缺省为ASC。四川农业大学潘勇浩2016建立索引建立按工号升序排列的聚簇索引：CREATECLUSTEREDINDEXidxGHONTeacher(工号)建立按姓名降序排列的唯一索引：CREATEUNIQUEINDEXidxXMONTeacher(姓名DESC)建立按工号升序，姓名降序排列的普通索引：CREATEINDEXidx_GH_XMONTeacher(工号ASC，姓名DESC)四川农业大学潘勇浩2016删除索引DROPINDEX索引名ON表名•应该对查询频率高的列建索引，以提高查询效率；•索引可以随时建立和删除；•索引的使用和维护完全由DBMS决定；•一个表上可建多个索引。但索引过多会耗费空间，且会降低插入、删除、更新的效率。索引的几点说明例如删除表“Teacher”中名为“idx_xm”的索引：DROPINDEXidx_xmOnTeacher四川农业大学潘勇浩2016查询命令的一般格式SELECT目标列表达式列表FROM表名或视图名列表[WHERE条件表达式][GROUPBY列名1[HAVING条件表达式]][ORDERBY列名2[ASC/DESC]]根据WHERE子句的检索条件，从FROM子句指定的基本表或视图中选取满足条件的元组，再按照SELECT子句中指定的目标列表达式投影得到结果表。如果有GROUPBY子句，则将查询结果按照列名1分组，即列值相等的元组在结果表中只产生一条记录。另外分组时如果有HAVING短语，则只输出满足HAVING条件的分组。如果有ORDERBY子句，结果表要按照列名2的值排序。四川农业大学潘勇浩2016SELECT子句原始数据查询结果只有指定的这几列指定查询结果的列，或者说表结构∏A(R)四川农业大学潘勇浩2016SELECT子句可以给结果中的列另取列名原始数据三种表达方式都可以四川农业大学潘勇浩2016SELECT子句列可为任意表达式原始数据查询结果GetDate()得出当前时间Year(D)求出日期D的年四川农业大学潘勇浩2016SELECT子句可以用“*”选择所有列，更简单原始数据查询结果有所有列四川农业大学潘勇浩2016WHERE子句选择满足条件的行,条件一般是σF(R)原始数据查询结果[NOT]θ表达式其中θ可以是：=,,,=,=,!=,!,!四川农业大学潘勇浩2016WHERE子句选择条件为限定范围，格式为：[NOT]BETWEEN下限AND上限原始数据其实条件就是：1200≤工资≤2000否定时只需写成：工资NotBetween四川农业大学潘勇浩2016WHERE子句集合检查，格式为：[NOT]IN(值列表)其中各个值之间用逗号分隔原始数据其实条件就是：工资=800or工资=1200or工资=1500四川农业大学潘勇浩2016WHERE子句字符串匹配，用于匹配字符、日期，格式为：[NOT]LIKE串模式[ESCAPE'转义符']串模式中可出现以下通配符：“%”匹配任意多个任意字符(包括空字符)；“_”匹配一个任意字符（不能是空字符）；例如‘To%’代表‘Tool’、‘Toy’、‘To’等以“To”开头的任意字符串；又如‘B_T’可代表‘BIT’、‘BaT’等头为‘B’尾为‘T’的只有3个字符的任意串，但不能代表'BoaT'与'BT'等。四川农业大学潘勇浩2016WHERE子句字符匹配示例原始数据姓刘不就是以“刘”字开头的姓名吗四川农业大学潘勇浩2016WHERE子句字符匹配示例原始数据查询出只有1人名字最后一字为“明”的实际有4人四川农业大学潘勇浩2016WHERE子句字符匹配示例原始数据查询结果四川农业大学潘勇浩2016WHERE子句字符匹配中转义符的用法如果查询的串中要包含“%”等通配符，则必须用转义符告诉SQL解释器将通配符转义为实实在在的字符这里的下划线是要匹配的实际字符所以要转义转义符可以为除通配符以外的任意字符如上例条件亦可为:姓名LIKE‘%^_%’ESCAPE‘^’四川农业大学潘勇浩2016WHERE子句空值检查，格式为：IS[NOT]NULL原始数据注意空值和空白的区别对允许为空的列才有意义！四川农业大学潘勇浩2016WHERE子句复合条件用AND,OR联接多个条件组成逻辑表达式即性别为男且职称为讲师，或者性别为女且职称为助教的教师四川农业大学潘勇浩2016DISTINCT短语用DISTINCT可消除查询结果中重复的行原始数据只有这5种不同的职称取值四川农业大学潘勇浩2016TOP短语用TOPn[percent]查询前n[%]行原始数据只要前3行四川农业大学潘勇浩2016ORDERBY子句ASC（缺省）表示升序DESC表示降序。原始数据工资从小到大排列Orderby工资DESC就可实现降序排列控制结果中元组的排序方式四川农业大学潘勇浩2016ORDERBY子句支持多级排序原始数据每个排序字段都有独立的排序方式不要误解成按工资和生日的降序排列四川农业大学潘勇浩2016连接查询连接查询指在两张以上的相关表中进行查询，从而实现将存储于不同表中的数据连接成完整的查询信息。最基础的连接是交叉连接，即广义笛卡尔积。SELECTST.*,C.*FROMST,C学号姓名ST.课号C.课号课名98001张三K01K01数据结构98001张三K01K02软件工程98002李四K02K01数据结构98002李四K02K02软件工程98003王五K01K01数据结构98003王五K01K02软件工程结果有3×2=6行ST的列C的列学号姓名课号98001张三K0198002李四K0298003王五K01STC课号课名K01数据结构K02软件工程×四川农业大学潘勇浩2016连接查询SELECTST.姓名,C.课名FROMST,CWHEREST.课号=C.课号姓名课名张三数据结构李四软件工程王五数据结构学号姓名课号98001张三K0198002李四K0298003王五K01ST课号课名学分K01数据结构3K02软件工程4K03数据库3CST与Ｃ的交叉连接课号相等才有意义吧学号姓名ST.课号C.课号课名98001张三K01K01数据结构98001张三K01K02软件工程98002李四K02K01数据结构98002李四K02K02软件工程98003王五K01K01数据结构98003王五K01K02软件工程真正有意义的连接R.AθS.BRS四川农业大学潘勇浩2016连接查询格式T-SQL多表连接查询的一般格式:SELECT目标列表达式列表FROM表1,表2,···WHERE连接条件ANSI多表连接查询的一般格式:SELECT目标列表达式列表FROM表1连接方式JOIN表2ON连接条件其中连接方式可以为内连接(INNER)、左外连接(LEFTOUTER)、右外连接(RIGHTOUTER)、全外连接(FULLOUTER)一般用于内连接可以实现各种方式的连接四川农业大学潘勇浩2016内连接一般情况下的连接都是内连接，即只连接符合条件的行SELECTST.姓名,C.课名FROMST,CWHEREST.课号=C.课号SELECT姓名,课名FROMSTINNERJOINCONST.课号=C.课号等价学号姓名课号98001张三K0198002李四K0298003王五K0198004赵六98005孙七K01ST课号课名学分K01数据结构3K02软件工程4K03数据库3K04C语言3C姓名课名张三数据结构李四软件工程王五数据结构孙七数据结构查询学生姓名及其所学课程的名称赵六呢？C语言和数据库课呢？四川农业大学潘勇浩2016外接连要查询其中一张连接表的所有行时就需要用外连接，结果