慕课网--mysql开发技巧一 学习笔记

现在存在下面的两张表，表的结构如下所示

师徒四人表结构：
id，user_name,over
数据：
id user_name over
1 唐僧 旃檀功德佛
2 猪八戒 净坛使者
3 孙悟空 斗战胜佛
4 沙僧 金身罗汉

孙悟空朋友表结构：
id，user_name,over
数据：
id user_name over
1 孙悟空 成佛
2 牛魔王 被降服
3 蛟魔王 被降服
4 鹏魔王 被降服
5 狮驼王 被降服

over字段表示最后的成就

 

我们创建上面的两张表

CREATE TABLE `shitu` (
  `id` tinyint(3) unsigned NOT NULL auto_increment,
  `user_name` varchar(22) NOT NULL,
  `over` varchar(22) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `friend` (
  `id` tinyint(4) NOT NULL auto_increment,
  `user_name` varchar(22) default NULL,
  `over` varchar(22) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

左连接：

mysql> select a.user_name , a.over ,b.over       from  shitu  as a             left join  friend as b on a.user_name = b.user_name;  
+-----------+------------+----------+
| user_name | over       | over     |
+-----------+------------+----------+
| 唐僧      | 旃檀功德佛 | NULL     |
| 猪八戒    | 净坛使者   | NULL     |
| 孙悟空    | 斗战剩发   | 斗战胜佛 |
| 沙僧      | 金剩罗汉   | NULL     |
+-----------+------------+----------+
4 rows in set

inner join 连接：

mysql> select a.user_name , a.over ,b.over       from  shitu  as a             
 join  friend as b on a.user_name = b.user_name;  
+-----------+----------+----------+
| user_name | over     | over     |
+-----------+----------+----------+
| 孙悟空    | 斗战剩发 | 斗战胜佛 |
+-----------+----------+----------+
1 row in set

右连接

mysql> select a.user_name , a.over ,b.over       from  shitu  as a             right
 join  friend as b on a.user_name = b.user_name;  
+-----------+----------+----------+
| user_name | over     | over     |
+-----------+----------+----------+
| 孙悟空    | 斗战剩发 | 斗战胜佛 |
| NULL      | NULL     | 被降服   |
| NULL      | NULL     | 被降服   |
+-----------+----------+----------+
3 rows in set

 

9.   使用join从句更新表

 把同时存在于取经四人组和悟空兄弟表中的记录的人在取经四人表中的over字段更新为“齐天大圣”

1、第一步 首先取出两张表中都同时存在的人得到集合C

mysql> select a.user_name , a.over ,b.over from shitu as a join friend as b on a.user_name = b.user_name;

2、第二步：判断shitu表中那些人的人名在集合c中存在，如果存在将该记录更新成“齐天大圣”

上面是一种经典的设计思路，我们来看下面的代码

mysql> update 
     shitu  as  tmp1
set
     tmp1.over = '齐天大圣'
where
     tmp1.user_name
in
     (
       select 
             a.user_name
       from
             shitu as a
        join
             friend as b
        on
            a.user_name = b.user_name        
            
     )
;        
     
1093 - You can't specify target table 'tmp1' for update in FROM clause

如何解决该问题了

增加一个临时表解决上面的问题

mysql> update 
     shitu  as  tmp1
set
     tmp1.over = '齐天大圣'
where
     tmp1.user_name
in 
   (
       select 
       
           t2.user_name   
        from 
           (
               select 
                     a.user_name
               from
                     shitu as a
                join
                     friend as b
                on
                    a.user_name = b.user_name        
            
            ) as t2     
     
   
   )       
;  
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from shitu;
+----+-----------+------------+
| id | user_name | over       |
+----+-----------+------------+
|  1 | 唐僧      | 旃檀功德佛 |
|  2 | 猪八戒    | 净坛使者   |
|  3 | 孙悟空    | 齐天大圣   |
|  4 | 沙僧      | 金剩罗汉   |
+----+-----------+------------+
4 rows in set

 

参看博客：http://blog.csdn.net/fdipzone/article/details/52695371

上面这种代码比较复杂，可以进行优化，使用下面的代码进行优化，在数据量很大的时候上面的sql语句性能较低，可以使用下面的代码进行优化