开发中踩过的SQL坑

由于个人目前接触比较多的有：oracle、sybase、mysql 这三种关系数据库系统，是在开发项目工作的时候，遇到比较常用的sql语句， 或者碰到折腾了心态一些坑，就会偶尔记录下，希望能给大家带来方便和有所参考，少走些弯路、能多打几句代码， 同时也给我自己带来方便（以后文章可以用作备忘录啦！！！）

Oracle

--快速删除表全部数据（不删除表结构）
truncate TABLE 表名	

--解决数据库锁表情况处理：
--1.查询被锁的会话ID：
select session_id from v$locked_object （结果:370）
--2.查询上面会话的详细信息：
SELECT sid, serial#, username, osuser FROM v$session where sid = 370
--3.杀掉锁定的会话：
ALTER SYSTEM KILL SESSION '370,2696' 

-----解决字段值过长问题：将字段类型varchar2改成clob类型-----
--（直接变更字段类型会报：ora22858 数据类型的变更无效）
--1.新增一个clob类型的字段：
alter table test add name1 clob
--2.将原来的varchar列的值复制的新列clob：
update test set name1=name
--3.删除原来的varchar列：
alter table test drop column name
--4.将新列clob类型重新命名：
alter table test rename column name1 to name

--查看ORACLE内存使用情况：
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / 	D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1;

--查询系统当前时间
select * from sysdate

--查询今年内的数据
> (TO_DATE(CONCAT(substr(TO_CHAR(sysdate, 'YYYY-MM-DD HH24:MI:SS'),0,4),'-01-01 00:00:00'),'YYYY-MM-DD HH24:MI:SS'))

--创建表空间
CREATE TABLESPACE 表空间名  DATAFILE '/orcl_data/AICRIS_dev1.dbf' SIZE 30G;
--创建用户（即数据库名）
create user 用户名  identified by 用户密码  default tablespace 表空间名;
--给新用户赋权
grant create table to 用户名;
grant connect to 用户名;
alter user aicris quota unlimited on 表空间名;

-----备份与还原-----
--1.切换oracle角色
su - oracle
--2.设置NLS_LANG环境变量，必须与数据源保持一致
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
--3.备份整个数据库
exp '用户名/"密码"@IP:1521/数据库名' file=/orcl_data/20200625/备份源.dmp  log=/orcl_data/20200625/备份日志.log 
--4.还原
imp '用户名/"密码"' file=/orcl_data/20200625/备份源.dmp log=/orcl_data/20200625/还原日志.log full=y

------创建管理员用户并赋权------
sqlplus / as sysdba;	--超级管理员sys登陆
CREATE user admin identified by chinaweal2020 	--创建用户admin，密码chinaweal2020
alter user admin account unlock		--解锁
grant create session to admin		--创建session的权限，即登陆权限，允许用户登录数据库
--新建用户授予resource权限，新建用户可以创建表；
--新建用户授予connect权限，新建用户可以登录数据库；
--给用户授予dba权限，用户可以查看系统表；
GRANT resource,connect,dba to admin	
--如果只想给用户授予操作表数据、不操作表结构的权限
GRANT connect to admin	
--查询赋权，列出‘给新用户ADMIN赋予现有数据库AICORG的所有数据表的查插改删权限’的相关sql语句
select 'grant SELECT , INSERT, UPDATE, DELETE on '||OWNER||'.'||TABLE_NAME||' to ADMIN;' 
from all_tables where OWNER='AICORG'  
--执行查询出来的全部sql语句

-----创建超级管理员并赋予与sys角色相同权限-----
--创建超级管理员
create user SysAdmin
  identified by "123456"
  default tablespace SYSTEM
  temporary tablespace TEMP
  profile DEFAULT;
--使用SYS角色给新的超级管理员赋权
grant execute on SYS.DBMS_ALERT to SysAdmin;
grant execute on SYS.DBMS_AQ to SysAdmin with grant option;
grant execute on SYS.DBMS_AQADM to SysAdmin with grant option;
grant execute on SYS.DBMS_AQELM to SysAdmin with grant option;
grant execute on SYS.DBMS_AQ_IMPORT_INTERNAL to SysAdmin with grant option;
grant execute on SYS.DBMS_DEFER_IMPORT_INTERNAL to SysAdmin;
grant execute on SYS.DBMS_REPCAT to SysAdmin;
grant execute on SYS.DBMS_RULE_EXIMP to SysAdmin with grant option;
grant execute on SYS.DBMS_SQL to SysAdmin;
grant execute on SYS.DBMS_SYS_ERROR to SysAdmin;
grant execute on SYS.DBMS_SYS_SQL to SysAdmin;
grant execute on SYS.DBMS_TRANSFORM_EXIMP to SysAdmin with grant option;
grant select, insert, update, delete, references, alter, index on SYS.INCEXP to SysAdmin;
grant select, insert, update, delete, references, alter, index on SYS.INCFIL to SysAdmin;
grant select, insert, update, delete, references, alter, index on SYS.INCVID to SysAdmin;
grant select on WMSYS.WM$UDTRIG_INFO to SysAdmin;
grant aq_administrator_role to SysAdmin with admin option;
grant dba to SysAdmin with admin option;
grant unlimited tablespace to SysAdmin with admin option;

---

SyBase

--删除表索引
ALTER TABLE 表名 DROP CONSTRAINT 索引名

--给表增加字段
ALTER table 表名 add col1 int null , col2 int null , col3 varchar(10) null

--修改表字段长度(长度大小只能增大，不能减少)
ALTER table 表名 modify column1 varchar(1000) null

--查询今年内数据
convert(char,datepart(yy,时间字段)) = (select convert(char,datepart(yy,getdate())) )

--查看数据库设备
sp_helpdevice	

--删除设备
sp_dropdevice 设备名

--查看表结构、视图结构
sp_help 表名（视图名）

--查看数据库大小
use 数据库名
sp_spaceused

--查看数据库大小及设备信息
sp_helpdb 数据库名    

--查看用户自定义的表
select name from sysobjects where type='U'

--删除数据库
drop database dbname

--数据库的创建，必须依赖存储设备、日志设备
--1.创建数据存储设备
disk init
   name="DEV_DB_CLIENT_DAT01",
   physname="E:\sybase\DSFY100\data\DEV_DB_CLIENT_DAT01.dat",
   size="128M"
go
exec sp_deviceattr DEV_DB_CLIENT_DAT01, dsync, false
go
--2.创建数据日志设备
disk init 
   name="DEV_DB_CLIENT_LOG01",
   physname="E:\sybase\DSFY100\data\DEV_DB_CLIENT_LOG01.dat",
   size="32M"
go
exec sp_deviceattr DEV_DB_CLIENT_LOG01, dsync, false
go
--3.创建数据库
create database 数据名
   on DEV_DB_CLIENT_DAT01=128
   log on DEV_DB_CLIENT_LOG01=32
go

-----备份与还原（全库）-----
--1.将所有脏页写入到数据库设备
checkpoint
go
--2.将内存中存储的统计信息刷新到systabstats系统表
sp_flushstats
go
--3.开始备份
dump database 数据库名 to 'compress::F:\backup\CW\数据源.dmp'
go
--4.开始还原
load database 数据库名 from 'compress::F:\backup\CW\数据源.dmp'
go
--5.还原后，必须激活数据库
online database 数据库名
go

-----备份与还原（单表）-----
--进入sybase安装目录的bin目录
cd D:\Sybase\ASE-15_0\bin
--导出表数据
bcp dbname..tablename out d:\bcp\tablename.txt -Usa -P密码 -c -b 10000
--导入表数据
bcp dbname..tablename in d:\bcp\tablename.txt -Usa -P密码 -c -b 10000
--注：在导入大量数据时加上-b参数，分批提交不以致于数据库日志被塞满

---

MySql

--备份数据库
--进入mysql/bin目录
./mysqldump -h 主机IP -u root -B -p zb-blog>/bak/zb-blog.bak

--还原数据库
--进入数据库
在mysql/bin目录，执行./mysql -u root -p

use 数据库名；
source /usr/local/mysql_bak/zb-blog.bak

--停止mysql服务
在mysql目录下，service mysql stop

--启动mysql服务
在mysql目录下，service mysql start

---

不定时更新，同时欢迎大家下方留言更新