MySQL语法进阶

MySQL语法进阶

将表导入数据库

mysql -uroot -ppassword -e 'create database database_name'
mysql -uroot -ppassword databas_name < books_utf8.sql

查询数据

条件查询

SELECT语句可以通过WHERE条件来设定查询条件，查询结果是满足查询条件的记录。例如，要指定条件“分数在80分或以上的学生”，写成WHERE条件就是SELECT * FROM students WHERE score >= 80。

mysql> select * from books where bid <= 3;
+-----+-----------------------------+---------+-----------------------------+-------+------------+-----------+------------+
| bId | bName                       | bTypeId | publishing                  | price | pubDate    | author    | ISBN       |
+-----+-----------------------------+---------+-----------------------------+-------+------------+-----------+------------+
|   1 | 网站制作直通车              | 2       | 电脑爱好者杂志社            |    34 | 2004-10-01 | 苗壮      | 7505380796 |
|   2 | 黑客与网络安全              | 6       | 航空工业出版社              |    41 | 2002-07-01 | 白立超    | 7121010925 |
|   3 | 网络程序与设计－asp         | 2       | 北方交通大学出版社          |    43 | 2005-02-01 | 王玥      | 75053815x  |
+-----+-----------------------------+---------+-----------------------------+-------+------------+-----------+------------+
3 rows in set (0.00 sec)

<条件1> OR <条件2>，表示满足条件1或者满足条件2

mysql> select * from books where bid > 40 or  btypeid !=3;

<条件1> AND <条件2>表达满足条件1并且满足条件2

mysql> select * from books where bid <= 3 and bid >=3;
+-----+-----------------------------+---------+-----------------------------+-------+------------+--------+-----------+
| bId | bName                       | bTypeId | publishing                  | price | pubDate    | author | ISBN      |
+-----+-----------------------------+---------+-----------------------------+-------+------------+--------+-----------+
|   3 | 网络程序与设计－asp         | 2       | 北方交通大学出版社          |    43 | 2005-02-01 | 王玥   | 75053815x |
+-----+-----------------------------+---------+-----------------------------+-------+------------+--------+-----------+
1 row in set (0.01 sec)

NOT <条件>，表示“不符合该条件”的记录。

mysql> select * from books where not bid>3;
+-----+-----------------------------+---------+-----------------------------+-------+------------+-----------+------------+
| bId | bName                       | bTypeId | publishing                  | price | pubDate    | author    | ISBN       |
+-----+-----------------------------+---------+-----------------------------+-------+------------+-----------+------------+
|   1 | 网站制作直通车              | 2       | 电脑爱好者杂志社            |    34 | 2004-10-01 | 苗壮      | 7505380796 |
|   2 | 黑客与网络安全              | 6       | 航空工业出版社              |    41 | 2002-07-01 | 白立超    | 7121010925 |
|   3 | 网络程序与设计－asp         | 2       | 北方交通大学出版社          |    43 | 2005-02-01 | 王玥      | 75053815x  |
+-----+-----------------------------+---------+-----------------------------+-------+------------+-----------+------------+
3 rows in set (0.00 sec)

使用<>判断不相等	score <> 80	name <> 'abc'	
使用LIKE判断相似	name LIKE 'ab%'	name LIKE '%bc%'	%表示任意字符，例如'ab%'将匹配'ab'，'abc'，'abcd'

投影查询

如果我们只希望返回某些列的数据，而不是所有列的数据，我们可以用SELECT 列1, 列2, 列3 FROM ...，让结果集仅包含指定列。这种操作称为投影查询。

例如，从students表中返回id、score和name这三列：

SELECT id, score, name FROM students;

给列起别名

使用SELECT 列1, 列2, 列3 FROM ...时，还可以给每一列起个别名，这样，结果集的列名就可以与原表的列名不同。它的语法是SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM ...

mysql> select bid id from books where bid <3;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

排序

我们使用SELECT查询时，细心的读者可能注意到，查询结果集通常是按照id排序的，也就是根据主键排序。这也是大部分数据库的做法。如果我们要根据其他条件排序怎么办？可以加上ORDER BY子句。例如按照成绩从低到高进行排序

SELECT id, name, gender, score FROM students ORDER BY score;

如果要反过来，按照成绩从高到底排序，我们可以加上DESC表示“倒序”：

SELECT id, name, gender, score FROM students ORDER BY score DESC;

如果score列有相同的数据，要进一步排序，可以继续添加列名。例如，使用ORDER BY score DESC, gender表示先按score列倒序，如果有相同分数的，再按gender列排序：

SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;

如果有WHERE子句，那么ORDER BY子句要放到WHERE子句后面。例如，查询一班的学生成绩，并按照倒序排序：

SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;

分页查询

使用SELECT查询时，如果结果集数据量很大，比如几万行数据，放在一个页面显示的话数据量太大，不如分页显示，每次显示100条。

要实现分页功能，实际上就是从结果集中显示第1100条记录作为第1页，显示第101200条记录作为第2页，以此类推。

因此，分页实际上就是从结果集中“截取”出第M~N条记录。这个查询可以通过LIMIT <N-M> OFFSET <M>子句实现。

每页3条记录。要获取第1页的记录，可以使用LIMIT 3 OFFSET 0：

SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;

上述查询LIMIT 3 OFFSET 0表示，对结果集从0号记录开始，最多取3条。注意SQL记录集的索引从0开始。

如果要查询第2页，那么我们只需要“跳过”头3条记录，也就是对结果集从3号记录开始查询，把OFFSET设定为3：

SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 3;

聚合查询

对于统计总数、平均数这类计算，SQL提供了专门的聚合函数，使用聚合函数进行查询，就是聚合查询，它可以快速获得结果。

mysql> select count(*) from books;
+----------+
| count(*) |
+----------+
|       44 |
+----------+
1 row in set (0.00 sec)

聚合查询同样可以使用WHERE条件，因此我们可以方便地统计出有多少男生、多少女生、多少80分以上的学生等：

SELECT COUNT(*) boys FROM students WHERE gender = 'M';

除了COUNT()函数外，SQL还提供了如下聚合函数：

函数 说明
SUM 计算某一列的合计值，该列必须为数值类型
AVG 计算某一列的平均值，该列必须为数值类型
MAX 计算某一列的最大值
MIN 计算某一列的最小值

分组查询

如果我们要统计一班的学生数量，我们知道，可以用SELECT COUNT(*) num FROM students WHERE class_id = 1;。如果要继续统计二班、三班的学生数量，难道必须不断修改WHERE条件来执行SELECT语句吗？

对于聚合查询，SQL还提供了“分组聚合”的功能。

SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;

也可以使用多个列进行分组。例如，我们想统计各班的男生和女生人数：

SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;

多表查询

SELECT查询不但可以从一张表查询数据，还可以从多张表同时查询数据。查询多张表的语法是：SELECT * FROM <表1> <表2>。

分表查询的同时给列起别名：

mysql> SELECT books.btypeid b_btypeid, category.btypeid c_btypeid FROM books, category limit 3;
+-----------+-----------+
| b_btypeid | c_btypeid |
+-----------+-----------+
| 2         |         1 |
| 2         |         2 |
| 2         |         3 |
+-----------+-----------+
3 rows in set (0.00 sec)

注意，多表查询时，要使用表名.列名这样的方式来引用列和设置别名，这样就避免了结果集的列名重复问题。但是，用表名.列名这种方式列举两个表的所有列实在是很麻烦，所以SQL还允许给表设置一个别名，让我们在投影查询中引用起来稍微简洁一点：

mysql> SELECT b.btypeid b_btypeid, c.btypeid c_btypeid FROM books b, category c limit 3;
+-----------+-----------+
| b_btypeid | c_btypeid |
+-----------+-----------+
| 2         |         1 |
| 2         |         2 |
| 2         |         3 |
+-----------+-----------+
3 rows in set (0.00 sec)

多表查询也是可以添加WHERE条件的

mysql> SELECT b.btypeid b_btypeid, c.btypeid c_btypeid FROM books b, category c  where b.btypeid=1 limit 3;
+-----------+-----------+
| b_btypeid | c_btypeid |
+-----------+-----------+
| 1         |         1 |
| 1         |         1 |
| 1         |         1 |
+-----------+-----------+
3 rows in set (0.00 sec

连接查询

连接查询是另一种类型的多表查询。连接查询对多个表进行JOIN运算，简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。

内连接——INNER JOIN来实现：

mysql> select b.bname,b.bid,b.btypeid,c.btypename from books b inner join category c on b.btypeid = c.btypeid limit 3;
+-----------------------------+-----+---------+-----------+
| bname                       | bid | btypeid | btypename |
+-----------------------------+-----+---------+-----------+
| 网站制作直通车              |   1 | 2       | 网站      |
| 黑客与网络安全              |   2 | 6       | 黑客      |
| 网络程序与设计－asp         |   3 | 2       | 网站      |
+-----------------------------+-----+---------+-----------+
3 rows in set (0.00 sec)

注意INNER JOIN查询的写法是：

  1. 先确定主表，仍然使用FROM <表1>的语法；
  2. 再确定需要连接的表，使用INNER JOIN <表2>的语法；
  3. 然后确定连接条件，使用ON <条件...>，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接；
  4. 可选：加上WHERE子句、ORDER BY等子句。

外连接--OUTER JOIN

mysql> select b.bname,b.bid,b.btypeid,c.btypename from books b  right  outer  join category c on b.btypeid = c.btypeid limit 40,48;
+----------------------------------------------------+------+---------+---------------+
| bname                                              | bid  | btypeid | btypename     |
+----------------------------------------------------+------+---------+---------------+
| SQL Server 2000 从入门到精通                       |   41 | 1       | windows应用   |
| SQL Server 7.0数据库系统管理与应用开发             |   42 | 1       | windows应用   |
| ASP 3初级教程                                      |   43 | 2       | 网站          |
| XML 完全探索                                       |   44 | 2       | 网站          |
| NULL                                               | NULL | NULL    | 阿斯顿        |
+----------------------------------------------------+------+---------+---------------+
5 rows in set (0.01 sec)


mysql> select b.bname,b.bid,b.btypeid,c.btypename from books b  left  outer  join category c on b.btypeid = c.btypeid limit 40,48;
+----------------------------------------------------+-----+---------+---------------+
| bname                                              | bid | btypeid | btypename     |
+----------------------------------------------------+-----+---------+---------------+
| SQL Server 2000 从入门到精通                       |  41 | 1       | windows应用   |
| SQL Server 7.0数据库系统管理与应用开发             |  42 | 1       | windows应用   |
| ASP 3初级教程                                      |  43 | 2       | 网站          |
| XML 完全探索                                       |  44 | 2       | 网站          |
+----------------------------------------------------+-----+---------+---------------+
4 rows in set (0.00 sec)

有RIGHT OUTER JOIN，就有LEFT OUTER JOIN，以及FULL OUTER JOIN。它们的区别是：

INNER JOIN只返回同时存在于两张表的行数据，由于students表的class_id包含1，2，3，classes表的id包含1，2，3，4，所以，INNER JOIN根据条件s.class_id = c.id返回的结果集仅包含1，2，3。

RIGHT OUTER JOIN返回右表都存在的行。如果某一行仅在右表存在，那么结果集就会以NULL填充剩下的字段。

LEFT OUTER JOIN则返回左表都存在的行。

FULL OUTER JOIN，它会把两张表的所有记录全部选择出来

MySQL 不直接支持 FULL OUTER JOIN。

模糊查询

通配符是% ，模糊查询不能跟=必须跟like

mysql> select * from books where bname like "网%";
+-----+--------------------------------+---------+-----------------------------+-------+------------+-----------+------------+
| bId | bName                          | bTypeId | publishing                  | price | pubDate    | author    | ISBN       |
+-----+--------------------------------+---------+-----------------------------+-------+------------+-----------+------------+
|   1 | 网站制作直通车                 | 2       | 电脑爱好者杂志社            |    34 | 2004-10-01 | 苗壮      | 7505380796 |
|   3 | 网络程序与设计－asp            | 2       | 北方交通大学出版社          |    43 | 2005-02-01 | 王玥      | 75053815x  |
|   7 | 网页样式设计－CSS              | 2       | 人民邮电出版社              |    45 | 2002-03-01 | 张晓阳    | 7505383663 |
|  18 | 网站设计全程教程               | 2       | 科学出版社                  |    50 | 2006-01-01 | 吴守辉    | 7505380796 |
|  22 | 网页界面设计艺术教程           | 2       | 人民邮电出版社              |    54 | 2006-01-01 | 刘刚      | 7505380796 |
+-----+--------------------------------+---------+-----------------------------+-------+------------+-----------+------------+
5 rows in set (0.00 sec)

having和where

WHERE：用于在数据分组之前对查询结果进行筛选，适用于直接对列数据进行筛选。

HAVING：用于在数据分组之后对查询结果进行筛选，通常配合聚合函数一起使用，用来筛选聚合后的结果。

  1. WHERE 示例：

假设你有一个 books 表，并且你想查询价格大于 100 的书籍：

sql复制代码SELECT bname, price
FROM books
WHERE price > 100;
  • 说明：WHERE 在 FROM 阶段应用，用来筛选符合条件的原始数据（如 price > 100）。
  1. HAVING 示例：

假设你想查询每个类别（btypeid）的书籍数量，并且只显示那些类别中书籍数量大于 5 的类别：

sql复制代码SELECT btypeid, COUNT(*) AS book_count
FROM books
GROUP BY btypeid
HAVING COUNT(*) > 5;
  • 说明：HAVING 是在 GROUP BY 后应用的，用来筛选经过 COUNT(*) 聚合后的结果。在这个例子中，HAVING COUNT(*) > 5 用来筛选每个类别中的书籍数量大于 5 的类别。

WHERE 不能与聚合函数一起使用来筛选聚合结果。例如，你不能使用 WHERE COUNT(*) > 5。

HAVING 是用于筛选聚合结果的。你可以在 HAVING 子句中使用聚合函数。

WHERE 用于筛选原始数据，HAVING 用于筛选聚合后的数据。

视图

  • 创建视图

    语法：

 create view 视图名 as  select 语句。
 <视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
 <SELECT语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。


mysql> create view hello as select a.bid,a.bname,a.price,b.btypeid,b.btypename from books a,category b ;^C
mysql> select * from hello limit 1;
+-----+-----------------------+-------+---------+---------------+
| bid | bname                 | price | btypeid | btypename     |
+-----+-----------------------+-------+---------+---------------+
|   1 | 网站制作直通车        |    34 |       1 | windows应用   |
+-----+-----------------------+-------+---------+---------------+
1 row in set (0.00 sec)

删除视图

mysql> drop view hello;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+-------------------------+
| Tables_in_database_name |
+-------------------------+
| books                   |
| category                |
+-------------------------+
2 rows in set (0.00 sec)
posted @   pro111  阅读(6)  评论(0)  编辑  收藏  举报
相关博文：
· MySQL存储过程
· MySQL索引和外键
· MySQL简易教程
· SQL语句用法总结
· 数据库语法
阅读排行：
· 全程不用写代码，我用AI程序员写了一个飞机大战
· DeepSeek 开源周回顾「GitHub 热点速览」
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
点击右上角即可分享
微信分享提示