MySQL常用操作

MySQL

创建时间: 2018-06-14 23:15

字数: 2,004 阅读:

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

常用sql命令

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

创建远程账号并授予与默认账户相同的访问权限
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;
```
创建本地账号并授权与默认账户相同的访问权限
grant all on *.* to root@'localhost' identified by'123456789';
创建一个远程用户账号，并只能访问指定的数据库,且只有select和update权限
grant select,update on testdb.* to 'testdb'@'%' identified by '123456';
刷新权限表,更新用户权限后，需要刷新权限表使其生效。
flush privileges;
查看数据库编码
SHOW VARIABLES LIKE '%character%';
查看当前数据库所使用的引擎
SHOW VARIABLES LIKE '%engin%';
查看数据库版本号
SHOW VARIABLES LIKE '%version%';
创建新用户
CREATE USER username IDENTIFIED BY 'password';
删除用户
DROP USER username@localhost;
查看用户授权：
show grants for 'tom'@'%';
修改密码：
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');

查询表结构：

select * from information_schema.columns
where table_schema = 'develop'  #表所在数据库
and table_name = 'roles' ; #你要查的表

或查看建表语句： show create table `tb_name`;

选择判断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

其他实用命令

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; 将旧表数据导入新表，要求新表不存在。
外部管理：

/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引擎的区别与应用场景

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

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

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