MySQL
- 1.在Mysql中有一张表，其ID是自增主键，有10条记录，ID从1到10，现在删掉最后2条记录，重启MySql服务器，再往表中insert一条记录，这条记录的ID是11还是9？
- 2.CHAR和VARCHAR的区别？
- 3.MySql有哪几种锁？怎么查看InnoDB的锁？
- 4.简述在MySQL数据库中MyISAM和InnoDB的区别?
- 5.数据库三大范式？
- 6.什么是Sql注入？
- 7.Mysql去重的方式有哪些？
- 8.CLOB字符串大对象 和BLOB二进制大对象
- 9. 往mysql执行2次相同的update操作，mysql受影响行数第一次为1，第二次为0 ，但是受影响行数为0 的时候，mybatis依旧返回1，这时候可以使用useAffectedRows=true,再次执行相同更新的时候就返回0了
- X0.Mysql完整查询语法和执行顺序
- X1.MySql基础
- X2.建表语句示例
- X3.mysql5.6 支持两个timestamp列，新建或更新记录时，自动记录时间戳
- X4. case when then else end语法
- X5. date_format函数和str_to_date函数
- X6. sysdate函数
- X7. Distinct 单列去重+列组合去重
- X8. count 函数
- X9. ALL | ANY | SOME | IN
- X10. Union | Union ALL
- X11.实现Oracle的nulls first 和 nulls last功能，即null在前和null在后
- X12. MySql的索引
- X13. exists关键字
- X14.常用函数汇总
- X15. SELECT FOR UPDATE 悲观锁
- X16. Tinyint(1)数据格式和实体类中的Boolean类型自动转换
- X17. SUM()
- X18.GROUP_CONCAT
- L1.SQL ZOO实战
- L2. LeetCode
1.在Mysql中有一张表，其ID是自增主键，有10条记录，ID从1到10，现在删掉最后2条记录，重启MySql服务器，再往表中insert一条记录，这条记录的ID是11还是9？
- ① 如果表的类型是MyISAM的话，结果是11，因为MyISAM表会把自增主键的最大值持久化到文件中，重启依旧不会丢失最大值。
- ② 如果表的类型是InnoDb的话，结果是9。因为InnoDb表只是把自增主键的最大值存到内存中，一旦重启mysql服务器就丢失了最大值。
2.CHAR和VARCHAR的区别？
- CHAR类型的列的值 长度固定为创建表时声明的长度，长度值范围是1到255。
- CHAR值被存储时，不够固定长度的话会被用空格填充，检索CHAR值时需要去尾部空格。
- VARCHAR的长度是可变的（最大值以内），创建表时声明的长度为它的最大值
3.MySql有哪几种锁？怎么查看InnoDB的锁？
-
MyISAM只支持表锁， InnoDB支持表锁和行锁
-
1.表级锁：开销小，加锁快，不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发量最低。
-
2.行级锁：开销大，加锁慢，会出现死锁，锁定粒度小，发生锁冲突的概率最低，并发量也最高。
-
InnoDB行级锁类型
- 共享锁S：又称读锁，简单讲就是多个事务对同一数据进行共享一把锁，都能访问到数据，但是只能读不能修改。
- 排他锁X：又称写锁，排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的排他锁，只有获取排他锁的事务可以对数据进行修改。
-
如何查看 InnoDB 的锁信息？
- 示例
# A事务 不提交
START TRANSACTION;
UPDATE bbc t set t.area = area+1 where t.`name` = 'Albania';
# B事务 不提交
START TRANSACTION;
UPDATE bbc t set t.area = area+1 where t.`name` = 'Albania';
# 查看此时innodb锁状态信息 X：代表排他锁 S：代表共享锁（读锁）
select * from information_schema.innodb_locks;
show engine innodb status; #InnoDB整体状态，其中包括锁的情况
4.简述在MySQL数据库中MyISAM和InnoDB的区别?
- 事务方面： InnoDB支持，MyISAM不支持。这就是Mysql将默认存储引擎从MyISAM改为InnoDB的重要原因之一。
- 外键方面： InnoDB支持外键，MyISAM不支持。对于一个包含外键的InnoDB表转为MyISAM会失败。
- 索引方面： InnoDB是聚集索引，MyISAM是非聚集索引。MyISAM支持全文索引，而InnoDB不支持全文索引。不过InnoDB可以使用sphinx支持全文索引。
- 锁粒度方面： InnoDB最小的锁粒度是行锁，MyISAM的最小锁粒度是表锁。MyISAM一个更新语句都会锁住整张表，导致其它读写操作被阻塞，并发访问度极低。这也是Mysql将默认存储引擎从MyISAM改为InnoDB的重要原因之一。
- 硬盘存储结构方面： MyISAM包含表定义文件，数据文件和索引文件。InnoDB没有专门的数据文件，它的数据和索引都放在一个文件中，数据以主键进行聚集存储，真正的数据保存在索引的叶子节点上。
5.数据库三大范式？
- 所谓范式就是设计规范。
- 第一范式（1NF）：每一列属性都是不可再分的属性值。
如: 电话可以拆分成 家庭电话，公司电话，手机号。就不符合第一范式。
- 第二范式（2NF）：在满足1NF的基础上，要求数据库表中的每一列属性都与主键属性相关。如学生表中有主键学号，那么表中其它字段都可以根据学号去获取。
《学生表》
[学号] [姓名] [年龄] [手机号] [所在院校] [院校信息] [所在宿舍]
- 第三范式（3NF）：在满足2NF的基础上，要求数据不能存在传递关系，即每一列属性都跟主键有直接关系而不是间接关系。
//院校地址和院校电话和学生没有直接关系，不符合第三范式
Student表（学号，姓名，年龄，性别，所在院校，院校地址，院校电话）
//将表分为Student表和school表就达到第三范式的要求
Student表（学号，姓名，年龄，性别，所在院校）
school表（所在院校，院校地址，院校电话）
6.什么是Sql注入？
- 所谓SQL注入就是一种将SQL代码添加到输入参数中，传递到SQL服务器并执行的一种攻击手法.。
String sql =
"select * from user where username='"+username+"'and password='"+password+"'";
SQL--> select * from user where username='username' and password='password';
-----注入的两种方式-----
1.在用户名后面拼接一个永等式，输入zs' or '1' = '1
SQL-->select * from user where username='zs' or '1' = '1' and password='password';
2.使用'#注释后面的SQL语句,在用户名后面拼接一个'#
SQL-->select * from user where username='zs'#'and password='password';
7.Mysql去重的方式有哪些？
- 使用mysql自带的 Distinct 关键字进行去重
- 使用 group by 字段名 来实现去重
8.CLOB字符串大对象 和BLOB二进制大对象
- BLOB和CLOB都是大字段类型，BLOB是按二进制来存储的，而CLOB是可以直接存储文字的。其实两个是可以互换的的，或者可以直接用LOB字段代替这两个。但是为了更好的管理
ORACLE数据库：
通常像图片、文件、音乐等信息就用BLOB字段来存储，先将文件转为二进制再存储进去。
文章或者是较长的文字，就用CLOB存储，这样对以后的查询更新存储等操作都提供很大的方便。
9. 往mysql执行2次相同的update操作，mysql受影响行数第一次为1，第二次为0 ，但是受影响行数为0 的时候，mybatis依旧返回1，这时候可以使用useAffectedRows=true,再次执行相同更新的时候就返回0了
jdbc:mysql://localhost:3306/nz1906?characterEncoding=utf-8&useAffectedRows=true
X0.Mysql完整查询语法和执行顺序
-- 查询完整语法--
SELECT DISTINCT [column_list]
FROM [table]
JOIN [table] ON [condition]
WHERE [condition]
GROUP BY [column_name]
HAVING [condition]
ORDER BY [condition][ASC/DESC]
LIMIT [num1,num2]
-- SQL执行顺序--
1.FROM 表
2.JOIN 表
3.ON 条件
4.WHERE 分组前筛选
5.GROUP BY 分组
6.聚合函数 AVG() MAX() MIN()
7.HAVING 分组后筛选
8.SELECT 查询
9.DISTINCT 去重
10.ORDER BY 排序
11.LIMIT 分页
X1.MySql基础
| 命令 | 说明 |
|---|---|
| SELECT VERSION() | 返回MYSQL数据库的版本号 |
| SELECT CURRENT_USER() | 返回当前数据库用户 |
| SELECT DATABASE() | 返回当前使用的数据库名 |
| SHOW INDEX FROM [表名] | 查看表中所有索引 |
| CREATE INDEX [索引名] ON [表名(列名)] | 创建索引 |
| DROP INDEX [索引名] ON [表名] | 删除表中某个索引 |
1.mysql -u [用户名] -p [密码] ：登录mysql
2.quit :退出数据库
3.show tables : 展示当前数据库的所有表
4.create database [库名]: 创建一个数据库
5.drop database [库名]: 删除一个数据库
6.alter database [库名] character [编码格式]: 修改数据库的编码格式
7.use [库名] : 使用某个库
8.desc [表名] : 查看表结构
9.CREATE TABLE [表名] ([字段名] [值类型] [约束], [字段名] [值类型] [约束]...)
10.DROP TABLE [表名] : 删除表
11.DELETE FROM [表名] : 逐条删除所有记录
12.TRUNCATE FROM [表名] : 删除整张表，然后再构件一个与原来结构相同的表
13.INSERT INTO [表名] ([字段1名]，[字段2名].. ) VALUES ([字段1的值],[字段2的值])
14.UPDATE [表名] SET [字段A]=[对应值] ,[字段B]=[对应值],[字段C]=[对应值] WHERE [条件]
15.DELETE FROM [表名] WHERE [条件]
16.SELECT * from tb_emp WHERE ISNULL(`name`):查找name值等于null的记录,不能使用name=null.
17.COUNT(*): 在统计结果数量的时候,不会忽略null值的记录,COUNT(列名)会忽略为null的记录
18.ALTER TABLE [表名] ADD [新列名] [类型] [约束] : 往表中添加新的字段
19.ALTER TABLE [表名] DROP [列名] : 删除表中的字段
20.ALTER TABLE [表名] CHANGE [旧列名] [新列名] [旧列名类型] : 修改字段的名称
21.WHERE 是分组前的条件筛选 GROUP BY之前
22.HAVING 是分组后的条件筛选 GROUP BY之后
23.DISTINCT 关键字: 实现去重。
如: SELECT DISTINCT ID,NAME FROM user 表示ID拼接NAME之后的结果出现重复就会被过滤掉。
24.PRIMARY KEY(user_id,class_id) ：联合主键，userId拼接classId之后的结果出现重复就会被过滤掉。
X2.建表语句示例
create table tb_spu
(
id bigint auto_increment comment '商品ID',
title varchar(256) not null default '' comment '标题',
sub_tile varchar(256) not null default '' comment '子标题',
brand_id bigint not null comment '品牌ID',
create_time datetime null comment '创建时间',
update_time datetime null comment '更新时间',
is_saled tinyint(1) not null default 1 comment '是否上架，1上架，0下架',
is_valid tinyint(1) not null default 1 comment '是否有效，1有效，0已删除',
primary key(id)
)comment 'spu表，该表描述的是一个抽象性的商品，比如 iphone8';
-----------------------------------------------------------------------------------------
CREATE TABLE `t_product` (
`pid` int(6) NOT NULL AUTO_INCREMENT,
`pname` varchar(20) DEFAULT NULL,
`p_tid` int(4) DEFAULT NULL,
`punit` varchar(8) DEFAULT NULL,
`pnumber` int(6) DEFAULT NULL,
`premark` varchar(500) DEFAULT NULL,
`pdel` int(6) DEFAULT NULL,
PRIMARY KEY (`pid`),
KEY `FK_TID` (`p_tid`) USING BTREE, --为列p_tid建立索引，使用BTREE数据结构
CONSTRAINT `FK_TID` FOREIGN KEY (`p_tid`) REFERENCES `t_type` (`tid`) --设置外键
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
--AUTO_INCREMENT=n命令来重设自增的起始值。
-----------------------------------------------------------------------------------------
CREATE TABLE `hc_project_vote` (
`rcd_id` INT (11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`create_time` datetime COMMENT '创建时间',
`update_time` datetime COMMENT '更新时间',
`sn` VARCHAR (30) DEFAULT '' COMMENT 'sn',
`project_sn` VARCHAR (30) DEFAULT '' COMMENT '项目sn',
`user_sn` VARCHAR (30) DEFAULT '' COMMENT '投票者sn',
`vote_time` datetime COMMENT '投票时间',
`visit_ip` VARCHAR (15) DEFAULT '' COMMENT '投票者ip',
PRIMARY KEY (`rcd_id`),
KEY `idx_hc_vote_project_sn` (`project_sn`) USING BTREE,
KEY `idx_hc_vote_user_sn` (`user_sn`) USING BTREE
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '项目投票记录表';
X3.mysql5.6 支持两个timestamp列，新建或更新记录时，自动记录时间戳
CREATE TABLE temp
(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10),
created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
X4. case when then else end语法
UPDATE tb_user
SET name=
CASE id
WHEN 1 THEN '小1'
WHEN 2 THEN '小2'
WHEN 3 THEN '小3'
WHEN 4 THEN '小4'
WHEN 5 THEN '小5'
ELSE 'xx'
END
WHERE id in (1,2,3,4,5,6);
--------------------
<update id="updateDeptChildren" parameterType="java.util.List">
update sys_dept set ancestors =
<foreach collection="depts" item="item" index="index"
separator=" " open="case dept_id" close="end">
when #{item.deptId} then #{item.ancestors}
</foreach>
where dept_id in
<foreach collection="depts" item="item" index="index"
separator="," open="(" close=")">
#{item.deptId}
</foreach>
</update>
X5. date_format函数和str_to_date函数
- mysql日期和字符相互转换
date_format(date,'%Y-%m-%d') ::oracle中的to_char()函数
str_to_date(date,'%Y-%m-%d') ::oracle中的to_date()函数
%Y：代表4位的年份
%y：代表2为的年份
%m：代表月, 格式为(01……12)
%c：代表月, 格式为(1……12)
%d：代表月份中的天数,格式为(00……31)
%e：代表月份中的天数, 格式为(0……31)
%H：代表小时,格式为(00……23)
%k：代表 小时,格式为(0……23)
%h： 代表小时,格式为(01……12)
%I： 代表小时,格式为(01……12)
%l ：代表小时,格式为(1……12)
%i： 代表分钟, 格式为(00……59)
%r：代表 时间,格式为12 小时(hh:mm:ss [AP]M)
%T：代表 时间,格式为24 小时(hh:mm:ss)
SELECT *
FROM t_02
WHERE DATE_FORMAT(create_time,'%y%m%d%H')
BETWEEN DATE_FORMAT('2020-06-02 00:00:00','%y%m%d%H')
AND DATE_FORMAT('2020-06-04 23:30:30','%y%m%d%H')
---------------------------------------------------------------------------------------
<select id="selectConfigList" parameterType="SysConfig" resultMap="SysConfigResult">
<include refid="selectConfigVo"/>
<where>
<if test="configName != null and configName != ''">
AND config_name like concat('%', #{configName}, '%')
</if>
<if test="configType != null and configType != ''">
AND config_type = #{configType}
</if>
<if test="configKey != null and configKey != ''">
AND config_key like concat('%', #{configKey}, '%')
</if>
<if test="beginTime != null and beginTime != ''"><!-- 开始时间检索 -->
and date_format(create_time,'%y%m%d') >=
date_format(#{beginTime},'%y%m%d')
</if>
<if test="endTime != null and endTime != ''"><!-- 结束时间检索 -->
and date_format(create_time,'%y%m%d') <=
date_format(#{endTime},'%y%m%d')
</if>
</where>
</select>
X6. sysdate函数
<update id="updateConfig" parameterType="SysConfig">
update sys_config
<set>
<if test="configName != null and configName != ''">
config_name = #{configName},
</if>
<if test="configKey != null and configKey != ''">
config_key = #{configKey},
</if>
<if test="configValue != null and configValue != ''">
config_value = #{configValue},
</if>
<if test="configType != null and configType != ''">
config_type = #{configType},
</if>
<if test="updateBy != null and updateBy != ''">
update_by = #{updateBy},
</if>
<if test="remark != null">
remark = #{remark},
</if>
update_time = sysdate()
</set>
where config_id = #{configId}
</update>
<insert id="insertDept" parameterType="SysDept">
insert into sys_dept(
<if test="deptId != null and deptId != 0">dept_id,</if>
<if test="parentId != null and parentId != 0">parent_id,</if>
<if test="deptName != null and deptName != ''">dept_name,</if>
<if test="ancestors != null and ancestors != ''">ancestors,</if>
<if test="orderNum != null and orderNum != ''">order_num,</if>
<if test="leader != null and leader != ''">leader,</if>
<if test="phone != null and phone != ''">phone,</if>
<if test="email != null and email != ''">email,</if>
<if test="status != null">status,</if>
<if test="createBy != null and createBy != ''">create_by,</if>
create_time
)values(
<if test="deptId != null and deptId != 0">#{deptId},</if>
<if test="parentId != null and parentId != 0">#{parentId},</if>
<if test="deptName != null and deptName != ''">#{deptName},</if>
<if test="ancestors != null and ancestors != ''">#{ancestors},</if>
<if test="orderNum != null and orderNum != ''">#{orderNum},</if>
<if test="leader != null and leader != ''">#{leader},</if>
<if test="phone != null and phone != ''">#{phone},</if>
<if test="email != null and email != ''">#{email},</if>
<if test="status != null">#{status},</if>
<if test="createBy != null and createBy != ''">#{createBy},</if>
sysdate()
)
</insert>
X7. Distinct 单列去重+列组合去重
单列去重：name重复就会呗过滤
select distinct name
from t_test
列组合去重:name和age组合起来重复的话，就会呗过滤掉
select distinct name,age
from t_test
X8. count 函数
==============COUNT(字段名)================
COUNT(column_name):返回指定列的元素个数，NULL不计入结果
select count(column_name) from table_name
==============COUNT(*)或者count(数字):================
COUNT(*)或者count(数字):返回符合条件的所有记录数量
select count(*)
from user
where name='zs'
============COUNT(DISTINCT 字段名)==========
COUNT(DISTINCT 字段名)：返回该字段不重复的记录数
SELECT COUNT(DISTINCT num1)
FROM t_test01
X9. ALL | ANY | SOME | IN
select s1 from t1 where s1 > ANY(select s1 from t2)
select s1 from t1 where s1 > ALL(select s1 from t2)
只要 表t1中的s1列元素 与t2中s1列中有一个元素不相同就被帅选出来
select s1 from t1 where s1 <> any (select s1 from t2);
select s1 from t1 where s1 <> some (select s1 from t2);语句some是any的别名，用法相同
IN 相当于: =any 等于其中的任何一个
NOT IN 相当于: <>all 不等于其中全部的值
X10. Union | Union ALL
MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
语法
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
ALL: 可选，返回所有结果集，包含重复数据。
|--------|
Score Id
3.5 1
3.65 2
4 3
3.85 4
4 5
3.65 6
|--------|
select * from scores WHERE Id =3 OR Score = 3.65
上面语句可替换成
SELECT * from scores WHERE Id =3
UNION
SELECT * from scores WHERE Score = 3.65
UNION 用于将不同表中相同列中查询的数据展示出来；（去重）
UNION ALL 用于将不同表中相同列中查询的数据展示出来；（没有去重）
X11.实现Oracle的nulls first 和 nulls last功能，即null在前和null在后
实现null在前功能，使用isnull函数默认不排序，排序需要追加
select num1,num2
from t_test01
order by not isnull(num1),num1 desc
实现null在后功能，使用isnull函数默认不排序，排序需要追加
select num1,num2
from t_test01
order by isnull(num1),num1 desc
X12. MySql的索引
- 索引：是帮助MySql高效获取数据的数据结构，可以加快数据库查询速度，通俗的来说索引就好比书本的目录。
- MySql的默认存储引擎是innodb，索引的结构是B+Tree
SHOW INDEX FROM [TABLE_NAME] : 查看表中所有索引
CREATE INDEX [INDEX_NAME] ON [TABLE_NAME(COLUMN_NAME)] :创建索引
DROP INDEX [INDEX_NAME] ON [TABLE_NAME] : 删除表中某个索引
- 联合索引又叫复合索引，即一个覆盖表中两列或者以上的索引.
create table test(
a int,
b int,
c int,
KEY index_demo(a,b,c));
1.多列索引在and查询中应用
select * from test where a=? and b=? and c=?；查询效率最高，索引全覆盖。
select * from test where a=? and b=?；索引覆盖a和b。
select * from test where b=? and a=?；经过mysql的查询分析器的优化，索引覆盖a和b。
select * from test where a=?；索引覆盖a。
select * from test where b=? and c=?；没有a列，不走索引，索引失效。
select * from test where c=?；没有a列，不走索引，索引失效。
2.多列索引在范围查询中应用
select * from test where a=? and b between ? and ? and c=?；索引覆盖a和b，因b列是范围查询，因此c列不能走索引。
select * from test where a between ? and ? and b=?；a列走索引，因a列是范围查询，因此b列是无法使用索引。
select * from test where a between ? and ? and b between ? and ? and c=?；a列走索引，因a列是范围查询，b列是范围查询也不能使用索引。
3.多列索引在排序中应用
select * from test where a=? and b=? order by c；a、b、c三列全覆盖索引，查询效率最高。
select * from test where a=? and b between ? and ? order by c；a、b列使用索引查找，因b列是范围查询，因此c列不能使用索引，会出现file sort。
4.总结
联合索引的使用在写where条件的顺序无关，mysql查询分析会进行优化而使用索引。但是减轻查询分析器的压力，最好和索引的从左到右的顺序一致。使用等值查询，多列同时查询，索引会一直传递并生效。因此等值查询效率最好。索引查找遵循最左侧原则。但是遇到范围查询列之后的列索引失效。排序也能使用索引，合理使用索引排序，避免出现file sort。
========================================================================================
在使用联合索引的时候，我们遵守一个最左原则
即INDEX LianHeIndex (name,age)支持 name | name age组合查询,而不支持age查询
换句话说，在执行
select * from stu where name=? 索引有效
select * from stu where name=? and age=? 索引有效
select * from stu where age=? 索引无效
如果我们是在name和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在name、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(name, age)的复合索引，那么其实相当于创建了(name)、(name,age)两个索引，这被称为最佳左前缀特性。
因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。
【注意事项】
只要列中包含有NULL值都将不会被包含在索引中
复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的，所以我们在数据库设计时尽可能不要让字段的默认值为NULL。
X13. exists关键字
EXISTS(包括 NOT EXISTS )子句的返回值是一个布尔值。
EXISTS内部有一个子查询语句(SELECT ... FROM...)， 我将其称为EXIST的内查询语句。
其内查询语句返回一个结果集。 EXISTS子句根据其内查询语句的结果集空或者非空，返回一个布尔值。
通俗的理解：将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，
这一行行可作为外查询的结果行，否则不能作为结果.
【将customers查出的每一行带入内查询作为检验，返回true就显示】
SELECT *
FROM day1106.customers a
WHERE EXISTS(SELECT *
FROM day1106.forcustomers b
WHERE b.name = a.name )
X14.常用函数汇总
============================数字函数===========================
ABS(-1):求-1的绝对值，返回1 SELECT ABS(-1);
AVG(column):求平均值
CEIL(1.5):返回大于或等于1.5的最小整数--> 2
FLOOR(1.5):返回小于或等于1.5的最大整数-->1
EXP(3):返回e的三次方 e在数学中代表自然对数的底数 e=2.71828..
GREATEST(ex1,ex2,ex3):返回列表中的最大值
LEAST(ex1,ex2,ex3):返回列表中的最小值
LN(2)：返回2的自然对数
LOG(x):返回自然对数(以 e 为底的对数)
MAX(expression):返回字段中的最大值
MIN(expression)返回字段 expression 中的最大值
POW(x,y)：返回x的y次方
RAND():返回0到1的随机数
ROUND(x)：返回离 x 最近的整数 SELECT ROUND(1.23456);返回1
ROUND(x,y):指定x保留几位小数 SELECT ROUND(1.23456，2)返回1.23
SIGN(x):返回数字x的符号 用-1代表负数，0代表0，1代表正数
SQRT(x)返回x的平方根 SELECT SQRT(25)：返回5
SUM(expression)返回指定字段的总和 SELECT SUM(role_id) from sys_role
TRUNCATE(x,y)返回数值 x 保留到小数点后 y 位的值
SELECT TRUNCATE(1.23456,3);返回1.234
============================字符串函数===========================
SELECT ASCII('AB'); 返回第一个字符的ASCII码值：65
SELECT LENGTH('abcd'):返回字符串的长度
CONCAT(s1,s2…sn) ：字符串拼接
FIND_IN_SET(s1,s2) 返回在字符串s2中与s1匹配的字符串的位置：
--SELECT FIND_IN_SET("c", "a,b,c,d,e");返回3
FORMAT(x,n)函数可以将数字 x 进行格式化 “#,###.##”,
将 x 保留到小数点后 n 位，最后一位四舍五入
--SELECT FORMAT(250500.5634, 2):返回250,500.56
INSERT(S1,X,LEN,S2)：字符串s2替换s1中从x位置开始LEN个字符
--SELECT INSERT("google.com", 1, 6, "runnob");返回 runnob.com
LOCATE(s1,s)从字符串 s 中获取 s1 的开始位置
--SELECT LOCATE('st','myteststring');返回5
LCASE(s)/LOWER(s)将字符串 s 的所有字母变成小写字母
UCASE(S)/UPPER(S)将字符串 s 的所有字母变成大写字母
TRIM(s)去掉字符串 s 开始和结尾处的空格
LTRIM(s)去掉字符串 s 开始处的空格
RTRIM(s)去掉字符串 s 结尾处的空格
SUBSTR/SUBSTRING(s, start, length)从字符串 s 的 start 位置截取长度为 length 的 子字符串
POSITION(s1 IN s)从字符串 s 中获取 s1 的开始位置
REPEAT(s,n)将字符串 s 重复 n 次
REVERSE(s)将字符串s的顺序反过来
STRCMP(s1,s2)比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，
如果 s1>s2 返回 1，如果 s1<s2 返回 -1
--SELECT STRCMP('SSS','SSZ');返回-1
============================日期函数===========================
CURDATE()/CURRENT_DATE()返回当前日期 返回2019-02-19
CURRENT_TIME()/CURTIME()返回当前时间 21:15:12
ADDDATE(d,n)计算起始日期 d 加上 n 天的日期
SELECT ADDDATE('2018-01-11',INTERVAL 10 DAY) 返回2018-01-21
ADDTIME(t,n)时间 t 加上 n 秒的时间
--SELECT ADDTIME('2011-11-11 11:11:11', 5); 返回2011-11-11 11:11:16
CURRENT_TIMESTAMP 返回当前日期和时间 2020-01-12 21:19:07
DATE()从日期或日期时间表达式中提取日期值
SELECT DATE('2011-11-11 11:11:11'); 返回2011-11-11
DAY(d)返回日期值 d 的日期部分
--SELECT DAY('2011-11-13 11:11:11');返回13
DATEDIFF(d1,d2)计算日期 d1->d2 之间相隔的天数
--SELECT DATEDIFF('2001-01-01','2001-02-01');返回-31
DATE_FORMAT按表达式 f的要求显示日期 d
--SELECT DATE_FORMAT('2011.11.11 11:11:11','%Y-%m-%d %r');
返回2011-11-11 11:11:11 AM
DAYNAME(d)返回日期 d 是星期几，如 Monday,Tuesday
--SELECT DAYNAME('2011-11-11 11:11:11');返回Friday
DAYOFMONTH(d)计算日期 d 是本月的第几天
--SELECT DAYOFMONTH('2011-11-11 11:11:11');返回11
DAYOFWEEK(d)日期 d 今天是星期几，1 星期日，2 星期一，以此类推
--SELECT DAYOFWEEK('2020-1-12 11:11:11');返回1
DAYOFYEAR(d)计算日期 d 是本年的第几天
--SELECT DAYOFYEAR('2020-1-12 11:11:11');返回12
EXTRACT(type FROM d)从日期 d 中获取指定的值，type 指定返回的值
--SELECT EXTRACT(MINUTE FROM '2011-12-13 14:15:16'); 返回15
UNIX_TIMESTAMP()得到时间戳
--SELECT UNIX_TIMESTAMP('2019-2-19'); 返回1550505600
FROM_UNIXTIME()时间戳转日期
--SELECT FROM_UNIXTIME(1550505600);返回2019-02-19 00:00:00
--SELECT FROM_UNIXTIME(1550505600, '%Y-%m-%d');返回2019-02-19
============================高级函数===========================
IF(expr,v1,v2)如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2
--SELECT IF(1>0,'yes','no');返回yes
CONV(x,f1,f2)返回f1进制的x变成 f2 进制后的数
--SELECT CONV(88,10,2):返回1011000
SELECT VERSION() 返回MYSQL数据库的版本号
SELECT DATABASE()返回当前使用的数据库名
SELECT CURRENT_USER()返回当前数据库用户 ：root@localhost
X15. SELECT FOR UPDATE 悲观锁
- 悲观锁是对数据被修改持悲观态度（认为数据在被修改的时候一定会存在并发问题），因此在整个数据处理过程中将数据锁定。悲观锁，每次去操作数据的时候，都认为别的事务会修改数据，所以在操作之前会上锁,主键一定是唯一性索引，唯一性索引并不一定就是主键。
- 使用多窗口模拟数据库并发问题。
1.for update是在数据库中上锁用的，可以为数据库中的行上一个排它锁。当一个事务的操作未完成时候，其他事务可以读取但是不能写入或更新。
2.for update的意义:透过的事务机制来确保读取及提交的数据都是正确的。
====================================================================
注1: FOR UPDATE 仅适用于InnoDB，且必须在事务区块(BEGIN/COMMIT)中才能生效。
注2: 要测试锁定的状况，可以利用MySQL 的Command Mode ，开二个视窗来做测试。
注3: InnoDB默认是行级别的锁，当有明确指定的主键时候，是行级锁。否则是表级别。
注4: 如果for update没有命中索引会锁表
#1.明确指定主键，并且查出有数据，使用行锁，别的事务无法修改数据，只能读数据
SET autocommit=0;
BEGIN;
SELECT * FROM document WHERE obj_id =1 FOR UPDATE;
COMMIT;
#2.明确指定主键，但查无数据，不使用锁
SET autocommit=0;
BEGIN;
SELECT * FROM document WHERE obj_id =156555654 FOR UPDATE;
COMMIT;
#3.主键不明确，满足条件的记录都被行锁锁主，不是使用表锁
SET autocommit=0;
BEGIN;
SELECT * FROM document WHERE obj_id>10 FOR UPDATE;
COMMIT;
#4.没有指定主键，有无数据，都使用表锁
SET autocommit=0;
BEGIN;
SELECT * FROM document WHERE obj_name ='list------test' FOR UPDATE;
COMMIT;
#5.明确指定索引，并且有数据，使用行锁，无数据就无锁
SET autocommit=0;
BEGIN;
SELECT * FROM document WHERE objdef_id = 3 FOR UPDATE;
COMMIT;
#6.指定的索引是一个范围，使用多个行锁，锁定满足条件的记录
SET autocommit=0;
BEGIN;
SELECT * FROM document WHERE objdef_id >2 FOR UPDATE;
COMMIT;
===========================================================================
1.当开启一个事务进行for update的时候，另一个事务也有for update的时候会一直等着，直到第一个事务结束吗？
答：会的。除非第一个事务commit或者rollback或者断开连接，第二个事务会立马拿到锁进行后面操作。
2.如果没查到记录会锁表吗？
答：数据不存在，是不会锁表的
X16. Tinyint(1)数据格式和实体类中的Boolean类型自动转换
-
mysql中没有布尔型 boolean=tinyint(1)
-
mysql 中 tinyint(1) ===> 0代表false，1代表true
=================前提====================
查询或插入的这个数据库字段的数据类型为Tinyint(1)
=================新增记录=================
往数据库表新增记录时，使用true或false，数据库会自动把true转换为1，false转换为0
=================查询记录=================
查询数数据库时，数据库会把0自动转化为false，1自动转换为true存储到实体类中的Boolean类型属性当中
X17. SUM()
- SUM(表达式)： 满足表达式就 + 1。
- SUM(列名) :对该列所有值进行相加，NULL会被忽略。
- SUM( DISTINCT 列名)：对该列的所有不重复值进行相加，NULL会被忽略。
- SUM(表达式)的应用示例
name sub score
-----------------
张三 数学 90
张三 语文 50
张三 地理 40
李四 语文 55
李四 政治 60
王五 语文 55
-----------------
1.查询出2门及2门以上不及格者的平均成绩
SELECT
s.`name`,
AVG(s.score)
FROM
students s
GROUP BY
s.`name`
HAVING
SUM(s.score < 60) >= 2 ; #SUM(表达式) 满足表达式就 + 1
X18.GROUP_CONCAT
- GROUP_CONCAT(列名)：分组之后，对组内所有该字段值进行拼接。
- GROUP_CONCAT(表达式)：分组之后，把组内的该字段的每个值都代入表达式得到的结果再拼接起来。
id date rst
-------------------
1 2011-1-1 胜
2 2011-1-1 胜
3 2011-1-1 负
4 2011-1-1 负
5 2011-1-2 胜
6 2011-1-2 负
7 2011-1-2 负
8 2011-1-3 胜
9 2011-1-3 胜
10 2011-1-3 胜
11 2011-1-3 胜
12 2011-1-4 负
13 2011-1-4 负
14 2011-1-4 负
15 2011-1-4 负
SELECT
GROUP_CONCAT(c.id)
FROM
match_rec c
GROUP BY
c.date;
1,2,3,4
5,6,7
8,9,10,11
12,13,14,15
SELECT
GROUP_CONCAT(c.id >10)
FROM
match_rec c
GROUP BY
c.date;
0,0,0,0
0,0,0
0,0,0,1
1,1,1,1
L1.SQL ZOO实战
name region area population gdp
--------------------------------------------------------------
Afghanistan South Asia 652225 26000000 67144000000
Albania Europe 28728 3200000 6656000000
Algeria Middle East 2400000 32900000 75012000000
Andorra Europe 468 49000 6656000050
Bangladesh South Asia 143998 152600000 67144000000
UnitedKingdom Europe 242514 59600000 2022824000000
Turkey Middle East 168843 12900000 1234567890
Oman Middle East 1800000 10200000 1345000
Indonesia South Asia 2200000 269536482 13345678
xxx Europe 59680000 1024535
af1 Africa 48000 202282800000
af2 Africa 48800 202289400000
af3 Africa 49000 232282400000
--------------------------------------------------------------
1.【選擇代碼以顯示在每個區域人口最小的國家的國家名稱，區域和人口】
select region,name,population
from bbc x
where x.population<=
All(select y.population
from bbc y
where x.region = y.region)
------------------------------------------------
SELECT * FROM bbc x
where x.population =
(SELECT MIN(y.population) FROM bbc y WHERE x.region = y.region);
2.【選擇代碼以顯示國家名稱，該國所在的地區每國人口都超過50000】
select name,region,population
from bbc x
where 50000 <
All(select population
from bbc y
where x.region = y.region)
-----------------------------------------------
SELECT * FROM bbc x WHERE NOT EXISTS(
SELECT 1 FROM bbc y WHERE x.region = y.region AND y.population < 50000
)
ORDER BY x.region DESC,x.population DESC
3.【選擇代碼以顯示國家名稱，該國家人口少於它周圍的任意國家（同洲）的人口三分之一】
SELECT * FROM bbc x
where x.population < ALL(SELECT y.population/3 FROM bbc y
WHERE y.region = x.region
AND x.name <> y.name);
-------------------------------------------------
SELECT * FROM bbc x
where not EXISTS(SELECT 1 FROM bbc y
where x.region = y.region
and x.name <> y.name
and y.population/3 <= x.population )
4.【查询和英国一个洲，且人口数大于英国的国家名称】
SELECT * FROM bbc
WHERE population >
(SELECT population
FROM bbc
WHERE name='United Kingdom')
AND region =
(SELECT region
FROM bbc
WHERE name = 'United Kingdom')
5.【顯示國家名稱，該國有比非洲任何國家有更大的國內生產總值GDP】
SELECT * FROM bbc x
WHERE x.gdp > (SELECT MAX(gdp) FROM bbc x where x.region = 'Africa')
AND x.region <> 'Africa'
----------------------------------------------
yr subject winner
----------------------------------------------
1960 Chemistry Willard F. Libby
1960 Literature Saint-John Perse
1960 Medicine Sir Frank Macfarlane Burnet
1960 Medicine Peter Madawar
-----------------------------------------------
6.【哪幾年頒發了物理獎，但沒有頒發化學獎?】
select DISTINCT yr from nobel
where subject ='physics'
and yr not in (
select yr from nobel where subject='Chemistry'
)
-------------------------------------------------
select DISTINCT a.yr from nobel a
where not EXISTS (SELECT 1 FROM nobel b WHERE b.yr = a.yr and b.subject='Chemistry' )
7.【首次頒發的經濟獎 (Economics)的得獎者是誰?】
select winner from nobel where subject='Economics'
and yr =
(select yr from nobel
where subject = 'Economics'
order by yr asc
limit 0,1)
8.【日本物理學家益川敏英 (Toshihide Maskawa) 曾獲得物理獎。同年還有兩位日本人一同獲得物理獎。試列出這2位日本人的名稱】
select winner from nobel
where
yr =(select yr
from nobel
where winner = 'Toshihide Maskawa'
and subject = 'physics')
and winner <> 'Toshihide Maskawa'
and subject = 'physics'
9.【紅十字國際委員會 (International Committee of the Red Cross) 曾多次獲得和平獎。試找出與紅十字國際委員會同年得獎的文學獎(Literature)得獎者和年份】
select winner,yr from nobel
where subject='Literature' and yr in
(SELECT yr FROM nobel
where winner = 'International Committee of the Red Cross')
==========================================================================
[部门表]
dep_id dep_name
------------------
1 JAVA
2 C
3 PHP
4 H5
5 GO
------------------
[员工表]
emp_id name age salary dep_id
-----------------------------
1 zs 20 4000 1
2 ls 31 5000 2
3 ww 32 6000 3
4 zl 27 7000 3
5 xq 40 10000 2
6 wb 38 3500 4
7 xj 50 6000 5
8 ss 15 2500 5
9 s1 33 8000 1
10 s2 55 12000 4
11 s3 66 5000 1
12 s4 77 5500 2
13 dsb 256 6600 10
-- 1.查询平均工资大于6000的部门
SELECT d.dep_name,AVG(salary)
FROM tb_emp e
INNER JOIN tb_dep d ON d.dep_id=e.dep_id
GROUP BY d.dep_name
HAVING AVG(salary)>2000;
-- 2.查询部门平均工资低于6500的部门都有几个人
SELECT d.dep_name,avg(salary), COUNT(*) FROM tb_emp e
INNER JOIN tb_dep d ON d.dep_id=e.dep_id
GROUP BY d.dep_name
HAVING avg(salary)<=6500;
-- 3.查询Java部门中年龄超过20的人
SELECT name, age,dep_name
FROM tb_emp e
INNER JOIN tb_dep d ON d.dep_id=e.dep_id
WHERE d.dep_name ='JAVA' AND age>20
-- 4.查询java部门的平均工资
SELECT dep_name, avg(salary)
FROM tb_emp e
INNER JOIN tb_dep d ON d.dep_id=e.dep_id
GROUP BY dep_name
HAVING dep_name='JAVA';
-- 5.查询java部门中年龄最小的人
SELECT dep_name,name,age
FROM tb_emp e
INNER JOIN tb_dep d ON d.dep_id=e.dep_id
WHERE dep_name='JAVA'
ORDER BY age ASC
LIMIT 0,1;
-- 6.查询java部门都有哪些人
SELECT dep_name,NAME
FROM tb_emp e
INNER JOIN tb_dep d ON d.dep_id=e.dep_id
WHERE dep_name='JAVA';
==========================================================================
id name salary dep_id
---------------------------
1 Joe 85000 1
2 Henry 80000 2
3 Sam 60000 2
4 Max 90000 1
5 Janet 69000 1
6 Randy 85000 1
7 Will 7000 1
1.查询第二高的薪水,查不到返回null
select ifnull(
(select distinct Salary
from Employee
order by Salary desc
limit 1,1),null
) as SecondHighestSalary
[爱好表，中间表，用户表]
-- 5.查询爱好是足球的有哪些人
SELECT u.name ,l.love_type
FROM tb_user u
INNER JOIN tb_love_user lu ON lu.us_id=u.id
INNER JOIN tb_love l ON l.id=lu.lo_id
WHERE love_type='足球'
-- 6.查询爱好是足球和篮球的有哪些人
SELECT u.name ,l.love_type,COUNT(*)
FROM tb_user u
INNER JOIN tb_love_user lu ON lu.us_id=u.id
INNER JOIN tb_love l ON l.id=lu.lo_id
WHERE love_type='足球' OR love_type='篮球'
GROUP BY `name`
HAVING COUNT(*)=2
-- 7.查询哪种球类爱好喜欢的人最多
SELECT COUNT(*),love_type
FROM tb_user u
INNER JOIN tb_love_user lu ON lu.us_id=u.id
INNER JOIN tb_love l ON l.id=lu.lo_id
GROUP BY love_type
ORDER BY COUNT(*) DESC
LIMIT 0,1
//=====ORACLE=====
SELECT
m.dated AS 日期,
sum(case m.rst when '胜' THEN 1 ELSE 0 END) AS 胜 ,
sum(case m.rst when '负' THEN 1 ELSE 0 END) AS 负
FROM
match_rec m
GROUP BY
m.dated;
=========或者=========
SELECT DISTINCT
m.dated AS 日期,
sum(case m.rst when '胜' THEN 1 ELSE 0 END)over(partition by m.dated) AS 胜 ,
sum(case m.rst when '负' THEN 1 ELSE 0 END)over(partition by m.dated) AS 负
FROM
match_rec m;
=========或者=========
SELECT DISTINCT
m.dated AS 日期,
sum(case when m.rst like '胜' THEN 1 ELSE 0 END)over(partition by m.dated) AS 胜 ,
sum(case when m.rst like '负' THEN 1 ELSE 0 END)over(partition by m.dated) AS 负
FROM
match_rec m;
//=====MYSQL======
SELECT
m.date AS '日期',
SUM(m.rst = '胜') AS '胜',
SUM(m.rst = '负') AS '负'
FROM
match_rec m
GROUP BY
m.date
L2. LeetCode
- 262题：行程和用户
Trips 表中存所有出租车的行程信息。
每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。
Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。
+----+-----------+-----------+---------+--------------------+----------+
| Id | Client_Id | Driver_Id | City_Id | Status |Request_at|
+----+-----------+-----------+---------+--------------------+----------+
| 1 | 1 | 10 | 1 | completed |2013-10-01|
| 2 | 2 | 11 | 1 | cancelled_by_driver|2013-10-01|
| 3 | 3 | 12 | 6 | completed |2013-10-01|
| 4 | 4 | 13 | 6 | cancelled_by_client|2013-10-01|
| 5 | 1 | 10 | 1 | completed |2013-10-02|
| 6 | 2 | 11 | 6 | completed |2013-10-02|
| 7 | 3 | 12 | 6 | completed |2013-10-02|
| 8 | 2 | 12 | 12 | completed |2013-10-03|
| 9 | 3 | 10 | 12 | completed |2013-10-03|
| 10 | 4 | 13 | 12 | cancelled_by_driver|2013-10-03|
+----+-----------+-----------+---------+--------------------+----------+
Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止，
Role 则是一个表示（‘client’, ‘driver’, ‘partner’）的枚举类型。
+----------+--------+--------+
| Users_Id | Banned | Role |
+----------+--------+--------+
| 1 | No | client |
| 2 | Yes | client |
| 3 | No | client |
| 4 | No | client |
| 10 | No | driver |
| 11 | No | driver |
| 12 | No | driver |
| 13 | No | driver |
+----------+--------+--------+
写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率。
基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。
取消率的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)
+------------+-------------------+
| Day | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 | 0.33 |
| 2013-10-02 | 0.00 |
| 2013-10-03 | 0.50 |
+------------+-------------------+
==========================题解SQL==========================
SELECT t.Request_at 'Day' ,
ROUND(SUM(Status like 'cancelled%')/COUNT(1),2) 'Cancellation Rate'
FROM Trips t,
Users u1,
Users u2
WHERE t.Client_Id = u1.Users_Id
AND t.Driver_Id = u2.Users_Id
AND u1.Banned <>'Yes'
AND u2.Banned <>'Yes'
AND t.Request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY t.Request_at
- 626题 ：换座位
小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。
其中纵列的 id 是连续递增的
小美想改变相邻俩学生的座位。
你能不能帮她写一个 SQL query 来输出小美想要的结果呢？
示例：
+---------+---------+
| id | student |
+---------+---------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+---------+---------+
假如数据输入的是上表，则输出结果如下：
+---------+---------+
| id | student |
+---------+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+---------+---------+
注意：如果学生人数是奇数，则不需要改变最后一个同学的座位。
SELECT t.num as 'id',
t.student
FROM
(SELECT IF(id%2<>0,IF(id<>(SELECT max(id) FROM seat),id+1,id),id-1) as num,student
FROM seat
order by num asc) t
- 185 题 ：部门工资前三高的所有员工
Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name，工资 Salary 和部门编号 DepartmentId 。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
| 7 | Will | 70000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
解释：
IT 部门中，Max 获得了最高的工资，
Randy 和 Joe 都拿到了第二高的工资，Will 的工资排第三。
销售部门（Sales）只有两名员工，Henry 的工资最高，Sam 的工资排第二。
===思路1：相同部门的员工（包含自己）比自己高的工资去重之后不会大于三个====
SELECT
e. NAME EmployeeName,
e.Salary,
d. NAME DepartMentName
FROM
Employee e
INNER JOIN department d ON e.DepartMentId = d.Id
WHERE 3 >= (
SELECT COUNT(DISTINCT e2.Salary)
FROM Employee e2
WHERE e2.DepartMentId = e.DepartMentId
AND e2.Salary >= e.Salary
)
ORDER BY e.DepartMentId ASC,e.Salary DESC
===思路2 将部门工资降序去重后，找倒数第三个，找不到就找倒数第二个，找不到就找倒数第一个
SELECT d.name Department,
e.name Employee,
e.Salary Salary
FROM Employee e
INNER JOIN Department d on d.Id = e.DepartmentId
WHERE Salary >= (
ifNull((select distinct em.Salary from Employee em
where em.DepartmentId = e.DepartmentId
order by em.Salary desc
limit 2,1),
ifNull((select distinct em.Salary from Employee em
where em.DepartmentId = e.DepartmentId
order by em.Salary desc
limit 1,1),
(select distinct em.Salary from Employee em
where em.DepartmentId = e.DepartmentId
order by em.Salary desc
limit 0,1)
)
)
)
ORDER BY e.DepartMentId,e.Salary DESC
- 体育馆的人流量
表：Stadium
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| visit_date | date |
| people | int |
+---------------+---------+
visit_date 是表的主键
每日人流量信息被记录在这三列信息中：序号 (id)、日期 (visit_date)、 人流量 (people)
每天只有一行记录，日期随着 id 的增加而增加
编写一个 SQL 查询以找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。
返回按 visit_date 升序排列的结果表。
查询结果格式如下所示。
Stadium table:
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 1 | 2017-01-01 | 10 |
| 2 | 2017-01-02 | 109 |
| 3 | 2017-01-03 | 150 |
| 4 | 2017-01-04 | 99 |
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-09 | 188 |
+------+------------+-----------+
Result table:
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-09 | 188 |
+------+------------+-----------+
id 为 5、6、7、8 的四行 id 连续，并且每行都有 >= 100 的人数记录。
请注意，即使第 7 行和第 8 行的 visit_date 不是连续的，输出也应当包含第 8 行，因为我们只需要考虑 id 连续的记录。
不输出 id 为 2 和 3 的行，因为至少需要三条 id 连续的记录。
====思路：a<b<c a<c<b b<a<c c<a<b b<c<a c<b<a
SELECT DISTINCT
a.*
FROM
Stadium a,
Stadium b,
Stadium c
WHERE a.people>=100
AND b.people>=100
AND c.people>=100
AND ((a.id = b.id-1 AND b.id = c.id -1) OR
(a.id = c.id-1 AND c.id = b.id -1) OR
(b.id = a.id-1 AND a.id = c.id -1) OR
(c.id = a.id-1 AND a.id = b.id -1) OR
(b.id = c.id-1 AND c.id = a.id -1) OR
(c.id = b.id-1 AND b.id = a.id -1)
)
ORDER BY id ASC;
1. 查询第N高的的薪水
set n = N-1;
select distinct Salary
from Employee
order by Salary desc
limit n,1
==========================================================================
2.给Score进行排名，给出Rank值，分数相同Rank相同
+----+-------+
| Id | Score |
+----+-------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
+----+-------+
排名后结果：
+-------+------+
| Score | Rank |
+-------+------+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
+-------+------+
select a.Score as Score,
(select count(distinct b.Score) from Scores b where b.Score >= a.Score) as Rank
from Scores a
order by a.Score DESC
过程分析：
--------------------------------------
最后的结果包含两个部分，第一部分是降序排列的分数，第二部分是每个分数对应的排名。
第一部分不难写：
select a.Score as Score
from Scores a
order by a.Score DESC
--------------------------------------
比较难的是第二部分。假设现在给你一个分数X，如何算出它的排名Rank呢？
我们可以先提取出大于等于X的所有分数集合H，将H去重后的元素个数就是X的排名。比如你考了99分，但最高的就只有99分，那么去重之后集合H里就只有99一个元素，个数为1，因此你的Rank为1。
先提取集合H：
select b.Score from Scores b where b.Score >= X;
我们要的是集合H去重之后的元素个数，因此升级为：
select count(distinct b.Score) from Scores b where b.Score >= X as Rank;
--------------------------------------
而从结果的角度来看，第二部分的Rank是对应第一部分的分数来的，所以这里的X就是上面的a.Score，把两部分结合在一起为：
select a.Score as Score,
(select count(distinct b.Score) from Scores b where b.Score >= a.Score) as Rank
from Scores a
order by a.Score DESC
==========================================================================
3.编写一个 SQL 查询，查找所有至少连续出现三次的数字
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
预期结果为：
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
连续出现的意味着相同数字的 Id 是连着的，由于这题问的是至少连续出现 3 次，我们使用 Logs 并检查是否有 3 个连续的相同数字。
SELECT *
FROM
Logs l1,
Logs l2,
Logs l3
WHERE
l1.Id = l2.Id - 1
AND l2.Id = l3.Id - 1
AND l1.Num = l2.Num
AND l2.Num = l3.Num
=======================
Id Num Id Num Id Num
1 1 2 1 3 1
=======================
然后我们从上表中选择任意的 Num 获得想要的答案。同时我们需要添加关键字 DISTINCT ，因为如果一个数字连续出现超过 3 次，会返回重复元素。
SELECT DISTINCT
l1.Num AS ConsecutiveNums
FROM
Logs l1,
Logs l2,
Logs l3
WHERE
l1.Id = l2.Id - 1
AND l2.Id = l3.Id - 1
AND l1.Num = l2.Num
AND l2.Num = l3.Num
==========================================================================
4.查询收入超过他们经理的员工的姓名
Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。
+----+-------+--------+-----------+
| Id | Name | Salary | ManagerId |
+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
+----+-------+--------+-----------+
给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。
+----------+
| Employee |
+----------+
| Joe |
+----------+
select Name as Employee
from Employee x
where ManagerId is not null
and Salary >
(select Salary
from Employee y
where x.ManagerId = y.id )
-----------------------------------------------------------
SELECT
a.Name AS 'Employee'
FROM
Employee AS a,
Employee AS b
WHERE
a.ManagerId = b.Id
AND a.Salary > b.Salary
-----------------------------------------------------------
SELECT
a.NAME AS Employee
FROM Employee AS a
JOIN Employee AS b
ON a.ManagerId = b.Id
AND a.Salary > b.Salary
==========================================================================
5.查询每个部门获得前三高工资的所有员工
Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name，工资 Salary 和部门编号 DepartmentId 。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
| 7 | Will | 70000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
解释：
IT 部门中，Max 获得了最高的工资，Randy 和 Joe 都拿到了第二高的工资，Will 的工资排第三。销售部门（Sales）只有两名员工，Henry 的工资最高，Sam 的工资排第二。
------------------------------------------------------------
select d.name Department,e.name Employee,e.Salary Salary
from Employee e
inner join Department d on d.Id = e.DepartmentId
where Salary >= (
ifNull((select distinct em.Salary from Employee em
where em.DepartmentId = e.DepartmentId
order by em.Salary desc
limit 2,1),
ifNull((select distinct em.Salary from Employee em
where em.DepartmentId = e.DepartmentId
order by em.Salary desc
limit 1,1),
(select distinct em.Salary from Employee em
where em.DepartmentId = e.DepartmentId
order by em.Salary desc
limit 0,1))
)
)
ORDER BY Department,Salary desc;
------------------------------------------------------------
核心思想：思路转变-->将同组工资进行去重，那么大于第三高工资的只有2个，大于第二高的只有1个，大于第一高的0个，换句话说，只要大于前面工资的小于3 就是前三高
SELECT d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM Employee e1
JOIN Department d ON e1.DepartmentId = d.Id
WHERE
3 > (SELECT COUNT(DISTINCT e2.Salary)
FROM Employee e2
WHERE e2.Salary > e1.Salary
AND e1.DepartmentId = e2.DepartmentId
)
ORDER BY Department,Salary desc;
==========================================================================
6.查询所有从不订购任何东西的客户
某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。
Customers 表：
+----+-------+
| Id | Name |
+----+-------+
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
+----+-------+
Orders 表：
+----+------------+
| Id | CustomerId |
+----+------------+
| 1 | 3 |
| 2 | 1 |
+----+------------+
例如给定上述表格，你的查询应返回：
+-----------+
| Customers |
+-----------+
| Henry |
| Max |
+-----------+
select c.Name as 'Customers'
from Customers c
left join Orders o on o.CustomerId = c.Id
where o.CustomerId is null
-------------------------------------------------------------------------------------
select c.Name as `Customers`
from Customers c
where c.Name not in
(select b.Name from Customers b inner join Orders o on o.CustomerId = b.Id )
==========================================================================
7.查询每个部门工资最高的员工
Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
SELECT d.name as 'Department',x.name as 'Employee',x.Salary as Salary
FROM Employee x
INNER JOIN Department d ON d.Id = x.DepartmentId
WHERE x.Salary >=
All(SELECT y.Salary FROM Employee y WHERE x.DepartmentId = y.DepartmentId )
==========================================================================
8.查找与之前（昨天）日期相比温度更高的所有日期的Id
给定一个 Weather 表，编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 Id。
+---------+------------------+------------------+
| Id(INT) | RecordDate(DATE) | Temperature(INT) |
+---------+------------------+------------------+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+---------+------------------+------------------+
例如，根据上述给定的 Weather 表格，返回如下 Id:
+----+
| Id |
+----+
| 2 |
| 4 |
+----+
SELECT w1.id as 'Id'
FROM Weather w1
INNER JOIN Weather w2
ON DATEDIFF(w1.RecordDate, w2.RecordDate) = 1 # 相当于w1的天数-w2的天数等于1天
AND w1.Temperature > w2.Temperature
---------------------------------------------------------------------------
SELECT
w1.id AS 'Id'
FROM
weather w1
JOIN
weather w2 ON DATE_FORMAT(w1.RecordDate,'%y%m%d')-
DATE_FORMAT(w2.RecordDate,'%y%m%d') = 1
AND w1.Temperature > w2.Temperature
==========================================================================
9.删除 Person 表中所有重复的电子邮箱，保留ID最小的那个
编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小的那个。
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
+----+------------------+
Id 是这个表的主键。
例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
+----+------------------+
DELETE FROM Person
WHERE Id NOT in(
SELECT b.Id
FROM
(select Id
from Person x
where Id <= ALL(select Id from Person y where x.Email = y.Email)) AS b
)
##注意：必须把结果集当做1个表 先查一遍才能删除，
##不然mysql报错 You can't specify target table 'Person' for update in FROM clause
3833
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
