MySQL常用操作

  1. 常用sql命令
  2. Linux 管理Mysql
  3. InnoDB与MyISAM引擎的区别与应用场景
  4. 脏读,幻读与不可重复读
  5. MySql数据最大长度

常用sql命令

远程登录和本地登录尽管用户名相同,密码却是各自不同的密码。不加identified之后的语句表示使用已存在的账号。

  1. 创建远程账号并授予与默认账户相同的访问权限
    grant all on *.* to root@'%' identified by'123456';
    注意:8.0 之后的mysql版本将创建账号与授权操作分开了,操作拆分:

    create user root@'%' identified by '123456';
    grant all on *.* to root@'%' with grant option;
    
  2. 创建本地账号并授权与默认账户相同的访问权限
    grant all on *.* to root@'localhost' identified by'123456789';

  3. 创建一个远程用户账号,并只能访问指定的数据库,且只有select和update权限
    grant select,update on testdb.* to 'testdb'@'%' identified by '123456';
  4. 刷新权限表,更新用户权限后,需要刷新权限表使其生效。
    flush privileges;
  5. 查看数据库编码
    SHOW VARIABLES LIKE '%character%';
    Mysql数据库编码
  6. 查看当前数据库所使用的引擎
    SHOW VARIABLES LIKE '%engin%';
    Mysql数据库引擎
  7. 查看数据库版本号
    SHOW VARIABLES LIKE '%version%';
    Mysql数据库版本号
  8. 创建新用户
    CREATE USER username IDENTIFIED BY 'password';
  9. 删除用户
    DROP USER username@localhost;
  10. 查看用户授权:
    show grants for 'tom'@'%';
  11. 修改密码:
    SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');
  12. 查询表结构:
    select * from information_schema.columns
    where table_schema = 'develop'  #表所在数据库
    and table_name = 'roles' ; #你要查的表
    
    或查看建表语句: show create table `tb_name`;
  13. 选择判断CASE语句
    CASE   <单值表达式>
        WHEN <表达式值> THEN <SQL语句或者返回值>
        WHEN <表达式值> THEN <SQL语句或者返回值>
        ...
        WHEN <表达式值> THEN <SQL语句或者返回值>
        ELSE <SQL语句或者返回值>
    END 
    
    13.1. 简单Case函数写法(注意sex的位置)
    select *,(CASE sex WHEN '1' THEN '男' WHEN '0' THEN '女' ELSE '保密' END) as sex_text
    from user
    
    13.2. Case搜索函数写法(注意sex的位置)
    select *,(CASE WHEN sex='1' THEN '男' WHEN sex='0' THEN '女' ELSE '保密' END) as sex_text
    from user
    
  14. 其他实用命令

    show databases;
    show tables;
    DROP TABLE IF EXISTS table_name; 删除表,有日志,可回滚,触发trigger
    truncate table_name; 清空表并重置索引和自增ID,无日志,无法回滚,不触发trigger
    revoke update on prod.* from username; 回收用户权限
    create table if not exists newname like oldname; 创建一个和旧表结构一样的新表
    insert into tbname2 select * from tbname1; 旧表数据插入另一个表,要求tbname2表已存在。
    select * into new_tbname from old_tbname; 将旧表数据导入新表,要求新表不存在。

  15. 外部管理:

    /usr/sbin/mysqld --verbose --help 查看mysql配置,my.cnf路径等
    mysql -u[USER_NAME] -p 登录

Linux 管理Mysql

配置文件默认在:/ect/my.cnf
一般配置如下:

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
# 让mysql绑定IPv4地址
bind-address=0.0.0.0
port = 3306
character-set-server = utf8
collation-server = utf8_general_ci
init_connect='SET NAMES utf8'
lower_case_table_names = 1
max_connections = 400
max_connect_errors = 1000
max_allowed_packet = 1G

# myslq5.7对密码有复杂度要求,此配置关闭验证
validate_password=OFF

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

登陆mysql:
mysql -u username -p password

CentOS 6.x下:
service mysqld status 查看Mysql状态

service mysqld start/stop/restart 开启/关闭/重启(若使用service mysqld start/stop/restart 无法启动服务,可尝试使用/etc/init.d/mysqld 直接启动)
chkconfig mysqld on 设置开机自启动
chkconfig –list|grep mysqld 检查状态

CentOS 7.x下:
systemctl start/stop/restart mysqld
设置开机自启动
systemctl enable mysqld
systemctl daemon-reload

##

InnoDB与MyISAM引擎的区别与应用场景

  1. 事务处理:MyISAM是非事务安全型的,而InnoDB是事务安全型的(支持事务处理等高级处理)
  2. 并发控制不同:MyISAM是表级锁,而InnoDB是支持MVCC(多版本并发控制)的行级锁
  3. 索引实现:MyISAM和InnoDB的索引实现都是基于B+TREE。这两种存储引擎都是通过primary key(id)或者index(col1,col2…)的方式来添加索引的,在内部实现上这两种索引分别叫做主键索引和辅助索引,而这4种(2种引擎 x 2种索引)索引都是基于B+TREE的。MyISAM是非聚集索引,在B+TREE中叶子节点数据除了有索引数据,剩下的是存储物理行的地址,主键索引和辅助索引实现上几乎没什么区别。InnoDB的主键索引是聚集索引,辅助索引是非聚集索引。聚集索引指的是,在B+TREE中的叶子节点数据包含了记录中的全部数据,InnoDB辅助索引中和MyISAM不同的是,辅助索引除了索引值包含的是该行的主键值。
  4. 外键支持:MyISAM表不支持外键,而InnoDB支持
  5. 查询表的行数不同:MyISAM的行数是保存好的,InnoDB需要遍历
  6. select ,update ,insert ,delete 操作:如果执行大量的SELECT,MyISAM是更好的选择;如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑,应该使用InnoDB表。
  7. 但是如果InnoDB有大数据列,比如 varchar(300),这种比较多的话,那么排序的时候用主键排序会比较慢,因为主键下面放着所有数据列。

总的来说,如果MyISAM是比InnoDB更简单的存储引擎,因为MyISAM相对简单所以在效率上要优于InnoDB。如果系统读多,写少,对原子性要求低。那么MyISAM最好的选择。且MyISAM恢复速度快。可直接用备份覆盖恢复。

如果系统读少,写多的时候,尤其是并发写入高的时候。InnoDB就是首选了。

脏读,幻读与不可重复读

脏读:一个事务读取到了另外一个事务没有提交的数据(未确认的相关性)
一个事务正在访问数据,并且对数据进行了修改,修改还没有提交到数据库中,这时,另外一个事务也访问这个数据,然后使用了这个新的数据(脏数据)。如果事务一回滚了,事务二的操作将是错误的。

幻读:同一事务中,用同样的操作读取多次,得到的记录行数不相同(丢失或覆盖更新)
一个事务查询了表中的所有记录,同时,第二个事务新增或删除了若干条记录。第一个事务的用户再次查询,发现两次查到的记录数量不一致

不可重复读的:在同一事务中,用同样的操作读取多次,得到记录内容不一致(不一致的分析)
一个事务内根据同一个条件对记录进行多次查询,同时,第二个事务对数据进行了修改,这种修改覆盖到了第一个事务查询到的记录。第一个事务的用户再次查询时,发现两次查到的记录内容不一致

参考:

Innodb与Myisam引擎的区别与应用场景

脏读、不可重复读、幻读

何为脏读、不可重复读、幻读

MySql数据最大长度

MySQL 3种text类型的最大长度如下:

VARCHAR 2^16-1-1(null标识位)-2(长度标识位) = 65533 bytes

TEXT 2^16-1 = 65535 bytes ~64kb (使用另外空间存放长度位)

MEDIUMTEXT 2^24-1 = 16777215 bytes ~16Mb

LONGTEXT 2^32-1 = 4294967295 bytes ~4Gb

以VARCHAR为例,最多可存储 65533 bytes:

若采用utf8编码,每字符3个字节,则字符长度不能超过 21844

若采用gbk编码,每字符2个字节,则字符长度不不能超过32766

从官方文档中我们可以得知当varchar大于某些数值的时候,其会自动转换为text,大概规则如下:

  • 大于varchar(255)变为 tinytext
  • 大于varchar(500)变为 text
  • 大于varchar(20000)变为 mediumtext

所以对于过大的内容使用varchar和text没有太多区别。关于索引性能方面,text是不超过 1000 bytes的前缀索引,varchar是全部索引,但实际最大也是 1000 bytes,所以性能也没有太大的区别。

MySQL之char、varchar和text的设计


转载请注明来源,欢迎对文章中的引用来源进行考证,欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论,也可以邮件至 using1174@foxmail.com

文章标题: MySQL常用操作

文章字数: 2,004

本文作者: Jun

发布时间: 2018-06-14, 23:15:00

最后更新: 2022-08-19, 20:30:50

原始链接: http://yoursite.com/2018/06/14/MySQL常用操作/

版权声明: "署名-非商用-相同方式共享 4.0" 转载请保留原文链接及作者。

目录
×

喜欢就点赞,疼爱就打赏