Relational database Basics

Relational database Basics

The 1th chapter about relational database management system

1. Common Sql:mysql (small, free, simple, Oracle Company), SQL Server (Microsoft, charges,. NET, Large), ORACLE,DB2

2. Database Features:

• Reduce the redundancy of storage data;
• higher data uniformity;
• The stored data can be shared;
• can establish the standard that the database follows;
• Easy to maintain data integrity;
• To achieve the security of data;

3. Data storage Model: Hierarchical model, mesh model, relational model, object model

4. The relational model considers the world as being composed of entities and linkages (relationship). The so-called entity refers to an objective and distinguishable thing in the real world, which may be tangible or intangible, concrete or abstract, alive or lifeless.

5, the entity has an attribute called the attribute (Attribute), the entity can be described by a number of attributes.

6. Databases created with relational models are called relational databases (RELATIONL database). A relational database is just a container for storing data, and most data relies on a software called a Database management system (DBMS) to manage data in the database. The software that manages a relational database is called a relational database management system (RDBMS). A database application interacts with a relational database through an RDBMS.

7. Table is the core unit of a relational database, which is where the data is stored.

8. Within the table, the data is divided into columns (column) and rows (row). Each row that is filled into a table represents an entity, which means that each row in the table represents a thing in the real world.

9. Each table contains 0 to more rows. Instead, each column of the row represents an attribute of the entity. Each table consists of one or more columns, which represent the attributes of the entity, which describes the name of the data and also the type of the data. In a table, rows intersect each column.

10. In a relational database table, the primary key (Primary key) is used to uniquely identify each row, and the foreign key (Forergn key) is used to express the association between the table and the table.

11. Relationship between the two tables:

• Single-to-one: a Main table record, corresponding to a record from the table
• One-to-many: a master table record that corresponds to multiple entries from the table
• Many-to-many: a main table record, corresponding to a number of records from the table, at the same time, a record from the table, also corresponds to a number of main table records. There must be an intermediate table between the primary table and the table to record the relationship between the main table and the table.

12. RDBMS is a kind of software program that manages, operates and maintains relational database, which is divided into two types: local and Server database management system.

13. SQL (structured Query Language) is a computer language for managing relational databases and communicating with data in a database. SQL is the standard language for relational database management. Different RDBMS support for SQL standards is different.

14. Several technologies defined by SQL:

• DQL data Query Language: Query for data in the database;
• DML Data Manipulation Language: Used to modify data in the database (add and remove);
• DDL Data Definition Language: Create, modify, delete the internal data structure of the database;
• DCL Data Control Language: Controls database access rights.

15. When defining the columns of the table, add primary key, which indicates that the column is set as primary key. Primary key column features: cannot be duplicated, cannot be null.

16. An index is a structure that sorts the values of one or more columns in a database table, using an index to quickly access specific information in a database table.

CREATE index index name on table name (attribute name);

17.

• Delete all the data without the DELETE statement of the Where condition using delete;
• Use the TRUNCATE statement to directly delete all data in the table, standard structure.
• The difference: Delete Logs the log, meaning that the deleted data can also be recovered, but inefficient; truncate does not log, the deleted data cannot be recovered, but is highly efficient.

18, SELECT DISTINCT birthday from T_cur; go to re-query

Distinct is unique.

19. Aggregation function:

Count Statistics Row Quantity (Ps:count (column name) does not count null values, COUNT (*) statistics NULL)

Sum gets a single column of total value sum (column name)

AVG calculates a single-column average (Ps:avg (IsNull (grade,0) calculates the average score, and if the column is null, it is calculated as 0.

Max gets the maximum value of a column min gets the minimum value of a column

Note: There can be no spaces between the function name and the parentheses.

20.

When you group a query, the fields of the query can only be grouped fields and aggregate functions.

21. SQL Statement Execution Order

From---WHERE----GROUP by---SELECT---have---ORDER by----LIMIT

22. The difference between having and where in SQL

Both are filter conditions where the where runs before grouping and therefore cannot perform any aggregate functions, having a function that is run after grouping and can only be used as a filter for aggregate functions.

23. Overview of Database Integrity

In order to prevent the generation of garbage data, the efficiency of database execution is affected.

Reliability + accuracy = data integrity.

1, Entity integrity;---guarantee that a row of data is valid

2, domain integrity;---ensure that a column of data is valid

3, referential integrity;---guarantee that the reference number is valid. Data that appears from the foreign key in the table must appear in the primary key column of the primary table

4. User-defined integrity;---guarantee custom rules

24, to ensure that each line represents the entity can be distinguished from one another, there can be no two identical records;

A row of data in a table has entity integrity if it is exactly the same as the entity it represents.

Implementation method:

• A PRIMARY KEY constraint (Primary key) is one or more columns in a table, the primary key column cannot be empty, nor can it be duplicated, and there can be only one primary key in a table.
• A unique constraint (unique) means that all values in a given column must be unique, that the value of each row in the table must be unique, that it differs from the primary key constraint in that the column can be empty, and that you can set a unique constraint on more than one column in a single table.
• The default constraint property can set a default value, whichever is the default if the property value is not entered when the row is inserted.

25, foreign keys and foreign KEY constraints:

A foreign key is a dependency between a column in a table and a column in a primary table.

A FOREIGN KEY constraint is a constraint that is imposed on the primary key associated with an external key, and if the constraint is violated, the modification of the data is not allowed. (The value of the foreign key column must refer to the value of the primary key column in the primary table or NULL)

Relational database Basics