创建数据库

create database if not exists database2 
default character set utf8mb4 
default collate utf8mb4_unicode_ci;


#utf8mb4_unicode_ci utf8mb4_general_ci

mysql -h 192.168.1.1. -P 3306 -u root -p123456

常用语句

SHOW DATABASES #查询所有库名
select database();  #当前使用的数据库

show variables like 'port'; #查看数据库使用端口
show variables like ‘character%’; 查看数据库编码
show variables like ‘%datadir%’; 查看数据文件存放路径

select distinct concat (‘user: ‘’’,user,’’’@’’’,host,’’’;’)as qurey from mysql.user;
 查看数据库的所有用户信息

创建用户mysql5

#创建管理员用户
GRANT ALL PRIVILEGES ON *.* TO "user"@"%" IDENTIFIED BY "密码";

• mysql8

#创建默认密码
create user 'user'@'%' identified by '密码';
grant all privileges on *.* to  'uroot'@'%' with grant option;

#创建mysql_native_password
create user 'user'@'%' identified with mysql_native_password by '密码';
grant all privileges on *.* to  'uroot'@'%' with grant option;

#创建caching_sha2_password
create user 'user'@'%' identified with caching_sha2_password by '密码';
grant all privileges on *.* to  'user'@'%' with grant option;

#修改密码类型
alter user 'user'@'%' identified with caching_sha2_password by '密码';

修改用户密码
SET PASSWORD FOR 'user'@'%'=PASSWORD('新密码');
修改用户的权限
GRANT 权限 ON 所在数据库.* TO 'user'@'%' identified by '新密码'；
#常用权限
update delete select

创建只读帐号

create user '帐号'@'%' identified with mysql_native_password by '密码';
GRANT SELECT ON 表名.* TO '帐号'@'%';

mysql修改密码为mysql_native_password

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '密码';

mysql5备份还原

删除数据库

DROP DATABASE database_name;

检测表名是否存在

#方法一
SHOW TABLES LIKE 'table_name';
#方法二
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'database_name' AND table_name = 'table_name';