Mariadb3—多表查询

1.内关联
select 字段名 from 表名1 inner join 表名2 on 表名1.字段名=表名2.字段名  where 条件 

2.左关联
select 字段名 from 表名1 left join 表名2 on 表名1.字段名=表名2.字段名  where 条件   

3.右关联
select 字段名 from 表名1 right join 表名2 on 表名1.字段名=表名2.字段名  where 条件  

4.自关联
select 字段名 from 表名 as 别名1 inner join 表名 as 别名2 on 别名1.字段名=别名2.字段名   where 条件  


5.视图

创建视图：
create view 视图名称 as select语句

删除视图：
drop view 视图名称

6.事务
打开事务：begin | start trancaction
结束事务：
成功操作：commit
失败操作：rollback

7.索引
查看索引：
show index from 表名

创建索引： 
方式一：创建表时创建
create table create_index (字段名 数据类型 约束条件，字段名 数据类型 约束条件，…… key（字段名)）

方式二： 在已经存在的表上添加索引
create index 索引名称 on 表名(字段名)

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

-- 数据库的备份与恢复(shell下运行)
 
#备份testdb数据库
mysqldump -uroot -proot --databases testdb > testdb.sql
#备份所有数据库
mysqldump -uroot -proot --all-databases > all_databases.sql
#备份testdb数据库下的students表
mysqldump -uroot -proot testdb students > students.sql
#还原数据（sql命令行下）
source testdb.sql


视图
对于复杂的查询，在多个地方被使用，如果需求发生了改变，需要更改sql语句，则需要在多个地方进行修改，维护起来非常麻烦
 
假如因为某种需求，需要将user拆房表usera和表userb，该两张表的结构如下：
        　　测试表:usera有id，name，age字段
       　　 测试表:userb有id，name，sex字段
    　　　这时如果php端使用sql语句：select * from user;那就会提示该表不存在，这时该如何解决呢。解决方案：创建视图。以下sql语句创建视图：
       　　 create view user as select a.name,a.age,b.sex from usera as a, userb as b where a.name=b.name;
视图本质就是对查询的封装
定义视图，建议以v_开头
　create view 视图名称 as select语句;
查看视图：查看表会将所有的视图也列出来
　　　　show tables;
删除视图
　　　　drop view 视图名称;
　　　　例：
　　　　drop view v_stu_score_course;
使用：视图的用途就是查询
　　　　select * from v_stu_score_course;

例：

 学生表

 

 

 班级表

 

create view v_student_info as select classes.c_name as '班级', students.name as '学生' from students inner join classes on students.cls_id=classes.id order by classes.id;

 

 

 
 
事务
为什么要有事务
　　　事务具有ACID特性：原子性(A,atomicity)、一致性(C,consistency)、隔离性(I,isolation)、持久性(D,durabulity)。
事务广泛的运用于订单系统、银行系统等多种场景
例如：A用户和B用户是银行的储户，现在A要给B转账500元，那么需要做以下几件事：
检查A的账户余额>500元；
A 账户中扣除500元;
B 账户中增加500元;
上述3个步骤的操作必须打包在一个事物中，任何一个步骤失败，则必须回滚所有步骤
可以用start transaction语句开始一个事物，然后要么使用commit提交将修改的数据持久保存，要么使用rollback撤销所有修改，事物sql的样本如下：
start transation;
select balance from checking where customer_id=1024232;
update checking set balance=balance-200.00 where customer_id=1024232;
update savings set balance=balance+200.00 where customer_id=1024232;
commit;
原子性：事务内的所有操作要么都执行，要么都不执行，它是一个不可分割的工作单位。
一致性：数据库总是从一个一致性的状态转换到另一个一致性的状态（在前面的例子当中，一致性确保了，即使在执行第三、四条语句之间系统崩溃，支票账户也不会损失200美元，因为事物最终没有被提交，所以事物中所做的修改也不会保存到数据库中）
隔离性：一个事物所做的修改在最终提交之前，对其他事物是不可见的（在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外一个账户汇总程序开始运行，则其看到的支票账户的余额并没有被减去200美元。）
持久性：事务完成后，该事务内涉及的数据必须持久性的写入磁盘保证其持久性。当然，这是从事务的角度来考虑的的持久性，从操作系统故障或硬件故障来说，这是不一定的。
事务命令
要求：表的引擎类型必须是innodb类型才可以使用事务，这是mysql表的默认引擎
查看表的创建语句，可以看到engine=innodb
show create table students;

修改数据的命令会触发事务，包括insert、update、delete
开启事务，命令如下：
开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中
begin;

提交事务，命令如下
将缓存中的数据变更维护到物理表中
commit;

回滚事务，命令如下：
放弃缓存中变更的数据
rollback;
 
索引
思考：在图书馆中是如何找到一本书的？
一般的应用系统对比数据库的读写比例在10:1左右，而且插入操作和更新操作很少出现性能问题，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是重中之重
当数据库中数据量很大时，查找数据会变得很慢
优化方案：索引
语法
查看索引
show index from 表名;

创建索引
方式一：建表时创建索引
create table create_index(
id int primary key,
name varchar(10) unique,
age int,
key (age)
);

方式二：对于已经存在的表，添加索引
如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
字段类型如果不是字符串，可以不填写长度部分

create index 索引名称 on 表名(字段名称(长度))
例：
create index age_index on create_index(age);

删除索引：
drop index 索引名称 on 表名;
 
 
测试时间
set profiling=1;#打开sql语句执行时间

show profiles;#查看sql执行时间


外键：
1.查看外键： show create table  表名
2.创建外键：
alter table 表名  add  constraint 外键名 foreign key(字段名) references 表名(字段名) on delete cascade 

ps：增加on delete cascade 之后，可以级联删除

3. 删除外键：
alter table 表名 drop foreign key 外键名字

做外键时，两个表的字段的数据类型要一样才能关联，否则报下面的错误 errno:150 

 

在数据库命令行执行SQL文件 ：source 文件名 

在shell命令行执行SQL语句：
mysql -r用户名 -u数据库密码  -e "sql语句；sql语句;……"

查看数据库的参数  参数可以修改
show variables 
（支持模糊查询）

查看数据库状态
show status