mySQL第二天

主外键、级联操作 复杂查询 索引 数据库备份

create table grade(
gid int(4) primary key auto_increment comment “班级的id”, //主键
gname varchar(20) comment “班级的名字”,
gdesc varchar(30) comment “班级的描述”,
gdate date comment “班级创建的时间”

);

create table student(
sid int(4) primary key auto_increment,
sname varchar(20),
spwd varchar(20),
gid int(4) 外键
);

外键要对应其他表的主键

insert into grade values(1,”java”,”很牛”,’2018-7-31’),(2,”andrio”,”中公”,’2018-7-30’);

insert into student values(1,”fujiawei”,”123”,1),(2,”wangda”,”123”,1),(3,”lishu”,”123”,2),(4,”liqian”,”123”,2);

alter table student add constraint fk_gid foreign key(gid) references grade(gid);//建立外键

alter table student drop foreign key fk_gid ;//删除外键

利用外键实现级联操作

在进行主外键关联的时候，可以指定在删除，更新父表（主键表）时，对子表（外键表）进行的相应操作:

cascade(层叠) 父表在更新或者删除时，更新或者删除时，更新或者删除子表对应的记录
set null(设为null) 父表在更新或者删除的时候，子表的对应字段被set null
restrict(约束)
no action(无动作)：在子表有关记录的情况下父表不能更新

alter table student add constraint fk_grade foreign key(gid) references grade(gid) on delete cascade;

alter table student add constraint fk_grade foreign key(gid) references grade(gid) on delete set null;

//数据库备份（不借助可视化工具的方法）:
在dos里输入：
mysqldump -uroot(u+123) -p123(p+密码) test grade>d:/mysqltxt.sql//导出数据库

mysqldump -uroot -p123 test grade>d:/mysqltxt.sql

在mysql里输入：
source d:/mysqltxt.sql;//导入数据库

//内联查询
SELECT s.StudentNo,s.StudentName,r.SubjectNo,r.StudentResult FROM student s INNER JOIN result r ON s.StudentNo=r.StudentNo;

//等值连接
SELECT s.StudentNo,s.StudentName,r.SubjectNo,r.StudentResult FROM student s,result r where s.StudentNo=r.Stud

SELECT s.StudentNo,s.StudentName,r.SubjectNo,r.StudentResult FROM student s LEFT JOIN result r ON s.StudentNo=r.StudentNo;//左连接，左表为基准，右表中不存在左表的值时右表相应值为null

SELECT s.StudentNo,s.StudentName,r.SubjectNo,r.StudentResult FROM student s LEFT JOIN result r ON s.StudentNo=r.StudentNo WHERE r.StudentResult IS NULL;

SELECT s.StudentNo,s.StudentName,su.SubjectName,r.StudentResult FROM result r INNER JOIN student s ON r.StudentNo=s.StudentNo INNER JOIN subject su ON r.SubjectNo=su.SubjectNo;

SELECT s.StudentNo,s.StudentName,r.StudentResult FROM result r INNER JOIN student s ON r.StudentNo=s.StudentNo INNER JOIN subject su ON r.SubjectNo=su.SubjectNo WHERE su.SubjectName=”数据库结构-1” ORDER BY r.StudentResult DESC;

SELECT s.StudentNo,s.StudentName,r.StudentResult FROM result r INNER JOIN student s ON r.StudentNo=s.StudentNo INNER JOIN subject su ON r.SubjectNo=su.SubjectNo WHERE su.SubjectName=”高等数学-2” AND r.StudentResult>=80;

//用子查询语句，效果和上面一致
SELECT s.StudentNo,s.StudentName FROM student s WHERE s.StudentNo IN(SELECT r.StudentNo FROM result r WHERE r.SubjectNo IN (SELECT su.SubjectNo FROM subject su WHERE su.SubjectName=”高等数学-2” ) AND r.StudentResult>=80);

索引： 好处: 提高查询速度，主要用于查询
常规索引：主键索引（非空、唯一、有且只有一个）
唯一索引（可以添加多个唯一索引）
index索引 全局索引

CREATE TABLE emp(eid INT(4),
ename VARCHAR(20),
epwd VARCHAR(20)
);

ALTER TABLE emp ADD PRIMARY KEY(eid);//添加主键索引

ALTER TABLE emp ADD UNIQUE KEY(字段名);//添加唯一索引