mysql - 关系数据库应用编程基础_2、查询姓“李”且全名为三个汉字的学生的姓名。-CSDN博客

本文链接：https://blog.csdn.net/ls_ange/article/details/83011367

mysql - 关系数据库应用编程基础

实体和属性
- 实体
  现实世界中任何一个可以识别的对象
- 属性
  实体所具有的特性,一个实体可用若干属性来描述
- 关系数据库中的表现
  实体的实例是储存在表中的行，属性是储存在表中的列。
实现实体的关系
- 键（key）
  在实体属性中，用于区别实体集合中不同个体的某个属性或某几个属性的组合，称为关键字（键）。
- 主键（Primary key）
  一个实体的实例上可以有多个不同的键存在，所有这些符合条件的键称为候选键，被指定作为键的称为主键。
- 外键（Foreign key）
  非本实体实例的键，但它是其他实体实例的键。又称为外关键字或外部码。
关系的三类完整性约束
- 实体完整性:一定要有主键(只有唯一标志一行的用途,可以用组合键)
- 引用完整性:通过外键实现
- 域完整性和域约束:使用默认值等
范式

第一范式:属性是不能再分的数据项

第二范式:每个属性都要完全依赖主键

第三范式:不能传递依赖
约束
约束名的取名规则推荐采用：约束类型_约束字段
- 主键（Primary Key）约束：如 PK_stuNo
- 唯一（Unique Key）约束：如 UQ_stuID
- 默认（Default Key）约束：如 DF_stuAddress
- 检查（Check Key）约束：如 CK_stuAge
- 外键(Foreign Key)约束：如 FK_stuNo

基本操作

学生-课程数据库 :

学生表：Student(Sno,Sname,Ssex,Sage,Sdept)

课程表：Course(Cno,Cname,Cpno,Ccredit)

学生选课表：SC(Sno,Cno,Grade)

新建数据库

DROP DATABASE IF EXISTS mystudents;

CREATE DATABASE mystudents DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

DROP DATABASE testdb

新建表

student

create table student(
    id int PRIMARY KEY AUTO_INCREMENT,
    sno VARCHAR(10) UNIQUE,
    sname VARCHAR(20) not NULL,
    sgender VARCHAR(2) DEFAULT '女',
    sbirthday DATE,
    sdept VARCHAR(20)
)engine=innodb auto_increment=5

course

create table course(
    cno VARCHAR(4) PRIMARY KEY,
    cname VARCHAR(40) not NULL,
    cpno VARCHAR(4),
    ccredit SMALLINT,
    FOREIGN KEY (cpno) REFERENCES course(cno)
)

CREATE table sc(
    sno VARCHAR(10),
    cno VARCHAR(4),
    grade SMALLINT,
    PRIMARY KEY(sno,cno),
    FOREIGN Key(sno) REFERENCES student(sno),
    FOREIGN KEY(cno) REFERENCES course(cno)
)

查看表结构

desc sc;

SHOW CREATE TABLE sc;

添加删除约束

ALTER TABLE course ADD PRIMARY KEY(cno,sno);
ALTER TABLE course DROP PRIMARY KEY;

ALTER TABLE sc ADD FOREIGN KEY(sno) REFERENCES student(sno);
ALTER TABLE sc ADD FOREIGN KEY(cno) REFERENCES course(cno);

ALTER TABLE student ADD UNIQUE(sno,cno);
ALTER TABLE student DROP INDEX name;

ALTER TABLE student ALTER ssex set DEFAULT '女';

添加数据

INSERT INTO <表名> [(<属性列1>[，<属性列2 >…)] VALUES (<常量1> [，<常量2>] … )

修改数据

UPDATE <表名> SET <列名>=<表达式>[，<列名>=<表达式>]… [WHERE <条件>]；

删除数据

DELETE FROM <表名> [WHERE <条件>]；

数据查询

SELECT '最棒哒',sname as 姓名,2018-YEAR(sbirthday) as 年龄 from student;

取消重复行

SELECT DISTINCT sno FROM sc

查询数学系全体学生的名单:

select sname from student where sdept like '数学'

查询所有年龄在20岁以下的学生姓名及其年龄:

SELECT sname,2018-YEAR(sbirthday) as sage from student where 2018-YEAR(sbirthday)<20

查询年龄在20~23岁之间的学生的姓名、系别和年龄(between …and…):

SELECT sname,sdept,2018-YEAR(sbirthday) as sage from student where 2018-YEAR(sbirthday) BETWEEN  20 and 23

查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别:

SELECT sname from student where sdept in ('数学','电子','计算机')

like
查询姓"李"且全名为三个汉字的学生的姓名

SELECT sname from student where sname like '李__'

is null
查询缺少成绩的学生的学号和相应的课程号

SELECT sno,cno from sc WHERE grade is NULL

not and or
查询计算机系年龄在20岁以下的学生姓名

SELECT sname from student where sdept like '计算机' and 2018-YEAR(sbirthday)<20

排序

升序：ASC；降序：DESC；缺省值为升序

查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。
```
SELECT * from student ORDER BY sdept asc,sage DESC
```
查看全体同学的数学成绩，并按降序排列
```
SELECT sname,mathscore from score ORDER BY mathscore DESC
```

聚合函数

count() sum() avg() max() min()
统计有多少学生参加考试

SELECT  COUNT(DISTINCT sno) from sc where grade is not null

3号课程的总成绩和平均分

SELECT SUM(grade),AVG(grade) from sc where cno like 'c003'

022学生的总分

SELECT SUM(grade) from sc where sno like 's022'

找出最低分

SELECT MIN(grade),cno,sno from sc

分组

HAVING短语与WHERE子句的区别：

WHERE从中选择满足条件的元组
HAVING短语作用于组，从中选择满足条件的组

查看每个人的总分和平均分

SELECT SUM(grade),AVG(grade),sno from sc GROUP BY sno

查看平均分最高的信息

SELECT AVG(grade),sno from sc GROUP BY sno ORDER BY AVG(grade) desc LIMIT 1

查看平均分不及格信息 where 首次筛选 having 分组后筛选

SELECT AVG(grade),sno from sc GROUP BY sno HAVING AVG(grade)<60

查看平均分大于70，并从高到低排列

SELECT AVG(grade),cno from sc GROUP BY cno HAVING AVG(grade)>70 ORDER BY AVG(grade) DESC

找出参加三门考试的同学

SELECT COUNT(sno),sno from sc GROUP BY sno HAVING COUNT(sno)=3

统计每门课程的选修人数

SELECT COUNT(sno) as '考试人数',cno from sc GROUP BY cno

统计选修人数少于2人的课程标号

SELECT COUNT(sno) as '考试人数',cno from sc GROUP BY cno HAVING COUNT(sno)<2

limit

limit 开始记录,总共取多少行记录

总分前3名的学生

SELECT SUM(grade),sno from sc GROUP BY sno ORDER BY SUM(grade) DESC LIMIT 0,4

总分倒数第一的学生

SELECT SUM(grade),sno from sc GROUP BY sno ORDER BY SUM(grade) asc LIMIT 1