Oracle Learning Notes

First episode:

#目前主流数据库 Microsoft Sqlsever and access MySQL ad2 Sybase informix Oracle #小型数据库: Access: Less than 100 people with low security requirements #中型数据库: Mysql,sqlsev ER: Load at 5000 to 15000 #大型数据库: sybase,oracle,db2: Load can handle massive Sybase Oracle DB2 #用什么数据库? 1. Project size: A, how much load (user volume) 2. Cost 3. Security Oracle Certification: Second set of Oracle installation database identity MyOracle

#默认产生两个用户 a.sys------->change_on_install (Superuser, with the highest permissions, SYSDBA role, CREATE DATABASE) B.system----> Manager (Management operator, great permissions.) With Sysoper role, createdatebase permissions)

#一般来说对数据库维护, you can use the system user

The third episode, Oracle's startup, configuration #Oracle database is multi-family----> to an instance database ① different users have different permissions on the table, #sqlserver a user-----> multiple databases #启动 ①oracleserver+ database name; ② Start Oracleorahome90tnslistener

#卸载 ① #oracle管路工具的介绍

The self-contained tool software, mainly executes SQL statements, P1\sql fast. Step: 1) Start-->oracle orachome90-->appliacation development--. Sql*plus 2) in the Run bar input SQLPUSW can 3) program-->oracle Orahome 90-->appplication Development-->sql*plus Worksheet

Oracle Enterprise

Host string: That is, the database name;

#https://localhost:5500/em can view database run status, create new tablespace and User Configuration

#show user;

#plSqlDeveloper

#scott/tiger #连接命令 1) Conn USAGE: Conn username/password @ network server name when connecting as a privileged user, you must bring as SYSDBA or as Sysoper conn System/manager #断开连接命令: Dis C #修改密码命令: PASSW # Exit Command: Exit #文件操作命令 1) Start and @//Run script start d:\aa.sql; 2) Edit: edits specified file 3) Spool: Outputs the contents of the Sql*plus screen to the specified file To. Spool D:\b.sql last input spool off

#设置显示行的宽度: Set Linesize 120; Paging settings: Set pagesize 5; (Sets the number of rows per page, default is 14)

Section III Oracle User management

#创建一个新的用户使用create user statement, typically with DBA authority, to use the Create user xiaoming identified by m123;

#给用户修改密码 need to have DBA authority or have ALTER user system permission; password xiaoming;

#删除用户 typically delete a user as a DBA, and cannot commit suicide; if another user deletes the user's permission to drop users.

* When deleting a user, if the deleted user has already created the table, then a parameter is required. (Cascade). Included: Drop user username "cascade"

#创建的新用户是没有任何权限的, even the permission to log in to the database does not, you need to specify the appropriate permissions, give the user permissions to use the Grant command, reclaim permissions using revoke. *grant Connect to Xiaoming #权限: System permissions (User rights to database access) and object permissions (user permissions for data object operations for other users SELECT, INSERT, UPDATE) #数据对象: User-created tables, views, indexes, triggers .... 。 #角色: Custom role and pre-defined role DBA role; Connect role; Resource role (can be built in any table space);

#创建表 *grant Resource to Xiaoming (grant full permissions to create table) create a Tables test (UserId vachar2 (30), UserName vachar2 (+));

#希望用户去查询scott的emp表; *grant Select on EMP to xiaoming #grant Select on EMP xiaoming//authorized to Xiaoming

#select *form scott.emp;

#收回权限: Revoke select on EMP from Xiaoming

#权限的传递 (Maintenance of rights): Xiao Ming users can query Scott's EMP table, but also hope that xiaoming can continue to pass this permission to other users.

* If object permissions, join with GRANT OPTION *grant Select on EMP to xiaoming with GRANT option, and grant connect to Xiaoming Wit if system permissions H admin option;

#使用profile Manage user passwords: Profile is a collection of commands that are password-constrained, resource-constrained, and when a database is established, Oracle automatically establishes a form named default. When the setup user does not specify the profile option, Oracle assigns default to the user. 1) Account lockout: Refers to the maximum number of times a user can enter a password when logging in, or the time that the user is locked out, typically executing the command with the DBA's identity.

①: Creating profile Create profiles Lock_account Limit//lock--account representative name failed_logion_attempts 3 Password_lock_time 2; ② user Xiaoming profile Lock_account;

2) Unlock *alter user xiaming account unlock;

3) Termination in order for the user to change the password periodically, the terminating password can be used to execute; *create profile myprofile limit password_life_time password_garce_time 2;//with DBA status Users are required to modify their login mask every 10 days. Grace 2 days.

4) Password history I want the user to change the password, you can not use the password previously used, you may use the password history. *create profile Password_history limit password_life_time password_grace_time2password_reuse_time 10;

5) Delete profile *drop profile Password_history[cascade];

Https://localhost:5500/em

No. 06. Oracle Table Management

#表名和列的命名规则 1) must start with a letter 20 cannot exceed 30 characters 3) cannot use Oracle reserved word

#oracle的数据类型 1) Char fixed length up to 2000 characters 2) varchar2 variable length max 4000 character 3) clob large object Max 4g

4) numeric type number range-10 of the -38~10 of the 38-square, can represent integers, can also represent decimals

5) Date type dates include month day and minute timestamp is an extension of the date data type

6) image (including sound, video) blob binary data

#怎样创建表---Student table CREATE TABLE student (Stnumber number (4), stname varchar2, Sex char (2), birthday date, sal number (7,2) ); #删除表 drop table students;

---class table CREATE table classes (classId number (2), CNAME varchar2 (40));

#添加字段 ALTER TABLE student Add (classId number (2));

#查看表 desc Student

#修改字段的长度 ALTER TABLE student Modify (XM VARCHAR2 (30));

#删除一个字段//Try not to use ALTER TABLE student drop column Sal;

#修改表名 rename student to Stu;

#添加数据 All fields are added insert into student values (' A001 ', ' Zhang San ', ' Male ', ' November-December-1997 ', ' 222.2 ', 21) Date format: Oracle default date format ' Dd-mon-yy ' default format for date: Alter session set nls_date_format= ' YYYY-MM-DD '; #插入部分字段 insert into student (xh,xm,sex) value (', ', ）

#插入空值 INSERT into student (Xh,xm,sex) value (",", null)

#查询没有生日的人 Select *from student where birthday is null;//is not null;

#修改一个字段 Update student set sex= ' female ' where= ' A001 ';

#修改多个字段 Update student set sex= ' female ', birthday= ' 1980-04-01 ' where= ' A001 '; Modify the character segment comma to do so.

#修改含有null用is修改.

#删除数据 Delete from student;//deletes the contents of the table, but the structure of the table is still there; it can be recovered by log;

#在删除之前添加回滚点 (Set save point) savepoint A; #回滚命令 rollback to A;

#删除表的结构和数据 drop table student;

#删除一条记录 Delete from student where xh= ' A001 ';

#删除表中的所有记录, the table structure is still in, do not write logs, can not retrieve deleted records, familiar fast truncate table student;

#第八讲 Oracle Table queries

#查看表的结构 desc EMP

#查询指定列 Select ename, Sal,job,deptno from EMP;

#set timing on; open operation time;

#insert into Users (userid,username,userpassword) Select *from users; automatically grow data;

#如何取消重复行 SELECT distinct deptno,job from EMP;

#如何smith salary, work, department select Deptno,job,sal from EMP where ename= ' SMITH ';

#使用算术表达式

Select Sal*13,ename from EMP;

#给列取别名 Select SAL*13+NVL (comm,0) *13 "annual wage", ename from EMP;

#如何处理null值: Use the NVL function to handle

#显示工资高于3000的员工 *select ename,sal from EMP where sal>=3000;

#显示在1982后入职的职员 *select ename,hiredate from emp where hiredate> ' January-January-1982 '

#显示工资在2000 ~2500 Employees *select ename,sal from EMP where sal>=2000&sal in <=2500;

#如何使用like操作符% represents any 0 to more characters; _ denotes any single character; #如何显示首字符为S的员工姓名和工资 *select ename,sal from emp where ename like ' s% ';

#如何显示第三个字符为S的员工姓名和工资 *select ename,sal from the EMP where ename like ' __o% ';

#在where条件中使用in *select *from emp where empno in (123,213,324);

#如何显示没有上级的员工 *select *from emp where Mgr is null;

#第09讲

#使用order by sentence

#如何按照工资从低到高显示员工信息 *select *from emp ORDER by Sal #从高到底 *select *from emp ORDER by sal Desc;

#按照部门号升序而雇员的工资降序; *select *from emp Order by deptno, Sal Desc;

#使用列的别名排序 *select enamel,sal*12 "annual salary" from the EMP Order by "annual salary";

#oracle表复杂查询 #数据分组----Max,min,avg,count

#如何显示所有员工最高最低工资 *select Max (sal), Min (sal) from EMP;

#如何显示所有员工最高最低工资 *select ename,sal from emp where sal= (select min (sal) from EMP);

#显示工资高于平均工资的员工信息 *select *from emp where sal> (select AVG (SAL) from EMP);

#group by and group by group is used to group the results of a query by grouping statistics group by to restrict group display results

#显示每个部门的平均工资和最高工资 *select avg (SAL), Max (SAL), Deptno from EMP gruop by Deptno;

#显示每个部门的每种岗位的平均工资和最低工资 *select avg (SAL), Max (SAL), deptno,job from EMP Group by Deptno,job;

#显示平均工资低于2000的部门 *select avg (SAL), Max (SAL), Deptno from emp gruop by DEPTNO have avg (SAL) <2000 #注意 1) Groupings can only appear in the selection column, or Now having. 2) first appear order by and have having;

#显示平均工资高于2000的部门, then by AVG sort *select avg (SAL), Max (SAL), Deptno from EMP Group BY DEPTNO have avg (SAL) >2000 ORDER by AVG (SA L);

#多表查询: Two queries for more than two tables or views.

#显示雇员工资所在部门的名字 *select a1.ename,a2.dname from emp a1,dept A2 where A1.deptno=a2.deptno;

#规定: The condition of a multi-table query is at least not less than the number of tables-1;

#第10讲多表查询 #显示部门号为10的部门名, employee name and salary *select a1.dname,a2.ename,a2.sal from dept a1,emp A2 where A1.deptno=a2.deptno and A1.dept no=10;

#显示各员工的姓名, wages, and their wage levels #select A1.ename,a1.sal,a2.grade from EMP a1,salgrade A2 where a1.sal between A2. Losal and A2. Hisal;

#自连接: Connection query on the same table;

#显示某员工的上级领导的姓名//treat a table as two *select woker.ename,?boss.ename from EMP Worker,emp boss where Wokker.mgr=boss.empno and worker . ename=king;

#子查询 (nested query): A SELECT statement embedded in another SQL;

#单行子查询: A query that returns only one row of data shows employees in the same department SMITN

* Query Simith department number select Deptno from emp where ename= ' SMITH ' * Show select *from emp where deptno= (select Deptno from EMP where E Name= ' SMITH '); #执行语句从左到右;

#多行子查询 #查询和10号部门的工作相同的雇员的名字, position, salary, department number

SELECT * from emp where job in (select distinct job from EMP where deptn=10);

Note: The where job is written after = or in depends on whether it is a single row or multiple lines, only =;

#在多行子查询中使用all operator

#如何显示工资比部门30的所有员工的工资高的员工的姓名. Payroll and Departmental *select ename,sal,dept from EMP where Sal> all (select Sal from emp wherr deptno=30); * Method 2 Select *from emp where sal> (select Max (SAL) from EMP where deptno=30);

#在多行子查询中使用any operator

Name, *select ename,sal,dept from EMP where sal> any (select Sal from emp wherr deptno=30) that shows the wages of any one employee in the department 30. * Method 2 Select *from emp where sal> (select min (sal) from EMP where deptno=30);

#多列子查询

#如何查询与simth department and post the same employee//query His department select Deptno,job from EMP where ename= ' SMITH '; Show Select *from emp WHERE (deptno,job) = (select Deptno,job from emp where ename= ' SMITH ');

#在from子句中使用子查询 #显示高于自己部门平均工资的员工的信息//1, the average salary and department number of the query department select DEPTNO,AVG (SAL) mysal from EMP Group by DEPTNO; Think of the above query as a sub-table select A2.ename,a2.sal,a2.deptno,a1.mysal from EMP A2, (select Deptno,avg (SAL) mysal from the EMP Group by dept NO) A1 where A2.deptno=a1.deptno and a2.sal>a1.mysal;

#分页查询 * Paging A total of three ways 1) rownum page select *from EMP 2) display rownum select A1.*,rownum rn from (select *from EMP) A1 3)

Take 6~10 line Select A1.*,rownum rn from (select *from emp) a1;//rn line number step 1.select a1.*,rownum rn from (select *from EMP) A1 where rownum<10; 2.select *from (select A1.*,rownum rn from (select *from emp) A1 where rownum<=10) where rn>=6;

Note: A. If you specify a query column, you only need to modify the innermost query select *from (select A1.*,rownum rn from (select Ename,sal from EMP) A1 where rownum<=10) where rn> = 6; B. Sort by simply modifying the innermost query select *from (select A1.*,rownum rn from (select Ename,sal from emp Order by Sal) A1 where rownum<=10) wh Ere rn>=6;

#用结果创建新表 This command is a quick way to build the standard method of CREATE table my table (Id,name,sal,deptno) as select Empno,ename,sal,job,deptno from EMP; * For example CREATE TABLE MYEMP1 (id,name,sal) as select Empno,ename,sal from EMP;

#合并查询: Operation Symbol Union, Union All,intersect,minus 1) Union This operation is used to obtain the unions of the two result sets. The duplicate rows in the result set are automatically removed. *select ename,sal,job from EMP where SAL >2500 Union select Ename,sak, the job from EMP where job= ' manager '; 2) UNION ALL: Do not cancel duplicate rows 3) The Intersect operation is used to get the intersection of the two result sets 4) the minus operation is used to obtain the difference set of the two result sets

#创建新的数据库 1) Tool Wizard: ADCA 2) manually created

-----------12th Java Operation Oracle--------

#java连接oracle????? #

#使用特定格式插入日期 * Use to_date function: User Specifies the format of the added date *insert into EMP values (9998, ' Little Red ', ' MANAGER ', ' 7782 ', to_date (1998-12-12, ' Yyyy-mm-dd '), 78.9,55.33,10);

#当用values子句时一次能插入多值 *create Table KKK (myID number (4), MyName varchar2 (a), mydept number (5)); INSERT into KKK (myid,muname,mydept) Select Empno,ename,deptno from emp where deptno=10;

#用子查询更新数据 * Hope that Scott's position, salary and subsidy are the same as SMITh's; *update emp Set (JOB,SAL,COMM) = (select Job,sal,comm from emp where Ename= ' Smith ') where ename= ' SCOTT ';

--------Oracle Transaction processing------

#事物用于保证数据的一致性, it consists of a set of related DML statements that either succeed or fail all of the DML statements of the group;

#事物和锁: When performing a transaction operation (DML statement), Oracle locks on the table being played, preventing other users from the structure of the table. #锁: File Lock ...

#提交事务 * When a commit is used to commit a transaction, the transaction is confirmed when the commit statement is executed. Ends a transaction. Delete the savepoint, release the lock. When a commit statement is used to end a transaction, the other session looks at the new data after the transaction changes

#回退事务: A savepoint is a point in a transaction that cancels a partial transaction and automatically deletes all the savepoint defined by that thing when the transaction is ended. When rollback is executed, the savepoint can be rolled back to the specified point.

#savapoint A1; #delete from EMP where empno=9996; #savapoint A2; #delete from EMP where empno=9999; #rollback to A2;

#第14讲 Functions of Oracle

#只读事务: Only operations that perform queries are allowed, and no other DML operations transactions are allowed, and a read-only transaction ensures that the user can only get data at a point in time. Other sessions can commit new transactions, but read-only transactions will not get the latest data changes #设置只读事务: Set transaction read only;

#sql函数的使用 1) Character function lower (char): Converts a string to lowercase format; Upper (char): Converts a string to uppercase format; length (char): Returns a string of lengths; substr (Char,m,n): Takes a substring of a string; Replace (char1,search_string,replace_string) InStr (Char1,char2,[,n[,m]) takes the substring at the position of the string

* Display the employee's name in lowercase to select lower (ename) from EMP;

* Display the employee's name in uppercase to select Upper (ename) from EMP;

* Employees whose names are exactly 5 characters are displayed; Select *from emp Length (ename) = 5;

* Displays the first three characters of all employee names; select substr (ename,1,3) from EMP;

* Display the names of all employees in uppercase letters; Select UPPER (Substr (ename,1,1)) from epm;//Capital Select Lower (substr (Ename,1,length (ename)-1)) From epm;//lowercase Select upper (substr (ename,1,1)) | | Lower (substr (Ename,1,length (ename)-1)) from EMP; merge

#替换 Select Replace (ename, ' a ', ' I am a tiger ');

--------Mathematical function--------#包括: Cos cosh,exp,il,log,sin,round,trunc,mod,floor,ceil #round (n,[m]) The function is used to perform rounding; #trunc (N,[m] This function is used to intercept numbers, and if M is omitted, the fractional part is truncated, if M is a positive number is truncated to the M-bit of the decimal point, if M is negative, the first m bit of the decimal point is truncated, #mod取模; #floor (returns the largest integer less than or equal to N), rounding up the #ceil ( Returns the smallest integer greater than or equal to n);

* Displays the daily wage of all employees at one months for 30 days, ignoring the remainder;

The use of #SQL function-the date function #日期函数用于处理date类型的数据. By default, the format of the date is dd-mon-yy. 1) Sysdate: The function returns the system time; *select sysdate from dual; 2) add_months (d,n): Plus month * Find more than eight months of employee select *from emp where Sysdate>add_months (hiredate,800) 3) Last_day (d): Returns the day of the month on which the specified date displays the number of days the employee joined the company select Sysdate-hiredate "Entry Days", ename from EMP; 4) Find all employees hired on the 3rd day of each month select HireDate, ename from EMP where Last_day (hiredate) -2=hiredate;

#转换函数