1.Oracle 工具:sqlplus Sqlplus / as sysdba Shutdown immediate(关闭数据库) Startup(启动数据库) 注意：数据库开启才可以进行操作 Select username,account_status from dba_users;(查询数据库中所有的用户名称与用户状态) Alter user scott identified by tiger account unlock;(scott用户解锁) Sqlplus scott/tiger(使用SCOTT 密码为tiger登陆ORACLE数据库) Connect scott/tiger (在SQL>中直接使用SCOTT用户连接数据库) Show all;(看所有变量) Set sqlprompt “_user>”(设置sqlplus环境下面的提示符用自身用户显示) 注意：如要变量下次重启生效必须把变量写入 /u01/app/oracle/product/10.2.0/db_1/sqlplus/admin/glogin.sql中 Host(回到系统) Help index（查看所有命令） Help+命令(类似于LINUX中的MAN,查看命令的格式与用法) Disconnect(关闭SQLPLUS工具但是不离开数据库) Describe emp(查看EMP表) Define(定义变量,常用与写脚本用) 如：DEFINE _EDITOR =”vi” (CHAR) (定义VI编辑器用于保存最后一条执行的SQL语句) Save /u01/app/oracle/aa.sql (保存SQL语句) Get /u01/app/oracle/aa.sql (调用保存的SQL语句) 2.select（select 查询语句是ORACLE中最常用的语句） DML语句包括（insert 写入，update改变，delect删除，merge两张表同步） 注意：merge常用在ETL（数据仓库）底下 DDL数据定义语句包括（create创建对象，alter修改，drop删除对象，rename改名，truncate删除整个表） TCL事务处理语句（commit提交立刻生效，rollback回滚，savepoint保存点，savepoint(保存点) DCL权限语句（grant赋予权限，revoke(收回权限) ORACLE的表称之为堆表（keep table） SELECT (1) writing basic SQL select statemanes
1. selecting all columns SCOTT>select * from dept; SCOTT>select dname,deptno,loc from dept;
Basic SELECT Statement SELECT *|{[DISTINCT] column|expression [alias],...} FROM table; 2. selecting specific columns SCOTT>select deptno,dname from dept; SCOTT>select ename,sal from emp;
3. writing SQL statements SQL statements are not case sensitive. SQL statements can be on one or more lines. Keywords cannot be abbreviated or split across lines. Clauses are usually placed on separate lines. Indents are used to enhance readability.
select * FROM EMP; SELECT * FROM emp;
4. Arithmetic Expressions An arithmetic expression can contain column names, constant numeric values, and the arithmetic operators.
+ - * / arithmetic operators in any clause of a SQL statement except in the FROM clause select ename,sal+300 from emp; Operator Precedence Multiplication(*) and division(/) take priority over addition(+) and subtraction(-). Operators of the same priority are evaluated from left to right. Parentheses(...) are used to force prioritized evaluation and to clarify statements.
select ename,sal,12*sal+100 from emp; select ename,sal,12*(sal+100) from emp;
5. defing a NULL value A null is a value that is unavailable, unassigned, unknown, or inapplicable. A null is not the same as zero or a blank space.
SCOTT>select ename,sal,comm from emp; SCOTT>select ename,sal,12*sal*comm from emp;
Arithmetic expressions containing a null value evaluate to null.(空值和任何数字计算等于空值) 6. defining a Column alias Renames a column heading. Is useful with calculations. Immediately follows the column name - there can also be the optional AS keyword between the column name and alias. Requires double quotation marks if it contains spaces or special characters or is case sensitive.(备注名)
SCOTT>select ename name,sal,12*sal*comm from emp; SCOTT>select ename name,sal,12*sal*comm as "Annual Salary" from emp;
7. Concatenation Operator Concatenates columns or character strings to other columns. Is represented by two vertical bars (||). Creates a resultant column that is a character expression.
SCOTT>select ename||job as "Employees" from emp; SCOTT>select ename||' is a '||job as "Employees" from emp; (||是连接2个列之间的符号)
8. Literal Character Strings A literal is a character, a number, or a date included in the SELECT list. Date and character literal values must be enclosed within single quotation marks. Each character string is output once for each row returned.
SCOTT>select ename ||' is a'||job as "Employee Details" from emp; 9. Duplicate Rows SCOTT>select deptno from dept; SCOTT>select distinct deptno from emp;（取唯一）
(2) Restrictng and sorting data SELECT *|{[DISTINCT] column|expression [alias],...} FROM table [WHERE condition(s)];
a) WHERE CLAUSE Restrict the rows returned by using the WHERE clause. The WHERE clause follows the FROM clause.
*where:resttricts the query to rows that meet a condition.