工作中常用的sql小技巧

1.oracle、sqlserver、mysql备份表数据的SQL

1.1.mysql备份表数据

• 只复制表结构到新表

create table “新表” select * from “旧表” where 1 =2;

create table “新表” like “旧表”;

上面两种方式的区别：使用第一条语句，备份的新表并没有旧表的primary key 、auto_increment等属性，需要重新对新表进行设置。

• 复制表结构及数据到新表

create table “新表” select * from “旧表”;

将oldtable中所有的内容都拷贝过来，同时也存在备份的新表不具备旧表 primary key、auto_increment等属性，需要对新表再次设置。

---

1.2.oracle备份表数据

create table “新表” as select * from “旧表”;

---

1.3.sqlserver备份表数据

select * into “新表” from “旧表”;

---

2.oracle、sqlserver、mysql查看锁表和解锁方法

2.1.mysql查看锁表和解锁方法

2.1.1.查看表是否被锁

• 查看表是否被锁

show engine innodb status;

• 查看造成死锁的sql语句，分析索引情况，然后优化sql

show processlist;

• 查看造成死锁占用时间长的sql语句

show status like ‘%lock%;

---

2.1.2.查看表被锁状态和结束死锁步骤

• 查看表被锁状态

show OPEN TABLES where In_use > 0; //这个语句记录当前锁表状态

• 查询进程

show processlist;//查询表被锁进程

• 杀死进程

kill id;

• 分析锁表的SQL

分析相应SQL，给表加索引，常用字段加索引，表关联字段加索引。

• 查看正在锁的事物

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

• 查看等待锁的事物

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

---

2.2.oracle查看锁表和解锁方法

2.2.1.相关表

SELECT * FROM v$lock;
SELECT * FROM v$sqlarea;
SELECT * FROM v$session;
SELECT * FROM v$process ;
SELECT * FROM v$locked_object;
SELECT * FROM all_objects;
SELECT * FROM v$session_wait;

2.2.2.查看被锁的表

select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;

2.2.3.查看那个用户那个进程照成死锁

select b.username,b.sid,b.serial#,logon_time from v$locke{d}_{o}bjecta,v$session b where a.session_id = b.sid order by b.logon_time;

2.2.4.查看连接的进程

SELECT sid, serial#, username, osuser FROM v$session;

2.2.5.查出锁定表的sid, serial#,os_user_name, machine_name, terminal，锁的type,mode

SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,s.terminal, s.logon_time, l.type FROM v$sessions,v$lock l WHERE s.sid = l.sid AND s.username IS NOT NULL ORDER BY sid;

这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。

2.2.6.杀掉进程 sid,serial#

alter system kill session’210,11562’;

---

2.3.sqlserver查看锁表和解锁方法

2.3.1查看被锁表

select spId from master…SysProcesses where db_Name(dbID) = ‘数据库名称’ and spId <> @@SpId and dbID <> 0;

2.3.2解除锁

exec ('Kill '+cast(@spid as varchar));

2.3.3.查看被锁表

select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName from sys.dm_tran_locks where resource_type=‘OBJECT’;

spid 锁表进程

tableName 被锁表名

2.3.4.解锁

declare @spid int

Set @spid = 57 --锁表进程

declare @sql varchar(1000)

set @sql='kill '+cast(@spid as varchar)

exec(@sql)

---

3.oracle、sqlserver、mysql创建、新增、删除主键

3.1.oracle创建、新增、删除主键

• 创建表的同时创建主键约束

create table students (

studentid int ,

studentname varchar(8),

age int,

constraint yy primary key(studentid)

);

• 表建好后添加主键

alter table 表名 add constraint 主键名 primary key(主键字段);

• 删除主键

ALTER TABLE 表名 DROP CONSTRAINT 主键名;

3.2.sqlserver创建、新增、删除主键

• 创建表的同时创建主键约束

CREATE TABLE Personsfff(

Id_P int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

CONSTRAINT pk1_PersonID PRIMARY KEY (Id_P,LastName)

)

• 表建好后添加主键

alter table 表名 add constraint 主键名 primary key(字段名1,字段名2……);

• 删除主键

alter table 表名 drop constraint 主键名;

3.3.mysql创建、新增、删除主键

• 创建表的同时创建主键约束
  1. 单字段主键:

  CREATE TABLE EIMS_EMERGENCY_ENTER (
  
  SID VARCHAR(50) primary key NOT NULL,
  
  ENTERID VARCHAR(50) NOT NULL,
  
  DATA_TYPE VARCHAR(25) NOT NULL,
  
  DEPT_NAME VARCHAR(100)
  
  )

  2. 联合主键:

  CREATE TABLE EIMS_EMERGENCY_ENTER (
  
  SID VARCHAR(50) NOT NULL,
  
  ENTERID VARCHAR(50) NOT NULL,
  
  DATA_TYPE VARCHAR(25) NOT NULL,
  
  DEPT_NAME VARCHAR(100),
  
  primary key(SID,ENTERID)
  
  )

• 表建好后添加主键

ALTER TABLE <数据表名> ADD PRIMARY KEY(<列名>);

• 删除主键

ALTER TABLE TABLE_NAME DROP PRIMARY KEY;

---

4.oracle、sqlserver、mysql表字段增加、修改、删除

4.1.mysql表字段增加、修改、删除

4.1.1.mysql表字段增加

添加单列:
    ALTER TABLE 表名 ADD 列名 数据类型
添加多列:
    ALTER TABLE 表名 ADD 列名1 数据类型1,Add 列名2 数据类型2
另外，在添加字段时，还可以指定位数（日期类型除外）、是否为空、默认值
例如：ALTER TABLE t_test ADD type TINYINT(1) NOT NULL DEFAULT 0;

4.1.2.mysql表字段修改

修改单列数据类型:
    ALTER TABLE 表名 MODIFY COLUMN 列名 数据类型
同时修改多列数据类型:
    ALTER TABLE 表名 MODIFYCOLUMN 列名 数据类型,MODIFY COLUMN 列名 数据类型

另外，在修改字段时，也可以指定位数、是否为空、默认值
ALTER TABLE t_test MODIFY COLUMN type TINYINT(2) DEFAULT 1;

在对字段进行修改时，注意下面几个问题：

• 以前的字段如果有值为NULL，无法将字段修改为NOT NULL
• 如果新的字段类型无法匹配所有的值，则无法将字段修改为新的类型
• 默认值可以随便更改，但是修改后的默认值只对以后添加的记录有效，对以前的记录无效

4.1.3.mysql表字段删除

删除单列:
　　　　ALTER TABLE 表名 DROP COLUMN 列名
删除多列:
　　　　ALTER TABLE 表名 DROP COLUMN 列名1,DROP COLUMN 列名2

4.1.4.mysql同时添加和修改多列

    ALTER TABLE 表名 ADD 列名1 数据类型1,MODIFY COLUMN 列名 数据类型,DROP COLUMN 列名1
    （COLUMN 关键字可以省略）

---

4.2.sqlserver表字段增加、修改、删除

4.2.1.sqlserver表字段增加

添加单列:
　　　　ALTER TABLE 表名 ADD 列名 数据类型
添加多列:
　　　　ALTER TABLE 表名 ADD 列名1 数据类型1,列名2 数据类型2

4.2.2.sqlserver表字段修改

修改单列数据类型:
　　　　ALTER TABLE 表名 ALTER COLUMN 列名 数据类型
同时修改多列数据类型:
        不支持

4.2.3.sqlserver表字段删除

删除单列:
    ALTER TABLE 表名 DROP COLUMN 列名
删除多列:
    ALTER TABLE 表名 DROP COLUMN 列名1,列名2

4.2.4.sqlserver同时添加和修改多列

  不支持,每次只能进行一种操作.

---

4.3.oracle表字段增加、修改、删除

4.3.1.oracle表字段增加

添加单列:
　　　　ALTER TABLE 表名 ADD 列名 数据类型
添加多列:
　　　　ALTER TABLE 表名 ADD ( 列名1 数据类型1,列名2 数据类型2)

4.3.2.oracle表字段修改

修改单列数据类型:
　　　　ALTER TABLE 表名 MODIFY 列名 数据类型
同时修改多列数据类型:
　　　　ALTER TABLE 表名 MODIFY ( 列名1 数据类型1,列名2 数据类型2)

4.3.3.oracle表字段删除

删除单列:
　　　　ALTER TABLE 表名 DROP COLUMN 列名
删除多列:
　　　　ALTER TABLE 表名 DROP (列名1,列名2)

4.3.4.oracle同时添加和修改多列

  ALTER TABLE 表名 ADD ( 列名1 数据类型1,列名2 数据类型2) MODIFY ( 列名3 数据类型3,列名4 数据类型4)

---

5.oracle、sqlserver、mysql表或者字段是否存在

5.1.mysql表或者字段是否存在

• 表:

select COUNT(*) from information_schema.tables WHERE table_name = ‘table_name’;

• 字段

select COUNT(*) from information_schema.columns WHERE table_name = ‘table_name’ and column_name = ‘column_name’;

5.2.oracle表或者字段是否存在

• 表:

SELECT count(*) FROM USER_OBJECTS WHERE OBJECT_NAME = ‘table_name’;

• 字段

SELECT COUNT(*) FROM USER_TAB_COLUMNS WHERE TABLE_NAME = ‘table_name’ and column_name = ‘column_name’;

5.3.sqlserver表或者字段是否存在

• 表:

select COUNT(*) from dbo.sysobjects WHERE name= ‘table_name’;

• 字段

select COUNT(*) from syscolumns WHERE id=object_id(‘table_name’) and name= ‘column_name’;

–判断字段不存在则添加

if not exists(select * from sys.columns where name=‘字段名’ and [object_id]=object_id(N’表名’)) alter table 表名 add 字段名 nvarchar(255)

–判断字段存在则删除

if exists(select * from sys.columns where name=‘字段名’ and [object_id]=object_id(N’表名’)) alter table 表名 drop column 字段名

---