Practice for SQL

1.run on environment


[root@oracle-db-19c ~]# 
[root@oracle-db-19c ~]# su - oracle
[oracle@oracle-db-19c ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 18 20:09:18 2022
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> show user
USER is "SYS"
SQL> alter session set container=PDB1;
 
Session altered.
 
SQL> show con_name
 
CON_NAME
------------------------------
PDB1
SQL> set pagesize 200
SQL> set linesize 200
SQL> conn pstest/pstest@PDB1
Connected.
SQL> 
SQL>

2.Creating Table


SQL> 
SQL> create table DEPARTMENTS (  
  2    deptno        number,  
  3    name          varchar2(50) not null,  
  4    location      varchar2(50),  
  5    constraint pk_departments primary key (deptno)  
  6  );
 
Table created.
 
SQL> 
SQL> select * from tab;
 
TNAME                                                                                                                            TABTYPE        CLUSTERID
-------------------------------------------------------------------------------------------------------------------------------- ------------- ----------
DEPARTMENTS                                                                                                                      TABLE
 
SQL> create table EMPLOYEES (  
  2    empno             number,  
  3    name              varchar2(50) not null,  
  4    job               varchar2(50),  
  5    manager           number,  
  6    hiredate          date,  
  7    salary            number(7,2),  
  8    commission        number(7,2),  
  9    deptno           number,  
 10    constraint pk_employees primary key (empno),  
 11    constraint fk_employees_deptno foreign key (deptno) 
 12        references DEPARTMENTS (deptno)  
 13  );
 
Table created.
 
SQL>

Notes: Foreign keys must reference primary keys, so to create a "child" table the "parent" table must have a primary key for the foreign key to reference.

2.Creating Triggers

Triggers are procedures that are stored in the database and are implicitly run,When an INSERT, UPDATE, or DELETE occurred on a table or view. Triggers support system and other data events on DATABASE and SCHEMA.

Triggers are frequently used to automatically populate table primary keys, the trigger examples below show an example trigger to do just this.


SQL> 
SQL> create or replace trigger  DEPARTMENTS_BIU
  2      before insert or update on DEPARTMENTS
  3      for each row
  4  begin
  5      if inserting and :new.deptno is null then
  6          :new.deptno := to_number(sys_guid(), 
  7            'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
  8      end if;
  9  end;
 10  /
 
Trigger created.
 
SQL> create or replace trigger EMPLOYEES_BIU
  2      before insert or update on EMPLOYEES
  3      for each row
  4  begin
  5      if inserting and :new.empno is null then
  6          :new.empno := to_number(sys_guid(), 
  7              'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
  8      end if;
  9  end;
 10  /
 
Trigger created.
 
SQL>

3.Inserting Data


SQL> insert into departments (name, location) values('Finance','New York');
 
1 row created.
 
SQL> insert into departments (name, location) values('Development','San Jose');
 
1 row created.
 
SQL>
 
SQL> column deptno default
SQL> select * from departments;
 
    DEPTNO NAME                                               LOCATION
---------- -------------------------------------------------- --------------------------------------------------
3.1602E+38 Finance                                            New York
3.1602E+38 Development                                        San Jose
 
SQL> 
 
SQL> 
SQL> 
SQL> insert into EMPLOYEES 
  2     (name, job, salary, deptno) 
  3     values
  4     ('Sam Smith','Programmer', 
  5      5000, 
  6    (select deptno 
  7    from departments 
  8    where name = 'Development'));
 
insert into EMPLOYEES 
   (name, job, salary, deptno) 
 
1 row created.
 
SQL> SQL>   2    3     values
  4     ('Mara Martin','Analyst', 
  5     6000, 
  6     (select deptno 
  7     from departments 
  8     where name = 'Finance'));
 
1 row created.
 
SQL> 
SQL> insert into EMPLOYEES 
  2     (name, job, salary, deptno) 
  3     values
  4     ('Yun Yates','Analyst', 
  5     5500, 
  6     (select deptno 
  7     from departments 
  8     where name = 'Development'));
 
1 row created.
 
SQL>

4.Indexing Columns

Typically developers index columns for three major reasons:

To enforce unique values within a column
To improve data access performance
To prevent lock escalation when updating rows of tables that use declarative referential integrity


SQL> column table for a30
SQL> column index for a30
SQL> column column for a30
SQL> column column for a40
SQL> select table_name "Table", 
  2         index_name "Index", 
  3         column_name "Column", 
  4         column_position "Position"
  5  from  user_ind_columns 
  6  where table_name = 'EMPLOYEES' or 
  7        table_name = 'DEPARTMENTS'
  8  order by table_name, column_name, column_position;
 
Table                          Index                          Column                                     Position
------------------------------ ------------------------------ ---------------------------------------- ----------
DEPARTMENTS                    PK_DEPARTMENTS                 DEPTNO                                            1
EMPLOYEES                      PK_EMPLOYEES                   EMPNO                                             1
 
SQL> 
 
SQL> 
SQL> create index employee_dept_no_fk_idx on employees(deptno);
 
Index created.
 
SQL> create unique index employee_ename_idx on employees(name);
 
Index created.
 
SQL>

5. Querying Data


SQL> 
SQL> select * from employees;
 
     EMPNO NAME                                               JOB                                                   MANAGER HIREDATE      SALARY COMMISSION     DEPTNO
---------- -------------------------------------------------- -------------------------------------------------- ---------- --------- ---------- ---------- ----------
3.1602E+38 Sam Smith                                          Programmer                                                                    5000            3.1602E+38
3.1602E+38 Mara Martin                                        Analyst                                                                       6000            3.1602E+38
3.1602E+38 Yun Yates                                          Analyst                                                                       5500            3.1602E+38
 
SQL> 
SQL> 
SQL> select e.name employee,
  2            (select name 
  3             from departments d 
  4             where d.deptno = e.deptno) department,
  5             e.job
  6  from employees e
  7  order by e.name;
 
EMPLOYEE                                           DEPARTMENT                                         JOB
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
Mara Martin                                        Finance                                            Analyst
Sam Smith                                          Development                                        Programmer
Yun Yates                                          Development                                        Analyst
 
SQL>

6. Adding Columns


SQL> alter table EMPLOYEES 
  2  add country_code varchar2(2);
 
Table altered.
 
SQL>

7. Querying the Oracle Data Dictionary


SQL> select table_name, tablespace_name, status
  2  from user_tables
  3  where table_Name = 'EMPLOYEES';
 
TABLE_NAME                                                                                                                       TABLESPACE_NAME                STATUS
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------ --------
EMPLOYEES                                                                                                                        USERS                          VALID
 
SQL>
 
SQL> column column_id default
SQL> column column_name for a30
SQL> column data_type for a30
SQL> select column_id, column_name , data_type
  2  from user_tab_columns
  3  where table_Name = 'EMPLOYEES'
  4  order by column_id;
 
 COLUMN_ID COLUMN_NAME                    DATA_TYPE
---------- ------------------------------ ------------------------------
         1 EMPNO                          NUMBER
         2 NAME                           VARCHAR2
         3 JOB                            VARCHAR2
         4 MANAGER                        NUMBER
         5 HIREDATE                       DATE
         6 SALARY                         NUMBER
         7 COMMISSION                     NUMBER
         8 DEPTNO                         NUMBER
         9 COUNTRY_CODE                   VARCHAR2
 
9 rows selected.
 
SQL>

8. Updating Data


 
SQL> update employees
  2  set country_code = 'US';
 
3 rows updated.
 
SQL> update employees
  2  set commission = 2000
  3  where  name = 'Sam Smith';
 
1 row updated.
 
SQL> select name, country_code, salary, commission
  2  from employees
  3  order by name;
 
NAME                                               CO     SALARY COMMISSION
-------------------------------------------------- -- ---------- ----------
Mara Martin                                        US       6000
Sam Smith                                          US       5000       2000
Yun Yates                                          US       5500
 
SQL>

9. Aggregate Queries


SQL> 
SQL> select 
  2        count(*) employee_count,
  3        sum(salary) total_salary,
  4        sum(commission) total_commission,
  5        min(salary + nvl(commission,0)) min_compensation,
  6        max(salary + nvl(commission,0)) max_compensation
  7  from employees;
 
EMPLOYEE_COUNT TOTAL_SALARY TOTAL_COMMISSION MIN_COMPENSATION MAX_COMPENSATION
-------------- ------------ ---------------- ---------------- ----------------
             3        16500             2000             5500             7000
 
SQL>

10. Compressing Data

As your database grows in size to gigabytes or terabytes and beyond, consider using table compression. Table compression saves disk space and reduces memory use in the buffer cache. Table compression can also speed up query execution during reads. There is, however, a cost in CPU overhead for data loading and DML. Table compression is completely transparent to applications. It is especially useful in online analytical processing (OLAP) systems, where there are lengthy read-only operations, but can also be used in online transaction processing (OLTP) systems.

You specify table compression with the COMPRESS clause of the CREATE TABLE statement. You can enable compression for an existing table by using this clause in an ALTER TABLE statement. In this case, the only data that is compressed is the data inserted or updated after compression is enabled. Similarly, you can disable table compression for an existing compressed table with the ALTER TABLE...NOCOMPRESS statement. In this case, all data the was already compressed remains compressed, and new data is inserted uncompressed.

To enable compression for future data use the following syntax.


SQL> alter table EMPLOYEES compress for oltp; 
 
Table altered.
 
SQL> alter table DEPARTMENTS compress for oltp; 
 
Table altered.

11. Deleting Data


 
SQL> delete from employees 
  2  where name = 'Sam Smith';
 
1 row deleted.
 
SQL>

12. Dropping Tables


 
SQL> drop table departments cascade constraints;
 
Table dropped.
 
SQL> drop table employees cascade constraints;
 
Table dropped.
 
SQL>

13. Un-dropping Tables

If the RECYCLEBIN initialization parameter is set to ON (the default in 10g), then dropping this table will place it in the recycle bin. To see if you can undrop a table run the following data dictionary query:


SQL> select object_name, 
  2         original_name, 
  3         type, 
  4         can_undrop, 
  5         can_purge
  6  from recyclebin;
 
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
ORIGINAL_NAME                                                                                                                    TYPE                      CAN CAN
-------------------------------------------------------------------------------------------------------------------------------- ------------------------- --- ---
BIN$7b6u2Xx2SP/gU4oIqMCr/g==$0
PK_DEPARTMENTS                                                                                                                   INDEX                     NO  YES
 
BIN$7b6u2Xx4SP/gU4oIqMCr/g==$0
DEPARTMENTS                                                                                                                      TABLE                     YES YES
 
BIN$7b6u2Xx7SP/gU4oIqMCr/g==$0
EMPLOYEE_ENAME_IDX                                                                                                               INDEX                     NO  YES
 
BIN$7b6u2Xx8SP/gU4oIqMCr/g==$0
EMPLOYEE_DEPT_NO_FK_IDX                                                                                                          INDEX                     NO  YES
 
BIN$7b6u2Xx9SP/gU4oIqMCr/g==$0
PK_EMPLOYEES                                                                                                                     INDEX                     NO  YES
 
BIN$7b6u2Xx/SP/gU4oIqMCr/g==$0
EMPLOYEES                                                                                                                        TABLE                     YES YES
 
BIN$7b6u2Xx3SP/gU4oIqMCr/g==$0
DEPARTMENTS_BIU                                                                                                                  TRIGGER                   NO  NO
 
BIN$7b6u2Xx+SP/gU4oIqMCr/g==$0
EMPLOYEES_BIU                                                                                                                    TRIGGER                   NO  NO
 
 
8 rows selected.
 
SQL>
 
SQL> 
SQL> flashback table DEPARTMENTS to before drop;
flashback table EMPLOYEES to before drop;
select count(*) departments 
 
Flashback complete.
 
SQL> from departments;
 
Flashback complete.
 
SQL>   2  
DEPARTMENTS
-----------
          2
 
SQL> select count(*) employees
  2  from employees;
 
 EMPLOYEES
----------
         2
 
SQL>

相关阅读:
MYSQL多表联查on和where的区别
 Zabbix 使用同一ODBC监控不同版本MySQL
用于独立系统应用的光伏MPPT铅酸电池充电控制器建模（Simulink实现）
python爬虫进阶篇（异步）
wpf devexpress在未束缚模式中生成Tree
【李宏毅】深度学习-2021HW3-CNN（图像分类）
屋顶太阳能光伏系统的性能分析指标研究
 单调栈！！！
Hive insert插入数据与with子查询
 Failed to start The nginx HTTP and reverse proxy server.
原文地址：https://blog.csdn.net/u011868279/article/details/127928927