连接查询

连接的种类

1. 笛卡尔积（交叉连接）：两个表中所有的行都发生连接

2. 等值连接（非等值连接）：多表联合查询时，添加where 等值连接条件
3. 自身连接：一个表通过某连接条件和本身进行连接的方式

多表链接的方式

自然连接、交叉连接（笛卡尔积）、内连接、外连接，其中内、外连接的语法：

多表连接查询语法(重点)
SELECT 字段列表FROM 表1  INNER|LEFT|RIGHT JOIN  表2
ON 表1.字段 = 表2.字段

高级查询（完整格式）

SELECT ……聚合函数（分组函数）……     FROM tb_name[JOIN 表名][ON 连接条件] [WHERE 条件判断][GROUP BY {col_name | postion} [ASC | DESC], ...] [HAVING WHERE 条件判断][ORDER BY {col_name|expr|postion} [ASC | DESC], ...][ LIMIT {[offset,]rowcount | row_count OFFSET offset}];

执行顺序：

from--where-group by -having-select-order by

（根据多列分组时，group by 子句中各列之间用逗号分隔）

子查询中的空值问题

子查询的结果中有一条空值，这条空值导致主查询没有记录返回。
这是因为所有的条件和空值比较结果都是空值。因此无论什么时候只要空值有可能成为子查询结果集合中的一部分，就不能使用NOT IN 运算符

视图相关

基础、定义

视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，并可以将其当作表来使用。

基础语法：

create view 视图名 as select语句(任意形式的查询)；

此外，视图根据数据的来源，可以分为单表视图和多表视图

创建视图时不允许出现重复的列名

查询视图（结构、创建语句）

desc 视图名；
show create view 视图名;

使用和修改视图

我们可以认为：创建视图，就是给一条select语句起别名，或者说是封装select语句。

视图本身不可修改，但是视图的来源（select）语句是可以修改的。因此，修改视图，就是修改视图的来源（select）语句。

完整语法：

CREATER	[OR REPLACE] [ALGORITHM = {UNDEFINED|MERGE|TEMPTABLE}]
VIEW view_mame[(column_list)]
AS select_statement
[WITH [CASCADED|LOCAL] CHECK OPTION]

)
删除视图：

drop view 视图名;

table包含真实的数据，而view说到底就是封装的select语句，并不包含真实的数据。虽然删除视图并不会影响数据，但在实际工作中，建议还是不要乱删别人建立的视图，因为视图封装的select语句很有可能包含复杂的业务逻辑。

*视图的意义

视图可以节省 SQL 语句，将一条复杂的查询语句用视图来进行封装，以后可以直接对视图进行操作；
数据安全，视图操作主要是针对查询的，如果对视图结构进行处理，例如删除，并不会影响基表的数据；
视图往往在大型项目中使用，而且是多系统使用，可以对外提供有用的数据，但是隐藏关键（或无用）的数据；
视图是对外提供友好型的，不同的视图提供不同的数据，就如专门对外设计的一样；
视图可以更好（或者说，容易）的进行权限控制。

视图数据操作

1. 多表视图不能进行新增数据；
2. 可以向单表视图新增数据，但是视图中包含的字段必须有基表中所有不能为空的字段；
3. 对视图数据修改的实质是对基表的修改；
4. 多表视图不能删除数据，单表视图可以；
5. 理论上，无论是多表视图还是单表视图，都可以进行数据的更新；（因为更新限制with check option 的存在，更新视图并不总是成功的。如果创建视图时，设置了某个字段的限制，那么视图进行更新操作时，系统就会进行验证，要保证更新之后数据依然能够被查出来，否则不能更新）
6. 更新视图之外的数据不报错，但不会成功；

索引

定义

1、索引是表的目录，在查找内容之前可以先在目录中查找索引位置，以此快速定位查询数据。对于索引，会保存在额外的文件中。

2、索引，是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。

索引选取类型

1、越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。

2、简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。

3、尽量避免NULL：应该指定列为NOT nuLL,在MySQL中， 含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂

不适用的场景

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

第二，对于那些只有很少数据值的列也不应该增加索引。因为本来结果集合就是相当于全表查询了，所以没有必要。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比 例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因 此，当修改性能远远大于检索性能时，不应该创建索引。

第五，不会出现在where条件中的字段不该建立索引。

使用场景

1、表的主键、外键必须有索引；外键是唯一的，而且经常会用来查询

2、数据量超过300的表应该有索引；

3、经常与其他表进行连接的表，在连接字段上应该建立索引；经常连接查询，需要有索引

4、经常出现在Where子句中的字段，加快判断速度，特别是大表的字段，应该建立索引，建立索引，一般用在select ……where f1 and f2 ，我们在f1或者f2上建立索引是没用的。只有两个使用联合索引才能有用

5、经常用到排序的列上，因为索引已经排序。

6、经常用在范围内搜索的列上创建索引，因为索引已经排序了，其指定的范围是连续的

优缺点

优点

索引由数据库中一列或多列组合而成，其作用是提高对表中数据的查询速度
索引的优点是可以提高检索数据的速度

缺点

索引的缺点是创建和维护索引需要耗费时间

索引可以提高查询速度，会减慢写入速度

索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

索引分类

1，普通索引：
仅加速查询 最基本的索引，没有任何限制，是我们大多数情况下使用到的索引。

CREATE INDEX  index_name  on user_info(name)  ;

2，唯一索引：
与普通索引类型，不同的是：加速查询 + 列值唯一（可以有null）

CREATE UNIQUE INDEX  mail  on user_info(name)  ;

3，全文索引：
全文索引（FULLTEXT）仅可以适用于MyISAM引擎的数据表；作用于CHAR、VARCHAR、TEXT数据类型的列。

4，组合索引：
将几个列作为一条索引进行检索，使用最左匹配原则。

创建索引：

create table healerjean (
id   bigint(20)  NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT  '主键',
name  VARCHAR(32) NOT NULL COMMENT '姓名',
email VARCHAR(64) NOT NULL COMMENT  '邮箱',
message text DEFAULT  NULL COMMENT '个人信息',INDEX index_name (name) COMMENT '索引name'
) COMMENT  = '索引测试表';

在存在的表上创建索引

create index index_name on healerjean(name)

删除索引

drop index_name on healerjean;
alter TABLE users drop index name_index ;

查看索引

show index from healerjean;

用户和权限管理

权限表

权限工作原理

账户管理

创建新用户：

CREATE USER username@ip IDENTIFIED BY 'password';//MySQL8.0以后只能这样创建用户
RENAME USER old_user TO new_user;//修改用户名

用户权限管理

GRANT priv_type ON database.table
TO user 
[WITH GRANT OPTION];//授予权限REVOKE ALL PRIVILEGES, GRANT OPTION FROM username@ip;//收回权限DROP USER username@ip;//删除用户

修改密码
ALTER USER username@ip IDENTIFIED BY ‘password’;//mysql8.0可用
SET PASSWORD [FOR username@ip] = PASSWORD(‘password’);//不可用
UPDATE mysql.user SET authentication_string = PASSWORD(‘password’) WHERE user = ‘username’;//未测，理论可行

MySQL创建用户时会指定一个host，默认是localhost，那么这个用户只能本机访问，其他机器用这个账户没有访问权限，host改为%，表示允许所有机器访问。

分区管理

当表中数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。
表进行分区后，逻辑上仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间（物理文件），这样查询数据时，不至于每次都扫描整张表。

表分区的优点

表分区可以存储更多的数据，与单个磁盘或文件系统分区相比。

分区表的数据更容易维护，如: 想批量删除大量数据可以使用清除整个分区的方式。另外，还可以对一个独立分区进行优化、检查、修复等操作。可以备份和恢复独立的分区，这在非常大的数据集的场景下效果非常好。

分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。

优化查询，在where字句中包含分区列时，可以只使用必要的分区来提高查询效率，同时在涉及sum ()和count ()这类聚合函数的查询时，可以在每个分区上面并行处理，最终只需要汇总所有分区得到的结果。

性能的提升，在扫描操作中，如果MySQL的优化器知道哪个分区中才包含特定查询中需要的数据，它就能直接去扫描那些分区的数据，而不用浪费很多时间扫描不需要的地方了。

MySQL分区技术可以让DBA对数据的管理能力提升。通过优良的MySQL分区，DBA可以简化特定数据操作的执行方式。

分区的类型

操作实例：

CREATE TABLE tr(
id int,
name varchar(50),
purchased date
)
PARTITION BY RANGE(year(purchaesd))
(
partition p0 values less than(1970),
partition p1 values less than(1980),
partition p2 values less than(1990),
partition p3 values less than(2000),
)

删除分区：

ALTER TABLE tr DROP PRATITION p3; 

增加分区：

ALTER TABLE tr ADD PARTITION (PARTITION p3 VALUES LESS THAN(2000));

对于通过RANGE分区的表，只可以使用ADD PARTITION添加新的分区到分区列表的高端。设法通过这种方式在现有分区的前面或之间增加一个新的分区，将会导致错误。
添加新的LIST分区时，新的分区不能包括现有分区值列表中的任意值。
修改分区：

ALTER TABLE tr REORGANIZE PARTITION p0 INTO(
partition s0 values less than (1960),
partition s1 values less than (1970)
);ALTER TABLE tr REORGANIZE PARTITION s0, s1 INTO(
partition p0 values less than (1970)
);ALTER TABLE tr REORGANIZE PARTITION p0, p1, p2, p3 INTO(
partition m0 values less than (1980),
partition m1 values less than (2000)
);

注意：当使用“ALTER TABLE … REORGANIZE PARTITION” 来对已经按照RANGE和LIST分区表进行重新分区时，需要注意：

1.用来确定新分区模式的PARTITION子句使用与用在CREATE TABLE中确定分区模式的PARTITION子句相同的规则

ues less than (1970)
);

ALTER TABLE tr REORGANIZE PARTITION p0, p1, p2, p3 INTO(
partition m0 values less than (1980),
partition m1 values less than (2000)
);

注意：当使用“ALTER TABLE … REORGANIZE PARTITION” 来对已经按照RANGE和LIST分区表进行重新分区时，需要注意：

1.用来确定新分区模式的PARTITION子句使用与用在CREATE TABLE中确定分区模式的PARTITION子句相同的规则

2.新分区模式不能有任何重叠的区间（适用于按照RANGE分区的表）或值集合（适用于按照LIST分区的表）

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！