sql语句案例

创建表

/*create database db_testOne
on(
   name='db_testOne_date',
   filename = 'D:\dataBaseTemp\db_testOne_date.mdf',
   size = 3mb,
   maxsize = 10mb,
   filegrowth = 5% 
)
/*日志文件的具体描述*/
log on(
  name = 'db_testOne_data.ldf',
  filename = 'D:\dataBaseTemp\db_testOne_date.ldf',
  size = 2mb,
  filegrowth = 1mb
)
*/

/*使用数据库*/
use db_testOne
go 
if exists(select * from sysobjects where name='StudentList')
drop table StudentList
create table StudentList(
  sid char(8) PRIMARY KEY,
  sname char(8) NOT NULL,
  sClassSelect char(8),
  sLibraryBooks int NOT NULL
)
go 
if exists(select * from sysobjects where name='teacherList')
drop table teacherList
create table teacherList(
  sid char(8) PRIMARY KEY,
  sname char(8) NOT NULL,
  sClassTeach char(8),
  sLibraryBooks int NOT NULL
)

增删查改
/*插入数据*/
use db_testOne
go
/*INSERT INTO dbo.StudentList
VALUES(10001,'张三','数学',1);
INSERT INTO dbo.StudentList
VALUES(10002,'李四','英语',2);
INSERT INTO dbo.StudentList
VALUES(10003,'王五','科学',3);
*/
/*INSERT INTO dbo.StudentList
VALUES(10004,'涨价话','数学',1);
INSERT INTO dbo.StudentList
VALUES(10005,'网卡成','英语',2);
INSERT INTO dbo.StudentList
VALUES(10006,'展期寻','科学',3);
*/
/*查询*/
/*select * from dbo.StudentList;*/
/*WHERE dbo.StudentList.sLibraryBooks = 2;*/
/*删除*/
DELETE FROM dbo.StudentList
WHERE dbo.StudentList.sname = '网卡成' AND dbo.StudentList.sLibraryBooks=2;
select * from dbo.StudentList;
/*更新*/
UPDATE dbo.StudentList 
SET sid = 999999,sLibraryBooks = 1
WHERE dbo.StudentList.sname='张三';