Handy MySQL Commands - I

1. To login (from unix shell) use -h only if needed.

$ mysql -h hostname -u root -p
Or
$ mysql
Or
$ sudo mysql

2. Create a database on the sql server.

mysql> create database testdb;

3. List all databases on the sql server.

mysql> show databases;

4. Switch to a database.

mysql> use testdb;

5. Create table

mysql> CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

6. Insert inside table

mysql> INSERT INTO Persons (PersonID, LastName, FirstName, Address, City)
VALUES (1, 'Rajpoot', 'Ankit', 'Gadarwara', 'Bhopal');

7 To see all the tables in the db.

mysql> show tables;

8. To see database’s field formats.

mysql> describe Persons;

9.To delete a table.

mysql> drop table Persons;

10. To delete a db.

mysql> drop database tets1

11. Show all data in a table.

mysql> SELECT * from Persons;

12. Returns the columns and column information pertaining to the designated table.

mysql> show columns from Persons;

13. Show certain selected rows with the value “whatever”.

mysql> SELECT * from Persons where FirstName="Ankit";

14. Show all records containing the FirstName “Ankit” AND the PersonId 1.

mysql> SELECT * from Persons where FirstName="Ankit" AND PersonId=1;

15. Show all records not containing the FirstName “Ankit” AND the PersonId ‘1’ order by the PersonId field.

mysql> SELECT * from Persons where FirstName!="Ankit" AND PersonId=1 order by PersonId;

16. Show all records starting with the letters ‘Ank’ AND thePersonId 1.

mysql> SELECT * from Persons where FirstName like "Ank%" AND PersonId=1;

17. Use a regular expression to find records. Use “REGEXP BINARY” to force case-sensitivity. This finds any record beginning with An.

SELECT * from Persons where FirstName RLIKE "^An";

18. Show unique records.

mysql> SELECT distinct FirstName from Persons;

19. Show selected records sorted in an ascending (asc) or descending (desc).

mysql> SELECT FirstName,LastName FROM Persons ORDER BY FirstName DESC;

20. Count rows.

mysql> SELECT count(*) from Persons;

That’s it for this time! I hope you enjoyed this post. As always, I welcome questions, notes, comments and requests for posts on topics you’d like to read. See you next time! Happy Coding !!!!!