8
【必知必会的MySQL知识】③DML语言 - xiezhr
source link: https://www.cnblogs.com/xiezhr/p/17343314.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
【必知必会的MySQL知识】③DML语言
前面的两篇文章中，我们已经对MySQL有了基本了解。
并且知道了怎么用工具连接数据库？怎么创建数据库？怎么创建表？
这一篇呢我们就来看看怎么在我们创建的表中插入数据、删除数据和修改数据。也就是上一篇文章中提到的DML 数据操作语言
根据上一章所说的，我们创建一个db_xiezhr 数据库，并向数据库中添加一张用户信息表。
① 通过命令行建库，建表
mysql> create database if not exists db_xiezhr;
Query OK, 1 row affected (0.00 sec)
mysql> use db_xiezhr
Database changed
mysql> drop table if exists user_profile;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE `user_profile`
(`id` int NOT NULL,
`device_id` int NOT NULL,
`user_name` varchar(100) NOT NULL ,
`gender` varchar(14) NOT NULL,
`age` int ,
`user_email` varchar(50) ,
`user_zip` varchar(10),
`university` varchar(32) NOT NULL,
`province` varchar(32) NOT NULL,);
Query OK, 0 rows affected (0.01 sec)
② 当然了，我们可以通过上一篇文章中说到的工具之一建库、建表
这里以sqlyog工具为例，建立db_xiezhr数据库，并创建用user_profile户信息表
- 建立db_xiezhr数据库
- 创建用user_profile户信息表
以上，我们数据库和数据表就都已经创建成功了
#方式一：
INSERT INTO 表名 VALUES(值,...);
#方式二：
INSERT INTO 表名(字段名,...) VALUES(值,...);
#方式三：
INSERT INTO 表名(字段名,...) SELECT (字段名,...) FROM 表名;
插入完整行数据
①简单写法
- 存储到每个里表中的数据在VALUES子句中给出，值得顺序也必须要与定义时候的字段顺序一致，没有的值需要使用NULL。
- 这种写法简单，但并不安全，应尽量避免使用。一旦表结构变化了，可能就会出问题
# 向user_profile用户信息表中添加一条数据
insert into user_profile
values(
1,
1,
'张三',
'male',
'28',
null,
null,
'北京大学',
'BeiJing'
);
② 安全写法
- 跟上面简单写法效果一样，往用户信息表中插入一条数据
- 在表名后括号中明确给出了列名，在插入数据时，将用VALUES列表中的相应值填入对应的列名中。第一个列名对应第一个值，第二个列名对应第二个值......
- 因为提供了列名，所以值也不需要按照表中的次序来插入。即使表结构变化了，sql语句也能正常执行。
- 不需要插入的列user_email和user_zip 列，值也可以不用写出来了，也比较灵活
INSERT INTO `db_xiezhr`.`user_profile` (
`id`,
`device_id`,
`user_name`,
`gender`,
`age`,
`university`,
`province`
)
VALUES
(
1,
1,
'张三',
'male',
'28',
'北京大学',
'BeiJing'
) ;
注：
- 不管使用哪种insert语法，都必须给出values的正确数目。
- 如果不提供列名，则必须给每个列都提供一个值
- 如果提供列名，则必须对每个写出的列提供一个值
- 在insert语法中省略某列时，需要具备两个条件，一是该列被定义为允许NULL值二是表定义中给出了默认值
插入多行数据
这里你可能说我多写几条sql语句不就可以了么，哈哈，这也可以实现了么？
INSERT INTO `user_profile` (
`id`,
`device_id`,
`user_name`,
`gender`,
`age`,
`university`,
`province`
)
VALUES
(
2,
2,
'李四',
'male',
'36',
'天津大学',
'TianJin'
) ;
INSERT INTO `user_profile` (
`id`,
`device_id`,
`user_name`,
`gender`,
`age`,
`university`,
`province`
)
VALUES
(
3,
3,
'王五',
'female',
'25',
'天津大学',
'TianJin'
) ;
但这里提供另一种语法,一条语句就可以完成
INSERT INTO `user_profile` (
`id`,
`device_id`,
`user_name`,
`gender`,
`age`,
`university`,
`province`
)
VALUES
(
2,
2,
'李四',
'male',
'36',
'天津大学',
'TianJin'
),
(
3,
3,
'王五',
'female',
'25',
'天津大学',
'TianJin'
) ;
将检索出来的数据插入表
这里有两张表，一张是user_profile，数据如下
一张表是tmp_user_profile，数据如下
我们需要将tmp_user_profile 表中数据插入到user_profile 表中,可以通过如下脚本实现
INSERT INTO `user_profile` (
`id`,
`device_id`,
`user_name`,
`gender`,
`age`,
`university`,
`province`
)
SELECT
`id`,
`device_id`,
`user_name`,
`gender`,
`age`,
`university`,
`province`
FROM tmp_user_profile;
执行完上面脚本后，user_profile表中数据
准备两张表
- 下面我们建两张表，一张表为 product 表，用来存放产品信息，其中有产品价格字段 saleprice；另外一张表是 product_price 表。
# 产品信息表product
mysql> select * from product;
+----+-----------+--------------------------------------------+-----------+--------+
| id | productid | productname | saleprice | author |
+----+-----------+--------------------------------------------+-----------+--------+
| 1 | 10001 | 公众号XiezhrSpace【Oralce从入门到放弃】 | 100 | xiezhr |
| 2 | 10002 | 公众号XiezhrSpace【MySQL从入门到放弃】 | 99 | xiezhr |
| 3 | 10003 | 公众号XiezhrSpace【快速上手Linux核心命令】 | 88 | xiezhr |
| 4 | 10004 | 公众号XiezhrSpace【Java从入门到精通】 | 150 | xiezhr |
| 5 | 10005 | 公众号XiezhrSpace【Idea从入门到上瘾】 | 200 | xiezhr |
| 6 | 10006 | 公众号XiezhrSpace【如何快速搭建个人博客】 | 120 | xiezhr |
+----+-----------+--------------------------------------------+-----------+--------+
6 rows in set (0.02 sec)
# 产品价格表product_price
mysql> select * from product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
| 1 | 10001 | NULL |
| 2 | 10002 | NULL |
| 3 | 10003 | NULL |
| 4 | 10004 | NULL |
| 5 | 10005 | NULL |
| 6 | 1006 | NULL |
+----+-----------+-------+
6 rows in set (0.00 sec)
# 1、单表更新
UPDATE 表名 SET 列 = 值,... WHERE 查询条件;
# 2、根据一张表更新另一张表
# ① 使用update
UPDATE
表1 别名,
表2 别名
SET
列 = 值,
...
WHERE 连接条件 AND 筛选条件 ;
# ②通过INNER JOIN
UPDATE
表1 别名
INNER JOIN 表2 别名
ON 连接条件 AND 筛选条件
SET
列 = 值,
...；
# ③ 通过LEFT JOIN
UPDATE
表1 别名
LEFT JOIN 表2 别名
ON 连接条件 AND 筛选条件
SET
列 = 值,
...；
# ③ 通过子查询
UPDATE
表2 别名
SET 列 = (SELECT 表达式 FROM 表1 WHERE 连接条件 AND 筛选条件)；
# 2、同时更新两张表
UPDATE
表1
INNER JOIN 表2
ON 连接条件 AND 筛选条件
SET 表1.列=值1,
表2.列=值2;
① 将产品信息表product 中10001 号产品价格更新为999
mysql> UPDATE product t SET t.`saleprice` =999 WHERE t.`productid` = '10001';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from product;
+----+-----------+--------------------------------------------+-----------+--------+
| id | productid | productname | saleprice | author |
+----+-----------+--------------------------------------------+-----------+--------+
| 1 | 10001 | 公众号XiezhrSpace【Oralce从入门到放弃】 | 999 | xiezhr |
| 2 | 10002 | 公众号XiezhrSpace【MySQL从入门到放弃】 | 99 | xiezhr |
| 3 | 10003 | 公众号XiezhrSpace【快速上手Linux核心命令】 | 88 | xiezhr |
| 4 | 10004 | 公众号XiezhrSpace【Java从入门到精通】 | 150 | xiezhr |
| 5 | 10005 | 公众号XiezhrSpace【Idea从入门到上瘾】 | 200 | xiezhr |
| 6 | 10006 | 公众号XiezhrSpace【如何快速搭建个人博客】 | 120 | xiezhr |
+----+-----------+--------------------------------------------+-----------+--------+
6 rows in set (0.00 sec)
② 将 product_price 表中的价格字段 price 更新为 product 表中价格字段 price 的 80%。
# 使用“UPDATE table1 t1,table2,...,table n”的方式来多表更新
mysql> UPDATE product t1, product_price t2 SET t2.price = t1.`saleprice` * 0.8 WHERE t1.productid= t2.productId;
Query OK, 5 rows affected, 2 warnings (0.01 sec)
Rows matched: 5 Changed: 5 Warnings: 2
mysql> select * from product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
| 1 | 10001 | 80 |
| 2 | 10002 | 79 |
| 3 | 10003 | 70 |
| 4 | 10004 | 120 |
| 5 | 10005 | 160 |
| 6 | 1006 | NULL |
+----+-----------+-------+
6 rows in set (0.00 sec)
# 通过INNER JOIN
mysql> select * from product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
| 1 | 10001 | 80 |
| 2 | 10002 | 79 |
| 3 | 10003 | 70 |
| 4 | 10004 | 120 |
| 5 | 10005 | 160 |
| 6 | 1006 | NULL |
+----+-----------+-------+
6 rows in set (0.00 sec)
# 通过LEFT JOIN
mysql> UPDATE product t1 LEFT JOIN product_price t2 ON t1.productid= t2.productid SET t2.price = t1.`saleprice` * 0.8 WHERE t1.productid='10001';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
| 1 | 10001 | 80 |
| 2 | 10002 | NULL |
| 3 | 10003 | NULL |
| 4 | 10004 | NULL |
| 5 | 10005 | NULL |
| 6 | 1006 | NULL |
+----+-----------+-------+
6 rows in set (0.00 sec)
# 通过子查询
mysql> UPDATE product_price t2 SET t2.price=(SELECT t1.`saleprice` *0.8 FROM product t1 WHERE t1.productid = t2.productid);
Query OK, 5 rows affected, 2 warnings (0.01 sec)
Rows matched: 6 Changed: 5 Warnings: 2
mysql> select * from product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
| 1 | 10001 | 80 |
| 2 | 10002 | 79 |
| 3 | 10003 | 70 |
| 4 | 10004 | 120 |
| 5 | 10005 | 160 |
| 6 | 1006 | NULL |
+----+-----------+-------+
6 rows in set (0.00 sec)
③ 同时更新两张表(正式开发中用得比较少)
- 两张表做关联，同时更新了 product_price 表的 price 字段和 product 表的 author两个字段。
mysql> UPDATE product t1 INNER JOIN product_price t2 ON t1.productid= t2.productid SET t2.price = t1.`saleprice` * 0.8, t1.`author` = 'xiezhr001';
Query OK, 5 rows affected, 2 warnings (0.00 sec)
Rows matched: 10 Changed: 5 Warnings: 2
mysql> select * from product;
+----+-----------+--------------------------------------------+-----------+-----------+
| id | productid | productname | saleprice | author |
+----+-----------+--------------------------------------------+-----------+-----------+
| 1 | 10001 | 公众号XiezhrSpace【Oralce从入门到放弃】 | 100 | xiezhr001 |
| 2 | 10002 | 公众号XiezhrSpace【MySQL从入门到放弃】 | 99 | xiezhr001 |
| 3 | 10003 | 公众号XiezhrSpace【快速上手Linux核心命令】 | 88 | xiezhr001 |
| 4 | 10004 | 公众号XiezhrSpace【Java从入门到精通】 | 150 | xiezhr001 |
| 5 | 10005 | 公众号XiezhrSpace【Idea从入门到上瘾】 | 200 | xiezhr001 |
| 6 | 10006 | 公众号XiezhrSpace【如何快速搭建个人博客】 | 120 | xiezhr |
+----+-----------+--------------------------------------------+-----------+-----------+
6 rows in set (0.00 sec)
mysql> select * from product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
| 1 | 10001 | 80 |
| 2 | 10002 | 79 |
| 3 | 10003 | 70 |
| 4 | 10004 | 120 |
| 5 | 10005 | 160 |
| 6 | 1006 | NULL |
+----+-----------+-------+
6 rows in set (0.00 sec)
# 1、单表删除
DELETE FROM 表名 【WHERE 筛选条件 】;
# 2、多表删除（级联删除）
① 关联删除
DELETE
表1的别名,
表2的别名
FROM
表1 别名,
表2 别名
WHERE 连接条件 AND 筛选条件 ;
② 内连接、左右连接删除
DELETE
表1的别名,
表2的别名
FROM
表1 别名
INNER | LEFT | RIGHT JOIN 表2 别名 ON 连接条件
WHERE 筛选条件 ;
① 单表删除
# 删除产品id为10005的产品信息
mysql> select * from product;
+----+-----------+--------------------------------------------+-----------+-----------+
| id | productid | productname | saleprice | author |
+----+-----------+--------------------------------------------+-----------+-----------+
| 1 | 10001 | 公众号XiezhrSpace【Oralce从入门到放弃】 | 100 | xiezhr001 |
| 2 | 10002 | 公众号XiezhrSpace【MySQL从入门到放弃】 | 99 | xiezhr001 |
| 3 | 10003 | 公众号XiezhrSpace【快速上手Linux核心命令】 | 88 | xiezhr001 |
| 4 | 10004 | 公众号XiezhrSpace【Java从入门到精通】 | 150 | xiezhr001 |
| 5 | 10005 | 公众号XiezhrSpace【Idea从入门到上瘾】 | 200 | xiezhr001 |
| 6 | 10006 | 公众号XiezhrSpace【如何快速搭建个人博客】 | 120 | xiezhr |
+----+-----------+--------------------------------------------+-----------+-----------+
6 rows in set (0.00 sec)
mysql> delete from product where productid = '10005';
Query OK, 1 row affected (0.02 sec)
mysql> select * from product;
+----+-----------+--------------------------------------------+-----------+-----------+
| id | productid | productname | saleprice | author |
+----+-----------+--------------------------------------------+-----------+-----------+
| 1 | 10001 | 公众号XiezhrSpace【Oralce从入门到放弃】 | 100 | xiezhr001 |
| 2 | 10002 | 公众号XiezhrSpace【MySQL从入门到放弃】 | 99 | xiezhr001 |
| 3 | 10003 | 公众号XiezhrSpace【快速上手Linux核心命令】 | 88 | xiezhr001 |
| 4 | 10004 | 公众号XiezhrSpace【Java从入门到精通】 | 150 | xiezhr001 |
| 6 | 10006 | 公众号XiezhrSpace【如何快速搭建个人博客】 | 120 | xiezhr |
+----+-----------+--------------------------------------------+-----------+-----------+
5 rows in set (0.00 sec)
② 多表级联删除
mysql> select * from product;
+----+-----------+--------------------------------------------+-----------+-----------+
| id | productid | productname | saleprice | author |
+----+-----------+--------------------------------------------+-----------+-----------+
| 1 | 10001 | 公众号XiezhrSpace【Oralce从入门到放弃】 | 100 | xiezhr001 |
| 2 | 10002 | 公众号XiezhrSpace【MySQL从入门到放弃】 | 99 | xiezhr001 |
| 3 | 10003 | 公众号XiezhrSpace【快速上手Linux核心命令】 | 88 | xiezhr001 |
| 4 | 10004 | 公众号XiezhrSpace【Java从入门到精通】 | 150 | xiezhr001 |
| 6 | 10006 | 公众号XiezhrSpace【如何快速搭建个人博客】 | 120 | xiezhr |
+----+-----------+--------------------------------------------+-----------+-----------+
5 rows in set (0.00 sec)
mysql> select * from product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
| 1 | 10001 | 80 |
| 2 | 10002 | 79 |
| 3 | 10003 | 70 |
| 4 | 10004 | 120 |
| 5 | 10005 | 160 |
| 6 | 1006 | NULL |
+----+-----------+-------+
6 rows in set (0.00 sec)
mysql> DELETE t1, t2 FROM product t1, product_price t2 WHERE t1.`productid` = t2.`productid` AND t1.`productid` = '10002';
Query OK, 2 rows affected (0.01 sec)
mysql> select * from product;
+----+-----------+--------------------------------------------+-----------+-----------+
| id | productid | productname | saleprice | author |
+----+-----------+--------------------------------------------+-----------+-----------+
| 1 | 10001 | 公众号XiezhrSpace【Oralce从入门到放弃】 | 100 | xiezhr001 |
| 3 | 10003 | 公众号XiezhrSpace【快速上手Linux核心命令】 | 88 | xiezhr001 |
| 4 | 10004 | 公众号XiezhrSpace【Java从入门到精通】 | 150 | xiezhr001 |
| 6 | 10006 | 公众号XiezhrSpace【如何快速搭建个人博客】 | 120 | xiezhr |
+----+-----------+--------------------------------------------+-----------+-----------+
4 rows in set (0.00 sec)
mysql> select * from product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
| 1 | 10001 | 80 |
| 3 | 10003 | 70 |
| 4 | 10004 | 120 |
| 5 | 10005 | 160 |
| 6 | 1006 | NULL |
+----+-----------+-------+
5 rows in set (0.00 sec)
mysql> select * from product;
+----+-----------+--------------------------------------------+-----------+-----------+
| id | productid | productname | saleprice | author |
+----+-----------+--------------------------------------------+-----------+-----------+
| 1 | 10001 | 公众号XiezhrSpace【Oralce从入门到放弃】 | 100 | xiezhr001 |
| 3 | 10003 | 公众号XiezhrSpace【快速上手Linux核心命令】 | 88 | xiezhr001 |
| 4 | 10004 | 公众号XiezhrSpace【Java从入门到精通】 | 150 | xiezhr001 |
| 6 | 10006 | 公众号XiezhrSpace【如何快速搭建个人博客】 | 120 | xiezhr |
+----+-----------+--------------------------------------------+-----------+-----------+
4 rows in set (0.00 sec)
mysql> select * from product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
| 1 | 10001 | 80 |
| 3 | 10003 | 70 |
| 4 | 10004 | 120 |
| 5 | 10005 | 160 |
| 6 | 1006 | NULL |
+----+-----------+-------+
5 rows in set (0.00 sec)
mysql> DELETE t1, t2 FROM product_price t2 INNER JOIN product t1 ON t1.`productid` = t2.`productid` WHERE t1.`productid` = '10003';
Query OK, 2 rows affected (0.01 sec)
mysql> select * from product;
+----+-----------+-------------------------------------------+-----------+-----------+
| id | productid | productname | saleprice | author |
+----+-----------+-------------------------------------------+-----------+-----------+
| 1 | 10001 | 公众号XiezhrSpace【Oralce从入门到放弃】 | 100 | xiezhr001 |
| 4 | 10004 | 公众号XiezhrSpace【Java从入门到精通】 | 150 | xiezhr001 |
| 6 | 10006 | 公众号XiezhrSpace【如何快速搭建个人博客】 | 120 | xiezhr |
+----+-----------+-------------------------------------------+-----------+-----------+
3 rows in set (0.00 sec)
mysql> select * from product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
| 1 | 10001 | 80 |
| 4 | 10004 | 120 |
| 5 | 10005 | 160 |
| 6 | 1006 | NULL |
+----+-----------+-------+
4 rows in set (0.00 sec)
- 这篇文章主要说了MySQL中单表多表的增删改，在update、delete 使用的时候一定要细心
- 除非打算更新删除表中所有数据，否则绝对不要使用不带where子句的update或delete语句
- 保证每个表都有主键
- 在对update或delete语句使用where子句之前，先用select进行查询测试，保证过滤出来的记录是正确的；
- update或delete语句执行前，尽量做好数据备份
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK