create database Liber;use Liber;#顯示數据庫 20150210 Geovin Du 涂聚文SHOW DATABASES;drop table BookKindList;#书目录create table BookKindList(	BookKindID INT NOT NULL AUTO_INCREMENT, #自动增加	BookKindName nvarchar(500) not null,	BookKindParent int null,   PRIMARY KEY(BookKindID)  #主键);#这样也可以create table BookKindList(	BookKindID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, #自动增加#主键	BookKindName nvarchar(500) not null,	BookKindParent int null    );#书位置create table BookPlaceList(	BookPlaceID INT NOT NULL  PRIMARY KEY AUTO_INCREMENT,	BookPlaceName nvarchar(500) not null,		BookPlaceParent int null	);#书系列Series或套名称（一本的0.无，有分上下本）drop table BookSeriesList;create table BookSeriesList(	BookSeriesID INT NOT NULL  PRIMARY KEY AUTO_INCREMENT,	BookSeriesName nvarchar(500) not null);#職位Positioncreate table PositionList(	PositionID INT NOT NULL  PRIMARY KEY AUTO_INCREMENT,	PositionName nvarchar(500) not null);#部門Department  ShortPYcreate table DepartmentList(	DepartmentID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,	DepartmentName nvarchar(500) not null);insert into DepartmentList(DepartmentName) values ('行政部');insert into DepartmentList(DepartmentName) values ('资讯部');select * from DepartmentList;#語种 Languagecreate table LanguageList(	LanguageID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,	LanguageName nvarchar(500) not null);#出版社Press #拼音索引create table PressList(	PressID INT  NOT NULL PRIMARY KEY AUTO_INCREMENT,	PressName nvarchar(500) not null	);#作家Authorcreate table AuthorList(	AuthorID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,	AuthorName nvarchar(500) not null);#BookStatus 书藉存在状态（1，在用，2，报废，3。转移）create table BookStatusList(	BookStatusID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,	BookStatusName nvarchar(500) not null);#借阅状态：借出，续借，归还，预借Lend, Renewal, Restitution,Reservationscreate table LendStatusList(	LendStatusID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,	LendStatusName nvarchar(500) not null);#书信息create table BookInfoList(	BookInfoID  INT NOT NULL PRIMARY KEY AUTO_INCREMENT,#自动增加#主键	BookInfoKind int not null,  					                #书籍类型外键	BookInfoPlace int not null,						            #放置位置外键  BookInfoISBN varchar(50) not null,				#书籍ISBN编码	BookInfoBarCode varchar(60) not null,			#管理条码(barcode) 	BookInfoName nvarchar(500) not null,		    #书名	BookInfoSeries	int default 1,		            #书系列 ,0為無係列	BookInfoAuthor int null,	 					        #作者	BookInfoPress int null,							        #出版社	BookInfoLanguage int null,						        #语种	BookInfoPublish datetime ,	                #出版时间	BookInfoImage text null,						        #封面图片	BookInfoStatus int default 1 not null,			#书藉状态（1，在用（在库），2，报废，3。转移）	BookInfoRemarks text null,						        #备注	BookInfoOperatorId int null,					    #操作人员ID	BookInfoAddDate datetime not null,	    #添加時間 可不以默认时间DEFAULT CURDATE()	BookInfoPrice float default 1.00,					#书价格  BookUseCode varchar(100)	);desc BookKindList;#查询表结构show tables;#查询所有表select * from BookKindList; #查询insert into BookKindList(BookKindName,BookKindParent)values('六福书目录',0);insert into BookKindList(BookKindName,BookKindParent)values('文学',1);insert into BookKindList(BookKindName,BookKindParent)values('科学技术',1);/*自定义函数*/#部门函数DELIMITER $$DROP FUNCTION IF EXISTS `geovindu`.`f_GetDepartmentName` $$CREATE FUNCTION `geovindu`.`f_GetDepartmentName` (did int) RETURNS varchar(100)BEGINdeclare str varchar(100);return(select DepartmentName from DepartmentList where DepartmentID=did);END $$DELIMITER ;#使用函数select f_GetDepartmentName(1);select * from BookInfoList;#作家函数DELIMITER $$DROP FUNCTION IF EXISTS `geovindu`.`f_GetAuthorName` $$CREATE FUNCTION `geovindu`.`f_GetAuthorName` (did int) RETURNS varchar(400)BEGIN   declare str varchar(100);return(select AuthorName from AuthorList where AuthorID=did);END $$DELIMITER ;/*视图*/select * from geovindu.views;desc View_BookInfoList;show create view View_BookInfoList;select * from View_BookInfoList;CREATE VIEW `geovindu`.`View_BookInfoList` AS  select BookInfoID , BookInfoKind , BookInfoPlace ,BookInfoSeries , BookInfoAuthor , BookInfoPress , BookInfoLanguage , BookInfoStatus , BookInfoOperatorId ,  BookInfoISBN , BookInfoBarCode , BookInfoName ,    BookInfoRemarks ,BookInfoAddDate,BookInfoPublish ,BookInfoPrice,BookKindList.BookKindName,BookPlaceList.BookPlaceName,f_GetAuthorName(BookInfoAuthor)from BookInfoList,BookKindList,BookPlaceListwhere BookInfoList.BookInfoKind=BookKindList.BookKindID  and BookInfoList.BookInfoPlace=BookPlaceList.BookPlaceID;/*储存过程 解决方案的思维模式基本相同，只是一些指令不同*/#IN 表示输入参数#OUT表示输出参数#INOUT:表示即可以输入参数也可以输出参数#存储过程 利用mysql-query-browser创建存储过程和函数#删除DELIMITER $$DROP PROCEDURE IF EXISTS `geovindu`.`DeleteBookKind` $$CREATE PROCEDURE `geovindu`.`DeleteBookKind` (IN param1 INT)BEGIN         Delete From bookkindlist WHERE BookKindID  = param1;END $$DELIMITER ;#查询所有DELIMITER $$DROP PROCEDURE IF EXISTS `geovindu`.`proc_Select_BookKindListAll()` $$CREATE PROCEDURE `geovindu`.`proc_Select_BookKindListAll()` ()BEGIN    SELECT * FROM bookkindlist;END $$DELIMITER ;select * from  `geovindu`.`bookkindlist`;SELECT * FROM bookkindlist;#统计DELIMITER $$DROP PROCEDURE IF EXISTS `geovindu`.`BookKindCount` $$CREATE PROCEDURE `geovindu`.`BookKindCount` (OUT param1ID INT)BEGIN        select COUNT(*) into param1ID  From bookkindlist;END $$DELIMITER ;#更新 DELIMITER $$DROP PROCEDURE IF EXISTS `geovindu`.`proc_Update_BookKindList` $$CREATE PROCEDURE `geovindu`.`proc_Update_BookKindList` (IN param1ID Int,IN param1Name NVarChar(1000),IN param1Parent Int)BEGINIF NOT EXISTS (SELECT * FROM BookKindList WHERE BookKindName=param1Name) then #如果存在相同的记录，不更新名称UPDATE BookKindList	SET		BookKindName=param1Name ,		BookKindParent=param1Parent	where		BookKindID=param1ID;ELSE    UPDATE BookKindList	SET BookKindParent=param1Parent	where		BookKindID=param1ID;END IF;END $$DELIMITER ;#查询一条DELIMITER $$DROP PROCEDURE IF EXISTS `geovindu`.`proc_Select_BookKindList` $$CREATE PROCEDURE `geovindu`.`proc_Select_BookKindList` (IN param1 INT)BEGIN        SELECT * FROM BookKindList WHERE BookKindID = param1;END $$DELIMITER ;#插入一条DELIMITER $$DROP PROCEDURE IF EXISTS `geovindu`.`proc_Insert_BookKindList` $$CREATE PROCEDURE `geovindu`.`proc_Insert_BookKindList` (IN param1Name NVarChar(1000),IN param1Parent Int)BEGIN        insert into BookKindList(BookKindName,BookKindParent) values(param1Name,param1Parent);END $$DELIMITER ;#插入一条返回值DELIMITER $$DROP PROCEDURE IF EXISTS `geovindu`.`proc_Insert_BookKindOut` $$CREATE PROCEDURE `geovindu`.`proc_Insert_BookKindOut` (IN param1Name NVarChar(1000),IN param1Parent Int,OUT ID INT)BEGIN     IF NOT EXISTS (SELECT * FROM BookKindList WHERE BookKindName=param1Name) then   #如果存在相同的记录，不添加        INSERT INTO BookKindList (BookKindName,BookKindParent)VALUES(param1Name ,param1Parent);        #set ID=Last_insert_id()        SELECT LAST_INSERT_ID() into ID;      end if;END $$DELIMITER ;