[MySQL]基础+CRUD_mysql定义数据类型为变长字符串-优快云博客

本文链接：https://blog.youkuaiyun.com/Rserendipity/article/details/132641046

1、数据库由来

不妨先思考这个问题：既然语言本身已经封装了文件操作，例如C++的fstream类，Java的Writer/Reader类，Python的open()，为什么还需要数据库呢？

文件的安全性问题，直接写入到文件，写入的文件不会自动加密，会有隐私问题。
文件不利于数据查询和管理，不可能每次操作文件都写对应的代码吧，那比学习SQL更加麻烦了。
文件不利于存储海量数据，语言提供的文件操作只是最基础的功能，当文件越来越大，就需要一定的管理了，这就涉及到数据库的存储引擎的概念了。
文件在程序中控制不方便，例如上文管理，不同地方、不同项目的存储都需要编写不一样的代码，工作量多很多，并且也不好进行代码的管理。

为了解决上述问题（于是那些大佬们设计了一层软件层，更好的管理文件，即SQL语言），统一、方便的进行文件操作。

2、连接数据库

关于数据库的安装，许多博客都有完整的教程，不再赘述。

命令行登录形式：
mysql -h [localhost] -u [root] -p123456 -P

演示：使用root用户，密码123456，登录本地的mysql服务器
mysql -u root -p123456
注意：-p和密码之间没有空格，其他选线之间由空格隔开，也可以不在这里写密码，直接使用mysql -u root -p 登录，然后再终端内输入密码。

-u（必选，user缩写）：指定登录用户
-h（可选，host缩写）：指定主机号，不加这个选项默认是 localhost（本机）
-p（可选，password缩写，p为小写）：指定密码，不加这个默认是无密码登录
-P（可选，Port缩写，P为大写）：指定登录端口，mysql默认端口是3306

常见错误

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

密码不正确

ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)

端口不正确

3、库操作

注意：数据库的关键字忽略大小写，所以以下代码等价：
show databases;
SHOW DATABASES;
为了阅读代码方便，故下文全部使用小写

1、显示所有数据库：

show databases; # databases是复数形式

2、创建数据库：

create database [if not exists] database_name [extra_option];

方括号 []的内容为可选
if not exists：如果待创建的库不存在的话，才创建这个数据库，不加这个选项，当创建的数据库重名时，创建失败。
extra_option：额外的选项，可以指定该数据库的字符集以及校验集。不指定时使用全局配置文件的默认设置。
– charset=XXX; 指定字符集为XXX
– collate XXX; 指定校验集为XXX

查看数据库支持的字符集与校验规则
show charset;
show collation;

查看系统默认字符集以及校验规则
show variables like 'character_set_database';
show variables like 'collation_database';

例子：

创建名为 db1 的数据库：

create database db1;

创建一个使用utf字符集，并带校对规则的 db2 数据库：

create database db2 charset=utf8 collate utf8_general_ci;

3、删除数据库

drop database [databases_name];

删除名为database_name的数据库

删除数据库是非常危险的操作！！！
删除数据库会导致其中的表全部被删除，无法恢复！！！
《删库跑路》的删库，指带的就是这个操作

4、修改数据库字符集/校验集

alter database database_name [extra_option];

database_name：需要更改的数据库名
[extra_option] 同上文
– charset=XXX; 更改字符集为XXX
– collate XXX; 更改校验集为XXX

修改库名为db2的字符集为gbk

alter database db2 charset=gbk;

5、修改数据库名

修改数据库名较为复杂，需要用到数据库的备份/恢复功能。

备份操作为shell中进行，而非mysql中

语法
mysqldump -u [user_name] -p[password] [database_name] > [file_path]
实际用法
mysqldump -u root -p123456 test > /home/cjj/save.sql
解释：使用root用户，把test数据库备份到/home/cjj路径下的save.sql文件里。

备份操作的本质：把当前数据库里所有的sql语句保存起来，包括创建表，修改表，向标中插入数据等等所有操作。

恢复操作在mysql中进行

语法
source [file_path];
如果在备份时加上了-B选项，恢复时会自动创建原库名。如果没有加上该选项，那么需要先使用use [database];选择数据库，选择该数据库以后恢复的表会在选择的数据库中进行。

于是我们可以得到更改数据库名的操作：

不使用-B选项进行备份
创建新的数据库
使用新的数据库
将备份恢复到新的数据库
删除原数据库

4、表操作

表的操作主要就是增删改查，也就是我们平常说的CRUD

在进行任何表操作时，都需要先选择一个数据库

1、创建表

语法：

create table table_name (
	[字段1] [类型] [comment XXX] [option],
	[字段2] [类型] [comment XXX] [option],
	. . . 
)[extra_option];

解释：

table_name（必选）：你需要创建表的名字
字段/类型（至少写一个字段/类型）：圆括号里写上对应的字段以及类型
comment（可选）：注释，创建表以后注释会被忽略，但是可以查看表信息从而查看注释
option（可选）：设置表的约束信息，约束章节详细讲解
extra_option（可选）：设置表的数据存储字符集和校验集，具体如下：
- character set [选择字符集]
- collate [选择校验规则]
- engine [选择存储引擎]

示例：

创建一个名为users的表
该表含有四个字段，分别为id name password birthday
类型分别为int varchar char date类型会在下面数据类型章节介绍
字符集选择为utf8
存储引擎选择为MyISAM

create table users (
	id int,
	name varchar(20) comment '用户名',
	password char(32) comment '密码是32位的md5值',
	birthday date comment '生日'
) character set utf8 engine MyISAM;

2、查看表的信息：

查看上面users的字段和类型

mysql> desc users;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| password | char(32)    | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

查看创建users表时的具体操作

mysql> show create table users;
+-------+----------------------------------------------------------------------+
| Table |                           Create Table                               |
+-------+----------------------------------------------------------------------+
| users | CREATE TABLE `users` (                                               |
|       |  `id` int DEFAULT NULL,                                              |
|       |   `name` varchar(20) DEFAULT NULL COMMENT '用户名',                   |
|       |   `password` char(32) DEFAULT NULL COMMENT '密码是32位的md5值',        |
|       |   `birthday` date DEFAULT NULL COMMENT '生日'                         |
|       |   ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3；                           |
+-------+----------------------------------------------------------------------+
1 row in set (0.02 sec)

3、修改表

语法：

alter table table_name [option];

解释：

table_name：需要修改的表名
option：对表的操作
- rename：重命名表
  - 代码：rename to [new_table_name];
  - 解释：将table_name表名更改为new_table_name，关键字to可以省略
- add：增加一列
  - 代码：add [col_name] [col_type] after [old_col_name];
  - 解释：在old_col_name这一列后面新增一列，列字段为col_name，类型为col_type
- modify：修改某一列的属性
  - 代码：modify [col_name] [new_type];
  - 解释：更改col_name的类型为new_type
- change：修改列名
  - 代码：change [old_col] [new_col] [new_col_type];
  - 解释：将old_col修改成：new_col字段，new_col_type属性
- drop：删除某一列
  - 代码：drop [col_name]
  - 解释：删除col_name这一列
  - 注意：删除一列以后，该列的信息会丢失

示例：（以上文中创建的users为例）

更改表名为user_info
- alter table users rename to user_info;
新增一列地址信息，字段为address，类型为varchar(50)
- alter table user_info add address varchar(50) comment '地址' after birthday;
更改name的类型为text
- alter table user_info modify name text;
更改password为pd
- alter table user_info change password pd varchar(20);
删除生日这一列
- alter table user_info drop birthday;

经过上述的操作以后，此时查看user_info表结果如下：

mysql> desc user_info;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int         | YES  |     | NULL    |       |
| name    | text        | YES  |     | NULL    |       |
| pd      | varchar(20) | YES  |     | NULL    |       |
| address | varchar(50) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

4、向表中插入数据（增）

语法：insert into [table_name] values ([信息1] [信息2]…);

解释：

向table_name表中插入信息
信息的类型必须和定义表时匹配，如果类型不匹配，会导致插入失败

示例：

插入一条id为1，姓名为zhangsan，密码为123，地址为zhongguo的信息到user_info内
insert into user_info values (1, 'zhangsan’, '123’, 'zhongguo’);
插入多行信息：可以在一条信息后加上逗号，从而插入多行
insert into user_info values (2, 'lisi', '456’, 'US’), (3, 'wangwu’, '789’, 'AAA’);

5、删除表中的行（删）

语法：delete from [table_name] [option];

解释：

删除table_name表中的行
不加option时，会删除全表数据，所以，没有加任何option约束的delete是非常危险的
option：筛选
- where子句：当满足某种条件时，删除该行的数据
  - 例如where id=1：删除id等于1的行
  - 例如where name='zhangsan'：删除名字为zhangsan的行
  - 例如where name like 'z%'：删除名字前缀为z的行
  - 例如where name like 'z_'：删除名字为z某的行
  - %：匹配0到任意个字符
  - _：匹配一个字符
- 多个条件可以用and或者or连接，对应各类语言的&&和||操作
  - where id=1 or name='lisi';：删除 id等于1 或者名字等于lisi 的行

示例：

删除user_info表中姓名为zhangsan的行
delete from user_info where name='zhangsan';
删除user_info表中id为3的行
delete from user_info where id=3;

6、查看表中的数据（查）

语法：select [datas_description] from table_name [option];

解释：

从table_name表中查找数据
datas_description：需要查找的列信息
- *：全列信息，不过一般不建议这么使用，查询的列越多，所需要消耗的资源越多，也可能会影响到索引。
- [列1],[列2],[列3] .... ：指定部分信息查询，即需要什么就查询什么
- 额外选项distinct：对该列的结果去重
option：筛选
- where的效果和使用方法和上文delete类似
- order by：排序
  - order by id：按照id排序（默认升序）
  - asc：升序选项
  - desc：降序选项
- limit：分页
  - limit [n] offset [s]
  - 从偏移量为s的地方开始，显示n条数据

示例：

查看user_info表中所有记录
select * from user_info;
查看user_info表中密码记录，并去重
select distinct pd from user_info;
查看user_info表 id大于2小于5 的姓名和住址
select name,address from user_info where id >= 2 and id <= 5;
查看user_info表全部信息，分一页查询，一页只显示3条数据
select * from user_info limit 3 offset 0;

7、修改表中的数据（改）

语法：update [table_name] set [new_info] [option];

解释：

更新table_name表中的数据
将某列更新为new_info，不加option时会全列更新
option：和上述select和delete中的一致

示例：

将user_info表中的id为1的密码更新为abcdefg
update user_info set pd='abcdefg' where id=1;
将user_info表中的name为zhangsan的地址更新为US
update user_info set address='US' where name=‘zhangsan’;

5、数据类型

1. 数据类型

常用的用加粗标注

分类	数据类型	说明
数值	bool	布尔值，1/0 代表真假逻辑
	bit(M)	位类型，M指定位数，范围1 ~ 64
	tinyint [unsigned]	带符号范围-127 ~ 128，无符号范围0 ~ 255
	smallint [unsigned]	带符号范围-2¹⁵ ~ 2¹⁵-1，无符号范围0 ~ 2¹⁶ -1
	int [unsigned]	带符号范围-2³¹ ~ 2³¹-1，无符号范围0 ~ 2³² -1
	bigint [unsigned]	带符号范围-2⁶³ ~ 2⁶³-1，无符号范围0 ~ 2⁶⁴ -1
	float[(M,D)] [unsigned]	M：指定长度，D：指定小数点精度，占用4bytes
	double[(M,D)] [unsigned]	M：指定长度，D：指定小数点精度，占用8bytes，精度比float更高
	decimal(M,D)	最高精度的浮点数，M、D意义同上
文本、二进制	char(size)	固定长度的字符串，最大长度255
	varchar(size)	变长字符串，最大长度65535
	blob	二进制数据
	text	大文本文件，不支持索引，不支持默认值
时间	data	yyyy-mm-dd 格式的时间
	datatime	yyyy-mm-dd hh:mm:ss 格式的时间
	timestamp	yyyy-mm-dd hh:mm:ss 格式的时间，表示当前的时间戳，更新该行数据时会自动更新至系统当前时间戳
枚举	enum	枚举，多选一
	set	枚举，多选多

2. 数值类型测试：

整数：

mysql> create table tt1(num tinyint);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tt1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tt1 values(128); -- 越界插入，报错
ERROR 1264 (22003): Out of range value for column 'num' at row 1

说明：

在mysql中可以指定数据类型为有符号/无符号，默认是有符号的
不过一般不用指定为无符号，一般来说，有符号存不下的，无符号也大概率存不下，应该换更大类型来存

浮点数：

float / double：

语法：float[(m, d)] [unsigned] : M指定显示长度，d指定小数位数，占用空间4个字节
使用：小数：float(4,2)表示的范围是-99.99 ~ 99.99，MySQL在保存值时会进行四舍五入。
案例：

mysql> create table tt6(id int, salary float(4,2));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tt6 values(100, -99.99);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tt6 values(101, -99.991); #多的这一点被拿掉了
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt6;
+------+--------+
| id | salary |
+------+--------+
| 100 | -99.99 |
| 101 | -99.99 |
+------+--------+
2 rows in set (0.00 sec)

decimal：

语法：decimal(m, d) [unsigned] : 定点数m指定长度，d表示小数点的位数

使用：decimal(5,2) 表示的范围是-999.99 ～ 999.99

mysql> create table tt8 ( id int, salary float(10,8), salary2
decimal(10,8));

mysql> insert into tt8 values(100,23.12345612, 23.12345612);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt8;
+------+-------------+-------------+
| id   |    salary   |   salary2   |
+------+-------------+-------------+
| 100  | 23.12345695 | 23.12345612 | # 发现decimal的精度更准确，因此如果我们希望某个数据表示高精度，选择decimal
+------+-------------+-------------+

可以发现，decimal的精度比float的精度高很多，所以，需要更高精度时，建议使用decimal

文本 / 字符串：

char(size)：

固定长度的字符串，size为字符串的长度。存入的字符串的长度不能超过这个值，当字符串的长度没有达到size时，剩下的空间会浪费。
可以类比于 C/C++ 语言的：char str[size];

varchar(size)：

变长的字符串，size为字符串的最大长度，存入的字符串长度也不能超过这个值。会使用额外的空间记录长度，当存入字符串的长度未达到size时，不会造成浪费，但是会占用额外的1-3字节存储字符串长度
可以类比于 C++ 语言的 string str;

blob(size): （较少使用）

存放二进制的数据，size含义同上。
可以类比于Java的 byte str[size];

text：（较少使用）

无需指定长度，最大长度65535字符
一般用于存放大文件
不推荐直接把数据放入到mysql中
建议文件保存，在mysql中使用varchar类型存放文件路径

比较char和varchar：

待存储数据	char(4)	varchar(4)	char实际占用字节	varchar实际占用字节
abcd	abcd	abcd	4*3=12	4*3+1=13
A	A	A	4*3=12	1*3+1=4
abcde	无法存储	无法存储	数据超过长度	数据超过长度

如何选择定长或变长字符串？

如果数据确定长度都一样，就使用定长（char），比如：身份证，手机号，md5
如果数据长度有变化,就使用变长(varchar), 比如：名字，地址，但是你要保证最长的能存的进去。
定长的磁盘空间比较浪费，但是效率高。
变长的磁盘空间比较节省，但是效率低。
定长的意义是，直接开辟好对应的空间
变长的意义是，在不超过自定义范围的情况下，用多少，开辟多少。

测试：

# 创建表
mysql> create table t1 (s1 char(5), s2 varchar(5), s3 text);
Query OK, 0 rows affected (0.01 sec)

# 插入正常数据
mysql> insert into t1 value (‘12345’, ‘12345’, ‘this is a big text’);
Query OK, 1 rows affected (0.01 sec)

# 插入越界数据
mysql> insert into t1 value (‘123456’, NULL, NULL);
ERROR 1406 (22001): Data too long for column ‘s1’ at row 1

# 插入越界数据
mysql> insert into t1 value (NULL, ‘123456’, NULL);
ERROR 1406 (22001): Data too long for column ‘s2’ at row 1

时间 / 日期：

data：

YYYY-MM-DD格式的时间，占用3字节
用于记录生日这种只需要知道日期、不需要精确的时分秒的时候

datatime：

YYYY-MM-DD HH:II:SS 格式的时间，占用8字节
用于需要记录精确时间点的时候

timestamp：

自动记录从1970年开始的时间差，格式同datatime，占用4字节
在插入数据/更新数据的时候，时间戳会自动更新

测试：

# 创建表
mysql> create table birthday (t1 date, t2 datetime, t3 timestamp);
Query OK, 0 rows affected (0.01 sec)

# 插入数据：
mysql> insert into birthday(t1,t2) values('1997-7-1','2008-8-8 12:1:1'); --插入两种时间
Query OK, 1 row affected (0.00 sec)

mysql> select * from birthday;
+------------+---------------------+---------------------+
| t1 | t2 | t3 |
+------------+---------------------+---------------------+
| 1997-07-01 | 2008-08-08 12:01:01 | 2023-9-18 18:28:55 | --添加数据时，时间戳自动补上当前时间
+------------+---------------------+---------------------+
1 row in set (0.01 sec)

枚举：

enum：

多选一，只能在预设的信息里选择一个
每一个枚举值，在保存的时候是用下标标记的，下标从1开始，到n
设有枚举量[a, b, c, d, e]，填选d时，可以填d，或者4

set：

多选多，可以在预设的信息里选择多个
同样是用下标标记的，对应的是二进制序列
设有枚举量[a, b, c, d, e]，填选ad时，可以填’a,d’，或者9（二进制序列位1001）
- a -> 1（二进制1）
- b -> 2（二进制10）
- a,b -> 3（二进制11）
- c -> 4（二进制100）
- ……

测试：

# 建表
mysql> create table votes(
-> username varchar(30),
-> hobby set('登山','游泳','篮球','武术'),
-> gender enum('男','女')
-> );
Query OK, 0 rows affected (0.02 sec)

# 插入数据
mysql> insert into votes values('雷锋', '登山,武术', '男');
Query OK, 1 rows affected (0.02 sec)

mysql> insert into votes values('Juse','登山,武术',2);
Query OK, 1 rows affected (0.02 sec)

# 通过enum的下标进行查询
mysql> select * from votes where gender=2;
+----------+---------------+--------+
| username |     hobby     | gender |
+----------+---------------+--------+
| Juse     |   登山,武术    |  女     |
+----------+---------------+--------+
1 row in set (0.02 sec)