数据库DQL语句归纳及练习

查询所有字段并返回

select * from 表名；
select 字段1，2，3...from 表名;
select 字段 as 别名 from 表名；（as可以省略）

去除重复记录

select distinct 字段 from 表名；

条件查询：
where 语句

SELECT * FROM emp WHERE `name` like '_';
UPDATE emp set idcard='1234x' WHERE id =2;
SELECT * FROM emp WHERE idcard LIKE '%x'; 
SELECT * FROM emp WHERE idcard LIKE '____x'; 

聚合函数：（count、max、min、avg、sum）

SELECT COUNT(idcard) FROM emp;
SELECT COUNT(*)FROM emp;
SELECT AVG(age) FROM emp;
SELECT MAX(age) FROM emp;
SELECT MIN(age) FROM emp;
SELECT SUM(age) FROM emp WHERE workaddress='重庆';

分组查询：group by
select 字段列表 from 表名【where条件】 group by 分组字段名【having 分组后过滤条件】；

SELECT gender FROM emp GROUP BY gender;
SELECT MAX(`name`),gender,COUNT(*) FROM emp GROUP BY gender;
SELECT gender,AVG(age)FROM emp GROUP BY gender;
SELECT workaddress,COUNT(workaddress) FROM emp GROUP BY workaddress;
SELECT COUNT(workaddress),age FROM emp GROUP BY age;
SELECT workaddress,COUNT(*) FROM emp WHERE age<=18 GROUP BY workaddress HAVING COUNT(*)>=3;

排序查询：
select 字段列表 from 表名 order by 字段1 排序方式1，字段2 排序方式2；
排序方法：
ASC:升序（默认）
DESC:降序

SELECT * FROM emp ORDER BY age ASC;
SELECT * FROM emp ORDER BY age;
SELECT * FROM emp ORDER BY id ASC;
SELECT *FROM emp ORDER BY age DESC;
SELECT * FROM emp ORDER BY entrydate;
SELECT * FROM emp ORDER BY age,entrydate DESC;

分页查询：
limit
select 字段列表 from 表名 LIMIT 起始索引，查询记录数；
起始索引=（页码-1）*每页展示记录数

SELECT * FROM emp LIMIT 0,10;
SELECT * FROM emp LIMIT 10,10;
SELECT * FROM emp LIMIT 0,3;
SELECT * FROM emp LIMIT 4;