数据库sql常用查询
1.and
(逻辑与) 关键字 放在where之后 用做连接多个条件的连接符
select * from table_name where column_name1 > 200 and column_name2 =1;
同时满足2个条件
2.or
(逻辑或) 运算优先级低于and
满足2个条件中的一个
select * from table_name where column_name1 > 200 or column_name2 = 1;
3. in
(逻辑或)表示参数与in之后的集合匹对,有对应关系则显示,没有则忽略
select * from table_name where column_name in(1,2);
4.between… and …
表示在…和…这两个参数区间 包含前也包含后
select * from table_name where column_name between (0) and (1000);
5.gounp by
分组函数 又叫聚合函数
select column_name1,column_name2 from table name group by column_name1;
6.order by
排序函数 默认按照所传参数列升序降序 order by column_name desc
1.asc关键字,一般可以省略
2.desc关键字规定了 排序方式按照降序排列
select * from table_name order by column_name;
select * from table_name order by column_name desc;
7.having…
条件函数 表示满足…条件
select column_name1,avg(column_name2) from table_name group by column_name1 having avg(column_name2)>20000;
8.distinct
去除重复数据属性
select distinct col_name from table_name;```
9. as
别名 一般可以省略
select *,column_name as 'a' from table_name;
10.limit
limit 5 取条数函数 表示从开始索引取5条数据
select * from table_name limit 5;
limit 2,3 表示从下标为2的数据开始取,取3条数据
select * from product limit 2,3;
11.like
模糊查询关键字 通配符 1.代表一个字符位/占位符 2.%代表0个或多个字符位/占位符 select * from table_name where column_name like’ %%’
查讯名字中带有的‘策’的人:
select * from product where pname like '%策%';
查讯姓孙的,且3个名字的人(4个_):
select * from product where pname like '孙____';
查讯姓孙的,且2个字名字的人(2个_):
select * from product where pname like '孙__';
一般来说 where-- group by-- having --order by – limit;
例子:
select cid,pid,price,avg(price),count(pid) from product where price > 0 group by cid order by pid desc limit 0,2;
以上是今天我学到的查询,明天学联合查询。where没有写不过就和if差不多一个意思。╮(╯_╰)╭