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 ); #職位Position create table PositionList ( PositionID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, PositionName nvarchar(500) not null ); #部門Department ShortPY create 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; #語種 Language create 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 ); #作家Author create 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,Reservations create 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) BEGIN declare 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,BookPlaceList where 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) BEGIN IF 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 ;