SPJ of computer experiment of database principle and Application

Posted by adguru on Thu, 10 Feb 2022 14:19:01 +0100

preface

Recently, the teacher of computer class has gone through [database establishment and maintenance], [database table and index creation], [single table query] and [multi table query], but his mind is still messy, so he sorted out this note for future sorting and review. The main content is the query of single table and multi table.

Note: the following personal class notes are for reference only. If there is any mistake, we look forward to the criticism and correction of the majority of netizens~

1, Relational model

All the following operations are for processing the data:

When creating a table, you must pay attention to the order. For example, if you first create an SPJ table, there will be an error in foreign key calling (because the three tables s, P and j are not established, foreign keys cannot be called), but you can also select the method of partial execution first, and then select and execute after foreign key operation. In case of errors, the code sequence shall be consistent as far as possible.

The table code is as follows:

CREATE DATABASE SPJ;

USE SPJ;

CREATE TABLE S(
	SNO NCHAR(10),
	SNAME NCHAR(10),
	SADDR NCHAR(50),
	PRIMARY KEY(SNO)
);

CREATE TABLE J(
	JNO NCHAR(10),
	JNAME NCHAR(20),
	JCITY NCHAR(20),
	BALABCE DECIMAL(5,2),
	PRIMARY KEY(JNO)

);

CREATE TABLE P(
	PNO NCHAR(10),
	PNAME NCHAR(20),
	COLOR NCHAR(10),
	WEIGHTS INT,
	PRIMARY KEY(PNO)
);
CREATE TABLE SPJ(
	SNO NCHAR(10),
	
	PNO NCHAR(10),
	JNO NCHAR(10),
	PRICE NCHAR(100),
	QTY INT,
	
	PRIMARY KEY(SNO,JNO,PNO),
	FOREIGN KEY(SNO) REFERENCES S(SNO),
	FOREIGN KEY(PNO) REFERENCES P(PNO),
	FOREIGN KEY(JNO) REFERENCES J(JNO)
);

Tip: there are some deficiencies. Some integrity rules are not defined, such as * "NOT NULL" or "CHECK * (a certain amount > 0)". Because it does not affect subsequent operations, it will not be added first... (well, I'm a little lazy)

The following is the code for inserting table data, as shown in the figure:

INSERT INTO S VALUES('S1','Raw material company','23 north gate, Nanjing')
INSERT INTO S VALUES('S2','Hongxing steel pipe factory','100 Pudong, Shanghai')
INSERT INTO S VALUES('S3','Parts manufacturing company','Nanjing Dongjin Road')
INSERT INTO S VALUES('S4','Accessories company','58 Shangrao, Jiangxi')
INSERT INTO S VALUES('S5','Raw material factory','88 Hongxing Road, Beijing')
INSERT INTO S VALUES('S8','Dongfang accessories factory','Tianjin Yexi Road')


INSERT INTO P VALUES('P1','a steel bar','black','25')
INSERT INTO P VALUES('P2','Steel pipe','white','26')
INSERT INTO P VALUES('P3','Nut','red','11')
INSERT INTO P VALUES('P4','Screw','yellow','12')
INSERT INTO P VALUES('P5','gear','red','18')


INSERT INTO J VALUES('J1','The Oriental Pearl','Shanghai','0.00')
INSERT INTO J VALUES('J2','Refinery','Changchun','-11.20')
INSERT INTO J VALUES('J3','Metro 3','Beijing','678.00')
INSERT INTO J VALUES('J4','Pearl Line','Shanghai','456.00')
INSERT INTO J VALUES('J5','Steelmaking site','Tianjin','123.00')
INSERT INTO J VALUES('J6','Nanpu Bridge','Shanghai','234.70')
INSERT INTO J VALUES('J7','Hongxing cement plant','Jiangxi','343.00')

INSERT INTO SPJ VALUES('S1','P1','J1','22.60','80')
INSERT INTO SPJ VALUES('S1','P1','J4','22.60','60')
INSERT INTO SPJ VALUES('S1','P3','J1','22.80','100')
INSERT INTO SPJ VALUES('S1','P3','J4','22.80','60')
INSERT INTO SPJ VALUES('S3','P3','J5','22.10','100')
INSERT INTO SPJ VALUES('S3','P4','J1','11.90','30')
INSERT INTO SPJ VALUES('S3','P4','J4','11.90','60')
INSERT INTO SPJ VALUES('S4','P2','J4','33.80','60')
INSERT INTO SPJ VALUES('S5','P5','J1','22.80','20')
INSERT INTO SPJ VALUES('S5','P5','J4','22.80','60')
INSERT INTO SPJ VALUES('S8','P3','J1','13.00','20')
INSERT INTO SPJ VALUES('S1','P3','J6','22.80','6')
INSERT INTO SPJ VALUES('S3','P4','J6','11.90','6')
INSERT INTO SPJ VALUES('S4','P2','J6','33.80','8')
INSERT INTO SPJ VALUES('S5','P5','J6','22.80','8')

Note: you should also pay attention to the order when inserting the table!

2, Use SQL statements to create and modify basic tables

1. Add field name of basic table

Add a Tel attribute to the basic table S

The code is as follows (example):

ALTER TABLE S ADD TELE CHAR(13)

Execution effect diagram: (the red part is the field just added, because there is no assignment, it is null by default)

2. Add field name of basic table

Delete the contact number (TEL) attribute in the basic table S
The code is as follows (example):

ALTER TABLE S DROP COLUMN TELE

After execution, the newly added field [TELE] will be deleted

3. Index

The general format of the index creation statement is:

Create [unique] [clustered | nonclustered] index < index name > on {< table name > | view name >} (< column name > [ASC | desc] [... n])

Where UNIQUE refers to UNIQUE index, CLUSTERED refers to CLUSTERED index and NONCLUSTERED refers to NONCLUSTERED index
The index can be built on one or more columns of the table or view. The column names are separated by commas. After each column name, the order of index values can be specified by order (ASC ascending, DESC descending)

For example: make a non clustered index on the SPJ table, in which SNO is arranged in descending order and PNO and JNO are arranged in ascending order
The code is as follows:

CREATE NONCLUSTERED INDEX SPJ_SNO_PNO_JNO ON SPJ(SNO DESC,PNO ASC,JNO ASC)

Delete index: use DROP keyword

DROP INDEX SPJ_SNO_PNO_JNO ON SPJ(SNO DESC,PNO ASC,JNO ASC)

2, Single table query of database table using SQL statement

Query is the core content of database application. SELECT is used for query in sql

1. Query the specified column

Example: query all information of parts
The code is as follows:

SELECT * FROM S

Where [*] refers to all field names, equivalent to:

SELECT SNO,SNAME,SADDR FROM S

2. Query for expression calculation and changing expression mode

The order of each column in the target expression can be inconsistent with that in the table. The display order can be changed when the user needs to query. (no demonstration here)

In addition, the target list of the SELECT clause can be not only the attribute column in the table, but also the relevant expression.

Example: output the price as the [original price] column and the QTY as the [quantity after discount] (random examples have no meaning)

The code is as follows:

SELECT PRICE AS original price,0.8*QTY AS Quantity after discount FROM SPJ

(as can be omitted)

The operation results are shown in the figure below:

3. Query to eliminate duplicate rows

In the SELECT query statement, the first row has the option of ALL|DISTINCT. ALL means to query ALL rows, and the default state is; Duplicate rows will be cancelled after DISTINCT query.

For example:
1. Query the part number of all parts
The code is as follows:

SELECT DISTINCT SNO FROM SPJ

The operation results are shown in the figure below:

If DISTINCT is not added, it defaults to ALL

SELECT  SNO FROM SPJ

The result output is as follows:

Obviously, the result of the second graph is not what we want. There are too many repeated lines. It can be seen that DISTINCT is very important in statistics

4.WHERE condition query

The query of records that meet the specified conditions can be implemented through the WHERE clause. The common query criteria of WHERE clause are shown in the figure:

5. Grouping query and Sorting Query

*Grouping query: * GROUP BY clause can group the rows of the query result table according to the principle of equal values of one or more columns. The purpose of query result grouping is to * * refine the action object of the set function** If you need to filter these groups according to certain conditions after grouping, you can use the HAVING short sentence to specify the filtering conditions.

Note: HAVING condition refers to the condition required to be set after grouping, while WHERE refers to the condition set for all contents in the query table (or multiple tables).

*Sort query: * rearrange the query results by specifying the ascending (ASC) or descending (DESC) order of one or more field values through the ORDER BY clause. (default ascending order)

6. Experimental content

1. Query the number of suppliers for J1, the maximum quantity, minimum quantity and average quantity of parts provided.

The code is as follows:

SELECT COUNT(JNO)Number of suppliers ,MAX(QTY)Maximum quantity,MIN(QTY)Minimum quantity,AVG(QTY)Average quantity
FROM SPJ
WHERE JNO='J1'

Note: AS is omitted here

2. Query the supplier number SNO that supplies parts to project J1 and the part number is P1.

The code is as follows:

SELECT SNO FROM SPJ
WHERE JNO='J1' AND PNO='P1'

3. Query the supplier name in Shanghai, assuming that the SADDR column of the supplier relationship starts with the city name.

The code is as follows:

SELECT SNAME  FROM S
WHERE SADDR LIKE 'Shanghai%'

4. Query the project number, part number and quantity with 100 ~ 1000 parts.

The code is as follows:

SELECT JNO,PNO,QTY
FROM SPJ
WHERE QTY BETWEEN 100 AND 1000

5. The query gives more than three (including three) projects, the supplier name of the loan and the number of projects provided (note that providing multiple parts of one project can be counted as multiple projects).

The code is as follows:

SELECT DISTINCT SNO, COUNT(JNO)
FROM SPJ
GROUP BY SNO HAVING COUNT(JNO)>=3

The operation results are shown in the figure below:

6. Query the project number, name and city without positive balance, and the results are arranged in ascending order by project number.

The code is as follows:

SELECT JNO,JNAME,JCITY
FROM J
WHERE BALANCE <= 0
ORDER BY JNO

3, Multi table query of database tables using SQL statements

1. Join query and merge query between multiple tables

Join query: if a query involves two or more tables at the same time, it is called join query.
According to their different connection modes, they can be divided into equivalent and non equivalent connection, self connection, external connection, combined connection and so on.

Equivalent connection and non equivalent connection: as previously learned, the operation method of relational algebra is implemented.

In SQL language, the general format is:

Table name 1 Column name 1 < comparison operator > table name 2 Column name 2

When the join operator is "=", it is called equivalent join, otherwise it is non equivalent join.

Natural join: the table joins itself.
External connection: in order to save the record information to be deleted for unconditional connection.

External connection symbol: FULL[OUTER] JOIN
Left outer join symbol: LEFT[OUTER] JOIN
Right outer join symbol: RIGHT[OUTER] JOIN

Merge query: merge query results. Use the UNION operator to combine data from different queries to form a query result with comprehensive information. UNION will automatically eliminate duplicate data. It must be noted that the table structure used by each sub query participating in the consolidated query results should be the same, that is, the data number of each sub query should be the same, and the corresponding types should be consistent.

2. Nested query of multiple SELECT statements

Subquery with IN predicate

Basic syntax:

Expression [not] in (subquery)

In syntax, the simplest form of an expression is a column name or constant. This semantics is to judge whether the value of an expression is in the sub query result.

Subquery with comparison operator

Basic syntax:

expression θ some (subquery)
expression θ all (subquery)

In grammar, θ Are comparison operators: <, >, > =, < =, =, < >. Semantics is to compare the value of an expression with the result of a subquery:
·If the value of the expression satisfies at least one value of the subquery result θ Relationship, the expression θ The result of "some" is true;
·If the value of the expression is satisfied compared with all the values of the sub query results θ Relationship, then "expression" θ The result of "all (sub query)" is true;

be careful:
When expression = some,
Equivalent to "expression in" (subquery)“

But not in and "< > some" have different meanings. Not in corresponds to "< > all"

Subquery with EXISTS predicate

Basic syntax:

[not] exists (subquery)

Semantics is whether there are tuples in the sub query. exists means that the returned result of the sub query statement in () is not empty, indicating that the main sql statement will be executed if the where condition is true. If it is empty, it means that the where condition is not true, and the sql statement will not be executed.

However, not Exists can realize many new functions
On the contrary, not exists and exists. If the result of the sub query statement is empty, that is, if it does not meet the conditions, it means that the where condition is true and the sql statement is executed

3. Experimental content

1. Find the full name of the project using P3 parts

The code is as follows:

SELECT DISTINCT JNAME 
FROM J,SPJ
WHERE SPJ.PNO = 'P3' AND SPJ.JNO = J.JNO

You can also use the IN predicate, as follows:

SELECT DISTINCT JNAME 
FROM J
WHERE JNO IN
	(SELECT JNO 
	FROM SPJ
	WHERE PNO='P3' )

2. It is required to use at least the engineering number JNO with part numbers P3 and P5

The code is as follows:

SELECT DISTINCT X.JNO
FROM SPJ X, SPJ Y
WHERE X.JNO=Y.JNO AND X.PNO='P3' AND Y.PNO='P5'

3. Find the project name of all parts used

The code is as follows:

SELECT JNAME
FROM J
WHERE NOT EXISTS (SELECT * FROM P
					WHERE NOT EXISTS(SELECT * FROM SPJ
										WHERE JNO=J.JNO AND PNO=P.PNO ))

4. Count the total number of engineering parts (more than three kinds) and the total number of parts in Shanghai. The query results shall be arranged in ascending order according to the number of parts, and those with the same number shall be arranged in descending order according to the total quantity.

The code is as follows:

SELECT SPJ.JNO,COUNT(DISTINCT PNO) AS COUNT_PNO, SUM(QTY) AS SUM_QTY
FROM J,SPJ
WHERE JCITY = 'Shanghai' AND J.JNO=SPJ.JNO
GROUP BY SPJ.JNO 
HAVING COUNT(DISTINCT PNO)>3
ORDER BY 2,3 DESC

Method 2:

SELECT SPJ.JNO,COUNT(DISTINCT PNO) AS COUNT_PNO, SUM(QTY) AS SUM_QTY
FROM J INNER JOIN SPJ 
		ON J.JNO=SPJ.JNO AND JCITY='Shanghai'
GROUP BY SPJ.JNO 
HAVING COUNT(DISTINCT PNO)>3
ORDER BY 2,3 DESC

5. The engineering number JNO of P3 and P5 parts shall not be used for retrieval, and the retrieval results shall be stored in the table STORE

The code is as follows;

SELECT JNO INTO STORE 
FROM J
WHERE JNO NOT IN (SELECT X.JNO FROM SPJ X, SPJ Y 
				WHERE X.PNO='P3' AND Y.PNO='P5'AND X.JNO=Y.JNO)

summary

1. Pay attention to data backup during coding to facilitate subsequent use.
2. Remember to select execute when executing the statement.
3. For a problem, the inner join method is considered first. If it cannot be solved, then the sub query is considered. Finally, the Cartesian product is considered (this method is generally not used).

(emmm, for the time being, it will be supplemented later. It is used for personal review and can be used for reference)

Topics: Database SQL

Programmer Think

SPJ of computer experiment of database principle and Application

preface

1, Relational model

2, Use SQL statements to create and modify basic tables

1. Add field name of basic table

2. Add field name of basic table

3. Index

2, Single table query of database table using SQL statement

1. Query the specified column

2. Query for expression calculation and changing expression mode

3. Query to eliminate duplicate rows

4.WHERE condition query

5. Grouping query and Sorting Query

6. Experimental content

3, Multi table query of database tables using SQL statements

1. Join query and merge query between multiple tables

2. Nested query of multiple SELECT statements

3. Experimental content

summary

Hot Topics