MYSQL必知必会

MYSQL必知必会

进阶

汇总数据

聚集函数

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()、MIN()	返回某列的最大值和最小值
SUM()	返回某列值之和

count(*)对表中行的数目进行计数，不管表列中包含的是空值(NULL)还是非空值

count(column)对特定列中具有值的行进行计数，忽略NULL值

select avg(distinct price) as avg_price from products;

创建分组

除聚集计算语句外，select语句中的每个列都必须在group语句中给出

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

过滤分组，因为where过滤的是指定的行而不是分组，所以需要使用having子句，having支持所有where操作符

where是在数据分组前过滤，having是在数据分组后进行过滤

select cust_id, count(*) as num from orders group by cust_id having count(*) > 2;
select vend_id, count(*) as num from products where price > 10 group by vend_id, having count(*) > 2 order by name;

子查询

# 显示costomer表中每个客户的订单数，订单和相应的客户ID存储在orders表中
select cust_name, cust_state, (select count(*) from orders where orders.cust_id = customers.cust_id) as num from customers order by cust_name;

联结表

笛卡尔积

由没有联结条件的表关系返回的结果为笛卡尔积，检索出的行的数目将是第一个表中的行数乘以第二个表中的行数

select vend_name, prod_name, prod_price from vendors, products order by vend_name, prod_name;

等值联结

select vend_name, prod_name, prod_price from vendors inner join products on vendors.vend_id = products.vend_id;

外部联结

对每个客户下了多少订单进行计数，包括那些至今尚未下订单的客户

列出所有产品及订购数量，包括没有人订购的产品

select customer.cust_id, orders.order_num from customers left outer join orders on customers.cust_id = orders.cust_id;

组合查询

union必须由两条或两条以上的select语句组成

union中的每个查询必须包含相同的列、表达式或聚集函数

列数据类型必须兼容：类型不必完全相同，但是可以隐含转换的类型

select vend_id, prod_id, prod_price from products where prod_price < 5
union
select vend_id, prod_id, prod_price from products where vend_id in (1001, 1002);

union默认自动去除重复的行，如果需要返回所有匹配的行，可以使用union all

select vend_id, prod_id, prod_price from products where prod_price < 5
union all
select vend_id, prod_id, prod_price from products where vend_id in (1001, 1002);

对组合查询结果排序，只能使用一条order by语句，必须出现在最后一条select语句之后，对于结果集，不存在用一种方式排序一部分，用另一个种方式排序另一部分的情况

select vend_id, prod_id, prod_price from products where prod_price < 5
union
select vend_id, prod_id, prod_price from products where vend_id in (1001, 1002) order by vend_id, prod_price;

插入数据

insert into customers values(NULL, 'test', 900, 'AA');
# 明确给出列名，避免字段顺序问题
insert into customers(cust_name, cust_address, cust_city) values('Pep', 'Main', 'CC');

插入多条数据

insert into customers(cust_name, cust_address, cust_city) values('Pep', 'Main', 'CC'),('Martain', 'Galaxy', 'New');

从select中检索数据后，再进行插入

insert into customer2(id, name, age) select id, name, age from customer;

更新和删除数据

update customers set cust_name = 'Fudds' cust_email = 'elmer@.com' where cust_id = 1005;
# 删除某列的值
update customers set cust_email = NULL where cust_id = 1005;
# 删除一行数据
delete from customers where cust_id = 1005;
# 更快的删除(实际是删除原来的表并重新创建一个表，不需要逐行删除表中的数据)
truncate table 

创建和操纵表

CREATE TABLE vendors (
	vend_id INT NOT NULL AUTO_INCREMENT,
	vend_name CHAR ( 50 ) NOT NULL,
	vend_address CHAR ( 50 ) NULL,
	vend_city CHAR ( 50 ) NULL,
	vend_zip CHAR ( 50 ) NULL,
PRIMARY KEY ( vend_id ) 
) ENGINE = innodb;
# 给表增加列
alter table vendors add vend_phone char(20);
# 删除表中列
alter table vendors drop column vend_phone;
# 删除表
drop table customers2;
# 重命名表
rename table customer2 to customers;