Defining how it is stored

informationn.¶
CREATE DATABASEit is used to create a new empty database.
DROP DATABASEit is used to completely delete a database
existing.
CREATE TABLEis used to create a new table, where the information is
stores 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 data.
or already existing.
TRUNCATEit 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 my table; gives the number of records in the table

+++++++++++++

Wednesday, May 20, 2009

BASIC
SQL is a standard for accessing and managing databases.

This tutorial will show you how to manage databases in MySQL, SQL Server, MS Access.
Oracle, Sybase, DB2 and other databases

What is SQL?
SQL is a standard structured language for queries
SQL allows you to access and manage databases
SQL is a standard (ANSI American National Standards Institute)

What can SQL do?

SQL can execute queries to a database

SQL can retrieve data from a database
SQL can insert records into a database
SQL can update records in a database
SQL can delete records from a database
SQL can create new databases
SQL can create new tables in a database
SQL can create stored procedures (stored code) in a database.
SQL can create views in a database
SQL can set permissions on tables, procedures, and views.

SQL is a standard but despite being an ANSI (American National Standards) standard
There are different versions of the SQL language.

And in any case, they continue to meet the ANSI standard as these versions support the
majority of commands such as SELECT, UPDATE, DELETE, INSERT, WHERE

What is an RDBMS?

RDBMS (from English Relational Database Management System)

RDBMS (Relational Database Management System)
it is the foundation for SQL and for all modern databases
such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

Data in an RDBMS is stored in objects called tables.

A table is a related collection of entries that consists of columns.
and rows

Using SQL on your Website

To build a website that displays data from a database, you will need the following:
An RDBMS database program (MS Access, SQL Server, MySQL)
A server-side language such as PHP or ASP
SQL
HTML/CSS

To carry out these examples, download and install SQL SERVER EXPRESS by clickingHERE

SQL syntax

Tables

A database contains one or more tables. Each table is identified by a name.

(example Customers or Orders). The tables contain records (rows) with data

SQL statements

We create the database

CREATE DATABASE company

We create the table People

CREATE TABLE [Link]

(
P_id int PRIMARY KEY IDENTITY,
Name nchar(20) NOT NULL,
Last names nchar(30) NOT NULL,
Address nchar(40) NOT NULL,
City char(10) NOT NULL
)
GO

We inserted some records:

INSERT INTO People

Marco Antonio
INSERT INTO Persons
Martha Beatriz
INSERT INTO People
Juana Elvira
INSERT INTO Persons
Nora Zulma
INSERT INTO Persons
Laura Lucero
INSERT INTO Persons
VALUES ('Maria de la luz','Trejo Campos','Calle E 822','Tampico')
INSERT INTO Persons
VALUES ('Trinidad','Trejo Bautista','Calle E 822','Tampico')
INSERT INTO People
Marcel Abisag
INSERT INTO Persons
VALUES ('Jose Abraham','Sobrevilla Trejo','Calle E 822','Tampico')
INSERT INTO Persons
Samuel Salomon

Many of the actions you need to perform on a database are done with statements.
SQL

We select all the records from the table

The following statement will select all records from the table 'Persons':

SELECT * FROM Persons

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.
lowercase

Semicolon after SQL statements

Some database systems require a semicolon at the end of each SQL statement.

The semicolon is a standard that terminates each SQL statement in database systems.
that enable more than one SQL statement to be executed in the same call to the server

SQL DML and DLL

SQL can be divided into two parts: The Data Manipulation Language (DML) and the
Data Definition Language (DDL)

The DML part of SQL for query and update commands are:

SELECT - extracts data from a database

UPDATE - updates data in a database
DELETE - deletes data from a database
INSERT INTO - inserts data into a database

SQL DLL that allows databases and tables to be created or deleted.

Also define indexes (keys), specify links between tables, and impose relationships between them.
tables.

The DDL part of SQL, the most important DDL statements in SQL are:

CREATE DATABASE - create a new database

ALTER DATABASE - modifies a database
CREATE TABLE - create a new table
ALTER TABLE - modifies a table
DROP TABLE - deletes a table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index

The SELECT statement

The SELECT statement is used to select data from a database.

The result is stored in a temporary table called result-table.

The SQL syntax of the SELECT statement is:

SELECT column_name(s)
FROM table_name

2) SELECT * FROM table_name

Example 1

SELECT column_name(s)
FROM table_name

SELECT FirstName, LastName, City

FROM Personas
Example 2

SELECT * FROM table_name

SELECT * FROM table_name

Navigation in a results table

Many database systems allow navigation in the result table

programming functions such as: Move-To-First, Get-Record-Content, Move-To-
Next-Record, etc.

Programming functions like these are not part of this tutorial. To learn how to access
data with the function call, await my next ADO and PHP tutorial.

The SELECT DISTINCT statement

Within a table, some columns may contain duplicate values. This is not a
problem, sometimes you will want to list only the different (distinct) values in the table

The word DISTINCT can be used as a key to return only the values
different

SQL SELECT DISTINCT Syntax

SELECT DISTINCT column_name(s)

FROM table_name

Example:

SELECT DISTINCT LastNames

FROM Personas

Below is the result of the DISTINCT statement on the table 'People':

SELECT DISTINCT City FROM People

Below is the result of the DISTINCT statement on the table 'Persons':

SQL WHERE

The where clause is used to extract only the records that meet the criterion.
specified

The SQL WHERE Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name operator value

Example of the WHERE clause

SELECT *
FROM Personas
WHERE LastNames = 'Trejo Lemus'
Quotes in text fields

SQL uses single quotes for text values (many database management systems handle
data accept double quotes). For SQL, text values must be enclosed in quotes
simple

Numerical values should not be placed in quotes.

For text values:

This is the correct way:

SELECT *
FROM Personas
WHERE Name='Lucero'

This is the wrong way:

SELECT *
FROM Personas
WHERE Name=Lucero

For numerical values:

This is the correct way:

SELECT *
FROM Personas
WHERE P_id = 9

This is the incorrect way:

SELECT *
FROM Personas
WHERE P_id = '9'

Allowed operators in the WHERE clause

With the WHERE clause, the following operators can be used:
SQL Operators AND and OR

The AND and OR operators are used to filter records based on more than one condition.

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 Persons

WHERE Name='Marcel Abisag'
AND LastName='Sobrevilla Trejo'

The result would be:

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 People

WHERE Name='Martha Beatriz'
OR Name='Juana Elvira'

The result will be the following:

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
Name equal to 'Marcel Abisag' OR equal to 'Jose Abraham'

We will use the following SELECT statement:

SELECT * FROM People WHERE

Sobrevilla Trejo
AND (Name='Marcel Abisag' OR Name='Jose Abraham')

The result will be the following:

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 by a column.
specify

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

SQL ORDER BY syntax

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC DESC

Now we are going to select all the people from the table, but showing them in order by the
Name field

We will use the following SELECT statement:

SELECT * FROM People

ORDER BY Name

The result will be the following:

ORDER BY DESC

Now we are going to select all the people from the table but showing them in an order.
descending by the field Name with the word DESC

We use the following SELECT statement:

SELECT * FROM People

ORDER BY Name DESC

The result will be the following:

SQL INSERT INTO statement

The INSERT INTO statement is used to insert a record or row into a table.
SQL INSERT INTO Syntax
It is possible to write the INSERT INTO statement in two ways.

The first form does not specify the names of the columns where the data will be inserted.
only the values:

INSERT INTO table_name

VALUES (value1, value2, value3,...)

The second form specifies the names of the columns and the values inserted.

INSERT INTO table_name (column1, column2, column3,...)

VALUES (value1, value2, value3,... )

Example INSERT INTO

Given that we have the following table People:

We will insert a new record as follows:

INSERT INTO Personas

Martha

We would show the result with the statement SELECT * FROM Personas and it would be as follows:

Insert data only in specified columns

It is possible to add data in specific columns
The following SQL statements will add a new row, but will only add data in the
columns Name and Surname

INSERT INTO Persons (Name, Last Names)

VALUES ('Antonio', 'Trejo Campos')

To show the result with the statement: SELECT * FROM Personas

It will show the following:

SQL UPDATE Statement

The UPDATE statement is used to update records in a table.

SQL UPDATE Syntax

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 affected.
updated. 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 Personas
Canoga Park
WHERE Name='Antonio' AND LastName='Trejo Campos'

The result will be the following:

CAUTION!!! when using the UPDATE statement

If you omit the WHERE clause, all records will be updated in this way:

UPDATE Personas
Canoga Park

The result would be:

SQL DELETE statement

The DELETE statement is used to delete records or rows in a table.

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 Persons table with the
next statement:

DELETE FROM People

WHERE Name='Marco Antonio' AND Surnames='Trejo Lemus'
SELECT * FROM People

The result will be the following:

Delete all rows

It is possible to delete all the rows in a table without deleting the table itself. This means that the
the structure of the table, attributes and indexes 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.

APPENDIX 1
The following code will create the database on the SQL EXPRESS server.

1.- Click on Start --> All Programs --> Microsoft SQL SERVER 2008 --> SQL
Server Management Studio

2.- Click on the CONNECT button

3.- Click on New Query (located in the top left margin)

Select and Copy the following 'GENERATOR CODE

DATABASE company:
______________________________________________________

USE master
if exists (select * from sysdatabases where name='company')
begin
raiserror('The database exists; deleting it....',0,1)
DROP database empresa
end
GO
raiserror('Creating database company....',0,1)
go
CREATE DATABASE company
GO
USE company
GO
CREATE TABLE Personas(
P_id int PRIMARY KEY IDENTITY,
Name nchar(20) NOT NULL,
Last names nchar(30) NOT NULL,
Address nchar(40) NOT NULL,
City nchar(10) NOT NULL
GO
GO
INSERT INTO Personas VALUES ('Marco Antonio', 'Trejo Lemus', 'Calle E 822', 'Tampico')
INSERT INTO Personas VALUES ('Martha Beatriz','Trejo Lemus','Calle E 822','Tampico')
Juana Elvira
INSERT INTO Persons VALUES ('Nora Zulma', 'Trejo Lemus', 'Calle E 822', 'Tampico')
INSERT INTO People VALUES ('Laura Lucero', 'Sobrevilla Trejo', 'Calle E 822', 'Tampico')
INSERT INTO Persons VALUES ('Maria de la Luz', 'Trejo Campos', 'Calle E 822', 'Tampico')
Trinidad
INSERT INTO People VALUES ('Marcel Abisag','Sobrevilla Trejo','E Street
822', 'Tampico') INSERT INTO Personas VALUES ('Jose Abraham', 'Sobrevilla Trejo', 'Calle E
822','Tampico')INSERT INTO People VALUES ('Samuel Salomon','Olmeda Trejo','E Street
Tampico
GO
select * from Persons
quit

4.- Paste the code

5.- Execute the SQL code by clicking on the !Execute option that is displayed.
continuation:
Publishof forMarco Antonio Trejo Lemusin19:188 comments:

Monday, May 18, 2009

ADVANCED
SQL TOP Clause

The TOP clause is used to specify the number of records that exist.
Can you verify the length of the tables with thousands of records, returning the number of
records

Note: Not all databases support the TOP clause.

SQL Server Syntax

SELECT TOP percentage number column_name(s)

FROM table_name

Now we will select only the first two records from the table shown below:

We will use the following SELECT statements:

SELECT TOP 2 * FROM People

The result will be as follows:

SQL TOP PERCENT statement

Now we will select only 50% of the records in the table

We will use the following SELECT statements:

SELECT TOP 50 PERCENT * FROM People

The result is shown below in the table:

SQL LIKE operator

SQL LIKE Operator

The LIKE operator is used in a WHERE clause to search for a pattern in a column.

LIKE syntax
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern

Example of LIKE operator

In the table "People":

We are going to look for the people who live in the city of Tampico that start with 'Ta' of the
table in question

We will use the following SELECT statement:

SELECT * FROM Persons

WHERE City LIKE 'Ta%'

The sign '%' can be used to define wildcards (letters that are missing in the pattern of
search) both before or after the search pattern

The result would be the following:

Now we are going to select the people who live in the city that starts with a 'T' from the
people table

We will use the following SELECT statement:

SELECT * FROM People

WHERE City LIKE '%T'

The result would be the following:

Now we will select the people who live in the city that contains the pattern 'tam'.
the table of people

We will use the following SELECT statement:

SELECT * FROM People

WHERE City LIKE '%tam%'

The result would be the following:

It is also possible to select the people who live in the city that do not contain the pattern.
tamp from the table persons, using the keyword NOT

We will use the following SELECT statement:

SELECT * FROM Persons

WHERE City NOT LIKE '%tamp%'

The result would be the following:

SQL Wildcards

SQL wildcards can be used in data retrieval from a database.

they can replace one or more characters when searching for data

Wildcards must be used with the LIKE operator

The following wildcards can be used with SQL:

Using the wildcard %
Now we will select the people who live in the city of Tampico whose names start with "Ta".
from the table People

We will use the following SELECT statement:

SELECT * FROM People

WHERE City LIKE 'Ta%'

Now we will look for the people who live in the city that contains the pattern 'ico' from the table.
People

We will use the following SELECT statement:

SELECT * FROM People

WHERE City LIKE '%ico%'

Using the _ Wildcard

Now we will select the people whose first name starts with any character.
followed by "Ma" from the table People

We will use the following SELECT statement:

SELECT * FROM People
WHERE Name LIKE '_ar'