Ddl語句，數(shù)據(jù)定義表 ，創(chuàng)建刪除。SQL ?結(jié)構(gòu)化查詢語句 ??DML數(shù)據(jù)操控表：對數(shù)據(jù)進行操作，如增刪查改

Grid control網(wǎng)絡(luò)控制 control 像是一個集成的管理工具。Bi做數(shù)據(jù)倉庫的應(yīng)用。

Entity實體集 ???每個實體集是表，每個關(guān)系是表

數(shù)據(jù)流程：建模----把用戶腦海里的變成er模型表述----關(guān)系數(shù)據(jù)庫中的表table model物理化-----在磁盤上進行配置。

查看hr用戶名下的表，解鎖hr用戶：

$ sqlplus / as sysdba（直接利用操作系統(tǒng)身份驗證）或SQL> conn / as sysdba在sql里切換身份

SQL> show user??進行身份確認(rèn)

SQL> select table_name from dba_tables where owner='HR';??????查看里面有多少表

SQL> select * from hr.employees;

SQL> alter user hr account unlock identified by hr;????進行解鎖并分配口令

$ sqlplus hr/hr或者SQL> conn hr/hr

SQL> show user

SQL> select * from tab;??

SQL> select * from employees;??????????自己名下的表就不用寫前綴

SQL> desc employees???????查看表結(jié)構(gòu)

?

使用sqlplus的全屏編輯功能：

$ echo $EDITOR

SQL> select * from hr.employees;

SQL> ed

SQL> / 執(zhí)行

?

查詢永遠(yuǎn)是行列的交叉。

Select last_name,salary ???確定列

From employees ??來自哪張表

Where department_id=90 ??確定行

Sql沒有大小寫之分，但一般語法關(guān)鍵字大寫，對象的名稱小寫

?

可以對查詢的結(jié)果，進行額外的運算

ANNUAL 列的別名，后加列名，可以寫as 也可以不寫

例：select last_name, salary*12*(1+COMMISSION_PCT) annual_salary from employees;

?

鏈接操作符|| ，為了讓輸出的信息可讀性更高，還可拼湊select語句

Grant select on hr.employees to user01;把用戶名下的表權(quán)限給user01

單引號是字符串的定界符。

Distinct去除重復(fù)值

Desc快速了解表結(jié)構(gòu)

Where語句進行行和列的查詢

字符串必須用單引號引起來 ????oracle的字符串區(qū)分大小寫 ????日期：格式敏感

Select sysdate from dual; ?dual 空表，保存語法的完整性

Select last-name fyrom employees where hire_date=to_date(‘2006-05-23’, ‘yyyy-mm-dd’)

To_date 進行格式轉(zhuǎn)換

基礎(chǔ)select語句：

SQL> select * from employees;

SQL> desc employees

SQL> select LAST_NAME, SALARY, COMMISSION_PCT from employees;

SQL> desc departments

SQL> select department_id, department_name from departments;

SQL> select distinct DEPARTMENT_ID from employees;

SQL> select last_name, salary*12*(1+commission_pct) total_salary, department_id from employees;

SQL> select first_name||', '||last_name from employees;

SQL> select first_name||', '||last_name fullname from employees;

?

練習(xí)：

輸出下列員工信息：

Eleni(first_name) Zlotkey(last_name) employeeid is ... at department .. total salary is …

使用連字符構(gòu)造語句：

SQL> select table_name from user_tables;

SQL> select 'grant select on hr.'||table_name||' to scott;' from user_tables;

?

SQL> spool /home/oracle/grant.sql

SQL> select 'grant select on hr.'||table_name||' to scott;' from user_tables;

SQL> spool off

$ vi /home/oracle/grant.sql ????去除沒用的行

SQL> @/home/oracle/grant.sql

單引號的處理：

SQL> select 'I'm teaher' from dual;

ERROR:

ORA-01756: quoted string not properly terminated

SQL> select 'I''m teaher' from dual;

SQL> select q'{I'm teaher}' from dual; []<>()都可以

where和order by

數(shù)字條件：

SQL> select salary from employees where employee_id=100;

字符串大小寫敏感：

SQL> select last_name, salary from employees where last_name='King';

SQL> select last_name, salary from employees where lower(last_name)='king';

SQL> select table_name, tablespace_name from user_tables where table_name='EMPLOYEES';

日期是格式敏感：

SQL> select ?sysdate from dual；

SQL> alter session set nls_date_format='RR-Mon-dd';

SQL> select ?sysdate from dual；

SQL> select last_name from employees where hire_date='2006-05-23';報錯

SQL> select last_name from employees where hire_date='23-MAY-06';

SQL> select last_name from employees where hire_date=to_date('2006-05-23', 'yyyy-mm-dd');

區(qū)間查詢：

SQL> select last_name from employees where salary>=3400 and salary<=4000;

SQL> select last_name from employees where salary between 3400 and 4000;

SQL> select last_name from employees where salary between 3000 and 5000 and department_id=50;

in：

SQL> select last_name from employees where department_id=30 or department_id=40 or department_id=50;

SQL> select last_name from employees where department_id in (30, 40, 50);

通配符：

SQL> select last_name, job_id from employees where job_id like '%\_MAN' escape '\';

null作為條件：

SQL> select last_name from employees where commission_pct is null;

SQL> select last_name from employees where commission_pct is not null;

and/or/not：

SQL> select last_name, job_id, department_id, salary from employees where job_id='SA_REP' or department_id=50 and salary>=8000;

SQL> select last_name, job_id, department_id, salary from employees where (job_id='SA_REP' or department_id=50) and salary>=8000;

?

轉(zhuǎn)載于:https://www.cnblogs.com/m4116/p/7246590.html

總結(jié)

以上是生活随笔為你收集整理的7月27的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。