【数据库】MySQL库与表的基本操作
目录
一、数据库概述
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;