Mysql基础知识02-基础SQL语句

王 茂南
王 茂南
王 茂南
3081
文章
75
评论
2020年3月31日08:03:07
评论 6011字阅读20分2秒
摘要这一篇介绍一下关于MySQL的基本操作和常用的命令. 主要会包括, 简单查询：desc; select (distinct)去重; select count(1)统计总数; 进阶查询：限制where,and; 排序order by; 正则表达式; like; 分类汇总：group by –count(case when); sum; 子查询：查询的嵌套l 连接查询：join; left outer join; right outer join的内容.

简介

这一篇介绍一下MySQL常见的语句, 整体的目录如下所示:

  • 简单查询：desc; select (distinct)去重; select count(1)统计总数
  • 进阶查询：限制where,and; 排序order by; 正则表达式; like
  • 分类汇总：group by --count(case when); sum
  • 子查询：查询的嵌套
  • 连接查询：join; left outer join; right outer join

关于MySQL的基础知识, 可以查看链接: Mysql基础知识01

 

创建数据库

之前我们是直接导入的数据库, 我们也是可以进行自行创建, 创建方式如下:

  1. CREATE DATABASE IF NOT EXISTS yourdbname DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

 

MySQL简单查询

desc 命令

desc: 显示 table 的概况, 如有什么字段, 字段类型. 一般情况下, 我们会使用 desc 来看一下表的字段的信息, 方便之后的操作.

Mysql基础知识02-基础SQL语句

 

select 命令

select 查看所有字段信息

我们先用 select 和 limit 来查看一下几张表格的简单情况, 这次主要用到表格current_dept_emp, salaries, employees, departments这几张表, 注意看每个表的字段，之后连接查询的时候会有用.

当不知道一个表里存了多少数据的时候, 可以先用limit限制一下条数.

  1. select * from current_dept_emp limit 10;
Mysql基础知识02-基础SQL语句

我们接着依次看一下其他几张表的信息, 首先查看salaries表的信息.

Mysql基础知识02-基础SQL语句

接着查看employees表的信息.

Mysql基础知识02-基础SQL语句

最后查看department的信息, 这里是dept_no与department的对应的表.

Mysql基础知识02-基础SQL语句

select 查看指定字段信息

除了上面直接查看一个表的所有信息, 我们也是可以查看指定的字段的信息.

Mysql基础知识02-基础SQL语句

select 内容垂直显示

有的时候内容比较多的时候，直接显示的结果不是很好，这个时候就可以使用 \G 来是的每一行显示一个字段的内容（注意此时最后不需要使用冒号了）；

Mysql基础知识02-基础SQL语句

 

count(1)计数

除了可以使用select查看信息之外, 我们还可以使用select进行计数, 查看表里有多少条记录. 如下所示, 我们使用count(1)进行计数.

Mysql基础知识02-基础SQL语句

 

distinct去重

大部分时候, 我们需要对查询的结果进行去重, 这个时候就需要使用distinct命令了. 例如还是从上面的表中搜索dept_no, 不过这次要对结果进行去重.

Mysql基础知识02-基础SQL语句

这时就只有9条记录了, 之前在上面查询单个字段的时候可以看到明显不止10个. 之后我们在讲子查询的时候还会讲到这个例子.

 

进阶查询

介绍了上面简单的查询之后, 就是进阶的查询. 这一部分会包括排序, 限制和正则表达式.

order by排序

有的时候, 我们需要对结果进行从大到小, 或是从小到大的排序, 这个时候就需要使用order by的命令.

Mysql基础知识02-基础SQL语句

我们可以对排序的结果进行正序或是逆序. 下面是倒叙.

  1. select distinct dept_no
  2. from current_dept_emp
  3. order by dept_no DESC;
Mysql基础知识02-基础SQL语句

同样, 我们可以使用ASC来完成正序的结果展示.

  1. select distinct dept_no
  2. from current_dept_emp
  3. order by dept_no ASC;

 

where限制

大部分时候, 我们在进行搜索的时候, 我们是有搜索的要求的, 这个时候要是有where来限制搜索的条件. 例如我们在这里限制日期的范围和限制性别是女.

这里我们有两个地方可以注意一下:

  • 两个条件, 使用and进行连接
  • 日期的范围可以是between来进行搜索.
Mysql基础知识02-基础SQL语句

下面是搜索的结果.

Mysql基础知识02-基础SQL语句

 

like相似搜索

有的时候, 对于string类的内容, 我们希望搜索其中是否包含指定的字母, 这个时候我们就可以使用like来进行搜索. 就是比如like '%on%' 就是里面包含字母组合on.

Mysql基础知识02-基础SQL语句

如这里查询的结果表示first_name中包含字母组合on.

Mysql基础知识02-基础SQL语句

 

regexp正则表达式

上面的like只能用于一些简单的情况, 真正复杂的情况我们就需要使用正则表达式来进行操作了. 类如正则表达式可能会用在车牌, 身份证或是评论的匹配.

关于正则表达式的内容, 可以参考这一篇文章, Python正则表达式指南-详细版, 一些详细的规则, 可以参考这一篇文章, Python正则表达式指南

如下面的例子, 我们搜索first_name的首字母是M, 最后一个字母是a-c中间的一个, 的信息.

  1. select *
  2. from employees
  3. where first_name regexp '^M.*[a-c]$'
  4. limit 10;

最终的结果如下所示:

Mysql基础知识02-基础SQL语句

 

分类汇总groupby

分类汇总计数与重命名

首先我们来看一下最基本的分类汇总的功能. 比如上面我们知道current_dept_emp里的dept_no字段只有9种, 我们想统计一下每种类型下面有多少条记录:

  1. select dept_no, count(*) as count_num
  2. from current_dept_emp
  3. group by dept_no
  4. order by dept_no;

我们还要注意以下对搜索结果字段进行重命名的方式. 最终的结果如上所示.

Mysql基础知识02-基础SQL语句

 

分类统计与条件-case

有的时候, 我们在进行个数统计的时候, 会需要对单个的某个属性进行条件的设置, 这个时候就需要使用case来进行完成. 我们可以使用sum或是使用count来配合case的使用. 我们看下面的例子.

下面我们统计在某一段时间内current_dept_emp里的dept_no字段的记录条数.

sum与case完成统计

使用sum的时候, 我们判断是则返回1, 否则返回0.

Mysql基础知识02-基础SQL语句
  1. select dept_no,
  2.     sum(case when from_date between '1986-01-01' and '1990-01-01' then 1 else 0 end) as count_num
  3. from current_dept_emp
  4. group by dept_no
  5. order by dept_no;

最终的结果如下所示:

Mysql基础知识02-基础SQL语句

count与case完成统计

使用count的时候, 我们判读是范围number, 否则返回NULL.

Mysql基础知识02-基础SQL语句
  1. select dept_no,
  2.     count(case when from_date between '1986-01-01' and '1990-01-01' then emp_no else NULL end) as count_num
  3. from current_dept_emp
  4. group by dept_no
  5. order by dept_no;

最终的结果如下所示:

Mysql基础知识02-基础SQL语句

可以看到上面的两种查询方式得到的结果是一样的.

 

子查询

所谓子查询, 其实就是嵌套的查询. 我们这里就举一个很简单的例子, 我们需要注意每个子查询都要起一个名字, 如这里的as a就是给select出来的内容起了一个名字, 上面我们也是使用同样的方式给count的新的列起名字的.

Mysql基础知识02-基础SQL语句
  1. select count(1) as total_num
  2. from(
  3.     select distinct dept_no
  4.     from current_dept_emp
  5. ) as a;

在写子查询的时候, 我们可以先写from里面的内容, 再写外面查询的内容. 最终的结果如下.

Mysql基础知识02-基础SQL语句

 

联合查询

有的时候要查询的信息包含在多张表里面, 这个时候就需要使用联合查询. 联合查询有三种, 分别是

  • join
  • left outer join
  • right outer join

具体区别可以看下面的图:

Mysql基础知识02-基础SQL语句
  • 可以看到A join B会取A, B中did那一列都有的内容;
  • A left outer join B会取A的did那一列所有的, 若B中没有, 则保存为null;

下面我们使用上面的表进行具体例子的介绍:

我们现在想要查询一个员工的平均薪水(表salaries中的salary字段)和住房的名称(表departments中得dept_name字段), 我们应该如何进行操作呢.

基础的联合查询

一步一步来, 首先我们想看员工与薪水的对应, 此时一个员工会有多个薪水记录, 我们先不求平均:

  1. select a.emp_no, a.first_name, a.last_name, a.gender, b.salary
  2. -- 查询员工信息
  3. from(
  4.     select emp_no, first_name, last_name, gender
  5.     from employees
  6. ) as a
  7. -- 查询薪水信息
  8. left outer join(
  9.     select emp_no, salary
  10.     from salaries
  11. ) as b
  12. on a.emp_no=b.emp_no
  13. limit 10;

结果如下所示, 可以看到每一个员工有多条工资记录.

Mysql基础知识02-基础SQL语句

同时, 我们需要将dept_name和dept_no进行关联.

  1. select department_number.emp_no as emp_no,
  2.     department_number.dept_no as dept_no,
  3.     department_name.dept_name as dept_name
  4. from(
  5.     select emp_no, dept_no
  6.     from current_dept_emp
  7. ) as department_number
  8. left outer join(
  9.     select dept_no, dept_name
  10.     from departments
  11. ) as department_name
  12. on department_number.dept_no=department_name.dept_no
  13. limit 10;

最终的结果显示如下所示.

Mysql基础知识02-基础SQL语句

 

group by多条记录

但是我们发现一个员工会有多条薪水的记录, 我们看一下他的平均薪水, 并且从高到底排序.

  1. select a.emp_no, a.first_name, a.last_name, a.gender,
  2.     sum(b.salary) as total_salary,
  3.     count(1) as total_count,
  4.     sum(b.salary)/count(1) as average_salary
  5. -- 查询员工信息
  6. from(
  7.     select emp_no, first_name, last_name, gender
  8.     from employees
  9. ) as a
  10. -- 查询薪水信息
  11. left outer join(
  12.     select emp_no, salary
  13.     from salaries
  14. ) as b
  15. on a.emp_no=b.emp_no
  16. -- 多列同时group by
  17. group by a.emp_no, a.first_name, a.last_name, a.gender
  18. order by average_salary DESC
  19. limit 10;

这样就计算出每个员工的平均工资, 最终的排序的结果如下所示.

Mysql基础知识02-基础SQL语句

 

最终结果

有了上面的铺垫, 下面就是比较简单了. 我们只需要将上面的求平均工资的表和求公寓信息的表关联上, 就可以了. 下面这段代码会比较长, 可以分解出来看, 不是很难懂, 就只是长.

  1. select emp_info.emp_no as emp_no,
  2.     emp_info.first_name as first_name,
  3.     emp_info.last_name as last_name,
  4.     emp_info.gender as gender,
  5.     emp_info.total_salary as total_salary,
  6.     emp_info.total_count as total_count,
  7.     emp_info.average_salary as average_salary,
  8.     dep_info.dept_name as dept_name
  9. from(
  10.     -- 综合员工信息
  11.     select a.emp_no as emp_no,
  12.         a.first_name as first_name,
  13.         a.last_name as last_name,
  14.         a.gender as gender,
  15.         sum(b.salary) as total_salary,
  16.         count(1) as total_count,
  17.         sum(b.salary)/count(1) as average_salary
  18.     -- 查询员工信息
  19.     from(
  20.         select emp_no, first_name, last_name, gender
  21.         from employees
  22.     ) as a
  23.     -- 查询薪水信息
  24.     left outer join(
  25.         select emp_no, salary
  26.         from salaries
  27.     ) as b
  28.     on a.emp_no=b.emp_no
  29.     -- 多列同时group by
  30.     group by a.emp_no, a.first_name, a.last_name, a.gender
  31. ) as emp_info
  32. join(
  33.     -- 综合公寓信息
  34.     select department_number.emp_no as emp_no,
  35.         department_number.dept_no as dept_no,
  36.         department_name.dept_name as dept_name
  37.     from(
  38.         select emp_no, dept_no
  39.         from current_dept_emp
  40.     ) as department_number
  41.     left outer join(
  42.         select dept_no, dept_name
  43.         from departments
  44.     ) as department_name
  45.     on department_number.dept_no=department_name.dept_no
  46. ) as dep_info
  47. on emp_info.emp_no=dep_info.emp_no
  48. order by average_salary DESC
  49. limit 10;

最终的结果如下所示, 我们可以看到计算得到了每个员工的平均工资和他的公寓的名称.

Mysql基础知识02-基础SQL语句

  • 微信公众号
  • 关注微信公众号
  • weinxin
  • QQ群
  • 我们的QQ群号
  • weinxin
王 茂南
  • 本文由 发表于 2020年3月31日08:03:07
  • 转载请务必保留本文链接：https://mathpretty.com/11979.html
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: