5.查询

简介

查询的基本语法

select * from 表名;

from关键字后面写表名，表示数据来源于是这张表
select后面写表中的列名，如果是*表示在结果中显示表中所有列
在select后面的列名部分，可以使用as为列起别名，这个别名出现在结果集中
如果要查询多个列，之间使用逗号分隔

消除重复行

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

select distinct gender from students;

条件

使用where子句对表中的数据筛选，结果为true的行会出现在结果集中
语法如下：

select * from 表名 where 条件;

比较运算符

等于=
大于>
大于等于>=
小于<
小于等于<=
不等于!=或<>
查询编号大于3的学生

select * from students where id>3;

查询编号不大于4的科目

select * from subjects where id<=4;

查询姓名不是“黄蓉”的学生

select * from students where sname!='黄蓉';

查询没被删除的学生

select * from students where isdelete=0;

逻辑运算符

and
or
not
查询编号大于3的女同学

select * from students where id>3 and gender=0;

查询编号小于4或没被删除的学生

select * from students where id<4 or isdelete=0;

模糊查询

like
%表示任意多个任意字符
_表示一个任意字符
查询姓黄的学生

select * from students where sname like '黄%';

查询姓黄并且名字是一个字的学生

select * from students where sname like '黄_';

查询姓黄或叫靖的学生

select * from students where sname like '黄%' or sname like '%靖%';

范围查询

in表示在一个非连续的范围内
查询编号是1或3或8的学生

select * from students where id in(1,3,8);

between ... and ...表示在一个连续的范围内
查询学生是3至8的学生

select * from students where id between 3 and 8;

查询学生是3至8的男生

select * from students where id between 3 and 8 and gender=1;

空判断

注意：null与''是不同的
判空is null
查询没有填写地址的学生

select * from students where hometown is null;

判非空is not null
查询填写了地址的学生

select * from students where hometown is not null;

查询填写了地址的女生

select * from students where hometown is not null and gender=0;

优先级

小括号，not，比较运算符，逻辑运算符
and比or先运算，如果同时出现并希望先算or，需要结合()使用

聚合

为了快速得到统计数据，提供了5个聚合函数
count(*)表示计算总行数，括号中写星与列名，结果是相同的
查询学生总数

select count(*) from students;

max(列)表示求此列的最大值
查询女生的编号最大值

select max(id) from students where gender=0;

min(列)表示求此列的最小值
查询未删除的学生最小编号

select min(id) from students where isdelete=0;

sum(列)表示求此列的和
查询男生的编号之后

select sum(id) from students where gender=1;

avg(列)表示求此列的平均值
查询未删除女生的编号平均值

select avg(id) from students where isdelete=0 and gender=0;

分组

按照字段分组，表示此字段相同的数据会被放到一个组中
分组后，只能查询出相同的数据列，对于有差异的数据列无法出现在结果集中
可以对分组后的数据进行统计，做聚合运算
语法：

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

查询男女生总数

select gender as 性别,count(*)
from students
group by gender;

查询各城市人数

select hometown as 家乡,count(*)
from students
group by hometown;

分组后的数据筛选

语法：

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

having后面的条件运算符与where的相同
查询男生总人数

方案一

select count(*)
from students
where gender=1;


-----------------------------------
方案二：

select gender as 性别,count(*)
from students
group by gender
having gender=1;

对比where与having

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

排序

为了方便查看数据，可以对数据进行排序
语法：

select * from 表名
order by 列1 asc|desc,列2 asc|desc,...

将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推
默认按照列值从小到大排列
asc从小到大排列，即升序
desc从大到小排序，即降序
查询未删除男生学生信息，按学号降序

select * from students
where gender=1 and isdelete=0
order by id desc;

查询未删除科目信息，按名称升序

select * from subject
where isdelete=0
order by stitle;

获取部分行

当数据量过大时，在一页中查看数据是一件非常麻烦的事情
语法

select * from 表名
limit start,count

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

示例：分页

已知：每页显示m条数据，当前显示第n页
求总页数：此段逻辑后面会在python中实现
- 查询总条数p1
- 使用p1除以m得到p2
- 如果整除则p2为总数页
- 如果不整除则p2+1为总页数
求第n页的数据

select * from students
where isdelete=0
limit (n-1)*m,m

总结

完整的select语句

select distinct *
from 表名
where ....
group by ... having ...
order by ...
limit star,count

执行顺序为：
- from 表名
- where ....
- group by ...
- select distinct *
- having ...
- order by ...
- limit star,count
实际使用中，只是语句中某些部分的组合，而不是全部

简介

实体与实体之间有3种对应关系，这些关系也需要存储下来
在开发中需要对存储的数据进行一些处理，用到内置的一些函数
视图用于完成查询语句的封装
事务可以保证复杂的增删改操作有效

关系

一对多的关系：

创建成绩表scores，结构如下
- id
- 学生
- 科目
- 成绩
思考：学生列应该存什么信息呢？
答：学生列的数据不是在这里新建的，而应该从学生表引用过来，关系也是一条数据；根据范式要求应该存储学生的编号，而不是学生的姓名等其它信息
同理，科目表也是关系列，引用科目表中的数据

1.students学生表

CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(11) NOT NULL,
`gender` bit(1) DEFAULT b'1',
`birhtday` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of students
-- ----------------------------
INSERT INTO `students` VALUES ('1', '郭靖', '', '1990-01-06 00:00:00');
INSERT INTO `students` VALUES ('2', '小龙女', '', '1998-02-03 00:00:00');
INSERT INTO `students` VALUES ('4', '郭襄', '', null);
INSERT INTO `students` VALUES ('5', '灭绝师太', '', null);
INSERT INTO `students` VALUES ('6', '如来', '', null);

2.scores成绩表

CREATE TABLE `scores` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`stuid` int(11) DEFAULT NULL,
`subid` int(11) DEFAULT NULL,
`score` decimal(5,2) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `stuid` (`stuid`),
KEY `subid` (`subid`),
CONSTRAINT `scores_ibfk_1` FOREIGN KEY (`stuid`) REFERENCES `students` (`id`),
CONSTRAINT `scores_ibfk_2` FOREIGN KEY (`subid`) REFERENCES `subjects` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of scores
-- ----------------------------
INSERT INTO `scores` VALUES ('1', '2', '2', '89.00');
INSERT INTO `scores` VALUES ('2', '4', '4', '77.00');
INSERT INTO `scores` VALUES ('3', '5', '3', '86.00');
INSERT INTO `scores` VALUES ('4', '1', '1', '99.00');
INSERT INTO `scores` VALUES ('5', '1', '2', '77.00');
INSERT INTO `scores` VALUES ('6', '2', '2', '88.00');
INSERT INTO `scores` VALUES ('9', '4', '4', '77.00');

3.subjects科目表

CREATE TABLE `subjects` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`stitle` varchar(22) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of subjects
-- ----------------------------
INSERT INTO `subjects` VALUES ('1', 'python');
INSERT INTO `subjects` VALUES ('2', 'java');
INSERT INTO `subjects` VALUES ('3', 'c++');
INSERT INTO `subjects` VALUES ('4', 'C#');

创建表的语句如下

create table scores(
id int primary key auto_increment,
stuid int,
subid int,
score decimal(5,2)
);

外键

思考：怎么保证关系列数据的有效性呢？任何整数都可以吗？
答：必须是学生表中id列存在的数据，可以通过外键约束进行数据的有效性验证
为stuid添加外键约束

alter table scores add constraint stu_sco foreign key(stuid) references students(id);

此时插入或者修改数据时，如果stuid的值在students表中不存在则会报错
在创建表时可以直接创建约束

create table scores(
id int primary key auto_increment,
stuid int,
subid int,
score decimal(5,2),
foreign key(stuid) references students(id),
foreign key(subid) references subjects(id)
);

外键的级联操作

在删除students表的数据时，如果这个id值在scores中已经存在，则会抛异常
推荐使用逻辑删除，还可以解决这个问题
可以创建表时指定级联操作，也可以在创建表后再修改外键的级联操作
语法

alter table scores add constraint stu_sco foreign key(stuid) references students(id) on delete cascade;



级联操作的类型包括：

restrict（限制）：默认值，抛异常
cascade（级联）：如果主表的记录删掉，则从表中相关联的记录都将被删除
set null：将外键设置为空
no action：什么都不做

先看个问题

问：查询每个学生每个科目的分数
分析：学生姓名来源于students表，科目名称来源于subjects，分数来源于scores表，怎么将3个表放到一起查询，并将结果显示在同一个结果集中呢？
答：当查询结果来源于多张表时，需要使用连接查询
关键：找到表间的关系，当前的关系是
- students表的id---scores表的stuid
- subjects表的id---scores表的subid
则上面问题的答案是：

select students.sname,subjects.stitle,scores.score
from scores
inner join students on scores.stuid=students.id
inner join subjects on scores.subid=subjects.id;

结论：当需要对有关系的多张表进行查询时，需要使用连接join

连接查询

连接查询分类如下：
- 表A inner join 表B：表A与表B匹配的行会出现在结果中
- 表A left join 表B：表A与表B匹配的行会出现在结果中，外加表A中独有的数据，未对应的数据使用null填充
- 表A right join 表B：表A与表B匹配的行会出现在结果中，外加表B中独有的数据，未对应的数据使用null填充
在查询或条件中推荐使用“表名.列名”的语法
如果多个表中列名不重复可以省略“表名.”部分
如果表的名称太长，可以在表名后面使用' as 简写名'或' 简写名'，为表起个临时的简写名称

练习

查询学生的姓名、平均分

select students.sname,avg(scores.score)
from scores
inner join students on scores.stuid=students.id
group by students.sname;

查询男生的姓名、总分

select students.sname,avg(scores.score)
from scores
inner join students on scores.stuid=students.id
where students.gender=1
group by students.sname;

查询科目的名称、平均分

select subjects.stitle,avg(scores.score)
from scores
inner join subjects on scores.subid=subjects.id
group by subjects.stitle;

查询未删除科目的名称、最高分、平均分

select subjects.stitle,avg(scores.score),max(scores.score)
from scores
inner join subjects on scores.subid=subjects.id
where subjects.isdelete=0
group by subjects.stitle;

多对多的关系

学生表和课程表之间的关系是多对多，即一个学生可以选择多门课程，一门课程，可以被多个学生来修选

多对多（ 3个表= 2个实体表 + 1个关系表 )

※第三范式: 两个实体都建成独立的主表, 另外再单独建一个关系表（采用联合主键）
1、分别建议两个实体表(没有外键，但有自己的主键, 没有冗余信息)

//DROP TABLE stud;
//学生表
CREATE TABLE stud(
  id VARCHAR(32) PRIMARY KEY,
  NAME VARCHAR(30)
);

//课程表

CREATE TABLE ject(
  id VARCHAR(32) PRIMARY KEY,
  NAME VARCHAR(30)
);

2、另外补建一个关系表

CREATE TABLE sj(
  studid VARCHAR(32) NOT NULL,
  jectid VARCHAR(32)
);

//注意，要先建联合主键，再添加外键。顺序不能反了。

ALTER TABLE sj ADD CONSTRAINT sj_pk PRIMARY KEY(studid,jectid);
ALTER TABLE sj ADD CONSTRAINT sj_fk1 FOREIGN KEY(studid) REFERENCES stud(id);
ALTER TABLE sj ADD CONSTRAINT sj_fk2 FOREIGN KEY(jectid) REFERENCES ject(id);

//删除外键约束

//ALTER TABLE sj DROP FOREIGN KEY sj_fk1;
//ALTER TABLE sj DROP FOREIGN KEY sj_fk2;

posted on 2018-03-10 17:45 wei_hu 阅读(217) 评论(0) 编辑收藏举报

刷新页面返回顶部

导航