mysql 0929

`##############################
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 '身份证带x',
entrydate DATE COMMENT '入职时间'
) COMMENT '员工表';

############################## DQL
drop table if exists emp;

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, '00001', '柳岩666', '女', 20, '123456789012345678', '北京', '2000-01-01');
insert into emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (2, '00002', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01');
insert into emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (3, '00003', '韦一笑', '男', 38, '123456789712345670', '上海', '2005-08-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (4, '00004', '赵敏', '女', 18, '123456757123845670', '北京', '2009-12-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (5, '00005', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (6, '00006', '杨逍', '男', 28, '12345678931234567X', '北京', '2006-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (7, '00007', '范瑶', '男', 40, '123456789212345670', '北京', '2005-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (8, '00008', '黛绮丝', '女', 38, '123456157123645670', '天津', '2015-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (9, '00009', '范凉凉', '女', 45, '123156789012345678', '北京', '2010-04-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (10, '00010', '陈友谅', '男', 53, '123456789012345670', '上海', '2011-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (11, '00011', '张士诚', '男', 55, '123567897123465670', '江苏', '2015-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (12, '00012', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (13, '00013', '张三丰', '男', 88, '123656789012345678', '江苏', '2020-11-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (14, '00014', '灭绝', '女', 65, '123456719012345670', '西安', '2019-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (15, '00015', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (16, '00016', '周芷若', '女', 18, null, '北京', '2012-06-01');

select * from emp;

别名

select name,workno,age from emp; -- 查询name workno age 字段
select * from emp; -- 查询所有
select workaddress '工作地址' from emp; -- 别名
select workaddress as '工作地址' from emp; -- 别名
select distinct workaddress as '工作地址' from emp; -- 去除重复 distinct

where条件

select * from emp where age = 88;
select * from emp where age < 20;
select * from emp where age <= 20 ;
select * from emp where idcard is null;
select * from emp where idcard is not null;
select * from emp where age <> 80; ## !=
select * from emp where age between 15 and 20; ##>=15 and <= 20 >= 15 && <= 20
select * from emp where gender = '女' and age <25 ;
select * from emp where age =18 or age = 20 or age = 40; ## age in(18,20,40);
select * from emp where name like(''); ## name like '' ;
select * from emp where idcard like '%x' ;

聚合函数

select count(id) from emp; ##count(*) , NULL 不会被计算
select avg(age) from emp;
select max(age) from emp;
select min(age) from emp;
select sum(age) from emp where workaddress = '西安';

分组查询

select gender,count() from emp group by gender;
select gender,count(),age,count() from emp group by gender , age;
select gender,avg(age) from emp group by gender;
select workaddress as '工作地址' , count() as address_count from emp where age < 45 group by workaddress having address_count >= 3;

排序

select * from emp order by age asc;
select * from emp order by entrydate desc;
select * from emp order by age asc , entrydate desc;

分页

select * from emp limit 0,10; -- 第一个10条
select * from emp limit 10,10; -- 第二页的10条

---------------------

select * from emp ;
select * from emp where gender = '女' and age in (20,21,22,23);
select * from emp where gender = '男' and age between 20 and 40 and name like '___';
select gender,count(*) from emp where age < 60 group by gender;
select name,age,entrydate from emp where age <=35 order by age asc , entrydate desc;
select * from emp where gender = '男' and age between 20 and 40 order by age asc, entrydate desc limit 0,5;

编写顺序 执行顺序

select 字段列表 4

from 表名列表 1

where 条件列表 2

grroup by 分组字段列表 3

having 分组后条件列表

order by 排序字段列表 5

limit 分页参数 6

验证

select name,age from emp where age > 15 order by age asc;

-- DCL 用户权限

`