Quick intro to MySQL

Installation

sudo apt-get update
sudo apt-get upgrade
sudo apt-get install mysql-server
sudo mysql_secure_installation
sudo mysql_install_db

Adding databases

Login as root

$ mysql -u root -p
mysql> CREATE DATABASE TEST;

Adding user

mysql> GRANT ALL ON TEST.* TO lion@localhost IDENTIFIED BY ‘temppass1’;
mysql> FLUSH PRIVILEGES;

Display specific columns from a table

mysql> SELECT Continent FROM Country;

+---------------+
| Continent |
+---------------+
| North America |
| Asia          |
| Africa        |
| North America |
| Europe        |

Display specific columns from a table but make rows unique

mysql> SELECT DISTINCT Continent FROM Country;

Display COLUMN names

How would I know which columns to ask for if the list is big and scrolls too fast and I can’t see the column names. Every column is first defined in SQL, then and only then data is added to them.
mysql> SHOW COLUMNS FROM Country;

Select specific number of rows

1. Select first 10 rows: LIMIT <no of rows>

SELECT NAME FROM country limit 10 ;

2. Select from row 4-13 : LIMIT <line no> <no of rows>

SELECT NAME FROM country limit 3,10 ;

Table data command list

Open table

DATABASES are like Excel files
Tables are like Sheets in each file

mysql> SHOW DATABASES;
mysql> USE JOURNAL;
mysql> SHOW TABLES;
mysql> SELECT * FROM FITNESS;

Create a table

mysql> CREATE TABLE DAILY_JOURNAL (Date DATE NOT NULL, 
Entry VARCHAR(200), Feeling VARCHAR(20), needsimprovement VARCHAR(40) ) ;

Add primary key

mysql> ALTER TABLE DAILY_JOURNAL ADD PRIMARY KEY (Date);

Delete data

mysql> DELETE FROM DAILY_JOURNAL WHERE needsimprovement = "goal setting";

Insert data

mysql> INSERT INTO DAILY_JOURNAL (Date, Entry, needsimprovement) VALUES("2016-12-14","1. ordered washing machine", "goal setting");

Add a new column

mysql> ALTER TABLE DAILY_JOURNAL ADD Learnt VARCHAR(40);

Rename column

Changing column name, renaming a column, alter column name

mysql> ALTER TABLE DAILY_JOURNAL 
CHANGE gratefulfor GratefulFor VARCHAR(40);

Giving just a new column name is not enough. The variable type has to be defined again.

Edit row data

mysql> UPDATE DAILY_JOURNAL SET Learnt='1. getopts 2. REGEX 3. MYSQL' 
WHERE Date='2016-12-15';