续4.7定义表
create table book
(book_id char(6),
name varchar(20) not null,
hire_date datetime not null)
go
4.8默认值对象的定义
create default today as getdate()
go
4.9绑定默认值对象
use xscj
exec sp_bindefault 'today','book.hire_date'
例子:
//定义数据类型birthday_date
use xscj
exec sp_addtype birthday_date,'datetime','null'
go
//定义day默认值对象
create default day as '1960,00,00'
//将默认值对象day绑定到birthday_date数据类型
use xscj
exec sp_bindefault 'day','birthday_date'
//域完整性约束
use xscj
create table kc
(课程号char(6) not null,
课程名char(8) not null,
学分tinyint check(学分>=0 and学分<=10) null,
备注text null)
go
constraint fk foreign key(managerid) reference employees(managerid),
constraint fk foreign key(depmid) reference deptments(depmid),
SQL Server数据管理
一.条件表达式
1.1算数操作符四则运算符,如“+”、“-”、“*”、“/”以及取摸运算符“%”等
select price + 5 newprice
from products
1.2比较操作符“=”,“<”,”<=”,”>”,”>=”,!=或“<>”
select customername
from customers
where companyarea <> ‘beijing
1.3字符操作符like操作符通配符“%”
selec *
from employees
where emplyeename like ‘back%’
1.4逻辑表达式and(与)or(或)not(非)
select employeeid
from employees
where emplyeename like‘p%’
and daysofholiday < 3
二.插入数据
2.1使用valuse子句插入数据
insert into shippers (companyname, phone)
values ('snowflake shipping', '(503)555-7233')
2.2使用insert… select语句添加数据
insert costomers
select substring(firstname, 1, 3) + substring(lastname, 1, 2),
lastname, firstname , title, address, city, region ,
postalcode, country, homephone, null
from employee
三.更新数据
update语句
update northwind.dbo.customers ||数据库名.所有者.表名||
set city = s.city
from suppliers s
where s.companyname = customers.companyname
四.删除数据
delete语句
delete northwind.dbo.orders
where datediff(month,shippeddate,getdate())> 6
SQL Server数据查询
1. 语句格式
selectt [all|distinct] <目标列表达式> [,<目标列表达式>] …
from <表名或视图名>[,<表名或视图名> ] …
[ where <条件表达式> ]
[ group by <列名1> [having <条件表达式> ] ]注:筛选只有满足指定条件的组
[ order by <列名2> [ asc|desc ] ];注:升序|降序
二.单表查询
2.1使用列别名改变查询结果的列标题
select sname name,'year of birth: ’ birth,
2000-sage birthday,islower(sdept) department from student
2.2.查询满足条件的元组
(1)比较大小
select sname,sage
from student<SPAN style="FONT-FAMILY: 'Arial', '