MySql学习笔记03——DQL（数据查询）基本命令

最新推荐文章于 2024-03-26 18:54:49 发布
最新推荐文章于 2024-03-26 18:54:49 发布
阅读量245 收藏
点赞数
分类专栏： 数据库 文章标签： mysql 学习 笔记
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Maxcu/article/details/132663870
版权

DQL

导入数据

首先使用use database进入数据库中，然后使用命令

source D:\mysql_learning\mysql_learning\document\bjpowernode.sql

注意文件名不能有双引号，命令结尾没有分号。

SQL脚本

.sql文件是SQL脚本文件，它里面的内容都是SQL语句，当调用这个文件的时候就会执行文件里面的所有语句。

批量的执行SQL语句，可以使用sql脚本文件。

在mysql当中怎么执行sql脚本呢？

sourse 脚本路径

你在实际的工作中，第一天到了公司，项目经理会给你一个xxx.sql文件，你执行这个脚本文件，你电脑上的数据库数据就有了！

查看表结构

mysql> show tables;
+------------------------+
| Tables_in_bjpowernnode |
+------------------------+
| dept                   |
| emp                    |
| salgrade               |
+------------------------+

查看表中的数据

select * from 表名;

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 |
+-------+--------+-----------+------+------------+---------+---------+--------+

不看表中的数据，只看表中的结构

desc 表名;

mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO    | int         | NO   | PRI | NULL    |       |  # varchar就是string
| 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    |       |
+----------+-------------+------+-----+---------+-------+

简单查询

简单查询都是和select相关的操作，只要是和select相关的操作都不会对原表中的数据进行修改，只会进行查询操作。

查询一个字段

select 字段名 from 表名;

查询多个字段

select 字段名1，字段名2... from 表名;# 用逗号将多个字段名分隔开

查询所有字段

# 一种方式是
select a,b,c,d.... from 表名;
#另外一种方式是
select * from 表名;

但是第二种方式一般效率较低，不是很推荐。

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 Job,Sal from emp;
+-----------+---------+
| Job       | Sal     |
+-----------+---------+
| CLERK     |  800.00 |
| SALESMAN  | 1600.00 |
| SALESMAN  | 1250.00 |
| MANAGER   | 2975.00 |
| SALESMAN  | 1250.00 |
| MANAGER   | 2850.00 |
| MANAGER   | 2450.00 |
| ANALYST   | 3000.00 |
| PRESIDENT | 5000.00 |
| SALESMAN  | 1500.00 |
| CLERK     | 1100.00 |
| CLERK     |  950.00 |
| ANALYST   | 3000.00 |
| CLERK     | 1300.00 |
+-----------+---------+
14 rows in set (0.00 sec)

给查询的列取别名，按照别名来进行显示

select 字段名 as 别名 from 表名;

mysql> select Job,Sal as salary from emp;
+-----------+---------+
| Job       | salary  |
+-----------+---------+
| CLERK     |  800.00 |
| SALESMAN  | 1600.00 |
| SALESMAN  | 1250.00 |
| MANAGER   | 2975.00 |
| SALESMAN  | 1250.00 |
| MANAGER   | 2850.00 |
| MANAGER   | 2450.00 |
| ANALYST   | 3000.00 |
| PRESIDENT | 5000.00 |
| SALESMAN  | 1500.00 |
| CLERK     | 1100.00 |
| CLERK     |  950.00 |
| ANALYST   | 3000.00 |
| CLERK     | 1300.00 |
+-----------+---------+
14 rows in set (0.00 sec)

注意，这里的取别名只会对显示的列显示别名的效果，不会对原本的表中的列名进行修改。

同时，as关键字是可以被省略的，用空格代替，如果别名中的含有空格，需要用单引号将别名括起来，当然，使用双引号也行，但是oracle中不支持双引号进行这样的操作，因此还是统一使用单引号。

列参进行数学运算

字段是支持直接进行数学运算的，例如：

mysql> select ename, sal * 12 as "year's salary" from emp;
+--------+---------------+
| ename  | year's salary |
+--------+---------------+
| 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.01 sec)

这样久能实现查看到年薪是多少了，同时还取了个别名。

条件查询

查询出符合条件的内容

select 字段名1，字段名2...
    from 表名 
        where 条件;

主要使用的一些条件

  • 等于=
mysql> select ename,job,sal from emp where sal=1250;# 找到工资为1250的人
+--------+----------+---------+
| ename  | job      | sal     |
+--------+----------+---------+
| WARD   | SALESMAN | 1250.00 |
| MARTIN | SALESMAN | 1250.00 |
+--------+----------+---------+
2 rows in set (0.00 sec)

# 当然也支持数学运算
mysql> select ename,job,sal from emp where sal*8=10000;
+--------+----------+---------+
| ename  | job      | sal     |
+--------+----------+---------+
| WARD   | SALESMAN | 1250.00 |
| MARTIN | SALESMAN | 1250.00 |
+--------+----------+---------+
2 rows in set (0.00 sec)

#也是支持查找字符串的
mysql> select ename,job,sal from emp where ename='SMITH';
+-------+-------+--------+
| ename | job   | sal    |
+-------+-------+--------+
| SMITH | CLERK | 800.00 |
+-------+-------+--------+
1 row in set (0.00 sec)
  • 不等于!=或者<>
mysql> select ename,job,sal from emp where sal!=1250;
+--------+-----------+---------+
| ename  | job       | sal     |
+--------+-----------+---------+
| SMITH  | CLERK     |  800.00 |
| ALLEN  | SALESMAN  | 1600.00 |
| JONES  | MANAGER   | 2975.00 |
| BLAKE  | MANAGER   | 2850.00 |
| CLARK  | MANAGER   | 2450.00 |
| SCOTT  | ANALYST   | 3000.00 |
| KING   | PRESIDENT | 5000.00 |
| TURNER | SALESMAN  | 1500.00 |
| ADAMS  | CLERK     | 1100.00 |
| JAMES  | CLERK     |  950.00 |
| FORD   | ANALYST   | 3000.00 |
| MILLER | CLERK     | 1300.00 |
+--------+-----------+---------+
12 rows in set (0.00 sec)
  • 小于<
  • 大于>
  • 大于等于>=
  • 小于等于<=

上面的都是类似的

  • 区间
    • between...and... 严格的要求左小右大，同时between…and…是闭区间，包括两端的值
    • () and ()
mysql> select ename,job,sal from emp where sal>=950 and sal<3000;
+--------+----------+---------+
| ename  | job      | sal     |
+--------+----------+---------+
| ALLEN  | SALESMAN | 1600.00 |
| WARD   | SALESMAN | 1250.00 |
| JONES  | MANAGER  | 2975.00 |
| MARTIN | SALESMAN | 1250.00 |
| BLAKE  | MANAGER  | 2850.00 |
| CLARK  | MANAGER  | 2450.00 |
| TURNER | SALESMAN | 1500.00 |
| ADAMS  | CLERK    | 1100.00 |
| JAMES  | CLERK    |  950.00 |
| MILLER | CLERK    | 1300.00 |
+--------+----------+---------+
10 rows in set (0.01 sec)
  • is null /is not null

null在mysql中表示没有，如果用=或者！=表示查找为值为空的，那样是找不到null元素的

# is not null
mysql> select ename,job,comm from emp where COMM is not null;
+--------+----------+---------+
| ename  | job      | comm    |
+--------+----------+---------+
| ALLEN  | SALESMAN |  300.00 |
| WARD   | SALESMAN |  500.00 |
| MARTIN | SALESMAN | 1400.00 |
| TURNER | SALESMAN |    0.00 |
+--------+----------+---------+
4 rows in set (0.00 sec)

# is null
mysql> select ename,job,comm from emp where COMM is null;
+--------+-----------+------+
| ename  | job       | comm |
+--------+-----------+------+
| SMITH  | CLERK     | NULL |
| JONES  | MANAGER   | NULL |
| BLAKE  | MANAGER   | NULL |
| CLARK  | MANAGER   | NULL |
| SCOTT  | ANALYST   | NULL |
| KING   | PRESIDENT | NULL |
| ADAMS  | CLERK     | NULL |
| JAMES  | CLERK     | NULL |
| FORD   | ANALYST   | NULL |
| MILLER | CLERK     | NULL |
+--------+-----------+------+
10 rows in set (0.00 sec)
  • and/or/not

and表示并且，or表示或者，and和or同时出现的时候会有优先级问题，and的优先级更高，如果想优先使用or，就用小括号括起来，not 就是表示否，一般和is 和 in 一起用 ，is not null / not in

mysql> select ename,job,sal from emp where job='MANAGER' or job='SALESMAN';
+--------+----------+---------+
| ename  | job      | sal     |
+--------+----------+---------+
| ALLEN  | SALESMAN | 1600.00 |
| WARD   | SALESMAN | 1250.00 |
| JONES  | MANAGER  | 2975.00 |
| MARTIN | SALESMAN | 1250.00 |
| BLAKE  | MANAGER  | 2850.00 |
| CLARK  | MANAGER  | 2450.00 |
| TURNER | SALESMAN | 1500.00 |
+--------+----------+---------+
7 rows in set (0.00 sec)
  • in

in就是多个or的集合 ，（not in 不在这个范围中）

mysql> select empno,ename,job from emp where job in('MANAGER', 'SALESMAN');
+-------+--------+----------+
| empno | ename  | job      |
+-------+--------+----------+
|  7499 | ALLEN  | SALESMAN |
|  7521 | WARD   | SALESMAN |
|  7566 | JONES  | MANAGER  |
|  7654 | MARTIN | SALESMAN |
|  7698 | BLAKE  | MANAGER  |
|  7782 | CLARK  | MANAGER  |
|  7844 | TURNER | SALESMAN |
+-------+--------+----------+
7 rows in set (0.00 sec)

mysql> select ename,sal from emp where sal in(800, 5000);# 只会找到sal=500和sal=5000的信息
+-------+---------+
| ename | sal     |
+-------+---------+
| SMITH |  800.00 |
| KING  | 5000.00 |
+-------+---------+
2 rows in set (0.00 sec)

括号里面的值不能理解成区间范围，只能理解成可选项。

  • like % _

模糊查找

%表示若干个字符，_表示一个字符，如果查找的字符串中含有这两个关键字符，需要用转义字符\

例如：查找名字以T结尾的成员信息

mysql> select ename from emp where ename like '%T';
+-------+
| ename |
+-------+
| SCOTT |
+-------+
1 row in set (0.00 sec)

排序

查询所有员工薪资，排序？

按照一个字段排序

select 字段名1，字段名2，...
from 表名
order by 字段名;

mysql> select ename,sal from emp order by sal;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| JAMES  |  950.00 |
| ADAMS  | 1100.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN  | 1600.00 |
| CLARK  | 2450.00 |
| BLAKE  | 2850.00 |
| JONES  | 2975.00 |
| SCOTT  | 3000.00 |
| FORD   | 3000.00 |
| KING   | 5000.00 |
+--------+---------+
14 rows in set (0.00 sec)

通过发现可以知道默认是从上到下，升序排列

指定降序和升序排列

  • 降序
select 字段名1，字段名2，...
from 表名
order by 字段名 desc;
  • 升序
select 字段名1，字段名2，...
from 表名
order by 字段名 asc;

关键字asc和desc分别代表ascend和descend，帮助记忆。

按照多个字段排序

select 字段名1，字段名2，...
from 表名
order by 字段名1 asc/desc,字段名2 asc/desc ...;
# 如果按照字段名1进行排序遇到相等的情况，就按照字段名2进行排序

比如说：

查询员工名字和薪资，要求按照薪资升序，如果薪资一样的话，再按照名字升序排列。

mysql> select
    ->          ename,sal
    ->  from
    ->          emp
    ->  order by
    ->          sal asc, ename asc;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| JAMES  |  950.00 |
| ADAMS  | 1100.00 |
| MARTIN | 1250.00 |
| WARD   | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN  | 1600.00 |
| CLARK  | 2450.00 |
| BLAKE  | 2850.00 |
| JONES  | 2975.00 |
| FORD   | 3000.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
+--------+---------+
14 rows in set (0.00 sec)

mysql> select
    ->          ename,sal
    ->  from
    ->          emp
    ->  order by
    ->          sal asc, ename asc;# sal在前，起主导，只有sal相等的时候，才会考虑启用ename排序。

综合测试：找出工资在1250到3000之间的员工信息，要求按照薪资降序排列。

mysql> select ename,sal
    -> from emp
    -> where sal>=1250 and sal<=3000
    -> order by
    -> sal desc;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SCOTT  | 3000.00 |
| FORD   | 3000.00 |
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
+--------+---------+
10 rows in set (0.01 sec)

注意：

关键字顺序不能变：
        select
            ...
        from
            ...
        where
            ...
        order by
            ... ;

以上语句的执行顺序必须掌握：
第一步：from
第二步：where
第三步：select
第四步：order by（排序总是在最后执行！）

数据处理函数/单行处理函数

也被称为单行处理函数：一个输入对应一个输出。

lower转小写

mysql> select lower(ename) from emp;
# 这里也可以取别名
# select lower(ename) as ename from emp;
+--------------+
| lower(ename) |
+--------------+
| smith        |
| allen        |
| ward         |
| jones        |
| martin       |
| blake        |
| clark        |
| scott        |
| king         |
| turner       |
| adams        |
| james        |
| ford         |
| miller       |
+--------------+
14 rows in set (0.01 sec)

--------------------------------
mysql> select lower(ename) as ename,lower(Job) as job from emp;# 也可以处理多列
+--------+-----------+
| ename  | job       |
+--------+-----------+
| smith  | clerk     |
| allen  | salesman  |
| ward   | salesman  |
| jones  | manager   |
| martin | salesman  |
| blake  | manager   |
| clark  | manager   |
| scott  | analyst   |
| king   | president |
| turner | salesman  |
| adams  | clerk     |
| james  | clerk     |
| ford   | analyst   |
| miller | clerk     |
+--------+-----------+
14 rows in set (0.00 sec)

upper转大写

和小写一个用法

substr提取子串

substr(被截取的字符串，起始下标，截取的长度)

注意，SQL语句中的下标是从1开始的

例如，我们用substr提取出字符串中的首字母来判断首字母是不是‘A’,并且将相关信息提取出来。

mysql> select ename ,job,sal from emp where substr(ename,1,1)='A';
+-------+----------+---------+
| ename | job      | sal     |
+-------+----------+---------+
| ALLEN | SALESMAN | 1600.00 |
| ADAMS | CLERK    | 1100.00 |
+-------+----------+---------+
2 rows in set (0.01 sec)

concat拼接字符串

mysql> select concat(empno,ename,job) from emp;# 这里发现是可以拼接多个字符串的
+-------------------------+
| concat(empno,ename,job) |
+-------------------------+
| 7369SMITHCLERK          |
| 7499ALLENSALESMAN       |
| 7521WARDSALESMAN        |
| 7566JONESMANAGER        |
| 7654MARTINSALESMAN      |
| 7698BLAKEMANAGER        |
| 7782CLARKMANAGER        |
| 7788SCOTTANALYST        |
| 7839KINGPRESIDENT       |
| 7844TURNERSALESMAN      |
| 7876ADAMSCLERK          |
| 7900JAMESCLERK          |
| 7902FORDANALYST         |
| 7934MILLERCLERK         |
+-------------------------+
14 rows in set (0.00 sec)

length取长度

mysql> select length(ename) enamelength from emp;
+-------------+
| enamelength |
+-------------+
|           5 |
|           5 |
|           4 |
|           5 |
|           6 |
|           5 |
|           5 |
|           5 |
|           4 |
|           6 |
|           5 |
|           5 |
|           4 |
|           6 |
+-------------+
14 rows in set (0.00 sec)

trim去空格

也是去除字符串的前后空白

mysql> select * from emp where ename=trim('  KING ');
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+
1 row in set (0.00 sec)

但是

mysql> select * from emp where ename=trim('  K I NG ');
Empty set (0.00 sec)

这样是查询不到的。

round四舍五入

select 'abc' from emp; // select后面直接跟“字面量/字面值”
mysql> select 'abc' from emp;
+-----+
| abc |
+-----+
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
+-----+

mysql> select abc from emp;
        ERROR 1054 (42S22): Unknown column 'abc' in 'field list'
        这样肯定报错，因为会把abc当做一个字段的名字，去emp表中找abc字段去了。

select 1000 as num from emp; // 1000 也是被当做一个字面量/字面值。
        +------+
        | num  |
        +------+
        | 1000 |
        | 1000 |
        | 1000 |
        | 1000 |
        | 1000 |
        | 1000 |
        | 1000 |
        | 1000 |
        | 1000 |
        | 1000 |
        | 1000 |
        | 1000 |
        | 1000 |
        | 1000 |
        +------+

结论：select后面可以跟某个表的字段名（可以等同看做变量名），也可以跟字面量/字面值（数据）。

select round(1245.6735,0) as num from emp;# 将1245.6735保留到整数位
+------+
| num  |
+------+
| 1246 |
| 1246 |
| 1246 |
| 1246 |
| 1246 |
| 1246 |
| 1246 |
| 1246 |
| 1246 |
| 1246 |
| 1246 |
| 1246 |
| 1246 |
| 1246 |
+------+
select round(1245.6735,1) as num from emp;# 保留一位小数
+--------+
| num    |
+--------+
| 1245.7 |
| 1245.7 |
| 1245.7 |
| 1245.7 |
| 1245.7 |
| 1245.7 |
| 1245.7 |
| 1245.7 |
| 1245.7 |
| 1245.7 |
| 1245.7 |
| 1245.7 |
| 1245.7 |
| 1245.7 |
+--------+
依次类推
select round(1245.6735,-1) as num from emp;# -1那么就是保留到十位
+------+
| num  |
+------+
| 1250 |
| 1250 |
| 1250 |
| 1250 |
| 1250 |
| 1250 |
| 1250 |
| 1250 |
| 1250 |
| 1250 |
| 1250 |
| 1250 |
| 1250 |
| 1250 |
+------+

rand生成随机数

mysql> select rand() as random from emp;
+---------------------+
| random              |
+---------------------+
|  0.6779789288323176 |
| 0.03282456009911798 |
| 0.13018604473228199 |
|   0.552456574097701 |
|  0.3717247670253038 |
| 0.20125414356706117 |
|  0.8910964474930395 |
|  0.8517198374976589 |
|  0.5853098757428209 |
|  0.3713898969547729 |
| 0.10101988827904676 |
|  0.3909297812645601 |
|  0.6515892722193052 |
| 0.08515704803649062 |
+---------------------+

可以结合round函数生成整数随机数

mysql> select round(rand()*100,0) as random from emp;# 生成100以内的随机数
+--------+
| random |
+--------+
|     47 |
|     10 |
|      9 |
|     13 |
|     38 |
|     50 |
|     39 |
|     42 |
|     95 |
|     47 |
|     52 |
|     20 |
|     43 |
|     53 |
+--------+

ifnull 处理null值

在SQL语句中规定，所有数据与null进行数学运算之后的结果只能是null

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 ename,sal+comm as salcomm from emp;# 会发现所有sal中的数据加上null都变成了null；
+--------+---------+
| ename  | salcomm |
+--------+---------+
| SMITH  |    NULL |
| ALLEN  | 1900.00 |
| WARD   | 1750.00 |
| JONES  |    NULL |
| MARTIN | 2650.00 |
| BLAKE  |    NULL |
| CLARK  |    NULL |
| SCOTT  |    NULL |
| KING   |    NULL |
| TURNER | 1500.00 |
| ADAMS  |    NULL |
| JAMES  |    NULL |
| FORD   |    NULL |
| MILLER |    NULL |
+--------+---------+

但是有时候我们并不想这么做，我们认为有时候null代表着0，那么这个时候该怎么做呢？

使用ifnull函数ifnull（数据，null被当作哪个值）

mysql> select ename,sal+ifnull(comm,0) as salcomm from emp;# 这里我们将null当作0
+--------+---------+
| ename  | salcomm |
+--------+---------+
| SMITH  |  800.00 |
| ALLEN  | 1900.00 |
| WARD   | 1750.00 |
| JONES  | 2975.00 |
| MARTIN | 2650.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+

case…when…then…when…then…else…end模拟if-else语句

当员工的工作岗位是MANAGER的时候，工资上调10%，当工作岗位是SALESMAN的时候，工资上调50%,其它正常。
（注意：不修改数据库，只是将查询结果显示为工资上调）

case(变量)when(变量满足条件1)then(如果满足条件1后的执行)when(变量满足条件2)then...else...end

select ename,
        job,
        sal as oldsal,
        (case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal
        from emp;
+--------+-----------+---------+---------+
| ename  | job       | oldsal  | newsal  |
+--------+-----------+---------+---------+
| SMITH  | CLERK     |  800.00 |  800.00 |
| ALLEN  | SALESMAN  | 1600.00 | 2400.00 |
| WARD   | SALESMAN  | 1250.00 | 1875.00 |
| JONES  | MANAGER   | 2975.00 | 3272.50 |
| MARTIN | SALESMAN  | 1250.00 | 1875.00 |
| BLAKE  | MANAGER   | 2850.00 | 3135.00 |
| CLARK  | MANAGER   | 2450.00 | 2695.00 |
| SCOTT  | ANALYST   | 3000.00 | 3000.00 |
| KING   | PRESIDENT | 5000.00 | 5000.00 |
| TURNER | SALESMAN  | 1500.00 | 2250.00 |
| ADAMS  | CLERK     | 1100.00 | 1100.00 |
| JAMES  | CLERK     |  950.00 |  950.00 |
| FORD   | ANALYST   | 3000.00 | 3000.00 |
| MILLER | CLERK     | 1300.00 | 1300.00 |
+--------+-----------+---------+---------+
14 rows in set (0.00 sec)

str_to_date

将字符串varchar类型转换成date类型。

date_format

将date类型转换成具有一定格式的varchar字符串类型。

分组函数/多行处理函数

输入多行，输出一行。

  • count 计数
  • sum 求和
  • avg 平均值
  • max 最大值
  • min 最小值

注意：
分组函数在使用的时候必须先进行分组，然后才能用。
如果你没有对数据进行分组，整张表默认为一组。

mysql> select max(sal) from emp;# 求最大值
+----------+
| max(sal) |
+----------+
|  5000.00 |
+----------+
1 row in set (0.01 sec)

mysql> select min(sal) from emp;# 求最小值
+----------+
| min(sal) |
+----------+
|   800.00 |
+----------+
1 row in set (0.01 sec)

mysql> select avg(sal) from emp;# 求平均值
+-------------+
| avg(sal)    |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.00 sec)

mysql> select sum(sal) from emp;# 求总和
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
1 row in set (0.00 sec)

mysql> select count(sal) from emp;# 求个数
+------------+
| count(sal) |
+------------+
|         14 |
+------------+
1 row in set (0.00 sec)

值得注意的是分组函数有几个注意事项

  • 分组函数自动忽略掉null值，我们不需要提前对null值进行处理，例如：
mysql> select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
|           4 |
+-------------+
  • 分组函数中count(*)与count(具体字段)的区别

count(*)会统计表中的总行数。

count(具体字段)会忽略字段中的null。

因为每一行记录不可能都为NULL，一行数据中有一列不为NULL，则这行数据就是有效的。

  • 分组函数不能用在where子句中（分组查询中会解释）
select ename,job,sal from emp where max(sal);
ERROR 1111 (HY000): Invalid use of group function
  • 所有的分组函数可以组合在一起用
mysql> select sum(sal),min(sal),max(sal),avg(sal),count(*) from emp;
+----------+----------+----------+-------------+----------+
| sum(sal) | min(sal) | max(sal) | avg(sal)    | count(*) |
+----------+----------+----------+-------------+----------+
| 29025.00 |   800.00 |  5000.00 | 2073.214286 |       14 |
+----------+----------+----------+-------------+----------+
1 row in set (0.00 sec)

分组查询

在实际的应用中，可能有这样的需求，需要先进行分组，然后对每一组的数据进行操作。

例如我们要计算每个部门的薪资和，每个工作岗位的平均薪资，这都是要进行分组，分组之后才能计算

select
    ...
    from
    ...
    group by
    ...

结合已经学到的关键字，可以知道有代码顺序：

select
...
from 
...
where
...
group by
...
order by
...

上面的执行顺序应该是

  1. from
  2. where
  3. group by
  4. select
  5. order by

结合分组函数的使用注意事项分组函数必须先分组，才能执行，那么where子句中如果有分组函数，但是group by是在where语句执行完之后才会执行的，因此执行where语句中的分组函数，那个时候是还没有完成分组的，于是就会报错。

单个分组

找出每个工作岗位的工资和。

select 
    job,sum(sal)
from
    emp
group by
    job;

+-----------+----------+
| job       | sum(sal) |
+-----------+----------+
| CLERK     |  4150.00 |
| SALESMAN  |  5600.00 |
| MANAGER   |  8275.00 |
| ANALYST   |  6000.00 |
| PRESIDENT |  5000.00 |
+-----------+----------+

那么上面的代码中select后面还能加上别的字段名吗？

答案是不可以的，那样会报错。如果想要找出每个工作岗位的工资最高的人是谁，显示名字啥的，需要结合后面学习到的知识才能运行。

在有的时候，上面说的情况不会报错，但是那样是没有意义的。

重点结论：
在一条select语句当中，如果有group by语句的话，
select后面只能跟：参加分组的字段，以及分组函数。
其它的一律不能跟。

多个分组

找出“每个部门，不同工作岗位”的最高薪资

mysql> select
    ->      deptno,job,max(sal)
    -> from
    ->      emp
    -> group by
    ->      deptno,job# 根据多个选项进行分组
    -> order by
    ->      deptno;
+--------+-----------+----------+
| deptno | job       | max(sal) |
+--------+-----------+----------+
|     10 | CLERK     |  1300.00 |
|     10 | MANAGER   |  2450.00 |
|     10 | PRESIDENT |  5000.00 |
|     20 | ANALYST   |  3000.00 |
|     20 | CLERK     |  1100.00 |
|     20 | MANAGER   |  2975.00 |
|     30 | CLERK     |   950.00 |
|     30 | MANAGER   |  2850.00 |
|     30 | SALESMAN  |  1600.00 |
+--------+-----------+----------+
9 rows in set (0.00 sec)

having语句进行进一步过滤

找出每个部门最高薪资，要求显示最高薪资大于3000的？

首先我们找到每个部门的最高薪资

mysql> select 
            deptno,max(sal) 
        from 
            emp 
        group by 
            deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     20 |  3000.00 |
|     30 |  2850.00 |
|     10 |  5000.00 |
+--------+----------+

然后我们再对最高薪资进行过滤

mysql> select 
            deptno,max(sal) 
        from 
            emp 
        group by 
            deptno 
        having 
            max(sal)>3000;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
+--------+----------+

思考：这样做效率是不是很低？

实际上我们可以先把所有大于3000的人找出来再分组

mysql> select 
            deptno,max(sal) 
        from 
            emp 
        where 
            sal>3000 
        group by 
            deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
+--------+----------+

但是有的时候也会存在where无法过滤的情况

找出每个部门平均薪资，要求显示平均薪资高于2500的。

这个例子中，我们就必须先找出平均薪资了，是无法用where过滤的

mysql> select 
            deptno,avg(sal) 
        from 
            emp 
        group by 
            deptno 
        having 
            avg(sal)>2500;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+

distinct关键字

distinct关键字是用来对查询记录进行重复处理

mysql> select job from emp;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| SALESMAN  |
| MANAGER   |
| SALESMAN  |
| MANAGER   |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
| SALESMAN  |
| CLERK     |
| CLERK     |
| ANALYST   |
| CLERK     |
+-----------+

例如这里查询job字段，会有重复的job出现，这个时候可以用distinct关键字进行去重

mysql> select distinct job from emp;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+

注意：distinct关键字只能出现在字段名之前，不能夹在字段名中间。

mysql> select ename,distinct job from emp;

这样是错误的。

当distinct关键字后面有多个字段名的时候，它会实现联合这多个字段名进行查询结果的去重。

mysql> select deptno,job from emp;
+--------+-----------+
| deptno | job       |
+--------+-----------+
|     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     |
+--------+-----------+

mysql> select distinct deptno,job from emp;
+--------+-----------+
| deptno | job       |
+--------+-----------+
|     20 | CLERK     |
|     30 | SALESMAN  |
|     20 | MANAGER   |
|     30 | MANAGER   |
|     10 | MANAGER   |
|     20 | ANALYST   |
|     10 | PRESIDENT |
|     30 | CLERK     |
|     10 | CLERK     |
+--------+-----------+

统计job的种类数量：

mysql> select count(distinct job) from emp;
+---------------------+
| count(distinct job) |
+---------------------+
|                   5 |
+---------------------+

小结

select 
        ...
    from
        ...
    where
        ...
    group by
        ...
    having
        ...
    order by
        ...

上面是已经学到的整个框架，顺序是不能变的。

执行顺序大概是：

  1. from 从某张表中查询数据
  2. where 先经过where条件筛选出有价值的数据。
  3. group by 对这些有价值的数据进行分组。
  4. having 分组之后可以使用having继续筛选。
  5. select 使用select查询出来。
  6. order by 最后排序输出！

找出每个岗位的平均薪资，要求显示平均薪资大于1500的，除MANAGER岗位之外，要求按照平均薪资降序排。

mysql> select 
            job ,avg(sal) as avgsal
        from 
            emp
        where 
            job!='MANAGER'
        group by 
            job
        having
            avg(sal)>1500
        order by     
            avgsal desc;
+-----------+-------------+
| job       | avgsal      |
+-----------+-------------+
| PRESIDENT | 5000.000000 |
| ANALYST   | 3000.000000 |
+-----------+-------------+

连接查询

从一张表中单独查询，称为单表查询。
emp表和dept表联合起来查询数据，从emp表中取员工名字，从dept表中取部门名字。
这种跨表查询，多张表联合起来查询数据，被称为连接查询。

根据表连接的方式分类：
内连接：
等值连接
非等值连接
自连接

​ 外连接：
​ 左外连接（左连接）
​ 右外连接（右连接）

​ 全连接

笛卡尔积现象

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 dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

当两张表进行无条件的连接查询：

mysql> select ename,dname from emp,dept;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | OPERATIONS |
| SMITH  | SALES      |
| SMITH  | RESEARCH   |
| SMITH  | ACCOUNTING |
| ALLEN  | OPERATIONS |
| ALLEN  | SALES      |
| ALLEN  | RESEARCH   |
| ALLEN  | ACCOUNTING |
| WARD   | OPERATIONS |
| WARD   | SALES      |
| WARD   | RESEARCH   |
| WARD   | ACCOUNTING |
| JONES  | OPERATIONS |
| JONES  | SALES      |
| JONES  | RESEARCH   |
| JONES  | ACCOUNTING |
| MARTIN | OPERATIONS |
| MARTIN | SALES      |
| MARTIN | RESEARCH   |
| MARTIN | ACCOUNTING |
| BLAKE  | OPERATIONS |
| BLAKE  | SALES      |
| BLAKE  | RESEARCH   |
| BLAKE  | ACCOUNTING |
| CLARK  | OPERATIONS |
| CLARK  | SALES      |
| CLARK  | RESEARCH   |
| CLARK  | ACCOUNTING |
| SCOTT  | OPERATIONS |
| SCOTT  | SALES      |
| SCOTT  | RESEARCH   |
| SCOTT  | ACCOUNTING |
| KING   | OPERATIONS |
| KING   | SALES      |
| KING   | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | OPERATIONS |
| TURNER | SALES      |
| TURNER | RESEARCH   |
| TURNER | ACCOUNTING |
| ADAMS  | OPERATIONS |
| ADAMS  | SALES      |
| ADAMS  | RESEARCH   |
| ADAMS  | ACCOUNTING |
| JAMES  | OPERATIONS |
| JAMES  | SALES      |
| JAMES  | RESEARCH   |
| JAMES  | ACCOUNTING |
| FORD   | OPERATIONS |
| FORD   | SALES      |
| FORD   | RESEARCH   |
| FORD   | ACCOUNTING |
| MILLER | OPERATIONS |
| MILLER | SALES      |
| MILLER | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
56 rows in set (0.01 sec)

我们发现，他其实是拿emp表中的每个成员分别和dept中的四个成员连接，最终也就是有了4*14行的数据了，但是这里面有一些数据是没有意义的，因为我们连接两个表进行查询是为了通过两个表具有相同的元素进行关联查询，即通过deptno关联起来两个表，但是结果是具有冗余数据的，这种现象就是笛卡尔积现象。

image-20220716104818495

避免笛卡尔积现象

连接时加条件，满足这个条件的记录被筛选出来！

select
    ename,dname# 它既会去emp中找ename也会去dept中找ename（dname）同理，因此效率并不是很高
    # 提高效率可以这样写：emp.ename,dept.dname
from 
    emp,dept
where 
    emp.deptno=dept.deptno;

+--------+------------+
| 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 |
+--------+------------+

这样就能实现两个表通过deptno产生关联，连接之后再进行查询。

最终查询的结果条数是14条，但是匹配的过程中，匹配的次数减少了吗？
还是56次，只不过进行了四选一。次数没有减少。

注意：通过笛卡尔积现象得出，表的连接次数越多效率越低，尽量避免表的
连接次数。

同时一种书写习惯是，给表取别名

# 表起别名。很重要。效率问题。
    select 
        e.ename,d.dname 
    from 
        emp e, dept d
    where
        e.deptno = d.deptno; //SQL92语法。

内连接与等值连接

条件是等量关系，所以被称为等值连接。

# SQL92语法。
select 
        e.ename,d.dname 
    from 
        emp e, dept d
    where
        e.deptno = d.deptno;

sql92的缺点：结构不清晰，表的连接条件，和后期进一步筛选的条件，都放到了where后面。

# SQL99语法
select 
        e.ename,d.dname
    from
        emp e
    inner join #inner可以省略（带着inner可读性更好！！！一眼就能看出来是内连接）
        dept d
    on
        e.deptno = d.deptno;# 条件是等量关系，所以被称为等值连接。

sql99优点：表连接的条件是独立的，连接之后，如果还需要进一步筛选，再往后继续添加where。

# SQL99语法：
        select 
            ...
        from
            a
        join
            b
        on
            a和b的连接条件
        where
            筛选条件

内连接与非等值连接

条件不是一个等量关系，称为非等值连接。

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

mysql> select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

mysql> select
    ->   e.ename,e.sal,s.grade
    -> from
    ->   emp e
    -> join
    ->   salgrade s
    -> on
    ->   e.sal >= s.losal and e.sal <=s.hisal;# emp表中的薪资在salgrade表中的区间范围之内的
                                                 # 条件不是一个等量关系，称为非等值连接。
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+

内连接与自然连接

案例：查询员工的上级领导，要求显示员工名和对应的领导名？

员工名和领导名的相关信息都是在同一个表中，技巧就是将emp a看作员工表，将emp b看作领导表，然后将这两个表进行等值连接

mysql> select empno,ename,mgr from emp;
+-------+--------+------+
| empno | ename  | mgr  |
+-------+--------+------+
|  7369 | SMITH  | 7902 |
|  7499 | ALLEN  | 7698 |
|  7521 | WARD   | 7698 |
|  7566 | JONES  | 7839 |
|  7654 | MARTIN | 7698 |
|  7698 | BLAKE  | 7839 |
|  7782 | CLARK  | 7839 |
|  7788 | SCOTT  | 7566 |
|  7839 | KING   | NULL |
|  7844 | TURNER | 7698 |
|  7876 | ADAMS  | 7788 |
|  7900 | JAMES  | 7698 |
|  7902 | FORD   | 7566 |
|  7934 | MILLER | 7782 |
+-------+--------+------+

empno是员工编号，mgt是员工对应的领导的编号。

select 
    a.ename as '员工',b.ename as '领导'
from
    emp a
join 
    emp b
on
    a.mgr=b.empno;# 员工的领导编号 = 领导的员工编号
+--------+--------+
| 员工   | 领导   |
+--------+--------+
| SMITH  | FORD   |
| ALLEN  | BLAKE  |
| WARD   | BLAKE  |
| JONES  | KING   |
| MARTIN | BLAKE  |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |
+--------+--------+
13 rows in set (0.00 sec)

由于KING没有领导，因此没有这个人的数据，一共只有13行数据。

技巧：一张表看做两张表。

外连接与左外连接右外连接

内连接所连接查询的表没有主次关系，不能全部都匹配出来（比如上方的那个KING案例，就没有匹配到他的领导）

外连接是具有主次关系的，关键字：right/left

右外连接

select 
    e.ename,d.dname
from
    emp e 
right outer join # outer是可以省略的，带着可读性强。
    dept d
on
    e.deptno = d.deptno;# 完全根据这个条件进行匹配
+--------+------------+
| ename  | dname      |
+--------+------------+
| MILLER | ACCOUNTING |
| KING   | ACCOUNTING |
| CLARK  | ACCOUNTING |
| FORD   | RESEARCH   |
| ADAMS  | RESEARCH   |
| SCOTT  | RESEARCH   |
| JONES  | RESEARCH   |
| SMITH  | RESEARCH   |
| JAMES  | SALES      |
| TURNER | SALES      |
| BLAKE  | SALES      |
| MARTIN | SALES      |
| WARD   | SALES      |
| ALLEN  | SALES      |
| NULL   | OPERATIONS |
+--------+------------+

会发现最底下会有NULL，这是因为d表中有的值在e表中是无法匹配到相关数据的。

right表示在join右边的这张表看作主表，主表中的所有数据都要在另一个表中关于on中的条件进行查询，如果没有找到可以匹配的，就用NULL代替。

左外连接

select 
    e.ename,d.dname
from
    dept d 
left join 
    emp e
on
    e.deptno = d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| MILLER | ACCOUNTING |
| KING   | ACCOUNTING |
| CLARK  | ACCOUNTING |
| FORD   | RESEARCH   |
| ADAMS  | RESEARCH   |
| SCOTT  | RESEARCH   |
| JONES  | RESEARCH   |
| SMITH  | RESEARCH   |
| JAMES  | SALES      |
| TURNER | SALES      |
| BLAKE  | SALES      |
| MARTIN | SALES      |
| WARD   | SALES      |
| ALLEN  | SALES      |
| NULL   | OPERATIONS |
+--------+------------+

在join关键字左边的表作为主表，将主表中的所有数据进行查询

带有right的是右外连接，又叫做右连接。
带有left的是左外连接，又叫做左连接。
任何一个右连接都有左连接的写法。
任何一个左连接都有右连接的写法。

内连接与外连接主要区别在于：

内连接所关联的的表之间是没有主次关系的，当一个表中的数据在另一个表中的没有相关联的，就不会进行查询匹配。

外连接所关联的表之间是有主次关系的，关键字right/left用来标记关键字join旁的哪个表作为主表，主表中的所有数据都必须进行查询，若没有相关联的匹配项就用null代替。

mysql> select
            a.ename as '员工',b.ename as '领导'
     from
              emp a
     left join
              emp b
     on
              a.mgr=b.empno;
+--------+--------+
| 员工   | 领导   |
+--------+--------+
| SMITH  | FORD   |
| ALLEN  | BLAKE  |
| WARD   | BLAKE  |
| JONES  | KING   |
| MARTIN | BLAKE  |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| KING   | NULL   |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |
+--------+--------+

多表连接

语法：
        select 
            ...
        from
            a
        join
            b
        on
            a和b的连接条件
        join
            c
        on
            a和c的连接条件
        right join
            d
        on
            a和d的连接条件

一个SQL语句中内连接和外连接可以混合使用。

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

select
    e.ename,d.dname,e.sal,s.grade
from
    emp e
join
    dept d
on
    e.deptno=d.deptno
join
    salgrade s
on 
    e.sal between s.losal and hisal
order by
    e.sal;
+--------+------------+---------+-------+
| ename  | dname      | sal     | grade |
+--------+------------+---------+-------+
| SMITH  | RESEARCH   |  800.00 |     1 |
| JAMES  | SALES      |  950.00 |     1 |
| ADAMS  | RESEARCH   | 1100.00 |     1 |
| WARD   | SALES      | 1250.00 |     2 |
| MARTIN | SALES      | 1250.00 |     2 |
| MILLER | ACCOUNTING | 1300.00 |     2 |
| TURNER | SALES      | 1500.00 |     3 |
| ALLEN  | SALES      | 1600.00 |     3 |
| CLARK  | ACCOUNTING | 2450.00 |     4 |
| BLAKE  | SALES      | 2850.00 |     4 |
| JONES  | RESEARCH   | 2975.00 |     4 |
| SCOTT  | RESEARCH   | 3000.00 |     4 |
| FORD   | RESEARCH   | 3000.00 |     4 |
| KING   | ACCOUNTING | 5000.00 |     5 |
+--------+------------+---------+-------+
14 rows in set (0.00 sec)

结合外连接，将每个员工的上司也输出

select
    e.ename '员工',d.dname,m.ename '上司',e.sal,s.grade
from
    emp e
join
    dept d
on
    e.deptno=d.deptno
join
    salgrade s
on 
    e.sal between s.losal and hisal
left join
    emp m
on
    e.mgr=m.empno
order by
    e.sal;
+--------+------------+--------+---------+-------+
| 员工    | dname      | 上司   | sal     | grade |
+--------+------------+--------+---------+-------+
| SMITH  | RESEARCH   | FORD   |  800.00 |     1 |
| JAMES  | SALES      | BLAKE  |  950.00 |     1 |
| ADAMS  | RESEARCH   | SCOTT  | 1100.00 |     1 |
| MARTIN | SALES      | BLAKE  | 1250.00 |     2 |
| WARD   | SALES      | BLAKE  | 1250.00 |     2 |
| MILLER | ACCOUNTING | CLARK  | 1300.00 |     2 |
| TURNER | SALES      | BLAKE  | 1500.00 |     3 |
| ALLEN  | SALES      | BLAKE  | 1600.00 |     3 |
| CLARK  | ACCOUNTING | KING   | 2450.00 |     4 |
| BLAKE  | SALES      | KING   | 2850.00 |     4 |
| JONES  | RESEARCH   | KING   | 2975.00 |     4 |
| FORD   | RESEARCH   | JONES  | 3000.00 |     4 |
| SCOTT  | RESEARCH   | JONES  | 3000.00 |     4 |
| KING   | ACCOUNTING | NULL   | 5000.00 |     5 |
+--------+------------+--------+---------+-------+
14 rows in set (0.00 sec)

子查询

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

    select
        ..(select).
    from
        ..(select).
    where
        ..(select).

where子查询

案例：找出比最低工资高的员工姓名和工资？

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

这个句子是错误的，因为分组函数不能出现在where之中。

第一步：查询最低工资是多少

mysql> select min(sal) from emp;
+----------+
| min(sal) |
+----------+
|   800.00 |
+----------+

第二步：找到>800的人

mysql> select ename,sal from emp where sal>800;
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+

第三步：合并

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

执行到where会先去执行子查询，得到最小值，然后执行where中的判断语句。

from子查询

在from后面的子查询所得到的结果可以看作一张临时表。

案例：找出每个岗位的平均工资的薪资等级。

第一步：找到每个岗位的平均薪资（分组查询）

mysql> select job,avg(sal) from emp group by job;
+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| CLERK     | 1037.500000 |
| SALESMAN  | 1400.000000 |
| MANAGER   | 2758.333333 |
| ANALYST   | 3000.000000 |
| PRESIDENT | 5000.000000 |
+-----------+-------------+

第二步：将上述表看作一张临时表，与工资等级表进行连接

select 
    t.job,t.avgsal,s.grade
from 
    (select job,avg(sal) as avgsal from emp group by job) t
join
    salgrade s
on
    t.avgsal between s.losal and s.hisal;
+-----------+-------------+-------+
| job       | avgsal      | grade |
+-----------+-------------+-------+
| CLERK     | 1037.500000 |     1 |
| SALESMAN  | 1400.000000 |     2 |
| MANAGER   | 2758.333333 |     4 |
| ANALYST   | 3000.000000 |     4 |
| PRESIDENT | 5000.000000 |     5 |
+-----------+-------------+-------+

select子查询

mysql> select
          e.ename,e.deptno,(select d.dname from dept d where e.deptno = d.deptno) as dname
         from
          emp e;
+--------+--------+------------+
| ename  | deptno | dname      |
+--------+--------+------------+
| SMITH  |     20 | RESEARCH   |
| ALLEN  |     30 | SALES      |
| WARD   |     30 | SALES      |
| JONES  |     20 | RESEARCH   |
| MARTIN |     30 | SALES      |
| BLAKE  |     30 | SALES      |
| CLARK  |     10 | ACCOUNTING |
| SCOTT  |     20 | RESEARCH   |
| KING   |     10 | ACCOUNTING |
| TURNER |     30 | SALES      |
| ADAMS  |     20 | RESEARCH   |
| JAMES  |     30 | SALES      |
| FORD   |     20 | RESEARCH   |
| MILLER |     10 | ACCOUNTING |
+--------+--------+------------+
14 rows in set (0.00 sec)

union合并查询结果集

select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMAN';
+--------+----------+
| ename  | job      |
+--------+----------+
| JONES  | MANAGER  |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
+--------+----------+

union主要体现在合并表的效率很高，在表连接中，每次连接一次新的表，匹配的次数就会乘以新表的行数

但是使用union可以减少匹配的次数，并且完成表的拼接

a 连接 b 连接 c
a 10条记录
b 10条记录
c 10条记录
匹配次数是：1000=10*10*10

使用union的话是：

a连接b：10*10

union

a连接c：10*10

100次 + 100次 = 200次。

使用union的一写注意事项：

  • 要求合并的两个表的列数相同

  • 要求合并的两个表的列与列之间的数据类型一致

  • 注释：UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

  • union会自动删除重复行，union all 则不会

limit分页

limit startIndex, length

通过limit去除表中的一部分数据，提高用户的的体验

startIndex默认是从0开始的

省缺用法：

limit 5;

取出前5条数据。

select
    ename,sal
from 
    emp
order by
    sal desc
limit 5;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+

这样就找到了前5条

limit 2，3;# 表示从下标2开始，也就是第三条数据开始，取出3条数据，也就是第3，4，5

公式：

每页显示pageSize条记录：
第pageNo页：limit (pageNo - 1) * pageSize , pageSize

Maxcu
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql中+desc用法,数据库desc的用法有哪些用法
weixin_39793638的博客
03-23 8936
计算机程序的学习比较复杂，那么数据库desc的用法的用法你知道吗？下面学习啦小编就跟你们详细介绍下数据库desc的用法的用法，希望对你们有用。数据库中desc的用法1)desc就是用于查询出结果时候对结果进行排序，是降序排序，而asc就是升序。。要用与order by一起用。2)例如select * from student order by id desc; 就是把选出的结果经过“按id从大到小...
mysql desc用法_MySQL学习笔记（不断更新中）
weixin_39769039的博客
12-03 1216
1、数据库show databases; #在Mysql中查数据库： use 数据库名称； #使用一个数据库： create database 数据库名称； #新建一个数据库：2、表show tables; #在数据库中查表 create tables 表名(列名1 数据格式， 列名2 数据格式 ...
【MySQL-关键字】MySQL中的desc什么意思？有哪些作用？
笔记、Bug解决
01-06 4万+
MySQL中的desc关键字的意思和作用
mysql中desc的两种用法---1、查询表结构和降序
睡不醒的每天
07-08 2222
PS：此处desc是describe的缩写，用法： desc 表名/查询语句。手动指定按照薪水由大到小排序（降序关键字desc）手动指定按照薪水由小到大排序（升序关键字 asc）
MySql desc 的三种用法
hjjoe1213123的专栏
04-19 8988
1、第一种用法查看SQL脚本的执行情况，与explain命令效果一致 desc select * from table_name; 2、第二种用法查看表结构 desc table_name; 3、第三种用法对表某列数据进行排序 select * from table_name order by col_name desc; 以上用法你会了吗 ...
Mysql数据表操作
喻师傅的学习笔记
04-21 1799
Mysql数据表操作 前言 在mysql数据库中，数据库最基本的单元是表，所有的数据都存储在数据表中，若要对数据执行添加、查看、修改、删除等操作，首先需要在指定的数据库中准备一张数据表。 1.创建数据表 创建数据表指的是在已存在的数据库中建议新的表，基本语法格式： create [temporary] table [if not exists] 表名 （字段名 字段类型 [字段属性]…） [表选项] []表示可选项 可选项temporary表示临时表，仅在当前对话中可见，并且在会话关闭时自动删除。 “字段
MySQL 使用DQL命令查询数据的实现方法
09-09
在MySQL数据库管理系统中，DQL（Data Query Language）是一种用于查询数据的标准SQL语法。本文将深入探讨如何在MySQL中使用...在实际工作中，结合具体业务需求灵活运用这些知识点，将极大地提升数据查询和分析的效率。
MySQL学习笔记之DQL(六)
01-20
查询语句(DQL)的使用 我们在演示之前先建一个表，如下： CREATE TABLE student02( id INT, NAME VARCHAR(32), age INT, sex VARCHAR(16), address VARCHAR(16), math INT, english INT ); 然后插入一些数据...
MySql学习笔记（详细）
最新发布
05-18
3、DQL语言查询数据 4、Mysql函数 5、事务和索引 6、权限设计 7、JDBC 能学到什么：详细了解MYSQL基础以及使用 阅读建议： 建议配合狂神教学视频使用：...
MySQL学习笔记 MySQL学习笔记
09-28
- DQL（数据查询语言）：包括基础查询、条件查询、排序、分组、函数、连接查询、子查询、分页和联合查询。 - DML（数据操纵语言）：涉及INSERT、UPDATE和DELETE语句，用于添加、修改和删除数据。 - DDL（数据定义...
为什么下载mysql找不到installer文件呀_爬虫实例：玩转mysql（预备篇）
weixin_39524147的博客
11-22 237
考完试的第一篇文章，开心~/1.什么是数据库/数据库是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。数据库是以一定方式储存在一起、能与多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合，可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的数据进行新增、查询、更新、删除...
mysql desc指令_mysql 表格操作指令大全（Show、desc、create、insert into、delete、select、drop、update、alter）...
weixin_32729025的博客
02-17 1310
使用数据库use 数据库名eg:use weiying 2.查看库里所有的表Show tables 3.查看所表的结构 desc 表名desc score 4.建表create table 表名(字段 数据类型,字段 数据类型)CREATE TABLE `weiying`.`employee` (em_id int,em_name varchar(20),em_department v...
【MySQL入门-高级-优化】笔记
qwerasdctds的博客
07-25 109
数据定义语言。
MySQL查看表结构命令和MySQL数据表添加字段（三种方式）
m0_37449634的博客
12-15 2390
创建完数据表之后，经常需要查看表结构（表信息）。在中，可以使用 DESCRIBE 和 SHOW CREATE TABLE 命令来查看数据表的结构。
MySQL基本语法
m0_74296666的博客
04-23 217
s;查看所有数据库名称（记得database为复数）[数据库名称] 创建一个新的数据库[数据库名称] 删除数据库[数据库名称] 修改数据库show tables;查看当前数据库中所有数据表| mysql || sys |->;->;| mysql || sys |相同名字的数据库不可被重复建立，其名字同样不区分大小写。
Mysql数据库——高级SQL语句
一坨小橙子的博客
03-26 2076
Mysql常用语句（Select查询、Distinct去重、Where条件判断、In根据已知条件判断、Between介于两个值之间的数据）、Mysql高级语句（排序（Order by）、且（and）或（or）嵌套（）、分组（Group by）、聚合函数（Count、Sum、Max、Min、Avg）、限制行（Limit）、别名（Alias）、通配符（%、_））
MySQL笔记4：desc命令的两个用法
热门推荐
魏波
05-24 9万+
MySQL中desc命令的两个用法！ 1.desc查看表结构的详细信息 desc table_name； 2.desc降序排列数据 select ename,sal from emp order by sal desc; 手动指定按照薪水由大到小排序（降序关键字desc） select ename,sal from emp orde...
5.数据表基本操作
m2282475145的博客
11-05 683
字段名 数据类型 PRIMARY KEY 【默认值】-> (-> );[CONSTRAINT <约束名>] PRIMARY KEY 【字段名】-> (-> );上述两个例子执行后的结果是一样的，都会在id字段上设置主键约束。
mysql desc_mysql desc(DESCRIBE)命令实例讲解
weixin_34307867的博客
01-18 424
mysql desc命令用于查看表结构，是DESCRIBE命令的简写形式。mysql desc命令语法：desc tablename该命令会显示表哪些信息呢？具体包括：字段名称(Field)字段类型(Type)字段是否为null字段是否为主键(key)字段的默认值(default)Extra实例：mysql> CREATE TABLE employee (-> ID INT(2) ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值