一.index statement(语句)
1.index用来给表格创建索引,使查看数据时更便捷
注:只有在需要频繁索引时,才创建索引,否则不需要创建,因为表格如果带着索引一起更新时比单独更新表格需要更多时间
创建一个表格上的索引,允许有重复值
CREATE INDEX index_name
ON table_name (column1, column2, ...);
eg.在persons表格中的lastname列上创建索引,且索引名称为idx_lastname
CREATE INDEX idx_lastname
ON Persons (LastName);
eg.在persons表格中的LastName和first那么两列上创建索引,用括号括起来,并用逗号分隔
CREATE INDEX idx_pname
ON Persons (LastName, FirstName);
创建一个表格上的唯一索引,不可有重复值
CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);
3.删除表格中的索引
DROP INDEX table_name.index_name;
二.auto increment field
1.quto increment
通常用于primary key,自动添加数据
eg.在表格persons里,ID列作为主键,为默认的自动递增
CREATE TABLE Persons (
ID int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
注:这里的identity是表示自动递增,ID的值将会从1开始,而且每次增加1
eg.给表格里增加一行数据,无需给ID号码,它会自动添加进去
INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen');
运行结果是,firstname和lastname两列分别是lars和monsen,而ID号自动添加
三.views视图
1.create view 创建视图
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
北风northwind数据库里,已经有默认存在的几个view:
eg.从products表格中创建一个view,名叫current product list,显示所有discontinued为no的数据信息
CREATE VIEW [Current Product List] AS
SELECT ProductID, ProductName
FROM Products
WHERE Discontinued = No;
eg.然后查看刚才创建的view
SELECT * FROM [Current Product List];
eg.从products表格中创建一个view,名叫products above average price,显示所有unitprice大于平均值的数据信息
CREATE VIEW [Products Above Average Price] AS
SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice > (SELECT AVG(UnitPrice) FROM Products);
eg.然后查看刚才创建的view
SELECT * FROM [Products Above Average Price];
eg.从另一个view即products sales for 1997里创建一个view,名叫category sales for 1997,显示1997年里每一个种类的总销售额
CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName, Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName;
eg.然后查看刚才创建的view
SELECT * FROM [Category Sales For 1997];
eg.可以加个附加条件,只显示beverages这一类别的总销售额
SELECT * FROM [Category Sales For 1997]
WHERE CategoryName = 'Beverages';
2.updating a view 更新视图
创建或替换一个view
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
eg.增加一列category,到current product list这个view里
CREATE OR REPLACE VIEW [Current Product List] AS
SELECT ProductID, ProductName, Category
FROM Products
WHERE Discontinued = No;
3.dropping a view 删除视图
DROP VIEW view_name;
四.hosting主机
1.网址需要存储和检索数据,所以网站服务器要能够进入数据库系统。
通常采用ISP(Internet Service Provider)托管的网站服务器,例如MS SQL Server,Oracle,MySQL,和MS Access。
2.MS SQL
3.Oracle 甲骨文
4.MySQL
5.MS Access