mysql命令和语法

mysql的一些基本命令和语法

一、显示数据库列表

 show databases;

二、创建数据库

drop database IF  EXISTS  test;
create database test CHARACTER SET utf8 COLLATE utf8_general_ci;

三、 在mysql中创建用户名和密码

CREATE USER 'test'@'%' IDENTIFIED BY 'mysql';

四、 授权test用户拥有test数据库的所有权限

• 授权增删改查给相应的用户

  grant select,insert,delete,update on *.* TO '用户名'@'%'

• windows

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

• linux

  grant ALL privileges on test.* to 'test'@'%' identified by 'mysql' with grant option;

Mysql8.0

ALTER USER 'test'@'%' IDENTIFIED WITH mysql_native_password BY '新密码';
grant ALL privileges on nacos.* to 'nacos'@'%'  with grant option;

五、 刷新权限

-- 重新给用户修改权限以后需要刷新权限
flush privileges;

六、查看字符集

show variables like 'character%';

七、 mysql数据备份导入导出

-- 格式：mysqldump -h 链接ip -P (大写)端口 -u 用户名 -p 密码数据库名>d:XX.sql(路径)

-- 导出
mysqldump -h 127.0.0.1 -P 3306 -uroot -proot -database cmdb > /data/backup/cmdb.sql
-- 导入
mysql -h 127.0.0.1 -P 3306 -uroot -proot —database cmdb < /data/backup/cmdb.sql

八、mysql 默认时区

mysql 5.7.20安装的时候默认时区为系统时区UTC，如果不设置时区，由于时区不对，jdbc连接数据库的时候会报以下错误。

Caused by: java.sql.SQLException:
	The server time zone value ‘�й���׼ʱ��’
	is unrecognized or represents more than one time zone.

• 查看当前时区命令

  -- 如果是中国标准时间， 会输出08:00
  select timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00'));
  -- 或者
  SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);
  -- 此命令不能完全查看时区
  show variables like '%time_zone%';

• 修改当前时区为中国时区：+8:00 东八区 ，这里需要注意的是，如果mysql重启了以后就会失效

  set global time_zone = '+8:00';    
  flush privileges;

• 设置永久生效，修改mysql的配置文件my.ini。[mysqld]字段下面添加一行：default-time-zone=’+08:00’，保存重启，或者保存以后按上面步骤设置

九、查看当前版本号

1. 通过linux的命令，执行mysql -V查看
2. 登录进mysql，执行select version();

十、查看表结构

SELECT
@rownum := @rownum + 1 AS '序号',
COLUMN_NAME 列名,
COLUMN_TYPE 数据类型,
DATA_TYPE 字段类型,
CHARACTER_MAXIMUM_LENGTH 长度,
IS_NULLABLE 是否为空,
COLUMN_DEFAULT 默认值,
COLUMN_COMMENT 备注
FROM
( SELECT @rownum := 0 ) r,
INFORMATION_SCHEMA.COLUMNS
where
-- developerclub为数据库名称，到时候只需要修改成你要导出表结构的数据库即可
table_name ='tableName'

十一、修改字段

如果要修改，先执行删除再执行添加操作

ALTER TABLE 表名 ADD COLUMN 字段 int(11) DEFAULT NULL COMMENT '备注' AFTER 在某个字段之后;
ALTER TABLE 表名 drop COLUMN 字段;
#修改表字段，注意not null如果有的话必须放后面
ALTER TABLE table CHANGE column column VARCHAR(255)  CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL;
#修改字段的同时修改字符
ALTER TABLE table CHANGE column column VARCHAR(255)  CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL;

十二、修改字符集

字符集可以单独修改，也可以根据上面的根据字段一起修改

#查看数据库字符集
show create database databasename;
#修改数据库的默认字符集
alter database db_name default character set character_name;
#查看表字符集
show create table tablename;
#修改表默认字符集
alter table tablename default character set utf8mb4 collate utf8mb4_general_ci;
alter table tablename default character set utf8mb4 collate utf8mb4_unicode_ci;
#转换表字段的字符集
alter table tablename convert to character set utf8mb4 collate utf8mb4_general_ci;
#修改字段的同时修改字符
ALTER TABLE tablename CHANGE column columnname VARCHAR(255)  CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL;

十三、查看数据库状态

显示进程：包含连接的客户端等等

SHOW PROCESSLIST;

查看是否锁表

SHOW OPEN TABLES WHERE In_use > 0;

查看正在锁的事务

-- 8.0之前
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 
-- 8.0之后
 select * from performance_schema.data_locks;

查看等待锁的事务

-- 8.0之前
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
-- 8.0之后
select * from performance_schema.data_lock_waits;