msql知识点整理

一、SQL简介

Structured Query Language结构化查询语言
作用：是一种定义、操作、管理关系数据库的句法。
结构化查询语言的工业标准由ANSI(美国国家标准学会，ISO的成员之一)维护。
各大数据库厂商都支持ANSI的SQL标准，但也都对SQL进行了扩展。
SQL：标准和扩展的东西（方言）

二、SQL语句的组成

*DQL：数据查询语言
*DML：数据操作语言
*DDL：数据定义语言
DCL：数据控制语言
TPL：事务处理语言
CCL：指针控制语言

DDL：数据定义语言

用于描述数据库中要存储的现实世界实体的语言。即创建数据库和表的结构。
常用关键字：CREATE ALTER DROP TRUNCATE

DML：数据操作语言

作用：用于向数据库表中插入、删除、修改数据
常用关键字：INSERT UPDATE DELETE
特别注意：MySQL中字符串或日期要包含在单引号中。
空值：NULL

DQL：数据查询语言

DQL:Data Query Language
作用：查询数据，返回结果集。
常用语句：SELECT

DCL：数据控制语言

是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL

TPL：事务处理语言

它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION，COMMIT和ROLLBACK。

CCL：指针控制语言

它的语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。

三、数据的约束

关系型数据库通过约束机制可以保证数据的完整性

数据库的完整性有：
- 实体完整性
- 域完整性
- 参照完整性

在mysql中常用的约束有：
- 主键约束：PRIMARY KEY
- 默认约束：DEFAULT
- 唯一约束：UNIQUE
- 外键约束：FOREIGN KEY
- 非空约束：NOT NULL

这里主键约束属于实体完整性，FOREIGN KEY属于参照完整性，其他属性域完整性。

主键约束

规定表的一行（即每一条记录）在表中是唯一的实体。通过定义主键约束来实现。

    主键：PRIMARY KEY        特点：不能为null，且唯一。    逻辑主键：比如ID，不代表实际的业务意义，只是用来唯一标识一条记录。（推荐）    业务主键：比如username作为主键。    另外：AUTO_INCREMENT  可以设置自增主键。

默认约束

即给某个列设置默认值，如果在插入的是时候该列的值为空，那么就会使用该默认值。

唯一约束

很明显，这里是规定一张表中指定的一列的值不能重复，

外键约束

参考其他表的主键的值作为该列的值。
一个表可以有多个外键，每个外键必须REFERENCES(参考)另一个表的主键，被外键约束的列，取值必须在它参考的列中有对应值。

非空约束

插入值的时候该列不能为空。

四、分组查询

GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。

#语法SELECT column_nameFROM table_nameWHERE column_name operator valueGROUP BY column_name

五、限定结果集

限定结果集即分页查询，在mysql中使用limit

#limit index,size:第一个index是开始的下表，第二个size是要查询的记录条数select * from tablename limit index,size;

数据排序

order by子句：当查询语句中含有where和limit的时候，order by子句需要放在where子句之后，limit子句之前。默认按照升序对记录进行排序。如果要使用降序可以在列名后面添加DESC，升序的ASC#语法SELECT column_nameFROM table_nameWHERE column_name operator valueORDER BY column_name

另外：可以使用多个列排序，则是先排序满足第一个条件的，然后再满足第二个条件的
例如：

六、子查询

子查询是指一个查询被包含在另一个查询之中，急将一个SELECT放在另一个SQL语句当中。子查询可以返回一个值、一行数据、多行数据的某一列，或多行数据的多列。
子查询有：

• 标量子查询；
• 列子查询；
• 行子查询；
• 表子查询；

七、表连接 (面试重点)

表连接查询有：
- 交叉连接
- 内连接
- 外连接

交叉连接(CROSS JOIN)

返回的是两张表记录的笛卡尔积。
例如：

SELECT * FROM customers,orders;#隐式查询#或者SELECT * FROM customers CROSS JOIN orders;

内连接（INNER JOIN）

两边表同时符合条件的组合
内连接如果没有指定连接条件的话，和笛卡尔积的交叉连接结果一样，但是不同于笛卡尔积的地方是，没有笛卡尔积那么复杂要先生成行数乘积的数据表，内连接的效率要高于笛卡尔积的交叉连接。

但是通常情况下，使用INNER JOIN需要指定连接条件。
例如：

SELECT * FROM customers c,orders o WHERE c.id=o.customer_id;#或者SELECT * FROM customers c INNER JOIN orders o ON c.id=o.customer_id;

外连接（OUTER JOIN）

返回的结果不仅包含符合连接条件的行，而且包括左表(左外连接时), 右表(右连接时)或者两边连接(全外连接时)的所有数据行。

左外链接（LEFT JOIN）

返回符合链接条件的记录，同时返回左表中不满足链接条件的剩余记录，右表没有对应的条目显示NULL

右外连接（RIGHT JOIN）

与左外连接正好相反，返回符合链接条件的记录，同时返回右表中不满足链接条件的剩余记录，左表没有对应的条目显示NULL

全外连接FULL JOIN

显示符合条件的数据行，同时显示左右不符合条件的数据行，相应的左右两边显示NULL，即显示左连接、右连接和内连接的并集

八、函数

MySQL数据库提供了很多函数包括：
- 数学函数；
- 字符串函数；
- 日期和时间函数；
- 条件判断函数；
- 系统信息函数；
- 加密函数；
- 格式化函数；
函数详解参考：mysql函数详解

九、索引

索引是对数据表中一列或多列的值进行排序的一种结构，使用索引可以提高数据库中特定数据的查询速度。
含义：索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。

特点：

1. 通过创建唯一索引，可以保证数据库中每一行数据的唯一性；
2. 可以大大加快数据的查询速度，这也是创建索引的主要原因；
3. 在实现数据的参考完整性方面，可以加速表和表之间的连接；
4. 在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。

缺点：

1. 创建和维护索引需要耗费时间，并且随着数据数据量的增加耗时将增加；
2. 索引需要占用次磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有较大的索引，索引文件可能比数据文件更快达到最大文件尺寸；
3. 当对表中的数据增删改的时候，索引也要动态的维护，这样降低了数据的维护速度。

十、存储过程

存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它

存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式。

存储过程的优点：

1、增强SQL语言的功能和灵活性：存储过程可以用控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

2、标准组件式编程：存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。

3、较快的执行速度：如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。

4、减少网络流量：针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织进存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大减少网络流量并降低了网络负载。

5、作为一种安全机制来充分利用：通过对执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

十一、触发器

mysql中的触发器与存储过程一样，都是嵌入到没有MySQL中的一个程序。触发器是由事件来触发某个操作，这些事件包括INSET、UPDATE和DELETE语句。
触发器（tigger）是个特殊的存储过程，不同的是触发器是有事件触发，而存储过程是手动使用CALL来调用

十二、视图

视图是从一个或多个表中导出来的表，是一种虚拟存在的表。
作用：

• 使操作简单化，可以对经常使用的查询定义一个视图，使用户不必为同样的查询操作指定条件
• 增加数据的安全性，通过视图，用户只能查询和修改指定的数据。
• 提高表的逻辑独立性，视图可以屏蔽原有表结构变化带来的影响。

十三、事务

一个事务是一个连续的一组数据库操作，就好像它是一个单一的工作单元进行。换言之，永远不会是完整的事务，除非该组内的每个单独的操作是成功的。如果在事务的任何操作失败，则整个事务将失败。
事务的特性： 事务有以下四个标准属性的缩写ACID，通常被称为：
- 原子性: 确保工作单元内的所有操作都成功完成，否则事务将被中止在故障点，和以前的操作将回滚到以前的状态。
- 一致性: 确保数据库正确地改变状态后，成功提交的事务。
- 隔离性: 使事务操作彼此独立的和透明的。
- 持久性: 确保提交的事务的结果或效果的系统出现故障的情况下仍然存在。

十四、权限管理

MySQL服务器通过权限表来控制用户对数据库的访问，权限表放在MySQL数据库中。有MySQL_install_db脚本初始化。存储账户权限信息表主要有：user、db、host、table_priv、columns_priv和procs_priv.