MySQL - NOT 運算子

Hello there, future database wizards! Today, we're going to dive into the magical world of the MySQL NOT operator. Don't worry if you're new to this - I'll be your friendly guide through this adventure, and by the end, you'll be wielding the NOT operator like a pro!

MySQL NOT 運算子

The NOT operator is like the rebel of the MySQL world. It's all about negation, turning things upside down, and saying "nope" to conditions. Imagine you're a teacher (like me!) and you want to find all the students who haven't submitted their homework. That's where NOT comes in handy!

Let's start with a simple example:

SELECT * FROM students WHERE NOT grade = 'A';

This query will fetch all students who didn't get an A. It's like saying, "Show me everyone except the A students." Pretty cool, right?

Here's another one:

SELECT * FROM products WHERE NOT price > 100;

This will give us all products that don't cost more than $100. It's a sneaky way of saying "Show me products that cost $100 or less."

NOT with IN Operator

Now, let's level up and combine NOT with the IN operator. The IN operator is like a VIP list, and NOT IN is saying "everyone who's not on the list."

SELECT * FROM fruits WHERE name NOT IN ('apple', 'banana', 'orange');

This query will show all fruits except apples, bananas, and oranges. It's like having a fruit bowl and picking out everything that's not those three fruits.

NOT with IS NULL Operator

NULL values in databases are like ghosts - they're there, but not really. The NOT IS NULL operator helps us find all the non-ghost values.

SELECT * FROM customers WHERE phone_number IS NOT NULL;

This will find all customers who have provided a phone number. It's like calling out "Marco!" and waiting for everyone who can respond with "Polo!"

NOT with LIKE Operator

The LIKE operator is used for pattern matching, and NOT LIKE is its mischievous twin. It's perfect for finding things that don't match a certain pattern.

SELECT * FROM books WHERE title NOT LIKE 'The%';

This query will find all books whose titles don't start with "The". It's like organizing a bookshelf and putting all the "The" books on one side and everything else on the other.

NOT with BETWEEN Operator

BETWEEN helps us find values within a range, and NOT BETWEEN does the opposite. It's like setting up a "Kids Only" zone and then finding everyone who's not allowed in.

SELECT * FROM employees WHERE age NOT BETWEEN 25 AND 35;

This will find all employees who are either younger than 25 or older than 35. It's a great way to segment your data!

NOT with UPDATE Statement

The NOT operator isn't just for SELECT statements. We can use it in UPDATE statements too! Let's see an example:

UPDATE inventory SET in_stock = 0 WHERE NOT product_id IN (101, 102, 103);

This query updates the inventory, setting all products as out of stock except for products with IDs 101, 102, and 103. It's like clearing out your store but keeping a few special items available.

NOT with DELETE Statement

We can also use NOT in DELETE statements. Be careful with this one - it's powerful!

DELETE FROM old_records WHERE NOT date_created > '2020-01-01';

This will delete all records created before 2020. It's like doing a big clean-up and keeping only the newer stuff.

NOT Operator Using a Client Program

Now, let's see how we can use the NOT operator in a client program. Here's a simple Python example using the MySQL Connector:

import mysql.connector

# Connect to the database
cnx = mysql.connector.connect(user='your_username', password='your_password',
host='127.0.0.1', database='your_database')
cursor = cnx.cursor()

# Execute a query with NOT
query = "SELECT * FROM students WHERE NOT grade = 'F'"
cursor.execute(query)

# Fetch and print the results
for (id, name, grade) in cursor:
print(f"Student {id}: {name} - Grade: {grade}")

# Close the connection
cursor.close()
cnx.close()

This script connects to a MySQL database, runs a query to find all students who didn't get an F, and prints out their information. It's like running a report card day, but only for the students who passed!

And there you have it, folks! You've just taken a whirlwind tour of the MySQL NOT operator. Remember, in the world of databases, sometimes it's just as important to know what you don't want as what you do want. The NOT operator is your trusty sidekick in these situations.

Keep practicing, stay curious, and before you know it, you'll be NOTing your way through databases like a pro! Happy querying!

運算子組合	示例	描述
NOT	WHERE NOT condition	否定一個條件
NOT IN	WHERE column NOT IN (value1, value2, ...)	排除指定的值
IS NOT NULL	WHERE column IS NOT NULL	找到非空的值
NOT LIKE	WHERE column NOT LIKE pattern	排除符合模式的值
NOT BETWEEN	WHERE column NOT BETWEEN value1 AND value2	排除在範圍內的值

Credits: Image by storyset