Fundamentals of the Mysql/oracle Database (ii)

Mysql/oracle Database

• Oracle database management system is a computer software that manages database access, and is comprised of Oracle databases and Oracle instances
  
  • Oracle database: A collection of related operating system files that are organized together to become a logical whole, that is, an Oracle database. Oracle databases must work with memory instances in order to provide data management services externally.
  • Oracle instance: A data structure located in physical memory that consists of multiple background processes and a shared pool of memory in the operating system, which can be accessed by a process lock.
    
    • Oracle uses them to manage database access
    • Oracle instances are the usual database services (service)
  • An instance can manipulate the database; At any time an instance can be associated with only one database, access a database, and the same database can be accessed by multiple instances (RAC)
• Oracle differs from MySQL in:
  
  • In an Oracle database, use number to denote a numeric type, date to represent a type, and VARCHAR2 to represent a character type
• The null value differs from 0, and the result is null (NULL), the null value is invalid, the unspecified
• Creation of aliases: (Way three)

• Use double quotes "", such as: display aliases intact (especially if the alias is made up of multiple words)

  select employee_id "id",last_name "Name",12*salary "annual_sal"from employees;

• Connector:

  • Concatenate columns and columns with characters
  • Use "| |" To connect
  • Can be used to "compose" columns

  • Such as:

    select first_name || ‘ ‘ || last_name from employees;

• String:
  
  • The string can be a character, number, date in the select list
  • Dates and characters can only appear in single quotation marks
  • Each time a row is returned, the string is output once

• Use distinct to filter out duplicate data, such as:

  select distinct dept_id from empt;

• SQL statements with the Sql*plus command:
  
  • SQL: A language, ANSI standard, keywords cannot be abbreviated, use statements to control the definition of tables in a database, and data in tables.
  • Sql*plus: An environment, one of the characteristics of Oracle, Keywords can be abbreviated (such as: Edit (ed), describe (DESC)), the command can not change the value of data in the database, centralized operation

• Database operation functions:

  • Single-line function

    • Single-line function classification: character, value, date, conversion, universal

      单行函数：①操作数据对象；        ②接收参数返回一个结果        ③只对一行进行变换        ④每行返回一个结果        ⑤可以嵌套与转换数据类型        ⑥参数可以是一列或一个值

    • Character functions:

      • Character size control functions, such as:

        LOWER(‘HELLOWORLD‘)             //helloworldUPPER(‘hello‘)                  //HELLOinitcap(‘helloWorld you‘)       //Helloworld You,首个字母大写

      • Character control functions, such as:

        CONCAT(‘Hello‘, ‘World‘)        //HelloWorldSUBSTR(‘HelloWorld‘,1,5)        //HelloLENGTH((‘HelloWorld‘)           //10INSTR(‘HelloWorld‘,‘W‘)     //6LPAD(24000,10,‘*‘)              //*****24000RPAD(24000,10,‘*‘)              //24000*****TRIM(‘H‘ FROM ‘HelloHWorldH‘)   //elloHWorldREPLACE(‘abcd‘,‘b‘,‘m‘)     //amcd

    • Numeric functions:

      • Round: Five in

        round(45.926,2)     //45.93round(45.926,-1)    //50

      • TRUNC: Truncate

        trunc(45.926,2)     //45.92

      • Redundancy:

        MOD(1600,300)       //100

    • Date function:
      
      • Date-based data in Oracle actually contains two values: Date and time
        
        • Date: function sysdate return
        • Two date subtraction returns the number of days between dates (but does not allow addition operations)
      • Months_between: Two months of difference between dates
      • Add_months: Add a number of months to the specified date
      • Next_day: The next week of the specified date * corresponds to the date
      • Last_day: Last day of the month
      • ROUND: Date Rounding
      • TRUNC: Date Truncation

    • Conversion functions:

      • Implicit (Oracle automatically completes the following conversions)

        VARCHAR2 or CHAR ---------------------> NUMBERVARCHAR2 or CHAR ---------------------> DATENUMBER  ------------------------------> VARCHAR2DATE    ------------------------------> VARCHAR2

      • Dominant

        • Data→to_char () →character→to_date () →date

            Select To_char (sysdate, ' Yyyy-mm-dd ') from Dual;select to_date (' 1995-05-23 ', ' yyyy-mm-dd ') The from dual;//uses double quotes when using characters, such as: Select To_char (sysdate, '
            yyyy "Year" MM "month" DD "Day" from dual;  

        • Number→to_char () →character→to_number () →number

           //1,234,567.890, ' $999,999,999,999.999 ' represents USD, ' l999,999,999,999.999 ', indicating local currency symbol (l = local) Select To_char (1234567.89, ' 999,999,999,999.999 ') from Dual;select to_
             Number (' y001,234,567.89 ', ' l000,000,999.99 ') from dual;  

          In the conversion of numeric values, the To_char () function often uses a centralized format:

           -9: number-0:0-$: Dollar sign-L: Local currency symbol-.: Denotes decimal point-,: denotes thousand character  

    • Common functions:

      • This class of functions applies to any data type and also to null values

      • NVL (EXPR1,EXPR2):

        • converts null values to a known value
        The
        • data types you can use are date, character, value
        The form of the

        • function:

            NVL (commission_pct, 0)//is expressed as a value of 0 when commission_pct is null, otherwise equals COMMISSION_PCTNVL (hire_ Date, ' 01-jan-97 ')  

      • NVL2 (EXPR1,EXPR2,EXPR3): Expr1 NOT NULL, return EXPR2, NULL, return EXPR3
      • Nullif (EXPR1,EXPR2): equals returns NULL, not equal to return EXPR1
      • coalesce (expr1,expr2,..., exprn): If the first expression is empty, the next expression is returned, The other parameters are coalesce. The advantage of
        
        • coalesce compared to NVL is that coalesce can handle alternating multiple values at the same time

    • Conditional expression:

      • Using if-then-else logic in SQL statements

      • Two methods are used:

        • Case expression:

          • Format:

            case expr when comparison_expr1 then return_expr1    [when comparison_expr2 then return_expr2    ...]    

          • Such as:

            select department_id,case department_id when 10 then salary*1.1                   when 20 then salary*1.2                   else salary*1.3endfrom employeeswhere department_id in(10,20,30);

      • Decode function:

        • Such as:

          select department_id,decode(department_id, 10, 1.1*salary,                      20,1.2*salary,                      30,1.3*salary)from employees;

• Nested group functions:

  • such as: show the maximum of the average salary of each department

    select max(avg(salary))from employeesgroup by department_id;

• Oracle Database sub-query:

  • Sub-query syntax:

    select select_listfrom tablewhere expr operator(    select select_list2    from table2;        //table与table2可以是同一个表);

  • The subquery executes one time before the main query, and the result of the subquery is used by the main query

  • Attention:

    1.子查询要包括在括号内2.将子查询放在比较条件的右侧3.子查询中可以使用组函数4.子查询中要留意空值

  • Single row comparison operators used by single-line subqueries:

    =、>、>=、<、<=、<>(不等于)

    • Such as:

      select salary,last_namefrom employeeswhere salary > (select salary                from employees                where last_name=‘Abel‘);

  • Multiline operators used by multiple rows of subqueries:

    如:in、any(和子查询返回的某一个值比较)、all(和子查询返回的所有值比较)

  • Composite subquery Nesting

                //查询平均工资最低的部门信息select *from departmentswhere department_id = (select department_id                       from employees                       group by department_id                       having avg(salary) = (select min(avg(salary))                                             from employees                                             group by department_id                                            )                       )

• Create and manage Tables (DDL) (Create, Alter, DROP, rename, truncate) → Operations are not rolled back

  • Tables in Oracle database

    • User-defined tables

      • a set of tables that users create and maintain themselves
      • contains the information required by the user

      • Common Query dictionary command:

          select * from User_tables; View what user-created tables have select table_name from User_tables;  View table names for user-created tables select distinct object_type from user_objects;     View user-defined various database objects select * from User_catalog; View user-defined tables, views, synonyms, and sequences  

    • data Dictionaries
      
      • A set of tables created automatically by Oracle Server
      • Package With database information

  • How to create a table:

    • Method One: Create empty table directly

      create table myemploy(    id number(5),    name varchar2(20),          //注意：定义字符串使用的是varchar2    salary number(10,2),        //表示10位数据,其中两位是小数    hire_date date);

    • Method Two: Create a table using a subquery

      • Columns in the specified column and subquery correspond to each other

      • Defining columns by column name and default value

        //方式二:从其他的表中抽取部分列生成新表create table emptas select employee_id,last_name,salary,hire_datefrom employees;//注意,上表生成的信息会把原来的表中的数据也复制过来,若想不生成数据,则可加上不等的过滤条件,如:    create table employee    as     select employee_id,last_name,salary,hire_date    from employees    where 1=3;              //因为2不可能等于3,故创建的表为有相应结构而无数据的空表

• Data processing (DML) (insert INTO, delete from, upddate, select) → actions can be rolled back

  • A special way to insert data: Copy data from other tables, such as:

    insert into emp(employee_id,last_name,salary,hire_date)select employee_id,last_ame,salary,hire_datefrom employeeswhere employee_id < 20;     //加上限制条件表示仅复制部分数据

  • Create script: Use & Variables in SQL statements to specify column values,& variables are placed in the values clause, such as:

    //该插入方法是在执行语句后来添加数据的insert into emp(employee_id,last_name,salary,hire_date)values(&employee_id,‘&last_name‘,&salary,&hire_date);

    -update:update the entire column is updated without limiting the condition

  • In update, you can use a subquery when you update data that is based on another table, such as:

        upadate emp_copy    set departmen_id = (select departmen_id                        from employees                        where employee_id = 20                        )    where job_id = (select job_id                    from employees                    where employee_id = 10                    );

  • Data additions and deletions related operations
    
    • Commit (Commit): Similar to File save
    • Rollback (rollback): When the operation data is not at that time, it can be rolled back to the pre-modified data

  • Database transactions:

    • Start with execution of the first DML statement

    • End with one of the following:

      • Commit or ROLLBACK statement

        commit与rollback语句的优点:    1.确保数据完整性    2.数据改变被提交之前预览    3.将逻辑上相关的操作分组在进行增删改操作时可以设置保存点,如:        savepoint A;在未commit之前,进行多步(增删改)操作后,可以直接通过保存点回滚到保存点处,如:    rollback to savepoint A;注意：回滚只能在commit之前进行,在commit之后是无效的

      • DDL statements (Auto-commit)
      • User session ends normally
      • System Abnormal termination
    • The state of the data before committing or rolling back
      
      • The state of the data before the change can be restored.
      • Users performing DML operations can query for previous corrections through the Delect statement
      • Other users cannot see changes made by the current user until the current user finishes the thing
      • The rows involved in the DML statement are locked and cannot be manipulated by other users
  • It is best to perform a commit when the database operation (add and Scan) is completed.

• Oracle Database Creation constraints:

  • Non-null constraints (NOT NULL), such as:

    create table emp(    id number(10) constraint emp_id_nn not null,    //emp_id_nn表示约束名    name varchar(20),    salary number(10,2))

  • Unique constraints (unique), such as:

    create table emp(    id number(10) constraint emp_id_uq unique,  //列级约束,emp_id_uq约束名    name varchar(20) constraint,    salary number(20),    phone number(11),    //表级约束    constraint emp_phone_uq unique(phone)       //括号内指明约束列)

  • PRIMARY KEY constraint (primary key), such as:

    create table emp(    id number(5) constraint emp_id_pk primary key,    name varchar(20),    salary number(9,2)    //或者使用表级约束    //constraint emp_id_pk primary key(id))

  • FOREIGN KEY constraint (foreign key): Connect two tables and associate two tables

    • FOREIGN key: Specify columns in a child table at the table level
    • References: Columns that are marked in the parent table

    • Such as:

      create table empt(    id number(5),    name varchar(20),    salary number(7,2),    dept_id number(5),    constraint emp_dept_fk foreign key(dept_id) references depts(dept_id) )create table depts(    dept_id number(5),    dept_name varchar(10),    job_id number(3))

  • When a column in the parent table is deleted when the data is deleted, the corresponding column in the child table (Foreign Key Association) is also deleted

  • Syntax for adding constraints:

    • Using the ALTER TABLE statement:

        1. Add or remove constraints, but you cannot modify constraints 2. Constraint 3. Add not Null constraints to use the Modify statement  

    • Format:

        ALTER TABLE table_nameadd (drop ...)
        [Constraint constraint_name] Type (column);  

    • Such as:

       //Add NOT null ALTER TABLE empmodify salary number (8,2) not null;//add additional constraint ALTER TABLE EMPADD constraint Emp_uq unique (name);//delete constraint ALTER TABLE EMPDROP constraint emp_uq;//invalid constraint ALTER TABLE empdisable constraint emp_uq;//effective (
       Note that the prerequisite for enable is that there is no corresponding constraint problem with the column where the constraint resides) ALTER TABLE empenable constraint emp_uq;  

  • Query constraint:

    • user_constraints

    • Such as:

       //query constraint name, constraint type, constraint select Constraint_name,   Constraint_type,search_condition from User_constraintswhere table_name= ' employees '; The expression table name  

    • Query defines the columns of the constraint:

      • user_cons_columns

      • If:

          SE
           Lect constraint_name,column_namefrom user_cons_columnswhere table_name= ' employees ';  

• View:

  • view is a virtual table that is based on an existing table
  • The advantages of a statement that provides data content to a view as a SELECT statement
  • view:
    
    • Control Data access
    • simplify queries
    • avoid repeated access to the same data

  • View rules for using DML

    • You can perform DML operations in a simple view As
    • cannot use delete when the view definition contains the following elements:
      
      • Group function
      • GROUP BY clause
      • DISTINCT Association Word
      • rownum pseudo-column (representing a column that does not exist in the original table)

    • Such as:

        Create or replace view Emp_viewas
         Selectavg (Salary) Avg_sal from Employeesgroup by department_id;  

• Sequence: database objects that can be used by multiple users to produce unique values

  • automatically provide unique numeric values
  • shared objects
  • primarily used to provide primary key values
  • loading sequence values into memory can improve access efficiency. /li>

  • Create format:

      create sequence seq_name[increment by n]//value per increment [start wiht n]//starting from [{MAXVA Lue N |
      Nomaxvalue}][{minvalue N | nominvalue}][{cycle | nocycle}]//Whether a loop is required [{Cache n | nocache}]//cache login  

  • Such as:

      Create sequence emp_seqincrement by 5start with 2maxvalue 50cyclenochche;  

  • Sequence Execution method:

      1.select emp_sql.nextval from dual;
      2.select emp_sql.curval from dual;  

  • Note: When you first call a method, you need to use Nextval.
  Modification of the

  • Sequence (cannot modify the initial value)

      alter sequence seq_nameincrement by newvalnomaxvalue ...;  

  • Modify sequence Note:
    
    • 1. Must be the owner of the sequence or have ALTER permission on the sequence
    • 2. Only future sequence values will be changed
    • 3. Change The ' initial value ' of the variable sequence can only be implemented by removing the sequence after the
    • 4.rollback is unable to roll back the sequence

Base of the Mysql/oracle database (ii)