半个月时间把MySQL重新巩固了一遍，梳理了一篇几万字 “超硬核” 文章！_将null插入datetime字段后,该字段的值实际上是mysql服务器当前的日期和时间。
| 阿里云国内75折 回扣 微信号：monov8 |
| 阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6 |
- MySQL 是
最流行的关系型数据库管理系统在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System关系数据库管理系统)应用软件之一
一. 创建数据库
- 语法
create database 数据库名字;
mysql> create database sqltest;
- 结果
二. 查看已经存在的数据库
- 语法
show databases;
mysql> show databases;
- 结果
可以发现在上面的列表中除了刚刚创建的 mzc-testsqltest外还有另外 4 个数据库它们都是安装MySQL 时系统自动创建的其各自功能如下。
- information_schema主要存储了系统中的一些数据库对象信息。比如用户表信息、列信息、权限信息、字符集信息、分区信息等。
- cluster存储了系统的集群信息。
- mysql存储了系统的用户权限信息。
- test系统自动创建的测试数据库任何用户都可以使用。
三. 选择数据库
- 语法
use 数据库名;
mysql> use mzc-test;
- 返回
Database changed代表我们已经选择 sqltest 数据库后续所有操作将在 sqltest 数据库上执行。
- 有些人可能会问到连接以后怎么退出。其实不用退出来use 数据库后使用show databases就能查询所有数据库如果想跳到其他数据库用use 其他数据库名字。
四. 查看数据库中的表
- 语法show
tables;
mysql> show tables;
- 结果
五. 删除数据库
- 语法
drop database 数据库名称;
mysql> drop database mzc-test;
- 结果
- 注意删除时最好用 `` 符号把表明括起来
六. 设置表的类型
- MySQL的数据表类型
MyISAM、InnoDB、HEAP、 BOB、CSV等
一. 创建表
语法create table 表名 {列名,数据类型,约束条件};
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
- 结果
注意表名还请遵守数据库的命名规则这条数据后面要进行删除所以首字母为大写。
二. 查看表定义
- 语法
desc 表名
mysql> desc Student;
- 结果
- 虽然 desc 命令可以查看表定义但是其输出的信息还是不够全面为了查看更全面的表定义信息有时就需要通过查看创建表的 SQL 语句来得到可以使用如下命令实现
- 语法
show create table 表名 \G;
mysql> show create table Student \G;
- 结果
一. 数学函数
对数值型的数据进行指定的数学运算如abs()函数可以获得给定数值的绝对值round()函数可以对给定的数值进行四舍五入。
1. ABS(number)
- 作用返回 number 的绝对值
SELECT ABS(s_score) FROM score;
-
ABS(-86) 返回86 -
number 参数可以是任意有效的数值表达式。如果 number 包含 Null则返回 Null如果是未初始化变量则返回 0。
2. PI()
-
例1pi() 返回3.141592653589793 -
例2pi(2) 返回6.283185307179586 -
作用计算圆周率及其倍数
3. SQRT(x)
- 作用返回非负数的x的二次方根
4. MOD(x,y)
- 作用返回x被y除后的余数
5. CEIL(x)、CEILING(x)
- 作用返回不小于x的最小整数
6. FLOOR(x)
- 作用返回不大于x的最大整数
7. FLOOR(x)
- 作用返回不大于x的最大整数
8. ROUND(x)、ROUND(x,y)
- 作用前者返回最接近于x的整数即对x进行四舍五入后者返回最接近x的数其值保留到小数点后面y位若y为负值则将保留到x到小数点左边y位
SELECT ROUND(345222.9)
- 参数说明 numberExp 需要进行截取的数据 nExp 整数用于指定需要进行截取的位置>0从小数点往右位移nExp个位数 <0从小数点往左
nExp个位数 =0表示当前小数点的位置
9. POW(x,y)和、POWER(x,y)
- 作用返回x的y次乘方的值
10. EXP(x)
- 作用返回e的x乘方后的值
11. LOG(x)
- 作用返回x的自然对数x相对于基数e的对数
12. LOG10(x)
- 作用返回x的基数为10的对数
13. RADIANS(x)
- 作用返回x由角度转化为弧度的值
14. DEGREES(x)
- 作用返回x由弧度转化为角度的值
15. SIN(x)、ASIN(x)
- 作用前者返回x的正弦其中x为给定的弧度值后者返回x的反正弦值x为正弦
16. COS(x)、ACOS(x)
- 作用前者返回x的余弦其中x为给定的弧度值后者返回x的反余弦值x为余弦
17. TAN(x)、ATAN(x)
- 作用前者返回x的正切其中x为给定的弧度值后者返回x的反正切值x为正切
18. COT(x)
- 作用返回给定弧度值x的余切
二. 字符串函数
1. CHAR_LENGTH(str)
- 作用计算字符串字符个数
SELECT CHAR_LENGTH('这是一个十二个字的字符串');
2. CONCAT(s1,s2…)
- 作用返回连接参数产生的字符串一个或多个待拼接的内容任意一个为NULL则返回值为NULL
SELECT CONCAT('拼接','测试');
3. CONCAT_WS(x,s1,s2,…)
- 作用返回多个字符串拼接之后的字符串每个字符串之间有一个x
SELECT CONCAT_WS('-','测试','拼接','WS')
4. INSERT(s1,x,len,s2)
- 作用返回字符串s1其子字符串起始于位置x被字符串s2取代len个字符
SELECT INSERT('测试字符串替换',2,1,'牛');
5. LOWER(str)和LCASE(str)、UPPER(str)和UCASE(str)
- 作用前两者将str中的字母全部转换成小写后两者将字符串中的字母全部转换成大写
SELECT LOWER('JHGYTUGHJGG'),LCASE('HKJHKJHKJHKJ');
SELECT UPPER('aaaaaa'),UCASE('vvvvv');
6. LEFT(s,n)、RIGHT(s,n)
- 作用前者返回字符串s从最左边开始的n个字符后者返回字符串s从最右边开始的n个字符
SELECT LEFT('左边开始',2),RIGHT('右边开始',2);
7. LPAD(s1,len,s2)、RPAD(s1,len,s2)
- 作用前者返回s1其左边由字符串s2填补到len字符长度假如s1的长度大于len则返回值被缩短至len字符前者返回s1其右边由字符串s2填补到len字符长度假如s1的长度大于len则返回值被缩短至len字符
SELECT LEFT('左边开始',2),RIGHT('右边开始',2);
8. LTRIM(s)、RTRIM(s)
- 作用前者返回字符串s其左边所有空格被删除后者返回字符串s其右边所有空格被删除
SELECT LTRIM(' 左边开始'),RTRIM(' 右边开始 ');
9. TRIM(s)
- 作用返回字符串s删除了两边空格之后的字符串
SELECT TRIM(' 是是 ');
10. TRIM(s1 FROM s)
- 作用删除字符串s两端所有子字符串s1未指定s1的情况下则默认删除空格
11. REPEAT(s,n)
- 作用返回一个由重复字符串s组成的字符串字符串s的数目等于n
SELECT REPEAT('测试',5);
12. SPACE(n)
- 作用返回一个由n个空格组成的字符串
SELECT SPACE(20);
13. REPLACE(s,s1,s2)
- 作用返回一个字符串用字符串s2替代字符串s中所有的字符串s1
14. STRCMP(s1,s2)
- 作用若s1和s2中所有的字符串都相同则返回0根据当前分类次序第一个参数小于第二个则返回-1其他情况返回1
SELECT STRCMP('我我我','我我我');
SELECT STRCMP('我我我','是是是');
15. SUBSTRING(s,n,len)、MID(s,n,len)
- 作用两个函数作用相同从字符串s中返回一个第n个字符开始、长度为len的字符串
SELECT SUBSTRING('测试测试',2,2);
SELECT MID('测试测试',2,2);
16. LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str,str1)
- 作用三个函数作用相同返回子字符串str1在字符串str中的开始位置从第几个字符开始
SELECT LOCATE('字','获取字符串的位置');
17. REVERSE(s)
- 作用将字符串s反转
SELECT REVERSE('字符串反转');
18. ELT(N,str1,str2,str3,str4,…)
- 作用返回第N个字符串
SELECT ELT(2,'字符串反转','sssss');
三. 日期和时间函数
当前时间
1. CURDATE()、CURRENT_DATE()
- 作用将当前日期按照"YYYY-MM-DD"或者"YYYYMMDD"格式的值返回具体格式根据函数用在字符串或是数字语境中而定
2. CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE()
- 作用这四个函数作用相同返回当前日期和时间值格式为"YYYY_MM-DD HH:MM:SS"或"YYYYMMDDHHMMSS"具体格式根据函数用在字符串或数字语境中而定
SELECT CURRENT_TIMESTAMP()
SELECT LOCALTIME()
SELECT NOW()
SELECT SYSDATE()
3. UNIX_TIMESTAMP()、UNIX_TIMESTAMP(date)
- 作用前者返回一个格林尼治标准时间1970-01-01 00:00:00到现在的秒数后者返回一个格林尼治标准时间1970-01-01 00:00:00到指定时间的秒数
SELECT UNIX_TIMESTAMP()
4. FROM_UNIXTIME(date)
- 作用和UNIX_TIMESTAMP互为反函数把UNIX时间戳转换为普通格式的时间
5. UTC_DATE()和UTC_TIME()
- 前者返回当前UTC世界标准时间日期值其格式为"YYYY-MM-DD"或"YYYYMMDD"后者返回当前UTC时间值其格式为"YYYY-MM-DD"或"YYYYMMDD"。具体使用哪种取决于函数用在字符串还是数字语境中
SELECT UTC_DATE()
SELECT UTC_TIME()
6. MONTH(date)和MONTHNAME(date)
- 作用前者返回指定日期中的月份后者返回指定日期中的月份的名称
SELECT MONTH(NOW())
SELECT MONTHNAME(NOW())
7. DAYNAME(d)、DAYOFWEEK(d)、WEEKDAY(d)
- 作用DAYNAME(d)返回d对应的工作日的英文名称如Sunday、Monday等DAYOFWEEK(d)返回的对应一周中的索引1表示周日、2表示周一WEEKDAY(d)表示d对应的工作日索引0表示周一1表示周二
8. WEEK(d)
- 计算日期d是一年中的第几周
SELECT WEEK(NOW())
9. DAYOFYEAR(d)、DAYOFMONTH(d)
- 作用前者返回d是一年中的第几天后者返回d是一月中的第几天
SELECT DAYOFYEAR(NOW())
SELECT DAYOFMONTH(NOW())
10. YEAR(date)、QUARTER(date)、MINUTE(time)、SECOND(time)
- 作用 YEAR(date)返回指定日期对应的年份范围是
1970~2069QUARTER(date)返回date对应一年中的季度范围是1~4MINUTE(time)返回time对应的分钟数范围是0~59SECOND(time)返回制定时间的秒值
SELECT YEAR(NOW())
SELECT QUARTER(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())
11. EXTRACE(type FROM date)
- 作用从日期中提取一部分type可以是YEAR、YEAR_MONTH、DAY_HOUR、DAY_MICROSECOND、DAY_MINUTE、DAY_SECOND
12. TIME_TO_SEC(time)
- 作用返回以转换为秒的time参数转换公式为"3600小时 + 60分钟 + 秒"
SELECT TIME_TO_SEC(NOW())
13. SEC_TO_TIME()
- 作用和TIME_TO_SEC(time)互为反函数将秒值转换为时间格式
SELECT SEC_TO_TIME(530)
14. DATE_ADD(date,INTERVAL expr type)、ADD_DATE(date,INTERVAL expr type)
- 作用返回将起始时间加上expr type之后的时间比如DATE_ADD(‘2010-12-31 23:59:59’, INTERVAL 1 SECOND)表示的就是把第一个时间加1秒
15. DATE_SUB(date,INTERVAL expr type)、SUBDATE(date,INTERVAL expr type)
- 作用返回将起始时间减去expr type之后的时间
16. ADDTIME(date,expr)、SUBTIME(date,expr)
- 作用前者进行date的时间加操作后者进行date的时间减操作
四. 条件判断函数
1. IF(expr,v1,v2)
- 作用如果expr是TRUE则返回v1否则返回v2
2. IFNULL(v1,v2)
- 作用如果v1不为NULL则返回v1否则返回v2
3. CASE expr WHEN v1 THEN r1 [WHEN v2 THEN v2] [ELSE rn] END
- 作用如果expr等于某个vn则返回对应位置THEN后面的结果如果与所有值都不想等则返回ELSE后面的rn
五. 系统信息函数
1. VERSION()
- 作用查看MySQL版本号
SELECT VERSION()
2. CONNECTION_ID()
- 作用查看当前用户的连接数
SELECT CONNECTION_ID()
3. USER()、CURRENT_USER()、SYSTEM_USER()、SESSION_USER()
- 作用查看当前被MySQL服务器验证的用户名和主机的组合一般这几个函数的返回值是相同的
SELECT USER()
SELECT CURRENT_USER()
SELECT SYSTEM_USER()
SELECT SESSION_USER()
4. CHARSET(str)
- 作用查看字符串str使用的字符集
SELECT CHARSET(555)
5. COLLATION()
- 作用查看字符串排列方式
SELECT COLLATION('sssfddsfds')
六. 加密函数
1. PASSWORD(str)
- 作用从原明文密码str计算并返回加密后的字符串密码注意这个函数的加密是单向的不可逆因此不应将它应用在个人的应用程序中而应该只在MySQL服务器的鉴定系统中使用
SELECT PASSWORD('mima')
2. MD5(str)
- 作用为字符串算出一个MD5 128比特校验和改值以32位十六进制数字的二进制字符串形式返回
SELECT MD5('mima')
3. ENCODE(str, pswd_str)
- 作用使用pswd_str作为密码加密str
SELECT ENCODE('fdfdz','mima')
4. DECODE(crypt_str,pswd_str)
- 作用使用pswd_str作为密码解密加密字符串crypt_strcrypt_str是由ENCODE函数返回的字符串
SELECT DECODE('fdfdz','mima')
七. 其他函数
1. FORMAT(x,n)
- 作用将数字x格式化并以四舍五入的方式保留小数点后n位结果以字符串形式返回
SELECT FORMAT(446.454,2)
2. CONV(N,from_base,to_base)
- 作用不同进制数之间的转换返回值为数值N的字符串表示由from_base进制转换为to_base进制
3. INET_ATON(expr)
- 作用给出一个作为字符串的网络地址的点地址表示返回一个代表该地址数值的整数地址可以使4或8比特
4. INET_NTOA(expr)
- 作用给定一个数字网络地址4或8比特返回作为字符串的该地址的点地址表示
5. BENCHMARK(count,expr)
- 作用重复执行count次表达式expr它可以用于计算MySQL处理表达式的速度结果值通常是00只是表示很快并不是没有速度。
- 另一个作用是用它在MySQL客户端内部报告语句执行的时间
6. CONVERT(str USING charset)
- 作用使用字符集charset表示字符串str
更多用法还请参考http://www.geezn.com/documents/gez/help/117555-1355219868404378.html
【SQL实战练习】
- 题目来自互联网建议每道题都在本地敲一遍巩固记忆
创建数据库
创建表并初始化数据
-- 学生表 CREATE TABLE `student`( `s_id` VARCHAR(20), `s_name` VARCHAR(20) NOT NULL DEFAULT '', `s_birth` VARCHAR(20) NOT NULL DEFAULT '', `s_sex` VARCHAR(10) NOT NULL DEFAULT '', PRIMARY KEY(`s_id`) ); -- 课程表 CREATE TABLE `course`( `c_id` VARCHAR(20), `c_name` VARCHAR(20) NOT NULL DEFAULT '', `t_id` VARCHAR(20) NOT NULL, PRIMARY KEY(`c_id`) ); -- 教师表 CREATE TABLE `teacher`( `t_id` VARCHAR(20), `t_name` VARCHAR(20) NOT NULL DEFAULT '', PRIMARY KEY(`t_id`) ); -- 成绩表 CREATE TABLE `score`( `s_id` VARCHAR(20), `c_id` VARCHAR(20), `s_score` INT(3), PRIMARY KEY(`s_id`,`c_id`) ); -- 插入学生表测试数据 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);表结构
- 这里建的表主要用于sql语句的练习所以并没有遵守一些规范。下面让我们来看看相关的表结构吧
学生表student
s_id = 学生编号s_name = 学生姓名s_birth = 出生年月s_sex = 学生性别
课程表course
c_id = 课程编号c_name = 课程名称t_id = 教师编号
教师表teacher
t_id = 教师编号t_name = 教师姓名
成绩表score
s_id = 学生编号c_id = 课程编号s_score = 分数
习题
- 开始之前我们先来看看四张表中的数据。
1. 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT st.*, sc.s_score AS '语文', sc2.s_score '数学' FROM student st LEFT JOIN score sc ON sc.s_id = st.s_id AND sc.c_id = '01' LEFT JOIN score sc2 ON sc2.s_id = st.s_id AND sc2.c_id = '02'
2. 查询"01"课程比"02"课程成绩低的学生的信息及课程分数
SELECT st.*, s.s_score AS 数学, s2.s_score AS 语文 FROM student st LEFT JOIN score s ON s.s_id = st.s_id AND s.c_id = '01' LEFT JOIN score s2 ON s2.s_id = st.s_id AND s2.c_id = '02' WHERE s.s_score < s2.s_score
3. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT st.s_id AS '学生编号', st.s_name AS '学生姓名', AVG( s.s_score ) AS avgScore FROM student st LEFT JOIN score s ON st.s_id = s.s_id GROUP BY st.s_id HAVING avgScore >= 60
4. 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
- (包括有成绩的和无成绩的)
SELECT st.s_id AS '学生编号', st.s_name AS '学生姓名',( CASE WHEN ROUND( AVG( sc.s_score ), 2 ) IS NULL THEN 0 ELSE ROUND( AVG( sc.s_score ), 2 ) END ) FROM student st LEFT JOIN score sc ON st.s_id = sc.s_id GROUP BY st.s_id HAVING AVG( sc.s_score )< 60 OR AVG( sc.s_score ) IS NULL
5. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT st.s_id AS '学生编号', st.s_name AS '学生姓名', COUNT( sc.c_id ) AS '选课总数', sum( CASE WHEN sc.s_score IS NULL THEN 0 ELSE sc.s_score END ) AS '总成绩' FROM student st LEFT JOIN score sc ON st.s_id = sc.s_id GROUP BY st.s_id
6. 查询"流"姓老师的数量
SELECT COUNT(t_id) FROM teacher WHERE t_name LIKE '流%'
7. 查询学过"流浪法师"老师授课的同学的信息
SELECT st.* FROM student st LEFT JOIN score sc ON sc.s_id = st.s_id LEFT JOIN course cs ON cs.c_id = sc.c_id LEFT JOIN teacher tc ON tc.t_id = cs.t_id WHERE tc.t_name = '流浪法师'
8. 查询没学过"张三"老师授课的同学的信息
-- 查询流浪法师教的课 SELECT cs.* FROM course cs LEFT JOIN teacher tc ON tc.t_id = cs.t_id WHERE tc.t_name = '流浪法师' -- 查询有流浪法师课程成绩的学生id SELECT sc.s_id FROM score sc WHERE sc.c_id IN ( SELECT cs.c_id FROM course cs LEFT JOIN teacher tc ON tc.t_id = cs.t_id WHERE tc.t_name = '流浪法师') -- 取反查询没有学过流浪法师课程的同学信息 SELECT st.* FROM student st WHERE st.s_id NOT IN ( SELECT sc.s_id FROM score sc WHERE sc.c_id IN ( SELECT cs.c_id FROM course cs LEFT JOIN teacher tc ON tc.t_id = cs.t_id WHERE tc.t_name = '流浪法师' ) )
9. 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
- 方法 1
-- 查询学过编号为01课程的同学id SELECT st.s_id FROM student st INNER JOIN score sc ON sc.s_id = st.s_id INNER JOIN course cs ON cs.c_id = sc.c_id AND cs.c_id = '01'; -- 查询学过编号为02课程的同学id SELECT st2.s_id FROM student st2 INNER JOIN score sc2 ON sc2.s_id = st2.s_id INNER JOIN course cs2 ON cs2.c_id = sc2.c_id AND cs2.c_id = '02'; -- 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息 SELECT st.* FROM student st INNER JOIN score sc ON sc.s_id = st.s_id INNER JOIN course cs ON cs.c_id = sc.c_id AND sc.c_id = '01' WHERE st.s_id IN ( SELECT st2.s_id FROM student st2 INNER JOIN score sc2 ON sc2.s_id = st2.s_id INNER JOIN course cs2 ON cs2.c_id = sc2.c_id AND cs2.c_id = '02' );
- 方法 2
SELECT a.* FROM student a, score b, score c WHERE a.s_id = b.s_id AND a.s_id = c.s_id AND b.c_id = '01' AND c.c_id = '02';10. 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT st.s_id FROM student st INNER JOIN score sc ON sc.s_id = st.s_id INNER JOIN course cs ON cs.c_id = sc.c_id AND cs.c_id = '01' WHERE st.s_id NOT IN ( SELECT st.s_id FROM student st INNER JOIN score sc ON sc.s_id = st.s_id INNER JOIN course cs ON cs.c_id = sc.c_id AND cs.c_id = '02' );
11. 查询没有学全所有课程的同学的信息
- 方法 1
SELECT * FROM student WHERE s_id NOT IN ( SELECT st.s_id FROM student st INNER JOIN score sc ON sc.s_id = st.s_id AND sc.c_id = '01' WHERE st.s_id IN ( SELECT st.s_id FROM student st INNER JOIN score sc ON sc.s_id = st.s_id AND sc.c_id = '02' WHERE st.s_id ) AND st.s_id IN ( SELECT st.s_id FROM student st INNER JOIN score sc ON sc.s_id = st.s_id AND sc.c_id = '03' WHERE st.s_id ) );
- 方法 2
SELECT a.* FROM student a LEFT JOIN score b ON a.s_id = b.s_id GROUP BY a.s_id HAVING COUNT( b.c_id ) != '3';12. 查询至少有一门课与学号为"01"的同学所学相同的同学的信息
SELECT DISTINCT st.* FROM student st LEFT JOIN score sc ON sc.s_id = st.s_id WHERE sc.c_id IN ( SELECT sc2.c_id FROM student st2 LEFT JOIN score sc2 ON sc2.s_id = st2.s_id WHERE st2.s_id = '01' );
13. 查询和"01"号的同学学习的课程完全相同的其他同学的信息
SELECT st.* FROM student st LEFT JOIN score sc ON sc.s_id = st.s_id GROUP BY st.s_id HAVING GROUP_CONCAT( sc.c_id )=( SELECT GROUP_CONCAT( sc2.c_id ) FROM student st2 LEFT JOIN score sc2 ON sc2.s_id = st2.s_id WHERE st2.s_id = '01' );
14. 查询没学过"邪恶小法师"老师讲授的任一门课程的学生姓名
SELECT * FROM student WHERE s_id NOT IN ( SELECT sc.s_id FROM score sc INNER JOIN course cs ON cs.c_id = sc.c_id INNER JOIN teacher t ON t.t_id = cs.t_id AND t.t_name = '邪恶小法师');
15. 查询两门及其以上不及格课程的同学的学号姓名及其平均成绩
SELECT st.s_id AS '学号', st.s_name AS '姓名', AVG( sc.s_score ) AS '平均成绩' FROM student st LEFT JOIN score sc ON sc.s_id = st.s_id WHERE sc.s_id IN ( SELECT sc.s_id FROM score sc WHERE sc.s_score < 60 OR sc.s_score IS NULL GROUP BY sc.s_id HAVING COUNT( 1 )>= 2 ) GROUP BY st.s_id
16. 检索"01"课程分数小于60按分数降序排列的学生信息
SELECT st.* FROM student st INNER JOIN score sc ON sc.s_id = st.s_id AND sc.c_id = '01' AND sc.s_score < '60' ORDER BY sc.s_score DESC; SELECT st.* FROM student st LEFT JOIN score sc ON sc.s_id = st.s_id WHERE sc.c_id = '01' AND sc.s_score < '60' ORDER BY sc.s_score DESC;
17. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
- 方法 1
SELECT st.*, AVG( sc4.s_score ) AS '平均分', sc.s_score AS '语文', sc2.s_score AS '数学', sc3.s_score AS '英语' FROM student st LEFT JOIN score sc ON sc.s_id = st.s_id AND sc.c_id = '01' LEFT JOIN score sc2 ON sc2.s_id = st.s_id AND sc2.c_id = '02' LEFT JOIN score sc3 ON sc3.s_id = st.s_id AND sc3.c_id = '03' LEFT JOIN score sc4 ON sc4.s_id = st.s_id GROUP BY st.s_id ORDER BY AVG( sc4.s_score ) DESC;
- 方法 2
SELECT st.*, ( CASE WHEN AVG( sc4.s_score ) IS NULL THEN 0 ELSE AVG( sc4.s_score ) END ) AS '平均分', ( CASE WHEN sc.s_score IS NULL THEN 0 ELSE sc.s_score END ) AS '语文', ( CASE WHEN sc2.s_score IS NULL THEN 0 ELSE sc2.s_score END ) AS '数学', ( CASE WHEN sc3.s_score IS NULL THEN 0 ELSE sc3.s_score END ) AS '英语' FROM student st LEFT JOIN score sc ON sc.s_id = st.s_id AND sc.c_id = '01' LEFT JOIN score sc2 ON sc2.s_id = st.s_id AND sc2.c_id = '02' LEFT JOIN score sc3 ON sc3.s_id = st.s_id AND sc3.c_id = '03' LEFT JOIN score sc4 ON sc4.s_id = st.s_id GROUP BY st.s_id ORDER BY AVG( sc4.s_score ) DESC;
18. 查询各科成绩最高分、最低分和平均分
- 以如下形式显示课程ID课程name最高分最低分平均分及格率中等率优良率优秀率
- 及格为>=60中等为70-80优良为80-90优秀为>=90
SELECT cs.c_id, cs.c_name, MAX( sc1.s_score ) AS '最高分', MIN( sc2.s_score ) AS '最低分', AVG( sc3.s_score ) AS '平均分', (( SELECT COUNT( s_id ) FROM score WHERE s_score >= 60 AND c_id = cs.c_id )/( SELECT COUNT( s_id ) FROM score WHERE c_id = cs.c_id )) AS '及格率', (( SELECT COUNT( s_id ) FROM score WHERE s_score >= 70 AND s_score < 80 AND c_id = cs.c_id )/( SELECT COUNT( s_id ) FROM score WHERE c_id = cs.c_id )) AS '中等率', (( SELECT COUNT( s_id ) FROM score WHERE s_score >= 80 AND s_score < 90 AND c_id = cs.c_id )/( SELECT COUNT( s_id ) FROM score WHERE c_id = cs.c_id )) AS '优良率', (( SELECT COUNT( s_id ) FROM score WHERE s_score >= 90 AND c_id = cs.c_id )/( SELECT COUNT( s_id ) FROM score WHERE c_id = cs.c_id )) AS '优秀率' FROM course cs LEFT JOIN score sc1 ON sc1.c_id = cs.c_id LEFT JOIN score sc2 ON sc2.c_id = cs.c_id LEFT JOIN score sc3 ON sc3.c_id = cs.c_id GROUP BY cs.c_id;
19. 按各科成绩进行排序并显示排名(实现不完全)
- mysql没有rank函数
- 加@score是为了防止用union all 后打乱了顺序
SELECT c1.s_id, c1.c_id, c1.c_name, @score := c1.s_score, @i := @i + 1 FROM ( SELECT c.c_name, sc.* FROM course c LEFT JOIN score sc ON sc.c_id = c.c_id WHERE c.c_id = "01" ORDER BY sc.s_score DESC ) c1, ( SELECT @i := 0 ) a UNION ALL SELECT c2.s_id, c2.c_id, c2.c_name, c2.s_score, @ii := @ii + 1 FROM ( SELECT c.c_name, sc.* FROM course c LEFT JOIN score sc ON sc.c_id = c.c_id WHERE c.c_id = "02" ORDER BY sc.s_score DESC ) c2, ( SELECT @ii := 0 ) aa UNION ALL SELECT c3.s_id, c3.c_id, c3.c_name, c3.s_score, @iii := @iii + 1 FROM ( SELECT c.c_name, sc.* FROM course c LEFT JOIN score sc ON sc.c_id = c.c_id WHERE c.c_id = "03" ORDER BY sc.s_score DESC ) c3; SET @iii = 0;
20. 查询学生的总成绩并进行排名
SELECT st.s_id, st.s_name, ( CASE WHEN sum( sc.s_score ) IS NULL THEN 0 ELSE SUM( sc.s_score ) END ) FROM student st LEFT JOIN score sc ON st.s_id = sc.s_id GROUP BY st.s_id ORDER BY SUM( sc.s_score ) DESC
21. 查询不同老师所教不同课程平均分从高到低显示
SELECT t.t_id, t.t_name, AVG( sc.s_score ) FROM teacher t LEFT JOIN course c ON c.t_id = t.t_id LEFT JOIN score sc ON sc.c_id = c.c_id GROUP BY t.t_id ORDER BY AVG( sc.s_score ) DESC
22. 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
SELECT a.* FROM ( SELECT st.s_id, st.s_name, c.c_id, c.c_name, sc.s_score FROM student st LEFT JOIN score sc ON sc.s_id = st.s_id INNER JOIN course c ON sc.c_id = c.c_id AND c.c_id = '01' ORDER BY sc.s_score DESC LIMIT 1, 2 ) a UNION ALL SELECT b.* FROM ( SELECT st.s_id, st.s_name, c.c_id, c.c_name, sc.s_score FROM student st LEFT JOIN score sc ON sc.s_id = st.s_id INNER JOIN course c ON c.c_id = sc.c_id AND c.c_id = '02' ORDER BY sc.s_score DESC LIMIT 1, 2 ) b UNION ALL SELECT c.* FROM ( SELECT st.s_id, st.s_name, c.c_id, c.c_name, sc.s_score FROM student st LEFT JOIN score sc ON sc.s_id = st.s_id INNER JOIN course c ON c.c_id = sc.c_id AND c.c_id = '03' ORDER BY sc.s_score DESC LIMIT 1, 2 ) c;
23. 统计各科成绩各分数段人数课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
SELECT c.c_id, c.c_name, ( SELECT COUNT( 1 ) FROM score sc WHERE sc.c_id = c.c_id AND sc.s_score <= 100 AND sc.s_score > 80 )/( SELECT COUNT( 1 ) FROM score sc WHERE sc.c_id = c.c_id ) AS '100-85', (( SELECT COUNT( 1 ) FROM score sc WHERE sc.c_id = c.c_id AND sc.s_score <= 85 AND sc.s_score > 70 )/( SELECT COUNT( 1 ) FROM score sc WHERE sc.c_id = c.c_id )) AS '85-70', (( SELECT COUNT( 1 ) FROM score sc WHERE sc.c_id = c.c_id AND sc.s_score <= 70 AND sc.s_score > 60 )/( SELECT COUNT( 1 ) FROM score sc WHERE sc.c_id = c.c_id )) AS '70-60', (( SELECT COUNT( 1 ) FROM score sc WHERE sc.c_id = c.c_id AND sc.s_score <= 60 AND sc.s_score >= 0 )/( SELECT COUNT( 1 ) FROM score sc WHERE sc.c_id = c.c_id )) AS '85-70' FROM course c ORDER BY c.c_id
24. 查询学生平均成绩及其名次
SET @i = 0; SELECT a.*, @i := @i + 1 FROM ( SELECT st.s_id, st.s_name, round( CASE WHEN AVG( sc.s_score ) IS NULL THEN 0 ELSE AVG( sc.s_score ) END, 2 ) AS agvScore FROM student st LEFT JOIN score sc ON sc.s_id = st.s_id GROUP BY st.s_id ORDER BY agvScore DESC ) a
25. 查询各科成绩前三名的记录
SELECT a.* FROM ( SELECT st.s_id, st.s_name, c.c_id, c.c_name, sc.s_score FROM student st LEFT JOIN score sc ON sc.s_id = st.s_id INNER JOIN course c ON c.c_id = sc.c_id AND c.c_id = '01' ORDER BY sc.s_score DESC LIMIT 0, 3 ) a UNION ALL SELECT b.* FROM ( SELECT st.s_id, st.s_name, c.c_id, c.c_name, sc.s_score FROM student st LEFT JOIN score sc ON sc.s_id = st.s_id INNER JOIN course c ON c.c_id = sc.c_id AND c.c_id = '02' ORDER BY sc.s_score DESC LIMIT 0, 3 ) b UNION ALL SELECT c.* FROM ( SELECT st.s_id, st.s_name, c.c_id, c.c_name, sc.s_score FROM student st LEFT JOIN score sc ON sc.s_id = st.s_id INNER JOIN course c ON c.c_id = sc.c_id AND c.c_id = '03' ORDER BY sc.s_score DESC LIMIT 0, 3 ) c
26. 查询每门课程被选修的学生数
SELECT c.c_id, c.c_name, COUNT( 1 ) FROM course c LEFT JOIN score sc ON sc.c_id = c.c_id INNER JOIN student st ON st.s_id = c.c_id GROUP BY c.c_id
27. 查询出只有两门课程的全部学生的学号和姓名
SELECT st.s_id, st.s_name FROM student st LEFT JOIN score sc ON sc.s_id = st.s_id INNER JOIN course c ON c.c_id = sc.c_id GROUP BY st.s_id HAVING COUNT( 1 ) = 2
28. 查询男生、女生人数
SELECT s_sex, COUNT(1) FROM student GROUP BY s_sex
29. 查询名字中含有"德"字的学生信息
SELECT * FROM student WHERE s_name LIKE '%德%'
30. 查询同名同性学生名单并统计同名人数
select st.s_name,st.s_sex,count(1) from student st group by st.s_name,st.s_sex having count(1)>1
31. 查询1990年出生的学生名单
SELECT st.* FROM student st WHERE st.s_birth LIKE '1990%';
32. 查询每门课程的平均成绩结果按平均成绩降序排列平均成绩相同时按课程编号升序排列
SELECT c.c_id, c_name, AVG( sc.s_score ) AS scoreAvg FROM course c INNER JOIN score sc ON sc.c_id = c.c_id GROUP BY c.c_id ORDER BY scoreAvg DESC, c.c_id ASC;
33. 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
SELECT st.s_id, st.s_name, ( CASE WHEN AVG( sc.s_score ) IS NULL THEN 0 ELSE AVG( sc.s_score ) END ) scoreAvg FROM student st LEFT JOIN score sc ON sc.s_id = st.s_id GROUP BY st.s_id HAVING scoreAvg > '85';
34. 查询课程名称为"数学"且分数低于60的学生姓名和分数
SELECT * FROM student st INNER JOIN score sc ON sc.s_id = st.s_id AND sc.s_score < 60 INNER JOIN course c ON c.c_id = sc.c_id AND c.c_name = '数学';
35. 查询所有学生的课程及分数情况
SELECT * FROM student st LEFT JOIN score sc ON sc.s_id = st.s_id LEFT JOIN course c ON c.c_id = sc.c_id ORDER BY st.s_id, c.c_name;
36. 查询任何一门课程成绩在70分以上的姓名、课程名称和分数
SELECT st.s_id,st.s_name,c.c_name,sc.s_score FROM student st LEFT JOIN score sc ON sc.s_id = st.s_id LEFT JOIN course c ON c.c_id = sc.c_id WHERE st.s_id IN ( SELECT st2.s_id FROM student st2 LEFT JOIN score sc2 ON sc2.s_id = st2.s_id GROUP BY st2.s_id HAVING MIN( sc2.s_score )>= 70 ORDER BY st2.s_id )
37. 查询不及格的课程
SELECT st.s_id, c.c_name, st.s_name, sc.s_score FROM student st INNER JOIN score sc ON sc.s_id = st.s_id AND sc.s_score < 60 INNER JOIN course c ON c.c_id = sc.c_id
38. 查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
SELECT st.s_id, st.s_name, sc.s_score FROM student st INNER JOIN score sc ON sc.s_id = st.s_id AND sc.c_id = '01' AND sc.s_score >= 80;
39. 求每门课程的学生人数
SELECT c.c_id, c.c_name, COUNT( 1 ) FROM course c INNER JOIN score sc ON sc.c_id = c.c_id GROUP BY c.c_id;
40. 查询选修"死亡歌颂者"老师所授课程的学生中成绩最高的学生信息及其成绩
SELECT st.*, sc.s_score FROM student st INNER JOIN score sc ON sc.s_id = st.s_id INNER JOIN course c ON c.c_id = sc.c_id INNER JOIN teacher t ON t.t_id = c.t_id AND t.t_name = '死亡歌颂者' ORDER BY sc.s_score DESC LIMIT 0,1;
41. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT st.s_id, st.s_name, sc.c_id, sc.s_score FROM student st LEFT JOIN score sc ON sc.s_id = st.s_id LEFT JOIN course c ON c.c_id = sc.c_id WHERE ( SELECT COUNT( 1 ) FROM student st2 LEFT JOIN score sc2 ON sc2.s_id = st2.s_id LEFT JOIN course c2 ON c2.c_id = sc2.c_id WHERE sc.s_score = sc2.s_score AND c.c_id != c2.c_id )>1;
42. 查询每门功成绩最好的前两名
SELECT a.* FROM ( SELECT st.s_id, st.s_name, c.c_name, sc.s_score FROM student st LEFT JOIN score sc ON sc.s_id = st.s_id INNER JOIN course c ON c.c_id = sc.c_id AND c.c_id = '01' ORDER BY sc.s_score DESC LIMIT 0, 2 ) a UNION ALL SELECT b.* FROM ( SELECT st.s_id, st.s_name, c.c_name, sc.s_score FROM student st LEFT JOIN score sc ON sc.s_id = st.s_id INNER JOIN course c ON c.c_id = sc.c_id AND c.c_id = '02' ORDER BY sc.s_score DESC LIMIT 0, 2 ) b UNION ALL SELECT c.* FROM ( SELECT st.s_id, st.s_name, c.c_name, sc.s_score FROM student st LEFT JOIN score sc ON sc.s_id = st.s_id INNER JOIN course c ON c.c_id = sc.c_id AND c.c_id = '03' ORDER BY sc.s_score DESC LIMIT 0, 2 ) c;
写法 2
SELECT a.s_id, a.c_id, a.s_score FROM score a WHERE ( SELECT COUNT( 1 ) FROM score b WHERE b.c_id = a.c_id AND b.s_score > a.s_score ) <= 2 ORDER BY a.c_id;
43. 统计每门课程的学生选修人数超过5人的课程才统计
- 要求输出课程号和选修人数查询结果按人数降序排列若人数相同按课程号升序排列
SELECT c.c_id, COUNT( 1 ) FROM score sc LEFT JOIN course c ON c.c_id = sc.c_id GROUP BY c.c_id HAVING COUNT( 1 ) > 5 ORDER BY COUNT( 1 ) DESC, c.c_id ASC;
44. 检索至少选修两门课程的学生学号
SELECT st.s_id FROM student st LEFT JOIN score sc ON sc.s_id = st.s_id GROUP BY st.s_id HAVING COUNT( 1 )>= 2;
45. 查询选修了全部课程的学生信息
SELECT st.* FROM student st LEFT JOIN score sc ON sc.s_id = st.s_id GROUP BY st.s_id HAVING COUNT( 1 )=( SELECT COUNT( 1 ) FROM course)
46. 查询各学生的年龄
SELECT st.*, TIMESTAMPDIFF( YEAR, st.s_birth, NOW()) FROM student st
47. 查询本周过生日的学生
SELECT st.* FROM student st WHERE WEEK ( NOW())+ 1 = WEEK ( DATE_FORMAT( st.s_birth, '%Y%m%d' ))
48. 查询下周过生日的学生
SELECT st.* FROM student st WHERE WEEK ( NOW())+ 1 = WEEK ( DATE_FORMAT( st.s_birth, '%Y%m%d' ));
49. 查询本月过生日的学生
SELECT st.* FROM student st WHERE MONTH ( NOW())= MONTH ( DATE_FORMAT( st.s_birth, '%Y%m%d' ));
50. 查询下月过生日的学生
SELECT st.* FROM student st WHERE MONTH ( TIMESTAMPADD( MONTH, 1, NOW()))= MONTH ( DATE_FORMAT( st.s_birth, '%Y%m%d' ));
【阿里巴巴开发手册】
点击预览在线版: 阿里巴巴开发手册
内容偏向基础适合各个阶段人员的学习与巩固如果对您还有些帮助希望给博主点个赞
支持一下感谢