【Mysql系列】01_查询+排序

一、初始

1、mysql服务启动停止

WIN+X：以管理员身份运行

在windows操作系统当中，使用命令来启动和关闭mysql服务。

语法：

net stop 服务名称;
net start 服务名称;

2、登录mysql

使用bin目录下的mysql.exe命令来连接mysql数据库服务器。

PS C:\Users\Lenovo> mysql -uroot -p123456
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.45-community-nt-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

本地登录（显示编写密码的形式）：

3、mysql常用命令

命令	代码
退出mysql	exit
查看mysql中有哪些数据库	show databases;
使用某个数据库	use 数据库名字;
创建数据库	create database 数据库名字;
查看某个数据库下有哪些表	show tables
查看表当中的数据	select*from 表名;
查看表的结构	desc 表名;
查看mysql版本号	select version();
查看当前使用的数据库	select database();
终止命令的执行	\c

• 注意：用分好结尾，分号是英文的。
• mysql默认自带了四个数据库。
• describe缩写为desc。
• 命令没有碰见分号不会执行。

4、关键字执行顺序

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

执行顺序：先from再使用where过滤，然后使用分组group by，之后查询select，最后排序输出order by。

二、查询

1、表的理解

数据库当中最基本的单元是表：table

姓名	性别	年龄
张三	男	25

• 数据库当中以表格形式存储数据的。因为表比较直观。
• 任何一张表都有行和列：
  • 行（row）：被称为数据/记录。
  • 列（column）：被称为字段。
    每个字段都有：字段名，数据类型，约束等属性。

2、sql分类

• 1、DQL：数据查询语言（凡是带有select关键字的都是查询语言）
• 2、DML：数据操作语言（凡是对表当中的数据进行增删改查的都是DML）

insert delete update
insert 增
delete删
update改

• 3、DDL：数据定义语言
  凡是带有create，drop，alter的都是DDL。
  DDL主要操作是表的结构，不是表中的数据。
  create：新建
  drop：删除
  alter：修改

• 4、TCL：事务控制语言
  事务提交：commit
  事务回滚：rollback

• 5、DCL：数据控制语言
  例如：授权grant，撤销授权revoke

3、简单查询

select 字段名 from 表名;

• select和from都是关键字。
• 字段名和表名都是标识符。

强调：

• 对于sql语句来说，是通用的。
• 所有的SQL语句都是以；结尾。
• SQL语句不区分大小写。

select后面直接跟字面量/字面值：

select 'abc' from emp;

会生成新的一列，所有的值都是abc

• 结论：select后面可以跟某个表的字段名，也可以跟字面量/字面值。

4、查询多个字段

select deptno,dname from dept;

• 查询多个字段则使用逗号分隔不同的字段。

5、查询所有字段

select * from dept;

• 会把*转换为字段

6、给查询的列起别名

select deptno,dname as deptname from dept;

• 使用as关键字起别名。
• 只是将显示的查询结果列名显示为deptname，原表的列名还是叫dname。
• select语句是永远不会进行修改操作的，只负责查询与检索。
• as可以省略
• 起别名的时候，别名里面有空格，则使用单引号
• 在所有的数据库当中，字符串统一使用单引号
• 双引号在oracle数据库当中用不了，但是mysql当中可以使用

select deptno,dname 'dept name'from dept;

7、计算员工年薪

• 字段可以使用数学表达式

select ename,sal*12 as yearsal from emp;

select ename,sal*12 as '年薪' from emp;

8、条件查询

• 不是将表当中所有数据都查出来，而是查询出来符号条件的。

select ... from ... where 条件;

符号	含义
=	等于
<> 或 !=	不等于
<=	小于等于
between…and… 等同于 >=and<=	两个值之间
is null	为空
is not null	不为空
or	或者
and	并且
in	在此范围（相当于多个or）
not in	不在这个范围
%	匹配任意多个字符
_	匹配任意一个字符

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

select empno,ename from emp where sal=800;

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

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

查询薪资小于800的员工姓名和编号：

select empno,ename from emp where sal < 800;

查询SMITH的编号和薪资：

select empno ,sal from emp where ename='SMITH';

查询薪资在2450和3000之间的员工信息（包括2450和3000）：

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

• 使用between and 的时候必须遵循左小右大。并且是闭区间。

查询哪些员工的补助为null：

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

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

查询哪些员工的补助不为null：

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

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

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

查询工作岗位是MANAGER 和SALEMAN的员工：

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

and和or同时出现的话，有优先级问题吗？
有，and优先级大于or。因此需要使用括号括起来。

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

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

• and和or同时出现，则and优先级较高。如果想让or先执行，则需要加小括号。以后在开发中如果不确定优先级，则加小括号就行了。

查询工作岗位是MANAGER 和SALEMAN的员工（使用in）：

select empno,ename,job,sal from emp where job  in('MANAGER' ,'SALESMAN');

• in相当于多个or，但是in不是一个区间。in后面跟的是一个具体的值。

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

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

9、模糊查询

like 称为模糊查询，支持%或者下划线匹配。

%：匹配任意多个字符。
_：匹配任意一个字符。

找出名字里面含有o的：

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

找出名字以T结尾的：

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

找出名字以K开始的：

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

找出第二个字母是A的：

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

找出第三个字母是R的：

select ename from emp where ename like '__R%';//前面两个下划线

找出名字中含有下划线的：

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

• 使用\进行转义

三、排序

使用：order by 字段；

1、升序与降序

解释	表示
指定升序	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;

2、多字段排序

可以两个字段排序吗，或者说按照多个字段排序？
可以，加上逗号就行。

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

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

• sal在前，启主导作用，只有sal相等的时候，才会考虑ename。

3、根据字段的位置排序

select ename,sal from emp order by 2;

2表示第二列，即按照查询结果的第二列sal来排序。

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

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

• 以上关键字的顺序不能改变
  第一步：from
  第二步：where
  第三部：select
  第四部：order by（排序总是在最后执行）

四、单行处理函数

表达	解释
lower	转换成小写
upper	转换成大小
substr	取子串（substr(被截取的子串，起始下标，截取的长度)）
length	取长度
trim	去空格
str_to_date	将字符串转换成日期
date_format	格式化日期
format	设置千分位
round	四舍五入
rand()	生成随机数
ifnull	可以将null转换成一个具体的值

• 数据处理函数又称为单行处理函数。
• 单行处理函数的特点：一个输入对应一个输出。
• 和单行处理函数相对的是：多行处理函数。（多行处理函数特点：多个输入，对应一个输出）

1、lower&upper

lower：转小写

select lower(ename) as ename from emp;

• 使ename这列从大写转变成小写

upper：转大写

select upper(ename) as ename from emp;

• 使ename这列从小写转变成大写

2、substr子串

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

• 起始下标从1开始，没有0

找出员工名字第一个字母是A的员工信息：

第一种方式：

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

第二种方式：

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

首字母大写：

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

3、length

length：取长度

select length(ename) enamelength from emp;

4、trim

trim：可以去除字符串的前后空格。

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

5、round

round：四舍五入

select round(1234.567,0) as result from emp;

• (1234.567,0)：0表示保留到整数位，遵循四舍五入，因此结果是1235

select round(1234.567,1) as result from emp;//保留1位小数
select round(1234.567,2) as result from emp;//保留2位小数
select round(1234.567,-1) as result from emp;//保留到十位，结果为1230
select round(1234.567,-2) as result from emp;//保留到百位，结果为1200

6、rand

rand：生成0-1之间的随机数。

select rand() from emp;

生成100以内的随机数：

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

7、ifnull

ifnull是空处理函数，专门处理空的。

• ifnull可以将null转换为具体的值。

• 在所有的数据库当中，只要有NULL参与的数学运算，最终结果就是NULL。为了避免这个现象，需要使用ifnull函数。

• ifnull函数的用法：ifnull(数据，被当做哪个值)

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

8、case

case…when…then…when…then…else…end

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

select ename ,job,
(case job 
when 'MANAGER' then sal*1.1 
when 'SALEMAN' then sal*1.5
else sal end) as new sal
from emp;

五、多行处理函数

表达	解释
count	计数
sum	求和
avg	平均值
max	最大值
min	最小值

• 多行处理函数也叫分组函数。
• 多行处理函数的特点：输入多行，最终输出一行。
• 注意：
• 1、分组函数必须先进行分组之后才能使用。如果没有对数据进行分组，则整张表为一组。
• 2、分组函数自动忽略null，不需要提前对null进行处理。
• 3、分组函数当中count(*) 和count(具体字段) 有什么区别？
  • count(具体字段)：表示统计该字段下所有不为NULL的元素的总数
  • count(*)：统计表当中的总行数。（只要有一行数据count则++）因为每一行记录不可能全部为null。一行数据当中有一列不为null，则这行数据就是有效的。
• 4、分组函数不能直接使用在where子句当中。
• 5、所有的分组函数可以组合起来一起使用。

找出最高工资：

select max(sal) from emp;

计算所有工资总和：

select sum(sal) from emp;

计算平均工资：

select avg(sal) from emp;//14个工资加起来然后除以14

计算员工数量：

select count(ename) from emp;

找出比最低工资高的员工信息：
分组函数不能直接使用在where子句当中。

select ename,sal from emp where sal>min(sal);//报错！！！！！！

上面的代码报错，原因在于：
1、分组函数在使用的时候必须先分组之后才能使用。

• where执行的时候还没有分组，所以where后面不能出现分组函数。
• 执行顺序：先from再使用where过滤，然后使用分组group by，之后查询select，最后排序输出order by。
• select后面可以使用分组函数的原因：当select执行的时候，分组已经执行完毕，所以可以使用。

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

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

六、分组查询

1、groupby

在实际的应用当中，可能有这样的需求：需要先进行分组，然后对每一组的数据进行操作，这时我们需要使用分组查询。

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

找出每个工作岗位的工资和：
实现思路：按照工作岗位分组，然后对工资求和。

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

上面语句的执行顺序：先从emp表当中查询数据，根据job字段进行分组，然后对每一组的数据进行sum(sal)

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

以上语句在mysql中可以执行，但是毫无意义。ename是14行，其他字段是5行。
以上语句在oracle中执行报错。

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

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

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

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

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

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

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

以上sql语句执行的效率较低，可以先将大于3000的都找出来，然后再进行分组：

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

• where和having优先选择where，where做不了的，再选择having。

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

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

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

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

2、distinct

把查询结果去除重复记录
注意：原表数据不会被修改，只是查询结果去重
去重需要使用一个关键字：distinct

• distinct只能出现在所有字段的最前面，此时表示所有工作岗位联合去重。

select distinct job from emp;

统计工作岗位的数量：

select count(distinct job)
from emp;