"MySQL must know" reading notes _3

PS: This time the amount of information is a bit large.

Aggregating different values

SELECT AVG (DISTINCT prod_price) as Avg_price

From Products

WHERE vend_id = 1003

# The same value is not calculated

Combining aggregate functions

SELECT COUNT (*) as Num_items,

MIN (Prod_price) as Price_min,

MAX (Prod_price) as Price_max,

AVG (Prod_price) as Price_avg

From Products

Create groups

# do not create a grouped look

SELECT vend_id from Products

# Create a grouped look

SELECT Vend_id,count (*) as Num_prods

From Products

GROUP by vend_id

P84~p96

Primary key: Unique identity

FOREIGN key: A table's primary key value, a column contained in another table that defines the relationship between two tables.

PS: FOREIGN KEY constraint, which causes a row of data in table a to be deleted if the primary key of table a is a foreign key of table B , and the table B There is a data reference in the foreign key to delete the data, then the deletion fails. before you can delete the data in table A , you need to touch the constraint to delete the data in table B .

PS: It is necessary to learn the concept of database paradigm in order to know more about the design method in the book.

A junction is a mechanism used to correlate a table in a SELECT statement and is therefore called a junction.

A junction is not a physical entity, it does not exist in the actual database table.

SELECT Vend_name, Prod_name, Prod_price

From vendors, products

WHERE vendors.vend_id = products.vend_id

ORDER by Vend_name, Prod_name

Cartesian product: The result of a table relationship without a join condition is a Cartesian product. The number of rows retrieved will be the number of rows in the first table multiplied by the number of rows in the second table. (PS: (r*s))

SELECT Vend_name, Prod_name, Prod_price

From vendors, products

#WHERE vendors.vend_id = products.vend_id

ORDER by Vend_name, Prod_name

When the amount of data is large, it should be ensured that all junctions have a where clause, which reduces the amount of data returned and, in the same way, guarantees the correctness of the clauses.

The inner join is the same as the result of the junction statement above, but the following statement is preferred by specification.

SELECT Vend_name, Prod_name, Prod_price

From vendors INNER joins products

On vendors.vend_id = products.vend_id

Do more experiments: There is generally more than one way to perform any given SQL operation. There are few methods of absolute correctness or absolute error. Performance is affected by a number of factors, so it is necessary to experiment with different selection mechanisms to find the best way to fit the situation.

External connection

SELECT customers.cust_id, Orders.order_num

From customers left OUTER JOIN orders

On customers.cust_id = orders.cust_id

There is no *= operator under MySQL , which is popular in other DBMS.

There are two forms of external link types: Left and right outer connections. The only difference is that the order of the associated tables is different. The specific use of which is purely based on convenience.

junction with aggregation function

SELECT Customers.cust_name,

CUSTOMERS.CUST_ID,

COUNT (orders.order_num) as Num_ord

From Customers INNER JOIN orders

On customers.cust_id = orders.cust_id

GROUP by customers.cust_id

SELECT Customers.cust_name,

CUSTOMERS.CUST_ID,

COUNT (orders.order_num) as Num_ord

From customers left OUTER JOIN orders

On customers.cust_id = orders.cust_id

GROUP by customers.cust_id

UNION combination query: Combines The results of two SELECT statements with

SELECT vend_id, prod_id, Prod_price

From Products

WHERE Prod_price <= 5

UNION

SELECT vend_id, prod_id, Prod_price

From Products

WHERE vend_id in (1001,1002)

The UNION automatically removes duplicate rows from the query results, which is the default behavior. To return all rows, you need to use UNIONall.

Sort the result of the combined query: the last order by of the statement is used to sort the results returned by all SELECT statements.

Use full-Text search: typically enabled when a table is created, the CREATE TABLE statement accepts the fulltext clause, which gives a comma-delimited list of indexed columns.

Do not use fulltext when importing data, it takes time to update the index.

Use two functions after index:MATCH () specifies the column being searched;against () Specifies the search expression to use.

SELECT Note_text

From Productnotes

WHERE Match (Note_text) against (' Rabbit ')

The value passed to Match () must be the same as in the fulltext () definition.

Full-Text search is not case-sensitive unless BINARY is used.

Using Query extensions

SELECT Note_text

From Productnotes

WHERE Match (Note_text) against (' anvils ' with QUERY EXPANSION)

Boolean Text Search

SELECT Note_text

From Productnotes

WHERE Match (Note_text) against (' anvils ' in BOOLEAN MODE)

Inserting the retrieved data

INSERT into TableName1 (filed1,filed2) SELECT filed1,filed2 from Tablename2

Update data in multiple fields

UPDATE tablename1

SET filed1 = value1,

Filed2 = value2

WHERE filed3 = Value3

Delete all rows in the table faster, using the TRUNCATE table statement.

# Create a table

CREATE TABLE Vendors

{

vend_id INT not NULL auto_increment, # self-increment property

Vend_name CHAR (#) Not NULL, # is not an empty property

Vend_city CHAR () NULL, # allow Null property

vend_age int not NULL default 1, # defaults to 1, function not allowed, only solid

PRIMARY key (vend_id, Vend_name) # Federated primary Key, can be a single field master key

} engine = INNODB # Select engine, if no default is MyISAM

Foreign keys cannot cross the engine.

Common Database Engine

InnoDB is a reliable transaction processing engine that does not support full-text search.

Memory is functionally equivalent to MyISAM, But because the data is stored in memory, it is fast

MyISAM is an extremely high-performance engine that supports full-text search, but does not support transactional processing.

Update table

ALTER TABLE tablename1

ADD filed1 CHAR (20)

ALTER TABLE tbalename2

DROP COLUMN filed1

Delete a table

DROP TABLE tablename1

Renaming a table

RENAME Tbale Oldtablename to Newtablename

To delete a stored procedure

DROP PROCEDURE procedurename

Stored procedures that use parameters

Create PROCEDURE procedurename (# creates a stored procedure

in prfiled1 int, # receives from external parameters

Out Prfiled2 DECIMAL (8,2) # output results to external

)

BEGIN

SELECT Min (filed1) # internal query Statement

into Prfiled2 # internal query result output to variable

from table1;

WHERE filed2 = prfiled1 # using the receive external parameter as a query condition

END;

Executing stored procedures

Call ProcedureName (@prfiled1, @prfiled2)

select  @prfiled2