Learn more about Mysql compared with MSSQL (VII.)--Query _mysql

Compare the similarities and differences, and deepen the memory.

This article "Compared with the MSSQL learning MySQL (Seven)" is the same, the same place slightly brought, different places I will give examples, thank you for your support O (∩_∩) o

This section mainly introduces MySQL in the basic query (MySQL official reference manual)

The basic grammatical form of select in MySQL:

Select attribute List
From table name and view list
[where-condition expression]
[Group BY property name [having conditional expression]]
[Order BY attribute name [Asc|desc]]
[Limit <offset>,row Count]

Description

WHERE clause: query by condition specified by condition expression.

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

HAVING clause: Has a GROUP by to have clause that can be output only if the condition specified in the condition expression is met.

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

ORDER BY clause: Sort by the fields specified by the property name. The sort method is indicated by the "ASC" and "desc" two parameters, and the default is to sort by "ASC", that is, ascending.

Set up a test table

To create a test table

CREATE TABLE Fruits
(
 f_id char () NOT NULL,
 s_id INT not NULL,
 f_name char (255) NOT NULL,
 F_price DE Cimal (8,2) not NULL,
 PRIMARY KEY (f_id)
)

Inserting test data

INSERT into Fruits
VALUES (' A1 ', F_id,s_id,f_name,f_price, ' Apple ', 5.2),
(' B1 ', 102, ' BlackBerry ', 5.2),
(' BS1 ', 5.2, ' orange ',),
(' BS2 ', "melon", "5.2"),
(' T1 ', the, ' banana ', 5.2);

Querying data for f_id fields using the SELECT statement

SELECT f_id,f_name from Fruits

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

This is the same as SQL Server!

Common queries

SELECT * from Fruits
Select F_id,f_name to fruits where f_price >5.1
select f_id,f_name from Fruits WHERE s_i D in (101,102)
select F_id,f_name to fruits where s_id not in (101,102)
select f_id,f_name from fruits where f_p Rice BETWEEN 2 and
SELECT f_id,f_name from Fruits WHERE f_price not BETWEEN 2 and 10

Character matching query with like

1, percent wildcard "%", match 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 '

Similarly, there are also in SQL Server

Use [SSS]
go
SELECT * FROM [dbo].[ AAA] WHERE [name] like ' s_____ '

Query null value

CREATE TABLE Customers
(
 c_id INT not null auto_increment,
 c_name char not NULL,
 c_city char (50) C13/>null,
 PRIMARY KEY (c_id)
)
INSERT into Customers (c_name,c_city)
VALUES (' liming ', ' the ', ')
(' Hongfang ', NULL)
SELECT * FROM Customers WHERE c_city is NULL

SELECT * FROM Customers WHERE c_city isn't 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 LI KE ' ____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 ', Cherry ', 6),
(' A8 ', 102, ' coconut ', 7)

If you want to see the name of the type of fruit each supplier provides, MySQL can use the Group_concat () function in GROUP by.

Show the values of each field in each group

SELECT S_id,group_concat (f_name) as NAMES from Fruits GROUP by s_id 

SQL Server is not Group_concat () function, SQL Server to achieve the same effect requires the use of XML functions, MySQL this is very good

Having: Filtering groupings

Groups the data in the fruits table according to s_id, and displays the grouped information of the fruit species greater than 1

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

Using the 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 in the total column

The ROLLUP keyword is also available in SQL Server, see: All, PERCENT, cube keywords, rollup keywords, and grouping functions in SQL Server

Note: When you use rollup, you cannot use the ORDER BY clause to sort the results, that is, rollup and orders by are mutually exclusive!

Limit limit 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" argument indicates which line of MySQL to start with and is an optional parameter, if you do not specify a "position offset"

Will start with the first record in the table (the position offset of the first record is 0, and the next day the position offset of the record is 1 ...). Analogy

The second argument "number of rows" indicates the number of record bars returned

SELECT * from Fruits

SELECT * from Fruits LIMIT 4,3

The results above return 3 records from the 5th record row (since the start of 0)

Note: LIMIT 4 OFFSET 3 can be used in MYSQL5.6, meaning to get 3 records starting with line 5th Records, and LIMIT 4,3 return the same result

In SQLSERVER2012, you start to support this kind of MySQL-like syntax, but you need to sort by a column, rather than in a column like MySQL.

Use [SSS]
go

CREATE TABLE fruits
(
  f_id CHAR (a) not NULL,
  s_id INT to NOT NULL,
  f_name CHAR (255) N OT null,
  f_price DECIMAL (8, 2) not NULL,
  PRIMARY KEY (f_id)
)

inserts into Fruits
    (f_id,
     s _id,
     f_name,
     f_price
    )
    SELECT ' A1 ', Mr
        ,
        ' Apple ',
        5.2
    UNION
    All Select ' B1 ',
        102,
        ' BlackBerry ',
        5.2
    UNION all
    SELECT ' BS1 ', and
        ' Orange ',
        5.2
    UNION ALL
    Select ' BS2 ',
        5.2,
        ' melon ',
        the
    union ALL
    Select ' T1 ',
        the Banana ',
        5.2
    UNION ALL
    Select ' A6 ', and
        ' Cherry ',
        6 UNION ALL
    Select ' A8 ' ,
        102,
        ' coconut ',
        7
 

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

SELECT *
from  [dbo].[ Fruits] ORDER by
[s_id] ASC
    OFFSET 4 rows
FETCH NEXT 3 rows;

Although not as convenient as MySQL, it is a step forward, and for offset FETCH next performance you can refer to Song's article:

An attempt to enhance SQL Server2012 T-SQL for paging

Child query

Subquery This feature is introduced from MYSQL4.1.

Inserting test data

CREATE TABLE tbl1 (num1 INT not NULL);
CREATE TABLE tbl2 (num2 INT not NULL)

inserts into TBL1 VALUES (1), (4), (in);
INSERT into Tbl2 VALUES (6), (14), (11), (20)

The Any keyword is followed by a comparison operator to return TRUE if any value returned by the subquery is true

Returns all the num2 columns of the TBL2 table, and then compares the NUM1 values in TBL1 to the results of the query criteria as long as they are greater than any of the num2 values

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

The all keyword is followed by a comparison operator, indicating that all values returned by the subquery are more than true, and return True

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

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

INSERT into num2 VA with [SSS] go
CREATE table TBL1 (NUM1 int. NOT NULL)
CREATE TABLE tbl2 (TBL1 INT not NULL)

Lues (1), (4), (a)
INSERT into Tbl2 VALUES (6), (m), (a)

select Num1 from Tbl1 WHERE num1>any (select Num2 from Tbl2)
SELECT num1 to Tbl1 WHERE Num1>all (SELECT num2 from TBL2)

The result is the same

Merging queries

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

Separate SELECT statements using the union or UNION ALL keyword

UNION: Delete duplicate records at execution time, all returned rows are unique

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

Select S_id,f_name,f_price from  
fruits
WHERE f_price<9.0
UNION 
Select S_id,f_name,f_price
From Fruits
WHERE s_id in (101,103)

The first query F_price less than 9.0 records, and the second query deals with records queries that are S_ID 101 and 103.

Because some records in F_price less than 9.0 s_id are 102, 105, 106, these results will not be removed and 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 more functional way.

In MySQL, use the RegExp keyword to specify a character-matching pattern for regular expressions

1. A record of a query that begins with a specific character or string

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

SELECT * from Fruits WHERE f_name REGEXP ' ^b '

Returns the record of the F_name field starting with B

2, the query at the end of a specific character or string records

Character "$" matches text at the end of a particular character or string

SELECT * from Fruits WHERE f_name REGEXP ' y$ '

Returns a record with the F_name field ending in 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. Plus "+" matches the preceding character at least once

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

BlackBerry and Banana meet 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 matching condition

5. Match the specified string

Regular expressions can match the specified string, as long as the string is in the query text, such as to match multiple strings, using the separator "|" Between multiple strings Separated

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

You can see that the 3 values for Apple, melon, and coconut all contain the string "on" and "AP", satisfying the matching criteria

6. Match any one of the specified characters

The square bracket "[]" specifies a character set that matches only one of the characters, that is, the text that is found

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

square brackets [] You can also specify a collection of values

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

The s_id field value has 1 of 3 digits, which is the matching record field

[456] can also be written as [4-6] that specifies the set interval

7. Matches characters other than the specified characters

' [^ character set] ' matches any character not in the specified collection

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

Returns records that do not begin with the A-E 1-2 letters, such as A1,B1, that do not meet the requirements

8, use {n,} or {n,m} to specify the number of consecutive occurrences of the string

"String {N,}", which represents a character that matches at least N times before, and "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,} '

At least 1 times the letter 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 query in MySQL, and compares the differences with SQL Server, especially the regular queries in MySQL are flexible

This is a little better than SQL Server.

If there is a wrong place, welcome to shoot Bricks O (∩_∩) o

Attached to the author of the photo of an O (∩_∩) o guess which