php复习 第十三天 mysql基础3

<?

//子查询需求：一个查询需要另一个查询的结果参与的时候。关键字: in、not in、exists、not exists

// user_violation 用户违规表结构
mysql> describe user_violation;
+---------+-------------+------+-----+----------+----------------+
| Field   | Type        | Null | Key | Default  | Extra          |
+---------+-------------+------+-----+----------+----------------+
| id      | tinyint(4)  | NO   | PRI | NULL     | auto_increment |
| uid     | tinyint(4)  | NO   |     | NULL     |                |
| details | varchar(32) | NO   |     | 发布广告 |                |
+---------+-------------+------+-----+----------+----------------+
3 rows in set (0.01 sec)

//违规记录如下
mysql> select * from user_violation;
+----+-----+--------------+
| id | uid | details      |
+----+-----+--------------+
|  1 |   3 | 发布违规连接 |
|  2 |   3 | 发布违规连接 |
|  3 |   6 | 发布违规连接 |
|  4 |   5 | 发布违规连接 |
|  5 |   3 | 发布违规连接 |
|  6 |   2 | 违反用户条例 |
|  7 |   2 | 违反用户条例 |
|  8 |   3 | 发布广告     |
+----+-----+--------------+
8 rows in set (0.00 sec)

//案例1 查询有哪些用户违反了规定？
mysql> select u.id,u.username from user u where u.id in (select u_v.uid from user u,user_violation u_v where u.id = u_v.uid);
+----+----------+
| id | username |
+----+----------+
|  2 | local    |
|  3 | zhangsan |
|  5 | maliu    |
|  6 | zhangqi  |
+----+----------+
4 rows in set (0.00 sec)

//案例2 查询违反规定用户id,username及次数.
//这些题目都是自己给自己提问的。所以我真是的是挖坑给自己跳。我用了一晚上的时间一直在用子查询和分组函数就是统计不出来这个总违规次数。因为今天晚上复习了子查询，所以一直沉浸在这个子查询中。。于是去了次wc，抽了根烟 思考了一下。首先计算出总违规次数，有点思路了。

mysql> select u_v.uid,count(u_v.uid) from user_violation u_v group by u_v.uid;
+-----+----------------+
| uid | count(u_v.uid) |
+-----+----------------+
|   2 |              2 |
|   3 |              4 |
|   5 |              1 |
|   6 |              1 |
+-----+----------------+
4 rows in set (0.00 sec)


//然后利用内连接的方式将其结果串在一起。重温了当年死也不会犯下的笛卡尔集等等错误。艰难的把结果打印了出来。

//内连接：
mysql> select u_v.uid,count(u_v.uid),u.username from user u,user_violation u_v where u.id = u_v.uid group by u_v.uid;
+-----+----------------+----------+
| uid | count(u_v.uid) | username |
+-----+----------------+----------+
|   2 |              2 | local    |
|   3 |              4 | zhangsan |
|   5 |              1 | maliu    |
|   6 |              1 | zhangqi  |
+-----+----------------+----------+
4 rows in set (0.00 sec)


----------

总结了一下自己的失误：
#1）子查询所得的结果得到的只是简简单单的id罢了。我一直在进行聚合分组。忘记了子查询真正的用途。
#mysql> select u.id,u.username from user u where u.id in (select u_v.uid from user u,user_violation u_v where u.id = u_v.uid);

#2）笛卡尔集的错误。 
#A(0,1),B(1,2) 
#如果A.y != b.x 那么结果集就会交叉。


----------

#然后在接下来的半个小时里，我一直在盯着看自己写的代码
#莫名其妙的笑了。
#其实真的是一道很简单的问题，自己却把自己困在了原地。
#平复了一下心情，在想有没有别的解法，想尝试一下左、右外链接频繁失败。还在寻找原因。。。

//为什么内连接可以 外连接不行？
mysql> select u_v.uid,count(u_v.uid) from user_violation u_v left join user on u_v.uid = user.id  group by u_v.uid;
+-----+----------------+
| uid | count(u_v.uid) |
+-----+----------------+
|   2 |              2 |
|   3 |              4 |
|   5 |              1 |
|   6 |              1 |
+-----+----------------+
4 rows in set (0.00 sec)