SQL must know must NOTE 2

15. Inserting data

1). Data insertion

Insert is used to insert rows into a database table in several ways:

(1) Insert the complete line;

INSERT into Customers VALUES (

' 1000000006 ',

' Toy land ',

' 123 any Street ',

' New York ',

' NY ',

' 11111 ',

' USA ',

Null

Null

）；

(2) Insert part of the line;

INSERT into Customers (cust_id, Cust_name, cust_address, cust_city, Cust_state, Cust_zip, Cust_country)

VALUES (' 100000000006 ', ' Toy land ', ' 123 any Street ', ' New York ', ' NY ', ' 11111 ', ' USA ');

(3) Insert the results of some queries:

Use Insert Select to implement, such as:

INSERT into Customers (cust_id, Cust_contact, Cust_email, Cust_name, cust_address, cust_city, Cust_state, Cust_zip, Cust_ Country)

SELECT cust_id, Cust_contact, Cust_email, Cust_name, cust_address, cust_city, Cust_state, Cust_zip, cust_country from Cus tnew;//column names do not have to match, but the position of the columns must correspond to each other.

Note: inserting multiple lines

Insert usually inserts only one row, to insert multiple rows, to execute multiple INSERT statements, but the Insert SELECT statement is an exception, and inserts are inserted regardless of the number of bars returned by the SELECT statement.

2). Copying from one table to another

You can use the SELECT INTO statement to copy the contents of a table into a completely new table (the table is created at run time).

The difference between Insert select and select INTO:

The former is to insert data from an existing table into another existing table, which copies data from an existing table into another newly created table.

SELECR * into custcopy from Customers;

16. Updating and deleting data

1). Update data

The basic update statements include the tables to be updated, the column names and their new values, and the filter criteria that determine which rows to update.

(1) Updating specific rows in a table

UPDATE Customers SET cust_name = ' Bonnie cobin ' WHERE cust_id = ' 10000005 ';

(2) Update all rows in the table

If no criteria are selected, all rows in the entire table are updated.

However, you can update multiple columns (properties):

UPDATE Customers SET cust_name = ' Bonnie cobin ', cust_email = ' [email protected] ' WHERE cust_id = ' 10000005 ';

By the go: To delete a column's value, you can set it to null:update Customers set cust_email = NULL WHERE cust_id = ' 10000005 ';

2). Delete data

(1) Delete a specific line

DELETE from Customers WHERE cust_id = ' 10000005 ';

(2) Delete all rows

DELETE from Customers;

There is also a way to quickly delete all rows: Use the TRUNCATE TABLE statement, faster!

Before you make a delete or update, test it with a SELECT statement to make sure that you are updating or deleting the correct data, and you usually have to use a statement with a where condition to do the update or delete operation.

17. Create a table

1). Create a table:

General CREATE TABLE USE statement: CREATE TABLE TableName ();

Create Table Products (prod_id char (TEN) not NULL, vend_id char (at $ not null, Prod_name char (+) not null));

Table name:products

Note: A null value, which is not a value, is not an empty string, and the string is a valid value, not a value of none.

2). Update table

Use the ALTER table statement.

This table must exist when the table is updated.

Ie: Add columns to an existing table, which is almost the only operation supported by each database:

ALTER TABLE Vendors ADD vend_phone CHAR (20);

Note: Because changes to the database cannot be undone, you must back up before you change the table.

3). Delete Table

Use the DROP TABLE statement to delete a database table.

Deleting a table cannot be undone, and the table will be permanently deleted.

Tip: Use relationship rules to place accidental deletions

Many DBMS allow rules to be enforced to prevent the deletion of tables associated with other tables. When implementing these rules, if a table with related relationships is deleted by the DROP TABLE statement, it is forced to block deletion unless the relationship is deleted.

4). Renaming tables

DB2, MariaDB, MySQL, Oracle, and PostgreSQL users use rename, SQL serversp_rename stored procedures, SQLite users using the ALTER TABLE statement

SQL Server users use the sp_rename statement, and SQLite users use the ALTER TABLE statement.

18. Working with views

1). View

A view is a virtual table, and unlike a table that contains data, a view is simply a query that contains dynamically retrieved data when it is used.

i.e.: there is a query statement:

SELECT Cust_name, cust_contact from Customers, Orders, OrderItems

WHERE customers.cust_id = orders.cust_id and Orderitems.order_num = orders.order_num and prod_id = ' RGAN01 ';

If the entire query is packaged into a virtual table named Productcustomers, you can easily retrieve the same data:

SELECT Cust_name, cust_contact from productcustomers WHERE prod_id = ' RGAN01 ';

So productcustomers, as a view, contains no columns or data and contains only one query.

What are the benefits of using a view? (1) SQL query statements can be reused to simplify complex SQL operations, after writing a query, you can easily reuse it without having to know its basic query details, (2) Use part of the table rather than all of the table, (3) protect the data, you can grant users access to specific parts of the table, rather than the entire table access rights ; (4) Change the data format and representation, and the view can return data that is different from the representation and format of the underlying table. After the rabbit is created, it can be used in the same way as the table.

Rules and restrictions for using views: (1) The view must be uniquely named, (2) The view must be created with sufficient access rights, which are usually granted by the database administrator;

(3) The view can be nested and (4) reduce the performance of the query, so it should be thoroughly tested before the product environment is used, and (5) Many DBMS prohibit the use of the ORDER BY clause in the view query. (6) The view cannot be indexed, nor can it have associated triggers or default values.

2). Create a View

The view is created with the CREATE VIEW viewname statement, to delete the view, you can use the Drop view viewname statement, and if you want to overwrite (or update) The view, you must first delete it and then recreate it.

Ie:create VIEW Productcustomers as

SELECT Cust_name, Cust_contact, prod_id from Customers, Orders, OrderItems

WHERE customers.cust_id = orders.cust_id and orderitems.order_num = Orders.order_num;

Description: The syntax above creates a view productcustomers, joins 3 tables, and returns a list of all customers who have ordered any product. If you execute select * from Productcustomers, customers who have ordered any product will be listed. To retrieve only the customers who ordered the product RGAN1, the executable:

SELECR Cust_name, Cust_contact, prod_id from productcustomers Where prod_id = ' RGAN01 ';

• Use the view to reformat the retrieved data;
• You can use a view to filter unwanted data:

Ie:create VIEW customeremaillist as SELECT cust_id, Cust_name, cust_email from Customers WHERE cust_email are not NULL;

Select * from Customeremaillist;

• Working with views and calculated fields:

SELECT prod_id, quantity, Item_price,

Quantity*item_price as Expanded_price

From OrderItems

WHERE order_num = 20008;

Output:

prod_id Quantity Item_price Expanded_price

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

RGAN01 5 4.9900 24.9500

BR03 5 11.9900 59.9500

BNBG01 10 3.4900 34.9000

BNBG02 10 3.4900 34.9000

BNBG03 10 3.4900 34.9000

Working with Views:

CREATE VIEW orderitemsexpanded as

SELECT Order_num,

prod_id, quantity, Item_price,

Quantity*item_price as Expanded_price

from OrderItems;

Input:

SELECT * from orderitemsexpanded WHERE order_num = 20008;

Output:

Order_num prod_id Quantity Item_price expanded_price

------------------------------------------------20008 RGAN01 5 4.99 24.95

20008 BR03 5 11.99 59.95

20008 BNBG01 10 3.49 34.90

20008 BNBG02 10 3.49 34.90

20008 BNBG03 10 3.49 34.90

19. Using Stored Procedures

Scene: In order to process orders, check to ensure that the inventory of the corresponding items;

If the item is in stock, it needs to be scheduled, no longer sold to others, and the item's data is reduced to reflect the correct inventory amount;

If no items in stock need to be ordered, some interaction with the supplier is required;

It is necessary to inform the corresponding customer about which items are in storage (and can be shipped immediately) and which items are unsubscribe.

1). Stored Procedures

The above scenario requires multiple SQL statements for multiple tables, sometimes the order is not fixed, and each time the processing to execute these statements, a bit cumbersome, in order to be used more than once, we can create a stored procedure. In simple terms, a stored procedure is one or more statements that are saved for later use. It can be considered a batch file.

2). Why use stored procedures?

(1) by encapsulating the processing in an easy-to-use unit, complex operations can be simplified (as described in the previous example).

The consistency of the data is ensured by not requiring a series of processing steps to be repeatedly established. If all developers and applications use the same stored procedure, the code used is

is the same.

The extension of this point is to prevent errors. The more steps you need to perform, the greater the likelihood of errors. Prevent errors to ensure data consistency.

(2) Simplify the management of changes. If the table name, column name, or business logic (or other content) changes, you only need to change the code of the stored procedure. The people who use it don't even need to know

These changes.

The extension of this point is security. Restricting access to underlying data through stored procedures reduces the chances of data corruption (data corruption, either unconsciously or otherwise).

(3) Because stored procedures are typically stored in a compiled form, the DBMS handles fewer commands and improves performance.

There are some SQL elements and attributes that can be used only in a single request, and stored procedures may use them to write code that is more powerful and flexible.

So there are three main benefits of using stored procedures, simple, secure, and high performance.

3). Execute Stored Procedure

EXECUTE statement

EXECUTE addnewproduct (' JTS01 ', ' stuffed Eiffel Tower ', 6.49, ' Plush stuffed toy with the text La tour Eiffel in red White and Blue ');

The example above is to execute a stored procedure and add a new product to the table products.

And as we execute the above statement, the stored procedure does the work of validating the passed data, ensuring that all 4 parameters have values, generating a unique ID for the primary key, inserting the new product into the Products table, and storing the generated primary key and the passed data in the appropriate column.

4). Create a stored procedure

SQL Server version:

Input:

CREATE PROCEDURE Mailinglistcount

As

DECLARE @cnt integer//declares a local variable named @cnt using the DECLARE statement (all local variables in SQL start with @)

SELECT @cnt = COUNT (*)

From Customers

WHERE not Cust_email is NULL; RETURN @cnt;

Call SQL Server:

DECLARE @ReturnValue INT

EXECUTE @ReturnValue = Mailinglistcount;

SELECT @ReturnValue;

20. Managing Transaction Processing

1). Transaction processing (transaction Processing): maintains database integrity by ensuring that batches of SQL operations are either fully executed or not executed at all. Transaction processing is a mechanism for managing SQL operations that must be performed in batches to ensure that the database does not contain incomplete operational results. With transactions, you can ensure that a set of operations does not stop halfway, that they are either completed or not executed at all (unless explicitly instructed). If no error occurs, the entire set of statements is submitted to the database table, and if an error occurs, a fallback is made to restore the database to a known and secure state.

Among them, there are several repeated occurrences of the key words:

A transaction (transaction) refers to a set of SQL statements;

Fallback (rollback) refers to the process of revoking a specified SQL statement;

Commit (commit) refers to writing the results of an unsaved SQL statement to a database table;

Tip: Which statements can be rolled back?

Transaction processing is used to manage insert, UPDATE, and DELETE statements. The SELECT statement cannot be rolled back (the fallback SELECT statement is not necessary), nor can it return

Exit Create or drop operation. These statements can be used in transactions, but they are not undone when they are rolled back.

2). Control transaction Processing

The key to managing a transaction is to break down the group of SQL statements into logical blocks and specify when the data should be rolled back and when it should not be rolled back.

In SQL Server, the identity processing block begins and ends with the following:

Begin Transaction

...

Commit Transaction

The SQL ROLLBACK command is used to rollback (REVOKE) The SQL statement:

Ie:delete from Orders;

rollback;//do this to restore deleted rows back

General SQL statements can be directly executed and written directly to the database table, which is called implicit commit (implicit commit), that is, the commit (write or save) operation is automatic.

But some also need to be explicitly committed, using the COMMIT statement:

Ie:begin TRANSACTION

DELETE orderitems WHERE order_num = 12345;

DELETE Orders WHERE order_num = 12345;

COMMIT TRANSACTION

For some complex transactions, it is not possible to commit at one time, you may need to commit the module, you can fall back to a point, and to support the transaction that is part of the fallback, you must place the placeholder in the appropriate place in the transaction block, so that if fallback is required, you can fall back to a placeholder. Each retention point should have his or her own unique identification name, so that the DBMS knows where to fall back when it is rolled back.

Ie: (SQL Server)

SAVE TRANSACTION delete1;

ROLLBACK transcation delete1;

21. Using Cursors

1). Cursors

Sometimes, you need to forward or backward one or more rows in the retrieved row, which is where the cursor is used. A cursor (sursor) is a database query stored on a DBMS server that is not a SELECT statement but a result set retrieved by the statement. After the cursor is stored, the application can scroll as needed or the data in the browser.

Cursors are primarily used in interactive applications where users need to scroll through the data on the screen and browse or make changes to the data.

2). Using Cursors

Before you use a cursor, you must declare (define) it. This process does not actually retrieve the data, it simply defines the SELECT statement and cursor options to use. Once declared, the cursor must be opened for use. This process actually retrieves the data from the previously defined SELECT statement. For cursors filled with data, the rows are fetched (retrieved) as needed. When the cursor is used, the cursor must be closed and, if possible, the cursor will be released (depending on the specific DBMS). After you declare a cursor, you can open and close the cursor frequently as needed. When the cursor is open, the fetch operation can be performed frequently as needed.

To create a cursor (using the DECLARE statement):

DECLARE custcursor cursor for SELECT * from Customers WHERE cust_email is null;//create a cursor for a customer with empty message to find the address of the vacant e-mail.

Open cursor (using OPEN CURSOR statement)

Using a FETCH statement to access cursor data, fetch indicates which rows to retrieve, where to retrieve them, and where to place them.

Close cursor (using CLOSR CURSOR statement)

Once the cursor is closed, it needs to be opened again if it is to be used again, and no declaration is required for the second use.

22. Advanced SQL Features

1). Constraints (Constraint)

Rules that govern how database data is inserted or processed. The DBMS enforces referential integrity by imposing constraints on the database. The primary key (Primary key) is a special constraint. A foreign key is a column in a table whose value must be listed in the primary key of another table. Foreign keys are an extremely important part of guaranteeing referential integrity. A unique constraint is used to guarantee that the data in a column (or set of columns) is unique. A unique constraint is similar to a primary key, but differs greatly from the primary key:

A table can contain multiple unique constraints, but only one primary key is allowed per table.

The unique constraint column can contain null values.

The unique constraint column can be modified or updated.

The value of a unique constraint column can be reused.

Unlike a primary key, a unique constraint cannot be used to define a foreign key. Indexes are used to sort data to speed up search and sort operations.

2). Index

Before you create an index, you should keep the following in mind:

Indexes improve the performance of retrieval operations, but reduce the performance of data insertions, modifications, and deletions. When performing these operations, the DBMS must dynamically update the index.

Index data can take up a lot of storage space.

Not all data is suitable for indexing. Data that does not have much value (such as states) is less likely to be indexed than data with more possible values (such as first or last name).

Indexes are used for data filtering and data sorting. If you often sort the data in a particular order, the data might be appropriate for indexing.

Creating index: Create index Prod_name_ind on products (prod_name); The index Prod_name_ind is created on the product table, and the columns contained in the index are given in parentheses after the table name.

3). Trigger

A trigger is a special stored procedure that executes automatically when a particular database activity occurs. Triggers can be associated with insert,update,delete actions on a particular table. Unlike stored procedures (stored procedures are simply stored SQL statements), triggers are associated with a single table. Triggers that are associated with an insert operation on the Orders table are executed only when the row is inserted in the Orders table. Here are some common uses of triggers:

Ensure data consistency. For example, all state names are converted to uppercase in an INSERT or update operation.

Perform activities on other tables based on changes to a table. For example, an audit trail record is written to a log table whenever a row is updated or deleted.

Perform additional validation and fallback data as needed. For example, to ensure that a customer's available funds are not over-qualified, and if they are exceeded, the insert is blocked.

Calculates the value of a column or updates a timestamp.

Create TRIGGER: (The following example creates a trigger that converts the Cust_state column in the Customers table to uppercase for all insert and update operations)

CREATE TRIGGER Customer_state

On Customers

For INSERT, UPDATE

As

UPDATE Customers

SET cust_state = Upper (cust_state)

WHERE customers.cust_id = inserted.cust_id;

SQL must know must NOTE 2