java第十五课 mysql数据库基础学习2

目录

回顾

创建数据库

创建表

建立主外键关系

插入数据

单标查询

1查询表

2分页查询

3模糊查询

4分组查询

多表查询

1等价连接

2内连接

3左连接

创建视图

on和where的区别

---

回顾

1.数据库的创建以及删除  (设置编码)

2.使用数据库，往里面创建多个表格

3.往表格中插入数据

4.多表格中的数据  进行  增删改查

5.where的应用   根据什么条件进行  修改  删除   查询  

6.逻辑词的使用   and  or  not  （between … and…  /  in（））

7.表内约束   primary key  ，unique， check ,not null,  (auto_increment)

8.聚合函数 avg()  sum()  max()  min()  count()

创建数据库

CREATE DATABASE SchoolDB;
USE SchoolDB;

创建表

创建成绩表score，年级表grade，学生表student

CREATE TABLE student(
id INT AUTO_INCREMENT PRIMARY KEY,
stuNo VARCHAR(30) NOT NULL UNIQUE,
stuPass VARCHAR(30) DEFAULT '1234' NOT NULL,
gradeId INT
);

CREATE TABLE score(
id INT AUTO_INCREMENT PRIMARY KEY, -- 成绩表编号
cName VARCHAR(20) NOT NULL,-- 课程名称
score DOUBLE ,-- 分数
stuId INT -- 学生编号
);

CREATE TABLE grade(
id INT AUTO_INCREMENT PRIMARY KEY,
gradeName VARCHAR(20)
);

建立主外键关系

有主外键的表：

• 添加数据--先添加主键表数据，再添加外键表数据
• 删除数据--先删除外检表数据，再删除主键表数据

-- 建立主外键关系  grade（主键表）--student（外检表）
ALTER TABLE student  -- 外键表名称
ADD CONSTRAINT stu_Fk1  -- 外键约束名称
FOREIGN KEY(gradeId)  -- 外键表中的列
REFERENCES grade(id);  -- 对应主键表的主键

-- 建立  student---score 主外键关系
ALTER TABLE score 
ADD CONSTRAINT score_Fk1
FOREIGN KEY(stuId)
REFERENCES student(id);

插入数据

-- 1.插入年级表
INSERT INTO grade(gradeName) VALUES('大一');
INSERT INTO grade(gradeName) VALUES('大二');
INSERT INTO grade(gradeName) VALUES('大三');
INSERT INTO grade(gradeName) VALUES('大四');
SELECT* FROM grade;

-- 2.学生表插入数据
INSERT INTO student(stuNo,gradeId)VALUES('jack',4);
INSERT INTO student(stuNo,gradeId)VALUES('frank',4);
INSERT INTO student(stuNo,gradeId)VALUES('mary',3);
INSERT INTO student(stuNo,gradeId)VALUES('rouse',3);
INSERT INTO student(stuNo,gradeId)VALUES('ooook',3);
INSERT INTO student(stuNo,gradeId)VALUES('koooo',1);

-- 3.插入成绩表
INSERT INTO score(stuId,cName,score)VALUES(1,'java程序设计',89);
INSERT INTO score(stuId,cName,score)VALUES(1,'mysql数据库设计',89);
INSERT INTO score(stuId,cName,score)VALUES(1,'html网页设计',89);
INSERT INTO score(stuId,cName,score)VALUES(2,'java程序设计',89);
INSERT INTO score(stuId,cName,score)VALUES(3,'java程序设计',89);
INSERT INTO score(stuId,cName,score)VALUES(3,'c#程序设计',89);
INSERT INTO score(stuId,cName,score)VALUES(3,'数据结构',89);
INSERT INTO score(stuId,cName,score)VALUES(4,'数据结构',89);
INSERT INTO score(stuId,cName,score)VALUES(5,'数据结构',89);
INSERT INTO score(stuId,cName,score)VALUES(6,'数据结构',89);

单标查询

1查询表

SELECT * FROM score;

SELECT * FROM student;

2分页查询

查询得到的数据分页显示

select * from table limit m,n

• m：表示从表中的第m条数据开始
• n：表示取n条数据

-- 1.分页查询语句(网页版本需要用到)
-- 一页显示5个数据     0(起始值)    5 :截取的长度、
-- 分页的核心算法在第一个参数 (nowPage-1)*5
SELECT * FROM score LIMIT 0,5;

3模糊查询

like

-- 2.模糊查询
-- %：代表任意长度任意字符   _:长度为1的任意字符
-- 在成绩表中查询课程名称含有 设计  字样的 数据
SELECT * FROM score WHERE cName LIKE '%设计%';

列：

SELECT * FROM Persons
WHERE City LIKE 'N%'

查询结果：

4分组查询

• group by 分组：就是选取表示的某一列的值 此列中的值回去掉重复值
• having： 对分组得结果进行操作
• order by： 对分组的结果进行排序

要求计算学生的  平均分  总分 

SELECT stuId,SUM(score),AVG(score)
FROM score 
WHERE stuId<=5
GROUP BY stuId       -- 根据什么分组  
HAVING  AVG(score)>=60 -- 对于分组得到的结果进行筛选
ORDER BY AVG(score) DESC;
-- 总结：group by 只能跟聚合函数使用

多表查询

1等价连接

SELECT student.`id`,student.`stuNo`,
score.`cName`,score.`score`
FROM student,score
WHERE score.`stuId`=student.`id`;  -- 根据表之间的那些字段建立联系的


SELECT student.`id`,student.`stuNo`,
score.`cName`,score.`score`,grade.`gradeName`
FROM student,score,grade
WHERE student.`id`=score.`stuId`  AND 
student.`gradeId`=grade.`id`;

2内连接

inner join：内连接

-- 2.内连接  （内连接的原理与等价连接的原理 以及效果完全等价）
SELECT student.`id`,student.`stuNo`,
score.`cName`,score.`score`,grade.`gradeName`
FROM score
INNER JOIN student
ON score.`stuId`=student.`id`-- 表连接的条件
INNER JOIN grade
ON grade.id=student.`gradeId`;

3左连接

-- 3.左连接  以左表作为主表进行一一匹配  匹配成功返回数据
-- 匹陪失败 返回null
SELECT student.`id`,student.`stuNo`,
score.`cName`,score.`score`
FROM score  -- 左边 左表
RIGHT JOIN  student  -- 右表   右链接将右边的表作为主表
ON student.`id`=score.`stuId`
WHERE score.`score` IS NULL;

创建视图

视图：用来保存虚拟表中的数据，视图的查询和表格相同

CREATE VIEW view_Student01
AS 
SELECT student.`id`,student.`stuNo`,
score.`cName`,score.`score`
FROM score  -- 左边 左表
RIGHT JOIN  student  -- 右表   右链接将右边的表作为主表
ON student.`id`=score.`stuId`

on和where的区别

在使用 LEFT JOIN 时，ON 和 WHERE 过滤条件的区别如下：

ON 条件是在生成临时表时使用的条件，它不管 ON 中的条件是否为真，都会返回左边表中的记录；
WHERE 条件是在临时表已经生成后，对临时表进行的过滤条件。因为此时已经没有 LEFT JOIN 的含义（必须返回左侧表的记录）了，所以如果 WHERE 条件不为真的记录就会被过滤掉。