MySQL数据库项目实例教程 课件 项目四：学生寝室管理数据库

MySQL数据库项目四：学生寝室管理数据库MySQL数据库4.1设计学生寝室管理数据库备份与恢复数据库管理用户与权限查询数据表目录创建数据库与数据表需求分析概念结构设计逻辑结构设计数据库设计一般分为6个阶段物理结构设计数据库实施数据库运行和维护需求分析通过交谈问卷调查设计人员寝室信息管理学生信息管理班级信息管理班级信息班级编号和班级名称学生信息学号、姓名、性别、身高、出生日期、联系电话、家庭住址寝室信息寝室编号、寝室门牌号、寝室楼名称、床位数、寝室朝向概念结构设计概念结构设计是整个数据库设计的关键总结归纳抽象E-R图创建数据表学生寝室管理系统实体：班级、学生、寝室由于一个班级有多名学生，一个学生只属于某一个班级，所以班级和学生的关系是一对多的关系。由于一个寝室可以住多名学生，一个学生只能住在某一个寝室，所以寝室和学生的关系是一对多的关系。图1学生和班级的局部E-R图学生班级编号班级名称班级学号家庭住址联系电话出生日期身高性别姓名属于图2学生和寝室的局部E-R图学生学号家庭住址联系电话出生日期身高性别姓名入住寝室床位数寝室编号寝室门牌号寝室楼名称寝室朝向图3学生寝室管理数据库全局E-R图将各局部E-R图进行合并，消除冗余后形成全局E-R图逻辑结构设计概念结构设计阶段设计的E-R图反映了用户需求的模型，它独立于任何一种数据模型，独立于任何一个数据库管理系统。创建数据表逻辑结构设计的任务把概念结构设计的概念模型转换成某个具体的数据库管理系统所支持的数据模型。MySQL数据库管理系统是关系型数据库管理系统班级班级编号，班级名称寝室寝室编号，寝室门牌号，寝室楼名称，床位数，寝室朝向学生学号，姓名，身高，性别，出生日期，联系电话，家庭住址1.将实体转换成关系模式班级（班级编号，班级名称）学生（寝室编号，寝室门牌号，寝室楼名称，床位数，寝室朝向，所在班级编号，

所在寝室编号）寝室（学号，姓名，身高，性别，出生日期，联系电话，家庭住址）2.实体间的关系转换成关系模式学生寝室管理数据库中班级和学生、寝室和学生都是一对多的关系创建数据表数据库设计数据库设计中逻辑结构设计阶段的结果不是唯一的。进一步提高数据库系统性能在逻辑结构设计阶段应根据应用需求调整和优化数据模型。物理结构设计创建数据表物理结构设计阶段以逻辑结构设计阶段为依据结合具体的数据库管理系统的特点与存储设备的特性确定数据库在物理设备上的存储结构和存取方法表1班级表（class）学生寝室管理数据库使用的数据库管理系统产品是MySQL8.0表2寝室表（dorm）表3学生表数据库设计本讲结束MySQL数据库项目四：学生寝室管理数据库MySQL数据库4.2.1使用SQL语句创建数据库与数据表任务CREATEDATABASE创建数据库12CREATETABLE创建数据表3CREATETABLE创建数据表的同时添加数据约束4ALTERTABLE管理数据表创建数据库创建数据库CREATEDATABASE使用CREATEDATABASE语句创建数据库语法格式：CREATEDATABASE[IFNOTEXISTS]数据库名[[DEFAULT]CHARACTERSET字符集名][[DEFAULT]COLLATE

校对规则名];语法说明：

[IFNOTEXISTS]：可以避免出现数据库已经存在无法再新建的错误。

[DEFAULT]CHARACTERSET字符集名：用于指定数据库的字符集。

[DEFAULT]COLLATE校对规则名：用于指定字符集的校对规则。创建数据库CREATEDATABASE【例】创建一个名为“dormdb”的数据库（1）CREATEDATABASEdormdb;创建数据库CREATEDATABASE【例】创建一个名为“dormdb”的数据库（2）CREATEDATABASEIFNOTEXISTSdormdb;创建数据库CREATEDATABASE【例】创建一个名为“dormdb”的数据库，字符集设置为utf8mb4，校对规则设置为utf8mb4_general_ci（2）CREATEDATABASEIFNOTEXISTSdormdbCHARACTERSETutf8mb4COLLATEutf8mb4_general_ci;

创建数据库CREATEDATABASE成功创建数据库后，可以使用SHOWDATABASES语句显示当前服务器中已经存在的数据库。语法格式：SHOWDATABASES;创建数据库CREATEDATABASE在创建数据表或对数据表进行操作之前，需要使用USE语句选择数据库，否则会出现“没有选择数据库”的错误。语法格式：USE

数据库名;创建数据库CREATEDATABASE【例】将dormdb数据库设置为当前数据库SQL语句：USE

dormdb;创建数据库CREATEDATABASE数据库创建之后，数据库使用的字符集和校对规则就确定了，如果需要修改数据库的字符集和校对规则，可以使用ALTERDATABASE语句。语法格式：ALTERDATABASE[数据库名]{[DEFAULT]CHARACTERSET字符集名|[DEFAULT]COLLATE

校对规则名};创建数据库CREATEDATABASE【例】将dormdb数据库的字符集修改为gb2312，默认校对规则修改为gb2312_unicode_ciSQL语句：ALTERDATABASEdormdbDEFAULTCHARACTERSETgb2312DEFAULTCOLLATEgb2312_chinese_ci;创建数据库CREATEDATABASE删除数据库是指在数据库系统中删除已经存在的数据库，即将已经存在的数据库从磁盘中清除。语法格式：DROPDATABASE[IFEXISTS]数据库名;创建数据表CREATEDATABASE【例】删除dormdb数据库SQL语句：DROPDATABASEdormdb;或DROPDATABASEIFEXISTSdormdb;创建数据表创建数据表CREATETABLE数据库与数据表是什么关系呢?创建数据表CREATETABLE核心：油容器：桶创建数据表CREATETABLE核心：数据表容器：数据库创建数据表CREATETABLE创建数据表的语句为CREATETABLE语法格式：CREATETABLE[IFNOTEXISTS]数据表名(

字段名1数据类型[列级约束][注释][…],

字段名2数据类型[列级约束][注释][…], …

字段名n数据类型[列级约束][注释][…] [,表级约束] …)[表选项];创建数据表CREATETABLE创建数据表的语句为CREATETABLE语法格式：CREATETABLE[IFNOTEXISTS]数据表名(

字段名1数据类型[列级约束][注释][…],

字段名2数据类型[列级约束][注释][…], …

字段名n数据类型[列级约束][注释][…] [,表级约束] …)[表选项];语法说明：数据表名：数据表名的形式可以是“数据库名.数据表名”或“数据表名”。[IFNOTEXISTS]：可以避免出现表已经存在无法再新建的错误。[表选项]：用于设置表的相关特性，如存储引擎、字符集、校对集等。创建数据表CREATETABLE创建数据表的语句为CREATETABLE最简的语法格式：CREATETABLE数据表名(

字段名1数据类型,

字段名2数据类型, …

字段名n数据类型 );创建数据表CREATETABLE【例】在dromdb数据库中创建班级表（class）class表字段名称类型完整性约束字段描述classidCHAR(10)主键班级编号classnameVARCHAR(10)非空班级名称创建数据表CREATETABLE【例】在dromdb数据库中创建寝室表（dorm）字段名称类型完整性约束字段描述dormidINT主键、自增寝室编号roomnoCHAR(3)非空寝室门牌号apartmentVARCHAR(10)非空寝室楼名称totalbedsINT床位数在2到8之间床位数towardVARCHAR(10)默认值为“南”寝室朝向dorm表创建数据表CREATETABLE【例】在dromdb数据库中创建学生表（student）字段名称类型完整性约束字段描述studentidCHAR(8)主键学号snameVARCHAR(10)非空姓名sexCHAR(1)非空性别heightFLOAT身高birthdayDATE

出生日期telephoneVARCHAR(20)唯一联系电话addressVARCHAR(50)家庭住址classidVARCHAR(10)外键所在班级编号dormidINT外键所在寝室编号student表创建数据表CREATETABLE如何确保数据的准确性和一致性？约束创建数据表的同时添加数据约束创建数据表的同时添加数据约束CREATETABLE创建数据表的语句为CREATETABLE语法格式：CREATETABLE[IFNOTEXISTS]数据表名(

字段名1数据类型[列级约束][注释][…],

字段名2数据类型[列级约束][注释][…], …

字段名n数据类型[列级约束][注释][…] [,表级约束] …)[表选项];创建数据表的同时添加数据约束CREATETABLE数据完整性约束：

主键约束、非空约束、检查约束、唯一约束、默认值约束和外键约束。创建数据表的同时添加数据约束CREATETABLE主键约束语法格式：字段名数据类型PRIMARYKEY创建数据表的同时添加数据约束CREATETABLE主键约束语法格式：字段名数据类型PRIMARYKEY字段名称类型完整性约束字段描述classidCHAR(10)主键班级编号classnameVARCHAR(10)非空班级名称创建数据表的同时添加数据约束CREATETABLE非空约束语法格式：字段名数据类型NOTNULL字段名称类型完整性约束字段描述dormidINT主键、自增寝室编号roomnoCHAR(3)非空寝室门牌号apartmentVARCHAR(10)非空寝室楼名称totalbedsINT床位数在2到8之间床位数towardVARCHAR(10)默认值为“南”寝室朝向创建数据表的同时添加数据约束CREATETABLE检查约束语法格式：字段名数据类型CHECK(表达式)字段名称类型完整性约束字段描述dormidINT主键、自增寝室编号roomnoCHAR(3)非空寝室门牌号apartmentVARCHAR(10)非空寝室楼名称totalbedsINT床位数在2到8之间床位数towardVARCHAR(10)默认值为“南”寝室朝向创建数据表的同时添加数据约束CREATETABLE默认约束语法格式：字段名数据类型DEFAULT默认值字段名称类型完整性约束字段描述dormidINT主键、自增寝室编号roomnoCHAR(3)非空寝室门牌号apartmentVARCHAR(10)非空寝室楼名称totalbedsINT床位数在2到8之间床位数towardVARCHAR(10)默认值为“南”寝室朝向创建数据表的同时添加数据约束CREATETABLE自增约束语法格式：字段名数据类型AUTO_INCREMENT字段名称类型完整性约束字段描述dormidINT主键、自增寝室编号roomnoCHAR(3)非空寝室门牌号apartmentVARCHAR(10)非空寝室楼名称totalbedsINT床位数在2到8之间床位数towardVARCHAR(10)默认值为“南”寝室朝向创建数据表的同时添加数据约束CREATETABLE唯一约束语法格式：字段名数据类型UNIQUE字段名称类型完整性约束字段描述studentidCHAR(8)主键学号snameVARCHAR(10)非空姓名sexCHAR(1)非空性别heightFLOAT身高birthdayDATE

出生日期telephoneVARCHAR(20)唯一联系电话addressVARCHAR(50)家庭住址classidVARCHAR(10)外键所在班级编号dormidINT外键所在寝室编号创建数据表的同时添加数据约束CREATETABLE外键约束语法格式：CONSTRAINT约束名FOREIGNKEY(外键字段名)REFERENCES外键参照的表的名(关联字段名)字段名称类型完整性约束字段描述studentidCHAR(8)主键学号snameVARCHAR(10)非空姓名sexCHAR(1)非空性别heightFLOAT身高birthdayDATE

出生日期telephoneVARCHAR(20)唯一联系电话addressVARCHAR(50)家庭住址classidVARCHAR(10)外键所在班级编号dormidINT外键所在寝室编号创建数据表CREATETABLE

创建数据表的同时添加数据约束CREATETABLE添加外键的位置与其他约束不同呢？创建数据表的同时添加数据约束CREATETABLE列级约束主键约束外键约束检查约束唯一约束非空约束默认约束自增约束表级约束主键约束外键约束检查约束唯一约束创建数据表CREATETABLE

管理数据表管理数据表ALTERTABLE修改数据表的操作：（1）修改数据表名（2）修改字段名（3）修改字段的数据类型（4）添加字段（5）删除字段（6）修改字段的排列位置（7）添加约束管理数据表ALTERTABLE修改数据表名语法格式：ALTERTABLE原数据表名RENAME[TO]新数据表名;管理数据表ALTERTABLE【例】将数据表class改名为tb_class。SQL语句：ALTERTABLEclassRENAMEtb_class;管理数据表ALTERTABLE修改字段名和数据类型可以使用CHANGE子句语法格式：ALTERTABLE数据表名CHANGE

原字段名新字段名新数据类型;管理数据表ALTERTABLE【例】将class表中的classid字段改名为id，数据类型保持不变。SQL语句：ALTERTABLEclassCHANGEclassididCHAR(10);管理数据表ALTERTABLE如果只需要修改字段的数据类型，可以使用MODIFY子句语法格式：ALTERTABLE数据表名MODIFY

字段名新数据类型;管理数据表ALTERTABLE【例】将数据表class中classname字段的数据类型由VARCHAR(10)修改成VARCHAR(20)。SQL语句：ALTERTABLEclassMODIFYclassnameVARCHAR(20);管理数据表ALTERTABLECHANG子句和MODIFY子句都可以修改字段的数据类型，那他们有什么区别呢ALTERTABLE数据表名CHANGE

原字段名新字段名新数据类型;ALTERTABLE数据表名MODIFY

字段名新数据类型;管理数据表ALTERTABLE添加字段语法格式：ALTERTABLE数据表名ADD[COLUMN]新字段名数据类型[约束条件][FIRST|AFTER

已存在字段名];管理数据表ALTERTABLE【例】在寝室表dorm中的roomno字段后添加一个INT类型的字段c1。SQL语句：ALTERTABLEdormADDclINTAFTERroomno;管理数据表ALTERTABLE删除数据表中的字段语法格式：ALTERTABLE数据表名DROP[COLUMN]字段名;管理数据表ALTERTABLE【例】将字段c1从寝室表dorm中删除。SQL语句：ALTERTABLEdormDROPcl;管理数据表ALTERTABLE修改字段的排列位置语法格式：ALTERTABLE数据表名MODIFY

字段名1数据类型FIRST|AFTER字段名2;管理数据表ALTERTABLE【例】将寝室表中的toward字段的位置修改到apartment字段之后。SQL语句：ALTERTABLEdormMODIFYtowardINTAFTERapartment;管理数据表ALTERTABLE修改数据表时添加删除主键约束添加主键约束的语法格式：ALTERTABLE数据表名ADD[CONSTRAINT

主键约束名]PRIMARYKEY(字段名1,字段名2,...,字段名n);管理数据表ALTERTABLE修改数据表时添加删除主键约束删除主键约束的语法格式：ALTERTABLE数据表名DROPPRIMARYKEY;管理数据表ALTERTABLE【例】将班级表class的classid字段设置为主键。SQL语句：ALTERTABLEclassADDCONSTRAINTpk_classPRIMARYKEY(classid);管理数据表ALTERTABLE【例】删除班级表class的主键约束。SQL语句：ALTERTABLEclassDROPPRIMARYKEY；管理数据表ALTERTABLE修改数据表时添加删除外键约束添加外键约束的语法格式：ALTERTABLE数据表名ADD[CONSTRAINT外键约束名]FOREIGNKEY(字段名1,字段名2,...,字段名n)REFERENCES

主表名(主键字段1[,主键字段2,...]);管理数据表ALTERTABLE修改数据表时添加删除主键约束删除外键约束的语法格式：ALTERTABLE数据表名DROPFOREIGNKEY外键约束名；管理数据表ALTERTABLE【例】为学生表student添加外键，学生表student的classid字段参照班级表class表的classid字段。SQL语句：ALTERTABLEstudentADDCONSTRAINTfk_student_classidFOREIGNKEY(classid)REFERENCESclass(classid);管理数据表ALTERTABLE【例】删除学生表student的外键约束。SQL语句：ALTERTABLEstudentDROPFOREIGNKEYfk_student_classid;管理数据表ALTERTABLE修改数据表时添加删除唯一约束添加唯一约束的语法格式：ALTERTABLE数据表名ADD[CONSTRAINT

唯一约束名]UNIQUE(字段名1,字段名2,...,字段名n);管理数据表ALTERTABLE修改数据表时添加删除唯一约束删除唯一约束的语法格式：ALTERTABLE数据表名DROP{INDEX|KEY}

唯一约束名；管理数据表ALTERTABLE【例】为学生表student的telephone字段添加唯一约束。SQL语句：ALTERTABLEstudent

ADDCONSTRAINTuq_student_telephoneUNIQUE(telephone);管理数据表ALTERTABLE【例】删除学生表student的telephone字段的唯一约束。SQL语句：ALTERTABLEstudentDROPINDEXuq_student_telephone;管理数据表ALTERTABLE修改数据表时添加删除检查约束添加检查约束的语法格式：ALTERTABLE数据表名ADD[CONSTRAINT

检查约束名]CHECK(表达式);管理数据表ALTERTABLE修改数据表时添加删除检查约束删除检查约束的语法格式：ALTERTABLE数据表名DROPCHECK检查约束名；管理数据表ALTERTABLE【例】为寝室表dorm的totalbeds字段添加检查约束，要求床位数在2到8之间。SQL语句：ALTERTABLEdorm

ADDCONSTRAINTck_dorm_totalbedsCHECK(totalbeds>=2ANDtotalbeds<=8);管理数据表ALTERTABLE【例】删除寝室表dorm的totalbeds字段检查约束。SQL语句：ALTERTABLEdormDROPCHECKck_dorm_totalbeds;管理数据表ALTERTABLE修改数据表时添加删除非空约束语法格式：ALTERTABLE数据表名MODIFY[COLUMN]

字段名新数据类型[NOTNULL|NULL]管理数据表ALTERTABLE【例】为班级表class的classname字段添加非空约束。SQL语句：ALTERTABLEclassMODIFYclassnameVARCHAR(10)NOTNULL;【例】删除班级表class的classname字段的非空约束。SQL语句：ALTERTABLEclassMODIFYclassnameVARCHAR(10)NULL;管理数据表ALTERTABLE修改数据表时添加删除默认值约束语法格式：ALTERTABLE数据表名MODIFY[COLUMN]

字段名新数据类型[DEFAULT默认值]管理数据表ALTERTABLE【例】为寝室表dorm的toward字段添加默认值'南'。SQL语句：ALTERTABLEdormMODIFYtowardVARCHAR(10)DEFAULT'南’;【例】删除寝室表dorm的toward字段的默认值'南'。SQL语句：ALTERTABLEdormMODIFYtowardVARCHAR(10)；管理数据表ALTERTABLE修改数据表时添加删除自增约束语法格式：ALTERTABLE数据表名MODIFY[COLUMN]

字段名新数据类型[AUTO_INCREMENT]管理数据表ALTERTABLE【例】为寝室表dorm的dormid字段添加自增约束。SQL语句：ALTERTABLEdormMODIFYdormidINTAUTO_INCREMENT;【例】删除寝室表dorm的dormid字段的自增约束。SQL语句：ALTERTABLEdormMODIFYdormidINT；管理数据表ALTERTABLE在MySQL中，使用DROPTABLE语句来删除数据表。语法格式：DROPTABLE数据表名;管理数据表ALTERTABLE【例】删除学生表studentSQL语句：DROPTABLEstudent;管理数据表ALTERTABLE【注意】①在删除表时，需要确保该表中的字段未被其他表关联，如果有关联，则需要先删除关联表或删除关联，否则删除表的操作将会失败。②删除数据表的操作应该谨慎使用。一旦删除了数据表，那么表中的数据将会全部清除，没有各份则无法恢复。课程小结1创建数据库2创建数据表3创建数据表的同时添加数据约束4管理数据表MySQL数据库4.2.2操作数据表任务INSERT插入数据12UPDATE更新数据3DELETE删除数据插入数据INSERT

INSERT语句的语法格式：INSERT[INTO]数据表名[(字段名列表)]VALUES(值列表);其中，VALUES子句包含各字段需要插入的数据。插入数据INSERT1.向数据表中插入完整的一行数据INSERT[INTO]数据表名[(字段名列表)]VALUES(值列表);INSERT[INTO]数据表名VALUES(值列表);插入数据INSERT2.向数据表中插入行的一部分INSERT[INTO]数据表名(字段名列表)VALUES(值列表);(字段名列表)插入数据INSERT【例】向class表中插入一行数据，班级编号为“200101”，班级名称为“20汽营1班”

SQL语句：INSERTINTOclassVALUES('200101','20汽营1班');('200101','20汽营1班');插入数据INSERT【例】向student表中插入一条记录，其中学生编号为“20010101”，学生姓名为“李旺”，学生性别为“男”。

SQL语句：INSERTINTOstudent(studentid,studentname,sex)

VALUES('20010101','李旺','男');插入数据INSERT使用INSERTINTO语句插入数据是应注意：①向CHAR、VARCHAR、TEXT及日期型这些数据类型的字段插入数据时，字段值要用半角单引号（'）或双引号（"）引起来。②向具有自增约束AUTO_INCREMENT的字段插入数据时，建议插入NULL值，此时将向自增型字段插入下一个编号。③向具有默认值约束字段插入数据时，字段值可以使用DEFAULT关键字，表示插入的是该字段的默认值。更新数据UPDATE使用UPDATE语句更新数据表中的数据时，可以更新特定的数据，也可以同时更新所有记录的数据UPDATE语句的语法格式:UPDATE

数据表名SET

字段名=值[WHERE

条件表达式];其中，SET子句用于指定表中要修改的字段名及其字段值，值可以是表达式，也可以是该字段所对应的默认值。更新数据UPDATE【例】修改学生表student中“李旺”同学的电话号。SQL语句：UPDATEstudentSETtelephone=WHEREstudentname='李旺';更新数据UPDATE当使用UPDATE语句更新数据时，应该注意：①用WHERE子句指定需要更新的行，用SET子句指定新值。②UPDATE无法更新标识列。③如果行的更新违反了约束或规则，比如违反了字段非空约束，或者新值是不兼容的数据类型，将取消该语句，并返回错误提示，不会更新任何记录。④每次只能修改一个表中的数据。⑤可以同时把一个字段或多个字段、一个变量或多个变量放在一个表达式中。删除数据DELETE使用DELETE语句删除数据表中的数据的语法格式：DELETEFROM数据表名[WHERE

条件表达式];其中，WEHRE子句为可选项。WHERE子句指定了删除条件，如果没有WHERE子句，DELETE语句将删除表中的所有记录。删除数据DELETE【例】删除学生表student中“李旺”同学的记录。SQL语句：DELETEFROMstudentWHEREstudentname='李旺';删除数据DELETE【例】删除学生表student中所有同学的记录SQL语句：DELETEFROMstudent;创建数据库与数据表本讲结束MySQL数据库项目四：学生寝室管理数据库MySQL数据库4.3.1查询数据表任务SELECT单表查询12JOIN多表连接查询单表查询SELECT查询数据列是指从数据表中查询指定的字段值组成结果集。通过SELECT子句的字段名组成结果集的列。单表查询SELECT（1）查询指定的列如果只需要查询数据表中的某些字段的值，在关键字SELECT后指定需要查询的字段，当不止一个字段时字段之间用半角逗号“，”隔开语法格式：SELECT

字段名1,字段名2,...,字段名nFROM

数据表名;单表查询SELECT【例】查询student表中学生的信息，包含studentid、studentname和sex三个字段SQL语句为：SELECTstudentid,studentname,sexFROMstudent;单表查询SELECT（2）查询所有的列如果查询数据表中所有字段的值，可以使用关键字“*”表示数据表中所有字段，而不用一一列举表中所有字段，字段顺序与数据表结构一致。语法格式：SELECT*FROM数据表名;单表查询SELECT【例】查询学生表student的数据记录SQL语句：SELECT*FROMstudent;单表查询SELECT（3）计算列值使用SELECT语句进行查询时，不仅可以查询数据表中字段的值，还可以通过表达式进行计算。单表查询SELECT【例】从学生表student中查询出学生的姓名和年龄SQL语句：SELECTstudentname,YEAR(CURDATE())-YEAR(birthday)FROMstudent;单表查询SELECT（4）为结果集中的列指定标题默认情况下，结果集显示的列标题就是查询字段的名称或表达式，当希望查询结果中显示的列使用自定义的列标题时，可使用AS子句更改结果集中的列标题，AS关键字也可以省略。SELECT

字段名[AS]别名FROM数据表名;单表查询SELECT【例】从学生表student中查询出学生的姓名和年龄，并将结果集中的列标题指定为“姓名”，“年龄”SQL语句：SELECTstudentnameAS

姓名,YEAR(CURDATE())-YEAR(birthday)年龄FROMstudent;单表查询SELECT为列指定别名操作时，必须注意：①当引用中文别名时，可以不加引号，但是不能使用全角引号，否则查询会出错。②当引用英文的别名超过两个单词时，则必须用引号将其引起来。单表查询DISTINCT当在MySQL中执行数据查询时，查询结果可能会包含重复行。如果需要去除这些重复行，可以在SELECT语句中使用关键字DISTINCT。语法格式：SELECTDISTINCT字段名FROM表名;单表查询DISTINCT【例】在寝室表dorm中查询寝室都有哪些朝向SQL语句：SELECT

DISTINCTtowardFROMdorm;单表查询LIMITLIMIT通常放在SELECT语句后面，用来指定查询结果从哪一条记录开始，一共查询多少条记录。使用LIMIT限制结果集返回的行数，语法格式：SELECT*|字段名[,字段名...]FROM

数据表名LIMIT[偏移量,]记录数;语法说明：偏移量指定查询结果从哪一行开始，如果省略偏移量（默认值为0），则查询从第一行开始。记录数指定查询结果中查询的记录的行数。单表查询LIMIT【例】在学生表student中查询前6行学生信息SQL语句：SELECT*FROMstudentLIMIT6;或SELECT*FROMstudentLIMIT0,6;单表查询LIMIT【例】在学生表student中查询从第5行开始的连续6行的学生信息，SQL语句：SELECT*FROMstudentLIMIT6,6;单表查询ORDERBY排序是指查询时仅按照一个指定字段进行升序或降序排序语法格式：SELECT[ALL|DISTINCT]*|字段名[,字段名...]FROM数据表名ORDERBY字段名[ASC|DESC];语法说明：ASC为升序，DESC为降序，省略的情况下默认为ASC。单表查询ORDERBY【例】在学生表student中查询班级编号为“200101”的班级学生的姓名、性别和生日，查询结果按生日升序排序SQL语句：SELECTstudentname,sex,birthdayFROMstudentWHEREclassid='200101'ORDERBYbirthday;单表查询WHERE在SELECT语句中可以WHERE子句指定查询条件语法格式：SELECT*|字段名[,字段名...]FROM数据表WHERE

条件表达式;单表查询WHERE使用比较运算符指定查询条件语法格式：WHERE

表达式1比较运算符表达式2单表查询WHERE【例】在寝室表dorm中查询寝室朝向为“南”的寝室信息SQL语句：SELECT*FROMdormWHEREtoward='南';单表查询WHERE【例】在寝室表dorm中查询寝室床位数超过4个的寝室信息SQL语句：SELECT*FROMdormWHERE

totalbeds>=4;单表查询WHERE使用逻辑运算符指定查询条件语法格式：WHERE[NOT]表达式1逻辑运算符表达式2[...逻辑运算符表达式n]单表查询WHERE【例】在学生表student中查询2002年4月出生的学生信息SQL语句：SELECT*FROM

student

WHERE

YEAR(birthday)=2002AND

MONTH(birthday)=4;单表查询WHERE【例】在学生表student中查询班级编号为“200101”和“200102”的两个班的学生信息SQL语句：SELECT*FROM

student

WHERE

classid='200101'ORclassid='200102';单表查询WHERE使用范围运算符指定查询条件语法格式：WHERE

表达式[NOT]BETWEEN

起始值AND

终止值单表查询WHERE【例】在寝室表dorm中查询寝室床位数在6到8之间的寝室信息SQL语句：SELECT*FROM

dorm

WHERE

totalbedsBETWEEN6AND8;或SELECT*FROMdormWHEREtotalbeds>=6ANDtotalbeds<=8;单表查询WHERE使用IN运算符可以确定表达式的取值是否属于某一值列表，当表达式取值与值列表中的任何一个值匹配时，即返回TRUE，否则返回FALSE。如果表达式不属于某一值列表，可使用NOTIN运算符。语法格式：WHERE

表达式[NOT]

IN

(值1,值2,...,值n)单表查询WHERE【例】在学生表student中查询班级编号为“200101”和“200102”的两个班的学生信息SQL语句：SELECT*FROM

student

WHERE

classidIN('200101','200102');单表查询WHERE在SELECT语句中，可以使用空值运算符判断字段值是否为空值。ISNULL可以查询某字段值为空的记录；ISNOTNULL与ISNULL相反，可以查询字段值不为空的记录。语法格式：WHERE

字段名IS[NOT]NULL单表查询WHERE【例】在学生表student中查询走读学生的信息SQL语句：SELECT*FROM

student

WHERE

classidISNULL;【例】在学生表student中查询住宿学生的信息SQL语句：SELECT*FROM

student

WHERE

classidISNOTNULL;单表查询WHERE

模糊查询是查找数据表中与用户输入关键字相近或相似的记录信息。模糊匹配运算符通常与通配符一起使用，由字符串和通配符组成的字符串必须用半角单引号（'）或双引号（"）引起来。单表查询WHERE使用模糊匹配运算符指定查询条件语法格式：WHERE

字段名[NOT]LIKE'字符串’;语法说明：（1）字符串一般是由普通字符和通配符组成，必须用半角单引号（'）或双引号（"）引起来。（2）通配符%(百分号)

代表零个或任意多个字符，_(下划线)代表任意单个字符。单表查询WHERE【例】在学生表student中查询所有姓李的学生信息SQL语句：SELECT*FROM

student

WHERE

studentnameLIKE'李%';【例】在学生表student中查询生源地不是沈阳市的学生信息SQL语句：SELECT*FROM

student

WHERE

addressNOT

LIKE'%沈阳市%';单表查询WHERE使用模糊匹配运算符指定查询条件语法格式：WHERE

字段名[NOT]LIKE'字符串’;语法说明：（1）字符串一般是由普通字符和通配符组成，必须用半角单引号（'）或双引号（"）引起来。（2）通配符%(百分号)

代表零个或任意多个字符，_(下划线)代表任意单个字符。单表查询GROUPBY

统计函数用于对一组数据值进行统计计算并返回单一值，又称为组合函数、聚合函数等。SELECT子句中可以使用统计函数进行计算，计算结果作为新列出现在查询结果集中。单表查询GROUPBY函数名

说明COUNT返回组中的项数MAX返回组中的最大值MIN返回组中的最小值SUM返回组中的所有值的和AVG返回组中的各值的平均值MySQL支持的常用统计函数及其作用单表查询GROUPBY1.COUNT()函数COUNT函数用于统计数据表中数据记录的行数，或者满足特定条件的数据记录的行数

COUNT(*)统计数据表中数据记录的行数，包括值为NULL的记录。

COUNT(字段名)统计数据表中某一字段的行数，忽略值为NULL的记录。单表查询GROUPBY【例】在学生表student中，统计在籍学生总人数及住宿学生总人数SQL语句：SELECT

COUNT(*)AS'在籍人数',COUNT(dormid)AS'住宿人数'FROMstudent;单表查询GROUPBY2.MAX函数和MIN函数MAX函数和MIN函数分别用于求表达式中所有值中的最大值与最小值。3.SUM函数和AVG函数SUM函数和AVG函数分别用于求表达式中所有值的总和与平均值。语法格式：MAX(表达式)MIN(表达式)SUM(表达式)AVG(表达式)单表查询GROUPBY【例】通过学生表student，统计寝室床位数的总数和每个寝室床位数的平均数，统计寝室床位数的最大值与最小值SQL语句：SELECT

MAX(totalbeds)AS'最大值',MIN(totalbeds)AS'最小值',SUM(totalbeds)AS'总床位数',AVG(totalbeds)AS'平均数'FROMdorm;单表查询GROUPBY如果需要按某字段数据值进行分组，在分组的基础上再进行统计计算，就要使用GROUPBY子句。语法格式：SELECT

字段名[,字段名...]FROM

数据表名GROUPBY字段名[HAVING

条件表达式]

;单表查询GROUPBY【例】在寝室表dorm中根据寝室的朝向进行分组SQL语句：SELECT*FROMdormGROUPBYtoward;单表查询GROUPBY【例】在寝室表dorm中统计每个朝向的寝室数量SQL语句：SELECTtoward,COUNT(dormid)FROMdormGROUPBYtoward;单表查询GROUPBY【例】在寝室表dorm中统计每个寝室楼的床位总数SQL语句：SELECTapartment,SUM(totalbeds)FROMdormGROUPBYapartment;单表查询GROUPBY使用GROUPBY子句进行分组统计时，为了能得到有意义的数据，SELECT子句要查询的字段只能是以下两种情况。（1）字段应用了统计函数。（2）未应用统计函数的字段必须包含在GROUPBY子句中。单表查询GROUPBY【例】在寝室表dorm中统计哪个朝向的寝室数量超过15SQL语句：SELECTtoward,COUNT(dormid)FROMdormGROUPBYtowardHAVINGCOUNT(dormid)>15;单表查询GROUPBYHAVING和WHERE的不同点:

HAVING子句针对结果集中的数据；WHERE子句针对的是数据表中的数据。

HAVING子句只过滤分组后的数据；WHERE子句在分组前对数据进行过滤。

HAVING子句位于GROUPBY子句之后；而WHERE子句位于GROUPBY子句之前。

HAVING可以与统计函数一起使用；而WHERE则不可以。多表连接查询JOIN多表连接查询是指查询同时涉及两个或两个以上的表。一、交叉连接（CROSSJOIN）二、内连接（INNERJOIN）三、外连接（OUTERJOIN）四、自连接（SELFJOIN）多表连接查询CROSSJOIN交叉连接（CROSSJOIN）查询结果集返回两个表的任何记录行的笛卡尔积，即查询结果集的列为各连接表的字段之和，记录行数为各连接表记录行数的乘积值。语法格式:SELECT*|字段名[,字段名...]FROM

数据表1,数据表2[,数据表3...];或SELECT*|字段名[,字段名...]FROM

数据表CROSSJOIN数据表2[CROSSJOIN数据表3...];多表连接查询CROSSJOIN【例】将班级表class与学生表student进行交叉连接SQL语句:SELECT*FROMclass,student;或SELECT*FROMclassCROSSJOINstudent;多表连接查询CROSSJOIN多表连接查询INNERJOIN内连接（INNERJOIN）是使用比较运算符指定连接条件，只返回满足连接条件的数据行，即在交叉连接生成的结果集中按照连接条件进行筛选后形成的结果。简单地说，就是利用条件表达式来消除交叉连接的某些数据行。语法格式：SELECT*|字段名[,字段名...]FROM

数据表名1,数据表名2WHERE

连接条件;或者SELECT*|字段名[,字段名...]FROM

数据表名1[INNER]JOIN数据表名2ON

连接条件;多表连接查询INNERJOIN语法格式：SELECT*|字段名[,字段名...]FROM

数据表名1[AS]别名1,数据表名2[AS]别名2WHERE

连接条件;或者SELECT*|字段名[,字段名...]FROM

数据表名1[AS]别名1[INNER]JOIN

数据表名2[AS]别名2ON

连接条件;多表连接查询INNERJOIN内连接的类型（1）等值连接：在连接条件中使用等号（=）比较运算符来比较连接字段的值，其查询结果中包含被连接表的所有字段，包括重复字段。（2）非等值连接：在连接条件中使用了除等号之外的比较运算符（>、<、>=、<=、!=）来比较连接字段的值。（3）自然连接：与等值连接相同，都是在连接条件中使用等号（=）比较运算符，但结果集中不包括重复字段。多表连接查询INNERJOIN【例】将班级表class与学生表student进行内连接SQL语句：SELECT*FROMclass,studentWHEREclass.classid=student.classid;或SELECT*FROMclassAScINNERJOINstudentASsONc.classid=s.classid;多表连接查询INNERJOIN【例】查询“20汽营1班”学生的姓名、电话及家庭住址SQL语句：SELECT

studentname,telephone,address

FROMclass,studentWHEREclass.classid=student.classidANDclassname='20汽营1班';或SELECT

studentname,telephone,address

FROMclassAScINNERJOINstudentASsONc.classid=s.classidWHEREclassname='20汽营1班';多表连接查询INNERJOIN【例】将班级表class与学生表student进行自然连接SQL语句：SELECT

student.*,classnameFROMclass,studentWHEREclass.classid=student.classid;或SELECT

student.*,classnameFROMclassAScINNERJOINstudentASsONc.classid=s.classid;多表连接查询OUTERJOIN通过外连接，在返回符合连接条件的记录的基础上，还可以返回某个数据表不符合条件的数据。外连接包括3种类型：左外连接、右外连接和全外连接。多表连接查询OUTERJOIN

左外连接，也可称为左连接。以左边数据表为基准去连接右边数据表，返回连接关键字（LEFTJOIN）左边数据表中所有的记录，以及右边数据表中符合连接条件的记录。

当左边数据表的某行记录在右边数据表中没有匹配的记录时，在连接结果中该行记录对应的右边数据表的字段均以NULL填充。语法格式：SELECT*|字段名[,字段名...]FROM数据表名1LEFT[OUTER]JOIN

数据表名2ON

连接条件;多表连接查询OUTERJOIN【例】将寝室表dorm和学生表student进行左外连接SQL语句：SELECT

*FROM

dormdLEFTOUTERJOINstudentsONd.dormid=s.dormid;多表连接查询OUTERJOIN【例】统计空闲寝室的数量SQL语句：SELECT

COUNT(*)

FROM

dormdLEFTOUTERJOINstudentsONd.dormid=s.dormidWHEREstudentnameISNULL;多表连接查询OUTERJOIN

右外连接，也可称为右连接。以右边数据表为基准去连接左边数据表，返回连接关键字（RIGHTJOIN）右边数据表（主表）中所有的记录，以及左边数据表（从表）中符合连接条件的记录。当右边数据表的某行记录在左边数据表中没有匹配的记录时，在连接结果中该行记录对应的左边数据表的字段均以NULL填充。语法格式：SELECT*|字段名[,字段名...]FROM数据表名1RIGHT[OUTER]JOIN

数据表名2ON

连接条件;多表连接查询OUTERJOIN【例】将寝室表dorm和学生表student进行右外连接SQL语句：SELECT*FROMdormdRIGHTOUTERJOINstudentsONd.dormid=s.dormid;多表连接查询OUTERJOIN

全外连接，也可称为完全连接，连接关键字为FULLJOIN。

全外连接得到的结果集除了包含符合连接条件的记录外，同时也包含不满足条件的两个数据表的记录，即包含两个表的全部记录。

当左边数据表的某行记录在右边数据表中没有匹配的记录时，在连接结果中该行记录对应的右边数据表的字段均以NULL填充。

当右边数据表的某行记录在左边数据表中没有匹配的记录时，在连接结果中该行记录对应的左边数据表的字段均以NULL填充。多表连接查询OUTERJOIN全外连接的语法格式:SELECT*|字段名[,字段名...]FROM

数据表名1FULL[OUTER]JOIN

数据表名2ON

连接条件;MySQL暂不支持全外连接，可以通过其他方法间接实现全外连接。多表连接查询OUTERJOIN自连接把一个数据表与它自身的副本进行连接。

自连接的连接操作可以利用别名的方法实现一个数据表自身的连接。

实际上，这种自身连接方法与两个数据表的连接操作完全相似，只是需要为数据表指定别名，字段名前也要加上数据表的别名进行限定。多表连接查询OUTERJOIN【例】查询和李旺在同一个班级的学生的姓名SQL语句：SELECTa.studentname,b.studentnameFROMstudentaJOINstudentbONa.classid=b.classidWHEREa.studentname='李旺'ANDb.studentname!='李旺';MySQL数据库4.3.2比较子查询、IN子查询任务<、<=、=、>、>=、!=、<>比较子查询12IN、NOTININ子查询子查询

子查询也被称为嵌套查询，就是SELECT查询时另一个查询的附属。子查询就是将一个查询语句嵌套在另一个查询语句中。外层的SELECT语句称为外部查询、父查询或主查询，内层的SELECT语句称为内部查询或子查询。子查询的执行过程可以描述为：首先执行子查询中的语句，并将返回的结果作为外层查询的筛选条件，然后再执行外层查询。子查询子查询通常与比较运算符、IN、ANY（SOME）、ALL以及EXISTS等关键字结合使用。比较子查询<、<=、=、>、>=、!=、<>比较子查询是指在父查询与子查询之间用比较运算符进行连接的查询。语法格式：WHERE表达式比较运算符{ALL|SOME|ANY}(子查询)语法说明：表达式为与子查询进行比较的表达式。比较运算符可以为<、<=、=、>、>=、!=、<>。

ALL、SOME和ANY操作符说明对比较运算的限制。比较子查询<、<=、=、>、>=、!=、<>【例】查询“王建国”同学所住寝室的信息SQL语句：SELECT*FROMdormWHEREdormid=(SELECTdormidFROMstudentWHEREstudentname='王建国');比较子查询<、<=、=、>、>=、!=、<>【例】在student表中查询年龄最大的学生所在班级SQL语句：SELECTclassnameFROMclassWHEREclassid=(SELECTclassidFROMstudentWHEREbirthday=(SELECTMIN(birthday)FROMstudent));比较子查询<、<=、=、>、>=、!=、<>【例】

在student表中查询比“200101”班任意一名学生年龄小的学习信息（查询结果不包括“200101”本班同学）SQL语句：SELECT*FROMstudentWHEREclassid<>'200101'ANDbirthday>ANY(SELECTbirthdayFROMstudentWHEREclassid='200101');比较子查询<、<=、=、>、>=、!=、<>【例】在student表中查询比“200101”班所有学生年龄都小的学习信息（查询结果不包括“200101”本班同学）SQL语句：SELECT*FROMstudentWHEREclassid<>'200101'ANDbirthday>ALL(SELECTbirthdayFROMstudentWHEREclassid='200101');IN子查询IN、NOTININ子查询是指父查询与子查询之间用IN或NOTIN进行连接并判断某个字段的值是否在子查询返回的结果集中。如果子查询的结果多于一个时再使用“=”比较会出错，可以IN运算符进行比较。语法格式：WHERE表达式[NOT]IN(子查询)其中，当表达式与子查询的结果集中的任何一个值相等时，条件表达式的值为TRUE，否则为FALSE；如果使用了NOT，则条件表达式的值正好相反。IN子查询IN、NOTIN【例】查询所有住在A座寝室楼的学生的信息SQL语句：SELECT*FROMstudentWHEREdormidIN(SELECTdormidFROMdormWHEREapartment='A’);本示例还可以使用ANY关键字SQL语句：SELECT*FROMstudentWHEREdormid=ANY(SELECTdormidFROMdormWHEREapartment='A');MySQL数据库4.3.3EXISTS子查询EXISTS子查询EXISTS、NOTEXISTSEXISTS子查询不需要返回任何实际数据，而只需要返回一个逻辑值（逻辑真值“TRUE”或逻辑假值“FALSE”）。也就是说，它的作用是在WHERE子句中测试子查询返回的行是否存在。如果存在则返回真值，否则返回假值。

EXISTS子查询EXISTS、NOTEXISTS【例】如果A座102寝室有同学入住，查询该寝室的朝向，如果该寝室空闲，则不查询。SQL语句：SELECTtowardFROMdormWHEREapartment='A'ANDroomno='102'ANDEXISTS(SELECT*FROMstudent,dormWHEREstudent.dormid=dorm.dormidANDapartment='A'ANDroomno='102')

EXISTS子查询EXISTS、NOTEXISTS子查询按逻辑进行分类，可分为相关子查询和非相关子查询。非相关子查询是子查询中仅仅使用了自己定义的数据源，非相关子查询是独立于父查询的子查询，子查询只执行一次，执行完毕后将值传递给父查询。相关子查询是子查询中使用了父查询的数据源，即子查询的查询条件依赖于父查询的某个属性值，此时父查询的执行与子查询的执行相互依赖。相关子查询中的子查询需要重复执行。EXISTS子查询EXISTS、NOTEXISTS相关子查询的执行过程：①子查询为父查询的每一个记录执行一次，父查询将子查询引用字段的值传给子查询。②如果子查询的任何记录与其匹配，则父查询取此记录放入结果集中。③回到①重复执行，直到处理完父表中的每一条记