mysql（杂）

数据库

https://blog.csdn.net/c361604199/article/details/79479398（常用）

 

 

# 创建一个数据库 ：CREATE DATABASE 库名;

# 创建一个表 ： CREATE TABLE 表名(id INT, name VARCHAR(10));

# 添加列   ： alter table 表名 add column 列名 varchar(30);

# 删除行数据 ：delect from 表名 where condition；

# 添加主键 ：alter table class add primary key (id);

# 添加check约束 ：alter table moviestar add constraint 约束名 check(gender='男' or gender='女');

# 添加外键约束 ： ALTER TABLE  主表名 ADD CONSTRAINT 约束名 FOREIGN KEY(属性) REFERENCES 副表(属性）;

# 修改数据 ：UPDATE cs_user SET gender = '男' WHERE id = 4

 

文件导入

• use testdb;
• mysql> desc movie_tbl;
•  
• mysql> LOAD DATA INFILE '/var/lib/mysql-files/Movies.csv' INTO TABLE movie_tbl
   

用户管理

mysql>use mysql;

查看

mysql> select host,user,password from  user  ;

创建

mysql> create user  zx_root    IDENTIFIED  by 'xxxxx';   //identified by 会将纯文本密码加密作为散列值存储

修改

mysql>rename   user  feng  to   newuser；//mysql 5之后可以使用，之前需要使用update 更新user表

删除

mysql> drop  user newuser;   //mysql5之前删除用户时必须先使用revoke 删除用户权限，然后删除用户，mysql5之后drop 命令可以删除用户的同时删除用户的相关权限

更改密码

mysql> set password  for  zx_root =password('xxxxxx');

 mysql> update  mysql.user  set  password=password('xxxx')  where user='otheruser'

查看用户权限

mysql> show grants for zx_root;

赋予权限

mysql> grant  select  on dmc_db.*   to  zx_root;

回收权限

mysql> revoke  select on dmc_db.*  from  zx_root;  //如果权限不存在会报错

 

连接mysql的通用方式，可以省略其中一部分参数 ：

    mysql -uroot -h 127.0.0.1 -P 3306 -p

 

    注意：大写P是端口  小写p是密码

    

上面的命令也可使用多个权限同时赋予和回收，权限之间使用逗号分隔

mysql> grant  select，update，delete  ，insert   on dmc_db.*   to   zx_root;

如果想立即看到结果使用

flush  privileges ;

命令更新 

 

设置权限时必须给出一下信息

1，要授予的权限

2，被授予访问权限的数据库或表

3，用户名

grant和revoke可以在几个层次上控制访问权限

1，整个服务器，使用 grant ALL  和revoke  ALL

2，整个数据库，使用on  database.*

3，特点表，使用on  database.table

4，特定的列

5，特定的存储过程

 

user表中host列的值的意义

%               匹配所有主机

localhost     localhost不会被解析成IP地址，直接通过UNIXsocket连接

127.0.0.1       会通过TCP/IP协议连接，并且只能在本机访问；

::1                  ::1就是兼容支持ipv6的，表示同ipv4的127.0.0.1

 

 

grant 普通数据用户，查询、插入、更新、删除 数据库中所有表数据的权利。

grant select on testdb.* to common_user@’%’

grant insert on testdb.* to common_user@’%’

grant update on testdb.* to common_user@’%’

grant delete on testdb.* to common_user@’%’

或者，用一条 MySQL 命令来替代：

grant select, insert, update, delete on testdb.* to common_user@’%’

9>.grant 数据库开发人员，创建表、索引、视图、存储过程、函数。。。等权限。

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

grant create on testdb.* to developer@’192.168.0.%’;

grant alter on testdb.* to developer@’192.168.0.%’;

grant drop on testdb.* to developer@’192.168.0.%’;

grant 操作 MySQL 外键权限。

grant references on testdb.* to developer@’192.168.0.%’;

grant 操作 MySQL 临时表权限。

grant create temporary tables on testdb.* to developer@’192.168.0.%’;

grant 操作 MySQL 索引权限。

grant index on testdb.* to developer@’192.168.0.%’;

grant 操作 MySQL 视图、查看视图源代码 权限。

grant create view on testdb.* to developer@’192.168.0.%’;

grant show view on testdb.* to developer@’192.168.0.%’;

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

grant create routine on testdb.* to developer@’192.168.0.%’; -- now, can show procedure status

grant alter routine on testdb.* to developer@’192.168.0.%’; -- now, you can drop a procedure

grant execute on testdb.* to developer@’192.168.0.%’;

10>.grant 普通 DBA 管理某个 MySQL 数据库的权限。

grant all privileges on testdb to dba@’localhost’

其中，关键字 “privileges” 可以省略。

11>.grant 高级 DBA 管理 MySQL 中所有数据库的权限。

grant all on *.* to dba@’localhost’

12>.MySQL grant 权限，分别可以作用在多个层次上。

1. grant 作用在整个 MySQL 服务器上：

grant select on *.* to dba@localhost; -- dba 可以查询 MySQL 中所有数据库中的表。

grant all on *.* to dba@localhost; -- dba 可以管理 MySQL 中的所有数据库

2. grant 作用在单个数据库上：

grant select on testdb.* to dba@localhost; -- dba 可以查询 testdb 中的表。

3. grant 作用在单个数据表上：

grant select, insert, update, delete on testdb.orders to dba@localhost;

4. grant 作用在表中的列上：

grant select(id, se, rank) on testdb.apache_log to dba@localhost;

5. grant 作用在存储过程、函数上：

grant execute on procedure testdb.pr_add to ’dba’@’localhost’

grant execute on function testdb.fn_add to ’dba’@’localhost’

注意：修改完权限以后 一定要刷新服务，或者重启服务，刷新服务用：FLUSH PRIVILEGES。

 

 

权限表

权限	说明
all
alter
alter routine	使用alter procedure 和drop procedure
create
create routine	使用create  procedure

create temporary tables	使用c reate temporary table
create  user
create view
delete
drop
execute	使用call和存储过程

file	使用select into outfile  和load data infile
grant option	可以使用grant和revoke
index	可以使用create index 和drop index
insert
lock tables	锁表
process	使用show full processlist

reload	使用flush
replication client	服务器位置访问
replocation slave	由复制从属使用
select
show databases
show view

shutdown	使用mysqladmin shutdown 来关闭mysql
super
update
usage	无访问权限

 

 

 

mysql错误总结

 

总结：对于 ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1'  此类错误返回时， (using password: ?)中?的

 

关键字是YES还是NO，关键不在于用户是否存在，密码是否正确，它的结果取决于登录时，用户对于密码有没有字符串的输入，

如果没有，MySQL数据库验证后，若出错返回此类信息，则应是 (using password: NO)，若用户对密码有字符串的输入，返回的则是

 

(using password: YES)。

 

 

原因1 : 客户端远程访问的用户账号并未创建

 

检查

 

以管理员ROOT登录后，show grants for 'user'@’IP‘; 或者 select user from mysql.user; 确认用户账号是否存在。

 

mysql> show grants for 'test'@'127.0.0.1';

ERROR 1141 (42000): There is no such grant defined for user 'test' on host '127.0.0.1'

mysql>

 

返回信息：ERROR 1141 (42000): There is no such grant defined for user 'test' on host '127.0.0.1'

说明，没有jtsec用户，或者没有对jtsec用户进行在192.168.8.123远程访问的授权。

 

解决

 

mysql>  grant all privileges on *.* to 'test'@'127.0.0.1' identified by 'test' with grant option;

 

mysql> flush privileges;

 

mysql> show grants for 'test'@'127.0.0.1';

 

mysql> select user,host from mysql.user;

 

 

原因2 : 用户账号存在，但未对其所在的客户端的IP进行远程访问授权允许

 

检查

 

以管理员ROOT登录后 show grants for 'user'@'IP';

 

mysql> show grants for 'root'@'127.0.0.1';

 

ERROR 1141 (42000): There is no such grant defined for user 'root' on host '127.0.0.1'

 

 

返回信息：ERROR 1141 (42000): There is no such grant defined for user 'root' on host '127.0.0.1'

说明，没有root用户，或者没有对root用户进行在127.0.0.1远程访问的授权。

 

 

mysql> show grants for 'root'@'localhost';

或者直接查询mysql的user用户表select user,host from mysql.user;

mysql> select user,host from mysql.user;

 

 

解决

 

进行root用户的远程访问授权，可以授权到指定的客户端IP，也可以授权为所有IP都可访问（host值为%）。

 

授权为所有IP都使用用户root，密码root，来远程访问数据库

 

mysql> GRANT ALL PRIVILEGES ON *.* TO'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

 

mysql> flush privileges;

 

再次进行授权的查询

 

mysql> show grants for 'root'@'%';

 

再次查询授权表记录

mysql> select user,host,password from mysql.user;

注意：

mysql5.7用下面这条语句查询

mysql> select user,host,authentication_string from mysql.user;

 

 

原因3 : 用户账号授权访问的密码不正确

 

检查

以管理员ROOT登录后， select user,host, authentication_string  from mysql.user;

mysql> select user,host, authentication_string from mysql.user;

 

解决

使用正确的访问密码进行访问即可。

 

ps aux |grep mysql   查看mysql的进程。

通过kill -9 pid杀死进程。

重启mysql  service mysql start