《MySql》入门指南

·  阅读 3604
《MySql》入门指南

你好,我是愣锤。

前端小伙伴想了解全栈内容时,很多人上来可能就喜欢用Node.js+mongo数据库来切入,但是MySql数据库在实际场景中是应用更多的。因此熟练掌握SQL是你必须要迈出的一步!

但是小伙伴不用怕,基本的SQL语句的学习,所需要的知识量甚至比CSS还要简单哈。本文将基于8.0.16版本的MYSQL数据库,介绍通用SQL语句。通读本文并加以练习,定可以掌握MySql数据的常见数据操作。MySql数据库的安装不再赘述,小伙伴可以在网上查阅资料去官网下载配置。本文使用的数据库可视化工具是Navicat Premium,如下图所示:

image.png

基本命令

  • 数据库连接

使用数据库的前提先连接上数据(再前提是先安装数据库...),然后才可以在终端里面输入SQL语句运行。

# 安装好mysql数据库和配置好后,mysql命令用于启动数据库
# -u指定数据库账号为root,-p是要输入密码,命令运行后终端会要求你输入密码
mysql -u root -p
复制代码

密码输入完毕键入回车,连接成功后如下图所示:

image.png

此时小伙伴可能有疑问了,这里是在终端运行的SQL,那怎么在项目中使用呢?这里小伙伴先别急,项目中使用MySql也是通过一些库连接上数据库,然后调用库的一些方法传入我们拼接的SQL语句的。这里我们是学习SQL语句的各种操作。

在学习SQL语句之前,需要着重注意的是,SQL语句对大小写不敏感! SQL语句对大小写不敏感!! SQL语句对大小写不敏感!!! 重要的事情说三遍哈!还有就是语句结束要加分号哈。

  • 创建数据库
CREATE DATABASE 数据库名;
复制代码

创建数据库很简单,上面的一行命令解决。如何查看数据库有没有创建成功呢?可以在上面的Navicat Premium可视化工具查看,也可以运行下面的查看数据命令。

  • 查看所有数据库
# 注意末尾有分号
SHOW DATABASES;
复制代码

image.png

能创建数据库,那自然是能删除数据库的啦,命令很简单,咱们继续往后学习。

  • 删除数据库
DROP DATABASE 数据库名;
复制代码

在创建数据库完成之后,那就该各种增删改查的数据库操作了。增删改查的前提是我们先选择一个数据库,不然那么多数据库鬼知道要操作哪个呀!别说鬼了,就是你家的狗子也不知道哈!舔狗也不知道呀!哈哈,扯偏了,拉回正题,下面学习如何选择数据库。

  • 选择数据库
USE 数据库名;
复制代码

image.png

我们知道一个数据库是由很多数据表组成的,那么指定了数据库之后,我们想查看当前数据库下面有哪些数据表该怎么办呢?

  • 查看指定数据库的所有表
# 前提是先使用USE先选择数据库
SHOW TABLES;
复制代码

如下图所示,可以看到所有的数据表,如果你没有创建数据表那么就是空的,接下来紧接着就会讲解数据表的各种操作。当然了你也可以使用可视化工具查看,后面也就不再赘述了:

image.png

数据库表

上面选择好了数据库之后,如何创建一个数据表呢?

  • 创建数据库表
# 如果`user`表不存在则创建创建user表
CREATE TABLE IF NOT EXISTS `user`(
   `uid` INT UNSIGNED AUTO_INCREMENT,
   `username` VARCHAR(128) NOT NULL,
   PRIMARY KEY ( `uid` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
复制代码

值得注意的是,AUTO_INCREMENT表示列值自增;PRIMARY KEY指定主键的字段,每张表只能有一个主键,他的列值是唯一且不为空的;NOT NULL表示在操作数据库时不允许字段为NULL,否则报错;结尾的ENGINE=InnoDB指定引擎类型,CHARSET=utf8;指定字符集。INTVARCHAR(128)是定义的列值的数据类型。关于数据类型有哪些,稍后会有讲解,这里大家先有个概念就好。

  • 删除数据库表
DROP TABLE 表名;
复制代码

创建完了数据表之后,如何查看我们的数据表到底都创建了那些数据字段呢?可以通过下面的命令快速查看。

  • 查看创建的数据表结构
desc 表名;
复制代码

image.png

SQL数据类型

MySQL 支持所有标准 SQL 数值数据类型,像日期时间字符串数值等。

类型说明范围
CHAR定长字符串0-255字节
VARCHAR变长字符串0-65535字节
TINYTEXT短文本字符串0-255字节
TEXT长文本字符串0-65535字节
TINYINT小整数值(-128,127)
INT大整数值(-2 147 483 648,2 147 483 647)
DATE日期值1000-01-01/9999-12-31
DATETIME日前时间值1000-01-01 00:00:00/9999-12-31 23:59:59

需要注意的是char(n)varchar(n) 括号中的 n 代表字符的个数,不是字节数。

其他数据类型可以参考文档

插入数据

学会了基本的数据类型之后,我们看看如何往一个数据表中插入一条数据记录。使用INSERT INTO关键词往指定数据表插入数据,如下所示:

  • 往表中插入数据
INSERT INTO 表名 (field1, field2, ...fieldN) VALUES (value1, value2,...valueN);
复制代码

注意的是:多个数据字段使用逗号隔开,如果值是字符串,则使用单引号双引号包裹,自增的值不需要赋值。

数据表中有了数据之后我们如何查询一条数据出来呢?这时候就要用到SELECT关键词了,我们继续往后看。

查询数据

查询的逻辑就是利用select关键词从指定的数据表中查询符合条件的数据,如果没有指定查询条件则是查询所有的。

  • 查询表所有列数据
# 查询user表的所有记录
SELECT * FROM user;
复制代码
  • 查询表中指定列的所有数据
# 查询user表的username列的所有数据
SELECT username FROM user;

# 查询user表的多个列的所有数据
SELECT username, otherColumn1, otherColumn2 FROM user;
复制代码

注意注意请注意: *表示所有列字段,但是从优化角度来看,一般不使用*查询所有的列字段,而是使用逗号查询所需要的列,这点就像前端的雅虎军规一样。

学会了基本的数据表查询数据操作后,接下来我们将学习更复杂的查询逻辑,查询符合我们条件的数据。例如常见的场景有查询uid=n的用户数据,查询n条文章类型为js的所有博客数据等等。

  • WHERE关键字可以用于指定查询条件
# 查询user表中username为make的这条记录所有字段信息
SELECT * FROM user WHERE username='make';
复制代码

image.png

WHERE关键词用于查询数据时附加查询条件,如果是多个查询条件,则需要使用ANDOR连接多个表达式来表示的关系,例如我们查询学生成绩大于90分的且是男生的,这种就会有多个查询条件。多条件表达式的用法我们继续往下看。

  • ANDOR可以在WHERE后面使用多个表达式,表示的逻辑
# 选取books表中type为666且author为施耐庵的记录数据集合
SELECT * FROM books WHERE type=666 AND author='施耐庵';

# 选取books表中type为666或者author为make的记录数据集合
SELECT * FROM books WHERE type=666 OR author='make';
复制代码
  • 表达式操作符可以为=!=><>=<=
# 选取books表中id大于2的数据记录集合
SELECT * FROM books WHERE id > 2;
复制代码

学会了使用WHERE附加查询条件之后,那如果我们想从指定位置查询指定条数呢?典型的场景就是后端接口常见的分页查询呀!

  • LIMIT指定查询数据,OFFSET指定查询位置

现有一张books表的数据如下

image.png

# 从books表中下标位置为2开始,查询2条记录的数据
SELECT * FROM books LIMIT 2 OFFSET 2;
复制代码

image.png

除了查找到精确的数据之外,有些时候我们可能想模糊查询,比如查询文章标题中包含js的数据,那在MySql中该如何查询呢?

  • LIKE关键词模糊查找
# LIKE语法
SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'
复制代码

LIKE后面的表达式中 % 表示任意字符,有点类似于linux中的*

例如我们查询books表中所有bookname西游开头的记录:

SELECT * FROM books WHERE username LIKE '西游%';
复制代码
  • UNION连接多个SELECT表达式
# 查询level表中level字段小于2和大于5的记录
SELECT * FROM level WHERE level < 2 UNION SELECT * FROM level WHERE level > 5;
复制代码

image.png

默认情况下,UNION得到的交集会去除重复的数据记录,也可以显示的指定是否去除:

# DISTINCT字段表示去除重复记录,也是默认值
SELECT * FROM level WHERE level < 2 UNION DISTINCT SELECT * FROM level WHERE level > 5;

# ALL字段表示不去除重复记录
SELECT * FROM level WHERE level < 2 UNION ALL SELECT * FROM level WHERE level > 5;
复制代码

基本的数据查询都学会了之后,是不是成就感满满呢?那么接下来就该是数据更新了!

更新数据

  • 将指定列的所有数据进行更新
UPDATE 表名 SET field1=value1, field2=value2;
复制代码
  • WHERE根据筛选条件进行更新
UPDATE 表名 SET field1=value1, field2=value2 WHERE 表达式;
复制代码

删除数据

  • 删除表中符合条件的记录数据
# 从books表中删除id为6的记录
DELETE FROM books WHERE id=6;
复制代码
  • 删除表中全部数据
# 删除user表中全部记录
DELETE FROM user;
复制代码

查询结果排序

  • 升序,ORDER BY默认是升序,默认值是ASC
# 查询level表中所有记录,按field1升序排序
SELECT * FROM level ORDER BY field1;
复制代码
  • 降序,ORDER BY DESC指定降序排序
# 查询level表中所有记录,按field1降序排序
SELECT * FROM level ORDER BY field1 DESC;
复制代码

分组

GROUP BY可以将数据表按指定字段分组,然后可以对分组的数据进一步操作。例如下面将数据表按照name分组并统计每个name有多少条数据:

SELECT name, COUNT(*) FROM level  GROUP BY name;
复制代码

image.png

学习了这么多,是不是让你感()到()快()乐()呢?哈哈,言归正传,下面我们继续学习如何进行多表联查,这是很重要的一部分,因为在实际的场景中,很多时候是要多个表一起查询才得到最终返回给客户端的数据的。

多表联查

  • JOIN可以用于在多张表中查询数据,获取两个表中字段匹配关系的记录。类似于交集。
# 查询存在于user表中且在level表中有相同level值的记录
# 查询返回的fields为user.uid、user.author、user.level、level.name、level.description
select a.uid, a.author, a.level, b.name as levelName, b.description as levelDesc  from user a join level b on a.level = b.level;
复制代码

需要注意的是AS关键词可以对返回的field重命名,即起的一个别名,如下图所示:

image.png

  • LEFT JOIN 获取左表符合条件的所有记录,即使右表没有对应匹配的记录
# 对于在b表中没有查到的记录的数据,则以null填充
select a.uid, a.author, a.level, b.name as levelName  from user a left join level b on a.level = b.level;
复制代码

image.png

  • RIGHT JOIN 获取右表符合条件的所有记录,即使左表没有对应匹配的记录
select a.uid, a.author, a.level, b.name as levelName  from user a right join level b on a.level = b.level;
复制代码

image.png

条件表达式使用正则

除了使用LIKE关键词进行模糊匹配,WHERE的条件表达式也可以使用正则表达式进行查询,关键词是REGEXP:

# 查询books表中bookname是西游开头的所有记录
select * from books where bookname regexp '^西游';
复制代码

REGEXP的使用场景不仅是模糊查询,更多的还是给自定义查询提供一种灵活的方式。接下来我们学习 SQL 中又一个重要的概念----事务

事务

事务的主要作用是保证一组数据库操作都成功执行,不然如果有一步数据操作出错了会导致不完整性。举个例子,当你删除一个用户时,同时需要用户的文章信息、登录数据等等时,要确保删除用户时的其他删除操作也必须成功。事务具有如下几个重要特征:

  • MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
  • 事务处理可以用来维护数据库的完整性,保证成批的 SQL 语句要么全部执行,要么全部不执行。
  • 事务用来管理 insertupdatedelete 语句

使用用法主要有2步,一是使用BEGIN;开始一个事务;二是使用ROLLBACK;可以回滚事物或者使用COMMIT;进行事务确认。下面我们看具体的使用示例:

# 开始事务
begin;

# 一些数据库操作
# 此时所有数据操作并未被真正写入数据库
insert into books (bookname, type, author) values ('深入浅出Vue.js', 123, '刘博文');
insert into books (bookname, type, author) values ('new book', 123, 'make');
insert into books (bookname, type, author) values ('new book2', 123, 'make');

# 事物确认,此时所有数据才被全部写入
commit;
复制代码

image.png

下面,大家思考一个问题,如果我们数据表创建完成之后,在开发过程中突然发现我们缺少了一些字段,或者一些数据类型不对,我们需要修改表那又该怎么办呢?

ALTER更新数据表

ALTER的作用是修改数据表名或者字段。

  • 向数据表增加列
# 向user表中新增newField1列,类似为INT
alter table user add newField1 int;
复制代码
  • 修改数据表字段的名称和类型

MODIFY作用是修改字段类型,CHANGE可以同时修改名称和类型。

# 将user表的newField1字段的类型修改为varchar(64)
alter table user modify newField1 varchar(64);

# 将user表的newField1字段名称修改为new_field1,类型修改为varchar(128)
alter table user change newField1 new_field1 varchar(128);
复制代码

注意:alter修改类型时覆盖操作,不是增量覆盖,因此修改类型时要加上原先所有的:

# 举个例子,原先的uid字段如下
`uid` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID',

# 在使用alter修改类型时,
# 哪怕仅仅改一个数据类型为int,也需要把后面的NOT NULL等类型携带上
alter table user modify uid int NOT NULL AUTO_INCREMENT COMMENT '用户ID'
复制代码
  • DROP移除字段
# 移除user表的new_field1字段
alter table user drop new_field1;
复制代码
  • 设置/删除字段的默认值

alter操作时如果没有指定默认值,则默认值是NULL,可以通过下面的例子删除/设置字段的默认值:

# 设置user表的new_field字段的默认值为100
# 注意,alter是用了两次
alter table user alter new_field set default 100;

# 移除user表的new_field字段的默认值
alter table user alter new_field drop default;
复制代码
  • 修改数据表的名称
# 我们先创建一个数据表
create table new_table(uid int);

# 修改new_table数据表的名称为new_table2
alter table new_table rename to new_table2;
复制代码

image.png

索引

索引分为主键索引普通索引唯一索引全文索引,查看数据表的索引情况的命令是show index from 数据表名,下图展示了我的user表的索引情况:

image

  • 主键索引

主键索引是一种特殊的唯一索引,不允许有NULL值。一般在创建表的时候指定主键索引,一个表只能有一个主键。

# 创建demo_table表,
# 通过PRIMARY KEY指定为主键
# 通过unique指定为唯一索引
# 通过index可以指定为普通索引
create table demo_table(
  id int not null primary key,
  username varchar(64) unique
) engine=innodb;
复制代码

image.png

  • 唯一索引

唯一索引对应的列值必须唯一,但可以为NULL,如果是组合索引,则列值的组合必须唯一。

可以通过alter table 表名 add unique (列名);添加唯一索引。alter table 表名 add unique (列1, 列2);创建唯一组合索引。

  • 普通索引

普通索引是基本的索引,没有限制。创建普通索引的方式是alter table 表名 add index 索引名称 (列名);,创建普通组合索引的方式是alter table 表名 add index 索引名称 (列1, 列2);

  • 全文索引
# 给demo_table2先增加一列text_field1
alter table demo_table2 add text_field1 text;

# demo_table2表的text_field1字段添加全文索引
alter table demo_table2 add fulltext(text_field1);
复制代码
  • 删除索引

索引一经创建便不可修改,如果要修改则需要删除重建。

drop index 索引名称 on 表名;
复制代码

注意:索引是一种数据结构,索引可以提升检索速度,但是会额外占用磁盘工具,降低写的速度。因此,不要过度索引。

参考文章:MySQL索引优化看这篇文章就够了!作者:良月柒

导出

  • 导出某个表的数据
# 将books表中所有数据导出到./books.sql文件中
select * from books into outfile '/tmp/books.sql';
复制代码

命令运行后你可能遇到如下错误导致无法导出:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
复制代码

image.png

这个错误是说数据库的secure-file-priv设置不允许导入导出数据。我们先查看一下我们的数据库secure-file-priv配置情况:

# 终端运行
show variables like '%secure%';
复制代码

image.png

secure-file-priv值为NULL说明不允许导入导出,没有具体值时表示不对导入导出做限制,有具体值表示只允许在指定路径导入导出。

解决办法大家可以参考这篇文章

问题解决后,再次运行导出数据的命令,可以看到在/tmp目录下已经生成了books.sql文件,文件内容如下:

1	资本论	123	make
2	西游记	123	吴承恩
3	三国演义	123	罗贯中
4	水浒传	123	施耐庵
5	红楼梦	123	曹雪芹
7	深入浅出Node.js	123	朴灵
8	深入浅出Vue.js	123	刘博文
9	深入浅出Vue.js2	123	刘博文
10	深入浅出Vue.js3	123	刘博文
11	深入浅出Vue.js4	123	刘博文
复制代码
  • 导出整个数据库

注意,导出的是整个数据库所有表的表和数据。导出的表结构可以在其他地方导入直接创建表和数据。

# 将指定数据库所有表的格式导出到express-blog.sql文件
# 直接终端输入,不需要先mysql登录数据库
mysqldump -u root -p 要导出的数据库名 > express-blog.sql
复制代码

紧接着会要求输入数据库密码,输入完毕后,可以看到导出了一个express-blog.sql文件,其内容如下:

-- MySQL dump 10.13  Distrib 8.0.16, for macos10.14 (x86_64)
--
-- Host: localhost    Database: express-blog
-- ------------------------------------------------------
-- Server version	8.0.16

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
 SET NAMES utf8mb4 ;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `books`
--

DROP TABLE IF EXISTS `books`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 SET character_set_client = utf8mb4 ;
CREATE TABLE `books` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `bookname` varchar(100) NOT NULL,
  `type` varchar(50) NOT NULL,
  `author` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `books`
--

LOCK TABLES `books` WRITE;
/*!40000 ALTER TABLE `books` DISABLE KEYS */;
INSERT INTO `books` VALUES (1,'资本论','123','make'),(2,'西游记','123','吴承恩'),(3,'三国演义','123','罗贯中'),(4,'水浒传','123','施耐庵'),(5,'红楼梦','123','曹雪芹'),(7,'深入浅出Node.js','123','朴灵'),(8,'深入浅出Vue.js','123','刘博文'),(9,'深入浅出Vue.js2','123','刘博文'),(10,'深入浅出Vue.js3','123','刘博文'),(11,'深入浅出Vue.js4','123','刘博文');
/*!40000 ALTER TABLE `books` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `level`
--

DROP TABLE IF EXISTS `level`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 SET character_set_client = utf8mb4 ;
CREATE TABLE `level` (
  `name` varchar(255) DEFAULT NULL,
  `level` int(64) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `level`
--

LOCK TABLES `level` WRITE;
/*!40000 ALTER TABLE `level` DISABLE KEYS */;
INSERT INTO `level` VALUES ('青铜',0,'倔强青铜等级'),('白银',1,'白银等级'),('黄金',2,'闪耀黄金等级'),('铂金',3,'铂金等级'),('钻石',4,'璀璨钻石等级'),('大师',5,'超凡大师等级'),('王者',6,'无上王者等级'),('王者',7,'闪耀等级'),('王者',8,'闪耀等级');
/*!40000 ALTER TABLE `level` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `user`
--

DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 SET character_set_client = utf8mb4 ;
CREATE TABLE `user` (
  `uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `author` varchar(100) NOT NULL,
  `level` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `user`
--

LOCK TABLES `user` WRITE;
/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` VALUES (1,'make',1),(2,'berg',3),(3,'emei',11);
/*!40000 ALTER TABLE `user` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2022-04-08 23:00:40
复制代码

从文件内容可以看出,实际上数据库导出的是一系列创建数据表和插入数据的命令。

  • 导出指定的表结构
mysqldump -u root -p 数据名 数据表名 > ./xx.sql
复制代码

导入

  • 将导出的数据表数据导入到数据表
# 将'/tmp/books.sql'中的数据导入到books表中
LOAD DATA INFILE '/tmp/books.sql' INTO TABLE books;
复制代码
  • 将导出的数据库(或数据表)导入
# 根据导出./books.sql文件在express-blog数据库中创建表和表的数据
mysql -u root -p express-blog < ./books.sql
复制代码

结束语

熟练掌握SQL的使用,一定是前端小伙伴切入后端技术栈绕不过去的一道弯,希望通过本文的学习可以全栈的路上祝小伙伴们一臂之力。我是你们的老朋友愣锤,欢迎点赞收藏❤️❤️

分类:
后端
收藏成功!
已添加到「」, 点击更改