mysql

最新推荐文章于 2024-02-20 14:55:48 发布
最新推荐文章于 2024-02-20 14:55:48 发布
阅读量139 收藏
点赞数
分类专栏： 个人笔记
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/panqicofco/article/details/84564453
版权

安装方式：

1、源代码：编译安装

2、二进制格式：展开至特定路径，并经简单配置后使用

3、程序包管理器管理的程序包

 

二进制安装：


内连接：

左外连接：

left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。

左(外)连接，左表的记录将会全部表示出来，而右表只会显示符合搜索条件的记录。右表记录不足的地方均为NULL

右外连接：

right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。

与左(外)连接相反，右(外)连接，左表只会显示符合搜索条件的记录，而右表的记录将会全部表示出来。左表记录不足的地方均为NULL

完全外连接：

UNION 用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行。
UNION 内部的 SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型。
同时，每条 SELECT 语句中的列的顺序必须相同.
SQL UNION 语法：
sql脚本代码如下:

1

2

3

SELECT column_name FROM table1

UNION

SELECT column_name FROM table2

默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行

union:联合的意思，即把两次或多次查询结果合并起来。
要求：两次查询的列数必须一致

SQL UNION ALL 语法
sql脚本代码如下:

1

2

3

SELECT column_name FROM table1

UNION ALL

SELECT column_name FROM table2

另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同

执行流程：

MariaDB [hellodb]> select classid,count(*) from students where classid is not null group by classid;                                     
+---------+----------+
| classid | count(*) |
+---------+----------+
|       1 |        4 |
|       2 |        3 |
|       3 |        4 |
|       4 |        4 |
|       5 |        1 |
|       6 |        4 |
|       7 |        3 |
+---------+----------+
7 rows in set (0.00 sec)

MariaDB [hellodb]> select gender,sum(stuage) from students group by gender;                                                              
+--------+-------------+
| gender | sum(stuage) |
+--------+-------------+
| M      |         495 |
| F      |         220 |
+--------+-------------+
2 rows in set (0.00 sec)

MariaDB [hellodb]> select classid,avg(stuage) from students where classid is not null group by classid having avg(stuage) > 25;          
+---------+-------------+
| classid | avg(stuage) |
+---------+-------------+
|       2 |     36.0000 |
|       5 |     46.0000 |
+---------+-------------+
2 rows in set (0.00 sec)

MariaDB [hellodb]> select gender,sum(stuage) from ( select classid,stuage,gender from students) as s where s.stuage > 25 group by gender;
+--------+-------------+
| gender | sum(stuage) |
+--------+-------------+
| M      |         317 |
| F      |          30 |
+--------+-------------+
2 rows in set (0.00 sec)

MariaDB [mysql]> set password for pqq@'192.168.1.166'=password('140903');
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> update mysql.user set password=password('100908') where user='root';      
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

MariaDB [mysql]> grant select,update on hellodb.students to pqqpqq@'192.168.1.%';
Query OK, 0 rows affected (0.01 sec)

MariaDB [mysql]> grant all on *.* to pqq@'192.168.1.166';                                   
Query OK, 0 rows affected (0.01 sec)

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> revoke select,update on hellodb.students from pqqpqq@'192.168.1.%';
Query OK, 0 rows affected (0.00 sec)     取消先前所赋权限

MariaDB [(none)]> flush privileges;                                                  
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show grants for pqqpqq@'192.168.1.%';                              
+--------------------------------------------------------------------------------------------------------------------------+
| Grants for pqqpqq@192.168.1.%                                                                                            |
+--------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'pqqpqq'@'192.168.1.%' IDENTIFIED BY PASSWORD '*00DE453221329E3C1A54CFB834B6488BFFC5633C' |
+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

查询缓存

 

MariaDB [(none)]> show variables like 'query_cache_min_res_unit';  
#查询缓存中内存块的最小分配单位，默认4k
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| query_cache_min_res_unit | 4096  |
+--------------------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> show variables like 'query_cache_type'; #是否开启缓存
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | ON    |
+------------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> show global status like 'Qcache%';  #查询缓存相关状态
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 33536824 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 1        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

MariaDB [(none)]> show variables like 'query_cache_size';
#查询缓存总共可用的内存空间；单位字节，必须是1024的整数倍，最小值40KB，低于此值有警报
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_cache_size | 33554432 |
+------------------+----------+
1 row in set (0.00 sec)

MariaDB [hellodb]> select name from students where stuid=2;
+------------+
| name       |
+------------+
| Shi Potian |
+------------+
1 row in set (0.01 sec)

MariaDB [hellodb]> show global status like 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 33535288 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 1        |   #将查询记录插入  此值加1 
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 2        |
| Qcache_queries_in_cache | 1        |
| Qcache_total_blocks     | 4        |
+-------------------------+----------+
8 rows in set (0.00 sec)

MariaDB [hellodb]> select name from students where stuid=2;
+------------+
| name       |
+------------+
| Shi Potian |
+------------+
1 row in set (0.00 sec)

MariaDB [hellodb]> show global status like 'Qcache%';      
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 33535288 |
| Qcache_hits             | 1        |   #这条查询命中缓存，此值加1
| Qcache_inserts          | 1        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 2        |
| Qcache_queries_in_cache | 1        |
| Qcache_total_blocks     | 4        |
+-------------------------+----------+
8 rows in set (0.00 sec)

命中率计算  4/(24+4)

1、单进程多线程

2、mysql协议连接到服务端

3、先看看缓存中有没有，有就直接返回

   那么查询这个操作就结束了，一般来说查询的命中率有3成就可以了

   不过有时可能命中只有一成，但那一成是一个非常复杂的查询命令也是可以的

   判断好坏要以开销比（资源消耗）

4、没有命中的话

5、解析器，语法分析

6、有时需要预处理器的配合，生成解析树

   生成多条路可走（那应该走哪条，理论上是语句最简单的）

7、应该走开销最小的路径（但未必是语句最简单的（堵））

8、那谁来选择走——查询优化器（它还能可能会改写）

9、然后给执行计划，因为有多个线程，但引擎只有一个，要有队列

10、查询执行引擎执行——转换成对应的存储引擎的API调用

11、对应的存储引擎到磁盘上获取数据，再返还给执行引擎，再返还给客户端

12、同时判断下是否需要缓存

MariaDB [hellodb]> rename table coc to coc111;  #改表名
Query OK, 0 rows affected (0.01 sec)

MariaDB [hellodb]> alter table coc111 engine=MyISAM;  #改引擎
Query OK, 14 rows affected (0.02 sec)              
Records: 14  Duplicates: 0  Warnings: 0

MariaDB [hellodb]> show table status like 'coc111'\G   #查看表状态
*************************** 1. row ***************************
           Name: coc111
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 14
 Avg_row_length: 8
    Data_length: 112
Max_data_length: 2251799813685247
   Index_length: 2048
      Data_free: 0
 Auto_increment: 15
    Create_time: 2018-11-28 21:46:27
    Update_time: 2018-11-28 21:46:27
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

MariaDB [hellodb]> alter table students add index(Name);   #增加索引
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [hellodb]> show indexes from students;  #查看索引
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students |          0 | PRIMARY  |            1 | StuID       | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |
| students |          1 | Name     |            1 | Name        | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

MariaDB [hellodb]> alter table students drop Name;     #删除索引
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [hellodb]> alter table students modify gender ENUM('M','F') not null;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0           #修改字段属性

MariaDB [hellodb]> alter table students change Age StuAge tinyint(3) unsigned;
Query OK, 0 rows affected (0.03 sec)             #修改字段名
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [hellodb]> desc students;                         
+-----------+---------------------+------+-----+---------+----------------+
| Field     | Type                | Null | Key | Default | Extra          |
+-----------+---------------------+------+-----+---------+----------------+
| StuID     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| StuAge    | tinyint(3) unsigned | YES  |     | NULL    |                |
| gender    | enum('M','F')       | NO   |     | NULL    |                |
| ClassID   | tinyint(3) unsigned | YES  |     | NULL    |                |
| TeacherID | int(10) unsigned    | YES  |     | NULL    |                |
+-----------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
MariaDB [hellodb]> create table test (name varchar(50) not null,   #创建一个测试表
    -> age tinyint unsigned not null,
    -> primary key(name,age));
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> desc test;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | varchar(50)         | NO   | PRI | NULL    |       |
| age   | tinyint(3) unsigned | NO   | PRI | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

一般先第三种，再第二种

先复制结构，再复制数据

第二种方式只是复制了数据，结构没有复制

MariaDB [hellodb]> desc toc;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| ID       | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
| CourseID | smallint(5) unsigned | YES  |     | NULL    |                |
| TID      | smallint(5) unsigned | YES  |     | NULL    |                |
| phone    | tinyint(3) unsigned  | NO   |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

MariaDB [hellodb]> alter table toc modify phone bigint not null;    #修改phone字段属性   
Query OK, 0 rows affected (0.04 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [hellodb]> insert toc (ID,phone)values('9527','13160180088');
Query OK, 1 row affected (0.01 sec)

MariaDB [hellodb]> select id,phone from toc where id = '9527';
+------+-------------+
| id   | phone       |
+------+-------------+
| 9527 | 13160180088 |
+------+-------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> alter table toc add version char(8) not null after id;
Query OK, 0 rows affected (0.03 sec)        #具体在哪个字段后面
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [hellodb]> desc toc;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| ID       | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
| version  | char(8)              | NO   |     | NULL    |                |
| CourseID | smallint(5) unsigned | YES  |     | NULL    |                |
| TID      | smallint(5) unsigned | YES  |     | NULL    |                |
| phone    | bigint(20)           | NO   |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

MariaDB [hellodb]> alter table toc drop version;  #删除字段
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
MariaDB [hellodb]> select * from classes;
+---------+----------------+----------+
| ClassID | Class          | NumOfStu |
+---------+----------------+----------+
|       1 | Shaolin Pai    |       10 |
|       2 | Emei Pai       |        7 |
|       3 | QingCheng Pai  |       11 |
|       4 | Wudang Pai     |       12 |
|       5 | Riyue Shenjiao |       31 |
|       6 | Lianshan Pai   |       27 |
|       7 | Ming Jiao      |       27 |
|       8 | Xiaoyao Pai    |       15 |
+---------+----------------+----------+
8 rows in set (0.00 sec)

MariaDB [hellodb]> select class from classes where NumOfStu between 10 and 20;                    
+---------------+
| class         |
+---------------+
| Shaolin Pai   |
| QingCheng Pai |
| Wudang Pai    |
| Xiaoyao Pai   |
+---------------+
4 rows in set (0.00 sec)

MariaDB [hellodb]> select class from classes where NumOfStu in (11,13,12);    
+---------------+
| class         |
+---------------+
| QingCheng Pai |
| Wudang Pai    |
+---------------+
2 rows in set (0.00 sec)

MariaDB [hellodb]> select class from classes where class like 'M%';       
+-----------+
| class     |
+-----------+
| Ming Jiao |
+-----------+
1 row in set (0.00 sec)

ASC升序

DESC降序

MariaDB [hellodb]> select gender,sum(stuage) from students group by gender;
+--------+-------------+
| gender | sum(stuage) |     #group by 分组 以Gender统计F，M共有多少个
+--------+-------------+
| M      |         495 |
| F      |         220 |
+--------+-------------+
2 rows in set (0.00 sec)

MariaDB [hellodb]> select classid,count(stuid) from students group by classid;               
+---------+--------------+
| classid | count(stuid) |
+---------+--------------+    #以班组ID，统计各班人数	
|    NULL |            3 |
|       1 |            4 |
|       2 |            3 |
|       3 |            4 |
|       4 |            4 |
|       5 |            1 |
|       6 |            4 |
|       7 |            3 |
+---------+--------------+
8 rows in set (0.00 sec)

MariaDB [hellodb]> select classid,count(stuid) from students group by classid having count(classid) >= 4;
+---------+--------------+
| classid | count(stuid) |   #以班组ID，统计各班人数大于等4的
+---------+--------------+
|       1 |            4 |
|       3 |            4 |
|       4 |            4 |
|       6 |            4 |
+---------+--------------+
4 rows in set (0.00 sec)

MariaDB [hellodb]> select * from students limit 2,3
    -> ;                                              # 从第二行之后取三行
+-------+--------+--------+---------+-----------+
| StuID | StuAge | gender | ClassID | TeacherID |
+-------+--------+--------+---------+-----------+
|     3 |     53 | M      |       2 |        16 |
|     4 |     32 | M      |       4 |         4 |
|     5 |     26 | M      |       3 |         1 |
+-------+--------+--------+---------+-----------+
3 rows in set (0.00 sec)

MariaDB [hellodb]> select stuid as iddd from students;
+------+
| iddd |  #取别名只限于你这一次查询，并不是在数据库里永久的把这一列增加了名称
+------+
|    1 |
|    2 |
|    3 |
+------+

AVG函数求平均值

取年龄大于平均值的人


MariaDB [hellodb]> select s.stuid,s.stuage,s.gender from (select * from students where gender='M') as s where s.stuage > 25;
+-------+--------+--------+
| StuID | StuAge | gender |
+-------+--------+--------+
|     3 |     53 | M      |
|     4 |     32 | M      |
|     5 |     26 | M      |
|     6 |     46 | M      |
|    13 |     33 | M      |  #性别为M，且年龄大于25岁的
|    24 |     27 | M      |
|    25 |    100 | M      |
+-------+--------+--------+
7 rows in set (0.00 sec)

插入字段、数据

MariaDB [hellodb]> select count(*) from students where stuage >25;
+----------+
| count(*) |   #统计年龄大于25的出现次数
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

MariaDB [hellodb]> select sum(stuage) from students where stuage >25;         
+-------------+
| sum(stuage) |  #统计年龄大于25之和
+-------------+
|         347 |
+-------------+
1 row in set (0.00 sec)

MariaDB [hellodb]> select classid,count(stuid) from students where classid is not null group by classid;
+---------+--------------+
| classid | count(stuid) |
+---------+--------------+
|       1 |            4 |
|       2 |            3 |
|       3 |            4 |
|       4 |            4 |
|       5 |            1 |
|       6 |            4 |
|       7 |            3 |
+---------+--------------+
7 rows in set (0.01 sec)
 
MariaDB [hellodb]> select classid,count(stuid) from students where classid is not null group by classid having count(stuid) >=3;    
+---------+--------------+
| classid | count(stuid) |
+---------+--------------+
|       1 |            4 |
|       2 |            3 |
|       3 |            4 |
|       4 |            4 |
|       6 |            4 |
|       7 |            3 |
+---------+--------------+
6 rows in set (0.00 sec)

MariaDB [hellodb]> select distinct stuage from students where stuage > 45;
+--------+
| stuage |
+--------+  #不显示重复的
|     53 |
|     46 |
|    100 |
+--------+
3 rows in set (0.00 sec)

MariaDB [hellodb]> select * from students as s,classes as c where s.classid = c.classid;
+-------+--------+--------+---------+-----------+---------+----------------+----------+
| StuID | StuAge | gender | ClassID | TeacherID | ClassID | Class          | NumOfStu |
+-------+--------+--------+---------+-----------+---------+----------------+----------+
|     1 |     22 | M      |       2 |         3 |       2 | Emei Pai       |        7 |
|     2 |     22 | M      |       1 |         7 |       1 | Shaolin Pai    |       10 |
+-------+--------+--------+---------+-----------+---------+----------------+----------+
# 第一张表的classid与第二张表的classid匹配

MariaDB [hellodb]> select s.stuid,c.class from students as s,classes as c where s.classid = c.classid;  
+-------+----------------+
| stuid | class          |
+-------+----------------+
|     1 | Emei Pai       |
|     2 | Shaolin Pai    |
|     3 | Emei Pai       |
|     4 | Wudang Pai     |
|     5 | QingCheng Pai  |
+-------+----------------+
# 表名加字段 stu表的name字段 classes表的class字段

联合查询  合并成一个表输出显示

MariaDB [hellodb]> create view view_stu as select * from students where stuage >= 45;
Query OK, 0 rows affected (0.01 sec)

MariaDB [hellodb]> select * from view_stu;
+-------+--------+--------+---------+-----------+
| StuID | StuAge | gender | ClassID | TeacherID |
+-------+--------+--------+---------+-----------+
|     3 |     53 | M      |       2 |        16 |
|     6 |     46 | M      |       5 |      NULL |
|    25 |    100 | M      |    NULL |      NULL |
+-------+--------+--------+---------+-----------+
3 rows in set (0.00 sec)

MariaDB [hellodb]> drop view view_stu;
Query OK, 0 rows affected (0.00 sec)

执行操作时施加的锁模式：

读锁：共享锁

写锁：独占锁，排它锁

锁粒度：

表锁：table lock  锁定整张表

行锁：row lock 锁定需要的行

粒度越小，开销越大，并发性能越好

粒度越大，开销越小，并以性能越差

MariaDB [hellodb]> select * from classes;
+---------+----------------+----------+
| ClassID | Class          | NumOfStu |
+---------+----------------+----------+
|       1 | Shaolin Pai    |       10 |
|       2 | Emei Pai       |        7 |
|       3 | QingCheng Pai  |       11 |
|       4 | Wudang Pai     |       12 |
|       5 | Riyue Shenjiao |       31 |
|       6 | Lianshan Pai   |       27 |
|       7 | Ming Jiao      |       27 |
|       8 | Xiaoyao Pai    |       15 |
+---------+----------------+----------+
8 rows in set (0.00 sec)

MariaDB [hellodb]> lock tables classes read;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> insert into classes value (10,'hotwing',41); 
ERROR 1099 (HY000): Table 'classes' was locked with a READ lock and can't be updated
创建读锁，再写就阻塞了（可以读） 不过一旦取消锁，就立刻修改成功了 （需在另一个窗口操作）

MariaDB [hellodb]> lock tables classes write;  
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> select * from classes;
如果把锁改成write锁，就排他，别人看都看不了

新建一张表--把原来数据导进入--删除原来的--再把新表改回原来名字

备份时，要手动对表施加读锁

MariaDB [hellodb]> start transaction; 启动一个事务
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> select database();
+------------+
| database() |
+------------+
| hellodb    |
+------------+
1 row in set (0.00 sec)

MariaDB [hellodb]> select * from classes;
+---------+----------------+----------+
| ClassID | Class          | NumOfStu |
+---------+----------------+----------+
|       1 | Shaolin Pai    |       10 |
|       2 | Emei Pai       |        7 |
|       3 | QingCheng Pai  |       11 |
|       4 | Wudang Pai     |       12 |
|       5 | Riyue Shenjiao |       31 |
|       6 | Lianshan Pai   |       27 |
|       7 | Ming Jiao      |       27 |
|       8 | Xiaoyao Pai    |       15 |
|      10 | hotwing        |       41 |
+---------+----------------+----------+
9 rows in set (0.00 sec)

MariaDB [hellodb]> delete from classes where classid = 10;  删除一行
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> select * from classes;     查看没有了           
+---------+----------------+----------+
| ClassID | Class          | NumOfStu |
+---------+----------------+----------+
|       1 | Shaolin Pai    |       10 |
|       2 | Emei Pai       |        7 |
|       3 | QingCheng Pai  |       11 |
|       4 | Wudang Pai     |       12 |
|       5 | Riyue Shenjiao |       31 |
|       6 | Lianshan Pai   |       27 |
|       7 | Ming Jiao      |       27 |
|       8 | Xiaoyao Pai    |       15 |
+---------+----------------+----------+
8 rows in set (0.00 sec)

但是通过另一个窗口查看 内容还在，因为目前还在事务中，没有提交（MariaDB [hellodb]> commit;）
如果提交了，大家内容就都一样了
MariaDB [hellodb]> select * from classes;
+---------+----------------+----------+
| ClassID | Class          | NumOfStu |
+---------+----------------+----------+
|       1 | Shaolin Pai    |       10 |
|       2 | Emei Pai       |        7 |
|       3 | QingCheng Pai  |       11 |
|       4 | Wudang Pai     |       12 |
|       5 | Riyue Shenjiao |       31 |
|       6 | Lianshan Pai   |       27 |
|       7 | Ming Jiao      |       27 |
|       8 | Xiaoyao Pai    |       15 |
|      10 | hotwing        |       41 |
+---------+----------------+----------+
9 rows in set (0.00 sec)

当然也可以回滚
MariaDB [hellodb]> rollback;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> select * from classes;
+---------+----------------+----------+
| ClassID | Class          | NumOfStu |
+---------+----------------+----------+
|       1 | Shaolin Pai    |       10 |
|       2 | Emei Pai       |        7 |
|       3 | QingCheng Pai  |       11 |
|       4 | Wudang Pai     |       12 |
|       5 | Riyue Shenjiao |       31 |
|       6 | Lianshan Pai   |       27 |
|       7 | Ming Jiao      |       27 |
|       8 | Xiaoyao Pai    |       15 |
|      10 | hotwing        |       41 |
+---------+----------------+----------+
9 rows in set (0.00 sec)

如果你删除了好多，想回滚，它会全部恢复，这样力度太多了

可以定义保存点

MariaDB [hellodb]> start transaction;                     
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> delete from classes where classid = 4;
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> savepoint a;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> delete from classes where classid = 10;
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> select * from classes;
+---------+----------------+----------+
| ClassID | Class          | NumOfStu |
+---------+----------------+----------+
|       1 | Shaolin Pai    |       10 |
|       2 | Emei Pai       |        7 |
|       3 | QingCheng Pai  |       11 |
|       5 | Riyue Shenjiao |       31 |
|       6 | Lianshan Pai   |       27 |
|       7 | Ming Jiao      |       27 |
|       8 | Xiaoyao Pai    |       15 |
+---------+----------------+----------+
7 rows in set (0.00 sec)

MariaDB [hellodb]> rollback to a;  回滚到保存点a之后，恢复classid=10
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> select * from classes;
+---------+----------------+----------+
| ClassID | Class          | NumOfStu |
+---------+----------------+----------+
|       1 | Shaolin Pai    |       10 |
|       2 | Emei Pai       |        7 |
|       3 | QingCheng Pai  |       11 |
|       5 | Riyue Shenjiao |       31 |
|       6 | Lianshan Pai   |       27 |
|       7 | Ming Jiao      |       27 |
|       8 | Xiaoyao Pai    |       15 |
|      10 | hotwing        |       41 |
+---------+----------------+----------+
8 rows in set (0.00 sec)

MariaDB [hellodb]> commit;  提交     保存点a之前的生效 classid=4 删除生效
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> select * from classes;
+---------+----------------+----------+
| ClassID | Class          | NumOfStu |
+---------+----------------+----------+
|       1 | Shaolin Pai    |       10 |
|       2 | Emei Pai       |        7 |
|       3 | QingCheng Pai  |       11 |
|       5 | Riyue Shenjiao |       31 |
|       6 | Lianshan Pai   |       27 |
|       7 | Ming Jiao      |       27 |
|       8 | Xiaoyao Pai    |       15 |
|      10 | hotwing        |       41 |
+---------+----------------+----------+
8 rows in set (0.00 sec)

mysql日志

记录查询日志

MariaDB [hellodb]> set global general_log='on';
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> show global variables like '%log%';

general_log                               | ON  


[root@mysql-6 mysql]#ls
aria_log.00000001  ib_logfile1        mysql-6.pid       mysql-bin.000006  mysql-bin.000012
aria_log_control   ibtmp1             mysql-bin.000001  mysql-bin.000007  mysql-bin.000013
hellodb            multi-master.info  mysql-bin.000002  mysql-bin.000008  mysql-bin.index
ib_buffer_pool     mysql              mysql-bin.000003  mysql-bin.000009  performance_schema
ibdata1            mysql-6.err        mysql-bin.000004  mysql-bin.000010  test
ib_logfile0        mysql-6.log        mysql-bin.000005  mysql-bin.000011  testdb1
[root@mysql-6 mysql]#cat mysql-6.log 
/usr/local/mysql/bin/mysqld, Version: 10.2.18-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
181130 17:01:47    12 Query     show global variables like '%log%'

MariaDB [hellodb]> set global log_output='table';       
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> select * from toc;
+------+----------+------+-------------+
| ID   | CourseID | TID  | phone       |
+------+----------+------+-------------+
| 9527 |     NULL | NULL | 13160180088 |
+------+----------+------+-------------+
1 row in set (0.00 sec)

MariaDB [hellodb]> select * from mysql.general_log; #设置成记录到表里
+----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+
| event_time                 | user_host                 | thread_id | server_id | command_type | argument                        |
+----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+
| 2018-11-30 17:09:24.252644 | root[root] @ localhost [] |        12 |         1 | Query        | select * from toc               |
| 2018-11-30 17:09:37.720222 | root[root] @ localhost [] |        12 |         1 | Query        | select * from mysql.general_log |
+----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+
2 rows in set (0.00 sec)

慢查询日志

slow_query_log = ON
log_output = file
slow_query_log_file = /data/mysql/mysql-6-slow.log
long_query_time = 10
MariaDB [hellodb]> show variables like 'long%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

MariaDB [hellodb]> show variables like 'slow%';
+---------------------+------------------------------+
| Variable_name       | Value                        |
+---------------------+------------------------------+
| slow_launch_time    | 2                            |
| slow_query_log      | ON                           |
| slow_query_log_file | /data/mysql/mysql-6-slow.log |
+---------------------+------------------------------+
3 rows in set (0.00 sec)

 

日志文件：六类

一般查询日志 log, general_log, log_output

慢查询日志

错误日志

二进制日志

中继日志

事务日志

 

二进制日志：“修改”

position：位置

time：时间

滚动：

1、大小

2、时间

/etc/my.cnf

[mysqld]

log-bin=mysql-bin

binlog_format=mixed(混合模式：语句+行)     还有：基于语句，基于行

如果对存储空间，以及I/O没什么要求的话 也可以用row格式  行准确点

MariaDB [hellodb]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      342 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [hellodb]> flush logs;
Query OK, 0 rows affected (0.01 sec)

MariaDB [hellodb]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      385 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [hellodb]> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       389 |
| mysql-bin.000002 |       385 |
+------------------+-----------+
2 rows in set (0.00 sec)


查看二进制日志
MariaDB [hellodb]> show binlog events in 'mysql-bin.000002';
+------------------+-----+-------------------+-----------+-------------+------------------------------------------------+
| Log_name         | Pos | Event_type        | Server_id | End_log_pos | Info                                           |
+------------------+-----+-------------------+-----------+-------------+------------------------------------------------+
| mysql-bin.000002 |   4 | Format_desc       |         1 |         256 | Server ver: 10.2.18-MariaDB-log, Binlog ver: 4 |
| mysql-bin.000002 | 256 | Gtid_list         |         1 |         299 | [0-1-157]                                      |
| mysql-bin.000002 | 299 | Binlog_checkpoint |         1 |         342 | mysql-bin.000001                               |
| mysql-bin.000002 | 342 | Binlog_checkpoint |         1 |         385 | mysql-bin.000002                               |
+------------------+-----+-------------------+-----------+-------------+------------------------------------------------+
4 rows in set (0.00 sec)
MariaDB [hellodb]> insert into toc (id,courseid,tid,phone)values('9528','34','35','13160180098');
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> flush logs;        
Query OK, 0 rows affected (0.01 sec)

MariaDB [hellodb]> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       389 |
| mysql-bin.000002 |       652 |
+------------------+-----------+
2 rows in set (0.00 sec)

MariaDB [hellodb]> show binlog events in 'mysql-bin.000002';
+------------------+-----+-------------------+-----------+-------------+----------------------------------------------------------------------------------------------+
| Log_name         | Pos | Event_type        | Server_id | End_log_pos | Info                                                                                         |
+------------------+-----+-------------------+-----------+-------------+----------------------------------------------------------------------------------------------+
| mysql-bin.000002 |   4 | Format_desc       |         1 |         256 | Server ver: 10.2.18-MariaDB-log, Binlog ver: 4                                               |
| mysql-bin.000002 | 256 | Gtid_list         |         1 |         299 | [0-1-157]                                                                                    |
| mysql-bin.000002 | 299 | Binlog_checkpoint |         1 |         342 | mysql-bin.000001                                                                             |
| mysql-bin.000002 | 342 | Binlog_checkpoint |         1 |         385 | mysql-bin.000002                                                                             |
| mysql-bin.000002 | 385 | Gtid              |         1 |         427 | BEGIN GTID 0-1-158                                                                           |
| mysql-bin.000002 | 427 | Query             |         1 |         574 | use `hellodb`; insert into toc (id,courseid,tid,phone)values('9528','34','35','13160180098') |
| mysql-bin.000002 | 574 | Xid               |         1 |         605 | COMMIT /* xid=13 */                                                                          |
| mysql-bin.000002 | 605 | Rotate            |         1 |         652 | mysql-bin.000003;pos=4                                                                       |
+------------------+-----+-------------------+-----------+-------------+----------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)

记录了刚插入数据的信息

MariaDB [hellodb]> show binlog events in 'mysql-bin.000002' from 427\G
*************************** 1. row ***************************
   Log_name: mysql-bin.000002
        Pos: 427
 Event_type: Query
  Server_id: 1
End_log_pos: 574
       Info: use `hellodb`; insert into toc (id,courseid,tid,phone)values('9528','34','35','13160180098')
*************************** 2. row ***************************
   Log_name: mysql-bin.000002
        Pos: 574
 Event_type: Xid
  Server_id: 1
End_log_pos: 605
       Info: COMMIT /* xid=13 */

mysqlbinlog mysql-bin.000002

多久从缓冲区同步二进制数据到二进制文件中去

时间越长，性能越好，但安全性也就越差

为0表示不同步，（不基于时间来同步），仅在事务提交时同步

而默认事务提交又为ON（表示自动提交）     所以也就是二进制日志也是跟着自动同步的，影响性能

建议autocommit改为OFF的原因之一

建议：切勿将二进制日志与数据文件放在同一设备

如果不想记录到二进制文件当中

sql_log_bin 会话层面修改 临时的

大数据量备份，用物理备份

mysqldump --databases hellodb --lock-all-tables > /tmp/hellodb.sql

不过这样锁定表，性能不好

如果能保证底层都是innodb引擎的话：

先把所有的缓存同步到磁盘上，并施加读锁

滚动日志(看看记录到哪个二进制文件上的什么位置了，以后只要恢复这之后的就行)

进行备份

mysqldump --databases hellodb > /tmp/hellodb.sql

释放锁

也可以用这一条命令:

[root@mysql-6 mysql]#mysqldump --databases hellodb --lock-all-tables --flush-logs --master-data=2 > /tmp/hdb.sql

如果都是Innodb引擎：热备，如下

[root@mysql-6 mysql]#mysqldump --databases hellodb --single-transaction --flush-logs --master-data=2 > /tmp/hdb1.sql

模拟一次即时点恢复

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      328 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
先看下，目前二进制日志到哪了
mysqldump --databases hellodb --lock-all-tables --flush-logs --master-data=1 > /tmp/hdb.sql

进行备份

MariaDB [(none)]> use hellodb;
Database changed
MariaDB [hellodb]> create table newtable(name char(33));
Query OK, 0 rows affected (0.02 sec)

MariaDB [hellodb]> insert into newtable values('tom'),('jerry');  
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [hellodb]> drop database hellodb;
Query OK, 8 rows affected (0.06 sec)

再模拟创建表，插入表，误删除库，准备恢复（完全+即时）

[root@mysql-6 mysql]#cat /tmp/hdb.sql
-- MySQL dump 10.16  Distrib 10.2.18-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: hellodb
-- ------------------------------------------------------
-- Server version       10.2.18-MariaDB-log

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=371;

查看刚刚备份的文件，发现目前已经备份到了mysql-bin.000002的371位置之后

[root@mysql-6 mysql]#mysqlbinlog --start-position=371 mysql-bin.000002
# at 413
#181201 14:31:53 server id 1  end_log_pos 519 CRC32 0x06d8ad01  Query   thread_id=10    exec_time=0     error_code=0
use `hellodb`/*!*/;
SET TIMESTAMP=1543645913/*!*/;
SET @@session.pseudo_thread_id=10/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/;
SET @@session.sql_mode=1411383296/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table newtable(name char(33))
/*!*/;
# at 519
#181201 14:32:40 server id 1  end_log_pos 561 CRC32 0x0b0db5ca  GTID 0-1-2 trans
/*!100001 SET @@session.gtid_seq_no=2*//*!*/;
BEGIN
/*!*/;
# at 561
#181201 14:32:40 server id 1  end_log_pos 675 CRC32 0x5bd7f512  Query   thread_id=10    exec_time=0     error_code=0
SET TIMESTAMP=1543645960/*!*/;
insert into newtable values('tom'),('jerry')
/*!*/;
# at 706
#181201 14:33:00 server id 1  end_log_pos 748 CRC32 0x9345e9e9  GTID 0-1-3 ddl
/*!100001 SET @@session.gtid_seq_no=3*//*!*/;
# at 748  ##############这句就不用再恢复了，恢复到这之前就行了
#181201 14:33:00 server id 1  end_log_pos 839 CRC32 0xeb498401  Query   thread_id=10    exec_time=0     error_code=0
SET TIMESTAMP=1543645980/*!*/;
drop database hellodb
[root@mysql-6 mysql]#mysqlbinlog --start-position=371 --stop-position=706 mysql-bin.000002 > /tmp/hellodb.2.inc.sql

将000002文件的371之后到706之间的内容重定向出来

 

开始恢复：先进Mysql，关掉二进制日志

MariaDB [(none)]> set session sql_log_bin=0;   #会话层面
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> source /tmp/hdb.sql;   #完全备份
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> source /tmp/hellodb.2.inc.sql;   #增量备份
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> set session sql_log_bin=1;    #再把二进制日志开启  
Query OK, 0 rows affected (0.00 sec)

思考：如果完全备份后的误删，不是删的库而是只删了表，应该就只要还原二进制文件就行

MariaDB [hellodb]> show master status;             
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000007 |      385 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [hellodb]> create table cao(name char(22));
Query OK, 0 rows affected (0.01 sec)

MariaDB [hellodb]> create table kao(name char(22));
Query OK, 0 rows affected (0.01 sec)

MariaDB [hellodb]> insert into cao values('gg');
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> drop table cao;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> drop table kao;              
Query OK, 0 rows affected (0.01 sec)

做一些误操作

然后将要恢复的二进制数据重定向出来

[root@mysql-6 mysql]#mysqlbinlog --start-position=385 mysql-bin.000007
[root@mysql-6 mysql]#mysqlbinlog --start-position=385 --stop-position=842 mysql-bin.000007 > /tmp/hellodb.7.inc.sql

再开始恢复

MariaDB [hellodb]> set session sql_log_bin=0;      
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> source /tmp/hellodb.7.inc.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> set session sql_log_bin=1;    
Query OK, 0 rows affected (0.00 sec)

建议：关闭二进制日志，关闭其他用户连接

 

备份策略：基于mysqldump

备份：mysqldump+二进制日志文件

周日做一次完全备份：备份的同时，滚动日志

周一至周六：备份二进制日志；

恢复：完全备份+各二进制日志文件中至此刻的事件

mysqladmin flush-logs

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

不赖床会死星人
关注
专栏目录
MySQL定期整理磁盘碎片
weixin_44976692的博客
01-23 2万+
通过本博客，我们深入探讨了为什么需要定期整理MySQL磁盘碎片，以及如何进行这一操作。合理使用这些方法可以有效提升数据库性能，确保系统长时间稳定运行。希望这些信息对读者在实际应用中有所帮助。在下一篇博客中，我们将继续探讨MySQL数据库的其他方面，敬请期待！
MySQL安装配置教程（超级详细、保姆级）
热门推荐
Soloversion的博客
03-26 42万+
一、 下载MySQL Mysql官网下载地址https://downloads.mysql.com/archives/installer/ 1. 选择想要安装的版本，本篇文章选择的是5.7.31版本，下面的那个文件，点击Download下载 二、 安装MySQL 1. 选择设置类型 双击运行mysql-installer-community-5.7.31.0.msi 这里选择是自定义安装，所以直接选择“Custom”，点击“Next” “Developer Default”是开发者默认
mysql床数据库的命令_mysql数据库基本操作（五）
weixin_39861905的博客
01-19 133
表纪录之查(单表查询)2上一篇讲了4类查询的方式，现在接着上一篇继续看还有哪些方式。group by 分组查询顾名思义，分组查询要分组，因为mysql5.7之后默认不支持group by语句，需要改配置文件，我没有去改，如果需要用到group by的可以百度一下如何修改配置文件，再练习下面这个例子。group by 分组查询：CREATE TABLE order_menu(id INT PRIMA...
MySQL创建表查询库查询表方法
weixin_44171249的博客
07-04 392
1.查询数据库中是否存在表 2.查询mysql中是否存在这个库 3.数据库中床表表的方法
MySQL安装教程（详细版）
最新发布
m0_71422677的博客
02-20 21万+
MySQL免费安装教程、如何验证MySQL是否安装成功？怎么关掉MySQL服务？MySQL 端口问题如何解决？MySQL如何开机自启OR手动自启？安装MySQL时Starting the server 安装失败怎么解决？
Linux安装MySQL5.7
吃四碗饭的嘤嘤怪的博客
06-25 41万+
下载地址：https://dev.mysql.com/downloads/mysql/5.7.html#downloads 解压 tar -xvf mysql-5.7.26-linux-glibc2.12-x86_64.tar 再移动并重命名一下 mv mysql-5.7.26-linux-glibc2.12-x86_64 /usr/local/mysql ...
mysql无法连接
Chasingthewinds的博客
03-17 7757
Mysql无法连接的一般原因及相应解决方法
MySQL Workbench基本用法
potato123232的博客
01-03 2万+
MySQL Workbench相当于SQL语言的解释器。
mysql配置主从
Tyler唐的博客
12-05 1万+
mysql配置主从
MySQL计算年龄
qq_26097991的博客
04-06 1万+
MySQL根据出生日期计算年龄的五种方法比较 方法一 SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthday)), '%Y')+0 AS age 方法一，作者也说出了缺陷，就是当日期为未来日期时结果为0，而不是负数；这里使用了5个函数和两个运算符。 方法二 SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birthday, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d
mysql8.0数据库无法被远程连接问题排查（mysql远程连接设置方法）‘root‘@‘localhost‘和‘root‘@‘%‘（无法连接mysql无法远程连接、mysql连接被拒绝）
Dontla的博客
09-23 1万+
同时，‘root’@‘localhost’ 仍然存在，它只允许从Ubuntu服务器本地进行连接。命令，但在做这个操作之前，请确保 ‘root’@’%’ 可以正常工作，并且不需要 ‘root’@‘localhost’ 这个用户。如果还有问题，可能需要查看更详细的日志信息，比如MySQL的错误日志，通常位于。如果防火墙启用了，并且没有允许3306端口（默认的MySQL端口），则需要添加规则允许此端口。如果root用户的host字段是localhost，那么你需要更改权限让root用户可以从任何主机登录。
mysql jdbc连接驱动jar包（适用于mysql5.7版本，亲测可用）
08-17
MySQL JDBC连接驱动是Java应用程序与MySQL数据库之间通信的桥梁，使得开发者可以使用Java语言来执行SQL语句，处理数据库中的数据。本驱动适用于MySQL 5.7版本，并且经过实际测试，确认可用。这个jar包是完全免费的，...
mysql-connector-java-8.0.27
12-24
MySQL Connector/J 8.0.27 是MySQL数据库与Java应用程序之间的重要桥梁，它是一个实现了JDBC（Java Database Connectivity）接口的驱动程序，允许Java开发者在应用中无缝地访问MySQL数据库。MySQL Connector/J使得...
mysql驱动jar 文件适用MySQL5.7
01-18
MySQL驱动jar文件是Java应用程序与MySQL数据库之间通信的关键组件，主要功能是提供Java Database Connectivity (JDBC) API，使得Java开发者能够通过编写Java代码来执行SQL语句，从而操作MySQL数据库。标题提到的...
Java连接MySQL数据库的超级详细步骤（Windows）
firstlt0217的博客
05-19 11万+
1. 数据准备 1. 下载JDK 下载Java开发工具包JDK，下载地址：https://www.oracle.com/java/technologies/javase-jdk14-downloads.html，下载待用。 2. 下载Mysql Java 连接 MySQL 需要驱动包，下载地址为：http://dev.mysql.com/downloads/connector/j/。 解压后得到 jar 库文件，待用，最终会将红框文件导入相应项目文件中。 3. 下载MySQL 下载地址：https:/
编写sort函数
不赖床会死星人的博客
02-13 1077
初版： lst = [1, 2, 3, 4, 5] def sort(iterable, reverse=False): def comp(a, b): flag = a > b if reverse else a < b return flag ret = [] for x in iterable: for...
参数注解
不赖床会死星人的博客
02-16 724
1、对函数的参数进行类型注解 2、对函数的返回值进行类型注解 3、只对函数参数做一个辅助说明，并不对函数参数进行类型检查 4、提供给第三方工具，做代码分析，发现隐藏BUG 5、函数注解的信息，保存在__annotations__属性中 def add(x: int, y: int, *args, **kwargs) -> int: ret = x + y ret...
面向对象题目一
不赖床会死星人的博客
03-05 589
随机整数生成类，可以指定一批生成的个数，可以指定数值的范围，可以调整每批生成数字的个数 使用这个类，随机生成20个数字，两两配对形成二维坐标系的坐标，把这些坐标组织起来，并打印输出 一： import random class Randnum: def __init__(self, rmin=0, rmax=100, num=10): self.min = rmi...
递归函数习题
不赖床会死星人的博客
01-03 517
# 阶乘 def fn(n): if n == 1: return 1 else: return n * fn(n-1) print(fn(5)) def factorila(a, mul=1): mul *= a if a == 1: return mul return factorila(a...
MySQL 5.1.58 安装教程
2. 创建用户组和用户：`groupadd mysql` 创建名为mysql的用户组，`useradd -g mysql mysql -s /sbin/nologin` 添加一个名为mysql的用户并将其添加到mysql组，且指定不登录 shell。 3. 解压MySQL源码包：`tar xzf ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值