SQL Notes
Basics
-
Create and use the database (MySQL as an example)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
CREATE DATABASE gregs_list; USE gregs_list; CREATE TABLE doughnut_list ( doughnut_id INT NOT NULL AUTO_INCREMENT, doughnut_name VARCHAR(10) DEFAULT NULL, doughnut_type VARCHAR(8) DEFAULT NULL, PRIMARY KEY (doughnut_id) ); -- Variable Char, up to 10 and 8 chars. DESC doughnut_list; -- Describe the table. -- DROP TABLE doughnut_list; -- Delete the table. INSERT INTO doughnut_list (doughnut_name, doughnut_type) VALUES ('hello', 'world'); -- The order doesn't matter, but they must match SELECT * FROM doughnut_list; -- Show the table. * means all columns. SELECT doughnut_name FROM doughnut_list; -- Just show the name column SELECT * FROM doughnut_list WHERE doughnut_name = 'hello' AND doughnut_type = 'world'; -- Show the table selectively. = instead of ==
-
For
INSERT
, there are three variations from above:- Changing the order of the columns, as long as the values match them in the same order.
- Omitting column names altogether, as long as we add all the values in the same order.
- Leaving some columns out, as long as the values we’re adding match the columns.
-
\'
or''
to insert data with the single quote.
SELECT
-
<>
means not equal. -
Use
IS NULL
to select null ones:WHERE doughnut_name IS NULL;
. -
WHERE location LIKE '%CA';
selects all the values that end with CA.%
replaces anything while_
replaces one letter. -
WHERE value BETWEEN 1 AND 10;
. -
WHERE language IN ('Java', 'Python', 'C', 'C++');
is equivalent to usingOR
. Plus, we haveWHERE language NOT IN ('Java', 'Python', 'C', 'C++');
. -
NOT
goes right afterWHERE
when used withBETWEEN
andLIKE
:WHERE NOT location LIKE '%CA';
WHERE NOT value BETWEEN 1 AND 10;
. -
ORDER BY foo DESC, bar;
can be added to the end (DESC means descending.)
DELETE
-
DELETE FROM doughnut_list WHERE doughnut_type = 'bar';
. -
DELETE FROM doughnut_list;
deletes the entire table. -
Use
SELECT
first to make sure what we’re deleting is correct.
UPDATE
-
UPDATE doughnut_list SET doughnut_type = 'barbar' WHERE doughnut_type = 'bar';
. -
Without
WHERE
, every possible one will be updated. -
Use comma to seperate different columns after
SET
. -
It can do basic math:
SET cost = cost + 1
.
ALTER
-
ALTER TABLE doughnut_list ADD COLUMN doughnut_id INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (doughnut_id);
-
FIRST
,SECOND
,LAST
,BEFORE foo
,AFTER foo
can be used. -
ALTER TABLE doughnut_list RENAME TO foo;
. -
ALTER TABLE doughnut_list CHANGE COlUMN foo bar INT;
changes foo to bar with int. -
ALTER TABLE doughnut_list MODIFY COLUMN foo VARCHAR(10);
changes the data type. We can also doFIRST
orLAST
. -
ALTER TABLE doughnut_list DROP COLUMN foo;
deletes a column.