数据库的一些命令

佚名 6年前 (2019-04-22) 随笔 687人围观抢沙发百度已收录

查出锁定object的session的信息以及被锁定的object名 SELECT l.session_id sid, s.serial#, l.locked_mode,l.oracle_username, l.os_user_name,s.machine, s.terminal, o.object_name, s.logon_time FROM v$locked_object l, all_objects o, v$session s WHERE l.object_id = o.object_id AND l.session_id = s.sid ORDER BY sid, s.serial# ; 杀锁命令----解锁 alter system kill session 'sid,serial#' 数据库修改ORACLE 11G 数据空表不占空间访问方法1：alter system set deferred_segment_creation = false; -- 方法2：select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0; 执行得到的结果在命令窗口执行在CMD命令窗口执行 set NLS_LANG=american_america.AL32UTF8 exp aaa/aaa@ip/orcl file=F:/xxx.dmp log=F:/xxx.log 导出 set NLS_LANG=american_america.AL32UTF8 imp aaa/aaa@ip/orcl full=y file=F:/xxx.dmp log=F:/xxx.log 导入两表结构完全相同，把b表中的数据导入到a表中 insert into a select * from b 两表结构部分相同，把b表中的数据导入到a表中 insert into a(col1,col2,col3...) select (col1,col2,col3...) from b oracle把某列数据插入到另一列 update [表名] set [另外一列]=[前一列] 删除表数据 Delete from tablename where 1=1 oracle时间戳（毫秒）转为date SELECT TO_CHAR(时间戳/ (1000 * 60 * 60 * 24) + TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH:MI:SS'), 'YYYY-MM-DD HH:MI:SS') AS CDATE FROM PDA_ORDER t; 1、执行插入语句，获取自动生成的递增的ID值 INSERT INTO SysRole (RoleName,RoleDesc) VALUES('超级管理员','描述') SELECT @@identity as 'id' 2、在同一数据表找到相同属性的记录 SELECT RoleName FROM SysRole GROUP BY RoleName HAVING COUNT(ID)>1 3、随机提取记录 SELECT RoleName FROM SysRole ORDER BY NEWID 4、按照姓氏笔画排序 SELECT RoleName FROM SysRole ORDER BY RoleName COLLATE Chinese_PRC_Stroke_CI_AS 5、获取某个日期所在月份的最大天数 SELECT DAY(DATEADD(dd,-DAY('2017-5-12'),DATEADD(mm,1,'2017-5-12'))) as 'DayNumber' 6、实现用0或1 来显式男或女 select name ,Sex= case Sex when '0' then '男' when '1' then '女' end from Sys_user 7、显式文章、提交人和最后回复时间 select a.title,a.username,b.adddate from tablename as a ,select(max(adddate)) from tablename where tablename.title=a.title) b 8、嵌套子查询 select a,b,c from table1 where a in(select a from table2) 9、复制表结构(源表名：a ,目标表名：b) select * into from a where 1<>1 或者 select top 0 * into [b] from [a] 10、复制表数据(源表名：a ,目标表名：b) insert into b(name,desc,createtime) as select name,desc,createtime from table1 as a 11、通配符的使用 select * from table1 where name like 'A-L%' --筛选name列首字母在A~L之间的记录 select * from table1 where name like'[ABCD]%' --筛选name列首字母是 A、B、C或D的记录 select * from table1 where name like '[A-DH]%' --筛选name列首字母在A~D或者是H的记录 select * from table1 where name like '[^D]%' --筛选name列首字母不是D的记录 select * from table1 where name like 'Xiao_ming' -- _ 匹配任意单个字符 12、操作列 alter table table1 add desc nvarchar(2000) not null default '' --新增一列 alter table table1 alter column desc nvarchar(500) --修改列 alter table table1 drop colmn desc --删除列 13、存储过程 --插入数据的存储过程示例 CREATE procedure [dbo].[proc_CreateUser] @username nvarchar(200), @password nvarchar(200), @truename nvarchar(200), @role int as insert INTO Users (UserName,Password,TrueName,Role,CreateDate) VALUES(@username,@password,@truename,@role,GETDATE) --更新数据示例 CREATE procedure [dbo].[proc_UpdateUser] @id int, @username nvarchar(200) as update Users set UserName=@username where Id=@id --删除数据 CREATE procedure [dbo].[proc_DelUser] @id int as delete Users where Id=@id 14、视图示例 create view view_user as select id from user go 15、启动数据库 export oracle_sid=repo(数据库名称) lsnrctl start(启动监听) lsnrctl status(查看监听状态) sqlplus / as sysdba(登录) startup(启动) 查看sequence SELECT LAST_NUMBER,INCREMENT_BY FROM USER_SEQUENCES WHERE SEQUENCE_NAME='ORG_SEQ' 创建sequence create sequence S_S_DEPART minvalue 1 maxvalue 999999999999 start with 1 increment by 1 nocache;