🐬MySQL

MySQL 101

Command Line

We can use the mysql utility for basic authentication and interaction with the MSQL database

Login Locally

mysql -u root -p

Remote Login

mysql -u root -h <IP> -P 3306 -p

Queries must end with ;

Create a Database

CREATE DATABASE users;

Show all Databases and Use A Databases

SHOW DATABASES;
USE users;

Create a Table

A data type defines what kind of value a column may hold. Can consist from numbers, strings, date, time.

CREATE TABLE logins (
    id INT,
    username VARCHAR(180),
    password VARCHAR(100),
    date_created DATETIME
    );

Show all Tables

SHOW TABLES;

Show Table Structure

With the DESCRIBE keyword we can see the table structure

DESCRIBE logins;

SQL Statements

INSERT

Used to add new records a table

INSERT INTO table_name VALUES (column1_value, column2_value, column3_value);

SELECT

Allows us get data from a tables

Retrieves the user table where their id = 1

SELECT * FROM table_name

SELECT name, password FROM users WHERE id=1;

DROP

Remove tables and databases from the server

DROP table table_name

ALTER

Change table name and fields

Add new Column

ALTER TABLE table_name ADD new_column_name INT;

Rename Column

ALTER TABLE table_name RENAME COLUMN old_column_name new_column_name;

UPDATE

Update table properties

UPDATE table_name SET column1=newvalue1, column2=newvalue2, WHERE <condition>;

Query Results

We can control the output of our results and sort them with the following keywords;

ORDER BY

SELECT * FROM table ORDER BY name;

It's also possible to change the order from ascending to descending

SELECT * FROM table ORDER BY name DESC;

LIMIT

Limits the number of items returned

SELECT * FROM table LIMIT number;

WHERE

Filte for specific data based on a condition

for example

WHERE name = rod

WHERE id < 5

SELECT * FROM table WHERE name = 'rod';

LIKE

Used to match a certain pattern

SELECT * FROM table WHERE name LIKE 'rod%';

The % acts as wildcard and will match all characters after it.

The _ symbol is used to match exactly one character.

SELECT * FROM table WHERE name LIKE '___';

SQL Operators

Allows us to use mutiple conditions at once

AND

Takes two condition and return true or false based on the outcome.

condition1 AND condition2;

SELECT * FROM table WHERE name = 'Bill' AND company = 'Microsoft'

OR

Takes two expressions and returns true when at least one is true

condition1 OR condition 2;

SELECT * FROM table WHERE name = 'Bon' OR last_name = 'Jovi';

NOT

Toggle between a boolean value

condition1 NOT 1;

SELECT * FROM table WHERE occupation NOT 'programmer'; 

Symbol Operators

AND, NOT and OR can be represented as &&, II and !

SELECT * FROM table WHERE user = 'Ron' && school = 'Hogwarts; 

SELECT * FROM table WHERE user != 'Tara'; 

Basic SQL queries

Union SQL query;

Retrieves a table containing a row with the user's information (where their id = 5) and all usernames and credit card numbers from accounts.

SELECT name, password FROM users WHERE id=5
UNION SELECT username, card_number FROM accounts;

If we have a query that gets an item based in the $id:

SELECT name, price FROM products WHERE id='$id';

We can change it to:

SELECT name, price FROM products WHERE id='' OR 'a'='a';

Since this will always be true, we should get back all the items in the products table!

We can also use this in a UNION:

SELECT name, price FROM products WHERE id='' UNION SELECT username, password FROM accounts WHERE 'a'='a';