【mysql】基础

基本命令

# ROOT用户进入数据库
mysql -uroot -p

# 使用用户登录数据库
mysql -u 用户名 -p

# 重启mysql 
sudo service mysql restart

# 创建数据库
create database 数据库名字 character set utf8mb4 collate utf8mb4_unicode_ci;

# 进入数据库
use mysql_name;

# 查看所有数据库
show databases;

# 查看当前数据库所有数据表
show tables;

# 查询当前在哪个数据库
select database();

# 查看当前数据表的所有字段
desc 数据表名;

数据表操作

# 创建数据表
create table `userinfo` (
  # 字段名 类型(宽度)约束条件,
  `id` int(11) not null auto_increment comment '用户id',
  `username` varchar(50) not null comment '用户名',
  `password` varchar(50) not null comment '密码',
  `email` varchar(50) default null comment '邮箱',
  `phone` varchar(20) default null comment '手机号',
  # default current_timestamp   默认以当前时间戳 
  `created_time` datetime not null default current_timestamp comment '创建时间',
  # on update current_timestamp 并且在更新时自动更新为当前时间戳
  `updated_time` datetime not null default current_timestamp on update current_timestamp comment '更新时间',
  primary key (`id`),
  unique key `username` (`username`),
  key `email` (`email`),
  key `phone` (`phone`)
) 
engine=innodb default charset=utf8mb4 comment='用户表'; # 引擎为InnoDB，字符集为utf8mb4


# 删除数据表
DROP TABLE `userinfo` ;

# 修改字段属性（例子为设置某个字段为自增ID）; 报错应该先删除原主键约束，再添加主键自增
alter table 数据表名 modify id int;
alter table 数据表名 drop primary key;
alter table 数据表名 modify id int auto_increment primary key;

# 添加字段
alter table 数据表名 add column 字段 varchar(11);

# 设置字段为主键。
alter table 数据表名 add primary key (字段名);

# 将字段ID的类型更改为整数
alter table 数据表名 modify 字段 字段类型;

数据操作

# 插入数据
insert into 表名 (列1, 列2, 列3, ...) values ('值1', '值2', '值3', ...);

# 删除数据
delete from 表名 where 条件;
delete from userinfo where id = 1;

# 修改数据
update 表名      set 列1  =  新值1, 列2 = 新值2 where 条件;
update userinfo set email = 'demo' where id = 1;

# 查询数据
select * from 表名;
select * from 表名 where 字段名='关键词' / 字段名 > 85;
select * from 表名 where 字段名='A' or where 字段名='B';
select * from 表名 binary where 字段名='关键词'; # binary 区分大小写
select * from 表名 binary like  字段名='关键词'; # 模糊查询
select * from 表名 limit 2 offset 0;   # limit 展示个数 offset 索引算起
# 去重
select distinct column1, column2, ... from table_name;
select distinct email from table_name;
# 聚合函数使用COUNT、SUM、AVG、MIN和MAX
select count(*) from 表名;
# group by：将phone分组计算有多少个不同的数量
select phone,count(id) from userinfo group by phone; 
+-------+-----------+
| phone | count(id) |
+-------+-----------+
| 10086 |         2 |
| 10089 |         1 |
| 10090 |         1 |
+-------+-----------+
# having：用于筛选分组后结果的筛选条件
select phone,count(id) from userinfo group by phone having count(id) > 5;  

用户与权限管理

# 创建数据库用户
create user '用户名'@'localhost' identified by 'password';

# 删除用户
drop user '用户名'@'主机';

# 查看数据库的所有用户列表
select user, host from mysql.user;

# 赋予用户权限
grant <权限列表> on <数据库名>.<表名> to '<用户名>'@'<主机>';
例子：
grant  select, insert, on mydatabase.mytable to 'user1'@'localhost';   # 赋予权限
revoke select, insert, on mydatabase.mytable from 'user1'@'localhost'; # 收回权限

# insert / update / delete / create  允许增删改查
# drop   		允许用户删除数据库或表
# alter  		允许用户修改表结构
# grant option   允许用户授予或撤销其他用户的权限
# all privileges 允许用户拥有所有权限
grant select on 数据库名.* to '用户名'@'localhost';
flush privileges; # 必须刷新才有效果

索引算法

1: B-Tree（平衡树）索引算法：B-Tree是MySQL最常用的索引算法，适用于范围查询和精确查询。
#  数据库索引，文件系统，网络路由表，存储管理，日志文件

2: Hash（哈希）索引算法：Hash索引基于哈希函数，适用于等值查询，但不适用于范围查询。
#  数据完整性验证，密码存储，唯一标识和查找，数据分片和负载均衡，数据校验和

3: Full-Text（全文）索引算法：Full-Text索引用于全文搜索，支持自然语言查询，适用于大量文本的搜索。
# 搜索引擎，新闻和文章网站，电子商务网站，社交媒体平台，文件管理系统，博客和论坛平台，在线教育平台

4: R-Tree（R树）索引算法：R-Tree用于多维数据的索引，如地理位置数据，适用于范围查询。
# 地理信息系统（GIS），图像处理，网络路由，数据挖掘和机器学习

5: Bitmap（位图）索引算法：Bitmap索引用于低基数（不同值较少）的列，适用于等值查询。


# 创建索引：
create table table_name (
    column1 data_type,
    column2 data_type,
    ...
  # index 自定义名称S (column1, column2) using 索引名称
    index index_name (column1, column2) using algorithm_name
);

create index my_index  on table_name (column1, column2) using algorithm_name;
 
# 可以是单列，可以是联合索引
alter table table_name add index index_name (column1, column2);
  
# 唯一索引：
alter table table_name add unique index idx_email (email);
 

导入与导出

# 导出数据库 
mysqldump -u root -p database_name > filename.sql 

# 导入数据库
mysql -u root -p database_name < filename.sql   

# 导出数据表 
mysqldump -u root -p database_name table_name > filename.sql

# 导入数据表
mysql -u root -p database_name < filename.sql

备份与恢复

# 注意： mysqldump命令是在系统命令行中执行，而不是在MySQL命令行中执行。

# 备份整个数据库
mysqldump -uroot -p123456 -A > backup.sql

# 备份特定的数据库
mysqldump -uroot -p123456 wh_mysql > backup.sql

# 备份特定的数据表 --tables
mysqldump -uroot -p123456 --tables wh_mysql table1,table2 > backup.sql


# 定时备份
1：crontab -e  
2：15 21 * * * 脚本命令


# 恢复数据库
1: 登录数据库 mysql -u your_username -p
2: 创建数据库 create database your_database;
3：恢复数据库 mysql -u your_username -p your_database < /path/to/backup.sql


# 恢复数据表
1: 登录数据库 mysql -u your_username -p
2: 进入数据库 use your_database;
3：恢复数据库 mysql -u your_username -p your_database < /path/to/backup.sql

分析语句计划

# desc SQL语句
  desc select * from table_name where column_name = 'value';

# id：查询的标识符，按查询的顺序递增。
# select_type：查询的类型，常见的有SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）等。
# table：访问的表名。
# partitions：访问的分区。
# type：访问表的方式，常见的有ALL（全表扫描）、INDEX（全索引扫描）、RANGE（索引范围扫描）等。
# possible_keys：可能使用的索引。
# key：实际使用的索引。
# key_len：使用的索引长度。
# ref：使用的索引列与值的比较。
# rows：估计扫描的行数。
# filtered：过滤后匹配的百分比。
# Extra：额外的信息，如是否使用了临时表、是否使用了文件排序等。