澳门游艺场9159-9159金沙游戏场

数据库操作,类似需求用SSIS或者其他ETL工作很容

作者: 澳门游艺场  发布:2020-03-31

缘由

数据库概述 / Database Overview


 

1 关于SQL / About SQL

构化查询语言(Structured Query Language)简称SQL,是一种特殊目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统;同时也是数据库脚本文件的扩展名。

结构化查询语言是高级的非过程化编程语言,允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法,也不需要用户了解具体的数据存放方式,所以具有完全不同底层结构的不同数据库系统, 可以使用相同的结构化查询语言作为数据输入与管理的接口。结构化查询语言语句可以嵌套,这使它具有极大的灵活性和强大的功能。

1.1 SQL语言 / SQL Language

结构化查询语言包含6个部分:

1. 数据查询语言(DQL: Data Query Language

其语句,也称为“数据检索语句”,用以从表中获得数据,确定数据怎样在应用程序给出。保留字SELECTDQL(也是所有SQL)用得最多的动词,其他DQL常用的保留字有WHERE,ORDER BY,GROUP BY和HAVING。这些DQL保留字常与其他类型的SQL语句一起使用。

2. 数据操作语言(DML: Data Manipulation Language

其语句包括动词INSERT, UPDATEDELETE。它们分别用于添加,修改和删除表中的行。也称为动作查询语言。

3. 事务处理语言(TPL

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

4. 数据控制语言(DCL

它的语句通过GRANTREVOKE获得许可,确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。

5. 数据定义语言(DDL

其语句包括动词CREATEDROP。在数据库中创建新表或删除表(CREAT TABLE 或 DROP TABLE);为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。

6. 指针控制语言(CCL

它的语句,像DECLARE CURSOR, FETCH INTOUPDATE WHERE CURRENT用于对一个或多个表单独行的操作

 

1.2 SQL数据类型 / SQL Data

结构化查询语言共有五种数据类型,字符型文本型数值型逻辑型日期型

1. 字符型

**VARCHAR VS CHAR**

VARCHAR型和CHAR型数据的这个差别是细微的,但是非常重要。他们都是用来储存字符串长度小于255的字符。

假如你向一个长度为四十个字符的VARCHAR型字段中输入数据Bill Gates。当你以后从这个字段中取出此数据时,你取出的数据其长度为十个字符——字符串Bill Gates的长度。假如你把字符串输入一个长度为四十个字符的CHAR型字段中,那么当你取出数据时,所取出的数据长度将是四十个字符。字符串的后面会被附加多余的空格

当你建立自己的站点时,你会发现使用VARCHAR型字段要比CHAR型字段方便的多。使用VARCHAR型字段时,你不需要为剪掉你数据中多余的空格而操心。

VARCHAR型字段的另一个突出的好处是它可以比CHAR型字段占用更少的内存和硬盘空间。当你的数据库很大时,这种内存和磁盘空间的节省会变得非常重要。

2. 文本型 TEXT

使用文本型数据,你可以存放超过二十亿个字符的字符串。当你需要存储大串的字符时,应该使用文本型数据。

注意文本型数据没有长度,而上一节中所讲的字符型数据是有长度的。一个文本型字段中的数据通常要么为空,要么很大。

当你从HTML FORM的多行文本编辑框(TEXTAREA)中收集数据时,你应该把收集的信息存储于文本型字段中。但是,无论何时,只要你能避免使用文本型字段,你就应该不使用它。文本型字段既大且慢,滥用文本型字段会使服务器速度变慢。文本型字段还会吃掉大量的磁盘空间

一旦你向文本型字段中输入了任何数据(甚至是空值),就会有2K的空间被自动分配给该数据。除非删除该记录,否则你无法收回这部分存储空间。

3. 数值型 整数INT 小数NUMERIC 钱数MONEY

INT VS SMALLINT VS TINYINT

通常,为了节省空间,应该尽可能的使用最小的整型数据。一个TINYINT型数据只占用一个字节;一个INT型数据占用四个字节。这看起来似乎差别不大,但是在比较大的表中,字节数的增长是很快的。另一方面,一旦你已经创建了一个字段,要修改它是很困难的。因此,为安全起见,你应该预测一下,一个字段所需要存储的数值最大有可能是多大,然后选择适当的数据类型。

NUMERIC

为了能对字段所存放的数据有更多的控制,你可以使用NUMERIC型数据来同时表示一个数的整数部分和小数部分。NUMERIC型数据使你能表示非常大的数——比INT型数据要大得多。一个NUMERIC型字段可以存储从-10^38到10^38范围内的数。NUMERIC型数据还使你能表示有小数部分的数。例如,你可以在NUMERIC型字段中存储小数3.14。

MONEY VS SMALLMONEY

你可以使用 INT型或NUMERIC型数据来存储钱数。但是,专门有另外两种数据类型用于此目的。

如果你希望你的网点能挣很多钱,你可以使用MONEY型数据。如果你的野心不大,你可以使用SMALLMONEY型数据。MONEY型数据可以存储从-922,337,203,685,477.5808922,337,203,685,477.5807的钱数。如果你需要存储比这还大的金额,你可以使用NUMERIC型数据。

SMALLMONEY型数据只能存储从-214,748.3648214,748.3647 的钱数。同样,如果可以的话,你应该用SMALLMONEY型来代替MONEY型数据,以节省空间

4. 逻辑型 BIT

如果你使用复选框(CHECKBOX)从网页中搜集信息,你可以把此信息存储在BIT型字段中。BIT型字段只能取两个值:0或1

Note: 在你创建好一个表之后,不能向表中添加 BIT型字段。如果你打算在一个表中包含BIT型字段,你必须在创建表时完成

5. 日期型

DATETIME VS SMALLDATETIME

一个 DATETIME型的字段可以存储的日期范围是从1753年1月1日第一毫秒到9999年12月31日最后一毫秒

如果你不需要覆盖这么大范围的日期和时间,你可以使用SMALLDATETIME型数据。它与DATETIME型数据同样使用,只不过它能表示的日期和时间范围比DATETIME型数据小,而且不如DATETIME型数据精确。一个SMALLDATETIME型的字段能够存储从1900年1月1日到2079年6月6日的日期,它只能精确到

DATETIME型字段在你输入日期和时间之前不包含实际的数据,认识这一点是重要的

 

1.3 SQL常用命令 / SQL Frequent Command

       可参考常用命令。

1.4 T-SQL简介 / About Transact-SQL

Transact-SQL(T-SQL)是微软和Sybase使用的一种SQL扩展语言,用于与关系型数据库进行事务交互。T-SQL扩展了SQL的多项标准,包括过程编程,本地变量及多种支持函数,同时也对DELETE和UPDATE语句进行了修改,允许DELETE与UPDATE增加条件FROM。

在流程控制方面,加入了BEGIN和END,BREAK,CONTINUE,GOTO以及IF和ELSE,RETURN,WAITFOR和WHILE等,使其更具编程化。

 

2 关于数据库 / About Database

2.1 常用数据库

目前主要的几种流行数据库为MySQL, SQL server, Oracle, Sysbase, Access等,详细内容可以参考常用数据库。

2.1.1 MySQL

2.1.1.1 MySQL简介

MySQL是最受欢迎的开源SQL数据库管理系统,它由MySQL AB开发、发布和支持。MySQL AB是一家基于MySQL开发人员的商业公司

MySQL是一个快速的、多线程、多用户和健壮的SQL数据库服务器。MySQL服务器支持关键任务、重负载生产系统的使用,也可以将它嵌入到一个大配置(mass- deployed)的软件中去。

  与其他数据库管理系统相比,MySQL具有以下优势:

  (1) MySQL是一个关系数据库管理系统。

  (2) MySQL是开源的。

  (3) MySQL服务器是一个快速的、可靠的和易于使用的数据库服务器。

  (4) MySQL服务器工作在客户/服务器嵌入系统中。

  (5) 有大量的MySQL软件可以使用

2.1.1.2 MySQL使用教程

关于MySQL数据库的使用可以参考教程。

2.1.1.3 MySQL数据类型

可以参考MySQL的数据类型。

2.1.1.3.1 整型

MySQL数据类型

含义(有符号)

tinyint(m)

1个字节  范围(-128~127)

smallint(m)

2个字节  范围(-32768~32767)

mediumint(m)

3个字节  范围(-8388608~8388607)

int(m)

4个字节  范围(-2147483648~2147483647)

bigint(m)

8个字节  范围(+-9.22*10的18次方)

Note: 取值范围如果加了unsigned,则最大值翻倍,如tinyint unsigned的取值范围为(0~256)。 int(m)里的m是表示SELECT查询结果集中的显示宽度,并不影响实际的取值范围,没有影响到显示的宽度,不知道这个m有什么用。

2.1.1.3.2 浮点型(float和double)

MySQL数据类型

含义

float(m,d)

单精度浮点型,8位精度(4字节),m总个数,d小数位

double(m,d)

双精度浮点型,16位精度(8字节),m总个数,d小数位

Note: 设一个字段定义为float(5,3),如果插入一个数123.45678,实际数据库里存的是123.457,但总个数还以实际为准,即6位。

2.1.1.3.3 定点数

浮点型在数据库中存放的是近似值,而定点类型在数据库中存放的是精确值。

decimal(m,d) 参数m<65 是总个数,d<30且 d<m 是小数位。

2.1.1.3.4 字符串(char, varchar, _text)

MySQL数据类型

含义

char(n)

固定长度,最多255个字符

varchar(n)

固定长度,最多65535个字符

tinytext

可变长度,最多255个字符

text

可变长度,最多65535个字符

mediumtext

可变长度,最多2的24次方-1个字符

longtext

可变长度,最多2的32次方-1个字符

charvarchar

1.char(n) 若存入字符数小于n,则以空格补于其后,查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格,varchar不限于此。

2.char(n) 固定长度,char(4)不管是存入几个字符,都将占用4个字节,varchar是存入的实际字符数+1个字节(n<=255)或2个字节(n>255),所以varchar(4),存入3个字符将占用4个字节。

3.char类型的字符串检索速度要比varchar类型的快。

varchartext

1.varchar可指定n,text不能指定,内部存储varchar是存入的实际字符数+1个字节(n<=255)或2个字节(n>255),text是实际字符数+2个字节。

2.text类型不能有默认值。

3.varchar可直接创建索引,text创建索引要指定前多少个字符。varchar查询速度快于text,在都创建索引的情况下,text的索引似乎不起作用。

2.1.1.3.5 二进制数据(_Blob)

1._BLOB和_text存储方式不同,_TEXT以文本方式存储,英文存储区分大小写,而_Blob是以二进制方式存储,不分大小写。

2._BLOB存储的数据只能整体读出。

3._TEXT可以指定字符集,_BLO不用指定字符集。

2.1.1.3.6 日期时间类型

MySQL数据类型

含义

date

日期 '2008-12-2'

time

时间 '12:25:36'

datetime

日期时间 '2008-12-2 22:06:44'

timestamp

自动存储记录修改时间

若定义一个字段为timestamp,这个字段里的时间数据会随其他字段修改的时候自动刷新,所以这个数据类型的字段可以存放这条记录最后被修改的时间。

2.1.1.3.7 数据类型的属性

 MySQL关键字

含义

NULL  

数据列可包含NULL值

NOT   NULL

数据列不允许包含NULL值

DEFAULT  

默认值

PRIMARY   KEY

主键

AUTO_INCREMENT  

自动递增,适用于整数类型

UNSIGNED  

无符号

CHARACTER   SET name

指定一个字符集

 

2.1.1.4 MySQL自带数据库

2.1.1.4.1 information_schema

MySQL中,除了自行安装的数据库外,还有一个information_schema数据库,详细信息来源于参考链接。

information_schema数据库是MySQL自带的,它提供了访问数据库元数据的方式。什么是元数据呢?元数据关于数据的数据,如数据库名或表名,列的数据类型,或访问权限等。有些时候用于表述该信息的其他术语包括“数据词典”和“系统目录”。

在 MySQL中,把information_schema看作是一个数据库,确切说是信息数据库。其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名,数据库的表,表栏的数据类型与访问权限等。在information_schema中,有数个只读表。它们实际上是视图,而不是基本表,因此,你将无法看到与之相关的任何文件。

2.1.1.4.1.1 数据库内的表说明

SCHEMATA表: 提供了当前mysql实例中所有数据库的信息show databases的结果取之此表。

TABLES表: 提供了关于数据库中的表的信息(包括视图)。详细表述了某个表属于哪个schema,表类型,表引擎,创建时间等信息。show tables from schemaname的结果取之此表。

COLUMNS表: 提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。show columns from schemaname.tablename的结果取之此表。

STATISTICS表: 提供了关于表索引的信息。show index from schemaname.tablename的结果取之此表。

USER_PRIVILEGES(用户权限)表: 给出了关于全程权限的信息,该信息源自于mysql.user授权表。是非标准表

SCHEMA_PRIVILEGES(方案权限)表: 给出了关于方案(数据库)权限的信息。该信息来自mysql.db授权表。是非标准表

TABLE_PRIVILEGES(表权限)表: 给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。是非标准表

COLUMN_PRIVILEGES(列权限)表: 给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表

CHARACTER_SETS(字符集)表: 提供了mysql实例可用字符集的信息。show character set的结果集取之此表。

COLLATIONS表: 提供了关于各字符集的对照信息。

COLLATION_CHARACTER_SET_APPLICABILITY表: 指明了可用于校对的字符集。这些列等效于show collation的前两个显示字段。

TABLE_CONSTRAINTS表: 描述了存在约束的表。以及表的约束类型。

KEY_COLUMN_USAGE表: 描述了具有约束的键列。

ROUTINES表: 提供了关于存储子程序(存储程序和函数)的信息。此时,ROUTINES表不包含自定义函数(UDF)。名为“mysql.proc name”的列指明了对应于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。

VIEWS表: 给出了关于数据库中的视图的信息。需要有show views权限,否则无法查看视图信息。

TRIGGERS表: 提供了关于触发程序的信息。必须有super权限才能查看该表。

2.1.1.4.1.2 使用技巧

  1. 类似MS SQL SERVER中的SP_HELPTEXT查看存储过程、函数、视图的功能

SHOW CREATE PROCEDURE 存储过程名;

SHOW CREATE FUNCTION 函数名;

SHOW CREATE VIEW 视图名;

还可以使用以下办法(但下面的办法只包含存储过程主体部分,不包含Create以及传入参数申明的部分):

SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='数据库名' AND ROUTINE_TYPE='PROCEDURE' and ROUTINE_NAME='存储过程名';

函数也类似,只需要将查询条件对应改成ROUTINE_TYPE='FUNCTION'就行

2. 查看当前实例包含的数据库列表

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;

3. 查询数据库中所有的表(包含视图

SELECT * FROM INFORMATION_SCHEMA.tables WHERE TABLE_SCHEMA='数据库名' and TABLE_TYPE='BASE TABLE';

SELECT * FROM INFORMATION_SCHEMA.tables WHERE TABLE_SCHEMA='数据库名' and TABLE_TYPE='VIEW';

4. 查询数据库中所有的视图

SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA='数据库名';

5. 查询表对应的字段

a. SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='数据库名' and table_name='表名';

b. 横向列出表对应的字段,方便写Insert语句: select GROUP_CONCAT(COLUMN_name) as '字段' from INFORMATION_SCHEMA.COLUMNS WHERE table_schema='数据库名' and table_name='表名';

6. 查询关键字在哪些存储过程或者函数中存在

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='数据库名' AND ROUTINE_TYPE='PROCEDURE' AND ROUTINE_DEFINITION like '%关键字%';

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='数据库名' AND ROUTINE_TYPE='FUNCTION' AND ROUTINE_DEFINITION like '%关键字%';

2.1.2 SQL Server

2.1.2.1 SQL Server简介

SQL Server (MSSQL)是由微软开发的数据库管理系统,是Web上最流行的用于存储数据的数据库,它已广泛用于电子商务、银行、保险、电力等与数据库有关的行业。

目前最新版本是SQL Server 2005,它只能在Windows上运行,操作系统的系统稳定性对数据库十分重要。并行实施和共存模型并不成熟,很难处理日益增多的用户数和数据卷,伸缩性有限。

SQL Server 提供了众多的Web和电子商务功能,如对XML和Internet标准的丰富支持,通过Web对数据进行轻松安全的访问,具有强大的、灵活的、基于Web的和安全的应用程序管理等。而且,由于其易操作性及其友好的操作界面,深受广大用户的喜爱。

 

2.1.3 Oracle

提起数据库,第一个想到的公司,一般都会是Oracle(甲骨文)。该公司成立于1977年,最初是一家专门开发数据库的公司。Oracle在数据库领域一直处于领先地位。 1984年,首先将关系数据库转到了桌面计算机上。然后,Oracle5率先推出了分布式数据库、客户/服务器结构等崭新的概念。Oracle 6首创行锁定模式以及对称多处理计算机的支持,最新的Oracle 8主要增加了对象技术,成为关系—对象数据库系统。目前,Oracle产品覆盖了大、中、小型机等几十种机型,Oracle数据库成为世界上使用最广泛的关系数据系统之一。

Oracle数据库产品具有以下优良特性。

(1) 兼容性: Oracle产品采用标准SQL,并经过美国国家标准技术所(NIST)测试。与IBM SQL/DS、DB2、INGRES、IDMS/R等兼容。

(2) 可移植性: Oracle的产品可运行于很宽范围的硬件与操作系统平台上。可以安装在70种以上不同的大、中、小型机上;可在VMS、DOS、UNIX、Windows等多种操作系统下工作。

(3) 可联结性: Oracle能与多种通讯网络相连,支持各种协议(TCP/IP、DECnet、LU6.2等)。

(4) 高生产率: Oracle产品提供了多种开发工具,能极大地方便用户进行进一步的开发。

(5) 开放性: Oracle良好的兼容性、可移植性、可连接性和高生产率使Oracle RDBMS具有良好的开放性。

2.1.4 Sysbase

1984年,Mark B. Hiffman和Robert Epstern创建了Sybase公司,并在1987年推出了Sybase数据库产品。Sybase主要有三种版本:一是UNIX操作系统下运行的版本;二是Novell Netware环境下运行的版本;三是Windows NT环境下运行的版本。对UNIX操作系统,目前应用最广泛的是SYBASE 10及SYABSE 11 for SCO UNIX。

Sybase数据库的特点:

(1) 它是基于客户/服务器体系结构的数据库。

(2) 它是真正开放的数据库。

(3) 它是一种高性能的数据库。

2.2 数据库结构

2.2.1 数据库 / Database

Visual Basic中使用的数据库是关系型数据库(Relational Database)。一个数据库由一个或一组数据表组成。每个数据库都以文件的形式存放在磁盘上,即对应于一个物理文件。不同的数据库,与物理文件对应的方式也不一样。对于dBASE, FoxPro和Paradox格式的数据库来说,一个数据表就是一个单独的数据库文件,而对于Microsoft Access、Btrieve格式的数据库来说,一个数据库文件可以含有多个数据表。

2.2.2 数据表 / Table

简称表,由一组数据记录组成,数据库中的数据是以表为单位进行组织的。一个表是一组相关的按行排列的数据;每个表中都含有相同类型的信息。表实际上是一个二维表格,例如,一个班所有学生的考试成绩,可以存放在一个表中,表中的每一行对应一个学生,这一行包括学生的学号,姓名及各门课程成绩。

2.2.3 记录 / Record

表中的每一行称为一个记录,它由若干个字段组成。

2.2.4 字段 / Field

也称域。表中的每一列称为一个字段。每个字段都有相应的描述信息,如数据类型、数据宽度等。

2.2.5 索引 / Index

为了提高访问数据库的效率,可以对数据库使用索引。当数据库较大时,为了查找指定的记录,则使用索引和不使用索引的效率有很大差别。索引实际上是一种特殊类型的表,其中含有关键字段的值(由用户定义)和指向实际记录位置的指针,这些值和指针按照特定的顺序(也由用户定义)存储,从而可以以较快的速度查找到所需要的数据记录。

2.2.6 查询 / Query

一条SQL(结构化查询语言)命令,用来从一个或多个表中获取一组指定的记录,或者对某个表执行指定的操作。当从数据库中读取数据时,往往希望读出的数据符合某些条件,并且能按某个字段排序。使用SQL,可以使这一操作容易实现而且更加有效。SQL是非过程化语言(有人称为第四代语言),在用它查找指定的记录时,只需指出做什么,不必说明如何做。每个语句可以看作是一个查询(query),根据这个查询,可以得到需要的查询结果。

2.2.7 过滤器 / Filter

过滤器是数据库的一个组成部分,它把索引和排序结合起来,用来设置条件,然后根据给定的条件输出所需要的数据。

2.2.8 视图 / View

数据的视图指的是查找到(或者处理)的记录数和显示(或者进行处理)这些记录的顺序。在一般情况下,视图由过滤器和索引控制。视图中并不存储数据,视图是一种虚表。

2.3 数据库类型

2.3.1 关系型数据库

关系型数据库是指采用了关系模型来组织数据的数据库。

关系模型是在1970年由IBM的研究员E.F.Codd博士首先提出的,在之后的几十年中,关系模型的概念得到了充分的发展并逐渐成为主流数据库结构的主流模型。

简单来说,关系模型指的就是二维表格模型,而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织

关系模型中常用的概念:

关系: 可以理解为一张二维表,每个关系都具有一个关系名,就是通常说的表名

元组: 可以理解为二维表中的一,在数据库中经常被称为记录

属性: 可以理解为二维表中的一,在数据库中经常被称为字段

域: 属性的取值范围,也就是数据库中某一列的取值限制

关键字: 一组可以唯一标识元组的属性,数据库中常称为主键,由一个或多个列组成

关系模式: 指对关系的描述。其格式为:关系名(属性1,属性2, ... ... ,属性N),在数据库中成为表结构

关系型数据库的优点:

容易理解: 二维表结构是非常贴近逻辑世界的一个概念,关系模型相对网状、层次等其他模型来说更容易理解

使用方便: 通用的SQL语言使得操作关系型数据库非常方便

易于维护: 丰富的完整性(实体完整性、参照完整性和用户定义的完整性)大大减低了数据冗余和数据不一致的概率。

2.3.2 非关系型数据库

非关系型数据库提出另一种理念,例如,以键值对存储,且结构不固定,每一个元组可以有不一样的字段,每个元组可以根据需要增加一些自己的键值对,这样就不会局限于固定的结构,可以减少一些时间和空间的开销。使用这种方式,用户可以根据需要去添加自己需要的字段,这样,为了获取用户的不同信息,不需要像关系型数据库中,要对多表进行关联查询。仅需要根据id取出相应的value就可以完成查询。但非关系型数据库由于很少的约束,他也不能够提供像SQL所提供的where这种对于字段属性值情况的查询。并且难以体现设计的完整性。他只适合存储一些较为简单的数据,对于需要进行较复杂查询的数据,SQL数据库显的更为合适。

由于非关系型数据库本身天然的多样性,以及出现的时间较短,因此,不想关系型数据库,有几种数据库能够一统江山,非关系型数据库非常多,并且大部分都是开源的。

这些数据库中,其实实现大部分都比较简单,除了一些共性外,很大一部分都是针对某些特定的应用需求出现的,因此,对于该类应用,具有极高的性能。依据结构化方法以及应用场合的不同,主要分为以下几类,

面向高性能并发读写的key-value数据库:

key-value数据库的主要特点即使具有极高的并发读写性能,Redis,Tokyo Cabinet,Flare就是这类的代表。

面向海量数据访问的面向文档数据库:

这类数据库的特点是,可以在海量的数据中快速的查询数据,典型代表为MongoDB以及CouchDB。

面向可扩展性的分布式数据库:

这类数据库想解决的问题就是传统数据库存在可扩展性上的缺陷,这类数据库可以适应数据量的增加以及数据结构的变化。

2.3.3 NoSQL

NoSQL(NoSQL = Not Only SQL ),意即“不仅仅是SQL”,是一项全新的数据库革命性运动,早期就有人提出,发展至2009年趋势越发高涨。NoSQL的拥护者们提倡运用非关系型的数据存储,相对于铺天盖地的关系型数据库运用,这一概念无疑是一种全新的思维的注入。

2.4 数据库相关知识

2.4.1 Schema(模式)与dbo

关于Schema的理解来自于这个链接

SQL中有一个Schema的概念,实际上Schema就是数据库对象的集合,这个集合包含了表、视图、存储过程、索引等,为了区分不同的集合,就需要给不同的集合起不同的名字,默认情况下一个用户对应一个集合,用户的schema名等于用户名,并作为该用户缺省schema。所以schema集合看上去像用户名。

如果把database看作是一个仓库,仓库很多房间(schema),一个schema代表一个房间,table可以看作是每个房间中的储物柜,user是每个schema的主人,有操作数据库中每个房间的权利,就是说每个数据库映射的user有每个schema(房间)的钥匙。

我们访问一个表时,没有指明该表属于哪一个schema中的,系统就会自动给我们在表上加上缺省的shema名。在数据库中一个对象的完整名称为schema.object,而不属user.object。

在MySQL中创建一个Schema和创建一个Database的效果好像是一样的,但是在sqlserver和orcal数据库中效果又是不同的。

在SQL Server 2000中,user和schema总有一层隐含的关系,让我们很少意识到其实user和schema是两种完全不同的概念,假如我们在某一数据库中创建了用户Bosco,那么此时后台也为我们默认的创建了schema【Bosco】,schema的名字和user的名字相同。

在SQL Server 2005中,为了向后兼容,当用sp_adduser存储过程创建一个用户的时候,sqlserver2005同时也创建了一个和用户名相同的schema,然而这个存储过程是为了向后兼容才保留的,当我们用create user创建数据库用户时,我们可以为该用户指定一个已经存在的schema作为默认的schema,如果我们不指定,则该用户所默认的schema即为dbo schema,dbo房间(schema)好比一个大的公共房间,在当前登录用户没有默认schema的前提下,如果你在大仓库中进行一些操作,比如create table,如果没有制定特定的房间(schema),那么你的物品就只好放进公共的dbo房间(schema)了。但是如果当前登录用户有默认的schema,那么所做的一切操作都是在默认的schema上进行。

Oracle数据库中不能新建一个schema,要想创建一个schema,只能通过创建一个用户的方法解决,在创建一个用户的同时为这个用户创建一个与用户名同名的schem并作为该用户的缺省shcema。即schema的个数同user的个数相同,而且schema名字同user名字一一对应并且相同。

 

数据库操作

日常工作中经常遇到类似的问题:把某个服务器上的某些指定的表同步到另外一台服务器。

参考链接


什么是SQL数据库:

SQL是Structured Query Language(结构化查询语言)的缩写。SQL是专为数据库而建立的操作命令集,是一种功能齐全的数据库语言。在使用它时,只需要发出“做什么”的命令,“怎么做”是不用使用者考虑的。SQL功能强大、简单易学、使用方便,已经成为了数据库操作的基础,并且现在几乎所有的数据库均支持SQL。

类似需求用SSIS或者其他ETL工作很容易实现,比如用SSIS的话,就会会存在相当一部分反复的手工操作。

一、基础

1、说明:创建数据库
CREATE DATABASE database-name
2、说明:删除数据库
drop database dbname
3、说明:备份sql server
--- 创建 备份数据的 device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backupMyNwind_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
电脑 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'
go

5、检查备份集
RESTORE VERIFYONLY from disk='E:dvbbs.bak'

6、修复数据库
ALTER DATABASE [dvbbs] SET SINGLE_USER
GO
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK
GO
ALTER DATABASE [dvbbs] SET MULTI_USER
GO

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))
9159金沙游戏场,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      5
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表是否相等:

建源的数据库信息,目标的数据库信息,如果是多个表,需要一个一个地拉source和target,然后一个一个地mapping,然后运行实现数据同步。

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) [澳门游艺场9159,下一步] 设置快照文件夹

采用默认servernamePub

(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

/************* mysql视图 ***********:

一、视图view作用:

  1. 可以简化我们的查询:

比如:复杂的统计时,先用视图生成一个中间结果,再查询视图。

  1. 更精细的权限控制:

比如:某张用户表为例。

现在,有两个网站合作,可以查询对方网站的用户信息,需要向对方开放用户表的权限,又不想开放用户表中的密码字段。

CREATE VIEW v_use AS

select user_id,username,email from user;

可以开放这个没有密码字段的视图权限给对方。

  1. 数据多,分表时可以用到:

比如:小说站,article表,1000多万篇,数据量太大,

这个时候可以把小说的记录纵向分成article1, article2 ,article3.....article5 . 这5张表每张表放200万就可以了。

查询小说时,不知道在哪张表

create view v_article as

select title from article2 union select title from article2 ...union select title article 3...

这个时候可以把要查的字段,从五张表中取出来放到一个视图里,查这个视图。

二、视图特性:

  1. 视图中必须存放select 查询语句,视图可以当做表用

  2. 视图就像是一个表的影子,当表发生改变的时候,已经创建好的视图也随着发生改变。

  3. 视图是否可以修改?(不建议对视图执行insert,update,delete 操作)

可以修改的情况:视图和表的数据一一对应。

不可以修改的情况:视图和表的数据不一一对应

思考:什么就叫做一一对应呢?

答案:

  一一对应是指,根据select关系,从表中取出的行,只能计算出视图中确定的一行,反之,视图中任意抽出一行,

  也能够返回出表中的具体确定一行。就像函数的映射一样。视图中的某字段的平均数,和,差 和该字段中的数据不是一一对应,

  或者视图中已经进行了重新排序和limit都不是一一对应。只有本字段和本字段才算:由表的数据可以推出视图的数据,

  由视图的数据可以推出表的数据。但是如果视图中是平均数,和,查的话,是推不出表中每个数据的数值的。
  1. 数据库数据data存储的格式:.frm 代表表的结构,是定义文件。

.NYD 代表表的数据

.NYI 代表表的索引

视图文件的话,是没有.NYD和.NYI的,只有一个.frm文件。没有真实的数据,有的只是一个关系结构。

三、视图的algorithm :

思考:相比于建临时表,哪个快?

建表:查询 -》 形成临时表 -》 查询临时表 (慢)

叠加:合并条件 -》 查询表 (快)

algorithm = merge 当引用视图时,引用视图的语句与定义视图的语句合并。

temptable 当引用视图时,根据视图的创建语句建立一个临时表

undefined 未定义,自动,由系统帮你判断。
*/

/*
1、algorithm = merge:

意味着视图只是一个语句规则,当查询视图时,把查询视图的语句(比 如where 那些)与创建时的语句where子句等合并分析形成一条select语句。

建一张简单的查询视图,不用临时表,只用条件合并。 */

CREATE [ALGORITHM=MERGE] view v1
AS
SELECT goods_id,goods_price FROM goods WHERE goods_price < 5000;

查询视图语句:

SELECT goods_id,goods_price FROM v1 WHERE goods_price > 4000;

最终执行语句:

SELECT goods_id,goods_price FROM goods WHERE goods_price > 4000 AND goods_price < 5000;

这个简单的查询还建临时表的话,开销就有点大。这个时候,我们可以指定algorithm 选项为merge .

2、algorithm = temptable:

CREATE ALGORITHM = TEMPTABLE view v3 AS SELECT * FROM goods ORDER BY goods_price;

3、algorithm = undefined :

如果不知道该用什么哪种方式就无须指定algorithm的方式使用默认,则algorithm = undefined让系统自动选择。

四、视图语法:

/*1、创建视图:

CREATE VIEW 视图名称 AS 查询语句;  */

CREATE VIEW v_goods_sum AS

SELECT sum(goods_price), goods_brand.brand_name FROM goods left join goods_brand

 ON goods.goods_id=goods_brand.brand_id GROUP BY goods_brand.brand_name;

/*2、查询创建视图的sql:

SHOW CREATE VIEW 视图名称; */

SHOW CREATE VIEW v_goods_sum;

/*3、查看视图: */

#方法一:进入该数据库的前提下,查看指定数据库中的视图

SHOW TABLES;

/*方法二:没进入数据库的前提下,查看指定数据中的视图

在数据库information_schema 的 表tables里记录和视图和有关表的信息;

注意:

    table_name  被查的视图名称。

    table_schema 被查的视图或表存放在的数据库名字 */

/*1、查询所有视图:

USE information_schema;

SELECT table_schema,table_name,table_type FROM tables

WHERE table_schema='库名' AND table_type='view';  */

#实例:查询数据库shopping下的所有视图:

SELECT table_schema,table_name,table_type FROM tables WHERE table_schema='shopping' AND table_type='view';

/*4、查询某个库所有表和视图:

USE information_schema;

SELECT table_schema,table_name,table_type FROM tables WHERE table_schema='库名'; */

#查询数据库shopping下的所有表和视图:

SELECT table_schema,table_name,table_type FROM tables WHERE table_schema='shopping';

/*5、使用视图(把视图当表用):

select * from 视图名
select 字段,字段,字段 from 视图名称
select * from 视图名 where 条件;  */

SELECT * FROM v_goods_sum;

/*6、修改视图:

ALTER VIEW  视图名 AS 查询语句;  */

ALTER VIEW v_goods_sum AS

SELECT sum(goods_price) AS '品牌总价格', goods_brand.brand_name AS '品牌名称' FROM goods left join goods_brand

    ON goods.goods_id=goods_brand.brand_id GROUP BY goods_brand.brand_name;

#测试
SELECT * FROM v_goods_sum;

/*7、删除视图:

DROP VIEW 视图名; */

DROP VIEW v_goods_sum;

/*8、php调用视图:

$result = mysql_query('SELECT * FROM 视图名称');

*/

然后很可能,这个workflow使用也就这么一次,就寿终正寝了,却一样要浪费时间去做这个ETL。

自定义函数 AND 存储过程 procedure

一、自定义函数:

用户自定义函数(user-defined function,UDF)是一种对MySQL扩展的途径,其用法与内置函数相同。

自定义函数的两个必要条件:参数、返回值。函数可以返回任意类型的参数,同样可以接受这些类型的参数。

注意:函数是基于数据库的,所有在写函数的时候,必须先选择数据库。

1、创建自定义函数:

USE db_name; #选择数据库

CREATE FUNCTION function_name

RETURNS {STRING|INTEGER|REAL|DECIMAL} 返回值类型

routine_body(函数体)

注意:关于函数体

1.函数体由合法的SQL语句构成;
2.函数体可以使用简单的SELECT或INSERT语句
3.函数体如果为复合结构则使用BEGIN...END语句
4.复合结构可以包含声明、循环、控制结构

*/

USE hello;

快速数据同步实现

1.1、创建不带参数的自定义函数:

CREATE FUNCTION f1()
RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(),'%Y-%m-%d');

SELECT f1();

于是在想,可不可能快速实现类似需求,尽最大程度减少重复的手工操作?类似基于命令行的方式,简单快捷,不需要太多的手动操作。

1.2、创建带有参数的自定义函数:

CREATE FUNCTION f3(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
RETURNS FLOAT(10,2) UNSIGNED
RETURN (num1+num2)/2;
SELECT f2(1,5);

于是就有了本文,基于Python,快速实现SQL Server的数据库之间的数据同步操作,后面又稍微扩展了一下,可以实现不同服务器的数据库之间的表结构,表对应的数据,存储过程,函数,用户自定义类型表的同步

注意:如果在调用的时候没有参数,那么会系统报错

+---------+
| f2(1,5) |
+---------+
| 3.00 |
+---------+

目前支持在两个SQL Server数据源之间:每次同步一张或者多张表/存储过程,也可以同步整个数据库的所有表/存储过程。

1.3、创建具有复合结构函数体的自定义函数:

支持sqlserver2012以上版本

推导第一步:

CREATE FUNCTION addbook(b_name VARCHAR(20))
RETURNS INT UNSIGNED
INSERT book(book_name) values(b_name);
RETURN LAST_INSERT_ID();

/*注意:

  当写到这里的时候,由于下面还有SQL语句,所有必须写分号,但是MySQL遇到分号后会让整个创建方法的语句也结束,
  所以必须使用DELEMIT 来改变结束符。*/

注意:修改结束符为 //

需要考虑到一些基本的校验问题:在源服务器上,需要同步的对象是否存在,或者输入的对象是否存在于源服务器的数据库里。

推导第二步:

DELIMITER //
CREATE FUNCTION addBook(b_name VARCHAR(30))
RETURNS SMALLINT UNSIGNED
INSERT book(book_name) VALUES (b_name);
RETURN LAST_INSERT_ID() //
DELIMITER ;

/*注意:

  1、虽然修改了结束分号,不再让SQL结束符和自定义函数中语句结束符冲突,但是方法体需要的是一个整体,
  所以必须引入BEGIN .. END来把所有的执行语句标识为一个方法体。

  2、显示的结果为自定义的返回值,使用完毕后再修改结束符为 ;

*/

在目标服务器上,对于表的同步:

最后成功创建

DELIMITER //
CREATE FUNCTION addBook(b_name VARCHAR(30))
RETURNS INT UNSIGNED
BEGIN
INSERT book(book_name) VALUES(b_name);
RETURN LAST_INSERT_ID();
END //
DELIMITER ;

1,表的存在依赖于schema,需要考虑到表的schema是否存在,如果不存在先在target库上创建表对应的schema

调用自定义函数的时候用SELECT关键字来调用,

2,target表中是否有数据?如果有数据,是否以覆盖的方式执行

无法获得正确的last_insert_id(),检查你的id字段是否是自增长。AUTO_INCREMENT

SELECT addBook('hello word');
+-----------------------+
| addBook('hello word') |
+-----------------------+
| 10 |
+-----------------------+

2、查看所有的自定义函数和存储过程:

SELECT db,name FROM mysql.proc;

3、删除自定义函数:

DROP FUNCTION IF EXISTS function_name;

实例:删除自定义函数addBook

DROP FUNCTION IF EXISTS addBook;

/*

二、存储过程:

1、概念:

我们经常会对数据库进行CURD(增删改查),当我们输入SQL命令后,MySQL引擎会对其进行语法分析查看我们输入的SQL语句的正确性,
如果正确的话进行编译,编译成MySQL引擎识别的命令,完成执行后把执行结果返回给客户端,这个就是MySQL的执行流程,试想,如果我们
想把这个流程简化一下,可以选择把语法分析和编译的过程省略,这样就可以提高Mysql的执行效率。如果实现,那么就需要存储过程。

存储过程是SQL语句和控制语句的预编译集合,以一个名称存储并作为一个单元处理。

比如:
有2个SQL语句,那么MySQL引擎会对这两个语句进行逐一的语法分析、编译和执行,而使用存储过程只在第一次的时候进行语法分析
和编译,而之后就直接调用这个执行结果了,即省略了两个环节。

2、优点:

A、增强SQL语句的功能和灵活性。(流程控制和条件判断和较复杂的运算都可以执行)
B、实现较快的执行速度(不逐一语法分析和编译,而是采用预编译直接执行)
C、减少网络流量(单个提交SQL语句,浪费流量)

3、创建存储过程:

CREATE
[DEFINER = {user | CURRENT_USER}] #创建者,默认为当前登陆到客户端用户
PROCEDURE sp_name([proc_parameter[,.....]])
[characteristic .....] routine_body
proc_aprameter: [IN | OUT | INOUT] param_name type

IN:表示该参数的值必须在调用存储过程时指定,这个值不能被返回
OUT:表示该参数的值可以被存储过程改变,并且可以返回
INOUT:表示该参数的调用时指定,并且可以被改变和返回。

过程体:

1、过程体由合法的SQL语句构成
2、过程体可以是任意的SQL语句CURD。(但不能创建数据库和数据表)
3、过程体如果为复合结构则使用BEGIN....END语句
4、复合结构可以包含声明、循环、控制结构

范例:

DELIMITER //
 CREATE PROCEDURE 存储过程名称()
 BEGIN
   sql.. ;
   sql.. ;
 END //
DELIMITER ;

注意:存储过程是基于数据库的,所有在创建和使用存储过程的时候必须先进入数据库

*/

3.0、查看存储过程状态:

SHOW PROCEDURE STATUS;

3.1 创建不带参数的存储过程:

获取MySQL版本的功能,如果没有参数,在调用的过程中带不带括号都可以。

USE hello;

CREATE PROCEDURE pr_1() SELECT VERSION();

CALL pr_1;
+-----------+
| VERSION() |
+-----------+
| 5.5.20 |
+-----------+

CALL sp_1();
+-----------+
| VERSION() |
+-----------+
| 5.5.20 |
+-----------+

3.2、创建带有参数的简单存储过程实例:

DELIMITER //
CREATE PROCEDURE pr_2(width INT,height INT)
BEGIN
SELECT CONCAT('你的面积是',width*height) AS area;
IF width > height THEN
SELECT '胖子' AS '体型';
ELSEIF width < height THEN
SELECT '瘦子' AS '体型';
ELSEIF width = height THEN
SELECT 'hello' AS '体型';
END IF;
END //
DELIMITER ;

CALL pr_2(10,10);

对于存储过程的同步:

注意:此存储过程中出现了两次SELECT关键字,说明该存储过程在执行的时候有两个返回值

3.3、创建带有IN类型参数的存储过程:

根据ID删除记录(因为有参数,所以调用的时候括号不能省略)

DELIMITER //
CREATE PROCEDURE removeBookById(IN id INT UNSIGNED)
BEGIN
DELETE FROM book WHERE book_id = id;
END //
DELIMITER ;

CALL removeBookById(10);

3.4 创建带有OUT类型参数的存储过程:

删除book表中指定ID的记录,然后返回剩余记录的条数

DELIMITER //
CREATE PROCEDURE removeBookAndReturnBookNums(IN id INT UNSIGNED,OUT bookNums INT UNSIGNED)
BEGIN
DELETE FROM book WHERE book_id = id;
SELECT count(1) INTO bookNums FROM book ;
END //
DELIMITER ;

CALL removeBookAndReturnBookNums(8,@nums);

SELECT @nums;

+-------+
| @nums |
+-------+
| 8 |
+-------+

3.5、创建带有INOUT类型参数的存储过程:

DELIMITER //
CREATE PROCEDURE pr_3(INOUT age INT UNSIGNED)
BEGIN
SET age = age + 2;
END //
DELIMITER ;

SET @age = 20;

CALL pr_3(@age);
SELECT @age;

+------+
| @age |
+------+
| 22 |
+------+

3.6、创建带有多个OUT类型参数的存储过程:

1,类似于表,需要考虑存储过程的schema是否存在,如果不存在先在target库上创建表对应的schema2,类似于表,arget数据库中是否已经存在对应的存储过程,是否以覆盖的方式执行3,存储过程可能依赖于b表,某些函数,用户自定义表变量等等,同步存储过程的时候需要先同步依赖的对象,这一点比较复杂,实现过程中遇到在很多很多的坑

ROW_COUNT() 受影响行数

insert test values(null,66666),(null,0000);
SELECT row_count();
+-------------+
| row_count() |
+-------------+
| 2 |
+-------------+

DELIMITER //
CREATE PROCEDURE removeUserByAgeAndReturnInfos(In p_age SMALLINT UNSIGNED,OUT deleteUsers SMALLINT UNSIGNED,OUT userCounts SMALLINT UNSIGNED)
BEGIN
DELETE FROM users WHERE age = p_age;
SELECT ROW_COUNT() INTO deleteUsers;
SELECT COUNT(id) FROM users INTO userCounts;
ENG //
DELIMITER ;

CALL removeUserByAgeAndReturnInfos(12,@a,@b);
SELECT @a,@b;

4、调用存储过程: CALL 存储过程名(值);

5、删除存储过程: DROP PROCEDURE 存储过程名;

6、查看存储过程:

mysql库->proc 表

查看所有的自定义函数和存储过程:

SELECT db,name FROM mysql.proc;

查看指定数据库下的存储过程

SELECT db,name FROM mysql.proc WHERE db = '所属的数据库';

实例:查看数据库hello下的存储过程

SELECT db,name FROM mysql.proc WHERE db = 'hello';

/*
三、总结:

1、自定义函数和存储过程:

1、存储过程实现的功能要复杂一些;自定义函数的针对性更强一些。
2、存储过程可以返回多个值;自定义函数只能有一个值
3、存储过程一般独立执行;自定义函数主要作为其他SQL语句的组成部分来出现(和内置函数用法相同)。

*/

/*
四、基础语法总结:

1、定义变量

第一种:
declare 变量名 数据类型;
declare 变量名 类据类型 [default 值] ;

  注意:
      1、定义变量的代码必须出现开始位置
      2、这种变量为局部变量,只在begin..end之间起作用,end以后则变量销毁。

第二种:
SET @i = 7

  注意:
      1、这种变量只对当前用户使用的客户端有效。
      2、用户变量

  查看该变量:SELECT @i  */

可能存在对象A依赖于对象B,对象B依赖于对象C……,这里有点递归的意思

实例1:

DELIMITER //
CREATE PROCEDURE pro_1()
BEGIN
declare username varchar(20);
declare age smallint;
declare height int default 175;
SET username = '张三';
SET age = '180';
SELECT concat('姓名',username,'年龄是',age,'身高是',height);
END //
DELIMITER ;

set @name ='hanlibao';

DELIMITER //

CREATE PROCEDURE pro_2(username varchar(30))
BEGIN
declare age smallint;
declare height int default 175;
SET age = '180';
SELECT concat('姓名',username,'年龄是',age,'身高是',height);
END //
DELIMITER ;

call pro_2(@name);

/*2、变量赋值:

declare 变量名 数据类型 default 值;

SET 变量名= 值;

SELECT 字段 into 变量 from 表名 where 条件;
SELECT 字段 into 变量,字段 into 变量 from 表名
SELECT 聚合函数(字段) into 变量 from 表名 */

这一点导致了重构大量的代码,一开始都是直来直去的同步,无法实现这个逻辑,切实体会到代码的“单一职责”原则

实例2:

DELIMITER //
CREATE PROCEDURE hello4()
BEGIN
declare a int;
declare b varchar(30);
declare c varchar(100);
SET a = 20;
SET b = 'beijing';
SELECT stuname into c from student where stuid = a;
SELECT c;
END //
DELIMITER ;

/*3、运算符:

算术运算符 + - * /
逻辑运算符 and or not */

参数说明

实例3:

DELIMITER //
CREATE PROCEDURE hello5()
begin
declare a int default 20;
declare b int default 30;
declare sum int;
SET sum = a + b;
SELECT sum;
end //
DELIMITER ;

/*4.1、判断语句: IF ... ELSE..

IF 条件 THEN
sql语句...;
END IF;

IF 条件 THEN
sql语句...;
ELSE
sql语句...;
END IF;

IF 条件 THEN
sql语句...;
ELSEIF 条件 THEN
sql语句...;
ELSEIF 条件 THEN
sql语句...;
ELSE
sql语句
END IF; */

参数说明如下,大的包括四类:

实例4:

DELIMITER //
CREATE PROCEDURE pro_2()
BEGIN
DECLARE age INT DEFAULT 18;
IF age >= 18 THEN
SELECT '已成年';
ELSE
SELECT '未成年';
END IF;
END //
DELIMITER ;

/*4.2、判断语句: CASE

CASE 变量
WHEN 条件一 THEN 执行体1;
WHEN 条件二 THEN 执行体2;
...
END CASE; */

1,源服务器信息 ,没有用户名密码的情况下,使用windows身份认证模式

实例5:

DELIMITER //
CREATE PROCEDURE pro_6()
BEGIN
DECLARE pos INT DEFAULT 0;
SET pos = FLOOR(5*RAND());
CASE pos
WHEN 1 THEN SELECT 'still flying';
WHEN 2 THEN SELECT 'fall in sea';
WHEN 3 THEN SELECT 'in the island';
ELSE SELECT 'I dont know';
END CASE;
END //
DELIMITER ;

CALL pro_6();

/*5、循环语句

WHILE 条件 DO
sql语句..
END WHILE; */

2,目标服务器信息,没有用户名密码的情况下,使用windows身份认证模式

实例6:

DELIMITER //
CREATE PROCEDURE pro_4()
BEGIN
DECLARE total INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
WHILE i <= 100 DO
SET total = total + i;
SET i = i + 1;
END WHILE;
SELECT total;
END //
DELIMITER ;
CALL pro_4();

/*
REPEAT
sql....;
UNTIL 条件 END REPEAT; */

3,同步的对象类型以及对象

实例7:

DELIMITER //
CREATE PROCEDURE pro_7()
BEGIN
DECLARE total INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
REPEAT
SET i = i + 1;
SET total = total + i;
UNTIL i >= 100 END REPEAT;
SELECT total;
END //
DELIMITER ;
CALL pro_7();

/*
标记:loop
sql
if 条件 then
leave 标记;
end if;
end loop; */

本文由澳门游艺场9159发布于澳门游艺场,转载请注明出处:数据库操作,类似需求用SSIS或者其他ETL工作很容

关键词: