mysql入门（三）

• Z时代
• 2024-01-10
• 分类：综合

数据库查询操作

关键字:select
select   结果集   from   数据源   [where 条件]   [group by(分组)]   [having 条件]   [order by(排序) asc|desc]   [limit(限制) s,n]

结果集(字段,*代表所有字段)；s start 从第几条开始,查询n条数据,s不写默认从第一条开始。

(1)字段表达式

select 除了可以做查询,还可以调用函数,也可以用来做计算。
select rand();

(2)from子句

from 子句后面跟的是数据源。

(3)dual表

dual不是一个真实的表,只是一个语法；​

dual的主要作用是为了保证select语句的完整性而设计的。

(4)where子句

where子句的作用,筛选过滤数据；

一般跟在数据源的后面,位置不能乱放。

(5)is null | is not null

筛选数据是否为空或不为空；

放在where的后面,是where的判断条件之一。

(6)between | not between

筛选数据的范围,用在数字集上

select*fromuserwhere uid between2and4;
select*fromuserwhere uid>1and uid<5;

(7)MySQL中的运算符

a.算数运算符

+  -  *  /  %  ++  --

b.比较运算符

>  <  >=  <=  != (<>)

c.逻辑运算符

and   与
or      或
not    非

(8).聚合函数

max()    最大值
min()     最小值
sum()    求和
avg()     平均值  
count()  计数
聚合函数只能写在结果集中。

(9).通配符

_   ：一次通配一个字符
​
%  ：一次通配多个字符

(10).模糊查询(like)

作用:查找长字符串中的某一个关键字

select*fromuserwhere passwd like"_介石";  
​
select*fromuserwhere passwd like"%介石%";

(11).分组查询

关键字:group by
将相同的值分为N个类别,使得统计变的更加的便捷。

createtable stuinfo(
sid int auto_increment primarykey comment"学号",
sname char(64) comment"姓名",
sex enum("女","男"),
age tinyint unsigned,
city char(64)
);
insertinto stuinfo values(null,"小马",2,20,"杭州"),(null,"小王",2,21,"潮州"),(null,"小林",2,18,"大连"),
                          (null,"小强",2,30,"宿迁"),(null,"小军",2,80,"仙桃");
insertinto stuinfo values(null,"小梅",1,18,"杭州"),(null,"小芳",1,34,"潮州"),(null,"小宝",2,18,"大连"),
                          (null,"小天",1,21,"宿迁"),(null,"小杨",1,11,"仙桃");
createtable stumarks(
stuno int comment"学号",
ch float comment"语文",
ma float comment"数学"
);
insertinto stumarks values(1,88,99),(2,100,0),(3,0,0),(4,80,59),(5,100,100);
insertinto stumarks values(6,88,99),(7,100,0),(8,10,0),(9,80,59),(10,100,100);

分组查询

-- 根据性别分组查询 （得不到想要的结果）
select*from stuinfo groupby sex;
-- 根据性别+城市
select*from stuinfo groupby sex,city;
​
-- group_concat(),concat是级联(合并)；得到根据性别分好的两组姓名
select group_concat(sname) from stuinfo groupby sex;

(12).回溯统计(了解)

关键字:with rollup。不常用，放在group by 的后面，在统计的基础上在统计一次。

select*,group_concat(sname) from stuinfo groupby sex with rollup;

(13).having条件

where:设置筛选条件,只能在group by前面；
having:设置筛选条件,要求条件匹配的字段必须在结果集当中,在group by后面。

select * from stuinfo where age > 18;               # 正确
​
select * from stuinfo having age > 18;              # 正确
​
select  sname from stuinfo where age > 18;          # 正确
​
select  sname,age from stuinfo having age > 18;     # 错误
​
select * from stuinfo where age>18 group by sex;    # 正确
​
select * from stuinfo having age>18 group by sex;   # 错误
​
select sname,age from stuinfo where city="杭州" group by sex having age>18; # 正确

(14).order by排序[asc|desc]

order by 添加在group by 的后面,只是负责排序​

1.asc,升序排列,默认值

2.desc,降序排列​

select* `count` from stuinfo where age>18orderby sid desc;

(15).limit限制

limit 在分页的时候是经常使用的；limit 在order by 的后面,是最后一个子句。

1.  limit 10  #默认从第一条开始,向后取10条
2.  limit 10,10   #从第10条开始向后取10条

​select*from stuinfo limit 3;
​
select*from stuinfo limit 0,3;
​
select*from stuinfo limit 5,5;
​
select*from stuinfo where age>18orderby sid desc limit 3;

(16).查询数据中的选项

distinct:去重。

selectdistinct city from stuinfo;

(17).on duplicate key update

on duplicate key update 有重复则更新，配合insert使用的一组工具型sql子句​。

假如数据库当中有了已经有了一个id为1的实体,我还箱在插入id为1的实体,一定不能成功。​

如果表中没有id为11的实体则插入,如果有,则执行指定的更新：

insertinto stuinfo values(11,"四毛",2,20,"杭州") on duplicate keyupdate sname="四毛",sex=1;

数据库联合查询

关键字：union

1.all:可写可不写；2.distinct:去重。

-- 查找大连的男生和杭州的女生
select*from stuinfo where city="大连"and sex=2unionselect*from stuinfo where city="杭州"and sex=1;
select*from stuinfo where (city="大连"and sex=2) or (city="杭州"and sex=1);

使用union的要求:

1.两边的字段数要相同；
2.字段名和数据类型可以不一致,按照左边的表字段返回。

select sid,sname,age from stuinfo unionselect*from stumarks; -- 会报错，两边字段数不相等
select sid,sname,age from stuinfo unionselect*from stumarks;

男生的年龄降序,女生的年龄升序。

使用union分段排序一定不会成功,必须添加limit,limit的值要足够大(100w就可以了)。为什么给100就可以了:每张极限效率在80W左右。

(select*from stuinfo where sex=2orderby age desc limit 999999999) union (select*from stuinfo where sex=1orderby age limit 999999999);

多表查询

分类

1.内连接      inner join
2.外连接      outer join
   左外连接  left join
   右外连接  right  join
3.交叉连接  cross join
4.自然连接  natural join

(1).内连接(inner join)

连接的关键字应当写在表和表之间；
给表起别名 stuinfo as a == stuinfo a,as默认可以省略；
给两张变建立关联,on a.sid = b.stuno；

select*from stuinfo a innerjoin stumarks b on a.sid = b.stuno;

inner join 可以直接写成 join

select*from stuinfo a join stumarks b on a.sid = b.stuno;

内连接的特点:内连接在关联表两边的数据完全对等的情况下才返回,任何一个表的数据不完整,则不显示。

(3).外连接(outer join)

a.左外连接​

特点:以左边表的数据为基准,只要左边表有该条数据,右表数据为空自动填补Null

select*from stuinfo a leftouterjoin stumarks b on a.sid = b.stuno;

b.右外连接

特点:以右边表的数据为基准,只要右边表有该条数据,左表数据为空自动填补Null

select*from stuinfo a rightouterjoin stumarks b on a.sid = b.stuno;​

(4).交叉连接(cross join)

-- 交叉连接的返回结果和内连接一致
select*from stuinfo a crossjoin stumarks b on a.sid=b.stuno;
​
-- 笛卡尔积写法
select*from `stuinfo`,`stumarks`;

(5).自然连接(natural join)

自动查找关联字段。
1.自动判断条件,依据同名字段；
2.如果没有同名字段,返回的是笛卡尔积；
3.自动返回并整理结果：a.连接的字段返回一个；b.连接的字段放前面。
1). natural join               自然内连接
2). natural left join         自然左外连接
3). natural right join       自然右外连接

select*from stuinfo a natural join stumarks b;

(6).using()

using()是一个MySQL内置函数。作用:自动连接关联字段。

select*from stuinfo leftjoin stumarks using(`sid`);

练习

-- 1.显示地区和每个地区参加语文考试的人数,按照地区人数降序排列 
/* count(ch) as `count`:这里的count为什么起别名,函数只可以写在结果集当中,排序需要使用count的结果,别名可以避免这个规则.
count不统计Null */
select city,count(ch) as `count` from stuinfo  leftjoin stumarks using(`sid`) groupby `city` orderby `count` desc;
-- 2.显示男生和女生人数
selectsum(sex=1) 女,sum(sex=2) 男 from stuinfo;
select sex,count(sex) from stuinfo groupby sex;
select sex,count(sex) from stuinfo where sex=1unionselect sex,count(sex) from stuinfo where sex=2;
-- 3.显示每个地区的男生人数和女生人数,总人数
selectsum(sex=1) 女,sum(sex=2) 男,count(sex) 总人数 from stuinfo groupby city;

子查询

查询语句中包涵一个查询，外面的查询是父查询,里面的就是子查询，子查询是为父查询提供查询条件。

查询数学成绩大于80分的学生信息(不包含成绩)；
子查询返回的值应该是单一的一个字段；
在子查询能用in的,绝对不用=。

select*from stuinfo where sid in (select sid from stumarks where ma >80);
​
select a.*from stuinfo a leftjoin stumarks using(sid) where ma>80;​

查找数学成绩最高分的学生信息

selectmax(b.ma),a.*from stuinfo a leftjoin stumarks b using(sid);

写子查询,先理清楚关系:返回数学最高分的学生sid,查找学生信息

select*from stuinfo where sid in (select sid from stumarks where ma in (selectmax(ma) from stumarks));

(1)in | not in

在查询中,返回的结果是一个集合,等于号会发生错误,这时是用in 和 not in来解决集合问题

-- 查询数学成绩不及格的学生
select*from stuinfo where sid notin (select sid from stumarks where ma>=60);
​
select*from stuinfo where sid in (select sid from stumarks where ma<60);
​
-- 查询没有参加数学考试的学生
select*from stuinfo where sid in (select sid from stumarks where ma isnull);
​
-- 查询没有参加考试的学生
select*from stuinfo where sid in (select sid from stumarks where ma isnullor ch isnull);

(2)some,all,any

some和any是一样的,表示的是一些,类似于in。

!=some 和 !=any  != not in； !=all  =  in

-- 查询数学成绩不及格的学生
select*from stuinfo where sid =some (select sid from stumarks where ma<60);
​
select*from stuinfo where sid =any (select sid from stumarks where ma<60);
select*from stuinfo where sid =all (select sid from stumarks where ma<60);      ​-- 查不出
​
-- 查询数学成绩90分以上的学生
select*from stuinfo where sid !=some (select sid from stumarks where ma<90);    -- 查不出
select*from stuinfo where sid !=any (select sid from stumarks where ma<90);     ​-- 查不出
​
select*from stuinfo where sid !=all (select sid from stumarks where ma<90);
​
select*from stuinfo where sid notin (select sid from stumarks where ma<90);

主要记住in和 not in即可。

(3)exists | not exists

返回bool值

-- 如果有人数学成绩达到100分,则显示所有人的信息
select*from stuinfo whereexists(select sid from stumarks where ma=100);
​
-- 上面的条件取反（找到有不成立的条件）
select*from stuinfo wherenotexists(select sid from stumarks where ma <0);

https://blog.csdn.net/nihui123/article/details/90452750

https://www.runoob.com/mysql/mysql-database-export.html

以上是 mysql入门（三） 的全部内容， 来源链接： utcz.com/z/532860.html