SQL Notes
Miscellaneous SQL commands for MySQL
| Task | Command |
|---|---|
| display current time | SELECT NOW(); |
| display logged-in user | SELECT USER(); |
| display MySQL version | SELECT VERSION(); |
| display current database | SELECT DATABASE(); |
| switch database | USE sample; |
| show tables in current db | SHOW TABLES |
| describe a table | DESCRIBE table; EXPLAIN table; SHOW COLUMNS FROM tblname; SHOW FIELDS FROM tblname; |
| using wildcards | SHOW COLUMNS FROM tblname LIKE "%server"; |
| list databases | SHOW DATABASES; |
| sorting with a condition(assign 0 as value if field is null) | ORBER BY IF(member IS NULL,0,1) |
| show only first fifty values | SELECT * FROM table LIMIT 50; |
| show eleventh to fifteenth values | LIMIT 10,5 |
| get one random value | ORDER BY RAND() LIMIT 1; |
| modify output | SELECT CONCAT(lastname, ", ", firstname) AS Fullname FROM table; |
| expressions for dates | WHERE date >= "1964-05-14" #people younger than me
WHERE MONTH(bday) = 5
WHERE MONTHNAME(bday) = "May"
WHERE DAYOFMONTH(bday) = 14
WHERE MONTH(BDAY) = MONTH(CURDATE())
TO_DAYS(CURDATE()) - TO_DAYS(bday) AS Age;
RIGHT(bday, 5)
SELECT DATE_ADD("1970-01-01", INTERVAL 10 YEAR)
SELECT DATE_ADD("1970-01-01", INTERVAL 10 DAY)
SELECT DATE_ADD("1970-01-01", INTERVAL 10 MONTH)
SELECT TIME_TO_SEC(1001) |
| Calculate time difference from two seperate date and time columns | SELECT timediff((concat(Resultdate, " " , Resulttime)),(concat(Orderdate, " ", Ordertime))) as Elapsed from Results |
| using variables | @var := value
SELECT @date := date FROM table WHERE id = 2;
SELECT * FROM table WHERE bday =< @date; |
| summary functions | COUNT(), MIN(), MAX(), AVG(), SUM() # use HAVING instead of WHERE to apply conditions to summary functions: eg SELECT name, address, COUNT(*) AS Count FROM address HAVING Count < 3; SELECT sex, AVG(age) FROM student WHERE score > 90; |
| List defined users on database | SELECT User, Host FROM mysql.user |
| Show permissions defined for a user | SHOW GRANTS FOR username@hostname |
| Change password in mysql client | set password = password("yournewpassword") |
| Kill a process | kill processid |
| Task | Command |
|---|---|
| display output in vertical format | \G |
| cancel current SQL statement | \c |
| connect to "sample" database | mysql sample |
| read in SQL commands from file "sqlcommands.sql" to sample db | mysql sample < sqlcommands.sql mysql> SOURCE sqlcommands.sql |
| read in SQL commands from file "sqlcommands.sql" producing xml output | mysql --xml sample < sqlcommands.sql |
| read in SQL commands from file "sqlcommands.sql" producing html output | mysql --html sample < sqlcommands.sql |
| read in data from tab seperated text file | mysql> LOAD DATA INFILE "data.tsv" INTO table tblname; |
| show databases | : mysqlshow |
| show tables in sample db | : mysqlshow sample |
