Kushal’s Java Blog

Kushal Paudyal October 9th, 2009

[Most of these queries are tested for MySQL Database]

• Creating A Table With Primary Key:
  CREATE TABLE SANJAAL.`DATA_NEPAL_DISTRICTS` (
  `DISTRICT_ID` INTEGER AUTO_INCREMENT,
  `DISTRICT_NAME` VARCHAR(100) DEFAULT NULL,
  `DISTRICT_CD` CHAR(3) DEFAULT NULL,
  PRIMARY KEY (DISTRICT_ID)
  ) ENGINE=INNODB DEFAULT CHARSET=UTF8

• Loading Text Data From File Into Table:
  LOAD DATA INFILE ‘data.txt’ INTO TABLE db2.my_table;

• Adding A Column:
  ALTER TABLE SANJAAL.SONGS ADD COLUMN `ARTIST` VARCHAR (100) DEFAULT NULL;

• Adding Column and A Primary Key:
  ALTER TABLE SANJAAL.DATA_NEPAL_AIRPORTS ADD AIRPORT_ID INT AUTO_INCREMENT, ADD PRIMARY KEY (AIRPORT_ID)

• Dropping the columns:
  ALTER TABLE SANJAAL.RICHEST_400_AMR_2009 DROP ID

• Modifying A Column:
  ALTER TABLE SANJAAL.DATA_NEPAL_AIRPORTS CHANGE ICAO_CODE ICAO_CODE CHAR(4)

• Renaming a table:
  RENAME TABLE SANJAAL.SONGS_TEMP TO SANJAAL.SONGS

• String Concatination:
  SELECT CONCAT(‘<li>’, ‘<a href=”‘, url, ‘”>’,artist,” – “, songs_name,’”</a></li>’)
  from sanjaal.songs

• SUBSTRING_INDEX(str,delim,count)
  Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.mysql> SELECT SUBSTRING_INDEX(‘www.mysql.com’, ‘.’, 2);
  -> ‘www.mysql’
  mysql> SELECT SUBSTRING_INDEX(‘www.mysql.com’, ‘.’, -2);
  -> ‘mysql.com’
  
• Show the ‘Create Table’ Script For Existing Tables:
  SHOW CREATE TABLE SANJAAL.RICHEST_400_AMR_2009

• SQL Group By and Count Even If No Results:
  SELECT A.ID, A.CATEGORY, COUNT( B.CATEGORYID ) CATCOUNT
  FROM CATEGORYTBL A
  LEFT JOIN QUOTATIONSTBL B ON A.ID = B.CATEGORYID
  GROUP BY A.ID, A.CATEGORY

• Maximum Length Of Data In A Column:
  SELECT MAX(LENGTH(ICAO_CODE)) FROM SANJAAL.DATA_NEPAL_AIRPORTS;

• Selecting Random Rows From Table:
  SELECT myColumns FROM mytable ORDER BY RAND() LIMIT 5

Sanjaal.com is owned and maintained by Sanjaal Corps, Nepal. The company offers Webhosting and Domain Registration Services, IT Solutions and Business Analysis. Sanjaal.com website features H1B Visa Information, Entertainment Portal, Link Directory Service, Free Articles, Free Open Source Tutorials on Java and J2EE Platform, Digital Photography, High Resolution Picture Gallery and Free Reliable Image Hosting Services. Future plan includes Open Source Software Development Portal, Technical Solutions and Customizable Movie and Music Arena. We would be introducing data backup, data recovery, data hosting and voip solutions. Stay free from phishing – our website does not ask for your credit card and banking information. Happy Surfing!

• Comments(0)