28,024 次浏览 发布于 由cst

MySQL 查询

MySQL 单表多表查询

select 字段名 from 表名 where 条件

例如：select id from user where name='张三'

​ 查询出 user表中 name为张三 的id

条件查询关键字

= 等于
<> 或 != 不等于
>、 < 、>= 、<=  大于、小于、大于等于、小于等于

between ... and .... 两个值之间，但前值必须小于后值

is null 和 is not null  判断空的

and 并且  ， or 或者

in 包含 例如 in('a','b');

not 用于取非，主要用于 is 和 in

like 模糊查询 例如 username like %c% 或 username like _c%
     %匹配任意个字符 、下划线_ 匹配单个字符
其它关键字
distinct 去重 例如，select distinct dep_name from ....

desc 降序
asc  升序（默认）
order by age asc,name desc; 按年龄升序，当年龄一样时，按名字降序
例子：找出工资在1250到3000之间的员工的姓名和薪资，要求按薪资进行降序排序。

select name,sal 
from emp 
where sal between 1250 and 3000 
order by sal desc;

单行处理函数

数据处理函数又被成为单行处理函数，特点是一个输入对应一个输出。 
常见的有
lower 转小写 lower('A')
upper 转大写 lower('a')
substr 截取字符串 substr(字段名,起始位置从1开始,截取的长度)
length 取长度 
trim 去两边的空格
str_to_date 字符串转日期 （'字符串日期','日期格式'）%Y %m %d %h %i分 %s秒
            str_to_date（'01-10-1999','%d-%m-%Y'）
            这种'1999-01-01' 正确格式无需使用函数，mysql会自动转换
date_format （'日期类型数据','日期格式'） 日期转字符串 date_format(birth,'%m/%d/Y') 这函数没啥用，存正确格式的日期，mysql会自动转换
rand 生成随机数
round 四舍五入 round(1.23, 2) 保留2位小数
concat 对字符串进行拼接 concat(name1,name2)
ifnull 将null转换成一个具体指值 ifnull(age,0) ，表示如果年龄是空，用0代替

case 字段名 when 条件 then 做什么 else 做什么 end 
例子：当sex为1时，表示为男，否则表示为 女
case sex when '1' then '男' else '女' end

多行处理函数（分组函数）

分组函数又被称为多行处理函数，特点：多个输入对应一个输出,自动忽略null的记录，不能使用在where后。
使用方式：函数名(字段名)，例如 sum(score) ，表示对 分数进行求和。

常见的有 
max 取最大值
min 取最小值
avg 求平均值
sum 求和

count 计数 
    count(*)、count(字段名)和 count(1) 的区别？ 

    答：(*)会计算包含null的记录，count(字段名)则忽略null的记录，使用*效率更低
        列名为主键，count(列名) 会比 count(1) 快 ；
        列名不为主键，count(1) 会比 count(列名) 快 ；
        如果表多个列并且没有主键，则 count(1) 的执行效率优于 count() ；
        如果有主键，则 select count（主键）执行效率最优；
        如果表只有一个字段，则 select count(*)最优。

分组查询（重要）

select 字段名 from 表名 where 条件 order by 字段名

SQL语句执行顺序： from → where → group by → select → order by

例子1：按照工作岗位分组，对工资进行求和，查询出岗位和工资总和
     select job,sum(sal) from emp group by job;

例子2：按照部门编号分组，找出每个部门的最高薪资
     select depno,max(sal) from emp group by depno;

例子3: 找出每个部门，不同工作岗位的最高薪资（按照部门编号分组）
     select depno,job,max(sal) from emp group by depno,job;

例子4: 找出每个部门的最高薪资大于3000的信息（按照部门编号分组）
     方式1：select depno,max(sal) from emp where sal>3000 group by depno;
     方式2：select depno,sal from emp group by depno having max(sal)>3000;
     having 可以对分完组后的数据进行再次过滤，不能单独使用，得和group by配合。
     优先使用方式1，where，效率更高

例子5：找出每个部门的平均薪资大于2500的信息（按照部门编号分组） 
     select depno,avg(sal) from emp group by depno having avg(sal)>2500;
     此例子只能用having，无法用where

备注：用了 group by后，select 后面只能跟 参与分组的字段 或 分组函数，否则无意义；

注意：使用分组函数前必须要先分组，所以不能在where 后使用分组函数，例如不能 ...where min(x);

为什么min(x) 不能用在where 的后面，因为使用min(x)的时候，还没有进行分组！请参考SQL的执行顺序。

Mysql 多表查询

内连接

等值连接

查询每个员工所在的部门名称，显示员工名和部门名

select e.ename,d.dname
from emp e
join dept d
on e.deptno = d.deptno;

非等值连接

找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级？

select e.ename,e.sal,s.grade
from emp e
join salgrade s
on e.sal between s.losal and s.hisal;

自连接

查询员工的上级领导，要求员工名和对应的领导名(同  张表)

select a.ename,b.ename
from emp a
join emp b
on a.mgr = b.empno

外连接

sql语句中的inner和outer可以省略，带有right或left的是外连接。

左外连接

select e.ename,d.dname
from dept d
 left join emp e
on e.deptno = d.deptno;

右外连接

select e.ename,d.dname
from dept d
right join emp e
on e.deptno = d.deptno;

right 代表什么，表示将join关键字右边的这张表看成主表，主要是为了将右边的这张表的数据全部查询出来，捎带着关联查询左边的表，在外连接当中，两张表连接，产生了主次关系。

三表连接

找出每个员工的部门名称以及工资等级，要求显示员工名、部门名、薪资、薪资等级

select e.ename,d.dname,e.sal,s.grade
from emp e
join dept d
on e.deptno = d.deptno
join salgrade s
on e.sal between s.losal and hisal;
找出每个员工的部门名称以及工资等级，还有上级领导，要求显示员工名、领导名、部门名、薪资、薪资等级

select e.ename,l.ename,d.dname,e.sal,s.grade
from emp e
join dept d
on e.deptno = d.deptno
join salgrade s
on e.sal between s.losal and hisal;
left join emp l //left 保证emp表的数全部查出来
on e.mgr = l.empno

MySQL 子查询

select 中 嵌套 select语句，被嵌套的select 语句成为子查询

可嵌套的位置：select (select) from (select) where (select)

where后出现子查询

找出比最低工资高的员工姓名和工资

select ename,sal from emp where sal > (select min(sal) from emp);

from 后出现子查询

找出每个岗位的平均薪资的薪资等级

select t.*,s.grade
from (select job,avg(sal) as avgsal from emp group by job) t
join salgrade s
on t.avgsal between s.losal and s.highsal;

select 后出现子查询

找出每个员工的部门名称，要求显示员工名，部门名（此题了解即可）

select e.ename,(select d.dname from dept d where e.deptno=d.deptno) as dname
from emp e;

此情况的子查询只能一次返回1条结果，如果结果是多条会报错，因为和其它字段结果行数不匹配

其它知识

union的例子

第1种：select ename,job from emp where job='Manager' or job='Salesman';

第2种：select ename,job from emp where in('Manager','Salesman');

第3种：
select ename,job from emp where job='Manager';
union
select ename,job from emp where job='Salesman'

第3种使用 union的效率要高一些，对于表连接来说，每连接一次新表则匹配的次数满足笛卡尔积，成倍的翻，但是union可以减少匹配的次数，还可以完成两结果的拼接。
注意，使用union 进行结果合并时，要求两语句的列数和各字段相同
limit

limit startIndex,length;

limit 5; 前5条记录
limit 2,5; 从第3条开始，往后取5条

注意：limit在order by后执行！

“MySQL 查询”的4,584个回复

  75. I was recommended this blog by way of my cousin. I am now
    not sure whether this submit is written via him as nobody else understand such precise approximately
    my trouble. You are wonderful! Thanks!

  94. Thanks for some other magnificent post. Where else may just anybody get that kind
    of information in such an ideal approach of writing?

    I have a presentation subsequent week, and I am at the search for
    such info.

  95. Excellent post. I used to be checking continuously this
    blog and I’m impressed! Extremely helpful information specially the last section 🙂 I maintain such information a
    lot. I was seeking this particular info for a long time. Thanks and good luck.

  107. Hey! Would you mind if I share your blog with
    my twitter group? There’s a lot of people that I think would really enjoy your content.
    Please let me know. Many thanks

  129. We’re a gaggle of volunteers and starting a new scheme in our
    community. Your web site offered us with helpful info to work on. You’ve performed
    an impressive activity and our whole group will
    likely be thankful to you.

  237. This is really interesting, You’re a very skilled
    blogger. I’ve joined your rss feed and look forward to seeking more
    of your magnificent post. Also, I’ve shared your site in my social networks!

  242. What i do not realize is in reality how you’re not really much
    more smartly-appreciated than you may be now.
    You are so intelligent. You understand therefore considerably in relation to this subject, made
    me in my view consider it from so many numerous angles.
    Its like men and women don’t seem to be interested except it
    is one thing to accomplish with Woman gaga! Your individual stuffs nice.
    At all times maintain it up!

  252. I was extremely pleased to discover this page. I want to to thank you for your time
    for this particularly fantastic read!! I definitely loved every part of it and I have you book-marked to check out new things
    in your blog.

  259. Hey I know this is off topic but I was wondering if you knew of any widgets I could add to my blog that automatically tweet my newest
    twitter updates. I’ve been looking for a plug-in like
    this for quite some time and was hoping maybe you would have some experience with something like this.
    Please let me know if you run into anything.
    I truly enjoy reading your blog and I look
    forward to your new updates.

  267. I will right away grasp your rss feed as I can not find your email subscription link or newsletter service.
    Do you have any? Please allow me understand in order that I could subscribe.
    Thanks.

  272. This design is wicked! You obviously know how
    to keep a reader amused. Between your wit and your videos, I was almost moved
    to start my own blog (well, almost…HaHa!) Great job. I really enjoyed what
    you had to say, and more than that, how you presented it. Too cool!

  295. Excellent beat ! I wish to apprentice even as you amend your site, how can i subscribe for a weblog web site?
    The account helped me a applicable deal. I were tiny bit acquainted of this your broadcast provided vivid transparent idea

  296. You actually make it seem so easy along with your
    presentation however I in finding this topic to be really one thing that I feel
    I would never understand. It seems too complicated and very wide for me.
    I am having a look ahead to your next put up, I will try to
    get the cling of it!

  310. Write more, thats all I have to say. Literally, it seems as
    though you relied on the video to make your point.
    You definitely know what youre talking about, why waste your intelligence
    on just posting videos to your weblog when you could be giving us something informative
    to read?

  353. I have been surfing on-line greater than three hours these days, yet
    I never found any fascinating article like yours. It’s lovely value
    enough for me. Personally, if all site owners and bloggers made good
    content as you probably did, the web can be a lot more helpful
    than ever before.

  384. Hi just wanted to give you a quick heads up and let you know a few of the images aren’t loading correctly.
    I’m not sure why but I think its a linking issue.

    I’ve tried it in two different web browsers and both show
    the same outcome.

  452. May I just say what a relief to find an individual who actually knows what they are discussing on the internet. You actually know how to bring an issue to light and make it important. More and more people should check this out and understand this side of the story. I can’t believe you are not more popular given that you surely possess the gift.

  453. May I simply just say what a comfort to find somebody that genuinely knows what they are discussing on the web. You definitely know how to bring a problem to light and make it important. More people should read this and understand this side of your story. I was surprised you aren’t more popular since you definitely possess the gift.

  462. May I just say what a relief to find someone who genuinely understands what they are talking about online. You definitely understand how to bring a problem to light and make it important. A lot more people have to look at this and understand this side of the story. I was surprised you aren’t more popular given that you certainly have the gift.

  463. Many thanks for this article. I might also like to say that it can become hard while you are in school and merely starting out to establish a long credit standing. There are many learners who are simply trying to make it through and have a lengthy or favourable credit history are often a difficult point to have.

  467. Just wish to say your article is as surprising. The clarity to your put up is simply excellent and that i could assume you are an expert in this subject. Fine along with your permission allow me to take hold of your feed to keep up to date with approaching post. Thanks one million and please keep up the rewarding work.

  554. Hi! I know this is somewhat off-topic however I
    needed to ask. Does operating a well-established website such as
    yours require a massive amount work? I am brand new to operating a blog however I do write in my diary everyday.
    I’d like to start a blog so I can share my experience and thoughts online.

    Please let me know if you have any recommendations or tips for new
    aspiring blog owners. Thankyou!

  555. Hey! I know this is sort of off-topic however I needed
    to ask. Does managing a well-established blog like yours take a
    large amount of work? I am brand new to blogging but I do write in my diary
    daily. I’d like to start a blog so I will be able to share my experience and feelings online.
    Please let me know if you have any kind of suggestions or tips for new aspiring bloggers.
    Thankyou!

  560. Good day! This is kind of off topic but I need some advice from an established blog.
    Is it difficult to set up your own blog? I’m not very techincal but I can figure things out pretty quick.

    I’m thinking about creating my own but I’m not sure where to begin. Do you have any tips or suggestions?
    Thank you

  2012. Having read this I thought it was rather enlightening.

    I appreciate you finding the time and energy to put this information together.
    I once again find myself spending a significant amount of time both
    reading and posting comments. But so what, it was still worthwhile!

  2058. Check out 1this guide onhow to get free gems on cooking fever

    gh45kkm
    This game is a whole lot more fun when you have as many gems as you want.If you enjoy mobile games like this you should check out the site above

  2528. Howdy! This is my first comment here so I just
    wanted to give a quick shout out and tell you I really enjoy reading through your
    posts. Can you suggest any other blogs/websites/forums that cover
    the same subjects? Thank you!

  2531. Check out this website that teaches you 1how to get free gems on cooking fever

    k23lartds11
    This game is a whole lot more fun when you have as many gems as you could ever want.If you love mobile games like this you should check out the site above

  2799. Hey I know this is off topic but I was wondering if you knew of any
    widgets I could add to my blog that automatically tweet my
    newest twitter updates. I’ve been looking for a plug-in like this for quite
    some time and was hoping maybe you would have some experience with something like this.

    Please let me know if you run into anything. I truly enjoy reading your blog and I look forward
    to your new updates.

  2986. I have been exploring for a little for any
    high quality articles or weblog posts in this sort
    of area . Exploring in Yahoo I at last stumbled upon this website.

    Reading this info So i’m glad to express that I’ve an incredibly excellent
    uncanny feeling I found out just what I needed.
    I such a lot certainly will make sure to do not put out of your mind this site and give it a look on a relentless basis.

  3157. Go to this site to get 1free diamonds cooking fever

    u3iauds11
    This game is a whole lot more fun when you have as many gems as you could ever want.If you love mobile games like this you need to check out the link above