MySQL-高级

在创建表时可以直接创建约束(外键约束)

create table scores(
id int primary key auto_increment,
score decimal(5,2),
stuid int,
subid int,
foreign key(stuid) references students(id),
foreign key(subid) references subjects(id)
)charset=utf8;
•为stuid添加外键约束(在外面插入外键)
alter table scores add constraint stu_sco foreign key(stuid) references students(id);
• 推荐使用逻辑删除，还可以解决这个问题
• 可以创建表时指定级联操作，也可以在创建表后再修改外键的级联操作
• 创建的时候指定
create table scores(
id int primary key auto_increment,
score decimal(5,2),
stuid int,
subid int,
foreign key(stuid) references students(id) on delete cascade,
foreign key(subid) references subjects(id)on delete cascade
)charset=utf8;
• 级联操作的类型包括：
restrict（限制）：默认值，抛异常
cascade（级联）：如果主表的记录删掉，则从表中相关联的记录都将被删除
set null：将外键设置为空
no action：什么都不做
上面这四种方案都不是最好的，最好的还是，推荐使用逻辑删除，

连接查询

连接查询分内连接（inner join）完全匹配，左连接(left join)以左边的表为主，右连接(right join )以右边的表为主
1.内连接（inner join）
select students.name,subjects.title,scores.score from scores
inner join students on scores.stuid=students.id
inner join subjects on scores.subid=subjects.id;
2.左连接(left join)
表A left join 表B：表A与表B匹配的行会出现在结果中，外加表A中独有的数据，未对应的数据使用null填充。
2.1 语句
select * from students left join scores on students.id = scores.stuid;
select * from scores left join students on students.id = scores.stuid;
3.右连接(right join )
表A right join 表B：表A与表B匹配的行会出现在结果中，外加表B中独有的数据，未对应的数据使用null填充。
3.1 语句
select * from scores right join students on students.id = scores.stuid;
select * from students right join scores on students.id = scores.stuid
• 如果表的名称太长，可以在表名后面使用' as 简写名'或' 简写名'，为表起个临时的简写名称
select name ,score  from scores as sco right join students as stu on stu.id = sco.stuid;

• 查询学生的姓名、平均分

根据学生名字
select students.name,avg(scores.score) from scores
inner join students on scores.stuid=students.id
group by students.name;
根据id
select students.name,avg(scores.score) from scores
inner join students on scores.stuid=students.id
group by students.id;
• 查询男生的姓名、总分
select students.name,sum(scores.score) from scores
inner join students on scores.stuid=students.id
where students.gender=1
group by students.name;
• 查询科目的名称、平均分
select subjects.title,avg(scores.score) from scores
inner join subjects on scores.subid=subjects.id
group by subjects.title;
• 查询未删除科目的名称、平均分、最高分
select subjects.title,avg(scores.score),max(scores.score) from scores
inner join subjects on scores.subid=subjects.id
where subjects.isdelete=0
group by subjects.title;

自关联

这就是自关联，表中的某一列，关联了这个表中的另外一列，但是它们的业务逻辑含义是不一样的，城市信息的pid引用的是省信息的id
从sql文件中导入数据source areas.sql;
查询省的名称为“山西省”的所有城市
select city.* from areas as city
inner join areas as province on city.pid=province.id
where province.atitle='山西省';

视图

视图本质就是对查询的一个封装。是查询变的简单。
• 通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。
视图的特性
视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）；

创建视图：

create view v_stu_sco as 
select students.*,scores.score from scores
inner join students on scores.stuid=students.id;
根据视图查询得到封装的SQL语句的内容：
select * from v_stu_sco;

事务

· 当一个业务逻辑需要多个sql完成时，如果其中某条sql语句出错，则希望整个操作都退回。使用事务可以完成退回的功能，保证业务逻辑的正确性；
事务四大特性(简称ACID)
原子性(Atomicity)：事务中的全部操作在数据库中是不可分割的，要么全部完成，要么均不执行；
一致性(Consistency)：几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致
隔离性(Isolation)：事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透明的。
持久性(Durability)：对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障。
• 要求：表的类型必须是innodb或bdb类型，才可以对此表使用事务；
• 数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程序的要求。
• 查看表的创建语句
show create table students;
• 修改表的类型
alter table '表名' engine=innodb;
当数据被更改时，如：update,insert, delete这个时候可以使用事务。
• 事务语句：开启begin;   提交commit;    回滚rollback；

索引

选择列的数据类型
超小的数据通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。
简单的数据类型更好：整型数据比起字符，处理开销更小因为字符串的比较更复杂。
尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL，在MySQL中，含有空值的列很难进行查询优化，因为他们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。
索引能提高数据访问的性能
主键和唯一索引，可以提高查询速度
单列索引，即一个索引只包含单个列,一个表可以有多个单列索引但这不是组合索引。
组合索引，即一个索引包含多个列。
查看索引
show index from students;
创建索引
create index indexName on table(列(length))；
字符串要指定长度，数字不用指定长度。
删除索引
drop index indexName on table;
 缺点
1、虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete,因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。 2、建立索引会占用磁盘空间的索引文件。
开启运行时间监测：
set profiling = 1;
查看执行的时间
show profiles;