用命令文件生成数据库的脚本

最新推荐文章于 2022-01-14 14:43:53 发布
最新推荐文章于 2022-01-14 14:43:53 发布
阅读量1k 收藏
点赞数
分类专栏： sql server 文章标签： 数据库 insert go null action database
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/tanyun1111/article/details/1239968
版权

1、createData.cmd

 

@Echo Off

REM ***************************************************************************
REM **        File: CreateDatabase.cmd
REM **        Name: Database Setup
REM **        Desc: Database setup batch file.
REM **
REM **        Date: 1/12/2002
REM **
REM **************************************************************************/

REM Use the following section to set the server and sa password
REM of the SQL Server on which you want to create the database.
set SERVER=localhost
set LOGIN=sa
set PWD=11111111
set DBNAME=petshop_j2ee

@Echo.
@Echo *******************************************************************************
@Echo *         PetShop Blueprint Application Database Setup                        *
@Echo *                                                                             *
@Echo *                                                                             *
@Echo * This script will create the PetShop database and database objects.          *
@Echo * If you wish to cancel, press [CTRL]-C and terminate the batch job.          *
@Echo *                                                                             *
@Echo *******************************************************************************
@Echo.
pause

@Echo.
@Echo.
@Echo *******************************************************************************
@Echo * Creating the Database and Inserting the Data                               *
@Echo *******************************************************************************
@Echo.
osql -S %SERVER% -U %LOGIN% -P %PWD% -d master -i PetShop_Schema.sql


@Echo.
@Echo *******************************************************************************
@Echo *                                                                             *
@Echo * Database Setup Script Complete                                              *
@Echo *                                                                             *
@Echo *******************************************************************************
@Echo.
pause

 

2、PetShop_Schema.sql

/******************************************************************************
** File: PetShop_Schema.sql
** Name: Physical Database Schema SQL Script
** Desc: This script will create the PetShop database schema
**
**
*******************************************************************************/

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'petshop_j2ee')
 DROP DATABASE petshop_j2ee
GO


CREATE DATABASE petshop_j2ee
GO


exec sp_dboption N'petshop_j2ee', N'autoclose', N'false'
GO
exec sp_dboption N'petshop_j2ee', N'bulkcopy', N'false'
GO
exec sp_dboption N'petshop_j2ee', N'trunc. log', N'false'
GO
exec sp_dboption N'petshop_j2ee', N'torn page detection', N'true'
GO
exec sp_dboption N'petshop_j2ee', N'read only', N'false'
GO
exec sp_dboption N'petshop_j2ee', N'dbo use', N'false'
GO
exec sp_dboption N'petshop_j2ee', N'single', N'false'
GO
exec sp_dboption N'petshop_j2ee', N'autoshrink', N'false'
GO
exec sp_dboption N'petshop_j2ee', N'ANSI null default', N'false'
GO
exec sp_dboption N'petshop_j2ee', N'recursive triggers', N'false'
GO
exec sp_dboption N'petshop_j2ee', N'ANSI nulls', N'false'
GO
exec sp_dboption N'petshop_j2ee', N'concat null yields null', N'false'
GO
exec sp_dboption N'petshop_j2ee', N'cursor close on commit', N'false'
GO
exec sp_dboption N'petshop_j2ee', N'default to local cursor', N'false'
GO
exec sp_dboption N'petshop_j2ee', N'quoted identifier', N'false'
GO
exec sp_dboption N'petshop_j2ee', N'ANSI warnings', N'false'
GO
exec sp_dboption N'petshop_j2ee', N'auto create statistics', N'true'
GO
exec sp_dboption N'petshop_j2ee', N'auto update statistics', N'true'
GO


use petshop_j2ee
GO

BEGIN TRANSACTION
GO

CREATE TABLE userAccount (userId varchar(80), password varchar(25), email varchar(80), firstName varchar(80), lastName varchar(80), address1 varchar(80), address2 varchar(40), city varchar(80), state varchar(80), zip varchar(20), country varchar(20), phone varchar(80), languagePreference varchar(80), categoryId varchar(80), myListOptions bit, bannerOptions bit, recordVersion uniqueidentifier NOT NULL DEFAULT NEWID(), PRIMARY KEY(userId) )
GO

INSERT INTO userAccount (userId, password , email, firstName, lastName, address1, address2, city, state, zip, country, phone, languagePreference, categoryId, myListOptions, bannerOptions) VALUES ('j2ee', 'j2eepass', 'yourname@yourdomain.com', 'ABC', 'XYX', '901 San Antonio Road', 'UCUP02-666', 'Palo Alto', 'CA', '94303', 'USA', '555-555-5555', 'en', 'DOGS', 1, 1)
GO

 

CREATE TABLE bannerData (categoryId varchar(80), bannerName varchar(80) NOT NULL, PRIMARY KEY(categoryId) )
GO

INSERT INTO bannerData (categoryId, bannerName) VALUES ('BIRDS', 'banner_birds.gif')
GO
INSERT INTO bannerData (categoryId, bannerName) VALUES ('CATS', 'banner_cats.gif')
GO
INSERT INTO bannerData (categoryId, bannerName) VALUES ('DOGS', 'banner_dogs.gif')
GO
INSERT INTO bannerData (categoryId, bannerName) VALUES ('FISH', 'banner_fish.gif')
GO
INSERT INTO bannerData (categoryId, bannerName) VALUES ('REPTILES', 'banner_reptiles.gif')
GO

 

CREATE TABLE category (categoryId varchar(10), PRIMARY KEY(categoryId) )
GO

INSERT INTO category (categoryId) VALUES ('BIRDS')
GO
INSERT INTO category (categoryId) VALUES ('CATS')
GO
INSERT INTO category (categoryId) VALUES ('DOGS')
GO
INSERT INTO category (categoryId) VALUES ('FISH')
GO
INSERT INTO category (categoryId) VALUES ('REPTILES')
GO

 

CREATE TABLE categoryDetail (categoryId varchar(10), locale varchar(10) NOT NULL, name varchar(80) NOT NULL, PRIMARY KEY(categoryId, locale) )
GO

INSERT INTO categoryDetail (categoryId, locale, name) VALUES ('BIRDS', 'en', 'Birds')
GO
INSERT INTO categoryDetail (categoryId, locale, name) VALUES ('CATS', 'en', 'Cats')
GO
INSERT INTO categoryDetail (categoryId, locale, name) VALUES ('DOGS', 'en', 'Dogs')
GO
INSERT INTO categoryDetail (categoryId, locale, name) VALUES ('FISH', 'en', 'Fish')
GO
INSERT INTO categoryDetail (categoryId, locale, name) VALUES ('REPTILES', 'en', 'Reptiles')
GO
INSERT INTO categoryDetail (categoryId, locale, name) VALUES ('BIRDS', 'pt', 'Pássaros')
GO
INSERT INTO categoryDetail (categoryId, locale, name) VALUES ('CATS', 'pt', 'Gatos')
GO
INSERT INTO categoryDetail (categoryId, locale, name) VALUES ('DOGS', 'pt', 'Cães')
GO
INSERT INTO categoryDetail (categoryId, locale, name) VALUES ('FISH', 'pt', 'Peixes')
GO
INSERT INTO categoryDetail (categoryId, locale, name) VALUES ('REPTILES', 'pt', 'Répteis')
GO

 


CREATE TABLE item (itemId varchar(10), productId varchar(10) NOT NULL, unitCost numeric(10, 2) NOT NULL, supplierId int NOT NULL, quantity int NOT NULL, PRIMARY KEY(itemId) )
GO

INSERT INTO item (itemId, productId, unitCost, supplierId, quantity) VALUES ('EST-1', 'FI-SW-01', 16.5, 1, 10000)
GO
INSERT INTO item (itemId, productId, unitCost, supplierId, quantity) VALUES ('EST-10', 'K9-DL-01', 18.5, 1, 10000)
GO
INSERT INTO item (itemId, productId, unitCost, supplierId, quantity) VALUES ('EST-11', 'RP-SN-01', 18.5, 1, 10000)
GO
INSERT INTO item (itemId, productId, unitCost, supplierId, quantity) VALUES ('EST-12', 'RP-SN-01', 18.5, 1, 10000)
GO
INSERT INTO item (itemId, productId, unitCost, supplierId, quantity) VALUES ('EST-13', 'RP-LI-02', 18.5, 1, 10000)
GO
INSERT INTO item (itemId, productId, unitCost, supplierId, quantity) VALUES ('EST-14', 'FL-DSH-01', 58.5, 1, 10000)
GO
INSERT INTO item (itemId, productId, unitCost, supplierId, quantity) VALUES ('EST-15', 'FL-DSH-01', 23.5, 1, 10000)
GO
INSERT INTO item (itemId, productId, unitCost, supplierId, quantity) VALUES ('EST-16', 'FL-DLH-02', 93.5, 1, 10000)
GO
INSERT INTO item (itemId, productId, unitCost, supplierId, quantity) VALUES ('EST-17', 'FL-DLH-02', 93.5, 1, 10000)
GO
INSERT INTO item (itemId, productId, unitCost, supplierId, quantity) VALUES ('EST-18', 'AV-CB-01', 193.5, 1, 10000)
GO
INSERT INTO item (itemId, productId, unitCost, supplierId, quantity) VALUES ('EST-19', 'AV-SB-02', 15.5, 1, 10000)
GO
INSERT INTO item (itemId, productId, unitCost, supplierId, quantity) VALUES ('EST-2', 'FI-SW-01', 16.5, 1, 10000)
GO
INSERT INTO item (itemId, productId, unitCost, supplierId, quantity) VALUES ('EST-20', 'FI-FW-02', 5.5, 1, 10000)
GO
INSERT INTO item (itemId, productId, unitCost, supplierId, quantity) VALUES ('EST-21', 'FI-FW-02', 5.29, 1, 10000)
GO
INSERT INTO item (itemId, productId, unitCost, supplierId, quantity) VALUES ('EST-22', 'K9-RT-02', 135.5, 1, 10000)
GO
INSERT INTO item (itemId, productId, unitCost, supplierId, quantity) VALUES ('EST-23', 'K9-RT-02', 145.49, 1, 10000)
GO
INSERT INTO item (itemId, productId, unitCost, supplierId, quantity) VALUES ('EST-24', 'K9-RT-02', 255.5, 1, 10000)
GO
INSERT INTO item (itemId, productId, unitCost, supplierId, quantity) VALUES ('EST-25', 'K9-RT-02', 325.29, 1, 10000)
GO
INSERT INTO item (itemId, productId, unitCost, supplierId, quantity) VALUES ('EST-26', 'K9-CW-01', 125.5, 1, 10000)
GO
INSERT INTO item (itemId, productId, unitCost, supplierId, quantity) VALUES ('EST-27', 'K9-CW-01', 155.29, 1, 10000)
GO
INSERT INTO item (itemId, productId, unitCost, supplierId, quantity) VALUES ('EST-28', 'K9-RT-01', 155.29, 1, 10000)
GO
INSERT INTO item (itemId, productId, unitCost, supplierId, quantity) VALUES ('EST-3', 'FI-SW-02', 18.5, 1, 10000)
GO
INSERT INTO item (itemId, productId, unitCost, supplierId, quantity) VALUES ('EST-4', 'FI-FW-01', 18.5, 1, 10000)
GO
INSERT INTO item (itemId, productId, unitCost, supplierId, quantity) VALUES ('EST-5', 'FI-FW-01', 18.5, 1, 10000)
GO
INSERT INTO item (itemId, productId, unitCost, supplierId, quantity) VALUES ('EST-8', 'K9-PO-02', 18.5, 1, 10000)
GO
INSERT INTO item (itemId, productId, unitCost, supplierId, quantity) VALUES ('EST-9', 'K9-DL-01', 18.5, 1, 10000)
GO

 

CREATE TABLE itemDetail (itemId varchar(10), locale varchar(10) NOT NULL, attribute varchar(255) NOT NULL, PRIMARY KEY(itemId, locale) )
GO

INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-1', 'pt', 'Grande')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-1', 'en', 'Large')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-10', 'pt', 'Fêmea Adulta com Manchas')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-10', 'en', 'Spotted Adult Female')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-11', 'pt', 'Sem veneno')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-11', 'en', 'Venomless')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-12', 'en', 'Rattleless')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-12', 'pt', 'Sem guizo')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-13', 'pt', 'Adulto Verde')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-13', 'en', 'Green Adult')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-14', 'pt', 'Sem cauda')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-14', 'en', 'Tailless')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-15', 'pt', 'Com cauda')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-15', 'en', 'With tail')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-16', 'en', 'Adult Female')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-16', 'pt', 'Fêmea Adulta')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-17', 'en', 'Adult Male')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-17', 'pt', 'Macho Adulto')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-18', 'en', 'Adult Male')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-18', 'pt', 'Macho Adulto')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-19', 'en', 'Adult Male')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-19', 'pt', 'Macho Adulto')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-2', 'pt', 'Pequeno')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-2', 'en', 'Small')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-20', 'en', 'Adult Male')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-20', 'pt', 'Macho Adulto')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-21', 'en', 'Adult Female')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-21', 'pt', 'Fêmea Adulta')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-22', 'en', 'Adult Male')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-22', 'pt', 'Macho Adulto')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-23', 'en', 'Adult Female')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-23', 'pt', 'Fêmea Adulta')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-24', 'en', 'Adult Male')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-24', 'pt', 'Macho Adulto')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-25', 'en', 'Adult Female')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-25', 'pt', 'Fêmea Adulta')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-26', 'en', 'Adult Male')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-26', 'pt', 'Macho Adulto')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-27', 'en', 'Adult Female')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-27', 'pt', 'Fêmea Adulta')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-28', 'en', 'Adult Female')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-28', 'pt', 'Fêmea Adulta')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-3', 'pt', 'Sem dentes')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-3', 'en', 'Toothless')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-4', 'pt', 'Manchado')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-4', 'en', 'Spotted')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-5', 'pt', 'Sem Manchas')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-5', 'en', 'Spotless')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-8', 'pt', 'Cachorrito')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-8', 'en', 'Male Puppy')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-9', 'pt', 'Cachorro Adulto sem Manchas')
GO
INSERT INTO itemDetail (itemId, locale, attribute) VALUES ('EST-9', 'en', 'Spotless Male Puppy')
GO

 

CREATE TABLE lineItem (orderId bigint , lineNumber int, itemId varchar(10) NOT NULL, quantity int NOT NULL, PRIMARY KEY(orderId, lineNumber) )
GO


CREATE TABLE [order] (orderId bigint IDENTITY (1, 1) NOT NULL, userId varchar(80) NOT NULL, orderDate datetime NOT NULL, shipToFirstName varchar(80) NOT NULL, shipToLastName varchar(80) NOT NULL, shipToAddress1 varchar(80) NOT NULL, shipToAddress2 varchar(80) NOT NULL, shipToCity varchar(80) NOT NULL, shipToState varchar(80), shipToZip varchar(20) NOT NULL, shipToCountry varchar(80) NOT NULL, shiptoPhoneNumber varchar(80), billToFirstName varchar(80) NOT NULL, billToLastName varchar(80), billToAddress1 varchar(80), billToAddress2 varchar(80), billToCity varchar(80), billToState varchar(80), billToPostalCode varchar(20) NOT NULL, billToCountry varchar(80) NOT NULL, billToPhoneNumber varchar(80), totalPrice numeric(10, 2), cardType varchar(20) NOT NULL, cardNumber varchar(20) NOT NULL, cardExpireMonth char(2) NOT NULL, cardExpireYear char(4) NOT NULL, locale varchar(10) NOT NULL, PRIMARY KEY (orderId) )
GO


CREATE TABLE product (productId varchar(10), categoryId varchar(10) NOT NULL, image varchar(255), PRIMARY KEY(productId) )
GO

INSERT INTO product (productId, categoryId, image) VALUES ('AV-CB-01', 'BIRDS', 'bird4.gif')
GO
INSERT INTO product (productId, categoryId, image) VALUES ('AV-SB-02', 'BIRDS', 'bird2.gif')
GO
INSERT INTO product (productId, categoryId, image) VALUES ('FI-FW-01', 'FISH', 'fish3.gif')
GO
INSERT INTO product (productId, categoryId, image) VALUES ('FI-FW-02', 'FISH', 'fish2.gif')
GO
INSERT INTO product (productId, categoryId, image) VALUES ('FI-SW-01', 'FISH', 'fish1.gif')
GO
INSERT INTO product (productId, categoryId, image) VALUES ('FI-SW-02', 'FISH', 'fish4.gif')
GO
INSERT INTO product (productId, categoryId, image) VALUES ('FL-DLH-02', 'CATS', 'cat1.gif')
GO
INSERT INTO product (productId, categoryId, image) VALUES ('FL-DSH-01', 'CATS', 'cat3.gif')
GO
INSERT INTO product (productId, categoryId, image) VALUES ('K9-CW-01', 'DOGS', 'dog4.gif')
GO
INSERT INTO product (productId, categoryId, image) VALUES ('K9-DL-01', 'DOGS', 'dog6.gif')
GO
INSERT INTO product (productId, categoryId, image) VALUES ('K9-PO-02', 'DOGS', 'dog3.gif')
GO
INSERT INTO product (productId, categoryId, image) VALUES ('K9-RT-01', 'DOGS', 'dog1.gif')
GO
INSERT INTO product (productId, categoryId, image) VALUES ('K9-RT-02', 'DOGS', 'dog5.gif')
GO
INSERT INTO product (productId, categoryId, image) VALUES ('RP-LI-02', 'REPTILES', 'lizard1.gif')
GO
INSERT INTO product (productId, categoryId, image) VALUES ('RP-SN-01', 'REPTILES', 'snake1.gif')
GO

 


CREATE TABLE productDetail (productId varchar(10), locale varchar(10) NOT NULL, name varchar(80) NOT NULL, description varchar(255) NOT NULL, PRIMARY KEY(productId, locale) )
GO

INSERT INTO productDetail (productId, locale, name, description) VALUES ('AV-CB-01', 'en', 'Amazon Parrot', 'Great companion for up to 75 years')
GO
INSERT INTO productDetail (productId, locale, name, description) VALUES ('AV-CB-01', 'pt', 'Arara', 'Companhia Excelente durante 75 anos')
GO
INSERT INTO productDetail (productId, locale, name, description) VALUES ('AV-SB-02', 'en', 'Finch', 'Great stress reliever')
GO
INSERT INTO productDetail (productId, locale, name, description) VALUES ('AV-SB-02', 'pt', 'Canário', 'Alivia o stress')
GO
INSERT INTO productDetail (productId, locale, name, description) VALUES ('FI-FW-01', 'en', 'Koi', 'Fresh Water Fish from Japan')
GO
INSERT INTO productDetail (productId, locale, name, description) VALUES ('FI-FW-01', 'pt', 'Koi', 'Peixe de água doce do Japão')
GO
INSERT INTO productDetail (productId, locale, name, description) VALUES ('FI-FW-02', 'en', 'Golden Fish', 'Fresh Water Fish from China')
GO
INSERT INTO productDetail (productId, locale, name, description) VALUES ('FI-FW-02', 'pt', 'Peixe Dourado', 'Peixe de água doce da China')
GO
INSERT INTO productDetail (productId, locale, name, description) VALUES ('FI-SW-01', 'en', 'Angel Fish', 'Salt Water Fish from Australia')
GO
INSERT INTO productDetail (productId, locale, name, description) VALUES ('FI-SW-01', 'pt', 'Peixe Anjo', 'Peixe de água salgada da Australia')
GO
INSERT INTO productDetail (productId, locale, name, description) VALUES ('FI-SW-02', 'en', 'Tiger Shark', 'Salt Water Fish from Australia')
GO
INSERT INTO productDetail (productId, locale, name, description) VALUES ('FI-SW-02', 'pt', 'Tiger Shark', 'Peixe de água salgada da Australia')
GO
INSERT INTO productDetail (productId, locale, name, description) VALUES ('FL-DLH-02', 'en', 'Persian', 'Friendly house cat, doubles as a princess')
GO
INSERT INTO productDetail (productId, locale, name, description) VALUES ('FL-DLH-02', 'pt', 'Persa', 'Gato Doméstico, faz as vezes de uma princesa')
GO
INSERT INTO productDetail (productId, locale, name, description) VALUES ('FL-DSH-01', 'en', 'Manx', 'Great for reducing mouse populations')
GO
INSERT INTO productDetail (productId, locale, name, description) VALUES ('FL-DSH-01', 'pt', 'Gato Comum', 'Excelente para reduzir a população de ratos')
GO
INSERT INTO productDetail (productId, locale, name, description) VALUES ('K9-CW-01', 'en', 'Chihuahua', 'Great companion dog')
GO
INSERT INTO productDetail (productId, locale, name, description) VALUES ('K9-CW-01', 'pt', 'Chihuahua', 'Excelente cão de companhia')
GO
INSERT INTO productDetail (productId, locale, name, description) VALUES ('K9-DL-01', 'en', 'Dalmation', 'Great dog for a Fire Station')
GO
INSERT INTO productDetail (productId, locale, name, description) VALUES ('K9-DL-01', 'pt', 'Dalmata', 'Excelente cão para o Quartel de Bombeiros')
GO
INSERT INTO productDetail (productId, locale, name, description) VALUES ('K9-PO-02', 'en', 'Poodle', 'Cute dog from France')
GO
INSERT INTO productDetail (productId, locale, name, description) VALUES ('K9-PO-02', 'pt', 'Poodle', 'Cão engraçado oriundo de França')
GO
INSERT INTO productDetail (productId, locale, name, description) VALUES ('K9-RT-01', 'en', 'Golden Retriever', 'Great family dog')
GO
INSERT INTO productDetail (productId, locale, name, description) VALUES ('K9-RT-01', 'pt', 'Golden Retriever', 'Excelente cão de família')
GO
INSERT INTO productDetail (productId, locale, name, description) VALUES ('K9-RT-02', 'en', 'Labrador Retriever', 'Great hunting dog')
GO
INSERT INTO productDetail (productId, locale, name, description) VALUES ('K9-RT-02', 'pt', 'Labrador Retriever', 'Excelente cão de caça')
GO
INSERT INTO productDetail (productId, locale, name, description) VALUES ('RP-LI-02', 'en', 'Iguana', 'Friendly green friend')
GO
INSERT INTO productDetail (productId, locale, name, description) VALUES ('RP-LI-02', 'pt', 'Iguana', 'Um amigo verdinho')
GO
INSERT INTO productDetail (productId, locale, name, description) VALUES ('RP-SN-01', 'en', 'Rattlesnake', 'Doubles as a watch dog')
GO
INSERT INTO productDetail (productId, locale, name, description) VALUES ('RP-SN-01', 'pt', 'Cascavél', 'Faz as vezes de um cão de guarda')
GO

 

CREATE TABLE supplier (supplierId int, name varchar(80) NOT NULL, status char(2), address1 varchar(80), address2 varchar(80), city varchar(80), state varchar(80), zip char(5), phone varchar(80), PRIMARY KEY(supplierId) )
GO

INSERT INTO supplier (supplierId, name, status, address1, city, state, zip, phone) VALUES (1, 'XYZ Pets', 'AC', '600 Avon Way', 'Los Angeles', 'CA', '94024', '212-947-0797')
GO
INSERT INTO supplier (supplierId, name, status, address1, city, state, zip, phone) VALUES ('2', 'ABC Pets', 'AC', '700 Abalone Way', 'San Francisco', 'CA', '94024', '415-947-0797')
GO

 

CREATE VIEW itemInformation AS SELECT item.itemId, item.unitCost, item.productId, itemDetail.attribute, itemDetail.locale, productDetail.name, productDetail.description, item.quantity, product.image FROM item INNER JOIN itemDetail ON item.itemId = itemDetail.itemId INNER JOIN product ON item.productId= product.productId INNER JOIN productDetail ON product.productId = productDetail.productId WHERE (productDetail.locale = itemDetail.locale)
GO


CREATE VIEW productInformation AS SELECT product.categoryId, product.productId, product.image, productDetail.locale, productDetail.name, productDetail.description FROM product INNER JOIN productDetail ON product.productId = productDetail.productId
GO

 


CREATE INDEX categoryDetail_idx1 ON categoryDetail (locale)
GO
CREATE INDEX categoryDetail_idx2 ON categoryDetail (categoryId, locale)
GO

CREATE INDEX userAccount_idx1 ON userAccount (password)
GO
CREATE INDEX userAccount_idx2 ON userAccount (userId, password)
GO

CREATE INDEX item_idx1 ON item (productId)
GO
CREATE INDEX item_idx2 ON item (productId, itemId, unitCost)
GO


CREATE INDEX itemDetail_idx1 ON itemDetail (itemId)
GO
CREATE INDEX itemDetail_idx2 ON itemDetail (locale)
GO
CREATE INDEX itemDetail_idx3 ON itemDetail (attribute)
GO


CREATE INDEX order_idx1 ON [order] (userId)
GO
CREATE INDEX order_idx2 ON [order] (orderId, userId)
GO

CREATE INDEX lineItem_idx1 ON lineItem (orderId)
GO
CREATE INDEX lineItem_idx2 ON lineItem (itemId)
GO

CREATE INDEX product_idx1 ON product (categoryId)
GO

CREATE INDEX productDetail_idx1 ON productDetail (productId)
GO
CREATE INDEX productDetail_idx2 ON productDetail (name)
GO
CREATE INDEX productDetail_idx3 ON productDetail (description)
GO

 

ALTER TABLE categoryDetail ADD CONSTRAINT categoryDetail_category_fk FOREIGN KEY(categoryId) REFERENCES category (categoryId) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
ALTER TABLE item ADD CONSTRAINT item_product_fk FOREIGN KEY(productId) REFERENCES product (productId) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
ALTER TABLE item ADD CONSTRAINT item_supplier_fk FOREIGN KEY(supplierId) REFERENCES supplier (supplierId) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
ALTER TABLE itemDetail ADD CONSTRAINT itemDetail_item_fk FOREIGN KEY(itemId) REFERENCES item (itemId) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
ALTER TABLE lineItem ADD CONSTRAINT lineItem_order_fk FOREIGN KEY(orderId) REFERENCES [order] (orderId) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
ALTER TABLE lineItem ADD CONSTRAINT lineItem_item_fk FOREIGN KEY(itemId) REFERENCES item (itemId) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
ALTER TABLE product ADD CONSTRAINT product_category_fk FOREIGN KEY(categoryId) REFERENCES category (categoryId) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
ALTER TABLE productDetail ADD CONSTRAINT productDetail_product_fk FOREIGN KEY(productId) REFERENCES product (productId) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
ALTER TABLE userAccount ADD CONSTRAINT userAccount_bannerData_fk FOREIGN KEY(categoryId) REFERENCES bannerData (categoryId) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
ALTER TABLE [order] ADD CONSTRAINT order_userAccount_fk FOREIGN KEY(userId) REFERENCES userAccount (userId) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

 


-- ============================
-- OPTIMISTIC LOCKING TRIGGERS
-- ============================


IF EXISTS (SELECT name FROM sysobjects WHERE name = 't_olock_userAccount' AND type = 'TR')
   DROP TRIGGER t_olock_userAccount
GO

CREATE TRIGGER t_olock_userAccount ON [userAccount]
INSTEAD OF UPDATE
AS
SET NOCOUNT ON
IF EXISTS(
 SELECT *
 FROM
  [userAccount]
  INNER JOIN inserted
   ON
   [userAccount].[userId] = inserted.[userId]
   AND
   [userAccount].[recordVersion] <> inserted.[recordVersion]
 WHERE
  (inserted.[recordVersion] IS NOT NULL)
 )
 BEGIN
  RAISERROR (16934, 16, 1)
 END
ELSE
 BEGIN
  UPDATE
   [userAccount]
  SET
   [userId] = inserted.[userId],
   [password] = inserted.[password],
   [email] = inserted.[email],
   [firstName] = inserted.[firstName],
   [lastName] = inserted.[lastName],
   [address1] = inserted.[address1],
   [address2] = inserted.[address2],
   [city] = inserted.[city],
   [state] = inserted.[state],
   [zip] = inserted.[zip],
   [country] = inserted.[country],
   [phone] = inserted.[phone],
   [languagePreference] = inserted.[languagePreference],
   [categoryId] = inserted.[categoryId],
   [myListOptions] = inserted.[myListOptions],
   [bannerOptions] = inserted.[bannerOptions],
   [recordVersion] = NEWID()
  FROM
   [userAccount]
   INNER JOIN inserted
    ON
    [userAccount].[userId] = inserted.[userId]
 END
GO

----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------

 

COMMIT TRANSACTION
GO

/*

EXEC sp_changeobjectowner 'userAccount' ,  'dbo'

EXEC sp_changeobjectowner 'bannerData' ,  'dbo'

EXEC sp_changeobjectowner 'category' ,  'dbo'

EXEC sp_changeobjectowner 'categoryDetail' ,  'dbo'

EXEC sp_changeobjectowner 'item' ,  'dbo'

EXEC sp_changeobjectowner 'itemDetail' ,  'dbo'

EXEC sp_changeobjectowner 'lineItem' ,  'dbo'

EXEC sp_changeobjectowner '[order]' ,  'dbo'

EXEC sp_changeobjectowner 'product' ,  'dbo'

EXEC sp_changeobjectowner 'productDetail' ,  'dbo'

EXEC sp_changeobjectowner 'supplier' ,  'dbo'

EXEC sp_changeobjectowner 'itemInformation' ,  'dbo'

EXEC sp_changeobjectowner 'productInformation' ,  'dbo'

*/
 

tanyun1111
关注
专栏目录
2、基于已生成的数据库中的数据进行操作的脚本
qsbr_854662880的专栏
01-08 914
基于mysql5.6数据库进行的操作。
quartz数据库脚本
10-29
总的来说，Quartz数据库脚本是实现Quartz在不同数据库环境下的关键组件，它们使得开发者能够在不修改Quartz源码的情况下，方便地在各种数据库系统中部署和使用作业调度服务。这不仅提高了灵活性，也降低了项目维护的...
使用t4模板动态生成数据库表的插入脚本
07-25
使用t4模板动态生成数据库表的插入脚本,使用时修改T_Privilege.tt中的数据库连接，修改begin 循环中拼接的插入文本，修改数据库变量名db.
数据库生成脚本
weixin_30509393的博客
10-23 113
在任一个数据库右击 点击生成脚本 然后点击下一步 两个单先按钮任意选,一个是编写整个数据库,下面那个可以选择单一的一个表,也可以全部选择,然后点击下一步 点击高级按钮 要编写脚本的数据的类型: 仅限架构 或 架构和数据 或 仅限数据 然后点击确定,然后一直下一...
生成文件shell脚本以及说明（一）
weixin_34269583的博客
03-14 615
生成文件shell脚本以及说明：#!/bin/shfile1=$(date +%Y%m%d%H%M%S) #定义变量获取当前时间ALM_TYPE=BOSS4ZC #定义变量INSTANCE_ID=62100408#定义变量STATUS=MAJOR#定义变量ALM_INFO=Mysql备用主机宕机10.**.62.**,请及时处理.#定义变量ALM_TIME=$(date +'...
Confluence 6 用户提交的备份和恢复脚本
HONEY MOOSE
06-18 486
下面的代码是用户提交的，在使用的时候需要小心，因为 Atlassian 不提供这些代码的技术支持。如果你在使用或者修改这些代码的时候有任何问题，请粘贴到 post them to Atlassian Answers。删除老的备份 —— Windows 的 Wscript 脚本这个脚本将会检查备份文件然后删除他们（必要的话），下面的代码可能需要一些编辑。'If you want 3 day old ...
数据库自动收缩
weixin_30362083的博客
10-26 174
EXEC sp_dboption 'menuDB', 'autoshrink', 'TRUE' 转载于:https://www.cnblogs.com/the7stroke/archive/2011/10/26/2224700.html
使用PowerDesigner12.5设计数据库并生成MySQL脚本文件[整理].pdf
10-14
使用 PowerDesigner 12.5 设计数据库并生成 MySQL 脚本文件 基于 PowerDesigner 12.5 软件，用户可以设计数据库并生成 MySQL 脚本文件。本篇文章将指导用户从头开始设计数据库，生成物理数据模型，并最终生成 MySQL...
Mybatis代码生成器Demo(含数据库脚本).rar
07-28
4. `数据库脚本` - 创建数据库表的SQL脚本，用于测试代码生成器。 通过这个Demo，你可以学习如何配置和使用Mybatis代码生成器，以及如何根据实际情况选择合适的生成方式。同时，这也是一个很好的实践机会，帮助你...
利用python生成一个导出数据库的bat脚本文件的方法
09-21
在本文中，我们将探讨如何使用Python来生成一个导出数据库的批处理（BAT）脚本文件。这种方法可以帮助自动化数据库备份过程，特别是对于需要定期备份多个数据库的环境。以下是实现这个功能的关键步骤和知识点： ...
shell脚本生成.txt数据文件
03-29
在这个特定的场景中，我们关注的是如何使用Shell脚本来生成`.txt`数据文件。`.txt`文件是最常见的文本文件格式，通常用于存储纯文本信息，例如日志、代码、文档等。 首先，让我们了解基本的Shell脚本结构。一个简单...
SQL Server 生成sql脚本并执行 还原数据库
热门推荐
故梦-阿梓
03-28 3万+
 上次介绍过如何通过bak文件来还原数据库文件，今天来介绍一下如何通过导出sql脚本的方式来备份并且还原新的数据库，不过这种方式比较适合数据库数据不太多的中小型数据表，下面通过SQL Server2012为例来进行说明。      一、  备份数据库           1.  选择某一个数据库，右键依次选择：任务==》生成脚本；            　　　　2.  选择要编写脚本的数据库对象，...
数据库SQL命令大全
Alan_King79的博客
01-14 1万+
一、关于数据库的SQL命令 1.创建数据库 CREATE DATABASE 数据库名称 [ON [FILEGROUP 文件组名称] ( NAME=数据文件逻辑名称, FILENAME='路径+数据文件名', SIZE=数据文件初始大小, MAXSIZE=数据文件最大容量, FILEGROWTH=数据文件自动增长容量, )] [LOG ON ( NAME=日志文件逻辑名称, FILENAME='路径+日志文件名', SIZE=日志文件初始大小, MAXSIZE=日志文件最大容量, FILEGROWTH=
SQLServer：如何生成数据库的脚本
我想我是海 冬天的大海 心情随风轻摆
11-24 4664
三种方式： 1、编程方式实现； --功能强大，但要求有编程基础，没有相关的技术文档。 --适合：表、索引等无法通过其它方法批量生成的对象。 2、利用系统视图实现； --获取非常方便，但只能获取到 标量函数、表值函数、存储过程、触发器、视图 这几种对象，其它对象无法获取。 3、数据库手动操作生成脚本； --较为方便， 但失去了自动化和可编程。
SQL Server生成含有数据的脚本文件
PinkCoder
06-22 2万+
SQL Server生成含有数据的脚本文件
.NET新手系列（九）
weixin_34242658的博客
05-07 102
没怎么整理，乱得很，姑且如此吧...-_- 1.遇到的问题： Mysql中编写SQL语句以及MYSQL-FRONT等 解决方法： 学过SQL SERVER，MYSQL的语法有些不同。 1)创建存储过程语句，括号是必须的，存储过程的参数必须写在括号里。语句块的开始和结束处要分别用BEGIN和END，当然还要记住最基本的分号。 另外，存储过程的调用语句要用CALL。 如果有输出参数，则O...
如何导出sql脚本文件？
DMW2016的博客
06-27 1900
如何导出MySql数据库的脚本文件？
SQL Server常用系统存储过程
快步奔小康
10-10 432
--列出SQLServer实例中的数据库sp_databases--返回SQLServer、数据库网关或基础数据源的特性名和匹配值的列表sp_server_info--返回当前环境中的存储过程列表sp_stored_procedures--返回当前环境下可查询的对象的列表（任何可出现在 FROM子句中的对象）sp_tablesselect * from
Sql Server 中如何将一个数据库生成脚本文件
胖太乙
09-23 1万+
前言 有些人在离职之前，都喜欢把原有自己做的项目做个备份，当然数据库也不例外,不过数据库比较大，而且还都是在联机状态，总不能断开数据库， 然后去拷贝数据库文件吧，这显然是不可能的，下面我就来给大家介绍一种方法， 不需要断开连接， 即可拷贝数据库文件（将数据库生成脚本， 然后执行脚本就又重新生成数据库文件了。） 第一步 数据库右键 -> 任务 -> 生成脚本文件 第二步 选择“选择特定数据库对象” ， 可以全部勾选 第三步 这里需要注意下，高级里面有个选项—要编写脚本的数据库类型，这个选项默
pg转mysql_postgresql转mysql生成数据库脚本
最新发布
05-28
将 PostgreSQL 数据库转换为 MySQL 数据库需要执行以下步骤： 1. 导出 PostgreSQL 数据库结构 使用 pg_dump 命令导出 PostgreSQL 数据库结构，例如： ``` pg_dump -s mydb > mydb.sql ``` 该命令将导出 mydb 数据库的结构并保存到 mydb.sql 文件中。 2. 转换数据库脚本 使用工具将 PostgreSQL 数据库结构转换为 MySQL 数据库结构，例如使用 pg2mysql 工具。可以使用以下命令安装 pg2mysql 工具： ``` sudo apt-get install pg2mysql ``` 转换 mydb.sql 文件的命令如下： ``` pg2mysql mydb.sql > mydb_mysql.sql ``` 该命令将转换 mydb.sql 文件并保存为 mydb_mysql.sql。 3. 导入 MySQL 数据库 使用 MySQL 命令行界面导入转换后的 mydb_mysql.sql 文件，例如： ``` mysql -u username -p mydb < mydb_mysql.sql ``` 该命令将 mydb_mysql.sql 文件导入到名为 mydb 的 MySQL 数据库中。 注意：该过程可能会丢失某些 PostgreSQL 特定的功能，因此需要手动检查转换的 MySQL 数据库结构是否符合要求。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值