傻男人---个人博客

mysql--常用命令与操作

一、表

修改表名

ALTER TABLE 旧表名 RENAME TO 新表名 ;

ALTER TABLE  `user` RENAME `new_user`;

删除表

drop table user;optimize table  user;

修改表备注

ALTER TABLE 表名 COMMENT '新注释';

ALTER TABLE `user` COMMENT = '用户表';

删除user并优化表并释放磁盘空间

1、字段

添加字段

ALTER TABLE 表名 ADD [COLUMN] 字段名字段类型是否可为空 COMMENT '注释' AFTER 指定某字段 ;

ALTER TABLE `user` ADD COLUMN `member` int(11) DEFAULT 0 COMMENT '会员' after name;

在user表中增加member字段并且在字段name后面

更新字段
- 修改字段名
alter table table1 change column1 column1 varchar(100) DEFAULT '测试' COMMENT '注释';
```
 ALTER TABLE `user` CHANGE name  user_name varchar(32) DEFAULT NULL COMMENT '名字';
```
- 修改字段类型与长度
ALTER TABLE 表名 MODIFY COLUMN 字段名字段类型(字段长度);
```
 ALTER TABLE `user` MODIFY  COLUMN name varchar(32);
```
删除字段

ALTER TABLE 表名 DROP [COLUMN] 字段名 ;

ALTER TABLE `user` drop COLUMN `member`;

删除user表中member字段

2、索引

添加索引
- a.添加PRIMARY KEY(主键索引)--该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
```
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);
```
- b.添加UNIQUE(唯一索引)--这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
```
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list);
```
- c.添加INDEX(普通索引)--索引值可出现多次
```
ALTER TABLE tbl_name ADD INDEX index_name (column_list)
```
- d.添加FULLTEXT(全文索引)
```
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)
```
刪除索引

ALTER TABLE `user` DROP INDEX `idx_name`;
或者
DROP INDEX <索引名> ON <表名>;

修改索引

二、查询

1、根据条件删除表中重复的数据

删除租户菜单中,租户id与菜单id重复的数据

DELETE t1 FROM sys_tenant_menu AS t1 INNER JOIN sys_tenant_menu AS t2 ON t1.tenant_id = t2.tenant_id and t1.menu_id = t2.menu_id  AND t1.id > t2.id;

2、实现递归查询

a、准备表

DROP TABLE IF EXISTS `sys_document_catalogue`;
CREATE TABLE `sys_document_catalogue` (
   `id` bigint(20) NOT NULL COMMENT '自动编号',
   `parent_id` bigint(20) default 0 COMMENT '父id(0父级)',
   `name` varchar (64) NOT NULL COMMENT '目录名',
   PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

b、初始数据

数据请自行插入

c、向下递归

利用find_in_set()函数和group_concat()函数实现递归查询：

drop FUNCTION if exists getChildListDown;
DELIMITER $$

CREATE FUNCTION `getChildListDown`(rootId BIGINT)
RETURNS varchar(4000) CHARSET utf8
DETERMINISTIC -- 添加 DETERMINISTIC 标记
BEGIN
  DECLARE parentVar VARCHAR(1000) DEFAULT ''; -- 初始化 parentVar
  DECLARE childVar VARCHAR(1000) DEFAULT CAST(rootId AS CHAR); -- 初始化 childVar
  DECLARE childIds VARCHAR(1000); -- 新增变量用于存储子菜单ID

  WHILE childVar IS NOT NULL DO
    SET parentVar = CONCAT(parentVar, ',', childVar);

    -- 使用 SELECT INTO 获取子菜单 ID 的逗号分隔列表
    SELECT GROUP_CONCAT(id) INTO childIds
    FROM sys_document_catalogue
    WHERE FIND_IN_SET(parent_id, childVar) > 0;

    -- 更新 childVar 为新的子菜单 ID 列表
    SET childVar = childIds;
  END WHILE;

  RETURN parentVar;
END$$

DELIMITER ;

生成方法是可能出现的错误

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

解决方法： set global log_bin_trust_function_creators=TRUE;

调用

SELECT getChildListDown(0); 查询id为4下面的所有节点

SELECT * from sys_document_catalogue WHERE FIND_IN_SET(id,getChildListDown(4))

d、向上递归

DROP FUNCTION IF EXISTS getChildListUp;
CREATE FUNCTION getChildListUp(rootId BIGINT) RETURNS VARCHAR(4000)
BEGIN
DECLARE parentVar VARCHAR(4000);
DECLARE childVar VARCHAR(4000);
 
SET parentVar='$';
SET childVar = CAST(rootId AS CHAR);
SET parentVar = CONCAT(parentVar,',',childVar);
 
SELECT parent_id INTO childVar FROM sys_document_catalogue WHERE id = childVar;
WHILE childVar <> 0 DO
SET parentVar = CONCAT(parentVar,',',childVar);
SELECT parent_id INTO childVar FROM sys_document_catalogue WHERE id = childVar;
END WHILE;
RETURN parentVar;
END

调用

SELECT getChildListUp(0); 查询id为4的所有的上节点

SELECT * from sys_document_catalogue WHERE FIND_IN_SET(id,getChildListUp(4))

e、直接查询(方式一)

SELECT id AS ID,parent_id AS 父ID ,levels AS 父到子之间级数, paths AS 父到子路径 FROM (
SELECT id,parent_id,

                    @le:= IF (parent_id = 0 ,0,
         IF( LOCATE( CONCAT('|',parent_id,':'),@pathlevel)   > 0  ,
                  SUBSTRING_INDEX( SUBSTRING_INDEX(@pathlevel,CONCAT('|',parent_id,':'),-1),'|',1) +1
        ,@le+1) ) levels,
				
        @pathlevel:= CONCAT(@pathlevel,'|',id,':', @le ,'|') pathlevel,
        @pathnodes:= IF( parent_id =0,',0',
           CONCAT_WS(',',
           IF( LOCATE( CONCAT('|',parent_id,':'),@pathall) > 0  ,
               SUBSTRING_INDEX( SUBSTRING_INDEX(@pathall,CONCAT('|',parent_id,':'),-1),'|',1)
              ,@pathnodes ) ,parent_id  ) )paths
    ,@pathall:=CONCAT(@pathall,'|',id,':', @pathnodes ,'|') pathall
         FROM  sys_document_catalogue,
             (SELECT @le:=0,@pathlevel:='', @pathall:='',@pathnodes:='') vv 
-- 						 WHERE parent_id = 0
         ORDER BY  parent_id,id
     ) src
ORDER BY id

f、直接查询(方式二)

查询id为4下面的所有节点

SELECT
	ID.LEVEL,
	DATA.* 
FROM
	(
	SELECT
		@ids AS _ids,
		( SELECT @ids := GROUP_CONCAT( id ) FROM 表名 WHERE FIND_IN_SET( 父级 id字段, @ids ) ) AS cids,
		@l := @l + 1 AS LEVEL 
	FROM
		表名,
		( SELECT @ids := '条件id', @l := 0 ) b 
	WHERE
		@ids IS NOT NULL 
	) ID,
	表名 DATA 
WHERE
	FIND_IN_SET( DATA.id, ID._ids ) 
ORDER BY
	LEVEL,
	id

对应本次的查询

SELECT GROUP_CONCAT(a.id) as id FROM
(
	SELECT ID.LEVEL,DATA.* 
	FROM
		(
		SELECT
			@ids AS _ids,
			( SELECT @ids := GROUP_CONCAT( id ) FROM sys_document_catalogue WHERE FIND_IN_SET( parent_id, @ids ) ) AS cids,
			@l := @l + 1 AS LEVEL 
		FROM
			sys_document_catalogue,
			( SELECT @ids := 4, @l := 0 ) b 
		WHERE
			@ids IS NOT NULL 
		) ID,
		sys_document_catalogue DATA 
	WHERE
		FIND_IN_SET( DATA.id, ID._ids ) 
	ORDER BY
		LEVEL,
		id
) a

3、备份与恢复

将当前数据库的数据备份到其他的库中

mysqldump --default-character-set=utf8mb4 --host=192.168.0.90 -uroot -pxxx -P3306 --opt process-test | mysql --host=172.16.10.150 -uroot -pxxx -P13063 --default-character-set=utf8mb4 -C process_bus

将192.168.0.90上process-test数据库备份到172.16.10.150数据库的process_bus这个库中

将数据库表备份到指定的目录

mysqldump -uroot -pxxxx robot neo_label> /back-up/neo_label.sql

导出整个实例

mysqldump -uroot -pxxxxxx --all-databases > /back-up/all_database.sql

导出指定库

mysqldump -uroot -pxxxxxx --databases testdb > /back-up/testdb.sql

导出指定表

mysqldump -uroot -pxxxxxx testdb test_tb > /back-up/test_tb.sql

导入指定SQL文件（指定导入testdb库中）

mysql -uroot -pxxxxxx testdb < testdb.sql

4、开启慢查询

下面为临时的方式，永久的方式需要修改my.cnf文件的值

a、查询数据库是否开启慢查询

show variables like 'slow_query%';

返回结果如下

Variable_name	Value
slow_query_log	OFF
slow_query_log_file	/var/lib/mysql/localhost-slow.log

默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的，可以通过设置slow_query_log的值来开启

b、临时开启(重启失效)

set global slow_query_log=1;

c、查看你是否开启

show variables like 'slow_query%';

观察slow_query_log的值是否为NO

d、写入测试数据

select sleep(10) as a, 1 as b;

e、查看slow_query_log_file中是否有值

[root@localhost ~]# tail -f /var/lib/mysql/localhost-slow.log
/usr/sbin/mysqld, Version: 5.7.34-log (MySQL Community Server (GPL)). started with:
Tcp port: 13063  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2022-12-26T03:29:40.332751Z
# User@Host: root[root] @  [192.168.50.183]  Id: 145921
# Query_time: 10.001994  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use base;
SET timestamp=1672025380;
select sleep(10) as a, 1 as b;

5、设置最大连接数

查看配置最大连接数

show variables like '%max_connections%';

查看当前的连接数

show global status like 'Max_used_connections';

设置最大连接数

临时方式

set GLOBAL max_connections=1000;

永久方式(在配置文件中增加如下的配置)

[mysqld]
max_connections=1024

6、修改Sql_mode

Sql_mode是一组mysql的语法校验规则

查看当前模式

select @@GLOBAL.sql_mode;

设置模式

临时方式
- 全局方式(只是对新建表起作用)

SET @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

针对之前表的设置

SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

永久方式

在配置文件中增加如下的配置

[mysqld]
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

7、其他操作

通过语句设置初始值

alter tabletestAUTO_INCREMENT=10000;

8、查询的结果集添加自增序号

查询的结果集没有ID字段时，为方便前台展示等业务需求，需要添加一个自增的序号字段(ID)

语法如下： SELECT (@i:=@i+1) 别名1,表字段信息 FROM 表名, (SELECT @i:=0) AS 别名2 示例如下： Select (@i:=@i+1) as RowNum,A.* from Table1 A,(Select @i:=0) B

上面示例等同于

Set @i:=0;
Select (@i:=@i+1) as RowNum,A.* from Table1 A

9、查看默认密码

8.0之前

grep 'temporary password' /var/log/mysqld.log

8.0后

grep 'temporary password' /var/log/mysql/mysqld.log

10、其他操作

清空表

清空所有的数据，只是保留表结构

truncate TABLE table_name;

删除数据

delete from 表名 where 表达式

其他的一些操作

查询安装路径

show variables like '%datadir%';

附：几种常见的mode

ONLY_FULL_GROUP_BY：select后面的字段中，只能出现分组列和聚合列。
NO_AUTO_VALUE_ON_ZERO：该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。
STRICT_TRANS_TABLES：在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制
NO_ZERO_IN_DATE：在严格模式下，不允许日期和月份为零。
NO_ZERO_DATE：在严格模式下，不允许插入0日期。
ERROR_FOR_DIVISION_BY_ZERO：在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时MySQL返回NULL
NO_AUTO_CREATE_USER：禁止GRANT创建密码为空的用户
NO_ENGINE_SUBSTITUTION：如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常
PIPES_AS_CONCAT：将”||”视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似
ANSI_QUOTES：启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符

注意：本文归作者所有，未经作者允许，不得转载

mysql--常用命令与操作

mysql--常用命令与操作

一、表

1、字段

2、索引

二、查询

1、根据条件删除表中重复的数据

2、实现递归查询

a、准备表

b、初始数据

c、向下递归

生成方法是可能出现的错误

调用

d、向上递归

调用

e、直接查询(方式一)

f、直接查询(方式二)

3、备份与恢复

将当前数据库的数据备份到其他的库中

将数据库表备份到指定的目录

导出整个实例

导出指定库

导出指定表

导入指定SQL文件 （指定导入testdb库中）

4、开启慢查询

a、查询数据库是否开启慢查询

b、临时开启(重启失效)

c、查看你是否开启

d、写入测试数据

e、查看slow_query_log_file中是否有值

5、设置最大连接数

查看配置最大连接数

查看当前的连接数

设置最大连接数

6、修改Sql_mode

查看当前模式

设置模式

7、其他操作

8、查询的结果集添加自增序号

9、查看默认密码

10、其他操作

清空表

清空所有的数据，只是保留表结构

删除数据

其他的一些操作

查询安装路径

附：几种常见的mode

全部评论: 0 条

热门文章

最新发布

最新评论

导入指定SQL文件（指定导入testdb库中）