CREATE DATABASE studentDB DEFAULT CHARACTER
SET GBK;
DROP TABLE students;
DROP TABLE sc;
DROP TABLE course;
CREATE TABLE students (
sno VARCHAR(20) NOT NULL,
姓名 VARCHAR(50) NOT NULL,
性别 VARCHAR(10),
出生日期 VARCHAR(10),
专业号 INT,
班级 VARCHAR(50),
电话 VARCHAR(20),
身份证号 VARCHAR(20),
PRIMARY KEY (sno)
);
#UPDATE students AS s
#JOIN students AS b ON s.学号 = b.学号
#该段用于第二次上机 SET s.身份证号 = b.身份证号;
#ALTER TABLE students
#该段用于第二次上机 DROP COLUMN 出生日期;
DESCRIBE students;
# 上面用于第二次上机
INSERT students(sno,姓名,性别,出生日期) VALUES('123456','Ray','M','2021'),
('152011','Helen','F','2015'),
('111111','张三','M','2021');
UPDATE students SET 班级='人工智能05',专业号='1513' WHERE sno=152011;
DELETE FROM students WHERE 姓名='张三';
TRUNCATE students;
INSERT students VALUES('111111','王五','M','2021','1513','人工智能02','123','123'),
('111112','王六','M','2021','1513','人工智能03','123','123'),
('111113','张三','M','2021','1502','人工智能02','123','123'),
('111114','李四','M','2021','1513','人工智能05','123','123');
CREATE TABLE sc (
sno INT NOT NULL,
cno INT NOT NULL,
grade INT NOT NULL
);
INSERT sc VALUES('111111','1','82'),
('111112','2','85'),
('111113','3','86'),
('111111','1','80'),
('111112','2','86'),
('111113','3','87'),
('111111','1','79'),
('111112','2','89'),
('111113','3','65');
SELECT sno'学号',grade'成绩' FROM sc WHERE grade>=75 and grade<=85;
SELECT * FROM students WHERE 姓名 LIKE '王%' AND 班级='人工智能02';
SELECT cno,MAX(grade) FROM sc GROUP BY cno;
CREATE TABLE course (
cno INT NOT NULL,
cname VARCHAR(50),
ccredit int,
cdept varchar(20)
);
INSERT course VALUES('1','math','5','A'),
('2','PE','2','B'),
('3','C++','4','C');
SELECT *
FROM sc natural join course natural join students
limit 4;
