数据库—内容的查询语句

--and 代表的是与,意思是左右两边的判断需要同时满足
select * from products where unitPrice > 20 and categoryID=1


--or
select * from products where unitPrice>50 or categoryID=8


--not   不等于
select * from products where not unitPrice=97

精准查询    
insert into products (productid,productName) values(78,'老干妈')


--is null代表是空的,不能写成=null
select * from products where categoryID is null

select * from products where categoryID is not null

--between
select * from products where unitPrice between 10 and 20


--in 不连续的情况
select * from products where categoryID in (1,2,8)
select * from products where categoryID =1 or categoryID=2 or categoryID=8


--模糊查询
-- _指匹配任意一个字符
select * from products where productName like '_niseed Syrup'
-- %指匹配零个或多个字符
select * from products where productName like 'Chai%'


select * from products where productName like '%ob%'






--聚合函数


--统计 count
select count(*) 总数量 from products
select count(productID) from products
--如果字段为null,则不计入统计
select count(categoryID) from products




--求和 sum
select sum(unitPrice) 所有商品的总价格 from products
select sum(unitsinstock) from products


--求平均数
select avg(unitPrice)  from products
select sum(unitPrice)/count(productID) from products








--分组 group by
select count(productID),categoryID from products group by categoryID
--having 指对于group by之后结果进行判断 where 指对于原来的数据结果进行判断
select count(productID),categoryID from products group by categoryID having count(productID) >=10


--max
select max(unitPrice) from products


--min
select min(unitPrice) from products






--order by
--desc降序
select * from products order by unitPrice desc
--asc
select * from products order by unitPrice asc


select count(productID) from products where unitPrice>10 group by categoryID having count(productID) >5 order by count(productID) asc








--(a+b)(c+d)=ac+ad+bc+bd


--交叉连接,会得到一个笛卡尔乘积
--78*8=624
select * from products cross join categories
select * from products,categories




--自然连接,如果连接的字段有空值,则不会对应
select * from products natural join categories
select a.productname,b.categoryname from products a natural join categories b


select * from products a,categories b where a.categoryid=b.categoryid




select count(productID),categoryID from products group by categoryID




select * from categories a,(select count(productID) 商品数量,categoryID from products group by categoryID) b where a.categoryID=b.categoryID




--自连接
create table department
(
did int,
dname varchar(20),
parentid int
)
alter table department add constraint pk_did primary key(did)




insert into department(did,dname,parentID) values(1,'董事会',null);
insert into department(did,dname,parentID) values(2,'财务部',1);
insert into department(did,dname,parentID) values(3,'技术部',1);
insert into department(did,dname,parentID) values(4,'财务一部',2);
insert into department(did,dname,parentID) values(5,'财务二部',2);
insert into department(did,dname,parentID) values(6,'技术一部',3);
insert into department(did,dname,parentID) values(7,'技术二部',3);




select * from department


select a.dname 上级部门,b.dname 部门名称 from department a,department b where a.did=b.parentID


--左连接,左外连接
--(+) 用来标记右表
select * from products a,categories b where a.categoryID=b.categoryID(+) order by productID
select * from products a left outer join categories b on a.categoryid=b.categoryid






--右连接
select * from products a,categories b where a.categoryID(+)=b.categoryID order by productID
select * from products a right outer join categories b on a.categoryid=b.categoryid




--全连接
select * from products a full outer join categories b on a.categoryID=b.categoryID
insert into categories(categoryid,categoryName) values(9,'饮料')