My experiences with MYSQL (7)

My experiences with MYSQL (7)

My experiences with MYSQL (1)

My experiences with MYSQL (2)

My experiences with MYSQL (III)

My experiences with MYSQL (4)

My experiences with MYSQL (5)

My experiences with MYSQL (6)

 

In this "my MYSQL learning experiences" series, I have always compared MYSQL and SQLSERVER. comparison is a better way to learn one thing.

Compares the similarities and differences between people to deepen the memory

The same is true for this article "My MYSQL learning experience (7)", which is omitted in the same place. I will give an example in different places. Thank you for your support of o (∩ _ ∩) o

 

 

This section describes basic queries in MYSQL (MYSQL official reference manual)

The basic syntax of select in MySQL:

Select attribute list from Table Name and View list [where condition expression] [group by attribute name [having condition expression] [order by attribute name [asc | desc] [limit <offset>, row count]

 

Note:

Where clause: queries based on the conditions specified by the "conditional expression.

Group by clause: groups fields specified by "attribute name.

Having Clause: A group by clause can have a having clause. Only a group by clause that meets the conditions specified in the conditional expression can be output.

The group by clause is usually used together with Aggregate functions such as count () and sum.

Order by clause: sorts the fields specified by "attribute name. The sorting method is indicated by the "asc" and "desc" parameters, which are sorted by "asc" by default, that is, ascending order.

 

Create a test table

Create test table

CREATE TABLE fruits(  f_id CHAR(10)  NOT NULL,  s_id INT NOT NULL,  f_name  CHAR(255)  NOT NULL,  f_price DECIMAL(8,2) NOT NULL,  PRIMARY KEY(f_id))

Insert 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);

 

Use the select statement to query data of the f_id Field

SELECT f_id,f_name FROM fruits

 

Note: SQL statements in MYSQL are case-insensitive, so the select and SELECT functions the same.

This is the same as SQLSERVER!

 

Common Query

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

 

Search for like characters

1. percent wildcard "%", matching any length of characters, or even including zero characters

SELECT f_id,f_name FROM fruits WHERE f_name LIKE 'b%y'

2. The underline wildcard "_" can only match any character at a time

The following statement has four underscores.

SELECT f_id,f_name FROM fruits WHERE f_name LIKE '____n'

Likewise, SQL Server also has

USE [sss]GOSELECT * FROM [dbo].[aaa] WHERE [name] LIKE 's_____'

 

 

Query null values

CREATE TABLE customers(  c_id INT  NOT NULL AUTO_INCREMENT,  c_name CHAR(25) 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 IS NOT NULL

 

AND, OR, DISTINCT keyword

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

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)

 

To view the name of the fruit type provided BY each vendor, MYSQL can use the GROUP_CONCAT () function in group,

Display the values of fields in each group

SELECT s_id,GROUP_CONCAT(f_name) AS NAMES FROM fruits GROUP BY s_id 

SQLSERVER does not have the GROUP_CONCAT () function. To achieve the same effect, SQLSERVER must use the xml function. MYSQL is doing very well in this respect.

 

Having: filter groups

Groups data in the fruits table based on s_id and displays the group information with the fruit category greater than 1.

SELECT s_id ,GROUP_CONCAT(f_name) AS NAMES FROM fruits GROUP BY s_id HAVING COUNT(f_name)>1

 

Use with rollup in group

SELECT s_id ,COUNT(1) AS total FROM fruits GROUP BY s_id WITH ROLLUP

Adds the last row. 7 indicates the sum of all values in the total column.

The rollup keyword is also available in SQLSERVER. For details, see ALL, PERCENT, CUBE keyword, ROLLUP keyword, and GROUPING function in SQLSERVER.

Note: When ROLLUP is used, the order by clause cannot be used for result sorting at the same time, that is, ROLLUP and order by are mutually exclusive!

 

Limit the number of query results

In SQLSERVERTOPKeyword, which is used in MYSQLLIMITKeywords

LIMIT [location offset], number of rows

The first "location offset" parameter indicates the row from which MYSQL is displayed. It is an optional parameter. If "location offset" is not specified"

Starting from the first record in the table (the position offset of the first record is 0, and the position offset of the record on the next day is 1 ...... and so on)

The second parameter "number of rows" indicates the number of returned records

SELECT * FROM fruits

SELECT * FROM fruits LIMIT 4,3

The above results return three records starting from 5th record rows (because the number starts from 0 ).

 

Note: In MYSQL5.6, you can use LIMIT 4 OFFSET 3 to obtain the three records starting from the first row of records, which is the same as the results returned by LIMIT 5th.

 

In SQLSERVER2012, MYSQL-like syntax is supported, but sorting by a column is required, instead of sorting by a column as MYSQL does.

USE [sss]GOCREATE TABLE fruits(    f_id CHAR(10) NOT NULL ,    s_id INT NOT NULL ,    f_name CHAR(255) NOT NULL ,    f_price DECIMAL(8, 2) NOT NULL ,    PRIMARY KEY ( f_id ))INSERT  INTO fruits        ( f_id ,          s_id ,          f_name ,          f_price        )        SELECT  'a1' ,                101 ,                'apple' ,                5.2        UNION ALL        SELECT  'b1' ,                102 ,                'blackberry' ,                5.2        UNION ALL        SELECT  'bs1' ,                105 ,                'orange' ,                5.2        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

 

First sort by s_id, and then return 3 Records starting from row 5th

SELECT  *FROM    [dbo].[fruits]ORDER BY [s_id] ASC        OFFSET 4 ROWSFETCH NEXT 3 ROWS ONLY;

 

Although it is not as convenient as MYSQL, it is also an improvement. For the performance of offset fetch next, refer to Song xiaojian's article:

SQL Server2012 T-SQL's attempt to enhance Paging

 

Subquery

SubqueryThis feature starts fromMYSQL4.1Start introduction.

Insert Test Data

CREATE TABLE tbl1(num1 INT NOT NULL);CREATE TABLE tbl2(num2 INT NOT NULL)INSERT INTO tbl1 VALUES(1),(4),(13),(27);INSERT INTO tbl2 VALUES(6),(14),(11),(20)

 

The ANY keyword is followed by a comparison operator, indicating that TRUE is returned if it is compared with ANY value returned by the subquery.

Return all num2 columns in the tbl2 table, and compare the num1 value in tbl1 with the value. If it is greater than any value in num2, it is the result that meets the query conditions.

SELECT num1 FROM tbl1 WHERE num1>ANY(SELECT num2 FROM tbl2)

 

The ALL keyword is followed by a comparison operator, indicating that it is TRUE when compared with ALL values returned by the subquery. TRUE is returned.

SELECT num1 FROM tbl1 WHERE num1>ALL(SELECT num2 FROM tbl2)

 

InSQLSERVERYou can also use the ANY and ALL keywords.

USE [sss]GOCREATE TABLE tbl1(num1 INT NOT NULL)CREATE TABLE tbl2(num2 INT NOT NULL)INSERT INTO tbl1 VALUES(1),(4),(13),(27)INSERT INTO tbl2 VALUES(6),(14),(11),(20)SELECT num1 FROM tbl1 WHERE num1>ANY(SELECT num2 FROM tbl2)SELECT num1 FROM tbl1 WHERE num1>ALL(SELECT num2 FROM tbl2)

The results are the same.

 

Merge Query

When the UNION keyword is used, the columns and Data Types of the Two Queries must be the same.

Use the UNION or union all keywords to separate SELECT statements.

UNION: deletes duplicate records during execution. All returned rows are unique.

Union all: duplicate rows are not deleted, and results are not automatically sorted.

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 queries records with f_price less than 9.0, and the second query queries records with s_id 101 and 103.

Because the s_id of some records whose f_price is less than 9.0 is 102, 105, and 106, these results will not be removed and will be merged with the second query.

Therefore, the final result will contain records with s_id 102, 105, and 106.

 

Regular Expression Query

The regular expression is inSQLSERVERIt does not exist in it, but it is not only in MYSQL, but also has rich functions.

Use the REGEXP keyword in MYSQL to specify the character matching mode of the Regular Expression

 

1. query records starting with a specific character or string

The character "^" matches the text starting with a specific character or string

SELECT * FROM fruits WHERE f_name REGEXP '^b'

Returns records starting with B in the f_name field.

 

 

2. query records ending with a specific character or string

The character "$" matches the text ending with a specific character or string.

SELECT * FROM fruits WHERE f_name REGEXP 'y$'

Returns the records whose f_name field ends with y.

 

 

3. Use the symbol "." To replace any character in the string.

The character "." matches any character

SELECT * FROM fruits WHERE f_name REGEXP 'a.g'

A and g contain a single character. orange meets the requirements.

 

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

The asterisk "*" matches the preceding characters multiple times, including 0. The plus sign "+" matches the previous character at least once.

SELECT * FROM fruits WHERE f_name REGEXP '^ba*'

Blackberry and banana meet the requirements, starting with B, a matches any number of times, no matter where the order appears

 

SELECT * FROM fruits WHERE f_name REGEXP '^ba+'

"A +" matches the letter "a" at least once. Only banana meets the matching conditions.

 

5. Match the specified string

A regular expression can match a specified string, as long as the string is in the query text. To match multiple strings, use the separator "|" to separate multiple strings.

SELECT * FROM fruits WHERE f_name REGEXP 'on|ap'

The three values of apple, melon, and coconut contain strings "on" and "ap", meeting the matching conditions.

 

6. match any of the specified characters

The square brackets "[]" specify a character set that matches only one of the characters, that is, the searched text.

SELECT * FROM fruits WHERE f_name REGEXP '[ot]'

 

Square brackets [] can also specify a numerical set

SELECT * FROM fruits WHERE s_id REGEXP '[456]'

One of the three numbers in the s_id field is the matching record field.

[456] can also be written as [4-6] to specify the set interval

 

7. Match characters other than the specified character

"[^ Character Set combination]" matches any character that is not in the specified set

SELECT * FROM fruits WHERE f_id REGEXP '[^a-e1-2]'

 

Returns records starting with 1-2 letters a-e. For example, a1 and b1 do not meet the requirements.

 

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

"String {n ,}" indicates that the string matches the previous character at least n times;" string {n, m} "indicates that the string matches the previous character no less than n times, no more than m times.

 SELECT * FROM fruits WHERE f_name REGEXP 'b{1,}'

Match at least 1 letter B, and both blackberry and banana meet the requirements.

 

 SELECT * FROM fruits WHERE f_name REGEXP 'ba{1,3}'

The "ba" string can appear at least once and at most three times. The banana string meets the requirements.

Summary

This section describes the queries in MYSQL and compares them with SQLSERVER, especially the regular queries in MYSQL are flexible and variable.

This is a little better than SQLSERVER

 

 

If anything is wrong, you are welcome to make a brick o

 

I will attach a photo of the Microsoft marathon Development competition when I am working as a handyman.