select查询基础

  • DQL(Data QueryLanguage)：数据查询语⾔，通俗点讲就是从数据库获取数据的，按照DQL的语法给数据库发送⼀条指令，数据库将按需求返回数据
  • 基本语法 select 查询的列 from 表名;
    • select语句中不区分⼤⼩写，SELECT和select、FROM和from效果⼀样
    • 查询的结果放在⼀个表格中，表格的第1⾏称为列头，第2⾏开始是数据，类属于⼀个⼆维数组
  • 查询常量 select 常量值1,常量值2,常量值3;
  • 查询表达式 select 1+2,3*10,10/3;
  • 查询函数 select 函数;，如 select mod(10,4),isnull(null),ifnull(null,'第⼀个参数为空返回这个值'),ifnull(1,'第⼀个参数为空返回这个值，否则返回第⼀个参数');
    • mod函数，对两个参数取模运算
    • isnull函数，判断参数是否为空，若为空返回1，否则返回0
    • ifnull函数，2个参数，判断第⼀个参数是否为空，如果为空返回第2个参数的值，否则返回第1个参数的值
  • 查询指定的字段 select 字段1,字段2,字段3 from 表名;
  • 查询所有列 select * from 表名，*表⽰返回表中所有字段
  • 列别名：在创建数据表时，⼀般都会使⽤英⽂单词或英⽂单词缩写来设置字段名，在查询时列名都会以英⽂的形式显⽰，这样会给⽤户查看数据带来不便，这种情况可以使⽤别名来代替英⽂列名，增强阅读性 select 列 [as] 别名 from 表;
    • 使⽤双引号创建别名：select a "列1",b "列2" from test1;
    • 使⽤单引号创建别名：select a '列1',b '列2' from test1;
    • 不⽤引号创建别名：select a 列1,b 列2 from test1;
    • 使⽤as创建别名：select a as 列1,b as 列 2 from test1;
    • 别名中有特殊符号的，⽐如空格，此时别名必须⽤引号引起来
  • 懵逼⽰例，看效果：
  1. -- 认真看⼀下第1个和第3个返回的结果（列头和数据），是不是懵逼状态，建议这种的最好使⽤as，as后⾯跟上别名
  2. mysql> select 'a' 'b';
  3. +----+
  4. | a |
  5. +----+
  6. | ab |
  7. +----+
  8. 1 row in set (0.00 sec)
  9. mysql> select 'a' b;
  10. +---+
  11. | b |
  12. +---+
  13. | a |
  14. +---+
  15. 1 row in set (0.00 sec)
  16. mysql> select 'a' "b";
  17. +----+
  18. | a |
  19. +----+
  20. | ab |
  21. +----+
  22. 1 row in set (0.00 sec)
  23. mysql> select 'a' as "b";
  24. +---+
  25. | b |
  26. +---+
  27. | a |
  28. +---+
  • 表别名 select 别名.字段,别名.* from 表名 [as] 别名;
  1. mysql> select t.* from test1 as t;
  2. +---+---+
  3. | a | b |
  4. +---+---+
  5. | 1 | a |
  6. | 2 | b |
  7. | 3 | c |
  8. +---+---+
  10. mysql> select * from test1 as t;
  11. +---+---+
  12. | a | b |
  13. +---+---+
  14. | 1 | a |
  15. | 2 | b |
  16. | 3 | c |
  17. +---+---+
  • 总结
    • 建议别名前⾯跟上as关键字
    • 查询数据的时候，避免使⽤select *，建议需要什么字段写什么字段

select条件查询

  • 条件查询 select 列名 from 表名 where 列 运算符 值，where后⾯跟上⼀个或者多个条件，条件是对前⾯数据的过滤，只有满⾜where后⾯条件的数据才会被返回
  • 条件查询运算符
    • 等于（=）select 列名 from 表名 where 列 = 值;
    • 不等于（<>、!=） select 列名 from 表名 where 列 <> 值; 或 select 列名 from 表名 where 列 != 值;，<> 这个是最早的⽤法，!=是后来才加上的，两者意义相同，在可移植性上前者优于后者
    • ⼤于（>） select 列名 from 表名 where 列 > 值;，数值按照⼤⼩⽐较，字符按照ASCII码对应的值进⾏⽐较，⽐较时按照字符对应的位置⼀个字符⼀个字符的⽐较
  • 逻辑查询运算符
    • AND（并且）select 列名 from 表名 where 条件1 and 条件2;
    • OR（或者）select 列名 from 表名 where 条件1 or 条件2;
  • like（模糊查询）select 列名 from 表名 where 列 like pattern;
    • pattern中可以包含通配符，有以下通配符：
    • %：表⽰匹配任意⼀个或多个字符
    • _：表⽰匹配任意⼀个字符
  • BETWEEN AND(区间查询) selec 列名 from 表名 where 列名 between 值1 and 值2;
    • 返回对应的列的值在[值1,值2]区间中的记录
    • 使⽤between and可以提⾼语句的简洁度
    • 两个临界值不要调换位置，只能是⼤于等于左边的值，并且⼩于等于右边的值
  1. -- 下⾯两条sql效果⼀样
  2. select * from stu t where t.age between 25 and 32;
  3. select * from stu t where t.age >= 25 and t.age <= 32;
  • IN查询 select 列名 from 表名 where 字段 in (值1,值2,值3,值4);
    • in 后⾯括号中可以包含多个值，对应记录的字段满⾜in中任意⼀个都会被返回
    • in 列表的值类型必须⼀致或兼容
    • in 列表中不⽀持通配符
  1. -- 需要查询年龄为10岁、15岁、20岁、30岁的⼈
  2. select * from test6 t where t.age=10 or t.age=15 or t.age=20 or
  3. t.age = 30;
  4. select * from test6 t where t.age in (10,15,20,30);
  • NOT IN查询 select 列名 from 表名 where 字段 not in (值1,值2,值3,值4);，和列表中都不匹配的会被返回
  • NULL存在的坑：查询运算符、like、between and、in、not in对NULL值查询不起效
  • NULL值专⽤查询
    • IS NULL（返回值为空的记录） select 列名 from 表名 where 列 is null;
    • IS NOT NULL（返回值不为空的记录） select 列名 from 表名 where 列 is not null;
  • <=>（安全等于）:既可以判断NULL值，又可以判断普通的数值，可读性较低，⽤得较少
  • 经典⾯试题
    • 当name没有NULL值时，返回的结果⼀样
    • 当name有NULL值时，第2个sql查询不出name为NULL的记录
  1. select * from students;
  2. select * from students where name like '%';
  • 总结
    • like中的%可以匹配⼀个到多个任意的字符，_可以匹配任意⼀个字符
    • 空值查询需要使⽤IS NULL或者IS NOT NULL，其他查询运算符对NULL值⽆效
    • 建议创建表的时候，尽量设置表的字段不能为空，给字段设置⼀个默认值
    • <=>（安全等于）玩玩可以，建议少使⽤

排序和分⻚（order by 、limit）

  • 排序语法 select 字段名 from 表名 order by 字段1 [asc|desc],字段2 [asc|desc];
    • 需要排序的字段跟在order by之后；
    • asc|desc表⽰排序的规则，asc：升序，desc：降序，默认为asc；
    • ⽀持多个字段进⾏排序，多字段排序之间⽤逗号隔开。
  • 按别名排序，如 select age '年龄',id as '学号' from stu order by 年龄 asc,学号 desc;
  • 按函数排序，两种示例
    • SELECT id 编号,birth 出⽣⽇期,year(birth) 出⽣年份,name 姓名 from student ORDER BY year(birth) asc,id asc;
    • SELECT id 编号,birth 出⽣⽇期,year(birth) 出⽣年份,name 姓名 from student ORDER BY 出⽣年份 asc,id asc;
    • year函数：属于⽇期函数，可以获取对应⽇期中的年份
    • 第⼀种是在order by中使⽤了函数，第⼆种是使⽤了别名排序
  • where之后进⾏排序，如 select a.id 订单编号,a.price 订单⾦额 from t_order a where a.price>=100 order by a.price desc;
  • limit：⽤来限制select查询返回的⾏数，常⽤于分页等操作，select 列 from 表 limit [offset,] count;
    • offset：表⽰偏移量，通俗点讲就是跳过多少⾏，offset可以省略，默认为0，表示跳过0⾏；范围：[0,+∞)
    • count：跳过offset⾏之后开始取数据，取count⾏记录；范围：[0,+∞)
    • limit中offset和count的值不能⽤表达式
  • 获取最⼤的⼀条记录：先降序，然后取第⼀条记录 select a.id 订单编号,a.price 订单⾦额 from t_order a order by a.price desc limit 1;
  • 获取排名第n到m的记录：我们需要先跳过n-1条记录，然后取m-n+1条记录
  • 分⻚查询：select 列 from 表名 limit (page - 1) * pageSize,pageSize;
  • limit注意事项
    • 不能使⽤表达式，后⾯只能够跟明确的数字
    • 后⾯的2个数字不能为负数
  • 总结
    • order by … [asc|desc]⽤于对查询结果排序，asc：升序，desc：降序，asc|desc可以省
      略，默认为asc
    • limit⽤来限制查询结果返回的⾏数，有2个参数（offset，count），offset：表⽰跳过
      多少⾏，count：表⽰跳过offset⾏之后取count⾏
    • limit中offset可以省略，默认值为0
    • limit中offset 和 count都必须⼤于等于0
    • limit中offset和count的值不能⽤表达式
    • 分页排序时，排序不要有⼆义性，⼆义性情况下可能会导致分页结果乱序，可以在后⾯追加⼀个主键排序

分组查询（group by、having）

  • 分组查询
    • group_function：聚合函数
    • groupbyexpression：分组表达式，多个之间⽤逗号隔开
    • group_condition：分组之后对数据进⾏过滤
    • 分组中，select后⾯只能有两种类型的列：出现在group by后的列；或者使⽤聚合函数的列
  1. SELECT column, group_function,... FROM table
  2. [WHERE condition]
  3. GROUP BY group_by_expression
  4. [HAVING group_condition];
  • 聚合函数
    • max 查询指定列的最大值
    • min 查询指定列的最小值
    • count 统计查询结果的行数
    • sum 求和，返回指定列的总和
    • avg 求平均值，返回指定列数据的平均值
  • 单字段分组，如
  1. SELECT
  2. user_id ⽤户id, COUNT(id) 下单数量
  3. FROM
  4. t_order
  5. GROUP BY user_id;
  • 多字段分组，如
  1. SELECT
  2. user_id ⽤户id, the_year 年份, COUNT(id) 下单数量
  3. FROM
  4. t_order
  5. GROUP BY user_id, the_year;
  • 分组前筛选数据，使⽤where关键字
  1. SELECT
  2. user_id ⽤户id, COUNT(id) 下单数量
  3. FROM
  4. t_order t
  5. WHERE
  6. t.the_year = 2018
  7. GROUP BY user_id;
  • 分组后筛选数据
  1. SELECT
  2. user_id ⽤户id, COUNT(id) 下单数量
  3. FROM
  4. t_order t
  5. WHERE
  6. t.the_year = 2018
  7. GROUP BY user_id
  8. HAVING count(id)>=2;
  10. -- 或
  11. SELECT
  12. user_id ⽤户id, count(id) 下单数量
  13. FROM
  14. t_order t
  15. WHERE
  16. t.the_year = 2018
  17. GROUP BY user_id
  18. HAVING 下单数量>=2;
  • where和having的区别
    • where是在分组（聚合）前对记录进⾏筛选，⽽having是在分组结束后的结果⾥筛选，最后返回整个sql的查询结果
    • 可以把having理解为两级查询，即含having的查询操作先获得不含having⼦句时的sql查询结果表，然后在这个结果表上使⽤having条件筛选出符合的记录，最后返回这些记录，因此，having后是可以跟聚合函数的，并且这个聚集函数不必与select后⾯的聚集函数相同
  • 分组后排序
  1. SELECT
  2. user_id ⽤户id, max(price) 最⼤⾦额
  3. FROM
  4. t_order t
  5. GROUP BY user_id
  6. ORDER BY 最⼤⾦额 desc;
  • where & group by & having & order by & limit ⼀起协作，先后顺序有明确的限制
  1. select 列 from
  2. 表名
  3. where [查询条件]
  4. group by [分组表达式]
  5. having [分组过滤条件]
  6. order by [排序条件]
  7. limit [offset,] count;
  9. -- 比如：
  10. SELECT
  11. user_id ⽤户id, COUNT(id) 下单数量
  12. FROM
  13. t_order t
  14. WHERE
  15. t.the_year = 2018
  16. GROUP BY user_id
  17. HAVING count(id)>=2
  18. ORDER BY 下单数量 DESC
  19. LIMIT 1;
  • mysql分组中的坑
    • 分组中select后⾯的列只能有2种：出现在group by后⾯的列，使⽤聚合函数的列
    • 建议在写分组查询的时候，最好按照标准的规范来写，select后⾯出现的列必须在group by中或者必须使⽤聚合函数
  1. -- 获取每个⽤户下单的最⼤⾦额及下单的年份，输出：⽤户id，最⼤⾦额，年份
  3. -- 错误示例，the_year不符合上⾯说的2条规则
  4. select user_id ⽤户id, max(price) 最⼤⾦额, the_year 年份
  5. FROM t_order t
  6. GROUP BY t.user_id;
  8. -- ⽐较少见，in中使⽤了多字段查询
  9. SELECT user_id ⽤户id, price 最⼤⾦额, the_year 年份
  10. FROM t_order t1
  11. WHERE (t1.user_id , t1.price) IN
  12.     (SELECT t.user_id, MAX(t.price)
  13.     FROM t_order t
  14.     GROUP BY t.user_id);
  16. SELECT user_id ⽤户id, price 最⼤⾦额, the_year 年份
  17. FROM t_order t1, 
  18.     (SELECT t.user_id uid, MAX(t.price) pc
  19.     FROM t_order t
  20.     GROUP BY t.user_id) t2
  21. WHERE t1.user_id = t2.uid AND t1.price = t2.pc;
  • 总结
    • 在写分组查询的时候，最好按照标准的规范来写，select后⾯出现的列必须在group by中或者必须使⽤聚合函数
    • select语法顺序：select、from、where、group by、having、order by、limit，顺序不能搞错了，否则报错
    • in多列查询的使⽤，下去可以试试