50 SQL statements (MySQL version) create tables to insert data

This semester is learning database. Some time ago, the teacher asked us to do 50 classic SQL statements. At that time, we did it faster. Some of them were Baidu's, and we didn't understand them very well.

So from the beginning of this essay, I will make 50 copies of classic SQL statements to deepen my understanding.

The answer is for reference only, not necessarily completely right. If you find mistakes or have better ones, welcome to comment, communicate with each other and grow together!!!

--------------------------Table structure--------------------------

student(StuId,StuName,StuAge,StuSex) student table

teacher(TId,Tname) teacher table

Course (CID, CNAME, C? TID)

SC (SID, s-ciu, score) score sheet

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

Table building:

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 data:

insert into student value('1000','zhang wuji',18,'male'),
('1001','Zhou Zhiruo',19,'female'),
('1002','Yang Guo',19,'male'),
('1003','Zhao Min',18,'female'),
('1004','little dragon maiden',17,'female'),
('1005','Zhang Sanfeng',18,'male'),
('1006','linghu chong',19,'male'),
('1007','Ren yingying',20,'female'),
('1008','Yue Lingshan',19,'female'),
('1009','Trinket',18,'male'),
('1010','Kang min',17,'female'),
('1011','Xiao Feng',19,'male'),
('1012','Huang Rong',18,'female'),
('1013','Guo Jing',19,'male'),
('1014','Zhou Botong',19,'male'),
('1015','Ying Gu',20,'female'),
('1016','Li Qiushui',21,'female'),
('1017','pharmacist hwang',18,'male'),
('1018','Li Mochou',18,'female'),
('1019','Feng Mufeng',17,'male'),
('1020','wang chongyang',17,'male'),
('1021','Guo Xiang',18,'female');

insert into teacher value('001','Yao Ming'),
('002','Ye Ping'),
('003','Ye Kai'),
('004','Meng Xinghun'),
('005','Seeking defeat alone'),
('006','Qiu Qianren'),
('007','Qiu micrometer'),
('008','Zhao Zhijing'),
('009','Violet'),
('010','Guo Furong'),
('011','Tong Xiangyu'),
('012','White Hall'),
('013','LV Qinghou'),
('014','Li Dazui'),
('015','There is no shortage of flowers'),
('016','Gold does not change'),
('017','Jordan');

insert into course value('001','business management','002'),
('002','Marx','008'),
('003','UML','006'),
('004','data base','007'),
('005','logical circuit','006'),
('006','English?','003'),
('007','electronic circuit ','005'),
('008','Introduction to thought','004'),
('009','History of Western Philosophy','012'),
('010','linear algebra','017'),
('011','Fundamentals of computer','013'),
('012','AUTO CAD Drafting','015'),
('013','Graphic Artist Designer','011'),
('014','Flash Comic','001'),
('015','Java Development','009'),
('016','C#Basics','002'),
('017','Oracl Database principle','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);

Tags: MySQL Database SQL Java

Posted on Sun, 03 May 2020 17:30:28 -0400 by ambivalent