Mysql学习 Day3 DML语言知识(select,update,delete)

MySQL的数据管理

1.DML语言(重要很重要)

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

DML语言：数据操作语言

• insert
• update
• delete

2.外键

方式一：创建表时给主键增加约束 （较复杂）

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

删除有外键关系的表时，必须先删除引用别人的表(从表)，再删除被引用的表(主表)

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

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

CREATE TABLE `student` (
  `id` INT(4) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) NOT NULL DEFAULT '张三' COMMENT '姓名',
  `pwd` VARCHAR(10) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
  `gradeid` INT(10) NOT NULL COMMENT '学生年级',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  `address` VARCHAR(100) DEFAULT NULL COMMENT '住址',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 创建表时没有外键关系
-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(外键列名) REFERENCES 关联外键表(外键表字段);
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

以上的操作都是数据库级别的外键，不建议使用！避免数据库过多，造成困扰

数据库就是单独表，只用来存数据，只有行(数据)和列(字段)
想使用多张表数据，用程序来实现实现外键功能

3.添加insert

语法：
insert into 表名([字段名1，字段名2....]) values('值1'),('值2')....

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

-- 由于主键自增，可以省略主键
-- 如果不写表的字段名，就会一一匹配
INSERT INTO `grade` VALUES('大二');   -- error

-- 一般写插入语句，一定要数据和字段名一一对应
-- 插入多个字段
INSERT INTO `grade`(`gradename`) VALUES('大二'),('高三');
INSERT INTO `student`(`name`,`pwd`) VALUES('幺幺','aaaaaa');

注意：

1. 字段和字段间使用英文逗号隔开
2. 字段是可以省略的，但是后面的值必须要一一对应，不能少
3. 可以同时插入多条数据，values后面的值需要使用，隔开values(),()....

4.修改update

语句：
update 表名 set 列名1=值1[,列名2=值2.....] where 条件；

-- 修改学员名字
-- 不指定条件的情况下，会改动所有表
UPDATE `student` SET `name`='旺旺' WHERE id=1;

-- 修改多个属性
UPDATE `student` SET `name`='旺旺',`gradeid`='大四' WHERE id=2;

-- 通过多个条件定位数据
UPDATE `student` SET `name`='摇摇' WHERE sex='男' AND `gradeid`='高二';

条件：where子句
操作符会返回布尔值

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

注意：

• 列名 是数据库的列，尽量带上``
• 条件是筛选的条件，如果没指定会修改所有列
• value是一个具体值，也可以是一个变量
  UPDATE student SET birthday=CURRENT_TIME WHERE name=‘哦哦’;
• 多个设置的属性之间，使用英文，隔开

5.删除delete

5.1.delete

语句：
delete from 表名 [where 条件]；

-- 删除数据（避免这种写法，整表数据删掉）
DELETE FROM `student`;
-- 删除指定数据
DELETE FROM `student` WHERE id = 1;

5.2.truncate

清空数据库中表，表的结构和索引约束不变
语句：
truncate 表名;

delete和truncate区别：
相同点：都能删除数据，都不会删除表结构
不同点：

• truncate 重新设置 自增列，计数器回归零
• truncate 不会影响事务

-- 测试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'),('4'),('5');

DELETE FROM `test`;   -- 不会影响自增
再插入数据，id自增字段从6开始

TRUNCATE TABLE `test`;   -- 自增会归零
再插入数据，id自增字段从1开始

※了解：
delete删除问题，重启数据库，现象

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