SQL基础知识培训三.ppt

SQL基础知识培训三设计事业部 李红,一些习题：,1、从USER表里选出与USID=“001”同组（即GRID相等）的全部记录；2、从USER表里取出组号GRID=“01”的全部用户号USID和用户名称USNM，从用户-帐户关联信息表里取出该客户号下的所有帐号ACNO。（有用户必有帐户）。3、从USER表里取出所有客户号USID以及客户名称，从STOCK表里取出对应客户号的库存编号STID。（并不是所有的客户号都有库存编号）。4、从USER表里取出GRID=“01”的全部记录，与从USER_OLD表里取出GRID=“02”的全部记录合并后输出。（USER表与USER_OLD 表结构相同）。,今日培训目录和大纲,1、子查询2、多表连接查询3、使用UNINO语句合并查询结果4、SQL语句优化的一些方法,1.子查询可以在一个SELECT语句中嵌入另一个完整的SELECT语句，则嵌入到SELECT语句中的SELECT称为子查询。子查询应该用括号加以区分。示例：查询和用户”001”同组的用户号和用户名：SELECT CUNO,CUNM FROM USERSWHERE GRID=(SELECT GRIDFROM USERS WHERE CUNO=“001”);,2.多表连接查询 内连接；外连接；左连接；右连接；等值连接；非等值连接。,2.1内连接通过比较源表间共享的列的值从多个源表检索行的操作。内联接排除来自不具有与其它源表中的行匹配的行的源表的行。,示例：SELECT USER.USID,USER.USNM,STOCK.STID FROM USER,STOCK WHERE USER.USID=STOCK.USID;将取出USER表和STOCK表具有相同USID的各项的值。,2.2外连接一种联接，包括满足搜索条件的联接表的所有行，甚至包括在所联接的表中没有匹配行的表中的行。对于当一个表中的行与另一个表中的行不匹配时所返回的结果集行，将为解析到没有相应行的表中的所有结果集列提供 NULL 值。,外连接左连接在s_id列上联接 学生档案 表和 学生成绩 表。结果只显示相匹配的数据。若要在结果中包括所有的学生信息，而不管 学生成绩 表中是否有关联的记录，可以使用 左向外联接 LEFT OUTER JOIN。不管第二个表中是否有匹配的数据，结果将包含第一个表中的所有行。SELECT 学生档案.学号,学生档案.姓名,学生档案.性别,学生档案.班级,学生成绩.学习科目,学生成绩.学习成绩,学生成绩.s_id FROM 学生档案 LEFT OUTER JOIN 学生成绩 ON 学生档案.s_id=学生成绩.s_id,外连接右连接在s_id列上联接 学生档案 表和 学生成绩 表。结果只显示相匹配的数据。若要在结果中包括所有的学生成绩，而不管 学生信息表中是否有关联的记录，可以使用 右向外联接运算符 RIGHT OUTER JOIN。不管第一个表中是否有匹配的数据，结果将包含第二个表中的所有行。例：SELECT 学生档案.学号,学生档案.姓名,学生档案.性别,学生档案.班级,学生成绩.学习科目,学生成绩.学习成绩,学生成绩.s_id FROM 学生档案 RIGHT OUTER JOIN学生成绩 ON 学生档案.s_id=学生成绩.s_id,外连接全连接若要通过在联接结果中包括不匹配的行保留不匹配信息，请使用完整外部联接，FULL OUTER JOIN。不管另一个表是否有匹配的值，此运算符都包括两个表中的所有行。假设在 s_id 列上联接 学生档案 表和 学生成绩 表。结果只显示相匹配的数据。FULL OUTER JOIN 运算符指明：不管表中是否有匹配的数据，结果将包括两个表中的所有行。例：SELECT 学生档案.学号,学生档案.姓名,学生档案.性别,学生档案.班级,学生成绩.学习科目,学生成绩.学习成绩,学生成绩.s_id FROM 学生档案 FULL OUTER JOIN 学生成绩 ON 学生档案.s_id=学生成绩.s_id。,3.使用UNINO语句合并查询结果使用UNION语句可以合并两个或者多个查询的结果。UNION语句用第二个查询结果合并第一个查询结果。它不显示两个查询中重复的行。使用UNION ALL会保留重复的行。使用UNION语句要求两个或多个查询语句返回的查询结果结构全部一致，包括列的个数和类型。,示例：SELECT*FROM USER WHERE GRID=“01”UNION ALLSELECT*FROM USER_OLD WHERE GRID=“02”;SELECT USID,USNM FROM USER WHERE GRID=“01”UNIONSELECT USID,USNM FROM USER_OLD WHERE GRID=“02”;,4.SQL语句优化的一些方法侧重于讲述一些最基本的提高sql语句的执行效率的知识，希望可以抛砖引玉，对大家写出高效率的sql语句有所帮助。,4.1索引创建的规则1经常作为where条件的字段要创建索引。2重复值比较多的字段，创建索引的作用不大。例如，对于记录状态jiluzt这样的字段，它只有十多个有效的值，对应几十万的记录，在索引中有没有这个字段，意义不是很大。3要把重复值少的字段放在复合索引前面。对于sql语句，如果前面的条件就可以确定一条记录的话，就不必去比较后面的字段了。比如：表akhzh创建索引的时候，如果需要创建一个khzhlx和kehuzh的索引，就需要把kehuzh放在前面。这样才可以准确、快速的定位到要查找的记录。,4索引最好不要创建太多，一般一个表不要超过8个。能合并的索引应该合并。索引其实是牺牲insert,delete的效率来提高select的效率，如果索引过多，表的insert和update是的速度将很慢。5索引的合并。不必要的、重复的索引应该删除，可以减少数据库占用的空间，提高数据库insert,update的效率。如果两个或者几个索引的前几个字段相同，并且除去相同的字段，后面的字段的重复值又很多，那么这几个索引就可以合并成字段少的那个索引。,举例1：下面的这几个索引由于jiluzt，cxcflx的有效值只有几个，所以这三个索引就可以合并成一个索引6，前面两个索引可以删除。如下面：BCXCF 查询查复登记簿Index idx4HUILUU 汇路SXIORQ 发送日期SHBHHH 收报行行号JILUZT 记录状态 D 0Index idx5HUILUU 汇路SXIORQ 发送日期SHBHHH 收报行行号CXCFLX 查询查复类型Index idx6HUILUU 汇路SXIORQ 发送日期SHBHHH 收报行行号,举例2：下面的索引2也是多余的，索引1已经是唯一索引了，那么索引2和任何字段组合都是唯一索引，并且是没有必要的：BTCPJ 同城票交登记簿UNIQUE Index idx1TCHTCH 同城提出号Index idx2TCHTCH 同城提出号YNGYJG 营业机构号,4.2 写where条件的基本规则 1order by和group by中的字段顺序，也应该和对应的索引的字段顺序一致2where条件中的语句应该尽可能多的从第一个字段开始和索引的顺序一致。只有这样才可以最有效的使用索引。也许有些不一致的情况下，数据库会去自动的匹配合适的索引，但是为了安全起见，最好还是和索引字段的顺序一样。3Where条件的字段顺序在和索引的顺序一致的条件下，尽量把最具有限制性的条件放在前面。4尽量的减少matches的使用，最好使用=代替。主要由于matches查询时不能使用索引。比如 where kehhao match“1*”用语句 where kehhao1=1代替。,4.2 写where条件的基本规则 5判断字段为空最好用where fieldname is null，他的效率比语句 where fieldname=高。6如果字段的类型是字符串的，在写where条件的时候一定要加上引号。如：不应该写成where jiluzt=1，而应该写成where jiluzt=1；否则的华，虽然也可以查到，但是效率很低，因为数据库要把字段的值进行类型转换，同时有可能原有的索引就没有作用了。曾经遇到过一个动态的sql语句，经过对where条件中一个字符串字段的值增加引号，使得这个语句的查询时间缩短了几十倍。,4.2 写where条件的基本规则 7复合索引的查询规则：复合条件的查询的规则很复杂，大家如果对sql语句的执行效率有疑惑的话，尽量的多动动手，在sql前面增加一句：set explain on;，执行完毕后，去看看当前目录下的sqexplain.out，看看它到底有没有用索引。如果没有使用索引、或者是没有使用到合适的索引，你就需要修改你的where条件中字段的顺序或者适当的增加或者修改索引了。为了大家加深印象，建议大家先想一想下面的where执行情况，然后再去验证验证，看看结果到底是不是和你想的一样。复合索引包含A、B、C三个字段：对于语句 where A=xxx，会不会用这个索引呢？结果：能。对于语句 where B=xxxx 会不会使用这个索引？结果：不能。对于语句where A=xxxxx and B=xxxx，会不会用这个索引？结果：能。对于语句where A=xxxxx and C=xxxx，会不会用这个索引？结果：能，但是效率不高。对于语句 where B=xxxx and C=xxxxx 会不会使用这个索引？结果：不能。,4.2 sql执行过程的监测 sql语句执行过程跟数据库内部的策略有很大的关系，在执行sql语句之前，增加一句：set explain on;它就会把数据库内部查询策略记录到当前目录的sqexplain.out。sql语句提高效率的关键就是能使用到有效的索引。在sqexplain.out如果出现INDEX PATH的字样，则表示这个sql语句使用了索引；同样如果在这个文件中出现SEQUENTIAL SCAN的字样，则说明他是从数据库中第一条记录查询到最后一条顺序查询，时间当然长了。,4.2 sql执行过程的监测 sqexplain.out的例子：QUERY:-select*from azhjx where zhyyjg=034201 and kaihrq=20020101 and kaihrq=20020101)Upper Index Filter:=20021118Key-First Filters:(=0)有INDEX PATH字样，说明这是一个使用索引的查询。,4.2 sql执行过程的监测 sqexplain.out的例子：QUERY:-select*from bdgkh where kehzwm matches*餐饮*Estimated Cost:38009Estimated#of Rows Returned:14343 1)cbsver.bdgkh:SEQUENTIAL SCAN Filters:MATCHES*餐饮*这个例子中有 SEQUENTIAL SCAN 则说明该sql没有使用索引，顺序查询所有的记录。顺序查询会随着记录数的增加，查询时间会很多倍增加。,4.2其他的需要注意问题 尽量减少使用rowid，因为访问rowid需要对磁盘操作，速度很慢。大规模load数据的时候，要去掉数据库的日志。大规模load数据的时候，最好删除表的索引，Load完成后再创建索引。大规模的load和delete后，要更新相应表的统计信息（update statistics）。Prepare的动态sql的效率不高，最好减少使用。,