Java数据库部分（MySQL+JDBC）（一、MySQL超详细学习笔记）（下）-阿里云开发者社区

Java数据库部分（MySQL+JDBC）（一、MySQL超详细学习笔记）（中）：https://developer.aliyun.com/article/1580543

12 表的操作

12.1 查看表 show

-- 查看当前数据库下所有的表
show tables;

12.2 查看表结构 desc

-- 查看表结构
-- desc 表名
desc emp

12.3 表的创建【重要】

CREATE TABLE 表名(

列名数据类型 [约束] ,

列名数据类型 [约束] ,

…

列名数据类型 [约束]

);

-- 建表
create table user(
    username varchar(50),
    password varchar(50),
    age int,
    phone_num varchar(20),
    birthday date,
    money double
)

12.4 删除表

drop table 表名;删除表

-- 删除表
-- drop table 表名;
-- drop table if exists 表名;
drop table if exists user;

12.5 修改表【了解】alter

添加列

alter table 表名 add 添加的字段数据类型;

-- 在user表中添加一个address字段
alter table user add address varchar(100);

修改列的数据类型

alter table 表名 modify 修改的字段数据类型;

-- 在user表中修改birthday字段的类型为datetime类型
alter table user modify birthday datetime;

删除列

alter table 表名 drop 字段名;

-- 在user表中删除一个phone_num字段
alter table user drop phone_num;

修改列名

alter table 表名 change 旧列名新列名数据类型;

-- 在user表中将username改为name
alter table user change username name varchar(50)

13 表的约束【理解】

13.1 约束

约束：

用于限制加入到表中的数据的类型和规范

13.2 约束的分类

实体完整性

域完整性

引用完整性

13.3 实体完整性

主键约束

主键的特点：非空+唯一

主键的语法：字段名数据类型 primary key

唯一约束

唯一特点：某个字段在一列数据中是唯一的（可以为多个null）

唯一语法：字段名数据类型 unique

自增长约束

自增长特点：一般与主键一起使用，每添加一条数据，主键自动生成，在原有的基础上加1
自增长语法：字段名数据类型 primary key auto_increment

联合主键约束

联合主键特点：与主键的作用含义一样，只不过由多个字段一起组成主键
应用场景：中间表
自增长语法： primary key(字段1,字段2)

/*
  约束：
    实体完整性约束
      主键约束
        主键：指的是一行数据唯一标识的那一列
          自然主键：主键字段就是这个表的某个属性
          代理主键：使用一个与表无关字段来作为表的主键
        主键的特点：非空+唯一
                主键的语法：字段名 数据类型 primary key

            唯一约束
         唯一特点：某个字段在一列数据中是唯一的
                  唯一语法：字段名 数据类型 unique

            自增长约束
         自增长特点：一般与主键一起使用，每添加一条数据，主键自动生成，在原有的基础上加1   
         自增长语法： 字段名 数据类型 primary key auto_increment

             联合主键约束
        联合主键特点：与主键的作用含义一样，只不过由多个字段一起组成主键
                应用场景：中间表
        自增长语法： primary key(字段1,字段2)
*/

drop table if exists user;
create table user(
  id int primary key,
  name varchar(20),
  age int
)
insert into user values(1,'jack',30); 

insert into user (name,age) values('jack',30); -- 错误，违反主键约束
insert into user values(null,'jack',30); -- 错误，违反主键约束(非空)
insert into user values(1,'rose',30); -- 错误，违反主键约束(唯一)


drop table if exists user;
create table user(
  id int primary key,
  name varchar(20) unique,
  age int
)
insert into user values(1,'jack',30);


insert into user values(2,'jack',31);  -- 错误，违反唯一约束
insert into user values(3,null,33); 
insert into user values(4,null,34); 



drop table if exists user;
create table user(
  id int primary key auto_increment,
  name varchar(20) unique,
  age int
)


insert into user (name,age) values ('jack',33);
insert into user (name,age) values ('rose',33);
insert into user  values (null,'马冬梅',34);



drop table if exists user;
create table user(
  name varchar(20),
  age int,
  primary key(name,age)
)
insert into user values('jack',30);
insert into user values('jack',31);
insert into user values('rose',31);
insert into user values('jack',30); -- 错误，违反联合主键约束

13.4 域完整性

/*
    域完整性约束
      数据类型约束(必填)

      非空约束
         非空特点：添加数据的时候，不能为null
         非空语法：字段名 数据类型 not null

      默认值约束
         默认值特点：这一列数据如果不添加，则默认值生效
         默认值语法： 字段名 数据类型 default '默认值'

*/

drop table if exists user;
create table user(
  id int primary key auto_increment,
  name varchar(20) not null,
  age int
)
insert into user (name,age) values (null,33); -- 错误，违反非空约束
drop table if exists user;
create table user(
  id int primary key auto_increment,
  name varchar(20) default '宋小宝',
  age int
) 
insert into user (name,age) values (null,33); -- 注意：默认值不生效
insert into user (age) values (33); -- 默认值生效

13.5 引用完整性

引用完整性约束：

外键约束

语法：foreign key

特点

主表中有的从表中可以没有

主表中没有的从表中不能有（从表中修改、增加）

删除主表数据要保证从表中没有（主表中删除）

总结：在实战开发中,如果能不使用外键的尽量就不使用外键。使用逻辑外键

/*
  引用完整性约束(在实际开发，如果能够不使用外键约束就不使用，我们可以使用逻辑外键)
    引用完整性约束：指的是多张表之间的引用约束
    主表(dept:主键)    从表(emp：外键)
        外键约束
       特点：
         1、主表中有的从表中可以没有，但是主表没有的从表一定不能有
         2、删除主表中的数据的时候，要保证从表没有与之关联的数据
         3、修改从表中数据的时候。要保证主表中存在
            
            语法：
        constraint 外键约束的名称 foreign key(外键字段) references 关联表(关联字段)

*/

drop table if exists tb_dept;
create table tb_dept(
  deptno int primary key,
  dname varchar(30),
  location varchar(30)
);

drop table if exists tb_emp;
create table tb_emp(
  empno int primary key,
  ename varchar(30),
  job varchar(30),
  deptno int,
  constraint fk_dept_emp foreign key(deptno) references tb_dept(deptno)
)
insert into tb_dept values(100,'开发部','金融港');
insert into tb_dept values(101,'人事部','光谷广场');
insert into tb_emp values(6666, 'jack','java开发',20000); -- 错误，违反了外键约束

insert into tb_emp values(6666, 'jack','java开发',100);
delete from tb_dept where deptno = 100; -- 错误，违反了外键约束
update tb_emp set deptno = 102 where empno = 6666;

14 权限管理【了解】

创建用户

语法：create user 用户名 identified by 用户密码

用户授权

 语法：grant 权限名称(select、update、insert、alter、create…) on 数据库.数据表 to 用户

回收权限

语法：revoke 权限名称(select、update、insert、alter、create…) on 数据库.数据表 from 用户

删除用户

语法: drop user 用户名

-- 创建用户
create user 'cxk' identified by '123456';

-- 授权
-- grant all(select、delete、update、select、create、drop..) on *.* to 用户
-- *.* 所有表
grant select on java2108.emp to 'cxk'

-- 回收权限
-- revoke all on *.* from 用户
revoke select on java2108.emp from 'cxk';

-- 删除
-- drop user if exists 用户
drop user if exists 'cxk'

15 视图【了解】

15.1 概念

视图，虚拟表，从一个表或多个表中查询出来的表，作用和真实表一样，包含一系列带有行和列的数据。视图中，用户可以使用SELECT语句查询数据，也可以使用INSERT，UPDATE，DELETE修改记录，视图可以使用户操作方便，并保障数据库系统安全。

15.2 视图特点

优点

简单化，数据所见即所得。

安全性，用户只能查询或修改他们所能见到得到的数据。

逻辑独立性，可以屏蔽真实表结构变化带来的影响。

缺点

性能相对较差，简单的查询也会变得稍显复杂。

修改不方便，特变是复杂的聚合视图基本无法修改。

15.3 视图的创建

语法：CREATE VIEW 视图名 AS 查询数据源表语句;

15.3.1 创建视图

-- 创建视图
create view myView as (select empno,ename,job,mgr,hiredate,deptno from emp)

15.3.2 使用视图

-- 查询视图
select * from myView;

15.4 视图的修改

方式一：CREATE OR REPLACE VIEW 视图名 AS 查询语句

方式二：ALTER VIEW 视图名 AS 查询语句

15.4.1 修改视图

-- 修改视图
-- 方式1：
create or replace view myView as((select empno,ename,job,hiredate,deptno from emp))
-- 方式2：
alter view myView as ((select empno,ename,hiredate,deptno from emp))

15.5 视图的删除

DROP VIEW 视图名

15.5.1 删除视图

-- 删除视图
drop view if exists myView;

注意：删除视图不会影响原表

15.6 视图的注意事项

注意：

视图不会独立存储数据，原表发生改变，视图也发生改变。没有优化任何查询性能。

如果视图包含以下结构中的一种，则视图不可更新

聚合函数的结果
DISTINCT 去重后的结果
GROUP BY 分组后的结果
HAVING 筛选过滤后的结果
UNION、UNION ALL 联合后的结果

16 事务【理解】

16.1 事务的概念

**事务（transaction）**是一个原子操作。是一个最小执行单元。可以由一个或多个SQL语句组成，在同一个事务当中，所有的SQL语句都成功执行时，整个事务成功，有一个SQL语句执行失败，整个事务都执行失败。

16.2 事务的边界

开始：连接到数据库，执行一条DML语句。上一个事务结束后，又输入了一条DML语句，即事务的开始

结束：
1). 提交：
a. 显示提交：commit;

b. 隐式提交：一条创建、删除的语句，正常退出（客户端退出连接）;

2). 回滚：

a. 显示回滚：rollback;

b. 隐式回滚：非正常退出（断电、宕机），执行了创建、删除的语句，但是失败了，会为这个无效的语句执行回滚。

16.3 事务的原理

数据库会为每一个客户端都维护一个空间独立的缓存区(回滚段)，一个事务中所有的增删改语句的执行结果都会缓存在回滚段中，只有当事务中所有SQL 语句均正常结束（commit），才会将回滚段中的数据同步到数据库。否则无论因为哪种原因失败，整个事务将回滚（rollback）。

16.4 事务的特性

Atomicity(原子性)

一个事务不可分割，要么一起执行成功，要么一起失败

Consistency(一致性)

事务操作的前后要保证的数据的一致

Isolation(隔离性)

一个事务的操作不能影响到另一个事务的执行

Durability(持久性)

一个事务操作完成对数据库的影响是永久

16.5 事务应用

应用环境：基于增删改语句的操作结果（均返回操作后受影响的行数），可通过程序逻辑手动控制事务提交或回滚

事务完成转账

#A 账户给 B 账户转账。
#1.开启事务
set autocommit=0;  #禁止自动提交 set autocommit=1;#开启自动提交
#2.事务内数据操作语句
UPDATE ACCOUNT SET MONEY = MONEY-1000 WHERE ID = 1;
UPDATE ACCOUNT SET MONEY = MONEY+1000 WHERE ID = 2;
#3.事务内语句都成功了，执行 COMMIT；
COMMIT;
#4.事务内如果出现错误，执行 ROLLBACK;
ROLLBACK;

注意：开启事务后，执行的语句均属于当前事务，成功再执行 COMIIT，失败要进行 ROLLBACK

16.6 事务的并发问题

1、脏读：事务A读到事务B没有提交的数据

2、不可重复读：事务A在同一个事务中，因为事务B修改了数据并提交，造成两次读取到的数据不一致
3、幻读：事务A在同一个事务中，因为事务B新增了数据并提交，如果事务A修改数据，会出现多修改了一条数据，出现幻觉

16.7 如何解决事务并发问题

通过设置事务隔离级别可以解决以上问题

事务隔离级别	脏读	不可重复读	幻读

读未提交（read-uncommitted）	是	是	是
不可重复读（read-committed）—Oracle默认的隔离级别	否	是	是

可重复读（repeatable-read）—MySQL默认隔离级别，幻读一般是可以接受的	否	否	是
串行化（serializable）–效率低	否	否	否

16.8 演示问题

查看当前数据库的隔离级别

select @@tx_isolation;

修改当前数据库的隔离级别

set session transaction isolation level 隔离级别

eg： set session transaction isolation level repeatable read；

17 SQL语言的分类

17.1 SQL语言分类

数据查询语言DQL（Data Query Language）：select、where、order by、group by、having 。【重要】

数据定义语言DDL（Data Definition Language）：create、alter、drop。
数据操作语言DML（Data Manipulation Language）：insert、update、delete 。【重要】
事务处理语言TPL（Transaction Process Language）：commit、rollback 。【重要】
数据控制语言DCL（Data Control Language）：grant、revoke。

补充：

T，失败要进行 ROLLBACK**

16.6 事务的并发问题

1、脏读：事务A读到事务B没有提交的数据

2、不可重复读：事务A在同一个事务中，因为事务B修改了数据并提交，造成两次读取到的数据不一致

3、幻读：事务A在同一个事务中，因为事务B新增了数据并提交，如果事务A修改数据，会出现多修改了一条数据，出现幻觉

16.7 如何解决事务并发问题

通过设置事务隔离级别可以解决以上问题

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
不可重复读（read-committed）—Oracle默认的隔离级别	否	是	是

可重复读（repeatable-read）—MySQL默认隔离级别，幻读一般是可以接受的	否	否	是
串行化（serializable）–效率低	否	否	否

16.8 演示问题

查看当前数据库的隔离级别

select @@tx_isolation;

修改当前数据库的隔离级别

set session transaction isolation level 隔离级别

eg： set session transaction isolation level repeatable read；

17 SQL语言的分类

17.1 SQL语言分类

数据查询语言DQL（Data Query Language）：select、where、order by、group by、having 。【重要】

数据定义语言DDL（Data Definition Language）：create、alter、drop。
数据操作语言DML（Data Manipulation Language）：insert、update、delete 。【重要】

事务处理语言TPL（Transaction Process Language）：commit、rollback 。【重要】

数据控制语言DCL（Data Control Language）：grant、revoke。

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

Java数据库部分（MySQL+JDBC）（一、MySQL超详细学习笔记）（下）

12 表的操作

12.1 查看表 show

12.2 查看表结构 desc

12.3 表的创建 【重要】

12.4 删除表

12.5 修改表【了解】alter

13 表的约束【理解】

13.1 约束

13.2 约束的分类

13.3 实体完整性

13.4 域完整性

13.5 引用完整性

14 权限管理【了解】

15 视图【了解】

15.1 概念

15.2 视图特点

15.3 视图的创建

15.3.1 创建视图

15.3.2 使用视图

15.4 视图的修改

15.4.1 修改视图

15.5 视图的删除

15.5.1 删除视图

15.6 视图的注意事项

16 事务【理解】

16.1 事务的概念

16.2 事务的边界

16.3 事务的原理

16.4 事务的特性

16.5 事务应用

16.6 事务的并发问题

16.7 如何解决事务并发问题

16.8 演示问题

17 SQL语言的分类

17.1 SQL语言分类

补充 ：

16.6 事务的并发问题

16.7 如何解决事务并发问题

16.8 演示问题

17 SQL语言的分类

17.1 SQL语言分类

补充 ：

热门文章

最新文章

相关课程

相关电子书

推荐镜像

12.3 表的创建【重要】

补充：

补充：