MySQL Query Miscellaneous

MySQL Query miscellaneous 1, display the current system time

MariaDB [hellodb]> SELECT NOW();+---------------------+| NOW()               |+---------------------+| 2017-01-20 05:10:32 |+---------------------+

2. Remove duplicate content Query

Keep only the same data: MariaDB [hellodb]> SELECT DISTINCT GENDER from students;+--------+| GENDER |+--------+| M | | F |+--------+2.1 MariaDB [hellodb]> SHOW GLOBAL VARIABLES like ' query% '; +------------------------------+---------+ | variable_name | Value |+------------------------------+---------+| Query_alloc_block_size | 8192 | | Query_cache_limit | 1048576 | | Query_cache_min_res_unit | 4096 | | Query_cache_size | 0 | | query_cache_strip_comments | OFF | | Query_cache_type | On | | Query_cache_wlock_invalidate | OFF | | Query_prealloc_size | 8192 |+------------------------------+---------+2.2 Query Cache Hit count: MariaDB [hellodb]> SHOW GLOBAL STATUS like ' qcache% '; + -------------------------+-------+| variable_name | Value |+-------------------------+-------+| Qcache_free_blocks | 0 | | Qcache_free_memory | 0 | | Qcache_hits | 0 | | Qcache_inserts |0 | | Qcache_lowmem_prunes | 0 | | qcache_not_cached | 0 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 0 |2.3 Query Total number of queries mariadb [hellodb]> SHOW GLOBAL STATUS like ' com_se% '; +----------------+-------+| variable_name | Value |+----------------+-------+| Com_select | 57 | | com_set_option | |+----------------+-------+

3. Query field aliases

MariaDB [hellodb]> SELECT Name AS StuName FROM students;+---------------+| StuName       |+---------------+| Diao Chan     || Ding Dian     || Duan Yu       || Hua Rong      || Huang Yueying |……

4. Expression arithmetic operation

4.1 Find students older than 50: MariaDB [hellodb]> SELECT name,age from students WHERE ages > 50;+--------------+-----+| Name | Age |+--------------+-----+|  Xie Yanke | 53 | |  Yinjiao King | 98 | | Sun Dasheng | 100 | | Jinjiao King | |+--------------+-----+4.2 find students older than 50 after age plus 30: MariaDB [hellodb]> SELECT name,age from students WHERE age+30 > 50; 4.3 queries age not equal to 100 of classmates: MariaDB [hellodb]> Select Name,age from students WHERE ages! = 100; or MariaDB [hellodb]> select Name, Age from students where ages <> 100;4.4 queries 20-40-year-old classmate: MariaDB [hellodb]> SELECT name,age from students WHERE aged BET Ween and 40;4.5 in, showing 18 and 100-year-olds: MariaDB [hellodb]> SELECT name,age from students WHERE ages in (18,100); +------------ --+-----+| Name | Age |+--------------+-----+|  Xue Baochai | 18 | | Sun Dasheng | 100 | | Jinjiao King | 100 |4.6 query Student ID empty student: MariaDB [hellodb]> SELECT name,classid from students WHERE ClassID is null;+--------------+------ ---+| Name | ClassID |+--------------+---------+|    Xu Xian | NULL | |    Sun Dasheng | NULL | |    Jinjiao King | NULL | |    Yinjiao King | NULL |+--------------+---------+4.7 find student ID non-empty students: MariaDB [hellodb]> SELECT name,classid from students WHERE ClassID is Not null;4.8 like:mariadb [hellodb]> select Name,age from students WHERE Name is like "%da"; Empty Set (0.00 sec) MariaDB [hellodb]> SELECT name,age from students WHERE Name like "%eng"; +-------------+-----+| Name | Age |+-------------+-----+| Sun Dasheng | 100 |

5. Logical operators

5.1 For the class sex group, but not show all students: MariaDB [hellodb]> SELECT * from students GROUP by gender;+-------+-------------+-----+--- -----+---------+-----------+| Stuid | Name | Age | Gender | ClassID |     Teacherid |+-------+-------------+-----+--------+---------+-----------+| 7 |  Xi Ren | 19 |       F |      3 |     NULL | | 1 |  Shi Zhongyu | 22 |       M |         2 | 3 |5.1.1 Group aggregation, viewing the average age of men and women: MariaDB [hellodb]> SELECT avg (age), Gender from students GROUP by gender;+----------+------- -+| AVG (age) |  Gender |+----------+--------+| 19.0000 |  F | | 40.7647 | M |+----------+--------+5.1.2 view sex with an average age greater than 20, having a filter for group by MARIADB [hellodb]> SELECT avg (age) as Aage,gender F ROM students GROUP by Gender have aage>20;+---------+--------+| AAge | Gender |+---------+--------+| 40.7647 |  M |+---------+--------+5.2 See how many people are in each class: MariaDB [hellodb]> SELECT count (stuid) as Nos,classid from students GROUP by classid;+-----+---------+| NOS | ClassID |+-----+---------+|    4 |   NULL | |       4 |   1 | |       3 |   2 | |       4 |   3 | |       4 |   4 | |       1 |   5 | |       4 |   6 | |       3 | 7 |+-----+---------+5.3 View classes with a total class size greater than 2: MariaDB [hellodb]> SELECT count (stuid) as Nos,classid from students GROUP by Clas SID has nos>2;+-----+---------+| NOS |   ClassID |+-----+---------+|    4 |   NULL | |       4 |   1 | |       3 |   2 | |       4 |   3 | |       4 |   4 | |       4 |   6 | |       3 | 7 |

6. Ascending and descending order

6.1 默认升序：MariaDB [hellodb]> SELECT Name,Age FROM students ORDER BY Age;+---------------+-----+| Name          | Age |+---------------+-----+| Lu Wushuang   |  17 || Lin Daiyu     |  17 || Xue Baochai   |  18 || Wen Qingqing  |  19 || Duan Yu       |  19 || Yue Lingshan  |  19 || Xi Ren        |  19 || Diao Chan     |  19 || Ren Yingying  |  20 || Xiao Qiao     |  20 || Xu Zhu        |  21 || Huang Yueying |  22 |6.2 降序：MariaDB [hellodb]> SELECT Name,Age FROM students ORDER BY Age DESC;+---------------+-----+| Name          | Age |+---------------+-----+| Jinjiao King  | 100 || Sun Dasheng   | 100 || Yinjiao King  |  98 || Xie Yanke     |  53 || Shi Qing      |  46 || Tian Boguang  |  33 || Ding Dian     |  32 |

7. LIMIT

7.1 只显示前5行：MariaDB [hellodb]> SELECT Name,Age FROM students ORDER BY Age LIMIT 5;+--------------+-----+| Name         | Age |+--------------+-----+| Lu Wushuang  |  17 || Lin Daiyu    |  17 || Xue Baochai  |  18 || Wen Qingqing |  19 || Duan Yu      |  19 |+--------------+-----+7.2 取第11-20个，第一个参数是偏移跳过去多少个：MariaDB [hellodb]> SELECT NAME,Age FROM students ORDER BY Age DESC LIMIT 10,10;

Multi-table Query 1, equivalent connection

1.1 Find two tables Teacherid the same line mariadb [hellodb]> SELECT * from Students,teachers WHERE students. Teacherid=teachers. tid;+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+| Stuid | Name | Age | Gender | ClassID | Teacherid | TID | Name | Age |     Gender |+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+| 5 |  Yu Yutong | 26 |       M |         3 |   1 | 1 |  Song Jiang | 45 |     M | | 1 |  Shi Zhongyu | 22 |       M |         2 |   3 | 3 |  Miejue Shitai | 77 |     F | | 4 |  Ding Dian | 32 |       M |         4 |   4 | 4 |  Lin chaoying | 93 | F |+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+1.1 Change the gold angle and silver corner Teacherid to 1,2mariadb [hellodb]> UPDATE students SET teacherid=1 WHERE stuid=26; MariaDB [hellodb]> UPDATE students SET teacherid=2 WHERE stuid=27; MariaDB [hellodb]> SELECT * from students WHERE Name like "%king", +-------+--------------+-----+--------+---------+-----------+| Stuid | Name | Age | Gender | ClassID |    Teacherid |+-------+--------------+-----+--------+---------+-----------+| 26 | Jinjiao King | 100 |    M |         NULL |    1 | | 27 |  Yinjiao King | 98 |    M |         NULL | 2 |+-------+--------------+-----+--------+---------+-----------+1.2mariadb [hellodb]> SELECT * from students, Teachers WHERE students. Teacherid=teachers. tid;+-------+--------------+-----+--------+---------+-----------+-----+---------------+-----+--------+| Stuid | Name | Age | Gender | ClassID | Teacherid | TID | Name | Age |     Gender |+-------+--------------+-----+--------+---------+-----------+-----+---------------+-----+--------+| 5 |  Yu Yutong | 26 |       M |         3 |   1 | 1 |  Song Jiang | 45 |    M | | 26 | Jinjiao King | 100 |    M |         NULL |   1 | 1 |  Song Jiang | 45 |    M | | 27 | Yinjiao King| 98 |    M |         NULL |   2 | 2 |  Zhang Sanfeng | 94 |     M | | 1 |  Shi Zhongyu | 22 |       M |         2 |   3 | 3 |  Miejue Shitai | 77 |     F | | 4 |  Ding Dian | 32 |       M |         4 |   4 | 4 |  Lin chaoying | 93 | F |+-------+--------------+-----+--------+---------+-----------+-----+---------------+-----+--------+1.3 See who the teacher is for each student: MariaDB [hellodb]> SELECT s.name as stuname,t.name as teaname from students as s,teachers as T WHERE S.TEAC herid=t.tid;+--------------+---------------+| Stuname | Teaname |+--------------+---------------+| Yu Yutong | Song Jiang | | Jinjiao King | Song Jiang | | Yinjiao King | Zhang Sanfeng | | Shi Zhongyu | Miejue Shitai | | Ding Dian | Lin chaoying |+--------------+---------------+

2. Index

2.1 See if a multi-table query can work with indexes: MariaDB [hellodb]> EXPLAIN SELECT s.name as stuname,t.name as teaname from students as s,teachers as T WHERE s.teacherid=t.tid\g*************************** 1. Row *************************** id:1 select_type:simple table:s type:all #全表扫描possible_ke Ys:null key:null key_len:null ref:null rows:27 Extra: ************************* * * 2. Row *************************** id:1 select_type:simple table:t Type:ALLpossible_keys:PRIMA RY #只用了主键扫描 key:null key_len:null ref:null rows:4 extra:using where; Using Join buffer (flat, BNL join) 2 rows in Set (0.00 sec) 2.2 View class table: MariaDB [hellodb]> SELECT * from classes;+--------- +----------------+----------+| ClassID | Class |       Numofstu |+---------+----------------+----------+| 1 |       Shaolin Pai |       10 | | 2 |        Emei Pai |       7 | | 3 | Qingcheng Pai |      11 | | 4 |       Wudang Pai |       12 | | 5 |       Riyue Shenjiao |       31 | | 6 |       Lianshan Pai |       27 | | 7 |       Ming Jiao |       27 | | 8 |       Xiaoyao Pai | |+---------+----------------+----------+2.3 see which class each classmate belongs to mariadb [hellodb]> SELECT S.name,c.class from students As s,classes as C WHERE s.classid=c.classid;+---------------+----------------+| Name | Class |+---------------+----------------+| Shi Zhongyu | Emei Pai | | Shi Potian | Shaolin Pai | | Xie Yanke | Emei Pai | | Ding Dian | Wudang Pai |

3, left outer connection, right outer connection

3.1 Show all classmates class, no class also show mariadb [hellodb]> SELECT S.name,c.class from students as s left JOIN classes as C on S.classid=c . classid;+---------------+----------------+| Name | Class |+---------------+----------------+| Shi Zhongyu | Emei Pai | | Shi Potian | Shaolin Pai | | Xie Yanke | Emei Pai | | Ding Dian | Wudang Pai | | Yu Yutong | Qingcheng Pai | | Shi Qing | Riyue Shenjiao | | Xi Ren | Qingcheng Pai | | Lin Daiyu | Ming Jiao | | Ren Yingying | Lianshan Pai | | Yue Lingshan | Qingcheng Pai | | Yuan Chengzhi | Lianshan Pai | | Wen qingqing | Shaolin Pai | | Tian Boguang | Emei Pai | | Lu Wushuang | Qingcheng Pai | | Duan Yu | Wudang Pai | | Xu Zhu | Shaolin Pai | | Lin Chong | Wudang Pai | | Hua Rong | Ming Jiao | | Xue Baochai | Lianshan Pai | | Diao Chan | Ming Jiao | | Huang yueying | Lianshan Pai | | Xiao Qiao | Shaolin Pai | | Ma Chao | WUdang Pai | | Xu Xian | NULL | | Sun Dasheng | NULL | | Jinjiao King | NULL | | Yinjiao King | NULL |3.2 right connection, see which class no one learns mariadb [hellodb]> SELECT S.name,c.class from students as S R JOIN classes as C on s.classid=c.classid;+---------------+----------------+| Name | Class |+---------------+----------------+| Shi Zhongyu | Emei Pai | | Shi Potian | Shaolin Pai | | Xie Yanke | Emei Pai | | Ding Dian | Wudang Pai | | Yu Yutong | Qingcheng Pai | | Shi Qing | Riyue Shenjiao | | Xi Ren | Qingcheng Pai | | Lin Daiyu | Ming Jiao | | Ren Yingying | Lianshan Pai | | Yue Lingshan | Qingcheng Pai | | Yuan Chengzhi | Lianshan Pai | | Wen qingqing | Shaolin Pai | | Tian Boguang | Emei Pai | | Lu Wushuang | Qingcheng Pai | | Duan Yu | Wudang Pai | | Xu Zhu | Shaolin Pai | | Lin Chong | Wudang Pai | | Hua Rong | Ming Jiao | | Xue BaoChai | Lianshan Pai | | Diao Chan | Ming Jiao | | Huang yueying | Lianshan Pai | | Xiao Qiao | Shaolin Pai | | Ma Chao | Wudang Pai | | NULL | Xiaoyao Pai |+---------------+----------------+3.3 self-connected, use a table as two tables to mariadb [hellodb]> SELECT s.name,t.name as Tname F ROM students as s,students as T WHERE s.teacherid=t.stuid;+--------------+-------------+| Name | Tname |+--------------+-------------+| Shi Zhongyu | Xie Yanke | | Shi Potian | Xi Ren | | Xie Yanke | Xu Zhu | | Ding Dian | Ding Dian | | Yu Yutong | Shi Zhongyu | | Jinjiao King | Shi Zhongyu | | Yinjiao King | Shi Potian |+--------------+-------------+

4, sub-query-where

4.1 Queries older than average age: average age: MariaDB [hellodb]> SELECT avg. from students; MariaDB [hellodb]> Select Name,age from students WHERE age> (SELECT avg (age) from students); +--------------+-----+| Name | Age |+--------------+-----+|  Tian Boguang | 33 | |  Shi Qing | 46 | |  Xie Yanke | 53 | |  Yinjiao King | 98 | | Sun Dasheng | 100 | | Jinjiao King | |+--------------+-----+4.2 view its index: MariaDB [hellodb]> EXPLAIN Select Name,age from students WHERE age> (SELECT avg (age) From students) \g*************************** 1. Row *************************** id:1 select_type:primary table:students Type:rangepossible_k Eys:age key:age key_len:1 ref:null rows:7 extra:using where******************* 2. Row *************************** id:2 select_type:subquery table:students type:indexpossible_ Keys:null key:age key_len:1 ref:null rows:27       Extra:using index2 rows in Set (0.00 sec) 

5, sub-query-in

5.1 查询同学年龄跟老师年龄一样的MariaDB [hellodb]> SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);

6, sub-statement-from

6.1 查询平均年龄大于25的班级求每个班级平均年龄：MariaDB [hellodb]> SELECT avg(Age),ClassID FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID;+----------+---------+| avg(Age) | ClassID |+----------+---------+|  81.2500 |    NULL ||  20.5000 |       1 ||  36.0000 |       2 ||  20.2500 |       3 ||  24.7500 |       4 ||  46.0000 |       5 ||  20.7500 |       6 ||  19.6667 |       7 |+----------+---------+MariaDB [hellodb]> SELECT s.aage,s.ClassID FROM (SELECT avg(Age) AS aage,ClassID FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID) AS s WHERE s.aage>30;+---------+---------+| aage    | ClassID |+---------+---------+| 36.0000 |       2 || 46.0000 |       5 |

7. Joint Inquiry

7.1 Connecting the results of two tables, the value will mariadb [hellodb]> select Name,age from students UNION select Name,age from teachers;+---------- -----+-----+| Name | Age |+---------------+-----+|  Shi Zhongyu | 22 | |  Shi Potian | 22 | |  Xie Yanke | 53 | |  Ding Dian | 32 |. 7.2 View the index of the work query statement, three query statements: MariaDB [hellodb]> EXPLAIN Select Name,age from students UNION Select Name,age from teachers\g** 1. Row *************************** id:1 select_type:primary table:students Type:allpossible_key S:null key:null key_len:null ref:null rows:27 Extra: ************************** * 2.  Row *************************** id:2 select_type:union table:teachers Type:allpossible_keys: NULL key:null key_len:null ref:null rows:4 Extra: *************************** 3 .    Row *************************** id:null select_type:union RESULT    Table: <union1,2> Type:ALLpossible_keys:NULL key:null key_len:null ref:null  Rows:null extra:3 rows in Set (0.01 sec) MariaDB [hellodb]>

7.3

MySQL Query Miscellaneous