SQL语句查询关键字

一：SQL表中操作字段相关的语句

1.新增字段

1.尾部加入：

• alter table 表名 add 字段名 字段类型 约束条件;
• eg:alter table t11 add gender varchar(32);

2.指定位置插入：

• alter table 表名 add 字段名 字段类型 after 字段名;
• eg:alter table t11 age int after id;

3.头部插入：

• alter table 表名 add 字段名 字段类型 first;
• eg:alter table t11 add hobby varchar(32) first;

2.修改字段

1修改字段：.alter table 表名 change 字段名 新字段名 字段类型；

eg: alter table t11 change hobby hobby1 varchar(32);

2.修改字段类型：alter table 表名 modify 字段名 新的字段类型；

eg: alter table t11 modify hobby1 char(32);

3.删除字段

alter table 表名 drop 字段名;

eg: alter table t11 drop hobby1;

二：查询关键字之select 和 from

1.select

• 选择要操作的字段

2.from

• 选择要操作的表

三：查询关键字之where筛选

前提：先建一下表并且插入给出的数据

1.数据准备(直接拷贝)
	create table emp(
      id int not null unique auto_increment,
      name varchar(20) not null,
      sex enum('male','female') not null default 'male', #大部分是男的
      age int(3) unsigned not null default 28,
      hire_date date not null,
      post varchar(50),
      post_comment varchar(100),
      salary double(15,2),
      office int, #一个部门一个屋子
      depart_id int
    );
	#三个部门：教学，销售，运营
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
    ('jason','male',18,'20170301','浦东第一帅形象代言',7300.33,401,1), #以下是教学部
    ('tom','male',78,'20150302','teacher',1000000.31,401,1),
    ('kevin','male',81,'20130305','teacher',8300,401,1),
    ('tony','male',73,'20140701','teacher',3500,401,1),
    ('owen','male',28,'20121101','teacher',2100,401,1),
    ('jack','female',18,'20110211','teacher',9000,401,1),
    ('jenny','male',18,'19000301','teacher',30000,401,1),
    ('sank','male',48,'20101111','teacher',10000,401,1),
    ('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
    ('呵呵','female',38,'20101101','sale',2000.35,402,2),
    ('西西','female',18,'20110312','sale',1000.37,402,2),
    ('乐乐','female',18,'20160513','sale',3000.29,402,2),
    ('拉拉','female',28,'20170127','sale',4000.33,402,2),
    ('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
    ('程咬金','male',18,'19970312','operation',20000,403,3),
    ('程咬银','female',18,'20130311','operation',19000,403,3),
    ('程咬铜','male',18,'20150411','operation',18000,403,3),
    ('程咬铁','female',18,'20140512','operation',17000,403,3);
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

1练习：

# 1.查询id大于等于3小于等于6的数据
select * from emp where id>=3 and id<=6;
select * from emp where id between 3 and 6;

# 2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary in (20000,18000,17000);
select * from emp where salary=20000 or salary=18000 or salary=17000;

# 3.查询员工姓名中包含o字母的员工姓名和薪资
select name, salary from emp where name like '%o%';

# 4.查询员工姓名是由四个字符组成的员工姓名与其薪资
select name, salary from emp where name like '____';
select name, salary from emp where char_length(name) = 4;

# 5.查询id小于3或者大于6的数据
select * from emp where id<3 or id>6;
select * from emp where id not between 3 and 6;

# 6.查询薪资不在20000，18000，17000范围的数据
select * from emp where salary not in (20000,18000,17000);

# 7.查询岗位描述为空的员工名与岗位名
select name,post from emp where post_comment is NULL;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

四：查询关键字之group by 分组

1.定义

• 按照一些指定的条件将单个单个的数据分为一个个整体

注意：分组之后是以组为查询单位，所以正常情况下select后面跟的是组，如果是单个字段会直接报错，想要跟字段名的话需要借助一些特殊方法

• set global sql_mode=‘strict_trans_tables,only_full_group_by’;

2.配合分组常见使用的有聚合函数

​ max 最大值
​ min 最小值
​ sum 总和
​ count 计数
​ avg 平均

3.练习

# 1.每个部门的最高工资
select post,max(salary) from emp group by post;
select post as '部门', max(salary) as '最高薪资' from emp group by post;

# 2.每个部门的最低工资
select post,min(salary) from emp group by post;

# 3.每个部门的平均工资
select post, avg(salary) from emp group by post;

# 4.每个部门的工资和
select post, sum(salary) from emp group by post;

# 5.每个部门的人数
select post,count(id) from emp group by post;


# 6.查询分组之后的部门名称和每个部门下所有的学生姓名
select post, group_concat(name) from emp group by post;

# 7.给查询出来的每个数据加上自定义的字符
select post, group_concat(name, '$￥') from emp group by post;

# 8.同时查询多个数据，并且自定义分割符号
select post, group_concat(name,'|',salary) from emp group by post;
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

五：查询关键字之having过滤

1.区分where和having

• where用于分组之前的筛选
• having用于分组之后的筛选

2.练习

# 1.统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门
select post, avg(salary) from emp where age>30 group by post having avg(salary)>10000;
1
2

六：查询关键字之distinct 去重

去重的前提是数据必须一模一样

select distinct age from emp;
1

七：查询关键字之order by排序

# 1.默认升序
select * from emp order by salary asc;
select * from emp order by salary;

# 2.降序
select * from emp order by salary desc;

# 3.先按照age降序排，在年纪相同的情况下再按照薪资升序排
select * from emp order by age desc,salary;
select * from emp order by age desc,salary asc;

# 4.统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
select post,avg(salary) from emp where age>10 group by post having avg(salary)>1000 order by avg(salary);
1
2
3
4
5
6
7
8
9
10
11
12
13

八：查询关键字之limit分页

# 1.限制展示条数
select * from emp limit 5;

# 2.自定义从第几条开始展示几条数据(分页显示)
select * from emp limit 5,3;
1
2
3
4
5

九：查询关键字之regexp正则

# 1.查询以j开头，以n或者y结尾的名字
select * from emp where name regexp '^j.*(n|y)$';
1
2

十：多表查询

前提：

#建表
create table dep1(
    id int primary key auto_increment,
    name varchar(20) 
);

create table emp1(
    id int primary key auto_increment,
    name varchar(20),
    gender enum('male','female') not null default 'male',
    age int,
    dep_id int
);

#插入数据
insert into dep1 values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'安保')
;

insert into emp1(name,gender,age,dep_id) values
('jason','male',18,200),
('dragon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);
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

1.子查询

• 将一张表的查询结果用括号括起来当作另一条SQL语句的查询条件

# 1.查询jason的部门名称
select name from dep1 where id=(select dep_id from emp1 where name='jason');
1
2

2.连表操作

1.定义：

• 将要查询的表拼接在一起形成一张表，在这张表中进行查询操作

2.几种连表的方式：

• inner join 内连接
  只拼接两边都有的字段数据
• left join 左连接
  以左表为基准 展示所有的数据 没有对应则NULL填充
• right join 右连接
  以右表为基准 展示所有的数据 没有对应则NULL填充
• union 全连接

# 1.内连接
select * from emp1 inner join dep1 on emp1.dep_id = dep1.id;

# 2.左连接
select * from emp1 left join dep1 on emp1.dep_id = dep1.id;

# 3.右连接
select * from emp1 right join dep1 on emp1.dep_id = dep1.id;

# 4.全连接
select * from emp1 left join dep1 on emp1.dep_id = dep1.id
union
select * from emp1 right join dep1 on emp1.dep_id = dep1.id;
1
2
3
4
5
6
7
8
9
10
11
12
13

作业：

    1. 查询岗位名以及岗位包含的所有员工名字
    select post, group_concat(name) from emp group by post;
    2. 查询岗位名以及各岗位内包含的员工个数
    select post, count(id) from emp group by post;
    3. 查询公司内男员工和女员工的个数
    select sex,count(id) from emp group by sex; 
    4. 查询岗位名以及各岗位的平均薪资
    select post, avg(salary) from emp group by post;
    5. 查询岗位名以及各岗位的最高薪资
    select post, max(salary) from emp group by post;
    6. 查询岗位名以及各岗位的最低薪资
    select post, min(salary) from emp group by post;
    7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
    select sex, avg(salary) from emp group by sex;
    8. 统计各部门年龄在30岁以上的员工平均工资
    select post, avg(salary) from emp where age>30 group by post;
    9. 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
    select post, avg(salary) from emp where age>10 group by post having avg(salary)>1000 order by avg(salary); 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18