MySQL 常用操作整理

发表于

本学期 Java GUI 数据库课程的MySQL增删查改命令汇总

基本操作

  • 列出当前所有数据库 / 表

    1
    2
    SHOW DATABASES;
    SHOW TABLES;

  • 选择数据库 / 表

    1
    USE name;

  • 查看表属性

    1
    DESC tableName;

  • 新建表

    1
    2
    3
    4
    5
    6
    CREATE TABLE tableName1(
        name1 type,
        name2 type,
        PRIMARY KEY(name1, name2),
        FOREIGN KEY(name1) REFERENCES tableName2(name)
    );

  • 插入数据

    1
    INSERT INTO tableName VALUES();

  • 全参与

    1
    ON DELETE CASCADE

更新操作

  • 添加列

    1
    ALTER TABLE tableName add colName colType;

  • 添加主键

    1
    ALTER TABLE tableName add PRIMARY KEY(keyName);

  • 更新数据

    1
    UPDATE tableName SET keyName = keyValue WHERE cond;

  • 删除数据

    1
    DELETE FROM tableName WHERE cond;

  • 删除数据库

    1
    DROP name;

  • 删除表

    1
    DROP TABLE tableName;

  • 删除列

    1
    ALTER TABLE tableName DROP colName;

  • 添加外键

    1
    ALTER TABLE tableName ADD CONSTRAINT fkName FOREIGN KEY(colName) REFERENCES tableName(colName);

    外键必须是唯一的

查询操作

  • 简单查询

    1
    SELECT * FROM tableName WHERE cond;

  • 查询结果去冗余

    1
    SELECT DISTINCT * FROM tableName WHERE cond;

  • 多表查询

    1
    SELECT * FROM tableName1 T1, tableName2 T2 WHERE cond;

  • 与 / 或

    1
    SELECT * FROM tableName WHERE cond1 and (cond2 or cond3);

  • 模糊匹配

    1
    SELECT * FROM tableName WHERE keyName LIKE "%pattern%";

  • 正则表达式匹配

    1
    SELECT * FROM tableName WHERE keyName REGEXP "pattern";

  • 嵌套查询

    1
    2
    3
    SELECT * FROM tableName1 WHERE cond1 and keyName IN(
        SELECT * FROM tableName2 WHERE cond2
    )

  • EXISTS / NOT EXISTS

    判断是否存在，返回真或假

    1
    2
    3
    SELECT * FROM tableName1 WHERE cond1 and EXISTS(
        SELECT * FROM tableName2 WHERE cond2
    )

  • AVG()

    返回平均值

    1
    SELECT AVG(colName) FROM tableName WHERE cond;

  • MAX()

    返回最大值

    1
    SELECT MAX(colName) FROM tableName WHERE cond;

  • MIN()

    返回最小值

    1
    SELECT MIN(colName) FROM tableName WHERE cond;

  • SUM()

    返回总和

    1
    SELECT SUM(colName) FROM tableName WHERE cond;

  • COUNT()

    返回计数

    1
    SELECT COUNT(colName) FROM tableName WHERE cond;

  • 自定义表头

    1
    2
    SELECT colName "newName" FROM tableName WHERE cond;
    SELECT colName AS newName FROM tableName WHERE cond;

  • GROUP BY

    以 … 分组

    1
    2
    3
    4
    #查询每门课程的选课人数
    SELECT Course_ID, COUNT(Student_ID)
    From Enroll
    GROUP BY Course_ID;

  • HAVING

    WHERE是作为检索条件，HAVING是对每一个分组的检索结果进行筛选的条件

    在使用GROUP BY的时候，WHERE只能用在GROUP BY的前面，要想对GROUP使用条件就使用HAVING

    1
    2
    3
    4
    5
    #查询每门课程的选课人数，要求选课人数大于100
    SELECT Course_ID, COUNT(Student_ID) AS NUM
    From Enroll
    GROUP BY Course_ID
    HAVING NUM > 100;