SQL常用指令_sql指令-CSDN博客

本文链接：https://blog.csdn.net/typersever/article/details/50802298

SQL常用指令

SQL和PL/SQL常用指令

数据库操作包括DML、DDL、DCL
数据库模式定义语言DDL(Data Definition Language)
数据操纵语言DML（Data Manipulation Language）
数据控制语言（Data Control Language）

·常用操作

select …… from dual;
dual为伪表
例如： select sysdate from dual;

SYSDATE
-----------
2016/3/4 11

desc查看表信息
desc EMP；

Name          Type         Nullable Default Comments 
------------- ------------ -------- ------- -------- 
ID            NUMBER(10)   Y                         
NAME          VARCHAR2(20) Y                         
DEPARTMENT_ID NUMBER(10)   Y

·DDL

常用的语法
创建数据表 create table table_name
修改数据表 alter table table_name
删除数据表 drop table table_name

创建

创建新表

create table EMP
(   
    id number(10),
    name varchar2(20),
    salary number(20),
    department_id number(10),
    job_id number(10)
    ...
);

使用子查询创建新表

create table EMP
as
select id, name, salary, department_id
from employees
where department_id = 80;

修改

修改表，增加字段

alter table EMP
add (
        department_id number(10);
        ...
    );

修改表，修改字段，类型，类型大小

alter table EMP
modify department_id number(20);

修改表，删除字段

alter table EMP
drop column department_id;

改表名

alter table EMP
rename to employees;

删除

删除表

drop table EMP;

清楚表数据

truncate EMP;

约束

创建约束

create table EMP
(   
    id number(10),
    name varchar2(20),
    salary number(20),
    department_id number(10),
    job_id number(10)
    constraint emp_id_pk primary key(id)
);

not null约束（只能定义在列级约束）

create table EMP
(   
    id number(10),
    name varchar2(20) not null,
    salary number(20) constraint emp_sal_nn not null,
    department_id number(10),
    job_id number(10)
);

Unique唯一约束

create table EMP
(   
    id number(10),
    name varchar2(20) unique,
    email varchar2(20),
    salary number(20),
    department_id number(10),
    job_id number(10)
    constraint emp_id_uk unique(email)
);

foreign key外键约束

create table EMP
(   
    id number(10),
    name varchar2(20),
    salary number(20),
    department_id number(10),
    job_id number(10),
    constraint emp_id_pk primary key(id),
    constraint emp_department_id_fk   foreign key(department_id)
        references departments(department_id)
);

check约束

create table EMP
(   
    id number(10),
    name varchar2(20),
    salary number(20)
        constraint emp_sal_ck check(salary > 0),
    department_id number(10),
    job_id number(10)
);

-添加或删除约束,但是不能修改约束
-有效化或无效化约束
-添加 NOT NULL 约束要使用 MODIFY 语句
-无效化约束使用disable constraint 约束名，相反使用enable

·DML

查询数据

查询全部列

 select * from EMP

查询多列

select id, name from EMP

查询使用别名可以省略as

select id as employee_id from EMP

查询合并列（注意使用双引号表示分清大小写）

select id || name as "employee" from EMP

删除重复列的查询

select distinct name from EMP

带条件的查询使用where

select id, name, salary, department_id 
from EMP

where department_id = 80;
范围条件的查询

select id, name, salary
from EMP
where salary between 2500 and 3000;

指定值条件查询

select id, name, salary 
from EMP
where name in('Tom', 'Jim');

模糊查询（%代表多个字符，_代表一个字符，escape’\’ 可以转义”_”, “\%”）

select id, name, salary
from EMP
where name Like '_T%';

查询（非）空值

select id, name, salary
from EMP
where salary is (not) null and department_id =80;

查询使用Order by排序，使用asc 和 desc 升降序

select id, name, salary
from EMP
where department_id = 80
Order by salary (asc)desc, employee_id;

增加数据

增加一条数据(给出字段需要按照顺序，给出相应的value值)
其中，主键若为自增长value为null

insert into EMP(id, name, salary )
values(0, 'Tom', 1000);

使用子查询的方式增加数据，把employees表对应字段值插入EMP表

insert into EMP(id, name, salary)
select id, name, salary from employees;

创建脚本的方式插入数据

insert into EMP(id, name, salary)
values(&id, '&name', &salary)

更新数据

更新数据（只用where指定更新范围）

update EMP
set salary  = 3000
where department_id = 80;

使用子查询方法更新数据

update EMP
set salary = (select salary from employees where id = 3)
where department_id = (select department_id
                       from employees where id = 3);

删除

删除记录，where规定范围

delete from EMP
where id = 5;

事务处理

-通常DDL和DCL语句是自动提交事务的（create，alter，drop）
-使用COMMIT 和 ROLLBACK语句,我们可以:
确保数据完整性。
数据改变被提交之前预览。
将逻辑上相关的操作分组。

-改变前的数据状态是可以恢复的
执行 DML 操作的用户可以通过 SELECT 语句查询之前的修正
其他用户不能看到当前用户所做的改变，直到当前用户结束事务。
DML语句所涉及到的行被锁定，其他用户不能操作。

使用savapoint创建保存点

update ....
savapoint update_done;
insert ....
rollback to update_done;

使用rollback，回滚到删除之前，数据得到恢复

delete from EMP where id = 5;
rollback;

使用commit，数据被删除并提交，不可恢复

delete from EMP where id = 5;
commit;

多表查询

多表查询基本语法，where子句写入连接条件

select e1.id, e1.name, e2.salary, e2.department_id 
from EMP1 e1, EMP2 e2
where e1.id = e2.id;

外连接，除了满足where条件的数据外，只要左（右）外连接，就会把左（右）不满足条件的数据也加进来，右（左）的列填入null值

右外连接
select e1.id, e1.name, e2.salary, e2.department_id 
from EMP1 e1, EMP2 e2
where e1.id(+) = e2.id;

左外连接
select e1.id, e1.name, e2.salary, e2.department_id 
from EMP1 e1, EMP2 e2
where e1.id = e2.id(+);

自连接（所用的都是同一个表的数据）

select e.id, e.name, e0.salary, e0.department_id 
from EMP e, EMP e0
where e.id = e0.id;

自然连接
-NATURAL JOIN 子句，会以两个表中具有相同名字的列为条件创建等值连接。
-在表中查询满足等值条件的数据。
-如果只是列名相同而数据类型不同，则会产生错误。
Job 表（job_id, city）

select id, name, salary, department_id, job_id, city 
from EMP
natural join Job;

使用using子句指定连接中需要用到的列，（没有用natural）
select id, name, salary, department_id, job_id, city 
from EMP
join Job using(job_id);

使用on子句多表连接

select e1.id, e1.name, e2.salary, e2.department_id, j.job_id, j.city 
from EMP1 e1
join EMP2 e2
on e1.id = e2.id
join Job j
on e2.job_id = j.job_id;

左外连接，右外连接，满连接

左外连接
select e1.id, e1.name, e2.salary, e2.department_id 
from EMP1 e1
left outer join EMP2 e2
on (e1.id = e2.id);

右外连接
select e1.id, e1.name, e2.salary, e2.department_id 
from EMP1 e1
right outer join EMP2 e2
on (e1.id = e2.id);

满连接
select e1.id, e1.name, e2.salary, e2.department_id 
from EMP1 e1
full outer join EMP2 e2
on (e1.id = e2.id);

分组查询

分组函数

avg（salary）–可以使用distinct不计算重复值
count(id) –可以使用distinct不计算重复值 count(*)计算总记录
max(salary)
min(salary)
sum(salary) –可以使用distinct不计算重复值

使用Group by(未包含在分组函数中的列，都需要放倒group by子句中)

select department_id, avg(salary)
from EMP
where department_id < 80
group by department_id;
order by department_id;

使用having，过滤分组

select department_id, avg(salary)
from EMP
where department_id < 80
group by department_id
having avg(salary) > 2000;