Tim's Life
MySQL库表操作
编辑
2018-06-09
计算机基础科学
00
请注意，本文编写于 1933 天前，最后修改于 105 天前，其中某些信息可能已经过时。

目录

数据库操作
字符集与校验规则
创建数据库
修改数据库
删除数据库
备份和恢复
查看连接情况
表的操作
创建表
查看表结构
修改表/表结构
Mysql数据类型
数值类型
tinyint-bigint
bit
float
decimal
字符串类型
char
varchar
char和varcahr比较
日期和时间类型
enum和set

数据库操作

字符集与校验规则

当我们创建数据库没有指定字符集和校验规则时，系统使用默认字符集：utf8，校验规则 是：utf8_ general_ ci ，这个校验规则中的 ci就是Case insensitive意为不区分大小写

创建一个使用utf8 的字符集，并带校对规则为utf8_general_ci的数据库。

mysql
create database DBName charset=utf8 collate utf8_general_ci;

查看系统默认字符集、默认校验规则

mysql
show variables like 'character_set_database'; 
show variables like 'collation_database';

支持的字符集、支持的校验规则

mysql
show charset;
show collation;

创建数据库

mysql
mysql> create database myDB;
Query OK, 1 row affected (0.90 sec)

mysql> show create database myDB;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| myDB     | CREATE DATABASE `myDB` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create database `myDB2`;
Query OK, 1 row affected (0.00 sec)

mysql> show create database myDB2;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| myDB2    | CREATE DATABASE `myDB2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL 建议我们关键字使用大写，但是不是必须的。 数据库名字的反引号,是为了防止使用的数据库名刚好是关键字 /*!40100 DEFAULT CHARACTER SET utf8 */这个不是注释，表示当前Mysql版本大于4.01版本，就执行这句话

修改数据库

对数据库的修改主要指的是修改数据库的字符集，校验规则

例如把 mytest 数据库字符集改为 gbk

mysql
alter database mytest charset=gbk

删除数据库

不要随意删除数据库，否则还是容易从删库到跑路的

mysql
drop databse [if exists] 数据库名称

如果加上if exists那么删除一个不存在的数据库也不会出错，但是会有警告(查看警告就使用show warnings;)，如果不加if exists去删除一个不存在的数据库就会报错

mysql
mysql> drop database mydbs;
ERROR 1008 (HY000): Can't drop database 'mydbs'; database doesn't exist
mysql> drop database if exists mydbs;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                   |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'warning' at line 1 |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

备份和恢复

mysql
mysqldump -P3306 -uroot -p -B 数据库名称 > 路径+数据库名称.sql

使用示例

mysql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb1              |
| performance_schema |
| sakila             |
| test               |
| userdata           |
| world              |
+--------------------+
11 rows in set (0.00 sec)

mysql> exit;
Bye

C:\Users\15291\Desktop>mysqldump -P3306 -uroot -p -B mydb1 > ./mydb1.sql
Enter password: ****

备份后会生成mydb1.sql文件，通过执行这个sql脚本就会恢复数据库：

mysql> source C:/Users/15291/Desktop/mydb1.sql;

如果备份的不是整个数据库，而是其中的一张表:

mysqldump -u root -p 数据库名 表名1 表名2 > ./mytest.sql

同时备份多个数据库:

mysqldump -u root -p -B 数据库名1 数据库名2 ... > 数据库存放路路径

如果备份一个数据库时，没有带上-B参数， 在恢复数据库时，需要先创建空数据库，然后使用数据库，再使用source来还原

查看连接情况

可以告诉我们当前有哪些用户连接到我们的MySQL，如果查出某个用户不不是你正常登陆的，很有可能数据库被人入侵了。如果发现数据库比较慢时，可以用这个指令来查看数据库连接情况：

mysql
mysql> show processlist;
+----+------+-----------------+------+---------+------+-------+------------------+
| Id | User | Host            | db   | Command | Time | State | Info             |
+----+------+-----------------+------+---------+------+-------+------------------+
|  9 | root | localhost:55469 | NULL | Query   |    0 | init  | show processlist |
+----+------+-----------------+------+---------+------+-------+------------------+
1 row in set (0.04 sec)

表的操作

创建表

mysql
CREATE TABLE table_name (
	field1 datatype,
	field2 datatype,
	field3 datatype
) character set 字符集 collate 校验规则 engine 存储引擎;

不同的存储引擎，创建表的文件不一样，这个在Myisam存储引擎中说到过 假设users表存储引擎是Myisam，在数据目中有三个不同的文件，分别是： users.frm：表结构 users.MYD：表数据 users.MYI：表索引

查看表结构

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

修改表/表结构

mysql
ALTER TABLE tablename ADD (column datatype [DEFAULT expr][,column datatype]...);
ALTER TABLE tablename MODIfy (column datatype [DEFAULT expr][,column datatype]...);
ALTER TABLE tablename DROP (column);

使用示例：

在users表的password字段后添加assets字段，类型为varchar(50)，备注为图片路径

mysql
mysql> alter table users add assets varchar(50) comment '图片路径' after password;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

修改users表中name字段的类型为varchar(30)

mysql
mysql> alter table users modify name varchar(30);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc users;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| name     | varchar(30) | YES  |     | NULL    |       |
| password | char(32)    | YES  |     | NULL    |       |
| assets   | varchar(50) | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.02 sec)

删除users表中assets字段

mysql
mysql> alter table users drop assets;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

对user表的名字进行重命名

mysql
mysql> alter table users rename to user;
Query OK, 0 rows affected (0.01 sec)

对user表中的name字段进行重命名并更改字段类型

mysql
mysql> alter table user change name xingming varchar(50);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| xingming | varchar(50) | YES  |     | NULL    |       |
| password | char(32)    | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

删除user表

mysql
mysql> drop table user;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| person        |
+---------------+
1 row in set (0.00 sec)

Mysql数据类型

数值类型

tinyint-bigint

这是有符号的范围，无符号的返回自行推导，和C的无符号是一致的

mark

在MySQL中，整型可以指定是有符号的和无符号的，默认是有符号的。 可以通过UNSIGNED来说明某个字段是无符号的，但是一般遇到存储类型不足以存储数据的大小时，应该换成更大的类型，而不是换成对应的无符号类型！

bit

bit[(M)]: 位字段类型。M表示每个值的位数，范围从1到64。如果M被忽略，默认为1 注意bit类型，bit字段在显示时，是按照ASCII码对应的值显示！ 如果我们有这样的值:只存放0或1，这时可以定义bit(1)，这样可以节省空间！

float

float[(m, d)] [unsigned]: M指定显示长度，d指定小数位数，占用空间4个字节！

decimal

浮点数float与定点数decimal的存储方式的不同决定了他们的用途不同：《浮点数的存储方式》 decimal用于保存必须为确切精度的值，很显然底层是用字符串来存储的

mysql
decimal(m, d) [unsigned]

定点数m指定长度，d表示小数点的位数

float表示的精度大约是7位。decimal整数最大位数m为65。支持小数最大位数d是30。如果d被省略，默认为0.如果m被省略，默认是10。 建议：如果希望小数的精度高，推荐使用decimal(对于银行这种对小数要求非常高的业务，decimal还是大有用处的)

字符串类型

char
mysql
char(L)

固定长度字符串，L是可以存储的长度，单位为字符，最大长度值可以为255，这里需要注意的是一个汉字和一个字母均被视为一个字符！

varchar
mysql
varchar(L)

可变长度字符串，L表示字符长度，最大长度65535个字节 关于varchar(len)，len到底是多大，这个len值，和表的编码密切相关：varchar长度可以指定为0到65535之间的值，但是有两个字节用于记录数据大小，所以说有效字节数65532。 当我们的表的编码是utf8时，varchar(n)的参数n最大值是65532/3=21844[因为utf中，一个字符占用3个字节]，如果编码是gbk，varchar(n)的参数n最大是65532/2=32766（因为gbk中，一个字符占用2字节）

char和varcahr比较

mark

两者如何选择？ 如果数据确定长度都一样，就使用定长（char），比如：身份证，手机号，md5 如果数据长度有变化,就使用变长(varchar), 比如：名字，地址，但是你要保证最长的能存的进去 定长的磁盘空间比较浪费，但是效率高 变长的磁盘空间比较节省，但是效率

日期和时间类型

常用的日期有如下三个：

  • datetime 时间日期格式 ‘yyyy-mm-dd HH:ii
    ’ 表示范围从1000到9999，占用八字节
  • date:日期 ‘yyyy-mm-dd’，占用三字节
  • timestamp：时间戳，从1970年开始的 yyyy-mm-dd HH:ii
    格式和datetime完全一致，占用四字节

使用示例：

mysql
mysql> create table birthday (t1 date, t2 datetime, t3 timestamp);
Query OK, 0 rows affected (1.00 sec)

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| birthday      |
| person        |
+---------------+
2 rows in set (0.00 sec)

mysql> insert into birthday(t1,t2) values('1997-7-1','2008-8-8 12:1:1');
Query OK, 1 row affected (0.22 sec)

mysql> select * from birthday;
+------------+---------------------+---------------------+
| t1         | t2                  | t3                  |
+------------+---------------------+---------------------+
| 1997-07-01 | 2008-08-08 12:01:01 | 2018-12-07 18:57:58 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

enum和set

语法： enum：枚举，“单选”类型；

mysql
enum('选项1','选项2','选项3',...);

该设定只是提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值；而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,3,….最多65535个；当我们添加枚举值时，也可以添加对应的数字编号。

set：集合，“多选”类型；

mysql
set('选项值1','选项值2','选项值3', ...);

该设定只是提供了若干个选项的值，最终一个单元格中，设计可存储了其中任意多个值；而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,4,8,16,32，…. 最多64个。

不建议在添加枚举值，集合值的时候采用数字的方式，因为不利于阅读。 使用示例：

mysql
mysql> create table votes(
    -> username varchar(30),
    -> hobby set('登山','黎狗子吃粑粑','运动'),
    -> gender enum('男','女'));
Query OK, 0 rows affected (0.43 sec)

mysql> insert into votes values('LiLiNaNa','登山,黎狗子吃粑粑','男');
Query OK, 1 row affected (0.09 sec)

mysql> select * from votes;
+----------+-------------------+--------+
| username | hobby             | gender |
+----------+-------------------+--------+
| LiLiNaNa | 登山,黎狗子吃粑粑 | 男     |
+----------+-------------------+--------+
1 row in set (0.02 sec)

集合查询使用find_ in_ set函数：

mysql
find_in_set(sub,str_list)

如果sub 在str_list 中，则返回下标；如果不在，返回0； str_list 用逗号分隔的字符串。

mysql
mysql> insert into votes values('Juse','登山',2);
Query OK, 1 row affected (0.07 sec)

mysql> select * from votes where find_in_set('登山', hobby);
+----------+-------------------+--------+
| username | hobby             | gender |
+----------+-------------------+--------+
| LiLiNaNa | 登山,黎狗子吃粑粑 | 男     |
| Juse     | 登山              | 女     |
+----------+-------------------+--------+
2 rows in set (0.09 sec)

本文作者:Tim

本文链接:

版权声明:本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！