MySQL学习笔记（二）

MySQL学习（二）

导入准备好的数据

bjpowernode.sql 为准备好的数据库表

输入create database bjpownode 创建数据库

输入use bjpowernode 使用数据库

输入以下导入数据

source  D:\BaiduNetdiskDownload\learn_mysql\document\bjpowernode.sql #没有分号！

source后面是准备数据的路径

关于导入的表

dept：部门表 ；emp：员工表；salgrade：工资等级表

查看表中的数据

select * from 表名

select * from emp; #查看员工表所有数据
select * from dept;
select * from salgrade;

查看表的结构

desc 表名

desc emp;
desc dept;
desc salgrade;

mysql常用命令（续一）

exit; #退出数据库
show databases; #查看所有数据库
use test; #使用test数据库
create database bjpowernode; #创建名叫bjpowernode的数据库
show tables; #查看数据库下有哪些表
select version();  #查看mysql数据库的版本号
select databse();  #查看当前使用的数据库
#mysql中不见分号不执行，分号表示结束
#\c可以终止命令输入，或者ctrl+C

mysql语句学习

简单查询

查询一个字段 ： select 字段名 from 表名;

注意：select和from都是关键字，字段名和表名都是标识符

select dname from dept;

查询两个或多个字段 ：select 字段名1,字段名2… from 表名

select deptno,dname from dept;

查询所有字段：一、把每个字段都写上；二、使用*（效率低，可读性差，实际开发中不建议）

给查询的列起别名：使用as关键字起别名（或者直接省略）

select deptno,dname as deptname from dept;

注：1、只是查询结果起了别名，原表不改，select语句不会进行修改操作

​ 2、如果起别名的时候别名里有空格会报错，或者可以使用单引号或者双引号括起来（建议使用单引 号，因为单引号是标准，在oracle中就不能使用双引号，只能使用单引号）

计算员工年薪：字段中可以使用数学表达式

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

条件查询

查询出符合条件的数据

语法格式：

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

所有条件：

# = 等于
select empno,ename from emp where sal = 800; #查询薪资等于800的员工姓名和编号
select empno,sal from emp where ename = 'SMITH'; #查询SMITH的编号和薪资
# <>或!= 不等于
select empno,ename from emp where sal != 800; #查询薪资不等于800的员工姓名和编号
select empno,ename from emp where sal <> 800; #小于号和大于号组成的不等号
# < 小于
select empno,ename,sal from emp where sal < 2000; #查询薪资小于2000的员工姓名和编号
# <= 小于等于
select empno,ename,sal from emp where sal <= 3000; #查询薪资小于等于3000的员工姓名和编号
# > 大于
select empno,ename,sal from emp where sal > 3000; #查询薪资大于3000的员工姓名和编号
# >= 大于等于
select empno,ename,sal from emp where sal >= 3000; #查询薪资大于等于3000的员工姓名和编号
select empno,ename,sal from emp where sal >= 2450 and sal <= 3000; 
#查询薪资在2450和3000之间的员工信息？包括2450和3000
# between … and …. 两个值之间, 等同于 >= and <=
select empno,ename,sal from emp where sal between 2450 and 3000; 
#使用between,and必须遵循左小右大，闭区间，包括两端的值
# is null 为 null（is not null 不为空）
#select empno,ename,sal,comm from emp where comm = null; 
#该语句不行，不能使用=，null表示什么也没有，不能用等号衡量
select empno,ename,sal,comm from emp where comm is null; #查询哪些员工的津贴/补助为null
select empno,ename,sal,comm from emp where comm is not null; #查询哪些员工的津贴/补助不为null
# and 并且
select empno,ename,job from emp where job = 'MANAGER' and sal>2500; 
#查询工作岗位是MANAGER并且工资大于2500的员工信息
# or 或者
select empno,ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';#查询工作岗位是MANAGER和SALESMAN的员工
select * from emp where sal > 2500 and deptno = 10 or deptno = 20;
#因为and优先级比or高，所以以上语句会先执行and，然后执行or，即将工资大于2500并且部门编号为10的员工，或者20部门所有员工找出来。
select * from emp where sal > 2500 and (deptno = 10 or deptno = 20); #带括号让or先执行
# in 包含，相当于多个 or （not in 不在这个范围中），in不是个区间，in后面是具体的值
select empno,ename,job from emp where job in('MANAGER', 'SALESMAN'); #查询工作岗位是MANAGER和SALESMAN的员工
select ename,sal from emp where sal in(800, 5000); #不是表示800到5000，而是800和5000
select ename,sal from emp where sal not in(800, 5000, 3000);#不在800,5000,3000中的数据
#not 可以取非，主要用在 is 或 in 中

模糊查询

like，支持%或下划线匹配

%匹配任意多个字符，下划线匹配任意一个字符，%和_都是特殊符号

select ename from emp where ename like '%O%'; #找出名字里含O的
select ename from emp where ename like '%T';  #找出名字以T结尾的
select ename from emp where ename like 'K%';  #找出名字以K开始的
select ename from emp where ename like '_A%'; #找出第二个字每是A的
select ename from emp where ename like '__R%'; #找出第三个字母是R的
mysql> select name from t_student where name like '%\_%'; #找出名字里含有下划线的，\转义字符

排序

按单个字段排序：

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;  #指定降序

按两个或多个字段排序：

select ename,sal from emp order by sal asc, ename asc;
#查询员工名字和薪资，要求按照薪资升序，如果薪资一样的话，再按照名字升序排列。
select ename,sal from emp order by 2; #2表示第二列sal，即按薪资排序，不建议在开发中这样写

综合案例

select ename,sal from emp where sal between 1250 and 3000 order by sal desc;
#找出工资在1250到3000之间的员工信息，要求按照薪资降序排列。

第一步：from 第二步：where 第三步：select 第四步：order by（排序总是在最后执行！）

函数

数据处理函数

数据处理函数又被称为单行处理函数，特点：一个输入对应一个输出

与单行处理函数相对的是：多行处理函数，多个输入对应一个输出

单行处理函数

常见的单行处理函数：

# lower 转换小写
select lower(ename) as ename from emp;
# upper 转换大写
select upper(name) as name from t_student;
# substr 取子串（substr( 被截取的字符串, 起始下标,截取的长度)）
select substr(ename, 1, 1) as ename from emp; #下标从1开始，没有0
# 找出员工名字第一个字母是A的员工信息
# 第一种方法：模糊查询
select ename from emp where ename like 'A%';
# 第二种方式：substr函数
select ename from emp where substr(ename,1,1) = 'A';
# concat函数进行字符串的拼接
select concat(empno,ename) from emp;
select concat(upper(substr(name,1,1)),substr(name,2,length(name) - 1)) as result from t_student;
#首字母大写
# length 取长度
select length(ename) enamelength from emp;
# trim 去空格
select * from emp where ename = trim('   KING');
# str_to_date 将字符串转换成日期
# date_format 格式化日期
# format 设置千分位
# case..when..then..when..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 newsal from emp;
#当员工的工作岗位是MANAGER的时候，工资上调10%，当工作岗位是SALESMAN的时候，工资上调50%,其它正常
#不修改数据库，只是将查询结果显示为工资上调	
# round 四舍五入
select round(1236.567, 0) as result from emp; #0表示保留到整数位
select round(1236.567, 1) as result from emp; #保留1位小数
select round(1236.567, 2) as result from emp; #保留2位小数
select round(1236.567, -1) as result from emp; #保留到十位
# rand() 生成随机数
select rand() from emp; #生成0到1的随机数
select round(rand()*100,0) from emp; #生成100以内的随机数
# ifnull 可以将 null 转换成一个具体值，ifnull是空处理函数，专门处理空的
# 在所有数据库当中，只要有NULL参与的数学运算，最终结果就是NULL。
# ifnull函数用法：ifnull(数据, 被当做哪个值)，如果“数据”为NULL的时候，把这个数据结构当做哪个值。
select ename, (sal + ifnull(comm, 0)) * 12 as yearsal from emp;
# 计算每个员工的年薪： 年薪 = (月薪 + 月补助) * 12

分组函数（多行处理函数）

特点：输入多行，最多处理一行

注：分组函数在使用时必须先进行分组，然后才能使用，如果没有分组，整张表默认为一组

# max 最大值
select max(sal) from emp; #找出最高工资
# min 最小值
select min(sal) from emp; #找出最低工资
# sum 求和
select sum(sal) from emp; #计算工资和
# avg 平均值
select avg(sal) from emp; #计算平均工资
# count	计数
select count(ename) from emp; #计算员工数量

分组函数使用时注意问题：

一、分组函数自动忽略NULL，不需要提前对NULL进行处理。

二、分组函数中count(*)和count(具体字段)有什么区别？

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

​ count(*)：统计表当中的总行数。（只要有一行数据count则++）。因为每一行记录不可能都为NULL，都为NULL没有意义，一行数 据中有一列不为NULL，则这行数据就是有效的。

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

四、所有的分组函数可以组合起来一起用。

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

分组查询（非常重要）

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

select … from … group by …

将之前的关键字全部组合在一起，使用顺序:

select … from … where … group by … order by …

执行顺序：1. from 2.where 3.group by 4. select 5.order by

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

select ename,sal from emp where sal > min(sal); #报错。

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

select sum(sal) from emp; 

这个没有分组，因为select在group by之后执行，所以sum()函数可以用

# 找出每个工作岗位的工资和
# 实现思路：按照工作岗位分组，然后对工资求和
select job,sum(sal) from emp group by job;
# 执行顺序：先从emp表中查询数据，根据job字段进行分组，然后对每一组的数据进行sum(sal)
select ename,job,sum(sal) from emp group by job;
# 以上语句在mysql中可以执行，但是毫无意义。
# 以上语句在oracle中执行报错，oracle的语法比mysql的语法严格。（mysql的语法相对来说松散一些！）

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

# 找出每个部门的最高薪资
# 实现思路：按照部门编号分组，求每一组的最大值。
select deptno,max(sal) from emp group by deptno;
# 找出“每个部门，不同工作岗位”的最高薪资
# 技巧：两个字段联合成1个字段看。（两个字段联合分组）
select deptno, job, max(sal) from emp group by deptno, job;

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

having不能单独使用，having不能代替where，having必须和group by联合使用。

# 找出每个部门最高薪资，要求显示最高薪资大于3000的
# 第一步：找出每个部门最高薪资，按照部门编号分组，求每一组最大值。
select deptno,max(sal) from emp group by deptno;
# 第二步：要求显示最高薪资大于3000
select deptno,max(sal) from emp group by deptno having max(sal) > 3000;
#这样的两步语句执行效率低
#实际思路：先将大于3000的都找出来，然后再分组。
select deptno,max(sal) from emp where sal > 3000 group by deptno;

优化策略：where和having，优先选择where，where实在完成不了了，再选择having。

# where实现不了的例子
# 找出每个部门平均薪资，要求显示平均薪资高于2500的。
# 第一步：找出每个部门平均薪资
select deptno,avg(sal) from emp group by deptno;
# 第二步：要求显示平均薪资高于2500的
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2500;

总结

输入顺序（不能颠倒）： select ..from…where…group by…having…order by…

执行顺序： 1.from 2. where 3. group by 4. having 5. select 6.order by

从某张表中查询数据，
先经过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
			avgsal desc;