Chapter 15 MySQL Database_PHP Tutorial-PHP Tutorial-php.cn

Learning points:
1. Web database overview
2. MySQL operations
3. MySQL common functions
4. Detailed explanation of SQL statements
5.phpMyadmin

1. Web Database Overview

Now that we are familiar with the basic knowledge of PHP, this is the chapter where we want to leave PHP temporarily and focus on
relational databases to let everyone understand that databases are better than file storage. These advantages include:
1. Relational databases have faster data access than ordinary files.
2. Relational databases make it easier to check and extract data that meets specific conditions.
3. Relational databases have special built-in mechanisms to handle concurrent access. As a programmer, you don’t need to worry about this.
4. Relational databases can provide instant access to data.
5. Relational databases have built-in permission systems.

The concept of relational database
So far, relational database is the most commonly used database type. They have a very good theoretical
basis in relational algebra. When using a relational database, you don't need to understand relational theory (which is a good thing), but you still need to
understand some basic concepts about databases.

1) Tables
Relational databases are composed of relationships, which are often called tables. As the name suggests, a relationship is a
table of data. A spreadsheet is a form.

2) Column
Each column in the table has a unique name and contains different data. Additionally, each column has an associated data type
.
3) Row
Each row in the table represents a customer. Each row has the same format and therefore the same properties. The line also becomes
as a record.
4) Value

Each row consists of a single value corresponding to each column. Each value must be of the same data type as the column's definition.
5) Key
The unique identifier corresponding to each piece of data.
6) Schema
The complete design of the entire set of tables in the database becomes the schema of the database.
7) Relationship
Foreign key identifies the relationship between two table data.
　How to design a Web database
　1) Consider the actual object to be modeled.
　2) Avoid saving redundant data.
3) Use atomic column values (only one data per attribute per row is stored.)
4) Choose meaningful keys.
5) Consider the questions you need to ask the database.
6) Avoid the design of multiple empty attributes

Web database architecture
Communication between browser and web server:

Communication between browser and PHP&MySQL server

1) The user's web browser issues an HTTP request to request a specific web page.
2) The Web server receives the .php request to obtain the file, and passes it to the PHP engine, asking it to process it.
3) The PHP engine starts parsing the script. The script contains a command to connect to the database and a command to execute a query.
PHP opens a connection to the MYSQL database and sends the appropriate query.
4) The MYSQL server receives database queries and processes them. Return the results to the PHP engine.
5) PHP runs the script as you go. Typically, this includes formatting the query results into HTML format. Then
and then output HTML back to the Web server.
6) The web server sends HTML to the browser.

2. MySQL operations

MySQL general commands

1) Display the version number and date of the current database.
　SELECT VERSION(),CURRENT_DATE();
2) Set the field name through the AS keyword.
SELECT VERSION() AS version; //Chinese can be set, through single quotes
3) Return calculation results through SELECT execution
SELECT (20+5)*4;
4) Through multiple lines Implement the user and date of the database
　>SELECT
　>USER()
　>,
　>NOW()
　>;
5) Display the database through one row User and date
　>SELECT USER();SELECT NOW();
6) Cancellation of command
　>c
7) Exit of MySQL window
　>exit;

MySQL common data types

Integer type: TINYINT, SMALLINT, INT, BIGINT
Floating point type: FLOAT, DOUBLE, DECIMAL(M,D)
Character type: CHAR, VARCHAR
Date type: DATETIME, DATE, TIMESTAMP
Remark type: TINYTEXT, TEXT, LONGTEXT

MySQL database operation
1) Display the currently existing database
　>SHOW DATABASES;
2) Select the database you need
　>USE guest;
3) View the current database Selected database
　>SELECT DATABASE();
4) View all contents of a table
　>SELECT * FROM guest; //You can first check how many tables there are by SHOW TABLES;
5) Set Chinese encoding according to the database
　>SET NAMES gbk; //set names utf8;
6) Create a database

　>CREATE DATABASE book;
7) Create a table in the database
　>CREATE TABLE users (
　>username VARCHAR(20), //NOT NULL setting is not allowed to be empty
　>sex CHAR(1),
　>birth DATETIME);
8) Display the structure of the table
　>DESCIRBE users;
9) Insert a piece of data into the table
>INSERT INTO users (username,sex,birth) VALUES ('Lee','x',NOW());
10) Filter the specified data
　> SELECT * FROM users WHERE username = 'Lee ';
11) Modify the specified data
　>UPDATE users SET sex = 'Male' WHERE username='Lee';
12) Delete the specified data
　> DELETE FROM users WHERE username ='Lee';
13) Sort by specified data
　> SELECT * FROM users ORDER BY birth DESC; //Forward order
14) Delete the specified table
　>DROP TABLE users ;
15) Delete the specified database
　>DROP DATABASE book;

3. MySQL common functions

4. Detailed explanation of SQL statements

1. Create a class database school, which contains a class table grade, including number (id), name (name),
email (email), rating (point), and registration date (regdate).
mysql>CREATE DATABASE school; //Create a database
mysql> CREATE TABLE grade (
　 //UNSIGNED means unsigned, TINYINT(2) unsigned integer 0-99, NOT NULL means it cannot be
Empty, AUTO_INCREMENT means that no field has been added starting from 1, and the total number is one
　-> id TINYINT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
　-> name VARCHAR(20) NOT NULL,
-> email VARCHAR(40),
　-> point TINYINT(3) UNSIGNED NOT NULL,
　-> regdate DATETIME NOT NULL,
　-> PRIMARY KEY (id) //Indicates id As the primary key, the id value must be unique and cannot be repeated.
　-> );

2. Add 5-10 new student records to this class table grade
mysql> INSERT INTO grade (name, email, point, regdate) VALUES
('Lee','yc60.com@gmail .com',95,NOW());

3. View the records of all fields of the class, view the records of class id, name, email
mysql> SELECT * FROM grade;

mysql> SELECT id,name,email FROM grade;

4. Students whose names are equal to 'Lee', students whose scores are greater than 90, members whose emails are not empty, members who are between 70-90
mysql> SELECT * FROM grade WHERE name='Lee';
mysql> SELECT * FROM grade WHERE point>90;
mysql> SELECT * FROM grade WHERE email IS NOT NULL; SELECT * FROM grade WHERE point IN (95,82,78);
5. Find students whose emails use 163, students who do not contain the yc60.com string
　mysql> SELECT * FROM grade WHERE email LIKE '% mysql> SELECT * FROM grade ORDER BY regdate DESC;
mysql> SELECT * FROM grade ORDER BY point ASC; SELECT * FROM grade LIMIT 3;
mysql> SELECT * FROM grade LIMIT 2,2; 163.com' WHERE name='Lee';

9. Delete student data numbered 4
mysql> DELETE FROM grade WHERE id=4;

10. Go through the above grouping functions

Omitted.
11. Check the information of this table

mysql> SHOW TABLE STATUS G;

12. Optimize a table mysql> OPTIMIZE TABLE grade;

5. PhpMyAdmin

phpMyAdmin (PMA for short) is written in PHP and can control and operate
MySQL online through the Internet. It is the preferred database maintenance tool for many MySQL administrators and website administrators. The MySQL database can be completely operated through phpMyAdmin

Create database schollCreate a database->Select utf8 character set

Export another database SQL
1. Select another database->Export
2. Select the table to be exported->Select all

3. Select Add DROP TABLE / DROP VIEW (Once the basic table is deleted, the data in the table and the corresponding indexes and views created

will be automatically deleted)
4. Select Save as file
5. Select Execute and save the sql file

Import database
1. Select the database to be imported
2. Select Import and select the sql file

3. Just execute

Rebuild the table

1. Find the table creation statement just output in the sql file.

2. Copy the table creation statement

3. Then select sql, select paste, and execute

Repair the data table

1. Select the table to be repaired

2. Among the selected items, select Repair table, then

Optimize data table
1. Select the table to be optimized

2. Among the selected items, select the optimized table, and then

Modify, delete, insert table records

Execute SQL statement

Note: The article comes from Li Yanhui’s PHP video tutorial. This article is for communication only and may not be used for commercial purposes. Otherwise, you will be responsible for the consequences.

http://www.bkjia.com/PHPjc/759624.html

www.bkjia.comtrue

http: //www.bkjia.com/PHPjc/759624.html