MySQL库的操作

文章目录

• 一、创建数据库
• 二、字符集和校验规则
• • 1.查看系统默认字符集以及校验规则
  • 2.查看数据库支持的字符集
  • 3.查看数据库支持的字符集校验规则
  • 4.校验规则对数据库的影响
• 三、操纵数据库
• • 1.查看数据库
  • 2 显示创建语句
  • 3.修改数据库
  • 4.删除数据库
• 四、备份和恢复
• • 1.备份
  • 2.还原
• 五、查看连接情况

---

一、创建数据库

CREATEDATABASE[IFNOTEXISTS]db_name[create_specification[,create_specification]...];create_specification:[DEFAULT]CHARACTERSETcharset_name[DEFAULT]COLLATEcollation_name

其中：

• 大写的表示关键字
• [] 是可选项
• CHARACTER SET: 指定数据库采用的字符集
• COLLATE: 指定数据库字符集的校验规则

样例：

• 创建名为db1的数据库

mysql>createdatabasedb1;Query OK,1rowaffected(0.00sec)

当我们创建数据库没有指定字符集和校验规则时，系统使用默认字符集：utf8mb3，校验规则是：utf8mb3_general_ci（当然，这具体取决于你的配置文件）

• 创建一个使用utf8mb3字符集的 db2 数据库

mysql>createdatabasedb2charset=utf8mb3;Query OK,1rowaffected,1warning(0.01sec)

• 创建一个使用utf8mb3字符集，并带校对规则的 db3 数据库。

mysql>createdatabasedb3charset=utf8mb3collateutf8mb3_general_ci;Query OK,1rowaffected,2warnings(0.01sec)

二、字符集和校验规则

1.查看系统默认字符集以及校验规则

mysql>showvariableslike'character_set_database';+------------------------+---------+|Variable_name|Value|+------------------------+---------+|character_set_database|utf8mb3|+------------------------+---------+1rowinset(0.00sec)mysql>showvariableslike'collation_database';+--------------------+--------------------+|Variable_name|Value|+--------------------+--------------------+|collation_database|utf8mb3_general_ci|+--------------------+--------------------+1rowinset(0.00sec)

2.查看数据库支持的字符集

mysql>showcharset;+----------+---------------------------------+---------------------+--------+|Charset|Description|Defaultcollation|Maxlen|+----------+---------------------------------+---------------------+--------+|armscii8|ARMSCII-8Armenian|armscii8_general_ci|1||ascii|US ASCII|ascii_general_ci|1||big5|Big5 Traditional Chinese|big5_chinese_ci|2||binary|Binarypseudocharset|binary|1||cp1250|Windows Central European|cp1250_general_ci|1||cp1251|Windows Cyrillic|cp1251_general_ci|1||cp1256|Windows Arabic|cp1256_general_ci|1||cp1257|Windows Baltic|cp1257_general_ci|1||cp850|DOS West European|cp850_general_ci|1||cp852|DOS Central European|cp852_general_ci|1||cp866|DOS Russian|cp866_general_ci|1||cp932|SJISforWindows Japanese|cp932_japanese_ci|2||dec8|DECWest European|dec8_swedish_ci|1||eucjpms|UJISforWindows Japanese|eucjpms_japanese_ci|3||euckr|EUC-KR Korean|euckr_korean_ci|................

这里只截取一部分

3.查看数据库支持的字符集校验规则

mysql>showcollation;+-----------------------------+----------+-----+---------+----------+---------+---------------+|Collation|Charset|Id|Default|Compiled|Sortlen|Pad_attribute|+-----------------------------+----------+-----+---------+----------+---------+---------------+|armscii8_bin|armscii8|64||Yes|1|PAD SPACE||armscii8_general_ci|armscii8|32|Yes|Yes|1|PAD SPACE||ascii_bin|ascii|65||Yes|1|PAD SPACE||ascii_general_ci|ascii|11|Yes|Yes|1|PAD SPACE||big5_bin|big5|84||Yes|1|PAD SPACE||big5_chinese_ci|big5|1|Yes|Yes|1|PAD SPACE||binary|binary|63|Yes|Yes|1|NOPAD||cp1250_bin|cp1250|66||Yes|1|PAD SPACE||cp1250_croatian_ci|cp1250|44||Yes|1|PAD SPACE||cp1250_czech_cs|cp1250|34||Yes|2|PAD SPACE||cp1250_general_ci|cp1250|26|Yes|Yes|1|PAD SPACE||cp1250_polish_ci|cp1250|99||Yes|1|PAD SPACE||cp1251_bin|cp1251|50||Yes|1|PAD SPACE||cp1251_bulgarian_ci|cp1251|14||Yes|1|PAD SPACE||cp1251_general_ci|cp1251|51|Yes|Yes|1|PAD SPACE||cp1251_general_cs|cp1251|52||Yes|1|PAD SPACE||cp1251_ukrainian_ci|cp1251|23||Yes|1|PAD SPACE||cp1256_bin|cp1256|67||Yes|................

这里只截取一部分

4.校验规则对数据库的影响

• 创建一个数据库，校验规则使用utf8mb3_ general_ ci(ci:case insensitive)
  [不区分大小写]

mysql>createdatabasetest1collateutf8mb3_general_ci;Query OK,1rowaffected,1warning(0.00sec)mysql>usetest1;Databasechanged mysql>createtableperson(namevarchar(20));Query OK,0rowsaffected(0.01sec)mysql>insertintopersonvalues('a');Query OK,1rowaffected(0.00sec)mysql>insertintopersonvalues('A');Query OK,1rowaffected(0.00sec)mysql>insertintopersonvalues('b');Query OK,1rowaffected(0.00sec)mysql>insertintopersonvalues('B');Query OK,1rowaffected(0.00sec)

• 创建一个数据库，校验规则使用utf8mb3_ bin(二进制序列)[区分大小写]

mysql>createdatabasetest2collateutf8mb3_bin;Query OK,1rowaffected,1warning(0.01sec)mysql>usetest2;Databasechanged mysql>createtableperson(namevarchar(20));Query OK,0rowsaffected(0.02sec)mysql>insertintopersonvalues('a');Query OK,1rowaffected(0.01sec)mysql>insertintopersonvalues('A');Query OK,1rowaffected(0.00sec)mysql>insertintopersonvalues('b');Query OK,1rowaffected(0.00sec)mysql>insertintopersonvalues('B');Query OK,1rowaffected(0.00sec)

• 进行查询

不区分大小写的查询：

mysql>usetest1;Databasechanged mysql>select*frompersonwherename='a';+------+|name|+------+|a||A|+------+2rowsinset(0.00sec)

区分大小写的查询：

mysql>usetest2;Databasechanged mysql>select*frompersonwherename='a';+------+|name|+------+|a|+------+1rowinset(0.00sec)

• 结果排序

不区分大小写的排序：

mysql>usetest1;Databasechanged mysql>select*frompersonorderbyname;+------+|name|+------+|a||A||b||B|+------+4rowsinset(0.00sec)

区分大小写的排序：

mysql>usetest2;Databasechanged mysql>select*frompersonorderbyname;+------+|name|+------+|A||B||a||b|+------+4rowsinset(0.00sec)

三、操纵数据库

1.查看数据库

mysql>showdatabases;+--------------------+|Database|+--------------------+|db1||db2||db3||information_schema||mysql||performance_schema||sys||test1||test2|+--------------------+9rowsinset(0.00sec)

2 显示创建语句

showcreatedatabase数据库名；

样例：

mysql>showcreatedatabasetest1;+----------+------------------------------------------------------------------------------------------------------+|Database|CreateDatabase|+----------+------------------------------------------------------------------------------------------------------+|test1|CREATEDATABASE`test1`/*!40100 DEFAULT CHARACTER SET utf8mb3 *//*!80016 DEFAULT ENCRYPTION='N' */|+----------+------------------------------------------------------------------------------------------------------+1rowinset(0.00sec)

注意：

• MySQL 建议我们关键字使用大写，但是不是必须的
• 数据库名字的反引号``,是为了防止使用的数据库名刚好是关键字
• /!40100 default…/ 这个不是注释，表示当前mysql版本大于4.01版本，就执行这句话，/!80016 …/同样如此

3.修改数据库

ALTERDATABASEdb_name[alter_spacification[,alter_spacification]...]alter_spacification:[DEFAULT]CHARACTERSETcharset_name[DEFAULT]COLLATEcollation_name

注意：

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

样例：

• 将 test1 数据库字符集改成 gbk

mysql>alterdatabasetest1charset=gbk;Query OK,1rowaffected(0.00sec)mysql>showcreatedatabasetest1;+----------+--------------------------------------------------------------------------------------------------+|Database|CreateDatabase|+----------+--------------------------------------------------------------------------------------------------+|test1|CREATEDATABASE`test1`/*!40100 DEFAULT CHARACTER SET gbk *//*!80016 DEFAULT ENCRYPTION='N' */|+----------+--------------------------------------------------------------------------------------------------+1rowinset(0.00sec)

4.删除数据库

DROPDATABASE[IFEXISTS]db_ name;

删除之后的结果：

• 数据库内部看不到对应的数据库
• 对应的数据库文件夹被删除，级联删除，里面的数据表全部被删

注意：

• 没有特殊需要，不要随意删除数据库

四、备份和恢复

1.备份

命令行操作：
将test1备份为test1.sql
-B表示 --databases，意思是备份指定的整个数据库（包括数据库中的所有表）以及数据库本身的信息（如创建数据库的语句）

mysqldump -P3306 -u root -p 密码 -B 数据库名>数据库备份存储的文件路径

打开备份的test1.sql文件：

所以：
备份时是将所有在test1数据库做的有效操作都给保存起来。备份的不是只有数据，而是将历史上所有的有效操作也保存起来了

• 备份数据库中的一张表

mysqldump -u root -p 数据库名 表名1 表名2>数据库备份存储的文件路径

• 备份多个数据库

mysqldump -u root -p -B 数据库名1 数据库名2...>数据库备份存储的文件路径

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

2.还原

source数据库备份存储的文件路径

将test1.sql里面的操作全都执行一次

五、查看连接情况

mysql>showprocesslist;+----+------+-----------+-------+---------+------+-------+------------------+|Id|User|Host|db|Command|Time|State|Info|+----+------+-----------+-------+---------+------+-------+------------------+|14|root|localhost|test1|Query|0|init|showprocesslist|+----+------+-----------+-------+---------+------+-------+------------------+1rowinset,1warning(0.00sec)

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