Mysql学习笔记20171123

MySQL 学习笔记学习笔记 1 查询语句查询语句 1 1 简单查询简单查询 1 1 1distinct 删除重复行删除重复行 SELECT 语句中使用 ALL 或 distinct 选项来显示表中符合条件的所有行或删除 其中重复的数据行 默认为 ALL 使用 distinct 选项 时 对于所有重复的数据行 在 SELECT 返回的结果集合中只保留一行 distinct 一般需要处理单一字段时使用 一般需要处理单一字段时使用 不能用于多个字段 不能用于多个字段 例如 select distinct ID AA BB from tName select distinct ID from tName 区别在于 第一种情况是把三个字段的内容都相同的记录认为是相同的记录 合 并一条 如果有一个字段是不同的 认为不是相同 不合并 我要消除 name 字段值重复的记录 同时又要得到 id 字段的值 其中 id 是自增 字段 如何用 distinct 消除重复记录的同时又能选取多个字段值 select distinct name from t1 能消除重复记录 但只能取一个字段 现在要同时 取 id name 这 2 个字段的值 select distinct id name from t1 可以取多个字段 但只能消除这 2 个字段值全部 相同的记录 最后解决方法 最后解决方法 SELECT id name FROM t1 WHERE id IN SELECT MAX id FROM t1 GROUP BY name order by id desc 注意开头的 id 的一定要 后面的 order by 里有的字段一定要加进 select 结果 要不然排序无效 1 1 2限制返回的行数限制返回的行数 使用 TOP n PERCENT 选项限制返回的数据行数 TOP n 说明返回 n 行 而 TOP n PERCENT 时 说明 n 是表示一百分数 指定返回的行数等于总行数的百分 之几 例如 SELECT TOP 2 FROM testtable SELECT TOP 20 PERCENT FROM testable 1 1 3WHERE 子句设置查询条件子句设置查询条件 WHERE 子句可包括各种条件运算符 1 比较运算符 大小比较 select age from person where name 孙权 1 2 2in 嵌套查询嵌套查询 in 关键字用于 where 子句中用来判断查询的表达式是否在多个值的列表中 返 回满足 in 列表中的满足条件的记录 示例 select name from person where countryid inin select countryid from country where countryname 魏国 输出结果为 1 2 3some 嵌套查询嵌套查询 some 在 sql 中的逻辑运算符号 如果在一系列比较中 有些值为 True 那么 结果就为 True some 的语法是 some 子查询 示例 select name from person where countryid somesome 用等号和以下查询到的值比较 如果与其中一个相等 就返回 select countryid from country where countryname 魏国 输出结果为 1 2 4all 嵌套查询嵌套查询 all 是 sql 中的逻辑运算符好 如果一系列的比较都为 true 那么结果才能为 true allall 当 countryid 大于以下返回的所有 id 此结果才为 True 此结果才返回 select countryid from country where countryname 魏国 输出结果为 1 2 5exists 嵌套查询嵌套查询 exists 是 sql 中的逻辑运算符号 如果子查询有结果集返回 那么就为 True exists 代表 存在 的意义 它只查找满足条件的那些记录 一旦找到第一个 匹配的记录后 就马上停止查找 exists 子查询 其中子查询是一个首先的 select 语句 不允许有 compute 子句和 into 关键字 exists 的意思是 子查询是否有结果集返回 例如 SELECT FROM Person WHERE exists SELECT 1 1 SELECT 0 SELECT NULL 返回结果都一样 因为这三个子查询都有结果集返回 因此总是 True SELECT FROM Person 照常执行 但是如果子查询中因为加了条件而没有结果集返回 则主语句就不执行了 SELECT FROM Person WHERE exists SELECT FROM Person WHERE Person Id 100100 如果不存在 Person Id 的记录 则子查询没有结果集返回 主语句不执行 1 3 连接查询连接查询 1 3 1左连接 左连接 LEFT JOIN LEFT JOIN 关键字会从左表 table name1 那里返回所有的行 即使在右表 table name2 中没有匹配的行 数据库在通过连接两张或多张表来返回记录时 都会生成一张中间的临时表 然后再将这张临时表返回给用户 在在 left join 下 下 on 和和 where 两者的区别 两者的区别 on 是在生成临时表的时候使用的条件是在生成临时表的时候使用的条件 不管 on 的条件是否起到作用 都会返 回左表 table name1 的行 where 则是在生成临时表之后使用的条件则是在生成临时表之后使用的条件 此时已经不管是否使用了 left join 了 只要条件不为真的行 全部过滤掉 SQLSQL 语句 语句 select a id a No b name from table1 a left join table2 b on a No a No b Nob No andand b name aaa b name aaa select a id a No b name from table1 a left join table2 b on a No a No b No b No wherewhere b name aaa b name aaa 表 1 table2 ididNoNo 1n1 2n2 3n3 表 2 table2 NoNonamename n1aaa n2bbb n3ccc 第一个结果集 第一个结果集 id No name 1 n1 aaa 2 n2 Null 3 n3 Null 第二个结果集 第二个结果集 id No name 1 n1 aaa 第一个 sql 的执行流程 首先找到 b 表的 name 为 aaa 的记录行 on a No b No and b name aaa 然后找到 a 的数据 即使不符合 b 表的规则 生成临时表返 回用户 第二个 sql 的执行流程 首先生成临时表 然后执行 where 过滤 b name aaa 不为真的结果集 最后返回给用户 因为 on 会首先过滤掉不符合条件的行 然后才会进行其它运算 所以按理说 on 是最快的 在多表查询时 on 比 where 更早起作用 系统首先根据各个表之间 的联接条件 把多个表合成一个临时表后 再由 where 进行过滤 然后再计算 计 算完后再由 having 进行过滤 由此可见 要想过滤条件起到正确的作用 首先要 明白这个条件应该在什么时候起作用 然后再决定放在那里 对于 JOIN 参与的表的关联操作 如果需要不满足连接条件的行也在我们的查 询范围内的话 我们就必需把连接条件放在 ON 后面 而不能放在 WHERE 后面 如果我们把连接条件放在了 WHERE 后面 那么所有的 LEFT RIGHT 等这些操作 将不起任何作用 对于这种情况 它的效果就完全等同于 INNER 连接 对于那些 不影响选择行的条件 放在 ON 或者 WHERE 后面就可以 记住 所有的连接条件都必需要放在记住 所有的连接条件都必需要放在 ON 后面 不然前面的所有后面 不然前面的所有 LEFT 和和 RIGHT 关联将作为摆设 而不起任何作用 关联将作为摆设 而不起任何作用 select pj id pj name IFNULL COUNT vm1 vmname 0 as 虚拟机数量 from project pj LEFT JOIN select vm project id vm name as vmname from vm host vm where vm status A vm1 on pj id vm1 project id where pj status P GROUP BY pj id 1 3 2右连接 右连接 RIGHT JOIN 1 3 3内连接内连接 内连接 也叫等值连接 inner join 产生同时符合 A 和 B 的一组数据 mysql select from A inner join B on A name B name id name id name 1 Pirate 2 Pirate 3 Ninja 4 Ninja 1 3 4全连接全连接 注意 mysql 不支持不支持 Full join 不过可以通过 UNION 关键字来合并 LEFT JOIN 与 RIGHT JOIN 来模拟 FULL join 1 4 联合查询 联合查询 UNION union 对两个结果集进行并集并集操作 重复数据只显示一次 重复数据只显示一次 union all 对两个结果集进行并集并集操作 重复数据全部显示 重复数据全部显示 1 4 1语法语法 SELECT column name FROM table1 UNION SELECT column name FROM table2 UNION 用于合并两个或多个 SELECT 语句的结果集 并消去表中任何重复行 UNION 内部的 SELECT 语句必须拥有相同数量的列 列也必须拥有相似的数 据类型 同时 每条 SELECT 语句中的列的顺序必须相同 1 4 2实例实例 stud