a2数据库管理

MySQL数据管理

外键管理

外键作用：

保持数据一致性，完整性，主要目的是控制存储在外键表中的数据。 使两张表形成关联，外键只能引用外表中的列的值或使用空值。

创建外键：

• 建表时指定外键约束

-- 创建数据表 grade
CREATE TABLE IF NOT EXISTS `grade`(
		`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
		`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
		PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8


/* studet 表的 gradeid 字段引用 grade 表的 gradeid 字段
1.定义外键key
		KEY `FK_外键key的名称` (`引用表的字段名`)
2.给外键添加约束(执行引用) 
		REFERENCES `被引用表的表名`(`被引用表的字段名`)
*/
CREATE TABLE IF NOT EXISTS `student`(
		`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
		`name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
		`password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
		`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
		`born` DATETIME DEFAULT NULL COMMENT '出生日期',
		`gradeid` INT(10) NOT NULL COMMENT '学生的年级', 
    	-- 需要将这个字段使用外键绑定到 grade 表的同名字段
		
    	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
		`email` VARCHAR(50) DEFAULT NULL COMMENT	'邮箱',
		PRIMARY KEY(`id`),
		KEY `FK_gradeid` (`gradeid`),  -- 定义外键key
		CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`) -- 给这个外键添加约束
)ENGINE=INNODB DEFAULT CHARSET=utf8 

• 建表后修改

-- 创建数据表 grade
CREATE TABLE IF NOT EXISTS `grade`(
		`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
		`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
		PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8


/* 添加外键方式二
ALTER TABLE `从表表名` ADD CONSTRAINT `外键名` FOREIGN KEY(`从表字段名`) REFERENCES `主表表名`(`主表字段名`) 
*/
CREATE TABLE IF NOT EXISTS `student`(
		`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
		`name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
		`password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
		`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
		`born` DATETIME DEFAULT NULL COMMENT '出生日期',
		`gradeid` INT(10) NOT NULL COMMENT '学生的年级', -- 需要将这个字段使用外键绑定到 grade 表的同名字段
		`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
		`email` VARCHAR(50) DEFAULT NULL COMMENT	'邮箱',
		PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8 

-- 创建表时没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

删除外键及表：

mysql清空表或者删除表带外键处理办法：

SET foreign_key_checks = 0;  /*禁用外键约束*/
truncate table table_a ;
truncate table table_b;
delete table table_c;
SET foreign_key_checks = 1;

mysql清空表可以用delete和truncate两个命令来完成：

1. delete

​ ① 语法：delete from table_name；

​ ② 示例：DELETE FROM order;

2. truncate

​ ① 语法：truncate table table_name；

​ ② 示例：TRUNCATE TABLE order;

3. 区别

​ ① 使用delete清空表中的记录，内容的ID仍然从删除点的ID继续建立，而不是从1开始。而truncate相当于保留了表的结构而重新建立了一张同样的新表。delete的效果有点像将mysql表中所有记录一条一条删除到删完。

​ ② 效率上truncate比delete快。但truncate删除后不记录mysql日志，不可以恢复数据。

DML语言

DML（数据操作语言）：用于操作数据库对象中所包含的数据

包括：INSERT ( 添加数据语句 )、UPDATE ( 更新数据语句 ) 、DELETE ( 删除数据语句 )

添加数据：

• INSERT命令：

  INSERT INTO 表名 [ (字段1, 字段2, 字段3, …) ]  VALUES ('值1', '值2', '值3', …)
  

  • 字段或值之间用英文逗号隔开

• “字段1, 字段2…”该部分可省略，但添加的值务必与表结构数据列顺序相对应,且数量一致

  • 可同时插入多条数据，values 后用英文逗号隔开

--  新增 insert into 表名 [(列1,列2....)] values(值1,值2....)
insert into grade(gradeName,gradeTime) values('S1',"1999-01-01"),('S2',"1999-01-01");
insert into `subject`(subjectName,classHour,gradeId) values("CSS",10,7);

下面介绍一条insert语句同时插入多条数据的语法：

INSERT INTO 表名 ([列名],[列名]) 
 VALUES
([列值],[列值])),
([列值],[列值])), 
([列值],[列值]));

修改数据：

• UPDATE命令：

  UPDATE 表名 SET column_name = value [ , column_name2 = value2, …. ]  [ WHERE condition ];
  

  • column_name 为要更改的数据列

• value 为修改后的数据，可以为变量、具体值、表达式或者嵌套的SELECT结果

  • condition为筛选条件，如不指定则修改该表的所有列数据

-- 修改  update 表名 set 列1=值,列=值2 [where 条件]
update grade set gradeName="S2",gradeTime="1999-01-01" where gradeId=8;

where条件子句：

有条件的从表中筛选数据

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

删除数据：

• DELETE命令：

DELETE  FROM  表名 [ WHERE condition ]; 

-- 删除 delete 	from 表名 [where 条件]
delete from grade where gradeId=1;

condition为筛选条件，如不指定，则删除该表的所有列数据

• TRUNCATE命令：

  用于完全清空表数据，但表结构、索引、约束等不变

TRUNCATE  [TABLE]  table_name

• 区别：

  相同：都能删除数据、不删除表结构，但TRUNCATE 速度更快

  不同：使用TRUNCATE TABLE重新设置AUTO_INCREMENT计数器

  ​ 使用TRUNCATE TABLE不会对事务有影响

查询数据及别名as：

-- 查询 select 列名1,列名2..... from 表名 [where 条件]
--  * 列可以用*代替   返回所有数据列(不推荐使用)
--  别名 as：列别名  表别名  
select subjectNo, subjectName as 课程名称, classHour 课时, a.gradeId   
from `subject` a 
where a.classHour>50 ;