MySQL常用的查询操作

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 常用的操作

• 查看当前的数据库

SELECT database();

• 结果

• 查询当前数据库版本

SELECT version();

• 结果

• 查看当前时间

SELECT now();

• 结果

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

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

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

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

2.1 条件查询

• 示例：查询工资大于11000的员工。

SELECT uid id, name, salary FROM staffs WHERE salary > 11000;

• 示例：查询工资在6500~8000之间的员工。

SELECT uid, name, salary FROM staffs WHERE salary > 6500 AND salary < 8000;

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

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

2.2 分组查询

• 1.示例：分别统计男女员工的数量

SELECT gender, COUNT(1) FROM staffs GROUP BY gender;

• 结果

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

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

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

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

• 以上两种写法，输出的结果相同

---

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

• 2.示例：分组统计男女员工的平均年龄

SELECT gender, AVG(age) avg_age FROM staffs GROUP BY gender;

• 结果

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

• 3.示例：统计年龄大于20的男性员工

SELECT gender, COUNT(1) num FROM staffs GROUP BY gender HAVING gender = 'male';

• 结果

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

2.3 排序

• 1.示例：按照员工工资升序排列。

SELECT uid, name, salary FROM staffs ORDER BY salary ASC;

• 2.示例：按照员工工资降序排列

SELECT uid, name, salary FROM staffs ORDER BY salary DESC;

• 3.示例：多字段排序，按照年龄降序排序，工资升序排序

SELECT * FROM staffs ORDER BY age DESC, salary ASC;

• 结果

2.4 分页查询

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

• 示例：将数据分成每页5条，查询第3页的数据

SELECT * FROM staffs LIMIT 5 OFFSET 10;

上述sql语句可以简写：

SELECT * FROM staffs LIMIT 5, 10;

• 结果

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

offset = (page - 1) * num

关于分页查询的总结：

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

2.5 子查询

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

SELECT * FROM staffs ORDER BY salary DESC LIMIT 1;

• 结果

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

SELECT * FROM staffs WHERE salary = (SELECT MAX(salary) FROM staffs);

• 结果

2.6 集合查询

• 示例: 查询uid为1、3、5的用户信息

SELECT uid, name FROM staffs WHERE uid IN(1, 3, 5);

• 结果

2.7 模糊查询

• 示例：匹配用户姓名中包含a的员工

SELECT uid, name FROM staffs WHERE name LIKE 'a%';

• 结果

• 示例：匹配用户姓名第二个字母为i的员工

SELECT uid, name FROM staffs WHERE name LIKE '_i%';

• 结果

2.8 关联查询

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

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

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

• 表结构和数据

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

-- 建表语句
CREATE TABLE categories (
  cid INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL COMMENT '栏目名称',
  PRIMARY KEY (cid)
) ENGINE = INNODB COLLATE = utf8mb4_unicode_ci;
-- 插入数据
INSERT INTO categories (name) VALUES ('国内新闻'),('国际新闻'),('娱乐新闻'),('军事新闻');

• 表结构和数据

2.8.4 内连接

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

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

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

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

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

如果只看国内新闻：

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

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

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

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

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

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

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

2.8.5 外连接

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

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

• 左外连接

SELECT * FROM articles a LEFT JOIN categories c ON a.cid = c.cid;

• 右外连接

SELECT * FROM articles a RIGHT JOIN categories c ON a.cid = c.cid;

---

• 左、右外连接转内连接

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

左外连接转内连接

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

右外连接转内连接

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

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

---

2.8.6 自然连接

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

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

三、视图

3.1 创建视图

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

3.2 更新视图

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

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

3.3 删除视图

DROP VIEW v_staffs;

四、索引

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

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

4.1 创建索引

4.1.1 创建一个普通索引

-- 创建索引
CREATE INDEX 索引名称 ON 表名(字段名);
-- 示例
CREATE INDEX i_email ON staffs(email);

4.1.2 创建唯一索引

CREATE UNIQUE INDEX i_unique_email ON staffs(email);

4.1.3 创建主键索引

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

4.2 删除索引

-- 删除索引
DROP INDEX 索引名称 ON 表名;
-- 示例
DROP INDEX i_email ON staffs;

4.3 查看索引

-- 查看索引
SHOW INDEX FROM 表名;
-- 示例
SHOW INDEX FROM staffs;

五、预处理

• 防止SQL注入攻击；
• SQL语句中的数据，只有在执行阶段再与字段绑定。

-- 生成预处理sql语句
PREPARE STMT FROM 'SELECT uid, name, salary FROM staffs WHERE salary > ? LIMIT ?;';
-- 将真实数据绑定到预处理语句的占位符(?)上
-- SET @salary = 5000;
-- SET @LIMIT = 10;
-- 可以写到一行代码里
SET @salary = 5000, @LIMIT = 10;
-- 执行
EXECUTE STMT USING @salary, @LIMIT;