SQL基础语法_刘世民

1.建库建表语法，字段数据类型, 需要案例

CREATE DATABASE IF NOT EXISTS love DEFAULT CHARSET utf8;
USE love;

CREATE TABLE IF NOT EXISTS student
(
    sid INT PRIMARY KEY AUTO_INCREMENT,
    sname VARCHAR(50) NOT NULL,
    gender VARCHAR(50) NOT NULL,
    class VARCHAR(50) NOT NULL COMMENT"班级",
    class_id INT
);

2.增删改查语法，需要案例

INSERT INTO student(sname,gender,class,class_id)
        VALUES("张三","男","大数据32001",1),
            ("李四","女","大数据32002",1),
            ("王五","男","虚拟现实32001",2),
            ("老六","男","虚拟现实32002",3);

DELETE FROM student WHERE sname="老六";
UPDATE student SET gender="男" WHERE sname="李四";
SELECT *FROM student;

3.表关联查询，需要案例

CREATE TABLE IF NOT EXISTS student2
(
    uid INT PRIMARY KEY NOT NULL,
    uname VARCHAR(50) NOT NULL,
    ugender VARCHAR(50) NOT NULL,
    uclass VARCHAR(50) NOT NULL COMMENT"班级",
    uclass_id INT
);
SELECT *FROM student2;
DROP TABLE student2;
INSERT INTO student2(uid,uname,ugender,uclass,uclass_id)
        VALUES(1,"张三","男","大数据32001",1),
            (2,"李四","女","大数据32002",1),
            (4,"王五","男","虚拟现实32001",2),
            (5,"老六","男","虚拟现实32002",3),
            (6,"老气","男","虚拟现实32004",3),
            (7,"老八","男","虚拟现实32005",3);
SELECT w.*,a.* FROM student w LEFT JOIN  student2 a ON w.sid=a.uid;

4.子查询，需要案例

CREATE TABLE IF NOT EXISTS de
(
    cid INT PRIMARY KEY AUTO_INCREMENT,
    cname VARCHAR(50) NOT NULL,
    teacher_id INT
);
INSERT INTO de(cname,teacher_id)
    VALUES("语文",1),
        ("数学",2),
        ("英语",3);
SELECT *FROM de;

CREATE TABLE IF NOT EXISTS lol
(
    lid INT,
    student_id INT,
    corse_id INT,
    NUMBER INT
);
INSERT INTO lol(lid,student_id,corse_id,NUMBER)
    VALUES(1,1,1,79),
          (1,1,2,80),
          (1,1,3,70),
          (2,2,1,66),
          (2,2,2,80),
          (2,2,3,87),
          (3,3,1,97),
          (3,3,2,69),
          (3,3,3,89);
SELECT *FROM lol;
SELECT st.*,d.* FROM student st 
LEFT JOIN lol d ON st.sid=d.lid 
LEFT JOIN de e ON e.teacher_id=d.corse_id;