首页 > 数据库 >sql: SQLite,MySQL,SQL Server

sql: SQLite,MySQL,SQL Server

时间：2023-06-25 22:11:08浏览次数：51  
标签：SQLite StudentId -- StudentBirthday SQL MySQL go id select

SQLite 3

CREATE TABLE DuStudentList (
	StudentId	INTEGER PRIMARY KEY AUTOINCREMENT,
	StudentName	TEXT NOT NULL,
	StudentNO	TEXT NOT NULL,
	StudentBirthday	DATETIME
	
);


CREATE TABLE DuCourse (
	CourseId	INTEGER,
	CourseName	TEXT NOT NULL,
	PRIMARY KEY("CourseId" AUTOINCREMENT)
);

CREATE TABLE DuGradeClass (
	ClassId	INTEGER,
	ClassName	TEXT NOT NULL,
	PRIMARY KEY("ClassId" AUTOINCREMENT)
);



CREATE TABLE DuStudentScore(
	ScoreId	INTEGER PRIMARY KEY AUTOINCREMENT,
	StudentId	INTEGER,
	CourseId	INTEGER,
	Score	NUMERIC,
	FOREIGN KEY(StudentId) REFERENCES DuStudentList(StudentId) ,
	FOREIGN KEY(CourseId) REFERENCES DuCourse(ClassId) 
	
);

CREATE TABLE DuStudentScore(
	ScoreId	INTEGER PRIMARY KEY AUTOINCREMENT,
	StudentId	INTEGER,
	CourseId	INTEGER,
	Score	NUMERIC,
	FOREIGN KEY(StudentId) REFERENCES DuStudentList(StudentId) ,
	FOREIGN KEY(CourseId) REFERENCES DuCourse(ClassId) 
	
)


create table DuStudentClass(
	StudentClassId INTEGER PRIMARY KEY AUTOINCREMENT,
	StudentId INTEGER,        
	ClassId INTEGER,
	Foreign Key(StudentId) REFERENCES DuStudentList(StudentId),  
	Foreign Key(ClassId) REFERENCES DuGradeClass(ClassId)
);

　　

MySQL 8.0

create database geovindu CHARACTER SET utf8mb4;

use geovindu;

# 当日过了就增加1岁 mysql 2023-06-19
select TIMESTAMPDIFF(year,'1996-09-09',now()) as age;
select TIMESTAMPDIFF(year,'1977-06-18',now()) as age;
select TIMESTAMPDIFF(year,'1977-06-19',now()) as age;
select TIMESTAMPDIFF(year,'1977-06-20',now()) as age;
select TIMESTAMPDIFF(year,'1977-02-14',now()) as age;


drop table InsuranceMoney;

#保险表
create table InsuranceMoney
(
    ID  INT NOT NULL AUTO_INCREMENT comment'主键id',  #自动增加,
    InsuranceName nvarchar(50) comment'保险类型名称',
    InsuranceCost float comment'保险费用',
    IMonth int  null comment'交费月份',
    PRIMARY KEY(ID)  # 主键
 ) comment='保险表';
 
 drop table DuStudentList;
 #学生表
 create table DuStudentList
(
     StudentId INT NOT NULL AUTO_INCREMENT comment'主键id',  #自动增加,
     StudentName nvarchar(50) comment'学生姓名',
     StudentNO varchar(50) comment'学号',                    #学号
     StudentBirthday datetime comment'学生生日',                  #学生生日      
     primary key(StudentId) 
) comment='学生表';

insert into DuStudentList(StudentName,StudentNO,StudentBirthday) values(N'刘三','001','2007-12-27');

insert into DuStudentList(StudentName,StudentNO,StudentBirthday) values(N'王二','002','2008-2-14');

select * from DuStudentList;

 #课程名称
DROP TABLE DuCourse;

create table DuCourse
(
   CourseId INT NOT NULL AUTO_INCREMENT comment'主键id',  #自动增加 
   CourseName nvarchar(50) comment'课程名称',             #课程名称
   PRIMARY KEY(CourseId)
) comment='课程名称表';

insert into DuCourse(CourseName) values(N'语文');

insert into DuCourse(CourseName) values(N'英语');

insert into DuCourse(CourseName) values(N'数学');

select * from DuCourse;



#班表名称
DROP TABLE DuGradeClass;

create table DuGradeClass
(
    ClassId INT NOT NULL AUTO_INCREMENT comment'主键ID',
    ClassName nvarchar(50) comment'班级名称',             #班级名称
    PRIMARY KEY(ClassId)
) comment='班级名称表';

alter table DuGradeClass comment '班级名称表';

insert into DuGradeClass(ClassName) values(N'二年级一班');
 
insert into DuGradeClass(ClassName) values(N'二年级二班');

select * from DuGradeClass;




#成绩表
DROP TABLE DuStudentScore;

create table DuStudentScore
(
     ScoreId INT NOT NULL AUTO_INCREMENT comment'ID 主键',
     StudentId int comment'学生ID 外键',  #学生ID 外键
     CourseId int comment'课程ID 外键',      #课程ID 外键
     Score float comment'成绩',   #成绩
     PRIMARY KEY(ScoreId),
     Foreign Key(StudentId) REFERENCES DuStudentList(StudentId), #学生ID 外键
     Foreign Key(CourseId) REFERENCES DuCourse(CourseId) #课程ID 外键
) comment='成绩表';

insert into DuStudentScore(StudentId,CourseId,Score) values(1,1,90);
insert into DuStudentScore(StudentId,CourseId,Score) values(1,2,56);
insert into DuStudentScore(StudentId,CourseId,Score) values(1,3,80);

 
insert into DuStudentScore(StudentId,CourseId,Score) values(2,1,92);
insert into DuStudentScore(StudentId,CourseId,Score) values(2,2,83);
insert into DuStudentScore(StudentId,CourseId,Score) values(2,3,78);

select * from DuStudentScore;



#班级学生
DROP TABLE DuStudentClass;

create table DuStudentClass
(
    StudentClassId INT NOT NULL AUTO_INCREMENT comment'主键id',
    StudentId int comment'学生ID 外键',   #学生ID 外键
    ClassId int comment'班级ID 外键',     #班级ID 外键
	PRIMARY KEY(StudentClassId),
    Foreign Key(StudentId) REFERENCES DuStudentList(StudentId),
    Foreign Key(ClassId) REFERENCES DuGradeClass(ClassId)
)comment='班级学生表';

insert into DuStudentClass(StudentId,ClassId) values(1,1);

insert into DuStudentClass(StudentId,ClassId) values(2,1);

select * from DuStudentClass;

DELIMITER $$
CREATE FUNCTION isEligible(
age INTEGER
)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
IF age > 18 THEN
RETURN ("yes");
ELSE
RETURN ("No");
END IF;
END$$
DELIMITER ;

#Mysql 8.0
set @tfScore=0; # float;
set @inputStudentId=2; # int
SELECT sum(Score) into @tfScore from DuStudentScore where  StudentId=@InputStudentId;
SELECT @tfScore;   


#函数
DELIMITER $$   
CREATE function DufunGetStudentScore(inputStudentId INTEGER) returns float DETERMINISTIC
BEGIN
	declare tfScore float;
	SELECT sum(Score) into  tfScore from DuStudentScore where  StudentId=inputStudentId;
	return  tfScore;
END$$
DELIMITER ;

select TIMESTAMPDIFF(hour,'1977-02-14',CURDATE())/8766 as Age;


select DufunGetStudentScore(2);

# 成绩视图  StudentScoreList
DROP  VIEW ViewStudentScore;
# ,TIMESTAMPDIFF(hour,b.StudentBirthday,CURDATE())/8766 as Age
CREATE VIEW  ViewStudentScore
AS
select a.*,c.CourseName,b.StudentNO,b.StudentName,b.StudentBirthday,TIMESTAMPDIFF(hour,b.StudentBirthday,CURDATE())/8766 AS age from DuStudentScore as a
LEFT JOIN DuStudentList as b on a.StudentId=b.StudentId
LEFT JOIN DuCourse as c on a.CourseId =c.CourseId
LEFT JOIN DuStudentClass as d on d.StudentId=b.StudentId;

select * from ViewStudentScore;

#行列转换
# PIVOT  https://mysql.rjweb.org/doc.php/pivot  #https://ubiq.co/database-blog/how-to-create-dynamic-pivot-tables-in-mysql/
select StudentName as '姓名',
sum(case CourseName when '语文' then Score else 0 end) 语文,sum(case CourseName when '数学' then Score else 0 end) 数学,
sum(case CourseName when '英语' then Score else 0 end) 英语 from ViewStudentScore group by StudentName;



# 班级视图 StudentClassList
DROP  VIEW ViewStudentClass;

CREATE VIEW  ViewStudentClass
AS
select a.*,d.ClassName,b.StudentNO,b.StudentName,b.StudentBirthday,TIMESTAMPDIFF(hour,b.StudentBirthday,CURDATE())/8766 AS age,DufunGetStudentScore(a.StudentId) totalScore from DuStudentClass as a
left join DuStudentList as b on a.StudentId=b.StudentId
left join DuGradeClass as d on a.ClassId=d.ClassId;

　　

 

sql server 2019:

/*create database geovindu
go

use geovindu
go
*/






SELECT  tab.name AS 'TableName',  idx.name AS 'PrimaryName',  col.name AS 'FieldName',c.name AS 'TypeName',col.max_length AS 'Length',b.value AS 'TableDescription',col.is_nullable as 'IS_NULL',COLUMNPROPERTY(col.object_id,col.name,'PRECISION') AS'DataLength',isnull(COLUMNPROPERTY(col.object_id,col.name,'Scale'),0) as N'NumberLength' FROM sys.indexes idx,sys.index_columns idxCol,sys.tables tab,sys.columns col,sys.systypes AS c,sys.extended_properties AS b
 WHERE idx.object_id = idxCol.object_id AND idx.index_id = idxCol.index_id AND idx.is_primary_key = 1 
 AND idx.object_id = tab.object_id AND idx.object_id = col.object_id AND idxCol.column_id = col.column_id 
 AND  col.user_type_id=c.xusertype 
 AND b.minor_id=0
 AND tab.object_id=b.major_id 


 
 SELECT  tab.name AS 'TableName',  idx.name AS 'PrimaryName',  col.name AS 'FieldName',c.name AS 'TypeName',col.max_length AS 'Length',b.value AS 'TableDescription',col.is_nullable as 'IS_NULL',COLUMNPROPERTY(col.object_id,col.name,'PRECISION') AS'DataLength',isnull(COLUMNPROPERTY(col.object_id,col.name,'Scale'),0) as N'NumberLength' FROM sys.indexes idx,sys.index_columns idxCol,sys.tables tab,sys.columns col,sys.systypes AS c,sys.extended_properties AS b
 WHERE idx.object_id = idxCol.object_id AND idx.index_id = idxCol.index_id AND idx.is_primary_key = 1 
 AND idx.object_id = tab.object_id AND idx.object_id = col.object_id AND idxCol.column_id = col.column_id 
 AND  col.user_type_id=c.xusertype 
 AND b.minor_id=0

 select * from sys.extended_properties
 go

 -- 获取表的描述
  SELECT  tab.name AS 'TableName',  idx.name AS 'PrimaryName',  col.name AS 'FieldName',c.name AS 'TypeName',col.max_length AS 'Length',b.value AS 'TableDescription',col.is_nullable as 'IS_NULL',COLUMNPROPERTY(col.object_id,col.name,'PRECISION') AS'DataLength',isnull(COLUMNPROPERTY(col.object_id,col.name,'Scale'),0) as N'NumberLength' FROM sys.indexes idx,sys.index_columns idxCol,sys.tables tab,sys.columns col,sys.systypes AS c,sys.extended_properties AS b
 WHERE idx.object_id = idxCol.object_id AND idx.index_id = idxCol.index_id AND idx.is_primary_key = 1 
 AND idx.object_id = tab.object_id AND idx.object_id = col.object_id AND idxCol.column_id = col.column_id 
 AND  col.user_type_id=c.xusertype 
 AND b.minor_id=0


DROP TABLE InsuranceMoney
GO
create table InsuranceMoney
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    InsuranceName nvarchar(50),
    InsuranceCost float,
    IMonth int 
 )
 go

-- 表描述
EXECUTE sp_addextendedproperty N'MS_Description', '保险费用导入表', N'SCHEMA', N'dbo', N'table', N'InsuranceMoney', NULL, NULL
--列的描述
EXECUTE sp_addextendedproperty N'MS_Description', 'ID', N'user', N'dbo', N'table', N'InsuranceMoney', N'column', N'ID'
EXECUTE sp_addextendedproperty N'MS_Description', '保险名称', N'user', N'dbo', N'table', N'InsuranceMoney', N'column', N'InsuranceName'
EXECUTE sp_addextendedproperty N'MS_Description', '保险交费金额', N'user', N'dbo', N'table', N'InsuranceMoney', N'column', N'InsuranceCost'
EXECUTE sp_addextendedproperty N'MS_Description', '交费月份', N'user', N'dbo', N'table', N'InsuranceMoney', N'column', N'IMonth'
go

 select * from InsuranceMoney
 go

 /*
学生类:姓名、年龄、学号、成绩
 
班级类:班级名称、学生列表显示所有学生
根据学号查找学生
添加一个学生
删除一个学生(学生对象、学号)
根据学号升序排序
根据成绩降序排序
 
*/
 
--学生表
DROP TABLE DuStudentList
GO
create table DuStudentList
(
     StudentId INT IDENTITY(1,1) PRIMARY KEY,
     StudentName nvarchar(50),
     StudentNO varchar(50),                    --学号
     StudentBirthday datetime                  --学生生日      
 
)
go
 
 -- 表描述
EXECUTE sp_addextendedproperty N'MS_Description', '学生表', N'SCHEMA', N'dbo', N'table', N'DuStudentList', NULL, NULL
--列的描述
EXECUTE sp_addextendedproperty N'MS_Description', 'ID', N'user', N'dbo', N'table', N'DuStudentList', N'column', N'StudentId'
EXECUTE sp_addextendedproperty N'MS_Description', '姓名', N'user', N'dbo', N'table', N'DuStudentList', N'column', N'StudentName'
EXECUTE sp_addextendedproperty N'MS_Description', '学号', N'user', N'dbo', N'table', N'DuStudentList', N'column', N'StudentNO'
EXECUTE sp_addextendedproperty N'MS_Description', '出生日期', N'user', N'dbo', N'table', N'DuStudentList', N'column', N'StudentBirthday'
go



Select StudentId,StudentName,StudentNO, StudentBirthday, getdate() as CurrentDate, DATEDIFF(hour,StudentBirthday,GETDATE())/8766 as age from DuStudentList
go


update DuStudentList set StudentName='geovindu', StudentNO='006', StudentBirthday='2008-02-14' where StudentId=6

 --1
Select StudentId,StudentName,StudentNO, StudentBirthday, getdate() as CurrentDate, year(getdate())-year(StudentBirthday) as age,DATEDIFF(hour,StudentBirthday,GETDATE())/8766 as newage from DuStudentList
go
 
select * from DuStudentList order by StudentName desc

--2 
Select StudentId,StudentName,StudentNO, StudentBirthday,getdate() as CurrentDate, datediff(YY,StudentBirthday,getdate()) as age 
from DuStudentList
go



select datediff(YY,'1977-06-18',getdate()) as age 
select datediff(YY,'1977-06-19',getdate()) as age 
select datediff(YY,'1977-06-20',getdate()) as age 

--3 错误
Select StudentId,StudentName,StudentNO, StudentBirthday,getdate() as CurrentDate,
datediff(MONTH,StudentBirthday, getdate())/12 - case when month(StudentBirthday)=month(getdate()) and day(StudentBirthday) > day(getdate()) then 1 else 0 end
as age from DuStudentList
go

Select datediff(MONTH,'1977-06-18', getdate())/12 - case when month('1977-06-18')=month(getdate()) and day('1977-06-18') > day(getdate()) then 1 else 0 end as age
go

Select datediff(MONTH,'1977-06-19', getdate())/12 -case when month('1977-06-19')=month(getdate()) and day('1977-06-19') > day(getdate()) then 1 else 0 end as age
go

Select datediff(MONTH,'1977-06-20', getdate())/12 -case when month('1977-06-20')=month(getdate()) and day('1977-06-20') > day(getdate()) then 1 else 0 end as age
go

--4 
 Select datediff(MONTH,'1977-06-18', getdate())/12
 Select datediff(MONTH,'1977-06-19', getdate())/12
 Select datediff(MONTH,'1977-06-20', getdate())/12

 WITH AgeCTE AS
(
	SELECT StudentId,StudentName,StudentNO, StudentBirthday, CASE WHEN DATEADD(yy, DATEDIFF(yy, StudentBirthday, GETDATE()), StudentBirthday) < GETDATE() THEN DATEDIFF(yy, StudentBirthday, GETDATE()) ELSE DATEDIFF(yy, StudentBirthday, GETDATE()) - 1 END AS Age FROM DuStudentList
)
SELECT * FROM AgeCTE
go

select CASE WHEN DATEADD(yy, DATEDIFF(yy, '1977-06-18', GETDATE()), '1977-06-18') < GETDATE() THEN DATEDIFF(yy, '1977-06-18', GETDATE()) ELSE DATEDIFF(yy, '1977-06-18', GETDATE()) - 1 END AS Age
go


declare @now datetime
set @now=getdate()
SELECT FLOOR(DATEDIFF(DAY,'1977-6-18', @now) /365)
go

SELECT DATEDIFF(hour,'1977-6-18',GETDATE())/8766 AS AgeYearsIntTrunc

--1 erro
DECLARE @date datetime, @tmpdate datetime, @years int, @months int, @days int
SELECT @date = '1977-6-20'

SELECT @tmpdate = @date

SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())

SELECT @years, @months, @days
go



select datediff(year,[StudentBirthday], getdate()) - case when month([StudentBirthday]) > month(getdate()) or (month([StudentBirthday]) = month(getdate()) and day([StudentBirthday]) > day(getdate())) then 1 else 0 end age from DuStudentList


DECLARE @CurrentDate DATE = CAST(GETDATE() AS DATE), @DOB DATE = '1967-06-20', @Years INT = 0, @Months INT = 0, @Days INT = 0
SET @Years = DATEDIFF(DAY,@DOB,@CurrentDate)/365
SET @Months = DATEDIFF(MONTH,DATEADD(YEAR,DATEDIFF(DAY,@DOB,@CurrentDate)/365,@DOB),@CurrentDate)
SET @Days = DATEDIFF(DAY,DATEADD(Month,DATEDIFF(MONTH,DATEADD(YEAR,DATEDIFF(DAY,@DOB,@CurrentDate)/365,@DOB),@CurrentDate),DATEADD(YEAR,DATEDIFF(DAY,@DOB,@CurrentDate)/365,@DOB)),@CurrentDate)
SELECT CONCAT (@Years, ' Years, ', @Months, ' Months and ', @Days, ' Days') AS DateOfBirth

DECLARE @StartDate datetime, @EndDate datetime
set @StartDate='1977-06-21'
set @EndDate=getdate()
SELECT (DATEDIFF(MONTH, @StartDate, @EndDate) - CASE WHEN DATEPART(DAY, @StartDate) > DATEPART(DAY, @EndDate) THEN -1 ELSE 0 END) / 12 AS AgeInYears
go

declare @as_of datetime,@bday datetime
SET @as_of = GETDATE()
SET @bday = 'mm/dd/yyyy'
select (0 + Convert(Char(8),@as_of,112) - Convert(Char(8),@bday,112)) / 10000

SELECT *,  
    CASE 
        WHEN MONTH(GETDATE()) > MONTH([StudentBirthday]) OR MONTH(GETDATE()) = MONTH([StudentBirthday]) AND DAY(GETDATE()) >= DAY([StudentBirthday]) THEN DATEDIFF(year, [StudentBirthday], GETDATE()) 
        ELSE DATEDIFF(year, [StudentBirthday], GETDATE()) - 1 
    END AS age
FROM DuStudentList


--https://stackoverflow.com/questions/1572110/how-to-calculate-age-in-years-based-on-date-of-birth-and-getdate
--以当天具体时间为准，过了增加一岁 AgeYearsIntTrunc
DECLARE @dob  datetime
SET @dob='1977-06-19 20:00:00'

SELECT DATEDIFF(hour,@dob,GETDATE())/8766.0 AS AgeYearsDecimal
    ,CONVERT(int,ROUND(DATEDIFF(hour,@dob,GETDATE())/8766.0,0)) AS AgeYearsIntRound
    ,DATEDIFF(hour,@dob,GETDATE())/8766 AS AgeYearsIntTrunc


DECLARE @Now  datetime, @Dob datetime
SELECT   @Now='1990-05-05', @Dob='1980-05-05'  --results in 10
--SELECT @Now='1990-05-04', @Dob='1980-05-05'  --results in  9
--SELECT @Now='1989-05-06', @Dob='1980-05-05'  --results in  9
--SELECT @Now='1990-05-06', @Dob='1980-05-05'  --results in 10
--SELECT @Now='1990-12-06', @Dob='1980-05-05'  --results in 10
--SELECT @Now='1991-05-04', @Dob='1980-05-05'  --results in 10

SELECT
    (CONVERT(int,CONVERT(char(8),@Now,112))-CONVERT(char(8),@Dob,112))/10000 AS AgeIntYears



insert into DuStudentList(StudentName,StudentNO,StudentBirthday) values(N'刘三','001','2007-12-27')
go
insert into DuStudentList(StudentName,StudentNO,StudentBirthday) values(N'王二','002','2008-2-14')
go

insert into DuStudentList(StudentName,StudentNO,StudentBirthday) values(N'涂聚文','003','1977-2-14')
go
insert into DuStudentList(StudentName,StudentNO,StudentBirthday) values(N'geovindu','004','2008-12-14')
go
 
select * from DuStudentList
go
 
 
--课程名称
DROP TABLE DuCourse
GO
create table DuCourse
(
   CourseId INT IDENTITY(1,1) PRIMARY KEY,
   CourseName nvarchar(50)                  --课程名称
)
go
 
-- 表描述
EXECUTE sp_addextendedproperty N'MS_Description', '课程表', N'SCHEMA', N'dbo', N'table', N'DuCourse', NULL, NULL
--列的描述
EXECUTE sp_addextendedproperty N'MS_Description', 'ID', N'user', N'dbo', N'table', N'DuCourse', N'column', N'CourseId'
EXECUTE sp_addextendedproperty N'MS_Description', '课程名称', N'user', N'dbo', N'table', N'DuCourse', N'column', N'CourseName'
go

insert into DuCourse(CourseName) values(N'语文')
go
insert into DuCourse(CourseName) values(N'英语')
go
insert into DuCourse(CourseName) values(N'数学')
go
 
select * from DuCourse
go
 
 
 
 
--班表名称
DROP TABLE DuGradeClass
GO
create table DuGradeClass
(
    ClassId INT IDENTITY(1,1) PRIMARY KEY,
    ClassName nvarchar(50)              --班级名称
)
go

-- 表描述
EXECUTE sp_addextendedproperty N'MS_Description', '班级表', N'SCHEMA', N'dbo', N'table', N'DuGradeClass', NULL, NULL
--列的描述
EXECUTE sp_addextendedproperty N'MS_Description', 'ID', N'user', N'dbo', N'table', N'DuGradeClass', N'column', N'ClassId'
EXECUTE sp_addextendedproperty N'MS_Description', '班级名称', N'user', N'dbo', N'table', N'DuGradeClass', N'column', N'ClassName'
go


insert into DuGradeClass(ClassName) values(N'二年级一班')
go
 
insert into DuGradeClass(ClassName) values(N'二年级二班')
go
 
select * from DuGradeClass
go
 
 
 
--成绩表
DROP TABLE DuStudentScore
GO
create table DuStudentScore
(
     ScoreId INT IDENTITY(1,1) PRIMARY KEY,
     StudentId int
        Foreign Key REFERENCES DuStudentList(StudentId),  --学生ID 外键
     CourseId int
        Foreign Key REFERENCES DuCourse(CourseId),      --课程ID 外键
     Score float                                      --成绩
)
go
-- 表描述
EXECUTE sp_addextendedproperty N'MS_Description', '学生表', N'SCHEMA', N'dbo', N'table', N'DuStudentScore', NULL, NULL
--列的描述
EXECUTE sp_addextendedproperty N'MS_Description', 'ID', N'user', N'dbo', N'table', N'DuStudentScore', N'column', N'ScoreId'
EXECUTE sp_addextendedproperty N'MS_Description', '学生ID 外键', N'user', N'dbo', N'table', N'DuStudentScore', N'column', N'StudentId'
EXECUTE sp_addextendedproperty N'MS_Description', '课程ID 外键', N'user', N'dbo', N'table', N'DuStudentScore', N'column', N'CourseId'
EXECUTE sp_addextendedproperty N'MS_Description', '成绩', N'user', N'dbo', N'table', N'DuStudentScore', N'column', N'Score'
go


insert into DuStudentScore(StudentId,CourseId,Score) values(1,1,90)
insert into DuStudentScore(StudentId,CourseId,Score) values(1,2,56)
insert into DuStudentScore(StudentId,CourseId,Score) values(1,3,80)
go
 
insert into DuStudentScore(StudentId,CourseId,Score) values(2,1,92)
insert into DuStudentScore(StudentId,CourseId,Score) values(2,2,83)
insert into DuStudentScore(StudentId,CourseId,Score) values(2,3,78)
go

insert into DuStudentScore(StudentId,CourseId,Score) values(3,1,20)
insert into DuStudentScore(StudentId,CourseId,Score) values(3,2,45)
insert into DuStudentScore(StudentId,CourseId,Score) values(3,3,30)
go
 
insert into DuStudentScore(StudentId,CourseId,Score) values(4,1,99)
insert into DuStudentScore(StudentId,CourseId,Score) values(4,2,88)
insert into DuStudentScore(StudentId,CourseId,Score) values(4,3,68)
go
 
select * from DuStudentScore
go

--得到学生的各科成绩总分
drop function dbo.DufunGetStudentScore 
go 
CREATE function dbo.DufunGetStudentScore
(
	
	@StudentId int  --会员ID

) 
returns float
as 
begin
declare @tScore float
SELECT @tScore=isnull(sum(Score),0) from dbo.DuStudentScore where  StudentId=@StudentId
return @tScore
end 
GO


 
 --平均成绩
SELECT 'sum Score' AS Score,   
  [1], [2], [3], [4]  
FROM  
(
  SELECT CourseId, Score   
  FROM DuStudentScore
) AS StudentScore  
PIVOT  
(  
  sum(Score)  
  FOR CourseId IN ([1], [2], [3], [4])  
) AS PivotTable;  
 --SQL SERVER 2005动态SQL


 SELECT 'sum Score' AS Score,   
  [1], [2], [3], [4]  
FROM  
(
  SELECT StudentId, Score   
  FROM DuStudentScore
) AS StudentScore  
PIVOT  
(  
  sum(Score)  
  FOR StudentId IN ([1], [2], [3], [4])  
) AS PivotTable;  



 
---班级学生
DROP TABLE DuStudentClass
GO
create table DuStudentClass
(
    StudentClassId INT IDENTITY(1,1) PRIMARY KEY,
    StudentId int
        Foreign Key REFERENCES DuStudentList(StudentId),   --学生ID 外键
    ClassId int
        Foreign Key REFERENCES DuGradeClass(ClassId),     --班级ID 外键
)
go
-- 表描述
EXECUTE sp_addextendedproperty N'MS_Description', '学生表', N'SCHEMA', N'dbo', N'table', N'DuStudentClass', NULL, NULL
--列的描述
EXECUTE sp_addextendedproperty N'MS_Description', 'ID', N'user', N'dbo', N'table', N'DuStudentClass', N'column', N'StudentClassId'
EXECUTE sp_addextendedproperty N'MS_Description', '学生ID 外键', N'user', N'dbo', N'table', N'DuStudentClass', N'column', N'StudentId'
EXECUTE sp_addextendedproperty N'MS_Description', '班级ID 外键', N'user', N'dbo', N'table', N'DuStudentClass', N'column', N'ClassId'

go

select *,DATEDIFF(hour,StudentBirthday,GETDATE())/8766 as Age from DuStudentList


insert into DuStudentClass(StudentId,ClassId) values(1,1)
go
insert into DuStudentClass(StudentId,ClassId) values(2,1)
go

insert into DuStudentClass(StudentId,ClassId) values(3,1)
go
insert into DuStudentClass(StudentId,ClassId) values(4,1)
go
select * from DuStudentClass
go
 
-- 成绩视图  StudentScoreList
DROP  VIEW ViewStudentScore
GO

CREATE VIEW  ViewStudentScore
AS
select a.*,c.CourseName,b.StudentNO,b.StudentName,b.StudentBirthday,DATEDIFF(hour,b.StudentBirthday,GETDATE())/8766 as Age from DuStudentScore as a
LEFT JOIN DuStudentList as b on a.StudentId=b.StudentId
LEFT JOIN DuCourse as c on a.CourseId =c.CourseId
LEFT JOIN DuStudentClass as d on d.StudentId=b.StudentId
go

select * from ViewStudentScore
go

 select * from ViewStudentScore order by StudentNO desc , Score desc


select StudentName as '姓名',
sum(case CourseName when '语文' then Score else 0 end) 语文,sum(case CourseName when '数学' then Score else 0 end) 数学,
sum(case CourseName when '英语' then Score else 0 end) 英语 from ViewStudentScore group by StudentName

--成绩单 可以用存储过程，可以动态的显示科目，根据增加的科目动态变化 Chinese Math English
CREATE VIEW  ViewStudentScoreTotal
AS
select * from
(select StudentId,StudentName,CourseName,Score,StudentNO,StudentBirthday,Age,dbo.DufunGetStudentScore(StudentId) totalScore  from ViewStudentScore) test pivot(sum(Score) for CourseName in(语文,数学,英语)
) pvt
go

select * from ViewStudentScoreTotal 
go
select * from ViewStudentScoreTotal order by 语文 desc;
select * from ViewStudentScoreTotal order by Age desc;

-- 班级视图 StudentClassList
DROP  VIEW ViewStudentClass
GO

CREATE VIEW  ViewStudentClass
AS
select a.*,d.ClassName,b.StudentNO,b.StudentName,b.StudentBirthday,DATEDIFF(hour,b.StudentBirthday,GETDATE())/8766 as Age,dbo.DufunGetStudentScore(a.StudentId) totalScore from DuStudentClass as a
left join DuStudentList as b on a.StudentId=b.StudentId
left join DuGradeClass as d on a.ClassId=d.ClassId
go



alter proc pro_test
@userImages varchar(200),
@Subject varchar(20),
@Subject1 varchar(200),
@TableName varchar(50)
as
 declare @sql varchar(max)='select * from (select '+@userImages+' from'+@TableName+') tab
pivot
(
sum('+@Subject+') for Subject('+@Subject1+')
) pvt'
exec (@sql)
go
exec pro_test 'UserName,Subject,Source',

'TestTable',
'Subject',
'语文，数学，英语'

　　

标签：SQLite,StudentId,--,StudentBirthday,SQL,MySQL,go,id,select
From： https://www.cnblogs.com/geovindu/p/17504121.html

相关文章

  • PostgreSQL插件那么多，怎样管理最高效？
    云服务环境下，如何让客户更方便地在各个PostgreSQL的版本下安装插件和扩展功能，成为云服务厂商的一个挑战。华为云RDSforPostgreSQL通过插件管理功能，很好地解决了PostgreSQL版本与插件耦合的问题，帮助用户更直观、更快速地安装管理数据库插件。关于PostgreSQL插件众所周知，PostgreSQL......
  • Microsoft.Data.Sqlite.SqliteException:“SQLite Error 1: 'no such column: t.Value
    使用EFcore获取SQLite的sequence时一直有此错误：Microsoft.Data.Sqlite.SqliteException:“SQLiteError1:'nosuchcolumn:t.Value'.” 以下是出错代码：varid=JVDbContext.Instance.Database.SqlQuery<int>($"SELECTseq+1FROMsqlite_sequenceWHEREname=&#......
  • SqlServer优化排查
    --查看死锁进程spidsp_who2active--进程持续排行cpu：SELECT*FROMsys.sysprocessesorderbycpudesc--查询造成阻塞或死锁的Sql语句：dbccinputbuffer(520)--查看数据库列表select*frommaster..sysdatabases查看锁表：selectrequest_session_idspid,OBJECT_N......
  • redis,mongo,mysql,es区别
    Redis、MongoDB、MySQL和Elasticsearch（ES）都是常用的数据库系统，各有不同的特点和适用场景，具体区别如下：Redis：Redis是一种高性能键值存储数据库，基于内存操作，支持数据持久化，支持数据类型丰富灵活，如字符串、哈希、列表、集合、有序集合等。Redis还提供了订阅/发布、事务、Lua脚本、......
  • Postgresql 9.2安装配置教程（Windows）
    1、安装Postgresql9.2 https://blog.csdn.net/LY1201A/article/details/1064287332、安装postgishttps://www.codenong.com/cs107080664/3、配置连接https://www.cnblogs.com/xysgis/articles/16513814.html4、PostgreSQL安装，出现Failedtoloadsqlmodulesintothedat......
  • 构建高可用性的 SQL Server：Docker 容器下的主从同步实现
    摘要：本文将介绍如何在Docker环境下搭建MSSQLServer的主从同步，帮助读者了解主从同步的原理和实现方式，进而提高数据的可靠性和稳定性。一、前言在当今信息化的时代，数据的安全性和稳定性显得尤为重要。数据库是许多企业和组织存储和管理数据的核心，因此如何保证数据库的高可用......
  • Java学习之Mysql基础问题整理
    Mysql常见存储引擎命令：SHOWENGINES; Mysql版本5.5以后默认引擎是InnoDB，5.5版本以前的是MyISAMInnoDB和MyISAM的区别1、InnoDB支持事务，MyISAM不支持。对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个......
  • 构建高可用性的 SQL Server：Docker 容器下的主从同步实现
    摘要：本文将介绍如何在Docker环境下搭建MSSQLServer的主从同步，帮助读者了解主从同步的原理和实现方式，进而提高数据的可靠性和稳定性。一、前言在当今信息化的时代，数据的安全性和稳定性显得尤为重要。数据库是许多企业和组织存储和管理数据的核心，因此如何保证数据库的高可用性......
  • 【DataBase】SQL函数_ substr()函数
    substr()函数用于截取对应字段指定长度。SUBSTR(string,pos,len)string:指定字符串pos:规定字符串从何处开始，（这里的第一个位置是1而不是0）为正数时则从字段开始出开始，为负数则从结尾出开始。len:要截取字符串的长度。（是从1开始计数而不是0）  INSTR()......
  • Mysql数据库索引
    什么时候需要创建索引？(1)主键自动建立唯一索引;(2)频繁作为查询条件的字段应该创建索引;(3)查询中排序的字段创建索引将大大提高排序的速度(索引就是排序加快速查找)(4)查询中统计或者分组的字段;什么时候不需要创建索引?(1)频繁更新的字段不适合创建索引，因为每次更新不单单......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99