mysql——DML数据操纵语言

DML语言（DATA manipulation LANGUAGE）
数据库操作语言：
    插入：INSERT
    修改：UPDATE
    删除：DELETE

   
一、插入

方式一

语法：    INSERT INTO 表名（列名......）VALUES（值......）；
特点：    ①插入的值的类型与列的类型要一致
    ②
1、插入的值的类型与列的类型要一致

INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id) 
VALUES(13,'唐艺昕','女','1990-4-23','18988888888',NULL,2);

2、可以为NULL的列如何插入值（①NULL填充②字段和值都不写）
方式1

INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id) 
VALUES(13,'唐艺昕','女','1990-4-23','18988888888',NULL,2);

方式2

INSERT INTO beauty(id,NAME,sex,borndate,phone,boyfriend_id) 
VALUES(14,'金星','女','1990-4-23','13888888888',9);

案例

INSERT INTO beauty(id,NAME,sex,phone) 
VALUES(15,'娜扎','女','13888888888');

3、列的顺序可以调换

INSERT INTO beauty(NAME,sex,id,phone)
VALUE('蒋欣','女',16,'110');

4、插入列数和值的数目必须一致

INSERT INTO beauty(NAME,sex,id,phone,boyfriend_id)
VALUE('关晓彤','女',17,'110');

5、可以省略列名，默认为所有列，列的顺序与表的顺序一致

INSERT INTO beauty
VALUES(18,'张飞','男',NULL,'119',NULL,NULL);

方式二

语法：    INSERT INTO 表名
    SET 列名=值，列名=值......

INSERT INTO beauty
SET id=19,NAME='刘涛',phone='999';

两种方式对比：
    1、方式一支持一次插入多行,方式二不支持
    INSERT INTO beauty 

    VALUES(23,'唐艺昕1','女','1990-4-23','18988888888',NULL,2),
    (24,'唐艺昕2','女','1990-4-23','18988888888',NULL,2),
    (25,'唐艺昕3','女','1990-4-23','18988888888',NULL,2);    

    2、方式一支持子查询，方式二不支持

    INSERT INTO beauty(id,NAME,phone)
    SELECT 26,'宋茜','118';

    INSERT INTO beauty(id,NAME,phone)
    SELECT id,boyname,'123456'
    FROM boys WHERE id<3;

二、修改

1、修改单表记录

语法：    UPDATE 表名 ①
    SET 列=新值，......③
    WHERE 筛选条件②
执行顺序：①②③

案例1：修改beauty表中，姓唐的电话更改为13899888899

UPDATE beauty
SET phone='13899888899'
WHERE NAME LIKE '唐%';

案例2：修改boys表中id号为2的名称为张飞，魅力值为10

UPDATE boys
SET boyName='张飞',userCP='10'
WHERE id=2;

2、修改多表记录（级联更新）

sql92语法：
    UPDATE 表1 别名，表2 别名
    SET 列=值，.......
    WHERE 连接条件
    AND 筛选条件;
sql99语法
    UPDATE 表1, 别名 INNER|LEFT|RIGHT JOIN 表2 别名
    ON 连接条件
    SET 列=值，......
    WHERE 筛选条件
案例1：修改张无忌的女朋友的手机号为114

UPDATE beauty g INNER JOIN boys b
ON g.boyfriend_id=b.id
SET g.phone='114'
WHERE b.boyName='张无忌';

案例2：修改没有男朋友的女神的男朋友编号都为2号

UPDATE beauty g
LEFT JOIN boys b
ON g.boyfriend_id=b.id
SET boyfriend_id=2
WHERE b.id IS NULL;

三、删除

方式一：DELETE（按行删除）

语法：    DELETE FROM 表名 WHERE 筛选条件

1、单表删除※

案例1：删除beauty表中手机编号最后一位为9的女神信息
DELETE FROM beauty
WHERE phone LIKE '%9';

2、多表删除

sql92语法
DELETE 表1的别名 #仅删除表1的记录，如要删除表2，则需要更改为表2
FROM 表1 别名，表2 别名
WHERE 连接条件
AND 筛选条件

sql99语法
DELETE 表1的别名 #仅删除表1的记录，如要删除表2，则需要更改为表2
FROM 表1 别名
INNER|LEFT|RIGHT 表2 别名
ON 连接条件
WHERE 筛选条件

案例1：删除张无忌的女朋友的信息

DELETE g
FROM beauty g
JOIN boys b ON g.boyfriend_id=b.id
WHERE boyName='张无忌';

案例2：删除黄晓明的信息以及女朋友的信息

DELETE g,b
FROM beauty g
JOIN boys b
ON g.boyfriend_id=b.id
WHERE boyName='黄晓明';

方式二：TRUNCATE

语法： TRUNCATE TABLE 表名；
TRUNCATE不允许加WHERE筛选条件
案例1：将boys表中魅力值大于100的男神删除
TRUNCATE TABLE boys;

DELETE与TRUNCATE比较※
1、DELETE可以加WHERE条件，TRUNCATE不行
2、使用TRUNCATE比DELETE效率高
3、假如要删除的表中有自增长列，如果使用DELETE后，再插入数据，自增长的值从断点开始
而TRUNCATE，自增长列的值从1开始
4、TRUNCATE删除没有返回值，DELETE删除有返回值
5、TRUNCATE删除后不可以回滚，DELETE删除可以回滚

DELETE FROM boys;
INSERT INTO boys(boyName,userCP) 
VALUES('张飞',100),('刘备',100),('关云长',100);

TRUNCATE TABLE boys;
INSERT INTO boys(boyName,userCP) 
VALUES('张飞',100),('刘备',100),('关云长',100);

TRUNCATE TABLE my_employees;

习题

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;

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 表中插入数据

INSERT INTO users
VALUES(1,'Rpatel',10),
(2,'Bdancs',10),
(3,'Bbiri',20),
(4,'Cnewman',30),
(5,'Aropebur',40);

5. 将 3 号员工的 last_name 修改为“drelxer”

UPDATE my_employees
SET Last_name='drelxer'
WHERE Id=3;

6. 将所有工资少于 900 的员工的工资修改为 1000

UPDATE my_employees
SET Salary=1000
WHERE Salary<900;

7. 将 userid 为 Bbiri 的 USER 表和 my_employees 表的记录全部删除

DELETE m,u
FROM users u
JOIN my_employees m ON u.userid=m.userid
WHERE u.Userid='Bbiri';

8. 删除所有数据

TRUNCATE TABLE my_employees;

DELETE FROM users;

9. 检查所作的修正

SELECT * FROM my_employees;
SELECT * FROM users;