MySQL基础(三)——DML

MySQL基础(三)——DML

视频学习来源:MySQL_基础+高级篇- 数据库 -sql -mysql教程_mysql视频_mysql入门_尚硅谷;

作者:木子六日;

MySQL版本:5.7.33;

插入语法

USE myemployees;

# 插入语法
# 方式一
INSERT INTO beauty (NAME, phone)
VALUES
	('高圆圆', '110');

# 插入多条
INSERT INTO beauty (NAME, phone)
VALUES
	('1', '1'),
	('2', '2'),
	('3', '3'),
	('4', '4'),
	('5', '5');

# 插入子查询结果
INSERT INTO beauty (NAME, phone) SELECT
	b.boyName,
	'123456'
FROM
	boys b;

# 方式二:不支持子查询，不支持批量插入
INSERT INTO beauty
SET `name` = 'ljj',
 phone = '17805806974';

删除语法

USE myemployees;

# 删除语法
DELETE
FROM
	beauty
WHERE
	LENGTH(phone) < 7;

# truncate删除是不能回滚的，但是delete是可以回滚的
# 删除整张表(下次插入自增列从断点处开始);
DELETE
FROM
	beauty;

# 彻底清空表(下次插入自增列从1开始);
TRUNCATE beauty;

# delete也支持连接
# 案例：删除张无忌的女朋友的信息
DELETE be
FROM
	beauty be
JOIN boys bo ON bo.id = be.boyfriend_id
WHERE
	bo.boyName = '张无忌';

修改语法

USE myemployees;

# 修改语法
# 修改单表
UPDATE beauty
SET phone = '123456789'
WHERE
	`name` = 'ljj';

# update语法也支持连接
UPDATE boys bo
INNER JOIN beauty be ON bo.id = be.boyfriend_id
SET be.phone = '114'
WHERE
	bo.boyName = '张无忌';