常用的SQL语句

最新推荐文章于 2024-07-12 07:13:33 发布

潘诺西亚的火山

最新推荐文章于 2024-07-12 07:13:33 发布

阅读量519

点赞数 1

分类专栏：笔记文章标签： sql 数据库 database

本文链接：https://blog.csdn.net/helldoger/article/details/122164199

版权

笔记专栏收录该内容

7 篇文章 3 订阅

订阅专栏

USE [master]
GO

IF EXISTS(SELECT * FROM sysdatabases WHERE NAME='ScadaData')
BEGIN
    DROP DATABASE ScadaData   --如果数据库存在先删掉数据库
END
GO

CREATE DATABASE ScadaData
ON
PRIMARY  --创建主数据库文件
(
    NAME='ScadaData',
    FILENAME='E:\ScadaData.mdf',
    SIZE=5MB,
    MaxSize=100MB,
    FileGrowth=10MB
)
LOG ON --创建日志文件
(
    NAME='ScadaDataLog',
    FileName='E:\ScadaData.ldf',
    Size=2MB,
    MaxSize=20MB,
    FileGrowth=1MB
)
GO
use ScadaData
--添加表
IF NOT EXISTS (SELECT * FROM sys.objects WHERE NAME='ActualData')

CREATE TABLE ActualData
(
    Id INT IDENTITY(1,1) not NULL,
    InsertTime DateTime NULL,
    VarName NVARCHAR(20)  NULL,
    Value NVARCHAR(20) NULL,
    Remark NVARCHAR(50)  NULL
)

use ScadaData
--添加表
IF NOT EXISTS (SELECT * FROM sys.objects WHERE NAME='ReportData')

CREATE TABLE ReportData
(
    Id INT IDENTITY(1,1) not NULL,
    InsertTime DateTime NULL,
    LQT_Level NVARCHAR(20)  NULL,
    LQT_InPre NVARCHAR(20) NULL,
    LQT_InTemp NVARCHAR(20)  NULL,
	 LQT_OutPre NVARCHAR(20)  NULL,
    LQT_OutTemp NVARCHAR(20) NULL,
    LQT_BSPre NVARCHAR(20)  NULL,
	 LQB1_Current NVARCHAR(20)  NULL,
    LQB1_Fre NVARCHAR(20) NULL,
    LQB2_Current NVARCHAR(20)  NULL,
	 LQB2_Fre NVARCHAR(20)  NULL,
    KYJ1_OutTemp NVARCHAR(20) NULL,
 KYJ2_OutTemp NVARCHAR(20)  NULL,
    KYJ3_OutTemp NVARCHAR(20) NULL,
 CQG1_OutPre NVARCHAR(20)  NULL,
    CQG2_OutPre NVARCHAR(20) NULL,
 CQG3_OutPre NVARCHAR(20)  NULL,
    Env_Temp NVARCHAR(20) NULL,
	 FQG_Temp NVARCHAR(20)  NULL,
    FQG_Pre NVARCHAR(20) NULL
)

use ScadaData
--添加表
IF NOT EXISTS (SELECT * FROM sys.objects WHERE NAME='AlarmData')

CREATE TABLE AlarmData
(
    Id INT IDENTITY(1,1) not NULL,
    InsertTime DateTime NULL,
    VarName NVARCHAR(20)  NULL,
    AlarmState NVARCHAR(20) NULL,
    Priority Int  NULL,
	 AlarmType NVARCHAR(20)  NULL,
    Value float NULL,
    AlarmValue float NULL,
	 Operator NVARCHAR(20)  NULL,
    Note NVARCHAR(150) NULL
)

use ScadaData
--添加表
IF NOT EXISTS (SELECT * FROM sys.objects WHERE NAME='SysAdmins')

CREATE TABLE SysAdmins
(
    LongId INT IDENTITY(10000,1) not NULL,
    LoginName NVARCHAR(20) NULL,
    LoginPwd NVARCHAR(20)  NULL,
    Role int NULL
)

insert into SysAdmins( LoginName,LoginPwd,Role) Values('Administrator','123',1)

--指向当前要使用的数据库
use master
go

--判断当前数据库是否存在
if exists (select * from sysdatabases where name='StudentManageDB')
drop database StudentManageDB--删除数据库
go
--创建数据库
create database StudentManageDB
on primary
(
	--数据库文件的逻辑名
    name='StudentManageDB_data',
    --数据库物理文件名（绝对路径）
    filename='D:\DB\StudentManageDB_data.mdf',
    --数据库文件初始大小
    size=10MB,
    --数据文件增长量
    filegrowth=1MB
)
--创建日志文件
log on
(
    name='StudentManageDB_log',
    filename='D:\DB\StudentManageDB_log.ldf',
    size=2MB,
    filegrowth=1MB
)
go
--创建学员信息数据表
use StudentManageDB
go
if exists (select * from sysobjects where name='Students')
drop table Students
go
create table Students
(
    StudentId int identity(100000,1) ,
    StudentName varchar(20) not null,
    Gender char(2)  not null,
    Birthday datetime  not null,
    StudentIdNo numeric(18,0) not null,--身份证号   
    Age int not null,
    PhoneNumber varchar(50),
    StudentAddress varchar(500),
    ClassId int not null  --班级外键
)
go
--创建班级表
if exists(select * from sysobjects where name='StudentClass')
drop table StudentClass
go
create table StudentClass
(
	ClassId int primary key,
    ClassName varchar(20) not null
)
go
--创建成绩表
if exists(select * from sysobjects where name='ScoreList')
drop table ScoreList
go
create table ScoreList
(
    Id int identity(1,1) primary key,
    StudentId int not null,
    CSharp int null,
    SQLServerDB int null,
    UpdateTime datetime 
)
go
--创建管理员用户表
if exists(select * from sysobjects where name='Admins')
drop table Admins
create table Admins
(
	LoginId int identity(1000,1) primary key,
    LoginPwd varchar(20) not null,
    AdminName varchar(20) not null
)
go
--创建数据表的各种约束
use StudentManageDB
go
--创建“主键”约束primary key
if exists(select * from sysobjects where name='pk_StudentId')
alter table Students drop constraint pk_StudentId
alter table Students
add constraint pk_StudentId primary key (StudentId)

--创建检查约束check
if exists(select * from sysobjects where name='ck_Age')
alter table Students drop constraint ck_Age
alter table Students
add constraint ck_Age check (Age between 18 and 35) 

--创建唯一约束unique
if exists(select * from sysobjects where name='uq_StudentIdNo')
alter table Students drop constraint uq_StudentIdNo
alter table Students
add constraint uq_StudentIdNo unique (StudentIdNo)


--创建身份证的长度检查约束
if exists(select * from sysobjects where name='ck_StudentIdNo')
alter table Students drop constraint ck_StudentIdNo
alter table Students
add constraint ck_StudentIdNo check (len(StudentIdNo)=18)

--创建默认约束 
if exists(select * from sysobjects where name='df_StudentAddress')
alter table Students drop constraint df_StudentAddress
alter table Students 
add constraint df_StudentAddress default ('地址不详' ) for StudentAddress

if exists(select * from sysobjects where name='df_UpdateTime')
alter table ScoreList drop constraint df_UpdateTime
alter table ScoreList 
add constraint df_UpdateTime default (getdate() ) for UpdateTime

--创建外键约束
if exists(select * from sysobjects where name='fk_classId')
alter table Students drop constraint fk_classId
alter table Students
add constraint fk_classId foreign key (ClassId) references StudentClass(ClassId)

if exists(select * from sysobjects where name='fk_StudentId')
alter table ScoreList drop constraint fk_StudentId
alter table ScoreList
add constraint fk_StudentId foreign key(StudentId) references Students(StudentId)


-------------------------------------------插入数据--------------------------------------
use StudentManageDB
go

--插入班级数据
insert into StudentClass(ClassId,ClassName) values(1,'软件1班')
insert into StudentClass(ClassId,ClassName) values(2,'软件2班')
insert into StudentClass(ClassId,ClassName) values(3,'计算机1班')
insert into StudentClass(ClassId,ClassName) values(4,'计算机2班')
--insert into StudentClass(ClassId,ClassName) values(5,'网络1班')
--insert into StudentClass(ClassId,ClassName) values(6,'网络2班')

--插入学员信息
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
         values('王小虎','男','1989-08-07',22,120223198908071111,'022-22222222','天津市南开区红磡公寓5-5-102',1)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
         values('贺小张','女','1989-05-06',22,120223198905062426,'022-33333333','天津市河北区王串场58号',2)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
         values('马小李','男','1990-02-07',21,120223199002078915,'022-44444444','天津市红桥区丁字沽曙光路79号',4)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
         values('冯小强','女','1987-05-12',24,130223198705125167,'022-55555555',default,2)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
         values('杜小丽','女','1986-05-08',25,130223198605081528,'022-66666666','河北衡水路北道69号',1)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
         values('王俊桥','男','1987-07-18',24,130223198707182235,'022-77777777',default,1)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
         values('张永利','男','1988-09-28',24,130223198909282235,'022-88888888','河北保定市风华道12号',3)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
         values('李铭','男','1987-01-18',24,130223198701182257,'022-99999999','河北邢台市幸福路5号',1)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
         values('宁俊燕','女','1987-06-15',24,130223198706152211,'022-11111111',default,3)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
         values('刘玲玲','女','1989-08-19',24,130223198908192235,'022-11111222',default,4)
         
            
         
--插入成绩信息
insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100000,60,78)
insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100001,55,88)
insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100002,90,58)
insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100003,88,75)

insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100004,62,88)
insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100006,52,80)
insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100007,91,66)
insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100009,78,35)

--插入管理员信息
insert into Admins (LoginPwd,AdminName) values(123456,'王晓军')
insert into Admins (LoginPwd,AdminName) values(123456,'张明丽')

--删除学员信息
--delete from Students 

--truncate table Students --删除全部数据以后，自动标识列重新编号

--显示学员信息和班级信息
select * from Students
select * from StudentClass
select * from ScoreList
select * from Admins

use StudentManageDB
go
--创建主键约束

if exists(select * from sysobjects where name='pk_StudentId')
alter table Students drop constraint pk_StudentId
alter table Students add constraint pk_StudentId primary key(StudentId)

--创建唯一约束
if exists(select * from sysobjects where name='uq_StudentIdNo')
alter table Students drop constraint uq_StudentIdNo
alter table Students add constraint uq_StudentIdNo  unique(StudentIdNo)

--创建检查约束
if exists(select * from sysobjects where name='ck_Age')
alter table Students drop constraint ck_Age
alter table Students add constraint ck_Age  check(Age between 18 and 25)

if exists(select * from sysobjects where name='ck_PhoneNumber')
alter table Students drop constraint ck_PhoneNumber
alter table Students add constraint ck_PhoneNumber  check(len(PhoneNumber)=11)


update Students set PhoneNumber='13099012876' where StudentId=10000
select * from Students


if exists(select * from sysobjects where name='df_StudentAddress')
alter table Students drop constraint df_StudentAddress
alter table Students add constraint df_StudentAddress default('地址不详') for StudentAddress

insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo ,PhoneNumber,
StudentAddress,ClassId)
values('李小璐','女','1989-01-12',24,120229198901121315, '13099012876',default,1)

insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo ,PhoneNumber,
ClassId)
values('李小璐','女','1989-01-12',24,120229198901121316, '13099012876',1)


insert into StudentClass (ClassId,ClassName) values(1,'软件1班')
if exists(select * from sysobjects where name='fk_ClassId')
alter table Students drop constraint fk_ClassId
alter table Students add constraint fk_ClassId  foreign key (ClassId) references StudentClass(ClassId)

select * from studentClass


use StudentManageDB
go
select StudentName as 姓名,Gender as 性别,出生日期=birthday
from Students where Gender='男'

select 姓名=StudentName,地址和电话=StudentAddress+'【'+PhoneNumber+'】' 
from Students where Gender='男'

select 总成绩=CSharp+SQLServerDB from ScoreList

select * from ScoreList
insert into ScoreList(StudentId,CSharp) values(100009,78)

select * from ScoreList where SQLServerDB is null


select StudentName as 姓名,Gender as 性别,出生日期=birthday,所在学校='北京大学'
from Students where Gender='男'

select top 5 StudentName,Gender,Birthday from Students

select top 20 percent StudentName,Gender,Birthday from Students

select StudentId,(CSharp+5) as C#,DB=SQLServerDB
from ScoreList where (CSharp+5)>90
order by CSharp ASC

select StudentId,(CSharp+5) as C#,DB=SQLServerDB
from ScoreList where (CSharp+5)>90
order by CSharp DESC

select top 3 StudentId,CSharp as C#,DB=SQLServerDB
from ScoreList  
where StudentId not in(select top 6 StudentId from ScoreList order by SQLServerDB DESC,CSharp DESC )
order by SQLServerDB DESC,CSharp DESC

select StudentId,CSharp as C#,DB=SQLServerDB
from ScoreList  
order by SQLServerDB DESC,CSharp DESC


use StudentManageDB
go
select StudentName,StudentAddress from Students
where StudentAddress like '天津%'

select StudentName,StudentAddress from Students
where StudentName like '%小%'

select * from ScoreList
where CSharp between 80 and 90

select StudentName,StudentAddress,Birthday from Students
where Birthday between '1987-01-01' and '1988-01-01'

select StudentName,StudentAddress,age from Students
where Age in(21,22,23)

select StudentName,StudentAddress,age from Students
where StudentName in('王小虎','贺小张')


select SUM(CSharp) as C#总成绩 from ScoreList

select 总人数=COUNT(*) from Students 

select MAX(Csharp) as C#最高分 ,MIN(CSharp) as C#最低分,AVG(CSharp) as C#最低分 from ScoreList

select Students.StudentId,C#成绩=CSharp,StudentName,ClassName
from ScoreList
inner join Students on  Students.StudentId=ScoreList.StudentId
inner join StudentClass on Students.ClassId=StudentClass.ClassId
 where CSharp >80

select  Students.StudentId,StudentName,Gender ,C#成绩=CSharp from Students
left outer join ScoreList on Students.StudentId=ScoreList.StudentId
where Gender='男'

select  Students.StudentId,StudentName,Gender ,C#成绩=CSharp from ScoreList
left outer join Students on Students.StudentId=ScoreList.StudentId
where Gender='男'

select 班级=StudentClass.ClassName,人数=COUNT(*),C#最高分=Max(CSharp),DB最高分=MAX(SQLServerDB),
AVG(CSharp) as C#平均分,AVG(SQLServerDB) as DB平均分
from Students
inner Join StudentClass on Students.ClassId =StudentClass.ClassId
inner join ScoreList on ScoreList.StudentId=Students.StudentId
group by ClassName
having AVG(CSharp)>=70 and AVG(SQLServerDB)>=70

select * from ScoreList
select StudentId from ScoreList group by StudentId  having COUNT(*)>1

select * from ScoreList
where StudentId in(select StudentId from ScoreList group by StudentId  having COUNT(*)>1)
order by StudentId

select * from ScoreList
where (select COUNT(*) from ScoreList s where s.StudentId=ScoreList.StudentId)>1
order by StudentId


use StudentManageDB
go
insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100006,52,80)
insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100007,91,66)
insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100009,78,35)

select * from ScoreList order by StudentId

--在知道那个字段重复的情况
select StudentId from ScoreList  group by StudentId having COUNT(*)>1

--查询所有重复的记录
select * from ScoreList
where StudentId in(select StudentId from ScoreList  group by StudentId having COUNT(*)>1)
order by StudentId

--其他方法
select * from ScoreList
where (select COUNT(*) from ScoreList  s where s.StudentId=ScoreList.StudentId)>1
order by StudentId

--过滤掉重复数据
select distinct StudentId,CSharp from ScoreList

select distinct StudentId,CSharp,SQLServerDB from ScoreList


--指向当前要使用的数据库
use master
go
--判断当前数据库是否存在
if exists (select * from sysdatabases where name='SMDB')
drop database SMDB --删除数据库
go
--创建数据库
create database SMDB
on primary
(
	--数据库文件的逻辑名
    name='SMDB_data',
    --数据库物理文件名（绝对路径）
    filename='D:\DB\SMDB_data.mdf',
    --数据库文件初始大小
    size=10MB,
    --数据文件增长量
    filegrowth=1MB
)
--创建日志文件
log on
(
    name='SMDB_log',
    filename='D:\DB\SMDB_log.ldf',
    size=2MB,
    filegrowth=1MB
)
go
--创建学员信息数据表
use SMDB
go
if exists (select * from sysobjects where name='Students')
drop table Students
go
create table Students
(
    StudentId int identity(100000,1) ,
    StudentName varchar(20) not null,
    Gender char(2)  not null,
    Birthday smalldatetime  not null,
    StudentIdNo numeric(18,0) not null,--身份证号
    CardNo  varchar(20) not null,--考勤卡号
    StuImage text null,--学员照片
    Age int not null,
    PhoneNumber varchar(50),
    StudentAddress varchar(500),
    ClassId int not null  --班级外键
)
go
--创建班级表
if exists(select * from sysobjects where name='StudentClass')
drop table StudentClass
go
create table StudentClass
(
	ClassId int primary key,
    ClassName varchar(20) not null
)
go
--创建成绩表
if exists(select * from sysobjects where name='ScoreList')
drop table ScoreList
go
create table ScoreList
(
    Id int identity(1,1) primary key,
    StudentId int not null,
    CSharp int null,
    SQLServerDB int null,
    UpdateTime smalldatetime not null
)
go
--创建考勤表
if exists(select * from sysobjects where name='Attendance')
drop table Attendance
create table Attendance
(
	Id int identity(100000,1) primary key,--标识列
    CardNo varchar(20) not null,--学员卡号
    DTime smalldatetime not null --打卡时间
)
go
--创建管理员用户表
if exists(select * from sysobjects where name='Admins')
drop table Admins
create table Admins
(
	LoginId int identity(1000,1) primary key,
    LoginPwd varchar(20) not null,
    AdminName varchar(20) not null
)
go
--创建数据表的各种约束
use SMDB
go
--创建“主键”约束primary key
if exists(select * from sysobjects where name='pk_StudentId')
alter table Students drop constraint pk_StudentId

alter table Students
add constraint pk_StudentId primary key (StudentId)

--创建检查约束check
if exists(select * from sysobjects where name='ck_Age')
alter table Students drop constraint ck_Age
alter table Students
add constraint ck_Age check (Age between 18 and 35) 

--创建唯一约束unique
if exists(select * from sysobjects where name='uq_StudentIdNo')
alter table Students drop constraint uq_StudentIdNo
alter table Students
add constraint uq_StudentIdNo unique (StudentIdNo)

if exists(select * from sysobjects where name='uq_CardNo')
alter table Students drop constraint uq_CardNo
alter table Students
add constraint uq_CardNo unique (CardNo)

--创建身份证的长度检查约束
if exists(select * from sysobjects where name='ck_StudentIdNo')
alter table Students drop constraint ck_StudentIdNo
alter table Students
add constraint ck_StudentIdNo check (len(StudentIdNo)=18)

--创建默认约束 
if exists(select * from sysobjects where name='df_StudentAddress')
alter table Students drop constraint df_StudentAddress
alter table Students 
add constraint df_StudentAddress default ('地址不详' ) for StudentAddress

if exists(select * from sysobjects where name='df_UpdateTime')
alter table ScoreList drop constraint df_UpdateTime
alter table ScoreList 
add constraint df_UpdateTime default (getdate() ) for UpdateTime

if exists(select * from sysobjects where name='df_DTime')
alter table Attendance drop constraint df_DTime
alter table Attendance 
add constraint df_DTime default (getdate() ) for DTime

--创建外键约束
if exists(select * from sysobjects where name='fk_classId')
alter table Students drop constraint fk_classId
alter table Students
add constraint fk_classId foreign key (ClassId) references StudentClass(ClassId)

if exists(select * from sysobjects where name='fk_StudentId')
alter table ScoreList drop constraint fk_StudentId
alter table ScoreList
add constraint fk_StudentId foreign key(StudentId) references Students(StudentId)


-------------------------------------------插入数据--------------------------------------
use SMDB
go

--插入班级数据
insert into StudentClass(ClassId,ClassName) values(1,'软件1班')
insert into StudentClass(ClassId,ClassName) values(2,'软件2班')
insert into StudentClass(ClassId,ClassName) values(3,'计算机1班')
insert into StudentClass(ClassId,ClassName) values(4,'计算机2班')
insert into StudentClass(ClassId,ClassName) values(5,'网络1班')
insert into StudentClass(ClassId,ClassName) values(6,'网络2班')

--插入学员信息
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values('王小虎','男','1989-08-07',22,120223198908071111,'0004018766','022-22222222','天津市南开区红磡公寓5-5-102',1)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values('贺小张','女','1989-05-06',22,120223198905062426,'0006394426','022-33333333','天津市河北区王串场58号',2)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values('马小李','男','1990-02-07',21,120223199002078915,'0006073516','022-44444444','天津市红桥区丁字沽曙光路79号',4)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values('冯小强','女','1987-05-12',24,130223198705125167,'0006254540','022-55555555',default,2)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values('杜小丽','女','1986-05-08',25,130223198605081528,'0006403803','022-66666666','河北衡水路北道69号',1)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values('王俊桥','男','1987-07-18',24,130223198707182235,'0006404372','022-77777777',default,1)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values('张永利','男','1988-09-28',24,130223198909282235,'0006092947','022-88888888','河北保定市风华道12号',3)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values('李铭','男','1987-01-18',24,130223198701182257,'0006294564','022-99999999','河北邢台市幸福路5号',1)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values('宁俊燕','女','1987-06-15',24,130223198706152211,'0006092450','022-11111111',default,3)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values('刘玲玲','女','1989-08-19',24,130223198908192235,'0006069457','022-11111222',default,4)
         
         insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values('王小军','女','1986-05-08',25,130224198605081528,'0006403820','022-66666666','河北衡水路北道69号',1)
         insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values('刘小丽','女','1986-05-08',25,130225198605081528,'0006403821','022-66666666','河北衡水路北道69号',1)
         insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values('张慧鑫','女','1986-05-08',25,130226198605081528,'0006403822','022-66666666','河北衡水路北道69号',1)
         insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values('李素云','女','1986-05-08',25,130227198605081528,'0006403823','022-66666666','河北衡水路北道69号',1)
         insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values('赵小金','女','1986-05-08',25,130228198605081528,'0006403824','022-66666666','河北衡水路北道69号',1)
         insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values('王浩宇','男','1986-05-08',25,130229198605081528,'0006403825','022-66666666','河北衡水路北道69号',1)
         insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values('崔永鑫','女','1986-05-08',25,130222198605081528,'0006403826','022-66666666','河北衡水路北道69号',1)
         insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values('包丽云','女','1986-05-08',25,130220198605081528,'0006403827','022-66666666','河北衡水路北道69号',1)
         
         insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values('孙丽媛','女','1986-05-08',25,130228198605081530,'0006403854','022-66666666','河北衡水路北道69号',1)
         insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values('郝志云','男','1986-05-08',25,130229198605081531,'0006403855','022-66666666','河北衡水路北道69号',1)
         insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values('王保华','女','1986-05-08',25,130222198605081532,'0006403856','022-66666666','河北衡水路北道69号',1)
         insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values('李丽颖','女','1986-05-08',25,130220198605081544,'0006403857','022-66666666','河北衡水路北道69号',1)
         
         
         
--插入成绩信息
insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100000,60,78)
insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100001,55,88)
insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100002,90,58)
insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100003,88,75)

insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100004,62,88)
insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100006,52,80)
insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100007,91,66)
insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100009,78,35)

insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100000,60,78)
insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100001,55,88)
insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100002,90,58)
insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100003,88,75)

insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100004,62,88)
insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100006,52,80)
insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100007,91,66)
insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100009,78,35)

--插入管理员信息
insert into Admins (LoginPwd,AdminName) values(123456,'王晓军')
insert into Admins (LoginPwd,AdminName) values(123456,'张明丽')

--删除学员信息
--delete from Students 

--truncate table Students --删除全部数据以后，自动标识列重新编号

--显示学员信息和班级信息
select * from Students
select * from StudentClass
select * from ScoreList
select * from Admins
select * from Attendance

潘诺西亚的火山

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
打赏
0
评论
常用的SQL语句

USE [master]GOIF EXISTS(SELECT * FROM sysdatabases WHERE NAME='ScadaData')BEGIN DROP DATABASE ScadaData --如果数据库存在先删掉数据库ENDGOCREATE DATABASE ScadaDataONPRIMARY --创建主数据库文件( NAME='ScadaData', FILENAME='E:\ScadaData.mdf', SIZE=5MB
复制链接

扫一扫