Normal Day

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
-- 创建数据库
create database employee;

-- 展示所有数据库
show databases;

-- 进入数据库
use employee;

-- 展示当前数据库
select database();

-- 创建表
create table employee(
    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 '员工表';

-- 展示当前数据库所有表
show tables ;

-- 查看指定表结构
desc employee;

-- 添加字段
alter table employee add nickname varchar(50) comment '昵称';

-- 修改数据类型
alter table employee modify nickname char(30);

-- 修改字段名和数据类型
alter table employee change nickname nick varchar(30);

-- 删除字段
alter table employee drop nick;

-- 修改表名
alter table employee rename to emp;
show tables;

-- 删除表
drop table employee;

-- 删除指定表并重新建表
truncate table employee;

-- 给指定字段添加数据
insert into emp values (1,'00001','柳岩','女',20,'12345678901245678','北京','2000-01-01'),
                       (2,'00002','张无忌','男',18,'12345678901245670','北京','2005-09-01'),
                       (3,'00003','韦一笑','男',38,'123456789712345670','上海','2005-08-01'),
                       (4,'00004','赵敏','女',18,'123456757123845670','北京','2009-12-01'),
                       (5,'00005','小昭','女',16,'123456769012345678','上海','2007-07-01'),
                       (6,'00006','杨逍','男',28,'12345678931234567X','北京','2006-01-01'),
                       (7,'00007','范瑶','男',40,'123456789212345670','北京','2005-05-01'),
                       (8,'00008','黛绮丝','女',38,'123456157123645670','天津','2015-05-01'),
                       (9,'00009','范冰冰','女',45,'123156789012345678','北京','2010-04-01'),
                       (10,'00010','陈友谅','男',53,'123456789012345670','上海','2011-01-01'),
                       (11,'00011','张士诚','男',55,'123567897123465670','江苏','2015-05-01'),
                       (12,'00012','常遇春','男',32,'123446757152345670','北京','2004-02-01'),
                       (13,'00013','张三丰','男',88,'123656789012345678','江苏','2020-11-01'),
                       (14,'00014','灭绝','女',65,'123456719012345670','西安','2019-05-28'),
                       (15,'00015','胡青牛','男',70,'12345674971234567X','西安','2018-04-01'),
                       (16,'00016','周芷若','女',18,null,'北京','2012-06-01');
select * from emp;

-- 修改表的数据
update emp set name='范贤',age=19 where name='范冰冰';

-- 删除数据
delete from emp where name='范贤';

-- 查询字段
select id,name,gender from emp;
select * from emp;

-- 字段设置别名
select workno as '编号',name as '姓名' from emp;
select workno '编号',name '姓名' from emp;

-- 去除重复记录
select distinct workaddress from emp;

-- 条件查询
select * from emp where idcard is null;
select * from emp where idcard is not null;
select * from emp where age >= 15 && age <=20;
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 min(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 < 45 group by workaddress having count(*)>=3;
select workaddress, count(*) address_count from emp where age < 45 group by workaddress having address_count>=3;
select workaddress,gender,count(*) from emp group by gender, workaddress;

-- 排序查询
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;
select * from emp limit 10,10;

-- 案例
select * from emp where age in (20,21,22,23);
select * from emp where gender='男' && age >=20 && age <=40 && 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='男' && age between 20 and 40 order by age asc ,entrydate asc limit 0,5;
select name,age from emp where age >15 order by age asc;