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';