Orcal Database Fundamentals 1

Database Summary

Language classification	Detailed description
1. Data query Statements	Select
2. Data manipulation statements (DML)	Insert; Update; Delete;
3. Database definition statements (DDL)	Create; Alter; Drop; Rename; Truncate;
4. Object Control Statement (TC)	Commit Rollback SavePoint;
5. Data Control Statement (DCL)	Grant Revoke

Data: Is the basic object of the database, is to describe the error symbol record.

Database: Storage Management data, presentation of the warehouse built on the computer

The concept of a database began in 1962.

Database storage configuration, test plans, test cases, test scenarios.

The build of the test environment needs to be backed up to the database.

relational database: 1. Oracle,2.db2,3.sql server,4.mysql,5.access,6.mariadb

The Redis storage locates data, is the network database, the open source uses the ANSI C language.

Mysql

SQL Server

Qracle Features: Multi-platform, highest performance, highest security certification.

Version of Qracle: 1.8i (i:internet)

2.9i

3.10g (G:grid)

4.11g

5.12C (C:cloud)

Files in the Oracle root directory: db_1: Software that primarily hosts Oracle systems, including executables, network configuration files

Admin: Main store trace files generated during database running

Bdump: Eligibility Sub-directory DB for saving background process trace files

Cdump: A crash is a kernel transfer file that the operating system process uses to write

Oradata: Physical file that holds the database (error, configuration file)

Flash_recovery_area: Database starts automatic backup file, flash back log file

Create:

Tnsnames.ora ORCL123 refers to the name of the service that needs to be entered.

Server-side settings

Listener.ora: Configuration file for Server listener process

Sqlnet.ora:

Chapter One SQL writing specification

A clear specification

All table names, field names in the 1.sql statement are all lowercase, system reserved words, built-in function names, and SQL reserved words capitalized.

2. Add a space before and after the connector.

3. For more complex SQL statements

Other precautions

The casing of the 1.SQL command is insensitive.

The 2.SQL command can be written as one or more lines

Chapter II Foundation of SQL language

SQL language Classification

1. Data query statement: SELECT

2. Data manipulation statements (Dml:data manipulation): Insert,update,delete

Basic SELECT command

1.select: The column or expression to return

2.from: Table from which data is retrieved

3.where: Row-level filtering

4.group by: Group description

5.having: Ancestor Filter

6.order by: Output sort order

7.limit: Number of rows to retrieve

SQL statement Rules

1.SQL statements are case insensitive

2.SQL statements

An arithmetic expression

First multiplication and then add minus, parentheses

Define NULL values

A null value is a value that is not available, not known, and not applicable.

Null value is not equal to zero or space

An algorithm expression that includes a null value is equal to NULL.

Defining Aliases for columns

1. Change the header header of a column

2. Using calculation results

3. Alias of the column (can use as)

4. If special characters are used,

Connection operation

1. Connect a column or character to another column

Literal string

A literal string is a character, expression, or number that is included in the select list.

Date and character literal characters must be enclosed in single quotation marks.

Each return character is output once.

Construct command

Re-record

By default, the query displays

Chapter III Qualifying and sorting data

1. Restricting data for a query

Limit the records you select

Use the WHERE clause to qualify returned records

WHERE clause after from

String and date

1. String and date are enclosed in single quotation marks

2. Strings are case-sensitive and date values are format-sensitive

3. The default name is Day-m-y

Comparison operators

Greater than less than

Other comparison operators

Between ... And ... : Between two values

In (list): Match listed values

Like: matches a character pattern

Is null: null value

Mgr in

logical operators

And,or,not

<>: Not equal to

Priority rules

The parentheses will span all precedence rules:

1. All comparison operations *, +-

2.NOT

3.AND

4.OR

ORDER BY: Sort

ASC: Ascending

Desc: Descending

How to connect to a database: 1.sqlplus

2. Sign in with Sqlplus and enter user and password in client login

3.isqlplus sign-in on the web

Problems encountered:

During the installation process, the Scott user was not unlocked.

Solution:

1. Input Sqlplus sys as SYSDBA

2. Input Sqlplus Sys/tiger as Sysdba/[email protected]

Fourth Chapter One group function

1. Describe the various functions that can be used in SQL

2. Characters, numbers, date functions can be used in SELECT statements

3. Describe the use of conversion functions

SQL functions

Two kinds of SQL functions

Single-line function

1. Manipulating Data Items

2. Accept the parameter and return a value

3. Works on each return line

4. Each row returns a result

5. Modifiable data types

6. You can use nested

Function_name (column|expression,[arg1,arg2,...])

Character functions

Description of the character function

The x in replace must be a string.

Use case Conversion

Show employee number, name, department number:

Sql>select empno, Ename,deptno

2 from EMP

3 WHERE ename = ' Blake ';

Sql>select empno, Ename,deptno

2 from EMP

3 WHERE LOWER (ename) = ' Blake ';

EMPNO ename DEPTNO

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

7698 BLAKE 30

Using character handler functions

Sql>select Ename,concat (Ename,job), LENGTH (ename),

2 INSTR (ename, ' A ')

3 from EMP

4 WHERE SUBSTR (job,1,5) = ' SALES ';

Ename CONCAT (ename,job) LENGTH (ename) INSTR (ename, ' A ')

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

MARTIN Martinsalesman 6

numeric functions

Using the ROUND function

Using the Trung function

Using functions

1.Oracle with internal numbers stored date: century, year, month, day, hour, minute, second.

2. The default date format is Dd-mon-yy.

3.SYSDATE is a function that returns a date and time.

4.DUAL is the virtual table used to view the sysdate.

Using Date arithmetic

Date function

Conversion functions

Implicit data-type conversions

Clear Data type conversions

To_char function Operation Date

To_char (date, ' FMT ')

Formatting rules

1. Must be enclosed in single quotation marks and case sensitive.

2. Can contain any valid date format.

3. There is an FM element that is used to fill the blanks or prohibit the preceding 0.

4. Use both to separate date values.

Date format Basics

Use the TO_CHAR function to process the date--ff

To_char function handles numbers

To_number and To_date functions

1. Use the To_number function to convert characters to numbers:

To_number (char)

2. Use the To_date function to convert characters to dates:

To_date (char[, ' FMT ')

Prohibit conversions:

1. Decimal and hexadecimal:

Sal>select to_char (2463, ' xxxx ') fromdual;

To_ch

-----

99f

1 rows have been selected.

Hexadecimal and decimal

Sql>select to_number (' 99f ', ' xxxx ') from dual;

To_number (' 99F ', ' XXXX ')

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

2463
1 rows have been selected.

NVL function

To convert a null value to the actual value:

1. The data format can be date, character, number.

2. The data type must match.

NVL (comm,0)

NVL (hiredate, ' 01-jan-97 ')????

NVL (Job, ' NO job yet ')

Using the NVL function

NVL2 function

NVL2 (EXPR1,EXPR2,EXPR3)

If EXPR1 is not NULL, returns EXPR2, if EXPR1 is null, returns EXPR3.

Expr1 can be of any data type.

Decode function

Using the Decode function

Nested functions

Examples of nested functions

Comparison of SQL directives and Isql*plus commands

Isqlplus

Edit commands for Sql*plus

1.I[NPUT]: Input SQL statement

2.i[nput]text

3.l[ist]n

4.l[ist]m N

5.r[un]: Execute SQL statement

6.n

7.N text

8.0 text

The fifth chapter shows data from multiple tables

1. Use equal and unequal links to write SELECT statements to access multiple tables

2. Use an outer join to query the data.

3. Self-connection of the table

Fetching data from multiple tables

What is a connection

Descartes Results

1. Descartes results are formed in:

1. The connection condition is omitted

2. Invalid connection condition

3. All records of the first table are connected to all records in the second table

2. To avoid Cartesian results we always use a valid connection in the WHERE clause.

Generate Cartesian results

Descartes Example

Types of connections

What is an equivalent connection

Data records obtained using an equivalent connection

Qualifying column names

1. Use the table name as a prefix to specify column names in multiple tables.

2. Use table prefixes to improve performance.

3. Use the alias of the column to distinguish between columns of the same name in different tables.

Multi-table Connection

Non-equivalent connection

Get a value record using a non-equivalent connection

Self-joins of tables

External connection

Using external links

Class Practice

Sixth chapter using Group functions

What is a grouping function

Types of grouping functions

1.AVG ([distinct| All]n): Averaging

2.SUM ([distinct| All]n): Sum

3.COUNT ({*|[ Distinct| ALL]EXPR): Count

4.MAX ([distinct| ALL]EXPR): Ask for maximum value

5.MIN ([distinct| ALL]EXPR): Minimum value

6.STDDEV ([distinct| ALL]X): Standard deviation

7.VARIANCE ([distinct| ALL]X): Variance

Using the AVG and SUM functions

Using the Min and Max functions

Using the Count function

grouping functions and Null values

Using the NVL function in a grouping function

Generating data sets

GROUP BY clause

Using the GROUP BY clause

Multi-column Grouping

Using the GROUP BY clause on multiple columns

Illegal query using the GROUPING function--e

Illegal statements when using grouping functions

HAVING clause

Nested group functions

Summary

Seventh Zhang Ziyi (query

Using subqueries to resolve issues

Sub-query

Working with sub-queries

Rules that use sub-queries

1. Sub-queries are enclosed in parentheses

2. Only the query is placed on the right side of the comparison operator

3. Do not add an ORDER BY clause in a subquery

4. Use single-line operators for single-line subqueries

5. Use the multiline operator for multiple rows of subqueries

Types of subqueries

Single-line subquery

Using grouping functions in subqueries

Using the having statement in a subquery

The wrong subquery

Is such a subquery valid?

Multi-row subqueries

Using the any operator in multiline subqueries

Orcal Database Fundamentals 1