Server数据库实现对象同步轻量级,Language精妙操作

时间:2020-04-07 14:23来源:江苏十一选五手机版数据库
对此仓库储存进程的一路: 数据库概述 / Database Overview   1 关于SQL /About SQL 构化查询语言(StructuredQueryLanguageState of Qatar简单称谓SQL,是一种分外指标的 编制程序语言 ,是一种 数据库查

对此仓库储存进程的一路:

数据库概述 / Database Overview


 

1 关于SQL / About SQL

构化查询语言(Structured Query LanguageState of Qatar简单称谓SQL,是一种分外指标的编制程序语言,是一种数据库查询和程序设计语言,用于存取数据以至查询、更新和保管关周到据库系统;同期也是数据库脚本文件的扩充名。

布局化查询语言是尖端的非进程化编制程序语言,允许顾客在高层数据构造上行事。它不须要顾客钦赐对数据的存放方法,也无需客商明白具体的多寡存放情势,所以具备完全区别底层结构的区别数据库系统, 能够行使相仿的布局化查询语言作为数据输入与管理的接口。构造化查询语言语句可以嵌套,那使它兼具相当的大的圆滑和强有力的坚决守住。

1.1 SQL语言 / SQL Language

构造化查询语言满含6个部分:

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

其语句,也可以称作“数据检索语句”,用以从表中获得数据,鲜明数据怎么样在应用程序给出。保留字SELECTDQL(也是持有SQL)用得最多的动词,其余DQL常用的保留字有WHERE,O奥迪Q5DER BY,GROUP BY和HAVING。那么些DQL保留字常与此外体系的SQL语句一同使用。

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

其语句包含动词INSERT, UPDATEDELETE。它们各自用于增添,匡正和删除表中的行。也可以称作动作查询语言。

3. 事务管理语言(TPL

它的口舌能确定保障被DML语句影响的表的有所行当即得以更新。TPL语句包含BEGIN TRANSACTION,COMMIT和ROLLBACK。

4. 数额调节语言(DCL

它的说话通过GRANTREVOKE取得批准,显著单个客户和顾客组对数据库对象的走访。某个君越DBMS可用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**

VARCHA福特Explorer型和CHAHaval型数据的那些出入是略微的,可是丰富主要。他们都以用来囤积字符串长度小于255的字符。

若果你向八个长度为39个字符的VARCHAR型字段中输入数据BillGates。当您未来从那几个字段中收取此数额时,你收取的数码其尺寸为10个字符——字符串BillGates的长度。倘令你把字符串输入三个尺寸为四贰13个字符的CHAR型字段中,那么当您收取数据时,所抽出的多长将是四十多少个字符。字符串的背后会被增好多余的空格

当您创建和谐的站点时,你会发觉使用VARCHATucson型字段要比CHA索罗德型字段方便的多。使用VARCHA瑞鹰型字段时,你无需为剪掉你多少中剩下的空格而想念。

VARCHASportage型字段的另二个崛起的好处是它能够比CHATiggo型字段占用越来越少的内部存款和储蓄器和硬盘空间。当您的数据库异常的大时,这种内部存款和储蓄器和磁盘空间的节约会变得不行主要。

2. 文本型 TEXT

行使文本型数据,你可以贮存当先五十亿个字符的字符串。当你须要储存大串的字符时,应该运用文本型数据。

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

当你从HTML FORM的多行文本编辑框(TEXTAREA)中募集数据时,你应有把募集的音讯囤积于文本型字段中。不过,无论曾几何时,一经您能幸免采用文本型字段,你就应该不利用它。文本型字段既大且慢,滥用文本型字段会使服务器速度变慢。文本型字段还大概会吃掉大批量的磁盘空间

借使你向文本型字段中输入了别的数据(以致是空值),就能有2K的空间被自动分配给该数量。除非删除该记录,不然你不能够打消那有的仓库储存空间。

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

INT VS SMALLINT VS TINYINT

常常,为了省去空间,应该尽量的利用最小的整型数据。二个TINYINT型数码只占用贰个字节;一个INT型数码占用多个字节。那看起来如同差距一点都不大,不过在可比大的表中,字节数的增高是快捷的。另一面,一旦你已经成立了多个字段,要纠正它是非常不方便的。由此,为安全起见,你应当预测一下,壹个字段所急需仓库储存的数值最大有非常大恐怕是多大,然后选取适当的数据类型。

NUMERIC

为了能对字段所贮存的数目有更加多的主宰,你能够运用NUMECRUISERIC型数据来还要表示二个数的卡尺尾部分和小数部分。NUMEEvoqueIC型数据令你能表示丰富大的数——比INT型数据要大得多。三个NUME奥德赛IC型字段能够储存从-10^38到10^38界定内的数。NUMEEnclaveIC型数据还使你能表示有小数部分的数。譬如,你能够在NUMETiguanIC型字段中存放小数3.14。

MONEY VS SMALLMONEY

你能够行使 INT型或NUMELX570IC型数据来囤积钱数。不过,专门有此外二种数据类型用于此指标。

假诺您期待您的网点能挣相当多钱,你能够利用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日第一纳秒到9999年10月二18日最终一微秒

假定您不供给覆盖这么大面积的日期和岁月,你可以采取SMALLDATETIME型数码。它与DATETIME型数据一致选取,只可是它能表示的日期和时间限制比DATETIME型数据小,何况不比DATETIME型数据规范。三个SMALLDATETIME型的字段能够存款和储蓄从一九零八年八月1日到2079年一月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,RETUTucsonN,WAITFO讴歌MDX和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具备以下优势:

  (1State of QatarMySQL是三个关周全据库管理种类。

  (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~256State of Qatar。 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,实际数据Curry存的是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(顾客权限State of Qatar表: 给出了有关全程权限的新闻,该音信源自于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 二〇〇七,它只可以在Windows上运转,操作系统的系统牢固对数据库十三分至关心重视要。并行实施和现成模型并不成熟,很难处理日益增加的顾客数和数据卷,伸缩性有限。

SQL Server 提供了多数的Web和电商功用,如对XML和Internet标准的足够帮忙,通过Web对数码举行轻便安全的拜访,具备强有力的、灵活的、基于Web的和鼓浪屿的应用程序管理等。何况,由于其易操作性及其友好的操作分界面,相当受附近客户的心爱。

 

2.1.3 Oracle

谈到数据库,第三个想到的厂家,平时都会是Oracle(钟鼓文卡塔尔。该商厦树立于一九七九年,最先是一家特地开辟数据库的公司。Oracle在数据库领域间接处于超越地位。 一九八七年,首先将关周详据库转到了桌面Computer上。然后,Oracle5率先推出了遍布式数据库、客商/服务器布局等崭新的概念。Oracle 6首创行锁定情势以至对称多管理计算机的扶植,最新的Oracle 8首要扩充了指标技能,成为事关—对象数据库系统。近些日子,Oracle付加物覆盖了大、中、小型Computer等几十种机型,Oracle数据库成为世界上运用最布满的关周全据系统之一。

Oracle数据库产物具有以下能够脾气。

(1卡塔尔(قطر‎ 包容性: Oracle成品采纳标准SQL,并透过美利坚合众国国标技艺所(NIST卡塔尔测量检验。与IBM SQL/DS、DB2、INGRES、IDMS/讴歌RDX等分外。

(2卡塔尔国 可移植性: Oracle的付加物可运转于很宽范围的硬件与操作系统平台上。能够设置在70种以上区别的大、中、小型计算机上;可在VMS、DOS、UNIX、Windows等各类操作系统下办事。

(3卡塔尔(قطر‎ 可联结性: Oracle能与多样简报网络不断,援救各样协商(TCP/IP、DECnet、LU6.2等)。

(4卡塔尔 高坐褥率: Oracle产品提供了各样开辟工具,能超级大地便民客户实行更为的开垦。

(5卡塔尔国 开放性: Oracle优质的包容性、可移植性、可连接性和高临蓐率使Oracle ENVISIONDBMS具备优质的开放性。

2.1.4 Sysbase

1983年,Mark B. Hiffman和RobertEpstern成立了Sybase集团,并在1986年分娩了Sybase数据库成品。Sybase主要有二种版本:一是UNIX操作系统下运作的本子;二是Novell Netware情形下运营的版本;三是Windows NT景况下运作的本子。对UNIX操作系统,近年来利用最广大的是SYBASE 10及SYABSE 11 for SCO UNIX。

Sybase数据库的特征:

(1卡塔尔(قطر‎它是依附客户/服务器系统布局的数据库。

(2卡塔尔(قطر‎它是真正开放的数据库。

(3State of Qatar它是一种高质量的数据库。

2.2 数据库布局

2.2.1 数据库 / Database

Visual Basic中接收的数据库是关系型数据库(Relational Database卡塔尔(قطر‎。叁个数据库由一个或一组数据表结缘。每个数据库都是文件的方式存放在磁盘上,即对应于一个物理文件。差别的数据库,与物理文件对应的办法也不相通。对于dBASE, 福克斯Pro和Paradox格式的数据库来讲,贰个数据表正是三个独自的数据库文件,而对此Microsoft Access、Btrieve格式的数据库来讲,二个数据库文件能够分包多少个数据表。

2.2.2 数据表 / Table

简单称谓表,由一组数据记录组成,数据库中的数据是以表为单位进展集体的。八个表是一组有关的按行排列的多寡;每一种表中都满含相仿档案的次序的音讯。表实际上是三个二维表格,比方,叁个班全数学员的考试战表,能够寄放在多个表中,表中的每一行对应二个上学的小孩子,这一行包涵学生的学号,姓名及各门课程战表。

2.2.3 记录 / Record

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

2.2.4 字段 / Field

也称域。表中的每一列称为一个字段。每一种字段都有相应的叙说消息,如数据类型、数据上涨的幅度等。

2.2.5 索引 / Index

为了抓好访问数据库的频率,可以对数据库使用索引。当数据库一点都不小时,为了搜求内定的笔录,则选取索引和不行使索引的效率有比相当大差异。索引实际上是一种特殊类其他表,当中饱含关键字段的值(由顾客定义State of Qatar和针对实际记录地方的指针,这个值和指针依照一定的逐个(也由客户定义卡塔尔存款和储蓄,进而能够以异常的快的进度查找到所供给的数据记录。

2.2.6 查询 / Query

一条SQL(布局化查询语言State of Qatar命令,用来从三个或八个表中得到一组内定的记录,可能对有些表实行钦定的操作。当从数据库中读取数据时,往往愿意读出的数额相符有个别法规,而且能按某些字段排序。使用SQL,可以使这一操作轻松达成何况越是可行。SQL是非进程化语言(有人称之为第四代语言卡塔尔国,在用它寻觅钦赐的记录时,只需提出做什么,不必表明如何是好。每一种语句能够作为是叁个询问(query卡塔尔国,依据这一个查询,能够获取要求的询问结果。

2.2.7 过滤器 / Filter

过滤器是数据库的一个组成都部队分,它把索引和排序结合起来,用来安装标准,然后依据给定的尺度输出所须要的数据。

2.2.8 视图 / View

数码的视图指的是查找到(大概管理卡塔尔国的记录数和呈现(也许扩充管理卡塔尔那个记录的逐个。在日常景观下,视图由过滤器和目录调节。视图中并不存款和储蓄数据,视图是一种虚表。

2.3 数据库类型

2.3.1 关系型数据库

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

涉嫌模型是在一九六七年由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 State of Qatar,意即“不仅是SQL”,是一项斩新的数据库革命性运动,开始时代就有人建议,发展至2008年来势越来越高涨。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 二〇〇〇中,user和schema总有一层隐含的关联,让我们少之甚少意识到骨子里user和schema是二种截然两样的概念,假使我们在某一数据库中成立了客商Bosco,那么那时后台也为咱们暗中认可的开创了schema【Bosco】,schema的名字和user的名字一成不改变。

在SQL Server 2007中,为了向后特别,当用sp_adduser存储进程创制二个顾客的时候,sqlserver二〇〇七同时也开创了三个和顾客名相像的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名字一一对应并且相通。

 

数据库操作

上述所述是作者给我们介绍的遵照Python的SQL Server数据库落成指标同步轻量级,希望对大家有所扶持,即使大家有别的疑窦应接给本身留言,作者会及时回复大家的!

参考链接


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

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

SELECT f1();

总结

末了成功创办

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 ;

经常说来工作中不常遇上相符的难点:把某部服务器上的一些钦定的表同步到别的一台服务器。

演绎第二步:

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、显示的结果为自定义的返回值,使用完毕后再修改结束符为 ;

*/

6,鉴于insert into values(***),(***),(***)语法上同意的最大值是1000,因而每生成1000条数据,就联合二遍

留心:此存款和储蓄进度中冒出了三次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类型参数的存款和储蓄进度:

脚下支撑在多少个SQL Server数据源之间:每一遍同步一张也许多张表/存款和储蓄进程,也得以协同整个数据库的全体表/存款和储蓄进程。

自定义函数 AND 存款和储蓄进度 procedure

一、自定义函数:

顾客自定义函数(user-defined function,UDFState of Qatar是一种对MySQL扩张的门路,其用法与内置函数相符。

自定义函数的五个供给条件:参数、重回值。函数能够回来任意档期的顺序的参数,相仿能够选用那一个项目标参数。

在乎:函数是基于数据库的,全部在写函数的时候,必需先接收数据库。

1、创造自定义函数:

USE db_name; #选用数据库

CREATE FUNCTION function_name

RETU奇骏NS {ST哈弗ING|INTEGERAV4|REAL|DE科沃兹L} 再次回到值类型

routine_body(函数体)

小心:关于函数体

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

*/

USE hello;

极其表明

实例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; */

其实在协同数据的时候,也得以把须要一同的行数提收取来做参数,比较容易,这里暂无做。

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

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

实施同步的机能

实例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 */

7,自增列的identity_insert标志打开与关闭管理

一、基础

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 (使用旧表创建新表State of Qatar
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
注:列扩大后将不可能去除。DB第22中学列加上后数据类型也无法改良,唯一能纠正的是增添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 田野先生1 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 中但不在 TABLE第22中学的行并消释全部重复行而派生出四个结出表。当 ALL 随 EXCEPT 一齐使用时 (EXCEPT ALL卡塔尔,不免除重复行。
C: INTERSECT 运算符
INTEENVISIONSECT 运算符通过只囊括 TABLE1 和 TABLE2中都有个别行并扫除全体重复行而派生出二个结实表。当 ALL 随 INTE大切诺基SECT 一同利用时 (INTECRUISERSECT 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可用State of Qatar
法一: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, cState of Qatar 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:bState of Qatar
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 State of Qatar
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(State of Qatar卡塔尔(قطر‎>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最大的笔录的保有新闻(近似那样的用法能够用于论坛每月排名榜,每月销路好付加物深入分析,按学科成绩排行,等等.State of Qatar
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、表明:列出数据Curry具有的表名
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))
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 SE福特ExplorerVE锐界中央市直机关接循环写入数据
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('原始密码','加密后密码'State of Qatar = 1--相通;不然不等同 encrypt('原始密码'State of Qatar
select pwdencrypt('原始密码'卡塔尔国
select pwdcompare('原始密码','加密后密码'State of Qatar = 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表是不是等于:

参数表达

什么是SQL数据库:

SQL是Structured Query Language(构造化查询语言卡塔尔(قطر‎的缩写。SQL是专为数据库而建设布局的操作命令集,是一种功用齐全的数据库语言。在应用它时,只必要发出“做哪些”的授命,“如何做”是不用使用者思量的。SQL作用强盛、轻松命理术数、使用方便,已经济体改成了数据库操作的底工,何况以往大概全体的数据库均补助SQL。

职能如下

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  */
create function [dbo].[fn_test01]()RETURNS intASBEGIN declare @count int = 0 select @count = count(1) from dbo.table02 return @countEND

实例8:

DELIMITER //
CREATE PROCEDURE hello10()
begin
declare i int default 0;
haha:loop
SELECT i;
SET i = i + 1;
if i > 5 then
leave haha;
end if;
end loop;
end //
DELIMITER ;

3,同步的对象类型甚至对象

实例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,强逼覆盖的图景下,会drop掉指标表,幸免指标表与源表构造不等同,非强逼覆盖的情况下,即使字段不一样等,则抛出极度

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

1,代码布局优化,尤其鲜明和条例的构造2,数据同步的频率难点,对于多表的导入导出操作,信任于单线程,八个大表导出串行的话,恐怕存在功能上的瓶颈,如何依据表的数据量,尽可能平均地分配多四个线程中,提高功用3,特别团结清晰的拾贰分提醒以至日志记录,生成导出日志音讯。4,异构数据同步,MySQL《==》SQL Server《==》Oracle《==》PGSQL

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

帮助sqlserver2013以上版本

实例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; */

于是就有了本文,基于Python,飞快实现SQL Server的数据库之间的数据同步操作,前边又稍微扩充了一下,能够兑现区别服务器的数据库之间的表布局,表对应的数目,存款和储蓄进程,函数,客商自定义类型表的联合签字

实例9:

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();

常用命令:

select now(); 打印当前日期时间的。
select curdate();打印当前的日期。
select curtime();打印当前时间
select database();打印当前所在数据库的。
select version();打印mysql 版本号。
select user();  打印当前用户。 

始建数据库

create database [if not exists] 数据库名称 [default character set=编码方式]  

改善数据库 (数据库名称可以忽视,那个时候,语句对应于默许数据库。):

ALTER DATABASE db_name [ DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ]

翻看数据库音讯:

SHOW CREATE DATABASE [IF NOT EXISTS ] 数据库名称

查看现成的有着数据库

SHOW DATABASES;

转移使用的数据库(步入某一库卡塔尔

USE 库名;

剔除数据库:

DROP DATABASE  [IF NOT EXISTS ] 数据库名称

3.数据类型:
整型:
tinyint :占1个字节 范围,有符号 -128--127,无符号:0--255
smallint, 占2个字节,有符号 -32768---32767,无符号:0--65535
mediumint:占3个字节,有符号 -8388608-8388607 ,无符号 0-16777215
int: 占4个字节:有符号 -2147483648---2147483647,无符号0-4284967295
bigint,占8字节。
浮点型:
float 占4字节: 1.17e-38----3.4e+38
double 占8字节:
decimal 以字符串的样式表示浮点数。 "3.14"
字符型:
char(len) 定长 255字节。
varchar(len卡塔尔 变长的字符 占2五12个字符。
tinytext 0---255
text 65535
mediumtext 16777215
longtext 2的32次方
enum(value1,value2,value3.....value65535卡塔尔数据只可以取value1....valuen中的值。n<=65535

日期时间格式:
            date  2012-02-18
            datetime 日期时间型  2012-12-21  12:12:12

创办数量库表:

create table [if not exists] 数据表名(
    列定义:
    列名称 数据类型 【not null | null】[default value][auto_increment]【索引 unique key | primary key】
    列名称 数据类型 【not null | null】[default value][auto_increment]【索引 unique key | primary key】
    列名称 数据类型 【not null | null】[default value][auto_increment]【索引 unique key | primary key】
    列名称 数据类型 【not null | null】[default value][auto_increment]【索引 unique key | primary key】
    列名称 数据类型 【not null | null】[default value][auto_increment]【索引 unique key | primary key】
    列名称 数据类型 【not null | null】[default value][auto_increment]【索引 unique key | primary key】
    .........           
)[ENGINE=InnoDB DEFAULT CHARSET=utf8 ];

索引(约束)

INDEX (普通索引), PRIMARY KEY(主键索引) , UNIQUE (唯一索引), FOREIGN KEY(外键索引)

创建目录的时机

到那边大家曾经学会了创造目录,那么大家须求在哪些情况下树立目录呢?平时的话,在WHERE和JOIN中现身的列须求树立目录,但也不完全如此,因为MySQL只对<,<=,=,>,>=,BETWEEN,IN,以至某个时候的LIKE才会接收索引。

上面都说利用索引的益处,但过多的应用索引将会以致滥用。由此索引也是有它的缺点:

1.尽管索引大大进步了查询速度,相同的时候却会稳中有降更新表的速度,如对表实行INSERT、UPDATE和DELETE。因为更新表时,MySQL不止要保留数据,还要保存一下索引文件。

2.建设布局索引会占用磁盘空间的目录文件。平日景观这一个主题素材不太严重,但要是你在三个大表上开创了种种组成索引,索引文件的会膨胀非常的慢。

3.索引只是提升效能的一个要素,倘若您的MySQL有天命据量的表,就供给花时间商讨创建最精美的目录,或优化查询语句。

约束

auto_increment(自增长) , NULL(空) , NOT NULL(非空) , DEFAULT(设置默认值)

积存引擎 [ ENGINE ]

ARCHIVE  档案存储引擎。

BDB  带页面锁定的事务安全表。也称为BerkeleyDB。

CSV  值之间用逗号隔开的表。

FEDERATED  可以访问远程表的存储引擎。

HEAP  MEMORY (HEAP)存储引擎”。

***InnoDB  带行锁定和外键的事务安全表。

MEMORY  本表类型的数据只保存在存储器里。

MERGE  MyISAM表的集合,作为一个表使用。也称为MRG_MyISAM。

***MyISAM   二进制轻便式存储引擎,此引擎是MySQL所用的默认存储引擎。

NDBCLUSTER  成簇表,容错表,以存储器为基础的表。也称为NDB。

MySQL数据库MyISAM和InnoDB存款和储蓄引擎的比较

MySQL有八种囤积引擎,MyISAM和InnoDB是中间常用的三种。
MyISAM是MySQL的暗中认可存款和储蓄引擎,基于古板的ISAM类型,扶助全文字笔迹查证索,但不是专门的学存候全的,何况不扶助外键。每张MyISAM表存放在多少个文本中:frm 文件存放表格定义;数据文件是MYD (MYData卡塔尔;索引文件是MYI (MYIndex卡塔尔(قطر‎。

InnoDB是事务型引擎,帮衬回滚、崩溃恢复生机才干、多版本现身调整、ACID事务,辅助行级锁定(InnoDB表的行锁不是绝对的,固然在进行三个SQL语句时MySQL无法分明要扫描的界定,InnoDB表同样会锁全表,如like操作时的SQL语句),以致提供与Oracle类型一致的不加锁读取方式。InnoDB存款和储蓄它的表和索引在两个表空间中,表空间能够富含数个公文。

重大分歧:

MyISAM是非事务安全型的,而InnoDB是事情安全型的。
MyISAM锁的粒度是表级,而InnoDB扶助行级锁定。
MyISAM辅助全文类型索引,而InnoDB不扶植全文索引。
MyISAM相对简便易行,所以在成效上要优于InnoDB,Mini应用能够虚构使用MyISAM。
MyISAM表是保留成文件的格局,在跨平台的数额转移中使用MyISAM存款和储蓄会省去过多的辛勤。
InnoDB表比MyISAM表更安全,能够在有限扶植数据不会废弃的情状下,切换非事务表到事务表(alter table tablename type=innodb)。

查阅数据库表:

show tables ; //查看库内的所有表的情况

剔除数据表:

DROP  TABLE [ IF EXISTS ] tb_name [, tb_name2 ...] 

清空表

delete from 表名;

截段表

truncate table 表名;

修正数据表名称:

RENAME TABLE tb_name TO new_tb_name [ , tb2_name TO new_tb2_name ... ]

翻开数据表的协会:

    desc 数据表名; 

    SHOW COLUMNS FROM 数据表名;

插入记录

1.INSERT [INTO] tb_name [(col_name,...)] VALUES (val,...);

更新记录(单表跟新卡塔尔(قطر‎

UPDATE table_reference SET col_name1 = {expr|DEFAULT},[col_name1 = {expr|DEFAULT},....] [WHERE where_confition];

剔除记录(单表删除)

DELETE FROM tb_name [WHERE where_confition]

询问记录(单表查询)

SELECT select_exper[,select_exper,...]   FROM 数据表名 

WHWECRUISER 条件查询(对记录举行过滤 (能够应用MYSQL支持的函数和运算符))

运算符
    and 与   or  或   > 大于    >=  大于等于    <   小于      <=  小于等于    !=  不等于 <>  不等于  IN()  , NOT IN() , 

    BETWEEN ...AND , NOT BETWEEN ...AND


模糊查询 like
    %:代表0个或多个任意字符
    _:代表1个任意字符

聚合函数(聚合查询)(统计查询)
    count 数量查询
        select count(字段名) from 表名;

    sum 总和查询
        select sum(字段) from 表名;

    avg 平均数查询
        select avg(字段) from 表名;

    max最大值查询
        select max(字段) from 表名;

    min最小值查询
        select min(字段) from 表名;

查询结果分组  

[GROUP BY {col_name|position}[ASC|DESC]]

having 语句设置分组条件

SELECT col_name FROM tb_name GROUP BY col_name HAVING 条件

ORDER BY 排序

[ORDER BY {col_name|expr|position}ASC|DESC],...]

SELECT * FROM tb_name ORDER BY col_name [ASC |DESC], col_name2 [ASC | DESC]

LIMIT 限制结果返回数量

SELECT * FROM tb_name LIMIT [offset,] number

MYSQL 多表连接查询

子查询(可以在SELECT, INSERT , UPDATE,SET,DO里用到)

    SELECT * FROM tb_name where col_name = (SELECT ...);

    使用比较运算符的子查询(=,>,<,>=,<=,<>,!=,<=>)

    SELECT * FROM goods WHERE goods_price>=(SELECT ROUND(AVG(goods_price),2) FROM goods);

连接 MYSQL 在SELECT 语句、多表跟新,多表删除语句中支持JOIN 操作。{[INNER |CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN}

内连接[INNER JOIN]{仅显示符合连接条件的内容}
使用ON关键字来设定连接条件,也可以使用WHERE来代替。(通常使用On关键字来设定条件连接,使用WHERE关键字进行结果集记录过滤)

SELECT [col_name,...] FROM tb_name as t1 INNER JOIN tb_name2 AS t2 ON t1.col_name1= t2.col_name1;

外连接 {[LEFT | RIGHT] OUTER JOIN}
{左外连接显示左表的全部记录,及右表符合连接条件的记录}

SELECT [col_name,...] FROM tb_name as t1 LEFT OUTER JOIN tb_name2 AS t2 ON t1.col_name1= t2.col_name1;

{右外连接显示右表的全部记录,及左表符合连接条件的记录}

SELECT [col_name,...] FROM tb_name as t1 RIGHT OUTER JOIN tb_name2 AS t2 ON t1.col_name1= t2.col_name1;

联合查询(将两张表的记录合为一张表)

改进数据表

  添加删除列

  ALTER TABLE tb_name ADD [COLUMN] col_name<列名称> column_definition <列类型,列定义> [FIRST <放到第一>| AFTER col_name<放到该列之后> ] 

  添加多列

  ALTER TABLE tb_name ADD [COLUMN] (col_name column_definition,...)

  删除列

  ALTER TABLE tb_name DROP [COLUMN] col_name

  删除多列

  ALTER TABLE tb_name DROP [COLUMN] col_name ,DROP col_name2,...

  动态添加主键约束

  ALTER TABLE tb_name ADD [CONSTRAINT [symbol]<用途给主键起名称>] PRIMARY KEY [index_type](index_col_name)

  添加唯一约束

  ALTER TABLE tb_name ADD [CONSTRINT [symbol]] UNIQUE [INDEX | KEY ] [index_name] [index_type](index_col_name)

  添加外键约束 //外键不用关注。

  ALTER TABLE tb_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] [index_type] (index_col_name) reference_definition () tb_name1 (col_name)

  添加/删除默认约束

  ALTER  TABLE tb_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT }

  删除主键约束

  ALTER TABLE TB_name DROP PRIMARY KEY

  删除唯一约束

  ALTER TABLE tb_name DROP {INDEX|KEY}  col_index_name

  删除外键约束

  ALTER TABLE tb_name DROP FOREIGN KEY fr_symbol

  修改列定义

  ALTER TABLE tb_name MODIFY [COLUMN] col_name col_name column_definition [FIRST | AFTER col_name]

  修改列名称

  ALTER TABLE tb_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]

SQL 功能 分类:

DDL 数据定义语言:创建,删除,修改数据库以及数据库对象
DML 数据管理语言:增 删  改 记录的语句;
DQL 数据查询语言:select
DCL 数据控制语言:用户的管理,权限的分配.

表以致数额同步

实例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; */

说明:

实例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; */

# -*- coding: utf-8 -*-# !/usr/bin/env python3__author__ = 'MSSQL123'__date__ = '2019-06-07 09:36'import osimport sysimport timeimport datetimeimport pymssqlfrom decimal import Decimalusage = ''' -----parameter explain----- source database parameter -s_h : soure database host ----- must require parameter -s_i : soure database instace name ----- default instance name MSSQL -s_d : soure database name ----- must require parameter -s_u : soure database login ----- default windows identifier -s_p : soure database login password ----- must require when s_u is not null -s_P : soure database instance port ----- default port 1433 target database parameter -t_h : target database host ----- must require parameter -t_i : target database instace name ----- default instance name MSSQL -t_d : target database name ----- must require parameter -t_u : target database login ----- default windows identifier -t_p : target database login password ----- must require when s_u is not null -t_P : target database instance port ----- default port 1433 sync object parameter -obj_type : table or sp or function or other databse object ----- tab or sp or fn or tp -obj : table|sp|function|type name ----- whick table or sp sync overwirte parameter -f : force overwirte target database object ----- F or N --help: help document Example: python DataTransfer.py -s_h=127.0.0.1 -s_P=1433 -s_i="MSSQL" -s_d="DB01" -obj_type="tab" -obj="dbo.t1,dbo.t2" -t_h=127.0.0.1 -t_P=1433 -t_i="MSSQL" -t_d="DB02" -f="Y" python DataTransfer.py -s_h=127.0.0.1 -s_P=1433 -s_i="MSSQL" -s_d="DB01" -obj_type="sp" -obj="dbo.sp1,dbo.sp2" -t_h=127.0.0.1 -t_P=1433 -t_i="MSSQL" -t_d="DB02" -f="Y" '''class SyncDatabaseObject(object): # source databse s_h = None s_i = None s_P = None s_u = None s_p = None s_d = None # obj type s_obj_type = None # sync objects s_obj = None # target database t_h = None t_i = None t_P = None t_u = None t_p = None t_d = None f = None file_path = None def __init__(self, *args, **kwargs): for k, v in kwargs.items(): setattr(self, k, v) # connect to sqlserver def get_connect(self, _h, _i, _P, _u, _p, _d): cursor = False try: if (_u) and (_p): conn = pymssql.connect(host=_h, server=_i, port=_P, user=_u, password=_p, database=_d) else: conn = pymssql.connect(host=_h, server=_i, port=_P, database=_d) if (conn): return conn except: raise return conn # check connection def validated_connect(self, _h, _i, _P, _u, _p, _d): if not (self.get_connect(_h, _i, _P, _u, _p, _d)): print("connect to " + str(_h) + " failed,please check you parameter") exit(0) ''' this is supposed to be a valid object name just like xxx_name,or dbo.xxx_name,or [schema].xxx_name or schema.[xxx_name] then transfer this kind of valid object name to format object name like [dbo].[xxx_name](give a default dbo schema name when no schema name) other format object name consider as unvalid,will be rasie error in process format object name 1,xxx_name ====== [dbo].[xxx_name] 2,dbo.xxx_name ====== [dbo].[xxx_name] 3,[schema].xxx_name ====== [dbo].[xxx_name] 3,schema.xxx_name ====== [schema].[xxx_name] 4,[schema].[xxx_name] ====== [schema].[xxx_name] 5,[schema].[xxx_name ====== rasie error format message ''' @staticmethod def format_object_name(name): format_name = "" if ("." in name): schema_name = name[0:name.find(".")] object_name = name[name.find(".") + 1:] if not ("[" in schema_name): schema_name = "[" + schema_name + "]" if not ("[" in object_name): object_name = "[" + object_name + "]" format_name = schema_name + "." + object_name else: if ("[" in name): format_name = "[dbo]." + name else: format_name = "[dbo]." + "[" + name + "]" return format_name ''' check user input object is a valid object ''' def exits_object(self, conn, name): conn = conn cursor_source = conn.cursor() # get object by name from source db sql_script = r'''select top 1 1 from ( select concat(QUOTENAME(schema_name(schema_id)),'.',QUOTENAME(name)) as obj_name from sys.objects union all select concat(QUOTENAME(schema_name(schema_id)),'.',QUOTENAME(name)) as obj_name from sys.types )t where obj_name = '{0}' '''.format(self.format_object_name(name)) cursor_source.execute(sql_script) result = cursor_source.fetchall() if not result: return 0 else: return 1 conn.cursor.close() conn.close() # table variable sync def sync_table_variable(self, tab_name, is_reference): conn_source = self.get_connect(self.s_h, self.s_i, self.s_P, self.s_u, self.s_p, self.s_d) conn_target = self.get_connect(self.t_h, self.t_i, self.t_P, self.t_u, self.t_p, self.t_d) cursor_source = conn_source.cursor() cursor_target = conn_target.cursor() if (self.exits_object(conn_source, self.format_object_name(tab_name)))  0: pass else: print("----------------------- warning message -----------------------") print("--------warning: object " + tab_name + " not existing in source database ------------") print("----------------------- warning message -----------------------") print() return exists_in_target = 0 sql_script = r'''select top 1 1 from sys.table_types tp where is_user_defined = 1 and concat(QUOTENAME(schema_name(tp.schema_id)),'.',QUOTENAME(tp.name)) = '{0}' '''  .format((self.format_object_name(tab_name))) # if the table schema exists in target server,skip cursor_target.execute(sql_script) exists_in_target = cursor_target.fetchone() # weather exists in target server database if (self.f == "Y"): if (is_reference != "Y"): # skiped,table type can not drop when used by sp sql_script = r''' if OBJECT_ID('{0}') is not null drop type {0} '''.format(self.format_object_name(tab_name)) cursor_target.execute(sql_script) conn_target.commit() else: if exists_in_target: print("----------------------- warning message -----------------------") print("the target table type " + tab_name + " exists ,skiped sync table type from source") print("----------------------- warning message -----------------------") print() return sql_script = r''' DECLARE @SQL NVARCHAR(MAX) = '' SELECT @SQL = 'CREATE TYPE ' + '{0}' + 'AS TABLE' + CHAR(13) + '(' + CHAR(13) + STUFF(( SELECT CHAR(13) + ' , [' + c.name + '] ' + CASE WHEN c.is_computed = 1 THEN 'AS ' + OBJECT_DEFINITION(c.[object_id], c.column_id) ELSE CASE WHEN c.system_type_id != c.user_type_id THEN '[' + SCHEMA_NAME(tp.[schema_id]) + '].[' + tp.name + ']' ELSE '[' + UPPER(y.name) + ']' END + CASE WHEN y.name IN ('varchar', 'char', 'varbinary', 'binary') THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(5)) END + ')' WHEN y.name IN ('nvarchar', 'nchar') THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / 2 AS VARCHAR(5)) END + ')' WHEN y.name IN ('datetime2', 'time2', 'datetimeoffset') THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')' WHEN y.name = 'decimal' THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')' ELSE '' END + CASE WHEN c.collation_name IS NOT NULL AND c.system_type_id = c.user_type_id THEN ' COLLATE ' + c.collation_name ELSE '' END + CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END + CASE WHEN c.default_object_id != 0 THEN ' CONSTRAINT [' + OBJECT_NAME(c.default_object_id) + ']' + ' DEFAULT ' + OBJECT_DEFINITION(c.default_object_id) ELSE '' END END From sys.table_types tp Inner join sys.columns c on c.object_id = tp.type_table_object_id Inner join sys.types y ON y.system_type_id = c.system_type_id WHERE tp.is_user_defined = 1 and y.name'sysname' and concat(QUOTENAME(schema_name(tp.schema_id)),'.',QUOTENAME(tp.name)) = '{0}' ORDER BY c.column_id FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 7, ' ') + ');' select @SQL as script '''.format(self.format_object_name(self.format_object_name((tab_name)))) cursor_target = conn_target.cursor() cursor_source.execute(sql_script) row = cursor_source.fetchone() try: if not exists_in_target: # execute the script on target server cursor_target.execute(str(row[0])) # drop current stored_procudre if exists conn_target.commit() print("*************table type " + self.format_object_name(tab_name) + " synced *********************") print() # give a blank row when finish except: print("----------------------- error message -----------------------") print("-----------table type " + self.format_object_name(tab_name) + " synced error ---------------") print("----------------------- error message -----------------------") print() # raise cursor_source.close() conn_source.close() cursor_target.close() conn_target.close() # schema sync def sync_schema(self): conn_source = self.get_connect(self.s_h, self.s_i, self.s_P, self.s_u, self.s_p, self.s_d) conn_target = self.get_connect(self.t_h, self.t_i, self.t_P, self.t_u, self.t_p, self.t_d) cursor_source = conn_source.cursor() cursor_target = conn_target.cursor() arr_schema = [] # get all table in database when not define table name schema_result = cursor_source.execute(r''' select name from sys.schemas where schema_id4 and schema_id16384 ''') for row in cursor_source.fetchall(): cursor_target.execute(r''' if not exists(select * from sys.schemas where name = '{0}') begin exec('create schema [{0}]') end '''.format(str(row[0]))) conn_target.commit() cursor_source.close() conn_source.close() cursor_target.close() conn_target.close() def sync_table_schema_byname(self, tab_name, is_reference): conn_source = self.get_connect(self.s_h, self.s_i, self.s_P, self.s_u, self.s_p, self.s_d) conn_target = self.get_connect(self.t_h, self.t_i, self.t_P, self.t_u, self.t_p, self.t_d) cursor_source = conn_source.cursor() cursor_target = conn_target.cursor() if (self.exits_object(conn_source, self.format_object_name(tab_name)) == 0): print("----------------------- warning message -----------------------") print("---------------warning: object " + tab_name + " not existing in source database ----------------") print("----------------------- warning message -----------------------") print() return # if exists a reference table for sp,not sync the table agagin if (self.exits_object(conn_target, self.format_object_name(tab_name))  0): if (self.f != "Y"): print("----------------------- warning message -----------------------") print("---------------warning: object " + tab_name + " existing in target database ----------------") print("----------------------- warning message -----------------------") print() return sql_script = r''' select top 1 1 from sys.tables where type_desc = 'USER_TABLE' and concat(QUOTENAME(schema_name(schema_id)),'.',QUOTENAME(name)) = '{0}' '''.format((self.format_object_name(tab_name))) # if the table schema exists in target server,skip cursor_target.execute(sql_script) exists_in_target = cursor_target.fetchone() if exists_in_target: if (self.f == "Y"): if (is_reference != "Y"): cursor_target.execute("drop table {0}".format(tab_name)) else: print("----------------------- warning message -----------------------") print("the target table " + tab_name + " exists ,skiped sync table schema from source") print("----------------------- warning message -----------------------") print() return sql_script = r''' DECLARE @object_name SYSNAME , @object_id INT SELECT @object_name = '[' + s.name + '].[' + o.name + ']' , @object_id = o.[object_id] FROM sys.objects o WITH (NOWAIT) JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id] WHERE QUOTENAME(s.name) + '.' + QUOTENAME(o.name) = '{0}' AND o.[type] = 'U' AND o.is_ms_shipped = 0 DECLARE @SQL NVARCHAR(MAX) = '' ;WITH index_column AS ( SELECT ic.[object_id] , ic.index_id , ic.is_descending_key , ic.is_included_column , c.name FROM sys.index_columns ic WITH (NOWAIT) JOIN sys.columns c WITH (NOWAIT) ON ic.[object_id] = c.[object_id] AND ic.column_id = c.column_id WHERE ic.[object_id] = @object_id ), fk_columns AS ( SELECT k.constraint_object_id , cname = c.name , rcname = rc.name FROM sys.foreign_key_columns k WITH (NOWAIT) JOIN sys.columns rc WITH (NOWAIT) ON rc.[object_id] = k.referenced_object_id AND rc.column_id = k.referenced_column_id JOIN sys.columns c WITH (NOWAIT) ON c.[object_id] = k.parent_object_id AND c.column_id = k.parent_column_id WHERE k.parent_object_id = @object_id ) SELECT @SQL = 'CREATE TABLE ' + @object_name + '' + '(' + '' + STUFF(( SELECT '' + ', [' + c.name + '] ' + CASE WHEN c.is_computed = 1 THEN 'AS ' + cc.[definition] ELSE UPPER(tp.name) + CASE WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary', 'text') THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(5)) END + ')' WHEN tp.name IN ('nvarchar', 'nchar') THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / 2 AS VARCHAR(5)) END + ')' WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset') THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')' WHEN tp.name = 'decimal' THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')' ELSE '' END + CASE WHEN c.collation_name IS NOT NULL THEN ' COLLATE ' + c.collation_name ELSE '' END + CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END + CASE WHEN dc.[definition] IS NOT NULL THEN ' DEFAULT' + dc.[definition] ELSE '' END + CASE WHEN ic.is_identity = 1 THEN ' IDENTITY(' + CAST(ISNULL( /*ic.seed_value*/ 1, '0') AS CHAR(1)) + ',' + CAST(ISNULL(ic.increment_value, '1') AS CHAR(1)) + ')' ELSE '' END END + '' FROM sys.columns c WITH (NOWAIT) JOIN sys.types tp WITH (NOWAIT) ON c.user_type_id = tp.user_type_id LEFT JOIN sys.computed_columns cc WITH (NOWAIT) ON c.[object_id] = cc.[object_id] AND c.column_id = cc.column_id LEFT JOIN sys.default_constraints dc WITH (NOWAIT) ON c.default_object_id != 0 AND c.[object_id] = dc.parent_object_id AND c.column_id = dc.parent_column_id LEFT JOIN sys.identity_columns ic WITH (NOWAIT) ON c.is_identity = 1 AND c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id WHERE c.[object_id] = @object_id ORDER BY c.column_id FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '' + ' ') + ISNULL((SELECT '' + ', CONSTRAINT [' + k.name + '] PRIMARY KEY (' + (SELECT STUFF(( SELECT ', [' + c.name + '] ' + CASE WHEN ic.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END FROM sys.index_columns ic WITH (NOWAIT) JOIN sys.columns c WITH (NOWAIT) ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id WHERE ic.is_included_column = 0 AND ic.[object_id] = k.parent_object_id AND ic.index_id = k.unique_index_id FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')) + ')' + '' FROM sys.key_constraints k WITH (NOWAIT) WHERE k.parent_object_id = @object_id AND k.[type] = 'PK'), '') + ')' + '' + ISNULL((SELECT ( SELECT '' + 'ALTER TABLE ' + @object_name + ' WITH' + CASE WHEN fk.is_not_trusted = 1 THEN ' NOCHECK' ELSE ' CHECK' END + ' ADD CONSTRAINT [' + fk.name + '] FOREIGN KEY(' + STUFF(( SELECT ', [' + k.cname + ']' FROM fk_columns k WHERE k.constraint_object_id = fk.[object_id] and 1=2 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')' + ' REFERENCES [' + SCHEMA_NAME(ro.[schema_id]) + '].[' + ro.name + '] (' + STUFF(( SELECT ', [' + k.rcname + ']' FROM fk_columns k WHERE k.constraint_object_id = fk.[object_id] FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')' + CASE WHEN fk.delete_referential_action = 1 THEN ' ON DELETE CASCADE' WHEN fk.delete_referential_action = 2 THEN ' ON DELETE SET NULL' WHEN fk.delete_referential_action = 3 THEN ' ON DELETE SET DEFAULT' ELSE '' END + CASE WHEN fk.update_referential_action = 1 THEN ' ON UPDATE CASCADE' WHEN fk.update_referential_action = 2 THEN ' ON UPDATE SET NULL' WHEN fk.update_referential_action = 3 THEN ' ON UPDATE SET DEFAULT' ELSE '' END + '' + 'ALTER TABLE ' + @object_name + ' CHECK CONSTRAINT [' + fk.name + ']' + '' FROM sys.foreign_keys fk WITH (NOWAIT) JOIN sys.objects ro WITH (NOWAIT) ON ro.[object_id] = fk.referenced_object_id WHERE fk.parent_object_id = @object_id FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)')), '') + ISNULL(((SELECT '' + 'CREATE' + CASE WHEN i.is_unique = 1 THEN ' UNIQUE' ELSE '' END + ' NONCLUSTERED INDEX [' + i.name + '] ON ' + @object_name + ' (' + STUFF(( SELECT ', [' + c.name + ']' + CASE WHEN c.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END FROM index_column c WHERE c.is_included_column = 0 AND c.index_id = i.index_id FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')' + ISNULL('' + 'INCLUDE (' + STUFF(( SELECT ', [' + c.name + ']' FROM index_column c WHERE c.is_included_column = 1 AND c.index_id = i.index_id FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')', '') + '' FROM sys.indexes i WITH (NOWAIT) WHERE i.[object_id] = @object_id AND i.is_primary_key = 0 AND i.[type] = 2 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ), '') select @SQL as script '''.format(self.format_object_name(tab_name)) cursor_target = conn_target.cursor() cursor_source.execute(sql_script) row = cursor_source.fetchone() if not row[0]: return try: cursor_target.execute(row[0]) # drop current table schema if exists conn_target.commit() print("*************schema " + self.format_object_name(tab_name) + " synced *************") print() # give a blank row when finish except: print("----------------------- warning message -----------------------") print("-----------schema " + self.format_object_name(tab_name) + " synced failed---------------") print("----------------------- warning message -----------------------") print() cursor_source.close() conn_source.close() cursor_target.close() conn_target.close() def get_table_column(self, conn, tab_name): column_names = "" conn = conn cursor_source = conn.cursor() # get object by name from source db sql_script = r'''select name from sys.columns where object_id = object_id('{0}') and is_computed=0 order by object_id '''.format(self.format_object_name(tab_name)) cursor_source.execute(sql_script) result = cursor_source.fetchall() for row in result: column_names = column_names + row[0] + "," return column_names[0:len(column_names) - 1] conn.cursor.close() conn.close() def sync_table_schema(self): #default not sync by referenced other object is_reference = "N" conn_source = self.get_connect(self.s_h, self.s_i, self.s_P, self.s_u, self.s_p, self.s_d) conn_target = self.get_connect(self.t_h, self.t_i, self.t_P, self.t_u, self.t_p, self.t_d) cursor_source = conn_source.cursor() cursor_target = conn_target.cursor() arr_table = [] if (self.s_obj): for tab_name in self.s_obj.split(","): if (tab_name) and (self.exits_object(conn_source, tab_name)0): self.sync_table_schema_byname(tab_name, is_reference) else: print("----------------------- warning message -----------------------") print("-----------schema " + self.format_object_name(tab_name) + " not existing in source database---------------") print("----------------------- warning message -----------------------") print() else: # sync all tables # get all table in database when not define table name sql_script = ''' SELECT QUOTENAME(s.name)+'.'+ QUOTENAME(o.name) FROM sys.objects o WITH (NOWAIT) JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id] WHERE o.[type] = 'U' AND o.is_ms_shipped = 0 ''' cursor_source.execute(sql_script) for row in cursor_source.fetchall(): self.sync_table_schema_byname(str(row[0]), is_reference) # sync data from soure table to target table def sync_table_data(self): conn_source = self.get_connect(self.s_h, self.s_i, self.s_P, self.s_u, self.s_p, self.s_d) conn_target = self.get_connect(self.t_h, self.t_i, self.t_P, self.t_u, self.t_p, self.t_d) cursor_source = conn_source.cursor() cursor_target = conn_target.cursor() arr_table = [] if (self.s_obj): arr_table = self.s_obj.split(',') for tab_name in arr_table: if (self.exits_object(conn_target, self.format_object_name(tab_name)) == 0): arr_table.remove(tab_name) print("----------------- warning message -----------------------") print("----------------- warning: table " + tab_name + " not existing in target database ---------------------") print("----------------- warning message -----------------------") else: # get all table in database when not define table name tab_result = cursor_source.execute(r''' SELECT QUOTENAME(s.name)+'.'+ QUOTENAME(o.name) FROM sys.objects o WITH (NOWAIT) JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id] WHERE o.[type] = 'U' AND o.is_ms_shipped = 0 ''') for row in cursor_source.fetchall(): arr_table.append(str(row[0])) insert_columns = "" insert_columns = self.get_table_column(conn_source, tab_name) for tab_name in arr_table: if (self.f != "Y"): sql_script = "select top 1 {0} from {1} ".format(insert_columns, tab_name) # if exists data in target table,break cursor_target.execute(sql_script) exists = cursor_target.fetchone() if exists: print("----------------------- warning message -----------------------") print("the target table " + tab_name + " exists data,skiped sync table type from source") print("----------------------- warning message -----------------------") print() continue else: sql_script = "truncate table {0} ".format(tab_name) # if exists data in target table,break cursor_target.execute(sql_script) conn_target.commit() insert_columns = "" insert_columns = self.get_table_column(conn_source, tab_name) insert_prefix = "" # weather has identity column cursor_source.execute(r'''select 1 from sys.columns where object_id = OBJECT_ID('{0}') and is_identity =1 '''.format(tab_name)) exists_identity = None exists_identity = cursor_source.fetchone() if (exists_identity): insert_prefix = "set identity_insert {0} on; ".format(tab_name) # data source insert_sql = "" values_sql = "" current_row = "" counter = 0 sql_script = r''' select {0} from {1} '''.format(insert_columns, tab_name) cursor_source.execute(sql_script) # create insert columns ''' for field in cursor_source.description: insert_columns = insert_columns + str(field[0]) + "," insert_columns = insert_columns[0:len(insert_columns) - 1] ''' insert_prefix = insert_prefix + "insert into {0} ({1}) values ".format(tab_name, insert_columns) for row in cursor_source.fetchall(): counter = counter + 1 for key in row: if (str(key) == "None"): current_row = current_row + r''' null, ''' else: if (type(key) is datetime.datetime): current_row = current_row + r''' '{0}', '''.format(str(key)[0:23]) elif (type(key) is str): # 我槽!!!,这里又有一个坑: current_row = current_row + r''' '{0}', '''.format( key.replace("'", "''").replace('u0000', '').replace('x00', '')) elif (type(key) is Decimal): d = Decimal(key) s = '{0:f}'.format(d) current_row = current_row + r''' '{0}', '''.format(s) elif (type(key) is bytes): # print(hex(int.from_bytes(key, 'big', signed=True) )) current_row = current_row + r''' {0}, '''.format( hex(int.from_bytes(key, 'big', signed=False))) else: current_row = current_row + r''' '{0}', '''.format(key) current_row = current_row[0:len(current_row) - 2] # remove the the last one char "," values_sql = values_sql + "(" + current_row + ")," current_row = "" # execute the one batch when if (counter == 1000): insert_sql = insert_prefix + values_sql insert_sql = insert_sql[0:len(insert_sql) - 1] # remove the the last one char "," if (exists_identity): insert_sql = insert_sql + " ;set identity_insert {0} off;".format(tab_name) try: cursor_target.execute(insert_sql) except: print( "----------------------error " + tab_name + " data synced failed-------------------------") raise conn_target.commit() insert_sql = "" values_sql = "" current_row = "" counter = 0 print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + "*************** " + self.format_object_name( tab_name) + " " + str(1000) + " rows synced *************") if (values_sql): insert_sql = insert_prefix + values_sql insert_sql = insert_sql[0:len(insert_sql) - 1] # remove the the last one char "," if (exists_identity): insert_sql = insert_sql + " ; set identity_insert {0} off;".format(tab_name) # execute the last batch try: cursor_target.execute(insert_sql) except: print("------------------error " + tab_name + " data synced failed------------------------") raise conn_target.commit() insert_sql = "" values_sql = "" current_row = "" print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + "*************** " + self.format_object_name( tab_name) + " " + str( counter) + " rows synced *************") print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + "----------------synced " + self.format_object_name( tab_name) + " data finished---------------") print() cursor_source.close() conn_source.close() cursor_target.close() conn_target.close() def sync_dependent_object(self, obj_name): # 强制覆盖,不需要对依赖对象生效,如果是因为属于依赖对象而被同步的,先检查target中是否存在,如果存在就不继续同步,这里打一个标记来实现 is_refernece = "Y" conn_source = self.get_connect(self.s_h, self.s_i, self.s_P, self.s_u, self.s_p, self.s_d) conn_target = self.get_connect(self.t_h, self.t_i, self.t_P, self.t_u, self.t_p, self.t_d) cursor_source = conn_source.cursor() cursor_target = conn_target.cursor() ''' find dependent objects if exists dependent objects,sync Dependent objects objects in advance ''' sql_check_dependent = r''' SELECT * FROM ( SELECT distinct rtrim(lower(s.type)) COLLATE Chinese_PRC_CI_AS as obj_type, QUOTENAME(d.referenced_schema_name)+'.'+QUOTENAME(d.referenced_entity_name) COLLATE Chinese_PRC_CI_AS as obj FROM sys.dm_sql_referenced_entities('{0}','OBJECT') as d inner join sys.sysobjects s on s.id = d.referenced_id union all SELECT distinct rtrim(lower(d.referenced_class_desc)) COLLATE Chinese_PRC_CI_AS as obj_type, QUOTENAME(d.referenced_schema_name)+'.'+QUOTENAME(d.referenced_entity_name) COLLATE Chinese_PRC_CI_AS as obj FROM sys.dm_sql_referenced_entities('{0}','OBJECT') as d inner join sys.types s on s.user_type_id = d.referenced_id )t '''.format(self.format_object_name(obj_name)) cursor_source.execute(sql_check_dependent) result = cursor_source.fetchall() for row in result: if row[1]: if (row[0] == "u"): if (row[1]): self.sync_table_schema_byname(row[1], is_refernece) elif (row[0] == "fn" or row[0] == "if"): if (row[1]): self.sync_procudre_by_name("f", row[1], is_refernece) elif (row[0] == "type"): if (row[1]): self.sync_table_variable(row[1], is_refernece) def sync_procudre_by_name(self, type, obj_name, is_reference): conn_source = self.get_connect(self.s_h, self.s_i, self.s_P, self.s_u, self.s_p, self.s_d) conn_target = self.get_connect(self.t_h, self.t_i, self.t_P, self.t_u, self.t_p, self.t_d) cursor_source = conn_source.cursor() cursor_target = conn_target.cursor() if (self.exits_object(conn_source, self.format_object_name(obj_name)) == 0): print("---------------warning message----------------") print("---------------warning: object " + obj_name + " not existing in source database ----------------") print("---------------warning message----------------") print() return if (self.exits_object(conn_target, self.format_object_name(obj_name))  0): if (self.f != "Y"): print("---------------warning message----------------") print("---------------warning: object " + obj_name + " existing in target database ----------------") print("---------------warning message----------------") print() return ''' 本来想直接生成删除语句的: 这里有一个该死的转义,怎么都弄不好,中午先去吃饭吧, 下午回来想了一下,换一种方式,不要死磕转义问题了 sql_script = select 'if object_id('+''''+QUOTENAME(schema_name(uid))+ '' + QUOTENAME(name)+''''+') is not null ' +' drop proc '+QUOTENAME(schema_name(uid))+ '.' + QUOTENAME(name) , OBJECT_DEFINITION(id) from sys.sysobjects where xtype = 'P' and uid not in (16,19) ''' sql_script = r''' select QUOTENAME(schema_name(uid))+'.'+QUOTENAME(name), OBJECT_DEFINITION(id) from sys.sysobjects where xtype in ('P','IF','FN') and uid not in (16,19) ''' if (obj_name): sql_script = sql_script + " and QUOTENAME(schema_name(uid))+ '.' + QUOTENAME(name) ='{0}' ".format( self.format_object_name(obj_name)) cursor_source.execute(sql_script) row = cursor_source.fetchone() try: if type == "f": sql_script = r''' if object_id('{0}') is not null drop function {0} '''.format(self.format_object_name(row[0])) elif type == "p": sql_script = r''' if object_id('{0}') is not null drop proc {0} '''.format(self.format_object_name(row[0])) cursor_target.execute(sql_script) # drop current stored_procudre if exists conn_target.commit() # sync dependent object if (is_reference != "N"): self.sync_dependent_object(self.format_object_name(row[0])) # sync object it self cursor_target.execute(str(row[1])) # execute create stored_procudre script conn_target.commit() print("*************sync sp: " + self.format_object_name(row[0]) + " finished *****************") print() except: print("---------------error message----------------") print("------------------ sync " + row[0] + "sp error --------------------------") print("---------------error message----------------") print() cursor_source.close() conn_source.close() cursor_target.close() conn_target.close() def sync_procudre(self, type): is_reference = "N" conn_source = self.get_connect(self.s_h, self.s_i, self.s_P, self.s_u, self.s_p, self.s_d) conn_target = self.get_connect(self.t_h, self.t_i, self.t_P, self.t_u, self.t_p, self.t_d) cursor_source = conn_source.cursor() cursor_target = conn_target.cursor() if (self.s_obj): for proc_name in self.s_obj.split(","): self.sync_dependent_object(proc_name) self.sync_procudre_by_name(type, proc_name, is_reference) # sync all sp and function else: sql_script = r''' select QUOTENAME(schema_name(uid))+'.'+QUOTENAME(name), OBJECT_DEFINITION(id) from sys.sysobjects where xtype = upper('{0}') and uid not in (16,19) '''.format(type) cursor_source.execute(sql_script) for row in cursor_source.fetchall(): self.sync_dependent_object(row[0]) self.sync_procudre_by_name(type, row[0], is_reference)if __name__ == "__main__": ''' sync = SyncDatabaseObject(s_h="127.0.0.1", s_i = "sql2017", s_P = 49744, s_d="DB01", t_h="127.0.0.1", t_i="sql2017", t_P=49744, t_d="DB02", s_obj_type = "sp", s_obj = "dbo.sp_test01", f="Y") sync.sync_procudre("p") ''' p_s_h = "" p_s_i = "MSSQL" p_s_P = 1433 p_s_d = "" p_s_u = None p_s_p = None p_s_obj = "" p_type = "" p_t_s = "" p_t_i = "MSSQL" p_t_P = "1433" p_t_d = "" p_t_u = None p_t_p = None # force conver target database object,default not force cover target database object p_f = "N" # sync obj type table|sp p_obj_type = None # sync whick database object p_obj = None if len(sys.argv) == 1: print(usage) sys.exit(1) elif sys.argv[1] == '--help': print(usage) sys.exit() elif len(sys.argv) = 2: for i in sys.argv[1:]: _argv = i.split('=') # source server name if _argv[0] == '-s_h': p_s_h = _argv[1] # source server instance name if _argv[0] == '-s_i': if (_argv[1]): p_s_i = _argv[1] # source server instance PORT if _argv[0] == '-s_P': if (_argv[1]): p_s_P = _argv[1] # source database name if _argv[0] == '-s_d': p_s_d = _argv[1] if _argv[0] == '-s_u': p_s_u = _argv[1] if _argv[0] == '-s_p': p_s_p = _argv[1] if _argv[0] == '-t_h': p_t_h = _argv[1] if _argv[0] == '-t_i': if (_argv[1]): p_t_i = _argv[1] if _argv[0] == '-t_P': if (_argv[1]): p_t_P = _argv[1] if _argv[0] == '-t_d': p_t_d = _argv[1] if _argv[0] == '-t_u': p_t_u = _argv[1] if _argv[0] == '-t_p': p_t_p = _argv[1] if _argv[0] == '-f': if (_argv[1]): p_f = _argv[1] # object type if _argv[0] == '-obj_type': if not (_argv[1]): print("-obj_type can not be null ") exit(0) else: p_obj_type = _argv[1] # object name if _argv[0] == '-obj': if (_argv[1]): p_obj = _argv[1] # require para if p_s_h.strip() == "": print("source server host cannot be null") exit(0) if p_s_d.strip() == "": print("source server host database name cannot be null") exit(0) if p_t_h.strip() == "": print("target server host cannot be null") exit(0) if p_t_d.strip() == "": print("target server host database name cannot be null") exit(0) sync = SyncDatabaseObject(s_h=p_s_h, s_i=p_s_i, s_P=p_s_P, s_d=p_s_d, s_u=p_s_u, s_p=p_s_p, s_obj=p_obj, t_h=p_t_h, t_i=p_t_i, t_P=p_t_P, t_d=p_t_d, t_u=p_t_u, t_p=p_t_p, f=p_f) sync.validated_connect(p_s_h, p_s_i, p_s_P, p_s_d, p_s_u, p_s_p) sync.validated_connect(p_t_h, p_t_i, p_t_P, p_t_d, p_t_u, p_t_p) if (p_f.upper() == "Y"): confirm = input("confirm you want to overwrite the target object ") if confirm.upper() != "Y": exit(0) print("-------------------------- sync begin ----------------------------------") print() if (p_obj_type == "tab"): # sync schema sync.sync_schema() # sync table schema sync.sync_table_schema() # sync data sync.sync_table_data() elif (p_obj_type == "sp"): # sync schema sync.sync_schema() # sync sp sync.sync_procudre("p") elif (p_obj_type == "fn"): # sync schema sync.sync_schema() # sync sp sync.sync_procudre("fn") elif (p_obj_type == "tp"): # sync schema sync.sync_schema() # sync sp sync.sync_table_variable() else: print("-obj_type is not validated") print() print("-------------------------- sync finish ----------------------------------")

实例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,必要筛选出来非计算列字段,insert语句只好是非总结列字段

不或然获得不错的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、概念:

大家平日会对数据库实行CU大切诺基D(增加和删除改查),当大家输入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);

缘由

在乎:假若在调用的时候从不参数,那么会系统报错

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

1,加密的积累进程如故函数是心有余而力不足落实同台的,因为无法转移成立对象的剧本1,table type的一路也是八个蛋疼的长河,这段日子帮忙,可是援救的并倒霉,原因是开创table type在此以前,先删除正视于table type的对象,不然不可能删除与创设。

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 事件探查器'State of Qatar
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到第三十八个记录。

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( 'SQ英雄结盟EDB ', 'sql服务器名 '; '顾客名 '; '密码 ',数据库名.dbo.表名卡塔尔

--生开销地球表面

select * into 表 from openrowset( 'SQ英雄结盟EDB ', 'sql服务器名 '; '客户名 '; '密码 ',数据库名.dbo.表名State of Qatar

--把地点表导入远程表

insert openrowset( 'SQ英雄结盟EDB ', 'sql服务器名 '; '客户名 '; '密码 ',数据库名.dbo.表名State of Qatar

select *from 本地表

--更新本地球表面

update b

set b.列A=a.列A

from openrowset( 'SQ英雄联盟EDB ', '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,lengthState of Qatar取子串,字符串的下标是从“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码。

SQLServer2003同步复制本领完成步骤

一、 预备职业

1.颁发服务器,订阅服务器都成立一个同名的windows客户,并设置同一的密码,做为揭橥快速照相文件夹的得力访谈顾客

--管理工科具

--Computer管理

--客商和组

--右键顾客

--新建客商

--建构三个直归于administrator组的登入windows的顾客(SynUser)

2.在文告服务器上,新建二个共享目录,做为公布的快速照相文件的存放目录,操作:

自己的Computer--D: 新建三个索引,名称叫: PUB

--右键这么些新建的目录

--属性--共享

--接受"分享该文件夹"

--通过"权限"按纽来设置具体的顾客权限,保障第一步中创制的客户(SynUser)具有对该公文夹的装有权力

--确定

3.装置SQL代理(SQLSEEvoqueVERAGENTState of Qatar服务的运转顾客(发表/订阅服务器均做此设置卡塔尔

发端--程序--管理工科具--服务

--右键SQLSERVERAGENT

--属性--登陆--选择"此账户"

--输入也许采取第一步中开创的windows登陆客商名(SynUser)

--"密码"中输入该顾客的密码

4.设置SQL Server身份验证情势,杀绝连接时的权位难题(发表/订阅服务器均做此设置卡塔尔国

供销社处理器

--右键SQL实例--属性

--安全性--身份验证

--选择"SQL Server 和 Windows"

--确定

5.在发表服务器和订阅服务器上相互影响注册

商店管理器

--右键SQL Server组

--新建SQL Server注册...

--下一步--可用的服务器中,输入你要注册的长间隔服务器名 --增加

--下一步--连接使用,选用第贰个"SQL Server身份验证"

--下一步--输入顾客名和密码(SynUser)

--下一步--选用SQL Server组,也得以成立四个新组

--下一步--完成

6.对于只可以用IP,不可能用Computer名的,为其注册服务器别称(此步在实行中没用到)

(在连接端配置,比如,在订阅服务器上配置来说,服务器名称中输入的是揭破服务器的IP卡塔尔(قطر‎

始于--程序--Microsoft SQL Server--客商端网络实用工具

--别名--添加

--互连网库选取"tcp/ip"--服务器小名输入SQL服务器名

--连接参数--服务器名称中输入SQL服务器ip地址

--假使您改改了SQL的端口,撤除选取"动态调控端口",并输入相应的端口号

二、 正式配置

1、配置公布服务器

开发公司微型机,在发布服务器(B、C、D)上举行以下步骤:

(1) 从[工具]下拉菜单的[复制]子菜单中选拔[布署发表、订阅服务器和散发]现身布局发表和散发向导

(2) [下一步] 选取分发服务器 能够选择把发表服务器本人作为分发服务器大概别的sql的服务器(选用自个儿)

(3) [下一步] 设置快速照相文件夹

应用暗中认可servernamePub

(4) [下一步] 自定义配置

能够筛选:是,让自个儿设置分发数据库属性启用发布服务器或设置揭橥设置

否,使用下列暗中认可设置(推荐)

(5) [下一步] 设置分发数据库名称和职位 采取暗许值

(6) [下一步] 启用公布服务器 选取作为发表的服务器

(7) [下一步] 接纳须要发表的数据库和公布项目

(8) [下一步] 选取注册订阅服务器

(9) [下一步] 完毕结构

2、创立出版物

揭露服务器B、C、D上

(1)从[工具]菜单的[复制]子菜单中选取[始建和管制宣布]命令

(2卡塔尔(قطر‎选取要成立出版物的数据库,然后单击[成立公布]

(3)在[始建发表起始]的晋升对话框中单击[下一步]系统就能够弹出三个对话框。对话框上的内容是复制的四个类型。大家今日选第二个相当于默许的快速照相发表(其余四个大家能够去拜候协理卡塔尔(قطر‎

(4)单击[下一步]系统需要钦赐能够订阅该公告的数据库服务器类型,

SQLSE路虎极光VE福睿斯允许在不相同的数据库如 orACLE或ACCESS之间开展多少复制。

但是在那我们选拔运转"SQL SETiggoVE奇骏 2001"的数据库服务器

(5)单击[下一步]系统就弹出八个概念作品的对话框也正是挑选要出版的表

只顾: 即使前面选拔了政工宣布 则再这一步中只可以选取带有主键的表

(6卡塔尔接纳公布名称和陈述

(7卡塔尔国自定义发布属性 向导提供的选料:

是 小编将自定义数据筛选,启用无名订阅和或任何自定义属性

否 依据钦赐情势成立公布 (提议采纳自定义的措施)

(8)[下一步] 选择筛选公布的不二秘诀

(9)[下一步] 还可以是不是同意佚名订阅

1卡塔尔假若采纳签字订阅,则须求在通告服务器上增加订阅服务器

方法: [工具]->[复制]->[安顿揭橥、订阅服务器和散发的性质]->[订阅服务器] 中添加

不然在订阅服务器上号召订阅时会出现的唤醒:改公布区别意无名氏订阅

万一还是供给无名氏订阅则用以下消弭办法

[商店微处理器]->[复制]->[公布内容]->[属性]->[订阅选项] 选用允许无名氏央浼订阅

2State of Qatar假使选拔无名氏订阅,则配备订阅服务器时不会冒出上述提醒

(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','','SQ英雄联盟EDB','srv2的sql实例名或ip'

exec sp_addlinkedsrvlogin 'srv2','false',null,'用户名','密码'

go

--2.在 srv1 和 srv2 这两台计算机中,运维msdtc(布满式事务管理服务State of Qatar,何况安装为机关运营

。我的计算机--调控面板--管理工科具--服务--右键 Distributed Transaction Coordinator--属性--运营--并将开发银行项目设置为自动启动

go

--然后创设三个功课准期调用地方的联合管理存款和储蓄过程就能够了

厂家微电脑

--管理

--SQL Server代理

--右键作业

--新建作业

--"常规"项中输入作业名称

--"步骤"项

--新建

--"步骤名"中输入步骤名

--"类型"中选择"Transact-SQL 脚本(TSQL)"

--"数据库"选拔履行命令的数据库

--"命令"中输入要实行的语句: exec p_process

--确定

--"调度"项

--新建调治

--"名称"中输入调整名称

--"调节项目"中接纳你的课业试行安排

--假如选拔"每每现身"

--点"改良"来安装你的光阴陈设

下一场将SQL Agent服务运维,并设置为电动运营,否则你的功课不会被实施

设置格局:

作者的微处理机--调节面板--管理工科具--服务--右键 SQLSELX570VERAGENT--属性--运维项目--选用"自动运行"--分明.

--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 :

心想:相比较于建临时表,哪个快?

建表:查询 -》 产生一时表 -》 查询不经常表 (慢卡塔尔国

外加:合并条件 -》 查询表 (快State of Qatar

algorithm = merge 当引用视图时,引用视图的口舌与定义视图的口舌合併。

temptable 当援用视图时,依据视图的创办语句创设叁个有的时候表

undefined 未定义,自动,由系统帮您认清。
*/

/*
1、algorithm = merge:

代表视图只是二个口舌法则,当查问视图时,把询问视图的话语(比 如where 这一个State of Qatar与创设时的语句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 视图名称');

*/

动用如下参数,同步源数据库的多个存款和储蓄进程到指标数据库,因为此处是在本机命名实例下测量检验,因而实例名和端口号输入

演绎第一步:

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

/*注意:

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

留意:校订完成符为 //

别的对象的一块儿

已知的难点:

3,同步表布局,饱含字段,索引,限制等等,然则无能为力支撑外键,特意去掉了外键,思考怎么?因吹斯汀。

就疑似要求用SSIS恐怕其余ETL专门的学业超级轻松完结,比如用SSIS的话,就能够会存在一定部分频仍的手工业操作。

4,同步的指标在指标服务器上设有的意况下,是不是强迫覆盖

1,20一九零六122030:经测量检验,这两天一时半刻不帮忙Sequence对象的同台。

2,target表中是不是有数量?假使有多少,是还是不是以隐蔽的艺术进行

1,表的存在依赖于schema,供给酌量到表的schema是还是不是留存,若是不设有先在target库上创立表对应的schema

那或多或少变成了重构大批量的代码,一起头都以直来直去的协同,不能完毕那一个逻辑,切实心得到代码的“单一职务”原则

1,当表的目录为filter index的时候,不能转移包蕴where条件的目录创立语句,那三个看起来蛋疼的表构造导出语句,权且没时间改它。2,近来不扶持任何少用的体系字段,举例地理空间字段什么的。

然后超级大概,那个workflow使用也就那样一回,就一命归阴了,却一直以来要浪费时间去做这几个ETL。

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

那或多或少形成在代码中鲜见浓厚,进行了长日子的debug,完全未有想到这些函数是以此鸟样子,因为此地找到信任对象的体系,却找不到目的自笔者,次奥!!!其余一种意况就是动态SQL了,不可能使用 sys.dm_sql_referenced_entities那几个系统函数找到其凭仗的对象。

create proc [dbo].[sp_test01]asbegin set no count on; delete from dbo.table01 where id = 1000 select dbo.fn_test01()end

信任对象的解决,依然相比较蛋疼的

1,源服务器消息 ,未有客户名密码的事态下,使用windows身份验证情势

表同步的准绳是,创立指标表,遍历源数据的表,生成insert into values(***),(***),(***卡塔尔国格式的sql,然后插入目的数据库,这里大概步骤如下:

行使如下参数,同步源数据库的三张表到对象数据库,因为此处是在本机命名实例下测量试验,由此实例名和端口号输入

1,相仿于表,须求考虑存款和储蓄进程的schema是或不是留存,要是不设有先在target库上成立表对应的schema2,肖似于表,arget数据库中是不是曾经存在对应的积攒进度,是不是以隐蔽的秘技实践3,存款和储蓄进度可能依靠于b表,有个别函数,顾客自定义表变量等等,同步存款和储蓄进程的时候要求先同步信任的对象,这点相比复杂,达成过程中相见在重重广大的坑

参数表明如下,大的归纳四类:

仓库储存进程同步的法则是,在源数据库上扭转创立存款和储蓄进度的讲话,然后写入目的库,这里大约步骤如下:1,存款和储蓄进程信任于schema,所以同步存款和储蓄进程在此之前先同步schema2,同步的进程会检查信任对象,假如依附别的对象,暂停当前指标同步,先同步信任对象3,重复第二手续,直至达成4,对于仓库储存进程的联手,如若是抑遏覆盖的话,逼迫覆盖仅仅对存款和储蓄进程自个儿生效,对信任对象并不见到成效,假诺依靠对象不设有,就创建,否则不做其余业务

仓库储存进度对象的一路

敏捷数据同步落成

2,指标服务器音讯,没有客商名密码的动静下,使用windows身份认证形式

若果在暗中认可schema为dbo的靶子,在积存进程也许函数中未有写schema,使用 sys.dm_sql_referenced_entities那几个系统函数是无可奈何找到其依靠的靶子的,奇葩的是足以找到schema的类型,却从没回去对象自己。

已知难点:

代码重午节写好了,这两天抽空进行了部分测量试验以至bug fix,应该还潜在不菲不解的bug,专门的工作量比想象中的大的多了去了。

一道某一个对象的依据对象,使用如下SQL查出来对象重视音讯,由此这里就稀有深远,同步正视对象。这里就象是于同步A的时候,A信任于B和C,然后结束同步A,先同步B和C,同步B可能C的时候,大概又凭仗于别的对象,然后继续先同步其依靠对象。

比如输入obj_type="sp" 且-obj=为None的意况下,会同步源数据库中的全部存款和储蓄进程以至其依附对象

注脚:测量检验要协作的积攒进度之一为[dbo].[sp_test01],它依附于此外多个对象:dbo.table01和dbo.fn_test01()

5,转义处理,在拼凑SQL的时候,须要张开转义管理,不然会引致SQL语句错误,近些日子管理了字符串中的'字符,二进制字段,时间字段的转义管理

1,表信任于schema,所以同步表早先先同步schema

支撑其余数据库对象的一块儿,比方function,table type等,因为能够在同步其余存储进度对象的时候顺便的同步function,table type,那么些与表恐怕存款和储蓄进度看似,不做过多表明。

亟待思忖到某此中坚的校验难题:在源服务器上,要求联合的靶子是不是留存,只怕输入的对象是或不是存在于源服务器的数据Curry。

唯恐存在对象A依赖于对象B,对象B依赖于对象C……,这里有一些递归的意趣

而dbo.fn_test01(卡塔尔(قطر‎的如下,重视于其它二个对象:dbo.table02

已知的主题素材:

建源的数据库新闻,目的的数据库音信,如若是多少个表,需求三个三个地拉source和target,然后一个三个地mapping,然后运行实现数据同步。

于是在想,可不恐怕急速实现相仿要求,尽最大程度减弱重复的手工操作?形似基于命令行的办法,轻便快捷,无需太多的手动操作。

1,如若输入obj_type="tab" 且-obj=为None的气象下,会同步源数据库中的全部表。2,那一个频率决议于机器质量和互连网传输,本机测量试验的话,每秒中得以交给3到4次,也正是每秒钟能够付出3000~4000行左右的多少。

须求改进的地点

例如说须要赶快搭建二个测量试验境况,须要一块全体的表结交涉各样表的一部分数据就可以。

故而,这几个测量试验的[dbo].[sp_test01]就依靠于其余对象,如若其依附的目的不设有,同步的时候,仅仅一同这些蕴藏进度自个儿,是没风趣的

编辑:江苏十一选五手机版数据库 本文来源:Server数据库实现对象同步轻量级,Language精妙操作

关键词: