MySQL数据库操作_mysql 如何数据库-优快云博客

一：MySQL数据库介绍

二：MySQL数据库操作

一：MySQL数据库介绍

在熟悉安装及访问 MySQL 数据库以后，接下来将学习使用 MySQL 数据库的基本操作，这也是在服务器运维工作中不可或缺的知识。本节中的所有数据库语句均在“mysq1>”操作环境中执行，并且每一条操作语句都是以分号(;)结束的

SQL 语言主要由以下几部分组成。

DDL(Data Definition Language，数据定义语言):用来建立数据库、数据库对象和定义字段，如CREATE、ALTER、DROP。

DML(Data Manipulation Language，数据操纵语言):用来插入、删除和修改数据库中的数据，如INSERT、UPDATE、DELETE。

DQL(Data Query Language，数据查询语言):用来查询数据库中的数据如 SELECT.

DCL(Data Control Language,数据控制语言):用来控制数据库组件的存取许可、存取权限等，如 COMMIT、ROLLBACK、GRANT、REVOKE。

二：MySQL数据库操作

1.系统数据库

经初始化后的 MySQL 服务器，默认建立了四个数据库:sys、mysq1、information schemaperformance schema

information schema:虚拟库，不占用磁盘空间，存储的是数据库启动后的一些参数，如用户表信息、列信息、权限信息、字符信息等

performance schema: MySQL 5.5开始新增一个数据库:主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件、锁等现象

mysql:授权库，主要存储系统用户的权限信息

sys: 主要用于存储系统性能信息和监控数据,对数据库的性能优化和故障排除具有关键作用

2.数据库操作

（1）.创建数据库

语法：

示例：创建一个名为db1的库

（2）数据库命名规则

可以由字母，数字，下划线，@，#，$

区分大小写

唯一性

不能使用关键字如 create select

不能单独使用数字

（3）选择数据库

示例：切换|进入 db1

（4）查看数据库

查看数据库有哪些表

显示创建名为db1的数据库时使用的sql语句

返回当前数据库的名称

（5）删除数据库

示例：删除数据库db1

三：mysql表操作

1.表介绍

库相当于文件夹，而表相当于文件，表中的一条记录相当于文件的一行内容，不同的是，表中的一条记录有对应的标题，称为表的字段

ID	name	age	sex
1	张三	18	male
2	李四	40	female
3	王五	90	male

其中：id，name，age，sex称为字段，其余的，一行内容称为一条记录

2.查看表

使用show tables 查看当前所在的数据库中包含的表，在操作之前，需要先使用use语句切换到所使用的数据库。

先进入数据库

查看所有表

3.创建表

（1）语法

注意：

在同一张表中，字段名不能相同
宽度和约束条件可选
字段名和类型是必须的，字段的类型宽度和约束条件是可选项
表中最后一个字段不要加逗号

（2）数据类型介绍

表内存放的数据有不同的类型，类似于使用excel存储数据时也需要设置的数据类型，每种数据类型都有自己的宽度，但宽度是可选的，不设置宽度时，会使用字段的默认宽度。

数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
INT	4字节	(-2 147 483 648， 2 147483 647)	(0，4 294 967 295)	大整数值
DOUBLE	8 字节	(-1.797E+308,-2.22E-308)	（0,2.22E- 308,1.797E+308)	双精度浮点数值
DOUBLE(M,D)	8个字节，M表示长度，D表示小数位数	同上，受M和D的约束DOUBLE(5,2)-999.99-999.99	同上，受M和D的约束	双精度浮点数值
DECIMAL(M,D)	DECIMAL(M,D)	依赖于M和D的值，M最大值为65	依赖于M和D的值，M最大值为65	小数值

日期类型

类型	大小	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	-838:59:59'/'838:59:59	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-0100:00:00/ 9999-12-31 23:59:59	YYYY-MM-DDHH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-0100:00:00/2038结束时间是第 2147483647 秒北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨03:14:07	YYYYMMDDHHMMSS	混合日期和时间值，时间戳

字符串类型

类型	大小	用途
CHAR	0-255字符	定长字符串 char(10)10个字符
VARCHAR	0-65535 字节	变长字符串 varchar(10) 10个字符
BLOB(binary large object)	0-65535字节	二进制形式的长文本数据
TEXT	0-65535字节	长文本数据

（4）.约束条件

约束条件与数据类型的宽度一样，都是可选参数，类似于使用exce1存储数据时，可以利用 excel 的公式限制员工 ID 列:禁止重复值，且不能为空

约束条件作用:用于保证数据的完整性和一致性，主要分为:

约束条件	说明
PRIMARY KEY (PK)	标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK)	标识该字段为该表的外键
NOT NULL	标识该字段不能为空
UNIQUE KEY (UK)	标识该字段的值是唯一的
AUTO INCREMENT	标识该字段的值自动增长(整数类型而且为主键)
DEFAULT	为该字段设置默认值
UNS IGNED	无符号
ZEROFILL	使用0填充
ENUM（）	限制字段可以存储的值集合

（5）创建表示例

查看表结构

语法：describe 表名；若只指定表名参数，则需要先使用UES语句切换到目标数据库

查看详细表结构或者创建表所使用的语句可以执行 show create table t1\G:#查看表详细结构，可加\G。\G表示以长格式展示结果

4.修改表

（1）修改表名

示例：

（2）增加字段

示例：

（3）删除字段

（4）修改字段

6.复制表

只复制表结构，不复制表中数据

复制表结构+记录（key不会复制：主键，外键，索引）

7.删除表

删除数据库中的表，需要指定“数据库名.表名”作为参数;若只指定表名参数，则需先通过执行“USE”语句切换到目标数据库

四：MySQL数据库操作

1.介绍

在 MySQL 管理软件中，可以通过 SQL 语句中的 DM,语言来实现数据的操作，包括使用 INSERT 实现数据的插入、使用 UPDATE 实现数据的更新、使用 DELETE实现数据的删除、使用 SELECT 查询数据。

2.插入数据insert

顺序插入

insert into 表名 valuse （值1，值2，值3....)；

查看tt表的结构

插入数据

指定字段插入数据：

insert into 表名（字段1，字段2，字段3...)values（值1，值2，值3...);

插入多条记录：

insert into 表名 valuse （值1，值2，值3....)，（值1，值2，值3....)，（值1，值2，值3....)；

3.删除数据

delete语句：用于删除表中指定的数据记录，语句格式如下：

delete from 表名 where 条件表达式

4.更新数据

update 语句：用于修改，更新表中的数据记录，语句格式如下：

update 表名 set 字段名1=字段值1 ，字段名2=字段值2 where 条件表达式

5.查询数据

select语句：用于从指定的表中查找符合条件的数据记录。

（1）准备数据

（2）简单的查询

语法：

显示表中所有数据：select * from 表名

把表中需要的列的数据查询出来：select 字段1，字段2... from 表名

示例：

（3）where 条件

WHERE 是 SQL, 中用于筛选数据的核心子句，其作用是通过指定条件从表中过滤出符合条件的记录。它可应用于 SELECT、UPDATE、DELETE 等语句中，控制操作的范围

where 字句中可以使用:

比较运算符:>く>=<=く>!=
between 80 and 100 值在 10 到20 之间
in(80,90,100)值是 10 或 20 或 30
like 'egon%‘
逻辑运算符:在多个条件直接可以使用逻辑运算符 and or not

（1）比较运算符：>、<、>=，<=,!=

薪资大于 10000 的人姓名

不是 403 办公室的人姓名

(2)between and :在什么之间

薪资在 10000 至 15000 之间的人姓名

(3)in: 集合查询

薪资是 9000 或 10000 或 30000 的人姓名

(4) like:像，模糊匹配

_:任意单个字符

%:任意多个字符

名字以“程”开头的人的信息

(5)逻辑运算符：and or not

薪资 17000 并且办公室是 403 的人

薪资 17000 或者办公室是 403 的人

薪资不是 9000 或 10000 或 30000 的人姓名

（6）group by 分组与聚合所数:count()avg()max()min()sum()

分组指的是:将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等

count():计数

计算男生和女生的人数

avg():平均值

每个岗位的平均薪资

max():最大值

每个岗位的最高薪资

min():最小值

每个岗位的最低薪资

sum():总和

每个岗位的薪资总和

（7）having过滤

执行优先级从高到低:where>groupby>having

Where 发生在分组 group by 之前，因而 Where 中可以有任意字段，但是绝对不能使用聚合函数。

Having 发生在分组 group by 之后，因而 Having 中可以使用分组的字段，无法直接取到其他字段,但可以使用聚合函数

有哪些岗位是平均工资大于 10000 的

（8）order by 排序

使用 SELECT 语句可以将需要的数据从 MySQL 数据库中查询出来，如果对查询的结果进行排序，该如何去实现呢?可以使用ORDERBY 语句来完成排序，并最终将排序后的结果返回给用户

排序的关键字可以使用 ASC或者 DESC。ASC 是按照升序进行排序的，是默认的排序方式，即 ASC 可以省略。SELECT 语句中如果没有指定具体的排序方式，则默认按 ASC 方式进行排序。DESC是按降序方式进行排列

（9）limit限制结果条目

在使用 MySQL SELECT 语句进行查询时，结果集返回的是所有匹配的记录。有时候仅需要返回第一行或者前几行，这时候就需要用到LIMIT 子句

倒叙排列后仅显示第一行

6.正则匹配

MySQL 正则表达式通常是在检索数据库记录的时候，根据指定的匹配模式匹配记录中符合要求的特殊字符串。MySQL的正则表达式使用REGEXP 这个关键字来指定正则表达式的匹配模式

Name 是 z 开头的姓名

Name 是u结尾的姓名

Name 是 wan 和 wu之间至少1个g的姓名

7.多表查询

多表查询用于从多个关联表中提取组合数据，常见方式包括JOIN 连接和子查询，以下是关键知识点与使用场景:

准备数据

（1）子查询

子查询也被称作内查询或者嵌套查询，是指在一个查询语句里面还嵌套着另一个查询语句。子查询语句是先于主查询语句被执行的，其结果作为外层的条件返回给主查询进行下一步的查询过滤。子查询不仅可以在SELECT 语句中使用，在 INERT、UPDATE、DELETE 中也同样适用。在嵌套的时候，子查询内部还可以再次嵌套新的子查询，也就是说可以多层嵌套。

在开始实际的举例之前，先来学习一下 IN 这个操作符的用法，IN 用来判断某个值是否在给定的结果集中，通常结合子查询来使用。IN的语法结构如下:

<表达式>[ NOT] IN <子查询>

当表达式与子查询返回的结果集中的某个值相等时，返回TRUE，否则返回FALSE。若启用了 NOT 关键字，则返回值相反。需要注意的是，子查询只能返回一列数据，如果需求比较复杂，一列解决不了问题，可以使用多层嵌套的方式来应对。

查询平均年龄在 25 岁以上的部门名

查看技术部员工姓名

查看不足1人的部门名(子查询得到的是有人的部门 id)

(2)多表连接查询

MySQL 的连接查询，通常都是将来自两个或多个表的行结合起来，基于这些表之间的共同字段，进行数据的拼接。首先，要确定一个主表作为结果集，然后将其他表的行有选择性的连接到选定的主表结果集上。使用较多的连接查询包括，内连接、左连接和右连接。

内连接(只连接匹配的行)

MySQL中的内连接就是两张或多张表中同时符合某种条件的数据记录的组合。通常在 FROM 子句中使用关键字INNERJOIN来连接多张表，并使用 ON 子句设置连接条件。内连接的语法格式如下。

左连接(优先显示左表全部记录)

MySQL 除了内连接，还可以使用外连接。区别于 MySQL 外连接是将表分为基础表和参考表，再依据基础表返回满足条件或不满足条件的记录。外连接按照连接时表的顺序来分，有左连接和右连接之分。

左连接也可以被称为左外连接，在FROM 子句中使用 LEFT JOIN 或者 LEFT0UTER JOIN 关键字来表示。左连接以左侧表为基础表，接收左表的所有行，并用这些行与右侧参考表中的记录进行匹配,也就是说匹配左表中的所有行以及右
表中符合条件的行

.右连接(优先显示右表全部记录)

右连接也被称为右外连接，在FROM子句中使用RIGHTJOIN 或者 RIGHT0UTERJOIN 关键字来表示。右连接跟左连接正好相反，它是以右表为基础表，用于接收右表中的所有行，并用这些记录与左表中的行进行匹配。也就是说匹配右表中的每一行及左表中符合条件的记录

五：MySQL数据库用户授权

MySQL 数据库的 root 用户账号拥有对所有数据库、表的全部权限，频繁使用 root 账号会给数据库服务器带来一定的安全风险。实际工作中，通常会建立一些低权限的用户，只负责一部分数据库、表的管理和维护操作，甚至可以对查询、修改、删除记录等各种操作做进一步的细化限制，从而将数据库的风险降至最低。

1.创建用户

Mysql 用户创建与授权的分离，必须先创建用户才能给用户授权语法:

greate user 用户名@来源地址 identified by ‘密码’

用户名@来源地址:用于指定用户名称和允许访问的客户机地址,即谁能连接能从哪里连接。来源地址可以是域名、IP地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址，如“%.bdgn.com”“192.168.1.%”等。
IDENTIFIEDBY:用于设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略“IDENTIFIED BY”部分，则用户的密码将为空

执行以下操作可以添加一个名为“xiaogi”的数据库用户,并允许其从本机访问验证密码为“123456”

2.授权操作

授权语法：

grant 权限列表 on 数据库名.表名 to 用户名@来源地址

权限列表:用于列出授权使用的各种数据库操作，以逗号进行分隔，如select, insert,update”。使用“all”表示所有权限，可授权执行任何操作。
数据库名.表名:用于指定授权操作的数据库和表的名称，其中可以使用通配符“*”。例如，使用“auth.*”表示授权操作的对象为 auth

执行以下操作可以为数据库用户'xiaoqi’@ localhost’，设置对 test 数据库中的所有表具有查询权限

切换到其他 She11 终端，以用户 xiaoqi 的身份连接数据库。在已授权的数据库上操作将被允许，否则将被拒绝。例如，允许用户xiaoqi 查询 test 数据库中表的数据记录，但禁止查询其他数据库中的表的记录。show databases只能看到被授权的库

3.查看权限

SHOW GRANTS 语句:专门用来查看数据库用户的授权信息，通过FOR子句可指定查看的用户对象(必须与授权时使用的对象名称一致)，语句格式如下：

show grants for 用户名@来源地址

执行以下操作可以査看用户 dbuser 从主机 192.168.4.19 访问数据库时的授权信息。

4.撤销权限

REVOKE语句:用于撤销指定用户的数据库权限，撤销权限后的用户仍然可以连接到 MySQL 服务器，但将被禁止执行对应的数据库操作，语句格式如下：

revoke 权限列表 on 数据库名.表名 from 用户名@来源地址

执行以下操作可以撤销用户 xiaogi 从本机访问数据库 test 的所有权限

总结

MySQL 作为最流行的关系型数据库之一，是数据存储与管理的核心工具。本课程系统讲解了 MySQL的核心操作技能，从数据库(库)的创建与管理、表结构的定义与维护、数据的增删改查操作，到用户权限的精细化控制，全面掌握数据库全生命周期操作。通过理论与实践结合,理解权限分配对数据安全的关键作用，为构建可靠、安全的数据库系统奠定坚实基础。