Mysql Tutorial - Basic SQL Queries

               In this post (tutorial), we will see basic sql queries which we need for database (e.g. Mysql) operations. 



1. Installing Mysql on Ubuntu

Syntax:
sudo apt-get install mysql-server

2. Getting Mysql prompt

Syntax:
mysql -u root -p

Note: Enter the Mysql root user password to get Mysql prompt.

3. Showing all users

Syntax:
select user from mysql.user;

4. Creating New User

Syntax:
create user 'username'@'localhost' identified by 'password';

5. Granting permissions to new user

Syntax:
grant all privileges on *.* to 'username'@'localhost';

flush privileges;

Note:  We can allocate specific privileges like CREATE, DROP etc.
Following is the list of privileges we can allocate:

ALL PRIVILEGES – grants all privileges to the MySQL user
CREATE – allows the user to create databases and tables
DROP - allows the user to drop databases and tables
DELETE - allows the user to delete rows from specific MySQL table
INSERT - allows the user to insert rows into specific MySQL table
SELECT – allows the user to read the database
UPDATE - allows the user to update table rows


                 Here *.* represents Database.Table. 
* is wildcard symbol which specifies all databases and all tables. 

Database.* means all tables in the database Database.

6. Exiting from Mysql prompt

Syntax:
exit 

7. Logging into mysql prompt by new user

Syntax:
mysql -u username -p

8. Removing user

Syntax:
drop user 'username'@'localhost';

9. Showing existing databases

Syntax:
show databases;

10. Creating new database

Syntax:
create database databasename;


11. Using particular Database

Syntax:
use databasename


12. Deleting database

Syntax:
drop database databasename;

13. Creating Table

Syntax:
create table tablename
(
first-column-name datatype(size) primary key,
second-column-name datatype(size),
third-column-name datatype(size),
.
.
.
nth-column-name datatype(size) 
);

Note: Datatypes are int or integer, varchar. Size is number of characters or digits. 

e.g. 
create table student
(
rollno int(5),
name varchar(20),
city varchar(10),
contactno int(11)
);

14. Showing description/details of table

Syntax: 
desc tablename;

e.g. 
desc student;

15. Showing list of tables in selected database

Syntax:
show tables; 

16. Removing Table

Syntax:
drop table tablename;

17. Inserting record (row) to table

Syntax:
insert into tablename(columnname1, columnname2, columnname3...) values('value1', 'value2', 'value3'...);

e.g. inserting record into above table student
insert into student(rollno,name,city,contactno) values(1234,'Parag','Pune',909090909);

OR

insert into student values(1234,'Parag','Pune',9090909090);

18. Updating/Altering Table definition

Syntax:

Adding new column to table

alter table tablename add columnname datatype(size);

Changing existing coulmn

alter table tablename modify columnname datatype(size);

19. Updating record from table

Syntax:
update tablename
set columnname1=value1,
      columnname2=value2,
      . 
      .
      .
     columnnamen=valuen
where columnname=value;

e.g. 
update student
set city='Mumbai'
where name='Parag';

20. Deleting records from table

Syntax:
delete from tablename where columnname='value';  

21. Showing records(data) from table

Syntax:

Selecting all records with values of all columns

select * from tablename;

e.g. 
select * from student;

Selecting all records with values of few columns

select columnname1, columnname2, ...  from tablename;


e.g.
select name,city from student;


Selecting few records with values of few columns

select columnname1, columnname2, ...  from tablename where columnname='value';

e.g. 
select name from student
where city='Mumbai';

22. AND
                    It is used to mention multiple conditions. Only those records are shown which follows all the conditions.

Syntax: 
select columnnames from tablename 
where 
columnname1=value1 AND columnname2=value2; 

23. OR
                   It is also used to mention multiple conditions. Here, only those records are shown which follow atleast one condition.

Syntax:
select columnnames from tablename
where 
columnname=value1 OR columnname2=value2;

24. BETWEEN
                  It is used show records where values are in the given range. 

Syntax:
select columnnanes from tablename
where
columnname BETWEEN value1 AND value2;

25. AS
                  It is used to mention alias of the column name.

Syntax: 
select columnname AS alias from tablename;

26. AVG()
                  It is an aggregate function. It is used to show average of numeric values of any numeric column.

Syntax:
select AVG(columnname) from tablename;

27. COUNT()
                  It is an aggregate function. It is used to give number of records where column is not null.

Syntax:
select COUNT(columnname) from tablename;

28. GROUP BY
                   It is used with aggregate functions. It is used to arrange identical data in groups.

Syntax:
select columnname, COUNT(*) from tablename
GROUP BY columnname;

e.g. 
select city, COUNT(*) from student
GROUP BY city;

29. HAVING
                   It is used with aggregate functions and GROUP BY to mention any condition or constraint.

Syntax:
select columnname, COUNT(*) from tablename
GROUP BY columnname
HAVING condition;

e.g. 
select city, COUNT(*) from student
GROUP BY city
HAVING COUNT(*)>1; 

30. DISTINCT
                  It avoids repetitive  data while showing records.

Syntax:
select DISTINCT(columnname) from tablename;

e.g. 
select DISTINCT(city) from student;

31. SUM()
                  It is an aggregate function. It returns sum of numeric values of numeric column.

Syntax: 
select SUM(columnname) from tablename;

e.g. 
select SUM(marks) from student;

32. INNER JOIN
                    It is used to combine data from multiple tables. INNER JOIN shows records for the values which are present in all the tables.

Syntax:
select columnnames from tablename1 JOIN tablename2 
ON tablename1.columnname=tablename2.columnname;

e.g. 
select * from student JOIN studentmarks
ON
student.rollno=studentmarks.rollno;

33. OUTER JOIN
                    Like INNER JOIN, it is used to combine data from different tables. Difference is that it combines records(rows) with similar data as well as dissimilar data.

Syntax:
select columnnames from tablename1 LEFT/RIGHT JOIN tablename2 
ON tablename1.columnname=tablename2.columnname;

e.g. 
select * from student LEFT JOIN studentmarks
ON
student.rollno=studentmarks.rollno;

Note:
LEFT JOIN shows columns from left table and null values for right table.

e.g. 
select * from student RIGHT JOIN studentmarks
ON
student.rollno=studentmarks.rollno;

Note:
RIGHT JOIN shows columns from right table and null values for left table.

                  

Check Other Posts on Database http://www.comrevo.com/2016/09/database.html

Next: Abbreviations in Computer Science

Previous: Basic Linux Commands with Examples