Basic Query SQL statements

DAY03 Basic Query SQL statements

First, overview:

Learn MySQL database must learn SQL query function, plainly, the use of the database is to store data, query data, the use of such data a process. It is only after viewing that we know if we have a successful store and whether we can use the data directly.

Second, the specific SQL

1, query the basic syntax of the database:

Select needs to query the field name and content from specify the related table name in the where query when the condition group by how to group results order by how to sort the result bottles The second condition that the having query satisfies is the result of the limit limit query output

(a): single-table Query

1. Query all fields

SELECT * from table name;

2. Query the specified field

Select field name from table name;

3. Querying the specified data

SELECT * FROM table name where condition;

4. Query with "in" keyword

SELECT * FROM table name where condition [not] in (element 1, ...         element N); Not: is an optional parameter, plus not means that the conditional element is not satisfied within the collection: represents the elements in the collection, separated by commas between the elements

5. Range query with "between and"

SELECT * FROM table name where condition [not] between takes value 1 and takes value 2; Not: is an optional parameter that does not satisfy a condition value of 1 in the specified range: The starting value of the range represents the value 2: Indicates the range's terminating value

6. Character matching query with "like" "Implement Fuzzy Query"

Like character to implement fuzzy query, generally in our web page search, retrieval and other parts of the use of fuzzy query to use the two wildcard characters: "%" and "_" two.          "%": can match one or more characters, can represent any long string "_": Match only one character, such as M_n; start with M, N end 3 characters select * from table name where condition like '%a '; Query This table contains all the data of the a character.

7. Multi-condition query with "and"

SELECT * FROM table name where Condition 1 and condition 2; Connect multiple conditions using the AND keyword

8. Multi-condition query with "Or"

SELECT * FROM table name where Condition 1 or condition 2; As long as one of the conditions is satisfied, it will be queried

9. Use "distinct" keyword to cancel duplicate data

Select DISTINCT field name from table name; To cancel a repeating record row in a query result

10. Sort by using "ORDER BY" keyword

SELECT * FROM table name ORDER by [Asc][desc]; ORDER BY: Refers to the way in which ASC is sorted: refers to "ascending" sort desc: Refers to "descending" sort

11. Grouping by keyword with "group by"

Select field 1, Field 2, ... from table name GROUP by a field 1 or field 2 group check; You can also group multiple fields at the same time

12. Limit the number of query results using "limit"

SELECT * from ORDER by ASC limit 3; The results of the query show the first 3 records

(b) Aggregate function query

1. Use the "count ()" function

Select COUNT (*) from table name; All the records in the statistics

2. Use the sum () function

Select sum (row) from table name; To find the total number of rows in a table.

3. Use the "AVG ()" function

Select AVG (row) from table name; To find an average of rows in a table

4. Use the max () function

Select Max (row) from table name; Maximum value of row field in query table

5. Use "min ()" function

Select min (row) from table name; Minimum value of row field in query table

(c) Connection enquiry

1, Internal connection query

Select field 1, Field 2, Field 3 from Table name 1, table name 2 where table name 1. field 1= table Name 2. Field 2; The query to implement a connection between a table and a table is to make sure that all two tables have the same field names.

2, External connection query

The outer join is different from the inner connection, when the external connection refers to the use of the "outer join" keyword to connect two tables together.

Divided into left and outer connections, "Right join", outer join "R", fully connected 3 types

Grammar:

Select field name from table name 1 left/right join table name 2 on table name 1. field 1= table Name 2. Field 2;

2.1. Left outer connection "Ieft join"

Left OUTER join: refers to a combination of all data from the left table to each data in the right table, the returned results in addition to the concatenated data, including non-conforming data in the left table, and adds a null value to the corresponding column in the right table.

Select field name from Table Name 1 LEFT JOIN table name 2 on table name 1. field 1= table Name 2. Field 2;

2.2. Right OUTER JOIN

A right outer join is a combination of all the data in the right table in each of the data in the left table, along with the data returned in addition to the concatenated data, including the non-conforming criteria in the right table, and adding a null value to the corresponding column in the left table.

Select field name from table name 1 Right JOIN table name 2 on table name 1. field 1= table Name 2. Field 2;

2.3. Cross join: Cartesian product

SELECT * FROM table 1 cross Join table 2

(d) Sub-query

Subquery: That is, the select query is a subsidiary of another query. What is a subordinate relationship, is a query statement query out of the result is a second query a condition or a basis, the premise, etc., only when it is queried to be able to query the results of the second.

1. Sub-query with "in" keyword

Using the In operator allows you to detect whether a particular value exists in the result set and executes an external query statement if the detection succeeds.

SELECT * FROM table name 1 where field 1 in (select field 1 from table name 2); All records in table 1 are queried, but field 1 must appear in table 2.

2. Subqueries with comparison operators

Subqueries using operators are more mundane, including "=,! =, >, <, >=, <="

3. Sub-query with "exists" keyword

When using the EXISTS keyword, the inner query statement does not return a record of the query, but instead returns a true or False value.

SELECT * FROM table name 1 where exists (SELECT * from table Name 2 where field name); Use a subquery query in table 2 for the existence of a certain qualification of the data, if any, to execute the outer query data, if not, only to execute the inner query data

4. Word query with "any" keyword

Using the Any keyword means that the outer query statement can be executed by changing the condition as long as any one of these conditions is satisfied.

5. Sub-query with "all" keyword

Using the All keyword means that the inner query statement must satisfy all the criteria, and when the ALL keyword is used, the outer query can be executed only if all the conditions of the inner query are met.

(v) consolidated query Results

The result of a merge query is to merge the results of multiple SELECT statement queries into one.

1. Use the "union" keyword

SELECT * FROM table 1 Union SELECT * FROM table 2;

Using the Union keyword merges the results of the query into one, and then removes the same, duplicate records.

2. Use the "union ALL" keyword

SELECT * FROM table 1 UNION ALL SELECT * from table 2;

Using the UNION ALL keyword simply merges the results in one and does not cancel the duplicate records.

(vi) define aliases for tables and fields

1. Define the alias of the table

SELECT * FROM table name 1 a where a.        Field 1; This A is the alias of the table, which reduces the time to write the table name.

2. Define aliases for fields

Basic form: field name [as] Alias

Select field 1 as A, Field 2 as B from table name;

(vii) query using regular Expressions

Basic form: Field name regexp ' Match method '

1. Mode characters

Pattern Character	meaning
^	Match records that begin with a specific character or string
$	Match records that end with a specific character or string
.	Matches any one character of a string, including carriage return and line break
[]	Match any one of the characters in the character set
[^ ]	Match any character except the character set
S1|S2|S3	Match any one of these characters
*	Matches more than one character before the symbol, including 1 and 0
string {N}	Match string appears n times

+	Matches more than one character before the character, including 1
string {m,n}	Match string appears at least m times, up to N times

This article from "Lonely One Night" blog, reproduced please contact the author!

Basic Query SQL statements