创建数据库与表

  1. #创建数据库
  2. create database 数据库名 charset=utf8;
  3. #使用数据库
  4. use 数据库名;
  5. #创建表
  6. create table goods(字段 类型 约束);
  8. create database deep_test charset=utf8;
  9. use deep_test;
  10. create table goods(
  11.     id int unsigned primary key auto_increment not null,
  12.     name varchar(150) not null,
  13.     cate_name varchar(40) not null,
  14.     brand_name varchar(40) not null,
  15.     price decimal(10,3) not null default 0,
  16.     is_show bit not null default 1,
  17.     is_saleoff bit not null default 0
  18. );
  20. insert into goods values(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',default,default); 
  21. insert into goods values(0,'y400n 14.0英寸笔记本电脑','笔记本','联想','4999',default,default);
  22. insert into goods values(0,'g150th 15.6英寸游戏本','游戏本','雷神','8499',default,default);

账户管理

查看用户

  1. #查看user表的结构
  2. desc <表名||库名.表名>; 
  3. desc mysql.user;
  5. #查看所有用户的主机、用户、密码、
  6. select host,user,authentication_string from mysql.user;
  7. select user,host,authentication_string from mysql.user \G;

设置用户密码与远程访问

  1. #查看用户权限管理
  2. select Host,User from mysql.user;                    
  4. #修改root用户主机
  5. update mysql.user set host ="%" where user = "root";  
  7. #修改root用户密码
  8. #ALTER USER 'root'@'Qwer@1234' PASSWORD EXPIRE NEVER;
  9. alter user 'root'@'%' IDENTIFIED BY 'Qwer@1234';
  10. alter user 'root'@'%' identified with mysql_native_password by 'Qwer@1234';
  12. #刷新权限
  13. flush privileges;

创建账户

  1. create user 'kim'@'%' identified by 'Qwer@1234';
  2. create user 'kim'@'localhost' identified by 'Qwer@1234';
  3. create user 'kim'@'119.23.72.172' identified by  'Qwer@1234';

授权

常用权限主要包括：create、alter、drop、insert、update、delete、select

  1. @查看用户权限有哪些
  2. show grants;
  3. show grants for 用户名[@主机地址];
  4. show grants for root \G;
  5. show grants for root@"%";
  6. show grants for kim@"localhost";
  7. show grants for kim@"119.23.72.172";
  8. show grants for kim@"%";
  10. #授予所有的权限
  11. grant <权限列表> on <数据库.表名> to '用户名'@'访问主机' identified by '密码';
  12. #grant all on *.* to 'kim'@'localhost' identified by 'Qwer@1234';
  13. #grant all on *.* to 'kim'@'localhost' identified by 'Qwer@1234' with grant option;
  15. grant all on *.* TO 'kim'@'%';
  16. grant select,insert on *.* to 'kim'@'localhost';
  17. grant select,insert on deep_test.* to 'kim'@'119.23.72.172';
  18. grant select,insert,drop,alter on *.* to 'kim'@'%';
  19. grant select, insert, update, delete, create, drop, reload, shutdown, process, file, references, index, alter, show databases, super, create temporary tables, lock tables, execute, replication slave, replication client, create view, show view, create routine, alter routine, create user, event, trigger, create tablespace, create role, drop role on *.* to `root`@`%` with grant option
  20. GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%` WITH GRANT OPTION
  22. #修改权限
  23. grant <权限列表> on <数据库.表名> to '用户名'@'访问主机' with grant option;
  24. grant select on deep_test.* to 'kim'@'localhost' with grant option;
  26. #刷新权限
  27. flush privileges;

删除权限

  1. # revoke delete on <数据库.表名> from 'kim'@'%';
  2. revoke delete on *.* from 'kim'@'%';

删除用户

  1. drop user 'kim'@'localhost';
  2. delete from mysql.user where user='kim';
  4. #刷新权限
  5. flush privileges;

连接远程

  1. mysql -h 119.23.72.172  -uroot -pQwer@1234

解决问题

解决Navicat连接不上MySql服务器报错

Client does not support authentication protocol requested by server； conside

  1. alter user 'root'@'%' identified with mysql_native_password by 'Qwer@1234';