MySQL库表操作

字数统计: 2.8k字   |   阅读时长≈ 12分

数据库操作

字符集与校验规则

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

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

1
create database DBName charset=utf8 collate utf8_general_ci;

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

1
2
show variables like 'character_set_database'; 
show variables like 'collation_database';

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

1
2
show charset;
show collation;

创建数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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

1
alter database mytest charset=gbk

删除数据库

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

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

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

1
2
3
4
5
6
7
8
9
10
11
12
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)

备份和恢复

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

使用示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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脚本就会恢复数据库：

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

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

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

同时备份多个数据库:

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

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

查看连接情况

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

1
2
3
4
5
6
7
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)

表的操作

创建表

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

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

查看表结构

1
2
3
4
5
6
7
8
9
10
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)

修改表/表结构

1
2
3
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)，备注为图片路径

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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) 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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字段 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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表的名字进行重命名 

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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表

1
2
3
4
5
6
7
8
9
10
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用于保存必须为确切精度的值，很显然底层是用字符串来存储的

1
decimal(m, d) [unsigned]

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

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

字符串类型

char
1
char(L)

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

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

使用示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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：枚举，“单选”类型；

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

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

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

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

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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函数：

1
find_in_set(sub,str_list)

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

1
2
3
4
5
6
7
8
9
10
11
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)
赏

谢谢你请我喝咖啡

支付宝
微信

扫一扫，分享到微信

tag:

    缺失模块。
    1、请确保node版本大于6.2
    2、在博客根目录（注意不是yilia-plus根目录）执行以下命令：
    npm i hexo-generator-json-content --save

    3、在根目录_config.yml里添加配置： 

      jsonContent:
    meta: false
    pages: false
    posts:
      title: true
      date: true
      path: true
      text: false
      raw: false
      content: false
      slug: false
      updated: false
      comments: false
      link: false
      permalink: false
      excerpt: false
      categories: false
      tags: true