一.数据结构

二.建库,建表

• 创建数据库

  use master
  go
  
  -- 查询是否存在'School'数据库,有则删除. --
  if exists (select * from sysdatabases where name = 'School')
      drop database School
  go
  
  -- 创建 'School' 数据库 --
  create database School
  go
  
  use School
  go
  

• 新增数据表

  • 新增Student 学生表

    -- 查询'School'数据库下是否存在'Student'数据表,有则删除. --
    if exists (select * from sysobjects where name = 'Student')
        drop table Student
    go
    
     -- 创建'Student'数据表 --
    create table Student
    (
        studentID bigint identity(1,1) primary key,  -- identity(1,1):自增长函数,从1开始,步进1. primary key:设为主键的意思
        studentName nvarchar(50),  -- nvarchar(50):50个Unicode 中文字符数据
        age int default(20), -- default(20):设置默认值为20
        gender nvarchar(50) default(1),
        state int default(1), -- 状态:  0冻结,  1激活
        createdTime datetime default(getdate()) -- 自动获取当前时间做为创建时间
    )
    go
    

  • 新增Subject 科目表

    -- 查询'School'数据库下是否存在'Subject'数据表,有则删除. --
    if exists (select * from sysobjects where name = 'Subject')
        drop table Subject
    go
    
    -- 创建'Subject'数据表 --
    create table Subject
    (
        subjectID bigint identity(1,1) primary key,  
        subjectName nvarchar(50),
        state int default(1),
        createdTime datetime default(getdate())
    )
    go
    

  • 新增Score 分数表

    -- 查询'School'数据库下是否存在'Score'数据表,有则删除. --
    if exists (select * from sysobjects where name = 'Score')
        drop table Score
    go
    
    -- 创建'Score'数据表 --
    create table Score
    (
        scoreID bigint identity(1,1) primary key,  
        studentID bigint,
        subjectID bigint,
        score bigint,
        state int default(1),
        createdTime datetime default(getdate())
    )
    go
    

三.数据表的增/删/改/查

• 增

  -- 新增三名学生数据 --
  insert into Student(studentName,age,gender) values('学生A',20,1)
  insert into Student(studentName,age,gender) values('学生B',19,1)
  insert into Student(studentName,age,gender) values('学生C',18,1)
  

• 查

  -- 查询学生表所有数据 --
  select * from Student
  

• 改

  -- 将学生A的年龄改为18 --
  update Student set age=18 where studentName='学生A'
  
  -- 将所有学生的性别改为女 --
  update Student set gender=0
  

• 删

  -- 删除学生C的数据 --
  delete from Student where studentName='学生C'
  

四.一般查询

-- 为了演示查询效果,先新增一些数据 --
insert into Student(studentName,age,gender) values('学生A',18,'男')
insert into Student(studentName,age,gender) values('学生B',19,'女')
insert into Student(studentName,age,gender) values('学生C',20,'男')

insert into Subject(subjectName) values('数学')
insert into Subject(subjectName) values('语文')
insert into Subject(subjectName) values('英语')

insert into Score(studentID,subjectID,score) values(1,1,85)
insert into Score(studentID,subjectID,score) values(1,2,57)
insert into Score(studentID,subjectID,score) values(1,3,90)
insert into Score(studentID,subjectID,score) values(2,1,70)
insert into Score(studentID,subjectID,score) values(2,2,95)
insert into Score(studentID,subjectID,score) values(2,3,98)
insert into Score(studentID,subjectID,score) values(3,1,54)
insert into Score(studentID,subjectID,score) values(3,2,70)
insert into Score(studentID,subjectID,score) values(3,3,85)

select * from Student
select * from Subject
select * from Score

-- 查询所有年龄小于19岁的学生 --
select * from Student where age<19

-- 查询所有学生并按照年龄倒序排序 --
select * from Student order by age desc

-- 查询所有学生年龄大于18,结果按正序排序 --
select * from Student where age>18 order by age asc  --asc 可省略

Paste_Image.png

-- 查询所有名字中存在'B'的学生 --
select * from Student where studentName like '%B%'  -- %百分号是匹配符.可代替任意字符任意个数

-- 查询学生的总量 --
select count(1) from student

-- 查询年龄较大的前两名学生
select top 2 * from Student order by age DESC

-- 根据性别分组,并分别写出每组学生的数量 --
select gender,count(1) from Student group by gender

-- 查询一共有多少种分值 --
select distinct score form Score

select score,count(1) from score group by score

五.高级查询

• 查询学生的分数

  select Student.studentName,Score.score from Student
  right join Score on Score.StudentID = Student.studentID
  

• 查询有不及格科目的学生

  select Student.studentName,Subject.subjectName,Score.score from Score
  left join student on Score.studentID = Student.studentID
  left join Subject on Subject.subjectID = Score.subjectID
  where Score.score < 60
  

• 查询每个学生的平均分和总分,并按总分倒序排序

  select Student.studentName,avg(Score.score) avg,sum(Score.score) sum from Student,Score
  where Student.studentID = Score.StudentID
  group by Student.studentName
  order by sum desc
  

---

想看视频版的请点击这里