MySQL操作笔记

mysql命令

MySQL注释符有三种：

1、#注释内容，表示单行注释 2、"-- 注释内容" （注意--后面有一个空格，注释到行尾，也是单行注释） 3、/注释内容/ ，可跨行注释 另外，需要注意以下几点： 1、 /* …. / 在大部分编程语言中都是注释，这个注释之中的语句是不被执行的。但是，在MySQL中为了保持兼容，比如从mysqldump导出的SQL语句能被其它数据库直接使用，它把一些特有的仅在MySQL上的语句放在 /! … */ 中，这样这些语句如果在其它数据库中是不会被执行，但在MySQL中它会执行。在这里，和！之间不能有空格。 2、 语句例如“/!50701 select * from test */;”，这里的50701表示假设数据库服务器是5.7.01以上版本(大于或等于)，该语句才会被执行。

示例如下：

# select * from mysql.user;

select * from mysql.user ; -- 查询mysql.user表中的信息


/*
select * from mysql.user;
select * from mysql.user where ID=1;
*/

mysql 语句中含;但又不是以该分号表示语句结束时，就需要用到delimiter来修改命令结束符

例如

show databases;	# 回车会立即执行

delimiter $$	# 修改结束符为$$
show databases;	# 该语句不会立即执行
$$		# 识别到$$ 才会执行整条语句
delimiter ;	# 将结束符改回为;

---

查看指定用户的权限

show grants for username@'%';
show grants for username@'1.1.1.1';

# 添加权限
grant select,insert on *.* to username@'%';
grant select,insert on db.* to username@'%';
grant select,insert on db.table to username@'%';
grant all privileges on *.* to username@'%';


# 删除权限
revoke all privileges on *.* from username@'%';
revoke select on db.table from username@'1.1.1.1';

# 刷新
flush privileges;

# 把某个查询结果当查询条件，比如，有A、B两张表，有共同字段ID，已知B表字段name,想通过name查到B表的ID然后通过ID到A表查询address；通过下面语句即可一条查出结果，而不需要分两次执行。
select address from A where ID=(select ID from B where name='tom')

---

mysql权限列表

权限	列	上下文
CREATE	Create_priv	数据库、表或索引
DROP	Drop_priv	数据库或表
GRANT OPTION	Grant_priv	数据库、表或保存的程序
REFERENCES	References_priv	数据库或表
ALTER	Alter_priv	表
DELETE	Delete_priv	表
INDEX	Index_priv	表
INSERT	Insert_priv	表
SELECT	Select_priv	表
UPDATE	Update_priv	表
CREATE VIEW	Create_view_priv	视图
SHOW VIEW	Show_view_priv	视图
ALTER ROUTINE	Alter_routine_priv	保存的程序
CREATE ROUTINE	Create_routine_priv	保存的程序
EXECUTE	Execute_priv	保存的程序
FILE	File_priv	服务器主机上的文件访问
CREATE TEMPORARY TABLES	Create_tmp_table_priv	服务器管理
LOCK TABLES	Lock_tables_priv	服务器管理
CREATE USER	Create_user_priv	服务器管理
PROCESS	Process_priv	服务器管理
RELOAD	Reload_priv	服务器管理
REPLICATION CLIENT	Repl_client_priv	服务器管理
REPLICATION SLAVE	Repl_slave_priv	服务器管理
SHOW DATABASES	Show_db_priv	服务器管理
SHUTDOWN	Shutdown_priv	服务器管理
SUPER	Super_priv	服务器管理

---

给用户授权

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'WITH GRANT OPTION 　　//赋予任何主机访问数据的权限 
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost'WITH GRANT OPTION 
FLUSH PRIVILEGES 　　//修改生效 
GRANT ALL PRIVILEGES ON *.* to 'root'@'%' IDENTIFIED BY 'pass4you' WITH GRANT OPTION; //以这个为准

创建数据库，并导入数据

create database shiku;
use shiku;
source shiku.sql;

不进入mysql命令行执行操作命令

mysql -e可以不需要进入mysql命令行界面即可执行命令，例如下面这两个操作：

mysql -u root -e "GRANT ALL PRIVILEGES ON *.* to 'root'@'localhost' IDENTIFIED BY '123456' WITH GRANT OPTION;"
mysql -u root -pLsjr_2016to2017 -e "GRANT ALL PRIVILEGES ON *.* to 'root'@'%' IDENTIFIED BY 'Lsjr_2016to2017' WITH GRANT OPTION;"

忘记root密码（5.7可用）

修改配置文件

vim /etc/my.cnf

在[mysqld]节点添加

skip-grant-tables

重启mysql 用空密码进入

mysql -uroot 执行下面的命令更新密码，authentication_string是5.7以上版本的密码字段，之前的版本为password

update mysql.user set authentication_string=password('123') where user='root' and Host = 'localhost';
flush privileges;

更新用户密码

用UPDATE直接编辑user表更新用户密码 ，password字段要看mysql版本，5.7以后的版本为authentication_string 登录MySQL。

mysql -u root -p
use mysql; 
update user set password=password('123') where user='root' and host='localhost'; 
flush privileges; 

---

部分操作示例

# 查看db库 tables表所有内容
select * from db.tables;

# 查询ID为1的行,显示所有字段
select * from db.tables where id = 1;

# 查询ID为1的行,只显示 name 和 age 字段
select name,age from db.tables where id = 1;	

# in 查询ID匹配括号中值的行，id 为11，21这种不会被匹配出来
select * from db.tables where id in (1,2,3,4);	

# and 连接多个条件
select * from db.tables where id = 1 and name = 'zzz';	

# 删除db库 tables 表 id 为1的行
delete from db.tables where id = 1;		

# 更新 tables表中 ID为1的name字段内容为zzz
update tables set name = 'zzz' where id = 1;		

# 查看索引
show index from db.table;	

# 查看表描述
desc db.table;	

# 查看建表时的信息
show create table TABLE_NAME; 

# 删除指定表
drop table TABLE_NAME; 

# 删除指定库
DROP DATABASE DB_NAME;

# 清空表，保留结构，可用日志恢复（每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。）
delete from DBNAME.TABLE_NAME; 

# delete命令清空表会保留之前的auto_increment自增值，以下命令可以对delete清除的表重置自增值为1
alter table table_name auto_increment=1;

# 清空表，保留结构，不可恢复（一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。）
# 而且还会重置自增auto_increment的值为1
truncate table TABLE_NAME; 

# 从当前表中查询出相应的字段，以此字段为条件进行表内删除
# 因为mysql不能对同一张表同时查询和更新/删除，需要将查询出来的结果放在一张临时存储，然后再引用进行删除
delete from DBNAME.TABLENAME where Id in (select tmp_table.Id from (select Id from DBNAME.TABLENAME where BeginTime <= '2020-12-1') tmp_table)

# 重命名表
rename TABLE old_table_name to new_table_name;

# 修复表
repair TABLE table_name;


# 查询部分状态 show porcesslist;
select * from information_schema.processlist where state like '%upda%';
select * from information_schema.processlist where state like '%wait%';
select * from information_schema.processlist where user='app';
select * from information_schema.processlist where db='jsc_gw';

# 从表中查询结果并插入另一张表内
inster into des_table(id,name,age) SELECT id,name,age from src_table;

# 如果插入时有重复的，就跳过
inster ignore into des_table(id,name,age) select id,name,age from src_table;

# 在MySQL中，如果有一个自增的主键字段，你可以在插入数据时不指定该字段，让数据库自动为你生成一个递增的值(这里用NULL表示)。如果其他字段也不需要显式指定，可以省略字段列表，直接指定要插入的值。
INSERT INTO your_table_name VALUES (NULL, 'value1', 'value2', 'value3');

删除或查询指定时间前的sql。需要表中有可识别的时间字段。然后用interval做关键字，来指定间隔时间

• DAY

• YEAR

• MONTH

• HOUR

• MINUTE

• SECOND

delete from dbname.tablename where date_time < now() - INTERVAL 10 DAY;
select * from dbname.tablename where date_time < now() - INTERVAL 10 DAY;

SQL中interval的用法

interval作为函数时 当interval作为一个函数时，它被当做一个比较函数，即interval()，如interval(4,0,1,2,3,4,5,6)，则在函数中，第一个数4作为被比较数，后面的0,1,2,3,4,5,6为比较数，然后将后面的数字依次与4进行比较，返回小于等于4的个数，所以上述结果为5，注意，只有将4后面的数字从小到大进行排列，interval函数才能正常使用，若排序混乱，可以使用，但会影响最终结果。

interval作为关键字时 当interval作为一个关键字时，表示为时间间隔，常用在date_add()、date_sub()函数中，常用于时间的加减法。

MySQL为字段添加默认时间

1. 将字段类型设为TIMESTAMP

2. 将默认值设为CURRENT_TIMESTAMP

添加一个字段，并记录每次更新时间，加上ON UPDATE CURRENT_TIMESTAMP(0)，每次操作结束会以当前时间更新该字段

ALTER TABLE `dbname`.`table_name` 
ADD COLUMN `date` timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) AFTER `Time`

修改字段，不记录更新操作时间，去掉ON UPDATE CURRENT_TIMESTAMP(0)

ALTER TABLE `81t`.`history` 
MODIFY COLUMN `date` timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP(0) AFTER `Time`

添加字段时不记录更新操作时间，去掉ON UPDATE CURRENT_TIMESTAMP(0)

ALTER TABLE `81t`.`history` 
ADD COLUMN `date_time` timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP(0) AFTER `date`

临时调整innodb_buffer_pool_size大小

show VARIABLES like "%innodb_buffer_pool_size%"
set GLOBAL innodb_buffer_pool_size=4294967296	# 4GB*1024*1024*1024

查询存储过程和函数

# 查看所有存储过程
show procedure status;

show procedure status where db = 'DBNAME';

# 查看指定库的某一个存储过程
show create procedure DB.PROCEDURE_ANME;



# 查询所有函数
show function status;

show function status where db = "DBNAME";


# 看指定库某一个函数
show create function DB.FUNCTION_NAME;

---

yum 安装mysql 5.7之后，会在mysqld.log中记录临时密码，通过mysql -uroot -p 登录后要改密码。

set global validate_password_policy=0; # 密码安全级别，如果要用简单密码，需要将该项设置为0
alter user 'root'@'localhost' identified by '123456';	#为root设置新密码

where 1=1 是为了避免where 关键字后面的第一个词直接就是 “and”而导致语法错误。where后面总要有语句，加上了1=1后就可以保证语法不会出错!

select * from table where 1=1

因为table中根本就没有名称为1的字段，所以该SQL等效于select * from table

SQL ORDER BY 关键字

ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序。 ORDER BY 关键字默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，您可以使用 DESC 关键字。

SQL ORDER BY 语法

SELECT _column_name_,_column_name_  
FROM _table_name_  
ORDER BY _column_name_,_column_name_ ASC|DESC;

示例

# 查询BeginTime时间早于指定时间的数据
select Id,BeginTime,EndTime from core_logs where BeginTime <= '2019-09-2';

# 按Id排序，看表最前面10行数据
select Id,BeginTime,EndTime from jscdb.core_logs order by Id ASC limit 0,10;  

# 按Id排序，看表最后10行数据
select Id,BeginTime,EndTime from jscdb.core_logs order by Id DESC limit 0,10;

# 按Id排序，删除表中前10行数数据
delete from jscdb.core_logs where 1=1 order by Id limit 10000;    

# 按Id排序，删除表中最后10行数数据
delete from jscdb.core_logs where 1=1 order by Id DESC 100;    

# 查看表中数据行数
select count(*) from jscdb.core_logs;   

# 看表前10行数据
select Id,BeginTime,EndTime  from jscdb.core_logs where 1=1 limit 10;   


# 删除BeginTime时间早于指定时间的数据
delete from jscdb.core_logs where Id in (select a.Id from (select * from jscdb.core_logs where BeginTime <= '2021-8-1') a);

now()函数

# 查询当前时间
select now();

# 计算时间+ - 代表加或减，INTERVAL表示间隔，单位可以是day hour second week 等常见时间单位
select (now() - INTERVAL 1 day);
select (now() + INTERVAL 1 hour);

导出查询结果为excel

echo "select * from db.tables;" | mysql -u root -p > /tmp/tables.xls

导出数据后直接通过excel打开可能会是乱码，是因为linux下导出默认是UTF-8格式，先用类似nodepad++编辑器打开tables.xls，然后转码为ANSI保存。就可以以通过excel打开。

以下为批量将当前目录下所有文件编码从GBK（ANSI）转为UTF-8

for file in `ls`;do iconv -f UTF-8 -t GBK $file -o $file;done

---

mysqldump 备份命令

常用参数	描述
-- -add-drop-table	在每个创建数据库表语句前添加删除数据库表的语句
-- -add-locks	备份数据库表时锁定数据库表
-- -all-databases	备份MySQL服务器上的所有数据库
-- -comments	添加注释信息
-- -compact	压缩模式，产生更少的输出
-- -complete-insert	输出完成的插入语句
-- -databases	指定要备份的数据库
-- -default-character-set	指定默认字符集
-- -force	当出现错误时仍然继续备份操作
-- -host	指定要备份数据库的服务器
-- -lock-tables	备份前，锁定所有数据库表
-- -no-create-db	禁止生成创建数据库语句
-- -no-create-info	禁止生成创建数据库库表语句
-- -password	连接MySQL服务器的密码
-- -port	MySQL服务器的端口号
-- -user	连接MySQL服务器的用户名
-q, --quick	加上 -q 后，不会把SELECT出来的结果放在buffer中，而是直接dump到标准输出中，顶多只是buffer当前行结果，正常情况下是不会超过 max_allowed_packet 限制的，它默认情况下是开启的。如果关闭该参数，则会把SELECT出来的结果放在本地buffer中，然后再输出给客户端，会消耗更多内存。
-w	指定备份条件
--opt	只备份结构，不备份数据

title: 语法结构
mysqldump -u用户名 -p密码 -h主机 数据库 -w “sql条件” --lock-all-tables > 路径

备份数据库及还原

备份

mysqldump -h -u root -p -P dbname > bakupfilename.sql
mysqldump -h 127.0.0.1 -u root -p123456 -P3306 mysql > /backuppath/mysql.sql
mysqldump -h 127.0.0.1 -u root -p123456 --all-databases > /backuppath/all-db.sql
mysqldump -h 127.0.0.1 -u root -p123456 --all-databases | gzip > /backuppath/all-db.gz	#备份带压缩,体积更小，恢复时先解压

示例：

mysql -u root -p123456 -e "flush tables with read lock;" 	#锁定为只能读，防止备份数据不完整。
mysqldump -h 1.1.1.1 -u root -p123456 --routines --events --force --all-databases | gzip > $(date +%F)-all-db.gz
mysql -u root -p123456 -e "unlock tables;"	#解除锁定

还原

mysql -u root -p
use mysql
source /backuppath/mysql.sql	#恢复指定库
source /backuppath/all-db.sql	#恢复所有库

还原时最好加上--default-character-set参数，指定编码格式跟原库一致，避免乱码

mysql --default-character-set=utf8 -u root -p -h 127.0.0.1 < db35

#恢复压缩备份单库
gunzip < tmp/dbname.sql.gz | mysql -uroot -p$PASSWD dbname

备份指定库时，使用--ignore-table=排除指定表不做备份,如果有个多，可以添加多个--ignore-table=

mysqldump -h 1.1.1.1 -u root -p DBNAME --ignore-table=DBNAME.TABLENAME | gzip > /backup_path/DBNAME.gz

[[Xtrabackup]]备份及还原操作

定时备份脚本

#!/bin/bash
backupdir=/data/mysql_backup
user=root
port=3306
passwd=passwd
host=1.1.1.1

{
    echo 'dump备份开始' "$(date +%Y年%m月%d日%H:%M:%S)"
    mysqldump --all-databases -u$user -p$passwd -h$host -P$port -R --single-transaction -q  | gzip > $backupdir/bak_"$(date +%Y%m%d%H%M%S)".zip 2
    echo '备份结束' "$(date +%Y年%m月%d日%H:%M:%S)"
} >> $backupdir/backup.log

find /data/mysql_backup/ -mtime +7 -exec rm -rf {} \;

---

mysqlimport命令

mysqladmin命令

参数

• create：创建数据库

• drop：删除数据库

• debug：打开调试日志并记录于erro log中

• status：显示简要状态信息

  • --sleep：设置间隔时长

  • --count：设置显示次数

• extended-status：显示扩展信息，输出mysql的和状态蛮及赋值，相当于“show global status”

• variables：显示mysqld的服务器变量

• flush-hosts：清空主机相关缓存，DNS缓存，此前因连接错误次数过多被拒的主机列表缓存

• flush-logs：日志滚动，只能滚动二进制日志和中继日志

• refresh：相当于同时使用flush-hosts和flush-logs

• flush-privileges：

• reload：

• flush-status

• flush-threads

• shutdown：

• start-slave

• stop-slave

• processlist

• ping

• kill

示例：

# 显示简要状态信息
mysqladmin -uroot -p -h 127.0.0.1 status
Uptime: 5944393  Threads: 7  Questions: 1524184  Slow queries: 0  Opens: 2070  Flush tables: 1  Open tables: 1639  Queries per second avg: 0.256

# 显示简要状态信息，-i 指定间隔秒，-c 指定显示次数
mysqladmin -uroot -p -h 127.0.0.1 -i 2 -c 3 status
Uptime: 5944397  Threads: 7  Questions: 1524187  Slow queries: 0  Opens: 2070  Flush tables: 1  Open tables: 1639  Queries per second avg: 0.256
Uptime: 5944399  Threads: 7  Questions: 1524189  Slow queries: 0  Opens: 2070  Flush tables: 1  Open tables: 1639  Queries per second avg: 0.256
Uptime: 5944401  Threads: 7  Questions: 1524236  Slow queries: 0  Opens: 2070  Flush tables: 1  Open tables: 1639  Queries per second avg: 0.256


# 显示当前线程列表
mysqladmin -uroot -p -h 127.0.0.1 processlist

# ping mysql服务，判断是否启动
mysqladmin -uroot -p -h 127.0.0.1 ping
mysqld is alive

# 结束指定线程
mysqladmin -uroot -p -h 127.0.0.1 kill 998,999

mysqlshow命令

示例：

# 显示指定库各表的行数和列数，一个-v 表示显示列，两个-v表示显示列和行
mysqlshow -uroot -p -h 127.0.0.1 mysql -v -v

---

Mysql其它错误处理请参考[[MySQL错误处理记录]]