How to search second maximum(second highest) salary value(integer)from table employee (field salary)in the manner so that mysql gets less load?
By below query we will get second maximum(second highest) salary value(integer)from table employee (field salary)in the manner so that mysql gets less load?
SELECT DISTINCT(salary) FROM employee order by salary desc limit 1 , 1 ;
(This way we will able to find out 3rd highest , 4th highest salary so on just need to change limit condtion like LIMIT 2,1 for 3rd highest and LIMIT 3,1 for 4th
some one may finding this way useing below query that taken more time as compare to above query SELECT salary FROM employee where salary < (select max(salary) from employe) order by salary DESC limit 1 ;
How To see all the tables from a database of mysql server.
mysql> show tables;
How to Show certain selected rows with the value “pcds”
mysql> SELECT * FROM tablename WHERE fieldname = “pcds”;
How To update info already in a table and Delete a row(s) from a table.
mysql> UPDATE [table name] SET Select_priv = ‘Y’,Insert_priv = ‘Y’,Update_priv = ‘Y’ where [field name] = ‘user’;
mysql> DELETE from [table name] where [field name] = ‘whatever’;
how we will Show selected records sorted in an ascending (asc) or descending (desc)
mysql> SELECT col1,col2 FROM tablename ORDER BY col2 DESC;
mysql> SELECT col1,col2 FROM tablename ORDER BY col2 ASC;
How to dump all databases for backup. Backup file is sqlcommands to recreate all db’s
# [mysql dir]/bin/mysqldump -u root -ppassword –opt >/tmp/alldatabases.sql
How to see table’s field formats or description of table
mysql> describe tablename;
How to Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server.
# /etc/init.d/mysql stop
# mysqld_safe –skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD(“newrootpassword”) where User=’root’;
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start
Use a regular expression to find records. Use “REGEXP BINARY” to force case-sensitivity. This finds any record beginning with r.
mysql> SELECT * FROM tablename WHERE rec RLIKE “^r”;
How to do login in mysql with unix shell
By below method if password is pass and user name is root
# [mysql dir]/bin/mysql -h hostname -u root -p pass