https://www.javatpoint.com/mysql-tutorial
https://www.w3schools.com/mySQl/func_mysql_time_format.asp
mysql> show databases;
mysql> use retail_db;
mysql> show tables like '%pet%';
CREATE TABLE pet
(
name VARCHAR(20),
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth DATE,
death DATE,
id int(20)
);
[haritaraka12078@cxln4 ~]$ cat pet1data.txt
duffball,Diane,hamster,f,1999-03-30,2002-03-30,6\N
hari,manyam,hamster,m,1992-03-30,2004-03-30,2\N
taraka,don,hamster,m,1994-03-30,2007-03-30,3\N
prabhu,teja,hamster,m,1996-03-30,2008-03-30,4\N
manyam,dia,hamster,m,1998-03-30,2003-03-30,5\N
LOAD DATA LOCAL INFILE
'/home/haritaraka12078/pet1data.txt' INTO TABLE pet1
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS (id, first_name, last_name, email, transactions, @account_creation)
SET account_creation = STR_TO_DATE(@account_creation, '%m/%d/%y');
mysql> LOAD DATA LOCAL INFILE
-> '/home/haritaraka12078/pet1data.txt' INTO TABLE pet
-> FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' ;
mysql> insert into pet values('hari','manyam','hamster','m','1992-03-30','2004-03-30','2');
Query OK, 1 row affected (0.02 sec)
mysql> select * from pet;
mysql> select count(8) from pet; -->faster because it will count only the first column
mysql> select count(*) from pet; -->will use the whole table to get to the same result
mysql> select * from pet;
mysql> select name,owner from pet;
mysql> select distinct name from pet;
mysql> select count(distinct name) from pet;
mysql> create table petnew as select * from pet where 1=1; --- > Full Table Create
mysql> create table pet3 as select * from pet where 1=0; --- > Only Table Structure Create
select
birth, CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age,
length(name) as length,
rpad(name,15,'*') as rpad,
lpad(name,15,'#') as lpad
from pet;
select
length(name),
ltrim(name) as ltrim,
length(ltrim(name)) as len_ltrim,
rtrim(name) as rtrim,
length(rtrim(name)) as len_rtrim,
length(rtrim(ltrim(name))) as len_rtrim_ltrim
from pet;
select
replace(rtrim(ltrim(name)),'h','x') as replace_str,
replace(replace(rtrim(ltrim(name)),'h','x'),'i','y') as mult_replace_str,
replace(name,'l','') as repl,length(replace(name,'l','')) as leng_repl, length(name)-length(replace(name,'l','')) as repeat_string
from pet;
select
repeat(name,2) as repeat_string ,
reverse(name) as rev_String,
concat(owner,'*****',name) as concat_string
from pet;
select
birth,
date_format(birth, '%d-%m-%y') as new_date1,
date_format(birth, '%d%m%y') as new_date2,
DATE_FORMAT(birth, "%M %d %Y") as new_date4,
DATE_FORMAT(birth, "%W %M %e %Y") as new_date5,
ADDDATE(birth, INTERVAL 10 DAY) as add_date,
date_sub(birth, INTERVAL 10 DAY) as sub_date
from pet;
select birth,
day(birth) as day_birth,
dayname(birth) as day_name,
birth+ 1 as new_date3,
CURRENT_DATE() + 1 as cur_date,
CURRENT_TIME() + 1 as cur_time,
CURRENT_TIMESTAMP() + 1 as CUR_TIMESTAMP,
datediff(birth, death) as date_diff
from pet;
SELECT
name,
SUBSTR(name, 5, 3) AS ExtractString ,
substring(name, 5, 3) AS ExtractString1,
SUBSTRING_INDEX("www.w3schools.com", ".", 1) as sub_str1,
SUBSTRING_INDEX("www.w3schools.com", ".", 2) as sub_str2,
SUBSTRING_INDEX("www.w3schools.com", ".", 3) as sub_str3,
SUBSTRING_INDEX("www.w3schools.com", ".", -1) as sub_str5,
SUBSTRING_INDEX("www.w3schools.com", ".", -2) as sub_str6,
SUBSTRING_INDEX(birth, "-", 2) as sub_str4,
instr(birth, "-") as instr1
from pet;
select mail,
instr(mail,'@') as instr1,
instr(mail,'@')+1 as instr2,
substr(mail,instr(mail,'@')+1) as substr1,
substring_index(substr(mail,instr(mail,'@')+1),'.',1) as domain
FROM pet;
select mail,
instr(mail,'@') as instr1,
instr(mail,'@')+1 as instr2,
substr(mail,instr(mail,'@')+1) as substr1,
substring_index(substr(mail,instr(mail,'@')+1),'.',1) as domain,
count(*) as domain_cnt
FROM pet
group by domain
order by domain_cnt desc;
select
mail,
length(mail) as len_mail,
INSTR(mail, '@') as instr1,
length(mail)-INSTR(mail, '@') as instr2,
right ( mail, length(mail)-INSTR(mail, '@') ) as instr3,
substring_index( right ( mail, length(mail)-INSTR(mail, '@') ),'.',1) as domain,
RIGHT(mail, 6) AS right_fun1,
RIGHT(mail, 4) AS right_fun2
from pet;;
====================================================================
