一、基本语法
-- 显示所有数据库 show databases; -- 创建数据库 CREATE DATABASE test; -- 切换数据库 use test; -- 显示数据库中的所有表 show tables; -- 创建数据表 CREATE TABLE pet ( name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE ); -- 查看数据表结构 -- describe pet; desc pet; -- 查询表 SELECT * from pet; -- 插入数据 INSERT INTO pet VALUES ('puffball', 'Diane', 'hamster', 'f', '1990-03-30', NULL); -- 修改数据 UPDATE pet SET name = 'squirrel' where owner = 'Diane'; -- 删除数据 DELETE FROM pet where name = 'squirrel'; -- 删除表 DROP TABLE myorder;
二、数据库大三大设计范式
1NF
不可分割性，只要字段值还可以继续拆分，就不满足第一范式。
不可分割的意思就按字面理解就是最小单位，不能再分成更小单位了。
字段只能是一个值，不能被拆分成多个字段，否则的话，它就是可分割的，就不符合一范式。
2NF
第二范式就是要有主键，要求其他字段都依赖于主键。
为什么要有主键？没有主键就没有唯一性，没有唯一性在集合中就定位不到这行记录，所以要主键。
在满足第一范式的前提下，主键外的每一列都必须完全依赖于主键。如果出现不完全依赖，只可能发生在联合主键的情况下。
3NF
在满足第二范式的前提下，除了主键列之外，其他列之间不能有传递依赖关系，即“消除冗余”。
消除冗余，就是各种信息只在一个地方存储，不出现在多张表中。
范式总结：范式，其实是用来学习参考的，设计的时候根据情况，未必一定要遵守，要灵活结合业务实际情况决定。
三、查询练习
准备数据
-- 创建数据库 CREATE DATABASE select_test; -- 切换数据库 USE select_test; -- 创建学生表 CREATE TABLE student ( no VARCHAR(20) PRIMARY KEY, name VARCHAR(20) NOT NULL, sex VARCHAR(10) NOT NULL, birthday DATE, -- 生日 class VARCHAR(20) -- 所在班级 ); -- 创建教师表 CREATE TABLE teacher ( no VARCHAR(20) PRIMARY KEY, name VARCHAR(20) NOT NULL, sex VARCHAR(10) NOT NULL, birthday DATE, profession VARCHAR(20) NOT NULL, -- 职称 department VARCHAR(20) NOT NULL -- 部门 ); -- 创建课程表 CREATE TABLE course ( no VARCHAR(20) PRIMARY KEY, name VARCHAR(20) NOT NULL, t_no VARCHAR(20) NOT NULL, -- 教师编号 -- 表示该 tno 来自于 teacher 表中的 no 字段值 FOREIGN KEY(t_no) REFERENCES teacher(no) ); -- 成绩表 CREATE TABLE score ( s_no VARCHAR(20) NOT NULL, -- 学生编号 c_no VARCHAR(20) NOT NULL, -- 课程号 degree DECIMAL, -- 成绩 -- 表示该 s_no, c_no 分别来自于 student, course 表中的 no 字段值 FOREIGN KEY(s_no) REFERENCES student(no), FOREIGN KEY(c_no) REFERENCES course(no), -- 设置 s_no, c_no 为联合主键 PRIMARY KEY(s_no, c_no) ); -- 查看所有表 SHOW TABLES; -- 添加学生表数据 INSERT INTO student VALUES('101', '曾华', '男', '1977-09-01', '95033'); INSERT INTO student VALUES('102', '匡明', '男', '1975-10-02', '95031'); INSERT INTO student VALUES('103', '王丽', '女', '1976-01-23', '95033'); INSERT INTO student VALUES('104', '李军', '男', '1976-02-20', '95033'); INSERT INTO student VALUES('105', '王芳', '女', '1975-02-10', '95031'); INSERT INTO student VALUES('106', '陆军', '男', '1974-06-03', '95031'); INSERT INTO student VALUES('107', '王尼玛', '男', '1976-02-20', '95033'); INSERT INTO student VALUES('108', '张全蛋', '男', '1975-02-10', '95031'); INSERT INTO student VALUES('109', '赵铁柱', '男', '1974-06-03', '95031'); -- 添加教师表数据 INSERT INTO teacher VALUES('804', '李诚', '男', '1958-12-02', '副教授', '计算机系'); INSERT INTO teacher VALUES('856', '张旭', '男', '1969-03-12', '讲师', '电子工程系'); INSERT INTO teacher VALUES('825', '王萍', '女', '1972-05-05', '助教', '计算机系'); INSERT INTO teacher VALUES('831', '刘冰', '女', '1977-08-14', '助教', '电子工程系'); -- 添加课程表数据 INSERT INTO course VALUES('3-105', '计算机导论', '825'); INSERT INTO course VALUES('3-245', '操作系统', '804'); INSERT INTO course VALUES('6-166', '数字电路', '856'); INSERT INTO course VALUES('9-888', '高等数学', '831'); -- 添加添加成绩表数据 INSERT INTO score VALUES('103', '3-105', '92'); INSERT INTO score VALUES('103', '3-245', '86'); INSERT INTO score VALUES('103', '6-166', '85'); INSERT INTO score VALUES('105', '3-105', '88'); INSERT INTO score VALUES('105', '3-245', '75'); INSERT INTO score VALUES('105', '6-166', '79'); INSERT INTO score VALUES('109', '3-105', '76'); INSERT INTO score VALUES('109', '3-245', '68'); INSERT INTO score VALUES('109', '6-166', '81'); -- 查看表结构 SELECT * FROM course; SELECT * FROM score; SELECT * FROM student; SELECT * FROM teacher;
基础查询
-- 查询 student 表的所有行 SELECT * FROM student; -- 查询 student 表中的 name、sex 和 class 字段的所有行 SELECT name, sex, class FROM student; -- 查询 teacher 表中不重复的 department 列 -- department: 去重查询 SELECT DISTINCT department FROM teacher; -- 查询 score 表中成绩在60-80之间的所有行（区间查询和运算符查询） -- BETWEEN xx AND xx: 查询区间, AND 表示 "并且" SELECT * FROM score WHERE degree BETWEEN 60 AND 80; SELECT * FROM score WHERE degree > 60 AND degree < 80; -- 查询 score 表中成绩为 85, 86 或 88 的行 -- IN: 查询规定中的多个值 SELECT * FROM score WHERE degree IN (85, 86, 88); -- 查询 student 表中 '95031' 班或性别为 '女' 的所有行 -- or: 表示或者关系 SELECT * FROM student WHERE class = '95031' or sex = '女'; -- 以 class 降序的方式查询 student 表的所有行 -- DESC: 降序，从高到低 -- ASC（默认）: 升序，从低到高 SELECT * FROM student ORDER BY class DESC; SELECT * FROM student ORDER BY class ASC; -- 以 c_no 升序、degree 降序查询 score 表的所有行 SELECT * FROM score ORDER BY c_no ASC, degree DESC; -- 查询 "95031" 班的学生人数 -- COUNT: 统计 SELECT COUNT(*) FROM student WHERE class = '95031'; -- 查询 score 表中的最高分的学生学号和课程编号（子查询或排序查询）。 -- (SELECT MAX(degree) FROM score): 子查询，算出最高分 SELECT s_no, c_no FROM score WHERE degree = (SELECT MAX(degree) FROM score); -- 排序查询 -- LIMIT r, n: 表示从第r行开始，查询n条数据 SELECT s_no, c_no, degree FROM score ORDER BY degree DESC LIMIT 0, 1; -- LIMIT n offset r: 表示查询n条数据,从第r行开始 SELECT s_no, c_no, degree FROM score ORDER BY degree DESC LIMIT 1 offset 0;
分组计算平均成绩
查询每门课的平均成绩
-- AVG: 平均值 SELECT AVG(degree) FROM score WHERE c_no = '3-105'; SELECT AVG(degree) FROM score WHERE c_no = '3-245'; SELECT AVG(degree) FROM score WHERE c_no = '6-166'; -- GROUP BY: 分组查询 SELECT c_no, AVG(degree) FROM score GROUP BY c_no;
分组条件与模糊查询
查询score表中至少有 2 名学生选修，并以 3 开头的课程的平均分数
分析表发现，至少有 2 名学生选修的课程是3-105、3-245、6-166，以 3 开头的课程是3-105、3-245。也就是说，我们要查询所有3-105和3-245的degree平均分。
-- 首先把 c_no, AVG(degree) 通过分组查询出来 SELECT c_no, AVG(degree) FROM score GROUP BY c_no +-------+-------------+ | c_no | AVG(degree) | +-------+-------------+ | 3-105 | 85.3333 | | 3-245 | 76.3333 | | 6-166 | 81.6667 | +-------+-------------+ -- 再查询出至少有 2 名学生选修的课程 -- HAVING: 表示持有 HAVING COUNT(c_no) >= 2 -- 并且是以 3 开头的课程 -- LIKE 表示模糊查询，"%" 是一个通配符，匹配 "3" 后面的任意字符。 AND c_no LIKE '3%'; -- 把前面的SQL语句拼接起来， -- 后面加上一个 COUNT(*)，表示将每个分组的个数也查询出来。 SELECT c_no, AVG(degree), COUNT(*) FROM score GROUP BY c_no HAVING COUNT(c_no) >= 2 AND c_no LIKE '3%'; +-------+-------------+----------+ | c_no | AVG(degree) | COUNT(*) | +-------+-------------+----------+ | 3-105 | 85.3333 | 3 | | 3-245 | 76.3333 | 3 | +-------+-------------+----------+
多表查询 - 1
查询所有学生的name，以及该学生在score表中对应的c_no和degree
SELECT no, name FROM student; +-----+-----------+ | no | name | +-----+-----------+ | 101 | 曾华 | | 102 | 匡明 | | 103 | 王丽 | | 104 | 李军 | | 105 | 王芳 | | 106 | 陆军 | | 107 | 王尼玛 | | 108 | 张全蛋 | | 109 | 赵铁柱 | +-----+-----------+ SELECT s_no, c_no, degree FROM score; +------+-------+--------+ | s_no | c_no | degree | +------+-------+--------+ | 103 | 3-105 | 92 | | 103 | 3-245 | 86 | | 103 | 6-166 | 85 | | 105 | 3-105 | 88 | | 105 | 3-245 | 75 | | 105 | 6-166 | 79 | | 109 | 3-105 | 76 | | 109 | 3-245 | 68 | | 109 | 6-166 | 81 | +------+-------+--------+
通过分析可以发现，只要把score表中的s_no字段值替换成student表中对应的name字段值就可以了，如何做呢？
-- FROM...: 表示从 student, score 表中查询 -- WHERE 的条件表示为，只有在 student.no 和 score.s_no 相等时才显示出来。 SELECT name, c_no, degree FROM student, score WHERE student.no = score.s_no; +-----------+-------+--------+ | name | c_no | degree | +-----------+-------+--------+ | 王丽 | 3-105 | 92 | | 王丽 | 3-245 | 86 | | 王丽 | 6-166 | 85 | | 王芳 | 3-105 | 88 | | 王芳 | 3-245 | 75 | | 王芳 | 6-166 | 79 | | 赵铁柱 | 3-105 | 76 | | 赵铁柱 | 3-245 | 68 | | 赵铁柱 | 6-166 | 81 | +-----------+-------+--------+
多表查询 - 2
查询所有学生的 no 、课程名称 ( course 表中的 name ) 和成绩 ( score 表中的 degree ) 列。
只有score关联学生的no，因此只要查询score表，就能找出所有和学生相关的no和degree：
SELECT s_no, c_no, degree FROM score; +------+-------+--------+ | s_no | c_no | degree | +------+-------+--------+ | 103 | 3-105 | 92 | | 103 | 3-245 | 86 | | 103 | 6-166 | 85 | | 105 | 3-105 | 88 | | 105 | 3-245 | 75 | | 105 | 6-166 | 79 | | 109 | 3-105 | 76 | | 109 | 3-245 | 68 | | 109 | 6-166 | 81 | +------+-------+--------+
然后查询course表：
SELECT no, name FROM course; +-------+-----------------+ | no | name | +-------+-----------------+ | 3-105 | 计算机导论 | | 3-245 | 操作系统 | | 6-166 | 数字电路 | | 9-888 | 高等数学 | +-------+-----------------+
只要把score表中的c_no替换成course表中对应的name字段值就可以了。
-- 增加一个查询字段 name，分别从 score、course 这两个表中查询。 -- as 表示取一个该字段的别名。 SELECT s_no, name as c_name, degree FROM score, course WHERE score.c_no = course.no; +------+-----------------+--------+ | s_no | c_name | degree | +------+-----------------+--------+ | 103 | 计算机导论 | 92 | | 105 | 计算机导论 | 88 | | 109 | 计算机导论 | 76 | | 103 | 操作系统 | 86 | | 105 | 操作系统 | 75 | | 109 | 操作系统 | 68 | | 103 | 数字电路 | 85 | | 105 | 数字电路 | 79 | | 109 | 数字电路 | 81 | +------+-----------------+--------+
