目录

一、数据库概述

1. SQL分类

2. 存储引擎

二、库的操作

1. 创建数据库

2. 字符集与校验规则

3. 查看数据库

4. 修改数据库

5. 删除数据库

6. 查看连接情况

三、表的操作

1. 创建数据表

2. 查看数据表

3. 修改数据表

4. 删除数据表

---

一、数据库概述

1. SQL分类

• DDL【Data Definition Language】数据定义语言，用来维护存储数据的结构，                  如：create、drop、alter
• DML【Data Manipulation Language】数据操纵语言，用来对数据进行操作，                          如：insert、delete、update、select（由DML有细分为DQL负责查询）
• DCL【Data Control Language】数据控制语言，负责权限管理和事务，                                  如：grant、revoke、commit

2. 存储引擎

存储引擎定义：数据库管理系统如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。

MySQL的核心就是插件式存储引擎，支持多种存储引擎，InnoDB是默认存储引擎。

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

二、库的操作

1. 创建数据库

-- 创建数据库
CREATE DATABASE [IF NOT EXISTS] db_name [create_specifiction ...]

create_specifiction:
    [DEFAULT] CHARACTER SET charset_name
    [DEFAULT] COLLATE collation_name

-- []是可选项
-- 大小表示关键字
-- CHARATER SET 指定数据库采样的字符集
-- COLLATE 指定数据库字符集的校验规则

2. 字符集与校验规则

-- 查看当前数据库默认的字符集与校验规则
show variables like 'character_set_database';
show variables like 'collation_database';

-- 查看MySQL支持的字符集与校验规则
show charset;
show collation;

-- 查看当前数据库的字符集与校验规则
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME 
FROM information_schema.SCHEMATA 
WHERE SCHEMA_NAME = 'db_name';

# 我们也可以在数据库的配置文件里查看字符集和校验规则
root@ubuntu:/var/lib/mysql/d1_mytest# cat db.opt 
default-character-set=utf8
default-collation=utf8_unicode_ci

-- 字符集指定了用什么编码方式来存储数据，负责数据的写入
-- 校验规则指定了怎么比较和读取数据，负责数据的读取

-- 有些字符集支持中文编码，有些不支持，utf8 和 gbk 是支持中文的
-- 在 utf8 字符集中，一个中文字符占3字节，但是从数据库的角度来看也只是一个字符

-- 同一个字符集的不同校验规则有可能有不同的排序和查询规则
-- 以 utf8_general_ci 和 utf8_bin 为例，
-- utf8_general_ci 是默认的校验规则，排序和查询不区分大小写
-- utf8_bin 排序和查询会区分大小写

3. 查看数据库

-- 查看数据库
show databases;

-- 显示数据库创建语句
show create database d1_mytest;
show create database d1_mytest\G

-- MySQL 建议我们关键字使用大写，但是不是必须的，小写更直观
-- 数据库名称可用 `` 反引号括住，是为了防止使用的数据库名刚好是关键字

4. 修改数据库

-- 修改数据库
ALTER DATABASE d1_mytest [alter_specification ...]

-- 对数据库的修改主要是指对数据库的字符集、校验规则进行修改
-- 即 alter_specification 是重置字符集和校验规则

mysql> alter database d1_mytest charset=gbk;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME 
    -> FROM information_schema.SCHEMATA 
    -> WHERE SCHEMA_NAME = 'd1_mytest';
+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| gbk                        | gbk_chinese_ci         |
+----------------------------+------------------------+
1 row in set (0.00 sec)

5. 删除数据库

-- 删除数据库
DROP DATABASE [IF EXISTS] db_name;

-- 执行删除操作之后：
-- 数据库内部看不到对应的数据库
-- 对应的数据库文件夹被删除，联级删除，里面的数据表全部删除

-- 不要轻易删除数据库！！！
-- 即便是要删除，也一定要做好备份

-- 数据库备份，备份的本质是将构造数据库的 SQL 语句装载进入备份文件
# mysqldump -P3306 -u root -p 密码 -B 数据库名 > 数据库备份的文件路径
-- 在数据库名后面跟多个名字，可以同时备份多个数据库
-- 如果在备份数据库的时候没有加上 -B 选项，那么在恢复数据库时不会自动创建数据库

-- 数据库恢复
mysql> source 数据库备份的文件路径;

-- 如果不备份整个数据库，而是备份其中的一张表，怎么做？
# mysqldump -u root -p 密码 数据库名 表名1 表名2 > 数据表备份的文件路径

6. 查看连接情况

-- 查看连接情况
show processlist;
-- 一个数据库可用由多个用户连接
-- 但是在查看连接情况的时候，只能看到自己权限内的用户连接
-- root 用户可以查看到全部用户的连接

当数据库访问缓慢时，可以查看连接情况进行检查是否是因为数据库遭受非法入侵。

三、表的操作

1. 创建数据表

-- 创建表
CREATE TABLE [IF NOT EXISTS] table_name (
    field1 datatype1,
    field2 datatype2,
    filed3 datatype3
)[engine=InnoDB charset=utf8 collate utf8_general_ci];
-- 在创建表的时候，可以对表的字段进行一些约束，也可以省略
-- 若不指定表的引擎、字符集和校验规则，表会继承数据库的配置

-- 创建表测试
CREATE TABLE IF NOT EXISTS test01 (
    id int,
    name varchar(32),
    age int
)engine=InnoDB charset=utf8 collate utf8_general_ci;

CREATE TABLE IF NOT EXISTS test02 (
    id int,
    name varchar(32),
    age int
)engine=MyISAM charset=gbk;
-- 不同的存储引擎，在数据库目录中的文件个数不同，原因是不同引擎存储的底层数据结构不同

root@ubuntu:/var/lib/mysql/d1_mytest# ll
total 136
drwxr-x--- 2 mysql mysql  4096 Mar 19 23:47 ./
drwx------ 6 mysql mysql  4096 Mar 19 20:58 ../
-rw-r----- 1 mysql mysql    59 Mar 19 23:21 db.opt
-rw-r----- 1 mysql mysql  8614 Mar 19 23:40 test01.frm
-rw-r----- 1 mysql mysql 98304 Mar 19 23:40 test01.ibd
-rw-r----- 1 mysql mysql  8614 Mar 19 23:47 test02.frm
-rw-r----- 1 mysql mysql     0 Mar 19 23:47 test02.MYD
-rw-r----- 1 mysql mysql  1024 Mar 19 23:47 test02.MYI
root@ubuntu:/var/lib/mysql/d1_mytest# 

# 在文件系统中，MyISAM 和 InnoDB 都有 .frm 文件，这个文件存储了表结构
# 在 InnoDB 中，表索引和表数据都存储在 .idb 文件中
# 在 MyISAM 中，表索引和表数据分别存储在 .MYI 和 .MYD 中

2. 查看数据表

-- 查看表
show tables;

-- 查看表结构
desc 表名;

mysql> desc test01;
+--------+--------------+--------+-------+---------+-------+
| Field  | Type         | Null   | Key   | Default | Extra |
+--------+--------------+--------+-------+---------+-------+
| id     | int(11)      | YES    |       | NULL    |       |
| name   | varchar(32)  | YES    |       | NULL    |       |
| age    | int(11)      | YES    |       | NULL    |       |
+--------+--------------+--------+-------+---------+-------+
3 rows in set (0.00 sec)

-- 字段名   字段类型   是否允许为空  索引类型   默认值    扩充

3. 修改数据表

在实际项目中，有可能我们会需要修改表的结构，如字段名称、字段大小、字段类型，或者修改表的配置，如表的字符集、存储引擎等等，或者是增添、删除字段。

但是修改表带来的风险是和删除表相当的，所以在进行修改的时候，特别是涉及到字段的修改，一定要慎重，为了避免修改表带来的风险，最好的方式就是在定义表的时候就决定好表的结构。

-- 修改表名
ALTER TABLE table_name RENAME TO new_name;
-- 修改表名之后，其数据库中对应的文件名也会修改

-- 修改列名
ALTER TABLE table_name CHANGE old_field new_field datatype [DEFAULT expr] ...;
-- 修改列名的时候，新列名需要完整定义，可以重新定义的时候修改列属性

-- 添加字段
ALTER TABLE table_name ADD (column datatype [DEFAULT expr], ...);

-- 修改字段
ALTER TABLE table_name MODIFY (column datatype [DEFAULT expr], ...);

-- 删除字段
ALTER TABLW table_name DROP (column);

mysql> desc test01;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table test01 add class int comment '班级' after id;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test01 modify name char(16);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test01;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| class | int(11)  | YES  |     | NULL    |       |
| name  | char(16) | YES  |     | NULL    |       |
| age   | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

4. 删除数据表

-- 删除表
DROP [TEMPORARY] TABLE table_name [IF EXISTS] table_name [, table_name] ...;

-- 示例
drop table test_table;