I. oracle basic SQL statements and functions, and oraclesql Functions

I. oracle basic SQL statements and functions, and oraclesql Functions

I. Data Definition Language (ddl)

Data definition language (ddl) is used to change the database structure, including creating, changing, and deleting database objects.

The Data Definition Language commands used to manipulate the table structure include:

Create table

Alter table

Truncate table

Drop table

Eg,

-- Create the data structure of the tb_stu table

Create table tb_stu (

Id number,

Name varchar2 (20)

);

 

-- Modify the data structure of the tb_stu table and add a column

Alter table tb_stu add pwd varchar2 (6 );

-- Modify a field

Alter table tb_stu modify pwd varchar2 (20 );

-- Rename the table: rename table_name1 to table_name2;

RENAME student TO tb_student;

-- Rename the 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 data in the tb_stu table

Truncate table tb_stu;

 

-- Delete the tb_stu table

Drop table tb_stu;

2. Data Operation Language (dml)

Data manipulation language (dml) is used to retrieve, insert, and modify data. dml uses statements such as insert, select, update, and delete to operate data contained in database objects.

(1) create a table using an existing table

Syntax: create table <new_table_name>

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;

3) create table tb_dept as select * from dept a where a. deptno = 10;

 

(2) Select rows without duplicates and use the distinct keyword

Eg, select distinct a. dname from tb_dept;

 

(3) Use column aliases

Select a. deptno Department number, a. dname "department name" from tb_dept;

-- There is a space in the middle, and "" is used ""

 

(4) Insert 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;

 

Iii. Transaction Control Language (tcl)

Put it in "Transactions" to explain

4. Data Control Language (dcl)

Go to "users and permissions" to explain

V. SQL Operators

Omitted

Vi. oracle Functions

1. Character Functions

Initcap (char) function: uppercase letter

Eg, select initcap ('hello') from dual;

Output result: Hello

 

Lower (char) function: converts all letters to lowercase letters.

Eg, select lower ('hello') from dual;

Output result: hello

 

Upper (char) function: converts all letters to uppercase letters.

Eg, select upper ('hello') from dual;

Output result: HELLO

 

Ltrim (char, set) function: removes the specified character on the left.

Eg, select ltrim ('xyzadmin', 'xyz') from dual;

Output result: admin

 

Rtrim (char, set) function: removes the specified character on the right.

Eg, select rtrim ('xyzadmin', 'admin') from dual;

Output result: xyz

 

Translate (char, from, to) function: returns to replace each character in from with a string after the corresponding character in.

Eg, 1), select translate ('jack', 'J', 'B') from dual; -- replace j with B

Output result: back

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

Output result: 143ab

Resolution: replace 2 with 4,

D. do not replace the string because it does not exist,

C because there is no replacement character, c in the string will be deleted

 

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 intercepted string in the source string. No 0 is returned.

Eg, select instr ('worldwid', 'D') from dual;

Output result: 5

Instr (C1, C2, I, J) --> determines whether a character or string exists and returns an index at the position where it appears. Otherwise, the return value is less than 1; searches for a specified character in a string and returns the location where the specified character is found;
String searched by C1
String to be searched by C2
The start position of the I search. The default value is 1.
Where J appears. The default value is 1.

 

Substr (char, m, n) function: truncates a 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: returns an integer and returns the corresponding character;

Eg, select chr (54740), chr (65) from dual;

Output result: Zhao

 

Lpad and rpad functions: paste characters

Lpad paste characters on the left of the column

Pad the character on the right of the column

For example, SELECT lpad ('logging', 10, '*') FROM dual;

Output result: *** Lin jiqin

Note: If the character is not enough, use * to fill it up.

 

Trim function: removes empty strings on both sides of the string. If this parameter is not specified, it is a space character by default.

 

Length (char) function: returns the length of a string.

Eg, select length ('abcdef') from dual;

Output result: 6

2. Date and Time Functions

Add_months function: add or subtract a month

Eg,

Select to_char (add_months (to_date ('1970-11-30 ', 'yyyy-mm-dd'), 2), 'yyyy-mm-dd') from dual; -- Forward

Output result:

Select to_char (add_months (to_date ('1970-11-30 ', 'yyyy-mm-dd'),-2), 'yyyy-mm-dd') from dual; -- back

Output result: 2011-9-30

 

Months_between (date2, date1) function: returns the month of the date2-date1.

Eg, select months_between ('19-february 1, 1999-February 2, 1999-February 2, ') from dual;

Output result: 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 result: 23:27:20

 

Round and trunc functions: Rounding according to the specified precision

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

Output result: 56-55 55-55

 

Next_day (date, 'day') function: calculates the date of the next week Based on the date and day of the week.

For example, select next_day ('30-May 20-2011 ', 'weday') next_day from dual;

Output result:

 

Extract (datetime) function: Get the Time Function

Eg,

Output result:

Select extract (year from sysdate) from dual; -- get YEAR

Select extract (month from sysdate) from dual; -- get MONTH

Select extract (day from sysdate) from dual; -- get DAY

Select to_char (sysdate, 'hh24') from dual; -- get hour

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

Select to_char (sysdate, 'ss') from dual; -- get seconds

 

Select extract (year from Hangzhou imestamp) year

, Extract (month from Hangzhou imestamp) month

, Extract (day from Hangzhou imestamp) day

, Extract (minute from policimestamp) minute

, Extract (second from policimestamp) second

, Extract (timezone_hour from policimestamp) th

, Extract (timezone_minute from policimestamp) 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 result: 15

Round (m, n) Rounding function:

Eg, select round (45.926, 2) from dual;

Output result: 45.93

Select round (45.926,-1) from dual;

Output result: 50

Select round (245.926,-2) from dual;

Output result: 200

Select round (275.926,-2) from dual;

Output result: 300

 

Trunc (m, n) truncation Function

I. Date

The date value intercepted by the trunc function for the specified element.

The syntax format is as follows:

TRUNC (date [, fmt])

Where: date is a date value.

Fmt date format, which is truncated by the specified Element format. Ignore it and it is intercepted by the latest 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 return the first day of the current year

Select trunc (sysdate, 'dd') from dual -- 2011-3-18 return current year month day

Select trunc (sysdate, 'yyyy') from dual -- 2011-1-1 return the first day of the current 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

Select trunc (sysdate, 'mi ') from dual -- 2011-3-18 14:41:00 the TRUNC () function does not have second precision

 

Ii. Numbers

Trunc (number, num_digits)

Number.

Num_digits is used to specify the number to take an integer. The default value of num_digits is 0.

When trunc () function is intercepted, No rounding is performed.

 

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) from dual --

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

 

Mod (m, n) Evaluate the remainder Function

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

Output result: 1

 

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

Eg, select ceil (44.778) from dual;

Output result: 45

 

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

Eg, select ceil (44.778) from dual;

Output result: 44

4. conversion functions

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

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

Select to_number ('123') from dual;

5. Mixed Functions

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 the to_char function is displayed.

 

Nvl2 (e1, e2, e3) function: If e1 is null, the function returns e3; otherwise, the function returns e2.

 

The nullif (exp1, expr2) function returns null if exp1 and exp2 are equal. Otherwise, the first value is returned.

Eg, select nullif ('A', 'A') from dual;

Output result: NULL is returned, and no value is returned.

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

Output result:

 

6. Grouping Functions

Avg (n), min (n), max (n), sun (n), count (n)

8. oracle multi-Table query

 

Multi-table joint Query

You can create a multi-Table query through a connection. The data queried from multiple tables can come from multiple tables, but appropriate connection conditions must exist between tables. To query multiple tables, you must identify the public columns that are connected to multiple tables. Generally, a comparison operator is used in the WHERE clause to specify the conditions for connection.

 

There are four ways to connect two tables:

* Equal connection

* Unequal join (as a single table query)

* Outer Join (left join and right join; left join: all are displayed in the left table. Only matching information is displayed in the right table, and right join is vice versa .)

* Self-join)

 

1. Equal connection

Two columns with the same meaning can be used to create equal join conditions. When two tables are queried using equal join, only rows with the same value in the two tables with the same join column will appear in the query results.

Displays the employee name and department ID and name.

Run the following query:

SELECT a. ename, B. id, B. dname

FROM employee a, dept B

WHERE a. id = B. id

Note: the format of the Equi-join statement is to list the names of two tables in sequence in the FROM clause. You must add the table name before each column of the table, and use ". columns are separated to indicate different tables. In the WHERE condition, specify the columns for equal join.

In the preceding training, the column names that do not appear in both tables can be omitted. Therefore, the preceding example can be simplified as follows:

SELECT *

FROM emp e, dept d

WHERE e. deptno = d. deptno;

 

2. External Connection

In the above example, there is a problem with equal connection: if an employee's department is not filled in, that is, it is left blank, then the employee will not appear in the query; or a department that has no employees does not appear in the query.

To solve this problem, you can use external connections. In addition to displaying records meeting equal connection conditions, the rows that do not meet the connection conditions are displayed. rows that do not meet the connection conditions are displayed at the end. The outer join operator is (+), which can appear on the left or right of equal join conditions. The meaning on the left or right is different. The following example is used to describe the meaning.

 

Use external connections to display records that do not meet equal conditions.

Displays the employee name and department ID and name.

Run the following query:

Left join method 1 (recommended and concise ):

-- The main table is opposite to the "+" (Appendix). It can be understood that all tables of the main table are displayed. If the "+" symbol does not match, a null value is displayed.

-- Whether or not dept exists, emp is displayed (emp is used as the primary table)

SELECT * FROM emp e, dept d WHERE d. deptno (+) = e. deptno; -- left join

SELECT * FROM emp e, dept d WHERE e. deptno = d. deptno (+); -- right join

-- Dept is displayed no matter whether emp exists (dept is used as the primary table)

SELECT * FROM emp e, dept d WHERE e. deptno (+) = d. deptno; -- left join

SELECT * FROM emp e, dept d WHERE d. deptno = e. deptno (+); -- right join

 

Method 2 of left join:

-- Whether or not dept exists, the employee ID is displayed.

SELECT * FROM emp e left join dept d ON d. deptno = e. deptno;

SELECT * FROM dept d right join emp e ON d. deptno = e. deptno;

 

3. Self-join (generally used in the tree-like permission structure)

A self-join table is connected to itself. For a self-join, you can imagine that two identical tables (copies of tables) exist. You can use different aliases to distinguish two identical tables.

SELECT worker. ename | 'the manager is' | manager. ename AS the employee manager

FROM employee worker, employee manager

WHERE worker. mgr = manager. empno;

 

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

The execution result is:

1. SMITH's manager is FORD.

2. ALLEN's manager is BLAKE.

3. WARD's manager is BLAKE.

 

 

Note: When performing multi-table join queries, a Cartesian product is formed if the following conditions occur:

-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 is also called the straight product. Assume that the Set A = {a, B}, and set B = {0, 1}, the Cartesian product of the Two sets is {(a, 0), (a, 1 ), (a, 2), (B, 0), (B, 1), (B, 2 )}. It can be expanded to multiple sets.

 

9. Set connection

10. Expansion

1. log on to the oracle database on the cmd Console: sqlplus scott/oracle @ orcl

 

2. Get the current time

--> Select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') from dual;

Output result value: 19:40:55

 

Get the current time (the decimal point is accurate to the next six digits, and 6 is variable)

--> Select to_char (systimestamp, 'yyyy-mm-dd hh24: mi: ssxff6') from dual;

Output result value: 19:45:35. 791000

 

3. view the table structure: desc dept;

 

4. Modify the Date Format: alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss'; (Note: It takes effect only in the current session)

 

5. Two null insertion Methods

Method 1: insert into tb_stu values (1, null, to_date ('201312', 'yyyymmdd '));

Method 2: insert into tb_stu (id, birthday) values (1, to_date ('20170101', 'yyyymmdd '));

 

6. clear screen: clear screen;

7. Two single quotes in oracle indicate one single quotation mark

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

 

8. oracle matching

Eg, select * from tb_stu where pwd like 'a % '-- match multiple

Select * from tb_stu where pwd like 'A _ '-- match

 

9. drop table tb_dept purge; -- permanently delete

Drop table tb_dept; -- put the table into the recycle bin. The table is not completely deleted.

 

10. select to_char (33, 'l99. 99') from dual; -- Obtain the local currency symbol and the output result is: ￥33.00.

Select to_char (33, '$99.99') from dual; -- get USD

 

11. Empty expressions in oracle

Select ''from dual;

Select null from dual;

 

12. query all the table names in the current database.

Select * from tab;