mysql-DQL

1.外键的补充

外键：连接多张表的关键列，用于保证数据一致性。
ALTER TABLE 从表 ADD CONSTRAINT 外键名 FOREIGN KEY(外键列名) REFERENCES 主表(外键对应的列名);
逻辑外键(推) 物理外键

DML语句

2.增加

INSERT INTO 表名
(字段名列表) VALUES(值列表);

增加多条数据

INSERT INTO 表名
(字段名列表) VALUES(值列表1),(值列表2),()…

3.修改

UPDATE
表名
SET
要修改的字段/列 = 修改的值,…
[WHERE 条件] # 如果不加where是进行全表更新

WHERE 后面可以接各种运算符 > < >= <= !=/<> = BETWEEN…AND… and or not

4.删除

DELETE FROM 表名 [WHERE 条件]

TRUNCATE 和 DELETE

DELETE 和 TRUNCATE都可以清空全表内容，但是Delete可以添加where条件。
DELETE删除会保留自增序列，但是TRUNCATE会清空。
DELETE不会影响事务，而TRUNCATE会结束事务。

– 准备基础数据

表创建

科目表

CREATE TABLE subject(
subjectNo INT PRIMARY KEY auto_increment COMMENT ‘科目编号’,
subjectName VARCHAR(50) COMMENT ‘科目名称’,
classHour INT COMMENT ‘课时’,
gradeId INT COMMENT ‘年级编号’
);

年级表

CREATE TABLE grade(
gradeId INT PRIMARY KEY auto_increment COMMENT ‘年级编号’,
gradeName VARCHAR(50) COMMENT ‘年级名称’
);

成绩表

CREATE TABLE result(
studentNo INT COMMENT ‘学生编号’,
subjectNo INT COMMENT ‘科目编号’,
examDate Datetime COMMENT ‘考试时间’,
studentResult DOUBLE COMMENT ‘考试成绩’
);

添加

INSERT INTO student
(studentNo,loginPwd,studentName,sex,gradeId,identityCard)
VALUES(1000,‘111111’,‘郭靖’,‘男’,1,‘23333333’),
(1001,‘123456’,‘李文才’,‘女’,2,‘32222222’),
(1003,‘222222’,‘贾斯文’,‘男’,3,‘33333333’);

科目

INSERT INTO subject
(subjectName,classHour,gradeId)
VALUES(‘高等数学’,110,1),
(‘C语言’,100,2),
(‘大学物理’,130,3);

年级

INSERT INTO grade
(gradeName)
VALUES(‘大一’),
(‘大二’),
(‘大三’);

成绩

INSERT INTO myschool.result
(studentNo,subjectNo,examDate,studentResult)
values(1000,1,‘2019-03-22’,94),
(1001,2,‘2019-03-20’,85),
(1000,3,‘2019-03-24’,60);

– DQL 数据查询语言 SELECT
– 1. 掌握MySQL的查询语句(基本完整语法)
/*
SELECT [ALL查询全部 | DISTINCT去重]

决定查询哪些列信息

{ 全列* | table.* | [ table.field1 [ as alias1] [, table.field2 [as alias2]][, …]] }

决定从哪些表中查询数据

FROM table_name [ as table_ alias ]
[ left|out|inner join table_name2 ] # 联合/连接查询
[ WHERE … ] # 指定结果需满足的条件
[ GROUP BY …] # 指定结果按照哪几个字段来分组
[ HAVING …] # 过滤分组的记录必须满足的次要条件
[ ORDER BY… ] #指定查询记录按一个或者多个条件排序
[ LIMIT { [ offset,] row_count | row_count OFFSET offset }] ; #指定查询的记录从哪条至哪条
*/

查询所有学生的所有信息

SELECT
* # 决定查询哪些列
FROM
student; # 不写where 表示决定查询所有学生

为老师查询学生们的通讯录 姓名 性别 手机号 邮箱 地址

SELECT
student.studentName,student.sex,address,phone,email
FROM
student;

要求查询上述信息 并将信息以中文展示

SELECT
studentName AS ‘学生姓名’, sex AS ‘性别’,address AS 地址,
phone ‘手机号’,email ‘邮箱’
FROM
student;

查询所有学生的性别 ALL默认表示全部查询

SELECT
ALL stu.sex
FROM
student AS stu;

DISTINCT 会将所查询的列信息进行筛选 去除重复的

SELECT
DISTINCT stu.sex
FROM
student AS stu;

查询课时在100-120之间的科目名称

SELECT
subjectName,classHour
FROM
subject
WHERE
#classHour >= 100 AND classHour <= 120;
classHour BETWEEN 100 AND 120;

查询手机号为null的学生信息 如果查询null值/非null 必须用is 或 is not

SELECT
studentName
FROM
student
WHERE
#phone = null;
phone IS NOT NULL;
– 2.掌握模糊查询

LIKE

查询姓贾的学生姓名和性别

SELECT
studentName,sex
FROM
student
WHERE
studentName LIKE ‘贾%’;

查询姓贾的但是是两个字姓名的学生信息

SELECT
*
FROM
student
WHERE
studentName LIKE ‘贾_’;

% 匹配0个或任意一个/多个字符

_ 匹配任意一个字符

查询地址包含西城信息的

SELECT
*
FROM
student
WHERE
address LIKE ‘%西城%’;

in范围查询

查询学号为1001和1003的学生信息

SELECT
*
FROM
student
WHERE
studentNo = 1001 OR studentNo = 1003;

查询学号为1001和1003的学生信息

SELECT
*
FROM
student
WHERE
studentNo IN(1001,1003);

– 3.理解连接查询的原理

当查询的数据需要来源多张表时，需要通过外键和连接运算符来查询信息。

连接查询分类

1.内连接(INNER JOIN) 显式内连接 隐式内连接 自连接

2.外连接(OUTER JOIN) 左外连接(LEFT [OUTER] JOIN) 右外连接(RIGHT [OUTER] JOIN)

– 4. 掌握内连接查询

显示内连接

查询科目名称和对应年级名称

SELECT
subjectName,gradeName
FROM
subject
INNER JOIN grade
ON subject.gradeId = grade.gradeId

用别名优化

SELECT
sb.subjectName,g.gradeName
FROM
subject AS sb
INNER JOIN grade AS g
ON sb.gradeId = g.gradeId

三张表联查

查询学生的姓名、科目名称、成绩

SELECT
stu.studentName,sub.subjectName,r.studentResult
FROM
result r
INNER JOIN student stu
ON r.studentNo = stu.studentNo
INNER JOIN subject sub
ON r.subjectNo = sub.subjectNo;

(常用)隐式内连接实现上述功能 可理解为是交集 如果有不存在的外键信息 则无法查询到

SELECT
stu.studentName,sub.subjectName,r.studentResult
FROM
student stu,subject sub,result r
WHERE
stu.studentNo = r.studentNo AND sub.subjectNo = r.subjectNo;

– 5.掌握外连接查询(左外连接)

左外连接(LEFT [OUTER] JOIN)

以左表为主 左边的表信息全部查询出来 右边的表信息进行匹配对应 如果没有相对应的会以Null显示

SELECT
sb.subjectName,g.gradeName
FROM
subject AS sb
LEFT JOIN grade AS g
ON sb.gradeId = g.gradeId

右外连接(RIGHT [OUTER] JOIN)

SELECT
sb.subjectName,g.gradeName
FROM
subject AS sb
RIGHT JOIN grade AS g
ON sb.gradeId = g.gradeId

– 6.理解自连接

查询游戏名称 并 将其分类名称查询出来

SELECT
c1.categoryName AS 游戏名称,c2.categoryName 分类名称
FROM
category c1,category c2
WHERE
c1.pid = c2.id
#AND c1.pid IS NOT NULL;