数据库-个人笔记

数据库-个人笔记

数据库设计阶段: 1需求分析 2概念模型设计 3逻辑结构设计 4物理结构设计 5数据库实施 6数据库运行和维护

E-R关系图

在E-R图中，通常使用以下形状表示不同的元素(注意主键之间的对应关系)：

1. 实体：通常用矩形框表示，矩形框内写明实体的名称，例如"学生"、"课程"等。

2. 属性：通常用椭圆形表示，椭圆内写明属性的名称，例如"学生姓名"、"课程编号"等。

3. 关系：通常用菱形表示，菱形内写明关系的名称，例如"选课"、"教授"等。

4. 主键：通常使用下划线标记主键属性，有时也会在属性旁边注明"PK"表示主键。

   实体与实体之间有三种联系，分别是一对一（1：1），一对多（1：n），多对多（m：n）

常见约束

1. 主键约束（Primary Key）：主键是唯一的，一个数据表只能由一个主键。
2. 唯一约束（Unique）：确保列中的值唯一，但允许空值存在。与主键约束不同的是，唯一约束允许列中的值为NULL。
3. 外键约束（Foreign Key）：用于在两个相关表之间建立引用关系，确保引用的数据在被引用表中存在。
4. 检查约束（Check ）：用于指定列中的值必须满足特定的条件，例如数值范围、格式要求等。
5. 非空约束（Not Null ）：确保列中的值不为空，即不允许NULL值存在。
6. 默认约束（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 专业名='计算机';