SQL编程语言2

1. 约束

约束：

• 约束是作用于表中列上的规则，用于限制加入表的数据
• 约束的存在保证了数据库中数据的准确性、有效性和完整性。

六种约束：

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

代码示例：

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

auto_increment：当列是数字类型且唯一约束修饰，通过auto_increment可以实现自增长

1.1 非空约束

语法
方式1：建表时添加非空约束
create table 表名(
列名 数据类型 not null,
…
);

方式2：建完表后添加非空约束
alter table 表名 modify 字段名 数据类型 not null;

删除约束：
alter table 表名 modify 字段名 数据类型

1.2 唯一约束

语法
方式1：建表时添加唯一约束
create table 表名(
列名 数据类型 unique auto_increment,-- auto_increment 当不指定值时自动增长
…
);

方式2：建完表后添唯一约束
alter table 表名 modify 字段名 数据类型 unique;

删除约束：
alter table 表名 drop index 字段名

1.3 主键约束

一张表只能有一个主键
语法
方式1：建表时添加唯一约束
create table 表名(
列名 数据类型 primary key auto_increment,-- auto_increment 当不指定值时自动增长
…
);

方式2：建完表后添唯一约束
alter table 表名 add primary key 字段名;

删除约束：
alter table 表名 drop primary key;

1.4 检查约束

1.5 默认约束

语法
方式1：建表时添加唯一约束
create table 表名(
列名 数据类型 default 默认值;
…
);

方式2：建完表后添唯一约束
alter table 表名 alter 列名 set default 默认值;

删除约束：
alter table 表名 alter 列名 drop default;

1.6 外键约束

外键约束：用于让两个表的数据建立连接，保证数据的一致性和完整性

语法
方式1：建表时添加外键约束
create table 表名（
列名 数据类型，
…
constraint 外键名称 foreign key (外键列名) references 主表(主表列名)
）

方式2：建完表后添加外键约束
alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称);

删除约束：
alter table 表名 drop foreign key 外键名称;

mysql> create table dept(
    -> id int primary key auto_increment,
    -> dep_name varchar(20),
    -> addr varchar(20)
    -> );

mysql> create table 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 dept(id)
    -> );

或者：

mysql> alter  table emp add constraint fk_emp_dept foreign key(dep_id) references dept(id);

删除外键

mysql> alter table emp drop foreign key fk_emp_dept;

外键约束要先创建主表以及添加主表数据

2.数据库设计

• 数据库设计就是根据业务系统的具体需求，为这个业务构建最优的数据存储模型
• 建立数据库中的表结构以及表与表之间的关联关系的过程

表关系

• 一对一
  如：用户和用户详情表
• 一对多（多对一）
  如：部门和员工
• 多对多
  如：商品和订单，一个商品对应多个订单，一个订单包含多个商品

2.1 表关系之一对多

• 实现方式：在多的一方建立外键，指向一的一方的主键

2.2 表关系之多对多

• 实现方式：建立第三张中间表，中间表至少包含两个外键，分别关联两房主键

建完表后添加外键
商品表：tb_goods
订单表：tb_order
中间表：tb_order_goods

mysql> alter table tb_order_goods add constraint fk_order_id foreign key(order_id) references tb_order(id);
mysql> alter table tb_order_goods add constraint fk_goods_id foreign key(goods_id) references tb_goods(id);

2.3 表关系之一对一

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

• 实现方式：在任意一方加入外键，关联另一方主键，并且设置外键为唯一（unique）

3.多表查询

mysql> select * from emp,dept;
对emp和dept两张表进行查询，会出现笛卡尔积的情况，及查询结果出现A、B集合的所有组合情况

• 多表查询：从多张表中查询数据
  1、连接查询
  内连接：相当于查询交集数据
  外连接：
  左外连接：相当于查询A表所有数据和交集部分数据
  右外连接：相当于查询B表所有数据和交集部分数据
  2、子查询

3.1 内连接查询

内连接查询语法：

• 隐式内连接：
  select 字段列表 from 表1,表2… where 条件;

mysql> select emp.name,emp.age,dept.dep_name from emp,dept where emp.dep_id=dept.id;

• 显示内连接
  select 字段列表 from 表1 [inner] join 表2 on 条件;

mysql> select * from emp inner join dept on emp.dep_id=dept.id;

3.2 外连接查询

外连接查询语法：

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

mysql> select * from emp left join dept on emp.dep_id=dept.id;

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

mysql> select * from emp right join dept on emp.dep_id=dept.id;

3.3 子查询

子查询概念：查询中嵌套查询，称嵌套查询为子查询
查询年龄比张三大的员工

首先查询张三年龄（张三24岁）
mysql> select age from emp where name=‘张三’;
然后查询年龄大于张三的员工信息
mysql> select * from emp where age>24;
合并之后可写为：mysql> select * from emp where age>(select age from emp where name=‘张三’);
select age from emp where name=‘张三’，该查询就成为子查询

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

• 单行单列：作为条件值，使用= 、！=、>、<等进行条件判断
  select 字段列表 from 表 where 字段名=（子查询）;
  查询年龄大于张三的员工信息：

select * from emp where age>(select age from emp where name='张三');

• 多行单列：作为条件值，使用in等关键字进行条件判断
  select 字段列表 from 表 where in (子查询);
  查询研发部和销售部所有员工信息：

mysql> select * from emp where dep_id in (select id from dept where dep_name='研发部' or dep_name='销售部');

• 多行多列：作为虚拟表
  select 字段列表 from （子查询）where 条件;
  查询入职日期是‘2021-11-11’之后的员工信息和部门信息

mysql> select * from (select * from emp where jion_date>'2021-11-11') emp,dept where emp.dep_id=dept_id;

4.事务

事务简介

• 数据库的事务是一种机制、一个操作序列，包含了一组数据库操作命令
• 事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，这一组数据库命令要么同时成功，要么同时失败
• 事务是一个不可分割的工作逻辑单元

– 开启事务
start transaction；
或者 begin；
– 提交事务
commit；
– 回滚事务
rollback；

事务的四大特征：

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