mysql使用大全

前言

着重介绍mysql的使用

使用

创建mysql用户

mysql> create user ‘caiji_cex’@’%’ identified by ‘xxx’;

修改数据库用户密码

• 语法一

mysql> set password for '用户名'@'localhost' = password('新密码');

• 示例一

mysql> set password for 'xxx'@'%' = password('xxx');

• 语法二

mysqladmin -u 用户名 -p 旧密码 password 新密码

• 示例二

mysqladmin -u root -p xxx password xxx

• 语法三

ALTER USER '用户名'@'%' IDENTIFIED BY '用户密码';

• 示例三

ALTER USER 'root'@'%' IDENTIFIED BY 'xxx';

替换字段部分内容

• 语法

UPDATE `table_name` SET `field_name` = replace (`field_name`,'from_str','to_str') WHERE `field_name` LIKE '%from_str%'

• 示例

update test set school = replace(school, '/a/', '/b/');

授权所有客户端可以访问数据库

update mysql.user set host='%' where user='root';
flush privileges;

备份数据库

• 语法:

mysqldump -u databaseUser -p databasePassword 数据库名 > xxx.sql/fileName

还原数据库

• 语法:

mysql -u databaseUser -p databasePassword 数据库名 < xxx.sql/fileName

授权mysql用户对某个数据库有所有权，并在所有的客户端可以访问

grant all privileges on databaseName.* to 'databaseUsername'@'%' identified by 'databasePassword' with grant option;
flush privileges;

回收mysql用户对某个数据库有所有权，并在所有的客户端可以访问

revoke drop on databaseName.* to 'databaseUsername'@'%' identified by 'databasePassword' with grant option;
flush privileges;

查询的数据以时间顺序输出

• 注：
  DESC(降序) ASC(升序)

SELECT `字段1`, `字段2`  FROM `表名` ORDER BY `时间字段` DESC ;
or
SELECT *  FROM `表名` ORDER BY `时间字段` DESC ;

统计某个字段其值的数量

SELECT COUNT( *) FROM `表名` WHERE 字段=value;

delete(删除)

• 语法:

DELETE FROM 表名 WHERE 条件(字段名=值)

删除表中的某行

DELETE FROM user WHERE Host=‘localhost’

update(更新)

• 语法:

UPDATE 表名 SET WHERE 字段名=值 条件(字段名=值)

更新表中的某行

DELETE FROM user WHERE Host=‘localhost’

select(查询)

• 语法:

SELECT */字段 FROM 表名 [WHERE 条件]

查询表中的所有数据

SELECT * FROM user

insert(插入)

• 语法:

insert into 表名 字段 values 值

rename表名

• 要求有alter和drop原表， create和insert新表的权限

更新表中的某行

insert into info (name, class) values (‘shangsi’, ‘four’)

配置相关参数

设置READ-COMMITTED作为默认隔离级别

SET GLOBAL tx_isolation=‘READ-COMMITTED’;

设置sql_mode = NO_AUTO_VALUE_ON_ZERO;

set sql_mode = NO_AUTO_VALUE_ON_ZERO;

创建数据库，并把character设为utf8，collate为utf8_bin

create database 数据库名 character set utf8 collate utf8_bin;

更新数据库character为utf8，collate为utf8_bin

alter database 数据库名 character set utf8 collate utf8_bin;
alter database wikidb character set utf8mb4 collate utf8mb4_bin;

• 注:
  需要重启数据库

mysql开启慢查询相关功能

# 开启慢查询
slow-query-log=ON
# 定义查过多少秒的查询算是慢查询
long_query_time=3
# 定义慢查询日志的路径
slow_query_log_file=/data/mysql/slow.log

重启mysql服务

查看定义的慢查询时间

show variables like ‘%storage_engine%’;

mysql> show VARIABLES like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 3.000000 |
+-----------------+----------+
1 row in set (0.06 sec)

查看慢查询日志信息

docker exec mysql mysqldumpslow -s r -t 10 /var/lib/mysql/mysql-slow.log

查询数据库当前设置的最大连接数

show variables like ‘%max_connection%’;

mysql> show variables like '%max_connection%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1000  |
+-----------------+-------+
1 row in set (0.03 sec)

• 设置最大连接数

set global max_connections=1000;

查询数据库线程数

show status like ‘Threads%’;

mysql> show status like  'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 3     |
| Threads_connected | 28    |				# Threads_connected ：这个数值指的是打开的连接数
| Threads_created   | 312   |				# 表示创建过的线程数
| Threads_running   | 1     |					# 这个数值指的是激活的连接数，这个数值一般远低于connected数值
+-------------------+-------+
4 rows in set (0.05 sec)

查看数据库是否开启慢查询

show variables like ‘%storage_engine%’;

mysql> show VARIABLES like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
1 row in set (0.04 sec)

查看数据库存储引擎

show variables like ‘%storage_engine%’;

mysql> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0.04 sec)

设置数据库存储引擎为innoDB

set default_storage_engine=InnoDB;

设置max_allowed_packet为25m

set global max_allowed_packet=25610241024;

查看binlog是否开启

SHOW GLOBAL VARIABLES LIKE ‘%log_bin%’;

示例

mysql> SHOW GLOBAL VARIABLES LIKE '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
+---------------------------------+--------------------------------+
5 rows in set (0.03 sec)

查看当前正在写入的binlog文件

show master status;

示例

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.11 sec)

查看当前正在写入的日志文件中的binlog事件

show binlog events; # show binlog events in ‘mysql-bin.000001’;

示例

mysql> show binlog events;
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.34-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids |         1 |         154 |                                       |
| mysql-bin.000001 | 154 | Stop           |         1 |         177 |                                       |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
3 rows in set (0.06 sec)

显示文件列表

show binary logs;

示例

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       154 |
+------------------+-----------+
2 rows in set (0.06 sec)

查看binlog相关参数

show variables like “%binlog%”;

示例

mysql> show variables like "%binlog%";
+--------------------------------------------+----------------------+
| Variable_name                              | Value                |
+--------------------------------------------+----------------------+
| binlog_cache_size                          | 32768                |
| binlog_checksum                            | CRC32                |
| binlog_direct_non_transactional_updates    | OFF                  |
| binlog_error_action                        | ABORT_SERVER         |
| binlog_format                              | ROW                  |
| binlog_group_commit_sync_delay             | 0                    |
| binlog_group_commit_sync_no_delay_count    | 0                    |
| binlog_gtid_simple_recovery                | ON                   |
| binlog_max_flush_queue_time                | 0                    |
| binlog_order_commits                       | ON                   |
| binlog_row_image                           | FULL                 |
| binlog_rows_query_log_events               | OFF                  |
| binlog_stmt_cache_size                     | 32768                |
| binlog_transaction_dependency_history_size | 25000                |
| binlog_transaction_dependency_tracking     | COMMIT_ORDER         |
| innodb_api_enable_binlog                   | OFF                  |
| innodb_locks_unsafe_for_binlog             | OFF                  |
| log_statements_unsafe_for_binlog           | ON                   |
| max_binlog_cache_size                      | 18446744073709547520 |
| max_binlog_size                            | 1073741824           |
| max_binlog_stmt_cache_size                 | 18446744073709547520 |
| sync_binlog                                | 1                    |
+--------------------------------------------+----------------------+
22 rows in set (0.15 sec)

查看binlog模式

SHOW GLOBAL VARIABLES LIKE ‘%binlog_format%’;

示例

mysql> SHOW GLOBAL VARIABLES LIKE '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.03 sec)

查看当前数据库字符集/查看数据库的编码格式

show VARIABLES like ‘character%’;

查看数据库的排序规则

show variables like ‘collation%’;

查看当前数据库编码

use databasename;show variables like ‘character_set_database’;

修改表字符集

ALTER TABLE 表名 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

修改列字符集

ALTER TABLE 表名 CHANGE 列名 列名 VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

创建表的id字段为自增长，并为主键

create table table_name(id int not null auto_increment primary key);

查询非root用户

select * from mysql.user where not(user = 'root');

查看最近修改的表

SELECT * FROM information_schema.TABLES
WHERE information_schema.TABLES.TABLE_SCHEMA = ‘table_name’

知识点

• 四种事务隔离
  READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE

mysql使用说明文档

官方使用说明文档