50個SQL語句(MySQL版) 建表 插入數據

本學期正在學習資料庫，前段時間老師讓我們做一下50個經典SQL語句，當時做的比較快，有一些也是百度的，自我感覺理解的不是很透徹。

所以從本篇隨筆開始，我將進行50個經典SQL語句的復盤，加深理解。

答案僅供參考，不一定完全正確，若發現錯誤或有更好的，歡迎評論，互相交流，一起成長！！！

--------------------------表結構--------------------------

student(StuId,StuName,StuAge,StuSex) 學生表

teacher(TId,Tname) 教師表

course(CId,Cname,C_TId) 課程表

sc(SId,S_CId,Score) 成績表

----------------------------------------------------------

建表：

create database MySchool character set "utf8mb4";
use MySchool;
create table student
(
    StuId varchar(5) primary key,
    StuName varchar(10) not null,
    StuAge int,
    StuSex char(2) not null
);
create table teacher
(
    TId varchar(3) primary key,
    Tname varchar(10) not null
);
create table course
(
    CId varchar(3) primary key,
    Cname varchar(20) not null,
    C_TId varchar(3) not null,
    FOREIGN KEY (C_TId) REFERENCES teacher(TId)
);
create table sc
(
    SId varchar(5) not null,
    FOREIGN KEY (SId) REFERENCES student(StuId),
    S_CId varchar(3) not null,
    FOREIGN KEY (S_CId) REFERENCES course(CId),
    Score float
);

插入數據：

insert into student value('1000','張無忌',18,'男'),
('1001','周芷若',19,'女'),
('1002','楊過',19,'男'),
('1003','趙敏',18,'女'),
('1004','小龍女',17,'女'),
('1005','張三豐',18,'男'),
('1006','令狐沖',19,'男'),
('1007','任盈盈',20,'女'),
('1008','岳靈珊',19,'女'),
('1009','韋小寶',18,'男'),
('1010','康敏',17,'女'),
('1011','蕭峰',19,'男'),
('1012','黃蓉',18,'女'),
('1013','郭靖',19,'男'),
('1014','周伯通',19,'男'),
('1015','瑛姑',20,'女'),
('1016','李秋水',21,'女'),
('1017','黃藥師',18,'男'),
('1018','李莫愁',18,'女'),
('1019','馮默風',17,'男'),
('1020','王重陽',17,'男'),
('1021','郭襄',18,'女');

insert into teacher value('001','姚明'),
('002','葉平'),
('003','葉開'),
('004','孟星魂'),
('005','獨孤求敗'),
('006','裘千仞'),
('007','裘千尺'),
('008','趙志敬'),
('009','阿紫'),
('010','郭芙蓉'),
('011','佟湘玉'),
('012','白展堂'),
('013','呂輕侯'),
('014','李大嘴'),
('015','花無缺'),
('016','金不換'),
('017','喬丹');

insert into course value('001','企業管理','002'),
('002','馬克思','008'),
('003','UML','006'),
('004','資料庫','007'),
('005','邏輯電路','006'),
('006','英語','003'),
('007','電子電路','005'),
('008','思想概論','004'),
('009','西方哲學史','012'),
('010','線性代數','017'),
('011','電腦基礎','013'),
('012','AUTO CAD製圖','015'),
('013','平面設計','011'),
('014','Flash動漫','001'),
('015','Java開發','009'),
('016','C#基礎','002'),
('017','Oracl資料庫原理','010');

insert into sc value('1001','003',90),
('1001','002',87),
('1001','001',96),
('1001','010',85),
('1002','003',70),
('1002','002',87),
('1002','001',42),
('1002','010',65),
('1003','006',78),
('1003','003',70),
('1003','005',70),
('1003','001',32),
('1003','010',85),
('1003','011',21),
('1004','007',90),
('1004','002',87),
('1005','001',23),
('1006','015',85),
('1006','006',46),
('1006','003',59),
('1006','004',70),
('1006','001',99),
('1007','011',85),
('1007','006',84),
('1007','003',72),
('1007','002',87),
('1008','001',94),
('1008','012',85),
('1008','006',32),
('1009','003',90),
('1009','002',82),
('1009','001',96),
('1009','010',82),
('1009','008',92),
('1010','003',90),
('1010','002',87),
('1010','001',96),
('1011','009',24),
('1011','009',25),
('1012','003',30),
('1013','002',37),
('1013','001',16),
('1013','007',55),
('1013','006',42),
('1013','012',34),
('1000','004',16),
('1002','004',55),
('1004','004',42),
('1008','004',34),
('1013','016',86),
('1013','016',44),
('1000','014',75),
('1002','016',100),
('1004','001',83),
('1008','013',97);