哈希空间

mysql数据库

前言

本文 mysql数据库是哈希空间 2020年6月整理编辑，总结最常用、最实用的MySQL基础语句。
祝你能够愉快享用这份精炼的文档。

本文连接 https://www.hashspace.cn/mysql-shujuku.html 欢迎转发分享。

MySQL数据库介绍

MySQL 是最为流行的免费开源关系型数据库系统。在国内外公司都有非常广泛、深入的使用。是目前数据库领域最重要的工具。

MySQL 下载

MySQL 分为服务器端软件即 MySQL Server 和 MySQL客户端软件即 MySQL Client 。
MySQL服务器端软件官方下载地址为 https://dev.mysql.com/downloads/installer/

目前最新版本为 MySQL 8.0

Windows 安装程序 420Mb
https://dev.mysql.com/get/Downloads/MySQLInstaller/mysql-installer-community-8.0.20.0.msi

Mac 安装

brew install mysqld

Ubuntu/Linux 安装

apt install mysqld

MySQL 连接数据库

通过使用 MySQL 客户端软件连接数据库。可以参考文章 MySQL 连接数据库和 mysql新手教程

SQL 也可以 Hello World

SQL 语句对大小写不敏感。SELECT 等效于 select 。


select "Hell World";

创建数据库 create database


create database 数据库名字

删除数据库 drop database


drop database 数据库名字

列出数据库


show databases

使用数据库 use


use 数据库名字

建表 CREATE TABLE

语法


CREATE TABLE 表名称
(
列名称1 数据类型 其它可选配置,
列名称2 数据类型 其它可选配置,
列名称3 数据类型 其它可选配置,
....
)

例子，创建一个学生信息表 student ，包括姓名 name ，生日 birthday ，身高 height


CREATE TABLE `student` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(16) NOT NULL DEFAULT '',
  `birthday` date NOT NULL,
  `height` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

下面对每个字段进行说明：

字段	类型	是否可空	说明
id	int unsigned	NOT NULL	id 为自增主键 `AUTO_INCREMENT` 类型，作为每条记录的唯一 id int 为正数类型，unsigned 表示无符号类型即只表示正数范围；NOT NULL 表示这个字段不能为空 NULL，必须有值
name	varchar(16)	NOT NULL	字符串类型，16个字符，默认为空字符串 ‘’
birthday	date	NOT NULL	日期类型，出生日期
height	int	NOT NULL	正数类型，比如 180 则表示 180cm 身高
KEY(`id`)	PRIMARY		指定主键为 id 字段
ENGINE			存储引擎使用 InnoDB引擎
DEFAULT CHARSET			指定字符集使用 utf8mb4 避免出现编码不兼容问题(默认类型可能兼容emoji 等特殊字符)

id 自增主键 AUTO_INCREMENT 说明

每个表只可以设置一个自增主键，也可以不设置。一般来说为了确定表中记录的唯一性，都应该默认设置自增主键id

NOT NULL 说明

NOT NULL 为可选类型，不写则表示该字段值可以为空NULL

DEFAULT 默认值说明

DEFAULT 为可选表示给字段设置一个初始默认值。

列出库里的所有表


show tables

从中可以找到新建的 student 表

插入数据 INSERT INTO

语法


INSERT INTO 表名  (列1, 列2,...) VALUES (值1, 值2,....)

例子，在学生信息表 student 表中插入一条数据姓名为 Jack，生日 1990-01-01 ，身高 180


INSERT INTO `student` (`name`, `birthday`, `height`)
VALUES ('Jack', '1990-01-01', 180);

查询数据 SELECT

语法


SELECT 列1,列2,... FROM 表名称

或全部列 *


SELECT * FROM 表名称

例子


select * from student

或使用 `` 将表名和字段名标注起来，防止触发MySQL关键词。


select * from `student`

where 查询条件

查找姓名 name 等于 Jack 的记录


select * from `student` where name='Jack'

结果：

id	name	birthday	height
1	Jack	1990-01-01	180

几种查询例子：

查找身高大于等于 170 的记录


select * from `student` where height >=170

and 查询

查找姓名 name 等于 Jack 且身高大于等于 170 的记录


select * from `student` where name='Jack' and height >=170

or 查询

查找姓名 name 等于 Jack 或身高大于等于 170 的记录


select * from `student` where name='Jack' or height >=170

null 记录查询

查找手机号 mobile 为空的记录


select * from `student` where mobile is null

更新数据 UPDATE

语法


UPDATE 表名称 SET 某列名称 = 新值 WHERE 某列名称 = 某值

例子，更新学生信息表 student 中姓名为 Jack 的生日为 1990-02-01


UPDATE student birthday='1990-02-01' where name='Jack'

删除数据 DELETE

语法


DELETE FROM 表名称 WHERE 列名称 = 值

例子，删除学生信息表 student 中姓名为 Jack 的记录。


DELETE FROM student WHERE where name='Jack'

例子2 ，删除学生信息表 student 中姓名为 Jack 且生日为 1990-02-01 的记录。


DELETE FROM student WHERE where name='Jack' and  birthday='1990-02-01'

例子3 ，如果不写 where 部分，则会删除表中的全部记录


DELETE FROM student

这时可以考虑使用清空表的语句 TRUNCATE

清空表数据 TRUNCATE

例子，清空 student 表，但是不删除表结构


TRUNCATE  student

表增加字段 ALTER TABLE ADD

语法


ALTER TABLE `表名字` ADD `新字段名`  字段类型 COMMENT '字段说明'

例子，在学生信息表 student 中，新增一个字段手机号 mobile ，类型为 varchar(16) 即16个字符。


ALTER TABLE `student` ADD `mobile` varchar(16) COMMENT '手机号'

表删除字段 ALTER TABLE DROP

语法


ALTER TABLE `表名字` DROP  `字段名`

例子，删除学生信息表 student 中手机号 mobile 字段


ALTER TABLE `student` DROP `mobile`

索引优化

索引是为了提高SQL运行效率而提供的一种手段。当对某个字段设置索引优化后，一些情况下使用 where 查询该字段时可以非常快速的返回这些满足条件的记录。

比如在学生信息表中有100条记录，我们现在要查询姓名为 Jack 的学生记录


select * from student where name='Jack'

理论上，我们需要扫描全部100条记录，依次比较 name 字段是否 Jack 。当我们给 name 字段设置索引后，通常仅需扫描几条记录就可以找到全部 name 为 Jack 的记录，极大的提高了SQL 的执行速度和效率。对于网站等服务是不是 卡、慢 通常有非常明显的作用。

索引类型

分为 INDEX 普通索引，UNIQUE 唯一索引，PRIMARY KEY 主键索引。
主键索引只能设置一个，INDEX/UNIQUE 可以设置多个。

可以对一个字段设置索引，也可以多个字段设置联合索引。

创建索引 ALTER TABLE ADD INDEX

语法


ALTER TABLE 表名字 ADD INDEX 索引名字(表字段)

例子给学生信息报 studet 表中的name 字段设置索引


ALTER TABLE student ADD INDEX index_name (name)

创建唯一索引 ALTER TABLE ADD UNIQUE

例子：因为手机号不应该有重复的，所以给学生信息 studet 表中的 mobile 字段设置唯一索引


ALTER TABLE student ADD UNIQUE mobile (mobile)

这里我们将索引名定为和字段名一样的 mobile ，可以根据需要设置，也可以不同。

删除索引 DROP INDEX

例子，删除学生信息表中的mobile 索引


ALTER TABLE student DROP INDEX mobile;

注意无论是普通索引还是唯一索引都使用 DROP INDEX 来删除。

查看索引


show index from student;

就可以方便的查看表中索引情况，下面是对结果中一些字段的说明：

Table 表的名称
Non_unique 如果索引不能包括重复词，则为0。如果可以，则为1
Key_name 索引的名称
Seq_in_index 索引中的列序列号，从1开始
Column_name 列名称
Collation 列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）
Null如果列含有NULL，则含有YES。如果没有，则该列含有NO
Index_type 用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）

查看建表语句


show create table student

返回结果如下


CREATE TABLE `student` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(16) NOT NULL DEFAULT '',
  `birthday` date NOT NULL,
  `height` int NOT NULL,
  `mobile` varchar(16) DEFAULT NULL COMMENT '手机号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `mobile` (`mobile`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4

我们可以看到建表语句中已经包含了我们新增的唯一索引 UNIQUE KEY mobile (mobile) 和普通索引 KEY index_name (name)

查询SQL执行计划

当给表加完索引后，我们想知道查询是否变得更高效了，这时候我们可以使用查询SQL执行计划的功能，即 SQL 语句前面加个 explain ，例子：


explain select * from student where name='Jack' ;

possible_keys 为可能使用的索引名
key 使用的索引名
key_len 索引长度
rows 扫描行数 这个是最重要的指标，该字段没设置索引时通常为全表记录数，如果设置了索引则会远小于全表记录数

查询、终止执行中的SQL

查询执行中的SQL任务

show processlist;

终止SQL ，其中id 为上面结果中的 id

kill id

mysqldump MySQL导入导出数据

导出数据

整个库

mysqldump -u 用户名 -p 数据库名 > 导出的文件名.sql

单个表

mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名.sql

导出表结构

不导出数据只要表结构，加参数 -d 参数即可
整个库结构

mysqldump -d -u 用户名 -p 数据库名 > 导出的文件名.sql

单个表结构

mysqldump -d -u 用户名 -p 数据库名 表名> 导出的文件名.sql

导入数据

/tmp/backup.sql 为备份文件

mysql 命令导入

mysql -u用户名    -p密码 </tmp/backup.sql

source 命令导入

进入mysql命令行

mysql -u 用户名 -p

执行导入命令


use 数据库
source /tmp/backup.sql

本文最佳观看地址：https://www.hashspace.cn/mysql-shujuku.html 阅读 3282