数据库设计的一般范式

/*
数据库设计:
    1、一对一
        添加唯一外键指向另一张表的主键
    2、一对多
        多的一方设置外键指向一的一方主键
    3、多对多
        借助第三张表来实现关联,第三张表分别设置2个字段设置外键分别指向两张表的主键,并且设置为联合主键
*/
/*
    旅游分类:categary
    旅游线路:line
    用户:user
    旅游分类(一) --- 旅游线路(多)
    用户(多) --- 旅游线路(多)
*/

-- 用户表:id, username, gender, age,主键id,自增长
CREATE TABLE tab_user (
    id INT PRIMARY KEY auto_increment,
    username VARCHAR ( 32 ),
    gender VARCHAR ( 1 ),
    age INT 
) CHARACTER SET utf8mb4;

-- 分类表:id, cat_name,主键id自增长
CREATE TABLE tab_category (
    id INT PRIMARY KEY auto_increment,
    cat_name VARCHAR ( 32 ) 
) CHARACTER SET utf8mb4;

-- 线路表:id, line_name, cat_id,外键cat_id约束分类表的主键id
CREATE TABLE tab_line (
    id INT PRIMARY KEY auto_increment,
    line_name VARCHAR ( 32 ),
    cat_id INT,
    FOREIGN KEY ( cat_id ) REFERENCES tab_category ( id ) 
) CHARACTER SET utf8mb4;

-- 收藏表:id, user_id, line_id,联合主键user_id与line_id,并且这两个字段分别为外键用户表主键、线路表主键
CREATE TABLE tab_favori (
    user_id INT,
    line_id INT,
    PRIMARY KEY ( user_id, line_id ),
    FOREIGN KEY ( user_id ) REFERENCES tab_user ( id ),
    FOREIGN KEY ( line_id ) REFERENCES tab_line ( id ) 
) CHARACTER SET utf8mb4;

/*
    数据库设计范式
    1、第一范式:1NF,每一列都是不可分割的原子项
        存在的问题:
            1、数据冗余;
            2、部分字段数据无法新增;
            3、部分字段无法新删除;
    2、第二范式:2NF,在1NF的的基础上,非码属性必须完全依赖于候选码(在1NF的基础上消除非主属性对主码的部分函数依赖)
        相关概念
            1、函数依赖:A-->B,如果通过A属性的值,可以确定唯一B属性值。则称B依赖于A
                学号-->姓名,(学号,课程名称)--> 分数
            2、完全函数依赖:A-->B,如果A是属性组,B的确定依赖于A中的所有属性值
                (学号,课程名称)--> 分数
            3、部分函数依赖:A-->B,如果A是属性组,B的确定只需要A中的部分属性值
                (学号,课程名称)--> 姓名
            4、传递函数依赖:A-->B-->C,如果A属性可以确定唯一属性B的值,再通过B属性,可以确定唯一C属性的值,则称C传递函数依赖A
                学号-->系名-->系主任
            5、在一个表中,如果一个属性或一个属性组,被其他所有属性完全依赖,则称这个属性(属性组)该表的码
    3、第三范式:3NF,在2NF的基础上,任何非主属性不依赖于其他非主属性(在2NF基础上消除传递依赖)

    总结:第一范式,字段不可分割;第二范式,要有主键,第三范式,字段内容不重复
*/

MySQL中多表查询的一般操作汇总

/****************************创建表、插入数据**********************************/

CREATE TABLE department (
    id INT PRIMARY KEY auto_increment,
    dept_name VARCHAR ( 32 ) 
) DEFAULT charset = utf8mb4;

CREATE TABLE employee (
    id INT PRIMARY KEY auto_increment,
    username VARCHAR ( 32 ),
    age INT,
    gender VARCHAR ( 2 ),
    dept_id INT,
FOREIGN KEY ( dept_id ) REFERENCES department ( id ) 
) DEFAULT charset = utf8mb4;

INSERT INTO department VALUES (1,'研发部'),(2,'销售部'),(3,'财务部');

SELECT*FROM department;

INSERT INTO employee VALUES (1,'张三',18,'男',1),(2,'李四',18,'男',1),(3,'王五',18,'男',2),(4,'赵六',18,'女',3),(5,'田七',18,'男',2);

/****************************多表查询**********************************/

SELECT*FROM employee,department;-- 直接查询多张表得到的结果是多表的笛卡尔积,即 M x N

/*
    多表查询的分类:
        1、内连接查询
        2、外连接查询
        3、子查询
*/
# 1、内连接查询
-- 隐式内连接查询,使用where条件来过滤不必要的数据
SELECT*FROM employee,department WHERE employee.dept_id=department.id;-- 通过where语句消除重复数据

-- 多表查询一般会指定字段
SELECT employee.id,employee.username,employee.gender,department.dept_name FROM employee,department WHERE employee.dept_id=department.id;
-- 显式内连接
SELECT*FROM employee INNER JOIN department WHERE employee.dept_id=department.id;

-- 可以为表定义别名更方便书写
SELECT t1.id,-- 员工表id
t1.username,-- 员工表姓名
t1.gender,-- 员工表性别
t2.dept_name-- 部门表名称
FROM employee t1,department t2 WHERE t1.dept_id=t2.id;

-- 外连接:左外右外
SELECT t1.*,t2.dept_name,t2.dept_loc FROM employee t1 LEFT OUTER JOIN department t2 ON t1.dept_id=t2.id;

-- 内连接
# 单行单列
SELECT*FROM employee WHERE mgr=(SELECT id FROM employee WHERE ename='刘明');
SELECT*FROM employee WHERE salary> (SELECT avg(salary) FROM employee);
# 多行单列
SELECT*FROM employee WHERE mgr IN (SELECT id FROM employee WHERE mgr=12);
# 多行多列,多行多列可等效于子查询结果为虚拟表
SELECT t1.*,t2.dept_name,t2.dept_loc FROM (SELECT*FROM employee WHERE salary>=10000) t1 JOIN department t2 WHERE t1.dept_id=t2.id;

/*** 多表查询练习 ***/
# 查询所有员工信息,编号、姓名、工资、职务名称、职务描述
SELECT t1.id,t1.ename,t1.salary,t2.jname,t2.description FROM employee t1,job t2 WHERE t1.job_id=t2.id;
SELECT t1.id,t1.ename,t1.salary,t2.jname,t2.description FROM employee t1 INNER JOIN job t2 WHERE t1.job_id=t2.id; 
SELECT t1.id,t1.ename,t1.salary,t2.jname,t2.description FROM employee t1 LEFT OUTER JOIN job t2 ON t1.job_id=t2.id;
# 查询员工编号、姓名、工资、职务名称、职务描述、部门名称、部门位置
-- 内连接隐式连接查询
SELECT
    t1.id,-- 员工编号
    t1.ename,-- 姓名
    t1.salary,-- 工资
    t2.jname,-- 职务名称
    t2.description,-- 职务描述
    t3.dept_name,-- 部门名称
    t3.dept_loc -- 部门位置

FROM
    employee t1,
    job t2,
    department t3 
WHERE
    t1.job_id = t2.id 
    AND t1.dept_id = t3.id;
-- 左外连接查询
SELECT
    t1.id,-- 员工编号
    t1.ename,-- 姓名
    t1.salary,-- 工资
    t2.jname,-- 职务名称
    t2.description,-- 职务描述
    t3.dept_name,-- 部门名称
    t3.dept_loc -- 部门位置

FROM
    employee t1
    LEFT JOIN job t2 ON t1.job_id = t2.id
    LEFT JOIN department t3 ON t1.dept_id = t3.id 
ORDER BY
    t1.id;
# 查询员工姓名、工资、工资等级
-- 隐式内连接查询
SELECT
    t1.id,
    t1.ename,
    t1.salary,
    t2.grade 
FROM
    employee t1,
    salary_grade t2 
WHERE
    t1.salary BETWEEN t2.losalary 
    AND t2.hisalary;
-- 左外连接查询
SELECT
    t1.id,
    t1.ename,
    t1.salary,
    t2.grade 
FROM
    employee t1
    LEFT JOIN salary_grade t2 ON t1.salary BETWEEN t2.losalary 
    AND t2.hisalary;

# 查询员工姓名、工资、职务名称、职务描述、部门名称、部门位置、工资等级
-- 内连接隐式查询
SELECT
    t1.ename 姓名,
    t1.salary 工资,
    t2.jname 职务名称,
    t2.description 职务描述,
    t3.dept_name 部门名称,
    t3.dept_loc 部门位置,
    t4.grade 工资等级
FROM
    employee t1,
    job t2,
    department t3,
    salary_grade t4 
WHERE
    t1.job_id = t2.id and
    t1.dept_id = t3.id and
    t1.salary BETWEEN t4.losalary 
    AND t4.hisalary;

-- 左外连接查询
SELECT
    t1.ename 姓名,
    t1.salary 工资,
    t2.jname 职务名称,
    t2.description 职务描述,
    t3.dept_name 部门名称,
    t3.dept_loc 部门位置,
    t4.grade 工资等级 
FROM
    employee t1
    LEFT JOIN job t2 ON t1.job_id = t2.id
    LEFT JOIN department t3 ON t1.dept_id = t3.id
    LEFT JOIN salary_grade t4 ON t1.salary BETWEEN t4.losalary 
    AND t4.hisalary;

# 查询部门编号、部门名称、部门位置、部门人数
-- 子查询
SELECT*,(SELECT COUNT(id) FROM employee WHERE employee.dept_id=t1.id) '部门人数' FROM department t1;

SELECT t1.*,t2.ecount '部门人数' FROM department t1,(SELECT dept_id,COUNT(id) ecount FROM employee GROUP BY dept_id) t2 WHERE t1.id=t2.dept_id;

# 查询所有员工及其直接上级的姓名,没有领导的员工也需要查询出来
-- 隐式内查询
SELECT t1.ename 姓名,t2.ename 上级 FROM employee t1,employee t2 WHERE t1.mgr=t2.id;
-- 左外连接查询
SELECT t1.ename 姓名,t2.ename 上级 FROM employee t1 LEFT OUTER JOIN employee t2 ON t1.mgr=t2.id;

MySQ中事务的基本概念

/*** 事务 ***/
# 如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败。
# 默认情况下单条sql语句都是自动提交事务
# 操作:
#      1、开启事务:start transaction
#      2、回滚:rollback
#      3、提交:commit
# MySQL事务默认的提交方式是自动提交,即每条SQL语句都是一个事务
# 查看提交方式 select @@autocommit
SELECT @@autocommit; 

# 事务四大特征
#      1、原子性:不可分割的最小单元,要么同时成功,要么同时失败
#      2、持久性:当事务提交或回滚后,数据库将会持久化地保存数据
#      3、隔离性:多个事务之间,相互独立。
#      4、一致性:事务操作的前后,数据总量不变

# 事务的隔离级别
# 概念:多个事务是隔离的,但多个事务操作的是同一批数据,则会出现问题,设置不同的隔离级别来解决这些问题;
# 问题:
#      1、脏读:一个事务读到了另一个事务还未提交的数据;
#      2、不可重复读(虚读):同一个事物,读取同一个数据,两次读取结果不一样;
#      3、幻读:一个事务操作了某个记录,另一个事务同样操作了这个记录,第一个事务查询不到自己的修改
# 隔离级别
#      1、read uncommitted:读未提交,存在问题123
#      2、read committed:读已提交,存在问题23
#      3、repeatable read:可重复读,存在问题3
#      4、serializable:串行化,没有问题,当有其他事务开启时,会阻塞等待其他事务提交后再执行
#      设置隔离级别越高,安全性越高,但性能越差,MySQL默认隔离级别是repeatable read,Oracle的默认级别是read commited
#  读取|修改隔离级别
#      SELECT @@tx_isolation;
#      SET GLOBAL TRANSACTION ISOLATION LEVEL READ xxx

-- 查看事务隔离级别
SELECT @@tx_isolation;

-- 修改事务隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 查看自动事务提交状态
SELECT @@autocommit;