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;