2-27.1 MySQL高级2

最新推荐文章于 2023-08-03 14:50:01 发布

Doub1eOne

最新推荐文章于 2023-08-03 14:50:01 发布

阅读量202

点赞数

分类专栏： Note 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/WilsonDone/article/details/127523869

版权

Note 专栏收录该内容

64 篇文章 0 订阅

订阅专栏

MySQL建表完整性

MySQL的建表完整性就是对存数据的约束

PS:建表约束还可以叫做 —》【数据完整性】

主键约束

特点：非空且唯一，它所表示的式一列中数据

#主键约束【保证这一列数据非空且唯一】
#  primary key
#创建一张表，课程编号，课程名字，课时
create table subject(	
	subjectID INT PRIMARY KEY,#此时这个课程编号是非空且唯一
  subjectName varchar(100),
	subjectHours INT
)charset = gbk;
#向表中插入数据
INSERT INTO subject(subjectID,subjectName,subjectHours)VALUES(1,'java',50);
#--> 1062 - Duplicate entry '1' for key 'PRIMARY' 说明当前主键列的值已经有一个1，不能再插入重复值
INSERT INTO subject(subjectID,subjectName,subjectHours)VALUES(1,'java',50);

#查看表数据
select * from subject;

唯一约束

#唯一约束 表示表中数据，不可重复，但是可以为null
#需要使用到一个关键字   unique

#创建一张表，课程编号，课程名字，课时
create table subject2(	
	subjectID INT PRIMARY KEY,#此时这个课程编号是非空且唯一
  subjectName varchar(100) UNIQUE,#这一列中存储数据 不可以重复，可以为null
	subjectHours INT
)charset = gbk;
#向表中插入数据
INSERT INTO subject2(subjectID,subjectName,subjectHours)VALUES(1,'java',50);
#--> 1062 - Duplicate entry 'java' for key 'subjectName' #说明这个列中已经存在相同课程名称
INSERT INTO subject2(subjectID,subjectName,subjectHours)VALUES(2,'C#',50);

#查看表数据
select * from subject2;

自动增长列

#自动增长列
#这个关键字多用修饰主键列，让主键列可以自动增长【PS:在忘记对主键存储值的时候，可以保证主键有值】
# 这个键列必须是一个 【整数类型】，默认是从1开始  每添加一条数据自动+1增长
#关键字 AUTO_INCREMENT

#创建一张表，课程编号，课程名字，课时
create table subject3(	
	subjectID INT PRIMARY KEY AUTO_INCREMENT,#此时这个课程编号是非空且唯一,数据可以自动+1增长
  subjectName varchar(100) UNIQUE,#这一列中存储数据 不可以重复，可以为null
	subjectHours INT 
)charset = gbk;
#向表中插入数据
INSERT INTO subject3(subjectName,subjectHours)VALUES('java',50);
#--> 1062 - Duplicate entry 'java' for key 'subjectName' #说明这个列中已经存在相同课程名称
INSERT INTO subject3(subjectName,subjectHours)VALUES('C#',50);

#指定 subjectID的值为一个固定数值，是否允许插入-->允许，不会受到自增长影响
INSERT INTO subject3(subjectID,subjectName,subjectHours)VALUES(5,'MySQL',50);

#在固定值后面在插入一条数据，但是不给给固定值 使用自增长
#在向固定值后续插入数据，会根据上一个ID值为起始自增+1
INSERT INTO subject3(subjectName,subjectHours)VALUES('JDBC',50);

#查看表数据
select * from subject3;

非空约束

#非空约束，此列必须有值  
#关键字 NOT NULL
#创建一张表，课程编号，课程名字，课时
create table subject4(	
	subjectID INT PRIMARY KEY AUTO_INCREMENT,#此时这个课程编号是非空且唯一,数据可以自动+1增长
  subjectName varchar(100) UNIQUE NOT NULL,#这一列中存储数据 不可以重复且不允许为null
	subjectHours INT 
)charset = gbk;
#ps：一个表中一般只有一个主键列，其他列也想使用主键列约束效果，就可以使用 UNIQUE NOT NULL
#--> 1048 - Column 'subjectName' cannot be null 说明这个列使用非空约束 ，不允许使用null
INSERT INTO subject4(subjectName,subjectHours)VALUES(null,50);

默认值

#默认值 允许给这个列使用一个 默认值，在没有传递值前提下有一个默认值出现在列中
# 关键字 DEFAULT
#创建一张表，课程编号，课程名字，课时
create table subject5(	
	subjectID INT PRIMARY KEY AUTO_INCREMENT,#此时这个课程编号是非空且唯一,数据可以自动+1增长
  subjectName varchar(100) UNIQUE NOT NULL,#这一列中存储数据 不可以重复且不允许为null
	subjectHours INT DEFAULT 20  #相当于在没有对列中粗才能数据的时候可以使用默认值填充
)charset = gbk;
#插入数据
INSERT INTO subject5(subjectName,subjectHours)VALUES('java',DEFAULT);
#插入数据 因为主键列自增长，课时列时有默认值，所以可以这样插入数据
INSERT INTO subject5(subjectName)VALUES('MySQL');
#插入数据,因为有列时有默认值的，所以可以不给值【使用的是默认】，也允许给值，如果给值了那么就不会在使用默认
INSERT INTO subject5(subjectName,subjectHours)VALUES('JDBC',50);
#查看表数据
select * from subject5;
#ps:这个DEFAULT关键字可以在建表使用编码集的时候使用 DEFAULT charset = gbk;

外键列

#此时需要两张表产生关联，会将一张表中非空且唯一的这个一列，作为在另外一张表中列存在，再另外一种表中表现形式就是 【外键列】

#创建一张表【专业表】
create table specialty(
	id INT PRIMARY KEY AUTO_INCREMENT,
	specialtyName varchar(20) UNIQUE NOT NULL
)
#此时需要使用专业表和课程表产生关联，需要将专业表中ID作为课程表中外键存在
#语法：
# constraint 引用名 foreign key(列名) references 被引用表名(列名)

#创建一张表，课程编号，课程名字，课时
create table subject6(	
	subjectID INT PRIMARY KEY AUTO_INCREMENT,#此时这个课程编号是非空且唯一,数据可以自动+1增长
  subjectName varchar(100) UNIQUE NOT NULL,#这一列中存储数据 不可以重复且不允许为null
	subjectHours INT DEFAULT 20,  #相当于在没有对列中粗才能数据的时候可以使用默认值填充
	
	#1.在表中新增一个列，这个列存是专业表中ID
	specialtyID INT NOT NULL,
	#2.添加外键
  CONSTRAINT fk_subject_specialty FOREIGN KEY(specialtyID) REFERENCES specialty(id)
)
#新增专业
INSERT INTO specialty(specialtyName)VALUES('计算机科学与技术');
#课程
INSERT INTO subject6(subjectName,specialtyID)VALUES('JDBC',1);
INSERT INTO subject6(subjectName,specialtyID)VALUES('Mysql',1);
#--> 1452 - Cannot add or update a child row: a foreign key constraint fails (`mydb1`.`subject6`, CONSTRAINT `fk_subject_specialty` FOREIGN KEY (`specialtyID`) REFERENCES `specialty` (`id`))
#因为有外键约束存在，所以传入值必须在原有表的主键列值必须存在
INSERT INTO subject6(subjectName,specialtyID)VALUES('服务业',10);

select * from specialty;
SELECT * FROM subject6;

多表联合查询语句

多表联合查询有以下几种方式

1.合并结果集【UNION ，UNION ALL】

2.连接查询【主推】：

2.1 内连接 [INNER] JOIN

2.2 外连接 OUTER JOIN

		2.2.1 左外连接【左连接】   LEFT  [OUTER]  JOIN

2.2.2 右外连接【右连接】 RIGHT [OUTER] JOIN

3.子查询【在一个查询语句中有另外一个查询语句】

请添加图片描述

合并结果集

注意：被合并的两个结果集：列数，列的类型必须是相同

UNION：可以将两个表查询结果集进行合并【取出重复记录】

     查询语句   UNION   查询语句
例如：
     select * from t1  UNION  select * from t2;
     #t1 表中  a1 b2 c3         t2 c3 d4 e5
     #结果 a1 b2 c3 d4 e5

UNION ALL：可以将两个表查询结果集进行合并【不去重】

  		查询语句   UNION ALL  查询语句
  例如：
     	select * from t1  UNION ALL select * from t2;
    	 #t1 表中  a1 b2 c3         t2 c3 d4 e5
     	 #结果 a1 b2 c3 c3 d4 e5

连接查询【推荐】

ps：多表连接查询的时候一定要避免**【笛卡尔积】**

笛卡尔积的产生：

#笛卡尔积产生会造成大量无用重复数据
#在多表查询的时候没有做任何条件限制
#产生了交集【表1中每一条数据都要和表2中每一条数据结合】

#查询emp,dept表
select * from emp,dept;
#此时查询这两种表的时候，出现了一个问题
/*
emp 表中是有15条数据的  dept表中是有4条数据，在没有添加任何条件的前提下，产生结果集是 15*4 = 60
 而这个60条数据中其实是有一些无用值出现例如
7369	SMITH	CLERK	7902	1980-12-17	800.00		20	10	ACCOUNTING	NEW YORK
7369	SMITH	CLERK	7902	1980-12-17	800.00		20	20	RESEARCH	DALLAS
7369	SMITH	CLERK	7902	1980-12-17	800.00		20	30	SALES	CHICAGO
7369	SMITH	CLERK	7902	1980-12-17	800.00		20	40	OPERATIONS	BOSTON
SMITH  是属于 部门20的员工 但是 部门10,20,40都出现在了查询结果，这样的结果就叫“笛卡尔积”
PS： 因为这个查询产生结果集效果和数学中“笛卡尔积” 产生的效果一直，所以我们就称之为“笛卡尔积”
*/

#避免笛卡尔积出现怎么办？
/*
1.这表是你开发的，就可以直接查找表中关联列，是否存在A表和B表之间存在一个相同列【外键约束】
2.如果这个表不是你开发， 只能去观察表【直接看表（代数据的） 或 查看表结果】
	直接查看表，就是找相关联的列      查看表结构 ，查看是否存在外键约束
*/

#去掉笛卡尔积，提供一个判断判断条件，这个条件满足这展示结果，不满足则展示
#这个条件判断列必须是多个表中都存在的列才可以 
#这两张表的关联列，depton 【emp表中存在】 depton【dept表中也存在】

select * from emp,dept where emp.deptno = dept.deptno;

#建议在使用多表查询的时候，可以使用别名
select * from emp AS e,dept AS d where e.deptno = d.deptno;

内连接

其实在MySQL中面SQL语句的写法其实就是【内连接】，但是它不是【SQL语句】的标准语法，可以理解为是MySQL中提供的的语法

SQL标准内连接语法

需要使用关键字  INNER JOIN ON
ps： "在MySQL中默认连接方式就是内连接，需要注意使用where作为条件约束，而是使用ON作为条件约束"
     "ON：里面写取出笛卡尔积"
     
#使用标准内连接语句查询员工属于哪个部门的所有信息
#标准SQL
SELECT * from emp INNER JOIN dept ON emp.deptno = dept.deptno;
#MySQL中因为默认多表查询是就是内连接，所以可以省略 INNER关键字
SELECT * from emp JOIN dept ON emp.deptno = dept.deptno;
/*
内连接特点：查询结果必须满足【ON后面的条件】
emp表中 depton这个列中数据有【10,20,30,40,50】  
dept表中 depton这个列数据有【10,20,30,40】
给出的条件是  emp.deptno = dept.deptno ,所以就过滤掉 50这条数据
*/

外连接（左连接和右连接）

左外连接（左连接）：查询出的结果如果不满足条件使用NULL代替，否则正常展示

#标准语法 LEFT OUTER JOIN ，在MySQL中是可以不写OUTER，所以和 LEFT JOIN效果是一样
select * from  emp LEFT JOIN dept ON  emp.deptno = dept.deptno;
/*
左连接：左连接是以左表为驱动表【即为主表】，然后连接查询右表，如果右表中有满足条件则展示，否者这个展示为null
说明： 其实emp【左表】但是emp表中有要一条数据是【部门编号是50】
			  dept【右表】，但是dept中是有没有50编号的
				所以最终展示结果
				就是在dept表查询出结果中有NULL值占位
*/

右外连接（右连接）：查询出结果满足则展示否则则展示出NULL

#标准语法 RIGHT OUTER JOIN ，在MySQL中是可以不写OUTER，所以和 RIGHT JOIN效果是一样
select * from  emp RIGHT JOIN dept ON  emp.deptno = dept.deptno;
/*
右连接：右表作为驱动表【即为主表】，然后连接查询左表，如果左表中没有满足条件，则展示NULL
说明：  其实dept表【右表（主表）】，在其中会有一条数据是【部门编号是40】
         emp是左表，其中只存在数据【10,20,30,50】 这几个部门编号
				 所以最终所展示说出来的结果就是  右表汇总40编号会正常展示
				 但是左表没有这个数据 就以 NULL进行展示
*/

子查询

ps：建议能使用连接查询就不写子查询，子查询效率低

什么是子查询？

一个select查询语句中嵌套另外一个select查询语句，这样会语句就是子查询

子查询出现的位置

1.出现在where的后面，或作为查询条件第一部分进行查询

PS：在where子句的后面使用子查询可以使用两个关键字**【any 和 all】**

2.出现在from后面，作为一个表存在进行查询

子查询的结果集

1.单行单列【多用于条件位置】 2. 单行多列【多用于在条件的位置】3.多行多列【多用于在条件的位置】

4.多行多列【多用于在表的位置】

子查询演示

#子查询 工资高于JONES的员工
#1.先查出JONES这个人，找到其工资
select sal from emp where ename = 'JONES';
#2.需要大于JONES工资的人，即大于第一步的查询结果集，所有就使用到了子查询
SELECT * from emp where sal >(select sal from emp where ename = 'JONES');

#工资高于30部门所有人员工信息
SELECT * from emp where sal >(select MAX(sal) from emp where deptno = 30);
#在查询在where语句之后可以使用 all【全部】 和 any【其中的一个】

#所有的信息都要满足all会获取所有信息，where子句需要判断这里所有条件
SELECT * from emp where sal > ALL(select MAX(sal) from emp where deptno = 30);


#只要满足一个条件就发送where条件判断
SELECT * from emp where sal > Any(select MAX(sal) from emp where deptno = 30);

#查询员工编号 7788的员工名称，工资，部门名称和地址
#1.先查出7788 员工名称和工资
select  ename ,sal from  emp where empno = 7788;
#2.查出部门名称和地址【MySQL是内连接】
select ename,sal,dname,loc from emp ,dept where empno= 7788 AND emp.deptno= dept.deptno

#子查询
SELECT e.ename,e.sal,d.dname,d.loc 
from emp as e,(select dname,loc,deptno from dept) as d
where e.empno = 7788 AND e.deptno = d.deptno

事务

模拟场景：生活中转账【A账户（10000）中进行转账操作，每次相应减少1000】，【B账户得到1000，相应的增加1000】

#正产转账
#从A账户【id为1】转换出1000元
update account set money = money-1000 where id = 1; #转账
#B账户得到1000元
update account set money = money+1000 where id = 2; #收款

select * from account;

#异常转账
#从A账户【id为1】转换出1000元
update account set money = money-1000 where id = 1; #转账
/*
模拟一个场景
A转出金额之后出现了异常【断电、出错、系统异常】
当A账户执行完毕之后，出现了异常，A账户出现减钱，B账户并没有的到加加钱
这样一来这个执行逻辑就非常的不合理
因为每一条SQL语句都是一个独立操作，一个操作执行完毕之后对数据库是永久影响
*/

#B账户得到1000元
update account set money = money+1000 where id = 2; #收款

select * from account;

数据库为了解决出现这个样问题，提供一个策略这个策略就是【事务】

事务：是一个原子性操作【要就都成功，要么就都失败】，是一个最小的执行单元，可以由一个或多个SQL语句组成，在同一个事务中，所有SQL语句都成功执行，那么整个事务就成功，只要有一个SQL语句执行失败，整个事务就失败，就可以通过事务来应对数据库中数据操作。

事务的边界

起始位置：连接数据库，执行一条DML语句，上一个事务就结束，又输出一条DML语句，即事务的开始

结束位置：

1.提交【commit】

1.1 显示提交，相当于是手动调用commit

1.2 隐示提交，一条创建、删除语句【一条语句的完整性】，正常退出

2.回滚【rollback】

2.1 显示回滚，相当于是手动调用rollback

2.2 隐示回滚：非正常退出【断电，宕机】，执行创建或删除语句的时候，执行和失败了，会为就这个无效的语句执行回滚，回顾到之前的状态

事务的原理

数据库会为每一个客户端都维护一个空间【独立的缓冲区（回滚段）】，一个事务中所有的增删改语句都执行在这个缓冲空间中，只有当事务中所有SQL语句都正常结束（正常执行），此时会执行【commit】，一旦这里SQL语句出现了异常执行，整个事务就会对这个操作进行【rollback】

PS：个人建议是手动开启事务，进行对数据操作的管理

手动开启事务

#手动开启事务
START  TRANSACTION;
#从A账户【id为1】转换出1000元
update account set money = money-1000 where id = 1; #转账
/*
模拟一个场景
A转出金额之后出现了异常【断电、出错、系统异常】
当A账户执行完毕之后，出现了异常，A账户出现减钱，B账户并没有的到加加钱
这样一来这个执行逻辑就非常的不合理
因为每一条SQL语句都是一个独立操作，一个操作执行完毕之后对数据库是永久影响
*/

#B账户得到1000元
update account set money = money+1000 where id = 2; #收款

#事务成功之后执行提交
COMMIT;
#事务出现了错误，异常等等原因，执行回滚
ROLLBACK;


select * from account;

事务的四大特征(ACID)

1.原子性(Atomicity)

表示一个事务内所有的操作都是一个整体，要么都成功，要么都失败

2.一致性(Consistency)

表示一个事务内有一个从操作失败，所有的更改过的数据都必须回滚到之前的状态

3.隔离性（isolation）

事务查看数据操作时数据所处的喘不过太，要么是另一个并发事务修改它之前的状态，要么是另一个事务修改它之后的状态，事务不会查看中间状态数据

4.持久性(Durability）

持久性是事务完成之后，它对系统影响是永久性的

权限管理

1.创建账户

create user  用户名  identified by 密码;
例如：
create user 'zhangsan' identified by '123';

2.授权

grant all on 数据库.表 to 用户名
例如：
grant all on mydb1.* to 'zhangsan';
PS: *相当于是所有表的权限

3.撤销权限

revoke all on 数据库.表 from 用户名
例如：
revoke all on mydb1.* to 'zhangsan';
ps：撤销权限之后只有账户再次重新连接数据库客户端才会生效

4.删除账户

drop user 用户名
例如：
drop user 'zhangsan';

视图

视图其实就是一个虚拟表，从一个或多个表中查询出来数据的代表，作用和真实的表时一样的，包含列和行。视图是用户可以用select于具有进行查询，而且还算可以使用insert，update，delete进行修改，视图可以作为一个方便操作表存在，并保证原有数据库中表的安全

视图优缺点

优点：

1.安全性，用户只能查询或修改他们能看到数据

2.简单化，所见即所得

3.逻辑性是独立，可以直接屏蔽原始的【真表】

缺点：

1.性能差，简单的查询也会被的复杂

2.修改不方便，特别是复杂的修改视图基本上完不成

创建视图

语法：
	 create view 视图名字 as 查询表得到信息
例如：
	 create view cv_emp AS select * from emp;
ps：此时这个视图相当于是将emp表中数据进行了一个完整拷贝【表结构和数据】

查看视图中数据

例如：
	select sal from cv_emp where deptno = 30;

修改视图

相当于是对视图的重新赋值
方式1：如果存在则修改，如果不存在则重复赋值
  create or replace view cv_dept as select deptno from dept;
方式2：直接对已经存在的视图进行修改
  alter view cv_dept as select * from dept;

删除视图

drop view 视图名
例如：
   drop view cv_emp;

视图的注意事项:

1.视图不会独立存储数据，原表发生改变，视图也会跟着改变，视图没有任何优化查询

2.如果视图包含以下结构中任意一种，则视图不可以更新

2.1 聚合函数结果

2.2 去重之后结果

2.3 分组之后结果

2.4 分组之后并进行过滤之后结果

2.5 union、union all 合并之后结果集不能在修改了

扩展

MySQL的数据备份

1.使用数据库操作工具，生成【.sql】文件即可备份【建议选择（结构和数据）】这样的表结构和数据都会进行备份

2.通过命令行备份

MySQL自身的数据库维护：

通过cmd命令进入dos窗口：

1.导出：mysqldump -u账户 -p密码数据库名称>脚本文件存储地址

mysqldump -uroot -padmin jdbcdemo> C:/shop_bak.sql

2.导入：mysql -u账户 -p密码数据库名称< 脚本文件存储地址

mysql -uroot -padmin jdbcdemo< C:/shop_bak.sql

最后一句话完事先备份，保证没问题！！！！！！！！！【谨记】

查看SQL语句执行计划

EXPLAIN + sql 
查询出当前SQL语句的执行计算
EXPLAIN select * from emp;

count（*）和count（1）和count（列名）

Doub1eOne

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
2-27.1 MySQL高级2

一个select查询语句中嵌套另外一个select查询语句，这样会语句就是子查询。
复制链接

扫一扫

专栏目录