MYSQL 学习笔记2

5、数据汇总

聚集函数：运行在组上，计算和返回单个值的函数

avg count max min sum

select avg(prod_price) as avg_price from products;

好处：不再显示所有的数据，节省传输带宽

max min也可用于文本，表示最后（前）行

 

select avg(distinct prod_price) as avg_price from products where vend_id = 1003;

使用disctinct，只计算其中不同值的平均值

组合使用：

select count(*) as num_items,

          min(prod_price) as price_min,

          max(prod_price) as price_max,

from products;

 

6、数据分组

select vend_id,count(*) as num_prods from products group by vend_id;

以vend_id，分组求和

group by 位置在where之后 order by之前

group by并没有排序特性

 

select vend_id,count(*) as num_prods

from products

where prod_price >= 10

group by vend_id

having count(*) >= 2;

统计price 大于等于10的,并按id计数，最终显示计数大于等于2的

where用于前过滤，having用于后过滤

 

7、子查询

定义：查询嵌套

select cust_id

from orders

where order_num in (select order_num from orderitems where prod_id = 'TNT2');

查询产品ID为tnt2的订单编号，并检索对应的客户ID信息并返回

子查询从内向外进行

使用子查询代码时，良好的换行习惯有助于理解代码

 

8、联结

外键：它包含另一个表的主键值，从而定义了两个表之间的关系

联结：一种能够在一条select语句中关联表的机制

 

select vend_name,prod_name,prod_price

from vendors,products

where vendors.vend_id = products.vend_id

order by vend_name,prod_name;

products.vend_id：完全限定名

 

select vend_name,prod_name,prod_price

from vendors inner join products 

on vendors.vend_id = products.vend_id;

与上代码等价。INNER JOIN为联结时的首选语法

 

表的别名：

SELECT cust_name,cust_contact
from customers as c,orders as o,orderitems as oi
where c.cust_id = o.cust_id
  and oi.order_num = o.order_num
  and prod_id = 'TNT2';

在引用时，给表起了个短别名，用于缩短语句，方便多次使用

 

自连接：解决表同名的歧义问题

select p1.prod_id,p1.prod_name
from products as p1,products as p2
where p1.vend_id = p2.vend_id
and p2.prod_id ='DTNTR';

这与下面的子查询等价，但一般联结的效率更高：

select prod_id,prod_name
from products
where vend_id = (select vend_id from products
                 where prod_id = 'DTNTR');

 

外联结：与内联结对应，表示联结中包含了相关表中没有关联的行

应用：查找顾客的订单数，其中也要查找未下订单的用以计算平均

使用内联结：

select c.cust_id ,o.order_num
from customers as c inner join orders as o
on c.cust_id = o.cust_id;

将无法找到未下单的客户

使用外联结：

select c.cust_id ,o.order_num
from customers as c left outer join orders as o
on c.cust_id = o.cust_id;

 

9、组合

定义：将不同条件下的数据组合，类似于where....or的用法

select……

uninon

select……

注意：每个select必须包含相同的列

默认删除重复行，如不需，则用union all

需要排序，可以且只能在最后加order by

 

10、表的创建、插入、删除

创建表示例：（以牛客例题为例）

CREATE TABLE actor
(
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,

)

创建表，从现有表中选择数据：

create table actor_name (as)
select first_name,last_name from actor;

 

插入数据示例：

INSERT INTO actor
VALUES

(1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'),
(2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33')

插入数据，若已存在，则忽略：

insert IGNORE into actor
values(3,'ED','CHASE','2006-02-15 12:34:33');

插入一个新列：

alter table actor

add `create_date` datetime not null default '0000-00-00 00:00:00'

删除数据：

delete from 表名 where

条件的使用方法与查找一致

 

更新数据：

update 表名  set 列名 = ***, 列名 = ***

where ***

 

重命名表：

alter table titles_test rename titles_2017