Mysql 的高级用法

MySQL高级部分

简介：

实体与实体之间有三种对应关系，这些关系也需要存储下来
视图用于完成查询语句的封装
事务可以保证复杂的增删操作有效
当数据巨大时，为了提高查询速度可以通过索引实现

关系

创建成绩表scores,结构如下；
创建表的语句如下：
create table scores（
id int primary key auto_increment,
stuid int,
subid int,
score decimal(4,1)
);

外键

思考：怎么保证关系列数据的有效性呢？任何整数都可以吗？
答：必须是学生表中id列存在的数据，可以通过外键约束进行数据的有效性验证
为stuid添加外键约束
alter table scores add constraint stu_sco foreign key(stuid) references student(id)

此时插入或者修改数据时，如果stuid的值在students 
表中不存在则会报错
在创建表时可以直接创建约束
create table scores（
id int primary key auto_increment,
stuid int,
stubid int,
score decimal(6,3),
foreign key(stuid) references students(id),
foreign key(subid) references subjects(id)
);

​

​

​

外键的级联操作

再删除students表的数据时，如果这个id值在scores中已经存在，则会抛异常
推荐使用逻辑删除，还可以解决这个问题
可以创建表时指定级联操纵，也可以在创建表后在修改外键的级联操作

语法

alter table scores add constraint stu_sco foreign key (stuid) references student
级联操作的类型包括：
 ：默认值，抛异常
cascade（级联）：如果主表的记录删掉，则从表中相关联的记录都将被删除
set null：将外键设置为空
no action：什么都不做

先看个问题

问：查询每个学生每个科目的分数
分析：学生姓名来源students表，科目名称来源于subject，分数来源于scores表，怎么将3个表放到 一起查询，并将结果显示在同一个结果集中呢？
答：当查询结果来源多种表时，需要使用链接查询
关键：找到表间的关系，当前的关系是
students 表的id----scores表的stuid
subject表的id-----scoers表的subid
则上面的问题答案是：
select students.name,subjects.stitle,scores.score
from scores
inner join students on scores.stuid=students.id
inner join subjects on scoers.subid=subject.id;
结论：当需要对有关系的多张表进行查询时，需要使用链接join。

实现姓名、学科、分数的组合：

select students.name,pan_subject.title,scores.score
from scores 
inner join students on scores.stuid=students.id
inner join pan_subject on scores.subid=subjects.id

​

链接查询：

链接查询分类如下：

表A inner join 表B：表A与表B匹配的行会出现在结果中
表A left join 表B：表A与表B匹配的行会出现在结果中，外加表A中独有的数据。未对应的数据使用null填充
表A right join 表B：表A与表B匹配的行会出现在结果中，外加表B独有的数据，未对应的数据使用null填充
在查询或条件中推荐使用“表名、列名”的语法
如果多个表中列名不重复可以省略“表名”部分
如果表的名称太长，可以在表名后面使用‘as’简写名‘或’简写‘名’，为表起个临时的简写名称

​

​

 

关联查询：

1.查询男生的姓名，总分

select name，sum（score）
from students
inner join scores on students.id=scores.stuid
where gender=1
group by students.id;

​

自关联：

设计省信息的表结构provinces
■ id
■ ptitle

设计市信息的表结构citys
■ id 
■ ctitle
■ proid

citys表的proid表示城市所属的省，对应着provinces表的id值

问题：能不能将两个表合成一张呢？

思考：观察两张表发现，citys表比provinces表多一个列proid,其他列的类型都是一样的。

意义：存储的都是地区信息，而且每种信息的数据量有限，没必要增加一个新表，或者将来还要存储区，乡镇信息，都增加新表的开销太大

答案：定义表areas，结构如下

​

​

 

从SQL文件中导数据

 

自己随便写的东西

这个失败了，原因目前不知道。有大佬知道原因可以告诉还是萌新的我。

视图：

■ 对于复杂的查询，在多次使用后.维护是一件非常麻烦的事情

■ 解决：定义视图

■视图本质就是对查询的一个封装

■定义视图

create view stuscore as
select students.*,scores.score from scores
inner join students on scores.stuid=students.id

■ 视图的用途就是查询

select * from stuscore;

简单的说就是视图把繁琐的select语句代替了，变得简单了。

实例：

我就把三个表做成一个连接查询：

select * from scores
inner join students on scores.stuid=students.id
inner join subjects on scores.subid=subjects.id

​

如果这个连接查询用的次数多的话，每次使用都很麻烦，所以就使用了视图来进行封装解决。

create view v_tianyue (起的名字,一般视图前面加一个v便于区分表和视图) as （+语句）
select * from    scores inner join students on scores.stuid=students.id
inner join pan_subject on scores.subid=pan_subject.id

这个语句会出错，原因是链接查询中有多个id。

出错提示：

​

正确操作：

create view v_tianyue as
 （ 结果这有重名的字段,这就以学生的id为准，select stu.*,sco.score,sub.title) from scores as sco 
inner join students as stu on sco.stuid=stu.id
inner join pan_subject as sub on sco.subid=sub.id;

​

查看创建的视图：

show tables；

​

用视图查看代替的语句：

​

进行修改视图：

alter view v_tianyue as

(结果这有重名的字段,这就以学生的id为准，select stu.*,sco.score,sub.title) from scores as sco 
inner join students as stu on sco.stuid=stu.id
inner join pan_subject as sub on sco.subid=sub.id
where stu.isdelete=0 and sub.isdelete=0;

​

事务：

■当一个业务逻辑需要多个SQL完成时，如果其中某条SQL语句出错，则希望整个操作都退回

■使用事务可以完成退回的功能，保证业务逻辑的正确性■

■事务四大特性（简称ACID）

■原子性（Atomicity）:

事务中的全部操作在数据库中是不可分割的，要么全部完成，要么均不执行。

■一致性（Consistency）:

几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致

■隔离性（Isolation）:

事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透明的

■持久性（Durability）:

对于任意已提交事务，系统必须保证该事物对数据库的改变不被丢失，即时数据库出现故障

■要求：表的类型必须是innodb或bdb类型（事务只支持这两种类型），才可以对此表使用事务

 

■查看表的创建语句

show create table students;

■查看表的创建语句 ：

show create table students；

■修改表的类型

alter table ‘表名’ engine=inodb;

■事务语句：

  ■开启begin；
  ■提交commit；
  ■回滚rollback；

■查看表的生成语句：

show create table students（表的名字）

​

默认数据库的引擎（查询速度慢，但是写入虚度快）

使用事务的情况：当数据被更改时，包括insert、update、delete，（select （查询）用不到事务）

​

演练：

 

1.开启一个新的终端(Ctrl+shift+N)打开终端，然后链接到mysql。

​
2.先开启一个事物

​

3.编写语句：

​
4.在另一个终端中查询
、​

不变的原因：因为是在内存级中的操作，并没有影响物理上的本地存储（相当于内存与外存储的区别，我是这么理解的，）

5.执行commit：

​
6.接着查询一遍：

​
实例二：

1.

​

2.在另一个终端中：

​

3.rollback的使用：

​

 

索引：

 

■ 思考：在图书馆中，如何找到一本书呢？
■ 一般的应用系统，读写比例在10:1左右，而且插入操作和更新操作很少出现性能问题，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是重中之重
■当数据库数据量很大时，查找数据会变得很慢
■搜引能提高数据访问性能
■主键和唯一索引，都是索引，可以提高查询速度
■下面介绍在mysql中如何实现索引

选择列的数据类型

■越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快
■简单的数据类型更好：整型数据比起字符串，处理开销更小，因为字符串的比较更复杂
■尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL，在mysql中，含有空值的列很难进行查询优化，因为它们使得索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值

建立索引：

where gender=0 and isdelete=0 and birthday>'1990-1-1'（注意点：范围的一般写到后面）

操作：

索引分单列索引和组合索引
     

       ■单列索引，即一个索引只包含单个列。一个表可以有多个单列索引。但这不是组合索引
       ■组合索引：即一个索包含多个列

查看索引（当前表的索引）：

SHOW INDEX FROM table_name（表的名字）;

​

创建索引：

CREATE INDEX indexName(索引名字) ON mytable(表的名字)(username(字段)(length))

删除索引：

DROP INDEX [indexName(索引的名字)] ON mytable;

缺点：

■虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT,UPDATE和DELETE.因为更新表时，mysql不仅要保存数据，还要保存一下索引文件。

■建立索引会占用磁盘的索引文件

实例：

■开启运行时间监测：

set profiling=1;

​

■执行查询语句：

select * from students where id=1；

​

■查看执行的时间：

show Profiles；

​

■为表students的id列创建索引：

create Index titleindex(自己起索引的名字(大小写不区分)) on areas(在某个表中去创立)(id(字段)(20)(字符串的长度))

​

​
■执行查询语句：

select * from students where id=1；

​
■再次查看执行的时间

show Profiles；

​

■可以创建一个表，插入10万条数据，查看有无索引的区别。

欢迎关注，更多优质的学习资料免费领取。