作者：Marina Garayoa Moreno 1 月以前
37
更多类似内容
The DELETE statement is used to delete existing records in a table.
DELETE FROM table_name WHERE condition;
DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';
The UPDATE statement is used to modify the existing records in a table.
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;
The INSERT INTO statement is used to insert new records in a table.
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
It is also possible to insert multiple rows in one statement.
To insert multiple rows of data, we use the same INSERT INTO statement, but with multiple values:
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES
('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway'),
('Greasy Burger', 'Per Olsen', 'Gateveien 15', 'Sandnes', '4306', 'Norway'),
('Tasty Tee', 'Finn Egan', 'Streetroad 19B', 'Liverpool', 'L1 0AA', 'UK');
The SELECT statement is used to select data from a database.
SELECT column1, column2, ...
FROM table_name;
Return data from the Customers table:
SELECT CustomerName, City FROM Customers;
The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;
The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".
The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;
An aggregate function is a function that performs a calculation on a set of values, and returns a single value.
Aggregate functions are often used with the GROUP BY clause of the SELECT statement.
The GROUP BY clause splits the result-set into groups of values and the aggregate function can be used to return a single value for each group.
The most commonly used SQL aggregate functions are:
MIN() - returns the smallest value within the selected columnMAX() - returns the largest value within the selected columnCOUNT() - returns the number of rows in a setSUM() - returns the total sum of a numerical columnAVG() - returns the average value of a numerical columnAVG
The AVG() function returns the average value of a numeric column.
SELECT AVG(column_name)
FROM table_name
WHERE condition;
Find the average price of all products:
SELECT AVG(Price)
FROM Products;
SUM
The SUM() function returns the total sum of a numeric column.
SELECT SUM(column_name)
FROM table_name
WHERE condition;
Return the sum of all Quantity fields in the OrderDetails table:
SELECT SUM(Quantity)
FROM OrderDetails;
COUNT
The COUNT() function returns the number of rows that matches a specified criterion.
SELECT COUNT(column_name)
FROM table_name
WHERE con
Find the total number of rows in the Products table:
SELECT COUNT(*)
FROM Products;dition;
MIN AND MAX
The MIN() function returns the smallest value of the selected column.
The MAX() function returns the largest value of the selected column.
SELECT MIN(column_name)
FROM table_name
WHERE condition;
SELECT MAX(column_name)
FROM table_name
WHERE condition;
Find the lowest price in the Price column:
SELECT MIN(Price)
FROM Products;
Find the highest price in the Price column:
SELECT MAX(Price)
FROM Products;
The ANY and ALL operators allow you to perform a comparison between a single column value and a range of other values.
The ANY operator:
ANY means that the condition will be true if the operation is true for any of the values in the range.
SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name
FROM table_name
WHERE condition);
SELECT ALL column_name(s)
FROM table_name
WHERE condition;
SELECT ProductName
FROM Products
WHERE ProductID = ANY
(SELECT ProductID
FROM OrderDetails
SELECT ALL ProductName
FROM Products
WHERE TRUE;
The SELECT TOP clause is used to specify the number of records to return.
The SELECT TOP clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.
Select only the first 3 records of the Customers table:
SELECT TOP 3 * FROM Customers;
SQL aliases are used to give a table, or a column in a table, a temporary name.
Aliases are often used to make column names more readable.
An alias only exists for the duration of that query.
An alias is created with the AS keyword.
When alias is used on column:
SELECT column_name AS alias_name
FROM table_name;
When alias is used on table:
SELECT column_name(s)
FROM table_name AS alias_name;
SELECT CustomerID AS ID
FROM Customers;
The ORDER BY keyword is used to sort the result-set in ascending(ASC) or descending(DESC) order.
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
Sort the products by price:
SELECT * FROM Products
ORDER BY Price;
The WHERE clause is used to filter records.
It is used to extract only those records that fulfill a specified condition.
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Select all customers from Mexico:
SELECT * FROM Customers
WHERE Country='Mexico';
The following operators can be used in the WHERE clause:
=: Equal
>: Greater than
<: Less than
>=: Greater than or equal
<=: Less than or equal
<>: Not equal.
BETWEEN: Between a certain range
LIKE: Search for a pattern
IN: To specify multiple possible values for a column
Select all customers with a CustomerID greater than 80:
SELECT * FROM Customers
WHERE CustomerID > 80;
EXIST
The EXISTS operator is used to test for the existence of any record in a subquery.
The EXISTS operator returns TRUE if the subquery returns one or more records.
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);
SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);
BETWEEN
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.
The BETWEEN operator is inclusive: begin and end values are included.
SELECT column_name(s)
FROM table_name
WHERE col
umn_name BETWEEN value1 AND value2;
Selects all products with a price between 10 and 20:
SELECT * FROM Products
IN
The IN operator allows you to specify multiple values in a WHERE clause.
The IN operator is a shorthand for multiple OR conditions.
SELECT column_name(s)
FROM table_name
WH
Return all customers from 'Germany', 'France', or 'UK'
SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');ERE column_name IN (value1, value2, ...);
NULL
It is not possible to test for NULL values with comparison operators, such as =, <, or <>.
We will have to use the IS NULL and IS NOT NULL operators instead.
SELECT column_names
FROM table_name
WHERE column_name IS NULL;
SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;
NOT
The NOT operator is used in combination with other operators to give the opposite result, also called the negative result.
In the select statement below we want to return all customers that are NOT from Spain:
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;
Select only the customers that are NOT from Spain:
SELECT * FROM Customers
WHERE NOT Country = 'Spain';
OR
The WHERE clause can contain one or more OR operators.
The OR operator is used to filter records based on more than one condition, like if you want to return all customers from Germany but also those from Spain:
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;
Select all customers from Germany or Spain:
SELECT *
FROM Customers
WHERE Country = 'Germany' OR Country = 'Spain';
LIKE
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
There are two wildcards often used in conjunction with the LIKE operator:
% represents zero, one, or multiple characters_ represents one, single characterSELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;
Select all customers that starts with the letter "a":
SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';
ESCAPE
The ESCAPE clause is supported in the LIKE operator to indicate the escape character.
ESCAPE characters are used in the pattern string to indicate that any wildcard character that occurs after the escape character in the pattern string should be treated as a regular character.
The default escape character is backslash (\)
LIKE 'pattern string' ESCAPE 'c'
SELECT * FROM emp WHERE ENAME LIKE 'J%$_%' ESCAPE '$';
This matches all records with names that start with letter ’J’ and have the ’_’ character in them.
SELECT * FROM emp WHERE ENAME LIKE 'JOE$_JOHN' ESCAPE '$';
This matches only records with name ’JOE_JOHN’.
WILDCARDS
A wildcard character is used to substitute one or more characters in a string.
Wildcard characters are used with the LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
%: Represents zero or more characters
_: Represents a single character
[]: Represents any single character within the brackets*
-:Represents any single character within the specified range
Return all customers that starts with the letter 'a':
SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';
AND
The WHERE clause can contain one or many AND operators.
The AND operator is used to filter records based on more than one condition, like if you want to return all customers from Spain that starts with the letter 'G':
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;
Select all customers from Spain that starts with the letter 'G':
SELECT *
FROM Customers
WHERE Country = 'Spain' AND CustomerName LIKE 'G%';
The SELECT DISTINCT statement is used to return only distinct (different) values.
SELECT DISTINCT column1, column2, ...
FROM table_name;
Select all the different countries from the "Customers" table:
SELECT DISTINCT Country FROM Customers;
The DROP DATABASE statement is used to drop an existing SQL database.
DROP DATABASE databasename;
DROP DATABASE marinaDB;
The CREATE DATABASE statement is used to create a new SQL database.
CREATE DATABASE databasename;
CREATE DATABASE marinaDB;
The DROP TABLE statement is used to drop an existing table in a database.
DROP TABLE table_name;
DROP TABLE Marina;
The CREATE TABLE statement is used to create a new table in a database.
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);
CREATE TABLE Marina(
MarinaID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
ALTER TABLE
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
The ALTER TABLE statement is also used to add and drop various constraints on an existing table.
ALTER TABLE table_name
ADD column_name datatype;
ALTER TABLE Customers
ADD Email varchar(255);
CONSTRAINTS
Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the ALTER TABLE statement.
CREATE TABLE Marina (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
....
);
The following constraints are commonly used in SQL:
NOT NULL - Ensures that a column cannot have a NULL valueUNIQUE - Ensures that all values in a column are differentPRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a tableFOREIGN KEY - Prevents actions that would destroy links between tablesCHECK - Ensures that the values in a column satisfies a specific conditionDEFAULT - Sets a default value for a column if no value is specifiedCREATE INDEX - Used to create and retrieve data from the database very quicklyDEFAULT
The following SQL sets a DEFAULT value for the "City" column when the "Marina" table is created:
CREATE TABLE Marina(
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'Sandnes'
);
CHECK
The following SQL creates a CHECK constraint on the "Age" column when the "Marina" table is created. The CHECK constraint ensures that the age of a person must be 18, or older:
CREATE TABLE Marina(
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CHECK (Age>=18)
);
FOREING KEY
The following SQL creates a FOREIGN KEY on the "MarinaID" column when the "Orders" table is created:
CREATE TABLE MarinaT(
MarinaID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (MarinaID) REFERENCES MarinaT(MarinaID)
);
PRIMARY KEY
The following SQL creates a PRIMARY KEY on the "ID" column when the "Marina" table is created:
CREATE TABLE Marina(
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);
UNIQUE
The following SQL creates a UNIQUE constraint on the "ID" column when the "Marina" table is created:
CREATE TABLE Marina(
ID int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
NOT NULL
The following SQL ensures that the "ID", "LastName", and "FirstName" columns will NOT accept NULL values when the "Marina" table is created:
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);
