Oracle am.2019/10/22 C:\app\Administrator\product C:\app\Administrator\oradata C:\app\Administrator\oradata\orcl XX.CTL 控制文件 XX.DBF 數據文件 XX.LOG 日誌文件 j ...
Oracle
am.2019/10/22
C:\app\Administrator\product
C:\app\Administrator\oradata
C:\app\Administrator\oradata\orcl
XX.CTL 控制文件
XX.DBF 數據文件
XX.LOG 日誌文件
jdk路徑
C:\app\Administrator\product\11.1.0\db_1\jdk\bin
資料庫文件預設存放路徑
C:\app\Administrator\product\11.1.0\db_1\database
(mysql -u root -p)
cmd dos命令行執行
sqlplus 用戶名/密碼
sqlplus system/123
連接Oracle伺服器
conn 用戶名/密碼 as 連接身份@伺服器連接字元串
-- 創建表空間
create tablespace 表空間名 datafile '數據文件名' size 大小;
create tablespace T128 datafile 'T128.dbf' size 10M
AUTOEXTEND ON;
create tablespace T128 datafile 'C:\T128\T128.dbf' size 10M
/
autoextend on 自動擴展表空間
/
--刪除非空表空間
drop tablespace 表空間名 including contents;
-- 查看表空間名字情況
select tablespace_name from dba_tablespaces;
-- 查看表結構
desc[ribe] 表名;
desc dba_tablespaces;
--創建用戶使用表空間
create user 用戶名 identified by 密碼 default tablespace 表空間;
create user T128 identified by 123 default tablespace T128;
-- 授權
grant 許可權 to 用戶名;
grant dba to t128; --管理員許可權
grant connect to t128; --連接權
session 會話權
use 資料庫名(mysql)
-- 指定用戶登錄
connect 用戶名/密碼;
--創建表
varchar2
int
char
date
--創建表auto_test設置id的值為自增長
create table auto_test(
id int not null auto_increment primary key,
name varchar2(10)
);
--序列sequence
drop sequence seq_abc;
create sequence seq_abc
start with 1 maxvalue 99999;
select seq_abc.nextval
select seq_abc.currval from dual;
select to_char(sysdate,'yyyymmdd')||empno||seq_abc.nextval,empno,ename from emp;
--課後練習
--創建一個員工管理系統(表空間)
create tablespace emp datafile 'emp.dbf' size 10M autoextend on;
--創建一個用戶使用該表空間
create user yc identified by 123 default tablespace emp;
--用system用戶授權DBA給以上用戶
grant dba to yc;
--連接該用戶
conn yc/123;
--為員工管理系統建如下表:員工信息表、部門表、員工考勤表
create table emp_info (
no int primary key,
name varchar2(10) not null,
sex char(2),
bir date,
tel number(11,0),
indate date
)
create table dept(
no int primary key,
name varchar2(20)
)
create table kq(
no int,
name varchar2(10),
dept varchar2(20),
clockin date,
clockout date,
constraint fk_kq_emp_no foreign key(no) references emp_info(no)
)
--自習設計以上表(提供資料庫設計文檔)
自行設計
ER圖
-- 修改表結構之添加約束
alter table emp_info add constraint pk_emp primary key(no);
數據類型
--date 日期和時間類型
select sysdate from dual:
15-4月-2000
to_char(參數) --將參數轉換成為字元串
to_date(參數) --將字元串轉換成為日期型格式
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from emp_info;
--拷貝表
create table 表名 as select 欄位 from 表名2;
--拷貝表結構
create table 表名 as select 欄位 from 表名2 where 不成立條件;
--事務create/drop/truncate
insert/update/delete
提交/回退
-- || 連接符
select 'abc'||name from emp_info;
select '員工編號：'||no||'員工姓名：'||name from emp_info;
--連接 concat()
select concat('hello','world') from dual;
--檢查約束
create table t1(id int check(id>=1 and id<=100),name char(10));
check
日期向前推2月
select add_months(sysdate,-2) from dual;
日期向後推2天
select sysdate+2 from dual;
向後推2個小時
select sysdate+2/24 from dual;
select last_day(sysdate) from dual;
--四捨五入
select round(sysdate,'mm') from dual;
--離當前最近的下一個禮拜二
select next_day(sysdate,'星期二') from dual;
查詢跟張三來自同一個地方的學生姓名
select a.name, a.address from stu a,stu b
where a.address= b.address
and b.name='張三';
字元串截斷：substr()
字元串長度：length()
內容替換：replace()
select substr('hello',1,3),length('hello'),replace('hello','l','x') from dual;
日期-數字=日期
日期+數字=日期
日期-日期=數字（天數）
--當前日期
select sysdate from fual;
--求出星期數:當前日期數-生日日期=天數 /7=星期數
select no,name,round((sysdate-bir)/7) from emp;
作業：
--創建一個部門表
create table dept(
deptid int primary key,
deptname varchar2(20)
);
--創建一個員工信息表
create table emp(
empid int primary key check(empid>0),
deptid int not null,
name varchar2(10),
sex varchar(4) check(sex in ('男','女','未知')),
bir date,
height numeric(3,2) check( height>0 and height<3 ),
tel numeric(11) check(tel like '1%' and length(tel)=11 ) ,
gz numeric(7,2) check(gz between 5000 and 50000),
constraint fk_emp_dept_deptid foreign key(deptid) references dept(deptid)
);
--創建一個考勤表
create table kq(
empid int,
ontime date,
offtime date,
constraint fk_kq_emp_empid foreign key(empid) references emp(empid)
);
insert into dept(deptid) values(1);
insert into dept(deptid) values(2);
insert into dept(deptid) values(3);
update dept set deptname='開發部' where deptid=1;
update dept set deptname='測試部' where deptid=2;
update dept set deptname='運營部' where deptid=3;
commit
select * from dept;
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(1,1,'西沉','男','15-4月-1998',1.80,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(2,1,'路飛','男','05-5月-2000',1.76,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(3,1,'索隆','男','11-11月-1998',1.83,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(4,2,'娜美','女','03-7月-1999',1.78,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(5,2,'喬巴','男','24-12月-2005',1.58,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(6,2,'山治','男','01-6月-1998',1.82,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(7,3,'烏索普','男','1-4月-2000',1.75,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(8,3,'弗蘭奇','男','07-8月-1979',1.90,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(9,3,'布魯克','男','12-12月-1937',1.92,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(10,1,'羅賓','女','04-5月-1989',1.81,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(11,2,'小櫻','女','03-3月-2000',1.76,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(12,3,'鳴人','男','05-9月-2000',1.56,'18692391446',50000);
commit
select * from emp;
insert into kq(empid,ontime,offtime) values(1,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into kq(empid,ontime,offtime) values(2,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into kq(empid,ontime,offtime) values(3,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into kq(empid,ontime,offtime) values(4,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into kq(empid,ontime,offtime) values(5,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into kq(empid,ontime,offtime) values(6,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into kq(empid,ontime,offtime) values(7,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into kq(empid,ontime,offtime) values(8,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into kq(empid,ontime,offtime) values(9,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
commit
select * from kq;
--取年份
select to_char(sysdate,'yyyy') from dual;
--查詢1999年後出生且性別為女的員工信息並按出生年份降序排序
select * from emp
where to_char(bir,'yyyy')>1999
and sex='女'
order by to_char(bir,'yyyy') desc;
select * from emp
where extract(year from bir)>1999
and sex='女'
order by extract(year from bir) desc;
-- 取出給定範圍的月數
select months_between(sysdate, date'2019-11-24') from emp;
--計算年齡
select floor(months_between(sysdate,bir)/12) from emp;
select floor((sysdate-bir)/365) from emp;
select to_char(sysdate,'yyyy')-to_char(bir,'yyyy') from emp;
select extract(year from sysdate)-extract(year from bir) from emp;
--查詢年齡21歲以上員工信息並按員工編號和年齡降序排序
select * from emp
where floor(MONTHS_BETWEEN(sysdate,bir)/12)>21
order by empid,floor(MONTHS_BETWEEN(sysdate,bir)/12) desc;
select * from emp
where to_char(sysdate,'yyyy')-to_char(bir,'yyyy')>21
order by empid,to_char(sysdate,'yyyy')-to_char(bir,'yyyy') desc;
select * from emp
where floor((sysdate-bir)/365) >21
order by empid,floor((sysdate-bir)/365) desc;
-- ||字元串連接符
select '員工編號是：'||empid||',姓名是:'||name from emp;
--查詢每個年齡員工的數量並按數量排序:年齡、數量
select floor((sysdate-bir)/366) as 年齡,count(*) as 數量 from emp
group by floor((sysdate-bir)/366)
order by 年齡,數量;
--查詢工資最高的員工的員工編號、姓名、性別、年齡、工資
select empid,name,sex,floor((sysdate-bir)/365) from emp
where gz=(select max(gz) from emp);
--查詢10月份各部門的遲到人數在2人以上的信息,並按人數降序排序:部門名稱、遲到人數
select c.deptname as 部門名稱,count(distinct a.empid) as 遲到人數 from kq a,emp b,dept c
where a.empid=b.empid and b.deptid=c.deptid
and to_char(a.ontime,'hh24:mi')>'08:20' and to_char(a.ontime,'mm')=10
group by c.deptname
having count(distinct a.empid)>=2
order by 遲到人數 desc;
--行號
select name,bir,rownum from emp where rownum<=5;
--排名
select row_number() over(order by sex desc),name,bir from emp;
-- 按身高排名
select empid,name,height,row_number() over(order by height) 名次 from emp;
--身高一樣，名次相同
select empid,name,height,rank() over(order by height) 名次 from emp;
--身高一樣，名次相同，不跳號
select empid,name,height,dense_rank() over(order by height) 名次 from emp;
--merge into
--樹形結構查詢
start with..connect by
select * from scott.emp start with empno=7369 connect by prior mgr=empno;
.人員表中增加上級編號欄位,記錄該員工的上級員工編號
.對人員表造樹形結構數據，將數據更新為只有一個人的上級ID為空，
所有人的上級ID均為其他人，呈現出父子孫的多層關係數據
.將以上數據按照父、子、孫的先後關係查詢呈現出來
.將人員表第一層員工的基本工資漲為20000,第二層漲1000,
第3層如果原來大於6000則增加500,否則增加400，其它層次一律漲200
select ename,sal,
decode(level,
1,20000,
2,sal+1000,
3,decode(sign(sal-6000),1,sal+500,sal+400),sal+200) x
from emp start with empno=7839 connect by prior empno=mgr;
select empno,empname,sal,level,
(case
when level=1 then 20000
when level=2 then sal+1000
when level=3 and sal>=6000 then sal+500
when level=3 and sal<6000 then sal+400
else sal+200
end)x
from emp start with empno=7839 connect by prior empno=mgr;
--多表關聯更新
update emp a set sal=
(
select(case
when level=1 then 20000
when level=2 then b.sal+1000
when level=3 and b.sal>=6000 then b.sal+500
when level=3 and b.sal<6000 then b.sal+400
else b.sal+200
end)x from emp b where a.empno=b.empno
start with b.empno=7839 connect by prior b.empno=b.mgr
);
--創建一個部門表
create table dept(
deptid int primary key,
deptname varchar2(20)
);
--創建一個員工信息表
create table emp(
empid int primary key check(empid>0),
deptid int not null,
name varchar2(10),
sex varchar(4) check(sex in ('男','女','未知')),
bir date,
height numeric(3,2) check( height>0 and height<3 ),
tel numeric(11) check(tel like '1%' and length(tel)=11 ) ,
gz numeric(7,2) check(gz between 5000 and 50000),
constraint fk_emp_dept_deptid foreign key(deptid) references dept(deptid)
);
--創建一個考勤表
create table kq(
empid int,
ontime date,
offtime date,
constraint fk_kq_emp_empid foreign key(empid) references emp(empid)
);
insert into dept(deptid) values(1);
insert into dept(deptid) values(2);
insert into dept(deptid) values(3);
update dept set deptname='開發部' where deptid=1;
update dept set deptname='測試部' where deptid=2;
update dept set deptname='運營部' where deptid=3;
commit
select * from dept;
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(1,1,'西沉','男','15-4月-1998',1.58,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(2,1,'路飛','男','05-5月-2000',1.76,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(3,1,'索隆','男','11-11月-1998',1.83,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(4,2,'娜美','女','03-7月-1999',1.78,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(5,2,'喬巴','男','24-12月-2005',1.58,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(6,2,'山治','男','01-6月-1998',1.82,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(7,3,'烏索普','男','1-4月-2000',1.75,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(8,3,'弗蘭奇','男','07-8月-1979',1.90,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(9,3,'布魯克','男','12-12月-1937',1.92,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(10,1,'羅賓','女','04-5月-1989',1.81,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(11,2,'小櫻','女','03-3月-2000',1.76,'18692391446',50000);
insert into emp(empid,deptid,name,sex,bir,height,tel,gz) values(12,3,'鳴人','男','05-9月-2000',1.56,'18692391446',50000);
commit
select * from emp;
insert into kq(empid,ontime,offtime) values(1,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into kq(empid,ontime,offtime) values(2,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into kq(empid,ontime,offtime) values(3,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into kq(empid,ontime,offtime) values(4,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into kq(empid,ontime,offtime) values(5,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into kq(empid,ontime,offtime) values(6,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into kq(empid,ontime,offtime) values(7,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into kq(empid,ontime,offtime) values(8,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into kq(empid,ontime,offtime) values(9,to_date('2019-10-24 8:30:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-10-24 21:00:00','yyyy-mm-dd hh24:mi:ss'));
commit
select * from kq;
--取年份
select to_char(sysdate,'yyyy') from dual;
--查詢1999年後出生且性別為女的員工信息並按出生年份降序排序
select * from emp
where to_char(bir,'yyyy')>1999
and sex='女'
order by to_char(bir,'yyyy') desc;
select * from emp
where extract(year from bir)>1999
and sex='女'
order by extract(year from bir) desc;
-- 取出給定範圍的月數
select months_between(sysdate, date'2019-11-24') from emp;
--計算年齡
select floor(months_between(sysdate,bir)/12) from emp;
select floor((sysdate-bir)/365) from emp;
select to_char(sysdate,'yyyy')-to_char(bir,'yyyy') from emp
select extract(year from sysdate)-extract(year from bir) from emp
--查詢年齡21歲以上員工信息並按員工編號和年齡降序排序
select * from emp
where floor(MONTHS_BETWEEN(sysdate,bir)/12)>21
order by empid,floor(MONTHS_BETWEEN(sysdate,bir)/12) desc;
select * from emp
where to_char(sysdate,'yyyy')-to_char(bir,'yyyy')>21
order by empid,to_char(sysdate,'yyyy')-to_char(bir,'yyyy') desc;
select * from emp
where floor((sysdate-bir)/365) >21
order by empid,floor((sysdate-bir)/365) desc;
-- ||字元串連接符
select '員工編號是：'||empid||',姓名是:'||name from emp;
--查詢每個年齡員工的數量並按數量排序:年齡、數量
select floor((sysdate-bir)/366) as 年齡,count(*) as 數量 from emp
group by floor((sysdate-bir)/366)
order by 年齡,數量;
--查詢工資最高的員工的員工編號、姓名、性別、年齡、工資
select empid,name,sex,floor((sysdate-bir)/365) from emp
where gz=(select max(gz) from emp);
--查詢10月份各部門的遲到人數在2人以上的信息,並按人數降序排序:部門名稱、遲到人數
select c.deptname as 部門名稱,count(distinct a.empid) as 遲到人數 from kq a,emp b,dept c
where a.empid=b.empid and b.deptid=c.deptid
and to_char(a.ontime,'hh24:mi')>'08:20' and to_char(a.ontime,'mm')=10
group by c.deptname
having count(distinct a.empid)>=2
order by 遲到人數 desc;
select name 姓名 from emp;
--行號
select name,bir,rownum from emp where rownum<=5;
--排名
select row_number() over(order by sex desc),name,bir from emp;
-- 按身高排名
select empid,name,height,row_number() over(order by height) 名次 from emp;
--身高一樣，名次相同
select empid,name,height,rank() over(order by height) 名次 from emp;
--身高一樣，名次相同，不跳號
select empid,name,height,dense_rank() over(order by height) 名次 from emp;
