SQL复习

1.1数据库系统的结构

1、单用户数据库系统：整个数据库系统(包括应用程序、DBMS、数据)装在一台计算机上
2、主从式结构的数据库系统：一个主机带多个终端的多用户结构

3、分布式结构的数据库系统：数据库中的数据在逻辑上是一个整体，但物理地分布在计算机网络的不同结点上。
4、客户／服务器结构的数据库系统：把DBMS功能和应用分开
5、浏览器/应用服务器/数据库服务器结构：服务器端分为Web服务器、应用服务器、数据库服务器等。
6、嵌入式数据管理系统的结构：
将数据库嵌入到了应用程序进程中，消除与客户机及服务器配置相关的开销

2.1 数据库基础知识

一创建和查看数据库
创建数据库，在数据库系统中划分一块存储数据的空间
1.创建数据库语法

CREATE DATABASE 数据库名称；

“数据库名称”是唯一的，不可重复出现
2.查看数据库语句

SHOW DATABASES；

使用SHOW语句查看已存在的数据库
3.查看某个已经创建的数据库信息

SHOW CREATE DATABASE 数据库名称；

1.2修改数据库
基本语法格式

ALTER DATABASE数据库名称 DEFAULE CHARACTER SET 编码方式 COLLATE 编码方式_bin

1.3删除数据库
基本语法格式

DROP DATABASE 数据库名称

2.2 数据类型
整数类型
浮点数类型和定点数类型
日期和时间类型
字符串类型
二进制类型
数据类型 字节数
一个字节8位，TINYINY 无符号数最大值28-1=255
浮点数类型和定点数类型

   FLOAT  4字节数
浮点数
	DOUBLE  8 字节数
定点数  DECIMAL（M，D） M+2 字节数 M数据的长度，D小数点后的长度

日期和时间类型
YEAR
DATE
TIME
DATETIME
TIMESTAMP

数据（Data）：存储在某种媒体上、用来描述某个事物的、能够识别的物理符号。如文字、数字、图形、声音、视频等。
数据处理：将数据转换为信息的过程。包括：收集、整理、存储、加工、分类、维护、排序、检索和传输等。
数据库（DB） ∈ 数据库管理系统（DBMS） ∈ 数据库系统（DBS）
数据库(DB) ：指存储在计算机内、有组织的、可共享的数据集合。
数据库管理系统(DBMS) ：用于数据库的建立、使用和维护，是数据库系统的核心软件，属于系统软件。
数据库系统(DBS) ：由硬件系统、数据库管理系统、应用程序、数据库用户组成。
数据库是存储和管理数据的仓库，但数据库本身不能直接存储数据，数据是存储在表中的。
模型是现实世界的模拟和抽象

概念模型
逻辑模型
物理模型

1. 关于概念模型中的几个术语：
   实体：存在于现实中，可以用一组属性来描述物体
   实体集：具有相同类型及相同属性的实体的集合（表）
   实体属性：实体的特征和性质 （列）----属性（字段）
   实体型：用实体名及属性名刻画全部同质实体的共同特征和性质
2. 逻辑模型有层次模型、网状模型、关系模型
3. 关系模型组成要素:
   （1）数据结构 （2）数据操作 （3）数据约束
   1.常用的关系操作
   （1）集合操作
   并、交、差、笛卡儿积、除
   （2）数据查询
   选择、投影、连接
   （3）数据更新
   插入、删除、修改

属性——字段（列）
元组——一行
R (A1，A2，…，An)

R (A1 ，A2，…，An)
学生表（学号，姓名，性别，专业号，年龄…）
课程表（课程编号，课程名称，学分…）
成绩表（学号，课程号，成绩）

4. SQL语言 select

select*
from students3
where  1=1 and sex=”男”

1.笛卡尔积
2.选择去行，投影去列
3选择后——等值连接（只去了行没去列）
4选择后再投影——自然连接（行列都去）
表的拆分 （“糖葫芦”）
读者ID 书籍 出版社 ISDN 馆藏信息 借还表
借还表id name 出版社id 馆藏ID 表ID
bookid ISDN ISDN 读者ID
出版社id 出版社id 借还日期
bookid 还书日期

冗余ID——并行
2.3数据表的基本操作
1.创建数据表
2.查看数据表
3.修改数据表
4.删除数据表
2.4 表的约束
PRIMARY KEY 主键约束，用于唯一标识对应的记录
FOREIGN KEY 外键约束
NOT NULL 非空约束
UNIQUE 唯一性约束
DEFAULT 默认值约束，用于设置字段的默认值
2.5 设置表的字段值自动增加
字段名 数据类型 AUTO_INCREMENT;
2.6 索引
MySQL 通配符 %
select*
from students
where name like “王%” #模糊查找
<=> 等价于 is
关联表创建更多索引
1.普通索引 2.唯一性索引3.全文索引4.单列索引5.多列索引
2.空间索引
反范式化设计
根据实际需求，允许数据冗余
分页：
Limit (pages-1)*rows,rows
取左子串函数
Leaf（“…”,n）
Leaf（“中华人民共和国”,2）——“中华”
取右子串函数
right（“…”,n）
right（“中华人民共和国”,3）——“共和国”
取中间串函数
Mid（“中华人民共和国”，3,2）——“人民共和国”

插入单行记录：
（1）1.insert into 表名（字段名，字段名，…）
2.values （字段名对应的值，值，值，…）
（2）insert into 表名
set 字段名1 = 值1[，字段名2 =值2，……]
如果只希望指定部分字段来插入记录，也可以在set关键字后面指定了多个“字段名=值”对来实现，它们之间用逗号分隔。

插入多行记录

insert into 表名(字段名1，字段名2，……)
value(值1，值2，……)，(值1，值2，……)，……;
将表2中的记录复制到表1中
insert into 表名1(字段名1,字段名2……)
select 字段名1,字段名2……
from 表名2
select min(hours) as minhour
from course

内联表 inner join
习惯（表名.字段名）
中间表写前面
select stuClassName as 班级名称,
sum(if(sex=“男”,cnt,0)) as “男”,
sum(if(sex=“女”,cnt,0)) as “女”
from(select stuClassName as 班级,sex,count() as cnt //不可以再次取别名
from student
GROUP BY stuClassName,sex) as a
GROUP BY stuclass
select stuClassName as “班级名称”,
sum(if(sex=“男”,cnt,0)) as “男”,
sum(if(sex=“女”,cnt,0)) as “女”
from(select stuClassName ,sex,count() as cnt
from student
GROUP BY stuClassName,sex
) as a
GROUP BY stuClassName
分类：group by 汇总：聚合函数
select student.name,
course.name,
teacher.name,
score.score
from score
inner join course
on score.courseId=course.id
inner join student
on score.studentId= student.id
inner join teacher
on score.teacherId=teacher.id

select teaching.week as 周一,
teaching.patch as 第一节,
classRoom.building as 教学楼,
classRoom.classRoom as 教室
from classroom
INNER JOIN teaching
on classRoom.id=teaching.classroomId
where teaching.week=1 and teaching.patch=1

select stuclass.className as 班级名称,
course.name as 课程名称,
teacher.name as 任课教师,
concat(classroom.building,classroom.classRoom) as 上课教室,
teaching.week as 星期,
concat(teaching.patch,"-",teaching.patch+1) as 节次,
concat(teaching.startWeek,"-",teaching.endWeek) as 起止周,
teaching.isFull as 单双周
from teaching
inner join stuClass
on stuClass.id=teaching.stuClassId
inner join course
on course.id=teaching.courseId
inner join teacher
on teacher.id=teaching.teacherId
inner join classroom
on classroom.id=teaching.classroomId
SELECT DISTINCT course.name
from score
inner join course
on score.courseId=course.id
3.select student.name,
course.name as 课程,
score.score as 成绩
from course
inner join score
on course.id=score.courseId
inner join student
on score.studentId =student.id
ORDER BY course.name,score desc
4.limit 0,5
5.select course.name as 课程,
max(score) as 最高分,
min(score) as 最低分
from score
inner join course
on course.id=score.courseId
GROUP BY course.name