MySQL 数据库

发表于 | 阅读次数

MySQL 数据库

创建要操作的数据库

  • 连接
1
mysql -uroot -p
  • 查看所有的数据库
1
show databases;
  • 选择要操作的数据库
1
use <database>;
  • 创建新的数据库
1
create database <database>;

例：

1
CREATE DATABASE users DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_bin
  • 创建数据表
1
CREATE TABLE 数据库表名称 (
2
    字段名称 字段属性...,
3
    PRIMARY KEY (主键字段名称),
4
    INDEX 索引名称(索引字段...)...
5
) ENGINE=InnoDB DEFAULT CHARSET=utf8

字段属性设置

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

例：

1
CREATE TABLE user(
2
	`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
3
	`username` VARCHAR(50) NOT NULL DEFAULT '',
4
	`age` TINYINT UNSIGNED NOT NULL DEFAULT 0,
5
	`gender` ENUM('男', '女') NOT NULL DEFAULT '男',
6
	PRIMARY KEY (`id`),
7
	INDEX uname(`username`),
8
	INDEX age(`age`),
9
	INDEX gender(`gender`)
10
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

MySQL 特点

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

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

查询所有数据

1
SELECT * FROM table_name

实际使用中，并不推荐 *

1
SELECT column_name,column_name FROM table_name

别名

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

例：

1
select username,age from users;

node & mysql - mysql2

安装 

1
npm i mysql2

使用

1
const mysql = require('mysql2/promise')

连接数据库

1
const connection = await mysql.createConnection(opts)

opts选项

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

添加数据

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

例：

数据添加

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

批量添加

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

Node.js - mysql2

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

返回值依据其操作来决定

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

更新数据

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

例：

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

Node.js - mysql2

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

返回值依据其操作来决定

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

删除数据

1
DELETE FROM table_name
2
WHERE some_column=some_value;

例：

1
delete from todos where id=14

删除表的其他方法

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

Node.js - mysql2

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

返回值依据其操作来决定

1
- DELETE: [{affectedRows},undefined]

查询数据

1
connection.query(SQL语句)

返回值依据其操作来决定

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

条件查询

1
SELECT column_name,column_name FROM table_name [WHERE Clause]

WHERE 子句

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

操作符

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

查询参数占位符

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

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

数量限制查询

1
SELECT column_name,column_name FROM table_name [LIMIT N]

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

查询偏移

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

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

偏移与限制

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

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

去重

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

1
SELECT DISTINCT column_name,column_name... FROM table_name

模糊查询

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

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

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

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

例：

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

NOT LIKE:与 LIKE 相反

正则

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

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

例：

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

多值匹配

IN:多值匹配

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

例：

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

NOT IN:与 IN 相反

范围查询

BETWEEN:范围查询

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

NOT BETWEEN :与 BETWEEN 相反

排序

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

1
SELECT column_name... FROM table_name ORDER BY column_name1 DESC,column_name2 ASC
  • DESC:降序
  • ASC:升序，默认
  • 如果有多个排序字段和规则，执行顺序为从左到右

多表查询

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

例：

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

内连接（同上）

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

例：

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

左连接

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

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

例：

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

右连接

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

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

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

函数

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

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

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

COUNT

返回匹配指定条件的行数

1
SELECT COUNT(column_name) FROM table_name

例：

1
select count(id) from todos

SUM

返回数值列的总数

1
SELECT SUM(column_name) FROM table_name

例：

1
select sum(id) from todos

AVG

返回数值列的平均值

1
SELECT AVG(column_name) FROM table_name

例：

1
select avg(id) from todos

MAX

返回指定列的最大值

1
SELECT MAX(column_name) FROM table_name

例：

1
select max(id) from todos

MIN

返回指定列的最小值

1
SELECT MIN(column_name) FROM table_name

例：

1
select min(id) from todos

UCASE

把字段的值转换为大写

1
SELECT UCASE(column_name) FROM table_name

例：

1
select UCASE(title) from todos

LCASE

把字段的值转换为小写

1
SELECT LCASE(column_name) FROM table_name

例：

1
select LCASE(title) from todos

MID

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

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

start:从1开始计算

例：

1
select MID(title,1,3) from todos

LENGTH

返回文本字段中值的长度

1
SELECT LENGTH(column_name) FROM table_name

例：

1
select LENGTH(title) from todos

NOW

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

1
SELECT NOW() FROM table_name

例：

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

GROUP BY

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

1
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

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

索引

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