• 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

• 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

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);