数据表操作
1.创建表
create table 表名(
字段名 类型 约束，
字段名 类型 约束，
.....
)
2.删除表
样式一：drop table 表名
样式二：drop table 表名 if exist 表名
数据操作-增删改查
1.简单查询
select * from 表名
2.添加数据
insert into 表名 value（......）
3.修改
update 表名 set 列1=值1，列2=值2..... where 条件
4.删除
delete from 表名 where 条件
5.消除重复行
select distinct sex from students
数据操作-查询
1.条件
比较运算符
- 等于: =
- 大于: >
- 大于等于: >=
- 小于: <
- 小于等于: <=
- 不等于: != 或 <>
例1：查询小乔的年龄
select age from students where name='小乔'
逻辑运算符
- and
- or
- not
例1：查询年龄小于20的女同学
select * from students where age<20 and sex='女'
例2：查询女学生或’1班’的学生
select * from students where sex='女' or class='1班'
例3：查询非天津的学生
select * from students where not hometown='天津'
模糊查询
- like
- % 表示任意多个任意字符
- _表示一个任意字符
例1：查询姓孙的学生
select * from students where name like '孙%'
例2：查询姓孙且名字是一个字的学生
select * from students where name like '孙_'
例3：查询叫乔的学生
select * from students where name like '%乔'
例4：查询姓名含白的学生
select * from students where name like '%白%'
范围查询
- in表示在一个非连续的范围内
例1：查询家乡是北京或上海或广东的学生
select * from students where hometown in('北京','上海','广东')
- between … and …表示在一个连续的范围内
例2：查询年龄为18至20的学生
select * from students where age between 18 and 20
空判断
- 注意：null与’'是不同的
- 判空is nul
例1：查询没有填写身份证的学生
select * from students where card is null
- 判非空is not null
例2：查询填写了身份证的学生
select * from students where card is not null
2.排序
- 为了方便查看数据，可以对数据进行排序
- 语法：
select * from 表名
order by 列1 asc|desc,列2 asc|desc,...
- 将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推
- 默认按照列值从小到大排列
- asc从小到大排列，即升序
- desc从大到小排序，即降序
例1：查询所有学生信息，按年龄从小到大排序
select * from students order by age
例2：查询所有学生信息，按年龄从大到小排序，年龄相同时，再按学号从小到大排序
select * from students order by age desc,studentNo
3.聚合函数
- 为了快速得到统计数据，经常会用到如下5个聚合函数
- count(*)表示计算总行数，括号中写星与列名，结果是相同的
- 聚合函数不能在 where 中使用
例1：查询学生总数
select count(*) from students;
- max(列)表示求此列的最大值
例2：查询女生的最大年龄
select max(age) from students where sex='女';
- min(列)表示求此列的最小值
例3：查询1班的最小年龄
select min(age) from students;
- sum(列)表示求此列的和
例4：查询北京学生的年龄总和
select sum(age) from students where hometown='北京';
- avg(列)表示求此列的平均值
例5：查询女生的平均年龄
select avg(age) from students where sex='女'
4.分组
- 按照字段分组，表示此字段相同的数据会被放到一个组中
- 分组后，分组的依据列会显示在结果集中，其他列不会显示在结果集中
- 可以对分组后的数据进行统计，做聚合运算
- 语法：
select 列1,列2,聚合... from 表名 group by 列1,列2...
例1：查询各种性别的人数
- select sex,count(*) from students group by sex
例2：查询各种年龄的人数
- select age,count(*) from students group by age
分组后的数据筛选
- 语法：
select 列1,列2,聚合... from 表名
group by 列1,列2,列3...
having 列1,...聚合...
- having后面的条件运算符与where的相同
例1：查询男生总人数
方案一
select count(*) from students where sex='男'
-----------------------------------
方案二：
select sex,count(*) from students group by sex having sex='男'
对比where与having
- where是对from后面指定的表进行数据筛选，属于对原始数据的筛选
- having是对group by的结果进行筛选
5.分页
-获取部分行
- 当数据量过大时，在一页中查看数据是一件非常麻烦的事情
- 语法
select * from 表名
limit start,count
- 从start开始，获取count条数据
- start索引从0开始
例1：查询前3行学生信息
select * from students limit 0,3
分页
- 已知：每页显示m条数据，求：显示第n页的数据
select * from students limit (n-1)*m,m
- 求总页数
查询总条数p1
使用p1除以m得到p2
如果整除则p2为总数页
如果不整除则p2+1为总页数
6.连接查询
等值连接(内连接)
方式一
select * from 表1,表2 where 表1.列=表2.列
方式二(又称内连接)
select * from 表1
inner join 表2 on 表1.列=表2.列
例1：查询学生信息及学生的成绩
select
*
from
students stu,
scores sc
where
stu.studentNo = sc.studentNo
---------------------------------------
select
*
from
students stu
inner join scores sc on stu.studentNo = sc.studentNo
例2：查询课程信息及课程的成绩
select
*
from
courses cs,
scores sc
where
cs.courseNo = sc.courseNo
---------------------------------------
select
*
from
courses cs
inner join scores sc on cs.courseNo = sc.courseNo
例3：查询学生信息及学生的课程对应的成绩
select
*
from
students stu,
courses cs,
scores sc
where
stu.studentNo = sc.studentno
and cs.courseNo = sc.courseNo
select
*
from
students stu
inner join scores sc on stu.studentNo = sc.studentNo
inner join courses cs on cs.courseNo = sc.courseNo
例4：查询王昭君的成绩，要求显示姓名、课程号、成绩
select
stu.name,
sc.courseNo,
sc.score
from
students stu,
scores sc
where
stu.studentNo = sc.studentNo
and stu.name = '王昭君'
---------------------------------------
select
stu.name,
sc.courseNo,
sc.score
from
students stu
inner join scores sc on stu.studentNo = sc.studentNo
where
stu.name = '王昭君'
例5：查询王昭君的数据库成绩，要求显示姓名、课程名、成绩
select
stu.name,
cs.name,
sc.score
from
students stu,
scores sc,
courses cs
where
stu.studentNo = sc.studentNo
and sc.courseNo = cs.courseNo
and stu.name = '王昭君'
and cs.name = '数据库'
---------------------------------------
select
stu.name,
cs.name,
sc.score
from
students stu
inner join scores sc on stu.studentNo = sc.studentNo
inner join courses cs on sc.courseNo = cs.courseNo
where
stu.name = '王昭君' and cs.name = '数据库'
例6：查询所有学生的数据库成绩，要求显示姓名、课程名、成绩
select
stu.name,
cs.name,
sc.score
from
students stu,
scores sc,
courses cs
where
stu.studentNo = sc.studentNo
and sc.courseNo = cs.courseNo
and cs.name = '数据库'
---------------------------------------
select
stu.name,
cs.name,
sc.score
from
students stu
inner join scores sc on stu.studentNo = sc.studentNo
inner join courses cs on sc.courseNo = cs.courseNo
where
cs.name = '数据库'
例7：查询男生中最高成绩，要求显示姓名、课程名、成绩
select
stu.name,
cs.name,
sc.score
from
students stu,
scores sc,
courses cs
where
stu.studentNo = sc.studentNo
and sc.courseNo = cs.courseNo
and stu.sex = '男'
order by
sc.score desc
limit 1
---------------------------------------
select
stu.name,
cs.name,
sc.score
from
students stu
inner join scores sc on stu.studentNo = sc.studentNo
inner join courses cs on sc.courseNo = cs.courseNo
where
stu.sex = '男'
order by
sc.score desc
limit 1
左连接
select * from 表1
left join 表2 on 表1.列=表2.列
例1：查询所有学生的成绩，包括没有成绩的学生
select
*
from
students stu
left join scores sc on stu.studentNo = sc.studentNo
例2：查询所有学生的成绩，包括没有成绩的学生，需要显示课程名
select
*
from
students stu
left join scores sc on stu.studentNo = sc.studentNo
left join courses cs on cs.courseNo = sc.courseNo
右连接
select * from 表1
right join 表2 on 表1.列=表2.列
添加两门课程
insert into courses values
(0, '语文'),
(0, '数学');
例1：查询所有课程的成绩，包括没有成绩的课程
select
*
from
scores sc
right join courses cs on cs.courseNo = sc.courseNo
例2：查询所有课程的成绩，包括没有成绩的课程，展示学生信息
select
*
from
scores sc
right join courses cs on cs.courseNo = sc.courseNo
left join students stu on stu.studentNo = sc.studentNo
自关联
例1：查询河南省的所有城市
select
*
from
areas as p
inner join areas as c on c.pid=p.aid
where
p.atitle='河北省';
添加区县数据
insert into areas values
('410101', '中原区', '410100'),
('410102', '二七区', '410100'),
('410103', '金水区', '410100');
例2：查询郑州市的所有区县
select
*
from
areas as c
inner join areas as a on a.pid=c.aid
where
c.atitle='郑州市';
例3：查询河南省的所有区县
select
*
from
areas as p
left join areas as c on c.pid=p.aid
left join areas as a on a.pid=c.aid
where
p.atitle='河南省'
子查询
- 在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句
主查询
- 主要查询的对象,第一条 select 语句
主查询和子查询的关系
- 子查询是嵌入到主查询中
- 子查询是辅助主查询的,要么充当条件,要么充当数据源
- 子查询是可以独立存在的语句,是一条完整的 select 语句
子查询分类
- 标量子查询: 子查询返回的结果是一个数据(一行一列)
- 列子查询: 返回的结果是一列(一列多行)
- 行子查询: 返回的结果是一行(一行多列)
- 表级子查询: 返回的结果是多行多列
标量子查询
例1：查询班级学生的平均年龄
查询班级学生平均年龄
select avg(age) from students
查询大于平均年龄的学生
select * from students where age > 21.4167
select * from students where age > (select avg(age) from students);
例2：查询王昭君的成绩，要求显示成绩
学生表中查询王昭君的学号
select studentNo from students where name = '王昭君'
成绩表中根据学号查询成绩
select * from scores where studentNo = '001'
select * from scores where studentNo = (select studentNo from students where name = '王昭君')
列级子查询
例3：查询18岁的学生的成绩，要求显示成绩
学生表中查询18岁的学生的学号
select studentNo from students where age=18
成绩表中根据学号查询成绩
select * from scores where studentNo in ('002','006')
select * from scores where studentNo in (select studentNo from students where age=18)
行级子查询
例4：查询男生中年龄最大的学生信息
select * from students where sex='男' and age=(select max(age) from students)
select * from students where (sex,age)=('男',30)
select * from students where (sex,age) = (select sex,age from students where sex='男' order by age desc limit 1)
表级子查询
例5：查询数据库和系统测试的课程成绩
select
*
from
scores s
inner join
(select * from courses where name in ('数据库','系统测试')) c
on s.courseNo = c.courseNo
子查询中特定关键字使用
- in 范围
格式: 主查询 where 条件 in (列子查询)
- any | some 任意一个
格式: 主查询 where 列 = any (列子查询)
在条件查询的结果中匹配任意一个即可,等价于 in
- all
格式: 主查询 where 列 = all(列子查询) : 等于里面所有
格式: 主查询 where 列 <>all(列子查询) : 不等一其中所有
select * from students where age in (select age from students where age between 18 and 20)
1321
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
