Mysql基础 02 外键、多表查询、索引

文章目录一、外键约束二、多表查询1、连接查询1笛卡尔积查询2内连接3外连接2、复合条件连接查询3、子查询三、索引一、外键约束外键所依赖的表为主表字段绑定外键的表为子表。主表CREATETABLEClassCharger(idTINYINTPRIMARYKEYauto_increment,nameVARCHAR(20),ageINT,is_marrigedtinyint);注如果ClassCharger在s3库中而当前库不在s3中可以将表名写为s3.ClassChargerINSERTINTOClassCharger(name,age,is_marriged)VALUES(冰冰,12,0),(丹丹,14,0),(歪歪,22,0),(姗姗,20,0),(小雨,21,0);子表创建表时添加外键[CONSTRAINT 外键名]FOREIGN KEY (charger_id) REFERENCES ClassCharger(id)注作为外键一定要和关联主键的数据类型保持一致。如果不写CONSTRAINT 外键名则会自动获取一个外键名。CREATETABLEStudent(idINTPRIMARYKEYauto_increment,nameVARCHAR(20),charger_idTINYINT,CONSTRAINTabcFOREIGNKEY(charger_id)REFERENCESClassCharger(id))ENGINEINNODB;注①charger_idTINYINT 类型用于存储关联到 ClassCharger 表的外键值。②CONSTRAINT abc FOREIGN KEY (charger_id) REFERENCES ClassCharger(id)这表示 Student 表的 charger_id 字段必须是 ClassCharger 表中 id 字段的合法值。③如果 ClassCharger 表中没有对应的 id则无法插入或更新 Student 表中的 charger_id。④如果 ClassCharger 表中的 id 被删除Student 表中对应的 charger_id 记录也会受到限制默认行为是 RESTRICT即阻止删除。⑤ENGINEINNODB指定使用 InnoDB 引擎该引擎支持外键约束。如果使用 MyISAM 引擎外键约束将被忽略。INSERTINTOStudent(name,charger_id)VALUES(alvin1,2),(alvin2,4),(alvin3,1),(alvin4,3),(alvin5,1),(alvin6,3),(alvin7,2);由于外键的作用下列两句sql都执行不成功。deletefromClassChargerwhereid1;insertintostudent(name,charger_id)values(haha,10);创建表后也可以添加、删除外键ALTERTABLEStudentADDCONSTRAINTabcFOREIGNKEY(charger_id)REFERENCESClassCharger(id);ALTERTABLEStudentDROPFOREIGNKEYabc;INNODB支持的ON语句在关系数据库中能唯一标识表中一行数据的列或列的组合叫做候选键。例如主键和唯一索引就是候选键。外键约束对子表的含义: 如果在父表中找不到候选键,则不允许在子表上进行insert/update外键约束对父表的含义:在父表上进行update/delete在子表中有对应匹配行的候选键时,父表的行为取决于在定义子表的外键时指定的 on update/on delete子句。①cascade方式在父表上update/delete记录时同步update/delete掉子表的匹配记录。外键的级联删除如果父表中的记录被删除则子表中对应的记录自动被删除ALTERTABLEStudentADDCONSTRAINTabcFOREIGNKEY(charger_id)REFERENCESClassCharger(id)ONDELETECASCADE;②set null方式在父表上update/delete记录时将子表上匹配记录的列设为null。注子表的外键列不能为not nulALTERTABLEStudentADDCONSTRAINTabcFOREIGNKEY(charger_id)REFERENCESClassCharger(id)ONDELETESETNULL;③Restrict方式 ④No action方式这两种方式用的比较少了解即可都是拒绝对父表进行删除更新操作。二、多表查询1、连接查询先准备两张表创建表employee并插入记录createtableemployee(emp_idintauto_incrementprimarykeynotnull,emp_namevarchar(50),ageint,dept_idint);insertintoemployee(emp_name,age,dept_id)values(A,19,200),(B,26,201),(C,30,201),(D,24,202),(E,20,200),(F,38,204);创建表department并插入记录createtabledepartment(dept_idint,dept_namevarchar(100));insertintodepartmentvalues(200,人事部),(201,技术部),(202,销售部),(203,财政部);1笛卡尔积查询SELECT*FROMemployee,department;2内连接A inner join B找出A表和B表中匹配on条件的所有记录。select*fromemployee,departmentwhereemployee.dept_iddepartment.dept_id;或select*fromemployeeinnerjoindepartmentonemployee.dept_iddepartment.dept_id;注这里 inner join 前后两张表的顺序颠倒查询结果一样只是每条记录内显示顺序不同先显示前面表的内容再显示后面表的。3外连接①左外连接A left join B在内连接的基础上增加A表有B表没有的结果。select*fromemployeeleftjoindepartmentonemployee.dept_iddepartment.dept_id;②右外连接A right join B在内连接的基础上增加B表有A表没有的结果。select*fromemployeeRIGHTJOINdepartmentonemployee.dept_iddepartment.dept_id;③全外连接可以理解为左连接和右连接的并集。mysql不支持full join语句可以使用union语句来实现全外连接。select*fromemployeeRIGHTJOINdepartmentonemployee.dept_iddepartment.dept_idUNIONselect*fromemployeeLEFTJOINdepartmentonemployee.dept_iddepartment.dept_id;注意union与union all的区别union会去掉相同的纪录2、复合条件连接查询查询员工年龄大于等于25岁的部门SELECTDISTINCTdepartment.dept_nameFROMemployee,departmentWHEREemployee.dept_iddepartment.dept_idANDage25;3、子查询子查询是将一个查询语句嵌套在另一个查询语句中。内层查询语句的查询结果可以为外层查询语句提供查询条件。子查询中可以包含IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字。还可以包含比较运算符 、 !、 、等。1查询employee表但dept_id必须在department表中出现过select*fromemployeewheredept_idIN(selectdept_idfromdepartment);2复制employee到AAcreatetableAA(select*fromemployee);注这里只复制表字段和记录不复制约束条件。3带EXISTS关键字的子查询EXISTS关字键字表示存在返回Ture或False。当返回Ture时外层查询语句将进行查询当返回值为False时外层查询语句不进行查询。select*fromemployeeWHEREEXISTS(SELECTdept_namefromdepartmentwheredept_id205);这里EXISTS返回False外层select查询结果为Empty set。三、索引索引在Mysql中也叫做“键”是存储引擎用于快速找到记录的一种数据结构。索引相当于字典的目录的页码可以大幅提高查询速度。创建与维护索引会消耗很多时间与磁盘空间但查询速度大大提高。注primary key主键也是一种索引。unique是唯一索引。设置字段约束条件为unique时会自动加一个unique索引。createtabletest(idintprimarykeyauto_increment,namevarchar(20),salaryintDEFAULT1000);insertintotest(name)values(111),(222),(333),(444);altertabletestmodifynamevarchar(20)unique;创建表时创建索引语法createtable表名(字段名1数据类型[约束条件],字段名2数据类型[约束条件],...[unique|fulltext|spatial]index|key[索引名](字段名[(长度)][asc|desc]));创建索引时如果不指定索引名则会以字段名为索引名。创建普通索引示例createtabletest1(idint,namevarchar(20),indexindex_name(name)-- 关键字index换成key效果相同);创建全文索引示例全文索引适合给大文本做索引。createtabletest2(idint,namevarchar(20),resumevarchar(100),fulltextindexindex_name(resume));创建多列索引示例createtabletest3(idint,namevarchar(20),resumevarchar(100),INDEXindex_name_resume(name,resume));在已存在表上创建索引方法一create[unique|fulltext|spatial]index索引名on表名(字段名[(长度)][asc|desc]);方法二altertable表名add[unique|fulltext|spatial]index索引名(字段名[(长度)][asc|desc])示例createindexindex_nameontest4(name);altertabletest4adduniqueindexindex_name(name);删除索引语法 drop index 索引名 on 表名;示例dropindexindex_nameontest4;