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

Editor to share with you the example analysis of the MySQL command, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

The basic operation of MySQL can include two aspects: MySQL commonly used statements such as add, delete, modify and query (CRUD) statements and MySQL advanced functions, such as stored procedures, triggers, transactions and so on. These two aspects can be subdivided as follows:

Common sentences in MySQL

CRUD of the table (or database)

The CRUD of table data, in which table data queries are the most frequently used and more complex. Queries can be divided into single-table or multi-table SELECT queries and multi-table join queries (INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN) and combined queries UNION and UNION ALL

The execution order of the keywords in the SQL statement

Advanced features of MySQL

Stored procedure

Transaction processing

Trigger

1. Table (or database) operation statement

1.1. Query table (or database)

Get all available databases: SHOW DATABASES

Select database: USE customers

Used to display status information of the database server: SHOW STATUS

Used to display the security permissions of authorized users: SHOW GRANTS

Used to display database servers or warning messages: SHOW ERRORS or SHOW WARNINGS

Used to display the creation statement when creating the database: SHOW CREATE DATABASE customers

Used to display the creation statement when the table was created: SHOW CREATE TABLE customers

Get all available tables in the currently selected database: SHOW TABLES

Get information about all the columns in the table: SHOW COLUMNS FROM tableName; and DESCRIBE statements have the same effect: DESCRIBE tableName

1.2. Create a new table / database

Create a new database: CREATE DATABASE customers

You can use the CREATE TABLE statement to create a table:

Here are some details:

Null value is allowed, indicating that the column value is allowed not to be given when inserting row data, while NOT NULL means that the column value must be explicitly given when inserting or updating the column data.

DEFAULT represents the default value of the column. When inserting row data, the default value specified by the column will be used if the value of the column is not given.

PRIMARY KEY is used to specify a primary key, which can specify a column of data and can be composed of multiple columns of data, such as PRIMARY KEY (cust_id,cust_name).

ENGINE is used to specify the engine type. The common engine types are: (1) InnoDB is an engine that supports reliable transaction processing, but does not support full-text search; (2) MyISAM is a high-performance engine that supports full-text search but does not support transaction processing; (3) MEMORY is functionally equivalent to MyISAM, but because the data is stored in memory, it is fast (especially suitable for temporary tables)

You can use FOREIGN KEY to create foreign keys when you create a table, where the FOREIGN KEY in one table points to the PRIMARY KEY in another table. The foreign key FOREIGN KEY is used to constrain the join action of breaking tables to ensure the data integrity of the two tables. It also prevents illegal data from being inserted into a foreign key column because the column value must point to the primary key of another table. Examples are:

1.3 Delete a table (or database)

Delete database: DROP DATABASE customers

Delete the table, using the DROP TABLE clause: DROP TABLE customers.

1.4 update table

Table structure information can be updated using the ALTER TABLE clause, such as adding a column to the table: ALTER TABLE vendors ADD vend_name CHAR (20). It is also often used to define foreign keys, such as:

Rename the table and use the rename clause. RENAME TABLE backup_customers TO customers, backup_vendors TO vendors; change multiple table names, separated by commas

2 table data operation statement

2.1 query table data

Basic query statement

Query the information of one or more columns or all columns in the table according to the filter condition SELECT FROM WEHERE:SELECT cust_id,cust_name FROM customers WHERE cust_id=10086;, where the filter condition operators are: =,! =, =, BETWEEN AND,IS NULL

Remove the DISTINCT:SELECT DISTINCT cust_name FROM customers for a column of information that is queried

Limit the number of rows of single-column query results: SELECT cust_name FROM customers LIMIT 5 position limit is followed by a value indicating that 5 rows of data are fetched from row 0; if LIMIT 5 represents 5 rows of data starting from row 5 (the actual row 6 record in the database), a total of 5 rows of data are fetched. Note: the data is counted from line 0

The ORDER BY clause takes one or more columns and sorts the output accordingly: SELECT cust_id,cust_name FROM customers ORDER BY cust_id DESC, cust_name

The IN operator is used to specify a range of conditions in which each condition can be matched: SELECT cust_id, cust_name FROM customers WHERE cust_id IN (1000 cust_name FROM customers WHERE cust_id IN 2000). In addition, the NOT operator can be used in conjunction with the IN operator to indicate that all data that does not meet the criteria have been retrieved

The LIKE operator is used to indicate a fuzzy query, and the wildcard characters used in conjunction with it are * *% * *, which indicates that any character can appear any number of times; _, _ indicates that only one character can be matched: SELECT cust_id,cust_name FROM customers WHERE cust_name LIKE'% happy%'

Use grouping query and can meet certain grouping filtering conditions GROUP BY HAVING. If you retrieve the order number and the total order amount for which the total order amount is greater than or equal to 50, and sort by the total amount: SELECT order_num,SUM (quantity*item_price) AS order_total FROM orderitems GROUP BY order_num HAVING SUM (quantity*item_price) > = 50 ORDER BY order_total

The comparison between WHERE and HAVING. WHERE is row-level filtering, while HAVING is group-level filtering. Data filtered by WHERE does not appear in the packet. The connection of wildcards and multiple WHERE clauses in WHERE also applies to HAVING clauses.

Notes for the use of GROUP BY: (1) grouping can be nested in the GROUP BY clause (that is, grouping GROUP BY cust_id, cust_name by multiple columns), but data aggregation is carried out on the last specified grouping; (2) each column listed in the GROUP BY clause must be a retrieval column or a valid expression. (3) if there is a null value, the value NULL is returned as a grouping. If there are multiple rows of null values, they will be divided into a group.

A query that is nested in other queries is called a subquery. The execution process is from inside to outside, and the result of the inner query is used as the condition of the outer query: SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'happy'). Of course, multi-table queries can be joined queries.

Join query

Inner joins, also known as internal joins, are based on equality tests between two tables. If no filtering condition is added, it will result in "Cartesian product". SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id=products.vend_id; can also use WHERE for multi-table join queries, but it is more recommended to use join methods such as INNER JOIN.

External connections include left outer connection LEFT JOIN, right outer connection RIGHT JOIN and full connection FULL JOIN. For example, query the number of orders for each customer: SELECT customers.cust_id,orders.orders_num FROM customers LEFT JOIN orders ON orders.cust_id = customers.cust_id;LEFT JOIN will return all the left table data, RIGHT JOIN will return all the right table data, and FULL JOIN will return all the data of the left and right tables.

Join queries are used with aggregate functions. For example, query the number of orders per customer: SELECT customers.cust_name,customers.cust_id,COUNT (orders.order_num) AS num_ord FROM customers INNER JOIN orders ON customers.cust_id=orders.cust_id GROUP BY customers.cust_id

Combined query

Multiple queries (SELECT) can use UNION to merge multiple query results into a result set to return, UNION must contain two or more SELECT queries, and each pass must contain the same column, expression or aggregation function, the data type does not have to be exactly the same, MySQL will carry out implicit type conversion. SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price > 5 UINON SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001meme 1002)

UNION returns the deduplicated result. If you don't need to deduplicate, you can use UNION ALL.

You can sort multiple composite queries using ORDER BY, but sort against the final result set of, rather than a single SELECT query, so there is only one ORDER BY clause for a combined query. SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price > 5 UINON SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001m 1002) ORDER BY vend_id

Use functions to process data

Stitching column name: SELECT Concat (vendName,' (', vendCountry,')') FROM vendors ORDER BY vendName

Perform arithmetic expression evaluation: SELECT prodId, quantity,price, quantity*price AS expandedPrice FROM orderItems

Text processing functions such as Upper (), LTrim (), RTrim (), etc. For example, use the Upper function to convert text to uppercase: SELECT vendName, Upper (vendName) FROM vendors ORDER BY vendName

Time and date processing functions, such as Date (), Day (), etc. SELECT custId, orderNum FROM orders WHERE Date (orderDate) = '2015-09-01'

Numeric processing functions, such as Abs (), Cos (), etc.

Commonly used aggregation functions. Such as AVG (), COUNT (), MAX (), MIN (), and SUM (). SELECT COUNT (*) AS numbers, MIN (prod_price) AS price_min, MAX (prod_price) AS price_max,AVG (prod_price) AS price_avg FROM products

2.2 insert table data

You can use the INSERT INTO clause to insert row data into a table, and it is safer to specify a column name. INSERT INTO customers (cust_name, cust_email) VALUES ('happy','happy@gmail.com'); the prerequisite for omitting a column name in the INSERT INTO clause is that the column can be defined as null or a default value is given when the column is defined

If you insert multiple rows of data, you can separate multiple sets of values with commas. INSERT INTO customers (cust_name, cust_email) VALUES ('happy','happy@gmail.com'), (' smart','smart@gmail.com')

Insert the queried data into the table, you can use the INSERT SELECT statement. INSERT INTO customers (cust_id,cust_contact) SELECT cust_id,cust_contact FROM customers WHERE cust_id > 5; where SELECT can have WHERE filter conditions; INSERT SELECT is usually used to replicate table data

2.3 updating table data

If you want to update the table data, use the UPDATE clause: UPDATE customers SET cust_name = 'happy',cust_email='happy@gmail.com' WHERE cust_id = 1001

Note: if you specify a row without a WHERE condition, all the data in a column in the table will be updated.

2.4 deleting table data

If you delete data from a table, you can use the delete clause. DELETE FROM customers WHERE cust_id = 10086; deleted data must be row data in the table, not a column. Therefore, compared with the UPDATE clause, the DELETE clause does not need to specify which column, just a specific table name

Note: if you do not add the WHERE specified condition, all row data in the entire table will be deleted. In addition, DELETE only deletes the data in the table, not the table structure information

If you want to delete all the data in the table, you can use TRUNCATE, which is more efficient than DELETE.

3. Keyword execution order in SQL

In the SQL statement, each keyword is executed sequentially, and at each step, a virtual table is generated, and the resulting virtual table is returned as the final result of execution. The following is the order in which common keywords are executed:

FROM: calculates the Cartesian product of the table on the left and the table on the right of FROM to generate a virtual table VT1

ON: perform ON filtering on the virtual table VT1. Only those rows that meet the criteria will be recorded in the virtual table VT2.

JOIN: if OUT JOIN, add the unmatched rows in the reserved table (such as the left table or the right table) to the virtual table VT2 as external rows, resulting in the virtual table VT3

WHERE: WHERE conditional filtering is performed on the virtual table VT3. Only the records that match will be put into the virtual table VT4.

GROUP BY: group the virtual table VT4 according to the columns in the GROUP BY clause to generate the virtual table VT5

CUBE | ROLLUP: perform CUBE or ROLLUP operations on virtual table VT5 to generate virtual table VT6

HAVING: perform HAVING conditional filtering on the virtual table VT6. Only the matching records will be inserted into the virtual table VT7.

SELECT: perform the SELECT operation, select the specified column, and insert it into the virtual table VT8

DISTINCT: deduplicates the records in the virtual table VT8 to generate the virtual table VT9

ORDER BY: sorts the records in the virtual table VT9 to generate the virtual table VT10

LIMIT: fetch the record of the specified row, generate the virtual table VT11, and return the result.

4. Indexes

The establishment of MySQL index is very important for the efficient operation of MySQL, and the 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, while a combined index, that is, an index, contains multiple columns.

4.1 create an index

There are two ways to create an index, one is to create it directly using CREATE INDEX, and the other is to add it by modifying the table structure, using the ALTER TABLE statement.

Use CREATE INDEX

The syntax is:

The corresponding syntax variable information is as follows:

[UNIQUE | FULLTEXT | SPATIAL]

The three keywords in parentheses represent the type of index created, and they represent three different index types: unique index, full-text index and spatial index. If we do not specify any keywords, the default is a normal index.

Index_name

Index_name represents the name of the index and is defined by the user so that administrative operations such as modifications to the index can be carried out later.

Index_type

Index_type represents the specific implementation of the index. In MySQL, there are two different forms of index-BTREE index and HASH index. Only BTREE can be used in tables whose storage engines are MyISAM and InnoDB. The default value is that BTREE; can use two types of indexes, HASH and BTREE, in tables with storage engine MEMORY or HEAP, and its default value is HASH.

Index_colname

Index_col_name represents the name of the field that needs to be indexed, and we can also create a composite index for multiple fields by separating the field names with commas in English. In addition, for fields of type CHAR or VARCHAR, we can only use the front part of the field content to create the index. We only need to add an instruction like (length) after the corresponding field name, which means that we only need to use the length characters in front of the field content to create the index. Here, we take the cust_name field of the customers table (type VARCHAR (50)) as an example, using the six-character prefix of the cust_name field to create the index.

Use ALTER TABLE

The syntax is:

4.2 Delete the index

Deletes the index with the specified name in the specified table, the syntax is:

For example, to delete an index named idx_cust_name, the SQL statement is:

4.3 modify the index

There is no direct instruction to modify the index in MySQL. In general, we need to delete the original index first, and then create an index with the same name as needed, so as to modify the index in disguise.

4.4 View Index

In MySQL, it is also very simple to view an index in a database table by using either of the following two commands.

5. Stored procedure

What is a stored procedure? Simply put, a stored procedure is a collection of one or more MySQL statements saved for reusability or the implementation of complex business functions, which can be regarded as batch files

Why use stored procedures? (1) simplify complex operations by encapsulating processing in easy-to-use units; (2) ensure data integrity because a series of processing steps are not required to be established repeatedly; if all developers and applications use the same stored procedure, the code is the same; (3) simplify the management of changes. If the table name, column name, or business logic changes, only need to change the code of the stored procedure, developers who use it do not even need to know these changes, that is, security; (4) improve performance, because the use of stored procedures is faster than using SQL statements alone; (5) stored procedures can be used to write code that is more flexible. Therefore, stored procedures have three characteristics: simple reuse, security and high performance.

What are the disadvantages of stored procedures? (1) the programming of stored procedures is more complex than basic SQL statements and requires higher skills; (2) there may be no permission to create stored procedures, and database administrators may restrict the permission to create stored procedures, allowing users to use stored procedures instead of allowing users to create stored procedures freely.

Create a stored procedure

Create a stored procedure. If you need to count the total amount of the user's order, if the user needs to pay tax, the total amount of the order will need to be added with taxes and fees.

There are some details: 1. Use the CREATE PROCEDURE statement to create. () parentheses are the parameters of the stored procedure, where the parameter types are: 1.IN type, which indicates the result passed to the stored procedure; 2.OUT type, which indicates the result returned by the stored procedure, which needs to be passed in the variable starting with @ when calling the stored procedure; 3.INOUT type, which indicates that it can be passed in and out in the stored procedure; and 2.DECLARE is used to declare a variable, such as total,taxrate here. Note that variables are defined in MySQL with the variable name first and the data type last. 3. The specific logic of the stored procedure is written between BEGIN END; 4. Assign a value to a variable using the INTO keyword; 5. Because it is used as a delimiter in each SQL statement in a stored procedure, it conflicts with a single SQL, so you can use DELIMITER to redefine the classifier. If / / is defined as the delimiter in this example, the natural stored procedure ends with END / / instead of END. At the same time, when the delimiter / / appears in pairs, revert to the default ";" as the delimiter

Execute stored procedure

The stored procedure is executed using the call clause, and the CALL clause accepts the name of the stored procedure and the parameters to be passed.

If an input parameter of type OUT is defined in the stored procedure, you need to pass in variables, such as @ total, when executing the stored procedure, and the variables start with @. If there are no parameters in the stored procedure, it can be indicated by empty parentheses, CALL ordertotal ()

Delete stored procedure

To delete stored procedures, you can use the DROP PROCEDURE clause. Such as DROP PROCEDURE ordertotal

Query stored procedure

Displays the statement to create a stored procedure, which can be used with SHOW CREATE PROCEDURE. Such as SHOW CREATE PROCEDURE ordertotal

Query the status of all stored procedures, which can be viewed if you use COMMENT to add comments in defining stored procedures. At the same time, the results can be filtered by LIKE. Such as SHOW PROCEDURE STATUS LIKE'% order%'

6. Transaction processing

What is a transaction?

Transaction processing is used to maintain the integrity of the database, which ensures that batches of MySQL operations are either fully executed or not executed at all. Transactions are a mechanism for managing MySQL operations that must be executed in batches, either as a whole or not at all.

Key concepts:

Transaction: refers to a set of SQL statements

Fallback: the process of undoing a specified SQL statement

Submit: refers to writing the results of an unstored SQL statement to a database table

Retention point: a temporary placeholder set in a transaction to which you can publish a fallback

How do I create an execution transaction?

The result of the execution is that the insert data ('1 SAFEPOINT 5, 18) is valid, because the fallback only starts after the retention point SAFEPOINT, that is, the result of the execution of the SQL statement before the retention point SAFEPOINT is still valid.

Here are some details:

STAET TRANSACTION is used to indicate that the following set of SQL statements is a transaction

SAFEPOINT is used to specify the retention point insertinto

ROLLBACK TO means to fall back from the specified retention point, that is, the execution result of the SQL statement before the reservation point is still valid. If you only use ROLLBACK for fallback, it means that all SQL statements executed since STAET TRANSACTION will be undone.

The MySQL commit (write or save) operation is automatic, which is called an implied commit. However, in a transaction block, the commit is not implied and is committed using the COMMIT clause. Such as:

Using COMMIT to commit the transaction, if both SQL statements are executed successfully, the data will be written to the table.

7. Trigger

What is a trigger?

When a SQL statement occurs, triggers are needed to automatically execute some other SQL statements. Triggers can only respond to these three specific actions: DELETE,INSERT,UPDATE.

Create a trigger?

Four most important pieces of information need to be given when creating a trigger: 1. Globally unique trigger name; 2. Table associated with trigger; 3. When the trigger is executed (before or after the operation is executed) 4. The activity (DELETE, INSERT, or UPDATE) that the trigger should respond to

Because triggers can only respond to specific three types of actions, there are only three types of triggers that can be created: INSERT, DELETE, and UPDATE triggers.

INSERT trigger

When executing INSERT triggers, we should also pay attention to the following points: 1. In the INSERT trigger code, you can reference a virtual table named NEW, and you can use NEW to access the row data you just inserted. 2. In BEFORE INSERT triggers, the values in NEW can be updated; 3. For the AUTO_INCREMENT column, NEW contains 0 before INSERT execution and a new custom generated value after INSERT execution.

Create an INSERT trigger that inserts a row of data each time and returns the id of the currently inserted row of data.

Here are some details:

Use CREATE TRIGGER to create triggers

AFTER INSERT indicates that the trigger does not perform a feature operation until the row data is inserted

FOR EACH ROW indicates that the trigger works for each row of data inserted

For INSERT triggers, you can use the virtual table NEW to use the row data you just inserted. For example, SELECT NEW.cust_id INTO @ newinsertid means to assign the id of the newly inserted row data to the variable @ newinsertid

DELETE trigger

DELETE triggers need to know the following two things before or after the execution of DELETE statements:

Within the DELETE trigger code, a virtual table named OLD can be referenced to access the deleted row

The data in the OLD table can only be read and cannot be updated, while in the INSERT trigger, the inserted row data can be updated through NEW.

For example, for the customers table, when a row of data is deleted, the cust_id and cust_name of the deleted data are returned:

Basically the same as creating INSERT triggers, except that only OLD can be used in DELETE triggers to access deleted row data.

UPDATE trigger

UPDATE triggers are executed before or after the UPDATE statement is executed, and you need to know the following:

NEW and OLD can be used to access data in BEFORE UPDATE triggers, while using NEW to access data in AFTER UPDATE triggers will report an error, and only OLD can be used to access data.

In BEFORE UPDATE triggers, the value in NEW can be changed, that is, the data to be used for UPDATE is allowed to be changed

Row data in OLD can only be read and cannot be updated

An example of an UPDATE trigger is as follows:

, the output is @ beforeupdate is' happay', and @ afterupdate is' reset_name'. Here are some details:

The data in the NEW virtual table can be changed, for example, using SET NEW.cust_name = 'reset_name';, to change the cust_name to be updated from "happy" to "reset_name"

You can use NEW and OLD to access data in BEFORE UPDATE triggers, while using NEW to access data in AFTER UPDATE triggers will report an error

Delete trigger?

To delete a trigger, you can use a DROP TRIGGER statement, such as DROP TRIGGER insertcustomers;. Triggers cannot be updated or overwritten. If you want to modify a trigger, you must delete it.

The above is all the contents of the article "sample Analysis of MySQL commands". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.