3.MySql的数据管理（DML语言）

1.外键（了解即可）

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

-- 创建年级表
CREATE TABLE IF NOT EXISTS `grade`(
	`grade_id` INT(10)	NOT NULL AUTO_INCREMENT COMMENT '学号',
	`grade_name` VARCHAR(50) NOT NULL COMMENT '年级名称',
	 PRIMARY KEY(`grade_id`)
)ENGINE = INNODB DEFAULT CHARSET=utf8

-- 学生表的grade_id 要去引用年级表的grade_id
-- 定义外键key
-- 给这个外键添加约束（执行引用）references 引用
CREATE TABLE IF NOT EXISTS `student` (
		`id` INT ( 4 ) NOT NULL AUTO_INCREMENT COMMENT '学号',
		`name` VARCHAR ( 30 ) NOT NULL DEFAULT '匿名' COMMENT '姓名',
		`pwd` VARCHAR ( 10 ) NOT NULL COMMENT '密码',
		`sex` VARCHAR ( 2 ) NOT NULL DEFAULT '男' COMMENT '性别',
		`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
		`grade_id` INT(10) NOT NULL COMMENT '学生的年级',
		`address` VARCHAR ( 100 ) DEFAULT NULL COMMENT '家庭住址',
		`email` VARCHAR ( 30 ) DEFAULT NULL COMMENT '电子邮箱',
	PRIMARY KEY ( `id` ), -- 主键 
	KEY `FK_gradeid` (`grade_id`),  -- 外键
	CONSTRAINT `FK_gradeid` FOREIGN KEY (`grade_id`) REFERENCES `grade`(`grade_id`) -- 外键关系
	) ENGINE = INNODB DEFAULT CHARSET = utf8

注：

删除有外键关系的表的时候，

需要先删除引用外键的表（从表），再删除被引用外键的表（主表）

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

-- 创建年级表
CREATE TABLE IF NOT EXISTS `grade`(
	`grade_id` INT(10)	NOT NULL AUTO_INCREMENT COMMENT '学号',
	`grade_name` VARCHAR(50) NOT NULL COMMENT '年级名称',
	 PRIMARY KEY(`grade_id`)
)ENGINE = INNODB DEFAULT CHARSET=utf8

-- 学生表的grade_id 要去引用年级表的grade_id
-- 定义外键key
-- 给这个外键添加约束（执行引用）REFERENCES 引用
CREATE TABLE IF NOT EXISTS `student` (
		`id` INT ( 4 ) NOT NULL AUTO_INCREMENT COMMENT '学号',
		`name` VARCHAR ( 30 ) NOT NULL DEFAULT '匿名' COMMENT '姓名',
		`pwd` VARCHAR ( 10 ) NOT NULL COMMENT '密码',
		`sex` VARCHAR ( 2 ) NOT NULL DEFAULT '男' COMMENT '性别',
		`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
		`grade_id` INT(10) NOT NULL COMMENT '学生的年级',
		`address` VARCHAR ( 100 ) DEFAULT NULL COMMENT '家庭住址',
		`email` VARCHAR ( 30 ) DEFAULT NULL COMMENT '电子邮箱',
	PRIMARY KEY ( `id` )
	) ENGINE = INNODB DEFAULT CHARSET = utf8

-- 创建表的时候没有外键，如何增加外键
-- 思路，利用修改表的语句格式（ALTER TABLE），利用（add）添加外键即可
ALTER TABLE `student`            -- 修改`student`表
ADD CONSTRAINT `FK_gradeid`      -- 添加一个约束名字叫：`FK_gradeid`
FOREIGN KEY(`grade_id`)          -- 哪一列作为外键
REFERENCES `grade`(`grade_id`)	 -- 引用`grade`表里的`grade_id`字段
-- 公式：
-- ALTER TABLE 表名	
-- ADD CONSTRAINT 约束名 FOREIGN KEY（作为外键的列）
-- REFERENCES 哪个表（`哪个字段`）

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

最佳解决方案：

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

2.DML语言(全部记住)

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

DML语音：数据操作语音

insert
update
delete

3.添加

insert

-- 插入语句（添加：insert）
INSERT INTO `grade`(`grade_name`) VALUES ('大四')
-- 由于主键自增我们可以省略
-- 如果不写表的字段他就会一一匹配
INSERT INTO `grade` VALUES (2,'大三')
-- 一般写入插入语句，我们一定要数据和字段一一对应

-- 查看`grade`表的信息
DESCRIBE `grade`

-- 往`grade`表中插入多个信息
INSERT INTO `grade`(`grade_id`,`grade_name`) 
VALUES ('1','大四'),('2','大三'),('3','大二'),('4','大一')

语法格式： insert into 表名 (字段名1,字段名2...) values('值1'),('值2')....

总结：

字段和字段之间使用英文隔开
字段是可以省略的，但是后面的值必须要一一对应
可以同时插入多条数据，values后面的值，需要使用，隔开即可。

Mysql防止重复插入唯一限制的4种方法

-- 1.insert ignore into 
-- 当插入数据时，如果出现错误的时候，如重复数据，将不返回错误，
-- 只以警告的形式返回，所以使用 ignore 请确保语句本身没有问题，不然会被忽略掉
INSERT IGNORE INTO `student`(name) VALUES('小飞')
-- 这种方式很简单，但是有一种可能，
-- 就是加入不是因为重复数据报错，而是因为其他数据报错的，也同样被忽略了。

-- 2.on duplicate key update
-- 当primary或者unique重复时，执行update语句，
-- 如update后为无用语句，如id=id，则同1功能，但错误不会被忽略掉
INSERT INTO `student`(name) VALUES ('小飞') ON duplicate KEY UPDATE id = id
-- 这种方法有一个前提，就是需要插入的约束，需要是主键或者唯一主键

-- 3.insert ... select ... where not exists
-- 根据select的条件来判断是否插入，可以不光通过primary和unique来判断，也可以根据其他来判断
INSERT INTO `grade`(`grade_id`,`grade_name`) SELECT ('1','大四') FROM `student`
WHERE NOT EXISTS (SELECT id FROM `student` WHERE id = 1) -- 该语句应该有问题，格式是正确的
-- 这样写较为繁琐

-- 4.replace into
-- 如果存在primary or unique相同记录，则先删除掉，再插入新记录
REPLACE INTO `grade`(`grade_id`,`grade_name`) VALUES ('1','大四')
-- 这种方法就是不管原来有没有相同的记录，都会先删除再插入

4.修改

update 修改谁（条件） set原来的值 = 新值

-- 修改学员的名字,带了条件
UPDATE `student` SET `name` = '小飞' where id = '1'

-- 不指定条件的情况下，会改动所有的表
-- 不要这样干！
UPDATE `student` SET `name` = 'it小飞'

-- 语法
-- UPDATE 表名 SET 修改内容 WHERE 条件

-- 修改多个属性,用逗号隔开
UPDATE `student` SET `name` = '小飞',`email` = '12345@qq.com' 
WHERE id = '1'

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

操作符会返回布尔值

-- 通过多个条件定位数据
-- AND 只改了 `name` = 'it小飞'和 sex = '女' 的数据
UPDATE `student` SET `name` =  '小李飞刀' WHERE `name`= 'it小飞' AND sex = '女'

-- OR 含有 `name` = 'it小飞'和 sex = '女' 的数据都改了
UPDATE `student` SET `name` =  '小李飞刀' WHERE `name`= 'it小飞' AND sex = '女'

语法：

UPDATE 表名 set colnum_name = value,[colnum_num = value , ....] where [条件]

注意事项：

colnum_name 是数据库的列，尽量带上 ``
条件，筛选的条件，如果没有指定，则会修改所有的列
value是一个具体的值，也可以是一个变量

多个设置的属性直接，使用英文逗号隔开。

-- 更改的是一个变量(将生日变成现在的时间:CURRENT_DATE or CURRENT_TIME)
UPDATE `student` SET `birthday` = CURRENT_DATE WHERE `name` = '小飞'

5.删除

delete命令

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

-- 删除数据（避免这样写，会全部删除）
DELETE FROM `student`

-- 删除指定数据
DELETE FROM `student` WHERE id = '1'
DELETE FROM `student` WHERE id = '2'

truncate命令

-- 清空`student`表
TRUNCATE `student`

delete 和 truncate命令的区别

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

-- 测试delete 和 TRUNCATE 的区别
CREATE TABLE `test`( 
	`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '编号',
	`name` VARCHAR(50) NOT NULL COMMENT '姓名',
	`pwd` VARCHAR(10) NOT NULL COMMENT '学号',
	PRIMARY KEY (`id`)
)ENGINE= INNODB DEFAULT CHARSET = utf8

-- 测试一下插入的几种方法，练练手
INSERT INTO `test`(`id`,`name`,`pwd`) VALUES ('1','小飞','123'),('2','小明','123')
INSERT INTO `test`(`name`,pwd) VALUES ('小包','123')
INSERT INTO `test`(`name`,pwd) VALUES ('小斌','456'),('小李','456')

-- DELETE删除后再增加发现主键自增不归零
DELETE FROM `test`
INSERT INTO `test`(`name`,`pwd`) VALUES('小李','123')

-- TRUNCATE删除后再增加发现主键自增归零了
TRUNCATE TABLE `test`
INSERT INTO `test`(`name`,`pwd`) VALUES ('小李','123')

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

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

posted @ 2022-12-06 15:41 It小飞呀阅读(40) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

itxiaofei