学习之SQL语句之DQL（数据库查询语言）

DQL英文全称是Data Query Language(数据查询语言)，数据查询语言，用来查询数据库中表的记录
查询关键字:SELECT

数据准备：
CREATE TABLE emp (
id INT COMMENT “编号”,
workno VARCHAR ( 10 ) COMMENT “工号”,
NAME VARCHAR ( 10 ) COMMENT “姓名”,
gender CHAR ( 1 ) COMMENT “性别”,
age TINYINT UNSIGNED COMMENT “年龄”,
idcard CHAR ( 18 ) COMMENT “身份证号”,
workaddress VARCHAR ( 50 ) COMMENT “工作地址”,
entrydate DATE COMMENT “入职时间”
) COMMENT “员工表”;

INSERT INTO emp ( id, workno, NAME, gender, age, idcard, workaddress, entrydate )
VALUES
( 1, “1”, “柳岩”, “女”, 20, “123456789012345678”, “北京”, “2000-01-01” ),
( 2, “2”, “张无忌”, “男”, 18, “123456789012345670”, “北京”, “2005-09-01” ),
( 3, “3”, “韦一笑”, “男”, 38, “123456789712345670”, “上海”, “2005-08-01” ),
( 4, “4”, “赵敏”, “女”, 18, “123456757123845670”, “北京”, “2009-12-01” ),
( 5, “5”, “小昭”, “女”, 16, “123456769012345678”, “上海”, “2007-07-01” ),
( 6, “6”, “杨道”, “男”, 28, “12345678931234567X”, “北京”, “2006-01-01” ),
( 7, “7”, “范瑶”, “男”, 40, “12345678921234567日”, “北京”, “2005-05-01” ),
( 8, “8”, “黛绮丝”, “女”, 38, “123456157123645670”, “天津”, “2015-05-01”),
( 9, “9”, “范凉凉”, “女”, 45, “123156789012345678”, “北京”, “2010-04-01” ),
( 10, “1”, “陈友谅”, “男”, 53, “123456789012345670”, “上海”, “2011-01-01” ),
( 11, “11”, “张士诚”, “男”, 55, “123567897123465670”, “江苏”, “2015-05-01” ),
( 12, “12”, “常遇春”, “男”, 32, “123446757152345670”, “北京”, “2004-02-01” ),
( 13, “13”, “张三丰”, “男”, 88, “123656789012345678”,“江苏”, “2020-11-01” ),
( 14, “14”, “灭绝”, “女”, 65, “123456719012345670”, “西安”, “2019-05-01” ),
( 15, “15”, “胡青牛”, “男”, 70, “12345674971234567X”, “西安”, “2018-04-01” ),
( 16, “16”,“周芷若”, “女”, 18, NULL, “北京”, “2012-06-01” );

DQL -基本查询

1、查询多个字段

SELECT 字段1，字段2… FROM 表名；
SELECT * FROM 表名；

2、设置别名

SELECT 字段1 [AS 别名],字段2[AS 别名]… FROM 表名；

3、去除重复记录

SELECT DISTINCT 字段列表 FROM 表名；
案例：

– 1.查询指定字段 name，workno，age 返回
select name, workno,age from emp;

– 2.查询所有字段返回
select id, workno, name, gender, age, idcard,workaddress, entrydate from emp;
select * from emp;
– 3.查询所有员工的工作地址，起别名
select workaddress as ‘工作地址’ from emp;
select workaddress “工作地址” from emp;

– 4.查询公司员工的上班地址(不要重复)
select distinct workaddress from emp;

DQL - 条件查询

案例：

DQL -聚合函数

DQL -分组查询

DQL - 排序查询

DQL-分页查询

– DQL练习