SQL语言学习 欢迎指正

Marbles

第一部分——建立最简单的表

学生-课程模式 S-T：
学生表：Student(Sno,Sname,Ssex,Sage,Sdept)        学号，姓名，性别，年龄，所在系
课程表：Course(Cno,Cname,Cpno,Ccredit)                课程号，课程名，先行课，学分
学生选课表：SC(Sno,Cno,Grade)                                学号，课程号，成绩

注意：
1.下划线表示主键（区分行，区分不同的元组。列是属性）

2.SC表的Grade的值可以为空（NULL,而NULL不是0）
         判断成绩是否为0：= 0
         判断成绩是否为NULL：is NULL

3. 三张表的关系：
       （参照表）SC<--->Student（被参照表）
       （参照表）SC<--->Course（被参照表）

4.值
         Student表中的Ssex定义时，要用check约束条件来限制
         Course表中的Cpno有的是没有的，Ccredit在有的表里是semester（学期数）


框架如下：

[SQL] 纯文本查看 复制代码

create table Student(
Sno char(9) primary key,--primary key主键定义在单列上(法一)
Sname char(20) ,
Ssex char(2),        --一个汉字两个字符
Sage smallint,
Sdept char(20)
);
create table Course
(
Cno char(4) primary key,
Cname char(40),
Cpno char(4),
Ccredit smallint
foreign key(Cpno) references Course(Cno)
--外键 Cpno, 此时Course表既是参照表也是被参照表
--根据语义，先行课一定是已有的课程
);
create table SC
(
Sno char(9),                        --参照其他表时，为了方便，对应列的格式尽量一致
Cno char(4),                        --char(9), char(4)
Grade smallint,
primary key(Sno, Cno),        --primary key(列名，列名)共同构成主键
                                                --主键定义在表上（法二）
foreign key(Sno) references Student(Sno),
          --参照表字段            被参照表字段
foreign key(Cno) references Course(Cno)
                                                        --最后三行语句都是表一级，列定义完之后才写的
)

练习：
1.建库

[SQL] 纯文本查看 复制代码

create database students

go

2.建表（只建了框架，里面没有数据）

[SQL] 纯文本查看 复制代码

use students
go
create table student
(
        sno             char(7)                primary key,                                                
        sname        char(10)              not null,                                                        
        ssex            char(2)                check(ssex in('男','女')),                        --或check(ssex='男' or ssex='女')
        sage           int,
        sdept         char(20)        
        )
go
create table course
(
        cno             char(10)        primary key,
        cname        char(20)        not null,
        credit          int                check(credit>0),
        semester int
)
go
create table sc
(
        sno char(7),
        cno char(10),
        grade int check(grade>=0 and grade<=100),
        primary key(sno,cno),
        foreign key(sno) references student(sno),
        foreign key(cno) references course(cno)
)
go

2.插入数据

[SQL] 纯文本查看 复制代码

use students
insert into student (sno,sname,ssex,sage,sdept)
values('0811101','李勇','男','21','计算机系'),
        ('0811102','刘晨','男','20','计算机系'),
        ('0811103','王敏','女','20','计算机系'),
        ('0811104','张小红','女','19','计算机系'),
        ('0821101','张立','男','20','信息管理系'),
        ('0821102','吴宾','女','19','信息管理系'),
        ('0821103','张海','男','20','信息管理系'),
        ('0831101','钱小平','女','21','通信工程系'),
        ('0831102','王大力','男','20','通信工程系'),
        ('0831103','张姗姗','女','19','通信工程系')

insert into course (cno,cname,credit,semester)
values('c001','高等数学',4,1),
        ('c002','大学英语',3,1),
        ('c003','大学英语',3,2),
        ('c004','计算机文化学',2,2),
        ('c005','java',2,3),
        ('c006','数据库基础',4,5),
        ('c007','数据结构',4,4),
        ('c008','计算机网络',4,4)

insert into sc (sno,cno,grade)--缺省为null
values('0811101','c001',96),('0811101','c002',80),
        ('0811101','c003',84),('0811101','c005',62),
        ('0811102','c001',92),('0811102','c002',90),
        ('0811102','c004',84),('0821102','c001',76),
        ('0821102','c004',85),('0821102','c005',73),
        ('0821102','c007',null),('0821103','c001',50),
        ('0821103','c004',80),('0831101','c001',50),
        ('0831101','c004',80),('0831102','c007',null),
        ('0831103','c004',78),('0831103','c005',65),
        ('0831103','c007',null)
        

此时的三张表为（分别查询表的前1000行得到）
                        


3. 存储（通过数据库的分离和附加，以后还会有数据库的备份和还原）

Su、

学生名字重复不是很正常吗？ 有很多其他东西作为学生标识的依据，学号，身份证等

mzq123000

汉字两个字符无法插入

Marbles

第二部分——单表查询

1.常用子句
     select子句：要显示的属性列     
     from子句：查询对象（基本表[真实存在数据]或视图[仅定义了一个结构]）
     where子句：查询条件（对所有元组[横向地]查询）
     group by子句：对查询结果按指定列的值分组，该列值相等的元组为一个组。常在每组中作用聚集函数（有group by不一定有having）
     having短语：输出满足指定条件的组（必须捆绑group by子句，分组之后组内筛选）
     order by子句：对查询结果按指定列值的升序（默认asc）或降序（desc）排序

2.语句执行顺序（1） from（2） 所有行where，可能会有表的连接（3） group by（4） 各组内 having（5） order by
3.常用的查询条件及其谓词

查询条件	谓词
比较	=,>,<,>=,<=,!=,<>,!>,!<; not + 上述比较运算符
确定范围	between and, not between and
确定集合	in, not in
字符匹配	like, not like
空值	is null, is not null           （is 不能用=代替）
多重条件（逻辑运算）	and, or, not                      （and 优先级更高，括号可以改变优先级）

补充：

1.字符匹配
       （1）使用到的通配符：
                    %(任意长度的字符串)                                 _（任意单个字符）
                    []（匹配方括号中的任何一个字符）           [^](不匹配方括号中的任何一个字符)
       （2）当匹配串为固定字符时，  like 和 = 等价
       （3）通配符 ->普通字符  
                      escape '\' 表示  \  为换码字符
                      eg：查询DB_Design课程的课程号和学分
                             select cno,credit from course
                             where cname like 'DB\_Design' escape '\'

2. distinct   删除重复的行，缺省为all

3.聚集函数
     (1)常用的有 count sum avg max min
     (2)若和group by捆绑，则他的作用范围在这个组，否则为整个表格
     (3)只能出现在select或者having中

4.练习：
       （注：每个练习后的
                         select * from sn
                         drop table sn     这两条语句，是为了方便检查语句是否正确，不属于题干操作）

在第一部分建好的students库中，进行如下操作：


(1)查询全体学生的学号与姓名，查询结果存入新表s1。

[SQL] 纯文本查看 复制代码

select sno,sname into s1 from student

select * from s1
drop table s1

(2)查询全体学生的姓名、学号和所在系，查询结果存入新表s2。

[SQL] 纯文本查看 复制代码

select sname,sno,sdept into s2 from student

select * from s2
drop table s2

(3)查询全体学生的详细记录，查询结果存入新表s3。

[SQL] 纯文本查看 复制代码

select sno,sname,ssex,sage,sdept into s3 from student

select * from s3
drop table s3

(4)查询全体学生的姓名及其出生年份，查询结果存入新表s4。

[SQL] 纯文本查看 复制代码

select sname,2022-sage 'birth' into s4 from student

select * from s4
drop table s4

(5)查询计算机系全体学生，查询结果存入新表s5。

[SQL] 纯文本查看 复制代码

select sno,sname,ssex,sage,sdept into s5 from student
where sdept='计算机系'
        
select * from s5
drop table s5

(6)查询所有年龄20岁以下的学生的姓名及年龄，查询结果存入新表s6。

[SQL] 纯文本查看 复制代码

select sname,sage into s6 from student
where sage<20
        
select * from s6
drop table s6

(7)查询成绩不及格学生的学号，查询结果存入新表s7.

[SQL] 纯文本查看 复制代码

select sno into s7 from sc
        where grade<60
        
select * from s7
drop table s7

(8)查询考试成绩在80~90之间的学生学号、课程号和成绩，查询结果存入新表s8。

[SQL] 纯文本查看 复制代码

select sno,cno,grade into s8 from sc
where grade between 80 and 90
        
select * from s8
drop table s8

(9)查询考试成绩不在80~90之间的学生学号、课程号和成绩，查询结果存入新表s9。

[SQL] 纯文本查看 复制代码

select sno,cno,grade into s9 from sc
where grade not between 80 and 90

select * from s9
drop table s9

(10)查询信息管理系、通信工程系和计算机系学生的姓名和性别，查询结果存入新表s10。

[SQL] 纯文本查看 复制代码

select sname,ssex into s10 from student
where sdept in ('信息管理系','通信工程系','计算机系')

select * from s10
drop table s10

(11)查询信息管理系、通信工程系和计算机系三个系之外的其他系学生的姓名和性别，查询结果存入新表s11。

[SQL] 纯文本查看 复制代码

select sname,ssex into s11 from student
where sdept not in ('信息管理系','通信工程系','计算机系')

select * from s11
drop table s11

(12)查询姓“张”的学生详细信息，查询结果存入新表s12。

[SQL] 纯文本查看 复制代码

select sno,sname,ssex,sage,sdept into s12 from student
where sname like '张%'

select * from s12
drop table s12

(13)查询姓“张”、姓“李”和姓“刘”的学生的详细信息，查询结果存入新表：s13。

[SQL] 纯文本查看 复制代码

select sno,sname,ssex,sage,sdept into s13 from student
where sname like '[张李刘]%'

select * from s13
drop table s13

(14)查询名字的第2个字为“小”或“大”的学生的姓名和学号，查询结果存入新表s14。

[SQL] 纯文本查看 复制代码

select sname,sno into s14 from student
where sname like '_[小大]%'

select * from s14
drop table s14

(15)查询所有不姓“刘”的学生姓名，查询结果存入新表s15。

[SQL] 纯文本查看 复制代码

select sname into s15 from student
where sname not like '刘%'

select * from s15
drop table s15

(16)在Student表中查询学号的最后一位不是2、3、5的学生信息，查询结果存入新表s16。

[SQL] 纯文本查看 复制代码

select sno,sname,ssex,sage,sdept into s16 from student
where sno not like '%[235]'

select * from s16
drop table s16

(17)查询还没有考试的学生的学号和相应的课程号，查询结果存入新表s17。

[SQL] 纯文本查看 复制代码

select sno cno into s17 from sc
where grade is not null

select * from s17
drop table s17

(18)查询计算机系男生的姓名，查询结果存入新表s18。

[SQL] 纯文本查看 复制代码

select sname into s18 from student
where ssex ='男' and sdept = '计算机系'

select * from s18
drop table s18

(19)查询c002和c003课程中考试成绩在80~90的学生的学号、课程号和成绩，查询结果存入新表s19

[SQL] 纯文本查看 复制代码

select sno,cno,grade into s19 from sc
where cno in ('c002','c003') and grade between 80 and 90

select * from s19
drop table s19

(20)查询修了“c002”课程的学生的学号及成绩，查询结果按成绩降序排序，查询结果存入新表s20

[SQL] 纯文本查看 复制代码

select sno,grade into s20 from sc
where cno = 'c002'
order by grade desc

select * from s20
drop table s20

(21)查询全体学生详细信息，结果按系名升序排列，同一个系的学生按出生日期降序排列，查询结果存入新表s21

[SQL] 纯文本查看 复制代码

select sno,sname,ssex,sage,sdept into s21 from student
order by sdept asc,sage asc

select * from s21
drop table s21

(22)统计学生总人数，查询结果存入新表s22。

[SQL] 纯文本查看 复制代码

select COUNT(sno) '学生总人数' into s22 from student

select * from s22
drop table s22

(23)统计选修了课程的学生人数，查询结果存入新表s23。

[SQL] 纯文本查看 复制代码

select COUNT(distinct sno) '选修了课程的学生人数' into s23 from sc

select * from s23
drop table s23

(24)计算学号为“0811101”的学生的考试总成绩，查询结果存入新表s24。

[SQL] 纯文本查看 复制代码

select COUNT(grade) '学生0811101的总成绩' into s24 from sc
where sno = '0811101'

select * from s24
drop table s24

(25)计算“0831103”学生的平均成绩，查询结果存入新表s25。

[SQL] 纯文本查看 复制代码

select AVG(grade) '学生0831103的平均成绩' into s25 from sc
where sno = '0831103'

select * from s25
drop table s25

(26)查询“c001”课程考试成绩的最高分和最低分，查询结果存入新表s26。

[SQL] 纯文本查看 复制代码

select MAX(grade) '最高分',MIN(grade) '最低分' into s26 from sc
where cno ='c001'

select * from s26
drop table s26

(27)统计每门课程的选课人数，列出课程号和选课人数，查询结果存入新表s27。

[SQL] 纯文本查看 复制代码

select cno,COUNT(sno) '选课人数' into s27 from sc
group by cno

select * from s27
drop table s27

(28)统计每个学生的选课门数和平均成绩，查询结果存入新表s28。

[SQL] 纯文本查看 复制代码

select sno '学号',COUNT(cno) '选课门数',AVG(grade) '平均成绩' into s28 from sc
group by sno

select * from s28
drop table s28

(29)统计每个系的女生人数，查询结果存入新表s29。

[SQL] 纯文本查看 复制代码

select sdept '系名', COUNT(sno) '女生人数' into s29 from student
where ssex = '女'
group by sdept

select * from s29
drop table s29

(30)统计每个系的男生人数和女生人数以及男生的最大年龄和女生的最大年龄。结果按系名的升序排序，查询结果存入新表s30。

[SQL] 纯文本查看 复制代码

select sdept '系名', ssex '性别' ,COUNT(sno) '人数', MAX(sage) '最大年龄' into s30 from student
group by sdept,ssex
order by sdept

select * from s30
drop table s30

(31)查询选课门数超过3门的学生的学号和选课门数，查询结果存入新表s31。

[SQL] 纯文本查看 复制代码

select sno '学号', COUNT(cno) '选课门数' into s31 from sc
group by sno
having COUNT(cno) > 3

select * from s31
drop table s31

(32)查询选课门数大于等于4门的学生的平均成绩和选课门数，查询结果存入新表s32.

[SQL] 纯文本查看 复制代码

select sno '学号' ,AVG(grade) '平均成绩',COUNT(cno) '选课门数' into s32 from sc
group by sno
having COUNT(cno) >= 4

select * from s32
drop table s32

(33)查询计算机系和信息管理系每个系的学生人数，查询结果存入新表s33。

[SQL] 纯文本查看 复制代码

select sdept '系名', COUNT(sno) '学生人数' into s33 from student
where sdept in ('计算机系','信息管理系')
group by sdept

select * from s33
drop table s33

欢迎指正，谢谢大家

nanhai31

666,学习一下，谢谢大佬