Mysql

Mysql基本知识系列四常见操作

一、mysql连接工具种类

1.1 Workbench

workbench是mysql官方的一个连接工具，下载地址：https://downloads.mysql.com/archives/workbench/

直接解压安装就行，重点是他是开源免费的

1.2 Navicat

navicat 比较常见，现在应该是navicat15，安装方式很简单，去官网下载最新版本http://www.navicat.com.cn/products/

安装好之后只有14天免费试用，中文破解版见在最下方，需要使用注册机。

1.3 mysql命令行工具

这个是需要掌握一些基础命令，如基础的使用命令行查询，然后创建用户，权限分配等。使用mysql -uroot -p，然后输入密码，即可进入执行sql语句。

二、常见命令

2.1 mysql连接

mysql -u user -p
mysql -u user -p db_name # 直接进入指定数据库

2.2 mysql 常见命令

注意：命令最好不要复制执行，手动输入一遍可以防止中文字符和编码问题，尤其是在linux服务器中

linux 环境下重启mysql，配置文件一般是/etc/my.cnf
```
systemctl restart mysqld.service
```

查看mysql版本号和日期，时间

SELECT version(), current_date, now(); # 查看mysql版本号和日期、时间 

SELECT version(); select now(); select current_date; # 分开写则表格分开

查看所有数据库，并切换到制定数据库

SHOW DATABASES; # 查看所有数据库
use test;

\g和;号一样，都是结束符
\c 撤销先前输入即可以继续在后面输入
\G 按行输出,加上后，会有一个行数输出

操作数据库

# 数据库名称组成除了三大项还可以含$，但不能是纯数字
CREATE DATABASE db_name; # 创建数据库
CREATE DATABASE db_name DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
# 按照utf8规则排序
# utf8mb4支持enoji表情
ALTER DATABASE db_name DEFAULT CHARSET SET utf8;  # 修改数据库
DROP DATABASE db_name; # 删除数据库

查看数据库所在位置

show VARIABLES LIKE 'datadir'; # 查看数据库所在位置

查看表属性

SHOW TABLES; # 查看所有表
DESCRIBE db_name; # 查看表的描述，也可以用 DESC table;

执行sql文件
```
source /usr/local/bak/abc.sql;
```
导出，导入sql文件
```
#导出现有数据库结构+数据:
mysqldump -u用户名 -p 数据库名称 > 导出文件路径
#导出现有数据库结构：
mysqldump -u用户名 -p -d 数据库名称 > 导出文件路径 
#导入sql文件数据：
mysqldump -uroot -p  数据库名称 < 导入文件路径
```
注意：linux服务器中，执行mysqldump -uroot -p密码 test > /usr/local/bak/abc.sql，会报错Warning

mysqldump: [Warning] Using a password on the command line interface can be insecure.

是因为有些版本不支持命令中使用了密码不安全，解决方法有两种
- 使用mysqldump -uroot -p test > /usr/local/bak/abc.sql然后回车之后输入密码
- 在mysql配置文件/etc/my.cnf中添加mysqldump的密码配置, 然后使用mysqldump -uroot test > /usr/local/bak/abc.sql
```
[mysqldump] 
password=123456
```

三、权限配置

3.1 用户创建

注意：

创建完用户后，都需要刷新一下
```
flush privileges;
```
账号用户名@主机来唯一表示的，有时会加上单引号**‘用户名’@‘主机’**来表示，主要是因为区别特殊字符，如账号中的@符号等

3.1.1 账户名构成方式：

用户名@主机（所以可以出现重复的用户名，跟其他的数据库不一样）

用户名：16个字符以内

主机名：可以用主机名和IP地址，也可以用通配符

通配符说明：172.18.10.%（IP地址为172.18.10段的所有IP地址都可以访问）

3.1.2 create user创建用户

Sql ： create user 'username‘@’host' identified by 'password' 其中密码是可选的

create user 'wzw'@'127.0.0.%' identified by '123'

说明：该方法创建出来的用户只有连接数据库的权限，需要后续继续授权；

这种方式虽能创建用户，且在mysql.user表中能查到，但是并没有权限

3.1.3 mysql.user表插入记录

因为数据库的用户信息都是保存在mysql.user这张表的，所以直接对该表进行插入语句，即可完成用户的创建，但是也没有权限；

use mysql;

# 插入账户，authentication_string是mysql5.7的字段，5.6为password
# ssl_cipher,x509_issuer,x509_subject是不为null,所以需要赋默认值
insert into user (Host,User,authentication_string,ssl_cipher,x509_issuer,x509_subject) values('%','wzw',password('123'),'','','');

flush privileges;

# 修改密码
update user set authentication_string=password('1234') where user='wzw' and host='%';

注意：由于mysql不同大版本中mysql.user结构不一样，网上查找查找的sql语句，需要区别一下版本，此处我的版本是5.7

3.1.4 GRANT命令创建用户

创建用户最正确的方式，同时还可以分配权限

# *.* 是通配符，表示所有数据库下的所有表, WITH GRANT OPTION表示具有grant权限，不建议加上
grant all on *.* to wzw@localhost identified by '123' WITH GRANT OPTION;

说明：用户wz分配可对所有数据库的所有表进行所有操作的权限，并设定口令为123。

3.2 权限分配

3.2.1 权限查看，撤销

#查看权限
show grants for wzw@localhost;

#权限撤销，注意revoke 必须和 grant 相对应，当初怎么grant 的，就必须怎么 revoke
revoke all on *.* from wzw@localhost;

3.2.2 grant语法

# grant语法
GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH {GRANT OPTION | resource_option} ...]

# grant 可以针对表，函数，存在过程赋权
object_type: {
    TABLE
  | FUNCTION
  | PROCEDURE
}

# grant赋权对象
priv_level: {
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name
}

# grant 密码验证方式
auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
  | IDENTIFIED BY PASSWORD 'auth_string'
}

# 不常见
tls_option: {
    SSL
  | X509
  | CIPHER 'cipher'
  | ISSUER 'issuer'
  | SUBJECT 'subject'
}

# grant 其他限制，如每小时可执行多少次查询等
resource_option: {
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}

权限类别，priv_type（表格来源官网文档https://dev.mysql.com/doc/refman/5.7/en/grant.html）

Privilege	Meaning and Grantable Levels
`ALL [PRIVILEGES\]`	Grant all privileges at specified access level except `GRANT OPTION` and `PROXY`.
`ALTER`	Enable use of `ALTER TABLE`. Levels: Global, database, table.
`ALTER ROUTINE`	Enable stored routines to be altered or dropped. Levels: Global, database, routine.
`CREATE`	Enable database and table creation. Levels: Global, database, table.
`CREATE ROUTINE`	Enable stored routine creation. Levels: Global, database.
`CREATE TABLESPACE`	Enable tablespaces and log file groups to be created, altered, or dropped. Level: Global.
`CREATE TEMPORARY TABLES`	Enable use of `CREATE TEMPORARY TABLE`. Levels: Global, database.
`CREATE USER`	Enable use of `CREATE USER`, `DROP USER`, `RENAME USER`, and `REVOKE ALL PRIVILEGES`. Level: Global.
`CREATE VIEW`	Enable views to be created or altered. Levels: Global, database, table.
`DELETE`	Enable use of `DELETE`. Level: Global, database, table.
`DROP`	Enable databases, tables, and views to be dropped. Levels: Global, database, table.
`EVENT`	Enable use of events for the Event Scheduler. Levels: Global, database.
`EXECUTE`	Enable the user to execute stored routines. Levels: Global, database, routine.
`FILE`	Enable the user to cause the server to read or write files. Level: Global.
`GRANT OPTION`	Enable privileges to be granted to or removed from other accounts. Levels: Global, database, table, routine, proxy.
`INDEX`	Enable indexes to be created or dropped. Levels: Global, database, table.
`INSERT`	Enable use of `INSERT`. Levels: Global, database, table, column.
`LOCK TABLES`	Enable use of `LOCK TABLES` on tables for which you have the `SELECT` privilege. Levels: Global, database.
`PROCESS`	Enable the user to see all processes with `SHOW PROCESSLIST`. Level: Global.
`PROXY`	Enable user proxying. Level: From user to user.
`REFERENCES`	Enable foreign key creation. Levels: Global, database, table, column.
`RELOAD`	Enable use of `FLUSH` operations. Level: Global.
`REPLICATION CLIENT`	Enable the user to ask where source or replica servers are. Level: Global.
`REPLICATION SLAVE`	Enable replicas to read binary log events from the source. Level: Global.
`SELECT`	Enable use of `SELECT`. Levels: Global, database, table, column.
`SHOW DATABASES`	Enable `SHOW DATABASES` to show all databases. Level: Global.
`SHOW VIEW`	Enable use of `SHOW CREATE VIEW`. Levels: Global, database, table.
`SHUTDOWN`	Enable use of mysqladmin shutdown. Level: Global.
`SUPER`	Enable use of other administrative operations such as `CHANGE MASTER TO`, `KILL`, `PURGE BINARY LOGS`, `SET GLOBAL`, and mysqladmin debug command. Level: Global.
`TRIGGER`	Enable trigger operations. Levels: Global, database, table.
`UPDATE`	Enable use of `UPDATE`. Levels: Global, database, table, column.
`USAGE`	Synonym for “no privileges”

3.2.3 账号赋权

赋权，如果该账号不存在，则创建账号

grant all on *.* to wzw_test@localhost identified by '123';

赋权，如果该账号不存在，则报错ERROR 1133 (42000): Unknown error 1133
```
grant all on *.* to wzw2@'%';
```

一般开发人员所需权限SELECT,UPDATE,DELETE,INSERT,CREATE,EXECUTE

GRANT INSERT,UPDATE,DELETE,SELECT,CREATE,EXECUTE ON *.* TO 'wzw2_test'@'%';

安装包和注册机

http://www.uzzf.com/soft/638797.html

如果觉得文章对你有用，请随意赞赏

mysql 数据库

Mysql基本知识系列四常见操作

https://www.wuqiongda.store/archives/21091502

作者

尘浮者

发布于

2021-09-15

更新于

2024-08-27

许可协议

CC BY 4.0

Mysql基本知识系列四常见操作

Mysql基本知识系列四常见操作

一、mysql连接工具种类

1.1 Workbench

1.2 Navicat

1.3 mysql命令行工具

二、常见命令

2.1 mysql连接

2.2 mysql 常见命令

三、权限配置

3.1 用户创建

3.1.1 账户名构成方式：

3.1.2 create user创建用户

3.1.3 mysql.user表插入记录

3.1.4 GRANT命令创建用户

3.2 权限分配

3.2.1 权限查看，撤销

3.2.2 grant语法

3.2.3 账号赋权

安装包和注册机

作者

发布于

更新于

许可协议

评论