Continued from MySQL tips and tricks:

General startup:

mysql -u [username] -p

mysql: USE db_name;

Note: Every SQL command needs to end with semicolon.

Config file edit: nano /etc/mysql/my.cnf to for instance turn off logging.

- - - - - - -

Specifics:

Change password:

set password = password("newpassword");

Creating a user:

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON * . * TO 'user'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

Getting most commented blog posts from JomSocial and ordering by the number of comments:

SELECT a.*, COUNT(c.contentid) AS count
FROM jos_content AS a LEFT JOIN jos_jomcomment AS c ON a.id = c.contentid
WHERE a.state = 1 AND a.catid = 1 $user_cond
GROUP BY a.id ORDER BY count DESC LIMIT 0, $limit

Note the GROUP BY clause, we needed it in order to be able to sort by count in a proper way. Here is another example of grabbing normal articles on the frontpage in Joomla and again counting the number of Jom Comments:

SELECT a.*, f.ordering, COUNT(c.contentid) AS comment_count
FROM jos_content_frontpage AS f
LEFT JOIN jos_content AS a ON f.content_id = a.id 
LEFT JOIN jos_jomcomment AS c ON a.id = c.contentid
WHERE a.state = 1
GROUP BY a.id ORDER BY a.created DESC LIMIT 0, $limit

Since it’s impossible to use something like SELECT DISTINCT * FROM table1, table2 WHERE table1.col1 LIKE ‘%table2.col1%’ due to the fact that the table2.col1 reference won’t expand, you could do something like this instead:

SELECT DISTINCT * FROM jos_users, jos_tag_img
WHERE LOCATE( jos_users.email, jos_tag_img.tag ) !=0

This is an unorthodox usage (at least for me) of a sub query. We want to fetch all bloggers and their last blog post, all at the same time. So we select it as c and order by created, ie date. Then we group by user id, the grouping will effectively remove all the superfluous content.

SELECT DISTINCT bu.title, bu.thumb, u.name, u.id, c.introtext, c.created, SUM( c.hits ) AS hits
FROM jos_myblog_user bu, jos_users u, (
    SELECT *
    FROM jos_content
    ORDER BY created DESC
) AS c
WHERE bu.featured =1
AND bu.user_id = u.id
AND c.created_by = u.id
AND c.sectionid =1
GROUP BY u.id
ORDER BY hits DESC

Note how we also need to add the AND c.created_by = u.id AND c.sectionid =1 in order to get the right posts, without them we would get the wrong content joined to the result. It’s a little bit contra intuitive that this is possible “after” the sub query should have been executed but I suppose it’s magic like this that the query optimizer is managing or something. I don’t really know, I’m just happy and surprised that the above query is giving me the correct records.

SELECT player.custId AS handle, SUM(activity.rakeCash) + SUM(activity.rakeStt) + SUM(activity.rakeMtt) AS rake, 
COALESCE(SUM(bonuses.paidBonus), 0) AS bonus
FROM gds_player player 
LEFT JOIN gds_player_poker_gaming_activity AS activity ON player.custId = activity.custId
LEFT JOIN gds_bonus_transactions AS bonuses ON (
  activity.date = bonuses.date 
  AND activity.custId = bonuses.custId 
  AND bonuses.bonusId != 2942
)
WHERE player.campaign = '$ref' 
AND activity.date <= '$end_date' 
AND activity.date >= '$start_date'
GROUP BY player.custId ORDER BY rake DESC

Another beast, note the multiple conditions in the JOIN ON clause there.

UPDATE `users` SET username = CONCAT(username, '_cl') 
WHERE username NOT REGEXP '^.*_cl$'

A good example of regular expressions in MySQL, the above will change all usernames that doesn’t end with “_cl” already to end with it.

GRANT ALL PRIVILEGES ON `database` . * TO 'user'@'localhost' WITH GRANT OPTION ;

Granting access to a database to a user.

ALTER TABLE users AUTO_INCREMENT = 2000000001;

Explicitly sets the auto increment value on a table.

UPDATE table SET value=(replace (value, 'string to replace', 'replace with'));

Will do a string replace on the value column in table.