Mysql常用操作

常用端口：3306
常用用户：root

安装绿色版mysql步骤：

• 获取相应版本mysql软件。
• 修改安装路径、数据存放路径、数据库端口信息。my.ini(basedir/datadir/port)
• dos命令进入数据库安装目录下的bin文件。
• 执行mysqld -initialize,进行初始化。
• 执行mysqld -install MySQL --defaults-file=“D:\Dev\mysql-5.7.11\my.ini”,进行安装。
• 启动mysql服务，net start mysql (关闭服务：net stop mysql ，卸载服务：mysql:mysqld
  -remove 或者是：sc delete mysql)
• 本地登录mysql,首次登录不需要密码。（如果提示需要密码，再my.ini文件中添加：skip-grant-tables,保存并重启mysql）
• 修改root用户密码。（use mysql; update user set password=PASSWORD(‘123456’) where user=‘root’; FLUSH PRIVILEGES; QUIT）
• 删除my.ini文件中的：skip-grant-tables,然后保存并重启mysql。

常用命令：

• 命令行登录mysql：mysql -u root -p
• 命令行远程连接mysql：mysql -h 127.0.0.1 -P 3306 -u root -p
• 查看mysql中所有数据库信息：show databases
• 创建数据库：create database ‘name’
• 删除数据库：drop database ‘name’
• 选择数据库：use ‘databasename’
• 查看数据库中的所有表：show tables
• 查看数据表的具体信息：desc ‘tablename’ 或者 describe ‘tablename’
• 查询数据库的版本和时间：select version(),current_date;
• 退出mysql: \q 或者 quit
• 查看数据库正在执行的sql: show processlist

将查询到的数据直接插入到表中：

• INSERT INTO tbl_person_info ( name, major, professionalTitle, workUnit, telephone, careerTypeId, updateTime,level)
  SELECT name, major, professionalTitle, workUnit, telephone, careerTypeId, updateTime,level from tbl_person_info where careerTypeId = 29

索引：

• 创建索引
  ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY [索引名] (字段名1 [(长度)] [ASC | DESC]) [USING 索引方法]；
  或
  CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名 ON 表名(字段名) [USING 索引方法]；
  CREATE INDEX index_name ON tbl_name(cloumnNames)
• 查看索引
  show index from tbl_name
• 删除索引
  drop index index_name on name
• 强制使用某个索引（FORCE INDEX(indexName)）
  使用之前：
  
  使用之后：
  explain select ca.careerTypeName,ca.id,SUM(pe.level) from tbl_career_type ca
  LEFT JOIN tbl_person_info pe FORCE INDEX(index_name) on ca.id = pe.careerTypeId
  where ca.careerTypeName = ‘计算机’
  GROUP BY ca.careerTypeName,ca.id

查询sql执行计划：

使用关键字 explain

使用Navicat自带解释功能

结果

结果字段含义

• id：Query Optimizer 所选定的执行计划中查询的序列号
• select_type:所使用的查询类型，判断是否是复杂语句，主要有以下这几种查询类型
  DEPENDENT SUBQUERY：子查询中内层的第一个SELECT，依赖于外部查询的结果集；
  DEPENDENT UNION：子查询中的UNION，且为UNION 中从第二个SELECT 开始的后面所有SELECT，同样依赖于外部查询的结果集；
  PRIMARY：子查询中的最外层查询，注意并不是主键查询；
  SIMPLE：除子查询或者UNION 之外的其他查询；
  ** SUBQUERY：子查询内层查询的第一个SELECT，结果不依赖于外部查询结果集；
  ** UNCACHEABLE SUBQUERY：结果集无法缓存的子查询；
  UNION：UNION 语句中第二个SELECT 开始的后面所有SELECT，第一个SELECT 为PRIMARY
  UNION RESULT：UNION 中的合并结果；
• table：显示这一行的数据是关于哪张表的
• type：显示连接使用了何种类型，对表所使用的访问方式。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL
  说明：不同连接类型的解释（按照效率高低的顺序排序）
  system：系统表，表中只有一行数据。这是const连接类型的特殊情况。
  const ：读常量，且最多只会有一条记录匹配。表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待。
  eq_ref：最多只会有一条匹配结果，一般是通过主键或者唯一键索引来访问；在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用。
  ref：Join 语句中被驱动表索引引用查询，这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好。
  range：索引范围扫描，这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况。
  ref_or_null：与ref 的唯一区别就是在使用索引引用查询之外再增加一个空值的查询。
  unique_subquery：子查询中的返回结果字段组合是主键或者唯一约束
  index_merge：查询中同时使用两个（或更多）索引，然后对索引结果进行merge 之后再读取表数据；
  index_subquery：子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个主键或者唯一索引；
  index：全索引扫描，这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）。
  ALL：全表扫描，这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免。
• possible_keys：显示可能应用在这张表中的索引。这里的索引名字是创建索引时指定的索引昵称；如果索引没有昵称，则默认显示的是索引中第一个列的名字。如果为空，没有可能的索引，可以为相关的域从WHERE语句中选择一个合适的语句
• key：实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引
• key_len:使用的索引的长度。在不损失精确性的情况下，长度越短越好
• ref:显示的是列的名字，显示索引的哪一列被使用了，MySQL将根据这些列来选择行，如果可能的话，是一个常数 。“对于每一种与另一个表中记录的组合，MySQL将从当前的表读取所有带有匹配索引值的记录。如果连接操作只使用键的最左前缀，或者如果键不是 UNIQUE或PRIMARY KEY类型（换句话说，如果连接操作不能根据键值选择出唯一行），则MySQL使用ref连接类型。如果连接操作所用的键只匹配少量的记录，则ref是一 种好的连接类型。”
• rows:MYSQL认为必须检查的用来返回请求数据的行数 ，这里最理想的数字就是1。
• filtered:过滤率，最大100，表示没有对行进行筛选
• Extra:关于MYSQL如何解析查询的额外信息,这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢.
  说明：extra列返回的描述的意义
  Distinct ：一旦mysql找到了与行相联合匹配的行，就不再搜索了。
  Not exists ：mysql优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了。
  No tables：Query 语句中使用FROM DUAL 或者不包含任何FROM 子句；
  **Using filesort **：当我们的Query 中包含ORDER BY 操作，而且无法利用索引完成排序操作的时候，MySQL Query Optimizer 不得不选择相应的排序算法来实现。看到这个的时候，查询就需要优化了。mysql需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。
  **Using index **：所需要的数据只需要在Index 即可全部获得而不需要再到表中取数据。列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候。
  Using temporary ：当MySQL 在某些操作中必须使用临时表的时候，在Extra 信息中就会出现Using temporary 。主要常见于GROUP BY 和ORDER BY 等操作中。看到这个的时候，查询需要优化了。这里，mysql需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上。
  Using where：如果我们不是读取表的所有数据，或者不是仅仅通过索引就可以获取所有需要的数据，则会出现Using where 信息；
  **Where used **：使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类ALL或index，这就会发生，或者是查询有问题。
  Using index for group-by：数据访问和Using index 一样，所需数据只需要读取索引即可，而当Query 中使用了GROUP BY 或者DISTINCT 子句的时候，如果分组字段也在索引中，Extra 中的信息就会是Using index for group-by；
  Using where with pushed condition：这是一个仅仅在NDBCluster 存储引擎中才会出现的信息，而且还需要通过打开Condition Pushdown 优化功能才可能会被使用。控制参数为engine_condition_pushdown 。
  Full scan on NULL key：子查询中的一种优化方式，主要在遇到无法通过索引访问null值的使用使用；
  Impossible WHERE noticed after reading const tables：MySQL Query Optimizer 通过收集到的统计信息判断出不可能存在结果；
  Select tables optimized away：当我们使用某些聚合函数来访问存在索引的某个字段的时候，MySQL Query Optimizer 会通过索引而直接一次定位到所需的数据行完成整个查询。当然，前提是在Query 中不能有GROUP BY 操作。如使用MIN()或者MAX（）的时候；
  Range checked for each Record（index map:#） ：没有找到理想的索引，因此对从前面表中来的每一个行组合，mysql检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一。