SQL（结构查询语言）类型
包括5种：
1.DQL（数据查询语言）
A.基本语法：SELECT FROM WHERE GROUP BY HAVING ORDEY BY LIMIT
B.常用函数：聚合函数、窗口函数、日期函数
C.常用查询：嵌套查询（SELECT FROM WHERE），UNION/UNION ALL组合查询，JOIN/LEFT JOIN/RIGHT JOIN/FULL JOIN等连接查询
2.DML（数据操作语言）
INSERT/DELETE/UPDATE 数据操纵分成数据查询和数据更新两类。数据更新又分成插入、删除、和修改三种操作。
3.DDL（数据定义语言）
CREATE/DROP/ALTER 。用于定义SQL模式、基本表、视图和索引的创建和撤消操作。数据定义语言DDL用来创建数据库中的各种对象-----表、视图、索引、同义词、聚簇等如：
CREATE TABLE / VIEW / INDEX / SYN / CLUSTER| 表 视图 索引 同义词 簇。
DDL操作是隐性提交的！不能rollback
SQL Server中每一条select、insert、update、delete语句都是隐形事务的一部分，
显性事务用BEGIN TRANSACTION明确指定事务。
4.TCL（事务控制语言）
常用于脚本开发，commit/rollback 包括对基本表和视图的授权，完整性规则的描述，事务控制等内容。
数据库事务具有原子性、一致性、隔离性和持久性。
并发访问数据库时，各并发事务之间数据库是独立的
事务是最小的执行单位，不允许分割；
执行事务前后，数据保持一致，对同一数据读取的结果相同；
一个事务被提交后对数据库中数据的改变是持久的。
5.DCL（数据控制语言）
常用于数据库授权，grant/revoke 包括对基本表和视图的授权，完整性规则的描述，事务控制等内容。
数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。如：
1) GRANT：授权。
2) ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。回滚
---ROLLBACK回滚命令使数据库状态回到上次最后提交的状态。
其格式为：SQL>ROLLBACK;
3) COMMIT [WORK]：提交。在数据库的插入、删除和修改操作时，只有当事务在提交到数据库时才算完成。
在事务提交前，只有操作数据库的这个人才能有权看到所做的事情，别人只有在最后提交完成后才可以看到。
SQL语言共分为四大类：数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL。
语法
CONCAT(A,B) 字符串拼接
UPPER/LOWER 大小写转换
DISTINCT 删除重复值
根据sql的语法规则，我们不能将数字作为标识的开头。
SELECT @@ERROR 返回最后一个T_SQL错误的错误号
SELECT @@IDENTITY 返回最后一个插入的标识值；
SELECT @@ROWCOUNT 返回受上一个SQL语句影响的行数
SELECT @@MAX_CONNECTIONS 显示可以创建的同时链接的最大数目
授权与角色
GRANT SELECT,DELETE,INSERT
ON 表名(字段1，字段2)
TO 用户1，用户2
WITH GRANT OPTION
收回权限：
REVOKE INSERT/SELECT等 ON TABLE 表名 FROM 用户名
注意：授权用户用TO收回用FROM
给数据库赋予登录权限的语句是GRANT USAGE ON 库.表 TO 用户
字符串函数
1.Charindex()函数
返回字符或字符串在另一个字符的起始位置CHARINDEX(查找字符，被查字符，开始位置)
通过CHARINDEX如果能够找到对应的字符串，则返回该字符串位置i
（有效位置范围为1<= i <= length(input)），否则返回0。注意位置是从1开始。
基本语法如下：
CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )
expressionToFind ：目标字符串，就是想要找到的字符串，最大长度为8000 。
expressionToSearch ：用于被查找的字符串。
2. SUBSTRING()函数
开始查找的位置，为空时默认从第一位开始查找。
SubString()截取字符串中的一部分字符。SUBSTRING（字符串，开始位置，截取长度）
SUBSTRING(字符串，起始位置，截取长度)
3.Stuff()函数
删除指定长度的字符，并在指定的起点处插入另一组字符。
STUFF函数(列名,开始位置,长度,替代字符串)
例子：
查询语句select stuff('lo ina',3, 1, 've ch')结果为“love china”
在第3个位置删除掉1个字符并插入‘ve ch’，得到“love china”。
4.Soundex()函数
返回表示字符串声音的一串字符
5. COALESCE函数
返回字符串中的第一个非空值，如果都是空，返回空值
6. TRUNCATE(数字,位数) 截取
7. ROUND(数字，位数N) 四舍五入 N保留小数点的位数
汇总函数
COUNT ,SUM .AVG, MAX,MIN()函数
Count函数
不计算重复值 count(distinct 字段)
1. count(*)：包括了所有列，相当于行数，统计结果不会忽略某些列值为NULL的行。
2. count(1)：用1代表代码行，忽略所有列的值，统计所有行，效果同count(*)。
3. count(列名)：只包括列名对应一列，统计结果，会忽略列值为空的行。
例：Mysql中表student_table(id,name,birth,sex)，插入如下记录：
('1001' , ' ' , '2000-01-01' , '男');
('1002' , null , '2000-12-21' , '男');
('1003' , NULL , '2000-05-20' , '男');
('1004' , '张三' , '2000-08-06' , '男');
('1005' , '李四' , '2001-12-01' , '女');
执行 select count(name) from student_table 的结果是（3）
count(name) 时不包括null值，所以结果是3；
插入时null与NULL的意思一样都是NULL。
REGEXP正则表达式规则
MySQL 中使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式。
^ 该符号表示匹配输入字符串的开始位置；
$ 表示匹配输入字符串的末尾位置；
[...] 表示匹配所包含的任意一个字符；
[^ charlist]表示不能匹配括号内的任意单个字符；
x|y 这条竖线表示匹配x 或匹配y。
有一张Person表包含如下信息：
Id Name Address Career
1 Bob China Town Chef
2 Carter Oxford Street Teacher
3 Anna Fourteen Avenue Dancer
选取居住地址Address以'C'或'O'开头的人员信息：
SELECT * FROM Person Address REGEXP '^[CO]';
选取居住地址Address不以'C'或'O'开头的人员信息：
SELECT * FROM Person Address REGEXP '^[^CO]';
或 SELECT * FROM Person Address NOT REGEXP '^[CO]';
选取居住地址Address以'C'或'O'开头或'et'结尾的人员信息：
SELECT * FROM persons WHERE Address REGEXP '^[CO]|et$';
SQL注入
单引号作为MySQL中的字段值封装方式，最容易被用作sql注入攻击
视图
创建视图
将视图存入临时表的操作，然后用临时表来执行语句；
CREATE ALGORITHM={TEMPTABLE} VIEW view_room AS ----临时表
SELECT student.sname, student.s_test_id, room.rid, room.rseat
FROM student,room WHERE student.s_test_id=room.s_test_id;
CREATE VIEW view_room (sname,s_test_id,rid,rseat) AS
SELECT student.sname, student.s_test_id, room.rid, room.rseat
FROM student,room WHERE student.s_test_id=room.s_test_id;
CREATE VIEW view_room AS
SELECT student.sname, student.s_test_id, room.rid, room.rseat
FROM student,room WHERE student.s_test_id=room.s_test_id;
另外用法：先将所需表建立内连接，形成临时表，再在单表上建立视图。
SELECT question.qid, set_teacher.tname, view_teacher.tname,set_teacher.s_tid, view_teacher.v_tid
FROM question, set_teacher, view_teacher AS question_2
WHERE question.s_tid=set_teacher.s_tid
AND question.v_tid=view_teacher.v_tid;
CREATE VIEW view_question (qid,s_tname,r_tname)
AS SELECT question.qid, set_teacher.tname, view_teacher.tname
FROM question_2;
2.修改视图用ALTER VIEW
3.删除视图用DROP VIEW 注意没有DELETE VIEW
知识点：
视图是SQL语句的结果集的可视化表。
视图不可以存放数据，存放的是SQL查询语句，它是一张临时表；
在视图中也可以定义新的视图，却无法创建表，因为视图是个虚表。
视图是虚拟表，视图所引用的表称为视图的基表。
视图可以被嵌套，一个视图中可以嵌套另一个视图
当SELECT语句的选择列表有TOP子句时，视图可以包含ORDER BY子句；
视图不能对临时表或表变量进行引用；
sp_helptext用于获取自定义视图创建的T_SQL文本，
更新视图数据可用sp_refreshview。
游标
声明游标语法中的INSENSITIVE参数，表示声明一个静态游标。下列选项，游标将会自动设定INSENSITIVE选项
1.当SELECT语句中使用DISTINCT、GROUP BY、HAVING, UNION语句时；
2.使用OUTER JOIN；非INNER JOIN;
3.为所选取的任意表没有索引。
4.将实数值当作选取的列。
游标是一种从包括多条数据记录的结果集中每次提取一条记录以便处理的机制，可以看做是查询结果的记录指针。
游标允许定位在结果集的特定行；
从结果集的当前位置(注意：非目标位置)检索一行或一部分行；
支持对结果集中当前位置的行进行数据修改。
为由其他用户对显示在结果集中的数据所做的更改提供不同级别的可见性支持
数据库函数
1.用户定义函数不用于执行修改数据库状态的操作；
2.用户定义函数属于数据库，只能在该数据库下调用；
3.标量函数和存储过程一样，可以使用EXECUTE语句执行。
4.与系统函数一样，用户定义函数可以从查询中调用
触发器
CREATE TRIGGER 触发器名
ON 表名
AFTER INSERT,UPDATE或DELETE操作
AS
BEGIN
语句
END
日期函数
YEAR()
MONTH()
date_format(order_date, '%Y-%m') = '2020-01'
DATE_ADD() 函数向日期添加指定的时间间隔。date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。
DATEDIFF(datepart, startdate, enddate) 函数返回两个日期之间的时间。
TIMESTAMPDIFF(SECOND, start_time, end_time) 两日期相减并转换为秒S
例：BORROW表中日期(RDATE字段)为当天
select * from BORROW where datediff(dd,RDATE,getdate())=0
函数group_concat()
场景：
在mysql的关联查询或子查询中，函数 group_concat(arg) 可以合并多行的某列(或多列)数据为一行，默认以逗号分隔。以及分组函数和统计函数的组合使用
order by desc/asc limit offset
limit n,m, n指定第一个返回记录行的偏移量，m指定返回记录行的最大数目。
select * from tablename limit 0,1 取出第一条记录
select * from tablename limit 1,1 第二条记录
Select * from tablename limit 10,20 从第11条到第31条记录
当 limit和offset组合使用的时候，limit后面只能有一个参数，表示要取的数量,offset表示要跳过的数量 。
select * from article LIMIT 3 OFFSET 1 = select * from article LIMIT 1,3
表示跳过1条数据,从第2条数据开始取，取3条数据，也就是取2,3,4三条数据
Order by id asc 升序 desc 降序
//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：
mysql>SELECT*FROM table LIMIT 95,-1; // 检索记录行 96-last.
分组函数
GROUP BY /HAVING
having子句是对group by 的分组结果指定条件
select 查询结果
from 从那张表中查找数据
where 查询条件（运算符、模糊查询）
group by 分组（每个）
having 对分组结果指定条件
order by 对查询结果排序
limit 从查询结果中取出指定行;
例：查询至少选择两门课程的学生学号
SELECT 学号,COUNT(DISTINCT 课程号)
FROM score
GROUP BY 学号
HAVING COUNT(DISTINCT 课程号)>=2;
MySQL查询执行顺序
MySQL查询语句的正确执行顺序：
FROM(including JOINs) ---> WHERE ---> GROUP BY ---> HAVING ---> SELECT ---> DISTINCT ---> ORDER BY ---> LIMIT/OFFSET
错误顺序：
SELECT ---> DISTINCT ---> FROM(including JOINs) ---> WHERE ---> GROUP BY ---> HAVING ---> ORDER BY ---> LIMIT/OFFSET
1.先找到要查询表格或连接要查询的表格，因此FROM才是第一步；
2.WHERE条件筛选；
然后如果遇到表格有分组的需要，则需要先GROUP BY；
分组时如果也存在筛选条件，这里就要用HAVING进行分组筛选；
这些执行过后才是查询操作SELECT；
SELECT的时候如果遇到重复数据，就需要去重，即使用DISTINCT;
接下来如果要对查询后的数据进行排序，会用到ORDER BY；
最后如果要指定返回的查询数据范围、条数则要用LIMIT/OFFSET函数。
多表查询
子查询
All()：对所有数据都满足条件，整个条件才成立，>=all()等价于max，<=all()等价于min；
Any：只要有一条数据满足条件，整个条件成立，>any()等价于>min，<any()等价于<max；
some的作用和Any一样
ANY子查询
找出成绩比课程0002的全部成绩中的任一成绩高的学生
SELECT 学号,成绩
FROM score
WHERE 成绩 > ANY(SELECT 成绩 FROM score WHERE 课程号='0002');
ALL子查询
-- 找出成绩比课程0002的全部成绩中的全部成绩都高的学生
/*
大白话翻译：查找课程0002的全部成绩；某个学生的全部成绩大于刚才的成绩就符合条件；
SELECT 成绩 FROM score WHERE 课程号='0002';
SELECT 学号,成绩 FROM score WHERE 成绩>ALL<子查询>;
*/
SELECT 学号,成绩 FROM score WHERE 成绩 > ALL(SELECT 成绩 FROM score WHERE 课程号='0002');
注意，子查询有以下注意事项：
子查询必须被圆括号()括起来；
子查询只能在有一列的SELECT子句中；
子查询在where语句中，子查询不可以对all子句进行运算（all 得到的是n行数据）；
养成良好的书写习惯，不要省略子查询的别名；
ORDER BY不能在子查询中使用。
标量子查询
标量子查询是返回结果只有一个单一值的子查询。所以标量子查询可以可比较运算符一起使用。
SELECT 学号,成绩 FROM score WHERE 成绩 > (SELECT AVG(成绩) FROM score);
关联子查询
每一科成绩都大于该科平均成绩学生的学号和成绩
SELECT 学号,课程号,成绩 FROM score as s1 WHERE 成绩 >
(SELECT AVG(成绩) FROM score as s2 WHERE s1.课程号=s2.课程号 GROUP BY 课程号);
表的增删改操作
更新
正确的UPDATE更新语法为：
UPDATE 表名 SET field1 = new-value1, field2 = new-value2 [WHERE Clause];
错误写法：
UPDATE 表名 SET field1 = new-value1 and field2 = new-value2 [WHERE Clause];
新增：
replace into 跟 insert into功能类似，不同点在于：replace into 首先尝试插入数据到表中，如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。否则，直接插入新数据。
注意插入数据的表必须有主键或者是唯一索引,否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据
REPLACE函数批量修改数据：
REPLACE INTO 表名(field1, field2...) VALUES (value1, value2)...;
INSERT INTO 表名(field1, field2...) VALUES (value1, value2)...;
字段名与字段值顺序一致即可，可以给部分或所有字段名加``(反引号非单引号)。
INSERT INTO 语句用于向一张表中插入新的行。
SELECT INTO 语句从一张表中选取数据插入到另一张表中。常用于创建表的备份复件或者用于对记录进行存档。
某打车公司要将驾驶里程（drivedistanced）超过5000里的司机信息转存到一张称为seniordrivers 的表中，他们的详细情况被记录在表drivers 中，
正确写法：select * into seniordrivers from drivers where drivedistanced >=5000
错误写法：insert into seniordrivers(drivedistanced) values from drivers where drivedistanced>=5000
注意：字段名可以用反引号或不用，不能用单引号
insert into student_info('id','name','birth','sex') values('1016' , '王五' , '2003-03-01' , '男'); ----错误，字段名不能用单引号
insert into student_info(`id`,name,`birth`,`sex`) values('1016' , '王五' , '2003-03-01' , '男'); -----正确，字段名可以用反引号
insert into student_info(id,name,birth,sex) values('1016' , '王五' , '2003-03-01' , '男'); -----正确，直接用字段名
删除：
删除记录的方式汇总：
1.根据条件删除：DELETE FROM tb_name [WHERE options] [ [ ORDER BY fields ] LIMIT n ]
2.全部删除（表清空，包含自增计数器重置）：
TRUNCATE TABLE tb_name 只清除数据
truncate table 比delete速度更快，且使用的系统和事务日志资源少。
truncate 删除表中的所有行，但表的结构及其列，约束，索引等保持不变。
3.如果要删除表定义及其数据，请使用drop table 语句
区别：
1：处理效率：drop> truncate >delete
2：删除范围：
drop删除整个表（结构和数据一起删除）；
truncate 删除全部记录，但不删除表结构；
delete只删除数据
3：高水位线：
delete不影响自增ID值，高水线保持原位置不动；
truncate会将高水线复位，自增ID变为1。
创建表
1.1 创建表
CREATE TABLE
[IF NOT EXISTS] tb_name -- 不存在才创建，存在就跳过
(column_name1 data_type1 -- 列名和类型必选
[ PRIMARY KEY -- 可选的约束，主键
| FOREIGN KEY -- 外键，引用其他表的键值
| AUTO_INCREMENT -- 自增ID
| COMMENT comment -- 列注释（评论）
| DEFAULT default_value -- 默认值
| UNIQUE -- 唯一性约束，不允许两条记录该列值相同
| NOT NULL -- 该列非空
], ...
) [CHARACTER SET charset] -- 字符集编码
[COLLATE collate_value] -- 列排序和比较时的规则（是否区分大小写等）
1.2 从另一张表复制表结构创建表： CREATE TABLE tb_name LIKE tb_name_old
1.3 从另一张表的查询结果创建表： CREATE TABLE tb_name AS SELECT * FROM tb_name_old WHERE options
2.1 修改表
ALTER TABLE 表名 修改选项 。选项集合：
{ ADD COLUMN <列名> <类型> -- 增加列
| CHANGE COLUMN <旧列名> <新列名> <新列类型> -- 修改列名或类型
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT } -- 修改/删除 列的默认值
| MODIFY COLUMN <列名> <类型> -- 修改列类型
| DROP COLUMN <列名> -- 删除列
| RENAME TO <新表名> -- 修改表名
| CHARACTER SET <字符集名> -- 修改字符集
| COLLATE <校对规则名> } -- 修改校对规则（比较和排序时用到）
3.1 删除表
DROP TABLE [IF EXISTS] 表名1 [, 表名2]。
索引创建、删除与使用：
1.1 create方式创建索引：
CREATE
[UNIQUE -- 唯一索引
| FULLTEXT -- 全文索引
] INDEX index_name ON table_name -- 不指定唯一或全文时默认普通索引
(column1[(length) [DESC|ASC]] [,column2,...]) -- 可以对多列建立组合索引
1.2 alter方式创建索引：ALTER TABLE tb_name ADD [UNIQUE | FULLTEXT] [INDEX] index_content(content)
2.1 drop方式删除索引：DROP INDEX <索引名> ON <表名>
2.2 alter方式删除索引：ALTER TABLE <表名> DROP INDEX <索引名>
3.1 索引的使用：
索引使用时满足最左前缀匹配原则，即对于组合索引(col1, col2)，在不考虑引擎优化时，条件必须是col1在前col2在后，或者只使用col1，索引才会生效；
索引不包含有NULL值的列
一个查询只使用一次索引，where中如果使用了索引，order by就不会使用
like做字段比较时只有前缀确定时才会使用索引
在列上进行运算后不会使用索引，如year(start_time)<2020不会使用start_time上的索引
例子：
CREATE INDEX idx_duration ON 表(字段);
CREATE UNIQUE INDEX uniq_idx_exam_id ON 表(字段);
CREATE FULLTEXT INDEX full_idx_tag ON 表(字段);
删除索引
方式1：
DROP INDEX 索引名 ON 表;
DROP INDEX 索引名 ON 表;
方式2：
alter table 表 drop index 索引名;
alter table 表 drop index 索引名;
注意：
唯一索引不允许两行具有相同的索引值，包括NULL值，允许有空值；
每个表只允许有一个全文索引；
非聚集索引具有完全独立于数据行的结构，所以不需要将物理数据页中的数据按列重新排序。
4.Which country has a population that is more than Canada but less than Poland? Show the name and the population.
select name,population from world where population between (select population from world where name = 'Canada')+1 and (select population from world where name = 'Poland')-1
注意，between的查找边界是包括边界值的，而这里要求“more than ··· but less than”，是大于一个值，小于另一个值，所以我们需要+1和-1；
5.Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.
select name,concat(round(population/(select population from world where name = 'Germany')*100,0),'%') from world where continent = 'Europe';
concat(a,B) 字符串拼接
6.Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values)
select name from world where gdp> all(select gdp from world where continent = 'Europe'and gdp>0 );
7.Find the largest country (by area) in each continent, show the continent, the name and thearea:
select name,continent,area from world as w1 where area >= all(select area from world as w2 where w1.continent=w2.continent and area>0);
如何对多表进行查询？
目录：
表的加法
表的联结
case表达式
union操作符可以合并多个select语句的结果集。
需要注意的2点：
union内部的select语句必须有相同数量的列；列名顺序必须相同且数据类型必须相似。
一般默认union操作符相加的是不同的值，如果允许重复需要全部显示，可以使用union all操作符：
把课程表course 和 course_add 结合,不允许重复
SELECT * FROM course UNION SELECT * FROM course_add;
-- 把课程表course 和 course_add 结合,允许重复
SELECT * FROM course UNION ALL SELECT * FROM course_add;
inner join 内联结：返回两个表可匹配的行；
left join 左联结：即使左表没有匹配，右表返回所有行；右边可以为空
right join右联结：即使右表没有匹配，左表返回所有行；左边可以为空
全连接
/* ① 使用左联结 left join取绿色部分面积；
②使用右联结right union 条件为左列名为null的部分面积；
③ 使用union all 相加
*/
SELECT a.学号,a.姓名,b.课程号,b.成绩 FROM student as a LEFT JOIN score as b ON a.学号=b.学号
UNION ALL
SELECT a.学号,a.姓名,b.课程号,b.成绩 FROM student as a RIGHT JOIN score as b ON a.学号=b.学号 where a.学号 IS NULL;
5）case表达式
# 查询出每门课程的及格人数和不及格人数
/*
定义条件：成绩>=60分及格，成绩<60分不及格；
按课程号进行分组，对分组结果的人数按照上一步的逻辑条件计数；
*/
SELECT 课程号,
SUM(CASE WHEN 成绩>=60 THEN 1
ELSE 0
END)AS 及格人数,
SUM(CASE WHEN 成绩<60 THEN 1
ELSE 0
END)AS 不及格人数
FROM score
GROUP BY 课程号;
-- 对各课程的成绩按照[100-85（含）]、[85-70（含）]、[70-60（含）]、[60以下]分段，并统计各分段数人数和课程名
/*
定义条件：成绩在100-85（含）为[100-85（含）]段，在85-70（含）为[85-70（含）]段，在70-60（含）为[70-60（含）]段，小于60就是[60以下]分段；
把成绩表score和课程表course交叉联结（右联结），显示课程号、课程名、和分数段；
按课程号、课程名进行分组，对分组结果的人数按照上上述的逻辑条件计数；
*/
SELECT a.课程号,b.课程名称,
SUM(CASE WHEN 成绩 BETWEEN 85 AND 100 THEN 1 ELSE 0 END)AS '100-80(含)',
SUM(CASE WHEN 成绩 BETWEEN 70 AND 85 THEN 1 ELSE 0 END)AS '85-70(含)',
SUM(CASE WHEN 成绩 BETWEEN 60 AND 70 THEN 1 ELSE 0 END)AS '70-60(含)',
SUM(CASE WHEN 成绩 <60 THEN 1 ELSE 0 END)AS '60以下'
FROM score AS a RIGHT JOIN course AS b
ON a.课程号=b.课程号
GROUP BY a.课程号,b.课程名称;
2.查询平均成绩大于85的所有学生的学号、姓名和平均成绩
SELECT a.学号,a.姓名,AVG(成绩) AS 平均成绩
FROM student AS a LEFT JOIN score as b
ON a.学号 = b.学号
GROUP BY 学号
HAVING AVG(成绩)>85;
窗口函数
rank()排序相同时会重复，总数不变，即会出现1、1、3这样的排序结果；
dense_rank()排序相同时会重复，总数会减少，即会出现1、1、2的排序结果；并列排序
row_number()排序相同时不会重复，会根据顺序排序。
查找每个班级成绩最高的2个学生成绩数据
拆分两步：
1.先按班级分组，并按照成绩降序排列，查看各班成绩排名：
SELECT *,
row_number() over ( PARTITION BY 班级 ORDER BY 成绩 DESC) AS row_num
FROM score_new;
2.只要我们筛选row_num值<=2即可，我们可以用子查询：
SELECT *
FROM
(SELECT *,
row_number() over ( PARTITION BY 班级 ORDER BY 成绩 DESC) AS row_num
FROM score_new) AS a
WHERE row_num <=2;
例子：每个用户2017年10月的第一笔订单金额
先按用户ID分组，并按下单日期升序排序(不写ASC默认是升序排序)
row_number() over ( PARTITION BY user_id ORDER BY date)
例：
在gameList表中(player_id, event_date)是主键，如何显示每个玩家（player_id）首次登录的设备号（device_id），并同时显示玩家ID（player_id）？
SELECT player_id, device_id
FROM（SELECT * ,rank（） over (partition by player_id order by event_date ) as rank_date
from gamelist ）t WHERE t.rank_date=1
2. 查找入职员工时间排名倒数第三的员工所有信息
【知识点】关联子查询，同样两次查询（查员工信息、查时间）都要在employees中获取数据；
注意：对时间排序取倒数第三，需要对时间使用distinct去掉重复值；倒数第三需要跳过2条数据，读取1条数据；我们使用limit y offset x;也可以使用limit x,y 跳过x条数据读取y条数据；
select * from employees where hire_date =
(select distinct hire_date from employees
order by hire_date desc
limit 1 offset 2);
某宝店铺连续2天及以上购物的用户及其对应的天数 ---重点
select
user_id,
count(*) days_count
from
(
select
user_id,
sales_date,
ROW_NUMBER() over ( partition by user_id order by sales_date
) rn
from
sales_tb
) a
group by
user_id,
date_add (sales_date, INTERVAL - rn day)
having
count(*) >= 2
DATE_ADD() 函数向日期添加指定的时间间隔。DATE_ADD(date,INTERVAL expr type)
date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。
知乎问答最大连续回答问题天数大于等于3天的用户及其对应等级
select t2.author_id,author_level,days_cnt
from (
select
author_id
,count(*) days_cnt
from (
select
author_id
,answer_date
,dense_rank()over(partition by author_id order by answer_date) num ---序号
from answer_tb
group by author_id,answer_date ---去重
) t1
group by author_id,date_add(answer_date,interval -num day)---做差，分组
或 group by author_id,date_sub(answer_date,interval num day)
having count(*)>=3
) t2
join author_tb t3
on t2.author_id=t3.author_id
order by t2.author_id
一个用户可能在同一天活跃多次，这样同一天的记录也有多次
所以在编号时使用DENSE_RANK():并列排序，不跳过重复序号——1、1、2
rank()排序相同时会重复，总数不变，即会出现1、1、3这样的排序结果；
dense_rank()排序相同时会重复，总数会减少，即会出现1、1、2的排序结果；并列排序
row_number()排序相同时不会重复，会根据顺序排序。
SQL题目
创建SQL Server登录账户Sql1，密码为“123456”；（2分）
CREATE LOGIN Sql1 WITH PASSWORD = '123456'
创建基于登录账户Sql2的数据库用户Sql2，并为该用户指定默认架构Sale。（3分）
CREATE USER Sql2 FROM LOGIN Sql2 WITH DEFAULT_SCHEMA =Sale
2. 参照第三道应用题所给数据库的部分模式，创建一个函数FunBook，根据用户提供的图书类别名查看相应类别图书的详细信息。
CREATE FUNCTION FunBook(@categoryName nvarchar(50))
RETURNS TABLE
AS RETURN
(SELECT b.*
FROM 图书 b JOIN 图书类别 c ON b.类别代号=c.类别代号
WHERE c.类别名=@categoryName
)
3. 根据第三道应用题所给数据库的部分模式，创建一个触发器TrInsUpd，当向图书表中插入或更新一条记录的类别代号时，新记录的类别代号必须在图书类别表中存在，否则提示类别代号不正确。
CREATE TRIGGER TrInsUpd ON 图书 AFTER INSERT,UPDATE
AS
IF NOT EXISTS(SELECT * FROM inserted WHERE 类别代号 IN(
SELECT 类别代号 FROM 图书类别))
BEGIN
PRINT '类别代号不正确！'
ROLLBACK TRANSACTION
END
4. 参照第三道应用题所给数据库的部分模式，创建一个存储过程PrcSelect，根据用户提供的图书类别名查看相应类别图书的详细信息。
CREATE PROCEDURE 存储过程名 @参数1 (AS可缺省) 参数1数据类型
AS
BEGIN
SELECT b.*
FROM 图书 b JOIN 图书类别 c ON b.类别代号=c.类别代号
WHERE c.类别名=@categoryName
END
5. 根据第三道应用题所给数据库的部分模式，创建一个函数FunBookSale，该函数根据给定的书号返回该图书销售的数量。
CREATE FUNCTION FunBookSale (@bookID int)
RETURNS int
AS
BEGIN
DECLARE @quantity int
SELECT @quantity = SUM(quantity)
FROM OrderItems
WHERE bookID=@bookID
RETURN @quantity
END
八、请按要求完成操作（30分）
某校学生-课程数据库部分关系模式如下：
学生表 Student(Sno,Sname,Sage,Ssex，department)
课程表Course(Cno,Cname, Ccredit，Tno)
成绩表 SC(Sno,Cno, grade)
教师表Teacher(Tno,Tname，tsex)
数据查询（每题2分，共20分）
（4） 查询至少有一门课程与李燕所选课程相同的学生的学号、姓名
select a.sno,sname
from student a,sc b
where a.sno = b.sno and sname <>'李燕' and
cno in(
select distinct cno
from student c,sc d
where c.sno = d.sno and sname = '李燕'
)
求选修了全部课程的学生信息
select *
from student
where not exists(
select * from course
where not exists(
select * from sc
where sno = student.sno and cno = course.cno
)
)
求1995年前出生的学生信息
select *
from student
where (year(getdate())-sage)<1995
查询所有学生的选课情况，包括没有选课的学生
select*
from student left outer join sc ------右边可以为空
on student.sno = sc.sno
查询两门以上不及格课程的同学的学号及其平均成绩
select sno ,avg(grade)
from sc
where sno in (
select sno
from sc
group by sno
having count(case when grade<60 then 1 else 0 end)>= 2
)
检索至少选修两门课程的学生学号
select sno
from sc
group by sno
having count(*) >= 2
2. 请SQL语句完成下列要求（共10分）
（1）创建课程表（2分）
create table course(
cno varchar(5) primary key,
cname varchar(30),
ccredit float,
tno varchar(5),
foreign key (tno) refrences teacher(tno)
（3） 在课程表的教师编号列上建立降序索引。（2分）
create index index_tno on teacher(tno desc)
create index 索引名 on 表(字段 ASC/desc)
（4）在学生表上创建一个触发器trigger_delete，当删除学生信息时，将删除的学生保存到oldstudent表中，oldstudent和student表结构相同。（4分）
create trigger trigger_delete
on student
after delete
as
begin
insert into oldstudent
select * from deleted
end
九. 已知学生数据库中存放了这样的两张表，一张为毕业生信息表(graduation)，记录毕业生的一些基本信息，一张为学生缴费表(fee)，记录了学生的缴费信息。由于学生毕业，我们要从学生信息表中把毕业生的记录删除，但是如果这个学生欠费的话，则不允许删除这个学生的记录。 (字段名都是中文，可以直接使用) (10分)
graduation (学号 姓名 性别 地址 联系方式)
fee(学号 姓名 已交费用 欠费)
1.建立一个存储过程pro_deletestudent在graduation表中删除指定毕业学生的信息，输入参数为学号。
create procedure pro_deletestudent
@sno char(9) ------定义入参名及数据类型
as
begin
delete from graduation where 学号 = @sno
end
2.在graduation表上建立一个触发器tr_checkfee，判断要删除的学生是否欠费，欠费则不允许删除该记录，否则删除该记录
create trigger tr_checkfee
on graduation
after for delete
as
begin
DECLARE @sno char(9);
Select @sno=deleted.sno from deleted
if exists(select * from fee where 学号 = @sno and欠费>0)
rollback
end
十、某书店后台数据库的部分关系模式如下：
图书类别（类别代号，类别名）
图书（书号，书名，ISBN，作者，单价，类别代号）
顾客（顾客编号，姓名，地址，推荐人编号）
推荐人编号表示推荐这名顾客注册的老顾客的编号
订单（订单号，顾客编号，订购日期，出货日期）
订单明细（订单号，书号，数量，总价）
按要求实现下列操作：
1.使用数据定义语言建立顾客、订单明细两张表的结构（注意添加相应的主外键约束）。（6分）
Create table 顾客(
顾客编号 char(25) primary key,
姓名 char(25),
地址 VARCHAR(20),
推荐人编号 char(20),
FOREIGN KEY (推荐人编号) REFERENCES 顾客(顾客编号)
);
Create table 订单明细(
订单号 char(8) primary key,
书号 char(10) primary key,
数量 INT SMALLINT,
总价 NUMC(20,2) NUMBERIC(8,2),
PRIMARY (订单号, 书号),
FOREIGN KEY (订单号) REFERENCES 订单(订单号)
FOREIGN KEY (书号) REFERENCES 图书(书号)
);
5.统计类别代号是CO01的图书册数和单价总和。（3分）
SELECT 书号,COUNT(书号) as 图书册数,SUM(单价) as 单价总和
FROM 图书
Where 类别代号=’CO01’
Group by 书号
6.显示有推荐人的顾客的详细信息以及其推荐人的姓名。（3分）
顾客（顾客编号，姓名，地址，推荐人编号）
SELECT A~顾客编号，A~姓名，A~地址，A~推荐人编号,
( SELECT 姓名 FROM 顾客 WHERE 顾客编号 = A~推荐人编号) AS 推荐人姓名
FROM 顾客 AS A WHERE 推荐人编号 <> ‘’
SELECT c1.*, c2.姓名
FROM 顾客 c1, 顾客 c2
WHERE c1.推荐人编号=c2.顾客编号
8.向表图书中除了类别代号以外的所有列中插入数据，书号为“11”，书名为“英语阅读词汇双突破”, ISBN为“7560922171”,作者为“杨建荣”，单价为19.00。（2分）
图书（书号，书名，ISBN，作者，单价，类别代号）
Insert into 图书（书号，书名，ISBN，作者，单价，类别代号）
Values(‘11’,’英语阅读词汇双突破’,’7560922171’,’杨建荣’,’ 19.00’,null)
9.创建一个视图，要求显示订单编号、书名、ISBN、并且要求书的作者为“王珊”。（3分）
Create view Query_Book
As
Begin
Select c2.订单编号、c1.书名, c1.ISBN
From 图书 c1, 订单明细 c2 Where c1.书号 = c2.书号
From 图书 c1 join订单明细 c2 on c1.书号 = c2.书号
where 作者=’王珊’
End
10.创建一个角色ROLE1，将顾客表的查询、更新、删除的权限授予该角色，并使用该角色对张明、赵强、李峰授权。（4分）
Grant CREATE role ROLE1
QUERY GRANT SELECT, UPDATE ,DETELE GRANT
ON TABLE 顾客
TO ROLE1
USER GRANT ROLE1 TO张明、赵强、李峰
/*Table structure for table `course` */
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cid` int NOT NULL,
`cname` varchar(20) COLLATE utf8mb4_german2_ci DEFAULT NULL,
`tid` int DEFAULT NULL,
PRIMARY KEY (`cid`),
KEY `tid` (`tid`),
CONSTRAINT `course_ibfk_1` FOREIGN KEY (`tid`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_german2_ci;
/*Data for the table `course` */
insert into `course`(`cid`,`cname`,`tid`)
values (1,'计算机与科学技术',2),(2,'毛泽东思想理论',1);
-- 1、查询"1"课程比"2"课程成绩高的学生的信息及课程分数
-- 查询学生的 1 课程 成绩与 2课程成绩
SELECT a.`stu_name`,b.`score,c.`score
FROM student a LEFT JOIN score b ON a.stu_id`=b.`stu_id` AND b.`cid`='01'
JOIN score c ON a.`stu_id` = c.`stu_id` AND c.`cid`='02' WHERE b.`score`>c.`score`
2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
SELECT a.stu_name,b.score,c.score
FROM student a LEFT JOIN score b ON a. stu_id=b.stu_id AND b.cid='01'
JOIN score c ON a.stu_id = c.stu_id AND c.cid='02' WHERE b.score<c.score
-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select s_id from scores where score<60
group by s_id
having count(*)>=2; # 法一
# 写法二
select s_id from
(select * from scores where score score<60 )as a1
group by a1.s_id having count(*)>=2;
# 写法三
select
t1.sid,sname,avg_score
from
(
select
sid,count(if(score<60,cid,null)),avg(score) as avg_score
from sc
group by sid
having count(if(score<60,cid,null)) >=2
)t1
left join student
on t1.sid=student.sid
-- 47、查询本周过生日的学生
Select sid,sname,sage
from student
where weekofyear(sage) = weekofyear(curdate())
-- 48、查询下周过生日的学生
select sid,sname,sage
from student
where weekofyear(sage) = weekofyear(date_add(curdate(),interval 1 week))
-- 49、查询本月过生日的学生
Select sid,sname,sage
from student
where month(sage) = month(curdate())
-- 50、查询下月过生日的学生
Select sid,sname,sage from student
where month(date_sub(sage,interval 1 month)) = month(curdate())
/下面是一个基于图书系统的15道SQL问答，供大家参考
问题描述：
本题用到下面三个关系表：
T_CARD 借书卡。 CNO 卡号，NAME 姓名，CLASS 班级
T_BOOKS 图书。 BNO 书号，BNAME 书名,AUTHOR 作者，PRICE 单价，QUANTITY 库存册数
T_BORROW 借书记录。 CNO 借书卡号，BNO 书号，RDATE 还书日期
备注：限定每人每种书只能借一本；库存册数随借书、还书而改变。
要求实现如下15个处理：
–1． 写出建立T_BORROW表的SQL语句，要求定义主码完整性约束和引用完整性约束。
create table T_CARD
(
cno number(10) primary key,
name varchar2(100),
class varchar2(100)
);
create table T_BOOKS
(
bno number(10) primary key,
name varchar2(100),
author varchar2(100),
price number(10,2),
quantity number(10)
);
create table T_BORROW
(
cno number(10),
bno number(10),
rdate date
);
alter table T_BORROW
add constraint borrow_pk primary key (cno, bno);
alter table T_BORROW
add constraint boorow_fk_cno foreign key (cno) references T_CARD(cno);
alter table T_BORROW
add constraint boorow_fk_bno foreign key (bno) references T_BOOKS(bno);
insert into T_CARD VALUES(1, ‘bryant’, ‘class_1’);
insert into T_CARD VALUES(2, ‘foxus’, ‘class_1’);
题目1：客户活跃度
有一个表VUA，里面有ID，DATE两个变量，
要选出在3.5号出现的用户ID，在后三天3.21-3.23出现的总次数统计
建表语句：
create table vua
(
id varchar(20) not null,
date datetime
);
insert into vua values('1','3-5');
insert into vua values('1','3-6');
insert into vua values('1','3-7');
insert into vua values('1','3-8');
insert into vua values('2','3-5');
insert into vua values('2','3-6');
查询语句：
select ID,COUNT(ID)
FROM vua A
WHERE A.DATE BETWEEN '3-6'AND '3-8'
AND ID IN ( SELECT DISTINCT ID FROM vua WHERE DATE='3-5')
GROUP BY ID
题目2：得分排名
求每一门科目，成绩排名前两名的同学
create table tabscore(
c_id varchar(20) not null ,
stuID varchar(40) not null,
score varchar(40) not null
);
INSERT INTO tabscore VALUES ('1', '1', '85.0');
INSERT INTO tabscore VALUES ('2', '1', '89.0');
INSERT INTO tabscore VALUES ('1', '2', '85.0');
INSERT INTO tabscore VALUES ('2', '2', '80.0');
INSERT INTO tabscore VALUES ('1', '3', '85.0');
INSERT INTO tabscore VALUES ('2', '3', '99.0');
查询语句：
select * from(
select c_id,stuID,score,
row_number() over(partition by c_id order by score desc) rank
from tabscore ) t
where rank <=2
select a.c_id,a.stuID,
(select count(distinct score) from tabscore
where c_id =a.c_id and score >=a.score group by c_id) rank
from tabscore a
order by a.c_id
题目3：行列转置
1.列转横
解答：
使用case when语句进行行转列操作
select ID,
SUM( CASE ITEM WHEN 'A' THEN SCORE ELSE 0 END) AS 'A',
或 sum(IF(ITEM='S',score,0)) as ‘A’,
SUM( CASE ITEM WHEN 'B' THEN SCORE ELSE 0 END) AS 'B',
SUM( CASE ITEM WHEN 'C' THEN SCORE ELSE 0 END) AS 'C'
from vua group by ID
建表语句：
create table vua
(
ID varchar(20) not null,
ITEM varchar(20) not null,
SCORE varchar(20) not null
);
insert into vua values('1','A','98');
insert into vua values('1','B','89');
insert into vua values('1','C','78');
insert into vua values('2','A','79');
insert into vua values('2','B','88');
insert into vua values('2','C','100');
不用聚合函数à
à
select name ,
max(case when subject='语文' then score else 0 end) as 语文 ,
max(case when subject='数学' then score else 0 end) as 数学 , max(case when subject='英语' then score else 0 end) as 英语 from test group by name ;
以窗口函数进行聚合的案例---看不懂
select name ,
max(case when subject='语文' then score else 0 end) over( partition by name ) 语文 ,
max(case when subject='数学' then score else 0 end) over( partition by name ) 数学 ,
max(case when subject='英语' then score else 0 end) over( partition by name ) 英语 from test ;
à
select name ,
sum(case when subject='语文' then score else 0 end) as 语文 , sum(case when subject='数学' then score else 0 end) as 数学 , sum(case when subject='英语' then score else 0 end) as 英语 from test group by name ;
2.横转列
解答：
select ID,'A' AS ITEM, A AS 'SCORE'
from vua
UNION ALL
select ID,'B' AS ITEM, B AS 'SCORE'
from vua
UNION ALL
select ID,'C' AS ITEM, C AS 'SCORE'
from vua
ORDER BY ID ASC
建表语句：
create table vua
(
ID varchar(20) not null,
A varchar(20) not null,
B varchar(20) not null,
C varchar(20) not null
);
insert into vua values('1','98','89','78');
insert into vua values('2','79','88','100');
sql 语句进阶操作
（count、sum、case-when、group_concat、order by limit offset、dense_rank rank、exis等解析及应用
SQL | 44道经典 SQL 笔试题与答案解析
https://cloud.tencent.com/developer/article/1610942
01 建表语句
create table Student(sid varchar(10),sname varchar(10),sage datetime,ssex nvarchar(10));
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' , '男');
create table Course(cid varchar(10),cname varchar(10),tid varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(tid varchar(10),tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
create table SC(sid varchar(10),cid varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
02 表结构预览
--学生表 Student(SId,Sname,Sage,Ssex)
--SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
--课程表 Course(CId,Cname,TId)
--CId 课程编号,Cname 课程名称,TId 教师编号
--教师表 Teacher(TId,Tname)
--TId 教师编号,Tname 教师姓名
--成绩表 SC(SId,CId,score)
--SId 学生编号,CId 课程编号,score 分数
1. 查询“01”课程比“02”课程成绩高的所有学生的学号
Select a.SId
From SC as a join SC as b
Where a. SId = b. SId and a.Cid = ‘01’ and b.Cid = ‘02’
And a. score > b. score
select distinct t1.sid as sid from
(select * from sc where cid='01') t1
left join
(select * from sc where cid='02') t2
on t1.sid=t2.sid
where t1.score > t2.score
2. 查询平均成绩大于60分的同学的学号和平均成绩；
Select sid ,avg(score) as score from SC where avg(score) > 60 group by sid
Select sid ,avg(score) as score from SC group by sid having avg(score)>60
知识点是group by与having条件组合使用
3. 查询所有同学的学号、姓名、选课数、总成绩
Select student.sid as sid,sname,
count(distinct cid) course_cnt,
sum(score) as total_score
from student left join sc -----右边可以为空，可以有的课程没有分数
on student.sid=sc.sid
group by sid,sname
4. 查询姓“李”的老师的个数；
Select count(*) count(distinct tid) as teacher_cnt
from Teacher where Tname like ‘李%’
5. 查询没学过“张三”老师课的同学的学号、姓名；
Select SId,Sname from Student where SId not exists in
( select SC.Sid from SC
right join Course on SC.CId = Course.Cid -----左边可以为空
right Join Teacher on on Teacher.TId = Course.TId where Tname = ‘张三’ )
6. 查询学过“01”并且也学过编号“02”课程的同学的学号、姓名；
select t.sid as sid, sname --------没看懂！
from
(
select
sid
,count(if(cid='01',score,null)) as count1
,count(if(cid='02',score,null)) as count2
from sc
group by sid
having count(if(cid='01',score,null))>0 and count(if(cid='02',score,null))>0
) t
left join student
on t.sid=student.sid
7. 查询学过“张三”老师所教的课的同学的学号、姓名；
Select student.sid,sname
from ( Select distinct cid
from course
left join teacher
on course.tid = teacher.tid
where teacher.tname = '张三'
) course
left join sc on course.cid = sc.cid
left join student on sc.sid = student.sid
group by student.sid,sname
8. 查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名；
select t1.sid, Student.sname
from ( select distinct t1.sid as sid from
(select * from sc where cid = '01') t1
left join (select * from sc where cid = '02') t2
on t1.sid = t2.sid
where t1.score > t2.score
)t1
left join student on t1.sid = student.sid
9. 查询所有课程成绩小于60分的同学的学号、姓名；
Select t1.sid,sname
from
(
Select sid,max(score)
from sc
group by sid
having max(score<60)
)t1
left join student
on t1.sid = student.sid
找出所有连续未登录5天及以上的用户并提取出这些用户最近一次登录的日期
创建用户登录数据表
CREATE TABLE user_login(
user_id INT,
login_date DATE
);
-- 插入模拟数据
INSERT INTO user_login VALUES
(1, '2022-01-01'),
(1, '2022-01-02'),
(1, '2022-01-03'),
(1, '2022-01-05'),
(1, '2022-01-06'),
(1, '2022-01-09'),
(1, '2023-01-01'),
(2, '2022-01-01'),
(2, '2022-01-03'),
(2, '2022-01-04'),
(2, '2022-01-06'),
(2, '2022-01-07'),
(2, '2022-01-08'),
(3, '2022-01-01'),
(3, '2022-01-02'),
(3, '2022-01-04'),
(3, '2022-01-05'),
(3, '2022-01-07'),
(3, '2022-01-08');
--找出所有连续未登录5天及以上的用户并提取出这些用户最近一次登录的日期
select t1.user_id ,t2.recent_login_date from
( select user_id , login_date , datediff(login_date ,lag(login_date) over(partition by user_id order by login_date ) ) as dt from user_login ) t1
left join (select max(login_date) recent_login_date,user_id from user_login group by user_id )t2
on t2.user_id = t1.user_id where t1.dt >=5
【错题集】
4.Mysql中表student_table(id,name,birth,sex)，查询男生、女生人数分别最多的3个姓氏及人数，正确的SQL写法是：
select * from (
SELECT sex ,substr(name,1,1) as first_name ,count(*) as c1
from student_table
where length(name) >=1 and sex = '男'
group by first_name
order by sex ,c1 desc limit 3
) t1
UNION all
select * from (
SELECT sex ,substr(name,1,1) as first_name ,count(*) as c1
from student_table where length(name) >=1 and sex = '女'
group by first_name
order by sex ,c1 desc limit 3
) t2 ;
错误写法：执行报错，limit需要用在子句中；
SELECT sex ,substr(name,1,1) as first_name ,count(*) as c1
from student_table where length(name) >=1 and sex = '男'
group by first_name
order by sex ,c1 desc limit 3
union all
SELECT sex ,substr(name,1,1) as first_name ,count(*) as c1
from student_table where length(name) >=1 and sex = '女'
group by first_name
order by sex ,c1 desc limit 3 ;
A中【right join 】是以右表为主表，结果会包含name不重复的记录，则A错误。
inner join时只会对非NULL的记录做join,并且2边都有的才会匹配上，结果只有'张三'1行
length()函数
Mysql中表student_table(id,name,birth,sex)，插入如下记录：
('1001' , '' , '2000-01-01' , '男');
('1002' , null , '2000-12-21' , '男');
('1003' , NULL , '2000-05-20' , '男');
('1004' , '张三' , '2000-08-06' , '男');
('1005' , '李四' , '2001-12-01' , '女');
执行 select * from student_table where length(name) >= 0 的结果行数是（）？
注意：1001和1002是不一样的，一个是空字符串，一个是NULL。针对这两种数据使用length()函数结果是不相同的。
针对1001 length(‘’)结果是0，
针对1002、1003 length(NULL)结果是空值NULL
因此题干where length(name) >= 0 会筛选出 1001、1004、1005三条数据
5.Mysql中表student_table(id,name,birth,sex)，删除name重复的id最大的记录，比如'张三'重复2次，id分别是1、2，则删除id=2的记录，保留id=1的记录。正确的SQL
delete t0
from student_table t0
inner join (
select t2.*
from
(select name,count(*) as c1 from student_table GROUP BY name having c1 > 1)t1
left join
(select name, max(id) as id from student_table group by name ) t2
on t1.name = t2.name ) t3
on t0.id = t3.id ;
错误写法：
执行报错【1241 - Operand should contain 1 column(s)】，in的子句中只能含有1个字段，t2.*含有2个字段，写成t2.id才对。
delete from student_table where id in (
select t2.*
from
(select name,count(*) as c1 from student_table GROUP BY name having c1 > 1)t1
left join
(select name, max(id) as id from student_table group by name ) t2
on t1.name = t2.name ) ;
6.Mysql中表student_table(id,name,birth,sex)，score_table(stu_id,subject_name,score)，查询每个学生的分数最高的学科以及对应分数、学生明细记录 没看懂！
select t3.*,t2.subject_name,t2.score
from (
select stu_id,max(score) as c1 from score_table group by stu_id
) t1
inner join
(
select * from score_table
)t2 on t1.stu_id = t2.stu_id and t1.c1 = t2.score
inner join student_table t3
on t1.stu_id = t3.id;
分数最高的那个学科的分数和那位学生，只有1条记录：
select t3.*,t2.subject_name,t2.score
from (
select stu_id,max(score) as c1 from score_table group by stu_id order by c1 desc limit 1
) t1
inner join
(
select * from score_table
)t2 on t1.stu_id = t2.stu_id and t1.c1 = t2.score
inner join student_table t3
on t1.stu_id = t3.id;
7.Mysql中表student_table(id,name,birth,sex)，插入如下记录：
('1001' , '' , '2000-01-01' , '男');
('1002' , null , '2000-12-21' , '男');
('1003' , NULL , '2000-05-20' , '男');
('1004' , '张三' , '2000-08-06' , '男');
('1005' , '李四' , '2001-12-01' , '女');
执行 select * from student_table where name <> '张三' 的结果行数是（2） 错误4
where name <> '张三' 会把NULL的值对应的记录排除掉，所以不包含null和NULL的记录；所以只有''和'李四'才符合条件，故只有2条记录！
8.表student_table(id,name,birth,sex)，id字段值可能重复，分别查询男生、女生的不重复id总数SQL
A
select
sum(distinct case when sex='男' then 1 else null end) as man_ids,
sum(distinct case when sex='女' then 1 else null end) as women_ids
from student_table;
B
select
count(distinct case when sex='男' then id else null end) as man_ids,
count(distinct case when sex='女' then id else null end) as women_ids
from student_table;
解析：A的man_ids、women_ids结果都是1，1。遇到第一条记录时，结果是1，累加后是1，之后结果都是1，distinct后结果则是null，所以count或sum后的结果都是1，1。没看懂！
9.Mysql中表student_table(id,name,birth,sex)，插入如下记录：
('1004' , '张三' ,'2000-08-06' , '男');
('1009' , '李四', '2000-01-01', '男');
('1010' , '李四', '2001-01-01', '男');
('1006' , '王五', '2000-08-06' , '女');
('1008' , '张三', '2002-12-01', '女');
('1012' , '张三', '2001-12-01', '女');
('1011' , '李四', '2002-08-06' , '女');
执行
select t1.*,t2.*
from (
select * from student_table where sex = '男' ) t1
left join
(select * from student_table where sex = '女')t2
on t1.name = t2.name ;
的结果行数是（4）？
on中相同的key匹配上时会产生笛卡尔积！1*2+2*1=4。
10.Mysql中表student_table(id,name,birth,sex)，插入如下记录：
('1003' , '' , '2000-01-01' , '男');
('1004' , '张三' , '2000-08-06' , '男');
('1005' , NULL , '2001-12-01' , '女');
('1006' , '张三' , '2000-08-06' , '女');
('1007' , ‘王五’ , '2001-12-01' , '男');
('1008' , '李四' , NULL, '女');
('1009' , '李四' , NULL, '男');
('1010' , '李四' , '2001-12-01', '女');
执行
select t1.*,t2.*
from (
select * from student_table where sex = '男' ) t1
inner join
(select * from student_table where sex = '女')t2
on t1.birth = t2.birth and t1.name = t2.name ;
的结果行数是（1）？
题目中【inner join ... on t1.birth = t2.birth and t1.name = t2.name ; 】
inner join意思是左右表中的birth、name都不为NULL时才会匹配上，结果中不含有一个字段为NULL或两个字段都为NULL的记录，结果只有‘张三’一条记录。
11. Mysql中表student_table(id,name,birth,sex)，插入如下记录：
('1004' , '张三' , '2000-08-06' , '男');
('1005' , NULL , '2001-12-01' , '女');
('1006' , '张三' , '2000-08-06' , '女');
('1007' , ‘王五’ , '2001-12-01' , '男');
('1008' , '李四' , NULL, '女');
('1009' , '李四' , NULL, '男');
('1010' , '李四' , '2001-12-01', '女');
执行
select count(t2.name) as c1
from (
select * from student_table where sex = '男' ) t1
left join
(select * from student_table where sex = '女')t2
on t1.birth = t2.birth and t1.name = t2.name ;
的结果行数是（1）？
解析：
题目中【left join ... on t1.birth = t2.birth and t1.name = t2.name ; 】
left join意思是包含inner join的结果（左右表中的birth、name都不为NULL时才会匹配上），
无法匹配t2中一个字段为NULL或两个字段都为NULL的记录（所以t2结果不含有'李四'、 '2001-12-01' ），查询结果如下图，count(t2.name)不包含NULL记录，所以结果是1。
12. Mysql中表student_table(id,name,birth,sex)，插入如下记录：
('1001' , '' , '2000-01-01' , '男');
('1002' , null , '2000-12-21' , '男');
('1003' , NULL , '2000-05-20' , '男');
('1004' , '张三' , '2000-08-06' , '男');
('1005' , '李四' , '2001-12-01' , '女');
('1006' , '张三' , '2001-12-02' , '女');
执行
select t1.name ,t2.name from
(select * from student_table where sex = '女') t1
left join
(select * from student_table where sex = '男') t2
on t1.name = t2.name where t2.name is null
union
select t1.name ,t2.name from
(select * from student_table where sex = '女') t1
right join
(select * from student_table where sex = '男') t2
on t1.name = t2.name where t1.name is null;
执行结果函数是：3行
官方解析：
select t1.name ,t2.name from
(select * from student_table where sex = '女') t1
left join
(select * from student_table where sex = '男') t2
on t1.name = t2.name where t2.name is null
结果是 '李四'；
union
select t1.name ,t2.name from
(select * from student_table where sex = '女') t1
right join
(select * from student_table where sex = '男') t2
on t1.name = t2.name where t1.name is null;
结果是'',NULL,NULL;
union时，NULL记录会减少1行，所以结果是3行。
13. 某学院包含多个专业。每个专业每年都招收一个班级的学生。
在招生过程中就已明确规定，一个学生只能就读于该学院的一个班级，但是一个班级可以招收不超过60个学生。
那么，班级和学生之间是________的关系。
解析：一个学生只能对应一个班级，但一个班级能对应多个学生。所以是多对一
14. Mysql中表student_table(id,name,birth,sex)，插入如下记录：
('1001' , '' , '2000-01-01' , '男');
('1002' , null , '2000-12-21' , '男');
('1003' , NULL , '2000-05-20' , '男');
('1004' , '张三' , '2000-08-06' , '男');
('1005' , '李四' , '2001-12-01' , '女');
查询name除'张三'之外的记录，正确的SQL是（）？
select * from student_table where name <> '张三' or name is null ;
错误：select * from student_table where name <> '张三' ;
错误原因：<>无法对null做筛选；
15. 已知员工表如下图所示，员工编号依次递增，现需改变相邻员工的编号，当员工总人数为奇数是，不需要改变最后一个员工的编号。下列SQL语句不正确的是（C） --
表employee：
+-----+-----------+
| eno | ename |
+-----+-----------+
| 1 | 小李 |
| 2 | 小王 |
| 3 | 小刚 |
| 4 | 小虎 |
+----+------------+
A． SELECT
ROW_NUMBER() OVER(ORDER BY(eno+1-2*POWER(0,eno%2))) AS eno,ename
FROM employee
B．SELECT
ROW_NUMBER() OVER(ORDER BY(eno-1+2*MOD(0,eno%2))) AS eno,ename
FROM employee
C．SELECT
IF(eno%2=0,eno-1,
IF(eno=(SELECT COUNT(DISTINCT eno) FROM employee),eno,eno+1))
AS eno,ename
FROM employee
ORDER BY eno
D．SELECT
IF(eno%2=0,eno+1,
IF(eno=(SELECT COUNT(DISTINCT eno) FROM employee),eno,eno-1))
AS eno,ename
FROM employee
ORDER BY eno
解析：若eno为偶数，应减1而非加1；若eno为奇数，且不为最后一个，应加1而非减1。
16. 已知grade表如下，要求找出分数重复的数据，下列选项中SQL语句正确的是（）
sno mark
1 85
2 95
3 85
4 78
SELECT mark FROM (
SELECT mark,COUNT(mark) AS num
FROM grade
GROUP BY mark
) AS STATISTIC -------注意子查询表要用别名
WHERE num>1
17. 修改表test_tbl字段i的缺省值为1000，可以使用SQL语句（ ）
ALTER TABLE test_tbl ALTER i SET DEFAULT 1000;
错误：
ALTER TABLE test_tbl MODIFY i SET DEFAULT 1000;
解析：
18.批处理是指包含一条或多条T-SQL语句的语句组，下列选项中，关于批处理的规则描述正确的是（）
A定义一个check约束后，可以在同一个批处理中使用
B修改一个表中的字段名后，不可以在同一个批处理中引用这个新字段
C Create default，Create rule等语句同一个批处理中可以同时提交多个
D 把规则和默认值绑定到表字段或自定义字段上之后，可以在同一个批处理中使用
正确答案：B
官方解析：
A选项，不能定义一个check约束后，立即在同一个批处理中使用；
C选项，Create default，Create rule，Create trigger，Create procedure，Create view等语句同一个批处理中只能提交一个；
D选项，不能把规则和默认值绑定到表字段或自定义字段上之后，立即在同一个批处理中使用。
其他资料：
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
