数据库的使用（SQL）（下）-阿里云开发者社区

六、数据库设计

6.1 设计简介

数据库设计概念

数据库设计就是业务系统个具体需求，结合我们所需要的DBMS，为这个业务系统构造出数据存储模式

建立数据库中得表结构以及表与表直接的关联关系
有哪些表？表里有那些字段，表与表之间有什么关系

数据库设计步骤

需求分析
逻辑分析
物理设计
维护设计

6.2表关系

6.2.1一对多

一对多

一个部门对应对个员工，一个员工对应多个部门

实现方式

再多的一方建立外键，指向一的一方的主键

建立语句

-- 删除表
DROP TABLE IF EXISTS tb_emp;
DROP TABLE IF EXISTS tb_dept;
-- 部门表
CREATE TABLE tb_dept(
  id int primary key auto_increment,
  dep_name varchar(20),
  addr varchar(20)
);
-- 员工表 
CREATE TABLE tb_emp(
  id int primary key auto_increment,
  name varchar(20),
  age int,
  dep_id int,
  -- 添加外键 dep_id,关联 dept 表的id主键
  CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES tb_dept(id) 
);

6.2.2多对多

多对多

一个商品对应多个订单，一个订单对应多个商品

实现方式

建立第三张中间表，中间表最少包含俩个外键，分别关联俩方的外键

案例

6.2.3一对一

一对一

一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表
将不经常用使用的字段放另一张表，用于性能提升

实现方式

在任意一方的键入外键，关联另一方主键，并且设置外键的唯一性(unigue)

案例

6.3 数据库设计案例

七、多表查询

7.1 连接查询

7.1.1 内连接

语法

-- 隐式内连接
select 字段列表 from 表1,表2 … where 条件;
-- 案例
select * from emp ,dept where emp.dep_id = dept.did;
-- 显示内连接
select 字段列表 from 表1 [inner] join 表2 on 条件;
-- 案例
select * from emp inner join dept on emp.dep_id = dept.did;
-- 上述语句inner可以省略
select * from emp join dept on emp.dep_id = dept.id;

内连接，相当于查询 A B交集数据

案例

-- 案例  隐式内连接
select * from emp ,dept where emp.dep_id = dept.did;
-- 案例 显示内连接
select * from emp inner join dept on emp.dep_id = dept.did;
-- 上述语句inner可以省略
select * from emp join dept on emp.dep_id = dept.id;

7.1.2 外连接

语法

-- 左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件;
-- 右外连接
select 字段列表 from 表1 right [outer] join 表2 on 条件;

左外连接：相当于查询A表所有数据和交集部分数据

右外连接：相当于查询B表所有数据和交集部分数据

左外连接

-- 查询emp表所有的数据和对应的部门信息(左外连接)
select * from emp left join dept on emp.dep_id = dept.did;

右外连接

-- 查询dept表所有数据和对应的员工信息(右外连接)
select * from emp right join dept on emp.dep_id = dept.dsid;

7.2子查询

概念

子查询：查询中嵌套查询语句，称嵌套查询为子查询

语法

/*
  需求： 查询工资高于猪八戒的员工信息
    第一步：先查出猪八戒的工资
    第二步：查询工资高于猪八戒的员工信息
*/
-- 子查询
select * from emp where salary > (select salary from emp where name = '猪八戒');

子查询根据查询结果不同，作用不同

子查询语句的结果：单行单列，子查询语句做为：条件值，使用= != > < 等条件语句
子查询语句的结果：多行单列，子查询语句做为：条件值，使用 in等关键字进行判断
子查询语句的结果：多行多列，子查询语句做为：虚拟表，

案例

环境准备：

DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
DROP TABLE IF EXISTS job;
DROP TABLE IF EXISTS salarygrade;
-- 部门表
CREATE TABLE dept (
  did INT PRIMARY KEY PRIMARY KEY, -- 部门id
  dname VARCHAR(50), -- 部门名称
  loc VARCHAR(50) -- 部门所在地
);
-- 职务表，职务名称，职务描述
CREATE TABLE job (
  id INT PRIMARY KEY,
  jname VARCHAR(20),
  description VARCHAR(50)
);
-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工id
  ename VARCHAR(50), -- 员工姓名
  job_id INT, -- 职务id
  mgr INT , -- 上级领导
  joindate DATE, -- 入职日期
  salary DECIMAL(7,2), -- 工资
  bonus DECIMAL(7,2), -- 奖金
  dept_id INT, -- 所在部门编号
  CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
  CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
-- 工资等级表
CREATE TABLE salarygrade (
  grade INT PRIMARY KEY,   -- 级别
  losalary INT,  -- 最低工资
  hisalary INT -- 最高工资
);
-- 添加4个部门
INSERT INTO dept(did,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');
-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');
-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);
-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

需求

查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述

/*
  分析：
    1. 员工编号，员工姓名，工资 信息在emp 员工表中
    2. 职务名称，职务描述 信息在 job 职务表中
    3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id
*/
-- 方式一 ：隐式内连接
SELECT
  emp.id,
  emp.ename,
  emp.salary,
  job.jname,
  job.description
FROM
  emp,
  job
WHERE
  emp.job_id = job.id;
-- 方式二 ：显式内连接
SELECT
  emp.id,
  emp.ename,
  emp.salary,
  job.jname,
  job.description
FROM
  emp
INNER JOIN job ON emp.job_id = job.id;

查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

/*
  分析：
    1. 员工编号，员工姓名，工资 信息在emp 员工表中
    2. 职务名称，职务描述 信息在 job 职务表中
    3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id
    4. 部门名称，部门位置 来自于 部门表 dept
    5. dept 和 emp 一对多关系 dept.id = emp.dept_id
*/
-- 方式一 ：隐式内连接
SELECT
  emp.id,
  emp.ename,
  emp.salary,
  job.jname,
  job.description,
  dept.dname,
  dept.loc
FROM
  emp,
  job,
  dept
WHERE
  emp.job_id = job.id
  and dept.id = emp.dept_id
;
-- 方式二 ：显式内连接
SELECT
  emp.id,
  emp.ename,
  emp.salary,
  job.jname,
  job.description,
  dept.dname,
  dept.loc
FROM
  emp
INNER JOIN job ON emp.job_id = job.id
INNER JOIN dept ON dept.id = emp.dept_id

查询员工姓名，工资，工资等级

/*
  分析：
    1. 员工姓名，工资 信息在emp 员工表中
    2. 工资等级 信息在 salarygrade 工资等级表中
    3. emp.salary >= salarygrade.losalary  and emp.salary <= salarygrade.hisalary
*/
SELECT
  emp.ename,
  emp.salary,
  t2.*
FROM
  emp,
  salarygrade t2
WHERE
  emp.salary >= t2.losalary
AND emp.salary <= t2.hisalary

查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

/*
  分析：
    1. 员工编号，员工姓名，工资 信息在emp 员工表中
    2. 职务名称，职务描述 信息在 job 职务表中
    3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id
    4. 部门名称，部门位置 来自于 部门表 dept
    5. dept 和 emp 一对多关系 dept.id = emp.dept_id
    6. 工资等级 信息在 salarygrade 工资等级表中
    7. emp.salary >= salarygrade.losalary  and emp.salary <= salarygrade.hisalary
*/
SELECT
  emp.id,
  emp.ename,
  emp.salary,
  job.jname,
  job.description,
  dept.dname,
  dept.loc,
  t2.grade
FROM
  emp
INNER JOIN job ON emp.job_id = job.id
INNER JOIN dept ON dept.id = emp.dept_id
INNER JOIN salarygrade t2 ON emp.salary BETWEEN t2.losalary and t2.hisalary;

查询出部门编号、部门名称、部门位置、部门人数

/*
  分析：
    1. 部门编号、部门名称、部门位置 来自于部门 dept 表
    2. 部门人数: 在emp表中 按照dept_id 进行分组，然后count(*)统计数量
    3. 使用子查询，让部门表和分组后的表进行内连接
*/
-- 根据部门id分组查询每一个部门id和员工数
select dept_id, count(*) from emp group by dept_id;
SELECT
  dept.id,
  dept.dname,
  dept.loc,
  t1.count
FROM
  dept,
  (
    SELECT
      dept_id,
      count(*) count
    FROM
      emp
    GROUP BY
      dept_id
  ) t1
WHERE
  dept.id = t1.dept_id

八、事务

8.1 概念

数据库的事务：是一种机制、一个操作序列，包含了数据库操作命令

事务：把所有命令作为一个整体一起向系统提交或撤销操作请求 [ 要么同时成功，要么同时失败]

事务：是一个不可分割的工作逻辑单元

8.2 语法

开启事务

START TRANSACTION;
或者  
BEGIN;

提交事务

commit;

回滚事务

rollback;

8.3代码

环境准备

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);

不加事务演示问题

-- 转账操作
-- 1. 查询李四账户金额是否大于500
-- 2. 李四账户 -500
UPDATE account set money = money - 500 where name = '李四';
出现异常了...  -- 此处不是注释，在整体执行时会出问题，后面的sql则不执行
-- 3. 张三账户 +500
UPDATE account set money = money + 500 where name = '张三';

整体执行结果肯定会出问题，我们查询账户表中数据，发现李四账户少了500。
添加事务sql如下：

-- 开启事务
BEGIN;
-- 转账操作
-- 1. 查询李四账户金额是否大于500
-- 2. 李四账户 -500
UPDATE account set money = money - 500 where name = '李四';
出现异常了...  -- 此处不是注释，在整体执行时会出问题，后面的sql则不执行
-- 3. 张三账户 +500
UPDATE account set money = money + 500 where name = '张三';
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;

上面sql中的执行成功进选择执行提交事务，而出现问题则执行回滚事务的语句。以后我们肯定不可能这样操作，而是在java中进行操作，在java中可以抓取异常，没出现异常提交事务，出现异常回滚事务。

8.4 四大特性

原子性（Atomicity）: 事务是不可分割的最小操作单位，要么同时成功，要么同时失败
一致性（Consistency） :事务完成时，必须使所有的数据都保持一致状态
隔离性（Isolation） :多个事务之间，操作的可见性
持久性（Durability） :事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

说明：

mysql中事务是自动提交的。

也就是说我们不添加事务执行sql语句，语句执行完毕会自动的提交事务。

可以通过下面语句查询默认提交方式：

SELECT @@autocommit;

查询到的结果是1 则表示自动提交，结果是0表示手动提交。当然也可以通过下面语句修改提交方式

set @@autocommit = 0;

数据库的使用（SQL）（下）

六、数据库设计

6.1 设计简介

6.2表关系

6.2.1一对多

6.2.2多对多

6.2.3一对一

6.3 数据库设计案例

七、多表查询

7.1 连接查询

7.1.1 内连接

7.1.2 外连接

7.2子查询

案例

八、事务

8.1 概念

8.2 语法

8.3代码

8.4 四大特性

热门文章

最新文章

相关课程

相关电子书

相关实验场景

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

数据库的使用（SQL）（下）

六、数据库设计

6.1 设计简介

6.2表关系

6.2.1一对多

6.2.2多对多

6.2.3一对一

6.3 数据库设计案例

七、多表查询

7.1 连接查询

7.1.1 内连接

7.1.2 外连接

7.2子查询

案例

八、事务

8.1 概念

8.2 语法

8.3代码

8.4 四大特性

热门文章

最新文章

相关课程

相关电子书

相关实验场景