mysql基本操作

一、连接操作

 格式：mysql-h主机地址 -u用户名 -p用户密码

例：连接到远程主机上的mysql假设远程主机的IP为：110.110.110.110用户

名为root,密码为abcd123则键入以下命令：

mysql-h110.110.110.110-uroot-pabcd123

二、用户级操作

1、select user() 查看自己的用户名

2、Linux命令ps -el | grep mysqld用来检测mysql服务器是否在运行。如果结果为：

   4 S  0  1796  1  0  85   0 -  1513 wait   ?   00:00:00 mysqld_safe

   4 S  27 1856  1796  0  78   0 - 34055 -      ?        00:00:00 mysqld

   就说明服务器已经运行起来了。

3、修改密码

update user set password="新密码" where user="用户名"。

4、新加用户

Grant select on 数据库.*to 用户名@登录主机 identified by “密码”。

三、数据库操作

1、创建数据库 create database <数据库名>

2、显示数据库 show database；

3、删除数据库 drop database <数据库名>

4、连接数据库 use <数据库名>

5、当前选中的数据库 select database();

6、查看数据库编码SHOW VARIABLES LIKE 'character%';

四、数据表操作

1、创建数据表 create table <表名>( <字段名1><类型1>,<字段名2><类型2>)；

2、查看表结构 describe 表名

3、获取表结构 desc表名，或者show columns from 表名；

4、删除数据表 drop table<表名>

5、表插入数据 insert into 表名 (字段1，字段2......) values ("值1"，“值2”...),....("值1","值2");

6、查询表中数据 select <字段1，字段2....>from<表名>where<表达式> ，查看前2行数据 select*from表名 limit（0，2）

7、删除表中数据 delete from 表名 where 表达式

8、修改表中数据 update 表名 set 字段=新值，..where 条件

9、增加字段 alter table 表名 add 字段 类型 其他

   加索引   alter table 表名 add index 索引名 （字段1，字段2）；

   加主键   alter table 表名 add primary key（字段名）；

   删除某个索引 alter table 表名 drop index 索引名；

   修改原字段名称及类型 alter table 表名 change旧字段名 新字段名 新类型；

   删除字段 alter table 表名 drop 字段名

10、修改表名  rename table 旧表名 to 新表名

五、备份数据库

 

1、导出整个数据库

   导出文件默认是存在mysql\bin目录下
   mysqldump -u 用户名 -p 数据库名 > 导出的文件名
   mysqldump -u user_name -p123456 database_name > outfile_name.sql

2、导出一个表

   mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
   mysqldump -u user_name -p database_name table_name > outfile_name.sql

3、导出一个数据库结构

   mysqldump -u user_name -p -d –add-drop-table database_name > outfile_name.sql
   -d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table

4、带语言参数导出

   mysqldump -uroot -p –default-character-set=latin1 –set-charset=gbk –skip-opt      database_name > outfile_name.sql

5、导入文件 source 文件路径  mysql>source d:\mydb.sql

注：导出文件时在操作系统控制台的ySQL\MySQL Server 5.5\bin目录下执行 ，导入文件是在mysql控制台执行

 

六、query cache

Query Cache 缓存了我们客户端提交给 MySQL 的 SELECT 语句以及该语句的结果集。大概来讲，就是将 SELECT 语句和语句的结果做了一个 HASH 映射关系然后保存在一定的内存区域中。

1、query cache 的五个变量（使用show variable like '%query_cache%'语句查看）

• query_cache_limit：允许 Cache 的单条 Query 结果集的最大容量，默认是1MB，超过此参数设置的 Query 结果集将不会被 Cache
• query_cache_min_res_unit：设置 Query Cache 中每次分配内存的最小空间大小，也就是每个 Query 的 Cache 最小占用的内存空间大小
• query_cache_size：设置 Query Cache 所使用的内存大小，默认值为0，大小必须是1024的整数倍，如果不是整数倍，MySQL 会自动调整降低最小量以达到1024的倍数
• query_cache_type：控制 Query Cache 功能的开关，可以设置为0(OFF),1(ON)和2(DEMAND)三种，意义分别如下：

o 0(OFF)：关闭 Query Cache 功能，任何情况下都不会使用 Query Cache

o 1(ON)：开启 Query Cache 功能，但是当 SELECT 语句中使用的 SQL_NO_CACHE 提示后，将不使用Query Cache

o 2(DEMAND)：开启 Query Cache 功能，但是只有当 SELECT 语句中使用了 SQL_CACHE 提示后，才使用 Query Cache

• query_cache_wlock_invalidate：控制当有写锁定发生在表上的时刻是否先失效该表相关的 Query Cache，如果设置为 1(TRUE)，则在写锁定的同时将失效该表相关的所有 Query Cache，如果设置为0(FALSE)则在锁定时刻仍然允许读取该表相关的 Query Cache。

 

2、query cache处理子查询

     query cache 是以客户端请求提交的 Query 为对象来处理的，只要客户端请求的是一个 Query，无论这个 Query 是一个简单的单表查询还是多表 Join，亦或者是带有子查询的复杂 SQL，都被当作成一个 Query，不会被分拆成多个 Query 来进行 Cache。所以，存在子查询的复杂 Query 也只会产生一个Cache对象，子查询不会产生单独的Cache内容。UNION[ALL] 类型的语句也同样如此。

 

3、query cache 的效率

Query Cache 的查找，是在 MySQL 接受到客户端请求后在对 Query 进行权限验证之后，SQL 解析之前。当 MySQL 接受到客户端的SQL后，仅仅只需要对其进行相应的权限验证后就会通过 Query Cache 来查找结果，甚至都不需要经过 Optimizer （查询优化器）模块进行执行计划的分析优化，更不许要发生任何存储引擎的交互，减少了大量的磁盘 IO 和 CPU 运算，所以效率非常高。

 

4、query cache的失效

为了保证 Query Cache 中的内容与是实际数据绝对一致，当表中的数据有任何变化，包括新增，修改，删除等，都会使所有引用到该表的 SQL 的 Query Cache 失效。

 

5、query cache导致系统整体性能下降

当开启了 Query Cache 之后，尤其是当我们的 query_cache_type 参数设置为 1 以后，MySQL 会对每个 SELECT 语句都进行 Query Cache 查找，查找操作虽然比较简单，但仍然也是要消耗一些 CPU 运算资源的。而由于 Query Cache 的失效机制的特性，可能由于表上的数据变化比较频繁，大量的 Query Cache 频繁的被失效，所以 Query Cache 的命中率就可能比较低下。所以有些场景下，Query Cache 不仅不能提高效率，反而可能造成负面影响。

 

6、query cache的几种状态参数（使用show status like'%Qcache%'语句查看）

• Qcache_free_blocks：目前还处于空闲状态的 Query Cache 中内存 Block 数目
• Qcache_free_memory：目前还处于空闲状态的 Query Cache 内存总量
• Qcache_hits：Query Cache 命中次数
• Qcache_inserts：向 Query Cache 中插入新的 Query Cache 的次数，也就是没有命中的次数
• Qcache_lowmem_prunes：当 Query Cache 内存容量不够，需要从中删除老的 Query Cache 以给新的 Cache 对象使用的次数
• Qcache_not_cached：没有被 Cache 的 SQL 数，包括无法被 Cache 的 SQL 以及由于 query_cache_type 设置的不会被 Cache 的 SQL
• Qcache_queries_in_cache：目前在 Query Cache 中的 SQL 数量
• Qcache_total_blocks：Query Cache 中总的 Block 数量

七、存储引擎

1、在线交易范畴内的表都统一使用innodb存储引擎，而不使用myisam的原因

n myisam不支持事务；如果存在myisam与innodb混用的话，事务数据的一致性存在风险。

n myisam读写阻塞，不适用于高并发环境；innodb支持行级锁。

n myisam不支持在线备份，配置复制时可能需要停机或者长时间锁表。

n myisam不够可靠，容易损坏丢失数据。

n 如果两种引擎混用，会给资源分配带来问题。因为有很多参数和设置需要停库才能修改，为了减少停库次数，我们目前都是采用预先最大化分配足额内存和磁盘空间的方式去做的。而myisam和innodb在内存和空间使用上都不一样，如果两者混用，那分配的时候就很难两方都照顾到。如果两者都按比例分配，很可能存在资源不能充分利用的情况；且随着业务的变化，需要调整的话，也会存在停机问题。

n 运维需要收集很多的运行状态数据，innodb有非常多的状态数据可收集；而对于myisam引擎，能收集到的数据非常有限，不利于我们做监控和容量规划。

2、查看存储引擎

n 查看支持的存储引擎

show engines

n 查看mysql默认的存储引擎

show variables like "%storage_engine%"

n 查看某个表用了什么引擎（在显示结果参数engine后面的就表示当前用的存储引擎）

    show create table 表名

八、mysql架构图

 架构示意简图

 

 

 

模块间的关系图

 

九、Mysql中的数据类型

1、数字日期类型

常用的时间存储格式主要有datetime、date、timestamp。从存储空间来看timestamp  4个字节，而其他，date 3个字节，datetime 8个字节，而timestamp只能存储1970年之后的时间。

2、字符类型

char(M)是静态长度类型，存放长度以字符数来计算，所以最终存储长度是基于字符集的，latin1最大存储长度为255字节，gbk为510字节。mysql5.0.3以前，如果定义的M值超过255，mysql会自动将char类型转换为可以存入对应数据量的text类型，而mysql5.0.3以后，所以超过255的定义都会直接拒绝，不再自动转换。

varchar(M)属于动态存储长度类型，仅存储占实际存储数据的长度。在mysql5.0.3以前varchar最大只能存放255个字符，占用存储空间的实际大小与字符集有关。从5.0.3开始，varchar的最大存储现在已经改为字节数。而且不再有单个字段的限制，而是除text和blog类型字段外单条记录最大不超过65536bytes。不过M仍然表示字符数，当实际数据在255字节之内时，会使用1个字节来存放实际长度，而大于255字节时，则需要使用2字节来存放。