1. Prepare a data sheet called fruits
MariaDB [vincen]> CREATE TABLE fruits #Create a data table
-> (
-> f_id char(10) NOT NULL,
-> s_id INT NOT NULL,
-> f_name char(255) NOT NULL,
-> f_price decimal(8,2) NOT NULL,
-> PRIMARY KEY(f_id)
-> );
Query OK, 0 rows affected (0.02 sec)
MariaDB [vincen]> INSERT INTO fruits (f_id, s_id, f_name, f_price) #Insert the appropriate data for the fruits data table
-> VALUES('a1', 101,'apple',5.2),
-> ('b1',101,'blackberry', 10.2),
-> ('bs1',102,'orange', 11.2),
-> ('bs2',105,'melon',8.2),
-> ('t1',102,'banana', 10.3),
-> ('t2',102,'grape', 5.3),
-> ('o2',103,'coconut', 9.2),
-> ('c0',101,'cherry', 3.2),
-> ('a2',103, 'apricot',2.2),
-> ('l2',104,'lemon', 6.4),
-> ('b2',104,'berry', 7.6),
-> ('m1',106,'mango', 15.6),
-> ('m2',105,'xbabay', 2.6),
-> ('t4',107,'xbababa', 3.6),
-> ('m3',105,'xxtt', 11.6),
-> ('b5',107,'xxxx', 3.6);
Query OK, 16 rows affected (0.00 sec) #16 records responded
Records: 16 Duplicates: 0 Warnings: 0
2. Query all fields (* match all)
select * from data table name;
MariaDB [vincen]> select * from fruits; +------+------+------------+---------+ | f_id | s_id | f_name | f_price | +------+------+------------+---------+ | a1 | 101 | apple | 5.20 | | a2 | 103 | apricot | 2.20 | | b1 | 101 | blackberry | 10.20 | | b2 | 104 | berry | 7.60 | | b5 | 107 | xxxx | 3.60 | | bs1 | 102 | orange | 11.20 | | bs2 | 105 | melon | 8.20 | | c0 | 101 | cherry | 3.20 | | l2 | 104 | lemon | 6.40 | | m1 | 106 | mango | 15.60 | | m2 | 105 | xbabay | 2.60 | | m3 | 105 | xxtt | 11.60 | | o2 | 103 | coconut | 9.20 | | t1 | 102 | banana | 10.30 | | t2 | 102 | grape | 5.30 | | t4 | 107 | xbababa | 3.60 | +------+------+------------+---------+ 16 rows in set (0.01 sec)
3. Query single field
select column name from data table name;
MariaDB [vincen]> select s_id from fruits; #Query the data of the column name "s" ID +------+ | s_id | +------+ | 101 | | 103 | | 101 | | 104 | | 107 | | 102 | | 105 | | 101 | | 104 | | 106 | | 105 | | 105 | | 103 | | 102 | | 102 | | 107 | +------+ 16 rows in set (0.01 sec)
4. Query multiple fields
select column name 1, column name 2.... from data table name;
MariaDB [vincen]> select s_id,f_name from fruits; #Query the data of the columns s ﹣ ID and f ﹣ name +------+------------+ | s_id | f_name | +------+------------+ | 101 | apple | | 103 | apricot | | 101 | blackberry | | 104 | berry | | 107 | xxxx | | 102 | orange | | 105 | melon | | 101 | cherry | | 104 | lemon | | 106 | mango | | 105 | xbabay | | 105 | xxtt | | 103 | coconut | | 102 | banana | | 102 | grape | | 107 | xbababa | +------+------------+ 16 rows in set (0.00 sec)
5. Query specified record
select column name 1, column name 2.... from data table name where query criteria;
MariaDB [vincen]> select s_id,f_name #Query the fields of s ﹣ ID and f ﹣ name
-> from fruits #From the fruits table
-> where #with the understanding that
-> s_id < 105 #The information in the s ﹤ ID column is less than 105
-> ; #End
+------+------------+
| s_id | f_name |
+------+------------+
| 101 | apple |
| 103 | apricot |
| 101 | blackberry |
| 104 | berry |
| 102 | orange |
| 101 | cherry |
| 104 | lemon |
| 103 | coconut |
| 102 | banana |
| 102 | grape |
+------+------------+
10 rows in set (0.01 sec)
6. Query with IN keyword, query the records that meet the conditions within the specified range
select column name 1, column name 2.... from data table name where column name IN condition ORDRE BY column name;
MariaDB [vincen]> select s_id,f_name,f_price #Query the three fields of s ﹣ ID, f ﹣ name and f ﹣ price
-> from fruits #From the fruits data table
-> where s_id IN (101,102) #The condition is that the data of s Ou ID is between 101 and 102
-> order by f_name; #Sort from the letter a down in the f ﹣ name column
+------+------------+---------+
| s_id | f_name | f_price |
+------+------------+---------+
| 101 | apple | 5.20 |
| 102 | banana | 10.30 |
| 101 | blackberry | 10.20 |
| 101 | cherry | 3.20 |
| 102 | grape | 5.30 |
| 102 | orange | 11.20 |
+------+------------+---------+
6 rows in set (0.00 sec)
7. BETWEEN AND query
select column name 1, column name 2... from data table name where column name BETWEEN start value AND end value;
MariaDB [vincen]> select f_name,f_price #Query the two fields of f'name and f'price
-> from fruits #From the fruits table
-> where f_price BETWEEN 2.00 AND 10.20 #If the value of F ﹐ price is between 2.00 and 10.20
-> ; #End
+------------+---------+
| f_name | f_price |
+------------+---------+
| apple | 5.20 |
| apricot | 2.20 |
| blackberry | 10.20 |
| berry | 7.60 |
| xxxx | 3.60 |
| melon | 8.20 |
| cherry | 3.20 |
| lemon | 6.40 |
| xbabay | 2.60 |
| coconut | 9.20 |
| grape | 5.30 |
| xbababa | 3.60 |
+------------+---------+
12 rows in set (0.01 sec)
8. LIKE character matching query
MariaDB [vincen]> select f_id,f_name #Query the two fields f ﹣ ID and f ﹣ name
-> from fruits #In the fruits table
-> where f_name LIKE 'b%' #The condition is that the letter b begins in f u name
-> ; #End
+------+------------+
| f_id | f_name |
+------+------------+
| b1 | blackberry |
| b2 | berry |
| t1 | banana |
+------+------------+
3 rows in set (0.01 sec)
9. AND multi criteria query
MariaDB [vincen]> select f_id,f_price,f_name #Query the three fields of F ﹣ ID, f ﹣ price and f ﹣ name
-> from fruits #From the fruits table
-> where s_id = '101' #If the value of s Ou ID is equal to 101
-> AND #also
-> f_price >= 5 #The value of F ﹐ price is greater than or equal to 5
-> ; #End
+------+---------+------------+
| f_id | f_price | f_name |
+------+---------+------------+
| a1 | 5.20 | apple |
| b1 | 10.20 | blackberry |
+------+---------+------------+
2 rows in set (0.01 sec)
10. OR multi criteria query
MariaDB [vincen]> select s_id,f_name,f_price #Query the three fields of s ﹣ ID, f ﹣ name and f ﹣ price
-> from fruits #From the fruits table
-> where s_id = 101 #If the value of the s UUID field is equal to 101
-> OR #perhaps
-> s_id = 102 #The value of the s-id field is equal to 102
-> ; #End
+------+------------+---------+
| s_id | f_name | f_price |
+------+------------+---------+
| 101 | apple | 5.20 |
| 101 | blackberry | 10.20 |
| 102 | orange | 11.20 |
| 101 | cherry | 3.20 |
| 102 | banana | 10.30 |
| 102 | grape | 5.30 |
+------+------------+---------+
6 rows in set (0.01 sec)
11. Sorting of query results (adding DESC is the opposite of the default)
MariaDB [vincen]> select f_name,f_price #Query the two fields of f_name and f_price
-> from fruits #In the fruits table
-> ORDER BY f_name; #Sort in order in the f? Name field
+------------+---------+
| f_name | f_price |
+------------+---------+
| apple | 5.20 |
| apricot | 2.20 |
| banana | 10.30 |
| berry | 7.60 |
| blackberry | 10.20 |
| cherry | 3.20 |
| coconut | 9.20 |
| grape | 5.30 |
| lemon | 6.40 |
| mango | 15.60 |
| melon | 8.20 |
| orange | 11.20 |
| xbababa | 3.60 |
| xbabay | 2.60 |
| xxtt | 11.60 |
| xxxx | 3.60 |
+------------+---------+
16 rows in set (0.01 sec)
MariaDB [vincen]> select f_name,f_price #Query the two fields of f_name and f_price
-> from fruits #From the fruits table
-> ORDER BY #sort
-> f_price #f_price
-> DESC #Reverse order (from large to small), opposite to the default order
-> ; #End
+------------+---------+
| f_name | f_price |
+------------+---------+
| mango | 15.60 |
| xxtt | 11.60 |
| orange | 11.20 |
| banana | 10.30 |
| blackberry | 10.20 |
| coconut | 9.20 |
| melon | 8.20 |
| berry | 7.60 |
| lemon | 6.40 |
| grape | 5.30 |
| apple | 5.20 |
| xxxx | 3.60 |
| xbababa | 3.60 |
| cherry | 3.20 |
| xbabay | 2.60 |
| apricot | 2.20 |
+------------+---------+
16 rows in set (0.00 sec)
12. Group query
MariaDB [vincen]> select s_id, #Query s-id field
-> COUNT(*) AS total #The total number of information related to s-id data is replaced by the total name
-> from fruits #From the fruits table
-> GROUP BY #Packet processing
-> s_id #S ID field name
-> ; #End
+------+-------+
| s_id | total |
+------+-------+
| 101 | 3 |
| 102 | 3 |
| 103 | 2 |
| 104 | 2 |
| 105 | 3 |
| 106 | 1 |
| 107 | 2 |
+------+-------+
7 rows in set (0.01 sec)
MariaDB [vincen]> select s_id, #Query s-id field
-> GROUP_CONCAT(f_name) AS name #Display the value of each field in the f ﹣ name group and replace the column name with name
-> FROM fruits #From the fruits table
-> GROUP BY #Packet processing
-> s_id #Field s_id
-> HAVING #Filter group information
-> COUNT(f_name) > 1; #The total amount of information in this column is greater than 1
+------+-------------------------+
| s_id | name |
+------+-------------------------+
| 101 | apple,blackberry,cherry |
| 102 | grape,banana,orange |
| 103 | apricot,coconut |
| 104 | lemon,berry |
| 105 | xbabay,xxtt,melon |
| 107 | xxxx,xbababa |
+------+-------------------------+
6 rows in set (0.00 sec)