MySQL高级sql语句

select name,score from class where id in (select name from class where score >80);
主语句：select name,score from class where id
子语句(集合)： select id from class where score >80
PS:子语句中的sql语句是为了，最后过滤出一个结果集，用于主语句的判断条件
in：将主表和子表关联/连接的语法

注:class为自己创建的数据库

语法：

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

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

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

1.1查询分数大于80的记录

准备环境：

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

1.2将test3里的记录全部删除，重新插入class表的记录

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

mysql> insert into test3 select * from class where id in (select id from class);

1.3将meixi的分数改为20

update class set score=20 where id in (select id from test3 where id=4);

注：记得不能拿同一个表做，一个作为查询一个用来修改

1.4删除分数大于80的记录

DELETE 也适用于子查询

 delete from test3 where id in (select id from class where score > 80);

先查询大于80的记录

发现查询到的记录已经删除

1.5删除分数不是大于等于80的记录

在 IN 前面还可以添加 NOT，其作用与IN相反，表示否定（即不在子查询的结果集里面）

delete from t1 where id not in (select id where score>=80);

1.6查询如果存在分数等于80的记录则计算class的字段数

select count(*) from class where exists(select id from info where score=80);

查询如果存在分数小于10的记录则计算class的字段数，如果class表没有小于10的，会返回0

select count(*) from class where exists(select id from class where score<10);

2.子查询，别名as

2.1查询class表id,name 字段

select id,name from class;
以上命令可以查看到class表的内容

select id,name from class;

2.2从class表中的id和name字段的内容做为"内容" 输出id的部分

select c.id from (select id,name from class) c;

二、MySQL视图

数据库中的虚拟表，这张虚拟表中不包含真实数据，只是做了真实数据的映射
视图可以理解为镜花水月/倒影，动态保存结果集（数据）

功能：
简化查询结果集、灵活查询、可以针对不同用户呈现不同结果集、相对有更高的安全性
本质而言视图是一种select(结果集的呈现)

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

区别：
①、视图是已经编译好的sql语句。而表不是

②、视图没有实际的物理记录。而表有。
show table status\G

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

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

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

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

1.满足80分的学生展示在视图中

1.1首先建立视图

mysql> create view v_score as select * from class where score>=80;

1.2查看表状态

show table status\G

1.3查看视图

mysql> select * from v_score;

1.4查看视图与源表结构

mysql> desc v_score;

1.5多表创建视图

创建pdd数据表

创建pdd表

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

 create view v_class(id,name,score,age) as select class.id,class.name,class.score,pdd.age from class,pdd where class.name=pdd.name;

查询可见只有相等的会出现

1.6修改原表数据

修改james分数为60分

update class set score='60' where name='james';

1.62同时可以通过视图修改原表

update v_score set score='120' where name='curry';

可以发现视图变化了

再去查看class表发现原图也修改了

修改表不能修改以函数、复合函数方式计算出来的字段
查询方便、安全性
查询方便：索引速度快、同时可以多表查询更为迅速（视图不保存真实数据，视图本质类似select）
安全性：我们实现登陆的账户是root ——》所拥有权限，视图无法显示完整的约束

三、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会忽略，空值会加入计算

插入一条长度记录，分数字段输入null，显示出来就是null

添加一个字段默认是NULL

统计card的行数 null值不计入统计

添加个数据再次统计发现有一行数据

四、连接查询

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

较常用的连接查询包括：内连接、左连接和右连接

准备环境：

创建两个数据表

插入表数据

1、内连接

MySQL 中的内连接就是两张或多张表中同时符合某种条件的数据记录的组合。通常在 FROM 子句中使用关键字 INNER JOIN 来连接多张表，并使用 ON 子句设置连接条件，内连接是系统默认的表连接，所以在 FROM 子句后可以省略 INNER 关键字，只使用关键字 JOIN。同时有多个表时，也可以连续使用 INNER JOIN 来实现多表的内连接，不过为了更好的性能，建议最好不要超过三个表
语法

mysql> select a.a_id,a_name from test1 a inner join test2 b on a.a_name=b.b_name;

通过inner join 的方式将两张表指定的相同字段的记录行输出出来

2.左连接

左连接也可以被称为左外连接，在 FROM 子句中使用 LEFT JOIN 或者 LEFT OUTER JOIN 关键字来表示。左连接以左侧表为基础表，接收左表的所有行，并用这些行与右侧参考表中的记录进行匹配，也就是说匹配左表中的所有行以及右表中符合条件的行。

语法：

mysql> select * from test1 a left join test2 b on a.a_name=b.b_name;

左表显示的test1的所有内容右表显示test2查询结果

左连接中左表的记录将会全部表示出来，而右表只会显示符合搜索条件的记录，右表记录不足的地方为NULL

3.右连接

右连接也被称为右外连接，在FROM 子句中使用 RIGHT JOIN 或者 RIGHT OUTER JOIN 关键字来表示。右连接跟左连接正好相反，它是以右表为基础表，用于接收右表中的所有行，并用这些记录与左表中的行进行匹配

语法

mysql> select * from test1 a right join test2 b on a.a_name=b.b_name;

右表显示test2全部内容左表显示test1符合条件的内容不知的地方显示NULL补足

五、存储过程

存储过程是一组为了完成特定功能的SQL语句集合。

存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。存储过程在执行上比传统SQL速度更快、执行效率更高。

5.1存储过程的优点

执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率
SQL语句加上控制语句的集合，灵活性高
在服务器端存储，客户端调用时，降低网络负载
可多次重复被调用，可随时修改，不影响客户端调用
可完成所有的数据库操作，也可控制数据库的信息访问权限

5.2创建存储过程

语法：

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

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

存储过程的主体都分,被称为过程体

以BEGIN开始，以END结束，若只有一条sQL语句，则可以省略BEGIN-END

以DELIMITER开始和结束

5.3查看存储过程

SHOW CREATE PROCEDURE [数据库.]存储过程名;        #查看某个存储过程的具体信息
 
 
SHOW PROCEDURE STATUS [LIKE '%Proc%'] \G          #查看存储过程状态

5.4.存储过程的参数

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

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

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

create procedure abc (in iname varchar(30)) 行参

name=iname 别名

call class（'xuzhou') 实参

5.5.修改存储过程

 
alter procedure <过程名> [<特征>……]

alter procedure bb modifies sql data sql security invoker;
 
modifies sql data：表名子程序包含写程序的语句
 
security：安全等级
 
invoker：当定义为 invoker 时，只要执行者有执行权，就可以成功执行