oracle 、mysql、 sql server使用记录

oracle 、mysql、 sql server使用记录 

mysql常用命令：

mysqld --启动mysql数据库
show databases; -- 查看数据库
use database; -- 选择数据库
show tables; -- 查看表
desc tableName; -- 查看表结构
show processlists; --如果查询慢，可以查看是否锁表
exit --退出mysql
service mysqld restart; --重启mysql服务

mysql声明过程

mysql中的procedure：
//声明
declare i int;
//赋值
set i = 1;
//或者使用 @i 直接使用，不用声明 

//创建procedure模板
create procedure dbname.myMethod()
begin
declare i int;
set i = 1;
test:while i<3 do
select i;
end while test;
//如果有增删改，不要忘记commit
commit;
end; 

//调用过程
call myMethod();

数据删除、清空

drop、delete和truncate：

drop：连同表结构和表数据直接删除

truncate：清空表数据，但不删除表结构，而且autoincrement从0开始计数；

delete：可以加where条件，删除特定区域记录，但是主键autoincrement继续之前的数字计数。

语法：

drop table tableName;
truncate table tableName;
delete from tableName where 1=1; 

速度,一般来说: drop> truncate > delete

truncate和 delete只删除数据不删除表的结构(定义)

数据导入导出

mysql导出数据

select * from test_table into outfile "d:/a.txt"; 

mysql导入数据

LOAD DATA INFILE '/tmp/1.txt' INTO TABLE tmpuserid FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; 

sql server

使用bcp导出 

oracle

待定 

表复制

mysql

1)先创建table1

create table table1(id bigint);

2)插入

insert into table1(id) select id from table2; --要求table1必须存在,字段必须一一对应 

oralce

方法一：复制表结构和数据

create table temp_clientloginuser
as
select distinct userid from tbuserloginlog; 

方法二：仅复制数据

--如果表结构一样
insert into mytable select * from yourtable;

-- 如果表结构不一样
insert into mytable(column1,column2...) select column1,column2 ... from yourtable;

3.ms sqlserver

select ptid into newtable from tmpuserid;
--sqlserver 会自动新建一张表结构和 from表一样的新表 

其他注意问题

1.sql server 查询select的时候要记得加 with(nolock) ，防止锁住；

2. oracle 中查询用户所包含的所有对象信息

select * from user_source

where lower(text) like '%yyyy%'

3.使用mysql和oracle时一定要对DML语言进行显式地commit，不然insert的数据不会生效，切记！

4. 要分清哪些命令是DML，哪些是DDL

DDL是数据定义语言，如drop，alter，truncate等都是DDL；

 

DML是数据操纵语言，如nsert，update，delete，merge等都是DML，

在oracle里DML需要显式地commit，当然可以rollback的，而DDL是不可以的。

5.面对表数据量很大，成千万上亿的情况，查询的时候不要join，直接查范围大也会很慢，最好的办法是将需要的数据导出到一张临时表，然后在临时表中进行操作，速度会快很对。

6.如何跨库？

在oracle中使用database link方式，如 在database3中使用

> select * from datatable@database2;

查询，但是，最好减少跨库查询，如确实需要，可以将需要的数据导入到一张临时表，然后在临时表中操作。 

not exists

例子：

select * from temp_clientloginuser_info a
where not exists(select 1 from tvsumamountmonth b where lower(a.ptid)=b.poptid and b.sumamount>0)

 

update 2013-7-12 10:07:45

 

mysql中日期函数：

// 当前时间
select now();   --2008-12-29 16:25:46
CURDATE() -- 2008-12-29
CURTIME() -- 16:25:46
SELECT DATE_ADD("1997-12-31 23:59:59",INTERVAL -1 DAY);  //日期增减
DATE_FORMAT(@insertDate, '%Y-%m-%d') //日期格式化，相当于oralce中的trunc方法 

sql server 中的日期函数：

-- 获取当前时间
select getdate();
-- 返回 2013-06-20 ,相当于oracle中的trunc方法
select CONVERT(varchar(10), getdate(),120); 

select getdate();
-- 返回 2013-06-20 ,相当于oracle中的trunc方法字符串转date：
select CONVERT(varchar(10), getdate(),120); 
CONVERT(datetime, getdate(),120);
convert(int,sum(NewSingleUser))
--sum()的返回值是根据里面的字段决定的，这里返回int64 long类型，convert函数把long转化为int类型

 

oracle 日期函数 to_date()

sysdate :  --系统日期
to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss') -- 时间格式转化
to_date('2004-05-07 13:23:44','hh24')  --13 取小时数
sysdate+1  --加一天
sysdate+1/24 --加1小时
sysdate+1/(24*60) --加1分钟
sysdate+1/(24*60*60) --加1秒钟
select to_date('2003-11-17','yyyy-mm-dd')+1 from dual;  --2003-11-18
add_months(sysdate,1)  --加一个月
add_months(sysdate,12) --加一年

 

alter语句

--oracle 删除一列
alter table t_jm_user drop column  USR_EmailValidate;


--sql server要修改表中列的数据类型
ALTER TABLE [PV_HIS_JifenWall_Report] alter COLUMN [type] nvarchar(10)

--删除mysql的一个分区表：
ALTER TABLE WebStat.PV_HIS drop partition p20130620;

查看分区表
SELECT TABLE_NAME, PARTITION_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE  Table_name = 'PV_HIS'; 

mysql

null与空’’

mysql中is not null 和 <> ''

-- sql语句以...结尾

delimiter //

查看mysql是否锁住：
mysql > show processlist;

主要看STATE列，

Locked　被其他查询锁住了。

Sending data　正在处理SELECT查询的记录，同时正在把结果发送给客户端。

Sorting for group　正在为GROUP BY做排序。
Sorting for order　正在为ORDER BY做排序。

Sorting result For a SELECT statement, this is similar to Creating sort index, but for nontemporary tables.

NULL This state is used for the SHOW PROCESSLIST state.