MySQL Learning notes

MySQL Technology Insider
First, MySQL basic knowledge
1.1. Display the columns in the table
SHOW COLUMNS from order_info like ' order% '
1.2. Display table
SHOW TABLES like ' order% ';
1.3. Limit the number of rows returned
For example, return 5 records starting from line 10th
SELECT last_name from student ORDER by birth DESC LIMIT 10, 5;
1.4. Time-related
SELECT
Curdate () as current date,
Year (now ()) as years,
Month (now ()) as months,
DayOfMonth (now ()) as Day,
Date_add (now (), INTERVAL) Date added,
Date_add (now (), INTERVAL month) added,
Date_add (now (), INTERVAL-2 year) years added,
Date_sub (now (), INTERVAL years) Add 2,
To_days (now ())-To_days (' 2015-08-01 ') as date subtraction,
Timestampdiff (Day,now (), ' 2015-08-01 ') as date subtract 2,
Unix_timestamp (now ()) as timestamp,
From_unixtime (1439869720) time stamp turn,
Str_to_date (' 2008-4-2 15:3:28 ', '%y-%m-%d%h:%i:%s ') as time
Ii. using SQL to manage data
2.1, Case:
Database, data table and view names are specified by the server's operating system (Windows does not differentiate, Unix differentiates)
Keyword and function names are not case-sensitive,
The names of stored functions, stored procedures, and events are not case-sensitive,
Data columns and index names are not case-sensitive,
The name of the trigger should be case-sensitive,
The alias of the data table is best to save the same letter case combination
2.2. Quick Build Table
CREATE TABLE kehu_20150819 like Kehu--copy data structure only
CREATE table kehu_20150818 as SELECT * from Kehu--copy table structure and data
2.3. Get Database metadata
List databases
SHOW dtabases;
Lists the default database or tables for a given database
SHOW TABLES;
SHOW TABLES from Db_name;
View the CREATE TABLE statement for a data table
SHOW CREATE TABLE tbl_name;
View data column or index information for a data table
SHOW COLUMNS from Tbl_name;
SHOW INDEX from Tbl_name;
The best way to query the INFORMATION_SCHEMA library
2.4, involving the deletion and update of multiple packets
2.4. Delete data rows from multiple data tables 11 times
DELETE t1,t2 from T1 INNER joins t2 on t1.id = t2.id;
Or
DELETE from t1,t2 USING T1 INNER joins t2 on t1.id = t2.id;
2.4.2 removing data rows from a data table
DELETE T1 from T1 the left JOIN T2 on t1.id =t2.id WHERE t2.id is NULL;
Or
DELETE from t1 USING T1 left JOIN t2 on t1.id = T2.id WHERE t2.id is NULL;
2.4.3 Update a single table
UPDATE score, grade_event SET score.score =score.score +1
WHERE score.event_id = grade_event.event_id
and grade_event.date = ' 2015-08-01 ';
Or
UPDATE score SET score =score +1
where event_id = (SELECT event_id from grade_event WHERE date = ' 2015-08-01 ');
You must use the 2.4.1 notation:
UPDATE t1,t2 SET t2.a = t1.a WHERE t2.id = t1.id;
Third, the data type
Coordinate value point (10 20)
Null "\ n" is interpreted as null
SELECT
Inet_aton (' 192.168.1.1 ') as IP to Integer,
Inet_ntoa (3232235777) As Integer turn IP;
Four, query optimization
Index Type:
Hash index The exact match comparison operation with "=" or "<=>" operator is faster, but in finding a range of
Poor performance in comparison operation, e.g. id< weight between and 150
Bit tree index is efficient in precise operation comparisons using <,<=,=,>=,>,<>,!= and between, or in range comparison operations.

MySQL Learning notes