测试学习5（数据库）

查询数据

最简单的查询语句：select * from 表名（*代表所有列）

起别名：select name as 姓名，sex as 性别 from students as 学生

去重查询：select distinct sex，class from students

条件查询：查询小乔的性别年龄

select sex，age from students where name=‘小乔’

可以看出where后面的语句是用来过滤行，select后面的语句是用来选择哪几列

比较运算符

等于：=

大于：>

大于等于：>=

小于：<

小于等于：<=

不等于：！=或<>

select * from students where hometown！=‘北京’

逻辑运算符

and，or，not

select * from students where sex=‘女’ or class=‘1班’

模糊查询

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 （‘上海’，‘北京’）

select * from students where age between 18 and 19

select * from students where not age between 18 and 19

空判断

NUll相当于没交卷子，空相当于交了没写

select * from students where card is null

select * from students where card=‘ ’

null要用is，空要用等号

排序

升序：asc

降序：desc

查询所有学生信息，按照年龄从小到大排序：

select * from students order by age asc（不写asc默认从小到大排序）

查询所有学生信息，按照年龄从大到小排序，年龄相同时，再按学号从小到大排序：

select * from students order by age desc，studentno 

（优先按照第一个字段排，第一个字段相同时，再按照第二个字段排）

按照中文排序：select * from students order by convert（name using gbk）

因为此时用的是utf-8，要将中文进行转换成gbk国标格式

聚合函数

统计人数：select count（*） from students

count（字段名）统计该字段名有多少行，*表示将所有字段当作一个整体，任意一行有值就会算入

查询最大值：max（）

查询年龄最大的学生:select max（age） from students

查询最小值：min（）

求和：sum（）

求平均值：avg（）

select max（age）as 最大年龄，min（age）as最小年龄，avg（age）as 平均年龄  from students

分组

group by（）

查询各种性别的人数：select sex，count（*） from students group by sex

查询各种年龄的人数：select age，count（*） from students group by age

按照某个字段进行分组，只要相同就会分到同一个组里，但是只会随机显示一条数据，所以需要结合聚合函数。通常按照什么分组，就要在前面把那个字段在前面写出来以显示

按照多个字段分组：

select class，sex，count（*） from students group by class，sex

分组过滤

group by 字段 having 条件

select sex，count（*） from students group by sex having sex='男'

having必须在group by 之后

where 和 having的区别

select class，max（age）as 最大年龄，min（age）as最小年龄，avg（age）as 平均年龄  from students where class！=‘1班’ group by class

select class，max（age）as 最大年龄，min（age）as最小年龄，avg（age）as 平均年龄  from students  group by class having class ！=‘1班’

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

获取部分行

limit start，count

选取5到8行：select * from students limit 4，4

前面数字代表起始位置（从0算起），后面数字代表需要的条数

分页

select count（*） from students 

获取总数，假设一页显示3条语句，用总数除以3得到总页数

select * from students limit 0，3  ：第一页

select * from students limit 3，3：第二页

。。。

select * from students limit 7相当于select * from students limit 0，7.简写了起始位。

连接查询

两个表的连接

方式1：

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

查询学生信息和学生成绩（分别为两个表）：

select * from students ，scores（此时会生成两个表的连接，行数相乘，称为笛卡尔积）

所以加上过滤条件：

select * from students ，scoreswhere students.studentno=scores.studentno

生成的结果不会生成一个新的表，只是显示的表

方式2（又称内连接）

select * from 表1

inner join 表2 on 表1.列=表2.列

select * from students（先查哪个表都可以）

inner join scores on students.studentno=scores.studentno

用方式1生成连接表会生成一个笛卡尔积临时表存在内存中，然后在进行过滤显示出来，使用方式2不会产生临时表，效率较高。

三个表的连接

方式1：

（查询语句较长时的格式）

select

*

from students ，courses，scores

where

students.studentNo=scores.studentno and scores.courseno=courses.courseno

多个表的连接，两两之间必须要有连接条件

方式2（内连接）

select * from students

 inner join scores on students.studentno=scores.studentno（此时后面只能写成绩表）

inner join courses on scores.courseno=courses.courseno

(必须要找到两个表之间关系的连接)

 过滤条件

查询某个学生某个课程的成绩

select stu.name,cs.name as 课程名,cs.courseno,sc.score from students as stu

inner join scroes as sc on stu.studentno=ac.studentno

inner join courses as cs on sc.coueseno=cs.courseno

左连接

join前面的表称为左边的表，把左边表的所有数据显示出来，如果右边表满足条件则连接，如果右边的表没有值连接则用null填充。

select * from students（左边的表）

left join scores on students.studentno=scores.studentno

三连接时同样join前面的表为左边的表

右连接同理（把右边表的数据全部显示出来）

查询所有学生的成绩，包括没有成绩的学生，需要显示课程名：

方式1.

select * from scores

inner join courses on scores.courseno=courses.courseno

right join students on students.studentno=scores.studentno

方式2.

select * from scores

right join students on students.studentno=scores.studentno

left join courses on scores.courseno=courses.courseno

总结就是找到需要查询的核心，核心在join左边就左连接，在右边就右连接