数据库约束-设计-多表查询-事务

1约束的概念: 约束是作用于表中列上的规则,用于限制加入表的数据
            约束的存在保证了数据库中数据的正确性,有效性和完整性

2.约束的分类       (1)非空约束(关键字not null    ):保证列中所有数据不能有null值
                   (2)唯一约束(关键字 unique     ):保证类中所有数据各不相同
                   (3)主键约束(关键字primary key ):主键是一行数据的唯一标识,要求非空且唯一
                   (4)检查约束(关键字  check     ):保证列中的值满足某一条件
                   (5)默认约束(关键字 default    ):保存数据时,未指定值则采用默认值
                   (6)外键约束(关键字 foreign key):外键用来让两个表的数据之间建立连接,保证数据的一致性和完整性
Mysql不支持检查约束    

3.前五种约束的案例
员工表
create table emp(
1.主键数字型 利用自增
2字符串型 可以利用java中UUID生成不重复的字符串作为主键
id int  primary key  (auto increment 自增长),--员工id 主键约束 且自增长
ename varchar(50) not null unique ,--员工姓名,非空且唯一 
joindate date    ,--not null --入职日期 非空 
salary double(7,2) not null ,--工资 非空
bonus  double(7,2) default 0 ,-- 奖金 如果没有奖金 默认为0
);
 一个表中只能有一个主键
(1外键约束案例(外键限制了 主表的删除和修改!!限制了从表的添加和修改))
语法 create table 表名 (
列名 数据类型  约束
...................
[constraint][外键名称] foreign key (外键列名) references 主表(主表类名)
);

建完表后添加外键约束
aler   table 表名  add constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名)
删除约束
alter table 表名 drop foreign key 外键名称    

查询外键  show createtable 

<<<数据库设计>>>
<1概念>数据库设计就是根据业务系统的需求 结合我们所学的DBMS,为这个业务系统构造处最优的数据存储模型,
建立数据库中的表结构以及表与表之间的关联关系的过程(有哪些表,表里有什么列,表和表之间有什么关系)
<2步骤>
1需求分析(数据是什么,数据具有哪些属性,数据与属性特点是什么)
2逻辑分析(通过ER图对数据库进行逻辑建模,不需要考虑我们所选用的数据库管理系统)
3物理设计(根据数据库自身的特点把逻辑设计转换为物理设计)
4维护设计(1对新的需求进行建表;2表优化)

<表关系>
1一对多 例如部门和员工(一个部门对应多个员工,一个员工对应一个部门)
!!实现:在多的一方建立外键,指向一的一方主键

2多对多 例如商品和订单(一个商品包含多个订单,一个订单包含多个商品)
!!建立第三张中间表,中间表至少包含两个外键,分别关联两方主键

3一对一 例如人和身份证 (一个人对应一个身份证)(用户和用户详情)
!!一对一关系用于表拆分,将一个实体中经常使用的字段方一张表,不经常使用的字段放另一张表,用于提高查询性能
实现方式:在任意一方加入外键,关联另一方主键,并且设置外键为唯一(unique)

<<<<多表查询>>>>
select *from 表名1 表名2...where 条件;这样写会产生 笛卡尔积(有A,B两个集合 取A,B所有的组合情况)
消除无效数据
1连接查询
内连接(相当于查询A,B交集数据)(内链接查出来的数据)(如果一个表里没跟另一表有关联就会查不出来数据)
语法
隐式内连接(常用!!!!)
select 字段列表 from 表1,表2....where 条件;
显示内连接(缺点,必须一张表一张表查询)
select 字段列表 from 表1   inner(可以省略) join 表2 on 条件;

外连接(外连接效率低 会使索引失效)
左外连接:相当于查询A表所有数据和交集部分的数据
select 字段列表 from 表1 left outer(可省略) join 表2 on 条件

右外连接:相当于查询B表所有数据和交集部分的数据
select 字段列表 from 表1 right outer(可省略) join 表2 on 条件

2子查询:查询中嵌套查询,称为嵌套查询为子查询
子查询根据查询的结果不同 作用不同 
单行单列(作为条件值,使用= != > < 等进行条件判断)
select 字段列表 from 表 where 字段名=(子查询); 
多行单列(作为条件之 使用 in等关键字进行条件判断)
select 字段列表 from 表 where 字段名 in (子查询)
多行多列(作为虚拟表)
select 字段列表 from (子查询)where 条件;

事务      
数据库的事务是一种机制 一个操作序列 包含了一组数据库操作命令
事务把所有的命令作为一个整体一起向系统提交或者撤销操作请求,即这一组数据库命令要么同时成功,要么同时失败
事务是一个不可分割的工作逻辑单元
开启事务
start transaction 或者 begin;
提交事务
commit
回滚事务
rollback
{//事务的四大特征}
A:原子性 :事务是不可分割的最小操作单位,要么同时成功,要么同时失败
C:一致性 :事务完成时,必须使所有的数据都保持一致状态
I:隔离性 :多个事务之间,操作的可见性
D:持久性:事务一单提交或回滚,他对数据库中的数据的改变就是永久的
9查询事务的默认提交方式
select @@autocommit; (1默认提交自动 0手动提交) 
ORACEL 数据库 默认是手动提交的

select 
t2.*
count(t1.dept_id)
from
dept t2
left join
cmp t1
on t1.dept_id =t2.id
group by
t2.dname

多表查询诀窍   1.确定哪些表   2.确定条件    
遇见一张表跨行  运用左外 使用同一张表 
还是确定表  emp t1   emp t2 条件 t1.mgr =t2.id

select
t1.enmame,
t2.ename
from
emp t1
left join
emp t2
on t1.mgr =t2.id