Tim

一枚野生程序员~

  • 主页
  • 分类
  • 标签
  • 归档
  • 关于
所有文章 工具

Tim

一枚野生程序员~

  • 主页
  • 分类
  • 标签
  • 归档
  • 关于

MySQL库表操作

阅读数:次 2018-06-08
字数统计: 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)
赏

谢谢你请我喝咖啡

支付宝
微信
  • 本文作者: Tim
  • 本文链接: https://zouchanglin.cn/498041550.html
  • 版权声明: 本博客所有文章除特别声明外,均采用 CC BY-SA 4.0 许可协议。转载请注明出处!
  • MySQL
  • 数据库

扫一扫,分享到微信

冯诺依曼架构
MySQL存储引擎
  1. 1. 数据库操作
    1. 1.1. 字符集与校验规则
    2. 1.2. 创建数据库
    3. 1.3. 修改数据库
    4. 1.4. 删除数据库
    5. 1.5. 备份和恢复
    6. 1.6. 查看连接情况
  2. 2. 表的操作
    1. 2.1. 创建表
    2. 2.2. 查看表结构
    3. 2.3. 修改表/表结构
    4. 2.4. Mysql数据类型
      1. 2.4.1. 数值类型
      2. 2.4.2. tinyint-bigint
      3. 2.4.3. bit
      4. 2.4.4. float
      5. 2.4.5. decimal
      6. 2.4.6. 字符串类型
        1. 2.4.6.1. char
        2. 2.4.6.2. varchar
        3. 2.4.6.3. char和varcahr比较
      7. 2.4.7. 日期和时间类型
      8. 2.4.8. enum和set
© 2017-2021 Tim
本站总访问量次 | 本站访客数人
  • 所有文章
  • 工具

tag:

  • 生活
  • Android
  • 索引
  • MySQL
  • 组件通信
  • Nginx
  • JavaSE
  • JUC
  • JavaWeb
  • 模板引擎
  • 前端
  • Linux
  • 计算机网络
  • Docker
  • C/C++
  • JVM
  • 上传下载
  • JavaEE
  • SpringCloud
  • Golang
  • Gradle
  • 网络安全
  • 非对称加密
  • IDEA
  • SpringBoot
  • Jenkins
  • 字符串
  • vim
  • 存储
  • 文件下载
  • Mac
  • Windows
  • NIO
  • RPC
  • 集群
  • 微服务
  • SSH
  • 配置中心
  • XML
  • Chrome
  • 压力测试
  • Git
  • 博客
  • 概率论
  • 排序算法
  • 分布式
  • 异常处理
  • 文件系统
  • 哈希
  • openCV
  • 栈
  • 回溯
  • SpringCore
  • 流媒体
  • rtmp
  • 面向对象
  • Vue
  • ElementUI
  • 软件工程
  • 异步
  • 自定义UI
  • ORM框架
  • 模块化
  • 交互式
  • Jsoup
  • Http Client
  • LRUCache
  • RabbitMQ
  • 消息通信
  • 服务解耦
  • 负载均衡
  • 权限
  • 多线程
  • 单例模式
  • Protobuf
  • 序列化
  • Python
  • m3u8
  • 堆
  • 二叉树
  • 自定义View
  • 观察者模式
  • 设计模式
  • 线程池
  • 动态扩容
  • 高可用
  • GC
  • ffmpeg
  • SpringMVC
  • REST
  • Redis
  • 缓存中间件
  • UML
  • Maven
  • Netty
  • 高性能网络
  • IPC通信
  • IO
  • Stream
  • 发布订阅
  • SQLite
  • Hash
  • 集合框架
  • 链表
  • Lambda
  • 汇编语言
  • 组件化
  • Router
  • 开发工具

    缺失模块。
    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
    

  • 思维导图
  • PDF工具
  • 无损放大
  • 代码转图
  • HTTPS证书