Shulou(Shulou.com)06/01 Report--

1. Create the required table and insert data mysql > create table fruits (f_id char (10) not null, s_id int not null, f_name char (255) not null, f_price decimal (8) not null, primary key (f_id)) Mysql > insert into fruits,-> values ('a1jewelry, 101pr.), > values,-> (' b1pl, 101pl), page10.2'),-> ('bs1',102,'orange','11.2'),-> (' bs2',105,'melon','8.2'),-> ('t1p102p102, 102pl). -> ('t _ 2),-> ('t _ 2),-> ('o _ 2), _ ()), _ > (), _ > (c _ 0 _ 2), _ _), _ > (_), _ > (_ -> ('m1jewelry 106pencils),-> (' m2pc105pr) xbabaylys, 2.6'),-> ('t4pr 107pence xbababaqiao pencils 2.6'),-> (' m3p3p1105pics xxttables pencils 11.6'),-> ('b5m5pl 107pencils xxxxcopies pencils 3.6') Query OK, 16 rows affected Records: 16 Duplicates: 0 Warnings: 0mysql > 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 (50) null,-> c_contact char (50) null,-> c_email char (50) null -> primary key (c_id)->) Query OK, 0 rows affected (0.00 sec) mysql > insert into customers)-> values (200Street','Tianjin','300000','LiMing','LMing@163.com')-> values (1002 Fromage Lane','Dalian','116000','Zhangbo','Jerry@hotnail.com'),-> (10003 Fromage Lane','Dalian','116000','Zhangbo','Jerry@hotnail.com') '1 Sunny Place','Qingdao','266000','LuoCong',NULL),-> (1004 Riverside Drive','Haikou','570000','YangShan','sam@hotmail.com') Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 02, view all data for both tables

3. Query the data mysql > select f_name from fruits of the f_name column in the fruits table

Query results:

4. Query the data of the f_name and f_price columns in the fruits table mysql > select fanciname.fareprice from fruits

The returned result is as follows:

5. Query the columns of f_name and f_price in the fruits table, and the value of f_price is equal to 5.2mysql > select fanciname.

The result returned is as follows:

6. Query the columns of f_name and f_price in the fruits table, and the value of f_price is greater than or equal to 10mysql > select fanciname > = 10

The result returned is as follows:

7. Query the columns of f_name and f_price in the fruits table, and the value of f_price is between 2 and 8 mysql > select fanciname and 2 and 8

The result returned is as follows:

8. Query the f_name and s_id columns in the fruits table, and the value of s_id is 101or 103.Query method 1:mysql > select fancinameLiger from fruits-> where s_id = 101 or s_id = 103; query method 2:mysql > select fancinametemery sdescripid from fruits-> where s_id in (101103)

The results returned by the above two query statements are the same, as follows:

9. Query the f_name and s_id columns in the fruits table, and the value of s_id is not 101or 103.Query method 1:mysql > select fancinameLegendary from fruits-> where s_id! = 101 and s_id! = 103; querying method 2:mysql > select fancinamesignsaccounid from fruits-> where s_id not in (101103)

The result returned is as follows:

10. The use of fuzzy queries "%" and "_" mysql > select f_name from fruits where f_name like'b%'

The result returned is as follows:

Mysql > select f_name from fruits where f_name like'b% y'

The result returned is as follows:

Mysql > select f_name from fruits where f_name like 'baked goods

11. Query rows in the fruits table where the value of s_id is 101 and the value of f_price is greater than 2.0 mysql > select * from fruits-> where s_id = 101 and f_price > '2.0'

The result returned is as follows:

12. Query the value of s_id in the fruits table is 101or 103and the value of f_price column is greater than 5mysql > select * from fruits where-> s_id in (101103) and f_price > 5

13. Query the s_id column in the fruits table and remove the duplicate value mysql > select distinct s_id from fruits

The returned result is as follows:

14. Query the s_id and f_name columns in the fruits table, and sort the results with s_id mysql > select

The result returned is as follows:

15. Query the f_name and f_price columns in the fruits table, and sort by the f_name and f_price columns mysql > select fanciname from fruits-> order by fanciname camera fanciprice

The result returned is as follows:

Note: multi-field sorting, if the first sorted field is the same, it will be sorted by the second field, and so on, and if the first field is different, it will be sorted directly by the first paragraph.

16. Query the f_price column in the fruits table and sort the results in descending order mysql > select f_price from fruits order by f_price desc

17. Query the number of times that different values of s_id column appear in fruits, and group them to show mysql > select sdescripidjournal count (*) as total from fruits group by s_id

The returned result is as follows:

18. Query all the values of the f_name column corresponding to each same s_id in the fruits table. The value of the f_name is displayed in a row, and its value is more than 1 mysql > select scuridmage groupconcat (f_name) as name from fruits group by s_id having count (f_name) > 1.

The result returned is as follows:

19. Query the row with null c_email column in customers table mysql > select * from customers where c_email is null

The query results are as follows:

-this is the end of this article. Thank you for reading-

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.