【MySQL笔记】库操作与表操作

🔥个人主页🔥：孤寂大仙V
🌈收录专栏🌈：MySQL
🌹往期回顾🌹：【MySQL】认识MySQL
🔖流水不争，争的是滔滔不

---

一、库操作

1.1 显示数据库

show databases;

show databases;” 是 MySQL 中的一条 SQL 语句，用于显示当前 MySQL 服务器上，当前用户权限范围内的所有可用数据库。

语法格式为SHOW DATABASES (LIKE ‘数据库名’);其中LIKE从句是可选项，用于匹配指定的数据库名称，支持部分匹配和完全匹配 。

---

1.2 创建数据库

语法

CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [,
create_specification] ...]
create_specification:
[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name

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

---

创建名为test1的数据库

create database test;

---

1.3 字符集和校验规则

创建数据库的时候，有两个编码集：
1.数据库编码集——数据库未来的存储数据
2.数据库效验集——支持数据库，进行字段比较使用的编码，本质也是一种读取数据库中数据的采用的编码格式。

数据库对数据进行任何操作，必须保证操作和编码是一致的。

创建指定字符集与指定校对规则的数据库
创建一个使用utf8字符集的数据库

create database db2 charset=utf8;

创建一个使用utf字符集并带校队规则的数据库

create database db3 charset=utf8 collate utf8_general_ci;

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

当我们创建数据库没有指定字符集和校验规则时，系统使用默认字符集：utf8，校验规则是：utf8_ general_ ci

查看数据库支持的字符集

show charset

字符集主要是控制用什么语言

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

show collation;

效验数据对数据库的影响

我们用能不能区分字母大小写的效验码举例

创建一个数据库，效验规则使用utf8_general_ci 不能区分大小写

create database test1 collate utf8_general_ci;

use test1;

create table person(name varchar(20));

mysql> insert into person (name) values ('a');

mysql> insert into person (name) values ('A');

mysql> insert into person (name) values ('b');

mysql> insert into person (name) values ('B');

mysql> insert into person (name) values ('c');

mysql> insert into person (name) values ('C');

select * from person;

---

创建一个数据库，效验规则使用utf8_bin 区分大小写

create database test2 collate utf8_bin;

create table person(name varchar(20));

mysql> insert into person (name) values ('a');

mysql> insert into person (name) values ('A');

mysql> insert into person (name) values ('b');

mysql> insert into person (name) values ('B');

mysql> insert into person (name) values ('c');

mysql> insert into person (name) values ('C');

select * from person;

---

校验集是会影响我们的数据库的结果的。

1.4 显示创建语句

show create database 数据库名；

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

1.5 修改数据库

语法

ALTER DATABASE db_name
[alter_spacification [,alter_spacification]...]
alter_spacification:
[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name

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

---

修改test1数据库字符集改为gbk

alter database test1 charset=gbk;

1.6 数据库删除

DROP DATABASE [IF EXISTS] db_ name;

执行删除之后的结果:

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

注意：不要随意删除数据库

---

删除test2数据库

drop database test2;

1.7 备份与还原

语法

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

将test1库备份到文件（退出连接）,在我们Linux想要下，你自己想要进行备份的路径下进行备份。

mysqldump -P3306 -uroot -p -B test1> test_25_3_11

发现备份文件，在数据中做的有效操作都备份起来了。

---

如果把数据库中的test1这一数据库删除，进行还原要进行一下操作

source后跟备份文件所在的路径

source /root/learn/test_25_3_11/test_25_3_11;

---

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

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

同时备份多个数据库

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

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

1.8 查看连接情况

就是看当前有哪些人用这个数据库

show processlist;

二、表操作

2.1 创建表

语法

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

• field 表示列名
• datatype 表示列的类型
• character set 字符集，如果没有指定字符集，则以所在数据库的字符集为准
• collate 校验规则，如果没有指定校验规则，则以所在数据库的校验规则为准

---

创建表之前要有数据库，表是存在数据库里的。

mysql> create table if not exists user1(
    -> id int,
    -> name varchar(20) comment '用户名',
    -> password char(32) comment '用户的密码',
    -> birthday date comment '用户的生日'
    -> )character set utf8 collate utf8_general_ci engine MyIsam;

mysql> create table if not exists user2(
    -> id int,
    -> name varchar(20) comment '用户名',
    -> password char(32) comment '用户的密码',
    -> birthday date comment '用户的生日'
    -> )charset=utf8 collate=utf8_general_ci engine=InnoDB;

---

不同的存储引擎，创建表的文件不一样
users 表存储引擎是 MyISAM ，在数据目中有三个不同的文件，分别是：

• users.frm：表结构
• users.MYD：表数据
• users.MYI：表索引

存储引擎是 InnoDB，有两个文件，分别是：

• user2.frm
• user2.ibd
  

2.2 查看当前数据库有哪些表

show tables；

2.2 查看表结构

desc 表名;

查看更详细信息

2.3 修改表

在项目实际开发中，经常修改某个表的结构，比如字段名字，字段大小，字段类型，表的字符集类型，表的存储引擎等等。我们还有需求，添加字段，删除字段等等。这时我们就需要修改表

删除表

语法

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...

把一个表删除

drop table user2;

修改表名字

alter table 原来的名字 rename to 新名字；

向表中插入内容

insert into user values(1,'张三'，'12345','2005-10-1');
insert into user values (2,'李四','54321','2004-10-1');

查看表的内容

select * from 表名称;

向表结构中新增行 表内容新增列

alter table 表名 add 新增的列的名称 大小 comment 描述 after birthday

alter table user add image_path varchar(128) comment '这个是用户的头像路径' after birthday;

新增后发现表结构中新增了一行，表内容中新增了一列。

修改表结构中信息的存储大小

alter table 表名称 modify name 新的字段

alter table user modify name varchar(60);

删除表结构中的行 表内容中的列

alter table 表名称 drop 想要删除的内容； 

alter table user drop password;