MySQL数据管理——（三）

MySQL数据管理——（三）

5.1外键(Foreign key)（了解即可）

创建外键方式一：

• 在创建表的时候，增加约束（比较繁琐，不推荐使用）

-- 创建一张年级表（主表）
CREATE TABLE `grade` (
  `gradeid` int(11) NOT NULL AUTO_INCREMENT COMMENT '年级ID',
  `gradename` varchar(60) NOT NULL COMMENT '年级名称',
  PRIMARY KEY (`gradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
-- 创建一张学生表（从表）
-- 在学生表中gradeid字段，要去引用年级表的gradeid字段
-- 外键使用步骤：
-- 1、定义外键key
-- 2、给这个外键添加约束（执行引用） references 引用
CREATE TABLE `student`(
`id` INT(10) NOT NULL COMMENT '学号',
`name` VARCHAR(60) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`age` INT(2) NOT NULL COMMENT '年龄',
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
`gradeid` INT(11) NOT NULL COMMENT '学生的年级',
`address` VARCHAR(120) 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

创建外键方式二：

• 创建表成功后，添加外键
• 创建表的时候，没有外键关系

-- 创建一张年级表（主表）
CREATE TABLE `grade` (
  `gradeid` int(11) NOT NULL AUTO_INCREMENT COMMENT '年级ID',
  `gradename` varchar(60) NOT NULL COMMENT '年级名称',
  PRIMARY KEY (`gradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- 创建一张学生表（从表）
CREATE TABLE `student`(
`id` INT(10) NOT NULL COMMENT '学号',
`name` VARCHAR(60) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`age` INT(2) NOT NULL COMMENT '年龄',
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
`gradeid` INT(11) NOT NULL COMMENT '学生的年级',
`address` VARCHAR(120) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(30) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY(`id`),
)ENGINE=INNODB DEFAULT CHARSET=utf8

• 建表后修改从表并添加外键

ALTER TABLE student ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);
-- 格式：
ALTER TABLE 从表名 ADD CONSTRAINT 约束名 FOREIGN KEY (作为外键的列) REFERENCES 主表名 (主表的字段);

删除外键：

注意 : 删除具有主外键关系的表时 , 要先删从表 , 后删主表

-- 删除外键
ALTER TABLE student DROP FOREIGN KEY FK_gradeid;
-- 发现执行完上面的,索引还在,所以还要删除索引
-- 注:这个索引是建立外键的时候默认生成的
ALTER TABLE student DROP INDEX FK_gradeid;

以上的操作都是物理外键，数据库级别的外键，不推荐使用！

总结：

• 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
• 使用多张表的数据，通过外键（程序实现比较简单）

5.2DML语言（重点）

DML（简称数据操作语言）：用于操作数据库对象中所包含的数据，包括：INSERT(添加数据库数据)、UPDATE(更新数据库数据)、DELETE(删除数据库数据)

i.添加数据

INSERT命令

-- 插入语句，插入多个字段时
-- 格式：
INSERT INTO 表名[(字段1,字段2,字段3)] VALUES('值1','值2','值3');
INSERT INTO `students`(`id`,`name`,`age`) VALUES('6','ww','14');

-- 插入语句，一个字段插入多个值时
--格式：
INSERT INTO 表名(字段) VALUES('值1'),('值2'),('值3');
INSERT INTO `grade`(`gradename`) VALUES('大四'),('小学'),('大三');

注意 :

• 字段或值之间用英文逗号隔开.
• '字段1,字段2…'该部分可省略 , 但添加的值务必与表结构,数据列,顺序相对应,且数量一致.
• 可同时插入多条数据 , values 后用英文逗号隔开.

ii.修改数据

UPDATE命令 : 通俗一点就是修改谁（条件）和set 原来的值=新值

-- 修改单个字段的值
-- 语法格式：
UPDATE 表名 SET colnum_name=value WHERE 条件;
-- 修改学生名字，带了条件
UPDATE `students` SET `name`='狂神' WHERE id=1;

-- 修改多个字段的值
-- 语法格式：
UPDATE 表名 SET colnum_name=value,[colnum_name=value,....] WHERE 条件;
-- 修改学生名字和年龄，带了条件
UPDATE `students` SET `name`='狂神',age='20' WHERE id=1;
UPDATE `students` SET `name`='狂神',age='20' WHERE id BETWEEN 2 AND 4;

-- 通过多个条件，修改单个字段的值
-- 语法格式：
UPDATE 表名 SET colnum_name=value WHERE 条件1 and 条件2 and ......;
-- 修改学生名字，多个条件
UPDATE `students` SET `name`='狂神666' WHERE id BETWEEN 2 AND 4 AND `name`= '狂神';
--value可以是具体的值，也可以是变量
UPDATE student SET `birthday`=CURRENT_TIME WHERE id=1 AND sex='男';

注意：

• colnum_name 是数据库的列，尽量使用``括起来
• 条件，筛选的条件，如果没有指定，则会修改所有的列
• value，可以是一个具体的值，也可以是一个变量
• 多个设置的属性之间，使用英文符号隔开

where 条件：可以简单的理解为有一定的条件从表中进行筛选数据

where 子句 运算符 id等于某个值，大于某个值，在某个区间进行修改…,其最终结果时一个布尔值

运算符	含义	示例	结果
=	等于	1=1	true
<>或!=	不等于	5!=6	true
>	大于	5>6	false
<	小于	s<6	true
>=	大于等于	5>=6	false
<=	小于等于	5<=6	true
BETWEEN	在某个范围之间	BETWEEN 1 AND 5	-
AND	并且	1=1 AND 5<=6	true
OR	或	1=1 OR 5>6	true

iii.删除数据

DELETE命令

--删除数据（避免这样写，会全部删除）
DELETE FROM 表名；

-- 删除数据
-- 语法格式：
DELETE FROM 表名 WHERE 条件;
DELETE FROM `grade` WHERE gradeid=1;

TRUNCATE命令

作用：完全清空一个数据库表，表的结构和索引约束都不会变！

--清空 `student`表
TRUNCATE `student`

DELETE和TRUNCATE关系：

• 相同点：都能删除数据，都不会删除表结构
• 不同：
  • TRUNCATE 重新设置自增列表，计数器会归零
  • TRUNCATE 不会影响事务

-- 测试实例
CREATE TABLE `test`(
	`id` INT(3) NOT NULL AUTO_INCREMENT,
	`coll` VARCHAR(30) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `test`(`coll`) VALUES('a'),('b'),('c')

TRUNCATE `test`-- 自增会归零

DELETE FROM `test`--不会影响自增

扩展知识：DELETE删除的问题，重启数据库现象

• INNODB 自增列会重1开始（存在内存当中，断电即失）
• MyISAM 继续从上一个自增量开始（存在文件中，不会丢失）