文章目录
一、介绍
针对于记录的相关操作：
使用insert实现数据的插入
update实现数据的更新
使用delete实现数据的删除
使用select查询数据。
二、查询数据SELECT - 单表查询
1.查询的语法
SELECT 字段1,字段2... FROM 表名
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数
数据准备
# 创建表
create table emp(
id int primary key auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age smallint(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
);
# 查看表结构
mysql> desc emp;
+--------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | smallint(3) unsigned | NO | | 28 | |
| hire_date | date | NO | | NULL | |
| post | varchar(50) | YES | | NULL | |
| post_comment | varchar(100) | YES | | NULL | |
| salary | double(15,2) | YES | | NULL | |
| office | int(11) | YES | | NULL | |
| depart_id | int(11) | YES | | NULL | |
+--------------+-----------------------+------+-----+---------+----------------+
10 rows in set (0.03 sec)
#插入记录
#三个部门：教学，销售，运营
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('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),
('tom','male',78,'20150302','teacher',1000000.31,401,1,'');
# 查看所有记录
mysql> select * from emp;
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| 1 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 2 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 3 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 4 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 5 | jack | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 6 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 7 | sank | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 8 | 哈哈 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 9 | 呵呵 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
| 10 | 西西 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 11 | 乐乐 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 12 | 拉拉 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 13 | 僧龙 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 14 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 15 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
| 16 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 17 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
| 18 | tom | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 |
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
18 rows in set (0.00 sec)
1.查询关键字之select、from
select
指定需要查询的字段信息
select * 查所有字段
select name 查name字段
select 方法 支持对字段做额外处理
select char_length(name)
from
指定需要查询的表信息
from mysql.user
from t1
SQL语句中关键字的执行顺序和编写顺序并不是一致的，可能会错乱
eg:
select id,name from userinfo;
我们先写的select在写的from,但是执行的时候是先执行的from再执行select
对关键字的编写顺序和执行顺序我们没必要过多的在意，熟练之后会非常自然的编写。
我们只需要把注意力放在每个关键字的功能上即可。
编写SQL语句的小技巧
针对select后面的字段名可以先用*占位往后写，最后再回来修改
在实际应用中select后面很少直接写 * 因为*表示所有。当表中字段和数据都特别多的情况下非常浪费数据库资源
"""
SQL语句的编写类似于代码的编写，不是一蹴而就的，也需要反反复复的修修补补
"""
"""
在MySQL中也有很多内置方法 我们可以通过查看帮助手册学习
help 方法名
"""
2.查询关键字之where筛选
where字句中可以使用：
1. 比较运算符：> < >= <= <> !=
2. between 80 and 100 值在10到20之间
3. in(80,90,100) 值是10或20或30
4. like 'egon%' # 模糊查询:没有明确的筛选条件
pattern可以是%或_，
%:匹配任意个数任意字符
_:匹配单个个数任意字符
5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
6. 关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)
示例：
# 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 = 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<3 or id>6;
select * from emp where id not between 3 and 6;
# 6.查询薪资不在20000，18000，17000范围的数据
select * from emp where salary != 20000 and salary !=18000 and salary !=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;
'''在sql中，NULL和''(空字符)不一样''
3.查询关键字之group by分组
什么是分组？为什么要分组？
1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的
2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等
3、为何要分组呢？是为了更好的统计相关数据。
取每个部门的最高工资
取每个部门的员工数
取男人数和女人数
小窍门：‘每个’,‘平均’这两个字后面的字段，就是我们分组的依据
4、大前提：
可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，需要自己设置sql_mode
set global sql_mode = 'only_full_group_by,STRICT_TRANS_TABLES';
5、如果想查看组内信息，需要借助于聚合函数
示例：
# 1.按部门分组
select * from emp group by post; # 分组后取出的是每个组的第一条数据
select id,name,sex from emp group by post; # 验证
"""MySQL5.6默认不会报错"""
设置sql_mode为only_full_group_by，意味着以后但凡分组，只能取到分组的依据，
不应该在去取组里面的单个元素的值，那样的话分组就没有意义了，因为不分组就是对单个元素信息的随意获取
set global sql_mode="strict_trans_tables,only_full_group_by";
# 重新链接客户端
select * from emp group by post; # 报错
select id,name,sex from emp group by post; # 报错
select post from emp group by post; # 获取部门信息
# 强调:只要分组了，就不能够再“直接”查找到单个数据信息了，只能获取到组名
select post,name from emp group by post; # 报错
3.2 聚合函数
聚合函数主要就是配合分组一起使用
示例：
max min sum count avg
# 2.获取每个部门的最高工资
# 以组为单位统计组内数据>>>聚合查询(聚集到一起合成为一个结果)
# 每个部门的最高工资
select post,max(salary) from emp group by post;
补充:在显示的时候还可以给字段取别名
as也可以省略，但是不推荐省，因为寓意不明确
select post as '部门',max(salary) as '最高工资' from emp group by post;
聚合函数的使用
# 每个部门的最低工资
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;
统计的时候只要是非空字段,效果都是一致的
这里显示age，salary，id最后演示特殊情况post_comment
3.3 group_concat()
如果真的需要获取分组以外的数据字段，可以使用group_concat()
# 每个部门的员工姓名
select post,group_concat(name) from emp group by post;
select post,group_concat(name,'|',sex) from emp group by post;
select post,group_concat(name,'|',sex, '|', gender) from emp group by post;
select post,group_concat(distinct name) from emp group by post;
separator:添加字符
select post,group_concat(distinct name separator '%') from emp group by post;
补充：
concat()：用于分组之前的字段拼接操作
concat_ws()：拼接多个字段并且中间的连接符一致，分组不分组都可以使用
# concat 不分组使用
select concat(name,sex) from emp;
select concat(name,'|',sex) from emp;
# concat_ws()
select post,concat_ws('|', name, age, gender) from emp group by post;
exists
select * from dep where exists (select * from emp where emp,id>100);
sql语句1 exists sql语句2
sql语句2有结果的情况下才会执行sql语句1,否则(没有结果)不执行sql语句1 返回空数据
3.4 having过滤
having在分组之后对数据进行筛选
# 统计各部门年龄在30岁以上的员工平均薪资,并且保留平均薪资大于10000的部门.
select post,avg(salary) from emp where age>30 group by post having avg(salary)>10000;
步骤1:先筛选出所有年龄大于30岁的员工数据
select * from emp where age > 30;
步骤2:再对筛选出来的数据按照部门分组并统计平均薪资
select post,avg(salary) from emp where age > 30 group by post;
步骤3:针对分组统计之后的结果做二次筛选
select post,avg(salary) from emp where age > 30 group by post having avg(salary) > 10000;
where与having的区别
where与having都是筛选功能，但是有区别
where在分组之前对数据进行筛选
having在分组之后对数据进行筛选
关键字where group by 同时出现的情况下，group by必须在where之后
where 先对整张表进行一次筛选(首次筛选)，group by再对筛选过后的表进行分组(二次筛选)
'''
稍微复杂一点的SQL 跟写代码几乎一样 也需要提前想好大致思路
每条SQL的结果可以直接看成就是一张表,基于该表如果还想继续操作则直接在产生该表的SQL语句上添加即可
'''
4. 关键字之distinct去重
对有重复的展示数据进行去重操作，一定要是重复的数据。数据必须一模一样才可以去重。
select distinct id,age from emp; # 关键字针对的是多个字段组合的结果
select distinct post from emp;
5. 关键字之order by排序
select * from emp order by salary; # 默认升序排(asc可以省略)
select * from emp order by salary desc; # 降序排
#先按照age降序排，在年轻相同的情况下再按照薪资升序排
select * from emp order by age desc,salary;
'''多字段排序，如果想让后面的字段排序生效，前提：前面的排序字段必须一样'''
# 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
select post,avg(salary) from emp where age>10 group by post having avg(salary)>1000 order by avg(salary) desc;
当一条SQL语句中很多地方都需要使用聚合函数计算之后的结果 我们可以节省操作(主要是节省了底层运行效率，代码看不出来)，给聚合结果'起别名'
select post,avg(salary) as avg_salary from emp where age > 10 group by post having avg_salary>1000 order by avg_salary;
6. 关键字之limit分页
1.限制展示条数
select * from emp limit 3;
2.分页显示
select * from emp limit 0,5; # 第一个参数表示起始位置，第二个参数表示的是条数，不是索引位置
select * from emp limit 5,5;
# 查询工资最高的人的详细信息
select * from emp order by salary desc limit 1;
7. 关键字之regexp正则
SQL语句的模糊匹配如果用不习惯 也可以自己写正则批量查询
select * from emp where name regexp '^j.*(n|y)$';
清空表操作
1. delete from t; # 不能把主键重置
2. truncate t; # 可以把主键重置为1开始
'''所以，以后如果有清空表的需求，我们选择truncate，因为truncate清空表之后，还可以恢复数据'''
3. DBA可以恢复数据------>binlog日志----->记录你操作sql语句的过程
4. DBA:数据库管理员
小练习
注意：刚开始查询表的时候，按照步骤来即可，先确定是哪张表，在确定查询这张表有没有限制条件，然后在看是否需要分组，最后在确定需要哪些字段。
- 查询岗位名以及岗位包含的所有员工名字
- 查询岗位名以及各岗位内包含的员工个数
- 查询公司内男员工和女员工的个数
- 查询岗位名以及各岗位的平均薪资
- 查询岗位名以及各岗位的最高薪资
- 查询岗位名以及各岗位的最低薪资
- 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
答案：
elect * from emp;
# 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. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
selecct sex,group_concat(name),avg(salary) from emp group by sex;
三、多表查询
1.数据准备
# 建表
create table dep(
id int primary key auto_increment,
name varchar(20)
);
create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int # 建立逻辑上的表关系
);
#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'保洁')
;
insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('egon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);
######################################################################
# 查看表结构和数据
mysql> desc dep;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)
mysql> desc emp;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+
5 rows in set (0.02 sec)
mysql> select * from dep;
+-----+--------------+
| id | name |
+-----+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
| 205 | 保洁 |
+-----+--------------+
5 rows in set (0.00 sec)
mysql> select * from emp;
+----+-------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+-------+--------+------+--------+
| 1 | jason | male | 18 | 200 |
| 2 | egon | female | 48 | 201 |
| 3 | kevin | male | 18 | 201 |
| 4 | nick | male | 28 | 202 |
| 5 | owen | male | 18 | 203 |
| 6 | jerry | female | 18 | 204 |
+----+-------+--------+------+--------+
6 rows in set (0.00 sec)
2.多表查询思路
1. 连表查询
inner join
left join
right join
union
2.子查询
# 大白话：分布操作
交叉连接：不适用任何匹配条件。生成笛卡尔积
mysql> select * from emp,dep; # 会将两张表中所有的数据对应一遍
+----+-------+--------+------+--------+-----+--------------+
| id | name | sex | age | dep_id | id | name |
+----+-------+--------+------+--------+-----+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 1 | jason | male | 18 | 200 | 201 | 人力资源 |
| 1 | jason | male | 18 | 200 | 202 | 销售 |
| 1 | jason | male | 18 | 200 | 203 | 运营 |
| 1 | jason | male | 18 | 200 | 205 | 保洁 |
| 2 | egon | female | 48 | 201 | 200 | 技术 |
| 2 | egon | female | 48 | 201 | 201 | 人力资源 |
| 2 | egon | female | 48 | 201 | 202 | 销售 |
| 2 | egon | female | 48 | 201 | 203 | 运营 |
| 2 | egon | female | 48 | 201 | 205 | 保洁 |
| 3 | kevin | male | 18 | 201 | 200 | 技术 |
| 3 | kevin | male | 18 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 18 | 201 | 202 | 销售 |
| 3 | kevin | male | 18 | 201 | 203 | 运营 |
| 3 | kevin | male | 18 | 201 | 205 | 保洁 |
| 4 | nick | male | 28 | 202 | 200 | 技术 |
| 4 | nick | male | 28 | 202 | 201 | 人力资源 |
| 4 | nick | male | 28 | 202 | 202 | 销售 |
| 4 | nick | male | 28 | 202 | 203 | 运营 |
| 4 | nick | male | 28 | 202 | 205 | 保洁 |
| 5 | owen | male | 18 | 203 | 200 | 技术 |
| 5 | owen | male | 18 | 203 | 201 | 人力资源 |
| 5 | owen | male | 18 | 203 | 202 | 销售 |
| 5 | owen | male | 18 | 203 | 203 | 运营 |
| 5 | owen | male | 18 | 203 | 205 | 保洁 |
| 6 | jerry | female | 18 | 204 | 200 | 技术 |
| 6 | jerry | female | 18 | 204 | 201 | 人力资源 |
| 6 | jerry | female | 18 | 204 | 202 | 销售 |
| 6 | jerry | female | 18 | 204 | 203 | 运营 |
| 6 | jerry | female | 18 | 204 | 205 | 保洁 |
+----+-------+--------+------+--------+-----+--------------+
30 rows in set (0.00 sec)
# '笛卡尔积'组合出的数据没有任何意义，应该将有关系的数据对应到一起才合理
# 基于笛卡尔积可以将部门编号与部门id相同的数据筛选出来
'''涉及到两张及以上的表时，字段很容易冲突 我们需要在字段前面加上表名来指定'''
mysql> select * from emp,dep where emp.dep_id=dep.id;
+----+-------+--------+------+--------+-----+--------------+
| id | name | sex | age | dep_id | id | name |
+----+-------+--------+------+--------+-----+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | egon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 18 | 201 | 201 | 人力资源 |
| 4 | nick | male | 28 | 202 | 202 | 销售 |
| 5 | owen | male | 18 | 203 | 203 | 运营 |
+----+-------+--------+------+--------+-----+--------------+
5 rows in set (0.00 sec)
3.连表查询
把多张表拼接成一张虚拟表(不是真实存在的表，临时在内存中保存)，然后按照单表查询思路查询。
语法：
语法：
select 字段名
from 表1 inner|left|right join 表2
on 表1.字段 = 表2.字段;
在关键字左边的表1叫：左表，在关键字右边的表2叫右表。
# 当一张表中存现字段冲突的时候，我们使用表名点字段名的方式解决.
# 这里还可以给表明起别名... as
'''
学会了连表操作之后也就可以连接N多张表
思路:将拼接之后的表起别名当成一张表再去与其他表拼接 再起别名当一张表 再去与其他表拼接 其次往复即可
'''
3.1 inner join
内连接：要两张表的共有数据
mysql> select * from emp inner join dep on emp.dep_id=dep.id;
+----+-------+--------+------+--------+-----+--------------+
| id | name | sex | age | dep_id | id | name |
+----+-------+--------+------+--------+-----+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | egon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 18 | 201 | 201 | 人力资源 |
| 4 | nick | male | 28 | 202 | 202 | 销售 |
| 5 | owen | male | 18 | 203 | 203 | 运营 |
+----+-------+--------+------+--------+-----+--------------+
5 rows in set (0.00 sec)
mysql> select * from emp as e inner join dep as d on e.dep_id=d.id; # 这里还可以给表明起别名
+----+-------+--------+------+--------+-----+--------------+
| id | name | sex | age | dep_id | id | name |
+----+-------+--------+------+--------+-----+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | egon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 18 | 201 | 201 | 人力资源 |
| 4 | nick | male | 28 | 202 | 202 | 销售 |
| 5 | owen | male | 18 | 203 | 203 | 运营 |
+----+-------+--------+------+--------+-----+--------------+
5 rows in set (0.00 sec)
3.2 left join
左连接：以左表为基准，查询左表中所有的数据，右表没有的数据用NULL填充
mysql> select * from emp left join dep on emp.dep_id=dep.id;
+----+-------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+-------+--------+------+--------+------+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | egon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 18 | 201 | 201 | 人力资源 |
| 4 | nick | male | 28 | 202 | 202 | 销售 |
| 5 | owen | male | 18 | 203 | 203 | 运营 |
| 6 | jerry | female | 18 | 204 | NULL | NULL |
+----+-------+--------+------+--------+------+--------------+
6 rows in set (0.00 sec)
3.3 right join
右连接------>以右表为基准，查询右表中所有的数据，左表没有的数据用NULL填充
mysql> select * from emp right join dep on emp.dep_id=dep.id;
+------+-------+--------+------+--------+-----+--------------+
| id | name | sex | age | dep_id | id | name |
+------+-------+--------+------+--------+-----+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | egon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 18 | 201 | 201 | 人力资源 |
| 4 | nick | male | 28 | 202 | 202 | 销售 |
| 5 | owen | male | 18 | 203 | 203 | 运营 |
| NULL | NULL | NULL | NULL | NULL | 205 | 保洁 |
+------+-------+--------+------+--------+-----+--------------+
6 rows in set (0.00 sec)
3.4 union
全连接------>连接两个SQL语句的结果。
select * from emp left join dep on emp.dep_id=dep.id
union
select * from emp right join dep on emp.dep_id=dep.id;
+------+-------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+-------+--------+------+--------+------+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | egon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 18 | 201 | 201 | 人力资源 |
| 4 | nick | male | 28 | 202 | 202 | 销售 |
| 5 | owen | male | 18 | 203 | 203 | 运营 |
| 6 | jerry | female | 18 | 204 | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | 205 | 保洁 |
+------+-------+--------+------+--------+------+--------------+
7 rows in set (0.00 sec)
4.子查询
1.一条SQL的执行结果当成另外一条SQL语句的执行条件。
2.内层查询语句的查询结果，可以为外层查询语句提供查询条件。
3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
4：还可以包含比较运算符：=、!=、>、<等
# 查询kevin所在的部门名称
子查询类似于我们日常生活中解决问题的方式>>>:分步操作
步骤1:先根据jason获取部门编号
select dep_id from emp where name='jason';
步骤2:再根据部门编号获取部门名称
select name from dep where id=200;
总结
select name from dep where id = (select dep_id from emp where name='kevin');
# 查看技术部员工姓名
select name from emp
where dep_id in
(select id from dep where name='技术');
'''
很多时候多表查询需要结合实际情况判断用哪种 更多时候甚至是相互配合使用
所求的字段在两张表的字段都有，使用连表操作
所求的字段只存在于一张表之中，使用子查询
'''
代码操作：
select name from dep where id = (select dep_id from emp where name='kevin');
+--------------+
| name |
+--------------+
| 人力资源 |
+--------------+
1 row in set (0.00 sec)
select name from emp
where dep_id in
(select id from dep where name='技术');
+-------+
| name |
+-------+
| jason |
+-------+
1 row in set (0.00 sec)
多表查询练习题
数据准备
/*
数据导入：
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 50624
Source Host : localhost
Source Database : sqlexam
Target Server Type : MySQL
Target Server Version : 50624
File Encoding : utf-8
Date: 10/21/2016 06:46:46 AM
*/
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for `class`
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`caption` varchar(32) NOT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `class`
-- ----------------------------
BEGIN;
INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');
COMMIT;
-- ----------------------------
-- Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`cname` varchar(32) NOT NULL,
`teacher_id` int(11) NOT NULL,
PRIMARY KEY (`cid`),
KEY `fk_course_teacher` (`teacher_id`),
CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `course`
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');
COMMIT;
-- ----------------------------
-- Table structure for `score`
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`course_id` int(11) NOT NULL,
`num` int(11) NOT NULL,
PRIMARY KEY (`sid`),
KEY `fk_score_student` (`student_id`),
KEY `fk_score_course` (`course_id`),
CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `score`
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');
COMMIT;
-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`gender` char(1) NOT NULL,
`class_id` int(11) NOT NULL,
`sname` varchar(32) NOT NULL,
PRIMARY KEY (`sid`),
KEY `fk_class` (`class_id`),
CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `student`
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');
COMMIT;
-- ----------------------------
-- Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` int(11) NOT NULL AUTO_INCREMENT,
`tname` varchar(32) NOT NULL,
PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `teacher`
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
编写复杂的SQL不要想着一口气写完
一定要先明确思路，然后一步步写一步步查一步步补
1、查询所有的课程的名称以及对应的任课老师姓名
2、查询平均成绩大于八十分的同学的姓名和平均成绩
3、查询没有报李平老师课的学生姓名
4、查询挂科超过两门(包括两门)的学生姓名和班级
答案：
分布步骤：
1805
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
