My MySQL learning Experience (vii) query

In this "My MySQL Learning experience" series, I have been comparing MySQL with SQL Server, comparing each other is a better way to learn something

Compare the similarities and differences, so as to deepen the memory.

This article "My MySQL Learning Experience (vii)" is also the same, the same place a few, different places I will give examples, thank you for your support O (∩_∩) o

This section focuses on basic queries in MySQL (MySQL official reference manual)

Basic syntax form for select in MySQL:

Select Property List from table name and view list [where conditional expression][group by property name [having conditional expression]][order by property name [Asc|desc]][limit <offset>,row Count]

Description

WHERE clause: query according to the criteria specified by the conditional expression.

GROUP BY clause: GROUP by the field specified by the property name.

HAVING clause: A GROUP BY can have clause that can be output only if the conditions specified in the conditional expression are met.

The GROUP BY clause is typically used with aggregate functions such as count (), sum ().

ORDER BY clause: Sort by the field specified by the property name. The sort method is indicated by the "ASC" and "desc" two parameters, which are sorted by "ASC" by default, ascending.

Build a Test table

Create a test table

CREATE TABLE Fruits (  f_id char) is not NULL,  s_id INT is not null,  f_name  CHAR (255) is not  null,  f_price DECIMAL (8,2) is not NULL,  PRIMARY KEY (f_id))

Inserting test data

INSERT into Fruits (f_id,s_id,f_name,f_price) VALUES (' A1 ', 101, ' Apple ', 5.2), (' B1 ', 102, ' BlackBerry ', 5.2), (' BS1 ', 105, ' Orange ', 5.2), (' BS2 ', 103, ' melon ', 5.2), (' T1 ', 106, ' banana ', 5.2);

Querying the data for the F_ID field using the SELECT statement

SELECT f_id,f_name from Fruits

NOTE: SQL statements in MySQL are case-insensitive, so the select and select functions are the same

This is the same as SQL Server!

Common queries

SELECT * from Fruits

SELECT f_id,f_name from Fruits WHERE f_price >5.1

SELECT f_id,f_name from Fruits WHERE s_id in (101,102)

SELECT f_id,f_name from Fruits WHERE s_id not in (101,102)

SELECT f_id,f_name from Fruits WHERE f_price between 2 and 10

SELECT f_id,f_name from Fruits WHERE f_price not  between 2 and 10

Character matching query with like

1, percent semicolon wildcard "%", matching any length of characters, even including 0 characters

SELECT f_id,f_name from Fruits WHERE f_name like ' b%y '

2, underline the wildcard character "_", can only match any one character at a time

The following statement has four underscores

SELECT f_id,f_name from Fruits WHERE f_name like ' ____n '

Also, there are some in SQL Server

Use [Sss]goselect * from [dbo]. [AAA] WHERE [name] like ' s_____ '

Querying null values

CREATE TABLE Customers (  c_id INT not  null auto_increment,  c_name char (+) NOT NULL,  c_city  char (50 )   NULL,  PRIMARY KEY (c_id))

INSERT into Customers (c_name,c_city) VALUES (' liming ', ' China '), (' Hongfang ', NULL)

SELECT * FROM Customers WHERE c_city is NULL

SELECT * FROM Customers WHERE c_city are not NULL

And, or, distinct keywords

SELECT f_id,f_name from Fruits WHERE f_name like ' ____n ' and f_id= ' BS2 '

SELECT f_id,f_name from Fruits WHERE f_name like ' ____n ' OR f_id= ' BS2 '

SELECT DISTINCT s_id from Fruits

GROUP by

SELECT s_id, COUNT (1) as total from Fruits GROUP by s_id

Insert two more records

INSERT into Fruits (f_id,s_id,f_name,f_price) VALUES (' A6 ', 101, ' Cherry ', 6), (' A8 ', 102, ' coconut ', 7)

If you want to see the names of the kinds of fruits each vendor provides, you can use the Group_concat () function in the group by in MySQL.

Displays the values of each field in each group

SQL Server does not have the Group_concat () function, and SQL Server needs to use the XML function to achieve the same effect, MySQL is doing very well

Having: Filtering grouping

Group the data in the fruits table according to s_id, and display group information with a fruit type greater than 1

SELECT s_id, Group_concat (f_name) as NAMES from Fruits GROUP by s_id have COUNT (f_name) >1

Using with rollup in GROUP by

SELECT s_id, COUNT (1) as total from Fruits GROUP by s_id with ROLLUP

Adds the last line, 7 represents the sum of all the values of the total column

The ROLLUP keyword is also available in SQL Server, as described in SQL Server: All, PERCENT, cube keyword, rollup keyword, and grouping function

Note: When using rollup, you cannot use the ORDER BY clause to sort results at the same time, that is, rollup and order by are mutually exclusive!

Limit limits the number of query results

The TOP keyword is used in SQL Server, and the LIMIT keyword is used in MySQL

limit[position offset], number of rows

The first position offset parameter indicates the line from which MySQL is displayed, and is an optional parameter if the position offset is not specified

Will start with the first record in the table (the position offset of the first record is 0, the position offset for the second day of the record is 1 ...). And so on

The second parameter, number of rows, indicates the number of record bars returned

SELECT * from Fruits

SELECT * from Fruits LIMIT 4,3

The above results return 3 records from the 5th record line (since 0 starts)

Note: You can use the limit 4 OFFSET 3 in MYSQL5.6, which means to get the 3 records starting from the 5th row of records, and the result returned by the limit 4,3 is the same

In SQLSERVER2012, we started to support MySQL-like syntax, but we needed to sort by a column instead of a column, unlike MySQL.

 use [sss]gocreate TABLE fruits (f_id char (TEN) NOT NULL, s_id INT NOT NULL, f_name CHAR (255) is not NULL, F_price DECIMAL (8, 2) not NULL, PRIMARY KEY (f_id)) INSERT into fruits (f_id, s_id, F_na        Me, F_price) SELECT ' A1 ', 101, ' Apple ', 5.2        UNION ALL SELECT ' B1 ', 102, ' BlackBerry ', 5.2 union ALL Select ' BS1 ', 5.2, ' orange ', UNION all select '                BS2 ', 103, ' melon ', 5.2 UNION all SELECT ' T1 ',                106, ' banana ', 5.2 UNION all SELECT ' A6 ', 101,                 ' Cherry ', 6 UNION all SELECT ' A8 ', 102, ' coconut ', 7 

Sort by s_id first, then return to the 3 records starting at line 5th

SELECT  *from    [dbo].[ Fruits]order by [s_id] ASC        OFFSET 4 rowsfetch NEXT 3 ROWS only;

Although not as convenient as MySQL, but also a kind of progress, and for the performance of offset FETCH next can refer to Song article:

SQL Server2012 the enhancement attempt for paging

Sub-query

The subquery feature is introduced from MYSQL4.1 .

Inserting test data

CREATE TABLE tbl1 (num1 INT not NULL); CREATE TABLE tbl2 (num2 INT not NULL) insert into TBL1 values (1), (4), (+), insert into TBL2 values (6), (14), (11), (20)

The Any keyword is followed by a comparison operator that returns true if any value returned from the subquery is more true

Returns all num2 columns of the TBL2 table, and then compares the values of NUM1 in tbl1 with them, as long as any value greater than num2 is the result of matching the query criteria

Select Num1 from Tbl1 WHERE num1>any (select num2 from TBL2)

The all keyword is followed by a comparison operator that returns true if all values returned from the subquery are true

Select Num1 from Tbl1 WHERE num1>all (select num2 from TBL2)

The any and all keywords can also be used in SQL Server

Use [sss]gocreate table TBL1 (NUM1 int. NOT NULL) the CREATE TABLE tbl2 (num2 INT NOT NULL) inserts into TBL1 VALUES (1), (4), (13), (27 INSERT into Tbl2 VALUES (6), (+), (one), () Select Num1 from Tbl1 WHERE num1>any (select num2 from TBL2) select Num1 from t BL1 WHERE Num1>all (SELECT num2 from TBL2)

The result is the same.

MySQL can use multi-column subqueries, and SQL Server does not

SELECT * FROM book WHERE (bookid,bookname) in (select Bookid,bookname from booking)

SQL Server

SELECT * FROM [dbo]. [Ttchar] WHERE ID in (the SELECT ID from  [dbo].[ Ttchar]) and NAME in (the SELECT NAME from [dbo].[ Ttchar])

Merging queries

With the Union keyword, two queries must have the same number of columns and data types when merging results.

Separate SELECT statements using the union or UNION ALL keyword

UNION: Delete duplicate records when executing, all returned rows are unique

UNION All: Do not delete duplicate rows and do not automatically sort results

Select S_id,f_name,f_price from    fruitswhere f_price<9.0union SELECT s_id,f_name,f_pricefrom fruitswhere s_id in (101,103)

The first query F_price less than 9.0 records query, the second query s_id 101 and 103 records query processing

Because some records in F_price less than 9.0 record s_id are 102, 105, 106, these results will not be removed will be merged with the second query

So the final result will be a record of s_id 102, 105, 106.

Regular expression Query

Regular expressions are not available in SQL Server , but not only in MySQL, but also in a richer and more functional way.

Use the RegExp keyword in mysql to specify a character-matching pattern for regular expressions

1. Query for records that begin with a specific character or string

The character "^" matches text that begins with a specific character or string

SELECT * from Fruits WHERE f_name REGEXP ' ^b '

Returns a record of the F_name field beginning with B

2. Query for records ending with a specific character or string

The character "$" matches text that ends with a specific character or string

SELECT * from Fruits WHERE f_name REGEXP ' y$ '

Returns the record of the F_name field ending with Y

3, with the symbol "." To replace any one of the characters in the string

Character "." Match any one character

SELECT * from Fruits WHERE f_name REGEXP ' A.G '

A and G two letters contain a single character, Orange meets the requirements

4. Use "*" and "+" to match multiple characters

The asterisk "*" matches the preceding character any number of times, including 0 times. The plus "+" matches the preceding character at least once

SELECT * from Fruits WHERE f_name REGEXP ' ^ba* '

BlackBerry and Banana Meet the requirements, B begins, a matches any number of times, regardless of the order in which they appear

SELECT * from Fruits WHERE f_name REGEXP ' ^ba+ '

"A +" matches the letter "a" at least once, only banana satisfies the match condition

5. Match the specified string

A regular expression can match a specified string, as long as the string is in the query text, such as to match multiple strings, using the delimiter "|" Between multiple strings. Separated

SELECT * from Fruits WHERE f_name REGEXP ' On|ap '

You can see that the apple, melon, coconut 3 values contain the string "on" and the "AP", which meet the matching criteria

6. Match any one of the specified characters

Square brackets "[]" specify a character set that matches only one of these characters, which is the text you are looking for

SELECT * from Fruits WHERE f_name REGEXP ' [ot] '

square brackets [] can also specify a value collection

SELECT * from Fruits WHERE s_id REGEXP ' [456] '

The s_id field value has 1 of 3 numbers that are match record fields

[456] can also be written as [4-6] the specified set of intervals

7. Match characters other than the specified character

"[^ charset]" matches any character not in the specified collection

SELECT * from Fruits WHERE f_id REGEXP ' [^a-e1-2] '

Returns records that are not at the beginning of the A-E 1-2 letter, for example, A1,B1 These records do not meet the requirements

8. Use {N,} or {n,m} to specify the number of consecutive occurrences of a string

"String {N,}", which represents at least n times the preceding character; "String {n,m}" means that the preceding string matches not less than n times, not more than m times.

SELECT * from Fruits WHERE f_name REGEXP ' b{1,} '

Match at least 1 letters b,blackberry and banana meet the requirements

SELECT * from Fruits WHERE f_name REGEXP ' ba{1,3} '

The "BA" string appears at least once, up to three times, banana this string meets the requirements

Summarize

This section describes the queries in MySQL and compares the differences with SQL Server, especially the regular queries in MySQL are flexible

This is slightly better than SQL Server.

Check the table structure only
Sql server
SELECT TOP 0 * from Dbo.tt
Equivalent to
SELECT * from Dbo.tt where 1=0
Equivalent to
Mysql
SELECT * from Dbo.tt where 1=0
Equivalent to
Mysql
SELECT * from Dbo.tt limit 0

My MySQL learning Experience (vii) query