Java教程

工作中常用的sql小技巧

本文主要是介绍工作中常用的sql小技巧,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

工作中常用的sql小技巧

  • 1.oracle、sqlserver、mysql备份表数据的SQL
    • 1.1.mysql备份表数据
    • 1.2.oracle备份表数据
    • 1.3.sqlserver备份表数据
  • 2.oracle、sqlserver、mysql查看锁表和解锁方法
    • 2.1.mysql查看锁表和解锁方法
      • 2.1.1.查看表是否被锁
      • 2.1.2.查看表被锁状态和结束死锁步骤
    • 2.2.oracle查看锁表和解锁方法
      • 2.2.1.相关表
      • 2.2.2.查看被锁的表
      • 2.2.3.查看那个用户那个进程照成死锁
      • 2.2.4.查看连接的进程
      • 2.2.5.查出锁定表的sid, serial#,os_user_name, machine_name, terminal,锁的type,mode
      • 2.2.6.杀掉进程 sid,serial#
    • 2.3.sqlserver查看锁表和解锁方法
      • 2.3.1查看被锁表
      • 2.3.2解除锁
      • 2.3.3.查看被锁表
      • 2.3.4.解锁
  • 3.oracle、sqlserver、mysql创建、新增、删除主键
    • 3.1.oracle创建、新增、删除主键
    • 3.2.sqlserver创建、新增、删除主键
    • 3.3.mysql创建、新增、删除主键
  • 4.oracle、sqlserver、mysql表字段增加、修改、删除
    • 4.1.mysql表字段增加、修改、删除
      • 4.1.1.mysql表字段增加
      • 4.1.2.mysql表字段修改
      • 4.1.3.mysql表字段删除
      • 4.1.4.mysql同时添加和修改多列
    • 4.2.sqlserver表字段增加、修改、删除
      • 4.2.1.sqlserver表字段增加
      • 4.2.2.sqlserver表字段修改
      • 4.2.3.sqlserver表字段删除
      • 4.2.4.sqlserver同时添加和修改多列
    • 4.3.oracle表字段增加、修改、删除
      • 4.3.1.oracle表字段增加
      • 4.3.2.oracle表字段修改
      • 4.3.3.oracle表字段删除
      • 4.3.4.oracle同时添加和修改多列
  • 5.oracle、sqlserver、mysql表或者字段是否存在
    • 5.1.mysql表或者字段是否存在
    • 5.2.oracle表或者字段是否存在
    • 5.3.sqlserver表或者字段是否存在

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 l o c k e d o b j e c t a , v locked_object a,v lockedo​bjecta,vsession 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 s e s s i o n s , v session s, v sessions,vlock 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)

    )

    1. 联合主键:

    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 字段名


这篇关于工作中常用的sql小技巧的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!