mysql - mysql 命令行的使用

1. Mysql 管理

• 连接数据库

-- 连接本地数据库
mysql -u root -p
-- 连接远程数据库
mysql -h 192.168.0.105 -u root -p
-- 连接远程指定数据库并设置字符集
mysql -h 192.168.0.105 -u root -p --default-character-set=utf8 test

• 修改root密码, 直接修改配置文件

[mysql]
root=root
password=root

• 修改配置使数据库可以远程进行访问

# mysql 配置文件
/etc/mysql/my.cnf
# mariadb 配置文件地址
/etc/mysql/mariadb.conf.d/50-server.cnf
# 修改文件内容 bind-address 127.0.0.1
bind-address 0.0.0.0

2. 用户管理

• 创建用户并授权在本地访问指定的数据库

-- 创建用户
create user 'gogs'@'localhost' identified by '123456';
-- 用户授权
grant all privileges on gogs.* to 'gogs'@'localhost';
-- 刷新授权
flush privileges;

• 创建用户并授权可以远程访问任何数据库

-- 新增用户并授权可以通过远程访问任何数据库
grant all privileges on *.* to 'leon'@'%' identified by 'leon' with grant option;
-- 刷新授权
flush privileges;

• 查看当前用户
  select user();

3. 操作数据库

• 查看数据库
  show databases;
• 查看当前数据库
  select database();
• 切换数据库
  use dbname;
• 创建数据库
  create database dbname charset utf8;
• 删除数据库
  drop database dbname;

4. 操作数据表

• 查看当前数据库的数据表
  show tables;
• 查看指定数据库的数据表
  show tables from dbname
• 创建数据表

CREATE TABLE table_name (
  column_name column_attribute
) table_options;

CREATE TABLE IF NOT EXISTS table_name (
  column_name column_attribute
) table_options;

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `password` char(32) NOT  NULL,
  `sex` tinyint(3) unsigned NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `uq_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• 删除数据表
  DROP TABLE table_name;

• 添加字段
  ALTER TABLE table_name ADD COLUMN column_name column_attribute [FIRST | AFTER column_name];

-- 在user表中created_at后面添加update_at字段
ALTER TABLE `user` ADD COLUMN updated_at TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP AFTER created_at;
-- 在user表的最前面添加user_id字段
ALTER TABLE `user` ADD COLUMN user_id int unsigned NOT NULL FIRST;

• 删除字段
  ALTER TABLE table_name DROP COLUMN column_name;

-- 删除user表中的updated_at字段 
ALTER TABLE `user` DROP COLUMN `updated_at`;

• 修改字段
  ALTER TABLE table_name MODIFY COLUMN column_name column_attribute;

-- 修改user表中的created_at字段
ALTER TABLE `user` MODIFY COLUMN `created_at` TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP(0);

5. 操作数据

• 插入数据

INSERT INTO table_name (field1, field2, ... fieldN) VALUES (value1, value2, ...valueN), 
  (value1, value2, ...valueN)

-- 批量插入两条数据
INSERT INTO `user` (username, password, created_at) VALUES ('leon', 'secret', '2019-11-10 23:00:00'), ('leon1', 'secret1', '2019-11-10 23:00:00');

• 查询数据
  SELECT column1, column2, ...columnN FROM table table_name [WHERE] [LIMIT] [ORDER BY]

-- 查询user表中的所有数据
SELECT * FROM `user`;
-- 查询user表中所有的username字段
SELECT username FROM `user`;
-- 查询user表中满足sex为1 且 age > 30, 按照ID降序排序的最前面10条记录
SELECT * FROM `user` WHERE sex=1 AND age>30 ORDER BY `id` DESC LIMIT 10;
-- 查询user表中 "username 为leon 且status为1" 或者 "usename 为demo且status为0" 的记录。
SELECT * FROM `user` WHERE (username='leon' AND status=1) OR (username='demo' AND status=0);

• 修改数据
  UPDATE table_name SET field1=value1, field2=value2, fieldN=valueN [WHERE][LIMIT]

-- 将user表中username为demo的记录，将status更新为1
UPDATE `user` SET status=1 WHERE username='demo';
-- 将user表中所有stauts为1的前10条记录更新为0
UPDATE `user` SET status=0 WHERE status=1 LIMIT 10;

• 删除数据
  DELETE FROM table_name [WHERE] [LIMIT]

-- 删除user表中status为0的记录`
DELETE FROM `user` WHERE status=0;
-- 删除user表中status为0的前10条记录
DELETE FROM `user` WHERE status=0 LIMIT 10;

6. 事务操作

事务是由一组sql语句组成的逻辑处理单元，要么全部执行成功，要么全部执行不成功。

• BEGIN: 开启一个事务
• COMMIT： 提交一个事务
• ROLLBACK： 回滚一个事务
• SET TRANSACTION: 设置事务隔离级别

BEGIN; # 开启事务
UPDATE `account` SET balance=balance-100 WHERE name='转出方';
UPDATE `account` SET balance=balance+100 WHERE name='转入方';
COMMIT; # 提交事务

7. mysqladmin 管理数据库

• 创建数据库
  mysqladmin -u root -p create testdatabase
• 删除数据库
  mysqladmin -u root -p drop testdatabase
• 修改密码
  mysqladmin -u root -p password 'new password'

8. mysqldump 备份数据库

• 备份数据库

-- 备份数据库test到文件backupfile.sql中
mysqldump -u root -p test > backupfile.sql

• 恢复数据库

-- 创建数据库test
mysqladmin -u root -p create test
-- 读入数据库备份
mysql -u root -p test < backupfile.sql

9. 常用mysql 命令

• help 获取帮助信息
• status 获取服务器信息
• use dbname 切换数据库
• source file.sql 读取sql命令文件
• system command 执行shell命令
• exit 退出msyql命令行