18 Feb
For a beginner there is a list of all basic mysql commands. If you are working on command prompt or shell then you should have to learn these basics. I am listing some of basic not all which helps to handle most of operation.
To login from Linux shell you can use
mysql -h hostname -u root -p
for window machine
[mysql dir]/bin/mysql -h hostname -u root -pCreate a database on the sql server
mysql> create database [databasename];
List all databases on the sql server
mysql> show databases;
Switch to a database
mysql> use [db name];
To see all the tables in the database
mysql> show tables;
To see database’s field formats
mysql> describe [table name];
To delete a database
mysql> drop database [database name];
To delete a table
mysql> drop table [table name];
Show all data in a table
mysql> SELECT * FROM [table name];
Returns the columns and column information pertaining to the designated table
mysql> show columns from [table name];
Show certain selected rows with the value “whatever”
mysql> SELECT * FROM [table name] WHERE [field name] = “whatever”;
Show all records containing the name “Rajeev” AND the phone number ’988′
mysql> SELECT * FROM [table name] WHERE name = “Rajeev” AND phone_number = ’988′;
Show all records not containing the name “Rajeev” AND the phone number ’988′ order by the phone_number field
mysql> SELECT * FROM [table name] WHERE name != “Rajeev” AND phone_number = ’988′ order by phone_number;
Show all records starting with the letters ‘Rajeev’ AND the phone number ’988′
mysql> SELECT * FROM [table name] WHERE name like “Rajeev%” AND phone_number = ’988′;
Show all records starting with the letters ‘Rajeev’ AND the phone number ’988′ limit to records 1 through 5
mysql> SELECT * FROM [table name] WHERE name like “Rajeev%” AND phone_number = ’988′ limit 1,5;
Use a regular expression to find records. Use “REGEXP BINARY” to force case-sensitivity. This finds any record beginning with a
mysql> SELECT * FROM [table name] WHERE rec RLIKE “^a”;
Show unique records
mysql> SELECT DISTINCT [column name] FROM [table name];
Show selected records sorted in an ascending (asc) or descending (desc)
mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;
Return number of rows
mysql> SELECT COUNT(*) FROM [table name];
Sum column
mysql> SELECT SUM(*) FROM [table name];
Join tables on common columns
mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;
Creating a new user
- Login as root
mysql -u root -p
- Switch to the MySQL db
use mysql;
- Insert the USER
mysql> INSERT INTO user (Host,User,Password) VALUES(‘%’,’username’,PASSWORD(‘password’));
- Update privileges
mysql> flush privileges;
Change a users password from unix shell
> mysqladmin -u username -h hostname -p password ‘new-password’
Change a users password from MySQL prompt
- Login as root
mysql -u root -p
- Set the password
mysql> SET PASSWORD FOR ‘user’@’hostname’ = PASSWORD(‘passwordhere’);
- Update Privileges
mysql> flush privileges;
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.
Steps are :-
# /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
Set a root password if there is on root password
# mysqladmin -u root password newpassword
Update a root password
# mysqladmin -u root -p oldpassword newpassword
I am a software engineer who specializes in Internet applications. I have worked with a wide variety of technologies and programming languages to open source LAMP environments. I have more than 6 years of object-oriented programming experience and am highly proficient in ActionScript, PHP, MYSQL, JavaScript, Jquery and a multitude of other technologies used in modern web applications.
Latest posts by Rajeev Achra (see all)
- Jquery webcam plugin - June 19, 2016
- How To Add and Delete Users on a CentOSServer - June 5, 2016
- How To Set Up vsftpd on CentOS 6 - June 5, 2016