SQL常用语句

创建库

CREATE DATABASE dometest; USE dometest;

创建表

CREATE TABLE dometest.t_user(    
    Id Int Not Null Auto_increment ,    
    name Varchar(512) ,     
    age Varchar(512) ,        
    Primary Key (id)     
);  

 

create database bookshop;   
use bookshop;   
  
CREATE TABLE bookshop.tb_book(    
   id INT NOT NULL AUTO_INCREMENT COMMENT 'id主键',    
   bookName VARCHAR(215) COMMENT '书名',    
   bookContent VARCHAR(1025) COMMENT '内容描述',    
   bookPrice DOUBLE(10,2) DEFAULT '0' COMMENT '价格',    
   bookAuthor VARCHAR(215) COMMENT '作者',    
   PRIMARY KEY (id)   
 ) COMMENT = '图书表';   

 

create table test
(
  id   number,
  name varchar2(100) default 'feifei'
);

comment on table TBL_ORDER_TIME is '测试表'; 
comment on column test.ID is '主键id';
comment on column test.NAME is '用户名';
 
alter table test add constraint id_pri primary key (ID);
alter table test add constraint name_uni unique (NAME);

 

create table tb_audit  (
   id           NUMBER NOT NULL,
   status	 VARCHAR2(255) NOT NULL,
   primary key (id)
);

 

删除表：

DROP TABLE test;

 

唯一约束：

CREATE TABLE TBL_BLACKLIST   
(   
 mobile varchar2(100) not null ,   
 mobileDesc  varchar2(128),   
 constraint mobile_uk unique(mobile)   
); 

CREATE TABLE TBL_ORDER_TIME   
(      
 ORDER_NO NUMBER(10),      
 PROM_ID NUMBER(8),      
 PROM_TYPE CHAR(1),   
 AMOUNT NUMBER(8,2) default 0   
);   

alter table tb_department add unique (inner_name);

ALTER TABLE `tb_user` ADD UNIQUE `u_user_account` (`account`);
 

 

 创建外键与删除

alter table tb_feifei drop constraint FK_feifei_id

alter table tb_feifei add constraint FK_feifei_id  foreign KEY(fei_id) REFERENCES tb_fei2(id)

 

中文注释：

comment on table TBL_ORDER_TIME is '工作表';   
comment on column TBL_ORDER_TIME.ORDER_NO is '订单号';   
comment on column TBL_ORDER_TIME.PROM_ID is '促销ID';   
comment on column TBL_ORDER_TIME.PROM_TYPE is '促销类型';   
comment on column TBL_ORDER_TIME.AMOUNT is '金额';  

修改字段：

ALTER TABLE tb_book   CHANGE id id INT(11) NOT NULL AUTO_INCREMENT COMMENT 'id值';

修改字段长度：

alter table tbl_invoice_log  modify ( log_content varchar2(200));  

增加字段：

ALTER table tb_order add payCash varchar2(5);  

ALTER TABLE shell_command ADD c_type INT(1) DEFAULT 0 COMMENT '1为手动执行，2为程序功能辅助';  

 删除字段：

alter table tbl_refund drop column lostState  

 删除重复记录：

delete from tbl_member_report a   
where (a.memberNo,a.opendate) in      
     (select memberNo,opendate from tbl_member_report group by memberNo,opendate having count(*) > 1)   
and    
     rowid not in  (select min(rowid) from tbl_member_report group by memberNo,opendate having count(*)>1) 

修改字段名称：

alter table tbl_feifei rename column no to no_bak;

Oracle分页：

select * from  
(select e.*,ROWNUM rn from (select * from emp order by empno) e)   
where rn between 1 and 10;   
  
-- ROWNUM 不能写 > 号 
select * from  
(select e.*,ROWNUM rn from (select * from emp order by empno) e where ROWNUM <= 10)   
where rn>=1;

 mysql分页：

select * from tb_user limit 0, 10;

从0开始，1到10条记录

 

 oracle 主键自增长：
 

create table simon_example   
(   
  id number(4) not null primary key,   
  name varchar2(25)   
)   
  
-- 建立序列：    
create sequence SIMON_SEQUENCE                          
	minvalue 1                  
	maxvalue 999999999999999999999999999     
	start with 1    
	increment by 1   
	cache 20;    
  
-- 建立触发器   
create trigger "simon_trigger" before   
	insert on simon_example for each row when(new.id is null)   
begin  
	select simon_sequence.nextval into:new.id from dual;   
end; 

 

CREATE SEQUENCE emp_sequence
　　INCREMENT BY 1 -- 每次加几个
　　START WITH 1 -- 从1开始计数
　　NOMAXVALUE -- 不设置最大值
　　NOCYCLE -- 一直累加，不循环
　　NOCACHE -- 不建缓冲区

Oracle批量更新update - select

UPDATE tb_department 
SET department_name = 
(SELECT dd.department_name FROM tb_20140211_department dd WHERE tb_department.id  = dd.id)
WHERE EXISTS (SELECT dd.department_name FROM tb_20140211_department dd WHERE tb_department.id = dd.id )


-- where exists

备份表数据：

INSERT INTO tb_order_bak(orderId,userId)  SELECT id orderId , userId  FROM tb_order;    
 
SELECT * INTO tb_order_bak FROM  tb_order;   
  
CREATE TABLE  tb_order_bak (SELECT * FROM tb_order);   

CREATE TABLE oa_user_role_20130130 AS SELECT * FROM oa_user_role; 

insert into tb_audit select * from tb_aduit_temp;

 

 创建索引：

CREATE INDEX index_tb_user_deptId ON tb_user (deptId);   
CREATE INDEX index_tb_project_projectId ON tb_project (projectId);  

 

 IN 和 Exists：

select * from oa_user u where exists (   
	select r.user_id from oa_user_role r where r.role_id = 73 and u.id = r.user_id group by r.user_id 
)   
  
select * from oa_user u where u.id in  (   
	select r.user_id from oa_user_role r where r.role_id = 73  group by r.user_id 
)  

生成UUID

SELECT UUID();
select sys_guid() from dual;

marge---oracle语句：

MERGE INTO products p   
 USING newproducts np ON (p.product_id = np.product_id)   
   WHEN MATCHED THEN    --符合更新   
     UPDATE SET p.product_name = np.product_name,   
            p.category = np.category   
        DELETE WHERE (p.category = 'ELECTRNCS')   
   WHEN NOT MATCHED THEN        --不符合，插入   
     INSERT VALUES (np.product_id, np.product_name, np.category)  

查看oracle的 SCHEMA

select SYS_CONTEXT('USERENV','CURRENT_SCHEMA') CURRENT_SCHEMA from dual;