SQL server模拟数据实现稍复杂查询语句

目录

1 DISTINCT（不允许重复）的用法

2 BETWEEN（在某个范围内）的用法

3 IN（属于若干个孤立的值）的用法

4 NULL（空值）的用法

5 ORDER BY（排序）的用法

6 模糊查询

7 分页查询

 模拟生成540万条数据

---

这里使用的数据还是上一篇博客模拟出来的数据。不会的同学可以参考我的上一篇博客

sql server 模拟数据进行select基本查询_Ricardo · M · YUAN的博客-优快云博客

1 DISTINCT（不允许重复）的用法

  

select studentName from student_score--* 查询学生名字
select DISTINCT studentName from student_score--* 去重

2 BETWEEN（在某个范围内）的用法

select * from student_score where scoreValue BETWEEN 80 AND 90 --*查询表中80到90分的数据
select * from student_score where scoreValue<=90 and scoreValue >= 80

select * from student_score where scoreValue not BETWEEN 80 AND 90--*查询表中80到90分以外的数据
select * from student_score where scoreValue<=90 or scoreValue >= 80

3 IN（属于若干个孤立的值）的用法

select * from student_score where scoreValue in (90,100)--*查询表中90到100分的数据
select * from student_score where scoreValue=90 or scoreValue=100

select * from student_score where scoreValue not in (90,100)--*查询表中90到100分以外的数据
select * from student_score where scoreValue!=90 and scoreValue!=100

4 NULL（空值）的用法

select * from student_score where studentName is null--*输出为空的名字

select * from student_score where studentName is not null --*输出不为空的数据

5 ORDER BY（排序）的用法

select * from student_score order by scoreValue --*排序默认升序

select * from student_score order by scoreValue DESC --*降序

6 模糊查询

like
%表示任意多个字符

select * from student_score where studentName like '张%'

select * from student_score where studentName not like '张%'

 

7 分页查询

查询需要1到20的记录

select b.* from(
    select 
    row_number() over(partition by className,courseName order by scoreValue desc) as rowid,
    a.*
    from student_score_30wan as a
) b
where b.rowid>=1 and b.rowid<=20

 模拟生成540万条数据

模拟生成540万条数据的sql语句，感兴趣的同学可以自取去玩玩。

​

create table student_score_30wan
(
    id int identity(1,1) primary key,
    studentName nvarchar(50),
    className nvarchar(50),
    courseName nvarchar(50),
    scoreValue int,
    createTime datetime
)


declare @classCount int
set @classCount=1
declare @className nvarchar(50)
declare @courseName nvarchar(50)
declare @scoreValue int
declare @studentCount int
set @studentCount=80

-- 定义一个姓氏表变量，表添加两个字段，自增编号和名字
declare @surnameTable table(
    id int identity(1,1) primary key, 
    name nvarchar(10)
)

-- 定义一个名字表变量，表添加两个字段，自增编号和名字
declare @nameTable table(
    id int identity(1,1) primary key, 
    name nvarchar(10)
)

-- 姓和名字
-- 姓氏
insert @surnameTable values
('王'),('李'),('张'),('刘'),('陈'),('杨'),('黄'),('赵'),('周'),('吴'),
('徐'),('孙'),('马'),('胡'),('朱'),('郭'),('何'),('罗'),('高'),('林') 
-- 名称
insert @nameTable values
('芸'),('荷'),('星'),('秋'),('嘉'),('娜'),('珊'),('菲'),('素'),('嫣'),
('慧'),('慕'),('歆'),('巧'),('绮'),('羽'),('静'),('柔'),('采'),('沐'),
('苑'),('姣'),('芳'),('宁'),('沛'),('玥'),('文'),('如'),('悦'),('若'),
('德'),('蕾'),('颜'),('依'),('盼'),('菀'),('秀'),('草'),('莺'),('倩'),
('柳'),('娴'),('彨'),('舒'),('雅'),('淑'),('冉'),('云'),('凝'),('棋')

-- 循环遍历班级
declare @num int
set @num=0
while @classCount<=6 begin
    set @studentCount=300000
    -- ===年级信息===
    set @className=convert(varchar(10),@classCount)+'年级'
    set @classCount+=1
    -- ===/年级信息===

    -- ===遍历创建学生记录===
    while @studentCount>0 begin
        set @num+=1
        -- 生成名字
        declare @name nvarchar(50)
        declare @nameLength int
        set @nameLength=1+round(rand()*1,0)
        set @name=(select name from @surnameTable where id=round(rand()*20,0))
        while(@nameLength>0) begin
            set @name+=(select name from @nameTable where id=round(rand()*50,0))
            set @nameLength-=1
        end
        set @name+=convert(nvarchar(10),@num)
        if @name=null begin
            set @name='张三'+convert(nvarchar(10),@num)
        end

        -- ===年级课程===
        -- 语文
        set @courseName='语文'
        set @scoreValue=round(rand()*50+50,0)
        insert into student_score_30wan(studentName,className,courseName,scoreValue,createTime)
        values(@name,@className,@courseName,@scoreValue,getdate())

        -- 数学
        set @courseName='数学'
        set @scoreValue=round(rand()*50+50,0)
        insert into student_score_30wan(studentName,className,courseName,scoreValue,createTime)
        values(@name,@className,@courseName,@scoreValue,getdate())

        -- 英语
        set @courseName='英语'
        set @scoreValue=round(rand()*50+50,0)
        insert into student_score_30wan(studentName,className,courseName,scoreValue,createTime)
        values(@name,@className,@courseName,@scoreValue,getdate())
        -- ===/年级课程===
        set @studentCount-=1
    end

end