Learning MySQL
My experience with SQL was horrible. I hated queries and databases, but I needed to learn it fast due to my job. I guess this is what I needed for me to learn it. I have a friend that enjoyed reading Head First Labs text books and he recommended me checking it out. With luck, Head First Labs had “Head First SQL“. This blog is basically notes I have taken during my reading of the textbook.
CREATE DATABASE gregs_list; //creates a database names gregs_list
USE gregs_list; //activate database named “gregs_list”
CREATE TABLE my_contacts
(
last_name VARCHAR(30),
first_name VARCHAR(20),
email VARCHAR(50),
gender CHAR(1),
birthday DATE,
profession VARCHAR(50),
location VARCHAR(50),
status VARCHAR(20),
interests VARCHAR(100),
seeking VARCHAR(100)
);
//to view your table
DESC my_contacts; or DESCRIBE my_contacts;
//deleting an empty table
DROP TABLE my_contacts;
//Insert into table
INSERT INTO my_contacts
(last_name, first_name, email, gender, birthday, profession, location, status, interests, seeking)
VALUES
(‘Anderson’, ‘Jillian’, ‘jill_anderson@breakneckpizza.com’, ‘F’, ‘1980-09-05’, ‘Technical Writer’, ‘Palo Alto, CA’, ‘Single’, ‘Kayaking, Reptiles’, ‘Relationship, Friends’);
//Insert into table with missing data
INSERT INTO my_contacts
(first_name, email, profession, location)
VALUES
(‘Pat’, ‘patpost@breakneckpizza.com’, ‘Postal Worker’, ‘Princeton, NJ’);
//Peek @ your table and view everything
SELECT * FROM my_contacts;
\\removing NULL values
CREATE TABLE my_contacts;
(
last_name VARCHAR(30) NOT NULL,
first_name VARCHAR(30) NOT NULL
);
\\filling in the NULL with DEFAULT
CREATE TABLE doughnut_list
(
doughnut_name VARCHAR(10) NOT NULL,
doughnut_type VARCHAR(6) NOT NULL,
doughnut_cost DEC(3,2) NOT NULL DEFAULT 1.00
);
\\finding specific entry within the table
SELECT * FROM my_contacts WHERE first_name = ‘Jillian’;
\\ Query data types with the WHERE command
CHAR or CHARACTER = single quotes
INT or INTEGER = no quotes
BLOB = single quotes
DATE = single quotes
DATETIME, TIME or TIMESTAMP = quotes
DEC = no quotes
\\single quotes are special characters (\)
INSERT INTO my_contacts
VALUES
(‘Funyon’, ‘Steve’, ‘steve@onionflavoredrings.com’, ‘m’, ‘1970-04-01’, ‘Punk’, ‘Grover\’s Mill, NJ’, ‘Single’, ‘smashing the state’, ‘compatriots, guitar players’);
or
(‘Funyon’, ‘Steve’, ‘steve@onionflavoredrings.com’, ‘m’, ‘1970-04-01’, ‘Punk’, ‘Grover”s Mill, NJ’, ‘Single’, ‘smashing the state’, ‘compatriots, guitar players’);
\\SELECT specific columns to limit results
SELECT drink_name, main, second FROM easy_drinks WHERE main = ‘soda’;
\\ Combining your queries
SELECT location FROM doughnut_ratings WHERE type = ‘plain glazed’ AND rating = 10;
\\ Finding numeric values
SELECT drink_name FROM easy_drinks WHERE main = ‘soda’ AND amount1 = 1.5;
\\ Query with “greater than” sign
SELECT drink_name FROM easy_drinks WHERE main = ‘soda’ AND amount1 > 1;
\\ combine two queries using OR
SELECT drink_name FROM easy_drinks WHERE main = ‘cherry juice’ OR second = ‘cherry juice’;
SELECT drink_name FROM easy_drinks WHERE main = ‘orange juice’ OR main = ‘apple juice’;
note:
* When you want ALL of your conditions to be true, use AND.
* When you want ANY of your conditions to be true, use OR.
\\ use IS NULL to find NULLs
SELECT drink_name FROM drink_info WHERE calories IS NULL;
\\ Saving time with a single keyword: LIKE
SELECT * FROM my_contacts WHERE location LIKE ‘%CA’;
% = tell the software you’re looking for all values in the location column that end with CA.
SELECT first_name FROM my_contacts WHERE first_name LIKE ‘_im’;
_ = Results in names with just one character before the “im”, example: Jim, Kim, and Tim
\\ Selecting ranges using AND and comparison operators
SELECT drink_name FROM drink_info WHERE calories >=30 AND calories
or
SELECT drink_name FROM drink_info WHERE calories BETWEEN 30 AND 60 [note: smaller number must always be first for the BETWEEN]
\\ Use IN with a set of values in parentheses
SELECT date_name FROM black_book WHERE rating IN (‘innovative’,’fabulous’,’delightful’,’pretty good’);
\\ NOT gives you the opposite results of IN
SELECT date_name FROM black_book WHERE rating NOT IN (‘innovative’,’fabulous’,’delightful’,’pretty good’);
\\ More NOT
You can use NOT with BETWEEN and LIKE. Most important thing to keep in mind is that NOT goes right after WHERE.
Examples:
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%’;
\\ Using new DELETE statement
DELETE FROM clown_info WHERE activities = ‘dancing’;
\\ INSERT – DELETE two step
Old record:
INSERT INTO clown_info VALUES (‘Clarabelle’, ‘Belmont Senior Center’, ‘F, pink hair, huge flower, blue dress’, ‘yelling, dancing’);
Insert new information:
INSERT INTO clown_info VALUES (‘Clarabelle’, ‘Belmont Senior Center’, ‘F, pink hair, huge flower, blue dress’, ‘dancing’);
Delete the old record:
DELETE FROM clown_info WHERE activities = ‘yelling’ AND name = “Clarabelle’;
\\ SELECT – INSERT – DELETE
SELECT FROM clown_info WHERE activities = ‘dancing’;
INSERT INTO clown_info VALUES (‘Zippo’, ‘Millstong Mall’, ‘F, orange suit, baggy pants’, ‘dancing, singing’);
DELETE FROM clown_info WHERE activities = ‘dancing’;
\\ Change your data with UPDATE
UPDATE doughnut_ratings SET type = ‘glazed’ WHERE type = ‘plain glazed’;
\\ SHOW CREATE TABLE – will return a CREATE TABLE statement that can exactly recreate our table, minus any data in it.
\\ The CREATE TABLE with a PRIMARY KEY
CREATE TABLE my_contacts
(
contact_id INT NOT NULL,
last_name VARCHAR(30),
first_name VARCHAR(20),
email VARCHAR(50),
gender CHAR(1),
birthday DATE,
profession VARCHAR(50),
location VARCHAR(50),
status VARCHAR(20),
interests VARCHAR(100),
seeking VARCHAR(100),
PRIMARY KEY (contact_id)
);
\\ Other SHOW commands
SHOW COLUMNS FROM “tablename”; – this command will display wall the columns in your table and their data type along with any other column-specific details.
SHOW CREATE DATABASE “databasename”; – just like the SHOW CREATE table, you’ll get the command that would exactly recreate your database.
SHOW INDEX FROM “tablename”; – This command will display any columns that are indexed and what type of index they have. So far, the only index we’ve looked at are primary keys, but this command will become more useful as you learn more.
SHOW WARNINGS; – if you get a message on your console that your SQL command has caused warnings, type this to see the actual warnings.
\\ 1, 2, 3… auto incrementally
CREATE TABLE my_contacts
(
contact_id INT NOT NULL AUTO_INCREMENT,
last_name VARCHAR(30),
first_name VARCHAR(20),
email VARCHAR(50),
gender CHAR(1),
birthday DATE,
profession VARCHAR(50),
location VARCHAR(50),
status VARCHAR(20),
interests VARCHAR(100),
seeking VARCHAR(100),
PRIMARY KEY (contact_id)
);
\\ Adding a PRIMARY KEY to an existing table
ALTER TABLE my_contacts ADD COLUMN contact_id INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (contact_id);
\\ Adding a column after another column
ALTER TABLE my_contacts ADD COLUMN phone VARCHAR(10) AFTER first_name;
\\ Adding a column
ALTER TABLE my_contacts ADD COLUMN age VARCHAR(10);
\\ Renaming the table
ALTER TABLE projekts RENAME TO project_list;
\\ change a column name and set it to AUTO_INCREMENT and also make it a primary key [number is the old column name and the new name is proj_id]
ALTER TABLE project_list CHANGE COLUMN number proj_id INT NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (‘proj_id’);
\\ change two columns with one SQL statement
ALTER TABLE project_list CHANGE COLUMN descriptionofproj proj_desc VARCHAR(100), CHANGE COLUMN contractoronjob con_name VARCHAR(30);
\\ Change only the data type of a column
ALTER TABLE project_list MODIFY COLUMN proj_desc VARCHAR(120);
\\ Dropping/removing a column
ALTER TABLE project_table DROP COLUMN start_date;
\\ Remove the primary key from a table
ALTER TABLE project_table DROP PRIMARY KEY;
\\ UPDATE with a CASE expression
UPDATE movie_table SET category =
CASE
WHEN drama = ‘T’ THEN ‘drama’
WHEN comedy = ‘T’ THEN ‘comedy’
WHEN action = ‘T’ THEN ‘action’
WHEN gore = ‘T’ THEN ‘horror’
WHEN scifi = ‘T’ THEN ‘scifi’
WHEN for_kids = ‘T’ THEN ‘family’
WHEN cartoon = ‘T’ THEN ‘family’
ELSE ‘misc’
END;
\\ ORDER using the ‘Title’ column in the table:
SELECT title, category FROM movie_table WHERE category = ‘family’ ORDER BY title;
\\ SUM can add them for us
SELECT SUM(sales) FROM cookie_sales WHERE first_name = ‘Nicole’; “sales – is the column with the numbers to add up”
\\ SUM all of them at once with GROUP BY
SELECT first_name, SUM(sales) FROM cookie_sakes GROUP BY first_name ORDER BY SUM(sales) DESC;
\\ AVG with GROUP BY
SELECT first_name, AVG(sales) FROM cookie_sales GROUP BY first_name; “AVG adds all of the values in a group and then divides by the total number of values”
\\ MAX (returns the single largest sale value for each girl)
SELECT first_name, MAX(sales) FROM cookie_sales GROUP BY first_name;
\\ MIN (returns the single lowest sale value for each girl)
SELECT first_name, MIN(sales) FROM cookie_sales GROUP BY first_name;
\\ COUNT (returns the number of rows in the sale_date column. if the value is NULL it isn’t counted)
SELECT COUNT(sale_date) FROM cookie_sales;
\\ COUNT on each user
SELECT first_name, COUNT(sale_date) FROM cookie_sales GROUP BY first_name;
\\ DISTINCT getting the count and removing the duplicates
SELECT DISTINCT sale_date FROM cookie_sales ORDER BY sale_date;
using COUNT:
SELECT COUNT(DISTINCT sale_date) FROM cookie_sales;
Leave a Reply