数据库的那些事情——重点sql记录

解表锁表

oracle 命令

--锁表sql查询，可查看导致锁表的sql
SELECT L.SESSION_ID SID,
       S.SERIAL#,
       L.LOCKED_MODE,
       L.ORACLE_USERNAME,
       S.USER#,
       L.OS_USER_NAME,
       S.MACHINE,
       S.TERMINAL,
       A.SQL_TEXT,
       A.ACTION
  FROM V$SQLAREA A, V$SESSION S, V$LOCKED_OBJECT L
 WHERE L.SESSION_ID = S.SID
   AND S.PREV_SQL_ADDR = A.ADDRESS
 ORDER BY SID, S.SERIAL#;
 
 --杀死锁表进程
 alter system kill session 'SID,SERIAL#';

查询表结构

1.mysql表结构查询sql

SELECT b.table_name    表名,
       b.TABLE_COMMENT 表注释,
       a.COLUMN_NAME   列名,
       COLUMN_TYPE     列类型（长度）,
       COLUMN_COMMENT  列说明
  FROM information_schema. COLUMNS a, INFORMATION_SCHEMA. TABLES b
 WHERE a.table_name = b.table_name;

2.orcale常用查看表结构命令

--获取表： 
select table_name from user_tables; --当前用户的表
select table_name from all_tables; --所有用户的表
select table_name from dba_tables; -- 包括系统表

select table_name from dba_tables where owner = '用户名'

--user_tables： table_name, tablespace_name, last_analyzed等

--dba_tables： ower, table_name, tablespace_name, last_analyzed等

--all_tables： ower, table_name, tablespace_name, last_analyzed等

--all_objects： ower, object_name, subobject_name, object_id, created,
--last_ddl_time, timestamp, status等

--  获取表字段：

select * from user_tab_columns where Table_Name = '用户表';
select * from all_tab_columns where Table_Name = '用户表';
select * from dba_tab_columns where Table_Name = '用户表';

--user_tab_columns： table_name, column_name, data_type, data_length, data_precision, data_scale, nullable, column_id等

--all_tab_columns ： ower, table_name, column_name, data_type, data_length, data_precision, data_scale, nullable, column_id等

--dba_tab_columns： ower, table_name, column_name, data_type, data_length, data_precision, data_scale, nullable, column_id等

--获取表注释：

select * from user_tab_comments

--user_tab_comments：table_name,table_type,comments

--相应的还有dba_tab_comments，all_tab_comments，这两个比user_tab_comments多了ower列。
 
--获取字段注释：
select * from user_col_comments

--user_col_comments：table_name,column_name,comments

--最终需要：
 Select t.table_name,
        t.comments,
        t1.column_name,
        t1.comments
     from user_col_comments t1
     join user_tab_comments t
        on t.table_name = t1.table_name

基础增删改查

把一个表的数据导入另外一张表

insert 表1 into(列名1,列名2,...)
select 列名1,列名2 from 表2，where 列1 not in (select 列1 from 表1 )

很好用的不锁表改数据的一条查询语句

select t.*, t.rowid from STUDENT_TJB t

排序（正序：asc 倒叙：desc）

--输入
drop table if exists  `salaries` ; 
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10005,94692,'2001-09-09','9999-01-01');
INSERT INTO salaries VALUES(10006,43311,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10007,88070,'2002-02-07','9999-01-01');
INSERT INTO salaries VALUES(10008,88070,'2002-02-07','9999-01-01');
INSERT INTO salaries VALUES(10009,95409,'2002-02-14','9999-01-01');
INSERT INTO salaries VALUES(10011,25828,'1990-01-22','9999-01-01');

（1）select distinct salary from salaries ORDER  by salary desc 
95409
94692
88958
88070
43311
25828
（2）select distinct salary from salaries ORDER  by salary asc 
25828
43311
88070
88958
94692
95409