Mysql之数据查询语言

查询（DQL）

一、使用MySQL

1. 基本命令

• 1）连接数据库：
  • mysql -u root -p
• 2）查看当前都有哪些数据库：show databases
• 3）使用数据库：use databaseName
• 4）查看当前数据库有哪些表：show tables
• 5）查看表的列：show columns from tableName

2. 其他命令

• 显示广泛的服务器状态信息:show status
• 显示创建特定数据库的MySQL语句：show create database databaseName
• 显示创建特定表的MySQL语句：show create table tableName
• 显示服务器的错误或警告信息：show errors、show warnings

二、简单检索

1. SELECT基本语句

• 检索单个列：select prod_name from products
• 检索多个列：select prod_id, prod_name, prod_price from products
• 检索所有列：select * from products
  • "*"为通配符

2. 检索关键字

• distinct：检索不同的行：select distinct vend_id from products
  • 若vend_id有重复的，检索结果只会显示出一个
• limit：限制结果：select prod_name from products limit 5
  • MySQL返回结果小于等于5行
  • 从第5行开始(不包括第5行)检索3行：select prod_name form products limit 5,3
• 完全限定的表名：select product.prod_name from product

三、排序检索

• 排序关键字：order by
• 按名字排序：select prod_name from produce order by prod_name
• 降序检索（desc）：select prod_name from produce order by prod_name desc
  • 默认为升序：ASC
  • 若有多个列排序检索，应在每个列后面加上DESC

四、过滤检索

1. 简单where语句

• 过滤检索关键字：where
• where子句操作符：
  • =、<>（不等于）、!=、<、<=、>、>=、between and
• 空值（NULL）检查：select prod_name from products where prod_price is null

2. 操作符

• 1）ADN、OR操作符
  • and：select prod_id, prod_price, prod_name from products where vend_id = 1003 and prod_price <= 10
  • or：select prod_name, prod_price from products where vend_id = 1002 or vend_id = 1003
  • 计算次序：and > or
• 2）IN操作符
  • in与or的作用相同
  • in操作符指明范围：select prod_name, prod_price from products where vend_id in (1002, 1003)
  • 使用in的好处：
    • 语法清楚、直观
    • 计算次序容易管理
    • in操作符比or操作符执行的快
    • in可以包含其他select语句
• 3）NOT操作符
  • not操作符用来否定后面的条件
  • select prod_name, prod_price from products where vend_id not in (1002,1003) order by prod_name

五、通配符

1. 简介

• 通配符：用来匹配值得一部分的特殊字符
• 搜索模式：由字面值、通配符或两者组合构成的所搜条件
• 通配符一般配合LIKE操作符使用

2. LIKE操作符

• 在搜索语句中使用通配符，必须使用LIKE操作符
• 谓词

3. 通配符

• 1）%通配符：n个字符（n >= 0）
  • select prod_id, prod_name from products where prod_name like 'jet%'：找出以jet开头的产品
  • %不能匹配NULL
• 2）_ 通配符：1个字符：与%使用方法一致

4. 注意

• 通配符搜索处理一般比前面讨论的其他搜索所花时间长
• 若非必要，不要将通配符用在搜索模式的开始处
• LIKE匹配整个列，如果被匹配的文本在列值中出现，LIKI将不会找到它，相应的行也不会被返回（除非使用通配符）

六、正则表达式检索

1. 简介

• 正则表达式自3.23.4后不区分大小写，若要区分大小写，使用BINARY关键字
  • 如：where prod_name regexp binary 'JetPack .000'
• REGEXP

2. 使用

• 1）基本使用：

  • select prod_name from products where prod_name REGEXP '.000' order byy prod_name
  • “.”表示任意一个字符

• 2）OR匹配：|

  • 为搜索两个串之一，使用 |
  • select prod_name from products where prod_name regexp '1000|2000' order by prod_name

• 3）匹配几个字符之一：[ ]

  • select prod_name from products where prod_name REGEXP '[123] Ton' order by prod_name
  • 匹配1 ton或者2 ton 或者 3 ton
  • 等同于[1|2|3] ton
  • 否定：[ ^123]，匹配1、2、3之外的所有

• 4）匹配范围：[1-5]

• 5）匹配特殊字符

  • 对于匹配："|"、"[]“特殊字符，需要转义字符：”’\ "

• 6）匹配字符类

  • s

  • 类	说 明

• 7）匹配多个实例

  • 元字符	说明
    *	0个或多个
    +	1个或多个（等于{1,}）
    ？	0个或1个（等于{0,1}）
    {n}	指定数目的匹配
    {n, }	不少于指定数目的匹配
    {n,m}	匹配数目的范围(m不超过255)

• 8）定位符

  • 元字符	说 明
    ^	文本的开始
    $	文本的结尾
    [[:<:]]	词的开始
    [[:>:]]	词的结尾

  • ^两种用途；

    • 集合中：即[…]中表示否定
    • 其他：指串的开始处

七、创建计算字段

1. 拼接字段

• Concat()：一个或多个串，中间用“，”隔开

• 例如：

  • select Concat(vend_name, '(',vend_country,')')
        -> from vendors
        -> order by vend_name;
    +-----------------------------------------+
    | Concat(vend_name, '(',vend_country,')') |
    +-----------------------------------------+
    | ACME(USA)                               |
    | Anvils R Us(USA)                        |
    | Furball Inc.(USA)                       |
    | Jet Set(England)                        |
    | Jouets Et Ours(France)                  |
    | LT Supplies(USA)                        |
    +-----------------------------------------+
    

  • RTrim()：可以去掉串右边多余空格。

  • LTrim()

  • Trim()

2. 起别名

• AS

• 如：

  • select Concat(vend_name, '(',vend_country,')') AS vend_title
        -> from vendors
        -> order by vend_name;
    

3. 算术计算

• select quantity * item AS expanded_price from orderitems where order_num = 20005

八、函数

1. 文本处理

函数	说明
Left（）	返回串左边字符
Length（）	返回串长度
Locate（）	找出串的一个字串
Lower（）	转小写
Upper（）	转大写
Trim（）	去掉串两边空格
Right（）	返回串右边的字符
Soundex（）	返回串soundex的值
SubString（）	返回字串的字符
concat()	连接

2. 日期时间

• now：返回当前日期+时间
• year：返回年
• month：返回月
• day：返回日
• data_format：将日期返回字符
• curdate：返回当前日期
• str_to_date：将字符转换为日期
• curtime：返回当前时间
• hour：小时
• minute：分钟
• second：秒
• datediff：返回两个日期相差天数
• monthname：以英文形式返回月

3. 数值处理

• ceil：向上取整
• round：四舍五入
• mod：取模
• floor：向下取整
• truncate：截断
• rand：获取随机数，返回0-1之间的小数

4. 汇聚函数

• count（字段）：统计该字段非空值的个数
• count（*）：统计结果集的行数

5. 其他函数

• version：当前数据库服务器版本
• database：当前打开的数据库
• user：当前用户
• password（‘字符’）：返回该字符的加密形式
• md5（‘字符’）：返回md5的加密形式

九、分组数据

1. 数据分组

• 语法：group by …
• select vend_id ,coutn(*) as num_prods from products group by vend_id
• group by指示MYSQL按vend_id排序并分数数据。这导致对每个vend_id而不是整个表计算num_prods一次

2. 过滤分组

• 语法：having…
• where和having的区别
  • where过滤指定的是行而不是分组。在数据分组前进行过滤
  • having过滤分组，在数据分组后进行过滤
• select cust_id, count(*) as orders from orders group by cust_id having count(*) >=2

十、子查询

例题：列出订购物品TNT2的所有客户

• 1）检索包含物品TNT2的所有订单编号

• 2）检索具有前一步骤列出的订单编号的所有客户的ID

• 3）检索前一步骤返回的所有客户ID的客户信息

• 代码：

• select cust_id
  from orders
  where order_num in(select order_num
  				from orderitems
  				where prod_id = 'TNT2')
  

注意：

• where子句中使用子查询，应该保证select语句具有与where子句中相同数目的列

十一、联结表

1. 简介

• 联结单词：join
• 关系表
• 外键：某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系
• 可伸缩性：设计良好的数据库或应用程序称之为可伸缩性好
• 为什么使用联结：分解数据为多个表能更有效的存储，更方便处理，并且具有更大的可伸缩性。查询多个表，使用联结
• 笛卡尔积：由没有联结关系的表返回的结果为笛卡尔积。检索出的行的数目是第一个表中的行数乘以第二个表中的行数
• 分类：
  • 内联结：
    • 等值联结
    • 非等值联结
    • 自联结
  • 外联结：
    • 左联结
    • 右联结
    • 权联结
  • 交叉联结

2. 内联结

① sql92语法

where连接

② sql99语法：

• select 查询列表
  from 表1 别名 【联结类型】
  join 表2 别名
  on 联结条件
  where 【筛选条件】
  

• 联结类型：

  • 内联结：inner
  • 左外：left 【outer】
  • 右外：right 【outer】
  • 全外：full 【outer】
  • 交叉联结：cross

③ 特点

• 添加排序、分组、筛选
• inner可以省略
• 筛选条件放在where后面，联结条件放在on后面，提高分离性，便于阅读

④ 实现

#一、内联结
#（一）等值联结
#案例1.查询员工名、部门名
select last_name, department_name
from employees e
inner join departments d
on e.department_id = d.department_id;

#案例2. 查询名字中包含e的员工名和工种名（添加筛选）
SELECT last_name, job_title
from employees e
inner join jobs j
on e.job_id = j.job_id
where last_name like '%e%';

#案例3.查询部门个数>3的城市名和部门个数（添加分组+筛选）
select city, count(*) 部门个数
from departments d
inner join locations l
on d.location_id = l.location_id
group by city
having count(*) > 3;
	 
#案例4.	查询员工名、部门名、工种名、并按部门名降序（三表联结）
select last_name, department_name, job_title
from employees e
inner join departments d on e.department_id = d.department_id
inner join jobs j on e.job_id = j.job_id
order by department_name desc;

#（二）非等值联结
#案例1. 查询员工的工资级别
select salary, grade_level
from employees e
join job_grades g
on e.salary between g.lowest_sal and g.highest_sal;

#三）自联结
#查询员工的名字、上级名字
select e.last_name, m.last_name
from employees e
join employees m
on e.manager_id = m.employee_id

3. 外联结

① 注意

• 1）应用场景
  • 用于查询一个表中有，一个表中没有的记录
• 2）特点：
  • 外联结的查询结果为主表中所有记录
  • 如果从表中有和他匹配的，则显示匹配的值
  • 如果没有匹配的，则显示null
  • 外联结查询结果 = 内联结结果 + 主表中有而从表中没有的结果
• 3）何为主从表？
  • 左联结：left join左边是主表
  • 右联结：right join右边是主表
• 4）左外和右外交换两表的顺序，可以实现同样的效果
• 5）全外连接=内联结的结果+表1中有但表2中没有的+表2有但表1中没有的

② 实现

#二、外联结
#引入 查询男朋友 不在男神表的女神名
use girls;
select * from beauty;
	
select b.name, 	bo.*
from beauty b
left join boys bo
on b.boyfriend_id = bo.id;

# 全外
use girls;
select b.*, bo.*
from beauty b
full outer join boys bo
on b.boyfriend_id = bo.borfriend_id;

# 交叉
select b.*, bo.*
from beauty b
cross join boys bo;

4. 分页查询

① 语法

select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序字段】
limit offset, size;
offert：要显示条目的其实索引（从0开始）
size：要显示的条目个数

② 实现

## 分页查询
#案例1.查询前五条员工信息
SELECT * FROM employees
LIMIT 0,5;

#案例2.查询第11条 -- 25 条
SELECT *
from employees
limit 10, 15

#案例3.有奖金的员工信息，并且工资较高的前10名
select * 
from employees
where commission_pct is not null
order by salary desc
limit 10;

③ 公式

• 要显示的页数：page
• 每页的条目数：size
• 公式：limit (page - 1)*size, size;

十二、联合查询

① 简介

• 关键字：union
• 联合查询：将多条查询语句的结果合并成一个结果

② 语法

查询语句1
union
查询语句2
union
...

③ 应用场景

• 1）查询结果来自与多个表
• 2）多个表之间没有直接的连接关系，但查询信息一致

④ 注意

• 多条查询语句的查询列数必须一致
• 多条查询语句的查询的每一列的类型和顺序最好一致
• union会去重
  • 如果不想去重，使用关键字：union all