MySQL学习教程（超详细步骤）

一、SQL简介

1.SQL的概述

Structure Query Language(结构化查询语言)简称SQL，它被美国国家标准局(ANSI)确定为关系型数据库语言的美国标准，后被国际化标准组织(ISO)采纳为关系数据库语言的国际标准。数据库管理系统可以通过SQL管理数据库；定义和操作数据，维护数据的完整性和安全性。

2.SQL、DB、DBMS分别是什么，它们之间的关系

DB: DataBase（数据库，数据库实际上在硬盘上以文件的形式存在）

DBMS: DataBase Management System（数据库管理系统，常见的有：MySQL Oracle DB2 Sybase SqlServer…）

SQL: 结构化查询语言，是一门标准通用的语言。标准的sql适合于所有的数据库产品。
SQL属于高级语言。SQL语句在执行的时候，实际上内部也会先进行编译，然后再执行sql。（sql语句的编译由DBMS完成。）

DBMS负责执行sql语句，通过执行sql语句来操作DB当中的数据。

3.SQL语句的分类

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

二、数据库的数据类型

1.MySQL中常用字段数据类型

MySQL中的字段数据类型	Java属性数据类型
tinyint / smallint / int	Integer
bigint	Long
varchar / char / text系列	String
datetime	LocalDateTime
decimal	BigDecimal

三、MySQL的安装与卸载

1.MySQL安装

第一步:打开MySQL官网,进入官网首页,点击DOWNLOADS,点击最下面: MySQL Community(GPL)Downloads

然后,点击: MySQL Installer for Windows

然后,修改下载版本(建议5.7版本),点击Download

然后,点击: No thanks, just start my download ，这里可以跳过注册，直接下载

第二步:安装包下载完成后,找到安装包文件位置,然后双击安装包

然后,点击运行

开始安装

然后,点击Next

然后,点击Execute 执行

然后,点击Next

然后,点击Next

然后,点击Next

设置用户名,密码。然后,点击Next

然后,点击Next

然后,点击Next
修改补正:下图圈画的路径地址不是MySQL安装地址,是MySQL安装程序可以通过更新位于以下位置的文件和文件夹的权限来保护服务器的数据目录,用处不大了解即可

然后,点击Execute 执行

然后,点击Finish

然后,点击Next

然后,点击Finish

第三步:验证是否安装成功,点击Windows,开始搜索输入Mysql,并选择第一个打开

然后出现黑窗口,输入MySQL设置的密码

点击回车,出现以下内容证明安装成功!

第四步:配置环境变量
1.找到MySQL安装路径(注意是bin文件夹),将其复制

2.右键点击此电脑→属性→高级系统设置→环境变量

3.点击“环境变量”后,在“系统变量”中选择“path”，然后点击"编辑"

4.点击“新建”后，将复制的Mysql路径粘贴上去，然后点击“确定”,“确定”,在“确定”。

这样,环境变量就配置好了,我们来测试一下:
window+R键弹出运行框，输入cmd命令点击回车,进入dos命令窗口，输入“mysql -uroot -p"点击回车，再输入密码，点击回车，出现下面界面则配置成功。

2.MySQL卸载

1、双击安装包，点击运行，然后点击remove。勾选要卸载的MySQL Server 5.7.43。一路Next就完了!

2、手动删除Program Files中的MySQL目录。

3、手动删除ProgramData目录（这个目录有可能是隐藏的,需要打开隐藏文件）中的MySQL。

四.数据表与数据库的基本操作

1.数据库的基本操作

1.1.登录mysql数据库管理系统,window+R键弹出运行框，输入cmd命令点击回车,进入dos命令窗口：mysql -u用户名 -p密码

1.2.查看有哪些数据库命令: show databases; (这个不是SQL语句，属于MySQL的命令。)

1.3.创建属于我们自己的数据库命令: create database blog_csdn; (这个不是SQL语句，属于MySQL的命令。),并从新查看有哪些数据库: show databases;

1.4.使用blog_csdn数据命令: use blog_csdn; (这个不是SQL语句，属于MySQL的命令。)

1.5.查看当前使用的数据库中有哪些表命令: show tables; (这个不是SQL语句，属于MySQL的命令。)

1.6.初始化数据命令: source +SQL脚本文件路径

运行结束后,查询数据表个数: show tabales; 查询得知,一共3张数据表

1.7.删除数据库命令：drop database blog_csdn;

1.8.查看当前使用的数据库命令：select database();

1.9.查看mysql的版本号: select version();

1.10.结束一条SQL语句命令: \c

1.11.退出MySQL命令: exit

1.12.查看其他数据库中的数据表: show tables from sys;

2.数据表的基本操作

博客所用到的三张数据表: dept 部门表,emp 员工表,salgrade 工资等级表

2.1.创建数据表及新增表中的数据

2.1.1.创建数据表:

语法:
create table 表名(
			字段名1 数据类型,
			字段名2 数据类型,
			字段名3 数据类型,
			....
		);

2.1.1.1.关于MySQL当中字段的常见数据类型:

数据类型	概述
int	整数型(java中的int)
bigint	长整型(java中的long)
float	浮点型(java中的float double)
char	定长字符串(String)
varchar	可变长字符串(StringBuffer/StringBuilder)
date	日期类型 （对应Java中的java.sql.Date类型）
BLOB	二进制大对象（存储图片、视频等流媒体信息） Binary Large OBject（对应java中的Object）
CLOB	字符大对象（存储较大文本，比如，可以存储4G的字符串。） Character Large OBject（对应java中的Object）

2.1.1.2.char和varchar怎么选择:
在实际的开发中，当某个字段中的数据长度不发生改变的时候，是定长的，例如：性别、生日等都是采用char。
当一个字段的数据长度不确定，例如：简介、姓名等都是采用varchar。
注意: 表名在数据库当中一般建议以：t_或者tbl_开始。

2.1.1.3.创建学生表：
学生信息包括：学号：bigint、姓名：varchar、性别：char、班级编号：int、生日：char

create table t_student(
			no bigint,
			name varchar(255),
			sex char(1),
			classno varchar(255),
			birth char(10)
		);

2.1.2.(新增)插入数据表中的数据(insert)

2.1.2.1.语法格式：

语法:
insert into 表名(字段名1,字段名2,字段名3,....) values(值1,值2,值3,....)

重点要求：字段的数量和值的数量相同，并且数据类型要对应相同。

2.1.2.2.向blog_csdn数据库中的t_student数据表中新增数据, no: 1 , name: ‘zhangsan’ , sex: ‘1’ , classno: ‘gaosan1ban’ , birth: ‘1950-10-12’: insert into t_student(no,name,sex,classno,birth) values(1,‘zhangsan’,‘1’,‘gaosan1ban’, ‘1950-10-12’);

也可以不按照数据表字段顺序插入数据: insert into t_student(name,sex,classno,birth,no) values(‘lisi’,‘1’,‘gaosan1ban’, ‘1950-10-12’,2);

创建数据表,并在字段sex后指定默认值:

create table t_student(
		no bigint,
		name varchar(255),
		sex char(1) default 1,
		classno varchar(255),
		birth char(10)
	);

需要注意：当一条insert语句执行成功之后，表格当中必然会多一行记录。即使多的这一行记录当中某些字段是NULL，后期也没有办法在执行。insert语句插入数据了，只能使用update进行更新。
2.1.2.3.一次插入多行数据: insert into t_student (no,name,sex,classno,birth) values (3,‘rose’,‘1’,‘gaosi2ban’,‘1952-12-14’),(4,‘laotie’,‘1’,‘gaosi2ban’,‘1955-12-14’);

2.1.3复制数据表

语法:
	create table 表名 as select语句;
	将查询结果当作数据表创建出来.

2.1.4将查询结果插入到数据表中

语法:
insert into 表名 select语句;
将查询结果插入到数据表中

2.2.查询数据表及表中的数据

2.2.1.简单查询

2.2.1.1.查看数据表的结构及字段信息MySQL命令：desc dept; (数据表名)

2.2.1.2.查看数据表中的数据: select * from dept;

2.2.1.3.查看创建数据表的SQL语句: show create table emp;

2.2.1.4.查询部分字段数据和全部字段数据
语法格式: select 字段名1,字段名2,字段名3,… from 表名;
注：
1、任何一条sql语句以“;”结尾。
2、sql语句不区分大小写。

查询员工的年薪(字段可以参与数学运算),并且给查询结果的列重命名: select ename,sal * 12 as yearsal from emp; as关键字用于起别名,可以省略用空格代替

起的别名是中文,我们需要给中文单引号括起来,‘年薪’: select ename,sal * 12 ‘年薪’ from emp;

2.2.2.条件查询

语法格式: select
字段,字段…
from
表名
where
条件;
执行顺序：先from，然后where，最后select

2.2.2.1.查询工资等于5000的员工姓名: select ename from emp where sal = 5000;

2.2.2.2.查询SMITH的工资: select sal from emp where ename = ‘SMITH’;

2.2.2.3.找出工资在1100和3000之间的员工,包括1100和3000: select ename,sal from emp where sal>=1100 and sal<=3000;
使用between and 关键字: select ename,sal from emp where sal between 1100 and 3000; between and在使用的时候必须左小右大。

between and除了可以使用在数字方面之外，还可以使用在字符串方面:
select ename from emp where ename between ‘A’ and ‘C’; 包括A,B开头的姓名,但是不包括C开头的。左闭右开。

2.2.2.4.找出哪些人津贴为NULL？
在数据库当中NULL不是一个值，代表什么也没有，为空。空不是一个值，不能用等号衡量。必须使用 is null或者is not null: select ename,sal,comm from emp where comm is null;

找出哪些人津贴不为NULL: select ename,sal,comm from emp where comm is not null;

找出哪些人没有津贴: select ename,sal,comm from emp where comm is null or comm=0;

2.2.2.5.and(两者都是)和or(或者)还有in(或者)
找出工作岗位是MANAGER和SALESMAN的员工: select ename,job from emp where job=‘MANAGER’ or job=‘SALESMAN’;

找出工作岗位是MANAGER并且还是SALESMAN的员工: select ename,job from emp where job=‘MANAGER’ and job=‘SALESMAN’;

and和or联合起来用：找出薪资大于1000的并且部门编号是20或30部门的员工: select ename,sal,deptno from emp where sal >1000 and deptno = 20 or deptno = 30;

但是,查询出来的数据根本不对(如上图),这块小伙伴们注意啦,因为and优先级比or高,所以这段SQL语句:select ename,sal,deptno from emp where sal > 3000 and deptno = 20 将会自动组队,而这段SQL语句:or deptno = 30 却将部门=30的所有员工全部查出来,所以,部门=30的员工里有一名员工的薪资小于1000。当运算符的优先级不确定的时候加小括号。

所以正确SQL语句如下: select ename,sal,deptno from emp where sal > 1000 and (deptno = 20 or deptno = 30);

**in等同于or：找出工作岗位是MANAGER和SALESMAN的员工: select ename,job from emp where job = ‘MANAGER’ or job = ‘SALESMAN’;
同样也可以这样写: select ename,job from emp where job in(‘MANAGER’,‘SALESMAN’); 执行出来的结果是一样的 **

not in: 不在这几个值当中:查询出工作岗位是MANAGER和SALESMAN的员工: select ename,job from emp where job not in(‘MANAGER’,‘SALESMAN’);

2.2.2.6.模糊查询(like),在模糊查询当中，必须掌握两个特殊的符号，一个是%: %代表任意多个字符，一个是_: _代表任意1个字符。
找出名字当中含有O的: select ename from emp where ename like ‘%O%’;

找出名字中第二个字母是A的: select ename from emp where ename like ‘_A%’;

找出名字中最后一个字母是T的: select ename from emp where ename like ‘%T’;

转译字符: 由于_为特殊字符,所以在数据表中查询带 ‘_’ 的数据时,通常用使用转义字符,_的转义字符为: \ _

2.2.3.排序(asc表示升序，desc表示降序)

语法格式: select
字段
from
表名
order by
通过什么字段排序;(默认是升序)

2.2.3.1.按照工资升序，找出员工名和薪资

2.2.3.2.按照工资降序排列，当工资相同的时候再按照名字的升序排列: 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;

2.2.4.分组函数(count 计数,sum 求和,avg 平均值,max 最大值,min 最小值) 所有的分组函数都是对“某一组”数据进行操作的。

注意:
分组函数一共5个。
分组函数还有另一个名字：多行处理函数。多行处理函数的特点：输入多行，最终输出的结果是1行。
2.2.4.1.分组函数自动忽略null: select sum(comm) from emp; 这条 SQL 语句执行的结果是null还是员工的津贴和?

答案是: 员工的津贴和.因为分组函数自动忽略null,所以null不参与到运算当中,所以最后得到的是员工的津贴和.(如上图示👆)

2.2.4.2.五种分组函数:
1.找出工资总和: select sum(sal) from emp;
2.找出工资平均值: select avg(sal) from emp;
3.找出工资最大值: select max(sal) from emp;
4.找出工资最小值: select min(sal) from emp;

5.找出总人数: select count(*) from emp;或者: select count(ename) from emp;
count( * )和count(具体的某个字段)，它们有什么区别？
count( * ):不是统计某个字段中数据的个数，而是统计总记录条数。（和某个字段无关）
count(comm): 表示统计comm字段中不为NULL的数据总数量。

2.2.4.3.找出工资高于平均工资的员工: select ename,sal from emp where sal > avg(sal); //ERROR 1111 (HY000): Invalid use of group function
以上的错误信息：无效的使用了分组函数
原因：SQL语句当中有一个语法规则，分组函数不可直接使用在where子句当中。因为group by是在where执行之后才会执行的。

解决方法: 嵌套select子查询,如下图。

2.2.4.4分组函数也能组合起来用：select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;

2.2.5.单行处理函数: 输入一行,输出一行。

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

**经修改,SQL语句为: select ename,(sal+ifnull(comm,0))*12 yearsal from emp; **

ifnull() 空处理函数:
if null (可能为null的数据,被当做什么处理) : 属于单行处理函数。
select ename,ifnull(comm,0) as comm from emp;

2.2.6.group by 和 having

group by ： 按照某个字段或者某些字段进行分组。
having : having是对分组之后的数据进行再次过滤。

2.2.6.1.找出每个工作岗位的最高薪资: select max(sal),job from emp group by job;

注意：分组函数一般都会和group by联合使用，这也是为什么它被称为分组函数的原因。
并且任何一个分组函数（count sum avg max min）都是在group by语句执行结束之后才会执行的。
当一条sql语句没有group by的话，整张表的数据会自成一组。
当一条sql语句中有group by的话,那么 select 后只能跟随分组函数和参与分组的字段: select job,avg(sal) from emp group by job; 根据job字段进行分组,分组函数:avg(sal),所以 select 后只能跟随job和avg(sal)

2.2.6.2.每个工作岗位的平均薪资: select job,avg(sal) from emp group by job;

2.2.6.3.找出每个部门不同工作岗位的最高薪资: select job,deptno,max(sal) from emp group by job,deptno;

2.2.6.4.找出每个部门的最高薪资，要求显示薪资大于2900的数据: select deptno,max(sal) from emp group by deptno having max(sal) > 2900;

但是效率不高,可以直接在查询的时候设立条件,直接where sal > 2900,修改如下: select deptno,max(sal) from emp where sal > 2900 group by deptno;(效率较高,比较推荐)

2.2.6.5.找出每个部门的平均薪资，要求显示薪资大于2000的数据: select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;

注意:select deptno,avg(sal) from emp where avg(sal) > 2000 group by deptno; // 错误了。因为where后面不能使用分组函数
这种情况只能使用having过滤。

2.2.7.关于查询结果集去重操作(关键字:distinct)

查询工作岗位: select job from emp;

去重操作后的查询工作岗位: select distinct job from emp;

以下的SQL语句出现错误: 记住：distinct只能出现在所有字段的最前面。

假如,distinct关键字后出现两个字段,或者多个字段,那么到底是给distinct关键字后的字段去重,还是所有字段去重?
答案:是给所有字段去重!
联合去重: 假如distinct关键字后跟随的字段越多,那么这条SQL语句的去重性就越差,因为数据的重复性受到了限制

统计岗位的数量: select count(distinct job) from emp;

2.2.8.一个完整的DQL语句怎么写

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

2.2.9.连接查询

2.2.9.1.什么是连接查询: 实际开发中，大部分情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终的结果。那为什么不把所有数据放到一张数据表中? 因为:数据会存在大量的重复，导致数据的冗余。

2.2.9.2.连接查询分类:
内连接：等值连接、非等值连接、自连接
外连接：左外连接（左连接）、右外连接（右连接）
全连接:（这个不讲，很少用！）

2.2.9.3.笛卡尔积现象
查询每个员工的部门名称，要求显示员工名和部门名: select e.ename,d.dname from emp e , dept d;

通过执行以上的SQL语句发现: 一共查询出56条数据。为什么? 因为当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。emp(员工表)有14条数据,dept(部门表)有4条数据,所以查询出56条数据

怎么避免笛卡尔积现象: 当然是加条件进行过滤。
思考：避免了笛卡尔积现象，会减少记录的匹配次数吗？ 不会，次数还是56次。只不过显示的是有效记录。

2.2.9.4.内连接之等值连接: 最大特点是：条件是等量关系。

查询每个员工的部门名称，要求显示员工名和部门名(理解:员工名是emp表中的字段,部门名是dept表中的字段,所以这块要用到联表查询,我们用内连接中的等值连接): select e.ename , d.dname from emp e inner join dept d where e.deptno = d.deptno;(SQL99语法)

除了SQL99语法,还可以用SQL92语法编写(了解),还是推荐SQL99语法,因为语法结构更清晰一些：表的连接条件和后来的where条件分离了。
查询每个员工的部门名称，要求显示员工名和部门名: select e.ename , d.dname from emp e ,dept d where e.deptno = d.deptno;

2.2.9.5.内连接之非等值连接：最大的特点是：连接条件中的关系是非等量关系。inner可以省略。

2.2.9.6.内连接之自连接：最大的特点是：一张表看做两张表。自己连接自己。这个挺刺激,一起好好学😊
找出每个员工的上级领导，要求显示员工名和对应的领导名:
首先: 我们看一下emp数据表

字段只需要员工/领导姓名(ename),员工的领导编号(mgr),员工/领导编号(empno)

那么回过头,我们看问题: 找出每个员工的上级领导，要求显示员工名和对应的领导名
既然员工有可能是领导,领导也有可能是员工,那么干脆给emp表起两个别名,代表不同的含义,一个是员工,一个是领导: select a.ename ‘员工姓名’ , a.mgr ‘员工领导编号’ , b.ename ‘员工领导姓名’ from emp a inner join emp b on a.mgr = b.empno;

2.2.9.7.外连接: 假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。
外连接的分类
左外连接（左连接）：表示左边的这张表是主表。
右外连接（右连接）：表示右边的这张表是主表。
左连接有右连接的写法，右连接也会有对应的左连接的写法。
找出每个员工的上级领导？（所有员工必须全部查询出来。): select a.ename ‘员工姓名’,b.ename’领导姓名’ from emp a left outer join emp b on a.mgr = b.empno;

转换成右外连接: select a.ename ‘员工姓名’,b.ename’领导姓名’ from emp b right outer join emp a on a.mgr = b.empno;
外连接最重要的特点是：主表的数据无条件的全部查询出来。

找出哪个部门没有员工: select e.deptno,d.deptno,d.dname from dept d left outer join emp e on e.deptno = d.deptno;

所以,正确的SQL语句为: select e.deptno,d.deptno,d.dname from dept d left outer join emp e on e.deptno = d.deptno where e.ename is null;

**2.2.9.8.三张表连接查询: 找出每一个员工的部门名称以及工资等级。
这种情况,大家不要着急,可以先查询出每一个数据表的数据: select *from emp; select *from dept; select * from salgrade; 对于大家来说,非常简单的查询! **

拿到数据后,我们分步骤处理: 找出每一个员工的部门名称: select e.ename,d.dname from emp e left outer join dept d on e.deptno = d.deptno;

我们继续处理问题: 以及工资等级。这个时候大家就懵了,还得连接薪资等级表,这怎么连呢?重点来了: select e.ename,d.dname from emp e left outer join dept d on e.deptno = d.deptno;这段SQL语句,从from关键字往后,一直到on关键字后的条件结束,这一小段都属于emp e表和dept d表的左外连接查询(emp e表是主表),所以在连一个表就应该在on关键字以及其条件后继续加连接查询: select e.ename ‘员工姓名’,d.dname ‘部门名称’,s.grade ‘工资等级’ from emp e left outer join dept d on e.deptno = d.deptno left join salgrade s on e.sal between s.losal and s.hisal;

再来一个,找出每一个员工的部门名称、工资等级、以及上级领导: select e.ename ‘员工姓名’ , e.deptno’部门编号’ , e.mgr ‘领导编号’ , d.dname from emp e join dept d on e.deptno = d.deptno;

继续处理,找出每一个员工的部门名称、工资等级、以及上级领导: select e.ename ‘员工姓名’ , e.deptno’部门编号’ , e.mgr ‘领导编号’ , 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;

继续处理,找出每一个员工的部门名称、工资等级、以及上级领导: select e.ename ‘员工姓名’ , e.deptno’部门编号’ , e.mgr ‘领导编号’ , d.dname’部门名称’ , s.grade’工资等级’ , e1.ename’领导名字’ from emp e join dept d on e.deptno = d.deptno inner join salgrade s on e.sal between s.losal and s.hisal left outer join emp e1 on e.mgr = e1.empno;

2.2.10.子查询

2.2.10.1.什么是子查询: select语句当中嵌套select语句，被嵌套的select语句是子查询。

子查询可以出现在哪里:

select
	..(select).
from
	..(select).
where
	..(select).

2.2.10.2.where子句中使用子查询
找出高于平均薪资的员工信息: select * from emp where sal > avg(sal); 错误的写法，where后面不能直接使用分组函数。
正确做法: 分步处理,
第一步: 算出平均工资: select age(sal) from emp;
第二步: 找出高于平均薪资的员工信息: select * from emp where (sal > select age(sal) from emp);

2.2.10.3.from子句中使用子查询
找出每个部门平均薪水的薪资等级:
分步:第一步:找出每个部门的平均薪资: select d.dname,avg(sal) avgsal from dept d ,emp group by dname;

第二步:将上调SQL语句查询出来的数据当做一张表,起别名为 t ,这样解决下一步: 找出每个部门平均薪水的薪资等级: select t.*,s.grade from (select deptno,avg(sal) avgsal from emp group by deptno) t inner join salgrade s on t.avgsal between s.losal and s.hisal;

帮助大家理解和几个注意点:
1.在做第一步的时候,一定要给 avg(sal)函数起别名,否则到第二步的时候SQL语句会报错
2.将第一步查询出来的数据表起别名,并与别的表进行连接,这是一种嵌套方式,比较常用,在以后的开发中,用select语句查询出对自己有用的数据表而进行继续的连接查询
3.内连接on关键字后跟随的条件: t.avgsal表示第一步SQL语句中的查询字段avg(sal) avgsal,所以条件是: on t.avgsal between s.losal and s.hisal;

找出每个部门平均的薪水等级:
分步:第一步:找出每个员工的薪水等级: select ename,sal,deptno,grade from emp,salgrade where sal between losal and hisal;

帮助理解:首先我们查询出员工表(emp)和薪资等级表: select * from emp; select * from salgrade; 怎么判断薪资等级:根据表中的sal字段数据进行比较匹配

我们将第一步查询出来的数据当作一张表,并起别名 t 这样的话,就好处理一些.
第二步: 找出每个部门平均的薪水等级: select avg(t.grade),t.deptno from (select ename,sal,deptno,grade from emp,salgrade where sal between losal and hisal) t,dept group by t.deptno;

2.2.10.4.在select后面嵌套子查询: 找出每个员工所在的部门名称，要求显示员工名和部门名: select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;

但是这里为了学习子查询,select 后嵌套 select ,所以SQL语句应该这么写: select e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e; (了解)

2.2.11.union关键字(可以将查询结果集相加)

找出工作岗位是SALESMAN和MANAGER的员工:
第一种: select ename,job from emp where job = ‘SALESMAN’ or job = ‘MANAGER’;
第二种: select ename,job from emp where job in (‘SALESMAN’,‘MANAGER’);
第三种: select ename,job from emp where job = ‘SALESMAN’ union select ename,job from emp where job = ‘MANAGER’;

2.2.12.limit(分页查询)

2.2.12.1.limt是MySQL特有额的,其他数据库中基本没有,不通用,Oracle中有一个相同的机制,叫做rownum。

2.2.12.2.limit取结果集中的部分数据,这它的作用

2.2.12.3.语法机制:
limit startIndex , length
startIndex 表示起始位置
length表示取几个

2.2.12.4.取出工资前5名的员工,思路:降序取前5个: select ename,sal from emp order by sal desc limit 0,5; 另一种写法: select ename,sal from emp order by sal desc limit 5; (将startIndex参数:0 省略)

找出工资排名在第四到第9的员工: select ename,sal from emp order by sal desc limit 3,6; 注意: limit 的两个参数: 默认从0起始位置开始(可以省略),取几个数据,大家别把 length 参数当做取到这个下标的数据参数

2.2.12.5.limit的执行顺序

limit是sql语句最后执行的一个环节：
	select			5
		...
	from			1
		...		
	where			2
		...	
	group by		3
		...
	having			4
		...
	order by		6
		...
	limit			7
		...	;

2.2.12.6.通用的标准分页sql
每页显示3条记录：
第1页：0, 3
第2页：3, 3
第3页：6, 3
第4页：9, 3
第5页：12, 3

每页显示pageSize条记录：
第pageNo页：(pageNo - 1) * pageSize, pageSize

pageSize是什么: 是每页显示多少条记录
pageNo是什么: 显示第几页

java代码{
	int pageNo = 2; // 页码是2
	int pageSize = 10; // 每页显示10条
	limit (pageNo - 1) * pageSize, pageSize
}

2.3.修改数据表中的数据

2.3.1.语法格式:

语法:
update 表名 set 字段名1=值1,字段名2=值2... where 条件;
注意：没有条件整张表数据全部更新。

2.3.1.1.将部门10的LOC修改为SHANGHAI，将部门名称修改为RENSHIBU: update dept1 set loc = ‘SHANGHAI’, dname = ‘RENSHIBU’ where deptno = 10;

2.3.1.2.更新所有记录: update dept1 set loc = ‘x’, dname = ‘y’;

2.4.删除数据表及删除表中的数据

2.4.1删除数据表

2.4.1.1.删除数据表: drop table t_student;
2.4.1.2.当这个表存在的话删除: drop table if exists t_student;

2.4.2删除数据表中的数据

2.4.2.1.语法格式:

语法:
	delete from 表名 where 条件;
	注意:没有条件全部删除

2.4.2.2.删除10部门数据: delete from dept1 where deptno = 10; oracle不支持这种写法
2.4.2.3.删除所有记录: delete from dept1;

2.4.2.4.删除数据较多的数据表:

语法:
	truncate table 表名;
	数据表被截断,不可回滚,数据永久丢失

五、约束

1.什么是约束,常见的约束有哪些

约束: 在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性、有效性、完整性。

常见的约束有哪些:
非空约束(not null)：约束的字段不能为NULL
唯一约束(unique)：约束的字段不能重复
主键约束(primary key)：约束的字段既不能为NULL，也不能重复（简称PK）
外键约束(foreign key)：…（简称FK）
检查约束(check)：注意Oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束。

2.非空约束 (not null)

验证非空约束:
	drop table if exists t_user; 看看数据表是否存在,存在则删除,反之

	create table t_user(
		id int,
		username varchar(255) not null,
		password varchar(255)
	);

	insert into t_user(id,password) values (1,'123'); 
	向新建的数据表中插入一条数据,故意没有给username字段赋值,使其为null
	结果因非空约束而报错:ERROR 1364 (HY000): Field 'username' doesn't have a default value

向数据表t_user中插入数据: insert into t_user(id,username,password) values (1,‘lisi’,‘005269’);

3.唯一约束(unique)

3.1给某一列添加unique

列级唯一约束(每个字段后添加关键字:unique,每个字段具有单独的唯一性)
drop table if exists t_user;
		create table t_user(
			id int,
			username varchar(255) unique  // 列级约束,给username字段添加唯一约束
		);
		insert into t_user values(1,'zhangsan');// 向数据表t_user中插入数据,username=zhangsan
		
		insert into t_user values(2,'zhangsan');// 向数据表t_user中插入数据,并赋值username=zhangsan
		ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'username' //经测试,报1062错误,因为username字段具有唯一性

		insert into t_user(id) values(2);
		insert into t_user(id) values(3);
		insert into t_user(id) values(4);

给两个列或者多个列添加unique:

表级唯一约束(在建表数据后,单独加上unique(字段1,字段2......),括号里的字段连接在一起具有唯一性)
drop table if exists t_user;
		create table t_user(
			id int, 
			usercode varchar(255),
			username varchar(255),
			unique(usercode,username) //(表级约束), 多个字段联合起来添加1个唯一约束:意思就是将字段usercode和字段username相连接出的新字段才具有唯一性
		);

		insert into t_user values(1,'111','zs'); //两字段连接后为:111zs
		insert into t_user values(2,'111','ls'); //两字段连接后为:111ls
		insert into t_user values(3,'222','zs'); //两字段连接后为:222zs
		//经过对比:两字段连接后,并没有发现字段数据重复,所以不会报错
		
		select * from t_user;
		
		insert into t_user values(4,'111','zs');//两字段连接后为:111zs
		ERROR 1062 (23000): Duplicate entry '111-zs' for key 'usercode' //经过对比与id=1的数据,连接字段数据重复,涉及唯一约束,所以报1062错误
		
		
列级唯一约束(每个字段后添加关键字:unique,每个字段具有单独的唯一性)
drop table if exists t_user;
		create table t_user(
			id int, 
			usercode varchar(255) unique, //列集约束: 这个字段数据具有唯一约束性
			username varchar(255) unique //列集约束: 这个字段数据具有唯一约束性
		);
		
		insert into t_user values(1,'111','zs');
		insert into t_user values(2,'111','ls');
		ERROR 1062 (23000): Duplicate entry '111' for key 'usercode' //经过对比,插入字段(usercode)数据重复,所以报1062错误

注意：not null约束只有列级约束。没有表级约束。

4.主键约束(primary key)

4.1怎么给一张表添加主键约束

drop table if exists t_user; //检验数据库中是否有这张数据表,有则删除,反之

		create table t_user(
			id int primary key,  // 列级约束
			username varchar(255),
			email varchar(255)
		); //创建t_user数据表,给id字段加上主键约束(primary key)
		
		insert into t_user(id,username,email) values(1,'zs','zs@123.com');
		insert into t_user(id,username,email) values(2,'ls','ls@123.com');
		insert into t_user(id,username,email) values(3,'ww','ww@123.com');
		//向数据表t_user中加入3条数据
		
		select * from t_user; //经查询数据插入成功,如下图

测试主键约束的非空性和唯一性

//向数据表中继续插入id=1的数据,经过测试,报1062错误
insert into t_user(id,username,email) values(1,'jack','jack@123.com');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

//向数据表中插入数据,没有给id字段赋值看看能否自动赋默认值 null ,经过测试,报1364错误
insert into t_user(username,email) values('jack','jack@123.com');
ERROR 1364 (HY000): Field 'id' doesn't have a default value

根据以上的测试得出：id是主键，因为添加了主键约束，主键字段中的数据不能为NULL，也不能重复。
主键的特点：不能为NULL，也不能重复。

4.2主键知识点

主键相关的术语:
-主键约束 : primary key
-主键字段 : id字段添加primary key之后，id叫做主键字段
-主键值 : id字段中的每一个值都是主键值。

主键有什么作用:
- 表的设计三范式中有要求，第一范式就要求任何一张表都应该有主键。
- 主键的作用：主键值是这行记录在这张表当中的唯一标识。（就像一个人的身份证号码一样。）

主键的分类:
根据主键字段的字段数量来划分：
-单一主键（推荐的，常用的。）
-复合主键(多个字段联合起来添加一个主键约束)（复合主键不建议使用，因为复合主键违背三范式。）
根据主键性质来划分：
-自然主键：主键值最好就是一个和业务没有任何关系的自然数。（这种方式是推荐的）
-业务主键：主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键，拿着身份证号码作为主键。（不推荐用,详情见注）
注:
最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候，主键值可能也需要随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复。

一张数据表的主键约束只能有1个。

4.3使用表级主键约束方式定义：

使用表级主键约束方式定义:
drop table if exists t_user;

		create table t_user(
			id int,
			username varchar(255),
			primary key(id) //使用表级约束方式定义主键
		);
		insert into t_user(id,username) values(1,'zs');
		insert into t_user(id,username) values(2,'ls');
		insert into t_user(id,username) values(3,'ws');
		insert into t_user(id,username) values(4,'cs');
		
		select * from t_user; //查询成功

		insert into t_user(id,username) values(4,'cx');
		ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY' //插入id=4的数据,报1062错误,证明主键的唯一性

4.4mysql提供主键值自增:

主键自增(auto_increment):
drop table if exists t_user;

		create table t_user(
			id int primary key auto_increment, // id字段自动维护一个自增的数字，从1开始，以1递增。
			username varchar(255)
		);
		
		insert into t_user(username) values('a');
		insert into t_user(username) values('b');
		insert into t_user(username) values('c');
		insert into t_user(username) values('d');
		insert into t_user(username) values('e');
		insert into t_user(username) values('f');
		
		select * from t_user;

提示:Oracle当中也提供了一个自增机制，叫做：序列（sequence）对象。

5.外键约束(foreign key)

5.1外键知识点

关于外键约束的相关术语：
-外键约束: foreign key
-外键字段：添加有外键约束的字段
-外键值：外键字段中的每一个值。

业务背景：请设计数据库表，用来维护学生和班级的信息

第一种方案：一张表存储所有数据:
缺点:数据冗余

no(pk)	name	classno	classname
1	zhangsan	101	浑南区九小六年1班
2	lisi	102	浑南区九小六年2班
3	wangwu	101	浑南区九小六年1班
4	zhaoliu	101	浑南区九小六年1班
5	maba	102	浑南区九小六年2班

第二种方案：两张表（班级表和学生表）
t_class 班级表：(父数据表)

cno	cname
101	浑南区九小1班
102	浑南区九小2班

t_student学生表：(子数据表)

sno(pk)	sname	classno(该字段添加外键约束fk)
1	zhangsan	101
2	lisi	102
3	wangwu	101
4	zhaoliu	101
5	maba	102

t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表。t_class表叫做父表。

将以上表的建表语句写出来：

顺序要求：
-删除数据的时候，先删除子表，再删除父表。
-添加数据的时候，先添加父表，在添加子表。
-创建表的时候，先创建父表，再创建子表。
-删除表的时候，先删除子表，在删除父表。

	//删除数据的时候，先删除子表，再删除父表。
	drop table if exists t_student; //判断子表是否存在，存在则删除，反之
	drop table if exists t_class; //判断父表表是否存在，存在则删除，反之

	//创建表的时候，先创建父表，再创建子表。
		create table t_class(
			cno int,
			cname varchar(255),
			primary key(cno) //创建父表，确定字段cno为主键约束
		);

		create table t_student(
			sno int,
			sname varchar(255),
			classno int,
			primary key(sno), //创建子表，确定字段sno为主键约束
			foreign key(classno) references t_class(cno) //确定字段classno字段为外键约束，这个外键约束来自数据表(父表)t_class数据表中的cno字段
		);

		//向父表t_class中插入数据
		insert into t_class values(101,'xxxxxxxxxxxxxxxxx');
		insert into t_class values(102,'yyyyyyyyyyyyyyyyy');

		//向子表t_student中插入数据
		insert into t_student values(1,'zs1',101);
		insert into t_student values(2,'zs2',101);
		insert into t_student values(3,'zs3',102);
		insert into t_student values(4,'zs4',102);
		insert into t_student values(5,'zs5',102);
		insert into t_student values(6,'zs6',102);
		
		select * from t_class; //查询成功
		select * from t_student; //查询成功
		
		insert into t_student values(7,'lisi',103); //向子表中插入数据,但是这个外键并不存在，所以报1452错误
		ERROR 1452 (23000): Cannot add or update a child row:......

外键值可以为NULL吗：外键可以为NULL。

外键字段引用其他表的某个字段的时候，被引用的字段必须是主键吗?
注意：被引用的字段不一定是主键，但至少具有unique约束。

六、存储引擎

1.新建数据表，并查看数据表的建表语句

创建数据表：
create table t_test(
		id bigint,
		name varchar(255)
	);
	
查看创建数据表t_test的建表语句：
show creat table t_test;

2.完整的建表语句

CREATE TABLE ‘t_x’ (‘id’ int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;
注意：在MySQL当中，凡是标识符是可以使用飘号括起来的。最好别用，不通用。
建表的时候可以指定存储引擎，也可以指定字符集，mysql默认使用的存储引擎是InnoDB方式，默认采用的字符集是UTF8。

3.什么是存储引擎

存储引擎这个名字只有在mysql中存在。（Oracle中有对应的机制，但是不叫做存储引擎。Oracle中没有特殊的名字，就是“表的存储方式”）
mysql支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式。
每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎。

4.查看当前mysql支持的存储引擎

查看当前mysql支持的存储引擎：
show engines \G

查看mysql版本：
select version();

5.7.43版本mysql支持的存储引擎有9个：

5.常见的存储引擎

5.1.MyISAM存储引擎

5.1.1.简单概述：
MyISAM这种存储引擎不支持事务，MyISAM是mysql最常用的存储引擎，但是这种引擎不是默认的。

MyISAM采用三个文件组织一张表：
xxx.frm（存储格式的文件）
xxx.MYD（存储表中数据的文件）
xxx.MYI（存储表中索引的文件）

5.1.2.优缺点：
优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。
缺点：不支持事务。

5.2.InnoDB存储引擎

5.2.1简单概述：
表的结构存储在xxx.frm文件中，数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法转换成只读。
这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制。InnoDB支持级联删除和级联更新。

5.2.2优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。

5.3.MEMORY存储引擎

5.3.1.简单概述：以前叫做HEPA引擎。

5.3.2优缺点：
优点：查询速度最快。
缺点：不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中的。

七、事务

1.什么是事务

1.1.一个事务是一个完整的业务逻辑单元，不可再分。
比如：银行账户转账，从A账户向B账户转账10000.需要执行两条update语句：

update t_act set balance = balance - 10000 where actno = 'act-001';
update t_act set balance = balance + 10000 where actno = 'act-002';

以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败。
要想保证以上的两条DML语句同时成功或者同时失败，那么就需要使用数据库的“事务机制”。

1.2.和事务相关的语句只有：DML语句。（insert delete update）
为什么？因为它们这三个语句都是和数据库表当中的“数据”相关的。事务的存在是为了保证数据的完整性，安全性。

1.3假设所有的业务都能使用1条DML语句搞定，还需要事务机制吗？
不需要事务。
但实际情况不是这样的，通常一个“事儿（事务【业务】）”需要多条DML语句共同联合完成。

2. 事务的特性

事务包括四大特性：ACID
A: 原子性：事务是最小的工作单元，不可再分。
C: 一致性：事务必须保证多条DML语句同时成功或者同时失败。
I：隔离性：事务A与事务B之间具有隔离。
D：持久性：持久性说的是最终数据必须持久化到硬盘文件中，事务才算成功的结束。

3.关于事务之间的隔离性

事务隔离性存在隔离级别，理论上隔离级别包括4个：

第一级别：读未提交（read uncommitted）
对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。
读未提交存在脏读（Dirty Read）现象：表示读到了脏的数据。

第二级别：读已提交（read committed）
对方事务提交之后的数据我方可以读取到。
这种隔离级别解决了: 脏读现象没有了。
读已提交存在的问题是：不可重复读。

第三级别：可重复读（repeatable read）
这种隔离级别解决了：不可重复读问题。
这种隔离级别存在的问题是：读取到的数据是幻象。

第四级别：序列化读/串行化读（serializable）
解决了所有问题。
效率低。需要事务排队。

oracle数据库默认的隔离级别是：读已提交。
mysql数据库默认的隔离级别是：可重复读。

4.演示事务

4.1.演示两个事务，假如隔离级别

演示第1级别：读未提交
set global transaction isolation level read uncommitted;

演示第2级别：读已提交
set global transaction isolation level read committed;

演示第3级别：可重复读
set global transaction isolation level repeatable read;

八、索引

1.什么是索引、有什么用

索引就相当于一本书的目录，通过目录可以快速的找到对应的资源。
在数据库方面，查询一张表的时候有两种检索方式：
第一种方式：全表扫描
第二种方式：根据索引检索（效率很高）

索引为什么可以提高检索效率呢？
其实最根本的原理是缩小了扫描的范围。
索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护。是有维护成本的。比如，表中的数据经常被修改这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。

添加索引是给某一个字段，或者说某些字段添加索引。
select ename,sal from emp where ename = ‘SMITH’;
当ename字段上没有添加索引的时候，以上sql语句会进行全表扫描，扫描ename字段中所有的值。当ename字段上添加索引的时候，以上sql语句会根据索引扫描，快速定位。

2.怎么创建索引对象、怎么删除索引对象

创建索引对象：
create index 索引名称 on 表名(字段名);
删除索引对象：
drop index 索引名称 on 表名;

3.什么时候考虑给字段添加索引

数据量庞大。（根据客户的需求，根据线上的环境）
该字段很少的DML操作。（因为字段进行修改操作，索引也需要维护）
该字段经常出现在where子句中。（经常根据哪个字段查询）
注意：主键和具有unique约束的字段自动会添加索引。根据主键查询效率较高。尽量根据主键检索。

4.查看sql语句的执行计划

查看sql语句执行计划：
explain select ename,sal from emp where sal = 5000;

给薪资sal字段添加索引

给sal字段添加索引：
create index emp_sal_index on emp(sal);

然后查看sql语句执行计划：
explain select ename,sal from emp where sal = 5000;

5.索引底层数据结构和实现原理

索引底层采用的数据结构是：B + Tree

通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。
select ename from emp where ename = ‘SMITH’;
通过索引转换为：
select ename from emp where 物理地址 = 0x3;

6.索引的分类

单一索引：给单个字段添加索引
复合索引: 给多个字段联合起来添加1个索引
主键索引：主键上会自动添加索引
唯一索引：有unique约束的字段上会自动添加索引

7.索引什么时候失效

select ename from emp where ename like ‘%A%’; 模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的。

九、视图

1.什么是视图

站在不同的角度去看到数据。（同一张表的数据，通过不同的角度去看待）。

2.怎么创建视图、怎么删除视图

创建视图：create view myview as select empno,ename from emp;
删除视图：drop view myview;
注意：只有DQL语句才能以视图对象的方式创建出来。

3.面向视图操作

对视图进行增删改查，会影响到原表数据。（通过视图影响原表数据的，不是直接操作的原表）可以对视图进行CRUD操作。

面向视图操作数据表中的数据：

create table emp_99 as select * from emp;//将查询出的数据赋值给数据表emp_99

create view myview1 as select empno,ename,sal from emp_99;//将数据表emp_99创建视图

update myview1 set ename='hehe',sal=1 where empno = 7369;//通过视图修改原表数据。

delete from myview1 where empno = 7369;//通过视图删除原表数据。

select * from myview1;//通过视图查询数据表的所有数据

4.视图的作用

视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，java程序员只对视图对象进行CRUD。

十、数据库设计三范式

1.什么是设计范式

设计表的依据。按照这个三范式设计的表不会出现数据冗余。

2.数据库设计三范式都有哪些

第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分。

第二范式：建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖。
多对多：三张表，关系表两个外键。

第三范式：建立在第二范式的基础之上，所有非主键字段直接依赖主键，不能产生传递依赖。
一对多：两张表，多的表加外键。

提醒：在实际的开发中，以满足客户的需求为主，有的时候会拿冗余换执行速度。

3.一对一怎么设计

一对一设计有两种方案：主键共享、外键唯一。

这样mysql博文学习到此结束！
很高兴,这篇博客能帮助到大家,也很感谢帮博主分享博客的帖子,如果大家有什么问题,欢迎评论区留言,我们一起解决,一起学习!
博主其它的学习资料:使用若依框架RuoYi前后端分离版(超详细步骤)、若依框架代码生成工具–单表增删改查(超详细步骤)、java注解大全,希望能帮助到大家!
拿走不谢,留个关注,最好在点个赞! 😊