数据库

MySQL

创建要操作的数据库

• 连接

mysql -uroot -p

• 查看所有的数据库

show databases;

• 选择要操作的数据库

use <database>;

• 创建新的数据库

create database <database>;

例：

CREATE DATABASE users DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_bin

• 创建数据表

CREATE TABLE 数据库表名称 (
    字段名称 字段属性...,
    PRIMARY KEY (主键字段名称),
    INDEX 索引名称(索引字段...)...
) ENGINE=InnoDB DEFAULT CHARSET=utf8

字段属性设置

• 字段类型：int(10)、char(10)、varchar(200)
• 是否为null：NOT NULL
• 无符号：UNSIGNED
• 自动增长：AUTO_INCREMENT
• 默认值：DEFAULT 0

例：

CREATE TABLE user(
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(50) NOT NULL DEFAULT '',
    `age` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `gender` ENUM('男', '女') NOT NULL DEFAULT '男',
    PRIMARY KEY (`id`),
    INDEX uname(`username`),
    INDEX age(`age`),
    INDEX gender(`gender`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

MySQL 特点

• 数据以表格的形式出现
• 表格中每一行表示一组数据
• 表格中每一列表示某组数据对应的字段（属性）
• 若干这样的行和列就组成了一张表
• 若干个表格组成一个库

MySQL 服务就是维护了若干个这样的库

查询所有数据

SELECT * FROM table_name

实际使用中，并不推荐 *

SELECT column_name,column_name FROM table_name

别名

SELECT column_name as c1,column_name as c2 FROM table_name as t WHERE t.column_name = val

列：

select username,age from users;

node & mysql - mysql2

安装

npm i mysql2

使用

const mysql = require('mysql2/promise')

连接数据库

const connection = await mysql.createConnection(opts)

opts选项

host：数据库服务器
user：数据库连接用户名称
password：数据连接密码
database：要操作的数据库

添加数据

INSERT INTO table_name(field1,...fieldN) VALUES(value1,...valueN)

列：

数据添加

 insert into users (username,age,gender) values ('小明',18,'男');

批量添加

insert into `user` (`username`, `age`, `gender`) values ('lili', 6, '女'),('小明', 18, '男');

Node.js - mysql2

connection.query('INSERT INTO table_name (fileld1,fileld2) VALUES (value1,value2)')

返回值依据其操作来决定

- INSERT INTO: [{affectedRows,insertId},undefined]

更新数据

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

列：

update todos set title='set' where id=14

Node.js - mysql2

connection.query('UPDATE table_name SET ??=?',[key,value])

返回值依据其操作来决定

- UPDATE: [{affectedRows,insertId},undefined]

删除数据

DELETE FROM table_name
WHERE some_column=some_value;

列：

delete from todos where id=14

删除表的其他方法

• DROP 表名称：删除表、数据以及结构
• TRUNCATE 表名称：删除表的数据，保留结构，不支持事务，不可撤销恢复
• DELETE 表名称：删除表的数据, 保留结构，支持事务

Node.js - mysql2

connection.query('DELETE FROM table_name WHERE id=?',[1])

返回值依据其操作来决定

- DELETE: [{affectedRows},undefined]

查询数据

connection.query(SQL语句)

返回值依据其操作来决定

• SELECT：[数据集合，字段集合]

条件查询

SELECT column_name,column_name FROM table_name [WHERE Clause]

WHERE 子句

WHERE condition1 [AND [OR]] condition2......

操作符

=、<>,!=、>、<、>=、<=

查询参数占位符

.query('SELECT ??,?? FROM ?? WHERE ?? = ?',['id','username','users','id','1'])

??: 字段名、表名
？:值

数量限制查询

SELECT column_name,column_name FROM table_name [LIMIT N]

N：数字，要限制的查询数据的最大条数

查询偏移

SELECT column_name,column_name FROM table_name [LIMIT N] [OFFSET M]

M：数字，要偏移的数量值，从0开始
OFFSET 必须与 LIMIT 一起使用，且 LIMIT 在前

偏移与限制

SELECT column_name,column_name FROM table_name [LIMIT [M,]N]

注意，这种写法偏移在前，限制在后，如果只有一个数字，默认为限制
ORDER BY 必须在 LIMIT 之前 WHERE（GROUP BY） 之后

去重

DISTINCT:查询表中不重复的记录，如果指定多个字段，则作为联合条件

SELECT DISTINCT column_name,column_name... FROM table_name

模糊查询

LIKE:模糊查询，通常与 % 配合使用，不使用 % 同 =
% 类似 *，通配

%name:以 name 结尾的内容
name%:以 name 开头的内容
%name%:包含 name 的内容

通配符
%：一个或多个字符
_：一个字符

SELECT column_name... FROM table_name WHERE column_name LIKE %name%

例：

select * from todos where title LIKE '%node%'

NOT LIKE:与 LIKE 相反

正则

SELECT 字段... FROM 表名 WHERE 字段名 REGEXP '规则'

规则：正则表达式
注意：字符串转义，\d 需要写成 '\d'

例：

SELECT * FROM user WHERE age REGEXP '3|1';

多值匹配

IN:多值匹配

SELECT column_name... FROM table_name WHERE column_name IN (value1,value2...)

例：

select title from todos where title IN ('123','1234')

NOT IN:与 IN 相反

范围查询

BETWEEN:范围查询

SELECT column_name... FROM table_name WHERE column_name BETWEEN value1 AND value2

NOT BETWEEN :与 BETWEEN 相反

排序

ORDER BY:按照某个字段某种规则进行排序

SELECT column_name... FROM table_name ORDER BY column_name1 DESC,column_name2 ASC

• DESC:降序
• ASC:升序，默认
• 如果有多个排序字段和规则，执行顺序为从左到右

多表查询

SELECT * FROM 表一, 表二 WHERE 表一.字段 运算符 表二.字段

例：

SELECT * FROM user,message WHERE user.id=message.uid;

内连接（同上）

SELECT * FROM 表一 JOIN 表二 ON 表一.字段 运算符 表二.字段
SELECT * FROM 表一 INNER JOIN 表二 ON 表一.字段 运算符 表二.字段

例：

SELECT * FROM user JOIN message ON user.id=message.uid;
SELECT * FROM user INNER JOIN message ON user.id=message.uid;   

左连接

SELECT 字段 FROM 表一 LEFT JOIN 表二
ON 表一.字段 运算符 表二.字段

LEFT JOIN 关键字从左表（表一）返回所有的行，即使右表（表二）中没有匹配。如果右表中没有匹配，则结果为 NULL。

例：

SELECT * FROM user LEFT JOIN message ON user.id=message.uid;

右连接

SELECT 字段 FROM 表一 RIGHT JOIN 表二
ON 表一.字段 运算符 表二.字段

RIGHT JOIN 关键字从右表（表二）返回所有的行，即使左表（表一）中没有匹配。如果左表中没有匹配，则结果为 NULL。

SELECT * FROM user RIGHT JOIN message ON user.id=message.uid;

函数

SQL 也提供了一些内置函数，以便对数据进行一些常规操作

• 聚合函数
  计算从列中取得的值，返回一个单一的值，如：COUNT、SUM、MAX、MIN

• 标量函数
  基于输入值，返回一个单一的值，如：UCASE、LCASE、NOW

COUNT

返回匹配指定条件的行数

SELECT COUNT(column_name) FROM table_name

例：

select count(id) from todos

SUM

返回数值列的总数

SELECT SUM(column_name) FROM table_name

例：

select sum(id) from todos

AVG

返回数值列的平均值

SELECT AVG(column_name) FROM table_name

例：

select avg(id) from todos

MAX

返回指定列的最大值

SELECT MAX(column_name) FROM table_name

例：

select max(id) from todos

MIN

返回指定列的最小值

SELECT MIN(column_name) FROM table_name

例：

select min(id) from todos

UCASE

把字段的值转换为大写

SELECT UCASE(column_name) FROM table_name

例：

select UCASE(title) from todos

LCASE

把字段的值转换为小写

SELECT LCASE(column_name) FROM table_name

例：

select LCASE(title) from todos

MID

从文本字段中提取指定字符

SELECT MID(column_name,start[,length]) FROM table_name

start:从1开始计算

例：

select MID(title,1,3) from todos

LENGTH

返回文本字段中值的长度

SELECT LENGTH(column_name) FROM table_name

例：

select LENGTH(title) from todos

NOW

返回当前系统的日期和时间

SELECT NOW() FROM table_name

例：

SELECT NOW() FROM todos // 2019-07-24 09:54:32

GROUP BY

用于结合聚合函数，根据一个或多个列对结果集进行分组

SELECT column_name FROM table_name GROUP BY column_name1

存储引擎

数据在计算机上存储的方式

MYSQL 常见存储引擎：InnoDB、MyISAM等

• InnoDB的优势在于提供了良好的事务处理、崩溃修复能力和并发控制。缺点是读写效率较差，占用的数据空间相对较大

• MyISAM的优势在于占用空间小，处理速度快，缺点是不支持事务的完整性和并发性

字符集、编码

指数据库存储的数据的编码

• utfmb4 ：支持更多的 unicode 字符（四字节）

数据校对

数据库除了要存储数据，还要对数据进行排序，比较等操作，不同的校对规则会有不同的结果

• utfmb4_unicode_ci:基于标准的 Unicode 来排序和比较，能够在各种语言之间精确排序

数据类型

数据存储的类型

数字类型：INTEGER,INT,SMALLINT,TINYINT,MEDIUMINT,BIGINT,DECIMAL,NUMERIC,FLOAT,DOUBLE

日期时间类型：DATE,DATETIME,TIMESTAMP,TIM,YEAR

字符串类型：CHAR,VARCHAR,BINARY,VARBINARY,BLOB,TEXT,ENUM,SET

主键

表中的一个或多个字段，它的值用于唯一地标识表中的某一条记录，用来保持数据的完整性

• 一个表只能有一个主键
• 主键可以是第一个字段，也可以有多个字段组成
• 主键值不能重复
• 加快对数据的操作

自增

auto_increment

添加数据的时候由数据库自动设置的值
一般在设计表的时候会设置一个自动增加字段作为主键

索引

对表中一列或多列（注意是列）的值进行排序的一种结构，使用索引可快速访问表中特定的信息
加快对表中记录的查找或排序