mysql高级sql

一，查询

1.按关键字排序

• 用 ORDER BY 语句来实现排序：
  • ORDER BY 语句用于对查询结果进行排序。可以根据一个或多个字段的值进行升序（ASC）或降序（DESC）排序。
• 排序可针对一个或多个字段：
  • 可以指定一个字段或者多个字段进行排序。如果是多个字段，查询结果会首先按第一个字段排序，在第一个字段相同时再按第二个字段排序，依此类推。
• ASC：升序，默认排序方式：
  • ASC 是升序排序，从小到大排序。如果不指定排序方式，默认使用升序排序。
• DESC：降序：
  • DESC 是降序排序，从大到小排序。需要在 ORDER BY 语句后明确指定。
• ORDER BY** 的语法结构**：

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

1.1按关键字排序操作

创建一个表

CREATE TABLE student_courses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    score DECIMAL(4, 2),
    address varchar(20),
    hobbyid INT
);

INSERT INTO student_courses (id, name, score, hobbyid) VALUES
(1, 'Li Ming', 85.5,'beijing', 1),
(2, 'Wang Xiao', 92.0,'shengzheng', 2),
(3, 'Zhang San', 75.0,'shanghai', 1),
(4, 'Li Hua', 91.0,'huangshan' 3),
(5, 'Zhao Lei', 87.0,'nanjing', 2);
(6,'hanmeimei',10,'nanjing',3);
(7,'lilei',11,'nanjing',5);

(1)按分数排序查询（不加asc默认为升序）

select id,name,score from student_courses order by score;

(2)按分数降序查询（DESC）

select id,name,score from student_courses order by score desc;

(3)使用where进行条件查询

过滤出南京并且按降序查询

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

(4)使用ORDER BY语句对多个字段排序

ORDER BY 语句可以使用多个字段来进行排序，当排序的第一个字段相同的记录有多条的情况下，这些多条的记录再按照第二个字段进行排序，ORDER BY 后面跟多个字段时，字段之间使用英文逗号隔开，优先级是按先后顺序而定，但是order by 之后的第一个参数只有在出现相同值时，第二个字段才有意义

1.查询学生信息先按兴趣id降序排列，相同分数的，id按降序排列

select id,name,hobbyid from student_courses order by hobbyid desc,ind desc

2.查询学生信息先按兴趣id降序排列,相同分数的,id按升序排列

select id,name,hobbyid from student_courses order by hobbyid desc,id;

1.2使用区间判断查询（and/or 且和或）

(1)and

select * from student_courses where score >80 and score <=90;

(2)or

select * from student_courses where score >80 or score =10;

(3)嵌套多个条件

select * from student_courses where score >70 or (score >75 and score <90 );

(4)distinct 查询不重复记录

select distinct hobbyid from student_courses;

1.3对查询结果进行分组

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

（1）count

统计hobbyid相同的分组，计算相同hobbyid的个数

select count(name),hobbyid from student_courses group by hobbyid;

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

select count(name),hobbyid from student_courses where score >=80 group by hobbyid;

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

SELECT COUNT(name) AS name_count, score, hobbyid 
FROM student_courses 
WHERE score >= 80 
GROUP BY hobbyid,score
ORDER BY name_count;

1.4限制结果条目（limit重要）

limit 限制输出的结果记录

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

(1)查询所有信息只显示前2行记录

select * from student_courses limit 2;

(2)查询所有信息只显示从第4行开始，往后的2行内容

select * from student_courses limit 4,2;

(2)结合order by语句，查询id的大小升序排列显示前2行

select id,name from student_courses order by id desc limit 2;

(3)查询信息只显示最后3行按降序显示出来(重要)

select id,name from student_courses order by id desc limit 3;

(4)查询并显示出第一行开始的后两行

SELECT * FROM student_courses LIMIT 2 OFFSET 1;

1.5设置别名

别名的作用和特点

简化查询语句：当表名或列名较长时，使用别名可以减少代码长度，使查询语句更简洁、易于书写和维护。

增强可读性：通过使用别名，尤其是在涉及多个表的复杂查询中，能够使查询语句更加清晰直观，便于理解。

临时性：别名仅在当前查询中有效，它不会改变数据库中实际的表名或列名。换句话说，别名是查询语句执行过程中的临时名称，用于替代表或列的原始名称。

列的别名：使用AS关键字为列设置别名。别名可以是任何合法的标识符，并且可以用于后续的查询中。

别名使用场景：

1.对复杂的表进行查询的时候，别名可以缩短查询语句的长度

2.多表相连查询的时候（通俗易懂、减短sql语句）

3.可以作为连接语句的操作符，可以将一个表的查询的结果插入新表中

格式

对于列的别名：SELECT column_name AS alias_name FROM table_name;
对于表的别名：SELECT column_name(s) FROM table_name AS alias_name;

(1)统计表内的字段有多少个，以数字形式显示（不加as也可以）

或

select count(*) as 总行数 from student_courses;

(2)将查询出的结果插入新表中

AS作用

1、创建了一个新表t1 并定义表结构，插入表数据（与info表相同）

2、但是”约束“没有被完全”复制“过来 #但是如果原表设置了主键，那么附表的：default字段会默认设置一个0

类似复制克隆

别名注意事项

1.在为表设置别名时，要保证别名不能与数据库中的其他表的名称冲突。

2.列的别名是在结果中有显示的，而表的别名在结果中没有显示，只在执行查询时使用。

create table cheshi01 as select * from student_courses;

或

create table cheshi02 select * from student_courses;

或

create table cheshi03 as select * from student_courses where score >=70;

as创建了一个新表并定义表结构，插入表数据（与student_courses表相同）

但是”约束“没有被完全”复制“过来

如果原表设置了主键，附表的default字段会默认设置一个0

(3)使用别名查询

select name as 姓名,score as 分数,address as 地址 from student_courses;()

或

select x.name as 姓名,x.score as 分数,x.address as 地址 from student_courses as x;

1.6通配符

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

通配符通常都是与LIKE使用的，并协同 WHERE 子句共同来完成查询任务。

常用的通配符(重要)

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

_ 下划线表示单个字符

(1)查询c开头的

select id,name from student_courses where name like 'z%';

(2)查询l开头和i结尾的

select id,name from student_courses where name like 'l___i';

(3)查询中间是g的

select id,name from student_courses where name like '%n%';

(4) 查询以o结尾的

select id,name from student_courses where name like '%o';

(5)查询以h开头其中间有e的

select id,name from student_courses where name like 'h%e%';

1.7子查询（内查询）

• 子查询也叫作内查询或嵌套查询， 是指在一个 SQL 查询中嵌入另一个查询。
• 子查询的执行顺序是：首先执行子查询，然后将其结果提供给外层查询（主查询）使用。
• 子查询的主要作用是为主查询提供过滤条件或中间结果，用于进一步的数据处理。
• 子查询和主查询可以作用于同一个表也可以查询不同的表。
• 子查询可以在 SELECT 、INERT、UPDATE、DELETE语句中使用。在嵌套的时候，子查询内部还可以再次嵌套新的子查询，可以多层嵌套。

格式

select id,name,score from 表名1 where id in (select id from 表名2);

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

SQL 中子查询的使用场景

• 当一个表达式与子查询返回的结果集中的某个值匹配时，会返回 TRUE，否则返回 FALSE。
• 如果使用了 NOT 关键字，则结果会相反：不匹配时返回 TRUE，匹配时返回 FALSE。
• 注意子查询只能返回单列数据，如果查询需求较为复杂，单列结果无法满足时，可以通过多层嵌套的子查询来处理。
• 大多数情况下，子查询通常与 SELECT 语句一起使用，用于从数据库中提取特定的数据。

(1)单表查询

查询表里大于90的

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

(2)多表查询

查询表里大于80的

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

(3)update

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

将表内某某成绩改成50

CREATE TABLE info3 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    score DECIMAL(4,2),
    address VARCHAR(20),
    hobbid INT
);

INSERT INTO info3 (name, score, address, hobbid) VALUES
('Xiao Ming', 72.30, 'Guangzhou', 1),
('Wang Qiang', 88.50, 'Shenzhen', 2),
('San Zhang', 65.75, 'Chengdu', 1),
('Hua Li', 93.20, 'Hangzhou', 3),
('Lei Zhao', 79.80, 'Suzhou', 2),
('Meimei Han', 45.00, 'Wuhan', 3);


UPDATE info3
SET score = 50
WHERE id IN (SELECT id FROM student_courses WHERE id = 1);

(4)delete

删除大于70的

delete from info3 where id in (select id where score > 70);

(5)insert

插入子查询查出来的数据

insert into info1 select * from student_courses where id in (select id from sudent_courses);

(6)NOT

在 IN 前面可以添加 NOT，其作用与IN相反，表示否定（即不在子查询的结果集里面）删除分数没有大于等于70的记录同表删除

delete from info3 where id not in (select id where score >= 70)

(7)EXISTS

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

1.查询如果存在分数大于80的记录则计算表的字段数,存在大于80的就显示没有就不显示

select count(*) from student_courses where exists (select id from student_courses where score > 80);

2.查询如果存在分数小等于90的记录则计算info的字段数，info表没有小于50的，所以返回0

条件为真查询条件为假不显示

select count(*) from info1 where existsz(select id from info1 where score < 10);

(8)别名as

将结果集做为一张表进行查询的时候，需要用到别名

例：需求：从info表中的id和name字段的内容做为"内容" 输出id的部分

select name from (select id,name from info1);
select name from (select id,name from info1) a;

报错的原因:select * from 表名为标准格式，上面的查询语句，“表名"的位置是一个完整结果集，mysql并不能直接识别，而此时给与结果集设置一个别名，以”select a.id from a“的方式查询将此结果集视为一张"表”，就可以正常查询数据了，

二，SQL视图

1.什么是视图？

视图是 MySQL 中的一种虚拟表，它并不直接存储数据，而是基于实际的基础表进行查询的结果集。视图的作用是将查询的结果集作为表一样进行操作，但它并不占用物理空间。视图可以用于简化复杂查询、增强安全性和权限控制。

2.视图的作用

• 简化复杂查询：通过视图，可以将复杂的 SQL 查询封装起来，简化后续的操作。
• 安全性：可以限制用户只访问特定的数据列或行，而无需访问整个表，保护数据安全。
• 灵活性：不同用户或角色可以通过视图看到不同的数据集，而不需要修改基础表。
• 只读操作：视图适合用于浏览和查询操作，不推荐用于增、删、改操作。

3.视图的使用场景

• 不同角色权限管理：通过视图为不同的用户角色（如管理员、普通用户）提供不同的结果集。
• 数据聚合与筛选：可以通过视图将多个表连接后的结果集呈现为单个表。

4.视图与表的区别

• 视图没有实际的物理数据，它只是基础表数据的映射；而表是物理存储的数据结构。
• 视图是已经编译好的 SQL 查询，它是查询结果的动态展示；表则是真实存储数据的对象。
• 视图不占用物理存储空间，而表占用实际的存储空间。
• 视图的创建与删除不影响基础表，但视图的内容来自基础表，更新视图时，基础表的数据也会被更新。
• 视图是逻辑层的抽象，提供了一种灵活的方式查看基础表中的数据。
• 视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。
• 表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。

5.注意事项

• 视图适用于多表查询和复杂 SQL 查询的封装，但不适用于频繁的增、删、改操作。
• 视图对性能的影响：虽然视图可以简化查询，但每次使用视图时，数据库都要重新执行视图的 SQL 查询，可能会影响性能，尤其是视图涉及多表连接时。
• 视图的更新限制：不是所有的视图都可以被更新，只有符合特定条件的视图（如基于单个表）才允许执行 UPDATE、DELETE 操作。

6.视图操作

(1)创建视图单表操作

将大于等于80分的数据展示出来

create view v_score as aelect * from info1 where score >=80;  

查看视图和原表结构，一模一样

修改jiaoshou成绩为50 ，后查看表info1

update info1 set score=50 where='jiaoshou';

视图里面没有了jiaoshou这个名字，因为成绩是50而视图里面是大于等于80的，所以视图里面没jisodhou

满足视图的要求，会在视图更新，不满足不显示

(2)多表视图创建

创建一个视图，需要输出id、学生姓名、分数以及年龄

create view v_info3 (id,name,score,age) as select info3.id,info3.name,info3.score,t02.age from info3,t022 where info3.name=t02.name;

(3)null值

在 SQL 中，NULL 是一个特殊的标记，用于表示数据缺失或未知的值。

当一个字段的值是 NULL 时，表示没有值，而不是一个空字符串或零。

使用 NULL 可以帮助我们处理不完整的数据或未知的数据输入。

在创建表时，限制某些字段不为空 not null

空值长度为0，不占空间，NULL值的长度为null（字符串占空间），占用空间，is null无法判断空值

a.空值（空字符串）的处理

可以使用 =、<> 或 != 运算符来判断或处理空值

null的三种值

(1)插入一条记录，分数字段输入null，显示出来就是null

alter table info3 add addr varchar(60);

(2)把表里的字段修改后符合要求的显示出来

update info3 set name'shengzheng' where score >=80;

update info3 set addr='nanjing' where score <=90;

(3)将表里addr改成南京，只要是分数大于等于90的

update info3 set addr='nanjing' where score >=90;

(4)将表里addr是null的显示出来

select * from info3 where addr is null;

(5)将表里addr不是null的显示出来

select * from info3 where is not null;

三，连接查询（重要）

• 在 MySQL 中，连接查询（JOIN）用于将来自两个或多个表的记录组合在一起，基于这些表之间的共同字段。
• 执行 SQL 连接查询时，通常需要指定一个主表（或基表），并将其他表的记录行有选择性地连接到这个主表的结果集上。主表的选择通常取决于查询的业务逻辑和数据需求。选择主表时，通常会选择那个包含核心数据或关系字段的表。
• 连接查询的主要类型包括内连接（INNER JOIN）、左连接（LEFT JOIN）、和右连接（RIGHT JOIN）

创建一个表

create table test11 (
c_id int(11) default null,
c_name varchar(32) default null,
c_level int(11) default null);

create table test12 (
d_id int(11) default null,
d_name varchar(32) default null,
d_level int(11) default null);

insert into test11 values (1,'aaaa',10);
insert into test11 values (2,'bbbb',20);
insert into test11 values (3,'cccc',30);
insert into test11 values (4,'dddd',40);

insert into test12 values (2,'bbbb',20);
insert into test12 values (3,'cccc',30);
insert into test12 values (5,'eeee',50);
insert into test12 values (6,'ffff',60);

3.1内连接

在 MySQL 中，内连接（INNER JOIN） 是用于组合两个或多个表中符合某个条件的记录的操作。内连接只返回在所有连接的表中匹配的行。INNER JOIN 是连接的默认类型，因此在 FROM 子句中，可以省略 INNER 关键字，直接使用 JOIN 关键字。 不超过三个表为好，超过三个需要优化了

格式

SELECT column_name(s)FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

select * from test11 inner join test12 on test11.c_id = test12.d_id;

3.2左连接

左连接

将两张表的内容进行匹配,按照sql查询的顺序从左至右，输出左表的全部内容和右表的重叠（相同，相等)的数据内容，其他都是nulll

select * from test11 left join on test12 on test11.c_name=test12.d_name;

3.3右连接

右连接

将两张表的内容进行匹配,按照sql查询的顺序从右至左，输出右表的全部内容和左表的重叠（相同，相等)的数据内容，其他都是nulll

select * from test12 right join test11 on test12.d_name=test11.c_name;

四，存储过程

1.什么是存储过程

在数据库应用中，简单的查询或数据操作通常可以通过单条 SQL 语句完成。然而，面对复杂的业务需求时，往往需要多条 SQL 语句联合执行才能实现。而存储过程提供了一种解决方案，它允许将一组相关的 SQL 语句封装为一个预定义的程序，并存储在数据库中，供以后反复调用。类似于编程语言中的函数，存储过程可以简化复杂操作，提高执行效率，减少开发中的重复性工作。通过存储过程，用户可以轻松地实现复杂的逻辑处理，同时减少网络开销和编译时间，提高数据库操作的整体性能。

2.存储过程简介

2.1 存储过程是一组为了完成特定任务而编写的 SQL 语句的集合，它能够将多条 SQL 语句封装在一个逻辑单元内，通过调用存储过程来执行这些语句。存储过程可以包含复杂的操作，如条件判断、循环以及事务处理。

存储过程的两个点

• 触发器（定时任务）：触发器是数据库中的一种机制，它在特定事件（如插入、更新或删除操作）发生时自动执行相关的SQL操作。定时任务则可以按预定的时间间隔自动执行存储过程或其他操作，帮助自动化数据维护和处理。
• 判断：存储过程内部可以包含条件判断语句（如IF、CASE）和循环控制语句，用于根据不同的业务逻辑和条件执行不同的操作，使得存储过程更加灵活和智能。

2.2MySQL 从 5.0 版本开始支持存储过程，这使得数据库能够更灵活地处理复杂的业务逻辑。存储过程经过预编译并存储在数据库服务器中，调用时无需再次编译，这大大提高了执行速度。同时，它减少了客户端和服务器之间的数据传输，从而提高了系统的效率。存储过程尤其适合处理频繁的操作、复杂的业务逻辑和批量数据处理场景。

访问select，假设访问超过了100万就会触发存储过程

存储过程在数据库中 创建并保存，它不仅仅是 SQ语句的集合，还可以加入一些特殊的控制结构，也可以控制数据的访问方式。存储过程的应用范围很广，例如封装特定的功能、 在不同的应用程序或平台上执行相同的函数等等。

2.3存储的优点

• 提高执行效率：存储过程在第一次执行时会被编译成二进制代码并存储在服务器的缓冲区中，后续调用时无需再次编译，提升了执行速度。
• 灵活性高：存储过程将SQL语句与控制语句结合，支持条件判断、循环等逻辑操作，能够灵活应对复杂的业务需求。
• 降低网络负载：存储过程存储在服务器端，客户端只需调用，无需频繁发送复杂的SQL语句，减少了客户端与服务器之间的网络传输量。
• 可重用性强：存储过程可以多次重复调用，且修改存储过程不会影响客户端的调用逻辑，方便维护和升级。
• 安全性与权限控制：存储过程能够执行所有的数据库操作，且可以通过定义权限来控制对敏感数据的访问，增强了数据库的安全性。

语法格式

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

(1)创建存储过程（不加参数）

DELIMITER $$							#将语句的结束符号从分号;临时改为两个$$(可以自定义)
CREATE PROCEDURE Proc()		#创建存储过程，过程名为Proc，不带参数
-> BEGIN								  #过程体以关键字 BEGIN 开始
-> create table mk (id int (10), name char(10),score int (10));
-> insert into mk values (1, 'wang',13);
-> select * from mk;			#过程体语句
-> END $$								  #过程体以关键字 END 结束
DELIMITER ;								#将语句的结束符号恢复为分号

(2)调用存储过程

CALL Proc();

1. 存储过程的主体被称为过程体
   存储过程的主体部分包含所有的SQL语句和控制逻辑，用来执行特定的任务。

2. 以 BEGIN 开始，以 END 结束
   如果存储过程包含多条 SQL 语句，必须用 BEGIN 和 END 来封装这些语句。
   如果存储过程只包含一条 SQL 语句，则可以省略 BEGIN 和 END。

3. 以 DELIMITER 开始和结束
   在 MySQL 中，默认的 SQL 语句结束符是分号（;）。为了定义存储过程（尤其是包含多条 SQL 语句的过程），需要更改结束符，因为存储过程的定义中会使用分号作为 SQL 语句的结尾。

(3)查看存储过程

格式

SHOW CREATE PROCEDURE [数据库.]存储过程名;		#查看某个存储过程的具体信息

show create procedure proc\G

查看

查看存储过程
SHOW PROCEDURE STATUS 

#查看指定存储过程信息
mysql> SHOW PROCEDURE STATUS like '%proc%'\G

(4)存储过程参数

IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）

OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）

INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

表示调用者向过程传入值，又表示过程向调用者传出值（只能是变量)

DELIMITER @@
CREATE PROCEDURE proc_new (IN inname VARCHAR(70))   #行参
    BEGIN 
    SELECT * FROM kkk01 WHERE name = inname; 
    END @@

mysql> DELIMITER ;

call proc ()                                                #实参

(5)修改存储过程

格式

ALTER PROCEDURE <过程名>[<特征>... ]
ALTER PROCEDURE GetRole MODIFIES SQL DATA SQL SECURITY INVOKER;
MODIFIES SQLDATA:表明子程序包含写数据的语句
SECURITY:安全等级
invoker:当定义为INVOKER时，只要执行者有执行权限，就可以成功执行。

(6)删除存储过程

存储过程内容的最好修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储过程。