mysql单表查询详解

最新推荐文章于 2023-03-13 14:45:01 发布
最新推荐文章于 2023-03-13 14:45:01 发布
阅读量1.1k 收藏 3
点赞数 2
分类专栏： MySQL从入门到精通 文章标签： mysql 数据库 database
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_46090675/article/details/121261412
版权

文章目录

一、单表查询

查询:用户根据不同的方式从数据库表中获取自己所需要的数据
是数据库操作中非常重要且非常频繁的

1.1 创建数据库

1.创建新库company
2.在company库中创建emp(雇员表)

  emp(雇员编号empno
       雇员名称empname
       雇员的职位empjob
       雇员领导编号empleaderno
       雇员入职日期hiredate
       雇员月薪salary
       雇员的津贴allowance
       雇员部门编号deptno)

3.给表插入数据

#创建库
create database company charset utf8mb4;

#创建表
create table emp(
	empno int unsigned  not null primary key auto_increment,
	empname varchar(20),
	empjob varchar(20),
	empleaderno	int unsigned,
	hiredate date,
	salary decimal(10,3),
	allowance decimal(10,3),
	deptno tinyint	unsigned	
)

mysql> desc emp;
+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| empno       | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| empname     | varchar(20)         | YES  |     | NULL    |                |
| empjob      | varchar(20)         | YES  |     | NULL    |                |
| empleaderno | int(10) unsigned    | YES  |     | NULL    |                |
| hiredate    | date                | YES  |     | NULL    |                |
| salary      | decimal(10,3)       | YES  |     | NULL    |                |
| allowance   | decimal(10,3)       | YES  |     | NULL    |                |
| deptno      | tinyint(3) unsigned | YES  |     | NULL    |                |
+-------------+---------------------+------+-----+---------+----------------+


# 插入数据
insert into emp values
(7369, 'Smith', 'clerk ', 7902,'1980-12-17',800, null, 20),
(7499, 'Allen', 'salesman', 7698,'1981-02-20', 1600,300,30),
(7521, 'Ward' , 'salesman', 7698, '1981-02-22',1250,500,30),
(7566, 'Jones', 'manager', 7839,'1981-04-02',2975, null, 20),
(7654, 'Maritn', 'salesman', 7698,'1981-09-28',1250, 1400,30),
(7698,'Blake', 'manager', 7839,'1981-05-01', 2850,null, 30),
(7782,'Clark' , 'manager', 7839,'1981-06-09', 2450, null, 10),
(7788, 'Scott', 'analyst', 7566,'1987-04-19',3000, null, 20),
(7839, 'King', 'president', null, '1981-11-17', 5000, null, 10),
(7844, 'Turner' , 'salesman', 7698,'1981-09-08',1500,0,30),
(7876, 'Adams', 'clerk', 7788,'1987-05-23',1100, null, 20),
(7900, 'James', 'clerk', 7698,'1981-12-03', 950, null, 30),
(7902,'Ford', 'analyst', 7566,'1981-12-03', 3000, null, 20),
(7934, 'Miller' , ' clerk', 7782,'1982-01-23', 1300,null, 10);


注：由于手误原因，最后一行' clerk'多打一个空格，实验中途才发现，最后改正了，但是不影响实验效果。

1.2 单表查询

1.2.1 查询所有字段

语法:
select 字段名1,字段名2... from 表名
例如:查询雇员表中雇员姓名、编号、职位、领导号、入职时间、薪资、奖金、部门编号

mysql> select empno,empname,empjob,empleader,hiredate,salary,allowance,deptno from emp;
+-------+---------+-----------+-----------+------------+----------+-----------+--------+
| empno | empname | empjob    | empleader | hiredate   | salary   | allowance | deptno |
+-------+---------+-----------+-----------+------------+----------+-----------+--------+
|  7369 | Smith   | clerk     | 7902      | 1980-12-17 |  800.000 |      NULL |     20 |
|  7499 | Allen   | salesman  | 7698      | 1981-02-20 | 1600.000 |   300.000 |     30 |
|  7521 | Ward    | salesman  | 7698      | 1981-02-22 | 1250.000 |   500.000 |     30 |
|  7566 | Jones   | manager   | 7839      | 1981-04-02 | 2975.000 |      NULL |     20 |
|  7654 | Maritn  | salesman  | 7698      | 1981-09-28 | 1250.000 |  1400.000 |     30 |
|  7698 | Blake   | manager   | 7839      | 1981-05-01 | 2850.000 |      NULL |     30 |
|  7782 | Clark   | manager   | 7839      | 1981-06-09 | 2450.000 |      NULL |     10 |
|  7788 | Scott   | analyst   | 7566      | 1987-04-19 | 3000.000 |      NULL |     20 |
|  7839 | King    | president | NULL      | 1981-11-17 | 5000.000 |      NULL |     10 |
|  7844 | Turner  | salesman  | 7698      | 1981-09-08 | 1500.000 |     0.000 |     30 |
|  7876 | Adams   | clerk     | 7788      | 1987-05-23 | 1100.000 |      NULL |     20 |
|  7900 | James   | clerk     | 7698      | 1981-12-03 |  950.000 |      NULL |     30 |
|  7902 | Ford    | analyst   | 7566      | 1981-12-03 | 3000.000 |      NULL |     20 |
|  7934 | Miller  |  clerk    | 7782      | 1982-01-23 | 1300.000 |      NULL |     10 |
+-------+---------+-----------+-----------+------------+----------+-----------+--------+

1.2.2 使用通配符*查询所有字段

语法:select * from 表名
例如:查询雇员表中所有信息

select * from emp;

1.2.3 查询指定字段

语法:select 字段名1,字段名2... from 表名
例如:查询雇员表中雇员姓名、职位、部门号、薪资

mysql> select empname,empjob,deptno,salary from emp;
+---------+-----------+--------+----------+
| empname | empjob    | deptno | salary   |
+---------+-----------+--------+----------+
| Smith   | clerk     |     20 |  800.000 |
| Allen   | salesman  |     30 | 1600.000 |
| Ward    | salesman  |     30 | 1250.000 |
| Jones   | manager   |     20 | 2975.000 |
| Maritn  | salesman  |     30 | 1250.000 |
| Blake   | manager   |     30 | 2850.000 |
| Clark   | manager   |     10 | 2450.000 |
| Scott   | analyst   |     20 | 3000.000 |
| King    | president |     10 | 5000.000 |
| Turner  | salesman  |     30 | 1500.000 |
| Adams   | clerk     |     20 | 1100.000 |
| James   | clerk     |     30 |  950.000 |
| Ford    | analyst   |     20 | 3000.000 |
| Miller  |  clerk    |     10 | 1300.000 |
+---------+-----------+--------+----------+

1.2.4 去重distinct的使用

语法:select distinct 字段名1,字段名2 ...from 表名
例如：查询雇员的部门号
查询雇员在哪几个部门号中

mysql> select distinct deptno from emp;
+--------+
| deptno |
+--------+
|     20 |
|     30 |
|     10 |
+--------+

distinct使用到多个字段上
例如:查询雇员部门号及职位
对比如下两种查询方式


mysql> select distinct deptno,empjob from emp;
+--------+-----------+
| deptno | empjob    |
+--------+-----------+
|     20 | clerk     |
|     30 | salesman  |
|     20 | manager   |
|     30 | manager   |
|     10 | manager   |
|     20 | analyst   |
|     10 | president |
|     30 | clerk     |
|     10 |  clerk    |
+--------+-----------+
9 rows in set (0.00 sec)

mysql> select deptno,empjob from emp;
+--------+-----------+
| deptno | empjob    |
+--------+-----------+
|     20 | clerk     |
|     30 | salesman  |
|     30 | salesman  |
|     20 | manager   |
|     30 | salesman  |
|     30 | manager   |
|     10 | manager   |
|     20 | analyst   |
|     10 | president |
|     30 | salesman  |
|     20 | clerk     |
|     30 | clerk     |
|     20 | analyst   |
|     10 |  clerk    |
+--------+-----------+

注意:distinct使用到多个字段上，只有多个字段的值都相同时才去重

1.2.5 查询中使用算术运算符

在这里插入图片描述
例如:查询雇员的年薪

mysql> select empname,salary*12 from emp;
+---------+-----------+
| empname | salary*12 |
+---------+-----------+
| Smith   |  9600.000 |
| Allen   | 19200.000 |
| Ward    | 15000.000 |
| Jones   | 35700.000 |
| Maritn  | 15000.000 |
| Blake   | 34200.000 |
| Clark   | 29400.000 |
| Scott   | 36000.000 |
| King    | 60000.000 |
| Turner  | 18000.000 |
| Adams   | 13200.000 |
| James   | 11400.000 |
| Ford    | 36000.000 |
| Miller  | 15600.000 |
+---------+-----------+

1.2.6.给字段起别名

语法:select 字段名1 [as] 别名,字段名2 .....from 表名

例如:查询雇员的年薪,别名为年薪

mysql> select empname as 姓名,salary*12 as 年薪 from emp;
+--------+-----------+
| 姓名   | 年薪      |
+--------+-----------+
| Smith  |  9600.000 |
| Allen  | 19200.000 |
| Ward   | 15000.000 |
| Jones  | 35700.000 |
| Maritn | 15000.000 |
| Blake  | 34200.000 |
| Clark  | 29400.000 |
| Scott  | 36000.000 |
| King   | 60000.000 |
| Turner | 18000.000 |
| Adams  | 13200.000 |
| James  | 11400.000 |
| Ford   | 36000.000 |
| Miller | 15600.000 |
+--------+-----------+

注意:如果别名中有空格特殊符号 需要使用单引号括起来

例如:查询雇员的年薪,别名为year sal

mysql> select empname as '姓名',salary*12 as 'year sal' from emp;
+--------+-----------+
| 姓名   | year sal  |
+--------+-----------+
| Smith  |  9600.000 |
| Allen  | 19200.000 |
| Ward   | 15000.000 |
| Jones  | 35700.000 |

1.2.7 concat使用

mysql> select concat('姓名:',empname,'职位:',empjob) from emp;
+--------------------------------------------+
| concat('姓名:',empname,'职位:',empjob)     |
+--------------------------------------------+
| 姓名:Smith职位:clerk                       |
| 姓名:Allen职位:salesman                    |
| 姓名:Ward职位:salesman                     |
| 姓名:Jones职位:manager                     |
| 姓名:Maritn职位:salesman                   |
| 姓名:Blake职位:manager                     |
| 姓名:Clark职位:manager                     |
| 姓名:Scott职位:analyst                     |
| 姓名:King职位:president                    |
| 姓名:Turner职位:salesman                   |
| 姓名:Adams职位:clerk                       |
| 姓名:James职位:clerk                       |
| 姓名:Ford职位:analyst                      |
| 姓名:Miller职位: clerk                     |
+--------------------------------------------+

二、对查询结果排序

升序默认的排序方式 asc（ascending order），降序 desc（ descending order）

2.1 在单个字段中排序

语法:select 查询内容 from 表名 order by 字段 asc|desc
例如:查询雇员信息按雇员薪资升序排序

mysql> select * from emp order by salary asc;
+-------+---------+-----------+-----------+------------+----------+-----------+--------+
| empno | empname | empjob    | empleader | hiredate   | salary   | allowance | deptno |
+-------+---------+-----------+-----------+------------+----------+-----------+--------+
|  7369 | Smith   | clerk     | 7902      | 1980-12-17 |  800.000 |      NULL |     20 |
|  7900 | James   | clerk     | 7698      | 1981-12-03 |  950.000 |      NULL |     30 |
|  7876 | Adams   | clerk     | 7788      | 1987-05-23 | 1100.000 |      NULL |     20 |
|  7521 | Ward    | salesman  | 7698      | 1981-02-22 | 1250.000 |   500.000 |     30 |
|  7654 | Maritn  | salesman  | 7698      | 1981-09-28 | 1250.000 |  1400.000 |     30 |
|  7934 | Miller  |  clerk    | 7782      | 1982-01-23 | 1300.000 |      NULL |     10 |
|  7844 | Turner  | salesman  | 7698      | 1981-09-08 | 1500.000 |     0.000 |     30 |
|  7499 | Allen   | salesman  | 7698      | 1981-02-20 | 1600.000 |   300.000 |     30 |
|  7782 | Clark   | manager   | 7839      | 1981-06-09 | 2450.000 |      NULL |     10 |
|  7698 | Blake   | manager   | 7839      | 1981-05-01 | 2850.000 |      NULL |     30 |
|  7566 | Jones   | manager   | 7839      | 1981-04-02 | 2975.000 |      NULL |     20 |
|  7788 | Scott   | analyst   | 7566      | 1987-04-19 | 3000.000 |      NULL |     20 |
|  7902 | Ford    | analyst   | 7566      | 1981-12-03 | 3000.000 |      NULL |     20 |
|  7839 | King    | president | NULL      | 1981-11-17 | 5000.000 |      NULL |     10 |
+-------+---------+-----------+-----------+------------+----------+-----------+--------+

2.2 在多个字段中使用排序

语法:select 查询内容 from 表名 order by 字段名1 asc|desc,字段名2 asc|desc...

例如:查询雇员信息，先按照雇员薪资升序排序，如果薪资相同则按入职日期降序排序

mysql> select * from emp order by salary asc,hiredate desc;
+-------+---------+-----------+-----------+------------+----------+-----------+--------+
| empno | empname | empjob    | empleader | hiredate   | salary   | allowance | deptno |
+-------+---------+-----------+-----------+------------+----------+-----------+--------+
|  7369 | Smith   | clerk     | 7902      | 1980-12-17 |  800.000 |      NULL |     20 |
|  7900 | James   | clerk     | 7698      | 1981-12-03 |  950.000 |      NULL |     30 |
|  7876 | Adams   | clerk     | 7788      | 1987-05-23 | 1100.000 |      NULL |     20 |
|  7654 | Maritn  | salesman  | 7698      | 1981-09-28 | 1250.000 |  1400.000 |     30 |
|  7521 | Ward    | salesman  | 7698      | 1981-02-22 | 1250.000 |   500.000 |     30 |
|  7934 | Miller  |  clerk    | 7782      | 1982-01-23 | 1300.000 |      NULL |     10 |
|  7844 | Turner  | salesman  | 7698      | 1981-09-08 | 1500.000 |     0.000 |     30 |
|  7499 | Allen   | salesman  | 7698      | 1981-02-20 | 1600.000 |   300.000 |     30 |
|  7782 | Clark   | manager   | 7839      | 1981-06-09 | 2450.000 |      NULL |     10 |
|  7698 | Blake   | manager   | 7839      | 1981-05-01 | 2850.000 |      NULL |     30 |
|  7566 | Jones   | manager   | 7839      | 1981-04-02 | 2975.000 |      NULL |     20 |
|  7788 | Scott   | analyst   | 7566      | 1987-04-19 | 3000.000 |      NULL |     20 |
|  7902 | Ford    | analyst   | 7566      | 1981-12-03 | 3000.000 |      NULL |     20 |
|  7839 | King    | president | NULL      | 1981-11-17 | 5000.000 |      NULL |     10 |
+-------+---------+-----------+-----------+------------+----------+-----------+--------+

注意:排序可以使用别名
例如:查询雇员的年薪，给年薪起别名yearsal 按照年薪降序排序

mysql> select empname, salary*12 as yearsal from emp order by yearsal;
+---------+-----------+
| empname | yearsal   |
+---------+-----------+
| Smith   |  9600.000 |
| James   | 11400.000 |
| Adams   | 13200.000 |
| Ward    | 15000.000 |
| Maritn  | 15000.000 |
| Miller  | 15600.000 |
| Turner  | 18000.000 |
| Allen   | 19200.000 |
| Clark   | 29400.000 |
| Blake   | 34200.000 |
| Jones   | 35700.000 |
| Scott   | 36000.000 |
| Ford    | 36000.000 |
| King    | 60000.000 |
+---------+-----------+

三、条件查询

条件查询：是用户按照一定条件查询，查询满足条件的部分记录
语法:select 查询内容 from 表 where 条件

3.1 比较运算符的使用

在这里插入图片描述

例如：查询部门号为20的雇员信息

mysql> select * from emp where deptno=20;
+-------+---------+---------+-----------+------------+----------+-----------+--------+
| empno | empname | empjob  | empleader | hiredate   | salary   | allowance | deptno |
+-------+---------+---------+-----------+------------+----------+-----------+--------+
|  7369 | Smith   | clerk   | 7902      | 1980-12-17 |  800.000 |      NULL |     20 |
|  7566 | Jones   | manager | 7839      | 1981-04-02 | 2975.000 |      NULL |     20 |
|  7788 | Scott   | analyst | 7566      | 1987-04-19 | 3000.000 |      NULL |     20 |
|  7876 | Adams   | clerk   | 7788      | 1987-05-23 | 1100.000 |      NULL |     20 |
|  7902 | Ford    | analyst | 7566      | 1981-12-03 | 3000.000 |      NULL |     20 |
+-------+---------+---------+-----------+------------+----------+-----------+--------+

查询薪资大于等于3000的雇员信息

mysql> select * from emp where salary>3000;
+-------+---------+-----------+-----------+------------+----------+-----------+--------+
| empno | empname | empjob    | empleader | hiredate   | salary   | allowance | deptno |
+-------+---------+-----------+-----------+------------+----------+-----------+--------+
|  7839 | King    | president | NULL      | 1981-11-17 | 5000.000 |      NULL |     10 |
+-------+---------+-----------+-----------+------------+----------+-----------+--------+

查询雇员的姓名为smith的雇员信息

mysql> select * from emp where empname='smith';
+-------+---------+--------+-----------+------------+---------+-----------+--------+
| empno | empname | empjob | empleader | hiredate   | salary  | allowance | deptno |
+-------+---------+--------+-----------+------------+---------+-----------+--------+
|  7369 | Smith   | clerk  | 7902      | 1980-12-17 | 800.000 |      NULL |     20 |
+-------+---------+--------+-----------+------------+---------+-----------+--------+

注意：MySQL默认的是不区分大小写的,如果需要区分大小写，则可以使用binary关键字
例如：

# 为空
mysql> select * from emp where binary empname='smith'; 
Empty set (0.01 sec)


mysql> select * from emp where binary empname='Smith';
+-------+---------+--------+-----------+------------+---------+-----------+--------+
| empno | empname | empjob | empleader | hiredate   | salary  | allowance | deptno |
+-------+---------+--------+-----------+------------+---------+-----------+--------+
|  7369 | Smith   | clerk  | 7902      | 1980-12-17 | 800.000 |      NULL |     20 |
+-------+---------+--------+-----------+------------+---------+-----------+--------+

3.2. [not]between and的使用

可以查询某区间范围的记录
例如:查询薪资在1200-3000之间的雇员信息
select * from empty where salary between 1200 and 3000

mysql> select * from emp where salary between 1200 and 3000;
+-------+---------+----------+-----------+------------+----------+-----------+--------+
| empno | empname | empjob   | empleader | hiredate   | salary   | allowance | deptno |
+-------+---------+----------+-----------+------------+----------+-----------+--------+
|  7499 | Allen   | salesman | 7698      | 1981-02-20 | 1600.000 |   300.000 |     30 |
|  7521 | Ward    | salesman | 7698      | 1981-02-22 | 1250.000 |   500.000 |     30 |
|  7566 | Jones   | manager  | 7839      | 1981-04-02 | 2975.000 |      NULL |     20 |
|  7654 | Maritn  | salesman | 7698      | 1981-09-28 | 1250.000 |  1400.000 |     30 |
|  7698 | Blake   | manager  | 7839      | 1981-05-01 | 2850.000 |      NULL |     30 |
|  7782 | Clark   | manager  | 7839      | 1981-06-09 | 2450.000 |      NULL |     10 |
|  7788 | Scott   | analyst  | 7566      | 1987-04-19 | 3000.000 |      NULL |     20 |
|  7844 | Turner  | salesman | 7698      | 1981-09-08 | 1500.000 |     0.000 |     30 |
|  7902 | Ford    | analyst  | 7566      | 1981-12-03 | 3000.000 |      NULL |     20 |
|  7934 | Miller  |  clerk   | 7782      | 1982-01-23 | 1300.000 |      NULL |     10 |
+-------+---------+----------+-----------+------------+----------+-----------+--------+

或者
mysql> select * from emp where salary > 1200 and salary < 3000;

注意：between 是开始 and 后是结尾 包括边界
查询薪资不在1200-3000之间的雇员姓名 薪资
select ename,sal from empty where sal not between 1200 and 3000

mysql> select * from emp where salary not between 1200 and 3000;
+-------+---------+-----------+-----------+------------+----------+-----------+--------+
| empno | empname | empjob    | empleader | hiredate   | salary   | allowance | deptno |
+-------+---------+-----------+-----------+------------+----------+-----------+--------+
|  7369 | Smith   | clerk     | 7902      | 1980-12-17 |  800.000 |      NULL |     20 |
|  7839 | King    | president | NULL      | 1981-11-17 | 5000.000 |      NULL |     10 |
|  7876 | Adams   | clerk     | 7788      | 1987-05-23 | 1100.000 |      NULL |     20 |
|  7900 | James   | clerk     | 7698      | 1981-12-03 |  950.000 |      NULL |     30 |
+-------+---------+-----------+-----------+------------+----------+-----------+--------+

3.3. [not] in

表示在指定集合中
语法: select 查询内容 from 表名 where 字段名 in(值1,值2,....)

查询雇员姓名是smith 、scott、king的雇员信息

mysql> select * from emp where empname in('smith','scott','kings');
+-------+---------+---------+-----------+------------+----------+-----------+--------+
| empno | empname | empjob  | empleader | hiredate   | salary   | allowance | deptno |
+-------+---------+---------+-----------+------------+----------+-----------+--------+
|  7369 | Smith   | clerk   | 7902      | 1980-12-17 |  800.000 |      NULL |     20 |
|  7788 | Scott   | analyst | 7566      | 1987-04-19 | 3000.000 |      NULL |     20 |
+-------+---------+---------+-----------+------------+----------+-----------+--------+

查询雇员姓名不是smith 、scott、king的雇员信息

mysql> select * from emp where empname not in('smith','scott','king');
+-------+---------+----------+-----------+------------+----------+-----------+--------+
| empno | empname | empjob   | empleader | hiredate   | salary   | allowance | deptno |
+-------+---------+----------+-----------+------------+----------+-----------+--------+
|  7499 | Allen   | salesman | 7698      | 1981-02-20 | 1600.000 |   300.000 |     30 |
|  7521 | Ward    | salesman | 7698      | 1981-02-22 | 1250.000 |   500.000 |     30 |
|  7566 | Jones   | manager  | 7839      | 1981-04-02 | 2975.000 |      NULL |     20 |
|  7654 | Maritn  | salesman | 7698      | 1981-09-28 | 1250.000 |  1400.000 |     30 |
|  7698 | Blake   | manager  | 7839      | 1981-05-01 | 2850.000 |      NULL |     30 |
|  7782 | Clark   | manager  | 7839      | 1981-06-09 | 2450.000 |      NULL |     10 |
|  7844 | Turner  | salesman | 7698      | 1981-09-08 | 1500.000 |     0.000 |     30 |
|  7876 | Adams   | clerk    | 7788      | 1987-05-23 | 1100.000 |      NULL |     20 |
|  7900 | James   | clerk    | 7698      | 1981-12-03 |  950.000 |      NULL |     30 |
|  7902 | Ford    | analyst  | 7566      | 1981-12-03 | 3000.000 |      NULL |     20 |
|  7934 | Miller  |  clerk   | 7782      | 1982-01-23 | 1300.000 |      NULL |     10 |
+-------+---------+----------+-----------+------------+----------+-----------+--------+

3.4 is null或者 is not null (不区分大小写null Null)

判断某个字段是否为空
例如:查询雇员中，有津贴的雇员信息

mysql> select * from emp where allowance is not null;
+-------+---------+----------+-----------+------------+----------+-----------+--------+
| empno | empname | empjob   | empleader | hiredate   | salary   | allowance | deptno |
+-------+---------+----------+-----------+------------+----------+-----------+--------+
|  7499 | Allen   | salesman | 7698      | 1981-02-20 | 1600.000 |   300.000 |     30 |
|  7521 | Ward    | salesman | 7698      | 1981-02-22 | 1250.000 |   500.000 |     30 |
|  7654 | Maritn  | salesman | 7698      | 1981-09-28 | 1250.000 |  1400.000 |     30 |
|  7844 | Turner  | salesman | 7698      | 1981-09-08 | 1500.000 |     0.000 |     30 |
+-------+---------+----------+-----------+------------+----------+-----------+--------+

查询雇员中，没有津贴的雇员信息

mysql> select * from emp where allowance is null;
+-------+---------+-----------+-----------+------------+----------+-----------+--------+
| empno | empname | empjob    | empleader | hiredate   | salary   | allowance | deptno |
+-------+---------+-----------+-----------+------------+----------+-----------+--------+
|  7369 | Smith   | clerk     | 7902      | 1980-12-17 |  800.000 |      NULL |     20 |
|  7566 | Jones   | manager   | 7839      | 1981-04-02 | 2975.000 |      NULL |     20 |
|  7698 | Blake   | manager   | 7839      | 1981-05-01 | 2850.000 |      NULL |     30 |
|  7782 | Clark   | manager   | 7839      | 1981-06-09 | 2450.000 |      NULL |     10 |
|  7788 | Scott   | analyst   | 7566      | 1987-04-19 | 3000.000 |      NULL |     20 |
|  7839 | King    | president | NULL      | 1981-11-17 | 5000.000 |      NULL |     10 |
|  7876 | Adams   | clerk     | 7788      | 1987-05-23 | 1100.000 |      NULL |     20 |
|  7900 | James   | clerk     | 7698      | 1981-12-03 |  950.000 |      NULL |     30 |
|  7902 | Ford    | analyst   | 7566      | 1981-12-03 | 3000.000 |      NULL |     20 |
|  7934 | Miller  |  clerk    | 7782      | 1982-01-23 | 1300.000 |      NULL |     10 |
+-------+---------+-----------+-----------+------------+----------+-----------+--------+

3.5 模糊查询

可以查询以XX开头 以XX结尾 包含XX 第几个是X
语法:

  select 查询内容 from 表名  where 字段名 like  
  %: 指任意长度的字符(可以有也可以没有)
  _: 匹配一个字符

例如:查询雇员中雇员姓名以S开头的雇员信息

mysql> select * from emp where binary empname like 'S%';
+-------+---------+---------+-----------+------------+----------+-----------+--------+
| empno | empname | empjob  | empleader | hiredate   | salary   | allowance | deptno |
+-------+---------+---------+-----------+------------+----------+-----------+--------+
|  7369 | Smith   | clerk   | 7902      | 1980-12-17 |  800.000 |      NULL |     20 |
|  7788 | Scott   | analyst | 7566      | 1987-04-19 | 3000.000 |      NULL |     20 |
+-------+---------+---------+-----------+------------+----------+-----------+--------+

查询雇员中雇员姓名以S结尾的雇员信息

mysql> select * from emp where binary empname like '%S';
Empty set (0.00 sec)

查询雇员中雇员姓名包含S的雇员信息

mysql> select * from emp where binary empname like '%S%';
+-------+---------+---------+-----------+------------+----------+-----------+--------+
| empno | empname | empjob  | empleader | hiredate   | salary   | allowance | deptno |
+-------+---------+---------+-----------+------------+----------+-----------+--------+
|  7369 | Smith   | clerk   | 7902      | 1980-12-17 |  800.000 |      NULL |     20 |
|  7788 | Scott   | analyst | 7566      | 1987-04-19 | 3000.000 |      NULL |     20 |
+-------+---------+---------+-----------+------------+----------+-----------+--------

查询雇员姓名第二个字符是l的雇员信息

mysql> select * from emp where  empname like '_l%';
+-------+---------+----------+-----------+------------+----------+-----------+--------+
| empno | empname | empjob   | empleader | hiredate   | salary   | allowance | deptno |
+-------+---------+----------+-----------+------------+----------+-----------+--------+
|  7499 | Allen   | salesman | 7698      | 1981-02-20 | 1600.000 |   300.000 |     30 |
|  7698 | Blake   | manager  | 7839      | 1981-05-01 | 2850.000 |      NULL |     30 |
|  7782 | Clark   | manager  | 7839      | 1981-06-09 | 2450.000 |      NULL |     10 |
+-------+---------+----------+-----------+------------+----------+-----------+--------+

3.6 条件中的逻辑运算符

3.6.1 and

指并且 在查询时候只有满足所有查询条件的记录才会被查询出来
语法：select 查询内容 from 表名 where 条件1 and 条件2 and....
例如:查询雇员表中部门号为20 ，职位为clerk的雇员姓名 部门编号 职位

mysql> select empname,deptno,empjob from emp where empjob='clerk' and deptno=20;
+---------+--------+--------+
| empname | deptno | empjob |
+---------+--------+--------+
| Smith   |     20 | clerk  |
| Adams   |     20 | clerk  |
+---------+--------+--------+

3.6.2 or

指或者 在查询时候只要满足任意一个条件的记录就能被查询出来
语法: select 查询内容 from 表名 where 条件1 or 条件2 or....

查询雇员表中部门号为20 ，或者职位为clerk的雇员姓名 部门编号 职位

mysql> select empname,deptno,empjob from emp where empjob='clerk' or deptno=20;
+---------+--------+---------+
| empname | deptno | empjob  |
+---------+--------+---------+
| Smith   |     20 | clerk   |
| Jones   |     20 | manager |
| Scott   |     20 | analyst |
| Adams   |     20 | clerk   |
| James   |     30 | clerk   |
| Ford    |     20 | analyst |
+---------+--------+---------+

四、分页查询

语法:

select 查询内容
    from  表名
	where 条件
	order by  字段名  asc|desc
	limit a,b

注意:
1.limit 从哪条记录开始, 每页显示的条数,且默认第一条的值a=0
例如，显示第n页,每页m条信息的语法为

select * from 表名 limit（n-1）*m，m
2.如果limit与order by连用时候，先排序再分页

例如查询薪资大于1200的员工信息，要求按薪资降序排序，只显示第一页，每页三条数据。


mysql> select * from emp where salary>1200 order by salary desc limit 0,3;
+-------+---------+-----------+-----------+------------+----------+-----------+--------+
| empno | empname | empjob    | empleader | hiredate   | salary   | allowance | deptno |
+-------+---------+-----------+-----------+------------+----------+-----------+--------+
|  7839 | King    | president | NULL      | 1981-11-17 | 5000.000 |      NULL |     10 |
|  7788 | Scott   | analyst   | 7566      | 1987-04-19 | 3000.000 |      NULL |     20 |
|  7902 | Ford    | analyst   | 7566      | 1981-12-03 | 3000.000 |      NULL |     20 |
+-------+---------+-----------+-----------+------------+----------+-----------+--------+

五、函数的使用

5.1 单行函数

5.1.1 字符函数

5.1.1.1 concat() 拼接字符串

例如:查询雇员的信息，以指定格式输出
雇员姓名:XX,薪资:XX,职位：XX,入职日期:XX,年薪:XX
例如：

mysql>  select concat('雇员姓名:',empname,'雇员职位:',empjob,'雇员薪资:',salary) from emp;
+---------------------------------------------------------------------------------+
| concat('雇员姓名:',empname, '雇员职位:',empjob, '雇员薪资:',salary)             |
+---------------------------------------------------------------------------------+
| 雇员姓名:Smith雇员职位:clerk 雇员薪资:800.000                                   |
| 雇员姓名:Allen雇员职位:salesman雇员薪资:1600.000                                |
| 雇员姓名:Ward雇员职位:salesman雇员薪资:1250.000                                 |
| 雇员姓名:Jones雇员职位:manager雇员薪资:2975.000                                 |
| 雇员姓名:Maritn雇员职位:salesman雇员薪资:1250.000                               |
| 雇员姓名:Blake雇员职位:manager雇员薪资:2850.000                                 |
| 雇员姓名:Clark雇员职位:manager雇员薪资:2450.000                                 |
| 雇员姓名:Scott雇员职位:analyst雇员薪资:3000.000                                 |
| 雇员姓名:King雇员职位:president雇员薪资:5000.000                                |
| 雇员姓名:Turner雇员职位:salesman雇员薪资:1500.000                               |
| 雇员姓名:Adams雇员职位:clerk雇员薪资:1100.000                                   |
| 雇员姓名:James雇员职位:clerk雇员薪资:950.000                                    |
| 雇员姓名:Ford雇员职位:analyst雇员薪资:3000.000                                  |
| 雇员姓名:Miller雇员职位: clerk雇员薪资:1300.000                                 |
+---------------------------------------------------------------------------------+
5.1.1.2 length()计算字符串的长度

例如:查询雇员中雇员姓名长度是4的雇员信息

注意：
mysql中的length()是按照字节统计长度的，汉字的长度和编码有关系,英文不受影响
python中的len()是按照字符统计长度的，汉字的长度和编码无关，一个汉字长度就为1，英文无影响

mysql>  select * from emp where length(empname)=4;
+-------+---------+-----------+-----------+------------+----------+-----------+--------+
| empno | empname | empjob    | empleader | hiredate   | salary   | allowance | deptno |
+-------+---------+-----------+-----------+------------+----------+-----------+--------+
|  7521 | Ward    | salesman  | 7698      | 1981-02-22 | 1250.000 |   500.000 |     30 |
|  7839 | King    | president | NULL      | 1981-11-17 | 5000.000 |      NULL |     10 |
|  7902 | Ford    | analyst   | 7566      | 1981-12-03 | 3000.000 |      NULL |     20 |
+-------+---------+-----------+-----------+------------+----------+-----------+--------+
5.1.1.3 lower() upper()转换大小写

例如:查询雇员姓名,大写的雇员姓名 ，小写的雇员姓名

mysql> select empname from emp;
+---------+
| empname |
+---------+
| Smith   |
| Allen   |
| Ward    |
| Jones   |
| Maritn  |
| Blake   |
| Clark   |
| Scott   |
| King    |
| Turner  |
| Adams   |
| James   |
| Ford    |
| Miller  |
+---------+
14 rows in set (0.00 sec)

mysql> select lower(empname) from emp;
+----------------+
| lower(empname) |
+----------------+
| smith          |
| allen          |
| ward           |
| jones          |
| maritn         |
| blake          |
| clark          |
| scott          |
| king           |
| turner         |
| adams          |
| james          |
| ford           |
| miller         |
+----------------+
14 rows in set (0.01 sec)

mysql> select upper(empname) from emp;
+----------------+
| upper(empname) |
+----------------+
| SMITH          |
| ALLEN          |
| WARD           |
| JONES          |
| MARITN         |
| BLAKE          |
| CLARK          |
| SCOTT          |
| KING           |
| TURNER         |
| ADAMS          |
| JAMES          |
| FORD           |
| MILLER         |
+----------------+
5.1.1.4 replace()

在指定的字符串中，将某子串替换为新的字符串
格式：replace(目标字符串,查找的子串,新字符串)
例如:将员工姓名中l替换为L

mysql> select replace(empname,'l','L') from emp;
+--------------------------+
| replace(empname,'l','L') |
+--------------------------+
| Smith                    |
| ALLen                    |
| Ward                     |
| Jones                    |
| Maritn                   |
| BLake                    |
| CLark                    |
| Scott                    |
| King                     |
| Turner                   |
| Adams                    |
| James                    |
| Ford                     |
| MiLLer                   |
+--------------------------+
5.1.1.5 substring()截取子串

substring(目标字符串,开始位置,长度)
注意:开始索引是从1开始，不是0
例如:获取雇员姓名的前四个字符

mysql> select substring(empname,1,4) from emp;
+------------------------+
| substring(empname,1,4) |
+------------------------+
| Smit                   |
| Alle                   |
| Ward                   |
| Jone                   |
| Mari                   |
| Blak                   |
| Clar                   |
| Scot                   |
| King                   |
| Turn                   |
| Adam                   |
| Jame                   |
| Ford                   |
| Mill                   |
+------------------------+

5.1.2 .数值函数

5.1.2.1 abs()

作用：取绝对值

mysql> select abs(-1),abs(3);
+---------+--------+
| abs(-1) | abs(3) |
+---------+--------+
|       1 |      3 |
+---------+--------+
1 row in set (0.00 sec
5.1.2.2 pi()

作用：获取圆周率

mysql> select pi();
+----------+
| pi()     |
+----------+
| 3.141593 |
+----------+
5.1.2.3 mod()
作用:取模

例如:select mod(3,2)

mysql> select mod(3,2);
+----------+
| mod(3,2) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> select mod(2,3);
+----------+
| mod(2,3) |
+----------+
|        2 |
+----------+
5.1.2.4 pow()

作用:求一个数的n次方

mysql> select power(3,2);
+------------+
| power(3,2) |
+------------+
|          9 |
+------------+
5.1.2.5 ceil() floor()
  • ceil():向上取整
  • floor():向下取整
mysql> select ceil(3.14);
+------------+
| ceil(3.14) |
+------------+
|          4 |
+------------+
1 row in set (0.00 sec)

mysql> select floor(3.99);
+-------------+
| floor(3.99) |
+-------------+
|           3 |
+-------------+
5.1.2.6 round()

round(num):返回四舍五入的整数
round(num,n)：返回四舍五入n位小数

mysql> select round(5.4),round(6.8),round(5.34,1),round(6.789,2);
+------------+------------+---------------+----------------+
| round(5.4) | round(6.8) | round(5.34,1) | round(6.789,2) |
+------------+------------+---------------+----------------+
|          5 |          7 |           5.3 |           6.79 |
+------------+------------+---------------+----------------+
1 row in set (0.00 sec)
5.1.2.7 truncate() 截取
truncate(num,n) 其中n的取值可以是0,1,2..如果n取值为0代表截取整数

mysql> select truncate(5.3,0),truncate(5.67,1),truncate(5.789,2);
+-----------------+------------------+-------------------+
| truncate(5.3,0) | truncate(5.67,1) | truncate(5.789,2) |
+-----------------+------------------+-------------------+
|               5 |              5.6 |              5.78 |
+-----------------+------------------+-------------------+
5.1.2.8 rand()
作用:获取浮点类型的随机数,范围0-1.0 其中包括0但不包括1

mysql> select rand(),rand(),rand();
+--------------------+---------------------+--------------------+
| rand()             | rand()              | rand()             |
+--------------------+---------------------+--------------------+
| 0.5739643895756121 | 0.11088874108240822 | 0.8325505674188496 |
+--------------------+---------------------+--------------------+

5.1.3 日期时间函数

5.1.3.1 now()

获取当前日期和时间 包括年月日 时分秒

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-11-11 16:25:24 |
+---------------------+
1 row in set (0.00 sec)
5.1.3.2 curdate()

获取当前日期 只包括年月日

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2021-11-11 |
+------------+
5.1.3.3 curtime()

获取当前时间 只包括时分秒

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 16:27:22  |
+-----------+
5.1.3.4 sysdate()、now()

获取函数执行时的日期和时间
now()获取SQL执行时的日期和时间

mysql> select sysdate(),now();
+---------------------+---------------------+
| sysdate()           | now()               |
+---------------------+---------------------+
| 2021-11-11 16:43:16 | 2021-11-11 16:43:16 |
+---------------------+---------------------+


mysql> select sysdate(),now(),sleep(2),sysdate(),now();
+---------------------+---------------------+----------+---------------------+---------------------+
| sysdate()           | now()               | sleep(2) | sysdate()           | now()               |
+---------------------+---------------------+----------+---------------------+---------------------+
| 2021-11-11 16:43:58 | 2021-11-11 16:43:58 |        0 | 2021-11-11 16:44:00 | 2021-11-11 16:43:58 |
+---------------------+---------------------+----------+---------------------+---------------------+
5.1.3.5 dayofyear()、week()

获取某个日期是所在年份的第几天
week()获取某个日期是所在年份的第几周
例如:select dayofyear(now()),week(now())

mysql> select dayofyear(now()),week(now());
+------------------+-------------+
| dayofyear(now()) | week(now()) |
+------------------+-------------+
|              315 |          45 |
+------------------+-------------+
5.1.3.6 datediff()

计算两个日期之间的时间间隔
例如:计算2021-1-1距离现在时间间隔

mysql>  select datediff('2021-1-1',now());
+----------------------------+
| datediff('2021-1-1',now()) |
+----------------------------+
|                       -314 |
+----------------------------+
5.1.3.7 date_add() date_sub() 实现日期的加减运算

date_add(日期,时间间隔类型关键字interval 时间间隔类型对应的表达式 时间间隔类型)
例如 day_hour 1_12 代表 1天12小时
year_month 2_1 代表 2年1个月

例如，现在时间加上两年一个月的时间

mysql> select date_add(now(),interval '2_1' year_month);
+-------------------------------------------+
| date_add(now(),interval '2_1' year_month) |
+-------------------------------------------+
| 2023-12-11 16:57:30                       |
+-------------------------------------------+

现在时间减去三天的时间

mysql> select date_sub(now(),interval '3' day);
+----------------------------------+
| date_sub(now(),interval '3' day) |
+----------------------------------+
| 2021-11-08 17:02:25              |
+----------------------------------+

5.1.4 流程控制函数

5.1.4.1 if(条件,t,f) 如果条件成立返回t,否则返回f

例如:select if(1>2,‘1大于2’,‘1小于2’)

mysql> select if(1>2,'1大于2','1小于2');
+-------------------------------+
| if(1>2,'1大于2','1小于2')     |
+-------------------------------+
| 1小于2                        |
+-------------------------------+
1 row in set (0.00 sec)

查询雇员的薪资，如果薪资>=3000 输入’高薪’ 否则’低薪’

mysql> select empname,salary,if(salary>3000,'高薪','低薪') from emp;
+---------+----------+-----------------------------------+
| empname | salary   | if(salary>3000,'高薪','低薪')     |
+---------+----------+-----------------------------------+
| Smith   |  800.000 | 低薪                              |
| Allen   | 1600.000 | 低薪                              |
| Ward    | 1250.000 | 低薪                              |
| Jones   | 2975.000 | 低薪                              |
| Maritn  | 1250.000 | 低薪                              |
| Blake   | 2850.000 | 低薪                              |
| Clark   | 2450.000 | 低薪                              |
| Scott   | 3000.000 | 低薪                              |
| King    | 5000.000 | 高薪                              |
| Turner  | 1500.000 | 低薪                              |
| Adams   | 1100.000 | 低薪                              |
| James   |  950.000 | 低薪                              |
| Ford    | 3000.000 | 低薪                              |
| Miller  | 1300.000 | 低薪                              |
+---------+----------+-----------------------------------+
5.1.4.2 ifnull(值1,值2)

如果值1不为空则返回值1，否则返回值2
查询雇员的年
select salary*12 from emp;
select empname,(salary+allowance)*12 from emp;
select empname,(salary+ifnull(allowance,0))*12 from emp; # 因为allowance有null值。

mysql> select empname,(salary+allowance)*12 from emp;
+---------+-----------------------+
| empname | (salary+allowance)*12 |
+---------+-----------------------+
| Smith   |                  NULL |
| Allen   |             22800.000 |
| Ward    |             21000.000 |
| Jones   |                  NULL |
| Maritn  |             31800.000 |
| Blake   |                  NULL |
| Clark   |                  NULL |
| Scott   |                  NULL |
| King    |                  NULL |
| Turner  |             18000.000 |
| Adams   |                  NULL |
| James   |                  NULL |
| Ford    |                  NULL |
| Miller  |                  NULL |
+---------+-----------------------+

有null值，显然不合理，需要做如下查询

mysql> select empname,(salary+ifnull(allowance,0))*12 from emp;
+---------+---------------------------------+
| empname | (salary+ifnull(allowance,0))*12 |
+---------+---------------------------------+
| Smith   |                        9600.000 |
| Allen   |                       22800.000 |
| Ward    |                       21000.000 |
| Jones   |                       35700.000 |
| Maritn  |                       31800.000 |
| Blake   |                       34200.000 |
| Clark   |                       29400.000 |
| Scott   |                       36000.000 |
| King    |                       60000.000 |
| Turner  |                       18000.000 |
| Adams   |                       13200.000 |
| James   |                       11400.000 |
| Ford    |                       36000.000 |
| Miller  |                       15600.000 |
+---------+---------------------------------+
5.1.4.3 nullif(值1,值2)

如果值1等于值2返回null,否则返回值1
例如 select nullif(1,2),nullif(1,1)

mysql> select nullif(1,2),nullif(1,1);
+-------------+-------------+
| nullif(1,2) | nullif(1,1) |
+-------------+-------------+
|           1 |        NULL |
+-------------+-------------+
5.1.4.4 case 值
when 值1 then 结果1
when 值2 then 结果2
...
else 其他结果
end

mysql> select  case 1
    -> when 1 then '结果是1'
    -> when 2 then '结果是2'
    -> else '其他结果'
    -> end;
+----------------------------------------------------------------------------------+
| case 1
when 1 then '结果是1'
when 2 then '结果是2'
else '其他结果'
end           |
+----------------------------------------------------------------------------------+
| 结果是1                                                                          |
+----------------------------------------------------------------------------------+
5.1.4.5 case 条件
 when 条件  then  结果1
 when 条件  then  结果2
 .....
 else 其他结果
 end

例如:查询雇员薪资 如果薪资>=3000返回 ‘高薪’ 否则’低薪’

select salary,case
    when sal>=3000 then '高薪'
	else '低薪'
	end 
	as  '薪资水平'  from emp;

mysql> select salary,case
    -> when salary >=3000 then '高薪'
    -> else '低薪'
    -> end  
    -> as'薪资水平'from emp;
+----------+--------------+
| salary   | 薪资水平     |
+----------+--------------+
|  800.000 | 低薪         |
| 1600.000 | 低薪         |
| 1250.000 | 低薪         |
| 2975.000 | 低薪         |
| 1250.000 | 低薪         |
| 2850.000 | 低薪         |
| 2450.000 | 低薪         |
| 3000.000 | 高薪         |
| 5000.000 | 高薪         |
| 1500.000 | 低薪         |
| 1100.000 | 低薪         |
|  950.000 | 低薪         |
| 3000.000 | 高薪         |
| 1300.000 | 低薪         |
+----------+--------------+

5.2 多行函数

1.多行函数:操作一组数据(多行记录) 返回一个结果 也叫分组函数
2.多行函数大多使用于统计

例如:统计各部门中雇员的人数
统计各部门中最高最低薪资是多少

5.2.1 count()

统计表中记录的数目

a.统计表中记录的总数目count(*)
例如查询雇员表中有多少条记录

mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
|       14 |
+----------+

b.count(exp)统计exp值非空的记录数目
例如:查询雇员表中有多少位雇员有津贴

mysql> select count(allowance) from emp;
+------------------+
| count(allowance) |
+------------------+
|                4 |
+------------------+

c.count(distinct(exp)) 返回表达式exp的值不重复且非空的总记录数目
例如:统计雇员表中有多少位雇员是领导

select count(distinct(empleader)) from emp;–统计的是除董事长外的领导人数(董事长是null)

mysql> select count(distinct(empleader)) from emp;
+----------------------------+
| count(distinct(empleader)) |
+----------------------------+
|                          6 |
+----------------------------+

统计雇员表中所有领导
ifnull(值1,值2) 如果值1不为null返回值1 ，否则返回值2
select count(distinct(ifnull(empleader,1))) from emp; # 因为经理的领导为null

mysql>  select count(distinct(ifnull(empleader,1))) from emp;
+--------------------------------------+
| count(distinct(ifnull(empleader,1))) |
+--------------------------------------+
|                                    7 |
+--------------------------------------+

5.2.2 sum()

a.sum(exp):返回表达式值得总和

mysql> select sum(salary) as '总月薪' from emp;
+-----------+
| 总月薪    |
+-----------+
| 29025.000 |
+-----------+

b.sum(distinct(exp))返回不重复的表达式exp的总和

mysql> select sum(salary),sum(distinct(salary)) from emp;
+-------------+-----------------------+
| sum(salary) | sum(distinct(salary)) |
+-------------+-----------------------+
|   29025.000 |             24775.000 |
+-------------+-----------------------+

5.2.3 avg()

a.avg(exp):返回表达式值得平均值
例如: select avg(salary) from emp;

mysql> select avg(salary) from emp; 
+--------------+
| avg(salary)  |
+--------------+
| 2073.2142857 |
+--------------+

b.avg(distinct(exp)):返回不重复的表达式exp的平均值
例如 select avg(distinct(salary)) from emp;

mysql> select avg(distinct(salary)) from emp;
+-----------------------+
| avg(distinct(salary)) |
+-----------------------+
|          2064.5833333 |
+-----------------------

5.2.4 max() min()

max(exp):返回表达式值得最大值
min(exp)：返回表达式值得最小值
例如

mysql> select min(salary),max(salary) from emp;
+-------------+-------------+
| min(salary) | max(salary) |
+-------------+-------------+
|     800.000 |    5000.000 |
+-------------+-------------+
1 row in set (0.00 sec)

5.3 分组统计

1.语法：

select 查询内容
	from  表名
	[where 条件]
	[group by 分组字段名1,分组字段名2...]
	[order by 字段名 asc|desc]
	[limit]

2.使用举例
a.求每个部门的人数
select deptno,count(*) from emp group by deptno;

mysql> select deptno,count(*) from emp group by deptno;
+--------+----------+
| deptno | count(*) |
+--------+----------+
|     10 |        3 |
|     20 |        5 |
|     30 |        6 |
+--------+----------+

b.求每个部门的平均工资
select deptno,avg(sal) from emp group by deptno;

mysql> select deptno,avg(salary) from emp group by deptno;
+--------+--------------+
| deptno | avg(salary)  |
+--------+--------------+
|     10 | 2916.6666667 |
|     20 | 2175.0000000 |
|     30 | 1566.6666667 |
+--------+--------------+

c.求每个部门中最高工资和人数
select deptno,max(sal),count(*) from emp group by deptno;

mysql> select deptno, max(salary),count(*) from emp group by deptno;
+--------+-------------+----------+
| deptno | max(salary) | count(*) |
+--------+-------------+----------+
|     10 |    5000.000 |        3 |
|     20 |    3000.000 |        5 |
|     30 |    2850.000 |        6 |
+--------+-------------+----------+

d.求每个岗位的人数

mysql> select empjob,count(*) as '总人数' from emp group by empjob;
+-----------+-----------+
| empjob    | 总人数    |
+-----------+-----------+
| analyst   |         2 |
| clerk     |         4 |
| manager   |         3 |
| president |         1 |
| salesman  |         4 |
+-----------+-----------+

e.显示每个部门不同岗位的人数

mysql> select deptno,empjob,count(*) as '总人数' from emp group by deptno,empjob;
+--------+-----------+-----------+
| deptno | empjob    | 总人数    |
+--------+-----------+-----------+
|     10 |  clerk    |         1 |
|     10 | manager   |         1 |
|     10 | president |         1 |
|     20 | analyst   |         2 |
|     20 | clerk     |         2 |
|     20 | manager   |         1 |
|     30 | clerk     |         1 |
|     30 | manager   |         1 |
|     30 | salesman  |         4 |
+--------+-----------+-----------+
3.注意
a.查询字段，如果没有在多行函数中包含，则必须是分组字段 下面查询会报错
select empname,empjob,sum(salary) from emp group by empjob;
b.如果没有group by ,查询字段不能与多行函数一起查询
select sal,empno from emp; 合法
select sum(sal),empno from emp; 不合法
c.不允许在where条件中使用多行函数,若要是用，必须要用having

5.4 having子句

1.语法：

select 查询内容
	 from 表名
	 [where 条件]
	 [group by 分组字段]
	 [having  条件]
	 [order by]
	 [limit]

select from --where过滤—group by–having过滤

2.使用举例
a.每个部门不同岗位的人数，且人数大于2

mysql> select deptno,empjob,count(*) from emp group by deptno,empjob having count(*) >=2;
+--------+----------+----------+
| deptno | empjob   | count(*) |
+--------+----------+----------+
|     20 | analyst  |        2 |
|     20 | clerk    |        2 |
|     30 | salesman |        4 |
+--------+----------+----------+

b.在emp表中列出工资最小值小于2000的职位
先查询每个职位的最低薪资
select empjob,min(salary) from emp group by empjob;

mysql> select empjob,min(salary) from emp group by empjob having min(salary)<2000;
+----------+-------------+
| empjob   | min(salary) |
+----------+-------------+
| clerk    |     800.000 |
| salesman |    1250.000 |
+----------+-------------+

c.列出平均工资大于1200的部门和职位搭配组合
先求每个部门不同职位的平均工资
select avg(sal) from emp group by deptno,job

mysql> select deptno,empjob,avg(salary) from emp group by deptno,empjob having avg(salary)>12200;
+--------+-----------+--------------+
| deptno | empjob    | avg(salary)  |
+--------+-----------+--------------+
|     10 | clerk     | 1300.0000000 |
|     10 | manager   | 2450.0000000 |
|     10 | president | 5000.0000000 |
|     20 | analyst   | 3000.0000000 |
|     20 | manager   | 2975.0000000 |
|     30 | manager   | 2850.0000000 |
|     30 | salesman  | 1400.0000000 |
+--------+-----------+--------------+
万wu皆可爱
关注
  • 2
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql锁定单个表的方法
09-10
主要介绍了mysql锁定单个表的方法 ,需要的朋友可以参考下
MySQL多表查询详解上
09-08
MySQL多表查询是数据库操作中的常见任务，尤其在处理复杂数据关系时不可或缺。本文将深入讲解MySQL中的多表查询，包括各种类型的连接查询，旨在帮助读者理解和掌握如何有效地从多个表中提取所需信息。 首先，我们要...
MySQL单表查询
最新发布
weixin_45392969的博客
03-13 2159
单表查询是指从一张表数据中查询所需的数据。本节将介绍单表查询中的各种基本的查询方 式，主要有查询所有字段、查询指定字段、查询指定记录、查询空值、多条件的查询、对查询结果 进行排序等。
【CSDN软件工程师能力认证学习精选】SQL语句查询
CSDN高校俱乐部官方博客
02-23 1366
CSDN软件工程师能力认证（以下简称C系列认证）是由中国软件开发者网CSDN制定并推出的一个能力认证标准。C系列认证历经近一年的实际线下调研、考察、迭代、测试，并梳理出软件工程师开发过程中所需的各项技术技能，结合企业招聘需求和人才应聘痛点，基于公开、透明、公正的原则，甑别人才时确保真实业务场景、全部上机实操、所有过程留痕、存档不可篡改。 我们每天将都会精选CSDN站内技术文章供大家学习，帮助大家系统化学习IT技术。 1.1 排序 通过order by语句，可以将查询出的结果进行排序。放置在sele.
mysql-单表查询
qq_59323864的博客
09-09 330
mysql> CREATE TABLE work_date SELECT 职工号,姓名,工作时间 FROM worker;INSERT INTO `表名` （``,``,``,）VALUES（ 12,33,33）mysql> SELECT DISTINCT 部门号 FROM worker;mysql> SELECT COUNT(职工号) FROM worker;`部门号` int（11）NOT NULL,-> where `性别`='女';mysql> select 部门号,姓名,
Hive之DQL操作
luoyepiaoxue2014的博客
11-25 1082
Hive系列第六章（实际是第七篇，就不改目录序号了，大家知道就行，后续的篇章类推即可）
MySQL多表查询详解下
09-08
MySQL的多表查询是数据库操作中的重要组成部分，它允许我们从多个相关表中获取数据，合并成一个单一的结果集。本篇文章将详细讲解MySQL的多表查询，特别是1999年的SQL标准语法。 首先，多表查询的核心是连接（JOIN...
MySQL单表查询实例详解
12-14
以下操作将在该表中进行 create table student ( id int unsigned primary key auto_increment, name char(12) not null, gender enum("male","famale") default "male", age tinyint unsigned not null, hoc_...
MySQL多表查询详解
03-08
文档讲述了MySQL中的多表查询和一些其他的知识点，这是以后经常用到的
MySql实现跨表查询的方法详解
09-10
在MySQL数据库中，跨表查询是处理多个相关表数据的关键技术。这允许我们从不同的表中获取并结合信息，以满足复杂的数据需求。本篇文章将深入探讨如何在MySQL中实现跨表查询，以及如何利用共享的字段来关联数据。 ...
MySQL单表查询常见操作实例总结
12-16
本文实例总结了MySQL单表查询常见操作。分享给大家供大家参考，具体如下： 创建fruits表： CREATE TABLE fruits ( f_id char(10) NOT NULL, s_id INT NOT NULL, f_name char(255) NOT NULL, f_price decimal(8,2) NOT NULL, PRIMARY KEY(f_id) ) ; INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES('a1', 101,'apple',5.2), ('b1',101,'bla
单表60亿记录等大数据场景的MySQL优化和运维之道 | 高可用架构
weixin_45583158的博客
08-09 4106
此文是根据杨尚刚在【QCON高可用架构群】中，针对MySQL在单表海量记录等场景下，业界广泛关注的MySQL问题的经验分享整理而成，转发请注明出处。杨尚刚，美图公司数据库...
mysql查询每个表的数据量_Mysql 单表适合的最大数据量是多少？如何优化其性能？...
weixin_39728909的博客
11-23 1654
我们说 Mysql 单表适合存储的最大数据量，自然不是说能够存储的最大数据量，如果是说能够存储的最大量，那么，如果你使用自增 ID，最大就可以存储 2^32 或 2^64 条记录了，这是按自增 ID 的数据类型 int 或 bigint 来计算的；如果你不使用自增 id，且没有 id 最大值的限制，如使用足够长度的随机字符串，那么能够限制单表最大数据量的就只剩磁盘空间了。显然我们不是在讨论这个问题...
Mysql数据库的单表查询
toytoytelecar的博客
04-22 417
我们在使用Mysql数据库存储数据时，对数据的查询方法是至关重要的，此博客主要介绍Mysql数据库的查询数据方法。 一.单表查询 单表查询就是我们仅对一个表进行查询，我们可以首先创建一个名为table的表单： create table student( id char(36) primary key, name varchar(8) not null, age int(3) default 0, mobile char(11), address varchar(150) ) i...
mysql 练习3 单表查询_5000字总结MySQL单表查询，新手看这一篇足够了！| SQL数分03...
weixin_31653453的博客
01-27 125
通过写SQL查询，我们可以发现很多简单查询语句主要就是由一些算术操作、字段操作、函数还有各种子句构成的，今天我们从这个角度对MySQL单表查询的基础知识进行一个汇总。Part.1是 什 么计算：计算字段、算术操作符、算术计算、字段拼接、格式化显示等函数：常用统计函数等子句：排序、过滤、分组、分组过滤、去重、限制等Part.2怎 么 玩这里我们以上面的薪酬统计表为例进行说明，具体字段解释可参阅：My...
arg是什么函数_Java学习第031天---组函数,子查询
weixin_39718890的博客
11-23 516
前面学习的 单标 和 多表 的练习题总结--单表查询--1 查询当前用户下的所有表select * from tab;--2 查询雇员表中所有信息select * from emp;--3 查询雇员编号,姓名,工作,工资select empno,ename,job,sal from emp;--4 查询雇员编号,姓名,工作,工资,并显示中文(为列起别名)select empno as "编号",e...
SQLServer单表查询
徐海兴的专栏
10-19 923
SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) AS numorders FROM Sales.Orders WHERE custid = 71 GROUP BY empid, YEAR(orderdate) HAVING COUNT(*)>1 ORDER BY empid,orderyear; 1.查询顺序
Mysql单表查询和多表查询
小人物的博客
10-01 2549
单表查询 一 单表查询的语法 #查询数据的本质：mysql会到你本地的硬盘上找到对应的文件，然后打开文件，按照你的查询条件来找出你需要的数据。下面是完整的一个单表查询的语法 select * from，这个select * 指的是要查询所有字段的数据。 SELECT distinct 字段1,字段2... FROM 库名.表名 #from后面是说从库的某个表中去找数据，mysql会去找到这个库...
mysql按照表单的条件查询_查询mysql数据库表单和使用函数介绍
weixin_29533307的博客
02-08 164
查询mysql数据库表单和使用函数介绍发布时间：2020-05-13 17:27:21来源：亿速云阅读：207作者：三月栏目：数据库下文主要给大家带来查询mysql数据库表单和使用函数，希望这些内容能够带给大家实际用处，这也是我编辑查询mysql数据库表单和使用函数这篇文章的主要目的。好了，废话不多说，大家直接看下文吧。单表查询准备一张雇员表company.employee雇员编号 emp_id ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值