• How to search a tablename in schema
if you want to search the tablename in a schema, you have to search in information_Schema system table. it stores all table information in it
eg: i want to search the schema, if it has any table name or column name as preferences
SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name like =’preferences’ or column_name = ‘preferences’
AND TABLE_SCHEMA=’rmseast_vtcrm1′;
It outputs the all the tables which has table name or column name as “preferences”
How to get the students who are in more than one group mysql
Using aggregate function and group by , Having clause you can get the students who are in more than one group
SQL
SELECT
COUNT(*) AS cnt,student_id
FROM
student_groups
GROUP BY
student_id HAVING COUNT(*)>1
How to find the duplicate rows by comparing two columns in a table mysql
you can use the self join to retrieve the duplicate records in a table by comparing two columns,
I want to find out the students who has the same Firstname and last name
SQL:
SELECT
DISTINCT n1.id source_row,n1.firstname,n1.lastname,
n1.login , n1.password
FROM
students n1 JOIN students n2 ON
n1.firstname = n2.firstname AND
n1.lastname = n2.lastname AND
n1.id != n2.id

How to get the length of a string in a column mysql
using length([column name]) , we can get the length of string
eg: get the students whose description length is more than 50
SELECT * from students where length(description)>50
How to get the records posted in this month in mysql
Used the DATE_SUB function to get the records
select * from feed where updated > DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)
To get the last month records
select * from feed where updated between
DATE_SUB(CURRENT_DATE, INTERVAL 2 MONTH) and DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)
you can use INTERVAL 1 DAY, INTERVAL 1 WEEK, INTERVAL 2 MONTH ETC.,

how to find duplicate rows in a table based on specific column
below query returns the rows duplicate firstnames in student table
select firstname, count(firstname) as cnt from student group by firstname having cnt>1

how to concatenate all rows of a particular column in a table mysql
table name : user
columns : name, username, password, email.
generally we get this scenario when we update the data from production database to local or development database, so at that time we need to disable sending emails from application or change all email address to dummy emails. so that we can restrict the sending emails from development.
update user set email = concat(email,”_disabled”);
above sql contactenates all rows of email with _disabled

How to connect the mysql from command prompt
we should know the username/password and Host name of your mysql server
syntax
mysql -u USERNAME -h HOSTNAME -p
eg:
mysql -u sri -h localhost -p
it prompts for the password..enter the password..
done..
How to get Current Date and Time in MySQL?
we can get the datetime using mysql function now()
mysql> SELECT NOW();
o/p : 2005-08-4 19:40:00