Basic query of MySQL database

1. Create required tables and insert data

#Create data table
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,2) not null, primary key(f_id) );
#insert data
mysql> 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.7'),
    -> ('m2',105,'xbabay','2.6'),
    -> ('t4',107,'xbababa','2.6'),
    -> ('m3',105,'xxtt','11.6'),
    -> ('b5',107,'xxxx','3.6');
#Create a second table
mysql> 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)
    -> );
#Second table insert data
mysql> 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','LMing@163.com'),
    -> (1002,'Stars','333 Fromage Lane','Dalian','116000','Zhangbo','Jerry@hotnail.com'),
    -> (10003,'Netbhood','1 Sunny Place','Qingdao','266000','LuoCong',NULL),
    -> (1004,'JOTO','829 Riverside Drive','Haikou','570000','YangShan','sam@hotmail.com');

2. View all data for both tables


3. Query the data of the f ﹣ name column in the fruits table

mysql> select f_name from fruits;


4. Query the data of f_name and f_price columns in fruits table

mysql> select f_name,f_price from fruits;


5. Query the columns of f'name and f'price in the fruits table, and the value of f'price is equal to 5.2

mysql> select f_name,f_price from fruits where f_price=5.2;


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 10

mysql> select f_name,f_price from fruits where f_price >= 10;


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 f_name,f_price from fruits where f_price between 2 and 8;


8. Query the f ﹣ name and s ﹣ ID columns in the fruits table, and the value of s ﹣ ID is 101 or 103

Different methods, same results
 Query method 1:

mysql> select f_name,s_id from fruits where s_id = 101 or s_id = 103;

Query method 2:

mysql> select f_name,s_id from fruits where s_id in(101,103);


9. Query the f ﹣ name and s ﹣ ID columns in the fruits table, and the value of s ﹣ ID is not 101 or 103
Query method 1

mysql> select f_name,s_id from fruits where s_id != 101 and s_id != 103;


Query method 2

mysql> select f_name,s_id from fruits where s_id not in(101,103);


10. Use of fuzzy queries "%" and ""

#Query the f u name column in the fruits table, and the value begins with "b"
mysql> select f_name from fruits where f_name like 'b%';

#Query the f u name column in the fruits table, and the value starts with "b" and ends with "y"
mysql> select f_name from fruits where f_name like 'b%y';

#Query the f u name column in the fruits table. The value starts with "b" and ends with "Y". There are three characters between b and y
mysql> select f_name from fruits where f_name like 'b___y';


11. Query the rows in the fruits table with the value of s_id 101 and the value of f_price greater than 2.0

mysql> select * from fruits where s_id = 101 and f_price > '2.0';


12. The value of s_id in the fruits table is 101 or 103 and the value of f_price column is greater than 5

mysql> select * from fruits where s_id in(101,103) and f_price > 5;


13. Query the s UU ID column in the fruits table and remove duplicate values

mysql> select distinct s_id from fruits;


14. Query the s UU ID and f UU name columns in the fruits table, and sort the results by s UU ID

mysql> select s_id,f_name from fruits order by s_id;

15. Query the f'name and f'price columns in the fruits table, and sort them in the f'name and f'price columns

mysql> select f_name,f_price from fruits order by f_name,f_price;


Note: for multi field sorting, if the first field is the same, it will be sorted by the second field, and so on. If the first field is different, it will be sorted by the first paragraph directly.
16. Query the f ﹣ price column in the fruits table and sort the results in descending order

#The default is asc ascending sort, which can be changed from DESC to descending sort
mysql> select f_price from fruits order by f_price desc;


17. Query the number of times that different values of the s ﹣ ID column appear in fruits, and display them in groups

#Call the count(*) function to count the number of times, set the alias through as, and group by
mysql> select s_id,count(*) as total from fruits group by s_id;


18. Query all the values of the f ﹣ name column corresponding to each same s ﹣ ID in the fruits table. The value of F ﹣ name is displayed in one row and has more than one value

mysql> select s_id,group_concat(f_name) as name from fruits group by s_id having count(f_name) > 1;


19. Query the row in the customers table where the c'email column is null

mysql> select * from customers where c_email is null;

Tags: Linux MySQL

Posted on Mon, 18 May 2020 05:18:47 -0400 by dstar101