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

1、mysql服务启动停止

WIN&＃43;X&＃xff1a;以管理员身份运行

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

语法&＃xff1a;

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 &＃39;help;&＃39; or &＃39;\h&＃39; for help. Type &＃39;\c&＃39; to clear the buffer.mysql>


本地登录&＃xff08;显示编写密码的形式&＃xff09;&＃xff1a;

3、mysql常用命令

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

4、关键字执行顺序

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

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

1、表的理解

数据库当中最基本的单元是表&＃xff1a;table

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

2、sql分类

• 1、DQL&＃xff1a;数据查询语言&＃xff08;凡是带有select关键字的都是查询语言&＃xff09;
• 2、DML&＃xff1a;数据操作语言&＃xff08;凡是对表当中的数据进行增删改查的都是DML&＃xff09;

insert delete update
insert 增
delete删
update改


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

• 4、TCL&＃xff1a;事务控制语言
  事务提交&＃xff1a;commit
  事务回滚&＃xff1a;rollback

• 5、DCL&＃xff1a;数据控制语言
  例如&＃xff1a;授权grant&＃xff0c;撤销授权revoke

3、简单查询

select 字段名 from 表名;


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

强调&＃xff1a;

• 对于sql语句来说&＃xff0c;是通用的。
• 所有的SQL语句都是以&＃xff1b;结尾。
• SQL语句不区分大小写。

select后面直接跟字面量/字面值&＃xff1a;

select &＃39;abc&＃39; from emp;


会生成新的一列&＃xff0c;所有的值都是abc

• 结论&＃xff1a;select后面可以跟某个表的字段名&＃xff0c;也可以跟字面量/字面值。

4、查询多个字段

select deptno,dname from dept;


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

5、查询所有字段

select * from dept;


• 会把*转换为字段

6、给查询的列起别名

select deptno,dname as deptname from dept;


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

select deptno,dname &＃39;dept name&＃39;from dept;


7、计算员工年薪

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

select ename,sal*12 as yearsal from emp;


select ename,sal*12 as &＃39;年薪&＃39; from emp;


8、条件查询

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

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


查询薪资等于800的员工姓名和编号&＃xff1a;

select empno,ename from emp where sal&＃61;800;


查询薪资不等于800的员工姓名和编号&＃xff1a;

select empno,ename from emp where sal !&＃61; 800;
select empno,ename from emp where sal <> 800;


查询薪资小于800的员工姓名和编号&＃xff1a;

select empno,ename from emp where sal < 800;


查询SMITH的编号和薪资&＃xff1a;

select empno ,sal from emp where ename&＃61;&＃39;SMITH&＃39;;


查询薪资在2450和3000之间的员工信息&＃xff08;包括2450和3000&＃xff09;&＃xff1a;

select empno,ename from emp where sal >&＃61;2450 and sal <&＃61;3000;
select empno,ename from emp where sal between 2450 and 3000;


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

查询哪些员工的补助为null&＃xff1a;

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


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

查询哪些员工的补助不为null&＃xff1a;

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


查询工作岗位是MANAGER并且工资大于2500的员工信息&＃xff1a;

select empno,ename,job,sal from emp where job&＃61;&＃39;MANAGER&＃39; and sal>2500;


查询工作岗位是MANAGER 和SALEMAN的员工&＃xff1a;

select empno,ename,job,sal from emp where job&＃61;&＃39;MANAGER&＃39; or job&＃61;&＃39;SALESMAN&＃39;;


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

查询工资大于2500&＃xff0c;并且部门编号为10或者20部门的员工&＃xff1a;

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


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

查询工作岗位是MANAGER 和SALEMAN的员工&＃xff08;使用in&＃xff09;&＃xff1a;

select empno,ename,job,sal from emp where job in(&＃39;MANAGER&＃39; ,&＃39;SALESMAN&＃39;);


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

查询薪资是800和5000的员工信息&＃xff1a;

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


9、模糊查询

like 称为模糊查询&＃xff0c;支持%或者下划线匹配。

%&＃xff1a;匹配任意多个字符。
_&＃xff1a;匹配任意一个字符。

找出名字里面含有o的&＃xff1a;

select ename from emp where ename like &＃39;%o%&＃39;;


找出名字以T结尾的&＃xff1a;

select ename from emp where ename like &＃39;%T&＃39;;


找出名字以K开始的&＃xff1a;

select ename from emp where ename like &＃39;K%&＃39;;


找出第二个字母是A的&＃xff1a;

select ename from emp where ename like &＃39;_A%&＃39;;


找出第三个字母是R的&＃xff1a;

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


找出名字中含有下划线的&＃xff1a;

select name from t_student where name like &＃39;%\_%&＃39;


• 使用\进行转义

使用&＃xff1a;order by 字段&＃xff1b;

1、升序与降序

select ename,sal
from emp
order by sal;


• 默认是升序&＃xff0c;即按照工资列进行升序排序。

升序&＃xff1a;

select ename,sal
from emp
order by sal asc;


降序&＃xff1a;

select ename,sal
from emp
order by sal desc;


2、多字段排序

可以两个字段排序吗&＃xff0c;或者说按照多个字段排序&＃xff1f;
可以&＃xff0c;加上逗号就行。

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

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


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

3、根据字段的位置排序

select ename,sal from emp order by 2;


2表示第二列&＃xff0c;即按照查询结果的第二列sal来排序。

找出工资在1250到3000之间的员工信息&＃xff0c;要求按照薪资降序排列&＃xff1a;

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


• 以上关键字的顺序不能改变
  第一步&＃xff1a;from
  第二步&＃xff1a;where
  第三部&＃xff1a;select
  第四部&＃xff1a;order by&＃xff08;排序总是在最后执行&＃xff09;

• 数据处理函数又称为单行处理函数。
• 单行处理函数的特点&＃xff1a;一个输入对应一个输出。
• 和单行处理函数相对的是&＃xff1a;多行处理函数。&＃xff08;多行处理函数特点&＃xff1a;多个输入&＃xff0c;对应一个输出&＃xff09;

1、lower&upper

lower&＃xff1a;转小写

select lower(ename) as ename from emp;


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

upper&＃xff1a;转大写

select upper(ename) as ename from emp;


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

2、substr子串

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


• 起始下标从1开始&＃xff0c;没有0

找出员工名字第一个字母是A的员工信息&＃xff1a;

第一种方式&＃xff1a;

select ename from emp where ename like &＃39;A%&＃39;;


第二种方式&＃xff1a;

select ename
from emp
where substr(ename,1,1)&＃61;&＃39;A&＃39;;


首字母大写&＃xff1a;

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


3、length

length&＃xff1a;取长度

select length(ename) enamelength from emp;


4、trim

trim&＃xff1a;可以去除字符串的前后空格。

select *
from emp
where ename&＃61;trim(&＃39; KING&＃39;);


5、round

round&＃xff1a;四舍五入

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


• (1234.567,0)&＃xff1a;0表示保留到整数位&＃xff0c;遵循四舍五入&＃xff0c;因此结果是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;//保留到十位&＃xff0c;结果为1230
select round(1234.567,-2) as result from emp;//保留到百位&＃xff0c;结果为1200


6、rand

rand&＃xff1a;生成0-1之间的随机数。

select rand() from emp;


生成100以内的随机数&＃xff1a;

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


7、ifnull

ifnull是空处理函数&＃xff0c;专门处理空的。

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

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

• ifnull函数的用法&＃xff1a;ifnull(数据&＃xff0c;被当做哪个值)

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


8、case

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

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

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

五、多行处理函数

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

找出最高工资&＃xff1a;

select max(sal) from emp;


计算所有工资总和&＃xff1a;

select sum(sal) from emp;


计算平均工资&＃xff1a;

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


计算员工数量&＃xff1a;

select count(ename) from emp;


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

select ename,sal from emp where sal>min(sal);//报错&＃xff01;&＃xff01;&＃xff01;&＃xff01;&＃xff01;&＃xff01;


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

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

所有的分组函数可以组合起来一起使用&＃xff1a;

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

六、分组查询

1、groupby

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

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


找出每个工作岗位的工资和&＃xff1a;
实现思路&＃xff1a;按照工作岗位分组&＃xff0c;然后对工资求和。

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


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

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


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

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

找出每个部门的最高薪资&＃xff1a;

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


找出每个部门不同工作岗位的最高薪资&＃xff1a;

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


找出每个部门最高薪资&＃xff0c;要求显示最高薪资大于3000的&＃xff1a;

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


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

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


• where和having优先选择where&＃xff0c;where做不了的&＃xff0c;再选择having。

找出每个部门平均薪资&＃xff0c;要求显示平均薪资大于2500的&＃xff1a;

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


找出每个岗位的平均薪资&＃xff0c;要求显示平均薪资大于1500的&＃xff0c;除MANAGER岗位之外&＃xff0c;要求按照平均薪资降序排&＃xff1a;

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


2、distinct

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

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

select distinct job from emp;


统计工作岗位的数量&＃xff1a;

select count(distinct job)
from emp;