数据库——数据库基础操作以及表和表的三种关系(以mysql为例)——事务

DDL

DDL--数据库操作:

show databases; #展示所有数据库

create database 数据库名 ; #创建一个新的数据库

use 数据库名 ; #使用某一个数据库

select database(); #查询当前在哪一个数据库下

drop database 数据库名 ; #删除某一个数据库

DDL——表操作

show tables ; #展示当前数据库下的所有表单

create table 表名(字段 字段类型 , 字段 字段类型) #创建一个新的表单

desc 表名; #展示某一个表单

show create table 表名; #查看建表语句

alter table 表名 add/modify/change/drop/rename to ...;
#分别用来添加字段/修改字段类型/修改字段名称及类型/删除字段/重命名表名

drop table 表名; #删除某一个表单

DML---数据的增删改

insert into 表名(字段1，字段2, ...) values(值1，值2，...)[,(值1，值2)...];
#添加数据,中括号里面表示可写可不写，需要注意字段和值的对应情况

update 表名 set 字段1=值1，字段2=值2[where 条件];
#修改数据，如果没有后面where就是默认修改字段里的所有数据


delete from 表名[where 条件];
#删除数据,没有条件就是默认删除整张表的数据

DQL语句---查询实现

select
    字段列表-------->字段名[as]别名
from
    表名
where             
    条件列表-------->  >,>=,<,<=,=,<>,like,between...and in,and,or (在分组之前过滤)
group by
    分组字段列表
having
    分组后条件列表------>分组后过滤
order by
    排序字段列表-------->升序asc ，降序desc
limit
    分页参数----------->起始索引(从0开始)，每一页展示记录数



聚合函数
count 计算数量
max   最大值
min   最小值
avg   平均值
sum   求和

语法
select 聚合函数(字段列表) from 表名;



    

DCL语句

1.用户管理
    create user '用户名'@'主机名' identified by '密码';
    #创建新用户

    alter user '用户名'@'主机名' identified with mysql_password by '密码';
    #修改用户密码
    
    drop user '用户名'@'主机名';

2.权限控制
    
    grant 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
    #赋予用户权限

    revoke 权限列表 ON 数据库名.表名 from '用户名'@'主机名';
    #撤销用户权限
    

约束

案例：

使用如下的建表语句建表时，约束会加在一个属性的后面变成列级完整性约束

drop table if exists emp;

create table emp(
    id int  primary key ,   -- 员工ID，主键且自增
    ename varchar(50) not null unique, -- 员工姓名，非空且唯一
    joindate DATE not null,  -- 入职日期 非空
    salary double(7,2) not null , -- 工资，非空
    bonus double(7,2) default 0  -- 奖金，默认为0
);

 外键约束

 规范:使用外键约束时必须先创建主表（外键所在表）然后再创建从表

        但是从表和主表可以使同一张表

数据库设计

 一张ER图

表关系 

• 一对一:如图一个员工对应一个id
• 

在上面设置外键是唯一的之后就可以和一对多关系区分开来实现一对一的关系。

•  一对多(多对一): 如图一个部门有多个员工

实现方式:在多的一方建立外键，指向一的一方主键，在上面的外键约束就是这么一回事

• 多对多

多对多实现:需要建立第三张表，中间表至少包含两个外键，分别关联两方主键

以下面的订单和商品的关系来实现

建表语句

 外键关系添加 

事务  

用银行转账的例子来说就是一方扣钱之后如果出现异常了另一方没收到钱就会把一开始扣的钱退回去 

建表语句

DROP TABLE IF EXISTS account;

-- 创建账户表
CREATE TABLE account(
	id int PRIMARY KEY auto_increment,
	name varchar(10),
	money double(10,2)
);

-- 添加数据
INSERT INTO account(name,money) values('张三',1000),('李四',1000);

UPDATE account set money = 1000;

select * from account;

 下面模拟出现异常

-- 1. 查询李四账户金额是否大于500

-- 2. 李四账户 -500
UPDATE account set money = money - 500 where name = '李四';

异常:出现异常了...
-- 3. 张三账户 +500
UPDATE account set money = money + 500 where name = '张三';

输出如下，只有一方少钱了 

加上事务BEGIN;后执行结果不会直接修改到数据库，会先保存在一个地方，使用ROLLBACK可以回滚也就是返回初始状态，COMMIT;之后才会修改到数据库。

BEGIN;
-- 转账操作
-- 1. 查询李四账户金额是否大于500

-- 2. 李四账户 -500
UPDATE account set money = money - 500 where name = '李四';

 -- 出现异常了...
-- 3. 张三账户 +500
UPDATE account set money = money + 500 where name = '张三';

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

 

 

 

-- 查看事务的默认提交方式
SELECT @@autocommit;
-- 1 自动提交  0 手动提交
-- 修改事务提交方式
set @@autocommit = 0;