#sql语句数据行操作补充
#增加:
#insert into table_name(字段1,字段2) values('属性1','属性2'),('属性1','属性2') 插入多行数据
#insert into t1(字段1,字段2) select 字段1,字段2 from t2; (某张表的某几列数据选择出来,插入)
#删除:delete from table_name [where id =/!=/>/< /<> 2] and/or name='啥啥啥'
#truncate table t1;
#改: update t1 set name='gkx' ,age=19 where id>12 and name='x' #可以同时改多个字段
'''#查:内容较多:
# 查所有,查部分字段,后面跟 where条件,查完后排序
#select id,name from t1 where id>10 and name = 'aaa'
#select id as setid ,name as setname from t1 where id>10 and name = 'aaa' # as 别名
#as 别名
#select name as cname ,age,1 from t1;
#这里的 1表示 增加一列,列名为1,该列所有行属性也都为1
#select * from t1 where id in (1,2,5); #如果id在 1,2,5就选择出来
#select * from t1 where id not in (1,2,5); #id除了1,2,5其他都选择出来
#select * from t1 where id in (select id from t2 where id>2) ;# 根据选择来选择
#select * from t1 where id between 5 and 12;
#闭区间 5和12也能取到
#select * from dmsxb where b_dm in (select b_dm from dmsxb group by b_dm having count(*)>1)
##还可以选择查询语句,不过这个语句结果需要为一个常量 select age,name,(select count(1) from tb) from tb1;
#习题17和21
#条件 ## case when 条件 then 【字段或者默认值】 else 字段 END
#select course_id,max(num),min(num),cname,
# case when min(num) < 10 THEN 0 else min(num) end from score left join course on score.course_id = course.cid group by course_id;
#if 三元运算
# select course_id,cname,tname,avg(if(isnull(score.num),0,score.num)) from score
#通配符:
#下划线 : 表示后边只能匹配一位,select * from tb12 where name like "a_"
#百分号 : select * from tb12 where name like "a%"'''
#limit:比如百度搜索,一次只显示一页,放置C/S端压力
#select *from t1 limit 10; #显示前十条 相当于select *from t1 limit 0,10;
#select * from t1 limit 4,5; #从第4行开始的5行 (不包含第4行)
#select * from t1 limit 5 offset 4 #从第4行开始的五行
#实现分页:
#page = int(input('page')) select * from limit (page-1)*10 , 10; 前者为起始位置
#select * from t1 order by id desc limit 10; (后10行:先降序排列,再选前十行)
'''limit 数值越大越慢,比如 limit 300000,10 它会先扫描30万行,然后再取下10行'''
#排序:
#select * from t1; #默认是从小到大排 即 asc ----> ascending
#select * from t1 order by 列名 desc; 即 desc ----> descending
#select * from t1 order by 列1 desc,列2 asc ; #根据列1从大到小排列,如果相同按列2从小到大排列