MySql | 基础知识 | 学习笔记

1. 数据库的基本知识

数据库顾名思义就是存储数据的仓库，而 MySql 则是数据库中关系数据库的一种，它主要是以二维表的结构来存储数据

2. 数据库的操作

数据库主要是利用 SQL 语句对数据库，表进行增删改查等操作。
SQL 通用语法

1. 单行或者多行书写，以分号结束

没写分号时，mysql 会认为语句没有输入完整，则不会执行语句。

只有写了分号时则可以正常执行语句

2. 可以使用空格或者缩进来增强语句的可读性
3. mysql数据库中，SQL语句不区分大小写，关键字建议使用大写
4. 注释（注释不影响 SQL 语句的执行）
   1. 单行注释 # –
   2. 多行注释， /* */

SQL 语句的分类

1. DDL 数据定义语言，用来定义数据库对象（数据库，表，字段）
2. DML 数据操作语言，用来对数据表中的数据进行增删改
3. DQL 数据查询语言，用来查询数据库中表的记录
4. DCL 数据控制语言，用来创建数据库用户，控制数据库的访问权限

在执行数据库所有操作之前，先登录数据库

-- 该命令执行之后，输入登录密码即可登录数据库
mysql -u root -p

注意：以上命令主要是以数据库管理员的身份进行登录的（拥有所有数据库相关的操作权限），该命令也可以作为数据库安装和配置是否成功的检验方式之一，以下表示登录成功

DDL-数据库操作

1. 查看数据库

-- 查看当前全部的数据库名
show databases;

2. 创建数据库

-- 创建新的数据库
CREATE database [IF NOT EXISIT] 数据库名字 [DEFAULT CHARSET utf8mb4]

-- MySql8 以上的版本在创建时候的默认编码是'utf8mb4'

注意：在执行以上 SQL 语句的时候 数据库名字不需要加单引号 ’ '，否则会出现以下报错

3. 选择数据库

-- 表示下面将会对数据库 admin 进行操作
use admin

出现以下这种情况之后就可以对数据库 admin 中进行表操作

注意：这里可以不用加分号 ;

4. 删除数据库

-- 删除数据库
DROP database admin;

当出现下列情况之后证明数据库删除成功

执行前后对比

DDL-表操作

1. 查看一个当前数据库中的所有表名

SHOW tables;

注意：在执行以上语句之前需要选择相应执行的数据库名称，如下图

2. 创建表结构

-- 创建表
CREATE TABLE 表名称(
  column_name1 datatype [COMMENT '注释'],
  column_name2 datatype
)[CHARACTER SET 编码格式];

column_name1 表示要创建的列名，datatype 表示存储数据的类型
注意：表名称不能加单引号 ’ '，不然如下图

如果是以下这张图则说明建表成功

3. 查看表结构

查看表结构一共有三种命令

-- 查看当前表结构
DESCRIBE itjiange;

-- 和上面的命令类似（是上面命令的简写）
DESC itjiange;

-- 主要用于显示指定表的创建语句，包括表的结构、列定义、约束等详细信息。
SHOW CREATE TABLE itjiange;

4. 删除表

-- 从数据库中删除表，在执行这条命令之前确保已经选中了该表所在的数据库
DROP TABLE 表名;

执行成功的结果如下

5. 给数据库中的表添加一列

ALTER TABLE 表名称 add [column] 列名(字段名) 类型; 

执行alter table copy add column name varchar(10);后

6. 修改列的数据类型

ALTER TABLE 表名称 modify [column] 列名(字段名) 类型;

执行alter table copy modify column name char;，表示将copy表中的name字段的数据类型从原来的varchar(10)更改为现在的char

7. 修改列的名称

ALTER TABLE 表名称 RENAME column 旧列名 to 新列名;

执行alter table copy rename column name to c_name;

注意：column不能省略

8. 同时修改列名称和数据类型

ALTER TABLE 表名 CHANGE [column] 原字段名 新字段名 新数据类型;

执行alter table copy change column c_name name varchar(10);

9. 删除列

ALTER TABLE 表名 DROP [column] 列名(字段名);

10. 修改表名

RENAME TABLE 旧名称 TO 新名称;
-- 或者
ALTER TABLE 表名称 RENAME TO 新名称;

11. 删除表

DROP TABLE [if existx] 表名;

此操作是将表删除了包括表中的数据

12. 截断表

TRUNCATE TABLE 表名;

该操作是将表删除后（包括表中的数据）然后再新建一个结构相同的新表

13. 创建和某表结构一样的新表

CREATE TABLE 表名 LIKE 要复制的表;

-
注意：这里数据是不会被复制的，仅复制表结构

DQL-条件查询

1. 条件过滤

SELECT * FROM 表名称 WHERE 列名(字段名) = 值;

执行select * from dept where dept_name = '开发部门';主要是为了查询dept表中，字段名dept_name等于开发部门的所有数据

SELECT * FROM 表名称 WHERE 列名(字段名) BETWEEN 数值1 AND 数值2;

该语句相当于$数值1<=列名(字段名)<=数值2$或$数值2<=列名(字段名)<=数值1$

2. 算数运算

-- 可以将表中查询出来的两个字段的结果进行相加运算
SELECT 字段1 + 字段2 FROM 表名称;

-- 修改的时候也可以进行算术运算
UPDATE 表名称 SET 字段1 + 字段2;

3. NULL 值查询

SELECT * FROM 表名称 WHERE 字段名 IS NULL;

SELECT * FROM 表名称 WHERE 字段名 IS NOT NULL;

注意：这里的NULL无法通过等值操作进行查询，只能用is，NULL表示空值，它并不是一个字符串型所以在 SQL 中不能加引号

4. 模糊查询

-- LIKE 表示模糊查询，在这里“ _ ”表示只占一位的任意字符，“ % ” 表示占零位或多位字符
SELECT * FROM 表名称 WHERE 字段名 LIKE '_k%';

5. 逻辑运算

-- 在这里的逻辑运算符可以是 <，=，<=
SELECT * FROM 表名称 WHERE 字段名 >= 值;

-- 在这里的 AND 表示且 & 的意思
SELECT * FROM 表名称 WHERE 字段名 IS NULL AND 字段名 < 值;

-- 在这里的 OR 表示或 | 的意思
SELECT * FROM 表名称 WHERE 字段名 IS NULL OR 字段名 < 值;

6. 排序

-- 这里的 DESC 是按照字段名从大到小降序排列
SELECT * FROM 表名称 ORDER BY 字段名 DESC;

-- 这里的 ASC 是按照字段名从小到大升序排列
SELECT * FROM 表名称 ORDER BY 字段名 DESC;

7. 分页查询

-- a 表示起始行，b 表示一页显示的数据的条数
SELECT * FROM 表名称 LIMIT a, b;

假设 n 表示第 n 页，则$a=(n-1)\ast b$

8. 单行函数

-- CONCAT() 方法可以将字段1和字段2进行拼接
SELECT CONCAT(字段1, 字段2) FROM 表名称;

• LENGTH(字段) 可以计算字段长度
• UPPER(字段) 可以转换字段大写
• LOWER(字段) 可以转换字段小写

DML-数据管理

1. 插入数据

-- 写法一：指定列名
INSERT INTO 表名称 (列名1, 列名2, ...) VALUE(值1, 值2, ...);

-- 写法二：不指定
INSERT INTO 表名称 VALUE(值1, 值2, ...);

写法一中要插入的值的顺序和前面列名的顺序一致，值的类型也和前面列名对应的数据类型一致，值的个数和前面列名一致即可。而写法二中插入的值必须和表中所有字段相一致

2. 更新数据

UPDATE 表名 SET 字段名运算 [WHERE ...]

3. 删除数据

DELETE FROM 表名 [WHERE ...]

DCL-用户管理

1. 查看当前登录的用户

SELECT user() [FROM dual] /*user() 是一个函数*/

dual是一个虚拟表，from dual语句主要作用是为了保持查询语句的完整性
该语句执行之后可得到当前登录数据库的用户的用户名

2. 创建新用户

CREATE user 用户名@'IP 地址(例如：192.168.4.31)' IDENTIFIED BY '密码';

如果这里的用户地址是本机以外的地址（外1），则外1可以访问本机的数据库了。（前提是同一局域网下，并且还需关闭局域网防火墙

3. 修改用户密码

-- 语句一
ALTER user 用户名@'IP 地址' IDENTIFIED BY '密码' PASSWORD EXPIRE NEVER;

-- 语句二
ALTER user 用户名@'IP 地址' IDENTIFIED with mysql_native_password BY '';

将更改指定用户的密码
语句一中PASSWORD EXPIRE NEVER是设置密码永不过期。
语句二中with mysql_native_password是 MySQL 的默认身份验证插件，它使用经过加密的密码存储在用户表中。在最新的 MySQL 版本中，它提供了更好的安全性。

4. 查询用户信息

SELECT user,host FROM mysql.user;

用户的信息都保存在 MySql 自带的数据库 mysql 的 user 表中，所以我们可以利用 DQL 查询语句进行表查询操作

5. 用新用户登录

# 这里可以利用新创建的用户名进行登录
mysql -u 用户名 [-h 'IP 地址'] -p;

注意：新创建的用户只有登录数据库的权限并没有任何操作数据库的权限，所以我们还需要为新创建的用户授予数据库操作等方面的权利

6. 为用户授权

• 查询用户的权限

SHOW GRANTS FOR 用户名@'IP 地址';

• 为用户授予权限

GRANT ALL ON *.* TO 用户名@'IP 地址';

这条语句主要用来给用户赋予所有数据库和所有表的所有操作权限，其中all表示所有权限，*.*中第一个*号表示所有数据库名称，第二个*号表示所有表名称
这里的ALL表示所有权限，可以替换为具体需要授予的权限，例如grant select, insert on * . * to '用户名'@'%'表示给用户赋予所有数据库好表的查询和增加的权限

在这里可授权的权限有四种：
① all（全部权限）② select（查询权限）③ delete（删除权限）④ insert（增加权限）

• ‘%’ 表示任意 IP 地址都可以访问

# 这里也可以利用 DML 语句设置 IP 地址
UPDATE mysql.user SET host='%' WHERE user='用户名';

• 刷新用户权限

flush privileges

执行该语句可以重新加载权限表，以确保对用户和权限的更改立即生效

7. 删除用户

# 删除用户
DROP user 用户名@'IP 地址';

3. 数据库的备份

mysqldump [选项] 数据库名 [表名] > 地址

选项说明：

参数名	缩写	含义
–host	-h	主机地址
–port	-P	服务器端口号
–user	-u	MySQL 用户名
–pasword	-p	MySQL 密码
–databases	-B	指定要备份的数据库
–all-databases	-A	备份mysql服务器上的所有数据库
–no-data	-d	不备份数据，默认为备份数据
–comments	-i	是否有信息备注，默认为打开的，使用–skip-comments 关闭

1. 只备份表结构

mysqldump -u root -p -d 数据库名 表名 > 存储地址 + 文件名称.sql --例如：e:\database\data.sql

2. 备份多个数据库

mysqldump -u root -p -B 数据库1, 数据库2 > 存储地址 + 文件名称.sql
--例如：e:\database\data.sql

3. 备份数据和结构

# 就是去掉 只备份表结构语句的 -d
mysqldump -u root -p 数据库名 表名 > 存储地址 + 文件名称.sql

4. 将查询的结果集保存为文件

# 写法一
mysql -u root -p -e "select * from 数据库.表名" > 存储地址 + 文件名称.sql

# 写法二
mysql -u root -p -e "select * from 表名" 数据库名 > 存储地址 + 文件名称.sql

5. 通过备份好的文件还原数据

# 方式一，在登录数据库之前即可操作
mysql -u root -p 新数据库名 < 存储地址 + 文件名称.sql

# 方式二，在登录数据库后选中数据库，然后将数据还原到所选中的数据库中
source 存储地址 + 文件名称.sql;

4. 细节

细节一

整形数据加引号后mysql可以自己把字符串型数据转换成整型数据，例如：

SELECT * FROM table_name WHERE name_int = '20';

这样也是可以正常查询出结果的

细节二

当表名或字段名和关键字冲突时可以用符号 `` 将表名字段名进行包裹，如果查询的字段名是
select，表是table时，可以写成select和table，例如：

SELECT `select` FROM `table`

注意：在创建表的时候，字段名或者表名最好避免使用关键字

自己浅浅整理的学习笔记，如果有问题还请多多指出，感谢诸位大佬