Fundamentals of the Mysql/oracle Database (ii)

Source: Internet
Author: User
Tags aliases mysql in savepoint

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.