MYSQL:union, 以及常用函数
/**
燕十八 公益PHP培训
课堂地址:YY频道88354001
学习社区:www.zixue.it
**/
<?php
header('content-type:text/html; charset=utf-8');
/*
要做:商城的留言板
一般情况,做留言板的显示很容易
直接select 查询,再显示出来
但ecshop中的留言板难点在于
留言数据来自于2张表
feedback 留言表
comment 评论表
我们需要把2张表中的数据取出来,显示.
思路:从业务逻辑层,用php来解决这个问题
1:先取feedback表,循环取出数据,放入一个数组
2:再取comment表, 循环取出数据,放入一个数组
3:把取出两个数组合并
4:循环合并后的大数组
*/
$conn = mysql_connect('localhost','root','111111');
$sql = 'use gyshop'; // 你的ecshop库名叫什么,你就选什么
mysql_query($sql,$conn);
$sql = 'set names utf8';
mysql_query($sql,$conn);
// 开始取feedback表的数据
$sql = 'select user_name,msg_content,msg_time from feedback where msg_status=1';
$rs = mysql_query($sql,$conn);
$feeds = array();
while($row = mysql_fetch_assoc($rs)) {
$feeds[] = $row;
}
//print_r($feeds);
// 开始取comment表的数据
$sql = 'select user_name,content as msg_content,add_time as msg_time from comment where status=1';
$rs = mysql_query($sql,$conn);
$comm = array();
while($row = mysql_fetch_assoc($rs)) {
$comm[] = $row;
}
//print_r($feeds);
//print_r($comm);
$all = array_merge($feeds,$comm);
//print_r($all);exit;
/*
技术经理看到了:
小王啊,你这个语句可以再精简.
没必要2条sql语句,取2张表,再array_merge.
完全可以1条语句来完成.
问:1条语句怎么写?
经理:我只是给你思路,百度'union'
*/
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="zh-CN">
<head>
<title>新建网页</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="description" content="" />
<meta name="keywords" content="" />
<script type="text/javascript">
</script>
<style type="text/css">
</style>
</head>
<body>
<h1>我也会ecshop的留言板</h1>
<table border="1">
<?php foreach($all as $v) { ?>
<tr>
<td><?php echo $v['user_name']; ?></td>
<td><?php echo $v['msg_time']; ?></td>
</tr>
<tr>
<td colspan="2"><?php echo $v['msg_content']; ?></td>
</tr>
<?php } ?>
</table>
</body>
</html>
/**
燕十八 公益PHP培训
课堂地址:YY频道88354001
学习社区:www.zixue.it
**/
<?php
header('content-type:text/html; charset=utf-8');
/*
要做:商城的留言板
用union全并结合集,一条sql语句来搞定
*/
$conn = mysql_connect('localhost','root','111111');
$sql = 'use gyshop'; // 你的ecshop库名叫什么,你就选什么
mysql_query($sql,$conn);
$sql = 'set names utf8';
mysql_query($sql,$conn);
// 开始取feedback表的数据
$sql1 = 'select user_name,msg_content,msg_time from feedback where msg_status=1';
$sql2 = 'select user_name,content as msg_content,add_time as msg_time from comment where status=1';
$sql = $sql1 . ' union ' . $sql2;
$rs = mysql_query($sql,$conn);
$all = array();
while($row = mysql_fetch_assoc($rs)) {
$all[] = $row;
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="zh-CN">
<head>
<title>新建网页</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="description" content="" />
<meta name="keywords" content="" />
<script type="text/javascript">
</script>
<style type="text/css">
</style>
</head>
<body>
<h1>我也会ecshop的留言板</h1>
<table border="1">
<?php foreach($all as $v) { ?>
<tr>
<td><?php echo $v['user_name']; ?></td>
<td><?php echo $v['msg_time']; ?></td>
</tr>
<tr>
<td colspan="2"><?php echo $v['msg_content']; ?></td>
</tr>
<?php } ?>
</table>
</body>
</html>
/**
燕十八 公益PHP培训
课堂地址:YY频道88354001
学习社区:www.zixue.it
**/
mysql> use test
Database changed
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| account |
| boy |
| category |
| class |
| girl |
| goods |
| m1 |
| member |
| mian |
| minigoods |
| result |
| salary |
| stu |
| test |
| test10 |
| test11 |
| test2 |
| test3 |
| test4 |
| test5 |
| test6 |
| test7 |
| test8 |
| test9 |
+----------------+
24 rows in set (1.16 sec)
mysql> create table m(
-> mid int,
-> hid int,
-> gid int,
-> mres varchar(10),
-> matime date
-> )engine myisam charset utf8;
Query OK, 0 rows affected (0.19 sec)
mysql>
mysql>
mysql> create table t (
-> tid int,
-> tname varchar(20)
-> )engine myisam charset utf8;
Query OK, 0 rows affected (0.09 sec)
mysql>
mysql>
mysql>
mysql> insert into m
-> values
-> (1,1,2,'2:0','2006-05-21'),
-> (2,2,3,'1:2','2006-06-21'),
-> (3,3,1,'2:5','2006-06-25'),
-> (4,2,1,'3:2','2006-07-21');
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql>
mysql>
mysql> insert into t
-> values
-> (1,'国安'),
-> (2,'申花'),
-> (3,'公益联动');
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from m;
+------+------+------+------+------------+
| mid | hid | gid | mres | matime |
+------+------+------+------+------------+
| 1 | 1 | 2 | 2:0 | 2006-05-21 |
| 2 | 2 | 3 | 1:2 | 2006-06-21 |
| 3 | 3 | 1 | 2:5 | 2006-06-25 |
| 4 | 2 | 1 | 3:2 | 2006-07-21 |
+------+------+------+------+------------+
4 rows in set (0.00 sec)
mysql> select * from t;
+------+----------+
| tid | tname |
+------+----------+
| 1 | 国安 |
| 2 | 申花 |
| 3 | 公益联动 |
+------+----------+
3 rows in set (0.02 sec)
mysql> update t set tname='公益联队' where tid=3;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> #要求取出比赛的结果,并且队伍id应显示出队名
mysql> #简单一点,取出主客队的id,并不做特殊处理
mysql> select hid,mres,gid,matime from m;
+------+------+------+------------+
| hid | mres | gid | matime |
+------+------+------+------------+
| 1 | 2:0 | 2 | 2006-05-21 |
| 2 | 1:2 | 3 | 2006-06-21 |
| 3 | 2:5 | 1 | 2006-06-25 |
| 2 | 3:2 | 1 | 2006-07-21 |
+------+------+------+------------+
4 rows in set (0.00 sec)
mysql> #再前进一小步,根据hid,左联t表,查出主队的队伍名称
mysql> #即简单的2张表左连接
mysql> select hid,tname,mres,gid,matime
-> from
-> m left join t on m.hid=t.tid;
+------+----------+------+------+------------+
| hid | tname | mres | gid | matime |
+------+----------+------+------+------------+
| 1 | 国安 | 2:0 | 2 | 2006-05-21 |
| 2 | 申花 | 1:2 | 3 | 2006-06-21 |
| 3 | 公益联队 | 2:5 | 1 | 2006-06-25 |
| 2 | 申花 | 3:2 | 1 | 2006-07-21 |
+------+----------+------+------+------------+
4 rows in set (0.08 sec)
mysql> +------+----------+------+------+------------+
-> | hid | tname | mres | gid | matime |
-> +------+----------+------+------+------------+
-> | 1 | 国安 | 2:0 | 2 | 2006-05-21 |
-> | 2 | 申花 | 1:2 | 3 | 2006-06-21 |
-> | 3 | 公益联队 | 2:5 | 1 | 2006-06-25 |
-> | 2 | 申花 | 3:2 | 1 | 2006-07-21 |
-> +------+----------+------+------+------------+\c
mysql> select hid,tname,mres,gid,tname,matime
-> from
-> (m left join t on m.hid=t.tid)
-> left join t on m.gid=t.tid;
ERROR 1066 (42000): Not unique table/alias: 't'
mysql> #错误的原因 是 m t t 相连,两张t表,名字冲突,起个别名就可以解决
mysql> # select 列名 as 别名,也可以 from 表名 as 表别名
mysql> select hid,t1.tname,mres,gid,matime
-> from
-> m left join t as t1 on m.hid=t1.tid;
+------+----------+------+------+------------+
| hid | tname | mres | gid | matime |
+------+----------+------+------+------------+
| 1 | 国安 | 2:0 | 2 | 2006-05-21 |
| 2 | 申花 | 1:2 | 3 | 2006-06-21 |
| 3 | 公益联队 | 2:5 | 1 | 2006-06-25 |
| 2 | 申花 | 3:2 | 1 | 2006-07-21 |
+------+----------+------+------+------------+
4 rows in set (0.00 sec)
mysql> select hid,t1.tname,mres,gid,t2.tname,matime
-> from
-> m left join t as t1 on m.hid=t1.tid;
ERROR 1054 (42S22): Unknown column 't2.tname' in 'field list'
mysql>
mysql> left join t2 on m.gid=t2.tid\c
mysql> select hid,t1.tname,mres,gid,t2.tname,matime
-> from
-> m left join t as t1 on m.hid=t1.tid
-> left join t as t2 on m.gid=t2.tid;
+------+----------+------+------+----------+------------+
| hid | tname | mres | gid | tname | matime |
+------+----------+------+------+----------+------------+
| 1 | 国安 | 2:0 | 2 | 申花 | 2006-05-21 |
| 2 | 申花 | 1:2 | 3 | 公益联队 | 2006-06-21 |
| 3 | 公益联队 | 2:5 | 1 | 国安 | 2006-06-25 |
| 2 | 申花 | 3:2 | 1 | 国安 | 2006-07-21 |
+------+----------+------+------+----------+------------+
4 rows in set (0.00 sec)
mysql> #得到最终列
mysql> select hid,t1.tname,mres,gid,t2.tname,matime
-> \c
mysql> select t1.tname,mres,t2.tname,matime
-> from
-> m left join t as t1 on m.hid=t1.tid
-> left join t as t2 on m.gid=t2.tid;
+----------+------+----------+------------+
| tname | mres | tname | matime |
+----------+------+----------+------------+
| 国安 | 2:0 | 申花 | 2006-05-21 |
| 申花 | 1:2 | 公益联队 | 2006-06-21 |
| 公益联队 | 2:5 | 国安 | 2006-06-25 |
| 申花 | 3:2 | 国安 | 2006-07-21 |
+----------+------+----------+------------+
4 rows in set (0.00 sec)
mysql> #左连接之后,where,group..5句照常使用
mysql> select hid,t1.tname,mres,gid,t2.tname,matime
-> from
-> m left join t as t1 on m.hid=t1.tid
-> left join t as t2 on m.gid=t2.tid
-> where matime between '2006-06-01' and '2006-07-01';
+------+----------+------+------+----------+------------+
| hid | tname | mres | gid | tname | matime |
+------+----------+------+------+----------+------------+
| 2 | 申花 | 1:2 | 3 | 公益联队 | 2006-06-21 |
| 3 | 公益联队 | 2:5 | 1 | 国安 | 2006-06-25 |
+------+----------+------+------+----------+------------+
2 rows in set (0.05 sec)
mysql> select hid,t1.tname,mres,gid,t2.tname,matime
-> from
-> m left join t as t1 on m.hid=t1.tid
-> left join t as t2 on m.gid=t2.tid
-> where matime > '2006-06-01' and matime < '2006-07-01';
+------+----------+------+------+----------+------------+
| hid | tname | mres | gid | tname | matime |
+------+----------+------+------+----------+------------+
| 2 | 申花 | 1:2 | 3 | 公益联队 | 2006-06-21 |
| 3 | 公益联队 | 2:5 | 1 | 国安 | 2006-06-25 |
+------+----------+------+------+----------+------------+
2 rows in set (0.00 sec)
mysql> #切换到ecsho库,练习union
mysql> #取comment表
mysql> select user_name,content,add_time from comment where status=1;
ERROR 1146 (42S02): Table 'test.comment' doesn't exist
mysql> use gyshop
Database changed
mysql> select user_name,content,add_time from comment where status=1;
+-----------+----------------+------------+
| user_name | content | add_time |
+-----------+----------------+------------+
| ecshop | 很好，我很喜欢 | 1242107120 |
+-----------+----------------+------------+
1 row in set (0.00 sec)
mysql> select user_name,content as aa,add_time as bb from comment where status=1;
+-----------+----------------+------------+
| user_name | aa | bb |
+-----------+----------------+------------+
| ecshop | 很好，我很喜欢 | 1242107120 |
+-----------+----------------+------------+
1 row in set (0.00 sec)
mysql> #回到test库继续练习
mysql> use test
Database changed
mysql> #要求查出价格低于100元 和 价格高于4000元的商品
mysql> #要求不能用or
mysql> #我先查<100的商品
mysql> select goods_id,goods_name,shop_price from goods where shop_price < 100;
+----------+-----------------------+------------+
| goods_id | goods_name | shop_price |
+----------+-----------------------+------------+
| 4 | 诺基亚N85原装充电器 | 58.00 |
| 3 | 诺基亚原装5800耳机 | 68.00 |
| 5 | 索爱原装M2卡读卡器 | 20.00 |
| 6 | 胜创KINGMAX内存卡 | 42.00 |
| 25 | 小灵通/固话50元充值卡 | 48.00 |
| 26 | 小灵通/固话20元充值卡 | 19.00 |
| 27 | 联通100元充值卡 | 95.00 |
| 28 | 联通50元充值卡 | 45.00 |
| 29 | 移动100元充值卡 | 90.00 |
| 30 | 移动20元充值卡 | 18.00 |
+----------+-----------------------+------------+
10 rows in set (0.06 sec)
mysql> select goods_id,goods_name,shop_price from goods where shop_price < 30;
+----------+-----------------------+------------+
| goods_id | goods_name | shop_price |
+----------+-----------------------+------------+
| 5 | 索爱原装M2卡读卡器 | 20.00 |
| 26 | 小灵通/固话20元充值卡 | 19.00 |
| 30 | 移动20元充值卡 | 18.00 |
+----------+-----------------------+------------+
3 rows in set (0.00 sec)
mysql> #取出>4000的商品
mysql> select goods_id,goods_name,shop_price from goods where shop_price > 4000;
+----------+----------------+------------+
| goods_id | goods_name | shop_price |
+----------+----------------+------------+
| 22 | 多普达Touch HD | 5999.00 |
+----------+----------------+------------+
1 row in set (0.00 sec)
mysql> #2个语句的结果拼接起来就可以了. union 合并,连接
mysql> select goods_id,goods_name,shop_price from goods where shop_price < 30
-> union
-> select goods_id,goods_name,shop_price from goods where shop_price > 4000;
+----------+-----------------------+------------+
| goods_id | goods_name | shop_price |
+----------+-----------------------+------------+
| 5 | 索爱原装M2卡读卡器 | 20.00 |
| 26 | 小灵通/固话20元充值卡 | 19.00 |
| 30 | 移动20元充值卡 | 18.00 |
| 22 | 多普达Touch HD | 5999.00 |
+----------+-----------------------+------------+
4 rows in set (0.00 sec)
mysql> #利于目前的知识,简化商城的留言版
mysql> #先取feedback表
mysql> select user_name,msg_content,msg_time from feedback where msg_status=1;
ERROR 1146 (42S02): Table 'test.feedback' doesn't exist
mysql> #再次切换到ecshop的库,去练习
mysql> select user_name,msg_content,msg_time from feedback where msg_status=1;
ERROR 1146 (42S02): Table 'test.feedback' doesn't exist
mysql> use gyshop;
Database changed
mysql> select user_name,msg_content,msg_time from feedback where msg_status=1;
+-----------+--------------------+------------+
| user_name | msg_content | msg_time |
+-----------+--------------------+------------+
| 匿名用户 | 骗子!寄来的是砖头. | 1351573375 |
+-----------+--------------------+------------+
1 row in set (0.00 sec)
mysql> #接下来,再取comment表
mysql> select user_name,content as msg_content,add_time as msg_time
-> from comment where status=1;
+-----------+----------------+------------+
| user_name | msg_content | msg_time |
+-----------+----------------+------------+
| ecshop | 很好，我很喜欢 | 1242107120 |
+-----------+----------------+------------+
1 row in set (0.01 sec)
mysql> #再合并结果就可以了
mysql> select user_name,msg_content,msg_time from feedback where msg_status=1
-> union
-> select user_name,content as msg_content,add_time as msg_time
-> from comment where status=1;
+-----------+--------------------+------------+
| user_name | msg_content | msg_time |
+-----------+--------------------+------------+
| 匿名用户 | 骗子!寄来的是砖头. | 1351573375 |
| ecshop | 很好，我很喜欢 | 1242107120 |
+-----------+--------------------+------------+
2 rows in set (0.09 sec)
mysql> #两次的列名称不一样,还能否union
mysql> select user_name,msg_content,msg_time from feedback where msg_status=1
-> union
-> select user_name,content,add_time from comment where status=1;
+-----------+--------------------+------------+
| user_name | msg_content | msg_time |
+-----------+--------------------+------------+
| 匿名用户 | 骗子!寄来的是砖头. | 1351573375 |
| ecshop | 很好，我很喜欢 | 1242107120 |
+-----------+--------------------+------------+
2 rows in set (0.08 sec)
mysql> #看看列的类型不一致
mysql> select user_name,msg_time from feedback
-> union
-> select add_time,user_name from comment;
+------------+------------+
| user_name | msg_time |
+------------+------------+
| ecshop | 1242107197 |
| 匿名用户 | 1351573375 |
| 1242107120 | ecshop |
| 1242107295 | ecshop |
+------------+------------+
4 rows in set (0.00 sec)
mysql> #union后的结果集,能否再排序
mysql> #答:能
mysql> select goods_id,goods_name,shop_price from goods where shop_price < 30
-> union
-> select goods_id,goods_name,shop_price from goods where shop_price > 4000
->
-> order by shop_price asc;
+----------+-----------------------+------------+
| goods_id | goods_name | shop_price |
+----------+-----------------------+------------+
| 30 | 移动20元充值卡 | 18.00 |
| 26 | 小灵通/固话20元充值卡 | 19.00 |
| 5 | 索爱原装M2卡读卡器 | 20.00 |
| 22 | 多普达Touch HD | 5999.00 |
+----------+-----------------------+------------+
4 rows in set (0.02 sec)
mysql> #用union,取出第4个栏目的栏目,和第5个栏目的商品. 并按价格升序排列
mysql> (SELECT goods_id,goods_name,shop_price FROM goods WHERE cat_id = 4 ORDER BY shop_price DESC)
-> UNION
-> (SELECT goods_id,goods_name,shop_price FROM goods WHERE cat_id = 5 ORDER BY shop_price DESC);
+----------+---------------------+------------+
| goods_id | goods_name | shop_price |
+----------+---------------------+------------+
| 1 | KD876 | 1388.00 |
| 14 | 诺基亚5800XM | 2625.00 |
| 18 | 夏新T5 | 2878.00 |
| 33 | 金立910浪漫镶钻手机 | 1233.00 |
| 23 | 诺基亚N96 | 3700.00 |
+----------+---------------------+------------+
5 rows in set (0.00 sec)
mysql> (SELECT goods_id,cat_id,goods_name,shop_price FROM goods WHERE cat_id = 4 ORDER BY shop_price DESC)
-> UNION
-> (SELECT goods_id,cat_id,goods_name,shop_price FROM goods WHERE cat_id = 5 ORDER BY shop_price DESC);
+----------+--------+---------------------+------------+
| goods_id | cat_id | goods_name | shop_price |
+----------+--------+---------------------+------------+
| 1 | 4 | KD876 | 1388.00 |
| 14 | 4 | 诺基亚5800XM | 2625.00 |
| 18 | 4 | 夏新T5 | 2878.00 |
| 33 | 4 | 金立910浪漫镶钻手机 | 1233.00 |
| 23 | 5 | 诺基亚N96 | 3700.00 |
+----------+--------+---------------------+------------+
5 rows in set (0.00 sec)
mysql> #上面这个做法就错了,应该是针对最终结果排序才对.
mysql> #应该如下写
mysql> SELECT goods_id,cat_id,goods_name,shop_price FROM goods WHERE cat_id = 4
-> UNION
-> SELECT goods_id,cat_id,goods_name,shop_price FROM goods WHERE cat_id = 5
-> order by shop_price asc;
+----------+--------+---------------------+------------+
| goods_id | cat_id | goods_name | shop_price |
+----------+--------+---------------------+------------+
| 33 | 4 | 金立910浪漫镶钻手机 | 1233.00 |
| 1 | 4 | KD876 | 1388.00 |
| 14 | 4 | 诺基亚5800XM | 2625.00 |
| 18 | 4 | 夏新T5 | 2878.00 |
| 23 | 5 | 诺基亚N96 | 3700.00 |
+----------+--------+---------------------+------------+
5 rows in set (0.00 sec)
mysql> (SELECT goods_id,cat_id,goods_name,shop_price FROM goods WHERE cat_id = 4 ORDER BY shop_price DESC)
-> UNION
-> (SELECT goods_id,cat_id,goods_name,shop_price FROM goods WHERE cat_id = 5 ORDER BY shop_price DESC)
->
-> order by shop_price asc;
+----------+--------+---------------------+------------+
| goods_id | cat_id | goods_name | shop_price |
+----------+--------+---------------------+------------+
| 33 | 4 | 金立910浪漫镶钻手机 | 1233.00 |
| 1 | 4 | KD876 | 1388.00 |
| 14 | 4 | 诺基亚5800XM | 2625.00 |
| 18 | 4 | 夏新T5 | 2878.00 |
| 23 | 5 | 诺基亚N96 | 3700.00 |
+----------+--------+---------------------+------------+
5 rows in set (0.00 sec)
mysql> #取第3个栏目,价格前3高,和第4个栏目,价格前2高.
mysql> #用union来完成
mysql> #先取3栏前3高
mysql> select goods_id,cat_id,goods_name,shop_price
-> from goods
-> where cat_id=3 order by shop_price desc limit 3;
+----------+--------+----------------+------------+
| goods_id | cat_id | goods_name | shop_price |
+----------+--------+----------------+------------+
| 22 | 3 | 多普达Touch HD | 5999.00 |
| 32 | 3 | 诺基亚N85 | 3010.00 |
| 17 | 3 | 夏新N7 | 2300.00 |
+----------+--------+----------------+------------+
3 rows in set (0.00 sec)
mysql> #再取4栏前2高
mysql> select goods_id,cat_id,goods_name,shop_price
-> from goods
-> where cat_id=4 order by shop_price desc limit 2;
+----------+--------+--------------+------------+
| goods_id | cat_id | goods_name | shop_price |
+----------+--------+--------------+------------+
| 18 | 4 | 夏新T5 | 2878.00 |
| 14 | 4 | 诺基亚5800XM | 2625.00 |
+----------+--------+--------------+------------+
2 rows in set (0.00 sec)
mysql> (select goods_id,cat_id,goods_name,shop_price from goods where cat_id=3 order by shop_price desc limit 3)
-> union
-> (select goods_id,cat_id,goods_name,shop_price
-> from goods
-> where cat_id=4 order by shop_price desc limit 2);
+----------+--------+----------------+------------+
| goods_id | cat_id | goods_name | shop_price |
+----------+--------+----------------+------------+
| 22 | 3 | 多普达Touch HD | 5999.00 |
| 32 | 3 | 诺基亚N85 | 3010.00 |
| 17 | 3 | 夏新N7 | 2300.00 |
| 18 | 4 | 夏新T5 | 2878.00 |
| 14 | 4 | 诺基亚5800XM | 2625.00 |
+----------+--------+----------------+------------+
5 rows in set (0.00 sec)
mysql> #回到test库,观察union对于重复行的处理
mysql> use test
Database changed
mysql> create table test12 (
-> sname varchar(20)
-> )engine myisam charset utf8;
Query OK, 0 rows affected (0.11 sec)
mysql> create table test13 (
-> sname varchar(20)
-> )engine myisam charset utf8;
Query OK, 0 rows affected (0.28 sec)
mysql> insert into test12 values ('a'),('b'),('c');
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into test13 values ('b'),('c'),('d');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test12
-> union
-> select * from test13;
+-------+
| sname |
+-------+
| a |
| b |
| c |
| d |
+-------+
4 rows in set (0.00 sec)
mysql> select * from test12
-> union all
-> select * from test13;
+-------+
| sname |
+-------+
| a |
| b |
| c |
| b |
| c |
| d |
+-------+
6 rows in set (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| account |
| boy |
| category |
| class |
| girl |
| goods |
| m |
| m1 |
| member |
| mian |
| minigoods |
| result |
| salary |
| stu |
| t |
| test |
| test10 |
| test11 |
| test12 |
| test13 |
| test2 |
| test3 |
| test4 |
| test5 |
| test6 |
| test7 |
| test8 |
| test9 |
+----------------+
28 rows in set (0.03 sec)
mysql> create table a (
-> id char(1),
-> num int
-> )engine myisam charset utf8;
Query OK, 0 rows affected (0.17 sec)
mysql>
mysql> insert into a values ('a',5),('b',10),('c',15),('d',10);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql>
mysql> create table b (
-> id char(1),
-> num int
-> )engine myisam charset utf8;
Query OK, 0 rows affected (0.17 sec)
mysql>
mysql> insert into a values ('b',5),('c,15),('d',20),('e',99);
'> \c
'> '\c
mysql> insert into b values ('b',5),('c',15),('d',20),('e',99);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from a;
+------+------+
| id | num |
+------+------+
| a | 5 |
| b | 10 |
| c | 15 |
| d | 10 |
+------+------+
4 rows in set (0.00 sec)
mysql> select * from b;
+------+------+
| id | num |
+------+------+
| b | 5 |
| c | 15 |
| d | 20 |
| e | 99 |
+------+------+
4 rows in set (0.00 sec)
mysql> #可用用左连接来做
mysql> select a.*,b.* from
-> a left join b on a.id=b.id;
+------+------+------+------+
| id | num | id | num |
+------+------+------+------+
| a | 5 | NULL | NULL |
| b | 10 | b | 5 |
| c | 15 | c | 15 |
| d | 10 | d | 20 |
+------+------+------+------+
4 rows in set (0.00 sec)
mysql> #再把上面的结果看成一张临时表,再次from型子查询,计算a.num+b.num的和
mysql> #这个思路,课下同学们自己来试.如遇到坑,查 ifnull函数
mysql> #而且少了e, 只好左连 union 右连,再子查询
mysql>
mysql> #换个思路,先把2张表的数据union到一块,再利用sum()函数来相加
mysql> select * from a;
+------+------+
| id | num |
+------+------+
| a | 5 |
| b | 10 |
| c | 15 |
| d | 10 |
+------+------+
4 rows in set (0.00 sec)
mysql> select * from b;
+------+------+
| id | num |
+------+------+
| b | 5 |
| c | 15 |
| d | 20 |
| e | 99 |
+------+------+
4 rows in set (0.00 sec)
mysql> select * from a
-> union
-> select * from b;
+------+------+
| id | num |
+------+------+
| a | 5 |
| b | 10 |
| c | 15 |
| d | 10 |
| b | 5 |
| d | 20 |
| e | 99 |
+------+------+
7 rows in set (0.00 sec)
mysql> #再sum一下,
mysql> select id,sum(num) from (
->
-> select * from a
-> union
-> select * from b
-> ) as tmp
->
-> group by id;
+------+----------+
| id | sum(num) |
+------+----------+
| a | 5 |
| b | 15 |
| c | 15 |
| d | 30 |
| e | 99 |
+------+----------+
5 rows in set (0.03 sec)
mysql> #c错了,不要去重复
mysql> select id,sum(num) from (
->
-> select * from a
-> union all
-> select * from b
-> ) as tmp
->
-> group by id;
+------+----------+
| id | sum(num) |
+------+----------+
| a | 5 |
| b | 15 |
| c | 30 |
| d | 30 |
| e | 99 |
+------+----------+
5 rows in set (0.00 sec)
mysql> exit
浙公网安备 33010602011771号