SQL 基础教程
SQL高级教程(一)
SQL高级教程(二)
SQL高级教程(三)
AUTO INCREMENT
- 每次插入新记录时,自动地创建主键字段的值
CREATE TABLE Persons
(
P_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)
INSERT INTO Persons (FirstName,LastName)
VALUES ('Bill','Gates')
#默认从1开始递增,下列语法从100开始
ALTER TABLE Persons AUTO_INCREMENT=100
Date
函数
#平均值
SELECT AVG(column_name) FROM table_name
#列数
SELECT COUNT(column_name) FROM table_name
#值不同的列数
SELECT COUNT(DISTINCT column_name) FROM table_name
#记录数
SELECT COUNT(*) FROM table_name
#第一个记录的值
SELECT FIRST(column_name) FROM table_name
SELECT LAST(column_name) FROM table_name
SELECT MAX(column_name) FROM table_name
SELECT MIN(column_name) FROM table_name
SELECT SUM(column_name) FROM table_name
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000
#将字段的值转换为大写
SELECT UCASE(column_name) FROM table_name
#将字段的值转换为小写
SELECT LCASE(column_name) FROM table_name
#从文本字段中提取字符
SELECT MID(column_name,start,length) FROM table_name
#返回文本字段值的长度
SELECT LEN(column_name) FROM table_name
#把数值字段舍入为指定的小数位数
SELECT ROUND(column_name,decimals) FROM table_name
#返回当前日期和时间
SELECT NOW() FROM table_name
#对字段的显示进行格式化
SELECT FORMAT(column_name,format) FROM table_name