SQL-.
SQL
15
SQL Structured Query Language, . SQL , , .
SQL- : , . ( ), SQL.
SELECT FROM
SELECT , . , SQL .
3 , Student ( SELECT FROM) . ID (studentID), (FirstName) (LastName) . (FullName).
SELECT studentID, FirstName, LastName, FirstName + ' ' + LastName AS FullName FROM student;
:
+-----------+-------------------+------------+------------------------+ | studentID | FirstName | LastName | FullName | +-----------+-------------------+------------+------------------------+ | 1 | Monique | Davis | Monique Davis | | 2 | Teri | Gutierrez | Teri Gutierrez | | 3 | Spencer | Pautier | Spencer Pautier | | 4 | Louis | Ramsey | Louis Ramsey | | 5 | Alvin | Greene | Alvin Greene | | 6 | Sophie | Freeman | Sophie Freeman | | 7 | Edgar Frank "Ted" | Codd | Edgar Frank "Ted" Codd | | 8 | Donald D. | Chamberlin | Donald D. Chamberlin | | 9 | Raymond F. | Boyce | Raymond F. Boyce | +-----------+-------------------+------------+------------------------+ 9 rows in set (0.00 sec)
CREATE TABLE
CREATE TABLE . , .
CREATE TABLE table_name (
column_1 datatype,
column_2 datatype,
column_3 datatype
);
ALTER TABLE
ALTER TABLE . :
ALTER TABLE table_name ADD column_name datatype;
CHECK
CHECK , .
CHECK , , . CHECK , .
Persons CHECK (Age). , 18 .
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CHECK (Age>=18)
);
CHECK CHECK :
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255),
CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
);
WHERE (AND, OR, IN, BETWEEN LIKE)
WHERE .
, , SELECT WHERE. WHERE, 5 .
SELECT studentID, FullName, sat_score, rcd_updated FROM student;
+-----------+------------------------+-----------+---------------------+ | studentID | FullName | sat_score | rcd_updated | +-----------+------------------------+-----------+---------------------+ | 1 | Monique Davis | 400 | 2017-08-16 15:34:50 | | 2 | Teri Gutierrez | 800 | 2017-08-16 15:34:50 | | 3 | Spencer Pautier | 1000 | 2017-08-16 15:34:50 | | 4 | Louis Ramsey | 1200 | 2017-08-16 15:34:50 | | 5 | Alvin Greene | 1200 | 2017-08-16 15:34:50 | | 6 | Sophie Freeman | 1200 | 2017-08-16 15:34:50 | | 7 | Edgar Frank "Ted" Codd | 2400 | 2017-08-16 15:35:33 | | 8 | Donald D. Chamberlin | 2400 | 2017-08-16 15:35:33 | | 9 | Raymond F. Boyce | 2400 | 2017-08-16 15:35:33 | +-----------+------------------------+-----------+---------------------+ 9 rows in set (0.00 sec)
SELECT, WHERE.
STUDENT studentID, FullName, sat_score, recordUpdated
FROM student
WHERE (studentID BETWEEN 1 AND 5 OR studentID = 8)
AND
sat_score NOT IN (1000, 1400);
+-----------+----------------------+-----------+---------------------+ | studentID | FullName | sat_score | rcd_updated | +-----------+----------------------+-----------+---------------------+ | 1 | Monique Davis | 400 | 2017-08-16 15:34:50 | | 2 | Teri Gutierrez | 800 | 2017-08-16 15:34:50 | | 4 | Louis Ramsey | 1200 | 2017-08-16 15:34:50 | | 5 | Alvin Greene | 1200 | 2017-08-16 15:34:50 | | 8 | Donald D. Chamberlin | 2400 | 2017-08-16 15:35:33 | +-----------+----------------------+-----------+---------------------+ 5 rows in set (0.00 sec)
UPDATE
UPDATE.
WHERE , . . :
UPDATE table_name
SET column1 = value1,
column2 = value2, ...
WHERE condition;
( Name) Id 4:
UPDATE Person SET Name = Elton John WHERE Id = 4;
, . , JOIN. :
UPDATE table_name1
SET table_name1.column1 = table_name2.columnA
table_name1.column2 = table_name2.columnB
FROM table_name1
JOIN table_name2 ON table_name1.ForeignKey = table_name2.Key
(Manager) :
UPDATE Person SET Person.Manager = Department.Manager FROM Person JOIN Department ON Person.DepartmentID = Department.ID
GROUP BY
GROUP BY .
GROUP BY:
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;
HAVING
HAVING , GROUP BY. , .
HAVING:
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > value;
AVG()
AVG, , , SQL.
:
SELECT groupingField, AVG(num_field) FROM table1 GROUP BY groupingField
Student:
SELECT studentID, FullName, AVG(sat_score) FROM student GROUP BY studentID, FullName;
AS
AS .
SELECT user_only_num1 AS AgeOfServer, (user_only_num1 - warranty_period) AS NonWarrantyPeriod FROM server_table
.
+-------------+------------------------+ | AgeOfServer | NonWarrantyPeriod | +-------------+------------------------+ | 36 | 24 | | 24 | 12 | | 61 | 49 | | 12 | 0 | | 6 | -6 | | 0 | -12 | | 36 | 24 | | 36 | 24 | | 24 | 12 | +-------------+------------------------+
, AS JOIN.
SELECT ord.product, ord.ord_number, ord.price, cust.cust_name, cust.cust_number FROM customer_table AS cust JOIN order_table AS ord ON cust.cust_number = ord.cust_number
.
+-------------+------------+-----------+-----------------+--------------+ | product | ord_number | price | cust_name | cust_number | +-------------+------------+-----------+-----------------+--------------+ | RAM | 12345 | 124 | John Smith | 20 | | CPU | 12346 | 212 | Mia X | 22 | | USB | 12347 | 49 | Elise Beth | 21 | | Cable | 12348 | 0 | Paul Fort | 19 | | Mouse | 12349 | 66 | Nats Back | 15 | | Laptop | 12350 | 612 | Mel S | 36 | | Keyboard| 12351 | 24 | George Z | 95 | | Keyboard| 12352 | 24 | Ally B | 55 | | Air | 12353 | 12 | Maria Trust | 11 | +-------------+------------+-----------+-----------------+--------------+
ORDER BY
ORDER BY SELECT. (FullName) . (ASC), DESC.
SELECT studentID, FullName, sat_score FROM student ORDER BY FullName DESC;
COUNT
COUNT .
COUNT:
- ( Group by)
- ( Group By)
SQL . , COUNT AS.
SELECT count(*) AS studentCount FROM student;
DELETE
DELETE .
! , . WHERE , . :
DELETE FROM table_name WHERE condition;
Person Id 3:
DELETE FROM Person WHERE Id = 3;
INNER JOIN
JOIN, , , .
SELECT * FROM A x JOIN B y ON y.aId = x.Id
LEFT JOIN
LEFT JOIN . . , null.
SELECT * FROM A x LEFT JOIN B y ON y.aId = x.Id
RIGHT JOIN
RIGHT JOIN . , , . , null.
SELECT * FROM A x RIGHT JOIN B y ON y.aId = x.Id
FULL OUTER JOIN
FULL OUTER JOIN , . , , . .
SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName
INSERT
INSERT .
INSERT INTO table_name (column_1, column_2, column_3) VALUES (value_1, 'value_2', value_3);
LIKE
LIKE WHERE HAVING ( GROUP BY) , .
SQL , FullName Monique Greene.
SELECT studentID, FullName, sat_score, rcd_updated
FROM student
WHERE
FullName LIKE 'Monique%' OR
FullName LIKE '%Greene';
+-----------+---------------+-----------+---------------------+ | studentID | FullName | sat_score | rcd_updated | +-----------+---------------+-----------+---------------------+ | 1 | Monique Davis | 400 | 2017-08-16 15:34:50 | | 5 | Alvin Greene | 1200 | 2017-08-16 15:34:50 | +-----------+---------------+-----------+---------------------+ 2 rows in set (0.00 sec)
LIKE NOT, , , , . SQL , FULL NAME cer Pau Ted.
SELECT studentID, FullName, sat_score, rcd_updated FROM student WHERE FullName NOT LIKE '%cer Pau%' AND FullName NOT LIKE '%"Ted"%';
+-----------+----------------------+-----------+---------------------+ | studentID | FullName | sat_score | rcd_updated | +-----------+----------------------+-----------+---------------------+ | 1 | Monique Davis | 400 | 2017-08-16 15:34:50 | | 2 | Teri Gutierrez | 800 | 2017-08-16 15:34:50 | | 4 | Louis Ramsey | 1200 | 2017-08-16 15:34:50 | | 5 | Alvin Greene | 1200 | 2017-08-16 15:34:50 | | 6 | Sophie Freeman | 1200 | 2017-08-16 15:34:50 | | 8 | Donald D. Chamberlin | 2400 | 2017-08-16 15:35:33 | | 9 | Raymond F. Boyce | 2400 | 2017-08-16 15:35:33 | +-----------+----------------------+-----------+---------------------+ 7 rows in set (0.00 sec)
