【Mysql】基础语法和一些SQL问题记录

作者: 都是浮云啊 | 来源:发表于2019-03-18 23:49 被阅读0次

[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

我们想知道, 账户 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

2.2 函数查询

COUNT()
这个函数的使用上面已经见过了,作用是查询结果返回列中包含的数据行数，它有2种用法
1. COUNT() 表示计算表中的总的行数, 代表所有的数据行,不管某数据列是否为 NULL
2. 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记录下来的，大家有好的下方留言我也可以给加到这里。

【Mysql】基础语法和一些SQL问题记录

开篇

1. 基础语法

1.1 表的新增、修改、删除

1.2 表中数据的插入、更新、删除操作

2. 表数据的查找（重要）

2.1 基础单表查询

2.2 函数查询

2.3 多表查询

2.3.1 连接查询

2.3.2 子查询

3. 碰到的一些有意思的SQL记录

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读