Oracle数据库安装以及SQL命令的使用
Oracle数据库安装
-
软件下载链接 https://pan.baidu.com/s/1DpgLH30Sft8apVC-f4DBjA
提取码:koyc -
安装步骤
-
推荐教程 oracle数据库教程
SQL使用
1)数据库语言类型
数据库的DDL、DML和DCL的区别与理解
-
DML(data manipulation language)
- 数据操纵语言,它们是SELECT、UPDATE、INSERT、DELETE,就象它的名字一样,这4条命令是用来对数据库里的数据进行操作的语言
-
DDL(data definition language)
- 数据库定义语言, DDL比DML要多,主要的命令有CREATE、ALTER、DROP等,DDL主要是用在定义或改变表(TABLE)的结构,数据类型,表之间的链接和约束等初始化工作上,他们大多在建立表时使用
-
DCL(Data Control Language)
- 是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句,包括(grant,deny,revoke等)语句。在默认状态下,只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL
-
TCL - Transaction Control Language
- 务控制语言,COMMIT - 保存已完成的工作,SAVEPOINT - 在事务中设置保存点,可以回滚到此处,ROLLBACK - 回滚,SET TRANSACTION - 改变事务选项
2) 账号授权
在此界面输入sqlplus /nolog回车
再输入conn /as sysdba回车
select name from v$datafile; 查看数据库文件存放地址,一会指定表空间路径需要,回车后
1、创建表空间
create tablespace table1
logging
datafile 'D:\APP\ADMINISTRATOR\ORADATA\ITSM1\table1.dbf'
size 1000m
autoextend on
next 100m maxsize 2048m
extent management local;
create temporary tablespace table1_temp
tempfile 'D:\APP\ADMINISTRATOR\ORADATA\ITSM1\table1_temp.dbf'
size 100m
autoextend on
next 100m maxsize 2048m
extent management local;
3、创建用户、并指定表空间
create user dbadmin identified by dbadmin default tablespace table1 temporary tablespace table1_temp;
4、授权、登录、dba权限等
grant connect,resource,dba to dbadmin;
grant unlimited tablespace to dbadmin;
3)表操作
5、表操作
/*创建表*/
CREATE TABLE table1
(
Id_P int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
/*修改表字段*/
alter table table1 drop column Id_P
alter table table1 add Id_P int ;
/*创建序列*/
create sequence Id_P start with 1 increment by 1;
/*增加表内容*/
INSERT INTO table1 VALUES (value1,value2,value3,...);
/*制定单元格插入内容*/
INSERT INTO table1(LastName,FirstName,Address,City) values ('Tony','Ann','America center park','NewYork' );
INSERT INTO table1(LastName,FirstName,Address,City) values ('Anix','Aoteman','Earth ','Japanese' );
INSERT INTO table1 values ('Tony','Ann','America center park','NewYork',ID_P.Nextval );
INSERT INTO table1 values ('Anix','Aoteman','Earth ','Japanese',ID_P.Nextval );
/*删除表内容*/
delete from table1 where table1.ID_P is null
/*修改表内容*/
UPDATE table1 set LastName='Obu' where table1.ID_P=2
/*查询表*/
select * from table1 where table1.ID_P=2
4)常见函数使用
字符函数
--字符函数
select substr('abcdefg',1,5)substr, --字符串截取
instr('abcdefg','bc') instr, --查找子串
'Hello'||'World' concat, --连接
trim(' wish ') trim, --去前后空格
rtrim('wish ') rtrim, --去后面空格
ltrim(' wish') ltrim, --去前面空格
trim(leading 'w' from 'wish') deleteprefix, --去前缀
trim(trailing 'h' from 'wish') deletetrailing, --去后缀
trim('w' from 'wish') trim1,
ascii('A') A1,
ascii('a') A2, --ascii(转换为对应的十进制数)
chr(65) C1,
chr(97) C2, --chr(十进制转对应字符)
length('abcdefg') len, --length
lower('WISH')lower,
upper('wish')upper,
initcap('wish')initcap, --大小写变换
replace('wish1','1','youhappy') replace, --替换
translate('wish1','1','y')translate, --转换,对应一位(前面的位数大于等于后面的位数)
translate('wish1','sh1','hy')translate1,
concat('11','22') concat --连接
from dual;
日期函数
--日期
--年 yyyy yyy yy year
--月 month mm mon month
--日+星期 dd ddd(一年中第几天) dy day
--小时 hh hh24
--分 mi
--秒 ss
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')currenttime,
to_char(sysdate,'yyyy') year,
to_char(sysdate,'mm') month,
to_char(sysdate,'dd') day,
to_char(sysdate,'day') week,
to_char(sysdate,'hh24')hour,
to_char(sysdate,'mi') minute,
to_char(sysdate,'ss') second
from dual;
select to_date('2009-07-04 05:02:01','yyyy-mm-dd hh24:mi:ss')currenttime,
to_char(to_date('2009-07-04 05:02:01','yyyy-mm-dd hh24:mi:ss'),'yyyy')year,
to_char(to_date('2009-07-04 05:02:01','yyyy-mm-dd hh24:mi:ss'),'mm')month,
to_char(to_date('2009-07-04 05:02:01','yyyy-mm-dd hh24:mi:ss'),'dd') day,
to_char(to_date('2009-07-04 05:02:01','yyyy-mm-dd hh24:mi:ss'),'day') week,
to_char(to_date('2009-07-04 05:02:01','yyyy-mm-dd hh24:mi:ss'),'day','NLS_DATE_LANGUAGE=American') week, --设置语言
to_char(to_date('2009-07-04 05:02:01','yyyy-mm-dd hh24:mi:ss'),'hh24')hour,
to_char(to_date('2009-07-04 05:02:01','yyyy-mm-dd hh24:mi:ss'),'mi') minute,
to_char(to_date('2009-07-04 05:02:01','yyyy-mm-dd hh24:mi:ss'),'ss') second
from dual;
--months_between
select months_between(to_date('03-31-2014','MM-DD-YYYY'),to_date('12-31-2013','MM-DD-YYYY')) "MONTHS"
FROM DUAL;
--next_day
select sysdate today, next_day(sysdate,6) nextweek from dual;
--时间区间
select cardid, borrowdate from borrow where to_date(borrowdate,'yyyy-mm-dd hh24:mi:ss')
between
to_date('2014-02-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and
to_date('2014-05-01 00:00:00','yyyy-mm-dd hh24:mi:ss');
--interval 间隔
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') currenttime,
to_char(sysdate - interval '7' year,'yyyy-mm-dd hh24:mi:ss') intervalyear,
to_char(sysdate - interval '7' month,'yyyy-mm-dd hh24:mi:ss') intervalMonth,
to_char(sysdate - interval '7' day,'yyyy-mm-dd hh24:mi:ss') intervalday,
to_char(sysdate - interval '7' hour,'yyyy-mm-dd hh24:mi:ss') intervalHour,
to_char(sysdate - interval '7' minute,'yyyy-mm-dd hh24:mi:ss') intervalMinute,
to_char(sysdate - interval '7' second,'yyyy-mm-dd hh24:mi:ss') intervalSecond
from dual;
--add_months 增加月份
select add_months(sysdate,12) newtime from dual;
--extract
select extract(month from sysdate) "This Month",
extract(year from add_months(sysdate,36)) " Years" from dual;
聚合函数
--count
select count(1) as count from student;--效率最高
select count(*) as count from student;
select count(distinct score) from student;
--avg
--distinct|all
select avg(score) score from student;
select avg(distinct score) from student;
select classno,avg(score) score from student group by classno;
--max
--distinct|all
select max(score) from student;
select classno, max(score) score from student group by classno;
--min
--distinct|all
select min(score) from student;
select classno, min(score) score from student group by classno;
--stddev(standard deviation)标准差
select stddev(score) from student;
select classno, stddev(score) score from student group by classno;
--sum
select sum(score) from student;
select classno, sum(score) score from student group by classno;
--median--中位数
select median(score) from student;
select classno, median(score) score from student group by classno;