Oracle basics

Oracle basics

1. Database introduction

database

Generalized database

  • Is a collection of physical operating systems and disks

Narrow database

  • Database + database operating system

Oracle: The data structure located in the physical memory is made of a shared memory pool lock with multiple background processes of the operating system. The shared memory can be used by all processes.

Storage structure:

  • Physical storage

  • Logical structure

    Database -> tablespace -> segment -> area -> the Oracle data block copy the code

SQL language

1. DDL (data definition languages)

  • Data definition language
  • Defines database objects such as different data segments, databases, tables, columns, indexes, etc.
  • CREATE
    DROP
    ALTER
    RENAME
    TRUNCATE
  • Often used by database administrators (DBA)
  • Used to define or change the structure of the table, data types, connections and constraints between tables and other initialization work, most of them are used in the creation of the table
  • operating
  1. Create table
- Create a table create table name (field name type (length),...); - Copy structure from other tables create table name1 as select field list from existing table where 1 != 1 ; Copy code
  1. Modify the table structure
- Modify table name rename original table name to new table name - Modify column name alter table table name rename column column name to new column name - Modify field type alter table table name modify (field type) - Add column alter table table add field types - delete the column the ALTER the table table name drop column fields copy the code
  1. Delete table
drop table table name; copy code

2. DML (data manipulation language)

  • Data manipulation statement
  • Used to add, delete, update and query database records, and check database integrity
  • insert
    ,
    delete
    ,
    update
    ,
    select
  • Operate in the table

3. DCL (data control language)

  • Data control is used
  • Statements used to control the direct permissions and access levels of different data segments
  • Defines the database, tables, fields, user access permissions and security levels
  • grant
    ,
    revoke
    Recycling rights,
    commit
    Commit transaction, rollback rollback transaction

2. create users and table structure

1 sys login

conn sys/root as sysdba select instance_name from v$ instance ; Copy code

2 Create a table space

create tablespace tablespace name datafile'path \file name.dbf' size 200 m; copy code

Create a tds file

3 Create user

the Create the User scott IDENTIFIED by Tiger default TABLESPACE scott_tb_space; the Create the User username IDENTIFIED by password default TABLESPACE table space; - create a user and set the table space Copy the code

4 Authorization

grand dba to scott; grand dba to username; Copy code

5 Ordinary user login

conn scott/tiger@xe select * from dual; copy the code

3. table design

table

In the database, the table is the organizational unit to store data, which is used to store information about some things;

Table name + storage information

constraint

Primary key constraint

The primary key is
used to locate a single row in the table, which can uniquely determine that a row of the table is
unique and non-empty.
A table can only have one primary key

Uniqueness constraint

unique
ensures the uniqueness of each row, but allows multiple null values

Non-empty constraint

not null
non-empty

Foreign key constraint

foreign key
master table: Table referenced
from a table referenced table, the primary table must follow the requirements

Check constraint

check

4. the select statement

  1. Express query
select *|colname[,...] from table [alians] - * You can replace select deptno, dname from dept; with specific information of all contents ; copy code
  1. De-duplication

Key words:

instinct

the SELECT DISTINCT deptno from emp copy the code
  1. Alias

Method 1: Use

as
Key words

select ename as name, sal as salary from emp; copy code

Method 2: Omit keywords

select ename name, sal salary from emp; copy code
  1. Sort

order by
Key words

select ename,sal from emp order by sal desc ; select ename, sal, deptno from emp order by deptno asc , sal desc ; copy code
  1. Execution order

5. deal with null values

Method 1 nvl() function

- The number returned when the first parameter is not empty, and the second parameter, the number returned when empty select ename, sal, comm, sal+somm as monthly income, from emp; - When comm is empty, sal is also empty, affecting the data results select ename, sal, comm, sal+somm + nvl(comm, 0 ) as monthly income, from emp; select nvl( 1 , 100 ) from dual; select nvl( null , 100 ) from dual; select * from emp where vvl(comm, 0 )<= 0 ; Copy code

Way 2

nulls first/nulls last for sorting

select * from emp order by comm desc nulls first/nulls last ; copy code

Null value judgment

  1. is null
  2. is not null
  3. not ... is null

6. pseudo columns and virtual tables

Pseudo column

A column that shouldn t exist, and then temporarily add another column as needed

- Seeking annual salary select ename, sal, 1 , from emp; select ename, sal, sal * 12 as annual salary from emp; copy code

Virtual table

  • dual virtual table
  • Used to form the grammatical rules of select, Oracle guarantees that dual will always have only one piece of data
  • The table has only one row and one column, which is used to select system variables or find the value of an expression
  • Insert update, delete and drop operations can be performed, but if drop is executed, the system will crash
  • Select to complete some information, you can use this table when you need to use an object

Such as

select 9899*888 from dual;

7. query conditions

  • Conditional query

classification:

  • >
    ,>,=,>=,<=
  • !=,<>,^=
  • between ...and: closed interval
  • not
SELECT * from Table WHERE SELECT * from EMP WHERE DEPTNO = 10 SELECT ename, DEPTNO from EMP WHERE DEPTNO! = 10 SELECT * from EMP WHERE ename = 'SMITH' duplicated code
  • Conditional concatenation operator
    • and
    • or
    • not

8. Fuzzy query

  • %: ambiguous characters with variable digit
  • like
select * from emp where like ' % S % ' * from EMP WHERE ename SELECT like ' % A % % ' Escape ( ' A '); * from EMP WHERE ename SELECT like ' % AAA % % ' Escape ( ' A ') * from EMP WHERE ename SELECT like ' % A % % A_ % ' Escape ( ' A ') copying the code

9. where clause

select * from where group by ... having ... order by ... select * from emp where deptno = ( select deptno from dep where dname = 'SALES' ) select * from salgrade where sal between ( select losal from salgrade where grade = 2 ) and ( select hisal fromsalgrad WHERE Grade = 2 ) copy the code

10. group by grouping

the SELECT ... from .... the WHERE ... Group by ... the SELECT AVG (SAL) from emp Group by deptno; the SELECT COUNT (*) from emp Group by deptno; Copy the code

Eleven, having filtering

select deptno. coumt(*), from emp where sal> 2000 group by deptno having count (*) >= 2 ; Copy code

12. Function

String splicing||

SELECT ename from EMP; SELECT ename, ename || "A" alias from EMP; SELECT ename, COMM, ename || COMM AS Test from EMP - when there is null, the splice will not copy the code

One-line function

  • When corresponding table records, one record returns one result
- String functions - concat(x,y) connect string x and y select ename||job as namejob from emp; select concat (ename,job) from emp; - instr(x,str,start,n); to find str in x, you can specify to start from start, you can specify to start from the nth time, and return the position of the string select instr ( 'helloworld' , 'e' ) from dual select instr ( 'helloworld' , 'a' ) from dual select ename, instr (ename, 'A' ) from emp; - length(x): returns the length of x - lower (x): x is converted to lower case - upper (x): x is converted to upper case - ltrim(x, trim_str): truncate the strim_str string on the left of x, by default truncating spaces select ltrim ( 'abc abc' ) from dual - rtrim(x, trim_str): cut the strim_str string on the right side of x, and cut off spaces by default select rstrim( 'abc abc' )|| 'a' from dua; select rstrim(lstrim( 'abc abc' )) from dual select concat (rstrim(lstrim()), 'a' ) from dual; - replace(x,old,new): find old from x and replace it with new - substr (x, start, length ): Returns the string x, beginning from the start, the interception of length characters, the default length, to the end of the default copy the code
- Mathematical functions - abs (x): absolute value - ceil (x): rounding up - Floor - MOD (X, Y) copy the code
- Date function - sysdate: current system time, no brackets select sysdate from dual; select sysdate + 10 from dual; - current_date returns the current system date without parentheses - add_months(d1, n1) returns the new date after n1 months after the addition of di selct empno, ename, hiredate, add_months(hiredate,3) from emp; selct empno, ename, hiredate, add_months(hiredate,-3) from emp; - last_day(d1) returns the date of the last day of the fishmeal where the date d1 is located select last_day (hireday) ,hiredauy from emp; - months_between(d1,d2) returns the number of months between date d1 and date d2 select sysdate , hiredate, months_betweem( sysdate , hiredate) from emp; select sysdate , hiredate, months_betweem(hiredate, sysdate ) from emp; - next_day (d1, [, c1 ]) Returns the date d1 next week, week c1, date SELECT NEXT_DAY ( SYSDATE , 'Monday' ) AS entry time from Dual; duplicated code
- Conversion function - to_char(x,c) convert date or number x into char data type according to the format of c select hiredate ,to_char(hiredate, 'mm' - 'dd' - 'yyyy' ) from emp; select hiredae , to_char(hiredate, 'mm"month"dd" " yyyy "year"' ) from emp; - to_date(x,c) convert the string x into a date according to the format of c select to_date ( '1900/1/1' , 'yyyy//mm/dd' ) - co_number(x) converts the string x into a number select to_number( '11' ) + 1 from dual; copy the code

Multi-line function

  • Also called aggregate function or composite function
  • Can operate on multiple records at the same time and return a result
  • AVG
    ,
    SUM
    ,
    MIN
    ,
    MAX
    ,
    COUNT
  • Note that null cannot participate in calculations
select count (nvl(comm, 0 )) from emp ; copy code

Thirteen, paging

  • Too much list content, use pagination to display
  • Program
    • Query all the data in the database at one time, and then summarize and display the specified records on each page-false pagination
    • Query the database multiple times, and get the data on this page each time and display it
- For the number of each record in each result set, select ename, sal, deptno, rownum from emp; select ename, sal, deptno, rownum from emp where deptno = 30 ; select ename, sal, deptno from emp where rownum <= 5 ; - query the first page of data, 5 data per page select ename, sal, deptno from emp where rownum <= 5 ; - then query the second page, rownum Always start at 1 select ename, sal, deptno, rownum from emp; - Generate clothes moth pseudo column select * from ( select ename, sal, deptno, rownum as rw from emp) where rw> 1 ; select * from ( select ename, sal, deptno , rownum as rw from emp) where rw> 5 and rw <= 10 ; select ename, sal, deptno, r1 r2 from ) select ename, sal, deptno r1, rownum r2 from ( select ename, sal, deptno, rownum r1 from emp order by sal desc )) where r2 <= 3 ; Copy code

14. Removal

  • rowid

Address value for a relative displacement in a location database recorded
normally, the value of the data into the database table row is the only i.e. it determines
a pseudo-column does not exist in the table
read in oracle The data row in the table is a pseudo column encoded according to the physical address information of each row of data.
According to the rowid of a row of data, the physical address information of a row of data can be found, so that the
rowid of the data row can be quickly located, which is the fastest for single-record positioning. of

  • Finding duplicate records
create table copy as select * from dept; select * from copy ; select deptno, dname, loc, rowid from copy order by deptno; insert into copy select * from dept; commit ; select min (rowid) from copy group bu deptno,dname,loc; select * from copy where row not in ( select min (rowid) from copy group by deptno,dname,loc); delete from copy where rowid not in ( select min (rowid) from copy group bu deptno,dname,loc ); commit ; copy code

15. Table link

  • The rows of one table are connected with the rows of another table according to the specified conditions to form a new process

  • 92 Grammar

select .. from t1,t2,t3,... where ... Simplify table name There may be self-connection Principle: According to the order of appearance of the table behind from, the preceding table is used as a for loop in memory, and the table appearing later is used as a for loop in the outer city. Cartesian Product Equivalent connection and non-equivalent connection (!=, >, <, <>, between and) select ename, sal, hiredate, grade from emp e, salgrade s where e.sal between losal and hisal; Self-join: Use the comparison operator to match the rows in the two tables based on the columns shared by each table select * from emp as e,emp as m where e.mgr = m.empno; External connection: it can connect to the left, connect to the right, or complete the external link Copy code
select * from dept as d, ( select count (*), deptno from emp group by deptno) as c where d.deptno = c.deptno(+); - "+" stands for non-primary table select d.deptno, dname , loc, nul(cc, 0 ) from dept d, ( select count (*) cc, deptno from emp group by deptno) c where d.deptno = c.deptno select * from emp e, emp m where e.mgr = m.empno(+); Copy code
  • 99 Grammar
- cross join cross connection to achieve Cartesian product select * from dept cross join emp; - natural join: natural join, do equivalent joins, require columns with the same name or primary and foreign keys select ename, empno, deptno, dname from emp natural join dept; select ename, deptno, dname from emp natural join dept where deptno = 10 ; - join using: Equivalent join, must have the same name column to join select enmae,empno,deptno,dname from emp join dept using (deptnu); select ename,deptno, dname from emp join using (dept no ) where deptno = 10 ; - join on: can do equivalence join, non-equivalent join, self join, resolve all joins, the relational column must be distinguished select ename, empno, e.deptno, dname from emp e join dept d on e.deptno = d .deptno select ename, sal, e.deptno, grade, dname from emp e join dept d on e.deptno = d.deptno join salgrade on e.sal between losal and hisal where e,deptno = 30 ; - Outer join: outer join, there are primary and secondary tables left [outer] join on left [outer] join using right [outer] join on right [outer] join using - full join on | using copy the code
  • The difference between 92 syntax and 99 syntax
content9299
Internal connectionselect ... from t1, t2 where t1.a = t2.b and t1.c = 1select ... from t1 cross join t2 where...
select ... from t1 natural join t2 where...
select ... from t1 join t2 using (field of the same name) where...
select ... from t1 join t2 on connection condition where...
Outer joinselect ... from t1, t2 where t1.a = t2.b(+)select ... from t1 left/right [outer] join t2 on/using join conditions
Fully connectedBoth tables are the main table
select t1 full join t2 on join conditions where

16. Set operation

Collection classification

union

Union, de-duplication, union operation on two result sets, excluding duplicate rows, the default sorting is in accordance with the rules

union all

Complete set, no de-duplication, union operation of two result sets, including duplicate rows, no sorting

interset

Intersection, find duplicates, perform intersection operations on the two result sets, excluding duplicate rows, sort by default rules

minus

Difference set, subtract duplicates, perform difference set operation on two result sets, do not include duplicate rows, default ordering

Claim

Two result sets, requiring a one-to-one correspondence between the number of fields and the field type

select'a ' , 'b' from dual; select'c ' , 'd' from dual; SELECT 'A' , 'B' from Dual Union SELECT 'C' , 'D' from Dual SELECT 'A' , 'B' from Dual; - seek and set, ab, two, deduplication select'a ' , 'b' from dual union select'c ' , 'd' from dual union all select'a ' , 'b' from dual; - the complete set does not remove duplicates select'a ' , 'b' from dual union select'c ' , 'd' from dual union all select'a ' , 'b' from dual; - ab ( Select'a ' , 'b' from dual union select'c ' , 'd' from dual) minus ( select'a ' , 'b' from dual union select'e ' , 'f' from dual) - cd Copy code

Seventeen, data type

  • VARCHAR2(size)

    Variable length character string, 1:4000

  • NVARCHAR2(size)

    Variable-length character string, according to the required national character set to define the maximum length must specify the length

  • NUMBER

  • LONG

  • DATA

  • RAW(size)

  • LONG RAW

  • CHAR(size)

  • NCHAR(size)

  • CLOB

  • NCLOB

  • BLOB

  • BFILE

Eighteen, create a table with constraints

create table t1( userid number ( 5 ) primary key , username varchar2 ( 30 ) check ( length (username between 4 and 20 ) not null , userpwd varchar2 ( 20 ) not null check ( length (userpwd) between 4 and 18 ), age number ( 3 ) default ( 18 ) check (age>= 18 ), gender char ( 3 ) default ( 'Male' ) check (gender in ( 'Male' , ' ' )), email varchar2 ( 30 ) unique , regtime date default ( sysdate ) ); create table t2 ( txtid number ( 5 ) primary key , - primary key constraint title varchar2 ( 32 ) not null check ( length (title)>= 4 and length (title) <= 30 , txt varchar2 ( 1024 ), pubtime date default ( sysdate ), userid number ( 5 ) reference t1(userid) on delete set null ) Copy code

Create a named constraint

create table t1( userid number ( 5 ), username varchar2 ( 30 ) contraint user_name not null , userpwd varchar2 ( 20 ) constraint not null , age number ( 3 ) default ( 18 ), gender char ( 3 ) default ( 'Male' ), email varchar2 ( 30 ), regtime date default ( sysdate ) constraint ke_uyser_id primary key (userid), constraint ck_user_name check ( length (username) between 4 and 20 ) constraint ck_user_pwd check ( length (userpwd) between 4 and 18 ), constraint ck_user_age check (age> = 18 ) , constraint A ck_user_gender the Check (Gender in ('male', 'Female' )), constraint ck_user_email unique (email) ); create table t2 ( txtid number ( 5 ), title varchar2 ( 32 ) nn_txt_title not null , txt varchar2 ( 1024 ), pubtime date default ( sysdate ), userid number ( 5 ), constraint pt_txt_id primary key (txid), constraint ck_txt_title check ( length (title)>= 4 and length (title) <= 30 constraint fk_txt_user_id foreign key (userid) references tb_user(userid) on delete set null ) Copy code

Create and append constraints

create table t1( userid number ( 5 ), username varchar2 ( 30 ), userpassword varhcar2( 20 ), age number ( 3 ), gender char ( 2 ), email varchar2 ( 30 ), regtime time default ( sysdate ) ); alter table t1 add constraint pk_us_id primary key (userid); alter table t1 add constraint ck_user_name check ( length (username) between 4 and 28 ); alter table t1 add constraint ck_user_pwd check ( length (userpwd) between 4 and 18 ); alter table t1 add constraint ck_user_age check(age>=18 ); alter table t1 add constraint ck_user_gender check (gender in (' ',' ')); alter table t1 add constraint uq_user_email unique(email); alter table t1 modify(username constraint nn_user_name not null); alter table t1 modify(userpawd constraint nn_user_pwd not null); alter table t1 modify (age default(18) alter table t1 modify Copy code
create table t2 ( txtid number ( 10 ), title varchar2 ( 32 ), txt varchar2 ( 1024 ), pubtime date , userid number ( 5 ) ); alter table t2 add constraint pk_txt_id primary key (txtid); later table t2 add constraint ck_txt_id check ( length (title) >= 4 and length (title)<= 30 ); alter table t2 add constaint fk_txt_ref_user_id foreign key (userid) references t1(userid); - Mandatory not delete alter table t2 add constaint fk_txt_ref_user_id foreign key (userid) references t1(userid) on delete set null ; - automatically set to empty alter table t2 addconstaint fk_txt_ref_user_id foreign key (userid) references t1(userid) on delete cascade ; - cascade delete Copy code

Disable and enable constraints

  1. Start disabled: enable, disable, whether to start verification constraints on newly changed data
  2. Validation, non-validation: validate, novalidate, whether to perform constraint validation on the objectively existing data in the table
  • enable validate: The default constraint combination state, data rows that violate constraints cannot be added, and data rows that violate constraints cannot exist in the data table.
  • enable novalidate: Cannot add data rows that violate constraints, but do not validate existing data rows that violate constraints
  • disable validate: You can add data rows that violate constraints, but do not validate existing data rows that violate constraints
  • disable novalidate: You can add data rows that violate constraints, and do not validate existing data rows that violate constraints

Delete constraint

alter table t2 drop constraint uq_user_email cascade ; copy code

19. DML

Data control statements manipulate data contained in database objects

  • insert: insert a record into the data table
  • update: modify the content of records that already exist and in the table
  • delete: delete one or more records in the data table