mysql Foundation

essential information

All query statements must be; End with a semicolon

Display database		show databases;
Use database   use database_name;
Table showing the database				show tables;
Display column information of database table	show columns from table_name;

Retrieve data SELECT

-- Retrieve all data from multiple columns of a table
SELECT column1,column2,column3 FROM table_name;

-- De duplication of the retrieved data column, DISTINCT Keyword applies to all columns retrieved, if DISTINCT column1,column2 Retrieve rows with two different columns
SELECT DISTINCT column1 FROM table_name;

-- Limit the number of rows in the query result LIMIT 5 Limit display to 5 lines
SELECT DISTINCT column1, column2 FROM table_name LIMIT 5;
-- Specify the position and number of rows to display. Display up to 4 rows from the sixth row (the number of rows starts from 0)
SELECT column1 FROM table_name LIMIT 5,4；
Equivalent to SELECT column1 FROM table_name LIMIT 4 OFFSET 5；

Sort retrieval data ORDER BY

-- Sort the search results by pro_price Sort by pro_name Sort. If the values in the first column are unique in the search results, the results in the second column will not be sorted
SELECT pro_id,pro_price,pro_name 
FROM products
ORDER BY pro_price,pro_name;

-- Specifies the default ascending sort direction( ASC)， A-Z，Sort assignments in descending order DESC keyword,Apply only to DESC Keywords before
SELECT pro_id,pro_price,pro_name 
FROM products
ORDER BY pro_price DESC,pro_name;

Filter data

• The position of WHERE sentence: WHERE in front, ORDER BY in the back

-- Filter to prod_price For 2.50 Line of
SELECT prod_name,pro_price FROM products WHERE prod_price = 2.50;

-- Filter to pro_name For three lines, the string is enclosed in single quotes
SELECT prod_name,pro_price FROM products WHERE prod_name = 'Zhang San';

-- Filter data with values in a certain range
SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;

-- Null value check prod_price Empty row
SELECT prod_name,prod_price FROM products WHERE prod_price IS NULL;

• Conditional operators supported by mysql

Operator	explain
=	be equal to
< > or=	Not equal to
<	less than
<=	Less than or equal to
>	greater than
>=	Greater than or equal to
BETWEEN	Between two values specified

• Combination of WHERE

  WHERE can contain multiple AND and OR combinations. By default (priority is not distinguished by parentheses), AND has higher priority than OR

  -- AND query vend_id 111 and price Values less than 10
  SELECT prod_id,prod_price,prod_name FROM products WHERE vend_id=111 AND pro_price<10;
  
  -- OR query vend_id 111 or price Values less than 10
  SELECT prod_id,prod_price,prod_name FROM products WHERE vend_id=111 OR pro_price<10;
  

• IN operator

  The IN operator is used to specify a condition range IN which each condition can be matched.

  SELECT prod_name,prod_price FROM products
  WHERE vend_id IN(111,222) ORDER BY prod_name;
  

• NOT operator

  NOT is used to negate the following conditions

  SELECT prod_name,prod_price FROM products
  WHERE vend_id NOT IN(111,222);
  

• Search statement template

  SELECT columnname,...
  FROM tablename,...
  [WHERE ...]
  [UNION]
  [GROUP BY]
  [HAVING]
  [ORDER BY]
  
  

Fuzzy search

• The percent sign% wildcard% indicates that any character appears any number of times

  -- search prod_name by jet First line( jet%, %jet%, %jet, j%t)
  SELECT pro_id,pro_name FROM products WHERE prod_name LIKE 'jet%';
  

• The underscore wildcard indicates that any character occurs only once

• Regular expression REGEXP

  -- Normal character matching, including 1000 characters
  SELECT prod_name FROM products WHERE prod_name REGEXP '1000';
  
  -- OR matching prod_name Rows containing 1000 or 2000
  SELECT prod_name FROM products WHERE prod_name REGEXP '1000｜2000';
  
  -- Match one of multiple matches 1 or 2 or 3 or 4
  SELECT prod_name FROM products WHERE prod_name REGEXP '[1234]';
  
  -- Match range data 0-9， a-z
  SELECT prod_name FROM products WHERE prod_name REGEXP '[0-9]';
  
  -- Match regular expression special characters preceded by \\  as\\.Match contains . Line of
  SELECT prod_name FROM products WHERE prod_name REGEXP '[\\.]';
  

  The difference between regular expressions and LIKE is that LIKE matches the whole column of data, and regular expressions can only contain the results of finding the target. LIKE is equivalent to that the beginning and end of regular expressions are limited by ^ $

Calculation field

• Field splicing

  -- Field splicing, Concat()Splice (mysql Dedicated, majority DBMS use+or||Splice)
  SELECT Concat(vend_name, '(',vend_country, ')') FROM vendors ORDER BY vend_name;
    
  -- RTrim()Function to delete the extra space on the right  LTrim()Delete extra space on the left  Trim()Delete spaces on both sides
  SELECT Concat(RTrim(vend_name), '(',RTrim(vend_country), ')') FROM vendors ORDER BY vend_name;
  

• Column alias

  -- Use keywords AS Give alias
  SELECT Concat(RTrim(vend_name), '(',RTrim(vend_country), ')') AS new_vend_title FROM vendors ORDER BY vend_name;
  
  -- Arithmetic calculation
  SELECT prod_id,quantity,quantity*item_price AS sum_price FROM orderitems WHERE order_num = 1555;
  

Data processing function

Different MDBS support different functions. The following functions can only be used in mysql

• Text processing function

-- Convert all result letters to uppercase
SELECT vend_name,UPPER(vender_name) AS vend_name_upcase FROM venders;

function	explain
Left()	Returns the character to the left of the string
Length()	Returns the length of the string
Locate()	Find a substring of the string
Lower()	Convert string to lowercase
LTrim()	Remove the space to the left of the string
Right()	Returns the character to the right of the string
RTrim()	Remove the space on the right side of the string
Soundex()	Returns the SOUNDEX value of the string (string matching similar pronunciation)
SubString()	Returns the character of the substring
Upper()	Convert string to uppercase

• Date and time handler

function	explain
AddDate()	Add a date (day, week, month, etc.)
AddTime()	Add one time (hour, minute and second)
CurDate()	Returns the current date
CurTime()	Returns the current time
Date()	Returns the date portion of the time date
DateDiff()	Calculate the difference between two dates
Date_format()	Returns a formatted date or time string
Hour()|Minite()|Month()|Second()	Return time hours | minutes | seconds ス months
Now()	Returns the current date and time
Time()	Returns the time portion of a date time

-- Extract date partial comparison
SELECT cust_id, order_num FROM orders WHERE Date(order_date) = '2014-12-12';

-- Find data at a certain time
SELECT cust_id, order_num FROM orders WHERE Date(order_date) BETWEEN '2014-12-12' AND '2014-12-30';
SELECT cust_id, order_num FROM orders WHERE YEAR(order_date)=2014 AND MONTH(order_date)=12;

Aggregate function

-- AVG()The field value corresponding to the calculated average value is null Not calculated
SELECT AVG(prod_price) AS avg_price FROM products;

-- COUNT()Count the number of specific rows
SELECT COUNT(*) AS num_cust FROM customs;

-- MAX() Returns the maximum value in the column
SELECT MAX(pro_price) FROM product;

-- MIN() Return the minimum value in the column. If it is character data, return the first data sorted in the corresponding column
SELECT MIN(pro_price) FROM product;

-- SUM() Returns the sum of the values in the specified column
SELECT SUM(quanlity) AS sum_quanlity FROM orders WHERE order_num=5;

-- DISTINCT() Only non duplicate values are considered DISTINCT Column names must be used
SELECT COUNT(DISTINCT price) AS avg_price FROM products;

Data grouping

-- GROUP BY Press vend_id Sort and group data for each vend_id Calculate once num_prods
-- SELECT Each column that appears in the statement must be GROUP BY Clause appears in the group column null Values are returned as a set
-- GROUP BY Must follow WHERE After the statement, ORDER BY before
SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;

-- HAVING  Filter the group with the number of grouping rows greater than or equal to 2
SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*)>=2;

Subquery

The result of the execution of a SELECT statement is used in the WHERE clause of a SELECT statement

In a SELECT statement, subqueries are always processed from the inside out

-- First from order_items Found in table prod_id by TNT2 of order_num,Returns two order numbers, which are expressed in IN The required comma separated form is passed to the outside of the column WHERE clause 
SELECT cust_id FROM orders
WHERE order_num IN (SELECT order_num
                   FROM order_items
                   WHERE prod_id = 'TNT2');

Join table

-- Create join table
SELECT  vend_name, prod_name, pro_price 
FROM vendors, products
WHERE vendors.vend_id=product.vend_id
ORDER BY vend_name,prod_name;
-- Equivalent to (Internal connection) INNER JOIN And ON Combined use, ON Equivalent to WHERE
SELECT vend_name, prod_name,prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

-- Use alias when joining tables
SELECT cust_name,cust_contact
FROM customers AS c, orders AS o, orderitems AS io
WHERE c.cust_id = o.cust_id
	AND oi.order_num = o.order_num
	AND proi_id = 'TNT2';
	
-- External connection is to retrieve the information of those lines without associated lines, and to retrieve customers, including those without orders
-- Extranet must be used RIGHT or LEFT Specify the table that needs to include all rows
SELECT customer.cust_id, orders.order_num
FROM customer LEFT OUT JOIN orders
ON customer.cust_id = order.cust_id

Combined query

The search statement uses UNION to join. The query in UNION must contain the same column, expression or aggregation function

UNION automatically takes out duplicate rows from the query results. If you want to match all rows, use UNION ALL

Addition, deletion and modification statement

• insert data

  -- If you do not specify the modified column, you need to specify the values of all columns, including NULL value
  INSERT INTO customers VALUES ('A','B', 'C');
  
  -- Specifies the value of the increased column
  INSERT INTO customers(name, address, city) VALUES ('A', 'B', 'C');
  
  -- Insert multiple rows of data, grouped by commas
  INSERT INTO customers(name, address, city) VALUES('A', 'B', 'C'),('D','E','F');
  
  -- insert SELECT Search results for
  INSERT INTO customers(name, address, city)
  	SELECT name,address, city FROM newcustomers;
  
  

• Update data

  -- UPDATE Need to specify WHERE Condition, otherwise the data of the whole table will be updated 
  -- When updating multiline data, if UPDATE And table names IGNORE Keyword, if any data update fails, the update will continue
  UPDATE customers SET email='newEmail.mail.com', address = 'newAddress' WHERE cust_id = '1005';
  
  

• Delete data

  DELETE FROM customers WHERE cust_id = 10006;
  

Manipulation table

• Create table

-- mysql Default use MyISAM Engine table creation
-- InnoDB Is a reliable transaction engine and does not support full-text search
-- MyISAM Transaction processing is not supported, but full-text search is supported
-- MEMORY Function equivalent to MyISAM， But it's stored in memory instead of disk, so it's fast
CREATE customers(
	cust_id INT	NOT NULL AUTO_INCREMNET,
  cust_name char(30) NOT NULL,
  cust_city char(50) NULL,
 	cust_quanlity int DEFAULT 1,
  PRIMARY KEY (cust_id)
)ENGINE=InnoDB;

-- Indexing
CREATE INDEX indexname ON tablename(columnname [ASC|DESC],...);

• Update table

-- Add a new column
ALTER TABLE vendors ADD COLUMN vend_phone;
-- Delete a column
ALTER TABLE vendors DROP COLUMN vend_phone;

-- Define foreign keys
ALTER TABLE orderitems ADD CONSTRAINT fk_orders_orderitems FOREIGN KEY(order_num) REFERENCES orders(order_num);

-- Delete table
DROP TABLE vendors;

-- rename table 
RENAME TABLE customers2 TO cumtomers;

view

View: it does not contain any data or columns that should be in the table, but SQL query results.

effect:

1. reusing SQL sentence
2. Simplify complex SQL operation
3. Use parts of the table instead of all
4. Protect data, 
5. Modify the data format and presentation, and the view can return data different from the underlying presentation format		

-- Create view create a view named productcustomer Join three tables and return the table information of all customers who order any product
CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact,prod_id
FROM customers, orders, ordersitems
WHERE customers.cust_id = orders.cust_id
	AND orderitems.order_num  = orders.order_num;
	
-- Retrieve view content
SELECT cust_name, cust_contact
FROM productcostomers
WHERE prod_id = 'TNT2';

stored procedure

-- Create stored procedure
CREATE PROCEDURE produce_name()
BEGIN
	SELECT avg(prod_price) AS priceavg
	FROM products
END;

-- Create a stored procedure with parameters
-- MySQL support IN(Pass to stored procedure),OUT(Outgoing from stored procedures, as used here)and INOUT(Incoming and outgoing to stored procedures)Parameter of type.
CREATE PROCEDURE produce_name_param(
	IN onumber INT,
  OUT ototal DECIMAL(8,2)
)
BEGIN
	SELECT Sum(item_price* quanlity)
	FROM ordersitems
	WHERE order_num = onumber
	INTO ototal;
END;

-- Using stored procedures
call produce_name();
call produce_name_param(20005, @total);



-- Delete stored procedure
DROP PROCEDURE produce_name;

-- Check stored procedures
SHOW CREATE PROCEDURE produce_name;

-- Show all stored procedures
SHOW PROCEDURE STATUS LIKE 'name';

cursor

A cursor is a database query stored on a MySQL server. It is not a SELECT statement, but a result set retrieved by the statement. After storing the cursor, the application can scroll or browse the data as needed.

A cursor must be declared (defined) before it can be used. This process does not actually retrieve data, it just defines the SELECT statement to be used.

-- Create cursor
CREATE PROCEDURE processorders()
BEGIN
	-- Define a cursor
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
  -- Open cursor
  OPEN ordernumbers;
  -- Retrieve data
  FETCH ordernumbers INTO o;
  -- Close cursor
  CLOSE ordernumbers;-- If it is not actively closed, it will be closed in END Close when
END;

After a cursor is opened, each row can be accessed separately using the FETCH statement. FETCH specifies what data to retrieve (the required columns) and where the retrieved data is stored.

trigger

-- Create trigger
CREATE TRIGGER product_tri AFTER INSERT ON products FOR EACH ROW SELECT 'Product ADD';

-- Delete trigger 
DROP TRIGGER prodct_tri

transaction processing

-- If the first item succeeds and the second item fails, then DELETE Will not submit
START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 1999;
DELETE FROM orders WHERE order_num = 1999;
COMMIT;

-- Set retention point
SAVEPOINT delete1;

-- Fallback to hold point
ROLLBACK TO delete1;

Principles of index establishment

• What situations require common indexes
  • The primary key automatically creates a unique index
  • Frequently queried fields need to be indexed
  • For multi table associative queries, indexes should be created for the keywords on both sides
  • The fields to be sorted or grouped in the query (order by/group by) need to be indexed
• Index creation is not required
  • There are few records in the table
  • A table that is frequently added, deleted, or modified
  • Frequently updated fields
  • Fields that are not frequently used in the where condition
• Index design principles
  • Low index discrimination
  • Indexing common query fields
  • Constant sorting / grouping / de duplication field indexing
  • Indexing primary and foreign keys
• Index failure
  • Use functions or operations on Indexes
  • Use < >, not in, not exists, for the index=
  • Use leading fuzzy query on index like% sele
  • Implicit transformation
  • or joined fields have unindexed fields
• Index optimization
  • Use join instead of subquery
  • When multiple tables are associated, the small table comes first and the large table comes last
  • Optimize the join order of where statements and put the clauses that can filter more conditions in front
  • Use a federated index instead of a single index
• other
  • The addition, deletion and modification of data will lead to frequent changes in the index. When a large number of data are updated in a short time, the index can be deleted first, and the index can be rebuilt after the data is updated. In addition, when creating an index, a lock will be generated to lock the whole table, and all DML statements cannot be executed

sql optimization

• For conditional queries, consider indexing the fields after where and order by
• Avoid null value judgment after the where condition, and avoid < >, not in, not exists=
• Avoid using functions after where
• Avoid using or join after where