【MySQL】MySQL基础

迟钝南瓜

已于 2024-09-22 17:21:50 修改

阅读量1.4k

点赞数 30

分类专栏： MySQL 文章标签： mysql 数据库

于 2024-09-22 17:17:52 首次发布

本文链接：https://blog.csdn.net/weixin_73223794/article/details/142439744

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

安装MySQL

http://repo.mysql.com/

下载想要版本的MySQL。ps.最好安装和⾃⼰系统⼀致的mysql版本，否则可能会存在软件兼容性问题

安装yum源，保证MySQL相关的yum源存在。

rpm -vih mysql57-community-release-el7-9.noarch.rpm
ls /etc/yum.repos.d/ -al

查看是否能正常工作。

yum list |grep mysql

安装MySQL服务。

sudo yum install -y mysql-community-server

ps.安装时若失败，遇到GPG Keys…，密钥过期问题则更新密钥。

rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022

查看配置文件和数据存储位置是否存在。

ls /etc/my.cnf  -- 配置文件的位置
sudo ls /var/lib/mysql  -- 数据库文件存储的位置
-- 这两个路径需要记住

查看服务是否启动。

ps axj |grep mysqld

设置开机自启动（设不设都行）

systemctl enable mysqld 
systemctl daemon-reload

配置root免密码登录

vim /etc/my.cnf
加入以下配置
skip-grant-tables   
port=3306
character-set-server=utf8
default-storage-engine=innodb

重启MySQL服务

systemctl restart mysqld

尝试登陆

mysql -u root -p

数据库概念

数据库本质就是存储文件的一套系统服务，和我们日常使用的文件系统类似，都是保存数据的一种方式。既然已经有文件系统了，为什么还要研究一套数据库出来，当然是因为文件系统有一些缺点，所以才需要改进。文件系统的缺点：

文件的安全性问题
文件不利于数据查询和管理
文件不利于存储海量数据
文件在程序中控制不方便

为了解决上述问题，专家们设计出更加利于管理数据的东西——数据库，它能更有效的管理数据。

总结：数据库的本质就是CS模式的一套网络服务，用于管理和存储数据。ps.服务端：mysqld，客户端：mysql。

主流数据库

SQL Sever：微软的产品，.Net程序员的最爱，中大型项目。
Oracle：甲骨文产品，适合大型项目，复杂的业务逻辑，并发一般来说不如MySQL。
MySQL：世界上最受欢迎的数据库，属于甲骨文，并发性好，不适合做复杂的业务。主要用在电商，SNS，论坛。对简单的SQL处理效果好。
PostgreSQL :加州大学伯克利分校计算机系开发的关系型数据库，不管是私用，商用，还是学术研究使用，可以免费使用，修改和分发。
SQLite：是一款轻型的数据库，是遵守ACID的关系型数据库管理系统，它包含在一个相对小的C库中。它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。
H2：是一个用Java开发的嵌入式数据库，它本身只是一个类库，可以直接嵌入到应用项目中。

连接服务器

mysql -h 127.0.0.1 -P 3306 -u root -p

如果没有写 -h 127.0.0.1 默认是连接本地
如果没有写 -P 3306 默认是连接3306端口号

服务器，数据库，表关系

所谓安装数据库服务器，只是在机器上安装了一个数据库管理系统程序，这个管理程序可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。为保存应用中实体的数据，一般会在数据库中创建多个表，以保存程序中实体的数据。数据库服务器、数据库和表的关系如下：

MySQL架构

最上层是链接池，既然是服务器自然要将许多连接请求管理好。

第二层是SQL语法分析，语义分析等对用户命令进行解析。

第三层是存储引擎，将数据进行存储，不同的存储引擎有不同的特点，在特定场景下可以合理选择。ps.这种方式形象的说法叫做支持热拔插。

最下层则是真实存储的数据了。

存储引擎

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

InnoDB是事务型数据库的首选引擎，支持事务、行级锁定和外键等高级数据库功能。

MyISAM，对大文件读写支持较好，并发处理能力高。

Memory存储引擎将所有数据存储在内存中，因此具有极高的插入、更新和查询效率。然而，由于数据存储在内存中，Memory引擎在MySQL重启时会丢失所有数据，且其性能受内存容量的限制。

存储引擎的一篇网上博客：https://www.ctyun.cn/developer/article/590163226259525

查看存储引擎：

show engines;

SQL分类

DDL【data definition language】数据定义语言，用来维护存储数据的结构代表指令: create, drop, alter
DML【data manipulation language】数据操纵语言，用来对数据进行操作代表指令： insert，delete，updateDML中又单独分了一个DQL，数据查询语言，代表指令： select
DCL【Data Control Language】数据控制语言，主要负责权限管理和事务代表指令： grant，revoke，commit

库的操作

创建数据库

语法：

CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [,create_specification] ...]

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

ps：

CHARACTER SET: 指定数据库采用的字符集
COLLATE: 指定数据库字符集的校验规则

案例：

create database database1; -- 创建名为database1的数据库
create database database2 charset=utf8; -- 创建名位database2，使用字符集为utf8的数据库
create database database2 character set utf8; -- 和上个效果相同，两种写法
create database database3 charset=utf8 collate utf8_general_ci; -- 使用utf字符集，并带校对规则的 db3 数据库

字符集和校验规则

查看系统默认字符集和校验规则：

show variables like 'character_set_database';
show variables like 'collation_database';

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

show charset; -- 查看支持的字符集
show collation; -- 查看支持的校验集

校验规则对数据库的影响

查看两个数据库所使用的字符集和校验集。

已知：utf8_general_ci是不区分大小的，utf8_bin是区分大小写的。

[root@hwcloud-9-7centos mysql]# cat test1/db.opt 
default-character-set=utf8
default-collation=utf8_general_ci
[root@hwcloud-9-7centos mysql]# cat test2/db.opt 
default-character-set=utf8
default-collation=utf8_bin

查看以下查询结果。

mysql> use test1;
Database changed
mysql> select * from person order by name;
+------+
| name |
+------+
| a    |
| A    |
| b    |
| B    |
| c    |
| D    |
+------+
6 rows in set (0.00 sec)

mysql> use test2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from person order by name;
+------+
| name |
+------+
| A    |
| B    |
| C    |
| a    |
| b    |
| c    |
+------+
6 rows in set (0.00 sec)

操纵数据库

查看数据库

show databases;  -- 查看数据库
use 数据库名; -- 使用该数据库
select database(); -- 查看当前在使用哪个数据库

显示创建语句

show create database 数据库名；-- 显示创建语句
-- 示例:
mysql> show create database test1;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| test1    | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

ps.

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

修改数据库

语法：

ALTER DATABASE db_name[alter_spacification [,alter_spacification]...]

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

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

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

mysql> show create database test3;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| test3    | CREATE DATABASE `test3` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

数据库删除

DROP DATABASE [IF EXISTS] db_ name;

建议不要随便删除数据库。

备份和恢复

备份：

mysqldump -P3306 -u root -p 密码 -B 数据库名 > 数据库备份存储的文件路径   -- 备份一个数据库
mysqldump -P3306 -u root -p 密码 数据库名 表1 表2 > 数据库备份存储的文件路径  -- 备份数据库的多张表
mysqldump -P3306 -u root -p 密码 -B 数据库名1 数据库名2 > 数据库备份存储的文件路径  -- 备份多个数据库

ps.-B选项是创建表的语句，如果没有则需要手动创建数据库，去source表即可。

还原：

mysql> source 备份文件路径

查看连接情况

查看有哪些人在连接我的数据库。

show processlist;

mysql> show processlist;
+----+------+-----------+--------+---------+------+----------+------------------+
| Id | User | Host      | db     | Command | Time | State    | Info             |
+----+------+-----------+--------+---------+------+----------+------------------+
|  7 | root | localhost | mytest | Query   |    0 | starting | show processlist |
+----+------+-----------+--------+---------+------+----------+------------------+
1 row in set (0.00 sec)

表的操作

创建表

语法：

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;
Query OK, 0 rows affected (0.01 sec)

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;
Query OK, 0 rows affected (0.01 sec)

user1的存储引擎是MyISAM，user2的存储引擎是InnoDB。

不同的存储引擎的表结构可以看到明显的不同。users.frm：表结构，users.MYD：表数据，users.MYI：表索引。

查看表信息

show tables; -- 查看表
desc 表名;  -- 查看表结构
show create table 表名; -- 查看创建表结果时的信息

mysql> show tables;
+-------------------+
| Tables_in_user_db |
+-------------------+
| user1             |
| user2             |
+-------------------+
2 rows in set (0.00 sec)

mysql> desc user1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| password | char(32)    | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> show create table user1 \G
*************************** 1. row ***************************
       Table: user1
Create Table: CREATE TABLE `user1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL COMMENT '用户名',
  `password` char(32) DEFAULT NULL COMMENT '用户密码',
  `birthday` date DEFAULT NULL COMMENT '生日'
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

修改表

语法：

ALTER TABLE tablename ADD (column datatype [DEFAULT expr][,column datatype]...);
ALTER TABLE tablename MODIfy (column datatype [DEFAULT expr][,column datatype]...);
ALTER TABLE tablename DROP (column);

示例：

-- 对表进行重命名 rename to ps.to可以省略
alter table user1 rename to user;

-- 向表中插入新列 add 
-- alter table user add image_path varchar(128) comment '头像路径' after birthday;
mysql> select * from user;
+------+--------+----------+------------+
| id   | name   | password | birthday   |
+------+--------+----------+------------+
|    1 | 张三   | 12345    | 2010-10-01 |
|    2 | 李四   | 54321    | 2015-10-01 |
+------+--------+----------+------------+
2 rows in set (0.00 sec)

mysql> alter table user add image_path varchar(128) comment '头像路径' after birthday;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from user;
+------+--------+----------+------------+------------+
| id   | name   | password | birthday   | image_path |
+------+--------+----------+------------+------------+
|    1 | 张三   | 12345    | 2010-10-01 | NULL       |
|    2 | 李四   | 54321    | 2015-10-01 | NULL       |
+------+--------+----------+------------+------------+
2 rows in set (0.00 sec)

-- 修改一列属性 modify ps.修改是覆盖式地修改，修改后comment描述不见了
-- alter table user modify name varchar(60);
mysql> show create table user \G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL COMMENT '用户名',
  `password` char(32) DEFAULT NULL COMMENT '用户密码',
  `birthday` date DEFAULT NULL COMMENT '生日',
  `image_path` varchar(128) DEFAULT NULL COMMENT '头像路径'
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table user modify name varchar(60);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show create table user \G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(60) DEFAULT NULL,
  `password` char(32) DEFAULT NULL COMMENT '用户密码',
  `birthday` date DEFAULT NULL COMMENT '生日',
  `image_path` varchar(128) DEFAULT NULL COMMENT '头像路径'
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

-- 删除列 drop 
-- alter table user drop password;
mysql> select * from user;
+------+--------+----------+------------+------------+
| id   | name   | password | birthday   | image_path |
+------+--------+----------+------------+------------+
|    1 | 张三   | 12345    | 2010-10-01 | NULL       |
|    2 | 李四   | 54321    | 2015-10-01 | NULL       |
+------+--------+----------+------------+------------+
2 rows in set (0.00 sec)

mysql> alter table user drop password;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from user;
+------+--------+------------+------------+
| id   | name   | birthday   | image_path |
+------+--------+------------+------------+
|    1 | 张三   | 2010-10-01 | NULL       |
|    2 | 李四   | 2015-10-01 | NULL       |
+------+--------+------------+------------+
2 rows in set (0.00 sec)

-- 修改一列名称 change
-- alter table user change name xingming varchar(20);
mysql> show create table user \G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(60) DEFAULT NULL,
  `birthday` date DEFAULT NULL COMMENT '生日',
  `image_path` varchar(128) DEFAULT NULL COMMENT '头像路径'
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> desc user;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int(11)      | YES  |     | NULL    |       |
| name       | varchar(60)  | YES  |     | NULL    |       |
| birthday   | date         | YES  |     | NULL    |       |
| image_path | varchar(128) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table user change name xingming varchar(20);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show create table user \G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(11) DEFAULT NULL,
  `xingming` varchar(20) DEFAULT NULL,
  `birthday` date DEFAULT NULL COMMENT '生日',
  `image_path` varchar(128) DEFAULT NULL COMMENT '头像路径'
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> desc user;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int(11)      | YES  |     | NULL    |       |
| xingming   | varchar(20)  | YES  |     | NULL    |       |
| birthday   | date         | YES  |     | NULL    |       |
| image_path | varchar(128) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

删除表

语法：

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

-- 删除表
-- drop table user;
mysql> show tables;
+-------------------+
| Tables_in_user_db |
+-------------------+
| user              |
| user2             |
+-------------------+
2 rows in set (0.00 sec)

mysql> drop table user;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+-------------------+
| Tables_in_user_db |
+-------------------+
| user2             |
+-------------------+
1 row in set (0.00 sec)