與MSSQL比較進修MYSQL的心得（七）--查詢

與MSSQL比較進修MYSQL的心得（七）--查詢。本站提示廣大學習愛好者：（與MSSQL比較進修MYSQL的心得（七）--查詢）文章只能為提供參考，不一定能成為您想要的結果。以下是與MSSQL比較進修MYSQL的心得（七）--查詢正文

---

比擬出年夜家的異同點，從而加深記憶

這一篇《與MSSQL比較進修MYSQL的心得（七）》也是一樣，雷同的處所略略帶過，分歧的處所我會給出例子，感謝年夜家的支撐o(∩_∩)o

這一節重要引見MYSQL裡的根本查詢（MYSQL官方參考手冊）

MySQL中select的根本語法情勢：

select 屬性列表
from 表名和視圖列表
[where 前提表達式]
[group by 屬性名[having 前提表達式]]
[order by 屬性名[asc|desc]]
[limit <offset>,row count]
 
解釋：

where子句：依照“前提表達式”指定的前提停止查詢。

group by子句：依照“屬性名”指定的字段停止分組。

having子句：有group by能力having子句，只要知足“前提表達式”中指定的前提的能力夠輸入。

group by子句平日和count()、sum()等聚合函數一路應用。

order by子句：依照“屬性名”指定的字段停止排序。排序方法由“asc”和“desc”兩個參數指出，默許是依照“asc”來排序，即升序。

樹立測試表

創立測試表

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

應用select語句查詢f_id字段的數據

SELECT f_id,f_name FROM fruits

 
留意：MYSQL中SQL語句是不辨別年夜小寫的，是以select和SELECT感化是雷同的

這一點跟SQLSERVER是一樣的！

經常使用查詢

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

 
帶like的字符婚配查詢

1、百分號通配符“%”，婚配隨意率性長度的字符，乃至包含零字符

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

 2、下劃線通配符“_”,一次只能婚配隨意率性一個字符

上面語句有四個下劃線

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

異樣，在SQLSERVER外面也是有的

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

 
查詢空值

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症結字

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 INTO fruits(f_id,s_id,f_name,f_price)
VALUES('a6',101,'cherry',6),
('a8',102,'coconut',7)

 
假如要檢查每一個供給商供給的生果的品種的稱號，MYSQL中可以在GROUP BY中應用GROUP_CONCAT()函數，

將每一個分組中各個字段的值顯示出來

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

SQLSERVER是沒有GROUP_CONCAT()函數的，SQLSERVER要到達異樣後果須要應用xml函數，MYSQL這方面做得異常好

having：過濾分組

依據s_id對fruits表中的數據停止分組，並顯示生果品種年夜於1的分組信息

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

 
在group by中應用with rollup

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

增長了最初一行，7表現total列的一切值的總和

而rollup症結字在SQLSERVER外面也有，詳見：SQLSERVER中的ALL、PERCENT、CUBE症結字、ROLLUP症結字和GROUPING函數

留意：當應用ROLLUP時，不克不及同時應用ORDER BY子句停止成果排序，即ROLLUP和ORDER BY是相互排擠的！

limit限制查詢成果的數目

在SQLSERVER中是應用TOP症結字，而在MYSQL中是應用LIMIT症結字

LIMIT[地位偏移量]，行數
 第一個“地位偏移量”參數指導MYSQL從哪一行開端顯示，是一個可選參數，假如不指定“地位偏移量”

將會從表中第一筆記錄開端（第一筆記錄的地位偏移量是0，第二天記載的地位偏移量是1......以此類推）

第二個參數“行數”指導前往的記載條數

SELECT * FROM fruits

SELECT * FROM fruits LIMIT 4,3

下面成果前往從第5筆記錄行（由於從0開端數）開端以後的3筆記錄

留意：在MYSQL5.6中可使用 LIMIT 4 OFFSET 3 ，意思是獲得從第5行記載開端的3筆記錄，和 LIMIT 4,3 前往的成果是一樣的

在SQLSERVER2012外面開端支撐相似MYSQL的這類語法，然則須要按某一列先排序，而不像MYSQL那樣不消依照某一列排序

USE [sss]
GO

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 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
 

先按s_id排序，然後前往第5行開端的3筆記錄

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

 
固然沒有MYSQL那末便利，不外也算是一種提高，而關於OFFSET FETCH NEXT的機能可以參考宋沄劍的文章：

SQL Server2012 T-SQL對分頁的加強測驗考試

子查詢

子查詢這個特征從MYSQL4.1開端引入。

拔出測試數據

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)

 
ANY症結字接在一個比擬操作符的前面，表現若與子查詢前往的任何值比擬為TRUE，則前往TRUE

前往tbl2表的一切num2列，然後將tbl1中的num1的值與之停止比擬，只需年夜於num2的任何一個值，即為相符查詢前提的成果

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

ALL症結字接在一個比擬操作符的前面，表現與子查詢前往的一切值比擬為TRUE，則前往TRUE

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

在SQLSERVER中也能夠應用ANY和ALL症結字

USE [sss]
GO
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)

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

成果是一樣的

歸並查詢

應用UNION症結字，歸並成果時，兩個查詢對應的列數和數據類型必需雷同。

各個SELECT語句之間應用UNION或UNION ALL症結字分隔

UNION：履行的時刻刪除反復的記載，一切前往的行都是獨一的

UNION ALL：不刪除反復行也纰謬成果停止主動排序

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)

第一個查詢把f_price小於9.0的記載查詢出來，第二個查詢把s_id為101和103的記載查詢處置

由於f_price小於9.0的記載裡有些記載的s_id是102、105、106，這些成果不會被去失落會跟第二個查詢停止歸並

所以終究的成果會有s_id為102、105、106的記載

正則表達式查詢

正則表達式在SQLSERVER外面是沒有的，然則在MYSQL裡不單只要，並且功效也比擬豐碩

MYSQL中應用REGEXP症結字指定正則表達式的字符婚配形式

1、查詢以特定字符或字符串開首的記載

字符“^”婚配以特定字符或許字符串開首的文本

SELECT * FROM fruits WHERE f_name REGEXP '^b'

前往f_name字段以b開首的記載

2、查詢以特定字符或字符串開頭的記載

字符“$”婚配以特定字符或許字符串開頭的文本

SELECT * FROM fruits WHERE f_name REGEXP 'y$'

前往f_name字段以y開頭的記載

3、用符號“.”來取代字符串中的隨意率性一個字符

字符“.”婚配隨意率性一個字符

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

a和g兩個字母之間包括單個字符，orange相符請求

4、應用“*”和“+”來婚配多個字符

星號“*”婚配後面的字符隨意率性屢次，包含0次。加號“+”婚配後面的字符至多一次

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

blackberry和banana相符請求，b開首，a婚配隨意率性屢次，不論湧現的次序在哪裡

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

 “a+”婚配字母“a”至多一次，只要banana知足婚配前提

5、婚配指定字符串

正則表達式可以婚配指定字符串，只需這個字符串在查詢文本中便可，如要婚配多個字符串，多個字符串之間應用分隔符“|”離隔

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

可以看到apple 、melon 、coconut 3個值中都包括有字符串“on”和“ap”，知足婚配前提

6、婚配指定字符中的隨意率性一個

方括號“[]”指定一個字符聚集，只婚配個中任何一個字符，即為所查找的文本

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

方括號[]還可以指定命值聚集

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

s_id字段值中有3個數字中的1個即為婚配記載字段

[456]也能夠寫成[4-6]即指定聚集區間

7、婚配指定字符之外的字符

“[^字符聚集]”婚配不在指定聚集中的任何字符

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

 

前往開首不在a-e  1-2字母的記載，例如a1，b1這些記載就不相符請求

8、應用{n,} 或許{n,m}來指定字符串聯續湧現的次數

“字符串{n,}”，表現至多婚配n次後面的字符；“字符串{n,m}”表現婚配後面的字符串很多於n次，不多於m次。

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

至多婚配1次字母b，blackberry和banana都相符請求

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

“ba”字符串起碼湧現一次，最多三次，banana這個字符串相符請求

總結

這一節引見了MYSQL裡的查詢，而且比擬了與SQLSERVER的差別，特殊是MYSQL裡的正則查詢靈巧多變

這一點比SQLSERVER略為優越

若有纰謬的處所，迎接年夜家拍磚o(∩_∩)o

附上作者艷照一張o(∩_∩)o 猜猜是哪一個