mysql sample select queries

User Rating: / 0
PoorBest 

The select statement has to be one of the most useful statements in SQL. The queries mentioned here can be used across the board with most of the popular databases out there including my personal favorite, mysql. SELECT * FROM tableName #returns all columns SELECT columnName FROM tableName #returns a specific columnName SELECT COUNT(*) FROM tableName #Returns the number of rows SELECT SUM(columnName) FROM tableName #Returns the sum of all the values in a column. Pre: Column should be numeric. SELECT DISTINCT columnName FROM tableName #Disregards any duplicate values that might exist and returns only unique data field instances. SELECT * FROM tableName WHERE condition #Returns all rows that match the condition. #the condition can be a constraint, a comparison, a wildcard (%) etc... SELECT * FROM tableName WHERE BINARY condition #Returns all rows that match the condition. #The condition in this case is "Case Sensitive" SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id #Join table1 and table2 on the id column, which in this case is the primary key in table1 and the foreign key in table2. This query will return all the rows in both tables. SELECT table1.* FROM table1 INNER JOIN table2 ON table1.id = table2.id #Same as the previous query expect that only data from table1 is returned in the result set SELECT LAST_INSERT_ID() as new_id #Returns the last created row's system ID in a new virtual column called "new_id" SELECT MAX(columnName) as max_col #Returns the maximum value of a column as max_col #MIN() does the opposite. SELECT * FROM tableName ORDER BY columnName DESC #Sorts the result set on columnName. Flags: ASC, DESC SELECT * FROM tableName LIMIT 10,20 #Return first 20 rows after row number 10. #This is useful for displaying a large result set with pagination. #For more information on paging look for an article on pagination in the PHP section. Comments Search Only registered users can write comments!

Last Updated ( Sunday, 22 June 2008 )

 

< Prev