MySQL 数据库入门

MySQL

创建要操作的数据库

连接

mysql -u root -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

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

索引

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

最后编辑于：2024.03.08 01:27:40

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 215,634评论 6赞 497
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 91,951评论 3赞 391
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 161,427评论 0赞 351
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 57,770评论 1赞 290
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 66,835评论 6赞 388
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 50,799评论 1赞 294
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 39,768评论 3赞 416
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 38,544评论 0赞 271
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 44,979评论 1赞 308
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 37,271评论 2赞 331
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,427评论 1赞 345
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 35,121评论 5赞 340
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 40,756评论 3赞 324
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,375评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,579评论 1赞 268
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 47,410评论 2赞 368
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,315评论 2赞 352

MySQL 数据库入门

MySQL

MySQL 特点

查询所有数据

node & mysql - mysql2

连接数据库

添加数据

更新数据

删除数据

删除表的其他方法

查询数据

条件查询

查询参数占位符

数量限制查询

查询偏移

偏移与限制

去重

模糊查询

正则

多值匹配

范围查询

排序

多表查询

函数

COUNT

SUM

AVG

MAX

MIN

UCASE

LCASE

MID

LENGTH

NOW

GROUP BY

存储引擎

字符集、编码

数据校对

数据类型

主键

自增

索引

推荐阅读更多精彩内容