高效查询方式
1 指定字段进行查看
select 字段1,字段2 from 表名;
2 对字段进行去重查看
SELECT DISTINCT "字段" FROM "表名";
3 where条件查询
SELECT "字段" FROM 表名" WHERE "条件";
4 and 和 or 进行逻辑关系的增加
SELECT "字段" FROM "表名" WHERE "条件1" AND "条件2";
SELECT "字段" FROM "表名" WHERE "条件1" OR "条件2";
5 查询取值列表中的数据
SELECT "字段" FROM "表名" WHERE "字段" IN ('值1', '值2', ...); #in,遍历一个取值列表
6 between的引用
7 like的查询方式
%:百分号表示零个、一个或多个字符 _:划线表示单个字符
-
select * from info2 where hobby like '%ing';
-
select * from info2 where name like '小_';
8 排序方式进行查询
order by,按关键字排序。
- 一般对数值字段进行排序。
- 如果对字符类型的字段进行排序,则会按首字母排序。
SELECT "字段" FROM "表名" [WHERE "条件"] ORDER BY "字段" [ASC|DESC] ;
#ASC是按照升序进行排序的,是默认的排序方式。
#DESC是按降序方式进行排序。
select * from info order by height;
select * from info order by height asc;
select * from info order by height desc;
多个字段排序时,优先排序前一个字段
9 limit
limit 查询前三行
limit查询 从第三行开始,往下数4行
运用函数查询
常用数字的函数
聚合函数
select avg(height) from info;
select count(name) from info;
select count(*) from info;
字符串函数
去除trim
截取 substr
substr(x,y) #截取x字符串 从第y个开始,截取到末尾
substr(x,y,z) #截取x字符串 从第y个开始截取 ,截取长度为z
select substr(hobby,2) from info2;
select substr(hobby,3) from info2;
字段拼接
select concat(name,height) from info;
#将info表中,name字段值和height字段值拼接在一起。
select name || height from info;
#将info表中,name字段值和height字段值拼接在一起,且中间加空格。
select name || ' ' || height from info;
返回字符长度 length
select length(hobby) from info2;
替换 replace
select replace(name,'小','大') from info2;
高级查询语句
GROUP BY(用于分组和汇总)
对GROUPBY后面的字段的查询结果进行汇总分组,通常是结合聚合函数一起使用的
"GROUP BY"有一个原则,凡是在"GROUP BY"后面出现的字段,必须在SELECT 后面出现;
凡是在SELECT 后面出现的、且未在聚合函数中出现的字段,必须出现在"GROUP BY"后面。
汇总统计
select name, count(name) from info group by name;
汇总并对其指定字段(数字类)进行累加
select name,sum(saving) from info3 group by name;
汇总并对其指定字段(数字类)进行累加,再进行降序
select name,sum(saving) from info3 group by name order by sum(saving) desc;
HAVING 过滤
-
用来过滤由"GROUP BY"语句返回的记录集,通常与"GROUP BY"语句联合使用。
-
HAVING语句的存在弥补了WHERE 关键字不能与聚合函数联合使用的不足。
-
where只能对原表中的字段进行筛选,不能对group by后的结果进行筛选。
SELECT 字段1,SUM(字段2) FROM "表格名" GROUP BY 字段1 HAVING(函数条件) ;
select name,sum(saving) from info3 group by name having sum(saving)>1500;
别名设置查询
字段别名
SELECT 字段1,字段2 AS 字段2的别名 from 表名; #AS可以省略不写
select name,sum(saving) as total_saving from info3 group by name having sum(saving)>1000;
select name,sum(saving) as total_saving from info3 group by name having total_saving>1000;
表别名
SELECT 表格别名.字段1 [AS] 字段别名 FROM 表格名 [AS] 表格别名; #AS可以省略不写
表的自我连接
无重复数值排名
有重复数值排名
子查询语句
子查询:连接表格,在WHERE 子句或HAVING 子句中插入另一个SQL语句。
SELECT "字段1" FROM "表格1" WHERE "字段2" [比较运算符] #外查询
(SELECT "字段1" FROM "表格2" WHERE "条件") ; #内查询
子查询返回的结果只能是1列。where条件in什么,子查询的列就是什么。
select * from info where name in(select name from info3 where saving > 1000);
create table ky32 (id int(4));
select id,name,score from info where id in (select id from ky32);
这两张表有相同的名字,查询成绩。
select id,name,score from info where name in (select name from ky32);
多表联查,不要超过三张
select id,name,score from info where id not in (select id from info where score > 70);
取反
子查询语句还可以用在insert update delete
select * from test;
insert into test select * from info where id in (select id from info where sex='女');
修改info表score=100,not in 子查询的条件是id>1.
update info set score=100 where id not in (select id from ky32 where id > 1);
delete from info where id in (select id where score > 80);
求“北京地区”的所有saving值之和
select sum(saving) from info3 where name in (select name from info where address='北京');
EXISTS
![](https://img-blog.csdnimg.cn/9d7c2a897b5d468096ae37ecabb1a597.png)
根据info表,查询出大于80分的同学,然后统计有多少个。
select count(*) from info a
where exists(select id from info where score > 80 and info.id=a.id);查询内外条件是否相同
视图
视图在mysql当中是一个虚拟表。基于查询结果得出的一个虚拟表。在工作当中,我们查询的表未必是真表。有可能是基于真表查询结果的一个虚拟表
可以简化负载的查询语句,隐藏表的细节。提供安全的数据访问。
创建视图表可以是一张表的结果集,也是多个表共同的查询的结果集
无主键等
视图表和真表之间的区别:
1、存储方式不一样,真表存储实际数据,真正写在磁盘当中的。视图不存储任何数据,仅仅是一个查询结果集的虚拟表
2、数据更新:表可以增删改查,但是视图一般情况只能用于查,展示数据
3、占用空间,真表真实占用空间,视图不占用数据库空间。
show full tables in kgc where table_type like 'view';查看视图
drop view v_score;删除视图
多表共同查询
info和test01
根据info的id,name score,加上test01的age
select * from v_info;
update info set score=90 where name ='蔡徐坤';
源表的数据发生变化,视图表的数据同步更新。
update v_info set age=100 where name = '鸡哥';
修改了视图表,源表的数据也会发生变化。一般情况我们是不对视图进行改的操作。
真表占了大多数,视图适用于安全性要求比较高的场景。对外访问,基本都是视图。
null值和空值:
null就是真空,空值就是空气
null啥也没有,空值是空着的没有准确数据的值。
select * from info where score is null;
select count(address) from info;
null是不被统计的,空值可以被统计。
表连接查询
MYSQL数据库中常用的表连接有三种:
- inner join(内连接):只返回两个表中联结字段相等的行(有交集的值)
- 是把两张表或者多张表(三张),同时符合特定条件的数据记录的组合。一个或者多个列相同值才会有查询的结果。
- left join(左连接):返回包括左表中的所有记录和右表中联结字段相等的记录
- 左外连接,用left join关键字来表示。在左连接当中,左侧表是基础表,接收左边的所有行,然后和右表(参考表)记录进行匹配。匹配坐标当中的所有行,以及右表中符合条件的行。
- right join(右连接):返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(内连接)
select * from info A inner join info3 B on A.name = B.name;
select a.id,a.name from test01 a inner join info b on a.id=b.id;
select * from info A, info3 B where A.name=B.name;
select * from info A inner join info3 B using(name);
左连接 left join
select * from info A LEFT JOIN info3 B on A.name=B.name;
右连接 right join
select * from info3 A right join info B on A.name=B.name ;