mysql数据库之常用SQL语句及事务学习笔记（一）

mysql学习笔记。

数据库介绍

几个常见的缩写：

• DB：数据库。全称：DataBase。
• DBMS：数据库管理系统。全称：DataBase Management System。
• DBS：数据库系统。全称：DataBase System。
• DBA：数据库管理员。全称：DataBase Administrator。

DBS、DBMS、DB三者关系：DBS包含DB、DBMS。

数据库定义：

数据库：按照一定的数据结构来组织、存储和管理数据。

数据库相关SQL

连接数据库：

mysql -u 用户名 -p 密码

退出数据库：

exit

查看所有数据库

show databases;

创建数据库

create database 数据库名称 character set utf8;

查看数据库详情

show create database 数据库名称;

使用数据库

use 数据库名称;

删除数据库

drop database 数据库名称;

数据表相关SQL

操作数据表之前一定要先使用数据库。只有使用了具体的数据库，才能在该数据库下创建数据表。

创建数据表

create table 表名称 (表字段);

指定数据库引擎和字符集创建数据表

create table 表名称 (表字段) engine=innodb charset=utf8;

数据库引擎有两个：innodb和myisam。默认数据库引擎是innodb。

查看已创建的所有数据表

show tables;

查看数据表详情

show create table 数据表名称;

查看表字段信息

desc 数据表名称;

删除数据表

drop table 数据表名称;

修改表相关SQL

修改数据表名称

rename table 原数据表名称 to 新数据表名称;

修改数据表引擎和字符集

alter table 数据表名称 engine=值 charset=字符集;

添加数据表字段

// 默认添加的字段放在所有字段最后面
alter table 数据表名称 add 字段名称 字段类型;

// 将新添加的字段放在最前面
alter table 数据表名称 add 字段名称 字段类型 first;

// 将新添加的字段放在某个字段后面
alter table 数据表名称 add 字段名称 字段类型 after 字段名称;

删除数据表字段

alter table 数据表名称 drop 字段名称;

修改表字段名称和类型

alter table 数据表名称 change 原字段名称 新字段名称 字段类型;

修改表字段类型和位置

// 修改后，字段还在原来位置
alter table 数据表名称 modify 字段名称 字段类型;

// 修改后，在所有字段最前面
alter table 数据表名称 modify 字段名称 字段类型 first;

// 修改后，在某个字段后面
alter table 数据表名称 modify 字段名称 字段类型 after 字段名称;

数据相关的SQL

插入数据(INSERT)

// 全表全字段插入
insert into 数据表名称 (字段1,字段2,字段3...) values (字段1的值,字段2的值,字段3的值...);

// 特定字段插入
insert into 数据表名称 (字段1,字段2) values (字段1的值,字段2的值);

// 一次插入多条数据
insert into 数据表名称 (字段1,字段2,字段3...) values(字段值...),(字段值...),(字段值...).....;

删除数据(DELETE)

// 删除全部数据
delete from 数据表名称;

// 删除指定数据
delete from 数据表名称 where 条件;

修改数据(UPDATE)

// 修改全部数据
update 数据表名称 set 字段1 = 值1;

// 指定条件修改
update 数据表名称 set 字段1 = 值1 where 修改条件;

// 修改多字段
update 数据表名称 set 字段1 = 值1,字段2 = 值2 where 修改条件;

注意：修改对字段时，使用逗号隔开，不能使用and连接。

查询数据(SELECT)

// 查询全部数据
select * from 数据表名称;

// 查询指定字段
select 字段1,字段2 from 数据表名称;

// 条件查询
select 字段1,字段2 from 数据表名称 where 条件;

// 排序查询:desc表示降序，asc表示升序
select * from 数据表名称 order by 字段 desc/asc;

// 分组查询
select * from 数据表名称 group by 字段;

// 含有条件的分组查询
select * from 数据表名称 group by 字段 having 条件;

// 分页查询:limit m,n表示跳过m条数据，查询出n条数据。下面的limit 0,5表示跳过0条数据，查询5条数据
// 因此查询出前5条数据。
select * from 数据表名称 limit 0,5;

查询的语句非常丰富，可以是上面的所有查询语句结合使用。格式如下：

select * from 数据表名称 where 条件 order by 字段 desc/asc group by 字段 limit 0,5;

数据表字段约束

主键约束

• 主键是指能够唯一确定该条数据的字段。
• 主键是唯一且非空的。
• 约束是指创建数据表时，给某些字段添加的限制条件。

// 为id添加主键约束
create table 数据表名称 (
    id int primary key
) default charset=utf8;

主键约束代码：

primary key

自增约束

• 自增约束是指该字段的值自动增加。
• 自增规则：从数据表中最大值基础上，自增。
• 自增约束一般和主键一起使用，称为：主键自增约束。

// 创建自增约束
create table 数据表名称 (
    id int primary key auto_increment
) default charset=utf8;

自增约束代码：

auto_increment

非空约束

• 限制某个字段的值不能为空。

create table 数据表名称 (
    name varchar(20) not null
) default charset=utf8;

非空约束代码：

not null

唯一约束

• 限制某个字段的值是唯一的，只能在数据表中出现一次。

create table 数据表名称 (
    name varchar(20) unique
) default charset=utf8;

唯一约束代码：

unique

设置字段默认值约束

• 当没有为某个字段插入数据值时，如果该字段具有默认值约束，则会插入默认值。
• 如果为含有默认值约束的字段，插入null值，则不会触发默认值约束，而是插入null。

create table 数据表名称 (
    age int default 1
) default charset=utf8;

插入数据时：

// 主动插入数据值，不会触发默认值约束
insert into 数据表名称 (age) values (20);

// 插入null时，不会触发默认值约束
insert into 数据表名称 (age) values (null);

// 不插入，则会触发默认值约束,下面这条插入语句会插入1
insert into 数据表名称 values();

外键约束

• 外键就是用于和另外的数据表建立关系的字段。
• 外键的值可以为null。但是不能是关联表中没有的数据值。
• 如果建立外键约束，那么在删除时，不能先删除关联表中的数据以及数据表，需要先删除对应外键的数据。

create table 数据表名称 (
    id int,
    deptid int contraint 约束名 foreign key(外键字段名) references 被依赖的表名(被依赖的字段名)
);

注释comment

• comment用于说明字段的含义。

create table 数据表名称 (
    id int comment '用户id',
    name varchar(50) comment '用户名'
) default charset=utf8;

数据冗余

• 由于表设计的不合理，出现了大量重复的数据，称为数据冗余。
• 解决方案：可以将重复的数据提取出来，单独设计为一张表，然后和另一张表建立外键约束。

事务

事务的定义

• 事务是数据库中能够执行同一业务多条语句的工作单元，它能够保证多条语句要么全部成功，要么全部失败。

事务的ACID特性

• 原子性(Atomicity)：事务是一个不可分割的工作单元，要么全部成功，要么全部失败。
• 一致性(Consistency)：事务执行之前是一个完整的状态，执行完成之后，是另一个完整的状态。
• 隔离性(Isolation)：多个用户并发访问数据库时，一个用户的事务不会影响另一个用户的事务。
• 持久性(Durability)：一个事务一旦提交，那么它对数据库的操作就是永久的。

事务相关SQL（TCL）

开启事务

begin;

提交事务

commit;

事务提交会将内存中对数据操作改动，一次性提交到磁盘当中。

回滚事务

rollback;

回滚事务会将内存中对数据库的改动清除，从而保证数据库的数据不受影响。

保存回滚点

savepoint 自定义回滚点名称;

回滚到某个回滚点

rollback to 回滚点名称;

使用事务模拟银行账户流程：

• 创建一个测试数据库：mytest

create database mytest character set utf8;

• 选择数据库

use mytest;

• 创建一张测试数据表：test

create table test (
    id int primary key auto_increment comment '账户id',
    name varchar(20) not null comment '用户名称',
    money int default 0 comment '账户余额',
    status varchar(10) not null comment '账户状态'
) default charset=utf8;

• 插入两条测试数据：

insert into test (name,money,status) values ('张三',2000,'正常');
insert into test (name,money,status) values ('李四',500,'冻结');

• 只有账户状态是“正常”的用户才能转账成功。
• 开启事务

begin;

• 执行转账业务：李四给张三转账200。

update test set money = money + 200 where name = '张三' and status = '正常';

update test set money = money - 200 where name = '李四' and status = '正常';

• 修改之后，可以查询结果。

select * from test;

注意：如果是在刚刚执行了事务的窗口查看数据，则会出现下面的内容：

这是因为查询的是当前内存中的数据。其实数据库中的数据是没有改变的。可以新开一个窗口进行查看即可知道。

• 新开一个窗口，再次查询。

这是因为开启了事务，没有提交之前，都不会对数据库中的数据进行持久性修改。

因为修改失败，所以可以使用回滚。

• 回滚事务

rollback;