sql:Mysql create view,function,procedure

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 ;

www.9778.com,sql:Mysql create view,function,procedure。