CREATE TABLE Persons ( Id_P int NOT NULLUNIQUE
, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), //UNIQUE (Id_P)
) ALTER TABLE PersonsADD UNIQUE (Id_P) //单一唯一
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName) //联合唯一
ALTER TABLE Persons
DROP INDEX uc_PersonID //撤销
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID //撤销
主键
CREATE TABLE Persons ( Id_P int NOT NULLPRIMARY KEY
, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), //CONSTRAINT uc_PersonID PRIMARY KEY (Id_P,LastName)
) 更改删除同上
外键
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (O_Id),
FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
)
验证
CREATE TABLE Persons
(
Id_P int NOT NULL CHECK (Id_P>0)
,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
默认
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)
ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT
索引
语法:
CREATE INDEX index_name
ON table_name (column_name)
DROP INDEX index_name ON table_name //Microsoft SQLJet(Access)语法
DROP INDEX table_name.index_name //MS SQL Server
DROP INDEX index_name //DB2 Oracle
ALTER TABLE table_name DROP INDEX index_name //MYSQL
TRUNCATE TABLE 表名称 //仅仅删除表格中的数据
ALTER TABLE table_name ADD column_name datatype //添加列 DROP:删除
ALTER TABLE table_name ALTER COLUMN column_name datatype //修改
自增值
CREATE TABLE Persons (P_Id int NOT NULL AUTO_INCREMENT, //MySQL
LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255),PRIMARY KEY (P_Id)
) ALTER TABLE Persons AUTO_INCREMENT=100 //修改起始值
SQLServer: IDENTITY
Access: AUTOINCREMENT
INSERT INTO Persons (FirstName,LastName) VALUES ('Bill','Gates') //不需要插入P_Id
Oracle:
CREATE SEQUENCE seq_person MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10
创建名为 seq_person 的序列对象,它以 1 起始且以 1 递增。该对象缓存 10 个值以提高性能。CACHE 选项规定了为了提高访问速度要存储多少个序列值。
使用:
INSERT INTO Persons (P_Id,FirstName,LastName) VALUES (seq_person.nextval,'Lars','Monsen')
视图视图是基于 SQL 语句的结果集的可视化的表
CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition 创建或更新 CREATE OR REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
时间:
MySQL
函数 | 描述 |
---|---|
NOW() | 返回当前的日期和时间 |
CURDATE() | 返回当前的日期 |
CURTIME() | 返回当前的时间 |
DATE() | 提取日期或日期/时间表达式的日期部分 |
EXTRACT() | 返回日期/时间按的单独部分 |
DATE_ADD() | 给日期添加指定的时间间隔 |
DATE_SUB() | 从日期减去指定的时间间隔 |
DATEDIFF() | 返回两个日期之间的天数 |
DATE_FORMAT() | 用不同的格式显示日期/时间 |
SQLServer
函数 | 描述 |
---|---|
GETDATE() | 返回当前日期和时间 |
DATEPART() | 返回日期/时间的单独部分 |
DATEADD() | 在日期中添加或减去指定的时间间隔 |
DATEDIFF() | 返回两个日期之间的时间 |
CONVERT() | 用不同的格式显示日期/时间 |
null处理
SQL Server / MS Access
SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0)) FROM Products
Oracle
SELECT ProductName,UnitPrice*(UnitsInStock+NVL(UnitsOnOrder,0)) FROM Products
MySQL
SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0)) FROM Products
函数
MS Access 中的合计函数
函数 | 描述 |
---|---|
AVG(column) | 返回某列的平均值 |
COUNT(column) | 返回某列的行数(不包括 NULL 值) |
COUNT(*) | 返回被选行数 |
FIRST(column) | 返回在指定的域中第一个记录的值 |
LAST(column) | 返回在指定的域中最后一个记录的值 |
MAX(column) | 返回某列的最高值 |
MIN(column) | 返回某列的最低值 |
STDEV(column) | |
STDEVP(column) | |
SUM(column) | 返回某列的总和 |
VAR(column) | |
VARP(column) |
在 SQL Server 中的合计函数
函数 | 描述 |
---|---|
AVG(column) | 返回某列的平均值 |
BINARY_CHECKSUM | |
CHECKSUM | |
CHECKSUM_AGG | |
COUNT(column) | 返回某列的行数(不包括NULL值) |
COUNT(*) | 返回被选行数 |
COUNT(DISTINCT column) | 返回相异结果的数目 |
FIRST(column) | 返回在指定的域中第一个记录的值(SQLServer2000 不支持) |
LAST(column) | 返回在指定的域中最后一个记录的值(SQLServer2000 不支持) |
MAX(column) | 返回某列的最高值 |
MIN(column) | 返回某列的最低值 |
STDEV(column) | |
STDEVP(column) | |
SUM(column) | 返回某列的总和 |
VAR(column) | |
VARP(column) |
Scalar 函数
Scalar 函数的操作面向某个单一的值,并返回基于输入值的一个单一的值。
MS Access 中的 Scalar 函数
函数 | 描述 |
---|---|
UCASE(c) | 将某个域转换为大写 |
LCASE(c) | 将某个域转换为小写 |
MID(c,start[,end]) | 从某个文本域提取字符 |
LEN(c) | 返回某个文本域的长度 |
INSTR(c,char) | 返回在某个文本域中指定字符的数值位置 |
LEFT(c,number_of_char) | 返回某个被请求的文本域的左侧部分 |
RIGHT(c,number_of_char) | 返回某个被请求的文本域的右侧部分 |
ROUND(c,decimals) | 对某个数值域进行指定小数位数的四舍五入 |
MOD(x,y) | 返回除法操作的余数 |
NOW() | 返回当前的系统日期 |
FORMAT(c,format) | 改变某个域的显示方式 |
DATEDIFF(d,date1,date2) | 用于执行日期计算 |
存储过程
drop procedure if exists test //删除
create procedure test //创建
(in inparams int,out outparams varchar(20)) //输入输出参数
begin
declare var char(10); //声明变量
if inparams = 1 then //if条件
set var='hello'; //变量赋值
else
set var='world';
end if;
insert into t_table values(var); //SQL语句
select name from t_table into outparams;
end
使用:call test(1,@out);
控制语句
举例:
IF语句
DELIMITER //
CREATE PROCEDURE p1(IN parameter1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = parameter1 + 1;
IF variable1 = 0 THEN
INSERT INTO t VALUES (17);
END IF;
IF parameter1 = 0 THEN
UPDATE t SET s1 = s1 + 1;
ELSE
UPDATE t SET s1 = s1 + 2;
END IF;
END; //
DELIMITER ;
case语句
CREATE PROCEDURE p2 (IN parameter1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = parameter1 + 1;
CASE variable1
WHEN 0 THEN INSERT INTO t VALUES (17);
WHEN 1 THEN INSERT INTO t VALUES (18);
ELSE INSERT INTO t VALUES (19);
END CASE;
END; //
循环语句
WHILE … END WHILE 举例:
CREATE PROCEDURE p4 ()
BEGIN
DECLARE v INT;
SET v = 0;
WHILE v < 5 DO
INSERT INTO t VALUES (v);
SET v = v + 1;
END WHILE;
END; //
loop
LOOP … END LOOP 举例:
CREATE PROCEDURE p5 ()
BEGIN
DECLARE v INT;
SET v = 0;
loop_label: LOOP
INSERT INTO t VALUES (v);
SET v = v + 1;
IF v >= 5 THEN
LEAVE loop_label;
END IF;
END LOOP;
END; //