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)