mysql数据库基础知识整理

1.mysql的sql语句关键字

1）limit、offset

select * from tablename limit 0,10

limit startindex,querysize。从startindex（索引是从0开始的）开始取querysize个数据。

select * from lotteryticket limit 10 offset 0

这条sql语句和上条语句效果一样。startindex和querysize换了换位置。

limit和offset的区别：除了写法不一样，似乎没什么区别^-^。

limit性能优化：当offset（偏移量，就是上边两条sql的0这个数字）越大的时候，性能越差。想办法减少offset的大小。

2）delete、truncate

有一个需求，删除表中的所有数据，以下两条sql都可以：

truncate table tablename;
delete from tablename;

区别：

1）truncate只能删除所有数据，delete 后边可以跟条件指定删除哪些数据。

2）truncate删除不会记录日志，而delete会记录日志（记录每一条删除的数据）。所以truncate效率比delete高很多。

3）truncate删除，如果有自增字段，重新添加数据时，自增默认从1开始，而delete按照原来删除的数据的最大值+1来自增。

3）union、union all

将两个查询结果集合并为一个，sql如下：

select a,b from tablenamea
union
select c,d from tablenameb

要求，select后边的字段个数必须一致，不然会报错。对应字段的类型可以不一样（b和d可以不一样，前提是d的字段值可以转化成b相同的类型，union过程中会自动转换）。

区别：

union 会对结果进行去重，union all不会对结果进行去重。所以union all的效率要比union高。

4）distinct

对结果进行去重的关键字，sql如下：

select distinct field01 from tablename

这样就会把field01中相同的值去除掉。

select distinct field01,field02 from tablename;

上边的sql，如果fields01和fields02都一样才会去重，如果有一个不一样数据就不会去除掉。

要点：distinct必须要放在所有字段的前边。

5）join

内连接：

select * from tablenamea
inner join tablenameb 
on tablenamea.id = tablenameb.id

上边sql，表示取tablenamea和tablenameb共有的部分数据。即tablenamea.id=1，如果tablenameb.id没有等于1的数据，那么tablenamea表中id=1的这条数据不显示在结果集。同样，tablenameb.id=2，如果tablenamea.id没有等于2的数据，那么tablenameb表中id=2的这条数据，也不显示在结果集中。

左连接：

select * from tablenamea
left join tablenameb
on tablenamea.id = tablenameb.id

以左表（tablenamea）为基准，根据关联条件（tablenamea.id = tablenameb.id）查找tablenameb中符合条件的数据。即tablenamea中的数据全部显示在结果集中，如果tablenameb中有符合关联条件的数据，则显示在结果集中，如果没有则显示为null。

右连接：

select * from tablenamea
right join tablenameb
on tablenamea.id = tablenameb.id

跟左连接相反，以右表（tablenameb）为基准，根据关联条件（tablenamea.id = tablenameb.id）查找tablenamea中符合条件的数据。即tablenameb中的数据全部显示在结果集中，如果tablenamea中有符合关联条件的数据，则显示在结果集中，如果没有则显示为null。

2.mysql的事务

（begin）/（start transaction）:开启事务。

（rollback）/（rollback work）：回滚。

（commit）/（commit work）：提交事务。

示例如下：

mysql> begin;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into testa(field01,field02) values(1,'a');
Query OK, 1 row affected (0.06 sec)

mysql> commit;
Query OK, 0 rows affected (0.09 sec)

mysql> select * from testa;
+---------+---------+
| field01 | field02 |
+---------+---------+
| 1       | a       |
+---------+---------+
1 row in set (0.14 sec)

mysql> begin;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into testa(field01,field02) values(2,'b');
Query OK, 1 row affected (0.06 sec)

mysql> rollback;
Query OK, 0 rows affected (0.09 sec)

mysql> select * from testa;
+---------+---------+
| field01 | field02 |
+---------+---------+
| 1       | a       |
+---------+---------+
1 row in set (0.14 sec)

设置事务的隔离级别：

四个隔离级别分别是：read uncommitted、read committed、repeatable read、serializable。

设置全局的隔离级别，代码如下：

set global transaction isolation level read commited;

设置session的隔离级别，代码如下：

set session transaction isolation level read commited;

当设置session的隔离级别后，关闭此窗口，重新打开，则此时session的隔离级别，重新为默认级别。 

设置global全局的隔离级别后，关闭窗口，重新打开，仍未之前设置的级别。

查看数据库的隔离级别：

select @@transaction_isolation;

默认显示的是session（当前对话）的事务隔离级别。

全局事务隔离级别查看sql如下：

select @@global.transaction_isolation;

3.mysql的存储过程

什么是存储过程：

完成特定功能的SQL语句集。

利用存储过程有什么优点：

1.减少网络开支，直接调用存储过程，不必一个sql就向数据库发一次查询。

2.可以使用控制语句，比sql功能强大。

3.对存储过程修改可以不用修改java代码。

存储过程的创建：

mysql> delimiter $$
mysql> create procedure myproc(out a int)
    ->     begin
    ->         select count(*) into a from tablename;
    ->     end;$$
Query OK, 0 rows affected (0.13 sec)
mysql> delimiter ;

delimiter $$:作用是把；（结束符）改为$$。存储过程写完之后要重新改回；。

参数中的out值得是输出参数，后边是变量名a，在后边是变量类型int。存储过程以begin开始end结尾。

输入参数为in，既是输出又是输入参数为inout。

存储过程的调用：

mysql> call myproc(@n);
Query OK, 1 row affected (0.03 sec)

mysql> select @n;
+------+
| @n   |
+------+
|    1 |
+------+
1 row in set (0.03 sec)

存储过程的删除和显示：

show procedure status where db='databasename';

展示已有的存储过程，databasename为要查找的数据库名称。

drop procedure databasename.procedurename;

删除已有存储过程，databasename为数据库名字，procedurename为存储过程名字。

存储过程逻辑控制语句的用法：

1）if 

create procedure myproc01(in a int,out b varchar(20))
begin
     if a=1 then set b='aa';
     elseif a=2 then set b='bb';
     else set b='other';
     end if;
end;

注意：elseif中间没有空格。

2）while

create procedure myproc02(in a int,out b varchar(20))
    declare i int default 0;
    declare result int default 0;
    while i<a do
        set i=i+1;
        set result=result+1;
    end while;
    set b=result;
end;

3）loop和leave

create procedure myproc03(in a int,out b varchar(20))
begin
    declare i int default 0;
    label01:loop
        set i=i+1;
        set b=i;
        if i>a then leave label01;
        end if;
    end loop;
end;

当i>a时，执行leave label01打断loop循环并退出loop循环，相当于java中的break。

4）iterate

create procedure myproc04(in a int,out b int,out c int)
begin
    declare i int default 0;
    declare result int default 0;
    label01:loop
        set i=i+1;
        if i=2 then iterate label01;
        end if;
        set result=result+1;
        if i>a then leave label01;
        end if;
    end loop;
    set b=i;
    set c=result;
end;

输入a=4,输出结果为b=5,c=4。当i=2的时候，执行iterate label01，重新回到label01开始执行，作用类似于java中的continue。

5）case when

create procedure myproc05(in a int,out b varchar(20))
    begin
    case a
        when 0 then set b='a';
        when 1 then set b='b';
        else set b='other';
    end case;
end;