Posts Tagged ‘SQL’

Users and Permissions in MySQL

// March 24th, 2009 // No Comments » // Database, MySQL

The best way for creating users in MySQL is by using CREATE USER or GRANT statements. Another way is by manipulating MySQLs grant tables, but this is not recommended.

By default the superuser is root. A password must be specified for this. It is best if this is only used for superadmin purposes.

For new accounts you need to create two users with the same name. One is for access via localhost, the other remotely.

GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

To remove a user, use

DROP USER user;

To Change a password, use:

SET PASSWORD FOR 'user'@'localhost' = PASSWORD('newpassword');
SET PASSWORD FOR 'user'@'%' = PASSWORD('newpassword');

SQL – Counting Occurrances of a String

// October 14th, 2008 // No Comments » // SQL

SELECT DISTINCT
person,
sum(CASE WHEN Field1 = 'String1' THEN 1 ELSE 0 END) AS [Field 1],
sum(CASE WHEN Field2 = 'String2' THEN 1 ELSE 0 END) AS [Field 2],
sum(CASE WHEN Field3 = 'String3' THEN 1 ELSE 0 END) AS [Field 3],
sum(CASE WHEN Field4 = 'String4' THEN 1 ELSE 0 END) AS [Field 4],
sum(CASE WHEN Field5 = 'String5' THEN 1 ELSE 0 END) AS [Field 5]
FROM
tblYourTable
GROUP BY person