Mysql学习笔记

1、MySQL学习笔记1 查询语句1.1 简单查询1.1.1 distinct删除重复行SELECT语句中使用ALL或distinct选项来显示表中符合条件的所有行或删除其中重复的数据行，默认为ALL。使用distinct选项 时，对于所有重复的数据行在SELECT返回的结果集合中只保留一行。distinct一般需要处理单一字段时使用，不能用于多个字段。例如：select   distinct   ID,AA,BB   from   tName select 

2、  distinct   ID   from   tName 区别在于: 第一种情况是把三个字段的内容都相同的记录认为是相同的记录,合并一条，如果有一个字段是不同的，认为不是相同，不合并。我要消除name字段值重复的记录，同时又要得到id字段的值，其中id是自增字段。 如何用distinct消除重复记录的同时又能选取多个字段值？ select distinct name from t1能消除重复记录，但只能取一个字段，现在要同时取id,name这2个字段的值。

3、60; 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

4、PERCENT时，说明n是表示一百分数，指定返回的行数等于总行数的百分之几。例如: SELECT TOP 2 * FROM testtable SELECT TOP 20 PERCENT * FROM testable1.1.3 WHERE子句设置查询条件WHERE子句可包括各种条件运算符:1. 比较运算符(大小比较):>、>=、=、<、<=、<>、!>、!<2. 范围运算符(表达式值是否在指定的范围):BETWEENANDNOT BETWEENAND3. 列表运算符(判断表达式是否为列表中的指定项):IN (项1,项2)NOT IN (项1,项

5、2)4. 模式匹配符(判断值是否与指定的字符通配格式相符):LIKENOT LIKE常用配字符:Ø 百分号%:可匹配任意类型和长度的字符，如果是中文，请使用两个百分号即%。Ø 下划线_:匹配单个任意字符，它常用来限制表达式的字符长度。Ø 方括号:指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。:其取值也和相同，但它要求所匹配对象为指定字符以外的任一个字符。5. 空值判断符(判断表达式是否为空):IS NULLNOT IS NULL6. 逻辑运算符(用于多条件的逻辑连接):NOTANDOR1.1.4 Order by排序使用ORDER BY子句对查询返回

6、的结果按一列或多列排序。ORDER BY子句的语法格式为:ORDER BY column_name ASC|DESC ,n其中ASC表示升序，为默认值，DESC为降序。ORDER BY不能按ntext、text和image数据类型进行排序。1.1.5 COUNT()函数 count(*)对行的数目进行计算，包含NULL。count(column)对特定的列的值具有的行数进行计算，不包含NULL值。count()还有一种使用方式，count(1)这个用法和count(*)的结果是一样的。1.1.6 查询字段为NULL在mysql中，查询某字段为空时，不可用 = null，而是 is n

7、ull，不为空则是 is not null。具体格式如下:select * from table name where column name is null;select * from table name where column name is not null;错误的写法：select * from  table name  wh

8、ere  column name=null;select * from  table name  where length(column name)=0;1.1.7 IFNULL、ISNULL和NULLIF区别IFNULL用法说明：IFNULL(expr1,expr2)如果expr1不是NULL，IFNULL()返回expr1，否则它返回expr2。IFNULL()返回一个数字或字符串值，取决于它被使用的上下文环境。ISNULL用法说明：ISNULL(expr)如expr为n

9、ull，那么isnull()的返回值为1，否则返回值为0。NULLIF用法说明：NULLIF(expr1,expr2)如果expr1 = expr2成立，那么返回值为NULL，否则返回值为expr1。1.1.8 case when语句SELECT                case            #如果  

10、  when sex='1' then '男' #sex='1'，则返回值'男'    when sex='2' then '女' #sex='2'，则返回值'女'     else '其他'        #其他的返回'其他    end   &

11、#160;        #结束from  sys_user            整体理解： 在sys_user表中如果sex='1'，则返回值'男'如果sex='2'，则返回值'女' 否则返回'其他。用法一：SELECT     CASE WHEN STATE = '1

12、9; THEN '成功'     WHEN STATE = '2' THEN '失败'    ELSE '其他' END  FROM  SYS_SCHEDULER用法二：SELECT STATE CASE WHEN '1' THEN '成功'     WHEN '2' THEN '失败'   

13、0;ELSE '其他' END  FROM  SYS_SCHEDULER1.2 嵌套查询（子查询）嵌套查询的意思是，一个查询语句(select-from-where)查询语句块可以嵌套在另外一个查询块的where子句中，称为嵌套查询。其中外层查询也称为父查询，主查询。内层查询也称子查询，从查询。嵌套查询的工作方式是：先处理内查询，由内向外处理，外层查询利用内层查询的结果嵌套查询不仅仅可以用于父查询select语句使用。还可以用于insert、update、delete语句或其他子查询中。1.2.1 子查询任何可以使用表达式的地方都可以使用子查询，只要它返回的

14、是单个值。如果某个表只出现在子查询中二不出现在外部查询中，那么该表的列就无法包含在输出中。select name,age from person where age > ( select age from person where name = '孙权')1.2.2 in嵌套查询in关键字用于where子句中用来判断查询的表达式是否在多个值的列表中。返回满足in列表中的满足条件的记录。示例：select name from person where countryid in ( select countryid from country where countryname

15、 = '魏国')输出结果为：1.2.3 some嵌套查询some在sql中的逻辑运算符号，如果在一系列比较中，有些值为True，那么结果就为True。some的语法是：<表达式> =|<>|!=|>|>=|!>|<|<=|!<some(子查询)示例：select name from person where countryid = some -用等号和以下查询到的值比较，如果与其中一个相等，就返回( select countryid from country where countryname = '魏国

16、9;)输出结果为：1.2.4 all嵌套查询all是sql中的逻辑运算符好，如果一系列的比较都为true，那么结果才能为true。<表达式> =|<>|!=|>|>=|!>|<|<=|!<all(子查询)示例：select name from person where countryid > all -当countryid大于以下返回的所有id，此结果才为True，此结果才返回( select countryid from country where countryname = '魏国')输出结果为：1.2.5

17、exists嵌套查询exists是sql中的逻辑运算符号。如果子查询有结果集返回，那么就为True。exists代表“存在”的意义，它只查找满足条件的那些记录。一旦找到第一个匹配的记录后，就马上停止查找。exists子查询其中子查询是一个首先的select语句，不允许有compute子句和into关键字。exists 的意思是，子查询是否有结果集返回。例如：SELECT * FROM PersonWHERE exists(SELECT 1 -SELECT 0 SELECT NULL 返回结果都一样，因为这三个子查询都有结果集返回，因此总是True SELECT * FROM Person照常执

18、行)但是如果子查询中因为加了条件而没有结果集返回，则主语句就不执行了：SELECT * FROM PersonWHERE exists( SELECT * FROM Person WHERE Person_Id = 100 -如果不存在Person_Id的记录，则子查询没有结果集返回，主语句不执行)1.3 连接查询1.3.1 左连接（LEFT JOIN）LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。在lef

19、t join下，on和where两者的区别：on是在生成临时表的时候使用的条件，不管on的条件是否起到作用，都会返回左表 (table_name1) 的行。where则是在生成临时表之后使用的条件，此时已经不管是否使用了left join了，只要条件不为真的行，全部过滤掉。SQL语句：select a.id,a.No, from table1 a left join table2 b on (a.No = b.No and ='aaa');select a.id,a.No, from table1 a left join table2 b o

20、n (a.No = b.No) where ='aaa'表1：table2idNo1n12n23n3表2：table2Nonamen1aaan2bbbn3ccc第一个结果集：|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 =aaa)。然后找到a的数据（即使不符合b表的规则），生成临时表返回用户。 

21、第二个sql的执行流程：首先生成临时表，然后执行where过滤=aaa不为真的结果集，最后返回给用户。因为on会首先过滤掉不符合条件的行，然后才会进行其它运算，所以按理说on是最快的。在多表查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里。对于JOIN参与的表的关联操作，如果需要不满足连接条件的行也在我们的查询范围内的话，我们就必需把连接条件放在ON后面，而不能放在WHE

22、RE后面，如果我们把连接条件放在了WHERE后面，那么所有的LEFT,RIGHT,等这些操作将不起任何作用，对于这种情况，它的效果就完全等同于INNER连接。对于那些不影响选择行的条件，放在ON或者WHERE后面就可以。记住：所有的连接条件都必需要放在ON后面，不然前面的所有LEFT,和RIGHT关联将作为摆设，而不起任何作用。select pj.id,,IFNULL(COUNT(vm1.vmname),0) as '虚拟机数量'from project pjLEFT JOIN(select ject_id, as vmname from vm_host vmwhere vm.status = 'A') vm1on (pj.id=ject_id )where pj.status != 'P'GROUP BY pj.id1.3.2 右连接（RIGHT JOIN）1.3.3 内连接内连接，也叫等值连接，inner join产生同时符合A和B的一组数据。mysql> select * from A