Oracle学习----基础篇（一）----SQL语法之单表查询

SQL语法之单表查询

 --查询表所有数据
    select * from EMP t;
 --查询表中指定字段
    select empno,ename,job from emp;
 --查询结果的字段使用别名
    select empno as 编号,ename as 姓名,job as 职位 from emp;
 --连接符 select 字段名||'字符' from 表名
    select '编号：'||empno||',姓名：'||ename||',工作：'||job as 员工信息 from emp;
 --去除重复distinct
    select distinct job from emp;
 --排序
 --单字段排序select * from 表名 order by 字段名（asc--小到大，desc--大到小）
    select * from emp order by sal desc; 
 --多字段排序
    select * from emp order by sal desc,hiredate desc; 
 --字段的逻辑运算
    select ename,sal*12 as yearSal from emp;
 --使用where子句查询筛选select * from 表名 where 条件
 --单筛选条件(=,>,<,>=,<=,<>)
    select * from emp where deptno<>'20';
 --多筛选条件（and,or,like,is null,is no null,in,between,and..）
    select * from emp where job='MANAGER' and deptno<>'20';
 --模糊查询like %表示任意多个字符，_表示一个字符
    select * from emp where ename like '_L%';

SQL语法之函数

--单行函数
--特点一：不改变真实的数据，只是对数据做了进一步的修饰和处理
--select 字段名，函数名（字段名），... from 表名 where...
--(1)字符函数 initcap()首字母大写 (2)lower()小写（3）upper()大写
select initcap(job),lower(job),upper(job) from emp;
--数值函数 abs()取绝对值，ceil()取负值等
select abs(-24) from dual;
select ceil(-24) from dual;
--日期函数 months_between,next_day,last_day
select months_between('01-1月-2019','01-1月-2018')as months from dual;

--多行函数 group by
--max() 返回字段的最大值 ,min()返回字段的最小值,sum(),avg(),count() 返回表的记录数
select max(sal) from emp;
--转换函数
--to_number 将字符转化为数值
select to_number('123')+2 from dual;
--to_char 将数值或日期转换为字符
select to_char(123456,'L999,999,999')from dual;
select to_char(hiredate,'yyyy-MM-dd')from emp;
--其他函数
--nvl(字段名，新的值)
--如果字段值不为null，则用新值
select ename,nvl(comm,0) from emp;
--nvl2(字段名，处理一，处理二)
--如果字段的值不为null，执行处理一，为null执行处理二
select ename,nvl2(comm,sal+comm,sal) as money from emp;

SQL语法之分组筛选

--分组查询
--group by 分组字段
--查询全部员工信息
select * from emp;
--查询每个单位下的员工数量
select deptno,count(*) from emp group by deptno;
--注意1：使用了分组以后，在select语句中只能出现分组字段和多行函数
--注意2：使用了多字段分组后，先按照第一个字段进行分组，然后每个小组继续按照第二个字段继续分组，以此类推
--注意3：在where子句中，不允许出现多行函数
--分组查询每个单位下的员工数量且单位数量大于3
select deptno,count(*) from emp where count(*)>3 group by deptno;--无法实现爱你

--分组筛选
--having 条件
-- 分组查询每个单位下的员工数量且单位数量大于3
select deptno,count(*) from emp group by deptno having count(*)>3; 
--注意1:having必须和group by组合使用，不允许单独使用
--注意2：where和having的区别：
--查询单位id为20或30的员工信息且进行分组查询每个单位下的员工数量且单位数量大于3
select deptno,count(*) from emp group by deptno having count(*)>3 and deptno in ('20','30'); 
select deptno,count(*) from emp where deptno in('20','30') group by deptno having count(*)>3; 

--区别
--where子句不允许出现多行函数，而having允许
--where的查询效果要高于having
--结论
--where进行字段级别的筛选，having进行多行函数的筛选

--SQL语句执行顺序
--  from->where->group by->select->having->order by

SQL语法之表数据的增删改

--增加数据
--insert into 表名 values(值1,值2,值3.....);
--insert into 表名(字段1,字段2,....) values(值1,值2,值3.....);

--删除数据
--delete from 表名;  不加where慎用 删除整张表
--truncate table 表名;  不加where慎用 删除数据，表留下
--delete from 表名 where 条件;  （常用） 

--更新数据
--update 表名 set 字段名=新的值,字段名=新的值... where 条件;

--表级别的备份
--全部备份
create table 新表名 as select * from 旧表 where 条件;
--部分备份
--create table 新表名 as select 字段1,字段2,... from 旧表 where 条件

SQL语法之用户及表空间

一个数据库可以有多个表空间，一个表空间里可以有多个表的物理空间；可以指定表空间的大小位置等。

-- 1、新建临时表空间uer_temp
create temporary tablespace uer_temp
tempfile 'C:\Oracle\oradata\orcl\uer_temp.dbf'
size 50M
AUTOEXTEND ON NEXT 50M maxsize 20480M--自动扩展大小且设置最大空间
extent management local;

-- 2、创建aspnet表空间
 --aspnet_jygl表空间用来存放Oracle的membership的相关内容
CREATE TABLESPACE aspnet_jygl 
logging
DATAFILE 'C:\Oracle\oradata\orcl\aspnet_jygl.dbf' 
SIZE 512M
AUTOEXTEND ON NEXT 64M 
EXTENT MANAGEMENT LOCAL;

--3、创建aspnet用户并授权
CREATE USER aspnet IDENTIFIED BY "aspnet"
default tablespace aspnet_jygl
temporary tablespace uer_temp;

GRANT CONNECT,RESOURCE,DBA TO aspnet;
GRANT UNLIMITED TABLESPACE TO aspnet_jygl;--直接授权用户账号密码永久有效

--4、删除用户(与该用户有关的数据全部丢失)
drop user aspnet cascade;

--5、更改用户默认表空间
alter database default tablespace 默认表空间名

--6、删除表空间
drop tablespace aspnet_jygl including contents and datafiles;
drop tablespace uer_temp including contents and datafiles;

--7、表空间改名
alter tablespace 原表空间名 rename to 新表空间名

--解决用户账号密码过期的问题（一定要设置，生产库新建的用户密码有效期为180天，如不及时更换，将导致数据库无法连接）
--（1）查看用户的profile是哪个，一般是default；
select username,profile from dba_users;
--（2）查看指定概要文件（如default）的密码有效期设置
select * from dba_profiles s where s.profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';
--(3)将密码有效期由默认的180天修改为无限期
alter profile default limit PASSWORD_LIFE_TIME unlimited;