reference/mysql.txt

# show engines
show engines;

# create database
create database database_name;

# use database
use database_name;

# create table
create table table_name(fname1 datatype, fname2 datatype) engine=engine_name;

# unsigned integer type
create table table_name(fname1 datatype, fname2 datatype unsigned) engine=engine_name;

# insert data into table
insert into table_name(value1, value2)

# data types: mysql supports Numeric, String, Date & time

# integer
data type	bytes	minimum value			maximum value
tinyint		1		-128					127
smallint	2		-32768					32767
mediumint	3		-8388608				8388607
integer		4		-2147483648				2147483647
bigint		8		-9223372036854775808	9223372036854775807

# float - single precision floating poing number (4 bytes)
# double - double precision floating point number(8 bytes)

# decimal
in decimal[M, D] the M is the maximum number of digits, the precision. The D is the 
number of digits to the right of the decimal point. It is the scale. If you have a
column with decimal(3, 1), you can insert numbers with maximum of three digits: two
before and one after the decimal point.

create table Numbers(fname1 tinyint, fname2 float, fname3 decimal(3,2));
insert into Numbers values (1, 1.1, 1.1), (2, 1.1, 1.1), (3, 1.1, 1.1); 
select * from Numbers;
select sum(fname2), sum(fname3) from Numbers;

# Date & Time Values
mysql has data types for storing dates and times: date, time, datetime, year, and timestamp

date(YYYY-MM-DD) - 1000-01-01 to 9999-12-31
select curdate();
select date('2017-01-31 12:01:00');
select adddate('2017-01-20', 8);
create table <table_name> (Id tinyint, Date_of_Admission date);

# time
select curtime(); // display current time






# create table Persons
create table Persons(PID smallint unsigned auto_increment primary key, Last_Name varchar(15) not null, First_Name varchar(15) not null, Age tinyint unsigned not null);
describe Persons;
insert into Persons (Last_Name, First_Name, Age) values ('Hansen', 'Ola', 20), ('Svendson', 'Ola', 23), ('Pettersen', 'Kari', 20);
select * from Persons;

# create table Orders
create table Orders(OID smallint unsigned auto_increment primary key, Order_Number integer not null, Person_ID smallint unsigned not null references Persons(PID));
describe Orders;
insert into Orders (Order_Number, Person_ID) values (77895, 3), (44678, 3), (22456, 2), (24562, 1);
select * from Orders;

# in
select * from Orders where Order_Number in (22456, 24562);

# like
select * from Orders where Order_Number like '2%';
select * from Orders where Order_Number like '_____'; // five characters

# between
select * from Orders where Order_Number between 30000 and 50000;

# count
select count(column_name) from table_name where condition;
select count(Order_Number) from Orders;

# average
select avg(column_name) from table_name where condition;
select avg(Order_Number) from Orders;

# sum
select sum(column_name) from table_name where condition;
select sum(Order_Number) from Orders;

# maximum
select max(column_name) from table_name where condition;
select max(Order_Number) from Orders;

# minimize
select min(column_name) from table_name where condition;
select min(Order_Number) from Orders;

# order by
select * from table_name order by column_name asc; // by default ascending
select * from table_name order by column_name desc;
select * from Orders order by Order_Number;





# truncating table with foreign key
set foreign_key_checks = 0;
truncate table table_name;
set foreign_key_checks = 1;

# add user in mysql
create user 'username'@'localhost' identified by "password";
grant all privileges on *.* to 'username'@'localhost';
flush privileges;

# 
mysql> load data infile '/home/raman/Downloads/iris.csv'
	 into table Iris 
	 fields terminated by ',' 
	 enclosed by '"'
	 lines terminated by '\n' 
	 ignore 1 rows;

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

mysql> show variable like 'secure_file_priv';

cd /etc/mysql/
sudo vi my.cnf