> SELECT COUNT(*) AS repetitions, uniqueid FROM user GROUP BY uniqueid HAVING repetitions > 1; +-------------+----------------------------------+ | repetitions | uniqueid | +-------------+----------------------------------+ | 1589 | | | 2 | 00888a840f6eefb91eec3612bd93e3c4 | | 2 | ... | | 2 | ff8a67aa3f5c162ec6e7a8b3a98dbffc | +-------------+----------------------------------+ 334 rows in set (22.76 sec)
$ mysql
> use user
Database changed
> SELECT user.countryid, COUNT(*) AS repetitions, country.countryname
FROM user
JOIN country ON user.countryid = country.countryid
GROUP BY user.countryid order by repetitions DESC;
+-----------+-------------+---------------+
| countryid | repetitions | countryname |
+-----------+-------------+---------------+
| 237 | 710721 | United States |
| 40 | 58652 | Canada |
... ... ...
| 96 | 10 | Guinea-Bissau |
| 211 | 9 | Sudan |
+-----------+-------------+---------------+ Check for the presence of a foreign key/constraint, stored procedures.
> show create table user_emailcampaign; +--------------------+--------------+ | Table | Create Table | +--------------------+--------------+ | user_emailcampaign | CREATE TABLE `user_emailcampaign` ( | | ... | | CONSTRAINT `user_emailCampaign_userid` | | FOREIGN KEY (`userid`) REFERENCES `user` (`userid`) | | ON DELETE CASCADE ON UPDATE CASCADE, | | CONSTRAINT `user_emailCampaign_emailCampaignid` | | FOREIGN KEY (`emailid`) REFERENCES `emailcampaign` (`emailid`) | | ON DELETE CASCADE ON UPDATE CASCADE) | | ENGINE=InnoDB DEFAULT CHARSET=utf8 +--------------------+--------------+
> SHOW PROCEDURE STATUS;
+-----------------+-------------------------+-----------+---------------+----------+---------+---------------+---------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment |
+-----------------+-------------------------+-----------+---------------+----------+---------+---------------+---------+
| test_10apr_v33 | CopyComponent | PROCEDURE | foo@localhost | 2010-... | 2010-.. | INVOKER | |
| test_10apr_v33 | GetComponentLineageInfo | PROCEDURE | foo@localhost | 2010-... | 2010-.. | INVOKER | |
... ... ... ... ... ... ... ...
| studiowebui_v30 | GetComponentLineageInfo | PROCEDURE | foo@localhost | 2010-... | 2010-.. | INVOKER | |
| studiowebui_v30 | MoveComponent | PROCEDURE | foo@localhost | 2010-... | 2010-.. | INVOKER | |
+-----------------+-------------------------+-----------+---------------+----------+---------+---------------+---------+ Non-English text shows up as ??? or other silly characters.
Elsewhere
MySQL by default handles requests and queries using very basic character text encoding. If you are performing inserts and selects for records that contain non-English text, best to tell MySQL to use utf8 character encoding. Otherwise, MySQL will insert and retrieve a crude approximation of the text, often reduced to streams of ? and/or other strange characters.
<PHP?
mysql_connect($dbhost, $username, $password) or die("mysql_connect() failed");
mysql_select_db($database) or die("mysql_select() failed");
mysql_query("SET CHARACTER SET utf8");
mysql_query("SET NAMES utf8");
$result = mysql_query($query) or die(mysql_error());
?> Sub-query, Union.
Elsewhere
UNION allows the results of more than one select statement to be combined (each select should ask for the same fields). MySQL behavior seems to default to eliminating identical records when UNION is used. A sub-query allows SQL statements that use a list of values to be dynamic, making for recursive SQL statements. Innermost queries are resolved first, then processing spins outward until entire statement is processed. Sub-queries can be fun, but if not carefully crafted they can be tremendously inefficient.
select distinct user.email, user.uniqueid, user.userid from user
join user_attrItem as uaPermissions on user.userid=uaPermissions.userid
where user.active and
!user.is_disabled and
(user.expc_passed or !user.expc_checked) and
user.email_confirmed and
user.countryid IN (47) and
user.email NOT IN (
SELECT distinct user.email FROM user
JOIN user_team ON user_team.userid = user.userid
JOIN teamRole ON user_team.teamRoleid=teamRole.teamRoleid AND teamRole.groupid=1500
UNION
SELECT user.email FROM user
JOIN user_audience ON user_audience.userid = user.userid
JOIN audience ON user_audience.audienceid = audience.audienceid
WHERE audience.audienceid = 19)
ORDER BY email; Conditionals and Virtual Fields
> select user.email,
if (user.email in (
select user.email from user
join user_attrItem as ua1 on
user.userid=ua1.userid and ua1.attrItemid=275),
'yes', 'no') as news,
if (user.email in (
select user.email from user
join user_attrItem as ua2 on
user.userid=ua2.userid and ua2.attrItemid=276),
'yes', 'no') as business,
if (user.email in (
select user.email from user
join user_attrItem as ua3 on
user.userid=ua3.userid and ua3.attrItemid=277),
'yes', 'no') as tech
from user
join user_attrItem as ua on user.userid=ua.userid and ua.attrItemid=1230
ORDER BY email
+----------------------+------+----------+------+
| email | news | business | tech |
+----------------------+------+----------+------+
| 1973marcos@nails.com | yes | yes | yes |
| 353034836@jj.com | no | no | no |
... ... ... ...
| zhyizm@621.com | yes | yes | yes |
| zm.mars@361.com | yes | yes | yes |
+----------------------+------+----------+------+ Derived Tables
Elsewhere
SELECT COUNT(*) AS qty, repetitions FROM ( SELECT COUNT(*) AS repetitions, email FROM bounce GROUP BY email HAVING repetitions > 1) as grr GROUP BY repetitions SELECT COUNT(*) AS repetitions, email FROM bounce GROUP BY email HAVING repetitions > 1 SELECT DATE_FORMAT(datetime, '%Y-%m-%d') ymd, COUNT(*) FROM bounce GROUP BY ymd