Summary of common MySQL operation networks in Linux

1. query number & amp; 20540; type data: SELECT * FROMtb_nameWHEREsum & gt; 10 1. query numeric data:
SELECT * FROM tb_name WHERE sum> 100;
Query predicate:>, =, <, <> ,! =,!> ,! <, =>, = <

2. query strings
SELECT * FROM tb_stu WHERE sname = 'Liu'
SELECT * FROM tb_stu WHERE sname like 'Liu %'
SELECT * FROM tb_stu WHERE sname like '% programmer'
SELECT * FROM tb_stu WHERE sname like '% PHP %'

3. query date data
SELECT * FROM tb_stu WHERE date = '2017-04-08'
Note: different databases have different types of date data ::
(1) MySQL: SELECT * from tb_name WHERE birthday = '2017-04-08'
(2) SQL Server: SELECT * from tb_name WHERE birthday = '2017-04-08'
(3) Access: SELECT * from tb_name WHERE birthday = #2011-04-08 #

4. query logical data
SELECT * FROM tb_name WHERE type ='t'
SELECT * FROM tb_name WHERE type = 'F'
Logical Operator: and or not

5. query non-empty data
SELECT * FROM tb_name WHERE address <> ''Order by addtime desc
Note: <> equivalent to PHP! =

6. querying numeric data using variables
SELECT * FROM tb_name WHERE id = '$ _ POST [text]'
Note: When using variables to query data, the input SQL variables do not need to be enclosed in quotation marks, because when the strings in PHP are connected to the numeric data, the program will automatically convert the numeric data into a string, connect to the string to be connected.

7. query string data using variables
SELECT * FROM tb_name WHERE name LIKE '% $ _ POST [name] %'
The exact match "%" indicates that the method can appear anywhere.

8. query the first n Records
SELECT * FROM tb_name LIMIT 0, $ N;
When the limit statement is used together with other statements, such as order by statements, SQL statements are constantly changing, making the program very flexible.

9. n records after Query
SELECT * FROM tb_stu order by id asc limit $ n

10. query n records starting from the specified position
SELECT * FROM tb_stu order by id asc limit $ _ POST [begin], $ n
Note: The data id starts from 0.

11. query the first n records in the statistical result
SELECT *, (yw + sx + wy) AS total FROM tb_score order by (yw + sx + wy) desc limit 0, $ num

12. query data in a specified time period
SELECT the field to be searched FROM table name WHERE field name BETWEEN initial value AND termination value
SELECT * FROM tb_stu WHERE age BETWEEN 0 AND 18

13. query statistics by month
SELECT * FROM tb_stu WHERE month (date) = '$ _ POST [date] 'Order BY date;
Note: the following functions are provided in the SQL language. you can use these functions to conveniently query data by year, month, or day.
Year (data): returns the value corresponding to the year in the data expression.
Month (data): returns the value corresponding to the month in the data expression.
Day (data): returns the value corresponding to the date in the data expression.

14. query records greater than the specified conditions
SELECT * FROM tb_stu WHERE age >$ _ POST [age] order by age;

Record duplication is not displayed in the query result
Select distinct field name FROM table name WHERE query condition
Note: the DISTINCT in the SQL statement must be used together with the WHERE clause. Otherwise, the output information will not change and the field cannot be replaced *.

Sixteen NOT statements and predicates are used to query the combination conditions.
(1) not berween... AND... When querying data between the start value and the end value, you can change it <起始值 and> End value
(2) is not null queries non-NULL values.
(3) is null queries NULL values.
(4) not in this formula specifies the search expression based on whether the keyword is included IN the list or excluded from the list. The search expression can be a constant or column name, the column name can be a group of constants, but more often it is a subquery.

17. display repeated records and records in the data table
SELECT name, age, count (*), age FROM tb_stu WHERE age = '19' group by date

18. perform descending/ascending queries on data
SELECT field name FROM tb_stu WHERE condition order by field DESC descending ORDER
SELECT field name FROM tb_stu WHERE condition order by field ASC ascending
Note: If you do not specify the sorting method when sorting fields, the default value is ASC ascending.

Perform multi-condition query on data
SELECT field name FROM tb_stu WHERE condition order by field 1 ASC field 2 DESC...
Note: The multi-condition sorting of query information aims to limit the output of records. Generally, the output effect is different because it is not a single condition.

Sort the statistical results in 20 pairs
The SUM function ([ALL] field name) or SUM ([DISTINCT] field name) can SUM the fields. if the function is ALL, ALL records of the field are summed, if DISTINCT is used, the sum of all fields of this field that do not record repeatedly is used.
For example, SELECT name, SUM (price) AS sumprice FROM tb_price group by name

SELECT * FROM tb_name order by mount DESC, price ASC

21. grouping statistics for a single column
SELECT id, name, SUM (price) AS title, date FROM tb_price group by pid order by title DESC
Note: When the group statement group by sorting statement order by appears in the SQL statement at the same time, you must write the grouping statement before the sorting statement. Otherwise, an error occurs.

Group statistics for more than 22 columns
Grouping statistics for multiple columns is similar to grouping statistics for a single column
SELECT *, SUM (Field 1 * field 2) AS (New Field 1) FROM table name group by field order by new field 1 DESC
SELECT id, name, SUM (price * num) AS sumprice FROM tb_price group by pid order by sumprice DESC
Note: the group by statement is generally followed by a series of non-aggregate functions, that is, it is not the column to be grouped.

Group statistics for 23 + tables

SELECT. name, AVG (. price), B. name, AVG (B. price) FROM tb_demo058 AS a, tb_demo058_1 AS B WHERE. id = B. id group by B. type;

Index operations

 

How to create an index· Indexes are usually used to increase the search speed of data rows matching other tables in the WHERE condition or performing join operations. Therefore, we usually select columns that appear in the WHERE clause, join clause, ORDERBY, or group by clause as index columns. · When selecting an index, you must consider the base of the data column. The base number refers to the number of different data contained in the index data column. If the base number is higher than the number of rows in the data table, the indexing effect is better. In other words, the more different values of the index data column, the better the index effect. If a data column contains only two values: 0 or 1, the index is of little use. If the probability of a value appearing is almost equal, half of the data rows may be obtained no matter which value is searched. In these cases, it is best not to use indexes at all. · If the index value is short, the selected data type should be as small as possible. For example, if TEXT can meet the requirements, we do not need to use MEDIUTEXT. · If a joint index is created, for example, for t1, t2, and t3, an index is also created for t1, t1, and t2. However, if the values of t2, t3, t1t3, and t2t3 are specified separately, no index will be used.
How to avoid the absence of indexes in SQL statementsFirst, let's take a look at the working principle of the mysql Optimizer: The main goal of the MySQL Query optimizer is to use the index as much as possible and use the strictest index to eliminate as many data rows as possible. Therefore, when we submit a query statement, the faster the optimizer can exclude non-conforming data, the faster the query result. · Try to compare columns with the same data type. For example, INT and BIGINT are different. CHAR (10) is considered as CHAR (10) or VARCHAR (10), but is different from CHAR (12) or VARCHAR (12. · Try not to use expressions or functions for index columns in the where clause. If you use function calls or more complex arithmetic expressions in the index column, MySQL does not use the index because it must calculate the expression value of each data row. WHERE mycol <4/2 use index
WHERE mycol * 2 <4 does not use an index. when using LIKE, do not use wildcards at the beginning. WHEREcol_name LIKE '% string %' WHEREcol_name LIKE 'ABC % '· Do not use type conversion. If an index column is of the int type and the value is of the limit type during query, no index can be used. SELECT * FROM mytbl WHERE num_col = 1; use index
SELECT * FROM mytbl WHERE num_col = '1'; no index is used. you can use the STRAIGHT_JOIN keyword in the SELECT statement to reload the optimizer selection. SELECTSTRAIGHT_JOIN... FROM t1, t2, t3 ...;
SELECT... FROM t1 STRAIGHT_JOIN t2 STRAIGHT_JOIN t3... View

A view is a table exported from one or more basic tables (or views. Unlike the basic table, it is a virtual table. The database only stores View definitions, but does not store view data. The data is still stored in the original basic table. Therefore, the data in the basic table changes, and the data queried from the view changes accordingly. In this sense, a view is like a window through which you can see the data that you are interested in and its changes in the database.
Since the View definition is based on the basic table, why should we define the view? This is because the rational use of views can bring many benefits:
1. View can simplify user operations
The view mechanism allows users to focus on the data of interest. If the data is not directly from the basic table, you can define the view to make the database look simple and clear, and simplify the user's data query operations. For example, the views that define several table connections hide the join operation between tables. In other words, the user only performs simple queries on a virtual table, but does not need to know how the virtual table is obtained.
2. Views allow users to view the same data from multiple perspectives
The view mechanism allows different users to view the same data in different ways. this flexibility is necessary when many users of different types share the same database.
3. Views provide a certain degree of logical independence for restructured databases
The physical independence of data means that your applications do not depend on the physical structure of the database. The logical independence of data means that when the database is re-constructed, your applications will not be affected if a new link or a new field is added to the original link. Hierarchical databases and network databases generally support physical independence of data, while logical independence cannot be fully supported.
In the case of relational databases, the re-construction of databases is often inevitable. The most common way to reconstruct a database is to vertically divide a basic table into multiple basic tables. For example, Student (Sno, Sname, Ssex, Sage, Sdept ),
There are two relationships: SX (Sno, Sname, Sage) and SY (Sno, Ssex, Sdept. The Student of the original table is the result of the natural connection between the SX table and the SY table. If you create a view Student:
Create view Student (Sno, Sname, Ssex, Sage, Sdept)
AS
Select sx. Sno, SX. Sname, SY. Ssex, SX. Sage, SY. Sdept
From sx, SY
Where sx. Sno = SY. Sno;
In this way, although the logical structure of the database has changed (to SX and SY tables), the application does not need to be modified because the newly created view is defined as the user's original relationship, keep the user's external mode unchanged, and the user's application can still search for data through the view.
Of course, a view can only provide logical independence of data to a certain extent. for example, a view update is conditional, therefore, the statement for modifying data in the application may still change due to the change in the basic table structure.
4. View provides security protection for confidential data
With the view mechanism, you can define different views for different users when designing a database application system, so that confidential data does not appear in the user view that should not be seen. In this way, the view mechanism automatically provides security protection for confidential data. For example, if the Student table involves the Student data of 15 departments in the school, 15 views can be defined on it. each view only contains the Student data of one department, only the directors of each department can query and modify the student view of the original department.
5. Use a view to clearly express the query
For example, you often need to execute this query to "find the course number for each student for which he has obtained the highest score ". You can first define a view to obtain the highest score of each student:
CREATE VIEW VMGRADE
AS
SELECT Sno, MAX (Grade) Mgrade
FROM SC
Group by Sno;
Then, use the following query statement to complete the query:
Select SC. Sno, Cno from SC, vmgrade where SC. Sno = VMGRADE. Sno and SC. Grade = VMGRADE. Mgrade;

Stored Procedure

Is a set of SQL statements for specific functions. it is a program written in the Transact-SQL language provided by SQL Server. After compilation, it is stored in the database and executed on the MySQL server, which can reduce data transmission between the client and the server. A stored procedure is an important object in a database. you can specify the name of a stored procedure and provide parameters (if the stored procedure has parameters) to execute it. A stored procedure is a process written by flow control and SQL statements. the procedure is compiled and optimized and stored on the database server. the stored procedure can be executed by an application through a single call, you can also declare variables. At the same time, stored procedures can receive and output parameters, return the status values for executing stored procedures, or nested calls.

Why use stored procedures:

Ü stored procedures are already certified technologies!

Ü the stored procedure will make the system run faster!

Ü stored procedures are reusable components! It is the database logic rather than the application.

Ü the stored procedure will be saved!

Advantages of stored procedures:

? The stored procedure is compiled only when it is created. you do not need to re-compile the stored procedure every time you execute it. Generally, the SQL statement is compiled every time it is executed, therefore, using stored procedures can speed up database execution.

? When complex operations are performed on databases (for example, when multiple tables are updated, inserted, queried, or deleted ), this complex operation can be encapsulated in a stored procedure and used together with the transaction processing provided by the database.

? Stored procedures can be reused to reduce the workload of database developers.

? High security. you can set that only one user has the right to use the specified stored procedure.

1. create a stored procedure

CREATE PROCEDURE
Sp_name ([proc_parameter [,...])
[Characteristic...] routine_body

Each parameter in proc_parameter consists of three parts.
The three parts are the input/output type, parameter name, and parameter type, in the following form:
[IN | OUT | INOUT] param_name type CREATE PROCEDURE
Food_price_count (IN price_info1FLOAT, IN price_info2FLOAT, OUT count INT)
READS SQL DATA
BEGIN
DECLARE temp FLOAT;
DECLARE match_price cursor for select price FROM food;
Declare exit handler for not found close match_price;
SET @ sum = 0;
Select count (*) INTO count FROM food
WHERE price> price_info1AND price OPEN match_price;
REPEAT
FETCH match_price INTO temp;
IF temp> price_info1 AND temp Then set @ sum = @ sum + temp;
End if;
UNTIL 0 end repeat;
CLOSE match_price;
END

CALL food_price_count (2, 18, @ count );

SELECT @ count, @ sum;

IN indicates the input parameter, OUT indicates the output parameter, INOUT indicates the input or output, and param_name indicates the parameter name of the stored procedure; the type parameter specifies the parameter type of the stored procedure, which can be any data type of the MySQL database.

The characteristic parameter has multiple values. The values are described as follows:

Language SQL: indicates that the routine_body part is composed of SQL statements, which is also the default LANGUAGE of the database system.

[NOT] DETERMINISTIC: specifies whether the execution result of a stored procedure is correct. DETERMINISTIC indicates that the result is correct. Each time a stored procedure is executed, the same input will get the same output. Not deterministic indicates that the result is uncertain. different outputs may be obtained for the same input. The result is uncertain by default.

{Contains SQL | no SQL | reads SQL data | modifies SQL data}: limits on the use of SQL statements by subprograms. Contains SQL indicates that the subprogram contains SQL statements but does not contain statements for reading or writing DATA. no SQL indicates that the subprogram does not contain SQL statements. reads SQL DATA indicates that the subprogram CONTAINS read DATA; modifies SQL DATA indicates the statement that contains the written DATA in the subprogram. By default, the system specifies contains SQL.

SQL SECURITY {DEFINER | INVOKER}: Specifies who has the permission to execute the task. DEFINER indicates that only the DEFINER can execute the job. INVOKER indicates that the caller can execute the job. By default, the permission specified by the system is DEFINER.

COMMENT 'string': COMMENT information.

Tip: When a stored procedure is created, contains SQL is specified by default, indicating that SQL statements are used in the stored procedure. However, if no SQL statement is used in the stored procedure, it is best to set it to no SQL. In addition, the best part of the stored procedure is the COMMENT part, which makes it easier to read the stored procedure code in the future.

2. call the stored procedure

MySQL uses the CALL statement to CALL the stored procedure. After a stored procedure is called, the database system executes the statements in the stored procedure.

Then, return the result to the output value. The basic syntax of the CALL statement is as follows:
CALL sp_name ([parameter [,…]) ;

3. view the definition of the stored procedure

In MySQL, you can use the show create statement to view the status of stored procedures and functions. The basic syntax is as follows:
Show create {PROCEDURE | FUNCTION} sp_name;
The "PROCEDURE" parameter indicates the stored PROCEDURE query; the "FUNCTION" parameter indicates the stored PROCEDURE query FUNCTION; and the "sp_name" parameter indicates the name of the stored PROCEDURE or FUNCTION.

4. modify the stored procedure

Modifying stored procedures and functions refers to modifying the defined stored procedures and functions. MySQL uses the alter procedure statement to modify the stored PROCEDURE. Use the alter function statement to modify the storage FUNCTION. This section describes how to modify stored procedures and functions in detail.
The syntax for modifying stored procedures and functions in MySQL is as follows:
ALTER {PROCEDURE | FUNCTION} sp_name [characteristic...]
Characteristic:
{Contains SQL | no SQL | reads SQL data | modifies SQL DATA}
| SQL SECURITY {DEFINER | INVOKER}
| COMMENT 'string'

5. delete the stored procedure
Deleting stored procedures and functions refers to deleting existing stored procedures and functions in the database. MySQL uses the drop procedure statement to delete stored procedures. You can use the drop function statement to delete a stored FUNCTION. The basic form is as follows:
DROP {PROCEDURE | FUNCTION} sp_name;