目录
数据库概念
数据库(Database)
简称DB，按照一定格式存储数据的一些文件的组合，顾名思义就是存储数据的仓库，实际上就是一堆文件。这些文件中存储了具有特定格式的数据 。数据库管理数据有诸多优点，如降低存储数据的冗余度，存储的数据可以共享，便于维护数据完整性，能够实现数据的安全性等。数据库的发展经历了萌芽、初级、中级、和高级阶段。
数据库管理系统（DateBase Management System）
简称DBMS，数据库管理系统是专门用来管理数据库中的数据的，可以对数据库当中的数据进行增、删、改、查 等操作，常见的数据库管理系统：MySql、Oracle、MS SQLServer、DB2、sysbase等…
SQL（Structured Query Language）
结构化查询语言，针对关系型数据库的一种语言；SQL 是一种操作数据库的语言，包括创建数据库、删除数据库、查询记录、修改记录、添加字段等。SQL在MySql中可以使用，同时在Oracle中也可以使用，在DB2中也可以使用 。
数据库类型
关系型数据库
MariaDB/MySQL
Oracle
DB2
MS SQLServer 只能用于windows系统
达梦
人大金仓
TIDB 分布式关系型数据库
NOSQL数据库
redis 缓存数据库
MongoDB 文档型 数据库
关系型数据库模型
层次模型
网状模型
关系模型
关系模型的相关概念
实体：在数据库中，实体通常对应于表中的行，而表则实体的一种集合。例如，在一个学生信息管理系统中，学生、课程和教师都可以被视为实体。
属性：在数据库中，属性通常对应于表中的列，每列代表实体的一种属性。例如，学生的姓名、学号和年龄都是学生的属性。
联系：在数据库中，联系通过关系表来实现，这个表通常包含两个或更多的实体作为表的列。
在关系模型中，实体和联系通常通过以下三种类型来进一步分类：
一对一：这种联系表示两个实体之间存在一对一的关系，即每个实体只有一个相关的实体，反之亦然。
一对多：这种联系表示一个实体与多个实体相关，但每个相关实体只与一个实体相关。
多对多：这种联系表示两个实体之间存在多对多的关系，即每个实体可以与多个实体相关，同时每个相关实体也可以与多个实体相关。
关系数据库相关概念
库 database：库是一个存储数据的容器，它可以包含多个数据库。在某些管理系统中，库等同于数据库。
表 table： 表是数据库中的一个表格，由行和列组成。表是存储数据的主要结构，每个表通常对应一个实体类型。表的列名称为属性，而表的行名称为记录。
行 record：行也称为记录，它是表中的一个单元，代表表中的一个具体实例。每一行包含了一组属性值，这些值共同描述了一个实体的状态。
列 column： 列是表中的一列，它代表了表的一个属性。每列都有一个数据类型，用于定义存储在其中的数据的种类和格式。
字段 field： 字段通常指的是表中的行与列的交叉点，它存储了单个数据项。在数据库中，每个字段都有其特定的数据类型和用途。
数据 data：数据是存储在数据库中的信息。它可以是文本、数字、日期、图像、声音等各种形式。数据是数据库管理和操作的核心。
mysqld服务器的启动脚本
[root@web1 ~]# cd /usr/lib/systemd/
[root@web1 systemd]# vi mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
ExecStartPre=/usr/bin/mysqld_pre_systemd
Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp=false服务启动时读取的默认配置文件: /etc/my.cnf
mysql 命令
mysql 直接登录时等同于 mysql -uroot -hlocalhost -P3306
[root@web1 my.cnf.d]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.42 Source distribution
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> exit
Bye
[root@web1 my.cnf.d]# mysql -uroot -hlocalhost -P3306
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.42 Source distribution
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> exit
Bye
直接在外部执行命令
[root@web1 ~]# mysql -e 'show databases'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+mysqladmin（超级管理命令）
设置root密码：
[root@web1 ~]# mysqladmin -uroot password '123.com'默认数据库
information_schema:信息数据库，存储所有的库、表、列的名称，任意可登录数据库的用户都可读；sql注入
mysql：主数据库，mysql运行的必须数据库，用户配置与信息
performance_schema：性能数据库，存储mysqld的资源使用、安全策略权限配置信息
sys库：官方提供的辅助工具库，主要作用是简化数据库性能分析、监控和管理
sql语言
create：创建用户、库、表
alter：改变
drop：删除
insert：插入
updata：更新
delete：删除数据
grant：用户赋权
revoke：移除用户权限
select：查询
mysql数据类型
整型
浮点型
BIT类型
定点数
日期时间类型
字符串
NULL类型
数据库操作命令
查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)查看所在数据库
select database();
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)
查看当前登录用户
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
查看当前数据库版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.42 |
+-----------+
1 row in set (0.00 sec)查看用户权限列表
mysql> show privileges\G;
*************************** 1. row ***************************
Privilege: Alter
Context: Tables
Comment: To alter the table
*************************** 2. row ***************************
Privilege: Alter routine
Context: Functions,Procedures
Comment: To alter or drop stored functions/procedures
*************************** 3. row ***************************
Privilege: Create
Context: Databases,Tables,Indexes
Comment: To create new databases and tables
*************************** 4. row ***************************
Privilege: Create routine
Context: Databases
Comment: To use CREATE FUNCTION/PROCEDURE
*************************** 5. row ***************************
Privilege: Create role
Context: Server Admin
Comment: To create new roles
*************************** 6. row ***************************
Privilege: Create temporary tables
Context: Databases
Comment: To use CREATE TEMPORARY TABLE
*************************** 7. row ***************************
Privilege: Create view
Context: Tables
Comment: To create new views
*************************** 8. row ***************************
Privilege: Create user
Context: Server Admin
Comment: To create new users
*************************** 9. row ***************************
Privilege: Delete
Context: Tables
Comment: To delete existing rows
*************************** 10. row ***************************
Privilege: Drop
Context: Databases,Tables
Comment: To drop databases, tables, and views
*************************** 11. row ***************************
Privilege: Drop role
Context: Server Admin
Comment: To drop roles
*************************** 12. row ***************************
Privilege: Event
Context: Server Admin
Comment: To create, alter, drop and execute events
*************************** 13. row ***************************
Privilege: Execute
Context: Functions,Procedures
Comment: To execute stored routines
*************************** 14. row ***************************
Privilege: File
Context: File access on server
Comment: To read and write files on the server
*************************** 15. row ***************************
Privilege: Grant option
Context: Databases,Tables,Functions,Procedures
Comment: To give to other users those privileges you possess
*************************** 16. row ***************************
Privilege: Index
Context: Tables
Comment: To create or drop indexes
*************************** 17. row ***************************
Privilege: Insert
Context: Tables
Comment: To insert data into tables
*************************** 18. row ***************************
Privilege: Lock tables
Context: Databases
Comment: To use LOCK TABLES (together with SELECT privilege)
*************************** 19. row ***************************
Privilege: Process
Context: Server Admin
Comment: To view the plain text of currently executing queries
*************************** 20. row ***************************
Privilege: Proxy
Context: Server Admin
Comment: To make proxy user possible
*************************** 21. row ***************************
Privilege: References
Context: Databases,Tables
Comment: To have references on tables
*************************** 22. row ***************************
Privilege: Reload
Context: Server Admin
Comment: To reload or refresh tables, logs and privileges
*************************** 23. row ***************************
Privilege: Replication client
Context: Server Admin
Comment: To ask where the slave or master servers are
*************************** 24. row ***************************
Privilege: Replication slave
Context: Server Admin
Comment: To read binary log events from the master
*************************** 25. row ***************************
Privilege: Select
Context: Tables
Comment: To retrieve rows from table
*************************** 26. row ***************************
Privilege: Show databases
Context: Server Admin
Comment: To see all databases with SHOW DATABASES
*************************** 27. row ***************************
Privilege: Show view
Context: Tables
Comment: To see views with SHOW CREATE VIEW
*************************** 28. row ***************************
Privilege: Shutdown
Context: Server Admin
Comment: To shut down the server
*************************** 29. row ***************************
Privilege: Super
Context: Server Admin
Comment: To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.
*************************** 30. row ***************************
Privilege: Trigger
Context: Tables
Comment: To use triggers
*************************** 31. row ***************************
Privilege: Create tablespace
Context: Server Admin
Comment: To create/alter/drop tablespaces
*************************** 32. row ***************************
Privilege: Update
Context: Tables
Comment: To update existing rows
*************************** 33. row ***************************
Privilege: Usage
Context: Server Admin
Comment: No privileges - allow connect only
*************************** 34. row ***************************
Privilege: XA_RECOVER_ADMIN
Context: Server Admin
Comment:
*************************** 35. row ***************************
Privilege: TELEMETRY_LOG_ADMIN
Context: Server Admin
Comment:
*************************** 36. row ***************************
Privilege: TABLE_ENCRYPTION_ADMIN
Context: Server Admin
Comment:
*************************** 37. row ***************************
Privilege: SHOW_ROUTINE
Context: Server Admin
Comment:
*************************** 38. row ***************************
Privilege: SET_USER_ID
Context: Server Admin
Comment:
*************************** 39. row ***************************
Privilege: SERVICE_CONNECTION_ADMIN
Context: Server Admin
Comment:
*************************** 40. row ***************************
Privilege: SENSITIVE_VARIABLES_OBSERVER
Context: Server Admin
Comment:
*************************** 41. row ***************************
Privilege: GROUP_REPLICATION_STREAM
Context: Server Admin
Comment:
*************************** 42. row ***************************
Privilege: REPLICATION_SLAVE_ADMIN
Context: Server Admin
Comment:
*************************** 43. row ***************************
Privilege: APPLICATION_PASSWORD_ADMIN
Context: Server Admin
Comment:
*************************** 44. row ***************************
Privilege: SESSION_VARIABLES_ADMIN
Context: Server Admin
Comment:
*************************** 45. row ***************************
Privilege: CLONE_ADMIN
Context: Server Admin
Comment:
*************************** 46. row ***************************
Privilege: CONNECTION_ADMIN
Context: Server Admin
Comment:
*************************** 47. row ***************************
Privilege: SYSTEM_VARIABLES_ADMIN
Context: Server Admin
Comment:
*************************** 48. row ***************************
Privilege: BACKUP_ADMIN
Context: Server Admin
Comment:
*************************** 49. row ***************************
Privilege: AUTHENTICATION_POLICY_ADMIN
Context: Server Admin
Comment:
*************************** 50. row ***************************
Privilege: REPLICATION_APPLIER
Context: Server Admin
Comment:
*************************** 51. row ***************************
Privilege: RESOURCE_GROUP_ADMIN
Context: Server Admin
Comment:
*************************** 52. row ***************************
Privilege: SYSTEM_USER
Context: Server Admin
Comment:
*************************** 53. row ***************************
Privilege: FIREWALL_EXEMPT
Context: Server Admin
Comment:
*************************** 54. row ***************************
Privilege: AUDIT_ABORT_EXEMPT
Context: Server Admin
Comment:
*************************** 55. row ***************************
Privilege: BINLOG_ENCRYPTION_ADMIN
Context: Server Admin
Comment:
*************************** 56. row ***************************
Privilege: INNODB_REDO_LOG_ARCHIVE
Context: Server Admin
Comment:
*************************** 57. row ***************************
Privilege: ENCRYPTION_KEY_ADMIN
Context: Server Admin
Comment:
*************************** 58. row ***************************
Privilege: PERSIST_RO_VARIABLES_ADMIN
Context: Server Admin
Comment:
*************************** 59. row ***************************
Privilege: BINLOG_ADMIN
Context: Server Admin
Comment:
*************************** 60. row ***************************
Privilege: FLUSH_OPTIMIZER_COSTS
Context: Server Admin
Comment:
*************************** 61. row ***************************
Privilege: FLUSH_STATUS
Context: Server Admin
Comment:
*************************** 62. row ***************************
Privilege: FLUSH_TABLES
Context: Server Admin
Comment:
*************************** 63. row ***************************
Privilege: AUDIT_ADMIN
Context: Server Admin
Comment:
*************************** 64. row ***************************
Privilege: FLUSH_USER_RESOURCES
Context: Server Admin
Comment:
*************************** 65. row ***************************
Privilege: GROUP_REPLICATION_ADMIN
Context: Server Admin
Comment:
*************************** 66. row ***************************
Privilege: INNODB_REDO_LOG_ENABLE
Context: Server Admin
Comment:
*************************** 67. row ***************************
Privilege: PASSWORDLESS_USER_ADMIN
Context: Server Admin
Comment:
*************************** 68. row ***************************
Privilege: ROLE_ADMIN
Context: Server Admin
Comment:
*************************** 69. row ***************************
Privilege: RESOURCE_GROUP_USER
Context: Server Admin
Comment:
69 rows in set (0.00 sec)查看指定用户的权限
mysql> show grants for root@localhost\G;
*************************** 1. row ***************************
Grants for root@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for root@localhost: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION
*************************** 3. row ***************************
Grants for root@localhost: GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION
3 rows in set (0.00 sec)
切换数据库 use databesename
mysql> use mysql
Database changed创建数据库
mysql> create database test character set utf8;
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)删除数据库
mysql> drop database test;
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
表结构命令
查看数据表
mysql> show tables;
+------------------------------------------------------+
| Tables_in_mysql |
+------------------------------------------------------+
| columns_priv |
| component |
| db |
| default_roles |
| engine_cost |
| func |
| general_log |
| global_grants |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| password_history |
| plugin |
| procs_priv |
| proxies_priv |
| replication_asynchronous_connection_failover |
| replication_asynchronous_connection_failover_managed |
| replication_group_configuration_version |
| replication_group_member_actions |
| role_edges |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+------------------------------------------------------+
37 rows in set (0.00 sec)
查看表结构 desc tablename；
mysql> desc func;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| name | char(64) | NO | PRI | | |
| ret | tinyint | NO | | 0 | |
| dl | char(128) | NO | | | |
| type | enum('function','aggregate') | NO | | NULL | |
+-------+------------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
创建数据表 create table tableName(columnName（列名称） dataType（数据类型）, ............);
mysql> create table student(id int primary key auto_increment,name varchar(50) not null,age tinyint,gender enum('男','女'),class varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql> show tables
-> ;
+----------------+
| Tables_in_test |
+----------------+
| student |
+----------------+
1 row in set (0.00 sec)alter命令用法
alter table tablename 选项
修改表名：alter table tab_name rename to tab_new;
mysql> alter table student rename to students;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| students |
+----------------+
1 row in set (0.01 sec)
修改字段类型（修改字段的相对位置）：alter table tab_name modify field type (first/before/after field1);
mysql> alter table students modify class int;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students;
+--------+-------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| age | tinyint | YES | | NULL | |
| gender | enum('男','女') | YES | | NULL | |
| class | int | YES | | NULL | |
+--------+-------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
修改字段名 alter table students change column oldcolumnname newcolumnname newtype;
mysql> alter table students change column age nianling varchar(30);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students;
+----------+-------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| nianling | varchar(30) | YES | | NULL | |
| gender | enum('男','女') | YES | | NULL | |
| class | int | YES | | NULL | |
+----------+-------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)添加字段alter table tablename add fileld type
mysql> alter table students add class_id int;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students;
+----------+-------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| nianling | varchar(30) | YES | | NULL | |
| gender | enum('男','女') | YES | | NULL | |
| class | int | YES | | NULL | |
| class_id | int | YES | | NULL | |
+----------+-------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
删除字段 alter table tab_name drop field;
mysql> alter table students drop class_id;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students;
+----------+-------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| nianling | varchar(30) | YES | | NULL | |
| gender | enum('男','女') | YES | | NULL | |
| class | int | YES | | NULL | |
+----------+-------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
表数据操作
单行插入：insert [into] tableName[(columnName,.........)] value('value1',value2,.......);
批量插入：insert into stu value('张三', 22, '11011011011', '男'),('王无',26 , '12011011011', '女'),('里斯',27 ,'12011011099', '男');
mysql> insert students (name,nianling,gender,class) value('张三',17,'男','2501');
Query OK, 1 row affected (0.00 sec)
mysql> select * from students;
+----+--------+----------+--------+-------+
| id | name | nianling | gender | class |
+----+--------+----------+--------+-------+
| 1 | 张三 | 17 | 男 | 2501 |
+----+--------+----------+--------+-------+
1 row in set (0.00 sec)
mysql> insert into students( name,nianling,gender,class) value('李四',18,'女',2502),('王五',19,'男',2503),('赵六',19,'女',2504);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from students;
+----+--------+----------+--------+-------+
| id | name | nianling | gender | class |
+----+--------+----------+--------+-------+
| 1 | 张三 | 17 | 男 | 2501 |
| 2 | 李四 | 18 | 女 | 2502 |
| 3 | 王五 | 19 | 男 | 2503 |
| 4 | 赵六 | 19 | 女 | 2504 |
+----+--------+----------+--------+-------+
4 rows in set (0.00 sec)
where字句运算符
比较运算符
| 运算符 | 说明 |
|---|---|
| >, >=, <, <= | 大于，大于等于，小于，小于等于 |
| = | 等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL |
| <=> | 等于，NULL 安全，例如 NULL <=> NULL 的结果是 TRUE(1) |
| !=, <> | 不等于 |
| BETWEEN a0 AND a1 | 范围匹配，[a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1) |
| IN (option, …) | 如果是 option 中的任意一个，返回 TRUE(1) |
| IS NULL | 是 NULL |
| IS NOT NULL | 不是 NULL |
| LIKE | 模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符 |
逻辑运算符
| 运算符 | 说明 |
|---|---|
| AND （&&） | 多个条件必须都为 TRUE(1)，结果才是 TRUE(1) |
| OR （||） | 任意一个条件为 TRUE(1), 结果为 TRUE(1) |
| NOT （！） | 条件为 TRUE(1)，结果为 FALSE(0) |
更新数据
update tablename set columnname=newvalue;
使所有年龄加1
mysql> update students set nianling = nianling + 1 ;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> select * from students;
+----+--------+----------+--------+-------+
| id | name | nianling | gender | class |
+----+--------+----------+--------+-------+
| 1 | 张三 | 18 | 男 | 2501 |
| 2 | 李四 | 19 | 女 | 2502 |
| 3 | 王五 | 20 | 男 | 2503 |
| 4 | 赵六 | 20 | 女 | 2504 |
+----+--------+----------+--------+-------+
4 rows in set (0.00 sec)
删除数据
全表删除
delete from tableName;条件删除
delete from tableName where columnName=currentValue;查询数据
全表查询
select * from tableName;
mysql> select * from students;
+----+--------+----------+--------+-------+
| id | name | nianling | gender | class |
+----+--------+----------+--------+-------+
| 1 | 张三 | 18 | 男 | 2501 |
| 2 | 李四 | 19 | 女 | 2502 |
| 3 | 王五 | 20 | 男 | 2503 |
| 4 | 赵六 | 20 | 女 | 2504 |
+----+--------+----------+--------+-------+
4 rows in set (0.00 sec)
统计查询，非空数据 count()
mysql> select count(name) from students;
+-------------+
| count(name) |
+-------------+
| 4 |
+-------------+
1 row in set (0.00 sec)
重新定义查询结果表中的列名称 as
mysql> select count(name) count from students;
+-------+
| count |
+-------+
| 4 |
+-------+
1 row in set (0.00 sec)
聚合函数查询
#sum()：计算和值 计算总年龄
mysql> select sum(nianling) '总年龄' from students;
+-----------+
| 总年龄 |
+-----------+
| 77 |
+-----------+
1 row in set (0.00 sec)
#avg()：计算平均值
mysql> select avg(nianling) '均年龄' from students;
+-----------+
| 均年龄 |
+-----------+
| 19.25 |
+-----------+
1 row in set (0.00 sec)
#max()：最大值
mysql> select max(nianling) '最大年龄' from students;
+--------------+
| 最大年龄 |
+--------------+
| 20 |
+--------------+
1 row in set (0.00 sec)
#min()：最小值
mysql> select min(nianling) '最小年龄' from students;
+--------------+
| 最小年龄 |
+--------------+
| 18 |
+--------------+
1 row in set (0.00 sec)分组查询
#group by
mysql> select name,class from students group by class,name;
+--------+-------+
| name | class |
+--------+-------+
| 张三 | 2501 |
| 李四 | 2502 |
| 王五 | 2503 |
| 赵六 | 2504 |
+--------+-------+
4 rows in set (0.00 sec)
分组后的条件：having
mysql> select name,class from students group by name,class having class > 2502;
+--------+-------+
| name | class |
+--------+-------+
| 王五 | 2503 |
| 赵六 | 2504 |
+--------+-------+
2 rows in set (0.00 sec)
排序查询 order by，降序查询 order by desc
mysql> select name,nianling from students order by nianling;
+--------+----------+
| name | nianling |
+--------+----------+
| 张三 | 18 |
| 李四 | 19 |
| 王五 | 20 |
| 赵六 | 20 |
+--------+----------+
4 rows in set (0.00 sec)
mysql> select name,nianling from students order by nianling desc;
+--------+----------+
| name | nianling |
+--------+----------+
| 王五 | 20 |
| 赵六 | 20 |
| 李四 | 19 |
| 张三 | 18 |
+--------+----------+
4 rows in set (0.00 sec)
分页查询 limit number 显示前number行
mysql> select name,nianling from students order by nianling desc limit 2;
+--------+----------+
| name | nianling |
+--------+----------+
| 王五 | 20 |
| 赵六 | 20 |
+--------+----------+
2 rows in set (0.00 sec)
子查询
mysql> select name,class from students where nianling in (select nianling from students where nianling = 20);
+--------+-------+
| name | class |
+--------+-------+
| 王五 | 2503 |
| 赵六 | 2504 |
+--------+-------+
2 rows in set (0.00 sec)
union联合查询
mysql> select * from students union select 12,3,4,5,6;
+----+--------+----------+--------+-------+
| id | name | nianling | gender | class |
+----+--------+----------+--------+-------+
| 1 | 张三 | 18 | 男 | 2501 |
| 2 | 李四 | 19 | 女 | 2502 |
| 3 | 王五 | 20 | 男 | 2503 |
| 4 | 赵六 | 20 | 女 | 2504 |
| 12 | 3 | 4 | 5 | 6 |
+----+--------+----------+--------+-------+
5 rows in set (0.00 sec)
多表查询
内连接查询
select A.name as name, A.school, B.job from B,A where A.name=B.name;
mysql> select * from classinfo
-> ;
+----------+-------+------+
| class_id | class | sum |
+----------+-------+------+
| 1 | 2501 | 2 |
| 2 | 2502 | 2 |
| 3 | 2503 | 4 |
| 4 | 2504 | 4 |
+----------+-------+------+
4 rows in set (0.00 sec)
mysql> select students.name,classinfo.class_id from students join classinfo on students.class=classinfo.class where classinfo.sum >+ 3;
+--------+----------+
| name | class_id |
+--------+----------+
| 王五 | 3 |
| 赵六 | 4 |
+--------+----------+
2 rows in set (0.00 sec)
外连接查询
左外链接 select A.name as nameA, A.school, B.name as nameB, B.job from B left join A on A.name=B.name;
mysql> select students.name,classinfo.sum from students left join classinfo on students.class=classinfo.class;
+--------+------+
| name | sum |
+--------+------+
| 张三 | 2 |
| 李四 | 2 |
| 王五 | 4 |
| 赵六 | 4 |
+--------+------+
4 rows in set (0.00 sec)
右外连接
mysql> select students.name,classinfo.sum from students right join classinfo on students.class=classinfo.class;
+--------+------+
| name | sum |
+--------+------+
| 张三 | 2 |
| 李四 | 2 |
| 王五 | 4 |
| 赵六 | 4 |
+--------+------+
4 rows in set (0.00 sec)
注：
在 MySQL 中，左外连接（LEFT JOIN） 和 右外连接（RIGHT JOIN） 是两种用于合并多个表数据的连接方式，它们的核心区别在于 以哪个表为主表 来保留所有记录。
主表：连接操作中保留所有记录的表。
从表：只保留匹配记录的表，未匹配的记录用 NULL 填充。
左外连接（LEFT JOIN）
主表：左表（FROM 后面的第一个表）。
从表：右表（JOIN 后面的表）。
特点：无论右表是否有匹配记录，左表的所有记录都会被返回。右表中未匹配的字段用 NULL 填充。
mysqld用户权限管理
| user_name | host_name | 说明 |
|---|---|---|
| zhx | 198.51.100.166 | zhx，只能从此ip连接 |
| zhx | 198.51.100.% | zhx，从192.168.115子网中的任何主机 |
| zhx | % | zhx，任何主机可连 |
创建远程管理用户 并赋权所有权限
mysql> create user wang@'192.168.44.%' identified by '1';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all on *.* to wang@'192.168.44.%';
Query OK, 0 rows affected (0.00 sec)
alter命令更改用户密码
mysql> alter user wang@'192.168.44.%' identified by '2';
Query OK, 0 rows affected (0.00 sec)
删除用户权限
mysql> revoke all on *.* from wang@'192.168.44.%'
-> ;
Query OK, 0 rows affected (0.00 sec)mysqldump备份
完整备份
所有数据库的备份：mysqldump --all-databases -uroot > all.sql mysql -uroot < all.sql
指定数据库的备份 ：mysqldump --databases jx -uroot > jx.sql mysql -uroot < jx.sql
指定数据表的备份：mysqldump jx stu > jx.stu.sql mysql -uroot jx< jx.stu.sql
[root@web1 ~]# mysqldump --database test -uroot > ./backup
mysqldump: [ERROR] unknown option '--database'.
[root@web1 ~]# mysqldump --databases test -uroot > ./backup
[root@web1 ~]# ls
anaconda-ks.cfg backup sh
[root@web1 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.42 Source distribution
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> drop database test;
Query OK, 2 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> exit
Bye
[root@web1 ~]# mysql -uroot < backup
[root@web1 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.42 Source distribution
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)增量备份
配置过程
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format="statement"
[root@web1 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.42 Source distribution
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 157 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
[root@web1 ~]# cd /va/lib/mysql
-bash: cd: /va/lib/mysql: No such file or directory
[root@web1 ~]# cd /var/lib/mysql
[root@web1 mysql]# ls
auto.cnf ca-key.pem '#ib_16384_1.dblwr' '#innodb_temp' mysql.sock performance_schema sys
binlog.000001 ca.pem ib_buffer_pool mysql mysql.sock.lock private_key.pem test
binlog.000002 client-cert.pem ibdata1 mysql-bin.000001 mysql_upgrade_info public_key.pem undo_001
binlog.000003 client-key.pem ibtmp1 mysql-bin.index mysqlx.sock server-cert.pem undo_002
binlog.index '#ib_16384_0.dblwr' '#innodb_redo' mysql.ibd mysqlx.sock.lock server-key.pem
数据恢复
基于位置
#基于开始位置
mysqlbinlog --start-position="245" mysql-bin.000003 | mysql -uroot
#基于结束位置
mysqlbinlog --stop-position="632" mysql-bin.000003 | mysql -uroot
#基于中间位置
mysqlbinlog --start-position="245" --stop-position="632" mysql-bin.000003 | mysql -uroot基于时间
#基于开始时间
mysqlbinlog --start-datetime="2022-12-23 16:17:59" mysql-bin.000003 | mysql -uroot
#基于结束时间
mysqlbinlog --stop-datetime="2022-12-23 16:17:59" mysql-bin.000003 | mysql -uroot
#基于中间时间
mysqlbinlog --start-datetime="2022-12-23 16:17:59" --stop-datetime="2022-12-23 16:17:59" mysql-bin.000003 | mysql -uroot