MySQL

信息

包含数据库的安装，及常用的 SQL

MySQL 安装

# 安装
sudo apt-get install mysql-server
# 启动
sudo service mysql start
# 停止
sudo service mysql stop
# 重启
sudo service mysql restart

登录

# 登录数据库
> mysql -u root -p

数据库结构操作

数据库

# 查看所有的数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| pos                |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

# 切换数据库
mysql> use dbName;
Database changed

# 创建数据库 dbName utf8mb4，排序规则为 utf8_general_ci
mysql> create database if not exists `dbName` default character set utf8mb4 collate utf8mb4_general_ci;
Query OK, 1 row affected (0.00 sec)

# 修改数据库字符串字符集为 utf8mb4
mysql> alter database dbName character set utf8mb4 collate utf8mb4_general_ci;
Query OK, 1 row affected (0.00 sec)

# 删除数据库
mysql> drop database dbName;
Query OK, 0 rows affected (0.00 sec)

# 查询数据库
mysql> show create database dbName;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| pos      | CREATE DATABASE `pos` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

用户

# 查询用户
mysql> select user, host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| root          | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

# 创建用户
mysql> create user 'username'@'host' identified by 'password';
Query OK, 0 rows affected (0.00 sec)

# 修改密码
mysql> alter user 'root'@'localhost' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.00 sec)

# 修改当前用户密码
mysql> alter user USER() identified by '123456'
Query OK, 0 rows affected (0.00 sec)

# 允许远程登录，解决登录时1130错误
mysql> update user set host='%' where user='root';
Query OK, 1 row affected (0.03 sec)

# 授权
mysql> grant all privileges on databasename.* to 'username'@'host';

# 刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

# 删除用户
mysql> drop user 'username'@'host';
Query OK, 0 rows affected (0.00 sec)

# 授权
grant all privileges on *.* to 'username'@'host';

表

# 查询表
mysql> show tables;

-- 创建表并增加注释
create table tabName(
    colName1 dataType1 comment '字段注释1',
    colName2 dataType2 comment '字段注释2',
    colName3 dataType3 comment '字段注释3',
    ...
    primary key(colName1)
) comment '表注释';

-- 添加/修改 表注释
alter table {tabName} comment '修改后的表的注释';

-- 删除表
drop table tabName;

-- 显示创建表的详细信息
show create table tabName;

列

-- 查询表内所有列的注释
show full columns from $tabName;

-- 添加列
alter table $tabName add $colName $dataType not null comment '字段注释';
-- 修改属性
alter table $tabName modify column $colName $dataType;
-- 字段增加注释
alter table $tabName modify column $colName $dataType comment '修改后的字段注释';
-- 修改列名并修改属性
alter table $tabName change column $colName $newColName $dataType comment '修改后的字段注释';
-- 删除列
alter table $tabName drop column $colName;

-- 创建虚拟列
alter table $tabName add column $colName $dataType comment '字段注释' as (concat($colName1, $colName2)) stored;

索引

-- 添加主键索引
alter table tabName add primary key(`colName`)
-- 添加Unique（唯一索引）
alter table tabName add unique(`colName`)
-- 添加Index（普通索引）
alter table tabName add index index_name(`colName`)
-- 添加 FullText（全文索引）
alter table tabName add fulltext(`colName`)

数据

复制表

# 类似 SQL Server 中 `select * into newTabName from tabName`
-- 1. 复制表结构
create table $newTabName like $tabName; 
-- 2. 导入数据
insert into $newTabName select * from $tabName;

跨表更新

update  member_user a inner join member_card b on a.id = b.user_id
set a.card_id = b.id where  a.mobile='16622224444';

update member_user a , member_card b set a.card_id = b.id
where a.id = b.user_id and a.mobile='16622224444'

导入导出

导出数据库

-- 导出数据库结构
mysqldump -u root -p --no-data pos > pos.sql

-- 导出数据库结构和数据
mysqldump -u root -p pos > pos.sql
--no-data：只导出表结构
--databases：导出数据库
--all-databases：导出所有数据库
--lock-tables：锁定表
--single-transaction：在导出数据之前锁定表，保证数据一致性
--skip-extended-insert：使用扩展插入
--skip-comments：不导出注释
--skip-triggers：不导出触发器
--skip-foreign-key-checks：不检查外键约束
--quick：快速导出数据，不使用缓冲区
--extended-insert：使用扩展插入
--default-character-set：指定字符集
--set-gtid-purged：设置GTID模式
--host：数据库服务器地址
--user：数据库用户名
--password：数据库密码
--port：数据库端口
--socket：数据库套接字文件
--result-file：导出结果文件路径
--databases：导出数据库
--all-databases：导出所有数据库
--tables：导出表
--ignore-table：忽略表
--where：导出条件

导入数据库

-- 导入数据库结构
mysql -u root -p pos < pos.sql

数据库维护

查看数据库占用空间

set @table_schema='pos';
select table_schema as `db`, table_name as `table`, table_rows as `rows`,
  round((data_length+index_length)/1024/1024,2) as `total_size(MB)`,
  round(data_length / 1024 / 1024, 2) as `data_size(MB)`,
  round(index_length / 1024 / 1024, 2) as `index_size(MB)`
from information_schema.`TABLES` where table_schema=@table_schema
order by data_length+index_length desc;

查询执行中的sql

-- 查询执行中的SQL
select Id,user,host,db,time,Info from information_schema.`PROCESSLIST` where info is not null and time>10;

-- 查询执行中的SQL
show processlist;

kill 0;
-- 批量kill 正在运行中的进程
select concat('KILL ',id,';') from information_schema.processlist where user='root';