新版博客SEO优化基本完成,新老博客内容正在整合中,保证每篇文章高质量。 SiteMap RSS Github
Mysql 高级操作
嘉美伯爵   2018年12月20日 09:45   数据库   MySQL   214  

连接

inner join(内连接)

  • 两个表都存在匹配时,返回行

  • 查询每个学生,每个科目的成绩
select students.sname, subjects.stitle, scores.score
from scores
inner join students on scores.stuid = students.id
inner join subjects on scores.subid = subjects.id;
### 如下
+-------+--------+-------+
| sname | stitle | score |
+-------+--------+-------+
| Gage  | 语文   | 90.00 |
+-------+--------+-------+

left join(左连接)

  • 返回左表中的所有数据,即使右表中没有匹配的数据

  • 查询每个学生的平均成绩
 select students.sname, avg(scores.score)
 from scores
 left join students on scores.stuid = students.id
group by students.sname;

right join (右连接)

  • 返回右表中的所有数据,即使左表中没有匹配的数据

full join (全连接)

  • 只要一个表存在匹配就返回

自关联

对于省市区表,我们可以采用一对多的形式建立三张表,但这样会带来不必要开销,分析后发现 省份无上级,我们建立三个字段,id为省份id, pid为市区id,这样只要让pid=id就可以使省市区关联起来

  • 定义数据表
create table areas(
id int primary key,
atitle varchar(20),
pid int,
foreign key(pid) references areas(id)
);
  • 查询山东省下的所以市区
select city.* from booktest_areas as city
inner join booktest_areas as province on city.num=province.id
where province.city='山东省';
  • 查询省市区
select city.*, county.* from booktest_areas as province
inner join booktest_areas as city on city.num = province.id
left join booktest_areas as county on county.num = city.id
where province.city='山东省';

内置函数

  • 对于复杂的一些查询,每次书写查询语句相当麻烦。我们可以定义视图
mysql> create view stuscore as
    -> select students.*, scores.score from scores
    -> inner join students  on students.id=scores.stuid;
### 查询
select * from stuscore;

事务

为保证业务的完整性,当一条语句出现错误,则此步骤全部回退

  • 原子性(Atomicity):事务中的全部操作在数据库中是不可分割的,要么全部完成,要么均不执行
  • 一致性(Consistency):几个并行执行的事务,其执行结果必须与按某一顺序串行执行的结果相一致
  • 隔离性(Isolation):事务的执行不受其他事务的干扰,事务执行的中间结果对其他事务必须是透明的
  • 持久性(Durability):对于任意已提交事务,系统必须保证该事务对数据库的改变不被丢失,即使数据库出现故障

提交

  • 默认隔离级别可重复读,若事务级别为读已提交,则终端二不commit的情况下可以读取到终端一的拆。
### 终端一
begin; 开启
insert into students (sname) value ("sss");
commit; 只有commit才表示执行成功
### 终端二
mysql> select * from students;
+----+-------+
| id | sname |
+----+-------+
|  1 | Gage  |
|  2 | sss   |
+----+-------+

回退

### 终端一
begin; 开启
insert into students (sname) value ("sss");
rollback;  人为异常,事务回退
insert into students (sname) value ("kksk"); 测试语句
### 终端二
mysql> select * from students;
+----+-------+
| id | sname |
+----+-------+
|  1 | Gage  |
|  2 | sss   |
|  4 | kksk  |
+----+-------+
  • 查看隔离级别
mysql> show variables like "%iso%";
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set, 1 warning (0.01 sec)

子查询

普通查询

  • 查询每位学生的各科成绩
mysql> select sname as 姓名,
    -> (select scores.score from scores inner join subjects on subjects.id=scores.subid where subjects.stitle="语文"  and students.id=scores.stuid)  as 语文,
    -> (select scores.score from scores inner join subjects on subjects.id=scores.subid where subjects.stitle="数学"  and students.id=scores.stuid)  as 数学
    -> from students;
+--------+--------+--------+
| 姓名   | 语文   | 数学   |
+--------+--------+--------+
| Gage   |  90.00 |  54.00 |
| sss    |   NULL |   NULL |
| kksk   |   NULL |   NULL |
+--------+--------+--------+

比较查询

  • 查询数学成绩大于85的学生
select sname as 姓名, 
(select scores.score from scores inner join subjects on subjects.id=scores.subid where subjects.stitle="数学"  and students.id=scores.stuid and score>85)  as 数学
from students;
+--------+--------+
| 姓名   | 数学   |
+--------+--------+
| Gage   |  98.00 |
| sss    |  86.00 |
| kksk   |   NULL |
+--------+--------+

范围查询

使用in子查询:表示该操作数(字段值)等于该子查询的任意一个值就满足条件

  • 返回所有以小开头的商品id
select pid from product where pid in
(select pid from product where pname like '小%');

any

表示该操作数的值跟列子查询的任意一个值满足条件就行

  • 返回以小开头的商品
select * from product where pid=any
(select pid from product where pname like '小%');

all

表示该操作数的值必须跟列子查询的所有值都满足给定的比较运算,才算满足了条件

  • 返回商品最高的哪个商品
mysql> select * from product where market_price>=all
    -> (select market_price from product);

exists

如果该子查询有结果数据(无论什么数据,只要大于等于1行),则为true,否则为false

select * from product where exists
(select pid from product where pname like 'p%');