当前位置：首页 > news >正文

Mysql基础 (二)

news 来源：原创 2024/5/20 3:48:02

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;