Oracle Learning Notes (5)--Query

• Basic query statements
  • SELECT [DISTINCT] column_name1,... | * FROM table_name [WHERE conditions]

• formatting in sql*plus
  • change display field name: COLUMN column_name HEADING new_name    (note column can be abbreviated to COL)
    • For example:

col username heading user name;
select * from users;

• Format the display result: COLUMN column_name format DataFormat; (note that column can be abbreviated to COL, the character type can only be set to the displayed length)
  • For example

Col username format A10;Note: The character type is represented by a, and A10 indicates that the set character type length is 10. Numeric types are represented by 9select * from Users;Col salary format 9999.9;Note: The numbervalue types are represented by 9select * from Users;

• Clears the format previously set: COLUMN column_name clear;
  • For example:

Col username clear; Col salary clear;

• Querying all fields in a table and specifying fields
  • Query all fields: SELECT * FROM table_name;
  • Query the specified field: SELECT column1_name,column2_name ... from table_name;

• set alias for field ( * query result did not change the name of the field)
  • select column_name as New_name,... from table_name; Note: As can be omitted, the original field name and the new field name are separated by a space.
    • For example:

Select ID as number, username as user name, salary salary from users;

• Operators and expressions
  • expression = operand + operator
  • oracle can have variables, constants and fields
  • operator
    • arithmetic operator (+,-,*,/)
    • comparison operator (>,>=,<,<=,=,<>)
    • precedence: decrements by not, and, or, in descending order. Comparison operators have higher precedence than logical operators
• Using operators in SELECT statements ( omitted )
• Query with conditions
  • Single-Condition Query
    • Query the employee's salary for AAA: Select salary from users where username= ' AAA ';
  • Multi-Criteria Queries
    • The query name is AAA, or employee information between 800 and 2000 of the salary

SELECT * from users where username= ' AAA ' or (Salary > Salary <=2000)

• fuzzy query like
  • Use of wildcard characters (_,%)
    • One _ can only represent one character
    • % can represent 0 to any number of characters
  • Using the LIKE query
    • Example: Query user information with user name beginning with a. SELECT * from the users where username like ' a% ';
    • Example: The second character of a query user name is a user information. SELECT * from the users where username like ' _a% ';
      
    • Example: Query user information that contains a in the user name. SELECT * from the users where username like '%a% ';
      

• Scope Query
  • between ... and closed intervals.
    • Example: Query all employee information between 800 and 2000 for employee salaries: SELECT * from users where salary between and 2000; Note contains 800 and
    • Example: Query employee salary is not all employee information between 800 and 2000: SELECT * from the users where salary not between and;
  • In/not in
    • Query user name is AAA or BBB user information: SELECT * from the users where username in (' AAA ', ' BBB ');
    • The query user name is not AAA, nor is the BBB user information: SELECT * from the users where username not in (' AAA ', ' BBB ');

• Sort the results of the query
  • select ... From ... [WHERE ...]  order by  column1 DESC/ASC,...;    DESC: Descending, ASC: Ascending

• Use of Case...when statements
  • Function: It is in our query statements can be based on the different values of our fields, but also can display different results.
  • Case column_name while value1 then RESULT1,... [ELSE result] END;
    • Example: To get his department based on a different user name

Select Username,case username when ' aaa ' then ' computer Department 'When the ' BBB ' then ' the marketing department ' Else ' other department ' end as department from users;

• If Column_name=value1 then Result1,... [ELSE result] END;
  • Example:

Select Username,case when Username= ' AAA ' then ' computer Department ' when Username= ' BBB ' and then ' marketing department ' Else ' other Department ' end as department from users;

• Example:

Select Username,case when salary<800 then ' low wages ' when salary>5000 then ' pay high ' end as wage level from users;

• Use of the Decode function
  • Decode (COLUMN_NAME,VALUE1,RESULT1,..., defaultvalue);
    • Example:

Select Username,decode (username, ' aaa ', ' computer Department ', ' BBB ', ' marketing department ', ' other ') as department from users;

Oracle Learning Notes (5)--Query