Mysql简单的学习记录（下）

一、约束

1. 概念

   • 作用于列上的规则，用于限制加入表的数据
   • 保证数据库正确性、有效性、完整性

2. 约束的分类

   • 约束名称	描述	关键字
     非空	保证列中所有数据不为null	not null
     唯一	保证列中所有数据不相同	unique
     主键	主键是一行数据唯一标识，非空且唯一	primary key
     检查	保证列中数据的值满足某一要求	check
     默认	保存数据时，未指定采用默认值	default
     外键	让两个表建立连接，保证数据一致性和完整性	foreign key

   • MySql不支持检查约束

3. 案例

   • create table emp{
     	id int primary key auto_increment, #员工id, 主键且自增长
     	ename varchar(50) not null unique, #姓名，非空且唯一
     	joindate DATE NOT NULL, #入职日期，非空
     	sa1ary D0UBLE(7,2) NOT NULL, #工资，非空
     	bonus DOUBLE(7,2) DEFAULT 0 #奖金，如果没有奖金默认为0
     }
     #添加约束
     	alter table 表名 modify 字段名 数据类型 约束类型
     #删除约束
     	alter table 表名 modify 字段名 数据类型
     

4. 外键约束

   • 
     create table emp{
     	id int primary key auto_increment, #员工id, 主键且自增长
     	ename varchar(50) not null unique, #姓名，非空且唯一
     	joindate DATE NOT NULL, #入职日期，非空
     	sa1ary D0UBLE(7,2) NOT NULL, #工资，非空
     	bonus DOUBLE(7,2) DEFAULT 0， #奖金，如果没有奖金默认为0
     	[constraint] [外键名称] foreign key(外键字段名称) references(表名)
     }
     #删除外键
     alter table 表名 drop FOREIGN key 外键名称;
     #添加外键
     alter table 表名 add constraint 外键名称 foreign key(列名称) references(表名(列名))
     

二、数据库设计

1. 数据库设计简介

   1. 软件的研发步骤

      • 需求分析 -> 设计 -> 编码 -> 测试 -> 安装部署

      • 设计
        软件结构设计
        数据库设计
        接口设计
        过程设计

   2. 数据库设计概念

      • 表结构，表和表之间关联关系
      • 哪些表，表的字段，表和表关系

   3. 数据库设计的步骤

      • 需求分析
      • 逻辑分析（ER图）
      • 物理设计（逻辑设计转化为物理设计）
      • 维护设计（新表……）

2. 表关系之一对多（多对一）

   • 部门和员工的关系
   • 学生和班级的关系

3. 表关系之多对多

   • 商品和订单
   • 一般通过中间表实现，中间表包含两个外键，关联双方主键

4. 表关系之一对一

   • 用户和用户信息（常用和不常用）
   • 常用的信息放在一张表，不常用的放一张表
   • 设置外键为 unique

三、多表查询

1. 笛卡尔积的问题

2. 连接查询

   select * from 表1,表2
   #内连接，两个表交集
   select * from 表1,表2 where 条件 #隐式内连接
   select * from 表1 [inner] join 表2 on 条件 #显示内连接
   #左外练级,查询前一个表的所有数据
   select * from 表1 left [outer] join 表2 on 条件
   #右外连接,查询后一个表的所有数据
   select * from 表1 right [outer] join 表2 on 条件
   

3. 子查询

   #单行单列
   select * from 表1 where 字段名 = (子查询)
   #多行单列
   select * from 表1 where 字段名 in (子查询)
   #多行多列
   select * from (子查询) where 条件
   

4. 案例

   #1.查询所有员工信总。查询员工编号，员工姓名，工资，职务召称，职务描述
   select emp.id,emp.ename,emp.bonus,job.jname,job.description
   from emp,job
   where emp.job_id = job.id;
   
   select emp.id,emp.ename,emp.bonus,job.jname,job.description
   from emp 
   join job 
   on emp.job_id = job.id;
   
   #2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
   select emp.id,emp.ename,emp.bonus,job.jname,job.description,dept.dname,dept.loc
   from emp,job,dept
   where emp.job_id = job.id and emp.dept_id = dept.id; 
   
   #3.查询员工姓名，工资，工资等级
   select emp.ename,emp.salary,t.grade
   from emp,salarygrade as t
   where emp.salary between t.losalary and t.hisalary 
   
   #4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
   select emp.ename,emp.salary,job.jname,job.description,dept.dname,dept.loc,t.grade
   from emp,job,salarygrade as t
   where emp.job_id = job.id and emp.dept_id = dept.id and emp.salary between t.losalary and t.hisalary ;
   
   #5.查询出部门编号、部门名称、部门位置、部门人数
   select dept.id,dept.dname,dept.loc,count(*) as sumpeople
   from dept,
   	(	select dept_id,count(*) 
       	from emp 
       	group by demp_id
   	) as t
   where dept.id = t.dept_id
   

四、事务

1. 事务简介

   • 操作序列，一组数据库操作命令，是不可分割的工作逻辑单元，一组数据库命令要么同时成功要么同时失败

2. 事务操作

   • 开启事务（ start transaction\begin ） 回滚事务 ( rollback ) 提交事务 ( commit )

3. 事务的四大特征(ACID)

   • Atomicity：原子性
   • Consistency：一致性
   • Isolation：隔离性
   • Durability：持久性
   • MySql事务默认自动提交