福州网站推广排名,创意设计公司业务范围,免费信息发布网站有哪些,wordpress文章图片显示不出DQL
导入数据
首先使用use database进入数据库中#xff0c;然后使用命令
source D:\mysql_learning\mysql_learning\document\bjpowernode.sql注意文件名不能有双引号#xff0c;命令结尾没有分号。
SQL脚本
.sql文件是SQL脚本文件#xff0c;它里面的内容都是SQL语句…DQL
导入数据
首先使用use database进入数据库中然后使用命令
source D:\mysql_learning\mysql_learning\document\bjpowernode.sql注意文件名不能有双引号命令结尾没有分号。
SQL脚本
.sql文件是SQL脚本文件它里面的内容都是SQL语句当调用这个文件的时候就会执行文件里面的所有语句。
批量的执行SQL语句可以使用sql脚本文件。
在mysql当中怎么执行sql脚本呢
sourse 脚本路径你在实际的工作中第一天到了公司项目经理会给你一个xxx.sql文件你执行这个脚本文件你电脑上的数据库数据就有了
查看表结构
mysql show tables;
------------------------
| Tables_in_bjpowernnode |
------------------------
| dept |
| emp |
| salgrade |
------------------------查看表中的数据
select * from 表名;mysql select * from emp;
----------------------------------------------------------------------
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
----------------------------------------------------------------------
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
----------------------------------------------------------------------不看表中的数据只看表中的结构
desc 表名;mysql desc emp;
--------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
--------------------------------------------------
| EMPNO | int | NO | PRI | NULL | | # varchar就是string
| ENAME | varchar(10) | YES | | NULL | |
| JOB | varchar(9) | YES | | NULL | |
| MGR | int | YES | | NULL | |
| HIREDATE | date | YES | | NULL | |
| SAL | double(7,2) | YES | | NULL | |
| COMM | double(7,2) | YES | | NULL | |
| DEPTNO | int | YES | | NULL | |
--------------------------------------------------简单查询 简单查询都是和select相关的操作只要是和select相关的操作都不会对原表中的数据进行修改只会进行查询操作。 查询一个字段
select 字段名 from 表名;查询多个字段
select 字段名1字段名2... from 表名;# 用逗号将多个字段名分隔开查询所有字段
# 一种方式是
select a,b,c,d.... from 表名;
#另外一种方式是
select * from 表名;但是第二种方式一般效率较低不是很推荐。
mysql select * from emp;
----------------------------------------------------------------------
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
----------------------------------------------------------------------
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
----------------------------------------------------------------------
14 rows in set (0.00 sec)mysql select Job,Sal from emp;
--------------------
| Job | Sal |
--------------------
| CLERK | 800.00 |
| SALESMAN | 1600.00 |
| SALESMAN | 1250.00 |
| MANAGER | 2975.00 |
| SALESMAN | 1250.00 |
| MANAGER | 2850.00 |
| MANAGER | 2450.00 |
| ANALYST | 3000.00 |
| PRESIDENT | 5000.00 |
| SALESMAN | 1500.00 |
| CLERK | 1100.00 |
| CLERK | 950.00 |
| ANALYST | 3000.00 |
| CLERK | 1300.00 |
--------------------
14 rows in set (0.00 sec)给查询的列取别名按照别名来进行显示
select 字段名 as 别名 from 表名;mysql select Job,Sal as salary from emp;
--------------------
| Job | salary |
--------------------
| CLERK | 800.00 |
| SALESMAN | 1600.00 |
| SALESMAN | 1250.00 |
| MANAGER | 2975.00 |
| SALESMAN | 1250.00 |
| MANAGER | 2850.00 |
| MANAGER | 2450.00 |
| ANALYST | 3000.00 |
| PRESIDENT | 5000.00 |
| SALESMAN | 1500.00 |
| CLERK | 1100.00 |
| CLERK | 950.00 |
| ANALYST | 3000.00 |
| CLERK | 1300.00 |
--------------------
14 rows in set (0.00 sec)注意这里的取别名只会对显示的列显示别名的效果不会对原本的表中的列名进行修改。
同时as关键字是可以被省略的用空格代替如果别名中的含有空格需要用单引号将别名括起来当然使用双引号也行但是oracle中不支持双引号进行这样的操作因此还是统一使用单引号。
列参进行数学运算
字段是支持直接进行数学运算的例如
mysql select ename, sal * 12 as years salary from emp;
-----------------------
| ename | years salary |
-----------------------
| SMITH | 9600.00 |
| ALLEN | 19200.00 |
| WARD | 15000.00 |
| JONES | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE | 34200.00 |
| CLARK | 29400.00 |
| SCOTT | 36000.00 |
| KING | 60000.00 |
| TURNER | 18000.00 |
| ADAMS | 13200.00 |
| JAMES | 11400.00 |
| FORD | 36000.00 |
| MILLER | 15600.00 |
-----------------------
14 rows in set (0.01 sec)这样久能实现查看到年薪是多少了同时还取了个别名。
条件查询 查询出符合条件的内容 select 字段名1字段名2...from 表名 where 条件;主要使用的一些条件
等于
mysql select ename,job,sal from emp where sal1250;# 找到工资为1250的人
---------------------------
| ename | job | sal |
---------------------------
| WARD | SALESMAN | 1250.00 |
| MARTIN | SALESMAN | 1250.00 |
---------------------------
2 rows in set (0.00 sec)# 当然也支持数学运算
mysql select ename,job,sal from emp where sal*810000;
---------------------------
| ename | job | sal |
---------------------------
| WARD | SALESMAN | 1250.00 |
| MARTIN | SALESMAN | 1250.00 |
---------------------------
2 rows in set (0.00 sec)#也是支持查找字符串的
mysql select ename,job,sal from emp where enameSMITH;
----------------------
| ename | job | sal |
----------------------
| SMITH | CLERK | 800.00 |
----------------------
1 row in set (0.00 sec)不等于!或者
mysql select ename,job,sal from emp where sal!1250;
----------------------------
| ename | job | sal |
----------------------------
| SMITH | CLERK | 800.00 |
| ALLEN | SALESMAN | 1600.00 |
| JONES | MANAGER | 2975.00 |
| BLAKE | MANAGER | 2850.00 |
| CLARK | MANAGER | 2450.00 |
| SCOTT | ANALYST | 3000.00 |
| KING | PRESIDENT | 5000.00 |
| TURNER | SALESMAN | 1500.00 |
| ADAMS | CLERK | 1100.00 |
| JAMES | CLERK | 950.00 |
| FORD | ANALYST | 3000.00 |
| MILLER | CLERK | 1300.00 |
----------------------------
12 rows in set (0.00 sec)小于大于大于等于小于等于
上面的都是类似的
区间 between...and... 严格的要求左小右大同时between…and…是闭区间包括两端的值() and ()
mysql select ename,job,sal from emp where sal950 and sal3000;
---------------------------
| ename | job | sal |
---------------------------
| ALLEN | SALESMAN | 1600.00 |
| WARD | SALESMAN | 1250.00 |
| JONES | MANAGER | 2975.00 |
| MARTIN | SALESMAN | 1250.00 |
| BLAKE | MANAGER | 2850.00 |
| CLARK | MANAGER | 2450.00 |
| TURNER | SALESMAN | 1500.00 |
| ADAMS | CLERK | 1100.00 |
| JAMES | CLERK | 950.00 |
| MILLER | CLERK | 1300.00 |
---------------------------
10 rows in set (0.01 sec)is null /is not null
null在mysql中表示没有如果用或者表示查找为值为空的那样是找不到null元素的
# is not null
mysql select ename,job,comm from emp where COMM is not null;
---------------------------
| ename | job | comm |
---------------------------
| ALLEN | SALESMAN | 300.00 |
| WARD | SALESMAN | 500.00 |
| MARTIN | SALESMAN | 1400.00 |
| TURNER | SALESMAN | 0.00 |
---------------------------
4 rows in set (0.00 sec)# is null
mysql select ename,job,comm from emp where COMM is null;
-------------------------
| ename | job | comm |
-------------------------
| SMITH | CLERK | NULL |
| JONES | MANAGER | NULL |
| BLAKE | MANAGER | NULL |
| CLARK | MANAGER | NULL |
| SCOTT | ANALYST | NULL |
| KING | PRESIDENT | NULL |
| ADAMS | CLERK | NULL |
| JAMES | CLERK | NULL |
| FORD | ANALYST | NULL |
| MILLER | CLERK | NULL |
-------------------------
10 rows in set (0.00 sec)and/or/not
and表示并且or表示或者and和or同时出现的时候会有优先级问题and的优先级更高如果想优先使用or就用小括号括起来not 就是表示否一般和is 和 in 一起用 is not null / not in
mysql select ename,job,sal from emp where jobMANAGER or jobSALESMAN;
---------------------------
| ename | job | sal |
---------------------------
| ALLEN | SALESMAN | 1600.00 |
| WARD | SALESMAN | 1250.00 |
| JONES | MANAGER | 2975.00 |
| MARTIN | SALESMAN | 1250.00 |
| BLAKE | MANAGER | 2850.00 |
| CLARK | MANAGER | 2450.00 |
| TURNER | SALESMAN | 1500.00 |
---------------------------
7 rows in set (0.00 sec)in
in就是多个or的集合 not in 不在这个范围中
mysql select empno,ename,job from emp where job in(MANAGER, SALESMAN);
-------------------------
| empno | ename | job |
-------------------------
| 7499 | ALLEN | SALESMAN |
| 7521 | WARD | SALESMAN |
| 7566 | JONES | MANAGER |
| 7654 | MARTIN | SALESMAN |
| 7698 | BLAKE | MANAGER |
| 7782 | CLARK | MANAGER |
| 7844 | TURNER | SALESMAN |
-------------------------
7 rows in set (0.00 sec)mysql select ename,sal from emp where sal in(800, 5000);# 只会找到sal500和sal5000的信息
----------------
| ename | sal |
----------------
| SMITH | 800.00 |
| KING | 5000.00 |
----------------
2 rows in set (0.00 sec)括号里面的值不能理解成区间范围只能理解成可选项。
like % _
模糊查找
%表示若干个字符_表示一个字符如果查找的字符串中含有这两个关键字符需要用转义字符\
例如查找名字以T结尾的成员信息
mysql select ename from emp where ename like %T;
-------
| ename |
-------
| SCOTT |
-------
1 row in set (0.00 sec)排序
查询所有员工薪资排序
按照一个字段排序
select 字段名1字段名2...
from 表名
order by 字段名;mysql select ename,sal from emp order by sal;
-----------------
| ename | sal |
-----------------
| SMITH | 800.00 |
| JAMES | 950.00 |
| ADAMS | 1100.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN | 1600.00 |
| CLARK | 2450.00 |
| BLAKE | 2850.00 |
| JONES | 2975.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| KING | 5000.00 |
-----------------
14 rows in set (0.00 sec)通过发现可以知道默认是从上到下升序排列
指定降序和升序排列
降序
select 字段名1字段名2...
from 表名
order by 字段名 desc;升序
select 字段名1字段名2...
from 表名
order by 字段名 asc;关键字asc和desc分别代表ascend和descend帮助记忆。
按照多个字段排序
select 字段名1字段名2...
from 表名
order by 字段名1 asc/desc,字段名2 asc/desc ...;
# 如果按照字段名1进行排序遇到相等的情况就按照字段名2进行排序比如说 查询员工名字和薪资要求按照薪资升序如果薪资一样的话再按照名字升序排列。 mysql select- ename,sal- from- emp- order by- sal asc, ename asc;
-----------------
| ename | sal |
-----------------
| SMITH | 800.00 |
| JAMES | 950.00 |
| ADAMS | 1100.00 |
| MARTIN | 1250.00 |
| WARD | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN | 1600.00 |
| CLARK | 2450.00 |
| BLAKE | 2850.00 |
| JONES | 2975.00 |
| FORD | 3000.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
-----------------
14 rows in set (0.00 sec)mysql select- ename,sal- from- emp- order by- sal asc, ename asc;# sal在前起主导只有sal相等的时候才会考虑启用ename排序。综合测试找出工资在1250到3000之间的员工信息要求按照薪资降序排列。
mysql select ename,sal- from emp- where sal1250 and sal3000- order by- sal desc;
-----------------
| ename | sal |
-----------------
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
-----------------
10 rows in set (0.01 sec)注意
关键字顺序不能变select...from...where...order by... ;以上语句的执行顺序必须掌握 第一步from 第二步where 第三步select 第四步order by排序总是在最后执行
数据处理函数/单行处理函数
也被称为单行处理函数一个输入对应一个输出。
lower转小写
mysql select lower(ename) from emp;
# 这里也可以取别名
# select lower(ename) as ename from emp;
--------------
| lower(ename) |
--------------
| smith |
| allen |
| ward |
| jones |
| martin |
| blake |
| clark |
| scott |
| king |
| turner |
| adams |
| james |
| ford |
| miller |
--------------
14 rows in set (0.01 sec)--------------------------------
mysql select lower(ename) as ename,lower(Job) as job from emp;# 也可以处理多列
-------------------
| ename | job |
-------------------
| smith | clerk |
| allen | salesman |
| ward | salesman |
| jones | manager |
| martin | salesman |
| blake | manager |
| clark | manager |
| scott | analyst |
| king | president |
| turner | salesman |
| adams | clerk |
| james | clerk |
| ford | analyst |
| miller | clerk |
-------------------
14 rows in set (0.00 sec)upper转大写
和小写一个用法
substr提取子串
substr(被截取的字符串起始下标截取的长度)注意SQL语句中的下标是从1开始的
例如我们用substr提取出字符串中的首字母来判断首字母是不是‘A’,并且将相关信息提取出来。
mysql select ename ,job,sal from emp where substr(ename,1,1)A;
--------------------------
| ename | job | sal |
--------------------------
| ALLEN | SALESMAN | 1600.00 |
| ADAMS | CLERK | 1100.00 |
--------------------------
2 rows in set (0.01 sec)concat拼接字符串
mysql select concat(empno,ename,job) from emp;# 这里发现是可以拼接多个字符串的
-------------------------
| concat(empno,ename,job) |
-------------------------
| 7369SMITHCLERK |
| 7499ALLENSALESMAN |
| 7521WARDSALESMAN |
| 7566JONESMANAGER |
| 7654MARTINSALESMAN |
| 7698BLAKEMANAGER |
| 7782CLARKMANAGER |
| 7788SCOTTANALYST |
| 7839KINGPRESIDENT |
| 7844TURNERSALESMAN |
| 7876ADAMSCLERK |
| 7900JAMESCLERK |
| 7902FORDANALYST |
| 7934MILLERCLERK |
-------------------------
14 rows in set (0.00 sec)length取长度
mysql select length(ename) enamelength from emp;
-------------
| enamelength |
-------------
| 5 |
| 5 |
| 4 |
| 5 |
| 6 |
| 5 |
| 5 |
| 5 |
| 4 |
| 6 |
| 5 |
| 5 |
| 4 |
| 6 |
-------------
14 rows in set (0.00 sec)trim去空格
也是去除字符串的前后空白
mysql select * from emp where enametrim( KING );
------------------------------------------------------------------
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
------------------------------------------------------------------
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
------------------------------------------------------------------
1 row in set (0.00 sec)但是
mysql select * from emp where enametrim( K I NG );
Empty set (0.00 sec)这样是查询不到的。
round四舍五入
select abc from emp; // select后面直接跟“字面量/字面值”
mysql select abc from emp;
-----
| abc |
-----
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
-----mysql select abc from emp;ERROR 1054 (42S22): Unknown column abc in field list这样肯定报错因为会把abc当做一个字段的名字去emp表中找abc字段去了。select 1000 as num from emp; // 1000 也是被当做一个字面量/字面值。------| num |------| 1000 || 1000 || 1000 || 1000 || 1000 || 1000 || 1000 || 1000 || 1000 || 1000 || 1000 || 1000 || 1000 || 1000 |------结论select后面可以跟某个表的字段名可以等同看做变量名也可以跟字面量/字面值数据。 select round(1245.6735,0) as num from emp;# 将1245.6735保留到整数位
------
| num |
------
| 1246 |
| 1246 |
| 1246 |
| 1246 |
| 1246 |
| 1246 |
| 1246 |
| 1246 |
| 1246 |
| 1246 |
| 1246 |
| 1246 |
| 1246 |
| 1246 |
------
select round(1245.6735,1) as num from emp;# 保留一位小数
--------
| num |
--------
| 1245.7 |
| 1245.7 |
| 1245.7 |
| 1245.7 |
| 1245.7 |
| 1245.7 |
| 1245.7 |
| 1245.7 |
| 1245.7 |
| 1245.7 |
| 1245.7 |
| 1245.7 |
| 1245.7 |
| 1245.7 |
--------
依次类推
select round(1245.6735,-1) as num from emp;# -1那么就是保留到十位
------
| num |
------
| 1250 |
| 1250 |
| 1250 |
| 1250 |
| 1250 |
| 1250 |
| 1250 |
| 1250 |
| 1250 |
| 1250 |
| 1250 |
| 1250 |
| 1250 |
| 1250 |
------rand生成随机数
mysql select rand() as random from emp;
---------------------
| random |
---------------------
| 0.6779789288323176 |
| 0.03282456009911798 |
| 0.13018604473228199 |
| 0.552456574097701 |
| 0.3717247670253038 |
| 0.20125414356706117 |
| 0.8910964474930395 |
| 0.8517198374976589 |
| 0.5853098757428209 |
| 0.3713898969547729 |
| 0.10101988827904676 |
| 0.3909297812645601 |
| 0.6515892722193052 |
| 0.08515704803649062 |
---------------------可以结合round函数生成整数随机数
mysql select round(rand()*100,0) as random from emp;# 生成100以内的随机数
--------
| random |
--------
| 47 |
| 10 |
| 9 |
| 13 |
| 38 |
| 50 |
| 39 |
| 42 |
| 95 |
| 47 |
| 52 |
| 20 |
| 43 |
| 53 |
--------ifnull 处理null值
在SQL语句中规定所有数据与null进行数学运算之后的结果只能是null
mysql select * from emp;
----------------------------------------------------------------------
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
----------------------------------------------------------------------
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
----------------------------------------------------------------------
14 rows in set (0.00 sec)mysql select ename,salcomm as salcomm from emp;# 会发现所有sal中的数据加上null都变成了null
-----------------
| ename | salcomm |
-----------------
| SMITH | NULL |
| ALLEN | 1900.00 |
| WARD | 1750.00 |
| JONES | NULL |
| MARTIN | 2650.00 |
| BLAKE | NULL |
| CLARK | NULL |
| SCOTT | NULL |
| KING | NULL |
| TURNER | 1500.00 |
| ADAMS | NULL |
| JAMES | NULL |
| FORD | NULL |
| MILLER | NULL |
-----------------但是有时候我们并不想这么做我们认为有时候null代表着0那么这个时候该怎么做呢
使用ifnull函数ifnull数据null被当作哪个值
mysql select ename,salifnull(comm,0) as salcomm from emp;# 这里我们将null当作0
-----------------
| ename | salcomm |
-----------------
| SMITH | 800.00 |
| ALLEN | 1900.00 |
| WARD | 1750.00 |
| JONES | 2975.00 |
| MARTIN | 2650.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| FORD | 3000.00 |
| MILLER | 1300.00 |
-----------------case…when…then…when…then…else…end模拟if-else语句
当员工的工作岗位是MANAGER的时候工资上调10%当工作岗位是SALESMAN的时候工资上调50%,其它正常。 注意不修改数据库只是将查询结果显示为工资上调
case(变量)when(变量满足条件1)then(如果满足条件1后的执行)when(变量满足条件2)then...else...end
select ename,job,sal as oldsal,(case job when MANAGER then sal*1.1 when SALESMAN then sal*1.5 else sal end) as newsalfrom emp;
-------------------------------------
| ename | job | oldsal | newsal |
-------------------------------------
| SMITH | CLERK | 800.00 | 800.00 |
| ALLEN | SALESMAN | 1600.00 | 2400.00 |
| WARD | SALESMAN | 1250.00 | 1875.00 |
| JONES | MANAGER | 2975.00 | 3272.50 |
| MARTIN | SALESMAN | 1250.00 | 1875.00 |
| BLAKE | MANAGER | 2850.00 | 3135.00 |
| CLARK | MANAGER | 2450.00 | 2695.00 |
| SCOTT | ANALYST | 3000.00 | 3000.00 |
| KING | PRESIDENT | 5000.00 | 5000.00 |
| TURNER | SALESMAN | 1500.00 | 2250.00 |
| ADAMS | CLERK | 1100.00 | 1100.00 |
| JAMES | CLERK | 950.00 | 950.00 |
| FORD | ANALYST | 3000.00 | 3000.00 |
| MILLER | CLERK | 1300.00 | 1300.00 |
-------------------------------------
14 rows in set (0.00 sec) str_to_date
将字符串varchar类型转换成date类型。
date_format
将date类型转换成具有一定格式的varchar字符串类型。
分组函数/多行处理函数
输入多行输出一行。
count 计数sum 求和avg 平均值max 最大值min 最小值 注意 分组函数在使用的时候必须先进行分组然后才能用。 如果你没有对数据进行分组整张表默认为一组。 mysql select max(sal) from emp;# 求最大值
----------
| max(sal) |
----------
| 5000.00 |
----------
1 row in set (0.01 sec)mysql select min(sal) from emp;# 求最小值
----------
| min(sal) |
----------
| 800.00 |
----------
1 row in set (0.01 sec)mysql select avg(sal) from emp;# 求平均值
-------------
| avg(sal) |
-------------
| 2073.214286 |
-------------
1 row in set (0.00 sec)mysql select sum(sal) from emp;# 求总和
----------
| sum(sal) |
----------
| 29025.00 |
----------
1 row in set (0.00 sec)mysql select count(sal) from emp;# 求个数
------------
| count(sal) |
------------
| 14 |
------------
1 row in set (0.00 sec)值得注意的是分组函数有几个注意事项
分组函数自动忽略掉null值我们不需要提前对null值进行处理例如
mysql select count(comm) from emp;
-------------
| count(comm) |
-------------
| 4 |
-------------分组函数中count(*)与count(具体字段)的区别
count(*)会统计表中的总行数。
count(具体字段)会忽略字段中的null。
因为每一行记录不可能都为NULL一行数据中有一列不为NULL则这行数据就是有效的。
分组函数不能用在where子句中分组查询中会解释
select ename,job,sal from emp where max(sal);
ERROR 1111 (HY000): Invalid use of group function所有的分组函数可以组合在一起用
mysql select sum(sal),min(sal),max(sal),avg(sal),count(*) from emp;
-----------------------------------------------------
| sum(sal) | min(sal) | max(sal) | avg(sal) | count(*) |
-----------------------------------------------------
| 29025.00 | 800.00 | 5000.00 | 2073.214286 | 14 |
-----------------------------------------------------
1 row in set (0.00 sec)分组查询
在实际的应用中可能有这样的需求需要先进行分组然后对每一组的数据进行操作。
例如我们要计算每个部门的薪资和每个工作岗位的平均薪资这都是要进行分组分组之后才能计算
select...from...group by...结合已经学到的关键字可以知道有代码顺序
select
...
from
...
where
...
group by
...
order by
...上面的执行顺序应该是
fromwheregroup byselectorder by
结合分组函数的使用注意事项分组函数必须先分组才能执行那么where子句中如果有分组函数但是group by是在where语句执行完之后才会执行的因此执行where语句中的分组函数那个时候是还没有完成分组的于是就会报错。
单个分组
找出每个工作岗位的工资和。
select job,sum(sal)
fromemp
group byjob;---------------------
| job | sum(sal) |
---------------------
| CLERK | 4150.00 |
| SALESMAN | 5600.00 |
| MANAGER | 8275.00 |
| ANALYST | 6000.00 |
| PRESIDENT | 5000.00 |
---------------------那么上面的代码中select后面还能加上别的字段名吗
答案是不可以的那样会报错。如果想要找出每个工作岗位的工资最高的人是谁显示名字啥的需要结合后面学习到的知识才能运行。 在有的时候上面说的情况不会报错但是那样是没有意义的。 重点结论 在一条select语句当中如果有group by语句的话 select后面只能跟参加分组的字段以及分组函数。 其它的一律不能跟。 多个分组
找出“每个部门不同工作岗位”的最高薪资
mysql select- deptno,job,max(sal)- from- emp- group by- deptno,job# 根据多个选项进行分组- order by- deptno;
-----------------------------
| deptno | job | max(sal) |
-----------------------------
| 10 | CLERK | 1300.00 |
| 10 | MANAGER | 2450.00 |
| 10 | PRESIDENT | 5000.00 |
| 20 | ANALYST | 3000.00 |
| 20 | CLERK | 1100.00 |
| 20 | MANAGER | 2975.00 |
| 30 | CLERK | 950.00 |
| 30 | MANAGER | 2850.00 |
| 30 | SALESMAN | 1600.00 |
-----------------------------
9 rows in set (0.00 sec)having语句进行进一步过滤
找出每个部门最高薪资要求显示最高薪资大于3000的
首先我们找到每个部门的最高薪资
mysql select deptno,max(sal) from emp group by deptno;
------------------
| deptno | max(sal) |
------------------
| 20 | 3000.00 |
| 30 | 2850.00 |
| 10 | 5000.00 |
------------------然后我们再对最高薪资进行过滤
mysql select deptno,max(sal) from emp group by deptno having max(sal)3000;
------------------
| deptno | max(sal) |
------------------
| 10 | 5000.00 |
------------------思考这样做效率是不是很低
实际上我们可以先把所有大于3000的人找出来再分组
mysql select deptno,max(sal) from emp where sal3000 group by deptno;
------------------
| deptno | max(sal) |
------------------
| 10 | 5000.00 |
------------------但是有的时候也会存在where无法过滤的情况
找出每个部门平均薪资要求显示平均薪资高于2500的。
这个例子中我们就必须先找出平均薪资了是无法用where过滤的
mysql select deptno,avg(sal) from emp group by deptno having avg(sal)2500;
---------------------
| deptno | avg(sal) |
---------------------
| 10 | 2916.666667 |
---------------------distinct关键字
distinct关键字是用来对查询记录进行重复处理
mysql select job from emp;
-----------
| job |
-----------
| CLERK |
| SALESMAN |
| SALESMAN |
| MANAGER |
| SALESMAN |
| MANAGER |
| MANAGER |
| ANALYST |
| PRESIDENT |
| SALESMAN |
| CLERK |
| CLERK |
| ANALYST |
| CLERK |
-----------例如这里查询job字段会有重复的job出现这个时候可以用distinct关键字进行去重
mysql select distinct job from emp;
-----------
| job |
-----------
| CLERK |
| SALESMAN |
| MANAGER |
| ANALYST |
| PRESIDENT |
-----------注意distinct关键字只能出现在字段名之前不能夹在字段名中间。
mysql select ename,distinct job from emp;这样是错误的。
当distinct关键字后面有多个字段名的时候它会实现联合这多个字段名进行查询结果的去重。
mysql select deptno,job from emp;
-------------------
| deptno | job |
-------------------
| 20 | CLERK |
| 30 | SALESMAN |
| 30 | SALESMAN |
| 20 | MANAGER |
| 30 | SALESMAN |
| 30 | MANAGER |
| 10 | MANAGER |
| 20 | ANALYST |
| 10 | PRESIDENT |
| 30 | SALESMAN |
| 20 | CLERK |
| 30 | CLERK |
| 20 | ANALYST |
| 10 | CLERK |
-------------------mysql select distinct deptno,job from emp;
-------------------
| deptno | job |
-------------------
| 20 | CLERK |
| 30 | SALESMAN |
| 20 | MANAGER |
| 30 | MANAGER |
| 10 | MANAGER |
| 20 | ANALYST |
| 10 | PRESIDENT |
| 30 | CLERK |
| 10 | CLERK |
-------------------统计job的种类数量
mysql select count(distinct job) from emp;
---------------------
| count(distinct job) |
---------------------
| 5 |
---------------------小结
select ...from...where...group by...having...order by...上面是已经学到的整个框架顺序是不能变的。
执行顺序大概是
from 从某张表中查询数据where 先经过where条件筛选出有价值的数据。group by 对这些有价值的数据进行分组。having 分组之后可以使用having继续筛选。select 使用select查询出来。order by 最后排序输出
找出每个岗位的平均薪资要求显示平均薪资大于1500的除MANAGER岗位之外要求按照平均薪资降序排。
mysql select job ,avg(sal) as avgsalfrom empwhere job!MANAGERgroup by jobhavingavg(sal)1500order by avgsal desc;
------------------------
| job | avgsal |
------------------------
| PRESIDENT | 5000.000000 |
| ANALYST | 3000.000000 |
------------------------连接查询 从一张表中单独查询称为单表查询。 emp表和dept表联合起来查询数据从emp表中取员工名字从dept表中取部门名字。 这种跨表查询多张表联合起来查询数据被称为连接查询。 根据表连接的方式分类 内连接 等值连接 非等值连接 自连接 外连接 左外连接左连接 右外连接右连接 全连接 笛卡尔积现象
mysql select * from emp;
----------------------------------------------------------------------
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
----------------------------------------------------------------------
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
----------------------------------------------------------------------
14 rows in set (0.00 sec)mysql select * from dept;
------------------------------
| DEPTNO | DNAME | LOC |
------------------------------
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
------------------------------
4 rows in set (0.00 sec)当两张表进行无条件的连接查询
mysql select ename,dname from emp,dept;
--------------------
| ename | dname |
--------------------
| SMITH | OPERATIONS |
| SMITH | SALES |
| SMITH | RESEARCH |
| SMITH | ACCOUNTING |
| ALLEN | OPERATIONS |
| ALLEN | SALES |
| ALLEN | RESEARCH |
| ALLEN | ACCOUNTING |
| WARD | OPERATIONS |
| WARD | SALES |
| WARD | RESEARCH |
| WARD | ACCOUNTING |
| JONES | OPERATIONS |
| JONES | SALES |
| JONES | RESEARCH |
| JONES | ACCOUNTING |
| MARTIN | OPERATIONS |
| MARTIN | SALES |
| MARTIN | RESEARCH |
| MARTIN | ACCOUNTING |
| BLAKE | OPERATIONS |
| BLAKE | SALES |
| BLAKE | RESEARCH |
| BLAKE | ACCOUNTING |
| CLARK | OPERATIONS |
| CLARK | SALES |
| CLARK | RESEARCH |
| CLARK | ACCOUNTING |
| SCOTT | OPERATIONS |
| SCOTT | SALES |
| SCOTT | RESEARCH |
| SCOTT | ACCOUNTING |
| KING | OPERATIONS |
| KING | SALES |
| KING | RESEARCH |
| KING | ACCOUNTING |
| TURNER | OPERATIONS |
| TURNER | SALES |
| TURNER | RESEARCH |
| TURNER | ACCOUNTING |
| ADAMS | OPERATIONS |
| ADAMS | SALES |
| ADAMS | RESEARCH |
| ADAMS | ACCOUNTING |
| JAMES | OPERATIONS |
| JAMES | SALES |
| JAMES | RESEARCH |
| JAMES | ACCOUNTING |
| FORD | OPERATIONS |
| FORD | SALES |
| FORD | RESEARCH |
| FORD | ACCOUNTING |
| MILLER | OPERATIONS |
| MILLER | SALES |
| MILLER | RESEARCH |
| MILLER | ACCOUNTING |
--------------------
56 rows in set (0.01 sec)我们发现他其实是拿emp表中的每个成员分别和dept中的四个成员连接最终也就是有了4*14行的数据了但是这里面有一些数据是没有意义的因为我们连接两个表进行查询是为了通过两个表具有相同的元素进行关联查询即通过deptno关联起来两个表但是结果是具有冗余数据的这种现象就是笛卡尔积现象。 避免笛卡尔积现象
连接时加条件满足这个条件的记录被筛选出来
selectename,dname# 它既会去emp中找ename也会去dept中找enamedname同理因此效率并不是很高# 提高效率可以这样写emp.ename,dept.dname
from emp,dept
where emp.deptnodept.deptno;--------------------
| ename | dname |
--------------------
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
--------------------这样就能实现两个表通过deptno产生关联连接之后再进行查询。 最终查询的结果条数是14条但是匹配的过程中匹配的次数减少了吗 还是56次只不过进行了四选一。次数没有减少。 注意通过笛卡尔积现象得出表的连接次数越多效率越低尽量避免表的 连接次数。 同时一种书写习惯是给表取别名
# 表起别名。很重要。效率问题。select e.ename,d.dname from emp e, dept dwheree.deptno d.deptno; //SQL92语法。内连接与等值连接
条件是等量关系所以被称为等值连接。
# SQL92语法。
select e.ename,d.dname from emp e, dept dwheree.deptno d.deptno;sql92的缺点结构不清晰表的连接条件和后期进一步筛选的条件都放到了where后面。
# SQL99语法
select e.ename,d.dnamefromemp einner join #inner可以省略带着inner可读性更好一眼就能看出来是内连接dept done.deptno d.deptno;# 条件是等量关系所以被称为等值连接。sql99优点表连接的条件是独立的连接之后如果还需要进一步筛选再往后继续添加where。
# SQL99语法select ...fromajoinbona和b的连接条件where筛选条件内连接与非等值连接
条件不是一个等量关系称为非等值连接。
案例找出每个员工的薪资等级要求显示员工名、薪资、薪资等级
mysql select * from salgrade;
---------------------
| GRADE | LOSAL | HISAL |
---------------------
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
---------------------mysql select- e.ename,e.sal,s.grade- from- emp e- join- salgrade s- on- e.sal s.losal and e.sal s.hisal;# emp表中的薪资在salgrade表中的区间范围之内的# 条件不是一个等量关系称为非等值连接。
------------------------
| ename | sal | grade |
------------------------
| SMITH | 800.00 | 1 |
| ALLEN | 1600.00 | 3 |
| WARD | 1250.00 | 2 |
| JONES | 2975.00 | 4 |
| MARTIN | 1250.00 | 2 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| KING | 5000.00 | 5 |
| TURNER | 1500.00 | 3 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| FORD | 3000.00 | 4 |
| MILLER | 1300.00 | 2 |
------------------------内连接与自然连接
案例查询员工的上级领导要求显示员工名和对应的领导名
员工名和领导名的相关信息都是在同一个表中技巧就是将emp a看作员工表将emp b看作领导表然后将这两个表进行等值连接
mysql select empno,ename,mgr from emp;
---------------------
| empno | ename | mgr |
---------------------
| 7369 | SMITH | 7902 |
| 7499 | ALLEN | 7698 |
| 7521 | WARD | 7698 |
| 7566 | JONES | 7839 |
| 7654 | MARTIN | 7698 |
| 7698 | BLAKE | 7839 |
| 7782 | CLARK | 7839 |
| 7788 | SCOTT | 7566 |
| 7839 | KING | NULL |
| 7844 | TURNER | 7698 |
| 7876 | ADAMS | 7788 |
| 7900 | JAMES | 7698 |
| 7902 | FORD | 7566 |
| 7934 | MILLER | 7782 |
---------------------empno是员工编号mgt是员工对应的领导的编号。
select a.ename as 员工,b.ename as 领导
fromemp a
join emp b
ona.mgrb.empno;# 员工的领导编号 领导的员工编号
----------------
| 员工 | 领导 |
----------------
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
----------------
13 rows in set (0.00 sec)由于KING没有领导因此没有这个人的数据一共只有13行数据。
技巧一张表看做两张表。
外连接与左外连接右外连接
内连接所连接查询的表没有主次关系不能全部都匹配出来比如上方的那个KING案例就没有匹配到他的领导
外连接是具有主次关系的关键字right/left
右外连接
select e.ename,d.dname
fromemp e
right outer join # outer是可以省略的带着可读性强。dept d
one.deptno d.deptno;# 完全根据这个条件进行匹配
--------------------
| ename | dname |
--------------------
| MILLER | ACCOUNTING |
| KING | ACCOUNTING |
| CLARK | ACCOUNTING |
| FORD | RESEARCH |
| ADAMS | RESEARCH |
| SCOTT | RESEARCH |
| JONES | RESEARCH |
| SMITH | RESEARCH |
| JAMES | SALES |
| TURNER | SALES |
| BLAKE | SALES |
| MARTIN | SALES |
| WARD | SALES |
| ALLEN | SALES |
| NULL | OPERATIONS |
--------------------会发现最底下会有NULL这是因为d表中有的值在e表中是无法匹配到相关数据的。
right表示在join右边的这张表看作主表主表中的所有数据都要在另一个表中关于on中的条件进行查询如果没有找到可以匹配的就用NULL代替。
左外连接
select e.ename,d.dname
fromdept d
left join emp e
one.deptno d.deptno;
--------------------
| ename | dname |
--------------------
| MILLER | ACCOUNTING |
| KING | ACCOUNTING |
| CLARK | ACCOUNTING |
| FORD | RESEARCH |
| ADAMS | RESEARCH |
| SCOTT | RESEARCH |
| JONES | RESEARCH |
| SMITH | RESEARCH |
| JAMES | SALES |
| TURNER | SALES |
| BLAKE | SALES |
| MARTIN | SALES |
| WARD | SALES |
| ALLEN | SALES |
| NULL | OPERATIONS |
--------------------在join关键字左边的表作为主表将主表中的所有数据进行查询 带有right的是右外连接又叫做右连接。 带有left的是左外连接又叫做左连接。 任何一个右连接都有左连接的写法。 任何一个左连接都有右连接的写法。 内连接与外连接主要区别在于
内连接所关联的的表之间是没有主次关系的当一个表中的数据在另一个表中的没有相关联的就不会进行查询匹配。
外连接所关联的表之间是有主次关系的关键字right/left用来标记关键字join旁的哪个表作为主表主表中的所有数据都必须进行查询若没有相关联的匹配项就用null代替。
mysql selecta.ename as 员工,b.ename as 领导fromemp aleft joinemp bona.mgrb.empno;
----------------
| 员工 | 领导 |
----------------
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | NULL |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
----------------多表连接
语法select ...fromajoinbona和b的连接条件joincona和c的连接条件right joindona和d的连接条件一个SQL语句中内连接和外连接可以混合使用。
案例找出每个员工的部门名称以及工资等级要求显示员工名、部门名、薪资、薪资等级
selecte.ename,d.dname,e.sal,s.grade
fromemp e
joindept d
one.deptnod.deptno
joinsalgrade s
on e.sal between s.losal and hisal
order bye.sal;
------------------------------------
| ename | dname | sal | grade |
------------------------------------
| SMITH | RESEARCH | 800.00 | 1 |
| JAMES | SALES | 950.00 | 1 |
| ADAMS | RESEARCH | 1100.00 | 1 |
| WARD | SALES | 1250.00 | 2 |
| MARTIN | SALES | 1250.00 | 2 |
| MILLER | ACCOUNTING | 1300.00 | 2 |
| TURNER | SALES | 1500.00 | 3 |
| ALLEN | SALES | 1600.00 | 3 |
| CLARK | ACCOUNTING | 2450.00 | 4 |
| BLAKE | SALES | 2850.00 | 4 |
| JONES | RESEARCH | 2975.00 | 4 |
| SCOTT | RESEARCH | 3000.00 | 4 |
| FORD | RESEARCH | 3000.00 | 4 |
| KING | ACCOUNTING | 5000.00 | 5 |
------------------------------------
14 rows in set (0.00 sec)结合外连接将每个员工的上司也输出
selecte.ename 员工,d.dname,m.ename 上司,e.sal,s.grade
fromemp e
joindept d
one.deptnod.deptno
joinsalgrade s
on e.sal between s.losal and hisal
left joinemp m
one.mgrm.empno
order bye.sal;
--------------------------------------------
| 员工 | dname | 上司 | sal | grade |
--------------------------------------------
| SMITH | RESEARCH | FORD | 800.00 | 1 |
| JAMES | SALES | BLAKE | 950.00 | 1 |
| ADAMS | RESEARCH | SCOTT | 1100.00 | 1 |
| MARTIN | SALES | BLAKE | 1250.00 | 2 |
| WARD | SALES | BLAKE | 1250.00 | 2 |
| MILLER | ACCOUNTING | CLARK | 1300.00 | 2 |
| TURNER | SALES | BLAKE | 1500.00 | 3 |
| ALLEN | SALES | BLAKE | 1600.00 | 3 |
| CLARK | ACCOUNTING | KING | 2450.00 | 4 |
| BLAKE | SALES | KING | 2850.00 | 4 |
| JONES | RESEARCH | KING | 2975.00 | 4 |
| FORD | RESEARCH | JONES | 3000.00 | 4 |
| SCOTT | RESEARCH | JONES | 3000.00 | 4 |
| KING | ACCOUNTING | NULL | 5000.00 | 5 |
--------------------------------------------
14 rows in set (0.00 sec)子查询
select语句中嵌套select语句被嵌套的select语句称为子查询。 select..(select).from..(select).where..(select).where子查询
案例找出比最低工资高的员工姓名和工资
select ename,sal from emp where salmin(sal);这个句子是错误的因为分组函数不能出现在where之中。
第一步查询最低工资是多少
mysql select min(sal) from emp;
----------
| min(sal) |
----------
| 800.00 |
----------第二步找到800的人
mysql select ename,sal from emp where sal800;
-----------------
| ename | sal |
-----------------
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| FORD | 3000.00 |
| MILLER | 1300.00 |
-----------------第三步合并
select ename,sal
from emp
wheresal(select min(sal) from emp);执行到where会先去执行子查询得到最小值然后执行where中的判断语句。
from子查询
在from后面的子查询所得到的结果可以看作一张临时表。
案例找出每个岗位的平均工资的薪资等级。
第一步找到每个岗位的平均薪资分组查询
mysql select job,avg(sal) from emp group by job;
------------------------
| job | avg(sal) |
------------------------
| CLERK | 1037.500000 |
| SALESMAN | 1400.000000 |
| MANAGER | 2758.333333 |
| ANALYST | 3000.000000 |
| PRESIDENT | 5000.000000 |
------------------------第二步将上述表看作一张临时表与工资等级表进行连接
select t.job,t.avgsal,s.grade
from (select job,avg(sal) as avgsal from emp group by job) t
joinsalgrade s
ont.avgsal between s.losal and s.hisal;
-------------------------------
| job | avgsal | grade |
-------------------------------
| CLERK | 1037.500000 | 1 |
| SALESMAN | 1400.000000 | 2 |
| MANAGER | 2758.333333 | 4 |
| ANALYST | 3000.000000 | 4 |
| PRESIDENT | 5000.000000 | 5 |
-------------------------------select子查询
mysql selecte.ename,e.deptno,(select d.dname from dept d where e.deptno d.deptno) as dnamefromemp e;
----------------------------
| ename | deptno | dname |
----------------------------
| SMITH | 20 | RESEARCH |
| ALLEN | 30 | SALES |
| WARD | 30 | SALES |
| JONES | 20 | RESEARCH |
| MARTIN | 30 | SALES |
| BLAKE | 30 | SALES |
| CLARK | 10 | ACCOUNTING |
| SCOTT | 20 | RESEARCH |
| KING | 10 | ACCOUNTING |
| TURNER | 30 | SALES |
| ADAMS | 20 | RESEARCH |
| JAMES | 30 | SALES |
| FORD | 20 | RESEARCH |
| MILLER | 10 | ACCOUNTING |
----------------------------
14 rows in set (0.00 sec)union合并查询结果集
select ename,job from emp where job MANAGER
union
select ename,job from emp where job SALESMAN;
------------------
| ename | job |
------------------
| JONES | MANAGER |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
------------------union主要体现在合并表的效率很高在表连接中每次连接一次新的表匹配的次数就会乘以新表的行数
但是使用union可以减少匹配的次数并且完成表的拼接
a 连接 b 连接 c a 10条记录 b 10条记录 c 10条记录 匹配次数是100010*10*10
使用union的话是
a连接b10*10uniona连接c10*10100次 100次 200次。使用union的一写注意事项 要求合并的两个表的列数相同 要求合并的两个表的列与列之间的数据类型一致 注释UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。 union会自动删除重复行union all 则不会
limit分页
limit startIndex, length通过limit去除表中的一部分数据提高用户的的体验
startIndex默认是从0开始的
省缺用法
limit 5;取出前5条数据。
selectename,sal
from emp
order bysal desc
limit 5;
----------------
| ename | sal |
----------------
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
----------------这样就找到了前5条
limit 23;# 表示从下标2开始也就是第三条数据开始取出3条数据也就是第345公式
每页显示pageSize条记录 第pageNo页limit (pageNo - 1) * pageSize , pageSize
