I. data preparation
First, define the data table. The input statement is as follows.
CREATE TABLE fruits ( 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) );
Insert the following data:
INSERT INTO fruits (f_id, s_id, f_name, f_price) 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);
Use the SELECT statement to query the data of the f u ID field.
mysql> SELECT f_id, f_name FROM fruits; +------+------------+ | f_id | f_name | +------+------------+ | a1 | apple | | a2 | apricot | | b1 | blackberry | | b2 | berry | | b5 | xxxx | | bs1 | orange | | bs2 | melon | | c0 | cherry | | l2 | lemon | | m1 | mango | | m2 | xbabay | | m3 | xxtt | | o2 | coconut | | t1 | banana | | t2 | grape | | t4 | xbababa | +------+------------+ 16 rows in set (0.02 sec)
2. Form query
1 retrieve data for all fields from the fruits table.
mysql> 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.00 sec)
2 query all fruit names in the f ﹣ name column of fruits table.
mysql> SELECT f_name FROM fruits; +------------+ | f_name | +------------+ | apple | | apricot | | blackberry | | berry | | xxxx | | orange | | melon | | cherry | | lemon | | mango | | xbabay | | xxtt | | coconut | | banana | | grape | | xbababa | +------------+ 16 rows in set (0.00 sec)
3 get two columns of F ﹣ name and f ﹣ price from fruits table
The statement uses the SELECT declaration to get all fruit names and prices under the two fields named f_name and f_price from the fruits table, which are separated by commas.
select f_name,f_price from fruits +------------+---------+ | f_name | f_price | +------------+---------+ | apple | 5.20 | | apricot | 2.20 | | blackberry | 10.20 | | berry | 7.60 | | xxxx | 3.60 | | orange | 11.20 | | melon | 8.20 | | cherry | 3.20 | | lemon | 6.40 | | mango | 15.60 | | xbabay | 2.60 | | xxtt | 11.60 | | coconut | 9.20 | | banana | 10.30 | | grape | 5.30 | | xbababa | 3.60 | +------------+---------+ 16 rows in set (0.00 sec)
4 check the name of the fruit whose price is 10.2 yuan.
SELECT f_name, f_price FROM fruits WHERE f_price = 10.2; +------------+---------+ | f_name | f_price | +------------+---------+ | blackberry | 10.20 | +------------+---------+ 1 row in set (0.02 sec)
5 find the price of the fruit named "apple".
SELECT f_name, f_price FROM fruits WHERE f_name = 'apple'; +--------+---------+ | f_name | f_price | +--------+---------+ | apple | 5.20 | +--------+---------+ 1 row in set (0.00 sec)
6 query the name of fruit whose price is less than 10.
SELECT f_name, f_price FROM fruits WHERE f_price < 10; +---------+---------+ | f_name | f_price | +---------+---------+ | apple | 5.20 | | apricot | 2.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 | +---------+---------+ 11 rows in set (0.01 sec)
7 S U records with IDS 101 and 102.
SELECT s_id,f_name, f_price FROM fruits WHERE s_id IN (101,102) ORDER BY f_name; +------+------------+---------+ | 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)
8 query all records whose s ﹣ u id is neither equal to 101 nor equal to 102.
SELECT s_id,f_name, f_price FROM fruits WHERE s_id NOT IN (101,102) ORDER BY f_name; +------+---------+---------+ | s_id | f_name | f_price | +------+---------+---------+ | 103 | apricot | 2.20 | | 104 | berry | 7.60 | | 103 | coconut | 9.20 | | 104 | lemon | 6.40 | | 106 | mango | 15.60 | | 105 | melon | 8.20 | | 107 | xbababa | 3.60 | | 105 | xbabay | 2.60 | | 105 | xxtt | 11.60 | | 107 | xxxx | 3.60 | +------+---------+---------+ 10 rows in set (0.00 sec)
9. Find out the name and price of fruit whose price is between 2.00 yuan and 10.20 yuan.
mysql> SELECT f_name, f_price FROM fruits WHERE f_price BETWEEN 2.00 AND 10.20; +------------+---------+ | 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.00 sec)
10. Check the name and price of fruits whose price is between 2.00 yuan and 10.20 yuan.
SELECT f_name, f_price FROM fruits WHERE f_price NOT BETWEEN 2.00 AND 10.20; +--------+---------+ | f_name | f_price | +--------+---------+ | orange | 11.20 | | mango | 15.60 | | xxtt | 11.60 | | banana | 10.30 | +--------+---------+ 4 rows in set (0.00 sec)
11 find all fruits that begin with the letter 'b'.
SELECT f_id, f_name FROM fruits WHERE f_name LIKE 'b%'; +------+------------+ | f_id | f_name | +------+------------+ | b1 | blackberry | | b2 | berry | | t1 | banana | +------+------------+ 3 rows in set (0.00 sec)
12 in the fruits table, query the records with the letter 'g' in f_name.
SELECT f_id, f_name FROM fruits WHERE f_name LIKE '%g%'; +------+--------+ | f_id | f_name | +------+--------+ | bs1 | orange | | m1 | mango | | t2 | grape | +------+--------+ 3 rows in set (0.00 sec)
13 query the names of fruits that start with 'b' and end with 'y'.
SELECT f_name FROM fruits WHERE f_name LIKE 'b%y'; mysql> SELECT f_id, f_name -> FROM fruits -> WHERE f_name LIKE '%g%'; +------+--------+ | f_id | f_name | +------+--------+ | bs1 | orange | | m1 | mango | | t2 | grape | +------+--------+ 3 rows in set (0.00 sec)
14 in the fruits table, the query ends with the letter 'y', and there are only four letters before 'y'.
mysql> SELECT f_id, f_name FROM fruits WHERE f_name LIKE '____y'; +------+--------+ | f_id | f_name | +------+--------+ | b2 | berry | +------+--------+ 1 row in set (0.00 sec)
3. Condition query
Create the data table customers in the database, which contains the data needed in this chapter.
CREATE TABLE customers ( c_id int NOT NULL AUTO_INCREMENT, c_name char(50) NOT NULL, c_address char(50) NULL, c_city char(50) NULL, c_zip char(10) NULL, c_contact char(50) NULL, c_email char(255) NULL, PRIMARY KEY (c_id) );
To demonstrate the need to insert data, insert executes the following statement.
INSERT INTO customers(c_id, c_name, c_address, c_city, c_zip, c_contact, c_email) VALUES(10001, 'RedHook', '200 Street ', 'Tianjin', '300000', 'LiMing', '[email protected]'), (10002, 'Stars', '333 Fromage Lane', 'Dalian', '116000', 'Zhangbo','[email protected]'), (10003, 'Netbhood', '1 Sunny Place', 'Qingdao', '266000', 'LuoCong', NULL), (10004, 'JOTO', '829 Riverside Drive', 'Haikou', '570000', 'YangShan', '[email protected]'); mysql> SELECT COUNT(*) AS cust_num FROM customers; +----------+ | cust_num | +----------+ | 4 | +----------+ 1 row in set (0.01 sec)
15 query the values of the C UU ID, C UU name, and C UU email fields of records with empty C UU email in the customers table.
mysql> SELECT c_id, c_name,c_email FROM customers WHERE c_email IS NULL; +-------+----------+---------+ | c_id | c_name | c_email | +-------+----------+---------+ | 10003 | Netbhood | NULL | +-------+----------+---------+ 1 row in set (0.01 sec)
16 query the values of the C UU ID, C UU name, and C UU email fields of the records whose C UU email is not empty in the customers table.
mysql> SELECT c_id, c_name,c_email FROM customers WHERE c_email IS NOT NULL; +-------+---------+-------------------+ | c_id | c_name | c_email | +-------+---------+-------------------+ | 10001 | RedHook | [email protected] | | 10002 | Stars | [email protected] | | 10004 | JOTO | [email protected] | +-------+---------+-------------------+ 3 rows in set (0.00 sec)
17 in the fruits table, query the fruit price and name with s_id = 101 and f_price greater than or equal to 5.
mysql> SELECT f_id, f_price, f_name FROM fruits WHERE s_id = '101' AND f_price >=5; +------+---------+------------+ | f_id | f_price | f_name | +------+---------+------------+ | a1 | 5.20 | apple | | b1 | 10.20 | blackberry | +------+---------+------------+ 2 rows in set (0.00 sec)
18 in the fruits table, query the fruit price and name with s_id = 101 or 102, f_price greater than 5, and f_name = apple.
mysql> SELECT f_id, f_price, f_name FROM fruits WHERE s_id IN('101', '102') AND f_price >= 5 AND f_name = 'apple'; +------+---------+--------+ | f_id | f_price | f_name | +------+---------+--------+ | a1 | 5.20 | apple | +------+---------+--------+ 1 row in set (0.00 sec)
19 query f ﹐ price and f ﹐ name of fruit supplier with s ﹐ id = 101 or s ﹐ id = 102.
mysql> SELECT s_id,f_name, f_price FROM fruits WHERE s_id = 101 OR s_id = 102; +------+------------+---------+ | 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.00 sec)
20 query f ﹐ price and f ﹐ name of fruit supplier with s ﹐ id = 101 or s ﹐ id = 102.
mysql> SELECT s_id,f_name, f_price FROM fruits WHERE s_id IN(101,102); +------+------------+---------+ | 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.00 sec)
21 query the value of the s UUID field in the fruits table, and return the value of the s UUID field without repetition.
mysql> SELECT DISTINCT s_id FROM fruits; +------+ | s_id | +------+ | 101 | | 103 | | 104 | | 107 | | 102 | | 105 | | 106 | +------+ 7 rows in set (0.00 sec)
22 query the value of F ﹣ name field in fruits table and sort it.
mysql> SELECT f_name FROM fruits ORDER BY f_name; +------------+ | f_name | +------------+ | apple | | apricot | | banana | | berry | | blackberry | | cherry | | coconut | | grape | | lemon | | mango | | melon | | orange | | xbababa | | xbabay | | xxtt | | xxxx | +------------+
23 query the f ﹣ name and f ﹣ price fields in the fruits table. First, sort by F ﹣ name, then by F ﹣ price.
mysql> SELECT f_name, f_price FROM fruits ORDER BY f_name, f_price; +------------+---------+ | 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.00 sec)
24 query the f ﹣ name and f ﹣ price fields in the fruits table, and sort the results in descending order of F ﹣ price.
mysql> SELECT f_name, f_price FROM fruits ORDER BY f_price DESC; +------------+---------+ | 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)
25 to query the fruits table, first sort in descending order of F ﹣ price, and then sort in ascending order of F ﹣ name field.
mysql> SELECT f_price, f_name FROM fruits ORDER BY f_price DESC, f_name; +---------+------------+ | f_price | f_name | +---------+------------+ | 15.60 | mango | | 11.60 | xxtt | | 11.20 | orange | | 10.30 | banana | | 10.20 | blackberry | | 9.20 | coconut | | 8.20 | melon | | 7.60 | berry | | 6.40 | lemon | | 5.30 | grape | | 5.20 | apple | | 3.60 | xbababa | | 3.60 | xxxx | | 3.20 | cherry | | 2.60 | xbabay | | 2.20 | apricot | +---------+------------+ 16 rows in set (0.00 sec)
26 the data in the fruits table is grouped according to the s Ou ID.
mysql> SELECT s_id, COUNT(*) AS Total FROM fruits GROUP BY s_id; +------+-------+ | s_id | Total | +------+-------+ | 101 | 3 | | 102 | 3 | | 103 | 2 | | 104 | 2 | | 105 | 3 | | 106 | 1 | | 107 | 2 | +------+-------+ 7 rows in set (0.00 sec)
27 group the data in the fruits table according to the s Ou ID, and display the fruit name of each supplier.
mysql> SELECT s_id, GROUP_CONCAT(f_name) AS Names FROM fruits GROUP BY s_id; +------+-------------------------+ | s_id | Names | +------+-------------------------+ | 101 | apple,blackberry,cherry | | 102 | orange,banana,grape | | 103 | apricot,coconut | | 104 | berry,lemon | | 105 | melon,xbabay,xxtt | | 106 | mango | | 107 | xxxx,xbababa | +------+-------------------------+ 7 rows in set (0.00 sec)
28 the data in fruits table are grouped according to the s Ou ID, and the grouping information of fruit species greater than 1 is displayed.
mysql> SELECT s_id, GROUP_CONCAT(f_name) AS Names FROM fruits GROUP BY s_id HAVING COUNT(f_name) > 1; +------+-------------------------+ | s_id | Names | +------+-------------------------+ | 101 | apple,blackberry,cherry | | 102 | orange,banana,grape | | 103 | apricot,coconut | | 104 | berry,lemon | | 105 | melon,xbabay,xxtt | | 107 | xxxx,xbababa | +------+-------------------------+ 6 rows in set (0.00 sec)
29 group the data in the fruits table according to the s Ou ID and display the number of records.
mysql> SELECT s_id, COUNT(*) AS Total FROM fruits GROUP BY s_id WITH ROLLUP; +------+-------+ | s_id | Total | +------+-------+ | 101 | 3 | | 102 | 3 | | 103 | 2 | | 104 | 2 | | 105 | 3 | | 106 | 1 | | 107 | 2 | | NULL | 16 | +------+-------+ 8 rows in set (0.00 sec)
To demonstrate the effect, create a data table first.
CREATE TABLE orderitems ( o_num int NOT NULL, o_item int NOT NULL, f_id char(10) NOT NULL, quantity int NOT NULL, item_price decimal(8,2) NOT NULL, PRIMARY KEY (o_num,o_item) ) ;
Then insert the presentation data.
INSERT INTO orderitems(o_num, o_item, f_id, quantity, item_price) VALUES(30001, 1, 'a1', 10, 5.2), (30001, 2, 'b2', 3, 7.6), (30001, 3, 'bs1', 5, 11.2), (30001, 4, 'bs2', 15, 9.2), (30002, 1, 'b3', 2, 20.0), (30003, 1, 'c0', 100, 10), (30004, 1, 'o2', 50, 2.50), (30005, 1, 'c0', 5, 10), (30005, 2, 'b1', 10, 8.99), (30005, 3, 'a2', 10, 2.2), (30005, 4, 'm1', 5, 14.99);
30 query the order number and total order price whose order price is greater than 100.
The total order price in the orderTotal column of the returned result is not displayed in a certain order. Next, use the ORDER BY keyword to sort the results by the total order price.
mysql> SELECT o_num, SUM(quantity * item_price) AS orderTotal FROM orderitems GROUP BY o_num HAVING SUM(quantity*item_price) >= 100; +-------+------------+ | o_num | orderTotal | +-------+------------+ | 30001 | 268.80 | | 30003 | 1000.00 | | 30004 | 125.00 | | 30005 | 236.85 | +-------+------------+ 4 rows in set (0.02 sec)
31 shows the first four rows of the results of the fruits table query.
mysql> SELECT * From fruits LIMIT 4; +------+------+------------+---------+ | 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 | +------+------+------------+---------+ 4 rows in set (0.00 sec)
32 in the fruits table, using the LIMIT clause, returns a record with a length of 3 rows starting from the fifth record.
mysql> SELECT * From fruits LIMIT 4, 3; +------+------+--------+---------+ | f_id | s_id | f_name | f_price | +------+------+--------+---------+ | b5 | 107 | xxxx | 3.60 | | bs1 | 102 | orange | 11.20 | | bs2 | 105 | melon | 8.20 | +------+------+--------+---------+ 3 rows in set (0.00 sec)
cakincqm Released 4086 original articles, won 554 praises and 3.03 million visitors+ His message board follow