数据库-个人笔记
数据库设计阶段: 1需求分析 2概念模型设计 3逻辑结构设计 4物理结构设计 5数据库实施 6数据库运行和维护
E-R关系图
在E-R图中,通常使用以下形状表示不同的元素(注意主键之间的对应关系):
-
实体:通常用矩形框表示,矩形框内写明实体的名称,例如"学生"、"课程"等。
-
属性:通常用椭圆形表示,椭圆内写明属性的名称,例如"学生姓名"、"课程编号"等。
-
关系:通常用菱形表示,菱形内写明关系的名称,例如"选课"、"教授"等。
-
主键:通常使用下划线标记主键属性,有时也会在属性旁边注明"PK"表示主键。
实体与实体之间有三种联系,分别是一对一(1:1),一对多(1:n),多对多(m:n)
常见约束
- 主键约束(Primary Key):主键是唯一的,一个数据表只能由一个主键。
- 唯一约束(Unique):确保列中的值唯一,但允许空值存在。与主键约束不同的是,唯一约束允许列中的值为NULL。
- 外键约束(Foreign Key):用于在两个相关表之间建立引用关系,确保引用的数据在被引用表中存在。
- 检查约束(Check ):用于指定列中的值必须满足特定的条件,例如数值范围、格式要求等。
- 非空约束(Not Null ):确保列中的值不为空,即不允许NULL值存在。
- 默认约束(Default):默认约束是数据库中的另一种常见约束类型,用于在插入新记录时为某个列提供默认值,定义日期和字符型数据要加引号
数据库操作
create DATABASE <db_name>; 创建数据库
drop DATABASE <db_name>; 删除数据库
SHOW CREATE DATABASE <db_name>; 查看创建的数据库信息
表操作
DISTINCT <column_name> ; 用于消除结果中重复字段
CREATE TABLE <table_name> (column_name column_type) ; 创建数据表
drop TABLE <table_name> ; 删除整个数据表
DELETE FROM <table_name> [WHERE condition]; 删除表中数据
truncate table_name ; 删除表中全部数据
Replace into table_name (字段列表) values (值); 替换数据/插入单条数据
update table_name SET 字段1=value1, 字段2=alue2 [WHERE condition]; 更新数据
create table <新表名> select * from <源表名> ; 复制表结构及数据到新表
CREATE TABLE 新表名 SELECT * FROM 源表名 W HERE FALSE ; 只复制表结构
CREATE TABLE 新表名 LIKE 源表名; 只复制表结构
ALTER操作
ALTER DATABASE db_name CHARACTER SET <gbk>; 修改数据库字符集
Alter table old_table_name rename [to] new_tb_name ; 修改表名
ALTER TABLE table_name ADD <column_name data_type>; 表添加字段
ALTER TABLE table_name MODIFY column_name <data_type>; 修改字段数据类型
ALTER TABLE table_name CHANGE [old_column_name] <new_column_name> <data_type>; 修改字段名称
ALTER TABLE table_name DROP <column_name> ; 删除表中字段
ALTER TABLE table_name ADD <约束名> (column_name) ; 添加字段约束
ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT value ; 修改字段默认值
ALTER TABLE table_name add constraint ck_gprice check(gprice>=0);
添加自定义约束 ck_gprice是约束名 check括号里面是表达式 check约束:常用于指定属性的取值范围
数据查询
select * from table_name;
用于范围比较的关键字: between...and... 和 in
链接(多表)查询
使用自连接查询时需为表指定两个别名
,一个表中查找具有相同列值的行
查找课程号不同、成绩相同的学生的学号、课程号和成绩。(自连接)
SELECT a.学号,a.课程号,a.成绩
from xs_kc as a JOIN xs_kc as b
on a.成绩=b.成绩
WHERE a.课程号 != b.课程号
and a.学号=b.学号;
等值多表连接
Select 表名.字段名 From 表1,表2
where·表1.字段名=表⒉字段名
[and·表2字段名=表3.字段名]
多表(链接)查询
Select·表名.字段名
From·表1
join 表2. on [条件]
join 表3 on [条件]
[where·条件表达式]
聚合函数
COUNT():计算指定列或行的数量。
SUM():计算指定列的总和。
AVG():计算指定列的平均值。
MAX():找出指定列的最大值。
MIN():找出指定列的最小值。
having 后面跟聚合函数条件语句,类似于where但不能用where
group by表示分组字段
聚合函数
select 聚合函数,列(要求出现在group by的后面)from 表
where 筛选条件 group by <字段> [having] 聚合函数条件 [order by 子句]
SELECT avg(成绩) as 平均成绩,学号 from xs_kc
GROUP BY 学号
HAVING avg(成绩)>75;
order by 语句表示排序字段 ASC表升序(系统默认), DESC 表降序
LIMIT 子句主要用于限制被SELECT语句返回的行数。
limit 5 表示返回5行结果
limit 3,5 表示从第4条记录开始的5条记录。
SELECT 列1, 列2 FROM 表名 ORDER BY 列1 ASC; -- 按照列1升序排列
SELECT 列1, 列2 FROM 表名 ORDER BY 列1 DESC; -- 按照列1降序排列
查找XS表中从第4位同学开始的5位学生的信息。
SELECT * FROM xs ORDER BY 学号 LIMIT 3,5;
like语句模糊查询
‘a%’ 以a开头的数据
‘%a’ 以a结尾的数据
‘%a%’ 含有a的数据
'%_ t ’ 倒数第二位是t
like ‘%#_%’ escape ‘#’ 含有下划线 转义字符 ‘#’
ESCAPE
关键字指定了一个转义字符 “%” 代表0个或多个字符 “_"代表单个字符
SELECT 学号, 姓名 FROM xs WHERE 学号 LIKE '%!_%' ESCAPE '!';
第一个百分号表示可以匹配任意数量的字符。第二个百分号也表示可以匹配任意数量的字符。
组合嵌套
组合嵌套查询要分步写好题干需要的结果
查找选修了 开课学期最早的 课程 的学生学号和姓名 (多值嵌套)
SELECT 学号,姓名 from xs
WHERE 学号 in
(SELECT 学号 from xs_kc WHERE 课程号 =
(SELECT 课程号 from kc WHERE 开课学期=
(SELECT min(开课学期) from kc)));
when-case语句
<case when>用于在SQL查询中根据不同的条件执行(显示)不同的操作
用于替换结果中的数据
SELECT 学号,姓名,总学分,
CASE
when 总学分 is null then '尚未选课'
when 总学分<50 then '不及格'
when 总学分 IN(50,52) then '合格'
when 总学分>52 then '优秀'
else <resoult>
END AS 等级 from xs
where 专业名='计算机';