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
语句
13.1. 简单Case函数写法(注意sex的位置)CASE <单值表达式> WHEN <表达式值> THEN <SQL语句或者返回值> WHEN <表达式值> THEN <SQL语句或者返回值> ... WHEN <表达式值> THEN <SQL语句或者返回值> ELSE <SQL语句或者返回值> END
13.2. Case搜索函数写法(注意sex的位置)select *,(CASE sex WHEN '1' THEN '男' WHEN '0' THEN '女' ELSE '保密' END) as sex_text from user
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;
删除表,有日志,可回滚,触发triggertruncate table_name;
清空表并重置索引和自增ID,无日志,无法回滚,不触发triggerrevoke 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就是首选了。
脏读,幻读与不可重复读
脏读
:一个事务读取到了另外一个事务没有提交的数据(未确认的相关性)
一个事务正在访问数据,并且对数据进行了修改,修改还没有提交到数据库中,这时,另外一个事务也访问这个数据,然后使用了这个新的数据(脏数据)。如果事务一回滚了,事务二的操作将是错误的。
幻读
:同一事务中,用同样的操作读取多次,得到的记录行数不相同(丢失或覆盖更新)
一个事务查询了表中的所有记录,同时,第二个事务新增或删除了若干条记录。第一个事务的用户再次查询,发现两次查到的记录数量不一致。
不可重复读的
:在同一事务中,用同样的操作读取多次,得到记录内容不一致(不一致的分析)
一个事务内根据同一个条件对记录进行多次查询,同时,第二个事务对数据进行了修改,这种修改覆盖到了第一个事务查询到的记录。第一个事务的用户再次查询时,发现两次查到的记录内容不一致。
参考:
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,所以性能也没有太大的区别。
转载请注明来源,欢迎对文章中的引用来源进行考证,欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论,也可以邮件至 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" 转载请保留原文链接及作者。