别名查询,使用关键词as
select * from student as p;
as可省略,且别名意义不大,一般用在多表查询中:
select s.id,t.id from student s,teacher t;或者select student.id,teacher.id from student,teacher;
列起别名:select name as ‘商品名’ ,price ‘价格’ from product;
去掉重复值:select distinct price from product;(去除price这一列重复的值)
select distinct * from student;(去除重复的行)
查询结果是表达式:select name,price+10 new_price from product;
意思就是查询的Price+10输出,真实数据库中的数据不变
特殊使用:
select 6-2;
select 6/2;
select least(10,20,30); 10 select least(10,null,30); null — 用least求最小
select greatest(10,20,30); 10 select greatest(10,null,30); null — 用greatest求最小
求最大值,最小值时,有null的话,直接为null。
价格在不等于100的物品:
select * from student where price!=100; //…where price <>
800;//…where not (price=800);
价格在200到1000之间的所有商品:
select * from product where price between 200 and 500;
…where price>=200 and price<=1000;
…where price>=200 && price<=1000;
价格是200或800的所有商品:
select * from student where price in (200,800);
… where price =200 or price =800;
…where price =200|| price =800;
查询含有‘裤’字的商品(模糊查询)
select * from student where pname like ‘%裤%’; %用来匹配任意字符
查询第二个字为‘蔻’的所有商品
select * from student pname like ‘_蔻%’; 下划线匹配单个字符
查询price为空/不同的值:
select * from student where price is null; 不能用=,因为null和谁都不相等
…where price is not null;
select 3&5; -- 位与
select 3|5; -- 位或
select 3^5;--位异或
select 3>>1; --位右移
select 3<<1; --位左移
select ~3; --位取反
价格升序(降序)
select * from student order by price desc; desc为降序,默认为升序asc。
聚合查询
聚合函数是对应的列进行操作
select count(id) from student; // select (*) from student; (求该表的非空行数)
select sum(price) from student where ....(价格的总和)
select max(price),min(price) from student where.....(price的max,min值)
select avg(price) from student where.....(求price的平均值)
关于聚合查询对null的处理:如果所处理的一列为null,忽略
分组查询
注意:分组后,select后面只能写分组字段和聚合函数。
select category_id,count(id) from student group by category_id;
select category_id,count(id) cnt from student group by category_id having cnt>4;
select category_id,count(id) cnt from student group by category_id where cnt>4;是错误的,因为where后面不能是聚合函数。
分页查询每页显示一定数目的数据
select * from student limit 5;每页显示5条数据
select * from student limit 6,6;
第一页编码是从0开始的,所以在第七页开始,显示接下来的6页。
插入语句,即一张表的数据插入到另一张表中
insert into student(category_id,count) select category_id,count(id) from student group by category_id;
ifnull(comm,0) 表示comm的值如果为空则当做0,否则还为原来的值。