Shulou(Shulou.com)06/03 Report--

This article introduces the relevant knowledge of "online Mall Database Commodity Category data Operation method course of SQL practice". In the operation process of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Project description

Under the environment of the rise of e-commerce, establishing the use of the Internet to open up sales channels, helping enterprises to adjust the commodity structure in time, and assisting dealers to open the information portal of goods sources has become an effective solution to solve the poor flow of information. E-commerce is beneficial for enterprises to change their management mechanism, establish a modern enterprise system, and improve their sales level and competitiveness. Realize a series of online shopping services, such as browsing goods, purchasing goods, creating orders, viewing all kinds of new products, special offers, hot-selling goods and so on. Message, customer management, commodity management, commodity information search, order management, commodity classification management, announcement / feedback management and other online shopping services, this project is to achieve the operation of online mall user information forms.

The database operation requirements of the online mall system are as follows:

1) Database EshopDB.

2) the parent table of the commodity category is EPType, and the table structure is shown in Table J2-40-1.

Table J2-40-1 EPType Table

Field name field description data type allowed to be empty memo EPTID category IDint No primary key EPTName category name character (50) No

3) EPType basic data, as shown in Table J2-40-2.

Table J2-13-2 EPType basic data

EPTIDEPTName1 blouse 2 hat 3 children's wear

4) the commodity category subtable ECType, the structure of which is shown in Table J2-40-3.

Table J2-40-3 ECType table

Field name field description data type is allowed to be empty ECTID category IDint No primary key EPTID parent category IDInt No Foreign key, refer to EPType table ECTName category name character (50) No

5) Table ECType basic data, as shown in Table J2-40-4.

Table J2-40-4 ECType basic data

ECTIDEPTIDECTName11 shirt 21 sportswear 31 coat 42 thermal cap 52 sports cap 63 boys 73 girls (1) Task description

* * Task 1: creating online mall database with SQL language

1) create a database EshopDB to determine whether there is a database with that name in the system, and delete it if so; if not, create the database.

2) the initial value of the main database file 10MB, the maximum 30MB, incremented by 15%.

3) the initial value of the log file is 5MB, and the maximum is 20MB, which grows automatically.

IF DB_ID ('EshopDB') IS NOT NULL DROP DATABASE EshopDBGOCREATE DATABASE EshopDBON PRIMARY (NAME=EshopDB, FILENAME='D:\ xxxx\ EshopDB.mdf', SIZE=10MB, MAXSIZE=30MB, FILEGROWTH=15%) LOG ON (NAME=EshopDB_log, FILENAME='D:\ xxxx\ EshopDB_log.ldf', SIZE=5MB, MAXSIZE=20MB)

* * Task 2: creation of EPType parent table and ECType child table of commodity categories in SQL language

1) create the database table according to the structure of table J2 # 40 # 1 and table J2 # 40 # 3 provided, and set up primary key and foreign key.

CREATE TABLE EPType (EPTID INT NOT NULL PRIMARY KEY, EPTName NVARCHAR (50) NOT NULL,) CREATE TABLE ECType (ECTID INT NOT NULL PRIMARY KEY, EPTID INT NOT NULL, ECTName NVARCHAR (50) NOT NULL, FOREIGN KEY (EPTID) REFERENCES EPType (EPTID))

* * Task 3 operations on the parent table of commodity categories EPType and child table ECType of commodity categories in SQL language

1) create a view to display the subcategory records under the hat category.

2) insert the records of women's leather shoes, men's sports shoes and children's shoes in the sub-table ECType of commodity categories to improve the records of EPType footwear in the parent table of commodity categories.

3) count out the total number of commodity subcategories.

INSERT INTO EPType VALUES (1m 'blouse'), (2 'hat'), (3) INSERT INTO ECType VALUES ('shirt'), (1 'shirt'), ('sportswear'), (3 'jacket'), (4 'warm cap'), (5 'sports cap'), (6 'boy'), (3 'girl') CREATE VIEW HATAS SELECT EPType.EPTName,ECType.ECTName FROM EPType) ECType WHERE ECType.EPTID= (SELECT EPTID FROM EPType WHERE EPType.EPTName=' hat') AND EPType.EPTName=' hat 'GOSELECT * FROM HATINSERT INTO EPType VALUES') INSERT INTO ECType VALUES (8pence4 'female shoes'), ('men's sneakers'), (10meme 4 'children's shoes') SELECT COUNT (*) FROM ECType "online Mall Database Commodity Category data Operation method course of SQL practice", so much for this introduction. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.