15th MySQL Database _php Tutorial

Learning Essentials:
1.Web Database Overview
Operation of 2.MySQL
3.MySQL Common functions
4.SQL Statement Explanation
5.phpMyadmin

A Web Database Overview

Now that we are familiar with the basics of PHP, this is what we want to leave the PHP chapter for a moment to focus on
A relational database to let you know that the database is a bit more than file storage. These advantages include:
1. Relational databases have faster data access than normal files.
2. Relational databases make it easier to access and extract data that meets specific criteria.
3. Relational databases have a dedicated built-in mechanism to handle concurrent access, and as programmers, there is no need to worry about this.
4. A relational database can provide immediate access to the data.
5. The relational database has a built-in permission system.

The concept of relational databases
So far, relational databases are the most commonly used database types. In terms of relational algebra, they have a very good theory.
Basis. When working with relational databases, there is no need to understand relational theory (which is a good thing), but still need
Understand some of the basic concepts about databases.

1) Form
Relational databases are made up of relationships, which are often referred to as tables. As the name implies, a relationship is a data
Form. A spreadsheet is a table.

2) Column
Each column in the table has a unique name that contains different data. In addition, each column has a related data class
Type.
3) Line
Each row in the table represents a customer. Each row has the same format and therefore has the same properties. The line is also
To record.
4) value

Each row consists of a single value corresponding to each column. Each value must be the same as the data type defined by the column.
5) Key
The unique identity for each data piece.
6) mode
The full design of the database complete table becomes the database schema.
7) Relationship
A foreign key identifies the relationship of two tabular 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 is stored for each property of each row). ）
4) Select a meaningful key.
5) Consider the need to ask the database questions.
6) Avoid the design of multiple empty attributes

Web Database Schema
Communication between the browser and the Web server:

Communication between the browser and the Php&mysql server

1) The user's Web browser makes an HTTP request requesting a specific Web page.
2) The WEB server receives the. PHP request to get the file and upload it to the PHP engine to request it to process.
3) The PHP engine starts parsing the script. The script has a command to connect to the database, and a command to execute a query.
PHP opens the connection to the MySQL database and sends the appropriate query.
4) The MYSQL server receives database queries and processes them. Returns the result to the PHP engine.
5) PHP Where do you go? Complete the script run, which typically includes formatting the query results in HTML format. Then
Then output the HTML back to the Web server.
6) The WEB server sends HTML to the browser.

Two MySQL operation

Log in to MySQL
1) Open MySQL Command line Client
2) Enter the root setup password

MySQL General Command

1) Displays the version number and date of the current database.
SELECT VERSION (), current_date ();
2) Set the field name with the AS keyword.
SELECT version () as VERSION; Can be set in Chinese, by single quotation mark
3) Return calculation results via select
SELECT (20+5);
4) User and date of database through multi-line implementation
>select
>user ()
,
>now ()
>;
5) Display database users and dates by one line
>select USER (); SELECT now ();
6) Cancellation of the order
>\c
7) MySQL window exit
>exit;

MySQL Common data types

Integer type: tinyint,smallint,int,bigint
Float type: float,double,decimal (m,d)
Character Type: Char,varchar
Date Type: Datetime,date,timestamp
Remark Type: Tinytext,text,longtext

MySQL Database Operations
1) Display the currently existing database
>show DATABASES;
2) Select the database you need
>use Guest;
3) View the currently selected database
>select DATABASE ();
4) View all the contents of a single table
>select * from guest; You can go through show TABLES to see how many tables
5) Set the Chinese code 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,//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 the 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 the specified data
> SELECT * from the users ORDER by birth DESC; Positive order
14) Delete the specified table
>drop TABLE users;
15) Delete the specified database
>drop DATABASE Book;

Three MySQL Common functions

Four Detailed SQL statement

1. Create a class database school, which contains a class table grade, which contains the number (ID), name (name),
Mail (email), Rating (point), registration date (regdate).
Mysql>create DATABASE School; Create a database
Mysql> CREATE TABLE Grade (
UNSIGNED is unsigned, TINYINT (2) unsigned integer 0-99,not NULL means that it cannot be
Empty, auto_increment indicates that a field has not been added since 1, accumulating a
-ID TINYINT (2) UNSIGNED not NULL auto_increment,
-Name VARCHAR (a) not NULL,
Email VARCHAR (40),
, point TINYINT (3) UNSIGNED not NULL,
-RegDate DATETIME not NULL,
-PRIMARY key (ID)//ID is the primary key, so the ID value is unique and must not be duplicated.
);

2. Add 5-10 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 the fields in the class and view the records of the class Id,name,email
Mysql> SELECT * from grade;

Mysql> SELECT id,name,email from grade;

4. Students with a name equal to ' Lee ', students with scores greater than 90, messages not empty members, 70-90 members
Mysql> SELECT * from grade WHERE name= ' Lee ';
Mysql> SELECT * from grade WHERE point>90;
Mysql> SELECT * from grade WHERE e-mail is not NULL;
Mysql> SELECT * from grade WHERE point between and 90;
Mysql> SELECT * from grade WHERE point in (95,82,78);
5. Find messages using 163 of learners, not including yc60.com strings
Mysql> SELECT * from grade WHERE email like '%163.com ';
Mysql> SELECT * FROM grade WHERE email isn't like '%yc60.com% ';
6. Sort by the reverse order of the student registration date according to the positive order of the score
Mysql> SELECT * from grade ORDER by RegDate DESC;
Mysql> SELECT * from grade ORDER by point ASC;
7. Show only the first three students, starting from the 3rd data show 2
Mysql> SELECT * from grade LIMIT 3;
Mysql> SELECT * from grade LIMIT 2, 2;
8. Change the e-mail name ' Lee '
Mysql> UPDATE grade SET email= ' yc60.com@163.com ' WHERE name= ' Lee ';

9. Delete student data with number 4
Mysql> DELETE from grade WHERE id=4;

10. Over and over the grouping function
Slightly.
11. Check the information on this table
mysql> SHOW TABLE STATUS \g;
12. Optimize a table
Mysql> OPTIMIZE TABLE grade;

Five PhpMyAdmin

PhpMyAdmin (PMA) is a PHP program that can be controlled and manipulated online via the Internet.
Mysql. He is the preferred Database maintenance tool for many MySQL administrators and Web site administrators through phpMyAdmin
The MySQL database can be fully manipulated.

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 that you want to export, choose All
3. Select Add DROP table/drop VIEW (the base table once deleted, the data in the table and the corresponding build
The indexes and views are automatically deleted)
4. Select Save As File
5. Select Execute, save SQL file

Import Database
1. Select the database to be imported
2. Select Import, select the SQL file

3. Execution can

Rebuilding tables
1. Locate the built-in statement that was just output from the SQL file.
2. Copy the Build Table statement
3. Then select SQL, select Paste, execute

Repair Data Sheet
1. Select the table you want to repair
2. In the selected item, select Repair table to

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

Modify, delete, insert table record

Execute SQL statement

Note: The article is from Eon to PHP video tutorial, this article is limited to exchange use, not for commercial purposes, otherwise the consequences.

http://www.bkjia.com/PHPjc/759624.html www.bkjia.com true http://www.bkjia.com/PHPjc/759624.html techarticle Learning Essentials: 1.Web Database Overview 2.MySQL Operations 3.MySQL Common functions 4.SQL statement in detail 5.phpMyadmin one. Web Database Overview Now that we are familiar with PHP basics ...

