MySQL - LIKE 运算符

要通过 Node.js 程序根据指定模式从表中获取记录，我们需要使用 mysql2 库中的 query() 函数执行带有 LIKE 子句的 SELECT 语句，如下所示 -

sql= "SELECT column_name(s) FROM table_name
WHERE column_name LIKE [condition]";
Con.query(sql);

要通过 Java 程序根据指定模式从表中获取记录，我们需要使用 JDBC 函数 executeUpdate() 执行带有 LIKE 子句的 SELECT 语句，如下所示 -

String sql = "SELECT column_name(s) FROM table_name
WHERE column_name LIKE [condition]";
statement.executeQuery(sql);

要通过 Python 程序根据指定的模式从表中获取记录，我们需要使用 MySQL Connector/Python 的 execute() 函数执行带有 LIKE 子句的 SELECT 语句，如下所示 -

like_Operator_query = "SELECT column1, column2, ...
FROM table_name WHERE column_name LIKE pattern"
cursorObj.execute(like_Operator_query)

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});
 //连接到 MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");
 //创建数据库
  sql = "create database TUTORIALS"
  con.query(sql);
 //选择数据库
  sql = "USE TUTORIALS"
  con.query(sql);
 //创建表
  sql = "CREATE TABLE IF NOT EXISTS tutorials_tbl(tutorial_id INT NOT NULL PRIMARY KEY, tutorial_title VARCHAR(100) NOT NULL, tutorial_author VARCHAR(40) NOT NULL, submission_date DATE);"
  con.query(sql);
 //插入记录
  sql = "INSERT INTO tutorials_tbl(tutorial_id, tutorial_title, tutorial_author, submission_date) VALUES(1, 'Learn PHP', 'John Paul', NOW()), (2, 'Learn MySQL', 'Abdul S', NOW()), (3, 'JAVA Tutorial', 'Sanjay', '2007-05-21'), (4, 'Python Tutorial', 'Sasha Lee', '2016-09-04'), (5, 'Hadoop Tutorial', 'Chris Welsh', NOW());"
  con.query(sql);
 //使用 LIKE 运算符
  sql = "SELECT * from tutorials_tbl WHERE tutorial_author LIKE '%jay';"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

输出

生成的输出如下 -

Connected!
--------------------------
[
  {
    tutorial_id: 3,
    tutorial_title: 'JAVA Tutorial',
    tutorial_author: 'Sanjay',
    submission_date: 2007-05-20T18:30:00.000Z
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class LikeClause {
	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/TUTORIALS";
		String user = "root";
		String password = "password";
		ResultSet rs;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT * from tutorials_tbl WHERE tutorial_author LIKE '%jay'";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()){
            	String Id = rs.getString("tutorial_id");
            	String Title = rs.getString("tutorial_title");
            	String Author = rs.getString("tutorial_author");
            	String Date = rs.getString("submission_date");
            	System.out.println("Id: " + Id + ", Title: " + Title + ", Author: " + Author + ", Submission-date: " + Date);
            }
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

输出

获得的输出如下所示 -

Table records: 
Id: 3, Title: Learning Java, Author: Sanjay, Submission-date: 2007-05-06

import mysql.connector
#建立连接
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
like_Operator_query = """
SELECT tutorial_id, tutorial_title, tutorial_author, submission_date
FROM tutorials_tbl
WHERE tutorial_title LIKE '%Tutorial%'
"""
cursorObj.execute(like_Operator_query)
# 获取所有匹配的行
matching_rows = cursorObj.fetchall()
# 打印匹配的行
for row in matching_rows:
    print(row)
cursorObj.close()
connection.close()

输出

以下是上述代码的输出 -

(3, 'JAVA Tutorial', 'Sanjay', datetime.date(2007, 5, 6))
(4, 'Python Tutorial', 'Sasha Lee', datetime.date(2016, 9, 4))
(5, 'Hadoop Tutorial', 'Chris Welsh', datetime.date(2023, 3, 28))
(6, 'R Tutorial', 'Vaishnav', datetime.date(2011, 11, 4))