Introduction to MySQL (6)-SQL data query based on datagrip

table of Contents

1. Preparation

1. Data preparation

Copy the data in the file to datagrip for execution, and build the table

Study the data in this chapter

Insert picture description here

2. Perform interface preparation

If you close the execution interface accidentally,

Insert picture description here


you can do this:

Insert picture description here


Insert picture description here


you can open the interface you just executed.

3. Data modification preparation

If you have not remembered the command to modify the data in the table, you can modify it directly in the table, and click the green upload icon after the modification is completed. This is the benefit of the graphical tool

Insert picture description here

Two, query

1. Grammar

select [distinct]    #distinct可写可不写
* | 列名,列名
from 表
where 条件;

2. Simple query

Take the data prepared above as an example

Insert picture description here
# 1、查询所有的商品
SELECT * FROM products;  
# * 是一个通配符,它和查询所有的列,效果是一样的
# 但底层数据执行的时候,效率是不一样的,所以项目开发时,一般都写出所有的列,而不用*

# 2、查询单个列:从products表中查询所有的产品名称(prod_name)
SELECT prod_name FROM products;

# 3、全限定列名和全限定表名
SELECT products.prod_name FROM mydb1.products;

# 4、查询多个列:从products表中查询产品id(prod_id)、产品名称(prod_name)、产品价格(prod_price)
SELECT prod_id,prod_name,prod_price FROM products;

# 5、在 vend_id 列有很多重复值,如何去除重复值,让每一种厂商的id只保留一份呢?
SELECT DISTINCT vend_id FROM products;
# 注意:DISTINCT关键字应用于所有列而不仅是跟它挨着的列。

3. Number limit query

Limit is a MySQL-specific syntax, and its function is to limit the number of query results. format:

select 列名 from 表名 limit [m,n]

When not writing m, return the first n data. When writing m, the database will start from row m and return n pieces of data;
note that the row number in MySQL starts from 0, which means that the first piece of data is row 0, not row 1.

(1) Simple query

# 查询前3条产品名称
SELECT prod_name FROM products LIMIT 3;
# 从第二条开始查询,取5条数据
SELECT prod_name FROM products LIMIT 1,5;
# 取出第3条至第6条,4条记录
select * from tablename limit 2,4

(2) Limit for paging

Pagination query format:

SELECT * FROM 表名 LIMIT startRow,pageSize;

E.g:

select * from products limit 0,5; #第一页,每页显示5条。
select * from products limit 5,5; #第二页,每页显示5条。
select * from products limit 10,5; #第三页,每页显示5条。

and also:

select * from products limit startRow,5; #第curPage页,每页显示5条

How to calculate the value of startRow?
-The page number and page number (page size) are calculated in the background-
The relevant data required for paging is analyzed as follows,
-Note: The following pseudo code is not used for execution

int curPage = 2; -- 当前页数
int pageSize = 5; -- 每页显示数量
int startRow = (curPage - 1) * pageSize; -- 当前页, 记录开始的位置(行数)计算
int totalSize = select count(*) from products; -- 记录总数量
int totalPage = Math.ceil(totalSize * 1.0 / pageSize); -- 总页数

4. Sort

Relational database design theory believes that if the sort order is not clearly specified, it should not be assumed that the order of the retrieved data is meaningful.
In other words, if the query results are not sorted, the database does not guarantee that the query results are in order. How to sort it?
Through the order by statement, the results of the query can be sorted. format:

SELECT 列名,... FROM 表名 ORDER BY 排序字段 ASC|DESC;
# ASC 升序 (默认), 从小到大排序
# DESC 降序, 从大到小排序

(1) Sort by single column: query the product table, and sort by price ascending and descending

SELECT prod_id,prod_name ,prod_price FROM products ORDER BY prod_price;
SELECT prod_id,prod_name ,prod_price FROM products ORDER BY prod_price DESC ;

(2) Chinese sorting: query the product table, and sort the Chinese by product name, format:

SELECT * FROM 表名 ORDER BY CONVERT(排序列名 USING gbk) ASC|DESC;
SELECT prod_id, prod_name FROM products ORDER BY CONVERT(prod_name USING gbk) ;

(3) Multi-column sorting: If you want to sort in descending order on multiple columns, you must specify the DESC keyword for each column.

#先按供应商id排序,再按产品价格排序
SELECT vend_id,prod_price FROM products ORDER BY vend_id,prod_price ;
# 先按供应商id降序,再按产品价格升序
SELECT vend_id,prod_price FROM products ORDER BY vend_id DESC ,prod_price ;

(4) Use the combination of order by and limit to find the names of the three most expensive products

SELECT prod_name,prod_price FROM products ORDER BY prod_price DESC LIMIT 3;

5. Condition query

Comparison operator<, >, <=, >=, =, <>,! =Less than, greater than, less than or equal to, greater than or equal to, not equal to, not equal to
\BETWEEN...AND...Value displayed in a certain interval (including head and tail)
\IN(set)The value displayed in the in list, for example: in(100,200)
\LIKE'character'Fuzzy query, in the like statement,% represents zero or more arbitrary characters, and _ represents one character, for example: first_name like'_a%';
\IS NULLDetermine whether it is empty
Logical operatorandMultiple conditions are met at the same time
\orAny one of multiple conditions is true
\notNot true, for example: WHERE vend_id NOT IN(1001,1002);
# 查询价格为55.00的商品名称和价格
SELECT prod_name,prod_price FROM products WHERE prod_price=55.00;

# 查询价格不等于55.00的商品名称和价格
SELECT prod_name,prod_price FROM products WHERE prod_price!=55.00;
SELECT prod_name,prod_price FROM products WHERE prod_price<>55.00;

# 查询价格在5-10元之间的商品名称和价格
SELECT prod_name,prod_price FROM products WHERE prod_price>=5.00 AND prod_price<=10.00;
SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 5.00 AND 10.00;

# 查询产品描述为null的商品名称和商品描述
SELECT prod_name,prod_desc FROM products WHERE prod_desc IS NULL ;

# 查询产品描述不为null的商品名称和商品描述
SELECT prod_name,prod_desc FROM products WHERE prod_desc IS NOT NULL ;

# 查询供应商id为1001 或者1002的商品名称、供应商id、商品价格
SELECT prod_name,vend_id,prod_price FROM products WHERE vend_id =1001 OR vend_id=1002;

# 查询供应商id为1001 或者1002的商品名称、供应商id、商品价格 并且价格大于10元 
# 注意优先级,and优先级大于or
SELECT prod_name,vend_id,prod_price FROM products WHERE vend_id =1001 OR vend_id=1002 AND prod_price>10;

# 查询供应商id为1001 或者1002的商品名称、供应商id、商品价格 ---用in() 实现
SELECT prod_name,vend_id,prod_price FROM products WHERE vend_id IN (1001,1002);
# 注意:in() 和 or 的作用差不多,但in()更强大,因为in里面可以包含其他select语句。

# 查询供应商id不为1001 或者1002的商品名称、供应商id、商品价格 ---用not in() 实现
SELECT prod_name,vend_id,prod_price FROM products WHERE vend_id NOT IN(1001,1002);
# 注意:MySQL支持使用NOT对IN、BETWEEN和 EXISTS子句取反 ,EXISTS子句后面会讲。

6. Fuzzy query

The format is as follows: (in the statement,% represents zero or more arbitrary characters, and _ represents one character)

SELECT 列名,... FROM 表名 WHERE 列名 LIKE 条件;

Example 1: Query the product name starting with i in the product name

SELECT prod_name FROM products WHERE prod_name LIKE 'i%';

Note 1:
MySQL is case-insensitive by default, so the query for lowercase i and uppercase I will also be queried.
If you want to be case-sensitive, there are two ways to
modify the table structure, adding the binary keyword after the field, but Modifying the table structure is generally not recommended.
The second is to add the BINARY format to the SQL statement:SELECT 列名 FROM 表名 WHERE BINARY 列名 LIKE '字符';

SELECT prod_name FROM products WHERE BINARY prod_name LIKE 'i%';

Note 2:
% can match 0 to more characters, but cannot match null

SELECT * FROM products WHERE prod_desc LIKE '%';

Example 2: Query the product whose second word in the product name is "王"

SELECT * FROM products WHERE prod_name LIKE '_王%';

Note:
Although wildcards are easy to use, don't abuse them. Try not to put% at the top, for example: where like'%王'. Because this is also very slow in search efficiency, the specific reasons will be discussed in depth in the advanced stage.

7, regular expressions

Regular expression (regular expression) describes a string matching pattern (pattern), can be used to check whether a string contains a certain substring, replace the matched substring, or extract from a string that meets a certain condition Substring etc.

For example, to determine whether the mailbox format entered by the user is legal, regular expressions can be used. To determine whether the user password contains special characters, regular expressions can be used. To extract phone numbers from a text file, you can use regular expressions.

If you need to find all files with a number in the name, you can use a regular expression in the format:

SELECT 列名 FROM 表名 WHERE 列名 REGEXP '条件';

(1) Query the product name that contains the two words "水" or "40" in the product name-use regular expression |

SELECT prod_name FROM products WHERE prod_name REGEXP '水|40';

(2) Query the product name with letters in the product name

SELECT prod_name FROM products WHERE prod_name REGEXP '[a-z]';