数据库实验内容.docx
- 文档编号:23021184
- 上传时间:2023-04-30
- 格式:DOCX
- 页数:17
- 大小:19.99KB
数据库实验内容.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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 实验 内容