sql: SQLite,MySQL,SQL Server

2023-11-12

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

找出各数据库的相同和差异，找出规律规则，用程序生成。

python

java

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

数据库编程

sql

SQLite

mysql

sqlserver

sql: SQLite,MySQL,SQL Server 的相关文章

以编程方式插入行（父行和子行）

我正在使用 Spring 和 JDBCTemplate 该场景是 CUSTOMER 表和 ORDERS 表的父子关系我想做一个插入例如 1 个客户和 5 个订单但我不确定如何以编程方式在 CUSTOMER 表中插入一行如何获取 Or
需要 SQL 查询澄清[关闭]

Closed 这个问题不符合堆栈溢出指南 help closed questions 目前不接受答案我有一个由以下列组成的表 billid patientid doctorid fees 如何显示治疗多名患者的医生尝试了以下代码并得到了
如何将 MySQL 查询输出保存到 Excel 或 .txt 文件？ [复制]

这个问题在这里已经有答案了如何将 MySQL 查询的输出保存到 MS Excel 工作表即使只能将数据存储在 txt文件就可以了 From 将 MySQL 查询结果保存到文本或 CSV 文件中 http www tech recipe
NVARCHAR 变量在Where 子句中不起作用

在 SQL Server 我想是 2018 我不知道如何判断中我的变量不起作用WHERE的条款NVARCHAR 比较应该返回值但它什么也没返回如果我只是手动输入声明的文本它会突然起作用并返回值没有任何逻辑原因应该有任何不同类型
Android 中的 Sugar ORM：更新 SQLite 中保存的对象

我是在 Android 上使用 SQLite 和 Sugar ORM 进行应用程序开发的新手并尝试阅读 Sugar ORM 文档但没有找到有关如何更新 SQLite 中保存的对象的任何信息更改对象属性后还可以保存对象吗就像是 Cus
如何将SQL数据加载到Hortonworks中？

我已在我的电脑中安装了 Hortonworks SandBox 还尝试使用 CSV 文件并以表结构的方式获取它这是可以的 Hive Hadoop nw 我想将当前的 SQL 数据库迁移到沙箱 MS SQL 2008 r2 中我将如何做
在 C# 中执行基于存储过程的查询后，如何重新使用 CommandText 的 SqlCommand 对象？

我有一个示例代码 aCommand CommandType CommandType StoredProcedure aCommand Parameters AddWithValue book id bookID aCommand Param
SQL Server 中高效的字符串后缀搜索？

我想找到与谓词匹配的所有行MyColumn LIKE FooBar 但通过使用索引查找而不是扫描一种方法本质上是创建 MyColumn 的反向版本并搜索反向模式 ooF 在该列上在反向列上有一个常规索引然而我不喜欢手动创建这个反向列
MySQL集群启动失败

这不是我第一次创建ndbcluster 但我没有收到这样的问题我正在关注本手册 https hub docker com r mysql mysql cluster by mysql团队我正在使用回显的默认配置在此 GitHub 存储库
H2 SQL 日期比较

在 H2 数据库中如何在 TIMESTAMP 类型的列上运行查询 SELECT FROM RECORDS WHERE TRAN DATE lt 2012 07 24 Try 2012 07 24
猪的组连接等效吗？

试图在 Pig 上完成这个任务寻找 MySQL 的 group concat 等效项例如在我的表中我有以下内容 3fields userid clickcount pagenumber 155 2 12 155 3 133 155
SQL Server：为什么 ISO-8601 格式的日期依赖于语言？

我需要一些帮助来理解 SQL Server 中的日期格式处理如果您尝试以下操作它将返回正确的结果 SET LANGUAGE English SELECT CAST 2013 08 15 AS DATETIME 2013 08 15 00
Java、Oracle 中索引处缺少 IN 或 OUT 参数:: 1 错误

您好我使用 Netbeans 8 0 2 和 Oracle 11g Express Edition 在 JSF 2 2 中编写了一个图书馆管理系统我有几个名为书籍借阅者等的页面以及数据库中一些名为相同名称的表我的问题是这样的
从 Grib 天气模型中提取数据

我已经下载了grib1模型数据来自GFS http en wikipedia org wiki Global Forecast System 我使用的是 Mac OS X 并且能够构建wgrib2文件来自NOAA http en wikip
在 django ORM 中查询时如何将 char 转换为整数？

最近开始使用 Django ORM 我想执行这个查询 select student id from students where student id like 97318 order by CAST student id as UNSIG
SQL - 需要查找重复记录但排除反向事务

我有一张交易表偶尔会有重复条目如果当管理员发现这些重复条目时他们将撤销交易从而创建负值但由于监管要求原始重复条目仍然保留我想创建一个 SQL 查询并使用 Crystal Reports 来制作报告以便管理员轻松查找重复
AWS RDS MySql - 如何在设置“公开可用”后允许访问

刚刚使用默认设置和用户密码创建了新的 AWS RDS MySql 实例我也将其设置为publicly available并在此过程中创建新的 VPC 目前无法从我的笔记本电脑连接到此 RDS mysql h endpoint u myu
如何使用 Mysql Python 连接器检索二进制数据？

如果我在 MySQL 中创建一个包含二进制数据的简单表 CREATE TABLE foo bar binary 4 INSERT INTO foo bar VALUES UNHEX de12 然后尝试使用 MySQL Connector P
MySQL 可选的带有 MATCH 的 LEFT JOIN

我有以下查询它对 MySQL Innodb 数据库中同一搜索词的两个不同表中的两列执行全文搜索 SELECT Id MATCH tb1 comment tb2 comment AGAINST search term IN BOOLEAN
mysql-connector-c++ - “get_driver_instance”不是“sql::mysql”的成员

我是 C 的初学者我认为学习的唯一方法就是接触一些代码我正在尝试构建一个连接到 mysql 数据库的程序我在 Linux 上使用 g 没有想法我运行 make 这是我的错误 hello cpp 38 error get driver

随机推荐

锁升级过程和原理

目录同步方法与一般方法字节码对比对象头中的锁信息锁升级中涉及的四种锁锁升级的过程锁升级过程和原理同步方法与一般方法字节码对比 public class SynchronizedTest1 public static void m
Linux权限详解

你好我是史丰源欢迎你的来访希望我的博客能给你带来一些帮助我的Gitee 代码仓库 Linux权限 Shell外壳程序运行原理 Shell 外壳相当于一层遮蔽内部的保护壳 Linux是一个操作系统请大家思考一个问题我们操作系统
pytest参数化实现DDT：读取JSON数据

JSON JavaScript Object Notation JS 对象简谱是一种轻量级的数据交换格式与上一篇文章类似使用pytest中的pytest mark parametrize 便可实现参数化代码如下所示导入json模块
conda新建、复制、删除、重命名envs环境

1 新建环境 conda create n torch python 3 6 这样就创建好了一个名叫torch的python3 6环境 2 复制环境 conda create n new torch clone torch 这样就将torc
【PSO-LSTM】基于PSO优化LSTM网络的电力负荷预测（Python代码实现）

欢迎来到本博客博主优势博客内容尽量做到思维缜密逻辑清晰为了方便读者座右铭行百里者半于九十本文目录如下目录 1 概述 1 1 LSTM神经网络算法 1 2 PSO算法 1 3 PSO LSTM负荷预测模型 2 运行结果 2
pytorch: 转onnx模型

摘要 onnx Open Neural Network Exchange 主要用于部署训练一般使用pytorch和tensorflow 等训练完成我们需要模型转成onnx 用于部署在云或者边缘计算上而为什么要要转成onnx模型呢主要
vscode CommandNotFoundError: Your shell has not been properly configured to use ‘conda activate‘.解决

1 问题出现已安装anaconda并已添加其环境变量测试在win cmd中输入conda及相关conda activate命令等都是正常的但当在vscode中run debug工程py脚本时出现以下错误及提示 2 踩坑记录 1 根据提
xterm使用详情

常用配置项 rendererType dom canvas 渲染器类型当 canvas 渲染器运行过慢时会回退为 DOM 渲染器 DOM 渲染器下不起作用的功能 Letter spacing Cursor blin cols numbe
word页码怎么从第三页开始设置为第一页_Word小技巧

在使用word编辑文档或者写论文的时候会涉及到页码的设置今天粉笔君就来给大家分享一下如何快速正确的设置页码格式一第一页为封面第二页为目录从第三页开始编码 1 将鼠标固定到文档第二页的第一个字符前在菜单栏中单击布局选项找到
Java+Servlet+Jsp(el, jstl)+MyBatis的CRUD练习小项目

1 概述这篇博客主要是分享一个基于Servlet Jsp el jstl MyBatis的CRUD练习小项目包括项目的源码以及项目的逻辑通过这个项目能够学习Java web中最基础的servlet jsp和mybatis的使用为后
一篇短文告诉你阿里云用户如何通过等保测评

随着等保2 0的脚步越来越近云上等保受到越来越多人的关注近日在成都举行的云栖大会安全论坛上牛君特意关注了阿里云云上系统的等保合规方案阿里云最早于2012年通过 ISO 27001 认证并于2016年9月通过新的云计算安全等级保护三
点云绪论（点云数据及获取、点云数据处理、常用软件及开源库）

文章目录点云数据及获取点云数据处理常用软件及开源库点云数据及获取定义点云 point cloud 三维点的数据集合属性三维坐标强度颜色时间戳点云组织形式 organized the point cloud is lai
BG-20k数据集分析及下载

包含20000张高清背景图片包含清晰的对象轮廓可以用做高质量图像生成的研究此数据集下载流程繁琐还需要签保证书获得授权等等可以联系此人提供高速下载链接 q 2389411778 备注要bg20k数据集
centos7 ping: www.baidu.com: Name or service not known

转自 https www cnblogs com maowenqiang articles 7727910 html root www ping www baidu comping www baidu com Name or service
RPC介绍+原理

RPC 远程过程调用它是一种通过网络从远程计算机程序上请求服务而不需要了解底层网络技术的协议 RPC协议假定某些传输协议的存在如TCP或UDP 为通信程序之间携带信息数据在OSI网络通信模型中 RPC跨越了传输层和应用层 RPC采用
spingboot热部署，实现jsp页面时时刷新

SpringBoot默认不支持JSP的实时更新目前有两种解决方案解决方法1 在配置文件手工开启实时编译JSP的功能 application yml 配置如下 server jsp servlet init parameters dev
双目标定（三）标定流程（含矫正）

1 原理参见单目标定基本原理 2 采集 2 1标定板的制作大小应当使得标定板在图像中占比尽可能大一些占图像长度比例应当超过1 3 如果实在无法保证标定板占图像比例比较大如果占比比较小则需要尽可能多采集一些图像平标定板应当尽可能
单片机基础——使用USART发送和接收数据（DMA模式）

1 准备工作硬件准备开发板首先需要准备一个小熊派IoT开发板并通过USB线与电脑连接 2 生成MDK工程选择芯片型号打开STM32CubeMX 打开MCU选择器搜索并选中芯片STM32L431RCT6 配置时钟源如果选择使用
STM32 标准外设SPL库、硬件抽象层HAL库、低层LL库区别？

1 STM32 之一 HAL库标准外设库 LL库 ZC Shou的博客 CSDN博客 ll库仔细阅读 2 STM32标准外设库 HAL库 LL库 King先生博客园 3 STM32 之 HAL库戈扬的博客仔细阅读 4 STM32
sql: SQLite,MySQL,SQL Server

SQLite 3 CREATE TABLE DuStudentList StudentId INTEGER PRIMARY KEY AUTOINCREMENT StudentName TEXT NOT NULL StudentNO TEXT

sql: SQLite,MySQL,SQL Server

sql: SQLite,MySQL,SQL Server 的相关文章

随机推荐

热门标签