MySQL数据指令

数据指令

增删改查

插入数据，如果已有主键值则插入数据失败。

mysql> INSERT INTO student
    -> (ID,name,grade)
    -> VALUES(755,'王东浩',80);Copy to clipboardErrorCopied

插入并替换数据，如果已有主键值则先删除再插入。

mysql> REPLACE INTO student
    -> (ID,name,grade)
    -> VALUES(755,'王东浩',80);Copy to clipboardErrorCopied

更新数据

mysql> UPDATE student
    -> SET name='孙鹏',grade=60
    -> WHERE id=753;Copy to clipboardErrorCopied

删除数据

mysql> DELETE FROM student
    -> WHERE id=754;Copy to clipboardErrorCopied

查询数据

mysql> SELECT id,name FROM student               -- 按条件查询数据
    -> WHERE id BETWEEN 753 and 755;

mysql> SELECT * FROM student;                    -- 查询全部数据Copy to clipboardErrorCopied

条件语句

• DISTINCT 关键字用于对查询结果去重，必须放于所有字段前。只有多个字段全部相等才会被去重。

mysql> SELECT DINTINCE age,sex FROM student;     -- 查询数据并去重Copy to clipboardErrorCopied

• WHERE 语句用于指定 更新/删除/查询 的操作范围，如果不设定范围将对全部数据进行操作。

mysql> SELECT * FROM student WHERE id = 100;
mysql> SELECT * FROM student WHERE id != 100;
mysql> SELECT * FROM student WHERE id [NOT] BETWEEN 30 AND 50;
mysql> SELECT * FROM student WHERE id [NOT] IN (30, 35 ,50);
mysql> SELECT * FROM student WHERE grade IS [NOT] NULL;Copy to clipboardErrorCopied

• LIKE 语句用于对字符串进行模糊匹配：%代表任意多个字符 _代表一个字符 /代表转义

mysql> SELECT * FROM student WHERE name LIKE 'Tom%';Copy to clipboardErrorCopied

分组排序

数据分组

1. GROUP 语句指定数据的分组方式，如果不含则默认把全部数据合并为一条数据。（本质是生成临时表）
2. AS 关键字为表或者列起别名，可省略。
3. HAVING 语句对分组后的结果进行筛选。

-- 查询班级总数
mysql> SELECT COUNT(*) FROM class;                    -- 全部合并

-- 查询各年级人数
mysql> SELECT grade, SUM(class.student_num) AS nums 
    -> FROM class 
    -> GROUP BY grade                                 -- 各班数据按年级合并
    -> HAVING SUM(class.student_num) > 200;           -- 筛选人数大于 200 的年级Copy to clipboardErrorCopied

数据排序

• ORDER 语句指定数据显示顺序，ASC 为升序 / DESC 为降序。
• LIMIT 语句对排序后的数据进行筛选，指定起始序号和总数量。

-- 查询学生信息
mysql> SELECT * 
    -> FROM student 
    -> ORDER BY grade DESC, ID ASC                   -- 按成绩降序排列，若相同按学号升序排列
    -> LIMIT 10,20;                                  -- 筛选第 11 - 30 名Copy to clipboardErrorCopied

多表查询

嵌套查询

1. FROM 型：子语句返回一个表，且必须给子查询结果取别名。
2. WHERE 型：子语句返回一个值，不能用于 UPDATE。

-- FROM 型
mysql> SELECT * 
    -> FROM (SELECT * FROM tb WHERE id > 0) AS subfrom 
    -> WHERE id > 1;

-- WHERE 型
mysql> SELECT * 
    -> FROM tb
    -> WHERE money = (SELECT max(money) FROM tb);Copy to clipboardErrorCopied

合并查询

1. 默认为 DISTINCT 形式，不同表查询到的相同数据只展示一个。
2. 设置为 ALL 则不同表查询到的相同结果重复展示。

-- DISTINCT 形式
mysql> (SELECT * FROM student WHERE id < 10) 
    -> UNION
    -> (SELECT * FROM student WHERE id > 20);

-- ALL 形式
mysql> (SELECT * FROM student1) 
    -> UNION ALL 
    -> (SELECT * FROM student2);Copy to clipboardErrorCopied

连表查询

• 内连接 INNER JOIN：（默认）未指定连接条件时，自动查找相同字段名匹配连接条件。

mysql> SELECT s.id,s.name,c.name
    -> FROM student s JOIN class c
    -> ON e.cid = c.id;

mysql> SELECT * 
    -> FROM student s, class c 
    -> WHERE s.id = c.id; Copy to clipboardErrorCopied

• 交叉连接 CROSS JOIN：未指定连接条件时，视为无连接条件。

mysql> SELECT *
    -> FROM boy CROSS JOIN girl;                 -- 显示所有交配可能


mysql> SELECT *
-> FROM boy, girl;                               -- 等价写法Copy to clipboardErrorCopied

• 外连接 OUTER JOIN：如果数据不存在，也会出现在连接结果中。

  • LEFT JOIN：左表数据一定显示，没有匹配右表数据用 null 填充。
  • RIGHT JOIN：右表数据一定显示，没有匹配左表数据用 null 填充。
  • FULL JOIN：两表数据一定显示，没有匹配数据用 null 填充。

mysql> SELECT s.id,s.name,c.name                   -- 显示学生的班级信息
    -> FROM student s LEFT JOIN class c            -- 没有班级的学生也会显示
    -> ON s.cid = c.id;

-- 先筛选再连接（效率等价，但如果有大量重复值提前筛选可以提高效率）
mysql> SELECT s.id,s.name,c.name    
    -> FROM student s LEFT JOIN (SELECT DINTINCT id, name FROM class) c       
    -> ON s.cid = c.id;