数据操作
1.1 查询
1.1.1 创建表
drop table if exists students;
create table students (
studentNo varchar(10) primary key,
name varchar(10),
sex varchar(1),
hometown varchar(20),
age tinyint(4),
class varchar(10),
card varchar(20)
)
1.1.2 准备数据
insert into students values
('001', '王昭君', '女', '北京', '20', '1班', '340322199001247654'),
('002', '诸葛亮', '男', '上海', '18', '2班', '340322199002242354'),
('003', '张飞', '男', '南京', '24', '3班', '340322199003247654'),
('004', '白起', '男', '安徽', '22', '4班', '340322199005247654'),
('005', '大乔', '女', '天津', '19', '3班', '340322199004247654'),
('006', '孙尚香', '女', '河北', '18', '1班', '340322199006247654'),
('007', '百里玄策', '男', '山西', '20', '2班', '340322199007247654'),
('008', '小乔', '女', '河南', '15', '3班', null),
('009', '百里守约', '男', '湖南', '21', '1班', ''),
('010', '妲己', '女', '广东', '26', '2班', '340322199607247654'),
('011', '李白', '男', '北京', '30', '4班', '340322199005267754'),
('012', '孙膑', '男', '新疆', '26', '3班', '340322199000297655')
1.1.3 查询所有字段
select * from students
1.1.4 查询指定字段
from前面确定列，where后面确定行
as为列取别名,别名出现在运行结果中，主要应用在多表查询中，可不写
格式：select 列1 (as 新列1),列2 (as 新列2),... from [表名] where [条件]
(1) as取别名
1. 表名.字段名
select students.name,students.age from students
单表查询 可以省略表名
select name,age from students
2. as 给表起别名
select s.name,s.age from students as s
3. as给字段起别名
select studentNo as 学号,name as 名字,sex as 性别 from students
(2) 消除重复数据
dinstnct消除时，是判断所有字段(列名)组成的数据是否重复，而不是只看单个字段的重复
格式：select distinct [列1],... from [表名]
展示一列
select distinct sex from students
展示两列
select distinct sex,age from students
展示所有列
select distinct * from students
1.2 条件
使用where子句对表中的数据筛选，符合条件的数据会出现在结果集中
格式：select [字段1],[字段2]... from [表名] where [条件];where后面支持多种运算符，进行条件的处理
1.2.3 比较运算
| 比较运算符 | 符号 |
|---|---|
| 等于 | = |
| 大于 | > |
| 大于等于 | >= |
| 小于 | < |
| 小于等于 | <= |
| 不等于 |
!= 或 <>
|
eg:
查询小乔的年龄
select age from students where name='小乔'
查询20岁以下的学生
select * from students where age<20
查询家乡不在北京的学生
select * from students where hometown <>'北京'
1.2.4 逻辑运算
| 逻辑运算符 | 符号 |
|---|---|
| 且 | and |
| 或 | or |
| 非 | not |
eg:
查询年龄小于20的女同学
select * from students where age<20 and sex='女'
查询女学生或'1班'的学生
select * from students where sex='女' or class ='1班'
查询非天津的学生
select * from students where not hometown='天津'
1.2.5 模糊查询 like
like
- %表示任意多个任意字符
- _表示一个任意字符
eg:
查询姓孙的学生
select * from students where name like '孙%'
查询姓孙且名字是一个字的学生
select * from students where name like '孙_'
查询姓名含白的学生
select * from students where name like '%白%'
1.2.6 范围查询 in
1. in
表示在一个非连续的范围内
eg:
查询家乡是北京或上海或广东的学生
select * from students where hometown in('北京','上海')
2. between ... and ...
表示在一个连续的范围内
eg:
查询年龄为18至20的学生
select * from students where age between 18 and 20
1.2.7 空判断
注意：
- null与''是不同的
- null不能和=一起使用，只能和is一起使用
1. is null
判空
eg:
查询没有填写身份证的学生
select * from students where card is null
查询身份证为空字符的学生
select * from students where card =''
2. is not null
判非空
eg:
查询填写了身份证的学生
select * from students where card is not null
1.3 排序 order by
格式：select * from [表名] order by [列1] asc|desc,[列2] asc|desc,...
- 将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推
- 默认按照列值从小到大排列
- asc从小到大排列，即升序
- desc从大到小排序，即降序
eg:
查询所有学生信息，按插入数据顺序排序
select * from students
查询所有学生信息，按年龄从小到大排序
select * from students order by age
查询所有学生信息，按年龄从大到小排序，年龄相同时，再按学号从小到大排序
select * from students order by age desc,studentNo
1.4 聚合函数
- 为了快速得到统计数据
- 聚合函数不能在 where 中使用
| 聚合函数 | 说明 |
|---|---|
count(*) |
计算总行数，括号中写与列名，结果是相同的，null不统计* |
max(列) |
求此列的最大值 |
min(列) |
求此列的最小值 |
sum(列) |
求此列的和 |
avg(列) |
求此列的平均值 |
eg:
查询学生总数
select count(*) from students
查询女生的最小年龄
select min(age) from students where sex='女'
查询1班的最大年龄
select max(age) from students where class='1班'
查询北京学生的年龄总和
select sum(age) from students where hometown='北京'
查询女生的平均年龄
select avg(age) from students where sex='女'
1.5 分组 group by
- 按照字段分组，表示此字段相同的数据会被放到一个组中
- 分组后，分组的依据列会显示在结果集中，其他列不会显示在结果集中
- 可以对分组后的数据进行统计，做聚合运算
格式：select 列1,列2,聚合... from [表名] group by 列1,列2...
eg:
查询各种性别的人数
select sex,count(*) from students group by sex
查询各种年龄的人数
select age,count(*) from students group by age
1.5.1 分组后的数据筛选
格式：
select 列1,列2,聚合... from [表名]
group by 列1,列2,列3...
having 列1,...聚合...
- having后面的条件运算符与where的相同
提示：
- where是对from后面指定的表进行数据筛选，属于对原始数据的筛选
- having是对group by的结果进行筛选
eg:
查询男生总人数(直接过滤)
select count(*) as 男生总数 from students where sex='男'
查询男生总人数(先分组再过滤)
select sex,count(*) as 男生总数 from students group by sex having sex='男'
1.6 分页 limit
当数据量过大时，在一页中查看数据是一件非常麻烦的事情
格式：
select * from [表名]
limit start,count
- 从start开始，获取count条数据
- start索引从0开始
eg:
查询前3行学生信息
select * from students limit 0,3
已知：每页显示m条数据，求：显示第n页的数据
select * from students limit (n-1)*m,m
扩展：
- 求总页数
- 查询总条数p1
- 使用p1除以m得到p2
- 如果整除则p2为总数页
- 如果不整除则p2+1为总页数
每页显示5条数据，显示每一页的数据
select * from students limit 0,5
select * from students limit 5,5
select * from students limit 10,5
更新中......
