SQL QUERY
SQL stands for Structured Query Language, it is used for accessing and manipulating databases.
Using SQL one can perform more actions in a database.Such as insert, update, select and delete a records from the database.
Essential SQL queries
1. To create a database in mysql
Create database is used to create the database.
Syntax
CREATE DATABASE database_name;
Example
CREATE DATABASE first_db;
2. To create a table in mysql
Create table is used to create a table in a database
Syntax
CREATE TABLE table_name(
columnName1 datatype(size),
columnName2 datatype(size)
);
Note:
- column_name parameter specifies the name of the column.
- datatype parameter specifies different types of data type(i.e., varchar, int, enum, date, etc).
- size parameter specifies the maximum length of the column.
Example
CREATE TABLE tbl_user(
id int NOT NULL,
username varchar(20),
age int(3),
place varchar(30),
PRIMARY KEY(id)
);
Here id is a primary key, so it increments id by 1
3. To insert a record in a table
Insert query is used to insert a new record in a table.
Syntax
There are two ways to insert a record into the database.
First way, is to insert a data by specifying both the column name and values in a query.
INSERT INTO table_name (columnName1, columnName2) VALUES (value1,value2,value3);
Second way,not specifying a column name but only specifying the values.
INSERT INTO table_name VALUES(value1,value2,value3);
In both way, we can insert the records in a table.
Example
To insert a username, age and place, use the sql insert query.
INSERT INTO tbl_user (username,age,place) VALUES (‘joe’, ’23’, ‘chennai’);
INSERT INTO tbl_user VALUES (‘joe’, ’23’, ‘chennai’);
4. To update/Edit a record in a database
Update query is used to update the existing records in a table.
Syntax
UPDATE table_name SET column1=value1, column2=value2 WHERE some_column=some_value;
Example
UPDATE tbl_login SET username=’John’ age=’24’ where place=’chennai’;
Note: WHERE clause is used to identify which record should be update. If we omit the WHERE clause, all the records will be updated.
5. To select the records from the database
Select query is used to select the records from the table.
Syntax
Two ways to select the record.One is to select the specified column and another one is to select all the records from the table.
SELECT columnName1, columnName2 FROM table_name;
SELECT * FROM table_name;
Example
SELECT username, age from tbl_user;
SELECT * from tbl_user;
6. Delete a record
Delete query is used to delete a rows from the table.
Syntax
DELETE FROM table_name WHERE some_column=some_value;
Another way to delete a rows from the table
DELETE * FROM table_name; (0r) DELETE FROM table_name;
Example
DELETE FROM tbl_user where username=”john”;
DELETE * FROM tbl_user; (0r) DELETE FROM tbl_user;
It deletes entire rows in the table.
7. Using Where clause in SQL
WHERE clause is used to extract only those records which satisfy the condition.
Syntax
SELECT columnName1, columnName2 from table_name WHERE some_column operator some_value;
Example
SELECT username,age from tbl_user WHERE place=”chennai”;
Note:Operators you may use >, <, >=, <=, <>, BETWEEN, LIKE, IN
SELECT * FROM tbl_user WHERE age>=23;
8. Alter table
Alter table statement is used to add, modify column in an existing table
Syntax
To add a column name in a table, use the following syntax
ALTER TABLE table_name ADD column_name data_type;
To change the data type of a column
ALTER TABLE table_name MODIFY COLUMN column_name data_type;
Example
ALTER TABLE tbl_user ADD phone bigint(11);
9. Truncate Table
Truncate table is used to delete the data inside the table.
Syntax
TRUNCATE TABLE table_name;
Example
TRUNCATE TABLE tbl_user;
10. Drop Table
Drop table statement is used to delete a table.
Syntax
DROP TABLE table_name;
Example
DROP TABLE tbl_user;