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将以一个单独行的格式向文件写入数据（没有字段或记录结束符），从而避免破坏二进制数据。

（编辑：银川站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!