MySQL-高阶语句

一、按关键字查询排序：

1.按排序方式进行查询：

1.1 升序：

按分数排序，默认不指定是升序排列：

mysql> select id,name,score from 表名 order by score;

1.2 降序 ：

mysql> select id,name,score from 表名 order by score desc;

1.3 order by 关键字排序：

 特点：

• 一般对数值字段进行排序。
• 如果对字符类型的字段进行排序，则会按首字母排序。
• order by 还可以结合where进行条件过滤

筛选地址是南京的学生按分数降序排列：

select id,name,score,address from 表名 where address='nanjing' order by score desc;

查询学生信息按成绩降序排，id也按降序排列 :

mysql> select id,name,age，address，score from 表名 order by  score desc,id desc;

按成绩降序排列，id升序排列 :

mysql> select id,name,age,address,score from ky35 order by score desc,iid;

2.区间判断以及查询不重复记录：

2.1 and/or - 且/或：

查询成绩，大于70小于90:

mysql> select * from ky35 where score>70 and score<90;

查询大于70小于20 :

mysql> select * from ky35 where score>70 or score<20;

 2.2 嵌套/多条件：

查询大于90，或者大于60且小于等于80的

mysql> select * from ky35 where score>90 or (score >60 and score<=80);

 2.3distinct 去重查询：

格式：
select distinct 字段 from 表名﹔

3.where 条件查询：

mysql> select * from ky35 where id=6;

4. 查询取值列表中的数据：

 SELECT "字段" FROM "表名" WHERE "字段" IN ('值1'， '值2'， ...);  
 #in,遍历一个取值列表

mysql> select * from ky35 where id in(1,2,3,6);

5.between 的引用：

 SELECT "字段" FROM "表名" WHERE "字段" BETWEEN '值1' AND '值2';

二、对结果进行分组：

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

#语法
SELECT column_name, aggregate_function(column_name)FROM table_name WHERE column_name operator value GROUP BY column_name;
 
#解释
SELECT column_name, aggregate_function(column_name)：这部分是选择你要查询的列以及对该列应用的聚合函数。常见的聚合函数有COUNT(), SUM(), AVG(), MAX(), MIN()等。例如，如果你想计算salary列的总和，可以写成SELECT salary, SUM(salary)。
 
FROM table_name：这部分指定了你从哪个表中获取数据。
 
WHERE column_name operator value：这是筛选条件，只有满足该条件的行才会被包括在内。operator可以是等于=, 不等于!=, 大于>, 小于<, 等等，例如WHERE department = 'Sales'。
 
GROUP BY column_name：这部分指定了按照哪一列进行分组。在进行聚合操作时，同一分组内的行会被一起处理。例如，如果你想要按部门统计薪水总额，可以写成GROUP BY department

1.简单操作： 

 按age相同的分组，计算相同分数的学生个数（基于id个数进行计数）

mysql> select age,count(id)from ky35 group by age;

按age相同的分组，计算相同分数的学生个数（基于name个数进行计数）

mysql> select age,count(name)from ky35 group by age;

结合where语句，筛选分数大于等于60的分组，计算学生个数：

mysql> select count(name),age from ky35 where score>=60 group by age;

结合order by把计算出的学生个数按升序排列：

mysql> select count(name),score,age from ky35 where score>=60 group by  age order by count(name);

2.常用数学的函数：

数学函数	作用
abs(x)	返回x的绝对值
rand()	返回0到1的随机数
mod(x, y)	返回x除以y以后的余数
power(x, y)	返回x的y次方
round(x)	返回离x最近的整数
round(x, y)	保留x的y位小数四舍五入后的值
sqrt(x)	返回x的平方根
truncate(x, y)	返回数字x截断为y位小数的值 #不四舍五入
ceil(x)	返回大于或等于x的最小整数
floor(x)	返回小于或等于x的最大整数
greatest(x1,x2,...)	返回集合中最大的值
least(x1,x2,...)	返回集合中最小的值

mysql> select abs(-1),rand(),mod(5,3),power(2,3);

 

3.聚合函数：

聚合函数	含义
avg()	返回指定列的平均值
count()	计数   返回指定列中非 NULL 值的个数
min()	返回指定列的最小值
max()	返回指定列的最大值
sum(字段)	返回指定列的所有值之和

 

三：限制结果条目（limit ）：

limit 限制输出的结果记录，在使用 MySQL SELECT 语句进行查询时，结果集返回的是所有匹配的记录（行）。有时候仅需要返回第一行或者前几行，这时候就需要用到 LIMIT 子句

# 语法
SELECT column1, column2, ... FROM table_name LIMIT [offset,] number

LIMIT 的第一个参数是位置偏移量（可选参数），是设置 MySQL 从哪一行开始显示。 如果不设定第一个参数，将会从表中的第一条记录开始显示。需要注意的是，第一条记录的 位置偏移量是 0，第二条是 1，以此类推。第二个参数是设置返回记录行的最大数目。

查询所有信息显示前4行记录：

mysql> select * from ky35 limit 3;

从第4行开始，往后显示3行内容：

select * from ky35 limit 3,3;

结合order by语句，按id的大小升序排列显示前三行：

mysql> select id,name from ky35 order by id limit 3;

输出最后三行：

mysql> select * from ky35 order by id desc limit 3;

四：设置别名：

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

# 语法
 
 SELECT 字段1,字段2 AS 字段2的别名 from 表名;   #AS可以省略不写
 
对于列的别名：SELECT column_name AS alias_name FROM table_name;
对于表的别名：SELECT column_name(s) FROM table_name AS alias_name;

示例：
列别名设置示例：
select name as 姓名,score as 成绩 from info;    
 
如果表的长度比较长，可以使用 AS 给表设置别名，在查询的过程中直接使用别名
临时设置info的别名为i
select i.name as 姓名,i.score as 成绩 from info as i;

对于列：

mysql> select name as 姓名,score 成绩,address 地址 from ky35

加不加as都可以显示

对于表:

mysql> select k.name as 姓名,k.score 成绩,k.address 地址 from ky35 as k;

使用场景：

• 对复杂的表进行查询的时候，别名可以缩短查询语句的长度
• 多表相连查询的时候（通俗易懂、减短sql语句）

 查询ky35表的字段数量，以info显示：

AS 还可以作为连接语句的操作符：

mysql> create table s1 as select * from ky35 where address='nanjing';
 
查看
mysql> select * from s1;

此处AS起到的作用：
1、创建了一个新表t1 并定义表结构，插入条件筛选的表数据
2、但是”约束“没有被完全”复制“过来  

在为表设置别名时，要保证别名不能与数据库中的其他表的名称冲突。 列的别名是在结果中有显示的，而表的别名在结果中没有显示，只在执行查询时使用。

五、通配符：

通配符主要用于替换字符串中的部分字符，通过部分字符的匹配将相关结果查询出来。

通常通配符都是跟 LIKE 一起使用的，并协同 WHERE 子句共同来完成查询任务。常用的通配符有两个，分别是：

%	百分号表示零个、一个或多个字符
_	下划线表示单个字符

查询地址是n开头的记录：

查询地址里是n和 j和 i 中间有一个字符的记录：

select * from ky35 where address like 'n_n_i__';

查询地址中间有 n 的记录：

mysql> select * from ky35 where address like '%n%';

 六、子查询：

子查询也被称作内查询或者嵌套查询，是指在一个查询语句里面还嵌套着另一个查询语句。子查询语句是先于主查询语句被执行的，其结果作为外层的条件返回给主查询进行下一 步的查询过滤。

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

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

• 主语句：select name,score from info where id
• 子语句(集合)： select id from info where score >80

子语句中的sql语句是为了，最后过滤出一个结果集，用于主语句的判断条件 

in: 将主表和子表关联/连接的语法

子查询：

mysql> select * from ky35 where id in (select id from ky35 where age > 20);

多表查询：

mysql> select * from ky35 where id in(select id from ky36);

子查询不仅可以在select语句中使用，在inert、update、delete中也可以同样适用。在嵌套的时候，子查询内部还可以再次嵌套新的子查询，也就是说可以多层嵌套。

1.用法：

语法 IN 用来判断某个值是否在给定的结果集中，通常结合子查询来使用

语法： <表达式> [NOT] IN <子查询>

当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE。 若启用了 NOT 关键字，则返回值相反。需要注意的是，子查询只能返回一列数据，如果需 求比较复杂，一列解决不了问题，可以使用多层嵌套的方式来应对。 多数情况下，子查询都是与 SELECT 语句一起使用的

1.1select语句：

查询分数大于60的记录：

子查询还可以用在 INSERT 语句中。子查询的结果集可以通过 INSERT 语句插入到其 他的表中

1.2 insert语句：

将ky36里的记录全部删除，重新插入info表的记录：

mysql> insert into ky36 select id,name,age from ky35 where id in (select id from ky35);

UPDATE 语句也可以使用子查询。UPDATE 内的子查询，在 set 更新内容时，可以是单独的一列，也可以是多列。

1.3 update语句：

update ky35 set name='liu' where id in (select id from ky36 where id=2);

not in:表示取反 

1.4 delete语句：

删除分数大于80的记录：

1.5 exists关键字：

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

1.6 结果集：

将结果集作为一张表进行查询的时候，我们也需要用到别名。

从info表中的id和name字段的内容作为“内容”，输出id的部分
select a.id from (select id,name from ky35) a;
select 表.字段 ，字段 from 表；

七、mysql视图：

1.视图的定义：

• 数据库中的虚拟表，这张虚拟表中不包含真实数据，只是做了真实数据的映射
• 视图可以理解为镜花水月/倒影，动态保存结果集（数据）
• 基础表info （7行记录） ——》映射（投影）--视图

功能：

• 简化查询结果集、灵活查询、可以针对不同用户呈现不同结果集、相对有更高的安全性。
• 本质而言，视图是一种select（结果集的呈现）

2.视图的创建：

#语法：
CREATE VIEW "视图表名" AS "SELECT 语句";

创建视图，把ky35表内score字段大于等于30的数据添加到名为v_gg的视图中： 

查看视图：

3.作用场景：

• 视图适合于多表连接浏览时使用！不适合增删改
• 而存储过程适用于使用较频繁的sql语句，这样可以提高执行效率！

4.视图和表的区别和联系：

区别：

• 视图是已经编译好的sql语句。而表不是
• 视图没有实际的物理记录。而表有。 show table status\G
• 表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能有创建的语句来修改
• 视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。
• 表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。
• 视图的建立和删除只影响视图本身，不影响对应的基本表。（但是更新视图数据，是会影响到基本表的）

联系：

视图(view)是在基本表之上建立的表，它的结构(即所定义的列)和内容(即所有数据行)都来自基本表，它依据基本表存在而存在。一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。

5.视图的作用：

1、当一个查询你需要频频的作为子查询使用时，视图可以简化代码，直接调用而不是每次都去重复写这个东西，有点高级语言中的封装的意思吧。
2、其实视图还有很多其他的用处，比如说你是一个系统的数据库管理员，你需要给他人提供一张表的某两列数据，而不希望他可以看到其他任何数据，这样你就可以给他建一个只有这两列数据的视图，然后把视图公布给他。
常用视图的场合应该就上述两处了，至少我经常在这两种情况下使用视图，其他地方用视图的还真不多。

八、连接查询：

MySQL 的连接查询，通常都是将来自两个或多个表的记录行结合起来，基于这些表之间的 共同字段，进行数据的拼接。首先，要确定一个主表作为结果集，然后将其他表的行有选择 性的连接到选定的主表结果集上。使用较多的连接查询包括：内连接、左连接和右连接。

1.内连接——inner join：

输出匹配相同数据/字段的数据内容

 select 字段 from 表1 inner join 表2 on 表1.字段=表2.字段

 

2.左连接——left join：

输出左表的全部内容和右表的共同的数据内容

SELECT 字段 FROM 表1 LEFT JOIN 表2 ON 表1.字段 = 表2.字段;

3.右连接——right join：

与左连接相反

输出右表的全部内容和左表的共同的数据内容

SELECT 字段 FROM 表1 right JOIN 表2 ON 表1.字段 = 表2.字段;

九、NULL 值：

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

1.null值与空值的区别：

• 空值长度为0，不占空间
• NULL值的长度为null，占用空间

空值使用"=“或者”<>"来处理（！=）
count（）计算时，NULL会忽略，空值会加入计算

查询null值：

 查询不为空的值：

十：union 联级：

 UNION联集：将两个SQL语句的结果合并起来，两个SQL语句所产生的字段需要是同样的数据记录种类。

1.union （合并后去重）：

生成结果的数据记录值将没有重复，且按照字段的顺序进行排序。#合并后去重

mysql> select name from 表名1 union select name from 表名2;

2.union all (合并后不去重)：

mysql> select name from 表名1 union all select name from 表名2;

3. 取非交集值：

（1）联级方法中 count（*）<=1

  (2)左右内连接  将is not null  改为  is  null

（3）子查询 外连接查询 not in （内连接查询）

十一：存储过程：

1.介绍：

数据库存储过程（Stored Procedure）是一种预编译的数据库对象，它包含了一系列用于执行特定任务的SQL语句集合。存储过程不仅包含查询语句，还可以包括流程控制语句（如IF...THEN...ELSE、WHILE、LOOP等）、变量声明、条件判断、循环结构以及其他数据库系统提供的编程元素。存储过程在数据库内部被编译和优化，存放在数据库服务器中，用户不需要每次都发送整个SQL脚本到数据库服务器执行，只需调用存储过程的名称，并传入相应的参数（如果存储过程带有参数的话），就可以执行存储过程内定义的复杂操作。

存储过程是一组为了完成特定功能的SQL语句集合。  两个点：第一 触发器（定时任务） 第二个判断 
存储过程这个功能是从5.0版本才开始支持的，它可以加快数据库的处理速度，增强数据库在实际应用中的灵活性。存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。操作数据库的传统 SQL 语句在执行时需要先编译，然后再去执行，跟存储过程一对比，明显存储过程在执行上速度更快，效率更高

存储过程的主要优点和特性：

1. 代码复用：存储过程可以被多个应用程序或用户反复调用，提高了代码的重用性和模块化程度。
2. 性能优化：由于存储过程在数据库内部执行，减少了网络传输成本，尤其对于大批量数据处理和复杂的业务逻辑，可以显著提高执行效率。
3. 安全性增强：通过权限管理和角色分配，可以控制谁有权执行存储过程，间接实现了对数据的安全保护，无需向用户暴露底层表结构。
4. 事务控制：存储过程可以包含事务处理，确保一组操作要么全部成功，要么全部回滚，保证数据完整性。
5. 参数化：存储过程可以定义输入参数、输出参数和输入输出参数，使其更加灵活，能够适应不同的应用场景。
6. 减少网络流量：只需发送存储过程名称和参数，降低了网络传输的数据量。
7. 模块化与封装：存储过程可以封装复杂的业务逻辑，便于维护和升级。

存储过程在许多大型数据库系统中都有广泛应用，如Oracle、SQL Server、MySQL、PostgreSQL等。开发者可以根据需求编写适合特定场景的存储过程，以提高数据库操作的效率和安全性。

语法：

CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
<过程名>：尽量避免与内置的函数或字段重名
<过程体>：语句
[ IN | OUT | INOUT ] <参数名><类型>

2. 操作：

2.1创建存储过程：

 
mysql> delimiter $$
mysql> create procedure ccgc()
    -> begin
    -> create table gg(id int,name varchar(20),score int(20));
    -> insert into gg values(1,'g',90);
    -> insert into gg values(2,'gg',80);
    -> select * from ccgc;
    -> end $$
 
mysql> delimiter ;

2.2 调用存储过程：

mysql> call cc();

2.3 查看存储过程：

mysql> show create procedure cc\G

2.4 查看指定存储过程信息：

#查看存储过程
SHOW PROCEDURE STATUS

2.5 删除存储过程：

DROP PROCEDURE IF EXISTS Proc;

3. 存储过程的参数：

IN	输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT	输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT	输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量） 即表示调用者向过程传入值，又表示过程向调用者传出值（只能是变量)