sql

CREATE TABLE student (
    nid INT auto_increment PRIMARY KEY,
    NAME VARCHAR (20)
) ENGINE = INNODB DEFAULT CHARSET = 'utf8';

CREATE TABLE corse (
    nid INT NOT NULL,
    kecheng CHAR (30) NOT NULL,
    chengji DECIMAL,
    CONSTRAINT fk_cc FOREIGN KEY (nid) REFERENCES student (nid)
) ENGINE = INNODB DEFAULT CHARSET = 'utf8';

INSERT INTO student (NAME)
VALUES
    ("lili"),
    ("小明"),
    ("小小明");

SELECT
    *
FROM
    student;

INSERT INTO corse (nid, kecheng, chengji)
VALUES
    (1, "语文", 0),
    (1, "数学", 0),
    (2, "数学", 0),
    (2, "语文", 0);

SELECT
    *
FROM
    `corse`;

SELECT
    *
FROM
    student,
    corse
WHERE
    student.nid = corse.nid;

SELECT
    *
FROM
    student
LEFT OUTER JOIN corse ON student.nid = corse.nid
LIMIT 2 OFFSET 2;

SELECT
    NAME,
    SUM(chengji)
FROM
    student
LEFT JOIN corse ON student.nid = corse.nid
GROUP BY
    student.nid
ORDER BY
    SUM(chengji) DESC;

#修改信息
UPDATE corse
SET chengji = 100
WHERE
    nid = 1
AND kecheng = '语文';

SELECT
    *
FROM
    corse;

SELECT
    *
FROM
    corse
WHERE
    nid IN (1, 2, 10);

SELECT
    *
FROM
    corse
WHERE
    nid NOT IN (1, 2, 10);

DELETE
FROM
    corse
WHERE
    nid = 1
AND kecheng = "语文";

SELECT
    *
FROM
    student
WHERE
    `name` LIKE '%明%';

SELECT
    *
FROM
    student
WHERE
    `name` LIKE '%';

最近在看sql