项目中常用的SQL语句

拼搏现实的明天。 2022-05-19 06:39 293阅读 0赞

**SQL项目常用整理**

**1、创建表**  
语法：CREATE tabal 表名（字段+约束，字段+约束）;  
举例：

create table test01(
    
             id int not null,
    
             name varchar(8) not null,
    
             gender varchar2(2) not null,
    
             age int not null,
    
             address varchar2(20) default ‘地址不详’ not null,
    
             regdata date
    
            );

**2、删除表(尽量不使用)**  
truncate table 表名 --删除表中的所有数据，速度比delete快很多,截断表

delete from table 条件

drop table 表名 --删除表  
  
Oracle truncate、 delete、 drop区别  
相同点：

(1).truncate和不带where子句的delete、以及drop都会删除表内的数据。

(2).drop、truncate都是DDL语句(数据定义语言),执行后会自动提交。

不同点：

(1). truncate 和 delete 只删除数据不删除表的结构(定义)  
drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index)；依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。

(2). delete 语句是数据库操作语言(dml)，这个操作会放到 rollback segement 中，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。  
truncate、drop 是数据库定义语言(ddl)，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。

(3).delete 语句不影响表所占用的 extent，高水线(high watermark)保持原位置不动  
drop 语句将表所占用的空间全部释放。  
truncate 语句缺省情况下见空间释放到 minextents个 extent，除非使用reuse storage；truncate 会将高水线复位(回到最开始)。

（4）.速度，一般来说: drop> truncate > delete

（5）.安全性：小心使用 drop 和 truncate，尤其没有备份的时候.否则哭都来不及  
使用上,想删除部分数据行用 delete，注意带上where子句. 回滚段要足够大.  
想删除表,当然用 drop  
想保留表而将所有数据删除，如果和事务无关，用truncate即可。如果和事务有关,或者想触发trigger,还是用delete。  
如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。

（6）.delete是DML语句,不会自动提交。drop/truncate都是DDL语句,执行后会自动提交。

（7）、TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。  
但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。  
DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。  
TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

（8）、TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。  
新行标识所用的计数值重置为该列的种子。  
如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。  
  
（9）、对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。  
由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

（10）、TRUNCATE TABLE 不能用于参与了索引视图的表。  
  
**3、添加主键**  
创建主外键（可以在创建表的同时添加主外键约束，也可以创建完成后再添加约束 ）  
方法一：表创建的同时，添加主键约束  
语法：  
create table 表名  
(  
列名1 数据类型及长度 constraint 主键名称(一般主键名称为”PK\_”开头) primary key,  
列名2 数据类型及长度 not null,——-not null 约束该列不为空，不写表示可以为空  
列名3 数据类型及长度  
)tablespace 表空间名称;——-指定将该表放在某个表空间里，可以省略不指定  
举例：

create table T_DEPOSIT 
             (savingid VARCHAR2(20) constraint pk_savingid primary key, 
              savingname VARCHAR2(20) not null, 
              descrip VARCHAR2(50) 
              )tablespace ATM_tablespace;

————注意————-  
CREATE TABLE T\_cardInfo –银行卡信息表  
(  
cardID varchar2(19) primary key,  
如果此处这样定义主键，则主键名称系统自己定义设置  
);  
方法二：表创建后，添加主键约束  
语法：  
alter table 表名  
add constraint 主键名称(一般主键名称为”PK\_”开头) primary key(要设为主键的列名);  
举例：

alter table T_Grade 
        add constraint pk_gradeId primary key (gradeId);

需要修改结构的，就用到alter语句，方法如下：  
  
ALTER TABLE语句用于修改已经存在的表的设计。  
**4、删除主键**  
（1）有命名  
alter table students drop constraint yy;  
（2）无命名  
可用 SELECT \* from user\_cons\_columns;  
查找表中主键名称得student表中的主键名为SYS\_C002715  
alter table student drop constraint SYS\_C002715;  
**5、创建一般(normal)索引**  
语法：  
CREATE \[UNIQUE\] INDEX index\_name ON table\_name(column\_name\[,column\_name…\]);  
**6、创建唯一(unique)索引**  
语法解析：  
（1）. UNIQUE:指定索引列上的值必须是唯一的。称为唯一索引。

（2）. index\_name：指定索引名。

（3）. tabl\_name：指定要为哪个表创建索引。

（4）. column\_name：指定要对哪个列创建索引。我们也可以对多列创建索引;这种索引称为组合索引。  
举例说明：  
案例：为EMP表的ENAME列创建创建唯一索引，为EMP表的工资列创建普通索引，把JOB列先变为小写再创建索引。

CREATE UNIQUE INDEX UQ_ENAME_IDX ON EMP(ENAME);       --唯一索引
    CREATE INDEX IDX_SAL ON EMP(SAL);                     --一般索引
    CREATE INDEX IDX_JOB_LOWER ON EMP(LOWER(JOB));        --基于函数的索引

代码解析：

① 为SCOTT.EMP表的ENAME列创建唯一索引。

② 为SCOTT.EMP表的SAL列创建一般索引。

③ 在查询中可能经常使用job的小写作为条件的表达式，因此创建索引时，  
可以先对JOB列中的所有值转换为小写后创建索引，  
而这时需要使用lower函数，这种索引称为基于函数的索引。  
  
**7、删除索引**  
当不需要时可以将索引删除以释放出硬盘空间。  
语法：  
DROP INDEX \[schema.\]indexname  
例如：  
SQL> drop index pk\_dept;  
注：当表结构被删除时，有其相关的所有索引也随之被删除。  
  
**8、表名注释**  
给表加注释语法：  
comment on table 表名 is '注释内容';  
举例：

comment on table OPERATOR_INFO is  '操作员信息表';

**9、字段注释**  
字段加注释语法：  
comment on column 表名.字段名 is '注释内容';  
举例：

comment on column OPERATOR_INFO.MAIN_OPER_ID is '归属操作员';

**10、插入数据**  
方法1  
我估计有点 SQL 基础的人都会写 INSERT 语句。下面是 SQL 标准写法。  
INSERT INTO employees (employee\_id, name) VALUES (1, 'Zhangsan');  
INSERT INTO employees VALUES (1, 'Shangbo');  
语法：  
insert into 表名（字段名，字段名...） values (值1，值2);

方法2  
其实， Oracle 还支持下面的写法，作用和上面的语句完全相同。  
INSERT INTO (SELECT employee\_id, name FROM employees) VALUES (2, 'Lisi');

方法3  
此外，同其他数据库一样，Oracle 也支持下面这种写法。  
INSERT INTO employees  
SELECT 3, 'Wangwu' FROM DUAL;  
方法4  
下面这种写法可以实现列转行，如我们有下面的表存储原始数据，原始数据可能从文件中来。

create table sales_input_table (
                   prod_id        number(9,0),
                   amt_mon        number(9,6),
                   amt_tue        number(9,6),
                   amt_wed        number(9,6),
                   amt_thu        number(9,6),
                   amt_fri        number(9,6)
                  );
            insert into sales_input_table values (1, 100.0, 200.0, 300.0, 400.0, 500.0);

下面我们通过一个 SQL 把上面的数据插入到下面的表中实现列转行。

CREATE TABLE sales (
                  prod_id        number(9,0),
                  time_id        date,
                  amount         number(9,0)
                 );
     
           INSERT ALL
           INTO sales (prod_id, time_id, amount) VALUES (prod_id, CURRENT_DATE, amt_mon)
           INTO sales (prod_id, time_id, amount) VALUES (prod_id, CURRENT_DATE + 1, amt_tue)
           INTO sales (prod_id, time_id, amount) VALUES (prod_id, CURRENT_DATE + 2, amt_wed)
           INTO sales (prod_id, time_id, amount) VALUES (prod_id, CURRENT_DATE + 3, amt_thu)
           INTO sales (prod_id, time_id, amount) VALUES (prod_id, CURRENT_DATE + 4, amt_fri)
           SELECT prod_id, amt_mon, amt_tue, amt_wed, amt_thu, amt_fri FROM sales_input_table;

方法5  
下面这种写法可以帮我们一次性把一个表中的数据倒入到多个表中，否则我们必须写多条 SQL 实现同样的功能。

INSERT ALL
                 WHEN order_total <= 100000 THEN
                      INTO small_orders
                 WHEN order_total > 100000 AND order_total <= 200000 THEN
                      INTO medium_orders
                 WHEN order_total = 500000 THEN
                      INTO special_orders
                 WHEN order_total > 200000 THEN
                      INTO large_orders
                 ELSE
                      INTO large_orders
          SELECT order_id, order_total, sales_rep_id, customer_id FROM orders;

注意，当 order\_total 大于 200000 时，orders 会被插入到 large\_orders 和 special\_orders 中。  
这可能不是你想要的结果，如果你只想让 orders 插入到 special\_orders 表中，你只需要把 ALL 替换成 FIRST。

**11、更新数据**  
语法：  
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值;  
举例：  
update es\_gc\_prod set prod\_open\_type='1' where prod\_code='1012';  
  
**12、oracle实现 无则insert插入,有则update更新，一条语句直接进行insert/update操作**  
merge into应用：实现 无则insert插入,有则update更新，一条语句直接进行insert/update操作  
merge ino语法：  
MERGE INTO table1 alias1  
USING (table2|view2|sub\_query2) alias2  
ON (join condition)  
WHEN MATCHED THEN  
UPDATE table1  
SET col1 = col\_val1,  
col2 = col2\_val  
WHEN NOT MATCHED THEN  
INSERT (column\_list) VALUES (column\_values);  
举例：

MERGE INTO hand_student_core_20500 t
    USING hand_student_core hsc
    ON (t.student_no = hsc.student_no AND t.course_no = hsc.course_no)
    WHEN MATCHED THEN
      UPDATE SET t.core = hsc.core
    WHEN NOT MATCHED THEN
      INSERT VALUES (hsc.student_no, hsc.course_no, hsc.core);

代码分析：  
将"hand\_student\_core\_20500"表和"hand\_student\_core"表，以"student\_no"和"course\_no"字段进行比对，如果"hand\_student\_core\_工号"中  
存在和"hand\_student\_core"表相同的"student\_no"和"course\_no"记录，则把"hand\_student\_core\_工号"的"core"字段更新成和"hand\_student\_core"表一致，  
否则，将"hand\_student\_core"表的记录插入到"hand\_student\_core\_工号"表。  
**13、删除数据**  
语法：  
delete from 数据库.表名 where id = '1'  
讲解：如果不用跨库，可以省略“数据库”。1代表唯一的一条记录，是参数。  
**14、增加字段**  
语法：  
添加字段的语法：alter table tablename add (column datatype \[default value\]\[null/not null\],….);  
举例：  
创建表结构：  
create table test1  
(id varchar2(20) not null);  
增加一个字段：

alter table test1  
add (name varchar2(30) default ‘无名氏’ not null);  
  
**15、修改字段（修改字段的名称，修改字段的类型）**  
语句：  
alter table tableName rename column oldCName to newCName; -- 修改字段名  
alter table tableName modify (cloumnName 数据类型); -- 修改数据类型  
例如：  
1、创建表：

CREATE TABLE Student(
    　　　　   id varchar2(32) primary key,
    　　　　   name varchar2(8) not null,
    　　　　   age number
    　　 );

2、修改字段名：

alter table Student rename name to StuName;

3、修改数据类型：

alter table Student modify (id varchar2(64));

**16、删除字段**  
语法：

alter table tablename drop (column);