Sql

前言

一些通用的sql语法，主要是Oracle数据库

一、Create

1. 建表：create table 表名( 列名称1 数据类型 主键 约束,…)
2. 创建用户(使用sys登录)：create user 用户名 identified by 密码;
3. 授权角色（连接、资源）：grant connect,resource to 用户名;grant create synonym to scott;–同义词【查看common包里的】
4. 一个用户给另一个用户授权：select ‘grant select, insert, update, delete on ‘||t.tname||’ to clueshunter_fjxm;’ from tab t where t.tabtype = ‘TABLE’;

---

二、Alter

1. 增加表字段

   --Alter Table 表名 add 字段名 数据类型 约束/默认值/非空性/
   ALTER TABLE T_YQ_MAJOR_INFO_SIGN ADD thyj VARCHAR2 (200); 
   -- 列名称
   comment on column T_YQ_MAJOR_INFO_SIGN.thyj
     	is '退回意见';
   --【注】：若非空，则默认值必须在前面

2. 删除表字段

   --Alter Table table_name Drop Column columnName;
   Alter Table t_diary Drop column time;

3. 修改表名

   --Alter Table 旧表名 Rename to 新表名;
   Alter Table t_diary Rename to t_diary_info

4. 修改表字段大小

   --Alter Table 表名 Modify 字段名 数据类型;
   Alter table T_YQ_CLUE_SIGN modify sfsc varchar(1);

5. 更新数据

   --UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
   Update T_MD_CODE_DIC set code_id='BM_YQBS_STATUS' where code_id='BM_YQ_STATUS';
   
   --如果是在插入的引号中又包含引号，则里面的需要为双引号
   Update t_md_code_dic t set t.entity_name = '(SELECT bh,mc from  BM_NBYJ t where  t.sjlb = ''XXLX'')',t.order_field = ''  where CODE_ID = 'BM_YQBS_XXLB';

6. 修改字段类型

   -- varchar2 转化为clob ，需要先删后建
   alter table T_YQ_MAJOR_INFO_SIGN add jsdw_a varchar2(4000);
   comment on column T_YQ_MAJOR_INFO_SIGN.jsdw_a is '接收单位a';
   update T_YQ_MAJOR_INFO_SIGN set jsdw_a = jsdw;
   alter table T_YQ_MAJOR_INFO_SIGN drop column jsdw;
   alter table T_YQ_MAJOR_INFO_SIGN add jsdw clob;
   comment on column T_YQ_MAJOR_INFO_SIGN.jsdw is '接收单位';
   update T_YQ_MAJOR_INFO_SIGN set jsdw = jsdw_a;
   alter table T_YQ_MAJOR_INFO_SIGN drop column jsdw_a;

---

三、Delete

1. 删除表记录

   Delete From 表名 Where 列名称 = 值

---

四、Select

1. 左连接

   • left join on .. and .. : 以左表为基，保留所有左边查询数据，查不到为空

   • left join on .. where .. : 先匹配，后筛选，不符合条件的会被去除

2. 查找（最近）表的修改记录

   SELECT *  FROM  tableName AS OF TIMESTAMP(SYSDATE-30/24/60)

3. 查找一张表中是否存在重复数据

   SELECT COUNT(NAME) as 出现次数,  NAME FROM  表名
   	GROUP BY  NAME   HAVING count(NAME) >= 2   ORDER BY  出现次数   DESC

4. 关联查询：内连接（等值、不等值）、外连接（左、右、全）、交叉连接

   参考链接：点击传送

5. 全模糊查询：当一个字段中有多值时，可使用造分割符的方式去查

   • rylbid里是多个值，并以“，”拼接，则 查询时 select … where “,” || rylbid || “,” like “,001002,” ;

---

五、其他

1. With As：相当于建立一个虚拟视图，把一大堆重复用到的sql语句放在with as里面，取一个别名，后面的查询就可以用它，这样对于大批量的sql语句起到一个优化的作用，而且清楚明了。

   with e as (select * from scott.emp e where e.empno=7499)
   	select * from e;

2. 根据现有表创建新表

   • 复制结构和数据： create table table_new as select * from table_old;
   • 只复制结构：create table table_new as select * from table_old where 1=0;

---

六、sql语句优化

1. 全模糊查询：使用instr(strSource,strTarget)代替like

   instr函数也有三种情况： 
   instr(字段,’关键字’)>0相当于 字段like ‘%关键字%’ 
   instr(字段,’关键字’)=1相当于 字段like ‘关键字%’ 
   instr(字段,’关键字’)=0相当于 字段not like ‘%关键字%’ 
   -- 例子
   SELECT * FROM [user] WHERE instr(uname ,’三’)>0

​