mysql语句练习(基本)(这些已经就够用了,想多接触的就看看复杂的)
create database jiangsu; #新建一个名为jiangsu的数据库 use jiangsu; #使用该数据库 create table location(Region char(20),Store_name char(20)); #创建location表,字段1为Region,数据类型为char,数据长度为20;字段2为Store_name,数据类型为char,长度为20. insert into location values('North','Xuzhou'); #插入4条数据 insert into location values('North','Suqian'); insert into location values('South','Nanjing'); insert into location values('South','Suzhou'); create table store_info(Store_name char(20),Sales int(10),Date char(10)); #再创建一张store_info表 insert into store_info values('Xuzhou',300,'2020-12-08'); #插入4条数据 insert into store_info values('Suqian',249,'2020-12-07'); insert into store_info values('Nanjing',1500,'2020-12-05'); insert into store_info values('Suzhou',700,'2020-12-08');
1、基本查询
----------------select------------------- select用于查询表格中的一个或多个字段的数据记录 语法格式:select 字段1,字段2,... from 表名; select * from location; #查询location表中的所有字段,*表示所有,如果不嫌麻烦,当然也可以将所有字段都输进去 select Region from location; #查询location表中的Region的数据 select sales,Date from store_info; #查询store_info表中的sales,Date字段的数据 ----------------DISTINCT------------------- DISTINCT用于将重复的数据压缩为一个 语法格式:select distinct 字段名 from 表名; select distinct Store_name from store_info; #查询dtore_info表中的Store_name字段的数据,如有重复,则只显示一个 ----------------where------------------- where用于带条件查询 语法格式:select 字段名 from 表名 where 条件语句; select Store_name from store_info where sales=300; #查询store_info表中sales字段的值等于300的Store_name的数据 select Store_name from store_info where sales>500; #查询store_info表中sales字段的值大于500的Store_name的数据 ----------------and or------------------- and,且,用于查询一个数据范围;or,或,用于查询包含条件语句的所有数据 语法格式:select 字段名 from 表名 where 条件1 and/or 条件2; select Store_name from store_info where sales>250 and sales<1000; #查询store_info表中sales字段的值大于250,且小于1000的Store_name的数据 select Store_name from store_info where sales<250 or sales>1000; #查询store_info表中sales字段的值小于250,或者 大于1000的Store_name的数据 select Store_name from store_info where sales>1000 or (sales >200 and sales < 500); #括号的优先级高,所以先根据括号里的and条件语句进行筛选,然后再根据or进行筛选,最后查询最终筛选出来的数据;该语句先筛选出sales大于200且小于500的值,再使用or进行或的删选,最终筛选出来的结果应该是在200到500之间的值或者大于1000的值。 select Store_name from store_info where sales>1000 or sales >200 and sales < 500; #如果不加括号,and的优先级是比or要高的,也就是说,当一条条件语句中同时存在and和or(没有括号),会先执行and条件。 ----------------in------------------- in用来显示已知值的数据,简单来说,in后面跟的是一个数据集合,查询语句会根据数据集合中的值进行筛选查询。not in 就是取数据集合中的反,不在数据集合中的数据。 语法格式:select 字段名1 from 表名 where 字段名2 in ('字段名2的值1','字段名2的值2,......'); select * from store_info where Store_name in ('Nanjing','Xuzhou'); #将Nanjing和Xuzhou的所有信息都查询出来。 注:in可以用or代替 上述语句等于:select * from store_info where Store_name='Nanjing' or Store_name='Xuzhou'; ----------------between...and------------------- between 值1 and 值2 ,在值1与值2之间(值2 > 值1),该语句查询的是一个范围,包含值1和值2。其作用相在某一方面当于大于等于 ... and 小于等于 ... 。 语法格式:select 字段名 from 表名 where 字段名 between 值1 and 值2; select * from store_info where Date between '2020-12-07' and '2020-12-10'; #查询store_info表中的Data的值在12-06与12-10之间的所有数据 ----------------通配符------------------- 通配符一般情况下和like一起使用进行模糊查询,模糊查询的概念就是将所有符合条件的数据全部查询出来,而等于号是精确查询,会直接将具体的某一数据查询出来 模糊查询的字符如下: %:百分号表示0个,1个或多个字符 _:下划线表示单个字符 语法格式:select 字段 from 表名 where 字段 like '通配符'; select * from store_info where Date like '2020%'; #将Date的值为2020,后面随便(2020后有没有都行)的值全部查询出来 select * from store_info where Date like '2020-12-0_'; #将2020-12-0,后面只能匹配一个字符(必须存在且只能有一个)的所有数据查询出来 ----------------like------------------- like,模糊查询,用于查询符合条件的所有数据,通常和通配符一起使用,语法和通配符一样的,因为是结合使用。 create database name; use name; create table stu_name(sname char(10)); insert into stu_name values('张'); insert into stu_name values('张三'); insert into stu_name values('张四'); insert into stu_name values('张无忌'); insert into stu_name values('一张纸'); insert into stu_name values('弓长张'); select * from stu_name where sname like '张%'; #查询所有张姓的名字,只要姓张就行 select * from stu_name where sname like '%张'; #查询所有最后一个字是张的姓名,前面无所谓 select * from stu_name where sname like '%张%'; #查询所有包含张的姓名,张字在姓在名都行 select * from stu_name where sname like '张_'; #查询所有张姓且只有两个字的名字 select * from stu_name where sname like '张__';(两条下划线) #查询所有张姓,且必须为三个字的名字 select * from stu_name where sname like '_张%'; #查询所有第二个字为张的名字 select * from stu_name where sname like '张_%'; #查询所有张姓,名字至少包含两个字的名字 select * from stu_name where sname like '张%_'; #查询所有张姓,名字至少包含两个字的名字,该语句和上面的查询结果一样,但理解是不同的。 ----------------order by------------------- order by 用于关键字的排序 语法格式:select 字段 from 表名 [where 条件语句] order by 字段 asc/desc; asc:按字段升序,默认为asc desc:按字段降序 select Store_name,Date,sales from store_info order by sales; #按照sales升序排列后,查询name、date和sales select Store_name,Date,sales from store_info order by sales desc; #按照sales降序排列后,查询name、date和sales
2、函数查询
-------------数学函数------------------- abs(x) #返回x的绝对值 rand() #返回0到1之间的随机数 mod(x,y) #返回x除以y的余数 power(x,y) #返回x的y次方 round(x) #返回离x最近的整数 round(x,y) #保留x的y位小数四舍五入之后的值 sqrt(x) #返回x的平方根 truncate(x,y) #返回x截断为y位小数的值 ceil(x) #返回大于或等于x的最小整数 floor(x) #返回小于或等于x的最大整数 greatest(x,y,z,...) #返回集合中最大的值 least(x,y,z,...) #返回集合中最小的值 --------------------------------------------- select abs(-1),rand(),mod(5,2),power(2,3),round(1.75); select round(3.1415926,5),sqrt(2),truncate(3.141592653,4),ceil(5.2),floor(3.2),greatest(1.61,2.54,0.87),least(5.23,8.71,4.13); --------------聚合函数-------------------- avg() #返回指定列的平均值 count() #返回指定列中非空值的个数 min() #返回指定列的最小值 max() #返回指定列的最大值 sum(x) #返回指定列的所有值的和 ------------------------------------------ select avg(sales) from store_info; #查询sales的平均值 平均值的另一种方法: select sum(sales)/(select count(sales) from store_info) from store_info; select count(Date) from store_info; #统计Date的数据个数,包括重复的值,但不包括空值 select count(distinct Date) from store_info; #统计Date的数据个数,重复的数据只统计一次,不包括空值 select count(*) from store_info; #全部统计,包括空值,count(*)扫描全表 select min(sales) from store_info; #查询sales的最小值 最小值的另一种方法: select sales from store_info order by sales limit 1; select max(sales) from store_info; #查询sales的最大值 最大值的另一种方法: select sales from store_info order by sales desc limit 1; select sum(sales) from store_info; #查询sales的和 -----------------字符串函数-------------------- trim() #返回去除指定格式的值 concat(x,y) #将提供的参数x和y拼接成一个字符串 substr(x,y) #获取字符串x中第y个位置开始的字符串,跟substring()函数作用相同 substr(x,y,z) #获取字符串x中第y个位置开始,长度为z的字符串 length(x) #返回字符串x的长度 replace(x,y,z) #将字符串z替代字符串x中的字符串y upper(x) #将字符串x中的所有字母变成大写 lower(x) #将字符串x中的所有字母变成小写 left(x,y) #返回字符串x中的前y个字符 right(x,y) #返回字符串x中的后y个字符 repeat(x,y) #将字符串x重复y次 space(x) #返回x个空格 strcmp(x,y) #比较x和y,返回的值可以为-1,0,1 reverse(x) #将字符串x反转 select concat(Region,Store_name) from location where Store_name='Xuzhou'; #将location表中Store_name='Xuzhou'的Region,Store_name的值拼接在一起 select Region || ' ' || Store_name from location where Store_name='Xuzhou'; #在my.cnf中开启了PIPES_AS_CONCAT模式后,可以使用 || 代替concat函数,将多个字符串拼接在一起 select substr(Store_name,3) from store_info where Store_name='Suqian'; #将Suqian的第3个字符往后的所有字符截取出来 select substr((select Region || ' ' || Store_name from location where Store_name='Xuzhou'),1,5); #将上一条拼接的字段的第1到5个字符截取出来 select trim([[位置] [要除移的字符串] from] 字符串); #位置:leading(开头),tariling(结尾),both(开头及结尾) #要除移的字符串:从字符串的开头、结尾,或开头及结尾除移的字符串,缺省时为空格 select trim(leading 'Xu' from 'Xuzhou'); #将Xuzhou开头的Xu去掉 select Region,length(Store_name) from location; #查询location表中的Region字段的值和Store_name的值的长度 select replace(Region,'th','thern') from location; #将location表中的Region字段的值包含th的替换为thern,然后返回。 ------------------group by------------------- group by用于对查询结果进行汇总分组,通常是结合聚合函数 一起使用,group by有一个原则,凡是在group by后面出现的 字段,必须在select后面出现。凡是在select后面出现、且未在 group by后面出现的字段,必须出现在group by后面。 语法格式:select 字段1,sum(字段2) from 表名 group by 字段1; select Store_name,sum(sales) from store_info group by Store_name order by sum(sales); ------------------having---------------------- having用来过滤由group by语句返回的记录值,通常与group by语句联合使用。having语句的存在弥补了where关键字不能与聚合函数联合使用的不足。 语法格式:select 字段1,sum(字段2) from 表名 group by 字段1 having (函数条件); select Store_name,sum(sales) from store_info group by Store_name having sum(sales) >1000; -------------------别名---------------------- 别名包括字段别名和表的别名,当一张表的名字或者表中的某一个字段名过于冗长时,可以使用别名将之代替,从而降低查询的失误率。 语法格式:select 字段 [AS] 字段别名 from 表名 [AS] 表格别名; select Store_name NAME,sales SALE from store_info; ------------------子查询--------------------- 子查询通常用于连接表格,在where子句或者having子句中插入另一个SQL语句。 语法格式:select 字段1 from 表格1 where 字段2 [比较运算符] (select 字段2 from 表格2 where 条件语句);括号里的select语句是内查询,括号外的select语句是外查询 select Region from location where Store_name=(select Store_name from store_info where sales=300); #比较运算符,可以是=、>、<或者>=、<= select sales from store_info where Store_name in (select Store_name from location); #也可以是in、between...and、like等
3、exists
------------------exists--------------------- exists用来测试内查询有没有产生任何结果,类似布尔值 是否为真。如果内查询产生了结果,则将结果作为外查询 的条件继续执行,如果没有结果,那么整条语句都不会产 生结果。 语法格式:select 字段1 from 表1 where exists (select 字段2 from 表2 where 条件语句); select sum(sales) from store_info where exists (select * from location where Region='North'); # #先执行内查询语句,即在location表中查询Region为North的所有数据,如果存在,则执行外查询语句;如果不存在,就不执行外查询 ------------------连接查询----------------------- inner join(内连接):内连接只返回两个表中字段的值相等的数据,即两表的交集 left join(左连接):返回包括左表中的所有记录和右表中联结字段相等的记录 right join(右连接):返回包括右表中的所有记录和左表中联结字段相等的记录 select * from location left join store_info on location.Store_name=store_info.Store_name; #左连接 select * from location right join store_info on location.Store_name=store_info.Store_name; #右连接 select * from location inner join store_info on location.Store_name=store_info.Store_name; #内连接法1 SELECT * FROM location A, store_info B WHERE A.Store_Name = B.Store_Name; #内连接法2 SELECT Region REGION, SUM(B.Sales) SALES FROM location A, store_info B WHERE A.Store_Name = B.Store_Name GROUP BY Region; #查询两表中name值相等的Region的值和sales的和,并按照Region字段进行分组,REGION是字段Region的别名,SALES是sum(sales)的别名,A是表location的别名,B是info表的别名
4、视图
---------------------view----------------------- view,视图,视图是一张虚拟的表,通常用于保存多表联合查询出来数据,这样可以极大的减轻SQL语句的复杂度,在执行n张表的联合查询时,视图可以起到很大的便利作用。视图跟表格的不同是,表格中有实际储存数据记录,而视图是建立在表格之上的一个架构,它本身并不实际储存数据记录。而视图不会因为退出数据库而消失。 语法格式1:create view 视图名 as 查询语句; #创建视图 语法格式2:drop view 视图名; #删除视图 show tables from 库名; #该命令不仅可以查看库所包含的表,也可以查看有哪些视图 ------------------------union------------------------- union,联集,其作用是将两个SQL语句的结果合并起来,两个SQL语句所产生的字段需要是同样的数据记录种类 union :生成结果的数据记录值将没有重复,且按照字段的顺序进行排序 union all :将生成结果的数据记录值都列出来,无论有无重复 语法格式:[select 语句 1] UNION [all] [SELECT 语句 2]; select Store_name from location union select Store_name from store_info; select Store_name from location union all select Store_name from store_info; -------------------求交集的几种方法------------------ 存在重复数据: 1.select A.Store_Name FROM location A inner join store_info B on A.Store_Name = B.Store_Name; 2.select A.Store_name from location A,store_info B where A.Store_name=B.Store_name; 3.select A.Store_name from location A inner join store_info B using (Store_name); 无重复数据: 1.想要得到无重复数据其实很简单,在重复数据的查询语句select后加上distinct去重即可。 select distinct A.Store_Name FROM location A inner join store_info B on A.Store_Name = B.Store_Name; 2.使用子查询,在内查询中查询info中的name字段,并将之作为外查询的条件,这样,外查询的查询语句的范围就只能在内查询查出的数据中进行。(子查询实际上就是变相的内查询) select distinct Store_name from location where Store_name in (select Store_name from store_info); 3.使用左查询,将location和info表进行左查询,会查询出location表中的所有name字段的值以及info表中与location表中name的值相等的数据,再使用distinct进行去重 select distinct A.Store_name from location A left join store_info B using(Store_name) where B.Store_name is not null; 4.使用级联查询,union all会将两张表的所有数据都连接到一起,这时只需要通过count()函数将大于1的数值统计出来,即可实现查询两表的共同数值。 select Store_name,count(A.Store_name) from (select Store_name from location union all select Store_name from store_info) A group by A.Store_name having count(A.Store_name)>1 ; ---------------------无交集--------------------- 既然我们可以查询出有交集的数据,那么取反就可以实现无交集的查询了 select distinct Store_name from location where Store_name not in (select Store_name from store_info);
5、sql判断
---------------------case--------------------- case是SQL语句用来做为when-then-else(当...就...否则...)之类逻辑的关键字 语法格式: select case 字段名 when 条件1 then 结果1 when 条件2 then 条件2 ..... [else 结果n] end from 表名; --------空值(NULL) 和 无值('') 的区别---------- 1.无值的长度为 0,不占用空间的;而 NULL 值的长度是 NULL,是占用空间的。 2.IS NULL 或者 IS NOT NULL,是用来判断字段是不是为 NULL 或者不是 NULL,不能查出是不是无值的。 3.无值的判断使用=''或者<>''来处理。<> 代表不等于。 4.在通过 count()指定字段统计有多少行数时,如果遇到 NULL 值会自动忽略掉,遇到无值会加入到记录中进行计算。 create table city(name char(10)); #新建city表 insert into city values ('beijing'); #插入三个值 insert into city values ('nanjing'); insert into city values ('xuzhou'); insert into city values (''); #插入两个无值 insert into city values (''); insert into city values (null); #插入两个空值 insert into city values (null); select * from city; #查询city表的所有值 select length(name) from city; #查询name字段值的长度 select count(name) from city; #统计name字段的值,空值会被忽略 select length('111'),length(null),length(''); #比较有值、空值、无值的长度 select * from city where name is null; #查询name为空的值 select * from city where name is not null; #查询name字段不为空的值 select * from city where name = ''; #查询name值为无值的数据 select * from city where name <> ''; #查询name字段不为无值的数据,空值会被忽略
6、正则表达式
--------------------正则表达式------------------------- ^:匹配文本的开始字符 $:匹配文本的结束字符 .:匹配任何一个字符 *:匹配零个或多个在它前面的字符 +:匹配前面的字符1次或多次 字符串:匹配包含指定的字符串 p1|p2:匹配p1或p2 [...]:匹配字符集合中的任意一个字符 [^...]:匹配不在括号中的任何字符 {n}:匹配前面的字符串 n 次 {n,m}:匹配前面的字符串至少n次,至多m次 语法格式:select 字段 from 表名 where 字段 REGEXP {模式}; select * from city where name regexp 'zhou'; #匹配数据中带zhou的 select * from city where name regexp 'nan|bei'; #匹配数据中有nan或bei的 select * from city where name regexp '^[xnb]'; #匹配以xnb任一字符开头的数据
mysql语句练习(复杂)
---------创建数据库、表、插入数据----------------------
-- 建表
-- 学生表
-- 创建学生表
CREATE TABLE Student(
s_id VARCHAR(20) PRIMARY KEY COMMENT '学生编号',
s_name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '学生姓名',
s_birth VARCHAR(20) NOT NULL DEFAULT '' COMMENT '出生年月',
s_sex VARCHAR(10) NOT NULL DEFAULT '' COMMENT '学生性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表';
-- 创建课程表
CREATE TABLE Course(
c_id VARCHAR(20) PRIMARY KEY COMMENT '课程编号',
c_name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '课程名称',
t_id VARCHAR(20) NOT NULL DEFAULT '' COMMENT '教师编号', -- 添加 NOT NULL 约束和默认值
FOREIGN KEY (t_id) REFERENCES Teacher(t_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='课程表';
-- 创建教师表
CREATE TABLE Teacher(
t_id VARCHAR(20) PRIMARY KEY COMMENT '教师编号',
t_name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '教师姓名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='教师表';
-- 创建成绩表
CREATE TABLE Score(
s_id VARCHAR(20) COMMENT '学生编号',
c_id VARCHAR(20) COMMENT '课程编号',
s_score INT(3) COMMENT '分数',
PRIMARY KEY(s_id,c_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='成绩表';
-- 插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
-- 插入课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
-- 插入教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
-- 插入成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);
--------------------------------------------------
---------创建数据库、表、插入数据----------------------
-- 建表
-- 学生表
-- 创建学生表
CREATE TABLE Student(
s_id VARCHAR(20) PRIMARY KEY COMMENT '学生编号',
s_name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '学生姓名',
s_birth VARCHAR(20) NOT NULL DEFAULT '' COMMENT '出生年月',
s_sex VARCHAR(10) NOT NULL DEFAULT '' COMMENT '学生性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表';
-- 创建课程表
CREATE TABLE Course(
c_id VARCHAR(20) PRIMARY KEY COMMENT '课程编号',
c_name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '课程名称',
t_id VARCHAR(20) NOT NULL DEFAULT '' COMMENT '教师编号', -- 添加 NOT NULL 约束和默认值
FOREIGN KEY (t_id) REFERENCES Teacher(t_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='课程表';
-- 创建教师表
CREATE TABLE Teacher(
t_id VARCHAR(20) PRIMARY KEY COMMENT '教师编号',
t_name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '教师姓名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='教师表';
-- 创建成绩表
CREATE TABLE Score(
s_id VARCHAR(20) COMMENT '学生编号',
c_id VARCHAR(20) COMMENT '课程编号',
s_score INT(3) COMMENT '分数',
PRIMARY KEY(s_id,c_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='成绩表';
-- 插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
-- 插入课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
-- 插入教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
-- 插入成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);
--------------------------------------------------
1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
```sql
SELECT
Student.s_id,
Student.s_name,
Student.s_birth,
Student.s_sex,
Score01.s_score AS score_01,
Score02.s_score AS score_02
FROM
Student
INNER JOIN Score AS Score01 ON Student.s_id = Score01.s_id AND Score01.c_id = '01'
INNER JOIN Score AS Score02 ON Student.s_id = Score02.s_id AND Score02.c_id = '02'
WHERE
Score01.s_score > Score02.s_score;
```
2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
```sql
SELECT
Student.s_id,
Student.s_name,
Student.s_birth,
Student.s_sex,
Score01.s_score AS score_01,
Score02.s_score AS score_02
FROM
Student
INNER JOIN Score AS Score01 ON Student.s_id = Score01.s_id AND Score01.c_id = '01'
INNER JOIN Score AS Score02 ON Student.s_id = Score02.s_id AND Score02.c_id = '02'
WHERE
Score01.s_score < Score02.s_score;
```
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
```sql
SELECT
Student.s_id,
Student.s_name,
AVG(Score.s_score) AS avg_score
FROM
Student
INNER JOIN Score ON Student.s_id = Score.s_id
GROUP BY
Student.s_id
HAVING
AVG(Score.s_score) >= 60;
```
4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
```sql
SELECT
Student.s_id,
Student.s_name,
IFNULL(AVG(Score.s_score), 0) AS avg_score
FROM
Student
LEFT JOIN Score ON Student.s_id = Score.s_id
GROUP BY
Student.s_id
HAVING
IFNULL(AVG(Score.s_score), 0) < 60;
```
5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩,并从高到低排序
```sql
SELECT
Student.s_id,
Student.s_name,
COUNT(Score.c_id) AS course_count,
SUM(Score.s_score) AS total_score
FROM
Student
LEFT JOIN Score ON Student.s_id = Score.s_id
GROUP BY
Student.s_id
ORDER BY
total_score DESC;
```
6、查询"李"姓老师的数量
```sql
SELECT
COUNT(*) AS li_teacher_count
FROM
Teacher
WHERE
t_name LIKE '李%';
```
7、查询学过"张三"老师授课的同学的信息
```sql
SELECT
DISTINCT Student.s_id,
Student.s_name,
Student.s_birth,
Student.s_sex
FROM
Teacher
INNER JOIN Course ON Teacher.t_id = Course.t_id AND Teacher.t_name = '张三'
INNER JOIN Score ON Course.c_id = Score.c_id
INNER JOIN Student ON Score.s_id = Student.s_id;
```
8. 查询没学过"张三"老师授课的同学的信息。
```sql
SELECT *
FROM Student
WHERE s_id NOT IN (
SELECT Score.s_id
FROM Score
JOIN Course ON Score.c_id = Course.c_id
JOIN Teacher ON Course.t_id = Teacher.t_id
WHERE Teacher.t_name = '张三'
);
```
9. 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息,及两门课程成绩。
```sql
SELECT Student.*, Score01.s_score AS score01, Score02.s_score AS score02
FROM Student
JOIN Score AS Score01 ON Student.s_id = Score01.s_id AND Score01.c_id = '01'
JOIN Score AS Score02 ON Student.s_id = Score02.s_id AND Score02.c_id = '02';
```
10. 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息。
```sql
SELECT *
FROM Student
WHERE s_id IN (
SELECT s_id
FROM Score
WHERE c_id = '01'
)
AND s_id NOT IN (
SELECT s_id
FROM Score
WHERE c_id = '02'
);
```
11. 查询没有学全所有课程的同学的信息。
```sql
SELECT *
FROM Student
WHERE s_id NOT IN (
SELECT s_id
FROM Score
GROUP BY s_id
HAVING COUNT(DISTINCT c_id) = (SELECT COUNT(*) FROM Course)
);
```
12. 查询至少有一门课与学号为"01"的同学所学相同的同学的信息。
```sql
SELECT DISTINCT Student.*
FROM Student
JOIN Score ON Student.s_id = Score.s_id
WHERE Score.c_id IN (
SELECT c_id
FROM Score
WHERE s_id = '01'
)
AND Student.s_id != '01';
```
13. 查询和"01"号的同学学习的课程完全相同的其他同学的信息。
```sql
SELECT DISTINCT s1.*
FROM Student s1
JOIN Score sc1 ON s1.s_id = sc1.s_id
WHERE sc1.c_id IN (
SELECT sc2.c_id
FROM Score sc2
WHERE sc2.s_id = '01'
)
AND s1.s_id NOT IN (
SELECT s_id
FROM Score
WHERE c_id NOT IN (
SELECT c_id
FROM Score
WHERE s_id = '01'
)
)
AND s1.s_id != '01'
```
15. 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩。
```sql
SELECT Student.s_id, Student.s_name, AVG(Score.s_score) AS avg_score
FROM Student
JOIN Score ON Student.s_id = Score.s_id
GROUP BY Student.s_id, Student.s_name
HAVING SUM(Score.s_score < 60) >= 2;
```
16. 查询"01"课程分数小于60,按分数降序排列的学生信息及01分数
```sql
SELECT s.s_id, s.s_name, s.s_birth, s.s_sex, sc.s_score
FROM Student AS s
JOIN Score AS sc ON s.s_id = sc.s_id
WHERE sc.c_id = '01' AND sc.s_score < 60
ORDER BY sc.s_score DESC;
```
17. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
```sql
SELECT s.s_id, s.s_name, AVG(sc.s_score) AS avg_score
FROM Student AS s
JOIN Score AS sc ON s.s_id = sc.s_id
GROUP BY s.s_id, s.s_name
ORDER BY avg_score DESC;
```
18. 查询各科成绩最高分、最低分和平均分及及格率、中等率、优良率、优秀率
```sql
SELECT
c.c_id,
c.c_name,
MAX(sc.s_score) AS max_score,
MIN(sc.s_score) AS min_score,
AVG(sc.s_score) AS avg_score,
SUM(sc.s_score >= 60) / COUNT(sc.s_id) AS pass_rate,
SUM(sc.s_score BETWEEN 70 AND 80) / COUNT(sc.s_id) AS middle_rate,
SUM(sc.s_score BETWEEN 80 AND 90) / COUNT(sc.s_id) AS good_rate,
SUM(sc.s_score >= 90) / COUNT(sc.s_id) AS excellent_rate
FROM Course AS c
LEFT JOIN Score AS sc ON c.c_id = sc.c_id
GROUP BY c.c_id, c.c_name;
```
20、查询学生的总成绩并进行排名
```sql
SELECT s.s_id, s.s_name, SUM(sc.s_score) AS total_score
FROM Student s
JOIN Score sc ON s.s_id = sc.s_id
GROUP BY s.s_id
ORDER BY total_score DESC;
```
21、查询不同老师所教不同课程平均分从高到低显示
```sql
SELECT t.t_id, t.t_name, c.c_name, AVG(sc.s_score) AS avg_score
FROM Teacher t
JOIN Course c ON t.t_id = c.t_id
JOIN Score sc ON c.c_id = sc.c_id
GROUP BY c.c_id
ORDER BY avg_score DESC;
```
23、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
```sql
SELECT
c.c_id,
c.c_name,
SUM(CASE WHEN sc.s_score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS `[100-85]`,
SUM(CASE WHEN sc.s_score BETWEEN 70 AND 84 THEN 1 ELSE 0 END) AS `[85-70]`,
SUM(CASE WHEN sc.s_score BETWEEN 60 AND 69 THEN 1 ELSE 0 END) AS `[70-60]`,
SUM(CASE WHEN sc.s_score < 60 THEN 1 ELSE 0 END) AS `[0-60]`
FROM Course c
JOIN Score sc ON c.c_id = sc.c_id
GROUP BY c.c_id;
```
备注: 如果你需要百分比,你可能需要对每个分数段的人数除以总人数进行计算。
24、查询学生平均成绩及其名次
```sql
SELECT s.s_id, s.s_name, AVG(sc.s_score) AS avg_score
FROM Student s
JOIN Score sc ON s.s_id = sc.s_id
GROUP BY s.s_id
ORDER BY avg_score DESC;
```
19. 按各科成绩进行排序,并显示排名
首先,需要注意在MySQL中使用变量来计算排名是一个常见的做法。对于每个课程,我们可以这样进行排序并给出排名:
```sql
SELECT
s_id,
c_id,
s_score,
RANK() OVER (
PARTITION BY c_id
ORDER BY s_score DESC
) as score_rank
FROM
Score
ORDER BY
c_id,
s_score DESC;
```
22. 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
MySQL中没有内置的方法来直接选择一个范围内的排名,但我们可以通过上一步的查询结果再进行筛选,比如:
```sql
SELECT *
FROM (
SELECT s_id,
c_id,
s_score,
@rank := IF(@prev_c_id = c_id, @rank + 1, 1) AS `rank`,
@prev_c_id := c_id
FROM Score,
(SELECT @rank := 0, @prev_c_id := '') AS vars
ORDER BY c_id, s_score DESC
) ranked_scores
WHERE `rank` BETWEEN 2 AND 3;
```
24.1 添加名次rank,(相同分数的相同名次,并列排名)
```sql
SELECT s_id,
c_id,
s_score,
DENSE_RANK() OVER (PARTITION BY c_id ORDER BY s_score DESC) AS `rank`
FROM Score;
```
25. 查询各科成绩前三名的记录
利用窗口函数的能力,我们可以很方便地实现这一点:
```sql
SELECT *
FROM (
SELECT s_id,
c_id,
s_score,
DENSE_RANK() OVER (PARTITION BY c_id ORDER BY s_score DESC) AS `rank`
FROM Score
) AS ranked_scores
WHERE `rank` <= 3;
```
26、查询每门课程被选修的学生数:
```sql
SELECT c_id, COUNT(DISTINCT s_id) as student_count
FROM Score
GROUP BY c_id;
```
解释:这条SQL语句统计每门课程(c_id)的选修学生数。使用`DISTINCT`确保每名学生只被计数一次。
27、查询出只有两门课程的全部学生的学号和姓名:
```sql
SELECT s.s_id, s.s_name
FROM Student AS s
JOIN (SELECT s_id FROM Score GROUP BY s_id HAVING COUNT(c_id) = 2) AS sc
ON s.s_id = sc.s_id;
```
解释:子查询首先从Score表中找出所有只选修了两门课程的学生,然后主查询通过连接操作与学生表Student进行合并,获取这些学生的学号(s_id)和姓名(s_name)。
28、查询男生、女生人数:
```sql
SELECT s_sex, COUNT(*) as count
FROM Student
GROUP BY s_sex;
```
解释:根据学生性别(s_sex)分组,并计算每组的学生数。
30、查询同名同性学生名单,并统计同名人数:
```sql
SELECT s_name, s_sex, COUNT(*) as count
FROM Student
GROUP BY s_name, s_sex
HAVING COUNT(*) > 1;
```
解释:根据姓名(s_name)和性别(s_sex)分组,只选取那些在分组后数量大于1的记录,即有相同姓名和性别的学生。
33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩:
```sql
SELECT s.s_id, s.s_name, AVG(sc.s_score) as avg_score
FROM Student AS s
JOIN Score AS sc ON s.s_id = sc.s_id
GROUP BY s.s_id
HAVING AVG(sc.s_score) >= 85;
```
解释:连接Student表和Score表,计算每个学生的平均成绩,然后选择平均成绩大于等于85的记录。
35、查询所有学生的课程及分数情况:
```sql
SELECT s.s_id, s.s_name, c.c_name, sc.s_score
FROM Student AS s
JOIN Score AS sc ON s.s_id = sc.s_id
JOIN Course AS c ON sc.c_id = c.c_id;
```
解释:连接Student、Score和Course表,获取所有学生的课程名称和成绩。
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数:
```sql
SELECT s.s_name, c.c_name, sc.s_score
FROM Student AS s
JOIN Score AS sc ON s.s_id = sc.s_id
JOIN Course AS c ON sc.c_id = c.c_id
WHERE sc.s_score > 70;
```
解释:连接Student、Score和Course表,并筛选出分数大于70分的记录。
37、查询不及格的学生id,姓名,及其课程名称,分数:
```sql
SELECT s.s_id, s.s_name, c.c_name, sc.s_score
FROM Student AS s
JOIN Score AS sc ON s.s_id = sc.s_id
JOIN Course AS c ON sc.c_id = c.c_id
WHERE sc.s_score < 60;
```
解释:连接Student、Score和Course表,筛选出成绩不及格(假设不及格分数线为60)的学生信息。
38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名:
```sql
SELECT s.s_id, s.s_name
FROM Student AS s
JOIN Score AS sc ON s.s_id = sc.s_id
WHERE sc.c_id = '01' AND sc.s_score >= 80;
```
解释:连接Student和Score表,筛选出选修了编号为'01'的课程且成绩不低于80分的学生信息。
39、求每门课程的学生人数
```sql
SELECT c_id, COUNT(DISTINCT s_id) AS student_count
FROM Score
GROUP BY c_id;
```
40、查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩
```sql
SELECT s.s_id, s.s_name, sc.s_score
FROM Student s
JOIN Score sc ON s.s_id = sc.s_id
JOIN Course c ON sc.c_id = c.c_id
JOIN Teacher t ON c.t_id = t.t_id
WHERE t.t_name = '张三'
ORDER BY sc.s_score DESC
LIMIT 1;
```
41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
```sql
SELECT s1.s_id, s1.c_id, s1.s_score
FROM Score s1
JOIN Score s2 ON s1.s_score = s2.s_score AND s1.c_id <> s2.c_id AND s1.s_id = s2.s_id;
```
42、查询每门功课成绩最好的前两名
```sql
SELECT *
FROM (
SELECT s_id,
c_id,
s_score,
DENSE_RANK() OVER (PARTITION BY c_id ORDER BY s_score DESC) AS `rank`
FROM Score
) AS ranked_scores
WHERE `rank` <= 2;
```
43、统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
```sql
SELECT c_id, COUNT(DISTINCT s_id) AS student_count
FROM Score
GROUP BY c_id
HAVING student_count > 5
ORDER BY student_count DESC, c_id ASC;
```
44、检索至少选修两门课程的学生学号
```sql
SELECT s_id
FROM Score
GROUP BY s_id
HAVING COUNT(c_id) >= 2;
```
45、查询选修了全部课程的学生信息
```sql
SELECT s.s_id, s.s_name
FROM Student s
JOIN Score sc ON s.s_id = sc.s_id
GROUP BY s.s_id
HAVING COUNT(DISTINCT sc.c_id) = (SELECT COUNT(*) FROM Course);
```
46、查询各学生的年龄,按照(这个语句不完整,但假设是按照年龄升序排列)
```sql
SELECT s_id, s_name, TIMESTAMPDIFF(YEAR, STR_TO_DATE(s_birth, '%Y-%m-%d'), CURDATE()) AS age
FROM Student
ORDER BY age;
```