MySQL笔记（一）

MySQL(Mosh视频)

提示：这里可以添加系列文章的所有文章的目录，目录需要自己手动添加
例如：第一章 Python 机器学习入门之pandas的使用

---

---

前言

安装可参考Mosh的ＭySQL教程，里面有Mac和Window的详细安装步骤。
Mosh视频前三小时内容。

1. MySQL 基础内容

(1) 数据库框架

（2）语法
– 注释
MySQL不分大小写；
一个选择语句结束使用分号；隔开。
字符串用’'或“”引起来
子句可以用空格隔开放在同一行，为了美观，一个子句一行；
列名或表名太多或太长时，可以换行；
涉及不同数据库时，当前数据库通过use语句设置；

use sql_current; -- 当前数据库为sql_current，注意冒号

2. Select 选择语句

（1）用* 表示返回所有列
（2） 可显式指定返回的列名
（3）对返回列进行数学运算
（4） 可对返回列名设置别名，as

SELECT name, unit price, unit price *1.1 AS new price
FROM products

(5) distinct 可返回唯一值，避免重复值

SELECT DISTINCT name

3. Where 条件过滤

（1）条件： <=,>=,<,>,=,!=,<>
（2）组合多个条件，AND，OR，NOT

Select *
From customers
Where birthdate>'1990-01-01' OR 
      (NOT (points>1000 AND state='VA'))

（3） 运算符IN

Where state in ('va','ga','fl')
Where state not  in ('va','ga','fl')

（4）运算符between

Where birth_date between '1990-01-01' and '2000-01-01'

（5）运算符 like
% 任意数量字符
_ 一个任意字符

Where last_name like '%b__a'
Where (address like '%TRAIL%' OR address like '%AVENUE%') AND phone number not like '%9'

（6）regexp （regular expectation）结合正则表达式
^ 开头 $ 结尾 | 或 [] 任意单个字符

Where last_name regexp '^field$'
Where last_name regexp 'ey$|on%'
-- 包含g,l,e中的一个字符
Where last_name regexp '[gle]m'
-- 包含a-h中任意字符
Where last_name regexp '[a-h]m'

(7) is null 寻找缺失记录

Where shipped-id is null
Where shipped_id is not null

4. 排序 order by

order by 默认按主键列排序

select *
from customers
-- 降序排列
order by first_name desc 
order by a*b  
--对select选择的列排序
select a,b
from customers
order by 1,2 -- 对a,b列进行排序

5. limit 限制返回的查询结果

select *
from customers
limit 3   -- 返回前三行
limit 6,3  --第一个6为偏移量，跳过前6行，取7，8，9，行

6. Inner join 内连接

-- select中必须要指定返回的是订单表还是客户表中的customer_id，否则会报错
select order_id,orders.customer_id,first_name
from orders
join customers
	on orders.customer_id=customers.costomer_id
	-- 连接条件，Join或 Inner Join，Inner可以省略

表格可以设置 别名，设置后，就必须使用别名不能使用原来的名字

select order_id,o.customer_id,first_name
from orders o
join customers c
	on o.customer_id=c.customer_id

7. Join across 合并不同列表

对不属于当前数据库的表添加数据库名前缀

use sql_a;  -- 当前列表
select order_id,o.customer_id,first_name
from orders o
join sql_b.customers c  -- 要连接的另外一个列表
	on o.customer_id=c.customer_id

8. self joins 自身连接

自身连接相当于连接两个表

use sql_a;
select *
from employees e
join employees m 
	on e.reports_to = m.employee_id
	-- 来自同一个表

9. 连接多个不同的表

订单表，用户表，订单状态表，连接三个表，返回根据订单id的用户id，订单状态

select o.order_id,
		c.first_name,
		os.name as order_status -- name为订单状态表里订单id对应的订单状态
from order o
join customers c
	on o.customer_id=c.customer_id
join  order_status os
	on o.status_id=os.order_status_id

10. Compound Join

复合主键

使用两个连接条件

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

11. Implicit join 隐式联合

尽量不使用，使用显式的join on语句

select *
from orders o, customers c
where o.customer_id=c.customer_id

12. Outer join 外连接

left/right outer join , outer关键字是可选的，可以省略

连接方式	返回记录
内连接	只返回符合连接条件的内容
外连接	（1）left join 无论条件是否满足，返回来自左表格的所有记录
	(2)right join 无论条件是否满足，返回右表的所有记录
内连接结果
对于客户的每一个id，如果订单中有该客户，则返回；如果订单中没有该客户，则不返回。

左连接结果
所有来自左表的记录都会返回。
返回连接条件是否成立，如果成立，返回数值，不成立，返回空

右连接结果
无论条件是否满足，返回右表的所有记录

13. Outer join between multiple tables 不同表间的外部连接

实际使用时，尽量避免右连接。

select 
		c.customer_id,
		c.first_name,
		o.order_id,
		sh.name as shipper
from customers c
-- 左连接订单id
left join orders o
		on c.customer_id = o.customer_id
-- 左连接托运id返回托运名称
left join shippers sh
		on o.shipper_id = sh.shipper_id
order by c.customer_id

14. Self outer joins 自身的外连接

无论是否有经理，返回所有员工对应的经理

use sql_a;
select *
from employees e
left join employees m 
	on e.reports_to = m.employee_id

15. Using 关键句

如果两个表有相同的列名，条件为两列相等时，可以使用using

select 
		c.first_name,
		o.order_id,
		sh.name as shipper
from orders o
left join customers c
		--on c.customer_id = o.customer_id
		using (customer_id)

复合主键时，多个条件

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

16. Natural join 自然连接

不建议使用，具有不确定性。

select o.order_id,c.first_name
from order o
natural join customers c

17. Cross joins 交叉连接

连接表1和表2所有的记录 ，不需要连接条件
交叉联结，也被称为笛卡尔积。交叉联结结果的行数，是所联结的表的行数的乘积。

select sh.name as shipper,p.name as product
from shipper sh
cross join products p
-- 隐式语法
from shipper sh,product p

18. Union

join是连接不同表的列，Union是连接不同表的行（即上下合并表格）,前提是合并内容的列数应相同。
合并一个表的两个查询结果或者不同表的查询结果，默认返回记录的列名参照第一个select（第一个表格）。

select 
		order_id,order_date,'Active' as status -- 增加了一个新列 active
from orders
where order_data>'2019-01-01';
Union
select 
		order_id,order_date,'Archived' as status
from orders
where order_data<'2019-01-01';

Union会删除结果中重复的数据，如果要保留所有的结果，使用Union all

19、Column attributes 插入、更新和删除数据

表格的属性

（1）Insert into 插入

插入单行

Insert into  customers
values (default, -- id设置了自动递增，因此可以选择默认，会自动填补递增的数字
		'John','Smith','1990-01-01',
		null,'address','city','CA',default)

可以在表名后说明必要的列名，其它可以缺省的l列可以省略

insert into customers (
		first_name,
		last_name,
		birth_date,
		address,
		city,
		state)

插入多行

insert into shippers (name)
values ('shipper1'),
		('shipper2'),
		('shipper3');

插入多个表中

insert into orders (customer_id,order_id,status)
values (1,'2019-01-02',1);

insert into order_items
-- 通过LAST_INSERT_ID()函数获取最新插入的order id
values (LAST_INSERT_ID(),1,1,2.9),
		(LAST_INSERT_ID(),2,1,3.9);

（2）Creating a copy of a table 创建表的副本

-- 这样创建的表没有主键
create table orders_a as
select * from orders

-- 复制一部分到另外一个表,使用select作为子查询，放在insert into中
insert into orders_a
select *
from orders
where order_date <'2019-01-01'

（3）updating 更新表格

更新满足过滤条件的一行数据

update invoices
set payment_total =10,payment_date='2019-01-03'
where client_id = 5 --  符合该条件的仅有一条数据
-- 没有Where过滤条件时，将更新所有数据

更新多行

--如果采用上述方法更新多行，会报错，MySQL不允许这样更新数据
where client_id = 3 --满足该条件的有多条数据

在偏好设置中，选择SQL Editor，在最下面的other选项中，取消勾选 Safe updates，即可同时更新多行。

在更新时使用子查询

update invoices
set 
	payment_total = invoice_total*0.5,
	payment_date = due_date
-- 只返回一个id记录
where client_id = 
					(select client_id
					from clients
					where name = 'Myworks') 
--返回多个id
where client_id in 
					(select client_id
					from clients
					where state in ('CA','NY')) 				

（4）delete 删去行

delete from…where…

delete from invoices
where invoice_id = 1

子查询

delete from invoices
where client_id in 
					(select client_id
					from clients
					where state in ('CA','NY'))

（5）还原数据库

重新运行创建数据库的脚本文件