1 数据库三大范式

  1. 1NF：不可分割性，只要字段值还可以继续拆分，就不满足第一范式。不可分割性，只要字段值还可以继续拆分，就不满足第一范式。
  2. 2NT：在满足第一范式的前提下，主键外的每一列都必须完全依赖于主键。如果出现不完全依赖，只可能发生在联合主键的情况下。
  3. 3NF：在满足第二范式的前提下，除了主键列之外，其他列之间不能有传递依赖关系，即“消除冗余”，各种信息只在一个地方存储，不出现在多张表中。

    2 MySQL基本命令

    1. -- 显示所有数据库
    2. show databases;
    3. -- 创建数据库
    4. CREATE DATABASE test;
    5. -- 切换数据库
    6. use test;
    7. -- 显示数据库中的所有表
    8. show tables;
    9. -- 创建数据表
    10. CREATE TABLE pet (
    11.  name VARCHAR(20), --类型要大写
    12.  owner VARCHAR(20),
    13.  species VARCHAR(20),
    14.  sex CHAR(1),
    15.  birth DATE,
    16.  death DATE
    17. );
    18. -- 查看数据表结构
    19. -- describe pet;
    20. desc pet;
    21. -- 查询表
    22. SELECT * from pet;
    23. -- 插入数据
    24. INSERT INTO pet VALUES ('puffball', 'Diane', 'hamster', 'f', '1990-03-30', NULL);
    25. -- 修改数据
    26. UPDATE pet SET name = 'squirrel' where owner = 'Diane';
    27. -- 删除数据
    28. DELETE FROM pet where name = 'squirrel';
    29. -- 删除表
    30. DROP TABLE myorder;

    3 select命令详解

    3.1 准备练习数据

    1. -- 创建数据库
    2. CREATE DATABASE select_test;
    3. -- 切换数据库
    4. USE select_test;
    5. -- 创建学生表
    6. CREATE TABLE student (
    7.  no VARCHAR(20) PRIMARY KEY,
    8.  name VARCHAR(20) NOT NULL,
    9.  sex VARCHAR(10) NOT NULL,
    10.  birthday DATE, -- 生日
    11.  class VARCHAR(20) -- 所在班级
    12. );
    13. -- 创建教师表
    14. CREATE TABLE teacher (
    15.  no VARCHAR(20) PRIMARY KEY,
    16.  name VARCHAR(20) NOT NULL,
    17.  sex VARCHAR(10) NOT NULL,
    18.  birthday DATE,
    19.  profession VARCHAR(20) NOT NULL, -- 职称
    20.  department VARCHAR(20) NOT NULL -- 部门
    21. );
    22. -- 创建课程表
    23. CREATE TABLE course (
    24.  no VARCHAR(20) PRIMARY KEY,
    25.  name VARCHAR(20) NOT NULL,
    26.  t_no VARCHAR(20) NOT NULL, -- 教师编号
    27.  -- 表示该 tno 来自于 teacher 表中的 no 字段值
    28.  FOREIGN KEY(t_no) REFERENCES teacher(no)
    29. );
    30. -- 成绩表
    31. CREATE TABLE score (
    32.  s_no VARCHAR(20) NOT NULL, -- 学生编号
    33.  c_no VARCHAR(20) NOT NULL, -- 课程号
    34.  degree DECIMAL,  -- 成绩
    35.  -- 表示该 s_no, c_no 分别来自于 student, course 表中的 no 字段值
    36.  FOREIGN KEY(s_no) REFERENCES student(no),
    37.  FOREIGN KEY(c_no) REFERENCES course(no),
    38.  -- 设置 s_no, c_no 为联合主键
    39.  PRIMARY KEY(s_no, c_no)
    40. );
    41. -- 添加学生表数据
    42. INSERT INTO student VALUES('101', '曾华', '男', '1977-09-01', '95033');
    43. INSERT INTO student VALUES('102', '匡明', '男', '1975-10-02', '95031');
    44. INSERT INTO student VALUES('103', '王丽', '女', '1976-01-23', '95033');
    45. INSERT INTO student VALUES('104', '李军', '男', '1976-02-20', '95033');
    46. INSERT INTO student VALUES('105', '王芳', '女', '1975-02-10', '95031');
    47. INSERT INTO student VALUES('106', '陆军', '男', '1974-06-03', '95031');
    48. INSERT INTO student VALUES('107', '王尼玛', '男', '1976-02-20', '95033');
    49. INSERT INTO student VALUES('108', '张全蛋', '男', '1975-02-10', '95031');
    50. INSERT INTO student VALUES('109', '赵铁柱', '男', '1974-06-03', '95031');
    51. -- 添加教师表数据
    52. INSERT INTO teacher VALUES('804', '李诚', '男', '1958-12-02', '副教授', '计算机系');
    53. INSERT INTO teacher VALUES('856', '张旭', '男', '1969-03-12', '讲师', '电子工程系');
    54. INSERT INTO teacher VALUES('825', '王萍', '女', '1972-05-05', '助教', '计算机系');
    55. INSERT INTO teacher VALUES('831', '刘冰', '女', '1977-08-14', '助教', '电子工程系');
    56. -- 添加课程表数据
    57. INSERT INTO course VALUES('3-105', '计算机导论', '825');
    58. INSERT INTO course VALUES('3-245', '操作系统', '804');
    59. INSERT INTO course VALUES('6-166', '数字电路', '856');
    60. INSERT INTO course VALUES('9-888', '高等数学', '831');
    61. -- 添加添加成绩表数据
    62. INSERT INTO score VALUES('103', '3-105', '92');
    63. INSERT INTO score VALUES('103', '3-245', '86');
    64. INSERT INTO score VALUES('103', '6-166', '85');
    65. INSERT INTO score VALUES('105', '3-105', '88');
    66. INSERT INTO score VALUES('105', '3-245', '75');
    67. INSERT INTO score VALUES('105', '6-166', '79');
    68. INSERT INTO score VALUES('109', '3-105', '76');
    69. INSERT INTO score VALUES('109', '3-245', '68');
    70. INSERT INTO score VALUES('109', '6-166', '81');

    3.2 select查询基本形式

    ```sql — 查询 student 表的所有行 SELECT * FROM student; — 查询 student 表中的指定列：name、sex 和 class SELECT name, sex, class FROM student; — 查询 teacher 表中不重复的 department 列 — DISTINCT命令: 去重查询 SELECT DISTINCT department FROM teacher;

— 查询 score 表中成绩在60-80之间的所有行（区间查询和运算符查询） — BETWEEN xx AND xx: 查询区间 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 = ‘女’;

  1. <a name="c1b29780"></a>
  2. ## 3.3 函数计算
  3. ```sql
  4. -- 查询 "95031" 班的学生人数
  5. -- COUNT: 统计计数，也可以用其他函数计算
  6. SELECT COUNT(*) FROM student WHERE class = '95031';
  8. -- AVG: 平均值
  9. SELECT AVG(degree) FROM score WHERE c_no = '3-105';
  10. -- 分组应用函数计算：先分组，后组内计算
  11. SELECT c_no, AVG(degree) FROM score GROUP BY c_no;
  13. -- 查询 score 表中的最高分的学生学号和课程编号（子查询或排序查询）。
  14. -- MAX：最大值；MIN：最小值
  15. SELECT s_no, c_no FROM score WHERE degree = (SELECT MAX(degree) FROM score);
  17. --YEAR 函数与带 IN 关键字查询
  18. --查询所有和 101 、108 号学生同年出生的 no 、name 、birthday 列
  19. select no, name, birthday from student where year(birthday) in (
  20. select year(birthday) from student where no in (101,108));
  22. --查询 student 表中每个学生的姓名和年龄
  23. select name, year(now())-year(birthday) as age from student;

3.4 排序查询

  1. -- 以 class 降序的方式查询 student 表的所有行
  2. -- DESC: 降序，从高到低
  3. -- ASC（默认）: 升序，从低到高
  4. SELECT * FROM student ORDER BY class DESC;
  5. SELECT * FROM student ORDER BY class ASC;
  6. -- 以 c_no 升序、degree 降序查询 score 表的所有行
  7. SELECT * FROM score ORDER BY c_no ASC, degree DESC;
  9. -- LIMIT r, n: 表示从第r行开始，查询n条数据
  10. SELECT s_no, c_no, degree FROM score ORDER BY degree DESC LIMIT 0, 1;
  11. -- LIMIT n offset r: 表示查询n条数据,从第r行开始
  12. SELECT s_no, c_no, degree FROM score ORDER BY degree DESC LIMIT 1 offset 3;

3.5 模糊查询

  1. -- 查询score表中至少有 2 名学生选修，并以 3 开头的课程的平均分数
  2. -- 首先把 c_no, AVG(degree) 通过分组查询出来
  3. -- 再查询出至少有 2 名学生选修的课程
  4. -- HAVING: 表示持有
  5. -- 并且是以 3 开头的课程
  6. -- LIKE 表示模糊查询，"%" 是一个通配符，匹配 "3" 后面的任意字符。
  7. -- 把前面的SQL语句拼接起来，
  8. SELECT c_no, AVG(degree), COUNT(*) FROM score GROUP BY c_no HAVING COUNT(c_no) >= 2 AND c_no LIKE '3%';
  10. -- NOT: 取反
  11. -- LIKE: 模糊查询
  12. -- 查询 student 表中不姓 "王" 的同学记录
  13. mysql> SELECT * FROM student WHERE name NOT LIKE '王%';

3.6 多表查询

  1. --查询所有学生的name，以及该学生在score表中对应的c_no和degree
  2. -- FROM...: 表示从 student, score 表中查询
  3. -- WHERE 的条件表示为，只有在 student.no 和 score.s_no 相等时才显示出来。
  4. SELECT name, c_no, degree FROM student, score WHERE student.no = score.s_no;
  6. --查询所有学生的 no 、课程名称 ( course 表中的 name ) 和成绩 ( score 表中的degree ) 列。
  7. -- 增加一个查询字段 name，分别从 score、course 这两个表中查询。
  8. -- as 表示取一个该字段的别名。
  9. SELECT s_no, name as c_name, degree FROM score, course WHERE score.c_no = course.no;
  11. --三表关联查询：查询所有学生的 name 、课程名 ( course 表中的 name ) 和 degree 
  12. -- 由于字段名存在重复，使用 "表名.字段名 as 别名" 代替
  13. SELECT student.name as s_name, course.name as c_name, degree
  14. FROM student, score, course
  15. WHERE student.no = score.s_no
  16. AND score.c_no = course.no;
  18. --多重select嵌套：查询计算机系的所有课程的成绩
  19. SELECT * FROM score WHERE c_no IN (
  20. SELECT no FROM course WHERE t_no IN (
  21. SELECT no FROM teacher WHERE department = '计算机系'
  22. ));

3.7 高级逻辑关键字

除了and、or、in等基本关键字，还有下面的高级关键字用于逻辑判断：

  1. --查询计算机系与电子工程系中的不同职称的教师
  2. -- NOT: 代表逻辑非
  3. -- UNION 合并两个集
  4. SELECT * FROM teacher WHERE department = '计算机系' AND profession NOT IN (
  5.     SELECT profession FROM teacher WHERE department = '电子工程系'
  6. )
  7. UNION
  8. SELECT * FROM teacher WHERE department = '电子工程系' AND profession NOT IN (
  9.     SELECT profession FROM teacher WHERE department = '计算机系'
  10. );
  12. --查询课程 3-105 且成绩至少高于 3-245 的 score 表
  13. -- ANY: 符合SQL语句中的任意条件。
  14. -- 也就是说，在 3-105 成绩中，只要有一个大于从 3-245 筛选出来的任意行就符合条件，
  15. -- 最后根据降序查询结果。
  16. SELECT * FROM score WHERE c_no = '3-105' AND degree > ANY(
  17.     SELECT degree FROM score WHERE c_no = '3-245'
  18. ) ORDER BY degree DESC;
  20. --查询课程 3-105 且成绩都高于 3-245 的 score 表
  21. -- ALL: 符合SQL语句中的所有条件。
  22. -- 也就是说，在 3-105 每一行成绩中，都要大于从 3-245 筛选出来全部行才算符合条件。
  23. SELECT * FROM score WHERE c_no = '3-105' AND degree > ALL(
  24.     SELECT degree FROM score WHERE c_no = '3-245'
  25. );

4 Join查询

4.1 准备练习数据

  1. CREATE DATABASE testJoin;
  2. CREATE TABLE person (
  3.     id INT,
  4.     name VARCHAR(20),
  5.     cardId INT
  6. );
  7. CREATE TABLE card (
  8.     id INT,
  9.     name VARCHAR(20)
  10. );
  11. INSERT INTO card VALUES (1, '饭卡'), (2, '建行卡'), (3, '农行卡'), (4, '工商卡'), (5, '邮政卡');
  12. INSERT INTO person VALUES (1, '张三', 1), (2, '李四', 3), (3, '王五', 6);

person表并没有为cardId字段设置一个在card表中对应的 id外键。如果设置了的话，person中cardId字段值为6的行就插不进去，因为该cardId值在card表中并没有。

4.2 内连接

  1. -- INNER JOIN: 表示为内连接，将两张表拼接在一起。
  2. -- on: 表示要执行某个条件。
  3. SELECT * FROM person INNER JOIN card on person.cardId = card.id;
  4. -- 将 INNER 关键字省略掉，结果也是一样的。on 也可以改用where
  5. -- 注意：card 的整张表被连接到了右边。
  6. +------+--------+--------+------+-----------+
  7. | id   | name   | cardId | id   | name      |
  8. +------+--------+--------+------+-----------+
  9. |    1 | 张三   |      1 |    1 | 饭卡      |
  10. |    2 | 李四   |      3 |    3 | 农行卡    |
  11. +------+--------+--------+------+-----------+

4.3 外连接

左外连接

完整显示左边的表 ( person ) ，右边的表如果符合条件就显示，不符合则补 NULL

  1. -- LEFT JOIN 也叫做 LEFT OUTER JOIN，用这两种方式的查询结果是一样的。
  2. SELECT * FROM person LEFT JOIN card on person.cardId = card.id;
  3. +------+--------+--------+------+-----------+
  4. | id   | name   | cardId | id   | name      |
  5. +------+--------+--------+------+-----------+
  6. |    1 | 张三   |      1 |    1 | 饭卡      |
  7. |    2 | 李四   |      3 |    3 | 农行卡    |
  8. |    3 | 王五   |      6 | NULL | NULL      |
  9. +------+--------+--------+------+-----------+

右外连接

完整显示右边的表 ( card ) ，左边的表如果符合条件就显示，不符合则补 NULL

  1. SELECT * FROM person RIGHT JOIN card on person.cardId = card.id;
  2. +------+--------+--------+------+-----------+
  3. | id   | name   | cardId | id   | name      |
  4. +------+--------+--------+------+-----------+
  5. |    1 | 张三   |      1 |    1 | 饭卡      |
  6. |    2 | 李四   |      3 |    3 | 农行卡    |
  7. | NULL | NULL   |   NULL |    2 | 建行卡    |
  8. | NULL | NULL   |   NULL |    4 | 工商卡    |
  9. | NULL | NULL   |   NULL |    5 | 邮政卡    |
  10. +------+--------+--------+------+-----------+

全外连接

完整显示两张表的全部数据。但是MySQL不支持这种语法的全外连接，会出现错误：

  1. -- SELECT * FROM person FULL JOIN card on person.cardId = card.id;
  2. -- 出现错误：
  3. -- ERROR 1054 (42S22): Unknown column 'person.cardId' in 'on clause'

MySQL中，应该使用 UNION 将两张表合并在一起。