DQL数据单表查询语句

常用DQL(数据查询语言)的操作

DQL(Data Query Language)：数据查询语言，查询数据库中表的记录。

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

(1)SELECT查询字段

SELECT * FROM person;

SELECT name, salary FROM person;

SELECT name n, salary s FROM person;

SELECT salary, (salary+100) s FROM person;

SELECT db01.person.name FROM person;

(2)FROM查询表

注意：如果给表起别名、在通过表名引用字段的时候就必须使用别名。

SELECT * FROM person;

SELECT person.name FROM person;

SELECT t.name FROM person t;

(3)WHERE查询条件

比较运算符：6个

SELECT * FROM person WHERE salary > 3000;

SELECT * FROM person WHERE salary >= 3000;

SELECT * FROM person WHERE salary < 3000;

SELECT * FROM person WHERE salary

SELECT * FROM person WHERE salary = 3000;

SELECT * FROM person WHERE salary != 3000;

逻辑运算符：3个

AND逻辑与：表示并且的意思、多个条件都满足。

SELECT * FROM person WHERE salary > 3000 AND salary < 5000 AND sex='男';

OR逻辑或：表示或者的意思、多个条件满足一个即可。

SELECT * FROM person WHERE salary = 3100 OR salary = 4100 OR salary=8100;

NOT逻辑非：表示取反、需要和其它运算符一起使用。

特殊运算符：4个

BETWEEN AND用于数字或日期区间的查询

SELECT * FROM person WHERE salary BETWEEN 3100 AND 8100;

SELECT * FROM person WHERE salary NOT BETWEEN 3100 AND 8100;

SELECT * FROM person WHERE salary >= 3100 AND salary

IN用于集合：in (数据1,数据2)

SELECT * FROM person WHERE name in ('张三', '李四');

SELECT * FROM person WHERE name NOT in ('张三', '李四');

SELECT * FROM person WHERE name = '张三' OR name = '李四';

LIKE用于字符或日期的模糊查询、必须和匹配符%或_一起使用才有效。

%匹配【零个或多个】任意字符。

_匹配【一个】任意字符。

SELECT * FROM person WHERE name LIKE '张%';

SELECT * FROM person WHERE name LIKE '%张';

SELECT * FROM person WHERE name LIKE '%张%';

SELECT * FROM person WHERE name LIKE '_a__';

SELECT * FROM person WHERE name NOT LIKE '_a__';

NULL空、表示未知、不知道、必须和is或is not连用查询。

INSERT INTO person(name) VALUES(NULL);

SELECT * FROM person WHERE name is null;

SELECT * FROM person WHERE name is NOT null;

(4)GROUP BY分组查询

理解：相同的是一组。显示的组信息、相同有几组最终就显示几条记录。

注意：如果按照某个字段分组、select后边只能写分组的组名或聚合函数。

SELECT sex, max(salary) ,min(salary) ,count(*) ,sum(salary) ,avg(salary)

FROM person

GROUP BY sex

(5)HAVING分组条件

注意区分：where是在分组之前过滤每一条记录。HAVING是在分组之后进行过滤筛选。

SELECT sex, count(*) c FROM person WHERE salary > 3000 GROUP BY sex HAVING c = 3;

(6)ORDER BY排序

默认是升序

SELECT * FROM person ORDER BY salary;

SELECT * FROM person ORDER BY salary ASC;

降序

SELECT * FROM person ORDER BY salary DESC;

注意：排序如果是多个字段、先按照第一个字段排序、再按照第N个字段排序

SELECT * FROM person ORDER BY birthday, salary DESC;

(7)DISTINCT返回唯一不同的值

SELECT DISTINCT salary FROM person;

SELECT DISTINCT name, salary FROM person;

(8)LIMIT限制返回的行数

排名功能：LIMIT n：表示返回前n条记录

注意：先排序、再截取

SELECT * FROM person LIMIT 5;

SELECT * FROM person ORDER BY salary DESC LIMIT 3;

分页功能：LIMIT m, n：从第m个记录开始、返回n条。

注意：SELECT * FROM person LIMIT (页码-1)*每页条数, 每页条数;