基础SQL语句大全.docVIP

第一章： --创建数据库 create database mydb on ( name=mydb_Data, filename=D:\DBFile\mydb_Data.mdf, size=2MB, maxsize=10MB, filegrowth=1MB ) log on ( name=mydb_Log, filename=D:\DBFile\mydb_Log.ldf, size=1MB, maxsize=10MB, filegrowth=10% ) --查看数据库信息 exec sp_helpdb mydb use mydb --修改信息 alter database mydb modify file ( name=mydb_Data, size=3MB ) --修改数据库名称 alter database firstdb modify name=seconddb exec sp_renamedb seconddb,firstdb --删除数据库 drop database firstdb create table ttt (id int) 第二章： --打开自己的数据库 use mydb --创建简单表 create table tb1 ( sid int, sname varchar(20), sex char(2) ) select * from tb1 --自动获取列值的表 --default约束 create table tb2 ( sid int , sname varchar(20), sex char(2) default 男 ) select * from tb2 insert into tb2 values(1,张三,男) insert into tb2(sid,sname) values(2,李四) insert into tb2 values(3,王五,null) --自动编号 create table tb3 ( sid int identity(1000,10), sname varchar(20) ) select * from tb3 insert into tb3 values(张三) insert into tb3 values(李四) insert into tb3 values(王五) delete from tb3 where sid=1020 insert into tb3 values(赵六) --主键约束 create table students ( sno char(4) primary key, sname varchar(20) not null, age int ) select * from students insert into students values(s001,张三,21) insert into students values(s001,张三,21)--error insert into students(sname,age) values(张三,21)--error insert into students(sno,age) values(s002,22)--error insert into students values(s002,李四,21) insert into students(sno,sname) values(s003,王五) --课程表 create table course ( cno int , cname varchar(30), constraint pk_course primary key (cno) ) select * from course insert into course values(1,C#基础) insert into course values(1,SQLServer) insert into course values(2,SQLServer) insert into course (cname) values(WindowsForm) --成绩表(学号，课程号，成绩) create table score ( sno char(4) references students(sno), cno int, grade int , Constraint fk_course foreign key (cno) references course(cno), Constraint pk_score primary key (sno,cno) ) select * from students select * from course select * from score insert into sco