Sql语句之select 5种查询

select 5种子句：注意顺序
where / group by /having / order by / limit /

清空表中的数据：truncate 表名；

导入表结构（不含数据）: create table 表2 like 表1;

删除表：drop table 表名；

导入数据：insert into g2 select * from stu order by name, fenshu desc;

//从临时表中查询=========子查询
select * from (select * from stu order by name, fenshu desc) as tmp group by name;

where 表达式 把表达式放在行中，看表达式是否为真；
列 当成 “变量” 来理解 可以运算
查询结果 当成“临时表” 来理解

子查询：3种：
where型子查询：把内层的查询结果作为外层子查询的条件
select * from stu where fenshu=(select max(fenshu) from stu);
select * from stu where fenshu in (select max(fenshu) from stu group by name);

from 型子查询：把内层的查询结果当成临时表供外层继续查询：
select * from (select * from stu order by name, fenshu desc) as tmp group by name; //必须有as tmp做别名，否则报错；

exists型子查询：（难点）
把外层的查询结果拿到内层，看内层的查询是否成立；

select * from stu where exists (select * from goods where goods.cat_id = category.cat_id);

select 查询5子句之order by：
根据字段进行排序而已：
若要倒序排列则用“desc”来声明一下即可
显示声明升序排列用“asc”来声明；

select name, fenshu from stu order by fenshu, name desc;

limit关键字；起到限制条目作用；

limit [offset]，N

offset:偏移量， 默认是0；
N：取出的条目
取第3行之后的4行；
select * from stu order by fenshu desc limit 3, 4;

 

select 查询5子句之having查询：（用于在缓冲区的查询，而不能在表中（即mysql的文件）查询）
select good_id, goods_name, market_price - shop_price as sheng from goods where
market_price > 200;

select good_id, goods_name, market_price -shop_price as sheng from goods having
sheng > 200;

select good_id, goods_name, market_price -shop_price as sheng from goods where cat_id = 3 having sheng >200;

select cat_id.sum(shop*goods_number) as huokuan from group by cat_id;

select cat_id.sum(shop*goods_number) as huokuan from group by cat_id having huokuan > 20000;

#每个人的平均分
select name, avg(scores) from stu group by name;

#每个人的挂科情况；
select name.scores < 60 from stu;

#每个人的挂科科目数目：
select name, sum(score < 60) from stu group by name;

select name, sum(score<60), avg(scores) as pj from stu group by name;