数据库操作进阶

数据库操作进阶

一、子查询

1.1嵌套查询

select语句是SQL的查询。SQL中允许创建子查询，即嵌套在其他查询中的查询。

子查询即在一个sql钟，它的where条件来源于另一个sql。或者说一个sql语句的结果作为另一个sql语句的条件

1.2计算字段

二、表联结

2.1外键

在一个表中，定义一个字段。这个字段中存储的数据是另外一张表中的主键

就是在一个表中的字段，代表着这个数据属于谁

外键可以通过物理外键和逻辑外键来实现

物理外键：创建表时，指定一个字段为外键并强关联到表中某个字段

需要在定义字段时通过sql语句来实现。

逻辑外键(常用)：

没有强关联关系，需要通过程序逻辑实现

一对一

一个表中的数据，对应着另外一张表中的一个数据，只能有一个

一对多

一个表中的一个数据，对应着另外一张表中的多条数据

多对多

一个表中的多个数据，对应着另外一张表中的多条数据

2.2 where和join的表联结用法

表联结就是一种查询的机制，用来在一个select语句中关联表进行查询，成为联结。

使用where进行联结

示例：需要查询出所有商品的供应商信息和商品信息。

select  vend_name,prod_name,prod_frice `

 `from vendors,products `

``where vendors.vend_if = products.vend_id`

`order by vend_name;

如果没有where条件，那么第一行中的每一行数据会和第二个表中的每一条数据进行匹配，不管逻辑上是否可以进行匹配

使用join进行联结

同样使用上述示例，使用join联结可以表示为：

select  vend_name,prod_name,prod_frice` 

`from vendors`

`(inner)(可删除) join products on vendors.vend_id = produact.vend_id;

在on的后面定义联结的条件

联结多个表进行查询

在where语句中用and联结多个条件进行查询

在join语句中，在上一个join条件语句后接着插入其他要插入的表

示例如下：

select prod_name,vend_name,prod_price,quantity

from orderitems

inner join products on orderitems.prod_id = products.prod_id

inner join vendors on products.vend_id = vendors.vend_id;

2.3 自联结

当前表与自己做联结（join）

示例：当发现某商品存在问题，要知道该供应商生产的其他商品是否也存在问题时，要通过查询找到出现问题的商品对应的供应商，同时查询该供应商生产的其他商品。使用join进行自联结查询时，需要借助as。

select p1.prod_id,p2.prod_name`

`from products as p1`

`join products as p2 on p1.vend_id =  p2_id`

`where p2.prod_id = 'xxx';

改成where语句

select p1.prod_id,p2.prod_name`

`from products as p1,products as p2`

`where p1.vend_id = p2.vend_id and p2.prod_id = 'xxx';`

子查询（嵌套查询）是目前可明确知道查询效率最低的方法，最好能够通过表联结的方式进行替换。

2.4 外部联结

内部联结只能对两个表中相关联的数据进行查询

外部联结 （left join , right join）

left join :以left join 左侧的表为基准，去关联右侧的表进行联结，如果有未关联的数据，那么结果为Null.

right join :以right join 右侧的表为基准，去关联左侧的表进行联结，如果有未关联的数据，那么结果为Null.

使用场景：例如查询统计每个用户下的订单数量时，用内联结去统计则会统计不到未下订单的用户数据，此时可采用外部联结进行查询。

注意事项：

保证使用正确的联结条件，否则将返回不正确的数据。

应该总是提供联结条件，否则会得出笛卡尔积。

在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的，一半也很有用，但应该一起测试它们前，分别测试每个联结。这将使故障排除更为简单。

2.5 组合查询UNION

Mysql也允许执行多个查询（多条select语句），并将结果作为单个查询结果返回。这些组合查询通常称为并（UNION）或复合查询（compuond query）.

UNION规则

UNION必须由两条或以上的select语句组成，语句之间用UNION分隔。

UNION中的每个查询必须包含相同的列、表达式或聚集函数。各个列不需要以相同的次序列出。

类数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如不同的数值类型或不同的日期类型）

UNION查询结果会自动去除重复的行，换句话说它的行为和直接在单挑select语句中使用多个where子句条件是一样的。若想返回所有行可使用UNION ALL

对于组合查询结果的排序

在用UNION组合查询时，只能使用一条order by子句，它必须出现在最后一条select语句之后

三、SQL_mode(了解)

sql_mode是mysql数据中的一个环境变量

定义了mysql应该支持的sql语法，数据校验等

如何查看数据库中的sql_mode

` select @@sql_mode`

ONLY_FULL_GROUP_BY针对group by d的聚合操作，select中的列，没有在group by中出现，将被判定为不合法

临时修改(服务器重启后失效)

set @@sql_mode ='...';

修改sql配置文件（重启后生效） my.cnf

在my.cnf[mysql]下面去配置

[mysqld]
xxx = xxx
sql_mode = '';

四、常用语法补充

4.1 CASE WHEN的使用方法

case when 常用于一些较为复杂的运算和列的添加。例如计算及格率，优良率等。

格式如下：

CASE WHEN+判断条件+THEN 1 ELSE 0 END

以计算及格率为例

select sc.cid,c.cname
sum(CASE WHEN sc.score >= 60 THEN 1 ELSE 0 END)/count(sc.cid) as '及格率',
from sc joi  course as c on sc.cid = c.cid
group by sc.cid,c.cname;

同样我们也可以用IF()函数来表示，格式如下

sum(IF(sc.score>=60),1,0)/count(sc.cid) as '及格率'

4.2 concat的使用方法

concat常用于字符串的拼接，以计算及格率为例，我们表示及格率时常以百分数的形式表示，可以通过concat语句来实现。

concat(((sum(CASE WHEN sc.score >= 60 THEN 1 ELSE 0 END)/count(sc.cid))*100),'%') as '及格率'

或者表示1+1=2的内容

select concat(1,'+',1,'=',2);

4.3update中的SQL查询

update语句常用的格式为：

update xxx set value = ? where ...

update后面是可以进行任何查询语句的，作用等同于from

update更新表，不能再set和where中用于子查询

update也可以对多个表进行更新

示例

update mst_b as up,(select mst_a.key,mst_a.value from mst_a join mst_b on mst_a.key = mst_b.key) as b
set up.value = b.value where up.key = b.key;

五、MySQL事务

事务（Transaction）是由一系列对系统中数据进行访问与更新的操作锁组成的一个程序执行逻辑单元

5.1事务的ACID特性

Atomicity原子性

事务的原子性是指事务必须是一个原子的操作序列单元。事务包含的各项操作在一次执行过程中，只允许出现两种状态之一。

要么全部执行成功，要么全部执行失败。

事务开始后的所有操作，要么全部做完，要么全部都不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体。

Consistency一致性

事务的一致性是指事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后。数据库必须处于一致性状态

Isolation隔离性

在并发环境中，并发的事务是相互隔离的。不同的事务并发操作相同的数据时，每个事务都有各自完整的数据空间。

一个事务内部的操作及使用的数据对其他并发事务是隔离的，并发执行的各个事务是不能相互干扰的

Duration持久性

事务一旦提交，数据库中的数据必须被永久的保存下来。即使服务器系统崩溃或服务器宕机等故障。只要数据库重新启动，那么一定能够将其恢复到事务成功结束后的状态。

5.2事务的语法

1） start transaction/begin;

2） commit; 使得当前修改确认

3） rollback; 使得当前的修改被放弃

5.3事务的并发问题

脏读：读取到了没有提交的数据，事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。

不可重复读：同一条命令返回不同的结果集（更新）。事务A多次读取同一数据，事务B在事务A多次读取的过程中，对数据做了更新并提交，导致事务A多次读取同一数据时，结果不一致。

幻读：重复查询的过程中，数据就发生了量的变化

5.4隔离级别（由低到高）

READ_UNCOMMITTED(读未提交)

READ_COMMITTED(读已提交)

REPEATABLE_RREAD(可重复读)

SERIALIZABLE(顺序读)

隔离级别高同时也导致并发性差，安全性就越来越高

一般数据默认及时是读已提交或可重复读

查看隔离级别v

select @@tx_isolation;

设置隔离级别

set session transaction isolation level read uncommitted;

注意事项

MySQL中只有innodb引擎支持事务操作

六、MySQL的特性-扩展

6.1存储过程

存储过程简单来说就是为了以后的使用而保存的一条或多条MySQL语句的集合。

存储过程是一组为了完成特定功能的SQL语句集。经过编译后存储在数据库中，在需要时可以直接调用

存储过程的优点

可以把一些复杂的sql进行封装，简化复杂的操作

保证了数据的完整性，防止错误

简单的变动秩序更改存储过程的代码即可

提高性能，因为使用存储过程比单独的SQL语句更快（预先编译）

缺点

存储过程的编写比SQL语句更复杂

一般没有创建存储过程的权限，智能调用

创建存储过程的语法

\d // (修改MySQL默认的语句结尾符，修改为//)

create procedure p()创建语句

begin和end用来限定存储过程体

执行存储

call p();

查看存储过程

show create procedure p \G

删除存储过程

drop procedure p

6.2触发器

触发器是MySQL相应写操作而自动执行的一条或一组定义在begin和end之间的MySQL语句。或者可理解为：提前定义好一个或一组操作，在指定SQL操作前或后来触发指定的SQL自动执行

触发器的语法

create TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt

#trigger_name 触发器名称

#trigger_time触发事件，可取值before或after

#trigger_event触发事件，可取值，insert,up date或delete

#tbl_name指定在哪个表

#trigger_stmt触发处理SQL语句

查看所有的触发器

show triggers\G

6.3视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

视图仅仅是用来查看存储在别处的数据的一种设施或方法。

视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。

在2或更改这些表中的数据时，视图将返回改变过的数据。

因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。

如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降得很厉害。

创建视图:
create view v_users as select id,name,age from users where age >= 25 and age
<= 35;
-- Query OK, 0 rows affected (0.00 sec)
view视图的帮助信息:
mysql> ? view
ALTER VIEW
CREATE VIEW
DROP VIEW
查看当前库中所有的视图
show tables; --可以查看到所有的表和视图
show table status where comment='view'; --只查看当前库中的所有视图
删除视图v_t1:
mysql> drop view v_t1;