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;