数据库实验内容.docx
《数据库实验内容.docx》由会员分享，可在线阅读，更多相关《数据库实验内容.docx（17页珍藏版）》请在冰点文库上搜索。
数据库实验内容
建立数据表
1.把sql文件复制到c盘下。
2.在mysql中运行sourcec:
/*.sql;
加载数据
1.把txt数据文件复制到C盘下。
2.在mysql中运行loaddatalocalinfile‘*.txt’intotablename;
一、获得关于数据库和数据表的信息
列出服务器管理着的数据库
Showdatabases;
列出当前数据库或指定数据库的数据表
Showtables;
Showtablesfromdb_name;
显示关于数据表中的数据列或索引的信息：
Showcolumnsfromtb_name;
Showindexfromtb_name;
显示关于当前数据库或者指定数据库中的数据表的描述信息：
Showtablestasus;
Showtalbestatusfromdb_name;
显示与数据表的当前对应的createtable语句：
Showcreatetabletb_name;
二、模式的选定、创建和删除：
1.创建数据库
createdatabasesampledb;
2.选定数据库：
usedbname;
3.删除数据库
dropdatabasedbname;
三、创建数据表、输入数据和删除数据表
1.使用的数据表的创建
数据表t1数据表t2
i1c1i2c2
1a2c
2b3b
3c4a
#创建表t1并输入数据
Createtablet1
（
i1char
（1）,
c1smallintunsigned
）
insertintot1values（1,’a’）,（2,’b’）,（3,’c’）;
#创建表t2并输入数据
createtablet2
（
i2char
（1）,
c2smallintunsigned
）;
insertintot2values（2,’c’）,（3,’b’）,（4,’a’）;
#创建表student并输入数据
CREATETABLEstudent
（
nameVARCHAR（20）NOTNULL,
sexENUM（'F','M'）NOTNULL,
student_idINTUNSIGNEDNOTNULLAUTO_INCREMENT,
PRIMARYKEY（student_id）
）;
LOADDATALOCALINFILE"student.txt"INTOTABLEstudent;
#创建表absence并输入数据
CREATETABLEabsence
（
student_idINTUNSIGNEDNOTNULL,
dateDATENOTNULL,
PRIMARYKEY（student_id,date）
）;
LOADDATALOCALINFILE"absence.txt"INTOTABLEabsence;
#创建表event并输入数据
CREATETABLEevent
（
dateDATENOTNULL,
typeENUM（'T','Q'）NOTNULL,
event_idINTUNSIGNEDNOTNULLAUTO_INCREMENT,
PRIMARYKEY（event_id）
）;
LOADDATALOCALINFILE"event.txt"INTOTABLEevent;
#创建表score并输入数据
CREATETABLEscore
（
student_idINTUNSIGNEDNOTNULL,
event_idINTUNSIGNEDNOTNULL,
PRIMARYKEY（event_id,student_id）,
scoreINTNOTNULL
）;
LOADDATALOCALINFILE"score.txt"INTOTABLEscore;
#创建表president并输入数据
CREATETABLEpresident
（
last_nameVARCHAR（15）NOTNULL,
first_nameVARCHAR（15）NOTNULL,
suffixVARCHAR（5）NULL,
cityVARCHAR（20）NOTNULL,
stateVARCHAR
（2）NOTNULL,
birthDATENOTNULL,
deathDATENULL
）;
LOADDATALOCALINFILE"president.txt"INTOTABLEpresident;
#创建表member并输入数据
CREATETABLEmember
（
member_idINTUNSIGNEDNOTNULLAUTO_INCREMENT,
PRIMARYKEY（member_id）,
last_nameVARCHAR（20）NOTNULL,
first_nameVARCHAR（20）NOTNULL,
suffixVARCHAR（5）NULL,
expirationDATENULLDEFAULT'0000-00-00',
emailVARCHAR（100）NULL,
streetVARCHAR（50）NULL,
cityVARCHAR（50）NULL,
stateVARCHAR
（2）NULL,
zipVARCHAR（10）NULL,
phoneVARCHAR（20）NULL,
interestsVARCHAR（255）NULL
）;
LOADDATALOCALINFILE"member.txt"INTOTABLEmember;
2.从已有数据表中创建：
Createtablestudent_fselect*fromstudentwheresex=’f’;
3.删除数据表：
droptabletb_name;
四、索引的添加和删除
1.使用altertable添加索引（普通索引、unique（唯一）、primarykey、fulltext（全文））：
altertabletb_nameaddindexindex_name（index_columns）;
altertabletb_nameadduniqueindex_name（index_columns）;
altertabletb_nameaddprimarykey（index_columns）;
altertabletb_nameaddfulltext（index_columns）;
2.使用createindex来创建索引（普通、唯一和全文，不能创建主键）：
createindexindex_nameontb_name（index_columns）;
createuniqueindexindex_nameontb_name（index_columns）;
createfulltextindexindex_nameontb_name（index_columns）;
3.创建新表时添加索引：
createtabletb_name
{
columnsdeclarations…
indexindex_name（index_columns）;
uniqueindex_name（index_columns）;
primarykey（index_columns）;
fulltextindex_name（index_columns）;
}
也可以在列定义末尾添加primarykey和unique来指定主键和唯一索引
createtable
{
iintnotnullprimarykey;
jchar（10）notnullunique;
}
4.删除索引：
dropindexindex_nameontb_name;
altertabletb_namedropindexindex_name;
altertabletb_namedropprimarykey;
五、修改数据表
1.重命名数据表：
altertabletb_namerenametonew_tb_name;
renametableold_nametonew_name;
rename可以在给多个数据表重命名，alter不可以
renametablet1tot2,t2tot3;
2.把一个数据表由一个数据库移动到另一个数据库：
Altertabledb_name.tbnamerenametonew_db.tb_name;
renamedb_name.tb_nametonew_db.tb_name;
3.改变数据列的类型
把col_name由smallintunsigned改为mediumintunsigned。
Altertabletb_namemodifycol_namemediumintunsigned;
Altertabletb_namechangcol_namecol_newnamemediumintunsigned;
六、检索条件
select*fromscorewherescore>95;
selectlast_name,first_namefrompresidentwherelast_name='roosevelt';
日期查找
selectlast_name,first_name,birthfrompresidentwherebirth<'1750-1-1'and（state='va'orstate='ma'）;
七、NULL值的处理
NULL表示没有数据，isnull表示没有数据，isnotnull表示有数据。
查找没有后缀名的美国总统
selectlast_name,first_name,suffixfrompresidentwheresuffixisnotnull;
查找在世的美国总统
selectlast_name,first_namefrompresidentwheredeathisnull;
八、对查询结果进行排序
使用orderby
按姓氏排序美国总统
selectlast_name,first_name,birthfrompresidentorderbylast_name;
按姓氏升序或降序排序美国总统（ascdesc）
selectlast_name,first_name,birthfrompresidentorderbylast_namedesc;
先按州逆序排序再按姓氏升序排序美国总统
selectlast_name,first_name,statefrompresidentorderbystatedesc,last_nameasc;
九、限制查询结果中的数据行个数
使用limit
查询出生日期前五（后五）的总统
selectlast_name,first_name,birthfrompresidentorderbybirthlimit5;
selectlast_name,first_name,birthfrompresidentorderbybirthdesclimit5;
查询第10条记录后的5条记录
selectlast_name,first_name,birthfrompresidentorderbybirthlimit10,5;
随机查询一条记录
十、对输出进行求值和命名
使用as添加别名
selectconcat（first_name,’‘,last_name）,concat（city,’,’,state）frompresident;
selectconcat（first_name,'',last_name）asName,concat（city,',',state）asBirthplacefrompresident;
别名含有空格时必需放在引号中
selectconcat（first_name,'',last_name）as'PresidentName',concat（city,',',state）as'PlaceofBirth'frompresident;
十一、与日期有关的问题
日期中的年、月、日可以用year（）,month（）,dayofmonth（）分离出来，monthname（）取得月份的名称,curdate（）取得当前的日期。
selectlast_name,first_name,birthfrompresidentwheremonth（birth）=3;
selectlast_name,first_name,birthfrompresidentwheremonthname（birth）=’march’;
选择今天出生的美国总统
selectlast_name,first_name,birthfrompresident
wheremonth（birth）=month（curdate（））anddayofmonth（birth）=dayofmonth（curdate（））;
十二、模式匹配
%通配任何字符，_通配一个字符
选择以字母W开头的总统姓名
selectlast_name,first_namefrompresidentwherelast_namelike‘w%’;
选择姓氏中含有w的总统
selectlast_name,first_namefrompresidentwherelast_namelike‘%w%’;
查询姓氏仅由四个字母组成的总统
selectlast_name,first_namefrompresidentwherelast_namelike‘____’;
十三、使用和设置SQL变量
赋值：
@varname:
=value或set@varname:
=value;
select@birth:
=birthfrompresidentwherelast_name=’Jackson’andfirst_name=’Andrew’;
selectlast_name,first_name,birthfrompresident
wherebirth<@birthorderbybirth;
set@one_week_ago=date_sub（curdate（）,interval7day）;
selectcurdate（）,@one_week_ago;
十四、生成统计信息：
1.使用distinct清除掉重复的行，查询历届总统的出生地
selectdistinctstatefrompresidentorderbystate;
2.使用count（）统计相关记录的条数
查询会员总数
selectcount（*）frommember;
查询类型为Q的考试的次数
selectcount（*）fromeventwheretype=’Q’;
统计捅有email和非终身会员的人数（其过期日期为非空值）
selectcount（*）,count（email）,count（expiration）frommember;
查询美国有多少个州出过总统
selectcount（distinctstate）frompresident;
3.使用group分类统计
统计男生和女生的人数
selectcount（*）fromstudentgroupbysex;
统计出生于美国种州的总统人数
selectstate,count（*）frompresidentgroupbystate;
按总统多少的顺序把美国各州排序
selectstate,count（*）ascountfrompresidentgroupbystateorderbycountdesc;
统计不同月份出生的美国总统数
selectmonth（birth）asmonth,monthname（birth）asname,count（*）ascount
frompresidentgroupbynameorderbymonth;
统计出生总统最多的前4个州
selectstate,count（*）ascountfrompresident
groupbystateorderbycountdesclimit4;
4.having与where一样用于设置查询条件，但having中允许出现统计函数
查询美国哪些州出了2位及以上的总统
selectstate,count（*）ascountfrompresidentgroupbystate
havingcount>1orderbycountdesc;
5.统计函数使用
查询考试信息
selectevent_id,
min（score）asminimum,
max（score）asmaximum,
（max（score）-min（score）+1）asran,
sum（score）astotal,
avg（score）asaverage,
count（score）ascount
fromscore
groupbyevent_id;
十五、从多个数据表检索信息
select语句的基本语法如下：
selectselection_col_list
fromtable_list
whereprimary_constraint
groupbygrouping_columns
orderbysorting_columns
havingsecondary_constraint
limitcount;
1.单连接（只从一个数据表中选择数据）
Select*fromt1;
2.全连接（笛卡尔积）
Selectt1.*,t2.*fromt1,t2;
3.等值连接：
Selectt1.*,t2.*fromt1,t2wheret1.i1=t2.i2;
4.左连接和右连接
Selectt1.*,t2.*fromt1leftjoint2ont1.i1=t2.i2;
左连接适合找出左边数据表里的，在右边数据表没有匹配物的数据行。
我们只关心右边有数据而左边没有数据的行。
Selectt1.*,t2.*fromt1leftjoint2ont1.i1=t2.i2wheret2.i2isnull;
在student,event,score三个表中查询缺考学生的补考信息：
selectstudent.name,student.student_id,event.date,event.event_id,event.type
fromstudent,eventleftjoinscoreonstudent.student_id=score.student_id
andscore.event_id=event.event_id
wherescore.scoreisnull
orderbystudent.student_id,event.event_id;
5.使用子选择：
select*fromscore
whereevent_idin（selectevent_idfromeventwheretype='t'）;
6.用子选择来生成一个参考值：
select*fromscore
whereevent_id=
（selectevent_idfromeventwheredate='2002-09-23'andtype='Q'）;
选择出生最早的美国总统
select*frompresident
wherebirth=（selectmin（birth）frompresident）;
7.exists和notexists
用来把外层查询检索到的数据值传递给内部查询，看它们是否满足内层查询给出的匹配条件。
selecti1fromt1
whereexists（select*fromt2wheret1.i1=t2.i2）;
selecti1fromt1
wherenotexists（select*fromt2wheret1.i1=t2.i2）;
8.in和notin
内层select语句应该返回且只返回一个数据列，这个数据列里的值将由外层select语句中的比较操作进行求值。
selecti1fromt1wherei1in（selecti2fromt2）;
selecti1fromt1wherei1notin（selecti2fromt2）;
9.union查询
从多个数据表选取记录创建记录集。
createtableta1（iint,cvarchar（10））;
insertintota1values（1,'red'）,（2,'blue'）,（3,'green'）;
createtableta2（iint,cvarchar（10））;
insertintota1values（-1,'tan'）,（2,'red'）;
createtableta3（ddate,iint）;
insertintota3values（'1904-01-01',100）,（'2004-01-01',200）,（'2004-01-01',200）;
例1：
selectifromta1unionselectifromta2unionselectifromta3;
特点：
输出更列的名字和数据类型将由union查询中第一个select所选取的数据列的名字和数据类型决定。
selecti,cfromt1unionselecti,dfromt3;
10.检索给定日期的考试分数
selectstudent_id,date,score,type
fromevent,score
wheredate=’2002-09-23’andevent.event_id=score.event_id;
也可更为清晰的写为：
selectscore.stud
升级会员 