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 | +--------+------------+----------+
这种方式的缺点:
- 效率差
- 可读性差
在实际开发中不建议,实际开发中还是用字段的形式查询,提高效率,增强可读性。
给查询的列起别名:
使用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
可以取非,主要用在 is
或 in
中
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
已经执行了,按照顺序就是select
在 group 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
#以上关键字的顺序不能颠倒!!
执行顺序:
- from
- where
- group by
- select
- 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
不能代替where
,having
必须和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 | +--------+----------+
优化策略:where
和having
,优先选择where
,where
实在完成不了,再选择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
- 先确定是哪张表
- 然后where条件筛选出有价值的数据
- 对这些有价值的数据进行分组
- 分组之后可以使用having继续筛选
- select查询出来
- 最后排序输出!
找出每个岗位的平均薪资,要求显示平均薪资大于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 |
+-----------+-------------+