MySQL笔记05

DML语言

数据操作语言：
	插入：insert
	修改：update
	删除：delete

一、插入

#一、插入语句
#方式一、
/*
语法：
	insert into 表名(列名,...)
	values(值1,...);
*/

#1、插入的值的类型要与列的类型一致或兼容
INSERT INTO beauty(id, `name`, sex, borndate, phone, photo, boyfriend_id)
VALUES(13, '唐艺昕', '女', '1990-4-23', '123456789', NULL, 2);
SELECT * FROM beauty;

#2、不可以为null的列必须插入值，可以为null的列如何插入值
#方式一：写列名用null填充
INSERT INTO beauty(id, `name`, sex, borndate, phone, photo, boyfriend_id)
VALUES(13, '唐艺昕', '女', '1990-4-23', '123456789', NULL, 2);
SELECT * FROM beauty;
#方式二:省略列名
INSERT INTO beauty(id, `name`, sex, borndate, phone, boyfriend_id)
VALUES(13, '唐艺昕', '女', '1990-4-23', '123456789', 2);
SELECT * FROM beauty;

#3、列的顺序可以调换
INSERT INTO beauty(`name`, sex, id, phone)
VALUE('蒋欣', '女', 16, '110');
SELECT * FROM beauty;

#4、列和值的个数必须一致
INSERT INTO beauty(`name`, sex, id, phone，boyfriend_id)
VALUE('关晓彤', '女', 16, '110');
SELECT * FROM beauty;

#5、可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致
INSERT INTO beauty
VALUE(18, '张飞', '男', NULL, '119', NULL, NULL);
SELECT * FROM beauty;


#方式二、
/*
语法：
insert into 表名
set 列名=值, 列名=值,...
*/

INSERT INTO beauty
SET id=19, name='刘涛',phone='123';
SELECT * FROM beauty;

#两种方式比较
#1、方式一支持插入多行，方式二不支持
INSERT INTO beauty
VALUE(18, '张飞', '男', NULL, '119', NULL, NULL),
			(19, '刘备', '男', NULL, '119', NULL, NULL),
			(20, '关羽', '男', NULL, '119', NULL, NULL);
#2、方式一支持子查询，方式二不支持
INSERT INTO beauty(id, `name`, phone)
SELECT 20, '宋茜', '456'

INSERT INTO beauty(id, `name`, phone)
SELECT id, boyName, '456'
FROM boys
WHERE id < 3;
SELECT * FROM beauty;

二、修改

#二、修改语句
/*
1、修改单标的记录
	语法：
		update 表名
		set 列=新值，列=新值，...
		where 筛选条件;
		
2、修改多表的记录
	语法：
	SQL92语法：
		update 表1 别名，表2 别名
		set 列=值，...
		where 连接条件
		and 筛选条件;
	SQL99语法：
		update 表1 别名
		inner|left|right join 表2 别名
		on 连接条件
		set 列=值
		where 筛选条件;
*/

#1、修改单标的记录
#案例1：修改beauty表中姓唐的电话为123456789
UPDATE beauty
SET phone = '123456789'
WHERE `name` LIKE '唐%';
SELECT * FROM beauty;
#案例2：将boys表中ID号为2的魅力值改为10
UPDATE boys
SET userCP = 10
WHERE id = 2;
SELECT * FROM boys;

#2、修改多表的记录
#案例1：修改张无忌的女朋友手机号为114
UPDATE boys bo
INNER JOIN beauty b
ON bo.id = b.boyfriend_id
set phone = '114'
WHERE bo.boyName = '张无忌';
SELECT * FROM beauty;
#案例2：修改没有男朋友的女神的男朋友编号都为2号
UPDATE boys bo
RIGHT JOIN beauty b
ON bo.id = b.boyfriend_id
SET b.boyfriend_id = 2
WHERE bo.id IS NULL;
SELECT * FROM beauty;

三、删除

#三、删除语句
/*
方式一：delete
语法：
	1、单标删除
	delete from 表名 where 筛选条件
	2、多表删除
		sql92语法：
			delete 【表1、2】的别名
			from 表1 别名， 表2 别名
			where 连接条件
			and 筛选条件;
		sql99语法：
			delete 【表1、2】的别名
			from 表1 别名
			inner|left|right join 表2 别名
			on 连接条件
			where 筛选条件;

方式二：truncate
语法：
	truncate table 表名;
*/

#方式一：delete
#1、单表的删除
#案例1：删除手机号以9结尾的女神信息
DELETE FROM beauty
WHERE phone LIKE '%9';
SELECT * FROM beauty;

#2、多表删除
#案例：删除张无忌的女朋友信息
DELETE b
FROM beauty b
INNER JOIN boys bo
ON bo.id = b.boyfriend_id
WHERE bo.boyName = '张无忌';
SELECT * FROM beauty;
#案例：删除黄晓明的信息及她女朋友的信息
DELETE b, bo
FROM beauty b
INNER JOIN boys bo
ON b.boyfriend_id = bo.id
WHERE bo.boyName = '黄晓明';

#方式二：truncate语句(也叫清空数据)
#案例：将魅力值>100的男神信息删除
TRUNCATE TABLE boys;

#两种方式的区别
#1、delete可以加where条件，truncate不能加
#2、truncate删除的效率高
#3、加入要删除的表中有自增长列，
	#如果使用delete删除后再插入数据，自增长列的值会从断点开始，
	#如果使用truncate删除再插入数据，自增长列的值从1开始
#4、truncate删除没有返回值，delete删除有返回值
#5、truncate删除不能回滚，delete删除可以回滚

增删改练习

#1.	运行以下脚本创建表my_employees
USE myemployees;
CREATE TABLE my_employees(
	Id INT(10),
	First_name VARCHAR(10),
	Last_name VARCHAR(10),
	Userid VARCHAR(10),
	Salary DOUBLE(10,2)
);
CREATE TABLE users(
	id INT,
	userid VARCHAR(10),
	department_id INT

);

#2.	显示表my_employees的结构
DESC my_employees;

#3.	向my_employees表中插入下列数据
ID	FIRST_NAME	LAST_NAME	USERID	SALARY
1	patel		Ralph		Rpatel	895
2	Dancs		Betty		Bdancs	860
3	Biri		Ben		Bbiri	1100
4	Newman		Chad		Cnewman	750
5	Ropeburn	Audrey		Aropebur	1550

#方式一：
INSERT INTO my_employees
VALUES(1,'patel','Ralph','Rpatel',895),
(2,'Dancs','Betty','Bdancs',860),
(3,'Biri','Ben','Bbiri',1100),
(4,'Newman','Chad','Cnewman',750),
(5,'Ropeburn','Audrey','Aropebur',1550);
DELETE FROM my_employees;
SELECT * FROM my_employees;
#方式二：
INSERT INTO my_employees
SELECT 1,'patel','Ralph','Rpatel',895 UNION
SELECT 2,'Dancs','Betty','Bdancs',860 UNION
SELECT 3,'Biri','Ben','Bbiri',1100 UNION
SELECT 4,'Newman','Chad','Cnewman',750 UNION
SELECT 5,'Ropeburn','Audrey','Aropebur',1550

#4.	 向users表中插入数据
1	Rpatel	10
2	Bdancs	10
3	Bbiri	20
4	Cnewman	30
5	Aropebur	40

INSERT INTO users
VALUES(1,'Rpatel',10),
(2,'Bdancs',10),
(3,'Bbiri',20);
SELECT * FROM users;

#5. 将 3 号员工的 last_name 修改为“drelxer”
UPDATE my_employees
SET Last_name = 'drelxer'
WHERE id = 3;
SELECT * FROM my_employees;

#6. 将所有工资少于 900 的员工的工资修改为 1000
UPDATE my_employees
SET Salary = 1000
WHERE Salary < 900;
SELECT * FROM my_employees;

#7. 将 userid 为 Bbiri 的 user 表和 my_employees 表的记录全部删除
DELETE u, me
FROM users u
INNER JOIN my_employees me
ON u.userid = me.Userid
WHERE u.userid = 'Bbiri';
SELECT * FROM my_employees;
SELECT * FROM users;

#8. 删除所有数据
DELETE FROM my_employees;
DELETE FROM users;

#9. 检查所作的修正
SELECT * FROM my_employees;
SELECT * FROM users;

#10. 清空表 my_employees
TRUNCATE TABLE users;
TRUNCATE TABLE my_employees;