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表

• 关键字 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 = '男'

• 条件运算符

• 逻辑运算符

表示取到 年龄小于 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

SQL语句小记（下）：https://developer.aliyun.com/article/1488679