1.sql分类：（1）DDL：数据库定义语言，用于定义表，列，索引等数据库对象. create,drop,alter等（2）DML：数据库操纵语言，增删改查数据库的记录insert，delete，update，select等（3）DCL：数据库控制语言，定义了数据库的表，字段，用户的访问权限和安全级别 grant，revoke等2.DDL语句（1）创建数据库:create database test1;（2）删除数据库：drop database test1；（3）创建表：create table user(name varchar(10),age int(2));（4）删除表：drop table user;（5）修改表：        alter table user modify name varchar(20);        alter table user add column sex varchar(10);        alter table user drop column age;        Alter table user change age age1 int(4);        Alter table user rename user1;3.DML语句（1）插入记录insert into user(name,age)values(‘zhangsan’,18);（2）删除记录：delete from user where name=’张三’（3）更新记录：update user set age=1 where name=’ 张三’（4）查询记录：select * from user;（5）查询不重复的记录j：select distinct name from user;（6）排序查询：select from user order by age desc;（7）限制排序：select * from user order by age limit 1,30;（8）聚合：select name,age from user group by age;（9）聚合后条件：select name,age from user group by age having age > 18;（having和where的区别：having是聚合后条件，where是聚合前条件）（10）表连接： ①内连接：Select ename,deptname from emp,dept where emp.deptno=dept.deptno;②外连接：左连接：（查询 emp 中所有用户名和所在部门名称：）select ename,deptname from emp left join dept on emp.deptno=dept.deptno;右连接：select ename,deptname from emp left join dept on emp.deptno=dept.deptno;· 左连接：包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录· 右连接：包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录（11）子查询：需要的条件是另一个select的结果（子查询的关键字主要包括in, not in,=,!=,exists,not exists ）（如果查询结果数唯一，可用=代替in）select * from emp where deptno in(select deptno from dept);select * from emp where deptno = (select deptno from dept limit 1);（12）记录联合：select * from t1 union select * from t2select * from t1 union all select * from t2（union和union all的区别：union是将union all查询出来的结果进行了一次distinct，去除重复后的结果）4.DCL语句（1）授权：创建一个数据库用户 z1，具有对 sakila 数据库中所有表的 SELECT/INSERT 权限：grant select,insert on sakila.* to 'z1'@'localhost' identified by '123';（2）收回权限：由于权限变更，需要将 z1 的权限变更，收回 INSERT，只能对数据进行 SELECT 操作：revoke insert on sakila.* from 'z1'@'localhost';

一、环境准备数据库版本--SQL SERVER 2008R2数据库存放路径：原路径：C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA迁移后路径：D:\SQLSERVER.LDF文件：数据库日志文件.MDF文件：数据库文件登录账户:windwos身份验证：windwos本地账户，具有管理员权限SQL SERVER身份验证：sa账户，具有管理员权限二、设计思路登录SQL SERVER----先备份数据库-----分离需要迁移的数据库-----进入数据库的源路径C盘---------------将数据库两个文件.ldf与.mdf剪切至其他磁盘中----再登录SQL SERVER----附加数据库ps：登录数据库请使用windows身份验证，防止附加数据库后数据库变为只读权限三、操作流程1.登录SQL SERVER，使用SSMS---widows身份验证，本地账户登录2.右键点击一个数据库-----任务------备份数据库。避免突发情况，以防万一！3. 再次右键点击一个数据库-----任务------分离数据库勾选“删除链接”-----勾选“更新统计信息”4.分离后，进入源路径C盘，将两个文件（.ldf与.mdf）剪切至D盘5.再次登录SQL SERVER,继续使用windwos身份验证（因为用SA用户去附加数据库的时候，会让数据库成为只读状态，所以能用windwos身份验证就用本地账户，因为权限的问题）6.右键“数据库”--“附加”“添加”--选择D盘刚才剪切过去的数据库.mdf文件--确定7.完成后刷新就可以查看数据库文件位置变了，且权限，文件任何都完整的迁移过来8.重复操作就可以把所有数据库迁移至其他盘四、如果出现数据库为只读状态因为迁移的时候用的SA用户，所以数据库文件的权限变了，因此变为只读状态，所以刚才一再提醒用windwos本地用户操作!当然也有解决办法：方案一：碰到这中情况一般是使用的sa账户登录的，只要改为Windows身份验证，再附加数据库即可搞定。方案二：使用sa登录SQL Server2008附加数据库，附加之后数据库为只读的，然后点数据库-->“属性”-->“选项”-->“状态”，发现“数据库为只读”这一项为True，改为false。当然，大部分情况下不会成功。会继续报错下图。这种情况下，找到你的数据文件和日志文件，右击“属性”-->“安全”，添加一个“Network Service”账户，然后赋予该账户完全控制的权限即可。再次使用sa登录，附加数据库或者再次将“只读”状态修改即可。

MySQL 默认开启事务自动提交模式，即除非显式的开启事务（BEGIN 或 START TRANSACTION），否则每条 SOL 语句都会被当做一个单独的事务自动执行。但有些情况下，我们需要关闭事务自动提交来保证数据的一致性。下面主要介绍如何设置事务自动提交模式。在 MySQL 中，可以通过  SHOW VARIABLES 语句查看当前事务自动提交模式，如下所示：mysql> SHOW VARIABLES LIKE 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit    | ON    | +---------------+-------+ 1 row in set, 1 warning (0.04 sec)结果显示，autocommit 的值是 ON，表示系统开启自动提交模式。在 MySQL 中，可以使用 SET autocommit 语句设置事务的自动提交模式，语法格式如下：SET autocommit = 0|1|ON|OFF;对取值的说明：值为 0 和值为 OFF：关闭事务自动提交。如果关闭自动提交，用户将会一直处于某个事务中，只有提交或回滚后才会结束当前事务，重新开始一个新事务。值为 1 和值为 ON：开启事务自动提交。如果开启自动提交，则每执行一条 SQL 语句，事务都会提交一次。示例下面我们关闭事务自动提交，模拟银行转账。使用 SET autocommit 语句关闭事务自动提交，且张三转给李四 500 元，SQL 语句和运行结果如下：mysql> SET autocommit = 0;                                                      ; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM mybank.bank; +--------------+--------------+ | customerName | currentMoney | +--------------+--------------+ | 张三         |      1000.00 | | 李四         |         1.00 | +--------------+--------------+ 2 rows in set (0.00 sec) mysql> UPDATE bank SET currentMoney = currentMoney-500 WHERE customerName='张三' ; Query OK, 1 row affected (0.02 sec) Rows matched: 1  Changed: 1  Warnings: 0 mysql> UPDATE bank SET currentMoney = currentMoney+500 WHERE customerName='李四'; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0这时重新打开一个 cmd 窗口，查看 bank 数据表中张三和李四的余额，SQL 语句和运行结果如下所示：mysql> SELECT * FROM mybank.bank; +--------------+--------------+ | customerName | currentMoney | +--------------+--------------+ | 张三         |      1000.00 | | 李四         |         1.00 | +--------------+--------------+ 2 rows in set (0.00 sec)结果显示，张三和李四的余额是事务执行前的数据。下面在之前的窗口中使用 COMMIT 语句提交事务，并查询 bank 数据表的数据，如下所示：mysql> COMMIT; Query OK, 0 rows affected (0.07 sec) mysql> SELECT * FROM mybank.bank; +--------------+--------------+ | customerName | currentMoney | +--------------+--------------+ | 张三         |       500.00 | | 李四         |       501.00 | +--------------+--------------+ 2 rows in set (0.00 sec)结果显示，bank 数据表的数据更新成功。在本例中，关闭自动提交后，该位置会作为一个事务起点，直到执行 COMMIT 语句和 ROLLBACK 语句后，该事务才结束。结束之后，这就是下一个事务的起点。关闭自动提交功能后，只用当执行 COMMIT 命令后，MySQL 才将数据表中的资料提交到数据库中。如果执行 ROLLBACK 命令，数据将会被回滚。如果不提交事务，而终止 MySQL 会话，数据库将会自动执行回滚操作。使用 BEGIN 或 START TRANSACTION 开启一个事务之后，自动提交将保持禁用状态，直到使用 COMMIT 或 ROLLBACK 结束事务。之后，自动提交模式会恢复到之前的状态，即如果 BEGIN 前 autocommit = 1，则完成本次事务后 autocommit 还是 1。如果 BEGIN 前 autocommit = 0，则完成本次事务后 autocommit 还是 0。

日志是所有应用的重要数据，MySQL 也有错误日志、查询日志、慢查询日志、事务日志等。当笔记的查看：二进制日志 binlog二进制日志 binlog 用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。使用任何存储引擎的 mysql 数据库都会记录 binlog 日志。在 binlog 中记录的是逻辑日志，也就是 SQL 语句。SQL 语句执行后，binlog 追加到日志文件中。可以设置 binlog 文件大小，超过大小后，自动创建新的文件。binlog 有三种格式，分别为 STATMENT、ROW 和 MIXED。STATMENT：把会修改数据的 sql 语句记录到 binlog 中；是 MySQL 5.7.7 之前的默认格式；ROW：不记录每条 sql 语句的上下文信息，仅记录哪条数据被修改了；是 MySQL 5.7.7之后的默认格式；MIXED：基于 STATMENT 和 ROW 两种模式的混合复制，一般使用 STATEMENT 模式，对于无法复制的操作使用 ROW 模式；在实际应用中，binlog 主要用于主从复制和数据恢复。主从复制是指在 master 机器开启 binlog，通过某种方式把 binlog 发送给 slave 机器，slave 机器根据 binlog 内容进行数据操作，从而保证主从数据一致性。另外，通过使用 mysqlbinlog 工具可以从 binlog 恢复数据。在 MySQL 5.7 之后，内置默认引擎已经变更为 InnoDB 引擎。 InnoDB 引擎在处理事务时，可以设置日志写入磁盘的时机，默认情况下是每次 commit 时写入磁盘。也可以通过 sync_binlog 参数设置成系统自动判断或每 N 个事务写入一次。查询日志查询日志记录了所有数据库请求的信息。无论这些请求是否得到了正确的执行。开启之后对性能有比较大的影响，因此使用不多。慢查询日志慢查询日志用来记录执行时间超过某个阈值的语句。执行时间阈值可以通过 long_query_time 来设置，默认是 10 秒。慢查询日志需要手动开启，对性能有一些影响，一般不建议开启。慢查询日志支持将记录写入文件，也支持写入数据库表事务日志 redo log事务的四大特性之一是持久性。因此事务成功后，数据库的修改永久保存，不能因为任何原因而回到原来的状态。redo log 是 InnoDB 引擎层实现的日志，并不是所有引擎都有，用来记录事务对数据页的修改，可以在崩溃时用于恢复数据。redo log 包括内存中的日志缓冲和磁盘上的日志文件。执行 SQL 语句后，先写入日志缓冲，后续再一次性把多条缓冲写入文件。在 InnoDB 中，数据页也会刷盘，redo log 存在的意义主要就是降低对数据页刷盘的要求。数据页的变更，redo log 没有必要全部保存。如果数据页刷盘比 redo log 快，则 redo log 的记录对于数据恢复意义不大；如果数据页刷盘比 redo log 慢，则 redo log 中比数据页快的部分可以用来快速恢复数据。因此 redo log 日志文件大小是固定的，当写到结尾时，会回到开头循环写日志事务日志 undo log事务的四大特性之一是原子性。对数据库的一系列操作，要么全部成功，要么全部失败，不允许部分成功部分失败。因此，需要记录数据的逻辑变化。原子性通过 undo log 来实现，比如事务中执行一条 insert 语句，undo log 就会记录一条 delete 语句；事务中执行一条 update 语句，undo log 就会记录一条相反的 update 语句。这样在事务失败时，就可以通过 undo log 来回滚到事务之前的状态。

SQL Server建表后如何分区？

学习了如何使用 SQL 语句操作数据表，为了让大家更好的理解，我来简单介绍一下如何将 SQL 语句映射为文件操作。1. 查询数据表前面介绍过，在 MySQL 中无论哪种存储引擎的表都会有一个 .frm 文件来保存数据表的结构定义。所以，执行 SHOW TABLES; 语句相当于列出数据库目录中所有 .frm 文件的基本名，所得到的结果是相同的。有些数据库系统使用注册表来记录某数据库里的所有数据表，但 MySQL 没有这样做，因为，MySQL 数据目录的层次结构已经把“注册表”隐藏在其中了。2. 创建数据表创建数据表时，需要执行 CREATE TABLE 语句定义数据表的结构。无论是哪一种存储引擎，MySQL 服务器都将创建一个 .frm 文件来保存数据表的结构定义的内部编码。MySQL 服务器还会根据指定数据表的具体类型创建出其他必要的文件。例如，它将为一个 MyISAM 数据表创建出一个 .MYD 数据文件和一个 .MYI 索引文件；为一个 MERGE 数据表创建出一个 .mgr 数据/索引文件。对于 InnoDB 数据表，InnoDB 处理程序将在 InnoDB 表空间里为数据表初始化一些数据和索引信息。3. 更新数据表当执行 ALTER TABLE 语句时，MySQL 服务器将对相对应数据表的 .frm 文件重新进行编码，来表明数据表的结构性变化，还要对有关的数据文件和索引文件的内容进行相应的修改。CREATE INDEX 和 DROP INDEX 等语句也是对相应数据表的 .frm 文件重新进行编码，因为 MySQL 服务器在内部是把它们当作等效的 ALTER TABLE 语句来处理的。改变 InnoDB 数据表的结构会引起 InnoDB 处理程序修改 InnoDB 表空间中数据表的数据，同时也对索引做出相应的修改。4. 删除数据表DROP TABLE 语句是通过删除该相应数据表的各种有关文件而实现的。对于某些数据表类型，可以通过在相应的数据库目录里删除与数据表有关的各个文件的办法来手动删除这个数据表。例如，假设 mydb 是当前数据库，mytb1 是一个 MyISAM、Archive 或 MERGE 类型的数据表，那么 DROP TABLE mytb1 语句就大致等效于下面这两条命令：cd DATADIR（数据库文件存放路径）rm -f mydb/mytb1.* 或 del mydb/mytb1.*对于 InnoDB 数据表，因为它的某些组成部分在文件系统里没有实体性的文件代表，所以没有等效的文件系统命令。例如，InnoDB 数据表在文件系统里只有一个相应的 .frm 文件，用文件系统级命令删除这个文件后，该数据表在 InnoDB 表空间中对应的数据和索引将没有任何意义。

replace into和insert into on duplicate key 区别replace的用法当不冲突时相当于insert，其余列默认值 当key冲突时，自增列更新，replace冲突列，其余列默认值 Com_replace会加1 Innodb_rows_updated会加1Insert into …on duplicate key的用法不冲突时相当于insert，其余列默认值 当与key冲突时，只update相应字段值。 Com_insert会加1 Innodb_rows_inserted会增加1实验展示表结构create table helei1( id int(10) unsigned NOT NULL AUTO_INCREMENT, name varchar(20) NOT NULL DEFAULT '', age tinyint(3) unsigned NOT NULL default 0, PRIMARY KEY(id), UNIQUE KEY uk_name (name) ) ENGINE=innodb AUTO_INCREMENT=1  DEFAULT CHARSET=utf8;表数据root@127.0.0.1 (helei)> select * from helei1; +----+-----------+-----+ | id | name | age | +----+-----------+-----+ | 1 | 贺磊 | 26 | | 2 | 小明 | 28 | | 3 | 小红 | 26 | +----+-----------+-----+ 3 rows in set (0.00 sec)replace into用法root@127.0.0.1 (helei)> replace into helei1 (name) values('贺磊'); Query OK, 2 rows affected (0.00 sec) root@127.0.0.1 (helei)> select * from helei1; +----+-----------+-----+ | id | name | age | +----+-----------+-----+ | 2 | 小明 | 28 | | 3 | 小红 | 26 | | 4 | 贺磊 | 0 | +----+-----------+-----+ 3 rows in set (0.00 sec) root@127.0.0.1 (helei)> replace into helei1 (name) values('爱璇'); Query OK, 1 row affected (0.00 sec) root@127.0.0.1 (helei)> select * from helei1; +----+-----------+-----+ | id | name | age | +----+-----------+-----+ | 2 | 小明 | 28 | | 3 | 小红 | 26 | | 4 | 贺磊 | 0 | | 5 | 爱璇 | 0 | +----+-----------+-----+ 4 rows in set (0.00 sec)replace的用法当没有key冲突时，replace into 相当于insert，其余列默认值当key冲突时，自增列更新，replace冲突列，其余列默认值Insert into …on duplicate key:root@127.0.0.1 (helei)> select * from helei1; +----+-----------+-----+ | id | name | age | +----+-----------+-----+ | 2 | 小明 | 28 | | 3 | 小红 | 26 | | 4 | 贺磊 | 0 | | 5 | 爱璇 | 0 | +----+-----------+-----+ 4 rows in set (0.00 sec) root@127.0.0.1 (helei)> insert into helei1 (name,age) values('贺磊',0) on duplicate key update age=100; Query OK, 2 rows affected (0.00 sec) root@127.0.0.1 (helei)> select * from helei1; +----+-----------+-----+ | id | name | age | +----+-----------+-----+ | 2 | 小明 | 28 | | 3 | 小红 | 26 | | 4 | 贺磊 | 100 | | 5 | 爱璇 | 0 | +----+-----------+-----+ 4 rows in set (0.00 sec) root@127.0.0.1 (helei)> select * from helei1; +----+-----------+-----+ | id | name | age | +----+-----------+-----+ | 2 | 小明 | 28 | | 3 | 小红 | 26 | | 4 | 贺磊 | 100 | | 5 | 爱璇 | 0 | +----+-----------+-----+ 4 rows in set (0.00 sec) root@127.0.0.1 (helei)> insert into helei1 (name) values('爱璇') on duplicate key update age=120; Query OK, 2 rows affected (0.01 sec) root@127.0.0.1 (helei)> select * from helei1; +----+-----------+-----+ | id | name | age | +----+-----------+-----+ | 2 | 小明 | 28 | | 3 | 小红 | 26 | | 4 | 贺磊 | 100 | | 5 | 爱璇 | 120 | +----+-----------+-----+ 4 rows in set (0.00 sec) root@127.0.0.1 (helei)> insert into helei1 (name) values('不存在') on duplicate key update age=80; Query OK, 1 row affected (0.00 sec) root@127.0.0.1 (helei)> select * from helei1; +----+-----------+-----+ | id | name | age | +----+-----------+-----+ | 2 | 小明 | 28 | | 3 | 小红 | 26 | | 4 | 贺磊 | 100 | | 5 | 爱璇 | 120 | | 8 | 不存在 | 0 | +----+-----------+-----+ 5 rows in set (0.00 sec)replace into这种用法，相当于如果发现冲突键，先做一个delete操作，再做一个insert 操作，未指定的列使用默认值，这种情况会导致自增主键产生变化，如果表中存在外键或者业务逻辑上依赖主键，那么会出现异常。因此建议使用Insert into …on duplicate key。

1.进入安装中心：可以参考硬件和软件要求、可以看到一些说明文档2.选择全新安装模式继续安装3.输入产品秘钥：这里使用演示秘钥进行4.在协议中，点击同意，并点击下一步按钮，继续安装5.进入全局规则检查项，这里可能要花费几秒钟，试具体情况而定6.配置更新项，推荐检查更新7.选择安装更新的具体内容8.安装程序文件9.安装规则检查10.安装功能选择11.实例配置，使用默认即可（如果之前安装过，这里需要特别注意下）12.PolyBase配置选择--默认即可13.服务器配置--默认即可14.引擎配置--建议使用混合模式，并记住密码15.Analysis Service配置--推荐使用使用默认16.Reporting Services配置--推荐使用使用默认17.Distributed Replay控制器配置---推荐使用使用默认（添加当前用户）18.Distributed Replay 客户端配置--推荐使用使用默认19.协议授权，接受协议20.最后安装确认页面，会显示所有的安装配置信息21.等待安装进度--这一步会消耗比较长的时间，在虚拟机里面耗时15分钟左右22.继续等待--较耗时23.服务正常安装完毕希望这个教程对大家有所帮助！！晚安！

找到想修改的数据库 右键 --> 属性  --> 左侧 选项1.Simple 简单恢复模式,Simple模式的旧称叫”Checkpoint with truncate log“，其实这个名字更形象，在Simple模式下，SQL Server会在每次checkpoint或backup之后自动截断log，也就是丢弃所有的inactive log records，仅保留用于实例启动时自动发生的instance recovery所需的少量log，这样做的好处是log文件非常小，不需要DBA去维护、备份log，但坏处也是显而易见的，就是一旦数据库出现异常，需要恢复时，最多只能恢复到上一次的备份，无法恢复到最近可用状态，因为log丢失了。 Simple模式主要用于非critical的业务，比如开发库和测试库，但是道富这边的SQL Server（即使是生产库）大都采用Simple模式，是因为这边的SQL Server大都用于非critical的业务（critical的数据库大都采用Oracle和DB2)，可以忍受少于1天的数据丢失(我们的job每天都会定时备份全库）。如果需要压缩数据库日志（Shrink语句），将数据库模式切换到简单恢复模式后压缩率才是最高的，如果你的数据库在完整恢复模式或大容量日志回复模式下采用日志压缩，压缩后的日志大小并不会很理想。2.Full 完整恢复模式,和Simple模式相反，Full模式的旧称叫”Checkpoint without truncate log“，也就是SQL Server不主动截断log，只有备份log之后，才可以截断log，否则log文件会一直增大，直到撑爆硬盘，因此需要部署一个job定时备份log。Full的好处是可以做point-in-time恢复，最大限度的保证数据不丢失，一般用于critical的业务环境里。缺点就是DBA需要维护log，增加人员成本（其实也就是多了定时备份log这项工作而已）。3.Bulk-logged 大容量日志恢复Bulk-logged模式和full模式类似，唯一的不同是针对以下Bulk操作，会产生尽量少的log： 1) Bulk load operations (bcp and BULK INSERT). 2) SELECT INTO. 3) Create/drop/rebuild index 众所周知，通常bulk操作会产生大量的log，对SQL Server的性能有较大影响，bulk-logged模式的作用就在于降低这种性能影响，并防止log文件过分增长，但是它的问题是无法point-in-time恢复到包含bulk-logged record的这段时间。 Bulk-logged模式的最佳实践方案是在做bulk操作之前切换到bulk-logged，在bulk操作结束之后马上切换回full模式。补存SQL Server 2008 支持三种恢复模式，即简单恢复模式、完整恢复模式和大容量日志恢复模式。1、简单恢复：无日志备份。自动回收日志空间以减少空间需求，实际上不再需要管理事务日志空间。 最新备份之后的更改不受保护。在发生灾难时，这些更改必须重做。 只能恢复到备份的结尾。2、完整恢复：需要日志备份。数据文件丢失或损坏不会导致丢失工作。可以恢复到任意时点（例如应用程序或用户错误之前）。3、大容量日志恢复：需要日志备份。是完整恢复模式的附加模式，允许执行高性能的大容量复制操作。通过使用最小方式记录大多数大容量操作，减少日志空间使用量。如果在最新日志备份后发生日志损坏或执行大容量日志记录操作，则必须重做自该上次备份之后所做的更改。否则不丢失任何工作。可以恢复到任何备份的结尾。不支持时点恢复。其中，如果符合下列任一要求，则使用完整恢复模式：    您必须能够恢复所有数据。    数据库包含多个文件组，并且您希望逐段还原读/写辅助文件组（以及可选地还原只读文件组）。        •您必须能够恢复到故障点。        • 您希望可以还原单个页。        • 您愿意承担事务日志备份的管理开销。因此还原单个数据页是完整恢复模式功能。

索引是数据库优化中最常用也是最重要的手段之一，通过索引可以帮助用户解决大多数的 SQL 性能问题。多数情况下，查询速度很慢时，加上索引便能解决问题。但也并非总是如此，因为优化不是件简单的事情。但是如果你不使用索引，在许多情况下，尝试通过其它途径来提高性能都纯粹是在浪费时间。应该首先使用索引来最大程度的改善性能，然后再看看是否还有其它有用的技术。索引提供了高效访问数据的方法，能够快速的定位表中的某条记录，加快数据库查询的速度，从而提高数据库的性能。如果查询时不使用索引，那么查询语句将查询表中的所有字段。这样查询的速度会很慢。使用索引进行查询，查询语句不必读完表中的所有记录，而只查询索引字段。这样可以减少查询的记录数，达到提高查询速度的目的。下面通过对比使用索引和不使用索引来分析索引对查询速度的影响。例 1为了便于读者更好的理解，分析之前，我们先查询一下 tb_students_info 数据表中的记录，SQL 语句和运行结果如下：mysql> SELECT * FROM tb_students_info; +----+------+ | id | name | +----+------+ |  1 | 张三 | |  2 | 李四 | |  3 | 王五 | |  4 | 赵六 | |  5 | 周七 | |  6 | 吴八 | |  7 | 朱九 | |  8 | 苏十 | +----+------+ 8 rows in set (0.02 sec)使用 EXPLAIN 分析未使用索引时的查询情况，SQL 语句和运行结果如下：mysql> EXPLAIN SELECT * FROM tb_students_info WHERE name='张三' \G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: tb_students_info    partitions: NULL          type: ALL possible_keys: NULL           key: NULL       key_len: NULL           ref: NULL          rows: 8      filtered: 12.50         Extra: Using where 1 row in set, 1 warning (0.00 sec)由结果可以看到，rows 列的值是 8，说明查询语句扫描了表中的 8 条记录。没有索引的表就相当于一组无序的行，如果我们想找到某条记录就必须检查表的每一行，看看它是否与那个期望值相匹配。这是一个全表扫描操作，其效率很低，如果表很大，而且仅有少数几条记录与搜索条件相匹配，那么整个扫描过程的效率将会超级低。在 tb_students_info 表的 name 字段添加索引，SQL 语句和运行结果如下：mysql> CREATE INDEX index_name ON tb_students_info(name); Query OK, 8 rows affected (0.14 sec)使用 EXPLAIN 再次执行上面的查询语句，SQL 语句和运行结果如下：mysql> EXPLAIN SELECT * FROM tb_students_info WHERE name='张三' \G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: tb_students_info    partitions: NULL          type: ref possible_keys: index_name           key: index_name       key_len: 63           ref: const          rows: 1      filtered: 100.00         Extra: NULL 1 row in set, 1 warning (0.00 sec)结果显示，rows 列的值为 1，表示这个查询语句只扫描了表中的 1 条记录。创建索引后访问的行由 8 行减少到 1 行，其查询速度自然比扫描 8 条记录快。而且 possible_keys 和 key 的值都是 index_name，这说明查询时使用了 index_name 索引。所以，在查询操作中，使用索引不仅能自动优化查询效率，还会降低服务器的开销。注意：由于 tb_students_info 表中记录较少，所以在这没有分析运行时间。表中记录多时，运行时间的差异也会体现出索引对查询速度的影响。

顾名思义，临时表就是临时用来存储数据的表，是建立在系统临时文件夹中的表，如果使用得当，完全可以像普通表一样进行各种操作。我们常使用临时表来存储中间结果集。如果需要执行一个很耗资源的查询或需要多次操作大表时，可以把中间结果或小的子集放到一个临时表里，再对这些表进行查询，以此来提高查询效率。临时表主要适用于需要临时保存数据的一些场景。一般情况下，临时表通常是在应用程序中动态创建或者由 MySQL 内部根据需要自己创建。临时表可以分为内部临时表和外部临时表。外部临时表外部临时表也可称为会话临时表，这种临时表只对当前用户可见，它的数据和表结构都存储在内存中。当前会话中断或结束后，数据表数据就会丢失，MySQL 会自动删除表并释放其所占空间。1）创建临时表创建临时表很容易，在 CREATE TABLE 语句上添加 TEMPORARY 关键字，如下所示：CREATE TEMPORARY TABLE <表名>...临时表的命名可以和非临时表同名，但是同名后非临时表将对当前会话不可见，直到临时表被删除。2）查询临时表创建了临时表之后，运行 SHOW TABLES 命令不会列出临时表，以及在 INFORMATION_SCHEMA 数据库中也不存在临时表的信息，这不是 Bug，而是设计就是如此。我们可以使用以下命令来查看临时表：SHOW CREATE TABLE <表名>; 3）删除临时表当然，我们也可以在当前会话中手动销毁临时表，SQL 语句如下：DROP TABLE <表名>;例 1下面我们创建 tmp_table 表，并对它进行操作。mysql> CREATE TEMPORARY TABLE tmp_table (     -> id INT NOT NULL,     -> name VARCHAR(10) NOT NULL); Query OK, 0 rows affected (0.03 sec) mysql> SHOW TABLES; +-------------------+ | Tables_in_test    | +-------------------+ | product           | | product_price     | | score             | | student           | | student_comment   | | tb_student        | | tb_student_course | | tb_students_info  | | tb_students_score | | tb_usertest       | +-------------------+ 10 rows in set (0.01 sec) mysql> SHOW CREATE TABLE tmp_table \G *************************** 1. row ***************************        Table: tmp_table Create Table: CREATE TEMPORARY TABLE `tmp_table` (   `id` int(11) NOT NULL,   `name` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 1 row in set (0.00 sec) mysql> DROP TABLE tmp_table; Query OK, 0 rows affected (0.01 sec) mysql> SHOW CREATE TABLE tmp_table \G ERROR 1146 (42S02): Table 'test.tmp_table' doesn't exist如果你在执行 DROP TABLE 命令之前退出当前 MySQL 会话，再使用 SHOW CREATE TABLE 命令来读取 tmp_table 表，会发现数据库中没有该表的存在，因为在退出会话时该临时表已经被销毁了。外部临时表也有一些限制，使用时需注意以下几点：所用数据库账号需要有建立和使用临时表的权限在同一条 SQL 语句中，不能关联2次相同的临时表不能用 RENAME 来重命名一个临时表，可以用 ALTER TABLE 来代替。内部临时表内部临时表是一种特殊轻量级的临时表，不同于手工创建的临时表，它是被 MySQL 自动创建的。在 SQL 的执行过程中可能会用到临时表来存储某些操作的中间结果，该过程由 MySQL 自动完成，用户无法手工干预，且这种内部表对用户来说是不可见的。我们可以通过 EXPLAIN 或者 SHOW STATUS 查看 MySQL 是否使用了内部临时表帮助完成某个操作。内部临时表在 SQL 语句的优化过程中扮演着非常重要的角色，MySQL 中的很多操作都要依赖于内部临时表来进行优化。但是使用内部临时表需要创建表以及中间数据的存取代价，所以用户在写 SQL 语句的时候应该尽量的去避免使用临时表。以下是生成内部临时表的几种可能：使用 ORDER BY/GROUP BY 的列并非全来自于表连接的第一个表Distinct 和 ORDER BY 联合使用多表连接需要保存中间结果集内部临时表有两种类型，一种是 HEAP 临时表，这种临时表的所有数据都会存在内存中，对于这种表的操作不需要 IO 操作；另一种是 OnDisk 临时表，顾名思义，这种临时表会将数据存储在磁盘上。OnDisk 临时表用来处理中间结果比较大的操作。以下情况可能阻碍 MySQL 使用 HEAP 临时表，改用 OnDisk 临时表：数据表中含有 BLOB 类型或 TEXT 类型字段列；在 GROUP BY 或 DISTINCT 任何条件中含有超过 512 字节的列；如果使用了 UNION 或 UNION ALL，而且 SELECT 列中含有任何超过 512 字节的列；如果 HEAP 临时表的大小大于 MAX_HEAP_TABLE_SIZE 系统变量的值，HEAP 临时表将会被自动转换成 OnDisk 临时表。MySQL 5.7 版本中，OnDisk 临时表可以通过 INTERNAL_TMP_DISK_STORAGE_ENGINE 系统变量选择使用 MyISAM 引擎或者 InnoDB 引擎。Created_tmp_tables 和 Created_tmp_disk_tables 变量用来记录所用临时表的数目。

一，判断文件是否存在    存储过程sys.xp_fileexist 用于判断文件是否存在，参数是文件（file）的路径或目录的路径：exec master.sys.xp_fileexist 'D:\test.txt'该存储过程返回的结果集有一行数据，三个字段，如下图：    二，创建子目录存储过程 sys.xp_create_subdir 用于创建子目录，参数是子目录的路径：exec master.sys.xp_create_subdir 'D:\test'执行存储过程，系统返回消息：Command(s) completed successfully，说明子目录创建成功。三，查看子目录结构存储过程sys.xp_dirtree 用于显示当前目录的子目录，该存储过程有三个参数：directory：第一个参数是要查询的目录；depth ：第二个参数是要显示的子目录的深度，默认值是0，表示显示所有的子目录；file ：第三个参数是bool类型，指定是否显示子目录中的文件（file），默认值是0，表示不显示任何文件，只显示子目录（directory）；exec master.sys.xp_dirtree 'D:\data'该存储过程返回的字段有子目录名称和相对深度，返回的结果中并没有显示子目录的父子关系：四，删除文件存储过程 sys.xp_delete_file 用于删除文件，该存储过程有5个参数：第一个参数是文件类型（File Type），有效值是0和1，0是指备份文件，1是指报表文件；第二个参数是目录路径（Folder Path）， 目录中的文件会被删除，目录路径必须以“\”结尾；第三个参数是文件的扩展名（File Extension），常用的扩展名是'BAK' 或'TRN'；第四个参数是Date，早于该日期创建的文件将会被删除；第五个参数是子目录（Subfolder），bool类型，0是指忽略子目录，1是指将会删除子目录中的文件；该存储过程并不可以删除所有的文件，系统限制它只能删除特定类型的文件。declare @Date datetime = dateadd(day,-30,getdate()) exec master.sys.xp_delete_file 0,'D:\test\','bak',@Date,0五，查看磁盘驱动的空闲空间存储过程 sys.xp_fixeddrives用于查看磁盘驱动器剩余（free）的空间exec sys.xp_fixeddrives六，执行DOS命令操作文件存储过程sys.xp_cmdshell 用于执行DOS命令，该功能对应SQL Server系统的xp_cmdshell高级选项，默认情况下，该选项是禁用的，执行该存储过程，系统会抛出错误消息：SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off  as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure.  For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.因此，在执行该存储过程之前，必须启用xp_cmdshell选项，由于启用该选项有潜在的风险，建议用户在执行代码之后，禁用该选项。1，启用/禁用xp_cmdshell选项xp_cmdshell选项属于系统的高级选项，执行以下代码，允许用户修改高级选项：-- To allow advanced options to be changed.  exec sp_configure 'show advanced options', 1;  go  -- To update the currently configured value for advanced options.  reconfigure;  go 使用以下代码启用xp_cmdshell选项：-- To enable the feature.  exec sp_configure 'xp_cmdshell', 1;  go  -- To update the currently configured value for this feature.  reconfigure;  go使用以下代码禁用xp_cmdshell选项：-- To disable the feature.  exec sp_configure 'xp_cmdshell', 0;  go  -- To update the currently configured value for this feature.  reconfigure;  go2，常用的DOS命令该存储过程使得用户可以通过TSQL命令执行DOS命令，exec sys.xp_cmdshell 'command_string'2.1 建立新文件或增加文件内容格式：ECHO 文件内容＞file_name exec master.dbo.xp_cmdshell 'echo abc > D:\share\test.txt'2.2 查看文件内容格式：TYPE file_nameexec master.dbo.xp_cmdshell 'type D:\share\test.txt'2.3 复制文件格式： COPY  file_name  new_folderexec master.dbo.xp_cmdshell 'copy D:\test\test.txt D:\share\'2.4 显示目录格式：DIR folderexec master.dbo.xp_cmdshell 'dir D:\share\'2.5 创建目录格式：MD folder_nameexec master.dbo.xp_cmdshell 'md D:\share\test\'2.6 删除目录格式：RD folderexec master.dbo.xp_cmdshell 'rd D:\share\test'2.7 删除文件格式：DEL file_nameexec master.dbo.xp_cmdshell 'del D:\share\test.txt'2.8 重命名文件格式：REN [盘符：][路径]〈旧文件名〉〈新文件名〉exec master.dbo.xp_cmdshell 'ren D:\test\test.txt new.txt'2.9 移动文件格式：MOVE  file_name new_folderexec master.dbo.xp_cmdshell 'move D:\test\new.txt D:\share\'2.10 切换目录格式：CD[盘符：][路径名][子目录名]

Transact-SQLTransact-SQL（又称 T-SQL），是在 Microsoft SQL Server 和 Sybase SQL Server 上的 ANSI SQL 实现，与 Oracle 的 PL/SQL 性质相近（不只是实现 ANSI SQL，也为自身数据库系统的特性提供实现支持），在 Microsoft SQL Server 和 Sybase Adaptive Server 中仍然被使用为核心的查询语言。数据库1、创建数据库USE master ;  GO  CREATE DATABASE Sales  ON  ( NAME = Sales_dat,   FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\saledat.mdf',   SIZE = 10,   MAXSIZE = 50,   FILEGROWTH = 5 )  LOG ON  ( NAME = Sales_log,   FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\salelog.ldf',   SIZE = 5MB,   MAXSIZE = 25MB,   FILEGROWTH = 5MB ) ;  GO2、查看数据库SELECT name, database_id, create_date  FROM sys.databases ;3、删除数据库DROP DATABASE Sales;表1、创建表CREATE TABLE PurchaseOrderDetail  (   ID uniqueidentifier NOT NULL   ,LineNumber smallint NOT NULL   ,ProductID int NULL   ,UnitPrice money NULL   ,OrderQty smallint NULL   ,ReceivedQty float NULL   ,RejectedQty float NULL   ,DueDate datetime NULL  );2、删除表DROP TABLE dbo.PurchaseOrderDetail;3、重命名表    EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';约束1、主键--在现有表中创建主键 ALTER TABLE Production.TransactionHistoryArchive  ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID); --在新表中创建主键 CREATE TABLE Production.TransactionHistoryArchive1  (   TransactionID int IDENTITY (1,1) NOT NULL   , CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)  ) ; --查看主键  SELECT name  FROM sys.key_constraints  WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = N'TransactionHistoryArchive';  GO  --删除主键 ALTER TABLE Production.TransactionHistoryArchive  DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID;  GO视图1、创建视图CREATE VIEW V_EmployeeHireDate  AS  SELECT p.FirstName, p.LastName, e.HireDate  FROM HumanResources.Employee AS e JOIN Person.Person AS p  ON e.BusinessEntityID = p.BusinessEntityID ;  GO 2、删除视图DROP VIEW V_EmployeeHireDate;存储过程1、创建存储过程CREATE PROCEDURE P_UspGetEmployeesTest   @LastName nvarchar(50),   @FirstName nvarchar(50)  AS   SELECT FirstName, LastName, Department   FROM HumanResources.vEmployeeDepartmentHistory   WHERE FirstName = @FirstName AND LastName = @LastName   AND EndDate IS NULL;  GO2、删除存储过程DROP PROCEDURE P_UspGetEmployeesTest;3、执行存储过程EXEC P_UspGetEmployeesTest N'Ackerman', N'Pilar';  -- Or  EXEC P_UspGetEmployeesTest @LastName = N'Ackerman', @FirstName = N'Pilar';  GO  -- Or  EXECUTE P_UspGetEmployeesTest @FirstName = N'Pilar', @LastName = N'Ackerman';  GO4、重命名存储过程EXEC sp_rename 'P_UspGetAllEmployeesTest', 'P_UspEveryEmployeeTest2';5、带有输出参数的存储过程CREATE PROCEDURE P_UspGetEmployeeSalesYTD  @SalesPerson nvarchar(50),  @SalesYTD money OUTPUT  AS   SELECT @SalesYTD = SalesYTD   FROM SalesPerson AS sp   JOIN vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID   WHERE LastName = @SalesPerson;  RETURN  GO --调用 DECLARE @SalesYTDBySalesPerson money;  EXECUTE P_UspGetEmployeeSalesYTD   N'Blythe',   @SalesYTD = @SalesYTDBySalesPerson OUTPUT;  GO数据类型

与其它数据库不同，MySQL 服务器可以在不同的 SQL 模式下运行，并且可以针对不同的客户端以不同的方式应用这些模式，具体取决于 sql_mode 系统变量的值。 SQL 模式定义了 MySQL 数据库所支持的 SQL 语法和数据校验（数据验证检查），这样可以更容易的在不同环境下使用 MySQL。 在 MySQL 中，SQL 模式常用来解决下面几类问题：通过设置 SQL Mode，可以完成不同严格程度的数据校验，有效地保障了数据的准确性。通过设置 SQL Mode 为 ANSI 模式，可以保证大多数 SQL 符合标准的 SQL 语法，使不同数据库之间进行迁移时，不需要进行较大的修改。在不同数据库之间进行数据迁移之前，设置 SQL Mode 可以使 MySQL 中的数据更方便地迁移到目标数据库中。sql_mode 系统变量的常用值下面列出了几种 SQL 模式常用的值。1) TRICT_ ALL_TABLES 和 STRICT_ TRANS_TABLES如果将 sql_mode 的值设置为 TRICT_ALL_TABLES 和 STRICT_TRANS_TABLES，那么 MySQL将启用“严格”模式。在严格模式下，MySQL 服务器会更加严格地对待接收到的不合格数据，它不会把这些不合格的数据转换为最为接近的有效值，而是会拒绝接收它们。简单来说 MySQL 的严格模式就是 MySQL 自身对数据进行的严格校验，例如格式、长度和类型等。2) TRADITIONAL类似于严格模式，但是对于插入的不合格值会给出错误而不是警告。可以应用在事务表和非事务表，用于事务表时，只要出现错误就会立即回滚。如果你使用的是非事务存储引擎，建议不要把 SQL Mode 值设置为 TRADITIONAL，因为出现错误前进行的操作不会回滚，这样会导致操作只进行了一部分。3) ANSI_QUOTESMySQL 服务器会把双引号识别为一个标识符引用字符，而不是字符串的引号字符。所以在启用 ANSI_QUOTES 时，不能用双引号来引用字符串。4) PIPES_ AS_ CONCAT会让 MySQL 服务器把||当成一个标准的 SQL 字符串连接运算符，而不会把它当成是 OR 运算符的同义词。在 Oracle 等数据库中，||被视为字符串的连接操作符，所以在其它数据库中含有||操作符的 SQL 在 MySQL 中将无法执行，为了解决这个问题，MySQL 提供了这个值。5) ANSI会同时启用 ANSI_QUOTES、PIPES_ AS_CONCAT 和其它的几个模式值，使 MySQL 服务器的行为比它的默认运行状态更接近于标准 SQL。如何设置 sql_mode在设置 SQL 模式时，需要指定一个由单个模式值或多个模式值（多个模式值用逗号分隔）构成的值，或者指定一个空字符串，用以清除该值。模式值不区分大小写。 如果想在启动服务器时设置 SQL 模式，那么可以在 mysqld 命令行，或者在某个选项文件里设置系统变量 sql_mode。可以使用下面语句：sql_mode= "TRADITIONAL "sql_mode= "ANSI_ QUOTES, PIPES_ AS_ CONCAT"如果只是想在运行时更改 SQL 模式，那么可以使用 SET 语句来设置 sql_mode 系统变量。SET sql_mode = ' TRADITIONAL' ; 如果想设置全局性的 SQL 模式，则需要加上 GLOBAL 关键字：SET GLOBAL sql_mode = ' TRADITIONAL';设置全局变量需要具备 SUPER 管理权限。新设置的全局变量值将成为此后连入客户端的默认 SQL 模式。 如果想获取当前会话或全局的 SQL 模式值，则可以使用如下语句:SELECT @@SESSION.sql_mode;SELECT @@GLOBAL. sql_mode;其返回值由当前启用的所有模式构成，两个模式之间以逗号隔开。如果当前没有启用任何模式，则返回一个空值。

一、简介从 5.0 版本才开始支持，是一组为了完成特定功能的SQL语句集合（封装），比传统SQL速度更快、执行效率更高。存储过程的优点1、执行一次后，会将生成的二进制代码驻留缓冲区（便于下次执行），提高执行效率2、SQL语句加上控制语句的集合，灵活性高3、在服务器端存储，客户端调用时，降低网络负载4、可多次重复被调用，可随时修改，不影响客户端调用5、 可完成所有的数据库操作，也可控制数据库的信息访问权限为什么要用存储过程？1.减轻网络负载；2.增加安全性二、创建存储过程2.1 创建基本过程使用create procedure语句创建存储过程存储过程的主体部分，被称为过程体；以begin开始，以end$$结束#声明语句结束符，可以自定义: delimiter $$ #声明存储过程 create procedure 存储过程名（in 参数名 参数类型） begin #定义变量 declare 变量名 变量类型 #变量赋值 set 变量名 = 值  sql 语句1；  sql 语句2；  ... end$$ #恢复为原来的语句结束符 delimiter ；（有空格）调用完存储过程后，发现in参数不会对全局变量的值引起变化，而out和inout参数调用完存储过程后，会对全局变量的值产生变化，会将存储过程引用后的值赋值给全局变量。in参数赋值类型可以是变量还有定值，而out和inout参数赋值类型必须为变量。