answersy.com Blog

See http://dev.mysql.com/doc/refman/4.1/en/bit-functions.html

http://dev.mysql.com/doc/refman/5.0/en/bit-functions.html

And the users' comments are very helpful!

SELECT
SUM(IF(rubrik & 1, 1, 0)) actus,
SUM(IF(rubrik & 2, 1, 0)) shopping,
SUM(IF(rubrik & 4, 1, 0)) utils,
SUM(IF(rubrik & 8, 1, 0)) communication,
SUM(IF(rubrik & 16, 1, 0)) services,
COUNT(user_id) AS total,
FROM preferences p

The official syntax can be found at http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

It is really cool!

For example I have a table Tbl_Cmp_Grp_Srt_Txt with four columns Cmp, Grp, Srt, Txt
Cmp has two different values "C1" and "C2", we want to group by Grp, sort by Srt and concat all the Txt's together and put the different groups side-by-side. Here is how I did it.

SELECT t1.Cmp, t1.Grp, t1.Txts, t2.Cmp, t2.Grp, t2.Txts
FROM
(SELECT Cmp, Grp, GROUP_CONCAT(Txt ORDER BY Srt separator '\t') Txts
FROM Tbl_Cmp_Grp_Srt_Txt
WHERE Cmp = 'C1' GROUP BY Grp) AS t1,
(SELECT Cmp, Grp, GROUP_CONCAT(Txt ORDER BY Srt separator '\t') Txts
FROM Tbl_Cmp_Grp_Srt_Txt
WHERE Cmp = 'C2' GROUP BY Grp) AS t1,
WHERE t1.Grp = t2.Grp
AND t1.Txts <> t2.Txts

• REPLACE(url, str2, str1), replace all the "https://" with "http://"
• SUBSTRING_INDEX(xxx, '/', 3), get everything before the 3rd '/'
• REPLACE(yyyy, str1, ''), remove 'http://'

DROP FUNCTION IF EXISTS getHostFromUrl;
CREATE FUNCTION getHostFromUrl (url text(1024)) RETURNS CHAR(255)
BEGIN
DECLARE str1 varchar(10);
DECLARE str2 varchar(10);
SET str1='http://';
SET str2='https://';
RETURN REPLACE(SUBSTRING_INDEX(REPLACE(url, str2, str1), '/', 3), str1, '');
END;

See official Mysql Reference http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html#function_md5

mysql> SELECT MD5('testing');

Step 1: Download the package

• You can find the official download Link at http://dev.mysql.com/downloads/
• For MySql 5.0: http://dev.mysql.com/downloads/mysql/5.0.html#downloads

Step 2: Install the Binary
shell> /usr/sbin/groupadd mysql
shell> /usr/sbin/useradd -g mysql mysql
shell> cd /usr/local
shell> gunzip < /PATH/TO/MYSQL-VERSION-OS.tar.gz | tar xvf -
shell> ln -s FULL-PATH-TO-MYSQL-VERSION-OS mysql
shell> cd mysql
shell> scripts/mysql_install_db --user=mysql
shell> chown -R root .
shell> chown -R mysql data
shell> chgrp -R mysql .
shell> bin/mysqld_safe --user=mysql & # start mysql daemon

Step 3: Post-installation
shell> su mysql
shell> mysql/scripts/mysql_install_db

shell> mysql -u root -p
mysql> use mysql
mysql> GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
mysql> set PASSWORD FOR 'user'@'localhost' = PASSWORD('password');
mysql> FLUSH PRIVILEGES;
mysql> select * from mysql.user;

mysql> create database db_name;
mysql> SHOW DATABASES;
mysql> use db_name;
mysql> SHOWTABLES;

You have a table, say Tbl_Facts (Id INT, Grade INT)

What you want to do is to put number of rows for different values of Grade side by side.

You can use IF function to do so if you know all the possible values of Grade

SELECT
SUM(IF(Grade  = 1, 1, 0)) AS CntGrade1,
SUM(IF(Grade  = 2, 1, 0)) AS CntGrade2,
SUM(IF(Grade  = 3, 1, 0)) AS CntGrade3,
SUM(IF(Grade  NOT IN (1, 2, 3), 1, 0)) AS CntOthers
FROM Tbl_Facts

If you have a formula to calculate a score of your facts, say when Grade is 1, you get 10 points; when Grade is 2, you get 5, when Grade is 3 you get 0, otherwise you get -5. What is the total score of the rows in your table?

SELECT
SUM((CASE Grade   WHEN 1 THEN 10 WHEN 2 THEN 5 WHEN 3 THEN 0 ELSE -5 END)) AS Score,
FROM Tbl_Facts

Although this is not very elegant, it works.

SELECT @no:=@no+1 rownum, t.*
FROM (SELECT @no:=0) r, table_name t