MySQL数据库基本操作命令
数据库基本操作
数据库可以看作是一个专门存储数据对象的容器，每一个数据库都有唯一的名称，并且数据库名称都是有实际意义的，这样就可以清晰的看出每一个数据库用来存放什么数据。在MySQL数据库中存在系统数据库和自定义数据库，系统数据库是安装在MySQL后系统自带的数据库，自定义数据库是由用户定义创建的数据库。
1、查看数据库
在MySQL中，可使用SHOW DATABASES语句来查看或者显示当前用户权限范围内的数据库。
SHOW DATABASES [LIKE'数据库名'];
LIKE从句是可选项，用于匹配自定的数据库库名称。LIKE从句可以部分匹配也可以完全匹配。
数据库名由单引号’'包围
案例1：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| grade |
| information_schema |
| mysql |
| newschema |
| performance_schema |
| sakila |
| student |
| sys |
| test_db |
| work |
| world |
+--------------------+
11 rows in set (0.00 sec)
可以发现，在上面的列表中有 6 个数据库，它们都是安装 MySQL 时系统自动创建的，其各自功能如下：
information_schema：主要存储了系统中的一些数据库对象信息，比如用户表信息、列信息、权限信息、字符集信息和分区信息等。
mysql：MySQL 的核心数据库，类似于 SQL Server 中的 master 表，主要负责存储数据库用户、用户访问权限等 MySQL 自己需要使用的控制和管理信息。常用的比如在 mysql 数据库的 user 表中修改 root 用户密码。
performance_schema：主要用于收集数据库服务器性能参数。
sakila：MySQL 提供的样例数据库，该数据库共有 16 张表，这些数据表都是比较常见的，在设计数据库时，可以参照这些样例数据表来快速完成所需的数据表。
sys：sys 数据库主要提供了一些视图，数据都来自于performation_schema，主要是让开发者和使用者更方便地查看性能问题。
world：world 数据库是 MySQL 自动创建的数据库，该数据库中只包括 3 张数据表，分别保存城市，国家和国家使用的语言等内容
案例2：
mysql> show databases like'sakila';
+-------------------+
| Database (sakila) |
+-------------------+
| sakila |
+-------------------+
1 row in set (0.00 sec)
## 2、创建数据库
在MySQL中可以使用CREATE DATABASE语句创建数据库：
CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>]
[[DEFAULT] COLLATE <校对规则名>];
<数据库名>：创建数据库的名称。MySQL 的数据存储区将以目录方式表示 MySQL 数据库，因此数据库名称必须符合操作系统的文件夹命名规则，不能以数字开头，尽量要有实际意义。注意在MySQL 中不区分大小写。
IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。此选项可以用来避免数据库已经存在而重复创建的错误。
[DEFAULT] CHARACTER SET：指定数据库的字符集。指定字符集的目的是为了避免在数据库中存储的数据出现乱码的情况。如果在创建数据库时不指定字符集，那么就使用系统的默认字符集。
[DEFAULT] COLLATE：指定字符集的默认校对规则。
MySQL 的字符集（CHARACTER）和校对规则（COLLATION）是两个不同的概念。字符集是指一系列符号以及符号对应的编码的集合，比如英文字母可以使用 ASCII 编码，中文可以使用 GBK 或者 UTF-8 编码，校对规则则是指一种比较字符的规则，在排序或者比较字符的时候使用。
每个字符集都会对应多个校对规则，是一对多的关系，而且每个字符集会有默认的校对规则，当我们在创建数据库或者表时，如果只指定字符集，则使用字符集对应的默认校对规则。
cs 大小写敏感
ci 大小写不敏感
bin 二进制编码比较
#查看支持的字符集
mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| binary | Binary pseudo charset | binary | 1 |
character 字符集名称
description 字符集描述
default collation 默认校对规则
maxlen 最大长度
#查看支持的校对规则
mysql> show collation;
+-----------------------------+----------+-----+---------+----------+---------+---------------+
| 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 |
collation 校对规则名称
id 校对规则编号
default 是否为默认校对规则
compiled 是否系统自带
sortlen 与字符集中表示字符串进行排序所需的内存有关
pad_attribute 末尾空格是否参与比较
#查看默认字符集和校对规则
mysql> show create database test_db;
+----------+-----------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database
|
+----------+-----------------------------------------------------------------------------------------------------------------------------------+
| test_db | CREATE DATABASE `test_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
utf8和utf8mb4的区别：
MySQL在5.5.3之后增加了这个utf8mb4的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集，除了将编码改为utf8mb4外不需要做其他转换。当然，为了节省空间，一般情况下使用utf8也就够了。
既然utf8能够存下大部分中文汉字，那为什么还要使用utf8mb4呢？ 原来mysql支持的 utf8 编码最大字符长度为 3 字节，如果遇到 4 字节的宽字符就会插入异常了。三个字节的 UTF-8 最大能编码的 Unicode字符是 0xffff，也就是 Unicode 中的基本多文种平面(BMP)。也就是说，任何不在基本多文本平面的
Unicode字符，都无法使用 Mysql 的 utf8 字符集存储。包括 Emoji 表情(Emoji 是一种特殊的 Unicode编码，常见于 ios 和 android 手机上)，和很多不常用的汉字，以及任何新增的 Unicode 字符，如表情等等(utf8的缺点)。
案例1：
##创建test数据库
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
##查看创建的test数据库
mysql> show databases like 'test' ;
+-----------------+
| Database (test) |
+-----------------+
| test |
+-----------------+
1 row in set (0.00 sec)
案例2：
#创建test01、test02数据库
mysql> create database test01;
Query OK, 1 row affected (0.01 sec)
mysql> create database test02;
Query OK, 1 row affected (0.01 sec)
#通过通配符查看
mysql> show databases like'test%';
+------------------+
| Database (test%) |
+------------------+
| test |
| test01 |
| test02 |
| test_db |
+------------------+
4 rows in set (0.00 sec)
#通过_通配符查看
mysql> show databases like'test_%';
+-------------------+
| Database (test_%) |
+-------------------+
| test01 |
| test02 |
| test_db |
+-------------------+
3 rows in set (0.00 sec)
案例3：
##如果目标数据库testdb不存在责创建
mysql> create database if not exists testdb;
Query OK, 1 row affected (0.01 sec)
##查看创建的数据库
mysql> show databases like 'testdb';
+-------------------+
| Database (testdb) |
+-------------------+
| testdb |
+-------------------+
1 row in set (0.00 sec)
案例4：
mysql> create database if not exists testdb_char character set gb2312 collate gb2312_chinese_ci;
Query OK, 1 row affected (0.01 sec)
mysql> show create database testdb_char;
+-------------+-----------------------------------------------------------------------------------------------------------+
| Database | Create Database
|
+-------------+-----------------------------------------------------------------------------------------------------------+
| testdb_char | CREATE DATABASE `testdb_char` /*!40100 DEFAULT CHARACTER SET gb2312 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+-------------+-----------------------------------------------------------------------------------------------------------+
3、修改数据库
在MySQL数据库中只能对数据库使用的字符集和校对规则进行修改。
在MySQL中，使用ALTER DATABASE来修改已经被创建或者存在的数据库相关的参数。
ALTER DATABASE [数据库名] {
[ DEFAULT ] CHARACTER SET <字符集名> |
[ DEFAULT ] COLLATE <校对规则名>}
ALTER DATABASE 用于更改数据库的全局特性。
使用 ALTER DATABASE 需要获得数据库 ALTER 权限。
数据库名称可以忽略，此时语句对应于默认数据库。
CHARACTER SET 子句用于更改默认的数据库字符集。
案例1：
mysql> ALTER DATABASE testdb_char
-> DEFAULT CHARACTER SET utf8mb4
-> COLLATE utf8mb4_unicode_ci;
Query OK, 1 row affected (0.01 sec)
mysql> show create database testdb_char;
+-------------+---------------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database
|
+-------------+---------------------------------------------------------------------------------------------------------------------------------------+
| testdb_char | CREATE DATABASE `testdb_char` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
4、删除数据库
当数据库不在使用时应该将其删除，以确保数据库存储空间中存放的是有效数据，删除数据库是将已经存在的数据库从磁盘空间上清楚，清楚之后，数据库中的所有数据将一同被删除。
在MySQL中，使用DROP DATABASE。
DROP DATABASE [IF EXISTS]<数据库名>
注意：MySQL 安装后，系统会自动创建名为 information_schema 和 mysql 的两个系统数据库，系统
数据库存放一些和数据库相关的信息，如果删除了这两个数据库，MySQL 将不能正常工作。
案例：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| grade |
| information_schema |
| mysql |
| newschema |
| performance_schema |
| sakila |
| student |
| sys |
| test |
| test01 |
| test02 |
| test_db |
| testdb |
| testdb_char |
| testsb |
| work |
| world |
+--------------------+
17 rows in set (0.00 sec)
mysql> drop database testdb_char;
Query OK, 0 rows affected (0.02 sec)
mysql> drop database testdb_char;
ERROR 1008 (HY000): Can't drop database 'testdb_char'; database doesn't exist
5、选择数据库
在MySQL中就有很多是系统自带的数据库，在操作之前就必须要选择一个数据库。
在MySQL中，使用USE语句来完成一个数据库到另一个数据库的跳转。
当时用CREATE DATABASE 语句创建数据库后，该数据库不会自动成为当前数据库，需要使用use语句来指定当前数据库。
USE <数据库名>
案例：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| grade |
| information_schema |
| mysql |
| newschema |
| performance_schema |
| sakila |
| student |
| sys |
| test |
| test01 |
| test02 |
| test_db |
| testdb |
| testdb_char |
| testsb |
| work |
| world |
+--------------------+
17 rows in set (0.00 sec)
mysql> select database();
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)
mysql> select database();
+------------+
| database() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)
6、数据库引擎
MySQL数据库引擎是数据库管理系统中用于存储，处理和保护数据的核心服务。不同的数据库引擎具有各自的特点，如存储机制，索引技巧，锁的粒度等。选择合适的数据库引擎可以显著改善服务器端的存储性能。
存储引擎就是如何存储数据，如何为存储的数据建立索引和如何更新，查询数据等技术的实现方法。因为在关系数据库中是以表的形式存储的，所以存储引擎也可以称为表类型(即存储和操作此表的类型)。
在oracle和sql server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的，而MySQL数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己需要编写自己的存储引擎。
6.1常见的数据库引擎
InnoDB：这是MySQL的默认存储引擎，支持ACID事务、行级锁定和外键。InnoDB是为处理大容量数据而设计的，具有高性能、高并发和崩溃恢复能力。它适用于需要频繁更新和高事务完整性的应用场景
MyISAM：MyISAM引擎提供高速存取的表，但不支持事务处理或行级锁定。它适合于插入和查询
为主的应用，如Web和数据仓库环境。MyISAM表存储在磁盘上，每个表由三个文件组成：格式文件（.frm）、数据文件（.MYD）和索引文件（.MYI）
MEMORY：MEMORY引擎使用内存中的数据创建表，提供快速的数据访问。这种引擎适用于临时存储和需要快速读取的小型表。但是，MEMORY表中的数据在MySQL服务重启后会丢失
6.2查看和设置存储引擎
查看MySQL支持的存储引擎，可以使用SHOW ENGINES命令
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| ndbinfo | NO | MySQL Cluster system information storage engine | NULL | NULL | NULL |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| ndbcluster | NO | Clustered, fault-tolerant tables | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.01 sec)
查看默认的存储引擎，可以使用SHOW VARIABLES LIKE 'default_storage_engine’命令
mysql> show variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set, 1 warning (0.01 sec)
创建表时，可以通过engine关键字来指定存储引擎：
CREATE TABLE mytable (...) ENGINE=InnoDB;
修改表的引擎：
ALTER TABLE mytable ENGINE=新引擎;
6.3存储引擎的选择
选择合适的存储引擎对于数据库的性能和功能至关重要。例如，如果应用程序需要事务支持和高并发，innoDB是一个很好的选择。如果应用程序主要进行读操作，MyISAM可能会提供好的性能。对于临时数据和快速访问，MEMORY引擎可能是最佳选择。在选择存储引擎时，需要考虑数据安全性，并发性，事务处理和性能需求。
案例：
##查看当前数据库的表
mysql> show tables;
Empty set (0.00 sec)
##指定存储引擎创建表
mysql> create table test_table(id int primary key auto_increment,name varchar(128) not null )engine=MyISAM;
Query OK, 0 rows affected (0.01 sec)
##查看创建的表
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test_table |
+----------------+
1 row in set (0.00 sec)
##查看表的详细信息
mysql> show create table test_table;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_table | CREATE TABLE `test_table` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(128) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
##修改表的存储引擎
mysql> alter table test_table engine=innodb;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
##查看修改后的存储引擎
mysql> show create table test_table;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_table | CREATE TABLE `test_table` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(128) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)