01:mysql Introduction

1.1 Database Introduction

1. What is a database?
1. A database is a warehouse that organizes, stores, and manages data in accordance with its structure, each with one or more different
API (interface) for creating, accessing, managing, searching, and copying saved data
2. We can also store the data in a file, but it is relatively slow to read and write data in the file. So, now we're using relational data
Library Management System (RDBMS) to store and manage large volumes of data
3. The so-called relational database is a database based on the relational model, which uses mathematical concepts and methods such as set algebra to process data in the database.
4. The RDBMS is the feature of the relational database management system (relational Management Systems):
1) The data appears in tabular form
2) Each record name of the behavior
3) the data field corresponding to the record name for each column
4) A number of rows and columns form a single sheet
5) A number of forms form database

2. RDBMS Terminology
1. database: The database is a collection of some associated tables.
2. Data sheet: table is the matrix of the data. A table in a database looks like a simple spreadsheet
3. column: a column (data element) contains the same data, such as the postal code.
4. line: a row (= tuple, or record) is a set of related data, such as a user's subscribed data.

5. redundancy: storage twice times the data, redundancy can make the system faster
1) Queries may often require a connection query between multiple tables, while a connection operation can slow down the query
2) The student's information is stored in the student table, the department information is stored in the Department table, if you want to query a student's department name
, you must find the number of the student's department from the student table and then go to department to find the name of the department based on this number.
3) If you often need to do this, the connection query will waste a lot of time, so you can add a redundancy in the student table
Field Dept_name, which is used to store the name of the student's department (so that it does not have to be connected every time)

6. PRIMARY key: The primary key is unique. Only one primary key can be included in a data table. You can use the primary key to query the data
7. FOREIGN key: foreign key is used to correlate two tables (two tables are connected by one field)
8. Composite key: composite key (key combination) multiple columns as an index key, typically used for composite indexes
9. Index: use an index to quickly access specific information in a database table. An index is a structure that sorts the values of one or more columns in a database table. A directory similar to a book.

1) When a field is set to index, all values in this field correspond to their own MD5
2) When querying from a database, you can quickly find this entry by means of a binary algorithm.

referential integrity: Referential integrity requirements are not allowed to reference non-existent entities in a relationship

3. MySQL Database features
1. MySQL is the most popular relational database management system, and MySQL is the best RDBMS for Web applications relational database
Management System: Relational database management System) One of the application software
2. Developed by the Swedish MySQL AB company, currently owned by Oracle Corporation
3. MySQL is an affiliate database management system that stores data in different tables rather than putting all the data in a large
In the warehouse, which increases speed and increases flexibility.
1) MySQL is open source, so you don't have to pay an extra fee
2) MySQL supports a large database. Can handle large databases that have thousands records
3) MySQL uses the standard SQL data language form
4) MySQL is very good support for PHP, PHP is the most popular web development language
5) MySQL support large database, support 50 million records of Data Warehouse, 32-bit system table file maximum support 4gb,64 bit system to support the largest table file 8TB
6) MySQL can be customized, using the GPL protocol, you can modify the source code to develop their own MySQL system

4, primary key, foreign key, index comparison

1. Difference between primary key and foreign key

1. A primary key is a unique identifier that determines a record, for example, a record that includes an identity plus, name, and age.
The ID number is the only person who can identify you, others may have duplicates, so the ID number is the primary key.

2. A foreign key is used to associate with another table. is a field that determines the record of another table, which is used to maintain data consistency.
For example, a field in table A is the primary key of table B, so he can be a foreign key to table A.

2. Difference between primary key and unique index

1. A primary key is a constraint, and a unique index is an index, and the two are essentially different.
2. The primary key must be created with a unique index, and the uniqueness index is not necessarily the primary key.
3. The uniqueness index column allows null values, while primary key columns are not allowed to be null.
4. The primary key can be referenced by another table as a foreign key, and a unique index cannot.
5. A table can create a maximum of one primary key, but multiple unique indexes may be created.
6. Primary keys are more suitable for unique identities that are not easily changed, such as auto-increment columns, Social Security numbers, and so on.

01:mysql Introduction