1 约束
1.1 约束的概念
约束是作用表中列上的规则,用于限制加入表中的数据。
约束的目的是保证插入的数据的有效性,完整性和正确性。
1.2 约束的种类
约束名称 | 描述 | 关键字 |
---|---|---|
非空约束 | 保证列中所有数据不能有NULL值 | NOT NULL |
唯一约束 | 保证列中所有数据各不相同 | UNIQUE |
主键约束 | 主键是一行数据的唯一标识,要求非空且唯一 | PRIMARY KEY |
检查约束 | 保证列中的值满足唯一条件 | CHECK |
默认约束 | 保存数据时,未指定值采用默认值 | DEFAULT |
外键约束 | 外键用来让两个表的数据之间简历链接,保证数据的一致性和完整性 | FOREIGN KEY |
1.3 创建数据库时添加约束
mysql> create table emp(
mysql> id int primary key auto_increment,
mysql> ename varchar(50) not null unique,
mysql> joindate date not null,
mysql> salary double(7, 2) not null,
mysql> bonus double(7, 2)default 0
mysql> );
Query OK, 0 rows affected (0.06 sec)
mysql> desc emp;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| ename | varchar(50) | NO | UNI | NULL | |
| joindate | date | NO | | NULL | |
| salary | double(7,2) | NO | | NULL | |
| bonus | double(7,2) | YES | | 0.00 | |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
1.4 非空约束
mysql> insert into emp(id, ename, joindate, salary, bonus) values(2, null, '1991-09-10', 6000, 2000);
ERROR 1048 (23000): Column 'ename' cannot be null
1.5 建表后删除/添加约束
mysql> desc emp;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| ename | varchar(50) | NO | UNI | NULL | |
| joindate | date | NO | | NULL | |
| salary | double(7,2) | NO | | NULL | |
| bonus | double(7,2) | YES | | 0.00 | |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
-- 1 建完表后删除约束
mysql> alter table emp modify joindate date;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| ename | varchar(50) | NO | UNI | NULL | |
| joindate | date | YES | | NULL | |
| salary | double(7,2) | NO | | NULL | |
| bonus | double(7,2) | YES | | 0.00 | |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
-- 2 添加约束
mysql> alter table emp modify joindate date not null;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
1.6 唯一约束
mysql> desc emp;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| ename | varchar(50) | NO | UNI | NULL | |
| joindate | date | NO | | NULL | |
| salary | double(7,2) | NO | | NULL | |
| bonus | double(7,2) | YES | | 0.00 | |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> -- 删除约束
mysql> alter table emp drop index ename;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| ename | varchar(50) | NO | | NULL | |
| joindate | date | NO | | NULL | |
| salary | double(7,2) | NO | | NULL | |
| bonus | double(7,2) | YES | | 0.00 | |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> -- 添加唯一约束
mysql> alter table emp modify ename varchar(50) unique;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| ename | varchar(50) | YES | UNI | NULL | |
| joindate | date | NO | | NULL | |
| salary | double(7,2) | NO | | NULL | |
| bonus | double(7,2) | YES | | 0.00 | |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
1.7 主键约束
上面建表语句中的id即为主键约束
id int primary key auto_increment,
mysql> desc emp;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| ename | varchar(50) | YES | UNI | NULL | |
| joindate | date | NO | | NULL | |
| salary | double(7,2) | NO | | NULL | |
| bonus | double(7,2) | YES | | 0.00 | |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
-- 1 去除唯一约束
mysql> alter table emp drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> -- 报错: 不能直接去掉主键约束 需要先去除自增长约束才可以
mysql> alter table emp modify id int;
Query OK, 4 rows affected (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| ename | varchar(50) | YES | UNI | NULL | |
| joindate | date | NO | | NULL | |
| salary | double(7,2) | NO | | NULL | |
| bonus | double(7,2) | YES | | 0.00 | |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> alter table emp drop primary key;
Query OK, 4 rows affected (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| ename | varchar(50) | YES | UNI | NULL | |
| joindate | date | NO | | NULL | |
| salary | double(7,2) | NO | | NULL | |
| bonus | double(7,2) | YES | | 0.00 | |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
-- 2 添加唯一约束
mysql> alter table emp add primary key(id);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| ename | varchar(50) | YES | UNI | NULL | |
| joindate | date | NO | | NULL | |
| salary | double(7,2) | NO | | NULL | |
| bonus | double(7,2) | YES | | 0.00 | |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
-- 设置自增长
mysql> alter table emp modify id int(11) auto_increment;
Query OK, 4 rows affected (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> desc emp;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| ename | varchar(50) | YES | UNI | NULL | |
| joindate | date | NO | | NULL | |
| salary | double(7,2) | NO | | NULL | |
| bonus | double(7,2) | YES | | 0.00 | |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
1.8 默认约束
1 创建表时给日期列添加默认值
mysql> create table product(
-> id int primary key,
-> create_date date default '1990-03-20'
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> desc product;
+-------------+---------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+------------+-------+
| id | int(11) | NO | PRI | NULL | |
| create_date | date | YES | | 1990-03-20 | |
+-------------+---------+------+-----+------------+-------+
2 rows in set (0.00 sec)
2 去掉默认值
mysql> alter table product alter create_date drop default;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc product;
+-------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| create_date | date | YES | | NULL | |
+-------------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
3 日期添加默认值
mysql> alter table product alter create_date set default '1990-03-20';
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc product;
+-------------+---------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+------------+-------+
| id | int(11) | NO | PRI | NULL | |
| create_date | date | YES | | 1990-03-20 | |
+-------------+---------+------+-----+------------+-------+
2 rows in set (0.00 sec)
1.9 外键约束
-- 创建老师表
mysql> create table tercher(
-> id int primary key auto_crament,
-> name varchar(50),
-> age int
-> );
-- 查看老师表
mysql> desc teacher;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
-- 创建班级表
mysql> create table class(
-> name varchar(10),
-> t_id int,
-> );
-- 查看班级表
mysql> desc class;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(10) | YES | | NULL | |
| t_id | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
-- 老师表插入数据
mysql> insert into teacher values
-> (null, '张老师', 28),
-> (null, '王老师', 38),
-> (null, '李老师', 24);
-- 学生表插入数据
mysql> insert into class values
-> ('三年一班', 1),
-> ('三年二班', 2),
-> ('三年三班', 3);
-- 班级表关联老师表
mysql> alter table class add constraint fk_class_teacher foreign key(t_id) references teacher(id);
Query OK, 3 rows affected (0.07 sec)
Records: 3 Duplicates: 0 Warnings: 0
-- 尝试删除老师表中内容 报错
mysql> delete from teacher where id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db1`.`class`, CONSTRAINT `fk_class_teacher` FOREIGN KEY (`t_id`) REFERENCES `teacher` (`id`))
2 数据库设计
2.1 表关系之一对多
示例同上面的外键约束
2.2 表关系之多对多
通过创建第三张表来实现多对多关系
-- 1 创建订单表
mysql> create table tb_order(
-> id int primary key auto_increment,
-> payment double(10,2),
-> payment_type tinyint,
-> status tinyint
-> );
Query OK, 0 rows affected (0.06 sec)
-- 2 创建商品表
mysql> create table tb_goods(
-> id int primary key auto_increment,
-> titlee varchar(100),
-> price double(10, 2)
-> );
Query OK, 0 rows affected (0.06 sec)
-- 3 创建关联表
mysql> create table tb_order_goods(
-> id int primary key auto_increment,
-> order_id int,
-> goods_id int,
-> count int
-> );
Query OK, 0 rows affected (0.05 sec)
-- 4 添加外键
mysql> alter table tb_order_goods add constraint fk_order_id foreign key(order_id) references tb_order(id);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table tb_order_goods add constraint fk_goods_id foreign key(goods_id) references tb_goods(id);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
查看关系
2.3 表关系之一对一
-- tb_user_desc表
mysql> create table tb_user_desc (
-> id int primary key auto_increment,
-> city varchar(20),
-> edu varchar(10),
-> income int
-> );
Query OK, 0 rows affected (0.05 sec)
-- tb_user表
mysql> create table tb_user(
-> id int primary key auto_increment,
-> photo varchar(100),
-> nickname varchar(40),
-> desc_id int unique,
-> constraint fk_user_desc foreign key(desc_id) references tb_user_desc(id)
-> );
Query OK, 0 rows affected (0.05 sec)
查看关系
3 多表查询
3.1 内连接
查询两张表的交集
首先创建测试数据
-- 创建部门表
CREATE TABLE dept (
did INT PRIMARY KEY auto_increment,
dname VARCHAR ( 20 )
);
-- 创建员工表
CREATE TABLE emp (
id INT PRIMARY KEY auto_increment,
NAME VARCHAR ( 10 ),
gender CHAR ( 1 ),-- 性别
salary DOUBLE,
join_date DATE,
dep_id INT,
FOREIGN KEY ( dep_id ) REFERENCES dept ( did )
);
-- 添加部门数据
INSERT INTO dept (did, dname) VALUES(1, '研发部'), (2, '市场部'), (3, '财务部'), (4, '销售部');
-- 添加员工数据
INSERT INTO emp(NAME, gender, salary, join_date, dep_id) VALUES
('韦德', '男', 3500, '1985-01-25', 1),
('哈登', '男', 4500, '1991-04-12', 3),
('麦基', '男', 8200, '1985-11-15', 2),
('詹娜', '女', 7400, '1985-05-25', 3),
('科勒', '女', 6400, '1985-12-11', 4),
('库里', '男', 2000, '1985-08-04', 2),
('伯德', '男', 9500, '1962-07-06', null);
3.1.1 显示内连接
mysql> select emp.NAME, emp.gender, dept.dname from emp inner join dept on emp.dep_id = dept.did;
+--------+--------+-----------+
| NAME | gender | dname |
+--------+--------+-----------+
| 韦德 | 男 | 研发部 |
| 哈登 | 男 | 财务部 |
| 麦基 | 男 | 市场部 |
| 詹娜 | 女 | 财务部 |
| 科勒 | 女 | 销售部 |
| 库里 | 男 | 市场部 |
+--------+--------+-----------+
6 rows in set (0.00 sec)
此处的inner可以省略
3.1.2 隐式内连接
-- 去掉无效数据, 查询两张表的交集
mysql> SELECT
-> *
-> FROM
-> emp,
-> dept
-> WHERE
-> emp.dep_id = dept.did;
+----+--------+--------+--------+------------+--------+-----+-----------+
| id | NAME | gender | salary | join_date | dep_id | did | dname |
+----+--------+--------+--------+------------+--------+-----+-----------+
| 22 | 韦德 | 男 | 3500 | 1985-01-25 | 1 | 1 | 研发部 |
| 23 | 哈登 | 男 | 4500 | 1991-04-12 | 3 | 3 | 财务部 |
| 24 | 麦基 | 男 | 8200 | 1985-11-15 | 2 | 2 | 市场部 |
| 25 | 詹娜 | 女 | 7400 | 1985-05-25 | 3 | 3 | 财务部 |
| 26 | 科勒 | 女 | 6400 | 1985-12-11 | 4 | 4 | 销售部 |
| 27 | 库里 | 男 | 2000 | 1985-08-04 | 2 | 2 | 市场部 |
+----+--------+--------+--------+------------+--------+-----+-----------+
6 rows in set (0.00 sec)
-- 只查看姓名,性别和部门名称
mysql> SELECT
-> emp.NAME,
-> emp.gender,
-> dept.dname
-> FROM
-> emp,
-> dept
-> WHERE
-> emp.dep_id = dept.did;
+--------+--------+-----------+
| NAME | gender | dname |
+--------+--------+-----------+
| 韦德 | 男 | 研发部 |
| 哈登 | 男 | 财务部 |
| 麦基 | 男 | 市场部 |
| 詹娜 | 女 | 财务部 |
| 科勒 | 女 | 销售部 |
| 库里 | 男 | 市场部 |
+--------+--------+-----------+
3.2 外连接
查出两张表能关联的数据
3.2.1 左外连接
mysql> select emp.NAME, emp.gender,dept.did, dept.dname from emp left join dept on emp.dep_id = dept.did;
+--------+--------+------+-----------+
| NAME | gender | did | dname |
+--------+--------+------+-----------+
| 韦德 | 男 | 1 | 研发部 |
| 麦基 | 男 | 2 | 市场部 |
| 库里 | 男 | 2 | 市场部 |
| 哈登 | 男 | 3 | 财务部 |
| 詹娜 | 女 | 3 | 财务部 |
| 科勒 | 女 | 4 | 销售部 |
| 伯德 | 男 | NULL | NULL |
+--------+--------+------+-----------+
7 rows in set (0.00 sec)
3.2.2 右外连接
mysql> select emp.NAME, emp.gender, dept.did, dept.dname from emp right join dept on emp.dep_id = dept.did;
+--------+--------+-----+-----------+
| NAME | gender | did | dname |
+--------+--------+-----+-----------+
| 韦德 | 男 | 1 | 研发部 |
| 哈登 | 男 | 3 | 财务部 |
| 麦基 | 男 | 2 | 市场部 |
| 詹娜 | 女 | 3 | 财务部 |
| 科勒 | 女 | 4 | 销售部 |
| 库里 | 男 | 2 | 市场部 |
+--------+--------+-----+-----------+
3.3 子查询
案例1:
1 查询詹娜的工资
mysql> select salary from emp where name = '詹娜';
+--------+
| salary |
+--------+
| 7400 |
+--------+
1 row in set (0.00 sec)
2 查询工资高于’詹娜’的员工信息
mysql> select * from emp where salary > 7400;
+----+--------+--------+--------+------------+--------+
| id | NAME | gender | salary | join_date | dep_id |
+----+--------+--------+--------+------------+--------+
| 24 | 麦基 | 男 | 8200 | 1985-11-15 | 2 |
| 28 | 伯德 | 男 | 9500 | 1962-07-06 | NULL |
+----+--------+--------+--------+------------+--------+
2 rows in set (0.00 sec)
3 嵌套查询
mysql> select * from emp where salary > (select salary from emp where name = '詹娜');
+----+--------+--------+--------+------------+--------+
| id | NAME | gender | salary | join_date | dep_id |
+----+--------+--------+--------+------------+--------+
| 24 | 麦基 | 男 | 8200 | 1985-11-15 | 2 |
| 28 | 伯德 | 男 | 9500 | 1962-07-06 | NULL |
+----+--------+--------+--------+------------+--------+
2 rows in set (0.00 sec)
案例2:
1 查询两个部门的did
mysql> select did from dept where dname = '财务部' or dname = '市场部';
+-----+
| did |
+-----+
| 2 |
| 3 |
+-----+
2 查询两个部门的员工
mysql> select * from emp where dep_id in ( select did from dept where dname = '财务部' or dname = '市场部');
+----+--------+--------+--------+------------+--------+
| id | NAME | gender | salary | join_date | dep_id |
+----+--------+--------+--------+------------+--------+
| 24 | 麦基 | 男 | 8200 | 1985-11-15 | 2 |
| 27 | 库里 | 男 | 2000 | 1985-08-04 | 2 |
| 23 | 哈登 | 男 | 4500 | 1991-04-12 | 3 |
| 25 | 詹娜 | 女 | 7400 | 1985-05-25 | 3 |
+----+--------+--------+--------+------------+--------+
4 rows in set (0.00 sec)
案例3:
查询入职日期在’1985-06-30’后的所有员工
mysql> select t1.NAME, t1.join_date,dept.did from (select * from emp where join_date > '1985-06-30')t1, dept where dept.did = t1.dep_id;
+--------+------------+-----+
| NAME | join_date | did |
+--------+------------+-----+
| 哈登 | 1991-04-12 | 3 |
| 麦基 | 1985-11-15 | 2 |
| 科勒 | 1985-12-11 | 4 |
| 库里 | 1985-08-04 | 2 |
+--------+------------+-----+
4 rows in set (0.00 sec)
4 事务
1 创建测试数据
mysql> create table account(
-> id int primary key auto_increment,
-> name varchar(10),
-> money double(10, 2)
-> );
Query OK, 0 rows affected (0.05 sec)
2 插入数据
mysql> insert into account(name, money) values('张三', 1000),('李四', 1000);
先开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
从张三账户中转出500
mysql> update account set money = money - 500 where name = '张三';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from account;
+----+--------+---------+
| id | name | money |
+----+--------+---------+
| 1 | 张三 | 500.00 |
| 2 | 李四 | 1000.00 |
+----+--------+---------+
2 rows in set (0.00 sec)
此时发现错误 回滚
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from account;
+----+--------+---------+
| id | name | money |
+----+--------+---------+
| 1 | 张三 | 1000.00 |
| 2 | 李四 | 1000.00 |
+----+--------+---------+
2 rows in set (0.00 sec)
正常情况下的流程是多次操作都没有错误时,使用commit提交事务
mysql> begin -- 开启事务
-> update account set money = money - 500 where name = '李四'
-> update account set money = money + 500 where name = '张三'
-> commit;