|
|
 |
Select Statement List of Categories
Page 1 Page 2
- How do I select a record from a mysql table?
- How do I select records from select records?
- How do I select specific records?
- How do I quote a value and whay should I?
- How I do select specific records using regular expressions?
- How do I select random records?
- How do I select unique or distinct records?
- How do I select a limited number or records?
- How do I simply data keys returned in selections with complext column or functions names?
- How do I order the selected records returned?
-
How do I select a record from a mysql table?
The statement below will select all columns (*) from the tablename you specify.
SELECT * FROM tablename
-
How do I select records from select records?
By using the HAVING clause. This is useful for performing functions on data already selected, rather than on the entire table.
SELECT * FROM tablename WHERE age > 21 HAVING weight < 50
-
How do I select specific records?
The statement below will select all columns (*) from the tablename you specify, where the column you specify contains the value you specify.
SELECT * FROM tablename WHERE column = ''
When specifing value, be sure that unless the values are numbers, that you quote them. You may also want to quote the value so that any quotes within the value are escaped. Here's two more examples.
SELECT * FROM tablename WHERE age > 40 && age < 50
SELECT * FROM tablename WHERE name = 'John'
-
How do I quote a value and whay should I?
Quoting value will help ensure server 500 errors are avoided. If you're querying your databases using variables, you're not always sure what the values hold, and often they do hold meta characters which need to be escaped.
Let's you're searching for family names, which can include names like O'Connor. The qoute in the name would cause an error if you where quoting the value of your test. Quoting the values using MySQL's quote function ensure all metacharacters, including quotes are properly escaped.
$VAR{name} = $DB->quote($VAR{name});
SELECT * FROM members WHERE surname = $VAR{name}
-
How I do select specific records using regular expressions?
Yes, MySQL does support regular expressions. And it also has a much simpler method where by you can select records that contain the string you're seeking. You do this by using either the LIKE or RLIKE clause.
To select records using the LIKE clause, you pad the value with a % sign on each side to act as a wildcard. The following statement :
SELECT * FROM tablename WHERE column LIKE '%Ron%'
would return records that contained 'Ronald' and 'Ronny'. Conversely, if you didn't like Ron, you could say :
SELECT * FROM tablename WHERE column NOT LIKE '%Ron%'
to return all records that did NOT contain the string 'Ron'.
RLIKE is similar to LIKE except that it takes and acts upon regular expressions. Regular expressions are a topic within themselves, so I'll just give an example here. MySQL does support the full Unix regular expression syntax.
SELECT * FROM tablename WHERE column RLIKE '^Ron.*'
This is similar to LIKE example above, but is telling MySQL to search for values that begin with 'Ron' by using the caret (^). So a record with 'Cron' would not be returned.
-
How do I select random records?
One method is to select your record or records, limit the selection to 1 or more records, then order the results randomly. All of this can be achieved with mySQL statements.
SELECT * FROM tablename WHERE live = 1 ORDER BY RAND() LIMIT 1
-
How do I select unique or distinct records?
SELECT DISTINCT colname FROM tablename
-
How do I select a limited number or records?
The LIMIT keyword takes either 1 or 2 arguments. If using just 1 argument, it will return the number or records you specify. As in :
SELECT * FROM tablename WHERE live = 1 LIMIT 1
Or, you can skip the first 20 records, and select the following 10 records, as in :
SELECT * FROM tablename LIMIT 20,10
-
How do I simply data keys returned in selections with complext column or functions names?
By using aliases.
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), '%i') AS easydate FROM tablename
-
How do I order the selected records returned?
You can order the results returned by one or more columns separated by commas. When ordering by columns, the data type is taken into account, and results are ordered by the column names in the order you define them.
For example, if you have a column named 'age' but the column is defined as a VARCHAR(255) rather than INT(11) type, the results will be ordered in such a way that the number 112 comes before the number 24, because 1 is less than 2 in the alphabet. If you're columns are well defined though, as integers, 112 is of course greater than 24 so you can expect MySQL will return the results in the expected order.
SELECT * FROM tablename WHERE age > 40 ORDER BY age
Or you could use
SELECT * FROM tablename WHERE age > 40 ORDER BY age, height, weight LIMIT 20
Which would return no more than 20 records, with people who are aged exactly 45, sorted by their height, then their weight. This would place the people weighing less, above the others of the same age.
You can also reverse the sorting using the DESC keyword. You'd need to define it with each column you're wanting sorted in descending order.
SELECT * FROM tablename WHERE age > 40 ORDER BY age DESC, height, weight DESC LIMIT 20
Page 1 Page 2
|
|
|
