mariadb数据库——关联、视图、事务、索引、外键
1.关联
1）连接查询（内关联）
inner join ... on
两个表连接查询
select * from students inner join classes
查询能够对应班级的学生以及班级信息
select * from students inner join classes on students.cls_id=classes.id;
按照要求显示姓名，班级
select students.*, classes.name from students inner join classes on students.cls_id=classes.id;
给数据表起名字
select s.name, c.name from students as s inner join classes as c on s.cls_id=c.id;
查询 有能够对应班级的学生以及班级信息，显示学生的所有信息，只显示班级名称
select students.*, classes.name from students inner join classes on students.cls_id=classes.id;
在以上查询中将班级姓名显示在第一列
select classes.name,students.* from students inner join classes on students.cls_id=classes.id;
查询有能够对应班级的学生以及班级信息，按照班级进行排序
select classes.id, students.* from students inner join classes on students.cls_id=classes.id order by classes.id;
当是同一个班级的时候，按照学生的id从小到大
select classes.id, students.* from students inner join classes on students.cls_id=classes.id order by classes.id, students.id;
2）连接查询（左关联，右关联）
左关联，以左边表为基准，条件对应不上的显示null
select * from students left join classes on students.cls_id=classes.id;
右关联，以后边为基准，条件对应不上的显示null
select * from classes left join students on students.cls_id=classes.id;
3）自关联
查询出河北省所有市
select province.name,city.name from areas as province inner join areas as city on province.aid=city.pid having province.name='河北省';
子查询
标量子查询
查询出北京市所有区的信息
select * from areas where pid in (select aid from areas where name='北京市');
2.视图
比如将一个user表拆分成usera表和userb表，如果再去调用user表就会显示user不存在，就需要创建视图
1）视图的本质就是对查询的封装
create view v_info as select students.*,classes.name as class from students inner join classes on classes.id=students.cls_id;
2）删除视图
drop view v_info;
3）使用视图（只用来查询）
select * from v_info;
3.事务
1）特点
原子性(atomicity)：事务内的所有操作要么都执行，要么都不执行，它是一个不可分割的工作单位。
一致性(consistency)：数据库总是从一个一致性的状态转换到另一个一致性的状态
隔离性(isolation)：一个事物所做的修改在最终提交之前，对其他事物是不可见的
持久性(durabulity)：事务完成后，该事务内涉及的数据必须持久性的写入磁盘保证其持久性。
2）命令
begin 开启事务
commit 提交事务
rollback 回滚事务 delete操作可回滚，truncate操作不可回滚
3）关闭自动提交事务
set autocommit=0; 临时关闭
vim /etc/my.cnf.d/server.cnf 修改配置文件重启后永久生效
[server]
autocommit=0
4.索引
1）添加索引
create index index_name on students(name);
2）查看索引
show index from students;
3）删除索引
drop index index_name on students;
4）测试时间
set profiling=1;#打开sql语句执行时间
show profiles;#查看sql执行时间
5.外键
如果一个实体的某个字段指向另一个实体的主键，就称为外键。被指向的实体，称之为主实体（主表），也叫父实体（父表）。负责指向的实体，称之为从实体（从表），也叫子实体（子表）。
对关系字段进行约束，当为从表中的关系字段填写值时，会到关联的主表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并报错。
1）创建外键
将学生的cls_id字段指向班级的id字段并设置级联删除功能
alter table students add constraint fk_students foreign key (cls_id) references classes(id) on delete cascade;
2）删除外键
alter table students drop foreign key fk_students;
6.第三方软件连接数据库
1）允许root用户在任意主机登录
grant all privileges on *.* to root@'%' identified by 'root';
2）使用Navicat连接数据库
3）使用Python解释器连接，需要pymysql模块
import pymysql
db = pymysql.connect(host="192.168.254.10", user="root",
password="root", db="testdb", port=3306)
cur = db.cursor()
for i in range(100000):
cur.execute("insert into test_table values (0,'name-%s','title-%s')" % (i,i))
db.commit()
result = cur.fetchall()
print(result)
4）利用shell终端执行命令
mysql -uroot -proot -e "show databases;"
