Mysql基础（一）

0x01 sql基本概念

基本表：

• 本身独立存在的表
• sql中一个关系就对应一个基本表
• 一个（或多个）基本表对应一个存储文件
• 一个表可以带若干索引

视图：

• 从一个或几个基本表导出的表
• 数据库中只存放视图的定义而不存放视图对应的数据
• 视图是一个虚表
• 用户可以在视图上再定义视图

0x02 基本表的定义

sql的动词：

SQL功能	动词
数据查询	SELECT
数据定义	CREATE、DROP、ALTER
数据操纵	INSERT、UPDATE、DELETE
数据控制	GRANT、REVOKE

1、创建数据库

create database st;

2、创建student，course，sc表
create table <表名>
(<列名> <数据类型> [<列级完整性约束条件>]，
<列名> <数据类型> [<列级完整性约束条件>]
);

create table student
(sno char(9) 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)
);

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

3、修改基本表
alter table <表名>
①向student表中增加“入学时间”列，其数据类型为日期型。新增的一列均为空值。

alter table student add s_sentrance date;

②将年龄的数据类型由字符型改为整型。

alter table student modify sage int(20);

③修改表名

alter table student rename to stu;

④删除表中字段

alter table student drop column s_sentrance;

4、删除基本表

drop table student cascade;
#cascade,删除基本表的同时，将相关的依赖对象一起删除。

0x03数据查询

查询语句：

• select子句：指定要显示的属性列
• from子句：指定查询对象（基本表或视图）
• where子句：指定查询条件
• group by子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组，通常会在每组中作用聚集函数。
• having短语：只有满足指定条件的组才能予以输出。
• order by子句：对查询结果表按指定列值的升序或降序排列。

1、单表连接
①查询全体学生的学号与姓名

select sno,sname from student;

②查询全体学生的姓名、学号、所在系

select sname,sno,sdept from student;

③查询全系学生的详细记录

select * from student;

④查询全体学生的姓名及出生年份

select sname,2020-sage from student;

⑤查询全体学生的姓名、出生年月和所在的院系，用小写字母表示系名

select sname,'year of birth:',2020-sage,lower(sdept) fromm student;

⑥查询选修了课程的学生学号

select distinct sno from sc;
#指定distinct关键字，去掉表中重复的行

⑦常用的查询条件

查询条件	谓词
比较	=,>,<,>=,<=,!=,<>,!>,!<,not+上述比较运算符
确定范围	between and,not between and
确定集合	in,not in
字符匹配	like，not like
空值	is null,is not null
多重条件（逻辑运算）	and,or,not

⑧查询计算机科学系全体学生名单

select sname from student where sdept='cs';

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

select sname,sage from student where sage<20;

①查询考试成绩不及格的学生的学号

select distinct sno from sc where grade<60;

②查询年龄在20-23之间的学生的姓名，系别和年龄

select sname,sdept,sage from student where sage between 20 and 23;

③查询年龄不在20-23岁之间的学生姓名，系别和年龄

select sname,sdept,sage from student where sage not between 20 and 23;

④查询计算机科学系（cs）、数学系（ma）和信息系（is）学生的姓名和性别

select sname,ssex from student where sdept in ('cs','ma','is');

⑤查询既不是计算机科学系，数学系，也不是信息系的学生的姓名和性别。

select sname,ssex from student where sdept not in ('is','ma','cs');

⑥查询学号为201215121的学生的详细情况

• %,代表任意长度（可以为0）的字符串，例a%b,表示以a开头，以b结尾的任意长度的字符串
• _（下横线），代表任意单个字符

select * from student where sno like '201215121';

⑦查询所有姓刘学生的姓名，学号和性别

select sname,sno,ssex from student where sname like '刘%';

⑧查询姓“欧阳”且全名为三个汉字的学生的姓名。

select sname from student where sname like '欧阳_';

⑨查询名字中第二个字为“阳”字的学生的姓名和学号

select sname,sno from student where sname like '_阳%'；

①查询所有不姓刘的学生姓名，学号和性别

select sname,sno,ssex from student where sname not like '刘%';

②查询db_desing课程的课程号

select cno from course where cname like 'db\_design';
#反斜杠\,用于转义

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

select sno,cno from sc where grade is null;
#‘is’不能用‘=’替代

④查询所有有成绩的学生学号和课程号

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

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

select sname from student where sdept='cs' and sage<20;

⑥order by子句

• 按一个或多个属性列排序
• 升序：asc。降序：desc，默认升序

查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列

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

⑦查询全体学生情况，按所在的系号升序，同一系按年龄降序排列

select * from student order by sdept,sage desc;

⑧聚集函数

• 统计元组个数：count(*)
• 统计一列中值的个数：count(distinct <列名>)
• 计算一列值的总和（此列必须是数值型）：sum(distinct <列名>)
• 计算一列值的平均值（此列必须是数值型）：avg(distinct <列名>)
• 计算一列中的最大值和最小值：max(distinct <列名>)，min(distinct <列名>)
  ⑨查询学生总人数

select count(*) from student;

①查询选修了课程的学生人数

select count(distinct sno) from sc;

②计算1号课程的学生平均成绩

select avg(grade) from sc where cno='1';

③查询选修一号课程的学生的最高分数

select max(grade) from sc where cno='1';

④查询学生201215121选修课程的总学分数

select sum(ccredit) from sc,course where sno='201215121' and sc.cno=course.cno;

⑤group by子句

• 细化聚集函数的作用对象
• 如果未对查询结果分组，聚集函数将作用于zhengge查询结果
• 对查询结果分组后，聚集函数将分别作用于每个组
• 按指定的一列或多列值分组，值相等的为一组

⑥求各个课程号及相应的选课人数

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

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

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

⑧查询平均成绩大于等于90分的学生学号和平时成绩

select sno,avg(grade) from sc group by sno having avg(grade)>=90;
#where子句中是不嗯给你用聚集函数作为条件表达式

⑨having短语与where子句的区别

• 作用对象不同
• where子句作用于基表或视图，从中选择满足条件的元组
• having短语作用于组，从中选择满足条件的组