第一章 初始化数据
16:31 2024/4/21 Day1
1,MySQL登录 命令: mysql -u(user)用户名 -p(password);
C:\Users\23769>mysql -uroot -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.36 MySQL Community Server - GPL
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
2,列出当前数据库管理系统中有哪些数据库:show databases;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
3,创建数据库,数据库名称为powernode:create database powernode;
mysql> creat database powernode;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'creat database powernode' at line 1
mysql> create database powernode;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| powernode | #创建成功
| sys |
+--------------------+
5 rows in set (0.00 sec)
4,使用powernode数据:use powernode, 查看当前用的是哪个数据库:select database();
mysql> use powernode
Database changed
5,查看当前数据库中有哪些表: show tables;
mysql> show table;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> show tables;
Empty set (0.00 sec)
6,删除数据库:drop database powernode;
这里未演示,可执行后使用 show databases;进行查看是否删除成功
7,退出数据库mysql: 使用命令:exit();
8,!!!初始化数据的sql脚本,导入脚本,使用source + .sql文件地址
-用source命令执行sql脚本的优点:**可支持大文件**。
- 什么是sql脚本:文件名是.sql,并且该文件中编写了大量的SQL语句,执行sql脚本程序就相当于批量执行SQL语句。
- 创建文件:powernode.sql,把以上SQL语句全部复制到sql脚本文件中。
- 执行SQL脚本文件,初始化数据库
第一步:命令窗口登录mysql
第二步:创建数据库powernode(如果之前已经创建就不需要再创建了):create database powernode;
第三步:使用数据库powernode:use bjpowernode;
第四步:source命令执行sql脚本,注意:source命令后面是sql脚本文件的绝对路径。#两步已经执行了
mysql> source D:\CScode\MySQL\resources\powernode.sql
Query OK, 0 rows affected, 1 warning (0.01 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.01 sec)
Query OK, 0 rows affected, 5 warnings (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
#show tables;出现三张表,表面powernode.sql脚本文件导入成功。
mysql> show tables;
+---------------------+
| Tables_in_powernode |
+---------------------+
| dept |
| emp |
| salgrade |
+---------------------+
3 rows in set (0.00 sec)
9,查看表结构:desc或describe,语法格式:desc或describe +表名
mysql> desc dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int | NO | PRI | NULL | |
| DNAME | varchar(14) | YES | | NULL | |
| LOC | varchar(13) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
- Field是字段名
- Type是这个字段的数据类型
- Null是这个字段是否允许为空
- Key是这个字段是否为主键或外键
- Default是这个字段的默认值
mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO | int | NO | PRI | NULL | |
| ENAME | varchar(10) | YES | | NULL | |
| JOB | varchar(9) | YES | | NULL | |
| MGR | int | YES | | NULL | |
| HIREDATE | date | YES | | NULL | |
| SAL | double(7,2) | YES | | NULL | |
| COMM | double(7,2) | YES | | NULL | |
| DEPTNO | int | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
/*
对以上表结构进行解释说明:
- emp表
- empno:员工编号,int类型(整数),不能为空,主键(主键后期学习约束时会进行说明)
- ename:员工姓名,varchar类型(字符串)
- job:工作岗位,varchar类型
- mgr:上级领导编号,int类型
- hiredate:雇佣日期,date类型(日期类型)
- sal:月薪,double类型(带有浮点的数字)
- comm:补助津贴,double类型
- deptno:部门编号,int类型
- dept表
- deptno:部门编号,int类型,主键
- dname:部门名称,varchar类型
- loc:位置,varchar类型
- salgrade表
- grade:等级,int类型
- losal:最低工资,int类型
- hisal:最高工资,int类型
*/
第二章 查询语言DQL专题
查询是SQL语言的核心,用于表达SQL查询的select查询命令是功能最强也是最为复杂的SQL语句,它的作用就是从数据库中检索数据,并将查询结果返回给用户。 select语句由:select子句(查询内容)、from子句(查询对象)、where子句(查询条件)、order by子句(排序方式)、group by子句(分组方式)等组成。查询语句属于SQL语句中的DQL语句,是所有SQL语句中最为复杂也是最重要的语句,所以必须掌握。接下来我们先从简单查询语句开始学习。
一,单表查询
1,select * from 表名; #查询表的所有信息。
注意:select * 的效率不如select 列名1,列名2,...,列名n from 表名;
- select和from是关键字,不能随便写
- **一条SQL语句必须以“;”结尾**
- **对于SQL语句来说,大小写都可以** (限于MYSQL ,对于Oracle数据库来说 时区分大小写的)
- 字段名和表名属于标识符,按照表的实际情况填写,不知道字段名的,可以使用desc命令查看表结构
mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
mysql> select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)
mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO | int | NO | PRI | NULL | |
| ENAME | varchar(10) | YES | | NULL | |
| JOB | varchar(9) | YES | | NULL | |
| MGR | int | YES | | NULL | |
| HIREDATE | date | YES | | NULL | |
| SAL | double(7,2) | YES | | NULL | |
| COMM | double(7,2) | YES | | NULL | |
| DEPTNO | int | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
2,查一个字段
查询一个字段说的是:一个表有多列,查询其中的一列。语法格式:select 字段名 from 表名;
mysql> select EMPNO from emp;
+-------+
| EMPNO |
+-------+
| 7369 |
| 7499 |
| 7521 |
| 7566 |
| 7654 |
| 7698 |
| 7782 |
| 7788 |
| 7839 |
| 7844 |
| 7876 |
| 7900 |
| 7902 |
| 7934 |
+-------+
14 rows in set (0.00 sec)
mysql> select ENAME from emp;
+--------+
| ENAME |
+--------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| TURNER |
| ADAMS |
| JAMES |
| FORD |
| MILLER |
+--------+
14 rows in set (0.00 sec)
#在mysql命令行客户端中,sql语句没有分号是不会执行的
#假设一个SQL语句在书写过程中出错了,怎么终止这条SQL呢?\c(ctlr + C)
mysql> show tables;
+---------------------+
| Tables_in_powernode |
+---------------------+
| dept |
| emp |
| salgrade |
+---------------------+
3 rows in set (0.00 sec)
mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO | int | NO | PRI | NULL | |
| ENAME | varchar(10) | YES | | NULL | |
| JOB | varchar(9) | YES | | NULL | |
| MGR | int | YES | | NULL | |
| HIREDATE | date | YES | | NULL | |
| SAL | double(7,2) | YES | | NULL | |
| COMM | double(7,2) | YES | | NULL | |
| DEPTNO | int | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
#- [ ] 任务1:查询所有部门名称。
mysql> select job from emp;
+-----------+
| job |
+-----------+
| CLERK |
| SALESMAN |
| SALESMAN |
| MANAGER |
| SALESMAN |
| MANAGER |
| MANAGER |
| ANALYST |
| PRESIDENT |
| SALESMAN |
| CLERK |
| CLERK |
| ANALYST |
| CLERK |
+-----------+
14 rows in set (0.00 sec)
#- [ ] 任务2:查询所有薪资等级。
mysql> desc salgrade;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| GRADE | int | YES | | NULL | |
| LOSAL | int | YES | | NULL | |
| HISAL | int | YES | | NULL | |
+-------+------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select grade from salgrade;
+-------+
| grade |
+-------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+-------+
5 rows in set (0.00 sec)
mysql> select losal from salgrade;
+-------+
| losal |
+-------+
| 700 |
| 1201 |
| 1401 |
| 2001 |
| 3001 |
+-------+
5 rows in set (0.00 sec)
3, 查多个字段
查询多个字段时,在字段名和字段名之间添加“,”即可。
语法格式:select 字段名1,字段名2,字段名3 from 表名;
#案例1:查询员工编号以及员工姓名。
mysql> select ename,empno from emp;
+--------+-------+
| ename | empno |
+--------+-------+
| SMITH | 7369 |
| ALLEN | 7499 |
| WARD | 7521 |
| JONES | 7566 |
| MARTIN | 7654 |
| BLAKE | 7698 |
| CLARK | 7782 |
| SCOTT | 7788 |
| KING | 7839 |
| TURNER | 7844 |
| ADAMS | 7876 |
| JAMES | 7900 |
| FORD | 7902 |
| MILLER | 7934 |
+--------+-------+
14 rows in set (0.00 sec)
# 注:字段的前后顺序无所谓(只是显示结果列的时候顺序变了)
#查询所有字段的可以将每个字段都列出来查询,也可以采用“*”来代表所有字段
采用“*”进行查询存在的缺点:
- select * from dept; 在执行的时候会被解析为 select DEPTNO, DNAME, LOC from dept; 再执行,所以这种效率方面弱一些。
- 采用“*”的可读性较差,通过“*”很难看出都有哪些具体的字段。
mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
4,在进行查询操作的时候,字段是可以参与数学运算的,例如加减乘除等。
#案例2:查询每个员工的年薪(月薪 * 12)
mysql> select ename,sal * 12 from emp;
+--------+----------+
| ename | sal * 12 |
+--------+----------+
| SMITH | 9600.00 |
| ALLEN | 19200.00 |
| WARD | 15000.00 |
| JONES | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE | 34200.00 |
| CLARK | 29400.00 |
| SCOTT | 36000.00 |
| KING | 60000.00 |
| TURNER | 18000.00 |
| ADAMS | 13200.00 |
| JAMES | 11400.00 |
| FORD | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.00 sec)
5,查询时字段可起别名 as的使用
查询结果列名“sal * 12”可读性较差,是否可以给查询结果的列名进行重命名呢? as关键字
mysql> select ename,sal * 12 as yearsal from emp;
+--------+----------+
| ename | yearsal |
+--------+----------+
| SMITH | 9600.00 |
| ALLEN | 19200.00 |
| WARD | 15000.00 |
| JONES | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE | 34200.00 |
| CLARK | 29400.00 |
| SCOTT | 36000.00 |
| KING | 60000.00 |
| TURNER | 18000.00 |
| ADAMS | 13200.00 |
| JAMES | 11400.00 |
| FORD | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.00 sec)
#通过as关键字起别名后,查询结果列显示yearsal,可读性增强。
#- 其实as关键字可以省略,只要使用空格即可
#- 通过以上测试,得知as可以省略,可以使用空格代替as,但如果别名中有空格呢?
答: 将别名使用单引号括起来。
eg:
/*
```sql
select ename, sal * 12 year sal from emp;
```
执行报错了,说语法有问题,这是为什么?分析一下:SQL语句编译器在检查该语句的时候,在year后面遇到了空格,会继续找from关键字,但year后面不是from关键字,所以编译器报错了。怎么解决这个问题?记住:如果别名中有空格的话,可以将这个别名使用双引号或者单引号将其括起来。
```sql
select ename, sal * 12 "year sal" from emp;
select ename, sal * 12 'year sal' from emp;
```
注意: **在mysql中,字符串既可以使用双引号也可以使用单引号,但还是建议使用单引号,因为单引号属于标准SQL。**
*/
#**别名是中文是可以的,但是对于低版本的mysql来说会报错,需要添加双引号或单引号。**我们当前使用的mysql版本是:8.0.24
mysql> select ename,sal * 12 as 年薪 from emp;
+--------+----------+
| ename | 年薪 |
+--------+----------+
| SMITH | 9600.00 |
| ALLEN | 19200.00 |
| WARD | 15000.00 |
| JONES | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE | 34200.00 |
| CLARK | 29400.00 |
| SCOTT | 36000.00 |
| KING | 60000.00 |
| TURNER | 18000.00 |
| ADAMS | 13200.00 |
| JAMES | 11400.00 |
| FORD | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.00 sec)
mysql> select ename,sal * 12 ‘year sal’ from emp;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sal’ from emp' at line 1
mysql> select ename,(sal * 12) ‘year sal’ from emp;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sal’ from emp' at line 1
mysql> select ename,(sal * 12) ‘year sall’ from emp;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sall’ from emp' at line 1
mysql> select ename,(sal * 12) as ‘year sal’ from emp;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sal’ from emp' at line 1
mysql> select ename, sal * 12 as ‘year sal’ from emp;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sal’ from emp' at line 1
mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO | int | NO | PRI | NULL | |
| ENAME | varchar(10) | YES | | NULL | |
| JOB | varchar(9) | YES | | NULL | |
| MGR | int | YES | | NULL | |
| HIREDATE | date | YES | | NULL | |
| SAL | double(7,2) | YES | | NULL | |
| COMM | double(7,2) | YES | | NULL | |
| DEPTNO | int | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
#- [ ] 任务:查询所有员工的信息,要求每个字段名采用中文显示。
mysql> select empno as 员工编号,ename as 姓名,job as 工作岗位,mgr as 上级领导,hiredate as 出生日期,sal as 薪资,comm as 电话,deptno as 地址编号 from emp;
+----------+--------+-----------+----------+------------+---------+---------+----------+
| 员工编号 | 姓名 | 工作岗位 | 上级领导 | 出生日期 | 薪资 | 电话 | 地址编号 |
+----------+--------+-----------+----------+------------+---------+---------+----------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+----------+--------+-----------+----------+------------+---------+---------+----------+
14 rows in set (0.00 sec)
条件查询 (往上是简单查询, 接下来是条件查询)
通常在进行查询操作的时候,都是查询符合某些条件的数据,很少将表中所有数据都取出来。怎么取出表的部分数据?需要在查询语句中添加条件进行数据的过滤。常见的过滤条件如下:
| **条件** | **说明** |
| --- | --- |
| = | 等于 |
| <>或!= | 不等于 |
| >= | 大于等于 |
| <= | 小于等于 |
| > | 大于 |
| < | 小于 |
| between...and... | 等同于 >= and <= |
| is null | 为空 |
| is not null | 不为空 |
| <=> | 安全等于(可读性差,很少使用了)。 |
| and 或 && | 并且 |
| or 或 || | 或者 |
| in | 在指定的值当中 |
| not in | 不在指定的值当中 |
| exists | |
| not exists | |
| like | 模糊查询 |
## 条件查询语法格式
```sql
select
...
from
...
where
过滤条件;
```
过滤条件放在where子句当中,以上语句的执行顺序是:重点哦,执行顺序别搞混
第一步:先执行from
第二步:再通过where条件过滤
第三步:最后执行select,查询并将结果展示到控制台
6,等于 =。 判断等量关系,支持多种数据类型,比如:数字、字符串、日期等。
#案例1:查询员工FORD的岗位及月薪
mysql> select job, sal from emp where ename = 'ford';
+---------+---------+
| job | sal |
+---------+---------+
| ANALYST | 3000.00 |
+---------+---------+
1 row in set (0.00 sec)
mysql> select job, sal from emp where ename = 'FORD';
+---------+---------+
| job | sal |
+---------+---------+
| ANALYST | 3000.00 |
+---------+---------+
1 row in set (0.00 sec)
mysql> select empno, ename from emp where job = 'manager';
+-------+-------+
| empno | ename |
+-------+-------+
| 7566 | JONES |
| 7698 | BLAKE |
| 7782 | CLARK |
+-------+-------+
3 rows in set (0.00 sec)
/*
question:存储在表emp中的员工姓名是FORD,全部大写,如果在查询的时候,写成全部小写会怎样呢?
通过测试发现,即使写成小写ford,也是可以查询到结果的,**不过这里需要注意的是:在Oracle数据库当中是查询不到数据的,Oracle的语法要比MySQL的语法严谨。对于SQL语句本身来说是不区分大小写的,但是对于表中真实存储的数据,大写A和小写a还是不一样的,这一点Oracle做的很好。MySQL的语法更随性。另外在Oracle当中,字符串是必须使用单引号括起来的,但在MySQL当中,字符串可以使用单引号,也可以使用双引号**,如下:
```sql
select
job, sal
from
emp
where
ename = "FORD";
```
*/
7, 不等于 <> 或 !=
判断非等量关系,支持字符串、数字、日期类型等。不等号有两种写法,第一种<>,第二种!=,第二种写法和Java程序中的不等号相同,第一种写法比较诡异,不过也很好理解,比如<>3,表示小于3、大于3,就是不等于3。你get到了吗?
#案例1:查询工资不是3000的员工编号、姓名、薪资
mysql> select empno,ename,sal from emp where sal <> 3000;
+-------+--------+---------+
| empno | ename | sal |
+-------+--------+---------+
| 7369 | SMITH | 800.00 |
| 7499 | ALLEN | 1600.00 |
| 7521 | WARD | 1250.00 |
| 7566 | JONES | 2975.00 |
| 7654 | MARTIN | 1250.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7839 | KING | 5000.00 |
| 7844 | TURNER | 1500.00 |
| 7876 | ADAMS | 1100.00 |
| 7900 | JAMES | 950.00 |
| 7934 | MILLER | 1300.00 |
+-------+--------+---------+
12 rows in set (0.00 sec)
mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO | int | NO | PRI | NULL | |
| ENAME | varchar(10) | YES | | NULL | |
| JOB | varchar(9) | YES | | NULL | |
| MGR | int | YES | | NULL | |
| HIREDATE | date | YES | | NULL | |
| SAL | double(7,2) | YES | | NULL | |
| COMM | double(7,2) | YES | | NULL | |
| DEPTNO | int | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
#- [ ] 任务:查询不在部门编号为10的部门工作的员工信息
mysql> select * from emp where deptno != 10;
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
+-------+--------+----------+------+------------+---------+---------+--------+
11 rows in set (0.00 sec)
8,大于 >
#案例:找出薪资大于3000的员工姓名、薪资
mysql> select ename, sal from emp where sal > 3000;
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
+-------+---------+
1 row in set (0.00 sec)
mysql> select ename, sal from emp where sal <= 3000 and sal <= 1600;
+--------+---------+
| ename | sal |
+--------+---------+
| SMITH | 800.00 |
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| MILLER | 1300.00 |
+--------+---------+
8 rows in set (0.00 sec)
9,大于等于 >=
# 这里更多的知识点是 and 的运用
and表示并且,还有另一种写法:&&
#案例:找出薪资大于等于1600并且小于等于3000的员工姓名、薪资。
mysql> select ename, sal from emp where sal <= 3000 and sal >= 1600;
+-------+---------+
| ename | sal |
+-------+---------+
| ALLEN | 1600.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
+-------+---------+
6 rows in set (0.00 sec)
mysql> desc salgrade;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| GRADE | int | YES | | NULL | |
| LOSAL | int | YES | | NULL | |
| HISAL | int | YES | | NULL | |
+-------+------+------+-----+---------+-------+
3 rows in set (0.00 sec)
#- [ ] 任务:找出工资级别为2~4(包含2和4)的最低工资和最高工资。
mysql> select losal,hisal from salgrade where grade > 1 and grade < 5;
+-------+-------+
| losal | hisal |
+-------+-------+
| 1201 | 1400 |
| 1401 | 2000 |
| 2001 | 3000 |
+-------+-------+
3 rows in set (0.00 sec)
10,or表示或者,还有另一种写法:||
#案例:找出工作岗位是MANAGER和SALESMAN的员工姓名、工作岗位
mysql> select ename,job from emp where job = 'MANAGER' or jpb = 'SALESMAN';
ERROR 1054 (42S22): Unknown column 'jpb' in 'where clause'
mysql> select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)
#写法二: ||
mysql> select ename,job from emp where job = 'MANAGER' || job = 'SALESMAN';
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set, 1 warning (0.00 sec)
#注意:这个题目描述中有这样一句话:MANAGER和SALESMAN,有的同学一看到“和”,就直接使用“and”了,因为“和”对应的英文单词是“and”,如果是这样的话,就大错特错了,因为and表示并且,使用and表示工作岗位既是MANAGER又是SALESMAN的员工,这样的员工是不存在的,因为每一个员工只有一个岗位,不可能同时从事两个岗位。所以使用and是查询不到任何结果的。
11,!!!重点
and和or同时出现时,and优先级较高,会先执行,如果希望or先执行,这个时候需要给or条件添加小括号。另外,以后遇到不确定的优先级时,可以通过添加小括号的方式来解决。对于优先级问题没必要记忆。
#案例:找出薪资小于1500,并且部门编号是20或30的员工姓名、薪资、部门编号。
mysql> select ename, sla, deptno from emp where sal < 1500 and (deptno = 20 or deptno = 30);
ERROR 1054 (42S22): Unknown column 'sla' in 'field list'
mysql> select ename, sal, deptno from emp where sal < 1500 and (deptno = 20 or deptno = 30);
+--------+---------+--------+
| ename | sal | deptno |
+--------+---------+--------+
| SMITH | 800.00 | 20 |
| WARD | 1250.00 | 30 |
| MARTIN | 1250.00 | 30 |
| ADAMS | 1100.00 | 20 |
| JAMES | 950.00 | 30 |
+--------+---------+--------+
5 rows in set (0.00 sec)
/*
先来看一下错误写法:
```sql
select
ename,sal,deptno
from
emp
where
sal < 1500 and deptno = 20 or deptno = 30;
```
![image.png](https://cdn.nlark.com/yuque/0/2021/png/21376908/1621912213872-f2b1fe9e-384c-404e-bf24-d81bd895ae23.png#averageHue=%2311100f&height=388&id=DymCc&originHeight=388&originWidth=524&originalType=binary&ratio=1&rotation=0&showTitle=false&size=26693&status=done&style=shadow&title=&width=524)
认真解读题意得知:薪资小于1500是一个大前提,要找出的是薪资小于1500的,满足这个条件的前提下,再找部门编号是20或30的,显然以上的运行结果中出现了薪资为1600的,为什么1600的会出现呢?这是因为“sal < 1500 and deptno = 20”结合在一起了,“depnto = 30”成了一个独立的条件。会导致部门编号为30的所有员工全部查询出来。我们应该让“deptno = 20 or deptno = 30”结合在一起
*/
#- [ ] 任务:找出薪资小于1500的,并且工作岗位是CLERK和SALESMAN的员工姓名、薪资、岗位。
mysql> select ename, sal, deptno from emp where sal < 1500 and (job = 'CLERK' or job = );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
mysql> select ename, sal, deptno from emp where sal < 1500 and (job = 'CLERK' or job = 'SALESMAN');
+--------+---------+--------+
| ename | sal | deptno |
+--------+---------+--------+
| SMITH | 800.00 | 20 |
| WARD | 1250.00 | 30 |
| MARTIN | 1250.00 | 30 |
| ADAMS | 1100.00 | 20 |
| JAMES | 950.00 | 30 |
| MILLER | 1300.00 | 10 |
+--------+---------+--------+
6 rows in set (0.00 sec)
12, between...and...等同于 >= and <=
做区间判断的,包含左右两个边界值。
它支持数字、日期、字符串等数据类型。
between...and...在使用时一定是**左小右大**。左大右小时无法查询到数据。 !!!需要特别注意的哦
between...and... 和 >= and <=只是在写法结构上有区别,执行原理和效率方面没有区别。
mysql> select ename, sal, deptno from emp where sal < 1500 and job between 'CLERK' and 'SALESMAN';
+--------+---------+--------+
| ename | sal | deptno |
+--------+---------+--------+
| SMITH | 800.00 | 20 |
| WARD | 1250.00 | 30 |
| MARTIN | 1250.00 | 30 |
| ADAMS | 1100.00 | 20 |
| JAMES | 950.00 | 30 |
| MILLER | 1300.00 | 10 |
+--------+---------+--------+
6 rows in set (0.00 sec)
#案例:找出薪资在1600到3000的员工姓名、薪资
mysql> select ename,sal from emp where sal between 1500 and 3000;
+--------+---------+
| ename | sal |
+--------+---------+
| ALLEN | 1600.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| TURNER | 1500.00 |
| FORD | 3000.00 |
+--------+---------+
7 rows in set (0.00 sec)
#若写成 :between 3000 and 1600; 则没有查询到任何数据,所以在使用的时候一定要注意:**左小右大**。
相当于是 sal >= 3000 and sal <= 1600; 当然返回空了
mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
#- [ ] 任务:查询在1982-01-23到1987-04-19之间入职的员工
它支持数字、日期、字符串等数据类型。 注意:以上SQL语句中日期需要加上单引号。
mysql> select ename from emp where HIREDATE between '1981-04-02' and '1981-09-08';
+--------+
| ename |
+--------+
| JONES |
| BLAKE |
| CLARK |
| TURNER |
+--------+
4 rows in set (0.00 sec)
mysql> select ename, HIREDATE from emp where HIREDATE between '1981-04-02' and '1981-09-08';
+--------+------------+
| ename | HIREDATE |
+--------+------------+
| JONES | 1981-04-02 |
| BLAKE | 1981-05-01 |
| CLARK | 1981-06-09 |
| TURNER | 1981-09-08 |
+--------+------------+
4 rows in set (0.00 sec)
13,is null ; is not null
判断某个数据是否为null,不能使用等号,只能使用 is null
判断某个数据是否不为null,不能使用不等号,只能使用 is not null
在数据库中null不是一个值,不能用等号和不等号衡量,null代表什么也没有,没有数据,没有值
14,in ; not in
job in('MANAGER','SALESMAN','CLERK') 等同于 job = 'MANAGER' or job = 'SALESMAN' or job = 'CLERK'
sal in(1600, 3000, 5000) 等同于 sal = 1600 or sal = 3000 or sal = 5000
in后面有一个小括号,小括号当中有多个值,值和值之间采用逗号隔开
sal in(1500, 5000),需要注意的是:这个并不是说薪资在1500到5000之间,in不代表区间,表示sal是1500的和sal是5000的
简单解释就是 in(值1, 值2,... , 值n) : 括号里面的是内容,并不是区间什么的,only内容本身
#案例1:找出工作岗位是MANAGER和SALESMAN的员工姓名、薪资、工作岗位
mysql> select ename,sal,job from emp where job in('MANAGER', 'SALESMAN');
+--------+---------+----------+
| ename | sal | job |
+--------+---------+----------+
| ALLEN | 1600.00 | SALESMAN |
| WARD | 1250.00 | SALESMAN |
| JONES | 2975.00 | MANAGER |
| MARTIN | 1250.00 | SALESMAN |
| BLAKE | 2850.00 | MANAGER |
| CLARK | 2450.00 | MANAGER |
| TURNER | 1500.00 | SALESMAN |
+--------+---------+----------+
7 rows in set (0.00 sec)
#案例2:找出薪资是1500/1600/3000的员工姓名、工作岗位
mysql> select ename,sal,job from emp where sal in(1500, 1600,3000);
+--------+---------+----------+
| ename | sal | job |
+--------+---------+----------+
| ALLEN | 1600.00 | SALESMAN |
| SCOTT | 3000.00 | ANALYST |
| TURNER | 1500.00 | SALESMAN |
| FORD | 3000.00 | ANALYST |
+--------+---------+----------+
4 rows in set (0.00 sec)
#案例2:找出薪资是1500/1600/3000的员工姓名、工作岗位
等价于:
mysql> select ename,sal,job from emp where sal = 1500 or sal = 1600 or sal = 3000;
+--------+---------+----------+
| ename | sal | job |
+--------+---------+----------+
| ALLEN | 1600.00 | SALESMAN |
| SCOTT | 3000.00 | ANALYST |
| TURNER | 1500.00 | SALESMAN |
| FORD | 3000.00 | ANALYST |
+--------+---------+----------+
4 rows in set (0.00 sec)
#案例:找出工作岗位不是MANAGER和SALESMAN的员工姓名、工作岗位
mysql> select ename,sal,job from emp where job not in('MANAGER', 'SALESMAN');
+--------+---------+-----------+
| ename | sal | job |
+--------+---------+-----------+
| SMITH | 800.00 | CLERK |
| SCOTT | 3000.00 | ANALYST |
| KING | 5000.00 | PRESIDENT |
| ADAMS | 1100.00 | CLERK |
| JAMES | 950.00 | CLERK |
| FORD | 3000.00 | ANALYST |
| MILLER | 1300.00 | CLERK |
+--------+---------+-----------+
7 rows in set (0.00 sec)
15, in、not in 与 NULL 重要知识点!!! 自己去搜博客看看吧。
16,模糊查询like
模糊查询又被称为模糊匹配,在实际开发中使用较多,比如:查询公司中所有姓张的,查询岗位中带有经理两个字的职位等等,这些都需要使用模糊查询。
模糊查询的语法格式如下:
```sql
select .. from .. where 字段 like '通配符表达式';
```
!!!在模糊查询中,通配符主要包括两个:一个是%,一个是下划线_。其中%代表任意多个字符。下划线_代表任意一个字符。
#案例1:查询员工名字以'S'开始的员工姓名
mysql> select ename from emp where ename like 'S%';
+-------+
| ename |
+-------+
| SMITH |
| SCOTT |
+-------+
2 rows in set (0.00 sec)
#案例2:查询员工名字以'T'结尾的员工姓名
mysql> select ename from emp where ename like '%T';
+-------+
| ename |
+-------+
| SCOTT |
+-------+
1 row in set (0.00 sec)
#案例3:查询员工名字中含有'O'的员工姓名
mysql> select ename from emp where ename like '%O%';
+-------+
| ename |
+-------+
| JONES |
| SCOTT |
| FORD |
+-------+
3 rows in set (0.00 sec)
#案例4:查询员工名字中第二个字母是'A'的员工姓名
mysql> select ename from emp where ename like '_A%';
+--------+
| ename |
+--------+
| WARD |
| MARTIN |
| JAMES |
+--------+
3 rows in set (0.00 sec)
#案例5:查询学员名字中含有下划线的。因为通配符里面就有 % 和 _ 。现在怎么办: 答在前面加转义字符‘\’。
mysql> show tables;
+---------------------+
| Tables_in_powernode |
+---------------------+
| dept |
| emp |
| salgrade |
+---------------------+
3 rows in set (0.00 sec)
mysql> drop table if exists student;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> create table student(id int, name varchar(255));
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+---------------------+
| Tables_in_powernode |
+---------------------+
| dept |
| emp |
| salgrade |
| student |
+---------------------+
4 rows in set (0.00 sec)
mysql> insert into student(id,name) values(1,'susan');
Query OK, 1 row affected (0.01 sec)
mysql> insert into student(id,name) values(2,'luck');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student(id,name) values(3,'jack');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+------+-------+
| id | name |
+------+-------+
| 1 | susan |
| 2 | luck |
| 3 | jack |
+------+-------+
3 rows in set (0.00 sec)
mysql> insert into student(id,name) values(4,'jack_son');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+------+----------+
| id | name |
+------+----------+
| 1 | susan |
| 2 | luck |
| 3 | jack |
| 4 | jack_son |
+------+----------+
4 rows in set (0.00 sec)
mysql> select name from student where name like '%\_%';
+----------+
| name |
+----------+
| jack_son |
+----------+
1 row in set (0.00 sec)
mysql> select name from student where name like '%_%';
+----------+
| name |
+----------+
| susan |
| luck |
| jack |
| jack_son |
+----------+
4 rows in set (0.00 sec)
#SQL之所以将所有数据全部显示了,因为下划线代表任意单个字符,如果你想让这个下划线变成一个普通的下划线字符,就要使用转义字符了,在mysql当中转义字符是“\”
17,排序操作
排序操作很常用,比如查询学员成绩,按照成绩降序排列。排序的SQL语法:
```sql
select .. from .. order by 字段 asc/desc; #默认为 : asc
```
# 单一字段升序
查询员工的编号、姓名、薪资,按照薪资升序排列。
mysql> select empno,ename,sal from emp order by sal asc;
+-------+--------+---------+
| empno | ename | sal |
+-------+--------+---------+
| 7369 | SMITH | 800.00 |
| 7900 | JAMES | 950.00 |
| 7876 | ADAMS | 1100.00 |
| 7521 | WARD | 1250.00 |
| 7654 | MARTIN | 1250.00 |
| 7934 | MILLER | 1300.00 |
| 7844 | TURNER | 1500.00 |
| 7499 | ALLEN | 1600.00 |
| 7782 | CLARK | 2450.00 |
| 7698 | BLAKE | 2850.00 |
| 7566 | JONES | 2975.00 |
| 7788 | SCOTT | 3000.00 |
| 7902 | FORD | 3000.00 |
| 7839 | KING | 5000.00 |
+-------+--------+---------+
14 rows in set (0.00 sec)
mysql> select empno,ename,sal from emp order by sal; #默认为升序
+-------+--------+---------+
| empno | ename | sal |
+-------+--------+---------+
| 7369 | SMITH | 800.00 |
| 7900 | JAMES | 950.00 |
| 7876 | ADAMS | 1100.00 |
| 7521 | WARD | 1250.00 |
| 7654 | MARTIN | 1250.00 |
| 7934 | MILLER | 1300.00 |
| 7844 | TURNER | 1500.00 |
| 7499 | ALLEN | 1600.00 |
| 7782 | CLARK | 2450.00 |
| 7698 | BLAKE | 2850.00 |
| 7566 | JONES | 2975.00 |
| 7788 | SCOTT | 3000.00 |
| 7902 | FORD | 3000.00 |
| 7839 | KING | 5000.00 |
+-------+--------+---------+
14 rows in set (0.00 sec)
mysql> select empno,ename,sal from emp order by desc;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc' at line 1
mysql> select empno,ename,sal from emp order by sal desc;
+-------+--------+---------+
| empno | ename | sal |
+-------+--------+---------+
| 7839 | KING | 5000.00 |
| 7788 | SCOTT | 3000.00 |
| 7902 | FORD | 3000.00 |
| 7566 | JONES | 2975.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7499 | ALLEN | 1600.00 |
| 7844 | TURNER | 1500.00 |
| 7934 | MILLER | 1300.00 |
| 7521 | WARD | 1250.00 |
| 7654 | MARTIN | 1250.00 |
| 7876 | ADAMS | 1100.00 |
| 7900 | JAMES | 950.00 |
| 7369 | SMITH | 800.00 |
+-------+--------+---------+
14 rows in set (0.00 sec)
# 多个字段排序
查询员工的编号、姓名、薪资,按照薪资降序排列,如果薪资相同的,再按照姓名升序排列。
总结就是说,把优先级高的字段写在前面即可!!!
mysql> select empno,ename,sal from emp order by sal desc ,ename asc;
+-------+--------+---------+
| empno | ename | sal |
+-------+--------+---------+
| 7839 | KING | 5000.00 |
| 7902 | FORD | 3000.00 |
| 7788 | SCOTT | 3000.00 |
| 7566 | JONES | 2975.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7499 | ALLEN | 1600.00 |
| 7844 | TURNER | 1500.00 |
| 7934 | MILLER | 1300.00 |
| 7654 | MARTIN | 1250.00 |
| 7521 | WARD | 1250.00 |
| 7876 | ADAMS | 1100.00 |
| 7900 | JAMES | 950.00 |
| 7369 | SMITH | 800.00 |
+-------+--------+---------+
14 rows in set (0.00 sec)
# 这里是3和2 是代表查询结果表中的列名第次:即 empno(1),ename(2),sal(3)。不推荐这样写
mysql> select empno,ename,sal from emp order by 3 desc ,2 asc;
+-------+--------+---------+
| empno | ename | sal |
+-------+--------+---------+
| 7839 | KING | 5000.00 |
| 7902 | FORD | 3000.00 |
| 7788 | SCOTT | 3000.00 |
| 7566 | JONES | 2975.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7499 | ALLEN | 1600.00 |
| 7844 | TURNER | 1500.00 |
| 7934 | MILLER | 1300.00 |
| 7654 | MARTIN | 1250.00 |
| 7521 | WARD | 1250.00 |
| 7876 | ADAMS | 1100.00 |
| 7900 | JAMES | 950.00 |
| 7369 | SMITH | 800.00 |
+-------+--------+---------+
14 rows in set (0.00 sec)
# where和order by的位置
mysql> select empno,ename,sal from emp where job in('NAMAGER','SALESMAN') order by 3 desc ,2 asc;
+-------+--------+---------+
| empno | ename | sal |
+-------+--------+---------+
| 7499 | ALLEN | 1600.00 |
| 7844 | TURNER | 1500.00 |
| 7654 | MARTIN | 1250.00 |
| 7521 | WARD | 1250.00 |
+-------+--------+---------+
4 rows in set (0.00 sec)
mysql> select empno,ename,sal*12 as 年薪 from emp where job in('NAMAGER','SALESMAN') order by 年薪 desc;
+-------+--------+----------+
| empno | ename | 年薪 |
+-------+--------+----------+
| 7499 | ALLEN | 19200.00 |
| 7844 | TURNER | 18000.00 |
| 7521 | WARD | 15000.00 |
| 7654 | MARTIN | 15000.00 |
+-------+--------+----------+
4 rows in set (0.00 sec)
mysql> select
-> select^C
mysql>
mysql>
mysql>
mysql> select 3
-> from 1
-> where 2
-> order by 4
-> ^C
**通过这个例子主要是想告诉大家:where先执行,order by语句是最后执行的。**
18,distinct去重
mysql> select job from emp;
+-----------+
| job |
+-----------+
| CLERK |
| SALESMAN |
| SALESMAN |
| MANAGER |
| SALESMAN |
| MANAGER |
| MANAGER |
| ANALYST |
| PRESIDENT |
| SALESMAN |
| CLERK |
| CLERK |
| ANALYST |
| CLERK |
+-----------+
14 rows in set (0.00 sec)
#可以看到工作岗位中有重复的记录,如何在显示的时候去除重复记录呢?在字段前添加distinct关键字。
注意:这个去重只是将显示的结果去重,原表数据不会被更改。
mysql> select dinstinct job from emp;
ERROR 1054 (42S22): Unknown column 'dinstinct' in 'field list'
mysql> select distinct job from emp;
+-----------+
| job |
+-----------+
| CLERK |
| SALESMAN |
| MANAGER |
| ANALYST |
| PRESIDENT |
+-----------+
5 rows in set (0.00 sec)
/*
接下来测试一下,在distinct关键字前添加其它字段是否可以?
```sql
select ename, distinct job from emp;
```
分析一下:ename是14条记录,distinct job是5条记录,可以同时显示吗?
![image.png](https://cdn.nlark.com/yuque/0/2022/png/21376908/1668570696423-05844698-00b1-4e9e-aa98-1a53f465cff4.png#averageHue=%23151311&clientId=u005f32df-cdfa-4&from=paste&height=104&id=uc30b7a53&originHeight=104&originWidth=945&originalType=binary&ratio=1&rotation=0&showTitle=false&size=15129&status=done&style=shadow&taskId=u877fbe24-2e82-46b5-85a4-fabccbe2c07&title=&width=945)
报错了,通过测试得知,distinct只能出现在所有字段的最前面。
结论:distinct只能出现在所有字段的最前面
*/**当distinct出现后,后面多个字段一定是联合去重的**, 我们来做两个练习就知道了:
mysql> select distinct job from emp;
+-----------+
| job |
+-----------+
| CLERK |
| SALESMAN |
| MANAGER |
| ANALYST |
| PRESIDENT |
+-----------+
5 rows in set (0.00 sec)
mysql> select distinct deptno from emp;
+--------+
| deptno |
+--------+
| 20 |
| 30 |
| 10 |
+--------+
3 rows in set (0.00 sec)
#练习2:找出公司中不同部门的不同工作岗位。
mysql> select distinct job,deptno from emp;
+-----------+--------+
| job | deptno |
+-----------+--------+
| CLERK | 20 |
| SALESMAN | 30 |
| MANAGER | 20 | # | MANAGER | 20 | 与 | MANAGER | 30 |不一样。 所以distinct是所有字段联合去重的
| MANAGER | 30 |
| MANAGER | 10 |
| ANALYST | 20 |
| PRESIDENT | 10 |
| CLERK | 30 |
| CLERK | 10 |
+-----------+--------+
9 rows in set (0.00 sec)
二,数据处理函数
17:15 2024/4/28
# 三条SQL中前两条中100和'abc'都是常量,最后一条SQL的abc没有添加单引号,它会被当做某个表的字段名,因为没有这个字段所以报错。
一:与字符串相关
1,转大写upper和ucase
# 查询所有员工名字,以大写形式展现
mysql> select upper(ename) from emp;
+--------------+
| upper(ename) |
+--------------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| TURNER |
| ADAMS |
| JAMES |
| FORD |
| MILLER |
+--------------+
14 rows in set (0.00 sec)
mysql> select ucase(ename) from emp;
+--------------+
| ucase(ename) |
+--------------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| TURNER |
| ADAMS |
| JAMES |
| FORD |
| MILLER |
+--------------+
14 rows in set (0.00 sec)
#案列: 查询员工smith的岗位、薪资(假如你不知道数据库表中的人名是大写、小写还是大小写混合)
mysql> select job, sal from emp where ename = SMITH or ename = smith;
ERROR 1054 (42S22): Unknown column 'SMITH' in 'where clause'
mysql> select job, sal from emp where ename = "SMITH" or ename = "smith";
+-------+--------+
| job | sal |
+-------+--------+
| CLERK | 800.00 |
+-------+--------+
1 row in set (0.00 sec)
mysql> select job, sal from emp where upper(ename) = "SMITH";
+-------+--------+
| job | sal |
+-------+--------+
| CLERK | 800.00 |
+-------+--------+
1 row in set (0.00 sec)
2,转小写lower和lcase
#查询员工姓名,以小写形式展现
mysql> select lower(ename) from emp;
+--------------+
| lower(ename) |
+--------------+
| smith |
| allen |
| ward |
| jones |
| martin |
| blake |
| clark |
| scott |
| king |
| turner |
| adams |
| james |
| ford |
| miller |
+--------------+
14 rows in set (0.00 sec)
mysql> select lcase(ename) from emp;
+--------------+
| lcase(ename) |
+--------------+
| smith |
| allen |
| ward |
| jones |
| martin |
| blake |
| clark |
| scott |
| king |
| turner |
| adams |
| james |
| ford |
| miller |
+--------------+
14 rows in set (0.00 sec)
3,截取字符串substr
语法:substr('被截取的字符串', 起始下标, 截取长度)
有两种写法:
第一种:substr('被截取的字符串', 起始下标, 截取长度)
第二种:substr('被截取的字符串', 起始下标),当第三个参数“截取长度”缺失时,截取到字符串末尾
注意:起始下标从1开始,不是从0开始。(1表示从左侧开始的第一个位置,-1表示从右侧开始的第一个位置。)
mysql> select substr("http://www.baidu.com", 1, 10);
+---------------------------------------+
| substr("http://www.baidu.com", 1, 10) |
+---------------------------------------+
| http://www |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> select substr("http://www.baidu.com", 1);
+-----------------------------------+
| substr("http://www.baidu.com", 1) |
+-----------------------------------+
| http://www.baidu.com |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> select substr("http://www.baidu.com", 10);
+------------------------------------+
| substr("http://www.baidu.com", 10) |
+------------------------------------+
| w.baidu.com |
+------------------------------------+
1 row in set (0.00 sec)
mysql> select substr("http://www.baidu.com", -1,10);
+---------------------------------------+
| substr("http://www.baidu.com", -1,10) |
+---------------------------------------+
| m |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> select substr("http://www.baidu.com", -1,9);
+--------------------------------------+
| substr("http://www.baidu.com", -1,9) |
+--------------------------------------+
| m |
+--------------------------------------+
1 row in set (0.00 sec)
mysql> select substr("http://www.baidu.com", -1);
+------------------------------------+
| substr("http://www.baidu.com", -1) |
+------------------------------------+
| m |
+------------------------------------+
1 row in set (0.00 sec)
#案列:找出员工名字中第二个字母是A的
mysql> select ename from emp where substr(ename,2,1) = 'A';
+--------+
| ename |
+--------+
| WARD |
| MARTIN |
| JAMES |
+--------+
3 rows in set (0.00 sec)
4,获取字符串长度length
mysql> select ename, length(ename) from emp;
+--------+---------------+
| ename | length(ename) |
+--------+---------------+
| SMITH | 5 |
| ALLEN | 5 |
| WARD | 4 |
| JONES | 5 |
| MARTIN | 6 |
| BLAKE | 5 |
| CLARK | 5 |
| SCOTT | 5 |
| KING | 4 |
| TURNER | 6 |
| ADAMS | 5 |
| JAMES | 5 |
| FORD | 4 |
| MILLER | 6 |
+--------+---------------+
14 rows in set (0.00 sec)
mysql> select length("我是你爸爸"),length(I am your father);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'your father)' at line 1
mysql> select length("我是你爸爸"),length(I am your father);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'your father)' at line 1
mysql> select length("我是你爸爸"),length("I am your father");
+----------------------+----------------------------+
| length("我是你爸爸") | length("I am your father") |
+----------------------+----------------------------+
| 10 | 16 |
+----------------------+----------------------------+
1 row in set (0.00 sec)
mysql> select "length中,一个汉字的长度是2"; //注意:一个汉字是2个长度。
+-----------------------------+
| length中,一个汉字的长度是2 |
+-----------------------------+
| length中,一个汉字的长度是2 |
+-----------------------------+
1 row in set (0.00 sec)
5,字符串拼接
语法:concat('字符串1', '字符串2', '字符串3'....)
拼接的字符串数量没有限制。
mysql> select concat("http://","baidu",".com");
+----------------------------------+
| concat("http://","baidu",".com") |
+----------------------------------+
| http://baidu.com |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select concat(ename) from emp;
+---------------+
| concat(ename) |
+---------------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| TURNER |
| ADAMS |
| JAMES |
| FORD |
| MILLER |
+---------------+
14 rows in set (0.00 sec)
mysql> select "数据处理函数的f单行处理,so concatonly得到了一次一个输入";
+----------------------------------------------------------+
| 数据处理函数的f单行处理,so concatonly得到了一次一个输入 |
+----------------------------------------------------------+
| 数据处理函数的f单行处理,so concatonly得到了一次一个输入 |
+----------------------------------------------------------+
1 row in set (0.00 sec)
注意:在mysql8之前,双竖线||也是可以完成字符串拼接的。但在mysql8之后,||只作为逻辑运算符,不能再进行字符串拼接了。
```sql
select 'abc' || 'def' || 'xyz';
```
mysql8之后,|| 只作为“或者”运算符,例如:找出工资高于3000或者低于900的员工姓名和薪资:
```sql
select ename, sal from emp where sal > 3000 || sal < 900;
mysql中可以使用+进行字符串的拼接吗?不可以,在mysql中+只作加法运算,在进行加法运算时,会将加号两边的数据尽最大的努力转换成数字再求和,如果无法转换成数字,最终运算结果通通是0
6,去除字符串前后空白trim
mysql> select concat(trim(' abc '), 'def');
+------------------------------------+
| concat(trim(' abc '), 'def') |
+------------------------------------+
| abcdef |
+------------------------------------+
1 row in set (0.00 sec)
#注意:默认是去除前后空白,也可以去除指定的前缀后缀
#去除前置0
mysql> select trim(leading '0' from '000111000');
+------------------------------------+
| trim(leading '0' from '000111000') |
+------------------------------------+
| 111000 |
+------------------------------------+
1 row in set (0.00 sec)
#去除后置0
mysql> select trim(trailing '0' from '000111000');
+-------------------------------------+
| trim(trailing '0' from '000111000') |
+-------------------------------------+
| 000111 |
+-------------------------------------+
1 row in set (0.00 sec)
#前置0和后置0全部去除
mysql> select trim(both '0' from '000111000');
+---------------------------------+
| trim(both '0' from '000111000') |
+---------------------------------+
| 111 |
+---------------------------------+
1 row in set (0.00 sec)
三,与数字相关
四,日期和时间相关函数
五,分组函数
17:37 2024/5/1
这里开始讲分组函数了!!!
分组函数的执行原则:先分组,然后对每一组数据执行分组函数。如果没有分组语句group by的话,整张表的数据自成一组。
分组函数包括五个:
- max:最大值
- min:最小值
- avg:平均值
- sum:求和
- count:计数
mysql> select "数据处理函数 是 单行处理函数(一个输入对应了一个输出)", "分组函数 是 多行处理函数";
-> ;
+--------------------------------------------------------+--------------------------+
| 数据处理函数 是 单行处理函数(一个输入对应了一个输出) | ; |
+--------------------------------------------------------+--------------------------+
| 数据处理函数 是 单行处理函数(一个输入对应了一个输出) | 分组函数 是 多行处理函数 |
+--------------------------------------------------------+--------------------------+
1 row in set (0.00 sec)
注意:这里讲一讲,数据处理函数 和 分组函数的区别
数据处理函数 是 单行处理:有多少个输入就有多少个输出
分组函数 是 多行处理:对各个分组调用分组函数(多个输入一个输出)
1,分组函数简单使用
mysql> select "分组函数", "max , min , avg , ,sum , count";
+----------+--------------------------------+
| 分组函数 | max , min , avg , ,sum , count |
+----------+--------------------------------+
| 分组函数 | max , min , avg , ,sum , count |
+----------+--------------------------------+
1 row in set (0.00 sec)
mysql> select count(*),sum(sal),avg(sal),min(sal),max(sal) from emp;
+----------+----------+-------------+----------+----------+
| count(*) | sum(sal) | avg(sal) | min(sal) | max(sal) |
+----------+----------+-------------+----------+----------+
| 14 | 29025.00 | 2073.214286 | 800.00 | 5000.00 |
+----------+----------+-------------+----------+----------+
1 row in set (0.00 sec)
# 案列:找出员工的最高薪资
mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
| 5000.00 |
+----------+
1 row in set (0.00 sec)
# 案列:找出员工的平均薪资
mysql> select avg(sal) from emp;
+-------------+
| avg(sal) |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.00 sec)
# 案列:统计员工的数量
mysql> select count(ename) from emp;
+--------------+
| count(ename) |
+--------------+
| 14 |
+--------------+
1 row in set (0.00 sec)
2,分组函数 自动忽略 null值。
mysql> select sum(comm) from emp;
+-----------+
| sum(comm) |
+-----------+
| 2200.00 |
+-----------+
1 row in set (0.00 sec)
!!!重点:所有的分组函数都是自动忽略NULL的。
mysql> select "分组函数自动忽略null值";
+------------------------+
| 分组函数自动忽略null值 |
+------------------------+
| 分组函数自动忽略null值 |
+------------------------+
1 row in set (0.00 sec)
```
select count(ename) from emp;
select count(*) from emp;
select count(1) from emp;
```
count(*)和count(1)的效果一样,统计该组中总记录行数。
count(ename)统计的是这个ename字段中不为NULL个数总和。
3,分组函数的执行顺序:
mysql> 执行顺序
-> select ...4
-> from ...1
-> where ...2
-> group by ...3
-> order by ...5
-> ^C
# 分组函数注意事项
**分组函数不能直接使用在where子句当中**
select ename,job from emp where sal > avg(sal); 这个会报错的
原因:分组的行为是在where执行之后才开始的。此时组都还没有分,是不能使用分组函数的。
4,分组查询语句:group by
按照某个字段分组,或者按照某些字段联合分组。注意:group by的执行是在where之后执行。
语法:
group by 字段
group by 字段1,字段2,字段3....
#案列:找出每个岗位的平均薪资
mysql> select avg(sal) from emp group by job;
+-------------+
| avg(sal) |
+-------------+
| 1037.500000 |
| 1400.000000 |
| 2758.333333 |
| 3000.000000 |
| 5000.000000 |
+-------------+
5 rows in set (0.00 sec)
mysql> select avg(sal),job from emp group by job;
+-------------+-----------+
| avg(sal) | job |
+-------------+-----------+
| 1037.500000 | CLERK |
| 1400.000000 | SALESMAN |
| 2758.333333 | MANAGER |
| 3000.000000 | ANALYST |
| 5000.000000 | PRESIDENT |
+-------------+-----------+
5 rows in set (0.00 sec)
5,注意事项:
当select语句中有group by的话,select后面只能跟分组函数或参加分组的字段
mysql> select ename,avg(sal),job from emp group by job;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'powernode.emp.ENAME' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> select "当SQL语句出现group by时,select后边只能跟 分组函数 和 (group by)的那个字段";
+------------------------------------------------------------------------------+
| 当SQL语句出现group by时,select后边只能跟 分组函数 和 (group by)的那个字段 |
+------------------------------------------------------------------------------+
| 当SQL语句出现group by时,select后边只能跟 分组函数 和 (group by)的那个字段 |
+------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO | int | NO | PRI | NULL | |
| ENAME | varchar(10) | YES | | NULL | |
| JOB | varchar(9) | YES | | NULL | |
| MGR | int | YES | | NULL | |
| HIREDATE | date | YES | | NULL | |
| SAL | double(7,2) | YES | | NULL | |
| COMM | double(7,2) | YES | | NULL | |
| DEPTNO | int | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.01 sec)
mysql> select max(sal) from emp group by deptno;
+----------+
| max(sal) |
+----------+
| 3000.00 |
| 2850.00 |
| 5000.00 |
+----------+
3 rows in set (0.00 sec)
#案列:找出每个部门最高工资
mysql> select max(sal) from emp group by deptno,MGR;
+----------+
| max(sal) |
+----------+
| 800.00 |
| 1600.00 |
| 2975.00 |
| 2850.00 |
| 2450.00 |
| 3000.00 |
| 5000.00 |
| 1100.00 |
| 1300.00 |
+----------+
9 rows in set (0.00 sec)
#案列:找出每个部门不同岗位的平均薪资
mysql> select deptno, job,max(sal) from emp group by deptno,job;
+--------+-----------+----------+
| deptno | job | max(sal) |
+--------+-----------+----------+
| 20 | CLERK | 1100.00 |
| 30 | SALESMAN | 1600.00 |
| 20 | MANAGER | 2975.00 |
| 30 | MANAGER | 2850.00 |
| 10 | MANAGER | 2450.00 |
| 20 | ANALYST | 3000.00 |
| 10 | PRESIDENT | 5000.00 |
| 30 | CLERK | 950.00 |
| 10 | CLERK | 1300.00 |
+--------+-----------+----------+
9 rows in set (0.00 sec)
mysql> select deptno, job,avg(sal) from emp group by deptno,job;
+--------+-----------+-------------+
| deptno | job | avg(sal) |
+--------+-----------+-------------+
| 20 | CLERK | 950.000000 |
| 30 | SALESMAN | 1400.000000 |
| 20 | MANAGER | 2975.000000 |
| 30 | MANAGER | 2850.000000 |
| 10 | MANAGER | 2450.000000 |
| 20 | ANALYST | 3000.000000 |
| 10 | PRESIDENT | 5000.000000 |
| 30 | CLERK | 950.000000 |
| 10 | CLERK | 1300.000000 |
+--------+-----------+-------------+
9 rows in set (0.00 sec)
6,having进行过滤
having写在group by的后面,当你对分组之后的数据不满意,可以继续通过having对分组之后的数据进行过滤。
where的过滤是在分组前进行过滤。
使用原则:尽量在where中过滤,实在不行,再使用having。越早过滤效率越高。
#案列:找出除20部分之外,其它部门的平均薪资
mysql> select deptno,avg(sal) from emp group by deptno having deptno <> 20;//不推荐
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+--------+-------------+
2 rows in set (0.00 sec)
mysql> select deptno,avg(sal) from emp where deptno <> 20 group by deptno; //推荐
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+--------+-------------+
2 rows in set (0.00 sec)
mysql> select "优先where 不得已才having", "因为提升效率,没用的数据提前剔除";
+--------------------------+----------------------------------+
| 优先where 不得已才having | 因为提升效率,没用的数据提前剔除 |
+--------------------------+----------------------------------+
| 优先where 不得已才having | 因为提升效率,没用的数据提前剔除 |
+--------------------------+----------------------------------+
1 row in set (0.00 sec)
#案列:查询每个部门平均薪资,找出平均薪资高于2000的 。此时只能使用having了
mysql> select avg(sal) from emp group by deptno having avg(sal) > 2000;
+-------------+
| avg(sal) |
+-------------+
| 2175.000000 |
| 2916.666667 |
+-------------+
2 rows in set (0.00 sec)
mysql> select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 20 | 2175.000000 |
| 10 | 2916.666667 |
+--------+-------------+
2 rows in set (0.00 sec)
mysql> select deptno,avg(sal) as avgsal from emp group by deptno having avg(sal) > 2000;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 20 | 2175.000000 |
| 10 | 2916.666667 |
+--------+-------------+
2 rows in set (0.00 sec)
mysql> select deptno,avg(sal) as avgsal from emp group by deptno having avgsal > 2000;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 20 | 2175.000000 |
| 10 | 2916.666667 |
+--------+-------------+
2 rows in set (0.00 sec)
mysql>
7 组内排序
substring_index函数的使用
group_concat函数的使用
# 总结单表的DQL语句
select ...5
from ...1
where ...2
group by ...3
having ...4
order by ...6
重点掌握一个完整的DQL语句执行顺序。将123456串成一段话。
六,连接查询
七,子查询
1, 什么是子查询
1. select语句中嵌套select语句就叫做子查询。
2. select语句可以嵌套在哪里?
1. where后面、from后面、select后面都是可以的。
mysql> 子查询
-> select语句中嵌套select语句就叫子查询
-> select语句可以嵌套在
-> where后面
-> from后面
-> select后面
-> ^C
2,where后面使用子查询
案例:找出高于平均薪资的员工姓名和薪资。
mysql> select eneme, sal from emp where sal >= avg(sal)
-> BUT 分组函数不能用在where后面
-> ^C
mysql> 第一步找出平均工资^C
mysql> select avg(sal) from emp;
+-------------+
| avg(sal) |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.01 sec)
mysql> 第二步:找出高于平均工资的员工^C
mysql> select ename, sal from emp where sal > 2073.214286;
+-------+---------+
| ename | sal |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+-------+---------+
6 rows in set (0.00 sec)
mysql> select ename, sal from emp where sal > select avg(sal) from emp;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select avg(sal) from emp' at line 1
mysql> select ename, sal from emp where sal > (select avg(sal) from emp);
+-------+---------+
| ename | sal |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+-------+---------+
6 rows in set (0.00 sec)
3,from后面使用子查询
小窍门:from后面的子查询可以看做一张临时表
案例:找出每个部门的平均工资的等级。
mysql> from后面的子查询可以看作一个临时表^C
mysql> select avg(sal) from emp group by job;
+-------------+
| avg(sal) |
+-------------+
| 1037.500000 |
| 1400.000000 |
| 2758.333333 |
| 3000.000000 |
| 5000.000000 |
+-------------+
5 rows in set (0.01 sec)
mysql> select grade from (select avg(sal) from emp group by job) JOIN salgrade ON avg(sal) between los
al and hisal;
ERROR 1248 (42000): Every derived table must have its own alias
mysql> 第一步找出每个部门的平均工资^C
mysql> select deptno, avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+--------+-------------+
3 rows in set (0.00 sec)
mysql> 第二步:将上面的查询结果当作临时表T,T表与salgrade表进行连接查询^C
mysql> select t.*, s.grade from (select deptno, avg(sal) as avgsal from emp group by deptno) t JOIN salgrade s ON avgsal between losal and hisal;
+--------+-------------+-------+
| deptno | avgsal | grade |
+--------+-------------+-------+
| 20 | 2175.000000 | 4 |
| 30 | 1566.666667 | 3 |
| 10 | 2916.666667 | 4 |
+--------+-------------+-------+
3 rows in set (0.01 sec)
mysql> desc dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int | NO | PRI | NULL | |
| DNAME | varchar(14) | YES | | NULL | |
| LOC | varchar(13) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
4,select后面使用子查询
mysql> select ename , (select dnaem from dept where e.deptno = dept.deptno) from emp as e;
ERROR 1054 (42S22): Unknown column 'dnaem' in 'field list'
mysql> select ename , (select dname from dept where e.deptno = dept.deptno) from emp as e;
+--------+-------------------------------------------------------+
| ename | (select dname from dept where e.deptno = dept.deptno) |
+--------+-------------------------------------------------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+-------------------------------------------------------+
14 rows in set (0.00 sec)
mysql> select ename , (select dname from dept where e.deptno = dept.deptno) as dname from emp as e;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)
mysql> select 后面的子查询:查每个员工所在的部门名称^C
5,EXISTS(存在)与 NO EXISTS(不存在)
在 MySQL 数据库中,EXISTS(存在)用于检查子查询的查询结果行数是否大于0。如果子查询的查询结果行数大于0,则 EXISTS 条件为真。(即存在查询结果则是true。)
主要应用场景:
- EXISTS 可以与 SELECT、UPDATE、DELETE 一起使用,用于检查另一个查询是否返回任何行;
- EXISTS 可以用于验证条件子句中的表达式是否存在;
- EXISTS 常用于子查询条件过滤,例如查询有订单的用户等。
mysql> 在 MySQL 数据库中,EXISTS(存在)用于检查子查询的查询结果行数是否大于0。如果子查询的查询结果行 数大于0,则 EXISTS 条件为真。(即存在查询结果则是true。)
-> ^C
mysql> drop table if exists t_customer;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> drop table if exists t_order;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql> create table t_customer(
-> customer_id int,
-> customer_name varchar(32)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> create table t_order(
-> order_id int,
-> order_price decimal(5,1),
-> customer_id int
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> insert into t_customer(customer_id,customer_name) values(1,'zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_customer(customer_id,customer_name) values(2,'lisi');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_customer(customer_id,customer_name) values(3,'wangwu');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into t_order(order_id, order_price, customer_id) values(10, 1000.0, 1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_order(order_id, order_price, customer_id) values(20, 2000.0, 1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_order(order_id, order_price, customer_id) values(30, 3000.0, 2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_order(order_id, order_price, customer_id) values(40, 4000.0, 2);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_customer;
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangwu |
+-------------+---------------+
3 rows in set (0.00 sec)
mysql> select * from t_order;
+----------+-------------+-------------+
| order_id | order_price | customer_id |
+----------+-------------+-------------+
| 10 | 1000.0 | 1 |
| 20 | 2000.0 | 1 |
| 30 | 3000.0 | 2 |
| 40 | 4000.0 | 2 |
+----------+-------------+-------------+
4 rows in set (0.00 sec)
mysql> 现在我们来看一个简单的案例,假设我们要查询先前有过订单的顾客,而订单信息保存在 t_order 表中,顾客信息保存在 t_customer 表中。^C
mysql> select distinct customer_id from t_order;
+-------------+
| customer_id |
+-------------+
| 1 |
| 2 |
+-------------+
2 rows in set (0.00 sec)
mysql> select * from t_customer where customer_id in (select distinct customer_id from t_order);
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 1 | zhangsan |
| 2 | lisi |
+-------------+---------------+
2 rows in set (0.00 sec)
mysql> select c.* from t_customer c JOIN t_order o ON c.customer_id = o.customer_id;
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 1 | zhangsan |
| 1 | zhangsan |
| 2 | lisi |
| 2 | lisi |
+-------------+---------------+
4 rows in set (0.00 sec)
mysql> select distinct c.* from t_customer c JOIN t_order o ON c.customer_id = o.customer_id;
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 1 | zhangsan |
| 2 | lisi |
+-------------+---------------+
2 rows in set (0.00 sec)
mysql> select * from t_customer where exists(子查询)
-> 子查询有个结果,若结果行数不为零。则exists为true, select语句通过。^C
mysql> select * from t_customer c where exists(select * from t_order o where c_customer_id = o.customer_id);
ERROR 1054 (42S22): Unknown column 'c_customer_id' in 'where clause'
mysql> select * from t_customer c where exists(select * from t_order o where c.customer_id = o.custome
r_id);
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 1 | zhangsan |
| 2 | lisi |
+-------------+---------------+
2 rows in set (0.00 sec)
mysql> 上面语句就相当于:对t_customer的每一行数据拿出来,用到子查询中去;再看子查询结果是否为零行。若exists为true 则打印该行数据。^C
mysql>
在这个查询语句中,子查询用于检查是否有订单与每个客户相关联。如果子查询返回至少一行,则表示该顾客已经下过订单,并返回此客户的所有信息,否则该顾客将不被包含在结果中。
以下是这个查询语句的执行过程:
1. 首先查询表 t_customer 中的所有顾客信息(以下简称为顾客表);
2. 对于顾客表中的每一行,都执行一次子查询,子查询查询该顾客有没有订单,如果有,则在结果集中保留该顾客信息;如果没有,则将该顾客排除;
3. 最终返回有订单顾客的所有信息。
除了 EXISTS,也可以使用 NOT EXISTS 条件从 SELECT、UPDATE、DELETE 语句中获取子查询的返回结果。NOT EXISTS 用于检查一个子查询是否返回任何行,如果没有行返回,那么 NOT EXISTS 将返回 true。
例如,我们想要查找所有没有下过订单的顾客,可以使用以下 sql 语句:
select * from t_customer c where not exists(select * from t_order o where o.customer_id=c.customer_id);
在这个查询语句中,如果没有任何与顾客相关联的订单,则 NOT EXISTS 子查询将返回一个空结果集,这时候 WHERE 条件为 true,并将返回所有顾客信息。如果顾客有订单,则 NOT EXISTS 子查询的结果集将不为空,WHERE 条件为 false,则不会返回该顾客的信息。
总之,无论是 EXISTS 还是 NOT EXISTS,都是非常有用的 SQL 工具。可以通过它们来结合子查询来动态过滤查询结果,使 SQL 查询变得更加灵活和高效。
6,in和exists区别
IN 和 EXISTS 都是用于关系型数据库查询的操作符。不同之处在于:
1. IN 操作符是根据指定列表中的值来判断是否满足条件,而 EXISTS 操作符则是根据子查询的结果是否有返回记录集来判断。
2. EXISTS 操作符通常比 IN 操作符更快,尤其是在子查询返回记录数很大的情况下。因为 EXISTS 只需要判断是否存在符合条件的记录,而 IN 操作符需要比对整个列表,因此执行效率相对较低。
3. IN 操作符可同时匹配多个值,而 EXISTS 只能匹配一组条件。
下面是一个简单的示例,用于演示 IN 和 EXISTS 之间的区别。假设我们有两个表 orders 和 products,orders 表中记录了订单信息,products 表中记录了商品信息。现在我们想查询所有“手机”和“平板电脑”这两种商品中,至少有一笔订单销售了 $1000 以上的商品:
使用 IN 操作符:
```sql
SELECT *
FROM products
WHERE product_name IN ('手机', '平板电脑')
AND product_id IN (
SELECT product_id
FROM orders
WHERE order_amount > 1000
);
```
使用 EXISTS 操作符:
```sql
SELECT *
FROM products
WHERE product_name IN ('手机', '平板电脑')
AND EXISTS (
SELECT *
FROM orders
WHERE orders.product_id = products.product_id
AND order_amount > 1000
);
```
总之,IN 和 EXISTS 都是用于条件过滤的操作符,但其实现方式和性能特点都不同,需要根据具体情况进行选择和使用。
mysql>
mysql> 案例:查询工作岗位是MANAGER和SALESMAN的员工。^C
mysql> select ename, job from emp where job in (MANAGER, SALESMAN);
ERROR 1054 (42S22): Unknown column 'MANAGER' in 'where clause'
mysql> select ename, job from emp where job in ("MANAGER", "SALESMAN");
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)
mysql> select ename, job from emp where job = "MANAGER" or job = "SALESMAN";
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)
7,union&union all
不管是union还是union all都可以将两个查询结果集进行合并。
union会对合并之后的查询结果集进行去重操作。
union all是直接将查询结果集合并,不进行去重操作。(union all和union都可以完成的话,优先选择union all,union all因为不需要去重,所以效率高一些。)
案例:查询工作岗位是MANAGER和SALESMAN的员工。
mysql> select ename, job from emp where job = "MANAGER"
-> union all
-> select ename, job from emp where job = "SALESMAN";
+--------+----------+
| ename | job |
+--------+----------+
| JONES | MANAGER |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)
mysql> union / union all 使用时,两个结果表的列数要一样,每列的数据类型要求mysql一般,oracle严格^C
以上案例采用or也可以完成,那or和union all有什么区别?考虑走索引优化之类的选择union all,其它选择or。
8, limit
1. limit作用:查询第几条到第几条的记录。通常是因为表中数据量太大,需要分页显示。
2. limit语法格式:
1. limit 开始下标, 长度
2,limit 长度(默认下标为0)
mysql>
mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
mysql> select * from emp limit 1,3;
+-------+-------+----------+------+------------+---------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+----------+------+------------+---------+--------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
+-------+-------+----------+------+------------+---------+--------+--------+
3 rows in set (0.00 sec)
mysql> select * from emp limit 3;
+-------+-------+----------+------+------------+---------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+----------+------+------------+---------+--------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
+-------+-------+----------+------+------------+---------+--------+--------+
3 rows in set (0.00 sec)
mysql> select * from emp limit 5,5;
+-------+--------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+------+--------+
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
+-------+--------+-----------+------+------------+---------+------+--------+
5 rows in set (0.00 sec)
八,DQL练习题
mysql> use powernode
Database changed
mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO | int | NO | PRI | NULL | |
| ENAME | varchar(10) | YES | | NULL | |
| JOB | varchar(9) | YES | | NULL | |
| MGR | int | YES | | NULL | |
| HIREDATE | date | YES | | NULL | |
| SAL | double(7,2) | YES | | NULL | |
| COMM | double(7,2) | YES | | NULL | |
| DEPTNO | int | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
1,取得每个部门最高薪水的人员名称
mysql> 1.1 找出每个部门的最高薪水^C
mysql> select deptno,max(sal) from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 20 | 3000.00 |
| 30 | 2850.00 |
| 10 | 5000.00 |
+--------+----------+
3 rows in set (0.00 sec)
mysql> 1.2将上表就当作临时表t 与 emp表作连接查询^C
mysql> select t.* , e.ename from (select deptno,max(sal) from emp group by deptno) t JOIN emp
e ON e.deptno = t.deptno and e.sal = t.max(sal);
ERROR 1630 (42000): FUNCTION t.max does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual
mysql> select t.* , e.ename from (select deptno,max(sal) as maxsal from emp group by deptno) t
JOIN emp e ON e.deptno = t.deptno and e.sal = t.maxsal;
+--------+---------+-------+
| deptno | maxsal | ename |
+--------+---------+-------+
| 30 | 2850.00 | BLAKE |
| 20 | 3000.00 | SCOTT |
| 10 | 5000.00 | KING |
| 20 | 3000.00 | FORD |
+--------+---------+-------+
4 rows in set (0.00 sec)
mysql> 思考 为什么不能直接:select deptno,max(sal), ename from emp group by deptno
-> 因为使用 group by时,select语句使用是分组函数 和 group by对象
-> ^C
2,那些人的薪水在平均薪水之上
mysql> select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+--------+-------------+
3 rows in set (0.00 sec)
mysql> select t.* , e.ename from (select deptno, avg(sal) as avgsal from emp group by deptno) t JOIN emp e ON t.deptno = e.deptno and e.sal >= avgsal;
+--------+-------------+-------+
| deptno | avgsal | ename |
+--------+-------------+-------+
| 30 | 1566.666667 | ALLEN |
| 20 | 2175.000000 | JONES |
| 30 | 1566.666667 | BLAKE |
| 20 | 2175.000000 | SCOTT |
| 10 | 2916.666667 | KING |
| 20 | 2175.000000 | FORD |
+--------+-------------+-------+
6 rows in set (0.00 sec)
mysql> select t.* , e.ename, e.sal from (select deptno, avg(sal) as avgsal from emp group by d
eptno) t JOIN emp e ON t.deptno = e.deptno and e.sal >= avgsal;
+--------+-------------+-------+---------+
| deptno | avgsal | ename | sal |
+--------+-------------+-------+---------+
| 30 | 1566.666667 | ALLEN | 1600.00 |
| 20 | 2175.000000 | JONES | 2975.00 |
| 30 | 1566.666667 | BLAKE | 2850.00 |
| 20 | 2175.000000 | SCOTT | 3000.00 |
| 10 | 2916.666667 | KING | 5000.00 |
| 20 | 2175.000000 | FORD | 3000.00 |
+--------+-------------+-------+---------+
6 rows in set (0.00 sec)
3,取得每个部门平均薪水的等级
mysql> select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+--------+-------------+
3 rows in set (0.00 sec)
mysql> desc salgrade;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| GRADE | int | YES | | NULL | |
| LOSAL | int | YES | | NULL | |
| HISAL | int | YES | | NULL | |
+-------+------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select t.*, s.grade from (select deptno,avg(sal) as avgsal from emp group by deptno) t
JOIN salgrade s ON avgsal between losal and hisal;
+--------+-------------+-------+
| deptno | avgsal | grade |
+--------+-------------+-------+
| 20 | 2175.000000 | 4 |
| 30 | 1566.666667 | 3 |
| 10 | 2916.666667 | 4 |
+--------+-------------+-------+
3 rows in set (0.00 sec)