ylb: 数据库操作方法基础

----------试图操作(view)---------------------
--创建视图
create view titles_view
as
select title,type from titles
--调用视图
select * from titles_view
--删除视图
drop view titles_view
--修改视图
alter view titles_view
as
select title,type,price from titles
go

--------对表（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 teacher
drop table student
create 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 Student
add birthday datetime,salary money
--在 Student 表 删除一个已有的列
alter table Student
drop column salary
--在 Sutdent 表 修改一个列的约束
alter table Student
alter 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 pubs
go
--查询书名表的所有列
select * from titles
--查询书名表的书名编号、书名名称、单价、类型
select * from titles
select title_id,title,price,type from titles
--as 用法 取别名
select title_id as '书名编号',title as '书名名称',price as '单价',type as'类型' from titles
--oder by 排序 asc,desc
--查询书名表的所有列 按价格排序(从大到小) asc
select title,price from titles order by price
select title,price from titles order by price asc
--查询书名表的所有列 按价格排序(从小到大)desc
select title,price from titles order by price desc
---where 条件
--查看书名编号为：BU1111的记录信息
select * from titles
select * 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 titles
select 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 titles
select 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 titles
select title,price from titles where price>10
--any 任何一个,all 都
select title,price from titles
where 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 titles
where price >all(select price from titles wheretype='business')
select max(price) from titles
--exists 存在
use master
go
-------对数据库（Database）的操作---------------
if exists(select * from sys.databases
where name='db2')
begin
 drop database db2
end
go
create database db2
go
use db2
 
2011/2/17 ylb pm17:20