SQL(MySQL)学习笔记

SQL

• Structured Query Language，结构化查询语言

数据模型

1. 层次模型
2. 关系模型
3. 网状模型

主键

• 关系表主键
  • 关系表中，任意两条记录不能重复。通过某个字段唯一区分出不同记录的字段叫主键
  • 主键选取的基本原则：不使用任何业务相关的字段作为主键
    1. 选取一：自增数据类型id
    2. 全局唯一GUID类型：GUID算法通过网卡MAC地址、时间戳和任意随机数，保证任意时间点产生的数据都是不同的。
• 联合主键
  • 关系表中，两个或等多的字段设置为主键的行为，称为联合主键。允许一列有重复，不是所有主键都重复即可。

外键

• 通过某一字段可以将表中数据与另一个数据表关联起来，这种列被称为外键
• 外键会约束数据库的性能，故不常用。
• 多对多关系表实际上是通过两个一对多关系表实现的，即通过一个中间表，关联两个一对多关系表，就形成了多对多关系表。
• 一对一关系表：一个表的记录对应到另一个表的唯一记录。

索引

• 索引是数据库中对某一列或多个列的值进行预排序的数据结构。（提升查询速度）
• 唯一索引，业务字段具有唯一性，身份证号、电话号、邮箱地址。。。

操作命令

查询命令

1. 进入MySQL数据库: mysql -u root -p
2. 显示所有数据库：show databases;
3. 使用某一数据库：use 数据库名;
4. 显示数据库中的所有数据表：show tables;
5. 创建数据表

• CREATE TABLE 表名(表的字段内容);

    CREATE TABLE IF NOT EXISTS students(
        id INT(11) AUTO_INCREMENT PRIMARY KEY,
        class_id BIGINT(11) NOT NULL,
        name VARCHAR(20) NOT NULL,
        gender VARCHAR(2) NOT NULL,
        score INT(4) NOT NULL
    )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  

6. 为数据表插入数据

• INSERT INTO 表名(字段1，字段2，字段3…) VALUES (值1，值2，值3…);

    INSERT INTO students(class_id, name, gender, score) VALUES 
    (1,"小明", "M", 90),(1,"小红", "F", 95),(1,"小军", "M", 88),(1,"小米", "F", 73),(2,"小白", "M", 81),(2,"小冰", "F", 55),(2,"小林", "M", 85),(3,"小新", "F", 91),(3,"小王", "M", 89),(3,"小丽", "F", 85);
  

7. 为数据表新增字段

    ALTER TABLE students ADD COLUMN age INT(4) NOT NULL;
   

8. 删除数据库或者数据表：

    -- 删除test数据库
    DROP DATABASE test;
    --删除students数据表
    DROP TABLE students;
   

9. 显示数据表结构

   DESC students;
   

10. 察看创建数据表的SQL语句
    SHOW CREATE TABLE students;

基本查询

• 语法：SELECT * FROM <表名>;
  • SELECT * FROM students; 能够查询表中的所有内容

条件查询

• 语法：SELECT * FROM <表名> WHERE <条件>;
  • SELECT * FROM students WHERE score>90;
  • SELECT * FROM students WHERE score>90 AND gender=“M”;
  • SELECT * FROM students WHERE score>80 OR gender=“M”;
  • SELECT * FROM students WHERE NOT class_id=2;

投影查询

• 语法：SELECT 列1，列2，列3 FROM <表名>;
  • 这样返回的结果就只有我们指定查询的列。
• 使用投影查询，并将列名重命名
  • SELECT 列1 别名1，列2 别名2，列3 别名3 FROM <表名>;
• 投影查询结合WHERE条件查询
  • 语法：SELECT 列1，列2，列3 FROM <表名> WHERE <条件>;

排序

• 从低到高排序语法：SELECT 列1，列2，列3 FROM <表名> ORDER BY <排序条件>;
  • 按成绩从低到高排序：SELECT id, name, gender, score FROM students ORDER BY score;
• 从高到低排序：SELECT 列1，列2，列3 FROM <表名> ORDER BY <排序条件> DESC;
• 排序条件相同时，再添加一个条件作为排序约束
  • 例如：SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;（先根据score进行倒序，分数相等时，根据gender排序）

分页查询

• 数据行过多时，分页显示
  1. 例如：SELECT id, name, gender, score FROM students ORDER BY score DESC LIMIT 3 OFFSET 0;(LIMIT 3:每页显示3条数据;OFFSET 0:从记录第一条开始。)
  2. 要继续查询：SELECT id, name, gender, score FROM students ORDER BY score DESC LIMIT 3 OFFSET 3;（只需偏移增量）
     • LIMIT：总是设定为pagesize；
     • OFFSET： 总是设定为pagesize * (pageIndex-1);
     • OFFSET 超过查询的最大数量并不会报错，而是返回一个空的结果集。

聚合查询

• 聚合函数（总数，平均数。。。）
  • 查询数据表总数据条目总数；
    • SELECT COUNT(*) FROM students;
  • 通常使用聚合查询时设置别名，方便察看
    • SELECT COUNT(*) num FROM students;
  • 使用聚合查询并设置WHERE条件
    • SELECT COUNT(*) num FROM students WHERE gender=“F”;
• 分组(分组聚合)
  • 按class_id分组：SELECT COUNT(*) FROM students GROUP BY class_id;
  • 按class_id分组：SELECT class_id,COUNT(*) FROM students GROUP BY class_id;
  • 按class_id和gender分组：SELECT class_id,gender,COUNT(*) FROM students GROUP BY class_id, gender;
  • 查出每个班级的平均分，结果集应当有3条记录:SELECT class_id,AVG(score) FROM students GROUP BY class_id;
  • 查出每个班级男生和女生的平均分，结果集应当有3条记录:SELECT class_id,gender,AVG(score) FROM students GROUP BY class_id,gender;

多表查询

• 语法：SELECT * FROM <表1>,<表2>;**（得到的是表1和表2的乘积，结果仍为二维表）。这种查询又叫笛卡尔查询，使用时要特别小心。
  • 别名用法
    • 例如：SELECT s.id sic,s.class_id,s.name sname,s.gender, c.id cid, c.name cname FROM students s, classes c;
    • 添加查询条件：SELECT s.id sic,s.class_id,s.name sname,s.gender, c.id cid, c.name cname FROM students s, classes c WHERE s.gender=“F” and c.id = 1;

连接查询

• 存放班级名称的name列存储在classes表中，只有根据students表的class_id，找到classes表对应的行，再取出name列，就可以获得班级名称。
• 内连接——INNER JOIN
  • SELECT s.id sid, s.name sname, s.class_id,c.name class_name, s.gender,s.score FROM students s INNER JOIN classes c ON s.class_id = c.id;
    • 首先确认主表，使用FROM<表1>
    • 再确认副表，使用INNER JOIN<表2>
    • 然后确认连接条件ON<条件…>
    • 可选：加上WHERE、ORDER BY等子句。
• 外连接——OUTER JOIN
  • SELECT s.id sid, s.class_id, c.name class_name, s.gender,s.name student_name, s.score FROM students s RIGHT OUTER JOIN classes c ON s.class_id = c.id;(# 主副两个表分别是左右【LEFT，RIGHT】)
  • SELECT s.id sid, s.class_id, c.name class_name, s.gender,s.name student_name, s.score FROM students s LEFT OUTER JOIN classes c ON s.class_id = c.id;
  • SELECT s.id sid, s.class_id, c.name class_name, s.gender,s.name student_name, s.score FROM students s FULL OUTER JOIN classes c ON s.class_id = c.id;

修改数据

INSERT:插入数据
UPDATE:修改数据
DELETE:删除数据

INSERT —— 插入数据

• 基本语法：INSERT INTO (字段1，字段2，字段3…) VALUES （值1，值2，值3…）
  • 插入一条数据
    • 例如：INSERT INTO students (class_id, name, gender, score) VALUES (3, “大脚”, “F”, 79);
  • 一次性插入多条数据
    • 例如：INSERT INTO students (class_id, name, gender, score) VALUES (3, “大脚”, “F”, 79),(4, “大牛”, “M”, 67);

UPDATE —— 修改数据

• 基本语法：UPDATE <表名> SET 字段1=值1，字段2=值2… WHERE …;
  • 例如：UPDATE students SET name=“大熊”, gender=“M”,score=87 WHERE id=12;
  • UPDATE students SET score = score + 10 WHERE score<80;
  • 如果WHERE条件没有匹配到任何记录，UPDATE语句不会报错，也不会有任何记录被更新。
    • 例如：UPDATE students SET score=100 WHERE ID=999;

DELETE —— 删除数据

• 基本语法：DELETE FROM <表名> WHERE id=1;
  • 删除整个表：DELETE FROM <表名>;
  • DELETE FROM students id=4 AND id=7;
  • DELETE FROM students WHERE score<60;

灵活的SQL语句

1. 插入或替换。如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就先删除原记录，再插入新记录。

REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

2. 插入或更新。插入一条新记录（INSERT），但如果记录已经存在，就更新该记录

INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;

3. 插入或忽略。当插入一条新记录（INSERT），但如果记录已经存在，就什么也不做。

INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

4. 快照。对一个表进行快照，即复制一份当前表的数据到一个新表。

CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;

5. 写入查询结果集。将查询结果写入到表中。

INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;