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
- 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
- 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
- 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
,revoke
Right of recovery,commit
Rollback 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
- 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
- duplicate removal
Key words: Instinct
select distinct deptno from emp
Copy the code
- 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
- 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
- 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
- is null
- is not null
- 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
- Enable Disable: enable, disable: whether to enable authentication constraints on newly changed data
- 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