MYSQL cheat sheet.md

SQL databases come in different "flavors". This cheatsheet references MYSQL commands.

A database is composed of tables that contain rows of data.

A table acts as a template for real-world data.

#Install on Ubuntu

sudo apt-get install mysql-server mysql-client libmysqlclient-dev

MYSQL terminal

sudo mysql

SQL lite terminal

sqlite3

NOTE: In SQL commands are called "Clauses". NOTE: all statements end with a semi colon.

Run command from text file

The text_file_directory should not be in quotes. Using quotes with give an error!

source text_file_directory   

SHOW databases

SHOW databases;

CREATE

CREATE DATABASE database_name;

USE

USE database_name;

#Check which database is active

SELECT database() 

DROP

DROP database database-name;

Show All Tables

SHOW tables

SHOW a tables default rows

DESCRIBE name-of-table

SELECT

SELECT * FROM name_of_table;

CREATE

CREATE TABLE table_name(
    column_1 data_type,
    column_2 data_type,
    column_2 data_type
);


CREATE TABLE user(

   id INTEGER,
   name TEXT,
   age INTEGER
);

INSERT

Inserts a new item into a table

INSERT INTO users (id, name, age) 
VALUES (1, 'Joe Whatever', 55);

Get Data with specific value

SELECT * FROM Users
WHERE ID=1;

ALTER

The ALTER TABLE statement adds a new column to a prexisting table.

 ALTER TABLE users
 ADD COLUMN profession TEXT;

UPDATE

UPDATE is a clause that edits a row in the table.

UPDATE users 
SET profession = "software engineer"
WHERE id = 4; 

DELETE Row

Deletes one or more rows from a table.

DELETE FROM users
WHERE profession IS NULL;
SELECT * FROM users

Delete table

DROP TABLE TABLENAME

CONSTRAINTS

Constraints restrict the data that can be used in columns. https://www.w3resource.com/mysql/creating-table-advance/constraint.php

CREATE TABLE user (
    ID int NOT NULL AUTO_INCREMENT,
    firstName varchar(255) NOT NULL,
    lastname varchar(255),
    age int,
    PRIMARY KEY (ID)
);

Creating Relationships Between Tables with Primary and Foreign Keys

Foreign and Primary keys are used to create associations between tables. For example, imagine 2 tables - user and todo. We want to create an association where a user can have many todos associated with them. To create the associations a user table will have a primary key and the value assigned to it(usually an integer) will also be reference in each associated todo as a foreign key.

In this example the user table is the parent and the todo table is a child

create table authors (
  id int auto_increment not null primary key,
  first_name varchar(20),
  last_name varchar(20)
);

create table books (
  id int auto_increment not null primary key,
  title varchar(32)
);

create table book_authors (
  id int auto_increment not null primary key,
  book_id int not null,
  author_id int not null,
  foreign key (author_id) references authors(id),
  foreign key (book_id) references books(id)
);

insert into authors (first_name, last_name) values ('Alan', 'Watts');
insert into authors (first_name, last_name) values ('Shunryu', 'Suzuki');
insert into authors (first_name, last_name) values ('Robert', 'Aitken');
insert into authors (first_name, last_name) values ('Carlos', 'Castaneda');


insert into books (title) values ('Zen Master Raven');
insert into books (title) values ('Zen Mind, Beginner\'s Mind');
insert into books (title) values ('The Way of Zen');
insert into books (title) values ('The Teachings of Don Juan');


insert into book_authors (book_id, author_id) values (1, 3);
insert into book_authors (book_id, author_id) values (2, 2);
insert into book_authors (book_id, author_id) values (3, 1);
insert into book_authors (book_id, author_id) values (4, 4);






SELECT * FROM book_authors
WHERE last_name='Watts';


SELECT * FROM book_authors
WHERE author_id=1;