oracle 11g 学习笔记

Oracle复习整理笔记-1-/461、Check规则Check(Agebetween15and30)把年龄限制在15~30岁之间2、新SQL语法在调用某一函数时，可以通过“=”符号来为特定的函数参数指定数据。而在Oracle11g中，在SQL语句中也可以这样的语法，例如：Selectf(x=6)fromdual;3、将sequence的值赋给变量11g之前的赋值方式Selectseq_x.next_valintov_xfromdual;11g赋值方式v_x:=seq_x.next_val;4、SQL的功能4.1数据定义Create,Drop,Alter4.2数据操纵Select,insert,update,delete4.3数据控制Grant,RevokeOracle复习整理笔记-2-/465、Oracle创建语法1、表空间createtablespacelq_oradatadatafile'd:\oracle_test\user_data01.dbf'size32mautoextendonnext32mmaxsize2048mextentmanagementlocaluniformsize16m;2、临时表空间createtablespacelq_oradata_temptempfile'd:\oracle_test\temp_data01.dbf'size32mautoextendonnext32mmaxsize2048mextentmanagementlocaluniformsize16m;3、创建用户createuserlqidentifiedbylqdefaulttablespacelq_oradatatemporarytablespacetemp;注：此处的temp为系统临时表空间，也可以自定义临时表空间；4、给用户授权grantconnect,resourcetolq;5、创建表createtableit_employees(employee_idnumber(6)notnullunique,first_namevarchar2(30),last_namevarchar2(30)notnull,emailvarchar2(30),phone_numbervarchar2(15),job_idvarchar2(10),salary_numbernumber(8,2),manager_idnumber(6)Oracle复习整理笔记-3-/46)添加数据insertintoit_employees(employee_id,first_name,last_name,email,phone_number,job_id,salary_number,manager_id,birth_date)values(seq_employee.nextval,'Micheal','Joe','[e-mail]','13149258943','it_prog',5000.67,seq_employee.nextval,to_date('2011-11-01','yyyy-mm-dd'));6、创建视图给用户分配创建视图的权限A:首先授予查询所有表的权限grantselectanytabletolq;B:再次授予查询所有字典表的权限grantselectanydictionarytolq;C：如果已经分配以上权限，还是权限不足,则可分配管理员权限grantdbatolq;createviewprog_employeesasselectemployee_id,first_name,last_name,email,phone_number,salary_number,manager_idfromit_employeeswherejob_id='it_prog'withcheckoption;注：可加上withcheckoption;7、创建索引聚簇索引：指索引向的顺序与表中记录的物理顺序相一致的索引组织。用户可以在查询频率最高的列上建立聚簇索引，从而提高查询效率。由于，聚簇索引是将索引和表记录放在一起存储，所以在一个基表上只能创建一个聚簇索引；在建立聚簇索引后，由于更新索引列数据时会导致表中物理顺序的变更，系统代价较高，因此对于经常更新的列不宜建立聚簇索引。Create[unique][cluster]index[索引名]On表名(列名[次序]，列名[次序]，列名[次序]……)其中，unique表示此索引的每一个索引值不能重复，对应唯一的数据记录。Cluster表示要创建的索引为聚簇索引。索引可以建立在对应表的一列或者多列上，如果是多个列，各个列之间需要用逗号分隔。次序用于指定索引值的排列次序，ASC表示升序，DESC表示降序，默认为ASC.Oracle复习整理笔记-4-/46Createindexit_lastnameonit_employees(last_name)描述：执行后会在表it_employees的last_name列上建立一个索引。而it_employees表中的数据将按照last_name值升序存放。6、Oracle删除语法Drop6.1：删除表Droptable表名删除表时，表中的数据、在该表上建立的索引一并都会被删除。6.2：删除视图Dropview视图名6.3：删除索引Dropindex索引名7、Oracle修改语法AlterAltertable表名[add新列数据类型[完整性约束]]新增birth_date列Altertableit_employeesaddbirth_datedate;[Drop完整性约束]删除employee_id字段的unique约束Altertableit_employeesDropunique(employee_id);[Modify列名[数据类型]]将manager_id字段改为8位Altertableit_employeesmodifymanager_idnumber(8)Oracle复习整理笔记-5-/468、Oracle查询select8.1：简单查询8.1.1使用from子句指定表select*fromhr.countries;select*fromhr.countries,hr.departments;8.1.2使用select指定列Selectcolumnname_1,……columnname_nFromtable_name_1,table_name_2.8.1.3算术表达式Selectem.first_name,em.last_name,em.salary_number*(1+0.2)new_salaryfromit_employeesem;查询出员工薪资上调10%之后的结果，为了提高刻度性，可以为列重新制定显示标题new_salary；Oracle复习整理笔记-6-/46注：如何列标题中包含一些特殊字符，例如空格等，则必须使用双引号将列标题扩起来。8.1.3distinct关键字Selectdistinctjob_idfromit_employees;8.2：where子句8.2.1条件表达式selectem.employee_id,em.first_name,em.last_namefromit_employeesemwhereem.first_namelike'B%';判断first_name以“B”开头的雇员；8.2.2连接运算and/orselectem.employee_id,em.first_name,em.last_namefromit_employeesemwhereem.phone_number='15321981677'andem.salary_number6000;用and做连接符，电话号码和薪酬为判断条件；-------------------------------------------------Oracle复习整理笔记-7-/46selectem.employee_id,em.first_name,em.last_namefromit_employeesemwhereem.first_name='Britney'orem.salary_number6000;8.2.3NULL值首先插入一条记录，此处Email只为NULLinsertintoit_employees(employee_id,first_name,last_name,email,phone_number,job_id,salary_number,manager_id,birth_date)values(seq_employee.nextval,'Britney','Joe',NULL,'15321981677','it_prog',10000.67,seq_employee.nextval,to_date('2011-11-01','yyyy-mm-dd'));查询值为null的语句：select*fromit_employeesemwhereem.emailisnull;select*fromit_employeesemwhereem.emailisnotnull;8.3：orderby子句selectem.last_name,em.job_id,em.salary_numberfromit_employeesemwhereem.salary_number5000orderbyem.job_id,em.salary_numberdesc;orderby子句后边可指定多个列名，首先根据第一列排序，当第一列值相同时，再对第二列进行比较排序，以此类推；Oracle复习整理笔记-8-/468.4：groupby子句通过GroupBy进行的查询:selectem.job_id,avg(em.salary_number),sum(em.salary_number),max(em.salary_number),count(em.job_id)fromit_employeesemgroupbyem.job_id;可以在groupby后使用rollup或者cube进行汇总，在查询结果中都会附加一条汇总信息，sql如下：selectem.job_id,avg(em.salary_number),sum(em.salary_number),max(em.salary_number),count(em.job_id)fromit_employeesemgroupbyrollup(em.job_id);8.5：Having子句selectjob_id,avg(SALARY_NUMBER),sum(SALARY_NUMBER),max(SALARY_NUMBER),count(*)fromIT_EMPLOYEESgroupbyJOB_IDhavingavg(SALARY_NUMBER)Oracle复习整理笔记-9-/465500通常与Groupby子句一起使用，在完成对分组统计后，可以使用Having子句对分组的结果做进一步筛选；8.6：多表连接查询8.6.1基本形式将表employees和表departments相连接，从而生成一个笛卡积：Selectemployee_id,last_name,department_namefromemployees,departments;8.6.2条件限定selectit.employee_id,it.last_name,dm.department_namefromit_employeesit,departmentsdmwhereit.department_id=dm.department_id;selectit.employee_id,it.last_name,dm.department_namefromit_emplo