sql 多表查询如何建立索引 sql多表查询语句大全讲解

• 操作表的SQL语句补充

• 修改表名
• 新增字段
• 修改字段
• 删除字段

• 表查询关键字

• 数据准备(直接拷贝)
• 查询关键字之select和from
• 查询关键字之where筛选

• where 后面的条件语法整理

• 查询关键字之group by分组

• 配合分组使用的聚合函数
• 关于分组结合聚合函数使用的代码

• 查询关键字之having过滤

• having和where的异同

• 查询关键字之distinct去重
• 查询关键字之order by排序

• order by排序语法整理

• 查询关键字值regexp正则

• 多表查询

• 准备工作
• 子查询
• 连表查询

 

---

操作表的SQL语句补充

修改表名

alter table 旧表名 rename 新表名；

新增字段

 直接添加字段(在已有的字段后面添加)

    alter table 表名 add 字段名 字段类型(数字) 约束条件；

  在指定的字段后面添加字段

    alter table 表名 add 字段名  字段类型(数字) 约束条件 after 已经存在的字段；

  在表的第一行添加字段

    alter table 表名 add 字段名 字段类型(数字) 约束条件 first；

修改字段

修改已有的字段名和字段类型

    alter table 表名 change 旧字段 新字段 新字段数据类型(数字) 约束条件；

  只修改字段类型(modify)

    alter table 表名 modify 字段名 新的字段类型(数字) 约束条件；

删除字段 

  alter table 表名 drop 字段名；

---

表查询关键字

数据准备(直接拷贝)

我们现在准备的代码都是为了讲解表查询关键字所作出的铺垫，方便我们结合实际情况更好的了解表查询关键字的概念和用法！！！

表查询关键字数据准备的代码

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);

结果如下

表查询关键字之select和from

在讲这两个关键字之前我们要先知道SQL语句中关键字的编写顺序和执行顺序是不一致的！！！

         eg：   select name from emp;

先执行from确定表，之后再执行select确定字段

模板：

    select * from 表名 其他操作

（select后的字段可能是实际的 也可能是通过SQL动态产生的 所以可以先用*占位最后再修改）

 

select	自定义查询表中字段对应的数据
from	指定操作的对象(到底是那张表，也有可能是多张表)   多张表我们用逗号隔开

表查询关键字之where筛选

语法 ：

  select 字段名(*) from 表名 where 条件；

关于where后面的条件我直接罗列出来，做了一个总结，具体使用可以对照看着下面的代码。

where 后面的条件语法整理

方法	用法
and， or， not	select 字段名 from 表名 where id<30 and id>10;                                       where id <30 or id=10;                                        where id not <30; 同时，and or not 三者还可以连用
关键字(like)  筛选包含xxx的字段 ('%xxx%')  查询是不是由几个字符组成的('____'这个是下划线，你想找的字符有几个字母就用几个下划线)	select 字段名 from 表名 where 字段名 like '%xxx%'; select 字段名 from 表名 where 字段名 like  '___';
在不在...之间  between A and B    (not between A and B)	select 字段名 from 表名 where 字段名 between A and B                                       where 字段名 not between A and B
在不在...范围内 in(200，100，300)    (not in(200,100,300) )	select 字段名 from 表名 where 字段名 in(200，100，300)                                       where 字段名 not in(200,100,300)
查询字段是不是为空 is NULL      (is not NULL)	select 字段名 from 表名 where 字段名 is NULL                                        where 字段名 is not NULL

以上代码的具体用法看下面的代码

where后面的条件用法代码

# 1.查询id大于等于3小于等于6的数据
select id,name 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 = 20000 or salary = 18000 or salary = 17000;
select * from emp where salary in (20000,18000,17000);  # 简写

# 3.查询员工姓名中包含o字母的员工姓名和薪资
# 在你刚开始接触mysql查询的时候，建议你按照查询的优先级顺序拼写出你的sql语句
"""
先是查哪张表 from emp
再是根据什么条件去查 where name like ‘%o%’
再是对查询出来的数据筛选展示部分 select name,salary
"""
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 not between 3 and 6;

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

# 7.查询岗位描述为空的员工名与岗位名  针对null不能用等号，只能用is
select name,post from emp where post_comment = NULL;  # 查询为空！
select name,post from emp where post_comment is NULL;
select name,post from emp where post_comment is not NULL;

 

---

查询关键字之group by分组

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

以组为单位     set global sql_mode='strict_trans_tables,only_full_group_by'; 语法： """ select 字段名 from 表名 group by 字段名;"""

配合分组使用的聚合函数

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

我们写SQL是否需要使用分组 可以在题目中得到答案
    每个、平均、最大、最小

关于分组结合聚合函数使用的代码

下面的代码自己试着敲打查看结果

分组和聚合函数使用的代码

获取每个部门的最高工资  
# 以组为单位统计组内数据>>>聚合查询(聚集到一起合成为一个结果)
# 每个部门的最高工资
select post,max(salary) from emp group by post;
补充:在显示的时候还可以给字段取别名
select post as '部门',max(salary) as '最高工资' from emp group by post;
as也可以省略 但是不推荐省 因为寓意不明确
# 每个部门的最低工资
select post,min(salary) from emp group by post;
# 每个部门的平均工资
select post,avg(salary) from emp group by post;
# 每个部门的工资总和
select post,sum(salary) from emp group by post;
# 每个部门的人数
select post,count(id) from emp group by post;

查询分组之后的部门名称和每个部门下所有的学生姓名
# group_concat（分组之后用）不仅可以用来显示除分组外字段还有拼接字符串的作用
select post,group_concat(name) from emp group by post;

select post,group_concat(name,"_SB") from emp group by post;

select post,group_concat(name,": ",salary) from emp group by post;

select post,group_concat(salary) from emp group by post;

 

---

查询关键字之having过滤

having和where的异同

同	where与having的功能其实是一样的 都是用来筛选数据
异	where用于分组之前的筛选 having用于分组之后的筛选

为了人为的区分 所以叫where是筛选 having是过滤

语法：

  select 字段名 from 表名 having 条件；

题目：统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门

select post,avg(salary) from emp
        where age >= 30
        group by post
        having avg(salary) > 10000;

---

查询关键字之distinct去重

语法：

  select distinct 字段名 from 表名；

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

select distinct age from emp;

---

查询关键字之order by排序

排序有升序(asc)，降序(dexc)，多字段数据相同排序,默认情况下是升序

同样的，在这个地方我也做个整理，你根据代码对照着敲一下。

order by排序语法整理

排序名子	语法
升序	select * from 表名 order by  字段名 asc；（asc如果不写也默认是升序排列）
降序	select * from 表名 order by  字段名 desc；
多字段数据相同排序	先对一个字段进行排序，再在这个基础上对另一个字段排序 select * from 表名 order by  字段名1 asc/desc; select * from 表名 order by  字段名2 asc/desc;

题目：统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序

select post,avg(salary) from emp
    where age > 10
    group by post
    having avg(salary) > 1000
    order by avg(salary)
    ;

---

查询关键字之limit分页

限制展示条数
  select * from emp limit 3;
查询工资最高的人的详细信息
  先排序，在按照升序或者降序取第一个或者最后一个的值
  select * from emp order by salary desc limit 1;

分页显示
  select * from emp limit 0,5;  # 第一个参数表示起始位置，第二个参数表示的是条数，不是索引位置
  select * from emp limit 5,5;

---

查询关键字值regexp正则

具体可以参考python的正则

正则表达式的特殊符号

.            匹配除换行符以外的任意字符 \w         匹配字母或数字或下划线 \W        匹配非字母或数字或下划线 \d          匹配数字 ^           匹配字符串的开头 $            匹配字符串的结尾     ps:^与$的组合能够明确的限制想要查找的具体数据  ^9$ a|b         匹配字符a或字符b      ps:管道符|在很多场景下的意思都是或 ()            给正则表达式分组 不影响正则表达式的匹配     ps:用于后续的正则起别名 分组获取对应数据 []            匹配字符组中的字符 [^]          匹配除了字符组中字符的所有字符

正则表达式量词

*            重复零次或更多次(默认就是尽可能多) +           重复一次或更多次(默认就是尽可能多) ?            重复零次或一次(默认就是一次) {n}          重复n次  {n,}          重复n次或更多次 {n,m}      重复n到m次

语法：

  select * from emp where name regexp '正则表达式';

---

多表查询思路

准备工作

子查询：

  将一张表的查询结果括号括起来当做另外一条SQL语句的条件
        eg:类似以日常生活中解决问题的方式
                第一步干什么
               第二步基于第一步的结果在做操作 ...

连表操作：
    先将所有涉及到结果的表全部拼接到一起形成一张大表 然后从大表中查询数据

建表：

多表查询思路准备代码

#建表
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);

 

---

子查询

  基于两张没有外键链接的表，但是去有着一定的联系。

# 查询jason的部门名称
	1.先获取jason的部门编号
		select dep_id from emp1 where name = 'jason';  # 200
	2.根据部门编号获取部门名称
    	select name from dep1 where id = 200;
 	子查询
    	select name from dep1 where id = (select dep_id from emp1 where name = 'jason');

---

连表操作

select * from emp1,dep1;  # 笛卡尔积
'''我们不会使用笛卡尔积来求数据 效率太低  连表有专门的语法'''

inner join	内连接 只拼接两边都有的字段数据 select * from 表1名 inner join 表2名 on 表一.两个表中相同的字段用=链接
left join	左连接 以左表为基准 展示所有的数据 没有对应则NULL填充 select * from 表1名 left join 表2名 on 表一.两个表中相同的字段用=链接
right join	右连接 以右表为基准 展示所有的数据 没有对应则NULL填充 select * from 表1名 right join 表2名 on 表一.两个表中相同的字段用=链接
union	全连接 两张表拼在一起，没有对应则NULL填充 三条连起来使用 select * from 表1名 left join 表2名 on 表一.两个表中相同的字段用=链接 union select * from 表1名 right join 表2名 on 表一.两个表中相同的字段用=链接

 

 

---

今日习题

 1. 查询岗位名以及岗位包含的所有员工名字
    select post,count(name) from emp group by post;
    2. 查询岗位名以及各岗位内包含的员工个数
    select post,sum(salary) from emp group by post;
    3. 查询公司内男员工和女员工的个数
    男：select post,sum(sex='male') from emp group by post;
    女：select post,sum(sex='female') from emp group by post;
    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);