1、建表语句
Create table abc(
a number(10),
b char(10),
c varchar2(10));
2、修改表字段名称
Alter table abc rename column b to d;
3、修改表字段属性
Alter table abc modify a primary key;
4、增加表字段
Alter table abc add e number(10) not null;
5、删除表,包括删除表结构
drop table abc;
6、删除表,只删除数据(注:删除表中所有的数据 留下空表格)
truncate table abc;
7、表插入数据
insert into abc (a,b,c) values (1,2,3);
insert into a1 (a,b,c) (select a,b,c from a2 where a='11');
8、表更新数据
update abc set a=2 where b=2;
9、表删除数据
delete from abc where b=2;
10、设置字段属性的关键字
not null 设置值不为空
unique 设置值唯一
primary key 主键
foreign key 外键
11、select语句运用
select * from abc;
select a,b,c from abc;
select DISTINCT(a),b,c from abc; 查询"a"字段不重复的记录
select * from abc where a in (1,2,3); 查询"a"字段分别为1、2、3的所有表记录
select * from abc where a in (select b from test); 查询"a"字段的值属于test表的"b"字段的值的所有表记录
select * from abc where a in (1,2,3) order by a asc; 查询"a"字段分别为1、2、3的所有表记录按升序排列(asc升序、desc降序,默认不写为升序)
select count(*) from abc; 计算一共有多少条记录(AVG平均、MAX最大值、MIN最小值、SUM求和)
select sum(a),b,c from abc group by b,c HAVING sum(a)>2; 查询表abc的sum(a)、b、c3个字段且sum(a)的值大于2按照分组b、c字段排序(注意:select后的字段必须
和group by + HAVING的字段一致)
select sum(a) from abc HAVING sum(a)>2;
select t.a,b abc,c from abc t; SQL查询设置表的别名为t ,字段b的别名为abc
select t.a,sum(b) abc,c from abc t group by t.a,c; SQL查询设置表的别名为t,sum(b)函数字段别名为abc(注意有函数的情况下必须要用group by 且select后的其他
字段必须要在group by后面出现)
select * from a1 t left join a2 t1 on t1.a=t.a; (左联接)利用左联接把a1表和a2表以t1.a=t.a进行关联
select t.a || 'abc' from abc t; 查询表abc给a字段的值后面加上abc(||具有拼接功能)
12、左联接和右联接的用法及区别
a1表 a2表
a b c a d e
1 2 3 1 1 1
2 2 3 2 2 2
3 2 3 3 3 3
5 5 5 4 4 4
(1)利用左联接(两种用法)
select * from a1 t1 left join a2 t2 on t1.a=t2.a;
select * from a1 t1,a2 t2 where t1.a=t2.a(+);
a b c a d e
1 2 3 1 1 1
2 2 3 2 2 2
3 3 3 3 3 3
5 5 5
(2)利用右联接(两种用法)
select * from a1 t1 right join a2 t2 on t1.a=t2.a;
select * from a1 t1,a2 t2 where t1.a(+)=t2.a;
a b c a d e
1 2 3 1 1 1
2 2 3 2 2 2
3 3 3 3 3 3
4 4 4
13、内联接和外联接用法及区别
(1)内联接用法
select * from a1 t1 inner join a2 t2 on t1.a=t2.a;
select * from a1 t1,a2 t2 where t1.a =t2.a;
a b c a d e
1 2 3 1 1 1
2 2 3 2 2 2
3 3 3 3 3 3
(2)外联接用法
左联接、右联接都属于外联接
14、创建表有联合主键
create table abc(
a number(10),
b number (11),
c char (10),
primary key (a,b)
);
15、常用的函数
(1)to char()转换函数的使用
转换字符->字符TO_CHAR(c):将其它类型的字符转换为char类型;
select TO_CHAR(t.a) from a1 t;
转换日期->字符TO_CHAR(d,fmt):将指定的日期按照指定格式转换为varchar2类型;
SELECT TO_CHAR(to_date('20090301125612','yyyymmddhhmiss'),'yyyy-mm-dd hh24:mi:ss') FROM DUAL;(结果为:2009-03-01 12:56:12)
转换数值->字符TO_CHAR(n[,fmt]):将指定数值n按照指定格式fmt转换为varchar2类型并返回;
SELECT TO_CHAR(-100, 'L99G999D99MI') FROM DUAL;(结果为:’RMB100.00-’)
SELECT TO_CHAR (456,'999.9') from dual;(结果为:’ 456.0’)
注:在转换数值->字符中fmt必须大于n,而且fmt中的数值必须为0,9
(2)to_date()转换函数的使用
TO_DATE(c,fmt) 将字符转换为日期类型。注意这里的fmt参数。如果ftm为'J'则表示按照公元制(Julian day)转换,此时c则必须为大于0并小于5373484的正整数
SELECT TO_DATE(2454336, 'J') FROM DUAL;(结果为:2007-8-23)
SELECT TO_DATE('2007-8-23 23:25:00', 'yyyy-mm-dd hh24:mi:ss') FROM DUAL;(结果为:2007-8-23 23:25:00)
(3)to_number()转换函数的使用
SELECT TO_NUMBER('-100.00', '9G999D99') FROM DUAL;(结果为:-100)
(4)Trim(c1)除去字符c1两边的空格
SELECT TRIM(' WhaT is tHis ') FROM DUAL;
SELECT TRIM(t.a) FROM abc t;
(5)lead(字段1,偏移量值)over(partition by 字段2 order by 字段3)
a1表
a b c
1 1 1
1 2 2
1 3 3
1 4 4
2 1 1
2 2 2
2 3 3
3 1 1
select t1.a as a,t1.b as b ,lead (t1.b,0)over(partition by t1.a order by t1.a) as b1 from a1 t1(取b字段不偏移)
a b b1
1 1 3
1 2 3
1 3 3
1 4 3
2 1 1
2 2 1
2 3 1
3 1 2
select t1.a as a,t1.b as b ,lead (t1.b,1)over(partition by t1.a order by t1.a) as b1 from a1 t1(取b字段向上偏移一位)
a b b1
1 1
1 2 1
1 3 2
1 4 3
2 1
2 2 1
2 3 2
3 1
(6)decode函数用法
select decode(a,b,yes,no) from 表 ; a=b 取yes,a<>b 取no
decode (a,0,0,b/a) 这个用法可以防止除数a为0的情况
(7)round函数用法
round(9999.99999,3) 值为9999,999保留3位小数
(8)NVL函数用法
NVL(a,b) a=null 则取值b a<>null 则取值a
(9)NVL2函数用法
NVL2(a,b,c) a=null 则取值b a<>null则取值c
16、创建存储过程语法
(1)存储过程基本语法
create OR REPLACE PROCEDURE存储过程名字 ( 参数1 IN NUMBER, 参数2 IN NUMBER ) IS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN
过程体;
END 存储过程名字
(2)变量赋值
变量名:=值
(3)for循环
create or replace procedure test
(
a in number,
b in number
)
is
value1 number:=0;
value2 number:=0;
BEGIN
过程体;
END test
Create table abc(
a number(10),
b char(10),
c varchar2(10));
2、修改表字段名称
Alter table abc rename column b to d;
3、修改表字段属性
Alter table abc modify a primary key;
4、增加表字段
Alter table abc add e number(10) not null;
5、删除表,包括删除表结构
drop table abc;
6、删除表,只删除数据(注:删除表中所有的数据 留下空表格)
truncate table abc;
7、表插入数据
insert into abc (a,b,c) values (1,2,3);
insert into a1 (a,b,c) (select a,b,c from a2 where a='11');
8、表更新数据
update abc set a=2 where b=2;
9、表删除数据
delete from abc where b=2;
10、设置字段属性的关键字
not null 设置值不为空
unique 设置值唯一
primary key 主键
foreign key 外键
11、select语句运用
select * from abc;
select a,b,c from abc;
select DISTINCT(a),b,c from abc; 查询"a"字段不重复的记录
select * from abc where a in (1,2,3); 查询"a"字段分别为1、2、3的所有表记录
select * from abc where a in (select b from test); 查询"a"字段的值属于test表的"b"字段的值的所有表记录
select * from abc where a in (1,2,3) order by a asc; 查询"a"字段分别为1、2、3的所有表记录按升序排列(asc升序、desc降序,默认不写为升序)
select count(*) from abc; 计算一共有多少条记录(AVG平均、MAX最大值、MIN最小值、SUM求和)
select sum(a),b,c from abc group by b,c HAVING sum(a)>2; 查询表abc的sum(a)、b、c3个字段且sum(a)的值大于2按照分组b、c字段排序(注意:select后的字段必须
和group by + HAVING的字段一致)
select sum(a) from abc HAVING sum(a)>2;
select t.a,b abc,c from abc t; SQL查询设置表的别名为t ,字段b的别名为abc
select t.a,sum(b) abc,c from abc t group by t.a,c; SQL查询设置表的别名为t,sum(b)函数字段别名为abc(注意有函数的情况下必须要用group by 且select后的其他
字段必须要在group by后面出现)
select * from a1 t left join a2 t1 on t1.a=t.a; (左联接)利用左联接把a1表和a2表以t1.a=t.a进行关联
select t.a || 'abc' from abc t; 查询表abc给a字段的值后面加上abc(||具有拼接功能)
12、左联接和右联接的用法及区别
a1表 a2表
a b c a d e
1 2 3 1 1 1
2 2 3 2 2 2
3 2 3 3 3 3
5 5 5 4 4 4
(1)利用左联接(两种用法)
select * from a1 t1 left join a2 t2 on t1.a=t2.a;
select * from a1 t1,a2 t2 where t1.a=t2.a(+);
a b c a d e
1 2 3 1 1 1
2 2 3 2 2 2
3 3 3 3 3 3
5 5 5
(2)利用右联接(两种用法)
select * from a1 t1 right join a2 t2 on t1.a=t2.a;
select * from a1 t1,a2 t2 where t1.a(+)=t2.a;
a b c a d e
1 2 3 1 1 1
2 2 3 2 2 2
3 3 3 3 3 3
4 4 4
13、内联接和外联接用法及区别
(1)内联接用法
select * from a1 t1 inner join a2 t2 on t1.a=t2.a;
select * from a1 t1,a2 t2 where t1.a =t2.a;
a b c a d e
1 2 3 1 1 1
2 2 3 2 2 2
3 3 3 3 3 3
(2)外联接用法
左联接、右联接都属于外联接
14、创建表有联合主键
create table abc(
a number(10),
b number (11),
c char (10),
primary key (a,b)
);
15、常用的函数
(1)to char()转换函数的使用
转换字符->字符TO_CHAR(c):将其它类型的字符转换为char类型;
select TO_CHAR(t.a) from a1 t;
转换日期->字符TO_CHAR(d,fmt):将指定的日期按照指定格式转换为varchar2类型;
SELECT TO_CHAR(to_date('20090301125612','yyyymmddhhmiss'),'yyyy-mm-dd hh24:mi:ss') FROM DUAL;(结果为:2009-03-01 12:56:12)
转换数值->字符TO_CHAR(n[,fmt]):将指定数值n按照指定格式fmt转换为varchar2类型并返回;
SELECT TO_CHAR(-100, 'L99G999D99MI') FROM DUAL;(结果为:’RMB100.00-’)
SELECT TO_CHAR (456,'999.9') from dual;(结果为:’ 456.0’)
注:在转换数值->字符中fmt必须大于n,而且fmt中的数值必须为0,9
(2)to_date()转换函数的使用
TO_DATE(c,fmt) 将字符转换为日期类型。注意这里的fmt参数。如果ftm为'J'则表示按照公元制(Julian day)转换,此时c则必须为大于0并小于5373484的正整数
SELECT TO_DATE(2454336, 'J') FROM DUAL;(结果为:2007-8-23)
SELECT TO_DATE('2007-8-23 23:25:00', 'yyyy-mm-dd hh24:mi:ss') FROM DUAL;(结果为:2007-8-23 23:25:00)
(3)to_number()转换函数的使用
SELECT TO_NUMBER('-100.00', '9G999D99') FROM DUAL;(结果为:-100)
(4)Trim(c1)除去字符c1两边的空格
SELECT TRIM(' WhaT is tHis ') FROM DUAL;
SELECT TRIM(t.a) FROM abc t;
(5)lead(字段1,偏移量值)over(partition by 字段2 order by 字段3)
a1表
a b c
1 1 1
1 2 2
1 3 3
1 4 4
2 1 1
2 2 2
2 3 3
3 1 1
select t1.a as a,t1.b as b ,lead (t1.b,0)over(partition by t1.a order by t1.a) as b1 from a1 t1(取b字段不偏移)
a b b1
1 1 3
1 2 3
1 3 3
1 4 3
2 1 1
2 2 1
2 3 1
3 1 2
select t1.a as a,t1.b as b ,lead (t1.b,1)over(partition by t1.a order by t1.a) as b1 from a1 t1(取b字段向上偏移一位)
a b b1
1 1
1 2 1
1 3 2
1 4 3
2 1
2 2 1
2 3 2
3 1
(6)decode函数用法
select decode(a,b,yes,no) from 表 ; a=b 取yes,a<>b 取no
decode (a,0,0,b/a) 这个用法可以防止除数a为0的情况
(7)round函数用法
round(9999.99999,3) 值为9999,999保留3位小数
(8)NVL函数用法
NVL(a,b) a=null 则取值b a<>null 则取值a
(9)NVL2函数用法
NVL2(a,b,c) a=null 则取值b a<>null则取值c
16、创建存储过程语法
(1)存储过程基本语法
create OR REPLACE PROCEDURE存储过程名字 ( 参数1 IN NUMBER, 参数2 IN NUMBER ) IS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN
过程体;
END 存储过程名字
(2)变量赋值
变量名:=值
(3)for循环
create or replace procedure test
(
a in number,
b in number
)
is
value1 number:=0;
value2 number:=0;
BEGIN
过程体;
END test