数据库进阶操作(全)

进阶一：多字段排序

排序显示，可以按单字段排序，也可以按多字段排序，多字段之间用英文逗号分隔。

排序的处理逻辑如下：

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，就没有作用了，账户余额不会恢复。