西电2023春季数据库选修上机 只有2.3.4次的，第一次是当堂完成的，是安装mysql，创建删除数据库，比较简单没留样

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;