MySQL的数据管理

MySQL的数据管理

外键（了解）

• 方式一：在创建表的时候，增加外键（比较麻烦）

  CREATE TABLE `grade`(
     `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
     `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
     PRIMARY KEY (`gradeid`)
  )ENGINE = INNODB DEFAULT CHARSET = utf8
  
  -- 学生表的 gradeid 字段 要去引用年级表的 gradeid
  -- 1.定义外键key
  -- 2.给这个外键添加约束（执行引用）
  
  -- 设置条件：
  -- 1.要设置外键的字段不能是这个表的主键
  -- 2.该外键所引用的字段必须是对应表的主键
  -- 3.两个字段必须具有相同的数据类型和约束
  
  CREATE TABLE IF NOT EXISTS `studnet` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  `gradeid` INT(10) NOT NULL COMMENT '学生的年级',
  `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
  `email` VARCHAR(50) 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(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
     `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
     PRIMARY KEY (`gradeid`)
  )ENGINE = INNODB DEFAULT CHARSET = utf8
     
     
  -- 学生表的 gradeid 字段 要去引用年级表的 gradeid
  -- 1.定义外键key
  -- 2.给这个外键添加约束（执行引用）
  
  CREATE TABLE IF NOT EXISTS `student` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  `gradeid` INT(10) NOT NULL COMMENT '学生的年级',
  `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`);
  
  -- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 哪个表(哪个字段);
  

注意：以上的操作都是物理外键，是数据库级别的外键，不建议使用。

建表麻烦且乱，不容易删除表；数据库过多会造成困扰。

建议：

1. 数据库就是单纯的表，只用来存数据，只有行数据和列（字段）
2. 想使用外键操作，会用程序去实现，即逻辑外键

DML语言（增删改需要记住）

数据库意义：数据存储和数据管理

DML语言：数据操作语言

• Insert
• update
• delete

添加

INSERT

在某一个表中添加数据

CREATE TABLE `grade`(
   `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
   `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
   PRIMARY KEY (`gradeid`)
)ENGINE = INNODB DEFAULT CHARSET = utf8
   
   
-- 学生表的 gradeid 字段 要去引用年级表的 gradeid
-- 1.定义外键key
-- 2.给这个外键添加约束（执行引用）

CREATE TABLE IF NOT EXISTS `student` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`gradeid` INT(10) NOT NULL DEFAULT '0' COMMENT '学生的年级',
`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`);

-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 哪个表(哪个字段);

-- 插入语句（添加）
-- insert into 表名 ([字段名1，字段2，字段3])values('值1'),('值2'),('值3'),......)
INSERT INTO `grade`(`gradename`) VALUES('大四')

-- 由于主键自增我们可以省略（如果不写表的字段，他就会一一匹配）
-- INSERT INTO `grade` VALUES('大三');  会报错
-- 一般写插入语句，我们一定要数据和字段一一对应
-- 插入多个字段
INSERT INTO `grade`(`gradename`) VALUES('大二'),('大一')

-- 一行插入多个字段
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('张三','aaaaa','男')

-- 多行插入多个字段
INSERT INTO `student`(`name`,`pwd`,`sex`)
VALUES ('李四','bbbbb','男'),('王五','ccccc','男')

注意：

1. 字段和字段之间使用英文逗号隔开
2. 字段是可以省略的，但是后面的值必须要一一对应，做到每一个存在的字段都不能少
3. 可以同时插入多条数据，VALUES 后面的值，需要使用，隔开即可。

修改

UPDATE

修改表中的某个数据，根据某个条件修改某个值

-- 修改学员名字,带了（where）条件
UPDATE `student` SET `name`='刘' WHERE `id` = 1;
-- 不指定条件的情况下，会把表中所有行的name字段都修改了
-- （千万小心，要是一些关键的表，这样操作就完蛋了）
UPDATE `student` SET `name` = '完蛋了'

-- 修改多个属性，逗号隔开
UPDATE `student` SET `name`='liu',`email`='123456@qq.com' WHERE `id`=1;

重点：条件判断

where子句可以写很复杂的语句，可以使用运算符，目前常用的是：id等于或大于小于某个值，或者是在某个区间内。

操作符会返回：布尔值

操作符	含义	范围	结果
=	等于	id = 5	改变id = 5的行
<> 或者 !=	不等于	id <> 6	除了id = 6以外的都改变
>	…	…	…
<	…	…	…
>=	…	…	…
<=	…	…	…
between…and…	闭区间	[2,5]	[2,5]闭区间内的改变
and	我和你两个条件都成立	id <5 and age=16	id小于5并且年龄等于16的
or	我或你两个条件成立一个	id>1 or age>12	id大于1或者年龄大于12

UPDATE `student` SET `name`='liu',`email`='1234@qq.com' WHERE `id` > 2 AND `name` = '完蛋了'
UPDATE `student` SET `name`='between',`email`='1234@qq.com' WHERE `id` BETWEEN 1 AND 3

注意：

• 数据库的列尽量要加上``
• 条件，筛选的条件，如果没有指定，则会修改所有的列
• value，是一个具体的值，也可以是变量或方法，如果设置多个字段，需要用英文逗号隔开这些字段。
• 多个设置的属性之间，使用英文逗号隔开。

删除

DELETE

从某个表中删除数据

-- 删除数据
DELETE FROM `student` -- 如果没有条件的话，就会清空整个表里面的数据（千万别）
DELETE FROM `student` WHERE `id`=1

TRUNCATE

完全清空一个数据库表，表的结构和索引约束也不会变。

-- 清空
TRUNCATE TABLE `student`

DELETE和TRUNCATE的区别：

• 相同点：都能删除数据，但不会删除表结构
• 不同点：
  • TRUNCATE 会重新设置自增列，计数器会归零，即自增归零
  • TRUNCATE 不会影响事物
  • DELETE 不会影响自增，如果继续增加数据的话，就会在原来自增量的基础上继续增加

-- 测试DELETE 和 TRUNCATE 区别
CREATE TABLE `test`(
    `id` INT(4) NOT NULL AUTO_INCREMENT,
    `coll` VARCHAR(20) NOT NULL,
    PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8


INSERT INTO `test`(`coll`) VALUES('1'),('2'),('3')

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

TRUNCATE TABLE `test` -- 自增会归零

了解：DELETE删除的问题，删除完毕之后，重启数据库，会有以下现象：

1. InnoDB 自增列会重新从1开始，因为这个引擎是存在在内存之中的，所以断电即失。
2. MyISAM 继续从上一个自增量开始，是存在文件中的，不会丢失