数据库常用命令

注意：命令不区分大小写

创建数据库

create database thefirstdemo(数据库名称);

展示数据库所有名称

show databases;
image-20230124231943085

使用指定数据库

use thefirstdemo(数据库名称);
image-20230124232321346

退出数据库

exit; //推出整个数据库
image-20230124232431387

表的理解

展示指定数据库中的表，显示出表名

use sys(数据库的名称);
show tables;
image-20230124233544514

数据库当中是以表格的形式表示数据的
因为表比较直观

姓名 性别 年龄 （列：字段）
————————————————
张三 男 20 ——>行（记录）
李四 女 21 ——>行（记录）

任何一张表都有行和列：
行(row) ：被称为数据/记录
列(colmn) ：被称为字段：姓名字段、年龄字段等

SQL分类

DQL

数据查询语言（凡是带有select关键字的都是查询语句）

select....

DML

数据操作语言（凡是对表当中的数据进行 增删改insert delete update 的都是DML），这个主要是操作表中的数据data

insert : 增
delete : 删
update : 改

DDL

数据定义语言（凡是带有 create 、 drop 、alter 的都是DDL）
DDL主要操作的是表的结构。不是表中的数据

这个增删改和DML不同，这个主要是对表结构进行操作。

create : 新建，等同于增
drop : 删除
alter : 修改

TTL

事务控制语言：

包括:
事务提交 : commit;
事务回滚 : rollback;

DCL

是数据控制语言：

例如:
授权 : grant;
撤销权限 : revoke....

DQL

表操作

表数据操作

mysql> crete database bjpowernode; : 创建bjpowernode数据库

image-20230125112025944

mysql> use bjpowernode; : 使用这个数据库

image-20230125112129708

mysql> source D:\MySQL\MySQL-data\document\bjpowernode.sql : //定位到bjpowernode文件夹中的bjpowernode文件,不过这个文件在D盘下

image-20230125112209729

show tables : //展示这个bjpowernode文件下的表

image-20230125112241664

mysql> select * from emp; : 查看emp表中的所有数据， *表示这个emp表中的所有，emp是一个表

image-20230125112400176

同理，分别查看dept表和salgrade表中的所有数据

image-20230125112431566 image-20230125112446455

表结构操作

不看表中的数据，只看表的结构，有一个命令： desc 表名

image-20230125164841875

查看当前使用的是哪个数据库，用 select database(); 命令

简单查询

查询一个字段：

select 字段名 from 表名;

其中要注意：select 和 from 都是关键字，字段名和表明都是标识符。

强调：
对于SQL语句来说，是通用的；所有的SQL语句以 “ ，” 结尾；另外SQL语句不区分大小写，都行

//查询部门名字，先查询部门有什么，从中找到名字的表名
desc dept;
//查询部门名字
select DNAME from dept;
image-20230125171120046

查询两个字段，或者多个字段，

使用逗号隔开“，” 即select deptno,dname from dept;

image-20230125171412990

查询所有字段

  • 第一种方式：可以把每个字段都写上

    select a,b,c,d,e,f....(字段名) from tablename;

    image-20230125171832376

  • 第二种方式：可以使用 *

    select * from dept;

    image-20230125171936986

    但是这种方式，效率低，可读性差，在实际开发中不建议使用方式二，建议使用方式一，可读性要好一点

给查询的列起别名

select 现在名 as 别名 from 表名 ;

//将dename 起别名为deptname

image-20230125172848851

注意：只是将显示的查询结果列名显示为 deptname，原表列名还是叫：dname，
记住：select 语句是永远都不会进行修改操作的（只负责查询）。

可以使用as关键字起别名，也可以省略as关键字，别名里有空格的话需要用单引号括起来select deptno,dname 'dept name' from dept; 表示还是deptname,中间没有空格，mysql数据库中可以使用双引号，但oracle数据库中不能

  • 字段可以使用数学表达式

    select ename,sal*12 from emp;

    image-20230125183037314

这样就运用到了别名的作用了

select ename,sal*12 as year_sal from emp;

image-20230125183158126

也可以用中文名，但需要用单引号括起来

select ename,sal*12 as '年工资' from emp;

image-20230125183323471

条件查询

条件查询需要用到 where语句，where 必须放到 from 语句表的后面，支持如下运算符

运算符 说明
= 等于
<> 或 != 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
between … and … 两个值之间，等同于 >= and <=
is null 为 null （is not null 不为空）
and 并且
or 或者
in 包含，相当于多个 or（not in 不在这个范围中）
not not 可以取非，主要用在 is 或 in 中
like 模糊查询，支持 % 或下划线匹配
% 匹配任意个字符
下划线，一个下划线只匹配一个字符
  • 条件查询语法格式：
select
	字段1，字段2，字段3...
from
	表名
where
	条件;

  • 查询SMITH的编号和薪资

    select empno,sal from emp where ename = 'SMITH'; //字符串使用单引号
    image-20230125185145803

  • = 等于

    查询薪资等于800的员工姓名和编号

    select empno,ename from emp where sal = 800;
    image-20230125185648067

  • <> 或 != 不等于

    查询薪资不等于800的员工姓名和编号

    select empno,ename from emp where sal != 800;
select empno,ename from emp where sal <> 800;
    image-20230125185902633
image-20230125185936399

  • < 小于, <= 小于等于

    查询薪资小于2000的员工姓名、编号和薪资

    select empno,ename,sal from emp where sal < 2000;
    image-20230125190225761

  • 大于，> ; 大于等于，>=

    查询薪资大于等于3000的员工姓名、编号和薪资

    select empno,ename,sal from emp where sal >=3000;
    image-20230125190501837

  • between … and … 两个值之间，等同于 >= and <=

    查询薪资在2450 和3000之间的员工信息，包括2450和3000

    • 第一种方式：>= and <= :（and是并且的意思）

      select empno,ename,sal from emp where sal <= 2450 and sal >= 3000;
      image-20230125190929288

    • 第二种方式：between … and ….

      select empno,ename,sal from emp where sal between 2450 and 3000;
      image-20230125191102560

      注意：使用 between and 的时候，必须遵循左小右大；between and 是闭区间，包括两端的值

  • null 为 null （is not null 不为空）

    查询哪些员工的津贴/补助为null

    select empno,ename,sal,comm from emp where comm is null;

    注意：在数据库中null不能使用等号进行衡量。需要使用 is null ，因为数据库中的null表示什么也没有，它不是一个值，所以不能使用等号衡量。

    image-20230125191650090

    查询哪些员工的津贴/补助不为null

    select empno,ename,sal,comm from emp where comm is not null;
    image-20230125191816220

  • and 并且

    查询工作岗位是MANAGER并且工资大于2500的员工信息

    select
	empno,ename,job,sal
from
	emp
where
	job = 'MANAGER' and sal > 2500;
    image-20230125192606163

  • or 或者

    查询工作岗位是MANAGER 和 SALESMAN 的员工

    select
	empno,ename,job
from 
	emp
where
	job = 'MANAGER' or job = 'SALESMAN';
    image-20230125192926587

  • and 和 or 同时出现，如果没有小括号的话优先级and比or要高，and先执行，然后才是or，如果有小括号则小括号里面的先执行。

    查询工资大于2500，并且部门编号为10 或 20 的员工

    select
	*
from
	emp
where
	sal > 2500 and (deptno = 10 or deptno = 20);
    image-20230125194425292

  • in 包含，相当于多个 or （not in 不在这个范围中）

    查询工作岗位是MANAGER 和 SALESMAN 的员工

    select empno,ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
//或者
select empno,ename,job from emp where job in('MANAGER', 'SALESMAN');
    image-20230125195328424

    查询薪资是800 和 5000 的员工信息

    select ename,sal from emp where sal = 800 or sal = 5000;
//或者
select ename,sal from emp where sal in(800, 5000);//这个不是800到5000区间都找到，而是找800或5000的员工
    image-20230125195603899

    注意：in括号中不是一个区间，in后面跟的是具体的值。

    not in ：除了括号中这几个值外的其他值，也可以用在is 中，即 is not 

    select ename,sal from emp where sal not in(800,5000);
    image-20230125195851560

  • like：称为模糊查询，支持 % 或 下划线匹配
    %匹配任意多个字符，下划线：任意一个字符（%是一个特殊的符号，__也是一个特殊符号）

找出名字中含有 o 的

select ename from emp where ename like '%o%';
image-20230125201429891

找出名字首字母是A的姓名

select ename from emp where ename like 'A%';
image-20230125212208257

找出名字尾字母为S的姓名

select ename from emp where ename like '%S';
image-20230125212347978

找出名字第二个字母为L的姓名

select ename from emp where enmae like '_L%';
image-20230125212758107

找出名字中含有下划线的

select ename from emp where ename like '%/_%'; //因为下划线是一个特殊符号，所以需要用转义字符

排序order by

  • 默认排序（从小到大排序）

查询所有员工薪资，排序

select 
	ename,sal
from
	emp
order by
	sal;  //默认是升序！！！
image-20230125204418436
  • 指定降序（从大到小）排序
select
	ename,sal
from
	emp
order by
	sal desc; //指定降序排序
image-20230125204600716

  • 指定升序（从小到大）排序

    select
	ename,sal
from
	emp
order by
	sal asc; //指定升序排序
    image-20230125204753269

  • 先按照一个值的指定顺序排，如果这个值有重复则按照另一个值排序

    查询员工名字和薪资，要求按照薪资升序，如果薪资一样的话，再按照名字升序排列

    select
	ename,sal
from
	emp
order by
	sal asc, ename asc; //sal相同的话再按ename排序
    image-20230125205330096

综合练习：

找出工资在 1250 到 3000 之间的员工信息，要求按照薪资降序排列

select
	ename,sal
from
	emp
where
	sal between 1250 and 3000
order by
	sal desc;
image-20230125210223210

关键字顺序不能变
select….from…..where……order by

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

数据处理函数

image-20230125210755175 image-20230125210809279

数据处理函数又被称为单行处理函数

单行处理函数的特点：一个输入对应一个输出。

和单行处理函数相对的是：多行处理函数。（多行处理函数特点：多个输入，对应1个输出）

单行处理函数常见

lower 转换为小写

select lower(ename) from emp; //可以起别名 select lower(ename) as ename from emp;
image-20230125211729192

upper 转换为大写

select upper(ename) from emp; //可以起别名

substr 取子串（substr（被截取的字符串，起始下标，截取的长度））

注意：起始下标从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; //可取别名（重命名）
    image-20230125214506512

    length ： 取长度

    select length(ename) as enamelength from emp;
    image-20230125214659101

trim : 去空格

查询KING这个名字，但带了空格

select * from emp where ename = " KING"; //注意有空格
image-20230125215808288

str_to_data : 字符串转换成日期

date_format : 格式化日期

format : 设置千分位

round函数 ：四舍五入

round(数值， 四舍五入保留小数值（-1表示十位，-2表示百位…）)

select round(1234.567, 0) from emp;  //保留到个位
image-20230125231540008 
select round(1234.567, 1) from emp; //保留小数一位
image-20230125233521530

rand( ) : 生成随机数，小于1

select rand() from emp;  //emp中有14行
image-20230125232757359 
select round(rand() * 100, 0) from emp;  //两位数之内，四舍五入保留到个位数，100以内的随机数
image-20230125232947692

ifnull: 将 null 转换成一个具体值

​ 空处理函数，专门处理空的
​ 在所有数据库当中，只要有NULL参与的数学运算，最终结果就是NULL

select ename, sal + comm as salcomm from emp;
image-20230126102232627

注意：只要参与运算，最终结果一定是NULL，为了避免这个现象，需要使用 ifnull 函数
ifnull( 数据，被当作哪个值)

//补助为null的时候，将补助当作0
select ename, (sal + ifnull(comm, 0)) * 12 from emp; //如果comm为空的话，当作0开看待
image-20230126102824184

case … when … then …when … then … else …end

当员工的工作岗位是 MANAGER 的时候，工资上调10%，当工作岗位是 SALESMAN 的时候，工资上调 50%，其他正常
（注意：不修改数据库，只是将查询结果显示为工资上调）

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;
image-20230126104347380

分组函数

分组函数，即多行处理函数：特点，输入多行，最终输出一行

注意：如果没有对数据分组，整张表默认为一组

count ： 计数

//计算员工数量
select count(ename) from emp;
image-20230126105450661

sum ： 求和

//计算工资和
select sum(sal) from emp;
image-20230126105504653

avg ： 平均值

//计算工资平均值
select avg(sal) from emp;
image-20230126105518843

max ： 最大值

//找出最高工资
select max(sal) from emp;
image-20230126105533127

min ： 最小值

//找出最低工资
select min(sal) from emp;
image-20230126105542204
  • 注意：分组函数不会受NULL的影响，分组函数自动忽略NULL，不需要对NULL进行处理
select count(comm) from emp;
image-20230126110422356 
select avg(comm) from emp;
image-20230126110809298

如果要想求出包括NULL在内的总数，可以用 ifnull(comm, 0) 将comm中的NULL值转换为具体数值

select count(ifnull(comm, 0)) from emp;
image-20230126110626837

count( * ) 和 count(具体字段) 的区别

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

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

  • 所有的分组函数可以组合起来一起用

    select sum(sal), min(sal), avg(sal), count(*) from emp;
    image-20230126120624155

分组查询

group by

关键字顺序不能变

select
	...
from
	...
where
	...
group by
	...
order by
	...

执行顺序：from、where、group by、select、order by ，执行完where才执行group by，不能颠倒

from来找到表，先用where过滤然后再group by分组，最后查询并排序
而select sum(sal) from emp; 是按照整张表为一组进行分组来求工资总数

//找出每个工作的薪资总和
select job, sum(sal) from emp group by job;
image-20230126123734006

重要：在一条select语句当中，如果有 group by 语句的话，select 后面只能跟：参加分组的字段，以及分组函数，其他的不能跟
比如说select ename, job, sum(sal) from emp group by job; //这个是错误的，不能跟无关的东西ename，会在
oracle中报错

//按照部门编号分组，求每一组的最大值
select deptno,max(sal) from emp group by deptno;
image-20230126124055772 
//找出 每个部门，不同工作岗位 的最高薪资。两个字段联合成1个字段看。
select 
	deptno, job, max(sal)
from 
	emp
group by
	deptno, job;
image-20230126133620248

having

分组查询后再筛选，分完组之后可以用having

找出每个部门最高薪资，要求显示最高薪资大于3000的

  • 方式一 : 用where在最开始的地方进行筛选，先筛选员工薪资大于3000的员工，再进行分组比对

    select
	deptno, max(sal)
from 
	emp
where
	sal > 3000     //注意：这里不能写max(sal) > 3000
group by
	deptno;
    image-20230126135532668

  • 方式二：用having，在分完组之后进行筛选薪资大于3000 的员工

    select 
	deptno, max(sal)
from
	emp
group by
	deptno
having
	max(sal) > 3000;
    image-20230126140241662

用where更节省时间，效率更高，但有的时候只能用having 来执行分组查询的比较命令

找出每个部门平均薪资，要求显示平均薪资高于2500的

select
	deptno, avg(sal)
from
	emp
group by
	deptno
having
	avg(sal);
image-20230126140623062

总结

关键字顺序

select
	...
from
	...
where
	...
group by
	...
having
	...
order by
	...

执行顺序：from、where、group by、having、select、order by

找出除MANAGER岗位之外，每个岗位的平均薪资，要求显示平均薪资大于1500的，要求按照平均薪资降序排

select
	job, avg(sal)
from 
	emp
where
	job != 'MANAGER'
group by
	job
having
	avg(sal) > 1500
order by
	avg(sal) desc;
image-20230126142329173

distinct 关键字

distinct：把查询结果去除重复元素
注意：原表数据不会被修改，只是查询结果去重

//将工作岗位去重
select distinct job from emp;
image-20230126153042440

注意：distinct 只能出现在所有字段的最前方，不能是select ename, distinct job from emp; 这样写是错误的

distinct 出现在job，deptno 两个字段之前，表示两个字段联合起来去重，就是说将这两个字段当为一个整体去除重复的数据

//将岗位和部门都相同的数据去重
select distinct job, deptno from emp;

连接查询

概述

SQL92：1992年的时候出现的语法

SQL99 ：1999年的时候出现的语法（重点学习）

根据表连接的方式分类：

内连接：

等值连接
非等值连接
自连接

外连接：

左外连接（左连续）
右外连接（右连接）

全连接（用的不多）

笛卡尔积现象

两张表在没有任何条件限制下进行连接查询，会发生笛卡尔积现象

两张表进行连接时，连接后的结果是两张表数据相乘的数量，因为要拿一个表的某一个数据和其他表中的所有数据进行匹配

select ename, deptno from emp; 和 select dname, deptno from dept; 两张表进行连接

select ename, dname from emp, dept; //将两张表进行连接，会出现14*4=56条数据

为了避免笛卡尔积现象，需要添加限制条件筛选

//将两张表中部门相同的名字输出显示
select 
	ename, dname
from
	emp, dept
where
	emp.deptno = dept.deptno;

或者SQL92语法

select
	e.ename, d.dname
from
	emp as e, dept as d
where
	e.deptno = d.deptno;
image-20230126161626924

最终查询的结果条数是14条，但是匹配的过程中，匹配的次数没有减少，还是56次，只不过添加了条件使得只需要比较deptno

内连接

两张表没有主次关系，平等的

等值连接

条件是等量关系，所以称为等值连接

查询每个员工所在部门名称，显示员工名和部门名

//SQL92 语法,缺点:表连接操作和后期筛选操作都集中到where中了
select 
	e.ename, d.dname
from 
	emp as e, dept as d
where
	e.deptno = d.deptno;
//SQL99 语法
select 
	e.ename, d.dname
from
	emp as e
inner join  //inner可以省略，表示内连接
	dept as d
on
	e.deptno = d.deptno;
//前面这些是指表与表的连接
where
	...(单独的对这些文件进行筛选操作)

即：select … from 表1 join 表2 on a和b的连接条件 where 筛选条件

非等值连接

条件不是一个等量关系，称为非等值连接

salgrade表中的数据

image-20230126165020899

将emp表与salgrade表连接，找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级

select 
	e.ename, e.sal, s.grade
from
	emp as e
join
	salgrade as s
on 
	e.sal between s.losal and s.hisal;
image-20230126165933247

自连接

一张表看成两张表

查询员工的上级领导，要求显示员工名和对应的领导名，需要用 员工的领导编号 = 领导的编号，而最大领导的编号为NULL，不显示

image-20230126172111714 
select
	e1.ename, e2.ename
from
	emp as e1
join 
	emp as e2
on
	e1.mgr = e2.mgr
image-20230126171922869

外连接

上面自连接中没有KING的NULL信息，外连接可以全部查出来

内连接：e1 和 e2 连接，两张表没有主次关系，是平等的关系，特点：完成能够匹配上这个条件的数据查询出来

外连接，right表示右（外）连接，left表示左（外）连接，特点：可以将主表中全部信息查询出来，顺带着查询次表中关联的信息

//外连接，查询员工的上级领导，要求显示员工名和对应的领导名
//右外连接 right
select
	e1.ename, e2.ename
from
	emp as e1
right join
	emp as e2
on
	e1.mgv = e2.empno;

右外连接：将join右边的表作为主表，全部显示信息，而左边作为次表，随着主表显示而显示数据

image-20230126182356581 
//左外连接left
select 
	e1.ename, e2.ename
from
	emp as e1
join
	emp as e2
on
	e1.mgv = e2.empno;

left：将join左边的表当作主表，将这张表的数据全部查询出来，捎带着关联查询右边的表

image-20230126183427937

外连接的查询结果条数一定是 >= 内连接的查询结果条数

多表连接

语法：

select
...
from
	a
join
	b
on
	a和b的连接条件
join
	c
on
	a和c的连接条件
right join    //右连接
	d
on
	a和d的连接条件

找出每个员工的部门名称以及工资等级，还有上级领导，要求显示员工名、领导名、部门名、薪资、薪资等级

select
	e1.ename, e2.ename, d.dname, s.grade, 
from
	emp e1
join
	dept as d
on
	e1.deptno = d.deptno
join
	salgrade as s
on
	e1.sal between s.losal and s.hisal
left join
	emp as e2
on
	e1.mgr = e2.empno;

子查询

概念

select 语句中嵌套 select 语句，被嵌套的 select 语句称为子查询

子查询可以出现的地方

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

子查询具体

where子句中的子查询

找出比最低工资高的员工的姓名和工资

select
	ename, sal
from 
	emp
where
	sal > (select min(sal) from emp);
image-20230126192617141

from子句中的子查询

from后面的子查询，可以将子查询的查询结果当作一张临时表。

查找出每个岗位的平均工资的薪资等级

select
	t.*, s.grade
from
	(select avg(sal) as avgsal from emp group by job) as t
join
	salgrade as s
on
	t.avgsal between s.losal and s.hisal;
image-20230126195824359

select子句中的子查询：了解

在select子句中嵌套select查询，只能一次返回一条结果，不能多条

找出每个员工的部门名称，要求显示员工名、部门名

select
	e.ename, (select d.dname from dept as d where e.deptno = d.deptno) as dname
from
	emp as e;
image-20230126201357184

union：合并查询结果集

合并查询结果可以使用 or 或者 in 的方法，但这两种方式匹配次数过多，union匹配次数较少，效率高

select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';

select ename,job from emp where job in('MANAGER','SALESMAN');

//union
select ename, job from emp where job = 'MANAGER'
union
select ename, job from emp where job = 'SALESMAN';
image-20230126202622198

union的效率要高一些，对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积
但是union可以减少匹配的次数，在减少匹配次数的情况下，还可以完成两个结果集的拼接

limit限制查询：重要

limit作用

作用：将查询结果集的一部分取出来，通常用在分页查询中，可以一页一页的翻页看信息，比如说百度是一次显示10条记录

用法：limit startIndex, length ： startIndex是起始下标，length是长度，下标从0开始，到length结束

缺省：limit 5; 这是取前5条数据

//按照薪资降序，取出排名在前5名的员工
select 
	ename,sal
from
	emp
order by
	sal desc
limit 5;  //取前5条数据
image-20230126204517434

mysql中的limit在order by 之后执行！！！

//取出工资排名在[3-7]名的员工降序排
select
	ename, sal
from
	emp
order by
	sal desc
limit
	2,5; //注意：第一个数字是起始位置(0表示第一个数，2表示第三个数值)，第二个数字是查询多少人，不是到那结束
image-20230126205004334

分页

每页显示3条记录
第1页：limit 0,3 [0 1 2]
第2页：limit 3,3 [3 4 5]
第3页：limit 6,3 [6 7 8]
第4页：limit 9,3 [9 10 11]

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

public static void main(String[] args){
	// 用户提交过来一个页码，以及每页显示的记录条数
	int pageNo = 5; //第5页
	int pageSize = 10; //每页显示10条

	int startIndex = (pageNo - 1) * pageSize;  //起始位置
	String sql = "select ...limit " + startIndex + ", " + pageSize;
}

记公式：

limit(pageNo - 1) * pageSize, pageSize

大总结

关于DQL语句总结

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

DDL

创建表：create

语法格式：

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

create table 表名(
	字段名1 数据类型,
    字段名2 数据类型,
    字段名3 数据类型
);
表名：建议以 t_ 或者 tbl_ 开始，可读性强，见名知意
字段名：见名知意
表名和字段名都属于标识符

关于mysql 中的数据类型

很多数据类型，我们只需要掌握一些常见的数据类型即可。

	varchar(最长255)
		可变长度的字符串
		比较智能，节省空间。
		会根据实际的数据长度动态分配空间。

		优点：节省空间
		缺点：需要动态分配空间，速度慢。

	char(最长255)
		定长字符串
		不管实际的数据长度是多少。
		分配固定长度的空间去存储数据。
		使用不恰当的时候，可能会导致空间的浪费。

		优点：不需要动态分配空间，速度快。
		缺点：使用不当可能会导致空间的浪费。

		varchar和char我们应该怎么选择？
			性别字段你选什么？因为性别是固定长度的字符串，所以选择char。
			姓名字段你选什么？每一个人的名字长度不同，所以选择varchar。

	int(最长11)
		数字中的整数型。等同于java的int。

	bigint
		数字中的长整型。等同于java中的long。

	float	
		单精度浮点型数据

	double
		双精度浮点型数据

	date
		短日期类型

	datetime
		长日期类型

	clob
		字符大对象
		最多可以存储4G的字符串。
		比如：存储一篇文章，存储一个说明。
		超过255个字符的都要采用CLOB字符大对象来存储。
		Character Large OBject:CLOB
	blob
		二进制大对象
		Binary Large OBject
		专门用来存储图片、声音、视频等流媒体数据。
		往BLOB类型的字段上插入数据的时候，例如插入一个图片、视频等，
		你需要使用IO流才行。
t_movie 电影表（专门存储电影信息的）

编号			名字				故事情节		上映日期		时长			   海报			类型
no(bigint)	name(varchar)	history(clob)	playtime(date)	time(double)   image(blob)	type(char)
------------------------------------------------------------------------------------------------------------
10000		哪吒			...........			2019-10-11		2.5				....		   '1'
10001	林正英之娘娘       ...........		2019-11-11		1.5				....			'2'
....

  • 创建一个学生表

    学号、姓名、年龄、性别、邮箱地址

    create table t_student(
	no int,
    name varchar(32),
    sex char(1),
    age int (3),
    email varchar(255)
);
    image-20230127190427839

  • 快速创建表（很少用）

    原理：

    ​ 将一个查询结果当作一张新表新建
    ​ 这个可以完成表的快速复制
    ​ 表创建出来，同时表中的数据也存在了

    create table emp2 as select * from emp;
    image-20230128153328664

删除表：drop

drop table xxx;

如果表不存在，则会报错 drop table t_student;

可以先判断表是否存在

drop table if exists t_student;
image-20230127194028017

插入数据：insert

插入数据

语法格式：insert into 表名(字段名1,字段名2,字段名3,...) values(值1,值2,值3,...); 

insert into t_student(no,name,sex,age,email) values(1,'zhangsan','m',20,'zhangsan@qq.com');
insert into t_student(email,name,sex,age,no) values('lisi@qq.com','lisi','f',20,2);
insert into t_student(no) values(3);
image-20230127192421216

insert语句执行成功了就会多出一条记录，没有给其他字段指定值的话，默认值是NULL

可以在建表的时候给字段名一个初始默认值，用default来指定默认值

create table t_student(
	no int,
    name varchar(32),
    sex char(1) default 'm',
    age int(3),
    email varchar(255)
);
image-20230127193020502 image-20230127193446559

一次可以插入多条数据：

语法：insert into 表名(字段名1，字段名2，...) values(),(),()...; 

insert into t_student
	(id, name, sex, age, email)
values
	(1, 'zhangsan', 'm', 20, 'zhangsan@qq.com'),
	(2, 'lisi', 'f', 18, 'lisi@qq.com'),
	(3, 'wangwu', 'm', 30, 'wangwu@qq.com');
image-20230128152705049

将查询结果插入到一张表中，前提是查询结果的字段名和顺序和另一张表相同

格式：insert into 表1 select 表1的所有字段名 from 表2； : 将表2中查询到的数据插入到表1

//将t_student1中的数据插入到 t_student 中
insert into t_student select id,name,sex,age,email from t_student1;

插入日期

数字格式化：format

将emp表中的sal表示成千分位，即999,999 而不是999999、

select ename,format(sal,'$999,999') as sal from emp;  //format(数值,'格式')
image-20230127195904374

使用date类型表示

create table t_user(
	id int,
    name varchar(32),
    birth date   //生日也可以使用date日期类型，也可以使用字符串类型 birth char(10)
);
image-20230127200736069

str_to_date : 将字符串varchar类型转换成 date 类型

通常使用在插入insert方面，因为插入的时候需要一个日期类型的数据，需要通过该函数将字符串转换成date

mysql默认的日期格式为%Y-%m-%d，如果提供的日期字符串格式为%Y-%m-%d，则不用更改

即insert into t_user(id,name,birth) values(2,lisi,'2001-05-12'); 

//str_to_date('字符串日期','日期格式');
insert into t_user(id,name,birth) values(1,'zhangsan',str_to_date('01-10-1990','%d-%m-%Y'));
image-20230127203923273

mysql的日期格式:

%Y ----> 年，大写代表全部1999，小写代表年份后两位99
%m ----> 月，格式为(01...12), %c 代表月格式为(1...12)
%d ----> 日
%h ----> 时
%i ----> 分
%s ----> 秒

date_format : 将date类型转换成具有一定格式的varchar 字符串类型，查询的时候可以使用进行转换

这个函数通常使用在查询日期方面，设置展示的日期格式。

//date_format(日期类型数据，'日期格式');
//例：将%Y-%m-%d格式转换为 %Y/%m/%d 格式
select id,name,birth date_format(birth, '%Y/%m/%d') as birth from t_user;
image-20230127205406452

查询的时候，mysql默认设置的是%Y-%m-%d

image-20230127205601297

java中的日期格式是yyyy-MM-dd HH:mm:ss SSS(毫秒)

date和datetime区别

date 是短日期类型：只包括年月日信息

create table t_user(
	id int,
    name varchar(32),
    birth date,
    create_time date
);
image-20230127210420716

mysql中短日期类型默认格式：%Y-%m-%d 

insert into 
	t_user(id,name,birth,create_time) 
values
	(1,'zhangsan','1999-9-9','2023-1-27 21:03:45');
image-20230127210647138

datetime 是长日期类型：包括年月日时分秒信息

create table t_user(
	id int,
    name varchar(32),
    birth date,
    create_time datetime
);
image-20230127211500330

mysql中长日期类型默认格式：%Y-%m-%d %h:%i:%s 

insert into 
	t_user(id,name,birth,create_time) 
values
	(1,'zhangsan','1999-09-09','2012-12-12 21:12:23');

image-20230127212058119

在mysql中可以获取当前时间，用now( )函数，并且获取的时间带有：时分秒信息，是datetime类型的

insert into
	t_user(id,name,birth,create_time)
values
	(2,'lisi','2012-12-12',now());

image-20230127212018420

修改数据：update

语法格式：update 表名 set 字段名1=值1，字段名2=值2，字段名3=值3,...where 条件;

注意：没有条件限制会导致所有数据全部更新。

update t_user set name = 'jack', birth = '2000-10-11' where id = 2;
image-20230127212930890 
//不加where条件会全部更改
update t_user set name = 'abc';
image-20230127214604460

删除数据：delete

语法格式：delete from 表名 where 条件;

注意：没有条件，整张表的数据会全部删除

delete from t_user where id = 2;
image-20230127213543297 
//没有where条件，整张表的数据会全部删除
delete from t_user;
image-20230127213755499

快速删除表中的数据，用 truncate ，delete是一个一个删除的，truncate直接全删除了，delete可以恢复，truncate不能恢复数据

delete语句删除数据的原理（delete属于DML语句）

​ 表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放！！！
​ 缺点：删除效率比较低
​ 优点：支持回滚，后悔了可以再恢复数据

image-20230128154325132

truncate 语句删除数据的原理（truncate属于DDL操作）truncate table 表名;

​ 这种删除效率比较高，表被一次阶段，物理删除
​ 缺点：不支持回滚，数据删除不能被恢复，只删除数据，表还在
​ 优点：快速删除

image-20230128154445444

修改表结构

对表结构的修改需要使用：alter ，属于DDL语句（包括：create drop alter)

  • 在实际的开发中，需求一旦确定之后，表一旦设计好之后，很少的
    进行表结构的修改。因为开发进行中的时候，修改表结构，成本比较高。
    修改表的结构，对应的java代码就需要进行大量的修改。成本是比较高的。
    这个责任应该由设计人员来承担！
  • 由于修改表结构的操作很少，所以我们不需要掌握，如果有一天
    真的要修改表结构，你可以使用工具(sqlyog之类的数据库软件工具)

约束

概述

在创建表的时候，我们可以给表中的字段加上一些约束，来保证这个表中数据的完整性、有效性！！！

四种约束：

  • 非空约束：not null
  • 唯一性约束：unique
  • 主键约束：primary key ：简称PK
  • 外键约束：foreign key ：简称FK
  • 检查约束：check （mysql 不支持，oracle 支持）

列级约束是指各个字段（列）的约束，这样的话会执行每一字段的约束判断

表级约束是指字段联合起来进行约束，将两个字段复合起来进行约束判断

还有一个：unsigned : 表示不能为负值

非空约束：not null

非空 not null 约束的字段不能为NULL，只有列级约束没有表级约束

//将name这个字段用not null约束起来，表示插入数据的时候name这个字段不能为NULL
create table t_vip(
	id int,
    name varchar(255) not null  //not null 只有列级约束，没有表级约束
)；
image-20230128182758069

这个表中的name不能为NULL

//插入数据，一条name不为NULL，一条name为NULL，但id可以为空
insert into t_vip(id,name) values(1,'zhangsan');
insert into t_vip(id) values(1);
image-20230128182830567 image-20230128183129317

xxx.sql这种文件被称为sql脚本文件，sql脚本文件中编写了大量的sql语句
在执行sql脚本文件的时候，该文件中所有的sql语句会全部执行
批量的执行sql语句可以导入：'mysql> source D:\course\03-MySQL\doucument\vip_sql'
用绝对路径，执行这个脚本文件，不要直接记事本打开，文件太大会导致死机！！！

唯一性约束：unique

唯一性约束 unique 约束的字段不能重复，但是可以为NULL，可以用列级约束，也可以用表级约束

//被约束的这个字段中所有的数值不能重复，其他字段可以重复
drop table if exists t_vip;//删除t_vip这个表
create table t_vip(
	id int,
    name varchar(255) unique,  //这是列级约束，只是这一字段是唯一性
    email varchar(255)
);
image-20230128184953926 
insert into t_vip(id, name, email) values(1, 'zhangsan', 'zhangsan@qq.com')；
insert into t_vip(id, name, email) values(2, 'lisi', 'zhangsan@qq.com');//name不一样，email一样可以插入成功，因为name没有重复
insert into t_vip(id, name, email) values(3, 'lisi', 'wangwu@qq.com'); //name一样则不能插入成功，因为name重复了
image-20230128190121881

唯一性约束unique可以为NULL

insert into t_vip(id) values(4);
insert into t_vip(id,name,email) values(5,NULL,'zhaoliu@qq.com');
image-20230128190707271

表级约束使用：需要给多个字段联合起来添加某一个约束的时候，需要使用表级约束

新需求，name 和 email 联合起来具有唯一性

这需要用表级约束，指明这两个字段联合起来具有唯一性

drop table if exists t_vip; //删除t_vip这个表
create table t_vip(
	id int,
    name varchar(255),
    email varchar(255),
    unique(name, email)  
    //约束没有添加在列的后面，这种约束被称为表级约束，表示这两个字段为一个整体，这两个字段都重复才算重复，只有一个重复则不满足重复约束条件。
);
image-20230128191439764

插入数据：

insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@qq.com');

insert into t_vip(id,name,email) values(2,'lisi','zhangsan@qq.com');//name不一样，但email一样，不会报错，因为name和email两者联合起来不重复，不满足约束条件

insert into t_vip(id,name,email) values(3,'lisi','lisi@qq.com');//name一样，但email不一样，不会报错，因为name和email两者联合起来不重复，不满足约束条件

insert into t_vip(id,name,email) values(4,'lisi','zhangsan@qq.com');//name一样，email一样，会报错，两者都一样，重复了
image-20230128192228011

unique 和 not null 联合起来，既不能为空，又不能重复

drop table if exists t_vip; //删除t_vip表
create table t_vip(
	id int,
    name varchar(255) not null unique
);
image-20230128193652817 
insert into t_vip(id,name) values(1, 'zhangsan'); //第一个值，添加成功
insert into t_vip(id) values(2); //name不能为空
insert into t_vip(id,name) values(3, 'zhangsan'); //name不能重复
image-20230128193939745

主键约束：primary key

主键约束：primary key （简称PK）

主键约束：就是一种约束

主键字段：该字段上添加了主键约束，这样的字段叫做：主键字段

主键值：主键字段中的每一个值都叫做：主键值

主键值有什么用？
主键值是每一行记录的唯一标识。
主键值是每一行记录的身份证号，这一行身份证号是跟其他行一定不相同的。
其他值都一样，只要主键值不一样，那么这两条数据就是不一样的。

注意：任何一张表都应该有主键，没有主键，表无效！！！

主键特征：primary key == not null + unique ：主键值不能是NULL，同时也不能重复

添加主键约束，一个字段做主键叫做单一主键，两个字段也能联合做一个主键，一张表中只能有一个主键

drop table if exists t_vip; //删除t_vip表
create table t_vip(
	id int primary key,   //这是列级约束
    name varchar(255)
);
//也可以用表级约束
create table t_vip(
	id int,
    name varchar(255),
    primary key(id,name)
);
image-20230128200536630

插入数据

//id是主键，表示id是唯一的，即其他都可以重复，但id是不会也不能重复
insert into t_vip(id,name) values(1,'zhangsan');
insert into t_vip(id,name) values(2,'zhangsan');  //id不一样，不会报错
insert into t_vip(id,name) values(1,'wangwu'); //id一样，会报错
insert into t_vip(id) values(3);  //非主键name可以为空NULL
insert into t_vip(name) values('lisi'); //主键不能为空NULL
image-20230128201456668

复合主键：primary key(id,name) ： 表示将id和name联合成一个主键，两者必须都重复才错误，而且复合主键的任何一个字段都不能为空NULL，不建议使用复合主键，单一主键够用了

drop table if exists t_vip; //删除t_vip
create table t_vip(
	id int,
    name varchar(255),
    email varchar(255),
    primary key(id,name)  //将id和name联合成一个主键
);
image-20230128202707618 
//id和name联合起来做主键
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@qq.com'); 

insert into t_vip(id,name,email) values(2,'zhangsan','zhangsan@qq.com');//id不重复，name重复，不会报错，因为id和name两个字段联合起来不重复

insert into t_vip(id,name,email) values(2,'lisi','zhangsan@qq.com');//id重复，name不重复，不会报错，因为id和name两个字段联合起来不重复，

insert into t_vip(id,name,email) values(1,'zhangsan','lisi@qq.com');//id和name联合起来重复，报错
image-20230128203829523 
//复合主键的任何一个字段都不能为空NULL
insert into t_vip(id,email) values(1,'zhangsan@qq.com'); //name为空，报错
insert into t_vip(name,email) values('lisi','lisi@qq.com'); //id为空，报错
image-20230128204424236

一张表，主键约束只能添加1个。（主键只能有1个。上面那个是复合主键，联合起来构成了一个）

主键建议使用：int 、bigint、char 等类型

​ 不建议使用：varchar类型来做主键，主键值一般都是数字，一般都是定长的

主键除了单一主键和复合主键之外，还可以这样进行分类：
自然主键：主键值是一个自然数1，2，3，4….，和业务没关系。
业务主键：主键值和业务紧密关联，例如拿银行卡账号做主键值。这就是业务主键！

    在实际开发中使用业务主键多，还是使用自然主键多一些？
        自然主键使用比较多，因为主键只要做到不重复就行，不需要有意义。
        业务主键不好，因为主键一旦和业务挂钩，那么当业务发生变动的时候，
        可能会影响到主键值，所以业务主键不建议使用。尽量使用自然主键。

自动维护一个主键值

drop table if exists t_vip; //删除t_vip表
create table t_vip(
		id int primary key auto_increment, //auto_increment表示自增，从1开始，以1递增！自然主键自增
		name varchar(255)
);
image-20230128205114912 
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
image-20230128205336065

外键约束：foregin key

  • 外键约束：foregin key （简称FK）

外键约束：就是一种约束

外键字段：该字段上添加了外键约束，这样的字段叫做：外键字段

外键值：外键字段中的每一个值都叫做：外键值

  • 外键约束通常使用场合：

当一张表中的某个字段下的值有很多重复的，比如说整个年级只有2个班级，很多人对应着一个班级
这样就可以再建一张表(父表)来存储这两个班级，然后用外键约束方式将两张表联合起来。

有外键约束的时候，子表中的班级信息索取范围就是父表的信息，
如果父表只有两个班级1和2，则子表也只有两个类型班级1，2，不能有另外的班级3。

设计数据库表，来描述 “班级和学生” 的信息

  • 方式一：

image-20230128210729636

在这张表中，班级classname数据有很多是重复的

缺点：数据冗余，空间浪费

  • 方式二

    image-20230128211411949

当cno字段没有任何约束的时候，可能会导致数据无效。可能出现一个102，但是102班级不存在。
所以为了保证cno字段中的值都是100和101，需要给cno字段添加外键约束。即在插入数据的时候就查找错误
那么：cno字段就是外键字段。cno字段中的每一个值都是外键值。

t_class 是父表，t_student是子表
删除表的顺序：先删子，再删父
创建表的顺序：先创建父，再创建子
删除数据的顺序：先删子，再删父
插入数据的顺序：先插入父，再插入子

思考：
子表中的外键引用的父表中的某个字段，被引用的这个字段必须是主键吗？
不一定是主键，但至少具有 unique 约束，外键值可以为NULL

创建父表和子表，先父后子

drop table if exists t_student;
drop table if exists t_class;

create table t_class( //创建主表
	classno int primary key, //将父表t_class的classno设置成主键
    classname varchar(255)
);
create table t_student(
	no int primary key auto_increment,  //将no自增主键
    name varchar(255),
    cno int,  //这是对应的父类的值，将通过cno来和父类的classno进行对应查找,两者的值相同
    foreign key(cno) references t_class(classno)  //将子表与父表外键约束连接，子表中的cno值必须存在于父表中的classno值中
)
image-20230128214120067

插入父子表，先父后子，父表没有信息，子表中没有与其对应的外键约束信息就插入不了子表，所以必须先插入父表，再插入子表

//先插入父表
insert into t_class(classno, classname) values(100, '北京市大兴区亦庄镇第二中学高三1班');
insert into t_class(classno, classname) values(101, '北京市大兴区亦庄镇第二中学高三1班');
image-20230128214625408 
 //再插入子表
insert into t_student(name,cno) values('jack', 100);
insert into t_student(name,cno) values('lucy', 100);
insert into t_student(name,cno) values('lilei', 100);
insert into t_student(name,cno) values('hanmeimei', 100);
insert into t_student(name,cno) values('hanmeimei', 100);
insert into t_student(name,cno) values('lisi', 101);
insert into t_student(name,cno) values('wangwu', 101);
insert into t_student(name,cno) values('zhaoliu', 101);
//子表中外键值可以为NULL
insert into t_student(name) values('xiaoming');
image-20230129105047480

存储引擎

概述

存储引擎是MySQL中特有的一个术语，其他数据库中没有。（Oracle中，有但不叫这个名字）

存储引擎实际上是一个表存储/组织数据的一种方式

不同的存储引擎，表存储数据的方式不同

操作

给表添加/指定存储引擎

//在建表的时候可以给表指定存储引擎
create table t_student(
	id int primary key,  //设置成主键
    name varchar(255),
    cno int,
    foreign key(cno) references t_class(classno)
)engine=InnoDB default charset=utf8;  //存储引擎为InnoDB，存储格式为utf8格式
image-20230129152725098 
//在建表的时候可以变字符集格式和存储引擎
create table t_product(
	id int primary key, //id为主键
    name varchar(255)
) engine=MyISAM default charset=gbk;  //存储引擎为MyISAM，字符编码为gbk格式的
image-20230129153204481

查看此mysql版本 select version();

点击此处查看mysql版本支持的存储引擎 show engines \G

image-20230129153544829

九大存储引擎，此版本支持八个

存储引擎介绍

InnoDB 存储引擎（默认）

这是mysql默认的存储引擎，同时也是一个重量级的存储引擎。
InnoDB支持事务，支持数据库崩溃后自动恢复机制。
InnoDB存储引擎最主要的特点是：非常安全。

特征：

– 每个 InnoDB 表在数据库目录中以.frm 格式文件表示
– InnoDB 表空间 tablespace 被用于存储表的内容（表空间是一个逻辑名称。表空间存储数据+索引。）
– 提供一组用来记录事务性活动的日志文件
– 用 COMMIT(提交)、SAVEPOINT 及ROLLBACK(回滚)支持事务处理
– 提供全 ACID 兼容
– 在 MySQL 服务器崩溃后提供自动恢复
– 多版本（MVCC）和行级锁定
– 支持外键及引用的完整性，包括级联删除和更新

InnoDB最大的特点就是支持事务：
以保证数据的安全。效率不是很高，并且也不能压缩，不能转换为只读，
不能很好的节省存储空间。

MyISAM 存储引擎

它管理的表具有以下特征：
使用三个文件表示每个表：

  • 格式文件 — 存储表结构的定义（mytable.frm）

  • 数据文件 — 存储表行的内容（mytable.MYD）

  • 索引文件 — 存储表上索引（mytable.MYI）：索引是一本书的目录，缩小扫描范围，提高查询效率的一种机制。

    可被转换为压缩、只读表来节省空间
    InnoDB中每个表都以.frm格式的文件表示

    提示一下：
    对于一张表来说，只要是主键，
    或者加有unique约束的字段上会自动创建索引。

MyISAM存储引擎特点：
    可被转换为压缩、只读表来节省空间
    这是这种存储引擎的优势！！！！

MyISAM不支持事务机制，安全性低。

MEMORY 存储引擎

使用 MEMORY 存储引擎的表，其数据存储在内存中（一断电数据 就消失了）而不是硬盘，且行的长度固定，
这两个特点使得 MEMORY 存储引擎非常快。

MEMORY 存储引擎管理的表具有下列特征：
    – 在数据库目录内，每个表均以.frm 格式的文件表示。
    – 表数据及索引被存储在内存中。（目的就是快，查询快！）
    – 表级锁机制。
    – 不能包含 TEXT 或 BLOB 字段。

MEMORY 存储引擎以前被称为HEAP 引擎。

MEMORY引擎优点：查询效率是最高的。不需要和硬盘交互。
MEMORY引擎缺点：不安全，关机之后数据消失。因为数据和索引都是在内存当中。

事务

概述

一个事务就是一个完整的业务逻辑，是一个最小的工作单元，不可再分

完整的业务逻辑：

假设转账，从A账户向B账户中转账10000.
将A账户的钱减去10000（update语句）
将B账户的钱加上10000（update语句）
这就是一个完整的业务逻辑。

以上的操作是一个最小的工作单元，要么同时成功，要么同时失败，不可再分。
这两个update语句要求必须同时成功或者同时失败，这样才能保证钱是正确的。

只有DML语句(即 insert,delete,update)和事务有关系，其他语句都无关

事务就是批量的DML语句同时成功，或者同时失败！保证数据的安全性，不能A给B钱，A给钱失败了而B却收到了A给的钱，需要同时成功或同时失败

多条DML语句的同时成功或同时失败

InnoDB存储引擎：提供一组用来记录事务性活动的日志文件

在事务的执行过程中，每一条DML的操作都会记录到“事务性活动的日志文件”中。
在事务的执行过程中，我们可以提交事务，也可以回滚事务。

提交事务？
    清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。
    提交事务标志着，事务的结束。并且是一种全部成功的结束。

回滚事务？
    将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件
    回滚事务标志着，事务的结束。并且是一种全部失败的结束。

操作

事务开始 : start transaction

没提交之前想撤销，即回滚: rollback

写完事务后提交（保存或存档）: commit 

即：先事务开始
start transaction      // 或者 set autocommit = off  //关闭自动提交
如果有想撤回的，回滚
rollback
最后提交信息
commit

mysql默认是自动提交的，即自动存档，每执行一条DML语句，则提交一次

这种自动提交实际上是不符合我们的开发习惯，因为一个业务通常是需要多条DML语句共同执行才能完成的，为了保证数据的安全，必须要求同时成功之后再提交，所以不能执行一条就提交一条。

关闭自动提交机制命令: start transaction; 

//回滚事务演示
use bjpowernode;

start transaction; //关闭自动提交机制命令
insert into t_user values(10,'abc');
insert into t_user values(10,'abc');
select * from t_user; //先查询，执行语句都存在
rollback; //回滚事务，将所有的执行语句撤回
select * from t_user;  //再查询，执行语句都不存在了
image-20230129164233235 
//提交事务演示
use bjpower node;

start transaction;
insert into t_user values(11,'jack','tj');
insert into t_user values(11,'jack','tj');

commit; //提交事务，保存存档
select * from t_user; //都存在

rollback; //回滚撤回事务，发现已经提交了无法撤销
select * from t_user; //都存在
image-20230129164743848

四个特性

  • 原子性

    说明事务是最小的工作单元。不可再分。

  • 一致性

    所有事务要求，在同一个事务当中，所有操作必须同时成功，或者同时失败，以保证数据的一致性。

  • 隔离性

    A事务和B事务之间具有一定的隔离。

    教室A和教室B之间有一道墙，这道墙就是隔离性。
    A事务在操作一张表的时候，另一个事务B也操作这张表会有隔离（有条件，下面讲）

  • 持久性

    事务最终结束的一个保障。事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上！

重点：事务的隔离性！！！

四个级别：

  • 读未提交 （最低级别）：read uncommitted 事务A可以读取到事务B未提交的数据。
    这种隔离级别存在的问题就是：脏读现象！(Dirty Read)，即读到了脏数据。
    这种隔离级别一般都是理论上的，大多数的数据库隔离级别都是二档起步！

  • 读已提交：read committed : 事务B提交了事务A才能读取

    解决了脏读的现象。

    存在问题：不可重复性

    一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值。
即事务B提交完数据后，事务A可以读取到这些数据，如果事务B再改变数据的话，事务A都是读取的事务B的最新值，不管事务A什么状态，读取的事务B数据都是最新值

  • 可重复读：repeatable read ：MySQL默认级别；提交之后也读不到，永远读取的都是刚开启事务时的数据

    事务A开启之后，不管是多久，每一次在事务A中读取到的数据都是一致的。即使事务B将数据已经修改，并且提交了，事务A读取到的数据还是没有发生改变，这就是可重复读。
    会出现幻读，每一次读取到的数据都是幻象。不够真实！

    就是每一次读到的事务B数据都是刚开启事务A时的数据，如果事务B在事务A运行过程中改变数据，则事务A是不会受到影响的。

  • 序列化/串行化：serializable ：最高隔离级别

    不能并发，即事务B还在修改数据，事务A就不能查到（会卡住），直到事务B修改完数据并提交(commit)后，事务A才能查到事务B的数据。

验证隔离性四个级别

  • 读未提交：read uncommitted

    事务B还没有提交(commit) ，事务A就已经读取到了事务B的数据

    被测试的表t_user
验证：read uncommitted
//将表设置为read uncommitted
mysql> set global transaction isolation level read uncommitted; 
    事务A													事务B
--------------------------------------------------------------------------------
use bjpowernode;
												use bjpowernode;
start transaction;
select * from t_user;
												start transaction;
												insert into t_user values('zhangsan');
select * from t_user;

  • 读已提交：read committed

    事务B提交后，事务A才能查到事务B的数据

    验证：read committed
//将表设置为 read committed
mysql> set global transaction isolation level read committed;
    事务A													事务B
--------------------------------------------------------------------------------
use bjpowernode;
												use bjpowernode;
start transaction;
												start transaction;
select * from t_user;
												insert into t_user values('zhangsan');
select * from t_user; //没有查到'zhangsan'这个信息，因为事务B还没有保存
												commit;
select * from t_user; //事务B保存了，可以查到

  • 可重复读：repeatable read

    事务A在事务B修改前运行，则读取不到事务B修改后的数据

    验证：repeatable read
//将表设置成 repeatable read
mysql> set global transaction isolation level repeatable read;
    事务A												事务B
--------------------------------------------------------------------------------
use bjpowernode;
												use bjpowernode;
start transaction;
												start transaction;
select * from t_user; //读取的是没改变的t_user
												insert into t_user values('lisi');
												insert into t_user values('wangwu');
												commit;
select * from t_user; //读取的还是没改变的t_user

  • 序列化/串行化：serializable

    事务B还在修改数据没提交的时候，事务A查事务B是会卡住的，直到事务B提交完

    验证：serializable
//将表设置成 serializable
mysql> set global transaction isolation level serializable;
    事务A												事务B
--------------------------------------------------------------------------------
												use bjpowernode;
use bjpowernode;
												start transaction;
start transaction;
												select * from t_user;
												insert into t_user values('abc');
select * from t_user;  //事务B未保存执行不了，处于卡着的状态，事务B一保存直接查到
												commit;

索引

概述

索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制，一张表的一个字段可以添加一个索引，也可以多个字段联合起来添加索引，索引相当于一本书的目录，方便查找指定位置的信息

对于一张表来说，可以有两种方式查找信息
方式一：从第一个值开始查找，直到找到要查找的信息：
方式二：通过目录（索引）去定位到一个大致的位置，即缩小范围进行查找，效率比较高

select * from t_user where name='zhangsan';

如果没有添加索引的话，上面这段代码是全表扫描
如果添加了索引的话，上面这段代码会根据索引进行扫描

数据库中的索引会排序，最底层的是类TreeSet进行排序，在MySQL中索引是一个B-Tree数据结构，即红黑树
遵循左小右大的原则存放，采用中序遍历方式遍历取数据

在任何数据库中主键都会自动添加索引对象
mysql中一个字段上如果有unique约束的话，也会自动创建索引对象

在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号

索引是一个单独的对象，不同的存储引擎以不同的形式存在
在 MyISAM 存储引擎中，索引存储在一个.MYI文件中
在 InnoDB 存储引擎中，索引存储在一个逻辑名称叫做 tablespace 的当中
在 MEMORY 存储引擎中，索引被存储在内存当中
不管索引存储在哪里，索引在mysql当中都是一个树的形式存在。

什么情况下考虑给字段添加索引？

  • 数据量庞大（到底有多么庞大算庞大，这个需要测试，因为每一个硬件环境不同）。
  • 该字段经常出现在where 的后面，以条件的形式存在，也就是说这个字段总是被扫描。
  • 该字段很少的DML（insert delete update）操作。（因为增删改之后，索引需要重新排序）

建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。

建议通过主键查询，建议通过 unique 约束的字段进行查询，效率是比较高的。

创建与删除索引

  • 创建索引

给字段创建索引：create index 索引名 on 表名(字段名); 

//给emp2表的ename字段添加索引，起名 emp2_ename_index
create index emp2_ename_index on emp2(ename);

  • 删除索引
    给字段删除索引：drop index 索引名 on 表名; 

    //删除emp2表中的索引emp_ename_index;
drop index epm2_ename_index on emp2;

在MySQL当中，怎么查看一个SQL语句是否使用了索引进行检索？ 

//不用索引查数据，用全局查
explain select * from emp2 where ename='KING';

image-20230130180631333

create index emp2_ename_index on emp2(index);
//用索引查数据
explain select * from emp2 where ename='KING';

image-20230130180223815

索引失效

一：模糊匹配中以%开头

select * from emp2 where ename like '%T';

image-20230130181844291

原因是因为模糊匹配当中以 “%” 开头了，以”%” 结尾则不会

尽量避免模糊查询的时候以 “%” 开始，这是一种优化的手段/策略

二：使用 or

合并查询结果集查找，如果使用 or ，那么要求 or 两边的条件字段都要有索引，才会走索引，如果其中一边有一个字段没有索引，那么另一个字段上的索引也会失效。

//使用or
explain select * from emp where ename='KING' or job='MANAGER';

image-20230130190503881

合并查询结果集查找，如果使用union，有索引的用索引，没索引的不用索引

explain select * from emp where ename='KING'
union
select * from emp where job='MANAGER';

image-20230130190935617

合并查询结果集查找建议使用 union

三：复合索引

两个字段或者更多字段联合起来添加一个索引，叫做复合索引

使用复合索引的时候，没有使用左侧的列（字段）查找，索引失效

//创建索引
create index emp2_job_sal_index on emp2(job,sal);
//只查找左侧指定字段，即job索引，会使用索引
explain select * from emp2 where job='MANAGER';

image-20230130191913297

//只查找右侧指定字段，即sal，不会使用索引
expalin select * from emp2 where sal=800;

image-20230130192226901

四：在where中索引添加运算（不是值）

在where中索引列中索引参加了运算，索引失效

explain select * from emp2 where sal=800;

image-20230130192841650

五：在where中索引使用了函数

where中索引使用了函数则索引失效

explain select * from emp2 where lower(ename)='SMITH';

image-20230130193134073

索引分类

索引在数据库当中分了很多类

  • 单一索引：一个字段上添加索引
  • 复合索引：两个字段或者更多的字段联合添加索引
  • 主键索引：主键上添加索引
  • 唯一性索引：具有 unique 约束的字段上添加索引

唯一性比较弱的字段上添加索引用处不大
唯一性比较弱的字段可能会存在大量的重复数据，这个时候索引不起什么作用
‘越唯一效率越高’

视图

概述

视图（view）：站在不同角度去看待同一份数据

视图的使用：

假设大表中的数据非常多，而又需要修改大表，可以将一个大表中的需要修改的数据提取到视图中进行修改

创建与删除视图

视图的创建

创建视图对象：create view 视图名称 as select * from 表名; 

create view dept2_view as select deptno,dname from dept2;
image-20230130194743659

删除视图对象：drop view 视图名称; 

drop view dept2_view;
image-20230130194908277

只有DQL语句才能以view的形式创建，即select xxx from xxx where xxx….

视图的用处

可以面向视图对象进行增删改查

对视图对象进行增删改查，会导致原表被操作

对视图的各种操作会直接影响到原表

即:视图就是原表的一部分，对视图的改变就是对原表的改变

//面向视图查询
select * from dept2_view;
image-20230130195808054 
//面向视图插入,视图中的值改变，原表中的值也会变
insert into dept2_view(deptno,dname) values(60,'ZHANGSAN');
image-20230130200447243 
//面向视图删除，只删除数据不删除表
delete from dept2_view;
//查询原表数据
select * from dept2_view; //数据已删完
image-20230130200716178 
//创建表与表连接的视图，更改此视图内容原表相应内容也会被更改
create view emp2_dept_view
as
	select
		e2.ename, e2.sal, d.dname
	from
		emp2 as e2
	join
		dept as d
	on
		e2.deptno=d.deptno;
//查看视图对象
select * from emp2_dept_view;
image-20230130202028436

使用

假设有一条非常复杂的SQL语句，而这条SQL语句需要在不同位置上反复使用

可以把这条复杂的语句提取要修改的数据为视图对象，在这个视图对象中修改数据，这样也会修改这条复杂的SQL语句

增删改查也叫 CRUD
C:Create(增)
R:Retrieve(查：检索)
U:Update(改)
D:Delete(删)

DBA常用命令

数据导入和导出(数据的备份)

数据导出

在windows 的 dos 命令窗口中（cmd）

mysqldump bjpowernode>D:\bjpowernode.sal -uroot -p123456

也可以导出指定的表

mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p123456

数据导入

注意：需要先登录到mysql数据库服务器上

然后创建数据库：create database bjpowernode;

使用数据库：use bjpowernode;

然后初始化数据库：source D:\bjpowernode.sql

数据库三范式（重要）

概述

  • 第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分
  • 第二范式：建立在第一范式的基础上，要求所有非主键字段完全依赖主键，不要产生部分依赖
  • 第三范式：建立在第二范式的基础上，要求所有非主键字段直接依赖主键，不要产生传递依赖

第一范式

要求任何一张表必须有主键，每一个字段原子性不可再分

最核心，最重要的范式，所有表的设计都需要满足

学生编号 	学生姓名 		联系方式
------------------------------------------
1001		张三		zs@gmail.com,1359999999
1002		李四		ls@gmail.com,13699999999
1001		王五		ww@163.net,13488888888

以上表不满足第一范式，第一：没有主键(PK)；第二：联系方式可以分为邮箱和电话，不满足原子性不可再分

//满足第一范式
	学生编号(pk) 		学生姓名	邮箱地址			联系电话
	----------------------------------------------------
	1001				张三		zs@gmail.com	1359999999
	1002				李四		ls@gmail.com	13699999999
	1003				王五		ww@163.net		13488888888

第二范式

建立在第一范式的基础上，要求所有非主键字段完全依赖主键，不要产生部分依赖

学生编号 		学生姓名	 教师编号 	教师姓名
----------------------------------------------------
1001			张三			001		王老师
1002			李四			002		赵老师
1003			王五			001		王老师
1001			张三			002		赵老师

以上表不满足第一范式：没有主键

//满足第一范式
学生编号+教师编号(pk)				学生姓名 		 教师姓名
	----------------------------------------------------
	1001			001				张三			王老师
	1002			002				李四			赵老师
	1003			001				王五			王老师
	1001			002				张三			赵老师

以上表有主键（学生编号+教师编号）复合主键，一般复合主键都是多对多

不满足第二范式：主键是（学生编号+教师编号）复合主键，
而非主键字段不完全依赖主键（学生姓名依赖学生编号，教师姓名依赖教师编号，
而不是学生姓名或教师姓名依赖整个复合主键（即学生姓名依赖学生编号+教师编号）

部份依赖会产生数据冗余，空间浪费（即学生姓名重复，教师姓名重复）

可以使用三张表来表示多对多的关系

多对多，三张表，关系表两个外键！！！

//学生表
		学生编号(pk)			学生名字
	------------------------------------
		1001					张三
		1002					李四
		1003					王五
教师表
		教师编号(pk)		教师姓名
	--------------------------------------
		001					王老师
		002					赵老师
学生教师关系表
		id(pk)				学生编号(fk)				教师编号(fk)
		------------------------------------------------------
		1						1001						001
		2						1002						002
		3						1003						001
		4						1001						002

第三范式

建立在第二范式的基础上，要求所有非主键字段直接依赖主键，不要产生传递依赖

学生编号（PK） 		学生姓名 	班级编号 	 	班级名称
---------------------------------------------------------
	1001				张三		01			一年一班
	1002				李四		02			一年二班
	1003				王五		03			一年三班
	1004				赵六		03			一年三班

以上表满足第一范式：有主键，每一个字段原子性不可再分

以上表满足第二范式：主键不是复合主键，非主键都是依赖主键的

不满足第三范式：产生了传递依赖（一年一班依赖01，01依赖1001），
一年三班产生了冗余问题，一对多关系

一对多，两张表，多的表加外键

班级表：一
班级编号(pk)					班级名称
----------------------------------------
01								一年一班
02								一年二班
03								一年三班
学生表：多
学生编号（PK） 	学生姓名 		班级编号(fk)
-------------------------------------------
1001				张三			01			
1002				李四			02			
1003				王五			03			
1004				赵六			03

总结

  • 一对多，两张表，多的表加外键

  • 多对多，三张表，关系表两个外键

  • 一对一

    一对一可以一张表，如果一张表中字段太多可以拆分表

    没拆分之前：

    image-20230130213817968

一对一，外键唯一！！！

可以拆分为两张表，不过有条件，外键中必须要加一个unique约束，一个只能对应一个

//t_login 登录信息表
		id(pk)		login_name		login_pwd	
		---------------------------------
		1		zhangsan			123			
		2		lisi				123	
t_user 用户详细信息表
		id(pk)		real_name		email		address........	login_id(fk+unique)  
		//加个unique约束，一个只能对应t_user表中的一个：即一对一
	-----------------------------------------------------------------------------------------
		100			张三			zhangsan@xxx						1
		200			李四			lisi@xxx