UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing'
WHERE LastName = 'Wilson'
INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')
INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')
DELETE FROM Person WHERE LastName = 'Wilson'
DELETE * FROM table_name
DELETE FROM table_name
SELECT DISTINCT Company FROM Orders
SELECT TOP number|percent column_name(s)
FROM table_name
SELECT TOP 50 PERCENT * FROM Persons
//MySQL 语法
SELECT column_name(s)
FROM table_name
LIMIT number
SELECT *
FROM Persons
LIMIT 5
//Oracle 语法
SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number
例子
SELECT *
FROM Persons
WHERE ROWNUM <= 5
SELECT * FROM Persons
WHERE City LIKE '%g'
SELECT * FROM Persons
WHERE City LIKE '%lon%'
SELECT * FROM Persons
WHERE LastName IN ('Adams','Carter')
SELECT * FROM Persons
WHERE LastName
BETWEEN 'Adams' AND 'Carter'
SELECT po.OrderID, p.LastName, p.FirstName
FROM Persons AS p, Product_Orders AS po
WHERE p.LastName='Adams' AND p.FirstName='John'
//用于 删除MySQL索引 的语法:
ALTER TABLE table_name DROP INDEX index_name
DROP TABLE 表名称
DROP DATABASE 数据库名称
//如果我们仅仅需要除去表内的数据,但并不删除表本身,那么我们该如何做呢?
//请使用 TRUNCATE TABLE 命令(仅仅删除表格中的数据):
TRUNCATE TABLE 表名称
如需在表中添加列,请使用下列语法:
ALTER TABLE table_name ADD column_name datatype
要删除表中的列,请使用下列语法:
ALTER TABLE table_name DROP COLUMN column_name
要改变表中列的数据类型,请使用下列语法:
ALTER TABLE table_name
ALTER COLUMN column_name datatype
//SQL CREATE VIEW 语法
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
MySQL Date 函数
下面的表格列出了 MySQL 中最重要的内建日期函数:
函数 | 描述 |
---|---|
NOW() | 返回当前的日期和时间 |
CURDATE() | 返回当前的日期 |
CURTIME() | 返回当前的时间 |
DATE() | 提取日期或日期/时间表达式的日期部分 |
EXTRACT() | 返回日期/时间按的单独部分 |
DATE_ADD() | 给日期添加指定的时间间隔 |
DATE_SUB() | 从日期减去指定的时间间隔 |
DATEDIFF() | 返回两个日期之间的天数 |
DATE_FORMAT() | 用不同的格式显示日期/时间 |
MySQL 也拥有类似 ISNULL() 的函数。不过它的工作方式与微软的 ISNULL() 函数有点不同。
在 MySQL 中,我们可以使用 IFNULL() 函数,就像这样:
SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0)) FROM Products
或者我们可以使用 COALESCE() 函数,就像这样:
SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0)) FROM Products
SELECT AVG(OrderPrice) AS OrderAverage FROM Orders
SELECT COUNT(Customer) AS CustomerNilsen FROM Orders WHERE Customer='Carter'
以上 SQL 语句的结果是 2,因为客户 Carter 共有 2 个订单:
CustomerNilsen |
---|
2 |
SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders
SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders
SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders
SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders
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