MySql 自学总结---基础点

1、sql中字符串需加上 ' ' 或 " "，建议单引号（单引号所有的数据库都识别，而双引号只有mysql数据库才能识别）

2、条件查询时需要用到where语句时，where必须放到from语句表后面（先from再where过滤）

3、sql语句中需要用到null时，直接=null是错误的，需要改成 is null（is not null）

4、and的优先级高于or（保险起见，尽量用（）括上）

5、表示包含关系的时候完全可以用and ，or 但是采用in（数据） （not in （））会更建简洁

6、like 模糊查询时 %表示通用符，_表示占位符

7、order by 默认升序（asc） 降序（desc），在order by后跟上排序字段，排序字段可以是多个，用逗号隔开；如果存在where子句，那么order by 必须放到where后面

order by sal desc ， job desc

8、ifnull（字段名，替换值）：空值处理函数，在数据库中有null参与的运算结果一定是null，为了防止计算结果出现null，建议先使用ifnull函数预先处理

<select id="selectCartProductCount">
    select IFNULL(sum(quantity), 0) as count from mmall_cart where user_Id = #{userId}
</select>

9、case...when...then...else...end  用法：匹配manger则工资上涨10%，salesman则上涨50%，其他不变（形成新的一行数据）

case job

       when 'manger'  then  sal*1.1

       when 'salesman'  then  sal*1.5

       else   sal

end

10、str_to_date（'日期字符串','日期格式'）将字符串转换为Date日期类型---通常在插入操作中使用

       str_to_date('02-20-1981','%m-%d-%Y')   //输出1981-02-20          

 //--%Y表示四位的年份  %y表示两位的年份  %m表示月（01,02...）%c（1,2...） %H代表24小时制  %h代表12小时制

//在实际开发中一般将日期报存为字符串，在存入数据库的时候采用转换函数转换为Date类型

11、data_format（日期类型数据，'日期格式'）将日期转换为特定格式字符串 -- 主要用于查询中

12、分组函数（sum，avg，max，min，count）自动忽略null值，无需where去除；count不会统计值为null的记录

13、去重：distinct 字段A：去除与字段A相同的记录，distinct 字段A，字段B：去除与字段A和字段B同时相同的记录

14、有group by的语句中，selec语句后面只能跟分组函数 和 参与分组的字段；order by必须放在 group by后面

15、where和having都是为了完成数据过滤，他们后面都是添加条件，但是where是在分组前过滤，having是在分组后过滤

         select job，avg（sal）from emp group by job having avg(sal) > 2000;

         select job，avg（sal）from emp where avg(sal) > 2000 group by job ;

一个完成的sql   以下关键字顺序不能变！！！

select xxx

from xxx

where xxx

group by xxx

having xxx

order by xxx

-------------------------------------------------------------------------------------------------------------------------------

distinct关键字应用于所有列
select distinct xx,xx from xx      //去除重复记录

select xx from xx limit a,b;       //a为起始下标(从0开始算起),b为长度

// ASC升序(默认)、DESC降序 
// order by 必须位于from后面,limit必须位于order by后面
select pro_id,pro_price,pro_name
from products
order by pro_price DESC, pro_name(ASC);
limit 1,2;    //默认起始下标是0
              
in 比 or 执行的更快一点,in的最大优点是可以包含其他select语句
and的优先级高于or

\\ 转义符
匹配正则表达式
select prod_name from products where prod_name REGEXP '^[0-9\\.]'
只在.或任意数字为串中第一个字符时才匹配它们

select prod_name from products where prod_name REGEXP '\\([0-9] sticks?\\)'
\\( 匹配(  [0-9]匹配任意数字 sticks? 匹配stick或sticks,其中?匹配前面的字符0次或1次 \\)匹配 )

^ 双重用途：[^123]否定集合、^[0-9] 匹配开始

select Concat(RTrim(vend_name),'(', RTrim(vend_country), ')') AS vend_title
from Vendors 
order by vend_name;

Concat(a,b,c...) 拼接字符串
RTrim() 去除右边空格, LTrim() 去除左边空格 Trim() 去除两边空格 AS xx 起别名（alias）

计算字段，创建计算字段
select prod_id, quantity, item_price, quantity*item_price AS expanded_price 
from orderitmes
where order_num = 20005
//  quantity*item_price 为计算字段，expanded_price 为别名，mysql支持 + - * /


数据处理函数

文本处理函数：Upper() Lower() 
Locate()      //找出串的一个子串 
Length()      //返回串的长度 
SunString()   //返回子串的字符
...

日期和时间处理函数：
DateDiff()    //计算两个日期之差
Date_Format() //返回一个格式化的日期或者时间串
DateOfWeek()  //对于一个日期返回对应的星期几
AddDate()     //增加一个日期(天、周等)    
AddTime()     //增加一个时间(时、分等)
Date()        //只提取日期部分，一般数据库日期类型为datetime yyyy-MM-dd HH:mm:ss,更方便匹配
Year() Month() //返回年 返回月，如果需要取一个月的数据就很方便 where Year()=xx and Month()=xx
...

数值处理函数：
Abs()        //返回数值的绝对值
Sqrt()       //返回数值的平方根
Rand()       //返回随机数
Mod()        //返回除操作的余数
...

聚集函数：
AVG()        //确定特定数值列的平均值，忽略值为null的行
COUNT()      //若指定列名,则null值会被忽略,若为*,则不忽略null
MAX()        //返回指定列最大值，忽略null行
MIN()        //同上，取最小值
SUM()        //返回指定列值得和(总计)，忽略null行，传入的列可为计算字段 
               SUM(item_price*quantity)
DISTINCT     //必须指定列名

数据分组：
创建分组：select vend_id, COUNT(*) AS num_prod from products group by vend_id
         按照vend_id进行分组，并对每个vend_id进行计数，而不是对整张表计数

过滤分组：group by 只能使用选择列，或者表达式列，而且必须使用每个选择列表达式
select cust_id, COUNT(*) AS orders from orders 
GROUP BY cust_id 
HAVING COUNT(*) > 2

where是在分组前进行过滤，而having是在分组后进行过滤，两者可以同时存在
select vend_id, count(*) as num_prod from products
where prod_price >= 10
group by vend_id
having count(*) >=2

排序：order by 可以使用任意列 区别于group by

关键词顺序：
select 
from 
where 
group by 
having 
order by 
limit


子查询：
过滤：select ... where num in (select....)
作为计算字段：select name, state, (select...) as order ...

联结表：join
select vend_name, prod_name, prod_price from vendors, products
where vendors.vend_id = products.vend_id 
order by vend_name, prod_name;

内联结：select vend_name, prod_name, prod_price from vendors 
inner join products on vendors.vend_id = products.vend_id;  
上面两句含义相同，但推荐使用内联结的方式

多张表：
SELECT 字段号
FROM 表1 INNER JOIN 表2 ON 表1.字段号 = 表2.字段号

INNER JOIN 表3 ON 表1.字段号 = 表3.字段号

INNER JOIN 表4 ON Member.字段号 = 表4.字段号
...
INNER JOIN 表X ON Member.字段号 = 表X.字段号

自联结：from 后面可以使用两张相同的表，为避免歧义，要使用别名
select p1.prod_id, p1,prod_name 
from products as p1, products as p2 
where p1.vend_id = p2.vend_id and p2.vend_id='DTNTR';

左联结和右联结：A left/right outer join B on...

union：放在每条select语句间，自动去除重复项
union all：同上，不会自动去重
使用union组合查询时，只能使用一条order by且必须出现在最后一条select语句之后，用来排序所有select返回的结果集

插入多条记录
insert xx(id,name...) values (1,a...),(2,b...),(3,c...)...

delete from xx (where ...)  删除表记录
truncate table  删除全表记录建议用这句，速度更快，底层实际是删除整张表，然后重新建表

视图：为虚拟的表，不包含表中任何列或者数据，它包含的是一个SQL查询
为什使用视图：
重用SQL；简化复杂的SQL，方便重用而不需要知道基本查询细节；使用表的组成部分，而不是整张表；保护数据，可以给用户授予表的特定部分的访问权限，而不是整张表；更改数据格式和表示，视图可以返回和底层表的表示和格式不同的数据。
创建视图：create view name as select...


存储过程：一条或多条SQL语句的集合，简单，安全，高性能
执行存储过程：call productprice(@low,@high,@averge)
执行名为producetprice的存储过程，他计算并返回商品的最低，最高和平均价格

创建存储过程：
create procedure name()
begin
    select..
end;

游标：


触发器：mysql响应以下任意语句而自动执行的一条SQL语句
delete、insert、update
其他SQL语句不支持触发器

创建触发器需要给出四条信息
唯一的触发器名、
触发器关联的表、
触发器应该响应的活动（delete、update、insert）、
触发器何时执行(处理之前还是之后)
每个表最多支持6个触发器，

创建触发器：create trigger
删除触发器：drop trigger 
create trigger newproduct after insert on products
for each row select 'product added'
创建名为newproduct的触发器，在每一个insert操作之后触发，对每个插入行执行，每个插入操作后显示文本product add

INSERT触发器：
在insert触发器中可以引用一个NEW得虚拟表，访问被插入的行
create trigger neworder after insert on orders 
for each row select NEW.order_num
此段代码含义为：创建了一个名为neworder的触发器，它按照after insert on orders执行，在插入一个新订单到orders表时，mysql生成一个新订单号并保存到order_num中。触发器从NEW.order_num取得这个值并返回。

DELETE触发器
在delete触发其中可以引用OLD的虚拟表，访问被删除的行


管理事务
事务：一组SQL语句
回退：撤销指定SQL语句的过程
提交：将未存储的SQL语句结果写入数据库表中
保存点：指事务处理中设置的临时占位符


analyze table orders 检查表键是否正确
check table orders 检查表

mysql锁机制-Lock

锁的类型：共享锁(读锁)、排他锁(写锁)

锁的粒度：表锁、页锁、行锁

表锁：

表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。

当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并发度大打折扣。
使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。

# 获取表锁
LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...
 
lock_type:
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE
 
# 释放表锁
UNLOCK TABLES

MyISAM在执行查询前，会自动执行表的加锁、解锁操作，一般情况下不需要用户手动加、解锁，但是有的时候也需要显示加锁。比如：检索某一个时刻t1，t2表中数据数量。

LOCK TABLE t1 read, t2 read;
select count(t1.id1) as 'sum' from t1;
select count(t2.id1) as 'sum' from t2;
UNLOCK TABLES;

页锁：

页级锁定是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。
在数据库实现资源锁定的过程中，随着锁定资源颗粒度的减小，锁定相同数据量的数据所需要消耗的内存数量是越来越多的，实现算法也会越来越复杂。不过，随着锁定资源颗粒度的减小，应用程序的访问请求遇到锁等待的可能性也会随之降低，系统整体并发度也随之提升。
使用页级锁定的主要是BerkeleyDB存储引擎。

行锁：

行级锁定最大的特点就是锁定对象的粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。
虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。

使用行级锁定的主要是InnoDB存储引擎。

总结：

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

从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

 

共享锁指的就是对于多个不同的事务，对同一个资源共享同一个锁

在执行语句后面加上lock in share mode就代表对某些资源加上共享锁了。

 

排它锁与共享锁相对应，就是指对于多个不同的事务，对同一个资源只能有一把锁。

与共享锁类型，在需要执行的语句后面加上for update就可以了