Mysql数据库笔记整理（二）

回顾：
数据库–>关系型数据库–>数据表–>SQL语言
SQL划分：
DDL：定义（结构）create,alter[add/drop/modify/change],drop,truncate

DML：操纵（数据）insert,update,delete[语法必须记住]

TPL：事务commit,rollback,savepoint

DQL：查询select…from…[重点]

DCL：权限grant,revoke,create user

二、基础查询
//查询全部列
select * from 表名;

select * from emp_zhang;

ps：
通配符*查询会降低查询速度和影响应用程序性能，建议少用

1）查询1005员工信息
select empno,ename from emp_zhang where empno=1005;

select * from emp_zhang where empno=1005;//where表示有条件的查询

select * from emp_zhang where ename=‘黄蓉’;

ps：
where表示行(记录)的过滤

=等于
!=或<>不等于

大于
=大于等于
<小于
<=小于等于

2）列别名
查询员工的薪水和年薪（salary12）
select ename,salary,salary12 from emp_zhang;

select ename,salary,salary*12 year_salary from emp_zhang;//as可以省略掉

select ename,salary,salary*12 as year_salary from emp_zhang;

ps：使用限定的表
select ename from emp_zhang;
或
select e.ename from emp_zhang e;

3）空值null
结论：（记住）
a.空值参与算数运算时结果为空
b.任何数据类型都可以取空值
c.空值在参与连接操作(concat(a,b))时结果为空

查询员的月薪（salary+bonus）
select ename,salary,bonus,salary+bonus mouth_salary from emp_zhang;//查询结果有问题

4）空值处理函数（记住）
ifnull(d1,d2):若d1为空则取d2来代替，反之如果d1不为空取d1的值

select ename,salary,bonus,salary+ifnull(bonus,0) mouth_salary from emp_zhang;

5)插入记录
empno： 1013;
ename： ‘欧阳锋’；
其他为：null

//全字段
insert into emp_zhang values(1013,‘欧阳锋’,null,null,null,null,null,null);

//指定字段
insert into emp_zhang(empno,ename) values(1013,‘欧阳锋’);

//查询员工的姓名，职位，要求没有职位的显示“No,position”
select ename,ifnull(position,‘No position’) position from emp_zhang;

6)连接操作
查询员工信息，要求员工姓名和职位连接在一起
刑如：‘张三丰Analyst’
select empno,concat(empno,ifnull(position,‘No position’))
message from emp_zhang;//欧阳锋职位为空，所有拼接结果也为空

7）去重
distinct:实现去重，只能跟在select后面

查询有哪些职位
select position from emp_zhang;

select distinct position from emp_zhang;//对position去重,一列去重

查询每个部门不重复的职位
select distinct deptno,position from emp_zhang;//需要对两个字段联合去重，表示全部列的唯一组合

8）大小写
Mysql默认查询不区分大小写，如果需要区分，必须在创建表时通过binary标识敏感数据
ps：
create table temp(
ename varchar(6) binary
);

查询职位为’Analyst’的员工信息
select ename,position from emp_zhang where position=‘Analyst’;

SELECT ename,POSITION from emp_zhang where position=‘analyst’;//mysql默认不区分大小写

SELECT ename,POSITION from emp_zhang where binary position=‘analyst’;//binary严格区分大小写，查询不到记录

9）介于两者之间
between 低值 and 高值
闭区间[低值，高值]

查询薪水大于等于5000并且薪水小于等于10000的员工信息
select ename,salary from emp_zhang where salary>=5000 and salary<=10000;
select ename,salary from emp_zhang where salary between 5000 and 10000;

查询员工入职日期在2009年的
select ename,hiredate from emp_zhang where hiredate between ‘2009-01-01’ and ‘2009-12-31’;
或者
select ename,hiredate from emp_zhang where date_format(hiredate,’%x’)=‘2009’;

ps：
'并且’使用and关键字代表同时满足
'或者’使用or关键字

10)IN使用
in(列表项)：判断等于列表项当中任意一项，满足一个即可

查询职位是’Analyst’或’Manager’的员工姓名和职位
select ename,position from emp_zhang where position=‘Analyst’ or position=‘Manager’;
或者
select ename,position from emp_zhang where position in(‘Analyst’,‘Manager’);

改动：
select ename,position from emp_zhang where position in(‘Analyst’,‘Manager’,null);//列表项中增加空值，没有影响结果。
//'欧阳锋’position为空没有被查询出来

ps：
a.使用in时列表项中有空值对结果没有影响
b.空值不能用等于或者不等于跟任何数据（包括本身）进行比较

IN等价于 =any
select ename,position from emp_zhang where position =any(‘Analyst’,‘Manager’,null);//mysql会报错

11）模糊查询
使用like关键字并结合通配符表示
_表示一个字符，
%表示0到多个字符

查询员工姓名中包含’张’字的员工信息
select empno,ename from emp_zhang where ename like ‘%张%’;

查询要求职位当中包含’sales’的员工姓名和职位
select ename,position from emp_zhang where position like ‘%sales%’;

查询职位中第二个字符是’a’的员工姓名和职位
select ename,position from emp_zhang where position like ‘_a%’;

//插入记录
insert into emp_zhang(empno,ename) values(1014,‘张三%’);

补充内容，了解
查询员工姓名中包含’%'员工信息
select empno,ename from emp_zhang where ename like ‘%/%%’ escape ‘/’;

ps：
escape：转义字符后面的_或%，使其不作为通配符，而是普通字符匹配

12）
空值不能用等于(=)或者不等于(!=和<>)跟任何数据（包括本身）进行比较

测试空值，肯定形式用is null，否定形式用is not null

查询那些员工没有奖金
select ename,bonus from emp_zhang where bonus is null;

13）查询中否定形式
查询哪些员工有奖金
select ename,bonus from emp_zhang where bonus is not null;

查询薪水不在5000和10000直接的
select ename,salary from emp_zhang where salary<5000 or salary>10000;
select ename,salary from emp_zhang where salary not between 5000 and 10000;

查询部门号不是20号部门和不是30号部门
select empno,ename from emp_zhang where deptno!=20 and deptno!=30;//<>错误，空值未出来

not in(列表项);判断不等于列表中所有项，要求全部满足
select empno,ename from emp_zhang where deptno not in(20,30);

改动：
select empno,ename from emp_zhang where deptno not in(20,30,null);
//使用not in()时列表项中有空值，结果查询不到任何记录。列表项中空值必须去掉。

ps：重要
in(列表项)：肯定，判断等于列表项中任意一项。
列表项有空值对结果没有影响。

not in(列表项):否定，判断不等于列表当中所有项。
列表项有空值对结果有影响（查询不到任何记录），
使用not in时列表项中空值必须去掉。

三、查询中常用函数
单行函数：每一行数据处理后返回一个结果
1）处理数字函数
round(数字,保留到小数点后指定位数)：
用于对数字进行四舍五入处理保留到小数点后指定位数。
如果保留到整数位，第二个参数可以用0表示或者不写。
//测试
select round(123.456,2) from dual;//from dual可以省略
select round(123.456,0);
select round(123.456);

truncate(数字,截取到小数点后面指定位数)：
用于对数字进行截取操作保留到小数点后指定位数。
如果截取到整数位，第二个参数用0表示,并且0不可以省略
//测试
select truncate(123.456,2);
select truncate(123.456,0);

2）拼接（连接操作）
concat(a,b,c):用于对多个字符串连接成一个字符串

查询员工姓名和职位，要求数据显示如：‘张三丰(Analyst)’
select empno concat(ename,’(’,ifnull(position,‘No Position’),’)’) message from emp_zhang;

3）去除空格
trim(字符数据)：去掉左右空格
例如：’( abc )’
select concat(’(’,’ abc ‘,’)’);
select concat(’(’,trim(’ abc ‘),’)’);//去两边空格
select concat(’(’,ltrim(’ abc ‘),’)’);//去左边空格
select concat(’(’,rtrim(’ abc ‘),’)’);//去右边空格

4）文本处理函数
upper(小写):转大写
lower(大写):转小写
//测试
select upper(‘abc’);
select lower(‘ABC’);

length(字符)：返回字符对应的字节长度
select ename,length(ename) from emp_zhang;//数据库用的是utf-8的字符编码，一个中文占3个字节

substring(字段,起始位置,最大字符数量)
select ename,substring(ename,2,2) from emp_zhang;//从第2个字符开始查找，最多返回2个字符

5）数据处理函数
abs(数据)：返回数据的绝对值
rand()：返回0-1直接的随机数
sqrt(数据):返回数据的平方根
pow(x,y):返回x的y次方
mod(x,y):返回x除以y的余数

select sqrt(16);
select pow(2,3);

6）处理日期函数
now():返回当前系统时间
date(日期时间):返回日期数据
time(日期时间):返回时间数据
year(日期时间):返回年份

//测试
select year(now());

adddate()：增加一个日期（年year，月month，日day）
//interval表示间隔的意思
select adddate(‘2019-07-09’,interval 3 day);
select adddate(‘2019-07-09’,interval 3 month);
select adddate(‘2019-07-09’,interval 3 year);
或者
select date_add(‘2019-07-09’,interval 3 day);
select date_add(‘2019-07-09’,interval 3 month);
select date_add(‘2019-07-09’,interval 3 year);

ps：
select adddate(‘2019-07-09’,3);//默认增加天数，三天

date_format(日期,格式):格式化日期数据
//测试
select date_format(now(),’%X年-%m月-%d日 %H时:%i分:%s秒’);

7）其他函数
case…when…:分支条件结构
语法结构：
case 字段
when 条件1 then 值1
when 条件2 then 值2
else 其他值 end

根据员工职位，计算加薪之后的薪水值
要求：
如果职位是’Analyst’加薪20%
如果职位是’Programmer’加薪5%
如果职位是’Clerk’加薪2%
其他职位薪水不变

select ename,position,salary,
case position
when ‘Analyst’ then salary1.2
when ‘Programmer’ then salary1.05
when ‘Clerk’ then salary*1.02
else salary end
new_salary from emp_zhang;

组函数（聚合函数）：多行数据处理后返回一个结果
count(列名|*)：求记录数
sum(列名)：求和
avg(列名)：求平均
max(列名)：求最大值
min(列名)：求最小值

//测试
select count(empno) from emp_zhang;//14
select count(position) from emp_zhang;//12 忽略空值
select count(*) from emp_zhang;//14 *不忽略空值

查询员工薪水综合
select sum(salary) from emp_zhang;

查员工人数，薪水总和，平均薪资
select count(*),sum(salary),avg(salary) from emp_zhang;//avg会忽略空值对结果有影响

select count(*),sum(salary),avg(ifnull(salary,0)) from emp_zhang;

select max(salary),min(salary) from emp_zhang;
改动：
select ename,max(salary),min(salary) from emp_zhang;
mysql不会报错，其他数据库不支持，避免使用改写法

总结：
count/sum/avg/max/min如果函数中写列名默认是忽略空值的，count(*)不忽略空值
sum/avg只能用于处理数值类型。