MySQL数据库
文章目录
1. 数据准备
1.1创建表并向表中插入数据
/*
创建学生表students
*/
CREATE TABLE students(
studentNo VARCHAR(10) PRIMARY KEY,
`name` VARCHAR(10),
sex VARCHAR(1),
hometown VARCHAR(20),
age TINYINT UNSIGNED,
class VARCHAR(10),
card VARCHAR(20)
);
/*
向学生表students中插入数据
*/
INSERT INTO students VALUES
('001', '王昭君', '女', '北京', '30', '1班', '110101199003157654'),
('002', '诸葛亮', '男', '上海', '29', '2班', '310102199104262354'),
('003', '张飞', '男', '南京', '30', '3班', '320102199003047654'),
('004', '白起', '男', '安徽', '35', '4班', '340202198605177654'),
('005', '大乔', '女', '天津', '28', '3班', '120101199204067654'),
('006', '孙尚香', '女', '河北', '25', '1班', '130502199506137654'),
('007', '百里玄策', '女', '山西', '39', '2班', '140102198107277654'),
('008', '小乔', '女', '河南', '25', '3班', 'null'),
('009', '百里守约', '男', '湖南', '31', '1班', ''),
('010', '妲己', '女', '广东', '24', '2班', '440701199607147654'),
('011', '李白', '男', '北京', '30', '4班', '110202199005017754'),
('012', '孙膑', '男', '新疆', '36', '3班', '650102198401297655');
/*
创建成绩表
*/
DROP TABLE IF EXISTS scores;
CREATE TABLE scores (
id INT PRIMARY KEY,
courseNo VARCHAR(10),
studentNo VARCHAR(10),
score INT
);
SELECT * FROM students;
SELECT * FROM scores;
insert into scores VALUES
(1, 1, 1, 90),
(2, 1, 2, 75),
(3, 2, 2, 98),
(4, 3, 1, 86),
(5, 3, 3, 80),
(6, 4, 4, 79),
(7, 5, 5, 96),
(8, 6, 6, 80)
;
DROP TABLE if EXISTS courses;
CREATE TABLE courses(
courseNo VARCHAR(10),
coursename VARCHAR(10)
);
INSERT INTO courses VALUES
(1, '数据库'),
(2, 'html'),
(3, 'linux'),
(4, '系统测试'),
(5, '单元测试'),
(6, '测试过程'),
(7, 'python');
2. 连接查询
2.1 基本概念
- 连接查询是关系数据库中最主要的查询，主要包括内连接、外连接和交叉连接等。通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点，也是它区别于其它类型数据库管理系统的一个标志。 在关系数据库管理系统中，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当检索数据时，通过连接操作查询出存放在多个表中的不同实体的信息。连接操作给用户带来很大的灵活性，他们可以在任何时候增加新的数据类型。为不同实体创建新的表，然后通过连接进行查询。
2.2 内连接
- 关键字： inner join on
- 语句： select * from a_table a inner join b_table b on a.a_id = b.b_id;
- 说明： 组合两个表中的记录，返回关联字段相符的记录，也就是返回两 个表的交集（阴影）部分。
2.3 左连接
-
关键字： left join on / left outer join on
-
语句： select * from a_table a left join b_table b on a.a_id = b.b_id;
-
说明： left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。 左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。
2.4 右连接
- 关键字： right join on / right outer join on
- 语句： select * from a_table a right outer join b_table b on a.a_id = b.b_id;
- 说明： right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。
附上一个来源链接： link.
-- 练习题：
-- 查询学生信息和学生成绩，只显示有成绩的学生(查询所有记录)；
SELECT * FROM students stu
INNER JOIN scores sco
ON stu.studentNo = sco.studentNo;
-- 查询学生信息，要求只显示姓名、课程号和成绩(查询指定字段)；
SELECT name, courseNo, score
FROM students stu
INNER JOIN scores sco
ON stu.studentNo = sco.studentNo;
-- 查询学生信息，要求只显示姓名、课程号、成绩，其中表students别名
-- 为stu，表scores别名为sc；
SELECT name, courseNo, score
FROM students stu
INNER JOIN scores sco
ON stu.studentNo = sco.studentNo;
-- 查询成绩表的信息，同时显示成绩对应的课程名称；
SELECT coursename, score
FROM scores
INNER JOIN courses
ON scores.courseNo = courses.courseNo;
-- 查询王昭君的信息，要求只显示姓名、课程号和成绩；
SELECT name, courseNo, score
FROM students
INNER JOIN scores
ON students.studentNo = scores.studentNo
WHERE name = '王昭君';
-- 查询姓名为王昭君，并且成绩小于90的信息，要求只显示姓名、成绩
SELECT `name`, score
FROM students
INNER JOIN scores
ON students.studentNo = scores.studentNo
WHERE name = '王昭君' AND
score < 90;
-- 查询学生信息和成绩以及成绩对应的课程名称；
SELECT students.studentNo, scores.courseNo,
coursename, score
FROM students
INNER JOIN scores
ON students.studentNo = scores.studentNo
INNER JOIN courses
ON scores.courseNo = courses.courseNo;
-- 查询所有学生的linux课程成绩，要求只显示姓名、成绩、课程名；
SELECT `name`, coursename, score
FROM students
INNER JOIN scores
ON students.studentNo = scores.studentNo
INNER JOIN courses
ON scores.courseNo = courses.courseNo
WHERE coursename = 'linux';
-- 查询成绩最高的男生信息，要求显示姓名、课程名、成绩；
SELECT `name`, sex, coursename, score
FROM students
INNER JOIN scores
ON students.studentNo = scores.studentNo
INNER JOIN courses
ON scores.courseNo = courses.courseNo
WHERE sex = '男' ORDER BY score DESC LIMIT 1;
-- 查询所有课程信息，包括没有成绩的课程；
SELECT * FROM courses LEFT JOIN scores ON courses.courseNo = scores.courseNo;
-- 查询所有学生的信息以及成绩，包含没有成绩的学生；
SELECT * FROM students
LEFT JOIN scores
ON students.studentNo = scores.studentNo;
-- 总结SQL三步法
- 第一步：搭框架
select * from 表名;
- 第二部：看条件
select * from 表名 where 字段 = 数值;
- 第三步：返回字段
select 字段1, 字段2 from 表名 where 字段 = 数值;
3. 自关联
3.1 背景思考
3.2 创建区域表areas，向表中插入数据
- 3.2.1 创建areas表：
-- 自关联
DROP TABLE if EXISTS areas;
CREATE TABLE areas(
id INT,
name VARCHAR(20),
pid INT
);
-- 向表中插入数据
INSERT INTO areas VALUES
(130000, '河北省', null),
(130100, '石家庄市', 130000),
(130400, '邯郸市', 130000),
(130600, '保定市', 130000),
(130700, '张家口市', 130000),
(130800, '承德市', 130000),
(440000, '广东省', NULL),
(440100, '广州市', 440000),
(440200, '韶关市', 440000),
(440300, '深圳市', 440000),
(440500, '汕头市', 440000),
(440600, '佛山市', 440000),
(440600, '', 440000);
-- 查询一共有多少个省
SELECT COUNT(*) FROM areas WHERE pid IS NULL;
-- 查询有多少个市
SELECT COUNT(*) FROM areas WHERE pid IS NOT NULL;
- 3.2.2
- 自关联是同一张表做连接查询，
- 自关联下，一定找到同一张表可关联的不同字段；
-- 例如：广东省的所有城市
SELECT * FROM areas a1
INNER JOIN areas a2
ON a1.id = a2.pid
WHERE a1.`name` = '广东省';
4. 子查询
4.1 定义
- 在一个select语句中嵌入另一个select语句，那么被嵌入的select语句称之为子查询。
4.2 主查询
- 外层的第一条select语句为主查询
4.3 主查询和子查询的关系
- 子查询是嵌入到主查询中的；
- 子查询是辅助主查询的，要么充当条件，要么充当数据源；
- 子查询是可以独立存在的语句，是一条完整的select语句；
-- 例如：查询大于平均年龄的学生记录
select avg(age) from student;
select * from students where age > 30.1667;
-- 使用子查询实现
SELECT * FROM students
WHERE age > (SELECT avg(age) FROM students);
-- 例如：查询30岁学生的成绩
-- 1.查询30岁学生的studentNO
select studentNO from students where age = 30;
studentNO
1
3
11
select * from scores where studentNO in (1, 3, 11);
-- 使用子查询实现
select * from scores
where studentNO
in (select studentNO from students where age = 30);
- 大练习：
-- 建表与数据插入
DROP TABLE if EXISTS departments;
/*创建部门表*/
CREATE TABLE departments(
deptid int(10) primary key,/*部门编号 与employees表 deptid关联*/
deptname VARCHAR(20) not null /*部门名称*/
);
/*向部门报表中插入数据*/
INSERT INTO departments
VALUES ('1001', '市场部'),
('1002','测试部'),
('1003','开发部');
DROP TABLE if EXISTS employees;
/*创建员工表*/
CREATE TABLE employees(
empid int PRIMARY KEY, /*职工编号*/
empname VARCHAR(20) not null, /*姓名*/
sex VARCHAR(4) DEFAULT NULL, /*性别*/
deptid int DEFAULT NULL, /*部门编号 与departments表deptid关联*/
jobs VARCHAR(20) DEFAULT null, /* 岗位名称*/
politicalstatus VARCHAR(20) DEFAULT NULL, /*政治面貌*/
leader int DEFAULT null /*上级领导的职工编号*/
);
INSERT INTO employees VALUES
('1', '王昭君', '女', '1003', '开发', '群众', '9'),
('2', '诸葛亮', '男', '1003', '开发', '群众', '9'),
('3', '张飞', '男', '1002', '测试', '团员', '4'),
('4', '白起', '男', '1002', '测试经理', '党员', null),
('5', '大乔', '女', '1002', '测试', '党员', '4'),
('6', '孙尚香', '女', '1001', '市场', '党员', '12'),
('7', '百里玄策', '男', '1001', '市场', '团员', '12'),
('8', '小乔', '女', '1002', '测试', '群众', '4'),
('9', '百里守约', '男', '1003', '开发经理', '党员', null),
('10', '妲己', '女', '1003', '开发', '团员', '9'),
('11', '李白', '男', '1002', '测试', '团园', '4'),
('12', '孙膑', '男', '1001', '市场经理', '党员', null),
('13', '周瑜', '男', '1001', '市场', '群众', '12');
DROP TABLE if EXISTS salary;
CREATE TABLE salary(
sid int PRIMARY KEY,
empid int not null, /*职工编号，与employees表empid关联*/
salary int not null /*工资*/
);
-- 向表中插入数据
INSERT INTO salary VALUES
('1', '7', '2100'),
('2', '6', '2000'),
('3', '12', '5000'),
('4', '9', '1999'),
('5', '10', '1900'),
('6', '1', '3000'),
('7', '2', '5500'),
('8', '5', '2000'),
('9', '3', '1500'),
('10', '8', '4000'),
('11', '11', '2600'),
('12', '4', '5300');
-- 检查表中的数据是否成功插入
SELECT * from departments;
--课堂练习
-- 1.1 列出男职工的总数和女职工的总数
SELECT sex, count(*) FROM employees GROUP BY sex;
-- 1.2 列出非党员职工的总数
SELECT count(*)
FROM employees
WHERE politicalstatus <> '党员';
-- 1.3 列出所有职工工号，姓名以及所在部分名称
SELECT emp.deptid, empname, deptname
FROM employees emp
INNER JOIN departments dep
ON emp.deptid = dep.deptid;
-- 1.4 列出所有职工工号，姓名和对应工资
SELECT emp.deptid, empname, salary
FROM employees emp
INNER JOIN salary sal
ON emp.empid = sal.empid;
-- 1.5 列出领导岗的姓名以及所在的部门
SELECT empname, deptname
FROM employees emp
INNER JOIN departments dep
ON emp.deptid = dep.deptid
WHERE jobs like '%经理';
-- 1.6 列出职工总人数大于4的部门号和总人数
SELECT dep.deptid, count(*)
FROM departments dep
INNER JOIN employees emp
ON dep.deptid = emp.deptid
GROUP BY dep.deptid
HAVING count(*) > 4;
-- 1.7 列出职工总人数大于4的部门号和部门名称
SELECT deptname, count(*)
FROM departments dep
INNER JOIN employees emp
ON dep.deptid = emp.deptid
GROUP BY dep.deptid
HAVING count(*) > 4;
-- 1.8 列出开发部和测试部的职工号，姓名
SELECT dep.deptid, empname, deptname
FROM employees emp
INNER JOIN departments dep
ON emp.deptid = dep.deptid
WHERE deptname = '开发部' OR deptname = '测试部';
-- 1.9 列出市场部所有女职工的姓名和政治面貌
SELECT empname, sex, politicalstatus
from employees emp
INNER JOIN departments dep
ON emp.deptid = dep.deptid
WHERE sex = '女' AND deptname = '市场部';
-- 1.10 显示所有职工姓名和工资，包括没有工资的职工姓名
SELECT empname, salary
FROM employees emp
LEFT JOIN salary sal
ON emp.empid = sal.empid;
-- 1.11 求不姓孙的所有职工工资总和
SELECT sum(salary)
FROM employees emp
LEFT JOIN salary sal
ON emp.empid = sal.empid
WHERE empname not like '孙%';
SELECT sum(salary)
FROM employees emp
LEFT JOIN salary sal
ON emp.empid = sal.empid;
4.4 标量子查询
- 标量子查询 -->> 子查询返回结果只有一行一列；
4.5 列子查询
- 列子查询 -->> 子查询返回一列多行；
4.6 表级子查询
- 表级子查询 -->>子查询返回多行多列；
5. 本章思维导图总结：
扫一扫
1032
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
