Essential SQL Commands Overview
Translated by
ScribdTranslationsEssential SQL Commands Overview
Translated by
ScribdTranslationsBasic SQL Commands
Defining how the information is stored.
CREATE DATABASEit is used to create a new empty database.
DROP DATABASEit is used to completely remove an existing database.
CREATE TABLEit is used to create a new table, where the information is stored
really.
ALTER TABLEit is used to modify an existing table.
DROP TABLE is used to completely remove an existing table.
Manipulating the data.
SELECTIt is used when you want to read (or select) your data.
INSERTit is used when you want to add (or insert) new data.
UPDATEIt is used when you want to change (or update) existing data.
DELETEit is used when you want to delete (or erase) existing data.
REPLACEit is used when you want to add or change (or replace) new or already existing data
existing.
TRUNCATE is used when you want to empty (or delete) all the data from the template.
A simple example.
CREATE DATABASE mydb;
USE mydb;
CREATE TABLE mytable ( id INT PRIMARY KEY, name VARCHAR(20) );
INSERT INTO mytable VALUES (1, 'Will');
INSERT INTO mytable VALUES (2, 'Marry');
INSERT INTO mytable VALUES (3, 'Dean');
SELECT id, name FROM mytable WHERE id = 1;
UPDATE table SET name='Willy' WHERE id=1;
SELECT id, name FROM mytable;
DELETE FROM mytable WHERE id=1;
SELECT id, name FROM mytable;
DROP DATABASE mydb;
SELECT count(1) from mytable; gives the number of records in the table
We create the database
CREATE DATABASE company
We create the People table
CREATE TABLE Persons
(
id int
Name varchar(20) NOT NULL,
Last names varchar(30) NOT NULL,
Address varchar(40) NOT NULL,
City varchar(10) NOT NULL
)
1. We insert some records:
INSERT INTO Personas
INSERT INTO Persons
VALUES ('Null', 'Marco Antonio', 'Trejo Lemus', 'Calle E 822', 'Tampico')
INSERT INTO People
VALUES ('Null', 'Martha Beatriz','Trejo Lemus','Calle E 822','Tampico')
INSERT INTO People
VALUES ('Null', 'Juana Elvira','Trejo Lemus','Calle E 822','Tampico')
INSERT INTO Persons
VALUES ('Null', 'Nora Zulma','Trejo Lemus','Calle E 822','Tampico')
INSERT INTO Persons
VALUES ('Null', 'Laura Lucero','Sobrevilla Trejo','Calle E 822','Tampico')
INSERT INTO Peoples
VALUES ('Null', 'Trinidad','Trejo Bautista','Calle E 822','Tampico')
INSERT INTO Persons
VALUES ('Null', 'Marcel Abisag','Sobrevilla Trejo','Calle E 822','Tampico')
INSERT INTO Persons
VALUES ('Null', 'Jose Abraham','Sobrevilla Trejo','Calle E 822','Tampico')
INSERT INTO Personas
VALUES ('Null', 'Samuel Salomon','Olmeda Trejo','Calle E 822','Tampico')
Many of the actions you need to perform in a database are done with SQL statements.
2. We select all records from the table
The following statement will select all records from the table 'People':
SELECT * FROM People
Below is an example of the result of the statement to the table called 'People':
This tutorial will teach you about the different statements in SQL.
Keep the following in mind: SQL statements are not case-sensitive.
3. SQL WHERE
The where clause is used to extract only the records that meet the specified criteria.
The SQL WHERE Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name operator value
Example of the WHERE clause
SELECT * FROM People WHERE LastName = 'Trejo Lemus'
SELECT * FROM Personas WHERE Name='Laura Lucero'
SELECT * FROM People WHERE id ='9'
4. AND Operator
The AND operator shows the record if the first condition and the second condition are true.
The OR operator displays the record if the first or the second condition is true
Now taking into account the following table:
To select only the people with the first name equal to Marcel Abisag and the last name equal to Sobrevilla
Trejo,
We will use the following SELECT statement:
SELECT * FROM People WHERE FirstName='Marcel Abisag' AND LastName='Sobrevilla Trejo'
The result would be:
5. OR Operator
Now we will select the people with the Name field equal to "Martha" or the Name field equal to
Elvira
We will use the following SELECT statement
SELECT * FROM Persons WHERE Name='Martha Beatriz' OR Name='Juana Elvira'
The result will be the following:
6. Combining AND & OR
You can combine AND and OR (using parentheses to form complex expressions)
Now we will select only the people with the Last Name field equal to 'Sobrevilla Trejo' AND First Name
equal to "Marcel Abisag" OR equal to "Jose Abraham"
We will use the following SELECT statement:
SQL ORDER BY
The ORDER BY keyword is used to sort the result-set.
The ORDER BY statement is used to sort a result set based on a specific column.
The ORDER BY statement is used to sort records in ascending order by default.
If you want to sort the records in descending order, use the word DESC.
7. SELECT * FROM Persons ORDER BY Name
The result will be the following:
8. ORDER BY DESC
Now we are going to select all the people from the table but displaying them in descending order.
by the field Name with the word DESC
SELECT * FROM People ORDER BY Name DESC
The result will be as follows:
UPDATE table_name
SET column1=value, column2=value,...
WHERE some_column=some_value
Note: The WHERE clause in the UPDATE syntax specifies which records will be updated. If
If you omit the WHERE clause, all records will be updated.
Now we are going to update the person "Antonio Trejo Campos" in the People table.
We will use the following SQL statements:
UPDATE Persons SET Address='Canoga Park', City='L.A.' WHERE Name='Marco
Antonio' AND LastName='Trejo Lemus'
The result will be the following:
WARNING!!! when using the UPDATE statement
If you omit the WHERE clause, all records will be updated in this way:
UPDATE Persons SET Address='Canoga Park', City='L.A.'
The result would be:
SQL DELETE Syntax
DELETE FROM table_name
WHERE some_column=some_value
Note: The WHERE clause in the DELETE syntax specifies the record or records that will be deleted.
If you omit the WHERE clause, all records will be deleted from the table.
Now we are going to delete the person "Marco Antonio Trejo Lemus" from the table People with the following
sentence
DELETE FROM People WHERE FirstName='Marco Antonio' AND LastName='Trejo Lemus'
SELECT * FROM People
The result will be as follows:
Delete all rows
It is possible to delete all the rows in a table without deleting the table itself. This means that the structure of the
table, attributes and indices will remain intact:
DELETE FROM table_name
or
DELETE * FROM table_name
Note: You must be careful when deleting records. Since you will not be able to undo what you do with this.
sentence.