数据库系统概念英文第六版所有的建表语句和插入的数据.docx
- 文档编号:4918329
- 上传时间:2022-12-11
- 格式:DOCX
- 页数:15
- 大小:17.76KB
数据库系统概念英文第六版所有的建表语句和插入的数据.docx
《数据库系统概念英文第六版所有的建表语句和插入的数据.docx》由会员分享,可在线阅读,更多相关《数据库系统概念英文第六版所有的建表语句和插入的数据.docx(15页珍藏版)》请在冰豆网上搜索。
数据库系统概念英文第六版所有的建表语句和插入的数据
CREATEDATABASEIFNOTEXISTS`university`/*!
40100DEFAULTCHARACTERSETgbk*/;
USE`university`;
--MySQLdump10.13Distrib5.6.13,forWin32(x86)
--
--Host:
localhostDatabase:
university
--------------------------------------------------------
--Serverversion5.7.4-m14
/*!
40101SET@OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT*/;
/*!
40101SET@OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS*/;
/*!
40101SET@OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION*/;
/*!
40101SETNAMESutf8*/;
/*!
40103SET@OLD_TIME_ZONE=@@TIME_ZONE*/;
/*!
40103SETTIME_ZONE='+00:
00'*/;
/*!
40014SET@OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS,UNIQUE_CHECKS=0*/;
/*!
40014SET@OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,FOREIGN_KEY_CHECKS=0*/;
/*!
40101SET@OLD_SQL_MODE=@@SQL_MODE,SQL_MODE='NO_AUTO_VALUE_ON_ZERO'*/;
/*!
40111SET@OLD_SQL_NOTES=@@SQL_NOTES,SQL_NOTES=0*/;
--
--Tablestructurefortable`advisor`
--
DROPTABLEIFEXISTS`advisor`;
/*!
40101SET@saved_cs_client=@@character_set_client*/;
/*!
40101SETcharacter_set_client=utf8*/;
CREATETABLE`advisor`(
`s_ID`varchar(5)NOTNULL,
`i_ID`varchar(5)DEFAULTNULL,
PRIMARYKEY(`s_ID`),
KEY`i_ID`(`i_ID`),
CONSTRAINT`advisor_ibfk_1`FOREIGNKEY(`i_ID`)REFERENCES`instructor`(`ID`)ONDELETESETNULL,
CONSTRAINT`advisor_ibfk_2`FOREIGNKEY(`s_ID`)REFERENCES`student`(`ID`)ONDELETECASCADE
)ENGINE=InnoDBDEFAULTCHARSET=gbk;
/*!
40101SETcharacter_set_client=@saved_cs_client*/;
--
--Dumpingdatafortable`advisor`
--
LOCKTABLES`advisor`WRITE;
/*!
40000ALTERTABLE`advisor`DISABLEKEYS*/;
INSERTINTO`advisor`VALUES('12345','10101'),('44553','22222'),('45678','22222'),('76543','45565'),('23121','76543'),('98988','76766'),('76653','98345'),('98765','98345');
/*!
40000ALTERTABLE`advisor`ENABLEKEYS*/;
UNLOCKTABLES;
--
--Tablestructurefortable`classroom`
--
DROPTABLEIFEXISTS`classroom`;
/*!
40101SET@saved_cs_client=@@character_set_client*/;
/*!
40101SETcharacter_set_client=utf8*/;
CREATETABLE`classroom`(
`building`varchar(15)NOTNULL,
`room_number`varchar(7)NOTNULL,
`capacity`decimal(4,0)DEFAULTNULL,
PRIMARYKEY(`building`,`room_number`)
)ENGINE=InnoDBDEFAULTCHARSET=gbk;
/*!
40101SETcharacter_set_client=@saved_cs_client*/;
--
--Dumpingdatafortable`classroom`
--
LOCKTABLES`classroom`WRITE;
/*!
40000ALTERTABLE`classroom`DISABLEKEYS*/;
INSERTINTO`classroom`VALUES('Packard','101',500),('Painter','514',10),('Taylor','3128',70),('Watson','100',30),('Watson','120',50);
/*!
40000ALTERTABLE`classroom`ENABLEKEYS*/;
UNLOCKTABLES;
--
--Tablestructurefortable`course`
--
DROPTABLEIFEXISTS`course`;
/*!
40101SET@saved_cs_client=@@character_set_client*/;
/*!
40101SETcharacter_set_client=utf8*/;
CREATETABLE`course`(
`course_id`varchar(7)NOTNULL,
`title`varchar(50)DEFAULTNULL,
`dept_name`varchar(20)DEFAULTNULL,
`credits`decimal(2,0)DEFAULTNULL,
PRIMARYKEY(`course_id`),
KEY`dept_name`(`dept_name`),
CONSTRAINT`course_ibfk_2`FOREIGNKEY(`dept_name`)REFERENCES`department`(`dept_name`)ONDELETESETNULL
)ENGINE=InnoDBDEFAULTCHARSET=gbk;
/*!
40101SETcharacter_set_client=@saved_cs_client*/;
--
--Dumpingdatafortable`course`
--
LOCKTABLES`course`WRITE;
/*!
40000ALTERTABLE`course`DISABLEKEYS*/;
INSERTINTO`course`VALUES('BIO-101','Intro.toBiology','Biology',4),('BIO-301','Genetics','Biology',4),('BIO-399','ComputationalBiology','Biology',3),('CS-101','Intro.toComputerScience','Comp.Sci',4),('CS-190','GameDesign','Comp.Sci',4),('CS-315','Robotics','Comp.Sci',3),('CS-319','ImageProcessing','Comp.Sci',3),('CS-347','DatabaseSystemConcepts','Comp.Sci',3),('EE-181','Intro.toDigitalSystems','Elec.Eng',3),('FIN-201','InvestmentBanking','Finance',3),('HIS-351','WorldHisyory','History',3),('MU-199','MusicVideoProduction','Music',3),('PHY-101','PhysicalPrinciples','Physics',4);
/*!
40000ALTERTABLE`course`ENABLEKEYS*/;
UNLOCKTABLES;
/*!
50003SET@saved_cs_client=@@character_set_client*/;
/*!
50003SET@saved_cs_results=@@character_set_results*/;
/*!
50003SET@saved_col_connection=@@collation_connection*/;
/*!
50003SETcharacter_set_client=utf8*/;
/*!
50003SETcharacter_set_results=utf8*/;
/*!
50003SETcollation_connection=utf8_general_ci*/;
/*!
50003SET@saved_sql_mode=@@sql_mode*/;
/*!
50003SETsql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/;
DELIMITER;;
/*!
50003CREATE*//*!
50017DEFINER=`root`@`localhost`*//*!
50003triggerdelete_course
beforedeleteoncourse
foreachrow
begin
if(old.course_idin(selectcourse_idfromtakes))
then
insertintocourse(course_id,title,dept_name,credits)
values(null,null,null,null);
endif;
end*/;;
DELIMITER;
/*!
50003SETsql_mode=@saved_sql_mode*/;
/*!
50003SETcharacter_set_client=@saved_cs_client*/;
/*!
50003SETcharacter_set_results=@saved_cs_results*/;
/*!
50003SETcollation_connection=@saved_col_connection*/;
/*!
50003SET@saved_cs_client=@@character_set_client*/;
/*!
50003SET@saved_cs_results=@@character_set_results*/;
/*!
50003SET@saved_col_connection=@@collation_connection*/;
/*!
50003SETcharacter_set_client=utf8*/;
/*!
50003SETcharacter_set_results=utf8*/;
/*!
50003SETcollation_connection=utf8_general_ci*/;
/*!
50003SET@saved_sql_mode=@@sql_mode*/;
/*!
50003SETsql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/;
DELIMITER;;
/*!
50003CREATE*//*!
50017DEFINER=`root`@`localhost`*//*!
50003triggerdelete_courses
beforedeleteoncourse
foreachrow
begin
if(old.course_idin(selectcourse_idfromtakes))
then
insertintoinstructor(ID,name,dept_name,salary)
values('10101','null','null','null');
endif;
end*/;;
DELIMITER;
/*!
50003SETsql_mode=@saved_sql_mode*/;
/*!
50003SETcharacter_set_client=@saved_cs_client*/;
/*!
50003SETcharacter_set_results=@saved_cs_results*/;
/*!
50003SETcollation_connection=@saved_col_connection*/;
--
--Tablestructurefortable`department`
--
DROPTABLEIFEXISTS`department`;
/*!
40101SET@saved_cs_client=@@character_set_client*/;
/*!
40101SETcharacter_set_client=utf8*/;
CREATETABLE`department`(
`dept_name`varchar(20)NOTNULL,
`building`varchar(15)DEFAULTNULL,
`budget`decimal(12,2)DEFAULTNULL,
PRIMARYKEY(`dept_name`)
)ENGINE=InnoDBDEFAULTCHARSET=gbk;
/*!
40101SETcharacter_set_client=@saved_cs_client*/;
--
--Dumpingdatafortable`department`
--
LOCKTABLES`department`WRITE;
/*!
40000ALTERTABLE`department`DISABLEKEYS*/;
INSERTINTO`department`VALUES('Biology','Watson',90000.00),('Comp.Sci','Taylor',100000.00),('Elec.Eng','Taylor',85000.00),('Finance','Painter',120000.00),('History','Painter',50000.00),('Music','Packard',80000.00),('Physics','Watson',70000.00);
/*!
40000ALTERTABLE`department`ENABLEKEYS*/;
UNLOCKTABLES;
--
--Tablestructurefortable`instructor`
--
DROPTABLEIFEXISTS`instructor`;
/*!
40101SET@saved_cs_client=@@character_set_client*/;
/*!
40101SETcharacter_set_client=utf8*/;
CREATETABLE`instructor`(
`ID`varchar(5)NOTNULL,
`name`varchar(20)NOTNULL,
`dept_name`varchar(20)DEFAULTNULL,
`salary`decimal(8,2)DEFAULTNULL,
PRIMARYKEY(`ID`),
KEY`dept_name`(`dept_name`),
CONSTRAINT`instructor_ibfk_2`FOREIGNKEY(`dept_name`)REFERENCES`department`(`dept_name`)ONDELETESETNULL
)ENGINE=InnoDBDEFAULTCHARSET=gbk;
/*!
40101SETcharacter_set_client=@saved_cs_client*/;
--
--Dumpingdatafortable`instructor`
--
LOCKTABLES`instructor`WRITE;
/*!
40000ALTERTABLE`instructor`DISABLEKEYS*/;
INSERTINTO`instructor`VALUES('10101','Srinivasan','Comp.Sci',65000.00),('121','aa','Comp.Sci',12300.00),('12111','aa','Music',12300.00),('12121','Wu','Finance',90000.00),('15151','Mozart','Music',40000.00),('22222','Einstein','Physics',95000.00),('32343','ElSaid','History',60000.00),('33456','Gold','Physics',87000.00),('45565','Katz','Comp.Sci',75000.00),('58583','Califieri','History',62000.00),('76543','Singh','Finance',80000.00),('76766','Crick','Biology',72000.00),('83821','Brandt','Comp.Sci',92000.00),('98345','Kim','Elec.Eng',80000.00);
/*!
40000ALTERTABLE`instructor`ENABLEKEYS*/;
UNLOCKTABLES;
/*!
50003SET@saved_cs_client=@@character_set_client*/;
/*!
50003SET@saved_cs_results=@@character_set_results*/;
/*!
50003SET@saved_col_connection=@@collation_connection*/;
/*!
50003SETcharacter_set_client=utf8*/;
/*!
50003SETcharacter_set_results=utf8*/;
/*!
50003SETcollation_connection=utf8_general_ci*/;
/*!
50003SET@saved_sql_mode=@@sql_mode*/;
/*!
50003SETsql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/;
DELIMITER;;
/*!
50003CREATE*//*!
50017DEFINER=`root`@`localhost`*//*!
50003triggerinsert_instructorinformation
beforeinsertoninstructor
foreachrow
begin
if(new.dept_namenotin(selectdept_namefromdepartment))
thensetnew.ID=null;
endif;
end*/;;
DELIMITER;
/*!
50003SETsql_mode=@saved_sql_mode*/;
/*!
50003SETcharacter_set_client=@saved_cs_client*/;
/*!
50003SETcharacter_set_results=@saved_cs_results*/;
/*!
50003SETcollation_connection=@saved_col_connection*/;
--
--Tablestructurefortable`prereq`
--
DROPTABLEIFEXISTS`prereq`;
/*!
40101SET@saved_cs_client=@@character_set_client*/;
/*!
40101SETcharacter_set_client=utf8*/;
CREATETABLE`prereq`(
`course_id`varchar(8)NOTNULL,
`prereq_id`varchar(8)NOTNULL,
PRIMARYKEY(`course_id`,`prereq_id`),
KEY`prereq_id`(`prereq_id`),
CONSTRAINT`prereq_ibfk_1`FOREIGNKEY(`course_id`)REFERENCES`course`(`course_id`)ONDELETECASCADE,
CONSTRAINT`prereq_ibfk_2`FOREIGNKEY(`
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 系统 概念 英文 第六 所有 表语 插入 数据