MySQL

MySQL

mysql表的创建和增删改查

创建数据库表

 

  1. 字符集：utf8mb4=utf8

  2. ##或--空格 单行注释，多行注释/* ......*/

  3. 创建数据库表：在新建查询中创建数据库表，创建create，修改alter，删除drop。

    例：create table t_student( );

create + (表格) +（表的名字）+（）；

  1. 在括号内创建字段：son学号，sname姓名，sex性别，，，，每个字段之间用逗号隔开，

    最后一个字段不用。

  2. 每个字段对应不同类型：son学号，整数类型int，sname姓名，字符变量varchar，

    date日期，date。

  3. 运行：全选--> 右键 -->单击 -->运行已选择

  4. 显示🆗创建成功，

修改添加数据

 

  1. 查看表的结构：desc t_student; (表的名字)

  2. 查看表中数据：select * from t_student; (表的名字)

  3. 查看建表语句：show create table t_student;

  4. 添加数据：insert into t_student values（1,张三，‘男’，18，，，，）;运行显示影响了一行数据。

  5. 写入当前的时间：now（），sysdate（），CURRENT_DATE( )

  6. 如果给出信息不全，要在valuse前面给出对应的字段名字。

修改删除表

 

  1. 修改表的结构：增加一列：alter table t_student add score double(5，2);

    修改+表+表名+添加+成绩（要增加的东西）+数据类型（整数位数，小数位数）

  2. 修改表中数据：update t_student sat score = 123.56 where sno=1;

  3. 删除一列：alter table t_student drop(删除) score；

  4. 增加一列，放在最前面：alter table t_student add score doubie(5,3) first;

  5. 增加一列，放在sex列的都后面：alter table t_student add score doubie(5,3) after sex；

  6. 修改一列：alter table t_student modify score float(4,1);modify修改的是列的类型，不会改变列名。

  7. 修改一列：alter table t_student change score score1 double(5,1);change修改的是列的名和类型。

  8. 删除表：drop table t_student;

快速创建

  1. --添加一张表:快速添加：和t_student结构，数据都是一致的 create table t_student2 as select * from t_student; -- 查看t_student2数据 select * from t_student2;

  2. -- 快速添加：结构和t_student一致，数据没有 create table t_student3 as select * from t_student where 1=2; --查看t_student3数据 select * from t_student3;

  3. --快速添加，部分列，部分数据一样 create table t_student4 as select sno,sname,age from t_student where sname='李四'; -- 查看t_student4的数据 select * from t_student4;

  • 删除数据

  • -- 删除数据操作 delete from t_syudent;-- (一条一条的删除表中数据，效率较低，且在添加数据时会接上前面删除的序号) truncate table t_student;-- （数据清空，只保留结构，且在输入数据从1开始，相当于新建了一张表）

表查询

 

  1. -- 对emp表查询： select * from emp ;-- * 代表所有数据 -- 显示部分列： select empno,ename,sal from emp; -- 显示部分行：where 子句 select * from emp where sal > 2000; -- 显示部分行，部分列 select empno,ename,job,mgr from emp where sal > 2000; --起别名:as - alias 别名 select empno 员工编号,ename 姓名,sal 工资 from emp; select empno as员工 编号,ename as姓 名,sal as工 资 from emp; select empno as'员工编号',ename as'姓名',sal as'工资' from emp; -- 错误原因： select empno as员工 编号,ename as姓 名,sal as工 资 from emp -- > 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for -- the right syntax to use near '编号,ename as姓 名,sal as工 资 from emp' at line 1 -- > 时间: 0s -- 当别名中带有特殊字符或空格不能省略单引号

  2. -- 算数运算符：+ select empno,ename,sal,sal+1000 as'涨薪后',deptno from emp where sal < 2500; select empno,ename,sal,comm,sal+comm from emp;-- 前面数为null时，相加结果也为null

  3. -- 去重操作distinct select job from emp; select distinct job from emp; select job,deptno from emp; select distinct job,deptno from emp;

  4. -- 排序 order by select * from emp order by sal; -- 默认情况下按升序排列 select * from emp order by sal asc;-- asc=升序缩写 select * from emp order by sal desc;-- desc=降序缩写 select * from emp order by sal asc,deptno desc;-- 在工资升序的情况下，deptno降序

 

单表查询总结

-- 单表查询总结

  1. -- select语句总结 select column ,group_function(column) from table [where condition] [group by group_by_expression] [having group_condition] [order by column];

-- 注意：顺序固定，不可改变顺序

-- select语句执行顺序 ​ from - where - group by - select - having - order by

  1. -- 单表查询练习： -- 列出工资最小最值小于2000的职位 select job,min(sal) from emp group by job having min(sal)<2000;

-- 列出平均工资大于1200元的部门和工作搭配组合 ​ select deptno,job,avg(sal) from emp group by deptno,job having avg(sal)>1200 order by deptno ; ​ -- 统计人数少于4的部门平均工资 ​ select deptno,count(1),avg(sal) from emp group by deptno having count(1)<4; ​ -- 统计个部门的最高工资，排除最高工资小于3000的部门 ​ select deptno,max(sal) from emp group by deptno having max(sal)<3000;

多表查询SQL99语法

 

-- 条件：

-- 1.筛选条件: where having -- 2.连接条件: on ,using,natural -- SQL99语法：筛选条件和连接条件是分开的

select * from emp e inner join dept d on(e.deptno=d.deptno) where sal > 3500;

问题：-- 查询员工的编号，姓名，部门编号； select empno,ename,deptno from emp; -- 查询员工的编号，姓名，部门编号,部门名称； select * from dept;-- 四条记录 select * from emp;-- 十四条记录

================================================================================

-- 多表查询： ​ 1. -- cross join 交叉连接 ​ select * from emp cross join dept;-- （14x4=56）条记录，笛卡尔乘积：实际上没有意义，只有理论意义 ​ select * from emp join dept;-- cross可以省略，mysql中可以，oracle中不可以

  1. -- 自然连接 natural join -- 优点：自动匹配同名的列，同名列只展示一次就可以，简单 select * from emp natural join dept; -- 缺点：查询字段时，没有指定字段所属的数据库表，效率低 -- 解决：指定表名 select emp.empno,emp.ename,emp.sal,emp.deptno,dept.dname,dept.loc from emp natural join dept; -- 缺点：表名太长 -- 解决：给表起别名 select e.empno,e.ename,e.sal,e.deptno,d.dname,d.loc from emp e natural join dept d;

-- 自然连接natural join：缺点：自动匹配表中所有的同名列，但是有时候只想匹配部分同名列

  1. -- 解决：内连接-using 子句 select * from emp e inner join dept d-- inner可以不写 using(deptno)- - 这里不能写natural join 了，这里是内连接 -- using 缺点：关联字段，必须是同名的 -- 解决: 内连接-on子句 select * from emp e inner join dept d on(e.deptno=d.deptno);

    -- inner join-on子句:显示的是所有匹配的信息 select * from emp e inner join dept d on e.deptno=d.deptno;

    select * from emp; select * from dept; -- on -- 问题，40号部门没有员工，没有显示在查询结果中 -- 如果员工没有部门，那么也没有显示在查询结果中

    ==============================================================

    1. -- outer join 外连接：除了显示匹配的数据之外，还可以显示部分或者全部不匹配的数据

      • -- 左外链接 left outer join :左面表的信息即使不匹配，也可以查看出效果 select * from emp e left outer join dept d on e.deptno=d.deptno;

      • -- 右外连接 right outer join ：右面表的信息即使不匹配，也可以查看出效果 select * from emp e right outer join dept d on e.deptno=d.deptno;

      • -- 全外连接full outer join：这个语法在MySQL中不支持，在oracle中支持-- 展示左右表全部不匹配的数据 select * from emp e full outer join dept d on e.deptno=d.deptno;

      • -- 解决MySQL中不支持全外连接的的问题：

      • select * from emp e left outer join dept d -- outer可省略 on e.deptno=d.deptno union-- 并集 去重 效率低 select * from emp e right outer join dept d on e.deptno=d.deptno;

      • select * from emp e left outer join dept d -- outer可省略 on e.deptno=d.deptno union all-- 并集 不去重 效率高 select * from emp e right outer join dept d on e.deptno=d.deptno;

      • -- MySQL中对集合操作比较弱，只支持并集操作，不支持交集和差集操作（oracle中支持交集，差集）

    -- 多表连接的类型：1.交叉连接cross join 2.自然连接 natural join -- 3.内连接-using子句 4. 内连接-on子句 -- 综合来看：内连接—on子句用到最多

三表查询99语法

-- 三表联查

select * from emp; select * from dept; select * from salgrade;

-- 查询员工的编号，薪水，部门编号，部门名称，薪水等级

select e.ename,e.sal,e.empno,e.deptno,d.deptno,d.dname,s.* from emp e right join dept d on e.deptno=d.deptno -- 相当于两表查询后又加了一个表 inner join salgrade s -- 查询emp表的部分列和dept全表，再加上salgrade的部分列 on e.sal between s.losal and s.hisal-- 在这个区间满足条件运行，员工工资在工资等级表中的最高级最低级区间

 

-- 自连接

  1. -- 查询员工的编号，姓名，上级编号，上级姓名(上级也是员工) select * from emp;

  2. -- 员工姓名和领导姓名都在一列，所以，要提取出领导姓名要制定条件 -- 先内连接自己，给自己别名为e2，相当于有创建了一张表，表e和表e2 内容相同 -- 然后制定条件，e表中领导编号等于e2表中员工编号

select * ​ from emp e ​ inner join emp e2 ​ on e.mgr = e2.empno;

  1. select e.empno 员工编号,e.ename 员工姓名,e.mgr 领导编号,e2.ename 员工领导姓名 from emp e inner join emp e2 on e.mgr = e2.empno;

  2. -- 没有领导的员工也显示出来 -- 左外连接select e.empno 员工编号,e.ename 员工姓名,e.mgr 领导编号,e2.ename 员工领导姓名 from emp e left join emp e2 on e.mgr = e2.empno;

=======================================================================================

-- 92语法

-- 查询员工编号，姓名，薪资，部门编号，部门名称

  1. select e.empno,e.ename,e.sal,e.deptno,d.dname from emp e,dept d -- 相当于99语法中的cross join ，出现笛卡尔乘积，没有意义

  2. select e.empno,e.ename,e.sal,e.deptno,d.dname from emp e,dept d where e.deptno = d.deptno; -- 相当于99语法中的natural join

  3. -- 查询员工编号，姓名，薪资，部门编号，部门名称，工资大于2000 select e.empno,e.ename,e.sal,e.deptno,d.dname from emp e,dept d where e.deptno = d.deptno and e.sal>2000;

  4. -- 查询员工姓名，岗位，上级编号，上级名称（自连接） select e.job,e.ename,e.mgr,e2.ename from emp e,emp e2 where e.mgr = e2.ename;

  5. -- 查询员工编号，姓名，薪资，部门编号，部门名称,薪资等级 select e.empno,e.ename,e.sal,e.deptno,d.dname,s.grade from emp e,dept d,salgrade s where e.deptno = d.deptno and e.sal >= s.losal and e.sal <= s.hisal;

  6. -- 总结： -- 92语法麻烦 -- 92语法中，表的连接条件 和 表的筛选条件 是放在一起的并没有分开 -- 99语法中提供了等多的查询连接类型：cross，natural，inner，outer

不相关子查询

1. 什么是子查询？

执行顺序：先执行子查询，在执行外查询

不相关子查询： 子查询可以独立运行，成为不相关子查询

不相关子查询分类：根据子查询的结果行数，可以分为单行子查询和多行子查询

 

  • -- 引入子查询 -- 查询所有比clark工资高的员工信息 -- 步骤一 ： 先查出Clark的工资 select sal from emp where ename = 'chark' -- 2450 -- 步骤二 ： 在查询所有比clark工资高的员工信息 select * from emp where sal>2450; -- 两次命令解决问题：效率低，第二个命令依托于第一个命令 -- 因为第二个命令不确定，所以第二个也会导致修改

  • -- 步骤一和步骤二合并：子查询 select * from emp where sal> (select sal from emp where ename = 'chark' ); -- 一个命令解决问题：效率高

单行子查询

  1. -- 单行子查询 -- 查询工资高于平均工资的员工名字和工资 select ename,sal from emp where sal >(select avg (sal) from emp);

  2. -- 查询和clark同一部门且比他工资低的员工名字和薪资 -- 查询 select ename,sal from emp where deptno =( select deptno from emp where ename = 'SMITH') and sal<( select sal from emp where ename = 'SMITH' );

  3. -- 查询职务和SCOOT相同，比SCOOT雇佣时间早的员工信息 select * from emp where job=(select job from emp where ename = 'SCOOT') and hirdate<(select hirdate from emp where ename ='SCOOT');

多行子查询

-- 多行子查询(查询到的结果是多个)

  1. -- 查询部门20中职务 同 部门10的雇员一样 的雇员信息。 -- 步骤一： 先查询雇员信息 select * from emp; -- 步骤二：查询部门20中的雇员信息 select * from emp where deptno = 20; -- 步骤三: 查询部门10的雇员职务 select job from emp where dentno = 10; -- 步骤四：查询部门20中职务 同 部门10的雇员一样 的雇员信息。 select * from emp where deptno = 20 and job in (select job from emp where dentno = 10);-- /join =any()

  2. -- 问题2. -- 查询工资比所有SALESMAN都高的雇员的编号，名字，工资 -- 步骤一：查询雇员的编号，名字，工资 select empno,ename,sal from emp; -- 步骤二：查询SALESMAN的工资 select sal from emp where job = 'SALESMAN'; -- 步骤三： 查询工资比所有SALESMAN都高的雇员的编号，名字，工资 select empno,ename,sal from emp where sal < all(select sal from emp where job = 'SALESMAN'); -- 单行子查询也可运行 select empno,ename,sal from emp where sal < (select max(sal) from emp where job = 'SALESMAN');

  3. -- 问题3. -- 查询工资低于任意一个CLERK的工资的员工信息。 select * from emp where sal < any(select sal from emp where job = 'CLERK') and job != 'CLERK'; -- 单行子查询也可运行 select * from emp where sal < (select max(sal) from emp where job = 'CLERK'); and job != 'CLERK';

相关子查询

-- 相关子查询:子查询不能独立运行

  1. -- 问题1. -- 查询最高工资的员工相关信息（不相关子查询） select * from emp where sal = (select max(sal) from emp); -- 查询每个部门最高工资的员工相关信息（相关子查询） -- 方法1.通过不相关子查询实现 -- 缺点：语句过多，到底多少部门未知 select * from emp where deptno = 10 and sal =(select max(sal) from emp where deptno = 10) union select * from emp where deptno = 20 and sal =(select max(sal) from emp where deptno = 20) union select * from emp where deptno = 30 and sal =(select max(sal) from emp where deptno = 30)

    -- 方法2.通过相关子查询实现 select * from emp e where sal = (select max(sal) from emp where deptno = e.deptno) order by deptno

 

  1. -- 问题2. 查询工资高于其所在岗位的平均工资的那些员工（相关查询） select * from emp e where sal >= (select avg(sal) from emp where job = e.job);

where子句的用法

-- where子句:将过滤条件放在where后，可以筛选/过滤出我们想要的符合条件数据 -- 查看emp表 select * from emp;

  1. -- where子句用法:where子句 + 关系运算符 select * from emp where deptno =10; select * from emp where deptno <=10; select * from emp where deptno >=10; select * from emp where deptno <10; select * from emp where deptno >10; select * from emp where deptno <>10; select * from emp where deptno !=10; select * from emp where job = 'CLERK'; select * from emp where job = 'clerk';-- 默认情况下不区分大小写 select * from emp where binary job = 'clerk';-- binary区分大小写 select * from emp where hiredate <'1981-12-25';

  2. -- where子句+逻辑运算符：and 与 select * from emp where sal>1500 and sal <3000; select * from emp where sal>1500 and sal <3000 order by sal; select * from emp where sal between 1500 and 3000 ;-- 包含1500和3000 select * from emp where sal>1500 && sal <3000;-- 不包含1500和3000

  3. -- where子句+逻辑运算符：or 或 select * from emp where deptno =10 or deptno =20; select * from emp where deptno =10 || deptno =20; select * from emp where deptno in (10,20);

  4. -- 模糊查询like select * from emp where ename like '%A%';-- %代表任意多个字符 select * from emp where ename like '_A%';-- _代表任意一个字符

  5. -- 关于null的查询 select * from emp where comm is null；-- is是 select * from emp where comm is not null；-- is not不是

  6. -- 关于小括号的使用:因为不同的运算符的优先级不同，加括号为了可读性 select * from emp where job = 'SALESMAN' or job = 'CLERK' and sal >=1500; select * from emp where job = 'SALESMAN' or job = 'CLERK' and (sal >=1500);

group by分组

 

  1. -- 分组 group by select * from emp; -- 统计各个部门的平均工资 select deptno,avg(sal) from emp;-- 字段和多行函数不能同时使用 select deptno,avg(sal) from emp group by deptno;-- 字段和多行函数不能同时使用，除非这个字段属于分组 select deptno,avg(sal) from emp group by deptno order by deptno desc;-- 按降序排列

-- 统计各个岗位的平均工资 ​ select job,avg(sal) from emp group by job; ​ select job,lower(job),avg(sal) from emp group by job;

  1. -- 分组后筛选 having -- 统计个部门工资，只显示平均工资2000以上的 select deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资>2000; select deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资>2000 order by deptno desc;

-- 统计各个岗位的平均工资，除了MANAGER ​ -- 方法1 ​ select job,avg(sal) from emp where job != 'MANAGER'group by job;-- where 分组前进行过滤 ​ -- 方法2 ​ select job,avg(sal) from emp group by job having job!='MANAGER';-- having 分组后进行过滤

mysql 表的约束

MySQL 索引

MySQL官方对索引的定义：“索引是帮助MySQL高校获取数据的数据结构

提取句子主干，就可以得到索引的本质，索引是数据的结构。

1. 列级约束

  • 主键索引 prmary key

    唯一标识，不可重复，主键只能有一个

  • 唯一索引unique key

    避免重复的列出现，多个列都可以标为唯一索引

  • 常规索引key

    默认的，index，key

2. 表级约束

全文索引

在特定的数据库引擎下才有，MyISAM

快速定位数据

 

=====================================外键约束======================================

外键约束只能加在表后面

外键约束foreign key，策略 1.不允许操作no action 2. 级联操作cascade（删除主表的内容会影响子表）

3.置空操作set null。

  1. -- 创建父表：班级表 create table t_class ( cno int (4) primary key auto_increment, cname varchar (10) not null, room char (4) )

  2. -- 添加班级数据 insert into t_class values (null,'java001','r803'); insert into t_class values (null,'java002','r403'); insert into t_class values (null,'java003','r803'); insert into t_class values (null,'大数据001','r103');

    -- 可以一次性添加多条记录 insert into t_class values (null,'java005','r803'),(null,'java004','r403'),(null,'java006','r803')

    -- 查询班级表 select * from t_class；

    -- 删除学生表 drop table t_student; -- 创建子表，学生表 create table t_student(-- 创建 sno int(6)primary key auto_increment, sname varchar(5)not null, classno char(4) -- 取值参考t_class表中的cno字段，不要求名字完全重复，但是类型长度定义尽量要求相同， );

-- 添加学生信息 insert into t_student values (null,'李四',1),(null,'王五',3); -- 查看学生表 select * from t_student;

-- 出现问题 -- 1. 添加一个学生对应的班级编号为4； insert into t_student values (null,'丽丽',4);

-- 2. 删除班级2班 delete from t_class where cno =1;

-- 出现问题的原因：外键因素，没有从语法上添加。 -- 解决方法：添加外键因素 -- 注意：外键约束只有表级约束没有列级约束。

create table t_student( sno int(6)primary key auto_increment, sname varchar(5)not null, classno int(4), constraint fk_stu_classno foreign key (classno) references t_class(cno) );

create table t_student( sno int(6)primary key auto_increment, sname varchar(5)not null, classno int(4) ); -- 在创建表以后添加外键约束 alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class(cno) -- 删除班级2班：直接删除不了，因为有外键约束 -- 加入外键策略 -- 策略1；no action 不允许操作 -- 通过操作sql操作 -- 先把班级2的学生对应的班级改成null update t_student set class = null where classno = 2; -- 然后删除班级2 delete t_class where cno = 2;

-- 策略二：删除三班：cascade 级联操作，操作主表时影响子表的外键信息 -- 先试试更新 update t_class set cno = 5 where cno = 3; -- 先删除之前的外键约束 alter table t_student drop foreign key fk_stu_classno; -- 重新添加外键约束 alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class(cno) on update cascade on delete cascade ; -- 删除 delete from t_class where cno=3; -- 策略三：set null 置空操作： -- 先删除之前的外键约束 alter table t_student drop foreign key fk_stu_classno; -- 重新添加外键约束 alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class(cno) on update set null on delete set null ; -- 更新 update t_class set cno = 8 where cno = 1; --查看学生表 select * from t_student; -- 注意：策略二和策略三的删除操作可以混搭使用 alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class(cno) on update cascade on delete set null

--应用场合 -- （1）朋友圈删除，点赞，评论都删除-- 级联操作 -- （2） 解散班级，对应的学生置为班级为null就可以，--set null；

MySQL备份

 

为什么要备份？

  • 防止数据丢失

  • 数据转移

  • MySQL数据库备份的方式

    直接拷贝物理文件

    在Sqlyog这种可视化工具中手动导出

    使用命令行导出 mysqldump 命令行使用

事务并发问题

 

  1. 脏读Dirty read 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这是另外一个事务也访问了这个数据，然后是用了这个数据，因为这个数据是还没有提交的数据，那么另外一个事务读到这个数据是“脏数据”，依据“脏数据”所做出的操作可能是不正确的

  2. 不可重复读Unrepeatablereade

    指在一个事务内多次重读同一数据，在这个事务还没结束时，另一个事务也访问该数据，那么，在第一个事务中的两次读数据之间，由于第二个数据的修改导致第一个事务两次读取的数据可能不太一样，这就发生了在一个事务内两次读到的数据是不一样的情况，因此成为不可重复读。

  3. 幻读Phantom read

    幻读与不可重复读类似。他发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2)插入了一些数据时，在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

  4. 不可重复读和幻读的区别

    不可重复读重点是修改，幻读重点在于增加一条新纪录或删除

    解决不可重复读的问题只需要锁住满足条件，解决幻读需要锁表

事务的隔离级别

 

事务的隔离级别用于决定如何控制并发用户读写数据的操作，数据库是允许多用户并发访问的，如果多个用户同时开启事务并对同一数据进行读写操作的话，有可能会出现脏读，不可重复读和幻读问题，所以WySQL中提供了四种隔离来解决上述问题，

 

事务的隔离级别从低到高依次为READ UNCOMMITTED, READ COMMMITTED, REPEATABLE READ 以及SERIAIZABLE，隔离级别越低，越能支持高并发的数据操作

 

查看默认的事务隔离级别： MySQL中默认的是repeatable read

select @@transaction_isolation;

设置事务的隔离级别 （设置当前会话的隔离级别）

set session transaction isolation level read uncommitted；

set session transaction isolation level read committed；

set session transaction isolation level repeatable read；

set session transaction isolation level serializable；

事务及其特性

 

  1. 事务及其特征： -- 是用来维护数据库完整性等，它能保证一系列的MySQL操作要么全部执行，要么全部 不执行

  2. 事务的概念： 是事务的是一个操作序列，该操作序列中的多个操作，要么都做，要么都不做，是一个不可分割的工作位， 是数据库环境中的逻辑工作单位，有DBMS（数据库管理系统）中的事务管理子系统负责事物的处理 目前常用的存储引擎有lnnoD8（MySQL5.5以后默认的存储引擎）和MyISAM（MySQL5.5之前默认的存储引擎）， 其中lnnoD8支持事务处理机制，而MyISAM不支持

  3. 事务的特性： 事务处理能够确保除非事务性序列内的所有操作都成功完成，否则不会用就更新面向数据的资源，通过将一组 相关操作组合为一个要么全部成功，要么全部失败的序列，可以简化错误恢复并使应用程序更加可靠 但并不是所有操作序列都可以称为事务，这是因为一个序列要成为事务，必须要满足事务的原子性（Atomicity），（Consistency）一致性，（Isolation）隔离性和（Durability）持久性，这四个特性简称为 ACID特性。

  • 原子性：原子是自然界最小的颗粒，具有不可再分的特性，事务中的所有操作可以看做一个原子，事务是应用中不可再分的最小的逻辑执行体。使用事务对数据进行修改的操作序列，要么全部执行，要么全不执行，通常，某个事务中的操作都具有共同的目标，并且是相互依赖的，如果数据库只执行这些操作的一部分，则可能会破坏事务的总体目标，而原子性消除了系统只处理部分操作的可能性。

  • 一致性：一致性是指事务执行的结果必须使数据库从一个一致性状态，变到另一个一致性状态。当数据库中只包含事务成功提交的结果时，数据库处于一致性状态，一致性是通过原子性来保证的。

  • 隔离性：隔离性是指各个事务的执行互不干扰，任意一个事务的内部操作对其他并发的事务，都是隔离的。也就是说，并发执行的事务中既不能看到对方的中间状态，也不能互相影响。

  • 持久性：持久性是指事务一旦提交，对数据所做的任何改变，都要记录到永久存储器中，通常是保存进物理数据库，即使数据库出现故障，提交的数据也应该能够恢复，但如果是由于外部原因导致的数据故障，如硬盘被损坏，那么之前提交的数据则可能会丢失。

  1. SQL展示：

    -- 创建账户表 create table account( id int primary key auto_increment, uname varchar(10) not null, balance double );

    -- 查看表中数据 select * from account;

    -- 在表中插入数据 insert into account values (null,'丽丽',2000),(null,'小刚',2000);

    -- 丽丽给小刚转账200元 update account set balance = balance - 200 where id = 1; update account set balance = balance + 200 where id = 2; -- 默认一个DML语句是一个事务，所以上面的语句执行力2个事务。 -- 必须让上面两个才做控制在一个事务中： -- 手动开启事务： start transaction; update account set balance = balance - 200 where id = 1; update account set balance = balance + 200 where id = 2;

    -- 手动回滚：刚在执行的操作全部取消： rollback;

    -- 手动提交： commit; -- 在回滚和提交操作前，数据库中的数据都是缓存中的数据，而不是数据库的真实数据

     

 

视图

 

视图本质上就是：一个查询语句，是一个虚拟的表，不存在表，查看视图，其实就是查看视图对应的SQL语句

  1. 视图的概念：

视图是一个从单张或多张基础数据表或其他视图中构建出来的虚拟表，同基础表一样，视图中也包含了一系列带有名称的列和行数据，但是数据库中只是存在视图的定义，也就是动态检索数据的查询工具，而并不存放视图中的数据，这些数据依旧存放在建构视图的基础表中，只有当用户使用视图时才去数据库请求相对应的数据，及视图中的数据是在引用视图时动态生成的。因此视图中的数据依赖于构建视图的基础表，如果基础表中的数据发生了变化，视图中相应的数据也会跟着改变。

  1. 视图的好处：

    简化用户操作：视图可以使用户将注意力集中在所关心的数据上，而不需要关心数据表的结构，与其他表的关联条件以及查询条件等。

    对机密数据提供安全保护：有了视图，就可以在设计数据库应用系统时，对不同的用户定义不同的视图，避免机密数据出现不应该看到这些数据的用户视图上，这样，视图就自动提供了对机密数据的安全保护功能

  2. SQL展示:

-- 创建单表视图 create or replace view myview01 as select empno,ename,job,deptno from emp where deptno = 20 with check option;

-- 查看视图 select * from myview01; -- 在视图中插入数据 insert into myview01 (empno,ename,job,deptno)values(9999,'李丽丽','CLERK',20); insert into myview01 (empno,ename,job,deptno)values(8888,'王丽丽','CLERK',30); insert into myview01 (empno,ename,job,deptno)values(6666,'张丽丽','CLERK',30);

-- 创建多张表视图 create or replace view myview02 as select e.empno,e.ename,e.sal,d.deptno,d.dname from emp e join dept d on e.deptno = d.deptno where sal >2000

select * from myview02;

-- 创建统计视图 create or replace view myview03 as select e.deptno,d.dname,avg(sal),min(sal),count(1) from emp e join dept d using(deptno) group by e.deptno

select *from myview03;

-- 基于视图的视图 create or replace view myview04 as select * from myview03 where deptno = 20;

select * from myview04;

MySQL存储过程

什么是存储过程Stored Procedure

SQL是一种非常便利的语言，从数据库抽取数据，或者对特定的数据集中更新时，都能通过简洁直观的代码实现。

但是这个所谓的“简洁”也是有限制，SQL基本是一个命令实现一个处理，是所谓的非程序语言。

再不能边写流畅的情况下，所有处理只能通过一个个命令来实现，当然，通过使用连接即子查询，即使使用SQL 的但一命令也能实现一些高级的处理，但是，其局限性是显而易见的，例如，在SQL语言中就很难实现针对不同条件进行不同的处理以及循环等功能。

这个时候就出现了存储过程这个概念，简单的说，存储过程就是数据库中保存的一系列SQL命令的集合，也可以互相之间有关系的SQL命令组织在一起形成的一个小程序。

  1. 存储过程的优点

    • 提高执行性能：存储过程执行率之所高，在于普通的SQL语句，每次都会对于法分析，编译，执行，而存储过程只是在第一次执行语法分析，编译，执行，以后都是对结果进行调用。

    • 可减轻网络负担：使用存储过程中，复杂的数据库操作也可以在数据库服务器中完成。只需要从客户端传递给数据必要的参数就行，比起需要多次传递SQL命令本身，这大大的减轻了网络负担。

    • 可将数据库的处理黑匣子化：应用程序中完全不用考虑存储过程的内部详细处理，只需要知道调用那个存储过程就可以了

  2. 练习

-- 定义一个存储过程，它没有返回值 -- 实现一个模糊查询的操作： select * from emp where ename like '%A%';

create procedure mypro01(name varchar(10)) begin if name is null or name = ''then select * from emp; else select * from emp where ename like concat( '%', name , '%'); end if; end ;

-- 删除存储过程： drop procedure mypro01;

-- 调用存储过程 call mypro01 (null); call mypro01 ('S');

-- 定义一个有返回值的存储过程 -- 实现一个模糊查询的操作： -- 参数前面的in可以省略不写 -- found_rows()mysql中定义的一个函数，作用是返回查询结果的条数 create procedure mypro02 (in name varchar(10),out num int(3)) begin if name is null or name = ''then select * from emp; else select * from emp where ename like concat ('%',name,'%'); end if; select found_rows() into num; end;

-- 调用存储过程 call mypro02(null,@num); select @num;

call mypro02('S',@num); select @num;

posted @   懒回顾…  阅读(26)  评论(0)  编辑  收藏  举报
相关博文：
·  HTML5
·  CSS3学习
·  Mysql基础(网安)
·  Mysql
·  1、SQL 语法
阅读排行：
· 25岁的心里话
· 闲置电脑爆改个人服务器（超详细） #公网映射 #Vmware虚拟网络编辑器
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· 零经验选手，Compose 一天开发一款小游戏！
· 一起来玩mcp_server_sqlite，让AI帮你做增删改查！！
点击右上角即可分享
微信分享提示