1. 用户与表空间

1.1 用户

1.1.1 登录SQL Plus

​ 系统用户

sys,system 管理员级别用户 密码自己设置的
sysman 管理员级别用户    密码自己设置的
scott 普通用户
	---->密码默认为tigger

​ 使用系统用户登录

使用system用户登录
[username/password][@server][as sysdba | sysoper]
	system/root @orcl as sysdba
	orcl就是自己设置的服务名

1.1.2 查看登录用户

show user 命令   
		---->查看当前登录用户
dba_users 数据字典  
		---->数据字典是数据库提供的表，用于查看数据库的信息	
desc dba_users

1.1.3 启用scott用户

启用用户的语句
	alter user [username] account unlock;
启用scott用户的语句
	alter user scott account unlock;
使用scott用户登录SQL Plus
	connect scott/tigger

1.2 表空间

1.2.1 表空间概述

理解表空间

数据库与表空间
	表空间是数据库的逻辑存储空间，在数据库中开辟的一段空间，用于存放数据库的对象，一个数据库由多个表空间构成。
表空间与数据文件
	表空间是由一个或多个数据文件组成的。

表空间的分类

永久表空间
	表，视图，存储过程
临时表空间
	数据库操作的中间过程
UNDO表空间
	保存事务所修改的旧址，数据回滚 

1.2.2 查看用户的表空间

dba_tablespaces、user_tablespaces数据字典
	管理员级别
	desc dba_tablespaces
	select tablespace_name from dba_tablespaces;
	有如下表空间：
		SYSTEM
		SYSAUX
		UNDOTBS1
		TEMP
		USERS
		EXAMPLE
		
	普通用户级别
	desc user_tablespaces
	select tablespace_name from user_tablespaces;
	有如下表空间：
		SYSTEM
		SYSAUX
		UNDOTBS1
		TEMP
		USERS
		EXAMPLE
dba_users、user_users数据字典
	管理员级别
	desc dba_users
	select default_tablespace,temporary_tablespace from dba_users where username='SYSTEM';
		DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
		------------------------------ ------------------------------
			SYSTEM                         TEMP
	普通用户级别
	desc user_users
	select default_tablespace,temporary_tablespace from user_users where username='SCOTT';
		DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
		------------------------------ ------------------------------
			USERS                          TEMP
设置用户的默认或临时表空间
	----> 	alter USER username 
    ---->	DEFAULT | TEMPORARY  
    ---->	TABLESPACE tablespace_name;
    
    alter USER system DEFAULT TABLESPACE system;

1.2.3 创建、修改、删除表空间

创建表空间
	CREATE[TEMPORARY] TABLESPACE
	tablespace_name
	TEMPFILE | DATAFILE 'xx.dbf' SIZE xx ;
	
	创建永久表空间
	create tablespace test1_tablespace datafile 'test1file.dbf' size 10m;
	创建临时表空间
	create temporary  tablespace temptest1_tablespace tempfile 'tempfile1.dbf' size 10m;
	
	查看永久表空间路径
	desc dba_data_files
	select file_name from dba_data_files where tablespace_name = 'TEST1_TABLESPACE';
	查看临时表空间路径
	desc dba_temp_files
	select file_name from dba_temp_files where tablespace_name = 'TEMPTEST1_TABLESPACE';

修改表空间
	修改表空间的状态
		1.设置联机或脱机状态
			ALTER TABLESPACE tablespace_name ONLINE | OFFLINE;
			
			alter tablespace test1_tablespace offline;
			
			查看状态
			desc dba_tablespaces
			select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';
		2.设置只读或可读写状态
			ALTER TABLESPACE tablespace_name READ ONLY | READ WRITE;
			查看状态
			desc dba_tablespaces
			select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';
	修改数据文件
		1.增加数据文件
			ALTER TABLESPACE tablespace_name ADD DATAFILE 'xx.dbf' SIZE xx;
			
			alter tablespace test1_tablespace add datafile  'test2_file.dbf' size 10m;
			select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE';
		2.删除数据文件
			ALTER TABLESPACE tablespace_name DROP DATAFILE 'filename.dbf';
			
			alter tablespace test1_tablespace drop datafile  'test2_file.dbf';
			select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE';

删除表空间
	DROP TABLESPACE tablespace_name [ INCLUDING CONTENTS ]
	
	drop tablespace test1_tablespace；-- 删除表空间
	drop tablespace test1_tablespace including contents; -- 删除表空间和表空间下的数据文件

2. 管理表

2.1 认识表

基本存储单位
二维结构
行（记录）和列（域和字段）
约定
	1.每一列数据必须具有相同的数据类型
	2.列名唯一
	3.每一行数据的唯一性

2.2 数据类型

字符型
	CHAR(n){n_max=2000}、NCHAR(n){n_max=1000}
	VARCHAR2(n){n_max=4000}、NVARCHAR2(n){n_max=2000}
数值型
	NUMBER(p,s){p->有效数字，s->小数点后的位数}
	FLOAT(n)
日期型
	DATE{表示范围：公元前4712年1月1日到公元9999年12月31日}
	TIMESTAMP
其他类型
	BLOB
	CLOB

2.3 管理表

创建表
	基本语法
		CREATE TABLE table_name
		(
        	column_name datatype, ...
        )
	练习：创建用户信息表
		所需字段
			编号 用户名 密码 邮箱 注册时间
		字段的类型
			整数 字符   字符   字符   日期
		 create table userinfo
		(
 			id number(6,0),
 			username varchar2(20),
  			userpwd varchar2(20),
 			email varchar2(30),
			regdate date 
		);
		
		desc userinfo
修改表
	添加字段
		ALTER TABLE table_name ADD column_name datatype;
		eq:	alter table userinfo add remarks varchar2(500);
	更改字段数据类型
		ALTER TABLE table_name MODIFY column_name datatype;
		eq1: alter table userinfo modify remarks varchar2(400);
		eq2: alter table userinfo modify userpwd number(6,0);
	删除字段
		ALTER TABLE table_name DROP COLUMN column_name;
		eq: alter table userinfo drop column remarks;
	修改字段名
		ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;
		eq:  alter table userinfo rename column  email to new_email;
	修改表名
		RENAME table_name TO new_table_name;
		eq: rename userinfo to new_userinfo;
删除表
	TRUNCATE TABLE table_name; -- 删除表中的全部数据
	eq: truncate table userinfo;
	DROP TABLE table_name; --删除表的结构
	eq: drop table userinfo;

3. 操作表中的数据

3.1 添加数据

INSERT 语句
	INSERT INTO table_name
	[(column1,column2, ...)]
	VALUES(value1,value2, ...)
操作实例
	1.向表中所有字段添加值
		insert into userinfo values(1,'xxx','123','xxx@126.com',sysdate);-- sysdate：当前日期
	2.向表中指定字段添加值
		insert into userinfo (id,username,userpwd)values(2,'yyy','123');
	3.向表中添加默认值
		create table userinfo1(id number(6,0),regdate date default sysdate);
		insert into userinfo1 (id) values(1);

3.2 复制表数据

在建表时复制
	CREATE TABLE table_new AS SELECT column1, ... | * FROM table_old;
	eq1: create table userinfo_new as select * from userinfo;
	eq2: create table userinfo_new1 as select id,username from userinfo;
在添加时复制
	INSERT INTO table_new [(column1, ...)] SELECT column1, ... | * FROM table_old;
	eq1: insert into userinfo_new select * from userinfo; 
	eq2: insert into userinfo_new (id,username)select id,username from userinfo; 

3.3 修改数据

UPDATE语句
	 UPDATE table_name SET column1=value1, ... [WHERE conditions]
操作实例
	1.无条件更新
		update userinfo set userpwd='111111';
	2.有条件更新
		update userinfo set userpwd='123456' where username='xxx';

3.4 删除数据

DELETE语句
	DELETE FROM table_name [WHERE conditions];
操作实例
	1.无条件删除
		delete from userinfo;
	2.有条件删除
		delete from userinfo where username='yyy';

4. 约束

4.1 约束概述

约束的作用：
	定义规则
	确保完整性

4.2 非空约束

 1.在创建表时设置非空约束
 	CREATE TABLE table_name(
    	column_name datatype NOT NULL, ...
    );
    
    eq： create table userinfo_1(
			id number(6,0),
        	username varchar2(20) not null,
        	userpwd varchar2(20) not null
   		 );
 2.在修改表时添加非空约束
 	ALTER TABLE table_name  
    MODIFY	column_name datatype NOT NULL;
    eq: alter table userinfo_1 modify id number(6,0) not null;
 3.在修改表时去除非空约束
 	ALTER TABLE table_name  
    MODIFY	column_name datatype  NULL;
    eq: alter table userinfo_1 modify id number(6,0)  null;

4.3 主键约束

作用：确保表当中每一行数据的唯一性
	非空 唯一
一张表只能设计一个主键约束
主键约束可以由多个字段构成（联合合主键或复合主键）

在创建表时设置主键约束
	方式一： 
		CREATE TABLE table_name(
    		column_name datatype PRIMARY KEY, ...
    	);
    
   		 eq: create table userinfo_2(
				id number(6,0) primary key,
        		username varchar2(20),
        		userpwd varchar2(20) 
   		 	);
   	方式二：
   		CONSTRAINT constraint_name
   		PRIMARY KEY(column_name1, ...);
   		
   		eq: create table userinfo_3(
				id number(6,0) ,
        		username varchar2(20),
        		userpwd varchar2(20) ,
            	constraint pk primary key(id,username)
   		 	);

4.4 在修改表时添加主键约束

ADD CONSTRAINT constraint_nane PRIMARY KEY(column_name1, ...);
desc user_constraints

eq: alter table userinfo add constraint pk_id primary key(id);
	select constraint_name from user_constraints where table_name='USERINFO';

更改约束的名称
	RENAME CONSTRAINT old_name TO new_name;
		eq: alter table userinfo rename constraint pk_id to new_pk_id;
删除主键约束
	1.DISABLE | ENABLE CONSTRAINT constraint_name; --disable 禁用约束 enable 启用约束
		eq: alter table userinfo disable constraint new_pk_id;
			select constraint_name,status from user_constraints where table_name='USERINFO';-- 查看状态
	2.DROP CONSTRAINT constraint_name; -- 删除约束
		eq: alter table userinfo drop constraint new_pk_id;
	3.DROP PRIMARY KEY[CASCADE] 
		eq： alter table  userinfo_2 drop primary key;

4.5 在创建表时添加外键约束

CREATE TABLE table1
(column_name datatype REFERENCES
table2(column_name), ... ); 
--table1 从表  table2 主表
-- 设置外键约束时，主表的字段必须是主键
-- 主从表中相应的字段必须是同一个数据类型
--  从表中外键字段的值必须来自主表中的相应字段的值，或者为null值

eq: create table typeinfo
	( typeid varchar2(10) primary key,
    typename varchar2(20));
    
    create table typeinfo_f
    (id varchar2(10) primary key,
    username varchar2(20),
    typeid_new varchar2(20) references typeinfo(typeid));
    
    insert into typeinfo values(1,1);
    insert into typeinfo_f(id,typeid_new) values(1,1);

4.6 在创建表时设置外键约速

CONSTRAINT constraint_name FOREIGN KEY
(column_name) REFERENCES
table_name(column_name)[ON DELETE CASCADE];

	eq1: create table userinfo_f2
		(id varchar2(10) primary key,
		username varchar2(20),
		typeid_new varchar2(10),
		constraint fk_typeid_new foreign key(typeid_new) references typeinfo(typeid));

	eq2: create table userinfo_f3
		(id varchar2(10) primary key,
		username varchar2(20),
		typeid_new varchar2(10),
		constraint fk_typeid_new1 foreign key(typeid_new) references typeinfo(typeid) on delete cascade);

4.7 在修改表时添加外键约束

ADD CONSTRAINT constraint_name FOREIGN KEY
(column_name) REFERENCES
table_name(column_name)[ON DELETE CASCADE];

	eq：	create table userinfo_f4
		(id varchar2(10) primary key,
		username varchar2(20),
		typeid_new varchar2(10));
		
		alter table userinfo_f4
		add constraint fk_typeid_alter  foreign key(typeid_new) references typeinfo(typeid);
		
		alter table userinfo_f4
		add constraint fk_typeid_alter1  foreign key(typeid_new) references typeinfo(typeid) on delete cascade;

4.8 删除外键约束

1.DISABLE | ENABLE CONSTRAINT constraint_name;--禁用 | 启用约束
	eq： select constraint_name,constraint_type,status from user_constraints where table_name='USERINFO_F4';
		alter table userinfo_f4 disable constraint fk_typeid_alter;
		alter table userinfo_f4 enable constraint fk_typeid_alter;
2.DROP CONSTRAINT constraint_name; -- 删除约束
	eq: alter table userinfo_f4 drop constraint fk_typeid_alter;		

4.9 在创建表时设置唯一约束

作用：保证字段值的唯一性
唯一约束和主键约束的区别：
	主键字段值必须是非空的
	唯一约束允许有一个空值
	主键在每张表中只能有一个
	唯一约束在每张表中可以有多

在创建表时设置唯一约束
	1.CREATE TABLE table_name
	(column_name datatype UNIQUE, ... );
	
		eq: create table userinfo_u(
        		id varchar2(20) primary key,
            	username varchar2(20) unique,
            	userpwd varchar2(20)
        	);
     
     2.CONSTRAINT constraint_name UNIQUE(column_name);
     
    	 eq: create table userinfo_u1(
        		id varchar2(20) primary key,
            	username varchar2(20),
            	userpwd varchar2(20),
              	CONSTRAINT un_username UNIQUE(username)
        	);

4.10 在修改表时添加唯一约束

ADD CONSTRAINT constraint_name UNIQUE(column_name);

	eq：alter table userinfo_u1 add constraint un_userpwd_new unique(userpwd);

4.11 删除唯一约束

1.DISABLE | ENABLE CONSTRAINT constraint_name;--禁用 | 启用约束
	eq： select constraint_name,constraint_type,status from user_constraints where table_name='USERINFO_U1';
		alter table userinfo_u1 disable constraint un_username;
		alter table userinfo_u1 enable constraint un_username;
2.DROP CONSTRAINT constraint_name; -- 删除约束
	eq: alter table userinfo_u1 drop constraint un_username;

4.12 在创建表时设置检查约束

作用：表中的值更具有实际意义

在创建表时设置检查约束
	1.CREATE TABLE table_name
	( column_name datetype CHECK(expressions), ... );
	
		eq: create table userinfo_c(
        		id varchar2(10) primary key,
            	username varchar2(20) ,
            	salary number(5,0) check(salary>0)
        	);
			insert into userinfo_c values(1,'xxx',-50); -- error  违反检查约束条件
			insert into userinfo_c values(1,'xxx',50);
			
	2. CONSTRAINT constraint_name CHECK(expressions);
	
		eq: create table userinfo_c1(
        		id varchar2(10) primary key,
            	username varchar2(20) ,
            	salary number(5,0),
            	constraint ck_salary check(salary>0)
        	);
        	insert into userinfo_c1 values(1,'xxx',-50); -- error  违反检查约束条件
		    insert into userinfo_c1 values(1,'xxx',50);

4.13 在修改表时添加检查约束

ADD CONSTRAINT constraint_name CHECK(expressions);

	eq: create table userinfo_c2(
        		id varchar2(10) primary key,
            	username varchar2(20) ,
            	salary number(5,0)
        	);
        alter table userinfo_c2 add constraint ck_salary_new check(salary>0);
        insert into userinfo_c2 values(1,'xxx',-50); -- error  违反检查约束条件
		insert into userinfo_c2 values(1,'xxx',50);

4.14 删除检查约束

1.DISABLE | ENABLE CONSTRAINT constraint_name;--禁用 | 启用约束
	eq： select constraint_name,constraint_type,status from user_constraints where table_name='USERINFO_C2';
		alter table userinfo_c2 disable constraint ck_salary_new;
		alter table userinfo_c2 enable constraint ck_salary_new;
2.DROP CONSTRAINT constraint_name; -- 删除约束
	eq: alter table userinfo_c2 drop constraint ck_salary_new;

5. 查询语句

5.1 查询概述

基本查询语句
在sql plus中设置格式
查询表中的所有字段及指定字段
给字段设置别名
运算符和表达式
在select语句中使用运算符
带条件的查询
模糊查询
范围查询
对查询结果进行排序
case_when语句的使用
decode函数的使用

5.2 基本查询语句

SELECT [DISTINCT]  column_name1, ... | *
FROM table_name
[WHERE conditions]

5.3 在sql plus中设置格式

COLUMN column_name HEADING new_name  --设置字段显示别名
-- COLUMN可以简写成COL 

	eq: create table users(id varchar2(10) primary key,username varchar2(20),salary number(7,2));
		col username heading 用户名;
		select * from users;
		
COLUMN column_name FORMAT dataformat -- 设置字段格式
-- 字符类型只能设置显示的长度
-- 数值类型用‘9’代表一个数字

	eq: col username format a10; -- 10个字符长度 
		col salary format 9999.9;  
        col salary format $9999.9;   
 
 COLUMN column_name CLEAR -- 清除字段格式
 	
 	eq: col salary clear;
 		col username clear;

5.4 查询表中的所有字段及指定字段

查询所有字段
	SELECT * FROM table_name;
查询指定字段
	SELECT column1_name, ... FROM table_name;

5.5 给字段设置别名

SELECT column_name AS new_name , ... FROM table_name;
-- AS可以省略，用空格隔开原来的字段名和新字段名即可
	
	eq: select id as 编号 ,username as 用户名,salary as 工资 from users;

5.6 运算符和表达式

表达式 = 操作数 + 运算符
oracle中的操作数可以有变量、常量和字段
运算符
	算术运算符（+，-，*，/）
	比较运算符（>,>=,<,<=,=,<>）
	逻辑运算符（ and , or, not）

5.7 在select语句中使用运算符

使用算术运算符
	select  id,username,salary+200 from  users;
使用比较运算符
	select username from users where salary>800;
使用逻辑运算符
	select username from users where salary>800 and salary <2100;
	select username from users where salary>800 or salary <2100;

5.8 带条件的查询

单一条件的查询
	select salary from users where username='aaa';
	select username,salary from users where id=3;
多条件的查询
	select * from users where username='aaa' or salary>2000;
	select * from users where username='aaa' or (salary>800 and salary<=2000);
	
逻辑运算符的优先级：按 not 、 and 、 or 的顺序依次递减 
比较运算符的优先级高于逻辑运算符

5.9 模糊查询

LIKE 运算符
通配符的使用（_,%）
	一个_只能代表一个字符
	%可以代表0到多个任意字符
使用 LIKE查询
	select * from users where username like 'a%';
	select * from users where username like 'a_';
	select *  from users where username like '_a%';
	select *  from users where username like '%a%';

5.10 范围查询

BETWEEN ... AND
	select * from users where salary between 800 and 2000;
	select * from users where salary not  between 800 and 2000;
IN / NOT IN
	select * from users where username in ('aaa','bbb');
	select * from users where username not in ('aaa','bbb');

5.11 对查询结果进行排序

SELECT ...  FROM ... [WHERE ...] ORDER BY column1 DESC / ASC, ...
	eq: select * from users order by id desc;
		select * from users order by username desc,salary asc;

5.12 case_when语句

1.CASE column_name 
WHEN value1 THEN result1, ...
[ELSE result] END
	
	eq: select username,case username when 'aaa' then '计算机部门' when 'bbb' then '市场部门' else '其他部门' end as 部门 from users;
	
2.CASE  
WHEN column_name = value1
THEN result1 ...
[ELSE result] END

	eq： select username,case when username='aaa' then '计算机部门' when username='bbb' then '市场部门' else '其他部门'  end as 部门 from users;
		select username ,case when salary<800 then '工资低' when salary>3000 then '工资高' else '工资一般' end as 工资水平 from users;

5.13 decode函数的使用

decode (column_name,value,result1, ... ,defaultvalue)

	eq： select username,decode(username,'aaa','计算机部门'，'bbb','市场部门'，'其他') as 部门 from users;