DB学习总结

最新推荐文章于 2024-09-12 01:15:37 发布

蜗牛不敲代码

最新推荐文章于 2024-09-12 01:15:37 发布

阅读量294

点赞数

分类专栏： db 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_44247505/article/details/107559516

版权

db 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

DB学习总结

数据库操作

创建数据库

create datebase 数据库名;

显示所有数据库

show databases;

删除数据库

drop database 数据库名;

选择数据库

use 数据库名;

表操作

创建表

create table 表名 (字段名 字段类型);

例：

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
    `runoob_id` INT UNSIGNED AUTO_INCREMENT,
    `runoob_title` VARCHAR(100) NOT NULL,
    `runoob_author` VARCHAR(40) NOT NULL,
    `submission_date` DATE,
    PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

删除表

drop table 表名;

表插入数据

insert into 表名 (字段1,字段2) values (值1，值2);

查询表中数据

select */字段名 from 表名 where 条件表达式;

删除表中数据

delete from 表名 where 条件表达式;

修改表中数据

update 表名 set 字段=新值 where 条件表达式;

增加字段

alter table 表名 add 字段 类型 其他

例：

alter table mytable add passtest int(4);
-- 加主关键字索引
alter table mytable add primary key(id);

删除字段

alter table 表名 drop 字段名;

修改表名

rename table 新表名 to 旧表名;

sql查询练习

创建数据库

Create Table  HAND_STUDENT
(
 STUDENT_NO Varchar(10) Not Null,
 STUDENT_NAME Varchar(20),
 STUDENT_AGE BigInt,
 STUDENT_GENDER Varchar(5),
 OBJECT_VERSION_NUMBER BigInt Not Null Default 1,
 CREATION_DATE  datetime Not Null Default CURRENT_TIMESTAMP,
 CREATED_BY BigInt Not Null Default -1,
 LAST_UPDATED_BY BigInt Not Null Default -1,
 LAST_UPDATE_DATE datetime Not Null Default CURRENT_TIMESTAMP,
 LAST_UPDATE_LOGIN BigInt Default -1
);
ALTER TABLE  HAND_STUDENT COMMENT '学生信息表';
ALTER TABLE  HAND_STUDENT MODIFY `STUDENT_NO` Varchar(10) Not Null Comment '学号';
ALTER TABLE  HAND_STUDENT MODIFY `STUDENT_NAME` Varchar(20) Comment '姓名';
ALTER TABLE  HAND_STUDENT MODIFY `STUDENT_AGE` BigInt Comment '年龄';
ALTER TABLE  HAND_STUDENT MODIFY `STUDENT_GENDER` Varchar(5) Comment '性别';
ALTER TABLE  HAND_STUDENT MODIFY `OBJECT_VERSION_NUMBER` BigInt Not Null Default 1 Comment '行版本号，用来处理锁';

ALTER TABLE `HAND_STUDENT` ADD UNIQUE (`STUDENT_NO`);

Create Table  HAND_TEACHER
(
 TEACHER_NO Varchar(10) Not Null,
 TEACHER_NAME Varchar(20),
 MANAGER_NO Varchar(10),
 OBJECT_VERSION_NUMBER BigInt Not Null Default 1,
 CREATION_DATE  datetime Not Null Default CURRENT_TIMESTAMP,
 CREATED_BY BigInt Not Null Default -1,
 LAST_UPDATED_BY BigInt Not Null Default -1,
 LAST_UPDATE_DATE datetime Not Null Default CURRENT_TIMESTAMP,
 LAST_UPDATE_LOGIN BigInt Default -1
);
ALTER TABLE  HAND_TEACHER COMMENT '教师信息表';
ALTER TABLE  HAND_TEACHER MODIFY `TEACHER_NO` Varchar(10) Not Null Comment '教师编号';
ALTER TABLE  HAND_TEACHER MODIFY `TEACHER_NAME` Varchar(20) Comment '教师名称';
ALTER TABLE  HAND_TEACHER MODIFY `MANAGER_NO` Varchar(10) Comment '上级编号';
ALTER TABLE  HAND_TEACHER MODIFY `OBJECT_VERSION_NUMBER` BigInt Not Null Default 1 Comment '行版本号，用来处理锁';

ALTER TABLE `HAND_TEACHER` ADD UNIQUE (`TEACHER_NO`);


Create Table  HAND_COURSE
(
 COURSE_NO Varchar(10) Not Null,
 COURSE_NAME Varchar(20),
 TEACHER_NO Varchar(10) Not Null,
 OBJECT_VERSION_NUMBER BigInt Not Null Default 1,
 CREATION_DATE  datetime Not Null Default CURRENT_TIMESTAMP,
 CREATED_BY BigInt Not Null Default -1,
 LAST_UPDATED_BY BigInt Not Null Default -1,
 LAST_UPDATE_DATE datetime Not Null Default CURRENT_TIMESTAMP,
 LAST_UPDATE_LOGIN BigInt Default -1
);
ALTER TABLE  HAND_COURSE COMMENT '课程信息表';
ALTER TABLE  HAND_COURSE MODIFY `COURSE_NO` Varchar(10) Not Null Comment '课程号';
ALTER TABLE  HAND_COURSE MODIFY `COURSE_NAME` Varchar(20) Comment '课程名称';
ALTER TABLE  HAND_COURSE MODIFY `TEACHER_NO` Varchar(10) Not Null Comment '教师编号';
ALTER TABLE  HAND_COURSE MODIFY `OBJECT_VERSION_NUMBER` BigInt Not Null Default 1 Comment '行版本号，用来处理锁';

ALTER TABLE `HAND_COURSE` ADD UNIQUE (`COURSE_NO`);

Create Table  HAND_STUDENT_CORE
(
 STUDENT_NO Varchar(10) Not Null,
 COURSE_NO Varchar(10) Not Null,
 CORE Decimal(4,2),
 OBJECT_VERSION_NUMBER BigInt Not Null Default 1,
 CREATION_DATE  datetime Not Null Default CURRENT_TIMESTAMP,
 CREATED_BY BigInt Not Null Default -1,
 LAST_UPDATED_BY BigInt Not Null Default -1,
 LAST_UPDATE_DATE datetime Not Null Default CURRENT_TIMESTAMP,
 LAST_UPDATE_LOGIN BigInt Default -1
);
ALTER TABLE  HAND_STUDENT_CORE COMMENT '学生成绩表';
ALTER TABLE  HAND_STUDENT_CORE MODIFY `STUDENT_NO` Varchar(10) Not Null Comment '学号';
ALTER TABLE  HAND_STUDENT_CORE MODIFY `COURSE_NO` Varchar(10) Not Null Comment '课程号';
ALTER TABLE  HAND_STUDENT_CORE MODIFY `CORE` Decimal(4,2) Comment '分数';
ALTER TABLE  HAND_STUDENT_CORE MODIFY `OBJECT_VERSION_NUMBER` BigInt Not Null Default 1 Comment '行版本号，用来处理锁';

ALTER TABLE `HAND_STUDENT_CORE` ADD UNIQUE (`STUDENT_NO`,`COURSE_NO`);

/*******初始化学生表的数据******/
insert into HAND_STUDENT(STUDENT_NO,STUDENT_NAME,STUDENT_AGE,STUDENT_GENDER) values ('s001','张三',23,'男');
insert into HAND_STUDENT(STUDENT_NO,STUDENT_NAME,STUDENT_AGE,STUDENT_GENDER) values ('s002','李四',23,'男');
insert into HAND_STUDENT(STUDENT_NO,STUDENT_NAME,STUDENT_AGE,STUDENT_GENDER) values ('s003','吴鹏',25,'男');
insert into HAND_STUDENT(STUDENT_NO,STUDENT_NAME,STUDENT_AGE,STUDENT_GENDER) values ('s004','琴沁',20,'女');
insert into HAND_STUDENT(STUDENT_NO,STUDENT_NAME,STUDENT_AGE,STUDENT_GENDER) values ('s005','王丽',20,'女');
insert into HAND_STUDENT(STUDENT_NO,STUDENT_NAME,STUDENT_AGE,STUDENT_GENDER) values ('s006','李波',21,'男');
insert into HAND_STUDENT(STUDENT_NO,STUDENT_NAME,STUDENT_AGE,STUDENT_GENDER) values ('s007','刘玉',21,'男');
insert into HAND_STUDENT(STUDENT_NO,STUDENT_NAME,STUDENT_AGE,STUDENT_GENDER) values ('s008','萧蓉',21,'女');
insert into HAND_STUDENT(STUDENT_NO,STUDENT_NAME,STUDENT_AGE,STUDENT_GENDER) values ('s009','陈萧晓',23,'女');
insert into HAND_STUDENT(STUDENT_NO,STUDENT_NAME,STUDENT_AGE,STUDENT_GENDER) values ('s010','陈美',22,'女');
commit;
/******************初始化教师表***********************/
insert into HAND_TEACHER(TEACHER_NO,TEACHER_NAME,MANAGER_NO) values ('t001', '刘阳','');
insert into HAND_TEACHER(TEACHER_NO,TEACHER_NAME,MANAGER_NO)  values ('t002', '谌燕','t001');
insert into HAND_TEACHER(TEACHER_NO,TEACHER_NAME,MANAGER_NO)  values ('t003', '胡明星','t002');
commit;
/***************初始化课程表****************************/
insert into HAND_COURSE(COURSE_NO,COURSE_NAME,TEACHER_NO) values ('c001','J2SE','t002');
insert into HAND_COURSE(COURSE_NO,COURSE_NAME,TEACHER_NO) values ('c002','Java Web','t002');
insert into HAND_COURSE(COURSE_NO,COURSE_NAME,TEACHER_NO) values ('c003','SSH','t001');
insert into HAND_COURSE(COURSE_NO,COURSE_NAME,TEACHER_NO) values ('c004','Oracle','t001');
insert into HAND_COURSE(COURSE_NO,COURSE_NAME,TEACHER_NO) values ('c005','SQL SERVER 2005','t003');
insert into HAND_COURSE(COURSE_NO,COURSE_NAME,TEACHER_NO) values ('c006','C#','t003');
insert into HAND_COURSE(COURSE_NO,COURSE_NAME,TEACHER_NO) values ('c007','JavaScript','t002');
insert into HAND_COURSE(COURSE_NO,COURSE_NAME,TEACHER_NO) values ('c008','DIV+CSS','t001');
insert into HAND_COURSE(COURSE_NO,COURSE_NAME,TEACHER_NO) values ('c009','PHP','t003');
insert into HAND_COURSE(COURSE_NO,COURSE_NAME,TEACHER_NO) values ('c010','EJB3.0','t002');
commit;
/***************初始化成绩表***********************/
insert into HAND_STUDENT_CORE(STUDENT_NO,COURSE_NO,CORE) values ('s001','c001',58.9);
insert into HAND_STUDENT_CORE(STUDENT_NO,COURSE_NO,CORE) values ('s002','c001',80.9);
insert into HAND_STUDENT_CORE(STUDENT_NO,COURSE_NO,CORE) values ('s003','c001',81.9);
insert into HAND_STUDENT_CORE(STUDENT_NO,COURSE_NO,CORE) values ('s004','c001',60.9);
insert into HAND_STUDENT_CORE(STUDENT_NO,COURSE_NO,CORE) values ('s001','c002',82.9);
insert into HAND_STUDENT_CORE(STUDENT_NO,COURSE_NO,CORE) values ('s002','c002',72.9);
insert into HAND_STUDENT_CORE(STUDENT_NO,COURSE_NO,CORE) values ('s003','c002',81.9);
insert into HAND_STUDENT_CORE(STUDENT_NO,COURSE_NO,CORE) values ('s001','c003','59');
commit;

问题

1、查询选修“谌燕”老师所授课程的学生中每科成绩最高的学生，显示（学号、姓名、课程名称、成绩）

SELECT st1.STUDENT_NO,st2.STUDENT_NAME,st1.COURSE_NAME,st1.CORE
FROM(
	SELECT STUDENT_NO,COURSE_NAME,CORE
	FROM(
		SELECT sc1.COURSE_NO,sc1.CORE,sc2.STUDENT_NO
		FROM( 
			SELECT sc.COURSE_NO,MAX(CORE) CORE
			FROM HAND_STUDENT_CORE sc
			WHERE sc.COURSE_NO IN(
				SELECT co.COURSE_NO
				FROM HAND_TEACHER te,HAND_COURSE co
				WHERE te.TEACHER_NAME='谌燕' and te.TEACHER_NO=co.TEACHER_NO)	
		GROUP BY sc.COURSE_NO)AS sc1 ,HAND_STUDENT_CORE AS sc2
		WHERE sc1.COURSE_NO = sc2.COURSE_NO AND sc1.CORE = sc2.CORE) as co1,HAND_COURSE as co2
	WHERE co1.COURSE_NO=co2.COURSE_NO) as st1,HAND_STUDENT as st2
WHERE st1.STUDENT_NO=st2.STUDENT_NO

2、查询两门以上不及格课程的同学及平均成绩，显示（学号、姓名、平均成绩（保留两位小数））

SELECT st1.STUDENT_NO,st2.STUDENT_NAME,st1.avge
FROM(
	SELECT sc2.STUDENT_NO,ROUND(AVG(CORE),2) AS avge
	FROM(
		SELECT sc.STUDENT_NO
		FROM HAND_STUDENT_CORE AS sc
		WHERE sc.CORE<60
		GROUP BY sc.STUDENT_NO 
		HAVING COUNT(*)>1) AS sc1,HAND_STUDENT_CORE AS sc2
	WHERE sc1.STUDENT_NO=sc2.STUDENT_NO
	GROUP BY sc2.STUDENT_NO) AS st1,HAND_STUDENT AS st2
WHERE st1.STUDENT_NO=st2.STUDENT_NO

3、查询学生s001 对应的成绩+教师编号+课程名称+课程编号

SELECT co1.CORE,co2.TEACHER_NO,co2.COURSE_NAME,co1.COURSE_NO
FROM( 
	SELECT sc.CORE,sc.COURSE_NO
	FROM HAND_STUDENT_CORE AS sc
	WHERE sc.STUDENT_NO='s001') AS co1,HAND_COURSE AS co2
WHERE co1.COURSE_NO=co2.COURSE_NO

4、查出以下列数据,学生姓名,课程,分数,是否及格(60分算及格,值显示为是否)

SELECT st1.STUDENT_NO,st2.STUDENT_NAME,st1.CORE,st1.`是否及格`
FROM( 
	SELECT co1.STUDENT_NO,co2.COURSE_NAME,co1.CORE,co1.`是否及格`
	FROM(	
		SELECT sc.STUDENT_NO,sc.COURSE_NO,sc.CORE,
		CASE WHEN CORE<60 THEN '不及格' 
			WHEN CORE>=60 THEN '及格' END AS '是否及格'
		FROM HAND_STUDENT_CORE sc) AS co1,HAND_COURSE AS co2
	WHERE co1.COURSE_NO=co2.COURSE_NO) AS st1,HAND_STUDENT AS st2
WHERE st1.STUDENT_NO=st2.STUDENT_NO

5、找出学习J2SE课程的学生,分数由高到低进行排名,展示的列: 学生姓名|分数

SELECT st2.STUDENT_NAME,st1.CORE
FROM(
	SELECT sc2.STUDENT_NO,sc2.CORE
	FROM(
		SELECT COURSE_NO
		FROM HAND_COURSE
		WHERE COURSE_NAME='J2SE') AS sc1,HAND_STUDENT_CORE AS sc2
	WHERE sc1.COURSE_NO=sc2.COURSE_NO) AS st1,HAND_STUDENT AS st2
WHERE st1.STUDENT_NO=st2.STUDENT_NO
ORDER BY st1.CORE DESC

6、有多少学生选择了’谌燕’老师的课,展示的列: 学生姓名|课程名

SELECT st2.STUDENT_NAME,st1.COURSE_NAME
FROM(
	SELECT sc2.STUDENT_NO,sc1.COURSE_NAME
	FROM(
		SELECT co2.COURSE_NO,co2.COURSE_NAME
		FROM(
			SELECT TEACHER_NO
			FROM HAND_TEACHER
			WHERE TEACHER_NAME='谌燕') AS co1,HAND_COURSE AS co2
		WHERE co1.TEACHER_NO=co2.TEACHER_NO) AS sc1,HAND_STUDENT_CORE AS sc2
	WHERE sc1.COURSE_NO=sc2.COURSE_NO) AS st1,HAND_STUDENT AS st2
WHERE st1.STUDENT_NO=st2.STUDENT_NO

7、找出选修课程为两门课的学生姓名

SELECT st2.STUDENT_NAME
FROM(
	SELECT STUDENT_NO
	FROM HAND_STUDENT_CORE
	GROUP BY STUDENT_NO
	HAVING COUNT(COURSE_NO)=2) AS st1,HAND_STUDENT AS st2
WHERE st1.STUDENT_NO=st2.STUDENT_NO