2022年小布老师SQL学习笔记

《2022年小布老师SQL学习笔记》由会员分享，可在线阅读，更多相关《2022年小布老师SQL学习笔记（36页珍藏版）》请在金锄头文库上搜索。

1、Liao SQL 基础介绍 Oracle 11g SQL Fundamentals Training Introduction 1、primary key 主键 表中的唯一的标示非空2、foreign key 外键 在本表中引用的另外一张表的主键3、ER 实体关系模型4、SQL 的分类4.1、Data manipulation language(DML)数据操作语言Select 查询数据Insert 插入数据Update 更新数据Delete 删除数据Merge 合并数据4.2、Data definition language(DDL)数据定义语言Create 创建表Alter 修改表Drop

2、 删除表Rename 重命名表Truncate 截取Comment 4.3、Data control language(DCL)数据控制语言Grant Revoke 取消4.4、Transaction control 事物控制Commit 提交Rollback 回滚Savepoint SQL show user -查看当前用户USER is SYS desc dba_users - 查看所有用户SQL set lines 200 SQL select username,account_status from dba_users; USERNAME ACCOUNT_STATUS - - PERF

3、STAT OPEN HR OPEN OE OPEN SH OPEN 精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 1 页，共 36 页OPS$ORACLE OPEN USER01 OPEN DIP EXPIRED & LOCKED IX EXPIRED & LOCKED MDDATA EXPIRED & LOCKED PM EXPIRED & LOCKED BI EXPIRED & LOCKED SQL select table_name from user_tables; -查看存在的表-字符串和日期型是左对齐，数字型是右对齐，表头是大写。SQ

4、L select * from departments; DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID - - - - 10 Administration 200 1700 20 Marketing 201 1800 30 Purchasing 114 1700 01-Restricting Data Using the SQL SELECT Statement 1、算数表达式+ - * / 可以用于除 FROM 子句的其他子句SQL select last_name,salary,salary + 300 from employee

5、s; LAST_NAME SALARY SALARY+300 - - - Walsh 45331.68 45631.68 Feeney 45190.56 45490.56 107 rows selected. -算数表达式中有NULL值结果为 NULL -和字符串做运算时，结果为字符串。2、别名用双引号括起来( 不能用于 WHERE 子句)select last_nameas Name ,commission_pct as Comm from employees; Name Comm - - Ozer .25 Bloom .2 Fox .2 Order by 中可以使用别名和指定序号精选学习资

6、料 - - - - - - - - - 名师归纳总结 - - - - - - -第 2 页，共 36 页3、链接| 将两个字段连起来显示-两个字段中插入空格select last_name | | job_id as Employees from employees; Employees - OConnell SH_CLERK Grant SH_CLERK Whalen AD_ASST select last_name | is a |job_id as Employees from employees; Employees - OConnell is a SH_CLERKGrant is a

7、 SH_CLERK; Whalen is a AD_ASST q可以输出“” 单引号(实验证明应该是输出一段自定义字符) 4、distinct查不同的值（范围是整个 SELECT 后的所有字段）select distinctdepartment_id from employees; 02-Restricting and Sorting Data 补充 where 子句：1）子句内的需要比较的字符需要用单引号引起来（比如 where xxx=123） ，而且字 符是区分大小写 的。2）子句如果比较的是日期字符（默认的格式是日-月-年） ，需要注意日期格式。3）运算符精选学习资料 - - - -

8、- - - - - 名师归纳总结 - - - - - - -第 3 页，共 36 页例子：Between and: select emp_name,sal where sal between 2500 adn 3000; Select emp_name,sal where emp_name where emp_name between tony adn suen 字符也可以比较，A-Z，这样比较Like 通配符% 代表 0 或者多个字符_ 代表 1 个字符Select emp_name from emp where empname like 张 % 4）IS NULL / IS NOT NUL

9、L 字段是否为空WHERE 子句里判断5）逻辑运算符AND OR NOT 1、Escape 字符转义SQL select last_name,job_id from employees 2 where job_id like %SA_% escape ; escape 来定义转义字符，也可以是| 或者其他的-escape 是将斜线设置为转义字符，把下划线转义成了一个普通的字符精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 4 页，共 36 页LAST_NAME JOB_ID - - Russell SA_MAN Partners SA_MAN Er

10、razuriz SA_MAN 2、SQL 执行优先级精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 5 页，共 36 页补充：排序 ORDER BY 默认是升序， DESC是降序。ORDER BY 子句里可以使用别名， WHERE ,group by , having 子句不能使用Order by N 后面可以自己加数字，代表SELECT 第 N 个字段，但不值得推荐。Order by 后面可以跟多个字段， 表示先按照第一个字段先排序，然后再第一个字段中相同的值中，第二例再排序。如果有 NULL值，升序是放到最后，降序是放到开始。但也可以自己改变：

11、表的别名：另外：1、 form 子句的别名和国际标准不一样：不能加AS ，其他子句可以。2、 表的别名设置后，其他地方使用表就只能用别名。列的别名则不用这样Where 子句不能包含多组函数，而且3、替换变量&和& 简单的字符串的替换；几乎可以适用所有子句是客户端软件的功能SQL select last_name,salary from employees 2 where employee_id= &employee_num ; 如是字符串和日期一定要加单引号Enter value for employee_num: 123 old 2: where employee_id=&employee_

12、num new 2: where employee_id=123 LAST_NAME SALARY - - Vollman 50161.44 双&替换变量后， 同一个变量， 只用输入一次。精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 6 页，共 36 页4、Define 定义变量（适合用在批处理里面）SQL define employee=200SQL select employee_id,last_name from employees 2 where employee_id= &employee ; old 2: where employee

13、_id=&employee new 2: where employee_id=200 EMPLOYEE_ID LAST_NAME - - 200 Whalen Set verify off 关闭提示Set verify on 打开提示03-Using Single-Row Functions to Customize Output单行函数1 、函数精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 7 页，共 36 页2、单行函数 ：字符串函数，数值函数，日期函数，转换函数conversion ，通用函数 general 2.1、字符串函数Lower

14、字符变大写Upper 字符变小写Initcap 首字大写2.2、字符串操作函数SQL select concat(12345,67890) from dual;-将两个字符串连接起来CONCAT(12 - 1234567890 SQL select substr(123456789,1,5) from dual; -截取 1 到 5 喂SUBST - 12345 SQL select substr(123456789,5) from dual;-截取 5 位以后的SUBST - 56789 SQL select length(1234567890) from dual; - 计算字符串的长度L

15、ENGTH(1234567890) - 10 SQL select instr(1234567890,0) from dual; - 查看 0 所在的位置INST R(1234567890,0) - 精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 8 页，共 36 页10 SQL select lpad(12345,10,*) from dual; - 在左侧添加指定的字符LPAD(1234 - *12345 SQL select rpad(12345,10,*) from dual; - 在右侧添加指定的字符RPAD(1234 - 12345*

16、SQL select replace(1234567890,1,0) from dual; - 替换字符REPLACE(1 - 0234567890 SQL select trim(1 from 1234567890) from dual; - 去除字符TRIM(1F - 234567890 2.3、数值函数2.3.1、Round( 数值 ,保留小数点的位数 ) 0 为小数点位正值向右移，负值向左移，进行四舍五入 。精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 9 页，共 36 页2.3.2、 trunc ( 数值 ,保留小数点的位数 ) 0 为

17、小数点位正值向右移，负值向左移，直接去掉后面的值2.3.3、Mod( 数值，除数 )取余数mod(1600,500) 1600/500余 100 经常用于判断数值的奇偶mod(数值， 2) 2.4、日期函数date Sysdate显示系统时间SQL select sysdate from dual; SYSDATE - 2012-02-22:13:50:30 Last_day ( 日期 ) 显示当月的最后一天SQL Select last_day (20120201) from dual; LAST_DAY(20120201 - 2012-02-29:00:00:00 Add_months(

18、日期, 增加的月数 ) SQL Select add_months (20120222,1) from dual;ADD_MONTHS(2012022 - 2012-03-22:00:00:00 精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 10 页，共 36 页04-Using Conversion Functions and conditional Expressions 转换函数1、To_char 日期转字符SQL select employee_id,to_char(hire_date,YYYY-MM-DD) hired 2 from e

19、mployees 3 where last_name=Higgins; EMPLOYEE_ID HIRED - - 205 1994-06-07 2、加 fm 去掉前导字符SQL select employee_id,to_char(hire_date,fmYYYY-MM-DD) hired 2 from employees 3 where last_name=Higgins; EMPLOYEE_ID HIRED - - 205 1994-6-7 显示时间SQL select employee_id,to_char(hire_date,YYYY-MM-DD HH24:MI:SS ) hired

20、 2 from employees 3 where last_name=Higgins; EMPLOYEE_ID HIRED - - 205 1994-06-07 00:00:00 3、To_char 数值转字符SQL select to_char(salary,$99,999.00) SALARY 2 from employees 精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 11 页，共 36 页3 where last_name=Ernst; SALARY - $49,530.72 4、To_number 字符转数值SQL select t

21、o_number(-$12,345.67,$99,999.99) from dual; TO_NUMBER(-$12,345.67,$99,999.99) - -12345.67 5、To_date 字符转日期SQL select to_date(July 4, 2007,Month DD, YYYY) FROM dual; TO_DATE(JULY4,2007 - 2007-07-04:00:00:00 6、加 fx 为精准配备SQL select to_date(July 4, 2007,fxMonth DD, YYYY) FROM dual; select to_date(July 4,

22、 2007,fxMonth DD, YYYY) FROM dual ERROR at line 1: ORA-01858: a non-numeric character was found where a numeric was expected 7、显示系统当前时间sysdate SQL select to_char(sysdate,YYYY-MM-DD HH24:MI:SS) sysdata from dual;SYSDATA - 2012-02-20 15:22:54 精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 12 页，共 36 页2

23、.、General 通用函数2.1、NVL 判断函数计算一年的工资并且加上分红NVL(commission_pct,0) NVL对括号中第一个参数进行判断，如第一个参数为非NULL则显示其本身，如第一个参数为NULL则显示为 0 2.2、NVL2 判断函数 2 精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 13 页，共 36 页NVL(commission_pct, SAL+COMM , SAL ) NVL对括号中第一个参数进行判断， 如第一个参数为非NULL则显示第二个参数，如第一个参数为NULL则显示第三个参数。2.3、NULLIF NULL

24、IF将括号中的两个参数进行比较，如不等显示第一个参数，如相等显示为NULL 精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 14 页，共 36 页2.4、COALESCE 将括号中的参数，自左向右进行逐个判断，如第一个参数为非NULL，则显示其本身，判断结束，如第一个参数为NULL，则对下一个参数进行判断，以此类推，如前面的参数都为NULL，则显示 NULL。2.5、CASE 根据部门的不同来计算工资2.6、DECODE 是 oracle 特有的函数，等同于CASE ，但写法简单。精选学习资料 - - - - - - - - - 名师归纳总结 -

25、- - - - - -第 15 页，共 36 页05-Reporting Aggregated Data Using the Group Functions组函数Where 子句不能用列别名，在1、Having 过滤 group by 的结果SELECT column,group_function FROM table WHERE condition GROUP BY group_by_expression HAVING group_condition ORDER BY column ; 字句的执行次序06-Displaying Data from Mutiple Tables 从多表查询数据

26、1、JOIN 的语法-国际标准SELECT table1.column, table2.column FROM table1 NATURAL JOIN table2 | JOIN table2 USING (volumn_name) | JOIN table2 ON (table1.column_name = table2.column_name) | LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name) | CROSS JOIN table2; -ORACLE 标准SELECT table

27、1.column, table2.column FROM table1,table2 WHERE table1.column1 = table2.column2; 精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 16 页，共 36 页2、别名SQL Select employee_id,last_name,e.department_id,department_name 2 From employees e,departments d 3 Where e.department_id = d.department_id; MPLOYEE_ID LAST

28、_NAME DEPARTMENT_ID DEPARTMENT_NAME - - - - 108 Greenberg 100 Finance 111 Sciarra 100 Finance 112 Urman 100 Finance 113 Popp 100 Finance 109 Faviet 100 Finance 206 Gietz 110 Accounting 205 Higgins 110 Accounting 106 rows selected. 3、NATURAL JOIN 自然连接(和 using 不能共存 )系统自动将不同表中的相同的列进行自然连接-国际标准SQL Select

29、 department_id,department_name,location_id,city 2 From departments 3 Natural join locations; DEPARTMENT_ID DEPARTMENT_NAME LOCATION_ID CITY - - - - 270 Payroll 1700 Seattle 20 Marketing 1800 Toronto 40 Human Resources 2400 London 80 Sales 2500 Oxford 70 Public Relations 2700 Munich 27 rows selected.

30、 -oracle标准SQL Select d.department_id,d.department_name,d.location_id,l.city 2 From departments d,locations l 3 Where d.location_id=l.location_id ; DEPARTMENT_ID DEPARTMENT_NAME LOCATION_ID CITY - - - - 270 Payroll 1700 Seattle 20 Marketing 1800 Toronto 40 Human Resources 2400 London 80 Sales 2500 Ox

31、ford 70 Public Relations 2700 Munich 精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 17 页，共 36 页27 rows selected. 4、USING 指定不同表中的某一列进行连接（只要求名字相同， 不要求数据类型相同）-国际标准SQL Select employee_id,last_name,location_id,department_id 2 From employees join departments 3 Using (department_id) ; （括号注意要加）EMPLOYEE_ID L

32、AST_NAME LOCATION_ID DEPARTMENT_ID - - - - 108 Greenberg 1700 100 111 Sciarra 1700 100 112 Urman 1700 100 113 Popp 1700 100 109 Faviet 1700 100 206 Gietz 1700 110 205 Higgins 1700 110 106 rows selected. 国际标准补充：如果 using 语句中的列， where 也使用了这列，那么where 子句中不要加前缀。-oracle标准SQL Select d.location_id, d.city,l.

33、department_id 2 From locations d,departments l 3 Where d.location_id = l.location_id ; LOCATION_ID CITY DEPARTMENT_ID - - - 1700 Seattle 270 1800 Toronto 20 2400 London 40 2500 Oxford 80 2700 Munich 70 精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 18 页，共 36 页27 rows selected. 5、ON 连接两个表的列名可以不同，但内容要

34、相同SQL Select e.employee_id,e.last_name,e.department_id,d.location_id 2 From employees e join departments d 3 On(e.department_id=d.department_id) ; EMPLOYEE_ID LAST_NAME DEPARTMENT_ID LOCATION_ID - - - - 108 Greenberg 100 1700 111 Sciarra 100 1700 112 Urman 100 1700 113 Popp 100 1700 109 Faviet 100 1

35、700 206 Gietz 110 1700 205 Higgins 110 1700 106 rows selected. 5.1、多表进行连接SQL select employee_id,city,department_name 2 from employees e 3 join departments d 4 on d.department_id=e.department_id 5 join locations l 6 on d.location_id=l.location_id; EMPLOYEE_ID CITY DEPARTMENT_NAME - - - 191 South San

36、Francisco Shipping 192 South San Francisco Shipping 193 South San Francisco Shipping 194 South San Francisco Shipping 195 South San Francisco Shipping 196 South San Francisco Shipping 197 South San Francisco Shipping 106 rows selected. SQL run 1 select employee_id,city,department_name 2 from employe

37、es e,departments d,locations l 3 where e.department_id=d.department_id4* and d.location_id=l.location_idEMPLOYEE_ID CITY DEPARTMENT_NAME - - - 191 South San Francisco Shipping 192 South San Francisco Shipping 精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 19 页，共 36 页193 South San Francisco Shipping

38、194 South San Francisco Shipping 195 South San Francisco Shipping 196 South San Francisco Shipping 197 South San Francisco Shipping 106 rows selected. 6、Self-Joins 用于同一张表的JION SQL select a.last_name| works for |b.last_name 2 from employees a,employees b3 where a.manager_id=b.employee_id; A.LAST_NAME

39、|WORKSFOR|B.LAST_NAME - Ozer works for Cambrault Johnson works for Zlotkey Grant works for Zlotkey Livingston works for Zlotkey Taylor works for Zlotkey Hutton works for Zlotkey Abel works for Zlotkey 106 rows selected. 7、Nonequijoin 非对称连接Outer Join 将匹配不上的列也显示出来8、LEFT OUTER JOIN 将左表中未匹配的内容也显示出来-国际标准

40、SQL select e.last_name,e.department_id,d.department_name 2 from employees e left outer join departments d 3 on (e.department_id=d.department_id); 107 rows selected. SQL select e.last_name,e.department_id,d.department_name 2 from employees e,departments d 3 where e.department_id=d.department_id (+);

41、107 rows selected. 精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 20 页，共 36 页SQL select count(*) from employees; COUNT(*) - 107 9、RIGHT OUTER JOIN 将右表中未匹配的内容也显示出来SQL run 1 select e.last_name,e.department_id,d.department_name 2 from employees e right outer join departments d 3* on (e.department_id=d.

42、department_id) 122 rows selected. SQL run 1 select e.last_name,e.department_id,d.department_name 2 from employees e,departments d 3* where e.department_id (+)=d.department_id 122 rows selected. -106+16=122 /* 有 16 部门没有雇员 */ 10、FULL OUTER JOIN 将左右表中未匹配的内容都显示出来SQL select e.last_name,d.department_id,d.

43、department_name 2 from employees e full outer join departments d 3 on (e.department_id=d.department_id); 123 rows selected. 11、Cross Join A 表数据数乘以 B 表的数据数-国际标准1 select last_name,department_name from employees 2* cross join departments 2889 rows selected. -oracle 标准Select last_name,department_name fr

44、om employees,departments; 2889 rows selected. -107*27=2889 07-Using Subqueries to Solve Queries 子查询补充：注意AAA=NULL j结果AAA IS NULL的区别精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 21 页，共 36 页1、在 HAVING 字句中使用子查询SQL run 1 select department_id,min(salary) from employees 2 group by department_id 3 having m

45、in(salary) 4* (select min(salary) from employees where department_id=50) DEPARTMENT_ID MIN(SALARY) - - 100 50852.64 30 44533.92 50895.84 20 49504.8 70 55270.56 90 65364.96 110 52828.32 40 50227.68 80 49585.44 10 47195.04 60 46947.36 11 rows selected. 2、多行子查询ANY 大于子查询中的最小值=ANY 相当于 IN ALL 大于子查询中的最大值=A

46、LL 不存在精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 22 页，共 36 页子查询返回时NULL时， ANY为 FALSE ; ALL为 TRUE 子查询返回时NULL时， NOT IN 为 !=ALL ; IN 为=ANY 08-Using the Set Operators 使用 set运算符1、指导原则：列数相等；列的顺序一样；列的数据类型属于通一列2、UNION 合并数据并去掉相同的SQL select * from test; - 有 10 条数据COL1 COL2 COL3 - - - 1 11 21 2 12 22 3 13 2

47、3 4 14 24 5 15 25 6 16 26 7 17 27 8 18 28 9 19 29 10 20 30 10 rows selected. SQL select * from test1; -有 5 条数据COL1 COL2 COL3 - - - 1 11 21 2 12 22 3 13 23 4 14 24 精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 23 页，共 36 页5 15 25 SQL run -合并后1* select * from test union select * from test1 COL1 COL2 C

48、OL3 - - - 1 11 21 2 12 22 3 13 23 4 14 24 5 15 25 6 16 26 7 17 27 8 18 28 9 19 29 10 20 30 10 rows selected. 3、UNION ALL 合并数据全部显示SQL run 1* select * from test union all select * from test1 COL1 COL2 COL3 - - - 1 11 21 2 12 22 3 13 23 4 14 24 5 15 25 6 16 26 7 17 27 8 18 28 9 19 29 10 20 30 1 11 21 2

49、 12 22 3 13 23 4 14 24 5 15 25 15 rows selected. 4、INTERSECT 取交集SQL select * from test intersect select * from test1; COL1 COL2 COL3 - - - 1 11 21 精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 24 页，共 36 页2 12 22 3 13 23 4 14 24 5 15 25 5、MINUS 相减SQL select * from test minus select * from test1; COL

50、1 COL2 COL3 - - - 6 16 26 7 17 27 8 18 28 9 19 29 10 20 30 6、表列不全的操作-列不全的用to_char(NULL) 补齐SQL select location_id,department_name dep ,to_char(NULL) location2 from departments 3 union 4 select location_id,to_char(NULL) dep ,state_province location 5 from locations; LOCATION_ID dep location - - - 2700

51、 Bavaria 2800 Sao Paulo 2900 Geneve 3000 BE 3100 Utrecht 3200 Distrito Federal, 50 rows selected. 补充： 1、在复合查询中order by 不能出现在子句中间，只能用在整个查询的最后，而且只能识别子句的第一个字段，或者跟数字，order by 1/2 代表第一列 /第二列。精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 25 页，共 36 页09-Manipulating Data 操纵数据1、INSERT 插入数据语法 :INSERT INTO ta

52、ble (column , column .) VALUES (value , value .); Insert into dept select * from departments; 2、UPDATE 更新数据语法： UPDATE table SET column = value , column = value, . WHERE condition; 3、DELETE 删除DELETE FROM table WHERE condition; 4、TRUNCATE -清空表 不能 rollback 语法： TRUNCATE TABLE table_name 5、commit和 rollba

53、ck 精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 26 页，共 36 页10-Using DDL Statements to Create and Manage Tables1、创建表的语法CREAT TABLE schema.table (column datatype DEFAULT expr ,.); 2、SQL desc user_objects; -用户对象表Name Null? Type - - - OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUM

54、BER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) SQL col object_name format a20; -改变宽度select object_name,object_type from user_objects; OBJECT_NAME OBJECT_TYP

55、E - - V1 VIEW S1 SEQUENCE T5 TABLE T6 TABLE COMPUTE_TAX FUNCTION GIVE_RAISE_TO_ALL PROCEDURE MONEY PACKAGE MONEY PACKAGE BODY T1 TABLE 49 rows selected. -创建表前查询有49 张表SQL create table t (id int,name char(10); -创建 t 表Table created. select object_name,object_type from user_objects;50 rows selected. #创建

56、 t 表后查询有 50 张表了精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 27 页，共 36 页SQL insert into t values(0,boobooke); #插入 1 条数据1 row created. Select * from hr.t; #以 SYSDBA 用户查看 HR用户的 T表ID NAME - - 0 boobooke 3、Default option#create table定义列名中加上 default后，在 insert into列不写值的话， 系统就会插入 default后面的值，SQL create ta

57、ble demo (id int,name char(10) default boobookel,hire_date date default sysdate ); Table created. SQL insert into demo(id) values(1); 1 row created. SQL insert into demo values(2, my,null);1 row created. SQL select * from demo; ID NAME HIRE_DATE - - - 1 boobookel 2012-02-28:14:14:54 2 my 4、表的约束4.1 在

58、列级别创建约束SQL Create table emp1 (employee_id number(6) constraint emp1_emp_id_pk primary key,2 first_name varchar2(20), 3 job_id varchar2(10) not null); #not null只能在列级别定义Table created. 4.2 查询表的约束信息SQL Select dbms_metadata.get_ddl (TABLE,EMP1) from dual; #查看约束的函数DBMS_METADATA.GET_DDL(TABLE,EMP1) - CREAT

59、E TABLE HR.EMP1 ( EMPLOYEE_ID NUMBER(6,0), FIRST_NAME VARC 精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 28 页，共 36 页4.3 在表级别创建约束SQL Create table emp2 (employee_id number(6),2 first_name varchar2(20), 3 job_id varchar2(10) not null#not null只能在列级别定义4 constraint emp2_emp_id_pk primary key (employee_id

60、); Table created. Not null约束-是列中不能为空值（ null） 。Unique约束列中不能有重复的值，两个 NULL值不会产生重复 ,创建 unique约束系统会自动增加一个索引4.4 主键约束primary key constraint = not null+unique一个表只能有一个主键约束，主键可以是多列的组合，创建primary key系统会自动增加一个索引SQL Create table myemp (emp_id number(6) primary key , name varchar(30); Table created.SQL Select obje

61、ct_name,object_type from user_objects; OBJECT_NAME OBJECT_TYPE - - SYS_C005926 INDEX #系统自动加的索引T TABLE DEMO TABLE MYEMP TABLE 55 rows selected. 4.5 外键约束 froeign key constraint 是另外一张表的主键SQL run 1* Create table dept (dept_id number(6) primary key,dept_name varchar(20) not null)Table created. #先创建一张有主键的

62、父表SQL run 1 create table emp ( 2 emp_id number(4) primary key, 3 emp_name varchar(20) not null, 4 dept_id number (6), 5 constraint emp_dept_fk_1 foreign key (dept_id) references dept(dept_id)6 on delete cascade 7* ) Table created. 精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 29 页，共 36 页#创建了有外键的子表，

63、 on delete cascade 设置时当父表中的数据删除时，连同子表关联的数据一并删除。SQL insert into dept values(0,IT); #向父表中插入两条记录，创建两个部门1 row created. SQL insert into dept values(1,FINANCIAL); 1 row created. SQL select * from dept; DEPT_ID DEPT_NAME - - 0 IT 1 FINANCIAL SQL insert into emp values(100,Charles,0); #在子表中分别在两个部门中插入两个雇员1 r

64、ow created. SQL insert into emp values(101,W,1);1 row created. SQL s elect * from emp; EMP_ID EMP_NAME DEPT_ID - - - 100 Charles 0 101 W 1 SQL delete from dept where dept_id=0; #在主表中删除一条数据1 row deleted. SQL select * from dept;DEPT_ID DEPT_NAME - - 1 FINANCIAL SQL select * from emp; #子表的数据也一并删除了EMP_I

65、D EMP_NAME DEPT_ID - - - 101 W on delete cascade 设置时当父表中的数据删除时，连同子表关联的数据一并删除。SQL run 1 create table emp1 ( 2 emp_id number(4) primary key, 3 emp_name varchar(20) not null, 4 dept_id number (6), 5 constraint emp_dept_fk_2 foreign key (dept_id) references dept(dept_id)6 on delete set null7* ) 精选学习资料 -

66、 - - - - - - - - 名师归纳总结 - - - - - - -第 30 页，共 36 页Table created. #创建子表中设置on delete set null 当父表中删除数据时，把子表的响应的数据置为 NULLSQL select * from dept; #父表中只有一条记录DEPT_ID DEPT_NAME - - 1 FINANCIAL SQL insert into emp1 values(100,Bob,1); #在子表中插入一个雇员1 row created. SQL select * from emp1; EMP_ID EMP_NAME DEPT_ID

67、- - - 100 Bob 1 SQL delete from dept where dept_id=1; #删除父表中的记录1 row deleted. SQL select * from emp1; #子表中关联父表中的字段置为NULL了EMP_ID EMP_NAME DEPT_ID - - - 100 Bob Check constraint 5、使用子查询创建表语法： CREATE TABLE table (column,column) AS subquery; #约束并不会传递到新表，只会传递NOT NULL 约束SQL create table dept80 (emp_id,nam

68、e,salary,join_date) -括号里是字段的别名2 as3 select employee_id,last_name,salary*12,hire_date 4 from employees 5 where department_id=80; Table created. -创建了一个新表dept80 备份表结构的方法SQL create table myobj as select * from user_objects where 1=0; Table created. 精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 31 页，共 3

69、6 页6、Alter table 修改表Alter table dept80 read only;-将 dept80 表设置成只读 只读的表是可以drop 掉的Alter table dept80 read write;-将 dept80 表设置成读写11 -Creating other schema objects 1、创建视图的语法CREATE OR REPLACE FORCE|NOFORCE VIEW view (alias,alias.) AS subquery WHIT CHECK OPTION CONSTRAINT constraint WHIT READ ONLY CONSTRA

70、INT constraint; -OR REPLACE视图如果存在就替换-FORCE|NOFORCE -WHIT CHECK OPTION CONSTRAINT constraint约束设置-WHIT READ ONLY CONSTRAINT constraint只读设置SQL run 1 create view empvu80 as2* select employee_id,last_name,salary from employees where department_id=80 -创建 empvu80视图View created. SQL desc empvu80 -empvu80视图创

71、建成功Name Null? Type - - - EMPLOYEE_ID NOT NULL NUMBER(6) LAST_NAME NOT NULL VARCHAR2(25) SALARY NUMBER(8,2) SQL create view salvu50 (ID_NUMBER,NAME,ANN_SALARY) as -括号中的是别名2 select employee_id,last_name,salary*12 from employees where department_id=50; View created. 2、修改视图ALTER VIEW view 精选学习资料 - - - -

72、 - - - - - 名师归纳总结 - - - - - - -第 32 页，共 36 页3、With check option SQL create table emp as select * from employees; -创建 emp 表Table created. SQL create or replace view empvu20 as select * from emp where department_id=20; -创建 empvu20视图View created. SQL select employee_id,last_name,department_id from empv

73、u20;-有两条记录EMPLOYEE_ID LAST_NAME DEPARTMENT_ID - - - 201 Hartstein 20 202 Fay 20 SQL update empvu20 set department_id=10 where employee_id=202; 1 row updated. -将其中一条的 department_id改为 10 SQL select employee_id,last_name,department_id from empvu20;-再次查询 empvu20视图就剩下一条记录了EMPLOYEE_ID LAST_NAME DEPARTMENT

74、_ID - - - 201 Hartstein 20 -说明当基础数据被修改了，不符合创建视图中子句的条件后，在查询视图此数据也在视图表中消失了。SQL create or replace view empvu20 as select * from emp where department_id=20 2 with check option ; -创建 empvu20视图并增加约束View created. SQL select employee_id,last_name,department_id from empvu20; EMPLOYEE_ID LAST_NAME DEPARTMENT_

75、ID - - - 201 Hartstein 20 -视图中有一条数据SQL update empvu20 set department_id=10 where employee_id=201; update empvu20 set department_id=10 where employee_id=201 * ERROR at line 1: ORA-01402: view WITH CHECK OPTION where-clause violation -由于违反了 view WITH CHECK OPTION，不允许修改SQL update empvu20 set last_name=

76、hj where employee_id=201; 1 row updated. SQL select employee_id,last_name from empvu20; EMPLOYEE_ID LAST_NAME - - 201 hj -last_name不是创建视图中约束的字段，是可以修改的。精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 33 页，共 36 页4、删除视图 DROP VIEW view 5、Sequences 序列5.1 创建序列语法CREATE SEQUENCE sequence INCREMENT BY n -每次增加

77、的个数START WITH n -开始的序号MAXVALUE n | NOMAXVALUE -最大序号MIXVALUE n | NOMIXVALUE -最小序号CYCLE | NOCYCLE -序号是否循环CACHE n |NOCACHE -缓存如果是 CYCLE ，没有指定最小序号，那么达到最大序号后，再查询的话就会从 1 开始SQL create sequence myseq increment by 10 start with 100 maxvalue 9999 nocache nocycle; -创建了 myseq序列Sequence created. SQL select myseq

78、.nextval from dual; NEXTVAL - 100 SQL / NEXTVAL - 110 SQL / NEXTVAL - 120 -NEXTVAL 每操作一次会增加一次的序列，不同的用户也会增加SQL select myseq.currval from dual; CURRVAL - 120 SQL / CURRVAL - 120 -CURRVAL 获取最后一次 NEXTVAL 的序号，也就是当前的序号精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 34 页，共 36 页5.2 修改 sequence #不能修改最小序号的值ALT

79、ER SEQUENCE sequence INCREMENT BY n -每次增加的个数START WITH n -开始的序号MAXVALUE n | NOMAXVALUE -最大序号CYCLE | NOCYCLE -序号是否循环CACHE n |NOCACHE -缓存5.3Sequence的应用1 insert into dept10(department_id,department_name,location_id) 2* values(myseq.NEXTVAL,hj,2500) - department_id按 myseq.NEXTVAL 的序号的插入SQL / 1 row creat

80、ed. SQL / 1 row created. -按 myseq.NEXTVAL 的序号的插入两条SQL select * from dept10; DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID - - - - 10 Administration 200 1700 160 hj 2500 170 hj 6、Indexe 索引6.1 创建索引的语法CREATE UNIQUE BITMAP INDEX index ON table (column, column.); UNIQUE 索引约束设置BITMAP 索引类型（ BITMAP是索

81、引类型的一种）SQL create index emp_last_name_idx on emp(last_name); Index created. 精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 35 页，共 36 页6.2 删除索引 DORP INDEX index 7、SYNONYM 别名语法CREATE PUBLIC SYNONYM synonym FOR object; Create synonym salsum for salvu50; Drop synonym salsum; 精选学习资料 - - - - - - - - - 名师归纳总结 - - - - - - -第 36 页，共 36 页