MySQL数据库查询操作

MySQL数据库基本的SQL语句（使用学生表作为示例）

-- 查看表格（所有列）数据
select * from Student; 

-- 新增指定数据
insert into stu values(null,'赵六','女','2021-09-28',99);

-- 查询符合条件的数据
select * from stu where gender = '男';
SeLEct * from stu where score > 80;  -- select等关键字不区分大小写

-- 创建数据库
create database TeachDB;
create database TeachDB if not exists TeachDB;
-- 若数据库名不符合标识符规则时，可以使用反引号
create schema `123`;
create database `my database`;
-- 创建时可选
create database TeachDB;
character set 'utf8mb4'
collate 'utf8mb4_0900_ai-ci';

-- 查看创建数据库的代码
show creat database TeachDB;

-- 查看所有的数据库
show database;

-- 删除数据库
drop database `123`;

-- 修改数据库
alter database TeachDB
character set utf8;

-- 选择数据库
use TeachDB;

-- 枚举类型，关键字：enum（仅能选一个）；集合类型，关键字：set（可选多个，用逗号隔开）
Ssex enum('男'，'女')default'男'

-- 设定范围
Grade int check(Grade >= 0 and Grade <= 100)
Grade int check(between 0 and 100)

-- 创建一个与已有表结构相同的表
create table Student1 like Student;
-- 创建一个与已有表结构和数据都相同的表
create table Student2 as select * from Student;

-- 为表添加一个字段（列）
alter table Studet add Stel chat(11);

-- 为表删除一个字段（列）
alter table Student drop column Stel;

-- 查看表的建表命令
show cteate table Student;

-- 查看表的结构
desc Student;

-- 查询所有学生的学号和姓名（对应关系代数中的投影运算）
select Sno,Sname from Student;

-- 为查询结果列取别名
-- 字段名 as 别名 （as可不写）
select Sno as 学号,Sname 姓名 from Student;
-- 查询学生的学号和出生年份
select Sno,year(now()) - Sage 出生年份 from Student; -- 此处“出生年份”为别名

-- 查询选修了课程的学生的学号(使用distinct来去除重复的元组)
select Sno from SC;
select distinct Sno from SC;

-- 查询cs系所有学生的姓名
select Sname from Student where Sdept = 'cs'; -- 不区分大小写
select Sname from Student where binary Sdept = 'cs'; -- 区分大小写

-- 查询MA系和cs系的学生的学号和姓名
select Sno,Sname from Student where Sdept = 'ma' or Sdept = 'cs';
/*或者*/ select Sno,Sname from Student where Sdept in('ma','cs');

-- 查询没有选修课的课程，使用关键字 is null(不为空使用 is not null)
select * from Course where Cpno is null;

/*模糊查询
（1）使用like
%表示0个或多个任意的字符；_表示1个任意的字符
（2）使用regexp
^ab 表示以ab开头的字符串；
ab$ 表示以ab结尾的字符串；
[abc] 表示abc中的任何一个字符串；
[^abc] 表示不包含a或b或c的任何一个字符；
a? 表示0个或1个字符a。

查询所有姓雷的学生 */
select * from Student where Sname like '雷%';
/*或者*/select * from Student where Sname regexp '^雷';

-- 查询所有不姓雷的学生
select * from Student where Sname not like '雷%';
/*或者*/select * from Student where Sname regexp '^[^雷]';

-- 查询所有姓雷且全名只有两个字的学生
select * from Studet where Snmae like '雷_'; -- 一个汉字用一个下划线来匹配

-- 指定返回结果的行数
select * from Student limit 3;
select * from student limit 0,3;
select * from student limit 1,3; -- 从第2条记录开始，取3条记录

 -- 使用order by进行排序，asc：升序（默认）、desc：降序
 select * from SC order by Grade;
 select * from SC order by Grade desc;
 select * from SC order by 3 desc; -- 用数字代替字段，3表示第三列
 -- 先按照Sdept升序排列，然后Sage降序排列
 select * from Student order by Sdept asc,Sage desc;
 
 /*聚合函数
（1）count([all | distinct]字段名)
 all：默认的，重复值会累计; distinct：重复值不会累计 */
 select count(Sno) from Student;
 select count(all Ssec) from Student;
 select count(distinct Ssec) from Student;
 select count(*) from Student; -- Student表中元组的个数（行数）
 
 -- (2)avg
 -- 查询2号课的平均分
 select avg(Grade)from SC where Cno = '2';
 -- 将平均成绩保留1位小数
 select cast(avg(Grade) as decimal(3,1)) from SC where Cno = '2';
 
 -- (3)max
 -- 查询1号课的最高分
 select max(Grade) from SC where Cno = '1';
 select Grade from SC where Cno = '1' order by Grade desc limit 1;
 
 -- 分组，使用 group by
 -- 查询课程号及该课选课人数
 select Cno,count(*) from SC group by Cno;
 -- 查询学号及该生的选课门数
 select Sno,count(*) from SC group by Sno;
 -- 查询课程号及该课选课人数（小于2的）
 select Cno,count(*) from SC group by Cno having count(*) < 2; -- 只能用having，不能用where
 
 /* where子句与having子句的区别：
where对整个表中的数据做筛选；where子句后面啊不可以接聚合函数
having对分组的结果做筛选；having子句后面可以接聚合函数
having仅在有group by的情况下才能使用 */

-- eg:
select Ssex, count(*)from Student where Sdept = 'is' group by Ssex having count(*) > 1;
-- step1: 执行where子句，对Student表中的数据做筛选（满足条件Sdept='is'）
-- step2：执行group by 子句，按Ssex进行分组，分别统计男生和女生的人数；
-- step3：执行having子句，对分组的结果做筛选（满足条件count(*) > 1)

-- 查询每门课的成绩都 >= 85 的学生的学号
select Sno from SC group by Sno having min(Grade) >= 85;

-- 查询2号课成绩最高的选课信息
select * from SC where Cno = '2' and Grade = (select max(Grade) from SC where Cno = '2')
/*不能*/ select * from SC where Cno = '2' and Grade = max(Grade); -- where后面不能接聚合函数

-- 连接查询
-- 一、笛卡尔积
select * from Student; select * from SC;
select Student.*,SC.* from Student,SC

-- 二、等值连接
select Student.*,SC.* from Student,SC where Student.Sno = SC.Sno;

-- 三、自然连接
-- 方法1
select Student.*,Cno,Grade from Student,SC where Student.Sno = SC.Sno;
-- 方法2
select * from Student natural join SC;

-- 查询学生的学号，姓名，课程号和成绩
select Student.Sno,Sname,Cno,Grade from Student,SC where Student.Sno = SC.Sno;

-- 查询学生的学号，姓名，课程名和成绩
select Student.Sno,Sname,Cname,Grade from Student,Course,SC where Student.Sno = SC.Sno and Course.Cno = SC.Cno;

-- 查询cs系所有学生的学号，姓名，课程号和成绩
select Studetn.Sno,Sname,Cno,Grade from Student,SC where Student.Sno = SC.Sno and Sdept = 'cs';

-- 四、自身连接（为表取两个不同的别名）
-- (1)为了简化表名
select A.Sno,Sname,Cname,Grade from Student A,course B,SC where A.Sno = SC.Sno and B.Cno = SC.Cno;

-- (2)自身连接，一定要为表取连个不同的别名
-- 查询每门课的课程号及间接选修课号
select A.Cno,B.Cpno from Course A,Course B where A.Cpno = B.Cno;

-- 五、内连接，外连接
-- (1)内连接 inner join
-- 查询选了课的学生的学号，姓名，课程号和成绩 (以下两种方法)
select Student.Sno,Sname,Cno,Grade from Student,SC where Student.Sno = SC.Sno;
select Student.Sno,Sname,Cno,Grade from Student inner join SC on Student.Sno = SC.Sno;

-- (2)左外连接 left [outer] join
-- (3)右外连接 right [outer] join
select Student.Sno,Sname,Cno,Grade from Student left outer join SC on Student.Sno = SC.Sno;
select Studetn.Sno,Sname,Cno,Grade from SC right join Student on Student.Sno = SC.Sno;

-- 嵌套查询
-- 查询与张三在同一个系的学生
select Sname from Student where Sdept = (select Sdept from Student where Sname = '张三');

-- 查询选修了1号课的学生的姓名
select Sname from Student, SC where Cno = '1' and Student.Sno = SC.Sno;
-- 或者
select Sname from Student where Sno in (select Sno from SC where Cno = '1');
-- 注意:当子查询返回的结果有多行时，要用in，不能用=

select Sname from Student where Sno = any(select Sno from SC where Cno = '1');
-- eg: 查询成绩比2021002的所有成绩都要高的选课记录
select * from SC where Grade > all (select Grade from SC where Sno = '2021002');
-- 或者
select * from SC where Grade > (select max(Grade) from SC where Sno = '2021002');
/*
>any：大于子查询结果中的某个值
>all：大于子查询结果中的所有值 */

-- 查询没有选修1号课的学生的姓名
select Sname from Student where Sno not in (select Sno from SC where Cno = '1');

-- 查询选修了“DB"这门课的学生学学号和姓名
select Student.Sno, Sname from Student, Course, SC 
where Cname = 'DB' and Student.Sno = SC.Sno and Course.Cno = SC.Cno;
-- 或者
select Student.Sno, Sname from Student 
where Sno in (select Sno from SC where Cno = (select Cno from Course where Cname = 'db'));

/* Exists
select .. from .. where exists(select .. from .. where....) */
-- 查询选修了1号课的学生的姓名
-- 分析：某个同学选修了1号课，说明，在SC表中，存在这样的记录：SC表中的学号与该生的学号相等，并且Cno等于1
select Sname from Student where exists(select * from SC where SC.Sno = Student.Sno and Cno = '1');

-- eg: 查询没有选修了1号课的学生的姓名
select Sname from Student where not exists(select * from SC where SC.Sno = Student.Sno and Cno = '1');

-- eg：查询成绩比该课的平均成绩要低的学生的选课信息
-- 以下写法是错误的
select * from SC where Grade < (select avg(Grade) from SC);
-- 应该这么写
select * from SC A where Grade < (select avg(Grade) from SC B where B.Cno = A.Cno);

/* 集合查询
并：union（去掉重复元组），union all（不去除重复元组）
交：SQL Server或Oracle中，使用intersect
差：SQL Server中，使用except；Oracle使用minus */

-- 查询cs系或is系学生的信息
select * from Student where Sdept = 'sc' union select * from Student where Sdept = 'se';

-- 将查询结果插入到表中
-- （1）当表事先存在时 insert into 表 select ... from ... where ...;
-- （2）当表事先不存在时 creat table 表 [as] select ... from ... where ...;

-- 将从SC表中查询到的每个学生的学号及平均成绩插入到平均成绩表中
insert into avgTable select Sno,round(avg(Grade)) from SC group by Sno;
-- 如果该表事先不存在
create table avgTable select Sno,round(avg(Grade)) avgGrade from SC group by Sno;

-- 修改数据
-- 将2021001的年龄改为38岁，所在系改为PE
update Student set Sage = 38,Sdept = 'pe' where Sno = '2021001';

-- 将cs系所有学生的成绩减5分
update SC set Grade = Grade - 5 where Sno in (select Sno from Student where Sdept = 'cs')

-- 删除表中所有数据
delete from 表名
truncate table 表名 -- truncate不记录事物日志