语法:
select
字段列表
from
表名列表
where
条件列表
group by
分组字段
having
分组之后的条件
order by
排序
limit
分页限定
基础查询
select 字段名1 ,字段名2. . . from 表名;
select * from 表名;
distinct
- 一般可以使用四则运算计算一些列的值。(一般只会进行数值型的计算)
- ifnull( 表达式1 , 表达式2 ) :null 参与的运算,计算结果都为null
- 表达式1 :哪个字段需要判断是否为null
- 如果该字段为null 后的替换值。
as :as 也可以省略
条件查询
> 、< 、<= 、>= 、= 、<>
BETWEEN . . . AND
IN ( 集合)
LIKE :模糊查询
占位符:
* _:单个任意字符
* % :多个任意字符
IS NULL
and 或 &&
or 或 ||
not 或 !
SELECT * FROM student WHERE age > 20 ;
SELECT * FROM student WHERE age >= 20 ;
SELECT * FROM student WHERE age = 20 ;
SELECT * FROM student WHERE age != 20 ;
SELECT * FROM student WHERE age <> 20 ;
SELECT * FROM student WHERE age >= 20 && age <= 30 ;
SELECT * FROM student WHERE age >= 20 AND age <= 30 ;
SELECT * FROM student WHERE age BETWEEN 20 AND 30 ;
SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25
SELECT * FROM student WHERE age IN ( 22 , 18 , 25 ) ;
SELECT * FROM student WHERE english = NULL ;
- 不对的。null 值不能使用 = (!= ) 判断
SELECT * FROM student WHERE english IS NULL ;
SELECT * FROM student WHERE english IS NOT NULL ;
SELECT * FROM student WHERE NAME LIKE '马%' ;
SELECT * FROM student WHERE NAME LIKE "_化%" ;
SELECT * FROM student WHERE NAME LIKE '___' ;
SELECT * FROM student WHERE NAME LIKE '%德%' ;
排序查询
order by 排序字段1 排序方式1 , 排序字段2 ,排序方式2.
1. ASC :默认,不写也是ASC :按照升序的方式排列;
2. DESC :按照降序的方式排列;
要求按照工资由高到低排序,如果工资相同,则按照雇佣日期由早到晚排序
SELECT * FROM emp ORDER BY sal DESC , hiredate ASC ;
聚合查询
将一列数据作为一个整体,进行纵向的计算 COUNT:计算统计个数,一般选择非空的列进行计算,例如:主键
SELECT COUNT ( 字段名) FROM student;
SELECT MAX ( 字段名) FROM student;
SELECT MIN ( 字段名) FROM student;
SELECT SUM ( 字段名) FROM student;
SELECT AVG ( math) FROM student;
如果某一个字段的值为空可以这么写,将其替换为指定数值,例如0
SELECT COUNT ( IFNULL( math, 0 ) ) FROM student;
分组查询
SELECT 要分组的字段 , MAX ( 被用来分组的字段) FROM 表名 WHERE 被用来分组的的字段的限定条件 GROUP BY 要分组的字段;