当前位置:网站首页>Déclarations SQL couramment utilisées

Déclarations SQL couramment utilisées

2022-01-15 02:10:01 La lave de Rodinia

USE [master]
GO

IF EXISTS(SELECT * FROM sysdatabases WHERE NAME='ScadaData')
BEGIN
    DROP DATABASE ScadaData   --Si la base de données existe, supprimez d'abord la base de données
END
GO

CREATE DATABASE ScadaData
ON
PRIMARY  --Créer un fichier de base de données primaire
(
    NAME='ScadaData',
    FILENAME='E:\ScadaData.mdf',
    SIZE=5MB,
    MaxSize=100MB,
    FileGrowth=10MB
)
LOG ON --Créer un fichier journal
(
    NAME='ScadaDataLog',
    FileName='E:\ScadaData.ldf',
    Size=2MB,
    MaxSize=20MB,
    FileGrowth=1MB
)
GO
use ScadaData
--Ajouter un tableau
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
--Ajouter un tableau
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
--Ajouter un tableau
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
--Ajouter un tableau
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)
-- Pointez vers la base de données actuellement utilisée 
use master
go

-- Déterminer si la base de données actuelle existe 
if exists (select * from sysdatabases where name='StudentManageDB')
drop database StudentManageDB--Supprimer la base de données
go
--Créer une base de données
create database StudentManageDB
on primary
(
	-- Nom logique du fichier de base de données 
    name='StudentManageDB_data',
    -- Nom du fichier physique de la base de données (Chemin absolu)
    filename='D:\DB\StudentManageDB_data.mdf',
    -- Taille initiale du fichier de base de données 
    size=10MB,
    -- Croissance des fichiers de données 
    filegrowth=1MB
)
--Créer un fichier journal
log on
(
    name='StudentManageDB_log',
    filename='D:\DB\StudentManageDB_log.ldf',
    size=2MB,
    filegrowth=1MB
)
go
-- Créer une fiche de données d'information pour les étudiants 
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,--Numéro d'identification 
    Age int not null,
    PhoneNumber varchar(50),
    StudentAddress varchar(500),
    ClassId int not null  --Clé étrangère de classe
)
go
--Créer une table de classe
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
--Créer une feuille de notes
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
-- Créer une table d'utilisateurs admin 
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
-- Diverses contraintes pour créer des tableaux de données 
use StudentManageDB
go
--Création“Clé primaire”Contraintesprimary 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)

--Créer une contrainte de contrôlecheck
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) 

--Créer une contrainte uniqueunique
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)


-- Créer une contrainte de vérification de la longueur de la carte d'identité 
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)

--Créer une contrainte par défaut 
if exists(select * from sysobjects where name='df_StudentAddress')
alter table Students drop constraint df_StudentAddress
alter table Students 
add constraint df_StudentAddress default ('Adresse inconnue' ) 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

--Créer une contrainte de clé étrangère
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)


-------------------------------------------Insérer des données--------------------------------------
use StudentManageDB
go

--Insérer les données de classe
insert into StudentClass(ClassId,ClassName) values(1,'Logiciels1Ben')
insert into StudentClass(ClassId,ClassName) values(2,'Logiciels2Ben')
insert into StudentClass(ClassId,ClassName) values(3,'Ordinateur1Ben')
insert into StudentClass(ClassId,ClassName) values(4,'Ordinateur2Ben')
--insert into StudentClass(ClassId,ClassName) values(5,'Réseau1Ben')
--insert into StudentClass(ClassId,ClassName) values(6,'Réseau2Ben')

-- Insérer des informations sur l'étudiant 
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
         values('Wang Xiaohu','Hommes','1989-08-07',22,120223198908071111,'022-22222222',' Honghe Apartment, Nankai District, Tianjin 5-5-102',1)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
         values(' He XiaoZhang ','Femme','1989-05-06',22,120223198905062426,'022-33333333',' Wang tanchang, district de Hebei, Tianjin 58No.',2)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
         values(' Ma Xiaoli ','Hommes','1990-02-07',21,120223199002078915,'022-44444444',' Tingzi Gu Shuguang Road, Hongqiao District, Tianjin 79No.',4)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
         values(' Feng Xiaoqiang ','Femme','1987-05-12',24,130223198705125167,'022-55555555',default,2)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
         values(' Du xiaoli ','Femme','1986-05-08',25,130223198605081528,'022-66666666',' North Hengshui Road, Hebei 69No.',1)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
         values(' Wang junqiao ','Hommes','1987-07-18',24,130223198707182235,'022-77777777',default,1)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
         values(' Chang Yongli ','Hommes','1988-09-28',24,130223198909282235,'022-88888888',' Fenghua Road, Baoding, Hebei 12No.',3)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
         values('Li Ming','Hommes','1987-01-18',24,130223198701182257,'022-99999999',' Xingtai Xingfu Road, Hebei 5No.',1)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
         values(' Ning junyan ','Femme','1987-06-15',24,130223198706152211,'022-11111111',default,3)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
         values(' Liu Lingling ','Femme','1989-08-19',24,130223198908192235,'022-11111222',default,4)
         
            
         
-- Insérer des informations sur le score 
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)

-- Insérer les informations de l'Administrateur 
insert into Admins (LoginPwd,AdminName) values(123456,' Wang Xiaojun ')
insert into Admins (LoginPwd,AdminName) values(123456,' Zhang Mingli ')

--Va chercher des informations
--delete from Students 

--truncate table Students -- Après avoir supprimé toutes les données , Renuméroter automatiquement les colonnes d'identification 

-- Afficher les informations de l'élève et de la classe 
select * from Students
select * from StudentClass
select * from ScoreList
select * from Admins
use StudentManageDB
go
--Créer une contrainte de clé primaire

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)

--Créer une contrainte 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)

--Créer une contrainte de contrôle
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('Adresse inconnue') for StudentAddress

insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo ,PhoneNumber,
StudentAddress,ClassId)
values('Li Xiaolu','Femme','1989-01-12',24,120229198901121315, '13099012876',default,1)

insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo ,PhoneNumber,
ClassId)
values('Li Xiaolu','Femme','1989-01-12',24,120229198901121316, '13099012876',1)


insert into StudentClass (ClassId,ClassName) values(1,'Logiciels1Ben')
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 Nom,Gender as Sexe,Date de naissance=birthday
from Students where Gender='Hommes'

select Nom=StudentName, Adresse et téléphone =StudentAddress+'【'+PhoneNumber+'】' 
from Students where Gender='Hommes'

select Total des réalisations=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 Nom,Gender as Sexe,Date de naissance=birthday,L'école='Université de Pékin'
from Students where Gender='Hommes'

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 'Tianjin%'

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

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('Wang Xiaohu',' He XiaoZhang ')


select SUM(CSharp) as C#Total des réalisations from ScoreList

select Nombre total de personnes=COUNT(*) from Students 

select MAX(Csharp) as C#Le score le plus élevé ,MIN(CSharp) as C#Score minimum,AVG(CSharp) as C#Score minimum from ScoreList

select Students.StudentId,C#Résultats=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#Résultats=CSharp from Students
left outer join ScoreList on Students.StudentId=ScoreList.StudentId
where Gender='Hommes'

select  Students.StudentId,StudentName,Gender ,C#Résultats=CSharp from ScoreList
left outer join Students on Students.StudentId=ScoreList.StudentId
where Gender='Hommes'

select Classe=StudentClass.ClassName,Nombre de personnes=COUNT(*),C#Le score le plus élevé=Max(CSharp),DBLe score le plus élevé=MAX(SQLServerDB),
AVG(CSharp) as C#Note moyenne,AVG(SQLServerDB) as DBNote moyenne
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

-- En sachant que ce champ est dupliqué 
select StudentId from ScoreList  group by StudentId having COUNT(*)>1

-- Rechercher tous les enregistrements en double 
select * from ScoreList
where StudentId in(select StudentId from ScoreList  group by StudentId having COUNT(*)>1)
order by StudentId

--Autres méthodes
select * from ScoreList
where (select COUNT(*) from ScoreList  s where s.StudentId=ScoreList.StudentId)>1
order by StudentId

-- Filtrer les données en double 
select distinct StudentId,CSharp from ScoreList

select distinct StudentId,CSharp,SQLServerDB from ScoreList

-- Pointez vers la base de données actuellement utilisée 
use master
go
-- Déterminer si la base de données actuelle existe 
if exists (select * from sysdatabases where name='SMDB')
drop database SMDB --Supprimer la base de données
go
--Créer une base de données
create database SMDB
on primary
(
	-- Nom logique du fichier de base de données 
    name='SMDB_data',
    -- Nom du fichier physique de la base de données (Chemin absolu)
    filename='D:\DB\SMDB_data.mdf',
    -- Taille initiale du fichier de base de données 
    size=10MB,
    -- Croissance des fichiers de données 
    filegrowth=1MB
)
--Créer un fichier journal
log on
(
    name='SMDB_log',
    filename='D:\DB\SMDB_log.ldf',
    size=2MB,
    filegrowth=1MB
)
go
-- Créer une fiche de données d'information pour les étudiants 
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,--Numéro d'identification
    CardNo  varchar(20) not null,-- Numéro de carte de présence 
    StuImage text null,-- Photos des stagiaires 
    Age int not null,
    PhoneNumber varchar(50),
    StudentAddress varchar(500),
    ClassId int not null  --Clé étrangère de classe
)
go
--Créer une table de classe
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
--Créer une feuille de notes
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
--Créer une feuille de présence
if exists(select * from sysobjects where name='Attendance')
drop table Attendance
create table Attendance
(
	Id int identity(100000,1) primary key,--Colonne d 'identification
    CardNo varchar(20) not null,-- Numéro de la carte d'étudiant 
    DTime smalldatetime not null --Temps de frappe
)
go
-- Créer une table d'utilisateurs admin 
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
-- Diverses contraintes pour créer des tableaux de données 
use SMDB
go
--Création“Clé primaire”Contraintesprimary 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)

--Créer une contrainte de contrôlecheck
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) 

--Créer une contrainte uniqueunique
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)

-- Créer une contrainte de vérification de la longueur de la carte d'identité 
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)

--Créer une contrainte par défaut 
if exists(select * from sysobjects where name='df_StudentAddress')
alter table Students drop constraint df_StudentAddress
alter table Students 
add constraint df_StudentAddress default ('Adresse inconnue' ) 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

--Créer une contrainte de clé étrangère
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)


-------------------------------------------Insérer des données--------------------------------------
use SMDB
go

--Insérer les données de classe
insert into StudentClass(ClassId,ClassName) values(1,'Logiciels1Ben')
insert into StudentClass(ClassId,ClassName) values(2,'Logiciels2Ben')
insert into StudentClass(ClassId,ClassName) values(3,'Ordinateur1Ben')
insert into StudentClass(ClassId,ClassName) values(4,'Ordinateur2Ben')
insert into StudentClass(ClassId,ClassName) values(5,'Réseau1Ben')
insert into StudentClass(ClassId,ClassName) values(6,'Réseau2Ben')

-- Insérer des informations sur l'étudiant 
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values('Wang Xiaohu','Hommes','1989-08-07',22,120223198908071111,'0004018766','022-22222222',' Honghe Apartment, Nankai District, Tianjin 5-5-102',1)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values(' He XiaoZhang ','Femme','1989-05-06',22,120223198905062426,'0006394426','022-33333333',' Wang tanchang, district de Hebei, Tianjin 58No.',2)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values(' Ma Xiaoli ','Hommes','1990-02-07',21,120223199002078915,'0006073516','022-44444444',' Tingzi Gu Shuguang Road, Hongqiao District, Tianjin 79No.',4)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values(' Feng Xiaoqiang ','Femme','1987-05-12',24,130223198705125167,'0006254540','022-55555555',default,2)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values(' Du xiaoli ','Femme','1986-05-08',25,130223198605081528,'0006403803','022-66666666',' North Hengshui Road, Hebei 69No.',1)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values(' Wang junqiao ','Hommes','1987-07-18',24,130223198707182235,'0006404372','022-77777777',default,1)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values(' Chang Yongli ','Hommes','1988-09-28',24,130223198909282235,'0006092947','022-88888888',' Fenghua Road, Baoding, Hebei 12No.',3)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values('Li Ming','Hommes','1987-01-18',24,130223198701182257,'0006294564','022-99999999',' Xingtai Xingfu Road, Hebei 5No.',1)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values(' Ning junyan ','Femme','1987-06-15',24,130223198706152211,'0006092450','022-11111111',default,3)
insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values(' Liu Lingling ','Femme','1989-08-19',24,130223198908192235,'0006069457','022-11111222',default,4)
         
         insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values(' Wang Xiaojun ','Femme','1986-05-08',25,130224198605081528,'0006403820','022-66666666',' North Hengshui Road, Hebei 69No.',1)
         insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values(' Liu xiaoli ','Femme','1986-05-08',25,130225198605081528,'0006403821','022-66666666',' North Hengshui Road, Hebei 69No.',1)
         insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values(' Zhang huixin ','Femme','1986-05-08',25,130226198605081528,'0006403822','022-66666666',' North Hengshui Road, Hebei 69No.',1)
         insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values(' Li suyun ','Femme','1986-05-08',25,130227198605081528,'0006403823','022-66666666',' North Hengshui Road, Hebei 69No.',1)
         insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values(' Zhao Xiaojin ','Femme','1986-05-08',25,130228198605081528,'0006403824','022-66666666',' North Hengshui Road, Hebei 69No.',1)
         insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values('Wang haoyu','Hommes','1986-05-08',25,130229198605081528,'0006403825','022-66666666',' North Hengshui Road, Hebei 69No.',1)
         insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values(' Cui Yongxin ','Femme','1986-05-08',25,130222198605081528,'0006403826','022-66666666',' North Hengshui Road, Hebei 69No.',1)
         insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values(' Bao liyun ','Femme','1986-05-08',25,130220198605081528,'0006403827','022-66666666',' North Hengshui Road, Hebei 69No.',1)
         
         insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values(' Sun Liyuan ','Femme','1986-05-08',25,130228198605081530,'0006403854','022-66666666',' North Hengshui Road, Hebei 69No.',1)
         insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values(' Hao Zhiyun ','Hommes','1986-05-08',25,130229198605081531,'0006403855','022-66666666',' North Hengshui Road, Hebei 69No.',1)
         insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values('Wang Baohua','Femme','1986-05-08',25,130222198605081532,'0006403856','022-66666666',' North Hengshui Road, Hebei 69No.',1)
         insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,CardNo,PhoneNumber,StudentAddress,ClassId)
         values(' Li Liying ','Femme','1986-05-08',25,130220198605081544,'0006403857','022-66666666',' North Hengshui Road, Hebei 69No.',1)
         
         
         
-- Insérer des informations sur le score 
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)

-- Insérer les informations de l'Administrateur 
insert into Admins (LoginPwd,AdminName) values(123456,' Wang Xiaojun ')
insert into Admins (LoginPwd,AdminName) values(123456,' Zhang Mingli ')

--Va chercher des informations
--delete from Students 

--truncate table Students -- Après avoir supprimé toutes les données , Renuméroter automatiquement les colonnes d'identification 

-- Afficher les informations de l'élève et de la classe 
select * from Students
select * from StudentClass
select * from ScoreList
select * from Admins
select * from Attendance





版权声明
本文为[La lave de Rodinia]所创,转载请带上原文链接,感谢
https://chowdera.com/2022/01/202201080558307287.html

随机推荐