数据库

2019-07-18 本文已影响0人 shadow123

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

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

索引

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

数据库

MySQL

MySQL 特点

查询所有数据

node & mysql - mysql2

连接数据库

添加数据

更新数据

删除数据

删除表的其他方法

查询数据

条件查询

查询参数占位符

数量限制查询

查询偏移

偏移与限制

去重

模糊查询

正则

多值匹配

范围查询

排序

多表查询

函数

COUNT

SUM

AVG

MAX

MIN

UCASE

LCASE

MID

LENGTH

NOW

GROUP BY

存储引擎

字符集、编码

数据校对

数据类型

主键

自增

索引

猜你喜欢

热点阅读