oracle基本操作

优化

SQL在解析where的时候，是从右至左解析的，所以：and时候应将易假的值放在右侧
如果有分组的话，where是先过滤再分组，而having是先分组再过滤，where是数据量打有优势

常用操作

关键字顺序

select 
from table 
where
group by
having
order by

表达式

SQL> select ename,job,sal "涨前薪水" ,
2  decode(job,'PRESIDENT',sal+1000,
3             'MANAGET',sal+800,
               ...可写多个
4             sal+400) as "涨后薪水"
5  from emp;

显示转换
时间函数
- 例：select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
- 例：select to_char(sysdate,'day') from dual;
- 例：select (sysdate-hiredate)/30 from emp;
数学函数
- round: 四舍五入例：round(45.926,2) 45.93
- trunc: 截断例: trunc(45.926,2) 45.92
- mod: 求余例: mod(1600,300) 100
group by

select列表中所有没有包含再组函数中的列，都必须在group by的后面出现

order by 排序
- 默认asc升序，desc降序
转义单引号本身:
- select 'hello''world' from dual
模糊查询
like '%'匹配任意多个字符
like '_'匹配一个字符
比较运算
- = 等于 >大于
- = 大于等于
- != 不等于
- between...and
算数运算
- 乘除的优先级高于加减
- 优先级相同时，按照从左至右运算
- 可以使用括号改变优先级
修改上一条出错的SQL
- num 代表哪一行出错
- c /错误关键字/正确关键字
- 在使用"/"来执行上一次的语句

清屏: `host clear;`
管理员登录Oracle数据库`sqlplus / as sysdba`
查看用户`select username from dba_users;`
更改密码`alter user 用户名 identified by 新密码；`
远程登录`sqlplust 用户名/密码@//IP地址/实例名 sqlplus 例: scott/11@//192.168.16.46/orcl`

常见出错

+ the account is locked意思是账户被锁定了，这种情况需要解锁
  - 使用指令alter user USERNAME account unlock进行解锁即可

设置tns监听

lsnrclt start/stop
// 如果上述操作失败:
/*
切换到root用户
hostname oracle
在切换到oracle用户
lsnrclt start
*/

设置字符集和日期

设置日期:  alter session set NLS_DATE_FORMAT='yyyy-mm-dd';
设置带有时间日期:  alter session set NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';
设置Oracle服务端的字符集
	设置数据库字符集
	SQL> conn /as sysdba;
	SQL> shutdown immediate;
	SQL> startup mount;
	SQL> alter system enable restricted session;
	SQL> alter system set job_queue_processes=0;
	SQL> alter system set aq_tm_processes=0;
	SQL> alter database open;
	SQL> alter database character set internal_use ZHS16GBK;
	SQL> alter system set nls_language = 'AMERICAN' scope = spfile;
	SQL> alter system set nls_territory = 'AMERICA' scope = spfile;
	SQL> shutdown immediate;
	SQL> startup;

	修改之后的结果是:
	SQL> select userenv('language') from dual;

	USERENV('LANGUAGE')
	----------------------------------------------------
	AMERICAN_AMERICA.ZHS16GBK
	
	NLS_LANG环境变量是oralce客户端字符集配置
    [oracle@deng ~]$ export NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"

常用搜索

过虑函数:  nvl(a,b) 如果a为NULL,函数返回b
判断是否是空置:  使用`is`和`is not`

基本操作

1.输入：sqlplus /nolog //进入oralce控制台
2.输入：conn /as sysdba //以管理员权限登录
3.输入：create user abc identified by 123456; //创建用户名adc密码123456
4.输入：grant dba to abc; //授予DBA权限

1.查看所有用户：
select * from dba_users; 
select * from all_users; 
select * from user_users;

2.查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：
select * from dba_sys_privs; 
select * from user_sys_privs; (查看当前用户所拥有的权限)

3.查看角色(只能查看登陆用户拥有的角色)所包含的权限
sql>select * from role_sys_privs;

4.查看用户对象权限：
select * from dba_tab_privs; 
select * from all_tab_privs; 
select * from user_tab_privs;

5.查看所有角色： select * from dba_roles;

6.查看用户或角色所拥有的角色：
select * from dba_role_privs; 
select * from user_role_privs;

可以起别名并且查询中带有基本运算：select xxx as name,sal, sal*xxx as xxx from xxx;

数据库管理员用户:
	用户名:sys
	密	码:sys
	
	
1. Oracle数据库启动

	第一步: 通过数据库管理员登录Oracle数据库
	[oracle@localhost ~]$ sqlplus / as sysdba 在本地登录所以不需要用户名和密码
	[oracle@localhost ~]$ sqlplus sys/sys as sysdba 用户和密码登录管理员权限

	[oracle@localhost ~]$ sqlplus / as sysdba;
	第二步: 启动Oracle数据库
	SQL> startup
	ORACLE instance started.

	Total System Global Area  826753024 bytes
	Fixed Size		    2217872 bytes
	Variable Size		  524290160 bytes
	Database Buffers	  293601280 bytes
	Redo Buffers		    6643712 bytes
	Database mounted.
	Database opened.
	SQL> 

	第三步: 启动监听服务
	SQL> quit
	[oracle@localhost ~]$ lsnrctl start


2. Oracle数据库关闭

	
	第一步: 通过数据库管理员登录Oracle数据库
	[oracle@localhost ~]$ sqlplus / as sysdba;
	
	第二步: 关闭Oracle数据
	SQL> shutdown immediate 立即关闭
	Database closed.
	Database dismounted.
	ORACLE instance shut down.
	SQL> 

	第三步: 关闭监听服务
	SQL> quit
	[oracle@localhost ~]$ lsnrctl stop 

3. sqlplus登录Oracle数据库

	前提条件是Oracle数据库必须启动
	
	3.1 使用sqlplus登录Oracle数据库
	[oracle@localhost ~]$ sqlplus 
	Enter user-name: scott
	Enter password: 
	
	3.2 使用sqlplus登录Oracle数据库
	[oracle@localhost ~]$ sqlplus  scott/11

	3.3 远程登录
	格式: sqlplust 用户名/密码@//IP地址/实例名
	[oracle@localhost admin]$ sqlplus scott/11@//192.168.16.46/orcl

	3.4 解锁用户
	[oracle@localhost admin]$ sqlplus / as sysdba
	SQL> alter user scott account unlock;
	
	3.5 修改用户密码
	SQL> alter user scott identified by 11;
	User altered.

	3.6 查看当前语言环境
	SQL> select userenv('language') from dual;

	USERENV('LANGUAGE')
	----------------------------------------------------
	AMERICAN_AMERICA.WE8MSWIN1252

	SQL> 

4. sqlplus基本操作

	前提条件是使用scott用户登录
	[oracle@localhost ~]$ sqlplus scott/11


	4.1 显示当前用户
	SQL> show user 
	USER is "SCOTT"
	SQL> 

	4.2 查看当前用户下的表
	SQL> select * from tab;

	TNAME			       TABTYPE	CLUSTERID
	------------------------------ ------- ----------
	BONUS			       TABLE
	DEPT			       TABLE
	EMP			       TABLE
	SALGRADE		       TABLE

	4.3 查看员工表的结构
	SQL> desc emp;

	4.4 设置行宽
	SQL> set linesize 140

	4.5 设置页大小
	SQL> set pagesize 100

	4.6 设置员工ename的列宽
	SQL> col ename for a8
	
	4.7 设置薪水的列宽
	SQL> col sal for 9999

	
5. scott方案

	grade: 薪水等级
	losal: 最低薪水
	hisal: 最高薪水
	
	SQL> select * from salgrade ;

		 GRADE	LOSAL	   HISAL
	---------- ---------- ----------
		 1	  700	    1200
		 2	 1201	    1400
		 3	 1401	    2000
		 4	 2001	    3000
		 5	 3001	    9999
		 
		 
	deptno: 部门编号
	dname: 部门名称
	loc: 部门位置
	SQL> select * from dept;

		DEPTNO DNAME	  LOC
	---------- -------------- -------------
		10 ACCOUNTING	  NEW YORK
		20 RESEARCH	  DALLAS
		30 SALES	  CHICAGO
		40 OPERATIONS	  BOSTON
		 3 3NAME	  3loc
		 5 5name	  5loc

	6 rows selected.

	empno: 员工编号
	ename: 员工姓名
	job:   员工工种
	mgr:   员工上司
	hiredate: 雇佣日期
	sal:	员工薪水
	comm: 员工奖金
	deptno: 员工部门编号
	
	SQL> select * from emp;

     EMPNO ENAME    JOB 	     MGR HIREDATE    SAL       COMM	DEPTNO
	---------- -------- --------- ---------- --------- ----- ---------- ----------
      7369 SMITH    CLERK	    7902 17-DEC-80   800		    20
      7499 ALLEN    SALESMAN	    7698 20-FEB-81  1600	300	    30
      7521 WARD     SALESMAN	    7698 22-FEB-81  1250	500	    30
      7566 JONES    MANAGER	    7839 02-APR-81  2975		    20
      7654 MARTIN   SALESMAN	    7698 28-SEP-81  1250       1400	    30
      7698 BLAKE    MANAGER	    7839 01-MAY-81  2850		    30
      7782 CLARK    MANAGER	    7839 09-JUN-81  2450		    10
      7788 SCOTT    ANALYST	    7566 19-APR-87  3000		    20
      7839 KING     PRESIDENT		 17-NOV-81  5000		    10
      7844 TURNER   SALESMAN	    7698 08-SEP-81  1500	  0	    30
      7876 ADAMS    CLERK	    7788 23-MAY-87  1100		    20
      7900 JAMES    CLERK	    7698 03-DEC-81   950		    30
      7902 FORD     ANALYST	    7566 03-DEC-81  3000		    20
      7934 MILLER   CLERK	    7782 23-JAN-82  1300		    10

	
6. 设置Oracle服务端的字符集

	设置数据库字符集
	SQL> conn /as sysdba;
	SQL> shutdown immediate;
	SQL> startup mount;
	SQL> alter system enable restricted session;
	SQL> alter system set job_queue_processes=0;
	SQL> alter system set aq_tm_processes=0;
	SQL> alter database open;
	SQL> alter database character set internal_use ZHS16GBK;
	SQL> alter system set nls_language = 'AMERICAN' scope = spfile;
	SQL> alter system set nls_territory = 'AMERICA' scope = spfile;
	SQL> shutdown immediate;
	SQL> startup;

	修改之后的结果是:
	SQL> select userenv('language') from dual;

	USERENV('LANGUAGE')
	----------------------------------------------------
	AMERICAN_AMERICA.ZHS16GBK
	
	NLS_LANG环境变量是oralce客户端字符集配置
    [oracle@deng ~]$ export NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"

posted on 2021-05-25 11:19 lodger47 阅读(56) 评论(0) 收藏举报

刷新页面返回顶部

lodger47

oracle基本操作

优化

常用操作

常见出错

设置tns监听

设置字符集和日期

常用搜索

基本操作

公告

导航