MySql菜鸟入门系列

MySql

(一)MySql基础介绍

MySQL学习使用注意事项：

1、必须访问一个已有的MySQL服务器，需要一个服务器账号（一个登录名和一个口令）

2、MySQL运行在所有主要平台上，包括Windows、Linux、Solaris、Mac OSX等

3、一个客户机软件（用来实际运行MySQL命令的程序），最容易得到的是mysql命令行实用程序（包含在每个MySQL安装中）

4、安装MySQL时都会提示重要信息，比如安装位置（默认）、root用户口令、端口、服务或进程名等，都可以使用默认值

5、多个MySQL服务器副本可安装于统一机器上，只要每个服务器使用不同的端口即可

一、数据库基础

1、数据库

数据库（database）：一个以某种有组织的方式存储的数据集合，保存有组织的数据的容器（通常是一个文件或一组文件）

数据库管理系统（DBMS：database message system）：数据库是通过DBMS创建和操作的容器

2、表

表（table）：某种特定类型数据的结构化清单

表是一种结构化文件，可用来存储某种特定类型的数据

数据库中每个表都有唯一的一个名字，用来标识自己，表名的唯一性取决于多个因素：如数据库名等结合（相同数据库不能出现名字相同的表，但不同数据库可以使用相同的表名）

表具有一些特性，其定义了数据在表中如何存储，比如：存储什么类型的数据，数据如何分解，各部分信息如何命名等

描述表的这组信息可以称为“模式”，其用来描述数据库中特定的表以及整个数据库（和其中表的关系）

模式（schema）：关于数据库和表的布局及特性的信息，一般指给予表的一些定义

3、列和数据类型

表由列组成，列中存储着表中某部分的信息

列（column）：表中的一个字段；所有表都有由一个或多个列组成的

分解数据：正确的将数据分解为多个列极为重要：例如省市县应该是独立的列，通过分解，才可能利用特定的列对数据进行排序和过滤，使得检索和访问更加方便（例如找出特定市县的相关数据等）

数据类型（datatype）：所容许的数据的类型：数据库中每个列都有相应的数据类型，其定义列可存储的数据种类（限制/容许该列中存储的数据）

作用：①限制可存储在列中的数据种类（例如防止在数值字段中录入字符值）

②帮助正确的排序数据

③对优化磁盘使用有重要作用

4、行

行（row）：表中的一个记录

表中的数据是按行存储的，保存的每个记录存储在自己的行内；如果将表想象为网格，网格中垂直的列为表列，水平行为表行

5、主键

主键（primary key）：一列或一组列，其值能够唯一区分表中的每一行；唯一标识表中每行的这个列（或这组列）称为主键

主键用来表示一个特定的行；没有主键，更新或删除表中特定行很困难，因为没有安全的方法保证操作只涉及相关的行

主键并不总是需要，但大多数情况下都应保证每个表具有一个主键，以便于数据操作和管理

表中任何列都可以作为主键，只要满足以下2个条件：

①任意两行都不具有相同的主键值；

②每个行都必须具有一个主键值（主键列不允许NULL值）；

主键值规则:主键通常定义于表的一列上，但也可以一起使用多个列作为主键，这种情况下，上述2个条件必须应用到构成主键的所有列，所有列值的组合必须是唯一的（单个列可以不唯一）

设置主键的几个好习惯：

①不更新主键列中的值；

②不重用主键列的值；

③不在主键中使用可能会更改的值（例如使用电话号码作为主键以标识某个人，当该人更改电话号码时，必须更改这个键）

二、SQL简介

SQL：结构化查询语言（Structured Query Language）缩写，一种专门用来与数据库通信的语言

设计SQL的目的是很好的完成一项任务，即提供一种从数据库中读写数据的简单有效的方法

优点如下：

①几乎所有的DBMS都支持SQL

②SQL简单易学，语句由描述性很强的英语单词构成，而且单词的数目并不多

③一门强有力的语言，灵活使用其语言元素，可进行非常复杂和高级的数据库操作

三、MySQL简介

MySQL是一种DBMS，即它是一种数据库软件

MySQL优点：

①成本低，MySQL开放源代码，一般都可以免费使用甚至免费修改

②性能好，MySQL执行效率很快

③很多大型公司（包括一些全世界知名的互联网企业）使用MySQL来处理自己的重要数据

④简安装使用非常容易

1、客户机-服务器软件

DBMS可分为两类：一类为基于共享文件系统的DBMS（包括诸如Microsoft和FileMaker），用于桌面用途，通常不用于高端或更关键的应用；一类为基于客户机-服务器的DBMS

常见的例如MySQL、Oracle、Microsoft SQL Server等数据库是基于客户机-服务器的数据库，分为2个部分：

服务器部分是负责所有数据访问和处理的一个软件，该软件运行在称为数据库服务器的计算机上；关于数据增删改查的所有请求都由服务器软件完成，这些请求来自客户机软件的计算机

客户机部分是与用户打交道的软件，由客户机软件通过网络提交请求给服务器软件，服务器软件根据需要处理数据，然后将结果返回给客户机软件

服务器软件为MySQL DBMS，可在本地安装的副本上运行，也可以连接到运行在你具有访问权的远程服务器上的一个副本

客户机可以是MySQL提供的工具、脚本语言（如Perl）、Web应用开发语言（如ASP、ColdFusion、JSP和PHP）、程序设计语言（如Java、C、C++）等

2、MySQL工具

MySQL是一个客户机-服务器DBMS，使用它需要一个客户机，即用来与MySQL打交道（给MySQL提供需要执行的命令）的应用；最好使用专门用途的实用程序，推荐如下三个：

①MySQL命令行实用程序

该程序是安装MySQL时自带的一个简单命令行实用程序；没有下拉菜单、用户界面、鼠标支持或其他支持的东西，但最好熟悉它，因为它是使用者安全依靠的一个客户机

②MySQL Adiministrator

MySQL Adiministrator（MySQL管理器）是一个图形交互客户机，用来简化MySQL服务器的管理

下载地址：http://dev.mysql.com/downloads/

③MySQL Query Browser

MySQL Query Browser（MySQL查询浏览器）是一个图形交互客户机，用来编写和执行MySQL命令

下载地址：http://dev.mysql.com/downloads/

四、连接MySQL及简单操作

连接MySQL数据库需要以下这些条件：

①主机名（计算机名）：如果是本地MySQL服务器，为localhost

②端口：如果使用默认端口，则为3306

③一个合法的用户名

④用户口令：即登陆密码（如果需要）

1、选择数据库

安装并且成功连接数据库之后，需要选择一个数据库，如果使用的是命令行实用程序，则可以使用user关键字（比如：user tables;）

关键字（key word）：MySQL语言组成部分的保留字，决不能用关键字命名一个表或列

2、了解数据库和表

数据库、表、列、用户以及权限等信息被存储在数据库和表中，内部表一般不直接访问，可以使用show命令来显示（比如：show tablebases;）

show tablebases;返回可用数据库的一个列表

show tables;返回当前选择的数据库内可用表的列表

show也可用来显示表列，比如：show columns from tables

自动增量：某些表列需要唯一值，例如：顾客ID，在每行添加到表中时，MySQL可以自动为每行分配下一个可用编号，不用手动分配，这个功能就是自动增量

如果需要该功能，则需要在用create语句创建表时把它作为表定义的组成部分

其他常见show语句还有：

show status：用于显示广泛的服务器状态信息

show create database 和show create table：分别用来显示创建特定数据库或表的MySQL语句

show grants：用于显示授予用户（所有用户或特定用户）的安全权限

show errors和show warnings：用于显示服务器错误或警告消息

(二)数据的检索和过滤

使用频率最高的SQL语句应该就是select语句了，它的用途就是从一个或多个表中检索信息，使用select检索表数据必须给出至少两条信息：想选择什么，以及从什么地方选择

一、检索数据

1、检索单个列

select column from table；

该SQL语句的检索结果将返回表中的所有行，数据没有过滤（过滤将得出结果集的一个子集），也没有排序（如没有明确排序查询结果，则返回数据的顺序没有特殊意义，只要返回相同数目的行，就是正确的）

MySQL如同大多数DBMS一样，不需要单条SQL语句后加分号，但特定DBMS可能必须在单条SQL语句后加分号；如果是多条SQL语句必须加分号（；），如果使用的是MySQL命令行，则必须用分号结束SQL语句

SQL语句不区分大小写（对所有SQL关键字使用大写，对所有列和表明使用小写，这样更易于阅读和调试）

2、检索多个列

select column1，column2，column3 from table；

在检索多个列时，要在列名之间加上逗号（，），最后一个列名不用加

SQL语句一般返回原始的、无格式的数据，数据的格式只是一个表示问题，而不是检索问题；因此表示方式一般在显示该数据的应用程序中规定，一般很少使用实际检索出的原始数据（没有应用程序提供的格式）

3、检索所有列

select * from table；

给定通配符*，则检索数据时返回表中所有列

一般除非确实需要检索表中的每个列，否则最好别使用*通配符；虽然使用*可能自己比较省事，但检索不需要的列通常会降低检索和应用程序的性能

优点在于：由于不明确指定列名，所以可以检索出名字未知的列

4、检索不同的行

select distinct column from table；

distinct告诉MySQL只返回不同的值；它必须直接放在列名的前面（distinct作用于所有列）

5、限制结果

select column from table limit N；

limit告诉MySQL只返回它指定值的第一行或前几行，N表示行的数量

select column from table limit X，Y；

limit X， Y告诉MySQL返回从行X开始的Y行；X为开始位置，Y为要检索的行数（limit带一个值总是从第一行开始，给出的数为返回的行数；带两个值可以指定从行号为第一个值的位置开始）

检索出来的第一行为行0而不是行1，因此，limit1,1将检索出第二行而不是第一行（在行数不够时，MySQL将只返回能返回的最大行数）

6、使用完全限定的表名

select table.column from database.table;

该SQL语句中检索的表名以及列名是完全限定的

二、排序检索数据

子句（clause）：SQL语句由子句构成，有些子句是必需的，有些是可选的；一个子句通常由一个关键字和所提供的数据组成

1、排序单个列

order by子句：取一个或多个列的名字，据此对输出进行排序（order by位于from子句之后；如果使用limit，它必须位于order by 之后）

select column from table order by column；

该列指示MySQL对column进行排列（通常order by子句中使用的列将是为显示所选择的列）

2、按多个列排序

select column1，column2，column3 from table order by column1，column2；

仅在多个行具有相同的column1时才对column2进行排序，如果指定的column1是唯一的，则不会按照column2排序

3、指定排序方向

select column1，column2，column3 from table order by column1 desc；

desc：desc关键字只应用到直接位于其前面的列名，作用是使其按照降序排列（与其相反的是asc关键字，在升序排序时可以用到，但一般情况下升序是默认的）

如果想在多个列上进行降序排序，则必须对每个列指定desc关键字

4、检索最高或最低值

select column from table orderby column desc limit 1；

column desc告诉MySQL按照降序排列，limit 1告诉MySQL只返回一行的数据

三、过滤数据

数据库包含大量的数据，很少需要检索表中所有航，通常会根据特定操作或报告需要提取表数据的子集；

只检索所需数据需要指定搜索条件（search criteria），搜索条件你也被称为过滤条件（filter condition）

1、where子句

select语句中，数据根据where子句中指定的搜索条件进行过滤，where子句在表名（from子句）之后给出

select column from table where column = N；

该语句的意思为从table表中筛选出column=N的行；采用了最简单的相等测试，检查一个列是否具有指定的值据此进行过滤

如果同时使用order by和where子句，order by位于where之后（否则会出错）

2、where子句操作符

=：等于 <>：不等于！=:不等于 <:小于 <=:小于等于 >:大于 >=:大于等于 between：在指定的两个值之间

3、不匹配检查

select column from table where column <> N；

select column from table where column != N；

where子句中，对过滤的值，有的用单引号，有的不用，原因在于：单引号用于限定字符串，如果将值与串类型的列进行比较，则需要，如用来与数值列比较，则不用引号

3、范围值检查

select column from table where column between X and Y；

为了检查某个范围的值，可以使用between操作符。使用between操作符需要两个值：范围的开始值和结束值（上面例子中X和Y就是开始和结束值）

between匹配范围内的所有的值，包括指定的开始值和结束值

4、空值检查

select column from table where column1 is null；

建表时，设计人员可以指定其中的列是否可以不包含值，在一个列不包含值时，称其为包含空值NULL（无值{no value}：它与字段包含0、空字符串或仅仅包含空格不同）

is null子句就是用来检查表中具有null值的列（在过滤数据选择出不具有特定值的行时，一定要验证返回数据中确实给出了被过滤列具有null的行）

四、使用操作符过滤数据

操作符（operator）：用来联结或改变where子句中的子句的关键字。也称为逻辑操作符（logical operator）

1、and操作符

select column1， column2，column3 from table where column1 = X and column <=Y；

and，用在where子句中的关键字，用来指示检索满足所有给定条件的行；即：and指示DBMS只返回满足所有给定条件的行（可添加多个过滤条件，每添加一条就要使用一个and）

2、or操作符

select column1， column2 from table where column1 = X or column <=Y；

or，用在where子句中的关键字，用来表示检索匹配任一给定条件的行；即：or告诉DBMS匹配任一条件而不是同时匹配两个条件

3、计算次序

select column1，column2 from table where column1 =X or column1 = Y and column2 >= N;

该SQL语句的意思是从table表中过滤出column2为N（含）以上且column1=X或Y的的所有行（实际中是先计算column1 = Y and column2 >= N然后计算column1 =X）

where可包含任意数目的and和or操作符；允许两者结合以进行复杂和高级的过滤（需要说明的是：and在计算次序中优先级高于or）

如果希望and和or之间计算次序不被错误组合，可将or操作符所对应的子句使用圆括号（）括起来，以明确的分组相应的操作符

圆括号具有较and或or更高的计算次序，DBMS首先过滤圆括号内的条件

PS：任何时候使用具有and和or操作符的where子句，都应该使用圆括号明确的分组操作符！

4、in操作符

select column1， column2 from table where column1 in （X,Y） order by column2；

该SQL语句的意思是从table表中检索所有column=X和Y的column2的行（in操作符用来指定匹配值的清单的关键字，功能和or相当）

圆括号在where子句中还有另一种用法，in操作符用来指定条件范围，范围中的每个条件都可以进行匹配；in取合法值的由逗号分隔的清单，全都括在圆括号中；

in操作符优点如下：

①使用长的合法选项清单时，in操作符的语法更清楚且更直观

②使用in操作符，计算的次序更容易管理（使用的操作符更少）

③in操作符一般比or操作符清单执行更快

④in最大的优点是可以包含其他select语句，使得能够更动态的建立where子句

5、not操作符

select column1， column2 from table where column1 not in （X,Y） order by column2；

该SQL语句的意思是从table表中列出除column1为X,Y之外的所有column2的行（not操作符用来否定后跟条件的关键字）

not操作符有且只有一个功能，就是否定它之后所跟的任何条件

MySQL支持使用not对in、between和exists子句取反，这与其他多数DBMS允许使用not对各种条件取反有很大差别

五、使用通配符过滤数据

通配符（wildcard）：用来匹配值的一部分的特殊字符，利用通配符可以创建比较特定数据的搜索模式（实际上是SQL的where子句中带有特殊含义的字符）

搜索模式（search pattern）：由字面值、通配符或两者组合构成的搜索条件

为在搜索子句中使用通配符，必须使用like操作符；like指示MySQL后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较

1、百分号（%）通配符

在搜索串中，%表示任何字符出现任意次数；通配符可以在搜索模式中任意位置使用，并且可以使用多个通配符

select column1， column2 from table where column1 like ‘test%’

上面的SQL语句表示任意以test起头的词；%告诉MySQL接受test之后的任意字符，不管它有多少字符

test%：所有以test起头的值/ %test%：匹配任何包含test的值/ te%st：所有以te开头以st结尾的值

PS：%通配符不可以匹配null

2、下划线（_）通配符

select column1， column2 from table where column1 like ‘_test ’

下划线通配符作用和%一样，但下划线通配符只匹配单个字符而不是多个字符（与%能匹配0个字符不一样，总是匹配一个字符）

3、使用通配符的技巧

①不要过度使用通配符（如果其他操作符能达到同样目的，应使用其他操作符）

②在确实需要使用通配符时，除非绝对有必要，否则不要把通配符用在搜索模式开始处（这样做是最慢的）

③注意通配符所在的位置

(三)用正则表达式搜索

正则表达式是用来匹配文本的特殊的串（字符集合），将一个模式（正则表达式）与一个文本串进行比较；

所有种类的程序设计语言、文本编辑器、操作系统等都支持正则表达式，正则表达式用正则表达式语言来建立；

MySQL仅支持多数正则表达式实现的一个很小的子集；

一、基本字符匹配

select column from table where column regexp ‘1000’ order by column；

该语句检索列column包含文本1000的所有行；它告诉MySQL：regexp后所跟的东西作为正则表达式（与文字正文1000匹配的一个正则表达式）处理。

select column from table where column regexp ‘.000’ order by column；

该语句中的正则表达式（.），是正则表达式语言中的一个特殊字符，表示匹配任意一个字符（该语句也可以使用like和通配符完成）

like和regexp的区别：

①like匹配整个列；

select column from table where column like ‘1000’ order by column；

该SQL语句将不返回数据，因为like匹配整个列，如果被匹配的文本在列值中出现，like将不会找到它，相应的行也不会被返回（除非使用通配符）

②regexp在列值内进行匹配

select column from table where column regexp ‘1000’ order by column；

该SQL语句会返回一行数据，如果被匹配的文本在列值中出现，regexp将会找到它，相应的行将被返回（如果希望regexp匹配整个列值，使用^和$定位符{anchor}）即可

MySQL中正则表达式匹配不区分大小写，如希望区分大小写，可使用binary关键字，如where column regexp binary ‘test .000’

二、进行or匹配

select column from table where column regexp ‘X|Y’ order by column;

or表示匹配其中之一，功能雷雨与select语句中的or语句，多个or条件可并入单个正则表达式

三、匹配几个字符之一

select column from table where column regexp ‘[XYZ]test’ order by column;

[]是另一种形式的or语句，作用是匹配指定的（其中之一）字符；正则表达式[XY]test为[X|Y|Z]test的缩写，它的意思是匹配X或者Y或者Z

字符集合也可以被否定，即匹配除指定字符外的任何东西，为否定一个字符集，可以在集合开始处设置一个‘^’例如[^XYZ]（[]定义更准确，|如果不括起来，则应用于整个串）

四、匹配范围

集合可以用来定义要匹配的一个或多个字符

比如[0123456789]，为了简化这种类型的集合，可使用（-）来定义一个范围，即[0-9]；（范围不仅仅局限于数值，还可以使字母字符等）

五、匹配特殊字符

正则表达式由具有特定含义的特殊字符构成；如果要匹配这些特殊字符，就需要用\\为前导；\\-表示查找-，\\.表示查找.

select column from table where column regexp ‘\\-’ order by column；

这种处理方式就是所谓的转义（escaping），正则表达式内具有特殊意义的所有字符都必须以这种方式转义；（为了匹配反斜杠{\}字符本身，需要使用\\\）

PS：多数正则表达式实现使用单个反斜杠转义特殊字符，以便能够使用这些字符本身，而MySQL要求两个反斜杠（MySQL自己解释一个，正则表达式库解释一个）

\\也用来引用元字符（具有特殊含义的字符），如下：

六、匹配字符类

字符类（character class）：有时候需要检索出我们需要的数字、所有字母字符或所有数字字母字符等的匹配，我们可以使用预定义的字符集，称为字符类；如下：

七、匹配多个实例

有时候需要对匹配的数目进行更强的控制，比如：寻找所有的数，不管数中包含多少数字，或寻找一个单词并尾随一个s（如果存在）等情况，我们可以利用正则表达式中的重复元字符来完成；如下

select column from table where column regexp ‘\$[0-9] sticks?\$’ order by column；

上面的正则表达式‘\$[0-9] test?\$’意为：\\匹配（[0-9]匹配任意数字，这里指匹配的数值范围，sticks?匹配stick和sticks{?使s可选，因为？匹配它前面任何字符的0或者1次出现}）

八、定位符

有时候为了匹配特定位置的文本，需要使用定位符，常用定位符列表如下：

例如：找出一个以一个数（包括小数点开始的数）开始的所有数值，简单搜索[0-9\\.]或（[[:digit:]\\.]）不行，因为它将在文本内任意位置查找匹配，可以使用^定位符，如下

select column from table where column regexp '^[0-9\\.]' order by column;

^匹配串的开始，有两种用法：

①在集合中（用[和]定义），用它来否定该集合

②用来指串的开始处

使regexp和like起相同作用：like匹配整串二regexp匹配子串，可以利用定位符，用^开始每个表达式，用$结束每个表达式，就可以使regexp和like作用一样

(四)字段及常用函数

一、字段

数据库表中的每一行叫做一个“记录”，每一个记录包含这行中的所有信息，但记录在数据库中并没有专门的记录名，常常用它所在的行数表示这是第几个记录。

在数据库中存放在表行列交叉处的数据叫做“值”，它是数据库中最基本的存储单元，它的位置要由这个表中的记录和字段来定义。

1、字段（filed）：与对象或类关联的变量（大多数时候，与列的意思相同）；每个字段由若干按照某种界限划分的相同数据类型的数据项组成。

但有时候，字段不是表中的列，而是在计算字段的连接上；

2、拼接（concatenate）：用来拼接2个列的函数，可将值联结到一起构成单个值

PS:多数DBMS使用+或者||实现拼接，MySQL则使用concat（）函数来实现（当把SQL语句转换为MySQL语句时，要注意这个区别）

concat（）函数：拼接串，即把一个或多个串连接起来形成一个较长的串；需要一个或多个指定的串，各个串之间用逗号分隔

3、别名（alias）：一个字段或值的替换名，别名用as关键字赋予（比如上面例子中的abc），有时也称为导出列（derived column）

别名的作用：

①指示SQL创建一个包含指定计算的计算字段

②在实际的表列名包含不符合规定的字符（如空格）时重新命名它

③在原来的名字含混或容易误解时扩充它

4、算数操作符：计算字段时常用的操作符

计算字段的常见用途就是对检索出的数据进行算数计算，MySQL基本操作符如下：

其中，圆括号（）可用来区分优先顺序

二、数据处理函数

1、特点

SQL支持利用函数来处理数据，函数一般都是在数据上执行的

函数没有SQL的可移植性强（能运行在多个系统上的代码称为可移植的{portable}），多数SQL是可移植的

函数可移植性不强（几乎每种主要的DBMS的实现都支持其他实现不支持的函数，而且有时差异很大）

PS：如果使用函数，应保证做好代码注释，以便日后使用时可以确切知道所编写的SQL代码的含义

大多数SQL实现支持一下类型函数：

①用于处理文本串（删除填充值，转换值大小写等）的文本处理函数

②用于在数值数据上进行算数操作（如返回绝对值，进行代数运算）的数值处理函数

③用于处理日期和时间值并从这些值中提取特定成分（如返回两个日期之差，检查日期有效性等）的日期和时间处理函数

④返回DBMS正在使用的特殊信息（如返回用户登陆信息，检查版本细节等）的系统函数

2、文本处理函数

例子：select column upper(column) as column1 from table order by column;

常见的文本处理函数表如下：

soundex是一个将任何文本串转换为描述其语音表示的字母数字模式的算法；soundex考虑了类似的发音字符和音节，使得能对串进行发音比较而不是字母比较（多数DBMS都提供对其的支持）

3、数值处理函数

数值处理函数仅处理数值数据，这些函数一般主要用于代数、三角或几何计算，使用频率相对不是太高（在主要的DBMS中，数值函数是最统一最一致的函数）

常用数值处理函数表如下：

4.日期和时间处理函数

日期和时间采用相应的数据类型和特殊的格式存储，一遍可以快速有效的排序或过滤，并且节省物理存储空间

一般来说应用程序不适用用来存储日期和时间的格式，因此日期和时间函数总是被用来读取、统计和处理这些值（日期和时间函数在MySQL语言中具有重要的作用）

常用日期和时间处理函数表如下：

PS：对于日期，无论插入更新还是使用where子句进行过滤，日期格式必须为yyyy-mm--dd，而且应始终使用4位数字的年份

如果需要的是日期值，使用date（）函数是一个好习惯；如果想要时间值时，time（）函数也是最好的选择

例子：select column1, column2 from table where date(table_date) between '2013-05-20' and '2014-5-20';

其中between操作符用来把2013-05-20和2014-05-20定义为一个要匹配的日期范围

select column1， column2 from table where year（table-date） = 2013 and month(table_date) = 5;

year（）是一个日期（从日期时间）中返回年份的函数，month（）从日期中返回月份；

因此，where year（table-date） = 2013 and month(table_date) = 9检索出table_date为2013年5月的所有行！

(五)汇总和分组数据

一、汇总数据

工作中经常需要汇总数据而不是将它们全部检索出来（实际数据本身：返回实际数据是对时间和处理资源的浪费），这种类型的检索有以下特点：

①确定表中的行数（或者满足某个条件或包含某个特定值的行数）

②获得表中行组的和

③找出表列（或所有行或某些特定的行）的最大值、最小值和平均值

聚集函数（aggregate function）：运行在行组上，计算和返回单个值的函数（MySQL还支持一些列的标准偏差聚集函数）；

SQL聚集函数如下表：

1、avg（）函数

avg（）通过对表中行数计数并计算特定列值之和，求得该列的平均值；avg（）可用来返回所有列平均值，也可用来返回特定列的平均值；

select avg(prod_price) as avg_price from pfoducts;

此select语句返回值avg_price，它包含producs表中所有产品的平均价格，avg_price是一个别名；

avg（）也可以用来确定特定列或行的平均值，例子如下：

select avg_(prod_price) as avg_price from products where vend_id = 1003;

这条SQL语句包含了where子语句，仅过滤出vend_id为1003的产品，avg_price中返回该供应商的产品的平均值；

PS：avg（）只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出，为了获得多个列的平均值，必须使用多个avg（）函数{avg（）函数忽略列值为NULL的行}；

2、count（）函数

count（）函数进行计数，可利用count（）确定表中行的数目或符合特定条件的行的数目；

count（）函数有两种使用方式：

①使用count（*）对表中行的数目进行计数，不管表列中包含的是空值（null）还是非空值；

②使用count（column）对特定列中具有值的行进行计数，忽略null值；

select count（*） as num_cust from customers；

这条SQL语句利用count（*）对customers表中所有行计数，计数值在num_cust中返回；

select count（cust_email） as cum_cust from customers；

这条SQL语句使用count（cust_email）对cust_email列中有值的行进行计数；

PS：如果指定列名，则指定列的值为空的行被count（）函数忽略，但如果count（）函数中用的是星号（*），则不忽略；

3、max（）函数

max（）返回指定列中的最大值，max（）要求指定列名，例子如下：

select max（prod_price） as max_price from products;

这条SQL语句中国返回products表中price列的最大值；

PS：MySQL允许max（）用来返回任意列中的最大值，包括返回文本列的最大值；但用于文本数据时，如果数据按相应的列排序，则max（）返回最后一行（max（）函数忽略列值为null的行）

4、min（）函数

min（）返回指定列的最小值，min（）也要求指定列名，例子如下：

select min（prod_price） as min_price from products；

这条SQL语句中min（）返回products表中price列最小值；

PS：MySQL允许min（）用来返回任意列中的最小值，包括返回文本列的最小值；但用于文本数据时，如果数据按相应的列排序，则min（）返回最前面的行（min（）函数忽略列值为null的行）

5、sum（）函数

sum（）函数用来返回指定列值的和（总计）；例子如下：

select sum(quantity) as items_ordered from orderitems where order_num = 20005;

函数sum（）返回orderitems中所有quantity列的值之和，where子句保证只统计某个指定列的数值；

PS：利用标准的算数操作符，所有聚集函数都可用来执行多个列上的计算（sum（）函数忽略列值为null的行）

6、distinct与聚集函数

MySQL5.0.3以及之后的版本，聚集函数和distinct可以搭配使用，比如：

①对所有的行执行计算，指定all参数或不给参数（all是默认所有行为，不需要指定，如果不指定distinct，则假定为all）；

②只包含不同的值，指定distinct参数；

③如果指定列名，则distinct只能用于count（）；distinct不能用于count（*），因此不允许使用count（distinct）；distinct必须使用列名，不能用于计算或者表达式；

select avg(distinct prod_price) as avg_price from products where vend_id = 1003;

这条SQL语句中，使用avg（）函数返回vend列中vend_id=1003的对应的price平均价格，因为使用了distinct参数，因此平均值只考虑不同的值（唯一值）

7、组合聚集函数

select语句可以包含多个聚集函数，比如：

select count（*） as num_items,

min(prod_price) as price_min,

max(prod_price) as price_max,

avg(prod_price) as price_avg

from products;

这条SQL语句，使用了单条select语句执行4个聚集计算，返回四个值（products表中items的数目、price的最高、最低以及平均值）

PS：在指定别名以包含某个聚集函数的结果时，不应该使用表中实际的列名；这样便于使用SQL更加容易和理解，以及排除方便排除错误。

二、分组数据

1、group by创建分组

在MySQL中，分组是在select语句中的group by子句中建立的，比如：

select vend-id,count(*) as num_prods from products group by vend_id;

这条SQL语句指定了2个列，group by指示MySQL按照vend_id排序并且分组（如果使用group by，则不必指定要计算的每个组）

group by子句指示指示MySQL分组数据，然后都每个组而不是整个结果集进行聚集；关于group by使用，请注意以下规则：

①group by子句可以包含任意数目的列（使得对分组进行嵌套，为数据分组提供更细致的控制）；

②如果在group by子句中嵌套分组，数据将在最后规定的分组上进行汇总，即：建立分组时，指定的所有列都一起计算（所以不能从个别列取回数据）；

③group by子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数），如果在select中使用表达式，则必须在group by子句中指定相同的表达式（不能使用别名）；

④除了聚集计算语句外，select中每个列都必须在group by子句中给出；

⑤如果分组列中具有null值，则null将作为一个分组返回（如果列中有多行null值，他们将分为一组）；

⑥group by子句必须出现在where子句之后，order by子句之前；

PS：使用with rollup关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值。

2、having过滤分组

where子句都可以用having代替，区别在于where过滤行，having过滤分组；having支持所有的where操作符，比如：

select cust_id,count(*) as orders from orders group by cust_id having count(*) >= 2;

这条SQL语句中的having子句过滤count（*）>=2（2个以上的分组）的那些分组；

having和where的区别：

where在数据分组前进行过滤，having在数据分组后进行过滤；where排除的行不包括在分组中（这可能会改变计算值，从而影响having子句中基于这些值过滤掉的分组）

having和where可以同时使用，比如：

select vend_id, count(*) as num_prods from products where prod_price>=10 group by vend_id having count(*)>= 2;

这条SQL语句中，where子句过滤掉所有prod_price至少为10的行，然后按照vend_id分组数据；having子句过滤技术为2或2以上的分组；

3、分组和排序

group by和order by的区别：

order by的重要性：一般使用group by子句时，应该也给出order by子句，这是保证数据正确性的唯一方法（千万不要依赖group by排序数据）。

4、select子句顺序

(六)常用语法和数据类型

阅读MySQL语法时，需要注意的规则：

①符号用来指出几个选择中的一个，比如：null | not null表示或者给出null或者给出not null；

②包含在方括号中的关键字或子句（如[like this]）是可选的；

③既没有列出所有的MySQL语句，也没有列出每一条子句和选项。

一、常用语法

1、alter table：更新已存在表的模式

alter table tablename

( add column datatype [null | not null] [constarints],

change column columns datatype [null | not null] [contarints].

drop column,);

2、commit：将事务处理写到数据库

commit；

3、create index：在一个或多个列上创建索引

create index indexname on tablename (column [asc | desc],...);

4、create procedure：创建存储过程

create procedure procedurename( [parameters] ) begin ...... end;

5、create table：创建新数据库表

create table tablename

( column datatype [null | not null] [constraints],

column datatype [null | not null] [constraints],

......);

6、create user：向系统中添加新的用户账户

create user username[@hostname]

[identified by [passwrod] 'password'];

7、create view：创建一个或多个表上的新视图

create [or replace] view viewname as select ...;

8、delete：从表中删除一行或多行

delete from tablename [where ...];

9、drop：永久删除数据库对象（表、视图、索引等）

10、insert：给表增加一行

insert into tablename [(columns,...)] values(values,...)

11、insert select：插入select结果到一个表

insert into tablename[(columns,...)]

select columns,... from tablename,...

[where ...];

12、rollback：撤销一个事务处理块

rollback [ to saveprointname];

13、savepoint：为使用rollback语句设立保留点

savepoint sql;

14、select：从一个或多个表（视图）中检索数据

select columnname,... from tablename,...

[where ...]

[union ...]

[group by ...]

[having ...]

[order by ...]

15、start transaction：一个新的事务处理块的开始

start transaction;

16、update：更新表中的一行或多行

uptable tablename set columname = value, ... [where ...];

二、数据类型

数据类型：定义列中可以存储什么数据以及该数据实际怎样存储的基本规则，其用于以下几个目的：

①允许限制可存储在列中的数据

②允许在内部更有效的存储数据

③允许变换排序顺序（作为数值数据类型，数值才能正确排序）

1、串数据类型

该类型为最常用的数据类型，用来存储串（比如名字、地址等）；有两种串类型，分别是定长串和变长串

定长串：接受长度固定的字符串，其长度实在创建表时指定的；定长列不允许多余指定的字符数目，它们分配的存储空间与指定的一样多（比如char）

变长串：存储可变长度的文本，有些变长数据类型具有最大定长，有些是完全变长的，不论哪种，指定的数据得到保存即可（灵活）

PS:MySQL处理定长列比变长列快速的很多，且MySQL不许云对变长列（或一个列的可变部分）进行索引

串数据类型表：

PS：①不管是用任何形式的串数据类型，串值都必须括在引号内（通常使用单引号）

②如果数值是计算（求和平均等）中使用的数值，应存储在数值数据类型列中；如果作为字符串（可能只包含数字）使用，则应保存在串数据类型列中；

2、数值数据类型

数值数据类型存储数值，MySQL支持多种数值数据类型，每种存储的数值具有不同的取值范围；

数值数据类型表：

PS：所有数值数据类型（除bit和bollean）都可以有符号或者无符号，有符号数值列可以存储正或负的数值，无符号数值列只能存储正数，默认情况为有符号（与串不同，数值不应该在括号内）

3、日期和时间数据类型

日期和时间数据类型表：

4、二进制数据类型

二进制数据类型可存储任何数据（甚至包括二进制信息），如图像、多媒体、字处理文档等

二进制数据类型表：

(七)联结表

一、联结表基础知识

1、关系表

把信息分解成多个表，一类数据一个表，各表通过某些常用值（即关系设计中的关系（relational））互相关联；

2、外键（foreign key）：外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系

3、联结表的优点：

①数据信息不重复，从而不浪费时间和空间

②如果某个数据信息变动，可以只更新该表中的某个记录，相关表数据不用变更

③数据无重复，可以更有效的存储和方便的处理，伸缩性强（能够适应不断增加的工作量而不失败，设计良好的数据库或者应用程序称为可伸缩性好（scale well））

PS：联结是一种机制，用来在一条select语句中关联表，因此称之为联结（联结在运行时关联表中正确的行）

二、创建联结

例如：select A_name,B_name,B_mobile

from A, B

where A.A_id = B.B_id

order by A_name, B_name;

该SQL语句中，select语句指定要检索的列（因为是创建关联，所以指定了3个列，区别在于A_name在A表中，B_name和B_mobile在另一个表）；from语句列出了2个表分别是A和B，A和B表使用where子句正确联结，

where子句指示MySQL匹配A表中的A_id和B表中的B_id相匹配，要匹配的两列以A.A_id和B.B_id完全限定表名（当引用的列可能存在二义性，必须使用完全限定表名（用一个点分隔的表名和列名））

PS：where子句的重要性

在一条select语句中联结几个表时，相应的关系是在运行中构造的；在联结两个甚至多个表时，实际上是将第一个表中的每一行与第二个表中的每一行配对，where子句作为过滤条件，它只包含匹配的给定条件的行。

没有where子句，第一个表中每个行将与第二个表中的每个行匹配，而不管逻辑上是否可以配在一起。

1、笛卡尔积

由没有联结条件的表关系返回的结果称为笛卡尔积（检索出的行的数目将是第一个表中的行数乘以第二个表中的行数）；

例如：select A_name,B_name,B_mobile

from A, B

order by A_name, B_name;

PS:应保证所有联结都有where子句，否则MySQL将返回比想要的数据多得多的数据，还应保证where子句的正确性。

2、内部联结

等值联结（equijoin）：基于两个表之间的相等测试，也称为内部联结

例如：select A_name,B_name,B_mobile

from A inter join B

on A_name = B_name;

这条SQL语句中，select语句与之前的相同，相较于上面的from，这里使用了inter join指定（当使用inter join时，联结条件用on子句而不是where子句，在这里on等同于where）

3、联结多个表

SQL对一条select语句中可以联结的表的数目没有限制，创建规则也基本相同（首先列出所有表，然后定义表之间的关系）

PS：MySQL在运行时关联指定的每个表以处理联结，这样很耗费资源，因此使用中应注意，不要联结不必要的表。

三、创建高级联结

1、使用表别名

别名除了允许用于列名和计算字段外，SQL还允许给表名起列名，好处在于：

①缩短SQL语句；

②允许在单条select语句中多次使用相同的表；

例如：select a_name, a_mobile

from A as a, B as b, C as c

where A.a_id = B.b_id

and C.c_num = B.b_num

and birthday_id = '1992-10-26';

PS:表别名不仅可用于where子句，还可用于select列表，order by子句以及语句其他部分（表别名只在查询执行中使用，表别名不返回到客户端主机）

2、自联结

自联结：自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。

例如：select p1.user_id, p1.user_name

from usertable as p1, usertable as p2

where p1.user_id = p2.user_id

and p2.user_id = '10086';

这条SQL语句中，usertable表第一次出现为别名p1，第二次为别名p2，在select语句中明确使用p1前缀给出所需列名，否则MySQL将返回错误，因为其无法正确区分想要的具体为哪一列；

3、自然联结

无论何时对表进行联结，应至少有一个列出现在不止一个表中（被联结的列）；标准的联结返回所有数据，自然联结排除多次出现，只返回一次。

一般使用select *通配符，对其他表的列使用明确的自己来完成，通配符只对第一个表使用，所有其他列明确列出。

4、外部联结

用来检索包含了在相关表中没有关联行的行，这种类型的联结称为外部联结。

例如：select A.cust_id, B.mobile_num

from A left outer join B

on A.cust_id = B.mobile_num;

这条select语句使用关键字outer join来指定联结的类型（不是在where子句中指定）；在使用outer join语法时，必须使用left或right指定包括其他所有行的表（right指出右边的表，left指出左边的表）

外部联结的类型：

分为左外部联结和右外部联结，唯一差别是关联表的顺序不同，左外部联结可以通过颠倒from或where子句中的表顺序转换为右外部联结，两种类型可以互换使用（聚集函数也可和联结一起使用）。

PS：联结使用要点

①注意使用的联结类型

②保证使用正确的联结条件，否则将返回不正确的数据

③应该总是提供联结条件，否则会得到笛卡尔积

④同一个联结下可包含多个表，甚至对每个联结采用不同的联结类型

(八)子查询和分组查询

一、子查询

1、子查询（subquery）：嵌套在其他查询中的查询。

例如：select user_id from usertable where mobile_no in (select mobile_no from mobile where mobile_id = '10086');

这条SQL语句中，括号内为从mobile表汇总检索mobile_id为10086的所有行中的mobile_no列，括号外为从user_table表中检索mobile_id为10086的所有行中的user_id列；

PS：select语句中，子查询总是从内向外处理（实际上，MySQL执行了2个select操作），where子句中使用子查询，必须保证select语句具有与where子句中相同数目的列；

子查询一般与in操作符结合使用，但也可用于测试等于（=）、不等于（<>）等。

格式化SQL：包含子查询的select语句一般相较来说阅读和调试更为不方便，特别是它比较复杂的情况下，因此把子查询分解为多行并且适当缩进，能极大的简化子查询的使用。

2、使用计算字段使用子查询

例如：select user_name,user_id,(select count(*))

from orders where orders_cust_id = usertable_user_id)

as orders

from usertable

order by user_name;

这条SQL语句对usertable表中每个用户返回3列：user_name,user_id和orders,orders是一个计算字段，由圆括号内的子查询建立，它对检索出的每个用户执行一次，

子查询中where子句它使用了完全限定表名，它告诉SQL比较orders表和usertable表中的user_id列。

相关子查询（correlated subquery）:涉及外部查询的子查询（任何时候只要列名可能存在多叉性，就必须使用这种语法[表名和列名有一个句点分隔]）。

PS：使用子查询建立查询的最可靠方法是逐渐进行（首先建立最内层的查询，确认后用硬编码数据建立外层查询，由内到外）

二、组合查询

MySQL允许执行多个查询（多条select语句），并将结果作为单个查询结果集返回，这些组合查询称为并（union）或复合查询（compound query）。

以下两种基本情况，需要使用组合查询：

①在单个查询中从不同表返回类似结构的数据；

②对单个表执行多个查询，按单个查询返回数据；

1、union

union可将多条select语句的结果组合成单个结果集，例子如下

select user_id, mobile_id, mobile_num

from mobiletables

where mobile_num = 10086

union

select user_id, mobile_id, mobile_num

from mobuletables

where user_id in (10000,10010);

这条SQL语句中，union指示MySQL执行两条select语句，并把输出组合成单个查询结果集。

union使用规则：

①union必须由两条或以上的select语句组成，语句之间用关键字union分隔；

②union中每个查询必须包含相同的列、表达或聚集函数（各个列不需要以相同的次序列出）；

③列数据类型必须兼容：类型不用完全相同，但必须是DBMS可以隐含的转换类型；

④union自动从查询结果集中去除重复的行（这是union的默认行为，如果想返回所有匹配行，可使用union all）

2、union all

union自动从查询结果集中去除重复的行，如果想返回所有匹配行，可使用union all；例子如下：

select user_id, mobile_id, mobile_num

from mobiletables

where mobile_num = 10086

union all

select user_id, mobile_id, mobile_num

from mobuletables

where user_id in (10000,10010);

union和where的区别：

union几乎可以完成与多个where条件相同的工作。union all为union的一种形式，它完成where子句完成不了的工作（如果需要每个条件匹配行全部出现，则必须使用union all）。

3、对组合查询结果排序

select语句的输出用order by子句排序，在用union组合查询时，只能使用一条order by子句，它必须出现在最后一条select语句之后。

select user_id, mobile_id, mobile_num

from mobiletables

where mobile_num = 10086

union all

select user_id, mobile_id, mobile_num

from mobuletables

where user_id in (10000,10010)

order by user_id, mobile_num;

MySQL将用它来排序所有的select语句返回的所有结果。

(九)插入，更新，删除

常用的SQL语句，除了select用于查询，还有insert、update、delete等。

一、insert

insert：用来插入（或添加）行到数据库中，常见方式有以下几种：

①插入完整的行；

②插入行的一部分；

③插入多行；

④插入某些查询的结果；

1、插入完整的行

例如：insert into usertable

values(null,

'zhangsan',

'25',

'1992-05-20',

null,

null);

这条SQL语句插入一条新的个人信息到usertable表，存储在每个表列中的数据在values中给出，对每个列必须提供一个值，如果某个列没有值，应使用null（假定表允许对该项指定空值）；

各个列必须以它们在表定义中出现的次序填充；缺点在于；高度依赖表中列的定义次序，如果表结构出现变动，难免出现问题。

更安全的方法如下（相对比较麻烦）：

例如：insert into usertable(user_id,

user_name,

user_year，

user_birthday,

user_address,

user_email)

values(null,

'zhangsan',

'25',

'1992-05-20',

null,

null);

这条SQL语句与上面的SQL语句完成同样的工作，但给出了列名，valuse必须以指定的次序匹配指定的列名，不一定按照列出现在表中的实际次序；优点是：即使表结构改变，此insert语句仍然正确工作。

insert使用注意事项：

①无论使用哪种insert语法，都必须给出values的正确数目，如果不提供列名，则必须给每个表列提供一个值；如果提供列名，则必须对每个列出的列给出一个值；

②如果表定义允许，则可以在insert操作中省略某些列，列必须满足这两个条件（1.改列定义为允许null值；2.在表定义中给出默认值【如果不给出值，则使用默认值】）；

③数据库被多个用户访问，一般检索是最重要的，可以通过在insert和into中间添加关键字low priority，指示MySQL降低insert语句的优先级（同样适用于update和delete语句）；

2、插入多个行

例如：insert into usertable(user_id,

user_name,

user_year，

user_birthday,

user_address,

user_email)

values(null,

'zhangsan',

'25',

'1992-05-20',

null,

null);

或者只要每条insert语句中的列名（和次序）相同，可以只在insert语句后面输入一次列名就可以（单个insert语句由多组值，每组值用一堆圆括号包含，用逗号隔开）

PS:MYSQL用单条insert语句处理多个插入比使用多个insert语句快。

3、插入检索出的数据

例如：insert into usertable(user_id,

user_name,

user_year，

user_birthday,

user_address,

user_email)

select user_id,

user_name,

user_year,

user_birthday,

user_adress,

user_email

from newtablebase;

这条SQL语句中，select从newtablebase表中检索出要插入的数据，select列出的每个列对应tablebase表名后所跟的列表中的每个列。

PS：insert select语句中，不一定要求列名匹配，实际上select中的第一列将用来填充表列中指定的第一列；insert select语句中select语句可包含where子句过滤插入的数据。

二、更新数据

如果要更新（修改）表中的数据，可以使用update语句，有以下两种方法：

①更新表中特定行；

②更新表中所有行；

update语句由三部分构成：

①要更新的表；

②列名和它们的新值；

③确定要更新行的过滤条件；

例如：update usertable

set user_email = '3033310371@qq.com'

where user_id = '10086';

这条SQL语句中，要更新的表名为usertable，set命令用来将新值赋给被更新的列user_email；

PS:update语句总是以要更新的表的名字开始，以where子句结束，它告诉MySQL更新哪一行。

更新多个列：update usertable

set user_name = 'zhangsan',

user_email = '3033310371@qq.com'

where user_id = '10086';

更新多个列时，只需要使用单个set命令，每个“列=值”对之间用逗号分隔（最后一列不用逗号）。

PS:如果用update更新多个行，且更新中一行或多行出现错误，则update操作将被取消（错误发生前执行的动作将被回滚到最初状态。即已更新的被恢复原来的值）；

如果希望出错误，更新继续执行，可以使用ignore关键字：update ignore tablebase......

如果希望删除一个值，可以将它设置为null（如果表定义允许null值），例子如下：

update usertable

set user_email = null

where user_id = '10086';

三、删除数据

从一个表汇总删除数据，使用delete语句；有以下两种方式：

①从表中删除特定行；

②从表中删除所有行；

例如：delete from usertable where user_id = '10086';

这条SQL语句中，delete from要求指定从中删除数据的表名，where子句过滤要删除的行。

PS：delete不需要列名或者通配符，delete删除整行而不是整列，为了删除指定的列，可使用update语句（delete从表中删除行甚至表中所有行，但不删除表本身）；

如果想删除表中所有行，可使用truncate table语句，它的执行效率比delete更快（truncate实际上是删除原来表并重新新建一个表，而不是逐行删除表中数据）。

更新和删除规则：

①除非确实打算更新或删除每一行，否则决不能使用不带where子句的update或delete语句；

②保证每个表都有主键，尽可能像where子句那样使用；

③对update和delete语句使用where子句前，最好先select进行测试，保证过滤的数据是正确的；

④使用强制实施引用完整性的数据库（这样MySQL将不允许删除具有与其他表相关联的数据的行）。

(十)操作表及全文本搜索

一、创建表

MySQL不仅用于表数据操作，还可以用来执行数据库和表的所有操作，包括表本身的创建和处理。

创建表一般有如下两种方式：

①使用具有交互式创建和管理表的工具；

②直接使用MySQL语句操纵表；

1、表创建基础

使用程序创建表，可使用SQL中的create table语句，需要以下两个信息：

①新表的名字，在关键字create table后给出；

②表列的名字和定义，用逗号分隔；

例如：

create table usertables

(

user_id int not null auto_increment,

user_name char(50) null,

user_address char(50) null,

user_city char(50) null,

user_email char(50) null,

user_mobile char(50) null,

primary key (user_id)

)engine=InnoDB;

这条SQL语句中，表名usertables紧跟在create table之后，实际的表定义（所有列）括在圆括号内，各列间用逗号分隔，表的主键用primary key指定为user_id。

PS:创建新表时，指定的表名必须不存在（如果只想在一个表不存在时创建它，应在表名前给出if not exists：这样做不检查表模式是否与打算创建的表模式匹配，只检查表名是否存在）。

2、使用null值

null就是没有值或者缺值；允许null值的列也允许在插入行时不给出该列的值，不允许null值的列不接受该列没有值的行（插入或更新行时，该列必须有值）；

每个表列或者是null列，或者是not null列，这种状态在创建时由表的定义规定，比如上面的例子；或者表中混合存在null和not null列。

PS：null为默认值，如果不指定not null，则认为指定的是null。

3、主键

主键值必须唯一，即表中的每个行必须具有唯一的主键值。如果主键使用单个列，则它的值必须唯一；如使用多个列，则这些列的组合值必须唯一。

创建表时，主键都用类似primary key的语句定义：primary key（column）；创建由多个列组成的主键，应该以逗号分隔的列表给出各列名（主键可以在创建表时定义，或者在创建表之后定义）。

4、自动增量

例如：cust_id int nut null auto_increment,

auto_increment告诉MySQL，本列每当增加一行时自动增量；每次执行一个insert操作时，MySQL自动对该列增量，给该列赋予下一个可用的值;

每个表只允许一个auto_increment列，而且它必须被索引（比如，通过使它成为主键）

last_insert_id：此函数指示MySQL返回最后一个auto_increment值，然后可将它应用于后续MySQL语句。

5、指定默认值

如果在插入行时没有给出值，MySQL允许指定此时使用的默认值；默认值用create table语句的列定义中的default关键字指定，例如：

create table usertables

(

user_id int not null，

user_name char(50) not null,

user_address char(50) not null,

user_city char(50) not null default shanghai,

user_email char(50) not null,

user_mobile char(50) not null,

primary key (user_id)

)engine=InnoDB;

这条语句中创建包含组成user信息的表，user_city列包含user的所在城市，该列的描述添加了default shanghai，在未给出城市的情况下使用上海（MySQL不允许使用函数作为默认值，它只支持常量）。

6、引擎类型

MySQL有一个具体管理和处理数据的内部引擎，使用create table语句时，该引擎具体创建表；使用select语句或进行其他数据库处理时，该引擎内部处理请求（引擎隐藏在DBMS内，不需要过多关注）。

MySQL相比于其他DBMS的区别在于，它具有多种引擎；因为各个引擎有不同的功能和特性，为不同的任务选择正确的引擎能获得良好的功能和灵活性。

如果省略engine=语句，则使用默认引擎（很可能是MYISAM），但并不是所有情况都这样，所以，engine语句很重要！

常见的几种搜索引擎：

InnoDB:一个可靠的事务处理引擎，不支持全文本搜索；

MEMORY：在功能上等同于MyISAM,数据存储在内存（不是磁盘），速度很快（特别适合临时表）；

MyISAM：一个性能极高的引擎，支持全文本搜索，但不支持事务处理；

PS:引擎类型可以混用，但缺陷在于：外键不能跨引擎，即：使用一个引擎的表不能引用具有使用不同引擎的表的外键。

二、更新表

为更新表定义，可以使用alter table语句（很少使用）；为了更新表结构，必须给出如下信息：

①alter table之后给出要更改的表名（该表必须存在，否则会报错）

②所做更改的列表；

例如：alter table usertable add user_country char(50);

这条SQL语句给usertable表增加了一个user_country的列，必须明确其数据类型。

删除刚添加的列，可以这样：

alter table usertable drop column user_country;

PS:alter table的一种常见用途是定义外键。

如果要多比较复杂的表进行更改，一般需要手动删除过程，涉及的步骤如下：

①用新的列布局创建一个表；

②使用insert select语句从旧表复制数据到新表，如果有必要，可使用转换函数和计算字段；

③检验包含所需数据的新表；

④重命名旧表（如果确定，可以删除它）；

⑤用旧表原来的名字重命名新表；

⑥根据需要，重新创建触发器、存储过程、索引和外键。

三、删除表

删除表（删除整个表而不是其内容），使用drop table语句，例如：

drop table usertable；

这条语句删除usertable表（假设它存在）；删除表没有确认，也不能撤销，执行这条语句将永久删除该表。

还可以重命名表，例如：

rename table usertable to usertablebase；

这条语句用rename table语句重命名一个表；还可以对多个表进行重命名，每个表之间用逗号间隔。

四、全文本搜索

1、启用索引

MySQL支持几种基本的数据库引擎，MySQL最长用的两个引擎为：MyISAM和InnoDB：

MyISAM支持全文本搜索，查询效率高；但局限在于不支持事务和外键；

InnoDB支持事务和外键，和MyISAM各有优劣；

与全文本搜索功能类似的有通配符和正则表达式匹配，但性能较低，通常会匹配表的所有行，而且这些搜索极少使用表索引，不能做到明确控制，且返回的结果不智能化；

在使用全文本搜索时，mysql不需要分别查看每个行，不需要分析和处理每个词，只需索引被搜索的列（需要随着数据的改变不断重新索引）

一般在创建表时启用全文本搜索（必须索引被搜索的列），create table语句接受fulltext子句，它给出被索引列的一个逗号分隔的列表；

例如：

CREATE TABLE productnotes
(
note_id int not null auto_incerement,

prod_id char(10) not null,

note_date datetime not null,

note_text text null,

primary key(note_id),

fulltext(note_text)

) engine = MyISAM;

这条create table语句定义表productnotes并列出它所包含的列即可；其中MySQL根据子句fulltext对（note_text）进行索引。

在定义之后，MySQL自动维护该索引；在增加、删除、或更新行时，索引随之自动更新。

PS：不要再导入数据时使用fulltext，这样有助于更快的导入数据。

2、执行索引

启用索引后，使用match（）和against（）执行全文本搜索；其中match（）指定被搜索的列，against（）指定要使用的搜索表达式。

例如：

select note_text

from productnotes

where match(note_text) against('rabbit');

这条select语句检索单个列note_text，由于where子句，一个全文本搜索被执行；match(note_text)指示MySQL针对指定的列进行搜索，against('rabbit')指定rabbit作为搜索文本。

PS：传递给match（）的值必须与fulltext（）定义中的相同；如果指定多个列，则必须列出它们（次序正确）；除非使用binary方式，否则全文本搜索不区分大小写（上面的例子没有使用该方式）。

like子句具有和全文本搜索相同的功能，但区别在于：全文本搜索的特点是对结果进行排序，具有较高等级的行先返回（如果排序多个搜索项，则包含多数匹配词的行将具有更高的优先级）。

3、查询扩展

作用:用来设法放宽所返回的全文本搜索结果的范围（MySQL对数据和索引进行两遍扫描来完成搜索）。

检索过程：

①进行一个基本的全文本搜索，找出与搜索条件匹配的所有行；

②MySQL检查这些匹配行并选择所有有用的词（将会简要的解释MySQL如何断定什么有用什么无用）；

③MySQL再次进行全文本搜索，这次不仅使用原来的条件，还是用所有有用的词。

用法：where子句中against指定的搜索表达式后跟with query erpansion;

比如：where match(note_text) against('rabbit' with query erpansion);

PS:表中的行越多（行的文本越多），实用查询扩展返回的结果越好。

4、布尔文本搜索

MySQL还支持另一种全文本搜索方式，称为布尔方式（boolean mode）；使用布尔方式需要提供一下条件：

①要匹配的词；

②要排斥的词（如果某行包含这个词，则不返回，即使它包含其他指定的词也是如此）；

③排列提示（指定某些词比其他词重要，更重要的词返回的等级更高）；

④表达式分组；

⑤其他的内容。

PS：即使没有fulltext索引也可以使用布尔方式（但这种方式很缓慢）。

例如：select note_text

from productnotes

where match(note_text) against('heavy -rope*' in bollean mode);

此全文本搜索检索包含词heavy的所有行，其中使用了in boolean mode以及布尔操作符，-rope*指示MySQL排除包含rope*（任何以rope开始的词）的行。

全文本布尔操作符列表如下：

下面列举几个例子，说明这些操作符的使用：

①这个搜索匹配包含次rabbit和biat的行：

select note_text

from froductnotes

where match(note_text) against('+rabbit +bait' in boolean mode);

②没有指定操作符，这个搜索匹配包含rabbit和bait中的至少一个词的行：

select note_text

from froductnotes

where match(note_text) against('rabbit bait' in boolean mode);

③这个搜索匹配短语rabbit bait而不是匹配两个词rabbit和bait：

select note_text

from froductnotes

where match(note_text) against('"rabbit bait"' in boolean mode);

PS：在布尔方式中，不按照等级降序排序返回的行。

5、全文本搜索使用说明

①在索引全文本数据时，短词被忽略且从索引中删除（短词定义为3个或3个以下字符的词：如果需要可以更改）；

②MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本搜索时总被忽略（如果需要，可以覆盖这个列表）；

③MySQL50％规则：如果一个词出现在50％以上的行中，则将它作为一个非用词忽略；50％规则不用于in boolean mode；

④如果表中的行数少于3行，则全文本搜索不返回结果；

⑤忽略词中的单引号，例如don't索引为dont；

⑥不具有词分隔符的语言不能恰当的返回全文本搜索结果；

⑦仅在MyISAM数据库引擎中支持全文本搜索。

(十一)视图及存储过程

一、视图

视图是虚拟的表，它只包含使用时动态检索数据的查询。

1、使用视图的好处

①重用SQL语句；

②简化复杂的SQL操作（可以方便的重用它而不必知道它的基本查询细节）；

③使用表的组成部分而不是整个表；

④保护数据（可以给用户授予表的部分访问权限而不是整个表的访问权限）；

⑤更改数据格式和表示（视图可返回与底层表的表示和格式不同的数据）。

PS：因为视图不包含数据，所以每次使用视图，都必须处理执行所需的任何一个检索；如果使用了多个联结和过滤创建了复杂的视图或嵌套视图，性能下降比较明显。

2、视图的规则和限制

①视图必须唯一命名（视图与别的视图或表不能有相同的名字）；

②创建的视图数目没有限制；

③为了创建视图，必须有足够的访问权限；

④视图可以嵌套；

⑤order by也可用于视图；但如果该视图中检索数据的select语句包含order by，那么该视图中的order by将被覆盖；

⑥视图不能索引，也不能有关联的触发器或默认值；

⑦视图可以好表一起使用。

3、使用视图

create view：创建视图；

show create view viemname：查看创建视图的语句；

drop view viewname:删除视图；

PS：更新视图时，可以先用drop再用create，也可以直接使用create or replace view；如果要更新的视图不存在，则第二条更新语句会创建一个视图，如果视图存在，则第二条更新语句替换原有视图。

3.1简化复杂的联结

视图最常见应用之一就是隐藏复杂的SQL，通常会设计联结；例如：

create view productcustomers AS

select cust_name,cust_conact,prod_id

from customers,orders,orderitems

where customers.cust_id = order.cust_id

and orderitems.order_num = order.order_num;

这条语句创建一个名为productomers的视图，联结三个表，以返回已订购任意产品的所有客户的列表。如果执行select * from productcustomers，将列出订购了任意产品的客户。

为了检索出订购了产品TEST的客户，可如下进行：

select cust_name,cust_contact

from productcustomers

where prod_id = 'TEST';

这条语句通过where子句从视图productcustomers中检索特定的数据。

PS：视图可以极大的简化复杂SQL语句的使用，利用视图，可一次性编写基础的SQL，然后根据需要多次使用。

3.2重新格式化检索出的数据

视图的另一个常用功能就是重新格式化检索出的数据，比如：

create view vendorlocations AS

select concat(RTrim(vend_name),'(',RTrim(vend_country),')')

AS vend_title

from vendors

order by vend_name;

这条SQL语句使用select语句创建视图，在单个组合计算列中返回供应商名和位置，以后每次需要时使用这个视图即可。

3.3使用视图过滤不想要的数据

视图对于普通的where子句也很有用，例如：

create view customeremaillist AS

select cust_id,cust_name,cust_email

from customers

where cust_email is not NULL;

这条SQL语句定义了customeremaillist视图，它过滤没有电子邮件地址的客户。

PS：如果从视图检索数据时使用一条where子句，则两组子句（一组在视图中，另一组是传递给视图的）将自动组合。

3.4视图与计算字段

视图对于简化计算字段来说，也很有用，例如：

create view orderitemsexpanded AS

select order_num,prod_id,quantity,item_price,

quantity*item_price AS expanded_price

from orderitems;

这条语句创建了orderitemsexpanded视图，它检索某个特定订单的物品以及每种物品的总价格，如果需要使用该视图，只需要执行一条select语句即可，比如：

select * from orderitemsexpanded where order_num = 10086;

3.5更新视图

通常来讲，视图是可更新的，更新一个视图即更新其基表（如果MySQL不能正确的确定被更新的基数据，则不允许更新（包括插入和删除））；即视图如果定义如下操作，则不可更新：

①分组（使用group by和having）；②联结；③子查询；④并；⑤聚集函数（min()、count()、sum()等）；⑥distinct；⑦导出列。

二、存储过程

定义：为方便以后使用而保存的一条或多条MySQL语句的集合，可将其视为批文件（虽然其作用不仅限于批处理）。

1、为什么要使用存储过程（简单、安全、高性能）

①通过把处理封装在容易使用的单元中，简化复杂的操作；

②不要求建立一些列处理步骤，保证了数据的完整性；

③简化对变动的管理（如果表名、列名或业务逻辑变更，只需要更改存储过程的代码），这一点的延伸就是安全性；

④提高性能（使用存储过程比使用单独的SQL语句要快）；

2、使用存储过程

①执行存储过程

MySQL中一般将存储过程的执行称为调用，执行的语句用call，call接受存储过程的名字以及传递给它的任意参数；例如下面的例子：

call productpricing(@pricelow,

@pricehigh,

@priceaverage);

这条SQL语句中，执行名为productpricing的存储过程，并计算返回产品的最低、最高和平均价格（存储过程可以显示结果，也可以不显示）。

②创建存储过程

下面创建一个返回产品平均价格的存储过程，例子如下：

create procedure productpricing()

begin

select avg(prod_price) as priceaverage

from products;

end;

此存储过程名为productpricing，用create procedure productpricing()语句定义；如果存储过程接受参数，它将在（）中列举出来，此存储过程没有参数，但后跟的（）仍然需要。

begin和end语句用来限定存储过程体，过程本身仅是一个select语句以及avg（）函数。

注意事项：MySQL命令行客户机的分隔符

默认的MySQL语句分隔符为；。如果命令行实用程序要解释存储过程自身内的；字符，则他们不会成为存储过程的部分，这样会使存储过程中的SQL出现语法错误；解决办法如下：

delimiter//

create procedure productpricing()

begin

select avg(prod_price) as priceaverage

from products;

end//

delimiter;

其中，delimiter//告诉命令行实用程序使用//作为新的语句结束分隔符；如果要恢复原来的分隔符，可使用delimiter（除/之外，任何字符都可以用作语句分隔符）;

③删除存储过程

比如：drop procedure productpricing;

这条语句删除刚才创建的存储过程；注意，后面没有使用（），只给出存储过程名。

PS：如果指定的存储过程不存在，则drop procedure将产生一个错误；当存储过程存在，想删除他时，可以使用drop procedure if exists.

④使用参数

一般来讲，存储过程不显示结果，而是把结果返回给指定的变量（变量内的一个特定的位置，用来临时存储数据）。

下面是上面的存储过程的修改版本：

create procedure productpricing(

out p1 decimal(8,2),

out ph decimal(8,2),

out pa decimal(8,2)

)

begin

select min(pro_price)

into p1

from products;

select max(prod_price)

into ph

from products;

select avg(prod_price)

into pa

from products;

end;

此存储过程中接受3个参数：p1存储产品最低价格，ph存储产品最高价格，pa存储产品平均价格；关键字out指出相应的参数用来从存储过程传出一个值（返回给调用者）；

PS：MySQL支持in（传递给存储过程）、out（从存储过程传出）和inout（对存储过程传入和传出）类型的参数。

为了调用上面修改过的存储过程，必须指定3个变量名，如下：

call productpricing(@pricelow,

@pricehigh,

@priceaverage);

PS：所有存储过程的变量都必须以“@”开始。

调用时候，上面的调用语句并不现实结果，它返回以后可以显示的变量；为了显示检索出的价格，使用下面的语句：

select @pricelow, @pricehigh, @priceaverage;

⑤检查存储过程

为了显示创建存储过程的create语句，使用show create procedure语句，比如：

show create procedure ordertotal;

如果想获得详细的关于存储过程的信息，比如创建时间、创建人等信息，使用show procedure status。

PS:show procedure status列出所有存储过程，为限制其输出，可使用LIKE指定一个过滤模式，例如：

show procedure status like 'ordertotal';

(十二)游标和触发器

一、游标

定义：存储在MySQL服务器上的数据库查询，是一种被select语句检索出来的结果集。

作用：方便在检索出来的结果集中前进或后退一行或多行。

游标主要用于交互式应用；MySQL中的游标只能用于存储过程（和函数）。

1、创建游标

游标使用declare语句创建；declare命名游标，并定义响应的select语句，根据需要带where和其他子句；例如：

create procedure processorders()

begin

declare ordernumbers CURSOR

for

select order_num from orders;

end;

这个存储过程中，declare定义和命名了游标ordernumbers，存储过程处理完成后，游标消失（因为它局限于存储过程内）。

2、打开和关闭游标

游标使用open cursor语句来打开，例如：

open ordernumbers；

在处理open语句时执行查询，存储检索出的数据以供浏览和滚动；

游标处理完成时，使用close语句关闭，例如：

close ordernumbers;

close释放游标使用的所有内部内存和资源，因此在每个游标不在需要时都应该关闭。

PS：一个游标关闭后，如果没有重新打开，则不能使用；但如果该游标被声明过，则不需要再次声明，用open语句打开使用即可。

如果不明确游标是否关闭，MySQL将会在到达end语句时自动关闭该游标；比如：

create procedure processorders()

begin

-- declare the cursor

declare ordernumbers cursor

for

select order_num from orders;

-- open the cursor

open ordernumbers;

-- close the cursor

close ordernumbers;

end;

这个存储过程声明、打开、关闭一个游标，但对检索出的数据没任何操作。

3、使用游标数据

游标被打开后，使用fetch语句分别访问它每一行；fetch指定检索什么数据（所需的列），检索的数据存储在什么地方，还向前移动游标中的内部行指针，使下一条fetch语句检索下一行（不重复读取）；例如：

create procedure processorders()

begin

--declare local variables

declare o int;

--declare the cursor

declare ordernumbers cursor for select order_num from orders;

--open the cursor

open ordernumbers;

--get order number

fetch ordernumbers into o;

--close the cursor

close ordernumbers;

end;

这条语句中fetch用来检索当前行的order_num列（自动从第一行开始）到一个名为o的局部声明变量中；对检索出的数据不做任何处理。

再看一个例子，循环检索数据，从第一行到最后一行：

create procedure processorders() --创建存储过程

begin

--declare local variables

declare done boolean default 0;

declare 0 int;

--declare the cursor

declare ordernumbers cursor

for

select order_num from orders; --结果集

--declare continue handler

declare continue handler for sqlstate '02000' set done=1; --在这里，done被设置为结束时为真

--open the cursor

open ordernumbers;

--loop through all rows

repeat

--get order number

fetch ordernumbers into0;

--end of loop

until done end repeat;

--close the cursor

close ordernumbers;

end;

这个例子与前一个例子一样，使用fetch检索当前order_num到声明的名为0的变量中。但区别在于：这个例子中fetch是在repeat内，因此它反复执行到done为真（由until done end repeat;规定）。

为了使它起作用，用一个default 0（假、不结束）定义变量done；在语句（declare continue handler for sqlstate '02000' set done=1;）中done被设置为真。

PS：用declare语句定义的局部变量必须在定义任意游标或句柄之前定义，而句柄必须在游标之后定义。

MySQL还支持循环语句，它可用来重复执行代码，直到使用leave语句手动退出为止；通常repeat语句的语法使它更适合于对游标进行循环。

4、使用游标的几个注意事项

①在使用游标前，必须声明（定义）它；这个过程实际上没有检索数据，只是定义要使用的select语句；

②一旦声明，则必须打开游标以供使用（这个过程用前面定义的select语句把数据实际检索出来）；

③对于填有数据的游标，根据需要取出（检索）各行；

④结束游标时，必须关闭游标。

二、触发器

MySQL语句在需要时被执行，存储过程也是如此，如果希望某条语句（或某些语句）在事件发生时自动执行，这就需要用到触发器。

触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于begin和end语句之间的一组语句）：

delete；

insert；

update；

其他MySQL语句不支持触发器。。。

1、创建触发器

需要以下4条信息：

①唯一的触发器名；

②触发器关联的表；

③触发器应该响应的活动（delete、insert或update）；

④触发器何时执行（处理之前或之后）；

PS：MySQL中，触发器名必须在每个表中唯一，但不限制每个数据库中唯一，即：同一数据库中两个表可以具有相同名字的触发器（其他的DBMS中不被允许）。

触发器使用create teigger语句创建，下面是一个简单的例子：

create trigger newproduct after insert on products

for each row select 'product added';

这里创建了一个newproduct的新触发器，给出了after insert，所以此触发器在insert语句成功执行后执行；其中还指定了for each row，因此代码对每个插入行执行。

PS：只有表才支持触发器，视图不支持（临时表也不支持）。

触发器按每个表每个事件每次的定义，每个表每个事件每次只允许一个触发器；因此每个表最多支持6个触发器（每条insert、update和delete的之前和之后），单一触发器不能与多个事件或多个表关联。

如果before触发器失败，则MySQL将不执行请求的操作；此外，如果before触发器或语句本身失败，MySQL将不执行after触发器（如果有的话）。

2、删除触发器

删除触发器使用drop trigger语句，例如：

drop trigger newproduct;

PS:触发器不能更新或覆盖；为了修改一个触发器，必须先删后建。

3、使用触发器

insert触发器

使用insert触发器，需要知道以下三点：

①在insert触发器代码内，可引用一个名为new的虚拟表，访问被插入的行；

②在before insert触发器内，new中的值也可以被更新（允许更改被插入的值）；

③对于auto increment列，new在insert执行之前包含0，在insert执行之后包含新的自动生成值；

例子如下：

create trigger neworder after insert on orders

for each row select new.order_num;

此代码创建一个名为neworder的触发器，按照after insert on order执行；在插入一条新的数据orders表时，MySQL生成一个新订单号并保存到order_num中；

触发器从new.order_num取这个值并返回它；此触发器必须按照after insert执行，因为在before insert语句执行之前，新order_num还没生成。

PS：通常将before用于数据验证和净化（目的是保证插入表中的数据确实是需要的数据）。这个规则也适用于update触发器。

delete触发器

使用delete触发器，需要知道以下两点：

①在delete触发器代码内，可以引用一个名为old的虚拟表，访问被删除的行；

②old中的值全都是只读的，不能更新；

下面是一个使用old保存将要被删除的行到一个存档表中：

create trigger deleteorder before delete on orders
for each row
begin
insert into archive_orders(order_num,order_date,cust_id)
values(OLD_order_num, OLD_order_date, OLD_cust_id);
end;

此代码在任意orders表中的数据被删除前执行此触发器；它使用一条insert语句将old中的值（要被删除的数据）保存到一个名为archive_orders的存档表中；

上面的例子中，使用begin end块的好处是触发器能容纳多条SQL语句。

PS：使用before delete触发器的优点：如果由于某些原因，数据不能存档，delete本身将被放弃。

update触发器

使用update触发器，需要知道以下三点：

①在update触发器代码内，可以引用一个名为old的虚拟表访问以前（update语句前）的值，引用一个名为new的虚拟表访问新更新的值；

②在before update触发器中，new中的值可能也被更新（允许更改将要用于update语句中的值）；

③old中的值全都是只读的，不能更新；

下面的例子，保证了州名称缩写总是大写：

create trigger updateevendor before update on vendors

for each row set.vend_state = upper(new.vend_state);

这个例子中，每次更新一个行时，new.vend_state中的值（将用来更新表行的值）都用upper(new.vend_state)替换。

△关于触发器一些必须知道的知识：

①创建触发器可能需要特殊的安全访问权限，但触发器的执行是自动的；如果insert、update、delete语句可以执行，则相应触发器也能执行；

②应该用触发器来保证数据的一致性（大小写、格式等）；有点在于它总是进行这种处理，而且是透明的进行，与客户机应用无关；

③触发器的一种非常有意义的使用是创建审计跟踪。使用触发器，把更改记录到另一个表非常容易；

④MySQL触发器不支持call语句，即不能从触发器内调用存储过程，所需的存储过程代码需要复制到触发器内。

(十三)事务处理和字符集

一、事务处理

事务处理（transaction processing）：是一种机制，用来维护数据库的完整性，管理必须成批执行的MySQL操作，以保证数据库不包含不完整的操作结果。

这样可以保证一组操作不会中途停止，或作为整体执行或完全不执行（除非明确指示）；如果没有发生错误，整组数据写入数据库，如发生错误，则进行回退，以恢复数据库到某个一直且安全的状态。

关于事务处理的几个重要术语：

①事务（transaction）：一组SQL语句

②回退（rollback）：撤销指定SQL语句的过程

③提交（commit）：将未存储的SQL语句结果写入数据库表

④保留点（savepoint）：事务处理中设置的临时占位符（place-holder），可以对它发布回退（与回退整个事务处理不同）

△管理事务处理的关键：将SQL语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应回退。

MySQL使用start transaction标识事务的开始

1、使用rollback

rollback命令用来回退（撤销）MySQL语句，例句如下：

select * from ordertotals;

start transaction;

delete from ordertotals;

select * from ordertotals;

rollback;

select * from ordertotals;

解析：这个例子从显示ordertotals表开始；首先执行一条select语句以显示该表不为空，然后开始一个事务处理，用一条delete语句删除ordertotals中的所有行，另一条select语句

验证ordertotals表确实为空；然后用一条rollback语句回退start transaction之后的所有语句，最后一条select语句显示该表不为空。

PS：rollback只能在一个事务处理内使用（在执行一条start transaction命令之后）。

事务处理用来管理select、insert和update语句；不能回退select语句（这样没意义）；不能回退create或drop操作（即使事务中使用这两条语句，执行回退，它们也不会被撤销）。

2、使用commit

一般MySQL语句都是直接针对数据库表执行和编写，也就是所谓的隐含提交（implicit commit），即提交（写或保存）操作是自动的；但事务处理块是明确的提交，利用commit语句；例句如下：

start transaction;

delete from orderitems where order_num = '20010';

delete from orders where order_num = '20010';

commit;

解析：这个例子中，从系统中完全删除订单20010；因为涉及更新两个数据库表orders和ordersitems，所以使用事务处理块来保证订单不被部分删除；最后的commit语句仅在不出错时写出更改。

如果第一条delete起作用，但第二条失败，则delete不会提交（被自动撤销）。

PS：当commit或rollback语句执行后，事务会自动关闭（将来的更改会隐含提交）。

3、使用保留点

相比rollback和commit来说，复杂的事务处理可能需要部分提交或回退；为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符，如果需要回退，可以回退到某个占位符，这些

占位符称为保留点，为了创建占位符，可以使用savepoint语句，比如：

savepoint deletel;

解析：每个保留点都取标识它的唯一名字，以便回退时MySQL知道要回退到何处，比如为了回退到本例给出的保留点，可以这样：rollback to deletel;

PS:保留点越多越好，这样更为灵活进行回退等操作；保留点在事务处理完成后自动释放，或也可以用release savepoint明确的释放保留点。

4、更改默认提交行为

默认的MySQL行为是自动提交所有更改，即该SQL语句都是针对表执行的，而且立即生效；为了指示MySQL不自动提交更改，可以使用这条语句：set autocommit = 0；

autocommit标志决定是否自动提交更改，不管有没有commit语句；即设置autocommit=0（假）指示MySQL不自动提交更改（直到autocommit被设置为真为止）。

PS：autocommit标志是针对每个联结，而不是服务器。

二、字符集和校对顺序

数据库表被用来存储和检索数据，不同的语言和字符集需要以不同的方式存储和检索。因此MySQL需要适应不同的字符集，适应不同排序和检索数据的方法。

常见的术语：

字符集：字母和符号的组合

编码：为某个字符集成员的内部表示

校对：为规定字符如何比较的指令

1、查看字符集

查看所支持的字符集完整列表，可以使用如下语句：

show character set;

这条语句显示所有可用的字符集以及每个字符集的描述和默认校对。

为查看所支持校对的完整列表，可使用如下语句：

show collation;

此语句显示所有可用的校对，以及它们适用的字符集。

PS:通常系统管理在安装时定义一个默认的字符集合校对，此外也可在创建数据库时，指定默认的字符集合校对；为了确定所用字符集合校对，可使用以下语句：

show variables like 'character%';

show cariables like 'collation%';

PS:事实上，字符集很少是服务器范围（甚至数据库范围）；不同表甚至不同列，都可能需要不同字符集，而且两者都可以在创建表时指定。

2、指定字符集和校对

为了给表指定字符集合校对，可使用如下语句：

create table mytable

(

columnn1 int,

columnn2 varchar(10)

)default character set hebrew

collate hebrew_general_ci;

解析：此语句创建一个包含两列的表，并且指定一个字符集合一个校对顺序。

△MySQL默认使用的字符集和校对：

①如果指定character set和collate两者，则使用这些值；

②如果只指定character set，则使用此字符集及其默认的校对（如show character set的结果中所示）；

③如果既不指定character set，也不指定collate，则使用数据库默认。

PS：MySQL还允许对每个列设置字符集和校对，例句如下：

create table mytable

(

columnn1 int,

columnn2 varchar(10),

columnn3 varchar(10) character set latin1 collate

→ latin1_general_ci

)default character set hebrew

collate hebrew_general_ci;

解析：这里对整个表以及一个特定的列指定了character set和collate；校对在对用order by子句检索出来的数据排序时起重要的作用，如果你需要用与创建表时不同的校对顺序排序特定的select

语句，可以在select语句自身中进行，比如：

select * from customers

order by lastname,firstname collate latinl_general_cs;

这个例子中，select使用collate指定一个备用的校对顺序（为区分大小写的校对），这样很显然将影响到结果排序的次序。

△如果绝对需要，串可以在字符集之间进行转换，使用cast()或 convert()函数。

(十四)管理维护及性能优化

关于MySQL的学习，《MySQL必知必会》这本书呢，看完已经两个月了，一直被工作以及生活的一些琐事拖着，趁着今晚有空闲，就整理完了最后的几章学习笔记，接下来的学习计划呢，

应该是python-web开发以及工作上的关于微服务架构的一些知识点；学习是一种状态，更是一种习惯，只有很努力，才能看起来毫不费力！

一、安全管理

1、访问控制

MySQL服务器的安全基础是：用户对他们需要的数据有适当的访问权，即需要给用户所需的权限且仅提供所需的访问权，这就是所谓的访问控制它需要创建和管理用户账号。

注意事项：慎用root登陆的使用；仅在绝对需要时使用它，日常的MySQL操作中，应该使用我们创建的账号来操作。

2、管理用户

MySQL用户账号和信息存储在名为mysql的MySQL数据库中，一般不需要直接访问，但有时需要直接访问（比如需要获得所有用户账号列表时），为此可以使用如下命令：

USE mysql;

select user from user;

mysql数据库有一个名为user的表，它包含所有用户账号；user表有个名为user的列，它存储用户登录名、

3、用户账号

①创建用户账号

create user ben IDENTIFIED BY 'p@$$wOrd'；

create user创建一个新用户账号，其中IEDNTIFIED BY用来指定一个口令，它指定的口令为纯文本，MySQL将在保存到user表之前对其进行加密；为了作为散列值指定

口令，使用IEDNTIFIED BY PASSWORD。

PS:grant和insert into的区别

grant也可以用来创建用户账号，但一般来说使用create user是最清楚和简单的；此外也可以通过插入行到user表来增加用户，但为了安全起见，不建议这么做。MySQL用来

存储用户账号信息的表极为重要，因此相对于直接处理，使用标记和函数来处理这些表更好。

②重新命名用户账号

rename user ben to imyalost;

③删除用户账号

删除用户账号（以及相关的权限），使用drop user语句，如下：

drop user imyalost;

4、设置访问权限

创建用户账号后，必须分配访问权限（新建的用户账号没有任何访问权限，可以登录但是无法读写）；为了看到赋予账号的权限，使用如下语句：

show grants for imyalost;

用户定义：MySQL的权限用户名和主机名结合定义，如果不指定主机名，则使用默认主机名%（授予用户访问权限而不管主机名）。

设置权限使用grants语句，要求如下：

①要授予的权限

②被授予访问权限的数据库或表

③用户名

grant select on crashcourse.* to imyalost;

此grant允许用户在crashcourse.*（crashcourse数据库的所有表）上使用select，用户imyalost对crashcourse数据库中所有数据拥有只读权限。

每个grant添加（更新）用户的一个权限，MySQL读取所有授权，并根据它们确定权限。

grant的反操作为revoke，用它撤销特定的权限，例如：

revoke select on crashcourse.* from imyalost;

解析：这条revoke语句取消上面的赋予用户imyalost的select权限；被撤销的权限必须存在，否则会出错。

PS：grant和revoke可在几个层次上控制访问权限：

①整个服务器，使用grant all和revoke all;

②整个数据库，使用on database.*;

③特定的表，使用on database.table;

④特定的列;

⑤特定的存储过程;

PS：具体的可授予或撤销的MySQL权限，请自行搜索；

在使用grant和revoke时，用户账号必须存在，但对所涉及的对象没有这个要求，这样的好处是：允许管理员数据库和表之前设计和实现安全措施；缺点在于，

当某个数据库或表被删除时，相关访问权限仍然存在，而且如果将来重新创建数据库或表时，这些权限仍然起作用。

简化多次授权：可以通过列出各权限并用逗号分隔，将多余的grant语句串联，如下所示：

grant select,insert on crashcourse.* to imyalost;

5、更改口令

更改用户口令，可使用set password语句，新口令必须如下加密：

set password for imyalost = password('n3w p@$$w0rd');

解析：set password更新用户口令，新口令必须传到password()函数进行加密。

此外，set password还可以设置自己的口令，比如：

set password = password('n3w p@$$w0rd');

PS:不指定用户名时，set password更新当前登录用户的口令。

二、数据库维护

1、备份数据

MySQL数据也需要经常备份，由于MySQL数据库基于磁盘文件，普通的备份系统就能备份MySQL数据，但由于这些文件总是处于打开和使用状态，普通的文件副本备份不一定有效。

下面列出一些可行的解决方案：

①使用命令行实用程序mysqldump转储所有数据库内容到某个外部文件；在进行常规备份前该程序应该正常运行，以便能正确备份转储文件；

②使用命令行实用程序mysqlhotcopy从一个数据库复制所有数据（并非所有数据库引擎都支持该程序）；

③使用MySQL的backup table或select into outfile转储所有数据到某个外部文件；这两条语句都接受将要创建的系统文件名，此系统文件必须不存在，否则会报错。

数据可用restoretable来复原。

PS：为保证所有数据被写入磁盘（包括索引数据），需要在备份前使用flush tables语句，刷新数据。

2、进行数据库维护

MySQL提供了一系列的语句，可用来保证数据库正确和正常运行，如下：

①analyze table orders;

此语句用来检查表键是否正确。

②check table orders,orderitems;

check table用来针对许多问题进行检查，在MyISAM表上还对索引进行检查。check table支持一系列的用语MyISAM表的方式，如下：

changed检查自最后一次检查以来改动过的表；

extended执行最彻底的检查；fast只检查未正常关闭的表；

medium检查所有被删除的链接并进行键检验；

quick只进行快速扫描。

③optimize table orders;

当从一个表删除大量数据，使用此语句来收回所用的空间，从而优化表的性能。

3、诊断启动问题

服务器启动问题通常在对MySQL配置或服务器本身进行更改时出现，一般在排除系统启动问题时，首先应尽量手动启动服务器；MySQL常用的mysqlid命令行如下：

①- -help显示帮助——————一个选项列表；

②- -safe-mode装载减去某些最佳配置的服务器；

③- -verbose显示全文本消息（为获得更详细的帮助消息与- -help联合使用）；

④- -version显示版本信息然后退出。

4、查看日志文件

MySQL管理员依赖的一系列日志文件，主要的日志文件如下：

①错误日志：包含启动和关闭问题以及任意关键错误的细节。此日志通常名为hostname.err，位于data目录中；此日志名可用- -log-error命令行选项更改；

②查询日志：记录所有的mysql活动，诊断问题时非常有用；由于其可能很快地变得非常大，因此不应长时间使用它；日志名通常为hostname.log，位于data目录中，

此名字可用- -log命令行选项更改；

③二进制日志：记录更新过数据的所有语句。通常名为hostname-bin，位于data目录内，此名字可用- -log-bin命令行选项修改；

④缓慢查询日志：记录执行缓慢的任何查询。在确定数据库何处需要优化很有用，通常名为hostname-slow.log，位于data目录中，可使用- -log-slow-querier命令行更改。

PS:使用日志时，可使用flush logs语句来刷新和重新开始所有日志文件。

三、性能优化

改善数据库性能需要知道的一些关键点：

1、关键的生产DBMS应该运行在自己专用的服务器上；

2、MySQL使用一系列默认的设置预先配置的，但后期很可能需要调整内存分配、缓冲区大小等；

3、MySQL是一个多用户多线程的DBMS，它经常执行多个任务。如果这些任务中某个执行缓慢，则所有的请求都会执行缓慢，如果遇到显著的性能不良，可使用show processlist

显示所有活动进程（以及它们的线程ID和执行时间，还可以用kill命令终结某个特定进程）；

4、编写select语句的方法很多，应多尝试联结、并、子查询等，找出最佳方法；

5、使用explain语句让MySQL解释它将如何执行一条select语句；

6、一般情况下，存储过程执行的比一条一条的执行其中的各条MySQL语句要快；

7、总是使用正确的数据类型；

8、绝不要检索比需要的还多的数据，即：不要使用select *（除非真正需要）；

9、有的操作（包括insert）支持一个可选的delayed关键字，如果使用它，将把控制立即返回给调用程序，并且一旦有可能就实际执行该操作；

10、导入数据时，应关闭自动提交；

11、必须索引数据库表以改善数据库性能，应该分析使用select语句以找出重复的where和order by字句（如果一个简单的where子句返回结果花费时间过长，则可以断定其中

使用的列就是需要索引的对象）；

12、使用多条select语句和连接它们的union语句，可以极大的改善性能；

13、索引改善数据检索的性能，但损害数据插入、删除和更新的性能。如果有些表收集数据但不经常被搜索，则在有必要之前不要索引它们（索引可根据需要添加和删除）；

14、like很慢，一般建议使用fulltext；

15、数据库是不断变化的实体；

16、最重要的规则：每条规则在某些条件下都会被打破。

(十五)Mysql性能调优的10个方法

1. 选择合适的存储引擎: InnoDB

除非你的数据表使用来做只读或者全文检索 (相信现在提到全文检索，没人会用 MYSQL 了)，你应该默认选择 InnoDB 。

你自己在测试的时候可能会发现 MyISAM 比 InnoDB 速度快，这是因为： MyISAM 只缓存索引，而 InnoDB 缓存数据和索引，MyISAM 不支持事务。

但是如果你使用 innodb_flush_log_at_trx_commit = 2 可以获得接近的读取性能 (相差百倍) 。

1.1 如何将现有的 MyISAM 数据库转换为 InnoDB:

mysql -u [USER_NAME] -p -e "SHOW TABLES IN [DATABASE_NAME];" | tail -n +2 | xargs -I '{}' echo "ALTER TABLE {} ENGINE=InnoDB;" > alter_table.sql
perl -p -i -e 's/(search_[a-z_]+ ENGINE=)InnoDB//1MyISAM/g' alter_table.sql
mysql -u [USER_NAME] -p [DATABASE_NAME] < alter_table.sql

1.2 为每个表分别创建 InnoDB FILE：

innodb_file_per_table=1

这样可以保证 ibdata1 文件不会过大，失去控制。尤其是在执行 mysqlcheck -o –all-databases 的时候。

2. 保证从内存中读取数据，讲数据保存在内存中

2.1 足够大的 innodb_buffer_pool_size

推荐将数据完全保存在 innodb_buffer_pool_size ，即按存储量规划 innodb_buffer_pool_size 的容量。这样你可以完全从内存中读取数据，最大限度减少磁盘操作。

2.1.1 如何确定 innodb_buffer_pool_size 足够大，数据是从内存读取而不是硬盘？

方法 1

mysql> SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| Innodb_buffer_pool_pages_data    | 129037 |
| Innodb_buffer_pool_pages_dirty   | 362    |
| Innodb_buffer_pool_pages_flushed | 9998   |
| Innodb_buffer_pool_pages_free    | 0      |  !!!!!!!!
| Innodb_buffer_pool_pages_misc    | 2035   |
| Innodb_buffer_pool_pages_total   | 131072 |
+----------------------------------+--------+
6 rows in set (0.00 sec)

发现 Innodb_buffer_pool_pages_free 为 0，则说明 buffer pool 已经被用光，需要增大 innodb_buffer_pool_size

InnoDB 的其他几个参数：

innodb_additional_mem_pool_size = 1/200 of buffer_pool
innodb_max_dirty_pages_pct 80%

方法 2

或者用iostat -d -x -k 1 命令，查看硬盘的操作。

2.1.2 服务器上是否有足够内存用来规划

执行 echo 1 > /proc/sys/vm/drop_caches 清除操作系统的文件缓存，可以看到真正的内存使用量。

2.2 数据预热

默认情况，只有某条数据被读取一次，才会缓存在 innodb_buffer_pool。所以，数据库刚刚启动，需要进行数据预热，将磁盘上的所有数据缓存到内存中。数据预热可以提高读取速度。

对于 InnoDB 数据库，可以用以下方法，进行数据预热:

1. 将以下脚本保存为 MakeSelectQueriesToLoad.sql

SELECT DISTINCT
    CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,
    ' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
    FROM
    (
        SELECT
            engine,table_schema db,table_name tb,
            index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
        FROM
        (
            SELECT
                B.engine,A.table_schema,A.table_name,
                A.index_name,A.column_name,A.seq_in_index
            FROM
                information_schema.statistics A INNER JOIN
                (
                    SELECT engine,table_schema,table_name
                    FROM information_schema.tables WHERE
                    engine='InnoDB'
                ) B USING (table_schema,table_name)
            WHERE B.table_schema NOT IN ('information_schema','mysql')
            ORDER BY table_schema,table_name,index_name,seq_in_index
        ) A
        GROUP BY table_schema,table_name,index_name
    ) AA
ORDER BY db,tb
;

2. 执行

mysql -uroot -AN < /root/MakeSelectQueriesToLoad.sql > /root/SelectQueriesToLoad.sql

3. 每次重启数据库，或者整库备份前需要预热的时候执行：

mysql -uroot < /root/SelectQueriesToLoad.sql > /dev/null 2>&1

2.3 不要让数据存到 SWAP 中

如果是专用 MYSQL 服务器，可以禁用 SWAP，如果是共享服务器，确定 innodb_buffer_pool_size 足够大。或者使用固定的内存空间做缓存，使用 memlock 指令。

3. 定期优化重建数据库

mysqlcheck -o –all-databases 会让 ibdata1 不断增大，真正的优化只有重建数据表结构：

CREATE TABLE mydb.mytablenew LIKE mydb.mytable;
INSERT INTO mydb.mytablenew SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytablezap;
ALTER TABLE mydb.mytablenew RENAME mydb.mytable;
DROP TABLE mydb.mytablezap;

4. 减少磁盘写入操作

4.1 使用足够大的写入缓存 innodb_log_file_size

但是需要注意如果用 1G 的 innodb_log_file_size ，假如服务器当机，需要 10 分钟来恢复。

推荐 innodb_log_file_size 设置为 0.25 * innodb_buffer_pool_size

4.2 innodb_flush_log_at_trx_commit

这个选项和写磁盘操作密切相关：

innodb_flush_log_at_trx_commit = 1 则每次修改写入磁盘
innodb_flush_log_at_trx_commit = 0/2 每秒写入磁盘

如果你的应用不涉及很高的安全性 (金融系统)，或者基础架构足够安全，或者事务都很小，都可以用 0 或者 2 来降低磁盘操作。

4.3 避免双写入缓冲

innodb_flush_method=O_DIRECT

5. 提高磁盘读写速度

RAID0 尤其是在使用 EC2 这种虚拟磁盘 (EBS) 的时候，使用软 RAID0 非常重要。

6. 充分使用索引

6.1 查看现有表结构和索引

SHOW CREATE TABLE db1.tb1/G

6.2 添加必要的索引

索引是提高查询速度的唯一方法，比如搜索引擎用的倒排索引是一样的原理。

索引的添加需要根据查询来确定，比如通过慢查询日志或者查询日志,或者通过 EXPLAIN 命令分析查询。

ADD UNIQUE INDEX
ADD INDEX

6.2.1 比如，优化用户验证表：

添加索引

ALTER TABLE users ADD UNIQUE INDEX username_ndx (username);
ALTER TABLE users ADD UNIQUE INDEX username_password_ndx (username,password);

每次重启服务器进行数据预热

echo “select username,password from users;” > /var/lib/mysql/upcache.sql

添加启动脚本到 my.cnf

[mysqld]
init-file=/var/lib/mysql/upcache.sql

6.2.2 使用自动加索引的框架或者自动拆分表结构的框架

比如，Rails 这样的框架，会自动添加索引，Drupal 这样的框架会自动拆分表结构。会在你开发的初期指明正确的方向。所以，经验不太丰富的人一开始就追求从 0 开始构建，实际是不好的做法。

7. 分析查询日志和慢查询日志

记录所有查询，这在用 ORM 系统或者生成查询语句的系统很有用。

log=/var/log/mysql.log

注意不要在生产环境用，否则会占满你的磁盘空间。

记录执行时间超过 1 秒的查询：

long_query_time=1
log-slow-queries=/var/log/mysql/log-slow-queries.log

8. 激进的方法，使用内存磁盘

现在基础设施的可靠性已经非常高了，比如 EC2 几乎不用担心服务器硬件当机。而且内存实在是便宜，很容易买到几十G内存的服务器，可以用内存磁盘，定期备份到磁盘。

将 MYSQL 目录迁移到 4G 的内存磁盘

mkdir -p /mnt/ramdisk
sudo mount -t tmpfs -o size=4000M tmpfs /mnt/ramdisk/
mv /var/lib/mysql /mnt/ramdisk/mysql
ln -s /tmp/ramdisk/mysql /var/lib/mysql
chown mysql:mysql mysql

9. 用 NOSQL 的方式使用 MYSQL

B-TREE 仍然是最高效的索引之一，所有 MYSQL 仍然不会过时。

用 HandlerSocket 跳过 MYSQL 的 SQL 解析层，MYSQL 就真正变成了 NOSQL。

10. 其他

单条查询最后增加 LIMIT 1，停止全表扫描。
将非”索引”数据分离，比如将大篇文章分离存储，不影响其他自动查询。
不用 MYSQL 内置的函数，因为内置函数不会建立查询缓存。
PHP 的建立连接速度非常快，所有可以不用连接池，否则可能会造成超过连接数。当然不用连接池 PHP 程序也可能将
连接数占满比如用了 @ignore_user_abort(TRUE);
使用 IP 而不是域名做数据库路径，避免 DNS 解析问题

11. 结束

你会发现优化后，数据库的性能提高几倍到几百倍。所以 MYSQL 基本还是可以适用大部分场景的应用的。优化现有系统的成本比系统重构或者迁移到 NOSQL 低很多。

最后，附一张构建高性能MySQL体系的思维导图：

(十六)Sql语句大全

一、基础

1、说明：创建数据库

CREATE DATABASE database-name

2、说明：删除数据库

drop database dbname

3、说明：备份sql server

--- 创建备份数据的 device

USE master

EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'

--- 开始备份

BACKUP DATABASE pubs TO testBack

4、说明：创建新表

create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

根据已有的表创建新表：

A：create table tab_new like tab_old (使用旧表创建新表)

B：create table tab_new as select col1,col2… from tab_old definition only

5、说明：删除新表

drop table tabname

6、说明：增加一个列

Alter table tabname add column col type

注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。

7、说明：添加主键： Alter table tabname add primary key(col)

说明：删除主键： Alter table tabname drop primary key(col)

8、说明：创建索引：create [unique] index idxname on tabname(col….)

删除索引：drop index idxname

注：索引是不可更改的，想更改必须删除重新建。

9、说明：创建视图：create view viewname as select statement

删除视图：drop view viewname

10、说明：几个简单的基本的sql语句

选择：select * from table1 where 范围

插入：insert into table1(field1,field2) values(value1,value2)

删除：delete from table1 where 范围

更新：update table1 set field1=value1 where 范围

查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!

排序：select * from table1 order by field1,field2 [desc]

总数：select count as totalcount from table1

求和：select sum(field1) as sumvalue from table1

平均：select avg(field1) as avgvalue from table1

最大：select max(field1) as maxvalue from table1

最小：select min(field1) as minvalue from table1

11、说明：几个高级查询运算词

A： UNION 运算符

UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。

两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。

B： EXCEPT 运算符

EXCEPT运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。

C： INTERSECT 运算符

INTERSECT运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。

注：使用运算词的几个查询结果行必须是一致的。

12、说明：使用外连接

A、left （outer） join：

左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

B：right （outer） join:

右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。

C：full/cross （outer） join：

全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。

12、分组:Group by:

一张表，一旦分组完成后，查询后只能得到组相关的信息。

组相关的信息：（统计信息） count,sum,max,min,avg 分组的标准)

在SQLServer中分组时：不能以text,ntext,image类型的字段作为分组依据

在selecte统计函数中的字段，不能和普通的字段放在一起；

13、对数据库进行操作：

分离数据库： sp_detach_db;附加数据库：sp_attach_db 后接表明，附加需要完整的路径名

14.如何修改数据库的名称:

sp_renamedb 'old_name', 'new_name'

二、提升

1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)

法一：select * into b from a where 1<>1（仅用于SQlServer）

法二：select top 0 * into b from a

2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)

insert into b(a, b, c) select d,e,f from b;

3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)

insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件

例子：..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..

4、说明：子查询(表名1：a 表名2：b)

select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)

5、说明：显示文章、提交人和最后回复时间

select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b

6、说明：外连接查询(表名1：a 表名2：b)

select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

7、说明：在线视图查询(表名1：a )

select * from (SELECT a,b,c FROM a) T where t.a > 1;

8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括

select * from table1 where time between time1 and time2

select a,b,c, from table1 where a not between 数值1 and 数值2

9、说明：in 的使用方法

select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)

10、说明：两张关联表，删除主表中已经在副表中没有的信息

delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )

11、说明：四表联查问题：

select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....

12、说明：日程安排提前五分钟提醒

SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5

13、说明：一条sql 语句搞定数据库分页

select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段

具体实现：

关于数据库分页：

declare @start int,@end int

@sql nvarchar(600)

set @sql=’select top’+str(@end-@start+1)+’+from T where rid not in(select top’+str(@str-1)+’Rid from T where Rid>-1)’

exec sp_executesql @sql

注意：在top后不能直接跟一个变量，所以在实际应用中只有这样的进行特殊的处理。Rid为一个标识列，如果top后还有具体的字段，这样做是非常有好处的。因为这样可以避免top的字段

如果是逻辑索引的，查询的结果后实际表中的不一致（逻辑索引中的数据有可能和数据表中的不一致，而查询时如果处在索引则首先查询索引）

14、说明：前10条记录

select top 10 * form table1 where 范围

15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)

select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)

16、说明：包括所有在 TableA中但不在 TableB和TableC中的行并消除所有重复行而派生出一个结果表

(select a from tableA ) except (select a from tableB) except (select a from tableC)

17、说明：随机取出10条数据

select top 10 * from tablename order by newid()

18、说明：随机选择记录

select newid()

19、说明：删除重复记录

1),delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)

2),select distinct * into temp from tablename

delete from tablename

insert into tablename select * from temp

评价：这种操作牵连大量的数据的移动，这种做法不适合大容量但数据操作

3),例如：在一个外部表中导入数据，由于某些原因第一次只导入了一部分，但很难判断具体位置，这样只有在下一次全部导入，这样也就产生好多重复的字段，怎样删除重复字段

alter table tablename

--添加一个自增列

add column_b int identity(1,1)

delete from tablename where column_b not in(

select max(column_b) from tablename group by column1,column2,...)

alter table tablename drop column column_b

20、说明：列出数据库里所有的表名

select name from sysobjects where type='U' // U代表用户

21、说明：列出表里的所有的列名

select name from syscolumns where id=object_id('TableName')

22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。

select type,sum(case vender when 'A' then pcs else 0 end),

sum(case vender when 'C' then pcs else 0 end),

sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type

显示结果：

type vender pcs

电脑 A 1

光盘 B 2

光盘 A 2

手机 B 3

手机 C 3

23、说明：初始化表table1

TRUNCATE TABLE table1

24、说明：选择从10到15的记录

select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc

三、技巧

1、1=1，1=2的使用，在SQL语句组合时用的较多

“where 1=1” 是表示选择全部 “where 1=2”全部不选，如：

if @strWhere !=''

begin

set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere

end

else

begin

set @strSQL = 'select count(*) as Total from [' + @tblName + ']'

end

我们可以直接写成

错误！未找到目录项。

set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere 2、收缩数据库

--重建索引

DBCC REINDEX

DBCC INDEXDEFRAG

--收缩数据和日志

DBCC SHRINKDB

DBCC SHRINKFILE

3、压缩数据库

dbcc shrinkdatabase(dbname)

4、转移数据库给新用户以已存在用户权限

exec sp_change_users_login 'update_one','newname','oldname'

5、检查备份集

RESTORE VERIFYONLY from disk='E:\dvbbs.bak'

6、修复数据库

ALTER DATABASE [dvbbs] SET SINGLE_USER

DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK

ALTER DATABASE [dvbbs] SET MULTI_USER

7、日志清除

SET NOCOUNT ON

DECLARE @LogicalFileName sysname,

@MaxMinutes INT,

@NewSize INT

USE tablename -- 要操作的数据库名

SELECT @LogicalFileName = 'tablename_log', -- 日志文件名

@MaxMinutes = 10, -- Limit on time allowed to wrap log.

@NewSize = 1 -- 你想设定的日志文件的大小(M)

Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)

DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'

DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE   ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') DELETE DummyTrans
SELECT @Counter = @Counter + 1
END
EXEC (@TruncLog)
END
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF

8、说明：更改某个表

exec sp_changeobjectowner 'tablename','dbo'

9、存储更改全部表

CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
@OldOwner as NVARCHAR(128),
@NewOwner as NVARCHAR(128)
AS

DECLARE @Name    as NVARCHAR(128)
DECLARE @Owner   as NVARCHAR(128)
DECLARE @OwnerName   as NVARCHAR(128)

DECLARE curObject CURSOR FOR
select 'Name' = name,
'Owner' = user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner
order by name

OPEN   curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN
if @Owner=@OldOwner
begin
   set @OwnerName = @OldOwner + '.' + rtrim(@Name)
   exec sp_changeobjectowner @OwnerName, @NewOwner
end
-- select @name,@NewOwner,@OldOwner

FETCH NEXT FROM curObject INTO @Name, @Owner
END

close curObject
deallocate curObject
GO

10、SQL SERVER中直接循环写入数据

declare @i int
set @i=1
while @i<30
begin
insert into test (userid) values(@i)
set @i=@i+1
end
案例：
有如下表，要求就裱中所有沒有及格的成績，在每次增長0.1的基礎上，使他們剛好及格:

Name score

Zhangshan 80

Lishi 59

Wangwu 50

Songquan 69

while((select min(score) from tb_table)<60)

begin

update tb_table set score =score*1.01

where score<60

if (select min(score) from tb_table)>60

break

else

continue

end

四、数据开发-经典

1.按姓氏笔画排序:

Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as //从少到多

2.数据库加密:

select encrypt('原始密码')

select pwdencrypt('原始密码')

select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同 encrypt('原始密码')

select pwdencrypt('原始密码')

select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同

3.取回表中字段:

declare @list varchar(1000),

@sql nvarchar(1000)

select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='表A'

set @sql='select '+right(@list,len(@list)-1)+' from 表A'

exec (@sql)

4.查看硬盘分区:

EXEC master..xp_fixeddrives

5.比较A,B表是否相等:

if (select checksum_agg(binary_checksum(*)) from A)
=
(select checksum_agg(binary_checksum(*)) from B)

print '相等'

else

print '不相等'

6.杀掉所有的事件探察器进程:

DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) FROM master.dbo.sysprocesses
WHERE program_name IN('SQL profiler',N'SQL 事件探查器')
EXEC sp_msforeach_worker '?'

7.记录搜索:

开头到N条记录

Select Top N * From 表
-------------------------------
N到M条记录(要有主索引ID)

Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID Desc
----------------------------------
N到结尾记录

Select Top N * From 表 Order by ID Desc

案例

例如1：一张表有一万多条记录，表的第一个字段 RecID 是自增长字段，写一个SQL语句，找出表的第31到第40个记录。

select top 10 recid from A where recid not in(select top 30 recid from A)

分析：如果这样写会产生某些问题，如果recid在表中存在逻辑索引。

select top 10 recid from A where……是从索引中查找，而后面的select top 30 recid from A则在数据表中查找，这样由于索引中的顺序有可能和

数据表中的不一致，这样就导致查询到的不是本来的欲得到的数据。

五、解决方案

1，用order by select top 30 recid from A order by ricid 如果该字段不是自增长，就会出现问题

2，在那个子查询中也加条件：select top 30 recid from A where recid>-1

例2：查询表中的最后以条记录，并不知道这个表共有多少数据,以及表结构。

set @s = 'select top 1 * from T where pid not in (select top ' + str(@count-1) + ' pid from T)'

print @s exec sp_executesql @s

9：获取当前数据库中的所有用户表

select Name from sysobjects where xtype='u' and status>=0

10：获取某一个表的所有字段

select name from syscolumns where id=object_id('表名')

select name from syscolumns where id in (select id from sysobjects where type = 'u' and name = '表名')

两种方式的效果相同

11：查看与某一个表相关的视图、存储过程、函数

select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'

12：查看当前数据库中所有存储过程

select name as 存储过程名称 from sysobjects where xtype='P'

13：查询用户创建的所有数据库

select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')

或者

select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01

14：查询某一个表的字段和数据类型

select column_name,data_type from information_schema.columns

where table_name = '表名'

15：不同服务器数据库之间的数据操作

--创建链接服务器

exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '

exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用户名 ', '密码 '

--查询示例

select * from ITSV.数据库名.dbo.表名

--导入示例

select * into 表 from ITSV.数据库名.dbo.表名

--以后不再使用时删除链接服务器

exec sp_dropserver 'ITSV ', 'droplogins '

--连接远程/局域网数据(openrowset/openquery/opendatasource)

--1、openrowset

--查询示例

select * from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)

--生成本地表

select * into 表 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)

--把本地表导入远程表

insert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)

select *from 本地表

--更新本地表

update b

set b.列A=a.列A

from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 b

on a.column1=b.column1

--openquery用法需要创建一个连接

--首先创建一个连接创建链接服务器

exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '

--查询

select * FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')

--把本地表导入远程表

insert openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')

select * from 本地表

--更新本地表

update b

set b.列B=a.列B

FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') as a

inner join 本地表 b on a.列A=b.列A

--3、opendatasource/openrowset

SELECT * FROM opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta

--把本地表导入远程表

insert opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ').数据库.dbo.表名

select * from 本地表

六、SQL Server基本函数

SQL Server基本函数

1.字符串函数 长度与分析用

1,datalength(Char_expr) 返回字符串包含字符数,但不包含后面的空格

2,substring(expression,start,length) 取子串，字符串的下标是从“1”，start为起始位置，length为字符串长度，实际应用中以len(expression)取得其长度

3,right(char_expr,int_expr) 返回字符串右边第int_expr个字符，还用left于之相反

4,isnull( check_expression , replacement_value )如果check_expression為空，則返回replacement_value的值，不為空，就返回check_expression字符操作类

5,Sp_addtype自定義數據類型

例如：EXEC sp_addtype birthday, datetime, 'NULL'

6,set nocount {on|off}

使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。如果存储过程中包含的一些语句并不返回许多实际的数据，则该设置由于大量减少了网络流量，因此可显著提高性能。

SET NOCOUNT 设置是在执行或运行时设置，而不是在分析时设置。

SET NOCOUNT 为 ON 时，不返回计数（表示受 Transact-SQL 语句影响的行数）。

SET NOCOUNT 为 OFF 时，返回计数

常识

在SQL查询中：from后最多可以跟多少张表或视图：256

在SQL语句中出现 Order by,查询时，先排序，后取

在SQL中，一个字段的最大容量是8000，而对于nvarchar(4000),由于nvarchar是Unicode码。

SQLServer2000同步复制技术实现步骤

一、 预备工作

1.发布服务器,订阅服务器都创建一个同名的windows用户,并设置相同的密码,做为发布快照文件夹的有效访问用户

--管理工具

--计算机管理

--用户和组

--右键用户

--新建用户

--建立一个隶属于administrator组的登陆windows的用户（SynUser）

2.在发布服务器上,新建一个共享目录,做为发布的快照文件的存放目录,操作:

我的电脑--D:\ 新建一个目录,名为: PUB

--右键这个新建的目录

--属性--共享

--选择"共享该文件夹"

--通过"权限"按纽来设置具体的用户权限,保证第一步中创建的用户(SynUser) 具有对该文件夹的所有权限

--确定

3.设置SQL代理(SQLSERVERAGENT)服务的启动用户(发布/订阅服务器均做此设置)

开始--程序--管理工具--服务

--右键SQLSERVERAGENT

--属性--登陆--选择"此账户"

--输入或者选择第一步中创建的windows登录用户名（SynUser）

--"密码"中输入该用户的密码

4.设置SQL Server身份验证模式,解决连接时的权限问题(发布/订阅服务器均做此设置)

企业管理器

--右键SQL实例--属性

--安全性--身份验证

--选择"SQL Server 和 Windows"

--确定

5.在发布服务器和订阅服务器上互相注册

企业管理器

--右键SQL Server组

--新建SQL Server注册...

--下一步--可用的服务器中,输入你要注册的远程服务器名 --添加

--下一步--连接使用,选择第二个"SQL Server身份验证"

--下一步--输入用户名和密码（SynUser）

--下一步--选择SQL Server组,也可以创建一个新组

--下一步--完成

6.对于只能用IP,不能用计算机名的,为其注册服务器别名（此步在实施中没用到）

 (在连接端配置,比如,在订阅服务器上配置的话,服务器名称中输入的是发布服务器的IP)

开始--程序--Microsoft SQL Server--客户端网络实用工具

--别名--添加

--网络库选择"tcp/ip"--服务器别名输入SQL服务器名

--连接参数--服务器名称中输入SQL服务器ip地址

--如果你修改了SQL的端口,取消选择"动态决定端口",并输入对应的端口号

二、 正式配置

1、配置发布服务器

打开企业管理器，在发布服务器（B、C、D）上执行以下步骤:

(1) 从[工具]下拉菜单的[复制]子菜单中选择[配置发布、订阅服务器和分发]出现配置发布和分发向导

(2) [下一步] 选择分发服务器 可以选择把发布服务器自己作为分发服务器或者其他sql的服务器（选择自己）

(3) [下一步] 设置快照文件夹

采用默认\\servername\Pub

(4) [下一步] 自定义配置

可以选择:是,让我设置分发数据库属性启用发布服务器或设置发布设置

否,使用下列默认设置（推荐）

(5) [下一步] 设置分发数据库名称和位置 采用默认值

(6) [下一步] 启用发布服务器 选择作为发布的服务器

(7) [下一步] 选择需要发布的数据库和发布类型

(8) [下一步] 选择注册订阅服务器

(9) [下一步] 完成配置

2、创建出版物

发布服务器B、C、D上

(1)从[工具]菜单的[复制]子菜单中选择[创建和管理发布]命令

(2)选择要创建出版物的数据库，然后单击[创建发布]

(3)在[创建发布向导]的提示对话框中单击[下一步]系统就会弹出一个对话框。对话框上的内容是复制的三个类型。我们现在选第一个也就是默认的快照发布(其他两个大家可以去看看帮助)

(4)单击[下一步]系统要求指定可以订阅该发布的数据库服务器类型,

SQLSERVER允许在不同的数据库如 orACLE或ACCESS之间进行数据复制。

但是在这里我们选择运行"SQL SERVER 2000"的数据库服务器

(5)单击[下一步]系统就弹出一个定义文章的对话框也就是选择要出版的表

注意: 如果前面选择了事务发布 则再这一步中只能选择带有主键的表

(6)选择发布名称和描述

(7)自定义发布属性 向导提供的选择:

是 我将自定义数据筛选,启用匿名订阅和或其他自定义属性

否 根据指定方式创建发布 （建议采用自定义的方式）

(8)[下一步] 选择筛选发布的方式

(9)[下一步] 可以选择是否允许匿名订阅

1)如果选择署名订阅,则需要在发布服务器上添加订阅服务器

方法: [工具]->[复制]->[配置发布、订阅服务器和分发的属性]->[订阅服务器] 中添加

否则在订阅服务器上请求订阅时会出现的提示:改发布不允许匿名订阅

如果仍然需要匿名订阅则用以下解决办法

[企业管理器]->[复制]->[发布内容]->[属性]->[订阅选项] 选择允许匿名请求订阅

2)如果选择匿名订阅,则配置订阅服务器时不会出现以上提示

(10)[下一步] 设置快照 代理程序调度

(11)[下一步] 完成配置

当完成出版物的创建后创建出版物的数据库也就变成了一个共享数据库

有数据

srv1.库名..author有字段:id,name,phone,

srv2.库名..author有字段:id,name,telphone,adress

要求：

srv1.库名..author增加记录则srv1.库名..author记录增加

srv1.库名..author的phone字段更新，则srv1.库名..author对应字段telphone更新

--*/

--大致的处理步骤

--1.在 srv1 上创建连接服务器,以便在 srv1 中操作 srv2,实现同步

exec sp_addlinkedserver 'srv2','','SQLOLEDB','srv2的sql实例名或ip'

exec sp_addlinkedsrvlogin 'srv2','false',null,'用户名','密码'

go

--2.在 srv1 和 srv2 这两台电脑中,启动 msdtc(分布式事务处理服务),并且设置为自动启动

。我的电脑--控制面板--管理工具--服务--右键 Distributed Transaction Coordinator--属性--启动--并将启动类型设置为自动启动

go

--然后创建一个作业定时调用上面的同步处理存储过程就行了

企业管理器

--管理

--SQL Server代理

--右键作业

--新建作业

--"常规"项中输入作业名称

--"步骤"项

--新建

--"步骤名"中输入步骤名

--"类型"中选择"Transact-SQL 脚本(TSQL)"

--"数据库"选择执行命令的数据库

--"命令"中输入要执行的语句: exec p_process

--确定

--"调度"项

--新建调度

--"名称"中输入调度名称

--"调度类型"中选择你的作业执行安排

--如果选择"反复出现"

--点"更改"来设置你的时间安排

然后将SQL Agent服务启动,并设置为自动启动,否则你的作业不会被执行

设置方法:

我的电脑--控制面板--管理工具--服务--右键 SQLSERVERAGENT--属性--启动类型--选择"自动启动"--确定.

--3.实现同步处理的方法2,定时同步

--在srv1中创建如下的同步处理存储过程

create proc p_process

as

--更新修改过的数据

update b set name=i.name,telphone=i.telphone

from srv2.库名.dbo.author b,author i

where b.id=i.id and

(b.name <> i.name or b.telphone <> i.telphone)

--插入新增的数据

insert srv2.库名.dbo.author(id,name,telphone)

select id,name,telphone from author i

where not exists(

select * from srv2.库名.dbo.author where id=i.id)

--删除已经删除的数据(如果需要的话)

delete b

from srv2.库名.dbo.author b

where not exists(

select * from author where id=b.id)

go



转载：https://www.cnblogs.com/imyalost/default.html

posted @ 2019-05-23 13:54 少爺我心高阅读(771) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

少爺我心高

MySql菜鸟入门系列

MySql

(一)MySql基础介绍

(二)数据的检索和过滤

(三)用正则表达式搜索

(四)字段及常用函数

(五)汇总和分组数据

(六)常用语法和数据类型

(七)联结表

(八)子查询和分组查询

(九)插入，更新，删除

(十)操作表及全文本搜索

(十一)视图及存储过程

(十二)游标和触发器

(十三)事务处理和字符集

(十四)管理维护及性能优化

(十五)Mysql性能调优的10个方法

1. 选择合适的存储引擎: InnoDB

1.1 如何将现有的 MyISAM 数据库转换为 InnoDB:

1.2 为每个表分别创建 InnoDB FILE：

innodb_file_per_table=1

2. 保证从内存中读取数据，讲数据保存在内存中

2.1 足够大的 innodb_buffer_pool_size

2.1.1 如何确定 innodb_buffer_pool_size 足够大，数据是从内存读取而不是硬盘？

2.1.2 服务器上是否有足够内存用来规划

2.2 数据预热

2.3 不要让数据存到 SWAP 中

3. 定期优化重建数据库

4. 减少磁盘写入操作

4.1 使用足够大的写入缓存 innodb_log_file_size

4.2 innodb_flush_log_at_trx_commit

4.3 避免双写入缓冲

5. 提高磁盘读写速度

6. 充分使用索引

6.1 查看现有表结构和索引

6.2.1 比如，优化用户验证表：

6.2.2 使用自动加索引的框架或者自动拆分表结构的框架

7. 分析查询日志和慢查询日志

8. 激进的方法，使用内存磁盘

9. 用 NOSQL 的方式使用 MYSQL

10. 其他

11. 结束

(十六)Sql语句大全

公告