Mysql数据库

本来这一部分内容一开始是放在 nodejs_study `` 中的，但是由于mysql完全属于后端开发的一个重点知识并且具有自己的体系，所以拿出来单开比较合理。

数据库的组成

database、collection、document

collection类似于数组，document类似于对象，是最小的基本单位，doucment中的属性称为字段

数据库的使用

通过sql查询语句在dbms（数据库管理软件）中操作db

sql的使用

sql的命名规定与规范

1.除了变量名限制为29个字符以外，其余命名不得超过30个字符

2.只能用字母(大小写敏感)、数字、下划线命名，并且数字不能开头，不能包含空格

3.同一个mysql软件中数据库不能同名；同一个数据库中，表不能同名；同一个表中，字段不能重名

4.命名不能用保留字，如果坚持保留，需要使用``着重号引起来

注释

单行 #

多行 / /

查看版本号

select version();

DDL（数据定义语言）

DDL主要分为四步：创建库-定字段-创建表-插数据

DDL不涉及对数据的操作，而是关注数据库的结构和元数据（容器）

关键字

CREATE：创建数据库、表、索引、视图等

ALTER：用于修改数据库对象的结构，如修改表结构、添加列、删除列等

DROP：用于删除数据库对象，如删除表、删除索引等

创建库

CREATE DATABASE datebase_name;

CREATE DATABASE IF NOT EXISTS;(检查数据库是否已存在)

CREATE DATABASE datebase_name CHARACTER character_set;（指定字符集库，默认值为utf8mb4）

CREATE DATABASE datebase_name COLLATE collate_rule;(指定排序规则,默认值为utf8mb4_0900_ai_ci，是一种不区分大小写的排序规则)

command不区分大小写

库管理

查看当前所有库：show databases;

查看当前使用库：select DataBase();

查看库中所有表：show tables from database_name;

查看创建库的信息：show create database database_name;

切换库：use database_name;

删除库：drop database database_name (if exists database_name);

创建表

create table table_name(
column_name type [column_restriction] [comment 'column comment'],
column_name type [column_restriction] [comment 'column comment'],
column_name type [column_restriction] [comment 'column comment'],
...)[comment 'table comment']

for example

create table if not exists book(
    book_name varchar(20) comment '图书名',
    book_price double(4,1) comment '图书价格',
    book_num int comment '图书数量'
)comment '图书表';

注意最后一项不能加逗号

核心要素：

表名
列名
列类型

可选要素：

列约束
表配置
表和列的注释

数据类型

整数

标准类型：int（4byte）和smallint（2byte）

mysql独立支持：tinyint(1byte);mediumint(3byte);bigint(8byte)

如果在type后加上unsigned（无符号），那么数据将没有负值，从零开始且整数变成原来的将近二倍

浮点数

decimal（m, d)

decimal存储较精确的数值数据值，m最大为65，d最大为30

float(m, d)

m是整个数字的位数，最大为24位; d是小数的位数，最大为8位

double(m, d)

m是整个数字的位数，最大为53位; d是小数的位数，最大为30位

精准度：decimal>double>float

运行速度：float>double>decimal

如果加上unsigned，不会将负值范围扩大到正值范围内，即：无符号=无负号

字符串

char(M) ，M是字符串长度，默认值为1，存储空间为M*4个字节，M在1和255之间，如果输入的字符串的长度小于M，则会在其后加上空格补全。

varchar(M)，M是字符串长度，必须填写，存储空间为M*4+1个字节，根据输入的字符的长度自动调整内存大小。

<div style="border: 1pxgreenyellow;text-align: center;width: 100px;">

为什么是4*M+1呢？因为varchar可能存入空值，1个字节是为空值准备的；相比之下，char不会存入空值（默认填充空格），所以没有这一个多出来的字节

</div>

sql检索char类型的数据时会自动去除尾部的空格，char类型速度快，varchar类型占用内存小。

mysql中一行数据的最大占有空间为65535字节，除了TEXT和BLOBS类型。

文本

text是字符串的大文本类型，不需要声明最大长度，没有长度限制。

短文本建议使用char和varchar，长文本建议使用text，但是text的性能较差，可以使用varchar记录文件地址。

时间

类型	名称	字节	日期格式	最小值	最大值
YREAR	年	1byte	YYYY或YY	1901	2155
TIME	时间	3byte	HH:MM:SS	-838:59:59	838:59:59
DATE	日期	3byte	YYYY-MM-DD	1000-01-01	9999-12-03
DATETIME	日期时间	8byte	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00	9999-12-31 23:59:59
TIMESTAMP	日期时间	4byte	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:00	2038-01-19 03:14:07

默认自动初始化和更新

(更新当前时间并插入)

column_name DATETIME(TIMESTAMP) (DEFAULT CURRENT_TIMESTAMP) (ON UPDATE CURRENT_TIMESTAMP);

（直接插入不新）

column_name DATETIME(TIMESTAMP) (DEFAULT CURRENT_TIMESTAMP);

表管理

修改表数据
ALTER TABLE table_name 
    ADD 字段名 TYPE COMMENT;
    CHANGE 原字段名 字段名 new_type COMMENT;
    MODIFY 字段名 new_type COMMENT;
    DROP 字段名;
删除表
DROP TABLE table_name;
清除表数据
TRUNCATE TABLE table_name;

删除表和清除表数据都无法回滚！

DML（数据操纵语言）

作用：插入、更新、删除

dml不影响库表结构，但影响数据库数据，基本操作单位是行

insert into

所有字段都插入：insert into table_name values(value1, value2, …);

指定字段插入：insert into table_name(column_name1, column_name2) values(value1, value2);

多行同时插入：insert into table_name values(value1, value2, …) ,(value11,value22, …);

update

全表修改，修改表中所有行的数据：update table_name set column1= value1, column2 = value2, … ;

条件全表修改：update table_name set column1 = value1, column2 = value2, … where condition;

mysql_study