//4.5.1建立索引 语法:
create index <索引名> on <表名> <列名清表> 代码:
create index tb_hr_personl_info_ix on tb_hr_personl_info (userid)
//4.5.2删除索引 语法:
drop index <表名><.><索引名> 代码:
//删除索引名tb_hr_personl_info_ix
drop index tb_hr_personl_info.tb_hr_personl_info_ix ----------------------------------------------------------- 4.6视图view
4.6.1视图view的概念：
视图不是表，也不是表数据的备份，在数据库模式中只是select语句的集合!
----------------------------------------------------------- 4.6.2建立视图Create View
语法:
CREATE VIEW
CREATE VIEW vw_customerlist AS SELECT * FROM Customers
----------------------------------------------------------- 4.6.3查询视图Query view 语法:select * from viewname 代码:select * from vw_customerlist
----------------------------------------------------------- 4.6.4修改视图ALTER VIEW 语法:select * from viewname 代码:select * from vw_customerlist
----------------------------------------------------------- 4.6.5视图删除DROP VIEW //4.6.2视图删除
语法:
drop view <视图名> 代码:
//视图删除v_hr_personl_info drop view v_hr_personl_info
----------------------------------------------------------- 4.6.6.过滤视图Filter view 语法:
select * from viewname where/having expressions 代码:
CREATE VIEW BankersMin AS
SELECT BankerName, BankerState FROM Bankers where BankerID < 5
SELECT * FROM BankersMin WHERE BankerState = 'CA' ORDER BY BankerName
-----------------------------------------------------------
4.6.7.可更新的视图Updatable View 语法:
CREATE VIEW
SELECT statement WITH CHECK OPTION 代码:
CREATE VIEW OregonShippers_vw AS
SELECT ShipperID, CompanyName, Phone
FROM Shippers WITH CHECK OPTION
//此视图的记录可以进行delete/update/insert insert into
delete from
update
-----------------------------------------------------------
4.7权限Privilege 4.7.1数据库用户添加 语法:
sp_addlogin [ @loginame = ] 'login' [ , [ @passwd = ] 'password' ] [ , [ @defdb = ] 'database' ] [ , [ @deflanguage = ] 'language' ] [ , [ @sid = ] sid ]
[ , [ @encryptopt= ] 'encryption_option' ] 代码:
数据库testdb上面添加一个登陆用户test,密码为tt EXEC sp_addlogin 'test', 'tt', 'testdb', 'us_english' EXEC sp_addlogin 'yao', 'it', 'mtyjxc', 'us_english' ----------------------------------------------------------- 4.7.2数据库用户删除
语法:DROP LOGIN <登陆名称> 代码:DROP LOGIN test
----------------------------------------------------------- 4.7.3用户权限授予grant grant语法: