Sherlock Group

ms_user table

user_id
username
email
area_id

1
sherlock
c_sherlock@email.com
1

2
poirot
poirot@email.com
2

3
watson
b_watson@email.com
1

4
hasting
hasting@email.com
2

5
mycroft
a_mycroft@email.com
1

SELECT *  FROM `ms_user`
ORDER BY `area_id` , `email` DESC , `username`;

Read Full Post »

The boolean full-text search capability supports the following operators:
1. +
A leading plus sign indicates that this word must be present in every row returned.
2. -
A leading minus sign indicates that this word must not be present in any row returned.
3. (no operator)
By default (when neither + nor - is specified) the word is optional, but […]

Read Full Post »

The MATCH() function performs a natural language search for a string against a text collection. A collection is a set of one or more columns included in a FULLTEXT index. The search string is given as the argument to AGAINST(). For every row in the table, MATCH() returns a relevance […]

Read Full Post »

“Having” clause is like “Where” clause, but it use when the query need to refer to aggregate functions, which the “WHERE” clause cannot.
SELECT user, MAX(salary) FROM users GROUP BY user HAVING MAX(salary);
Don’t use HAVING for items that should be in the WHERE clause.
For example, do not write this:
SELECT col_name FROM tbl_name HAVING col_name […]

Read Full Post »

Triggers are programmable events that react to queries and reside directly on the database server. Triggers can be executed before or after INSERT, UPDATE or DELETE statements.
with triggers, we can know what data has been insert, update or delete.
actually it’s easy to create, this is the example:

Read Full Post »