MySQL查询的⽅法_MYSQL查询⽅法mysql表格查询⽅法：
查询：
1.简单查询
select * from Info --查所有数据
select Code,Name from Info --查指定列的数据
select Code as ‘代号‘,Name as ‘姓名‘ from Info --给列指定别名
2.条件查询
select * from Info where Code=‘p001‘
select * from Info where Sex=‘true‘ and Nation=‘n001‘ --多条件并的关系
select * from Info where Sex=‘true‘ or Nation=‘n001‘ --多条件或的关系
3.范围查询
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‘)
5.模糊查询
select * from Car where Name like ‘%宝马%‘ --查包含宝马的
select * from Car where Name like ‘宝马%‘ --查以宝马开头的
select * from Car where Name like ‘%宝马‘ --查以宝马结尾的
select * from Car where Name like ‘宝马‘ --查等于宝马的
select * from Car where Name like ‘__E%‘ --查第三个字符是E的
% 代表是任意多个字符
_ 代表是⼀个字符
6.排序查询
select * from Car order by Price asc --以价格升序排列
select * from Car order by Price desc --以价格降序排列
select * from Car order by Oil desc,Price asc --以两个字段排序，前⾯的是主条件后⾯的是次要条件
7.分页查询
select top 5 * from Car
select top 5 * from Car where Code not in (select top 5 Code from Car)
当前页:page = 2; 每页显⽰：row = 10;
select top row * from Car where Code not in (select top (page-1)*row Code from Car)
8.去重查询
select distinct from
select distinct Brand from Car
9.分组查询
select Brand from Car group by Brand having count(*)>2
10.聚合函数(统计查询)
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 --求最⼩值
⾼级查询
1.连接查询
select * from Info,Nation --形成笛卡尔积
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 的形式
2.联合查询
select Code,Name from Info
union
select Code,Name from Nation
3.⼦查询
⼀条SQL语句中包含两个查询，其中⼀个是⽗查询(外层查询)，另⼀个是⼦查询(⾥层查询),⼦查询查询的结果作为⽗查询的条件。--查询民族为汉族的所有⼈员信息
select * from Info where Nation = (select Code from Nation where Name = ‘汉族‘)
(1)⽆关⼦查询
⼦查询可以单独执⾏，⼦查询和⽗查询没有⼀定的关系
--查询系列是宝马5系的所有汽车信息
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
MYSQL 查询⽅法
标签：数据  ati  esc  相关⼦查询  code  相关  笛卡尔积  卡尔  等于
本条技术⽂章来源于互联⽹，如果⽆意侵犯您的权益请点击此处反馈版权投诉

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。