SEARCH


 


MySQL Commands

1. How do I get a list of my MySQL tables?

Write a SELECT that includes the following text:

SHOW TABLES FROM <databaseName>;

This will present you with a list of the MySQL data dictionary type tables in your database, you can then also add a 'WHERE' or 'LIKE' command to the sql above in otrder to filter the results.

2. How do I get the latest date and time in an sql query so I can instert it into my date and time column in a table?

The best way of doing this is to use the command now() in your sql statement, e.g.:

INSERT INTO MYTABLE (MYDATE, DESCRIPTION) VALUES (now(), description);

2. How do I get the last occurence of a character from a string using MySQL?

This is a tricky one, but the best way to go about it is to first reverse the string then find the first occurence of the string. Then subtract the length of the string from the location of the string you are looking for, then use SUBSTRING to get the values you require.

The example below will update a column in a table with the value after the last occurence of a '=' sign in a field.

UPDATE TABLE SET col1 = SUBSTRING( col2, (LENGTH( col2 ) - LOCATE( '=', REVERSE( col2 ) ) ) +2)

2. How do I extract the 1st positions of a character string that are numbers and then order by number using MySQL?

This can be accomplished using one select statement. It involves using the REPLACE, LEFT and CAST functions.

Say we have this string:

1. This is no. 1
9. This is no. 9
10. This is no. 10

If you use an "order by" you will get this:

1. This is no. 1
10. This is no. 10
9. This is no. 9

As it's a character string, but we want to see it in numerical order.

SELECT * FROM `<table>` ORDER BY CAST(REPLACE(LEFT(<column>,2),'.','') AS SIGNED)

Now you will get the data back in the correct order.

3. How do I dump the entire contents of my database from MySQL into a file, that includes SQL type INSERT statements in it?

mysqldump -h <host name> -u<username> -p<password> <db name> > my-dump.file

4. How do I dump the contents of 1 table from MySQL into a file, that includes SQL type INSERT statements in it?

mysqldump -h<host name> -u<username> -p<password> <db name> <table name> > my-dump.file

TIP: When dumping tables or databases, it's worth adding --quote-names as a parameter, just in case you may have used a reserved word as a table or column name.

5. How do I load up a file into the database, which was created using mysqldump which includes SQL statements?

mysql -h<host name> -u<username> -p<password> <database name> < my-dump.file

6. How can I select a random set of records using MySQL?

ORDER BY RAND() combined with LIMIT is useful for selecting a random sample of a set of rows:

SELECT * FROM <tablename> ORDER BY RAND() LIMIT 1000;

Note that RAND() in a WHERE clause is re-evaluated every time the WHERE is executed.

7. How can I show records which are so many days old?

Have a date field in your table which includes the date the record was inserted, try a TIMESTAMP data type, for our example our field is called 'DATE_ADDED' and then use the DATEDIFF MySQL function.

Then run the following type of query:

SELECT <columns>
FROM <table>
WHERE DATEDIFF(NOW( ), DATE_ADDED) <28

Which will display all records which are less than 28 days old

8. How can I set my text to have a Capital letter and then lowercase like UCFIRST in PHP?

SELECT CONCAT(UPPER(SUBSTRING(firstName, 1, 1)), LOWER(SUBSTRING(firstName FROM 2))) AS properFirstName

You could also create your own MySQL function of this and then just call it, instead of writing it in selects all the time:

CREATE FUNCTION initCap (s varchar(255))

RETURNS VARCHAR(255) DETERMINISTIC

RETURN CONCAT(UPPER(LEFT(s,1)),MID(s,2));

9. How can I capitalise each word in a MySQL text string without using PHP?

Whereas point 8 only uppercases the first letter, this function below will capitalise each word in the string, in the same way that UCWORDS works in PHP.

Just copy this function code and run it at the Mysql command line prompt.

DELIMITER $$

DROP FUNCTION IF EXISTS `ICap`$$

CREATE FUNCTION `ICap`(mystring varchar(1000))
RETURNS VARCHAR(1000)
BEGIN

DECLARE i INT DEFAULT 1;
DECLARE myc, pc CHAR(1);
DECLARE myoutstring VARCHAR(1000) DEFAULT LOWER(mystring);

WHILE i <= CHAR_LENGTH(mystring) DO
SET myc = SUBSTRING(mystring, i, 1);
SET pc = CASE WHEN i = 1 THEN ' ' ELSE SUBSTRING(mystring, i - 1, 1) END;
IF pc IN (' ', '&', '''', '_', '?', ';', ':', '!', ',', '-', '/', '(', '.') THEN
SET myoutstring = INSERT(myoutstring, i, 1, UPPER(myc));
END IF;
SET i = i + 1;
END WHILE;

RETURN myoutstring;

END$$

DELIMITER ;

Then use SELECT ICap('test test'); to get back 'Test Test' - you could even call the function "ucwords" if you want to make it easier to remember.

10. How can I get the first word of a string?

There are two ways to do this using MID and LOCATE or by just using SUBSTRING_INDEX

a. SELECT MID(<column_name>,1,LOCATE(' ',<column_name>)) FROM <table>

b. SELECT SUBSTRING_INDEX( <column_name>, ' ', 1 ) FROM <table>

11. How do I add a primary key index to a table and ignore and remove duplicates?

Use the following SQL statement, put more than one column name in the brackets if you want more than one column as the primary key.

ALTER IGNORE TABLE <table> ADD PRIMARY KEY (<column name>)

12. How do I delete a primary key?

ALTER IGNORE TABLE <table> DROP PRIMARY KEY

13. How do I find all the duplicates in my table?

SELECT <column name>, COUNT( <column name> ) AS numOcc
FROM <table>
GROUP BY <column name>
HAVING (
COUNT( <column name>) >1
)

This is an example of where you have duplicates in one column only.

14. How can I show records for Last Month, This Month, Last Week, This Week, Last Year, This Year?

Just add these AND SQL statements to your queries, in these examples the ORDER_DATE is your column name which holds the date you are comparing against.

This Week

AND YEARWEEK(ORDER_DATE) = YEARWEEK(CURRENT_DATE)

Last Week

AND YEARWEEK(ORDER_DATE) = YEARWEEK(CURRENT_DATE - INTERVAL 7 DAY)

This Month

AND MONTH(ORDER_DATE) = MONTH(NOW()) AND YEAR(ORDER_DATE) = YEAR(CURRENT_DATE())

Last Month

AND SUBSTRING(ORDER_DATE FROM 1 FOR 7) = SUBSTRING(CURRENT_DATE - INTERVAL 1 month FROM 1 FOR 7)

or

AND MONTH(ORDER_DATE) = (MONTH(NOW())-1) AND YEAR(ORDER_DATE) = YEAR(CURRENT_DATE())

This Year

AND YEAR(ORDER_DATE) = YEAR(CURRENT_DATE())

Last Year

AND YEAR(ORDER_DATE) = (YEAR(CURRENT_DATE())-1)

15. How can I count the number of occurences in a MySQL string?

There is currently no built-in function to do this, so I use:

SELECT LENGTH( 'THIS TEST' ) - LENGTH( REPLACE ('THIS TEST','T','');

Answer output is: 3