MySQL学习比较（函数、约束、多表查询）

1、函数

字符串函数

全部转为小写。

concat(s1, s2, s3 ...);

lower(str);

全部转为大写。

upper(str);

左填充n个pad字符。

lpad(str, n, pad);

右填充n个pad字符。

rpad(str, n, pad);

剔除字符串头部和尾部的空格

trim(str);

返回从start位置起的len长度字符串

substring(str, start, len);

数值函数

向上取整

ceil(num);

向下取整

floor(num);

求模运算

mod(num1, num2);

随机生成0到1之间的数。

rand();

四舍五入保留x位小数。

round(num , X);

日期函数

打印当前日期，格式是yyyy-mm-dd

curdate();

打印当前时间，格式是hh:mm:ss

curtime();

打印当前日期和时间，格式：yyyy-mm-dd hh:mm:ss

now();

提取出日期中的年份。

year(date);

提取出日期中的月份。

month(date);

提取出日期中的天数。

day(date);

时间增加函数，在x时间上增加y年或y月或y天。

date_add(x, interval y year/month/day);

求取两个日期之间差异的天数。

datediff(date1, date2);

流程函数

如果value的值位true，返回t，否则返回f。

if(value, t, f);

如果value1不为空则返回value1，反之返回value2.

ifnull(value1, value2);

如果x是y则返回z，反之返回k。

case x when y then z else k end;

如果是x，则返回y，如果是z，则返回k，如果不是前两者，则返回q。

case when x then y when z then k else q end;

2、约束

约束在创建新字段时候常被用到。主要的约束有以下这些，且多个约束可以同时使用，结合使用时，中间以拓号隔开即可。

• 非空约束(not null)：限制该字段数据不能NULL。
• 唯一约束(unique)：保证该字段的所有数据都是唯一的。
• 主键约束(primary key)：要求这一行数据非空且唯一。
• 默认约束(default)：保存数据时，如果未指定该字段的值，则采用默认值。
• 检查约束(check)：保证该字段满足一个条件。
• 外键约束(foreign)：让多个表连接起来，保证数据的完整性。

比如我想在一张teacher_table的表中加入一个名为id的字段，并且对其声明主键约束，以及让其数据自动增加。

alter table teacher_table add id tinyint unsigned primary key auto_increment;

外键的添加、删除、更新

添加外键分为两种情况。
第一种情况，在创建表的同时就添加外键约束。语法如下。

create table <table_name>(
	<field_name1 type>,
	<field_name2 type>,
	...
	constraint <foreign_key_name> foreign key (<fieldx_name>) references <main_table(field_name)> 
)

比如我要在一个叫test的table上，对son_id添加外键约束，使其和teacher_table上的id对得上。

create table test(
    son_id int,
    name char(5),
    constraint id_link foreign key (son_id) references teacher_table(id)
);

第二种情况则是往表里添加新字段同时，添加外键约束。语法如下。

alter table <table_name> add constraint <foreign_key_name> foreign key (<fieldx_name>) references <main_table(field_name)> ;

删除外键的语法如下：

alter table <table_name> drop foreign key <foreign_key_name>;

外键在添加之后，如果用户删除或更新对应的字段的值，系统会自动检测该值是否存在对应的表的字段中存在，如果存在，则会执行默认的no action行为，即不允许删除或更新。
当然，我们也可以修改这种行为，比如让用户修改或删除外键所指的字段的值的时候执行cascade行为或set null行为。
cascade行为是指，修改字段中的值的时候，外键所对应的字段的值同步修改。
set null行为是指，修改字段中的值的时候，外键所对应的字段的值设为NULL。
我们可以在添加外键的时候就设置好，比如让其在修改的时候执行cascade行为，在删除的时候执行delete行为。

alter table <table_name> drop foreign key <foreign_key_name> on update cascade on delete set null;

3、多表查询

3.1、内查询

内查询仅查询多张表之间有关联的行，即满足判断条件的行。
隐式内查询：语法简单，但效率比较低。

select * from table1, table2 where <condition>;

显示内查询：语法较为复杂，但效率高。

select * from table1 join table2 on <condition>;

3.2、外查询

外查询分为左外查询和右外查询，左外查询的时候，会把左表所有行都展示出现，而对于右边则仅仅展示满足判断条件的行。右外查询也一样，只不过把左右表的顺序调换而已。

select * from table1 left join table2 on <condition>;
select * from table1 right join table2 on <condition>;

3.3、自查询

自查询是把一张表视为两张表，通过取两个别名的方式来区别，而查询方式适用内查询和外查询任意一种都可以。下面举一个例子说明一下，假设要对tablea进行自查询。

select * from tablea as a, tablea as b where <condition>;

当然多表查询并不局限于两张表，还可以拓展到N张表。

select * from table1, table2, table3, ... where ...;

select * from table1 join table2 on ... join table3 on ... join table4 on ...;

4、事务

MySQL中，每一条语句都可以视为一个事务，并且默认该事务自动提交，即执行语句之后，操作立马生效。但许多时候，我们希望执行完多条语句之后，再将所有操作一次性生效。这个时候就需要用以下语句来控制语句什么时候生效了。

start transaction;					--开启事务
/*
此处执行N条语句
*/
commit;							--提交事务，如果发生错误还可以用rollback回滚事务

而当同时进行多个事务的时候，还会出现以下三种常见问题。

• 脏读：脏读发生在一个事务读取了另一个事务未提交的数据。因为在read uncommitted的情况下，数据并不是等到事务提交之后再更改。这其实和我上面提到的“将所有操作一次性生效”的说法似乎是有点冲突的，但事实上，所谓一次性生效需要配合rollback和commit来保证一个事务的完整性。
• 不可以重复读：不可重复读是指在同一个事务中，多次读取同一数据集合时，由于其他事务的修改提交，导致两次读取的数据不一致。
• 幻读：幻读类似于不可重复读，但它关注的是一个事务读取了一系列符合某个搜索条件的记录，然后另一个事务插入了一些新的符合那个搜索条件的记录。当第一个事务重新执行相同的查询时，它将会检索到一些之前未见过的“幻影”记录。

针对以上三种现象，可以通过修改隔离级别来避免。

• read uncommitted（未提交读）：最低级别，允许脏读，不可重复读和幻读。
• read committed（提交读）：允许不可重复读和幻读，但避免了脏读。
• repeatable read（可重复读）：保证在同一个事务中多次读取同样的数据结果是一致的，避免了不可重复读，但在MySQL中通常也避免了幻读。
• serializable（可序列化）：最高级别，完全避免脏读，不可重复读和幻读。

隔离级别从上到下越来越高，但执行效率也越来越低。
查询隔离级别的语句

select @@transaction_isolation;

设置隔离级别的语句。

set session transaction_isolation='serializable';