reference/sqlref.txt

docker search mysql
docker pull mysql
docker images
docker run -d --name=msqdb -e MYSQL_ROOT_PASSWORD='hjkl;' -v /storage/msqdb/mysql-datadir:/var/lib/mysql mysql
docker ps -a
docker start msqdb
docker ps
docker exec -it msqdb bash

cat /etc/os-release

service --status-all

apt-get update
apt-get install openssh-server openssh-client
service ssh status
service ssh start

useradd -m raman
set password for user raman:
passwd raman
[hjkl;']

apt-get install sudo vim

vi /etc/passwd
raman:x:1000:1000::/home/raman:/bin/sh
raman:x:1000:1000::/home/raman:/bin/bash

sysvinit - /etc/init.d/mysql
systemd  - /lib/systemd/sytem/mysql.service

Relational Database Management System - RDBMS

For help with using MySQL, please visit the MySQL Forums, where you can discuss your
issues with other MySQL users.

mysql -u root -p
mysql -u user -p
mysql -u user -p dbname

select version(), current_date;

# Creating a Database and Tables
CREATE DATABASE bookstore;
SHOW DATABASES;
USE bookstore
CREATE TABLE books (book_id INT,
	title VARCHAR(50),
	author VARCHAR(50) );

DESCRIBE books;

# Analyzing and Manipulating Data
SELECT COUNT(*)
	FROM books
	JOIN authors USING(author_id)
	WHERE author_list = 'Tolstoy';

# sqlite - use SQL without a database server

# Relational Database Concepts
A database server can contain many databases
Databases are collections of tables
Tables are two-dimensional with rows (observations) and columns (variables)
Limited Mathematical and summary operations available
Very good at combining information from several tables

SHOW DATABASES;
SHOW TABLES IN database;
SHOW COLUMNS IN table;
DESCRIBE table; # shows the columns and their types

# Variable Types
SQL supports a very large number of different formats for internal storage
of information.
Numeric - INTEGER, SMALLINT, BIGINT
	  NUMERIC(w,d), DECIMAL(w,d) - numbers with width w and d decimal places


show databases;
use <databasename>;
show tables in <databasename>;
show columns in <tablename>; is equvalent to "describe <tablename>;"
show rows in <tablename>; # results in error

create table childrens(id char(6),
			race smallint,
			age decimal(6,3),
			height decimal(7,3),
			weight decimal(7,3),
			sex smallint);

insert into childrens values(100011, 2, 10.346, 148.5, 38.95, 1);

We can automate the process using the LOAD DATA command:
load data infile 'childrens.tab'
	into table childrens
	fields terminated by '\t'; 
This will read an entire tab-separated file into the database in one command

select * from childrens; # to see all of the data in table childrens


# Created Database: library and Table: booklist
Reference: MYSQL_in_a_Nutshell.pdf

create database library;
use library;

create table booklist(
subject varchar(50),
title varchar(75),
author varchar(50));

describe booklist;  # similar to show columns in booklist;

alter table booklist
change column subject book_id int auto_increment primary key,
change column title subject varchar(20),
change column author title varchar(75),
add column author varchar(50),
add column edition varchar(20);

show databases;
show tables;

insert into booklist
(subject, title, author, edition)
values('Linux', 'Introduction to Linux', 'Machtelt Garrels', '1.27 Edition');

select * from booklist;

select book_id, title, author, edition
from booklist
where subject = 'Linux';

select count(*)
from booklist
where subject = 'Linux';

update booklist
set edition = '1 Edition'
where book_id = '2';

delete from booklist
where author = ' Michael Stutz';

# database schema

show columns from clients like 'c%i%';

alter table clients
change column city client_city varchar(100) character set 'latin2',
modify column client_state char(4) after client_city;


# add column in a table
alter table <table_name> add column <column_name> <data_type>;

# set same value to all rows in a column
update <tabel_name> set <column_name> = 'value' where 1 = 1;

# rename a column
alter table <table_name> change <old_column> <new_column> <data_type>;

# drop a column from a table
alter table <table_name> drop column <column_name>;

# list first 10 records from a table
select * from <tablename> limit 10; # select rows 1 to 10
select * from <tablename> limit 0, 10;  # select rows 01 to 10
select * from <tablename> limit 10, 10; # select rows 11 to 20
select * from <tablename> limit 10 offset 10; # select rows 11 to 20

# execute sql script in MySQL
mysql> source /home/raman/Scripts/datadef.sql