Learn MySQL (UP)

Concrete Example 1. PHP Server Components

For beginners, it is recommended to use the integrated server component, which already includes PHP, Apache, Mysql and other services, eliminating the time spent by developers in the tedious configuration environment process.

The Window system can use wampserver,:http://www.wampserver.com/, support 32-bit and 64-bit systems, and select versions according to its own system.

The XAMPP supports MAC OS and Window systems: https://www.apachefriends.org/zh_cn/index.html.

2. Set the MySQL password

WAMP (wampserver) installed, first through the WAMP into the MySQL console, mysql default password is empty, prompt for password, direct enter. Using the use MySQL command, enter the MySQL database and enter the command statement to change the password: Update user set authentication_string = password (' New password '), Password_ expired = ' N ', password_last_changed = Now () where user = ' root ';

Then enter the command: flush privileges;, otherwise the operation to change the password will not take effect, the last input command: exit;

To this MySQL password has been set successfully, it should be noted that the above command in the semicolon cannot be omitted.

3. User settings

If you need to add a MySQL user, you only need to add new users to the user table in the MySQL database. and grant the user the appropriate permissions.

mysql> use MySQL;
Database changed


        
        
        
        PASSWORD (' New PASSWORD '), ' y ', ' y ', ' y ');
Query OK, 1 row affected (0.20 sec)

mysql> FLUSH privileges;
Query OK, 1 row affected (0.01 sec)

The user rights list is as follows: 1, Select_priv 2, Insert_priv 3, Update_priv 4, Delete_priv 5, Create_priv 6, Drop_priv 7, Reload_priv 8, Shutdown_ Priv 9, Process_priv 10, File_priv 11, Grant_priv 12, Eferences_priv 13, Index_priv 14, Alter_priv. Specify permissions for the user, in the corresponding permission column, set to ' Y ' in the INSERT statement.

4. Basic commands for managing MySQL

1. SHOW DATABASES: Lists the database list of MySQL database management system.

2. Use database name: Select the MySQL database to operate, and all MySQL commands are only for that database after using this command. corresponding to the use of PHP script to select MySQL database syntax:

mysqli_select_db (Connection,dbname);

Connection: Required. Specifies the MySQL connection to use (Mysqli_connect (Host,username,password,dbname,port,socket)). DBName: Required, specifies the default database to use.

3. Show TABLES: Displays all the tables for the specified database, using the use command to select the database to operate before the command is used.

4. Show Table STATUS from DATABASENAME: Displays information for all tables in database DATABASENAME

5. Show COLUMNS from datasheet: Displays data table properties, property types, primary key information, whether null, default value, and other information.

6. Show index from data table: Displays detailed index information for the data table, including primary key (primary key).

5.mysql PHP Syntax

PHP provides a variety of ways to access and manipulate MySQL database records. The PHP mysqli function is formatted as follows:

Mysqli_function (Value,value,...);

The syntax for connecting MySQL using a PHP script is as follows:

Mysqli_connect (Host,username,password,dbname,port,socket);

Host: Optional. Specifies the host name or IP address. Username: Optional. Specifies the MySQL user name. Password: Optional. Specify the MySQL password. dbname: Optional. Specifies the database that is used by default. PORT: Optional. Specifies the port number to attempt to connect to the MySQL server. Socket: Optional. Specify the socket or named pipe to be used.

And you can use PHP's Mysqli_close () function to break the link to the MySQL database. The function has only one parameter for the Mysqli_connect () function to create the MySQL connection identifier returned after a successful connection.

6. Operation of the MySQL database

PHP uses the Mysqli_query function to create or delete MySQL databases. The function has two parameters, returns TRUE on successful execution, or FALSE. The syntax format is as follows:

Mysqli_query (Connection,query,resultmode);

Connection: Required. Specifies the MySQL connection to use. Query: Required, specify the inquiry string, the value inserted here is: ' CREATE DATABASE DATABASENAME '. Resultmode: Optional. A constant. Can be any of the following values: Mysqli_use_result (Use this if you need to retrieve a large amount of data) Mysqli_store_result (default)

To create a database using the MySQL command:

Create database DatabaseName;

To delete a database using the MySQL command:

Drop database databasename;

7.MySQL Data types

MySQL supports multiple types and can be broadly divided into three categories: numeric, date/time, and string (character) types.

1. Numeric type: MySQL supports all standard SQL numeric data types. These types include strict numeric data types (INTEGER, SMALLINT, Decimal, and numeric), as well as approximate numeric data types (FLOAT, real, and double PRECISION). As an extension of the SQL standard, MySQL also supports integer types tinyint, Mediumint, and bigint.

2. Date and Time type: the date and time type that represents the time value is datetime, date, TIMESTAMP, hour, and year. Each time type has a valid value range and a value of "0", and a value of "0" is used when specifying an illegal MySQL value that cannot be represented.

3. String type: String type refers to Char, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, enum, and set.

8. Working with MySQL data sheets

1, through the mysql> Command window can be very simple to create a MySQL data table. Even if you create a table with an SQL statement:

mysql> CREATE TABLE table_name (
   -table_id INT not NULL auto_increment,
   -Table_title VARCHAR (+) not NULL,
   -Table_author VARCHAR (+) not NULL,
   -Submission_date Date,
   PRIMARY KEY (table_id)
   ) Engine=innodb DEFAULT Charset=utf8;
Query OK, 0 rows affected (0.16 sec)
Mysql>

Example analysis: 1. If you do not want the field to be null, you can set the property to NOT NULL, and you will get an error if the data entered in the field is null when you manipulate the database. 2. The PRIMARY key keyword is used to define the column as the primary key. You can use multiple columns to define a primary key, and the columns are separated by commas. 3, auto_increment definition as the self-increment of the property, generally used for the primary key, the value will automatically add 1. 4, engine set up the storage engine, CHARSET set the encoding.

2. Use PHP's Mysqli_query () function to create a data table for the existing database. The function has two parameters, returns TRUE on successful execution, or FALSE.

Mysqli_query (Connection,query,resultmode);

Connection: Required. Specifies the MySQL connection to use. Query: Required, specifies the string of queries, where the value inserted here is the details of the created data table. Resultmode: Optional. A constant. Can be any of the following values: Mysqli_use_result (Use this if you need to retrieve a large amount of data) Mysqli_store_result (default), detailed code is shown below:

&lt?php
$dbhost = ' localhost:3306 ';  MySQL Server host address
$dbuser = ' root ';            MySQL User name
$dbpass = ' 123456 ';          MySQL User name password
$conn = Mysqli_connect ($dbhost, $dbuser, $dbpass);
if (! $conn)
{
    Die (' Connection failed: '. Mysqli_error ($conn));
}
Echo ' Connection succeeded
‘;
$sql = "CREATE TABLE runoob_tbl (".
        "runoob_id INT not NULL auto_increment,".
        "Runoob_title VARCHAR (+) not NULL,".
        "Runoob_author VARCHAR (+) not NULL,".
        "Submission_date date,".
        "PRIMARY KEY (runoob_id)) Engine=innodb DEFAULT Charset=utf8;";
mysqli_select_db ($conn, ' Runoob ');
$retval = Mysqli_query ($conn, $sql);
if (! $retval)
{
    Die (' Data table creation failed: '. Mysqli_error ($conn));
}
echo "Data sheet creation succeeded \ n";
Mysqli_close ($conn);
? &gt

3. Delete the data table in the Mysql> Command Prompt window The SQL statement is drop table:

mysql&gt use DatabaseName;
Database changed
Mysql> DROP TABLE TableName
Query OK, 0 rows affected (0.8 sec)
Mysql&gt

4. PHP uses the Mysqli_query function to delete MySQL data tables. The function has two parameters, returns TRUE on successful execution, or FALSE.

Mysqli_query (Connection,query,resultmode);

Connection: Required. Specifies the MySQL connection to use. Query: Required, specifies the inquiry string, where the value inserted is: "DROP TABLE runoob_tbl";. Resultmode: Optional. A constant. Can be any of the following values: Mysqli_use_result (Use this if you need to retrieve a large amount of data) Mysqli_store_result (default), detailed code refer to 2nd in this project.

9. MySQL Insert Data

1. Insert the data into the MySQL data table blog_table-test using the SQL INSERT INTO statement:

mysql> use Dababasename;
Database changed
Mysql> INSERT into TableName
     (Table_title, Table_author, Submission_date)
    VALUES
    ("Learning closure", "Anani", now ());
Query OK, 1 rows affected, 1 warnings (0.01 sec)
Mysql>

2. Use the PHP mysqli_query () function to execute the SQL INSERT INTO command to insert the data. The function has two parameters, returns TRUE on successful execution, or FALSE.

Mysqli_query (Connection,query,resultmode);

Connection: Required. Specifies the MySQL connection to use. Query: Required, specifies the queried string, and the value inserted here is the detailed code that adds the data. Resultmode: Optional. A constant. Can be any of the following values: Mysqli_use_result (Use this if you need to retrieve a large amount of data) Mysqli_store_result (default). Detailed code:

&lt?php
$dbhost = ' localhost:3306 ';  MySQL Server host address
$dbuser = ' root ';            MySQL User name
$dbpass = ' new password ';          MySQL User name password
$conn = Mysqli_connect ($dbhost, $dbuser, $dbpass);
if (! $conn)
{
  Die (' Connection failed: '. Mysqli_error ($conn));
}
Echo ' Connection succeeded
‘;
Set the encoding to prevent Chinese garbled characters
Mysqli_query ($conn, "Set names UTF8");

$runoob _title = ' learning closue ';
$runoob _author = ' Anani ';
$submission _date = ' 2017-11-28 ';

$sql = "INSERT into Blog_table_test".
        "(Table_title,table_author, Submission_date)".
        "VALUES".
        "(' $runoob _title ', ' $runoob _author ', ' $submission _date ')";

mysqli_select_db ($conn, ' blog ');
$retval = Mysqli_query ($conn, $sql);
if (! $retval)
{
  Die (' Unable to insert data: '. Mysqli_error ($conn));
}
echo "Data insertion succeeded \ n";
Mysqli_close ($conn);
? &gt

10. MySQL Query data

1, in the MySQL database query data common SELECT syntax:

SELECT Column_name,column_name
From table_name
[WHERE Clause]
[LIMIT N] [OFFSET M]

Note: 1, the SELECT command can read one or more records. 2. In the query statement you can use one or more tables, separate the tables with commas (,), and use the where statement to set the query criteria. 3. Use the WHERE statement to include any conditions. 4. Use the LIMIT property to set the number of records returned. 5. Specify the data offset for the SELECT statement start query by offset. By default, the offset is 0. 6. Use the asterisk (*) instead of the other fields, and the SELECT statement returns all the field data for the table. For example, use the SELECT * Form tablename statement to return all records for the table.

2. Use the PHP function's Mysqli_query () and SQL SELECT command to get the data.

The function is used to execute the SQL command, and then the PHP function Mysqli_fetch_array () to use or output data for all queries. The Mysqli_fetch_array () function takes a row from the result set as an associative array (at this point the second argument to the function should be: MYSQL_ASSOC or corresponding function: Mysqli_fetch_assoc ()), or a numeric array (at which point the second argument to the function should be: mysql_num), or both of which return an array based on the rows obtained from the result set, and False if there are no more rows.

After we have finished executing the SELECT statement, releasing the cursor memory can be done by using PHP function Mysqli_free_result () to release the memory. The Mysqli_query () function whose parameters are performed on the query data.

11. MySQL WHERE Clause

1. The WHERE clause can be added to the SELECT statement to conditionally pick data from the table, common comparators are used in the WHERE clause, and one or more conditions can be specified with and OR OR. Of course, the WHERE clause can also be applied to the SQL DELETE or UPDATE command.

For example, in the SQL SELECT statement, use the WHERE clause to read the data in MySQL data table blog_table_test and set the condition to return all records in the table with the Table_author field value Anani:

SELECT * from Blog_table_test WHERE table_author= ' Anani ';

The string comparison of the WHERE clause of MySQL is case insensitive. You can use the BINARY keyword to set the string comparison of WHERE clauses to be case-sensitive. When used, place the keyword in a where and then add a condition.

2. Use the PHP function's Mysqli_query () and the same SQL SELECT command with the WHERE clause to get the data. The function is used to execute the SQL command, and then the PHP function Mysqli_fetch_array () to output the data for all queries.

12. MySQL UPDATE Query

If we need to modify or update the data in MySQL, we can use the SQL UPDATE command to manipulate it. And: 1, you can update one or more fields at the same time. 2. You can specify any condition in the WHERE clause. 3. You can specify any condition in the WHERE clause.

1. The following SQL Update command uses the WHERE clause to update the data specified in the Blog_table_test table:

mysql> UPDATE blog_table_test SET table_title= ' study HTML DOM ' WHERE table_id=1;
Query OK, 1 rows affected (0.01 sec)

2. PHP uses the function mysqli_query () to execute SQL statements, which can be used in SQL UPDATE statements or not using the WHERE clause. Do not use the WHERE clause to update all data in the data table, so be cautious. The function is the same as executing the SQL statement at the mysql> command prompt.

13. MySQL DELETE Statement

In MySQL, you can use the delete from command of SQL to delete records from a MySQL data table.

1. Use the WHERE clause in the SQL Delete command to delete the data selected by MySQL data table Blog_table_test. The following instance deletes the records in the Blog_table_test table that are table_id 1:

mysql> use blog;
Database changed
Mysql> DELETE from Blog_table_test WHERE table_id=1;
Query OK, 1 row affected (0.23 sec)

2. PHP uses the Mysqli_query () function to execute SQL statements, and you can use or not use the WHERE clause in the SQL DELETE command. The function is the same as executing the SQL command with the mysql> command.

14. MySQL LIKE clause

The percent% character is used in the SQL like clause to denote any character, similar to the asterisk * in UNIX or regular expressions. If percent% is not used, the LIKE clause has the same effect as equals sign =.

1. The following will be used in the SQL SELECT command where ... The LIKE clause to get all the records in the Table_title field that end with closure in the Blog_table_test table:

mysql> use blog;
Database changed
Mysql> SELECT * from Blog_table_test  WHERE table_title like '%closure ';

2, using PHP function Mysqli_query () and the same SQL SELECT with where ... The command of the LIKE clause to fetch the data, and then the PHP function Mysqli_fetch_assoc () to output the data for all queries.

15. MySQL UNION operator

The MySQL UNION operator is used to concatenate the results of more than two SELECT statements into a result set. Multiple SELECT statements remove duplicate data. The operator has two optional parameters: 1. Delete duplicate data in the result set. By default, the UNION operator has deleted duplicate data, so the DISTINCT modifier has no effect on the result. 2. Returns all result sets that contain duplicate data.

The following SQL statement uses UNION all to select all Anani data (containing duplicate values) from the "Blog_table_test" and "blog_table" tables:

SELECT Table_title, Table_author from Blog_table_test
WHERE title_author= ' Anani '
UNION All
SELECT Table_title, author from blog_table
WHERE table_author= ' Anani ';

16. MySQL Sort

If you need to sort the data that is read from the MySQL table using the SQL SELECT statement, you can sort by using the MySQL ORDER BY clause, and then return the search results. When you set a sort order, you can: 1, you can use any field as the criteria for sorting, and return the sorted query results. 2. Set multiple fields to sort. 3. Use the ASC or DESC keyword to set the query result to be sorted in ascending or descending order. By default, it is sorted in ascending order. 4. Add WHERE ... The LIKE clause to set the condition.

1. Use the ORDER BY clause at the command prompt to read data from MySQL data table blog_table_test and arrange the results in ascending order of the Submission_date field:

mysql> use blog;
Database changed
Mysql> SELECT * from Blog_table_test ORDER by Submission_date ASC;

2. Use the PHP function's mysqli_query () and the same SQL SELECT command with the ORDER BY clause to get the data. The function is used to execute the SQL command, and then the PHP function Mysqli_fetch_array () to output the data for all queries.

17. MySQL GROUP by statement

The GROUP BY statement groups The result set based on one or more columns, and we can use COUNT, SUM, AVG, and so on in the grouped columns.

The following instance uses the GROUP BY statement to group the data table by name and to count how many records each person has:

Mysql> SELECT Table_author, COUNT (*) from Blog_table_test GROUP by Table_author;

In the example above, we can use as to change the value of the table header that the function occupies in the result data table:

Mysql> SELECT Table_author, COUNT (*) as Count from Blog_table_test GROUP by Table_author;

With ROLLUP can be implemented on the basis of grouping statistics and then the same statistics (Sum,avg,count ... ）。 For example, the above data table is grouped by name, and then count the number of times everyone learns:

Mysql> SELECT Table_author, SUM (study_num) as Study_count from  blog_table_test GROUP by Table_author with ROLLUP;

COALESCE syntax: Select COALESCE (a,b,c), where if a==null, select B; If b==null, select C; If A!=null, select A; if a b c is null, the return is null (meaningless). For example:

Ysql> SELECT COALESCE (table_author, ' total '), SUM (Study_num) as Study_count from  blog_table_test GROUP by Table_ Author with ROLLUP;

18, the use of Mysql connection

The MySQL JOIN queries the data in two or more tables. Join is broadly divided into the following three categories: 1, Nner JOIN (can omit INNER, indicate inner join, or equivalent connection): Gets a record of the field matching relationship in two tables. 2. Left join: Gets all the records of the left table, even if the right table does not have a corresponding record. 3. Right join: Used to get all the records of the right table, even if the left table does not have a matching record.

1. Use MySQL's inner join to connect two tables to read the Table_count field values for all Table_author fields in the Blog_table_test table corresponding to the blog_table table:

Mysql> SELECT a.table_id, A.table_author, B.table_count from Blog_table_test a INNER joins blog_table B on A.table_autho R = B.table_author;

The above statement is equivalent to:

Mysql> SELECT a.table_id, A.table_author, B.table_count from Blog_table_test A, blog_table b WHERE a.table_author = b. Table_author;

2, left JOIN and right connection when there is no corresponding field field value on the other side, the side data is still read and the corresponding opposite side vacancy is set to NULL.

3, PHP using the Mysqli_query () function to execute SQL statements, you can use the same SQL statement above as the parameters of the Mysqli_query () function.

19. MySQL NULL Value Processing

MySQL uses the SQL SELECT command and the WHERE clause to read data from a data table, but the command may not work correctly when the supplied query condition field is NULL. To handle this situation, MySQL provides three large operators: 1, is NULL: This operator returns True when the value of the column is null. 2, is not NULL: the operator returns True when the value of the column is not NULL. 3, <=>: comparison operator (unlike the = operator), returns True when the two value of a comparison is NULL.

In a PHP script, you can handle the variable as NULL in the If...else statement and generate the appropriate conditional statement.

20. MySQL Regular Expression

MySQL supports regular expression matching, and MySQL uses the REGEXP operator for regular expression matching. For example, find all data that begins with ' an ' in the Table_author field:

Mysql> SELECT table_author from blog_table_test WHERE table_author REGEXP ' ^an ';

21. MySQL Transaction

MySQL transaction is mainly used to deal with large-scale and high-complexity data. In MySQL, transactions are supported only by databases or tables that use the INNODB database engine. Transactions are used to manage insert,update,delete statements. Transactions can be used to maintain the integrity of the database, ensuring that batches of SQL statements are either fully executed or not executed at all.

A transaction is required to meet 4 conditions (ACID): atomicity (atomicity): A set of transactions, either successful or withdrawn. , consistency (stability): There are illegal data (foreign key constraints, and so on), transaction recall. , Isolation (Isolation): Transactions run independently of each other. The result of one transaction affects other transactions, and then other transactions are recalled. The 100% isolation of a transaction requires a sacrifice of speed. , Durability (reliability): After the hardware and software crashes, the INNODB data table driver uses the log file to refactor the changes. Reliability and high speed cannot be combined, the INNODB_FLUSH_LOG_AT_TRX_COMMIT option determines when the transaction is saved to the log.

Transaction control statements: 1, begin or start TRANSACTION; explicitly open a transaction; 2. Commit; You can also use commit work, but the two are equivalent. Commit commits the transaction and makes all modifications to the database known as permanent, 3, ROLLBACK, and ROLLBACK work, but the two are equivalent. Rollback ends the user's transaction and revokes any uncommitted modifications that are in progress; 4. SavePoint Identifier;savepoint allows you to create a savepoint in a transaction that can have multiple savepoint;5, RELEASE SavePoint identifier; Deletes the savepoint of a transaction, and when there is no savepoint specified, execution of the statement throws an exception, 6, ROLLBACK to identifier, rollback of the transaction to the Mark Point, 7, SET TRANSACTION ; used to set the isolation level of a transaction. The InnoDB storage engine provides transaction isolation levels of READ UNCOMMITTED, read COMMITTED, Repeatable read, and serializable. 8, set autocommit=0 prohibit automatic submission; set autocommit=1 turn on auto-commit.

Examples of transactions:

mysql> use blog;
Database changed
Mysql> CREATE TABLE table_transaction_test (id int (5)) Engine=innodb;  # Create a data table
Query OK, 0 rows affected (0.04 sec)
Mysql> begin;  # Start a transaction
Query OK, 0 rows Affected (0.00 sec)

mysql> INSERT into Table_transaction_test value (1);
Query OK, 1 rows affected (0.01 sec)

mysql> rollback;   # Roll Back
Query OK, 0 rows Affected (0.00 sec)

Mysql> commit; # COMMIT TRANSACTION

The method of using transaction in PHP is similar to the previous PHP language operation MySQL, the main steps: 1, establish the connection: $conn =mysqli_connect (). 2, check the connection. 3, set the code: Mysqli_query ($conn, "Set names UTF8");. 4. Enter the specified database: mysqli_select_db ($conn, ' blog '); 5, set the transaction does not automatically commit: Mysqli_query ($conn, "set autocommit=0");. 6. Start Business: mysqli_begin_transaction ($conn);. 7. Transaction: if (!mysqli_query ($conn, "INSERT into table_transaction_test (ID) VALUES (1)")) {Mysqli_query ($conn, "ROLLBACK"); /Determine rollback when execution fails. 8, commit the transaction: Mysqli_commit ($conn); 9, disconnect: Mysqli_close ($conn);.

To learn more about MySQL, click: Learn MySQL (next)

Learn MySQL (UP)