mysql基本命令

# yum安装mysql数据库
$ yum install -y mysql mysql-devel mysql-server
$ /etc/init.d/mysqld start

# 添加/etc/my.conf配置：
$ vi /etc/my.conf
[mysqld]
default-character-set=utf8
character_set_server=utf8
[client]
default-character-set=utf8​

# mysql设置root登录密码为123456
$ /usr/bin/mysqladmin -u root password '123456'

# 登录mysql数据库
$ mysql -uroot -p
$ Enter password:(输入root密码)
mysql>

# 创建数据库
mysql> create database zabbix_db;

# 创建与配置文件不一样的数据库编码方式（例GBK）
mysql> CREATE DATABASE IF NOT EXISTS zabbix_db CHARACTER SET gbk;

# 显示数据库创建的时候使用的指令
mysql> SHOW CREATE DATABASE zabbix_db;

# 修改数据库编码格式
mysql> ALTER DATABASE zabbix_db CHARACTER SET utf8;

# 选择数据库
mysql> use zabbix_db;

# 直接删除数据库，不提醒
mysql> drop database test_db;

# 删除数据库时有提示
mysql> mysqladmin drop test_db;

# 显示表
mysql> show tables;

# 表的详细描述
mysql> describe zabbix_db;

# 显示当前mysql版本和当前时间
mysql> select version(),current_date;

# 查看警告信息
mysql> SHOW WARNINGS;

# 显示当前用户：
mysql> SELECT USER();

# 修改mysql中root的密码
mysql> update user set password=password("000000") where user=’root’;

# 刷新数据库
mysql> flush privileges;

# 创建用户并数据库授权
mysql> create user 'zabbix'@'localhost' identified by 'Zabbix@123';  #localhost只可本地登录，%任意远程主机登录
mysql> grant all privileges on zabbix_db.* to 'zabbix'@'%' identified by 'Zabbix@123' with grant option;

# 删除用户
mysql> delete from user where User='test' and Host='localhost';

# 删除授权
mysql> revoke all privileges on zabbix_db.* from zabbix@"%";

# 重命名表
mysql> alter table t1 rename t2;

# 备份数据库
mysql> mysqldump -h 192.168.2.35 -u root -p zabbix_db >zabbix_db_backup.sql

# 恢复数据库
mysql> mysqladmin -h 192.168.2.35 -u root -p create zabbix_db
mysql> mysqldump -h 192.168.2.35 -u root -p zabbix_db < zabbix_db_backup.sql

#数据表去重
mysql> delete from jalon_alert_monitor where id not in( 
select min_id from (select min(id) as min_id from jalon_alert_monitor group by school,host,event,status,timestamps,zclock,syncstatus) as a 
);