SELECT * from my_contacts;

Better select - Use the WHERE clause (to provide conditions for search)

SELECT * FROM my_contacts WHERE first_name = 'Anne';

Note that text strings need single quotation marks.

An asterisk (*) indicates that RDBMS returns all columns in the table.

Practice:

CREATE DATABASE drinks;

USE drinks;

CREATE TABLE easy_drinks(
 drink_name VARCHAR(30),
 main VARCHAR(30),
 amount1 DEC(3,1),
 second VARCHAR(30),
 amount2 DEC(4,2),
 directions VARCHAR(100)
);

Note the use of DEC in amount1 DEC(3,1)!!

Query Case Analysis

SELECT * FROM easy_drinks WHERE main = soda;
Errors are reported. VARCHAR variables need to be raised in single quotes.

WHERE main = "orange juice";   
Correct (double quotation marks)
But don't use double quotation marks. In programming languages "" means that it's an SQL statement.

WHERE amount2 < '1'; 
It works well, although the DEC variable does not need quotation marks

Single quotation marks are special characters

When inserting VARCHAR, CHAR, and BLOB data with single quotes, you must declare that the single quotes in the data are part of the text. Add backslash - escape.

INSERT INTO my_contacts (location) VALUES('lzp\'s house');

INSERT contains single quoted data

1 Use Backslash Transfer\

'lzp\'s house'

2 or use another single quotation mark

'lzp''s house'

WHERE clause also needs to be transferred

SELECT * FROM my_contacts WHERE location = 'lzp\'s house';

Comparison operator

= It's an equal sign.
<> Unequal sign
< less than > greater than
<= >=

SELECT drink_name FROM drink_info WHERE const >= 3.5 AND calories < 50;

Applying comparison operators to text data

Character table sequential query

SELECT drink_name FROM drink_info WHERE drink_name >= 'L' AND drink_name < 'M';

AND OR

AND satisfies the condition at the same time
OR satisfies one condition.

SELECT drink_name FROM easy_drinks WHERE main = 'cherry juice' OR second = 'cherry juice';

Note: Don't let a lot of NULL exist in the table, because you can't select NULL directly from the table.

NULL

Find a classification with a value of NULL

SELECT drink_name FROM drink_info WHERE calories IS NULL;

Note: IS NULL is the key word!!

Indirect acquisition of NULL

Through the conditions that can not be satisfied, the result is NULL.

LIKE - Finds part of the text string and returns all qualified rows

Example: Find all values that end with CA

SELECT * FROM my_contacts WHERE location LIKE '%CA';

wildcard

% - A stand-in for any number of unknown characters

_ A stand-in for an unknown character

SELECT first_name FROM my_contacts WHERE first_name LIKE '_im';

BETWEEN Scope

SELECT drink_name FROM drink_info WHERE calories BETWEEN 30 AND 60;
 [30,60]

Equivalent to

WHERE calories >= 30 AND calories <= 60;

trap

WHERE calories BETWEEN 60 AND 30; there is no value in the middle, so no result can be found.
Small number in front

IN - Returns the row or column if any value within the range is satisfied

SELECT date_name FROM black_book WHERE rating IN {'innovative', 'fabulous');

NOT IN means not in the collection.

NOT

NOT can be used with BETWEEN or LIKE.
The point is that NOT must be immediately behind WHERE (or AND/OR)

SELECT drink_name FROM drink_info WHERE NOT carbs BETWEEN 3 AND 5;

SELECT date_name from black_book WHERE NOT date_name LIKE 'A%' AND NOT date_name LIKE 'B%';

NOT IN is an exception
But it can also be expressed as

SELECT * FROM easy_drinks WHERE NOT main IN ('soda','iced tea');

NOT and NULL

SELECT * FROM easy_drinks WHERE NOT main IS NULL;
//or
SELECT * FROM easy_drinks WHERE main IS NOT NULL;