• Home
• Debian / Ubuntu
• Gentoo
• Linux
• Bash
• PHP
• Perl
• JavaScript
• Ajax
• jQuery
• MySQL
• Vi
• Wiki

1.  Selecting based on a date range

mysql> SELECT something FROM tbl_name WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html »

2.  Fulltext searching

Just create a table, and if the column data structure is right (text, blob, ... etc) you can create a column as a fulltext searchable column by using the following syntax,

mysql> alter table <table_name> add fulltext (<column_name>);

You can separate the columns in the syntax above with a comma if you want more than one to be indexable.

Then you use queries like,

mysql> select * from <table> where match(<column_name>) against ('search pattern' in boolean mode);

Again, you can add more columns to the match(<column_name>) syntax by separating them with commas.

http://jeremy.zawodny.com/blog/archives/000576.html »

3.  Query using grouping

In this query, I have a table that has a remote address column called remoteaddr.

The problem is, I want a query that will return these remote addresses in ascending numerical order and each remote address is listed only once and the number of them is listed too. The sample table may look like,

+-----------------+
| remoteaddr      |
+-----------------+
| 123.123.123.123 |
| 124.124.124.124 |
| 125.125.125.125 |
| 123.123.123.123 |
| 123.123.123.123 |
| 126.126.126.126 |
| 125.125.125.125 |
| 123.123.123.123 |
+-----------------+

mysql> select remoteaddr, count(remoteaddr) as num_remoteaddr from iptable group by remoteaddr order by remoteaddr asc;

The output of the above query would be the following, given the sample data above.

+-----------------+----------------+
| remoteaddr      | num_remoteaddr |
+-----------------+----------------+
| 123.123.123.123 |              4 |
| 124.124.124.124 |              1 |
| 125.125.125.125 |              2 |
| 126.126.126.126 |              1 |
+-----------------+----------------+

4.  Order results randomly

mysql> SELECT [whatever] FROM [table] WHERE [conditions] ORDER BY RAND();

5.  Reset a tables auto increment field

If you need to do this, just issue the command

mysql> ALTER TABLE [tablename] AUTO_INCREMENT = [number];

6.  Showing warnings

If a MySQL query returns warnings, just use the command,

mysql> show warnings;

to display any warning made.

7.  Setting your MySQL delimiter

If you want to change your delimiter in MySQL which is by default ; you can execute the command in MySQL,

mysql> delimiter |

This can be useful when writing stored procedures.

8.  Joins

Inner joins are of the form where tbl1.col1 = tbl2.col1 or something like that, and does not not output rows that don't match. A LEFT or RIGHT JOIN does the same thing but shows records from the left or right table being JOINed. The term outer can be added after the words LEFT or RIGHT for ODBC compatability and does nothing extra.

Reference »

8.1  Changing your password

mysql> set password = password('your_new_password');

8.2  Change a column type

mysql> alter table tblname modify colname coldef;

8.3  Adding Users (examples below)

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    ->     ON bankaccount.*
    ->     TO 'custom'@'localhost'
    ->     IDENTIFIED BY 'obscure';

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    ->     ON expenses.*
    ->     TO 'custom'@'whitehouse.gov'
    ->     IDENTIFIED BY 'obscure';

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    ->     ON customer.*
    ->     TO 'custom'@'server.domain'
    ->     IDENTIFIED BY 'obscure';

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';

mysql> GRANT USAGE ON *.* TO 'dummy'@'localhost';

After each of these, I do a flush privileges. This is usually only if you insert or update the mysql.user table. Just a precaution for grant.

9.  Adding users with some descriptions on what's going on.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
mysql> GRANT USAGE ON *.* TO 'dummy'@'localhost';

The accounts created by these GRANT statements have the following properties:

• Two of the accounts have a username of monty and a password of some_pass. Both accounts are superuser accounts with full privileges to do anything. One account ('monty'@'localhost') can be used only when connecting from the local host. The other ('monty'@'%') can be used to connect from any other host. Note that it is necessary to have both accounts for monty to be able to connect from anywhere as monty. Without the localhost account, the anonymous-user account for localhost that is created by mysql_install_db would take precedence when monty connects from the local host. As a result, monty would be treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific Host column value than the 'monty'@'%' account and thus comes earlier in the user table sort order.
• One account has a username of admin and no password. This account can be used only by connecting from the local host. It is granted the RELOAD and PROCESS administrative privileges. These privileges allow the admin user to execute the mysqladmin reload, mysqladmin refresh, and mysqladmin flush-xxx commands, as well as mysqladmin processlist. No privileges are granted for accessing any databases. You could add such privileges later by issuing additional GRANT statements.
• One account has a username of dummy and no password. This account can be used only by connecting from the local host. No privileges are granted. The USAGE privilege in the GRANT statement enables you to create an account without giving it any privileges. It has the effect of setting all the global privileges to 'N'. It is assumed that you will grant specific privileges to the account later.
• The statements that create accounts with no password will fail if the NO_AUTO_CREATE_USER SQL mode is enabled. To deal with this, use an IDENTIFIED BY clause that specifies a non-empty password.

http://dev.mysql.com/doc/refman/5.0/en/adding-users.html »

9.1  User passwords

You can do an

mysql> update user set password=password('somepassword') where Host='hostname' and User='username';
mysql> flush privileges;

or for the old password system

mysql> update user set password=old_password('somepassword') where Host='hostname' and User='username';
mysql> flush privileges;

9.2  Variables and math.

You can set variables in mysql and perform math. Some examples are below.

mysql> set @x=1;
mysql> set @y=5;
mysql> select @x + @y;

mysql> select sum(field) from table;
     Will return the sum of a column from a table.

9.3  Selecting

mysql> select * from table;
     Returns every row from the table.

mysql> select * from table where conditions;
     Returns every row from the table based on the conditions.

mysql> select field,field,field from table where conditions;
     Returns the contents you requested in a table format.
     You can also use \G instead of ; which will list the 
     contents in a more readable format.

mysql> select if(condition,true,false);
     Returns true or false is returned based on the condition. e.g. 5>7

9.4  Inserting

mysql> insert into table values (value,'value','value',value,'',value);

or

mysql> insert into table (field,field,field) values ('','','');

The second insert will insert a timestamp if you have a timestamp field.

Text fields must be surrounded by apostrophes (') or quotation marks (").

9.5  Fixing Errors

If you get an error like,

do

mysql> repair table a_table use_frm;

9.6  Rename a table

mysql> rename <table> <table_name_from> to <table_name_to>;

9.7  mysqldump compatibility

Say you are using MySQL 5 and you want to create a dump for MySQL 4, you can use the command,

# mysqldump -u username -p --compatible=mysql40 database > database.sql

The complete usage for MySQL 5 mysqldump can be found at http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html ».

2009-01-05