Database foundation ②

Posted by sheraz on Fri, 17 Sep 2021 01:28:24 +0200

❣💞❣ Database foundation ②

1, 92 syntax join table query

1. Cross connect: corss join

-- Cross connect
select * from emp cross join dept;

2. natural join

Note: if there are multiple fields with the same name, they will be connected equally

-- Inner connection
select * from emp natural join dept;

3. Using connection: Join using (field with the same name)

-- using
select * from emp e join dept d using(deptno);

4. Equivalent non equivalent join on

be careful; join....on fields with the same name need to specify qualifiers

-- join on
select * from emp e join emp d on e.mgr=d.empno;

5. left join on

-- left  join Left connection
select * from emp e left join emp d on e.mgr=d.empno;

6. right join on

-- right join Right connection
select * from emp e right join emp d on e.mgr=d.empno;

2, Rowid and Rownum database objects

1. rowid as pseudo column

-- rowid
select ename ,sal,empno,rowid from emp ;

-- duplicate removal
select * from tb_student;
minus
select distinct id,name,course,score from tb_student;
--rowid

select id,name,course,score,max(rowid)from tb_student group by id,name,course,score;
select id, name, course, score
  from (select id, name, course, score, max(rowid)
          from tb_student
         group by id, name, course, score);
-- rowid De duplication pass rowid Get         
select *
  from tb_student
 where rowid in (select max(rowid)
                   from tb_student
                  group by id, name, course, score);
                  
                  
select *
  from tb_student
 where not rowid in (select max(rowid)
                   from tb_student
                  group by id, name, course, score);
                  
                  
                  
delete from tb_student
 where not rowid in (select max(rowid)
                   from tb_student
                  group by id, name, course, score);
                  
select * from tb_student;

2. rownum the sequence number of the row in the result set, starting from the beginning

paging

-- rownum 
select deptno,dname,loc,rownum ro from dept;

-- Pagination display
select empno,ename ,sal,rownum from emp;
select *
  from (select empno, ename, sal, rownum ro from emp)
 where ro >= 4
   and ro <= 6;

3, Create table, insert, modify, delete

1. Creating tables and adding constraints

create table shop(shopno number(10),shopname varchar(10));
select * from shop
insert into shop values (1000,'Potato chips');

Primary key constraint, foreign key constraint, unique constraint, non empty constraint, check constraint, default constraint

-- Create table primary key, foreign key, non empty constraint, unique constraint, check constraint, default constraint
-- user surface
create table tb_users(
       userno number(4) primary key,
       username varchar2(3 char) unique );
-- shop surface
create table shop(
       shopno number(10) primary key,
       shopname varchar2(10) not null,
       shopprice number(10),
       shoptime date default(sysdate),
       userno number(4)references tb_users(userno));
       
-- insert data      
insert into tb_users values (1011,'Shao Zhengchun');
insert into tb_users values (1012,'Shen Zijie');
--Display data
select * from tb_users
select * from shop
--insert data
insert into shop values (1000111111,'Potato chips',1000,sysdate,1011);
insert into shop values (1000111121,'Spicy strip',1000,sysdate,1012);
insert into shop values (1000111112,'Potato chips',1000,sysdate,1011);

2. How to add constraints

Normal add when creating a table

-- shop surface
create table shop(
       shopno number(10) primary key,
       shopname varchar2(10) not null,
       shopprice number(10),
       shoptime date default(sysdate),
       userno number(4)references tb_users(userno));

Add add

add constraints Constraint name primary key(Field name)

alter append

alter table student add constraints Constraint name primary key(Field name)

3. Copy of table

Copy table structure

create table xx_emp as select * from emp where 1!=1;
select * from xx_emp;

Copy table structure + content

create table yy_emp as select * from emp where deptno=20;
select * from yy_emp;

4. Table deletion and comments

notes:

comment on table tb_users is 'User table'

Delete table
- By default, the secondary table is deleted, and then the primary table is deleted
- Cascade delete constraints between master and slave tables while deleting the master table
```
drop table shop;
drop table tb_users;
```

5. Modify table

rename x to y
alter table x rename column z to y

4, Business

What is a transaction: it refers to a group of related operations performed as a single logical unit of work. These operations require all or no completion at all

Role: transactions are used to ensure data security and effectiveness.

1. Characteristics of things ACID:

Atomicity: both success and failure.
Consistency: when the transaction is completed, it must be reflected in the related table.
Isolation: the transaction should be before and after the data is modified by another transaction.
Persistence: ensure that the modification of the database by the transaction is lasting and effective, and will not be lost in case of failure.

2. Transaction isolation level:

	Dirty reading	Non repeatable	Unreal reading
Read uncommitted (insert)	√	√	√
Read committed(update)	×	√	√
Repeatable read(update)	×	×	√
Serializable (insert)	×	×	×

3. Start and end of transaction

When adding, deleting or modifying automatically, the transaction is enabled by default
Transactions can be started manually
end:
- Successful submission: commit|DCL|DDL | close the client normally | automatic submission
- Failed rollback: roll back | unexpected exit

5, DML (Insert, upDate, delete)

Insert insert data

-- Insert information
create table yy_emp as select * from emp where deptno=20;
select * from yy_emp;

insert into yy_emp values(7111,'ABCD','LEADER',8888,sysdate,30000,1000,40);
 
insert into yy_emp select * from emp where deptno=30;

UpDate update data

-- Update data
update yy_emp set comm=0.00 where deptno=20;

Delete delete record
- When you want to delete data from a normal table - > OK
- When the record in the table to be deleted ----- > OK
- When the master table to be deleted contains data referenced from the slave table: ① delete the master data in the master table that is not referenced by the data in the slave table - > OK
- ② To delete the number of slave tables referenced by the master table - > no
  - You can delete the data from the secondary table and then delete the data from the primary table (default i)
  - Delete the data in the main table, followed by on delete cascade
  - Or add on delete set null

--Delete data
delete shop 
delete shop where userno=1011;
delete tb_users where userno=1011;

6, Data Truncate: Truncate

Truncate all data in the table
The data in the truncated primary table is directly checked on the table structure and referenced by the secondary table. Truncation is not allowed
Effect of deleting all data delete can delete all data
The difference between trancate and delete
- The same point: truncate, delete without where clause and drop will delete the data in the table
- difference:
  - 1.truncate and delete only delete data without deleting the structure (definition) of the table. The drop statement will delete the constraints, triggers and indexes on which the structure of the table depends. The stored procedures / functions that depend on the table will be retained, but will become invalid.
  - 2. The delete statement is dml. This operation will be put into the rollback segment and will take effect only after the transaction is committed. If there is a corresponding trigger, it will be triggered during execution. Truncate and drop are ddl, and the operation will take effect immediately. The original data will not be put into the rollback segment and cannot be rolled back. The operation will not trigger the trigger.
  - 3. The delete statement does not affect the extents occupied by the table. The high w2atermark remains in its original position. Obviously, the drop statement releases all the space occupied by the table. The truncate statement releases the space to minextents by default, unless reuse storage is used; truncate will reset the high watermark (back to the beginning)
  - 4. Speed: drop > truncate > delete
  - 5. Security: use drop and truncate carefully, especially when there is no backup

Unfinished to be continued

Topics: Database SQL

Programmer Think

Database foundation ②