SQL语言（一）--DQL（数据操作-查询）

1、查询所有字段

#查询表中所有字段
select * from 表名

#查询学生中所有字段
select * from students

2、查询指定字段

select 列1,列2,... from 表名

#表名.字段名
select students.name,students.age from students

#可以通过 as 给表起别名 
select s.name,s.age from students as s

#如果是单表查询 可以省略表明
select name,age from students

#使用as给字段起别名
select studentNo as 学号,name as 名字,sex as 性别 from students

3、在select后面列前使用distinct可以消除重复的行

#格式
select distinct 列1,... from 表名;

#消除重复的sex
select distinct sex from students;

4、条件查询--使用where子句对表中的数据筛选，符号条件的数据会出现在结果集中

select 字段1,字段2... from 表名 where 条件;

#查询学生表中id为1的所有信息
select * from students where id=1;

where后面支持多种运算符，进行条件的处理

• 比较运算
• 逻辑运算
• 模糊查询
• 范围查询
• 空判断

比较运算符：

• 等于: =
• 大于: >
• 大于等于: >=
• 小于: <
• 小于等于: <=
• 不等于: != 或 <>

--查询学生表中笑笑的年龄
select age from students where name='笑笑'

--查询学生表中20岁以下的学生
select * from students where age<20

--查询学生表中家乡不在广州的学生
select * from students where hometown!='广州'

逻辑运算符

• and
• or
• not

--查询学生表中年龄小于20的女同学
select * from students where age<20 and sex='女'

--查询学生表中所有的女同学或'1班'的学生
select * from students where sex='女' or class='1班'

--查询学生表中非深圳的学生
select * from students where not hometown='深圳'

模糊查询

• like
• %表示任意多个任意字符
• _表示一个任意字符

--查询学生表中所有姓李的学生
select * from students where name like '李%'

--查询学生表中姓李且名字是一个字的学生
select * from students where name like '李_'

--查询学生表中名字是叫笑的学生
select * from students where name like '%笑'

--查询学生表中姓名含有白的学生
select * from students where name like '%白%'

范围查询

in表示在一个非连续的范围内

between ... and ...表示在一个连续的范围内

--查询学生表中家乡是北京或上海或广东的学生
select * from students where hometown in('北京','上海','广东')

--查询学生表中年龄为13至20的学生
select * from students where age between 13 and 20

空判断

• 注意：null与''是不同的
• 判空is null
• 判非空is not null

--查询学生表中没有填写身份证的学生
select * from students where card is null


--查询学生表中填写身份证的学生
select * from students where card is not null

5、排序查询

• 默认按照列值从小到大排列
• asc从小到大排列，即升序
• desc从大到小排序，即降序

--将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推
select * from 表名
order by 列1 asc|desc,列2 asc|desc,...


--查询所有学生信息，按年龄从小到大排序
select * from students order by age

--查询所有学生信息，按年龄从大到小排序，年龄相同时，再按学号从小到大排序
select * from students order by age desc,studentNo

6、聚合函数

• count(*)表示计算总行数，括号中写星与列名，结果是相同的
• max(列)表示求此列的最大值
• min(列)表示求此列的最小值
• sum(列)表示求此列的和
• avg(列)表示求此列的平均值
• 注意:聚合函数不能在 where 中使用

--查询学生表中学生的总数
select count(*) from students;

--查询学生表中男生的最大年龄
select max(age) from students where sex='男';

--查询学生表中的最小年龄
select min(age) from students;

--查询学生表中家乡中广州学生的年龄总和
select sum(age) from students where hometown='广州';

--查询学生表中女生的平均年龄
select avg(age) from students where sex='女'

7、分组查询

• 按照字段分组，表示此字段相同的数据会被放到一个组中
• 分组后，分组的依据列会显示在结果集中，其他列不会显示在结果集中
• 可以对分组后的数据进行统计，做聚合运算

select 列1,列2,聚合... from 表名 group by 列1,列2...

#查询各种性别的人数
select sex,count(*) from students group by sex

#查询各种年龄的人数
select age,count(*) from students group by age

分组后的数据筛选

    having后面的条件运算符与where的相同

select 列1,列2,聚合... from 表名
group by 列1,列2,列3...
having 列1,...聚合...

#方案一：查询男生总人数
select count(*) from students where sex='男' 

#方案二：查询男生总人数
select sex,count(*) from students group by sex having sex='男'

对比where与having

• where是对from后面指定的表进行数据筛选，属于对原始数据的筛选
• having是对group by的结果进行筛选

8、分页查询

已知：每页显示m条数据，求：显示第n页的数据

select * from students limit (n-1)*m,m

求总页数

• 查询总条数p1
• 使用p1除以m得到p2
• 如果整除则p2为总数页
• 如果不整除则p2+1为总页数

#从start开始，获取count条数据
#start索引从0开始

select * from 表名 limit start,count

#查询前3行学生信息
select * from students limit 0,3

#查询第4到第6行学生信息
select * from students limit 3,3

9、连接查询

• 等值连接查询：查询的结果为两个表匹配到的数据

方式一

select * from 表1,表2 where 表1.列=表2.列

方式二(又称内连接)

select * from 表1
inner join 表2 on 表1.列=表2.列

 

#查询学生信息及学生的成绩
select * from students stu,scores sc where stu.studentNo = sc.studentNo

#内连接
select * from students stu inner join scores sc on stu.studentNo = sc.studentNo

• 左连接查询：查询的结果为两个表匹配到的数据加左表特有的数据，对于右表中不存在的数据使用null填充

select * from 表1
left join 表2 on 表1.列=表2.列

#查询所有学生的成绩，包括没有成绩的学生
select * from students stu left join scores sc on stu.studentNo = sc.studentNo

•  

• 右连接查询：查询的结果为两个表匹配到的数据加右表特有的数据，对于左表中不存在的数据使用null填充

select * from 表1
right join 表2 on 表1.列=表2.列

查询所有课程的成绩，包括没有成绩的课程
select * from scores sc right join courses cs on cs.courseNo = sc.courseNo

10、子查询

   在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句

子查询分类：

• 标量子查询: 子查询返回的结果是一个数据(一行一列)
• 列子查询: 返回的结果是一列(一列多行)
• 行子查询: 返回的结果是一行(一行多列)
• 表级子查询: 返回的结果是多行多列

子查询中特定关键字的使用：

• in 范围

  • 格式: 主查询 where 条件 in (列子查询)

• any | some 任意一个

  • 格式: 主查询 where 列 = any (列子查询)
  • 在条件查询的结果中匹配任意一个即可,等价于 in

• all

  • 格式: 主查询 where 列 = all(列子查询) : 等于里面所有
  • 格式: 主查询 where 列 <>all(列子查询) : 不等一其中所有

 

   主要查询的对象,第一条 select 语句：称之为主查询

子查询与主查询关系：

• 子查询是嵌入到主查询中
• 子查询是辅助主查询的,要么充当条件,要么充当数据源
• 子查询是可以独立存在的语句,是一条完整的 select 语句

11、自查询

表中的某一列，关联了这个表中的另外一列，但是它们的业务逻辑含义是不一样的