Java基础（二十四）：MySQL

一万年可长否

于 2023-09-14 21:40:10 发布

阅读量355

点赞数

分类专栏： Java基础文章标签： java mysql 开发语言

本文链接：https://blog.csdn.net/shendaiyan/article/details/132835623

版权

Java基础专栏收录该内容

26 篇文章 8 订阅

订阅专栏

文章目录

一、数据库（创建、显示、删除、备份、恢复）
二、MySQL常用数据类型
三、表结构的操作
四、表的增删改查
五、函数
六、多表查询
七、约束
八、自增长
八、索引
九、事务
九、MySQL表类型和存储引擎
十、视图
十一、MySQL管理

一、数据库（创建、显示、删除、备份、恢复）

# 创建数据库，指定字符集（utf-8）和校对规则（区分大小写）
create database `myDB` 
character set utf8 
collate utf8_bin

# 显示所有数据库
show databases
# 显示数据库创建语句
show create database myDB
# 删除数据库
drop database myDB


# 1.连接Mysql数据库的指令：
# mysql -h 主机IP -P 端口 -u 用户名 -p密码

# 2.备份数据库：mysqldump指令(要在DOS下执行)
# mysqldump -u 用户名 -p -B 数据库1 数据库2 > 文件名.sql
# 例：
# mysqldump -u root -p -B myDB > d:\\bak.sql

# 3.恢复数据库
# (1)方法一：(要在MySQL命令行执行)
# 例：
# mysql -u root -p
# source d:\\bak.sql
# (2)方法二：将bak.sql的代码在navicat中执行

# 4.备份和恢复数据库的表
# mysqldump -u 用户名 -p -B 数据库 表1 表2  > 文件名.sql
# 例：
# mysqldump -u root -p -B myDB user > d:\\bak.sql
# mysql -u root -p
# source d:\\bak.sql

二、MySQL常用数据类型

在这里插入图片描述

2.1 数值型（整数）

在这里插入图片描述

2.2 数值型（二进制bit）

在这里插入图片描述

2.3 数值型（小数）

在这里插入图片描述

2.4 字符型

注意：varchar实际创建不能用到65535,，因为是字节。例如：utf8最大能用(65535-3)/3=21844【utf8一个字符用3个字节】
在这里插入图片描述
字符串使用细节：

char(4)、varchar(4)的4代表4个字符而不是字节，不管中英文都是存放4个；
char(4)、varchar(4)当插入‘aa’，char占4个字符，varchar按照实际占用的空间的大小分配；
char用于数据定长的情况，例如密码、手机号；varchar用于数据不确定长度；因为查询速度char>varchar
存放文本时可以利用Text数据类型替代varchar，Text不能有默认值，大小[0~2^16]个字节；如果varchar不够用，可以考虑使用 mediumtext [0~2^24] 或 longtext [0~2^32]，简单点使用 text 。

2.5 日期类型

create table t(
	birthday date,
	job_time datetime,
	login_time timestamp 
		not null 
		default current_timestamp 
		on update current_timestamp);-- 表示时间戳非空，当前时间，自动更新
		
insert into t(birthday,job_time) value('2023-09-12','2023-09-12 22:54:15');
select * from t;
-- 如果更新表的某条记录，timestamp会自动更新为当前时间

三、表结构的操作

# 一、表结构的操作：

# 1.建表
create table `emp`(
			id int,
			name varchar(32),
			sex char(1),
			birthday date,
			entry_date datetime,
			job varchar(32),
			salary double,
			resume text)charset utf8 collate utf8_bin engine innodb;
	
# 2.在resume后面增加一列image
alter table `emp` 
	add image varchar(32) not null default ''
	after resume
# 3.显示表结构
desc `emp`
# 4.修改job列
alter table `emp`
	modify job varchar(60) not null default ''
# 5.删除sex列
alter table `emp`
	drop sex
# 6.修改表名为employee
rename table `emp` to `employee`
# 7.修改字符集为utf8
alter table `employee` character utf8
# 8.修改列名name为user_name
alter table `employee`
	change `name` `user_name` varchar(64) not null default ''
desc `employee`

四、表的增删改查

4.1 插入INSERT

insert 语句的细节

插入的数据应与字段的数据类型相同
数据的长度应在列的规定范内
在values中列出的数据位置必须与被加入的列的排列位置相对应。
字符和日期型数据应包含在单引号中
列可以插入空值 [ 前提是该字段允许为空 ] ，INSERT INTO TABLE VALUE(NULL)
INSERT INTO tab_name (列名…) VLUES (),(),() 形式添加多条记录
如果是给表中的所有字段添加数据，可以不写前面的字段名称
默认值的使用，当不给某个字段值时，如果有默认值就会添加，否则报错

4.2 修改UPDATE

使用细节:

UPDATE语法可以用新值更新原有表行中的各列
SET子句指示要修改哪些列和要给予哪些值
WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行(记录)
如果需要修改多个字段，可以通过 set 字段1=值1,字段2=值2…

4.3 删除DELETE

如果不使用where子句，将删除表中所有数据
Delete语句不能删除某一列的值 (可使用update 设为 null 或者 ‘’ )
使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句

演示insert update delete：

# 二、表的增读改删CRUD：
create table `goods`(
			id int,
			goods_name varchar(10),
			price double);

# 1. 插入
insert into `goods`(id,goods_name,price)
	values(10,'华为',8000);
# 插入多条数据
insert into `goods`
	values(20,'小米',2000),(60,'苹果',1000);
# 未指定列默认
insert into `goods`(id,goods_name)
	values(30,'三星');
	
	
	
# 2. 修改
# 修改所有数据
update `goods`
	set price = 5000
# 修改指定数据
update `goods`
	set price = 7000,id = 40 where goods_name = '华为'
	
	
	
# 3. 删除
# 删除指定
delete from `goods`
	where goods_name = '华为'
# 删除所有
delete from `goods`

4.4 (单表)查询SELETE

在这里插入图片描述

create table student(
						id int not null default 1,
						name varchar(20) not null default '',
						chinese float not null default 0.0,
						english float not null default 0.0,
						math float not null default 0.0);
insert into student 
	values(1,'小李',89,78,90),(2,'小黑',67,98,56),
	(3,'小白',87,78,77),(4,'小红',88,98,90),
	(5,'小绿',82,84,67),(6,'小紫',89,78,90),
	(7,'小橙',89,78,90);

delete from student
# --------1）基本查询--------
# 查询所有信息	
select * from student;
# 查询名字和英语成绩
select `name`,english from student;
# 去重（每个字段都相同才能去重）
select distinct english from student;

# --------2）表达式--------
# 统计总分(指定别名)
select `name`,(chinese+english+math) as total from student;

# --------3）运算符--------
# 1.and：查询math>60且id>4的学生成绩
select * from student where  math > 80 and id > 4
# 2.like：查询总分大于200，且数学小于语文，且姓小的同学
# % 表示0到多个任意字符，_ 表示单个任意字符
# '_ _ 刘 %'表示第三个字为'刘'的人
select * from student 
	where (chinese+english+math) > 200 and math < chinese and `name` like '小%'
# 3.between and闭区间：查询数学在80-90之间
select * from student where math between 80 and 90;
# 4.in或者or：查询数学成绩为89 90 91
select * from student where math in (89,90,91)
# 5. all和any将在下面 多表查询 的 子查询 中演示

五、函数

5.1 排序、统计和分组函数

# --------4）排序order by--------
# 升序asc[默认]
select * from student order by math;
# 降序desc
select * from student order by math desc;


# --------5）合计\统计函数 count、sum、avg、max、min--------

# 问：count(*)与count(列)的区别？
# count(*) 返回满足条件的记录的行数，不排除null
# count(列) 返回满足条件的某列有多少个，排除null

# 统计数学>80的人数
select count(*) from student where math > 80;
# 统计各科的总成绩
select sum(chinese),sum(english),sum(math) from student;
# 统计语文的平均分
select sum(chinese)/count(*) from student;
select avg(chinese) from student;
# 总分最高分
select max(chinese+english+math) from student;

# 练习1：显示雇员总数以及 没有获得补助(comm为空) 的雇员数
select count(*),count(if(comm is null,comm,null)) from emp;
select count(*),count(*)-count(comm) from emp;

# --------6）分组group by和过滤having--------
# 每个部门平均工资和最高工资
select avg(sal),max(sal),deptno from emp
	group by deptno;
# 每个部门每种岗位平均工资和最高工资
select avg(sal),max(sal),deptno,job from emp
	group by deptno,job;
# 平均工资低于2000的部门号和它的平均工资
select avg(sal) as avg_sal,deptno from emp
	group by deptno having avg_sal < 2000;

5.2 字符串函数

# --------7）字符串相关函数--------
# 1.charset(str)：返回字符集
select charset(ename) from emp;
# 2.concat(str1,...,strn)：连接字符串，将多个列拼接成一个
select concat(ename,' job is ',job) from emp;
# 3.instr(string,substring)：返回substring在string出现的位置，
#   没有返回0   dual亚元表，系统表，用做测试
select instr('helloworld','world') from dual
# 4.ucase(str)：大写
# 5.lcase(str)：小写
# 6.left(str,length)：从str左边取出length长度个字符
# 7.right(str,length)：从str右边取出length长度个字符
# 8.length(str)：str长度
# 9.replace(str,search_str,replace_str)：在str中用replace_str替换search_str
select replace(job,'manager','经理') from emp;
# 10.strcmp(str1,str2)：逐个字符比较大小
# 11.substring(str,position,length)：截取字符串
# 12.ltrim() rtrim() trim()：去掉左、右、左右空格 

# 练习：首字母小写方式显示员工姓名
select concat(lcase(substring(ename,1,1)),substring(ename,2)) from emp;
select concat(lcase(left(ename,1)),substring(ename,2)) from emp;

5.3 数学函数

# --------8）数学函数--------
# 1.abs(num)：绝对值
# 2.ceiling(num)：向上取整
# 3.floor(num)：向下取整
# 4.bin(num)：十进制转二进制
# 5.hex()：转十六进制
# 6.conv(num,from_base,to_base)：进制转换
# 例：十进制8转成二进制输出
select conv(8,10,2) from dual;
# 7.format(num,n)：保留n位小数
select format(785.1235,2) from dual;
# 8.least(num1,num2,...,numn)：求最小值
# 9.mod(num,denominator)：求余数
select mod(10,3) from dual;
# 10.rand([seed])：返回[0,1]随机数,
#     若加上seed(随便写)，表示固定随机数[之后产生的随机数不变]
select rand() from dual; # 每次返回不同随机数
select rand(3) from dual; # 每次返回相同随机数

5.4 时间日期

# --------9）日期时间--------

# 1.current_date()：当前日期
# 2.current_time() / now()：当前时间
# 3.current_timestamp()：当前时间戳

# 4.date(datetime)：获取时间datetime的日期
# 例：显示发布日期，但是不显示时间
select id,content,date(send_time) from mes;

# 5.date_add(date,interval num type)：在date基础上加上日期或时间
#    细节：date可以为日期 时间 时间戳
#          type类型：year,minute,secend,day,hour等
# 例：查询10分钟内发布的新闻
select * from mes
	where date_add(send_time,interval 10 minute) >= now();
	
# 6.date_sub(date,interval num type)：date减去一个时间
# 例：查询10分钟内发布的新闻
select * from mes
	where date_add(now(),interval 10 minute) <= send_time;
	
# 7.datediff(date1,date2)：日期差（天）
#    细节：date1和date2也可以是时间差
# 例：计算2011-11-11与1990-1-1相差天数
select datediff('2011-11-11','1990-1-1') from dual;
# 例：计算12:12:12与10:10:10的时间差
select datediff('12:12:12','10:10:10') from dual;

# 练习：如果你能活到80岁，求出你还能活的天数
select datediff(date_add('2000-01-01',interval 80 year),now()) from dual;

# 8.year() month() day() 
# 9.unix_timestamp()：返回1970-1-1到现在的秒数
# 10.from_unixtime(,)：把unix秒数转成指定格式日期
select from_unixtime(1618483484,'%Y-%m-%d') from dual;
select from_unixtime(1618483484,'%Y-%m-%d %H:%i:%s') from dual;

5.5 加密和系统函数

# --------10）加密和系统函数--------
# 1.user()：查询用户以及IP地址
select user() from dual;
# 2.database()：数据库名称
select database();
# 3.MD5(str)：为字符串加密
select MD5('1234') from dual;
# 4.password()：加密函数
select PASSWORD('hello') from dual;

5.6 流程控制

# --------11）流程控制函数--------
# 1.if(expr1,expr2,expr3)：如果expr1为true返回expr2 否则返回expr3
# 2.ifnull(expr1,expr2)：如果expr1不为null返回expr1 否则返回expr2
# 3.select case 
#					when expr1 then expr2
#					when expr3 then expr4
#         else expr4 end
# 练习：
#  查询emp表，若comm为null，则显示0.0
select ename,if(comm is null,0.0,comm) from emp;
select ename,if(comm,0.0) from emp;
#  如果job是 clerk显示职员 manager显示经理 salesman显示销售人员 其余正常
select ename,(select case
							when job='clerk' then '职员'
							when job='manager' then '经理'
							when job='salesman' then '销售人员'
							else job end) from emp;

5.7 分页查询

# --------12）分页查询--------
# 语法：limit 每页第一条数据的编号（从0开始）,每页记录数
# 公式：limit 每页记录数*(第几页-1)，每页记录数
# 例子：按照id升序取出，每页显示3条记录，显示出第1 3 5页
# 第1页
select * from emp
	order by empno
	limit 0,3
# 第3页
select * from emp
	order by empno
	limit 6,3
# 第5页
select * from emp
	order by empno
	limit 12,3

# 综合练习：各个部门的平均工资，并且是大于1000的，按照平均工资由高到低排序，显示出前两行记录
select deptno,avg(sal) as avg_sal from emp
	group by deptno
	having avg_sal > 1000
	order by avg_sal desc
	limit 0,2

六、多表查询

6.1 笛卡尔集

在这里插入图片描述

6.2 自连接

自连接指的是同一张表的连接查询，特点：把一张表当做多张表，需要给表取别名，列名不确定也可以指定列的别名。
在这里插入图片描述

select A.ename as '职员',B.ename as '领导' from emp A,emp B
	where A.mgr = B.empno;

6.3 （单行/多行）子查询 / 嵌套查询

# ----------3.子查询----------

# 1）单行子查询
# 查询和smith同一部门的员工
select * from emp
	where deptno = (select deptno from emp where ename = 'smith');
	
# 2）多行子查询
# 查询和部门10的工作相同的雇员的名字、岗位、工资、部门号，但是不含10号部门自己的员工
# 第一步：先找10号部门的工作有哪些
# 第二步：把上步当做子查询
select ename,job,sal,deptno from emp
	where job in (select distinct job from emp where deptno = 10) and deptno <> 10
	
# 3）把子查询当做临时表
# 查询ecshop中各个类别中价格最高的商品
# 第一步:建立临时表
select cat_id,max(shop_price) as max_price from ecs_goods
					group by cat_id;
# 第二步:把临时表合在一起
select goos_id,ecs_goods.cat_id,goods_name,shop_price
				from (
					select cat_id,max(shop_price) as max_price from ecs_goods
					group by cat_id
				) temp,ecs_goods
				where temp.cat_id = ecs_goods.cat_id
				and temp.max_price = ecs_goods.shop_price;
				
# 4）all的使用	
# 显示工资比部门30号的所有员工的工资都高的员工
select * from emp
	where sal > all(select sal from emp where deptno = 30)
select * from emp
	where sal > (select max(sal) from emp where deptno = 30)

# 5）any的使用
# 显示工资比部门30号的某个员工的工资都高的员工
select * from emp
	where sal > any(select sal from emp where deptno = 30)
select * from emp
	where sal > (select min(sal) from emp where deptno = 30)
	
	
# 5）多列子查询
# 查询和smith部门和工作都相同的人
select * from emp
	where (deptno,job)=(select deptno,job from emp where ename ='smith') and ename != 'smith';

6.4 表复制和去重

# ----------4.表复制和去重----------

create table my_table01(
	id int,
	`name` varchar(32),
	sal double,
	job varchar(32),
	deptno int);

desc my_table01
select * from my_table01

# 1）把emp标的记录复制到my_table01
insert into my_table01(id,`name`,sal,job,deptno)
	select empno,ename,sal,job,deptno from emp;
# 2)自我复制
insert into my_table01
	select * from my_table01;

# 练习：如何去重？
# 第一步 创建有重复数据的表
create table my_table02 like emp;
insert into my_table02
	select * from emp;
insert into my_table02
	select * from emp;
# 第二步 创建一张临时表my_temp存储不重复的数据
create table my_temp like my_table02;
insert into my_temp
	select distinct * from my_table02;
# 第三步 清空表my_table02并复制临时表my_temp
delete from my_table02;
insert into my_table02
	select * from emp;
# 第四步 删除临时表my_temp
drop table my_temp;

6.5 集合查询

# ----------5.合并查询----------
# 1)union all：合并，不去重
select ename,sal,job from emp where sal>2500;
union all
select ename,sal,job from emp where job='manager';
# 2)union：合并，自动去重
select ename,sal,job from emp where sal>2500;
union
select ename,sal,job from emp where job='manager';

# 此外还包括：intersect 交、except 差

6.6 外连接

# ----------6.外连接----------
# 自然连接：去掉重复的属性
# 1）左外连接：左侧的表完全显示，即使没有匹配到右边的表
# 2）右外连接：右侧的表完全显示，即使没有匹配到左边的表

# 左外连接:显示所有人姓名和成绩，没有成绩也要显示成绩为null
select ename,score from stu left join exam on stu.id = exam.id;
# 右外连接:显示所有人姓名和成绩，没有名字也要显示名字为null
select ename,score from stu right join exam on stu.id = exam.id;

# 练习：列出部门名称和其员工的名字和工作，没有员工的部门也要显示.
select dname,ename,job from dept left join emp 
		on dept.depton = emp.depton
select dname,ename,job from emp right join dept 
		on dept.depton = emp.depton
		
# 小结：实际开发中很少使用外连接

七、约束

约束用于确保数据库的数据满足特定的商业规则。在mysql中，约束包括：not null（非空）、unique（唯一）、primary key（主键）、foreign key（外键）、check五种。

#     ----------三、约束----------

# ----------1.主键----------

-- 主键使用的细节讨论

-- 1）primary key不能重复而且不能为 null。

-- 2）一张表最多只能有一个主键, 但可以是复合主键(比如 id+name)
CREATE TABLE t18
	(id INT PRIMARY KEY, -- 表示id列是主键 
	`name` VARCHAR(32), PRIMARY KEY -- 错误的
	email VARCHAR(32));
-- 演示复合主键 (id 和 name 做成复合主键)
CREATE TABLE t18
	(id INT , 
	`name` VARCHAR(32), 
	email VARCHAR(32),
	PRIMARY KEY (id, `name`) -- 这里就是复合主键：id和name都不重复
	);
INSERT INTO t18
	VALUES(1, 'tom', 'tom@sohu.com');  -- ok
INSERT INTO t18
	VALUES(1, 'jack', 'jack@sohu.com');  -- ok
INSERT INTO t18
	VALUES(1, 'tom', 'xx@sohu.com'); -- no
	
-- 3）主键的指定方式 有两种 
-- 第一种：直接在字段名后指定：字段名  primakry key
-- 第二种：在表定义最后写 primary key(列名); 



# ----------2.unique和not null----------
-- unqiue使用细节
-- 1. 如果没有指定 not null , 则 unique 字段可以有多个null
-- 如果一个列(字段)， 是 unique not null 使用效果类似 primary key
-- 2. 一张表可以有多个unique字段


# ----------3.外键----------
# 1.外键指向的表的字段，要求是primary key 或者是unique
# 2.表的类型是innodb,这样的表才支持外键
# 3.外键字段的类型要和主键字段的类型一致(长度可以不同)
# 4.外键字段的值，必须在主键字段中出现过，或者为null [前提是外键字段允许为null]
# 5.一旦建立主外键的关系，数据不能随意删除了.

create table my_class (
	id int primary key , -- 班级编号
	`name` varchar(32)not null default '');
 
-- 创建 从表 my_stu
create table my_stu (
	id int primary key , -- 学生编号
	`name` varchar(32)not null default '',
	class_id int , -- 学生所在班级的编号
	-- 下面指定外键关系
	foreign key (class_id) references my_class(id))


# ----------4.check----------
-- 演示check的使用
-- mysql5.7目前还不支持check ,只做语法校验，但不会生效
-- 了解 
-- 学习 oracle, sql server, 这两个数据库是真的生效.
 
-- 测试
CREATE TABLE t23 (
	id INT PRIMARY KEY,
	`name` VARCHAR(32) ,
	sex VARCHAR(6) CHECK (sex IN('man','woman')),
	sal DOUBLE CHECK ( sal > 1000 AND sal < 2000)
	);

八、自增长

使用自增长的方式：
在这里插入图片描述
自增长的使用细节：

在这里插入图片描述

	-- 演示自增长的使用
-- 创建表
CREATE TABLE t24
	(id INT PRIMARY KEY AUTO_INCREMENT,
	 email VARCHAR(32)NOT NULL DEFAULT '',
	 `name` VARCHAR(32)NOT NULL DEFAULT ''); 
DESC t24
-- 测试自增长的使用
INSERT INTO t24
	VALUES(NULL, 'tom@qq.com', 'tom');
 
INSERT INTO t24
	(email, `name`) VALUES('hsp@sohu.com', 'hsp');
 
SELECT * FROM t24;
 
-- 修改默认的自增长开始值
ALTER TABLE t25 AUTO_INCREMENT = 100
CREATE TABLE t25
	(id INT PRIMARY KEY AUTO_INCREMENT,
	 email VARCHAR(32)NOT NULL DEFAULT '',
	 `name` VARCHAR(32)NOT NULL DEFAULT ''); 
INSERT INTO t25
	VALUES(NULL, 'mary@qq.com', 'mary');
INSERT INTO t25
	VALUES(666, 'hsp@qq.com', 'hsp');
SELECT * FROM t25;

八、索引

索引的目的是加快查询速度，但是也占用一部分空间。

索引的类型：

主键索引 primary key
唯一索引 unique
普通索引 index
全文索引 fulltext[适用MyISAM]
一般开发不使用mysql自带的全文索引，Solr和ES用的多

在这里插入图片描述


# --------一、创建索引--------
# 1.查询是否有索引
show indexes from emp;
# 2.添加索引
# 1）添加唯一索引：
# 方式一：创建表的时候在列后添加unique
# 方式二：create unique index 索引名 on 表名(列);
create unique index id_index on emp(id);

# 2）添加普通索引：
# 方式一：create index 索引名 on 表名(列);
create index id_index on emp(id);
# 方式二：alter table 表名 add index 索引名 (列);
alter table emp add index id_index (id);

# 3）添加主键索引：
# 方式一：创建表的时候在列后添加primary key
# 方式二：alter table 表名 add primary key(列);
alter table emp add primary key(id);

# --------二、删除索引--------
drop index id_index on emp;

# 删除主键索引
alter table emp drop primary key;

# --------三、修改索引 = 删除索引 + 添加索引--------


# --------四、查询索引--------
show index from emp;
show indexes from emp;
show keys from emp;
desc emp;

九、事务

9.1 事务的概念以及重要操作

在这里插入图片描述

解释回退事务与提交事务：

在这里插入图片描述

事物操作示意图：

在这里插入图片描述

代码演示：

-- 事务的一个重要的概念和具体操作
-- 看一个图[看示意图]
-- 演示
-- 1. 创建一张测试表
create table t27
	( id int,
	  `name` varchar(32));
-- 2. 开始事务 
start transaction
-- 3. 设置保存点
savepoint a
-- 执行dml 操作
insert into t27 values(100, 'tom');
 
savepoint b
-- 执行dml操作
insert into t27 values(200, 'jack');
 
-- 回退到 b
rollback to b
-- 继续回退 a
rollback to a
-- 如果这样, 表示直接回退到事务开始的状态.
rollback  
-- 事务提交后就不能回退，其他连接就可以访问了
commit

事物的细节：

在这里插入图片描述

9.2 事务的隔离级别

在这里插入图片描述

在这里插入图片描述
关于隔离级别的解读：【重要】

隔离级别“读未提交”：当为设置此级别时，一个事物对表的修改还没提交，但是另一个事物可以读到它已经修改的数据，就会产生读脏。
隔离级别“读已提交”：当为设置此级别时，只有事务修改提交后，另一事物才会看到修改的数据，就不会产生读脏，当仍存在不可重复读和幻读。
隔离级别“可重复读”：当为设置此级别时，即使提交修改后，另一个事物也看不到修改后的数据，从而解决不可重复读和幻读。
隔离级别“可串行化”：采用加锁的方法，当一个事务对表操作时，另一个事物无法操作该表，从而不会有脏读、不可重复读、幻读。

在这里插入图片描述

# ---------事务的隔离级别----------

-- 演示mysql的事务隔离级别
 
-- 1. 开了两个mysql的控制台
-- 2. 查看当前mysql的隔离级别
select @@tx_isolation;
 
-- 当前为隔离级别为 可重复读
-- mysql> SELECT @@tx_isolation;
-- +-----------------+
-- | @@tx_isolation  |
-- +-----------------+
-- | REPEATABLE-READ |
-- +-----------------+
 
-- 3.把其中一个控制台的隔离级别设置 Read uncommitted
-- 当前为隔离级别为 读未提交
set session transaction isolation level read uncommitted
 
-- 4. 创建表
create `account`(
	id int,
	`name` varchar(32),
	money int);
	
 
-- 1. 查看当前会话隔离级别 
select @@tx_isolation
-- 2. 查看系统当前隔离级别
select @@global.tx_isolation
-- 3. 设置当前会话隔离级别【读未提交、读已提交、可重复读、可串行化】
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
-- 4. 设置系统当前隔离级别
set global transaction isolation level 你设置的级别
-- 5. mysql默认隔离级别为可重复读
--    要修改的话，在my.ini配置文件最后加上transaction-isolation = 你设置的级别
--    一般不修改

9.3 事物的ACID特性

在这里插入图片描述

九、MySQL表类型和存储引擎

在这里插入图片描述

演示myisam、innodb、memory

在这里插入图片描述

--     表类型和存储引擎

-- 一、产看所有存储引擎
show engines


-- 二、演示innodb存储引擎
-- 1.支持事务 2.支持外键 3.支持行级锁


-- 三、演示myisam存储引擎
create table emp(
	id int,
	`name` varchar(32))engine myisam;
-- 1.添加速度快 2.不支持外键和事务 3.支持表级锁

start transaction;
savepoint a;
insert into emp values(1,'jack');
rollback to a; # 回滚失败，因为不支持事务


-- 四、演示memory存储引擎
-- 1.数据存储在内存 2.执行速度快(没有IO读写) 3.默认支持索引(hash)
create table emp(
	id int,
	`name` varchar(32))engine memory;

insert into emp values(1,'jack'),(2,'jack'),(3,'jack');
-- 因为数据存储在内存中，重启数据库会丢失数据，但是表的结构还在


-- 五、修改存储引擎
alter table emp engine = innodb

如何选择存储引擎?

如果你的应用不需要事务，处理的只是基本的CRUD操作，那么MyISAM是不二选择，速度快；
如果需要支持事务，选择InnoDB；
Memory 存储引擎就是将数据存储在内存中，由于没有磁盘l/0的等待，速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。(经典用法用户的在线状态())

十、视图

视图的理解：

视图是根据基表(可以是多个基表)来创建的，视图是虚拟的表；
视图也有列，数据来自基表；
通过视图可以修改基表的数据；
基表的改变，也会影响到视图的数据；

视图的使用

-- -------------视图的使用------------

-- 创建一个视图emp_view01，只能查询emp表的(empno、ename, job 和 deptno ) 信息
 
-- 1.创建视图
CREATE VIEW emp_view01
	AS
	SELECT empno, ename, job, deptno FROM emp; 
 
-- 2.查看视图
DESC emp_view01
 
SELECT * FROM emp_view01;
SELECT empno, job  FROM emp_view01;
 
-- 3.查看创建视图的指令
SHOW CREATE VIEW emp_view01
-- 4.删除视图
DROP VIEW emp_view01;
 
 
 
 
 
-- 视图的细节
 
-- 1. 创建视图后，到数据库去看，对应视图只有一个视图结构文件(形式: 视图名.frm) 
-- 2. 视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete ]
 
-- 修改视图 会影响到基表
 
UPDATE emp_view01 
	SET job = 'MANAGER' 
	WHERE empno = 7369
	
SELECT * FROM emp; -- 查询基表
 
 
SELECT * FROM emp_view01
 
-- 修改基本表， 会影响到视图
 
UPDATE emp 
	SET job = 'SALESMAN' 
	WHERE empno = 7369
 
-- 3. 视图中可以再使用视图 , 比如从emp_view01 视图中，选出empno,和ename做出新视图
DESC emp_view01
 
CREATE VIEW emp_view02
	AS
	SELECT empno, ename FROM emp_view01
	
SELECT * FROM emp_view02

在这里插入图片描述

十一、MySQL管理

在这里插入图片描述

代码演示

--       一、Mysql用户的管理
-- 原因：当我们做项目开发时，可以根据不同的开发人员，赋给他相应的Mysql操作权限
-- 所以，Mysql数据库管理人员(root), 根据需要创建不同的用户，赋给相应的权限，供人员使用
 
-- 1. 创建新的用户
-- 解读 (1) 'hsp_edu'@'localhost' 表示用户的完整信息 'hsp_edu' 用户名 'localhost' 登录的IP
-- (2) 123456 密码, 但是注意 存放到 mysql.user表时，是password('123456') 加密后的密码
--     *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
create user 'hsp_edu'@'localhost' identified by '123456'
-- 查询所有用户
select `host`, `user`, authentication_string  
	from mysql.user
 
-- 2. 删除用户
drop user 'hsp_edu'@'localhost'
 
-- 3. 登录
-- 查看当前用户
select current_user();

-- root 用户修改其他用户 hsp_edu@localhost 密码, 是可以成功.
alter user 'hsp_edu'@'localhost' identified by '111111';



--           二、用户权限的管理
 
-- 使用root 用户创建 testdb  ,表 news
CREATE TABLE news (
	id INT ,
	content VARCHAR(32));
-- 添加一条测试数据
INSERT INTO news VALUES(100, '北京新闻');
SELECT * FROM news;
 
-- 给 hsp_edu 分配查看 news 表和 添加news的权限
GRANT SELECT , INSERT 
	ON mydb.news
	TO 'hsp_edu'@'localhost'
	
-- 可以增加update权限
GRANT UPDATE  
	ON mydb.news
	TO 'hsp_edu'@'localhost'
	
 
-- 回收 hsp_edu 用户在 mydb.news 表的所有权限
REVOKE SELECT , UPDATE, INSERT ON mydb.news FROM 'hsp_edu'@'localhost'
REVOKE ALL ON mydb.news FROM 'hsp_edu'@'localhost'
 
-- 删除 hsp_edu
DROP USER 'hsp_edu'@'localhost'



-- 说明 用户管理的细节
-- 1.在创建用户的时候，如果不指定Host, 则为% , %表示表示所有IP都有连接权限 
-- create user  xxx;
 
CREATE USER jack

SELECT `host`, `user` FROM mysql.user
 
-- 2.你也可以这样指定 
-- create user  'xxx'@'192.168.1.%'  表示 xxx用户在 192.168.1.*的ip可以登录mysql
 
CREATE USER 'smith'@'192.168.1.%'
 
-- 3.在删除用户的时候，如果 host 不是 %, 需要明确指定  '用户'@'host值'
 
DROP USER jack -- 默认就是 DROP USER 'jack'@'%'
 
DROP USER 'smith'@'192.168.1.%'

navicat的权限操作可能有老师讲的不同，请参考这篇博客

使用navicat可视化界面更换用户

特别说明
本文章是个人整理的学习笔记，参考b站韩顺平老师的课程（【零基础快速学Java】韩顺平零基础30天学会Java）。老师讲的非常好，有兴趣的可以去看一下。

一万年可长否

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Java基础（二十四）：MySQL

约束用于确保数据库的数据满足特定的商业规则。在mysql中，约束包括：not null（非空）、unique（唯一）、primary key（主键）、foreign key（外键）、check五种。本文章是个人整理的学习笔记，参考b站韩顺平老师的课程（【零基础快速学Java】韩顺平零基础30天学会Java）。老师讲的非常好，有兴趣的可以去看一下。自连接指的是同一张表的连接查询，特点：把一张表当做多张表，需要给表。索引的目的是加快查询速度，但是也占用一部分空间。，列名不确定也可以指定列的别名。
复制链接

扫一扫