SQL学习笔记查询

基本语法

Select [all | distinct] select_fields_list      -- all 显示所有含重复数据  distinct 不显示重复数据
  From  table_list/view_list                    -- 表或视图 可以是一个或多个表
  [Where conditions]                            -- 条件，可以在一个表 或 多个表
  [group by group_list]                         -- 按需要分组
  [having conditions]                           -- 用于分组的条件 相当于group 的 where
  [order by order_list]                         -- 按制定要求排序   asc升 desc降 newid()随机排序,其他有用random()的

查询语句执行顺序，一般为

FROM JOIN -- WHERE -- GROUP BY -- HAVING -- SELECT -- ORDER BY -- LIMIIT

比较运算符

=、>、< 、<>、（!=）、>=、<=、!>、!<
not、and 、or、between..and    --  Where 工资 between 1000 and 2000
in(列表值1、列表值2、……..)
is (not) null空值查询
like 模糊查询
%   包含0个或多个字符
_    包含一个字符
[ ]   指定范围如[a-z]、[1-9]
[^]  不属于指定范围 [^a-z]

查询例子

例子数据表

use db_mysql
go
create table 雇员
(
   雇员编号 int primary key,
   姓名 varchar(20) not null,
   性别 char(2) check(性别='男' or 性别='女')default'男',
   工资 int
)

use db_mysql
go
insert into 雇员(雇员编号,姓名,性别,工资) values(101,'B','男',1200)
insert into 雇员(雇员编号,姓名,性别,工资) values(102,'C','男',1500)
insert into 雇员(雇员编号,姓名,性别,工资) values(103,'D','女',1400)

类型转换

use db_mysql
go
select *,CAST(工资*0.08 as decimal(18,1))as 奖金 from 雇员

--Cast（字段名 as 转换的类型）：类型转换 
--  其中类型可以是 bianary char date datetime decimal signed int time unsigned

输出一个连接的字符串信息

use db_mysql
go
select ('雇员编号是'+cast(雇员编号 as varchar(20))+','+'工资是'+cast(工资 as varchar(20)))as 信息 from 雇员 where 工资=1000     
 --显示字符串时，其他类型要通过cast转换为字符型

条件输出信息 between..and

use db_mysql
go
select 姓名,工资状况 =
   case 
     when 工资>900 and 工资<=1200 then '低'
     when 工资>1200 and 工资<=1400 then '高'
   end
   from 雇员

等值查询 in

use db_mysql
go
select * from 订单 where 员工号 in (select 雇员编号 from 雇员 where 姓名='A' )
select * from 订单 where 员工号 in (100，101，102)
select * from 公司 where 公司号 not in （select 公司号 from 雇员） --没有雇员的公司

模糊查询 Like

use db_mysql
go
select * from 雇员 where 工资 like '_[2-4]00'     #1200 1300 1400

like 模糊查询
% 包含0个或多个字符
_ 包含一个字符
[ ] 指定范围如[a-z]、[1-9]
[^] 不属于指定范围 [^a-z]

高级查询

聚合函数
对一组数据进行计算并返回单一值，也称为多行函数或组合函数。对整个数据集合进行计算，返回一行汇总结果

函数名	功能
count（*）	统计选择的记录个数
count（）	统计特定列中值的个数
sum（）	计算总和（必须是数值）
avg（）	平均值
max（）	最大值
min（）	最小值

* count（），avg，sum可以使用 distinct关键字，计算时不包含重复行。count（*）、min、max由于不改变结果，没必要用

查询例子

select count（distinct（公司号））as 无重复公司个数 from 雇员

select count(*) as 2号公司人数,sum(工资）as 工资总和 from 雇员 
     where 公司号 in （select 公司号 from 公司 where 城市='上海'）

select * from 雇员 where 工资=（select max（工资）from 雇员）

分组查询
group by ：分组前的条件用 where 分组后的条件用having
分组执行聚合函数
例如：按公司号分组，显示每个公司男员工的平均工资大于1700的公司

select 公司号, avg(工资) as 不同公司平均工资 from 雇员 
      where 性别='男'  group by 公司号  
      having avg(工资)>1700   -- 显示大于1700的

多列分组查询

select 公司号,性别，AVG(工资) as 不同公司的平均工资 from 雇员
     where 性别 is not null  and  工资>2100 group by 公司号, 性别
select 公司号,性别，AVG(工资) as 不同公司的平均工资 from 雇员
     where 性别 is not null  and  工资>2100 group by 公司号, 性别
    having AVG(工资) > 1600

all: 在group 分组中即使没有符合查询条件的内容，分组字段也会显示。

select 公司号, max(工资) as 最大工资 from 雇员 where 工资>1900
group by all 公司号
--- 即使有公司 工资都小于1900，公司号也会显示，最大工资会显示null

SQL嵌套查询
用另一个查询语句的结果进行查询。

select * from 学生 where 分数 = （select max（分数）from 学生） 
select * from 学生 where 分数>( select avg(分数) from 学生) and 分数<(select max(分数) from 学生)
--也可以用between ( select avg(分数) from 学生) and (select max(分数) from 学生)  
-- 可以多级嵌套

select * from 学生  where  分数  in (select max(分数)from 学生)，(select min(分数)from 学生))

exists 嵌套返回True 或 False

select * from 班级 where exists(select * from 学生 where 班级号 = 学生.班级号  and  分数=100）
-- 显示班上有满分学生 的  班级信息

多表连接查询JOIN
连接查询是横向结合，将两个关系库表接成一个更宽的关系表，包括四种：内联查询、左连接查询、右连接查询、全连接查询。多表集合的并、交和差运算，也是多表查询。

多表查询
如果不设置连接条件，则会形成笛卡尔积，即多个表的组合，比如一个表有3行另一个表有10行，连接后一个表有3x10 = 30 行。

select 姓名,分数,地址 from  学生，家庭  -- 组合
-- 学生[姓名 分数 年龄 家庭编号]   家庭[编号，父亲  目前  地址]
select 姓名,分数,地址 from  学生，家庭  where  学生.家庭编号 = 家庭.编号
select 姓名，工资，城市 from 雇员，公司 where  雇员.公司号 = 公司.公司号  and
     (工资 between 600 and 999）and 城市 = '上海'

多表连接查询生成新表 into
有些操作是无法利用多表查询的，比如group by 必须将多表查询结果生成一个新表，对新表进行分组。

select xxx into 连接的新表 from 雇员, 公司  where  xxx
select  xxx from 连接的新表  group by  公司

超连接查询，即内联查询、左连接查询、右连接查询、全连接查询，使用超连接查询不仅可以将满足条件的记录显示出来，还可以吧一部分不满足条件的记录以NULL显示。
（1）内连接查询（inner join）使用比较运算对表之间的某些数据进行比较，并列出这些表中与连接条件相匹配的数据行。

--显示雇员姓名及公司坐在地
select 姓名，城市 from 公司 inner join 雇员 on 雇员.公司号 = 公司.编号
   and 城市 is not null and 姓名 like'%美%'
--多表内联查询
select xxx from 公司
   inner join 雇员 on 雇员.公司号 = 公司.编号
  inner join  订单  on  雇员.雇员号 = 订单.雇员号
  and 工资 > 1000 and 订单金额>9999

(2) 左连接查询：除了显示满足连接条件的记录之外，第一张表中不满足条件的记录也显示在结果中。

select xxx from 公司
   left join 雇员 on 雇员.公司号 = 公司.编号
   left join  订单  on  雇员.雇员号 = 订单.雇员号
  and 工资 > 1000 and 订单金额>9999
-- 第一个left jion  第一张表是 公司，第二张表是雇员；第二个left join 第一张表是雇员 第二张表是 订单

（3）右连接查询：除了显示满足连接条件的记录之外，第二张表中不满足条件的记录也显示在结果中。
（4）全连接查询：除了显示满足连接条件的记录之外，两张表中不满足条件的记录也显示在结果中。
·

select xxx from 公司
left join 雇员 on 雇员.公司号 = 公司.编号
right jion 订单 on 雇员.雇员号 = 订单.雇员号
and 工资>600

查询集合的并、交、差
SQL支持集合的并运算（union）、交运算（intersect）和差运算（except），也就是将几个select的结果集进行运算合并成一个查询结果。为了进行运算，两个查询结果具有相同的字段个数，并且对应的字段要具有相同的数据类型和取值范围。

-- 显示城市在 上海  和 工资>999的公司信息
select 编号 from 公司 where 城市 = ‘上海’
union
select 公司号 from 雇员 where 工资>999

SQL学习笔记 查询

SQL学习笔记查询