Mysql_dev about SQL Server-side understanding

Recommended books: MySQL must know, head first SQL

View

A view is a virtual table, unlike a table that contains data, and the view contains only queries that retrieve data dynamically when they are used, and do not contain any data on their own

Why use views?
1. Reuse of SQL statements
2. Simplify complex SQL operations
3. Use parts of the table rather than the entire table
4, protect the data. You can give a user access to a specific part of a table instead of access to the entire table (for example, when a bank outsourcing some items to an outsourced company only wants the outsourcing company to see if the user name in the user table is available, and you don't want the user to see the details of the user, you can use the view)
5. Change data format and representation

Rules and restrictions for views
1, must be the same as the table, the view must be uniquely named
2, in a database, the number of views that can be built is unlimited
3. Views can be nested, that is, you can build new views with data queried from other views
4. The order by can be used in the view, but if the select that retrieves data from the view also contains an order by, the order by in that view will
Be overwritten
5. The view cannot be indexed or has an associated trigger or default value
6. Views and tables can be used together, such as writing a query that joins tables and views

To create a view:
CREATE VIEW viewname
As
SELECT Customers.cust_name,customers.cust_id,count (orders.order_num) from customers
Left OUTER JOIN orders on customers.cust_id = orders.cust_id
GROUP by customers.cust_id

Working with Views:
SELECT from ViewName

Delete a view
DROP VIEW viewname

View Updates
Insert Update Delete on a view affects its base table because it does not contain any data
• Not all views can be updated (only one table is updated at the time of the update)
• Contains groupings (group by and having)
• Join Query
• Sub-query
• Aggregation Functions
DISTINCT
• Do not make the update operation on the view because it is inefficient. Views are primarily used for querying

Stored Procedures

A stored procedure is a set of SQL assemblies that are written to complete a particular function and run on the database side.

Stored Procedure Benefits
1. Simplify complex operations by encapsulating processing in easy-to-use units
2. Improve performance. Using stored procedures is faster than using separate SQL statements
3. Security, the caller only needs to invoke the specified stored procedure without worrying about the contents of the stored procedure

Stored Procedure Disadvantages:
1. Writing Complex
2. If you do not have the appropriate permissions, you will not be able to create stored procedures

Create a stored procedure
DELIMITER//Defining separators
Create PROCEDURE Productavg () Creating a stored procedure
Begin stored Procedure Start location
SELECT AVG (Prod_price) as avgprice from products; Statements for Stored procedures
end//the end of the stored procedure, followed by the delimiter defined earlier
DELIMITER; Returns the previously defined delimiter to the normal delimiter.

Call a stored procedure
Call Productavg ();

Stored procedure with return value
DELIMITER//
CREATE PROCEDURE productpricing (
Out PL Decimal (8,2),/out represents the return value parameter PL is the variable name DECIMAL is the data type/
Out ph DECIMAL (8,2),
Out PA DECIMAL (8,2)
)
BEGIN
SELECT MIN (prod_price) to PL from products; /into will pay the value to the variable PL/
SELECT MAX (prod_price) into the ph from the products;
SELECT AVG (Prod_price) into PA from products;
end//
DELIMITER;

Call a stored procedure with a return value
Call Productpricing (@pricelow, @priceheight, @priceavg)
SELECT @pricelow, @priceheight, @priceavg

Stored procedure with passed-in parameters
DELIMITER//
CREATE PROCEDURE OrderTotal (
In Ordernum INT,/In for incoming parameter/
Out Total DECIMAL (8,2)
)
BEGIN
SELECT SUM (item_price quantity) into total from orderitems WHERE order_num = ordernum;
end//
DELIMITER;

Call OrderTotal (20005, @total)
SELECT @total

A stored procedure that has a selection process
DELIMITER//
CREATE PROCEDURE ordertotaltax (
in Ordernum INT,/order number */
in tax BOOLEAN,/whether taxes are required * /
Out all DECIMAL (8,2)/Total amount (stored procedure returned) */
)
BEGIN
DECLARE ordertotal DECIMAL (8,2);/Declaration variable: Total amount/
DECLARE Taxvalue FLOAT DEFAULT 0.6; /DECLARE variable: Tax rate */
/Calculate Total price of the specified order/
SELECT SUM (item_price quantity) into OrderTotal from orderitems WHERE order_num = Ordernu M
/If tax is required */
If-then
SELECT OrderTotal + (OrderTotal taxvalue) into OrderTotal;
END IF;
/assigns the current total price to the return parameter */
SELECT ordertotal into total;
end//
DELIMITER;
Call a stored procedure with a selection process
/Invoke (tax)/
Call Ordertotaltax (20005,1, @total);
SELECT @total
/Invoke (do not tax) */
Call Ordertotaltax (20005,0, @total);
SELECT @total

Cycle
DELIMITER//
CREATE PROCEDURE whiletest ()
BEGIN
DECLARE num INT;
DECLARE Total INT DEFAULT 0;
SET num = 0;
While Num <
SET total = total + num;
SET num = num + 1;
END while;
SELECT Total;
end//
DELIMITER;

To delete a stored procedure
DROP PROCEDURE Productavg;

Java calls stored procedures

Https://gist.github.com/shiningguang/437b160974fb07c8faea

Trigger

A trigger is an SQL statement that is automatically executed when MySQL responds to an INSERT, UPDATE, DELETE statement, and only the table supports triggers, which the view does not support.
Information required by the trigger
1. Unique Trigger Name
2. The table associated with the trigger
3. The event that the trigger should respond to (insert? Update Delete ）
4. When the trigger executes (before or after processing)
5. One event of a table can have up to two triggers (before and after processing), so a table has a maximum of 6 triggers
6. If the trigger execution fails before the response, the response is not executed, and the trigger or response execution fails before the response, then the trigger will not execute

Insert Trigger
CREATE TRIGGER Tr_insert_tablea
After INSERT on TableName
For each ROW
INSERT into T_tableb (val) VALUES (New.val);

1. Within the Insert trigger, you can refer to a virtual table named new to access the inserted row
2. In the Before insert trigger, the value in new can also be updated (the value in which the change is inserted is run)
3. For the autogrow column, new will have a value of 0 before the insert is executed, and the newly automatically generated value after execution
/Gets the auto-generated primary key value just inserted */
CREATE TRIGGER T_insert_pk_tablea
After INSERT on T_tablea
For each ROW, SELECT new.id into @id;
/Test Trigger */
INSERT into T_tablea (val) VALUES (' abc ');
SELECT @id;

Delete Trigger
DELIMITER//
CREATE TRIGGER T_delete_tablea
After DELETE on T_tablea/delete trigger */
For each ROW
BEGIN
INSERT into T_tableb (val) VALUES (Old.val);
end//
DELIMITER;
/Test Trigger */
DELETE from T_tablea WHERE id = 2
1. In the Delete trigger code, you can reference an old virtual table to access the deleted row
The values in the 2.OLD table are all read-only and cannot be updated

UPDATE trigger
/change the name in table A to uppercase */
DELIMITER//
CREATE TRIGGER T_update_tablea
Before UPDATE on T_tablea
For each ROW
BEGIN
SET new.val = UPPER (new.val);
end//
DELIMITER;
/Test Trigger */
UPDATE T_tablea SET val = ' xyz ' WHERE id = 1;
SELECT from T_tablea;

1. In the update trigger code, you can refer to a virtual table named old to access the previous value, referencing a table named new to access the newly updated
Value
2. In the befor update trigger, the values in the new table are allowed to be updated (allowing changes to be used in the UPDATE statement)
The values in the 3.OLD table are read-only and cannot be updated
Delete Trigger
DROP TRIGGER Tr_insert_tablea

Transaction

Transactions are supported in MySQL only for databases or tables that use the InnoDB database engine
• Transactions can be used to maintain the integrity of the database, ensuring that a batch of MySQL operations is either fully executed or not executed at all
• Transactions are used to manage INSERT, UPDATE, DELETE statements

Terminology relating to transactions
Transaction (Transaction): Refers to a set of SQL statements
• Rollback (rollback): Refers to the process of revoking a specified SQL statement
• Commit: Writes the results of an unsaved SQL statement to the database
• Retention point (savepoint): Refers to a temporary placeholder set in a transaction that can be rolled back
• The key to transaction processing is to break down the group of SQL statements into logical blocks and specify when the data should be rolled back and when to commit

Rolling back a transaction
START transaction;/ Start Transaction */
DELETE from T_tablea WHERE id = 1;
rollback;/ Transaction rollback/

Commit a transaction
START transaction;/ Start Transaction */
DELETE from T_tablea WHERE id = 1;
rollback;/ Commit TRANSACTION/

Reserved points
START TRANSACTION;
DELETE from T_tablea WHERE id = 4;
SavePoint S1; /Declare a retention point/
DELETE from T_tablea WHERE id = 5;
ROLLBACK to S1; /rollback to S1 reserved points */

Four characteristics of a transaction: ACID
• Atomicity (Atomic): The processing statement that makes up a transaction consists of a logical unit, which is the smallest execution unit
• Consistency (consistent): data is consistent before and after a transaction is executed
• Isolation (Isolated): The processing of one transaction has no effect on another transaction
• Persistence (Durable): The result is permanently recorded in the database when the transaction is successfully processed

Transactions in JDBC
Set the auto-commit transaction state value to false first in code
Conn.setautocommit (FALSE);
Create the SQL statement and execute the first SQL statement
Set a retention point between an SQL statement and the second SQL statement
Conn.setsavepoint ();
Create press the second SQL statement and use STAT to execute the statement
The transaction is rolled back to its previous retention point
Conn.rollback (SP);
Just roll back to the retention point, or not, because before setting the Autocommit property value to False, the program has lost the ability to automatically commit. So when you execute the U, the first SQL statement is not executed. Therefore, when rolling back to the retention point, it is best to add conn.commit () to the program before the retention point is executed. and sets the Autocommit property value to True.
Conn.setautocommit (TRUE);

Index

Index is an important way to optimize database query speed

Index Type:
Normal index: The most basic type of index, no restrictions such as uniqueness
Create INDEX Valindex on T_tablea (Val (20))
Create INDEX Products_index on products (Prod_name (+), Prod_price)
• Unique index: Same as normal so basically the same, but all indexed columns can only appear once and remain unique
Create UNIQUE INDEX valindex2 on T_tableb (Val (20))
• Primary KEY index: A primary key index is a special unique index that is created automatically when a primary key is established
• Full-Text indexing: Full-text indexing can be created on varchar or text types

Disadvantages of the Index
1. Although the index greatly improves the query speed, it can reduce the speed of the update table, such as INSERT, UPDATE, delete operations on the table, because when updating the table, MySQL not only to save the data, but also to save the index file
2. Indexing takes up disk space. If multiple index combinations are created on a large table, the index file expands quickly.
When not to build an index:
It is not necessary to create an index when the result of a query is too large to be summed up, and it is not appropriate to index when the frequency of frequent updates is greater than the frequency of reading. It is not suitable for indexing when the amount of data is small. Only when the amount of data is large (hundreds of thousands of, millions of, tens of millions of)

Mysql_dev about SQL Server-side understanding