sql: SQLite,MySQL,SQL Server

SQLite 3

select StudentId,StudentName,StudentNO,StudentBirthday,(julianday('now')-julianday(StudentBirthday))/365 as Age from DuStudentList;


SELECT (julianday('now') - julianday('1976-07-04'))/365;

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

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

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

SELECT * FROM DuCourse;

SELECT * FROM　DuStudentScore;


insert into DuGradeClass(ClassName) values('二年级一班');
 
insert into DuGradeClass(ClassName) values('二年级geovindu班');
INSERT INTO DuStudentScore(StudentId,CourseId,Score) values(1,1,90);

INSERT INTO "main"."DuStudentScore"
("StudentId", "CourseId", "Score")
VALUES (1, 2, 80.5);

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 DuStudentClass

SELECT * FROM ViewStudentScore;


CREATE VIEW  ViewStudentScore
AS
select a.*,c.CourseName,b.StudentNO,b.StudentName,b.StudentBirthday,(julianday('now')-julianday(b.StudentBirthday))/365 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;

CREATE VIEW  ViewStudentClass
AS
select a.*,d.ClassName,b.StudentNO,b.StudentName,b.StudentBirthday,b.StudentBirthday,(julianday('now')-julianday(b.StudentBirthday))/365 as Age from DuStudentClass as a
left join DuStudentList as b on a.StudentId=b.StudentId
left join DuGradeClass as d on a.ClassId=d.ClassId

INSERT into DuStudentList(StudentName,StudentNO,StudentBirthday) VALUES("geovindu","00002","2008-01-03");
INSERT into DuStudentList(StudentName,StudentNO,StudentBirthday) VALUES("江山","00003","2007-03-05");

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)
);


DROP TABLE DuStudentScore;

CREATE TABLE DuStudentScore(
	ScoreId	INTEGER PRIMARY KEY AUTOINCREMENT,
	StudentId	INTEGER,
	CourseId	INTEGER,
	Score	NUMERIC	
)

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)
);

　　

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',
'语文，数学，英语'

　　

Oracel,SQL server, PostgreSQL,MySQL，SQLite都玩过

C#，python,Java,C++，慢慢学习