MySql 基础操作
数据库简介
数据库分为两种：
- RBMS（关系型数据库）关系型数据关注安全方面
- MySQL
- Oracle
- MsSQL
- PG
- NoSQL（非关系型数据库）非关系型数据主要关注性能方面
- Redis 、 memcached 键值
- json 文档
- 一般我们都是两种类型的数据库配合使用
数据库中的语言分为两种, 一种是SQL92一种是SQL99
MySQL中最经典的SQL语句类型
- DDL ： 数据定义语言
- DCL ： 数据控制语言
- DML ： 数据操作语言
- DQL ： 数据查询语言
执行SQL语句会执行一下操作
- 网络层: 进行网络连接， 数据校验，提供通信通道
- SQL层: 进行语法，语义，权限，解析， 优化， 执行
- 存储引擎: 获取数据，返回给用户
Linux数据安装
数据库安装教程比较齐全就浪费时间来编写如何安装了,现将一篇不错的博客引入在此
Linux安装MySql
数据库存储
数据库中库其实是一文件夹的形式来存储，我们找到对应的文件夹后的通过Liunx命令
makir meet
这个命令就可以创建一个库，我们在进入mysql中输入
show databases;
就能看到我们在数据库外边手动创建的meet目录
数据库中某个表其实在操作系统中也是也是以文件的形式存储的，我们现在就来看一下mysql这个库中`user``这个表在文件中是怎样展示的
-rwxrwxrwx 1 mysql mysql 10684 Apr 8 2020 user.frm
-rwxrwxrwx 1 mysql mysql 596 Apr 8 2020 user.MYD
-rwxrwxrwx 1 mysql mysql 2048 Apr 15 2020 user.MYI
-rwxrwxrwx 1 mysql mysql 10684 Apr 8 2020 userinfo.frm
-rwxrwxrwx 1 mysql mysql 10684 Apr 8 2020 userinfo.ibd
以上这写文件是是两种数据库存储引擎，我们先来看看上边3个文件分别具有怎样的功能，
- user.frm ： 记录着user表中所有列数据
- user.MYD ： 记录着user表中所有数据行信息
- user.MYI ： 记录着user表中所有的索引
这种是MyISAM引擎存储的表； 我们看完上边的3个后，现在来看看下边的两个是什么意思以及是什么引擎
- userinfo.frm ： 记录着userinfo表中所有列数据
- userinfo.ibd ： 记录着userinfo表中所有的记录以及索引
这种是InnoDB引擎是MysQL默认使用的一种存储引擎
对于innodb存储引擎而言，其数据文件最小的存储单位为页。默认为16KB大小。在页的基础上又分为区，段，和表空间。innodb存储引擎对于空间的申请不是每次以16KB的方式申请，而是以区的方式。一个区的大小为1MB，总共有64个页。这样的目的是提高空间的申请效率。如果数据是按照减值顺序存放的，那么读取这些页，将在一个连续的地址中，这样可以避免磁头的大量的寻址时间
用户与数据的关系
用户的作用
- 登录
- 管理数据库逻辑对象（库和表）
用户连接方式定义
用户名@白名单IP地址， 白名单是允许连接数据的IP
连接数据库方式（白名单支持的方式）
meet@'10.10.10.%' %代表最后一段是0~255范围内任意数字
meet@'10.10.5%' %代表于5组成的任意数字
meet@'10.10.2' 没有%就是明确指定这个IP可以连接
meet@'localhost' 通过socket方式进行连接
meet@'db2' 通过db2这种别名进行连接
meet@'10.2.23.%/255.255.254.0' 指定254这个子网掩码内最后一段是0~255内任意数字的IP
meet@'%' 任何IP
我们知道怎样控制用户连接方式后，我们想要让用户来连接数据库来干活了。第一步我们徐亚创建一用户，那我们就来看一看怎么样创建用户
创建用户
我们现在先进去到mysql当中，输入以下命令
create user meet@'10.22.12.%' identified by '123'
create user meet@'10.22.22.5%' identified by '111'
我们输入以上命令后可以在数据库中查看是否创建成功
mysql> select user,host from mysql.user;
+----------+-------------------------+
| user | host |
+----------+-------------------------+
| meet | % |
| root | 127.0.0.1 |
| root | ::1 |
| | iz2ze5bbo8swoo22chet75z |
| root | iz2ze5bbo8swoo22chet75z |
| iwebshop | locahost |
| | localhost |
| root | localhost |
+----------+-------------------------+
8 rows in set (0.00 sec)
通过这个我们就能清楚的知道我们的用户创建成功了，但是如果我们在创建的时候数输入的密码太简单想要重新修改怎么办呢？ 莫慌我们来看一下修改语句
alter user meet@'10,22.12%' identified by 'meet123'
这里修改语句使用的是alter，我们知道如何修改后我们也到对应知道怎么删除
drop user meet@'10,22.12%'
有人在想我直接删除这个用户名不就可以了吗? 当然不是，因为用户和白名单是一体的。
我们在查询用户的表的时候想知道这个密码时就需要明确的知道密码这个字段的名字，但是这个字段比较长不容易记住我们可以查看表结构来看
desc mysql.user
这样就能展示出这个表中所有的字段啦
select user,host,authentication_string from mysql.user;
这个就是查看用户表中用户名和主机名以及密码。
MySQL可以将某个命令设置一个权限，可以单独授权select等等，也可以授权all就是所有命令权限
我们创建好用户后就需要对这个用户来分配一个权限，这样我们就能的准确的控制这个用户能执行什么操作
grant all on test.* to meet@'%';
这条命令就是将用所有的权限分配给meet这个用户，还有一条比较简便的命令，可以同时创建用户和授权。
grant select,delete,drop,update,create on test.* to baoyuan@'10.2.2.5' identified by '123';
这样我们就能做到创建用户的同时将给用户设置权限了，但是这样操作其实是不规范的，规范的操作应该是第一种方式，但是好多人都为了省事直接使用第二种方式。mysql为了明确的强调这个问题在8.0版本后第二种抒写方式这样的语法就失效了
我们知道如何给用户授权权限后，我们如果想要知道这个权限是否配置生效我们就可以通过以下命令来查看
show grants for baoyuan@'10.2.2.5';
这个命令中后面的baoyuan@'10.2.2.5'是你还要查看的用户名，以下就是baoyuan@'10.2.2.5'这个用户拥有的权限
+--------------------------------------------------------------------------------+
| Grants for baoyuan@10.2.2.5 |
+--------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'baoyuan'@'10.2.2.5' |
| GRANT SELECT, UPDATE, DELETE, CREATE, DROP ON `test`.* TO 'baoyuan'@'10.2.2.5' |
+--------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
我们通过查看用户所拥有的权限中发现，宝元这个用户居然拥有delete和drop这种删除命令时想要收回这个怎么办？我们可以对权限进行回收
revoke delete on test.* form baoyuan@'10.2.2.5';
这样我们就把delete这个权限给回收了，我们也可以同时回收多个权限例如：
revoke update,create on test.* from baoyuan@'10.2.2.5';
我们同时回收了update，create这两个权限，我们现在可以通过查看的baoyaun这个用户来确是否回收成功，执行结果如下：
mysql> show grants for baoyuan@'10.2.2.5';
+--------------------------------------------------------+
| Grants for baoyuan@10.2.2.5 |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'baoyuan'@'10.2.2.5' |
| GRANT SELECT, DROP ON `test`.* TO 'baoyuan'@'10.2.2.5' |
+--------------------------------------------------------+
2 rows in set (0.00 sec)
通过这个我们就明确的知道可以同时回收多个权限，我们既然现在学习的是的用户管理方向的内容我们就来看看如果root用户密码忘记了我们应该怎么办呢？
第一步：我们先将mysql停止运行然后使用调试的语句来启动数据库
systemctl stop mysqld
mysald_safe --skip-grant-tables --skip-networking & #Linux使用这个命令
mysqld -nt --skip-grant-tables # windows使用这个命令
这条命令就是屏蔽掉用户验证以及TCP/IP连接方式，目前的mysql只能通过socket方式访问
第二步：通过本地socket方式访问mysql然后通过alter方式就行密码修改
mysql -uroot -p 密码为空或者任意输入
然后我们修改root这个用户的密码，但是我们是无法修改成功的因为我们在连接数据库的时候将用户登录这个校验给注释掉了，我们想要修改必须先要将用户和密码都加载到内存中我们来看一下这个怎么加载
flush privileges;
然后我们使用这个命令将数据库相关用户和密码加载到内存中后我们就使用下面的这条命令来修改root用户的密码
alter user root@'localhost' identified by 'root123';
这样我们就成功的将root用户的密码更改了,更改完成后我们需要将数据库重新启动
pillk mysqld
systemctl start msyqld
数据库连接
平时我们在连接数据库的时候都会使用
mysql -uroot -p
这样的方式来连接,这样其实默认是使用的socket方式来连接的,其实还有一种编写方式也是通过socket方式来连接的
mysql -uroot -p -S /tmp/mysql.socket
我们不仅仅能通过socket这样方式来连接数据库,我们还能通过TCP/IP的方式来连接
mysql -uroot -p -h 192.168.11.6 -P 3306
以上命令中出现两个我们之前没有见过的参数,我们现在来看一下这个参数是什么意思
- h 主机IP
- P 端口号
- u 用户名
- p 密码
- S 使用socket
- e 免交互执行命令
以上大P必须要配合h来使用, 单独使用的P是不行的
通过上边介绍我们可以知道,上边是使用socket方式连接,下边是使用TCP方式连接, 肯定有人会想要将这两种放到一起的话会使用那种连接呢?
我们来试一下
mysql -uroot -p -S /tmp/mysql.socket -h 192.168.22.88 -P 3306
我们连接后的怎样判断是走的socket还是http请求呢? 我们可以通过以下命令来辨识
mysql> show processlist;
+----+------+-----------------------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------------+------+---------+------+-------+------------------+
| 21 | root | 221.218.213.203:53189 | NULL | Query | 0 | init | show processlist |
+----+------+-----------------------+------+---------+------+-------+------------------+
1 row in set (0.01 sec)
通过这个我们可以看出来其实是通过TCP这样方式进行连接的, 我们到现在为止还差e和<不知道是怎样使用的,现在给演示一下
[root@meet ~]# mysql -uroot -p -e "select user,host from mysql.user;"
Enter password:
+----------+-------------------------+
| user | host |
+----------+-------------------------+
| meet | % |
| root | % |
| root | 127.0.0.1 |
| root | ::1 |
| | iz2ze5bbo8swoo22chet75z |
| root | iz2ze5bbo8swoo22chet75z |
| iwebshop | locahost |
| | localhost |
| root | localhost |
+----------+-------------------------+
通过以上内容我们可以看出本应该在mysql交互模式下执行的命令现在在普通模式下执行成功了
数据库配置文件
我们从开始到现在为止都是在使用msyql -uroot -p这样的命令来进行连接.这种连接方式就是固定了服务器的端口以及数据库中的库,表存储位置以及我们使用socket连接的时候socket问价存放的位置等等, 如果3306这个端口被其他应用才占用了或者说我们想要更换数据库存放文件的路径时我们就需要自己来定义一个方便我们自己的配置文件了
第一步
我们在/etc/目录下创建一个my.cnf配置文件
第二步
我们将以下内容写入到配置文件中
[mysqld] # 服务端配置标签
port=3309
datadir=/data/
basedir=/data/base
[mysql] # 数据库标签
pormpt=[\\d] # 这个显示当前数据库名称
[client] # 客户端配置标签
user=root
password=123
第三步 重启数据库service mysqld restart
这样我们就能按照我们想要的方式来连接并且使用数据库, 后续我们还能指定的连接数量以及查询方式等等, 后续会对my.cnf进行更新, 数据库配置文件我们也有个简单的认识后现在回到交互的状态下, 我们前边学习了如何创建用户,如何授权以及如何连接等等这些其实都是在干活时打基础, 现在我们就开始干活了
数据库操作
在之前我们介绍数据的时候提到过DDL,DCL,DML我们现在就来看看这个DDL
DDl就是数据库层面操作的语句的一种分类, 我们想要对数据库进行操作其实维度就是增,删,改,查 我们就先来创建一个数据库
增操作
create database `baoyuan`;
通过这个命令其实就能够创建一个数据库
查操作
首先我们需要验证以上增加数据库的命令是否生效,使用以下命令来查看所有的数据库
[(none)]show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| baoyuan |
| meet |
| mysql |
| performance_schema |
| sys |
| yaya |
+--------------------+
7 rows in set (0.00 sec)
通过上方命令我们能够确定数据库创建成功了,但是我们创建数据库的目的就是存放表,表中是存放数据的, 数据中就可能出现中文以及大小写的英文. 使用上边的命令进行创建后不分大小写并且无法展示中文. 我们能够知道我们创建的这个数据库是不是能够正常展示中文就使用以下命令来查看挡墙数据库使用的什么字符集
[(none)]show create database meet;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| meet | CREATE DATABASE `meet` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
latin1就是一个字符集,只不过这个字符集和ASCLL码和相似,没有中文所有不能正常显示中文, 但是我们的需求就是想要正常展示中文,我们就要科普一下utf8这个字符集,这个字符集是能够显示中文以及其他国家的文字,我们想要能够正常显示中文我们就需要使用以下命令对数据库的字符集进行修改操作
改操作
alter database baoyuan charset utf8;
执行这个更改字符集命令有个要求将就是新的字符集能显示的内容一定要完全大于当前数据库的字符集, 我们现在知道怎样创建数据库和修改以及查看就差最后一个危险的操作了
删操作
drop database baoyuan;
以上这个命令是个非常危险的操作, 使用场景只能是自己本地的测试环境别人创建的库也不能进行删除. 这个命令一定要慎重慎重
数据表操作
我们创建号了数据库后现在应该创建表了, 我们在创建表之前需要知道一些最常用的数据类型, 数据类型如下表格
| 类型 | 范围 | 用途 |
|---|---|---|
| INT | 0~4294967295 | 大整型 |
| TINYINT | 0~255 | 小整型 |
| CHAR | 0~255bytes | 定长字符串 |
| VARCHAR | 0-65535 bytes | 变成字符串 |
| LONGTEXT | 0~4294967295 bytes | 极大文本数据 |
| DATE | YYYY-MM-DD | 日期值 |
| TIME | HH:MM:SS | 时间值 |
| DATETIME | YYYY-MM-DD HH:MM:SS | 日期+时间 |
| TIMESTAMP | YYYYMMDD HHMMSS | 日期+时间/时间戳 |
我们看过了常用的数据类型后现在看一些常用字段
| 字段 | 功能 |
|---|---|
| primary key | 主键 |
| auto_increment | 自增 |
| not null | 不为空 |
| default | 默认 |
| unsigned | 无符号 |
| unique | 唯一 |
| comment | 备注 |
| enum | 枚举 |
| foreign key | 外键 |
我们现在知道了一些常用的数据类型和字段后我们现在来看创建一个数据表来应用一下这些内容
增操作
我们想要创建一个表就需要知道创建表的固定结构是什么
create table 表名(
字段名称 数据类型 属性,
字段名称 数据类型 属性,
);
我们通过上边的固定结构来创建一个用户表, 在创建表的时候我们需要明确的知道这个表中我们都需要拥有什么例如:
序号,用户名称,用户年龄,用户性别,用户爱好,用户身份证号,用户创建时间,等等
create table userinfo(
id INT NOT NULL PRIMARY KEY COMMENT '序号',
name VARCHAR(64) NOT NULL COMMENT '姓名',
age TINYINT NOT NULL COMMENT '年龄',
gender ENUM('男','女') DEFAULT '男' COMMENT '性别',
userid INT(18) UNSIGNED NOT NULL UNIQUE COMMENT '省份证',
create_time TIMESTAMP NOT NULL COMMENT '创建时间'
);
我们通过创建一个用户表就差不多将以上的内容用上了, 我们现在来看看我们创建的表的方式
查操作
select * from userinfo; # 查看当前这个userinfo表中所有数据
desc userinfo; # 查看userinfo这个表每个字段
show create table userinfo; # 查看userinfo这个表创建的原生语句
通过刚刚查看用户表有没有发现这个表中缺少一个字段名字, 如果我想联系某个人怎么联系呢, 是不是需要增加一个手机号的字段 现在我们就来增加一下
改操作
alter table userinfo add phone INT(11) UNSIGNED COMMENT '手机号';
我们在查看一下是否增加个手机号这个字段
desc userinfo;
有人在想我们创建的表的时候少创建一个字段后我们可以增加,但是通过刚刚的查看发现新增加的在最后边.我们能不能指定在某个字段后面增加一个字段,例如我想在name后边增加一个微信号
ALTER TABLE userinfo ADD wechat VARCHAR(64) NOT NULL UNIQUE COMMENT "微信号" AFTER name;
这样我们就知道如何指定位置添加字段了, 能在某个字段后面增加我们也能在某个字段之前增加.
ALTER TABLE userinfo ADD num INT(32) NOT NULL COMMENT "数字" FIRST;
有人也会想了要是创建的时候多一个字段应该怎么办呢?
删操作
alter table userinfo drop name;
以上这个命令是删除表中某一个字段, 如果我想删除一个数据表应该怎么办呢?
drop table userdata;
现在我们知道了如何创建数据库以及数据表后现在最主要的就是向表中添加数据了
增加数据
我们就用userifno这个表来存储一些数据 现在来看看如何增加数据
[baoyuan]insert into userinfo value(1,"Meet","GBYDWX",25,"男","110120119114",now(),"932023756@qq.com");
Query OK, 1 row affected (0.00 sec)
