Primary knowledge database (data type)

definition

What is a database: a piece of space in a hard disk that is used to store data collectively;

Why use the database:

1, the database can realize data sharing;

2, reduce data redundancy;

3, the independence of the data;

4, the consistency of data;

5, failure recovery;

Structure Type

Structure type of data:

Hierarchical (tree) data structure, mesh data structure, relational data structure (current) object data structure (possible future trends)

Database provider:

ORACLE/SYBASE/DB2 Large Database

Mysql/sqlserver Small Database

Access Micro Database

Oracle

Oracle Database:

Oracle Company, provides a network-oriented computing, support relational object model, a distributed database product;

Installation of Oracle:

1. View the registration list-call the regedit command, if there is an Oracle registry, delete it;

2, the composition of the database: 1, DB-storage data library; 2, DBMS-database management system, is a set of software, used in database data operation, maintenance, optimization, etc.

Users of the database:

1. DBA-Database administrator

2. Program Developer

Table structure of the database:

Rows, Columns

First line: Table header

Operating Languages

Manipulating the language of the Oracle database-SQL language

Structured Query Language-SQL language

1. Data definition Language-DDL

1.Create Create, drop destroy, alter Modify

2. Data manipulation Language-DML

1.Insert Insert, update modify, delete delete

3. Transaction control Language-TCL

1. Commit/rollback of Things Submitted

4. Data Query Language-DQL

1.Select Query Language

5. Data Control Language-DCL

1.grant give permission 2.revoke revoke permission

Database user

Sys/system/dba

start the SQL Tour

--Unlock for Scott

Alter user Scott account unlock

--Set the password for Scott

Alter user Scott identified by Tiger

--emp Staff Table

--dept Department Table

--salgrade Salary Table

--bouns Bonus Table

SELECT * FROM emp

SELECT * FROM Dept

SELECT * FROM Salgrade

SELECT * FROM bonus

--Grammatical structure

--select clause

--from clause

Select *| field name from table name

--sql compile input is case-insensitive, the compiler automatically becomes lowercase (keywords, fields only)

Select Ename,empno from emp

Data Type

　　--Number Type

--number (N,P)

　　--Character type

--char (n) |varchar (n) Variable length

--char (20) length is not variable

--varchar (20) variable length

　　--Splicing | |

--splicing the name and position of the employee table

Select Ename| | Job from EMP

--use a comma between the name and the post

Select Ename| | ', ' | | Job from EMP--with single quotation marks

　　--Go to heavy distinct

SELECT DISTINCT Deptno from emp

SELECT * FROM Dept

　　--null, you can't take a math operation.

Select ename,sal*12 from emp

Select Ename,sal*12+comm from emp

-- aliases--in double quotes

Select Ename as "name" from EMP

Select Ename "Name" from EMP

　　--Date type

--date

　　--where clause

--Query Smith's information

SELECT * from emp where ename = ' SMITH '

--where clause Application scope

The--where clause follows the FROM clause

--where clauses can write conditional expressions, column names, literals

Aliases cannot be used after the--where clause

　　--logical operators (two conditions produce one result)

--and and

-When two conditions are met, they will be retrieved.

--Query Employee table, department number is 20, salary is more than 1000;

SELECT * from emp where deptno = > Sal 1000

--or or

--as long as there is a satisfaction, it will be retrieved.

--Query Department number 20, or department Number 10 staff

SELECT * from emp where deptno = or Deptno = 10

--not non (secondary keyword is)

--Query employees who do not have an empty commission in the employee table

SELECT * FROM EMP where comm are NOT null

--Multi-criteria selection, multiple criteria can be matched

--In (condition 1, Condition 2 ...)

--Query the employee table, position is manager and staff information

SELECT * from emp where job in (' Clerk ', ' salesman ')

--in ... And...... Between the between and

--Set the matching area (range)

--Check the employee's salary is not between 1000 to 1500

SELECT * from emp where Sal is not between 1100 and 1500

--String matching

--Query employee's name with C's Employee information

SELECT * from emp where ename like '%c% '

　　--Sort

--Grammatical structure

--select ...

--from clause ...

--where clause ...

--order by sort ...

　　--Query The salary ascending order in the Employee table (desc descending)

SELECT *

From EMP

Where Sal between and $ order by deptno Desc

　　--Multi-conditional sorting

--Query the employee table requires departments to sort in ascending order, employee wages in descending order

SELECT *

From EMP

ORDER BY deptno,sal Desc

Primary knowledge database (data type)