基本的select语句包含以下4部分内容:
select select_list
from table_list
where search_conditions group by group_by_list having search_condition
order by order_list.
下来详细描述下:
1、where常用运算符:
比较运算符
> , < ,= , != (< >),>= , <=
in(v1,v2..vn)
between v1 and v2 在v1至v2之间(包含v1,v2)
逻辑运算符
not ( ! ) 逻辑非
or ( || ) 逻辑或
and ( && ) 逻辑与
模糊查询
like 像
通配符:
% 任意字符
_ 单个字符
where goods_name like '诺基亚%'
where goods_name like '诺基亚N__'
2、group by 分组
一般情况下group需与统计函数(聚合函数)一起使用才有意义
如:select goods_id,goods_name,cat_id,max(shop_price) from goods group by cat_id;
这里取出来的结果中的good_name是错误的!因为shop_price使用了max函数,那么它是取最大的,而语句中使用了group by 分组,那么goods_name并没有使用聚合函数,它只是cat_id下的第一个商品,并不会因为shop_price改变而改变
3、having 与where 的异同点
where搜索条件实在分组操作之前对记录进行筛选,然后再由group by对筛选后符合田间的进行分组。而Having搜索条件则是对分组操作之后得到的行在进行筛选操作。在Having子句之中可以包含聚合函数,而where子句不能。
where针对表中的列发挥作用,
查询数据
having对查询结果中的列发挥作用,
筛选数据
#查询本店商品价格比市场价低多少钱,输出低200元以上的商品
select goods_id,good_name,market_price - shop_price as s
from goods havings
>200 ;
//这里不能用where因为s是查询结果,而where只能对表中的字段名筛选
如果用where的话则是:
select goods_id,goods_name from goods where market_price - shop_price > 200;
#同时使用where与having
select cat_id,goods_name,market_price - shop_price as s from goods where cat_id = 3 having s > 200;
#查询积压货款超过2万元的栏目,以及该栏目积压的货款
select cat_id,sum(shop_price * goods_number) as t from goods group by cat_id having s > 20000
#查询两门及两门以上科目不及格的学生的平均分
思路:
#先计算所有学生的平均分
select name,avg(score) as pj from stu group by name;
#查出所有学生的挂科情况
select name,score<60 from stu;
#这里score<60是判断语句,所以结果为真或假,mysql中真为1假为0
#查出两门及两门以上不及格的学生
select name,sum(score<60) as gk from stu group by name having gk > 1;
#综合结果
select name,sum(score<60) as gk,avg(score) as pj from stu group by name having gk >1;
4、order by
(1) order by price //默认升序排列
(2)order by price desc //降序排列
(3)order by price asc //升序排列,与默认一样
(4)order by rand() //随机排列,效率不高
#按栏目号升序排列,每个栏目下的商品价格降序排列
select * from goods where cat_id !=2 order by cat_id,price desc;
基本查询其实挺简单的。下面主要说一下级联查询。
1、where型子查询
(把内层查询结果当作外层查询的比较条件)
#不用order by 来查询最新的商品
select goods_id,goods_name from goods where goods_id = (select max(goods_id) from goods);
#取出每个栏目下最新的产品(goods_id唯一)
select cat_id,goods_id,goods_name from goods where goods_id in(select max(goods_id) from goods group by cat_id);
2、from型子查询
(把内层的查询结果供外层再次查询)
#用子查询查出挂科两门及以上的同学的平均成绩
思路:
#先查出哪些同学挂科两门以上
select name,count(*) as gk from stu where score < 60 having gk >=2;
#以上查询结果,我们只要名字就可以了,所以再取一次名字
select name from (select name,count(*) as gk from stu having gk >=2) as t;
#找出这些同学了,那么再计算他们的平均分
select name,avg(score) from stu where name in (select name from (select name,count(*) as gk from stu having gk >=2) as t) group by name;
3、exists型子查询
(把外层查询结果拿到内层,看内层的查询是否成立)
#查询哪些栏目下有商品,栏目表category,商品表goods
select cat_id,cat_name from category where exists(select * from goods where goods.cat_id = category.cat_id);
4.交叉连接:cross join
即让两个表进行笛卡尔积。数目等于:表1的数量*表2的数量
语法格式: select * from table1 cross join table2;
5.内连接
使用=、>、<进行条件连接。
语法:select* from table1 inner join table2 on table1.colmn = table2.column ,其中inner可以省略。
select* from table1 join table2 on table1.column = table2.column
内链接的效果与等值连接差不多。
6.外连接
外连接有三种不同的连接方法:
左外:left join .. on
右外:right join .. on
完全外部连接:full join .. on
下来看下事例:
create table employee(
id int,
d_id int,
name varchar(32),
age int,
sex varchar(32),
homeaddr varchar(32)
);
create table department(
d_id int,
d_name varchar(32),
function varchar(32),
address varchar(32)
);
insert into employee values (1,1001,'张三',26,'男','北京市海淀区'),(2,1001,'李四',19,'女','北京市昌平区'),(3,1002,'王五',25,'男','湖南长沙市'),(4,1004,'Aric',15,'男','englian')
insert into department values (1001,'科技部','研发产品','3号楼5曾'),(1002,'生产部','生产产品','5号楼1层'),(1003,'销售部','销售产品','1号楼销售大厅')
左外查询:显示符合条件的数据行,同时显示左边数据表不符合条件的数据行,右边没有对应的条目显示NULL
select * from employee left join department on employee.d_id = department.d_id
我们发现结果的数目与empoyee表的数目相同。最后一个aric中在department里面没有数据,用null 填补。
右外查询:
显示符合条件的数据行,同时显示右边数据表不符合条件的数据行,左边没有对应的条目显示NULL
select * from employee right join department on employee.d_id = department.d_id
我们发现这时候的查询结果,保持右表的基准。但是数目却不同~ 右表有三个,但是最后的结果有四个。
原因是有两个人的d_id都是1001.
7.全外连接:
显示符合条件的数据行,同时显示左右不符合条件的数据行,相应的左右两边显示NULL