十四、Mysql基础
十四、Mysql基础
1.Mysql简介
是什么：MySQL：就是支持 SQL 语言的数据库软件，是目前互联网行业用得最多的数据库之一，免费、开源、稳定，兼容性强。
干什么用的：帮程序 / 网站存数据、管数据、取数据
| 场景 | MySQL 在干什么 |
|---|---|
| 网站 / APP | 存用户账号、密码、订单、文章、评论，比如你刷的短视频、买的东西，数据都存在类似 MySQL 的数据库里 |
| 业务系统 | 存公司员工信息、客户信息、库存数据，比如你现在写的 employees 表，就是典型的业务数据 |
| 数据分析 | 给报表、统计工具提供数据，比如你写的 “统计部门员工数、最高薪资”，就是从 MySQL 里拿数据算出来的 |
核心能力：
1.安全存储数据：不像 Excel 容易丢、容易乱，MySQL 有事务、备份、权限控制，保证数据不被误删、篡改，比如你之前写的只读用户、事务回滚，就是在利用它的安全能力。
2.高效查询数据：你写的 WHERE 筛选、ORDER BY 排序、GROUP BY 分组，还有刚学的索引，都是为了让它在海量数据里快速找到你要的内容。 3.规范管理数据：它用 “表 - 字段 - 行” 的关系模型存数据，比如 employees 表、dept 字段，能避免数据重复、格式混乱，让所有数据都井井有条。
类别：
mysql属于关系型数据库：是建立在关系模型基础上的数据库，其借助于集合代数等数学概念和方法来处理数据库中的数据。
存储结构：
关系型数据库采用二维表格的形式存储数据，每个数据表都必须对各个字段定义好（即先定义好表的结构），再根据表的结构存入数据。数据表中的行（记录）用来描述一个对象的信息，而列（字段）则用来描述对象的一个属性。
特点：
关系型数据库强调数据的一致性和完整性，采用ACID事务模型来保证数据的安全性和稳定性。同时，关系型数据库使用结构化查询语言（SQL）进行数据查询，支持复杂的查询和分析。
2. 基本概念
-
数据库：是存储数据的容器，通常包含多个数据表。
-
数据表：是数据库中存储数据的结构化形式，由行和列组成。
-
行（记录）：数据表中的一行代表一个实体对象的信息。
-
列（字段）：数据表中的一列代表实体对象的一个属性。
3.常用数据类型
-
int：整型，分为无符号和有符号两种。无符号的范围是 [0, 2^32-1]，有符号的范围是 [-2^31, 2^31-1]。
-
float：单精度浮点类型，占用4字节（32位）。
-
double：双精度浮点类型，占用8字节（64位）。
-
char：固定长度的字符类型，定义时指定长度，存储时会用空格填充至指定长度。
-
varchar：可变长度的字符类型，定义时指定最大长度，存储时仅占用实际字符长度加上额外的长度标识字节。
-
text：用于存储大文本数据。
-
image：用于存储图片数据，但在现代数据库中，通常使用BLOB类型来存储二进制数据。
-
decimal(p, s)：定点数类型，用于存储精确的小数。p表示总的有效数字位数，s表示小数点后的位数。例如，decimal(5,2)表示最多5位有效数字，其中2位在小数点后。
4.查看数据库结构
# 查看MySQL版本信息和连接用户名 SELECT VERSION(); SELECT USER(); # 查看当前服务器中的数据库 SHOW DATABASES;
MySQL会附带几个系统数据库，包括：
-
information_schema：存储关于数据库对象的信息，如数据库名、表、表字段的数据类型与访问权限等。
-
performance_schema：存储数据库服务器的性能参数信息。
-
mysql：存储系统的用户权限信息。
-
sys（MySQL 5.7及之后）：提供快速了解MySQL信息的视图、函数和存储过程，数据来源于performance_schema。
4.1 查看数据库中包含的表
# 选择目标数据库 USE 数据库名; # 查看该数据库中的表 SHOW TABLES;
4.2 查看表的结构（字段）
# 首先选择目标数据库 USE 数据库名; # 查看表的结构 DESCRIBE 表名; 举例：describe employees; # 或简写为 DESC 表名;
4.3 详细讲解 DESCRIBE 语句
DESCRIBE 语句用于显示表的字段信息，包括：
-
Field：字段名。
-
Type：字段的数据类型。
-
Null：字段是否允许为空（YES/NO）。
-
Key：字段是否被索引（PRI表示主键，UNI表示唯一索引，MUL表示非唯一索引）。
-
Default：字段的默认值。
-
Extra：额外的信息，如auto_increment（自增）。 例如，对于一个名为
students的表，DESCRIBE students;可能会返回如下信息：
+-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(255) | YES | | NULL | | | age | int(11) | YES | | NULL | | | major | varchar(255) | YES | | NULL | | +-------------+--------------+------+-----+---------+----------------+
这表示 students 表有四个字段：id（整型，主键，自增），name（可变长度字符型，允许为空），age（整型，允许为空），major（可变长度字符型，允许为空）。
5.数据库查询语言（SQL语句）
SQL（Structured Query Language）是用于管理关系型数据库的标准化语言。它分为四种基本操作类型：DDL、DML、DQL和DCL。
-
DDL（Data Definition Language，数据定义语言）：用于定义数据库结构和对象，如创建表、修改表结构、删除表等。常见的DDL语句包括CREATE、ALTER和DROP等。
-
DML（Data Manipulation Language，数据操作语言）：用于操作数据库中的数据，如插入、更新、删除数据等。常见的DML语句包括INSERT、UPDATE和DELETE等。
-
DQL（Data Query Language，数据查询语言）：用于从数据库中查询数据。常见的DQL语句是SELECT语句，用于从表中检索数据。
-
DCL（Data Control Language，数据控制语言）：用于授权、回滚事务等操作。常见的DCL语句包括GRANT、REVOKE、COMMIT和ROLLBACK等。
5.1 DDL 数据定义语言 用于管理数据库对象（库、表、索引等）
# 创建数据库 CREATE DATABASE 数据库名; 实例：创建数据库 `company_db` create database company_db; # 删除数据库 DROP DATABASE 数据库名; # 创建表 CREATE TABLE 表名 ( 字段1 数据类型, 字段2 数据类型[,...][,PRIMARY KEY (主键名)] ); 实例：创建员工表 `employees`，字段interest如下： - `emp_id` INT 主键 - `emp_name` VARCHAR(50) 非空 - `dept` VARCHAR(30) 非空 - `salary` DECIMAL(10,2) - `hire_date` DATE create table employees(emp_id INT PRIMARY KEY, -> emp_name varchar(50) not null, -> dept varchar(30) not null, -> salary decimal(10,2), -> hire_date date); # 删除表 DROP TABLE [数据库名.]表名; # 修改表名 ALTER TABLE 旧表名 RENAME 新表名; # 扩展表结构（增加字段） ALTER TABLE 表名 ADD 字段名 数据类型 [default 默认值]; # 修改字段名或添加唯一键 ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [unique key]; # 删除字段 ALTER TABLE 表名 DROP 字段名; # 添加主键约束 ALTER TABLE 表名 ADD PRIMARY KEY (主键名); # 添加外键约束 ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY (外键字段) REFERENCES 主表名 (主键字段); # 删除外键约束 ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
5.2 DML 数据操纵语言 用于对表中的数据进行管理
# 插入数据 INSERT INTO 表名(字段1,字段2[,...]) VALUES(字段1的值,字段2的值,...); 实例：插入5条测试数据 insert into employees (emp_id, emp_name, dept, salary, hire_date) values (1, 'Tom Smith', 'IT', 8000.00, '2022-03-15'), (2, 'Jane Doe', 'Sales', 6000.00, '2021-07-22'), (3, 'Bob Johnson', 'IT', 7500.00, '2023-01-10'), (4, 'Alice Wang', 'HR', 5500.00, '2020-11-05'), (5, 'Mike Chen', 'Sales', 6500.00, '2023-08-30'); # 更新数据 UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 条件表达式]; # 删除数据 DELETE FROM 表名 [WHERE 条件表达式]; # 清空表（DELETE方法）：单纯删除表数据 DELETE FROM 表名; # 清空表（TRUNCATE方法）：相当于把表重新删除但不删除表名（不受自增长影响） TRUNCATE TABLE 表名;
实例：
# 为所有IT部门员工加薪10% update employees set salary = salary * 1.10 where dept = 'IT'; # 将Jane Doe的部门改为"Marketing" update employees set dept = 'Marketing' where emp_name = 'Jane Doe'; # 删除薪资低于6000的员工 delete from employees where salary < 6000; # 新增一个字段`phone`（VARCHAR(20)） alter table employees add column phone varchar(20);
5.3 DQL 数据查询语言 用于从数据表中查找符合条件的数据记录
# 查询数据 SELECT 字段名1,字段名2[,...] FROM 表名 [WHERE 条件表达式]; # 查询所有数据 SELECT * FROM 表名; # 以列表方式竖向显示 SELECT 字段名 FROM 表名\G; # 限制查询结果数量 SELECT * FROM 表名 LIMIT 行数; SELECT * FROM 表名 LIMIT 偏移量,行数;
实例：
# 查询所有IT部门的员工 select emp_name from employees where dept = 'IT'; +-------------+------+ | emp_name | dept | +-------------+------+ | Tom Smith | IT | | Bob Johnson | IT | +-------------+------+ 2 rows in set (0.00 sec) # 查询薪资高于7000的员工姓名和部门 select emp_name,dept from employees where salary>7000; +-------------+------+ | emp_name | dept | +-------------+------+ | Tom Smith | IT | | Bob Johnson | IT | +-------------+------+ 2 rows in set (0.00 sec) # 查询2023年入职的员工 select emp_name,hire_date from employees where hire_date between '2023-01-01' and '2023-12-31'; +-------------+------------+ | emp_name | hire_date | +-------------+------------+ | Bob Johnson | 2023-01-10 | | Mike Chen | 2023-08-30 | +-------------+------------+ 2 rows in set (0.00 sec) # 按薪资降序排列所有员工 select emp_name from employees order by salary desc; +-------------+ | emp_name | +-------------+ | Tom Smith | | Bob Johnson | | Mike Chen | | Jane Doe | | Alice Wang | +-------------+ 5 rows in set (0.00 sec) 注： order by salary → 按工资排序 desc → 降序（从大到小) # 查询各部门的平均薪资 select dept, round(avg(salary),2) as 部门平均薪资 from employees group by dept; +-------+--------------------+ | dept | 部门平均薪资 | +-------+--------------------+ | HR | 5500.00 | | IT | 7750.00 | | Sales | 6250.00 | +-------+--------------------+ 3 rows in set (0.00 sec) 注： AVG() 是 MySQL 的聚合函数，专门用来计算平均值 AS 平均薪资 是给结果列起个别名，查询结果会更清晰 ROUND(..., 2) 是把结果保留 2 位小数，避免出现很长的小数。 # 查询薪资在6000到8000之间的员工 select emp_name from employees where salary between 6000 and 8000; +-----------+ | emp_name | +-----------+ | Jane Doe | | Mike Chen | +-----------+ 2 rows in set (0.00 sec) # 统计每个部门的员工数量 select dept, count(*) as 员工数量 from employees group by dept; +-----------+--------------+ | dept | 员工数量 | +-----------+--------------+ | IT | 2 | | Marketing | 1 | | Sales | 1 | +-----------+--------------+ 3 rows in set (0.00 sec) 注： COUNT(*)：统计每个分组的行数（员工数） GROUP BY dept：按部门分组 # 查询薪资最高的3名员工 select emp_name,dept,salary from employees order by salary desc limit 3; +-------------+-------+---------+ | emp_name | dept | salary | +-------------+-------+---------+ | Tom Smith | IT | 8800.00 | | Bob Johnson | IT | 8250.00 | | Mike Chen | Sales | 6500.00 | +-------------+-------+---------+ 3 rows in set (0.00 sec) 注： ORDER BY salary DESC：按薪资从高到低排序 LIMIT 3：只取前 3 条结果 额外： limit # 查询所有员工，按部门分组，再按薪资降序排列 select emp_name,dept,salary from employees order by dept asc, salary desc; +-------------+-----------+---------+ | emp_name | dept | salary | +-------------+-----------+---------+ | Tom Smith | IT | 8800.00 | | Bob Johnson | IT | 8250.00 | | Jane Doe | Marketing | 6000.00 | | Mike Chen | Sales | 6500.00 | +-------------+-----------+---------+ 4 rows in set (0.00 sec) 注： ORDER BY dept ASC：先按部门升序分组（相同部门排在一起） salary DESC：每个部门内再按薪资从高到低排序
5.4 DCL 数据控制语言 用于设置或者更改数据库用户或角色权限
5.4.1 新建用户
CREATE USER '用户名'@'来源地址' [IDENTIFIED BY [PASSWORD] '密码'];
参数说明：
-
'用户名'：指定新用户的名称。 -
'来源地址'：指定用户可以从哪些主机登录，如localhost、IP地址、网段或%（任意主机）。 -
'密码'：用户的密码，可以是明文或加密后的密文。 示例：# 创建一个只读用户`readonly_user`，只能查询`employees`表 create user 'readonly_user'@'localhost' identified by '123456';
5.4.2 查看用户信息
USE mysql; SELECT User, authentication_string, Host FROM user; # 查看当前登录用户 SELECT USER();
5.4.3 重命名用户
RENAME USER '旧用户名'@'来源地址' TO '新用户名'@'来源地址'; # 示例 RENAME USER 'zhangsan'@'localhost' TO 'lisi'@'localhost';
5.4.4 删除用户
DROP USER '用户名'@'来源地址'; # 示例 DROP USER 'lisi'@'localhost';
5.4.5 修改用户密码
# 修改当前登录用户密码
SET PASSWORD = PASSWORD('新密码');
ALTER USER '用户名'@'来源地址' IDENTIFIED BY '新密码';
# 修改其他用户密码
SET PASSWORD FOR '用户名'@'来源地址' = PASSWORD('新密码');
5.4.6 忘记root密码的解决办法
-
修改配置文件
/etc/my.cnf，添加skip-grant-tables。 -
重启MySQL服务。
-
直接登录MySQL。
-
使用
UPDATE语句修改root密码。 -
刷新权限。
-
删除配置文件中的
skip-grant-tables，重启MySQL服务。
6.数据库用户授权
6.1 授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'来源地址' [IDENTIFIED BY '密码'];
参数说明：
-
权限列表：如SELECT, INSERT, UPDATE，或ALL PRIVILEGES表示所有权限。（ALL PRIVILEGES：授予所有权限。SELECT：允许读取数据。INSERT：允许插入数据。UPDATE：允许更新数据。DELETE：允许删除数据。CREATE：允许创建新的数据库和表。DROP：允许删除数据库和表。ALTER：允许修改表结构。INDEX：允许创建和删除索引。GRANT OPTION：允许用户授予或撤销其他用户的权限。） -
数据库名.表名：如mydb.*表示mydb数据库中的所有表。 -
用户名@来源地址：指定用户及其来源地址。 示例：# 给用户授予 employees 表的只读权限 grant select on employees to 'readonly_user'@'localhost'; # 刷新权限，让设置生效 FLUSH PRIVILEGES;
6.2 查看权限
SHOW GRANTS FOR '用户名'@'来源地址'; # 示例 SHOW GRANTS FOR 'lisi'@'%';
6.3 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'来源地址'; # 示例 REVOKE ALL ON *.* FROM 'lisi'@'%'; SHOW GRANTS FOR 'lisi'@'%'; FLUSH PRIVILEGES;
AtomGit 是由开放原子开源基金会联合 CSDN 等生态伙伴共同推出的新一代开源与人工智能协作平台。平台坚持“开放、中立、公益”的理念，把代码托管、模型共享、数据集托管、智能体开发体验和算力服务整合在一起，为开发者提供从开发、训练到部署的一站式体验。
更多推荐
3
0- 0
已为社区贡献2条内容
所有评论(0)