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;
-- 分组，使用 order 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不记录事物日志
|