MySQL SQL

• ★ SQL is the standard language for dealing with Relational Databases.
• ★ SQL is used to insert, search, update, and delete database records.
• ★ SQL stands for Structured Query Language

How to Use SQL

Most Important SQL Commands

• ★ CREATE DATABASE - creates a new database
• ★ DROP DATABASE - deletes a database


• ★ CREATE TABLE - creates a new table
• ★ DROP TABLE - deletes a table
• ★ ALTER TABLE - modifies a table


• ★ INSERT INTO - inserts new data into a database table
• ★ UPDATE - updates data in a database table
• ★ DELETE - deletes data from a database table
• ★ SELECT - extracts data from a database table

SQL CREATE DATABASE Statement

• ★ The CREATE DATABASE statement is used to create a new SQL database.
• ★ Syntax:

				CREATE DATABASE databasename;
			

CREATE DATABASE Example

• ★ The following SQL statement creates a database called "testDB":

				CREATE DATABASE testDB;
			

• ★ Once a database is created, you can check it in the list of databases with the following SQL command: SHOW DATABASES;

SQL DROP DATABASE Statement

• ★ The DROP DATABASE statement is used to drop an existing SQL database.
• ★ Syntax:

				DROP DATABASE databasename;
			

DROP DATABASE Example

• ★ The following SQL statement drops the existing database "testDB":

				DROP DATABASE testDB;
			

SQL CREATE TABLE Statement

• ★ The CREATE TABLE statement is used to create a new table in a database.
• ★ Syntax:

				CREATE TABLE table_name (
				    column1 datatype,
				    column2 datatype,
				    column3 datatype,
				   ....
				);
			

CREATE TABLE Example

• ★ The following example creates a table called "Persons" that contains five columns: PersonID, LastName, FirstName, Address, and City:

				CREATE TABLE Persons (
				    PersonID int,
				    LastName varchar(255),
				    FirstName varchar(255),
				    Address varchar(255),
				    City varchar(255)
				);
			

• ★ The PersonID column is of type int and will hold an integer.
• ★ The LastName, FirstName, Address, and City columns are of type varchar and will hold characters, and the maximum length for these fields is 255 characters.

SQL DROP TABLE Statement

• ★ The DROP TABLE statement is used to drop an existing table in a database.
• ★ Syntax:

				DROP TABLE table_name;
			

DROP TABLE Example

• ★ The following SQL statement drops the existing table "Shippers":

				DROP TABLE Shippers;
			

SQL ALTER TABLE Statement

• ★ The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
• ★ ALTER TABLE - ADD Column

				ALTER TABLE table_name
				ADD column_name datatype;
			

• ☆ The following SQL adds an "ZipCode" column to the "Persons" table:

        ALTER TABLE Persons
        ADD ZipCode varchar(255);
			

• ★ ALTER TABLE - DROP COLUMN

				ALTER TABLE table_name
				DROP COLUMN column_name;
			

• ☆ The following SQL deletes the "ZipCode" column from the "Persons" table:

				ALTER TABLE Persons
				DROP COLUMN ZipCode;
			

• ★ ALTER TABLE - MODIFY COLUMN

				ALTER TABLE table_name
				MODIFY COLUMN column_name datatype;
			

• ☆ The following SQL statement modifies the "PersonID" columns from int to characters type:

        ALTER TABLE Persons
        MODIFY COLUMN PersonID varchar(255);
				

SQL INSERT INTO Statement

• ★ The INSERT INTO statement is used to insert new records in a table.
• ★ It is possible to write the INSERT INTO statement in two ways:
• ☆ 1. Specify both the column names and the values to be inserted:

				INSERT INTO table_name (column1, column2, column3, ...)
				VALUES (value1, value2, value3, ...);
			

• ☆ If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table.

					INSERT INTO table_name
					VALUES (value1, value2, value3, ...);					
				

INSERT INTO Example

• ★ The following SQL statement inserts a new record in the "Persons" table:

      INSERT INTO Persons (PersonID, LastName, FirstName, Address, City)
      VALUES ('1234', 'Lovalace', 'Ada', '1515 Bluebird', 'Murfreesboro');
			

• ★ The following SQL statement inserts a new record in the "Persons" table:

      INSERT INTO Persons
      VALUES ('1235', 'Moreno', 'Antonio', '120 Hanover St.', 'Smyrna');
			

SQL UPDATE Table

• ★ The UPDATE statement is used to modify the existing records in a table.
• ★ Syntax:

        UPDATE table_name
        SET column1 = value1, column2 = value2, ...
        WHERE condition;
			

UPDATE Table Example

• ★ The following SQL statement updates the first person (PersonID = 1234) with a new adress and a new city.

	UPDATE Persons
        SET Address = '835 73rd Ave', City = 'Medina'
        WHERE PersonID = 1234;
			

SQL DELETE Statement

• ★ The DELETE statement is used to delete existing records in a table.
• ★ Syntax:

        DELETE FROM table_name WHERE condition;
			

DELETE Example

• ★ The following SQL statement deletes the person Antonio Moreno from the "Persons" table:

			DELETE FROM Persons WHERE PersonID='1235';
			

• ★ The following SQL statement deletes all rows in the "Persons" table, without deleting the table:

				DELETE FROM Persons;
			

SQL SELECT Statement

• ★ The SELECT statement is used to select data from a database.
• ★ Syntax:

				SELECT column1, column2, ...
				FROM table_name;
			

• ★ If you want to select all the fields available in the table, use the following syntax:

				SELECT * FROM table_name;
			

SELECT Columns Example

• ★ The following SQL statement selects the "FirstName", "LastName" and "City" columns from the "Persons" table:

				SELECT FirstName, LastName, City FROM Persons;
			

• ★ The following SQL statement selects all the records in the "Persons" table:

				SELECT * FROM Persons;
			

Reference

• W3schools MySQL/