SQL语句小记

SQL

1、组合两个表

表1: Person

±------------±--------+
| 列名 | 类型 |
±------------±--------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
±------------±--------+
PersonId 是上表主键

表2: Address

±------------±--------+
| 列名 | 类型 |
±------------±--------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
±------------±--------+
AddressId 是上表主键

编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：

注意审题： 题目说：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息： FirstName, LastName, City, State 也就是说，地址信息（City, State）的查询结果是Null是OK的。但是，姓名（FirstName, LastName）必须有。 2） 为啥不用Where？ 因为where的实质就是根据你给的条件（personID相等），选取两表的公共部分。但是，因为PERSON表不是所有人都有地址信息的，但是ADDRESS表只显示有地址信息的人，这样选取出来的就是有地址信息的人，漏掉了没有地址信息的人。所以大家注意，where的本质就是过滤。 3） 如何连接？应该用PERSON表左连接（left join）ADDRESS表，保留person表的所有信息

left join … on

select p.FirstName,p.LastName,a.City,a.State from Person p left join Address a on p.PersonId = a.PersonId 

结果:

{"headers": ["FirstName", "LastName", "City", "State"], "values": [["Allen", "Wang", null, null], ["Bob", "Alice", "New York City", "New York"]]}

2、查询重复

编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。

示例：

±—±--------+
| Id | Email |
±—±--------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
±—±--------+

根据以上输入，你的查询应返回以下结果：

±--------+
| Email |
±--------+
| a@b.com |
±--------+

说明：所有电子邮箱都是小写字母。

--解法1  count(email)>1
select email from person group by email having count(email)>1

--解法2
select email from (select count(1) as t,email from person group by email)r  where r.t>1;

--解法3  效率最高
select distinct(p1.Email) from Person p1  join Person  p2 on p1.Email = p2.Email AND p1.Id!=p2.Id

一、查询

打开数据库

USE sqlstudty;

着重号 ` 用以标识字段，当关键字为字段时，以 着重号以区分.

意为表达 name 不是关键字,而是字段.

SELECT `name` FROM USER; 

查询的 可以是 常量、表达式、函数

USE sqlstudy;
SELECT 100;
SELECT 12*12;
SELECT VERSION();

起别名

• 关键字 as
• 使用 空格
• 如想取的别名中含有特殊符号(空格、￥、#等），需将别名加上双引号

SELECT 100 AS 常量;
SELECT 100 常量1;
SELECT 100 "特殊符号#"

去重

reader表

readerid	readername	identity	gender	school
S1001	胡峰	学生	男	计算机学院
S1002	包朦穗	学生	女	计算机学院
S1003	王文革	学生	女	计算机学院
S1004	高文丽	学生	女	计算机学院
S1005	郭鹏	老师	男	信息学院

• 关键字 DISTINCT

  可以多字段,但是就没有意义了

  SELECT DISTINCT identity FROM reader;  
  

identity
学生
老师

SELECT identity FROM reader;

identity

学生
学生
学生
学生
老师

加号"+"的作用

SELECT 100+100;  # 200
SELECT "100"+200;  # 300  对于字符,会先尝试转换为数字,不能则转换为 0
SELECT "tom"+100;  # 100
SELECT "tom"+"cat";  # 0
SELECT NULL+100;  # null  只要有null,则结果必定为null

拼接函数 CONCAT

SELECT CONCAT(`readername`,`identity`) FROM reader

胡峰学生
包朦穗学生
王文革学生
高文丽学生
郭鹏老师

显示表结构

DESC `reader`;

Field Type Null Key Default Extra

---

readerid char(10) NO PRI (NULL)
readername varchar(10) NO (NULL)
identity varchar(10) YES (NULL)
gender varchar(10) YES (NULL)
varchar(10) YES (NULL)
school
tel varchar(11) YES (NULL)

IFNULL

• IFNULL(expr1,expr2)

判断expr1是否为空—> null, 如果为空,以 expr2 代替显示.

SELECT IFNULL(tel,'暂未填写电话') AS "ifnull",tel FROM reader;

ifnull tel

---

暂未填写电话 (NULL)
1234 1234
暂未填写电话 (NULL)
暂未填写电话 (NULL)
暂未填写电话 (NULL)

ISNULL

判断如果为空,返回1,否则返回0.

SELECT ISNULL(tel) AS "iSnull",tel FROM reader;

iSnull tel

---

1 (NULL)
0 1234
1 (NULL)
1 (NULL)
0 110

二、条件查询

• 基本语法

select 字段 from 表名 where 条件

SELECT * FROM reader WHERE gender = '男'

• 条件运算符

  条件运算符	备注
  >	大于
  <	小于
  != 或 <>	不等于
  =	等于
  >=	大于等于
  <=	小于等于

• 逻辑运算符

  逻辑运算符	备注
  && 或 and	且
  || 或 or	或
  ! 或 not	非

表示取到 年龄小于 18 或者 大于 60 的

三种表示方法:

old < 18 or old > 60

not (old >=18 and old= <60)  # 不在 18-60之间

not between 18 and 60  # 注意:包含了临界值,即18与60.

模糊查询

关键字

• like

  where readername like '%王%';  # 查询 readername 含 '王' 的
  

  • 通配符 % 和 _

    where readername like '王_';  # 查询 readername 第一个字为'王',且readername为两个字的
    

    转义字符 \

    当我想查询的字段名包括 下划线 _ 时,我不想让它成为 通配符.

    where readername like '_王\_';    # 查询 readername为三个字符,且第二三个字为'王_'
    

    或者 使用关键字 ESCAPE 达到同样效果 , $ 可替换任意字符

    表示标识 $ 为转义字符

    where readername like '_王$_' ESCAPE '$';
    

• between and

  between 18 and 60 # 注意:包含了临界值,即18与60.
  

• in

  判断是否属于

  下面两句可达到相同效果.

  注意:列表中的值 类型需保持相同或兼容

  SELECT * FROM reader WHERE readername = '胡峰' OR readername = '郭鹏';
  SELECT * FROM reader WHERE readername IN ('胡峰','郭鹏');
  

• is null

• is not null

where tel is null;  # 判断电话为空的
where tel is not null;  # 不为空

• 安全等于 <=>

  同义

  where tel is null;
  where tel <=> null;
  

  但是 与 where tel = null; 不同义

  同义

  where tel = 10086;
  where tel <=> 10086;
  

  但是不能写为 where tel is 10086; 语句报错.

  Mysql服务的登录与退出

  登录:

  mysql [-h 主机名 -p 端口号] -u用户名 -p密码
  

  退出:

  exit 或者 快捷键 Ctrl+C三、排序查询
  

三、排序查询 ORDER BY

• 升序asc

• 降序desc

• 默认升序 即 asc可省

• 除limit子句外,order by都放在最后.

  按字段排序

  SELECT * FROM reader ORDER BY `readerid` ASC  # 排序写在最后
  

  按表达式排序 或别名

SELECT *,IFNULL(tel,0)*10 "表达式排序" FROM reader ORDER BY 表达式排序

readerid readername identity gender
school tel 表达式排序

---

S1001 胡峰是的 学生 男 计算机学院 (NULL) 0
S1003 大王_ 学生 女 计算机学院 (NULL) 0
S1004 高文丽但是 学生 女 计算机学院 (NULL) 0
S1005 郭鹏 老师 男 信息学院 110 1100
S1002 包朦穗啊是的 学生 女 计算机学院 1234 12340

LENGTH()函数 获取长度

一个汉字3个字节,

按照名字长度排序

SELECT * FROM reader ORDER BY LENGTH(`readername`) 

SELECT *,LENGTH(`readername`) "名字长度" FROM reader ORDER BY 名字长度

readerid readername identity gender
school tel 名字长度

---

S1005 郭鹏 老师 男 信息学院 110 6
S1003 大王_ 学生 女 计算机学院 (NULL) 7
S1001 胡峰是的 学生 男 计算机学院 (NULL) 12
S1004 高文丽但是 学生 女 计算机学院 (NULL) 15
S1002 包朦穗啊是的 学生 女 计算机学院 1234 18

多字段排序,先写先排序

单行函数

字符函数

length() 长度

作用:获取参数值得字节数

utf-8 字符集

一个汉字占用3个字节

一个字母占用一个字节

例:统计type字段的长度

SELECT `type`,LENGTH(TYPE) 'type长度' FROM books

concat() 拼接

作用:拼接字符串

upper、lower 大小写转换

作用：大小写转换

例:将bookId字段转换为小写

SELECT `bookid`,LOWER(`bookid`) FROM `books`

substr、substring 切片

substr只是substring的简写,

注意:SQL中索引从1开始

# 从索引4开始输出 --->    "的SUBSTR函数使用"

SELECT SUBSTR('SQL的SUBSTR函数使用',4) out_put

# 从索引5开始输出,长度为6的字符串 输出---->  "SUBSTR"

SELECT SUBSTR('SQL的SUBSTR函数使用',5,6) out_put

函数间可嵌套使用

instr 查索引

返回子串第一次出现的索引,找不到则返回 0

例:

SELECT INSTR('你好!SQL','SQL') OUTPUT

trim 除空格

去除空格输出

SELECT '      张翠花     ' les,TRIM('      张翠花     ') out_put

去除前后指定字符,注意,中间的无法去除

# 输出 张a翠花

SELECT TRIM('a' FROM 'aaaaaa张a翠花aaaaaaaa') out_put

lpad 左填充

填充在左边

# 使用 '啊' 将 '你好' 填充至长度为5

SELECT LPAD('你好',5,'啊') out_put

# 输出  '啊啊啊你好'

# 长度不足填充时,会截取

SELECT LPAD('你好',1,'啊') out_put

# 输出  '好'

rpad 右填充

# 使用 '啊' 将 '你好' 填充至长度为5

SELECT RPAD('你好',5,'啊') out_put

# 输出  '你好啊啊啊'

# 长度不足填充时,会截取

SELECT RPAD('你好',1,'啊') out_put

# 输出  '你'

replace 替换

注意:将所有出现的 都会 替换

SELECT REPLACE('PHP是世界上最好的编程语言','PHP','java') out_put

#输出 'java是世界上最好的编程语言'

数学函数

round 四舍五入

SELECT ROUND(1.49),ROUND(1.5)
#输出   1     2

# 四舍五入保留小数点后两位
SELECT ROUND(1.49321,2),ROUND(1.49754,2)
# 输出  1.49    1.50

ceil与floor 向上取整与向下取整

SELECT CEIL(1.001),FLOOR(1.999)
# 输出   2     1

truncate 截取

SELECT TRUNCATE(1.49754,2)
# 保留小数点后两位 截断,不四舍五入  输出  1.49

mod 取余

mod(a,b) == a-a/b*b

SELECT MOD(624,10)
select 624%10
# 同义

时间函数

now()

获取当前时间

curdate

curtime

SELECT NOW()
# 2021-11-03 23:36:37
SELECT CURDATE()
# 2021-11-03
SELECT CURTIME()
# 23:37:04

year

只 获取年份

SELECT YEAR(NOW())
# 2021
SELECT YEAR('1998-07-24')
# 1998
SELECT YEAR(pubdate) '年' FROM `books`
# 

month / monthname

SELECT MONTH(NOW())
# 11
SELECT MONTHNAME(NOW())
# November  返回月份英文名字

datediff 日期差

计算两个日期之间相差的天数, MAX(pubdate) 减去 MIN(pubdate),交换将会得到负值

SELECT DATEDIFF(MAX(`pubdate`),MIN(`pubdate`)) '相差天数' FROM `books`

SELECT DATEDIFF(NOW(),'2001-05-23') '计算活了多少天'
# 7470

时间格式转换 str_to_date

作用:识别 年月份

不同格式的话,SQL并不知道哪个是年,哪个是月,哪个是日,需 使用 str_to_date标识

SELECT STR_TO_DATE('2020-3-25','%Y-%c-%d') out_put
# 2020-03-25

日期转为字符 date-format

SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日')
# 2021年11月03日

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-t0icwfqN-1640141208497)(…/python/笔记/img/image-20211103234209833.png)]

其他函数

SELECT VERSION()  #mysql版本
SELECT DATABASE()  # 当前数据库
SELECT USER()  # 当前用户

流程控制函数

if

SELECT IF(10>9,'大','小') '比较大小10>9'

case

类似switch…case…

判断 number 字段的值与when 中是否相等,相等显示 对应then 中的值

SELECT number,
CASE number
WHEN 23 THEN '23'
WHEN 27 THEN '27'
ELSE '其他'
END AS 显示
FROM books

多重if

SELECT number,
CASE 
WHEN number >20 THEN '大于20的'
WHEN number=20 THEN '等于20'
ELSE '小于20'
END AS 判断
FROM books

分组函数

基本功能:作统计使用

• NULL都不参与
• 都支持与distinct关键字
• 和分组函数一同查询的字段有限制,group by

# 行数不对等,count(*)只有一行,而type字段有 count(*) 行

SELECT COUNT(*),type FROM books

COUNT(字段) 计数

统计字段出现的次数,不会统计为 NULL 的

例:想查询出teble表 名字 中,带有 “王” 的,有多少个人

select count(name) from table where name like concat('%',王,'%')

# 统计type字段有多少个学院,去重
SELECT COUNT(DISTINCT `type`) FROM books

#  统计行数,只要有一个字段不为NULL,就会被统计
# COUNT(*) 最常用,效率最高
SELECT COUNT(*) FROM books
# 以下都可以得到相同结果
select count(1) from books

select count('一个常量') from books

SUM(字段) 求和

合计字段之和,NULL不参与运算求和

例:薪资表中,想统计一共发了多少薪资(每个人薪资之和)

select sum(Salary) '总薪资' from table

avg 平均

平均值会保留小数点后4位,NULL不参与运算求和

sum与avg一般只处理数值类型的

max / min

NULL不参与

SELECT SUM(`number`) '求和',AVG(`number`) '平均值',MAX(`number`) '最大值',MIN(`number`) '最小值' FROM books

分组查询group by

基本语法

select '字段','分组字段/表达式/函数' 
from 表名
[where 条件] 可省
group by '分组字段/表达式/函数'
[order by 字段] 可省

查询每个学院 number字段的和与平均值

SELECT SUM(`number`),AVG(`number`),`type` FROM `books` GROUP BY `type`

having 关键字

添加分组后的筛选条件,—先查询,再进行having 筛选

放在最后

查询每个学院 number字段的和与平均值,且和大于100的

SELECT SUM(`number`),AVG(`number`),`type` FROM `books` GROUP BY `type` HAVING SUM(`number`)>100

where关键字

—> 查询时,根据条件筛选

分组前筛选—where-----在group by前

分组后筛选----having—在group by后

若where与having都可,优先where效更高

需求:隐藏那个手机号中间4位

隐藏 密码中间两位

SELECT REPLACE(`password`,SUBSTR(`password`,3,2),'**' ) out_put,`password` FROM `users`