01-Database Overview

1. Basic concepts of the database

(1). Database (DATABASE:DB): A database is a warehouse that organizes, stores, and manages data in accordance with its data structure. ----> warehouse where data is stored and managed.

(2). Database Management System:dbms: A computer system software dedicated to managing databases. The database management system can provide the database with the functions of defining, establishing, maintaining, querying and statistics, and accomplishing the functions of controlling the data integrity and security.

(3). MIS: Management Information System.

Note: We generally refer to the database, which refers to the DBMS: the database server.

2. Database technology Development process

Stage One: Hierarchical database and Mesh database technology phase.

Use pointers to represent the connection between data.

Phase two: relational database technology phase.

The classic milestone stage, represented by the DBMS is: Oracle, DB2, MySQL, SQL Server, Sybase, and so on.

Phase three: Post-relational database technology stage.

Because of the shortcomings of data model, performance and scalability in relational database, there are:
Ordbms: Object-oriented database technology.
NoSQL: Structured database technology.

with the development of big data, non-relational databases are now an extremely hot new field, the development of non-relational database products is very rapid, excellent NoSQL database is divided into 4 categories:

(1). Key-Value Storage database: Oracle BDB,Redis,beansdb

(2). Column Storage database:HBase,Cassandra,riak

(3). Document-based database:MongoDB,CouchDB

(4). Graphic database: neo4j, Infogrid, Infinite graph

3. Common relational database

database system owned company
--------------------------------------
Oracle Oracle
DB2 IBM
SQL Server Microsoft
MySQL ab-->sun-->oracle
---------------------------------------------------------------------
Oracle: Stable operation, high portability, full-featured, superior performance. Suitable for large enterprise areas.
DB2: High speed, good reliability, suitable for massive data, recovery is very strong. Suitable for medium to large enterprise areas.
SQL Server: Comprehensive, efficient, user-friendly, easy to operate, but not cross-platform. Suitable for small to medium business areas.
MySQL: Open source, small size, fast speed. Applicable to small and medium-sized enterprise fields.

4. Structured Query Language (structured query Language, or SQL)

SQL is a relational database standard language, its characteristics: simple, flexible, powerful.
----------------------------------------------------

SQL consists of 6 parts:

One: Data Query Language (DQL):

Its statement, also known as a "data retrieval statement," is used to obtain data from a table to determine how the data is given in the application. Reserved word select is the most used verb for dql (and all SQL), and the other dql commonly used reserved words are where,order by,group by and having. These dql reserved words are often used in conjunction with other types of SQL statements.

Two: Data manipulation language (DML):

Its statements include verb insert,update and delete. They are used to add, modify, and delete rows in a table, respectively. Also known as the action query language.

Three: Transaction processing language (TPL):

Its statement ensures that all rows of the table affected by the DML statement are updated in a timely manner. The TPL statement includes begin TRANSACTION,COMMIT and rollback.

Four: Data Control Language (DCL):

Its statements are licensed through grant or revoke to determine access to database objects for individual users and groups of users. Some RDBMS can use grant or revoke to control access to individual columns of a table.

Five: Data definition language (DDL):

Its statements include verb create and drop. Create a new table or delete a table (creat table or drop tables) in the database, add an index to the table, and so on. The DDL includes many reserved words related to the data obtained in the Human database directory. It is also part of the action query.

Six: Pointer control Language (CCL):

Its statements, like declare cursor,fetch into and update WHERE current, are used to operate on separate rows for one or more tables.

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

Writing rules:

1. SQL statement is not case sensitive in MySQL database

2.SQL statements can be written single-line or multiline

3. In SQL statements, keywords cannot span multiple lines or abbreviations

4. In order to improve readability, general keywords uppercase, other lowercase

5. Spaces and indents make the program easy to read

5. Relational Table Concepts

(1). What is a table?

We say that MySQL is a relational database. The most important concept of a relational database is the table. A table has a fixed number of columns and any number of rows, which are mathematically referred to as "relationships."

A: A two-dimensional table is a collection of properties of homogeneous entities, each corresponding to a row in a table, called a tuple in a relationship, equivalent to a common record;

B: A column in a table represents a property, called field, that is equivalent to a data item in a typical record, also called a column, field.

(2). case

01-Database Overview