1多表连接查询

1.1普通多表连接查询（了解，无意义）

select * from t_student,t_class;

笛卡尔积，出现的数据条数是两张表条数乘积(这种结果没有意义)

数据库中  .  点翻译成  的

表别名

select t.c_id as id,t.c_name name,t.c_address address,c.c_id class_id,c.c_name class_name  from t_student as t,t_class c;

修改后，不能出现笛卡尔积

select t.c_id as id,t.c_name name,t.c_address address,c.c_id class_id,c.c_name class_name  from t_student as t,t_class c where t.c_class_id = c.c_id;

where 是筛选条件，先从from后数据源中作出笛卡尔积，再从里面挑选满足条件的结果。并不是在连接的时候就查出来了。

1.2内连接查询

select * from 表1 inner join 表2 on 表1.列  运算符 表2.列

select t.c_id,t.c_name,c.c_name from t_student t inner join t_class c on t.c_class_id = c.c_id;

与之前的不一样，内连接先判断查询条件再进行连接，如果没有符合条件的，那么就不连接，就不显示结果。连接显示的是两张表的交集。

where是筛选条件  先确定好笛卡尔积，再进行筛选

on是连接条件  基于on后的条件进行连接

select t.c_id,t.c_name,c.c_name from t_student t inner join t_class c on t.c_class_id = c.c_id where t.c_gender = 2;

1.3左连接查询

左连接查询的关键字是 left join

作用：查询的结果为根据左表中的数据进行连接，如果右表中没有满足条件的记录，则连接空值。

select t.c_id,t.c_name,c.c_name from t_student t left join t_class c on t.c_class_id = c.c_id;
select t.c_id,t.c_name,c.c_name from t_student t left join t_class c on t.c_class_id = c.c_id where c.c_name = '网络工程18级一班';
select t.c_id,t.c_name,c.c_name from t_student t left join t_class c on t.c_class_id = c.c_id where c.c_name = '网络工程18级一班'order by t.c_id desc;
select t.c_id,t.c_name,c.c_name from t_student t left join t_class c on t.c_class_id = c.c_id where c.c_name = '网络工程18级一班'order by t.c_id desc limit 3;

看SQL语句的时候找关键字顺序：  from ， left join ， on， where ，order by，select

表名t_        

点 . 用在成员上，哪张表中的

1.4右连接查询

查询结果是以右表为基准，连接左表中的数据，如果没有符合条件的数据，在右表数据后，连接NULL

右连接作为了解，因为右连接完全可以使用右链接替代

select c.c_name 班级,t.c_name 姓名 from t_student t right join t_class c on c.c_id = t.c_class_id;

select c.c_name 班级,t.c_name 姓名 from t_class c left join t_student t on c.c_id = t.c_class_id;

上面结果一样。

1.5子查询

作用：在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句。

标量子查询：子查询结果是一行一列

查询班级中年龄大于平均年龄的学生信息

select * from t_student where c_age > (select avg(c_age) from t_student);

列级子查询：

查询所有学生所在班级的名称

select c_id,c_name from t_class where c_id in (select distinct c_class_id from t_student);

行级子查询：

查找班级中年龄最大，所在班号最小的学生

select * from t_student where (c_age,c_class_id) = (select max(c_age),min(c_class_id) from t_student);

元组

将后面的一行多列结果与前面括号里的内容依次进行比较。

select * from t_student where  c_age = (select max(c_age) from t_student) and c_class_id = (select min(c_class_id) from t_student);

1.6 自连接查询

作用：在查询数据时，只有一张表，查询时使用自己连接自己。

语法：select * from 表 as 表别名1 inner join 表 表别名2 on 表别名1.列 运算符 表别名2.列 where 条件

select city.* from areas as city inner join areas as province on city.pid = province.aid where province.atitle = '山西省';

select dis.* from areas as dis inner join areas as city on city.aid = dis.pid where city.atitle = '广州市';

自连接查询没有自己的方式，可以使用之前的语句进行连接。

重点：内连接、左连接、子查询

2.视图

30条军规：

• URL统一资源定位符
  
• 要将数据库区分开，使用的时候要对备份进行操作
  
• DNS域名解析服务
  
• 表中必须有主键，例如自增主键。primary key主键  auto_increment自增
  
• 字段必须定义为NOT NULL，并且提供默认值
  
• 禁止使用TEXT 长文本   BLOB二进制长文本  
  
• 禁止使用小数存储货币
  
• 用varchar(20)存储手机号
  
• 禁止使用ENUM枚举
  

特性：

• 视图是一张虚拟表，是一条被封装起来的SQL语句
  
• 视图不存储据图数据
  
• 基本表发生变化，视图也变。
  

操作：

• v_xxx    名字以v开头，表用t开头
  

select goods.id id,goods.name name ,goods.price price,cates.name cname,brands.name bname from goods inner join goods_cates cates on goods.cate_id = cates.id inner join goods_brands brands on goods.brand_id = brands.id;

创建一张虚拟表出来  视图

create view v_goods_info as select goods.id id,goods.name name ,goods.price price,cates.name cname,brands.name bname from goods inner join goods_cates cates on goods.cate_id = cates.id inner join goods_brands brands on goods.brand_id = brands.id;

查看视图：

show tables

使用视图；

select * from v_goods_info;

select * from v_goods_info where cname = '笔记本';

select * from v_goods_info where cname = '笔记本' order by price desc limit 2;

删除视图；

drop view v_good_info;

视图小结

1. 视图封装了对多张基本表的复杂操作，简化用户操作
   
2. 视图只是一个虚表,并不存储任何基本表的表数据,当用户使用视图的时候 视图会从基本表中取出
   
3. 通过视图可以对用户展示指定字段从而屏蔽其他字段数据，更加安全
   

3.索引

概念：

能够快速查询数据的线索就称之为索引。

索引原理

索引的原理就是为无序存放的数据建立一个有序的对应关系，通过这个关系可以快速查找数据。

索引的目的在于提高查询效率。

索引使用：

• 查看表中已有索引 show index from 表名
  
• 创建索引 create index 索引名称 on 表名(字段名称(长度))
  

• 如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
  
• 字段类型如果不是字符串，可以不填写长度部分
  

• 删除索引： drop index 索引名称 on 表名;
  

查询验证

• 开启运行时间监测 set profiling=1;
  
• 查询 ha-99999 的数据 select * from test_index where title='ha-99999;
  
• 为表 test_index 创建索引 create index title_index on test_index(title(10));
  
• 再次查询 ha-99999 的数据 select * from test_index where title='ha-99999;
  
• 查看执行时间 show profiles;
  
• 使用 desc 命令也可以查看索引的效率 desc select * from test_index where title='ha-99999';
  

索引小结：

1. 索引可以明显提高某些字段的查询效率。
   
2. 但不是所有的表都需要建立索引
   
3. 如果表中数据很少，没有必要建立索引
   
4. 如果一个表中的数据增删很频繁，不能建立索引 ，因为只要数据发生增减，索引就要重新建立。增加了系统开销，反而慢了。
   
5. 索引只适合查询操作频繁的表。
   

4.事务（比较重要）

InnoDB存储引擎支持事务

4.1概念：

事务 Transaction 是指作为一个基本工作单元执行的一系列SQL语句的操作，要么完全地执行，要么完全地都不执行。

4.2四大特性 ACID（面使用）

1. 原子性(Atomicity)

一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性

1. 一致性(Consistency)

数据库总是从一个一致性的状态转换到另一个一致性的状态。（在前面的例子中，一致性确保了，即使在执行第三、四条语句之间时系统崩溃，支票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。）

1. 隔离性(Isolation)

通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。（在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外的一个账户汇总程序开始运行，则其看到支票帐户的余额并没有被减去200美元。）

1. 持久性(Durability)

一旦事务提交，则其所做的修改会永久保存到数据库。（此时即使系统崩溃，修改的数据也不会丢失。）

4.3 事务操作

• 开启事务 开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中
  begin; 或 start transaction;
  start不可以，但是begin可以单独使用；
  
• 提交事务
  将缓存中的数据变更维护到物理表中
  commit;
  
• 回滚事务
  放弃缓存中变更的数据 表示事务执行失败 应该回到开始事务前的状态
  rollback;
  

事务的存在是解决数据在操作过程中的 ACID 问题。

事务要么提交，全部成功，要么回滚，一条都不进行。

5.用户管理

修改更新密码后，需要刷新一下

flush privilege；

6.主从配置

读写分离

负载均衡