from Info –查所有数据

mysql表格查询办法:

查询:

1.粗略询问

select * from Info –查全部数据
select Code,Name from Info –查钦点列的数据
select Code as ‘代号’,Name as ‘姓名’ from Info –给列内定别称

二.规则查询

select * from Info where Code=’p001′
select * from Info where Sex=’true’ and Nation=’n001′
–多条件并的关联
select * from Info where Sex=’true’ or Nation=’n00一’ –多条件或的涉嫌

三.限制查询

select * from Car where Price>40 and Price<50
select * from Car where Price between 40 and 50

4.离散询问

select * from Car where Code in (‘c001′,’c005′,’c010′,’c015’)
select * from Car where Code not in (‘c001′,’c005′,’c010′,’c015’)

伍.模糊查询

select * from Car where Name like ‘%BMW%’ –查包罗BMW的
select * from Car where Name like ‘BMW%’ –查以BMW初始的
select * from Car where Name like ‘%BMW’ –查以BMW最终的
select * from Car where Name like ‘BMW’ –查等于BMW的

select * from Car where Name like ‘__E%’ –查第多少个字符是E的

% 代表是随机多少个字符

_ 代表是一个字符

六.排序查询

select * from Car order by Price asc –以价格升序排列
select * from Car order by Price desc –以价格降序排列
select * from Car order by Oil desc,普赖斯 asc
–以五个字段排序,前面包车型地铁是主条件前边的是援救条件

7.分页查询

select top 5 * from Car
select top 5 * from Car where Code not in (select top 5 Code from Car)

当前页:page = 二; 每页展现:row = 10;

select top row * from Car where Code not in (select top (page-1)*row
Code from Car)

八.去重查询

select distinct Brand from Car

玖.分组查询

select Brand from Car group by Brand having count(*)>2

十.聚合函数(总括查询)

select count(*) from Car –查询全数数据条数
select count(Code) from Car –查询全数数据条数

select sum(Price) from Car –求和
select avg(Price) from Car –求平均
select max(Price) from Car –求最大值
select min(Price) from Car –求最小值

高档查询

一.连连查询

select * from Info,Nation –产生笛Carl积

select * from Info,Nation where Info.Nation = Nation.Code

select Info.Code,Info.Name,Sex,Nation.Name,Birthday from Info,Nation
where Info.Nation = Nation.Code

select * from Info join Nation on Info.Nation = Nation.Code –join on
的形式

二.一齐查询

select Code,Name from Info
union
select Code,Name from Nation

3.子查询

一条SQL语句中带有七个查询,在那之中二个是父查询(外层查询),另2个是子查询(里层查询),子查询查询的结果作为父查询的基准。

–查询民族为普米族的装有职员音信
select * from Info where Nation = (select Code from Nation where Name =
‘汉族’)

(一)非亲非故子查询

子查询能够独自实践,子查询和父查询未有必然的涉及

–查询类别是BMW5系的具有汽车音信
select * from Car where Brand =(select Brand_Code from Brand where
Brand_Name = ‘宝马5系’)

(2)相关子查询

–查找等速油耗低于该连串平均道路循环油耗的小车

select * from Car where Oil<(该类别的平均百公里油耗)
select avg(Oil) from Car where Brand = (该系列)

select * from Car a where Oil<(select avg(Oil) from Car b where
b.Brand = a.Brand)

 

相关文章