SQL: Mysql create view, function, procedure, mysqlprocedure

SQL: Mysql create view, function, procedure, mysqlprocedure

Create database Liber; use Liber; # data statistics 20150210 Geovin Du tu juwen show databases; drop table BookKindList; # create table BookKindList (BookKindID int not null AUTO_INCREMENT, # automatically add BookKindName nvarchar (500) not null, BookKindParent int null, primary key (BookKindID) # primary key); # create table BookKindList (BookKindID int not null primary key AUTO_INCREMENT, # automatically add # primary key BookKindName nvarchar (500) not null, BookKind Parent int null); # create table BookPlaceList (BookPlaceID int not null primary key AUTO_INCREMENT, BookPlaceName nvarchar (500) not null, BookPlaceParent int null ); # Series or set name (0 in a book. none, with sub-books) 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 P Rimary key AUTO_INCREMENT, PositionName nvarchar (500) not null); # Department of Operation Department partition pycreate table DepartmentList (partition mentid int not null primary key AUTO_INCREMENT, DepartmentName nvarchar (500) not null ); insert into values (values) values ('administrative authorization'); insert into DepartmentList (DepartmentName) values ('authorization'); select * from DepartmentList; # optional types of aggregate agecreate table aggregate agelist (aggregate ageid IN T not null primary key AUTO_INCREMENT, comment agename nvarchar (500) not null); # Press # create table PressList (PressID int not null primary key AUTO_INCREMENT, PressName nvarchar (500) not null); # writer Authorcreate table AuthorList (AuthorID int not null primary key AUTO_INCREMENT, AuthorName nvarchar (500) not null); # BookStatus book by State (1, in use, 2, decommission, 3. Transfer) create table BookStatusList (BookStatusID int not null primary key AUTO_INCREMENT, BookStatusName nvarchar (500) not null); # lending status: lending, Renewal, return, pre-lending 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, # auto-increment # primary key BookInfoKind Int not null, # book type foreign key BookInfoPlace int not null, # Place location foreign key BookInfoISBN varchar (50) not null, # book ISBN encoding BookInfoBarCode varchar (60) not null, # manage a bar code (barcode) BookInfoName nvarchar (500) not null, # title: BookInfoSeriesint default 1, # book series, 0 is the empty column BookInfoAuthor int null, # author BookInfoPress int null, # BookInfoLanguage int null, # language: BookInfoPublish datetime, # publication date: BookInfoImage text null, # cover image BookInfoStatus int d Efault 1 not null, # book Status (1, in use (in the database), 2, decommission, 3. Transfer) BookInfoRemarks text null, # Remarks BookInfoOperatorId int null, # Operator IDBookInfoAddDate datetime not null, # add time can not be set to default curdate () BookInfoPrice float default 1.00, # BookUseCode varchar (100); desc BookKindList; # query table structure show tables; # query all tables select * from BookKindList; # query insert into BookKindList (BookKindName, BookKindParent) values ('liufu book directory', 0); insert into BookKindList (BookKindName, BookKindParent) values ('literature ', 1); insert into BookKindList (BookKindName, BookKindParent) values ('science and technology ', 1);/* User-Defined FUNCTION */# department function 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 inclumentid = did); END $ DELIMITER; # Use the FUNCTION select f_GetDepartmentName (1); select * from BookInfoList; # writer function 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; /* view */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, region, BookInfoStatus, region, BookInfoISBN, BookInfoBarCode, BookInfoName, region, BookInfoAddDate, region, BookInfoPrice, bookKindList. bookKindName, BookPlaceList. bookPlaceName, f_GetAuthorName (BookInfoAuthor) from BookInfoList, BookKindList, BookPlaceListwhere BookInfoList. bookInfoKind = BookKindList. bookKindID and BookInfoList. bookInfoPlace = BookPlaceList. bookPlaceID;/* the thinking mode of the stored procedure solution is basically the same, but some commands are different */# IN indicates the input parameter # OUT indicates the output parameter # INOUT: indicates that you can enter or output parameters. # stored procedures use mysql-query-browser to create stored procedures and functions. # Delete DELIMITER $ drop procedure if exists 'geovindu '. 'deletebookkind' $ create procedure 'geovindu '. 'deletebookkind' (IN param1 INT) BEGIN Delete From bookkindlist WHERE BookKindID = param1; END $ DELIMITER; # query all 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; # Count DELIMITER $ drop procedure if exists 'geovindu '. 'bookkindcount' $ create procedure 'geovindu '. 'bookkindcount' (OUT param1ID INT) BEGIN select COUNT (*) into param1ID From bookkindlist; END $ DELIMITER; # update 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 # IF the same record exists, UPDATE BookKindListSETBookKindName = param1Name, BookKindParent = shard = param1ID; else update BookKindListSET BookKindParent = shard = param1ID; end if; END $ DELIMITER; # query a 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; # insert a DELIMITER $ drop procedure if exists 'geovindu '. 'proc _ Insert_BookKindList '$ create procedure 'geovindu '. 'proc _ upload' (IN param1Name NVarChar (1000), IN param1Parent Int) BEGIN insert into BookKindList (BookKindName, BookKindParent) values (param1Name, param1Parent); END $ DELIMITER; # insert a returned value 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 # If the same record exists, insert into BookKindList (BookKindName, BookKindParent) VALUES (param1Name, param1Parent) is not added; # set ID = Last_insert_id () SELECT LAST_INSERT_ID; end if; END $ DELIMITER;