Mosh--mysql 学习笔记（1）

数据库管理工具采用 Navicat

解决方法

选择语句 SELECT / FROM / WHERE / ORDER BY

select *  -- 返回所有列
from customers -- customers 表中
-- where customer_id = 1
-- ORDER BY first_name

选择子句 – AS / DISTINCT

SELECT
	first_name,
	last_name,
	points,
	( points + 10 )* 100 AS result_data --( points + 10 )* 100 AS "result data"
FROM customers

关键字 AS ： 起别名

select distinct state from customers
-- 选择一列时，可用 关键字 DISTINCT 剔除重复项

练习1

SELECT NAME, unit_price,( unit_price * 1.1 ) AS "new price" 
FROM
	products

运算符

< 、 <= 、 > 、 >=、 = 、 != (<>)

SELECT	* 
FROM customers 
WHERE birth_date > '1990-01-01' -- 日期标准表示方法
ORDER BY	birth_date

AND、OR 、NOT

 AND 的优先级大于 OR

NOT 作用：

WHERE birth_date <= '1990-01-01' and points <= 1000 
等效于
WHERE not (birth_date > '1990-01-01' or points > 1000)

练习

SELECT	* 
FROM	order_items 
WHERE	order_id = 6 	AND ( quantity * unit_price )> 30

IN

select * from customers
-- where state in('GA','CA','VA') 等效于
where state = 'GA'  OR state = 'CA' or state = 'VA'

select * from products
where quantity_in_stock in (49,38,72)

BETWEEN

select * from customers
-- where points >= 1000 and points <= 3000  等效于 
where points between 1000 and 3000

select * from customers
where birth_date between '1990-01-01' and '2000-01-01'
order by birth_date

LIKE （% 表示任意字符数 ，下划线 _ 表示一个单字符）搜索字符不区分大小写

select * from customers
where address like '%trail%' or address like '%avenue%' or phone like '%9' 

REGEXP （正则表达式 regular repression） 搜索字符串

^ 表示头部  ^fir 字符串以 fir 开头
$ 表示尾部  fir$ 字符串以 fir 结尾
| 表示多个搜寻模式  fir|mac 字符串包含 fir 或 mac 
[] 搜索括号内列举的单字符 [gim]e 表示搜索 ge，ie，me ； [a-h]e表示搜索 ae，be，ce，...，he

select * from customers
-- where first_name = 'elka' or first_name =  'ambur'
-- where first_name regexp 'elka|ambur'
-- where last_name regexp 'ey$|on$'
-- where last_name regexp 'se|^my' 
-- where last_name regexp 'br|bu'
-- where last_name regexp 'b[ru]'

NULL 获取缺失属性

select * from customers
-- where phone is not null
where phone is null

select * from orders
where shipper_id is null and shipped_date is null 

ORDER BY 子句

默认为升序，标签后跟 DESC 调整为降序

MySQL中可以用任何列排序数据，即使呢列没在 SELECT 子句中

select *,quantity* unit_price as 'total_price' from order_items
where order_id =2
-- order by quantity*unit_price desc
order by total_price desc 

LIMIT 子句

limit n -- 取前n行
limit n,m -- n 为偏移量，m为偏移后取的行数

select * from customers
order by points desc
limit 3 -- 取第1,2,3行
limit 2,2 -- 取第3,4行

LIMIT 子句永远都要放在最后

总结：子句顺序

select 
from
where 
order by
limit 

内连接 Inner Joins

连接多表列

select order_id,first_name,last_name,c.customer_id from orders o -- 设置表别名 o
inner join customers c -- inner 关键字可不写 -- 设置表别名 c
	on o.customer_id = c.customer_id -- 添加链接条件 顾客id相同

跨数据库链接

select * from order_items as oi
join sql_inventory.products as p
    on oi.product_id = p.product_id

自连接

select e.employee_id,e.first_name,m.first_name as manager 
from employees e
join employees m
		on e.reports_to = m.employee_id

连接多表

select o.order_id,o.order_date,c.first_name,c.last_name,os.`name` as 'status' from orders o
join customers c
	on o.customer_id = c.customer_id
join order_statuses os
  on o.`status` = os.order_status_id
order by o.order_id

select p.date,p.invoice_id,p.amount,c.`name`,pm.`name` as 'pay method' from payments p
join clients c
  on p.client_id = c.client_id
join payment_methods pm
  on p.payment_method = pm.payment_method_id

复合连接条件 （两列确定一条信息）

select * from order_items oi
join order_item_notes oin
  on oi.order_id = oin.order_Id and oi.product_id = oin.product_id

隐式连接语法 – 存在交叉连接隐患

-- 显式
-- select * from orders o
-- join customers c
-- 		on o.customer_id = c.customer_id
-- order by order_id

-- 隐式
select * from orders o,customers c
where o.customer_id = c.customer_id -- 若取消 where 会造成交叉连接
order by order_id

外连接 Outer Joins – left join; right join

/* 
select c.customer_id,c.first_name,o.order_id from customers c
-- left join orders o  -- 左连接-左表为主 customers 为左表，orders 为右表
right join orders o		 -- 右连接-右表为主 
  on c.customer_id = o.customer_id
order by c.customer_id  */
/*
select c.customer_id,c.first_name,o.order_id from orders o
right join customers c  
	on c.customer_id = o.customer_id
order by c.customer_id */

select p.product_id,p.`name`,oi.quantity from products p
left join order_items oi
  on p.product_id = oi.product_id
order by p.product_id

多表外连接

select c.customer_id,c.first_name,o.order_id,sh.`name`as shipper from customers c
left join orders o
  on c.customer_id = o.customer_id
left join shippers sh
  on o.shipper_id = sh.shipper_id   -- 不使用外连接，只返回符合等号条件的数据
order by c.customer_id

select o.order_date,o.order_id,c.first_name,sh.`name` as shipper,os.`name` as `status` from orders o
join customers c -- 每笔订单都有顾客，可用内连接
  on c.customer_id = o.customer_id
left join order_statuses os
  on os.order_status_id = o.`status`
left join shippers sh
  on sh.shipper_id = o.shipper_id
order by os.`name`

自外连接

select e.employee_id,e.first_name,m.first_name as manager from employees e
left join employees m
  on e.reports_to = m.employee_id

USING – 相同列名

select o.order_id,c.customer_id,sh.`name` as shipper from orders o
join customers c
--   on o.customer_id = c.customer_id
  using (customer_id) -- 两张表有相同名的列，可用使用 using 简化语句
left join shippers sh
  using (shipper_id)

select * from order_items oi
join order_item_notes oin
--   on oi.order_id = oin.order_id and oi.product_id = oin.product_id
	using (order_id,product_id)

select p.date,cl.`name` as client,p.amount,pm.`name` as payment_method from payments p
join clients cl
  using (client_id)
join payment_methods pm
	on p.payment_method = pm.payment_method_id

自然连接 natural Joins

交叉连接 cross Joins

-- 显式
/*
select sh.`name` as shipper,p.`name` as product from shippers sh
cross join products p
order by sh.`name` */
-- 隐式
select sh.`name` as shipper,p.`name` as product from shippers sh,products p
order by sh.`name`

联合 union

select customer_id,first_name,points,'Bronze' as type from customers
where points < 2000
union
select customer_id,first_name,points,'Silver' as type from customers
where points between 2000 and 3000
union
select customer_id,first_name,points,'Gold' as type from customers
where points > 3000
order by first_name

行操作

小问题：删除记录后自增id不连续

-- 每当删除记录后，需要新增之前，执行语句 
alter table orders auto_increment = 1;
insert into orders (customer_id,order_date,`status`)
values(2,'2019-01-01',1);
select * from orders

添加单行

-- 单行
-- insert into customers
-- values (default,'John','Smith',default,default,'address','city','ca',default)
-- or 
insert into customes (first_name,last_name,address,city,state)
values ('John','Smith','address','city','ca');
select * from customers

添加多行

-- 多行
insert into shippers(name)
values ('a'),('b'),('c');
select * from shippers

insert into products
values(default,'Jack',20,1.2),(default,'Jack',30,1.2),(default,'Jack',40,1.2);
select * from products;

分层行 ---- last_insert_id()

重点: 假如你使用单INSERT语句插入多个行， LAST_INSERT_ID() 只返回插入的第一行产生的值

alter table orders auto_increment = 1;
insert into orders(customer_id,order_date,status)
values(1,'1992-01-01',1);
insert into order_items
values(last_insert_id(),1,10,2.5),(last_insert_id(),2,20,3.5);

创建表复制 子查询

子查询是在另一段sql 语句中的选择语句

-- 创建新表
create table orders_archived as -- 订单存档 使用此方式mysql会忽略表格属性
-- insert into orders_archived -- 表存在直接插入即可
select * from orders  -- 子查询
where order_date < '2019-01-01'

create table invoices_archived as 
select i.invoice_id,i.number,c.`name` as client,
	i.invoice_total,i.payment_total,i.invoice_date,i.payment_date,
	i.due_date
from invoices as i
join clients as c
  using(client_id)
where payment_date is not NULL
order by i.invoice_id 

更新单行 ---- update；set

update invoices
set payment_total=default,payment_date=default
where invoice_id=1; -- 更新 id为1 的记录 

update invoices
set payment_total = invoice_total * 0.5, -- 结果存在问题 小数位为0 数据类型原因
	payment_date = due_date
where invoice_id = 3

更新多行

update invoices
set 
	payment_date = default
where payment_total = 0 -- 对应多行数据
-- where 子句中所有的运算符也可以在这里

update customers
set points = points +50
where birth_date < '1990-01-01'

update 使用 子查询

子查询是在另一段sql 语句中的选择语句

update invoices
set payment_total = invoice_total * 0.5,
	payment_date = due_date
where client_id = (
	-- 返回客户名为Yadel的id
	select client_id from clients 
	where name = 'Yadel'
)
-- 该子查询返回多条记录不能使用 = 了 ，用 in 替代
-- where client_id in (select client_id from clients where state in ('CA','NY'))

update orders
set comments = 'gold customer'
where customer_id in (
	select customer_id from customers
	where points > 3000
)

删除行 — delete from

alter table shippers auto_increment = 1;
insert into shippers(name)
values('a'),('b');
delete from shippers
where name in ('a','b')

数据汇总查询

聚合函数

聚合函数只运行非空值

select
	max(invoice_total) as highest,
	min(invoice_total) as lowest,
	avg(invoice_total) as average,
	sum(invoice_total) as total, -- 统计所有记录
	sum(invoice_total*1.1) as change_total,
	
	count(invoice_total) as number_of_invoices,
	-- 排除重复条目
	count(distinct client_id) as no_double_records,-- distinct 去除重复项
	count(*) as total_records,
	
	-- 除了数值，也可用于日期，字符串
	max(invoice_date) as date_max,
	-- 聚合函数仅运行非空值
	count(payment_date) as number_of_payments
	
from invoices
where invoice_date >= '2019-07-01'

select 
	'first half of 2019' as data_range,
	sum(invoice_total) as total_sales,
	sum(payment_total) as total_payment,
	sum(invoice_total - payment_total) as what_we_expect
from invoices
where invoice_date
	between '2019-01-01' and '2019-06-30'
union
select 
	'second half of 2019' as data_range,
	sum(invoice_total) as total_sales,
	sum(payment_total) as total_payment,
	sum(invoice_total - payment_total) as what_we_expect
from invoices
where invoice_date 
	between '2019-06-30' and '2019-12-31'
union
select 
	'total' as data_range,
	sum(invoice_total) as total_sales,
	sum(payment_total) as total_payment,
	sum(invoice_total - payment_total) as what_we_expect
from invoices

数据分组（单列，多列）统计

单列

select 
	client_id,
	sum(invoice_total) as total_sales
from invoices 
-- 数据分类前添加筛选器
where invoice_date >= '2019-07-01'
-- 单列分组
group by client_id -- 对数据分组 根据client_id
-- 默认状态下数据是按照group by 子句中指定的列排序的
order by total_sales desc -- desc 降序

添加筛选器后：

多列

select 
	state,city,
	sum(invoice_total) as total_sales
from invoices as i
join clients as c using(client_id)
group by state,city

select 
	date,
	pm.`name` as payment_method,
	sum(p.amount) as total_payments -- 注意点 要用sum 
from payments as p
join payment_methods as pm 
	on pm.payment_method_id = p.payment_method
group by date,payment_method
order by date

分组统计前：

分组统计后：

having

having 在分组行之后筛选数据，数据特征一定要在select子句中，而where 不需要
where 在分组前筛选数据

select 
	client_id,
	sum(invoice_total) as total_sales,
	count(*) as number_of_invoices
from invoices
-- 因为where在分组前，所有在此并不知道每个用户的销售额
-- where total_sales > 500 
group by client_id
-- 使用having 子句在分组行后筛选数据
-- 筛选数据特征一定要是select 子句中存在的
having total_sales > 500 and number_of_invoices >5

-- 获取located在VA并且花费至少100的顾客
select 
	c.customer_id, 
	c.first_name,
	c.last_name,
	sum(oi.quantity*oi.unit_price) as total_sales
from customers as c
join orders as o using (customer_id)
join order_items as oi using (order_id)
where state = 'VA'
group by c.customer_id, 
	c.first_name,
	c.last_name
having total_sales > 100

rollup 仅mysql存在

select 
	state,city,
	sum(invoice_total) as total_sales
from invoices as i
join clients as c using (client_id)
-- rollup 运算符只能应用于聚合值sum的列
-- 当你进行多列分组并运用rollup 运算符，可以看到每个组以及整个结果集的汇总值
group by state,city with rollup 

select pm.`name`,
	sum(p.amount) as total
from payments as p
join payment_methods as pm
	on p.payment_method = pm.payment_method_id
	-- 在使用rollup 运算符时不能在group by 子句中使用列别名
group by pm.`name` with rollup

使用别名会显示异常：

传送门

SQL进阶教程 | 史上最易懂SQL教程！10小时零基础成长SQL大师！！
mysql last_Insert_id()用法
mysql id从1开始自增 解决id不连续的问题