ORACLE之SQL语句
1 建表语句
create table t_user(
name varchar2(10),
age number(3)
);
create table t_boss(
name varchar2(10),
age number(2)
);
create table t_employee(
name varchar2(10),
age number(3),
salary number(4,2)
);
2 插入语句
2.1 插入单行
insert into t_employee(name,age,salary) values('daniel',15,10.00);
2.2 插入多行
insert all
into t_employee values('ZhangSan',18,20.00)
into t_employee values('LiSi',20,30.00)
select * from dual;
2.3 从其他表中插入多行
insert all
into t_user values(name,age)
into t_boss values(name,age)
select name,age from t_employee
2.4 带条件的从其他表中插入多行多表
insert all[first]
when age>16 then into t_user values(name,age)
when age>18 then into t_boss values(name,age)
select name,age from t_employee
ps:
first 第一个条件成立时,后面的语句不执行;
all 只要条件成立,语句就执行
3 修改语句
3.1 增加列
alter table t_user add sex varchar2(1);
3.2 修改列属性(一般只修改列的长度)
alter table t_user modify name varchar2(30);
3.3 修改列的名称
alter table t_user rename column sex to address;
3.4 修改表的名字
alter table t_user rename to t_user2;
3.5 删除列
alter table t_user drop column address;
4 删除语句
delete from t_user;
5 查询语句
select * from t_user;
ps:
查询语句的执行顺序:
from(数据多的表放前面)
on
join
where
group by
with
having(耗资源)
select
distinct
order by(耗时)
top