MYSQL 数据库的简单、连接、嵌套查询的简单操作（分组汇总，单列多值，多列多值）

た入场券 2022-05-25 03:38 129阅读 0赞

use xia12;
    CREATE TABLE IF NOT EXISTS course_info( stu_id INT NOT NULL,
    course_id char(3) NOT NULL,
    grade tinyint,
    course_name varchar(20)NOT NULL,
    foreign key(stu_id) references stu_info(stu_id),
    /*foreign key(course_id) references course_info(course_id),*/
    PRIMARY KEY(stu_id)
    )ENGINE=InnoDB;
    INSERT INTO course_info(stu_id,course_id,grade,course_name) 
    VALUES(111545234,701,80,"操作系统");
    INSERT INTO course_info(stu_id,course_id,grade,course_name) 
    VALUES(111545235,701,30,"计算机网络");
    INSERT INTO course_info(stu_id,course_id,grade,course_name) 
    VALUES(111545236,701,90,"计算机基础");
    INSERT INTO course_info(stu_id,course_id,grade,course_name) 
    VALUES(111545237,701,96,"计算机网络");
    INSERT INTO course_info(stu_id,course_id,grade,course_name) 
    VALUES(111545238,705,89,"计算机基础");

//以上是course\_id代码，先创建表，然后注释  
use xia12;/\*创建数据表\*/CREATE TABLE IF NOT EXISTS stu\_info(Stu\_id INT NOT NULL PRIMARY KEY,stu\_name VARCHAR(40) NOT NULL,age TINYINT UNSIGNED ,birthday timestamp NOT NULL DEFAULT CURRENT\_TIMESTAMP,sex CHAR(2) DEFAULT '男' CHECK(sex='男' OR sex='女'),address CHAR(30),mark smallint,major CHAR(10),grade FLOAT(5,2),sdept VARCHAR(10))ENGINE=InnoDB;/\*插入表数据,插入完之后，请注释掉，以免出现主键重复键入的错误\*//\*INSERT INTO stu\_info(Stu\_id,stu\_name,age,birthday,sex,address,mark,major,grade,sdept) VALUES(111545234,"张雨",20,'1998-05-28',"男","江苏徐州",543,"C++",89.90,"信息学院");INSERT INTO stu\_info(Stu\_id,stu\_name,age,birthday,sex,address,mark,major,grade,sdept) VALUES( 111545235,"何玉",24,'1994-06-27',"女","江苏宿迁",643,"MySql",79.90,"商学院");INSERT INTO stu\_info(Stu\_id,stu\_name,age,birthday,sex,address,mark,major,grade,sdept) VALUES( 111545236,"王江",19,'1999-11-18',"男","江苏扬州",47,"PHP",59.60,"信息学院");INSERT INTO stu\_info(Stu\_id,stu\_name,age,birthday,sex,address,mark,major,grade,sdept) VALUES( 111545237,"张帆",30,'1988-03-08',"女","江苏苏州",443,"Python",89.90,"电信学院");INSERT INTO stu\_info(Stu\_id,stu\_name,age,birthday,sex,address,mark,major,grade,sdept) VALUES( 111545238,"梦凡",23,'1995-06-28',"男","江苏盐城",333,"PHP",95.90,"商学院");INSERT INTO stu\_info(Stu\_id,stu\_name,age,birthday,sex,address,mark,major,grade,sdept) VALUES( 111545239,"刘留",20,'1998-05-28',"女","江苏南京",463,"JS",78.60,"信息学院");\*/alter table stu\_info modify birthday date NULL;/\*修改列的属性\*/select \*FROM stu\_info;/\*查询所有列，\*是通配符\*//\*查询指定列\*/SELECT stu\_id,stu\_name,sex,age FROM stu\_info;/\*SELECT stu\_id,stu\_name,YEAR(GETDATE())-YEAR(birthday) FROM stu\_info;存在问题getdate（）系统函数找不到\*/SELECT age AS'年龄'FROM stu\_info;/\*改变列标题显示 别名\*/SELECT DISTINCT sdept FROM stu\_info;/\*distinct删除重复行，去除结果重复的信息\*/SELECT stu\_id,stu\_name,mark FROM stu\_info WHERE 1=1 limit 3;/\*返回查询的部分数据，不同的数据库，有不同的方法\*/SELECT stu\_id,stu\_name,birthday,sex FROM stu\_info WHERE mark>500 AND sex='男'; /\*使用逻辑表达式选择行\*/SELECT stu\_id,stu\_name,birthday,sex FROM stu\_info WHERE ( mark>500 OR sex='男') AND sdept="商学院"; SELECT \* FROM stu\_info WHERE address IS NULL;/\*NULL 空值判断\*/SELECT \* FROM stu\_info WHERE mark BETWEEN 500 AND 650;/\*限定数据范围\*/SELECT \* FROM stu\_info WHERE sdept IN ('信息学院','商学院');/\*限制检索数据的范围 离散数据范围IN关键字\*/SELECT \* FROM stu\_info WHERE stu\_name LIKE '张%';/\*模糊查询 查询所有姓张的数据，NOT LIKE实现排除查询\*/SELECT \* FROM stu\_info WHERE stu\_name LIKE '张\_';/\*查询所有姓张的数据且姓名是两个数字\*//\*SELECT \* FROM stu\_info WHERE stu\_name LIKE '\[张王\]%';/\* 通配符在其中可能存在问题 存在bug\*//\*SELECT \* FROM stu\_info WHERE stu\_name LIKE '^\[张王\]';/\*存在bug\*/SELECT \* FROM stu\_info WHERE sex="男"ORDER BY mark DESC,Stu\_id;/\*排序查找结果 ,Stu\_id列名相同可以根据学号来排\*/ SELECT COUNT(\*) AS '学生人数' FROM stu\_info;/\*聚合函数 统计学生人数\*//\*分组筛选 加入where语句\*/SELECT stu\_id,MAX(grade),MIN(grade),AVG(grade),SUM(grade) FROM stu\_info WHERE sex="男" GROUP BY stu\_id HAVING AVG(grade)>80 ;/\*使用WHERE语句实现的连接，查出课程编号为701的同学学号成绩姓名\*/SELECT stu\_info.stu\_id,stu\_name,stu\_info.grade FROM stu\_info,course\_info WHERE stu\_info.stu\_id=course\_info.stu\_id AND course\_id="701";/\*连接查询分组筛选\*/SELECT stu\_info.stu\_id,stu\_name,stu\_info.grade FROM stu\_info,course\_info WHERE stu\_info.stu\_id=course\_info.stu\_id GROUP BY stu\_id HAVING grade>70;/\*单列单值嵌套查询\*/SELECT \* FROM stu\_info where birthday<(select birthday from stu\_info where stu\_id=111545235);/\*单列多值嵌套查询 IN\*/SELECT \* FROM course\_info WHERE stu\_id IN(SELECT stu\_id FROM stu\_info WHERE sdept="信息学院");/\*单列多只的转换连接\*/SELECT \* FROM stu\_info,course\_info WHERE stu\_info.stu\_id=course\_info.stu\_id AND sdept="信息学院";/\*单列多值嵌套查询 ALL ANY\*/SELECT stu\_id,stu\_name FROM stu\_info WHERE mark<ANY(SELECT mark FROM stu\_info WHERE sdept="电信学院");