mysql学习记录
### 闲谈为了理想，肝一波java web！
文中所需的sql数据库我已经上传到附件了！
### 前置知识
SQL语句分类
DQL-->查询语句，凡是select语句都是DQL(数据查询语言)
完整语法格式(右边数字代表执行顺序)
```
select * 5
from * 1
where * 2
group by * 3
having * 4
order by *； 6
```
DML-->insert delete update，对表中的数据进行增删改(数据操作语言)
DDL-->create drop alter，对表中的数据进行增删改(数据定义语言)
TCL-->commit提交事务，rollback回滚事务
DCL-->grant授权、revoke撤销权限等。
导入数据-->source sql文件路径
desc table_name;查表结构
select database();查看当前使用的数据库
select version(); 查看当前数据库版本
show create table table_name; 查看创建表的语句
### 查询语句
#### 简单的查询语句
`select 字段名1,字段名2,字段名3,...... from table_name;`
所有的sql语句都已“;”结尾；sql语句不区分大小写。
`select ENAME,SAL * 12 from emp;` sql语句可以参与到数学运算
`select ENAME,SAL * 12 as yeadsal from emp;` sql语句给查询出来的列重命名使用as关键字
#### 条件查询
##### and&or
and代表且，or代表或；and优先级高于or，想要or先执行可用小括号
语法格式
`select 字段名1，字段名2， from table_name where 条件语句；`
案例一 ：查询工资等于5000的员工姓名？
-->`select ENAME from emp where sal = 5000；`
案例二：查询SMITH的工资？
--> `select ENAME,SAL from emp where ENAME='SMITH';` 字符串要加单引号
案例三：查询工资不等于3000的
-->`select ENAME,SAL from emp where SAL <> 3000;` mysql中<>代表不等于的意思
##### between...and...
案例四：查询工资在1000到3300之间的员工
1.`select ename,sal from emp where sal between 1000 and 3300;` 使用between语法
2.`select ename,sal from emp where sal>=1000 and sal<=3300;` 常规语法
##### is null
案例五：找出津贴为null的员工
`select ename,sal,comm from emp where comm is null;`
##### not
案例六：找出津贴不为null的员工
`select ename,sal,comm from emp where comm is not null;`
##### in等同于or
案例七：找出工作岗位是MANAGER和SALESMAN的员工
`select ename,job,comm from emp where job = 'MANAGER' or job = 'SALESMAN';`使用of
`select ename,job,comm from emp where job in ('MANAGER','SALESMAN');`使用in
##### 模糊查询like
在模糊查询当中，有两个特殊符号，%代表任意多个字符，_代表任意一个字符
案例八-1：找出名字中带有B的员工
`select ename,sal from emp where ename like '%B%';`like之后的字符要加引号，使用%匹配多个字符
案例八-2：找出名字中第二个字母为A的员工
`select ename,sal from emp where ename like '%B%';`使用_匹配一个字符
#### 排序
##### order by
-->order 排序 by 通过；所以order by就是通过什么排序
-->order by 排序默认是升序的，指定降序为desx，升序为asc；
案例九-1：将员工薪资以升序排列打印出员工的姓名、薪资信息；
`select ename,sal from emp order by sal;`
案例九-2：将员工薪资以降序排列当薪资一样时以姓名首字母大小写升序排列；
`select ename,sal from emp order by sal desc,ename asc;` 在执行此条语句时，**当第一条排序相等时才会执行第二条排序**
案列九-3：将职位为'SALESMAN'的员工以薪资降序排列
`select ename,job,sal from emp where job='SALESMAN' order by sal desc;`
#### 分组函数
--> 分组函数运算自动忽略null
-->分组函数不能在where之后运算
-->分组函数通常都会和group by联合使用，分组函数在group by执行之后执行
count 计数
sum 求和
avg 平均值
max 最大值
min 最小值
案列十-1：找出工资高于平均工资的员工
-->`select ename,sal from emp where sal > (select avg(sal) from emp);` 没有使用group by时使用分组函数会自成一组，并且**分组函数不能单独在where之后执行**
##### group by
案例十-2：将员工信息按工种分类显示员工的工种、最高薪资？
`select job,max(sal) from emp group by job;`
案列十-3:找出每个部门每个工种的最高薪资？
`select deptno,job,max(sal) from emp group by deptno,job;`
##### having
-->用作条件筛选，当筛选不能使用where语句进行时，使用having进行筛选
案列十-4 找出每个部门平均薪资，并显示平均薪资大于2000的部门
`select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;`
#### 单行处理函数
ifnull-->ifnull(可能出现null的函数，处理之后的数据)
distinct 查询去重
### 连接查询
根据表的连接方式划分，包括:
内连接：
等值连接
非等值连接
自连接
外连接
左外连接
右外连接
全连接
笛卡尔积现象：当两张表进行连接查询时，没有任何条件限制，最终查询结果条数为两张表记录条数的乘积
关于别名
`select e.ename,d.dname from emp e,dept d;`emp e代表之后e就是emp，e.ename就是emp下的ename字段，d.dname同理
#### 等值连接(inner)
案例十一-1：找出每一个员工的部门名称，要求显示员工名称和部门名
`select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;`这里是sql92语法！加条件之后，他依旧进行了笛卡尔积次查询，知识显示出来的是我们进行限制的
##### join...on...where
案例十一-1：找出每一个员工的部门名称，要求显示员工名称和部门名
select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;sql99语法
语法格式
```
select *
from *
join *这里应该有个inner，内连接可以省略
on 连接条件
where ...
```
#### 非等值连接(inner)
-->最大特点：连接条件中的关系是非等值连接
案列十一-2：找出每个员工的工资等级，要求显示员工名、工资、工资等级。
`select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;`
#### 自连接
-->一张表看作两张表。自己连接自己
案例十一-3：找出每个员工的上级领导，要求显示员工名和对应的领导名
`select a.ename,b.ename from emp a join emp b on a.mgr=b.empno;`
在这张表里，员工的上级领导编号就等于领导的编号！
#### 外连接
语法格式
```
select *
from *
left/right join *
on *
where *
```
假设A表和B表进行连接，使用外连接时，AB两张表中有一张表时主表，一张表时副表，主要查询主表中的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出null与之匹配
案列十二-1：找出每个员工的领导，每个员工必须查出来
`select e.ename,b.ename from emp e left join emp b on e.mgr=b.empno;`
带left左边是主表，带right右边是主表
案列十二-2：找出哪个部门没有员工？
```
select d.dname,e.deptno from emp e right join dept d on d.deptno=e.deptno where e.deptno is null;
```
#### 多表连接查询
案例十三-1：找出每一个员工的部门名称以及工资等级。
第一种写法sql92方式 ： `select e.ename,d.dname,s.grade from emp e,dept d,salgrade s where e.deptno=d.deptno and (e.sal between s.losaland s.hisal);`
第二种写法sql99方式：`select e.ename,d.dname,s.grade from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and s.hisal order by grade;`
案例十三-2：找出每一个员工的部门名称、工资等级、以及上级领导。
`select e.ename,d.dname,s.grade`
`from emp e`
`join dept d`
`on e.deptno=d.deptno`
`join salgrade s`
`on e.sal between s.losal and s.hisal`
`left join emp b`
`on e.mgr=b.ename;`
### 子查询
子查询语法
```
selct
..(select).
from
..(select).
where
..(select).
```
##### from后接select
案例十四-1：找出每个部门平均薪水的薪资等级
select deptno,avg(sal) as avgsal from emp group by deptno;每个部门的平均薪水
`select deptno,t.avgsal,s.grade`
`from (select deptno,avg(sal) as avgsal from emp group by deptno) as t`
`join salgrade s`
`on t.avgsal between s.losal and s.hisal;`
### 联合查询
#### union
案例十五-1：找出工作岗位是SALESMAN和MANAGER的员工?
select ename,job from emp where job='SALESMAN' union select ename,job from emp where job='MANAGER';
#### limit分页查询
limit是mysql'特有的，其他数据库中没有的
limit取结果集中的部分数据，这是他的作用
语法
`limit startindex，length；`
startindex代表起始位置，从0开始，0表示第一条记录
length代表取几个
案例十六-1：取出工资前5名
`select ename,sal from emp order by sal desc limit 0,5;`
### 增删改查(GRUD)
建表语法格式
```
create table table_name(
字段名1 数据类型，
字段名2 数据类型，
字段名3 数据类型，
....
)
```
mysql中常见的数据类型
-->int、float、double、char、varchar、date、BLOB、CLOB等等
-->其中BLOB主要存储二进制大对象(图片、视频等流媒体信息)
-->CLOB存储字符大对象(4GB字符串)
案例十七-1：创建表要求如下
创建学生表：
学生信息包括：
学号、姓名、性别、班级编号、生日
学号(bigint)
姓名(varchar)
性别(char)
班级编号(int)
生日(cahr)
`create table t_stu(`
`nu bigint,`
`name varchar(255),`
`sex char(1),`
`classno varchar(255),`
`birth char(10)`
`);`
#### insert into表中插入数据
-->insert语句
-->语法格式： insert into tables_name(字段名1，字段名2，字段名3，....)values(值1，值2，值3.....)
-->字段的数量和值的数量相同，并且数据类型要对应
-->当insert 语句执行成功之后，表格当中必然会多一条记录
案列：insert into t_stu(nu,name,sex,classno,birth) values('1','fge7s','1','3','2021-10-7');
需要注意的是，这里所有的默认字段为null，即insert into t_stu(name) values('ww');
!(https://fge7supload-1307552994.cos.ap-shanghai.myqcloud.com/markdown/image-20211007115613675.png)
改变默认值的关键字为default，下面案例图中可以看到，不设置values时，默认sex为1；
案例：
`create table t_stu2(`
`nu bigint,`
`name varchar(255),`
`sex char(1) default 1,`
`classno varchar(255),`
`birth char(10)`
`);`
!(https://fge7supload-1307552994.cos.ap-shanghai.myqcloud.com/markdown/image-20211007115939007.png)
#### drop删除表
`drop table if exists table_name;`
!(https://fge7supload-1307552994.cos.ap-shanghai.myqcloud.com/markdown/image-20211007122256084.png)
#### as复制表
-->语法 create table table_name as select语句
-->就是将查询结果当做表复制出来
-->语法：insert into table_name select语句
-->将查询结果插入到表中
#### update修改表中数据
-->关键字 update
-->语法：update table_name set 字段名=值...where....
-->没有条件整张表数据全部更新
!(https://fge7supload-1307552994.cos.ap-shanghai.myqcloud.com/markdown/image-20211007122656931.png)
#### delete删除表中数据
-->语法格式：delete from table_name where 条件；
-->没有条件全部删除
注意delete删除之后是可以回复的，使用truncate table table_name删除无法恢复
### 表设计
约束（constraint）
-->约束：是在创建表的时候给表中的字段添加约束，添加约束时为了保证表中数据的合法性、有效性、完整性
-->常见约束有：非空约束(not null)唯一约束(unique) 主键约束(promary key)外键约束(foreign key)
非空约束(not null):约束的字段不能为null
唯一约束(unique) ：约束的字段不能重复
主键约束(promary key) ：约束的字段既不能重复也不能为null
外键约束(foreign key)
#### 非空约束案例(nut null)
`create table t_stu(`
`id int,`
`username varchar(255) not null,`
`password varchar(255) not null);`
#### 唯一性约束案例(unique)
`create table t_stu(`
`id int unique,`
`username varchar(255) unique,`
`password varchar(255))`
#### 主键约束案例(primary key)
主键值不能为空和null，并且主键约束使用`auto_increment`之后可以自增长
一张表的主键约束只能有一个
`create table t_stu(`
`id int primary key,`
`username varchar(255),`
`password varchar(255))`
### 事务
一个事务是一个完整的业务逻辑单元，不可再分
和事务相关的DML语句只有insert update delete
事务的存在保证数据的完整性安全性
通常一个事务需要多条DML语句完成
### 练习题
#### 题1：取得每个部门最高薪水的人员名称
select deptno,max(sal) as maxal from emp group by deptno;
+--------+---------+
| deptno | maxal |
+--------+---------+
| 10 | 5000.00 |
| 20 | 3000.00 |
| 30 | 2850.00 |
+--------+---------+
`select ename,sal,e.deptno from ( select deptno,max(sal) as maxsal from emp group by deptno) as t,emp as e where t.deptno=e.deptno and t.maxsal=e.sal;`
#### 题2：哪些人的薪水在部门的平均薪水之上
select deptno,avg(sal) as avgsal from emp group by deptno;
`select e.ename,e.sal,t.* from (select deptno,avg(sal) as avgsal from emp group by deptno) as t join emp e on e.deptno=t.deptno and e.sal>t.avgsal;`
#### 题3：取得部门中所有人的而平均薪水等级
`select e.deptno,avg(s.grade) from emp e join salgrade s on e.sal between s.losal and s.hisal group by e.deptno;` 总结的不错,谢谢分享. 多谢分享，这是好东西。 感谢分享！ 谢谢分享，楼主写的很详细 上学的时候学这个，头都晕了，再来温习一下 楼主阔以的~~~~~~~~ 感觉像是黑马的教程。加油。 总结的不错，加油 感谢大佬分享，相信在分享知识的同时也是在自我提高与促进，也感谢平台！
页:
[1]
2