FoundPHP Database Foundation

English

### FoundPHP  Database Foundation
Database is an indispensable part of storing website information. If you want to make a website into a dynamic website, you must use a database.

There are many types of databases on the market, including free MySQL, SQL server, and paid Oracle. Here we introduce that MySQL database can be used in many languages, including PERL, C, C++, JAVA and PHP. Among these languages, MySQL is the most widely used in PHP web development.

The MySQL database is a simple and lightweight database. It does not need to be installed as complicated as SQL server. Most of them only need to install the integrated environment of PHP, and MySQL will be installed automatically, such as PHPstudy.

####Database terminology
Before learning MySQL, let's first understand the related terms of the database:
-**Database:** A database is a collection of related tables.
-**Data table: **The table is a matrix of data. A table in a database looks like a simple spreadsheet.
-**Column:** One column (data element) contains the same type of data, such as postal code data.
-** Row: ** A row (= tuple, or record) is a set of related data, such as a piece of data subscribed by a user.
-**Redundancy: **Store twice the data. Redundancy reduces performance but improves data security.
-**Primary Key: **The primary key is unique. A data table can only contain one primary key. You can use the primary key to query data.
-**Foreign key: **Foreign key is used to associate two tables.
-**Composite key: **Composite key (composite key) uses multiple columns as an index key and is generally used for composite indexes.
-**Index: **Use the index to quickly access specific information in the database table. An index is a structure for sorting the values of one or more columns in a database table. Similar to a catalog of books.
-**Referential integrity:** Referential integrity requirements do not allow references to non-existent entities in the relationship. Integrity with entity is the integrity constraint condition that the relational model must satisfy, the purpose is to ensure the consistency of data.

MySQL is a relational database. This so-called "relational" can be understood as the concept of "table". A relational database consists of one or several tables, as shown in the figure:
![](data/files/edit/21/05/18/210518_10149705.png)
-Header: the name of each column;
-Column (col): a collection of data with the same data type;
-Row: Each row is used to describe the specific information of a record;
-Value (value): the specific information of the row, each value must be the same as the data type of the column;
-Key: The value of the key is unique in the current column.

#### Database Operation

- increase
- delete
- update
- inquire

The above 4 operations are commonly known as addition, deletion, modification, and checking.

##### 1. Create/Add
create a database:
```
CREATE DATABASE data storage name;
```
create a database table:
```
CREATE TABLE database table name (column_name column_type);
```
insert data:
```
INSERT INTO database table name
	( field1, field2,...fieldN )
	VALUES
	( value1, value2,...valueN );
```

##### 2. Delete
delete database
```
DROP DATABASE `Database name`;
```
delete the database table:
```
DROP TABLE `database table name`;
```
delete data:
```
DELETE FROM `database table name` WHERE query conditions
```

##### 3. Update
update data:
```
UPDATE `database table name `SET `Field`='Content' WHERE query conditions
```

##### 4. Inquire
update data:
```
SELECT * FROM `Database table name` WHERE query condition ORDER BY sort condition ASC
```