sql: SQLite,MySQL,SQL Server

于 2023-06-26 08:25:00 发布
阅读量706 收藏
点赞数
分类专栏： 数据库编程 文章标签： sql sqlite mysql sqlserver
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/geovindu/article/details/131389639
版权

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 

 

更多浏览个人博客园: 

®Geovin Du Dream Park™ - 博客园 (cnblogs.com)icon-default.png?t=N5K3https://www.cnblogs.com/geovindu/

geovindu
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
【SQLite预习课1】SQLite简介——MySQL的简洁版
Designer 小郑的技术博客
11-23 3869
本文讲解 SQLite 是什么，和 MySQL 相比有什么优势，介绍 SQLite 的历史和基本命令和语法，包括创建数据库、创建学生表、新增学生数据示例，可以给 SQLite 入门者带来帮助。
SQLite、SQL Server和MySQL
最新发布
春日予茶
03-27 380
总结来说，这三种数据库系统各有千秋，选择哪种取决于具体的应用场景和需求。SQLite适用于资源受限的环境和简单的数据库需求；SQL Server适用于企业级应用和复杂的分布式环境；而MySQL则广泛应用于各种规模的网站和在线应用。
SQLite与SQL差异
weixin_33937778的博客
06-15 552
这些关于SQLite与SQL差异的问题，你是否遇到过呢？笔者在这里为大家总结出了使用SQLite时，与SQL之间经常会出现的一些差异性问题，希望对大家的使用有所帮助。一、常见问题汇总  1 TOP  这是一个大家经常问到的问题，例如在SQLSERVER中可以使用如下语句来取得记录集中的前十条记录：  [pre]SELECT TOP 10 * FROM [index] ORDER BY inde...
在QT中使用数据库
热门推荐
云之飞踢的博客
04-22 2万+
在使用数据库时，需要查看安装的QT中支持的驱动列表。一般可以在QT安装目录中的plugins/sqldrivers文件夹中查看所有的驱动插件文件。 本文重点不在此，故不再细述。 在xxx.pro工程文件中需要添加如下： QT += sql 连接SQLite数据库： 该数据库为轻量级数据库，使用方便。代码如下： #include #include #include #i
SqlserverToSQLite
08-30
代码类，开发技术资源。SqlserverToSQLite，提供一些资源信息。
sql-sync：SQLite（客户端）和MySQL（主服务器）之间的脱机复制
02-06
SQLite（客户端）和MySQL（主服务器）之间的脱机复制。 该项目不是库，而是示例代码。 它的某些部分（ lib代码）可以在其他项目中重用。 该代码主要只是测试此处描述的方法是否有效。 该代码是为Node.JS构建的。 ...
mysql-wb-exportsqlite:SQLiteMySQL Workbench的CREATE SQL脚本导出插件
05-14
MySQL Workbench ExportSQLite插件ExportSQLite是的插件，可将架构目录导出为SQLite的CREATE SQL查询。 这是的Python端口，可用于MySQL Workbench 6.2或更高版本。安装打开MySQL工作台在“脚本”菜单中选择“安装...
DBHelper数据库操作类（支持OleDb、MySQL、Oracle、SQL、SQLite）.zip
04-22
数据库操作类（支持OleDb、MySQL、Oracle、SQL、SQLite、） 操作： 获取连接字符串 得到web.config里配置项的数据库连接字符串。 数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库. ...
使用PetaPoco ORM管理SQLServer，SQLIte，MySQL和ProgreSQL数据库
04-11
这个“使用PetaPoco ORM管理SQLServer，SQLIte，MySQL和ProgreSQL数据库”的主题，旨在展示PetaPoco如何与多种数据库类型无缝集成，包括SQL Server、SQLite、MySQL和PostgreSQL。 首先，PetaPoco的简单性和高效性是...
sqlite3-to-mysql：使Sqlite3导出MySQL可读
01-30
2. **SQL语法调整**：SQLite3和MySQL的SQL方言有区别，比如创建表的语句、JOIN操作、子查询等可能需要调整。 3. **数据预处理**：可能需要通过sed或其他文本处理工具对数据进行清洗或格式化，以满足MySQL的要求。 ...
SQL Server数据导入SQLite工具
01-30
SQL Server数据导入SQLite工具 Convert SQL Server DB to SQLite DB
SQLite与MySQL、SQLServer等异构数据库之间数据同步的解决方案
u013514958的博客
06-29 1万+
什么是SQLite DBSync？ SQLite DBSync是开源嵌入式数据库SQLite的数据同步引擎，实现了SQLite与SQLite数据库之间以及SQLite与异构数据库（Oracle、MySQL、SQLServer）之间的增量的、双向的数据同步功能。 SQLite DBSync 整体结构是？ SQLite DBSync 应用场景是由同步客户端、同步服务端...
[转]SQLite与SQLserver差异
代码笔记
04-10 2263
SQLite在线教程：http://www.runoob.com/sqlite/sqlite-syntax.html 与SQLserver区别：https://www.cnblogs.com/tianboblog/p/4200685.html 主要是： 查询时把两个字段拼接在一起 --sqlserver-- select Filed1+'@'+Filed2 from table --s...
sqlite数据库文件导入到sqlserver ...
weixin_33893473的博客
03-26 978
最近在公司做项目，需要做两个版本，都是cs的，然后要求是一个单机版，自带数据库，另一个要进行局域网内的连接，所以公司的大牛设计是，局域网版的用sqlserver2008，单机版的则用sqlite。然后各自运行，当然，最后的时候，单机版的数据要导入到网络版的数据库。 如何导入？思路如下： 首先，是从sqlite导出到sqlite文件，...
SQL Server转sqlite数据库
weixin_33850890的博客
10-08 357
下载地址：http://files.cnblogs.com/jason-davis/SQL_Server_To_SQLite_DB_Converter_bin.zip 下载地址：http://files.cnblogs.com/jason-davis/sqliteSpy.zip 转载于:https://www.cnblogs.com/jason-davis/p/4010...
sql server数据库与mysql_SQL Server、MySQL、SQLite 数据库对比
weixin_42360953的博客
02-04 804
一：sqlite1.sqlite占用的内存和cpu资源较少2.源代码开源，完全免费3.相对于其他数据库来说，移植比较方便。python自带了sqlite3模块，使用时不需要用户名密码等限制，直接连接数据库即可使用。4.检索速度上十几兆、几十兆的数据库sqlite很快，但是上G的时候最慢5.不支持多用户多线程同时读写数据库。这点在我一边爬数据存储，同时想对该表读的时候就会体现，会报错database...
如何将sql server数据库转化成sqlite数据库
爱码农爱生活
05-29 682
今天在将sql server转化为sqlite的数据库的时候，遇到不少的问题，在网上搜了很长时间，都没有找到合适的软件将sql server转化成sqlite，其中用到了SqliteDev软件，在转化的过程中，如果你的表有问题，则不能全部转化，不得已，在网上继续搜索转化的软件，终于找到了一款非常完美、且非常简单和使用的软件。先分享给大家： 1. 在1处，输入sql server的服务器地址名...
Python3数据库编程：SQLite, MySQL与关系数据库入门
常见的关系数据库管理系统（RDBMS）包括Microsoft SQL Server、Microsoft Access、Oracle、MySQL和SQLite等。 在深入Python数据库编程之前，了解数据模型的概念非常重要。数据模型是描述数据结构和数据间关系的方法...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值