MySQL数据库管理

登录

mysql -u root -p 123456'

报错:You must reset your password using ALTER USER statement before executing this statement.

SET PASSWORD = PASSWORD('lifebutf');

查看数据库列表

show databases;

创建数据库

CREATE DATABASE TESTDB;

使用某一个数据库

use TESTDB;

查看当前连接的数据库

select database();

查看当前数据库的所有表

show tables;

创建一个表

create TABLE EMPLOYEE (FIRST_NAME varchar(100), LAST_NAME varchar(100), AGE INT, SEX INT, INCOME INT);

创建一个用户

CREATE USER 'test'@'%' identified BY 123456';

为一个用户授权

grant ALL on testdb.* to 'test'@'%';

撤销用户的权限

revoke all on testdb.* from 'test'@‘%';

删除用户

drop user 'test'@‘%';

MySQL ERROR 1698 (28000)

# 1. 停止MySQL服务
# 2. 以安全模式启动MySQL
"""
sudo mysqld_safe --skip-grant-tables &
"""
# 3. 查看用户表
"""
select user, plugin from mysql.user;
"""
# 4. 更新用户信息
"""
update mysql.user 
    set 
        authentication_string=PASSWORD('newPwd'),
        plugin='mysql_native_password' 
    where 
        user='root';
"""

启动MySQL服务

sudo /usr/local/MySQL/support-files/mysql.server start

停止MySQL服务

sudo /usr/local/mysql/support-files/mysql.server stop

重启MySQL服务

sudo /usr/local/mysql/support-files/mysql.server restart

参考:

MySQL用户管理
error 28000