MySQL入门笔记2

一.数据查询语言（DQL）（单表）：

1. 查询的语法：

1.1 查询指定字段信息

select 字段1,字段2,...from 表名;

例如：

select id,name from zhangwu;

1.2 查询表中所有字段

select * from 表名;

例如：

select * from zhangwu; 

注意:使用"*"在练习、学习过程中可以使用，在实际开发中，不推荐使用。原因，要查询的字段信息不明确，若字段 数量很多，会导致查询速度很慢。

1.3 distinct用于去除重复记录

select distinct 字段 from 表名;

例如：

select distinct money from zhangwu;

1.4 别名查询

使用的as关键字，as可以省略的.

别名可以给表中的字段，表设置别名。 当查询语句复杂时，使用别名可以极大的简便操作。

表别名格式:

select * from 表名 as 别名;

select * from 表名 别名;

列别名格式：

select 字段名 as 别名 from 表名;

select 字段名 别名 from 表名;

例如

表别名： select * from zhangwu as zw;

列别名：

select money as m from zhangwu; 或 select money m from zhangwu;

1.5 计算：

我们在sql语句的操作中，可以直接对列进行运算。

例如：将所有账务的金额+10000元进行显示.

select pname,price+10000 from product;

2. 条件查询

2.1 语法：

where语句表条件过滤。满足条件操作，不满足不操作，多用于数据的查询与修改。

格式 :

select 字段 from 表名 where 条件;

where条件的种类如下：

比较运算符：

运算符	说明
> < = = <>	大于、小于、大于(小于)等于、不等于
BETWEEN ...AND...	显示在某一区间的值(含头含尾)
IN(set)	显示在in列表中的值，例：in(100,200)
LIKE 通配符	模糊查询，Like语句中有两个通配符： % 用来匹配多个字符；例first_name like ‘a%’; _ 用来匹 配一个字符。例first_name like ‘a_’;
IS NULL	判断是否为空 is null; 判断为空 is not null; 判断不为空

 逻辑运算符：

运算符	说明
and	多个条件同时成立 (当所有条件中有一个不成立，则不成立)
or	多个条件任一成立 (当所有条件中有一个成立，则成立)
not	不成立，例：where not(salary>100);

3 分页查询（limit）

3.1 分页语法：

-- n:从哪一条数据开始显示（第一条为0） m:数据条数

select 字段 from 表名 limit n,m;

4. 排序

4.1 排序查询

使用格式

-- 通过order by语句，可以将查询出的结果进行排序。放置在select语句的最后。

SELECT FROM 表名 ORDER BY 字段;

# ASC 升序 (默认)

# DESC 降序

5 聚合函数

5.1 聚合函数

之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对 一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。

count：统计指定列不为NULL的记录行数；

sum：计算指定列的数值和；

max：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串类型不是数值类型，那么计算结果为0 排0序运算；

min：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

avg：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

6 分组 group by

6.1 使用格式：

分组查询是指使用group by字句对查询信息进行分组,例如：我们要统计出zhangwu表中所有分类账务的总数量,这时 就需要使用group by 来对zhangwu表中的账务信息根据parent进行分组操作。

SELECT 字段1,字段2… FROM 表名 GROUP BY 字段 [HAVING 条件];

分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条件。

6.2 having与where的区别

having是在分组后对数据进行过滤.

where是在分组前对数据进行过滤

having后面可以使用分组函数(统计函数)

where后面不可以使用分组函数。

7 顺序问题：

7.1 书写顺序：

select 字段 from 表 where + group by [having ]+order by + limit

7.2 执行步骤：

SELECT s_classid,COUNT(*) -- 4 投影

FROM t_student -- 1 把表加载到内存

WHERE age=10 -- 2 筛选

GROUP BY s_classid -- 3 分组

HAVING(COUNT(*)>=1) -- 5 二次筛选 首先要得到第一次的结果 才可以进行筛选

ORDER BY COUNT(*) DESC -- 6 二次分组

LIMIT 1 -- 7 限定行数 肯定是你执行完之后最终的结果的 行数

-- 这7步顺序千万不能搞错

二.多表：

1.多表关系理解：

1.1 一对多关系：

1.常见实例：

客户和订单，分类和产品，部门和员工。

2.一对多关系建表原则：

从表（多方）的创建一个字段，字段作为外键指向主表（一方）的主键。

1.2 多对多关系：

1.常见实例：

学生和课程，商品和订单，人和角色。

2.多对多关系建表原则：

需要创建第三张表，中间表至少含有两个字段，这两个字段分别作为外键指向两张表的主键（本质而言，就是将一个 多对多关系拆分为两个一对多关系）。

两张表都是主表，第三张表为从表。

1.3 一对一关系（了解）：

1.实例：

在实际开发过程中使用并不多，因为一对一关系可以直接创建为一张表，分为多表不利于使用维护。

2.一对一的两种建表原则：

（1）外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一（unique）.

（2）外键是主键：主表的主键和从表的主键直接形成主外键关系。

2.创建表实现：

2.1 一对多：分类与商品

1.建表语句：注意：从表中的外键类型、长度需要与对应主表中的主键一致。

2.视图模型：

3.总结：

从表中不能添加\更新主表中不存在的数据；

主表中不能删除\更新从表中已使用的数据（为额外设置外键级联删除级联修改的情况下）。

2.2 多对多：订单与商品

1.建表语句：

2.视图模型：

3.多表查询语句：

3.0 数据准备：

USE testb;

# 商品分类表

INSERT INTO type (t_id, t_name)

VALUES ('t01', '家电'), ('t02', '服饰'), ('t03', '化妆品');

# 商品表

INSERT INTO product (p_id, p_name, p_price, pt_id)

VALUES

('p001', '联想', 5000, 't01'),

('p002', '雷神', 6000, 't01'),

('p003', '惠普', 5500, 't01'),

('p004', '李宁', 300, 't02'),

('p005', '乔丹', 280, 't02'),

('p006', '361度', 230, 't02'),

('p007', '香奈儿', 300, 't03'),

('p008', 'YSL', 300, 't03'),

('p009', '迪奥', 600, 't03');

3.1 笛卡尔积(交叉连接)：

在MySQL中可以为CROSS JOIN或者省略CROSS即JOIN，或者使用',' 如：

SELECT * FROM table1 CROSS JOIN table2

SELECT * FROM table1 JOIN table2

SELECT * FROM table1,table2 （隐式内连接）

由于其返回的结果为被连接的两个数据表的乘积，因此当有WHERE, ON或USING条件的时候一般不建议使用，因为 当数据表项目太多的时候，会非常慢。一般使用LEFT [OUTER] JOIN或者RIGHT [OUTER] JOIN

-- 笛卡尔积

select * from product cross JOIN type;

select * from product JOIN type;

select * from product ,type WHERE pt_id=t_id;# 可以通过添加条件过滤掉无用数据

3.2 内连接：

内连接INNER JOIN 在MySQL中把INNER JOIN叫做等值连接，即需要指定等值连接条件在MySQL中CROSS和INNER JOIN被划分在一起。

select 字段 from table1 inner join table2 on 条件；

-- 内连接

select * FROM product INNER JOIN type ON pt_id=t_id;

/* 注意：

（1）别名：在为表取别名之后，表的原名在当前语句中失效，只能使用别名

（2）由于涉及多表，需要考虑字段同名的情况，所以建议在使用字段时指定是哪一张表中的字段

*/

select p.p_id,p.p_name,p.pt_id, t.t_name FROM product as p INNER JOIN type t ON pt_id=t_id;

3.3 外连接：

MySQL中的外连接，分为左外连接和右外连接，即除了返回符合连接条件的结果之外，还要返回左表(左连接)或者右 表(右连接)中不符合连接条件的结果，如另一表中无相对应的使用NULL对应。

-- 左外连接

select 字段 from table1 left [outer] join table2 on 条件；

-- 右外连接

select 字段 from table1 right [outer] join table2 on 条件；

-- 外连接

-- 左外连接

select * FROM product left OUTER JOIN type ON product.pt_id=type.t_id;

-- 右外连接

select * FROM product RIGHT OUTER JOIN type ON product.pt_id=type.t_id;

3.4 子查询：

一个select语句中包含另一个完整的select语句（被查询表，条件）。

子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子 查询语句了。

-- 1.查询类型为t01,并且价格>5000

-- 查询类型为t01

SELECT * FROM product WHERE pt_id='t01';

-- 在已获取的数据中，价格>5000

SELECT * FROM (SELECT * FROM product WHERE pt_id='t01') aa WHERE p_price>5000;

-- 2.查询类型为家电,并且价格>5000

-- 查询类型为家电的类型信息type

SELECT t_id FROM type WHERE t_name='家电';

-- 在商品表中，获取所有类型为家电的信息

SELECT * FROM product p WHERE p.pt_id = (SELECT t_id FROM type WHERE t_name='家电');

-- 价格>5000

SELECT * FROM (SELECT * FROM product p WHERE p.pt_id = (SELECT t_id FROM type WHERE t_name='家电')) bb

WHERE bb.p_price>5000;

3.5 联合查询(了解)：

合并结果集就是把两个select语句的查询结果合并到一起

UNION：用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行。

UNION ALL：不去除重复记录，其它的和union一样

注意：UNION 内部的 SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型。

-- 联合查询 --

UNION 会自动去重

SELECT * from product WHERE pt_id='t01';

SELECT * from product WHERE pt_id in ('t02','t01');

SELECT * from product WHERE pt_id='t01' UNION SELECT * from product WHERE pt_id in ('t02','t01');

-- UNION ALL 将所有数据都获取，不会去重

SELECT * from product WHERE pt_id='t01' UNION ALL SELECT * from product WHERE pt_id in ('t02','t01');

-- 注意

SELECT p_id,p_name from product WHERE pt_id='t01';

SELECT * from product WHERE pt_id in ('t02','t01');

SELECT p_id,p_name from product WHERE pt_id='t01' UNION SELECT * from product WHERE pt_id in ('t02','t01'); SELECT p_price,p_name from product WHERE pt_id='t01' UNION SELECT p_name,p_price from product WHERE pt_id in ('t02','t01');

三.查询语句补充：

1.表连接的约束条件：

where，on，using：

-- WHERE

SELECT * FROM product_copy p,type t WHERE p.t_id=t.t_id;

-- ON

SELECT * FROM product_copy p LEFT JOIN type t ON p.t_id=t.t_id;

-- USING（字段）:指定字段在两张表中名称一致

SELECT * FROM product_copy p LEFT JOIN type t USING(t_id);

2.some，any，all

any，in，some，all分别是子查询关键词之一，可以与=、>、>=、结合起来使用，any表示等于、大于、 大于等于、小于、小于等于、不等于其中的任何一个数据，all表示全部数据。

any,all关键字必须与一个比较操作符一起使用。any关键词可以理解为“对于子查询返回的列中的任一数值，如果比较 结果为true，则返回true”。

3.多表左联

SELECT * FROM table LEFT JOIN table1 ON 条件1 LEFT JOIN table2 ON 条件2……