(转载请注明出处：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;
