北大青鸟S1 sql

北大青鸟西安兆隆—Kaiwill

------SQL 总结------

--1:建数据库

create database aaa

on

(

name=aaa_mdf,

filename='e:aa.mdf',

size=15,

filename='e:aa.ldf',

size=10,

maxsize=25,

filegrowth=5

)

use master

select name from sysdatabases --查看有几个数据库

exec sp_dbremove database_name --删除数据库

-------------------------------------------------------------------

--2:修改数据库属性

(

stuid int identity(1,1) primary key, ----主键约束

stuname nvarchar(20) unique not null, ----唯一约束 非空约束

class_num int foreign key references class(class_num), ---- 外

键约束

stuage int check(stuage>0 and stuage<100), ----检查约束

sex char(5) default '男', ----默认值

[primary key(stuid,stuname)] --设组合键为主键

)

-------------------------------------------------------------------

北大青鸟西安兆隆—Kaiwill

--(1)主键约束：

alter table student add constraint pk_stuid primary key(stuid) --

创建主键约束

alter table student drop constraint pk_stuid --删除主键约束

alter table student add constraint pk_stuid_stuname primary

key(stuid,stuname) --设组合键为主键

--(2)外键约束

alter table student add constraint fk_class_num foreign key(class_num)

--(4)一次增加多个约束

alter table student add constraint pk_stuid primary key(stuid),constraint

uq_stuname unique(stuname)

--(5)检查约束

alter table student add constraint check_age check(stuage>0 and

stuage<100)

--(6)default 约束

alter table student add constraint df_sex default '男' for sex

-------------------------------------------------------------------

--5:数据的插入，删除，修改

select * into test6 from test --数据和表结构一起复

制

insert into student

select /*字段列*/ from /*另一表*/

where 条件

--(2)删除

delete from student

where '条件'

delete from student --删除全部数据

truncate table student