常用sql语句

分手后的思念是犯贱 2022-09-18 10:54 289阅读 0赞

1.限制返回记录的行数（4-10行）

select rownum,c1 from t144 where rownum<10  
minus  
select rownum,c1 from t144 where rownum<5

2.批量插入

insert into tablename(select \* from othertable)

或

insert into (column1,column2,,,,,,,)tablename(select column1,column2,,,,,,,from othertable)

3,分组函数(结果中出现的列要在group by 中出现)

select count(age), username, , ,... from table   group by age,usename,,,,...

4.查看数据库中的存在的表

select table\_name  from dba\_tables (include all users)

select \* from tab(include view)

5.高效update，一次修改多个列

update smsmonitor sms  
 set (sms.smsmonitor\_sendflag, sms.smsmonitor\_maxnum) = (select s.smsmonitor\_sendflag,  
                                                                  s.smsmonitor\_maxnum  
                                                             from smsmonitor s  
                                                            where s.smsmonitor\_id =  
                                                                  'i74668')  
 where sms.smsmonitor\_id = 'i74669'

6，高效删除表中重复记录

DELETE FROM EMP E  
  
WHERE E.ROWID > (SELECT MIN(X.ROWID)  
  
FROM EMP X  
  
WHERE X.EMP\_NO = E.EMP\_NO);

7.如何定位重要(消耗资源多)的SQL  
  select sql\_text  
  from v$sql  
where disk\_reads > 1000 or (executions > 0 and buffer\_gets/executions > 30000);

8.如何跟踪某个session的SQL  
  exec dbms\_system.set\_sql\_trace\_in\_session(sid,serial\#,&sql\_trace);

select sid,serial\# from v$session where sid = (select sid from v$mystat where rownum = 1);  
  exec dbms\_system.set\_ev(&sid,&serial\#,&event\_10046,&level\_12,'');

8，mysql中字段操作

增加**字段**  
alter table name add code char(200) default 0  
删除**字段**  
ALTER TABLE table\_NAME DROP COLUMN column\_NAME  
修改**字段**类型  
ALTER TABLE table\_name  ALTER COLUMN column\_name new\_data\_type

添加主键字段

alter table tb add 列名 int primary key default 0

查询被锁住的sql语句

SELECT /\*+ rule \*/ s.username,  
decode(l.type,'TM','TABLE LOCK',  
'TX','ROW LOCK',  
NULL) LOCK\_LEVEL,  
o.owner,o.object\_name,o.object\_type,  
s.sid,s.serial\#,s.terminal,s.machine,s.program,s.osuser  
FROM v$session s,v$lock l,dba\_objects o  
WHERE l.sid = s.sid  
AND l.id1 = o.object\_id(+)  
AND s.username is NOT Null

解锁表

\--alter system kill session 'sid,serial\#';  
![None.gif][]\--把锁给KILL掉  
![None.gif][]alter system kill session '146,21177';

解锁用户sql

ALTER USER username ACCOUNT UNLOCK;

根据索引名称查询该索引所在的表信息

SELECT table\_name FROM all\_indexes WHERE index\_name='DCHNBACCFEEDET\_PK'

查询该索引的信息

SELECT \* FROM all\_objects WHERE object\_name LIKE 'DCHNBACCFEEDET\_PK'

[None.gif]: /images/20220828/4054024531af4078864be7babf24e729.png