MySQL常见问题以及常用命令行

MySQL安装

1. 这里可以见我另外一篇帖子https://editor.csdn.net/md/?articleId=103812451

常见问题

1. 链接不成功
   
   出现2059这个错误的原因是在mysql8之前的版本中加密规则为mysql_native_password，
   而在mysql8以后的加密规则为caching_sha2_password。
   通过网络查找发现我们可以将mysql用户登录的加密规则修改为mysql_native_password。

修改：
ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘password’ PASSWORD EXPIRE NEVER
alter user “root”@“localhost” identified by “admin” password expire never;
alter user “root”@“localhost” identified with mysql_native_password by “admin”;

插件方式不一样了
select user,host,plugin,authentication_string from mysql.user;

如果想修改，参照官方文档

命令行

1. 查看版本号：方式一：mysql -V，这里一定要写成大写的V，方式二：客户端命令 status
   

2. 登录：mysql -u root -p -u 指定user，-p 输入密码

3. 显示数据库：show database；
   

4. 使用数据库：use mysql;
   

5. 显示表：show tables;
   

6. mysql 显示表结构：常用的为1-4
   (1)desc tb_user;
   (2)describe tb_user;
   (3)show columns from tb_user;
   
   (4)show create table tb_user;显示建表语句

   (5)use information_schema;
   select * from columns where table_name=‘tb_user’;
   一堆乱七八糟的字符，不常用。

7. 表操作

    create table tb_user
    (
    	id int(20) not null auto_increment primary key,
    	name varchar(20) not null,
    	sex VARCHAR(2),
    	address VARCHAR(255)
    );
    
    insert into tb_user(name,sex,address) values ('jetty','1','黄山路89号');
    
    alter table tb_user add column hyu int(2) after name;
    //这里的after的效率并不高，如果不是必要，不用指定非要在其他列后面。
   
    alter table tb_user drop column hyu;
   

8. MySQL 中的int(3)与int(11)的区别

我们在间建表的时候经常会遇到这么一个问题，int(3)与int(11)他们各自能存储多大的int值呢？
这里存在一个误区，很多新人会认为int(3)，代表只存储长度为3个的数字，而int(11)代表可以存储长度为11个的数字，其实这是错误的。
MySQL的基本数据类型告诉我们：
一个int类型的字段就是占4个字节，也就是4*8=32位，因此可以表示的数字是2^32 = 42 9496 7296个数字，取值范围是[0, 4294967295]，这是无符号的，如果区分正负号的话数值的最大范围会变小。
另外，配合zerofill属性使用，会有其他的效果。
（1）插入数据时，当该字段的值的长度小于定义的长度时，会在该值的前面补上相应长度的0
（2）zerofill默认为int(10)
（3）当使用zerofill时，默认会自动加unsigned（无符号）属性，使用unsigned属性后，数值范围是原值的2倍。
例如，有符号为[-2147483648，2147483647]，无符号为[0, 4294967295]。

例如我们尝试插入age=2200000000会失败，而2100000000成功，默认是有符号的
我们改变下：

这时候数据存储范围已经变为：无符号[0, 4294967295]


9.primary key 、unique key 、index几个之间的关系

10.MySQL批量插入数据太慢
这时候我们可用 load data命令来加速导入
load data local infile ‘D:/hyu.yxt’ into table tb_user fields terminated by ‘,’;
默认是不支持这种倒入模式的，需要开启，一种简单的方式就是直接改字段值，但是不够彻底，mysql服务关闭后就失效了

set global local_infile = ‘ON’;

如上图，成功后变可以开始导入数据到数据表中了。但是，万万没想到：

重新登录mysql
mysql --local_infile -u root -p

再次导入1000000条数据，只需要40s，效率相当惊人，只有主键索引。

11. 数据清除
truncate tb_user;//全清
delete from tb_user;//全清
delete from tb_user where id = 1000;//清除特定条件的数据

12. 索引相关操作
    （1） show index from tb_user;//查看表的索引
    
    （2）表索引相关

索引类型：primary，unique，index，分别对应逐渐索引，唯一索引以及普通索引

desc tb_user;

新增：
alter table tb_user add index(name);//普通索引，索引名称默认为字段名name。

alter table tb_user add index Index_Name(name);//这里的Index_Name是索引名称，name为column名
alter table tb_user add index Index_Age(age);//这里的Index_Age为索引名，age为column名

alter table tb_user add index Index_Combine(name,age);添加联合索引，这些索引是有顺位的，就像NBA选秀一样

alter table tb_user add index Index_Combine(name,age);添加联合索引，这些索引是有顺位的，就像NBA选秀一样

删除：根据索引名来删除，有两种方式
alert table 或者 drop index

alter table tb_user drop index Index_Name;//这里的Index_Name是索引名称
drop index Index_Name on tb_user;//删除索引

alter table tb_user drop primay key;验证了一下，好像没有删除成功主键，这个语句可能是错的？

当建立的索引名称与Column名称相同时，一定要注意删除的时候，可能会删除了Column。
例如：
alter table tb_user drop name;删列
alter table tb_user drop index name;删索引

13. 在指定列名前/后面新增列使用：before / after

14. 版本查看：select version();
    

15. 其他操作命令

    select @@basedir;//查看mysql根路径
    sleect @@datadir;//查看mysql数据存储路径。
    

    show processlist;//查看当前有哪些线程在运行，查看链接数以及这些链接状态，有助于识别有问题的查询语句。只能查看100条
    show full processlist//同上，但是可以查看所有链接。

如果是root账号可以看到所有链接，如果是一般账号，只能看到自己的链接。

16. 新建user
    create user ‘username’@’%’ identified by ‘password’;
    grant all privileges on . to ‘username’@’%’;
    flush privileges;//必要时刷新一下缓存

17. 修改表名
    alter table stu rename to student;