0611-01.数据库基础2

emp表中有以下列：
 empno    员工号
 hiredate 入职日期
 ename    名字
 sal      工资
 comm     佣金
 deptno   部门号

1.查询emp表中工资大于2500的人的所有信息
select * from emp where sal>2500;

3.查询emp表中名字为ALLEN或者KING的人的工资和姓名、部门号
select sal,ename,deptno from emp where ename='ALLEN' or ename='KING';
4.查询emp表中部门为20号部门或者工资大于1500并且小于2500的人的所有信息
select * from emp where deptno=20 or sal>1500 and sal<2500;

5.查询emp表中名字为KING或者部门号为30号部门的人的姓名，佣金，部门号
select ename,comm,deptno from emp where ename='KING' or deptno=30;

6.查询emp表中员工号为7369的人的所有信息
select * from emp where empno=7369;

7.查询emp表中工资范围在800到1000的人或工资范围在2000到2500的人的所有信息
select * from emp where sal between 800 and 1000 or sal between 2000 and 2500;

8.查询emp表中工资高于1500，2000，2500的任何一个的人的所有信息
select * from emp where sal>1500 or sal>2000 or sal>2500;

9.查询emp表中名字以AL开头的人或者佣金为空的人的所有信息
select * from emp where ename like 'AL%' or comm is null;

10.查询emp表中佣金不为空的人并且工资大于800，1500，2000的所有工资的人的所有信息
select * from emp where comm is not null and sal>800 and sal>1500 and sal>2000;


=================================================================================================================================
http://book.ecmoban.com/images/db.htm
1.多表查询
当一张表中的数据无法满足条件时候使用多表查询
a.笛卡尔积
两张表在不做任何关联情况下查询出来的数据，会使数据量成倍增加，无效数据增加，为避免此类情况，使用内联查询，左联，右联查询
select * from ecs_goods;
select * from ecs_category;
select * from ecs_goods,ecs_category;
b.内联查询
语法：
select 列 from 表1，表2 where 表1.列=表2.列;
select 列 from 表1 inner join 表2 on 表1.列=表2.列;
判断关联列的方法
	1.名字相同
	2.作用一致
	3.主外键关系
需求:查询商品名称是KD876的分类名称
	1.分析列
		商品名称
		分类名称
	2.分析表
		商品名称  ecs_goods
		分类名称  ecs_category
	3.关联表
		select * from ecs_goods,ecs_category
		where ecs_goods.cat_id=ecs_category.cat_id;
	4.过滤
		select cat_name from ecs_goods,ecs_category
		where ecs_goods.cat_id=ecs_category.cat_id
		and goods_name='KD876';
需求:查询分类名称是3G手机的商品名称有哪些
	1.分析列
		分类名称
		商品名称
	2.分析表
		goods_name ecs_goods
		cat_name   ecs_category
	3.关联
		select * from ecs_goods g,ecs_category c
		where g.cat_id=c.cat_id;
	4.过滤
		select g.goods_name from ecs_goods g,ecs_category c
		where g.cat_id=c.cat_id
		and c.cat_name='3G手机';

查询商品本店售价范围在2000和3000之间所有的商品的分类名称 
a.分析列
	shop_price
	cat_name
b.分析表
	ecs_goods
	ecs_category
c.关联
	select * from ecs_goods a,ecs_category b
	where a.cat_id=b.cat_id;
d.过滤
	select b.cat_name from ecs_goods a,ecs_category b
	where a.cat_id=b.cat_id
	and a.shop_price between 2000 and 3000;

补充：去重函数  distinct 
select distinct(b.cat_name) from ecs_goods a,ecs_category b
where a.cat_id=b.cat_id
and a.shop_price between 2000 and 3000;


查询分类名称是3G手机并且商品名称以诺基亚开头的商品名称 
select g.goods_name from ecs_goods g,ecs_category c
where g.cat_id=c.cat_id
and c.cat_name='3G手机'
and g.goods_name like '诺基亚%';


--4.jpg
1.select s#,grade from sc where c#='C2';
2.select sname from s where sname like 'D%';
3.
select s.s#,s.sname  from s,sc,c
where s.s#=sc.s#
and sc.c#=c.c#
and c.cname='Maths';
4.
select s# from sc where c# in ('C2','C4');

--2.jpg
1.select  s.name from student s,achievement a
where s.id=a.id and a.mark>85;
2.select count(*) from achievement where mark>=90;
3.
insert into achievement values(7,80);
4.
update achievement set mark=87 where id=3;
5.
delete from student where name='Betty';
delete from achievement where id=6;


2.安装sakila数据库
	1>下载sakila数据库
	https://dev.mysql.com/doc/index-other.html
	2>解压压缩包
	3>进入到解压后的文件夹，地址栏中输入cmd
	4>登陆mysql
		mysql -u root -p
	5>导入sakila-schema.sql;
		source sakila-schema.sql;
	6>导入sakila-data.sql;
		source sakila-data.sql;
-----------------------------------------
查询以C开头的国家有哪些城市?

	select c.city from country co,city c
	where co.country_id=c.country_id
	and co.country like 'C%';
查询客户表中名字为MARY的所在城市?
	select c.city from city c,address a,customer cu
	where c.city_id=a.city_id
	and a.address_id=cu.address_id
	and cu.first_name='MARY';

查询客户表中名字为MARY的所在国家?
	select co.country from 
	city c,address a,customer cu,country co
	where c.city_id=a.city_id
	and a.address_id=cu.address_id
	and co.country_id=c.country_id
	and cu.first_name='MARY';

子查询
一条SQL语句的执行结果依赖于另外一条SQL语句
子查询可以在数据新增、修改、删除、查询中使用
需求:查询商品名称是KD876的分类名称
	1.查询商品表中满足条件的cat_id
	select cat_id from ecs_goods where goods_name='KD876';
	2.查询分类表中cat_id为上面查询出来结果的分类名称
	select cat_name from ecs_category where cat_id=(select cat_id from ecs_goods where goods_name='KD876');
--2.jpg
3.insert into achievement values((select id from student where name='Robert'),80);
4.update achievement set mark=87 where id=(select id from student where name='Rose');
5.
delete from achievement where id=(select id from student where name='Betty');
delete from student where name='Betty';

子查询分为单行子查询和多行子查询
单行子查询：查询子句返回的结果是一行，可以用=或者in
多行子查询：查询子句返回的结果是多行，用in

需求:查询商品名称以诺基亚开头商品的分类名称
1.查询商品表中满足条件cat_id
select cat_id from ecs_goods where goods_name like '诺基亚%';

2.查询分类表中cat_id为上面查询出来结果的分类名称
select cat_name from ecs_category where cat_id in (select cat_id from ecs_goods where goods_name like '诺基亚%');



查询以C开头的国家有哪些城市?
1.查询国家表中满足条件的country_id
select country_id from country where country like 'C%';
2.查询城市表中country_id为上面查询出来结果的城市名称
select city from city where country_id in (select country_id from country where country like 'C%');

查询客户表中名字为MARY的所在城市?
1.查询客户表中满足条件的address_id
select address_id from customer where first_name='MARY';
2.查询地址表中add	ress_id为上面查询结果的city_id
select city_id from address where address_id in (select address_id from customer where first_name='MARY');
3.查询城市表中city_id为上面结果的城市名称
select city from city where city_id in (select city_id from address where address_id in (select address_id from customer where first_name='MARY'));

查询客户表中名字为MARY的所在国家?
select country from country where country_id in (
select country_id from city where city_id in (select city_id from address where address_id in (select address_id from customer where first_name='MARY')));


分组查询
按照某种属性进行分类
语法:select 列 from 表
		where 条件
			group by 分组条件
				having 分组后过滤条件;

1.分组函数、聚合函数
max     最大
min		最小
avg 	平均
sum     求和
count   计数
分组函数通常和分组一起使用，也可以单独使用
需求:查询ecs_goods表中最高本店售价，最低本店售价，平均本店售价
select max(shop_price),min(shop_price),avg(shop_price) from ecs_goods;
需求:查询ecs_goods表中最高本店售价的商品名称是？
select max(shop_price) from ecs_goods;
select goods_name from ecs_goods where shop_price=(select max(shop_price) from ecs_goods);

2.分组
每后面跟的就是分组条件
需求:查询city表中每个country_id对应多少个城市
select   country_id,count(city) from city group by country_id;
需求:查询每个国家有多少个城市，要求显示国家名称，城市数量
select * from country co,city c
where co.country_id=c.country_id;

select co.country,count(c.city) from country co,city c
where co.country_id=c.country_id
group by co.country;

3.having
where   分组前过滤，后面不能直接跟分组函数
having  分组后过滤，后面可以直接跟分组函数

需求:查询城市数量超过50的国家名称
select co.country,count(c.city) from country co,city c
where co.country_id=c.country_id
group by co.country
having count(c.city)>50;

select co.country,count(c.city) from country co,city c
where co.country_id=c.country_id
and count(c.city)>50
group by co.country;


排序和分页
语法：
select 列 from 表
	where 条件
		group by 分组条件
			having 分组后过滤条件
				order by 排序条件;
排序
	顺序(从小到大)
	order by 列 
	order by 列 asc
	倒序(从大到小)
	order by 列 desc
	需求:查询city表中所有数据，按照country_id顺序排列
	select * from city order by country_id;
	需求:查询city表中所有数据，按照country_id从大到小排列
	select * from city order by country_id desc;
	补充：双排序
	select * from city order by country_id desc,city_id ;

分页(limit)
	需求:查询city表中前10条数据
	select * from city limit 10;
	需求:查询city表中10到20行数据
	select * from city limit 10,10;


左联查询（左表中所有数据都有）
语法:select 列 from 左表 left join 右表 on 左表.列=右表.列；
结果:内联查询结果+左表中有右表中没有的右表显示为空

create table a1(
	a  int
);
insert into a1 values(1),(2),(3);
create table a2(
	a int,
	b char(10)
);
insert into a2 values(1,'a'),(2,'b'),(4,'d');
select * from a1;
select * from a2;
select * from a1,a2 where a1.a=a2.a;
select * from a1 left join a2 on a1.a=a2.a;

右联查询(右表中所有数据都有)
语法:select 列 from 左表 right join 右表 on 左表.列=右表.列；