day09

最新推荐文章于 2024-04-27 15:09:26 发布

一个胖小白

最新推荐文章于 2024-04-27 15:09:26 发布

阅读量187

点赞数

分类专栏：自己笔记文章标签： mysql

本文链接：https://blog.csdn.net/weixin_46002359/article/details/123753033

版权

笔记同时被 2 个专栏收录

75 篇文章 0 订阅

订阅专栏

自己

42 篇文章 0 订阅

订阅专栏

一、五大约束

1.1 主键约束

 特点：非空 唯一 一张表只能有一个主键

1.2 唯一约束

 1.关键字：unique
 2.特点：唯一  不重复 可以为空
 3.主键约束与唯一约束的区别
     A.主键约束一张表只能由一个   唯一约束一张表可以有多个
     B.主键约束不能为空          唯一约束可以为空
     C.唯一约束可以设置为联合唯一约束（多个列组成唯一约束）
 4.唯一约束创建的两种方法：
     A.创建表时候创建唯一约束
     B.创建表之后添加约束

第一种方法：

 CREATE DATABASE day09;
 USE day09;
 
 #创建表时候创建唯一约束
 CREATE TABLE p1(
     pid INT(11) PRIMARY KEY AUTO_INCREMENT,
     pname VARCHAR(20) UNIQUE
 );

第二种方法

 #添加唯一约束、
 ALTER TABLE p2 ADD UNIQUE(pname);
 语法：alter table 表名 add constraint 唯一约束的名称 unique（列名）
 #设置唯一约束的时候设置唯一约束的名称
 ALTER TABLE p2 ADD CONSTRAINT u_name UNIQUE(pname);
 语法：alter table 表名 drop index 唯一约束名（列名）
 #删除唯一约束
 ALTER TABLE p2 DROP INDEX pname;

联合唯一约束

 #联合唯一约束
 CREATE TABLE p3 (
     pid INT (11) PRIMARY KEY AUTO_INCREMENT,
      pname VARCHAR(20),
      page INT(10),
      UNIQUE(pname,page)
 ); 
 #验证联合唯一约束
 INSERT INTO p3(pname,page) VALUES("张三",15);

验证：

1.3 非空约束

 1.关键字；not null
 2.说明；在插入数据的时候必须插入数据，不能为空
 3.在创建表的时候进行设置

 
 #创建表的时候设置非空
 CREATE TABLE p4 (
     pid INT(11) PRIMARY KEY AUTO_INCREMENT,
     pname VARCHAR(20) NOT NULL
     
 );

1.4 默认约束

 1.关键字：default
 2.在创建表的时候进行设置
 3.作用：用于设置列的默认值
 4.说明：
     A.指定列设置默认值后，为插入数据，默认列显示默认值
     B.在插入数据后，插入default，也是默认值
     C.只有在插入不是默认值时候，才会替换掉默认值

#创建表设置默认值
CREATE TABLE p5(
	pid INT(11) PRIMARY KEY AUTO_INCREMENT,
	pname VARCHAR(20) DEFAULT "张三"
);

CREATE TABLE p6(
	pid INT(11) PRIMARY KEY AUTO_INCREMENT,
	pname VARCHAR(20) DEFAULT '张三'
);

在设置默认值时候，使用单引号和双引号都可以

二、表与表之间的联系

2.1简介

1.mysql是关系型数据库，表与表之间存在联系
2.表与表之间的关系：一对多 、多对一 、多对多
3.创建表之后添加外键

2.2 一对一的关系

#创建丈夫表
CREATE TABLE man(
	`mid` INT(11) PRIMARY KEY AUTO_INCREMENT,
	mname VARCHAR(20) 
	
);

#创建妻子表
CREATE TABLE women(
	wid INT(11) PRIMARY KEY AUTO_INCREMENT,
	wname VARCHAR(20),
	`mid` INT(11)
); 

语法：alter table 主表 add constraint 外键名 foreign key(`列名`) reference 副表名(`列名`)
#添加外键约束
ALTER TABLE women ADD CONSTRAINT fk_mid FOREIGN KEY(`mid`) REFERENCES man(`mid`);

注意点：

设置外键的 ` 这个不是单引号，而是1旁边的那个

设置外键一般都会有一个主表,相关联的外键所在的表为从表（子表），另外一个为主表

外键图

定义外键的规则：

主表必须存在于数据库里面，或者主表正在创建。如果是后者，主表和从表是同一个表，这种表为自参照表，这个结构为自参照结构

外键中的数据类型必须与主键里面的一致

主表里面必须定义主键

2.3 一对多的关系

#创建部门表
CREATE TABLE dept(
	did INT(11) PRIMARY KEY AUTO_INCREMENT,
	dname VARCHAR(20)
);

#创建员工表
CREATE TABLE emp(
	eid INT(11) PRIMARY KEY AUTO_INCREMENT,
	ename VARCHAR(20),
	did INT(11)
);
#添加外键关系
ALTER TABLE emp ADD CONSTRAINT fk_did FOREIGN KEY (did) REFERENCES dept(did);

效果图

2.4 删除外键

先删除外键外键

语法：alter table 表名 drop foreign key 外键名（列名）
例子：ALTER TABLE emp DROP FOREIGN KEY fk_eid

删除外键的索引

2.5 多对多的关系

表设计--使用中间表来进行设计

 
#创建演员表
CREATE TABLE actor (
	aid INT(11) PRIMARY KEY AUTO_INCREMENT,
	aname VARCHAR(20)
);

#创建角色表
CREATE TABLE role (
	rid INT(11) PRIMARY KEY AUTO_INCREMENT,
	rname VARCHAR(20)
);

#创建中间表
CREATE TABLE a_r(
	a_r_id INT(11) PRIMARY KEY AUTO_INCREMENT,
	aid INT(11),
	rid INT(11)
);

#添加外键
#演员表和中间表
ALTER TABLE a_r ADD CONSTRAINT fk_aid FOREIGN KEY (aid) REFERENCES actor(aid);
#角色表和中间表
ALTER TABLE a_r ADD CONSTRAINT fk_rid FOREIGN KEY (rid) REFERENCES role(rid);

效果图

四、三大泛式

1.三大范式：用来约束数据库的，保证数据的完整性，以及正确性
2.三大范式：
	A.第一大范式:表中的每一列都是最小的原子单位,不能在进行拆分
	B.第二大范式：每一张表里面只能描述一种事物
	C.在遵从前面的两个范式的基础上，不能出现局部依赖，只能与主键建立依赖关系

第一大范式：

第二大范式：

第三大范式：

五、多表查询

5.1 素材

创建表

#创建学生表
CREATE TABLE student(
	pid INT(11) PRIMARY KEY AUTO_INCREMENT,
	pname VARCHAR(20),
	page INT(5),
	psex CHAR(2) ,
	sid int(11)
	
);
#创建学科表
CREATE TABLE `subject`(
	sid INT(11) PRIMARY KEY AUTO_INCREMENT,
	sname VARCHAR(20),
	sGPA INT(2)
);

添加数据：

#学生表插入数据
INSERT INTO student(pname,page,psex,sid) 
VALUES("罗志祥",25,'男',1),("林伟翔",22,'男',1),
("林伟翔",22,'男',2),("刘佳",15,'女',2),
("薛之谦",25,'男',2),("徐至琦",25,'女',3),
("张三",18,'男',3),("欧巴",35,'女',4);

#学科表插入数据
INSERT INTO `subject`(sname,sGPA ) VALUES("java",4),("c语言",3),("瑜伽",2),("网球",2);

5.2 表结构

5.3 非等值查询

1.语法：select * from 表1，表2
2.案例：
	#查询学生以及学生的学科
	select * from 表1，表2
	SELECT stu.*,sub.*  FROM student stu,`subject` sub;
3.问题：
	出现了笛卡尔乘积的现象

5.4 连接查询

5.4.1 显示内连接

 1.关键字：inner join 这个来进行连接 这就是显示内连接
 2.语法：select * from 左表 inner join 右表 on 关联条件
         #查询所有学生信息和学科名字
         SELECT stu.*,sub.sname,sub.sGPA FROM student stu INNER JOIN `subject` sub  ON stu.sid=sub.sid;

取值范围：

5.4.2 隐式外连接

 1.关键字：inner join 来进行查询连接
 2.语法：select * from 左表 inner join 右表  where 关联条件
 3.案例：
     #查询所有学生信息和学科名字（隐式外连接）
     SELECT * FROM student stu INNER JOIN `subject` sub WHERE stu.sid= sub.sid;

5.5 左连接

 1.语法：select * from 左表 left join 右表 on 匹配规则
 2.匹配规则：以左表为基准，右表一一匹配，如果匹配成功，两行进行显示，匹配没有成功，左表依然显示，右表设置           为null
 3.案例：
     #不管这么学科是否有这个学生，查询所有的学科的信息
     SELECT sub.*,stu.* FROM `subject` sub LEFT JOIN student stu ON sub.sid = stu.sid;

5.6 右外连接

 语法：select * from 左表 right join 右表 on 匹配条件
 2.匹配规则：以右表为基准，左表一一匹配，如果匹配成功，两行进行显示，匹配没有成功，右表依然显示，左表设置           为null
 3.案例：
     #不管是否有这门学科，查询所有的学生的信息
     SELECT * FROM `subject` sub RIGHT JOIN student stu ON sub.sid=stu.sid;

取值部分

5.7 子查询

 1.概念：一个完成的查询语句嵌套另外的一个查询语句
 2.两中体现：
     A.一个查询的结果当作另外一个查询语句的查询条件0
     B.一个查询语句的结果当作当前的查询语句的列来使用
 3.第一种体现：
     #查询java学课下的所有的学生
     A.查询所有的学生   select * from student  
     B.查询java学科      select * from ·subject· sub where sub.sname=“java”；
     select * from student stu where stu.sid in(select sub.sid from  `subject` sub where         sub.sname=“java”)
 4.第二种体现：
     #查询所有的学生以及选的学科
     A.查询所有的学生信息     select * from student  
     B.查询学科              select * from `subject` 
     C.两个的关联：sid
     SELECT stu.*,(SELECT sub.sname FROM `subject` sub WHERE stu.sid=sub.sid) as sname FROM              student stu;

六、多表的查询

6.1 备份与还原

6.1.1 第一种方法使用dos窗口来进行备份与还原

 1.备份：
     指令：mysqldump -u用户名 -p密码  数据库的库名  >  备份存放的地址
 2.还原
     指令：mysql -u用户名 -p密码 数据库名 <  备份文件存放的地址

备份：

还原：

6.1.2第二种方法可视化工具

备份：

还原：

6.2 素材

6.3 表结构

6.4 多表查询

 #查询参加了考试的同学信息（学号、学生姓名、科目编号、分数）
  SELECT stu.StudentNo,stu.StudentName,r.SubjectNo,r.StudentResult
   FROM student stu 
   INNER JOIN result r
    ON stu.StudentNo=r.StudentNo;
    
 #查询参加了考试的同学信息（学号、学生姓名、科目名称、分数）
 SELECT stu.StudentNo,stu.StudentName,sub.SubjectName,r.StudentResult 
 FROM student stu 
 INNER JOIN result r ON stu.StudentNo=r.StudentNo 
 INNER JOIN `subject` sub ON r.SubjectNo=sub.SubjectNo;
 
 
 #查询出了所有同学，不考试的也查出来(应该使用左连接) 关键字 left join on 匹配规则
 SELECT stu.StudentNo,stu.StudentName,r.StudentResult 
 FROM student stu 
 LEFT JOIN result r ON stu.StudentNo=r.StudentNo;
 
 #查一下缺考的同学（没有成绩）
 SELECT stu.StudentNo,stu.StudentName,r.StudentResult 
 FROM student stu 
 LEFT JOIN result r ON stu.StudentNo=r.StudentNo 
 WHERE r.StudentResult IS NULL;
 
 #思考题:查询参加了考试的同学信息（学号、学生姓名、科目名、分数）
 SELECT stu.StudentNo,stu.StudentName,sub.SubjectName,r.StudentResult 
 FROM student stu 
 INNER JOIN result r ON stu.StudentNo=r.StudentNo
 INNER JOIN `subject` sub ON r.SubjectNo= sub.SubjectNo;
 
 # 查询《数据库结构-1》的所有考试结果，并按成绩由高到低排列(升序asc 降序desc)
 SELECT sub.SubjectName,r.StudentResult  
 FROM result r 
 INNER JOIN `subject` sub ON r.SubjectNo=sub.SubjectNo 
 WHERE sub.SubjectName="数据库结构-1"
 ORDER BY r.StudentResult DESC;
 
 #查询所有课程《数据库结构-2》的考试成绩，并按照由高到低的显示，同时把该成绩对应的学生的学号、姓名打印出来
 SELECT stu.StudentNo,stu.StudentName,r.StudentResult
  FROM student stu 
  INNER JOIN result r ON stu.StudentNo=r.StudentNo
  INNER JOIN `subject` sub ON r.SubjectNo =sub.SubjectNo
  WHERE sub.SubjectName="数据库结构-2"
  ORDER BY r.StudentResult DESC;
 
  #查询课程为《高等数学-2》且分数不小于80分的学生的学号和姓名
  SELECT stu.StudentNo,stu.StudentName,r.StudentResult
  FROM student stu 
  INNER JOIN result r ON stu.StudentNo=r.StudentNo
  INNER JOIN `subject` sub ON r.SubjectNo=sub.SubjectNo
  WHERE sub.SubjectName="高等数学-2" AND r.StudentResult >=80; 
  
 #案例 ：查询《C语言-1》的前5名学生成绩信息(需要进行排序：关键字：order by 升序(asc) 降序(desc))
 SELECT stu.StudentNo,stu.StudentName,r.StudentResult
 FROM student stu 
 INNER JOIN result r ON stu.StudentNo = r.StudentNo
 INNER JOIN `subject` sub ON r.SubjectNo = sub.SubjectNo
 WHERE sub.SubjectName="C语言-1"
 ORDER BY r.StudentResult DESC
 LIMIT 0,5;
 
 #查询课程为《JAVA第一学年》成绩前10名分数大于80的学生信息（学号，姓名，课程名，分数）
  SELECT stu.StudentNo,stu.StudentName,sub.SubjectName,r.StudentResult
  FROM student stu 
  INNER JOIN result r ON stu.StudentNo=r.StudentNo
  INNER JOIN `subject` sub ON r.SubjectNo = sub.SubjectNo
  WHERE sub.SubjectName="JAVA第一学年" AND r.StudentResult>80
  ORDER BY r.StudentResult DESC 
  LIMIT 0,10;
  
 #按照不同的课程，分别算出其平均分、最高分和最低分，对于低于60分平均分的不予显示(使用左连接 需要进行分组，之后进行过滤)
 SELECT sub.SubjectName AS "课程名称",
 ROUND(AVG(r.StudentResult),2) AS "平均分",
 MAX(r.StudentResult) AS "最高分",
 MIN(r.StudentResult) AS "最低分" 
 FROM `subject` sub 
 LEFT JOIN result r ON sub.SubjectNo=r.SubjectNo
 GROUP BY sub.SubjectName
 HAVING AVG(r.StudentResult)>=60;

一个胖小白

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
day09

一、五大约束1.1 主键约束特点：非空唯一一张表只能有一个主键1.2 唯一约束1.关键字：unique2.特点：唯一不重复可以为空3.主键约束与唯一约束的区别 A.主键约束一张表只能由一个唯一约束一张表可以有多个 B.主键约束不能为空唯一约束可以为空 C.唯一约束可以设置为联合唯一约束（多个列组成唯一约束）4.唯一约束创建的两种方法： A.创建表时候创建唯一约束 B.创建表之后添加约...
复制链接

扫一扫