mysql单表查询与多表查询

单表查询

where

group by

having

distinct

order by

limit

多表查询

子查询

连表查询

🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓

前期表准备

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), #以下是教学部

('egon','male',78,'20150302','teacher',1000000.31,401,1),

('kevin','male',81,'20130305','teacher',8300,401,1),

('tank','male',73,'20140701','teacher',3500,401,1),

('owen','male',28,'20121101','teacher',2100,401,1),

('jerry','female',18,'20110211','teacher',9000,401,1),

('nick','male',18,'19000301','teacher',30000,401,1),

('sean','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);

#ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk

🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓

单表查询

书写顺序

select id,name from emp where id>3 and id<6;

  执行顺序

from  # 先确定是哪张表

where  # 再确定是否有过滤条件

select  # 最后确定要过滤出来的数据的哪些字段

🐨小知识点select * from emp\G;  # 整理表格

🍉where约束条件 查询

书写顺序:select 数据from 表名where 约束条件;

        执行顺序:from    # 先确定是哪张表

where  # 再确定是否有过滤条件

select  # 最后确定要过滤出来的数据的哪些字段

# 1.查询id大于等于3小于等于6的数据

select * from emp where id >=3 and id<=6;

select id,name from emp where id between 3 and 6;

        # 2.查询薪资是2000或者18000或者17000的数据

select * from emp where salary =20000 or salary = 18000 or salary=17000;

select id 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%';

select id,name from emp where name like '_w%';

        # 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;  # 取反，不取3到6之间的数

select * from emp where id<3 or id>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分组 查询⚠️：查询条件中有"每/各"字的都要分组

书写顺序：select 数据from 表名where 约束条件group by 分组依据;

        执行顺序：from

where

group by

select

        # 1、按部门分组

select * from emp group by post;  # 分组后取出的是每个组的第一条数据mac系统直接报错

select post,name from emp group by post;  # 验证mac系统直接报错

强调⚠️：分组之后只能获取到分组依据的字段值，不能直接获取到组内单个数据的信息。

设置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;  # 获取部门信息  可以获得分组后的职位表

🐨知识点：聚合函数max() | min() | avg() | sum() | count()

            以组为单位统计组内数据》》》聚合查询（聚集到一起合成为一个结果）

# 2、获取每个部门的最高工资

select post,max(salary) 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;

        🐨知识点：group_concat(分组之后用) 不仅可以用来显示除分组外字段还有凭借字符串的作用

# 3.查询分组之后的部门名称和每个部门下所有的学生姓名

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

            select post,group_concat(name,": ",salary) from emp group by post;  # 拼接字符串

🐨知识点：as语法，即可以给字段起别名

select name as 姓名,salary as 工资；

select emp.id,emp.name from emp as t1;  # 报错 因为表名已经被改成了t1

select t1.id,t1.name from emp as t1;

        🐨知识点：补充concat（不分组时用）拼接字符串达到更好的显示效果

select concat("name:",name) as 姓名,concat("sal:",salary) as 薪资from emp;

        🐨知识点： 查询四则运算

查询每个人的年薪

select name,salary*12 as annual_salary from emp;

select name,salary*12 annual_salary from emp;

🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌

1. 查询岗位名以及岗位包含的所有员工名字group_concat()

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

    2. 查询岗位名以及各岗位内包含的员工个数count()

select post,count(id) from emp group by post;

    3. 查询公司内男员工和女员工的个数count()

select sex,count(id) from emp group by sex;

    4. 查询岗位名以及各岗位的平均薪资avg()

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

    5. 查询岗位名以及各岗位的最高薪资max()

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

    6. 查询岗位名以及各岗位的最低薪资min()

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

    7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资

select sex,avg(salary) from emp group by sex;

    ⚠️# 关键字where group by同时出现的情况下，group by必须在where之后

# where先对整张表进行一次筛选，如何group by再对筛选过后的表进行分组

# 如何验证where是在group by之前执行而不是之后 利用聚合函数 因为聚合函数只能在分组之后才能使用

select id,name,age from emp where max(salary) > 3000;  # 报错！

select max(salary) from emp;  # 正常运行，不分组意味着每一个人都是一组，等运行到max(salary)的时候已经经过where，group by操作了，只不过我们都没有写这些条件

# 语法顺序

select

from

where

group by

    # 再识执行顺序

from

where

group by

select

    8、统计各部门年龄在30岁以上的员工平均工资

# 对where过滤出来的虚拟表进行一个分组

select post'部门',avg(salary)'平均工资' from emp where age>=30 group by post;

        # 还不明白可以分布执行查看结构

select * from emp where age>30;

        # 基于上面的虚拟表进行分组

select * from emp where age>=30 group by post;

🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌

🍓having:

        用法跟where一模一样，having用在group by之后进行的过滤，也就意味着where不能用聚合函数，但having可以

语法顺序：select 数据from 表名where 约束条件group by 分组依据having 聚合函数

执行顺序：from

where

group by

having

select

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

select post'部门',avg(salary)'平均工资' from emp where age >=30 group by post having avg(salary) >=10000;

        # 如果不信你可以将having取掉，查看结果，对比即可验证having用法！

#强调⚠️：having必须在group by后面使用

select * from emp having avg(salary) > 10000;  # 报错

🍓distinct:去重  （对有重复的展示数据进行去重操作）

执行顺序：from

where

group by

having

distinct

select

select distinct post from emp;

    🍓order by:排序

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

select * from emp order by salary desc;  # 默认是降序

select * from emp order by age,salary;  # 默认是升序

select * from emp order by age asc,salary 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 5;

        🐨知识点：分页显示  如果写了两个参数，第一个参数表示起始位置，第二个参数表示的是从第一个参数的位置开始往后获取的数据个数

select * from emp limit 5,10;

select * from emp limit 5,5;

        查询工资最高的人的详细信息

select * from emp order by salary desc limit 1;

    🍓正则：（regexp）

🐨知识点：% 任意_单个

贪婪匹配与非贪婪匹配.*  .*?

select * from emp where name regexp '^j.*(n|y)$';

# 总书写顺序

select distinct * from '表名' where '限制条件' group by '分组依据' having '过滤条件' order by limit;

# 总执行顺序：from

where

group by

having

order by

limit

distinct

select

🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋

🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓

🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋🍋

多表查询

创建表

create table dep(

id int,

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,'运营');

insert into emp(name,sex,age,dep_id) values

('jason','male',18,200),

('egon','female',48,201),

('kevin','male',38,201),

('nick','female',28,202),

('owen','male',18,200),

('jerry','female',18,204);

    # 当初为什么我们要分表，就是为了方便管理，在硬盘上确实是多张表，但是到了内存中我们应该把他们再拼成一张表进行查询才合理

🍓表查询

笛卡尔积查询：select * from emp,dep;  # 左表一条记录与右表所有记录都对应一遍

将所有的数据都对应了一遍，虽然不合理但是其中有合理的数据，现在我们需要做的就是找出合理的数据

# 查询员工及所在部门的信息

select * from emp,dep where emp.dep_id = dep.id;

        # 查询部门为技术部的员工及部门信息

select * from emp,dep where emp.dep_id = dep.id and dep.name='技术';

        将两张表关联到一起的操作，有专门对应的方法

# 1、内连接：只取两张表有对应关系的记录inner join(一张表一定在另一张表中【包含关系】)

select * from emp inner join dep on emp.dep_id = dep.id;

              select * from emp inner join dep on emp.dep_id = dep.id where dep.name = "技术";

        # 2、左连接: 在内连接的基础上保留左表没有对应关系的记录left join

select * from emp left join dep on emp.dep_id = dep.id;

        # 3、右连接: 在内连接的基础上保留右表没有对应关系的记录right join

select * from emp right join dep on emp.dep_id = dep.id;

        # 4、全连接：在内连接的基础上保留左、右面表没有对应关系的的记录left join union right join

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;

    🍓子查询

就是将一个查询语句的结果用括号括起来当作另外一个查询语句的条件去用

虚拟表：查询的数据都可以作为一张表

记住一个规律，表的查询结果可以作为其他表的查询条件，也可以通过起别名的方式把他作为一张虚拟表去跟其他表做关联查询！

select * from emp inner join dep on emp.dep_id = dep.id;

        # 1.查询部门是技术或者人力资源的员工信息

先获取技术部和人力资源部的id号，再去员工表里面根据前面的id筛选出符合要求的员工信息

select * from emp where dep_id in (select id from dep where name = "技术" or name = "人力资源");

        # 2.每个部门最新入职的员工 思路：先查每个部门最新入职的员工，再按部门对应上联表查询

select t1.id,t1.name,t1.hire_date,t1.post,t2.*from emp as t1

inner join

(select post,max(hire_date) as max_date from emp group by post) as t2

on t1.post = t2.post

            where t1.hire_date = t2.max_date;

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 195,783评论 5赞 462
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 82,360评论 2赞 373
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 142,942评论 0赞 325
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 52,507评论 1赞 267
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 61,324评论 5赞 358
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 46,299评论 1赞 273
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 36,685评论 3赞 386
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 35,358评论 0赞 254
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 39,652评论 1赞 293
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 34,704评论 2赞 312
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 36,465评论 1赞 326
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 32,318评论 3赞 313
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 37,711评论 3赞 299
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 28,991评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,265评论 1赞 251
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 41,661评论 2赞 342
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 40,864评论 2赞 335

推荐阅读更多精彩内容

  • MySQL简介和安装
    1. 了解SQL 1.1 数据库基础 ​ 学习到目前这个阶段，我们就需要以某种方式与数据库打交道。在深入学习MyS...
    锋享前端阅读 1,025评论 0赞 1
  • 摘录的一些知识点
    ORACLE自学教程 --create tabletestone ( id number, --序号usernam...
    落叶寂聊阅读 1,057评论 0赞 0
  • MySQL单表查询与多表查询
    参考与：http://codingxiaxw.cn/ 1.解决中文乱码问题 1.1查看MySQL数据库编码 在命令...
    joshul阅读 1,751评论 0赞 4
  • MySQL 常用语法总结
    一、SQL速成 结构查询语言(SQL)是用于查询关系数据库的标准语言，它包括若干关键字和一致的语法，便于数据库元件...
    shadow雨轩阅读 504评论 0赞 3
  • 戏子恨
    酒霞披裹满宜阳，婉转幽啼割愁肠。 痴嗔哀怨翻覆往，颦嫣皆虑跫嫌郎。 动荡唯忆童时康，藏埋征时亲故亡。 伶俜融戏诉悲...
    髻涟_菱觞阅读 297评论 0赞 6