集合操作

去除重復值：union?

不去除重復值：union?all

上下兩個查詢語句中，列的數量和類型要一致，不夠可以用空值或設置默認值做替代

select?employee_id,?job_id?from?employees

union?all

select?employee_id,?job_id?from?job_history;

例2：

select?employee_id,?job_id?from?employees

union

select?employee_id,?job_id?from?job_history;

查詢交集：intersect

select?employee_id,?job_id?from?employees

intersect

select?employee_id,?job_id?from?job_history;

查詢差集：minus

select?employee_id?from?employees

minus

select?employee_id?from?job_history;

上下兩個查詢語句中，列的數量和類型要一致，不夠可以用空值或設置默認值做替代

select?employee_id,?job_id,?salary?from?employees

union?all

select?employee_id,?job_id,?null?from?job_history;

?

select?employee_id,?job_id,?to_char(salary)?from?employees

union?all

select?employee_id,?job_id,?'no?salary'?from?job_history;

?

集合排序：?//排序語句只能寫在最后,?按照表1里面的列進行排序

select?employee_id,?job_id,?salary?from?employees

union?all

select?employee_id,?job_id,?null?from?job_history

order?by?salary;???

?

select?employee_id,?job_id,?null?from?job_history

union?all

select?employee_id,?job_id,?salary?from?employees

order?by?3;

DML（包括增刪改查??select?insert?update?delete）

insert:定義列名，values:賦值

SQL>?create?table?t1(x?int,?y?char(1),?z?date);

?

SQL>?insert?into?t1(x,?y,?z)?values?(1,?'a',?sysdate);?//?標準寫法

SQL>?insert?into?t1(x,?z,?y)?values?(2,?sysdate+1,?'b');

SQL>?insert?into?t1(x,?y,?z)?values?(1,?null,?sysdate);

SQL>?insert?into?t1(x,?z)?values?(2,?sysdate+1);//對于空值的列，可以省略此列

SQL>?insert?into?t1?values?(1,?null,?sysdate);

?

將員工表里的內容寫到表my_emp中

SQL>?create?table?my_emp?as?select?*?from?employees;??//子查詢

SQL>?create?table?my_emp?as?select?last_name,?salary?from?employees?where?department_id=50;?invalid?identifier

SQL>?create?table?avg_sal?as?select?department_id,?avg(salary)?avg_sal?from?employees?group?by?department_id;

SQL>?create?table?my_emp?as?select?*?from?employees?where?1=0;

SQL>?insert?into?my_emp?select?*?from?employees;

?

update:更新（修改）?列

SQL>?update?my_emp?set?salary=salary*1.1;

SQL>?update?my_emp?set?salary=salary*1.1?where?department_id=50;

SQL>?update?my_emp?set?salary=salary*1.1,?commission_pct=0.5?where?employee_id=197;

?

delete:刪除????

SQL>?delete?from?my_emp?where?employee_id=197;

SQL>?delete?from?my_emp?where?department_id=50;

SQL>?delete?from?my_emp;

?

子查詢：

SQL>?create?table?my_emp?as?select?*?from?employees;

SQL>?alter?table?my_emp?add(department_name?varchar2(30));

SQL>?update?my_emp?outer?set?department_name=(select?department_name?from?departments?where?department_id=outer.department_id);

?

update?(select?t1.department_name?as?aname,t2.department_name?bname?from?my_emp?t1?,departments?t2?where?t1.department_id=t2.department_id)?set?aname=bname;

練習：

在new_dept表中刪除沒有員工的部門

SQL>?create?table?my_dept?as?select?*?from?departments;

delete?from?my_dept?outer

where?not?exists

(select?1?from?my_emp

where?department_id=outer.department_id);

?

delete和truncate：

Dml（包括增刪改查）：不自動提交

ddl：一回車，就直接自動提交，還會把前面的dml也進行提交

delete truncate

語句類型 dml ddl

undo數據 產生大量undo數據 不產生undo數據

空間管理?????????????????不釋放 釋放

語法 where 刪除全部數據

?

DDL（create?alter?drop）

Char:定長

Vachar:可變長度?

字符串：

SQL>?create?table?t1(x?char(10),?y?varchar2(10));

SQL>?insert?into?t1?values('x',?'y'); ?

SQL>?select?dump(x),?dump(y)?from?t1;

數值：

SQL>?create?table?t1(x?number(5,2),?y?number(5));

SQL>?insert?into?t1?values?(123.45,?12345);

SQL>?insert?into?t1?values?(12.345,?12345);

SQL>?insert?into?t1?values?(12.345,?123.45);

SQL>?select?*?from?t1;?b

SQL>?insert?into?t1?values?(12.345,?112345);

日期時間：

SQL>?create?table?t1(a?date,?b?timestamp,?c?timestamp?with?time?zone,?d?timestamp?with?local?time?zone);

SQL>?insert?into?t1?values?(sysdate,?systimestamp,?systimestamp,?systimestamp);

SQL>?alter?session?set?time_zone='+9:00';

SQL>?select?*?from?t1;

修改表結構：

SQL>?alter?table?t1?add(e?char(10));

SQL>?alter?table?t1?drop(e);

SQL>?alter?table?t1?modify(d?not?null);

?

約束條件：

字段（列）：not?null,?check(salary>0)

行與行：primary?key,?unique

表與表之間：foreign?key

create?table?dept?(

????deptno?int?constraint?dept_deptno_pk?primary?key,

????dname?varchar2(20)?constraint?dept_dname_nn?not?null);

?

create?table?emp?(

????empno?int?constraint?emp_empno_pk?primary?key,

????ename?varchar2(20)?constraint?emp_ename_nn?not?null,

????email?varchar2(50)?constraint?emp_email_uq?unique,

????salary?int?constraint?emp_salary_ck?check(salary>0),

????deptno?int?constraint?emp_deptno_fk?references?dept(deptno))

?

SQL>?select?constraint_name,?constraint_type?from?user_constraints?where?table_name?in('DEPT',?'EMP');

?

SQL>?insert?into?emp?values?(100,?'abc',?'abc@123.com',?10000,?10);

insert?into?emp?values?(100,?'abc',?'abc@123.com',?10000,?10)

*

ERROR?at?line?1:

ORA-02291:?integrity?constraint?(HR.EMP_DEPTNO_FK)?violated?-?parent?key?not

found

?

?

SQL>?insert?into?dept?values?(10,?'sales');

?

1?row?created.

?

SQL>?insert?into?dept?values?(10,?'market');

insert?into?dept?values?(10,?'market')

*

ERROR?at?line?1:

ORA-00001:?unique?constraint?(HR.DEPT_DEPTNO_PK)?violated

?

?

SQL>?insert?into?dept?values?(20,?'market');

?

1?row?created.

?

SQL>?commit;

?

Commit?complete.

?

SQL>?insert?into?emp?values?(101,?'def',?'def@123.com',?10000,?20);

?

create?table?emp?(

????empno?int?constraint?emp_empno_pk?primary?key,

????ename?varchar2(20)?constraint?emp_ename_nn?not?null,

????email?varchar2(50)?constraint?emp_email_uq?unique,

????salary?int?constraint?emp_salary_ck?check(salary>0),

????deptno?int?constraint?emp_deptno_fk?references?dept(deptno)?on?delete?set?null)或者on?delete?cascade

?

instead?of?trigger視圖觸發器

?

序列：

SQL>?create?sequence?test_seq?increment?by?1?start?with?1?maxvalue?1000?nocycle?cache?20;

SQL>?create?table?t1(x?int?primary?key,?y?int);

SQL>?insert?into?t1?values?(test_seq.nextval,?11); 反復執行

SQL>?select?*?from?t1;

?

索引：

主鍵和唯一性約束自動創建索引：

SQL>?select?constraint_name,?constraint_type?from?user_constraints?where?table_name='EMPLOYEES';

SQL>?select?index_name,?index_type?from?user_indexes?where?table_name='EMPLOYEES';

SQL>?set?autot?on

SQL>?select?last_name?from?employees?where?employee_id=100; 走索引

SQL>?select?email?from?employees; 走索引

SQL>?select?last_name?from?employees?where?salary=2100; 全表掃描

SQL>?create?index?emp_salary_ix?on?employees(salary);

SQL>?select?last_name?from?employees?where?salary=2100; 走索引

SQL>?set?autot?off

轉載于:https://www.cnblogs.com/hxv-3670/p/7271911.html

總結

以上是生活随笔為你收集整理的《SQL查询语句4》的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。