MySQL select 的五种子句

1.where 条件查询
 比较运算符:
	等于		=
	不等于		!= 或者 <>
	小于		<
	小于等于		<=
	大于(等于)		>(=)
	大于等于		>=
	在某集合内	in	(等于in(a,b,...,n)中的任意一个值都行
	在某个范围内	between	(在between a and b 之间的值都可以,允许等于边界值)
        逻辑运算符:
	逻辑非		NOT 或 !
	逻辑或		OR 或 ||
	逻辑与		AND 或 &&
        模糊查询:
	像		like	
	通配符:
	通配任意字符	%	(like(诺基亚%))
	通配单个字符	_	(like(诺基亚N__))

2.group by 分组

运用在统计场合
	五个统计函数:	
	求最大		max	(select max(goods_price) from goods;)
				(select * from goods where goods_price = (select max(goods_price) from goods);)
				(select cat_id,max(goods_price) from goods group by cat_id;)
	求最小		min
	求总和		sum	(select sum(goods_number) from goods;)
	求平均		avg	(select avg(goods_price) from goods;)
	求总行数		count	(select count(*) from goods;)
可以给列或者计算结果取别名:as	(select cat_id sum(goods_number * goods_price) as ht from goods group by cat_id;)

3.having 筛选

算出挂科超过两科(包括两科)学生的平均分
create table stu (name varchar(10),class varchar(10),score tinyint);

insert stu
(name,class,score)
values
("zhangsan","shuxue",50),
("zhangsan","dili",40),
("zhangsan","shuxue",90),
("lisi","yuwen",55),
("lisi","zhengzhi",45),
("wangwu","zhengzhi",30);

select name,avg(score) from stu group by name;

select name,sum(score<60) from stu group by name;

select name,sum(score<60) as gk,avg(score) as pj from stu group by name having gk>=2;

4.order by 排序

	默认升序(显示声明用 asc),用 desc 可以设置为降序排列
	select * from stu order by name desc;


mysql> select * from stu order by name desc;
+----------+----------+-------+
| name     | class    | score |
+----------+----------+-------+
| zhangsan | shuxue   |    50 |
| zhangsan | dili     |    40 |
| zhangsan | shuxue   |    90 |
| wangwu   | zhengzhi |    30 |
| lisi     | yuwen    |    55 |
| lisi     | zhengzhi |    45 |
+----------+----------+-------+
6 rows in set (0.00 sec)


	select * from stu order by name,score desc;	


mysql> select * from stu order by name asc,score desc;
+----------+----------+-------+
| name     | class    | score |
+----------+----------+-------+
| lisi     | yuwen    |    55 |
| lisi     | zhengzhi |    45 |
| wangwu   | zhengzhi |    30 |
| zhangsan | shuxue   |    90 |
| zhangsan | shuxue   |    50 |
| zhangsan | dili     |    40 |
+----------+----------+-------+
6 rows in set (0.00 sec)
5.limit 限制结果条数
limit[offset,]N
offset:偏移量
N:取出的条目
offset如果不写,代表0,即(limit 0,3)等于(limit 3)
select * from stu order by name,score desc limit 2,2;

select * from stu order by score ;



以上5种子句如果需要同时使用,则必须按照以上顺序出现




评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值