MySql Exmple:
1.Copying the table in mysql
Syntax:
Create table <newtablename> select * from <oldtablename>
ex: create table srini select * from ach_state_master
2.Cpoying only selected table in mysql
syntax:
create table <newtablename> select <selected columns> from <oldtablename>
ex:create table srini2 select ach_sm_state_id, ach_sm_cntry_id from ach_state_master
3. Display the Mysql version
$ select version();
4. display the used databases name
$ select database();
5.specify whic row to select(where condtion is used for this statement)
$ select * from vms_candidate where can_firstname='santhosh'
$ select * from vms_candidate where can_firstname like 's%'
6. display the data as your wish
$ select concat(monthname(submitted_date),' ',dayofmonth(submitted_date),',',year(submitted_date)) from vms_candidate;
$ select date_format(submitted_date,'%M,%e,%Y') from vms_candidate;
7.
WHERE Clauses and Column Aliases
$ select date_format(submitted_date,'%Y') from vms_candidate where date_format(submitted_date,'%Y')=2013;
$ select * from vms_candidate where date_format(submitted_date,'%Y') like '2%'
$ select date_format(submitted_date,'%M,%e,%Y') from vms_candidate where date_format(submitted_date,'%Y') like '2%'
8. write the condition without using where condtion(if the condition is satisfied it will give boolean value otherwise it will give 0 valu)
$ select date_format(submitted_date,'%M,%e,%Y') < 2014 from vms_candidate;
$ select date_format(submitted_date,'%M,%e,%Y') > 2014 from vms_candidate;
$ select DISTINCT user_name='asdfs' from vms_admin_users;
$ select DISTINCT user_name='joseph' and password='ait123' from vms_admin_users;
$ select count(DISTINCT user_name='joseph') from vms_admin_users;
9. working with null
$ select * from vms_candidate where submitted_date =null;
$ select * rom vms_candidate where submitted_date !=null;(both of them dosn't dipaly any value)
correct:
$ select * from vms_candidate where submitted_date is null;
$ select * from vms_candidate where submitted_date is not null;
$ select if(id is null,'unknown',id) from vms_candidate ;
$ select ifnull(id,'unknown') from vms_candidate;
10.Sorting a Result Set (order by command is used)
$ select can_firstname from vms_candidate order by can_firstname;
$ select can_firstname,can_lastname from vms_candidate order by can_firstname,can_lastname;
11.Using Views to Simplify Table Access();(we can't able to edit the view tbale);
$ CREATE VIEW mail_view AS select can_firstname from vms_candidate order by can_firstname;
12.Selecting Data from More Than One Table
$ select * from vms_candidate where user_id= (select admintypeid
from admintype_menuoptions where admintypeid=3)
13.Selecting Rows from the Beginning or End of a Result Set(limit command is used)
$ select * from vms_candidate limit 5; -display the first five value)
$ select * from vms_candidate limit 4,5; -display the five value starts from 4th value)
14.create new table based on oldtable:
syntax:
create table <new table> like <oldtable>
$ create table vms_candidate1 like vms_candidate;
15.insert the value from old table into new table
$ insert into vms_candidate1 select * from vms_candidate where can_firstname='santhosh'
16.create the temporary table - when you close the server connection the temp table delete it self)
$ create temporary table vms_candidate select can_firstname from vms_candidate order by can_firstname
$ select * from name;
17.Checking or Changing a Table’s Storage Engine
$ SHOW TABLE STATUS LIKE 'vms_candidate' -display the table status like engine name)
$ SHOW CREATE TABLE vms_candidate; -(get the tabe inormation)
$ ALTER TABLE vms_candidate ENGINE = InnoDB; (chnage the engine name)
18.Find the character length:
$ select char_length(can_firstname) from vms_candidate;
$ select * from vms_candidate CHAR_LENGTH( can_firstname ) =8;
19.Writing String Literals
$ select i'm srinivasan'; -it show erorr because our quotation is wrong
$ select "i'm srinivasan";
20.Checking a String’s Character Set or Collation
$ select user(),charset(user()),collation(user());
21.Display the character set:
$ show character set;
$ SHOW COLLATION LIKE 'latin1%';
22.set the character set
$ set names 'latin1'
$ SELECT CHARSET('abc'), COLLATION('abc');
23.convert the string in uppercase or lowercase
$ select upper(can_firstname) from vms_candidate;
$ select lower(can_firstname) from vms_candidate;
$ select concat(upper(can_firstname),lower(can_firstname)) from vms_candidate;
24.Display the initial letter in cap
$ select inital_cap(can_firstname) from vms_candidate;
25.Pattern Matching with SQL Patterns
End letter matching
$ select can_lastname from vms_candidate where can_lastname like '%r'
Firsttwo line matching
$ select can_lastname from vms_candidate where can_lastname like 'ku%'
middle character matching
$ select can_lastname from vms_candidate where can_lastname like '%ma%'
third postion character matching
$ select can_lastname from vms_candidate where can_lastname like '__m%'
26. display the last two character :
$ select right(can_lastname,2) from vms_candidate;
27.display the first three character:
$ select left(can_lastname,3) from vms_candidate;
$ select concat(left(can_lastname,3),right(can_lastname,1)) from vms_candidate;
28.Pattern Matching with Regular Expressions
Strings that begin with a particular substring
$ select can_lastname from vms_candidate where can_lastname regexp '^ku'
Strings that end with a particular substring:
$ select can_lastname from vms_candidate where can_lastname regexp 'ar$'
Strings that contain a particular substring at any position:
$ select can_lastname from vms_candidate where can_lastname regexp 'ar'
Strings that contain a particular substring at a specific position:
$ select can_lastname from vms_candidate where can_lastname regexp '^...ar'
29.Breaking Apart or Combining Strings( use left(),right(),mid(),concat(),substring_index())
display the last two character
$ select right(can_lastname,2) from vms_candidate;
display the midel character:
$ select mid(can_lastname,2,4) from vms_candidate;
display the first three character
$ select left(can_lastname,3) from vms_candidate;
display the two column information in one column
$ select concat(can_lastname,' ',can_firstname) from vms_candidate;
The substring_index(columname,matchedchar,'positive or negative value')
positive value- display the right side character after matching the character
negative value - display the let side character after matching the character
$ select substring_index(can_lastname,'m',1) from vms_candidate;
The substring-substring(columnanem,lenght)
$ select substring(can_lastname,2) from vms_candidate;
$ select substring(can_lastname,2,2) from vms_candidate;
30.Searching for Substrings
locate()-function is used for searching the substring
$ select locate('ma',can_lastname) from vms_candidate;
$ select locate ('ma',conact(can_firstname,' ',lastname)') from vms_candidate;
31.Using FULLTEXT Searches
$ ALTER TABLE vms_candidate ADD FULLTEXT (can_firstname);
$ SELECT count(*) from vms_candidate WHERE MATCH(can_firstname) AGAINST('santhosh');
<!------------------Working with Dates and Times--------->
1.Changing MySQL’s Date Format using (date_format(columname,'abbervation'),timeformat(columname,'abberivation'))
$ select date_format(submitted_date,'%M,%e,%y') from vms_candidate;
$ select time_format(submitted_date,'%r') as '12-hourstime',time_format(submitted_date,'%T') as '24-hourstime' from vms_candidate;
2.Determining the Current Date or Time
display the current date(),currenttime and display the date and time
$ select curdate(),curtime(),now()
$ select UTC_DATE();
$ select UTC_TIME();
$ select utc_timestamp;
3.Extracting Parts of Dates or Times
Diplay the date
$ select date(submitted_date) from vms_candidate;
Display the time
$ select time(submitted_date) from vms_candidate;
Display the year
$ select year(submitted_date) from vms_candidate; --like 2013
Display the month name
$ select monthname(submitted_date) from vms_candidate; --like(feb)
Display the month in integer
$ select month(submitted_date) from vms_candidate; --like(1-12)
Display the day of month
$ select dayofmonth(submitted_date) as date from vms_candidate order by date ; --like( 1..31)
Display the day of week
$ select dayofweek(submitted_date) from vms_candidate; -(1 to 7)
Display the day name
$ select dayname(submitted_date) from vms_candidate; -like monday
Display the day of year
$ select dayofyear(submitted_date) from vms_candidate; like 218days
Display the weekday
$ select weekday(submitted_date) from vms_candidate; --(0--6)
Display the hours
$ select hour(submitted_date) from vms_candidate;
$ select second()
$ select minute()
4. Converting Between Temporal Data Types and Basic Units
change the time into seconds
$ select time_to_sec(curtime())
chanage the sec to time
$ select sec_to_time()
5.Converting between dates and days
convert date to days
$ select to_days(submitted_date) from vms_candidate;
convert days to date
$ select from_days(to_days(submitted_date)) from vms_candidate;
6. covert the date into unix time stamp
$ select unix_timestamp(submitted_date) from vms_candidate;
7.convert the unix timestamp into date
$ select from_unixtime(unix_timestamp(submitted_date)) from vms_candidate;
8.Calculating the Interval Between Two Dates or Times
$ select datediff(curdate(),submitted_date) from vms_candidate;
$ select timediff(time(submitted_date),curtime()) from vms_candidate;
9.Adding Date or Time Values
$ select addtime(time(submitted_date),curtime()) from vms_candidate;
$ select date_add(curdate(),INTERVAL 3 DAY) from vms_candidate;
$ select date_add(submitted_date,INTERVAL 3 DAY) from vms_candidate; display the date in after three days
$ select date_add(submitted_date,INTERVAL -3 DAY) from vms_candidate; display the date in befter three days
10.Calculating Ages
$ select timestampdiff(year,'1989-09-17',curdate()) from vms_candidate;
$ select timestampdiff(year,submitted_date,curdate()) from vms_candidate;
11.calculating Age in the months
$ select timestampdiff(month,submitted_date,curdate()) from vms_candidate;
12.calculaing age in day
$ select timestampdiff(day,submitted_date,curdate()) from vms_candidate;
$ select timestampdiff(day,'1989-09-17',curdate()) from vms_candidate;
13. Finding the First Day, Last Day, or Length of a Month
$ select submitted_date,date_sub(submitted_date,interval dayofmonth(submitted_date)-1 day) from vms_candidate;
$ select last_day(submitted_date) from vms_candidate;
14.Finding sunday in the particular week
$ select date_add(submitted_date,INTERVAL 1-dayofweek(submitted_date) DAY) from vms_candidate;
15.Finding saturday in th particular week
$ select dayname(date_add(submitted_date,INTERVAL 7-dayofweek(submitted_date) DAY)) from vms_candidate;
