MySQL学习总结1

1、SQL、DB和DBMS的介绍

SQL：结构化查询语言，保准的sql适用于所有的数据库产品。

DB：指的是数据库，数据库实际上以文件的形式存在于硬盘中。

DBMS：数据库管理系统。

我们常说的MySQL数据库其实指的是MySQL数据库管理系统。

2、SQL语句的分类

SQL语句	介绍
DQL（数据查询语言）	查询语句，凡是select语句都是DQL
DML（数据操作语言)	insert delete update，对表当中的数据进行增删改
DDL（数据定义语言）	create drop alter，对表结构的增删改
TCL（事务控制语言）	commit提交事务，rollback回滚事务
DCL（数据控制语言）	grant授权、revoke撤销权限等

3、sql脚本：

拓展名是.sql，并且该文件中编写了大量的sql语句

4、对数据库的常见操作命令

# 登录mysql数据库管理系统  
mysql -uroot -p
# 查看有哪些数据库
show databases;  # 这个不是SQL语句，属于MySQL的命令。
# 创建数据库
create database bjpowernode; # 这个不是SQL语句，属于MySQL的命令。
# 切换到bjpowernode数据库
use bjpowernode; # 这个不是SQL语句，属于MySQL的命令。
# 查看当前数据库下的表
show tables; # 这个不是SQL语句，属于MySQL的命令。
# 导入sql脚本，初始化数据
source D:\course\05-MySQL\resources\bjpowernode.sql
# 删除数据库
drop database bjpowernode;
# 查看表结构
desc emp;
# 查看当前使用的是哪个数据库
select database();
# 查看数据库版本
select version();
# 查看建表语句
show create table emp;
# 结束一条SQL语句
\c
# 退出mysql
exit;

5、sql规范

规范1：任何一条sql以“;”结尾;

规范2：推荐sql语句使用大写;

规范3：标准sql语句中要求字符串使用单引号括起来。虽然mysql支持双引号，尽量别用。

6、DQL总结

普通查询语法格式：

select
​      字段1，字段2，...
from
​      表名

查询员工的年薪

select ename,sal*12 from emp; # 字段可以参与数学运算
select ename,sal*12 as yearsal from emp; # 使用as给查询结果的列重命名
select ename,sal*12 yearsal from emp; # as可以省略
select ename,sal*12 '年薪' from emp; # 别名中有中文需要使用引号括起来

条件查询语法格式：

select
​      字段1，字段2，...
from
​      表名
where
​      条件;

执行顺序：先执行from，然后where，最后select

# 查询工资等于5000的员工姓名
select ename from emp where sal = 5000;
# 查询SMITH的工资
select sal from emp where ename = 'SMITH';
# 找出工资高于3000的员工
select ename,sal from emp where sal > 3000;
# 找出工资不等于3000的
select ename,sal from emp where sal != 3000;
# 找出工资在1100和3000之间的员工，包括1100和3000
select ename,sal from emp where sal >= 1100 and sal <= 3000;
select ename,sal from emp where sal between 1100 and 3000; 
# 找出哪些人津贴为NULL
select ename,comm from emp where comm is null;
# 找出哪些人津贴不为NULL
select ename,comm from emp where comm is not null;
# 找出哪些人没有津贴 
select ename,comm from emp where comm is null or comm = 0;
# 找出工作岗位是MANAGER和SALESMAN的员工
select ename,job from emp where job = 'SALESMAN' or job = 'MANAGER';
select ename,job from emp where job in('SALESMAN','MANAGER');
# 找出工作岗位不是MANAGER和SALESMAN的员工
select ename,job from emp where job != 'SALESMAN' and job != 'MANAGER';
select ename,job from emp where job not in('SALESMAN','MANAGER');

between and的使用注意事项

注意1：between and在使用的时候必须左小右大

between 3000 and 1100; # 查询不到任何数据

注意2：between and使用在数字方面表示闭区间

between 1100 and 3000; # 是闭区间 [1100 ~ 3000]

注意3：between and使用在字符串方面表示左闭右开

注意：and和or联合起来如果不确定运算符的优先级的时候加小括号。

7、模糊查询like

在模糊查询当中，必须掌握两个特殊的符号，一个是%，一个是_。

%代表任意个字符，_代表任意1个字符。

# 找出名字当中含有O的
select ename from emp where ename like '%O%'; # 注意不要忘记单引号
# 找出名字中第二个字母是A的
select ename from emp where ename like '_A%';
# 找出名字中有下划线的
select ename from emp where ename like '%\_%';
# 找出名字中最后一个字母是T的
select ename from emp where ename like '%T';

8、排序

使用order by关键字，默认是升序。

怎么指定升序或者降序呢？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;
# 按照工资的降序排列，当工资相同的时候再按照名字的升序排列。
select ename,sal from emp order by sal desc, ename asc; # 注意：越靠前的字段越能起到主导作用。只有当前面的字段无法完成排序的时候，才会启用后面的字段。
# 找出工作岗位是SALESMAN的员工，并且要求按照薪资的降序排列。
select ename,job,sal from emp where job = 'SALESMAN' order by sal desc;

执行顺序

select 
		字段						3
from
		表名						1
where
		条件						2
order by
		....						4
	
order by是最后执行的。

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

特点：输入多行，最终输出的结果是1行。

函数	介绍
count	计数
sum	求和
avg	平均值
min	最小值
max	最大值

注意1：所有的分组函数都是对“某一组”数据进行操作的。
注意2：分组函数自动忽略NULL。

# 找出工资总和
select sum(sal) from emp;
# # 找出最高工资？
select max(sal) from emp;
# 找出最低工资？
select min(sal) from emp;
# 找出平均工资？
select avg(sal) from emp;
# 找出总人数？
select count(*) from emp;
select count(ename) from emp;
# 找出工资高于平均工资的员工
select ename,sal from emp group by sal having sal > (select avg(sal) from emp);

找出工资高于平均工资的员工

select ename,sal from emp where sal > avg(sal);  //ERROR 1111 (HY000): Invalid use of group function
错误信息：无效的使用了分组函数
原因：SQL语句当中有一个语法规则，分组函数不可直接使用在where子句当中。
解释：因为group by是在where执行之后才会执行的。
select		     5
	..			
from			 1
	..
where			 2
	..
group by		 3
	..
having		     4
	..
order by		 6
	..	

count(*)和count(具体的某个字段)，他们有什么区别？

count(*):不是统计某个字段中数据的个数，而是统计总记录条数。（和某个字段无关）

count(comm): 表示统计comm字段中不为NULL的数据总数量。

10、单行处理函数

特点：输入一行，输出一行

# 计算每个员工的年薪
select ename,(sal+comm)*12 as yearsal from emp;  # NULL
重点：所有数据库都是这样规定的，只要有NULL参与的运算结果一定是NULL。
使用ifnull函数：
select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;

11、group by 和 having

关键字	介绍
group by	对某个字段进行分组
having	对分组之后的数据进行再次过滤

• 分组函数一般都会和group by联合使用，这也是它叫分组函数的原因
• 任何一个分组函数（count sum avg max min）都是在group by语句执行结束之后才会执行的。
• 当一条sql语句没有group by的话，整张表的数据会自成一组。
  select ename,max(sal),job from emp group by job;
  以上在mysql当中，查询结果是有的，但是结果没有意义，在Oracle数据库当中会报错。语法错误。
  Oracle的语法规则比MySQL语法规则严谨。

记住一个规则：当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段。

# 每个工作岗位的平均薪资
select job,avg(sal) from emp group by job;
# 出每个部门不同工作岗位的最高薪资
select deptno,job,max(sal) from emp group by deptno, job;
# 找出每个部门的最高薪资，要求显示薪资大于2900的数据
select deptno,max(sal) from emp group by deptno having max(sal) > 2900; # 效率低，不推荐
select deptno,max(sal) from emp where sal > 2900 group by deptno; # 效率较高，建议能够使用where过滤的尽量使用where
# 找出每个部门的平均薪资，要求显示薪资大于2000的数据
select deptno,avg(sal) from emp group by deptno having max(sal) > 2000;

12、一个完整SQL的写法和执行顺序 (重要)

select		     5
	..			
from			 1
	..
where			 2
	..
group by		 3
	..
having		     4
	..
order by		 6
	..
limit            7

13、去重

使用distinct关键字进行去重
注意：distinct只能出现在所有关键字的前面。

# 统计岗位数量
select count(distinct job) from emp;
select distinct job from emp; # 对job字段进行去重
select distinct job,deptno from emp; # 对job和deptno的联合结果进行去重
错误写法：select job, distinct deptno from emp;