MySQL库表操作

发表于： 2018-06-08 分类于：计算机基础科学

字数： 8640 阅读：≈ 18分钟浏览：评论：

数据库操作

字符集与校验规则

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

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

1create database DBName charset=utf8 collate utf8_general_ci;

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

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

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

1show charset;
2show collation;

创建数据库

 1mysql> create database myDB;
 2Query OK, 1 row affected (0.90 sec)
 3
 4mysql> show create database myDB;
 5+----------+---------------------------------------------------------------+
 6| Database | Create Database                                               |
 7+----------+---------------------------------------------------------------+
 8| myDB     | CREATE DATABASE `myDB` /*!40100 DEFAULT CHARACTER SET utf8 */ |
 9+----------+---------------------------------------------------------------+
101 row in set (0.00 sec)
11
12mysql> create database `myDB2`;
13Query OK, 1 row affected (0.00 sec)
14
15mysql> show create database myDB2;
16+----------+----------------------------------------------------------------+
17| Database | Create Database                                                |
18+----------+----------------------------------------------------------------+
19| myDB2    | CREATE DATABASE `myDB2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
20+----------+----------------------------------------------------------------+
211 row in set (0.00 sec)

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

修改数据库

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

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

1alter database mytest charset=gbk

删除数据库

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

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

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

 1mysql> drop database mydbs;
 2ERROR 1008 (HY000): Can't drop database 'mydbs'; database doesn't exist
 3mysql> drop database if exists mydbs;
 4Query OK, 0 rows affected, 1 warning (0.00 sec)
 5
 6mysql> show warnings;
 7+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
 8| Level | Code | Message                                                                                                                                                   |
 9+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
10| 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 |
11+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
121 row in set (0.00 sec)

备份和恢复

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

使用示例

 1mysql> show databases;
 2+--------------------+
 3| Database           |
 4+--------------------+
 5| information_schema |
 6| mydb1              |
 7| performance_schema |
 8| sakila             |
 9| test               |
10| userdata           |
11| world              |
12+--------------------+
1311 rows in set (0.00 sec)
14
15mysql> exit;
16Bye
17
18C:\Users\15291\Desktop>mysqldump -P3306 -uroot -p -B mydb1 > ./mydb1.sql
19Enter password: ****

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

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

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

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

同时备份多个数据库:

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

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

查看连接情况

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

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

表的操作

创建表

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

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

查看表结构

 1mysql> desc users;
 2+----------+-------------+------+-----+---------+-------+
 3| Field    | Type        | Null | Key | Default | Extra |
 4+----------+-------------+------+-----+---------+-------+
 5| id       | int(11)     | YES  |     | NULL    |       |
 6| name     | varchar(20) | YES  |     | NULL    |       |
 7| password | char(32)    | YES  |     | NULL    |       |
 8| birthday | date        | YES  |     | NULL    |       |
 9+----------+-------------+------+-----+---------+-------+
104 rows in set (0.03 sec)

修改表/表结构

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

使用示例：

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

 1mysql> alter table users add assets varchar(50) comment '图片路径' after password;
 2Query OK, 0 rows affected (0.14 sec)
 3Records: 0  Duplicates: 0  Warnings: 0
 4
 5mysql> desc users;                                                            
 6+----------+-------------+------+-----+---------+-------+
 7| Field    | Type        | Null | Key | Default | Extra |
 8+----------+-------------+------+-----+---------+-------+
 9| id       | int(11)     | YES  |     | NULL    |       |
10| name     | varchar(20) | YES  |     | NULL    |       |
11| password | char(32)    | YES  |     | NULL    |       |
12| assets   | varchar(50) | YES  |     | NULL    |       |
13| birthday | date        | YES  |     | NULL    |       |
14+----------+-------------+------+-----+---------+-------+
155 rows in set (0.02 sec)

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

 1mysql> alter table users modify name varchar(30);
 2Query OK, 0 rows affected (0.09 sec)
 3Records: 0  Duplicates: 0  Warnings: 0
 4
 5mysql> desc users;
 6+----------+-------------+------+-----+---------+-------+
 7| Field    | Type        | Null | Key | Default | Extra |
 8+----------+-------------+------+-----+---------+-------+
 9| id       | int(11)     | YES  |     | NULL    |       |
10| name     | varchar(30) | YES  |     | NULL    |       |
11| password | char(32)    | YES  |     | NULL    |       |
12| assets   | varchar(50) | YES  |     | NULL    |       |
13| birthday | date        | YES  |     | NULL    |       |
14+----------+-------------+------+-----+---------+-------+
155 rows in set (0.02 sec)

删除users表中assets字段

 1mysql> alter table users drop assets;
 2Query OK, 0 rows affected (0.09 sec)
 3Records: 0  Duplicates: 0  Warnings: 0
 4
 5mysql> desc users;
 6+----------+-------------+------+-----+---------+-------+
 7| Field    | Type        | Null | Key | Default | Extra |
 8+----------+-------------+------+-----+---------+-------+
 9| id       | int(11)     | YES  |     | NULL    |       |
10| name     | varchar(30) | YES  |     | NULL    |       |
11| password | char(32)    | YES  |     | NULL    |       |
12| birthday | date        | YES  |     | NULL    |       |
13+----------+-------------+------+-----+---------+-------+
144 rows in set (0.01 sec)

对user表的名字进行重命名

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

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

 1mysql> alter table user change name xingming varchar(50);
 2Query OK, 0 rows affected (0.09 sec)
 3Records: 0  Duplicates: 0  Warnings: 0
 4
 5mysql> desc user;
 6+----------+-------------+------+-----+---------+-------+
 7| Field    | Type        | Null | Key | Default | Extra |
 8+----------+-------------+------+-----+---------+-------+
 9| id       | int(11)     | YES  |     | NULL    |       |
10| xingming | varchar(50) | YES  |     | NULL    |       |
11| password | char(32)    | YES  |     | NULL    |       |
12| birthday | date        | YES  |     | NULL    |       |
13+----------+-------------+------+-----+---------+-------+
144 rows in set (0.02 sec)

删除user表

 1mysql> drop table user;
 2Query OK, 0 rows affected (0.01 sec)
 3
 4mysql> show tables;
 5+---------------+
 6| Tables_in_db1 |
 7+---------------+
 8| person        |
 9+---------------+
101 row in set (0.00 sec)

Mysql数据类型

数值类型

tinyint-bigint

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

在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用于保存必须为确切精度的值，很显然底层是用字符串来存储的

1decimal(m, d) [unsigned]

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

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

字符串类型

char

1char(L)

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

varchar

1varchar(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比较

两者如何选择？如果数据确定长度都一样，就使用定长（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完全一致，占用四字节

使用示例：

 1mysql> create table birthday (t1 date, t2 datetime, t3 timestamp);
 2Query OK, 0 rows affected (1.00 sec)
 3
 4mysql> show tables;
 5+---------------+
 6| Tables_in_db1 |
 7+---------------+
 8| birthday      |
 9| person        |
10+---------------+
112 rows in set (0.00 sec)
12
13mysql> insert into birthday(t1,t2) values('1997-7-1','2008-8-8 12:1:1');
14Query OK, 1 row affected (0.22 sec)
15
16mysql> select * from birthday;
17+------------+---------------------+---------------------+
18| t1         | t2                  | t3                  |
19+------------+---------------------+---------------------+
20| 1997-07-01 | 2008-08-08 12:01:01 | 2018-12-07 18:57:58 |
21+------------+---------------------+---------------------+
221 row in set (0.00 sec)

enum和set

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

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

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

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

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

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

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

 1mysql> create table votes(
 2    -> username varchar(30),
 3    -> hobby set('登山','黎狗子吃粑粑','运动'),
 4    -> gender enum('男','女'));
 5Query OK, 0 rows affected (0.43 sec)
 6
 7mysql> insert into votes values('LiLiNaNa','登山,黎狗子吃粑粑','男');
 8Query OK, 1 row affected (0.09 sec)
 9
10mysql> select * from votes;
11+----------+-------------------+--------+
12| username | hobby             | gender |
13+----------+-------------------+--------+
14| LiLiNaNa | 登山,黎狗子吃粑粑 | 男     |
15+----------+-------------------+--------+
161 row in set (0.02 sec)

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

1find_in_set(sub,str_list)

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

 1mysql> insert into votes values('Juse','登山',2);
 2Query OK, 1 row affected (0.07 sec)
 3
 4mysql> select * from votes where find_in_set('登山', hobby);
 5+----------+-------------------+--------+
 6| username | hobby             | gender |
 7+----------+-------------------+--------+
 8| LiLiNaNa | 登山,黎狗子吃粑粑 | 男     |
 9| Juse     | 登山              | 女     |
10+----------+-------------------+--------+
112 rows in set (0.09 sec)