首页 > 数据库 >MySQL语法进阶

MySQL语法进阶

时间：2024-12-20 20:21:16浏览次数：3  
标签：进阶 查询 语法 books btypeid MySQL WHERE id SELECT

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)

标签：进阶,查询,语法,books,btypeid,MySQL,WHERE,id,SELECT
From： https://www.cnblogs.com/cloudwangsa/p/18619924

相关文章

  • MySQL索引和外键
    MySQL索引和外键索引介绍索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。优点：加快搜索速度，减少查询时间。缺点：索引是以文件存储的。如果......
  • MySQL存储过程
    MySQL存储过程MySQL存储过程（StoredProcedure）是一组预编译的SQL语句，它们存储在数据库中并且可以被重复调用。存储过程帮助简化复杂的SQL查询和提高代码复用性，也可以提升性能，因为数据库服务器可以优化存储过程中的SQL语句执行。查看存储过程mysql>select*frommysql.p......
  • MySQL触发器和事务
    MySQL触发器和事务触发器（Trigger）触发器是一种自动化的数据库对象，它在执行特定的SQL操作（如INSERT、UPDATE或DELETE）时被自动触发。它可以在表上定义，并在特定事件发生时执行自定义的SQL语句。触发器的种类和时机在实际使用中，MySQL所支持的触发器有三种：INSERT触发器、UPD......
  • 如何在电脑上安装MYSQL数据库，软件Navicat
    1.电脑上如何按装MYSQL数据库（8.0.40版本）(以windows系统和MYSQL数据库8.0.40版本为例）首先，为了电脑安全的问题，我们可以在Google以及其他浏览器上，进行百度搜索“mysql”以及mysql数据库的官网网址（https://www.mysql.com/）进入界面之后，哇都是英文哈哈，不过没有关系，跟上我的脚步，在页面......
  • 【机器学习】股票价格预测：基于LSTM模型的完整实现与优化（附可运行代码及进阶操作）
    引言股票价格预测是一个复杂且具有挑战性的任务，传统的预测方法往往难以捕捉股票价格中的复杂关系。LSTM（长短期记忆网络）作为一种特殊的递归神经网络，因其能够处理时间序列中的长依赖问题，成为股票价格预测的有力工具。本文将详细介绍一个基于LSTM模型的股票价格预测项目，并结合实......
  • TS 原理详细解读(6)--语法增量解析
    呃....4年前开了一个坑，准备写一套完整介绍TS原理的文章。坑很大，要慢慢填，今天就来填一个把。本节主要介绍语法增量解析。什么是增量解析增量解析的意思是，如果我们直接从源码解析成语法树，叫做全量解析。语法树是由很多个节点对象组成的，比较吃内存。当用户修改源码后（无论修改哪......
  • 聊一聊坑人的 C# MySql.Data SDK
    聊一聊坑人的C#MySql.DataSDK 一：背景1.讲故事为什么说这东西比较坑人呢？是因为最近一个月接到了两个dump，都反应程序卡死无响应，最后分析下来是因为线程饥饿导致，那什么原因导致的线程饥饿呢？进一步分析发现罪魁祸首是 MySql.Data，这就让人无语了，并且反馈都是升级了MySql.Da......
  • Mysql（三）--事务 & 隔离级别 & MVCC
    什么是数据库事务？数据库事务就是一组数据库操作，要么全部成功要么全部失败。特性事务有四个特性（ACID），并且四个特性都必须要满足：（特性其实也就是特点）原子性（Atomicity）：一组操作，要么全部成功、要么全部失败。一致性（Consistency）：事务执行前与执行后数据完整性是一致的，没有收到破坏。......
  • mysql批量更新数据库内全部表某个字符串为另外一个字符串
    示例：把指定数据库minex-pms所有表中的project_depart_name字段重庆项目部更改为渝北项目部，不存在project_depart_name字段的表排除掉，使用临时表和sql脚本的方式实现sql脚本：--1.创建实体表来存储需要更新的表名CREATETABLEIFNOTEXISTStables_to_update(table_nam......
  • MySQL 数据插入慢
    问题背景使用MybatiPlus批量插入数据，497条数据居然花费了16264毫秒，完全不可接受！  解决方案 核心mybatis-plus批量插入需要添加rewriteBatchedStatements配置mybatis-plus批量插入的性能受事务的影响  方案一MySQLJDBC驱动在默认情况下会忽视executeBatch......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99