SqlReview

*** 以mysql  库moon中的user_t为例。

数据库操作：

  show databases;

  use database;

  create database moon;

  drop database moon;

  sp_detach_db： 分离数据库

  sp_attach_db ： 附加数据库   sp_attach_db  moon C:/....(路径)

  sp_renamedb ： 重命名   sp_renamedb  moon  moon_new

表操作：

  show tables;

create table 'moon' (
'id' int(11) not null auto_increment,
'name' varchar(50) default null, 
'birth' datetime not null, 
 primary key ('id') 
) engine=InnoDB auto_increment=22 default charset=utf-8;
注： engine=InnoDB 为默认引擎
    auto_increment=22 为数字自增，从22开始自增
    charset=utf-8  这里的utf-8作用于此表单，  my.ini中的utf-8为mysql语言编码   
    

show create table user_t;   (可以生成建表语句)

create table tab_new like tab_old (使用旧表创建新表)

Alter table user_t add column col tell  (列增加后将不能删除)

Alter table user_t add primary key(name)  (添加主键)

Alter table user_t drop primary key(name)  （删除主键）

 引擎 ：

  show table status from moon;  (查看moon中各表引擎信息)

  

  修改mysql默认引擎： 将mysql.ini另存为my.ini，在[mysqld]后面添加default-storage-engine=InnoDB，重启服务

                                     建表设定引擎：                           

                        create table mytbl(   
                            id int primary key,   
                            name varchar(50)   
                        )type=MyISAM;

建表后修改引擎：

alter table table_name type = InnoDB;

 desc user_t;   （查看表结构）

 show engines;  （查看版本支持引擎） 

存储引擎主要有： 1. MyIsam , 2. InnoDB, 3. Memory, 4. Blackhole,
 5. CSV, 6. Performance_Schema, 7. Archive, 8. Federated , 9 Mrg_Myisam

  InnoDB  是一个事务型的存储引擎，有行级锁定和外键约束。

          支持事务（提交、回滚和崩溃恢复能力）与外键约束（只有InnoDB支持外键 ），适合高并发操作，确定扫描范围下select不会造成锁表。

          支持自动增加列属性。适合处理多重并发的更新请求。

          支持数据恢复

          索引使用B+树

它的设计目标是处理大容量数据库系统，它本身其实就是基于MySQL后台的完整数据库系统，MySQL
运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索
引，而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数
据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，
使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能
确定要扫描的范围，InnoDB表同样会锁全表。

  MyIsam 

          独立于操作系统，方便数据转移

          不支持事务、行级锁和外键，插入和更新操作需要锁定整个表，效率低

          存储了表的行数，可以快速进行读取操作（索引使用B+树），适合加锁场景较多业务，insert和update操作较少时比较适用

          表损坏后无法主动恢复数据

  BlackHole

        不做实际存储，会记录表日志，适合做日志服务器，记录操作。

        适用于复制数据到备份库。

        适用于验证dump file语法的正确性和使用blackhole引擎来检测binlog功能所需要的额外负载

 引擎学习贴一下：https://www.cnblogs.com/sunsky303/p/8274586.html

 事务：

   ACID (atomicity、consistency、isolation、durability）原子性、一致性、隔离性和持久性

   原子性：事务提交要么提交成功，要么全部回滚

  一致性： 事务执行过程中被迫中断导致版本差异，破坏一致性

  隔离性： 并发执行的各个事务不能互相干扰 ，四种隔离级别如下：

                 Read Uncommited：未授权读取，其他事务可以读取事务执行过程中的值，此为脏读，隔离级别最低

                 Read Commited： 授权读取，其他事物只可读取到事务提交完成的值。允许不可重复读

                 Repeatable Read：可重复读取，禁止不可重复读取和脏读取，会出现幻影数据，即读取到事务提交前后的不同值

                 serializable：串行化， 隔离级别最高，不允许并发

 持久性： 已提交的事务会永久保存到数据库中

//停掉事务
set autocommit = 0;  
//调用存储过程
call ptestInndb; 
//重启事务
set autocommit = 1; 

事务操作： 

start transaction;  #手动开启事务
DML  (增删改)操作
commit;  #提交之后数据改变生效  rollback;  #事务回滚

 索引：

 索引是对查询优化最有效的手段，mysql中的索引是在存储引擎层而不是服务器层。索引分为聚簇索引和非聚簇索引，

     非聚集索引：通过B+树数据域存储数据地址，其索引和数据分开存储

                       B+树大法举例： select user_t.name from user_t where id = '1';

                           先搜索索引，如果存在id，取得key为id的索引存储的地址，去相应地址获取数据 '1'

                           B+树原理：根节点和分支节点不保存数据只用于索引，数据保存在叶子节点中。 比起B树叶子节点形成有序链表，便于范围查询也可以存储更多元素，查询磁盘IO次数少，查询性能稳定。  

聚集的优点：
可以把相关数据保存在一起。减少磁盘I/O
数据访问更快
使用覆盖索引扫描的查询可以直接使用页节点中的主键值
聚集的缺点：
聚簇数据最大限度地提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没有那么重要了，聚簇索引也就没什么优势了。
插入速速严重依赖于插入顺序。
更新聚簇索引列的代价很高。
出入新行或者主键更新需要移动时，可能面临"页分裂(page split)"问题。当行的主键值要求必须插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。
二级索引（非聚簇索引）即普通索引，在其叶子节点包含了引用行的主键列。

    前缀索引: 使索引更小，更快的有效办法, 但是无法使用前缀索引做order by 和group by，也无法使用前缀索引做覆盖扫描。

 create [unique] index idxname on tabname(col….) （创建索引）

create table user_t（
     id  int unsigned NOT NULL auto_increment,     #unsigned  无符号 用此类型可以增加数据长度
     name varchar(255) NOT NULL,
     id_index int unsigned NOT NULL DEFAULT 0,
     PRIMARY KEY (id),
     KEY (id_index)  #索引列
）;

 

索引方式（mysql）：

    btree: Innodb 和 MyIsam 默认方式，Mermory默认为hash  可用操作符：=,>,>=,<,<=between like ,通配符(%)开头，或者没有使用常量，则不会使用索引。

    hash: 算法是基于等值计算，只能用于对等比较  =,<=>,不需从根节点到页节点检索，因此检索效率也远高于BTree索引（精确定位查找），但无法范围查找。  不支持索引排序

sql索引：

1.添加PRIMARY KEY（主键索引）：
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 
2.添加UNIQUE(唯一索引) ：
ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 
3.添加INDEX(普通索引) ：
写法一：ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
写法二：CREATE INDEX index_name ON `table_name`(`column1`,`column2`,`column3`) 

       举例：alter table user_t add  index name_index(name);

4.添加FULLTEXT(全文索引) ：
ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 
5.添加多列索引：
写法一：ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
写法二：CREATE INDEX index_name ON `table_name`(`column1`,`column2`,`column3`)
SHOW INDEX FROM `table_name`;-- 查询表索引
DROP INDEX index_name ON `table_name`;-- 删除索引


       show index from user_t; show keys from user_t;   #显示表索引列
       describe menu_t;  #表设置

表字段根据业务需求设计索引后，CRUD会自行体现。

锁：

行锁（Record Locks）

间隙锁（Gap Locks）

临键锁（Next-key Locks）

共享锁/排他锁（Shared and Exclusive Locks）

意向共享锁/意向排他锁（Intention Shared and Exclusive Locks）

插入意向锁（Insert Intention Locks）

自增锁（Auto-inc Locks）
 

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html MySQL官网

https://www.cnblogs.com/rjzheng/p/9950951.html 史上最全的select加锁分析

https://blog.csdn.net/wufaliang003/article/details/81937418 InnoDB并发插入，居然使用意向锁

https://blog.csdn.net/ignorewho/article/details/86423147 插入意向锁

https://www.cnblogs.com/rjzheng/p/9955395.html MySQL事务隔离级别

https://www.jianshu.com/p/68b581481831AUTO-INC锁和AUTO_INCREMENT在InnoDB中处理方式https://www.jianshu.com/p/10a8d8977aaf  MySQL自增锁模式innodb_autoinc_lock_mode参数详解

运算符：

union: 组合两个表生成无重复字段的结果表     union all  不消除重复行

except  所有在表1中但不在表2中的行并消除重复行的结果表     except all  不消除重复行

intersect: 取表1和表2都有的并消除重复的结果表     intersect all 不消除重复行

 mysql建表大小写 ： utf8_general_ci：不区分大小写。   utf8_bin：区分大小写。

细节学习贴一下：https://www.runoob.com/mysql/mysql-tutorial.html

sql 

   like ’%value1%’

   oder by asc(正序）desc

  count : select count as totalcount from table1    [sum, avg, max, min]   

  is null: 判空   is not null    返回1或0

  between and :   between 1 and 3    1>=value <=3   返回1或0  

  in  : 是否存在于某个集合中   in (1,2,3)   返回1或0

  regexp:  regexp '正则'.

  xor: 异或 :真假 ===真 ，  真真（假假）===假                        ...(&& !  ||)

  left (outer) join :  左表所有行以及匹配连接行

  inner join:等值连接

  full/cross (outer) join:  去两表全集

  group by :  以count,sum,max,min,avg  分组，得到分组结果

  limit: 写于order by 之后。 limit 1,9    从第二位开始，获取九位数。  limit 0,9 等价于 limit 9 。 limit -1 新版mysql已当bug修改，             不再支持，可以返回一个较大的正数。

  not exists： 不包括，where not exists(....= ...)

select * from user_t u where not exists (select m.id from menu_t m where u.id != m.id);

 datediff: 返回两个日期之间的时间间隔。 

select * from user_t u where datediff(’minute’,u.birth,getdate())>5;  #已经超过了开始时间5分钟的

 distinct ：  去重 。 select distinct u.name from user_t u;

select name from user_t where id in (select id from menu_t);
select u.name as max_name from user_t u where  u.id >= (select avg(u.id)from user_t u);
select count(u.name) as max_name from user_t u where u.id between 1 and 1000;
select u.name as max_name from user_t u where u.id in(1,1000);
select u.id,u.name from user_t u left join menu_t m on m.id = u.id ;
#select u.id,u.name from user_t u full join menu_t m on m.id = '1' ; (X)
select u.name , u.id ,m.id as m_id from user_t u,menu_t m where not exists (select m.id from menu_t m where m.id = u.id);  #user_t的id在外边，以u.id为主作比较,列出所有情况
select * from user_t u where  u.id >= (select avg(u.id) from user_t u) order by u.id desc limit 3,10;
select * from user_t u where not exists (select m.id from menu_t m where u.id != m.id);
select * from user_t u where datediff(’minute’,u.birth,getdate())>5; 

sql注入：

###输入数据sql注入
  if (get_magic_quotes_gpc()) 
{
  $name = stripslashes($name);
}
$name = mysqli_real_escape_string($conn, $name);
 mysqli_query($conn, "SELECT * FROM users WHERE name='{$name}'");

####like语句sql注入
$sub = addcslashes(mysqli_real_escape_string($conn, "%something_"), "%_");
// $sub == \%something\_
 mysqli_query($conn, "SELECT * FROM messages WHERE subject LIKE '{$sub}%'");

sql执行顺序：

1. FROM

2. ON

3. JOIN

4. WHERE

5. GROUP BY

6. WITH CUBE or WITH ROLLUP

7. HAVING

8. SELECT

9. DISTINCT

10. ORDER BY

11. TOP