MySQL高阶语句

最新推荐文章于 2024-10-08 12:12:43 发布

確定饿的猫

最新推荐文章于 2024-10-08 12:12:43 发布

阅读量906

点赞数 29

文章标签： mysql 数据库 linux

本文链接：https://blog.csdn.net/qq_59677536/article/details/141867135

版权

MySQL增删改查作为最基础的操作，应该都比较熟悉，但有时候需要对数据库的数据进行一些像excel表格那样的筛选，会手足无措，select 一查查出来许多数据，其实，MySQL语句中也有筛选、排序等操作。

首先创建一个数据表，插入几条数据

create table abc (id int,name varchar(10) primary key not null ,score decimal(5,2),address varchar(20),hobbyid int(5));

insert into abc values(1,'james',80,'beijing',2);

insert into abc values(2,'curry',90,'shenzhen',2);

insert into abc values(3,'george',60,'shanghai',4);

insert into abc values(4,'westbrook',99,'hangzhou',5);

insert into abc values(5,'russel',98,'laowo',3);

insert into abc values(6,'durant',10,'nanjing',3);

insert into abc values(7,'rose',11,'nanjing',5);

insert into abc values(8,'jay',46,'suzhou',6);

查看一下数据表，接下来对查询进行操作

1.排序

排序可以升序或者降序，还可以指定字段进行排序，甚至在字段值有重复时，可以指定次要字段排序

格式就是：select 字段1，字段2 from 表名 order by 指定字段.... ASC/DESC

ASC就是升序，DESC就是降序

select * from abc；

将表按照分数高低降序排序

咱们再插入几条address=nanjing的数据

insert into abc values(9,'john',73,'nanjing',1);

insert into abc values(10,'lisa',56,'nanjing',2);

可以查询address=nanjing的数据中，将他们的分数升序排序，只显示名字、分数、地址

select name,score,address from abc where address='nanjing' order by score asc;

当排序的字段值出现重复时，可以设置次要字段，然后按照次要字段排序，假设都是降序

select * from abc order by hobbyid desc,score desc;

也可进行多条件筛选排序

比如大于90分或者小于60分的，大于60且小于90的

select *from abc where score>90 or score <60;

select *from abc where score>60 and score <90;

select * from abc where score<20 or (score>80 an score<100);

需要筛选的结果，去除重复的，用 distinct 命令

select distinct address from abc;

这样南京这些地方就只出现了一次

2.对结果进行分组

通过 SQL 查询出来的结果，还可以对其进行分组，使用 GROUP BY 语句来实现，GROUP BY 通常都是结合聚合函数一起使用的，常用的聚合函数包括：计数（COUNT）、求和（SUM）、求平均数（AVG）、最大值（MAX）、最小值（MIN），GROUP BY 分组的时候可以按一个或多个字段对结果进行分组处理。

假如现在需要计数各个地方有多少，比如有多少beijing的，多少nanjing的

select count(name), address from abc group by address;

四个nanjing，其他地方都是一个

同样的也可筛选出大于80分的，大于90分的

select count(name), score from abc where score>80 group by id;

如果不加group by 的话，指挥计入大于80分的个数

3.限制结果条目

有些时候，并不需要显示那么多条目的数据，只需要查看特定几行的数据，用到limit，limit后面有两个参数，第一个是位置偏移量（可选参数），是设置 MySQL 从哪一行开始显示。如果不设定第一个参数，将会从表中的第一条记录开始显示。需要注意的是，第一条记录的位置偏移量是 0，第二条是 1，以此类推。第二个参数是设置返回记录行的最大数目。

比如现在，我需要看这个表格中，分数在3-8位的，不看前两位和最后两位，就用limit 2,6，会显示从第三行开始及往后的六行

select * from abc order by score asc limit 2,6;

同理倒序也可以，根据别的字段排序也行

如果只显示分数前三的，那就直接limit 3; 就可以了

select * from abc order by score desc limit 3;

4.别名

在 MySQL 查询时，当表的名字比较长或者表内某些字段比较长时，为了方便书写或多次使用相同的表，可以给字段列或表设置别名。使用的时候直接使用别名，方便快捷

select id as 序号,name as 姓名,score as 分数,address as 地址,hobbyid as 爱好 from abc;

这里我没有特别长的字段，一般对比较长的字段使用别名效果会更好，或者对复杂的表进行查询的时候，别名可以缩短查询语句的长度

5.通配符

通配符和在Linux操作命令find 使用规则差不多，或者说是和正则表达式的通配符差不多，通常通配符都是跟 like 一起使用的，并协同 where 子句共同来完成查询任务

%：百分号表示零个一个或多个字符

_：下划线表示单个字符

查询 j 开头的，j%

select * from abc where name like 'j%';

_只能固定查询一个字符，就是假如查询ja_，只会查询到jay，不会有james，因为_ 只能代替一个字符

6.子查询

子查询也被成为内查询或者嵌套查询，就是一个查询内嵌套着另一个查询，先执行子查询，子查询执行完的结果返回给主查询，主查询再在子查询的结果里进行查询。

子语句与主语句查询的表可以相同也可以不相同

select name,score from abc where id in (select id from abc where score>80);

子查询也可以不在同一张表，先创建另一张表，数据比较随便，

这时候再查询新创建的表中的id，然后再student 表中id 在 abc 表中也有的

select id, name,score from abc where id in(select id from student);

student表中有id 12345 ，所以查询结果也是id为 12345 结果

也可以取反，用id not in ()，这样的话，输出的结果就与之相反，查询除了12345 以外的结果

也可以将abc 表中的内容都插入到新表中，我们创建一个新表 demo

这样也可以做到复制一张表

exist 关键字在子查询时，主要用于判断子查询的结果集是否为空。如果不为空，则返回 TRUE；反之，则返回 FALSE

....where exists (select id from abc where score>100);

这样的话如果abc表中有大于100的就会返回空，如果<90，只要表中存在score<90的数据，就会执行主语句

7.视图

视图作为MySQL的一种优化操作，可以针对不同的用户呈现不同的结果，相对有跟高的安全性。视图本质上就是对真实表做了一个映射，比方说还是那个表，我只想让某用户查看到姓名和地址，另一用户查看到姓名和分数等等。

视图适合于多表连接浏览时使用，不适合增、删、改

视图没有实际的物理记录，而表有

表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能有创建的语句来修改

视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。

表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。

视图的建立和删除只影响视图本身，不影响对应的基本表。（但是更新视图数据，是会影响到基本表的）

创建视图

create view 视图名 as select * from abc where 匹配条件

创建完成后查看表结构

查看视图的时候只能看见创建视图时符合条件的数据

修改视图的数据会影响原表的数据，但增删改不会

重新创建视图

create view v_view as select * from abc where score >90

将russel 分数改为88，然后再次查看视图

只有一条数据了

我们再看看原表

原表也被修改了

多表创建视图

create view v_mix(id,name,score,sex) as select abc.id,abc.name,abc.score,student.sex from abc,student where abc.id=student.id;

还是之前那两张表，多表创建视图后效果如下

8.null值

我们在创建、查看数据库时经常使用null字符，通常使用 NULL 来表示缺失的值，也就是在表中该字段是没有值的。如果在创建表时，限制某些字段不为空，则可以使用 NOT NULL 关键字，不使用则默认可以为空。在向表内插入记录或者更新记录时，如果该字段没有 NOT NULL 并且没有值，这时候新记录的该字段将被保存为 NULL。需要注意的是，NULL 值与数字 0 或者空白（spaces）的字段是不同的，值为 NULL 的字段是没有值的。在 SQL 语句中，使用 IS NULL 可以判断表内的某个字段是不是 NULL 值，相反的用 IS NOT NULL 可以判断不是 NULL 值。

select * from 表名 where addr is NULL;

查询为空的值