《第五章 MySQL复杂查询和触发器》由会员分享，可在线阅读，更多相关《第五章 MySQL复杂查询和触发器（40页珍藏版）》请在金锄头文库上搜索。
1、生物信息学数据库设计,第五章 MySQL复杂查询和触发器,多表查询及触发器,多表查询触发器,2,一 多表查询,索引主键维护表查询,1.1索引,索引的作用：提高搜索速度，减少查询时间。创建索引（键）方式：方式一create table 创建索引create table (字段1定义,字段n定义, index 索引名称 (字段1,字段2 ,), unique 索引名称 (字段1,字段2,) );方式二create uniqueindex添加索引create index 索引名 on 数据表 (字段1,字段2);create unique index 索引名 on 数据表 (字段1,字段2);=un
2、ique 选项不重,4,create table 方式索引例题,drop table if exists stud_info;create table stud_info ( id char(8) not null, name char(12) not null, sex char(2) default 男, birthday date not null default 1990-01-08, major char(6) not null default 生物信息学, index idx_major (major), unique idx_id (id);,5,create index 方式索
3、引例题,drop table if exists stud_info;create table stud_info ( id char(8) not null, name char(12) not null, sex char(2) default 男, birthday date not null default 1990-01-08, major char(6) not null default 生物信息学);create index idx_major on stud_info(major);create unique index idx_id on stud_info (id);,6,
4、1.2主键,主键字段值（键值）非空且不重，可以多字段组合主键，一个数据表中主键只能有一个。创建主键方式：方式一create table 创建索引create table (字段1定义,字段n定义, primary key 索引名称 (字段1,字段2 ,);添加主键方式：方式二alter table 添加索引alter table 数据表 add primary key 索引名 (字段1,字段2);,7,create table 方式创建主键例题,drop table if exists stud_info;create table stud_info ( id char(8) not null
5、, name char(12) not null, sex char(2) default 男, birthday date not null default 1990-01-08, major char(6) not null default 生物信息学, primary key pk_id (id);,8,alter table 方式添加主键例题,drop table if exists stud_info;create table stud_info ( id char(8) not null, name char(12) not null, sex char(2) default 男,
6、 birthday date not null default 1990-01-08, major char(6) not null default 生物信息学);alter table stud_info add primary key pk_id (id);,9,删除索引，主键,删除索引命令格式：drop index 索引名称 on 数据表名；删除主键命令格式：alter table 数据表名 drop primary key；,10,删除索引、主键例题,删除索引：drop index 索引名称 on 数据表名；drop index idx_major on stud_info;删除主键：
7、alter table 数据表名 drop primary key；alter table stud_info drop primary key;,11,1.3维护MySQL数据表,显示数据表结构。命令格式：describe ；例题：显示stud_info数据表的结构。mysqldescribe stud_info ；,12,修改数据表结构。修改、增加、删除字段名称、字段类型修改字段命令格式：alter table 数据表名 change 原字段名 新字段名 ；修改字段名例题：alter table stud_info change sex stu_sex char(2);修改字段类型例题：a
8、lter table stud_info change name name char(20);,13,修改数据表结构。修改、增加、删除字段名称、字段类型增加字段命令格式：alter table 数据表名 add 字段名 字段类型；增加字段例题：alter table stud_info add mobil char (12);,14,修改数据表结构。修改、增加、删除字段名称、字段类型删除字段命令格式：alter table 数据表名 drop 字段名；删除字段例题：alter table stud_info drop mobil;,15,5.删除数据表。命令格式：drop table if e
9、xists 数据表名；直接删除数据表例题：drop table stud_info; 删除stud_info数据表如果数据表存在就删除数据表：drop table if exists stud_info; 如果stud_info数据表存在就删除stud_info数据表,16,6.更换数据表名。命令格式：rename table 数据表名 to 新数据表名；更换数据表名例题：rename table stud_info to stud_infomation; 将数据表名为stud_info的更换为stud_information,17,1.4维护数据表记录,1.增加记录命令格式：insert i
10、nto 数据表名 (字段名1, 字段名n) values (数值1，数值n) ；字段i顺序任意，字段i与数值i一一对应。,18,2.删除记录命令格式1：删除数据表中全部记录。delete from 数据表名；删除数据表中全部记录例题：delete from stud_information ；命令格式2：删除数据表中符合条件的记录。delete from 数据表名 where 条件表达式删除数据表中符合条件的记录例题：delete from stud_information where id= 200801001;,19,3.修改记录命令格式1：修改数据表中全部记录指定字段的值update s
11、et , 字段名n=数值n字段i顺序任意，字段i与数值i一一对应，同时修改多个字段时set只使用一个。修改全部记录指定字段值例题：update stud_information set sex=男;,20,3.修改记录命令格式2：修改数据表中符合条件的记录指定字段的值update set , 字段名n=数值n where 修改学号为200801001的出生日期为1991-1-1，专业为临床医学例题：update stud_info set birthday=1991-1-1,major=临床医学 where id=200801001;,21,1.5选取数据表记录,1.选取数据表数据表达式结果命
12、令格式命令格式2：选取数据表中指定字段，指定记录的结果，并对输出结果进行重组。select from where order by 字段名 asc | desc having (arithematic function condition)group by 字段名表：显示获取结果from数据表名表：数据源where条件表达式：获取数据条件order by 字段名：按字段进行升序asc或降序desc排序group by 字段名：按字段进行分组,22,记录的导入 LOAD DATA LOCAL INFILE f:/1.txt INTO TABLE stu_grade;,选取输出一个数据表的全部记录
13、按照指定字段进行分组，并计算每一个分组的记录数count()。例题：distinct选项select distinct b.name from stu_grade a,stu_info b where a.id=b.id;select sex,count(sex) from stu_info group by sex;显示stu_grade数据表的学生课程数目。select id,count(subject_id) from stu_grade group by id;显示学生的总成绩 select id,sum(score) from stu_info group by id;,24,显示姓
14、名select b.name,sum(a.score) from stu_grade a,stu_info b where a.id=b.id group by a.id;select b.name as 姓名,sum(a.score) as 总成绩 from stu_grade a,stu_info b where a.id=b.id group by a.id;,排序select b.name as 姓名,sum(a.score) as 总成绩 from stu_grade a,stu_info b where a.id=b.id group by a.id order by 总成绩 de
15、sc;,having的使用 select b.name as 姓名,sum(a.score) as 总成绩 from stu_grade a,stud_info b where a.id=b.id group by a.id having sum(a.score) 580 order by 总成绩 desc;,数值为空的处理 ifnull(table1.score,0) select b.name as 姓名,sum(ifnull(a.score),0) as 总成绩 from stu_grade a,stud_info b where a.id=b.id group by a.id having sum(a.score) 580 order by 总成绩 desc;,28,比较表达式：not like例题：通配符%任意符号select 张华 like 张%，张华 like %张%，张华 like %张 ; 1 1 0通配符_1个字符select 张华 like 张_，张华 like 张_，张华 like _张 ; 1 0 0not选项是类似结果取反select 张华 not like 张%;,
