Mysql数据库引擎、备份数据以及一些命令

MyISAM：它是基于传统的ISAM类型，ISAM是Indexed Sequential Access Method (有索引的 顺序访问方法) 的缩写，它是存储记录和文件的标准方法。与其他存储引擎比较，MyISAM具有检查和修复表格的大多数工具。
MyISAM表格可以被压缩，而且它们支持全文搜索。

1. 它们不是事务安全的，而且也不支持外键。
2. 如果事物回滚将造成不完全回滚，不具有原子性。
3. 如果执行大量 的SELECT，MyISAM是更好的选择。
4. 默认用的是表级锁，不支持行级锁。

InnoDB：

1. 这种类型是事务安全的。它与BDB类型具有相同的特性,它们还支持外键。
2. InnoDB表格速度很快，具有比BDB还丰富的特性。
3. 因此如果需要一个事务安全的存储引擎，建议使用它。如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。
4. 默认用的是行级锁，也支持表级锁。

MyISAM使用场景

频繁执行全表count语句
对数据进行增删改的频率不高，查询非常频繁
没有事务

InnoDB适合的场景

数据增删改查都相当频繁
可靠性要求比较高，要求支持事务

分析

对于支持事物的InnoDB类型的表，影响速度的主要原因是AUTOCOMMIT默认设置是打开的，而且程序没有显式调用BEGIN 开始事务，导致每插入一条都自动Commit，严重影响了速度。可以在执行sql前调用begin，多条sql形成一个事物(即使autocommit打开也可以)，将大大提高性能。

为MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来，甚至在同一个查询中也可以混合。

MySQL5.5以后默认使用InnoDB存储引擎。

对于InnoDB表，自动增长列必须是索引。如果是组合索引，也必须是组合索引的第一列，但是对于MyISAM表，自动增长列可以是组合索引的其他列，这样插入记录后，自动增长列是按照组合索引到前面几列排序后递增的。

MySQL5.5以后默认使用InnoDB存储引擎，其中InnoDB和BDB提供事务安全表，其它存储引擎都是非事务安全表。

SQL语言共分为四大类：数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL。

1. 数据查询语言DQL
   数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE
   子句组成的查询块：
   SELECT <字段名表>
   FROM <表或视图名>
   WHERE <查询条件>

2. 数据操纵语言DML
   数据操纵语言DML主要有三种形式：
   1). 插入：INSERT
   2). 更新：UPDATE
   3). 删除：DELETE

3. 数据定义语言DDL
   数据定义语言DDL用来创建数据库中的各种对象-----表、视图、
   索引、同义词、聚簇等如：
   CREATE TABLE/VIEW/INDEX/SYN/CLUSTER
   表 视图 索引 同义词 簇
   TRUNCATE
   DROP
   DDL操作是隐性提交的！不能rollback

4. 数据控制语言DCL
   数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制
   数据库操纵事务发生的时间及效果，对数据库实行监视等。如：
   1). GRANT：授权。
   2). ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。
   回滚—ROLLBACK
   回滚命令使数据库状态回到上次最后提交的状态。其格式为：
   SQL>ROLLBACK;
   3). COMMIT [WORK]：提交。

Mysql一些命令

• 查看表结构
  describe 表;
  desc 表;

• 自增长设置

  show variables like 'auto_inc%'; – 查看当前数据库的自增长设置

  即 auto_increment的初始值(offset)是1, 步长(increment)是1. (步长指每次的递增量)。即每次递增1.

  修改auto_increment的初始值和步长

  set @@auto_increment_increment=2; --设置步长为2
  set @@auto_increment_offset=2; --设置初始值为3

• 数据备份

  1. 使用mysqldump将数据导出
     若是服务其中的数据，需先进入服务器
     mysqldump -u root -p --no-create-info -c 数据库名 [表名] > 文件名.sql ;
     （--no-create-info -c 不包含创建表）

  • 导出数据库中的某个表
    mysqldump -uroot -proot --databases db1 --tables a1 a2 >/tmp/db1.sql
  • 导出所有数据库
    mysqldump -uroot -proot --all-databases >/tmp/all.sql

  2. 将导出的文件从服务器拷贝下来
     scp 服务器中用户名@ip地址:需要拷贝文件的路径 拷贝后文件存储位置

  scp是secure copy的简写，用于在Linux下进行远程拷贝文件的命令，和它类似的命令有cp，不过cp只是在本机进行拷贝不能跨服务器，而且scp传输是加密的。可能会稍微影响一下速度。当你服务器硬盘变为只读
  read only system时，用scp可以帮你把文件移出来。另外，scp还非常不占资源， 不会提高多少系统负荷，在这一点上，rsync就远远不及它了。虽然rsync比scp会快一点，但当小文件众多的情况下，rsync会导致硬盘I/O非常高，而scp基本不影响系统正常使用。

  scp root@www.runoob.com:/home/root/others/music /home/space/music/1.mp3

  3. 将sql文件还原至相应位置
     mysql -u root -p 数据库 < sql文件

• LIMIT OFFSET

  显示5到10行的记录，即查询6行记录
  select * from tablename limit 4,6;

  mysql里分页一般用limit来实现

  跳过第一条，输出三条
  select * from table LIMIT 1,3
select * from table LIMIT 3 OFFSET 1
  上面两种写法都表示取2,3,4三条条数据

• 使用 BINARY关键字，设置条件区分大小写
  SELECT * from runoob_tbl WHERE BINARY runoob_author=‘runoob.com’;

• INSERT INTO ... VALUES ...
  INSERT INTO table_name ( field1, field2,…fieldN )
  VALUES
  ( value1, value2,…valueN );

• UPDATE
UPDATE table_name SET field1=new-value1, field2=new-value2
  [WHERE Clause]

• DELETE
  DELETE FROM 表 [ WHERE 条件]

  1. 属于DML语言，每次删除一行，都在事务日志中为所删除的每行记录一项。
  2. 产生rollback，事务提交之后才生效;如果有相应的 trigger,执行的时候将被触发，如果删除大数据量的表速度会很慢。
  3. 删除表中数据而不删除表的结构(定义)，同时也不释放空间。[table|view]

• TRUNCATE
  truncate table table_name;

  1. 默认情况下，truncate通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。所以使用的系统和事务日志资源少，可以使用reuse storage; truncate会将高水线复位(回到最开始).
  2. truncate是DDL语言, 操作立即生效,自动提交，原数据不放到rollback segment中,不能回滚. 操作不触发trigger.
  3. 删除内容、释放空间但不删除表的结构(定义)。[table]

• DROP
  drop table table_name;

  1. 删除表的结构，以及被依赖的约束(constrain),触发器(trigger),索引(index);删除之后，依赖于该表的存储过程/函数将保留,但是变为invalid状态.
  2. drop也属于DDL语言，立即执行，执行速度最快
  3. 删除内容和定义，释放空间。

删除部分总结：

• delete 语句可以使用where子句实现部分删除，而truncate不可以，会将表中的整个数据全部删除，使用时，可以按需求选择；
• 如果想从表中删除所有的数据，不要使用delete，可以使用truncate语句，因为这样执行速度更快。truncate语句实际是删除原来的表然后重新建立一个新表；
• 在没有备份情况下，谨慎使用 drop 与 truncate。要删除表结构使用drop;
• 对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

删除数据时，不删除外键对应的数据

1. 关闭外键约束
   SET FOREIGN_KEY_CHECKS = 0;
2. 删除数据
   delete from mytable where id>157;
3. 启用外键约束
   SET FOREIGN_KEY_CHECKS = 1;

删除部分原文

修改密码

set password for '用户名'@'localhost'=password('新密码');

删除用户

drop user用户名;

外键的一些操作

创建表时已创建了外键约束

CONSTRAINT fk FOREIGN KEY (fk_id)
REFERENCES parent_table(id)

1. 删除外键约束
   alter table 表名 drop foreign key 外键约束名;

2. 增加外键
   constraint是约束关键字，fk是自己取的名字

   alter table 表名 add constraint 外键约束名 foreign key(在stu表中的字段名) references 外键所在表(外键对应字段)
on delete set null;

on delete 与 on update 的参数注意事项

on delete和on update都有restrict，no action, cascade,set null属性。

• ON DELETE

  1. restrict(约束):当在父表（即外键的来源表）中删除对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除。

  2. no action:意思同restrict.即如果存在从数据，不允许删除主数据。

  3. cascade(级联):当在父表（即外键的来源表）中删除对应记录时，首先检查该记录是否有对应外键，如果有则也删除外键在子表（即包含外键的表）中的记录。

  4. set null:当在父表（即外键的来源表）中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（不过这就要求该外键允许取null）

• ON UPDATE

  1. restrict(约束):当在父表（即外键的来源表）中更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许更新。

  2. no action:意思同restrict.

  3. cascade(级联):当在父表（即外键的来源表）中更新对应记录时，首先检查该记录是否有对应外键，如果有则也更新外键在子表（即包含外键的表）中的记录。

  4. set null:当在父表（即外键的来源表）中更新对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（不过这就要求该外键允许取null）。

注：restrict和no action的区别：restrict具有最高的优先权执行，no action是在其他约束的动作之后执行。

rank 有重复 跳
DENSE_RANK 有重复 不跳

partition分区
select dense_rank() over(partition by gc_no order by gs_scoure desc )a, * from test.[dbo].[grade]
row_number 不重复

修改某个表的字段名、类型、指定为空或非空

alter table 表名称 change 字段名称 字段新名称 字段类型 [是否允许非空];
alter table 表名称 modify 字段名称 字段类型 [是否允许非空];
例如:
修改表class中的字段caption ,允许其为空
alter table class change caption caption varchar(255) not null;

用户授权

1. mysql> grant all privileges on *.* to '用户'@'%' identified by '密码' with grant option;
   all privileges：表示将所有权限授予给用户。也可指定具体的权限，如：SELECT、CREATE、DROP等。

on：表示这些权限对哪些数据库和表生效，格式：数据库名.表名，这里写“*”表示所有数据库，所有表。如果我要指定将权限应用到test库的user表中，可以这么写：test.user
to：将权限授予哪个用户。格式：”用户名”@”登录IP或域名”。%表示没有限制，在任何主机都可以登录。比如：”yangxin”@”192.168.0.%”，表示yangxin这个用户只能在192.168.0IP段登录
identified by：指定用户的登录密码
with grant option：表示允许用户将自己的权限授权给其它用户

2. 对用户做了权限变更之后，一定记得重新加载一下权限，将权限信息从内存中写入数据库。
   mysql> flush privileges;
   权限部分原文
3. 查看用户权限
   show grants for 'yangxin'@'localhost';
4. 回收权限
   删除yangxin这个用户的create权限，该用户将不能创建数据库和表。
   mysql> revoke create on *.* from 'yangxin@localhost';
   mysql>flush privileges;
5. 删除用户
   drop user 'yangxin'@'localhost';

in与not in,exists与not exists的区别？

mysql中exists和in的区别有：

1. in是把外表和内表做hash连接，先查询内表；
2. exists是对外表做loop循环，循环后在对内表查询；
3. 在外表大的时用in效率更快，内表大用exists更快。

mysql中exists和in的区别有：

1、in是把外表和内表做hash连接，先查询内表；
2、exists是对外表做loop循环，循环后在对内表查询；
3、在外表大的时用in效率更快，内表大用exists更快。