MySQL数据库的数据基本管理（外键、添加Insert、修改update、删除delete）

MySQL 数据管理

外键

• 方式一：在创建表的时候，增加约束

--创建一个年级表
CREATE TABLE `grade` (
  `grade_id` int(10) NOT NULL AUTO_INCREMENT COMMENT '年级ID',
  `grade_name` varchar(10) NOT NULL COMMENT '年级名称',
  PRIMARY KEY (`grade_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='年级信息表';

--创建一个学生信息表
CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(13) DEFAULT NULL,
  `psd` varchar(11) NOT NULL DEFAULT '123456' COMMENT '密码',
  `gender` varchar(3) DEFAULT NULL,
  `address` varchar(30) DEFAULT NULL COMMENT '家庭地址',
  `email` varchar(11) DEFAULT NULL COMMENT '电子邮箱',
  `grade_id` int(10) DEFAULT NULL COMMENT '年级ID',
  PRIMARY KEY (`id`),
	KEY `FK_grade_id`(`grade_id`),  --定义外键
	CONSTRAINT `FK_grade_id` FOREIGN KEY(`grade_id`) REFERENCES `grade` (`grade_id`) --添加约束（执行引用） references 引用
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='学生信息表';

/*步骤：1.学生表的grade_id字段去引用年级表的grade_id字段
       2.定义外键key
       3.给这个外键添加约束（执行引用）references 引用
*/

• 注：删除有外键关系的表的时候，必须要先删除引用别人的表（从表），再删除被引用的表（主表）

• 方式二：创建成功后，添加外键约束

--创建一个年级表
CREATE TABLE `grade` (
  `grade_id` int(10) NOT NULL AUTO_INCREMENT COMMENT '年级ID',
  `grade_name` varchar(10) NOT NULL COMMENT '年级名称',
  PRIMARY KEY (`grade_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='年级信息表';

--创建一个学生信息表
CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(13) DEFAULT NULL,
  `psd` varchar(11) NOT NULL DEFAULT '123456' COMMENT '密码',
  `gender` varchar(3) DEFAULT NULL,
  `address` varchar(30) DEFAULT NULL COMMENT '家庭地址',
  `email` varchar(11) DEFAULT NULL COMMENT '电子邮箱',
  `grade_id` int(10) DEFAULT NULL COMMENT '年级ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生信息表';

--创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_grade_id`FOREIGN KEY(`grade_id`) REFERENCES `grade`(`grade_id`)
--语法：ALTER TABLE 表名
ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 引用的表(引用表的字段)

• 以上的操作都是物理外键，数据库级别的外籍，不建议使用
• 建议：数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
• 使用多张表的数据可以用程序实现

DML：数据库操作语言

添加（Insert）

-- 插入语句（添加）
INSERT INTO student1(name)VALUES('小明')
--语法：INSERT INTO 表名(字段名)VALUES('值')
--一个字段插入多个值
INSERT INTO student1(name)VALUES('Jack'),('Rose'),('Gary')
--语法：INSERT INTO 表名(字段名) VALUES('值1'),('值2'),('值3')...

--多个字段插入值
INSERT INTO student1(name,psd,gender) VALUES('小华','123456789','女')
--语法：INSERT INTO 表名(字段名1,字段名2,字段名3) VALUES('值1','值2','值3')

• 注意事项：
  • 如果不写表的字段，会从第二个字段开始一一匹配，（由于主键自增，所以可以省略）
  • 字段和字段之间使用英文逗号隔开
  • 字段是可以省略的，但是后面的值必须要一一对应，不能少
  • 可以同时插入多条数据，VALUES后面的值，需要使用，隔开即可

修改（update）

UPDATE student1 SET name='晓华' WHERE id=1
--不知道条件的情况下，会改动表的所有值
UPDATE student1 SET name='SQL'
--修改多个属性，逗号隔开
UPDATE student1 SET name='小明',psd='12345678',gender='男'WHERE id=1

• 注：where 子句后面的运算符

操作符	含义	范围	结果
=	等于	5=6	false
<>或!=	不等于	5<>6	true
>	大于	5>6	false
<	小于	5<6	true
<=	小于等于	5<=6	true
>=	大于等于	5>=6	false
between…and…	字某个范围内	[2,5]
and	&&与	5>2and1>2	false
or	||或	5>2or1>2	true

--通过过个条件定位数据
UPDATE student1 SET name='小华'WHERE id=1 AND gender='男' AND psd='12345678'

删除（delete）

语法：delete from 表名 [where 条件]
--删除数据（避免这样写，会全部删除）
delete from `student`
--删除指定数据
delete from `student` where `id`=1

• truncate 命令

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

  --清空 student 表
  truncate `student`
  

• delete和 truncate的区别

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

• delete删除的问题，重启数据库后：

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