mysql基本操作和授权

时间：2022-06-18 04:09:44

mysql基本操作和授权：（介绍5.7的和5.6很多地方不一样哦）
MySQL Server version: 5.7.23
本文的数据库test
本文的表名test
本文的用户名test
show databases; 命令查看已经创建了哪些数据库。
show columns from test 或者desc test;获取表结构命令:
shou tables 查看所有的表
use database1; 切换数据库
show grants; 查看当前用户的权限
show grants for [email protected]"%"; 查看其它用户的权限， [email protected]"%"代表user表中的user和host字段
flush privileges; 刷新系统权限
mysql中没有像oracle set line 100 pages 9999的设置，可以在最后加入G
例如:select * from userG;

mysql -h 主机名 -u 用户名 -p
-p:密码登录, 如果登录的用户名密码为空, 可以忽略此选项。
-D:所选择的数据库名

重置mysql的数据库密码
1、首先停掉mysql 数据库 一般是安装在/etc/init.d/mysqld stop
2、修改mysql的配置文件 /etc/my.cnf
最后一行添加 skip-grant-tables 表示可以跳过权限去登录
3、重启 mysql 数据库 /etc/init.d/mysqld start
3、使用 mysql -u root -p
4、修改root密码。
update user set password=PASSWORD("123456") where user=‘root‘;
5、修改配置文件删除或禁用skip-grant-tables这行。
注：mysql的数据库老版本用参数authentication_string，新版本用参数password
update user set authentication_string=password(‘123456‘) where user=‘root‘;
update user set password=password(‘123456‘) where user=‘root‘;
flush privileges; --刷新系统权限表

创建数据库
create database test character set gbk;
测试建表
create table test(id int,name varchar(20),bianma varchar(20));
INSERT INTO test VALUES (1,‘tom1‘,‘13211‘);
INSERT INTO test VALUES (2,‘tom2‘,‘13212‘);
INSERT INTO test VALUES (3,‘tom3‘,‘13213‘);
INSERT INTO test VALUES (4,‘tom4‘,‘13214‘);

删除数据库
drop database test_database;
删除表
drop table test_tab;

创建用户命令：
GRANT USAGE ON . TO ‘test‘@‘localhost‘ IDENTIFIED BY ‘123456‘ WITH GRANT OPTION;
或者
create user ‘test1‘@‘%‘ identified by ‘123456‘;
两条命令都可以，用户名test密码是123456

删除用户命令：
mysql> drop user ‘test1‘@‘%‘
mysql> drop user ‘test‘@‘localhost‘
注意删除格式：‘user‘@‘host‘

mysql数据库授权:
grant 权限 on 数据库对象 to 用户
权限：增删改查， insert,delete,update,select,create,alter,drop,all等
references 外键权限，create temporary 创建临时表权限，index 创建索引权限，create view和show view 创建视图和查看视图权限，create routine和alter routine 创建和修改存储过程权限，execute 操作函数权限，
数据库对象:表名，数据库名.表名，test.，.*等
用户：要取得权限的用户，test，[email protected]，[email protected]"%"，格式的意思是user表中的user和host

授权用户test只查看表test中的两个字段的权限
grant select(name,id) on test to [email protected]‘%‘ ;

授权test用户拥有test数据库的所有权限：
grant all privileges on test. to [email protected] identified by ‘123456‘; --privileges可省略不写
grant all on test. to [email protected];
授权部分权限给用户
grant select,update on test. to [email protected] identified by ‘123456‘;
grant select,update on test. to [email protected] ;

授权test用户拥有所有数据库的某些权限： 　
grant select,delete,update,create,drop on . to [email protected]"%" identified by "123456";
或者
grant all on test.* to [email protected];

test用户可以将test数据库中的所有表的select权限授权给其它用户
grant select on test. to test with grant option;
注：以上例句将替换成表名，将test数据库中的某个表作为数据库对象

取消授权，撤销授权，授权收回
revoke all on . from test;
授权和取消授权的语句基本一致，将grant和revoke互换，from和to互换就可以。
取消授权
revoke select on . from test;
授权
grant select on . to test;

mysql能够像Oracle的sqlplus那样设置pagesize和linesize
select * from table_nameG;

mysql delete中where后能套用select
delete test_user from test_user a, (select id from test_user where id < 10) b
where a.id = b.id

表数据太大只查看5行
select from gp_plat_user where rownum < 6;
select from gp_plat_user limit 6;

错误集锦：
报错1.Ignoring query to other database
mysql> show databases;
Ignoring query to other database
mysql> show user;
Ignoring query to other database
方案:
登录的使用参数 -u
报错2.
ERROR 1130 (HY000): Host ‘10.1.1.10‘ is not allowed to connect to this MySQL server
[[email protected] logs]# mysql -uroot -p123456 -h 10.1.1.10
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1130 (HY000): Host ‘10.1.1.10‘ is not allowed to connect to this MySQL server
方案:跳过密码登录，修改数据库中的host字段

mysql> select host,user from user where user=‘root‘;
----------- ------
| host | user |
----------- ------
| localhost | root |
----------- ------
1 row in set (0.00 sec)
mysql> update user set host = ‘%‘ where user = ‘root‘;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> commit; --这个命令也可以提交哦
Query OK, 0 rows affected (0.00 sec)

mysql> select host,user from user where user=‘root‘;
------ ------
| host | user |
------ ------
| % | root |
------ ------
1 row in set (0.00 sec)

报错3.
登录时报错：
ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost‘ (using password: NO)
方案：mysql -uroot -p123456
报错4.登录后查询数据库报错：
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
提示说让操作之前先要用命令ALTER USER修改密码
我使用下面的alter命令修改后，不好使，就用set修改的，修改完后可以使用。
方案： alter user ‘root‘@‘localhost‘ identified by ‘123456‘; --不好使
set password=password("123456"); --好使

错误5.
mysql> insert into user(Host,User,authentication_string) values("localhost","test",password("123456"));
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
方案： set global validate_password_policy=0;
set global validate_password_length=1;
查看的方法：select @@validate_password_policy;
select @@validate_password_length;
详情可以看这个技术博主https://www.cnblogs.com/ivictor/p/5142809.html

错误6.
mysql> insert into user(Host,User,authentication_string) values("localhost","test",password("123456"));
ERROR 1364 (HY000): Field ‘ssl_cipher‘ doesn‘t have a default value
报错ssl_cipher字段不能为空，mysql添加用户不能直接insert user表中。
方案：网上说修改配置文件my.cnf中由
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES改为：sql_mode=NO_ENGINE_SUBSTITUTION
对于5.7版本的mysql不好使，使用如下的命令进行创建新用户好使
GRANT USAGE ON . TO ‘test‘@‘localhost‘ IDENTIFIED BY ‘123456‘ WITH GRANT OPTION;
mysql> GRANT USAGE ON . TO ‘test‘@‘localhost‘ IDENTIFIED BY ‘123456‘ WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select user,host from user;
--------------- -----------
| user | host |
--------------- -----------
| root | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| test | localhost |
--------------- -----------
4 rows in set (0.00 sec)
错误7：
[[email protected] logs]# mysql -utest -ptest
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user ‘test‘@‘localhost‘ (using password: YES)
方案：和错误2一样
修改host：update user set host = ‘%‘ where user = ‘test‘;

navicat的快捷键：
Ctrl Q、Ctrl N 打开查询窗口
Ctrl / 注释sql语句
Ctrl Shift / 解除注释
Ctrl R 运行查询窗口的sql语句
F6 打开一个mysql命令行窗口
Ctrl L 删除一行
Ctrl W 关闭一个查询窗口
Ctrl D 表的数据显示显示页面切换到表的结构设计页面，但是在查询页面写sql时是复制当前行

和权限有关的几张表

相关文章