分类
操作系统 数据库

Linux系统mysql密码和远程访问设置

 

一、设置或重置MySql密码

Mysql刚刚安装完成,密码是空的,直接回车可以登录。

方法一:最简单的方法,也是安装完mysql后,系统提示的方法。使用mysqladmin来完成。shell> mysqladmin -u root password “newpwd”

shell> mysqladmin -u root -h host_name password “newpwd”password后面的双引号不是必须的,不过如果密码包含空格或者一些特殊的符号,需要用引号。方法二:利用mysql里面的SET PASSWORD命令来完成,注意必须使用PASSWORD()函数来加密设置的这个newpwd,否则直接=’newpwd’不会生效。不过如果使用方法一用mysqladmin password设置密码或者用GRANT来设置,就不用使用PASSWORD()函数,因为它们已经自动调用了该函数。shell> mysql -u root

mysql> SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘newpwd’);

mysql> SET PASSWORD FOR ‘root’@’host_name’ = PASSWORD(‘newpwd’);

方法三:直接通过UPDATE user表来设置密码shell> mysql -u root

mysql> UPDATE mysql.user SET Password = PASSWORD(‘newpwd’)

-> WHERE User = ‘root’;

mysql> FLUSH PRIVILEGES;

如果忘记mysql的root密码可以采取下面的步骤重新设置

1。kill掉所有mysql的进程

2。使用–skip-grant-tables的参数启动mysql

shell> mysqld_safe –skip-grant-tables &

3。设置新的密码,同样两种方式可以选择

方法一

shell> mysqladmin -u root flush-privileges password “newpwd”

方法二

mysql> UPDATE mysql.user SET Password=PASSWORD(‘newpwd’)

-> WHERE User=’root’;

mysql> FLUSH PRIVILEGES;

4。停止mysql server,用正常的模式启动

5。OK,可以使用新设置的密码了

二、配置远程访问

Mysql为了安全性,在默认情况下用户只允许在本地登录,可是在有此情况下,还是需要使用用户进行远程连接,因此为了使其可以远程需要进行如下操作:

/etc/mysql/my.cnf

bind-address= 127.0.0.1//要改成你的外网ip


1、允许root用户在任何地方进行远程登录,并具有所有库任何操作权限,具体操作如下:
在本机先使用root用户登录mysql:
mysql -u root -p”youpassword” 
进行授权操作:
mysql>GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ IDENTIFIED BY ‘youpassword’ WITH GRANT OPTION;
重载授权表:
FLUSH PRIVILEGES;
退出mysql数据库:
exit


2、允许root用户在一个特定的IP进行远程登录,并具有所有库任何操作权限,具体操作如下:
在本机先使用root用户登录mysql:
mysql -u root -p”youpassword” 
进行授权操作:
GRANT ALL PRIVILEGES ON *.* TO [email protected]”172.16.16.152″ IDENTIFIED BY “youpassword” WITH GRANT OPTION;
重载授权表:
FLUSH PRIVILEGES;
退出mysql数据库:
exit


3、允许root用户在一个特定的IP进行远程登录,并具有所有库特定操作权限,具体操作如下:
在本机先使用root用户登录mysql:
mysql -u root -p”youpassword” 
进行授权操作:
GRANT select,insert,update,delete ON *.* TO [email protected]”172.16.16.152″ IDENTIFIED BY “youpassword”;
重载授权表:
FLUSH PRIVILEGES;
退出mysql数据库:
exit


4、删除用户授权,需要使用REVOKE命令,具体命令格式为:
REVOKE privileges ON 数据库[.表名] FROM user-name;
具体实例,先在本机登录mysql:
mysql -u root -p”youpassword” 
进行授权操作:
GRANT select,insert,update,delete ON TEST-DB TO [email protected]”172.16.16.152″ IDENTIFIED BY “youpassword”;
再进行删除授权操作:
REVOKE all on TEST-DB from test-user;
****注:该操作只是清除了用户对于TEST-DB的相关授权权限,但是这个“test-user”这个用户还是存在。
最后从用户表内清除用户:
DELETE FROM user WHERE user=”test-user”;
重载授权表:
FLUSH PRIVILEGES;
退出mysql数据库:
exit

5、MYSQL权限详细分类:
全局管理权限: 
FILE: 在MySQL服务器上读写文件。 
PROCESS: 显示或杀死属于其它用户的服务线程。 
RELOAD: 重载访问控制表,刷新日志等。 
SHUTDOWN: 关闭MySQL服务。
数据库/数据表/数据列权限: 
ALTER: 修改已存在的数据表(例如增加/删除列)和索引。 
CREATE: 建立新的数据库或数据表。 
DELETE: 删除表的记录。 
DROP: 删除数据表或数据库。 
INDEX: 建立或删除索引。 
INSERT: 增加表的记录。 
SELECT: 显示/搜索表的记录。 
UPDATE: 修改表中已存在的记录。
特别的权限: 
ALL: 允许做任何事(和root一样)。 
USAGE: 只允许登录–其它什么也不允许做。

分类
数据库

SqlServer2012新函数TRY_CONVERT不存在的问题解决

今天开发中在select列中使用convert把字符串转换成日期格式,有些字符串转换会报错,造成翻页翻着翻着就会500错误,由于需要生日字符串转换年龄还要排序,所以又无法在c#李转换.

我想c#里有tryparse,sql里搞不好也有。好在发现了有TRY_CONVERT 函数的存在,运行语句测试报错:

问题描述.
我们在SQL Server Management Studio中对数据库在SQL Server2012实例,试图运行下面的SELECT语句时:

SELECT TRY_CONVERT(date, ‘birthday’)

接收到服务端报错:(我是英文数据库服务器,中文可能是该函数没有定义)

Msg 195, Level 15, State 10, Line 1
‘TRY_CONVERT’ is not a recognized built-in function name.

 

造成原因.

这个sql语句不能够运行在 SQL Server 2005 (90)或SQL Server 2008 (100)的兼容级别.

 

解决办法.

数据库上右键, 选择属性, 然后切换到选项页, 修改数据库的兼容级别为sql2012( 110).

修改兼容级别到110后, 查询就可以成功了.

 

另外,如果没有权限进入数据库属性设置页的话,也可以通过sql命令修改:


ALTER DATABASE database_name 
SET COMPATIBILITY_LEVEL = { 90 | 100 | 110 }
分类
数据库

Oracle中存储过程传入按逗号分割的数值参数时报错的问题

前两天写存储过程,需要用到多参数传入,传入的参数是给一个select语句的where条件使用的。
但是写的时候遇到一个麻烦的问题,这个where条件的参数必须是number型的,而且使用的是in,也就是多个参数,比如select * from table_name_1 where id in (1,2,3,4,5),其中的1,2,3,4,5即是通过存储过程的参数传入的。
这里有个问题,如果在sql中,where id in (1,2,3,4,5)这条语句会认为1,2,3,4,5是五个单独的number型,而不会将其作为一个字符串。但是如果在存储过程中作为一个整体的参数进行传入时,即会将1,2,3,4,5判定为‘1,2,3,4,5’,而’1,2,3,4,5’是一个字符串,这时候sql执行的实际上是select * from table_name_1 where id in (‘1,2,3,4,5’),而id是一个number型的,这时候就会报错。
原存储过程大致如下:

create or replace procedure test_prc(an_id in number)
is
begin 
insert into tmp_table
select * from table_name_1 where id in (an_id);
commit;
end;

如上述的存储过程,由于传入的参数an_id是一个number型的,如果是一个单独的数字,过程执行没有问题,但如果是一个字符串,如’1,2,3,4,5’,这样就会报错,因为’1,2,3,4,5’是一个varchar。
如果将传入的参数an_id 改为varchar型的,如create or replace procedure test_prc(an_id in varchar),这时候虽然在传入的时候类型一致了,但是在执行select语句时——where id in (an_id),由于表table_name_1的id列是number型的, 传入的实际上是’1,2,3,4,5’,这时候,也会报错。
这种情况据我所知可以有两种方法来解决,第一种是使用动态sql;第二种是使用instr函数来解决。有的朋友使用的是动态sql,我没使用这种方法,第一是自己不太会写,第二是觉得太繁琐。这里重点说的是使用instr函数。以后可能也会尝试使用动态sql来试试看,到时候再来更新吧。
其实使用instr函数也没有什么多说的,仔细看懂了理解了就行了,先贴出结果吧。

create or replace procedure test_prc(an_id in varchar)
is
begin 
insert into tmp_table
select * from table_name_1 where instr(',' || an_id || ','  ,  ',' || id || ',');
commit;
end;

INSTR函数是个字符串搜索函数,默认情况下有两个参数:instr(string,set),用于在string中寻找set的位置,如果找到,返回set子串的第一个字符的位置,如果没有找到,则返回0。
之所以在开始和结束都加上’,’,是为去掉当查询11的时候,会将1和11都取出来。
至此,问题解决。

分类
数据库 编程开发

SqlServer查询字符串聚集 for xml

sqlserver提供了一系列将查询结果的其中一列的聚集函数,不如Sum(),Avg()等等,这些都是针对数据聚集的函数。那么有没有字符串聚集的函数呢?

没有这样的内置函数,一直以来只能通过自定义函数来实现。

但是sqlserver05以后有了一个比较完美的解决办法,使用for xml path来取以逗号分隔的多行数据的应用
for xml有两个修饰符,还有两个修饰符,一共有四种,分别是:
1、RAW
2、AUTO
3、PATH
4、EXPLICIT
基本上是按照功能的强弱正序排列的,闲话少说,直接看代码:

--RAW
--SELECT CorpCode FROM dbo.Kvp_CorpInfo FOR XML AUTO('')
--仅FOR XML 的RAW 或PATH 模式允许行标记名称。
SELECT CorpCode FROM dbo.Kvp_CorpInfo FOR XML RAW('')
--行标记省略(空的行标记名称)不能与以属性为中心的FOR XML 序列化一起使用。?
SELECT CorpCode FROM dbo.Kvp_CorpInfo FOR XML RAW(''),elements

SELECT TOP 100 productid,productname
FROM dbo.Bas_ProductInfo
FOR XML RAW, elements;
--FOR XML RAW, Type;--结果命名不同.msdn:您可以选择性地指定TYPE 指令将结果作为xml 类型进行检索。TYPE 指令不会更改结果的内容。只影响结果的数据类型。

--Auto
SELECT TOP 1 productid,productname
FROM dbo.Bas_ProductInfo A
FOR XML AUTO, elements;

--交叉数据的嵌套顺序
SELECT OrderInfo.SalesOrderID,OrderInfo.TotalAmount,OrderList.SalesOrderID,OrderList.SalesOrderListId,OrderList.ProductId,OrderList.SelfPrice
FROM dbo.Ope_SalesOrderRecord OrderInfo,dbo.Ope_SalesOrderList OrderList
WHERE OrderInfo.SalesOrderID=OrderList.SalesOrderID
FOR XML AUTO,elements

SELECT OrderList.SalesOrderID,OrderList.SalesOrderListId,OrderList.ProductId,OrderList.SelfPrice,OrderInfo.SalesOrderID,OrderInfo.TotalAmount
FROM dbo.Ope_SalesOrderRecord OrderInfo,dbo.Ope_SalesOrderList OrderList
WHERE OrderInfo.SalesOrderID=OrderList.SalesOrderID
FOR XML AUTO

/*
在创建父元素的过程中,会对每个即将创建的父节点进行比较,如果值不同,将向XML 添加新的父元素,值相同则比较属性,以此类推。
在比较这些列值时,如果要比较的任何列是text、ntext、image 或xml 类型,即使它们的值可能相同,FOR XML 也将认为它们是不同的,并且不对其进行比较。这是因为不支持大型对象的比较。这些元素将被添加到每个选定行的结果中。请注意,会比较(n)varchar(max) 和varbinary(max) 类型的列。
*/

--EXPLICIT 
/*
[适用于复杂结构的xml]
常见格式:
	ElementName!TagNumber!AttributeName!Directive
Directive:
如果未指定Directive 和AttributeName(例如Customer!1),则暗含一个element 指令(如Customer!1!!element),并且列数据包含在ElementName 中。	
一种用途是将值编码为ID、IDREF 和IDREFS。可以将ID、IDREF 和IDREFS 关键字指定为Directives。这些指令将覆盖属性类型。这使您能够创建文档内链接。
另外hide、element、elementxsinil、xml、xmltext 和cdata
除不发生实体编码外,xml 指令与element 指令相同。
http://msdn.microsoft.com/zh-cn/library/ms189068
*/

SELECT  
1 as tag,
null as parent,
A.CorpCode AS [人员!1!姓名],NULL AS[人员信息!2!年龄!xml] FROM dbo.Kvp_CorpInfo A WHERE CorpCode<>''
UNION
SELECT 
2 AS tag,
1 AS parent,
A.CorpCode,B.CorpId FROM dbo.Kvp_CorpInfo A,dbo.Kvp_CorpInfo B WHERE A.CorpCode<>'' AND a.CorpCode=b.CorpCode
order by [人员!1!姓名],tag--此处排序以供节点嵌套
FOR XML EXPLICIT

--PATH
SELECT 2+2 FOR XML PATH--值,xml:<row>4</row>
SELECT 2+2 '@T' FOR XML PATH--属性,xml:<row T="4" />
SELECT 2+2 T FOR XML PATH--节点,xml:<row><T>4</T></row>
SELECT 2+2 'T/F' FOR XML PATH--多级节点,xml:<row><T><F>4</F></T></row>

--名称指定为通配符的列
/*如果指定的列名是一个通配符(*),则插入此列的内容时就像没有指定列名那样插入。如果此列不是xml 类型的列,则此列的内容将作为文本节点插入*/
SELECT CorpCode '@Code',CorpCode '*',':' '*',CorpName '*'
FROM dbo.Kvp_CorpInfo WHERE CorpCode<>''
FOR XML PATH
/*
<row Code="0021">0021:北京**有限公司 </row>
<row Code="0006">0006:南京**有限公司   </row>
<row Code="0024">0024:上海**有限公司 </row>
<row Code="009A">009A:DCgou</row>
<row Code="0046">0046:北京**供应链服务有限公司</row>
<row Code="00X8">00X8:广东**有限公司</row>
*/

SELECT RuleID,RuleName,Param.query('/Rule') '*'
FROM dbo.Sys_RuleInfo WHERE RuleID=101150000000000011
FOR XML PATH

--列名为XPath 节点测试的列text()/comment()/node()
SELECT 2+2 'text()' FOR XML PATH--=writetext();
SELECT 2+2 'comment()' FOR XML PATH--=writecomment();
SELECT 2+2 'node()' FOR XML PATH--=*

--data()
SELECT corpcode 'data()' FROM dbo.Kvp_CorpInfo WHERE CorpCode<>'' FOR XML PATH('')--xml:0021 0006 0024 009A 0046 00X8
SELECT * FROM Sys_RuleInfo 

--使用path,获取以分隔符分隔的聚集字符串
SELECT CorpCode+',' FROM dbo.Kvp_CorpInfo WHERE CorpCode<>'' FOR XML PATH('')--xml:0021,0006,0024,009A,0046,00X8,
SELECT ''''+CorpCode+''',' FROM dbo.Kvp_CorpInfo WHERE CorpCode<>'' FOR XML PATH('')--xml:'0021','0006','0024','009A','0046','00X8',
<span style="color: #000000;"> </span>
分类
数据库 编程开发

关于SQL Server 中合并行的方法

————-参考————————

—-假设我们有如下数据表
——菜系表————
–店名        菜系——
–澳门豆捞    川菜
–澳门豆捞    粤菜
–澳门豆捞    闽菜
–为民大酒店  鲁菜
–为民大酒店  粤菜
–为民大酒店  川菜
———————–

——-执行如下语句

select 店名,菜系=STUFF((select ','+ltrim(菜系) from 菜系表 where 店名=t.店名 for XML path('')),1,1,'')
from 菜系表 t
group by 店名

——-结果如下————-
–店名        菜系———-
–澳门豆捞    川菜,闽菜,粤菜
–为民大酒店  川菜,鲁菜,粤菜
—————————-

————————————————————————————–
Stuff(expression1_Str,startIndex,lengthInt,expression2_Str)函数共有四个参数,
其功能是将expression1_Str中自startIndex位置起删除lengthInt个字符,然后将expression2插入到expression1_Str中的startIndex位置。startIndex 从1开始
数据库表中有三个字段,要以xxxx-xxxxxx-xxxxxx的格式输出,其中不满相应位数的,在后面补空格,即把类似23,1234,879这样的三个数以如下形式输出到报表中:23__-1234__-879___的格式输出,标注颜色的下划线一种颜色代表一个空格。
STUFF(SPACE(4),1,LEN(‘23’),’23’)+’-’+STUFF(SPACE(6),1,LEN(‘1234’),’1234’)+’-’+ STUFF(SPACE(6),1,LEN(‘879’),’879’),转换结果自然就为23__-1234__-879___了。
Stuff函数另一种解释

select stuff('string1',<starting position>,<length to delete from string 1>,'string2')

示例:

select stuff('youxiaofeng',2,1,'F')

从第二个字符开始,删掉一个字符,然后用’F’代替删掉字符的位置,如果不需要删掉字符,则将<length to delete from string 1>设为0即可。
注意:如果<starting position>或<length to delete from string 1 >是负的,或者<starting position>是大于<string 1>长度的数,则STUFF函数将返回NULL,如果指令的<length to delete from string 1>长于从<starting position>位置到<string 1>结尾的字符数,此函数将<string 1 >在<starting position-1>处截断

—————————————————————————
FOR XML PATH 语句的应用
大家都知道在SQL Server中利用 FOR XML PATH 语句能够把查询的数据生成XML数据,下面是它的一些应用示例。

DECLARE @TempTable table(UserID int , UserName nvarchar(50));
insert into @TempTable (UserID,UserName) values (1,'a')
insert into @TempTable (UserID,UserName) values (2,'b')
select UserID,UserName from @TempTable FOR XML PATH

运行这段脚本,将生成如下结果:

<row>
<UserID>1</UserID>
<UserName>a</UserName>
</row>
<row>
<UserID>2</UserID>
<UserName>b</UserName>
</row>

大家可以看到两行数据生成了两个节点,修改一下PATH的参数:

select UserID,UserName from @TempTable FOR XML PATH('lzy')

再次运行上述脚本,将生成如下的结果:

<lzy>
<UserID>1</UserID>
<UserName>a</UserName>
</lzy>
<lzy>
<UserID>2</UserID>
<UserName>b</UserName>
</lzy>

可以看到节点变成,其实PATH() 括号内的参数是控制节点名称的,这样的话大家可以看一下如果是空字符串(不是没有参数)会是什么结果?

select UserID,UserName from @TempTable FOR XML PATH('')

执行上面这段脚本将生成结果:

<UserID>1</UserID>
<UserName>a</UserName>
<UserID>2</UserID>
<UserName>b</UserName>

这样就不显示上级节点了,大家知道在 PATH 模式中,列名或列别名被作为 XPath 表达式来处理,也就是说,是列的名字,这样大胆试验一下不给指定列名和别名会是怎么样?

select CAST(UserID AS varchar) + '',UserName + '' from @TempTable FOR XML PATH('')

运行上面这句将生成结果

1a2b

所有数据都生成一行,而且还没有连接字符,这样的数据可能对大家没有用处,还可以再变化一下:

select CAST(UserID AS varchar) + ',',UserName + '',';' from @TempTable FOR XML PATH('')

生成结果

1,a;2,b;

大家现在明白了吧,可以通过控制参数来生成自己想要的结果,例如:

select '{' + CAST(UserID AS varchar) + ',','"' +UserName + '"','}' from @TempTable FOR XML PATH('')

生成结果

{1,”a”}{2,”b”}

还可以生成其他格式,大家可以根据自己需要的格式进行组合。

下面是一个数据统计的应用,希望大家可以通过下面的实例想到更多的应用

DECLARE @T1 table(UserID int , UserName nvarchar(50),CityName nvarchar(50));
insert into @T1 (UserID,UserName,CityName) values (1,'a','上海')
insert into @T1 (UserID,UserName,CityName) values (2,'b','北京')
insert into @T1 (UserID,UserName,CityName) values (3,'c','上海')
insert into @T1 (UserID,UserName,CityName) values (4,'d','北京')
insert into @T1 (UserID,UserName,CityName) values (5,'e','上海')

SELECT B.CityName,LEFT(UserList,LEN(UserList)-1) FROM (
SELECT CityName,
(SELECT UserName+',' FROM @T1 WHERE CityName=A.CityName FOR XML PATH('')) AS UserList
FROM @T1 A 
GROUP BY CityName
) B

生成结果(每个城市的用户名)

北京 b,d
上海 a,c,e

分类
数据库 编程开发

SQL如何按字段删除重复记录

--如何按字段删除重复记录 
  
 一张表里面以两个字段为唯一字段,当几条记录的这两个字段完全相同时,需要删除重复项,如下表 
 a b c d 
 1 2 3 4 
 1 5 3 5 
 1 2 7 9 
 以a、b为唯一字段,第一条和第三条的a、b完全相同,所以,需要删除第一条记录1 2 3 4 或者第三条记录1 2 7 9 
 即如下结果: 
 a b c d 
 1 2 3 4 
 1 5 3 5 
 或 
 a b c d 
 1 5 3 5 
 1 2 7 9 
  
 请问各位大侠这种sql语句怎么写 
  
  
 CREATE TABLE Tb1(id int, [a] varchar(255), [b] varchar(255), [c] varchar(255), [d] varchar(255)) 
 INSERT Tb1(id, [a], [b], [c], [d]) 
            SELECT 1, '1','2','3','4' 
 UNION ALL  SELECT 2, '1','5','3','5' 
 UNION ALL  SELECT 3, '1','2','7','9' 
 UNION ALL  SELECT 4, '1','4','7','6' 
  
 delete Tb1 where [id] not in (select max([id]) from Tb1 group by a,b ) 
 select * from tb1 
   
 drop table tb1 
  
 如果要同时删除第一和第三行 
 即如下结果: 
 a b c d 
 1 5 3 5 
  
 语句如下: 
  
 delete m from tb t 
 inner join 
 ( 
 select a ,b 
 from tb 
 group by a , b 
 having count(*)>1 
 )n 
 on m.a = n.a and m.b = n.b  
 或 
 delete * from tb as m, 
 ( 
 select a ,b 
 from tb 
 group by a , b 
 having count(*)>1 
 )n 
 where m.a = n.a and m.b = n.b  
  
  
 ------------------------------------------------------------------------------------ 
 在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢?谢谢! 
 1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断 
 select * from people 
 where peopleId in (select  peopleId  from  people  group  by  peopleId  having  count(peopleId) > 1) 
  
 2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录 
 delete from people  
 where peopleId  in (select  peopleId  from people  group  by  peopleId   having  count(peopleId) > 1) 
 and rowid not in (select min(rowid) from  people  group by peopleId  having count(peopleId )>1) 
  
 3、查找表中多余的重复记录(多个字段)  
 select * from vitae a 
 where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by peopleId,seq  having count(*) > 1) 
  
 4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录 
 delete from vitae a 
 where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) 
 and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1) 
  
 5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录 
 select * from vitae a 
 where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) 
 and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1) 
  
 比方说在A表中存在一个字段“name”,而且不同记录之间的“name”值有可能会相同, 
 现在就是需要查询出在该表中的各记录之间,“name”值存在重复的项; 
 Select Name,Count(*) From A Group By Name Having Count(*) > 1 
  
 如果还查性别也相同大则如下: 
 Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1 
 ------------------------------------------------------------------------------------------------ 
 declare @max integer,@id integer 
 declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >; 1 
 open cur_rows 
 fetch cur_rows into @id,@max 
 while @@fetch_status=0 
 begin 
 select @max = @max -1 
 set rowcount @max 
 delete from 表名 where 主字段 = @id 
 fetch cur_rows into @id,@max 
 end 
 close cur_rows 
 set rowcount 0 
  
 方法二 
   有两个意义上的重复记录,一是完全重复的记录,也即所有字段均重复的记录,二是部分关键字段重复的记录,比如Name字段重复,而其他字段不一定重复或都重复可以忽略。 
   1、对于第一种重复,比较容易解决,使用 
 select distinct * from tableName 
   就可以得到无重复记录的结果集。 
   如果该表需要删除重复的记录(重复记录保留1条),可以按以下方法删除 
 select distinct * into #Tmp from tableName 
 drop table tableName 
 select * into tableName from #Tmp 
 drop table #Tmp 
   发生这种重复的原因是表设计不周产生的,增加唯一索引列即可解决。 
  
   2、这类重复问题通常要求保留重复记录中的第一条记录,操作方法如下 
   假设有重复的字段为Name,Address,要求得到这两个字段唯一的结果集 
 select identity(int,1,1) as autoID, * into #Tmp from tableName 
 select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID 
 select * from #Tmp where autoID in(select autoID from #tmp2) 
   最后一个select即得到了Name,Address不重复的结果集(但多了一个autoID字段,实际写时可以写在select子句中省去此列) 
 select * from tablename where id in ( 
 select id from tablename  
 group by id  
 having count(id) > 1)
分类
数据库

Oracle数据导入导出imp/exp-备份恢复

 

功能:Oracle数据导入导出imp/exp就相当与oracle数据还原与备份。大多情况都可以用Oracle数据导入导出完成数据的备份和还原(不会造成数据的丢失)。

  Oracle有个好处,虽然你的电脑不是服务器,但是你装了oracle客户端,并建立了连接 (通过net8 assistant中本地——>服务命名 添加正确的服务命名

  其实你可以想成是客户端与服务器端修了条路,然后数据就可以被拉过来了)这样你可以把数据导出到本地,虽然可能服务器离你很远。你同样可以把dmp文件从本地导入到远处的数据库服务器中。

  利用这个功能你可以构建俩个相同的数据库,一个用来测试,一个用来正式使用。

  执行环境:可以在SQLPLUS.EXE或者DOS(命令行)中执行,DOS中可以执行时由于 在oracle 8i 中  安装目录\ora81\BIN被设置为全局路径,该目录下有EXP.EXE与IMP.EXE文件被用来执行导入导出。

  oracle用java编写,我想SQLPLUS.EXE、EXP.EXE、IMP.EXE这俩个文件是被包装后的类文件。SQLPLUS.EXE调用EXP.EXE、IMP.EXE他们所包裹的类,完成导入导出功能。

  下面介绍的是导入导出的实例,向导入导出看实例基本上就可以完成,因为导入导出很简单。

  数据导出:

  1 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中

1 exp system/[email protected] file=d:\daochu.dmp full=y

  2 将数据库中system用户与sys用户的表导出

1 exp system/manager[email protected] file=d:\daochu.dmp owner=(system,sys)

  3 将数据库中的表table1 、table2导出

1 exp system/[email protected] file=d:\daochu.dmp tables=(table1,table2)

  4 将数据库中的表table1中的字段filed1以"00"打头的数据导出

1 exp system/[email protected] file=d:\daochu.dmp tables=(table1) query=\" where filed1 like'00%'\"

  上面是常用的导出,对于压缩我不太在意,用winzip把dmp文件可以很好的压缩。

  不过在上面命令后面 加上 compress=y  就可以了

  数据的导入1 将D:\daochu.dmp 中的数据导入 TEST数据库中。

1 imp system/[email protected]  file=d:\daochu.dmp

  上面可能有点问题,因为有的表已经存在,然后它就报错,对该表就不进行导入。

  在后面加上 ignore=y 就可以了。

  2 将d:\daochu.dmp中的表table1 导入

1 imp system/[email protected]  file=d:\daochu.dmp  tables=(table1)

  基本上上面的导入导出够用了。不少情况我是将表彻底删除,然后导入。

  注意:

  你要有足够的权限,权限不够它会提示你。

  数据库时可以连上的。可以用tnsping TEST 来获得数据库TEST能否连上。

分类
数据库

JDBC(或Hibernate)调用Oracle存储过程

1、存储过程

包头:

create or replace package uwf.TOOLS is
  -- Author  : lwq
  -- Created : 2008-3-18 lwq 11:59:35
  -- Purpose : 
  -- Public type declarations
  function PROCESSDEFINITIONS_OF_USER(userID in long)return SYS_REFCURSOR;
end TOOLS;

包体:

create or replace package body uwf.TOOLS as
function PROCESSDEFINITIONS_OF_USER(userID in long) 
return SYS_REFCURSOR
as 
 definitions_ SYS_REFCURSOR;
begin
      OPEN definitions_ FOR 
      select ID_,NAME_
      from uwf.jbpm_processdefinition;
      return definitions_;
end PROCESSDEFINITIONS_OF_USER;
end TOOLS;

2、JDBC调用存储过程

try { 
  Connection conn =XXXXX.connection();
  CallableStatement cstmt = conn.prepareCall("{?= call uwf.TOOLS.PROCESSDEFINITIONS_OF_USER(?)}"); 
  cstmt.setLong(2, userID);
  cstmt.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR); 
  cstmt.execute(); 
     ResultSet rs = (ResultSet)cstmt.getObject(1);
       while(rs.next()){
         System.out.println("id : " + rs.getLong("ID_"));
         System.out.println("name : " + rs.getString("NAME_"));
       }// end while
  } catch (SQLException sqlException) { 
   sqlException.printStackTrace(); 
  }

3、Hibernate调用存储过程

List retList=null;
  Query query= XXXXXX.getSession().getNamedQuery("getProcessDefinitionsByUser"); 
  query.setLong(0, userID);
  retList = query.list();
  Iterator it =retList.iterator();
  while(it.hasNext())
  {
   Object[] pdObject = (Object[])it.next();
   //流程定义主键
   Long pdId=((BigDecimal)pdObject[0]).longValue();
   //流程定义名字
   String pdName=pdObject[1].toString();
    。。。。。。。。
  }

在hbm文件里要配置

<sql-query name="getProcessDefinitionsByUser" callable="true">
   {?=call uwf.TOOLS.PROCESSDEFINITIONS_OF_USER(?)}
  </sql-query>
分类
数据库

用java调用oracle存储过程总结

这段时间开始学习写存储过程,主要原因还是因为工作需要吧,本来以为很简单的,但几经挫折,豪气消磨殆尽,但总算搞通了,为了避免后来者少走弯路,特记述与此,同时亦对自己进行鼓励。

一:无返回值的存储过程

存储过程为:

CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2)  AS
BEGIN
INSERT INTO HYQ.B_ID (I_ID,I_NAME) VALUES (PARA1, PARA2);
END TESTA;

然后呢,在java里调用时就用下面的代码:

package com.hyq.src;
 
import java.sql.*;
import java.sql.ResultSet;
 
public class TestProcedureOne {
public TestProcedureOne() {
}
public static void main(String[] args ){
String driver = "oracle.jdbc.driver.OracleDriver";
String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521: hyq ";
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
CallableStatement cstmt = null;
 
try {
Class.forName(driver);
conn =  DriverManager.getConnection(strUrl, " hyq ", " hyq ");
CallableStatement proc = null;
proc = conn.prepareCall("{ call HYQ.TESTA(?,?) }");
proc.setString(1, "100");
proc.setString(2, "TestOne");
proc.execute();
}
catch (SQLException ex2) {
ex2.printStackTrace();
}
catch (Exception ex2) {
ex2.printStackTrace();
}
finally{
try {
if(rs != null){
rs.close();
if(stmt!=null){
stmt.close();
}
if(conn!=null){
conn.close();
}
}
}
catch (SQLException ex1) {
}
}
}
}

当然了,这就先要求要建张表TESTTB,里面两个字段(I_ID,I_NAME)。

二:有返回值的存储过程(非列表)

存储过程为:

CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2)  AS
BEGIN
SELECT INTO PARA2 FROM TESTTB WHERE I_ID= PARA1;
END TESTB;

在java里调用时就用下面的代码:

package com.hyq.src;
 
public class TestProcedureTWO {
public TestProcedureTWO() {
}
public static void main(String[] args ){
String driver = "oracle.jdbc.driver.OracleDriver";
String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:hyq";
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
try {
Class.forName(driver);
conn =  DriverManager.getConnection(strUrl, " hyq ", " hyq ");
CallableStatement proc = null;
proc = conn.prepareCall("{ call HYQ.TESTB(?,?) }");
proc.setString(1, "100");
proc.registerOutParameter(2, Types.VARCHAR);
proc.execute();
String testPrint = proc.getString(2);
System.out.println("=testPrint=is="+testPrint);
}
catch (SQLException ex2) {
ex2.printStackTrace();
}
catch (Exception ex2) {
ex2.printStackTrace();
}
finally{
try {
if(rs != null){
rs.close();
if(stmt!=null){
stmt.close();
}
if(conn!=null){
conn.close();
}
}
}
catch (SQLException ex1) {
}
}
}
}
 
}

注意,这里的proc.getString(2)中的数值2并非任意的,而是和存储过程中的out列对应的,如果out是在第一个位置,那就是proc.getString(1),如果是第三个位置,就是proc.getString(3),当然也可以同时有多个返回值,那就是再多加几个out参数了。

三:返回列表

由于oracle存储过程没有返回值,它的所有返回值都是通过out参数来替代的,列表同样也不例外,但由于是集合,所以不能用一般的参数,必须要用pagkage了.所以要分两部分,

1,  建一个程序包。如下:

CREATE OR REPLACE PACKAGE TESTPACKAGE  AS
TYPE Test_CURSOR IS REF CURSOR;
end TESTPACKAGE;

2,建立存储过程,存储过程为:

CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR) IS
BEGIN
OPEN p_CURSOR FOR SELECT * FROM HYQ.TESTTB;
END TESTC;

可以看到,它是把游标(可以理解为一个指针),作为一个out 参数来返回值的。

在java里调用时就用下面的代码:

package com.hyq.src;
import java.sql.*;
import java.io.OutputStream;
import java.io.Writer;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import oracle.jdbc.driver.*;
 
 
public class TestProcedureTHREE {
public TestProcedureTHREE() {
}
public static void main(String[] args ){
String driver = "oracle.jdbc.driver.OracleDriver";
String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:hyq";
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
 
try {
Class.forName(driver);
conn =  DriverManager.getConnection(strUrl, "hyq", "hyq");
 
CallableStatement proc = null;
proc = conn.prepareCall("{ call hyq.testc(?) }");
proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
proc.execute();
rs = (ResultSet)proc.getObject(1);
 
while(rs.next())
{
System.out.println("<tr><td>" + rs.getString(1) + "</td><td>"+rs.getString(2)+"</td></tr>");
}
}
catch (SQLException ex2) {
ex2.printStackTrace();
}
catch (Exception ex2) {
ex2.printStackTrace();
}
finally{
try {
if(rs != null){
rs.close();
if(stmt!=null){
stmt.close();
}
if(conn!=null){
conn.close();
}
}
}
catch (SQLException ex1) {
}
}
}
}

在这里要注意,在执行前一定要先把oracle的驱动包放到class路径里,否则会报错的。