1 数据库命令

(1) 显示所有数据库

show databases;

(2) 显示当前时间

select now();

(3) 显示数据库版本

select version();

(4) 创建数据库

create database 数据库名 charset=utf8;

(5) 删除数据库

drop database 数据库名;

(6) 查看当前使用的数据库

select database();

(7) 使用数据库

use python01;

(8) 查看当前字符集

show VARIABLES like 'character%';

(9) 查看mysql配置

(10) 修改数据库字符集

法一

set character_set_database = utf8;

法二

alter database xxx character set utf8;

2 数据表命令

(1) 显示当前数据库中的数据表

show tables;

(2) 创建数据表

create table xxx(id int, name varchar(30));

(3) 查看数据表结构

desc xxx;

(4) 创建一个有约束的数据表

create table yyy(id int primary key not null auto_increment, name varchar(30));
或
create table yyy(
    id int primary key not null auto_increment,
    name varchar(30)
);

create table zzz(
    id int primary key not null auto_increment,
    name varchar(30)
);

(5) 创建一个较复杂的数据表

create table students(
    id int unsigned not null auto_increment primary key,
    name varchar(30),
    age tinyint unsigned default 0,
    high decimal(5,2),
    gender enum("男","女","未知") default "未知",
    cls_id int unsigned
);

(6) 删除数据表

drop table xxx;

(7) 修改表-添加字段

alter table students add birthday datetime;

(8) 修改表-修改字段-改类型及约束

alter table students modify birthday date;

(9) 修改表-修改字段-改字段名，类型及约束

alter table students change birthday birth date default "2000-01-01";

(10) 修改表-删除字段

alter table students drop high;

(11) 查看表的创建语句

show create table students;

create table test(
id int unsigned primary key auto_increment not null,
name varchar(20),
age int,
height int default 170
);

(12) 重命名表

rename table people to students;

(13) 查看表的统计信息

show table status like 'tb_sgle'\G;

(14) 重新计算表的统计信息

analyze table tb_sgle;

(15) 添加和删除索引

普通索引

alter table 表名 add index 索引名(索引列);
alter table 表名 drop index 索引名;

唯一索引

alter table 表名 add unique (索引列);

(16) 数据表的增删改查

insert 表名 values(各列值);
delete from 表名 where 条件;
update 表名 set 字段=新值 where 条件;
select * from 表名 where 条件;

3 其它命令

(1) 开启关闭mysql服务

重启 /etc/init.d/mysql restart // 法一 sudo service mysql restart // 法二

开启 /etc/init.d/mysql start // 法一 sudo service mysql start // 法二

停止 /etc/init.d/mysql stop // 法一 sudo service mysql stop // 法二

(2) 查看服务是否开启

systemctl status mysql

(3) 修改密码

法一

注意: 这里是在不登录mysql, 直接在终端输入下列命令

mysqladmin -u用户名 -p旧密码 password 新密码

法二

mysql> use mysql;
mysql> update user set password=password(‘123’) where user=’root’ and host=’%’;
mysql> flush privileges;

(4) 创建用户

默认是%

create user ws identified by ‘123456’;

(5) 修改用户名和主机名

rename user ‘ws’@’localhost’ to ‘jk’@’localhost’;

(6) 删除用户

若没有明确指明主机名, 则主机名默认是’%’

drop user ‘ws’@’localhost’;

(7) 查看用户

select user, host from mysql.user;

(8) 查看用户权限

show grants for ‘ws’;

select * from mysql.user where user=’ws’\G;

(9) 授予用户权限

所有权限

grant all privileges on . to ‘ws’@’%’ with grant option;
只读权限

grant select on db.tb to ‘ws’@’%’;

(10) 回收用户权限

revoke all privileges on . from ‘ws’@’%’;

(11) 修改事务的隔离级别

查看当前隔离级别: show variables like 'transaction_isolation';
隔离级别: read uncommitted, read committed, repeatable read, serializable

全局范围内有效

set ``**global **``transaction isolation level serializable;
对新的会话有效, 对已经存在的会话无效

会话范围内有效

set ``**session **``transaction isolation level serializable;
对当前会话的所有后续事务有效, 对已经在执行的事务无效

对下一个事务有效

set ``transaction isolation level serializable;
下一个事务执行完后, 后续事务恢复之前的隔离级别, 不能在已开启的事务中执行

(12) 查看表状态

show table status like ‘表名’\G;

(13) 分析表

analyze table hero;

(14) 修改表中某字段的排序规则

alter table project change project_name varchar(10) character set utf8 COLLATE “utf8_general_cs”;

ERROR 1273 (HY000): Unknown collation: ‘utf8_general_cs’
如果是utf8, MySQL没有区分大小写的Unicode排序规则,因为这是一个难题.有些语言在大写之前整理小写,而其他语言在小写之前整理大写
使用collation utf8_bin(可能是最简单的)
utf8_bin将字符串中的每一个字符用二进制数据存储，区分大小写。

alter table project change project_name project_name varchar(10) character set utf8 COLLATE “utf8_bin”;

(15) 修改表的字符集

alter table project DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs;

(16) 修改表的字符集和所有列的字符集

alter table project convert to character set utf8mb4 collate utf8mb4_0900_as_cs;

(17) 修改库的排序规则

alter database db1 character set utf8md4 collate utf8mb4_0900_as_cs;

4 自动删除指定时间以前的记录

(1) 创建存储过程 (函数)

CREATE PROCEDURE prc_del_device_log(IN daycount int) BEGIN delete from device_log where (TO_DAYS(NOW()) - TO_DAYS(create_time))>=daycount; END;

(2) 创建事件 (调用函数)

2表示删除2天前的数据

CREATE EVENT auto_del_device_log ON SCHEDULE EVERY 1 DAY STARTS ‘2022-01-12 00:00:00’ ON COMPLETION PRESERVE ENABLE DO call prc_del_device_log(2);

(3) 开启事件调度器

SET GLOBAL event_scheduler = 1;

查看是否已开启事件调度器

SHOW VARIABLES LIKE ‘event_scheduler’;

如果是阿里云的MySQL, 需要到控制台去修改为ON

(4) 开启事件

ALTER EVENT auto_del_device_log ON COMPLETION PRESERVE ENABLE;

(5) 关闭事件

ALTER EVENT auto_del_device_log ON COMPLETION PRESERVE DISABLE;

5 Navicat自动运行

本质上是添加windows的定时任务, 如果不开机就不会运行, 不够智能

MySQL常用命令