Using Pseudocolumns
-- the following statement displays the SYSDATE, which is the current system date
-- NOW is a column alias for display purposes
-- DUAL is a dummy table with one row simply used to complete the SELECT statement
SELECT SYSDATE "NOW" FROM DUAL;
-- display the name of the current user, the user name should be HR
SELECT USER FROM DUAL;
-- using ROWNUM < 10 limits the number of rows returned to less than 10
SELECT employee_id, hire_date, SYSDATE FROM employees WHERE ROWNUM < 10;
Using Sequences
-- first initialize the employees_seq sequence with NEXTVAL
SELECT employees_seq.NEXTVAL FROM DUAL;
-- after initializing the sequence, use CURRVAL as the next value in the sequence
INSERT INTO employees VALUES
(employees_seq.CURRVAL, 'Belinda', 'Vernal', 'belinda.vernal', '555.111.2342',
'15-AUG-05', 'ST_CLERK', 6000, NULL, 124, 50);
-- query the employees table to check the current value of the sequence
-- which was inserted used as employee_id in the previous INSERT statement
SELECT employee_id, last_name FROM employees WHERE last_name = 'Vernal';
Using Character Functions
-- you can use the UPPER function to display uppercase data, LOWER for lowercase
SELECT employee_id, UPPER(last_name), LOWER(first_name) FROM employees;
-- you can use CONCAT function to concatenate character data
SELECT CONCAT('Last name: ', last_name) FROM employees;
-- you can use RTRIM and LTRIM to remove spaces from the beginning or end of
-- character data. Note the use of concatenation operator ||
SELECT employee_id, RTRIM(first_name) || ' ' || LTRIM(last_name) FROM employees;
-- you can TRIM to remove spaces from both the beginning and end
SELECT employee_id, TRIM(last_name) || ', ' || TRIM(first_name) FROM employees;
-- you can format the system date (SYSDATE) as a character string
-- with various format masks and then display
-- the following displays September 21 2005
SELECT TO_CHAR(SYSDATE, 'fmMonth DD YYYY') "Today" FROM DUAL;
-- the following displays 21-SEP-2005 AD
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY AD') "Today" FROM DUAL;
Using Arithmetic Operators
-- in the following query the commission is displayed as a percentate instead
-- of the decimal that is stored in the database
SELECT employee_id, (commission_pct * 100) "Commission %" FROM employees;
-- in the following query, the proposed new annual salary is calculated
-- for employees who report to the manager with Id 145
SELECT employee_id, ((salary + 100) * 12) "Proposed new annual salary"
FROM employees WHERE manager_id = 145;
Using Numeric Functions
-- you can use the ROUND function to round off numeric data, in this case to
-- two decimal places
SELECT employee_id, ROUND(salary/30, 2) "Salary per day" FROM employees;
Performing Date Arithmetic
Oracle Database provides a number of features to help with date arithmetic, so that
you do not need to perform your own calculations on the number of seconds in a day,
the number of days in each month, and so on. Some useful features include the
following:
ADD_MONTHS function, which returns the date plus the specified number of months.
■ MONTHS_BETWEEN function, which returns the number of months between two dates.
■ SYSDATE function, which returns the current date and time set for the operating system on which the database resides.
■ SYSTIMESTAMP function, which returns the system date, including fractional seconds and time zone, of the system on which the database resides.
■ TRUNC function, which when applied to a DATE value, trims off the time portion so that it represents the very beginning of the day (the stroke of midnight). By truncating two DATE values and comparing them, you can determine whether they refer to the same day. You can also use TRUNC along with a GROUP BY clause
to produce daily totals.
■ Arithmetic operators such as + and -. For example, SYSDATE-7 refers to 7 days before the current system date.
■ INTERVAL datatypes, which enable you to represent constants when performing date arithmetic rather than performing your own calculations. For example, you can add or subtract INTERVAL constants from DATE values or subtract two DATE values and compare the result to an INTERVAL.
■ Comparison operators such as >, <, =, and BETWEEN.
Using Date Functions
-- in the following statement you can use MONTHS_BETWEEN to compute months
-- employed for employees and then truncate the results to the whole month
-- note the use of the label (alias) "Months Employed" for the computed column
SELECT employee_id, TRUNC(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) "Months Employed"
-- the following displays the year hired for each employee id
SELECT employee_id, EXTRACT(YEAR FROM hire_date) "Year Hired" FROM employees;
Using Date Functions With Format Masks
-- use TO_DATE with a format mask to display or enter dates differently than the
-- current default date format
-- the following displays 1998 with the 'DD-MON-RR' format mask
SELECT TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-RR') ,'YYYY') "Year" FROM DUAL;
-- note that 'YY' in a format mask denotes the year in the current century
-- the following displays 2098 with the 'DD-MON-YY' format mask
SELECT TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-YY') ,'YYYY') "Year" FROM DUAL;
-- the following displays the date and time with a datetime format mask
SELECT TO_TIMESTAMP ('10-Sep-05 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF')
FROM DUAL;
-- the following displays the system date and time with a format mask
SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "Now" FROM DUAL;
Using Aggregate Functions
-- you can use COUNT to count the employees with manager 122
-- note the use of a column alias Employee Count
SELECT COUNT(*) "Employee Count" FROM employees WHERE manager_id = 122;
-- count the employees grouped by manager, also sort the groups
SELECT COUNT(*) "Employee Count", manager_id FROM employees
GROUP BY manager_id ORDER BY manager_id;
-- you can use MIN to find the minimum salary for employees with manager 122
SELECT MIN(salary) FROM employees WHERE manager_id = 122;
-- this computes the minimum and maximum salary by job_id groups
-- the job_ids groups are sorted in alphabetical order
SELECT MIN(salary), MAX(salary), job_id FROM employees
GROUP BY job_id ORDER BY job_id;
Creating a Simple Table
-- create a simple table for keeping track of birthdays
CREATE TABLE my_birthdays
( first_name VARCHAR2(20),
last_name VARCHAR2(25),
bday_date DATE
);
Creating a Table With Constraints
-- create a table similar to the employees table in the HR schema
CREATE TABLE my_employees
( employee_id NUMBER(6),
first_name VARCHAR2(20),
last_name VARCHAR2(25) CONSTRAINT my_emp_last_name_nn NOT NULL,
email VARCHAR2(25) CONSTRAINT my_emp_email_nn NOT NULL,
phone_number VARCHAR2(20),
hire_date DATE DEFAULT SYSDATE CONSTRAINT my_emp_hire_date_nn NOT NULL,
job_id VARCHAR2(10) CONSTRAINT my_emp_job_nn NOT NULL,
salary NUMBER(8,2) CONSTRAINT emy_mp_salary_nn NOT NULL,
commission_pct NUMBER(2,2),
manager_id NUMBER(6),
department_id NUMBER(4),
CONSTRAINT my_emp_salary_min CHECK (salary > 0),
CONSTRAINT my_emp_email_uk UNIQUE (email)
);
Creating, Modifying, and Dropping an Index
-- create a new index on the employees table using the email column
CREATE INDEX email_ix
ON employees (email);
-- disable the index
ALTER INDEX email_ix
RENAME TO my_email_ix;
-- drop the index
DROP INDEX my_email_ix;
-- create an index on a single column to make queries faster on that column
CREATE INDEX emp_last_name_ix ON employees (last_name);
DROP INDEX emp_last_name_ix;
-- create an index on two columns to make queries faster on the first column
-- or both columns
CREATE INDEX emp_mgr_id_ix ON employees (employee_id, manager_id);
DROP INDEX emp_mgr_id_ix;
-- a function-based index precalculates the result and speeds up queries that
-- use the function for searching or sorting, in this case UPPER(last_name)
CREATE INDEX emp_upper_last_name_ix ON employees (UPPER(last_name));
DROP INDEX emp_upper_last_name_ix;
Creating and Altering a Constraint
-- add a constraint a new constraint
ALTER TABLE my_employees
ADD CONSTRAINT ...
-- remove the constraint on email in the my_employees table
ALTER TABLE my_employees
DROP UNIQUE (email);
Altering a Table
-- add a new column to my_birthdays
ALTER TABLE my_birthdays
ADD (age NUMBER(3));
-- rename the my_employees table
ALTER TABLE my_employees RENAME to temp_employees;
Dropping a Table
-- drop tables from the database
-- use caution when use the DROP statement!
DROP TABLE my_birthdays;
DROP TABLE temp_employees;
Creating and Dropping Sequence
-- create a new sequence to use with the employees table
CREATE SEQUENCE new_employees_seq START WITH 1000 INCREMENT BY 1;
-- to use the sequence, first initialize the sequence with NEXTVAL
SELECT new_employees_seq.NEXTVAL FROM DUAL;
-- after initializing the sequence, use CURRVAL as the next value in the sequence
INSERT INTO employees VALUES
(new_employees_seq.CURRVAL, 'Pilar', 'Valdivia', 'pilar.valdivia',
'555.111.3333', '01-SEP-05', 'AC_MGR', 9100, .1, 101, 110);
-- query the employees table to check the current value of the sequence
-- which was inserted used as employee_id in the previous INSERT statement
SELECT employee_id, last_name FROM employees WHERE last_name = 'Valdivia';
-- drop the sequence
DROP SEQUENCE new_employees_seq;
Creating and Dropping a Synonym
-- create a synonym for the employees table
CREATE SYNONYM emps for HR.employees;
-- query the employees table using the emps synonym
SELECT employee_id, last_name FROM emps WHERE employee_id < 105;
-- drop the synonym
DROP SYNONYM emps;
SQL> conn sys/pass as sysdba;
Connected.
SQL> alter user scott account unlock; --解锁用户scott
User altered.
SQL> commit;
Commit complete.
SQL> conn scott/tiger
SQL> conn sys/pass as sysdba;
Connected.
SQL> alter user hr identified by pass; --修改hr的密码为pass
User altered.
SQL> conn hr/pass
Connected.
