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

2. 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

3. 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

2. De-duplication

Key words:

the SELECT  DISTINCT deptno from emp
 copy the code

3. Alias

Method 1: Use

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

Method 2: Omit keywords

select ename name, sal salary from emp;
 copy code

4. Sort

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

5. 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

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