sql学习

1、sql语句中where与having的区别

例1：要查找平均工资大于3000的部门
	select deparment, avg(salary) as average from salary_info group by deparment having average > 3000
	此时只能使用having，而不能使用where。一来，我们要使用聚合语句avg；二来，我们要对聚合后的结果进行筛选（average > 3000），因此使用where会被告知sql有误。
例2：要查询每个部门工资大于3000的员工个数	
	select deparment, count(*) as c from salary_info where salary > 3000 group by deparment
	此处的where不可用having进行替换，因为是直接对库中的数据进行筛选，而非对结果集进行筛选		
select appname, count(*) as count from t_etl_job where appname='ostsdk' group by appname;	    where只能放group by前面，且where后面不能接count
select appname, count(*) as count from t_etl_job group by appname having count>1	having放group by后面， 可以接count

2、case when用法：select *,case when sex=‘1’ then ‘男’ when sex=‘2’ then ‘女’ end as sexdesc from score;
3、MySQL对于经常查询或者删除的字段一定要加索引，否则数据量大之后，删都删不了
4、MySQL截取子字符串：substring(字段,1,4)
5、select distinct(id) from… 这里有了distinct，就不能这样写：select name,distinct(id)。。。。。

6、MySQL为什么加锁：
当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。加锁是实现数据库并发控制的一个非常重要的技术。
7、死锁原因
1）死锁的第一种情况
一个用户A 访问表A(锁住了表A),然后又访问表B；另一个用户B 访问表B(锁住了表B)，然后企图访问表A；这就死锁就产生了。
解决方法：
这种死锁比较常见，是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法。仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进 行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理， 必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。
或者最暴力的：
查看下在锁的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
杀死进程id（就是上面命令的trx_mysql_thread_id列）
kill 线程ID
8、MySQL锁
分类

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

表级锁和行级锁

• MyISAM的表级锁
  MyISAM只支持表级锁，MyISAM表级锁有以下两种模式：
  表共享读锁 （Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
  表独占写锁 （Table Write Lock）：会阻塞其他用户对同一表的读和写操作；
  MyISAM加表锁方法：
  MyISAM 在执行查询语句（SELECT）前，会自动给涉及的表加读锁，在执行更新操作UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要用 LOCK TABLE 命令给 MyISAM 表显式加锁

• InnoDB行级锁和表级锁
  InnoDB行锁的模式：
  共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
  排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

  InnoDB表锁（意向锁，Intention Locks）的模式：
  意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
  意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

  InnoDB 行锁实现方式：
  InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。

  InnoDB加锁方法：
  意向锁是 InnoDB 自动加的， 不需用户干预。
  而行锁，对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB会自动给涉及数据集加排他锁（X)；对于普通 SELECT 语句，InnoDB 不会加任何锁；事务可以通过以下语句显式给记录集加共享锁或排他锁：

  • 共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE。 其他session 仍然可以查询记录，并也可以对该记录加 share mode的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。
  • 排他锁（X)：SELECT * FROM table_name WHERE … FOR UPDATE。其他 session可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁

• InnoDB的间隙锁：
  当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。
  举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：
  Select * from emp where empid > 100 for update;
  是一个范围条件的检索，InnoDB不仅会对符合条件的