Mysql常用sql语句

1 清空表

TRUNCATE TABLE `edu_comment`;

2 添加字段

alter table e_down add soft_version1 varchar(50)

3 mysql导出sql文件

mysqldump -utongzhoushidai -ptongzhoushidai demo_web_tongzhoushidai> /data/htdocs/demo_web_tongzhoushidai.sql

4 mysql导入sql

mysql -utongzhoushidai -ptongzhoushidai -h 111.111.111.111 web_tongzhoushidai </data/htdocs/web/demo_webtongzhoushidai.sql

5 修改字段长度

alter table user modify column name varchar(50) ;

6 索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

7 两表删除

delete t1 from t1,t2 where 条件

8  count 带查询条件

COUNT(IF(exam_practice_rule_record.is_open = 0,TRUE,NULL))

9 修改表字符集：

ALTER TABLE edu_user_profile CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;