Oracle learning - database operation

Keywords: Database Oracle Java SQL

  • Oracle tables associated with users
  • Each user needs to determine the table storage location, which is called table space
  • In Oracle, table space is divided into two parts: permanent table space and temporary table space
    • Temporary table space: when using SQL statement operation, the result enters the temporary table space first
    • Persistent tablespace: when commit is executed, the data will move from the temporary tablespace to the persistent tablespace.

User rights and RBAC (learn)

  • Give user dba role
grand dba to user name
  • RBAC (role based access control)
    • Enhance the maintainability of permissions

1. Create user

  • grammar

    --Create user
     create user user name
     identified by password
     default tablespace
     Temporary tablespace
     Quota size on tablespace; / / the quota size currently used for the specified tablespace
    
  • example

    -- Create user
    create user tqyl
    identified by tqyl
    default tablespace tqyl
    temporary tablespace tqyltemp
    quota 50m on tqyl;
    
    --Give users all rights(Administrator rights dba)
    -- grant dba to user name;
    grant dba to tqyl;
    

2. Create tablespace (permanent)

  • Basic grammar

    create tablespace Tablespace name
    datafile 'Tablespace save location'
    size Default tablespace size
    autoextend on;    -- The space size is automatically increased. If the storage capacity exceeds the initial size, the capacity will be automatically expanded
    
  • example

    create tablespace tqyl
    datafile 'E:\workspace\Java\Oracle\space\tqyl.dbf'
    size 10m
    autoextend on;
    
  • Delete tablespace

    -- Delete tablespace
    drop tablespace tqyl
    including contents --Delete table contents
    and datafiles;     --Delete tablespace physical files   
    

3. Create a temporary tablespace

  • grammar

    • There are two differences between creating a table space and creating a table space: temporary and tempfile
    -- Create a temporary tablespace
    create temporary tablespace Temporary tablespace name
    tempfile 'Temporary tablespace location'
    size 10m
    autoextend on;
    
  • example

    -- Create a temporary tablespace
    create temporary tablespace tqyltemp
    tempfile 'E:\workspace\Java\Oracle\space\tqyltemp.dbf'
    size 10m
    autoextend on;
    

4. Table operation

4.1 create table

  • Create a new table
create table table name(
	Field name type [constraint],
    Field name 2 type [constraint],
);
  • Copy a table (structure + data)
create table table name as query statement;
  • data type
classification type describe
value type NUMBER Indicates that the maximum number of integers and floating-point numbers (5) can be saved is 99999 number (5,2) and the maximum number can be saved is
INT/INTEGER integer
FLOAT Double precision floating point
Character type CHAR(n) Fixed length character
VARCHAR2(n) Variable length character
Date type DATE Date, accurate to seconds
TIMESTAMP Date type, accurate to decimal seconds
Large object type LOB 8-12TB data and can be processed in sections
CLOB Store characters, up to 4 G
BLOB Store binary data, up to 4 G
  • example

    -- Create student table
    create table student(
      stuid int,
      stuname varchar2(20),
      sex char(1),
      age number(3,0),
      birthday date,
      email varchar2(20),
      phone char(11)
    );
    
    -- Copy a table
    select * from hr.employees;
    create table emp as select * from hr.employees;
    

4.2 modification table

Specific operation data of alter table name;
--Add
 alter table table name add (column description,...);
--Modification
 alter table table name modify (column description,...);
--Delete column
 alter table table name drop column column name;

4.3 delete table

drop table table name

5. Constraints

5.1 what are constraints?

A set of mechanisms to ensure the integrity and consistency of database data.

  • We use constraints to limit the data in the table (can't be added, modified or deleted at will)

5.2 common constraints

keyword describe characteristic
primary key Key constraint Columns decorated by keyword constraints are not allowed to be empty or duplicate
not null Nonnull constraint Columns decorated by non empty constraints cannot be empty
unique Unique constraint Columns decorated by unique constraints are not allowed to repeat
default Default constraint If the column decorated by the default constraint is null only, the default value is used
foreign key Foreign key constraint Describe one to many relationships through primary foreign key relationships
==check== Check constraints When the column decorated by the check constraint modifies data, the check condition is used for verification

5.3. Primary key constraint

  • Add constraint
-- Primary key constraint
-- Method 1: add constraints when creating tables and declaring fields (Only one field can be specified as the primary key)
create table pk01(
  pid int primary key,
  name varchar2(20)
);
-- Method 2: create a table, declare the fields, add constraints (you can set a union primary key) [recommended]
create table pk02(
  pid int,
  name varchar2(20),
  constraint pk02_pid primary key (pid)
);
-- Method 3: after creation, modify the table structure and add the constraint recommend
create table pk03(
  pid int,
  name varchar2(20)
);
alter table pk03 add constraint pk03_pid primary key (pid);
  • Delete constraint
-- Delete primary key
alter table pk03 drop primary key;
  • Primary key validation

    insert into pk01(pid,name) values(1,'jack');  -- Duplicate execution not allowed
    insert into pk01(name) values('jack');        -- Not allowed null
    commit;
    

5.4. Unique, non empty, default

  • Unique constraint, adding constraint method and primary key constraint always, that is to say, three methods can be adopted
-- Unique constraint (third)
create table un01(
  pid int,
  name varchar2(20)
);
alter table un01 add constraint un01_name unique(name);
  • Unique verification
insert into un01(pid,name) values(1,'jack');  -- Duplicate execution not allowed
insert into un01(name) values('rose');        -- Can be executed
commit;
  • Nonnull constraint
-- Nonnull constraint
create table nn01(
  pid int,
  name varchar2(20) not null 
);
  • Non empty validation
insert into nn01(pid,name) values(1,'jack');  -- Allow repeated entry
insert into nn01(pid) values(2);                --Not allowed null
commit;
  • Default constraint
-- Default constraint
create table de01(
  pid int,
  name varchar2(20) default('tourist')
);

  • Default validation
insert into de01(pid) values(2);       -- Enter only id´╝îname Default visitors
commit;

5.4.1 check constraints

-- Check and verify
create table ch01(
  pid int,
  sex char(10) check( sex in ('male','female') ),
  age int check( age >=0 and age <=100 )
);
  • Check and verify
insert into ch01(pid,sex,age) values(1,'male',18); -- Can be entered
insert into ch01(pid,sex,age) values(2,'Demon',18); -- not allow
commit;

5.5. Joint primary key

  • Union primary key: two or more fields, which are combined as primary keys, are called Union primary keys
  • Application scenario: many to many relationship middle table

-- Union primary key (middle table)
drop table student_course;
create table student_course(
  sid varchar2(20) ,
  cid varchar2(20) ,
  score number(4,1)
);
alter table student_course add constraint student_course_pk primary key (sid,cid);

insert into student_course(sid,cid,score) values('s001','c001',100); --Duplicate not allowed
commit;

6. DML (addition, deletion and modification)

  • DML: data operation language to complete database addition, deletion and modification

6.1 addition

--Complete usage
 insert into table name (field 1, field 2,...) values (value 1, value 2,...)
--Omit usage: value order must correspond to column order in the table
 insert into table name values (value 1, value 2,...)

6.2 modification

--Syntax 1: update all data in the table
 update table name set field 1 = value 1, field 2 = value 2
 --Syntax 2: update specified data (condition)
update table name set field 1 = value 1, field 2 = value 2,... where condition

6.3 deletion

--Syntax 1: delete all
 delete from table name;

--Syntax 2: delete specified data (condition)
delete from table name where condition;

--Grammar 3:
truncate table table name;
  • Difference between delete and truncate:
    • Delete delete delete the data in the table. Rollback is supported.
    • truncate deletes the table structure and recreates it.

7. Foreign key constraint

7.1 overview

  • Explanation of terms:

    • Primary table: Table 1 in a multi table relationship, also known as the primary table.
    • Primary key of the primary table: the primary key of the 1 table modifies the field.
    • Slave table: multiple tables in a multi table relationship, also known as slave tables.
    • Foreign key of slave table: the field used to store the primary key and primary key corresponding to the slave table.

7.2 grammar

  • Foreign key constraint: make the primary key of the primary table and the foreign key of the secondary table form the primary foreign key relationship (also known as one to many relationship)
alter table from table name add constraint foreign key name foreign key (foreign key name) references main table name (primary table primary key)
  • example
-- Foreign key constraint exercise
-- 1. Primary key must be declared when creating primary table (classification table)
create table tb_category(
  id varchar2(32) primary key,
  name varchar2(50)
);
-- 2. Create from table (product table)
create table tb_product(
  id varchar2(32),
  price number(4,1),
  cid varchar2(32)
);
-- 3. Add primary foreign key relationship
alter table tb_product add constraint product_fk foreign key (cid) references tb_category (id);

Posted by tobykw13 on Sun, 24 May 2020 20:14:59 -0700