sql平时小总结
sql练习：举例子：
1.
CREATE TABLE IF NOT EXISTS zz0 (
number INT(11));
CREATE TABLE IF NOT EXISTS zz1 (
number INT(11));
INSERT INTO zz0 (number) VALUES(111111);
INSERT INTO zz0 (number) VALUES(222222);
INSERT INTO zz0 (number) VALUES(888888);
INSERT INTO zz1 (number) VALUES(333333);
INSERT INTO zz1 (number) VALUES(444444);
INSERT INTO zz1 (number) VALUES(555555);
hive> select * from zz0;
111111
222222
888888
hive> select * from zz1;
111111
333333
444444
888888
hive> select * from zz0 join zz1 on zz0.number = zz1.number;
111111 111111
888888 888888
hive> select * from zz0 left outer join zz1 on zz0.number = zz1.number;
111111 111111
222222 NULL
888888 888888
hive> select * from zz0 right outer join zz1 on zz0.number = zz1.number;
NULL
111111 111111
NULL 333333
NULL 444444
888888 888888
hive> select * from zz0 full outer join zz1 on zz0.uid = zz1.uid;
NULL
111111 111111
222222 NULL
NULL 333333
NULL 444444
888888 888888
hive> select * from zz0 left semi join zz1 on zz0.uid = zz1.uid;
111111 111111
888888 888888
2.
GROUP BY 语句
CREATE TABLE Orders(
O_Id INT NOT NULL,
OrderDate date,
OrderPrice DECIMAL (18, 2),
Customer VARCHAR (20) NOT NULL
);
INSERT INTO Orders (O_Id,OrderDate,OrderPrice,Customer) VALUES(1,'2008/07/22',1000,'Carter');
INSERT INTO Orders (O_Id,OrderDate,OrderPrice,Customer) VALUES(2,'2008/12/24',1000,'Bush');
INSERT INTO Orders (O_Id,OrderDate,OrderPrice,Customer) VALUES(3,'2008/12/26',1000,'tom');
INSERT INTO Orders (O_Id,OrderDate,OrderPrice,Customer) VALUES(4,'2008/12/27',1000,'Carter');
INSERT INTO Orders (O_Id,OrderDate,OrderPrice,Customer) VALUES(5,'2008/11/29',1000,'Bush');
INSERT INTO Orders (O_Id,OrderDate,OrderPrice,Customer) VALUES(6,'2008/11/22',1000,'Adams');
mysql> select * from Orders;
+------+------------+------------+----------+
| O_Id | OrderDate | OrderPrice | Customer |
+------+------------+------------+----------+
| 1 | 2008-12-29 | 1000.00 | Bush |
| 1 | 2008-07-22 | 1000.00 | Carter |
| 2 | 2008-12-24 | 1000.00 | Bush |
| 3 | 2008-12-26 | 1000.00 | tom |
| 4 | 2008-12-27 | 1000.00 | Carter |
| 5 | 2008-11-29 | 1000.00 | Bush |
| 6 | 2008-11-22 | 1000.00 | Adams |
+------+------------+------------+----------+
7 rows in set (0.01 sec)
mysql> SELECT Customer,SUM(OrderPrice) FROM Orders
-> GROUP BY Customer
-> ;
+----------+-----------------+
| Customer | SUM(OrderPrice) |
+----------+-----------------+
| Adams | 1000.00 |
| Bush | 3000.00 |
| Carter | 2000.00 |
| tom | 1000.00 |
+----------+-----------------+
4 rows in set (0.10 sec)
mysql> SELECT Customer,SUM(OrderPrice) FROM Orders;
+----------+-----------------+
| Customer | SUM(OrderPrice) |
+----------+-----------------+
| Bush | 7000.00 |
+----------+-----------------+
1 row in set (0.05 sec)
3.IN 操作符
CREATE TABLE Persons(
Id_P INT NOT NULL,
LastName VARCHAR (20) NOT NULL,
FirstName VARCHAR (20) NOT NULL,
Address CHAR (25),
City CHAR (25),
PRIMARY KEY (Id_P)
);
INSERT INTO Persons (Id_P,LastName,FirstName,Address,City) VALUES(1,'Adams','John','Oxford Street','London');
INSERT INTO Persons (Id_P,LastName,FirstName,Address,City) VALUES(2,'Bush','George','Fifth Avenue','New York');
INSERT INTO Persons (Id_P,LastName,FirstName,Address,City) VALUES(3,'Bush','Carter','Thomas','Changan Street');
mysql> SELECT * FROM Persons;
+------+----------+-----------+---------------+----------------+
| Id_P | LastName | FirstName | Address | City |
+------+----------+-----------+---------------+----------------+
| 1 | Adams | John | Oxford Street | London |
| 2 | Bush | George | Fifth Avenue | New York |
| 3 | Bush | Carter | Thomas | Changan Street |
+------+----------+-----------+---------------+----------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM Persons
-> WHERE LastName IN ('Adams','Carter')
-> ;
+------+----------+-----------+---------------+--------+
| Id_P | LastName | FirstName | Address | City |
+------+----------+-----------+---------------+--------+
| 1 | Adams | John | Oxford Street | London |
+------+----------+-----------+---------------+--------+
1 row in set (0.10 sec)
4.join
CREATE TABLE Orders1(
Id_O INT NOT NULL,
OrderNo INT NOT NULL,
Id_P INT NOT NULL,
PRIMARY KEY (Id_O)
);
mysql> desc Orders1
-> ;
+---------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| Id_O | int(11) | NO | PRI | NULL | |
| OrderNo | int(11) | NO | | NULL | |
| Id_P | int(11) | NO | | NULL | |
+---------+---------+------+-----+---------+-------+
3 rows in set (0.05 sec)
INSERT INTO Orders1 (Id_O,OrderNo,Id_P) VALUES(1,4567,3);
INSERT INTO Orders1 (Id_O,OrderNo,Id_P) VALUES(2,9967,2);
INSERT INTO Orders1 (Id_O,OrderNo,Id_P) VALUES(3,2667,1);
INSERT INTO Orders1 (Id_O,OrderNo,Id_P) VALUES(4,1867,3);
INSERT INTO Orders1 (Id_O,OrderNo,Id_P) VALUES(5,1867,65);
mysql> SELECT Persons.LastName, Persons.FirstName, Orders1.OrderNo
-> FROM Persons, Orders1
-> WHERE Persons.Id_P = Orders1.Id_P
-> ;
+----------+-----------+---------+
| LastName | FirstName | OrderNo |
+----------+-----------+---------+
| Bush | Carter | 4567 |
| Bush | George | 9967 |
| Adams | John | 2667 |
| Bush | Carter | 1867 |
+----------+-----------+---------+
4 rows in set (0.05 sec)
mysql> SELECT Persons.LastName, Persons.FirstName, Orders1.OrderNo
-> FROM Persons
-> INNER JOIN Orders1
-> ON Persons.Id_P = Orders1.Id_P
-> ORDER BY Persons.LastName
-> ;
+----------+-----------+---------+
| LastName | FirstName | OrderNo |
+----------+-----------+---------+
| Adams | John | 2667 |
| Bush | George | 9967 |
| Bush | Carter | 1867 |
| Bush | Carter | 4567 |
+----------+-----------+---------+
4 rows in set (0.00 sec)
SELECT Persons.LastName, Persons.FirstName, Orders1.OrderNo
FROM Persons
INNER JOIN Orders1
ON Persons.Id_P=Orders1.Id_P
ORDER BY Persons.LastName
mysql> SELECT Persons.LastName, Persons.FirstName, Orders1.OrderNo
-> FROM Persons
-> INNER JOIN Orders1
-> ON Persons.Id_P=Orders1.Id_P
-> ORDER BY Persons.LastName;
+----------+-----------+---------+
| LastName | FirstName | OrderNo |
+----------+-----------+---------+
| Adams | John | 2667 |
| Bush | George | 9967 |
| Bush | Carter | 1867 |
| Bush | Carter | 4567 |
+----------+-----------+---------+
4 rows in set (0.00 sec)
SELECT Persons.LastName, Persons.FirstName, Orders1.OrderNo
FROM Persons
LEFT JOIN Orders1
ON Persons.Id_P=Orders1.Id_P
ORDER BY Persons.LastName
mysql> SELECT Persons.LastName, Persons.FirstName, Orders1.OrderNo
-> FROM Persons
-> LEFT JOIN Orders1
-> ON Persons.Id_P=Orders1.Id_P
-> ORDER BY Persons.LastName;
+----------+-----------+---------+
| LastName | FirstName | OrderNo |
+----------+-----------+---------+
| Adams | John | 2667 |
| Bush | George | 9967 |
| Bush | Carter | 1867 |
| Bush | Carter | 4567 |
+----------+-----------+---------+
4 rows in set (0.00 sec)
SELECT Persons.LastName, Persons.FirstName, Orders1.OrderNo
FROM Persons
RIGHT JOIN Orders1
ON Persons.Id_P=Orders1.Id_P
ORDER BY Persons.LastName
mysql> SELECT Persons.LastName, Persons.FirstName, Orders1.OrderNo
-> FROM Persons
-> RIGHT JOIN Orders1
-> ON Persons.Id_P=Orders1.Id_P
-> ORDER BY Persons.LastName;
+----------+-----------+---------+
| LastName | FirstName | OrderNo |
+----------+-----------+---------+
| NULL | NULL | 1867 |
| Adams | John | 2667 |
| Bush | George | 9967 |
| Bush | Carter | 4567 |
| Bush | Carter | 1867 |
+----------+-----------+---------+
5 rows in set (0.15 sec)
SELECT Persons.LastName, Persons.FirstName, Orders1.OrderNo
FROM Persons
FULL JOIN Orders1
ON Persons.Id_P=Orders1.Id_P
ORDER BY Persons.LastName
5.union all union
请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
CREATE TABLE Employees_China(
E_ID INT NOT NULL,
E_Name CHAR (25) NOT NULL
);
CREATE TABLE Employees_USA(
E_ID INT NOT NULL,
E_Name CHAR (25) NOT NULL
);
INSERT INTO Employees_China (E_ID,E_Name) VALUES(1,'Zhang, Hua');
INSERT INTO Employees_China (E_ID,E_Name) VALUES(2, 'Wang, Wei');
INSERT INTO Employees_China (E_ID,E_Name) VALUES(3,'Carter, Thomas');
INSERT INTO Employees_China (E_ID,E_Name) VALUES(4,'Yang, Ming');
INSERT INTO Employees_USA (E_ID,E_Name) VALUES(1,'Adams, John');
INSERT INTO Employees_USA (E_ID,E_Name) VALUES(2,'Bush, George');
INSERT INTO Employees_USA (E_ID,E_Name) VALUES(3,'Carter, Thomas');
INSERT INTO Employees_USA (E_ID,E_Name) VALUES(4,'Gates, Bill');
mysql> select * from Employees_China;
+------+----------------+
| E_ID | E_Name |
+------+----------------+
| 1 | Zhang, Hua |
| 2 | Wang, Wei |
| 3 | Carter, Thomas |
| 4 | Yang, Ming |
+------+----------------+
4 rows in set (0.06 sec)
mysql> select * from Employees_USA;
+------+----------------+
| E_ID | E_Name |
+------+----------------+
| 1 | Adams, John |
| 2 | Bush, George |
| 3 | Carter, Thomas |
| 4 | Gates, Bill |
+------+----------------+
4 rows in set (0.00 sec)
mysql> SELECT E_Name FROM Employees_China
-> UNION
-> SELECT E_Name FROM Employees_USA;
+----------------+
| E_Name |
+----------------+
| Zhang, Hua |
| Wang, Wei |
| Carter, Thomas |
| Yang, Ming |
| Adams, John |
| Bush, George |
| Gates, Bill |
+----------------+
7 rows in set (0.06 sec)
mysql> SELECT E_Name FROM Employees_China
-> UNION ALL
-> SELECT E_Name FROM Employees_USA;
+----------------+
| E_Name |
+----------------+
| Zhang, Hua |
| Wang, Wei |
| Carter, Thomas |
| Yang, Ming |
| Adams, John |
| Bush, George |
| Carter, Thomas |
| Gates, Bill |
+----------------+
8 rows in set (0.00 sec)
7.select into
SELECT INTO 语句从一个表中选取数据，然后把数据插入另一个表中。
SELECT INTO 语句常用于创建表的备份复件或者用于对记录进行存档。
CREATE TABLE mdb(
Id_P INT NOT NULL,
LastName VARCHAR (20) NOT NULL,
FirstName VARCHAR (20) NOT NULL,
Address CHAR (25),
City CHAR (25),
PRIMARY KEY (Id_P)
);
CREATE TABLE Persons_backup(
LastName VARCHAR (20) NOT NULL,
FirstName VARCHAR (20)
);
SELECT *
INTO Persons IN 'test.mdb'
FROM Persons
SELECT LastName,FirstName
INTO Persons_backup
FROM Persons
8.通过使用 SQL，可以为列名称和表名称指定别名（Alias）
9.BETWEEN 操作符
操作符 BETWEEN ... AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。
mysql> SELECT * FROM Persons
-> WHERE LastName
-> BETWEEN 'Adams' AND 'Carter';
+------+----------+-----------+---------------+----------------+
| Id_P | LastName | FirstName | Address | City |
+------+----------+-----------+---------------+----------------+
| 1 | Adams | John | Oxford Street | London |
| 2 | Bush | George | Fifth Avenue | New York |
| 3 | Bush | Carter | Thomas | Changan Street |
+------+----------+-----------+---------------+----------------+
3 rows in set (0.00 sec)
not between and
10.in
mysql> SELECT * FROM Persons
-> WHERE LastName IN ('Adams','Carter');
+------+----------+-----------+---------------+--------+
| Id_P | LastName | FirstName | Address | City |
+------+----------+-----------+---------------+--------+
| 1 | Adams | John | Oxford Street | London |
+------+----------+-----------+---------------+--------+
1 row in set (0.00 sec)
11.通配符
mysql> SELECT * FROM Persons
-> WHERE City LIKE 'Ne%';
+------+----------+-----------+--------------+----------+
| Id_P | LastName | FirstName | Address | City |
+------+----------+-----------+--------------+----------+
| 2 | Bush | George | Fifth Avenue | New York |
+------+----------+-----------+--------------+----------+
1 row in set (0.08 sec)
12.一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
)
13.
索引
您可以在表中创建索引，以便更加快速高效地查询数据。
用户无法看到索引，它们只能被用来加速搜索/查询。
注释：更新一个包含索引的表需要比更新一个没有索引的表更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。
CREATE INDEX PersonIndex
ON Persons (LastName)
14.什么是视图？
在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。
视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。
注释：数据库的设计和结构不会受到视图中的函数、where 或 join 语句的影响。
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
注释：视图总是显示最近的数据。每当用户查询视图时，数据库引擎通过使用 SQL 语句来重建数据。
15.
SQL NULL 值
如果表中的某个列是可选的，那么我们可以在不向该列添加值的情况下插入新记录或更新已有的记录。这意味着该字段将以 NULL 值保存。
NULL 值的处理方式与其他值不同。
NULL 用作未知的或不适用的值的占位符。
注释：无法比较 NULL 和 0；它们是不等价的。
NULL 值是遗漏的未知数据。
默认地，表的列可以存放 NULL 值。
本章讲解 IS NULL 和 IS NOT NULL 操作符。
16.
mysql> SELECT LastName,FirstName,Address FROM Persons
-> WHERE Address IS NOT NULL;
+----------+-----------+---------------+
| LastName | FirstName | Address |
+----------+-----------+---------------+
| Adams | John | Oxford Street |
| Bush | George | Fifth Avenue |
| Bush | Carter | Thomas |
+----------+-----------+---------------+
3 rows in set (0.00 sec)
mysql> SELECT LastName,FirstName,Address FROM Persons
-> WHERE Address IS NULL;
Empty set (0.03 sec)
18.SQL 拥有很多可用于计数和计算的内建函数。
mysql> select * from Persons ;
+------+----------+-----------+---------------+----------------+
| Id_P | LastName | FirstName | Address | City |
+------+----------+-----------+---------------+----------------+
| 1 | Adams | John | Oxford Street | London |
| 2 | Bush | George | Fifth Avenue | New York |
| 3 | Bush | Carter | Thomas | Changan Street |
+------+----------+-----------+---------------+----------------+
3 rows in set (0.00 sec)
mysql> select * from Orders ;
+------+------------+------------+----------+
| O_Id | OrderDate | OrderPrice | Customer |
+------+------------+------------+----------+
| 1 | 2008-12-29 | 1000.00 | Bush |
| 1 | 2008-07-22 | 1000.00 | Carter |
| 2 | 2008-12-24 | 1000.00 | Bush |
| 3 | 2008-12-26 | 1000.00 | tom |
| 4 | 2008-12-27 | 1000.00 | Carter |
| 5 | 2008-11-29 | 1000.00 | Bush |
| 6 | 2008-11-22 | 1000.00 | Adams |
+------+------------+------------+----------+
7 rows in set (0.00 sec)
select AVG(OrderPrice) from Orders ;
mysql> select AVG(OrderPrice) from Orders ;
+-----------------+
| AVG(OrderPrice) |
+-----------------+
| 1000.000000 |
+-----------------+
1 row in set (0.00 sec)
select count(OrderPrice) from Orders ;
SELECT Customer FROM Orders
WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)
mysql> SELECT DISTINCT OrderPrice FROM Orders;
+------------+
| OrderPrice |
+------------+
| 1000.00 |
+------------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(Customer) AS CustomerNilsen FROM Orders
-> WHERE Customer='Carter';
+----------------+
| CustomerNilsen |
+----------------+
| 2 |
+----------------+
1 row in set (0.02 sec)
mysql> SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders;
+-------------------+
| NumberOfCustomers |
+-------------------+
| 4 |
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders;
+-------------------+
| LargestOrderPrice |
+-------------------+
| 1000.00 |
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders;
+--------------------+
| SmallestOrderPrice |
+--------------------+
| 1000.00 |
+--------------------+
1 row in set (0.00 sec)
mysql> SELECT SUM(OrderPrice) AS OrderTotal FROM Orders;
+------------+
| OrderTotal |
+------------+
| 7000.00 |
+------------+
1 row in set (0.00 sec)
mysql> SELECT Customer,SUM(OrderPrice) FROM Orders
-> GROUP BY Customer;
+----------+-----------------+
| Customer | SUM(OrderPrice) |
+----------+-----------------+
| Adams | 1000.00 |
| Bush | 3000.00 |
| Carter | 2000.00 |
| tom | 1000.00 |
+----------+-----------------+
4 rows in set (0.00 sec)
INSERT INTO Orders(E_ID,E_Name) VALUES(1,'Zhang, Hua');
HAVING 子句
在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数（SUM(OrderPrice)）一起使用。
mysql> SELECT Customer,SUM(OrderPrice) FROM Orders
-> WHERE Customer='Bush' OR Customer='Adams'
-> GROUP BY Customer
-> HAVING SUM(OrderPrice)>1500;
+----------+-----------------+
| Customer | SUM(OrderPrice) |
+----------+-----------------+
| Bush | 3000.00 |
+----------+-----------------+
1 row in set (0.00 sec)
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
mysql> SELECT UCASE(LastName) as LastName,FirstName FROM Persons;
+----------+-----------+
| LastName | FirstName |
+----------+-----------+
| ADAMS | John |
| BUSH | George |
| BUSH | Carter |
+----------+-----------+
3 rows in set (0.10 sec)
mysql> SELECT LCASE(LastName) as LastName,FirstName FROM Persons;
+----------+-----------+
| LastName | FirstName |
+----------+-----------+
| adams | John |
| bush | George |
| bush | Carter |
+----------+-----------+
3 rows in set (0.00 sec)
mysql> SELECT MID(City,1,3) as SmallCity FROM Persons;
+-----------+
| SmallCity |
+-----------+
| Lon |
| New |
| Cha |
+-----------+
3 rows in set (0.00 sec)
第二种sql
mysql> CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
Query OK, 0 rows affected (0.06 sec)
mysql> DESC CUSTOMERS;
+---------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| ID | int(11) | NO | PRI | NULL | |
| NAME | varchar(20) | NO | | NULL | |
| AGE | int(11) | NO | | NULL | |
| ADDRESS | char(25) | YES | | NULL | |
| SALARY | decimal(18,2) | YES | | NULL | |
+---------+---------------+------+-----+---------+-------+
5 rows in set (0.04 sec)
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );
INSERT INTO CUSTOMERS
VALUES (7, 'Muffy', 24, 'Indore', 10000.00 );
INSERT INTO first_table_name [(column1, column2, ... columnN)]
SELECT column1, column2, ...columnN
FROM second_table_name
[WHERE condition];
2.WHERE
您可以使用比较或逻辑运算符一样指定条件，如：>, <, =, LIKE, NOT 等
mysql> SELECT ID, NAME, SALARY
-> FROM CUSTOMERS
-> WHERE NAME = 'Hardik';
+----+--------+---------+
| ID | NAME | SALARY |
+----+--------+---------+
| 5 | Hardik | 8500.00 |
+----+--------+---------+
1 row in set (0.00 sec)
mysql> SELECT ID, NAME, SALARY
-> FROM CUSTOMERS
-> WHERE SALARY > 2000 OR age < 25;
+----+----------+----------+
| ID | NAME | SALARY |
+----+----------+----------+
| 3 | kaushik | 2000.00 |
| 4 | Chaitali | 6500.00 |
| 5 | Hardik | 8500.00 |
| 6 | Komal | 4500.00 |
| 7 | Muffy | 10000.00 |
+----+----------+----------+
5 rows in set (0.00 sec)
SQL ORDER BY子句用于按升序或降序排序数据在一列或多列的基础上。一些数据库默认排序查询结果按升序排列。
mysql> SELECT * FROM CUSTOMERS ORDER BY NAME, SALARY;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
+----+----------+-----+-----------+----------+
7 rows in set (0.00 sec)
QL GROUP BY子句用于协同SELECT语句用来安排相同的数据分组。
GROUP BY子句在SELECT语句的WHERE子句之后并ORDER BY子句之前。
SELECT NAME, SUM(SALARY) FROM CUSTOMERS
GROUP BY NAME;
mysql> SELECT NAME, SUM(SALARY) FROM CUSTOMERS
-> GROUP BY NAME;
+----------+-------------+
| NAME | SUM(SALARY) |
+----------+-------------+
| Chaitali | 6500.00 |
| Hardik | 8500.00 |
| kaushik | 2000.00 |
| Khilan | 1500.00 |
| Komal | 4500.00 |
| Muffy | 10000.00 |
| Ramesh | 2000.00 |
+----------+-------------+
7 rows in set (0.00 sec)
mysql> SELECT NAME, SUM(SALARY) FROM CUSTOMERS;
+--------+-------------+
| NAME | SUM(SALARY) |
+--------+-------------+
| Ramesh | 35000.00 |
+--------+-------------+
1 row in set (0.00 sec)
mysql> SELECT DISTINCT SALARY FROM CUSTOMERS
-> ORDER BY SALARY;
+----------+
| SALARY |
+----------+
| 1500.00 |
| 2000.00 |
| 4500.00 |
| 6500.00 |
| 8500.00 |
| 10000.00 |
+----------+
6 rows in set (0.00 sec)
SQL排序结果
获取使用自己的优先顺序行，SELECT查询将如下：
mysql> SELECT * FROM CUSTOMERS
-> ORDER BY (CASE ADDRESS
-> WHEN 'DELHI' THEN 1
-> WHEN 'BHOPAL' THEN 2
-> WHEN 'KOTA' THEN 3
-> WHEN 'AHMADABAD' THEN 4
-> WHEN 'MP' THEN 5
-> ELSE 100 END) ASC, ADDRESS DESC;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
+----+----------+-----+-----------+----------+
7 rows in set (0.00 sec)
这将在你自己喜欢的顺序首先在一个自然顺序，对剩余的地址按客户的ADDRESS进行排序。同时剩余的地址将在相反的字母顺序进行排序。
自定义排序
mysql> SELECT ID, NAME, AGE, ADDRESS, SALARY
-> FROM CUSTOMERS
-> WHERE SALARY IS NULL;
Empty set (0.00 sec)
mysql> SELECT ID, NAME, AGE, ADDRESS, SALARY
-> FROM CUSTOMERS
-> WHERE SALARY IS NOT NULL;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
7 rows in set (0.19 sec)
SQL UNION子句/操作符用于合并两个或多个SELECT语句的结果，不返回任何重复的行。
要使用UNION，每个SELECT必须选择相同的列数，相同数目的列表达式，相同的数据类型，并让它们以相同的顺序，但它们不必具有相同的长度。
CREATE TABLE ORDERS2(
OID INT NOT NULL,
DATE date,
CUSTOMER_ID INT NOT NULL,
AMOUNT INT NOT NULL
);
INSERT INTO ORDERS2 (OID,DATE,CUSTOMER_ID,AMOUNT)
VALUES (102, '2009-10-08 00:00:00',3, 3000 );
INSERT INTO ORDERS2 (OID,DATE,CUSTOMER_ID,AMOUNT)
VALUES (100, '2009-10-08 00:00:00',3, 1500 );
INSERT INTO ORDERS2 (OID,DATE,CUSTOMER_ID,AMOUNT)
VALUES (101, '2009-11-20 00:00:00',2, 1560 );
INSERT INTO ORDERS2 (OID,DATE,CUSTOMER_ID,AMOUNT)
VALUES (103, '2009-05-20 00:00:00',4, 2060 );
mysql> select * from ORDERS2;
+-----+------------+-------------+--------+
| OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+------------+-------------+--------+
| 102 | 2009-10-08 | 3 | 3000 |
| 100 | 2009-10-08 | 3 | 1500 |
| 101 | 2009-11-20 | 2 | 1560 |
| 103 | 2009-05-20 | 4 | 2060 |
+-----+------------+-------------+--------+
4 rows in set (0.00 sec)
mysql> select * from CUSTOMERS;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
7 rows in set (0.00 sec)
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
LEFT JOIN ORDERS2
ON CUSTOMERS.ID = ORDERS2.CUSTOMER_ID
UNION
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
RIGHT JOIN ORDERS2
ON CUSTOMERS.ID = ORDERS2.CUSTOMER_ID;
mysql> SELECT ID, NAME, AMOUNT, DATE
-> FROM CUSTOMERS
-> LEFT JOIN ORDERS2
-> ON CUSTOMERS.ID = ORDERS2.CUSTOMER_ID;
+----+----------+--------+------------+
| ID | NAME | AMOUNT | DATE |
+----+----------+--------+------------+
| 3 | kaushik | 3000 | 2009-10-08 |
| 3 | kaushik | 1500 | 2009-10-08 |
| 2 | Khilan | 1560 | 2009-11-20 |
| 4 | Chaitali | 2060 | 2009-05-20 |
| 1 | Ramesh | NULL | NULL |
| 5 | Hardik | NULL | NULL |
| 6 | Komal | NULL | NULL |
| 7 | Muffy | NULL | NULL |
+----+----------+--------+------------+
8 rows in set (0.05 sec)
mysql> SELECT ID, NAME, AMOUNT, DATE
-> FROM CUSTOMERS
-> RIGHT JOIN ORDERS2
-> ON CUSTOMERS.ID = ORDERS2.CUSTOMER_ID;
+------+----------+--------+------------+
| ID | NAME | AMOUNT | DATE |
+------+----------+--------+------------+
| 3 | kaushik | 3000 | 2009-10-08 |
| 3 | kaushik | 1500 | 2009-10-08 |
| 2 | Khilan | 1560 | 2009-11-20 |
| 4 | Chaitali | 2060 | 2009-05-20 |
+------+----------+--------+------------+
4 rows in set (0.00 sec)
mysql> SELECT ID, NAME, AMOUNT, DATE
-> FROM CUSTOMERS
-> LEFT JOIN ORDERS2
-> ON CUSTOMERS.ID = ORDERS2.CUSTOMER_ID
-> UNION
-> SELECT ID, NAME, AMOUNT, DATE
-> FROM CUSTOMERS
-> RIGHT JOIN ORDERS2
-> ON CUSTOMERS.ID = ORDERS2.CUSTOMER_ID;
+------+----------+--------+------------+
| ID | NAME | AMOUNT | DATE |
+------+----------+--------+------------+
| 3 | kaushik | 3000 | 2009-10-08 |
| 3 | kaushik | 1500 | 2009-10-08 |
| 2 | Khilan | 1560 | 2009-11-20 |
| 4 | Chaitali | 2060 | 2009-05-20 |
| 1 | Ramesh | NULL | NULL |
| 5 | Hardik | NULL | NULL |
| 6 | Komal | NULL | NULL |
| 7 | Muffy | NULL | NULL |
+------+----------+--------+------------+
8 rows in set (0.07 sec)
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
LEFT JOIN ORDERS2
ON CUSTOMERS.ID = ORDERS2.CUSTOMER_ID
UNION all
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
RIGHT JOIN ORDERS2
ON CUSTOMERS.ID = ORDERS2.CUSTOMER_ID;
mysql> SELECT ID, NAME, AMOUNT, DATE
-> FROM CUSTOMERS
-> LEFT JOIN ORDERS2
-> ON CUSTOMERS.ID = ORDERS2.CUSTOMER_ID
-> UNION all
-> SELECT ID, NAME, AMOUNT, DATE
-> FROM CUSTOMERS
-> RIGHT JOIN ORDERS2
-> ON CUSTOMERS.ID = ORDERS2.CUSTOMER_ID;
+------+----------+--------+------------+
| ID | NAME | AMOUNT | DATE |
+------+----------+--------+------------+
| 3 | kaushik | 3000 | 2009-10-08 |
| 3 | kaushik | 1500 | 2009-10-08 |
| 2 | Khilan | 1560 | 2009-11-20 |
| 4 | Chaitali | 2060 | 2009-05-20 |
| 1 | Ramesh | NULL | NULL |
| 5 | Hardik | NULL | NULL |
| 6 | Komal | NULL | NULL |
| 7 | Muffy | NULL | NULL |
| 3 | kaushik | 3000 | 2009-10-08 |
| 3 | kaushik | 1500 | 2009-10-08 |
| 2 | Khilan | 1560 | 2009-11-20 |
| 4 | Chaitali | 2060 | 2009-05-20 |
+------+----------+--------+------------+
12 rows in set (0.00 sec)
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
inner JOIN ORDERS2
ON CUSTOMERS.ID = ORDERS2.CUSTOMER_ID
mysql> SELECT ID, NAME, AMOUNT, DATE
-> FROM CUSTOMERS
-> inner JOIN ORDERS2
-> ON CUSTOMERS.ID = ORDERS2.CUSTOMER_ID;
+----+----------+--------+------------+
| ID | NAME | AMOUNT | DATE |
+----+----------+--------+------------+
| 3 | kaushik | 3000 | 2009-10-08 |
| 3 | kaushik | 1500 | 2009-10-08 |
| 2 | Khilan | 1560 | 2009-11-20 |
| 4 | Chaitali | 2060 | 2009-05-20 |
+----+----------+--------+------------+
4 rows in set (0.01 sec)
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
JOIN ORDERS2
ON CUSTOMERS.ID = ORDERS2.CUSTOMER_ID
mysql> SELECT ID, NAME, AMOUNT, DATE
-> FROM CUSTOMERS
-> JOIN ORDERS2
-> ON CUSTOMERS.ID = ORDERS2.CUSTOMER_ID;
+----+----------+--------+------------+
| ID | NAME | AMOUNT | DATE |
+----+----------+--------+------------+
| 3 | kaushik | 3000 | 2009-10-08 |
| 3 | kaushik | 1500 | 2009-10-08 |
| 2 | Khilan | 1560 | 2009-11-20 |
| 4 | Chaitali | 2060 | 2009-05-20 |
+----+----------+--------+------------+
4 rows in set (0.08 sec)
可以通过给定另一名称被叫作别名临时重命名表或列。
使用表别名指在一个特定的SQL语句重命名表。 重命名是一个临时的变化，实际的表名称并不更改数据库中。
列别名用来命名表中的列的特定SQL查询的目的。
表别名的基本语法如下：
SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];
列别名的基本语法如下：
SELECT column_name AS alias_name
FROM table_name
WHERE [condition];
SELECT C.ID, C.NAME, C.AGE, O.AMOUNT
FROM CUSTOMERS AS C, ORDERS2 AS O
WHERE C.ID = O.CUSTOMER_ID;
mysql> SELECT C.ID, C.NAME, C.AGE, O.AMOUNT
-> FROM CUSTOMERS AS C, ORDERS2 AS O
-> WHERE C.ID = O.CUSTOMER_ID;
+----+----------+-----+--------+
| ID | NAME | AGE | AMOUNT |
+----+----------+-----+--------+
| 3 | kaushik | 23 | 3000 |
| 3 | kaushik | 23 | 1500 |
| 2 | Khilan | 25 | 1560 |
| 4 | Chaitali | 25 | 2060 |
+----+----------+-----+--------+
4 rows in set (0.00 sec)
SELECT ID AS CUSTOMER_ID, NAME AS CUSTOMER_NAME
FROM CUSTOMERS
WHERE SALARY IS NOT NULL;
mysql> SELECT ID AS CUSTOMER_ID, NAME AS CUSTOMER_NAME
-> FROM CUSTOMERS
-> WHERE SALARY IS NOT NULL;
+-------------+---------------+
| CUSTOMER_ID | CUSTOMER_NAME |
+-------------+---------------+
| 1 | Ramesh |
| 2 | Khilan |
| 3 | kaushik |
| 4 | Chaitali |
| 5 | Hardik |
| 6 | Komal |
| 7 | Muffy |
+-------------+---------------+
7 rows in set (0.00 sec)
什么时候避免使用索引？
尽管索引的目的在于提高数据库的性能，以下几种情况应该避免使用。以下准则显示，当使用索引应该重新考虑：
索引不应该用在小型表上。
有频繁的，大批量更新或插入操作的表。
索引不应该用于对包含大量NULL值的列。
列经常操纵不应该被索引
CREATE INDEX idx_age
ON CUSTOMERS ( AGE );
View(视图)
视图无非是存储在数据库中的相关名称的SQL语句。视图实际上是一个表中的预定义的SQL查询形式的组合物。
视图可以包含一个表中的所有行或从表中选择部分行。视图可以从一个或多个表取决于书面SQL查询来创建。
视图是一种虚拟表，让用户做到以下几点：
用户或用户类别的找到天然或直观的结构数据的方式。
限制访问的数据，使得用户可以看到，（有时）修改确实需要或不需要更多。
汇总可从表中的数据生成各种报告。
REATE VIEW CUSTOMERS_VIEW AS
SELECT name, age
FROM CUSTOMERS;
mysql> CREATE VIEW CUSTOMERS_VIEW AS
-> SELECT name, age
-> FROM CUSTOMERS;
Query OK, 0 rows affected (0.04 sec)
mysql> select * from CUSTOMERS_VIEW;
+----------+-----+
| name | age |
+----------+-----+
| Ramesh | 32 |
| Khilan | 25 |
| kaushik | 23 |
| Chaitali | 25 |
| Hardik | 27 |
| Komal | 22 |
| Muffy | 24 |
+----------+-----+
7 rows in set (0.10 sec)
WITH CHECK OPTION：
WITH CHECK OPTION是CREATE VIEW语句选项。WITH CHECK OPTION的目的是为了确保所有更新和插入满足视图定义的条件。
如果它没有满足条件，在UPDATE或INSERT返回一个错误。
以下是创建CUSTOMERS_VIEW 视图例子使用WITH CHECK OPTION：
CREATE VIEW CUSTOMERS_VIEW1 AS
SELECT name, age
FROM CUSTOMERS
WHERE age IS NOT NULL
WITH CHECK OPTION;
图在一定条件下可以更新：
SELECT子句不包含关键字DISTINCT。
SELECT子句不包含汇总函数。
SELECT子句不包含集合函数。
SELECT子句不能包含集合运算符。
SELECT子句不能包含一个ORDER BY子句。
FROM子句中不能包含多个表。
WHERE子句不能包含子查询。
查询不包含GROUP BY或HAVING。
计算列无法更新。
从基表中的所有NOT NULL列必须包含在视图是为了使用INSERT查询功能。
所以，如果一个视图满足所有上述规则，那么就可以更新视图。下面是一个例子，更新Ramesh的年龄：
语法
下面是HAVING子句在查询中的位置：
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
GROUP BY age
HAVING COUNT(age) >= 2;
mysql> SELECT ID, NAME, AGE, ADDRESS, SALARY
-> FROM CUSTOMERS
-> GROUP BY age
-> HAVING COUNT(age) >= 2;
+----+--------+-----+---------+---------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+--------+-----+---------+---------+
| 2 | Khilan | 25 | Delhi | 1500.00 |
+----+--------+-----+---------+---------+
1 row in set (0.07 sec)
事务就是对数据库执行的工作单元。事务是完成逻辑顺序的工作，无论是在手动方式由用户或者自动地通过某种数据库程序的序列的单元。
事务是一个或多个数据库更改传输。例如，如果创建一个记录或更新记录或删除记录在表上，那么在这个表上可进行事务。重要的是事务控制，以确保数据的完整性和处理数据库错误。
实际上，将很多SQL查询集到一个组，将执行所有这些一起作为事务的一部分。
事务特性：
事务具有以下四个标准属性，通常由首字母缩写ACID简称：
原子: 确保工作单元中的所有操作都成功完成; 否则，该事务被中止的故障点，操作回滚到操作之前的状态。
一致性: 确保数据库正确后成功提交事务更改状态。
隔离: 事务操作彼此独立和透明。
持久性: 可确保提交的事务的结果或仍然存在系统故障的情况下的作用。
事务控制：
用来控制事务有如下命令：
COMMIT: 保存更改。
ROLLBACK: 回滚更改。
SAVEPOINT: 回滚事务组创建点
SET TRANSACTION: 事务放置的名称。
事务控制指令只能用DML命令使用INSERT，UPDATE和DELETE只。它们不能在创建表或删除它们，因为这些操作都是自动提交到数据库中。
COMMIT命令:
COMMIT命令是用于保存由事务引用的数据库改变的事务命令。
COMMIT命令保存所有事务在数据库自上次COMMIT或ROLLBACK命令。
COMMIT命令的语法如下：
COMMIT;
例子：
考虑CUSTOMERS表具有以下记录：
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
以下会从表中删除age = 25的记录，然后提交更改在数据库中的例子。
SQL> DELETE FROM CUSTOMERS
WHERE AGE = 25;
SQL> COMMIT;
作为结果，表2中行数据会被删除，SELECT语句将产生以下结果：
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
ROLLBACK命令:
ROLLBACK命令是用来撤消尚未被保存到数据库事务的事务命令。
ROLLBACK命令只能撤消事务，因为发出的最后一个COMMIT或ROLLBACK命令。
ROLLBACK命令的语法如下：
ROLLBACK;
例子：
考虑CUSTOMERS表具有以下记录：
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
下面是示例，将删除表中age = 25 的记录，然后回滚改变在数据库中。
SQL> DELETE FROM CUSTOMERS
WHERE AGE = 25;
SQL> ROLLBACK;
其结果是，删除操作不会影响表中数据，使用SELECT语句将产生以下结果：
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
SAVEPOINT命令:
保存点SAVEPOINT是，可以回滚事务到某一事务节点，而不回滚整个事务。
SAVEPOINT命令的语法如下：
SAVEPOINT SAVEPOINT_NAME;
该命令只有在创建中的事务的SAVEPOINT语句中使用。ROLLBACK命令用来取消一组事务。
回滚到SAVEPOINT的语法如下：
ROLLBACK TO SAVEPOINT_NAME;
以下是打算从CUSTOMERS表中删除这三个不同的记录的一个例子。在每个删除之前创建的保存点SAVEPOINT， 这样就可以回滚到任何SAVEPOINT，并随时在适当时将数据返回到其原始状态：
例子：
考虑CUSTOMERS表具有以下记录：
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
现在，这里是一系列操作：
SQL> SAVEPOINT SP1;
Saveyiibai created.
SQL> DELETE FROM CUSTOMERS WHERE ID=1;
1 row deleted.
SQL> SAVEPOINT SP2;
Saveyiibai created.
SQL> DELETE FROM CUSTOMERS WHERE ID=2;
1 row deleted.
SQL> SAVEPOINT SP3;
Saveyiibai created.
SQL> DELETE FROM CUSTOMERS WHERE ID=3;
1 row deleted.
现在，这三个删除已经发生，假设你要改变主意，决定回滚到标识为SP2的SAVEPOINT。 由于SP2在之后第一个删除被创建，最后两个删除都被撤消：
SQL> ROLLBACK TO SP2;
Rollback complete.
请注意，只有第一个删除发生，因为它回滚到SP2：
SQL> SELECT * FROM CUSTOMERS;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
6 rows selected.
RELEASE SAVEPOINT命令:
RELEASE SAVEPOINT命令用于删除已经创建的一个SAVEPOINT。
RELEASE SAVEPOINT的语法如下：
SET TRANSACTION [ READ WRITE | READ ONLY ];
临时表：
CREATE TEMPORARY TABLE SALESSUMMARY (
product_name VARCHAR(50) NOT NULL
, total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
, avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
, total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
INSERT INTO SALESSUMMARY
(product_name, total_sales, avg_unit_price, total_units_sold)
VALUES
('cucumber', 100.25, 90, 2);
mysql> select * from SALESSUMMARY;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber | 100.25 | 90.00 | 2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)
SQL子查询
子查询 或 内部查询 或 嵌套查询在另一个SQL查询的查询 和嵌入式WHERE子句中。
子查询用于返回将被用于在主查询作为条件的数据，以进一步限制要检索的数据。
子查询可以 在SELECT，INSERT，UPDATE使用，而且随着运算符如DELETE语句 =, <, >, >=, <=, IN, BETWEEN 等.
这里有一些规则，子查询必须遵循：
1子查询必须被圆括号括起来。
2子查询只能在有一列的SELECT子句中，除非多个列中的子查询，以比较其选定列主查询。
3ORDER BY不能在子查询中使用，主查询可以使用ORDER BY。GROUP BY可以用来在子查询中如ORDER BY执行相同的功能。
4返回多于一个行子查询只能用于具有多个值运算符，如IN操作。
5SELECT列表中不能包含到值计算到任何引用 BLOB, ARRAY, CLOB, 或NCLOB.
6子查询不能立即封闭在一组函数。
7BETWEEN 操作符不能与子查询使用;然而，操作符BETWEEN可以在子查询中使用。
where 语句中
SELECT *
FROM CUSTOMERS
WHERE ID IN (SELECT ID
FROM CUSTOMERS
WHERE SALARY > 4500) ;
mysql> SELECT *
-> FROM CUSTOMERS
-> WHERE ID IN (SELECT ID
-> FROM CUSTOMERS
-> WHERE SALARY > 4500) ;
+----+----------+-----+---------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+---------+----------+
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+---------+----------+
3 rows in set (0.04 sec)
INSERT INTO CUSTOMERS_BKP
SELECT * FROM CUSTOMERS
WHERE ID IN (SELECT ID
FROM CUSTOMERS) ;
CREATE TABLE INSECT
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
name VARCHAR(30) NOT NULL, # type of insect
date DATE NOT NULL, # date collected
origin VARCHAR(30) NOT NULL # where collected
);
mysql> desc INSECT;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| date | date | NO | | NULL | |
| origin | varchar(30) | NO | | NULL | |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.08 sec)
INSERT INTO INSECT (id,name,date,origin) VALUES
(NULL,'housefly','2001-09-10','kitchen'),
(NULL,'millipede','2001-09-10','driveway'),
(NULL,'grasshopper','2001-09-10','front yard');
mysql> INSERT INTO INSECT (id,name,date,origin) VALUES
-> (NULL,'housefly','2001-09-10','kitchen'),
-> (NULL,'millipede','2001-09-10','driveway'),
-> (NULL,'grasshopper','2001-09-10','front yard');
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from INSECT;
+----+-------------+------------+------------+
| id | name | date | origin |
+----+-------------+------------+------------+
| 1 | housefly | 2001-09-10 | kitchen |
| 2 | millipede | 2001-09-10 | driveway |
| 3 | grasshopper | 2001-09-10 | front yard |
+----+-------------+------------+------------+
3 rows in set (0.00 sec)
