Chapter 2 MySQL database

Learning points:
1. Web Database Overview
2. MySQL operations
3. MySQL common functions
4. SQL statement details
5. phpMyadmin

 

1. Web Database Overview

Now, we are familiar with the basic knowledge of PHP. This is the chapter about leaving PHP for the time being.
Let's look at relational databases to make it easier to understand databases than file storage. These advantages include:
1. Relational databases access data faster than normal files.
2. Relational databases are easier to view and extract data that meets specific conditions.
3. Relational databases have a dedicated built-in mechanism to process concurrent access. As a programmer, you do not need to worry about this.
4. Relational databases can provide immediate access to data.
5. Relational databases have built-in permission systems.

 

Concept of Relational Database
So far, relational databases are the most common database types. They have good theories in relational algebra.
Basic. When using relational databases, you do not need to understand the relational theory (this is a good thing), but you still need
Understand some basic concepts about databases.

1) Table
A relational database is composed of Relational Tables. As the name implies, a link is a data
Table. A Workbook is a type of table.

2) Columns
Each column in the table has a unique name, containing different data. In addition, each column has a related data class.
Type.
3) rows
Each row in the table represents a customer. Each row has the same format and attributes. Success
Is a record.
4) Value

Each row is composed of a single value corresponding to each column. Each value must be of the same type as the data defined in this column.
5) Key
The unique identifier of each data entry.
6) Mode
The full set of database tables is designed as the database mode.
7) relationship
The foreign key identifies the relationship between two table data.
How to Design Web databases
1) consider the actual object to be modeled.
2) Avoid storing redundant data.
3) use the atomic column value (only one data is stored for each attribute of each row .)
4) Select a meaningful key.
5) consider the database issue.
6) avoid designing multiple null attributes

Web database architecture
Communication between browsers and Web servers:

Communication between browsers and PHP & MySQL servers

1) The user's Web browser sends an HTTP request to request a specific Web page.
2) The Web server receives a. php request to obtain the file and transmits it to the PHP engine for processing.
3) the php engine starts parsing scripts. The script contains a database connection command and a query command.
PHP opens the connection to the MYSQL database and sends the appropriate query.
4) the MYSQL server receives and processes database queries. Return the result to the PHP engine.
5) PHP uses where you are going to run the script. Generally, this includes formatting the query result into HTML format. However
And then output HTML to the Web server.
6) The Web server sends HTML to the browser.

 

Ii. MySQL operations

Log on to MySQL
1) Open MySQL Command Line Client
2) enter the root password.

MySQL Common commands

1) displays the version number and date of the current database.
Select version (), CURRENT_DATE ();
2) set the field name using the AS keyword.
Select version () AS version; // you can set Chinese characters using single quotes.
3) return the calculation result through SELECT execution
SELECT (20 + 5) * 4;
4) Implement database users and dates through multiple rows
> SELECT
> USER ()
>,
> NOW ()
>;
5) display database users and dates in one row
> Select user (); select now ();
6) command Cancellation
> \ C
7) Exit the MySQL window
> Exit;

 

Common MySQL Data Types

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

 

MySQL database operations
1) display the existing database
> Show databases;
2) Select the database you need
> USE guest;
3) view the selected Database
> Select database ();
4) view all the contents of a table
> SELECT * FROM guest; // you can use show tables; to check the number of TABLES.
5) set Chinese Encoding Based on 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 cannot be blank
> Sex CHAR (1 ),
> Birth DATETIME );
8) display the table structure
> DESCIRBE users;
9) insert a data entry to the table
> Insert into users (username, sex, birth) VALUES ('lil', 'x', NOW ());
10) filter specified data
> SELECT * FROM users WHERE username = 'lil ';
11) modify the specified data
> UPDATE users SET sex = 'male' WHERE username = 'lil ';
12) Delete the specified data
> Delete from users WHERE username = 'lil ';
13) sort by specified data
> SELECT * FROM users order by birth DESC; // Positive Sequence
14) Delete the specified table
> Drop table users;
15) Delete the specified database
> Drop database book;

 

Iii. MySQL common functions

 

Iv. SQL statement details

1. Create a class database school, which contains a class Table grade, including the number (id), name (name ),
Email, score, and registration date ).
Mysql> create database school; // CREATE a DATABASE
Mysql> create table grade (
// UNSIGNED indicates UNSIGNED, TINYINT (2) UNSIGNED integer 0-99, not null indicates NOT
Null. AUTO_INCREMENT indicates that no field is added from 1, accumulating 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 that id is the primary key and the id value must be unique.
-> );

2. Add 5-10 student records to the grade Table.
Mysql> insert into grade (name, email, point, regdate) VALUES
('Lil', 'y60. com@gmail.com ', 95, NOW ());

3. view the records of all fields in the class, and view the records of class id, name, and email.
Mysql> SELECT * FROM grade;

Mysql> SELECT id, name, email FROM grade;

4. For a student whose name is equal to 'lil', if the score is greater than 90, the email is not an empty member, but a member between 70 and 90.
Mysql> SELECT * FROM grade WHERE name = 'lil ';
Mysql> SELECT * FROM grade WHERE point> 90;
Mysql> SELECT * FROM grade WHERE email is not null;
Mysql> SELECT * FROM grade WHERE point BETWEEN 70 AND 90;
Mysql> SELECT * FROM grade WHERE point IN (, 82, 78 );
5. Search for students who use 163 of emails and do not contain yc60.com strings
Mysql> SELECT * FROM grade WHERE email LIKE '% 163.com ';
Mysql> SELECT * FROM grade WHERE email not like '% yc60.com % ';
6. sort by student registration date in descending order and by score in positive order
Mysql> SELECT * FROM grade order by regdate DESC;
Mysql> SELECT * FROM grade order by point ASC;
7. Only the data of the first three students is displayed, and 2 from 3rd
Mysql> SELECT * FROM grade LIMIT 3;
Mysql> SELECT * FROM grade LIMIT 2, 2;
8. email with the name changed to 'Lee'
Mysql> UPDATE grade SET email = 'yc4. com@163.com 'WHERE name = 'lil ';

9. Delete student data numbered 4
Mysql> delete from grade WHERE id = 4;

 

10. Grouping functions over once
.
11. Check the table information.
Mysql> show table status \ G;
12. Optimize a table
Mysql> optimize table grade;

 

V. PhpMyAdmin

PhpMyAdmin (PMA for short) is written in PHP and can be controlled and operated online over the Internet.
MySQL. He is the preferred database maintenance tool for many MySQL administrators and website administrators.
You can perform operations on the MySQL database.

Create a database scholl
Create a database-> select the 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 Creation
The indexes and views of are automatically deleted)
4. select Save As File
5. Select Run To save the SQL file.

Import Database
1. Select the database to be imported
2. Select Import and select the SQL File

3. Run the command.

 

 

Recreate a table
1. Find the table creation statement output in the SQL file.
2. Copy the table creation statement
3. Select SQL, paste, and execute.

Repair Data Tables
1. Select the table to be repaired
2. Select repair table from the selected items.

Optimize Data Tables
1. Select the table to optimize
2. In the selected item, select the optimize table.

Modify, delete, and insert Table records

Execute SQL statements

 

Note: This article is a video tutorial from Li Yanhui PHP. This article is intended for communication purposes only and cannot be used for commercial purposes. Otherwise, the consequences will be borne by you.