SQL> select * from student;
SNO SNAME SSEX SAGE SDEPT
---------- ---------------------------------------- -------- ---------- ----------------------------------------
6 李宇航 男 22 口腔医学
7 苏小白 男 22 中医药
1 张波 男 18 软件工程
2 李增福 男 21 预防医学
3 安霞 女 21 临床医学
4 贺建 女 20 应用数学
5 杨鹏飞 男 20 应用数学
已选择7行。
学生表
SQL> select * from studentscore;
SNO SNAME ENGLISH CHINESE MATH
---------- -------------------- ---------- ---------- ----------
1 张波 53 99 75
2 李增福 62 88 90
3 安霞 80 46 86
4 贺建 99 86 96
5 杨鹏飞 72 76 63
6 李宇航 62 65 66
7 苏小白 32 68 60
8 李旭 64 86 98
9 张必过 26 35 36
10 安星宇 76 75 73
已选择10行。
成绩表
这是我用的表
一、筛选查询
1.比较查询
格式 :select 列名1,... from 表名 where 比较条件
查询年龄大于20的所有人的姓名和年龄
语句:select sname as "姓名",sage as "年龄" from student where sage>20;
SQL> select sname as "姓名",sage as "年龄" from student where sage>20;
姓名 年龄
---------------------------------------- ----------
李宇航 22
苏小白 22
李增福 21
安霞 21
注意:不等于号为 !=或者 <>
2.逻辑查询
关键字:and,or,not
and只有全为true时返回,or只要有一个正确就返回,not求反操作
查询年龄大于20且非女生专业为口腔医学或者预防医学的姓名,专业,性别,年龄
select sname as "姓名",sage as "年龄",ssex as "性别",sdept as "专业" from student where sage>20 and (not ssex='女') and (sdept='预防医学' or sdept='口腔医学');
结果:
SQL> select sname as "姓名",sage as "年龄",ssex as "性别",sdept as "专业" from student where sage>20 and (not ssex='女') and (sdept='预防医学' or sdept='口腔医学');
姓名 年龄 性别 专业
---------------------------------------- ---------- -------- ----------------------------------------
李宇航 22 男 口腔医学
李增福 21 男 预防医学
3.模糊查询
用于符合一定要求的查询,比如查询姓李的人,为 name like '李%'
关键字:like,not like
与like搭配使用%和_:%说明还有0或者n个字符,_只有一个字符
比如:like %c%查找中间为c的字符串
例子:查找姓李的同学姓名
select sname from student where sname like '李%';
SQL> select sname from student where sname like '李%';
SNAME
----------------------------------------
李增福
李宇航
查询不姓李的同学:
select sname from student where sname not like '李%';
SQL> select sname from student where sname not like '李%';
SNAME
----------------------------------------
安霞
张波
杨鹏飞
苏小白
贺建
4.范围查询
关键词 between .....and 在....之间。
not between ...and 不在....之间。
使用范围:数字、字符串、日期
例:查询年龄在20到21之间学生姓名和年龄
select sname,sage from student where sage between 20 and 21;(包含20与21)
SQL> select sname,sage from student where sage between 20 and 21;
SNAME SAGE
---------------------------------------- ----------
李增福 21
安霞 21
贺建 20
杨鹏飞 20
查询年龄不在20到21之间的
select sname,sage from student where sage not between 20 and 21;
SQL> select sname,sage from student where sage not between 20 and 21;
SNAME SAGE
---------------------------------------- ----------
李宇航 22
苏小白 22
张波 18
5.判断内容是否为null
关键字: is null,is not null
当一个字段为null,那么如果对其进行一些计算操作那么结果也为null
我们在student中插入一条数据
insert into student (sno,sname,ssex)values (8,'李旭','男');
SQL> select * from student where sno=8;
SNO SNAME SSEX SAGE SDEPT
---------- ---------------------------------------- -------- ---------- ----------------------------------------
8 李旭 男
这条数据的年龄和专业都为null
例子:判断没有存入学生专业的那名学生姓名
我们用 =null 和 is null同时查找
SQL> select sname from student where sage is null;
SNAME
----------------------------------------
李旭
SQL> select sname from student where sage=null;
未选定行
SQL> select sname from student where sdept=null;
未选定行
我们发现虽然=null不报错,但是它查不到任何信息
查询年龄不为null的所有学生姓名
SQL> select sname from student where sage is not null;
SNAME
----------------------------------------
李宇航
苏小白
张波
李增福
安霞
贺建
杨鹏飞
已选择7行。
SQL> select sname from student where sage!=null;
未选定行
二、分组查询
首先介绍一些常用的函数
函数 | 说明 |
avg(str) | 返回数字列或者计算列的平均值 |
count(str) | 返回查询结果的记录数 |
max(str) | 返回一计算列或者计算列最大值 |
min(str) | 返回一计算列或者计算列最小值 |
sum(str) | 返回一数字列或者计算的总和 |
所用到表的内容:
SQL> select * from student;
SNO SNAME SSEX SAGE SDEPT
---------- ---------------------------------------- -------- ---------- ----------------------------------------
6 李宇航 男 22 口腔医学
7 苏小白 男 22 中医药
1 张波 男 18 软件工程
2 李增福 男 21 预防医学
3 安霞 女 21 临床医学
4 贺建 女 20 应用数学
5 杨鹏飞 男 20 应用数学
8 李旭 男 19 临床医学
9 张必过 男 19 预防医学
10 安星宇 男 23 软件工程
已选择10行。
1.单字段分组
关键字:group by
格式: select 列名.... where 表名 gruop by 列名;
例子:查询每一专业的人数
select sdept,count(sdept) from student group by sdept;
SQL> select sdept,count(sdept) from student group by sdept;
SDEPT COUNT(SDEPT)
---------------------------------------- ------------
口腔医学 1
临床医学 2
软件工程 2
预防医学 2
应用数学 2
中医药 1
已选择6行。
查询每一专业最大和最小年龄
select sdept,max(sage),min(sage) from student group by sdept;
SQL> select sdept,max(sage),min(sage) from student group by sdept;
SDEPT MAX(SAGE) MIN(SAGE)
---------------------------------------- ---------- ----------
口腔医学 22 22
临床医学 21 19
软件工程 23 18
预防医学 21 19
应用数学 20 20
中医药 22 22
已选择6行。
2.多字段分组
关键字:group by4
格式:select ...from 表名 列名...;
例:查询每一个专业男女年龄最大最小
select sdept,max(sage),min(sage),ssex from student group by sdept,ssex;
SQL> select sdept,max(sage),min(sage),ssex from student group by sdept,ssex;
SDEPT MAX(SAGE) MIN(SAGE) SSEX
---------------------------------------- ---------- ---------- --------
中医药 22 22 男
软件工程 23 18 男
临床医学 21 21 女
应用数学 20 20 女
临床医学 19 19 男
预防医学 21 19 男
应用数学 20 20 男
口腔医学 22 22 男
3.限制分组结构
关键字 having
对数据进行了限制
例:查找每一个专业的最大年龄按专业分组,且最大年龄大于21;
select sdept,max(sage) from student
group by sdept
having max(sage)>21;
SQL> select sdept,max(sage) from student
2 group by sdept
3 having max(sage)>21;
SDEPT MAX(SAGE)
---------------------------------------- ----------
口腔医学 22
软件工程 23
中医药 22
三、排序查询
1.单列排列
关键字 desc和asc一种为降序一种为升序
和order by 一起使用
格式:select 列名 from 表名
[where ....]
[order by 列名 desc];
例:查询数学成绩大于60人的名字
select sname,math from studentscore
where math>=60
order by math desc;
SQL> select sname,math from studentscore
2 where math>=60
3 order by math desc;
SNAME MATH
-------------------- ----------
李旭 98
贺建 96
李增福 90
安霞 86
张波 75
安星宇 73
李宇航 66
杨鹏飞 63
苏小白 60
已选择9行。
2.多列排列
关键字 desc和asc一种为降序一种为升序
和order by 一起使用
格式:select 列名 from 表名
[where ....]
[order by 列名1 desc,列名2 asc.....];
当第一列相同时那么在根据列名2规定排序
例:查询学生表里的学生姓名先按年龄降序,年龄相同按学号升序
select sname,sage,sno from student
order by sage desc,sno asc;
SQL> select sname,sage,sno from student
2 order by sage desc,sno asc;
SNAME SAGE SNO
---------------------------------------- ---------- ----------
安星宇 23 10
李宇航 22 6
苏小白 22 7
李增福 21 2
安霞 21 3
贺建 20 4
杨鹏飞 20 5
李旭 19 8
张必过 19 9
张波 18 1
已选择10行。
四、多表关联
在多表关联要应用到数据为空的时候,所以我们在插入几条数据
向student表中插入:
insert into student (sno,sname,ssex)values(11,'李四','男');
insert into student (sno,sname,ssex,sage,sdept)values(13,'苏非','女',19,'中医药');
向studentscore中插入:
insert into studentscore(sno,sname)values(12,'王五');
insert into studentscore values(14,'黄尚',86,82,80);
在介绍多表 查询前先介绍一下表的别名
表的别名类似列的别名相同不过用不需要关键词,只需要在表名后直接写即可
如图s便是student的表别名
SQL> select s.sname from student s;
SNAME
----------------------------------------
安星宇
安霞
张必过
张波
李增福
李宇航
李旭
杨鹏飞
苏小白
贺建
已选择10行。
多表查询的一个例子:查询编号为1的姓名和成绩
select s.sno,s.sname,sc.math,sc.english,sc.chinese
from student s,studentscore sc
where s.sno=sc.sno
and s.sno=1
SQL> select s.sno,s.sname,sc.math,sc.english,sc.chinese
2 from student s,studentscore sc
3 where s.sno=sc.sno
4 and s.sno=1;
SNO SNAME MATH ENGLISH CHINESE
---------- ---------------------------------------- ---------- ---------- ----------
1 张波 75 53 99
1.内连接等值链接
内连接据时两个表的连接,当两个表都存在时保存
关键字:inner join.....on。其中inner可以省略
格式:select 列....from 表1 join 表2 on 内连接条件
我们通过编号将两个表进行内连接
select s.sno,s.sname,s.sage,s.ssex,sc.math,sc.english,sc.chinese
from student s join studentscore sc
on s.sno=sc.sno;
结果:
SQL> select s.sno,s.sname,s.sage,s.ssex,sc.math,sc.english,sc.chinese
2 from student s join studentscore sc
3 on s.sno=sc.sno;
SNO SNAME SAGE SSEX MATH ENGLISH CHINESE
---------- ---------------------------------------- ---------- -------- ---------- ---------- ----------
1 张波 18 男 75 53 99
2 李增福 21 男 90 62 88
3 安霞 21 女 86 80 46
4 贺建 20 女 96 99 86
5 杨鹏飞 20 男 63 72 76
6 李宇航 22 男 66 62 65
7 苏小白 22 男 60 32 68
8 李旭 19 男 98 64 86
9 张必过 19 男 36 26 35
10 安星宇 23 男 73 76 75
已选择10行。
我们通过内连接的方式查询年龄大于20的所有人成绩
select s.sno,s.sname,s.sage,s.ssex,sc.math,sc.english,sc.chinese
from student s join studentscore sc
on s.sno=sc.sno
where s.sage>20;
结果:
SQL> select s.sno,s.sname,s.sage,s.ssex,sc.math,sc.english,sc.chinese
2 from student s join studentscore sc
3 on s.sno=sc.sno
4 where s.sage>20;
SNO SNAME SAGE SSEX MATH ENGLISH CHINESE
---------- ---------------------------------------- ---------- -------- ---------- ---------- ----------
2 李增福 21 男 90 62 88
3 安霞 21 女 86 80 46
6 李宇航 22 男 66 62 65
7 苏小白 22 男 60 32 68
10 安星宇 23 男 73 76 75
2.外连接
外连接一共分为3种:左外连接,右外连接和完全连接
a.左外连接
关键字:left outer join ..on或者为left join ..on
格式:select ... from 表1 left join 表2 on 条件;
内连接是将所有符合调节的要求输出
左外连接是以左表为主表将左表中信息输出即使右表没有与之匹配的数据,当右表没有与之匹配的数据则右表数据输出null
我们查询student表中同学的成绩
select s.sno,s.sname,s.sage,s.ssex,sc.math,sc.english,sc.chinese
from student s left join studentscore sc
on s.sno=sc.sno;
结果:
SQL> select s.sno,s.sname,s.sage,s.ssex,sc.math,sc.english,sc.chinese
2 from student s left join studentscore sc
3 on s.sno=sc.sno;
SNO SNAME SAGE SSEX MATH ENGLISH CHINESE
---------- ---------------------------------------- ---------- -------- ---------- ---------- ----------
1 张波 18 男 75 53 99
2 李增福 21 男 90 62 88
3 安霞 21 女 86 80 46
4 贺建 20 女 96 99 86
5 杨鹏飞 20 男 63 72 76
6 李宇航 22 男 66 62 65
7 苏小白 22 男 60 32 68
8 李旭 19 男 98 64 86
9 张必过 19 男 36 26 35
10 安星宇 23 男 73 76 75
13 苏非 19 女
11 李四 男
已选择12行。
我们可以看到,即使studentscore中没有学号为11,14的成绩信息它也会输出只是会以null代替
b.右外连接
关键字:right outer join..on或者 right join ..on..
格式:select .... from 表名1right join 表名2 on 条件
右外连接和左外接连刚好相反,以右表为主表,右边全部输出,左表没有数据输出null
示例:查询studentscore表中所有学生的信息
select sc.sno,sc.sname,s.sage,s.sdept,s.ssex,sc.math,sc.english,sc.chinese
rom student s right join studentscore sc
on s.sno=sc.sno;
SQL> select sc.sno,sc.sname,s.sage,s.sdept,s.ssex,sc.math,sc.english,sc.chinese
2 from student s right join studentscore sc
3 on s.sno=sc.sno;
SNO SNAME SAGE SDEPT SSEX MATH ENGLISH CHINESE
---------- -------------------- ---------- ---------------------------------------- -------- ---------- ---------- ----------
6 李宇航 22 口腔医学 男 66 62 65
7 苏小白 22 中医药 男 60 32 68
1 张波 18 软件工程 男 75 53 99
2 李增福 21 预防医学 男 90 62 88
3 安霞 21 临床医学 女 86 80 46
4 贺建 20 应用数学 女 96 99 86
5 杨鹏飞 20 应用数学 男 63 72 76
8 李旭 19 临床医学 男 98 64 86
9 张必过 19 预防医学 男 36 26 35
10 安星宇 23 软件工程 男 73 76 75
12 王五
14 黄尚 80 86 82
已选择12行。
上面数据我们可以看出右表中数据全部显示,与右表中没有匹配的数据都显示为null.
c.完全外连接
关键字:full outer join...on或者 full join ..on
格式:select...from 表1 full join 表2 on 条件。
完全外连接相当于左外连接和右外连接的结合,输出左右表中的所有查询的数据,如果对应的表中没有数据则用null代替。
示例:输出student 和studenrscor表中的学号和姓名
select s.sno,s.sname,sc.sno,sc.sname
from student s full join studentscore sc
on s.sno=sc.sno
SQL> select s.sno,s.sname,sc.sno,sc.sname
2 from student s full join studentscore sc
3 on s.sno=sc.sno;
SNO SNAME SNO SNAME
---------- ---------------------------------------- ---------- --------------------
12 王五
14 黄尚
1 张波 1 张波
2 李增福 2 李增福
3 安霞 3 安霞
4 贺建 4 贺建
5 杨鹏飞 5 杨鹏飞
6 李宇航 6 李宇航
7 苏小白 7 苏小白
8 李旭 8 李旭
9 张必过 9 张必过
10 安星宇 10 安星宇
13 苏非
11 李四
已选择14行。
我们可以看到将所有的名字都进行了输出,如果对应的表中没有该条数据用null代替
3.自然连接
关键字:natural join
格式:select...from 表1 natural jion 表2
和内连接相似,数据库自动会连接第一个表和第二个表中相同的列名进行匹配
4.自连接
就是自己与自己连接
自连接用的方法其实就是内连接和外连接的方法,只是两个表为同一个表。
如下面这个例子就是应用内连接的方法
示例:查找studentscore表中数学成绩与英语成绩相同的人的名字
select s.sname,s.math,sc.sname,sc.english
from studentscore s join studentscore sc
on s.math=sc.english
结果:
SQL> select s.sname,s.math,sc.sname,sc.english
2 from studentscore s join studentscore sc
3 on s.math=sc.english;
SNAME MATH SNAME ENGLISH
-------------------- ---------- -------------------- ----------
安霞 86 黄尚 86
黄尚
昨天考科三过了,美滋滋