数据库进阶操作(全)
进阶一：多字段排序
排序显示，可以按单字段排序，也可以按多字段排序，多字段之间用英文逗号分隔。
排序的处理逻辑如下：
a.先按order by 后的第一个排序条件进行排序。
b.如果排序后有相同的值，则再按第二个排序条件进行排序。
c.如果排序后没有相同的值，则忽略第二个排序条件。
例：
#在学生表中，将所有信息按年龄从大到小排序,当年龄相同时，则按学号从小到大排序
select *
from xsb
order by nl desc,xh asc;
#学生表，按班级从小到大排序，如果是同班同学，则按年龄从大到小排序
select *
from xsb
order by bj asc,nl desc;
进阶二：截断表 —— truncate
用于删除表中的所有记录，但不删除表结构。
语法：truncate 表名;
truncate 和delete 有何区别？主要有两点：
a.truncate的速度比delete快很多。（测试工作中，有时需要将某种数据量非常大的表清空，推荐用truncate）
（*注释：delete是逐行删除的，比较慢，truncate相当于drop掉整张表再重建这张表的表结构，所以执行速度快很多）
b.truncate的话自增字段会被置为初始值，从1开始重新生成。
c.truncate不支持回滚，delete支持回滚。（*）
进阶三：去重 —— distinct
用来过滤多余的重复记录，重复的只保留一条。
语法：select distinct 字段名 from 表名;
例：
#查询所有学生的籍贯
select distinct jg from xsb;
进阶四：分页查询 —— limit
用于指定返回的记录数。
语法：。。。。。。limit [位置偏移量,] 记录数
注释：位置偏移量默认从0开始。即，第一个记录的位置偏移量是0。记录数，返回的最大条目数。（从哪儿开始，共返回几条）
例：
#显示前5条学生记录
select * from xsb limit 0,5;
select * from xsb limit 5; #与上面这条sql语句等价
#显示第5~8条学生记录
select * from xsb limit 4,4;
#显示第7~16条学生记录
select * from xsb limit 6,10;
进阶五：多表查询
当查询的数据来自多张表时，可使用 子查询 或 关联查询 的方式来进行多表查询。
1.子查询
子查询：嵌套查询。查询里面嵌套了小的查询语句。
例 ：
#查询选修了"java语言"课的全部学生的总成绩
分析：来自于多张表，cjb，kcb
#第一步：在kcb中可以根据“java语言”条件来找到对应的kch；
select kch
from kcb
where kcm='java语言';
#第二步：再根据这个kch去cjb中可查询到对应的学生成绩，
SELECT cj
from cjb
where kch=(select kch from kcb where kcm='java语言');
注意：别把嵌套的子查询的分号拷贝进去，否则会被识别出sql语句结束，会报错。
#第三步：再使用sum（）求和。
SELECT sum(cj)
from cjb
where kch=(select kch from kcb where kcm='java语言');
2.关联查询
关联查询：将多张表联接成一张大的表，然后从这张大表里去查询特定的数据。
大表中生成出来记录数（行数）：行相乘。A表的行数*B表的行数。-----A表（2行）B表（3行）共生成6行（2*3）
大表中生成出来记录数（列数）：列相加。A表的列数+B表的列数。-----A表（3列）B表（2列）共生成5列（2+3）
例 ：
#查询学生成绩，要求显示 课程号 课程名 学号 成绩
分析：课程名在成绩表中是查不到的，需要在kch中查看，因此这个查询数据来自两张表 kcb cjb
第一步：将kcb和cjb进行笛卡尔积运算，合并成一张大表
select * from kcb,cjb；
（出来的查询结果中，有很多没有意义的垃圾数据，例如，把数据库的课程名和java语言的成绩关联在一起了，这类需要做过滤）
加上where子句和过滤条件。
select *
from kcb,cjb
where kcb.kch=cjb.kch;
（查询出来的结果中，有多余的列，因此我们按查询要求将列名替掉*）
select kch,kcm,xh,cj
from kcb,cjb
where kcb.kch=cjb.kch;
（执行后发现mysql提示kch这个字段有重名。因此，我们要指定一下这个字段来自哪张表，kcb.kch或者cjb.kch）
select kcb.kch,kcm,xh,cj
from kcb,cjb
where kcb.kch=cjb.kch;
3.多张表关联查询
例：
#查询籍贯是北京的同学的各科成绩，要求显示的字段有姓名、籍贯、课程号、课程名、成绩。
select xm,jg,cjb.kch,kcm,cj
from xsb,cjb,kcb
where xsb.xh=cjb.xh and kcb.kch=cjb.kch and jg='北京';
4.别名
当表名较长时，通常我们习惯为表取别名，以精简sql语句的长度。
别名加在from子句 表名后即可。可以用as 也可以不用。
例：
select kcb.kch,kcm,xh,cj
from kcb,cjb
where kcb.kch=cjb.kch;
使用别名后：
select kch,kcm,xh,cj
from kcb a,cjb b
where a.kch=b.kch;
5.内联接、外联接
l 内联接
只返回满足关联条件的结果集。（最典型的联接运算，通常会用到 = <>之类的运算符 ）inner join on
l 外联接
左外联接（left join...on）：除了返回满足关联条件的结果集，还会把左边的那张表完整的展示出来，右边那张表里的不满足条件的字段位置则补空值（null）
右外联接（right join...on）：。。。。。。。。。。。。。。。。。 右边。。。。。。。。。。。。左边。。。。。。。。。。。。。。。。补空值（null）
全外联接：。。。。。。。。。。。。。。。。。。。。。。。。。两边。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。补空值（null）
注意：mysql不支持全外联接full join。但是可以通过集合union来实现。（将左外和右外中间加个union连起来）
例：
#查询所有学生的课程和成绩，要求课程号、学号、姓名、成绩。（没有成绩的学生信息也要显示出来）
#分析：用内联接的方式无法满足要求。可通过左外联接来查询“所有学生”的信息（不管有没有成绩）。
#左外联接来实现：
SELECT xsb.xh,xm,kch,cj
from xsb left join cjb
on xsb.xh=cjb.xh;
#右外联接来实现：
SELECT xsb.xh,xm,kch,cj
from cjb right join xsb
on xsb.xh=cjb.xh;
#全外联接：全外联接通常使用full join。但是mysql不支持full join。别的关系型数据库（例如oracle）就是支持的。
SELECT xsb.xh,xm,kch,cj
from xsb left join cjb
on xsb.xh=cjb.xh
union
SELECT xsb.xh,xm,kch,cj
from xsb right join cjb
on xsb.xh=cjb.xh;
进阶六：事务
1、概念
事务：指的是一组DML操作（insert delete update），只允许它们要么都成功，要么都失败。
DML：数据库操作语言：insert delete update
DQL：数据库查询语言：select
DDL：数据库定义语言：create drop alter truncate
DCL：数据库控制语言（一般DBA用的）：
例：银行内有2个账户，A账户转账给B账户1000。本质是对两个账户的余额进行更新操作，如下：
update ×× set ye=ye-1000 where countname=’A’;
update ×× set ye=ye+1000 where countname=’B’;
这样的一组操作，如果只有一句成功，另一句失败，则会造成数据库的数据完整性被破坏，从业务角度来看，也会导致银行和用户的纠纷。
因此，这样的一组DML语句，通常会组合成事务的形式。
2、特性
1) 原子性：事务中的所有操作（一组SQL语句）被看做一个整体，不可分割，要么同时操作成功，要么同时操作失败。
2) 隔离性：一个事务在其操作期间，别的事务不可对其进行干扰。
3) 永久性：事务处理结束后，对数据的修改是永久性的。
4) 一致性：在事务开始之前和开始之后，数据库的完整性没有被破坏，保有了一致性和完整性。
3、“事务”的sql代码实现：
mysql里的事务sql代码实现——
start transaction；#开启事务
（sql语句）;
（sql语句）;
commit; #提交事务
rollback; #事务回滚 （没有提交的事务允许回滚）
例：
数据准备——
#创建账户存储表
create table account(
id int auto_increment primary key,
name varchar(20) not null,
money decimal
);
desc account;
insert into account(name,money) values('a',5000),('b',8000);
select * from account；
注意：MySQL的默认设置，事务是自动提交的，即执行了sql语句之后马上自动commit，如果你不希望它自动提交，则需要使用start transaction；来显示地开启事务，或者 执行 set autocommit=0来禁止自动提交。
默认自动提交，如果不希望自动提交，则使用start transaction；
start transaction;
update account set money=money-100 where name='a';
update account set money=money+100 where name='b';
#执行完两句update后select * from account; 可看到账户余额已经变化了
rollback; #没有运行commit提交前，可以回滚，回滚后，账户余额会恢复回去
commit; #commit以后，就无法rollback了，此后再执行rollback，就没有作用了，账户余额不会恢复。
