Mysql查询集合

• 2019 年 10 月 6 日
• 笔记

无论是风里，还是在雨里，我都在这里守候着你～

数据准备及回忆：

进入数据库：

mysql -uroot -p123456

创建一个数据库：

create database student charset = utf8;

进入数据库：

use student;

查看当前所在数据库：

select database();

创建classinfo表：

create table classinfo(

classid int unsigned primary key auto_increment not null,

classname varchar(20) default ''

);

创建studentinfo表：

添加一个is_delete列，表示是否删除。

create table studentinfo(

studentid int unsigned primary key auto_increment not null,

studentname nvarchar(20),

classid int unsigned,

studentsex enum('男','女','保密') default '保密',

studentage tinyint unsigned,

sadress varchar(255),

is_delete bit default 0

);

插入数据：

insert into classinfo values

(0,'Mysql一班'),

(0,'Mysql二班'),

(0,'Mysql三班');

insert into studentinfo values

(0,'张三',1,'男',18,'武汉',0),

(0,'小花',2,'女',20,'长沙',0),

(0,'李四',3,'男',21,'上海',0),

(0,'小猫',1,'女',18,'北京',0),

(0,'王五',2,'男',19,'南京',0),

(0,'小狗',3,'女',25,'成都',0);

查询

–查询所有字段

select * from studentinfo;

select * from classinfo;

–查询指定字段

select studentname,studentsex from studentinfo;

select classname from classinfo;

–使用as给字段添加别名

select studentname as 姓名,studentsex as 性别 from studentinfo;

select classname as 班级名称 from classinfo;

–使用as给表添加别名

select stu.studentname,stu.studentsex from studentinfo as stu;

select cla.classname from classinfo as cla;

–查询性别并去除重复的

select studentsex from studentinfo;

–查出所有的，如果我只想得到男和女和保密可以这样写来消除重复行

select distinct studentsex from studentinfo;

条件查询

— 比较运算符

— 查询年龄等于18的人

select * from studentinfo where studentage = 18;

— 查询年龄不等于18的人

select * from studentinfo where studentage != 18;

或者

select * from studentinfo where studentage <> 18;

— 查询年龄小于20

select * from studentinfo where studentage < 18;

— 查询年龄大于等于20的

select * from studentinfo where studentage >= 18;

–逻辑运算符and，or，not

— 查询18到20之间的

select * from studentinfo where studentage >= 18 and studentage < 20;

— 查询20岁以下，或者性别为女的

select * from studentinfo where studentage < 20 or studentsex = '女';

— 查询不在年龄小于20中的

select * from studentinfo where not studentage < 20;

— 优先级

直接加括号，每个语言都有优先级，记住？不可能的，加括号就好了。

模糊查询

— like

— % 替换一个或者多个

— _ 替换一个

— 查新名字中含有小的人

select * from studentinfo where studentname like '%小%';

— 查询名字中以小开头的人

select * from studentinfo where studentname like '小%';

— 查询名字是两个字的人

select * from studentinfo where studentname like '__';

— 查询所有

select * from studentinfo where studentname like '%';

— 查询名字至少有两个字的

select * from studentinfo where studentname like '__%';

— rlike 正则匹配

— 还是查询以小开头的

select * from studentinfo where studentname rlike '^小.*';

— 查询以小开头以花结尾的

select * from studentinfo where studentname rlike '^小.*花$';

范围查询

–in

— 查询id为1,2,3的

select * from studentinfo where studentid in (1,2,3);

— 查询id不为1,2,3的

select * from studentinfo where studentid not in (1,2,3);

— between and

— 查询年龄18到20之间的(包含18和20)

select * from studentinfo where studentage between 18 and 20;

— not between and

— 查询年龄18到20之间的(包含18和20)

select * from studentinfo where studentage not between 18 and 20;

— 判断是否为空

— 查询名字为空的（这里没有数据，可以添加一个）

select * from studentinfo where studentname is null;

— 查询名字不为空的

select * from studentinfo where studentname is not null;

排序

— order by

— asc 从小到大排序

— desc 从大到小排序

— 查询所有按照从小到大排序

— asc可以省略，默认就是从小到大

select * from studentinfo order by studentage;

select * from studentinfo order by studentage asc;

— 从大到小排序

select * from studentinfo order by studentage desc;

— order by 多个字段

— 如果有两个年龄相同的，他会默认用主键排序，我想让他用班级id排序

select * from studentinfo order by studentage asc,classid desc;

聚合函数

–总数 count

–查询男性有多少人

select count(*) as 男的 from studentinfo where studentsex = 1;

–最大值 max

–查询年龄的最大值

select max(studentage) as 最大年龄 from studentinfo;

–最小值 mix

–查询年龄的最小值

select mix(studentage) as 最小年龄 from studentinfo;

–求和 sum

–查询所有年龄的和

select sum(studentage) as 年龄和 from studentinfo;

–平均值avg

–查询所有年龄的平均值

select avg(studentage) as 年龄平均值 from studentinfo;

–或者

select sum(studentage)/count(*) as 年龄平均值 from studentinfo;

–如果我想四舍五入，保留一位小数

–round(111.444,1) 输出 111.4

select round(avg(studentage),1) as 年龄平均值 from studentinfo;

分组

–group by

–按照性别分组,查询男，女各有几个人。

select count(*),studentsex from studentinfo group by studentsex;

–只查询男的人数

select count(*),studentsex from studentinfo where studentsex = 1 group by studentsex;

–查询男，女各有几个人,并且显示出姓名

select count(*),studentsex,group_concat(studentname) from studentinfo group by studentsex;

count(*)

studentsex

group_concat(studentname)

3

男

张三,李四,王五

3

女

小花,小猫,小狗

–查询男，女各有几个人,并且显示出姓名和年龄

select count(*),studentsex,group_concat(studentname,studentage) from studentinfo group by studentsex;

count(*)

studentsex

group_concat(studentname)

3

男

张三18,李四21,王五19

3

女

小花20,小猫18,小狗25

–但是查询多了会连在一起这样写

select count(*),studentsex,group_concat("name_",studentname,"id_",studentage) from studentinfo group by studentsex;

–自己写看效果

–对分组进行条件查询

–查询男，女人数，并且平均年龄大于20

select count(*),studentsex,group_concat(studentname),avg(studentage) from studentinfo group by studentsex having avg(studentage)>20;

分页

–limit

–查询全部只提取前3行数据

select * from studentinfo limit 3;

–条件查询显示男的2行数据

select * from studentinfo where studentsex=1 limit 2;

–只查询了前面的，剩余的怎么查询？

select * from studentinfo limit 0,3; 第一页数据

select * from studentinfo limit 3,3; 第二页数据

0和1表示从第几个开始。

所有查询 limit是在最后的。

–查询所有女性，按照年龄从大到小，并且显示2条数据。

select * from studentinfo where studentsex=2 order by studentage desc limit 2;

连接查询

–两个表联查

–内连接

select * from studentinfo inner join classinfo;

我们发现，数据乱了。但是实现了双表联查。这不是我们想要的。我们把数据对应起来。

select * from studentinfo inner join classinfo on studentinfo.classid=classinfo.classid;

–如果我不想显示这么多数据，我只想要姓名和班级

select studentinfo.studentname,classinfo.classname from studentinfo inner join classinfo on studentinfo.classid=classinfo.classid;

–名字太长了给表格别名

select s.studentname,c.classname from studentinfo as s inner join classinfo as c on s.classid=c.classid;

所谓内连接就是找到条件，这里是classid中相同的显示出来，如果在studentinfo表中含有classid为4，而classinfo表中没有4就不会显示出来。

–外链接 left join

–外链接分为左连接和右链接

–左连接

–左连接以左表为主，相当于先查询左表的全部内容，右表根据相关字段匹配，没有的以空值匹配。

–这里两个表都是相关的数据，所以和内连接没有差别。

select s.studentname,c.classname from studentinfo as s left join classinfo as c on s.classid=c.classid;

–右链接 right join

select s.studentname,c.classname from studentinfo as s right join classinfo as c on s.classid=c.classid;

自关联

–什么是自关联？

–自己关联自己。

–为什么关联自己？

–比如省市区，表结构都是一样的，如果你创建三个表，比较麻烦，都创建一个表，用自连接就可以完成。

这里补充一个知识，我添加数据不可能去一个一个添加吧，

可以直接在网上下载一个sql脚本，直接执行sql脚本来添加数据，怎么执行sql脚本？

source sql文件名;

比如我写一个创建表的sql：

创建一个txt文件，写上

create table City(

cid int,

cname varchar(20),

pid int

);

修改为sql文件，

source C:/WINDOWS/Temp/city.sql;即可

可能会报错，出现没有权限访问，所以需要把sql文件放在C:WINDOWSTemp下即可

我们添加数据的时候就可以用这种方式来添加。

–自关联还是用的内链接，数据可以去网上下载，很多的。

–查询湖北省的所有区

select * from City c1 inner join City c2 on c1.cid = c2.pid having c1.cname="湖北省"

即可。

子查询

–select 中套select 就是子查询

–在查询的时候先查询子条件中的select，把子条件当做一个条件查询。

–查询年龄最大的

select * from studentinfo where studentage =(select max(studentage) from studentinfo)

–像刚刚的自关联也可以用子查询解决

select * from studentinfo where pid = (select cid from studentinfo where cname = "湖北省")