Iv. Oracle Basic SQL statements and functions

I. Oracle COMMON data types

One, data definition language (DDL)

Data definition language is used to alter the database structure, including creating, changing, and deleting database objects.

The data definition language commands used to manipulate the table structure are:

CREATE table

ALTER TABLE

TRUNCATE TABLE

drop table

eg

-- Create a Tb_stu table data structure

CREATE TABLE Tb_stu (

ID number,

Name VARCHAR2 (20)

);

-- Modify the Tb_stu table data structure and add a new column

ALTER TABLE Tb_stu add pwd varchar2 (6);

-- Modifying Fields

ALTER TABLE Tb_stu modify PWD varchar2 (20);

--Renaming tables: rename table_name1 to Table_name2;

RENAME student to Tb_student;

--Rename column: ALTER TABLE table_name rename column col_oldname to Colnewname;

ALTER TABLE student RENAME COLUMN pwd to pwd1;

-- Delete a field

ALTER TABLE tb_stu drop column pwd;

-- Delete tb_stu table Data

TRUNCATE TABLE tb_stu;

-- Delete tb_stu table

drop table Tb_stu;

Ii. Data Manipulation Language (DML)

Data manipulation language DML, data manipulation language, is used to retrieve, insert, and modify information, and DML uses statements such as INSERT, select, Update, and delete to manipulate the data contained by database objects.

(1). Create a table with an existing table

Syntax: CREATE TABLE <new_table_name> as

Select Column_names from <old_table_name>;

eg

1), CREATE TABLE tb_dept as SELECT * from dept;

2), CREATE table tb_dept as select A.deptno, A.dname from dept A;

3), CREATE TABLE tb_dept as SELECT * FROM dept a where a.deptno=10;

(2), select a row without duplicates, use the DISTINCT keyword

eg, select distinct a.dname from Tb_dept A;

(3), using column aliases

Select A.deptno department number, a.dname "department name" from Tb_dept A;

-- There are spaces in the middle, with ""

(4), inserting records from other tables

Syntax: INSERT into <table_name> [(cloumn_list)]

Select Column_names from <other_table_name>;

eg, insert into Student2 select * from student;

Third, the Transaction control Language (TCL)

Put it in the "transaction" tutorial

Iv. Data Control Language (DCL)

Go to "Users and Permissions" explained

V. SQL operators

Slightly

Vi. Oracle Functions

1. Character functions

Initcap (char) function: Capitalize first letter

eg, select Initcap (' Hello ') from dual;

Output Result: Hello

Lower (char) function: All letters are converted to lowercase

eg, select lower (' HeLLo ') from dual;

Output Result: Hello

Upper (char) function: All letters are converted to uppercase

eg, select upper (' Hello ') from dual;

Output Result: HELLO

LTrim (char,set) function: Remove the character specified on the left

eg, select LTrim (' xyzadmin ', ' XYZ ') from dual;

Output Result: admin

RTrim (char,set) function: Remove the character specified on the right

eg, select RTrim (' xyzadmin ', ' admin ') from dual;

Output Result: XYZ

Translate (char, from, to) function: Returns a string after each character in the from is replaced with the corresponding character in the to

eg, 1), select Translate (' Jack ', ' J ', ' B ') from dual; --To replace J with B

Output Result: Back

2), select Translate (' 123abc ', ' 2dc ', ' 4e ') from dual;

Output Result: 143ab

Resolution: 2 is replaced with 4,

D because there is nothing in the string, so no substitution,

C is removed from the string because there is no corresponding substitution character.

Replace (char, SearchString, [rep string]) function: replace

eg, select replace (' Jack and Jue ', ' j ', ' BL ') from dual;

Output result: Black and blue

InStr (Char, M, n) function: Returns the position of the truncated string in the source string without returning 0

Eg, select InStr (' Worldwide ', ' d ') from dual;

Output results: 5

InStr (C1,C2,I,J)--Determines whether a character or string exists, returns the index of where it appears, otherwise returns less than 1, searches for the specified character in a string, and returns the location of the specified character;
C1 string to be searched
C2 the string you want to search
I Search start position, default is 1
Where J appears, default is 1

substr (char, m, n) function: Intercept string

eg, select substr (' ABCDEFG ', 3, 2) from dual;

Output Result: CD

Concat (expr1, EXPR2) function: Connection string

eg, select concat (' Hello ', ' world ') from dual;

Output Result: Hello World

Chr (number) function: Give an integer and return the corresponding character;

eg, select Chr (54740), Chr (+) from dual;

Output Result: Zhao A

Lpad and Rpad functions: pasting characters

Lpad pasting characters to the left of a column

Rpad pasting characters to the right of a column

eg, SELECT lpad (' Lin Yi-chin ', ten, ' * ') from dual;

Output Result: * * * Lin Yi-chin

Note: Not enough characters are filled with *

Trim function: Removes empty strings on both sides of the string, or whitespace if not specified.

Length (char) function: Returns the lengths of a string

eg, select Length (' abcdef ') from dual;

Output results: 6

2. Date Time function

add_months function: Add or subtract a month

eg

Select To_char (Add_months (to_date (' 2011-11-30 ', ' yyyy-mm-dd '), 2), ' Yyyy-mm-dd ') from dual;--onward

Output Result: 2012-01-31

Select To_char (Add_months (to_date (' 2011-11-30 ', ' yyyy-mm-dd '), -2), ' Yyyy-mm-dd ') from dual;--back

Output Result: 2011-9-30

Months_between (date2,date1) function: gives the month of Date2-date1

eg, select Months_between (' 1 September-December-1999 ', ' 1 September-March -1999 ') from dual;

Output results: 9

Last_day function: Returns the last day of the date

eg, select To_char (Last_day (sysdate), ' Yyyy-mm-dd Hh24:mi:ss ') from dual;

Output: 2011-11-30 23:27:20

Round and TRUNC functions: Rounding with the specified precision

eg, select round (55.5), round ( -55.4), trunc (55.5), Trunc ( -55.5) from dual;

Output results: 56-55 55-55

Next_day (date, ' Day ') function: Date of date and day of the week calculated for one weeks

eg, select Next_day (' 30月-November-2011 ', ' Wednesday ') next_day from dual;

Output: July-December-11

Extract (DateTime) function: Get Time function

eg

Output Result:

SELECT EXTRACT (year from sysdate) from DUAL; --Get year

SELECT EXTRACT (MONTH from sysdate) from DUAL; --Get the month

SELECT EXTRACT (Day from sysdate) from DUAL; --Acquisition Day

Select To_char (sysdate, ' hh24 ') from dual; --Get hours

Select To_char (sysdate, ' mi ') from dual;--get minutes

Select To_char (sysdate, ' SS ') from dual;--get seconds

Select Extract (year from Systimestamp) year

, extract (month from Systimestamp) month

, extract (day from Systimestamp) Day

, extract (minute from systimestamp) minute

, extract (second from systimestamp) second

, extract (Timezone_hour from systimestamp) th

, extract (Timezone_minute from Systimestamp) TM

, extract (Timezone_region from Systimestamp) tr

, extract (Timezone_abbr from Systimestamp) ta

from dual;

3. Numeric functions

ABS function: Absolute value

eg, select ABS ( -15) from dual;

Output results: 15

Round (M, N) rounding function:

eg, select Round (45.926, 2) from dual;

Output results: 45.93

Select Round (45.926,-1) from dual;

Output results: 50

Select Round (245.926,-2) from dual;

Output results: 200

Select Round (275.926,-2) from dual;

Output results: 300

Trunc (M, n) intercept function

First, date

The date value that the Trunc function intercepts for the specified element.

Its specific syntax format is as follows:

TRUNC (Date[,fmt])

Where: Date a datetime value

FMT date format, which is truncated by the specified element format. Ignoring it is truncated by the nearest date.

If the date of the day is: 2011-3-18

Select Trunc (sysdate) from dual--2011-3-18 today's date is 2011-3-18

Select Trunc (sysdate, ' mm ') from dual--2011-3-1 returns the first day of the month.

Select Trunc (sysdate, ' yy ') from dual--2011-1-1 returns the first day of the year

Select Trunc (sysdate, ' DD ') from dual--2011-3-18 back to current month day

Select Trunc (sysdate, ' yyyy ') from dual--2011-1-1 returns the first day of the year

Select Trunc (sysdate, ' d ') from dual--2011-3-13 (Sunday) returns the first day of the current week

Select Trunc (sysdate, ' hh ') from dual--2011-3-18 14:00:00 current time is 14:41

Select Trunc (sysdate, ' mi ') from dual--2011-3-18 14:41:00 trunc () function does not have the exact seconds

Second, the number

Trunc (Number,num_digits)

Number requires a truncated rounding.

The num_digits is used to specify the number of rounding precision. The default value for Num_digits is 0.

Trunc () function is not rounded when truncated

Select Trunc (123.458) from dual--123

Select Trunc (123.458,0) from dual--123

Select Trunc (123.458,1) from dual--123.4

Select Trunc (123.458,-1) from dual--120

Select Trunc (123.458,-4) from dual--0

Select Trunc (123.458,4) from dual--123.458

Select Trunc (123) from dual--123

Select Trunc (123,1) from dual--123

Select Trunc (123,-1) from dual--120

MoD (m,n) to find the remainder function

eg, select mod (5,2) from dual;

Output results: 1

Ceil (n) function: takes the smallest integer greater than or equal to the value n

eg, select Ceil (44.778) from dual;

Output results: 45

Floor (n) function: takes the largest integer less than or equal to the value n

eg, select Ceil (44.778) from dual;

Output results: 44

4. Conversion function

Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual;

Select To_date (' 2011-12-01 10:33:56 ', ' yyyy-mm-dd hh24:mi:ss ') from dual;

Select To_number (' n ') from dual;

5. Mixed function

NVL (string1, replace_with) function: If string1 is null, the NVL function returns the value of Replace_with, otherwise the value of string1 is returned.

Note: string1 and replace_with must be of the same data type unless shown using the To_char function.

The function of NVL2 (E1, E2, E3) is: If E1 is null, the function returns E3, otherwise it returns E2.

The effect of the Nullif (EXP1,EXPR2) function is to return null (NULL) if EXP1 and exp2 are equal, otherwise the first value is returned.

eg, select Nullif (' A ', ' a ') from dual;

Output: Return null, no value

Select Nullif (' A ', ' 1 ') from dual;

Output Result: A

6. Grouping functions

AVG (n), Min (n), Max (n), Sun (n), COUNT (n)

Eight, Oracle Multi-Table query

Multi-Table Union query

A multi-table query can be established through a connection, and data from multiple table queries can come from more than one table, but the tables must have appropriate connection conditions. In order to query from multiple tables, you must identify the public columns that are connected to more than one table. The condition of a connection is typically indicated by a comparison operator in the WHERE clause.

There are four ways to connect two tables:

* Equal connection

* Unequal connection (as a single table query)

* Outer connection (left connection, right connection, left connection, left table all display, right table only shows matching information, right connection, vice versa). )

* Self-connect (self-correlating)

1. Equal connections

You can establish an equal join condition by using two tables that have the same meaning. When a query with two tables is made using an equal join, only rows that appear in two tables on the Join column and have equal values appear in the query results

Displays the employee name and the number and name of the department in which it is located.

Execute the following query:

SELECT a.ename, b.ID, B.dname

From employee A,dept b

WHERE a.id=b.id

Description: The format requirement for an equal join statement is to list the names of the two tables in the FROM clause, and to add the table name before each column of the table, using "." Delimited to indicate that the column belongs to a different table. In the Where condition, specify the columns for which you want to make an equal connection.

In the above training, the preceding table name prefixes can be omitted from columns that do not appear in the two tables. So the above example can be simplified to the following expression:

SELECT *

From EMP E, Dept D

WHERE E.deptno=d.deptno;

2. External connection

In the example above, there is a problem with an equal connection: If an employee's department has not been filled out, that is, the employee will not appear in the query, or a department does not have an employee, and the department will not appear in the query.

In order to solve this problem, you can use a connection, that is, in addition to displaying records that meet the conditions of an equal join, the rows that do not meet the join conditions are displayed, and the lines that do not meet the join criteria are displayed at the end. The outer JOIN operation identifier (+), which can appear on the left or right side of an equal connection condition. The meanings that appear on the left or right are different, as illustrated in the following examples.

Use a foreign connection to display records that do not meet the equality criteria.

Displays the employee name and the number and name of the department in which it is located.

Execute the following query:

Left Join method One (recommended use, concise):

--+ number ( schedule ) the opposite is the main table, you can understand the main table display all, + this way, if there is no match, the null value is displayed .

-- no matter Dept is present, EMP will show (EMP when Main Table )

SELECT * from EMP E, dept D WHERE D.deptno (+) =e.deptno; -- left Connection

SELECT * from EMP E, dept D WHERE E.deptno=d.deptno (+); -- Right Connection

-- no matter EMP is present, Dept will show (Dept as Main Table )

SELECT * from EMP E, dept D WHERE E.deptno (+) =d.deptno; -- left Connection

SELECT * from EMP E, dept D WHERE D.deptno=e.deptno (+); -- Right Connection

Left Join connection method two:

-regardless of whether the dept exists, the employee will show

SELECT * from EMP E left joins dept D on D.deptno=e.deptno;

SELECT * FROM Dept D right JOIN emp E on D.deptno=e.deptno;

3, self-connection (generally used in tree-type permission structure)

A self-connection is a table that is connected to itself. For self-joins you can imagine that there are two identical tables (a copy of tables and tables), and you can differentiate two identical tables by different aliases.

SELECT worker.ename| | ' 's manager is ' | | Manager.ename as employee manager

From employee worker, employee manager

WHERE worker.mgr = manager.empno;

------------

The result of the execution is:

The manager of 1.SMITH is FORD

The manager of 2.ALLEN is BLAKE

The manager of 3.WARD is BLAKE

Note: In the operation of multi-table joint query, if the following conditions occur, the Cartesian product will be formed

– The join condition is omitted

– Invalid Join condition

– All rows in the first table are joined to all rows in the second table

To avoid Cartesian product, always include valid join conditions

What is Cartesian product?

The product of Descartes (Descartes) is also called direct product. Assuming collection a={a,b}, set b={0,1,2}, the Cartesian product of two sets is {(a,0), (a,1), (a,2), (b,0), (b,1), (b,2)}. Can be extended to multiple collections of cases.

Nine, set connection

X. expansion

1. CMD Console Login to Oracle database: Sqlplus scott/[email protected]

2. Get the current time

--Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual;

Output result value: 2011-11-30 19:40:55

Gets the current time (decimal point is accurate to the back 6 bits, 6 is variable)

-->select to_char (Systimestamp, ' Yyyy-mm-dd hh24:mi:ssxff6 ') from dual;

Output result value: 2011-11-30 19:45:35.791000

3, view table structure: DESC dept;

4, Modified date format: Alter session set nls_date_format= ' Yyyy-mm-dd hh24:mi:ss ';(Note: Only valid in the current session)

5. Two methods of inserting null values

Method one: INSERT into tb_stu values (1, NULL, to_date (' 20111130 ', ' YYYYMMDD '));

Method Two: INSERT into Tb_stu (ID, Birthday) VALUES (1, to_date (' 20111130 ', ' YYYYMMDD '));

6, clear screen: clear screens;

7. Two single quotes in Oracle represent a single quotation mark

eg, update tb_stu set pwd= ' 1 ' 2 ' where id=1;

8. Oracle Match

Eg, select * from Tb_stu where pwd like ' A% '--matches multiple

SELECT * from Tb_stu where pwd like ' a_ '--matches a

9, drop table tb_dept purge; --Permanently deleted

drop table tb_dept; --Put the table in the Recycle Bin, and not completely delete the table

10, select To_char ("L99.99") from dual; --Get the local currency symbol, the output is: ￥33.00

Select To_char (, ' $99.99 ') from dual; --Get dollars

11. Two expressions for NULL in Oracle

Select ' from dual;

Select null from dual;

12. Querying all table names in the current database

SELECT * from tab;

Ext.: http://www.cnblogs.com/linjiqin/archive/2012/03/05/2379964.html

Iv. Oracle Basic SQL statements and functions