tsql操作数据库:
1:打开数据库:use test1
2:查看数据库信息
exec sp_helpdb database_name
3:修改数据库容量
4:更改数据库名称
ALTER DATABASE student44 SET SINGLE_USER; //修改成单用户状态
exec sp_renamedb 'mytest1', 'mytest' //重命名数据库
ALTER DATABASE mytest SET MULTI_USER ; /修改回多用户状态
5:分离和附加数据库
分离:exec sp_detach_db 'student44'
附加:create database student44
on(filename = 'D:\data\student_Data.mdf')
for attach
6:删除数据库
drop database student44
7:课堂任务参考答案:[题目参考第六次课ppt]
CREATE DATABASE student66
ON PRIMARY
(NAME = 'student_Data',
FILENAME = 'D:\data\student66.mdf',
SIZE = 3MB,
MAXSIZE = 20MB,
FILEGROWTH = 20%),
(NAME = student_Data2,
FILENAME = 'D:\data\student66.ndf',
SIZE = 3MB,
MAXSIZE = 20MB,
FILEGROWTH = 20%)
LOG ON
(NAME = student_log,
FILENAME = 'D:\data\student66.ldf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%)
create database seven
on primary
(
name='seven_data',
filename = 'D:\sqltest\seven_data.mdf',
size = 3MB,
maxsize = 20MB,
filegrowth = 20%
)
log on
(
name = 'seven_log',
filename = 'D:\sqltest\seven_log.ldf',
size = 1MB,
filegrowth = 10%
)
2:第一种约束:主键约束
关系:民族(民族代码,民族名称) 民族代码为主键
create table nation
(
nationCode char(4) primary key,
nationName varchar(20)
)
对表添加主键
create table student
(
code char(8) not null, //【想设置主键的属性】一定不能为空
name varchar(10)
)
alter table student add constraint pk_code primary key(code);
3:第二种:唯一约束
关系: 体育项目(体育项目id,体育项目名)
create table sports
(
sports_id char(3) primary key,
sports_name varchar(15) unique //unique:唯一约束
)
insert into sports(sports_id,sports_name)
values('1','篮球'),('2','羽毛球')
alter table sports
add constraint unique_name unique(sports_name);
4:第三种:检查约束
注册表(注册号,注册日期,分数)
create table register
(
re_num int primary key,
re_date date,
score float check(score>=0 and score<=100)
)
insert into register values('1','2016-3-31',101)//错误的
insert into register values('1','2016-3-31',98)
select * from register;
练习
1:用户登录表(id,name)
name要求必须是zhangsan,lisi,wangwu
2:学生(id,性别,年龄)------达标要求
性别要求必须是男和女
3:会员(会员id,会员名,会员等级)
会员等级是A,B,C,D,E
4:学生(学号,姓名,班级名)
班级名必须以‘计网’开头
==================================
备注
那check约束可以实现那些功能呢?
1范围规定:CHECK (jine>0)
2枚举值规定(等级)
ALTER TABLE tablename ADD CHECK (nandu in (1,2,3,4)) //定位为整形时
3特定的匹配
CHECK (fieldName like'9%')
check中可以使用的运算符,主要有>,<,and ,or,not,like,in等。
=====================
扩展学习:
check约束的操作htt