MySQL入门学习（二）MySQL 基础（一）查询语句

最新推荐文章于 2024-06-25 16:34:04 发布

Jock2018

最新推荐文章于 2024-06-25 16:34:04 发布

阅读量1.1k

点赞数

分类专栏： MySQL 文章标签： MySQL

本文链接：https://blog.csdn.net/qq_27283619/article/details/88982191

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

MySQL入门学习（二）MySQL 基础（一）查询语句）

1. 导入示例数据库

参考教程
第一步：连接到MySQL数据库并创建数据库

C:\Windows\System32>net start mysql  # 启动mysql服务
# cd 到相应的MySQL安装包的bin目录下,这里我卸载了MySQL8.0重装了MySQL5.7
C:\Windows\System32>D:
D:\>cd D:\DatabaseSoftware\MySQL5.7.25\bin
D:\DatabaseSoftware\MySQL5.7.25\bin>mysql -u root -p  # 连接
mysql> CREATE DATABASE IF NOT EXISTS yiibaidb DEFAULT CHARSET utf8 COLLATE utf8_general_ci;  # 创建数据库
 mysql> use yiibaidb;  # 选择数据库
mysql> source ‪D:\DatabaseSoftware\MySQL5.7.25\sqldata\yiibaidb.sql;  # 导入数据
mysql> select city, phone, country from `offices`;  # 测试导入结果

如图：
在这里插入图片描述

2. SQL是什么？MySQL是什么？

SQL和MySQL的区别是SQL是语言，MySQL是数据库管理系统。

2.1 SQL

SQL(发音为字母S-Q-L或者sequel)：Structured Query Language，结构化查询语音。SQL是一门专门用来和数据库进行交流的语言。是我们人类和数据库沟通的桥梁。
SQL不区分大小写，但是为了方便阅读和调试，我们对关键字使用大写，列名和表名使用小写。
SQL的优点：

通用性，几乎适用于所有的数据库。
简单易学，很接近我们人类的语言而且单词很少
灵活，可以通过简单的拼接完成高级复杂的操作。

2.2 MySQL

MySQL是一个关系数据库，也是一个数据库管理系统，开源免费。

3. 查询语句 SELECT FROM （指定列）

3.1 语句解释

SELECT：SQL的一个关键字，作用是从一个或多个表中检索信息。使用SELECT的时候至少要告诉它两个信息：1）从哪里选；2)选什么。

检索单个列

语法：SELECT [列名] FROM [表名]; (后面的分号不要漏了)

检索多个列

语法：SELECT [列名1], [列名2], [列名3] FROM customers; (注意最后一个列名后不加逗号",")

检索所有的列

语法：SELECT * FROM [表名]; (’*'通配符可以表示选取所有的列)

示例

# 检索单个列
mysql> use yiibaidb  # 选定yiibaidb数据库
Database changed
mysql> SELECT phone FROM customers;  # 从yiibaidb数据库中的customers表中取出phone这一列
# 检索多个列
mysql> SELECT phone,city, state, country FROM customers;  # 从yiibaidb数据库中的customers表中取出phone, city, state, country 这几列
# 检索所有列
mysql>SELECT * FROM customers;  # 从yiibaidb数据库中的customers表中取出所有列

3.2 去重语句（删去重复的值)

DISTINCT：关键字，作用使得数据库只返回不同的值。
语法：SELECT DISTINCT [列名] FROM [表名];
注意：不能部分使用DISITINCT，DISDINCT作用于所有的列，不仅是跟在后面的那一列。如果后面的列不同，那么所有的列都会被检索出来，从而去重失效。

mysql> SELECT DISTINCT city FROM customers;

3.3 前N个语句（实现检索任意行）

这里针对的是MySQL数据库，其他数据库有所区别。使用前需要查清楚。
LIMIT：关键字，指示MySQL检索的行数
OFFSET：关键字，指示MySQL从行开始检索。注意行数是从0开始编号，所以LIMIT 1 OFFSET 1会检索第2行，而不是第1行
语法：SELECT [列名] FROM [表名] LIMIT [行数] OFFSET [起始行];
简略写法：SELECT [列名] FROM [表名] LIMIT [起始行], [行数];

mysql> SELECT city FROM customers LIMIT 2;
+-----------+
| city |
+-----------+
| Nantes |
| Las Vegas |
+-----------+
2 rows in set (0.00 sec)

mysql> SELECT city FROM customers LIMIT 2 OFFSET 2;
+-----------+
| city |
+-----------+
| Melbourne |
| Nantes |
+-----------+
2 rows in set (0.00 sec)

mysql> SELECT city FROM customers LIMIT 1,3;
+-----------+
| city |
+-----------+
| Las Vegas |
| Melbourne |
| Nantes |
+-----------+
3 rows in set (0.00 sec)

3.4 CASE…END判断语句

官方文档
 示例教程
这里放出官方文档的两种用法：

# 用法一
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE
# 用法二
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

CASE…END语句可以实现复杂的条件结构，这一点先记下，日后记得可以考虑使用CASE…END

4. 筛选语句 WHERE （指定行）

4.1 语句解释

WHERE：关键字，配合SELECT筛选出我们需要的行。WHERE子句后跟着搜索条件（search criteria)或者叫过滤条件(filter condition)。
语法：SELECT [列名] FROM [表名] WHERE [条件];
解释：从表 [表名]中检索[列名]，然后返回满足[条件]的行。
示例：

mysql> SELECT city FROM customers WHERE city = 'NYC';
+------+
| city |
+------+
| NYC |
| NYC |
| NYC |
| NYC |
| NYC |
+------+

4.2 WHERE子句基础操作符

在这里插入图片描述
注意这个表里面存在冗余，而且不是所有的DBMS都支持这些操作。

检查单个值：
语法：SELECT prod_name, prod_price FROM Products WHERE prod_price <10;
解释：列出所有价格小于10的产品名称和价格。
不匹配检查
语法：SELECT vend_id, prod_name FROM Products WHERE vend_id <> ‘DLL01’;
解释：列出所有除了NLL01供应商制造的供应商和产品名。
**注意：!=和<>在不同的DBMS不同，使用前先查好。
范围检查（BETWEEN…AND)
语法：SELECT prod_name, prod_price FROM Products WHERE prod_price BETWEEN 5 AND 10;
解释：列出所有价格在5-10之间的产品名称和价格。
空值检查（*NULL）
NULL：无值（no value)，它与字段包含0，空字符串或仅仅包含空格不同。
语法：SELECT prod_name FROM Products WHERE prod_price IS NULL;
解释：列出所有没有价格的产品名。注意确定值是否为NULL，不能简单的检查是否=NULL。

注意：NULL 和非匹配
通过过滤选择不包含指定值的所有行时，你可能希望返回含 NULL值
的行。但是这做不到。因为未知（ unknown）有特殊的含义，数据库
不知道它们是否匹配，所以在进行匹配过滤或非匹配过滤时，不会返
回这些结果。
过滤数据时，一定要验证被过滤列中含 NULL的行确实出现在返回的
数据中。

4.3 WHERE子句操作符（AND、NOT、OR、IN）

AND：用在WHERE子句中的关键字，实现检索满足所有给定条件的行。
OR：用在WHERE子句中的关键字，实现检索满足任一条件的行（也遵循短路原则）。
AND 和OR可以任一组合，但是要注意优先级的问题。这个和Python中很像，解决办法是多使用圆括号，确保按照自己希望的顺序执行
IN：用在WHERE子句中的关键字，实现检索指定范围清单的行，功能与OR相当。
引入IN的原因：
1）IN操作符语法更极爱清楚、直观。
2）组合使用AND和OR以及IN时，求值顺序更加容易管理。
3）IN操作符一般比一组OR操作符执行快
4）最大优点是可以包含其他SELECT子句，能够更动态的建立WHERE子句
NOT：用在WHERE子句中的关键字，实现检索不满足给定条件的行。常与IN联用。
小结：AND、NOT、OR、IN和Python中的用法相似，可以类比学习。

4.4 WHERE子句通配符

通配符(wildcard)：用来匹配值的一部分的特殊字符。跟正则表达式里面的类似。
搜索模式（search pattern）：由字面值、通配符或两者组合构成的搜索条件。
谓词（predicate）：从技术上说，LIKE是谓词而不是操作符。虽然最终的结果是相同的。
注意通配符只能用于文本字段（字符串）。
1）百分号（%）通配符——匹配任意字符的任意次数。
语句1：SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE ‘FISH%’;
解释1：列出所有以FISH开头的产品的产品id和产品名。
注意，搜索可能会区分大小写，要根据具体的DBMS而定
语句2：SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE ‘%bean bag%’;
解释2：列出所有中间是bean bag的产品的产品id和产品名。
注意bean bag前或后可以为0个字符
语句3：SELECT prod_name FROM Products WHERE prod_name LIKE ‘F%y’;
解释3：列出所有以F开头y结尾的产品的产品id和产品名。用的不多。
注意常需要函数配合去掉空格
2)下划线（ _ ）通配符——只匹配一个字符，不能多也不能少。
语句：SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE ‘__ inch teddy bear’;
注意：有几个’_'就匹配几个字符，不能多也不能少。
3）方括号[]通配符——匹配[]里面的一个字符
语句：FROM Customers WHERE cust_contact LIKE ‘[JM]%’ ORDER BY cust_contact;
用法和正则里面类似。
小结：慎用通配符，这个跟正则表达式很像，用的时候要考虑性能问题。

5. 分组语句 GROUP BY

5.1 聚集函数

聚集函数（aggregate function）：对某些行运行的函数，计算并返回一个值。处用函数理表中的数据，返回我们需要的结果。
在这里插入图片描述
1） AVG()函数——求平均值
AVG()通过对表中行数计数并计算其列值之和，求得该列的平均值。AVG()可用来返回所有列的平均值，也可以用来返回特定列或行的平均值。
语句：SELECT AVG(prod_price) AS avg_price FROM Products;
解释：此 SELECT语句返回值 avg_price，它包含 Products表中所有产品的平均价格。avg_price是一个别名。
注意：AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个AVG()函数。
AVG()函数忽略列值为NULL的行。
2）COUNT()函数——计数
COUNT()函数进行计数。可利用 COUNT()确定表中行的数目或符合特定条件的行的数目。
两种使用方式：
（1）对行计数——使用 COUNT( * )对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。
（2）对列计数——使用COUNT( column )对特定列中具有值的行进行计数，忽略NULL值。
注意：如果指定列名，则COUNT()函数会忽略指定列的值为空的行，但如果COUNT()函数中用的是星号（ * ），则不忽略。
3）MAX()函数——最大值
MAX()返回指定列中的最大值。MAX()要求指定列名。MAX()函数忽略列值为NULL的行。
4）MIN()函数——最小值
MIN()的功能正好与MAX()功能相反，它返回指定列的最小值。与MAX()一样，MIN()要求指定列名。MIN()函数忽略列值为NULL的行。
5）SUM()函数——求和
SUM()用来返回指定列值的和（总计）。SUM()函数忽略列值为NULL的行。
函数的参数可以是表达式，进行一些运算操作。
5）组合聚集函数

SELECT COUNT(*) AS num_items,
    MIN(prod_price) AS price_min,
    MAX(prod_price) AS price_max,
    AVG(prod_price) AS price_avg
FROM Products;

5.2 语句解释 GROUP BY

分组：使用分组可以将数据分为多个逻辑组，对每个组进行聚集计算。分组是使用SELECT语句的GROUP BY子句建立的。
语句：

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;

解释：SELECT语句指定了两个列：vend_id包含产品供应商的 ID，num_prods为计算字段（用 COUNT(*)函数建立）。GROUP BY子句指示DBMS按vend_id排序并分组数据。这就会对每个vend_id而不是整个表计算 num_prods一次。
使用GROUP BY的几个重要规定：
1）GROUP BY子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组。
2）如果在GROUP BY子句中嵌套了分组，数据将在最后指定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
3）GROUP BY子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
4）大多数 SQL实现不允许GROUP BY列带有长度可变的数据类型（如文本或备注型字段）。
5）除聚集计算语句外，SELECT语句中的每一列都必须在GROUP BY子句
中给出。
6）如果分组列中包含具有NULL值的行，则NULL将作为一个分组返回。
如果列中有多行NULL值，它们将分为一组。
7）GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

5.3 HAVING子句

HAVING：与WHERE类似，二者的区别是，WHERE过滤行,用在分组前，HAVING过滤分组，用在分组后。
语法：

SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

WHERE和HAVING可以一起使用，不过HAVING必须和GROUP BY一起用。

6. 排序语句 ORDER BY

6.1 ORDER BY 语句解释

为了明确地排序用SELECT语句检索出的数据，可使用ORDER BY子句。
语句1：

SELECT prod_name
FROM Products
ORDER BY prod_name;

解释：指示 DBMS软件对prod_name列以字母顺序排序数据。
注意：在指定一条 ORDER BY子句时，应该保证它是 SELECT语句中最后一条子句。
语句2：按多个列排序

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price, prod_name;

解释：先按价格排序，如果价格一样，再按名字排序。如果价格可以排序，就不用比较名字了。
语句3：按位置排序

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY 2, 3;

解释：ORDER BY 2，3表示先按prod_price，再按prod_name进行排序。

6.2 正序（升序）和逆序（降序序）

ASC是升序，默认的是升序，所以一般不指定ASC。要降序必须指定DESC。
语句：

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC, prod_name;

解释：
DESC关键字只应用到直接位于其前面的列名。在上例中，只对prod_price列指定DESC，对prod_name列不指定。因此，prod_price列以降序排序，而prod_name列（在每个价格内）仍然按标准的升序排序。
注意如果想在多个列上进行降序排序，必须对每一列指定DESC关键字。

7. 函数

函数是为了方便数据的转换和处理。函数可移植性差，所以写函数要做好注释工作，方便后来人。

7.1 时间函数、数值函数、字符串函数

在这里插入图片描述

8. SQL注释

出现注释的需求：
1）SQL又长又复杂的语句；
2）SQL文件的描述信息等说明；
3）便于测试SQL语句。
注释的方法：
1）行内注释——使用–(两个连字符)之后的就是注释

SELECT prod_name -- 这是一条注释
FROM Products;

2）行内注释——使用# ，这一整行都是注释（用的比较少）

# 这是一条注释
SELECT prod_name
FROM Products;

3)多行注释——/* … */

/* SELECT prod_name, vend_id
FROM Products; */
SELECT prod_name
FROM Products;

注释从/开始，到/结束，/和/之间的任何内容都是注释。这种方式常用于给代码加注释。

9. SQL代码规范

SQL编程格式的优化建议
 SQL Style Guide

10. 项目练习

10.1 项目一查找重复的电子邮箱（难度：简单）

要求：
创建 email表，并插入如下三行数据
±—±--------+
| Id | c |
±—±--------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
±—±--------+
编写一个 SQL 查询，查找 Email 表中所有重复的电子邮箱。
根据以上输入，你的查询应返回以下结果：
±--------+
| Email |
±--------+
| a@b.com |
±--------+
说明：所有电子邮箱都是小写字母。
解答：
step 1：用Navicat新建一个表
在这里插入图片描述
step 2：手动在表里面输入数据或者使用插入语句一条一条插入

INSERT INTO email VALUES('1','a@a.com')

step3:查询代码及结果

mysql> SELECT Email
    -> FROM email
    -> GROUP BY Email
    -> HAVING COUNT(Email)>=2;
+---------+
| Email |
+---------+
| a@b.com |
+---------+

在Navicat完成如下：
在这里插入图片描述

10.2 项目二查找大国（难度：简单）

要求：
创建如下 World 表
±----------------±-----------±-----------±-------------±--------------+
| name | continent | area | population | gdp |
±----------------±-----------±-----------±-------------±--------------+
| Afghanistan | Asia | 652230 | 25500100 | 20343000 |
| Albania | Europe | 28748 | 2831741 | 12960000 |
| Algeria | Africa | 2381741 | 37100000 | 188681000 |
| Andorra | Europe | 468 | 78115 | 3712000 |
| Angola | Africa | 1246700 | 20609294 | 100990000 |
±----------------±-----------±-----------±-------------±--------------+
如果一个国家的面积超过300万平方公里，或者(人口超过2500万并且gdp超过2000万)，那么这个国家就是大国家。
编写一个SQL查询，输出表中所有大国家的名称、人口和面积。
例如，根据上表，我们应该输出:
±-------------±------------±-------------+
| name | population | area |
±-------------±------------±-------------+
| Afghanistan | 25500100 | 652230 |
| Algeria | 37100000 | 2381741 |
±-------------±------------±-------------+
解答：
step1：创建新表World
在这里插入图片描述
或者语句：

CREATE TABLE World(
name VARCHAR(255) NOT NULL PRIMARY KEY,
continent VARCHAR(255),
area INT,
population INT,
gdp int
);

step2：手动在表里面输入数据或者使用插入语句一条一条插入
代码：

INSERT INTO World VALUES('Afhjamostam','Asia','652230','25500100','20343000');
INSERT INTO World VALUES('Albania','Europe','28748','2831741','12960000');
INSERT INTO World VALUES('Algeria','Africa','2381741','37100000','188681000');
INSERT INTO World VALUES('Andorra','Europe','468','78115','3712000');
INSERT INTO World VALUES('Angola','Africa','1246700','20609294','100990000');

在这里插入图片描述
表中数据：

step3：查找大国
代码

SELECT name,population, area
FROM World 
WHERE (area >3000000) OR (population > 25000000 AND gdp >20000000);

结果：
在这里插入图片描述

11 参考资料

《SQL必知必会第四版》
【MySQL】2.基本语句