文章目录

• 前言
• • 一、数据类型
  • • 1.1 数值型
    • 1.2 字符型
    • 1.3 日期型
  • 二、数据库
  • • 2.1 创建数据库
    • 2.2 修改数据库
    • 2.3 删除数据库
  • 三、基本表
  • • 3.1 表结构
    • • 3.1.1 创建表结构
      • 3.1.2 修改表结构
      • 3.1.3 增加列
      • 3.1.4 删除列
      • 3.1.5 增加约束
      • 3.1.6 删除约束
      • 3.1.7 删除表
    • 3.2 数据查询
    • • 3.2.1 简单查询
      • 3.2.2 条件（where）
      • 3.2.3 消除（distinct）
      • 3.2.4 模糊（like）
      • 3.2.5 排序（order by）
      • 3.2.6 分组（group by）
      • 3.2.7 限制（limit）
      • 3.2.8 单行输出（group_concat）
      • 3.2.9 多表查询（连接）
      • 3.2.10 子查询（嵌套）
      • 3.2.11 复杂查询（exists）
      • 3.2.12 组合查询（union）
    • 3.3 数据更新
    • • 3.3.1 插入（insert）
      • 3.3.2 修改（update）
      • 3.3.3 删除（delete）
  • 四、视图
  • • 4.1 创建视图
    • 4.2 其他
• 总结

前言

一切从创建数据库开始
数据库→基本表（创建-增删改查）→视图（创建-增删改查）

一、数据类型

1.1 数值型

int #4个字节smallint #2个字节tinyint #1个字节float #浮点型numeric/decimal #小数

1.2 字符型

char #固定长度varchar#可变长度text #文本

1.3 日期型

datetime #8个字节year #年date #天time #时间

二、数据库

2.1 创建数据库

create database 数据库名default character set utf8;

2.2 修改数据库

alter database 数据库名;

2.3 删除数据库

drop database 数据库名;

三、基本表

3.1 表结构

3.1.1 创建表结构

定义

create table 表名(列名数据类型约束条件,列名数据类型约束条件,……);

创建学生表student
唯一性标识 id:整型，自动增长列，主键
学号 sno：9个字符，非空，不允许重复
姓名 sname：20个字符,非空
性别 ssex：2个字符，男或女
年龄 sage：tinyint，15到45之间
系别 sdept：20个字符，默认值为计算机系

create table student(id int auto_increment primary key,sno char(9) not null unique,sname char(20) not null,ssex char(2) check(ssex in ('男','女'))，sage smallint check(sage>=15 and sage<=45),sdept char(20) default '计算机系');

创建课程表course
课程号 cno：4个字符 主键
课程名 cname： 50个可变长字符，不允许为空
先修课程号 cpno： 4个字符
学分 ccredit： smallint
先修课程号 cpno 参照 课程号cno 取值

create table course(cno char(4) primary key,cname varchar(50) not null,cpno char(4),ccredit smallint,foreign key(cpno) references course(cno));

创建成绩表sc
学号 sno：9个字符
课程号 cno：4个字符
成绩 grade： smallint
sno,cno：组合主键
sno参照student表的sno取值
cno参照course表的cno取值

create table sc(sno char(9) not null,cno char(4) not null,grade smallint,primary key(sno,cno),foreign key(sno) references student(sno),foreign key(cno) references course(cno));

3.1.2 修改表结构

定义

alter table 表名 modify 列名 新数据类型 约束;

例：修改student的sdept为40个字符宽度

alter table student modify sdept char(40);

3.1.3 增加列

定义

alter table 表名 add 列名 数据类型 约束;

例：在student中增加address列

alter table student add address varchar(50) not null;

3.1.4 删除列

定义

alter table 表名 drop column 列名;

例：删除student的sage列

alter table student drop column sage;

3.1.5 增加约束

定义

alter table 表名 add constraint 约束名 约束类型(列名);

例：在student中增加sno为主关键字

alter table student add primary key(sno);

例：添加sc的sno列的外码约束为student的sno

alter table sc add foreign key(sno) references studnet(sno);

3.1.6 删除约束

定义

alter table 表名 drop 约束类型 约束名

例：删除student的主键约束

alter table student drop primary key

3.1.7 删除表

定义

drop table 表名

3.2 数据查询

定义

select 查询目标from 目标所在表where 条件group by 分组依据having 分组条件order by 排序依据limit 限制;

3.2.1 简单查询

1. 查询全体学生的学号、姓名

select sno,sname from student;

2. 查询全部列

select * from student;select * from course;select * from sc;

3. 查询全体学生的姓名和出生年份

select sname as 'student name',2023-sage 'birthday' from student;

3.2.2 条件（where）

4. 查询计算机系全体学生的姓名

select sname from student where sdept='计算机系';

5. 查询年龄小于20岁的学生的姓名

select sname from student where sage<20;

6. 查询计算机系年龄小于20岁的学生的姓名

select sname from student where sage<20 and sdept='计算机系';

7. 查询年龄介于22岁到24岁的学生姓名和年龄

select sname,sage from student where sage>=22 and sage<=24;

select sname,sage from student where sage between 22 and 24;

8. 查询信息系、数学系和计算机系学生的姓名和性别

select sname,ssex from student where sdept='信息系' or sdept='数学系' or sdept='计算机系';

select sname,ssex from student where sdept in ('信息系','数学系','计算机系');

9. 查询未确定系学生的基本信息

select * from student where sdept is null;

10. 查询无考试成绩的学生的学号

select sno from sc where grade is null;

11. 查询有考试成绩的学生的学号和成绩

select sno,grade from sc where grade is not null;

3.2.3 消除（distinct）

12. 查询由哪些学生选修了课程，只需给出学号（消除重复值）

select distinct sno from sc;

3.2.4 模糊（like）

13. 查询姓王的同学的基本信息

select * from student where sname like "王%";

14. 查询姓名中有王字的同学的基本信息

select * from studentwhere sname like "%王%";

3.2.5 排序（order by）

15. 将学生按年龄由小到大排序

select * from student order by sage asc;

16. 输出选修c02课程学生的学号和成绩，按成绩降序排序

select sno,grade from sc where sno='c02' order by grade desc;

3.2.6 分组（group by）

17. 统计学生的总人数

select count(*) from student group by sno;

18. 统计选修了课程的学生的人数

select count(distinct sno) from sc group by sno;

19. 计算学号为200215121学生的考试成绩总和

select sum(grade) from sc where sno='200215121' group by sno;

20. 计算c01课程的平均成绩

select avg(grade) from sc where cno='c01' group by cno ;

21. 查询最大的学生年龄

select max(sage) from student group by sno;

22. 统计每门课的平均成绩，输出课程号和平均成绩

select cno,avg(grade) from sc group by cno;

23. 统计每门课的选修人数，输出课程号和课程选修人数

select cno,count(*) from sc group by cno;

24. 查询每名学生的选课门数和平均成绩

select sno,count(*),avg(grade) from sc group by sno;

25. 查询选修了3门以上课程的学生的学号

select sno from sc group by sno having count(*)>3;

26. 查询选课门数大于等于4门的学生的平均成绩和选课门数

select sno,avg(grade),count(*) from scgroup by sno having count(*)>=4;

27. 查询选修门数超过3门，且平均分在70以上的学生的学号，选修门数，平均成绩，并按成绩从高到低排序

select sno,count(*),avg(grade) from scwhere grade>=60group by snohaving count(*)>3 and avg(grade)>70order by avg(grade) desc;

3.2.7 限制（limit）

28. 查询平均成绩前三名同学的学号，姓名，平均成绩

select student.sno,student.sname,avg(sc.grade) from student,scwhere student.sno=sc.snogroup by student.snoorder by avg(sc.grade)limit 3;

3.2.8 单行输出（group_concat）

29. 查询200215121学生的选课情况，显示：学号，课程号（横向排列成一行，用’,’分隔）

select sno,group_concat(cno) from sc where sno='200215121';

30. 查询每个学生的选课情况，显示：学号，课程号（横向排列成一行，用’,’分隔）

select sno,group_concat(cno) from sc group by sno;

31. 查询学生的选课情况，要求输出学号，姓名，课程门数，课程名列表（用逗号分隔），按照学号升序排序

select student.sno,student.sname,count(sc.cno),group_concat(cname order by cname separator ',') from sc,student,course where student.sno=sc.sno and course.cno=sc.cnogroup by student.snoorder by sno asc;

3.2.9 多表查询（连接）

32. 查询每个学生的基本信息及其选课情况

select student.sno,sname,ssex,sage,sdept,cno,gradefrom student left join sc on student.sno=sc.sno;

33. 查询计算机系学生的选课情况，要求输出姓名，课程号，成绩

select student.sname,sc.cno,sc.grade from student,scwhere student.sno=sc.sno and sdept='计算机系';

34. 查询计算机系学生的选课情况，要求输出姓名，课程名，成绩

select student.sname,course.cname,sc.grade from student,course,scwhere student.sno=sc.sno and course.cno=sc.cno and sdept='计算机系';

35. 查询计算机系学生的选课情况，要求输出姓名，课程名

select student.sname,course.cname from student,course,scwhere student.sno=sc.sno and course.cno=sc.cno and sdept='计算机系';

36. 查询学生的选课情况，要求输出学号，姓名，课程代号，成绩

select student.sno,student.sname,sc.cno,sc.grade from student,scwhere student.sno=sc.sno;

37. 查询学生的选课情况，要求输出学号，姓名，课程代号，成绩（包括未选课学生的信息）

select student.sno,student.sname,sc.cno,sc.gradefrom student left join sc on student.sno=sc.sno;

38. 查询各门课程的先修课名称情况

select a.cno,a.cname,b.cname from course a,course bwhere a.cpno=b.cno;

3.2.10 子查询（嵌套）

39. 查询与刘晨在同一个系的学生

select * from student where sdept=(select sdept from student where sname='刘晨')and sname!='刘晨';

40. 查询年龄最小的同学信息

select * from student where sage=(select min(sage) from student);

select * from student order by sage asc limit 1;

41. 查询有成绩大于90分的学生的学号和姓名

select student.sno,student.sname from student,scwhere student.sno=sc.sno and grade>90;

select sno,sname from student where sno in(select sno from sc where grade>90);

42. 查询有成绩大于90分的学生的学号和姓名和成绩

select student.sno,student.sname,sc.grade from student,scwhere student.sno=sc.sno and grade>90;

43. 查询选修了c02课程且成绩高于此课程的平均成绩的学生的学号和成绩

select sno,grade from sc where grade>(select avg(grade) from sc where cno='c02' group by cno);

44. 查询每门课课程且成绩高于此课程的平均成绩的学生的学号和成绩

select a.sno,a.grade from sc a where grade>(select avg(grade) from sc b where a.cno=b.cno group by b.cno);

45. 查询每个学生成绩高于他选修课的平均成绩的学生的学号和课程号及成绩

select sno,cno,grade from sc a where grade>(select avg(grade) from sc b where b.sno=a.sno);

46. 查询其他系中比信息系某一学生年龄小的学生姓名和年龄

select sname,sage from student where sdept!='信息系'and sage<(select max(sage) from student where sdept='信息系');

select sname,sage from student where sdept!='信息系' and sage<any(select age from student where sdept='信息系');

47. 查询所有选修了c02的学生的姓名

select sname from student where exists(select * from sc where cno='c02' and sno=student.sno);

select sname from student where sno in (select sno from sc where cno='c02');

select sname from sc,student where sc.sno=student.sno and sno='c02';

48. 查询所有未选修c02课程的学生的姓名

select sname from student where not exists(select * from sc where cno='c02' and sno=student.sno);

select sname from student,scwhere student.sno=sc.sno and cno!='c02';

49. 查询所有选修了’数据库’课程的学生的姓名

select sname from student,sc,coursewhere student.sno=sc.sno and course.cno=sc.cno and cname='数据库';

select sname from student where exists(select * from sc where cno=(select cno from course where cname='数据库'));

3.2.11 复杂查询（exists）

50. 查询同时选修了c01和c02课程的学生的学号

select sno from sc where cno='c01' and cno='c02';

select distinct sno from sc where sno in (select sno from sc where sno='c01') and sno in (select sno from sc where sno='c02');

select distinct sno from sc a where exists(select * from sc b where b.sno=a.sno and cno='c01') and exists(select * from sc c where c.sno=a.sno and cno='c02');

51. 查询同时选修了’数据库’和’数据结构’课程的学生的学号

select distinct sno from sc a whereexists(select * from sc b where b.sno=a.sno and cno=(select cno from course where cname='数据库'))and exists(select * from sc c where c.sno=a.sno and cno=(select cno from course where cname='数据结构'));

52. 查询选修了全部课程的学生的姓名（即查询这样的学生，没有一门课是他不选修的）

select sname from student where not exists(select * from coursewhere not exists(select * from sc where sno=student.sno and cno=course.cno);

53. 查询选修了学号为’200215122’学生选修的全部课程的学号

select distinct sno from sc awhere not exists(select * from sc b where sno='200215122' and not exists(select * from sc c wherec.sno=a.sno and c.cno=b.cno);

3.2.12 组合查询（union）

54. 查询所有课程名与学生名并将查询列命名为name

(select sname as name from student) union(select cname from course);

3.3 数据更新

3.3.1 插入（insert）

定义

insert into 表名 (列名) values (值列表);

1. 将新生记录为(200821105,陈冬,男,18,信息系)插入到student表中

insert into student (sno,sname,ssex,sage,sdept)values('200821105','陈冬','男',18,'信息系');

insert studentvalues('200821105','陈冬','男',18,'信息系');

2. 将与刘晨同一个系的新生记录(200821105,陈冬,男,18)插入到student表中

insert into student set sno='200821105',sname='陈冬',ssex='男',sage=18,sdept=(select sdept from student where sname='刘晨');

3. 将新生记录(200821107,陈冬,男,18,信息系),(200821118,刘晨,男,18,信息系)一起插入到student表中

insert into studentvalues('200821107','陈冬','男',18,'信息系'),('200821108','刘晨','男',18,'信息系');

4. 在sc表中插入一新记录学号为200821105,课程为c01

insert into sc (sno,cno) values('200821105','c01',null);

insert into sc values('200821105','c01',null);

5. 备份c01的成绩到c01_cj新表中

create table c01_cj like sc;insert into c01_cjselect * from sc where cno='c01';

3.3.2 修改（update）

定义

update 表名 set 列名=表达式 where 条件;

1. 将计算机系全体学生的成绩加5分

update sc set grade=grade+5where sno in (select sno from student where sdept='计算机系');

2. 将平均成绩80分以上的学生成绩加3分

update sc set grade=grade+3 where sno in(select sno from sc group by sno having avg(grade)>80);

3. 将数据结构3-5名学生的成绩加5分

update sc set grade=grade+5 where sno=(select sno from course where course.cno=sc.cno and cname='数据结构'order by grade desc limit 2,4);

3.3.3 删除（delete）

定义

delete from 表名 where 条件;

1. 删除学号为200215121的学生的记录

delete from sc where sno='200215121';delete from student where sno='200215121';

2. 删除所有学生的记录

delete from student;

3. 删除计算机系所有学生的选课记录

delete from sc where sno in(select sno from student where sdept='计算机系');

四、视图

4.1 创建视图

定义

create view 视图名 as 子查询;

1. 建立系名为计算机系的学生的视图

create view student_view asselect sno,sname,ssex,sdept from student where sdept='计算机系';

2. 建立系名为计算机系的学生的视图，要求对视图的更新进行检查

create view student_view asselect sno,sname,ssex,sdept from student where sdept='计算机系'with check option;

3. 建立信息系选修了c01课程的学生的视图

create view student_view asselect student.sno,student.sname,sc.gradefrom student,sc where student.sno=sc.sno and sdept='信息系' and cno='c01';

4.2 其他

同基本表操作一样

总结

数据库→基本表（创建-增删改查）→视图（创建-增删改查）