Summary of basic database knowledge

Reprint Please specify : Http://blog.csdn.net/uniquewonderq

1. Database principles

1.1 Database Introduction

1.1.1 Managing the file management database manually

File storage Methods The disadvantage of saving data:

1> lack of overall data management, data inconvenience to modify

2> is not conducive to data analysis and sharing

3> data volumes are growing rapidly, and large amounts of data cannot be stored in files for long periods

1.1.2.DB and DBMS

Databases (database, or DB) are warehouses that organize, store, and manage data in accordance with a data structure.

Database Management system, Jian Cheng DBMS: Software for managing databases

Introduction to 1.1.3 Relational database

Relationship: Describes an association or correspondence between two elements

Using a relational model to organize data into a two-dimensional data table (table)

Product: Oracle DB2Sybase SQL Server MYSQL

The concept of the 1.1.4 table

1> A relational database consists of multiple data tables (table), which is the basic storage structure of a relational database

2> represents two-dimensional, consisting of rows and columns

3> table Rows (row) are horizontal data, also known as Records

The column of the 4> table is the head row data, also called the field

5> correlation between tables and tables

1.2 Mainstream relational database

1.2.1Oracle Database Overview

Oracle is a well-known Oracle (Oracle) company's database product

Oracle is the world's first commercially-commercialized relational database management system

Oracle uses standard SQL (Structured Query language), supports multiple data types, provides object-oriented data support, has a fourth-generation language development tool, supports multiple platforms such as UNIX,WINDOWS,OS/2,

Oracle's products are rich in Oracle servers, Oracle development tools and Oracle Applications. The most notable of these is the Oracle database, currently the latest version of Oracle 12c

1.2.2DB2 Database Overview

DB2 is IBM's relational database management system

The DB2 is available in a number of different versions and can be run on a terminal machine from handheld products to mainframes

DB2 Universal Database Personal Edition and DB2 Universal database Workgroup Edition are single-user and multi-user systems that can run on OS/2 and Windows

DB2 is Oracle's main competitor

1.2.3 Sybase Database Overview

Sybase is a relational database system for Sybase, USA

Sybase is a database vendor that uses C/S technology earlier

Large database system on a typical UNIX or Windows NT platform in a client/server environment

Sybase is typically used with Sybase SQL anywhere for a client/server environment, the former as a server database, the latter as a client database, using the PowerBuilder developed by the company as a development tool, in the domestic large and medium-sized systems have a wide range of applications.

acquired by SAP in 2010

1.2.4SQL Server Database Overview

Mircrosoft SQL Server is a Microsoft product that runs on a Windows NT Server

The initial version of Mircrosoft SQL Server is suitable for small and medium sized enterprises, but the scope of application has been expanded to reach large, multinational enterprise database management.

1.2.4 MySQL Database

MySQL is an open source small relational database management system, widely used in small and medium-sized web sites

Low total cost of ownership, smaller than Oracle and DB2

January 16, 2008, Sun acquired mysql,2009 April 20, Sun was acquired by Oracle, so MySQL is now owned by Oracle Corporation

1.3 Structured Query Language

1.3.1 Structured Query Language

SQL (structured query Language) Structured Query language.

SQL is the standard language used to perform data operations, retrieval, and maintenance on relational databases, which can be used to query data, manipulate data, define data, and control data.

All databases are in the same or similar language

SQL can be divided into: DDL,DML,TCL,DQL,DCL, the following in turn to explain

1.3.2 Data Definition language (DDL)

Data Definition Language

Used to create, modify, and delete database objects

Common include:

Create: Creating a structure for a table or other object

Alter: Modify the knot of a table or other object

Drop: Delete the structure of a table or other object

TRUNCATE: Deleting table data, preserving table structure

1.3.3 Data Manipulation Language (DML)

Data Manipulation Language

Used to change data in a data table

and transaction related, after the execution of the transaction control statement after the submission of the real change in the database to apply

Commonly used include:

Insert: Inserting data into a data table

Update: Updating data that already exists in a database table

Delete: Deleting data from a data table

1.3.4 Transaction Control Language (TCL)

Transaction Control Language

Requirements for maintaining data consistency

Commonly used include:

Commit: Commit, confirm the data changes that have been made

SavePoint: The origin point so that the current transaction can be rolled back to the specified savepoint, making it easy to undo some changes

1.3.5 Data Query Language (DQL)

Data Query Language

Used to query the required data

SELECT statement

1.3.6 Data Control Language (DCL)

Data Control Language

Grant and retract operations for performing permissions

Commonly used include:

Grant: Granting permissions to a role granted to a user

Remove: Revoke the user's permissions

Summary of basic database knowledge