Shulou(Shulou.com)05/31 Report--

This article shares with you the content of a sample analysis of the basics of MySQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Getting started with MySQL

MySQL (Relational Database Management system)

MySQL is a relational database management system developed by the Swedish company MySQL AB and currently belongs to the products of Oracle. MySQL is one of the most popular relational database management systems. In terms of WEB applications, MySQL is the best RDBMS (relational database management system) application software.

MySQL is a relational database management system in which relational databases store data in different tables instead of all data in one large warehouse, which increases speed and flexibility.

The SQL language used by MySQL is the most commonly used standardized language for accessing databases. MySQL software adopts the dual licensing policy, which is divided into community version and commercial version. Because of its small size, high speed and low total cost of ownership, especially open source, the development of small and medium-sized websites generally choose MySQL as the website database.

Because of the excellent performance of its community version, it can form a good development environment with PHP and Apache.

1. A database (Database) is a warehouse that organizes, stores and manages data according to the data structure. Each database has one or more different API to create, access, manage, search and copy the saved data.

2. Use a relational database management system (RDBMS) to store and manage large amounts of data. Relational database is a database based on relational model, which deals with the data in the database with the help of mathematical concepts and methods such as set algebra.

3. Characteristics of RDBMS:

1. The data appears in tabular form.

two。 Various record names for each behavior

3. The data field corresponding to the record name of each column

4. Many rows and columns form a form

5. Several forms make up database

4. RDBMS terminology

Redundancy: storing twice as much data, redundancy can make the system faster.

Primary key: the primary key is unique. There can be only one primary key in a data table. You can use the primary key to query data.

Foreign keys: foreign keys are used to associate two tables.

Compound key: compound key (compound key) uses multiple columns as a single index key, which is generally used for composite indexes.

Indexes: use indexes to quickly access specific information in database tables. An index is a structure that sorts the values of one or more columns in a database table. A catalogue similar to a book.

Referential integrity: referential integrity requires that references to entities that do not exist are not allowed in the relationship. Entity integrity is the integrity constraint that the relational model must meet in order to ensure the consistency of the data.

5. MySQL is an associated database management system, which stores data in different tables instead of all data in a large warehouse, which increases speed and flexibility.

MySQL Management 6. Start and shut down the MySQL server:

(1) check whether the MySQL server is started:

Ps-ef | grepmysqld

(2) start the MySQL server:

Root@host# cd/usr/bin

. / safe_mysqld&

(3) shut down the currently running MySQL server:

Root@host# cd/usr/bin

. / mysqladmin-u root-p shutdown

Enterpassword: *

7. MySQL user Settings

Add a new user to the user table in the MySQL database:

Root@host# mysql-u root-p / / Select database Enter password:*mysql > use mysql;Database changedmysql > INSERT INTO user (host, user, password,select_priv, insert_priv,update_priv) / / set permissions YVALUES ('localhost',' guest',PASSWORD ('guest123'),' Yoshimi Y','Y'); Query OK, 1 row affected (0.20 sec) mysql > FLUSH PRIVILEGES

Note that the FLUSH PRIVILEGES statement needs to be executed. After this command is executed, the authorization table is reloaded.

Another way to add users is through the GRANT command of SQL

Mysql > GRANTSELECT,INSERT,UPDATE,DELETE,CREATE,DROP- > ON TUTORIALS.*- > TO 'zara'@'localhost'- > IDENTIFIED BY'zara123';8. Commands for managing MySQL

USE database name: select the MySQL database to operate:

Mysql > use W3CSCHOOL

Database changed

SHOW DATABASES: lists the databases of the MySQL database management system:

Mysql > SHOWDATABASES

SHOW TABLES: displays all tables in the specified database. Before using this command, you need to use the use command to select the database for operation.

Mysql > useW3CSCHOOL

Database changed

Mysql > SHOW TABLES

SHOW COLUMNS FROM datasheet: displays the attributes, attribute types, primary key information, NULL, default values and other information of the datasheet.

Mysql > SHOW COLUMNSFROM W3Cschool_tbl

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

SHOW INDEX FROM data Table: displays the detailed index information of the data table, including PRIMARY KEY (primary key).

SHOW TABLE STATUS LIKE Datasheet\ G: this command outputs the performance and statistics of the MySQL database management system.

Mysql > SHOW TABLESTATUS FROM W3CSCHOOL; # display information for all tables in the database W3CSCHOOL

Mysql > SHOW TABLESTATUS from W3CSCHOOL LIKE 'W3C% names; # information about tables whose names begin with W3Cschool

Mysql > SHOW TABLESTATUS from W3CSCHOOL LIKE 'W3C%'\ G; # plus\ G, query results are printed as columns

9. PHP MySQL function format: mysql_function (value,value,...)

MySQL connection 10. Connect using MySQL binary

[root@host] # mysql-uroot-p

Enter password:*

After a successful login, a mysql > command prompt window appears, on which you can execute any SQL statement.

To exit mysql > Command prompt window, you can use the exit command: mysql > exit

11. Use PHP script to connect to MySQL

PHP provides the mysql_connect () function to connect to the database.

Connectionmysql_connect (server,user,passwd,new_link,client_flag); 5 all parameters are optional

Use the mysql_close () function of PHP to break the link to the MySQL database.

Bool mysql_close (resource $link_identifier)

Usually do not use mysql_close (), because open non-persistent connections are automatically closed after the script is executed.

Mysql_close () does not close persistent connections established by mysql_pconnect ()

$conn = mysql_connect ($dbhost, $dbuser, $dbpass); if (! $conn) {die ('Could not connect:'. MySQL _ error ());} echo 'Connected successfully';mysql_close ($conn); MySQL create / delete database 12. Create a database using mysqladmin

Log in with the root user, the root user has the highest privileges, and you can use the mysql mysqladmin command to create the database.

[root@host] # mysqladmin-u root-pcreate/drop W3CSCHOOL

Enter password:*

13. Create a database using a PHP script

PHP uses the mysql_query function to create or delete an MySQL database.

Bool mysql_query (sql, connection); $conn = mysql_connect ($dbhost,$dbuser, $dbpass); if (! $conn) {die ('connection error:'. Mysql_error ();} echo 'connection successful'; $sql= 'CREATE/DROP DATABASE W3CSCHOOL connection successful = mysql_query ($sql, $conn); if (! $retval) {die (' failed to create database:'. Mysql_error ();} echo "Database W3CSCHOOL created successfully"; mysql_close ($conn); MySQL Select Database 14. Use PHP script to select MySQL database

PHP provides the function mysql_select_db to select a database.

Bool mysql_select_db (db_name,connection); $conn = mysql_connect ($dbhost,$dbuser, $dbpass); if (! $conn) {die ('connection failed:'. Mysql_error ();} echo 'connected successfully'; mysql_select_db ('W3CSCHOOL'); mysql_close ($conn); MySQL create / delete data table 15.MySQL create data table

The following information is required to create an MySQL data table:

Table name

Table field name

Define each table field

Creation syntax: CREATE TABLE table_name (column_name column_type)

Delete syntax: DROP TABLE table_name

In the following example, we will create the data table w3cschool_tbl in the W3CSCHOOL database:

Tutorials_tbl (tutorial_id INT NOT NULL AUTO_INCREMENT, tutorial_title VARCHAR (100) NOT NULL, tutorial_author VARCHAR (40) NOT NULL, submission_date DATE, PRIMARY KEY (w3cschool_id)); 16. Create a table from the command prompt

Use the SQL statement CREATE TABLE to create the data table.

Mysql > CREATE TABLE w3cschool_tbl (- > w3cschool_id INT NOTNULL AUTO_INCREMENT,-> w3cschool_titleVARCHAR (100th) NOTNULL,-> w3cschool_authorVARCHAR (40) NOTNULL,-> submission_date DATE,-> PRIMARY KEY (w3cschool_id)->)

The MySQL command Terminator is semicolon (;).

17. Create / delete data tables or insert data using PHP scripts

Syntax: bool mysql_query (sql, connection)

$sql = "CREATE TABLEtutorials_tbl (" create "tutorial_id INT NOTNULL AUTO_INCREMENT,". "tutorial_titleVARCHAR NOT NULL,". Tutorial_authorVARCHAR (40) NOT NULL,. "submission_dateDATE," "PRIMARY KEY (tutorial_id));"; $sql = "DROP TABLEw3cschool_tbl"; delete mysql_select_db ('TUTORIALS'); $retval = mysql_query ($sql, $conn); / / parameters set to determine success; if (! $retval) {die (' data table creation failed:'. Mysql_error ();} echo "datasheet created successfully\ n"; mysql_close ($conn); MySQL insert data

18. Insert the common INSERT INTO SQL syntax for data into the MySQL data table:

INSERT INTO table_name (field1, field2,...fieldN)

VALUES

(value1,value2,...valueN)

If the data is character type, you must use single or double quotation marks, such as "value".

The w3cschool_tbl table inserts a piece of data:

Mysql > INSERT INTOw3cschool_tbl

-> (w3cschool_title, w3C copyright authorship authoritative submissioncreative date)

-> VALUES

-> ("Learn PHP", "JohnPoul", NOW ())

(- >) is not part of the SQL statement, it only represents a new line, such as the SQL statement is too long, you can use the enter key to create a new line to write the SQL statement, the command Terminator of the SQL statement is the semicolon (;).

19. Insert data using PHP scripts

$sql = "INSERT INTO w3cschool_tbl".

"(w3C written title authoritative submissionary date)".

"VALUES".

"(" ('$w3c written titlewritten written written w3cwritten authoritative written written submissiondated') "

MySQL query data

20. The common SELECT syntax for querying data in an MySQL database:

SELECT column_name,column_name

FROM table_name

[WHERE Clause]

[OFFSET M] [LIMIT N]

You can read one or more records through the SELECT command.

You can specify through OFFSET that the data offset for the SELECT statement to start the query is 0 by default.

You can use the LIMIT property to set the number of records returned.

You can use an asterisk (*) instead of other fields, and the SELECT statement returns all the field data of the table

Mysql > SELECT * from w3cschool_tbl

21. Use PHP script to get data

Use the mysql_query () and SQL SELECT commands of the PHP function to get the data.

This function is used to execute the SQL command, and then use or output the data of all queries through the PHP function mysql_fetch_array ().

Try the following example to display all records of the data table w3cschool_tbl

$sql = 'SELECT w3cmovie title

W3cschool_author,submission_date

FROM w3c

Mysql_select_db ('W3CSCHOOL')

$retval = mysql_query ($sql, $conn)

If (! $retval)

{

Die ('Could not get data:'. MySQL _ error ())

}

While ($row = mysql_fetch_array ($retval, MYSQL_ASSOC))

While ($row = mysql_fetch_assoc ($retval))

Display data tables with MYSQL_NUM parameters

While ($row = mysql_fetch_array ($retval, MYSQL_NUM))

Display data tables with MYSQL_NUM parameters

{

Echo "Tutorial ID: {$row ['w3C roomid']}

".

"Title: {$row ['w3cstarted title']}

".

"Author: {$row ['w3C written authorship']}

".

"Submission Date: {$row ['submission_date']}

".

"--

"

}

Mysql_free_result ($retval); free cursor memory

Echo "Fetched data successfully\ n"

Mysql_close ($conn)

MYSQL_ASSOC, set this parameter to return an associative array of query results. You can use the field name as the index of the array.

MySQL selection data

22. Use SQL SELECT statements to read data from the MySQL table.

To conditionally select data from a table, you can add a WHERE clause to the SELECT statement

The following is the general syntax for SQL SELECT statements to read data from a data table using the WHERE clause:

SELECT field1,field2,...fieldN FROM table_name1, table_name2...

[WHERE condition1 [AND[OR]] condition2.

The WHERE clause can also be applied to SQL's DELETE or UPDATE commands.

Use LIKE to compare strings, otherwise the string comparison of the WHERE clause of MySQL is case-insensitive. You can use the BINARY keyword to set that the string comparison of the WHERE clause is case-sensitive.

23. Use PHP scripts to read data

Use the mysql_query () of the PHP function and the same SQL SELECT command with the WHERE clause to get the data. This function is used to execute the SQL command and then output the data for all queries through mysql_fetch_array ().

$sql = 'SELECT w3cmovie title

W3cschool_author, submission_date

FROM w3cschool_tbl

WHEREw3cschool_author= "Sanjay"'

MySQL UPDATE

24. We can use the SQL UPDATE command to modify or update the data in MySQL.

General SQL syntax:

UPDATE table_name SET field1=new-value1, field2=new-value2

[WHERE Clause]

Update the value of the w3cschool_title field with w3cschool_id 3 in the data table:

Mysql > UPDATE w3cschool_tbl

-> SET w3cschool_title='Learning JAVA'

-> WHERE w3cschool_id=3

Use PHP scripts to update data

$sql = 'UPDATE w3cschool_tbl

SETw3cschool_title= "Learning JAVA"

WHERE w3csubscription idling 3'

MySQL DELETE

25.DELETE FROM table_ name[WHERE Clause

If no WHERE clause is specified, all records in the MySQL table are deleted.

You can specify any condition in the WHERE clause

Delete records with a w3cschool_id of 3 in the w3cschool_tbl table

Mysql > DELETE FROMw3cschool_tbl WHERE w3cschool_id=3

Delete data with PHP script

$sql = 'DELETE FROMw3cschool_tbl

WHERE w3csubscription idling 3'

MySQL LIKE clause

The percent sign (%) character is used in the QL LIKE clause to represent any character

Without using the percent sign (%), the LIKE clause has the same effect as the equal sign (=).

The 26.QL SELECT statement uses the general syntax for reading data from a data table using the LIKE clause:

SELECT field1,field2,...fieldN table_name1, table_name2...

WHERE field1 LIKEcondition1 [AND [OR]] filed2 = 'somevalue'

LIKE is usually used with%, similar to a metacharacter search

Using the LIKE clause in PHP scripts

$sql = 'SELECTw3cschool_id, w3cschool_title

W3cschool_author,submission_date

FROM w3cschool_tbl

WHERE w3cschool_author LIKE "% jay%"'

MySQL sorting

SELECT field1,field2,...fieldN table_name1, table_name2...

ORDER BY field1, [field2...] [ASC [DESC]]

ASC or DESC keyword to set whether the query results are sorted in ascending or descending order. By default, it is arranged by liter.

MySQL grouping

SELECT column_name,function (column_name)

FROM table_name

WHERE column_nameoperator value

GROUP BY column_name

WITH ROLLUP can realize the same statistics on the basis of grouped statistics (SUM,AVG,COUNT. ).

Coalesce to set a name that can replace NUll, coalesce syntax:

Select coalesce (a _

Parameter description: select b if a==null, c if b==null, an if a b c is null, and null if both a b c are null.

Mysql > SELECTcoalesce (name, 'Total'), SUM (singin) as singin_countFROM employee_tbl GROUP BY name WITHROLLUP

+-+ +

| | coalesce (name, 'total') | singin_count |

+-+ +

| | Xiao Li | 2 |

| | Xiaoming | 7 |

| | Xiao Wang | 7 |

| | Total number | 16 | |

+-+ +

MySQL multi-table query

27. Use Mysql's JOIN in SELECT, UPDATE, and DELETE statements to federate multi-table queries.

JOIN is roughly divided into the following three categories according to its functions:

INNER JOIN (inner join, or equivalent join): gets the record of the matching relationship between the fields in two tables.

LEFT JOIN (left join): gets all the records in the left table, even if there are no matching records in the right table.

RIGHT JOIN (right join): contrary to LEFT JOIN, it is used to get all the records in the right table, even if there is no corresponding matching record in the left table.

| | w3cschool_author | w3cschool_count |

+-+ +

| | mahran | 20 |

| | mahnaz | NULL |

| | Jen | NULL |

| | Gill | 20 |

| | John Poul | 1 | |

| | Sanjay | 1 | |

+-+ +

Mysql > SELECT * fromw3cschool_tbl

+-+

| | w3cschool_id | w3cschool_title | w3cschool_author | submission_date | |

+-+

| | 1 | Learn PHP | John Poul | 2007-05-24 |

| | 2 | LearnMySQL | Abdul S | 2007-05-24 | |

| | 3 | JAVATutorial | Sanjay | 2007-05-06 |

Join the above two tables to read the w3cschool_count field values of all the w3cschool_author fields in the w3cstarted _ tcount_tbl table corresponding to the tcount_tbl table:

Mysql > SELECTa.w3cschool_id, a.w3cschool_author, b.w3cschool_count FROM w3cschool_tbl aINNER JOIN tcount_tbl b ON a.w3cschool_author = b.w3cschool_author

+-+

| | w3cschool_id | w3cschool_author | w3cschool_count | |

+-+

| | 1 | John Poul | 1 |

| | 3 | Sanjay | 1 | |

W3cschool_tbl is the left table, tcount_tbl is the right table

Mysql > SELECTa.w3cschool_id, a.w3cschool_author, b.w3cschool_count FROM w3cschool_tbl a LEFTJOIN tcount_tbl b ON a.w3cschool_author = b.w3cschool_author

+-+

| | w3cschool_id | w3cschool_author | w3cschool_count | |

+-+

| | 1 | John Poul | 1 |

| | 2 | Abdul S | NULL |

| | 3 | Sanjay | 1 | |

All selected field data in the data table w3cschool_tbl on the left, even if there is no corresponding w3cschool_author field value Abdul S in table tcount_tbl on the right.

MySQL NULL

IS NULL: this operator returns true when the value of the column is NULL.

IS NOT NULL: the operator returns true when the value of the column is not NULL.

A comparison of a null value with any other value, even if it is NULL, always returns false

Use PHP scripts to process NULL values:

In the PHP script, you can process whether the variable is empty or not in the if...else statement and generate the corresponding conditional statement.

MySQL regular expression

The REGEXP operator is used in 28.MySQL for regular expression matching.

^ matches the starting position of the input string. If the Multiline property of the RegExp object is set, ^ also matches the position after'\ n'or'\ r'.

$matches the end of the input string. If the Multiline property of the RegExp object is set, $also matches the position before'\ n'or'\ r'.

. Matches any single character except "\ n". To match any character, including'\ n', use a pattern like'[.\ n]'.

Example (table name: person_tbl) to deepen our understanding:

Find all the data in the name field that begins with 'st':

Mysql > SELECT name FROM person_tbl WHERE name REGEXP'^ st'

Find all the data in the name field that ends with 'ok':

Mysql > SELECT name FROM person_tbl WHERE name REGEXP 'ok$'

Find all the data in the name field that contains the 'mar' string:

Mysql > SELECT name FROM person_tbl WHERE name REGEXP 'mar'

Find all data in the name field that begins with a vowel character or ends with a 'ok' string:

Mysql > SELECT name FROM person_tbl WHERE name REGEXP' ^ [aeiou] | ok$'

MySQL transaction

29.MySQL transactions are mainly used to deal with data with large amount of operations and high complexity.

In MySQL, only databases or tables that use the Innodb database engine support transactions

Transaction processing can be used to maintain the integrity of the database, ensuring that batches of SQL statements are either executed or not executed

Transactions are used to manage insert,update,delete statements

A transaction must meet four conditions (ACID): Atomicity (atomicity), Consistency (stability), Isolation (isolation), and Durability (reliability)

1. Atomicity of transactions: a set of transactions that either succeed or are withdrawn.

2. Stability: illegal data (foreign key constraints, etc.) and transaction withdrawal.

3. Isolation: transactions run independently. If the result of one transaction affects other transactions, the other transactions will be withdrawn. 100% isolation of transactions requires the sacrifice of speed.

4. Reliability: after the software and hardware crash, the InnoDB data table driver will use log files to reconstruct and modify. You can't have both reliability and high speed, and the innodb_flush_log_at_trx_commit option determines when to save the transaction to the log.

Use transactions in the MySQL console to operate:

1, start a transaction

Start transaction

2, be the save point

Savepoint save point name

3, operation

4, you can roll back, you can submit, if there is no problem, you can submit it, and if you have any problems, roll back.

Using transaction instances in PHP

Mysql_query ("SETAUTOCOMMIT=0"); / / set to not commit automatically, because MYSQL immediately executes mysql_query ("BEGIN") by default; / / starts the transaction definition

If (! mysql_query ("insertinto trans (id) values ('2') {mysql_query (" ROOLBACK "); / / decide to roll back} mysql_query (" COMMIT ") when execution fails; / / execute transaction mysql_close ($handler); MySQL ALTER

30. You need to use the MySQL ALTER command when you modify the datasheet name or fields.

The ALTER command and the DROP clause are used to delete the I field of the table created above:

Mysql > ALTER TABLEtestalter_tbl DROP I

There is only one field left in the data table and you cannot use DROP to delete the field.

ADD clause to add columns to the data table, add the I field to the table testalter_tbl, and define the data type:

Mysql > ALTER TABLEtestalter_tbl ADD i INT

The following ALTERTABLE statement, after successful execution, uses SHOW COLUMNS to view the changes in the table structure:

ALTER TABLEtestalter_tbl DROP i

ALTER TABLEtestalter_tbl ADD i INT FIRST

ALTER TABLEtestalter_tbl DROP i

ALTER TABLEtestalter_tbl ADD i INT AFTER c

The FIRST and AFTER keywords are used only in the ADD clause, so if you want to reset the position of the datasheet field, you need to use DROP to delete the field and then use ADD to add the field and set the location.

Modify the field type and name:

31. Use the MODIFY or CHANGE clause in the ALTER command.

Change the type of field c from CHAR (1) to CHAR (10), you can execute the following command:

Mysql > ALTER TABLEtestalter_tbl MODIFY c CHAR (10)

Using the CHANGE clause, the syntax is very different. After the CHANGE keyword, this is followed by the name of the field you want to modify, and then specify the type and name of the new field. Try the following example:

Mysql > ALTER TABLEtestalter_tbl CHANGE i j BIGINT

ALTER TABLEtestalter_tbl CHANGE j j INT

ALTER modifies the default value of the field, mysql > ALTER TABLEtestalter_tbl ALTER i SET DEFAULT 1000

Default values for ALTER and DROP delete fields, ALTER TABLEtestalter_tbl ALTER i DROP DEFAULT

ALTER and TYPE modify the data table type, mysql > ALTER TABLEtestalter_tbl TYPE = MYISAM

ALTER TABLE uses RENAME to modify the name of the data table, mysql > ALTER TABLEtestalter_tbl RENAME TO alter_tbl

MySQL index

Index can greatly improve the retrieval speed of MySQL.

Indexes are divided into single-column indexes and combined indexes. A single-column index, that is, an index contains only a single column, and a table can have multiple single-column indexes, but this is not a combined index. Composite index, that is, a single index contains multiple columns.

Create an index to ensure that the index is the condition applied to the SQL query statement (typically as a condition of the WHERE clause).

An index is also a table that holds the primary key and index fields and points to the record of the entity table.

Disadvantages: although indexes greatly improve query speed, they also slow down the speed of updating tables, such as INSERT, UPDATE, and DELETE on tables. Because when updating the table, MySQL not only saves the data, but also saves the index file.

3 ways to create a general index

CREATE INDEX indexName ONmytable (username (length))

If it is of type CHAR,VARCHAR, the length can be less than the actual length of the field; for types of BLOB and TEXT, length must be specified.

Modify table structure

ALTER mytable ADD INDEX [indexName] ON (username (length))

Specify directly when you create a table

CREATE TABLE mytable (ID INT NOTNULL, username VARCHAR (16) NOTNULL, INDEX [indexName] (username (length)

Syntax for deleting an index

DROP INDEX [indexName] ONmytable

Unique index: preceded by UNIQUE

Use the ALTER command to add and remove indexes

There are four ways to add an index to a data table:

ALTER TABLE tbl_name ADD PRIMARYKEY (column_list): this statement adds a primary key, which means that the index value must be unique and cannot be NULL.

ALTER TABLE tbl_name ADD UNIQUEindex_name (column_list): the value that this statement creates an index must be unique (NULL may appear multiple times except for NULL).

ALTER TABLE tbl_name ADD INDEXindex_name (column_list): add a normal index, where the index value can appear multiple times.

ALTER TABLE tbl_name ADD FULLTEXTindex_name (column_list): this statement specifies that the index is FULLTEXT for full-text indexing.

Use the ALTER command to add and remove primary keys

A primary key can only work on one column. When adding a primary key index, you need to make sure that the primary key is not NOT NULL by default. Examples are as follows:

Mysql > ALTER TABLEtestalter_tbl MODIFY i INT NOT NULL

Mysql > ALTER TABLEtestalter_tbl ADD PRIMARY KEY (I)

You can also use the ALTER command to delete the primary key:

Mysql > ALTER TABLEtestalter_tbl DROP PRIMARY KEY

The SHOW INDEX command lists the relevant index information in the table. You can format the output information by adding\ G.

Mysql > SHOW INDEX FROMtable_name\ G

MySQL temporary table

Temporary tables are visible only in the current connection, and when you close the connection, MySQL automatically deletes the table and frees up all space.

Mysql > CREATE TEMPORARY TABLE SalesSummary

Mysql > DROP TABLE SalesSummary

MySQL copy table

Create a new clone table clone_tbl. If you want to copy the data from the data table, you can use INSERT INTO.... To implement the SELECT statement.

Mysql > INSERT INTOclone_tbl (w3cschool_id

-> w3cschool_title

-> w3cschool_author

-> submission_date)

-> SELECT w3cmovie title

-> w3c written authorship submissionproof date

-> FROM w3cschool_tbl

MySQL metadata

Want to know the following three kinds of information about MySQL:

Query result information: the number of records affected by the SELECT,UPDATE or DELETE statement.

Database and data table information: contains the database and data table structure information.

MySQL server information: contains the current status of the database server, version number, etc.

(1) execute $query using do ()

My $count = $dbh- > do ($query)

(2) execute $query using prepare () and execute ()

My $sth = $dbh- > prepare ($query)

My $count = $sth- > execute ()

In PHP, use the mysql_affected_rows () function to get the number of records affected by the query statement.

$result_id = mysql_query ($query, $conn_id); # return $count = ($result_id? mysql_affected_rows ($conn_id): 0) if the query fails; print ("$countrows were affected\ n"); database and datasheet list

PERL instance

# get all available tables in the current database.

My @ tables = $dbh- > tables (); foreach $table (@ tables) {print "Table Name $table\ n";}

PHP instance:

Db_list = mysql_list_dbs ($con); while ($db = mysql_fetch_object ($db_list)) {echo $db- > Database. ";}

MySQL sequence

The MySQL sequence is a set of integers: 1, 2, 3,...

Use MySQLAUTO_INCREMENT to define columns.

Mysql > CREATE TABLEinsect

-> (

-> id INT UNSIGNED NOT NULLAUTO_INCREMENT

Use the LAST_INSERT_ID () function in SQL to get the value of the self-incrementing column in the final insert table.

PERL instance

Use the mysql_insertid property to get the value of AUTO_INCREMENT. Examples are as follows:

$dbh- > do ("INSERT INTO insect (name,date,origin))

VALUES ('moth','2001-09-14)

My $seq = $dbh- > {mysql_insertid}

PHP instance

PHP uses the mysql_insert_id () function to get the value of the AUTO_INCREMENT column in the inserted SQL statement that is executed.

Mysql_query ("INSERT INTO insect (name,date,origin) VALUES ('moth','2001-09-14)", $conn_id); $seq = mysql_insert_id ($conn_id)

Reset sequence

If you delete multiple records from the data table and rearrange the AUTO_INCREMENT columns of the remaining data, you can do this by deleting the self-increasing columns and then re-adding them.

Mysql > ALTER TABLEinsect DROP id

Mysql > ALTER TABLEinsect

-> ADD id INT UNSIGNED NOT NULLAUTO_INCREMENT FIRST

-> ADD PRIMARY KEY (id)

In general, the starting value of the sequence is 1, but if you need to specify a starting value of 100:

-> id INT UNSIGNEDNOT NULL AUTO_INCREMENT = 100

Or after the table is created successfully, it is achieved by the following statement:

Mysql > ALTER TABLE tAUTO_INCREMENT = 100

MySQL handles duplicate data

Prevent duplicate data in the table

Setting the specified field to PRIMARY KEY (primary key) or UNIQUE (unique) index in the MySQL data table ensures the uniqueness of the data.

Set the table field first_name,last_name data can not be repeated, you can set the double primary key mode to set the uniqueness of the data, if you set a double primary key, then the default value of that key can not be NULL, can be set to NOT NULL. As follows:

CREATE TABLE person_tbl (first_name CHAR (20) NOT NULL, last_name CHAR (20) NOT NULL, sexCHAR (10), PRIMARY KEY (last_name, first_name))

The difference between INSERT IGNOREINTO and INSERT INTO is that INSERT IGNORE ignores data that already exists in the database, inserts new data if there is no data in the database, and skips it if there is any data. In this way, the data that already exists in the database can be retained to achieve the purpose of inserting data in the gap.

With INSERT IGNORE INTO, there are no errors after execution, and no duplicate data is inserted into the data table:

Mysql > INSERT IGNORE INTO person_tbl (last_name, first_name)

-> VALUES ('Jay',' Thomas')

Query OK, 1 row affected (0.00 sec)

REPLACE INTO into if there is a record with the same primary or unique, delete it first. And insert a new record.

UNIQUE (last_name, first_name)

Query duplicate records

Select user_name,count (*) as count fromuser_table group by user_name having count > 1

Select * from people

Where peopleId in (select peopleId from peoplegroup by peopleId having count (peopleId) > 1)

Statistical duplicate data

The number of duplicate records of first_name and last_name in the statistical table:

Mysql > SELECT COUNT (*) as repetitions,last_name, first_name-> FROM person_tbl-> GROUP BY last_name, first_name-> HAVING repetitions > 1; filter duplicate data

To read non-duplicated data, you can use the DISTINCT keyword in the SELECT statement to filter duplicate data.

Mysql > SELECT DISTINCT last_name, first_name

-> FROM person_tbl

-> ORDER BY last_name

You can also use GROUP BY to read data that is not duplicated in the data table:

Mysql > SELECT last_name, first_name-> FROM person_tbl-> GROUP BY (last_name, first_name); Delete duplicate data

To delete duplicate data from the data table, you can use the following SQL statement:

Mysql > CREATE TABLE tmp SELECT last_name,first_name, sex-> FROMperson_tbl;-> GROUP BY (last_name,first_name); mysql > DROP TABLE person_tbl;mysql > ALTER TABLE tmp RENAME TO person_tbl

You can also add simple methods such as INDEX (index) and PRIMAY KEY (primary key) to the data table to delete duplicate records in the table. The methods are as follows:

Mysql > ALTER IGNORE TABLE person_tbl-> ADD PRIMARY KEY (last_name, first_name)

MySQL and SQL injection

SQL situation that occurs when special characters are not filtered:

/ / set $name to insert SQL statements that we don't need.

$name = "Qadir';DELETE FROM users;"

Mysql_query ("SELECT* FROM users WHERE name=' {$name}'")

In the above injection statement, we did not filter the variables of $name, and we inserted an unneeded SQL statement in $name, which will delete all the data in the users table.

To prevent SQL injection, note the following points:

1. Never trust the user's input. To check the user's input, you can use regular expressions, or limit the length; convert single quotes and double "-" and so on.

two。 Never use dynamically assembled sql, you can use parameterized sql or directly use stored procedures for data query and access.

3. Never use database connections with administrator privileges, and use separate database connections with limited permissions for each application.

4. Do not store confidential information directly, encrypt or hash passwords and sensitive information.

5. The applied exception information should give as few hints as possible, and it is best to wrap the original error message with a custom error message.

The detection method of 6.sql injection is generally detected by auxiliary software or website platform, and the software generally adopts sql injection detection tool jsky, and the website platform has Yisi website security platform detection tool. MDCSOFT SCAN et al. MDCSOFT-IPS can effectively defend against SQL injection, XSS attacks and so on.

In scripting languages such as Perl and PHP, data entered by the user can be escaped to prevent SQL injection.

PHP's MySQL extension provides the mysql_real_escape_string () function to escape special input characters.

If (get_magic_quotes_gpc ()) {$name= stripslashes ($name);} $name= mysql_real_escape_string ($name); mysql_query ("SELECT * FROMusers WHERE name=' {$name}'"); injection in the Like statement

When querying like, if the value entered by the user has "_" and "%", this will happen: the user only wants to query "abcd_", but there are "abcd_", "abcde", "abcdf" and so on in the query results; there will also be problems when the user wants to query "30%" (Note: 30%).

In the PHP script, we can use the addcslashes () function to handle the above situation, as shown in the following example:

$sub = addcslashes (mysql_real_escape_string ("% something_"), "% _")

/ / $sub = =\% something\ _

Mysql_query ("SELECT * FROMmessages WHERE subject LIKE'{$sub}%'")

The addcslashes () function adds a backslash before the specified character.

Syntax format:

Addcslashes (string,characters)

MySQL export data

Export data using SELECT... INTO OUTFILE statements

Export the datasheet w3cschool_tbl data to the / tmp/tutorials.txt file:

Mysql > SELECT * FROMtutorials_tbl

-> INTO OUTFILE'/ tmp/tutorials.txt'

Generate a file with values separated by commas. This format can be used by many programs.

SELECT a dagger bjorn ahumb INTOOUTFILE'/ tmp/result.text'FIELDS TERMINATED BY',' OPTIONALLY ENCLOSED BY' "'LINES TERMINATED BY'\ n'FROM test_table

SELECT... The INTO OUTFILE statement has the following properties:

LOAD DATA INFILE is SELECT. Reverse operation of INTO OUTFILE, SELECT syntax. To write data from a database to a file, use SELECT... INTO OUTFILE, to read the file back to the database, use LOAD DATA INFILE.

SELECT in the form of SELECT...INTO OUTFILE 'file_name' can write selected lines to a file. The file is created on the server host, so you must have FILE permissions to use this syntax.

The output cannot be an existing file. Prevent file data from being tampered with.

You need to have an account that logs in to the server to retrieve files. Otherwise, SELECT... INTO OUTFILE won't do anything.

In UNIX, the file is created to be readable and the permissions are owned by the MySQL server. This means that although you can read the file, you may not be able to delete it

Export tables as raw data

Mysqldump is the utility that MySQL uses to transfer the database.

Export the datasheet tutorials_tbl to the / tmp directory:

$mysqldump-u root-p--no-create-info\

-- tab=/tmp W3CSCHOOL w3cschool_tbl

Password *

To export data for the entire database, you can use the following command:

$mysqldump-u root-pW3CSCHOOL > database_dump.txt

Password *

To back up all databases, use the following command:

$mysqldump-u root-p--all-databases > database_dump.txt

Password *

Specify the database name and data table in the mysqldump command.

Backup the data to the dump.txt file by executing the following command on the source host:

$mysqldump-u root-pdatabase_name table_name > dump.txt

Password *

To import the backed up database into the MySQL server, you can use the following command, which you need to confirm that the database has been created:

$mysql-u root-pdatabase_name

< dump.txt password ***** 以下命令将导出的数据直接导入到远程的服务器上，但请确保两台服务器是相通的，是可以相互访问的： $ mysqldump -u root -pdatabase_name \ | mysql -h other-host.com database_name MySQL 导入数据 从当前目录中读取文件dump.txt ，将该文件中的数据插入到当前数据库的 mytbl 表中。 mysql>

LOAD DATALOCAL INFILE 'dump.txt' INTO TABLE mytbl

If the user specifies a FIELDS clause, its clauses (TERMINATED BY, [OPTIONALLY] ENCLOSED BY, and ESCAPED BY) are also optional, but the user must specify at least one of them.

Mysql > LOAD DATALOCAL INFILE 'dump.txt' INTO TABLE mytbl

-> FIELDS TERMINATED BY':'

-> LINES TERMINATED BY'\ r\ n'

Specifies the order of the columns.

For example, if the column order in the data file is a _ c, but the column order in the inserted table is _, the syntax of data import is as follows:

Mysql > LOAD DATALOCAL INFILE 'dump.txt'

-> INTO TABLE mytbl (b, c, a)

Import data using mysqlimport

The mysqlimport client provides a command line interface for LOADDATA INFILEQL statements. Most of the options for mysqlimport correspond directly to the LOAD DATA INFILE clause.

To import data from the file dump.txt into the mytbl data Table, you can use the following command:

$mysqlimport-u root-p-- local database_name dump.txt

Password *

The mysqlimport command can specify options to set the specified format, and the command statement format is as follows:

$mysqlimport-u root-p-local-fields-terminated-by= ":"\

-- lines-terminated-by= "\ r\ n" database_name dump.txt

Password *

Use the-- columns option in the mysqlimport statement to set the order of the columns:

$mysqlimport-u root-p-local-columns=b,c,a\

Database_name dump.txt

Password *

Thank you for reading! This is the end of this article on "sample Analysis of the basis of MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.