Oracle Learning II: Table Management (data type, create/modify table, add/modify/delete data, data query)

Management of 1.Oracle Tables

• Naming conventions for table and column names:
• Must start with a letter;
• Length cannot exceed 30 characters;
• Cannot use Oracle's reserved words;
• Use only the following characters: A-Z, A-Z, 0-9, $, # etc.
• Data types supported by Oracle:
• Character type

Char fixed length maximum of 2000 characters

char (four)----' Little Han ' before the descriptors ' Xiao Han ', after adding 6 blanks to complement the whole. (High query efficiency)

VARCHAR2 (20) variable length max 4,000 characters

VACHAR2----' Korea ' Oracle assigns four characters, which saves space.

CLOB (abbreviation for character large object) maximum 4G for character type large objects

• Digital type

Number---Range: negative 10 of 38 square----10 38-time Square;---can represent integers or decimals

Eg:number (5,2)---indicates that a decimal number has 5 valid digits, 2 decimal places; Range: 999.99--999.99

Number (5)---represents a five-bit integer; Range: 99999--99999

• Date type

Date---Contains date and time of day and seconds

Timestamp---oracle9i extends the date data type to the millisecond level.

• Image

BLOB---Binary data, can store picture/sound 4G

Note: When storing pictures/sounds, the path is generally stored in the database, unless security is particularly high.

• Create a table
• CREATE TABLE table name (column list type, ...);

View the structure of the table: Desc table name;

• Modify Table
• Add a field:

Sql>alter table name Add (column name, column type);

• Modify the length of a field

Sql>alter Table student Modify (column name, column type);

• Modify the type/name of the field (cannot have data)

Sql>alter Table student Modify (column name, column type);

• Delete a field (this action is generally not recommended)

Sql>alter table student drop column name;

• Modify the name of the table

Sql>rename table name to new table name;

• Delete a table

Sql>drop table name;

• Table query (again later)
• Inserting data
• All fields are inserted

Insert into table name values (one record);

Tips: The default date format for Oracle is: ' Dd_mon (eg:5 month)-yy/yyyy ';

Change the default format for dates: Alter session set Nls_date_format = ' Yyyy-mm-dd ';

• Insert a partial field

Insert into table name (column 1, column 2, column 3, ...) VALUES (value 1, value 2, value 3, ...) ；

• Insert Null value

Insert into table name (column 1, column 2, column 3, ...) VALUES (value 1,null (null value), value 3, ...) ；

• Update data
• Change one or more fields

Update student Set column Name 1 = actual value 1, column name 2 = actual value 2, ... where column name = ' Pre-supplied value ';

• Modifying data that contains null values
• Delete data
• Delete from table name,---Remove all records, table structure is still in, write log, can recover, slow.

Recover Data---A. Set savepoint: savepoint point name; b. Rollback to SavePoint: rollback to point name;

• drop table name;---Delete the structure and data of the tables
• Delete from table name where column name = ' value ';---delete a record.
• TRUNCATE TABLE name; ---Delete all the records in the table, the table structure is still there, not write logs, cannot be recovered, fast.

Basic queries for 2.Oracle tables

• A simple query statement
• To view the table structure:

DESC table name;

• Query all columns:

SELECT * from table name;

tips:1.set timing on/off; ---switch to turn on time consumption;

2. The column names of the table are case-insensitive, and the recorded values are case-sensitive.

• Query the specified column:

Select the column name specified by the From table name;

• To cancel a duplicate row:

Select DISTINCT column name 1, column Name 2, ... from table name;

• Using an arithmetic expression: +-*/
• Use aliases for columns (spaces or AS): Table aliases do not use as, spaces

Select ename (column name) "Name (alias)", sal*12 as "yearly Income (alias)" from EMP (table name);

When you use aliases that contain case, space, and special characters, you need double quotation marks ("") instead of single quotes to enclose them, often giving an expression a meaningful alias.

• How to handle null values:

Use the NVL function to handle: NVL (column name, 0)---The column has a value of 0 if it is recorded as NULL, otherwise the actual value of the column.

• How to connect strings (| |): strings are enclosed in single quotation marks (").

Eg:select ename | | ' is a ' | | Job from EMP;

• Query using the WHERE clause:

Eg: date after January 1, 1982:time> ' January-January -1982 '; Sal is between 2000 and 2500: sal>2000 and sal<2500;

• Query criteria:

1. Comparison operators: <, >, <=, >=, <>/!, =

2. Using logical operators: OR And not

3. Other operators:

Like and not-like operators:%: Any 0 to more characters, _: denotes any single character, not like/like ' match string ' [escape ' swap character '] eg:like '%\_a% ' escape ' \ ';

In () and not IN (): determines the discrete range. ---where column name in (column value 1, column value 2, column value 3, ...);

Between A and b not between A and B: determines the continuous range.

Is null and not is null: Test is empty

Precautions:

1. When using numeric values in the WHERE clause, you can use either single quotation marks or no use, and you must use single quotes when using string values and date values. And the string value is case-sensitive, the date value is format-sensitive.

2. The default format for date values is dd-mm-yy, otherwise the To_date function must be converted.

• Using the ORDER BY statement

Order BY Column name 1 ASC (ascending), column name 2 DESC (descending);

Note: You can sort multiple columns; The ORDER BY clause must be the last clause; you can sort by using the alias of the column, the position of the column, or the column that is used for sorting (distinct not available);

Eg:select column name 1, column name 2 as "Alias", ... from table name ORDER by "Alias" ASC;

• Paging query (three ways) Speed: Law two > Law one > Law three

Law One: rownum page: rownum--line number eg: Record: 6--10

A. Make a subquery and consider it an inline view; select * from table;

B. Show rownum[oracle assigned]; Select A1.*,rownum rn from (SELECT * from table) A1;

C. Display records with line numbers less than 10; Select A1.*,rownum rn from (SELECT * from table) as A1 where rownum<=10;

D. Display records with line numbers greater than 6; select * FROM (select A1.*,ROWNUMRN rn from (SELECT * from table) as A1 where rownum<=10) where rn>6;

Explanation: 1. When querying only part of the column information, you only need to modify the specified column information for the innermost table.

2. Sort: Only the column information of the innermost table can be sorted, and the other groups can only change the column information of the innermost table.

3. The position of rownum, RN, etc. in step a,b,c cannot be changed.

Law II: According to rowID to divide:

SELECT * FROM table name where rowID in

(Select Rid from

(select RowNum RN, RID from

(select rowID rid,cid from table name ORDER BY cid DESC)

where rownum<10000)

where rn>9980)

ORDER BY DESC;

Law three: According to the analysis function to divide:

SELECT * FROM (select t.*, Row_number () over (order BY CID DESC) RK from table name T) where rk<10000 and rk>9980

3.Oracle Table Complex Query

• Data grouping
• Grouping functions:

max--the maximum value of a column or expression (maximum data, the latest time),<--> min,

avg--the average of a column or expression, sum--a column or an expression's aggregate value

count--returns the number of rows in a record row

Variance-The variance of a column or expression; stddev-the standard deviation of a column or an expression

Note: 1. When a column is compared to a grouping function in where, the query resolves the problem by using a subquery.

2. The grouping function can only appear in the query list, the having, the ORDER BY clause, absolutely not in the WHERE clause and the GROUP BY clause;

3. Both columns, expressions, and grouping functions are included in the selection list, and the columns and expressions must appear in the GROUP BY clause, and no child will be faulted;

4. If the SELECT statement contains group BY, have, order by, then their order is group by, have, order by;

5. In addition to count (*), other grouping functions, including count (column name), ignore columns that are listed as null;

6. Function (distinct/all column name)

• Group BY and having

GROUP BY---is used to group statistics on the results of a query;

Grouping methods: You can group one or more columns, and use rollup and cube to generate horizontal and vertical statistical results.

Having clauses---used to restrict the display of grouped results, group filtering-only show groups that meet the criteria;

The 1.having clause must be used with the GROUP BY clause, but the GROUP BY clause can be used on its own.

eg:select .... from table name where condition group By column name 1, column name 2,...having Group filter;

• Connection Query

A multi-table query---a query based on two or more tables or views of two. Descartes set: The condition of the multi-table query is at least the number of tables-1 (to avoid the occurrence of Cartesian connections);

• Multiple queries for different tables

Select a table column 1,...,b table column 1, ... from table A, B table, ... where a. column =b. Column and other conditions, etc. order by column

• A connection query----the same table as the self-connected one.

----approach: Assigning a table to two different aliases, and other queries consistent with multiple tables;

• Collection Query

To merge the results of multiple SELECT statements, you can use the SET Operator Union, UNION all, intersect, minus. But note: The number of columns in different result sets matches the corresponding data type (length can be different); The names of columns of different result sets can be different, with the column name of the first result set, and only one ORDER BY clause;

• Union

This operator is used to obtain a set of two result sets, which, when used, automatically removes duplicate rows from the result set. Select ... from. Union SELECT ... order by column name;

• UNION ALL

This operator is used to obtain a set of two result sets, which, when used, does not remove duplicate rows from the result set and does not sort. Usage is similar.

• Intersect

This operator is used to get the intersection of two result sets. Usage is similar.

• Minus

Using this operator, you get the difference set of two result sets, which only shows the result set that exists in the first collection but does not exist in the second collection. Usage is similar.

• A subquery---refers to a SELECT statement embedded in another SQL statement, also called a nested query.
• Single-column subquery:

---single-line subquery: A subquery statement that returns only one row of data;

① can be placed in the WHERE clause, having statement, from clause of the SELECT statement;

② operators: =, >, <, >=, <=, <>

---multi-row subquery: A subquery that returns multiple rows of data;

① operator: In, not in, EXISTS, not EXISTS, any, and all (the any and all must be used in conjunction with the comparison operator)

②in and exists differences: In is the appearance and the inner table as a hash join, and exists is the external loop, each loop and then query the internal table. If the two table size of the query is equal, then the in and exists are not very different. If one of the two tables is smaller, one is a large table, then the subquery table is large with exists, and the subquery table is small with in;

The difference between ③not in and not exists: If the query statement uses not-in so that the outer surface of the whole table is scanned, no index is used, and the sub-query of not extsts can still use the index on the table. So no matter which table is large, using not exists is faster than not. Try not to use the NOT in clause. Using the minus clause is faster than the NOT in clause, although you use the minus clause to make two queries:

④ using the all operator in multiline subqueries; Eg:sal>all (select Sal ...) -Sal is bigger than everything on the right, and Max can do it.

⑤ using any operator in multiline subqueries: Eg:sal>any (select Sal ...) --There's more to Sal than the right.

• Dolez query:

---refers to a subquery that returns multiple columns of data, and can be compared in pairs or non-paired.

Single line: SELECT * from table name where (column 1, column 2, ...) = (select column 1, column 2, ... from table name);

Multiple lines: in

tips: database execution is from right to left .

• Correlated subquery: A subquery that references some tables or columns in the parent query;
• Scalar subquery: Returns only one single row of data.
• Using subqueries in the FROM clause

Logical---A. Find out the average salary and department number for each department; Table A2

B. Consider the query results from the previous step as a sub-table (inline view); (select ... from ...) A1-- cannot be used as

C. Multi-table query with the above two tables A2,A1

Summary: When using a subquery in the FROM clause, the subquery is treated as a view, and therefore also called an inline view, and when a subquery is used in the FROM clause, the subquery is required to assign an alias (the alias cannot be specified with AS).

• Using subqueries in DDL, DML statements
• Using Subqueries in DDL statements

Use a subquery in the CREATE TABLE statement:

CREATE TABLE table name (column name 1, column name 2,...) as select Column Name 1, column name 2,... from table name where ... ；

Create TABLE table name as SELECT * from table name where ...;

To use a subquery in CREATE VIEW:

Create or Replace view view name as SELECT ...;

• Using subqueries in DML statements

To use subqueries in update:

Update table name set (...) = (select ...) where column name = (select ... from ...);

To use a subquery in delete:

Delete from table name where column name =/in (select ... from ...);

To use subqueries in insert:

Insert all columns: INSERT INTO table name Select .....

Insert Partial column: INSERT into table name (partial column) select ...; Must have primary key column and not NULL column in some columns

4. Create a new database

• Assistant: Creating/deleting/configuring databases using Database Configuration
• Law II: Can be created directly by manual steps

Oracle Learning II: Table Management (data type, create/modify table, add/modify/delete data, data query)