oracle篇】一.oracle入门

oracle

1. 启动sql plus

[username/password] [@server] [as sysdba/sysoper]
system/123456
connect sys/123456 as sysdba

2. 常用命令

# 查看用户
show user
desc dba_users 
select username from dba_users;
# 解锁/锁定用户的语句
alter user uername account [unlock/lock]
解析：在sys，system，sysman，scott四个用户权限中，scott用户最低。
# 查看用户表空间
desc dba_tablesspaces;
select tablespace_name form dba_tablesspaces;
desc user_tablesspaces
select tablespace_name form user_tablesspaces;

desc dba_users
select default_tablespace,temporary_tablespace from dba_users where username='SYSTEM'
# 设置默认表空间和临时表空间
ALTER USER username [DEFAULT|TEMPORARY] TABLESPACE tablespace_name
ALTER USER system DEFAULT TABLESPACE system
select default_tablespace,temporary_tablespace from dba_users where username='SYSTEM'
在Oracle数据库安装完成后，system用户的默认表空间和临时表空间分别是system和temp。

# 创建表空间
CREATE [TEMPORARY] TABLESPACE tablespace_name TEMPFILE|DATAFILE 'XX.dbf' SIZE XX
create tablespace data_tablespace datafile 'datafile.dbf' size 10m
create temporary tablespace temp_tablespace tempfile 'tempfile.dbf' size 10m

desc dba_data_files
select file_name from dba_data_files where tablespace_name='DATA_TABLESPACE'
select file_name from dba_temp_files where tablespace_name='TEMP_TABLESPACE'

# 修改表空间
## 设置联机/脱机状态
ALTER TABLESPACE tablespace_name [ONLINE|OFFLINE];
select status from dba_tablespaces where tablespace_name='DATA_TABLESPACE';

## 设置只读/可读写状态
ALTER TABLESPACE tablespace_name [READ ONLY|READ WRITE];
ALTER TABLESPACE data_tablespace read only;
select status from dba_tablespaces where tablespace_name='DATA_TABLESPACE';

## 修改数据文件
### 增加数据文件
ALTER TABLESPACE tablesapce_name ADD DATAFILE 'xx.dbf' SIZE xx;
alter tablespace data_tablespace add datafile 'data2_file.dbf' size 10m;
select file_name from dba_data_files where tablespace_name='DATA_TABLESPACE';
### 删除数据文件
ALTER TABLESPACE tablesapce_name DROP DATAFILE 'xx.dbf';
alter tablespace data_tablespace drop datafile 'data2_file.dbf';
select file_name from dba_data_files where tablespace_name='DATA_TABLESPACE';

## 删除表空间
DROP TABLESPACE tablespace_name [INCLUDING CONTENTS]
drop tablespce data_tablesace including contents

3. 表空间的分布

1. 永久表空间：表，视图，存储过程
2. 临时表空间：执行过程
3. UNDO表空间：事务所修改的旧值

4. 管理表

4.1 约定

1. 每一列数据必须具有相同的数据类型
2. 列名唯一
3. 每一行数据的唯一性

4.2 数据类型

1. 字符型：char(n)，nchar(n)，varchar2(n)，nvarchar2(n)
2. 数值型：number(p,s) ，float(n)
3. 日期型：date -4712~9999 ，timestamp
4. 其他类型：blob，clob
5. nvarchar2和varchar2都可以表示可变长度的字符，其中nvarchar2用于存储Unicode格式的数据，更适合存储中文数据。

4.3 创建查看表

create talbe table_name(
	column_name datatype,
	column_name date default sysdate
);
desc table_name

4.4 修改表

# 添加字段
alter table tablename add column_name datatype;
# 修改字段数据类型
alter table tablename modify column_name datatype;
# 删除字段
alter table tablename drop column column_name;
# 修改字段名
alter table tablename rename column_name to new_column_name;
# 修改表名
rename table_name to new_table_name;
# 删除表所有数据
truncate table table_name;
# 删除表
drop table table_name;

4.5 操作表数据

# 添加数据
insert into table_name(column1, column2) values(value1, value2);
# 复制表
## 创建表复制
create table new_table_name as select [column1, column2/ *] from old_table_name;
## 添加数据复制
insert into new_table_name [column1] select [column1, column2/ *] from old_table_name; 
# 修改数据
update table_name set column1=value1, column2=value2 [where conditions];
# 删除数据
delete from table_name [where conditions]

4.6 约束

1. 作用：定义规则，保持数据完整性

4.6.1 非空约束和主键约束

# 创建表设置非空约束/主键约束
CREATE TABLE table_name(
	column_name datatype NOT NULL/PRIMARY KEY
);
CREATE TABLE table_name(
	column_name datatype NOT NULL,
	CONSTRAINT constraint_name PRIMARY KEY(column1, column2)
);
# 查看定义好的主键约束
select constraint_name from user_contraints where table_name='xx';

# 修改表设置非空约束/主键约束
Alter TABLE table_name MODIFY column_name datatype NOT NULL;
Alter TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY(column1, column2);
# 更改约束名
Alter TABLE table_name RENAME CONSTRAINT old_constraint_name to new_constraint_name;
# 移除非空约束/主键约束
Alter TABLE tablename MODIFY column_name datatype NULL;
Alter TABLE tablename DROP constraint_name;
Alter TABLE tablename DROP PRIMARY KEY [CASCADE];
# 禁用/启动主键约束
Alter TABLE tablename DISABLE/ENABLE CONSTRAINT constraint_name;

4.6.2 外键约束

# 创建表设置外键约束
CREATE TABLE table1name(
	column_name datatype REFERENCES table2name(column_name)
);
CREATE TABLE table1name(
	column_name datatype,
    CONSTRAINT constraint_name FOREIGN KEY(column_name) REFERENCES table2name(column_name) [ON DELETE CASCADE]
);
# 修改表设置外键约束
ALTER TABLE tablename ADD CONSTRAINT constraint_name FOREIGN KEY(column_name) REFERENCES table2name(column_name) [ON DELETE CASCADE];
# 禁用/启动外键约束
Alter TABLE tablename DISABLE/ENABLE CONSTRAINT constraint_name;
# 删除外键约束
Alter TABLE tablename DROP CONSTRAINT constraint_name;

4.6.3 唯一约束

# 创建表设置唯一约束
CREATE TABLE tablename(
	column_name datatype UNIQUE
);
CREATE TABLE tablename(
	column_name datatype,
	CONSTRAINT constraint_name UNIQUE(column_name)
);
# 修改表设置唯一约束
ALTER TABLE tablename CONSTRAINT constraint_name UNIQUE(column_name);
# 禁用/启动唯一约束
select constraint_name, constraint_type, status from user_constraints where table_name='xx';
ALTER TABLE tablename DISABLE/ENABLE CONSTRAINT constraint_name;
# 删除唯一约束
Alter TABLE tablename DROP CONSTRAINT constraint_name;                                            

4.6.4 检查约束

# 创建表设置检查约束
CREATE TABLE tablename(
	column_name datatype CHECK(expressions)
);
CREATE TABLE tablename(
	column_name datatype,
	CONSTRAINT constraint_name CHECK(expressions)
);
# 修改表设置检查约束
ALTER TABLE tablename CONSTRAINT constraint_name CHECK(expressions);
# 禁用/启动检查约束
select constraint_name, constraint_type, status from user_constraints where table_name='xx';
ALTER TABLE tablename DISABLE/ENABLE CONSTRAINT constraint_name;
# 删除检查约束
Alter TABLE tablename DROP CONSTRAINT constraint_name;  

4.6.5 小结

1. 数据字段：user_constraints

4.7 sql plus设置查询格式

# sql plus 设置查询格式
col username HEADING 用户名;
col username FORMAT a10;
col username FORMAT 999.9;
col username FORMAT $9999.9;
# 清除查询格式
col username CLEAR;

4.8 查询

# 基本的查询语句
SELECT [DISTINCT] column1, column2 FROM tablename [WHERE conditions];
# 查询表中所有字段和指定字段
SELECT * FROM tablename;
SELECT column1, column2 FROM tablename;
# 设置别名
SELECT column1 AS new_name FROM tablename;
# 去重
SELECT DISTINCT username AS 用户名 from user;
# 带条件查询
SELECT * FROM tablename WHERE column='' [AND/OR column2=''];
# 优先值: not > and > or
# 模糊查询 like 通配符 _ 一个字符, % 多个字符
SELECT * FROM tablename WHERE column like 'a%';
# 范围查询
SELECT * FROM tablename WHERE column [NOT] BETWEEN 800 AND 2000;
SELECT * FROM tablename WHERE column IN/NOT IN ('xx','xx');
# 排序
SELECT * FROM tablename [WHERE conditions] ORDER BY column DESC/ASC;

# case..when
CASE column WHEN value THEN result [ELSE result] END;
## 第一种
select username, case username 
when 'aaa' then '计算机部门' when 'bbb' then '市场部门'
else '其他部门' and as 部门
## 第二种
select username case 
when username='aaa' then '计算机部门'
when username='bbb' then '市场部门'
else '其他部门' and as 部门

# decode函数
decode(column, value, result,default)
select username, decode(username,'aaa','计算机部门', 'bbb','市场部门', '其他部门') as 部门 from user;

4.9 函数

4.9.1 数值函数

# 四舍五入
	ROUND(n,[m]) 
        省略m : 0
        m>0 小数点后几位
        m<0 小数点前几位
    # dual oralce提供的目标表
            select round(23.4), round(23.45, 1), round(23.45, -1) from dual; 
# 取整函数
    CEIL(n) 最大值
    FLOOR(n) 最小值
        select ceil(23.45), floor(23.45) from dual;
# 计算函数
    ABS(n) 绝对值
        select abs(23.45), abs(-21), abs(0) from dual;
    MOD(m, n) 取余数
        select mod(5, 2) from dual;
    POWER(m, n) m的n次幂
        select power(5, 2) from dual;
    SORT(n) 开方
        select sort(4) from dual;
# 三角函数
    SIN(n), ASIN(n)
    COS(n), ACOS(n)
    TAN(n), ATAN(n)
    	select sin(2.11) from dual;

4.9.2 字符函数

# 大小写转换函数
    UPPER(char)
    LOWER(char)
    INITCAP(char) 首字母大写
    select upper('abc'), lower('ABC'), initcap('aBC') from dual;
# 获取子字符串函数
	SUBSTR(char, [m, [n]])
	select substr('adbc', 1, 2) from dual;
# 获取字符串长度
	LENGTH(char)
	select length('adb') from dual;
# 字符串连接函数
	CONCAT(char1, char2) 同 ||
	select 'ab' || 'cd' from dual;
	select concat('ab', 'cd') from dual;
# 去除子串函数
	TRIM(c2 FROM c1)
		select trim('a' from 'abc') from dual;
	LTRIM(c1, c2)
		select ltrim('abc', 'a') from dual;
	RTRIM(c1, c2)
		select rtrim('abc', 'a') from dual;
    TRIM(c1)
# 替换函数
	REPLACE(char, s_string[,r_string])
	select replace('abced','a','A') from dual;
	select replace('abced','a') from dual;

4.9.3 日期函数

# 系统时间
    SYSDATE
    默认格式 DD-MON-RR
# 日期操作
ADD_MONTHS(date, i)
	select add_months(sysdate, 3), add_months(sysdate, -3) from dual;
NEXT_DAY(date, char)
	select next_day(sysdate, '星期一') from dual;
LAST_DAY(date)
	select LAST_DAY(sysdate) from dual;
MONTHS_BEETWEEN(date1, date2)
	select months_between("20-5月-15", "10-1月-15") from dual;
EXTRACT(date FROM datetime)
	select extract(year/month/day from sysdate) from dual;
	select extract(hour from timestamp '2015-10-1 10:27:10') from dual;

4.9.4 转换函数

# 日期转换成字符
TO_CHAR(date[, fmt[, params]])
    YY YYYY YEAR
    MM MONTH
    DD DAY
    HH24 HH12
    MI SS
select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;
# 字符转换成日期
TO_DATE(date[, fmt[, params]])
select to_date('2020-5-11', 'YYYY-MM-DD') from dual;
# 数字转换成字符
TO_CHAR(number[, fmt])
    9 显示数字并忽略前面的0
    0 显示数字，位数不足用0补齐
    .或D 显示小数点
    ,或G 显示千位符
    $ 美元符号
    S 加正符号
select to_char(123456.789, '$99,999.99') from dual;
# 字符转成数字
    TO_NUMBER(char [, fmt])
    select to_number('$1,000', '$9999') form dual;

4.9.5 查询中使用函数

select substr(id, 7, 8) from tablename;
select replace(reg, "01","信息技术") from tablename;
select mod(age, 10) from tablename;
select extract(year from regdate) from tablename;
select * from tablename where extract(month from regdate) = 5