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
• ★ USE – selects the database to work with


• ★ 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


• ★ TRUNCATE TABLE – deletes all rows from a table but keeps the structure
★ RENAME TABLE – renames a 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 USE DATABASE Statement

• ★ Syntax:

				USE databasename;
			

USE DATABASE Example

• ★ After running this, any SQL commands (like SELECT, INSERT, etc.) will apply to the school database by default.

			USE school;
		

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');
			

Best Practice: Always Specify the Columns

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;
			

ALTER VS UPDATE

Feature	ALTER	UPDATE
What it does	Changes the structure of a table	Changes the data/values inside the table
Used for	Adding/removing columns, changing data types	Modifying existing rows (records)
Affects	The table design (schema)	The contents (data in rows)
Example	ALTER TABLE students ADD gpa DECIMAL(3,2);	UPDATE students SET gpa = 3.75 WHERE name = 'Alice';

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;
			

DROP vs DELETE

Concept	DROP	DELETE
What it does	Removes the entire table or database	Removes data (rows) from a table
Structure stays?	❌ No — table is gone completely	✅ Yes — table structure remains
Used for	Deleting tables, databases	Deleting specific rows or all rows
Rollback?	❌ Not recoverable (in most cases)	✅ Can be rolled back (with transactions)
Example	DROP TABLE students;	DELETE FROM students WHERE id = 1;

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;
			

SQL TRUNCATE Statement

• ☆ It removes all rows from the table very quickly.
• ☆ BUT it does NOT delete the table itself — just empties it.
• ☆ It resets the AUTO_INCREMENT counter back to 1 (important!).
• ☆ It is faster than using DELETE FROM students; because it doesn't log each row deletion individually.

TRUNCATE Table Example

			TRUNCATE TABLE students;
		

SQL RENAME Statement

RENAME TABLE Example

			RENAME TABLE old_name TO new_name;
		

RENAME a Column

			ALTER TABLE table_name CHANGE old_column_name new_column_name datatype;
		

Reference

• W3schools MySQL/