MySQL操作全攻略：库、表、数据、事务全面指南

么凹猫'

已于 2024-05-24 17:42:05 修改

阅读量1k

点赞数 36

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

于 2024-05-22 09:23:43 首次发布

本文链接：https://blog.csdn.net/m0_48173416/article/details/139073348

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

在这里插入图片描述

系列文章目录

更新ing...

文章目录

系列文章目录
前言
一、库的操作
二、表的操作
三、数据的操作
四、表结构的操作
五、补充

前言

深入MySQL：库、表、数据与事务精要解析：
MySQL，作为流行的数据库系统，其功能强大且操作灵活。本文将带您快速了解库的操作、表的管理、数据的处理以及事务的掌控，同时在文末补充了对mysql基本数据类型、字段完整性约束等内容的详解，希望您能从中获得有价值的启示。

一、库的操作

1.查询所有的库

show databases;

2.创建数据库

语法：create database 数据库名 charset utf8;
案例：create database test charset utf8;

3.删除数据库

语法：drop database 数据库名;
案例：drop database test;

4.选择库

选择库也称之为进入库、使用库
语法：>>> use 数据库名;
案例：>>> use test;

二、表的操作

$\color{red}{当想要对表进行操作前，一定要先选择库}$

1.查询所有的表

show tables;

2.创建表

$\color{red}{(建表过程中涉及到的 mysql基本数据类型，字段完整性约束在文末补充中)}$

语法：>>> 
create table 表名(
  字段1 类型 约束,
  字段2 类型 约束,
  字段3 类型 约束
);

user表
id 编号    整型
name 姓名   可变长度10-->字符串
age 年龄   小范围的整型
gender 性别   单选 男 女 保密
height 身高   小数   178.23
birthday 出生日期   日期

案例：>>>
create table user(
	id int,
	name varchar(10),
	age tinyint,
	gender enum("男","女","保密"),
	height decimal(5,2),
	birthday date
);

# 注意：
1.每一个字段必须要指定类型，约束可有可无
2.字段与字段之间使用逗号隔开
3.最后一个字段后不能写逗号，否则会报错

3.查询表创建的语句

语法：show create table 表名；
案例：show create table test;

4.查询表结构

语法： desc 表名;
案例： desc test;

5.删除表

语法：drop table 表名;
案例：drop table user;

三、数据的操作

1.查询表中的数据

语法： select * from 表名;
案例： select * from user;
*代表所有的字段

数据的查询内容较多，后续单独放到一篇：深入探索MySQL SELECT查询：从基础到高级，解锁数据宝藏的密钥

2.给表增加数据

2.1 给表中所有字段增加数据

# 1.一次增加一条数据
语法：>>> insert into 表名 value(值1,值2,值3……);
案例：>>> insert into user value(1,"张三",18,"男",178.23,"2002-03-06");

# 2.一次增加多条数据
语法：>>> insert into 表名 value(值1,值2,值3……),(值1,值2,值3……),(值1,值2,值3……);
案例：>>> insert into user value
(1,"张三",18,"男",178.23,"2002-03-06"),
(2,"李四",16,"女",156.23,"2003-05-06"),
(3,"喜羊羊",8,"保密",147.56,"2016-09-17");

# 注意：
1.每一条数据中的值放在一个小括号中包裹
2.多条数据之间使用逗号隔开
3.最后一条数据后不加逗号

2.2 给表中指定的字段增加数据

# 1.一次增加一条数据
语法：>>> insert into 表名(字段1,字段2……) value(值1,值2……);
案例：>>> insert into user(name,age,gender) value("玛卡巴卡",8,"男");

# 2.一次增加多条数据
语法：>>> insert into 表名(字段1,字段2……) value(值1,值2……),(值1,值2……),(值1,值2……);
案例：>>> insert into user(name,age,gender) value
("玛卡巴卡",8,"男"),
("唔西迪西",6,"女"),
("依古比古",10,"男");

3.数据的修改

# 修改表中指定字段所有的数据
语法：>>> update 表名 set 字段 = 新值;
案例：>>> update people set height = 156;

# 修改表中某一条数据中指定字段的值
语法：>>> update 表名 set 字段 = 新值 where 条件;
案例：>>> update people set height = 170 where sex=2;

4.数据的删除

4.1 物理删除

# 删除表中所有的数据
语法：>>> delete from 表名;
案例：>>> delete from people;

# 删除指定数据
语法：>>> delete from 表名 where 条件;
案例：>>> delete from people where name="喜羊羊";

4.2 逻辑删除

逻辑删除的本质就是在修改数据

1.给表增加一个字段判断是否进行删除
语法：>>> alter table 表名 add 字段名 类型 约束;
案例：>>> alter table people add is_con enum("√","×") default "√";

2.将要删除的数据的值进行修改
语法：>>> update 表名 set 字段 = 新值 where 条件;
案例：>>> update people set is_con = "×" where name = "张三";

四、表结构的操作

1.修改表名

语法：>>> alter table 表名 rename 新表名;
案例：>>> alter table user rename person;

2.给表增加字段

语法：>>> alter table 表名 add 字段名 类型 约束;
案例：>>> alter table person add address varchar(10) unique;

3.删除表中字段

语法：>>> alter table 表名 drop 字段名;
案例：>>> alter table person drop address;

4.修改表中字段

4.1不修改字段名的修改

语法：>>> alter table 表名 modify 字段名 类型 约束;
案例：>>> alter table person modify gender enum("男","女","保密","中性") default "保密";

4.2 修改字段名的修改

语法：>>> alter table 表名 change 原字段名 新字段名 类型 约束;
案例：>>> alter table person change gender sex enum("男","女","保密");

五、补充

1.mysql基础数据类型

# 整型：
	tinyint  范围较小   -128~127   一般用于年龄字段的限制
	int  范围较大
# 小数
	decimal(a,b)   指定当前小数最多有a位，其中小数位数最多是b位
	例如：decimal(5,2)   456.00
# 字符串
	char(num)
		表示固定长度的字符串，如char(3)，如果填充'ab'时会补一个空格为'ab '，3表示字符数
		应用：存储固定长度的数据  身份证号18   手机号 11
	varchar(num)
		表示可变长度的字符串，如varchar(3)，填充'ab'时就会存储'ab'，3表示字符数
		应用：存储可变长度数据   姓名  家庭住址
# 日期时间
	date   日期   "年-月-日"
	time   时间   "时:分:秒"
	datetime  日期时间   "年-月-日 时:分:秒"
	year   年份    "年份"
# 枚举
	enum(选项1，选项2……)
	枚举默认给每一个选项一个序号，序号从1开始
	例如：enum("男","女","保密")     gender="男" 	   gender=1
......后续通过使用情况补充......

2.字段完整性约束

2.1 主键

primany key

primary key == unique + not null
一张表中只能有一个字段拥有主键约束
给表中哪个字段设置了主键约束，要求当前这个字段必须要有值且值不能位null

create table t1(
id tinyint primary key,
name varchar(10)
);

insert into t1 value(1,"张三");
insert into t1 value(default,"漳卅");   # 设置的default位null，不能使用default
insert into t1 value(null,"漳卅");    # id值不能位null -->非空
insert into t1 value(1,"李四");    # id值不能重复-->唯一
insert into t1(name) value("王五");  # id不能没有值

2.2 自增

auto_increment
自增约束不能单独使用，需要与主键约束或唯一约束搭配使用
给表中哪个字段设置自增约束，当前字段要求整型，如果给值设置null、0、default、不传值，都能实现自增

create table t2(
id tinyint primary key auto_increment,
name varchar(10)
);

insert into t2 value(1,"张三");
insert into t2 value(1,"李四");   # id值不能重复
insert into t2 value(null,"李四");  # id值为null，实现自增
insert into t2 value(default,"李四");  # id值使用default，实现自增
insert into t2(name) value("李四");  # id不传值，实现自增
insert into t2 value(0,"李四");   # id值为0，实现自增
insert into t2 value(-2,"李四");

2.3 唯一

unique
给表中的哪一个字段增加了唯一约束，要求当前这个字段的值必须唯一，可以为空

create table t3(
id tinyint unique,
name varchar(5)
);

insert into t3 value(1,"张三");
insert into t3 value(1,"李四");   # id字段的值重复，报错 Duplicate entry '1' for key 't1.id'
insert into t3 value(2,"张三");   # 添加成功，name字段的值重复
insert into t3(name) value("张三");  # 添加成功，id字段不指定值，null不算重复值

2.4 非空

空  null
非空 not null
给表中的哪一个字段设置了非空约束，要求当前这个字段的值不能为null
(1.不能手动设置值为null  2.不能使用默认值null  3.如果默认值为null时，要求必须给这个字段传值，不传值会报错)

create table t4(
id tinyint,
name varchar(5) not null
);

insert into t4 value(1,"张三");
insert into t4 value(null,"张三");   # 添加成功，id值为null
insert into t4 value(2,null);   # 添加失败，name值不能为空 ERROR 1048 (23000): Column 'name' cannot be null
insert into t4(id) value(3);   # 添加失败，name字段不能不传值(默认值为null)

2.5 默认

default 默认值
给表中哪一个字段设置了默认值，此刻让该字段使用default或者不给该字段传值，都会直接应用默认值

create table t5(
id tinyint,
name varchar(5) default "神秘人"
);

insert into t5 value(1,"张三");
insert into t5 value(1,null);   # 添加成功，指定name字段的值为null
insert into t5 value(1,default);  # 添加成功，指定name字段的值为默认值
insert into t5(id) value(1);   # 添加成功，不给name字段指定值就会使用默认值

2.6 外键

2.6.1 概念

外键约束:对外键字段的值进行更新和插入时会和引用表中字段的数据进行验证，数据如果不合法则更新和插入会失败，保证数据的有效性

a.想要限制哪一张表中的哪一个字段的值，该字段应该设置外键约束，必须受到另外一张表的某个字段的值的限制
b.被限制的字段与起限制性作用的字段类型一致
c. 起限制性作用的字段要求必须是主键
d. 如果表中已经存在不合规的数据，再增加外键约束会失败；需要将不合规的数据删除，再增加外键约束即可

2.6.2 案例

create table address(
id tinyint primary key,
name varchar(5)
);

insert into address value
(1,"河南省"),
(2,"河北省"),
(3,"山东省");

create table user1(
	id tinyint,
	name varchar(5),
	add_id tinyint,
	foreign key(add_id) references address(id)
);

insert into user1 value(1,"张三",1);   # add_id在范围内，则添加成功
insert into user1 value(2,"李四",6);   # add_id超出范围，则添加失败

2.6.3 设置外键

foreign key(字段) references 表(字段)

1.在创建表时增加外键约束
语法： create table 表名(字段 类型 约束,字段 类型 约束,foreign key(字段) references 表(字段));
案例： create table user1(
		id tinyint,
		name varchar(5),
		add_id tinyint,
		foreign key(add_id) references address(id)
	);

2.给已经创建好的表增加外键约束
语法：alter table 表名 add foreign key(字段) references 表(字段);
案例：alter table user1 add foreign key(add_id) references address(id);

2.6.4 删除外键

如果给一张表中设置外键，系统会自动生成外键名

# 查询外键名-->查询创建表的语句
语法：>>> show create table 表名;
案例：>>> show create table user1;
结果：CONSTRAINT `user1_ibfk_1`    此时user1_ibfk_1就是外键名

# 从表中删除外键
语法：>>> alter table 表名 drop foreign key 外键名;
案例：>>> alter table user1 drop foreign key user1_ibfk_1;

3.事务

3.1 事务的定义

所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性

3.2 事务的特性

原子性：事务是一个不可分割的最小工作单元；事务中的操作要么全部成功提交，要么全部失败回滚；
一致性：事务总是从一个一致性的状态转换到另外一个一致性的状态；
隔离性：事务在最终提交之前，对其他事务不可见；
持久性：事务一旦提交，其所做的修改会永久保存到数据库中；

3.3 事务的操作

3.3.1 事务的查看

表的引擎类型必须是innodb类型才可以使用事务
# 查询创建表的语句
语法：>>> show create table 表名;
案例：>>> show create table people;

# 注意：出现ENGINE=InnoDB，说明表的引擎为innodb

3.3.2 事务的开启

begin;
start transaction;

3.3.3 事务的提交

commit;

3.3.4 事务的回滚

rollback;

3.4 事务的案例实现

3.4.1 案例

create table aa(
id tinyint,
money int
);

insert into aa value(1,100),(2,200);

3.4.2 事务提交案例

id1=100   id2=200
# 第一步：将终端1中的事务进行开启   
	>>> begin;
# 第二步：对终端1中的表进行操作-->增加数据、修改数据、删除数据……
	>>> insert into aa value(3,300)
# 第三步：查询终端1中的数据
	>>> select * from aa;
		id1=100   id2=200   id3=300
# 第四步：查询终端2中的数据
	>>> select * from aa;
		id1=100   id2=200
		问题：终端1和终端2都是aa表，终端2中的数据没有与终端1中的数据同步
		原因：终端1中的事务没有提交
# 第五步：将终端1中的事务进行提交
	>>> commit;
# 第六步：查询终端1中的数据
	>>> select * from aa;
		id1=100   id2=200   id3=300
# 第七步：查询终端2中的数据
	>>> select * from aa;
		id1=100   id2=200  id3=300
		结果：终端2中的数据与终端1中的数据同步
		原因：终端1中的事务提交

3.4.3 事务回滚案例

id1=100   id2=200  id3=300
# 第一步：在终端1中开启事务
	>>> begin;
# 第二步：在终端1中进行数据的操作
	>>> insert into aa value(4,400);
	>>> update aa set money = 1000 where id = 1;
	>>> delete from aa where id = 3;
# 第三步：查询终端1中的数据
	>>> select * from aa;
		id1=1000   id2=200   id4=400
# 第四步：查询终端2中的数据
	>>> select * from aa;
		id1=100   id2=200   id3=300
		问题：终端1和终端2都是aa表，终端2中的数据没有与终端1中的数据同步
		原因：终端1中的事务没有提交
# 第五步：将终端1中的事务进行回滚
	>>> rollback;
# 第六步：查询终端1中的数据
	>>> select * from aa;
		id1=100   id2=200   id3=300
		结果：终端1中的数据回复到最初始
		原因：将事务的进行回滚，在事务开始后的维护到本地缓存的所有的操作全部归零
# 第七步：查询终端2中的数据
	>>> select * from aa;
		id1=100   id2=200  id3=300
		结果：终端2中的数据与终端1中的数据同步
		原因：终端1中的事务提交