On this page you will find sample SQL statements with explainations. These examples all work with mysql.

For additional help with MySQL including installation, setup, troubleshooting and more, contact us by email or phone: experts@expertsinunix.com or (410) 838 5100. We do offer support on a per incident basis for $199 per incident.

If you would like to see some of the queries on this page in action look at our Server Performance Page.

create table BlackList (IPAddress VARCHAR(20), BlackListName VARCHAR(30),datecreated date,Primary Key(IPAddress));

The above command creates a table called BlackList with three columns. The first column is called IPAddress and it is type VARCHAR. Up to 20 characters are allowed in the field. The second column is called BlackListName is also a VARCHAR column that allows 30 characters. The third column is datecreated and it is a date column which allows date calculations on it. The table is indexed on IPAddress. This allows for fast searches on the table.

select * from tblgreylist where FromAddress rlike "<>";

The above select statement fetches all columns and rows from the tblgreylist table where the from address is like <>.

Select * from BlackList;

The above select statement fetches all columns and rows from the BlackList table.

select * from tblEmailFilter WHERE DATE_SUB(CURDATE(),INTERVAL 10 day) <= DateEntered;

The above select statement fetches all columns and rows from the tblEmailFiliter table where the DateEntered column is more than 10 days old.

Select IPAddress,datecreated from BlackList;

The above select statement fetches the IPAddress and datecreated columns from all of rows from the BlackList table.

select * from tblEmailFilter where ToAddress rlike "john";

The above select statement fetches all columns from tblEmailFilter where the ToAddress column has the word john. Case is not important.

select RejectReason4,Count(RejectReason3) from tblEmailFilter where RejectReason3 rlike "Rejected Blacklisted IP Address" and DATE_SUB(CURDATE(),INTERVAL 5 DAY) <= DateEntered GROUP by RejectReason4;

The above select statement shows the effectiveness of each DNSBL you are using in the ptsfilter over the last 6 days.

select RejectReason3,Count(RejectReason3) from tblEmailFilter WHERE DATE_SUB(CURDATE(),INTERVAL 5 DAY) <= DateEntered Group by RejectReason3;

The above select statement gives a count of rhe individual tests performed by ptsfilter over the last 6 days.

select RejectReason3,count(RejectReason3) as count from tblEmailFilter group by RejectReason3 order by count desc;

The above select statement will show you the effectiveness of each test you are using in your efforts to fight unwanted email.

select * from tblEmailFilter where DateEntered > "2005-01-26" order by DateEntered;

The above select statement fetches all columns from tblEmailFilter where the DateEntered column is greater than 2005-01-26 and sorts the reseults by the DateEntered column;

repair table tblEmailFilter;

The above statement checks the table tblEmailFilter and repairs it if necessary.

insert into BlackList (IPAddress,BlackListName,datecreated) values('192.16.2.223','locallist','2005-01-31');

The above statement inserts one row of data in the BlackList table.

delete tblEmailFilter,tblmessage from tblEmailFilter,tblmessage where COL010 = 'j315013M059887' and msgid = 'j315013M059887';

The above delete statement deletes the row from both tblEmailFilter and tblmessage where the msgid = j315013M059887.

delete from tblEmailFilter WHERE DATE_SUB(CURDATE(),INTERVAL 10 day) > DateEntered;

The above delete statement deletes all columns and rows from the tblEmailFiliter table where the DateEntered column is more than 10 days old.

alter table tblgreylist change FromAddress FromAddress varchar(200);

The above statement changes the size of the FromAddress column int tblgreylist table.

For more information about our software please contact us:
info@expertsinunix.com , or call our offices at 410-838-5100.