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
- 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