1 / 100

SQL

SQL. From: http://www.w3schools.com/sql. SQL. It is a standard language for accessing and manipulating databases MySQL , SQL Server, Access, Oracle, Sybase, DB2, and others SQL stands for Structured Query Language SQL is an ANSI (American National Standards Institute) standard

urbano
Télécharger la présentation

SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL From: http://www.w3schools.com/sql

  2. SQL • It is a standard language for accessing and manipulating databases • MySQL, SQL Server, Access, Oracle, Sybase, DB2, and others • SQL stands for Structured Query Language • SQL is an ANSI (American National Standards Institute) standard • There are many variations and different systems have their own extensions • But the major commands are same

  3. SQL • SQL include • Data Manipulation Language (DML) • Data Definition Language (DDL) • The query and update commands for DML • SELECT: extracts data from a database • UPDATE: updates data in a database • DELETE: deletes data from a database • INSERT INTO: insert new data into a database • DDL: permits database tables to be created or deleted, define indexes (keys), impose constraints between tables. Some important statements: • CREATE DATABASE: creates a new database • ALTER DATABASE: modifies a database • CREATE TABLE: creates a new table • ALTER TABLE: modifies a table • DROP TABLE: deletes a table • CREATE INDEX: creates an index • DROP INDEX: deletes an index

  4. Create Persons Table Persons Table CREATE DATABASE test; CREATE TABLE persons ( P_Id INT, LastName VARCHAR(25), FirstName VARCHAR(25), Address VARCHAR(25), City VARCHAR(15), PRIMARY KEY (P_Id) ); INSERT INTO persons VALUES (1, 'Hansen', 'Ola', 'Timoteivn10', 'Sandnes'); INSERT INTO persons VALUES (2, 'Svendson', 'Tove', 'Borgvn23','Sandnes'); INSERT INTO persons VALUES (3, 'Pettersen', 'Kari', 'Storgt20', 'Stavanger');

  5. Aqua Data Studio • You can use either “go” or “/” to replace the “;” so that it can process multiple SQLs at the same time. • More info: http://www.aquafold.com/support_faq.html#commands select * from t1 / select * from t2 / select * from t3 / select * from t1 select * from t2 select * from t3 select * from t1 goselect * from t2 goselect * from t3go

  6. SQL Basic

  7. SELECT • SELECTis used to select data from a database • The result is stored in a result table, called the result-set • SQL is not case sensitive • SELECT syntax SELECT column_name(s) FROM table_name; SELECT * FROM table_name;

  8. SELECT Persons Table SELECT LastName, FirstName FROM Persons; SELECT *FROM Persons;

  9. SELECT DISTINCT SELECT DISTINCT column_name(s) FROM table_name; SELECT DISTINCT city FROM persons;

  10. WHERE clause SELECT column_name(s) FROM table_name WHERE column_name operator value; SELECT * FROM persons WHERE city=‘Sandnes’;

  11. WHERE Clause • Text values should be quoted by single quotes or double quotes • Numeric values do not need to be enclosed in quotes SELECT * FROM persons WHERE city=‘Sandnes’; Or SELECT * FROM persons WHERE city=“Sandnes”; Or SELECT * FROM persons WHERE P_Id=1;

  12. WHERE Clause

  13. AND or OR • AND, OR operators are used to filter records based on more than one condition • AND=both the first and the second conditions is true • OR=either the first or the second condition is true

  14. AND or OR SELECT * FROM persons WHERE firstname=‘Tove’ AND lastname=‘Svendson’; SELECT * FROM persons WHERE firstname=‘Tove’ OR firstname=‘Ola’; SELECT * FROM persons WHERE lastname=‘Svendson’ AND (firstname=‘Tove’ OR firstname=‘Ola’);

  15. ORDER BY • The ORDER BY keyword is used to sort the result-set by a specified column • It sorts the records in ascending order by default • Use DESC for a descending order SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC;

  16. ORDER BY INSERT INTO persons VALUES (4, ‘Nilsen’, ‘Tom’, ‘Vingvn23', 'Stavanger'); SELECT * FROM Persons ORDER BY lastname; SELECT * FROM Persons ORDER BY lastname DESC;

  17. INSERT INTO • Use to insert new records in a table INSERT INTO table_name VALUES (value1, value2, value3,…); INSERT INTO table_name (column1, column2, column3, … VALUES (value1, value2, value3,…); INSERT INTO persons VALUES (4, ‘Nilsen’, ‘Tom’, ‘Vingvn23', 'Stavanger'); INSERT INTO persons (P_Id, lastname, firstname) VALUES (5, ‘Tjessem’, ‘Jakob’);

  18. UPDATE • Update records in a table UPDATE table_name SET column=value, column2=value2,… WHERE some_column=some_value; UPDATE Persons SET Address=‘Nissestien 67’, city=‘Sandnes’ WHERE lastname=‘Tjessem’ AND firstname=‘Jakob’; Warning: if you forget to add WHERE clause, all the address and city will be set to ‘Nissestien 67’ and ‘Sandnes’.

  19. DELETE statement • Used to delete records in a table DELETE FROM table_name WHERE some_column=some_value; DELETE FROM persons WHERE lastname=‘Tjessem’ AND firstname=‘Jakob’; DELETE FROM table_name; Or DELETE * FROM table_name;

  20. Test Create this Customers table and do the following SQL queries

  21. TEST CREATE TABLE customers ( CompanyName VARCHAR(100), ContactName VARCHAR(100), Address VARCHAR(100), City VARCHAR(50) ); INSERT INTO customers VALUES ('AlfredsFutterkiste', 'Maria Anders', 'Obere Str. 57', 'Berlin'); INSERT INTO customers VALUES ('Berglundssnabbköp', 'Christina Berglund', 'Berguvsvägen 8','Luleå'); INSERT INTO customers VALUES ('Centro comercialMoctezuma', 'Francisco Chang', 'Sierras de Granada 9993', 'México D.F.'); INSERT INTO customers VALUES ('Ernst Handel', 'Roland Mendel', 'Kirchgasse 6', 'Graz'); INSERT INTO customers VALUES ('FISSA Fabrica Inter. Salchichas S.A.', 'Diego Roel', 'C/Moralzarzal, 86 ', 'Madrid'); INSERT INTO customers VALUES ('Galería del gastrónomo', 'Eduardo Saavedra', 'Rambla de Cataluña, 23', 'Barcelona'); INSERT INTO customers VALUES ('Island Trading', 'Helen Bennett', 'Garden House Crowther Way', 'Cowes'); INSERT INTO customers VALUES ('Königlich Essen', 'Philip Cramer', 'Maubelstr. 90', 'Brandenburg'); INSERT INTO customers VALUES ('Laughing Bacchus Wine Cellars', 'YoshiTannamuri', '1900 Oak St.', 'Vancouver'); INSERT INTO customers VALUES ('MagazziniAlimentariRiuniti', 'Giovanni Rovelli', 'Via Ludovicoil Moro 22', 'Bergamo'); INSERT INTO customers VALUES ('North/South', 'Simon Crowther', 'South House 300 Queensbridge', 'London'); INSERT INTO customers VALUES ('Paris spécialités', 'Marie Bertrand', '265, boulevard Charonne', 'Paris'); INSERT INTO customers VALUES ('Rattlesnake Canyon Grocery', 'Paula Wilson', '2817 Milton Dr.', 'Albuquerque'); INSERT INTO customers VALUES ('Simons bistro', 'Jytte Petersen', 'Vinbæltet 34', 'København'); INSERT INTO customers VALUES ('The Big Cheese', 'Liz Nixon', '89 Jefferson Way Suite 2', 'Portland'); INSERT INTO customers VALUES ('Vaffeljernet', 'Palle Ibsen', 'Smagsløget 45', 'Århus'); INSERT INTO customers VALUES ('WolskiZajazd', 'ZbyszekPiestrzeniewicz', 'ul. Filtrowa 68', 'Warszawa');

  22. TEST SELEC * FROM customers; SELECT CompanyName, ContactName FROM customers; SELECT * FROM customers WHERE companyname LIKE ‘a%’; SELECT * FROM customers WHERE companyname LIKE ‘A%’; SELECT companyname, contactname FROM customers WHERE companyname > ‘A’; SELECT companyname, contactname FROM customers WHERE companyname > ‘G’ AND contactname > ‘G’;

  23. SQL Advanced

  24. LIMIT clause • Used to specify the number of records to return SELECT column_name(s) FROM table_name LIMIT number; SELECT * FROM persons LIMIT 2; Oracle, SQL Server: Top number|percent (e.g., Top 2, or Top 50 PERCENT Oracle: ROWNUM<=number (e.g., ROWNUM<=5)

  25. LIKE operator • Used in a WHERE clause to search for a specified pattern in a column SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern; SELECT * FROM persons WHERE city LIKE ‘S%’; %: define wildcards (missing letters) both before and after the pattern SELECT * FROM persons WHERE city LIKE ‘%s’;

  26. SQL Wildcards • SQL Wildcards can substitute for one or more characters when searching for data in a database • SQL wildcards must be used with the SQL LIKE operator

  27. SQL Wildcards SELECT * FROM persons WHERE city LIKE ‘Sa%’; SELECT * FROM persons WHERE city LIKE ‘%nes%’; SELECT * FROM persons WHERE firstname LIKE ‘_la’; SELECT * FROM persons WHERE lastname LIKE ‘S_end_on’;

  28. IN operator • To specify multiple values in a WHERE clause SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2,…) SELECT * FROM Persons WHERE lastname IN (‘Hansen’, ‘Pettersen’) SELECT * FROM Persons WHERE lastname IN (SELECT lastname from Persons where city=‘Sandnes’)

  29. BETWEEN operator • Used in a WHERE clause to select a range of data between two values SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value 2; SELECT * FROM persons WHERE lastname BETWEEN ‘Hansen’ AND ‘Pettersen’; Warning: different database systems have different ways of processing BETWEEN operator SELECT * FROM persons WHERE lastname NOT BETWEEN ‘Hansen’ AND ‘Pettersen’;

  30. Alias • An alias name can be given to a table or a column SELECT column_name(s) FROM table_name AS alias_name; Or SELECT column_name AS alias_name FROM table_name; SELECT po.O_ID, p.LastName, p.FirstName FROM Persons AS p, Orders AS po WHERE p.LastName='Hansen' AND p.FirstName='Ola'; Without alias SELECT Orders.OrderID, Persons.LastName, Persons.FirstName FROM Persons, Orders WHERE Persons.LastName=‘Hansen’ AND Persons.FirstName=‘Ola’;

  31. JOIN • Used to query data from two or more tables • JOIN: return rows when there is at least one match in both tables • LEFT JOIN: return all rows from the left table, even if there are no matches in the right table • RIGHT JOIN: return all rows from the right table, even if there are no matches in the left table • FULL JOIN: return rows where there is a match in one of the tables

  32. JOIN CREATE DATABASE test; CREATE TABLE orders ( O_Id INT, OrderNO INT, P_Id INT, PRIMARY KEY (O_Id) ); INSERT INTO orders VALUES (1, 77895, 3); INSERT INTO orders VALUES (2, 44678, 3); INSERT INTO orders VALUES (3, 22456, 1); INSERT INTO orders VALUES (4, 24562, 1); INSERT INTO orders VALUES (5, 34764, 15); Same as INNER JOIN

  33. INNER JOIN SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name; SELECT persons.lastname, persons.firstname, orders.orderNo From Persons INNER JOIN Orders ON persons.P_Id=orders.P_Id ORDER BY persons.Lastname; The INNER JOIN keyword returns rows where there is at least one match in both tables. If there are rows in Persons that do not have matches in Orders, those rows will NOT be listed.

  34. LEFT JOIN • Returns all rows from the left table, even if there are no matches in the right table SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name; In some databases, LEFT JOIN is called LEFT OUTER JOIN SELECT persons.lastname, persons.firstname, orders.orderno FROM persons LEFT JOIN orders ON persons.P_Id=orders.P_Id ORDER BY persons.lastname;

  35. RIGHT JOIN • Returns all rows from the right table (table 2), even if there are no matches in the left table (table 1) SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_name; In some databases, RIGHT JOIN is called RIGHT OUTER JOIN SELECT persons.lastname, persons.firstname, orders.orderno FROM persons RIGHT JOIN orders ON persons.P_Id=orders.P_Id ORDER BY persons.lastname;

  36. FULL JOIN • Return rows when there is a match in one of the tables SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name=table_name2.column_name; FULL JOIN is not supported by MySQL. FULL JOIN = LEFT JOIN UNION RIGHT JOIN SELECT persons.lastname, persons.firstname, orders.orderno FROM persons FULL JOIN orders ON persons.P_Id=orders.P_Id ORDER BY persons.lastname; SELECT persons.lastname, persons.firstname, orders.orderno FROM persons LEFT JOIN orders ON persons.P_Id=orders.P_Id UNION SELECT persons.lastname, persons.firstname, orders.orderno FROM persons RIGHT JOIN orders ON persons.P_Id=orders.P_Id

  37. UNION operator • Combines two or more SELECT statements • Each SELECT statement must have the same columns (same name, same data types, in the same order) • UNION selects only distinct values by default. To allow duplicate values, use UNION ALL. SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2; SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2; SELECT P_Id FROM persons UNION SELECT P_Id FROM orders;

  38. CREATE DATABASE • Used to create a database CREATE DATABASE database_name; CREATE DATABASE my_db;

  39. CREATE TABLE • Used to create a table within a database CREATE TABLE table_name (column_name1 data_type, column_name2 data_type, column_name3 data_type, ….); CREATE TABLE persons ( P_Id INT, LastName VARCHAR(25), FirstName VARCHAR(25), Address VARCHAR(25), City VARCHAR(15), PRIMARY KEY (P_Id) );

  40. Constraints • Used to limit the type of data that can go into a table • Can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement) • Type of constraints: • NOT NULL • UNIQUE • PRIMARY KEY • FOREIGN KEY • CHECK • DEFAULT

  41. NOT NULL • Enforce a column to not accept NULL values CREATE TABLE Persons ( P_Id INT NOT NULL, LastNamevarchar(255) NOT NULL, FirstNamevarchar(255), Address varchar(255), City varchar(255) );

  42. UNIQUE • Uniquely identifies each record in a database table • UNIQUE and PRIMARY KEY both provide a guarantee for uniqueness for a column or set of columns • A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

  43. UNIQUE CREATE TABLE Persons ( P_Id INT NOT NULL, LastNamevarchar(255) NOT NULL, FirstNamevarchar(255), Address varchar(255), City varchar(255) UNIQUE (P_Id) ); CREATE TABLE Persons ( P_Id INT NOT NULL UNIQUE, LastNamevarchar(255) NOT NULL, FirstNamevarchar(255), Address varchar(255), City varchar(255) ); MySQL SQL Server/Oracle/MS Access CREATE TABLE Persons ( P_Id INT NOT NULL, LastNamevarchar(255) NOT NULL, FirstNamevarchar(255), Address varchar(255), City varchar(255) CONSTRAINT uc_PersonID UNIQUE (P_Id, LastName) ); MySQL/SQL Server/Oracle/MS Access

  44. UNIQUE When Persons table has already been created, use Alter to add new constraints. ALTER TABLE Persons ADD UNIQUE (P_Id) ALTER TABLE Persons ADD CONSTRAINT un_PersonID UNIQUE (P_Id, LastName) To drop a UNIQUE constraint ALTER TABLE Persons DROP INDEX un_PersonID MySQL ALTER TABLE Persons DROP CONSTRAINT un_PersonID SQL Server/Oracle/MS Access

  45. PRIMARY KEY • Each table should have one and only one primary key • Primary key should be unique and does not contain NULL values CREATE TABLE Persons ( P_Id INT NOT NULL, LastNamevarchar(255) NOT NULL, FirstNamevarchar(255), Address varchar(255), City varchar(255) PRIMARY KEY (P_Id) ); CREATE TABLE Persons ( P_Id INT NOT NULL PRIMARY KEY, LastNamevarchar(255) NOT NULL, FirstNamevarchar(255), Address varchar(255), City varchar(255) ); SQL Server/Oracle/MS Access MySQL CREATE TABLE Persons ( P_Id INT NOT NULL, LastNamevarchar(255) NOT NULL, FirstNamevarchar(255), Address varchar(255), City varchar(255) CONSTRAINT pk_PersonID PRIMARY KEY (P_Id, LastName) ); MySQL/SQL Server/Oracle/MS Access

  46. PRIMARY KEY When Persons table has already been created, use Alter to add new constraints. ALTER TABLE Persons ADD PRIMARY KEY (P_Id) ALTER TABLE Persons ADD CONSTRAINT un_PersonID PRIMARY KEY (P_Id, LastName) To drop a constraint ALTER TABLE Persons DROP PRIMARY KEY MySQL ALTER TABLE Persons DROP CONSTRAINT pk_PersonID SQL Server/Oracle/MS Access

  47. FOREIGN KEY • A foreign key in one table points to a primary key in another table • The foreign key constraint prevents invalid data from being inserted into the foreign key column because it has to be one of the values contained in the table it points to. CREATE TABLE Orders ( O_Id INT NOT NULL, OrderNo INT NOT NULL, P_Id INT, PRIMARY KEY (O_Id), FOREIGN KEY (P_Id) REFERENCES Persons (P_Id) ); CREATE TABLE Orders ( O_Id INT NOT NULL PRIMARY KEY, OrderNo INT NOT NULL, P_Id INT FOREIGN KEY REFERENCES Persons(P_Id) ); SQL Server/Oracle/MS Access MySQL CREATE TABLE Orders ( O_Id INT NOT NULL, OrderNo INT NOT NULL, P_Id INT, PRIMARY KEY (O_Id), CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons (P_Id)); MySQL/SQL Server/Oracle/MS Access

  48. FOREIGN KEY When Orders table has already been created, use Alter to add new constraints. ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) ALTER TABLE Orders ADD FOREIGN KEY (P_Id) REFERENCES Persons (P_Id) To drop a constraint MySQL ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders ALTER TABLE Orders DROP CONSTRAINT fk_PerOrders SQL Server/Oracle/MS Access

  49. CHECK Constraint • Used to limit the value range of a column CREATE TABLE Persons(P_Idint NOT NULL,LastNamevarchar(255) NOT NULL,FirstNamevarchar(255),Address varchar(255),City varchar(255),CHECK (P_Id>0)) CREATE TABLE Persons(P_Idint NOT NULL CHECK (P_Id>0),LastNamevarchar(255) NOT NULL,FirstNamevarchar(255),Address varchar(255),City varchar(255)) SQL Server/Oracle/MS Access MySQL CREATE TABLE Persons(P_Idint NOT NULL,LastNamevarchar(255) NOT NULL,FirstNamevarchar(255),Address varchar(255),City varchar(255),CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes‘)); MySQL/SQL Server/Oracle/MS Access

  50. CHECK Constraint When a table has already been created, use Alter to add new constraints. ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City=‘Sandnes’) ALTER TABLE Persons ADD CHECK (P_Id>0) To drop a constraint SQL Server/Oracle/MS Access ALTER TABLE Persons DROP CONSTRAINT chk_Person

More Related