Saturday, 26 December 2015

Mysql Tutorial - Basic SQL Queries

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



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

No comments:

Post a comment