[TOC]
开篇
这个文章主要分为2部分,第1部分是一些基础的 SQL 操作,这部分对于程序员来说是必不可少的技能,标准的程序员应该能做到信手拈来,此外这部分 Mysql 官方文档也很详细,可多看看 Mysql 中文教程。第二部分持续更新,记录自己碰到的一些比较“骚”的SQL和自己日常工作中碰到的问题的一些分析。因为本身就都是总结,所以没做总结的部分。文章略长预警!,作为一篇逻辑意义上的“工具文章”来写的,从最小的开始说起,一是为了看看自己对基础掌握的怎么样,二是以后坐地铁无聊的时候刷刷,三是记录一些奇怪的 SQL 题目。
(注:关于 Mysql 的网上安装教程一箩筐,尽君所用 ~)
1. 基础语法
1.1 表的新增、修改、删除
我们可以通过如下命令创建一张表,这张表有那么几个字段 id,user_name,user_account,user_phone
,并且指定了存储引擎和字符编码规则。
此外,我们可以看到基本上每个字段都建立了一些约束,比如 BIGINT/VARCHAR/SMALLINT/INT
等这些表示字段的类型, NOT NULL
表示这个字段不为空,DEFAULT
表示这个字段的默认值, COMMENT
表示这个字段的含义(PS:建议所有创建的表都有这几个字段,一是为了约束编码规范,而是如果开发的字段名起的群魔乱舞的话还能根据COMMENT字段来看是什么意思而不必去猜~)
# 创建表 my_table:指定引擎为 InnoDB 、编码为 utf8、并且为 `user_phone` 字段创建索引
# 语法: CREATE TABLE table_name(COLUMN,COLUMN_TYPE);
CREATE TABLE IF NOT EXISTS my_table(
`id` BIGINT PRIMARY KEY COMMENT '主键id',
`user_name` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '用户名称',
`user_account` BIGINT NOT NULL DEFAULT '0' COMMENT '用户账户余额,单位:分',
`user_phone` BIGINT NOT NULL COMMENT '用户手机号',
INDEX(`user_phone`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
然鹅细心的小伙伴可能发现了,用户的手机号用 BIGINT
存储是不是不太合适,一般情况下我们使用 VARCHAR 类型的不是很好嘛。这个时候如果想要修改这个类型的话又要咋整呢,当然最简单粗暴的方法就是干掉重新来,但是这样的话之前的这个列的数据就都没了。所以修改字段是一定有的。如下:
# 修改表中某字段的类型或者默认值等属性 语法:ALTER TABLE 表名 MODIFY COLUMN 字段名 新数据类型 新类型长度 新默认值 新注释;
ALTER TABLE my_table MODIFY COLUMN `user_phone` VARCHAR(11) NOT NULL COMMENT '用户手机号';
如果想要修改字段的名称的话,就可以使用 CHANGE
来修改了,CHANGE
同时修改的时候也可以给新字段名定义约束,所以功能上比 MODIFY
要强一些的。
# 语法: ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型;
ALTER TABLE my_table CHANGE `user_phone` `phone` VARCHAR(11) NOT NULL COMMENT '手机号';
此时我们会发现忘记创建用户的年龄字段了,并且我们经常需要对年龄查找,所以此时我们加一个字段且加个索引
# 新增字段/索引
ALTER TABLE my_table ADD COLUMN `user_age` SMALLINT NOT NULL DEFAULT '0' COMMENT '用户年龄';
ALTER TABLE my_table ADD INDEX index_user_age(`user_age`);
最后一个了解下,假如某天我们不想要这个表了,虽然我们从来不会这么做,毕竟数据是无价的嘛,就是删除表的 SQL
# 删除表,直接删除结构
DROP TABLE IF EXISTS my_table;
1.2 表中数据的插入、更新、删除操作
1.1部分的内容都是一些关于数据库表的操作,属于一些了解并且会操作的部分。这部分就比较关键了,有了数据库表,我们就把主要的关注点放在数据的组织上去了,因为这部分的东西和我们的业务息息相关,这部分也即是我们经常说的 CRUD 部分,插入、更新、查找、删除(删除我们现在基本都是逻辑删除,不会有真正的删除的,还是那句话,信息时代数据是宝贵的,逻辑删除就是约定一个字段,isValid ,这个字段为0表示记录无效被删除了,为1表示记录是有效的,我们一般都是这么干的),其实CRUD部分,插入、更新、删除是比较简单的,查找是知识点最多的也是 SQL 优化的一个方向,所以这部分我们先了解下插入、更新、删除部分的知识点。
首先是插入,这个很简单了 如下:
INSERT INTO
my_table
(id,
user_name,
user_account,
user_phone,
user_age)
VALUES (
10001,
'paopao',
30000,
'13300001111',
18
);
然后是更新
UPDATE my_table
SET user_age = 19,user_name = '阿泡啊'
WHERE
id = 10001;
最后是删除 , 用的极少 ~
DELETE FROM my_table WHERE id = 10001;
2. 表数据的查找(重要)
2.1 基础单表查询
这部分也是这个文章的重头戏,也是最重要的部分,在应对大量数据的情况,我们总是需要考虑很多很多,来提高我们检索目标数据的效率,这个过程也是我们所说的 SQL 调优的一部分。在开始学习查询之前,我们先整一张表出来,建表 SQL 如下:
# 建表
CREATE TABLE `my_table` (
`id` bigint(20) NOT NULL COMMENT '主键id',
`user_name` varchar(32) NOT NULL DEFAULT '' COMMENT '用户名称',
`user_account` bigint(20) NOT NULL DEFAULT '0' COMMENT '用户账户余额,单位:分',
`user_phone` varchar(11) NOT NULL COMMENT '用户手机号',
`user_age` smallint(6) NOT NULL DEFAULT '0' COMMENT '用户年龄',
PRIMARY KEY (`id`),
KEY `user_phone` (`user_phone`),
KEY `index_user_age` (`user_age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
给表一些初始值,如下所示插入一些数据
初始值
- 查询表中的所有数据,查询结果在上面已经给出,我们来看看这条 SQL
首先,我们在SELECT
后面把每个字段名都写出来了,而不是直接使用了*
号通配,这是因为SELECT *
会降低我们查询的速度,它杜绝了走索引去查找的情况,而查找走索引会使得查询效率极高,因此会导致索引失效,此外如果我们的数据行定义类型的时候给的空间很大,会导致查询的数据体积增大,也会降低效率,所以我们不这么干(下面面试题这块会专门说一下)。此外,发现这种对于少量数据这么写完全没压力,直接从DB里捞出来了,但是如果表中的数据是千万级的呢,那岂不是很难受,要等老半天,所以呢,我们会带上一些查询条件去查询并且使用分页查询,降低数据库的压力,在生产环境中,因为IO的原因数据库总是会成为性能的瓶颈所在,如果我们堵死了数据库,那线上将会有一大片的报错,所以我们又需要了解到一种机制 分页LIMIT
在Mysql中,行记录是以 页 的方式进行组织存放的,而我们也可以在逻辑上为查询结果进行分页获取,LIMIT M,N
M 代表记录的行号,N代表步长。LIMIT 0,10
代表从记录的第 0 行开始,要得到10行数据,
# 1. 查询表中的所有数据
SELECT id,user_name,user_account,user_phone,user_age
FROM my_table;
# 2. 分页查询,取出前10条数据,执行结果见下图所示
SELECT id,user_name,user_account,user_phone,user_age
FROM my_table
WHERE
id > 0
LIMIT 0,10;
image.png
- 我们想知道 名字中以
d
为开头的所有记录,此时我们可以使用模糊查询
# 3. 模糊查询,查询 user_name 以 d 为开头的记录,查询结果如下图
SELECT id,user_name,user_account,user_phone,user_age
FROM my_table
WHERE
user_name LIKE 'd%';
# 4. 模糊查询,user_name 包含 a 的
SELECT id,user_name,user_account,user_phone,user_age
FROM my_table
WHERE
user_name LIKE '%a%';
image.png
image.png
- 我们想知道, 账户 user_account 余额为0或者为1000的记录,
OR
相当于逻辑或,满足其中一个就是true 对应AND
逻辑且,同时满足
# 5. 获取账户余额为0或者为1000的记录,对应不等于 !=
SELECT id,user_name,user_account,user_phone,user_age
FROM my_table
WHERE
user_account = 0 OR user_account = 1000;
# 也可以使用 IN 关键字,对应 NOT IN
SELECT id,user_name,user_account,user_phone,user_age
FROM my_table
WHERE
user_account IN(0,1000);
image.png
- 我们想知道年龄在 20-50 周岁的所有记录,我们可以使用
AND
或者使用BETWEEN AND
# 6. 年龄在 20-50 岁之间
SELECT id,user_name,user_account,user_phone,user_age
FROM my_table
WHERE
user_age >= 20 AND user_age <=50;
# 也可以使用 BETWEEN AND
SELECT id,user_name,user_account,user_phone,user_age
FROM my_table
WHERE
user_age BETWEEN 20 AND 50;
image.png
- 我们想知道,账户余额都有哪几种,也就是查询所有的账户余额并且去重
# 7. DISTINCT 去重
SELECT DISTINCT user_account FROM my_table;
image.png
- 我们想根据年龄正序或者倒序 使用
DESC ASC/DESC
,默认是正序的,我们看下倒序
# 8. 对查询结果年龄进行倒序
SELECT id,user_name,user_account,user_phone,user_age
FROM my_table
WHERE id > 0
ORDER BY user_age DESC;
image.png
- 分组查询:关键字是
GROUP BY
,前面我们想看账户余额有哪些种类可以通过 去重的手段去实现,但是我们无法知道每个种类都有几行记录,可以通过分组再次统计一波,并且分组后还可以过滤,这个时候 WHERE 后面的条件就不能满足了,因为那时候还没分组呢,所以我们有HAVING
关键字,比如筛选出的结果中取总数大于1的
# 9. 根据 user_account 进行分组
SELECT user_account , COUNT(user_account)
FROM my_table
WHERE id > 0
GROUP BY user_account;
# 取总数大于1的
SELECT user_account , COUNT(user_account)
FROM my_table
WHERE id > 0
GROUP BY user_account HAVING COUNT(user_account) > 1;
image.png
image.png
2.2 函数查询
-
COUNT()
这个函数的使用上面已经见过了,作用是查询结果返回列中包含的数据行数,它有2种用法- COUNT() 表示计算表中的总的行数, 代表所有的数据行,不管某数据列是否为 NULL
- COUNT(字段名称) 计算该字段名下的行数,会忽略这一列值 NULL 的记录
-
SUM() AVG()
SUM() 函数是一个求总和的函数,返回指定列值的总和
AVG() 函数是一个求平均值的函数,返回指定列值的平均值 -
MAX() MIN()
MAX() 返回指定列中的最大值
MIN() 返回指定列中的最小值
2.3 多表查询
工作中我们一般多表查询都是在程序层面做的,但是这部分也可以考察一位程序员的SQL基础是否过硬。
数据表准备 , 建表 SQL 如下:
# 多表查询学习开始,建表 SQL
# 表1:部门表
CREATE TABLE IF NOT EXISTS department(
id BIGINT PRIMARY KEY COMMENT '部门主键id',
name VARCHAR(32) NOT NULL DEFAULT '' COMMENT '部门名称'
)ENGINE = InnoDb DEFAULT CHARSET = UTF8 COMMENT '部门表';
# 表2:员工表
CREATE TABLE IF NOT EXISTS staff(
id BIGINT PRIMARY KEY COMMENT '员工主键id',
name VARCHAR(32) NOT NULL DEFAULT '' COMMENT '员工名称',
sex TINYINT NOT NULL DEFAULT '1' COMMENT '员工性别:0女 1男',
age SMALLINT NOT NULL DEFAULT '0' COMMENT '员工年龄',
department_id BIGINT NOT NULL DEFAULT '0' COMMENT '部门主键id'
)ENGINE = InnoDb DEFAULT CHARSET = utf8 COMMENT '员工表';
# 插入一些初始数据
INSERT INTO department(id,name) VALUES
(100001,'研发中心'),
(100002,'人力资源'),
(100003,'产品运营'),
(100004,'线上客服');
INSERT INTO staff(id,name,sex,age,department_id) VALUES
(500001,'风清扬',1,50,100001),
(500002,'天山童姥',0,45,100002),
(500003,'独孤求败',1,20,100003),
(500004,'东方不败',0,30,100004),
(500005,'叶孤城',1,32,100003),
(500006,'逍遥子',1,46,100002);
2.3.1 连接查询
连接就是把2张表通过某个字段关联到一起查询,建立在2张表至少有1个关联字段的前提下,如上面的表,我们在员工表中设置了员工所处的部门,也就是存储了部门的id,通过这个字段我们能知道某员工是哪个部门下的。
连接分为内连接、外连接。其中外连接又分为左外连接和右外连接。语法如下:
SELECT 字段列出来
FROM 表1 INNER/LEFT/RIGHT JOIN 表2
ON 表1.字段 = 表2.字段;
- 交叉连接:直接生成笛卡尔积,跟条件无关,其实就是全匹配
SELECT * FROM staff,department;
image.png
- 内连接:只连接匹配的行记录,找到了2张表共有的部分,比如员工表中有部门id,部门表中有部门id
SELECT staff.id,staff.name,staff.sex,department.id,department.name
FROM staff INNER JOIN department ON (staff.department_id = department.id);
# 也可以使用 WHERE 代替 ON
image.png
- 外连接:左外链接,优先左边的全部展示,右边的只有在左边用到了才会取到。就是以左表记录为参照,左表的每一行都会展示出来,如果某一行没有对应的右边数据,对应字段为NULL。右外连接与此过程相反。
# 左外连接
SELECT staff.id,staff.name,staff.sex,department.id,department.name
FROM staff LEFT JOIN department ON (staff.department_id = department.id);
# 右外连接
# 左外连接
SELECT staff.id,staff.name,staff.sex,department.id,department.name
FROM staff RIGHT JOIN department ON (staff.department_id = department.id);
2.3.2 子查询
子查询就是嵌套查询,并且内存查询的结果可以给外层作为查询条件。子查询可以包含 IN/NOT IN/ANY/ALL/EXISTS/NOT EXISTS
,还可以使用条件比较符 =/!=/>/<
等等
-
IN
关键字子查询
#IN 子查询,查询平均年龄在40岁以上的部门名称
SELECT department.name FROM department
WHERE id IN(SELECT department_id FROM staff GROUP BY department_id HAVING AVG(age) > 40);
image.png
查询研发中心的员工
# 查询研发中心和人力资源部门的员工信息
SELECT staff.name,staff.age,staff.sex FROM staff
WHERE department_id IN(SELECT id FROM department WHERE department.name = '研发中心' OR department.name = '人力资源');
image.png
- 比较运算符的子查询
# 查询大于平均年龄的员工姓名与年龄
SELECT name,age FROM staff
WHERE age > (SELECT AVG(age) FROM staff);
image.png
查询大于自己部门内平均年龄的员工姓名、年龄,我们先内连接自己,找到一张临时的表,这个临时表我们只需要部门id和部门的平均年龄字段,然后对2张表进行一次比较即可。
# 查询大于自己部门内平均年龄的员工姓名、年龄
SELECT s1.name,s1.age FROM staff AS s1
INNER JOIN (SELECT department_id,AVG(age) AS age FROM staff GROUP BY department_id ) AS s2
ON s1.department_id = s2.department_id
WHERE s1.age > s2.age;
image.png
- EXISTS 主要是用来判断是否存在的,只返回 true/false 。
3. 碰到的一些有意思的SQL记录
这部分都是和同事们的交流的一些SQL记录下来的,大家有好的下方留言我也可以给加到这里。
网友评论