【MySQL】入门基础(一)

MySQL入门基础

数据库概述及数据准备

使用到的数据库:bjpowernode.sql

传输链接:https://cowtransfer.com/s/233bc2ba9f9b43 或 打开【奶牛快传】cowtransfer.com 使用传输口令:es0w6m 提取;

sql、DB、DBMS分别是什么,他们之间的关系?

  • DB: DataBase数据库,数据库实际上在硬盘上以文件的形式存在)

  • DBMS: DataBase Management System(数据库管理系统,常见的有:MySQL Oracle DB2 Sybase SqlServer…)

  • SQL:

    • 结构化查询语言,是一门标准通用的语言。标准的sql适合于所有的数据库产品。
    • SQL属于高级语言。只要能看懂英语单词的,写出来的sql语句,可以读懂什么意思。
    • SQL语句在执行的时候,实际上内部也会先进行编译,然后再执行sql。(sql语句的编译由DBMS完成。)

DBMS负责执行sql语句,通过执行sql语句来操作DB当中的数据。
DBMS -(执行)-> SQL -(操作)-> DB


什么是表?

表:table

表:table是数据库的基本组成单元,所有的数据都以表格的形式组织,目的是可读性强。

一个表包括行和列:
行:被称为数据/记录(data)
列:被称为字段(column)

学号(int)	姓名(varchar)	年龄(int)
------------------------------------
110			张三				20
120			李四				21

每一个字段应该包括哪些属性?

字段名数据类型相关的约束

SQL语句的分类

3、学习MySQL主要还是学习通用的SQL语句,那么SQL语句包括增删改查,SQL语句怎么分类呢?
DQL(数据查询语言): 查询语句,凡是select语句都是DQL。
DML(数据操作语言)insert delete update,对表当中的数据进行增删改。
DDL(数据定义语言)create drop alter,对表结构的增删改。
TCL(事务控制语言)commit提交事务,rollback回滚事务。(TCL中的T是Transaction)
DCL(数据控制语言): grant授权、revoke撤销权限等。


导入数据

​ 第一步:登录mysql数据库管理系统
​ dos命令窗口:
mysql -uroot -p333
​ 第二步:查看有哪些数据库
show databases; (这个不是SQL语句,属于MySQL的命令。)

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

​ 第三步:创建属于我们自己的数据库
create database bjpowernode; (这个不是SQL语句,属于MySQL的命令。)
​ 第四步:使用bjpowernode数据
use bjpowernode;(这个不是SQL语句,属于MySQL的命令。)
​ 第五步:查看当前使用的数据库中有哪些表?
show tables;(这个不是SQL语句,属于MySQL的命令。)
​ 第六步:初始化数据
source D:\course\05-MySQL\resources\bjpowernode.sql

注意:数据初始化完成之后,有三张表:

show tables;

+-----------------------+
| Tables_in_bjpowernode |
+-----------------------+
| dept                  |
| emp                   |
| salgrade              |
+-----------------------+

​ bjpowernode.sql,这个文件以sql结尾,这样的文件被称为“sql脚本”。什么是sql脚本呢?
​ 当一个文件的扩展名是.sql,并且该文件中编写了大量的sql语句,我们称这样的文件为sql脚本。
​ 注意:直接使用source命令可以执行sql脚本。
​ sql脚本中的数据量太大的时候,无法打开,请使用source命令完成初始化。

删除数据库

drop database bjpowernode;

查看表结构:

use bjpowernode;

show tables;

+-----------------------+
| Tables_in_bjpowernode |
+-----------------------+
| dept                  |   (部门表)
| emp                   |   (员工表)
| salgrade              |   (工资等级表)
+-----------------------+

mysql> desc dept; //查看表dept的结构 describe

+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int(2)      | NO   | PRI | NULL    |       |		部门编号
| DNAME  | varchar(14) | YES  |     | NULL    |       |		部门名称
| LOC    | varchar(13) | YES  |     | NULL    |       |		部门位置
+--------+-------------+------+-----+---------+-------+

mysql> desc emp;

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO    | int(4)      | NO   | PRI | NULL    |       |	员工编号
| ENAME    | varchar(10) | YES  |     | NULL    |       |	员工姓名
| JOB      | varchar(9)  | YES  |     | NULL    |       |	工作岗位
| MGR      | int(4)      | YES  |     | NULL    |       |	上级领导编号
| HIREDATE | date        | YES  |     | NULL    |       |	入职日期
| SAL      | double(7,2) | YES  |     | NULL    |       |	月薪
| COMM     | double(7,2) | YES  |     | NULL    |       |	补助/津贴
| DEPTNO   | int(2)      | YES  |     | NULL    |       |	部门编号
+----------+-------------+------+-----+---------+-------+

mysql> desc salgrade;

+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| GRADE | int(11) | YES  |     | NULL    |       |		等级
| LOSAL | int(11) | YES  |     | NULL    |       |		最低薪资
| HISAL | int(11) | YES  |     | NULL    |       |		最高薪资
+-------+---------+------+-----+---------+-------+

表中的数据

mysql> select * from emp; //从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 |
+-------+--------+-----------+------+------------+---------+---------+--------+

mysql> select * from dept;

+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

mysql> select * from salgrade;

+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

常用命令

mysql> select database(); //查看当前使用的是哪个数据库

+-------------+
| database()  |
+-------------+
| bjpowernode |
+-------------+

mysql> select version(); 查看mysql的版本号。

+-----------+
| version() |
+-----------+
| 5.5.36    |
+-----------+

\c 命令,结束一条语句。

exit 命令,退出mysql。

查看创建表的语句:

show create table emp;

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                          |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp   | CREATE TABLE `emp` (
  `EMPNO` int(4) NOT NULL,
  `ENAME` varchar(10) DEFAULT NULL,
  `JOB` varchar(9) DEFAULT NULL,
  `MGR` int(4) DEFAULT NULL,
  `HIREDATE` date DEFAULT NULL,
  `SAL` double(7,2) DEFAULT NULL,
  `COMM` double(7,2) DEFAULT NULL,
  `DEPTNO` int(2) DEFAULT NULL,
  PRIMARY KEY (`EMPNO`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

简单的查询语句(DQL)

语法格式:
select 字段名1,字段名2,字段名3,… from 表名;

提示:
1、任何一条sql语句以“;”结尾。
2、sql语句不区分大小写

查询一个字段

1、查询部分的名字

select dname from dept;

+------------+
| dname      |
+------------+
| ACCOUNTING |
| RESEARCH   |
| SALES      |
| OPERATIONS |
+------------+

查询两个字段,或者多个字段

使用,逗号隔开

查询部门编号和部门名:

select deptno,dname from dept;

+--------+------------+
| deptno | dname      |
+--------+------------+
|     10 | ACCOUNTING |
|     20 | RESEARCH   |
|     30 | SALES      |
|     40 | OPERATIONS |
+--------+------------+

查询所有字段

  • 第一种方式:可以把每个字段都写上

    select a,b,c,d,... from tablename;

  • 第二种方式:直接使用*

    select * from dept; (千万不要写在java程序中,java中会把*号转换成字段,转换成字段的过程会占用一定的时间,效率低,可读性查)

    +--------+------------+----------+
    | DEPTNO | DNAME      | LOC      |
    +--------+------------+----------+
    |     10 | ACCOUNTING | NEW YORK |
    |     20 | RESEARCH   | DALLAS   |
    |     30 | SALES      | CHICAGO  |
    |     40 | OPERATIONS | BOSTON   |
    +--------+------------+----------+
    

    这种方式的缺点

    1. 效率差
    2. 可读性差

    在实际开发中不建议,实际开发中还是用字段的形式查询,提高效率,增强可读性。

给查询的列起别名

使用as关键字起别名。

注意:只是将显示的查询结果列明显示为 别名,原表列名没有改变。select 语句只负责查询,永远都不会进行修改操作的。

select deptno,dname as deptname from dept;

+--------+------------+
| deptno | deptname   |
+--------+------------+
|     10 | ACCOUNTING |
|     20 | RESEARCH   |
|     30 | SALES      |
|     40 | OPERATIONS |
+--------+------------+

as关键字也可以省略

select deptno,dname deptname from dept;

+--------+------------+
| deptno | deptname   |
+--------+------------+
|     10 | ACCOUNTING |
|     20 | RESEARCH   |
|     30 | SALES      |
|     40 | OPERATIONS |
+--------+------------+

假设别名里有空格,会发生什么情况?

select deptno,dname dept name from dept;

DBMS看到这样的语句,会进行SQL语句的编译,不符合语法,编译报错。

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 'name from dept' at line 1

解决方法:给别名加上引号' '/" "

select deptno,dname 'dept name' from dept; //单引号

select deptno,dname "dept name" from dept; //双引号

+--------+------------+
| deptno | dept name  |
+--------+------------+
|     10 | ACCOUNTING |
|     20 | RESEARCH   |
|     30 | SALES      |
|     40 | OPERATIONS |
+--------+------------+

注意:在所有的数据库当中,字符串统一使用单引号' '括起来,单引号是标准,双引号在Oracle数据库中用不了,但是在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 |
+--------+----------+

给查询结果的列重命名?
select ename,sal * 12 as yearsal from emp;

+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 19200.00 |
| WARD   | 15000.00 |
| JONES  | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+

别名中可以有中文?
select ename,sal * 12 as 年薪 from emp; // 错误
select ename,sal * 12 as '年薪' from emp;

+--------+----------+
| ename  | 年薪     |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 19200.00 |
| WARD   | 15000.00 |
| JONES  | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+

as关键字可以省略?
select empno,ename,sal*12 yearsal from emp;

+-------+--------+----------+
| empno | ename  | yearsal  |
+-------+--------+----------+
|  7369 | SMITH  |  9600.00 |
|  7499 | ALLEN  | 19200.00 |
|  7521 | WARD   | 15000.00 |
|  7566 | JONES  | 35700.00 |
|  7654 | MARTIN | 15000.00 |
|  7698 | BLAKE  | 34200.00 |
|  7782 | CLARK  | 29400.00 |
|  7788 | SCOTT  | 36000.00 |
|  7839 | KING   | 60000.00 |
|  7844 | TURNER | 18000.00 |
|  7876 | ADAMS  | 13200.00 |
|  7900 | JAMES  | 11400.00 |
|  7902 | FORD   | 36000.00 |
|  7934 | MILLER | 15600.00 |
+-------+--------+----------+

条件查询

什么是条件查询?

不是将表中所有的数据都查询出来,是查询出来符合条件的。

语法格式:

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

执行顺序:先from,然后where,最后select

有哪些条件?

= 等于

查询工资等于800的员工姓名和编号?

select empno,ename from emp where sal = 800;

+-------+-------+
| empno | ename |
+-------+-------+
|  7369 | SMITH |
+-------+-------+

查询SMITH的编号和薪资:

select empno,ename,sal from emp where ename = 'SMITH'; //字符串用单引号括起来

+-------+-------+--------+
| empno | ename | sal    |
+-------+-------+--------+
|  7369 | SMITH | 800.00 |
+-------+-------+--------+

<>!= 不等于

查询工资不等于800的员工姓名和编号?

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

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

<小于

查询工资小于2000的员工姓名和编号:

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

<=小于等于

查询工资小于等于3000的员工姓名和编号:

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

+-------+--------+---------+
| empno | ename  | sal     |
+-------+--------+---------+
|  7369 | SMITH  |  800.00 |
|  7499 | ALLEN  | 1600.00 |
|  7521 | WARD   | 1250.00 |
|  7566 | JONES  | 2975.00 |
|  7654 | MARTIN | 1250.00 |
|  7698 | BLAKE  | 2850.00 |
|  7782 | CLARK  | 2450.00 |
|  7788 | SCOTT  | 3000.00 |
|  7844 | TURNER | 1500.00 |
|  7876 | ADAMS  | 1100.00 |
|  7900 | JAMES  |  950.00 |
|  7902 | FORD   | 3000.00 |
|  7934 | MILLER | 1300.00 |
+-------+--------+---------+

>大于

查询工资大于3000的员工姓名和编号:

select empno,ename from emp where sal > 3000;

+-------+-------+
| empno | ename |
+-------+-------+
|  7839 | KING  |
+-------+-------+

>=大于等于

查询工资大于等于3000的员工姓名和编号:

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

+-------+-------+---------+
| empno | ename | sal     |
+-------+-------+---------+
|  7788 | SCOTT | 3000.00 |
|  7839 | KING  | 5000.00 |
|  7902 | FORD  | 3000.00 |
+-------+-------+---------+

between ... and ... 两个值之间,等同于 >= and <=

查询薪资在2450到3000之间的员工信息?包括2450和3000

  • 第一种方式:>= and <=

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

  • 第二种方式:between ... and ...

    select empno,ename,sal from emp where sal between 2450 and 3000;

    +-------+-------+---------+
    | empno | ename | sal     |
    +-------+-------+---------+
    |  7566 | JONES | 2975.00 |
    |  7698 | BLAKE | 2850.00 |
    |  7782 | CLARK | 2450.00 |
    |  7788 | SCOTT | 3000.00 |
    |  7902 | FORD  | 3000.00 |
    +-------+-------+---------+
    

    注意:使用between ... and ...的时候一定要遵循左小右大的顺序,而且是闭区间,包含两端的值。

查询哪些员工的津贴/补助为null:

select empno,ename,sal,comm from emp where comm = null; //错误的

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

+-------+--------+---------+------+
| empno | ename  | sal     | comm |
+-------+--------+---------+------+
|  7369 | SMITH  |  800.00 | NULL |
|  7566 | JONES  | 2975.00 | NULL |
|  7698 | BLAKE  | 2850.00 | NULL |
|  7782 | CLARK  | 2450.00 | NULL |
|  7788 | SCOTT  | 3000.00 | NULL |
|  7839 | KING   | 5000.00 | NULL |
|  7876 | ADAMS  | 1100.00 | NULL |
|  7900 | JAMES  |  950.00 | NULL |
|  7902 | FORD   | 3000.00 | NULL |
|  7934 | MILLER | 1300.00 | NULL |
+-------+--------+---------+------+

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

查询哪些员工的津贴/补助不为null:

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

+-------+--------+---------+---------+
| empno | ename  | sal     | comm    |
+-------+--------+---------+---------+
|  7499 | ALLEN  | 1600.00 |  300.00 |
|  7521 | WARD   | 1250.00 |  500.00 |
|  7654 | MARTIN | 1250.00 | 1400.00 |
|  7844 | TURNER | 1500.00 |    0.00 |
+-------+--------+---------+---------+

and 并且

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

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

+-------+-------+---------+---------+
| empno | ename | job     | sal     |
+-------+-------+---------+---------+
|  7566 | JONES | MANAGER | 2975.00 |
|  7698 | BLAKE | MANAGER | 2850.00 |
+-------+-------+---------+---------+

or 或者

查询工作岗位是manager或salesman的员工:

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

+-------+--------+----------+---------+
| empno | ename  | job      | sal     |
+-------+--------+----------+---------+
|  7499 | ALLEN  | SALESMAN | 1600.00 |
|  7521 | WARD   | SALESMAN | 1250.00 |
|  7566 | JONES  | MANAGER  | 2975.00 |
|  7654 | MARTIN | SALESMAN | 1250.00 |
|  7698 | BLAKE  | MANAGER  | 2850.00 |
|  7782 | CLARK  | MANAGER  | 2450.00 |
|  7844 | TURNER | SALESMAN | 1500.00 |
+-------+--------+----------+---------+

and和or同时出现的话,有优先级问题吗?

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

select empno,ename,sal,deptno from emp where sal > 2500 and deptno = 10 or deptno = 20; //错的

+-------+-------+---------+--------+
| empno | ename | sal     | deptno |
+-------+-------+---------+--------+
|  7369 | SMITH |  800.00 |     20 |
|  7566 | JONES | 2975.00 |     20 |
|  7788 | SCOTT | 3000.00 |     20 |
|  7839 | KING  | 5000.00 |     10 |
|  7876 | ADAMS | 1100.00 |     20 |
|  7902 | FORD  | 3000.00 |     20 |
+-------+-------+---------+--------+

and优先级要比or高,先执行and再执行or

找出工资大于2500并且部门编号为10 的员工,或者部门编号为20的所有员工。

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

+-------+-------+---------+--------+
| empno | ename | sal     | deptno |
+-------+-------+---------+--------+
|  7566 | JONES | 2975.00 |     20 |
|  7788 | SCOTT | 3000.00 |     20 |
|  7839 | KING  | 5000.00 |     10 |
|  7902 | FORD  | 3000.00 |     20 |
+-------+-------+---------+--------+

in 包含,相当于多个or (not in 不在这个范围中)

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

select empno,ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';

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

注意:in不是一个区间,后面跟的是具体的值。

查询薪资是800和5000的员工信息?

select empno,ename,sal from emp where sal in(800,5000); //不是800~5000的区间。

+-------+-------+---------+
| empno | ename | sal     |
+-------+-------+---------+
|  7369 | SMITH |  800.00 |
|  7839 | KING  | 5000.00 |
+-------+-------+---------+

not in表示不在这几个值当中的数据

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

+-------+--------+---------+
| empno | ename  | sal     |
+-------+--------+---------+
|  7499 | ALLEN  | 1600.00 |
|  7521 | WARD   | 1250.00 |
|  7566 | JONES  | 2975.00 |
|  7654 | MARTIN | 1250.00 |
|  7698 | BLAKE  | 2850.00 |
|  7782 | CLARK  | 2450.00 |
|  7788 | SCOTT  | 3000.00 |
|  7844 | TURNER | 1500.00 |
|  7876 | ADAMS  | 1100.00 |
|  7900 | JAMES  |  950.00 |
|  7902 | FORD   | 3000.00 |
|  7934 | MILLER | 1300.00 |
+-------+--------+---------+

not 可以取非,主要用在 isin

  • is null
  • is not null
  • in
  • not in

like 称为模糊查询,支持%_下划线匹配

  • %匹配任意多个字符

  • _下划线:任意一个字符

    (% 是一个特殊的字符,_ 也是一个特殊的符号)

查询名字里含有o的员工:

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

+-------+
| ename |
+-------+
| JONES |
| SCOTT |
| FORD  |
+-------+

找出名字以T结尾的员工:

select ename from emp where ename like '%T';

+-------+
| ename |
+-------+
| SCOTT |
+-------+

找出名字以K开始的员工:

select ename from emp where ename like 'K%';

+-------+
| ename |
+-------+
| KING  |
+-------+

找出第二个字母是A的员工:

select ename from emp where ename like '_A%';

+--------+
| ename  |
+--------+
| WARD   |
| MARTIN |
| JAMES  |
+--------+

找出第三个字母是R的员工:

select ename from emp where ename like '__R%'; //注意有两个下划线。

+--------+
| ename  |
+--------+
| WARD   |
| MARTIN |
| TURNER |
| FORD   |
+--------+

创建一个t_student表

create table t_student(
    -> name varchar(255)
    -> );
mysql> insert into t_student values('zhangsan');

mysql> insert into t_student values('lisi');

mysql> insert into t_student values('wangwu');

mysql> insert into t_student values('jack_son');

mysql> commit;

+----------+
| name     |
+----------+
| zhangsan |
| lisi     |
| wangwu   |
| jack_son |
+----------+

找出t_student中名字有下划线的:

select name from t_student where name like '%_%'; //错误的,需要有转义字符\

select name from t_student where name like '%\_%';

+----------+
| name     |
+----------+
| jack_son |
+----------+

排序(升序、降序)

按照工资升序,找出员工名和薪资?

select ename,sal from emp order by sal; //order by默认是升序

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

注意:默认是升序。怎么指定升序或者降序呢?asc表示升序desc表示降序
select ename , sal from emp order by sal; // 升序
select ename , sal from emp order by sal asc;// 升序
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 |
+--------+---------+

查询员工名字和薪资,按照薪资升序,如果薪资一样,再按照名字的升序排列:

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

按照工资的降序排列,当工资相同的时候再按照名字的升序排列。
select ename,sal from emp order by sal desc;
select ename,sal from emp order by sal desc , ename asc;
注意:越靠前的字段越能起到主导作用。只有当前面的字段无法完成排序的时候,才会启用后面的字段。

+--------+---------+
| ename  | sal     |
+--------+---------+
| KING   | 5000.00 |
| FORD   | 3000.00 |
| SCOTT  | 3000.00 |
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
| WARD   | 1250.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| SMITH  |  800.00 |
+--------+---------+

找出工作岗位是SALESMAN的员工,并且要求按照薪资的降序排列。
select ename,job,sal from emp where job = 'SALESMAN' order by sal desc;

+--------+----------+---------+
| ename  | job      | sal     |
+--------+----------+---------+
| ALLEN  | SALESMAN | 1600.00 |
| TURNER | SALESMAN | 1500.00 |
| WARD   | SALESMAN | 1250.00 |
| MARTIN | SALESMAN | 1250.00 |
+--------+----------+---------+

也可以根据字段的位置进行排序:

select ename,sal from emp order by 2; //2表示第二列,第二列是sal

按照查询结果的第2列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 |
+--------+---------+

找出工资在1250-3000之间的员工信息,要求按薪资降序排列:

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

关键字顺序不能改变。

select 
	字段						3
from
	表名						1
where
	条件						2
order by
	....						4

order by是最后执行的。

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

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

单行处理函数的特点一个输入对应一个输出

单行处理函数相对的是:多行处理函数。(多行处理函数的特点:多个输入对应一个输出。)

单行处理函数常见的有哪些

Lower 转换小写

select lower(ename) from emp;

+--------------+
| lower(ename) |
+--------------+
| smith        |
| allen        |
| ward         |
| jones        |
| martin       |
| blake        |
| clark        |
| scott        |
| king         |
| turner       |
| adams        |
| james        |
| ford         |
| miller       |
+--------------+

select lower(ename) as ename from emp;

+--------+
| ename  |
+--------+
| smith  |
| allen  |
| ward   |
| jones  |
| martin |
| blake  |
| clark  |
| scott  |
| king   |
| turner |
| adams  |
| james  |
| ford   |
| miller |
+--------+

upper 转换大写

select * from t_student;

+----------+
| name     |
+----------+
| zhangsan |
| lisi     |
| wangwu   |
| jack_son |
+----------+

select upper(name) as name from t_student;

+----------+
| name     |
+----------+
| ZHANGSAN |
| LISI     |
| WANGWU   |
| JACK_SON |
+----------+

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

select substr(ename,1,1) as ename from emp; //起始下标从1开始。

+-------+
| ename |
+-------+
| S     |
| A     |
| W     |
| J     |
| M     |
| B     |
| C     |
| S     |
| K     |
| T     |
| A     |
| J     |
| F     |
| M     |
+-------+

找到员工名字第一个字母为A的员工信息:

  • 第一种方法:模糊查询

    select ename from emp where ename like 'A%';

    +-------+
    | ename |
    +-------+
    | ALLEN |
    | ADAMS |
    +-------+
    
  • 第二种方法:取子串

    select ename from emp where substr(ename,1,1) = 'A';

    +-------+
    | ename |
    +-------+
    | ALLEN |
    | ADAMS |
    +-------+
    

首字母大写?

concat函数进行字符串拼接

select concat(empno,ename) from emp;

+---------------------+
| concat(empno,ename) |
+---------------------+
| 7369SMITH           |
| 7499ALLEN           |
| 7521WARD            |
| 7566JONES           |
| 7654MARTIN          |
| 7698BLAKE           |
| 7782CLARK           |
| 7788SCOTT           |
| 7839KING            |
| 7844TURNER          |
| 7876ADAMS           |
| 7900JAMES           |
| 7902FORD            |
| 7934MILLER          |
+---------------------+

select concat(upper(substr(name,1,1)),substr(name,2,length(name)-1)) as name from t_student;

+----------+
| name     |
+----------+
| Zhangsan |
| Lisi     |
| Wangwu   |
| Jack_son |
+----------+

length 取长度

select length(ename) enamelength from emp;

+-------------+
| enamelength |
+-------------+
|           5 |
|           5 |
|           4 |
|           5 |
|           6 |
|           5 |
|           5 |
|           5 |
|           4 |
|           6 |
|           5 |
|           5 |
|           4 |
|           6 |
+-------------+

trim去空格

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

str_to_date 将字符串转为日期

date_format 格式化日期

format 设置千分位

round四舍五入

select 'abc' as diy from emp; //select 后面直接跟’字面量/字面值’

+-----+
| diy |
+-----+
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| 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(1236.567,0) as round from emp; //保留整数位

+-------+
| round |
+-------+
|  1237 |
|  1237 |
|  1237 |
|  1237 |
|  1237 |
|  1237 |
|  1237 |
|  1237 |
|  1237 |
|  1237 |
|  1237 |
|  1237 |
|  1237 |
|  1237 |
+-------+

select round(1236.567,1) as round from emp; //保留一位小数

+--------+
| round  |
+--------+
| 1236.6 |
| 1236.6 |
| 1236.6 |
| 1236.6 |
| 1236.6 |
| 1236.6 |
| 1236.6 |
| 1236.6 |
| 1236.6 |
| 1236.6 |
| 1236.6 |
| 1236.6 |
| 1236.6 |
| 1236.6 |
+--------+

select round(1236.567,-1) as round from emp; //保留到十位

+-------+
| round |
+-------+
|  1240 |
|  1240 |
|  1240 |
|  1240 |
|  1240 |
|  1240 |
|  1240 |
|  1240 |
|  1240 |
|  1240 |
|  1240 |
|  1240 |
|  1240 |
|  1240 |
+-------+

select round(1236.567,-2) as round from emp; //保留到百位

+-------+
| round |
+-------+
|  1200 |
|  1200 |
|  1200 |
|  1200 |
|  1200 |
|  1200 |
|  1200 |
|  1200 |
|  1200 |
|  1200 |
|  1200 |
|  1200 |
|  1200 |
|  1200 |
+-------+

rand() 生成随机数

select rand() from dept;

+---------------------+
| rand()              |
+---------------------+
|  0.5466490495453115 |
|  0.3445154804219636 |
| 0.08263028420752873 |
|  0.3796050105053978 |
+---------------------+

100以内的随机数:

select round(rand()*100,0) as result from dept;

+--------+
| result |
+--------+
|     72 |
|     46 |
|     15 |
|     38 |
+--------+

ifnull 可以将null转换成一个具体值

  • ifnull 是空处理函数,专门处理空的。

    在所有数据库当中,主要有NULL参与的数学运算,最终结果就是NULL

select ename,sal,comm,sal+comm as salcomm from emp;

+--------+---------+---------+---------+
| ename  | sal     | comm    | salcomm |
+--------+---------+---------+---------+
| SMITH  |  800.00 |    NULL |    NULL |
| ALLEN  | 1600.00 |  300.00 | 1900.00 |
| WARD   | 1250.00 |  500.00 | 1750.00 |
| JONES  | 2975.00 |    NULL |    NULL |
| MARTIN | 1250.00 | 1400.00 | 2650.00 |
| BLAKE  | 2850.00 |    NULL |    NULL |
| CLARK  | 2450.00 |    NULL |    NULL |
| SCOTT  | 3000.00 |    NULL |    NULL |
| KING   | 5000.00 |    NULL |    NULL |
| TURNER | 1500.00 |    0.00 | 1500.00 |
| ADAMS  | 1100.00 |    NULL |    NULL |
| JAMES  |  950.00 |    NULL |    NULL |
| FORD   | 3000.00 |    NULL |    NULL |
| MILLER | 1300.00 |    NULL |    NULL |
+--------+---------+---------+---------+

计算每个员工的年薪:

select ename,sal,(sal+comm)*12 as yeasal from emp; //错误的

+--------+---------+----------+
| ename  | sal     | yeasal   |
+--------+---------+----------+
| SMITH  |  800.00 |     NULL |
| ALLEN  | 1600.00 | 22800.00 |
| WARD   | 1250.00 | 21000.00 |
| JONES  | 2975.00 |     NULL |
| MARTIN | 1250.00 | 31800.00 |
| BLAKE  | 2850.00 |     NULL |
| CLARK  | 2450.00 |     NULL |
| SCOTT  | 3000.00 |     NULL |
| KING   | 5000.00 |     NULL |
| TURNER | 1500.00 | 18000.00 |
| ADAMS  | 1100.00 |     NULL |
| JAMES  |  950.00 |     NULL |
| FORD   | 3000.00 |     NULL |
| MILLER | 1300.00 |     NULL |
+--------+---------+----------+

注意:NULL只要参与运算,最终结果一定是NULL。 为了避免这个现象,需要使用ifnull函数。

ifnull函数用法:ifnull(数据,被当做哪个值),如果"数据"为NULL的时候,把这个数据结构当做“哪个值”。

select ename,sal,(sal+ifnull(comm,0))*12 as yeasal from emp;

+--------+---------+----------+
| ename  | sal     | yeasal   |
+--------+---------+----------+
| SMITH  |  800.00 |  9600.00 |
| ALLEN  | 1600.00 | 22800.00 |
| WARD   | 1250.00 | 21000.00 |
| JONES  | 2975.00 | 35700.00 |
| MARTIN | 1250.00 | 31800.00 |
| BLAKE  | 2850.00 | 34200.00 |
| CLARK  | 2450.00 | 29400.00 |
| SCOTT  | 3000.00 | 36000.00 |
| KING   | 5000.00 | 60000.00 |
| TURNER | 1500.00 | 18000.00 |
| ADAMS  | 1100.00 | 13200.00 |
| JAMES  |  950.00 | 11400.00 |
| FORD   | 3000.00 | 36000.00 |
| MILLER | 1300.00 | 15600.00 |
+--------+---------+----------+

case.. when.. then.. when.. then..else..end

当员工的工作岗位是MANAGER的时候,工资上调10%,当工作岗位是SALESMAN的时候,工资上调50%,其他正常(注意:不修改数据库,只是将查询结果显示为工资上调)

select ename,job,sal from emp;

+--------+-----------+---------+
| ename  | job       | sal     |
+--------+-----------+---------+
| SMITH  | CLERK     |  800.00 |
| ALLEN  | SALESMAN  | 1600.00 |
| WARD   | SALESMAN  | 1250.00 |
| JONES  | MANAGER   | 2975.00 |
| MARTIN | SALESMAN  | 1250.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 |
+--------+-----------+---------+

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

分组函数(多行处理函数)

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

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

记住:所有的分组函数都是对“某一组”数据进行操作的。


找出工资总和?
select sum(sal) from emp;

+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+

找出最高工资?
select max(sal) from emp;

+----------+
| max(sal) |
+----------+
|  5000.00 |
+----------+

找出最低工资?
select min(sal) from emp;

+----------+
| min(sal) |
+----------+
|   800.00 |
+----------+

找出平均工资?
select avg(sal) from emp;

+-------------+
| avg(sal)    |
+-------------+
| 2073.214286 |
+-------------+

14个工资全部加起来,然后除以14


找出总人数?
select count(*) from emp;

+----------+
| count(*) |
+----------+
|       14 |
+----------+

select count(ename) from emp;

+--------------+
| count(ename) |
+--------------+
|           14 |
+--------------+

分组函数一共5个。
分组函数还有另一个名字:多行处理函数。
多行处理函数的特点:输入多行,最终输出的结果是1行。

分组函数在使用的时候需要注意什么?

注意:第一点分组函数自动忽略NULL,你不需要提前对null进行处理。
select count(comm) from emp;

+-------------+
| count(comm) |
+-------------+
|           4 |
+-------------+

select sum(comm) from emp;

+-----------+
| sum(comm) |
+-----------+
|   2200.00 |
+-----------+

select sum(comm) from emp where comm is not null;// 不需要额外添加这个过滤条件。sum函数自动忽略NULL。

select avg(comm) from emp;

+------------+
| avg(comm)  |
+------------+
| 550.000000 |
+------------+

第二点:分组函数中count(*)count(具体字段)有什么区别?

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

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

count(具体字段) :表示统计该字段下所有不为null的元素的总数。

count(*):统计表当中的总行数。(只要有一行数据,count++),因为每一行的记录不可能都为null,一行数据中有一列不为null,则这行数据就是有效的。

第三点分组函数不能直接使用在where子句中。

找出比最低工资高的员工信息:

select ename,sal where sal > min(sal); //错误的

ERROR 1111 (HY000): Invalid use of group function

为什么分组函数不能直接使用在where后面?

因为分组函数在使用的时候必须先分组之后才能使用。where执行的时候,还没有分组。所以where后面不能出现分组函数。

select sum(sal) from emp;

为什么这个sum()没有分组可以使用呢?

从语法级别上看,select执行的时候,group by已经执行了,按照顺序就是selectgroup by后执行,符合==先分组后使用==原则。

select ename,sal from emp group by sal having > min(sal);

第四点:所有分组函数可以组合一起来使用。

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

+----------+----------+----------+-------------+------------+
| sum(sal) | max(sal) | min(sal) | avg(sal)    | count(sal) |
+----------+----------+----------+-------------+------------+
| 29025.00 |  5000.00 |   800.00 | 2073.214286 |         14 |
+----------+----------+----------+-------------+------------+

==!!!==分组查询

先对数据进行分组,再对每组数据进行操作。

格式:

select
...
from
...
group by
...
#计算每一个部门的工资和:
#计算每个岗位的平均薪资:
#计算每个工作岗位的最高薪资:
...

将之前的关键字全部组合在一起,看一下它们的执行顺序:

select
...			4
from
...			1
where
...			2
group by
...			3
order by
...			5
#以上关键字的顺序不能颠倒!!

执行顺序

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

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

实现思路:按照工作岗位分组,然后对工资求和。

mysql> select
    -> job,sum(sal)	3
    -> from
    -> emp			1
    -> group by
    -> job;			2
    
+-----------+----------+
| job       | sum(sal) |
+-----------+----------+
| ANALYST   |  6000.00 |
| CLERK     |  4150.00 |
| MANAGER   |  8275.00 |
| PRESIDENT |  5000.00 |
| SALESMAN  |  5600.00 |
+-----------+----------+

注意

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

+-------+-----------+----------+
| ename | job       | sum(sal) |
+-------+-----------+----------+
| SCOTT | ANALYST   |  6000.00 |
| SMITH | CLERK     |  4150.00 |
| JONES | MANAGER   |  8275.00 |
| KING  | PRESIDENT |  5000.00 |
| ALLEN | SALESMAN  |  5600.00 |
+-------+-----------+----------+

以上语句可以执行,但是没有意义,如果在Oracle中就报错了,Oracle的语法比MySQL严格(MySQL相对来说松散一些)

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

找出每个部门的最高薪资:

实现思路:先将部门分组,求每一组的最大值(select后面添加ename没有意义)

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

+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
|     20 |  3000.00 |
|     30 |  2850.00 |
+--------+----------+

找出每个部门,不同工作岗位的最高薪资:

技巧:两个字段联合成一个字段看(两个字段联合分组)。

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

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

使用having可以对分完组之后的数据进一步过滤。

注意having不能单独使用,having不能代替wherehaving必须和group by联合使用。

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

  • 第一步:找出每个部门的最高薪资:按照部门编号分组,求每一组最大值。

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

    +--------+---------+----------+
    | deptno | sal     | max(sal) |
    +--------+---------+----------+
    |     10 | 2450.00 |  5000.00 |
    |     20 |  800.00 |  3000.00 |
    |     30 | 1600.00 |  2850.00 |
    +--------+---------+----------+
    
  • 第二步:要求显示最高薪资大于3000:

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

    +--------+----------+
    | deptno | max(sal) |
    +--------+----------+
    |     10 |  5000.00 |
    +--------+----------+
    

    思考 :以上的sql语句效率是不是点低?

    可以先找出大于3000的,再进行分组。

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

    +--------+----------+
    | deptno | max(sal) |
    +--------+----------+
    |     10 |  5000.00 |
    +--------+----------+
    

优化策略wherehaving,优先选择wherewhere实在完成不了,再选择having

where完成不了的

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

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

+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+

总结一个完整的DQL(数据库查询语句)语句的执行顺序

select
	..		5
from	
	..		1
where
	..		2
group by
	..		3
having
	..		4
order by
	..		6
  1. 先确定是哪张表
  2. 然后where条件筛选出有价值的数据
  3. 对这些有价值的数据进行分组
  4. 分组之后可以使用having继续筛选
  5. select查询出来
  6. 最后排序输出!

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

select job,avg(sal) as avgsal from emp where job <> 'MANAGER' group by job having avg(sal) > 1500 order by avg(sal) desc;

+-----------+-------------+
| job       | avgsal      |
+-----------+-------------+
| PRESIDENT | 5000.000000 |
| ANALYST   | 3000.000000 |
+-----------+-------------+
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

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

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

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值