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 同组的用户号和用户名 SELECTCUNO CUNMFROMUSERSWHEREGRID SELECTGRIDFROMUSERSWHERECUNO 001 2 多表连接查询内连接 外连接 左连接 右连接 等值连接 非等值连接 2 1内连接通过比较源表间共享的列的值从多个源表检索行的操作 内联接排除来自不具有与其它源表中的行匹配的行的源表的行 示例 SELECTUSER USID USER USNM STOCK STIDFROMUSER STOCKWHEREUSER USID STOCK USID 将取出USER表和STOCK表具有相同USID的各项的值 2 2外连接一种联接 包括满足搜索条件的联接表的所有行 甚至包括在所联接的表中没有匹配行的表中的行 对于当一个表中的行与另一个表中的行不匹配时所返回的结果集行 将为解析到没有相应行的表中的所有结果集列提供NULL值 2 2 1外连接 左连接在s id列上联接学生档案表和学生成绩表 结果只显示相匹配的数据 若要在结果中包括所有的学生信息 而不管学生成绩表中是否有关联的记录 可以使用左向外联接LEFTOUTERJOIN 不管第二个表中是否有匹配的数据 结果将包含第一个表中的所有行 SELECT学生档案 学号 学生档案 姓名 学生档案 性别 学生档案 班级 学生成绩 学习科目 学生成绩 学习成绩 学生成绩 s idFROM学生档案LEFTOUTERJOIN学生成绩ON学生档案 s id 学生成绩 s id 2 2 1外连接 右连接在s id列上联接学生档案表和学生成绩表 结果只显示相匹配的数据 若要在结果中包括所有的学生成绩 而不管学生信息表中是否有关联的记录 可以使用右向外联接运算符RIGHTOUTERJOIN 不管第一个表中是否有匹配的数据 结果将包含第二个表中的所有行 例 SELECT学生档案 学号 学生档案 姓名 学生档案 性别 学生档案 班级 学生成绩 学习科目 学生成绩 学习成绩 学生成绩 s idFROM学生档案RIGHTOUTERJOIN学生成绩ON学生档案 s id 学生成绩 s id 2 2 1外连接 全连接若要通过在联接结果中包括不匹配的行保留不匹配信息 请使用完整外部联接 FULLOUTERJOIN 不管另一个表是否有匹配的值 此运算符都包括两个表中的所有行 假设在s id列上联接学生档案表和学生成绩表 结果只显示相匹配的数据 FULLOUTERJOIN运算符指明 不管表中是否有匹配的数据 结果将包括两个表中的所有行 例 SELECT学生档案 学号 学生档案 姓名 学生档案 性别 学生档案 班级 学生成绩 学习科目 学生成绩 学习成绩 学生成绩 s idFROM学生档案FULLOUTERJOIN学生成绩ON学生档案 s id 学生成绩 s id 3 使用UNINO语句合并查询结果使用UNION语句可以合并两个或者多个查询的结果 UNION语句用第二个查询结果合并第一个查询结果 它不显示两个查询中重复的行 使用UNIONALL会保留重复的行 使用UNION语句要求两个或多个查询语句返回的查询结果结构全部一致 包括列的个数和类型 示例 SELECT FROMUSERWHEREGRID 01 UNIONALLSELECT FROMUSER OLDWHEREGRID 02 SELECTUSID USNMFROMUSERWHEREGRID 01 UNIONSELECTUSID USNMFROMUSER OLDWHEREGRID 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查询查复登记簿Indexidx4HUILUU汇路SXIORQ发送日期SHBHHH收报行行号JILUZT记录状态D 0 Indexidx5HUILUU汇路SXIORQ发送日期SHBHHH收报行行号CXCFLX查询查复类型Indexidx6HUILUU汇路SXIORQ发送日期SHBHHH收报行行号 举例2 下面的索引2也是多余的 索引1已经是唯一索引了 那么索引2和任何字段组合都是唯一索引 并且是没有必要的 BTCPJ同城票交登记簿UNIQUEIndexidx1TCHTCH同城提出号Indexidx2TCHTCH同城提出号YNGYJG营业机构号 4 2写where条件的基本规则1 orderby和groupby中的字段顺序 也应该和对应的索引的字段顺序一致2 where条件中的语句应该尽可能多的从第一个字段开始和索引的顺序一致 只有这样才可以最有效的使用索引 也许有些不一致的情况下 数据库会去自动的匹配合适的索引 但是为了安全起见 最好还是和索引字段的顺序一样 3 Where条件的字段顺序在和索引的顺序一致的条件下 尽量把最具有限制性的条件放在前面 4 尽量的减少matches的使用 最好使用 代替 主要由于matches查询时不能使用索引 比如wherekehhaomatch 1 用语句wherekehhao 1 1 代替 4 2写where条件的基本规则5 判断字段为空最好用wherefieldnameisnull 他的效率比语句wherefieldname 高 6 如果字段的类型是字符串的 在写where条件的时候一定要加上引号 如 不应该写成wherejiluzt 1 而应该写成wherejiluzt 1 否则的华 虽然也可以查到 但是效率很低 因为数据库要把字段的值进行类型转换 同时有可能原有的索引就没有作用了 曾经遇到过一个动态的sql语句 经过对where条件中一个字符串字段的值增加引号 使得这个语句的查询时间缩短了几十倍 4 2写where条件的基本规则7 复合索引的查询规则 复合条件的查询的规则很复杂 大家如果对sql语句的执行效率有疑惑的话 尽量的多动动手 在sql前面增加一句 setexplainon 执行完毕后 去看看当前目录下的sqexplain out 看看它到底有没有用索引 如果没有使用索引 或者是没有使用到合适的索引 你就需要修改你的where条件中字段的顺序或者适当的增加或者修改索引了 为了大家加深印象 建议大家先想一想下面的where执行情况 然后再去验证验证 看看结果到底是不是和你想的一样 复合索引包含A B C三个字段 对于语句whereA xxx 会不会用这个索引呢 结果 能 对于语句whereB xxxx 会不会使用这个索引 结果 不能 对于语句whereA xxxxx andB xxxx 会不会用这个索引 结果 能 对于语句whereA xxxxx andC xxxx 会不会用这个索引 结果 能 但是效率不高 对于语句whereB xxxx andC xxxxx 会不会使用这个索引 结果 不能 4 2sql执行过程的监测sql语句执行过程跟数据库内部的策略有很大的关系 在执行sql语句之前 增加一句 setexplainon 它就会把数据库内部查询策略记录到当前目录的sqexplain out sql语句提高效率的关键就是能使用到有效的索引 在sqexplain out如果出现INDEXPATH的字样 则表示这个sql语句使用了索引 同样如果在这个文件中出现SEQUENTIALSCAN的字样 则说明他是从数据库中第一条记录查询到最后一条顺序查询 时间当然长了 4 2sql执行过程的监测sqexplain out的例子 QUERY select fromazhjxwherezhyyjg 034201 andkaihrq 20020101 andkaihrq 20020101 UpperIndexFilter cbs azhjx kaihrq 20021118 Key FirstFilters cbs azhjx jiluzt 0 有INDEXPATH字样 说明这是一个使用索引的查询 4 2sql执行过程的监测sqexplain out的例子 QUERY select frombdgkhwherekehzwmmatches 餐饮 EstimatedCost 38009Estimated ofRowsReturned 143431 cbsver bdgkh SEQUENTIALSCANFilters cbsver bdgkh kehzwmMATCHES 餐饮 这个例子中有SEQUE