mysql学习(二)

最新推荐文章于 2024-07-29 13:31:56 发布

the_power

最新推荐文章于 2024-07-29 13:31:56 发布

阅读量132

点赞数

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/the_power/article/details/99637702

版权

数据库专栏收录该内容

12 篇文章 0 订阅

订阅专栏

mysql学习(二)

创建用户

方式1

# 首先要使用root账号登录
#相当于对mysql数据库的user表进行插入操作
#切换到mysql数据库
>use mysql;
Database changed

#插入用户

#方式1
> INSERT INTO USER (HOST,USER,Select_priv,Insert_priv,Update_priv,password) VALUES('localhost','gust3','Y','y','y','gust123');
#注：这种方式对于mysql5.1以下的生效
#对于5.7，它替换了password->authentication_string
# 找不到password字段，此时将字段换位authentication_string，又会报下面错误(没有默认值)
#ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value错误

如果是在SQLyog一类的图形界面里，方式1是支持的
方式1里面`PASSWORD(‘gust123’),是调用加密函数，这个函数在8版本移除了，可以使用MD5()代替
5.7以前user表密码字段是password，5.7中换成了authentication_string
flush privileges;重新载入授权表，否则需要重启才能通过新用户登录
插入用户时的权限可以查询user表获得

方式2

#添加用户：u2,密码:123456
mysql> GRANT USAGE ON *.* TO 'u2'@'localhost' IDENTIFIED BY '123456' WITH GRANT
OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)
#刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

#或者是
mysql> grant select,insert,update,delete,create,drop on tutorials.* to 'zara'@'localhost' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

方式2通过SQL的GRANT命令添加用户

方式3

CREATE USER 'newU'@'localhost' IDENTIFIED BY '123456';#该用户目前没有任何权限
flush privileges;
exit
#退出用刚刚创建的登录

mysql -unewU -p123456

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
#可以看到查询不出数据库
mysql> use book;
ERROR 1044 (42000): Access denied for user 'newU'@'localhost' to database 'book'
#访问一个已知存在的book数据库，被禁止
#需要使用grant给用户赋予权限(切换到root管理员账号，给newU赋权)
grant all privileges on *.* to newU@localhost;
flush privileges;

管理mysql常用命令

use 数据库名
选择要操作的mysql数据库
```
mysql> use mysql;
Database changed
```

show databases

列出mysql中的所有数据库列表

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| book               |
| book_new           |
| chat               |
| mybatis            |
| mysql              |
| performance_schema |
| shool              |
| solo               |
| sys                |
| user               |
| xskc               |
+--------------------+
12 rows in set (0.00 sec)

show index from

显示数据表的详细索引信息，包括主键

mysql> show index from book.book;
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardi
nality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+---------------+
| book  |          0 | PRIMARY  |            1 | id          | A         |
     3 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+---------------+
1 row in set (0.07 sec)

show table status 数据库 like []

输出mysql数据库管理系统的性能及统计信息

#显示数据库 book 中所有表的信息
mysql> show table status from book;

#显示 表鸣以 bo 开头的表的信息
mysql> show table status from book like 'bo%';

#加上 \G ,查询结果按列打印

show grant 用户

mysql> show grants for newU@localhost;
+---------------------------------------------------+
| Grants for newU@localhost                         |
+---------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'newU'@'localhost' |
+---------------------------------------------------+
1 row in set (0.00 sec)

*查看newU用户的权限

show create database 数据库 or show create table 表

mysql> use book_new;
Database changed
mysql> show create database book_new;
+----------+-------------------------------------------------------------------+

| Database | Create Database                                                   |

+----------+-------------------------------------------------------------------+

| book_new | CREATE DATABASE `book_new` /*!40100 DEFAULT CHARACTER SET utf8 */ |

+----------+-------------------------------------------------------------------+

1 row in set (0.00 sec)

上边是查看数据库的信息

下边查看表的信息

show create table book.admin;
| Table | Create Table

| admin | CREATE TABLE `admin` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `ausername` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `phone` varchar(50) NOT NULL,
  `touxiang` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 |

1 row in set (0.00 sec)

show variables like “%character”

mysql> show variables like '%character%';
+--------------------------+---------------------+
| Variable_name            | Value               |
+--------------------------+---------------------+
| character_set_client     | gbk                 |
| character_set_connection | gbk                 |
| character_set_database   | utf8                |
| character_set_filesystem | binary              |
| character_set_results    | gbk                 |
| character_set_server     | latin1              |
| character_set_system     | utf8                |
| character_sets_dir       | F:\mysql_1000\mysql |
+--------------------------+---------------------+
8 rows in set, 12 warnings (0.01 sec)

查看数据库的编码

grant命令专项

普通用户

对普通用户赋予权限
mysql> grant select on testdb.* to 'common_user'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

#一次性全都给
grant select, insert, update,delete on testdb.* to'common_user'@'%' identified by '123456';
给common_user用户，赋予对testdb数据库的所有表查询、插入、修改、删除权限

数据库开发人员

grant 创建、修改、删除 MySQL 数据表结构权限

mysql> grant create on testdb.* to 'developer'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

给开发人员权限，可以是create,alter,drop等，还可以给外键、临时表和索引权限

#给developer 对testdb所有表的 外键权限
mysql> grant references on testdb.* to developer@'%';
Query OK, 0 rows affected (0.00 sec)

#给developer 对testdb的 临时表权限
mysql> grant create temporary tables on testdb.* to developer@'%';
Query OK, 0 rows affected (0.00 sec)

#给developer 对testdb所有表的 索引权限
mysql> grant index on testdb.* to developer@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;#刷新权限
Query OK, 0 rows affected (0.00 sec)

由于上边已经创建过了developer这个用户，不需要再次创建了

grant操作mysql视图、查看视图源码权限

mysql> grant create view on testdb.* to developer@"%";#操作视图
Query OK, 0 rows affected (0.00 sec)

mysql> grant show view on testdb.* to developer@'%';#查看视图源码
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;#刷新权限
Query OK, 0 rows affected (0.00 sec)

grant 操作 mysql 存储过程、函数权限

mysql> grant create routine on testdb.* to developer@'%';#创建存储过程
Query OK, 0 rows affected (0.00 sec)

mysql> grant alter routine on testdb.* to developer@'%';#修改存储过程
Query OK, 0 rows affected (0.00 sec)

mysql> grant execute on testdb.* to developer@'%';#执行存储过程
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;#刷新
Query OK, 0 rows affected (0.00 sec)

普通DBA权限

grant all privileges on testdb to 'dba'@'%' identified by '123456';

高级DBA权限

grant all on *.* to 'rootDBA'@'%' identified by '123456';

参考链接

https://www.runoob.com/mysql/mysql-administration.html

the_power

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录