MySQL怎么创建多个表的更新与删除-编程学习网

本篇内容主要讲解“MySQL怎么创建多个表的更新与删除”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“MySQL怎么创建多个表的更新与删除”吧!

1.涉及多个表的更新与删除
创建测试用表：

mysql> create table users1
-> (
-> uid tinyint unsigned,
-> uname varchar(255),
-> gid tinyint unsigned
-> );
Query OK, 0 rows affected (0.06 sec)

mysql> create table groups1
-> (
-> gid tinyint unsigned,
-> gname varchar(255)
-> );
Query OK, 0 rows affected (0.02 sec)

[@more@]mysql> insert into users1 values (0, 'root', 0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into users1 values (201, 'ggyy', 101);
Query OK, 1 row affected (0.00 sec)

mysql> insert into users1 values (202, 'ssff', 101);
Query OK, 1 row affected (0.00 sec)

mysql> insert into groups1 values (0, 'root');
Query OK, 1 row affected (0.00 sec)

mysql> insert into groups1 values (101, 'guest');
Query OK, 1 row affected (0.00 sec)

mysql> select * from users1;
+------+-------+------+
| uid | uname | gid |
+------+-------+------+
| 0 | root | 0 |
| 201 | ggyy | 101 |
| 202 | ssff | 101 |
+------+-------+------+
3 rows in set (0.00 sec)

mysql> select * from groups1;
+------+-------+
| gid | gname |
+------+-------+
| 0 | root |
| 101 | guest |
+------+-------+
2 rows in set (0.00 sec)

下面的语句将users1表中属于guest组的用户的uid加10：

mysql> update users1, groups1 set users1.uid = users1.uid + 10 where users1.gid = groups1.gid and gr
oups1.gname = 'guest';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> select * from users1;
+------+-------+------+
| uid | uname | gid |
+------+-------+------+
| 0 | root | 0 |
| 211 | ggyy | 101 |
| 212 | ssff | 101 |
+------+-------+------+
3 rows in set (0.00 sec)

下面的语句将两个表中guest组的gid变为102：

mysql> update users1, groups1 set users1.gid = 102, groups1.gid = 102 where users1.gid = groups1.gid
and groups1.gid = 101;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

mysql> select * from users1;
+------+-------+------+
| uid | uname | gid |
+------+-------+------+
| 0 | root | 0 |
| 211 | ggyy | 102 |
| 212 | ssff | 102 |
+------+-------+------+
3 rows in set (0.00 sec)

mysql> select * from groups1;
+------+-------+
| gid | gname |
+------+-------+
| 0 | root |
| 102 | guest |
+------+-------+
2 rows in set (0.00 sec)

但是，这样的语句就会产生错误的结果：

mysql> update users1, groups1 set users1.gid = 102, groups1.gid = 102 where users1.gid = groups1.gid
and groups1.gname = 'guest';
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> select * from users1;
+------+-------+------+
| uid | uname | gid |
+------+-------+------+
| 0 | root | 0 |
| 211 | ggyy | 102 |
| 212 | ssff | 101 |
+------+-------+------+
3 rows in set (0.00 sec)

mysql> select * from groups1;
+------+-------+
| gid | gname |
+------+-------+
| 0 | root |
| 102 | guest |
+------+-------+
2 rows in set (0.00 sec)

ssff用户的gid没有更新，想不太明白原因。

下面的语句删除users1表中属于root组的用户的记录：

mysql> delete from users1 using users1, groups1 where users1.gid = groups1.gid and groups1.gname = '
root';
Query OK, 1 row affected (0.00 sec)

mysql> select * from users1;
+------+-------+------+
| uid | uname | gid |
+------+-------+------+
| 211 | ggyy | 102 |
| 212 | ssff | 102 |
+------+-------+------+
2 rows in set (0.02 sec)

mysql> select * from groups1;
+------+-------+
| gid | gname |
+------+-------+
| 0 | root |
| 102 | guest |
+------+-------+
2 rows in set (0.00 sec)

该删除语句可以写成这样的形式：“delete users1 from users1, groups1 where users1.gid = groups1.gid and groups1.gname = 'root';”。注意，from前面的是要删除记录的表，后面的是删除操作涉及的几个表（本例中是内连接，可以为其它连接类型）。

下面的语句删除users1表中属于guest组的用户的记录以及groups1表中guest组的记录。

mysql> delete from users1, groups1 using users1, groups1 where users1.gid = groups1.gid and groups1.
gname = 'guest';
Query OK, 3 rows affected (0.00 sec)

mysql> select * from users1;
Empty set (0.02 sec)

mysql> select * from groups1;
+------+-------+
| gid | gname |
+------+-------+
| 0 | root |
+------+-------+
1 row in set (0.00 sec)

同样，该删除语句可以写成这样的形式：“delete users1, groups1 from users1, groups1 where users1.gid = groups1.gid and groups1.gname = 'guest';”。

2.随机选择记录

将ORDER BY子句和RAND()函数结合使用，可以达到随机选择表中记录的效果：

mysql> select * from oraleng;
+--------------------------+--------------------------+
| ask | answer |
+--------------------------+--------------------------+
| How do you do? | How do you do? |
| How are you? | Fine.Thank you. |
| What's your name? | My name is Jack Sparrow. |
| Where are you from? | I'm from maldives. |
| What's the weather like? | It's fine. |
| What time is it now? | It's seven o'clock. |
| What day is it today? | It's Wednesday. |
+--------------------------+--------------------------+
7 rows in set (0.00 sec)

mysql> select * from oraleng order by rand() limit 1;
+--------------+-----------------+
| ask | answer |
+--------------+-----------------+
| How are you? | Fine.Thank you. |
+--------------+-----------------+
1 row in set (0.02 sec)

mysql> select * from oraleng order by rand() limit 1;
+-----------------------+-----------------+
| ask | answer |
+-----------------------+-----------------+
| What day is it today? | It's Wednesday. |
+-----------------------+-----------------+
1 row in set (0.02 sec)

mysql> select * from oraleng order by rand() limit 1;
+-------------------+--------------------------+
| ask | answer |
+-------------------+--------------------------+
| What's your name? | My name is Jack Sparrow. |
+-------------------+--------------------------+
1 row in set (0.02 sec)

mysql> select * from oraleng order by rand() limit 2;
+----------------------+---------------------+
| ask | answer |
+----------------------+---------------------+
| What time is it now? | It's seven o'clock. |
| Where are you from? | I'm from maldives. |
+----------------------+---------------------+
2 rows in set (0.02 sec)

3.控制SELECT行为

下面是一些能够改变SELECT语句行为的关键字：

DISTINCT：删除结果集中的包含重复值记录。
SQL_CALC_FOUND_ROWS：计算符合查询的总行数。不受LIMIT影响，通过调用FOUND_ROWS函数可以得到结果。
SQL_CACHE和SQL_NO_CACHE：指定查询结果是否需要高速缓存。
SQL_BUFFER_RESULT：强制将查询结果存储到一个临时表。这种缓冲消除了对查询的表的锁定。
SQL_BIG_RESULT和SQL_SMALL_RESULT：指定结果集的期望大小。这样可帮助找到对返回的记录进行排序和存储的最佳方法（基于磁盘或者内存中的临时表）。
SQL_HIGH_PRIORITY：提升与UPDATE, INSERT和DELETE语句相竞争的查询的优先级。可以在繁忙的数据库服务器上快速地执行查询。

4.从文件导入和向文件导出

可以使用LOAD DATA INFILE语句将文件中的数据导入到表中，也可以使用SELECT...INTO OUTFILE语句将表中的记录导出到文件中。

1)分隔符

在上述语句中，使用一些子句和关键字指定文件中的数据格式。

LINES TERMINATED BY子句：指定记录的结束符。（默认情况下，n表示新的一行。）
FIELDS子句：指定字段的分割符。FIELDS后面跟着TERMINATED BY, ESCAPED BY, ENCLOSED BY等关键字中的一个或多个。
TERMINATED BY指定字段的结束符（默认为t）；ESCAPED BY用于跳过特殊的字符（默认为反斜线）；ENCLOSED BY指定包围字段的符号（默认无）。

2)从文件中导入数据

E:downloadcontact.txt是一个包含着一组联系人信息的文本文件，其内容如下：

河北联通石家庄分公司,张少兰,0311-87052200
河北联通沧州分公司,王建荣,0317-3520079
河北联通保定分公司,孙凤睿,0312-3075574
河北联通廊坊分公司,庞海静,0316-2684535
河北联通秦皇岛分公司,代艳丽,0335-3050172
......

现在创建一个用于存储这些联系人信息的表：

mysql> create table contact
-> (
-> name varchar(20),
-> sex enum('男','女'),
-> tel bigint,
-> email varchar(50),
-> company varchar(50)
-> );
Query OK, 0 rows affected (0.13 sec)

使用Load DATA INFILE语句向其中导入数据：

mysql> load data infile 'E:downloadcontact.txt' into table contact
-> fields terminated by ',' escaped by '-' lines terminated by 'rn'
-> (company, name, tel);
Query OK, 46 rows affected (0.02 sec)
Records: 46 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from contact limit 7;
+--------+------+-------------+-------+----------------------+
| name | sex | tel | email | company |
+--------+------+-------------+-------+----------------------+
| 张少兰 | NULL | 31187052200 | NULL | 河北联通石家庄分公司 |
| 王建荣 | NULL | 3173520079 | NULL | 河北联通沧州分公司 |
| 孙凤睿 | NULL | 3123075574 | NULL | 河北联通保定分公司 |
| 庞海静 | NULL | 3162684535 | NULL | 河北联通廊坊分公司 |
| 代艳丽 | NULL | 3353050172 | NULL | 河北联通秦皇岛分公司 |
| 齐卫花 | NULL | 3132018225 | NULL | 河北联通张家口分公司 |
| 刘守政 | NULL | 3182698169 | NULL | 河北联通衡水分公司 |
+--------+------+-------------+-------+----------------------+
7 rows in set (0.00 sec)

几点说明：

a.进行导入的用户必须具有FILE权限。
b.文件路径中的“”符号要用“”来代替。
c.当文件中各部分内容与表中的字段数量或顺序不符时，可以在LOAD DATA INFILE语句的最后指定一个字段名的列表，来将文件中的各部分内容映射到正确的字段中。

介绍LOAD DATA INFILE语句中的一些关键字：

LOCAL：指定INFILE是在客户机的文件系统上。默认情况下，认为在服务器上。
LOW_PRIORITY：延迟LOAD DATA语句的执行，直到没有其它的客户端从表中读取为止。
IGNORE, REPLACE：当插入的新记录的一个键与已存在的记录的重复时，跳过该条新记录或用新记录替换已存在的记录。

3)向文件中导出记录

使用SELECT INTO...OUTFILE语句向文本文件contact2.txt中导出记录：

mysql> select name, tel, company from contact where name like '张%'
-> into outfile 'E:downloadcontact2.txt'
-> fields enclosed by '"' lines terminated by 'rn';
Query OK, 4 rows affected (0.06 sec)

查看一下该文件的内容：

"张少兰" "31187052200" "河北联通石家庄分公司"
"张雷" "3125902030" "河北电信保定分公司"
"张东旺" "3155960019" "迁安市星宇商贸有限公司"
"张蕾" "3123100913" "保定企盟信息网络有限公司"

几点说明：

a.进行导出的用户必须具有FILE权限。
b.导出文件事先不能存在。否则会发生错误：

ERROR 1086 (HY000): File 'E:downloadcontact2.txt' already exists

c.对于二进制数据，如BLOB类型，可以使用INTO DUMPFILE子句代替INTO OUTFILE子句。这样MySQL将以一个单独行的格式向文件写入数据（没有字段或记录结束符），从而避免破坏二进制数据。