MySQL语句学习

1.命令行：mysql -u root -p 输入密码进入。

2.mysql语句:

#只显示id在2000和2000+500之间的条目
select * from watch_tbl order by id limit 2000,500;
#显示id在1和200000之间的条目
select * from watch_tbl order by id limit 200000;
select * from usr_tbl;
select * from play_tbl;
select * from video_tbl;
select * from errorlog_tbl;

#显示表结构属性
show create table watch_tbl;
show create table usr_tbl;
show create table play_tbl;
show create table video_tbl;
show create table errorlog_tbl;

3.

#清空表数据，不可恢复
truncate table watch_tbl;
truncate table usr_tbl;
truncate table video_tbl;
truncate table play_tbl;
#清空表数据，可恢复
-- delete from watch_tbl 

#复制表结构和数据到新表中，新表旧表格式和数据完全一样；
-- CREATE TABLE watch_tbl SELECT * FROM temp.watch_tbl;
-- CREATE TABLE usr_tbl SELECT * FROM temp.usr_tbl;
-- CREATE TABLE video_tbl SELECT * FROM temp.video_tbl;
-- CREATE TABLE play_tbl SELECT * FROM temp.play_tbl;

#将旧的表数据导入新表中，新表旧表数据格式可能不完全一样；
INSERT INTO watch_tbl SELECT * FROM temp.watch_tbl;
INSERT INTO usr_tbl SELECT * FROM temp.usr_tbl;
INSERT INTO video_tbl SELECT * FROM temp.video_tbl;
INSERT INTO play_tbl SELECT * FROM temp.play_tbl;

insert play_tf_tbl(id,video_id,user_id,play_id) select id,video_id,user_id,id from play_tbl;
insert new_table(col1,col2,col3) select o_col1,o_col2,o_col3 from old_table;

4.

#统计满足条件的条目的个数
select count(ID) from usr_tbl where real_gender=0;
select count(id) from usr_tbl where real_gender=0;
SELECT COUNT(user_id) FROM play_tbl WHERE video_id=3;
#合并表然后根据条件查找，最后在计算满足条件的个数。
select distinct user_id ,  video_id, real_gender, real_age 
from  watch_tbl left join usr_tbl on watch_tbl.user_id=usr_tbl.id where video_id=1;
select count(user_id) from (
select distinct user_id , play_id, video_id, real_gender, real_age 
from watch_tbl left join usr_tbl on watch_tbl.user_id=usr_tbl.id where video_id=1)as table1;

5.

#显示MySQL的线程连接信息
show status like '%thread%';
show variables like '%sql_mode';

6.删除 表中某些行的信息

select id from (select * from watch_tbl_va  where video_id=1) as a;
delete from watch_tbl_va where id in (select a.id from (select * from watch_tbl_va  where video_id=1) as a);
delete from watch_tbl where id in (select id from (select * from watch_tbl  where video_id=1) as a);

7.Error Code: 1175. You are using safe update mode and you tried to update a 

解决方案：show variables like 'SQL_SAFE_UPDATES';
SET SQL_SAFE_UPDATES = 0;
 8.

select * from test20170602.watch_tbl_tester limit 200000;
select count(va_temp.user_id) from (select distinct user_id , play_id, video_id, real_gender, real_age 
                    from watch_tbl_va left join usr_tbl on watch_tbl_va.user_id=usr_tbl.id where video_id=4 and real_age=3)
					as va_temp left join play_tf_tbl on va_temp.user_id=play_tf_tbl.user_id and va_temp.play_id=play_tf_tbl.play_id where play_tf_tbl.value=1;

select count(va_temp.user_id) from (
                    select distinct user_id , play_id, video_id, real_gender, real_age 
                    from watch_tbl_va left join usr_tbl 
                    on watch_tbl_va.user_id=usr_tbl.id 
                    where video_id=4  )
					as va_temp left join play_tf_tbl on va_temp.user_id=play_tf_tbl.user_id and va_temp.play_id=play_tf_tbl.play_id where play_tf_tbl.value=1;


select count(user_id) from (
                    select distinct user_id , play_id, video_id, real_gender, real_age 
                    from watch_tbl_va left join usr_tbl 
                    on watch_tbl_va.user_id=usr_tbl.id 
                    where video_id=4  and real_gender=0)as table1