慕课网--mysql开发技巧一 学习笔记
2024-10-09 04:47:47
现在存在下面的两张表,表的结构如下所示
师徒四人表结构:
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语句性能较低,可以使用下面的代码进行优化
最新文章
- MySQL 分组后,统计记录条数
- JVM基础知识
- 老王讲自制RPC框架.(二.动态代理)
- nagios二次开发(五)---nagios和nagiosql的关系
- ClientAbortException 异常解决办法
- linux定时器HZ和Jiffies
- [置顶] Ants(Northeastern Europe 2007)
- js的几种简单排序算法及其效率实测
- jquery.i18n.properties前端国际化解决方案“填坑日记”
- Spring Boot 参数校验
- goflyway简单使用
- Hadoop ConnectTimeoutException
- c++刷题(33/100)笔试题1
- Sqoop2安装
- 调用数据库-corina
- Windows Phone 7 检查手机网络
- Linux运维基础入门(四):Linux中的网络知识04
- DBGRID控件里可以实现SHIFT复选吗?怎么设置?
- 走进AOP In .NET
- HTML-基础及一般标签