MySQL Usage Summary

MySQL is the most popular relational database management system, and in Web applications MySQL is one of the best RDBMS (relational database Management system: relational databases Management systems) application software. Here's a summary of MySQL's basic syntax and usage skills.

Standard SQL statement types

1, query statement: Mainly by the SELECT keyword to complete;

2. DML (data manipulation Language) statement: Mainly by the INSERT, UPDATE and delete three keywords completed;

3. DDL (data definition Language) statement: The main create, alter, DROP and truncate four keywords completed;

4. DCL (Data Control Language) statement: Mainly by GRANT and revoke two keywords to complete;

5, the transaction control statement: mainly by commit, rollback and savepoint three keywords complete.

Operation MySQL Database 1, login to MySQL

To log in to MySQL, enter the following command:

MYSQL-H host name-u user name –p password

-H: This command is used to specify the MySQL hostname that the client wants to log on, and the parameter can be omitted when logging on to the current machine;

-U: The name of the user to log in;

-P: Tells the server that a password will be used to log in, ignoring this option if the user name password you want to log in is blank.

2, create, delete, enter, view the database

To create a new database, you can use the following command:

Create DATABASE [IF not EXISTS] DB name

To delete the specified database, you can use the following command:

Drop database name

To enter the specified database, you can use the following command:

Use database name

To view the databases contained under the current instance, you can use the following command:

Show databases

3. View data table, table structure

To view all data tables under the current database, you can use the following command:

Show tables

To view the structure of a specified data table:

DESC Table Name

4. Import SQL Script

For some longer statements at the command prompt, it may be easy to get the error, so we can enter the statement into a. sql file by using any text editor and enter a command to execute the script through file redirection:

Mysql-u user name-p database name < storage location

DDL statements

DDL statements are statements that manipulate database objects, including creation (create), delete (drop), and modify (alter) database objects.

1. Create a data table

The syntax for creating a data table is as follows:

CREATE TABLE table name (column list type [constraint]);

to create a Student information table student, for example, the table holds the number (SNO), name, Gender (sex), age, and the content: CREATE TABLE student ( Sno Int primary KEY, name Char (8) is not NULL, Sex char (8) NOT NULL, Age int );

2. Modify the Data sheet

After you create the table, you want to modify the table by using the ALTER statement.

• Adding columns

ALTER TABLE name add column list data type [after insertion position]

• modifying columns

ALTER TABLE name change column Name column new name new data type

• Delete Column

ALTER TABLE name drop column name

• Renaming a table

ALTER TABLE name rename new table name

3. Delete Data Sheet

The syntax for deleting an entire table is as follows:

DROP table Name

DML statements

DML primarily operates data in the data table, using DML to implement "insert new data", "Modify existing Data", "Delete unwanted data".

1. Inserting data

The INSERT statement can be used to insert one or more rows of data into a database table with the following syntax:

Insert into table name ([column name 1, column Name 2, ...] ) VALUES (value 1, value 2, ...)

2. Modify the data

The UPDATE statement is used to modify the records of a data table, each time you can modify multiple records, by using the WHERE clause to limit which records to modify, the syntax is as follows:

Update table name set column name = new value where update condition

3. Delete data

The DELETE statement is used to delete data from a table with the following syntax:

Delete from table name where delete condition

Select query statement

The SELECT statement is the most versatile statement in the SQL statement, and the SELECT statement can perform single-table queries, subqueries, and multi-table join queries.

1. Single-table query

The syntax format for a SELECT statement for a single-table query is as follows:

Select column 1, column 2 ... from table name where query condition

2, sub-query

A subquery is the nesting of another query in a query statement with the following syntax:

Select column 1 ... from table name where column name [<, in] subquery

There are two tables, student (record the student's school number, name and other information), Grade (record the student's school number, the course result), inquires the student's name which corresponds to the Grade table secondary School Number:

Select name from student where Sno in (select Sno from grade)

3. Multi-Table Connection query

Many times, the data that needs to be selected is not from a table, but from multiple tables, which requires a multi-table join query with the following syntax:

Select column 1, column 2 ... from table 1, table 2 ... where join condition

There are three tables, student (record the student's school number, name and other information), course (record the course number, course name), Grade (record number, course number, course score), check the student's name, course name and the results for the course:

Select S.name, C.name, g.grade from student S,course C,grade g where S.sno = G.sno and C.cno = G.cno

4. ORDER BY order

In MySQL, you can use the ORDER BY statement to specify which field to sort by, and then return the search results, with the following syntax:

Select Column Name 1 ... from table name order by column name [Asc,desc]

5. GROUP BY group

The so-called grouping is to divide a data table into a number of small areas, and then for a number of small areas for data processing, the specific syntax is as follows:

Select group field, aggregate function from table name Group by Group Field [having filter condition]

There is a table grade, records the examinee number, the course number, the course result, according to the examinee number group, inquires each examinee's course total score:

Select Sno,sum (grade) from grade group by Sno Order by sum (grade) ASC

MySQL Transaction

A transaction is a contiguous set of database operations, a batch of SQL statements that are indivisible, either executed, or rolled back.

Why use the technology of transactions? Now many of the software are multi-user, multi-program, multi-threaded, for the same table may have a lot of people in use, in order to maintain the consistency of data, so the concept of a transaction. This is very abstract, for example: A to B transfer money, A's account-1000 yuan, B's account will be + 1000 yuan, these two update statements and need to be implemented as a whole, otherwise a deduction of money, B did not add money this situation is difficult to deal with.

1, the characteristics of the transaction

Transactions have the following four standard properties, abbreviated acid, commonly referred to as:

atomicity (Atomic): Ensures that all operations within the unit of work are completed successfully, otherwise the transaction will be aborted at the point of failure, and the previous operation will be rolled back to the previous state;

Consistency (consistent): Ensure that the database is successfully committed after it has changed state correctly;

Isolation (Isolated): Make transactional operations independent and transparent to each other;

Persistence (Durable): ensures that the result of the committed transaction or the effect of the system is still present in the event of a failure.

2, the use of the transaction

The use of begin, rollback, Commit, savepoint in MySQL is used for transactional processing:

begin: Start a transaction

rollback: Transaction rollback

commit: Transaction commit

savepoint: Creating a savepoint in a transaction

Take the table grade (record student number, course number, course score) as an example to illustrate the use of transactions:

Begin

Insert into grade values (103,01,88.8);

SELECT * from grade;

SavePoint first;

Insert into grade values (103,02,90);

SELECT * from grade;

Rollback to first;

SELECT * from grade;

Commit

MySQL stored procedures

The SQL statements that we commonly use to manipulate database languages need to be compiled and executed at the time of execution, while stored procedures (Stored Procedure) are sets of SQL statements that are compiled for specific functions and stored in the database after compilation. The user invokes execution by specifying the name of the stored procedure and the given parameter (if the stored procedure has parameters).

1, the advantages of stored procedures

Stored procedures often have the following advantages:

• Stored procedures enhance the functionality and flexibility of the SQL language. Stored procedures can be written with flow control statements, with a strong flexibility to complete complex judgments and more complex operations.
• Stored procedures allow standard components to be programmed. After a stored procedure is created, it can be called multiple times in the program without having to rewrite the SQL statement for the stored procedure. and database professionals can modify stored procedures at any time, without affecting the source code of the application.
• Stored procedures can achieve a faster execution speed. If an operation contains a large number of Transaction-sql code or is executed more than once, the stored procedure is much faster than the batch execution. Because the stored procedure is precompiled. When you run a stored procedure for the first time, the optimizer optimizes it for analysis and gives the execution plan that is ultimately stored in the system table. The batch TRANSACTION-SQL statements are compiled and optimized each time they are run, relatively slowly.
• Stored procedures can reduce network traffic. For operations on the same database object, such as queries, modifications, if the TRANSACTION-SQL statement involved in this operation is an organized stored procedure, when the stored procedure is called on the client computer, only the calling statement is transmitted on the network, which greatly increases network traffic and reduces network load.
• Stored procedures can be used as a security mechanism to make full use of them. The system administrator restricts the access to the corresponding data by executing the permission of a stored procedure, avoids the unauthorized user's access to the data, and ensures the security of the data.

2. Create, delete, and use stored procedures

The MySQL stored procedure is created in the following format:

CREATE PROCEDURE procedure name ([process parameters] [parameter name] [parametric type] ... ) Process Body

The format of the delete stored procedure is as follows:

drop procedure Procedure Name

The command to invoke a stored procedure is simple, in the following format:

Call Procedure Name (parameter 1, parameter 2 ...) ）

The following is a simple example that describes the creation and use of stored procedures:

Create a stored procedure

Use && to replace; as a delimiter

Delimiter &&

CREATE PROCEDURE test (in input int)

Begin

Select input;

Set input = 2;

Select input;

end;&&

Using Stored Procedures

Declare the delimiter as the default;

delimiter;

Call Test (3);

MySQL Workbench

Although we can execute the MySQL statement at the command prompt through a line of input or through a redirected file, this is inefficient, because there is no automatic check of the syntax before execution, and the likelihood of some errors caused by input errors is greatly increased, so try some visual MySQL database management tools , MySQL Workbench is a visual management tool that MySQL officially provides to MySQL, where you can directly manage the contents of the database in a visual way, and the SQL Script Editor of MySQL Workbench supports syntax highlighting and input language The law examines, of course, its powerful, not limited to these two points.

MySQL Workbench Official Description: http://www.mysql.com/products/workbench/

MySQL Workbench download page: http://dev.mysql.com/downloads/tools/workbench/

MySQL Usage Summary