¶ 二、数据库管理
¶ 2.1 外键（FOREIGN KEY）
MySQL 外键约束（FOREIGN KEY）用来在两个表的数据之间建立链接，它可以是一列或者多列。一个表可以有一个或多个外键。
外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键的值必须等于另一个表中主键的某个值。
外键是表的一个字段，不是本表的主键，但对应另一个表的主键。定义外键后，不允许删除另一个表中具有关联关系的行。
外键的主要作用是保持数据的一致性、完整性。例如，部门表 tb_dept 的主键是 id，在员工表 tb_emp5 中有一个键 deptId 与这个 id 关联。
主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表。
从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的表就是从表。
CREATE TABLE `student` (
`id` int(10) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`password` varchar(10) NOT NULL DEFAULT '123456' COMMENT '登录密码',
`sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` datetime DEFAULT NULL COMMENT '出生日期',
`gradeid` int(10) NOT NULL COMMENT '年级id',
`address` varchar(100) DEFAULT NULL COMMENT '家庭住址',
`email` varchar(30) DEFAULT NULL COMMENT '邮箱地址',
PRIMARY KEY (`id`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`) ;
-- 删除外键关系表 必须先解除外键关系才能删除
最佳实践
¶ 2.2 DML 数据操作语言
- insert
- update
- delete
¶ 2.3 添加 INSERT
-- INSERT INTO tablename(列名…) VALUES(列值);
INSERT INTO `grade`(`gradename`) VALUES('大四');
INSERT INTO `grade`(`gradename`) VALUES('大二'),('大三');
INSERT INTO `student`(`name`,`password`,`sex`) VALUES('张三','aaaaaa','女'),('李四','aaaaaa','女');
INSERT INTO `student` VALUES (5,'王五','aaaaaa','女','2000-01-01','1','太原','123@123.com');
¶ 2.4 修改 UPDATE
update 表名称 set 字段=vlaue
-- 修改id为1 的name属性为lxf
UPDATE `student` SET `name`='lxf' WHERE id=1;
-- 不加条件全部修改
UPDATE `student` SET `name` = 'lxf';
-- 修改多个属性用 ',' 隔开
UPDATE `student` SET `name`='lxf',`gradeid`='4' WHERE id=1;
-- 语法
-- UPDATE 表名 SET colnum_name=value,colnum_name=value[colnum_name=value……] WHERE [条件];
条件 WHERE
| 操作符 | 含义 | 范围 | 结果 |
|---|---|---|---|
| = | |||
| <>或!= | |||
| > | |||
| < | |||
| >= | |||
| <= | |||
| BETWEEN…AND… | 在某个范围 | ||
| AND | |||
| OR |
¶ 2.5 删除 DELETE
delete from 表名 WHERE [条件]
-- 清空表内容
DELETE FROM `student`
TRUNCATE `student`
-- 删除指定条件的行
DELETE FROM `student` WHERE id=1
CREATE TABLE `test` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`coll` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
INSERT INTO `test`(`coll`) VALUES ('1'),('2'),('3')
-- 不会影响自增
DELETE FROM `test`
-- 自增归零
TRUNCATE `test`
¶ 2.6 查询SELECT
DQL：数据库查询语言
SELECT * FROM table
- SELECT关键字后的“*”，代表查询数据表中的所有（字段）的内容，同时也可以指定要查询的字段名列表。
- FROM关键字后的table，指明要从哪个表查询数据。
- SELECT语句与其他SQL语句一样，不区分大小写，例如select和SELECT是相同的，但是字段名、表名或者值需要区分大小写。
SELECT 字段名1 AS 新列名1,字段名2 AS 新列名2,...FROM table
SELECT CONCAT(字段名1,字段名2,...)FROM table
AS 字段名称 as 别名 表名 as 别名
去重 distinct
SELECT DISTINCT * FROM table
数据库的列表达式
SELECT VERSION() -- 查询系统版本
SELECT 100*3-1 AS result -- 计算表达式结果
SELECT @@auto_increment_increment -- 查询自增的步长
-- 学员成绩+1分
SELECT `StudenNo`,`StudentResult`+1 AS '加1分' FROM result
where
作用：检索数据中符合条件的值
| 运算符 | 描述 |
|---|---|
| & | 按位和 |
| > | 大于运算符 |
| >> | 右移位 |
| >= | 大于或等于运算符 |
| < | 小于操作员 |
| <>,!= | 不相等运算符 |
| << | 左移 |
| <= | 小于或等于运算符 |
| ADN，&& | 逻辑和 |
模糊查询
| 运算符 | 语法 | 描述 |
|---|---|---|
| IS NULL | a is null | 如果操作符为null，结果为真 |
| IS NOT NULL | a is not null | 如果操作符不为null，结果为真 |
| BETWEEN | a between b and c | 若a在b、c之间，结果为真 |
| like | a like b | sql匹配 如果a匹配b没结果为真 |
| in | a in（a1,a2,a3……） | 如果a在a1,a2,a3……中 结果为真 |
select * from user where realname like '%%'
SELECT各子句的执行顺序
上面了解了SELECT语句的主要子句，当SELECT语句被执行时，其子句会按照固定的先后顺序执行。现假设SELECT语句带有所有的子句，执行顺序如下：
- SELECT子句
- FROM子句
- WHERE子句
- GROUP BY子句
- HAVING子句
- ORDER BY子句
- LIMIT子句
¶ 2.7 联表查询
join
CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`user_name` varchar(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `user_info` (
`user_id` int(11) NOT NULL,
`user_name` varchar(64) NOT NULL,
PRIMARY KEY (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into user(`user_name`) values ('a');
insert into user(`user_name`) values ('b');
insert into user(`user_name`) values ('c');
insert into user(`user_name`) values ('d');
insert into user(`user_name`) values ('e');
insert into user_info(`user_id`,`user_name`)values(1,'a');
insert into user_info(`user_id`,`user_name`)values(2,'b');
insert into user_info(`user_id`,`user_name`)values(3,'c');
insert into user_info(`user_id`,`user_name`)values(7,'f');
insert into user_info(`user_id`,`user_name`)values(6,'h');
select * from user a left join user_info b on a.id=b.user_id;
select * from user a right join user_info b on a.id=b.user_id;
select * from user a join user_info b on a.id=b.user_id;
inner join 返回两个表中都有的数据
left join 会从左表中返回所有的值，即使右表中没有匹配
right join 会从右表中返回所有的值，即使左表中没有匹配
自连接
create table tdb_cates(
id smallint PRIMARY KEY auto_increment,
cate_name varchar(20) not null,
parent_id smallint not null
)ENGINE=INNODB DEFAULT CHARSET=utf8
insert into tdb_cates(cate_name, parent_id) values('数码产品', 0);
insert into tdb_cates(cate_name, parent_id) values('家用产品', 0);
insert into tdb_cates(cate_name, parent_id) values('笔记本', 1);
insert into tdb_cates(cate_name, parent_id) values('智能手机', 1);
insert into tdb_cates(cate_name, parent_id) values('电器', 2);
insert into tdb_cates(cate_name, parent_id) values('家具', 2);
insert into tdb_cates(cate_name, parent_id) values('冰箱', 5);
insert into tdb_cates(cate_name, parent_id) values('洗衣机', 5);
insert into tdb_cates(cate_name, parent_id) values('汽车品牌', 0);
insert into tdb_cates(cate_name, parent_id) values('别克', 9);
insert into tdb_cates(cate_name, parent_id) values('宝马', 9);
insert into tdb_cates(cate_name, parent_id) values('雪佛兰', 9);
insert into tdb_cates(cate_name, parent_id) values('家纺', 0);
SELECT a.cate_name AS '父类',b.cate_name AS '子类' FROM tdb_cates AS a ,tdb_cates as b
WHERE a.id = b.parent_id
+----------+----------+
| 父类 | 子类 |
+----------+----------+
| 数码产品 | 笔记本 |
| 数码产品 | 智能手机 |
| 家用产品 | 电器 |
| 家用产品 | 家具 |
| 电器 | 冰箱 |
| 电器 | 洗衣机 |
| 汽车品牌 | 别克 |
| 汽车品牌 | 宝马 |
| 汽车品牌 | 雪佛兰 |
+----------+----------+
SELECT a.id,a.cate_name AS '父类',b.cate_name AS '子类' FROM tdb_cates AS a LEFT JOIN tdb_cates as b
on a.id = b.parent_id;
+----+----------+----------+
| id | 父类 | 子类 |
+----+----------+----------+
| 1 | 数码产品 | 笔记本 |
| 1 | 数码产品 | 智能手机 |
| 2 | 家用产品 | 电器 |
| 2 | 家用产品 | 家具 |
| 5 | 电器 | 冰箱 |
| 5 | 电器 | 洗衣机 |
| 9 | 汽车品牌 | 别克 |
| 9 | 汽车品牌 | 宝马 |
| 9 | 汽车品牌 | 雪佛兰 |
| 3 | 笔记本 | NULL |
| 4 | 智能手机 | NULL |
| 6 | 家具 | NULL |
| 7 | 冰箱 | NULL |
| 8 | 洗衣机 | NULL |
| 10 | 别克 | NULL |
| 11 | 宝马 | NULL |
| 12 | 雪佛兰 | NULL |
| 13 | 家纺 | NULL |
+----+----------+----------+
¶ 2.8 分页
排序
CREATE TABLE `test1` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`date_time` datetime NOT NULL,
`status` int(5) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
INSERT INTO `test1` VALUES
(NULL, '测试1', '2018-03-05 11:09:00', 1),(NULL, '测试2', '2018-03-06 11:09:00', 1),(NULL, 'abc', '2018-03-07 11:09:00', 1),
(NULL, 'def', '2018-04-08 11:09:00', 2),(NULL, '李某某', '2018-04-17 11:09:00', 1),(NULL, '饭某某', '2018-04-20 13:09:00', 2),
(NULL, '赵', '2018-04-20 01:09:00', 4),(NULL, '倩', '2018-04-28 11:09:00', 2),(NULL, 'andy', '2018-04-30 11:09:00', 1),
(NULL, 'tony', '2018-05-08 11:09:00', 4),(NULL, 'tom', '2018-05-07 11:09:00', 3),(NULL, 'bill', '2018-05-18 11:09:00', 3),
(NULL, 'james', '2018-06-07 11:09:00', 4),(NULL, 'anthony', '2018-06-18 11:09:00', 2),(NULL, '盖茨', '2018-04-21 11:09:00', 1),
(NULL, '部长', '2018-04-24 11:09:00', 4),(NULL, '李总', '2018-04-20 11:09:00', 5),(NULL, '张总', '2018-04-29 11:09:00', 2),
(NULL, '王总', '2018-04-19 11:09:00', 3),(NULL, '唐总', '2018-05-01 11:09:00', 2);
-- 单列排序 ASC 默认升序，降序后面接"DESC"即可。
SELECT * FROM test1 ORDER BY date_time
-- 多列排序 首先按`status`字段排序，若`status`相等，则按data_time排序。
SELECT * FROM test1 ORDER BY `status`, date_time DESC
-- 自定义排序 使用"FIELD()"函数，可指定顺序。
SELECT * FROM test1 ORDER BY FIELD(`status`, 3, 2, 4, 1, 5), date_time DESC
分页
select * from dept order by deptno desc limit 3,3;
-- m 起始值 n页面数据
select * from dept order by deptno desc limit m,n;
¶ 2.9 子查询
子查询（Sub Query）或者说内查询（Inner Query），也可以称作嵌套查询（Nested Query），是一种嵌套在其他 SQL 查询的 WHERE 子句中的查询。
子查询用于为主查询返回其所需数据，或者对检索数据进行进一步的限制。
子查询可以在 SELECT、INSERT、UPDATE 和 DELETE 语句中，同 =、<、>、>=、<=、IN、BETWEEN 等运算符一起使用。
使用子查询必须遵循以下几个规则：
子查询必须括在圆括号中。
子查询的 SELECT 子句中只能有一个列，除非主查询中有多个列，用于与子查询选中的列相比较。
子查询不能使用 ORDER BY，不过主查询可以。在子查询中，GROUP BY 可以起到同 ORDER BY 相同的作用。
返回多行数据的子查询只能同多值操作符一起使用，比如 IN 操作符。
SELECT 列表中不能包含任何对 BLOB、ARRAY、CLOB 或者 NCLOB 类型值的引用。
子查询不能直接用在聚合函数中
BETWEEN 操作符不能同子查询一起使用，但是 BETWEEN 操作符可以用在子查询中。
¶ 2.10 分组过滤
