SQL语句学习总结

(转载请注明出处：http://blog.csdn.net/buptgshengod)

1.归并重复项

原始mytable：

 +------+-------+
| user | brand |
+------+-------+
| aa   | 9     |
| aa   | 9     |
| bb   | 4     |
| bb   | 3     |
| cc   | 9     |
+------+-------+

输出重复的次数：

select user,brand, count(*) from mytable group by user,brand;

+------+-------+----------+
| user | brand | count(*) |
+------+-------+----------+
| aa   | 9     |        2 |
| bb   | 3     |        1 |
| bb   | 4     |        1 |
| cc   | 9     |        1 |
+------+-------+----------+

保存查询结果，并且删除count<2的项：

insert into table2(user,brand,count) select user,brand, count(*) from mytable group by user,brand ;

delete from talbes where count<2;

将txt文件导入数据库

LOAD DATA LOCAL INFILE "/Users/hakuri/Desktop/testSet.txt" INTO TABLE ali_test;

获取行数

select count(*)from table

两表的比对

表一：
+------+-------+-------+
| name | brand | other |
+------+-------+-------+
| fe   | 49    | fe    |
| wo   | 99    | 4g    |
| ef   | 23    | ij    |
| ig   | 41    | fe    |
+------+-------+-------+

表二
+------+-------+
| name | brand |
+------+-------+
| wo   | 99    |
| ig   | 40    |
| co   | 34    |
| ef   | 23    |
+------+-------+


如何生成表3
+------+-------+-------+
| name | brand | other |buy|
+------+-------+-------+
| fe   | 49    | fe    |0    |
| wo   | 99    | 4g    |1    |
| ef   | 23    | ij    |0       |
| ig   | 41    | fe    |1     |
+------+-------+-------+

就是添加第四列，如果两个表的name和brand同时相等，第四列buy的值得1，不等为0。求大神指点！

select a.name, a.brand, a.other, case when a.brand=b.brand then 1 else 0 end buy
from 表一 a left join 表二 b ON b.name=a.name

改名

alter table table_name change column column_name rename to new_name;