MySql | 数据查询语句(DQL)

最新推荐文章于 2024-09-12 01:15:37 发布
最新推荐文章于 2024-09-12 01:15:37 发布
阅读量1.4k 收藏
点赞数 1
分类专栏： MySQL 文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_51982545/article/details/120196570
版权

文章目录

简单查询

查询单个字段

select <字段名> from <表名>

  • 查询部门名

    select dname from dept;

    mysql> select dname from dept;
+------------+
| dname      |
+------------+
| ACCOUNTING |
| RESEARCH   |
| SALES      |
| OPERATIONS |
+------------+
4 rows in set (0.00 sec)

查询多个字段

select <字段名1>, <字段名2>, ..., <字段名n> from <表名>
// 输入的字段顺序就是之后展示的顺序

  • 查询部门编号和部门名

    select deptno, dname from dept;
select dname, deptno from dept;

    mysql> select deptno, dname from dept;
+--------+------------+
| deptno | dname      |
+--------+------------+
|     10 | ACCOUNTING |
|     20 | RESEARCH   |
|     30 | SALES      |
|     40 | OPERATIONS |
+--------+------------+
4 rows in set (0.00 sec)

mysql> select dname, deptno from dept;
+------------+--------+
| dname      | deptno |
+------------+--------+
| ACCOUNTING |     10 |
| RESEARCH   |     20 |
| SALES      |     30 |
| OPERATIONS |     40 |
+------------+--------+
4 rows in set (0.00 sec)

查询所有字段

select <字段名1>, <字段名2>, ..., <字段名n> from <表名>	// 输入所有的字段名
select * from <表名>		// 用*代替所有
// 后者会先将*解读为所有具体的字段再运行，效率更低；同时可读性更差，不推荐在实际开发中使用
// 但自己想要快速查看所有数据时可以使用

  • 查询dept表中所有数据

    select deptno, dname, loc from dept;	// 输入所有的字段名
select * from dept;			// 用*代替所有

    mysql> select deptno, dname, loc 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 * 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)

字段起别名

// 用as关键字
select <字段名> as <字段名别名> from <表名>
select <字段名1> as <字段1别名>, <字段名2> as <字段2别名>, ..., <字段名n> as <字段n别名> from <表名>

// as关键字也可以省略
select <字段名> <字段名别名> from <表名>
select <字段名1> <字段1别名>, <字段名2> <字段2别名>, ..., <字段名n> <字段n别名> from <表名>

  • 查询部门编号和部门名，并给部门名字段起别名

    注意：只是将显示的查询结果列名显示为deptname，原表列名还是叫：dname

    记住：select语句是永远都不会进行修改操作的。（因为只负责查询）

    select deptno, dname as deptname from dept;

    mysql> select deptno, dname as deptname from dept;
+--------+------------+
| deptno | deptname   |
+--------+------------+
|     10 | ACCOUNTING |
|     20 | RESEARCH   |
|     30 | SALES      |
|     40 | OPERATIONS |
+--------+------------+
4 rows in set (0.00 sec)

  • 起的别名可以是中文

    select deptno 部门编号, dname 部门名 from dept;

    mysql> select deptno 部门编号, dname 部门名 from dept;
+--------------+------------+
| 部门编号     | 部门名     |
+--------------+------------+
|           10 | ACCOUNTING |
|           20 | RESEARCH   |
|           30 | SALES      |
|           40 | OPERATIONS |
+--------------+------------+
4 rows in set (0.00 sec)

  • 起的别名中包含空格，需要用单引号括起来

    也可以使用双引号，但在SQL语言中，单引号表示字符串是标准，双引号是不标准的

    select deptno, dname 'dept name' from dept;

    mysql> select deptno, dname 'dept name' from dept;
+--------+------------+
| deptno | dept name  |
+--------+------------+
|     10 | ACCOUNTING |
|     20 | RESEARCH   |
|     30 | SALES      |
|     40 | OPERATIONS |
+--------+------------+
4 rows in set (0.00 sec)

字段参与数学运算

select <字段名的数学运算式> from <表名>
select <字段名1的数学运算式>, <字段名2的数学运算式>, ..., <字段名n的数学运算式> from <表名>

  • 计算员工的年薪

    select ename, sal*12 from emp;
select ename, sal*12 yearsal from emp;

    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)

mysql> select ename, sal*12 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)

条件查询

条件查询语法格式

select ... from ... where <条件表达式>

=：等于

  • 查询薪资等于800的员工信息

    select empno, ename from emp where sal=800;

    mysql> select empno, ename from emp where sal=800;
+-------+-------+
| empno | ename |
+-------+-------+
|  7369 | SMITH |
+-------+-------+
1 row in set (0.00 sec)

  • 查询史密斯的员工信息

    select empno, ename from emp where ename='smith';

    mysql> select empno, ename from emp where ename='smith';	// 字符串加单引号
+-------+-------+
| empno | ename |
+-------+-------+
|  7369 | SMITH |
+-------+-------+
1 row in set (0.00 sec)

<>或!=：不等于

  • 查询薪资不等于800的员工信息

    select empno, ename from emp where sal!=800;
select empno, ename from emp where sal<>800;

    mysql> select empno, ename from emp where sal!=800;
+-------+--------+
| empno | ename  |
+-------+--------+
|  7499 | ALLEN  |
|  7521 | WARD   |
|  7566 | JONES  |
|  7654 | MARTIN |
|  7698 | BLAKE  |
|  7782 | CLARK  |
|  7788 | SCOTT  |
|  7839 | KING   |
|  7844 | TURNER |
|  7876 | ADAMS  |
|  7900 | JAMES  |
|  7902 | FORD   |
|  7934 | MILLER |
+-------+--------+
13 rows in set (0.00 sec)

mysql> select empno, ename from emp where sal<>800;
+-------+--------+
| empno | ename  |
+-------+--------+
|  7499 | ALLEN  |
|  7521 | WARD   |
|  7566 | JONES  |
|  7654 | MARTIN |
|  7698 | BLAKE  |
|  7782 | CLARK  |
|  7788 | SCOTT  |
|  7839 | KING   |
|  7844 | TURNER |
|  7876 | ADAMS  |
|  7900 | JAMES  |
|  7902 | FORD   |
|  7934 | MILLER |
+-------+--------+
13 rows in set (0.00 sec)

<：小于

  • 查询薪资小于2000的员工信息

    select empno, ename from emp where sal<2000;

    mysql> select empno, ename from emp where sal<2000;
+-------+--------+
| empno | ename  |
+-------+--------+
|  7369 | SMITH  |
|  7499 | ALLEN  |
|  7521 | WARD   |
|  7654 | MARTIN |
|  7844 | TURNER |
|  7876 | ADAMS  |
|  7900 | JAMES  |
|  7934 | MILLER |
+-------+--------+
8 rows in set (0.00 sec)

<=：小于等于

  • 查询薪资小于等于3000的员工信息

    select empno, ename from emp where sal<=3000;

    mysql> select empno, ename from emp where sal<=3000;
+-------+--------+
| empno | ename  |
+-------+--------+
|  7369 | SMITH  |
|  7499 | ALLEN  |
|  7521 | WARD   |
|  7566 | JONES  |
|  7654 | MARTIN |
|  7698 | BLAKE  |
|  7782 | CLARK  |
|  7788 | SCOTT  |
|  7844 | TURNER |
|  7876 | ADAMS  |
|  7900 | JAMES  |
|  7902 | FORD   |
|  7934 | MILLER |
+-------+--------+
13 rows in set (0.00 sec)

>：大于

  • 查询薪资大于3000的员工信息

    select empno, ename from emp where sal>3000;

    mysql> select empno, ename from emp where sal>3000;
+-------+-------+
| empno | ename |
+-------+-------+
|  7839 | KING  |
+-------+-------+
1 row in set (0.00 sec)

>=：大于等于

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

    select empno, ename from emp where sal>=3000;

    mysql> select empno, ename from emp where sal>=3000;
+-------+-------+
| empno | ename |
+-------+-------+
|  7788 | SCOTT |
|  7839 | KING  |
|  7902 | FORD  |
+-------+-------+
3 rows in set (0.00 sec)

between and或>= and <=：两个值之间

between and 左小右大，且是闭区间

  • 查询薪资在2450到3000之间的员工信息

    select empno, ename from emp where sal>=2450 and sal<=3000;

    mysql> select empno, ename from emp where sal>=2450 and sal<=3000;
+-------+-------+
| empno | ename |
+-------+-------+
|  7566 | JONES |
|  7698 | BLAKE |
|  7782 | CLARK |
|  7788 | SCOTT |
|  7902 | FORD  |
+-------+-------+
5 rows in set (0.00 sec)

mysql> select empno, ename from emp where sal between 2450 and 3000;
+-------+-------+
| empno | ename |
+-------+-------+
|  7566 | JONES |
|  7698 | BLAKE |
|  7782 | CLARK |
|  7788 | SCOTT |
|  7902 | FORD  |
+-------+-------+
5 rows in set (0.00 sec)

is null：为空，is not null：不为空

注意：在数据库当中nul1不能使用等号进行衡量。需要使用is null因为数据库中的nul1代表什么也没有，它不是一个值，所以不能使用等号衡量。

  • 查询哪些员工的津贴/补助为空

    select empno, ename from emp where comm is null;

    mysql> select empno, ename from emp where comm is null;
+-------+--------+
| empno | ename  |
+-------+--------+
|  7369 | SMITH  |
|  7566 | JONES  |
|  7698 | BLAKE  |
|  7782 | CLARK  |
|  7788 | SCOTT  |
|  7839 | KING   |
|  7876 | ADAMS  |
|  7900 | JAMES  |
|  7902 | FORD   |
|  7934 | MILLER |
+-------+--------+
10 rows in set (0.00 sec)

  • 查询哪些员工的津贴/补助不为空

    select empno, ename from emp where comm is not null;

    mysql> select empno, ename from emp where comm is not null;
+-------+--------+
| empno | ename  |
+-------+--------+
|  7499 | ALLEN  |
|  7521 | WARD   |
|  7654 | MARTIN |
|  7844 | TURNER |
+-------+--------+
4 rows in set (0.00 sec)

and：且

  • 查询工作岗位是MANAGER并且工资大于2500的员工信息

    select empno, ename from emp where job='manager' and sal>2500;

    mysql> select empno, ename from emp where job='manager' and sal>2500;
+-------+-------+
| empno | ename |
+-------+-------+
|  7566 | JONES |
|  7698 | BLAKE |
+-------+-------+
2 rows in set (0.00 sec)

or：或

  • 查询工作岗位是MANAGER和SALESMAN的员工

    select empno, ename from emp where job='manager' or job='salesman';

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

and和or的优先级问题

and和or同时出现，and的优先级较高

  • 查询工资大于2500，并且部门编号为10或20部门的员工

    select empno, ename from emp where sal>2500 and (deptno=10 or deptno=20);

    mysql> select empno, ename from emp where sal>2500 and (deptno=10 or deptno=20);
+-------+-------+
| empno | ename |
+-------+-------+
|  7566 | JONES |
|  7788 | SCOTT |
|  7839 | KING  |
|  7902 | FORD  |
+-------+-------+
4 rows in set (0.00 sec)

in：在范围中，not in：不在范围中

in可以等价于多个or，注意in不是区间，in括号中是具体的值的罗列

  • 查询工作岗位是MANAGER和SALESMAN的员工信息

    select empno, ename from emp where job in('manager','salesman');

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

  • 查询工资不是800,3000,5000的员工信息

    select empno, ename from emp where sal not in(800, 3000, 5000);

    mysql> select empno, ename from emp where sal not in(800, 3000, 5000);
+-------+--------+
| empno | ename  |
+-------+--------+
|  7499 | ALLEN  |
|  7521 | WARD   |
|  7566 | JONES  |
|  7654 | MARTIN |
|  7698 | BLAKE  |
|  7782 | CLARK  |
|  7844 | TURNER |
|  7876 | ADAMS  |
|  7900 | JAMES  |
|  7934 | MILLER |
+-------+--------+
10 rows in set (0.00 sec)

not：取非

主要用在is和in中，not is、not in

like：模糊查询

%百分号匹配任意多个字符，_下划线匹配任意单个字符（加反斜杠转义为普通字符）

  • 查询名字中含有o的员工信息

    select empno, ename from emp where ename like '%o%';

    mysql> select empno, ename from emp where ename like '%o%';
+-------+-------+
| empno | ename |
+-------+-------+
|  7566 | JONES |
|  7788 | SCOTT |
|  7902 | FORD  |
+-------+-------+
3 rows in set (0.00 sec)

  • 查询名字中以t结尾的员工信息

    select empno, ename from emp where ename like '%t';

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

  • 查询名字中以k开头的员工信息

    select empno, ename from emp where ename like 'k%';

    mysql> select empno, ename from emp where ename like 'k%';
+-------+-------+
| empno | ename |
+-------+-------+
|  7839 | KING  |
+-------+-------+
1 row in set (0.00 sec)

  • 查询名字中第二个字母是a的员工信息

    select empno, ename from emp where ename like '_a%';

    mysql> select empno, ename from emp where ename like '_a%';
+-------+--------+
| empno | ename  |
+-------+--------+
|  7521 | WARD   |
|  7654 | MARTIN |
|  7900 | JAMES  |
+-------+--------+
3 rows in set (0.00 sec)

  • 查询名字中第三个字母是r的员工信息

    select empno, ename from emp where ename like '__r%';

    mysql> select empno, ename from emp where ename like '__r%';
+-------+--------+
| empno | ename  |
+-------+--------+
|  7521 | WARD   |
|  7654 | MARTIN |
|  7844 | TURNER |
|  7902 | FORD   |
+-------+--------+
4 rows in set (0.00 sec)

排序

单个字段排序

select ... from ... where ... order by <字段名> (asc/desc)	
// 默认升序，加asc指定升序，加desc指定降序

  • 查询所有员工名字和薪资，并按薪资排序

    select ename, sal from emp order by sal;

    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)

mysql> select ename, sal from emp order by sal desc;
+--------+---------+
| ename  | sal     |
+--------+---------+
| KING   | 5000.00 |
| 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 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| SMITH  |  800.00 |
+--------+---------+
14 rows in set (0.00 sec)

多个字段排序

select ... from ... where ... order by <字段名1> (asc/desc), <字段名2> (asc/desc), ... , <字段名n> (asc/desc)

  • 查询所有员工名字和薪资，按薪资排序，薪资相同按名字升序

    select ename, sal from emp order by sal asc, ename asc;

    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)

根据字段的位置排序

select ... from ... where ... order by <字段位置> (asc/desc)		// 字段位置即列号
// 了解一下即可，不建议在开发中这样写，不具有健壮型

  • 查询所有员工名字和薪资，并按薪资排序

    select ename, sal from emp order by 2;

    mysql> select ename, sal from emp order by 2;
+--------+---------+
| 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)

  • 查询工资在1250到3000之间的员工信息，要求按照薪资降序排列

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

    mysql> select ename, sal from emp where sal between 1250 and 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.00 sec)

单行处理函数

单行处理函数：输入一行，输出一行

单行处理函数又称数据处理函数

lower(字段名)：转换小写

select lower(ename) from emp;

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)

// 14个输入对应14个输出，这就是单行处理函数特点

upper(字段名)：转换大写

select upper(ename) from emp;

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)

substr(字段名, 起始下标, 截取长度)：取子串

select substr(ename, 1, 1) from emp;

mysql> select substr(ename, 1, 1) from emp;
+---------------------+
| substr(ename, 1, 1) |
+---------------------+
| S                   |
| A                   |
| W                   |
| J                   |
| M                   |
| B                   |
| C                   |
| S                   |
| K                   |
| T                   |
| A                   |
| J                   |
| F                   |
| M                   |
+---------------------+
14 rows in set (0.00 sec)

查询姓名第一字母为A的员工信息

select ename from emp where ename like 'A%';		// 方法一：模糊查询
select ename from emp where substr(ename, 1, 1)='A';	// 方法二：用substr()函数

// 方法一：模糊查询
mysql> select ename from emp where ename like 'A%';
+-------+
| ename |
+-------+
| ALLEN |
| ADAMS |
+-------+
2 rows in set (0.00 sec)

// 方法二：用substr()函数
mysql> select ename from emp where substr(ename, 1, 1)='A';
+-------+
| ename |
+-------+
| ALLEN |
| ADAMS |
+-------+
2 rows in set (0.00 sec)

length(字段名)：取长度

select length(ename) from emp;

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

concat(字符串, ...)：字符串拼接

select concat(empno, ename, sal) from emp;

mysql> select concat(empno, ename, sal) from emp;
+---------------------------+
| concat(empno, ename, sal) |
+---------------------------+
| 7369SMITH800.00           |
| 7499ALLEN1600.00          |
| 7521WARD1250.00           |
| 7566JONES2975.00          |
| 7654MARTIN1250.00         |
| 7698BLAKE2850.00          |
| 7782CLARK2450.00          |
| 7788SCOTT3000.00          |
| 7839KING5000.00           |
| 7844TURNER1500.00         |
| 7876ADAMS1100.00          |
| 7900JAMES950.00           |
| 7902FORD3000.00           |
| 7934MILLER1300.00         |
+---------------------------+
14 rows in set (0.01 sec)

首字母大写

select concat(upper(substr(ename, 1, 1)), lower(substr(ename, 2, length(ename) - 1))) from emp;

mysql> select concat(upper(substr(ename, 1, 1)), lower(substr(ename, 2, length(ename) - 1))) from emp;
+--------------------------------------------------------------------------------+
| concat(upper(substr(ename, 1, 1)), lower(substr(ename, 2, length(ename) - 1))) |
+--------------------------------------------------------------------------------+
| Smith                                                                          |
| Allen                                                                          |
| Ward                                                                           |
| Jones                                                                          |
| Martin                                                                         |
| Blake                                                                          |
| Clark                                                                          |
| Scott                                                                          |
| King                                                                           |
| Turner                                                                         |
| Adams                                                                          |
| James                                                                          |
| Ford                                                                           |
| Miller                                                                         |
+--------------------------------------------------------------------------------+
14 rows in set (0.00 sec)

trim(字符串)：去除前后空格

select * from emp where ename=trim('   king  ');

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)

round(数字)：四舍五入

select 1234.567 as bieming from dept;
select round(1234.567, 0) as bieming from dept;
select round(1234.567, 1) as bieming from dept;
select round(1234.567, -1) as bieming from dept;

mysql> select 1234.567 as bieming from dept;
+----------+
| bieming  |
+----------+
| 1234.567 |
| 1234.567 |
| 1234.567 |
| 1234.567 |
+----------+
4 rows in set (0.00 sec)

mysql> select round(1234.567, 0) as bieming from dept;
+---------+
| bieming |
+---------+
|    1235 |
|    1235 |
|    1235 |
|    1235 |
+---------+
4 rows in set (0.01 sec)

mysql> select round(1234.567, 1) as bieming from dept;
+---------+
| bieming |
+---------+
|  1234.6 |
|  1234.6 |
|  1234.6 |
|  1234.6 |
+---------+
4 rows in set (0.00 sec)

mysql> select round(1234.567, -1) as bieming from dept;
+---------+
| bieming |
+---------+
|    1230 |
|    1230 |
|    1230 |
|    1230 |
+---------+
4 rows in set (0.01 sec)

rand()：生成随机数

select rand() from emp;
select round(rand()*100, 0) from emp;

mysql> select rand() from emp;
+---------------------+
| rand()              |
+---------------------+
|  0.5602156506480794 |
|  0.1136106728702883 |
|   0.887406443140848 |
|  0.0962002278270202 |
|   0.818781840446202 |
|  0.8053085494835941 |
|  0.5701972568130095 |
| 0.43506066634791035 |
|  0.4647115920341682 |
| 0.01837599186075478 |
|  0.6977452139349144 |
| 0.43359812482595267 |
| 0.07475501305866522 |
| 0.07298072154911302 |
+---------------------+
14 rows in set (0.00 sec)

mysql> select round(rand()*100, 0) from emp;
+----------------------+
| round(rand()*100, 0) |
+----------------------+
|                    5 |
|                   31 |
|                   38 |
|                  100 |
|                   84 |
|                   22 |
|                   55 |
|                   12 |
|                   95 |
|                   40 |
|                   12 |
|                   41 |
|                   70 |
|                   26 |
+----------------------+
14 rows in set (0.00 sec)

ifnull(字段名, 为null时转换为什么值)：数据为空时的处理

数据库中，只要有null参与的数学运算结果都为null，因此需要对null进行处理

ifnull(字段名, 如果数据的该字段为null就转为什么值)

select ename, sal+comm from emp;
select ename, sal+ifnull(comm, 0) from emp;

mysql> select ename, sal+comm from emp;
+--------+----------+
| ename  | sal+comm |
+--------+----------+
| 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 |
+--------+----------+
14 rows in set (0.00 sec)

mysql> select ename, sal+ifnull(comm, 0) from emp;
+--------+---------------------+
| ename  | sal+ifnull(comm, 0) |
+--------+---------------------+
| 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 |
+--------+---------------------+
14 rows in set (0.00 sec)

case when then when then else end：条件字段

当员工的工作岗位是MANAGER的时候，工资上调10%，当工作岗位是SALESMAN的时候，工资上调50%

select
	ename,
	job,
	sal as oldsal,
	(case job
		when job='manager' then sal*1.1
		when job='salesman' then sal*1.5
		else sal
	end)
	as newsal
from
	emp;

mysql> select
    ->	 	ename,
    -> 		job,
    -> 		sal as oldsal,
    -> 		(case job
    -> 			when job='manager' then sal*1.1
    -> 			when job='salesman' then sal*1.5
    -> 			else sal
    -> 		end)
    -> 		as newsal
    -> from
    -> 		emp;
+--------+-----------+---------+---------+
| ename  | job       | oldsal  | newsal  |
+--------+-----------+---------+---------+
| SMITH  | CLERK     |  800.00 |  880.00 |
| ALLEN  | SALESMAN  | 1600.00 | 1760.00 |
| WARD   | SALESMAN  | 1250.00 | 1375.00 |
| JONES  | MANAGER   | 2975.00 | 4462.50 |
| MARTIN | SALESMAN  | 1250.00 | 1375.00 |
| BLAKE  | MANAGER   | 2850.00 | 4275.00 |
| CLARK  | MANAGER   | 2450.00 | 3675.00 |
| SCOTT  | ANALYST   | 3000.00 | 3300.00 |
| KING   | PRESIDENT | 5000.00 | 5500.00 |
| TURNER | SALESMAN  | 1500.00 | 1650.00 |
| ADAMS  | CLERK     | 1100.00 | 1210.00 |
| JAMES  | CLERK     |  950.00 | 1045.00 |
| FORD   | ANALYST   | 3000.00 | 3300.00 |
| MILLER | CLERK     | 1300.00 | 1430.00 |
+--------+-----------+---------+---------+
14 rows in set, 14 warnings (0.00 sec)

多行处理函数

多行处理函数：输入多行，输出一行

多行处理函数又称分组函数、聚合函数

多行处理函数必须先分组，如果没有分组，则整张表默认为一组

count(字段名)：计数

计算员工数量

select count(empno) from emp;

mysql> select count(empno) from emp;
+--------------+
| count(empno) |
+--------------+
|           14 |
+--------------+
1 row in set (0.00 sec)

sum(字段名)：求和

计算工资和

select sum(sal) from emp;

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

avg(字段名) ：平均值

计算平均工资

select avg(sal) from emp;

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

max(字段名)：最大值

查询最高工资

select max(sal) from emp;

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

min(字段名)：最小值

查询最低工资

select min(sal) from emp;

mysql> select min(sal) from emp;
+----------+
| min(sal) |
+----------+
|   800.00 |
+----------+
1 row in set (0.00 sec)

分组函数自动忽略null

select sum(comm) from emp;

mysql> select sum(comm) from emp;
+-----------+
| sum(comm) |
+-----------+
|   2200.00 |
+-----------+
1 row in set (0.00 sec)

mysql> select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)

注意count(*)和count(字段名)的区别

  • *代表一整行记录，而不可能有一整行记录都是null的（否则这条记录就没有意义）
  • count(*)输出总行数，count(字段名)统计该字段下所有不为NULL的元素的总数
select count(*) from emp;
select count(comm) from emp;

mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
|       14 |
+----------+
1 row in set (0.00 sec)

mysql> select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)

分组函数不能用在where子句中

分组函数必须先分组才能使用，而where子句先于group by子句执行，因此where执行的时候还没有分组

找出工资高于最低工资的员工信息

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

mysql> select empno, ename, sal from emp where sal>min(sal);
ERROR 1111 (HY000): Invalid use of group function

所有分组函数可以组合起来一起用

select sum(sal), min(sal), max(sal), avg(sal), count(*) from emp;

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 ...
// 关键字顺序不能换
// 语句执行顺序为from -> where -> group by -> select -> order by

  • 查询每个岗位的工资和

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

    mysql> 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 |
+-----------+----------+
5 rows in set (0.01 sec)

mysql> select ename, job, sum(sal) from emp group by job;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'bjpowernode.emp.ENAME' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
// 加了group by之后，select子句后只能跟分组字段和分组函数，不能跟其他字段

  • 查询每个部门的最高薪资

    select deptno, max(sal) from emp group by deptno;

    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.01 sec)

多个字段分组查询

select ... from ... group by <字段名1>, <字段名2>, ... , <字段名n>

  • 查询每个部门，不同工作岗位的最高薪资

    select deptno, job, max(sal) from emp group by deptno, job;

    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)

having子句

having子句用于过滤分组后的数据，不能单独使用，必须与group by搭配使用

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

  • 查询每个部门的最高薪资，要求显示最高薪资大于3000的

    select deptno, max(sal) from emp where sal>3000 group by deptno;
select deptno, max(sal) from emp group by deptno having max(sal)>3000;

    mysql> select deptno, max(sal) from emp where sal>3000 group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
+--------+----------+
1 row in set (0.00 sec)

mysql> select deptno, max(sal) from emp group by deptno having max(sal)>3000;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
+--------+----------+
1 row in set (0.00 sec)

// 前者先筛选出薪资大于3000的再分组，后者先分组再筛选出大于3000的，前者效率更高
// 因此先考虑用where，where实现不了的采用having

  • 查询每个部门的平均薪资，要求显示平均薪资大于2500的

    select deptno, avg(sal) from emp group by deptno having avg(sal)>2500;

    mysql> select deptno, avg(sal) from emp group by deptno having avg(sal)>2500;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+
1 row in set (0.00 sec)

总结

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

// 以上关键字只能按照这个顺序来，不能颠倒
// from -> where -> group by -> having -> select -> order by
/**
 * 先选择一张要查询的表(from)，然后用where筛选数据，再group by分组，再用having筛选分组后的数据
 * 将这些数据select出来，进行order by排序，最后输出
 */

  • 查询除了manager之外，各部门平均薪资高于1500的岗位，并按平均薪资降序排列

    select
	deptno, job, avg(sal) as avgsal
from
	emp
where
	job != 'manager'
group by
	deptno, job
having
	avg(sal)>1500
order by
	avgsal desc;

    mysql> select deptno, job, avg(sal) as avgsal from emp where job != 'manager' group by deptno, job having avg(sal)>1500 order by avgsal desc;
+--------+-----------+-------------+
| deptno | job       | avgsal      |
+--------+-----------+-------------+
|     10 | PRESIDENT | 5000.000000 |
|     20 | ANALYST   | 3000.000000 |
+--------+-----------+-------------+
2 rows in set (0.00 sec)

补充distinct关键字

去除重复记录

只是将查询结果去除重复记录，因为是select关键字所以不会修改原表上的记录

select distinct <字段名> from ...
select distinct <字段名1>, <字段名2>, ... , <字段名n> from ...

select job from emp;		// 有重复数据
select distinct job from emp;	// distinct关键字去重
select count(distinct job) from emp;		// 可以嵌套在函数里面

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.01 sec)

mysql> select distinct job from emp;	// distinct关键字去重
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+
5 rows in set (0.00 sec)

mysql> select count(distinct job) from emp;		// 可以嵌套在函数里面
+---------------------+
| count(distinct job) |
+---------------------+
|                   5 |
+---------------------+
1 row in set (0.00 sec)

select ename, distinct job from emp;	// 前面加其他字段会报错，distinct只能出现在所有字段的最前方
select distinct job, deptno from emp;	// distinct出现在最前方，表示job和deptno联合起来去重

mysql> select ename, distinct job from emp;	// 前面加其他字段会报错，distinct只能出现在所有字段的最前方
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 'distinct job from emp' at line 1

mysql> select job, deptno from emp;
+-----------+--------+
| job       | deptno |
+-----------+--------+
| CLERK     |     20 |
| SALESMAN  |     30 |
| SALESMAN  |     30 |
| MANAGER   |     20 |
| SALESMAN  |     30 |
| MANAGER   |     30 |
| MANAGER   |     10 |
| ANALYST   |     20 |
| PRESIDENT |     10 |
| SALESMAN  |     30 |
| CLERK     |     20 |
| CLERK     |     30 |
| ANALYST   |     20 |
| CLERK     |     10 |
+-----------+--------+
14 rows in set (0.00 sec)

mysql> select distinct job, deptno from emp;	// distinct出现在最前方，表示job和deptno联合起来去重
+-----------+--------+
| job       | deptno |
+-----------+--------+
| CLERK     |     20 |
| SALESMAN  |     30 |
| MANAGER   |     20 |
| MANAGER   |     30 |
| MANAGER   |     10 |
| ANALYST   |     20 |
| PRESIDENT |     10 |
| CLERK     |     30 |
| CLERK     |     10 |
+-----------+--------+
9 rows in set (0.00 sec)

连接查询

连接查询的基本概念

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

连接查询的分类

  • 根据语法年代分类：SQL92，SQL99（我们这里学习的是SQL99）
  • 根据表连接方式分类
    • 内连接：等值连接、非等值连接、自连接
    • 外连接：左外连接（左连接）、右外连接（右连接）
    • 全连接（不讲）

笛卡尔积现象

当两张表进行连接查询时，没有任何限制，会发生什么现象？
当两张表进行连接查询，没有任何条件限制的时候，最终查询结果条数，是两张表条数的乘积，这种现象被称为：笛卡尔积现象

查询每个员工所在的部门名称

select ename, deptno from emp;
select deptno, dname from dept;
select ename, dname from emp, dept;

mysql> select ename, deptno from emp;
+--------+--------+
| ename  | deptno |
+--------+--------+
| SMITH  |     20 |
| ALLEN  |     30 |
| WARD   |     30 |
| JONES  |     20 |
| MARTIN |     30 |
| BLAKE  |     30 |
| CLARK  |     10 |
| SCOTT  |     20 |
| KING   |     10 |
| TURNER |     30 |
| ADAMS  |     20 |
| JAMES  |     30 |
| FORD   |     20 |
| MILLER |     10 |
+--------+--------+
14 rows in set (0.00 sec)

mysql> select deptno, dname from dept;
+--------+------------+
| deptno | dname      |
+--------+------------+
|     10 | ACCOUNTING |
|     20 | RESEARCH   |
|     30 | SALES      |
|     40 | OPERATIONS |
+--------+------------+
4 rows in set (0.01 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.00 sec)

怎么避免笛卡尔积现象？
连接时加条件，只有满足条件的记录才连接起来

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

mysql> select ename, dname from emp, dept where emp.deptno = dept.deptno;

mysql> select emp.ename, dept.dname from emp, dept where emp.deptno = dept.deptno;

mysql> select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno;	// SQL92的语法

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

虽然避免了笛卡尔现象，但匹配次数还是56次，只是进行了四选一

内连接之等值连接

查询每个员工所在的部门名称

// SQL92语法
select
	e.ename, d.dname
from
	emp e, dept d
where
	e.deptno = d.deptno;
// 92语法的缺点：结构不清晰，表的连接条件，和后期进一步筛选的条件，都放到了where后面

// SQL99语法
select
	e.ename, d.dname
from
	emp e
(inner) join	// inner可以省略，带着inner可读性更好
	dept d
on
	e.deptno = d.deptno;	// 条件是等量关系，因此称为等值连接
// 99语法的优点：表连接的条件是独立的，连接之后，如果还需要进一步筛选，再往后继续添加where后面

mysql> select e.ename, d.dname from emp e join dept d on e.deptno = d.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 |
+--------+------------+
14 rows in set (0.00 sec)

内连接之非等值连接

查询每个员工的薪资等级

select
	e.ename, e.sal , s.grade
from
	emp e
(inner) join
	salgrade s
on
	e.sal between s.losal and s.hisal	// 条件不是一个等量关系，称为非等值连接
order by
	s.grade asc, e.sal, e.ename;	// 甚至还能排个序

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

内连接之自连接

查询员工的上级领导

select
	a.ename '员工', b.ename '领导'
from 
	emp a
join			// 一张表看成两张表，自连接
	emp b
on
	a.mgr = b.empno;		// 员工的领导编号 = 领导的员工编号

mysql> select
    -> a.ename '员工', b.ename '领导'
    -> 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)

外连接

select
	e.ename, d.dname
from
	emp e
(inner) join
	dept d
on
	e.deptno = d.deptno;	// 内连接的特点：两张表没有主次关系，只把完全能够匹配上这个条件的数据查询出来
	
select
	e.ename, d.dname
from
	emp e
right (outer) join		// outer可以省略，带着可读性强
	dept d
on
	e.deptno = d.deptno;
/**
 * 外连接的特点：两张表有主次关系，主次关系通过left和right关键字来表示
 * right代表将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表
 * 外连接的查询结果条数 >= 内连接的查询结果条数
 */

mysql> select e.ename, d.dname from emp e right join 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
+--------+------------+
15 rows in set (0.00 sec)

查询员工的上级领导，显示所有员工名字和领导名

select
	a.ename '员工', b.ename '领导'
from 
	emp a
left join
	emp b
on
	a.mgr = b.empno;		// 员工的领导编号 = 领导的员工编号

mysql> select
    -> a.ename '员工', b.ename '领导'
    -> 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  |
+--------+--------+
14 rows in set (0.00 sec)

全连接

全连接相当于外连接的时候两张表都是主表，MySql不支持全连接，在此不再讨论

多表连接

内外连接可以混合

select
	...
from
	a
join b on <a和b的连接条件>
join c on <a和c的连接条件>
join d on <a和d的连接条件>

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

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 s.hisal;

mysql> 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 s.hisal;
+--------+------------+---------+-------+
| ename  | dname      | sal     | grade |
+--------+------------+---------+-------+
| SMITH  | RESEARCH   |  800.00 |     1 |
| ALLEN  | SALES      | 1600.00 |     3 |
| WARD   | SALES      | 1250.00 |     2 |
| JONES  | RESEARCH   | 2975.00 |     4 |
| MARTIN | SALES      | 1250.00 |     2 |
| BLAKE  | SALES      | 2850.00 |     4 |
| CLARK  | ACCOUNTING | 2450.00 |     4 |
| SCOTT  | RESEARCH   | 3000.00 |     4 |
| KING   | ACCOUNTING | 5000.00 |     5 |
| TURNER | SALES      | 1500.00 |     3 |
| ADAMS  | RESEARCH   | 1100.00 |     1 |
| JAMES  | SALES      |  950.00 |     1 |
| FORD   | RESEARCH   | 3000.00 |     4 |
| MILLER | ACCOUNTING | 1300.00 |     2 |
+--------+------------+---------+-------+
14 rows in set (0.00 sec)

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

select
	e.ename, l.ename, d.dname, e.sal, s.grade
from
	emp e
join
	dept d
on
	e.deptno = d.deptno
join
	salgrade s
on
	e.sal between s.losal and s.hisal
left join
	emp l
on
	e.mgr = l.empno;

mysql> select
    -> e.ename, l.ename, d.dname, e.sal, s.grade
    -> from
    -> emp e
    -> join
    -> dept d
    -> on
    -> e.deptno = d.deptno
    -> join
    -> salgrade s
    -> on
    -> e.sal between s.losal and s.hisal
    -> left join
    -> emp l
    -> on
    -> e.mgr = l.empno;
+--------+-------+------------+---------+-------+
| ename  | ename | dname      | sal     | grade |
+--------+-------+------------+---------+-------+
| SMITH  | FORD  | RESEARCH   |  800.00 |     1 |
| ALLEN  | BLAKE | SALES      | 1600.00 |     3 |
| WARD   | BLAKE | SALES      | 1250.00 |     2 |
| JONES  | KING  | RESEARCH   | 2975.00 |     4 |
| MARTIN | BLAKE | SALES      | 1250.00 |     2 |
| BLAKE  | KING  | SALES      | 2850.00 |     4 |
| CLARK  | KING  | ACCOUNTING | 2450.00 |     4 |
| SCOTT  | JONES | RESEARCH   | 3000.00 |     4 |
| KING   | NULL  | ACCOUNTING | 5000.00 |     5 |
| TURNER | BLAKE | SALES      | 1500.00 |     3 |
| ADAMS  | SCOTT | RESEARCH   | 1100.00 |     1 |
| JAMES  | BLAKE | SALES      |  950.00 |     1 |
| FORD   | JONES | RESEARCH   | 3000.00 |     4 |
| MILLER | CLARK | ACCOUNTING | 1300.00 |     2 |
+--------+-------+------------+---------+-------+
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);
select min(sal) from emp;
select ename, sal from emp where sal>800;
select ename, sal from emp where sal>(select min(sal) from emp);

mysql> select ename, sal from emp where sal>min(sal);
ERROR 1111 (HY000): Invalid use of group function	// where子句中不能使用分组函数

// 思路
// 先找出最低工资
mysql> select min(sal) from emp;
+----------+
| min(sal) |
+----------+
|   800.00 |
+----------+
1 row in set (0.01 sec)

// 再找出比最低工资高的
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 |
+--------+---------+
13 rows in set (0.00 sec)

// 合并
mysql> select ename, sal from emp where sal>(select min(sal) from emp);
+--------+---------+
| 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 |
+--------+---------+
13 rows in set (0.01 sec)

from中的子查询

from中的子查询，可以将子查询的结果当成一张临时表

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

select job, avg(sal) from emp group by job;
select
	a.job, a.avgsal, s.grade
from
	(select job, avg(sal) avgsal from emp group by job) a	// 注意去掉分号，带有分组函数的要起别名
join
	salgrade s
on
	a.avgsal between losal and hisal;

// 先找出每个岗位的平均工资
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 |
+-----------+-------------+
5 rows in set (0.00 sec)


// 再将这张临时表与薪资等级表连接，找出薪资等级
mysql> select
    -> a.job, a.avgsal, s.grade
    -> from
    -> (select job, avg(sal) avgsal from emp group by job) a
    -> join
    -> salgrade s
    -> on
    -> a.avgsal between losal and hisal;
+-----------+-------------+-------+
| job       | avgsal      | grade |
+-----------+-------------+-------+
| CLERK     | 1037.500000 |     1 |
| SALESMAN  | 1400.000000 |     2 |
| MANAGER   | 2758.333333 |     4 |
| ANALYST   | 3000.000000 |     4 |
| PRESIDENT | 5000.000000 |     5 |
+-----------+-------------+-------+
5 rows in set (0.00 sec)

select中的子查询

了解即可，可读性比较差

查询每个员工所在的部门名

select e.ename, e.deptno, (select d.dname from dept d where e.deptno = d.deptno) dname from emp e;	
// 只要子查询的查询结果多于1条记录，该查询就报错

mysql> select e.ename, e.deptno, (select d.dname from dept d where e.deptno = d.deptno) dname from emp e;		// 只要子查询的查询结果多于1条记录，该查询就报错
+--------+--------+------------+
| 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合并查询结果集

对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，成倍的增长
union在减少匹配次数的情况下，还可以完成两个结果集的拼接

a10条记录，b10条记录，c10条记录

  • 第一种方式：a连接b连接c：匹配次数是：1000次
  • 第二种方式：a连接b一个结果： 10 × 10 = 100 次 10 \times 10 = 100次 10×10=100次；a 连接c一个结果： 10 × 10 = 100 次 10 \times 10 = 100次 10×10=100次，使用union的话是： 100 + 100 = 200 次 100 + 100 = 200次 100+100=200次。

union进行结果合并时，要求列数相同，且列的数据类型也要相同

查询工作岗位是manager和salesman的员工

select ename, job from emp where job = 'manager' or job = 'salesman';
select ename, job from emp where job in('manager', 'salesman');
select ename, job from emp where job = 'manager'
union
select ename, job from emp where job = 'salesman';

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 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'
    -> 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 |
+--------+----------+
7 rows in set (0.00 sec)

limit取部分查询结果集

limit取部分查询结果集，通常用在分页中

按照薪资降序，取出排名在前5名的员工

limit 起始下标, 长度
limit 长度	// 默认从0开始
// limit在order by执行之后再执行

select ename, sal from emp order by sal desc limit 5;
select ename, sal from emp order by sal desc limit 0, 5;

mysql> 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 rows in set (0.00 sec)

mysql> select ename, sal from emp order by sal desc limit 0, 5;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.00 sec)

取出工资排名在[3-5]名的员工？

select ename, sal from emp order by sal desc limit 2, 3;

mysql> select ename, sal from emp order by sal desc limit 2, 3;
// 2表示起始位置从下标2开始，就是第三条记录，3表示长度
+-------+---------+
| ename | sal     |
+-------+---------+
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
3 rows in set (0.00 sec)

每页显示3条记录

第1页：limit 0, 3；第2页：limit 3, 3；第3页：limit 6, 3；第4页：limit 9, 3；

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

DQL语句大总结

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

// 以上关键字只能按照这个顺序来，不能颠倒
// from -> where -> group by -> having -> select -> order by -> limit
/**
 * 先选择一张要查询的表(from)，然后用where筛选数据，再group by分组，再用having筛选分组后的数据
 * 将这些数据select出来，进行order by排序，最后limit切片输出
 */
-DiamondK-
关注
专栏目录
MySQL数据库之基础查询语句（DQL数据查询语句）
Sheenky的博客
04-20 2988
数据库的基础查询语法：从数据表（单个表/多个表）中提取出满足特定条件的语句为查询语句。 基本的查询语法如下： select 结果显示语句 from 需要查询的表 where 查询条件语句 结果处理语句; 基本的查询语句是通过select ...from .... where ....的形式呈现。同时，单表和多表的差别就在from 后面接的表格数目来确定的。 一、where子句 where子句：在删除、修改及查询的语句后都可以添加where⼦句（条件），⽤于筛选满⾜特定的添加的数据进⾏删除、修改和
《oracle数据库》Top-N分析：查询员工表中，工资排名在10-20之间的员工信息。
程序员之道
03-14 5365
Top-N分析 对 ROWNUM 只能使用 &lt; 或 &lt;=, 而用 =, &gt;, &gt;= 都将不能返回任何数据。 例：查询员工表中，工资排名在10-20之间的员工信息。 首先需要查询出按工资倒序排序后的序号，使用以下查询语句查询的序号为工资倒序排序前的序号。 那么，就得先对员工表的工资进行倒序排序，再查询序号 最后过滤，由于ROWNUM（序号）只能使用&lt;或&lt;=...
saprk RDD编程实战案例给定JSON格式的订单数据和分类数据,求出每个商品分类的成交金额,写入到mysql中
weixin_47699191的博客
03-31 276
需求 给定JSON格式的订单数据,求出每个商品分类的成交金额 订单数据 {"oid":"o123", "cid": 1, "money": 600.0, "longitude":116.397128,"latitude":39.916527} "oid":"o112", "cid": 3, "money": 200.0, "longitude":118.396128,"latitude":35.916527} {"oid":"o124", "cid": 2, "money": 200.0, "longitu
Mysql
Struggle233的博客
07-10 1634
Mysql(1)一、数据库简介 文本形式存储数据存在很多弊端：执行效率低；占用内存。DB： DateBase数据库，本质就是文件集（多个文件）会按照特定存储规范进行数据增删改查；DBMS： DateBaseManagementSystem数据库管理系统，本质就是管理数据库文件的软件，Oracle Mysql;SQL： Strctured Query Language：结构化查询语言，通过sql语言...
MYSQL(2)
weixin_47703200的博客
04-10 129
case: SELECT 字段名 case WHEN… THEN… END AS FROM 表名… 例： 查询员工年龄，并进行分段： 10-20yong20-30 midlife 30-40 old_man 40-50 old 代码： select name,age,case when age >= 10 and age < 20 then ‘yong’ when age >= 20 and age < 30 then ‘midlife’ when age >= 30
MySQL DQL（数据查询语言）
热门推荐
Java 技术专栏，从入门到精通，熟悉企业级开发
04-20 1万+
此为MySQL 专栏文章之一，讲解DQL 通用语法。MySQL 通用语法分类● DQL: 数据查询语言，用来 查询 数据库中表的记录。
【MySQL 系列】MySQL 语句篇_DQL 语句
栗筝i的博客
03-09 3428
DQL（Data Query Language），即数据查询语言，用来查询数据记录。DQL 基本结构由 SELECT FROM、WHERE、JOIN 等子句构成。 DQL 语句并不会改变数据库，而是让数据库将查询结果发送结果集给客户端，返回的结果是一张虚拟表。
MySQL：DQL 数据查询语句盘点
栗筝i的博客
10-18 4870
本篇内容包括：DQL 的简介、SELECT 语句、WHERE 条件语句、JOIN 连接查询(多表查询)和分组、过滤、排序、分页、子查询的使用。
MySQL DQL 子查询
Dablelv 的博客专栏。
09-07 1515
子查询是另一个语句中的 SELECT 语句。子查询也称为内查询（Inner Query），必须位于括号之中。包含子查询的查询称为外查询（Outer Query）。子查询支持多层嵌套，也就是子查询可以包含其他子查询。子查询的外部语句可以是以下任一语句：SELECT、INSERT、UPDATE、DELETE、SET 或 DO。下面是一个示例。在这个例子中，是外查询，位于括号中的是子查询。
MySQL中的DQL查询语句
03-30
在MySQL中对表中的数据的简单查询语句
MySQL中SQL语句——DQL(数据查询语句)
白日梦
12-10 2273
前言: 首先对于数据库有一定的了解，会对于Mysql的学习有一定的帮助；数据库主要分为 DB(数据库)，DBMS(数据库管理系统)，SQL(结构化查询语言，用于和DBMS通信的语言)；这篇要讲的DQL(数据查询语句)是属于SQL语言中的一种语言，因此是重中之重的语言，希望可以有一定的帮助。 Mysql的相关知识 好处:用于数据的持久存储。同时运用管理软件进行统一管理，方便查询； 数据库主要分为: DB(数据库)，保存一系列有组织的数据容器。 DBMS(数据库管理系统)，用于对DB获得数据进行操作管理，又称
mysql入门基础笔记——DQL查询语句
worther的博客
02-27 1270
数据库中的基本单元：表 table 查看数据库下表：mysql> show tables; ·数据库中是以表来储存数据的，表较为直观且每一行都有行和列 行（row）：数据/字段 列（colum）:字段 ，每一个字段都有字段名，数据类型和约束条件等属性 SQL语句分类 DQL:数据查询语言（凡是带有select关键字的都是查询语句） DML:数据操作语言（凡是对标志那个数据进行增删改的都是DML） insert 增 delete 删 updata 改 DDL：数据定义语言（凡是
【MySQL】表的操作【有关表结构的操作】【创建、查看、删除、修改表结构】
最新发布
aaa114514aaaa的博客
09-12 1047
对于mysql数据库的表的结构的查看，创建、删除、修改、【对字段的添加、修改】
基于SpringBoot+Vue+MySQL的考研互助交流平台
程序员大金的博客
09-11 673
基于SpringBoot+Vue+MySQL的考研互助交流平台，附源码。
mysql组合键唯一
qq_36321899的博客
09-11 336
这些方法可以帮助您在MySQL数据库中设置多个字段的组合唯一性，确保数据的完整性和准确性。如果您在操作过程中遇到任何问题，可以参考相关的MySQL文档或搜索相应的解决方案。请注意，在添加组合唯一键之前，确保表中的数据已经满足唯一性约束，否则操作将会失败。如果表中已经存在重复的组合数据，您需要先清理这些数据，然后才能成功添加唯一键。在MySQL中，如果您想要确保两个或多个列的组合数据是唯一的，您可以使用组合唯一键（也称为复合唯一键或联合唯一索引）。表添加一个组合唯一键，以确保。例如，如果您有一个名为。
基于SpringBoot+Vue+MySQL的瑜伽馆管理系统
程序员大金的博客
09-11 1219
基于SpringBoot+Vue+MySQL的瑜伽馆管理系统，附源码。
数据库运维实操优质文章文档分享（含Oracle、MySQL等） | 2024年8月刊
Era666的博客
09-10 2009
本文为大家整理了墨天轮数据社区2024年8月发布的优质技术文章、文档，主题涵盖Oracle、MySQL、PostgreSQL等主流数据库系统以及国产数据库的技术实操！
【mysql】mysql之优化
zerotoall的博客
09-11 2812
数据库优化 　 一、数据库硬件优化（选型） 　　 1.一般数据库选择 　　 2.数据库类型 　　 3.硬件选型 　　 4.操作系统优化 　　 5.应用端优化 　 二、创建数据库 　　 1.创建一个库一个表，并插入100万数据 　　 2.查看数据可用性 　　 3.进行压力测试 　 三、数据库参数优化 　　 1.Max_connections 　　 2.back_log 　　 3.wait_timeout和interactive_timeout 　　
MySQL DQL教程：数据查询与条件语句
1. 理解并运用DQL进行数据查询。 2. 掌握SELECT语句的基本结构和用法。 3. 学会使用WHERE子句设定查询条件，例如查询80-90分的成绩记录。 4. 理解并实践LEFT JOIN, OUTER JOIN, INNER JOIN等联合查询操作。 5. 使用...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值