mysql常用关键字

1.分组:group by,having,with rollup coalesce(a,b,c)

1.SELECT region, SUM(population), SUM(area)
    FROM bbc
    GROUP BY region
    HAVING SUM(area)>1000000
2.SELECT regagent,amount FROM `cy_pay_ok`  
    having amount>1000 ;
3.SELECT regagent,amount FROM `cy_pay_ok` 
     where amount>1000 ;
4.SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM  employee_tbl
     GROUP BY name WITH ROLLUP;

2.连接：inner join,left join, right join

3.排序 asc 升序，desc降序

4. null值处理：

is null : 当列的值是 NULL,此运算符返回 true
is not null : 当列的值不为 NULL, 运算符返回 true
<=> : 当两个值相等或者都为 null 时 返回true

5.事务处理

begin：
rollback;
commit;

6.alter命令

add
modify
change
drop

7.mysql 索引：普通索引，唯一索引，主键索引，全文索引，聚合索引

CREATE INDEX indexName ON table_name (column_name)
ALTER table tableName ADD INDEX indexName(columnName)
CREATE UNIQUE INDEX indexName ON mytable(username(length)) 
ALTER table mytable ADD UNIQUE [indexName] (username(length))

DROP INDEX [indexName] ON mytable


SHOW INDEX FROM table_name; \G

8.mysql导出数据

select * from [table_name] into outfile [file_path]

9.mysql导出数据

load data local infile [file_path] into table [table_name]


load data local infile [file_path] into table [table_name] (column_a,column_b,column_c)