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.
Next: Abbreviations in Computer Science
Previous: Basic Linux Commands with Examples
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