Database ah, think, to the database, the first database to learn at the beginning is Oracle, learn SQL statement, although the back also contacted PL, but the feeling is still built in SQL above learn.
Oracle emMM installation is here, should be open source software installation is no problem.
The official download address: www.oracle.com/technetwork…
Download it and install it. You can find a tutorial online.
1. Database introduction
1.1. Oracle Corporation
Oracle Software Systems Co., LTD., is currently the world’s second largest software company (first: Microsoft), Oracle company in the database market, undisputed first
1977: Software Development Labs;
1978: Relational Software Corporation “(RSI)
1982: Oracle Systems Corporation
Oracle Partners: Ed Oates, Bob Miner, Larry Ellison; **Bruce Scott ** was the first employee hired by Oracle
1.1 Oracle Database client
OracleServiceOrcl —– sqlPlus (1)
Start OracleOraDb11g_home1TNSListener: sqldeveloper (2)
Start Oracle Database ConsoleOrCl: Web Management Client (3)
https://localhost:1158/em
Copy the codeSummary: Install Oracle database and generate 7 services
OracleServiceOrcl: must be started
OracleOraDb11g_home1TNSListener: can not start (remote connection to access Oracle database need to start)
OracleDBConsoleorcl: Can not start (Web management needs to start)
OracleJobSchedulerORCL: Can not start (job scheduling)
OracleMTSRecoveryService | OracleOraDb11g_home1ClrAgent: can not start Microsoft technology related need to start the service
Oracle ORCL VSS Writer Service: The Oracle ORCL VSS Writer Service can not be started.
The Oracle database port: 1521 | MySql database port: 3306 | SQL Server port: 1433
1.2. SqlPlus Settings
Sqlplus is an Oracle command-line client program
Start SQLPlus and log in to sqlPLust Scott/Tiger
Show user: Display current login user select * from TAB; See what table resources under the current user (see table 4 - by default EMP | DEPT | SALGRADE | BONUS) set pagesize 40 set each page shows article number is 40 (size can decide) set linesize 300 Set the number of characters to be displayed in each line. 300(the size is customized) quit: Exits the SQLplusCopy the code1.3 Basic account management
How do I log in if I forget the password of sys account
Sqlplus SYS /nolog as sysdba: enter the system without password. Alter user sys identified by orcl: change password; Conn Scott /tiger Alter user Scott account lock; Lock Scott's account alter user Scott account unlock: Unlock Scott's accountCopy the code1.4. Database system
A complete database system consists of the following parts
The hardware system
The operating system
Database Management System (ORACLE CE with Management System)
Database (data warehouse: store data) — file (DBF — data file, CTL(control file),LOG(LOG file));
Database Administrator (DBA)
1.5. History of database development
Memory: cannot persist
File: Stores data
Hierarchical | network database: can store, can maintain, statistics and analysis
** Relational databases: ** Data is stored in tables
1.6. Database products
A large database, Oracle Oracle database | Sysbase company Sysbase database | IBM DB2 database
Medium-sized database: the MySQL database (open source) | Microsoft SQL Server
Small database: Access database (MS), the ASP (.net) | Sqlite (embedded database, a database file, do not need to install any services)
An Oracle database has many objects (tables, views, indexes, packages, procedures, functions……….) Data is stored in the table object;
Table structure: rows (data), columns (table structure); Each row of a table is called a record (entity)
Oracle database versions: I (OrACle8I, ORACle9I), G (OrACle10G, OralCE11G), C (OrACle12C)
2. Table object under SCOTT account
Select * from TAB # DEPT EMP SALGRADE BONUS DESC select * from TAB # DEPT EMP SALGRADE SALGRADE DESC select * from TAB # DEPT EMP SALGRADE SALGRADE DESCCopy the codeDEPT # DEPTNO NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13)Copy the codeEMPNO NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)Copy the codeSALGRADE # SALGRADE NUMBER LOSAL NUMBER HISAL NUMBERCopy the codeENAME VARCHAR2(10) JOB VARCHAR2(9) SAL NUMBER COMM NUMBERCopy the code3, SQL
SQL: Structure Query Language(STRUCTURED Query Language) is the Language that all relational databases obey. In order to meet the specific business needs of each database, each database vendor has extended the SQL syntax on the basis of respecting SQL (this part is different for each database). The Oracle database extends SQL with the syntax: PL/SQL, and the SQLServer database extends with the syntax: T-SQL
SQL is divided into the following categories: DQL | DML DDL | | DCL | TCL
DQL: Data Query Language; — — — — — the SELECT key | difficult 】 【
DML: Data Manipulation Language; —– INSERT | UPDATE | DELETE
DDL: Data Definition Language Data Definition Language – CREATE | ALTER | DROP
DCL: Data Control Language Data Control Language – GRANT | INVOKE
TCL: Transaction Control Language – COMMIT Transaction Control Language | ROLLBACK | SAVEPOINT
Interview questions: Write SQL statement questions (mainly query)
3.1 Basic SELECT Statements
Basic SELECT statement SELECT * | {[DISTINCT] column | expression (alias),... } FROM table;Copy the codeSELECT * FROM emp; (SELECT EMPNO,ENAME, JOB,MGR ,HIREDATE,SAL,COMM, DEPTNO FROM emp;) # according to the emp table EMPNO, ENAME, JOB, HIREDATE, SAL SELECT EMPNO, ENAME, JOB, HIREDATE, SAL FROM emp; SELECT DISTINCT deptno FROM EMp; # display emP table employee's number, name, position, salary, and salary after adding 200; SELECT empno,ename,job,sal, sal+200 FROM emp; SELECT empno,ename,job,sal+NVL(comm,0) FROM emp; SELECT empno,ename,job,sal+NVL(comm,0) monthsal FROM emp; SELECT empno,ename,job,sal+NVL(comm,0) AS monthsal FROM emp;Copy the code3.2 Conditional Filtering
SELECT *|{[DISTINCT] column|expression [alias],... } FROM table [WHERE condition(s)]Copy the codeSelect * from employee where id = 7369; select * from employee where id = 10; SELECT * FROM emp WHERE empno = '7369' requirements: setting conditions, the value of the attribute suggest using single quotes (no matter what type of data) # condition query: comparison operator (=, > > = < < = < > |! Select * FROM emp WHERE sal>=2000; #BETWEEN ... SELECT * FROM emp WHERE sal BETWEEN 2000 AND 3000; SELECT * FROM emp WHERE sal BETWEEN 2000 AND 3000; SELECT * FROM emp WHERE sal >=2000 AND sal<=3000; # (for the value of the attribute IN certain values of fixed) | NOT # query EMP IN position IN the table as follows: CLERK, all the staff of SALESMAN SELECT * FROM EMP WHERE JOB IN (' CLERK ', 'SALESMAN'); SELECT * FROM emp WHERE JOB NOT IN('CLERK','SALESMAN','DEVELOPER'); %:0 or any number of characters _ : any single character # Query all employees in the EMP table whose names have A character; SELECT * FROM emp WHERE ename LIKE '%A%'; Select * from EMP where job A starts with A; SELECT * FROM emp WHERE job LIKE 'A%'; Select * from EMP where A = A; SELECT * FROM emp WHERE ename like '_A%'; # # IS NULL | IS NOT NULL query commission IS empty of all employees SELECT * FROM emp WHERE comm IS NULL. Select * from department 30 where salary > 2500; select * from department 30 where salary > 2500; SELECT * FROM emp WHERE deptno=30 AND sal > 2500 SELECT * FROM emp WHERE (deptno=30 AND sal > 2500) OR (deptno=20 AND sal < 1500)Copy the code3.3 the sorting
SELECT *|{[DISTINCT] column|expression [alias],... } FROM table [WHERE condition(s)] [ORDER BY {column, expr, alias} [ASC|DESC]];Copy the code# sort EMP table by salary ascending; SELECT * FROM emp ORDER BY sal ASC; Select * from 'department' where 'department' = 'department'; select * from 'department' where 'department' = 'department' where 'department' = 'department' where 'department' = 'department'; SELECT * FROM emp WHEREdeptno=30 ORDER BY sal DESC SELECT * FROM emp WHEREdeptno=30 ORDER BY sal DESC;Copy the codeSELECT
FROM
WHERE
ORDER BY
Copy the code4, Oracle single-line functions
A function is an object in an Oracle database. Functions are divided into built-in functions (single-line functions) and custom functions. The main function of a function is to perform operations; Oracle single-line functions: character functions, numeric functions, date functions, conversion functions, general functions
Dual: a virtual table in the Oracle database, used to test functions and expressions.
4.1 Character Functions
SELECT lower ('HELLO'), upper ('HELLO'), initcap ('HELLO,world') FROM dual; SELECT LOWER(ename),UPPER(ename),INITCAP(ename) FROM emp; # common character functions: CONCAT SUBSTR LENGTH LPAD | RPAD TRIM the REPLACE INSTR # CONCAT (string 1, 2) string, # SUBSTR (string, start, len) # LPAD (string, Len, 'completion of characters') | RPAD (string, len,' completion of characters') # TRIM: go around the blank space to go out (middle) # REPLACE (replacement string, 'oldchar', 'newchar) # INSTR (string)' characters'Copy the code4.2 Numerical Functions
Several common functions for numerical operations: Round Trunc,mod
SELECT ROUND (12.1256, 2), ROUND (12.1256, 1), ROUND (12.1256) FROM dual; SELECT TRUNC(12.123), TRUNC(12.125) FROM dual; Can keep decimals, but will not be rounded; SELECT the mod (10, 3) FROM dual;Copy the code4.3 Date function
The default date format of the Oracle database is day-month-year. To_char is used to display the date in Chinese format.
SELECT sysdate from dual; MM yyyy | | yy: years | MM: months dd | d: day HH24 | hh12: MI | MI: SS | SS: secondsCopy the codeDate function in oracle: months_between | add_months | next_day | last_day; You need to understand date arithmetic
Date – Date = days
Date + number (days) = date
Date – Number (days) = date
Select ename, to_char(hireDate,' YYYY-MM-DD '), trunc(months_BETWEEN (sysdate,hiredate)), trunc(sysdate-hiredate) from emp; Select to_char(sysdate,' YYYY-MM-DD '), to_char(add_months(sysdate,5),' YYYY-MM-DD ') from dual; select to_char(sysdate,' YYYY-MM-DD ') from dual; Select sysdate,next_day(sysdate,' Thursday '),next_day(sysdate,4) from dual; Select last_day(sysdate) from dual; select last_day(sysdate) from dual; select last_day(last_date) from dual; SELECT * FROM emp WHERE hiredate = last_day(hiredate) ;Copy the codeFigure out how many years, months, and days each employee has been employed
Select ename, to_char(hireDate,' YYYY-MM-DD '), to_char(hireDate,' yyyY-MM-DD '), to_char(hireDate,' yyyY-MM-DD '), Trunc (mod(Months_BETWEEN (sysDate, hireDate),12)) (months_between (sysdate, hiredate))) from emp;Copy the code4.4 Conversion Function
SQL > alter database; To_date (char,'format') : Convert a date from a string format to a date type select to_date('2012-05-22 20:49:10',' YYYY-MM-SS ') hh24:mi:ss') from dual;Copy the code4.5 Null value handlers
| NVL (column | expression, value) | Null value handler function |
|---|---|
| NVL2 (column | expression, value1, value2) | If the expression is not empty, value1 is returned, otherwise value2 is returned |
| COALESCE (exp1, exp2,… value) | Expression 1 is empty, column 2 | expression,… ,value |
# null handler: NVL | NVL2 | # computing bonuses: commission is not null, annual bonus is 1.5 times that of the base salary, commission is empty, year-end bonuses for basic pay 1.2 times the select ename, NVL2 (comm, sal * 1.5, sal * 1.2) from emp; SELECT COALESCE(TO_CHAR(comm),TO_CHAR(MGR),' boss ') FROM emp SELECT COALESCE(TO_CHAR(comm),TO_CHAR(MGR),' boss ') FROM empCopy the code4.6 Conditional Expressions
Conditional expressions: CASE expressions and DECODE expressions, which have similar functions;
CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END select empno,ename,job,sal, case deptno when 10 then 'ACCOUNTING' when 20 then 'RESEARCH' when 30 then 'SALES' when 40 then 'OPERATIONS' else 'No department' end department name from emp; select empno,ename,job,sal, Decode (deptno,10,'ACCOUNTING',20,'RESEARCH',30,'SALES',40,'OPERATIONS',' no department ') department names from EMP;Copy the code5, multiple table query [this is important]
When it is necessary to take out the data of multiple tables at the same time, it is necessary to combine multiple tables for query. Multi-table query will produce the number of records generated by Cartesian product: N1*N2. Therefore, multi-table query efficiency is very low (if single-table query can be solved, try not to use multi-table query).
Select deptno, deptno, deptno, deptno, deptno, deptno, deptno, deptno, deptno, deptno, deptno, deptno, deptno, deptno, deptno, deptno, deptno, deptno, deptno, deptno, deptno, deptno, depTno, depTno, depTno
You can also set conditions based on the indirect properties of the two tables (contextual semantic matching)
5.1 Equivalent Query
Select * from employee table where id, name, position, salary, and department of employee Department address select emp empno, emp. Ename, emp. The job, an emp. Sal, dept. Deptno, dept., dname dept. Loc from emp, dept where emp.deptno=dept.deptno; # named use alias select e.e mpno, e.e name, e.j ob, e.s al, d.d eptno, d.d name, d.l oc from emp e, d dept where e.d eptno = d.d eptno; Select * from employee table where id, name, position, salary, department, address, salary grade; select e.empno,e.ename,e.job,e.sal,d.dname,d.loc,s.grade from emp e,dept d,salgrade s where e.deptno=d.deptno and e.sal Between s.l osal and Sheldon horowitz isal # query in the employees table employee number, name, position, salary, and employee's name, department address and employee's salary level; #grade = 1; 3:2 3 4 4 5 five select e.e mpno, e.e name, e.j ob, e.s al, d.d name, d.l oc, Case s.Glade when 1 then 'Intern salary' when 2 then 'Junior salary' when 3 then 'Intermediate salary' when 4 then 'Senior Salary' when 5 then 'Architect salary' End Grade from emp e,dept d,salgrade s where e.deptno=d.deptno and e.sal between s.losal and s.hisal; Select * from employee table where employee id, name, position, salary, department name, department address, employee's salary grade, employee's boss; Select e1 empno, e1 ename, e2, ename boss, e1. Job, e1. Sal, d.d name, d.l oc, s.g rade from emp e1, dept d, salgrade s, emp e2 where e1.deptno=d.deptno and e1.sal between s.losal and s.hisal and e1.mgr=e2.empnoCopy the code5.2 Connection Query
Join query is one of the implementation methods of multi-table query. Join query in OralCE database is divided into outer Join and inner Join.
Inner join: Equivalent to equivalent query (exactly the same)
Select * from employee table where id, name, position, salary, and department of employee Department to address the select e.e mpno, e.e name, e.j ob, e.s al, d.d eptno, d.d name, d.l oc from emp e inner join dept d on e.d eptno = d.d eptno; Select * from employee table where id, name, position, salary, department, address, salary grade; select e.empno ,e.ename,e.job,e.sal,d.deptno,d.dname,d.loc,s.grade from emp e inner join dept d on e.deptno=e.deptno inner join salgrade s on e.sal between s.losal and s.hisal; Select * from employee table where employee id, name, position, salary, department name, department address, employee's salary grade, employee's boss; Select e1 empno, e1 ename, e2, ename boss, e1. Job, e1. Sal, d.d eptno, d.d name, d.l oc, s.g rade from emp e1 inner join dept d on d.deptno=e1.deptno inner join salgrade s on e1.sal between s.losal and s.hisal inner join emp e2 on e1.mgr=e2.empno;Copy the codeExternal connection: divided into left to the outside connection, right to the outside connection, complete external connection
Left join: display all the data in the left table and all the data in the right table that meet the criteria (Descartes product removed)
Join right: show all the data in the right table and all the data in the left table that meet the criteria (without cartesian product)
Left join and right join are convertible (only table positions are different)
# according to all the staff of all departments and departments (overlapping) left the select d.d eptno, d.d name, d.l oc, e.e mpno, e.e name, e.j ob, e.s al from dept d left outer join emp e on d.deptno=e.deptno; # according to all the staff of all departments and departments (overlapping) right select d.d eptno, d.d name, d.l oc, e.e mpno, e.e name, e.j ob, e.s al from emp e right outer join dept d on e.deptno=d.deptno;Copy the codeFull outer join: display all data in left and right tables (without Cartesian product)
# according to the emp table all data and all departments table data cartesian product (to) the select d.d eptno, d.d name, d.l oc, e.e mpno, e.e name, e.j ob, e.s al from emp e full outer join dept d on e.deptno=d.deptno;Copy the code5.3 Natural Connection
A simplified version of the equivalent query or join query syntax, which can only be used if the associated attribute name is the same in two tables;
Query the employees in the table name, position, salary and employee's department name and address - (deptno) SELECT e.e name, e.j ob, e.s al, d.d name, d.l oc FROM emp e natural join dept dCopy the code5.4 the using clause
Using clause: Two tables have the same association attribute (deptno). Using can be used directly to simplify conditional setting
Query in the employees table name, position, salary and employee's department name and address - (deptno) SELECT e.e name, e.j ob, e.s al, d.d name, d.l oc FROM emp e join dept d using(deptno)Copy the code6. Subquery
Subquery: The query contained within a query is called a subquery (external query is called the master query). The result of a subquery is often the condition of the external query (the master query), so the subquery must be run first. Subqueries need to be enclosed in parentheses; The result of subquery is single column
Single row subquery: Select sal from emp where ename='FORD' select sal from emp where ename='FORD' from emp where ename='FORD Select * from emp where deptno = (select deptno from emp where ename='FORD') The result of a subquery has multiple values; When filtering conditions, you need to combine predicates: Select * from emp where sal > ALL (select sal from emp where sal > ALL (select sal from emp where sal > ALL) Select * from emp where sal > any (select sal from emp where deptno=20) Select * from emp where deptno IN (10,20) # select * from emp where deptno IN (10,20) # select * from emp where sal in (select sal from emp where deptno=20)Copy the codeOracle database paging problem — subquery
A pagination display of an Oracle database table that requires an attribute with consecutive row numbers. This attribute is provided directly by Oracle, and row numbers are obtained from ROWNUM (columns generated by ROWNUM are called pseudoexamples). Oracle database paging requires a three-tier nested SQL
Level 1 SQL: Solve sorting
Level 2 SQL: Resolve generate pseudoexamples (generate a consecutive line number — paging required)
Layer 3 SQL: Solve fetching a page of data
Paging: the database gets two parameters: Pageindex =1,pagesize=4 select * from (select e.*,rownum rn from (select * from emp order by empno) e) # where rn between startposition and endposition startposition:(pageindex-1)* pagesize + 1 endposition : Pagesize * pageIndex The above can realize the pagination of Oracle database table data, but the runtime, low efficiency; SELECT * FROM (SELECT e1.*,rownum RN FROM (SELECT * FROM EMp order by empno ASc) e1 WHERE rownum <=endposition) WHERE RN >=startposition startposition:(pageindex-1)* pagesize + 1 endposition : Pagesize * pageindex select * from emp limit startposition, endposition: mysql pagingCopy the codeTo display data in pages, you must pass two parameters to the database: pageIndex (current page) and Pagesize (size per page)
Table: Total record confirmation/pagesize –> total page number —— pageIndex –> Get current page data
pageindex=1,pagesize =4
Query data between 31st and 40th in the table (users)
select * from
(select e.*,rownum rn from
(select * from users) e)
where rn between 31 and 40
Copy the code7. Group query
Group query: the data obtained is in the unit of group. Group query serves for statistics. Group first needs to know the group function (aggregate function) for statistics based on group.
Sum,avg, Max,min,count
Select sum(sal+ NVL (comm,0), avg(sal+ NVL (comm,0), Max (sal+ NVL (comm,0)), Min (sal+ NVL (comm,0)) count(*) from emp;Copy the codeCount (*),count(1),count(column) difference
Grouping is always combined with group functions (not separated), and group queries yield statistical results in unit of group; Grouped by what, the results that can be displayed by grouped queries: grouped columns, grouped functions
Experience: according to the table which a property grouping (grouping columns are often have duplicate data columns: department | | position gender)
SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] HAVING group_condition] -- Having [ORDER BY column]; having [ORDER BY column];Copy the code# query emP table data, grouped by department, statistics of each department monthly total salary, maximum salary, minimum salary, average salary, total number of employees; select deptno, sum(sal),max(sal),min(sal),avg(sal),count(*) from emp group by deptno having min(sal) < 3000Copy the codeConclusion: find the data of grouping column | show (set of functions + grouping column)
ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col2)
RANK() OVER(PARTITION BY col1 ORDER BY col2)
DENSE_RANK() OVER(PARTITION BY col1 ORDER BY col2)
Copy the code8 DML.
DML: Data Manipulation Language; —– INSERT | UPDATE | DELETE
INSERT INTO table_name (column1,column2,...) values ( value1,value2, ...) ; UPDATE table_name set column1=new value,column2=new value,... DELETE FROM <table_name> WHERE <table_name>Copy the codeDelete all data from a table
Delete from EMp -- Delete all data in the table truncate TABLE EMP; Truncate: Delete Deletes all table data. Truncate deletes all table dataCopy the code9. Transaction
Session: Each connection to the Oracle database is established, indicating that a session is established with the Oracle database.
When adding, deleting, or modifying a table in an Oracle database, it is not written to the table data persistently by default, but stored in the session buffer. If no data is submitted, the data will not be persisted.
To commit data, execute: commit — commit as a transaction; Data can be rolled back before submission, but cannot be rolled back once submission is complete. Rollback operation: rollback.
A transaction is a unit of work that encapsulates a series of operations that must either succeed or fail. After a transaction is executed, it cannot be rolled back;
Transaction characteristics | features:
Atomicity: A series of operations cannot be separated (they must be committed together to run in the database as a whole)
Transfer: (2 steps: subtract one account, add another)
Consistency: Data must be operated consistently before and after a transaction.
Isolation: Transaction operations are not affected by other transactions
Persistence: Data is persistently written to once a transaction completes
Oracle database default (add, Delete, change) operations do not enable transaction, so you need to manually commit, you can enable Oracle database automatic transaction
set autocommit on;
set autocommit off;
10. DDL (Table correlation)
DDL: Data Definition Language Data Definition Language – CREATE | ALTER | DROP
The creation of Oracle database objects is transaction based by default;
10.1 Oracle Data Types
| The data type | describe |
|---|---|
| VARCHAR2(size) | Variable length character data |
| CHAR(size) | Fixed-length character data |
| NUMBER(p,s) | Variable length numerical data |
| DATE | Date-based data |
| CLOB | Character data, up to 4G |
| BLOB | Binary data, up to 4G |
10.2 create a table
CREATE TABLE <table_name>( column1 DATATYPE [NOT NULL] [PRIMARY KEY], column2 DATATYPE [NOT NULL], ... [Constraint < constraint name > constraint type...] )Copy the codeCreate a table (tb_STUDENTS)
Tb_students -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - the property name type constraints note sid varchar2 (50) student Numbers sname varchar2 (100). Student name Sgender Varchar2 (10) Student Gender Semail Varchar2 (50) Student email stel Varchar2 (20) Phone sBirthday Date Date of birth......Copy the codeRegarding the assignment of primary key attributes of the table: The primary key constraint requires that the attribute value cannot be empty and the attribute value cannot be repeated. The following two schemes are generally adopted for the value of primary key attributes:
1, identity column: is a number type (integer), will be automatically generated, convenient in mysql, sqlserver database, not convenient in oracle database
2. Uuid: a globally unique string value; Uuid generation can be generated in the Java language, and then uploaded to the database, or directly using OralCE to generate a unique UUID value;
Produce UUID: in two ways to invoke the oracle database SYS_GUID () function to generate | generated through the JAVA String UUID = UUID. RandomUUID (). The toString ();Copy the code10.3 the sequence
CREATE SEQUENCE <sequencen_name> INCREMENT BY n START WITH n [MAXVALUE n][MINVALUE n] [CYCLE|NOCYCLE] [CACHE n|NOCACHE]; 1 CURRVAL: retrieves the current value of the sequence. NEXTVAL: The next sequence valueCopy the codeSequence: Generates a sequential identity value
Mysql database generates autoincrement; sqlserver database generates identity
10.4 delete table
drop table tablename;
Copy the code10.5 Modifying the Table Structure
Add columns | | delete column change column
ALTER TABLE <table_name> ADD (TABLE 1 type [NOT NULL] ALTER TABLE TB_STUDENTS ADD Saddress VARCHAR2 (100) ALTER TABLE <table_name> drop (table1, table2....) ; alter table tb_students drop (saddress); ALTER TABLE <table_name> modify(ALTER TABLE <table_name>....) ; alter table tb_students modify (sname varchar2(50))Copy the code10.6 Oracle Flashback Mechanism
After oracle 10G is released, the flashback mechanism is added. Table objects deleted are stored in the recycle bin by default.
Oracle Recyclebin show recyclebin Recover deleted tables from the recycle bin; Flashback table name to before drop [rename to new table name] Purge table name from purge table Drop table tablename purge;Copy the code10.7 Changing table Names
RENAME <table_name> to <new table_name>;
Copy the code10.8 the replicated table
The replication table | copy table structure and data structure
create table deptbak as select * from dept;
create table deptbak as select * from dept where 1=2;
Copy the code11. Constraint
Constraint object: table data constraint — validity + integrity === correctness
When constraints are added: Constraints can be added at the same time as the table is created; You can also add constraints after the table is created.
Primary key constraint: A primary key constraint must be added to create a table. The primary key constraint must be non-empty + dimension 1
Non-null constraint: the value cannot be null;
Check constraint: Verifies the validity of the attribute value
Foreign key constraint: An attribute value in a table is associated with an attribute value in an external table
Unique constraint: values do not duplicate (can be null, but only one null value)
create table tb_students ( sid varchar2(50), sname varchar2(100) constraint null_students_sname not null , sgender varchar2(10) , mid varchar2(50), stel varchar2(20), constraint pk_students_sid primary key (sid) , Constraint ck_students_sgender check(sGENDER =' male 'or sgender=' female '), constraint fk_students_mid foreign key (mid) references tb_majors(mid), constraint un_students_stel unique(stel) )Copy the codeALTER TABLE <table_name> ADD CONSTRAINT <constraint_name> ALTER TABLE <table_name> DROP CONSTRAINT <constraint_name>; alter table tb_students add constraint pk_students_sid primary key(sid) alter table tb_students add constraint Ck_students_sgender check(sgender=' male 'or sgender=' female ') ALTER table tb_STUDENTS add constraint fk_studentS_mid Foreign key (mid) references tb_majors(mid); alter table tb_students add constraint un_students_stel unique(stel) alter table tb_students modify sname varchar2(50) constraint null_students_sname not nullCopy the code12. View
View: Based on the result of the query from the source table, the view does not store data (data is stored in the source table) — virtual table,
The view object can mask sensitive data in the source table. Can save complex SQL
CREATE [OR REPLACE] VIEW <view_name> AS <SELECT statement >; # block emP table sal,comm, other information users can view; create or replace view v_emp as select empno,ename,job,hiredate,deptno from emp; Create or replace view v_select as select e1.ename,e2.ename boss,d. name,e1. Sal, s.gleade from emp e1 join dept d on e1.deptno=d.deptno join salgrade s on e1.sal between s.losal and s.hisal join emp e2 on e1.mgr = e2.empn select * from v_emp; select * from v_select; DROP VIEW <view_nameCopy the code13. Synonyms
Synonyms: Aliases for objects are called synonyms
CREATE SYNONYM <synonym_name> for <tablename/viewname>
DROP SYNONYM synonym_name
14, index,
An index is an object in a database. An index object is similar to a book catalog. When an index is created for a query, the Oracle database automatically uses the index. When changes are made to the table data, Oracle automatically maintains the index.
Creating an index environment: If there is a large amount of table data, the attribute values of the table do not need to be updated frequently
Not suitable for indexes: Table data is small and data attributes are frequently updated
The CREATE INDEX < index_name > ON < table_name > (field [ASC | DESC]); create index index_ename on emp (ename,asc) select * from emp where ename ='';Copy the codeFor tables with primary key and dimension 1 constraints, the database automatically creates indexes based on primary key fields and on dimension 1 constraint attributes
15. Permission control
Be familiar with Oracle database permission Settings
Log in to the system as an administrator: create an account create user account IDENTIFIED by; conn stu/123 --- > ORA-01045: user STU lacks CREATE SESSION privilege; Logon denied indicates that the current account does not have session permission. Grant permission to account grant Create session to STu grant create table to stu grant Resource to Stu: grant create view to stu; grant create sequence to stu; . Common Permissions Create Session Create TABLE Create View Create sequence Create PROCEDURE Create trigger Create function Revoke permissions Permission from account Grant an account the right to access resources under this account permission ORA-01950: In table space 'USERS' no permissions: cannot access (read or write) the USERS table space resources, the need to pass through authorized grant administrator resource to stu: after this authorization, you can access the default table space (USERS) grant permissions on table | view... To account, grant select | insert | update | delete on the users To Scott when the account is long, need permissions based on role management Create the role the create role character name. | drop role character name To role authorization grant the create session, create table, create a view, the create sequence, create the trigger, Create function create procedure resource to developer grant developer to account 1, account 2...... ; | revoke developer from user02;Copy the code16, PL/SQL
PL/SQL is an enhanced version of the SQL syntax, which only applies to Oracle CE databases. The enhanced version is mainly the basic syntax required for programming (syntax basics in procedural languages).
Constants, variables, operators, process control statements, exception handling, etc.
PL/SQL basic program structure
[DECLARE] -- DECLARE part declaration statement BEGIN -- Execute part execution statement [EXCEPTION] -- EXCEPTION handling part execution statement END;Copy the codeHello,world SET SERVEROUTPUT ON console; -- Defaults to OFF; BEGIN DBMS_OUTPUT.PUT_LINE('hello,wrold'); END; V1 BEGIN INSERT INTO DEPT(DEPTNO,DNAME,LOC) VALUES (50,' DEPTNO ',' Beijing '); --COMMIT; END; V2 DECLARE vdeptno NUMBER := 70; Vdname VARCHAR2 := '1'; Varc := ref (varc, 1) and close > = ref (close, 2); BEGIN INSERT INTO DEPT(DEPTNO,DNAME,LOC) VALUES (vdeptno,vdname,vloc); Dbms_output.put_line (' added successfully '); END; Rowtype DECLARE vdeptno scott.dept.DEPTNO%type := 70; Vdname scott.dept.DNAME%type := 'DEPT '; Vloc scott.dept.LOC%type := 'scott.dept '; BEGIN INSERT INTO DEPT(DEPTNO,DNAME,LOC) VALUES (vdeptno,vdname,vloc); Dbms_output.put_line (' added successfully '); END; Select * from deptno where deptno = 10; V1 DECLARE vno DEPT.DEPTNO%type :=10; vdname DEPT.DNAME%type ; vloc DEPT.LOC%type; BEGIN SELECT * INTO vno, vdname,vloc FROM DEPT WHERE DEPTNO=vno; DBMS_OUTPUT.PUT_LINE('dno:' || vno || ' dname:' || vdname || ',loc' || vloc); END; Select * from deptno where deptno = 10; V2 DECLARE vrow DEPT%rowtype; BEGIN SELECT * INTO vrow FROM DEPT WHERE DEPTNO='10'; DBMS_OUTPUT.PUT_LINE('DNO:' || vrow.deptno || ' DNAME:' || vrow.dname || ',LOC:' || vrow.loc); END; &declare vrow DEPT% rowType; BEGIN SELECT * INTO vrow FROM DEPT WHERE DEPTNO='& '; DBMS_OUTPUT.PUT_LINE('DNO:' || vrow.deptno || ' DNAME:' || vrow.dname || ',LOC:' || vrow.loc); END; DECLARE VROW DEPT% rowType; BEGIN SELECT * INTO vrow FROM DEPT WHERE DEPTNO='& '; DBMS_OUTPUT.PUT_LINE('DNO:' || vrow.deptno || ' DNAME:' || vrow.dname || ',LOC:' || vrow.loc); EXCEPTION WHEN no_data_found THEN dbMS_output.put_line (' no such department '); WHEN OTHERS THEN dbMS_output.put_line (' Unknown error..... '); END;Copy the codeDECLARE vScore number(3) := '& please enter a score '; BEGIN IF vscore >= 60 THEN dbms_output_line (' pass '); END IF; END; DECLARE vscore number(3) := '& please enter score '; BEGIN IF vscore >= 60 THEN dbms_output_line (' pass '); ELSE DBMS_output.put_line (' failed '); END IF; END; -- DECLARE vempno EMP.EMPNO%type :='&empno'; vsal EMP.SAL%type ; BEGIN select sal into vsal from emp where empno=vempno; IF vsal >=4000 THEN dbms_output. PUT_LINE(' null '); ELSIF vsal >=3000 THEN dbms_output. PUT_LINE(' off '); ELSIF vsal >=2000 THEN dbms_output. PUT_LINE(' null '); ELSE dbMS_output.put_line (' poverty level '); END IF; END; --------------------------------------------------------------------------------------- DECLARE v_i number :=1; v_sum number :=0; BEGIN LOOP v_sum := v_sum + v_i; v_i := v_i + 1; EXIT WHEN v_i > 100 ; END LOOP; DBMS_OUTPUT.PUT_LINE('1+2+3+... +100 = ' || v_sum ); END; DECLARE v_i number :=1; v_sum number :=0; BEGIN WHILE v_i <=100 LOOP v_sum := v_sum + v_i; v_i := v_i + 1; END LOOP; DBMS_OUTPUT.PUT_LINE('1+2+3+... +100 = ' || v_sum ); END; DECLARE v_sum number :=0; BEGIN FOR v_i IN 1.. 100 LOOP v_sum := v_sum + v_i; END LOOP; DBMS_OUTPUT.PUT_LINE('1+2+3+... +100 = ' || v_sum ); END;Copy the code17. Cursor
CURSOR (CURSOR) : it is a point to a query result set object (named CURSOR | pointer), through the CURSOR objects can be read line by line in the result set of data;
Oracle database midstream object use: declare cursor — open the cursor —- to get data — close the cursor
Declare CURSOR: CURSOR < CURSOR name > IS <SELECT statement > OPEN CURSOR: OPEN< CURSOR name > FETCH data: FETCH < CURSOR name > INTO variables 1, 2, 3,.... The variable n,; Or FETCH < cursor name > INTO row object; - remove the cursor currently a CLOSE CLOSE "cursor name > cursor object properties: % ISOPEN | % NOTFOUNDCopy the codeDECLARE vrow dept%rowtype; Curdept IS select * from dept order by deptno ASC; -- curemp cursor variable BEGIN -- 2 Open the cursor IF curdept%ISOPEN THEN CLOSE curdept; END IF; OPEN curdept; - three to get the data (the FETCH < cursor name > object INTO line | variable;) LOOP FETCH curdept INTO vrow; Move cursor object to row 1 and fetch data; DBMS_OUTPUT.PUT_LINE('DEPTNO: ' || vrow.deptno || ' ,DNAME: ' || vrow.dname || ',LOC : ' ||vrow.loc); EXIT WHEN curdept%NOTFOUND; END LOOP; -- 4 CLOSE curdept; END;Copy the code18, functions,
Function: used primarily to perform calculations and has a return value;
Definition :CREATE [OR REPLACE] FUNCTION < procedure name >[(parameter list)] RETURN data type IS [local variable declaration] BEGIN Executable statement EXCEPTION EXCEPTION processing statement END [< procedure name >]; The type of the variable :in is the default type, indicating input; Out: outputs only. In out = both input and outputCopy the code# declare a function to count employees' annual salaries based on their accounts; create or replace function fn_yearsal(eno in SCOTT.EMP.SAL%TYPE) RETURN NUMBER IS yearsal SCOTT.EMP.SAL%TYPE :=0; BEGIN select (sal + nvl(comm,0))*12 into yearsal from emp where empno=eno; return yearsal; END; set serveroutput on; declare n emp.sal%type; m emp.sal%type; Begin N :=& Enter the employee ID. m:=fn_yearsal(n); Dbms_output. Put_line (' m has a value of '| | m); end;Copy the code19, process,
Process: Oracle database uses industry encapsulation to perform operations on tables or database maintenance SQL;
CREATE [OR REPLACE] PROCEDURE < PROCEDURE name >[(parameter list)] IS [local variable declaration] BEGIN Executable statement EXCEPTION END [< PROCEDURE name >]; The type of the variable :in is the default type, indicating input; Out: outputs only. In out = both input and output Example: Define a procedure to add a piece of data to a table; CREATE OR REPLACE PROCEDURE proc_insert IS BEGIN INSERT INTO dept(deptno,dname, LOC) VALUES(50,' dept ',' Beijing '); COMMIT; END; BEGIN proc_insert(); END; Example: Define a procedure to add a piece of data to a table; CREATE OR REPLACE PROCEDURE proc_insert2(dno IN DEPT.DEPTNO%TYPE,dn IN DEPT.DNAME%TYPE,loca IN DEPT.LOC%TYPE) IS BEGIN INSERT INTO dept(deptno,dname,loc) VALUES(dno,dn,loca); COMMIT; END; DECLARE dno NUMBER := &dno; dname VARCHAR2(14) := '&dn'; loc VARCHAR2(13) := '&loc'; BEGIN PROC_INSERT2(dno,dname,loc); END # example: Create or replace PROCEDURE PROC_COUNT (dno IN NUMBER, total OUT NUMBER) IS BEGIN select count(*) into total from emp where deptno=dno; END; DECLARE DNO NUMBER; TOTAL NUMBER; BEGIN DNO := 10; PROC_COUNT( DNO => DNO, TOTAL => TOTAL ); :TOTAL := TOTAL; END;Copy the code| process | function |
|---|---|
| No return value | Returns a value |
| Perform operations | Perform data operations (statistics) |
| PROCEDURE | FUNCTION |
| DML | DQL can not call in the process | DML DQL can |
| Procedures can be run independently as PL/SQL statements | Functions cannot be run independently as PL/SQL statements (need to be placed in an expression) |
20. [Data] packages
Package: Oracle object wrapper; Oracle packages are divided into two parts: header and body
CREATE [OR REPLACE] PACKAGE < PACKAGE name > AS -- public type and object declaration -- subroutine description END; Define the BODY of the PACKAGE CREATE [OR REPLACE] PACKAGE BODY < packet name > AS -- public type and object declaration -- subroutine BODY BEGIN - initialization statement END;Copy the codeCreate or replace PACKAGE DEPTPACKAGE AS -- Organize objects created by department table operations into packages; FUNCTION getTotalRecoreds RETURN NUMBER; - 2 and 4 PROCEDURE PROC_INSERT; END ; CREATE OR REPLACE PACKAGE BODY DEPTPACKAGE AS FUNCTION getTotalRecoreds RETURN NUMBER AS num number := 0; BEGIN select count(*) into num from dept; RETURN num; END ; PROCEDURE PROC_INSERT AS BEGIN INSERT INTO DEPT VALUES (11,'aa','bb'); END; END DEPTPACKAGE;Copy the code21. Trigger
Triggers: Triggers are automatic storage procedures in Oracle databases. When the conditions are met, it is automatically executed. Oracle triggers are divided into DML triggers and system triggers. For programmers, the main business is DML triggers;
To prevent add delete | | modification operations, you just need to trigger throws an error;
RAISE_APPLICATION_ERROR (error number (20000 – to – 20999), message /,} {true | false).
The CREATE (OR REPLACE) the TRIGGER name > < triggers BEFORE | AFTER INSERT | DELETE | UPDATE [OF < name >] ON < table name > [FOR EACH ROW] BEGIN END; Example 1: CREATE OR REPLACE TRIGGER tri1 BEFORE DELETE ON DEPT BEGIN DBMS_output. PUT_LINE(' deleting number..... ' '); END; Example 2: Write an update prompt trigger (requiring each update, CREATE OR REPLACE TRIGGER tri2 AFTER UPDATE ON dept FOR EACH ROW BEGIN DBMS_output. PUT_LINE(' a data complete UPDATE..... '); END; FOR EACH ROW: Add this setting to trigger triggers automatically FOR every ROW in the table that changes. Example 3: the data in the day of rest are not allowed to modify delete | | add; create or replace TRIGGER tri3 BEFORE DELETE OR UPDATE OR INSERT ON dept BEGIN IF DELETING THEN BEGIN IF TO_CHAR(SYSDATE,'day') = 'Saturday' OR TO_CHAR(SYSDATE,'day') = 'Sunday' THEN RAISE_APPLICATION_ERROR(-20001,' Data deletion is not allowed on Saturday and Sunday.... ',true); END IF; END; END IF; UPDATING THEN BEGIN IF TO_CHAR(SYSDATE,'day') = 'Saturday' OR TO_CHAR(SYSDATE,'day') = 'Sunday' THEN RAISE_APPLICATION_ERROR(-20001,' Data modification is not allowed on Saturday and Sunday.... ',true); END IF; END; END IF; IF INSERTING THEN BEGIN IF TO_CHAR(SYSDATE,'day') = 'Saturday' OR TO_CHAR(SYSDATE,'day') = 'Sunday' THEN RAISE_APPLICATION_ERROR(-20001,' Data addition is not allowed on Saturday and Sunday.... ',true); END IF; END; END IF; END; Example 4: Automatic backup when deleting data Create or replace TRIGGER tri4 BEFORE DELETE ON dept FOR EACH ROW BEGIN INSERT INTO deptbak VALUES(:old.deptno,:old.dname,:old.loc); END; Case 5: testing: old and: new create or replace the TRIGGER tri5 BEFORE UPDATE ON dept FOR EACH ROW the BEGIN - | updated data BEFORE the UPDATE; DBMS_OUTPUT.PUT_LINE(:old.deptno); DBMS_OUTPUT.PUT_LINE(:old.dname); DBMS_OUTPUT.PUT_LINE(:old.loc); DBMS_OUTPUT.PUT_LINE('--------------------------------------------------'); DBMS_OUTPUT.PUT_LINE(:new.deptno); DBMS_OUTPUT.PUT_LINE(:new.dname); DBMS_OUTPUT.PUT_LINE(:new.loc); END; : old: indicates the original data object, : new Indicates the new data objectCopy the code22. Data backup and recovery
1, import (import) | export (export)
Table backup: Exp Account/Password @database file = Backup files log= log files tables= Tables to be backed up; Exp Scott /tiger@orcl file=f:/dbbak/databk. DMP log=f:/dbbak/databklog.log tables=emp,dept Imp Account/password @database file = backup file log= log file tables= table name fromuser=" source account "touser= Target account COMMIT = Y (transaction commit) Ignore = Y (duplicate data ignore) IMP scott/tiger@orcl file=f:/dbbak/databk.dmp log=f:/dbbak/databklog.log tables=emp fromuser=scott touser=scott commit=y Ignore =y user mode: exp scott/tiger@orcl file=f:/dbbak/databk2.dmp log=f:/dbbak/databklog2.log imp scott/tiger@orcl File =f:/dbbak/databk2. DMP log= F :/dbbak/databklog2.log tables=emp fromuser= Scott touser= Scott commit=y ignore=y full library exp system/orcl@orcl file=f:/dbbak/databk3.dmp log=f:/dbbak/databklog3.log imp sys/orcl@orcl file=f:/dbbak/databk3.dmp log=f:/dbbak/databklog3.log full=y ignore=y destroy=yCopy the code2. Cold backup
If you want to do a cold backup, you need to back up several core contents of the database:
● Control file: refers to the core file that controls the instance service of the entire Oracle database, found directly through the “V $contronlFile”;
● redo logfile: can be used for database disaster recovery, directly through the “v$logfile” find;
● Data files: tablespace files found by “V datafile” and “vdatafile” and “vdatafile” and “vdatafile”;
● Core operation configuration file (pfile), run “show paramter pfile” to find;
In a real Oracle deployment, all files are stored on separate hard disks to achieve IO balance.