MySQL学习笔记

Get data

SELECT prod_name FROM products;
SELECT prod_name, prod_price FROM products;
SELECT * FROM products;
SELECT DISTINCT vend_id FROM products;
SELECT prod_name FROM products LIMIT 5;
SELECT prod_name FROM products LIMIT 5, 5;
SELECT products.prod_name FROM products;
SELECT prod_name FROM products ORDER BY prod_name;
SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price, prod_name;
SELECT prod_name, prod_price FROM products
ORDER BY prod_price DESC;
# If you want to sort descending on mulitple columns, 
# be sure each column has its own DESC keyword.

Filting Data

SELECT prod_name, prod_price
FROM products
WHERE prod_price = 2.50;

SELECT prod_name, vend_id
FROM products
WHERE vend_id <> 1003;

SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;

SELECT prod_name
FROM products
WHERE prod_price IS NULL;

Advanced Data Filtering

SELECT prod_id, prod_price, prod_name
FROM products
WHERE vend_id = 1003 AND prod_price < 10;

SELECT prod_name, prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003;

SELECT prod_name, prod_price
FROM products
WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;
# AND ranks higher in the order of evaluation than OR.

SELECT prod_name, prod_price
FROM products
WHERE vend_id IN (1002, 1003)
ORDER BY prod_name;

SELECT prod_name, prod_price
FROM products
WHERE vend_id NOT IN (1002, 1003)
ORDER BY prod_name;

Wildcard Filtering

Try to not use wildcards, never overuse them.

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE 'jet%';

SELECT prod_id, prdo_name
FROM products
WHERE prod_name LIKE '%anvil%'

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '_ ton anvail';
# - matches just one character