MySQL - NOT REGEXP 運算子

要使用 Node.js 程式透過 MySQL 查詢檢索資料庫中不滿足指定模式的所有記錄，我們需要使用 mysql2 庫的 query() 函式執行“SELECT”語句，如下所示：

sql = "SELECT * FROM person_tbl WHERE NAME NOT REGEXP '^sa'";
con.query(sql);

要使用Java程式透過MySQL查詢檢索資料庫中所有不滿足指定模式的記錄，我們需要使用JDBC函式executeUpdate()執行“SELECT”語句，如下所示：

String sql = "SELECT * FROM person_tbl WHERE NAME NOT REGEXP '^sa'";
statement.executeQuery(sql);

要使用Python程式透過MySQL查詢檢索資料庫中所有不滿足指定模式的記錄，我們需要使用MySQL Connector/Python的execute()函式執行“SELECT”語句，如下所示：

sql = "SELECT * FROM person_tbl WHERE NAME NOT REGEXP '^sa'" 
cursorObj.execute(sql)

var mysql = require('mysql2');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});
 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
  //console.log("Connected successfully...!");
  //console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 sql = "SELECT * FROM person_tbl WHERE NAME NOT REGEXP '^sa'";
 console.log("Select query executed successfully..!");
 console.log("Table records: ");
 con.query(sql);
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 });
});    

輸出

生成的輸出如下：

Select query executed successfully..!
Table records:
[
  { ID: 1, NAME: 'John', AGE: 33, ADDRESS: 'New York' },
  { ID: 2, NAME: 'Ram', AGE: 29, ADDRESS: 'Pune' },
  { ID: 4, NAME: 'Tanya', AGE: 26, ADDRESS: 'Paris' },
  { ID: 5, NAME: 'Anmol', AGE: 28, ADDRESS: 'Surat' },
  { ID: 6, NAME: 'Ramesh', AGE: 40, ADDRESS: 'Mumbai' }
]    

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class regexp_not {
    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 person_tbl WHERE NAME NOT REGEXP '^sa'";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("Name");
                String age = rs.getString("AGE");
                String address = rs.getString("ADDRESS");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}          

輸出

獲得的輸出如下所示：

Table records: 
Id: 1, Name: John, Age: 33, Address: New York
Id: 2, Name: Ram, Age: 29, Address: Pune
Id: 4, Name: Tanya, Age: 26, Address: Paris
Id: 5, Name: Anmol, Age: 28, Address: Surat
Id: 6, Name: Ramesh, Age: 40, Address: Mumbai

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
   host='localhost',
   user='root',
   password='password',
   database='tut'
)
#Creating a cursor object 
cursorObj = connection.cursor()
notregexp_operator_query = f"SELECT * FROM person_tbl WHERE NAME NOT REGEXP '^sa'"
cursorObj.execute(notregexp_operator_query)
# Fetching all the results
results = cursorObj.fetchall()
# display the results
print("Persons whose name does not start with 'sa' using NOT REGEXP:")
for row in results:
    print(row)
cursorObj.close()
connection.close()    

輸出

以上程式碼的輸出如下：

Persons whose name does not start with 'sa' using NOT REGEXP:
(1, 'John', 33, 'New York')
(2, 'Ram', 29, 'Pune')
(4, 'Tanya', 26, 'Paris')
(5, 'Anmol', 28, 'Surat')
(6, 'Ramesh', 40, 'Mumbai')