[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
- Finding the physical location of MySQL Database (datadir)
SELECT @@DATADIR - Renaming a Table
ALTER TABLE SANJAAL.SANJAAL_USERS CHANGE gender GENDER CHAR(1) - Re-ordering Column Position
ALTER table SANJAAL_USERS MODIFY COLUMN `MIDDLE_NAME` varchar(100) AFTER `FIRST_NAME`
