database_notes.md

Basics

to get all possible unique values in the rows of table

SELECT DISTINCT column_name FROM Table name

Import a sql file

mysql -h yourhostname -u username -p databasename < yoursqlfile.sql

Create a user

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Create a database

Delete A Database

DROP database theDBName;

Add that user to DB

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

See your database user info

select * from mysql.user WHERE User = 'newuser' \G;

starting and accessing mysql server mysql.server start followed by the command mysql -uroot -proot

This shows you what databases you have access to.

SELECT DATABASE();

This creates a new database

CREATE DATABASE test_database;

This shows a list of all databases in the current MySQL server

SHOW DATABASES;

This connects us to the database we created.

USE test_database

To create tables

CREATE TABLE contacts (name VARCHAR (255), age INT, birthday DATETIME);

To change a table ALTER TABLE contacts ADD favorite_color;

to remove a column ALTER TABLE contacts DROP favorite_color;

---

###To Create to do database

USE to_do;

CREATE TABLE categories (id serial PRIMARY KEY, name varchar (255));

DROP DATABASE to_do_test;

from user charlesamoss

SQL notes

creates "actors", with a "name" row that accepts a string of 50 characters:

CREATE TABLE actors (name VARCHAR(50));

creates "movies", with "title" accepts a string of 200 characters and "year" that accepts a wole number:

CREATE TABLE movies (title VARCHAR(200), year INTEGER);

adds a row in the "movies" table for Avatar:

INSERT INTO movies VALUES ("Avatar", 2009);

All names (databases, tables, columns, etc) should be in lowercase.

• Table names should be plural; for example, tasks not task.
• Names with multiple words are separated by an underscore. For example: date_of_birth not dateofbirth.
• If you add in a foreign key to your table (i.e. the column list_id into the tasks table), the foreign key is a singularized version of the table it represents. For example, list_id not lists_id.

Advanced

check this out after we have covered many-to-many relationships

• If a join table doesn't have any meaning besides just joining two tables, use table_name1_table_name2, with the names in alphabetical order. Example: cuisines_restaurants would be a join table for the table cuisines and restaurants.
• If the table has meaning besides just joining the table, use a name that describes the relationship. Example: visits would be a good name for a join table that joins together a table called people and a table called places.