MySQL Basics in 1 Hour

Getting Started with MySQL

mySQL (relational database management system)

MySQL is a relational database management system developed by Swedish company MySQL AB and currently a product 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 that stores data in different tables instead of putting all the 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 a dual licensing policy and is divided into community edition and commercial edition. Due to its small size, fast speed, low total cost of ownership, and especially its open source nature, MySQL is generally chosen as the website database for the development of small and medium-sized websites.
Due to the excellent performance of its community edition, it can form a good development environment when paired with PHP and Apache.

1. A database is a warehouse that organizes, stores, and manages data according to data structures. Each database has one or more different APIs for creating, accessing, managing, searching, and copying stored data.

2. Use relational database management systems (RDBMS) to store and manage large amounts of data. A relational database is a database built on the basis of the relational model, which uses mathematical concepts and methods such as set algebra to process data in the database.

3. RDBMS features:

1. Data appears in table form

2. Each line is a record name

3. Each column is the data field corresponding to the record name

4. Many rows and columns make up a form

5. Several forms make up the database

4. RDBMS Terminology

Redundancy: Store twice as much data. Redundancy can make the system faster.

Primary Key: A primary key is unique. A data table can contain only one primary key. You can use the primary key to query data.

Foreign Key: A foreign key is used to relate two tables.

Composite key: A composite key (composite key) uses multiple columns as an index key and is generally used for composite indexes.

Indexes: Use indexes to quickly access specific information in a database table. An index is a structure that orders the values ​​of one or more columns in a database table. Similar to the table of contents of a book.

Referential integrity: Referential integrity requires that a relationship does not allow references to non-existent entities. Entity integrity is an integrity constraint that must be met by the relational model to ensure data consistency.

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

MySQL Administration

6. Start and stop 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 changed

mysql> INSERT INTO user
(host, user, password,
select_priv, insert_priv, update_priv) //Set privilege Y
VALUES ('localhost', 'guest',
PASSWORD('guest123'), 'Y', 'Y','Y');

Query OK, 1 row affected (0.20 sec)

mysql> FLUSH PRIVILEGES;

Note that the FLUSH PRIVILEGES statement needs to be executed. This command will reload the authorization table after execution.

Another way to add users is through the SQL GRANT command

mysql> GRANT SELECT, 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> SHOW DATABASES;

SHOW TABLES: Displays all tables in the specified database. Before using this command, you need to use the use command to select the database to be operated.

mysql> useW3CSCHOOL;

Database changed

mysql> SHOW TABLES;

SHOW COLUMNS FROM data table: Displays the properties of the data table, attribute type, primary key information, whether it is NULL, default value and other information.

mysql> SHOW COLUMNSFROM W3Cschool_tbl;

+-----------------+--------------+------+-----+---------+-------+

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

+-----------------+--------------+------+-----+---------+-------+

SHOW INDEX FROM data table: Displays detailed index information of the data table, including the PRIMARY KEY.

SHOW TABLE STATUS LIKE data table\G: This command will output the performance and statistical information of the MySQL database management system.

mysql> SHOW TABLESTATUS FROM W3CSCHOOL; # Display information about all tables in the W3CSCHOOL database

mysql> SHOW TABLESTATUS from W3CSCHOOL LIKE 'W3Cschool%'; #Information of tables whose names begin with W3Cschool

mysql> SHOW TABLESTATUS from W3CSCHOOL LIKE 'W3Cschool%'\G; #Add \G to print the query results by column

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

MySQL Connection

10. Connecting using MySQL binary mode

[root@host]# mysql -uroot -p
Enter password:******
After a successful login, the mysql> command prompt window will appear, where you can execute any SQL statement.
To exit the mysql> command prompt window, use the exit command: mysql> exit

11. Connecting to MySQL using PHP script

PHP provides the mysql_connect() function to connect to the database.
connectionmysql_connect(server,user,passwd,new_link,client_flag);5All parameters are optional. Use PHP's mysql_close() function to disconnect from the MySQL database.
bool mysql_close (resource $link_identifier );
Normally, you should not use mysql_close() because open nonpersistent connections are automatically closed after the script finishes executing.
mysql_close() does not close a persistent connection 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. Creating a Database Using mysqladmin

Log in as the root user. The root user has the highest permissions and can use the mysql mysqladmin command to create a database.
[root@host]#mysqladmin -u root -pcreate/drop W3CSCHOOL
Enter password:*****

13. Creating a database using PHP script

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

bool mysql_query( sql, connection);
 
$conn = mysql_connect($dbhost,$dbuser, $dbpass);
if(!$conn)
{
 die('Connection error: ' . mysql_error());
}
echo 'Connection successful<br />';
$sql = 'CREATE/DROP DATABASE W3CSCHOOL';
$retval = mysql_query( $sql, $conn );
if(!$retval )
{
 die('Failed to create database: ' . mysql_error());
}
echo "Database W3CSCHOOL was created successfully\n";
mysql_close($conn);

MySQL Select Database

14. Select MySQL database using PHP script

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 'Connection successful';
mysql_select_db('W3CSCHOOL');
mysql_close($conn);

MySQL create/delete table

15. MySQL create table

The following information is required to create a MySQL 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 a 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 through the command prompt

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

mysql> CREATE TABLE w3cschool_tbl(
 -> w3cschool_id INT NOTNULL AUTO_INCREMENT,
 -> w3cschool_title VARCHAR(100) NOT NULL,
 -> w3cschool_author VARCHAR(40) NOT NULL,
 -> submission_date DATE,
 -> PRIMARY KEY (w3cschool_id )
 -> );

The MySQL command terminator is a semicolon (;).

17. Use PHP scripts to create/delete tables or insert data

Syntax: bool mysql_query( sql, connection);

$sql = "CREATE TABLEtutorials_tbl( "Create"tutorial_id INT NOTNULL AUTO_INCREMENT, ".
  "tutorial_title VARCHAR(100) NOT NULL, ".
  "tutorial_author VARCHAR(40) NOT NULL, ".
  "submission_dateDATE, ".
  "PRIMARY KEY (tutorial_id )); "
;
$sql = "DROP TABLEw3cschool_tbl"; deletemysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn); //Parameters set to determine whether it is successful;
if(!$retval )
{
 die('Data table creation failed: ' . mysql_error());
}
echo "Data table created successfully\n";
mysql_close($conn);

MySQL insert data

18. The common INSERT INTO SQL syntax for inserting data into a MySQL table is:

INSERT INTO table_name (field1, field2,...fieldN )
VALUES
( value1,value2,...valueN );

If the data is of character type, single or double quotes must be used, such as: "value".

Insert a piece of data into the w3cschool_tbl table:

mysql> INSERT INTOw3cschool_tbl
->(w3cschool_title, w3cschool_author,submission_date)
->VALUES
->("Learn PHP", "JohnPoul", NOW());

(->) is not part of the SQL statement. It only represents a new line. If 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 a semicolon (;).

19. Inserting data using PHP script

$sql = "INSERT INTO w3cschool_tbl ".
"(w3cschool_title,w3cschool_author,submission_date)".
"VALUES ".
"('$w3cschool_title','$w3cschool_author','$submission_date')";

MySQL query data

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

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

One or more records can be read through the SELECT command.

You can use OFFSET to specify the data offset where the SELECT statement starts querying. By default, the offset is 0.

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

You can use an asterisk (*) to replace other fields, and the SELECT statement will return all field data in the table.

mysql> SELECT * from w3cschool_tbl

twenty one. Using PHP script to get data

Use the PHP function mysql_query() and the SQL SELECT command to retrieve data.
This function is used to execute SQL commands and then use or output all queried data through the PHP function mysql_fetch_array().

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

$sql = 'SELECT w3cschool_id,w3cschool_title,
w3cschool_author,submission_date
FROM w3cschool_tbl';
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))

Displaying a table with the MYSQL_NUM parameter

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

Displaying a table with the MYSQL_NUM parameter

{
echo "Tutorial ID:{$row['w3cschool_id']} <br>".
"Title:{$row['w3cschool_title']} <br> ".
"Author:{$row['w3cschool_author']} <br> ".
"Submission Date : {$row['submission_date']}<br> ".
"--------------------------------<br>";
}
mysql_free_result($retval); Frees cursor memory
echo "Fetched data successfully\n";
mysql_close($conn);

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

MySQL Select Data

twenty two. Use SQL SELECT statement to read data from MySQL table.

If you need to conditionally select data from a table, you can add a WHERE clause to the SELECT statement. The following is the general syntax of the SQL SELECT statement using the WHERE clause to read data from a table:

SELECT field1,field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND[OR]] condition2.....

The WHERE clause can also be used in SQL DELETE or UPDATE commands.
Use LIKE to compare strings, otherwise the string comparison in MySQL's WHERE clause is case-insensitive. You can use the BINARY keyword to make string comparisons in the WHERE clause case-sensitive.

twenty three. Reading data using PHP script

Use the PHP function mysql_query() and the same SQL SELECT command with a WHERE clause to retrieve data. This function is used to execute SQL commands and then output all queried data through mysql_fetch_array().

$sql = 'SELECT w3cschool_id,w3cschool_title,
w3cschool_author, submission_date
FROM w3cschool_tbl
WHERE w3cschool_author="Sanjay"';

MySQL UPDATE

twenty four. To modify or update data in MySQL, we can use the SQL UPDATE command.

General SQL syntax:

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

[WHERE Clause]

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

mysql>UPDATE w3cschool_tbl
-> SET w3cschool_title='Learning JAVA'
-> WHERE w3cschool_id=3;

Update data using PHP script

$sql = 'UPDATE w3cschool_tbl
SETw3cschool_title="Learning JAVA"
WHERE w3cschool_id=3';

MySQL DELETE

25. DELETE FROM table_name[WHERE Clause

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

You can specify any condition in the WHERE clause

Delete the record with w3cschool_id 3 in the w3cschool_tbl table

mysql> DELETE FROMw3cschool_tbl WHERE w3cschool_id=3;

Deleting data using PHP script

$sql = 'DELETE FROMw3cschool_tbl
WHERE w3cschool_id=3';

MySQL LIKE Clause

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

Without the percent sign (%), the LIKE clause has the same effect as the equal sign (=).
26. The general syntax of the QL SELECT statement using the LIKE clause to read data from a table is:
SELECT field1,field2,...fieldN table_name1, table_name2...
WHERE field1 LIKEcondition1 [AND [OR]] field2 = 'somevalue'
LIKE is usually used with %, similar to a metacharacter search

Using LIKE Clause in PHP Scripts

$sql = 'SELECT w3cschool_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 keywords are used to set the query results to be in ascending or descending order. By default, it is sorted in ascending order.

MySQL Grouping

SELECT column_name,function(column_name)
FROM table_name
WHERE column_nameoperator value
GROUP BY column_name;

WITH ROLLUP can perform the same statistics (SUM, AVG, COUNT...) based on grouped statistical data.

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

select coalesce(a,b,c);

Parameter description: If a==null, select b; if b==null, select c; if a!=null, select a; if abc are both null, return null (meaningless).

mysql> SELECT coalesce(name, 'total'), SUM(singin) as singin_countFROM employee_tbl GROUP BY name WITHROLLUP;

+--------------------------+--------------+

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

+--------------------------+--------------+

| Xiaoli | 2 |

| Xiao Ming | 7 |

| Xiao Wang | 7 |

| Total | 16 |

+--------------------------+--------------+

MySQL multi-table query

27. Use MySQL's JOIN to combine multiple tables in SELECT, UPDATE and DELETE statements.

JOIN can be roughly divided into the following three categories according to their functions:

INNER JOIN (inner join, or equijoin): Get records with matching fields in two tables.
LEFT JOIN: Get all records in the left table, even if there are no corresponding matching records in the right table.
RIGHT JOIN: The opposite of LEFT JOIN, used to obtain all records in the right table, even if there are no corresponding matching records 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 |

Connect the above two tables to read the w3cschool_count field values ​​corresponding to all w3cschool_author fields in the w3cschool_tbl table in the tcount_tbl table:

mysql> SELECT a.w3cschool_id, a.w3cschool_author, b.w3cschool_count FROM w3cschool_tbl a INNER 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> SELECT a.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 from the data table w3cschool_tbl on the left, even if there is no corresponding w3cschool_author field value Abdul S in the table tcount_tbl on the right.

MySQL NULL

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

Comparing a NULL value to any other value (even NULL) always returns false.

Handling NULL values ​​using PHP script:

In PHP scripts, you can use if...else statements to check whether a variable is empty and generate corresponding conditional statements.

MySQL Regular Expressions

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

^ Matches the beginning 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 data starting with 'st' in the name field:

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

Find all data ending with 'ok' in the name field:

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

Find all data containing the string 'mar' in the name field:

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

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

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

MySQL Transactions

29. MySQL transactions are mainly used to process data with large operation volumes 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 all executed or none of them are executed.

Transactions are used to manage insert, update, and delete statements.

Transactions must meet four conditions (ACID): Atomicity, Consistency, Isolation, and Durability

1. Atomicity of transactions: A set of transactions either succeeds or is withdrawn.

2. Stability: If there is illegal data (such as foreign key constraints), the transaction will be withdrawn.

3. Isolation: Transactions run independently. If the result of a transaction affects other transactions, then the other transactions will be withdrawn. 100% isolation of transactions requires sacrificing speed.

4. Reliability: After a software or hardware crash, the InnoDB data table driver will use the log file to reconstruct and modify it. Reliability and high speed cannot be achieved at the same time. The innodb_flush_log_at_trx_commit option determines when transactions are saved to the log.

Use transactions in the MySQL console to operate:

1. Start a transaction

start transaction

2. Make a save point

savepoint savepoint name

3. Operation

4. You can roll back or commit. If there is no problem, commit. If there is a problem, roll back.

Using transaction examples in PHP

mysql_query("SETAUTOCOMMIT=0"); //Set to not automatically commit, because MYSQL executes immediately by default mysql_query("BEGIN"); //Start transaction definition

if(!mysql_query("insertinto trans (id) values('2')"))
{
mysql_query("ROOLBACK"); //Judge if the execution fails and roll back}
mysql_query("COMMIT");//Execute transaction mysql_close($handler);
MySQL ALTER

30. When you modify the name of a data table or modify a data table field, you need to use the MySQL ALTER command.

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

mysql> ALTER TABLE test alter_tbl DROP i;

If there is only one field left in the data table, DROP cannot be used to delete the field.

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

mysql> ALTER TABLE testalter_tbl ADD i INT;

After the following ALTERTABLE statement is successfully executed, use SHOW COLUMNS to view the changes in the table structure:

ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c;

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

Modify the field type and name:

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

To change the type of field c from CHAR(1) to CHAR(10), you can execute the following command:
mysql> ALTER TABLE test alter_tbl MODIFY c CHAR(10);
Using the CHANGE clause, the syntax is very different. After the CHANGE keyword, follow 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 TABLE testalter_tbl CHANGE ij BIGINT;
<pIf you now want to change the field j from BIGINT to INT, the SQL statement is as follows:
mysql> ALTER TABLE testalter_tbl CHANGE jj INT;

ALTER changes the default value of a field, mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
ALTER and DROP delete the default value of the column, 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 change the name of the data table, mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

MySQL Indexes

Indexes can greatly improve MySQL's retrieval speed. Indexes are divided into single-column indexes and combined indexes. A single-column index is an index that contains only a single column. A table can have multiple single-column indexes, but these are not composite indexes. Composite index, that is, an index contains multiple columns.
Create an index and ensure that the index is applied to the SQL query statement (usually as a condition of the WHERE clause).
The index is also a table that stores the primary key and index fields and points to the records of the entity table.
Disadvantages: Although indexes greatly improve query speed, they also reduce the speed of updating tables, such as INSERT, UPDATE, and DELETE. Because when updating a table, MySQL not only needs to save the data, but also the index file.
3 ways to create a common index

CREATE INDEX indexName ONmytable(username(length));

If it is CHAR or VARCHAR type, length can be smaller than the actual length of the field; if it is BLOB or TEXT type, length must be specified.

Modify table structure

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

Specify directly when creating a table

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

Syntax to delete an index

DROP INDEX [indexName] ONmytable;

Unique index: add UNIQUE in front

Adding and dropping indexes using the ALTER command

There are four ways to add indexes to a 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): This statement creates an index whose values ​​must be unique (except NULL, which may appear multiple times).
ALTER TABLE tbl_name ADD INDEXindex_name (column_list): Add a normal index. The index value can appear multiple times.
ALTER TABLE tbl_name ADD FULLTEXTindex_name (column_list): This statement specifies the index as FULLTEXT for full-text indexing.

Use the ALTER command to add and delete primary keys

A primary key can only be applied to one column. When adding a primary key index, you need to ensure that the primary key is not empty (NOT NULL) by default. The following are examples:

mysql> ALTER TABLE test alter_tbl MODIFY i INT NOT NULL;

mysql> ALTER TABLE test alter_tbl ADD PRIMARY KEY (i);

You can also delete a primary key using the ALTER command:

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

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

mysql> SHOW INDEX FROM table_name\G

MySQL temporary tables

Temporary tables are only visible to the current connection. 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 in a table you can use the INSERT INTO... SELECT statement to do so.

mysql> INSERT INTOclone_tbl (w3cschool_id,
-> w3cschool_title,
-> w3cschool_author,
-> submission_date)
-> SELECT w3cschool_id,w3cschool_title,
-> w3cschool_author,submission_date
->FROM w3cschool_tbl;

MySQL Metadata

I want to know the following three types of MySQL information:

Query result information: The number of records affected by the SELECT, UPDATE, or DELETE statement.
Database and data table information: Contains the structural information of the database and data tables.
MySQL server information: Contains the current status of the database server, version number, etc.

(1) Use do() to execute $query

my $count = $dbh->do($query);

(2) Use prepare() and execute() to execute $query

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 a query statement.

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

List of databases and tables

PERL Examples

# Get all available tables in the current database.

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

PHP example:

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

MySQL Sequence

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

Use MySQL AUTO_INCREMENT to define the column.

mysql> CREATE TABLE insert

-> (

-> id INT UNSIGNED NOT NULL AUTO_INCREMENT,

Use the LAST_INSERT_ID() function in SQL to get the value of the auto-increment column in the last inserted table.

PERL Examples

Use the mysql_insertid attribute to obtain the value of AUTO_INCREMENT. The following are examples:

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

VALUES('moth','2001-09-14','windowsill')");

my $seq = $dbh->{mysql_insertid};

PHP Example

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

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

Reset Sequence

If you have deleted multiple records in the data table and rearranged the AUTO_INCREMENT columns of the remaining data, you can do this by deleting the auto-increment column and then adding it again.

mysql> ALTER TABLEinsect DROP id;

mysql> ALTER TABLEinsect

-> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,

-> ADD PRIMARY KEY (id);

Normally the sequence starts at 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, use the following statement to achieve it:

mysql> ALTER TABLE tAUTO_INCREMENT = 100;

MySQL handles duplicate data

Preventing duplicate data from appearing in the table

Set the specified field in the MySQL data table as PRIMARY KEY or UNIQUE index to ensure the uniqueness of the data.

Set the first_name and last_name fields in the table to have unique data. You can set a dual primary key mode to set the uniqueness of the data. If you set a dual primary key, the default value of that key cannot be NULL and can be set to NOT NULL. As shown below:

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 IGNORE INTO and INSERT INTO is that INSERT IGNORE will ignore the data that already exists in the database. If there is no data in the database, new data will be inserted. If there is data, the data will be skipped. This can retain the existing data in the database and achieve the purpose of inserting data in the gap.

Using INSERT IGNORE INTO, no error will occur after execution, and no duplicate data will be 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 a record with the same primary or unique exists, delete it first. Insert new records again.

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)

Counting duplicate data

Count the number of duplicate records of first_name and last_name in the table:

mysql> SELECT COUNT(*) as repetitions,last_name, first_name

 ->FROM person_tbl
 ->GROUP BY last_name, first_name
 ->HAVING repetitions > 1;

Filtering Duplicate Data

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

mysql> SELECT DISTINCT last_name, first_name

->FROM person_tbl

->ORDER BY last_name;

You can also use GROUP BY to read non-duplicate data in the data table:

mysql> SELECT last_name, first_name
 ->FROM person_tbl
 ->GROUP BY (last_name, first_name);

Deduplication

To delete duplicate data in a 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 INDEX (index) and PRIMAY KEY (primary key) in the data table to delete duplicate records in the table. Here’s how:

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

MySQL and SQL Injection

When special characters are not filtered, the SQL that appears is:

// 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 $name variable, and an unnecessary SQL statement was inserted into $name, which will delete all data in the users table.

To prevent SQL injection, pay attention to the following points:

1. Never trust user input. To validate user input, you can use regular expressions or limit the length; convert single quotes and double "-", etc.

2. Never use dynamic SQL assembly. You can use parameterized SQL or directly use stored procedures to query and access data.

3. Never use a database connection with administrator privileges, use a separate database connection with limited permissions for each application.

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

5. The application's exception information should give as few prompts as possible. It is best to use custom error messages to wrap the original error information.

6. The detection method of SQL injection generally adopts auxiliary software or website platform for detection. The software generally uses SQL injection detection tool jsky, and the website platform has Yisi website security platform detection tool. MDCSOFT SCAN, etc. Using MDCSOFT-IPS can effectively defend against SQL injection, XSS attacks, etc.

In scripting languages ​​such as Perl and PHP, user input data 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 * FROM users WHERE name='{$name}'");

Injection in Like Statement

When performing a like query, if the user inputs values ​​containing "_" and "%", the following situation may occur: the user originally only wanted to query "abcd_", but the query results included "abcd_", "abcde", "abcdf", etc.; problems may also occur when the user wants to query "30%" (note: thirty percent).

In PHP scripts, 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 * FROM messages WHERE subject LIKE '{$sub}%'");

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

Syntax:

addcslashes(string,characters)

MySQL export data

Export data using the SELECT ... INTO OUTFILE statement

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

mysql> SELECT * FROM tutorials_tbl

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

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

SELECT a,b,a+b INTOOUTFILE '/tmp/result.text'
FIELDS TERMINATED BY',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

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

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

SELECT ... INTO OUTFILE 'file_name' can be used to write the selected rows to a file. The file is created on the server host, so you must have the FILE privilege to use this syntax.

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

You need an account to log in to the server to retrieve files. Otherwise SELECT ... INTO OUTFILE has no effect.

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

Export table as raw data

mysqldump is a utility used by MySQL to dump databases.

Export the tutorials_tbl table to the /tmp directory:

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

--tab=/tmp W3CSCHOOL w3cschool_tbl

password ******

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

$ mysqldump -u root -pW3CSCHOOL > database_dump.txt

password ******

To back up all databases, you can use the following command:

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

password ******

Specify the database name and table in the mysqldump command.

Execute the following command on the source host to back up the data to the dump.txt file:

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

password *****

Import the backed up database into the MySQL server. You can use the following command. You need to confirm that the database has been created:

$ mysql -u root -pdatabase_name < dump.txt password *****

The following command will import the exported data directly to the remote server, but please make sure that the two servers are connected and can access each other:</p>

$ mysqldump -u root -pdatabase_name \

| mysql -h other-host.com database_name

MySQL import data

Read the file dump.txt from the current directory and insert the data in the file into the mytbl table of the current database.

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, however, 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, the column order in the data file is a, b, c, but the column order in the insert table is b, c, a. The data import syntax is as follows:

mysql> LOAD DATALOCAL INFILE 'dump.txt'

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

Importing data using mysqlimport

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

To import data from the dump.txt file into the mytbl table, 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. 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 *****