编辑代码

CREATE DATABASE test;
use test;
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', '韦一笑6', '男', 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 max(age) from emp;
select min(age) from emp;
select sum(age) from emp;
select avg(age) from emp;
select count(idcard) from emp;
select id,name from emp;
create table cqj(
    id varchar(255)
);
show tables;
drop table cqj;
show tables;
select name from emp where name  like '%6' ;
select id,name,idcard from emp where age between 20 and 60;
select gender ,count(*) from emp group by gender;
select gender,avg(age) from emp group by gender;
select workaddress,count(*) from emp group by workaddress;
select workaddress,count(*) from emp where age<45 group by workaddress having count(*)>3;
select workaddress,count(*) from emp where age<45 group by workaddress;
select workaddress,count(*) from emp where name like '%6' group by workaddress;
show tables;
show DATABASEs;
select name,workno,age from emp;
select * from emp;
select workaddress as'工作地址' from emp;
select distinct workaddress from emp;
select * from emp where age<32;
select*from emp where age<27 and gender='男';
select * from emp where name like '___';
select * from emp where idcard like '%x';
select count(*) from emp;
select avg(age) from emp;
select max(age) from emp;
select sum(age) from emp where workaddress='北京';
select gender, count(*) from emp group by gender;
select gender, avg(age) from emp group by gender;
select workaddress, count(*) from emp where age<40 group by workaddress having count(*)>=2;
select workaddress,gender, count(*) from emp group by workaddress,gender;
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 10;
select * from emp limit 10,10;
select * from emp where age>=20 and age<=23 ;
select name from emp where gender='男' and (age>=20 and age<=40) and name like '___';
select gender,count(*) from emp where age<60 group by gender;
select name,age from emp where age<=35 order by age asc, entrydate desc;
select * from emp where gender='男' and (age>=20 and age<=40) order by age asc,entrydate desc limit 5;

select * from mysql.user;