search

博客列表 >MySQL常用的查询操作

MySQL常用的查询操作

残破的蛋蛋
残破的蛋蛋原创
2021年03月10日 11:55:441609浏览

MySQL常用的查询操作

一、MySQL查询语法

MySQL数据库使用SELECT语句来查询数据。

1.1 语法

SELECT column_name,column_name
FROM table_name [WHERE Clause][LIMIT N][ OFFSET M]

  • MySQL查询可以使用一个或者多个表，表与表之间需要用逗号(,)分割开，并且需要使用WHERE语句设置查询条件；
  • SELECT可以读取一条或多条记录；
  • 星号(*)表示SELECT语句会返回数据表的所有字段；
  • WHERE语句是用来设置查询条件的；
  • LIMIT用于限制返回的数据条数。
  • OFFSET用于指定SELECT语句开始查询数据时的偏移量，默认值为0。

1.2 常用的操作

  • 查看当前的数据库
  1. SELECT database();
  • 结果

查看当前数据库

  • 查询当前数据库版本
  1. SELECT version();
  • 结果

查看当前数据库版本

  • 查看当前时间
  1. SELECT now();
  • 结果

查看当前时间

二、常用的查询语句及示例

新建一张员工（staffs）表，并插入一下数据，建表语句如下：

  1. CREATE TABLE `users` (
  2.   `uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  3.   `name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '姓名',
  4.   `age` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '年龄',
  5.   `gender` enum('male','female') COLLATE utf8mb4_unicode_ci NOT NULL,
  6.   `salary` int(10) unsigned NOT NULL DEFAULT '2000',
  7.   `email` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '邮箱',
  8.   `birthday` date NOT NULL COMMENT '生日',
  9.   `create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建日期',
  10.   `update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改日期',
  11.   `test` int(10) unsigned NOT NULL DEFAULT '1',
  12.   PRIMARY KEY (`uid`)
  13. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

设计表

数据表建完之后插入一些数据：

建表

2.1 条件查询

  • 示例：查询工资大于11000的员工。
  1. SELECT uid id, name, salary FROM staffs WHERE salary > 11000;
  • 示例：查询工资在6500~8000之间的员工。
  1. SELECT uid, name, salary FROM staffs WHERE salary > 6500 AND salary < 8000;

注意： 以上方法查询的数据是不包含6500和8000这两个值的，也就是说不包含边界，如果要想使查询的结果包含边界值，应该使用BETWEEN...AND。

  1. SELECT uid, name, salary FROM staffs WHERE BETWEEN 6500 AND 8000;

2.2 分组查询

  • 1.示例：分别统计男女员工的数量
  1. SELECT gender, COUNT(1) FROM staffs GROUP BY gender;
  • 结果

分别统计男女员工的数量

但是gender和COUNT(1)看起来不直观，我们可以改个名字，sql语句需要这样写：

  1. SELECT gender AS 性别, COUNT(1) AS 数量 FROM staffs GROUP BY gender;

以上sql语句还有一个简写的方法，省略AS：

  1. SELECT gender 性别, COUNT(1) 数量 FROM staffs GROUP BY gender;
  • 以上两种写法，输出的结果相同

使用别名统计男女员工数量

聚合函数：max()、min()、avg()、count()

  • 2.示例：分组统计男女员工的平均年龄
  1. SELECT gender, AVG(age) avg_age FROM staffs GROUP BY gender;
  • 结果

AVG

上面计算的avg_age值还可以使用ROUND()函数保留两位小数，ROUND(AVG(age))。

  • 3.示例：统计年龄大于20的男性员工
  1. SELECT gender, COUNT(1) num FROM staffs GROUP BY gender HAVING gender = 'male';
  • 结果

having

分组条件，只能使用HAVING，不能使用WHERE ，因为WHERE只能从现存的字段中作为条件。

2.3 排序

  • 1.示例：按照员工工资升序排列。
  1. SELECT uid, name, salary FROM staffs ORDER BY salary ASC;

升序排序

  • 2.示例：按照员工工资降序排列
  1. SELECT uid, name, salary FROM staffs ORDER BY salary DESC;

降序排序

  • 3.示例：多字段排序，按照年龄降序排序，工资升序排序
  1. SELECT * FROM staffs ORDER BY age DESC, salary ASC;
  • 结果

多字段排序

2.4 分页查询

当我们查询出来的数据量太大的时候，一页展示的又太多，一般情况下都会将其分成N页，那么这时候就需要用到分页查询。

  • 示例：将数据分成每页5条，查询第3页的数据
  1. SELECT * FROM staffs LIMIT 5 OFFSET 10;

上述sql语句可以简写：

  1. SELECT * FROM staffs LIMIT 5, 10;
  • 结果

分页查询

至于每一页的其实偏移量应该怎么计算，这里其实有一个公式，假设每页的数据条数为num，当前查询的页码为page，那么，该公式可总结为：

offset = (page - 1) * num

关于分页查询的总结：

1. LIMIT 显示数量 OFFSET 偏移量（跳过的记录数量）;
2. LIMIT 偏移量， 显示数量（这是简写）;
3. LIMIT 子句一定要凡在SELECT的最后面。

2.5 子查询

查询工资最高的员工信息，通常的做法是：

  1. SELECT * FROM staffs ORDER BY salary DESC LIMIT 1;
  • 结果

ORDER BY

这样肯定是可以查询出来工资最高的员工信息的，但是还可以利用子查询来查询。

  1. SELECT * FROM staffs WHERE salary = (SELECT MAX(salary) FROM staffs);
  • 结果

子查询

2.6 集合查询

  • 示例: 查询uid为1、3、5的用户信息
  1. SELECT uid, name FROM staffs WHERE uid IN(1, 3, 5);
  • 结果

集合查询 IN

2.7 模糊查询

  • 示例：匹配用户姓名中包含a的员工
  1. SELECT uid, name FROM staffs WHERE name LIKE 'a%';
  • 结果

LIKE

  • 示例：匹配用户姓名第二个字母为i的员工
  1. SELECT uid, name FROM staffs WHERE name LIKE '_i%';
  • 结果

LIKE

2.8 关联查询

关联查询也叫多表查询，下面再创建两张表作为示例数据。

2.8.1 创建一张文章表(article)并插入一些数据

  1. -- 建表语句
  2. CREATE TABLE articles (
  3.   aid INT UNSIGNED NOT NULL AUTO_INCREMENT,
  4.   title VARCHAR(100) NOT NULL COMMENT '文章标题',
  5.   cid INT UNSIGNED NOT NULL COMMENT '栏目ID'
  6.   PRIMARY KEY (aid)
  7. ) ENGINE = InnoDB COLLATE = utf8mb4_unicode_ci;
  9. -- 插入数据
  10. INSERT INTO articles (title, cid) VALUES
  11. ('探索废除生育限制，为何是东北？', 1),
  12. ('8210亿元！春节零售餐饮消费迎开门红', 1),
  13. ('为防控疫情，美加墨陆地边界通行限制再延一月', 2),
  14. ('得州宣布重大灾害700万人遇新危机', 2);
  • 表结构和数据

创建articles表

向文章表插入数据

2.8.2 创建一张栏目表(categories)并插入一些数据

  1. -- 建表语句
  2. CREATE TABLE categories (
  3.   cid INT UNSIGNED NOT NULL AUTO_INCREMENT,
  4.   name VARCHAR(100) NOT NULL COMMENT '栏目名称',
  5.   PRIMARY KEY (cid)
  6. ) ENGINE = INNODB COLLATE = utf8mb4_unicode_ci;
  8. -- 插入数据
  9. INSERT INTO categories (name) VALUES ('国内新闻'),('国际新闻'),('娱乐新闻'),('军事新闻');
  • 表结构和数据

创建categories表

向categories表插入数据

2.8.4 内连接

现在我想让articles表中的cid字段显示新闻栏目的中文名称，如下图示例这样：

更改字段显示

首先，不用内连接的方式，sql语句应该这样写：

  1. SELECT a.aid, a.title, c.name FROM articles a, categories c WHERE a.cid = c.cid;

由于两张表的字段aid, title, name都不一样因此可以不用在字段前面加上表名限制，所以上面的语句还可以这样简写：

  1. SELECT aid, title, name FROM articles a, categories c WHERE a.cid = c.cid;

查询结果

如果只看国内新闻：

  1. SELECT aid, title, name FROM articles a, categories c WHERE a.cid = c.cid AND a.cid = 1;

查询国内新闻

上述写法有点繁琐，可以使用内连接改进(INNER JOIN + ON)：

  1. SELECT aid, title, name FROM articles a INNER JOIN categories c ON a.cid = c.cid;
  3. -- 默认就是内连接，因此INNER可省略
  4. SELECT aid, title, name FROM articles a JOIN categories c ON a.cid = c.cid;

如果我还是想只获取国内新闻，那么应该这样写：

  1. SELECT aid, title, name FROM articles a JOIN categories c ON a.cid = c.cid WHERE a.cid = 1;

如果多表关联字段同名，可以使用关键字USING简化：

  1. -- 关联的两张表cid字段一样
  2. SELECT aid, title, name FROM articles a JOIN categories c USING(cid);

2.8.5 外连接

首先再往articles表中插入一些演示数据：

  1. INSERT INTO articles(title, cid) VALUES
  2. ('体育教育成两会热点话题：学生只有分数赢不了未来', 7),
  3. ('玩家众筹25000美元做《Dota2》新教程：旧版本过时', 8);

新增数据

  • 左外连接
  1. SELECT * FROM articles a LEFT JOIN categories c ON a.cid = c.cid;

LEFT JOIN

  • 右外连接
  1. SELECT * FROM articles a RIGHT JOIN categories c ON a.cid = c.cid;

RIGHT JOIN

  • 左、右外连接转内连接

原理：通过添加过滤器，过滤掉从表中的某一个为NULL的字段即可。

左外连接转内连接

  1. SELECT * FROM articles a LEFT JOIN categories c ON a.cid = c.cid WHERE c.cid IS NOT NULL;

右外连接转内连接

  1. SELECT * FROM articles a RIGHT JOIN categories c ON a.cid = c.cid WHERE a.cid IS NOT NULL;

以上两种连接转换结果都是一样的：

左、右外连接转内连接

2.8.6 自然连接

自然连接是内连接的一种特例，前提是关联的表中存在同名字段，可以连USING()都省略了，如果不需要使用表别名，在写sql语句的时候，表的别名也可以省略。

  1. SELECT aid, title, name FROM articles NATURAL JOIN categories;

查询结果

三、视图

3.1 创建视图

  1. -- 创建视图
  2. CREATE VIEW v_staffs AS SELECT * FROM staffs;

3.2 更新视图

更新视图，会同步更新基本表数据。

  1. UPDATE v_staffs SET salary = salary + 1000 WHERE uid = 1;

3.3 删除视图

  1. DROP VIEW v_staffs;

四、索引

  • 索引应该创建在经常被查询的字段或者经常出现在结果集的字段上。

  • 索引分类：普通索引、主键索引、唯一索引、全文索引。

4.1 创建索引

4.1.1 创建一个普通索引
  1. -- 创建索引
  2. CREATE INDEX 索引名称 ON 表名(字段名);
  4. -- 示例
  5. CREATE INDEX i_email ON staffs(email);

创建普通索引

4.1.2 创建唯一索引
  1. CREATE UNIQUE INDEX i_unique_email ON staffs(email);

唯一索引

4.1.3 创建主键索引
  1. -- 创建主键索引
  2. ALTER TABLE 表名 ADD PRIMARY KEY 索引名称(字段名);
  4. -- 为主键创建一个索引
  5. ALTER TABLE test ADD PRIMARY KEY i_id(id);

4.2 删除索引

  1. -- 删除索引
  2. DROP INDEX 索引名称 ON 表名;
  4. -- 示例
  5. DROP INDEX i_email ON staffs;

4.3 查看索引

  1. -- 查看索引
  2. SHOW INDEX FROM 表名;
  4. -- 示例
  5. SHOW INDEX FROM staffs;

五、预处理

  • 防止SQL注入攻击；
  • SQL语句中的数据，只有在执行阶段再与字段绑定。
  1. -- 生成预处理sql语句
  2. PREPARE STMT FROM 'SELECT uid, name, salary FROM staffs WHERE salary > ? LIMIT ?;';
  4. -- 将真实数据绑定到预处理语句的占位符(?)上
  5. -- SET @salary = 5000;
  6. -- SET @LIMIT = 10;
  7. -- 可以写到一行代码里
  8. SET @salary = 5000, @LIMIT = 10;
  10. -- 执行
  11. EXECUTE STMT USING @salary, @LIMIT;
上一条：PHP扩展知识：URL相关函数和api接口案例下一条：中了exe病毒文件夹变exe应用程序解决方法
声明：本文内容转载自脚本之家，由网友自发贡献，版权归原作者所有，如您发现涉嫌抄袭侵权，请联系admin@php.cn 核实处理。
全部评论
文明上网理性发言，请遵守新闻评论服务协议
一神2021-03-10 12:42:361楼
很详细 学习了

相关文章

查看更多