数据库编程
文章平均质量分 69
geovi
£捡垃圾,就是我的一生渴求. £并且喜欢这句"书中自有黄金屋,书中自有颜如玉,书中自有千钟黍,书中车马多簇簇"的感慨."读书长见识,行路广阅历",是一个"悟"字. $自然是我最想追求的"偶像". ¥读者是,读之者,者之读.一沙一世界! ¥to be is to do
展开
-
Dynamic Cross-Tabs/Pivot Tables 通用统计报表列转行存储过程
--------------------------------------行,列转换---geovindu@163.com---www.dusystem.com---涂聚文use pubsGOCREATE PROCEDURE crosstab @select varchar(8000),@sumfunc varchar(100), @pivot varchar(100), @tabl翻译 2009-02-12 13:46:00 · 937 阅读 · 0 评论 -
sql: Compare Tables
---使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式select * from BookInfoList--存在不同的select BookInfoID,BookInfoBarCode from BookInfoList where BookInfoStatus=1 except select BookInventory原创 2014-06-12 11:25:31 · 888 阅读 · 0 评论 -
parent-childRecursive sum in parent-child hierarchy T-SQL
---树形(父子关系类)分级类统计(父子统计)--涂聚文 2014-08-14drop table BookKindListcreate table BookKindList( BookKindID INT IDENTITY(1,1) PRIMARY KEY, BookKindName nvarchar(500) not null, BookKin原创 2014-08-14 16:21:57 · 699 阅读 · 0 评论 -
WHILE (Transact-SQL)
---循环declare @n intdeclare @rowcount int declare @name varchar(50)create table #temp( id int identity(1,1), ColumnNme nvarchar(100))insert into #temp select COLUMN_NAME from 数据库名.INFORMATION_原创 2015-01-15 11:54:03 · 535 阅读 · 0 评论 -
sql: MySQL and Microsoft SQL Server Stored Procedures IN, OUT using csharp code
MySQL存储过程:#插入一条返回值DELIMITER $$DROP PROCEDURE IF EXISTS `geovindu`.`proc_Insert_BookKindOut` $$CREATE PROCEDURE `geovindu`.`proc_Insert_BookKindOut` (IN param1Name NVarChar(1000),IN param1Paren原创 2015-02-10 11:39:45 · 934 阅读 · 0 评论 -
sql:Mysql create view,function,procedure
use test;create database Liber;use Liber;#顯示數据庫 20150210 Geovin Du 涂聚文SHOW DATABASES;drop table BookKindList;#书目录create table BookKindList( BookKindID INT NOT NULL AUTO_INCRE原创 2015-02-10 15:06:41 · 862 阅读 · 0 评论 -
MySql:局域网和权限用户管理
MySql 5.6(XP)/5.7(win7) 添加用户和设置局域访问权限操作.请在 http://sourceforge.net/ 下载MySql Control Center(不是安装版本). use mysql; select * from user; update user set authentication_string=password('0214') wher原创 2015-03-20 12:59:51 · 1351 阅读 · 0 评论 -
Mysql问题:Connector/Net no longer supports server versions prior to 5.0
我用Connector/Net 6.9读取MySql 4.1 时,会报以下错:1.Connector/Net no longer supports server versions prior to 5.02.Unable to connect to any of the specified MySQL hosts.后来我下载 http://dev.mysql.com/downloads原创 2015-03-23 12:05:08 · 1690 阅读 · 0 评论 -
sql: postgreSQL sql script
--pg_catalogSELECT * from pg_class c,pg_attribute a,pg_type t where c.relname='BookKindList' and a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oidSELECT a.attname from pg_class c,pg_attribute a,原创 2015-04-02 12:20:08 · 984 阅读 · 0 评论 -
Getting SQLite metadata with Csharp
using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using System.Dat原创 2015-03-24 21:23:17 · 974 阅读 · 0 评论 -
sql:MySql create FUNCTION,VIEW,PROCEDURE
use geovindu;#函数DELIMITER $$drop function if exists f_GetDepartmentName $$CREATE function f_GetDepartmentName( did int) returns nvarchar(400) begindeclare str nvarchar(100);select Depar原创 2015-04-03 17:57:18 · 1267 阅读 · 0 评论 -
sql:SQL Server metadata queries
-- 2012--https://technet.microsoft.com/zh-cn/library/ms186778(v=sql.110).aspx--对象目录视图 (Transact-SQL)select * from sys.foreign_keysselect * from sys.foreign_key_columnsselect * from sys.all_colum转载 2015-05-15 17:20:29 · 813 阅读 · 0 评论 -
csharp: Oracle Metadata queries
数据类型表:TypeNameProviderDbTypeColumnSizeCreateFormatCreateParametersDataTypeBFILE14294967296BFILE System.Byte[]BLOB24294原创 2015-05-17 00:27:12 · 723 阅读 · 0 评论 -
sql: Oracle 11g create procedure
CREATE OR REPLACE PROCEDURE proc_Insert_BookKindList (temTypeName nvarchar2,temParent int)ASncount number;begin--SELECT COUNT (*) INTO ncount FROM BookKindList fm1 where EXISTS (SELECT BookKi原创 2015-05-26 22:44:05 · 941 阅读 · 0 评论 -
sql: Oracle 11g create table, function,trigger, sequence
----自动增长ID --序列创建 drop SEQUENCE BookPlaceList_SEQ;CREATE SEQUENCE BookPlaceList_SEQINCREMENT BY 1 -- 每次加几个START WITH 1 -- 从1开始计数NOMAXVALUE -- 不设置最大值NOCYCLE --原创 2015-05-22 09:35:29 · 795 阅读 · 0 评论 -
sql: table,view,function, procedure created MS_Description in sql server
--添加描述 Geovin Du--https://msdn.microsoft.com/en-us/library/ms180047.aspx--https://msdn.microsoft.com/zh-cn/library/ms180047(v=sql.120).aspx--https://msdn.microsoft.com/zh-cn/library/ms179853(v=sql.原创 2015-09-27 22:08:45 · 1053 阅读 · 0 评论 -
SQL Server: Get table primary key and Foreign Key using sql query
---某個錶的列名,主键,外键---20150923 涂聚文 SQL Server: Get table primary key and Foreign Key using sql query DECLARE @SchemaName VARCHAR(200),@TableName VARCHAR(200)SET @SchemaName='dbo'SET @TableName='Bo原创 2015-09-23 10:53:16 · 768 阅读 · 0 评论 -
sql:查询创建表的结构
--显示所有用户表:--1SELECT SCHEMA_NAME(schema_id) As SchemaName , name As TableName from sys.tables ORDER BY name--2。alternate:SELECT sch.name As SchemaName , tbl.name As Table转载 2014-05-15 23:01:29 · 946 阅读 · 0 评论 -
sql and csharp: Split Function
T-SQL: declare @int int,@prov int,@city int,@str nvarchar(500)set @str='天河麗特青春:中國廣東省廣州市天河區天河路623號天河娛樂廣場麗特青春百貨一樓,塗聚文'select @int=charindex(':',@str) select @prov=charindex('省',@str) select原创 2014-02-21 15:09:56 · 1174 阅读 · 0 评论 -
SQL:Example Uses of the SUBSTRING String Function
---Example Uses of the SUBSTRING String Function--http://www.sql-server-helper.com/tips/tip-of-the-day.aspx?tkey=4AB06421-E859-4B5F-A948-0C9640F3108D&tkw=sample-uses-of-the-substring-string-function转载 2014-01-21 12:11:23 · 650 阅读 · 0 评论 -
sql server 2000 一个表的数据插入到三个相关联表中,一条一条的插入
FROM http://www.cnblogs.com/geovindu/archive/2009/07/03/1516159.html1/**//********************************************************** 2测试数据 sql server 2000 一个表的数据插入到三个相关联表中,一条一条的插入 3---涂聚文 缔友原创 2009-07-04 11:58:00 · 1535 阅读 · 0 评论 -
SQL 一个表中的两个外键来自于同一个表创造的视图
/*****************************************---Geovin Du 塗聚文 締友計算機信息技術有限公司---公司集團有親屬員工顯示介紹人的姓名。一個表中的兩個外鍵是來自於一個同表*****************************************/--員工表IF EXISTS (select * from syso原创 2011-07-01 11:47:00 · 2148 阅读 · 0 评论 -
sql ntext數據類型字符替換
---ntext數據類型字符替換 ---2011-08-21 塗聚文 深圳大運會期間,政府貼出"溫馨提示",交通管制,世界之窗周邊不充許到陽台觀看,出入憑身份證create table tt( sid INT IDENTITY(1,1), cont ntext原创 2011-08-21 11:31:24 · 1151 阅读 · 0 评论 -
sql server 2000/2005 script
---2005附加數據庫---ATTACH DATABASE TEMPLATE---涂聚文 2012 元旦exec sp_attach_db 'Asset5','D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Asset5.mdf','D:\Program Files\Microsoft SQL Server\MSSQL原创 2012-01-05 14:54:44 · 680 阅读 · 0 评论 -
SQL 生成公曆和農曆對照數據,公曆查找農曆和農曆查找公曆函數
---数据来源:http://topic.csdn.net/u/20100424/07/29529d9d-bc54-4877-b198-4426b4d85024.html if object_id('SolarData') is not null drop table SolarData go create table SolarData ( yearid in转载 2012-09-17 16:22:37 · 1373 阅读 · 0 评论 -
SQL 生成公曆和農曆對照數據續--创建萬年曆查找各種周期性節日數據
---sql server 2005 測試 塗聚文 捷為工作室,締友計算機信息技術有限公司---查找公曆節日--select (@year-GooddayStaryear) as Gyear,GooddayContent from GoodDaysList where @year>=GooddayStaryear and GooddayStarmoth=@month and GooddayS原创 2012-09-21 12:39:56 · 3660 阅读 · 0 评论 -
sql distinct
---沒有去除重復的記錄select distinct ContractLaborEmployeeUidKey,ContractLaborEndDate from ContractLaborList order by ContractLaborEndDate descselect * from ContractLaborList order by ContractLaborE原创 2012-10-31 19:13:40 · 547 阅读 · 0 评论 -
sql script: select database select all table
---表中的主鍵select [name],'tablekey'= (select top 1 COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = sysobjects.name) from sysobjects where type='U'--所有表select *原创 2012-12-27 19:22:30 · 946 阅读 · 0 评论 -
Sql: 去除字符串中的相同的字符串函數
---去除字符串中重復的值函數create function StringRemove(@str nvarchar(2000))returns varchar(2000)asbegin declare @result nvarchar(2000),@temp nvarchar(1000) set @result='' set @temp=''原创 2013-01-19 17:12:30 · 820 阅读 · 0 评论 -
Sql:請假跨月份問題,或跨年份問題 日期部分边界
---請假跨月份問題,或跨年份問題 日期部分边界declare @sart datetime,@end datetime,@d int,@s1 datetime,@e1 datetime,@s2 datetime,@e2 datetime,@t varchar(50),@t2 varchar(50),@I intset @sart='2012-11-18 14:56:12'set @e原创 2013-04-11 11:18:41 · 1133 阅读 · 0 评论 -
display month as a calendar using sql
---sql server 2005 顯示一個月的數據,如果沒有空的也要顯示出來declare @T table([geovinddate] Datetime,[workcontent] nvarchar(8),[worker] nvarchar(50))Insert @Tselect '2012-8-11',N'遲到','geovindu' union allselect '20转载 2013-05-22 14:58:50 · 713 阅读 · 0 评论 -
sql server:自定義計算固定工作日,雙休日函數
---sql server declare @date datetimeset @date='2012-02-03'--getdate()--本月第一天SELECT DATEADD(mm, DATEDIFF(mm,0,@date), 0)--本月最后一天SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@date)+1, 0))原创 2013-05-29 09:24:40 · 1016 阅读 · 0 评论 -
SQL:exec sp_executesql 用法
--這種是無效的過程 declare @sql nvarchar(500), @where nvarchar(500),@i nvarchar(64),@p nvarchar(50),@id intset @id=5set @sql='select '+@p+'=AreaCode from AdministrativeAreaList where AreaID='+cast(@id原创 2013-07-16 10:00:21 · 1415 阅读 · 0 评论 -
SQL Server迭代求和
drop table t_geovindu create table t_geovindu ( xid int IDENTITY (1, 1), price money, DebitCredit VARCHAR(2), adate datetime default(getdate()) ) insert原创 2013-08-05 17:12:54 · 1166 阅读 · 0 评论 -
sql: 生日三个月内有效
DECLARE @birthday datetime,@stat datetime,@end datetime,@statbirthday datetime,@endbirthday datetime,@thirdbirthday datetime,@firthbirthday datetime, @year int,@month int , @day int,@str varchar(20),原创 2013-12-02 15:11:29 · 1002 阅读 · 0 评论 -
MySQL Stored Procedure Prepared Statement (Dynamic SQL) Parameterized
类似于SQL Server中的:sp_executesqlsql server script:--- 涂聚文 20160906IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Select_DuDeptUserCount')DROP PROCEDURE proc_Select_DuDeptUserCountGOCR原创 2016-09-07 16:33:00 · 800 阅读 · 0 评论