Day17-MySQL数据管理

MySQL数据管理

外键（了解即可）

方法一:创建表的时候，增加约束（麻烦，比较复杂）

CREATE TABLE IF NOT EXISTS`grade`(
    `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT'年级号',
    `gradename` VARCHAR(50) NOT NULL COMMENT'年级名',
    PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

#学生表的gradeid字段，引用年级表的gradeid
#定义外键key
#给这个外键添加约束（执行引用）REFERENCES引用
CREATE TABLE IF NOT EXISTS `student`(
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT'学号',
    `name` VARCHAR(20) 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'学生年级',
    `adress` 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

删除有外键关系的表，必须要先删除引用它的那个表。（如：student表引用了grade，所以要先删除student表才能删除grade表）

方法二：添加的外键约束

#创建表的时候没有外键
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)

以上操作都是物理外键，数据库级别的外键，我们不建议使用。（避免数据库过多造成困扰）

方法三：可以通过可视化操作

最佳实践

• 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
• 我们想使用多张表的数据，想使用外键（用程序去实践）

DML语言（全部记住）

数据库的意义：数据存储，数据管理

• insert

• update

• delete

添加

insert

#插入语句
#insert into 表名([字段1，字段2，字段3])values('值1'，'值2'，'值3'，......)
#如果不写表的字段，就会依次匹配，所以一般写插入语句就要数据和字段一一对应
#由于主键自增，我们可以省略gradeid
INSERT INTO `grade` (`gradename`)VALUES('大四')
#插入多个字段
INSERT INTO `grade` (`gradename`)
VALUES('大三'),('大二'),('大一')

INSERT INTO `student` (`name`,`pwd`,`sex`) 
VALUES('张三','123456','女'),
('李四','123456','男'),
('王五','123456','男')

修改

update 根据条件去修改值

#修改学员的名字
#指定条件的情况下
UPDATE `student` SET `name`='小赵' WHERE id = 2;
#不指定条件的情况下,会改变表中name所有的数据
UPDATE `student` SET `name`='小赵';
#格式：
#UPDATE 表名 SET `colnum_name`=value.[colnum_name =value......] WHERE 条件;

指定条件：

不指定条件：

#修改多个属性
UPDATE `student` SET `name`='小龚', `birthday`='2000.9.11' WHERE id = 1;

用逗号隔开就可以了

#通过多个条件定位数据，无上限
UPDATE `student` SET `name`='张三' WHERE id = 3 AND sex='男';

条件：where子句，运算符 id等于某个值、大于某个值或者在某个区间内修改…

操作符会返回布尔值

操作符	含义	范围	结果
=	等于	5=6	false
<>或!=	不等于	5<>6	true
>	大于	5>6	false
<	小于	5<6	true
<=	小于等于	5<=6	true
>=	大于等于	5>=6	false
BETWEEN… AND…	在什么之间，闭合区间	[2,5]
AND	和，相当于&&	5>1 and 1>2	false
OR	或，相当于||	5>1 or 1>2	true

注意：

• colnum_name是数据库的列，尽量带上``
• 要确定筛选的条件，如果没有指定就会修改所有的列。
• value，可以是一个具体的值，也可以是一个变量(一般是时间)

UPDATE `student` SET `birthday` = CURRENT_TIME WHERE id = 3 AND sex='男';

• 多个设置的属性之间，使用英文逗号隔开

删除

DELETE

语法：delete from 表名[where 条件]

#删除指定数据
DELETE FROM `student` WHERE id=4;
#未指定数据，会全部删除
DELETE FROM `student`

TRUNCATE

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

#清空student表
TRUNCATE `student`

DELETE和TRUNCATE的区别

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

CREATE TABLE `test`(
   `id` INT(4) NOT NULL AUTO_INCREMENT,
   `coll` VARCHAR(10) NOT NULL,
   PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `test`(`coll`) VALUES('1'),('2'),('3')
#DELETE 不会影响自增
DELETE FROM `test`
#TRUNCATE  自增归零
TRUNCATE TABLE `test`

DELETE:

TRUNCATE:

了解即可：DELETE的删除问题，重启数据库，现象

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