文章目录
准备工作
MySQL高阶语句
常用查询
对MySQL数据库的查询,除了基本的查询外,有时候需要对查询的结果集进行处理。例如只取10条数据、对查询结果进行排序或分组等等
按关键字排序
PS:类比Windows 任务管理器
使用 select 语句可以将需要的数据从 MySQL 数据库中查询出来,如果对查询的结果进行排序,可以使用 order by 语句来完成排序,并最终将排序后的结果返回给用户。这个语句的排序不光可以针对某一个字段,也可以针对多个字段
语法
select column1,column2, ... from table_name order by column1,column2,... ASC|DESC
ASC|DESC:
ASC 是按照升序进行排序的,是默认的排序方式,即 ASC 可以省略。 SELECT 语句中如果没有指定具体的排序方式,则默认按照 ASC 方式进行排序
DESC 是按降序方式进行排序。当然 order by 前面也可以使用 where 子句对查询结果进一步过滤
1、单字段排序
按分数排序,默认不指定则为升序排列asc
select name,score from info order by score [asc];
按降序排序,使用desc
select name,score from info order by score desc;
2、多字段排序
原则
order by 之后的参数,使用" , " 分割,优先级是按照先后顺序而定
select id,name,hobby from info order by hobby desc,id asc;
小结:order by 之后的第一个参数只有在出现相同的数值,第二个字段才有意义
3、或/且(or/and)
select * from info where score >80 and score <=90;
select * from info where score >90 or score <80;
嵌套/ 多条件查询
select * from info where score >90 or (score >60 and score <90);
4、查询不重复记录
select distinct 字段 from 表名;
select distinct hobby from info;
1、distinct 必须放在开头
2、distinct 只能使用需要去重的字段进行操作
也就是说我sidtinct 了name,hobby两个字段,我们后面想根据id尽心排序,是不可以的,因为只能name,age两个字段进行操作
3、distinct 去重两个字段时,含义是:几个字段同时重复时才会被过滤
对结果进行分组
通过SQL 查询出来的结果,还可以对其进行分组,使用 group by 语句来实现,group by 通常都是结合聚合函数一起使用的,常用的聚合函数包括:计数(COUNT)、求和(SUM)、求平均数(AVG)、最大数(MAX)、最小值(MIN),group by 分组的时候可以按一个或多个字段对结果进行分组处理
语法:
select 字段,聚合函数 from 表名,(where 字段名 (匹配) 数值) group by 字段名;
对info表中兴趣(hobby)相同的id进行数量统计,并按照相同hobby进行分组
基于上一条操作,结合order by 把统计的id数量按升序排序
限制结果条目
在使用 MySQL select 语句进行查询时,结果集返回的时所有匹配的记录。有时候仅需要返回第一行或者前几行,这时候就需要用到limit 子句
语法:
select column1, column2, ...from table_name limit [offset,] number offset,number
limit 的第一个参数是位置偏移量(可选参数),是设置 MySQL 从哪一行开始显示。
如果不设定第一个参数,将会从表中的第一条记录开始显示。需要主义的是,第一条记录的位置偏移量是0.第二条是1 ,以此类推。第二个参数是设置返回记录行的最大数目。
offset:索引下标
number:索引下表之后的几位
可以结合order by 排序
设置别名
在MySQL 查询时,当表的名字比较长或者表内某些字段比较长时,为了方便书写或者多次使用相同的表,可以给字段列或者表设置别名。使用的时候直接使用表名,简介明了,增强可读性
语法:
对于列的别名
select column_name as alias_name from table_name
对于表的别名
select column_name(s) from table_name as alias_name
在使用 AS 后,可以用 alias_name 代替 table_name,其中 AS 语句是可选的。AS 之后的别名,主要是为表内的列或者表提供临时的名称,在查询过程中使用,库内实际的表名或者字段名是不会被改变的
列别名设置示例:
select name as 姓名,score as 成绩 from info;
表数据别名设置示例
select i.name as 姓名,i.score as 成绩 from info as i;
使用场景:
1、对复杂的表进行查询的时候,别名可以缩短查询语句
2、多表相连查询的时候(通俗易懂、简短sql语句)
AS作为连接语句
create table ccc as select * from info;
此处AS起到的作用:
1、创建一个新表ccc 定义表结构,插入表数据(都与info表相同)
2、但是“约束”没有被“复制”过来,但是如果原表设置了主键,那么附表的: default 字段会默认设置一个0
通配符
通配符主要用于替换字符串中的部分字符,通过部分字符的匹配将相关结果查询出来
通常通配符都是跟 like (模糊查询) 一起使用的,并协同 where 子句共同来完成查询任务。常用的通配符有两个,分别是
%:百分号表示零个、一个或者多个字符
_:下划线表示单个字符
模糊查询“%”示例
select * from ccc where address like '南%';
模糊查询 ’ _ ’ 示例
select * from ccc where address like '_京';
子查询
定义、示例
子查询也被称作内查询或者嵌套查询,是指在一个查询语句里面还嵌套着另一个查询语句。
子查询语句是先于主查询语句被执行的,其结果作为外层的条件返回给主查询进行下一步的查询过滤。
PS:子语句可与主语句所查询的表相同,也可以是不同表
示例
select * from info where id in (select id from ccc where score>80);
主语句:select name,acore from info where id
子语句:select id from ccc where score>80
in:将主表和子表关联起来的语法
in的用法
in 之后的子查询语句会给他提供的一个范围(集合),作为‘ in ’ 之前where 的判断条件
示例:
需求:查询info表id为1,3,5,7的数据(通过子查询的方式)
单表查询方式
select * from info where id in (1,3,5,7);
多表查询方式:
create table num (id int(4));
insert into num values(2),(4),(6);
select * from info where id in (select id from num);
2、子查询-别名as
将结果集作为一张“表”进行查询的时候,我们也需要用到别名
select id from (select id,name from info);
报错原因
select * from 表名,此为标准格式,而以上的查询语句,“表名”的位置其实是一个结果集,MySQL并不能识别,而此时给与结果集设置一个别名,并且以“select a.id,name from a ”的方式查询,将此结果集“视为”一张表,就可以正常查询出数据了
改进
子查询-exists
select count(*) as number from ccc where exists (select id from ccc where name='钱怡');
as number 将count统计的结果作为number(列名)返回
exists:布尔值判断,后面的子查询语句是否成立
where:之后跟条件判断
将exists:只是为了判断exists之后的条件是否成立,如果成立,则正常执行主语句的匹配,如不成立,则不会执行主语句查询
PS:count 为计数,sum为求和,使用sum求和结合exists,如查询结果集不成立的话,输出为null
视图
数据库中的虚拟表,这张虚拟表中不包含真实数据,只是做了映射
可以理解成镜花水月/倒影,动态保存结果集(数据)
示例(将我们想给他们看的数据显示出来)
满足80分的学生展示在视图中
PS:这个结果会动态变化,同时可以给与不同的人群(例如权限范围)展示不同的视图
创建视图
create view v_score as select * from info where score>=80;
show table status\G;
查看视图
select * from v_score;
修改表中数据并再次查看视图
update info set score=88 where id=6;
null值
定义
在SQL 语句使用过程中,经常会碰到 NULL 这几个字符。通常使用null 来表示缺失的值,也就是表中该字段是没有值的
如果在创建表时,限制某些字段不为空,则可以使用 not null 关键字,不使用则默认可以为空。
在向表内插入记录或者更新记录时,如果该字段没有 not null 并且没有值,这时候新纪录的该字段将被保存为null 。需要注意的是,null值与数字0 或者空白(spaces)的字段是不同的,值为null的字段是没有值的
在SQL语句中,使用 is null 可以判断表内某个字段是不是null值,相反的用 is not null 可以判断不是null 值
null值和空值的区别(空气和真空)
控制长度为0,不占空间,null 值的长度为null,占用空间
is null无法判断空值
空值使用 = 或者 <> 来处理(!=)
count()计算时,null会忽略,空值会加入计算