MySql（DQL）

数据库的好处

1. 持久化数据到本地
2. 可以实现结构化查询，方便管理

数据库的相关概念

1. DB(Database)：数据库，保存一组有组织的数据的容器
2. DBMS（Database Manage System）：数据库管理系统，又称为数据库软件（产品），用于管理DB中的数据
3. SQL（Structured Query Language）:又称为数据库软件（产品），用于管理DB中的数据

数据存储数据的特点

1. 将数据放到表中，表再放到库中
2. 一个数据库中可以有多个表，每个表都有一个的名字，用来标识自己。表名具有唯一性。
3. 表具有一些特性，这些特性定义了数据在表中如何存储，类似java中 “类”的设计。
4. 表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似java 中的”属性”
5. 表中的数据是按行存储的，每一行类似于java中的“对象”。

Mysql的常见命令

1. 查看当前所有的数据库
   show databases;
2. 打开指定的库
   use + 库名
3. 查看当前库的所有表
   show tables;
4. 查看当其他库的所有表
   show tables from 库名;
5. 创建表
   create table 表名（
   列名 列类型
   列名 列类型
   ·····
   ）;
6. 查看表结构
   desc 表名;
7. 查看服务器的版本
   方式一：登录到mysql服务端
   select version();
   方式二：没有登录到mysql服务端
   mysql –version
   或
   mysql –V

SQL语言分类

DQL（Data Query Language）：数据查询语言
select
DML(Data Manipulate Language):数据操作语言
insert 、update、delete
DDL（Data Define Languge）：数据定义语言
create、drop、alter
TCL（Transaction Control Language）：事务控制语言
commit、rollback

DQL语言

基础查询
条件查询
排序查询
常见函数
分组函数
分组查询
连接查询

一、 基础查询

1. 语法：
   SELECT 要查询的东西
   FROM 表名；
   例：

   SELECT last_name
   FROM employees;

2. 去重：
   案例：查询员工表中涉及到的所有的部门编

   SELECT DISTINCT department_id
   FROM employees;

3. + 的作用
   比较：Java中的+ ：
   ①运算符：两个操作数都为数值型
   ②连接符：只要有一个操作数为字符串
   mysql中的+ ：
   仅仅只是运算符
   例：①select 100+90; 两个操作数都为数值型，则做加法运算
   ②select ‘123’+90;只要其中一方为字符型，试图将字符型数值转换成数值型，如果转换成功，则继续做加法运算
   ③select ‘john’+90; 如果转换失败，则将字符型数值转换成0
   ④select null+10; 只要其中一方为null，则结果肯定为null

二、条件查询

条件查询：根据条件过滤原始表中的数据，查询到想要的数据
语法：
SELECT
要查询的字段|表达式|常量值|函数
FROM
表名
WHERE
条件；
例：
SELECT
last_name
FROM
employees
WHERE
last_name LIKE ‘%a%’;

分类：
条件表达式
示例：salary>10000
条件运算符：
> < >= <= = != <>

逻辑表达式
示例：salary>10000 && salary<20000

逻辑运算符：
and（&&）:两个条件如果同时成立，结果为true，否则为false
or(||)：两个条件只要有一个成立，结果为true，否则为false
not(!)：如果条件成立，则not后为false，否则为true

模糊查询
①like
②between and
③in
④in null
案例1：last_name like ‘a%’
案例2：查询员工编 在100到120之间的员工信息
SELECT
*
FROM
employees
WHERE
employee_id BETWEEN 120 AND 100;

三、排序查询

语法：
SELECT
要查询的字段
FROM
表名
WHERE
条件
ORDER BY 排序的字段|表达式|函数|别名 【asc（升序、默认）|desc（降序）】

例：
SELECT
last_name,salary
FROM
employees
WHERE
salary>12000
ORDER BY salary DESC;

四、常见函数

①单行函数
字符函数
concat 拼接（两个varchars）
案例一：concat 拼接字符串
SELECT CONCAT(last_name,’_’,first_name) 姓名
FROM employees;
substr、substring 截取子串

注意：索引从1开始
#截取从指定索引处后面所有字符
SELECT SUBSTR(‘李莫愁爱上了陆展元’,7) out_put;

截取从指定索引处指定字符长度的字符
SELECT SUBSTR(‘李莫愁爱上了陆展元’,1,3) out_put;

upper 转换成大写
SELECT UPPER(‘john’);

lower 转换成小写
SELECT LOWER(‘joHn’);

trim 去前后制定的空格和字符（指定字符）
ltrim（left）去左边空格
rtrim（right）去右边空格

SELECT LENGTH(TRIM(’ 张翠山 ‘)) AS out_put;

SELECT TRIM(‘aa’ FROM ‘aaaaaaaaa张aaaaaaaaaaaa翠山aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa’)
AS out_put;

replace 替换
SELECT REPLACE(‘周芷若周芷若周芷若周芷若张无忌爱上了周芷若’,‘周芷若’,‘赵敏’)
AS out_put;

lpad 左填充
SELECT LPAD(‘殷素素’,2,’*’) AS out_put;

rpad 右填充
SELECT RPAD(‘殷素素’,12,‘ab’) AS out_put;

instr 返回子串第一次出现的索引
SELECT INSTR(‘杨不殷六侠悔爱上了殷六侠’,‘殷八侠’) AS out_put;

length 获取字节个数
SELECT LENGTH(‘john’);

数学函数
round 四舍五入
SELECT ROUND(-1.55);
SELECT ROUND(1.567,2);

rand 随机数
floor 向下取整
SELECT FLOOR(-9.99);

ceil 向上取整
SELECT CEIL(-1.02);

mod 取余
SELECT MOD(10,-3);
SELECT 10%3;

truncate 截断
SELECT TRUNCATE(1.69999,1);

日期函数
now当前系统日期+时间
SELECT NOW();

curdate当前系统日期
SELECT CURDATE();

curtime当前系统时间
SELECT CURTIME();

str_to_date 将字符转换成日期
SELECT STR_TO_DATE(‘1998-3-2’,’%Y-%c-%d’)
AS out_put;
SELECT DATE_FORMAT(NOW(),’%y年%m月%d日’) AS out_put;

#查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name,DATE_FORMAT(hiredate,’%m月/%d日 %y年’) 入职日期
FROM employees
WHERE commission_pct IS NOT NULL;

date_format将日期转换成字符

SELECT DATE_FORMAT(NOW(),’%y年%m月%d日’) AS out_put;

#查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name,DATE_FORMAT(hiredate,’%m月/%d日 %y年’) 入职日期
FROM employees
WHERE commission_pct IS NOT NULL;

流程控制函数
if 处理双分支
case语句 处理多分支
情况1：处理等值判断
情况2：处理条件判断
if else 的效果

SELECT IF(10<5,‘大’,‘小’);

SELECT last_name,commission_pct,IF(commission_pct IS NULL,‘没奖金，呵呵’,‘有奖金，嘻嘻’) 备注
FROM employees;
其他函数
version版本

SELECT VERSION();

database当前库

SELECT DATABASE();
user当前连接用户

SELECT USER();

分组函数

sum 求和
max 最大值
min 最小值
avg 平均值
count 计数

特点：

1. 以上五个分组函数都忽略Null值，处了count(*)
2. sum和avg一般用于处理数值型，而max、min、count可以处理任何类型
3. 都可以搭配distinct使用，用于统计去重后的结果
4. count的参数可以支持：字段、、常量值、一般放1 建议使用count()
   效率：
   MYISAM存储引擎下，count()的效率高
   INNODB存储引擎下，COUNT()和COUNT(1)的效率差不多，比count(字段）要高一些

分组查询

语法：
select 查询列表
from 表
【where 筛选条件】
group by 分组的字段
【order by 排序的字段】

特点：
1、和分组函数一同查询的字段必须是group by后出现的字段
2、筛选分为两类：分组前筛选和分组后筛选
针对的表 位置 连接的关键字
分组前筛选 原始表 group by 前 where
分组后筛选 group by 后的结果集 group by 后 having

案例：
①查询每个部门的员工人数>5
SELECT count(),department_id
FROM employee
GROUP BY department_id
HAVING COUNT()>5

②每个工种有奖金的员工的最高工资>6000的工种编 和最高工资，按最高工资升序
SELECT job_id ,max(salary) m
FROM employee
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING m>6000
ORDER BY m；

③查询每个工种每个部门的最低工资，并按最低工资降序
SELECT min(salary),job_id,department_id
FROM employee
GORUP BY job_id,department_id
ORDER BY min(salary) DESC；

连接查询

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行

发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

分类：

按年代分类：
sql92标准:仅仅支持内连接
sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接

按功能分类：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接
右外连接
全外连接
交叉连接

等值连接

案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT()
FROM employee e,jobs j
WHERE e.job_id = j.job_id
GROUP BY job_title
ORDER BY COUNT() DESC；

非等值连接

案例：查询员工的工资和工资等级

SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g. AND g.
AND g.=‘A’;

/*
select salary,employee_id from employees;
select * from job_grades;
CREATE TABLE job_grades
(grade_level VARCHAR(3),
lowest_sal int,
highest_sal int);

INSERT INTO job_grades
VALUES (‘A’, 1000, 2999);

INSERT INTO job_grades
VALUES (‘B’, 3000, 5999);

INSERT INTO job_grades
VALUES(‘C’, 6000, 9999);

INSERT INTO job_grades
VALUES(‘D’, 10000, 14999);

INSERT INTO job_grades
VALUES(‘E’, 15000, 24999);

INSERT INTO job_grades
VALUES(‘F’, 25000, 40000);

*/

自连接

案例：查询 员工名和上级的名称

SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.=m.;

内连接

语法：
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;

特点：
①添加排序、分组、筛选
②inner可以省略
③ 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集
案例4.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）

案例：①查询每个部门的员工个数
SELECT COUNT(*),department_name
FROM employees e
INNER JOIN departments d
ON e.=d.
GROUP BY department_name

#② 在①结果上筛选员工个数>3的记录，并排序

SELECT COUNT() 个数,department_name
FROM employees e
INNER JOIN departments d
ON e.=d.
GROUP BY department_name
HAVING COUNT()>3
ORDER BY COUNT(*) DESC;

外连接

应用场景：用于查询一个表中有，另一个表没有的记录

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

案例：查询哪个城市没有部门

SELECT city
FROM departments d
RIGHT OUTER JOIN locations l
ON d.=l.
WHERE d. IS NULL;

子连接

含义：
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询

分类：
按子查询出现的位置：
select后面：
仅仅支持标量子查询

按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）

分页查询

应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit 【offset,】size;
offset要显示条目的起始索引（起始索引从0开始）
size 要显示的条目个数
特点：
①limit语句放在查询语句的最后
②公式
要显示的页数 page，每页的条目数size
select 查询列表
from 表
limit (page-1)*size,size；

size=10
page
1 0
2 10
3 20
案例：有奖金的员工信息，并且工资较高的前10名显示出来
SELECT
*
FROM
employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10 ;

文章知识点与官方知识档案匹配，可进一步学习相关知识MySQL入门技能树首页概览31559 人正在系统学习中