MySQL--数据查询、插入、更新与删除数据

查询

单表查询

查询所有字段

在SELECT语句中使用通配符星号(*)查询所有字段。

-- 单表查询
-- * 通配符，查询所有列的名称
SELECT * FROM products

指定字段查询

在关键字SELECT后指定要查询的字段，多个字段之间用逗号(,)隔开。

-- 查询指定字段，字段间以逗号(,)隔开
SELECT productName,productCode,buyPrice FROM products

查询指定记录，使用WHERE对数据过滤

在SELECT语句中使用WHERE对数据进行过滤。

-- WHERE 对数据进行过滤
-- 在WHERE语句中，MySQL提供了一系列的条件运算符，如 =,<>(不相等),!=,<,<=,>,>=,BETWEEN....AND(介于两者之间)
-- BETWEEN AND 这里查询buyPrice 在95 到105之间的产品名称和价格
SELECT buyPrice,productName FROM products WHERE buyPrice BETWEEN 95 AND 105 

带IN关键字查询

IN操作符用来查询满足指定范围内的条件记录，使用IN操作符,将所有检索条件用括号括起来，检索条件之间用逗号隔开，只要满足条件范围内的一个值即为匹配项。

-- IN 操作符用来查询指定范围内的条件记录。
-- 查询buyPrice为102或者103.42的产品
SELECT buyPrice,productName FROM products WHERE buyPrice IN (102,103.42)
-- NOT IN 操作符用来查询不在指定范围内的条件记录。
-- 查询buyPrice不为102和103.42的产品
SELECT buyPrice,productName FROM products WHERE buyPrice NOT IN (102,103.42)

带LIKE的字符匹配查询

LIKE使用通配符查询。

-- 和LIKE一起使用的通配符有%和_。%匹配任意长度的字符，包括零字符。_匹配任意一个字符。
-- %pe 匹配以pe结尾的，%pe% 匹配包含pe的，pe%匹配包含pe的
SELECT buyPrice,productName FROM products WHERE productName LIKE '%pe'
-- 这里有 13个下划线_
SELECT buyPrice,productName FROM products WHERE productName LIKE '_____________Coupe'

查询空值 (IS NULL)

IS NULL查询某字段为空值的记录。IS NOT NULL查询某字段不为空值的记录。

-- IS NULL查询某字段为空的记录。
-- 查询products表中productDescription字段为空值的数据。
SELECT buyPrice,productName,productDescription FROM products WHERE productDescription IS NULL
-- NOT IS NULL查询某字段不为空的记录。
-- 查询products表中productDescription字段不为空值的数据。
SELECT buyPrice,productName,productDescription FROM products WHERE productDescription NOT IS NULL

带AND的多条件查询

在SELECT查询时，在WHERE语句中使用AND操作符限定只有满足所有查询条件的才会被返回。使用AND连接两个或多个查询的条件，多个条件表达式之间使用AND分开。

-- AND 连接多个查询条件,查询满足所有条件的产品
SELECT productName,buyPrice,productScale FROM products WHERE buyPrice > 100 AND productScale = '1:10'

带OR的多条件查询

与AND相反，在WHERE声明中使用OR操作符，表示只要满足其中一个条件的记录即可返回。OR跟AND一样可以连接两个或多个查询条件，多个条件表达式之间使用OR隔开。

-- OR 连接多个查询条件，查询满足其中一个条件的产品
SELECT productName,buyPrice,productScale FROM products WHERE buyPrice > 100 OR productScale = '1:10'

DISTINCT去重查询

DISTINCT关键字去掉重复的记录值。

-- DISTINCT去重。这里查询products表中productScale字段
SELECT DISTINCT productScale FROM products

ORDER BY 对查询结果排序

ORDER BY 对查询的结果进行排序。排序默认为升序排序ASC。降序排序为 DESC

单列排序

-- 查询products表按buyPrice升序排序
SELECT buyPrice,productName FROM products ORDER BY buyPrice
-- 查询products表按buyPrice降序排序
SELECT buyPrice,productName FROM products ORDER BY buyPrice DESC

多列排序

多列排序，多列排序排序的列之间需用逗号(,)隔开。在进行多列排序时，第一列必须有相同的列值，才会对第二列进行排序,否则将不在对第二列进行排序。

-- 先按buyPrice排序，再按productName排序。
SELECT buyPrice,productName FROM products ORDER BY buyPrice,productName
-- 多列排序之间，可以单独指定是升序，还是降序进行排序
SELECT buyPrice,productName FROM products ORDER BY buyPrice DESC,productName ASC

GROUP BY 分组查询

创建分组

GROUP BY关键字对数据进行分组，通常和集合函数一起使用。如：
MAX()计算当前列的最大值,
MIN()获取改列最小值,
COUNT()使用COUNT(*)记录查询的结果共有多少行,COUNT(列名)计算非空列的个数，
SUM()获取一列中所有值的和,
AVG()函数通过计算返回的行数和每一行数据的和，获得指定列数据的平均值。

-- 根据productScale对表products数据进行分组
-- 使用COUNT()函数显示不同productScale之间各有多少个
-- 这里AS是为字段取别名
SELECT productScale,COUNT(*) AS Total  FROM products GROUP BY productScale
-- 使用GROUP_CONCAT()函数将分组中各个字段的值显示出来
SELECT productScale,GROUP_CONCAT(buyPrice) AS Price FROM products GROUP BY productScale

使用HAVING过滤分组

GROUP BY可以和HAVING一起限定显示记录所需满足的条件，只有满足条件的分组才会被显示。

-- HAVING 过滤分组
-- GROUP BY可以和 HAVING 一起限定显示记录所需满足的条件，满足条件的分组才会被显示
-- 这里 HAVING COUNT(buyPrice) > 4 是指分组中buyPrice的个数大于4
-- HAVING 和WHERE的区别，HAVING在数据分组之后进行过滤来选择分组，WHERE在分组之前用来选择记录，WHERE排除的记录不再包括在分组中。
-- 执行效率上来说，WHERE 要比HAVING快
SELECT productScale,GROUP_CONCAT(buyPrice) AS Price FROM products GROUP BY productScale HAVING COUNT(buyPrice) > 4

在GROUP BY 语句中使用 WITH ROLLUP

使用WITH ROLLUP关键字之后，在所有查询的分组记录记录之后增加一条记录，计算查询出所有记录的总和。

-- WITH ROLLUP在所有查询出的分组记录之后增加一条记录，计算查询出所有记录的总和
SELECT productScale,COUNT(*) AS Total FROM products GROUP BY productScale WITH ROLLUP

多字段分组

GROUP BY 可以对多个字段进行分组，分组层次为从左到右。

-- 多字段分组，先按第一个再按第二个
SELECT productScale,buyPrice FROM products GROUP BY productScale,buyPrice

使用ORDER BY对分组数据进行排序

-- 使用ORDER BY对分组之后的数据排序。ORDER BY和ROLLUP不能同时使用，二者是相互排斥的。
SELECT productScale,SUM(buyPrice+MSRP) AS Price FROM products GROUP BY productScale HAVING SUM(buyPrice+MSRP) >= 500 ORDER BY Price

使用LIMIT限制查询结果的数量

LIMIT限制查询数量的结果，LIMIT M,N从第M行开始，查询N行。LIMIT N 从第一行开始，查询N行

-- 从第3行开始，查询4行
SELECT productScale,buyPrice FROM products LIMIT 3,4

连接查询

内连接查询

INNER JOIN使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组成新记录。也就是内连接查询中，只有满足条件的记录才出现在结果关系中。

-- 连接查询的条件使用ON连接条件
-- 内连接查询INNER JOIN,INNER JOIN 等同于WHERE。
-- 对比等值连接，使用INNER JOIN的好处在于可以更好的明确数据表的连接方式，同时，使用ON作为连接条件也能更清楚的知道是使用的是多表连接。
-- 这里AS为表取别名
SELECT p.productLine,p.buyPrice,s.htmlDescription,s.image
FROM products AS p INNER JOIN productlines AS s ON p.productLine = s.productLine
-- 等同于
SELECT p.productLine,p.buyPrice,s.htmlDescription,s.image
FROM products AS p, productlines AS s WHERE p.productLine = s.productLine

外连接查询

LEFT JION左连接：返回包括左表中的所有记录和右表中连接字段相等的记录。
RIGHT JION右连接：返回包括数据表中的所有记录和左表中连接字段相等的记录。

-- LEFT JOIN返回包含左表中的所有记录和右表中连接字段相等的记录
-- 这里返回products表（即左表）中所有的数据，productlines表（即右表）只显示符合条件的记录，不足条件的返回NULL。
-- 返回的记录数大于等于左表的记录数。比如p.productLine = s.productLine中，s.productLine中有3条和p.productLine相等，
-- 而p.productLine只有2条，则会返回3条记录数，因为p.productLine被s.productLine匹配了3次。
-- 这里p.productLine = s.productLine并不一定要是两个表中相同的字段，也可以是p.productLine = s.buyPrice
SELECT * FROM products AS p LEFT JOIN productlines AS s ON p.productLine = s.productLine
-- RIGHT JOIN返回包含右表中的所有记录和左表中连接字段相等的记录。是LEFT JOIN的反向连接。
SELECT * FROM products AS p RIGHT JOIN productlines AS s ON p.productLine = s.productLine

子查询

ANY、SOME、ALL关键字子查询

ANY、SOME表示满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。

-- 子查询
CREATE TABLE subQuery1(num1 INT(11) NOT NULL)
CREATE TABLE subQuery2(num2 INT(11) NOT NULL)
INSERT INTO subQuery1 VALUES (2),(4),(6),(8),(10)
INSERT INTO subQuery2 VALUES (1),(3),(5),(7),(9)
-- 查询的num2只要大于subQuery1表中任何一个num1就返回记录
SELECT * FROM subQuery2 WHERE num2 > ANY (SELECT num1 FROM subQuery1)
SELECT * FROM subQuery2 WHERE num2 > SOME (SELECT num1 FROM subQuery1)
-- 查询的num1要大于所有subQuery2表中num2就返回记录
SELECT * FROM subQuery1 WHERE num1 > ALL (SELECT num2 FROM subQuery2)

EXISTS关键字子查询

-- EXISTS 如果存在则返回true。EXISTS 返回的为true时，再对外层语句进行查询，否则不再对外层语句进行查询。
-- 若productlines表中存在productLine = 'land'，则返回true
SELECT * FROM products WHERE EXISTS(SELECT productLine FROM productlines WHERE productLine = 'land')
-- EXISTS和条件表达式一起使用
SELECT * FROM products WHERE buyPrice > 100 AND EXISTS(SELECT productLine FROM productlines WHERE productLine = 'land')
-- NOT EXISTS如果不存在则返回true
SELECT * FROM products WHERE NOT EXISTS(SELECT productLine FROM productlines WHERE productLine = 'land')

带IN关键字子查询

使用IN关键字子查询时，先执行内查询，再执行外查询，内查询的结果作为外查询的比较条件。
NOT IN与IN正好相反。

-- 查询productlines表中productLine = 'land'的记录，并根据查询记录，查询products表中productLine字段存在相同值的记录。
SELECT * FROM products WHERE productLine IN(SELECT productLine FROM productlines WHERE productLine = 'land')
-- 查询products表中productLine字段不存在子查询的结果中的记录
SELECT * FROM products WHERE productLine NOT IN(SELECT productLine FROM productlines WHERE productLine = 'land')

带比较符的子查询

子查询时可以使用的比较运算符有：<,<=,>,>=,=和!=等。

-- 带比较运算符的子查询
SELECT * FROM products WHERE productLine = (SELECT productLine FROM productlines WHERE productLine = 'land')

UNION 合并查询结果

-- UNION ALL合并查询结果。使用ALL不去重,这里把productScale的查询结果合并到buyPrice的查询。
SELECT buyPrice FROM products WHERE buyPrice > 100 UNION ALL SELECT productScale FROM products WHERE productScale = '1:10'
-- 不使用ALL，则去重复，返回的数据都是唯一的。
SELECT buyPrice FROM products WHERE buyPrice > 100 UNION SELECT productScale FROM products WHERE productScale = '1:10'

REGEXP正则表达式查询

REGEXP关键字指定正则表达式的字符匹配模式。正则表达式详见正则表达式

-- 查询products表中productName字段以18开头的记录
SELECT buyPrice,productName FROM products WHERE productName REGEXP '^18'

INSERT INTO 插入数据

INSERT INTO关键字插入数据，指定表名称和插入到新记录中的值。

-- 插入数据
-- 指定字段插入数据,VALUES 后括号里的数据要与表后要插入数据的字段一一对应.如果为所有字段插入数据则(num1)可以省略。
-- 若插入多条数据，则要插入的数据之间是用逗号(,)隔开.
INSERT INTO subQuery1(num1) VALUES (12)
-- 将查询结果插入到subQuery1表
INSERT INTO subQuery1 SELECT num2 FROM subQuery2 WHERE num2 = 9

UPDATE SET 更新数据

UPDATE SET更新特定的行或者同时更新所有的行。

UPDATE subQuery1 SET num1 = 14 WHERE num1 = 12

DELETE 删除数据

DELETE语句允许WHERE关键字指定删除条件。如果没有WHERE子句，则DELETE将删除表中的所有记录。

DELETE FROM subQuery1 WHERE num1 = 14