mysql手册总结2
来源:互联网 发布:网络平台招商方案 编辑:程序博客网 时间:2024/05/22 10:55
不再复制了,直接给链接得了,复制的太崩溃了。。
mysql手册地址:
http://dev.mysql.com/doc/refman/5.0/en/index.html
1 count(*)和 group by配合使用
初始表:
+---------+------+------------+
| address | sex | birth |
+---------+------+------------+
| tianjin | m | 1988-09-22 |
| beijing | f | 1988-09-22 |
| beijing | f | 1988-01-22 |
+---------+------+------------+
如果这么用:
select address, count(*) from friend group by address;
显示如下:
+---------+----------+
| address | count(*) |
+---------+----------+
| beijing | 2 |
| tianjin | 1 |
+---------+----------+
好像是正确,可是如果这么运行:
select birth, count(*) from friend group by address;
显示如下:
+------------+----------+
| birth | count(*) |
+------------+----------+
| 1988-09-22 | 2 |
| 1988-09-22 | 1 |
+------------+----------+
也就是说 分完组之后,显示select指出的东西,我这里指定显示birth,它会傻瓜式的选择这组里的第一个。
count数的是每个组有多少个组,如果不用groupby会把其当成一个大组。
2
在写mysql时候,注意换行这样思路容易比较清晰。
mysql> select event.name, age, address, date, descrip from
-> event INNER JOIN family
-> ON event.name = family.name
-> ;
看看牛人的写法:
SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.price < s2.price
WHERE s2.article IS NULL;
把每个从句的关键字写在每行的前面,每行只写一个大从句。
3
举例说明内连接,左连接,右连接的区别
原始table family是这样的
+------+------+---------+
| name | age | address |
+------+------+---------+
| jack | 23 | beijing |
| meng | 22 | tianjin |
+------+------+---------+
就这两个条目,先进行左连接:
mysql> select * from family f1 LEFT JOIN family f2 ON f1.age < f2.age;
+------+------+---------+------+------+---------+
| name | age | address | name | age | address |
+------+------+---------+------+------+---------+
| jack | 23 | beijing | NULL | NULL | NULL |
| meng | 22 | tianjin | jack | 23 | beijing |
+------+------+---------+------+------+---------+
2 rows in set (0.00 sec)
相当于对左边的f1中的每一条都试图根据condition来找一条连接后的记录,找到了没事,没找到就将其和f2的一条null连接。
右连接:
select * from family f1 RIGHT JOIN family f2 ON f1.age < f2.age;
+------+------+---------+------+------+---------+
| name | age | address | name | age | address |
+------+------+---------+------+------+---------+
| meng | 22 | tianjin | jack | 23 | beijing |
| NULL | NULL | NULL | meng | 22 | tianjin |
+------+------+---------+------+------+---------+
2 rows in set (0.00 sec)
内连接
mysql> select * from family f1 INNER JOIN family f2 ON f1.age < f2.age;
+------+------+---------+------+------+---------+
| name | age | address | name | age | address |
+------+------+---------+------+------+---------+
| meng | 22 | tianjin | jack | 23 | beijing |
+------+------+---------+------+------+---------+
1 row in set (0.00 sec)
3个连接的区别不用讲了吧?百闻不如一见。。自然就懂了。。
- mysql手册总结2
- mysql手册总结1---入门
- mysql手册
- Mysql手册
- Mysql 手册
- MySQL管理员手册
- Mysql安装手册
- MySql安装手册
- mysql在线中文手册
- MYSQL 手册【摘】
- mysql 正则表达式手册
- MySQL速查手册
- MySQL DBA 常用手册
- MySQL DBA 常用手册
- MySQL数据库维护手册
- mysql安装手册图解
- MySQL集群手册
- mysql 安装手册(转)
- 国外书签网站
- How To Delete Archive Log Files Out Of +Asm
- 经典的原型链例子。
- 数据库中乐观锁与悲观锁的概念
- 改变dialog的位置
- mysql手册总结2
- c++ completion (vim)
- 数据库可疑处理
- Order a column conditional in SQL
- 组合和聚合
- JTable 获得选中行
- Java泛型
- vb.net读取任意xml文件
- PHP文件上传测试