数据库进阶

数据库进阶

目录

• 1、MySQL数据库操作
• 2、SQL的select语句完整的执行顺序
• 3、SQL查询处理的步骤序号
• 4、说一下数据库存储过程的优点
• 5、如何对查询命令进行优化
• 6、数据库的优化
• 7、SQL注入是如何产生的，应如何防止
• 8、关系型数据库中，表和表之间有左连接，内连接，外连接，分别解释下他们的含义和区别

1、MySQL数据库操作

1、修改表，修改字段，重命名：

alter table 表名 change 原名 新名 类型及约束；
alter table students change birthday birth datetime not null;

2、修改表，修改字段，不重名：

alter table 表名 modify 列名 类型和约束；
alter table students modify birth date not null;

3、全列插入：

insert into 表名 values(...)
insert into students values(0,"张三",1,"李四","2002-6");

4、部分插入（值的顺序与给出的列顺序对应）：

insert into students(name, birthday) values("王五","2001-8");

5、修改：

update 表名 set 列1=值1，列2=值2 where
update students set gender=0, hometown="北京"， where id=5;

6、备份：

mysqldump -uroot -p 数据库名 > mysqldump.sql

7、恢复：

mysql -uroot -p 数据库名 < mysqldump.sql

2、SQL的select语句完整的执行顺序

1、from 子句组装来自不同数据源的数据

2、where 子句基于指定的条件对记录行进行筛选

3、group by 子句将数据划分为多个分组

4、使用聚集函数进行计算

5、使用 having 子句筛选分组

6、计算所有的表达式

7、select 的字段

8、使用 order by 对结果集进行排序

SQL 语言不同于其他编程语言的最明显特征是处理代码的顺序。在大多数据库语言中，代码按编码顺序被处理。但在 SQL 语句中，第一个被处理的子句式 from，而不是第一出现的 select。

3、SQL查询处理的步骤序号

1、FROM <left_table>

2、<join_type> JOIN <right_table>

3、ON <join_condition>

4、WHERE <where_condition>

5、GROUP BY <group_by_list>

6、WITH {CUBE | ROLLUP}

7、HAVING <having_condition>

8、SELECT

9、DISTINCT

10、ORDER BY <order_by_list>

11、<TOP_specification> <select_list>

以上每个步骤都会产生一个虚拟表，该虚拟表被用作下一个步骤的输入。这些虚拟表对调用者（客户端应用程序或者外部查询）不可用。只有最后一步生成的表才会给调用者。如果没有在查询中指定某一个子句，将跳过相应的步骤。

4、说一下数据库存储过程的优点

储存过程是一个可编程的函数，它在数据库中创建并保存。它可以有 SQL 语句和一些特殊的控制结构组成。

当希望在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。

存储过程通常有以下优点：

1、存储过程能实现较快的执行速度

2、存储过程允许标准组件是编程

3、存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算

4、存储过程可被作为一种安全机制来充分利用

5、存储过程能够减少网络流量

5、如何对查询命令进行优化

1、应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索

2、应尽量避免在 where 子句中对字段进行 null 值判断，避免使用 != 或 <> 操作符，避免使用 or 连接条件，或在 where 子句中使用参数、对字段进行表达式或函数操作，否则会导致全表扫描

3、不要在 where 子句中的 “=” 左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引

4、使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用

5、很多时候可考虑用 exists 代替 in

6、尽量使用数字型字段

7、尽可能的使用 varchar/nvarchar 代替 char/nchar

8、任何地方都不要使用 select from t，用具体的字段列表代替“”，不要返回用不到的任何字段

9、尽量使用表变量来代替临时表

10、避免频繁创建和删除临时表，以减少系统表资源的消耗

11、尽量避免使用游标，因为游标的效率较差

12、在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON，在结束时设置 SET NOCOUNT OFF

13、尽量避免大事务操作，提高系统并发能力

14、尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理

6、数据库的优化

1、优化索引、SQL 语句、分析慢查询

2、设计表的时候严格根据数据库的设计范式来设计数据库

3、使用缓存，把经常访问到的数据而且不需要经常变化的数据放在缓存中，能节约磁盘 I/O

4、优化硬件，采用 SSD，使用磁盘队列技术（RAID0，RAID1，RDID5）等

5、采用 MySQL 内部自带的表分区技术，把数据分层不同的文件，能够提高磁盘的读取效率

6、垂直分表，把一些不经常读的数据放在一张表里，节约磁盘 I/O

7、主从分离读写，采用主从复制把数据库的读操作和写入操作分离开来

8、分库分表分机器（数据量特别大），主要的原理就是数据路由

9、选择合适的表引擎，参数上的优化

10、进行架构级别的缓存，静态化和分布式

11、不采用全文索引

12、采用更快的存储方式，例如 NoSQL 存储经常访问的数据**

7、SQL注入是如何产生的，应如何防止

程序开发过程中不注意规范书写 SQL 语句和对特殊字符进行过滤，导致客户端可以通过全局变量 POST 和 GET 提交一些 SQL 语句正常执行，产生 SQL 注入

防止办法：

1、过滤掉一些常见的数据库操作关键字，或者通过系统函数来进行过滤

2、在 PHP 配置文件中将 Register_globals=off; 设置为关闭状态

3、SQL 语句书写的时候尽量不要省略小引号（tab 键上面那个）和单引号

4、提高数据库命名技巧，对于一些重要的字段根据程序的特点命名，取不易被猜到的

5、对于常用的方法加以封装，避免直接暴漏 SQL 语句

6、开启 PHP 安全模式 Safe_mode=on;

7、打开 magic_quotes_gpc 来防止 SQL 注入

8、控制错误信息，关闭错误提示信息，将错误信息写到系统日志

9、使用 mysqli 或 pdo 预处理

8、关系型数据库中，表和表之间有左连接，内连接，外连接，分别解释下他们的含义和区别

内连接查询：查询的结果为两个表匹配到的数据

左连接查询：查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用 null 填充

右连接查询：查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用 null 填充