Oracle入门基本操作

最新推荐文章于 2025-06-24 09:53:08 发布

若倾_

最新推荐文章于 2025-06-24 09:53:08 发布

阅读量503

点赞数 4

CC 4.0 BY-SA版权

文章标签： oracle 数据库

本文链接：https://blog.youkuaiyun.com/starry_lit/article/details/145412089

--一，建表操作

--直接建表

create table shucaib(

scid varchar2(10) primary key,

scname varchar2(20),

scsm number(6),

scprice number(4,2)

);

--通过子查询的方式创建

CREATE TABLE aaa AS

SELECT scid, scname, scsm, scprice

FROM shucaib;

--创建表时定义复合主键

CREATE TABLE shucaib (

scid VARCHAR2(10),

scsm NUMBER(6),

scname VARCHAR2(20),

scprice NUMBER(4, 2),

PRIMARY KEY (scid, scsm)

);

--二，操作表结构

--1.插入表结构《 alter table 表名 add (字段名字段类型默认值是否为空); 》

--主键列的值必须是唯一的（不允许重复）。

--主键列的值不能为NULL

--可以通过组合多个字段来定义一个复合主键（Composite Primary Key）。

--在已经存在主键的表中，不能直接“插入”一个新的主键字段来构成复合主键。

--主键的定义是唯一的，一旦表被创建并指定了主键，就无法直接修改主键的定义来添加新的字段。

--删除现有的主键约束：重新定义复合主键：

--增加主键

alter table shucaib

add primary key(scid);

--定义复合主键

ALTER TABLE SHUCAIB

ADD CONSTRAINT PK_SHUCAIB PRIMARY KEY (scid, scsm);

--删除主键约束

--不会删除主键列本身，只会移除主键约束的定义。

--主键列仍然会保留在表中，只是不再具有主键的约束特性（即唯一性和非空性）。

--不需要在DROP PRIMARY KEY后面指定列名。Oracle会自动识别并删除表的主键约束。

alter table SHUCAIB

drop primary key ;

--表追加最后一列

--在添加单个列时，关键字后面可以不加括号

alter table shucaib

ADD (aname varchar2(10));

--添加一列默认值为空，约束不允许为空

ALTER TABLE shucaib

ADD (cname VARCHAR(50) DEFAULT '空' NOT NULL);

--增加多个列字段

alter table shucaib

add (dname varchar(50),ename varchar(10));

--2.修改表结构《 alter table 表名 modify (字段名字段类型默认值是否为空); 》

--修改表名称

alter table shucaib

rename to shucaib;--新名字

--修改列的数据类型

ALTER TABLE shucaib

MODIFY (aname VARCHAR2(20),cname VARCHAR2(20));

--修改列的长度

ALTER TABLE shucaib

MODIFY (scprice NUMBER(5,2));

--修改列名

ALTER TABLE shucaib

RENAME COLUMN aname TO bname;

--3，删除表结构

--删除整列

ALTER TABLE shucaib

DROP COLUMN aname;

--删除多列

ALTER TABLE shucaib

drop column (bname,cname)

--删除整行

delete from shucaib where scid = 'zhangs';

--删除表

drop table shucaib;

--复制仅复制表的结构或数据，其他信息不会进行复制，比如（主键、外键、唯一键、索引等）

--4，复制表结构

--WHERE 1=2 指查询的结果为空，若写成 WHERE 1=1 ，则会将整个表结构及其表数据复制过去

--复制没有数据的空表

create table aaaa as select * from shucaib where 1=2;

--5.复制表结构和数据

create table aaa as select * from shucaib;

--三，操作表数据

--commit 是一次性提交到数据库保存，不commit就不会真正存储到数据库中。

--1，插入表数据

--insert into插入对应列的数据

insert into shucaib(scid,scname) values('zhangs an','12');

commit;

--插入所有列的数据

insert into shucaib values('zhansan','李四',0,0,'wangwu');

commit;

--2.修改表数据

--修改列部分数据

update shucaib set scsm=100 where scid='zhangsan';

commit;

--修改整列

update shucaib set scname='caicai';

commit;

--修改某几列数据

update shucaib set scname='李五',scsm=99,scprice=15 where scid='zhangsan';

commit;

--3.删除表数据

--清除列数据

update shucaib set scname=null where scid='zhan';

commit;

--清除表

--删除所有数据，保留日志，慢

delete from shucaib;

--删除所有数据，不保留日志，快

TRUNCATE TABLE shucaib;

--四. 常用语法查询

--1、关于=><=、between…and…、like、in、and、or 语句

---1.查询表大于等于5，小于等于100

select * from shucaib where scsm>=5 and scsm<=100

select * from shucaib where scsm between 5 and 99

---2.查询表所有zhangs..的

----与表中上下反序

select * from shucaib where SCid like 'zhangs%';

---3.查询表包含ngs并且是wangwu

select * from shucaib where scid like '%ngs%' and bname='wangwu';

---4.查询表包含zhangs或zhangwu的，并且是99

select * from shucaib where (scid like '%zhangs%' or bname like '%gwu%') and scsm=99;

---5.查询表名称为zhan、zhangs的人

select * from shucaib where scid in ('zhan'，'zhangs');

--2、关于 to_char、to_number、to_date 、cast 语句

--oracle

---to_char：转到字符串，例如日期转到字符串：

to_char(sysdate,'yyyy-mm-dd')

---to_number：转到数字，例如字符串转数字：

to_number('100')

---to_date：转到日期，例如字符串转日期：to_date('2017-01-01','yyyy-mm-dd')

select to_date('2025-01-20 20:10:30','yyyy-mm-dd hh24:mi:ss') from dual

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual

select to_number('100') from dual

select '1'+'2' from dual

select '1'||'2' from dual

select to_char('') from dual --'' null

--mysql

---cast：转到字符串，例如日期转到字符串：cast(now() as char) 或 DATE_FORMAT(NOW(), '%Y-%m-%d')

---cast：转到字符串，例如字符串转到数字：cast('100' as unsigned)

---cast：转到字符串，例如字符串转到日期：cast('19981122' as date) 或 STR_TO_DATE('2017-01-06','%Y-%m-%d')

--3、关于 max、min、avg、sum 、count 语句

---1.查询T_JS 中缴费(num)最大、最小、平均、总和

select max(num) from T_JS;

select min(num) from T_JS;

select avg(num) from T_JS;

select sum(num) from T_JS;

---2.查询T_JS 中姓别(xb)为男性的所有人数

select count(xb) 男性人数 from T_JS where xb='男'

--4、关于 round、trunc 语句

--强制保留小数位数，用0 补

select to_char(round(t.scprice,2),'fm990.00') from shucaib t

select to_char(round(scprice,2),'fm990.00') from shucaib

----第一条查询使用了表别名 t，这在复杂的查询中（例如涉及多表连接）可以提高代码的可读性和清晰度。

----第二条查询没有使用表别名，适用于简单的单表查询。

--round()：数值取值，四舍五入；trunc()：数值截取，非四舍五入

---1.查询教师T_JS中金额例如：1234.567，保留2位小数（需要四舍五入） ---返回结果 1234.57

select round(,2) from dual;

select 1+2 from dual

select a.

from round(1234.567) a,

shucaib b

--或者 ---采用cast与decimal结合，cast表示: cast(字段名 as 转换的类型 )

select cast(1234.567 as decimal(7,2)) from T_JS; ---decimal(7,2)表示数值中共有7位，保留2位小数

---2.查询教师T_JS中金额例如：1234.567，保留2位小数（不需要四舍五入） ---返回结果 1234.56

--oralce

select trunc(1234.567,2) from T_JS;

--mysql

--select truncate(1234.567,2) from T_JS;

--5、关于distinct 、decode、listagg 语句

--distinct()：去重；decode(替换)；listagg()：转行

--1.查询去重

---distinct去重

select distinct scsm from shucaib;

---GROUP BY去重,用于分组数据，同时也可以实现去重的效果。它通常用于需要对分组后的数据进行聚合操作的场景。

SELECT scsm

FROM shucaib

GROUP BY scsm;

---ROW_NUMBER()去重,常用于复杂的去重场景，比如需要根据某些字段排序后去重。

WITH RankedData AS (

SELECT scname,scprice,

ROW_NUMBER() OVER (PARTITION BY scname ORDER BY scprice) AS rn

FROM shucaib

)

SELECT scname,scprice

FROM RankedData

WHERE rn = 1;

---2.分组转行表中，并用，分开

select scname,

listagg(scprice,',') within group (order by scname) as scprice

from shucaib

where scname='12' ---可以和分组的scname不一样

group by scname;

---3.查询表中更换

---oracle

----如果 scsm 的值既不是 '99' 也不是 '0'，DECODE 函数会返回空值（NULL）。

select scname,decode(scsm,'99','好sc','0','坏sc','weizhi') as sex from shucaib;

---mysql中

--select xm,IF(xb='1','男','女') as sex from T_JS;

--6、关于 order by、group by、having 语句

--group by ：分组查询；having：查询排序，常配合asc、desc使用（重复数据查询）

---1.查询姓名（xm）排序

--默认升序

select * from shucaib

order by scname;

--降序

select * from shucaib

order by scname desc;

---2.查询出现次数

select scsm,count(scsm)

from shucaib

group by scsm;

---3.查询特定数据出现次数

select scsm,COUNT(scsm)

FROM shucaib

where scsm IN (99, 0)

GROUP BY scsm;

--查询特定数据出现次数并按统计数量降序排列结果。

SELECT scsm, COUNT(scsm) AS scsm_count

FROM shucaib

WHERE scsm IN (99, 0)

GROUP BY scsm

ORDER BY scsm_count DESC;

---4.统计表中数据出现次数并筛选出出现次数大于特定值的数据

select scsm,count(scsm)

from shucaib

group by scsm

having count(scsm)>2;

---5.查询T_JS 工号（xh）大于1的重复数据（根据单字段查询重复数据）

--根据单个字段查重

---查询出现次数满足特定条件的重复数据数

SELECT scsm, COUNT(*) AS scsm_count

FROM shucaib

GROUP BY scsm

HAVING COUNT(*) > 2;

---查询出现次数满足特定条件的重复数据信息

select *

from shucaib

where scsm in ( --主查询从表中筛选出scsm值在子查询结果中的记录IN子句用于匹配主查询中的scsm值是否存在于子查询返回的列表中。

select scsm

from shucaib

group by scsm

having count(scsm)>1--子查询的作用是找出所有出现次数大于1的 scsm 值。返回一个包含这些 scsm 值的列表。

);

--性能优化：

--如果表 shucaib 很大，子查询可能会导致性能问题。可以考虑为 scsm 字段添加索引，以提高查询效率。

--创建索引的语句 CREATE INDEX idx_scsm ON shucaib(scsm);

--如果数据库支持窗口函数（如 ROW_NUMBER() 或 COUNT()），可以使用更高效的查询方式。可以使用COUNT()窗口函数来避免子查询：例如：

---- 创建索引

CREATE INDEX idx_scsm ON shucaib(scsm);

---- 查询逻辑

SELECT *

FROM (

SELECT scsm, scname, COUNT(*) OVER (PARTITION BY scsm) AS scsm_count

FROM shucaib

) t

WHERE scsm_count > 1;

--可读性：如果子查询的逻辑较为复杂，可以将其定义为一个公共表表达式（CTE），以提高代码的可读性。例如：

WITH DuplicateSCSM AS (

SELECT scsm

FROM shucaib

GROUP BY scsm

HAVING COUNT(scsm) > 1

) --CTE部分

SELECT *

FROM shucaib

WHERE scsm IN (SELECT scsm FROM DuplicateSCSM); --通过 IN 子句引用了CTE中的 scsm 值。

--或者

--using 当同名字段作连接条件，可代替on更好用，可以使用多个字段作为条件（using(id)== on.a.id=b.id）

select *

from shucaib

inner join (

select scsm

from shucaib

group by scsm

having count(scsm)>1

) as t using(scsm);

--INNER JOIN用于连接主表T_JS和子查询的结果。

--USING (xh)是JOIN的语法糖，用于指定连接条件，表示两个表通过xh字段进行连接。

--使用IN子句替代JOIN：如果你的目标仅仅是筛选出xh值出现次数大于1的记录，使用IN子句可能更直观：

SELECT *

FROM shucaib

WHERE scsm IN (

SELECT scsm

FROM shucaib

GROUP BY scsm

HAVING COUNT(scsm) > 1

);

--使用CTE提高可读性：如果子查询逻辑较为复杂，可以使用公共表表达式（CTE）来提高代码的可读性：

WITH DuplicateXH AS (

SELECT scsm

FROM shucaib

GROUP BY scsm

HAVING COUNT(scsm) > 1

)

SELECT T.*

FROM shucaib T

INNER JOIN DuplicateXH D ON T.scsm = D.scsm;

--删除以上重复数据信息，根据scsm查询，scid作为唯一性判断

delete from shucaib

where id in(

select t.scid

from (

select *

from shucaib

where scsm in (

select scsm

from shucaib

group by scsm

having count(scsm)>1----内层子查询找出所有重复的scsm值（即出现次数大于1的 scsm）。

)

and id not in (

select min(scid)

from shucaib

group by scsm

having count(scsm)>1-----中间子查询找出每个重复的 xh 对应的最小scid

) ----外层子查询筛选出scsm重复但scid不是最小值的记录。

) as t ---mysql特殊需要增加一层嵌套和赋值

); ---主查询，删除这些记录

---更简洁的写法

DELETE FROM shucaib

WHERE id NOT IN (

SELECT MIN(scid)

FROM shucaib

GROUP BY scsm

);

---6.查询工号（xh）、姓名（xm）大于1的重复数据（根据多字段查询重复数据，可同第5点单字段）

--根据多个字段查重

select scsm,scname,count(*)

from shucaib

group by scsm,scname

having count(*)>1;

select *

from shucaib

where (scsm,scname) in (

select scsm,scname

from shucaib

group by scsm,scname

having count(scsm)>1

);

--或者

SELECT s.*

FROM shucaib s

INNER JOIN (

SELECT scsm, scname

FROM shucaib

GROUP BY scsm, scname

HAVING COUNT(*) > 1

) t

ON s.scsm = t.scsm AND s.scname = t.scname;

--删除以上重复数据信息，根据xh，xm判断，id作为唯一性判断

delete from T_JS

where id in(

select t.id

from (

select *

from T_JS

where (xh,xm) in (

select xh,xm

from T_JS

group by xh,xm

having count(xh)>1

)

and id not in (

select min(id)

from T_JS

group by xh,xm

having count(xh)>1)

) as t ---mysql特殊需要增加一层嵌套和赋值

);

--7、关于 case when 语句

--case when…:：判断条件，满足返回真，不满足返回假 ;

---例如：case when 1 then ‘1’ when 0 then ‘2’ else ‘未知’ end

---1.查询数据所在比例

selectcount(1) as 人口总数,

sum(case when scname='caicai' then 1 else 0 end) 男生,

sum(case when scname='caicai' then 1 else 0 end)*1/count(1) caicai所在比,

sum(case when scname='12' then 1 else 0 end) 女生,

sum(case when scname='12' then 1 else 0 end)*1/count(1) yier所占比,

sum(case when scname='0' then 1 else 0 end) 其他,

sum(case when scname='0' then 1 else 0 end)*1/count(1) 其他所占比

from shucaib;

---可以将 case when 表达式提取为子查询，使代码更清晰。

SELECT

COUNT(1) AS total_population,

SUM(CASE WHEN scname = 'caicai' THEN 1 ELSE 0 END) AS male_count,

SUM(CASE WHEN scname = 'caicai' THEN 1 ELSE 0 END) / COUNT(1) AS male_ratio,

SUM(CASE WHEN scname = '12' THEN 1 ELSE 0 END) AS female_count,

SUM(CASE WHEN scname = '12' THEN 1 ELSE 0 END) / COUNT(1) AS female_ratio,

SUM(CASE WHEN scname = '0' THEN 1 ELSE 0 END) AS other_count,

SUM(CASE WHEN scname = '0' THEN 1 ELSE 0 END) / COUNT(1) AS other_ratio

FROM

shucaib;

--8、关于 left join / right join / inner join…on 语句

---left join：（左连接），以左表为基础，返回左表所有与右边等值的记录

---right join：（右连接），以右表为基础，返回右表所有与左边等值的记录

---inner join：(等值连接)，返回两表等值的记录

---1.根据表查询关联

select a.*,b.scsm from shucaib a inner join shucaia b on a.scsm=b.scsm

--9、关于 length、concat、substr 语句

--length()：字符串长度；concat()：字符串拼接；substr()：字符串截取

---1.查询数据长度

select t.scid,length(scid),length(trim(scid)) from shucaib t;

SELECT data_type FROM user_tab_columns WHERE table_name = 'SHUCAIB' AND column_name = 'SCID';

SELECT scid FROM shucaib;

SELECT * FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_SORT';----二进制排序

----如果字段中可能包含 NULL 值，可以使用 NVL 或 COALESCE 函数处理。例如NULL 值会被替换为 0：

SELECT NVL(LENGTH(scid), 0) FROM shucaib;

----区分 LENGTH 和 LENGTHB,LENGTH 函数计算的是字符数。

----如果需要计算字节长度（例如对于多字节字符集），可以使用 LENGTHB 函数：

SELECT LENGTHB(scid) FROM shucaib;

----筛选出长度为特定值的记录，可以结合 WHERE 子句使用。例如：

SELECT scid FROM shucaib WHERE LENGTH(scid) = 4;

----统计字段的最大长度或最小长度，可以使用聚合函数：

SELECT MAX(LENGTH(scid)) AS max_length, MIN(LENGTH(scid)) AS min_length FROM shucaib;

---2.查询教师T_JS 姓名（xm）和身份证号（sfzh）拼接

---两个字段用空格分开

-----使用 CONCAT 函数，需要嵌套使用，因为 CONCAT 只能拼接两个字符串

SELECT CONCAT(CONCAT(scname, ' '), scid) AS concatenated_string

FROM shucaib;

----推荐使用 || 操作符，因为它更简洁、易读，且可以直接拼接多个字符串

SELECT scname || ' ' || scid AS concatenated_string

FROM shucaib;

----从表 shucaib 中选择满足条件的单行记录，将 scname 和 scid 拼接成一个字符串，

----并将其存储到变量 v_concatenated_string 中，最后通过 DBMS_OUTPUT.PUT_LINE 输出结果。

DECLARE

v_concatenated_string VARCHAR2(100);

BEGIN

-- 使用 SELECT INTO 获取单行结果

SELECT scname || ' ' || scid

INTO v_concatenated_string

FROM shucaib

WHERE scid = 'zhang'; -- 假设根据某个条件选择一行

DBMS_OUTPUT.PUT_LINE('Concatenated String: ' || v_concatenated_string);

END;

---如果 scname 是 NULL，scname || ' ' || scid 的结果将是 ' ' || scid，即只包含 scid。

---如果需要避免这种情况，可以在拼接前使用 NVL 函数处理 NULL 值：

DECLARE

v_concatenated_string VARCHAR2(100);

BEGIN

SELECT NVL(scname, '') || ' ' || NVL(scid, '') INTO v_concatenated_string

FROM shucaib

WHERE scid = 'zhang';

DBMS_OUTPUT.PUT_LINE('Concatenated String: ' || v_concatenated_string);

END;

----SELECT INTO 要求查询结果必须是单行。

----如果查询返回多行，会抛出 ORA-01422: exact fetch returns more than requested number of rows 错误。

----增加变量长度

DECLARE

v_concatenated_string VARCHAR2(200);

----------------------------------------------------------------------------------------

DECLARE

v_concatenated_string VARCHAR2(200); -- 增加变量长度

BEGIN

-- 使用 SELECT INTO 获取单行结果

SELECT NVL(scname, '') || ' ' || NVL(scid, '') -- 处理 NULL 值

INTO v_concatenated_string

FROM shucaib

WHERE scid = 'zhang'; -- 假设根据某个条件选择一行

-- 输出结果

DBMS_OUTPUT.PUT_LINE('Concatenated String: ' || v_concatenated_string);

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('No data found for the given condition.');

WHEN TOO_MANY_ROWS THEN

DBMS_OUTPUT.PUT_LINE('Too many rows returned. Please refine the WHERE clause.');

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);

END;

----------------------------------------------------------------------------------------

---3.截取数据的特定部分

select substr(scid,2,3) from shucaib where length(scid)=8; --(身份证号确认是18位的，从第7位开始，取4位)

--10、关于 union all、union、intersect、minus 语句

--union all：表示不去掉重复数据

--union：表示去掉重复数据

--intersect：表示交集，共有的数据

--minus：表示差集，先查出第一个，然后减去第二个的数据

----1.查询两张表中相同数据名称的数据 --保证查询字段一致

select scid,scname from shucaib

union

select scid,scname from shucaia;

--rollback 是回滚操作，代表的意思就是不commit就可以回滚到上一次操作

--对数据加法操作

update shucaib set scsm=scsm+10 where scid='zhan';

commit;

--对数据乘法操作

update shucaib set scsm=scsm*3 where scid='zhan';

commit;

--增加条件约束

alter table shucaib

add constraint aname check(xb in ('男','女'));

alter table shucaib

add constraint bname check(nl>0 and nl<=130);

--查询表结构方法

--方法一，使用DESCRIBE命令，在命令窗口（command）输入sql命令

DESCRIBE SHUCAIB;

--方法二，数据字典视图user_constraints

--查询表的约束信息

SELECT constraint_name, constraint_type

FROM user_constraints

WHERE table_name = 'SHUCAIB';

--查询表的列信息

SELECT column_name, data_type, data_length, nullable

FROM user_tab_columns

WHERE table_name = 'SHUCAIB';

--查询表的主键和唯一约束列

SELECT constraint_name, column_name

FROM user_cons_columns

WHERE table_name = 'SHUCAIB'

AND constraint_name IN (

SELECT constraint_name

FROM user_constraints

WHERE table_name = 'SHUCAIB'

AND constraint_type IN ('P', 'U')

);

--方法三，使用DBA_视图，需要DBA权限

-- 查询列信息

SELECT column_name, data_type, data_length, nullable

FROM dba_tab_columns

WHERE table_name = 'SHUCAIB';

-- 查询约束信息

SELECT constraint_name, constraint_type, status

FROM dba_constraints

WHERE table_name = 'SHUCAIB';

--右键表名称，左键‘描述（describe）’显示表每一列（字段）的详细情况

--右键表名称，左键‘查看（view）’即显示表的一切详细情况（表空间，表名称，索引，列，键，权限，触发器 ...）

--删除主键约束后，表中不再有唯一性约束。如果需要保留唯一性，可以考虑添加一个唯一约束

ALTER TABLE SHUCAIB

ADD CONSTRAINT UK_SCID UNIQUE (scid);

--查询主键约束名称

--表名必须是大写

--P 表示主键（Primary Key）

--U 表示唯一约束（Unique Constraint）

--R 表示外键（Foreign Key）

--C 表示检查约束（Check Constraint）

SELECT constraint_name

FROM user_constraints

WHERE table_name = 'SHUCAIB' AND constraint_type = 'P';

--检查唯一性

SELECT scid, scsm, COUNT(*)

FROM SHUCAIB

GROUP BY scid, scsm

HAVING COUNT(*) > 1;

--检查是否允许为空

SELECT scid, scsm

FROM SHUCAIB

WHERE scid IS NULL OR scsm IS NULL;

--for update 语句运行时，

--会在对应行（where 条件）加上行级锁，如果没有where条件，会对全表进行加锁，

--当其他人忘记提交或者回滚事务的时候，就会发生锁表，只适合单人进行操作，不适合多人同时操作；

--rowid 运行的时候，

--并没有给数据加上行级锁，可以对数据进行编辑，提交的瞬间完成上锁，提交，解锁等动作。

--所以，当多人对表进行操作的时候，并不会产生无法操作的现象。

SELECT t.*,rowid FROM Xm_ntbf t;

SELECT * FROM Xm_ntbf t FOR UPDATE;