In this article, basic SQL queries are described to create, update, drop, and perform other functions on a database.
Open a terminal by pressing Ctrl+Alt+T on Ubuntu or a command prompt (type cmd in the search box) on Windows and type $ mysql.
To create a database
>CREATE DATABASE database_name;
For example,
>CREATE DATABASE testdb;
To drop a database
>DROP DATABASE testdb;
To create a table in a database
>USE database_name;
>CREATE TABLE table_name;
For example,
>CREATE TABLE students;
To insert values in a table
>INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value, ...);
For example,
>INSERT INTO students (StdID, StdName, StdClass, StdAddress)
VALUES (MG20615, John, IX, 2311 North Los Robles Avenue Pasadena California USA);
You can add as many columns as you want to add to the table. If you want to add values in all columns of a table, then there is no need to specify column names, you can only insert values as shown below.
>INSERT INTO table_name
VALUES (value1, value2, value, ...);
To drop a table
>DROP TABLE table_name;
To delete all data inside a table:
>TRUNCATE TABLE table_name;
It will all data inside the table but not the data.
To alter table
To add a column
>ALTER TABLE table_name
ADD column_name datatype;
For example,
>ALTER TABLE students
ADD StdEmail varchar(255);
To drop a column from a table
>ALTER TABLE table_name
DROP column_name;
To alter a column
For SQL Server/MS Access:
>ALTER TABLE table_name
ALTER COLUMN column_name datatype;
For MySQL/Oracle (version <10G):
>ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
To retrieve data from a database
>SELECT column1, column2, column3, ...
FROM table_name;
To select all data:
>SELECT * FROM table_name;
To select only distinct values (no duplicate records)
>SELECT DISTINCT column1, column2, column3, ...
FROM table_name;
Retrieve data with a condition
>SELECT column1, column2, column3, ...
FROM table_name
WHERE condition;
For example,
>SELECT * FROM students
WHERE StdName='John';
In the above example, we have used the ‘=’ operator. You can use other operators such as ‘>’, ‘<‘, ‘>=’, ‘<=’, IN, LIKE, and ‘BETWEEN’ depending upon your condition.
To update a table with a condition
>UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
For example,
>UPDATE students
SET StdName = 'John Smith'
WHERE StdID = MG20615;
To delete record in a table
>DELETE FROM table_name
WHERE condition;
For example,
>DELETE FROM students
WHERE StdName = 'John Smith';
To delete all rows from a table
>DELETE FROM table_name;
It will delete all records from the table without deleting the table itself.
These are some basic SQL queries. More queries will be described in the upcoming articles.
