Chapter III to be sorted out

Posted by optikalefx on Mon, 20 Sep 2021 17:36:37 +0200

1 View

  • The difference between a view and a table: "Is the actual data saved?"
  • "Views are not tables. Views are virtual tables. Views depend on tables."

1.1 Create View

CREATE VIEW <View Name>(<Column Name 1>,<Column Name 2>,...) AS <SELECT Sentence>

-- Create views based on multiple tables without using join
-- create The column name of can be saved
CREATE VIEW view_shop_product(product_type, sale_price, shop_name)
SELECT product_type, sale_price, shop_name
  FROM product,
 WHERE product.product_id = shop_product.product_id;

-- Queries based on the above views
SELECT sale_price, shop_name
  FROM view_shop_product
 WHERE product_type = 'clothes';
  • Column 1 in the ELECT statement is column 1 in the view, and column 2 in the SELECT statement is column 2 in the view
  • The column name of the view is defined in the list after the view name
  • View names need to be unique in the database and cannot be duplicated with other views and tables
  • Views can be based not only on real tables, but also on views. However, for most DBMS s, focusing on diagrams can degrade SQL performance.
  • ORDER BY statements cannot be used when defining views in general DBMS s because, like views and tables, data rows are out of order
  • The definition of a view in MySQL allows the use of ORDER BY statements, but if you select from a particular view and the view uses its own ORDER BY statement, the ORDER BY in the view definition will be ignored

1.2 Modify view structure

ALTER VIEW <View Name> AS <SELECT Sentence>

-- exam1
ALTER VIEW productSum
        SELECT product_type, sale_price
          FROM Product
         WHERE regist_date > '2009-09-11';

1.3 Update View Content

-- Modified above productsum Exclude the above restrictions
UPDATE productsum
   SET sale_price = '5000'
 WHERE product_type = 'Office Supplies';

  • Only one data in the result is updated, because the view is only a window of the original table, it can only modify what is visible through the window
  • Modifying the original table in this way is not recommended

1.4 Delete View

DROP VIEW <View Name 1> [ , <View Name 2> ...]

-- exam
DROP VIEW productSum;

2 Subqueries

2.1 Nested Subquery

SELECT product_type, cnt_product
        FROM (SELECT product_type, 
                      COUNT(*) AS cnt_product
                FROM product 
               GROUP BY product_type) AS productsum
       WHERE cnt_product = 4) AS productsum2;
  • Table name from sub-query after AS
  • Subqueries are one-time, not stored in the storage media like views, but disappear after the SELECT statement executes
  • As the number of nested layers of subqueries overlap, SQL statements can be difficult to understand and inefficient to execute, so try to avoid this use

2.2 Scalar Quantum Query

  • Scalar quantum query: A select statement executed only returns one value, that is, a column of a specific row in a table
  • Scalar quantum queries can be used anywhere a single value can be used
-- One more avg_price Column, values are the same
SELECT product_id,
       (SELECT AVG(sale_price)
          FROM product) AS avg_price
  FROM product;

2.3 Associated Subqueries

  • This means there is a link between the query and the subquery
  • In the following example, the outer product table is marked p1, the inner product is set to p2, and two queries are joined through a WHERE statement
-- Find out in each category, sale Items whose price is higher than the average in the corresponding category
SELECT product_type, product_name, sale_price
  FROM product AS p1
 WHERE sale_price > (SELECT AVG(sale_price)
                      FROM product AS p2
                      WHERE p1.product_type = p2.product_type
   					  GROUP BY product_type);
  • The sql execution order tells us:
    • First from gets the data to be filtered
    • Execute a subquery where p1.producty_type is the type corresponding to the current data of the parent query

3 Functions

3.1 Arithmetic Function

Topics: Database MySQL SQL