I. Introduction to database

The database

Generalized database

  • Is a collection of physical operating systems and disks

Narrow database

  • Database + Database operating system

Oracle: Data structure located in physical memory, which is made up of a shared memory pool lock by multiple background processes of the operating system. The shared memory can be accessed by all processes

Storage structure:

  • Physical storage

  • Logical structure

    Database --> table space --> Segment --> extent --> Oracle data blockCopy the code

SQL language

DDL (Data Definition Languages)

  • Data definition language
  • Defines different data segments, databases, tables, columns, indexes, and other database objects
  • CREATE DROP ALTER RENAME TRUNCATE
  • It is often used by database administrators (DBAs)
  • Used to define or change the structure of a table, data types, joins and constraints between tables, etc
  • operation
  1. Create a table
Create a table
create table name(Field name type (length),.....) ;Copy structure from other tables
create table name1 as selectField listfromExisting tablewhere 1! =1;
Copy the code
  1. Alter table structure
Alter table name
renameThe original name of the tabletoThe new name of the table-- Change the column name
alter tableThe name of the tablerename columnThe column nametoThe new column-- Change the field type
alter tableThe name of the tablemodify(Field type)Add columns -
alter tableThe name of the tableaddThe field type- delete columns
alter tableThe name of the tabledrop columnfieldCopy the code
  1. Delete table
drop tableThe name of the table.Copy the code

2. Data Manipulation Language (DML)

  • Data manipulation statement
  • Used to add, delete, update, and query database records, and check database integrity
  • insert.delete.update.select
  • Perform operations on the table

3, DCL (Data Control Language)

  • Data control for
  • Statements used to control the level of permission and access directly to different data segments
  • Defines databases, tables, fields, user access rights, and security levels
  • grant,revokeRight of recovery,commitRollback Rollback the transaction

Create a user and table structure

1 sys login

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

2 Create a tablespace

create tablespaceTable space namedatafile 'path \ filenames.dbf' size 200m;
Copy the code

Create a TDS file

3 Creating a User

create user scott identified by tiger default tablespace scott_tb_space;
create userThe user nameidentified bypassworddefault tablespaceTable space;Create users and specify table Spaces
Copy the code

4 Authorization Rights

grand dba to scott; Grand DBA to username;Copy the code

5 Login as a common user

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

Three, table design

table

The database stores data in tables as organizational units, used to store the information of some things;

Table name + storage information

The constraint

Primary key constraint

The primary key is used to locate a single row in a table. It uniquely determines that a row of a table is unique and not empty. A table can have only one primary key

Uniqueness constraint

Unique ensures that each row is unique, but allows multiple nulls

Not null constraint

Not null is not empty

Foreign key constraints

Foreign Key Primary table: referenced table A table that is referenced from a secondary table must comply with the requirements of the primary table

The check constraint

check

Select statement

  1. Said the query
select  *|colname[,...] from table [alians]
-- * Can be replaced with specific information about all content
select deptno,dname from dept;
Copy the code
  1. duplicate removal

Key words: Instinct

select distinct deptno from emp
Copy the code
  1. The alias

Method 1: Use the as keyword

select ename asName, salaswagefromEmp;Copy the code

Method 2: Omit key words

selectEname name, sal salaryfrom emp;
Copy the code
  1. The sorting

Order by keywords

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

Handle null values

Mode 1 NVL () function

The first argument is the number to be returned when not null, and the second argument is the number to be returned when null
select ename, sal, comm, sal+somm asMonthly income,from emp; -- When comm is null, sal is null, affecting data results
select ename, sal, comm, sal+somm + nvl(comm,0)  asMonthly income,from emp;
select nvl(1.100) from dual;
select nvl(null.100) from dual;
select * from emp where vvl(comm,0) < =0;
Copy the code

Way 2

Nulls First and NULls LAST are used for sorting

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

A null value judgment

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

Pseudo columns and virtual tables

The pseudo column

A column that should not exist, and then temporarily add another column as needed

Salary - o
select ename, sal, 1.from emp;
select ename , sal, sal*12 asAnnual salaryfrom emp;
Copy the code

Virtual table

  • Dual virtual table
  • The syntax used to form a SELECT is guaranteed by Oracle to always have only one data for Dual
  • This table has only one row and one column and is used to select system variables or evaluate an expression
  • You can perform insert update, delete, and drop operations ————, but if you perform drop, the system will crash
  • You can use this table when you need an object to complete some information

Such as

select 9899*888 from dual;

7. Query conditions

  • Conditions of the query

Classification:

  • >, > =, > =, < =
  • ! ^ = =, < >
  • between … And: indicates a closed interval
  • not
select * from table where
select * from emp where deptno =10
select ename, deptno  from emp wheredeptno ! =10
select * from emp where ename ='SMITH'
Copy the code
  • Conditional join operator
    • and
    • or
    • not

Eight, fuzzy query

  • % : fuzzy character with variable digits
  • like
select * from emp where like ’%S%' select * from emp where ename like '%a%%' escape ('a');
select * from emp where ename like '%aaa%%' escape('a')
select * from emp where ename like '%a%%a_%' escape('a')Copy 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 from salgrad where grade =2)
Copy the code

Group by group

select.from.where.group by.select avg(sal) from emp group by deptno;
select count(*) from emp group by deptno;
Copy the code

Having filtering

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

Xii. Functions

String concatenation | |

select ename from emp;
select ename, ename ||"A" aliasfrom emp;
select ename ,comm,ename || comm  as test from emp When null is present, no concatenation is performed
Copy the code

A single function

  • When the corresponding table records, a record returns a result
-- string function

-- concat(x,y) concates the strings x and y
select ename||job as namejob from emp;
select concat(ename,job) from emp;

-- instr (x, STR,start,n); Looking for STR in x, you can specify that it starts at start, or you can specify that it starts at the NTH time to return the string position
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 lowercase
-- upper (x) : x converts to uppercase

-- ltrim (x, trim_str) : Truncate the strim_str string from the left side of x
select ltrim(' abc abc ') from dual

-- rtrim (x, trim_str) : truncates the strim_str string to the right of x, with Spaces truncated 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) : Finds old from x and replaces it with new

-- substr(x, start,length) : returns x string, starting from start, truncating length characters, default length, default to end
Copy the code
-- Mathematical function
-- ABS (x) : take the absolute value
-- ceil(x) : rounded up
-- floor
-- mod(x,y)
Copy the code
-- Date function

-- sysdate: indicates the current system time, without parentheses
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 DI plus N1 months
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 last day of fishmeal where date d1 resides
select last_day(hireday) ,hiredauy from emp;

-- months_between(d1,d2) Returns the number of months between dates D1 and d2
select sysdate, hiredate, months_betweem( sysdate, hiredate)from emp;
select sysdate, hiredate, months_betweem(hiredate, sysdate )from emp;

-- next_day(d1,[,c1]) -- next_day(d1,[,c1]
select next_day(sysdate.'Monday')  asIn the timefrom dual;
Copy the code
-- Conversion function
-- to_char(x,c) converts the date or number x to the char data type in c format
select hiredate ,to_char(hiredate, 'mm'-'dd'-'yyyy') from emp;
select hiredae, to_char(hiredate, 'yyyY-YYYY') from emp;

-- to_date(x,c) converts the string x to a date in c format
select to_date('1900/1/1'.'yyyy//mm/dd')

-- co_number(x) converts the string x to a numeric font
select to_number('11') +1 from dual;
Copy the code

Multi-line function

  • Also called aggregate functions or combinatorial functions
  • You can operate on multiple records at the same time and return a result
  • AVG.SUM.MIN.MAX.COUNT
  • Note null cannot participate in the operation
select count(nvl(comm,0)) from emp ;
Copy the code

13. Paging

  • There is too much in the list to display in pages
  • plan
    • Query all the data in the database at once, and then display the specified records in a summary on each page — fake pages
    • Query the database for several times, each time to obtain the data of this page and display
-- Number each record in each result set, starting with 1
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 where rownum always starts at 1

select ename, sal, deptno, rownum from emp; -- Produces the moth pseudocolumn
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 the code

Fourteen, to heavy

  • rowid

The address value used to locate a relative displacement of a record in the database. Normally, this value is determined at the row where the data is inserted into the database table but is only a pseudo column that does not exist in the table. A pseudo column encoded according to the physical address information of each row of data can find the physical address information of a row of data according to the ROWID of a row of data, so as to quickly locate the row of data. Rowid is the fastest to locate a single record

  • Lookup of 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 the code

Table link

  • Rows of one table are joined with rows of another table to form a new procedure according to specified conditions

  • 92 syntax

select .. from t1,t2,t3,... where ... Simplified table names may exist in the case of self-join principle: according to the order of the table after the from, the first table as memory for loop, after the table as outer city for loop Cartesian product equivalent join and non-equivalent join (! =, >, <, <>, between and) select ename, sal from emp, salgrade s where e.sal between losal and hisal; Select * from emp as e,emp as m where e.mgr = m.emno; select * from emp as m where e.mgr = m.emno; Outer join: can be left outer join, right outer join, or full outer linkCopy the code
select * from dept as d, (select count(*), deptno from emp group by deptno) as c where d.deptno = c.deptno(+);  -- "+" indicates 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 the code
  • 99 syntax
-- Cross join to implement cartesian product
select * from dept cross join emp;

-- Natural join, do equivalent join, require same name or primary foreign key
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 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 equivalent join, non-equivalent join, self-join, solve all join, 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 with 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 syntax 92 and syntax 99
content 92 99
In the connection select … from t1, t2 where t1.a = t2.b and t1.c = 1 select … from t1 cross join t2 where…

select … from t1 natural join t2 where…

select … From t1 join T2 where…

select … From t1 join t2 on where…
Outer join select … from t1, t2 where t1.a= t2.b(+) select … From t1 left/ right [outer] join T2 on/using
All connection Both tables are primary tables

Select T1 full join T2 on JOIN condition WHERE

Set operation

A collection of classification

union

Union, de-duplication, union operation of two result sets, excluding repeated lines, default order according to the rule

union all

Complete set, no weight, union operation of two result sets, including repeated lines, no sorting

interset

Intersection, find duplication, intersection of two result sets, excluding repeated lines, default rule ordering

minus

Difference set, minus repetition, difference set operation on two result sets, excluding repeated lines, default rule sort

requirements

Two result sets that require the number of fields and the field type to correspond with one

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; -- Take the union, ab, two of them

select 'a'.'b' from dual
union
select 'c'.'d' from dual
union all
select 'a' , 'b' from dual; -- The complete set is not heavy

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' fromDual)minus
（ select 'a'.'b' from dual 
union 
select 'e'.'f' fromDual)-- cd
Copy the code

17. Data types

  • VARCHAR2 (size)

    Variable length string, 1:4000

  • NVARCHAR2 (size)

    Variable length string. The maximum length must be specified according to the desired national character set

  • NUMBER

  • LONG

  • DATA

  • RAW (size)

  • LONG RAW

  • CHAR (size)

  • NCHAR (size)

  • CLOB

  • NCLOB

  • BLOB

  • BFILE

Create table with constraint

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'.'woman')),
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 the code

Create a constraint with a name

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 ck_user_gender check (gender in ('male'.'woman')),
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 the code

Create and append constraints

create table t1(
userid  number(5The 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('', 'female'));
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 the 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);- Forcibly do 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 null
alter table t2 add constaint fk_txt_ref_user_id foreign key(userid) references t1(userid) on delete cascade; -- Cascading deletion


Copy the code

Disable and enable constraints

  1. Enable Disable: enable, disable: whether to enable authentication constraints on newly changed data
  2. Validation and non-validation: validate and novalidate whether constraint validation is performed on objectively existing data in the table
  • Enable validate: Indicates the default combination of constraints. Rows that violate the constraint cannot be added to the table or exist in the table
  • Enable novalidate: Constraint violating rows cannot be added, but existing row violating rows are not validated
  • Disable validate: You can add rows that violate constraints but do not validate existing rows that violate constraints
  • Disable novalidate: Adds rows that violate constraints and does not validate existing rows that violate constraints

Remove the constraint

alter table t2 drop constraint uq_user_email cascade;
Copy the code

In the 19th, DML

Data control statements manipulate data contained in database objects

  • Insert: Inserts a record into a table
  • Update: Modifies the contents of existing and table records
  • Delete: Deletes one or more records from a data table