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"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  //Creating a Database
  sql = "create database TUTORIALS"
  con.query(sql);

  //Select database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Creating table
  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);

  //Inserting Records
  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);

  //Using LIKE operator
  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://: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
#establishing the connection
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)
# Fetch all the matching rows
matching_rows = cursorObj.fetchall()
# Printing the matching rows
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))