SQL Basic Flashcards
SQL and MySQL
SQL Structured Query Language
MySQL Database management sysytem-DBMS, other are PostgreSQL, SQLite, Oracle, There are slight differences in the syntax. Implement SQL standard
changing/selecting database
Use Database_name
to check what db i am using
Select Database_Name()
To drop a database:
DROP DATABASE <database-name>;</database-name>
To use a database:
USE <database-name>;</database-name>
To list available databases:
show databases;
The general command for creating a database:
CREATE DATABASE <database_name>;</database_name>
CREATE DATABASE soap_store;
2 main data types
numeric and string
show tables;
show columns from Table1;
DESC Table1;
Insert a cat into a table
INSERT INTO cats (name, age)
VALUES (‘Blue Steele’, 5);
Primary key
means unique,
auto-increment
cat_id INT PRIMARY KEY
at the begging or
PRIMARY KEY (cat_id)
at the end
Update example
UPDATE cats SET breed=’Shorthair’ WHERE breed=’Tabby’;
using SET and where
What to use before Deleting/Updating
Select before UPDATE/ DELETE!
String functions
- concat
- substring
- replace
- reverse
- char_length
- upper&lower
using select
SELECT CONCAT(author_fname,’ ‘, author_lname) AS author_name FROM books;
SELECT SUBSTRING(‘Hello World’, 1, 4);
Refining selections
distinc- only unique values -> Select Distinct
SELECT DISTINCT author_lname FROM books;
Order by example
SELECT author_lname FROM books ORDER BY author_lname;
ASCENDING By Default
Limit example
SELECT title, released_year FROM books
ORDER BY released_year DESC LIMIT 5;
LIKE
Better searching
WHERE author_fname LIKE ‘%da%’
WILDCARDS
Aggregate functions
Count
Group By
Min/Max
Sum
Avg
Count example
SELECT COUNT(*) FROM books;
SELECT COUNT(author_fname) FROM books;
SELECT COUNT(DISTINCT author_fname) FROM books;
SELECT COUNT(*) FROM books
WHERE title LIKE ‘%the%’;
GROUP BY
GROUP BY summarizes or aggregates identical data into single rows
Data types- String data types
char- fixed in size length, for text of similar length
varchar- text of different sizes
nvarchar- 1 character takes 2 bytes, max length is 50
Data types- numeric
tinyint- from -127 to 128
smallint- from -32k to +32k
mediumint- from -8 milion to + 8 milion
int- from -2b to +2b
Data types- decimal
decimal- (total number of digits (maxium/ up to), digits after decimal)
float- not as precise as double
double- very precise, dobule as precise
Constraints
Not null
unique
Check
Alter table
Named constraints
CREATE TABLE partiers2 (
name VARCHAR(50),
age INT,
CONSTRAINT age_over_18 CHECK (age > 18)
);
Primary key, foreign key
Primary key- it is always unique, not null, to identify in a table
Foreign key- refernces to another table within another table
INNER JOIN
Select all records from A and B where the join condition is met
SELECT * FROM customers
JOIN orders
ON customers.id = orders.customer_id;
LEFT JOIN
Select everything from A, along with any matching records in B
RIGHT JOIN
Select everything from B, along with any matching records in A
On delete cascade
ON DELETE CASCADE at the end at the table so when i delete the data that are being referenced it gets deleted
View
a reusable query
CREATE VIEW
full_reviews
AS
SELECT * FROM …
Having
specify condition on groups, typically formed using Group By clause
GROUP BY title HAVING COUNT(rating) > 1;
Roll up
works with Group by, make a statistic in the last row-average, count…
Modes
-setting of current sql
– To View Modes:
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
– To Set Them:
SET GLOBAL sql_mode = ‘modes’;
SET SESSION sql_mode = ‘modes’;
Window Function
make a Grupy By function, but show the result next to every row,
it doesnt reduce the amout of rows
