狂神Java学习笔记————MySQL（二）————外键和数据库数据的增删改查

外键

• 外键可以理解为两个表在现实世界中的一种联系在数据库中的体现。
• 比如：
  • 学生信息表和班级表存在联系：一个学生必须且只能在一个存在的班级，一个班级可以有很多学生（一对多）
  • 教师信息表和班级表存在联系：老师必须教的是存在的班级，一个班级对应多个老师，一个老师也可能对应多个班级（多对多）

---

以下方式一、二为物理外键，即数据库级别的外键，并不推荐使用！

• 外键定义：
• 方式一：创建表时添加约束

-- 主表班级
CREATE TABLE IF NOT EXISTS `class`(
	`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '班级id',
	`name` VARCHAR(20) NOT NULL COMMENT '班级名称',
	PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET=utf8; 
-- 从表学生
CREATE TABLE IF NOT EXISTS `students`( 
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号', 
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名', 
	`password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码', 
	`sex` VARCHAR(2) NOT NULL COMMENT '性别', 
	`birthday` DATE DEFAULT NULL COMMENT '出生日期',
	`classid` INT(10) NOT NULL COMMENT '学生班级',
	-- 创建外键
	PRIMARY KEY (`id`),
	KEY `FK_gradeid`(`id`),
	CONSTRAINT `FK_gradeid` FOREIGN KEY (`id`) REFERENCES `class`(`id`)
)ENGINE = INNODB DEFAULT CHARSET=utf8; 

• 方式二：在已经创建好的表中添加

/*ALTER TABLE `从表名`
ADD CONSTRAINT `约束名` FOREIGN KEY(`从表字段`) REFERENCES `主表名`(`主表字段`);*/
ALTER TABLE `student`
ADD CONSTRAINT `FK_classid` FOREIGN KEY(`classid`) REFERENCES `class`(`id`);

• 最佳实践
  数据库只单纯的表用来存储数据，只有行（数据）和列（字段）
  要使用多张表的数据，想使用外键的逻辑，在程序中实现

---

DML语言

• DML（Data Manipulation Language）数据库操作语言
  数据库意义：数据存储，数据管理
• 插入 insert

-- 插入语句
-- insert into 表名([字段1，字段2，字段3])values(值1，值2，值3);
INSERT INTO `class` (`id`, `name`) VALUES (NULL, '初一');
-- 也可以省略字段名，但此时必须包含所有字段的值
-- 自增可用null
INSERT INTO `class`  VALUES (NULL,'高三');
-- 添加多组值
INSERT INTO `class` (`name`) VALUES ('大一'),('大二')('大三');

• 注意：
  • 字段之间、值之间用英文逗号隔开
  • 'values’两边都要加括号，否则报错
  • 字段可以部分或全部省略但，若部分省略，值需要和前面字段一一对应，若全部省略则等效于全部字段按顺序排列
  • 可以同时插入多条数据，每一个（）内都是一条数据，用逗号隔开即可
• 修改 update

-- UPDATE `表名` SET `字段名`=值 WHERE 条件（可选）;
-- 修改id为1的学员名字
UPDATE `students` SET `name`='hyx' WHERE id=1;
-- 不指定条件的情况，会改动所有行
UPDATE `students` SET `name`='uzi' ;
-- 修改多个属性,用逗号隔开即可
UPDATE `students` SET `name`='hhh',`password`='qweqwe' WHERE id=1;
-- BETWEEN a AND b 包含ab
UPDATE `students` SET `name`='dic',`password`='123123' 
WHERE id BETWEEN 1 AND 2;
-- 通过多个条件定位
UPDATE `students` SET `name`='rgc',`password`='666666' 
WHERE id>1 AND sex='男';

• where语句操作符

符号	说明
=	相等
<>或者！=	不等于
>	大于
<	小于
<=	小于等于
>=	大于等于
between A and B	AB之间（包含AB）

• 删除 delete

-- DELETE FROM `表名` WHERE 条件;
DELETE FROM `students` WHERE id=1;
-- 清空数据库
TRUNCATE `students`;

• delete和truncate的区别
  • 都能删除数据库中的数据，不会改变表中的结构
  • truncate 会重新设置自增列的计数器
  • InnoDB引擎在delete之后重启，计数器也会重置，MYISAM不会
  • truncate 不会影响事务

DQL 查询数据（最重点）

• Data Query Language：数据查询语言
• 所有的查询操作都使用select
• 简单复杂的查询都能做
• 数据库中最核心的语言，最重要的语句
• 使用频率最高的语句

---

• 指定查询字段

-- 查询某表中全部数据
SELECT * FROM `students`;
-- 查询指定的字段
SELECT `id`,`name` FROM `students`;
-- 别名：给查询结果中字段和结果表取别名
SELECT `id` AS 学号,`name` AS 姓名 FROM `students` AS 学生信息;
-- 函数 Concat() 可以传入多个参数，逗号隔开
SELECT CONCAT('姓名：',`id`,`name`) FROM `students`;
-- 查询语句后，也可以添加from表达式限定条件
SELECT `id` AS 学号,`name` AS 姓名 FROM `students` AS 学生信息 
WHERE id=2;

• 去重

SELECT * FROM `result`;-- 查询全部的考试成绩
SELECT `StudentId` FROM `result`;-- 查询有哪些学生参加了考试
-- 但是结果中有很多重复数据
-- 去重DISTINCT
SELECT DISTINCT `StudentId` FROM `result`;
-- 同时查询多个字段时，只要所查询字段一样的数据，
-- 都会被去重只留一个
SELECT DISTINCT `name`,`classid` FROM `students`;

• 数据库的列（表达式）

-- 查询系统版本 函数
SELECT VERSION();
-- 计算 表达式
SELECT 100*2-1 AS 结果;
-- 查询自增长的步长 变量
SELECT @@auto_increment_increment;

-- 查询分数结果+1
-- 和使用Concat()函数类似，并不改变数据库表内数据的值
SELECT `StudentId`,`StudentResult`+1 AS '提分后' FROM `result`;

数据库的表达式：文本值，列，null，函数，计算表达式，系统变量