一、查询语句

查询语句的基本语法：

select * from 表名

或

select 列名,列名... from 表名

注：select * 写法的效率低于 select 列名的写法

select 和 from之间写的内容：

1. 列名加运算符，进行运算。

null值和任何数值做运算的结果都是null 

附加：null值和任何字符串连接，结果都是相当于和“”连接

使用 ifnull 函数处理null值的运算 ，Oracle数据库中是nvl函数

-- 查询学生姓名，余额-200的值
select sname,amt-200 from student
-- 如果运算的列含有null值，要处理null值保证运算结果的正确
-- 使用一个mysql的函数进行处理，ifnull函数
-- 函数直接调用，函数一定有结果
-- IFNULL(expr1,expr2) 如果expr1是null那么函数的结果就是expr2 ；否则结果就是expr1
select sname,IFNULL(amt,0)-200 from student

附加函数解释

concat函数：MySQL中是连接字符串的，在Oracle数据库中使用 || 连接字符串

2.为结果集的列定义别名

select sname as n,IFNULL(amt,0)-200 as 余额 from student

注：as可以省略

select sname n,IFNULL(amt,0)-200 余额 from student

 3. distinct关键字：去掉重复的行数

select distinct sname,birthday from student
-- 查询出有学生的班级编号
select distinct classid from student

二、条件查询

根据查询条件，查询出部分记录行的数据

语法：

select * from 表名 [where 布尔表达式]

 也就是 布尔表达式的结果是true，这行数据才能显示才结果集中

例如：

-- 查询姓名是jack的学生的所有信息
select * from student where sname='jack'

三、查询条件

1.比较运算的条件：： = 、>、< 、= 、!=或<>

-- 查询姓名不是jack的学生的所有信息
select * from student where sname<>'jack'

2.逻辑运算条件：and、or、not

select * from student where not sname='jack'
-- 余额在100~500之间的学生信息
select * from student where amt >= 100 and amt <= 500
-- 余额大于100的 或 身高大于 1.7
select * from student where amt>=100 or height>=1.7
-- and的优先级高于or
select * from student where amt>=100 or height>=1.7 and birthday > '2000-01-01'

and的优先级高于or，也可以使用小括号强制优先级

3. between ... and ... 数值范围条件

-- 余额在100~500之间的学生信息
select * from student where amt between 100 and 500

4.匹配条件/查询条件模糊的

使用like关键字 和 两个符号 % 、_ 组合使用

%和like组合使用代表 0个或更多个

_和like组合使用代表 单个任意字符

例如：

-- 查询名字以rose开头的
select * from student where sname like 'rose%'
-- 查询名字不是rose开头的
select * from student where sname not like 'rose%'
-- 查询名字含有o的学生
select * from student where sname like '%o%'
-- 查询名字第二个字母是o的
select * from student where sname like '_o%'

5.in关键字，集合列表范围查询

-- 查找姓名是jack、rose、tom的学生信息
select * from student where sname = 'jack' or sname = 'rose' or sname = 'tom'
select * from student where sname in ('jack','rose','tom')

6. 空值的判断条件

-- 生日为空的学生信息
select * from student where birthday is null
select * from student where birthday is not null

注：where 语句中不能使用别名 

四、排序

排序语句是对查询结果集的排序

使用order by 语句排序

语法：

 查询语句的最后 order by 列名或别名,列名或别名

 排序的列名后面默认是asc关键字，代表升序，desc关键字是降序

-- 查询学生信息，按姓名排序,降序
select * from student order by sname desc
-- 使用别名排序
select sname n from student order by n
-- 组合排序 按身高的升序排序，再按余额的降序排序
select sname,height,amt from student order by height,amt desc

五、limit关键字：限定查询记录

limit语句是在order by 的后面，可以对查询结果条数进行限定

select sname,height,amt from student limit 3
-- 等同于
select sname,height,amt from student limit 0,3
-- 从第1条开始（条数从0开始的）找3条
select sname,height,amt from student limit 1,3

注：分页功能就是用limit实现的

六、分组查询

对查询的数据进行分组，进行一些统计分析的工作。

分组函数/聚合函数

sum()、avg()、max()、min()、count()

select sum(height),avg(height),max(height),min(height),count(height) from student

count(height) 计算身高不为null的记录数，那么要计算学生表中有多少条记录：

select count(0) from student

mysql数据库中分组函数不能嵌套；oracle数据库分组函数最多嵌套两层

 查询结果进行分组查询

使用group by  列名，列名....

-- 计算各个班级中的最高身高
select max(height) from student group by classid

下面的SQL语句的数据是不正确的：sname并没有再group by 子句中

-- 计算各个班级中的最高身高
select sname,max(height) from student group by classid

与分组函数一起查询的列，一定要在group by子句中

select classid,max(height) from student group by classid

对两列进行分组查询

-- 计算各个班级中男生和女生的最高身高
select max(height) from student group by classid,sex;
select max(height) from student group by sex,classid

两条语句在结果上是没有区别的

group by中可以使用别名

select classid cid,sex s,max(height) from student group by cid,s;

注：在where语句中不能使用分组函数，可以在having语句中使用分组函数

 要做分组函数的条件时，写在having中，不是分组函数的条件写在where中

-- 计算班级平均身高大于1.72的班级
select classid,avg(height) from student
group by classid
having avg(height) > 1.72

七、where和having的区别

where中不能使用分组函数的条件，having中可以使用

where是对每条表记录的条件限定，having是对结果集记录的条件限定。

select classid,avg(height) from student
group by classid
having classid>0

select classid,avg(height) from student
where classid>0
group by classid
-- 上面两条语句的区别：
-- 语句1：student表的所有记录按班级分组，计算多个分组的值，对结果集进行having的条件限定获得两条
-- 语句2：student表的班级id大于0的记录按班级分组，计算多个分组的值，获得两条

八、多表连接查询

当查询的列或使用的条件的列，如果在多张表中，可以使用多表连接查询方式。

1. 等值连接

等值连接时，将两张表的所有记录进行组合(笛卡尔积)。组合的数据有一部分是不正确的，要通过查询条件查询出 正确的组合。

-- 查询学生和班级的信息
select * from student,class c
where classid = c.id
-- 查询学生姓名，和班级名称
select sname,cname from student,class c
where classid = c.id
-- 查询学生所有信息，和班级名称
select s.*,cname from student s,class c
where classid = c.id

多张表的多表连接查询

-- 查询员工姓名、部门名称、在部门中的工作年限
select empname,dname,worktime from employee e,department d,work w
where e.empid = w.empid and d.deptno = w.deptno
order by empname
-- 计算员工在公司工作了多少年
select empname,sum(worktime) from employee e,department d,work w
where e.empid = w.empid and d.deptno = w.deptno
group by empname
having sum(worktime)>2
-- 计算部门曾经有多少个员工工作过，显示部门名称和员工数量
select dname,count(0) from work w,department d
where d.deptno = w.deptno
group by w.deptno

2. 不等值连接

-- 查询员工姓名，部门名称 和 年限等级名称
select empname,dname,name from employee e,department d,work w,yearlevel y
where e.empid = w.empid and d.deptno = w.deptno and worktime between low and height

3. 内连接

内连接的结果和等值/不等值连接的结果是相同的。只是写法不同

-- 查询员工姓名、部门名称、在部门中的工作年限
select empname,dname,worktime from work w
inner join employee e on e.empid = w.empid
inner join department d on d.deptno = w.deptno
order by empname
-- 查询年龄大于30岁的员工姓名、部门名称、在部门中的工作年限
select empname,dname,worktime from work w
inner join employee e on e.empid = w.empid and age>30

inner join department d on d.deptno = w.deptno
order by empname;

select empname,dname,worktime from work w
inner join employee e on e.empid = w.empid
inner join department d on d.deptno = w.deptno
where age>30
order by empname

-- 计算员工在公司工作了多少年
select empname,sum(worktime) from work w
inner join employee e on e.empid = w.empid
inner join department d on d.deptno = w.deptno
group by empname
having sum(worktime)>2
-- 计算部门曾经有多少个员工工作过，显示部门名称和员工数量
select dname,count(0) from work w
inner join department d on d.deptno = w.deptno
where d.deptno = w.deptno
group by w.deptno
-- 查询员工姓名，部门名称 和 年限等级名称
select empname,dname,name from work w
inner join employee e on e.empid = w.empid
inner join department d on d.deptno = w.deptno
inner join yearlevel on worktime between low and height

注：内连接/等值/不等值连接问题：只会显示符合连接条件的数据，不会显示所有的数据

4. 外连接

外连接后，可以查询一个表的所有记录。外连接分为左外连接和右外连接，无论是哪种连接，都会显示左表的所有 记录。例如，A表左外连接B表，那么A表就是左表；B表右外连接A表，那么A表就是左表。

左外连接写法：

-- 查询学生和班级的信息 显示所有的学生记录
select * from student s
left outer join class c on s.classid = c.id
-- 查询所有的班级及班级中的学生
select * from class c
left outer join student s on s.classid = c.id

-- 连接多个表时，左外和内联一起写 ，学生表不是左表了
select sname,room from student s
left outer join class c on s.classid = c.id
inner join room r on r.id = c.roomid

5. 自连接

表自己连接自己，就是自连接

-- 查询学生姓名和班长姓名
select * from student s
left outer join student k on s.classking = k.id
-- 查询所有学生的上课地点 和 其班长的上课地点
select s.sname,m.room,k.sname,km.room from student s
left outer join class c on s.classid = c.id
left outer join room m on c.roomid = m.id
left outer join student k on s.classking = k.id
left outer join class kc on k.classid = kc.id
left outer join room km on kc.roomid = km.id

九、子查询

一个查询语句的结果作为另一个查询语句的条件

-- 查询和rose身高相同的学生
-- 外层的是主查询，括号里的是子查询
select sname,height from student
where height = (select height from student where sname = 'rose')
and sname != 'rose'

主查询每进行一行的条件检索，就执行一次子查询语句，如果主查询检索5行，子查询检索4行，一共检索20行 。子查询效率低，但子查询几乎可以解决所有查询需求。

子查询分为单行子查询和多行子查询

子查询的结果只有一行就是单行子查询；反之就是多行子查询

对单行子查询的操作只能是比较操作

对多行子查询的修饰可以是

in：

select sname,height from student
where height in (select height from student where sname = 'rose')

any:

任意一个，只要任意一个符合条件

-- 查询 比2班其中任意一个学生高的学生
select sname,height from student
where height > any(select height from student where classid = 2)
and classid != 2

all:

所有的，要满足子查询所有结果的条件

-- 查询 比2班所有学生高的学生
select sname,height from student
where height > all(select height from student where classid = 2)
and classid != 2

exists:

exists修饰的子查询，如果查询出记录，结果是true，没有查询记录，结果是false

-- 未结课班级的学生
select sname,classid from student
where classid in (select id from class where flag = '0')
-- exists语句替换，效率高于 in语句的子查询
select sname,classid from student s
where exists (select 0 from class c where flag = '0' and s.classid = c.id)

in的子查询 要 替换为exists子查询，效率高

替换的模板写法为

select * from A
where a1 in (select b1 from B)
-- 替换为exists
select * from A a
where exists(select 0 from B b where a.a1 = b.b1)

在笔试中一般都会碰到书写sql语句的题目，上次去面试就碰到了，所以在这里总结一些。

创建表语句

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[stuscore]
( [name] [varchar](50)   COLLATE Chinese_PRC_CI_AS   NULL,    
[subject] [varchar](50)   COLLATE Chinese_PRC_CI_AS   NULL,    
[score] [int]   NULL,   
[stuid] [int]    NULL) 
ON [PRIMARY] 
GO
SET ANSI_PADDING OFF

插入数据

insert into dbo.stuscore values ('张三','数学',89,1)；
insert into dbo.stuscore values ('张三','语文',80,1)；
insert into dbo.stuscore values ('张三','英语',70,1)；
insert into dbo.stuscore values ('李四','数学',90,2)；
insert into dbo.stuscore values ('李四','语文',70,2)；
insert into dbo.stuscore values ('李四','英语',80,2)；

查询语句

select * from dbo.stuscore

问题：

1.计算每个人的总成绩并排名(要求显示字段：姓名，总成绩)

select name,SUM(score) as allscore from dbo.stuscore
group by name 
order by allscore;

2.计算每个人的总成绩并排名(要求显示字段: 学号，姓名，总成绩) 

select stuid,name,SUM(score) as allscore from dbo.stuscore 
group by name,stuid 
order by allscore;

3.计算每个人单科的最高成绩(要求显示字段: 学号，姓名，课程，最高成绩)

select t1.stuid,t1.name,t1.subject,t1.score from stuscore t1,
(select stuid,max(score) as maxscore from stuscore group by stuid) t2 
where t1.stuid=t2.stuid and t1.score=t2.maxscore;

4.计算每个人的平均成绩（要求显示字段: 学号，姓名，平均成绩）

select stuid,name,AVG(score) avgscore from dbo.stuscore 
group by stuid,name;

5.列出各门课程成绩最好的学生(要求显示字段: 学号，姓名,科目，成绩) 

select t1.stuid,t1.name,t1.subject,t1.score from stuscore t1,(
select subject,MAX(score) as maxscore from stuscore group by subject)t2
where t1.subject = t2.subject and t1.score = t2.maxscore;

6.列出各门课程成绩最好的两位学生(要求显示字段: 学号，姓名,科目，成绩) 

select  t1.* from stuscore t1 where t1.stuid in (
select top 2 stuid from stuscore where subject = t1.subject order by score desc)
order by t1.subject;

9．列出数学成绩的排名（要求显示字段：学号，姓名，成绩，排名）

select stuid,name,score,
(select count(*) from stuscore t1 where subject ='数学' and t1.score > t2.score)+1 as 名次 from stuscore t2  
where subject='数学' order by score desc;

更全面的请点击：https://blog.csdn.net/javakakayi/article/details/65628024?utm_source=copy