Mysql高阶语句（二）

一、设置别名（alias ——>as）

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

SELECT column_name AS alias_name FROM table_name;

对于表的别名：

SELECT column_name(s) FROM table_name AS alias_name;

在使用 AS 后，可以用 alias_name 代替 table_name，其中 AS 语句是可选的。AS 之后的别名，主要是为表内的列或者表提供临时的名称，在查询过程中使用，库内实际的表名 或字段名是不会被改变的。
列别名设置举例：

select name as 姓名,score as 成绩 from test;    
也可以直接不加as：
select name 姓名,score 成绩 from test;

如果表的长度比较长，可以使用 AS 给表设置别名，在查询的过程中直接使用别名
举例：临时设置test表的别名为a

select a.name as 姓名,a.score as 成绩 from test as a;

 查询test表的字段数量，以‘字段总number’字样显示

select count(*) as number from test;

 注意：不使用as，也可以以正常使用：

select count(*) 字段总number from test；

as的使用场景：
1、对复杂的表进行查询的时候，别名可以缩短查询语句的长度。
2、多表相连查询的时候（通俗易懂、减短sql语句）。

此外，as还可以作为连接语句的操作符。
举例：创建test_bak表，将test表的查询记录全部插入test_bak表:

create table test_bak as select * from test;

 在此处AS起到的作用：
1、创建了一个新表test_bak 并定义表结构，插入表数据（与test表相同）
2、但是”约束“没有被完全被”复制“过来 ,但是如果原表设置了主键，那么附表的：default字段会默认设置一个0

 相似的有：
克隆、复制表结构：

create table test01_bak (select * from test);

也可以加入where 语句判断只复制socre大于等于60的数据：
方式一：

create table test02_bak as select * from test where score >=60;

 方式二：

create table test03_bak (select * from test where score>=60);

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

二、通配符
通配符主要用于替换字符串中的部分字符，通过部分字符的匹配将相关结果查询出来。
通常通配符都是跟 like一起使用的，并协同 where子句共同来完成查询任务。常用的通配符有两个，分别是：
%：百分号表示零个、一个或多个字符 .*
_：下划线表示单个字符。
举例：
从test表中查询名字是l开头的数据：
 

 select * from test where name like 'l%';

 查询名字里是l和l中间有一个字符的数据：

 select * from test where name like 'l_l_i' ;

 查询名字中间有m的记录：

select * from test where name like '%m%' ;

查询li后面3个字符的名字的数据：

select * from test where name like 'li___' ;

 通配符“%”和“_”不仅可以单独使用，也可以组合使用
查询名字以h开头的记录：

 三、子查询
子查询也被称作内查询或者嵌套查询，是指在一个查询语句里面还嵌套着另一个查询语 句。子查询语句是先于主查询语句被执行的，其结果作为外层的条件返回给主查询进行下一 步的查询过滤。
PS: 子语句可以与主语句所查询的表相同，也可以是不同表
执行顺序：最内层的子查询先执行
举例：
 

select name,score from test where id in (select id from test where score >80);
从“test”的表中选择姓名和分数列，其中id列的值在子查询中被选择，该子查询选择了分数大于80的所有id。因此，此查询将返回所有分数大于80的学生的姓名和分数。
#主语句：select name,score from info where id
子语句(集合)： select id from info where score >80

子语句中的sql语句是为了，最后过滤出一个结果集，用于主语句的判断条件
in: 将主表和子表关联/连接的语法（⭐⭐）

多表查询:
首先创建一个表为test_db作为多表查询使用：

create table test_db(id int);
insert into test_db values(1),(2),(3);
select * from test_db;

 多表查询:
举例：
 

select id,name,score from test where id in (select * from test_db);

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

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

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

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

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

子查询还可以用在 INSERT 语句中。子查询的结果集可以通过 INSERT 语句插入到其 他的表中。
举例：先将test_sb里的记录全部删除，重新插入test表的记录

DELETE FROM test_db; 删除test_ab的数据
insert into test_db select id from test where id in (select id from test);

 

 UPDATE 语句也可以使用子查询。UPDATE 内的子查询，在 set 更新内容时，可以是单独的一列，也可以是多列。
举例：
将test表中id为2的数据的score值更新为60，并将更新后的数据插入到test01_bak表中。

 update test01_bak set score=60 where id in (select id from test where id=2);

 DELETE 也适用于子查询
删除分数大于80的记录：

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

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

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

 EXISTS 这个关键字在子查询时，主要用于判断子查询的结果集是否为空。如果不为空， 则返回 TRUE；反之，则返回 FALSE
查询test01_bak如果存在分数等于80的记录则计算info的字段数：

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

 查询如果存在分数小于10的记录则计算info的字段数，test01_bak表没有小于10的，所以返回0:

select count(*) from test01_bak where exists(select id from test01_bak where score<50);

 子查询，别名as
#查询test01_bak表id,name 字段:

select a.id,a.name from (select id,name from test01_bak) a;
相当于
select test01_bak.id,name from test01_bak;

MySQL视图:
视图：优化操作+安全方案 ⭐⭐
数据库中的虚拟表，这张虚拟表中不包含真实数据，只是做了真实数据的映射
视图可以理解为镜花水月/倒影，动态保存结果集（数据）
基础表info （7行记录） ——》映射（投影）–视图

作用场景[图]:
针对不同的人（权限身份），提供不同结果集的“表”（以表格的形式展示）

作用范围：

select * from test02_bak;			#展示的部分是test02_bak表
select * from view_name;	#展示的一张或多张表

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

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

视图和表的区别和联系:

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

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

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

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

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

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

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

举例：
需求：满足80分的学生展示在视图中。
PS：这个结果会动态变化，同时可以给不同的人群（例如权限范围）展示不同的视图。
创建视图(单表)：
 

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

查看表状态：

show table status\G

查看视图

select * from v_score;

 查看视图与源表结构：

desc v_score;

 desc test02_bak;

多表创建视图：
创建test01表：

create table test01 (id int,name varchar(10),age char(10));
insert into test01 values(1,'zhangsan',20);
insert into test01 values(2,'lisi',30);
insert into test01 values(3,'wangwu',29);

举例：创建一个视图，需要输出id、学生姓名、以及年龄。

create view v_per(id,name,score,age) as select test02_bak.id,test02_bak.name,test02_bak.score,test01.age from test02_bak,test01 where test02_bak.name=test01.name;

修改原表数据：

mysql> update test02_bak set score='60' where name='liuyi';
Query OK, 1 row affected (1.62 sec)
Rows matched: 1  Changed: 1  Warnings: 0

查看视图：

mysql> select * from v_per;
+------+-----------+--------+----------+--------+
| id   | name      | score  | address  | hobbid |
+------+-----------+--------+----------+--------+
|    4 | tianqi    | 100.00 | hangzhou |      5 |
|    5 | jiaoshou  | 100.00 | laowo    |      3 |
|    6 | hanmeimei | 100.00 | nanjing  |      3 |
|    7 | lilei     | 100.00 | nanjing  |      5 |
+------+-----------+--------+----------+--------+
4 rows in set (0.00 sec)

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

mysql> update v_per set score='120' where name='tianqi';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select * from v_per;
+------+-----------+--------+----------+--------+
| id   | name      | score  | address  | hobbid |
+------+-----------+--------+----------+--------+
|    4 | tianqi    | 120.00 | hangzhou |      5 |
|    5 | jiaoshou  | 100.00 | laowo    |      3 |
|    6 | hanmeimei | 100.00 | nanjing  |      3 |
|    7 | lilei     | 100.00 | nanjing  |      5 |
+------+-----------+--------+----------+--------+
4 rows in set (0.00 sec)

mysql> select * from test02_bak;
+------+-----------+--------+------------+--------+
| id   | name      | score  | address    | hobbid |
+------+-----------+--------+------------+--------+
|    1 | liuyi     |  60.00 | beijing    |      2 |
|    2 | wangwu    |  50.00 | shengzheng |      2 |
|    3 | lisi      |  50.00 | shanghai   |      4 |
|    4 | tianqi    | 120.00 | hangzhou   |      5 |
|    5 | jiaoshou  | 100.00 | laowo      |      3 |
|    6 | hanmeimei | 100.00 | nanjing    |      3 |
|    7 | lilei     | 100.00 | nanjing    |      5 |
+------+-----------+--------+------------+--------+
7 rows in set (0.00 sec)

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