----------试图操作(view)-----------------------创建视图create view titles_viewasselect title,type from titles--调用视图select * from titles_view--删除视图drop view titles_view--修改视图alter view titles_viewasselect title,type,price from titlesgo
--------对表（Table）的操作------------------create table teacher(number int primary key,name varchar(20) not null,sex char(2) check(sex=‘男‘ or sex=‘女‘),birthday datetime,job_title varchar(20),salary money,memo ntext,nicheng varchar(20) unique,height numeric(7,2))select * from teacherdrop table studentcreate table Student(number int primary key,name varchar(20) not null,sex char(2) check(sex=‘男‘ or sex=‘女‘),teachernumber int foreign key references teacher(number))--在 Student 表 添加一个新列alter table Studentadd birthday datetime,salary money--在 Student 表 删除一个已有的列alter table Studentdrop column salary--在 Sutdent 表 修改一个列的约束alter table Studentalter column name varchar(20)insert Student(number,name,sex,teachernumber)values(0003,‘小小黑2‘,‘男‘,1)insert Student(number,name,sex,teachernumber)values(0004,‘小小黑4‘,‘男‘,1)--外键必须产生于主键--在删除的时候，如果这表上的列在其他表有外键的话--(如果插入的数据产生关联)必须先删外键数据之后，才可以删除这表的数据------------查询技术use pubsgo--查询书名表的所有列select * from titles--查询书名表的书名编号、书名名称、单价、类型select * from titlesselect title_id,title,price,type from titles--as 用法 取别名select title_id as ‘书名编号‘,title as ‘书名名称‘,price as ‘单价‘,type as‘类型‘ from titles--oder by 排序 asc,desc--查询书名表的所有列 按价格排序(从大到小) ascselect title,price from titles order by priceselect title,price from titles order by price asc--查询书名表的所有列 按价格排序(从小到大)descselect title,price from titles order by price desc---where 条件--查看书名编号为：BU1111的记录信息select * from titlesselect * from titles where title_id=‘BU1111‘--查看书的类型是"business"的所有信息select * from titles where type=‘business‘-- in 包含-- not in 不包含-- or 或者-- and 且--查看书的类型是"business,mod_cook"的所有信息select title,type from titles where type=‘business‘ ortype=‘mod_cook‘select title,type from titles where typein(‘business‘,‘mod_cook‘)--查看书的类型不是"business,mod_cook"的所有信息select title,type from titles where type!=‘business‘ andtype!=‘mod_cook‘select title,[type] from titles where type notin(‘busines‘,‘mod_cook‘)--一些函数应用min,max,sum,avg,count,count(*)select * from titles--不算price 等于null----min 最小值select min(price) from titlesselect price from titles where type=‘business‘select min(price) from titles where type=‘business‘ -----max 最大值select max(price) from titles----- sum 总和select sum(price) from titles-----avg 平均值select avg(price) from titles-----count(*),count(列明)select count(*) as ‘总计‘ from titlesselect count(title_id) ‘总计‘ from titles-- like  像select * from titles--查一下 title_id 中有‘BU‘的所有行数-----‘%‘ 代表所有字符select * from titles where title_id like ‘%BU%‘-----‘_’ 代表一个字符select * from titles where title_id like ‘__1%‘--group by 分组select type,count(*) ‘记录总数‘,min(price) ‘最小价格‘,max(price)‘最大价格‘,sum(price) ‘总价格‘
,avg(price) ‘平均价格‘ from titles group bytype--比较运算符=,>,<,>=,<=,!=----！= 不等于select title,price from titlesselect title,price from titles where price>10--any 任何一个,all 都select title,price from titleswhere price >any(select price from titles wheretype=‘business‘)select price from titles where type=‘business‘select min(price) from titles where type=‘business‘select title,price from titleswhere price >all(select price from titles wheretype=‘business‘)select max(price) from titles--exists 存在use mastergo-------对数据库（Database）的操作---------------if exists(select * from sys.databaseswhere name=‘db2‘)begin drop database db2endgocreate database db2gouse db2 2011/2/17 ylb pm17:20