0

important MYSQL syntaxes or query in php program

mysql

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;

 

Filed in: MYSQL

Recent Posts

Bookmark and Promote!

Leave a Reply

Submit Comment
© 2017 Expert Shout. All rights reserved. XHTML / CSS Valid.