mysql常用语句

1、select count(*) from steam.review_pages where status=0 选取满足条件的数据条数

2、select top 3 game_id,page from steam.review_pages where status=0 选取前几条数据

3、update review_pages set status=1 where game_id=%s and page>%s 更新满足条件的字段

4、insert into steam_review_detail(review_id,game_id,user_id,crawl_date,crawl_time,review_time,play_hour,"
"review_lan,review_attitude,review_content,page,user_link) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
插入数据(插入数据需一一对应，否则报错)

5、replace into review_pages(game_id,page) values(%s,%s) 插入数据，有则跳过无则插入

6、select game_id,page from steam.review_page where status=0 limit %s,%s 选择满足条件的多少至多少的记录
6_1、select game_id,page from steam.review_page where status=0 limit %s,1 选择第%s条数据

7、create table if not exists steam_user_recent(
user_id char(25) not null,
game_id char(20) not null,
game_name char(255),
total_hour float(15,3),
week2_hour float(15,3),
crawl_date date,
primary key(user_id,game_id) 建表操作

8、INSERT INTO steam.steam_game_dailyuserinfo1 SELECT * FROM steam.steam_game_dailyuserinfo；
复制一个表到另一个表中

9、alter table steam_userDailyInfo rename to steam_userdailyinfo; 变更表名

10、select * from steam.review_page where game_id in (107410,203770,211820)； 选中多个条件

11、show global variables like ‘port’ 查看数据库端口号

12、desc table_name 查看表结构

13、alter table table_name modify column column_name char(50) 修改表中某字段类型

14、truncate table table_name 清空全部数据，不写日志，不可恢复，速度极快

15、select game_id,review_lan,sum_hour,count(game_id) as lan_count from (select hot.game_id,ori.review_lan,hot.sum_hour from (select game_id,sum(play_hour) as sum_hour from crawler.steam_review_detail group by game_id order by sum_hour desc limit 10) hot left join steam_review_detail ori on hot.game_id==ori.game_id) a group by game_id,sum_hour,review_lan order by sum_hour,lan_count desc；
查询top游戏中，评论中每个国家的分布。

16、alter table page_num add constraint PRI primary key(game_id,crawl_date); 更改表的主键，切记如果表中已有一个主键，要新添主键时需要同时写

17、

查看日志：
show variables like ‘log_bin’; 查看日志是否启用