代码拉取完成，页面将自动刷新
数据库分为：
Windows下，完成MySQL安装后我们可以看到MySQL Workbench开发工具，启动并进入我们可以看到下面界面：
我们首先要学习结构化查询语言最基本的四个子句，内容包括：
之后我们还要学习：
这一节讲述如何从单表查询数据，从MySQL Workbench来探查数据库，每个数据库都是一个容器，其中有很多数据对象：
--设置缺省数据库
use sql_store;
--查询语句形式一
select * from customers;
--查询语句形式二
select
*
from
customers
where
customer_id = 1
order by
first_name;
--select 常数
select 1, 2
--查询指定表重的所有字段
select
*
from
customers;
--查询指定表重的指定字段
--查询的数学表达式支持及命名
select
first_name,
last_name,
points,
points + 10 as discount
from
customers
-- distinct
select
distinct state
from
customers;
练习：
--返回products的所有行，其中字段列表包括：
-- name
-- unit_price
-- new_price => (unit_price * 1.1)
select
*
from
customers
where
points > 3000;
where子句支持以下比较操作符号：
，大于
=，大于等于
再参看下面例子：
select
*
from
customers
where
state = 'va';
select
*
from
customers
where
birthdate > '1990-01-01';
练习：
--从orders表中返回2019年的数据
看下面例子：
select
*
from
customers
where
birth_date > '1990-01-01' and points > 1000;
select
*
from
customers
where
birth_date > '1990-01-01' or points > 1000;
select
*
from
customers
where
birth_date > '1990-01-01' or
(points > 1000 and state = 'va');
select
*
from
customers
where not
(birth_date <=> '1990-01-01' and points <=> 1000);
练习：
select
*
from
customer
where state in ('va', 'fl', 'ga');
select
*
from
customer
where state not in ('va', 'fl', 'ga');
练习：
select
*
from
customers
where
pointss between 1000 and 3000;
练习：
查询中的通配符包括：
select
*
from
customers
where
last_name like 'b%';
select
*
from
customers
where
last_name like 'brush%';
select
*
from
customers
where
last_name like '%b%';
select
*
from
customers
where
last_name like '_____y';
select
*
from
customers
where
last_name like 'b____y';
练习：
--查询结果包含'field'
select
*
from
customers
where
last_name regexp 'field';
--查询结果开始于'field'
select
*
from
customers
where
last_name regexp '^field';
--查询结果以'field'结尾
select
*
from
customers
where
last_name regexp 'field$';
--查询结果包含'field'或'mac'...
select
*
from
customers
where
last_name regexp 'field$|mac|^rose';
--查询结果包含‘ge’,'ie', 'me'
select
*
from
customers
where
last_name regexp '[gim]e';
select
*
from
customers
where
last_name regexp '[a-h]e'
在正则表达式中：
^表示开始$表示结束|表示逻辑或[abcd]表示包含列表中的各字母[a-f]表示包含按字母序a到f的各字母练习：
customers表中查询
first_name包含elka或ambur
last_name以ey或on结尾last_name以my开始或包含se
last_name包含b之后跟随字母r或u
本节演示如何查询字段值是null的情况，看下面例子：
--查询电话号码为空值的记录
select
*
from
customers
where
phone is null;
--查询电话号码不为空值的记录
select
*
from
customers
where
phone is not null;
练习：
orders表中获取那些没有被递送的记录本节演示order by排序子句，请看下面例子：
--first_name 正序排
select
*
from
customers
order by first_name;
--first_name正序排
select
*
from
customers
order by first_name asc;
--first_name倒序排
select
*
from
customers
order by first_name desc;
select
*
from
customers
order by state desc, first_name desc;
--1 => firstname, 2 => last_name
select
first_name,
last_name
from
customers
order by 1, 2
练习：
查看下面的例子：
--查询头部的3条数据
select
*
from
customers
limit 3;
--查询结果按3条分一页进行分页，并显示第六页的数据
select
*
from
customers
limit 6, 3
练习：
参看下面例子：
use sql_store;
select
o.order_id,
o.customer_id,
c.first_name,
c.last_name
from
orders o
left outer join customers c on o.customer_id = c.customer_id
order by
o.order_id;
设置了缺省库时跨库的Join连接可用下面方式：
use sql_store;
select
*
from
order_items oi
left outer join sql_inventory.products p on oi.product_id = p.product_id;
或者使用下面的查询语句则不需要指定缺省的数据库设备：
select
*
from
sql_store.order_items oi
left outer join sql_inventory.products p on oi.product_id = p.product_id;
查看employees表结构，我们发现reports_to字段指向表中的employee_id，那位3720雇员就是组织机构的管理员：
use sql_hr;
select
e.employee_id,
e.first_name,
m.first_name as manager
from
employees e
left outer join employees m on e.reports_to = m.employee_id
看下面的例子：
use sql_store;
select
o.order_id,
o.order_date,
c.first_name,
c.last_name,
os.name as status
from
orders o
left outer join customers c using(customer_id)
left outer join order_statuses os on o.status = os.order_status_id
再看另一例子：
use sql_invoicing;
select
p.date,
p.invoice_id,
p.amount,
c.name as client,
pm.name as payment_method
from
payments p
left outer join clients c using(client_id)
left outer join payment_methods pm on p.payment_method = pm.payment_method_id
use sql_store;
select
*
from
order_items oi
join order_item_notes oin
on oi.order_Id = oin.order_Id and oi.product_id = oin.product_id;
use sql_store;
select
*
from
orders o, customers c
where
o.customer_id = c.customer_id
缺省情况，所有的join都是inner join，我没看下面的例子:
Inner Join:
use sql_store;
select
c.customer_id,
c.first_name,
o.order_id
from
customers c
join
orders o using(customer_id)
order by
c.customer_id;
Outer Join:
use sql_store;
select
c.customer_id,
c.first_name,
o.order_id
from
customers c
left outer join
orders o using(customer_id)
order by
c.customer_id
下面的查询显示inner join的查结果：
select
c.customer_id,
c.first_name,
o.order_id
from
customers c
join orders o using(customer_id)
order by
c.customer_id
下面则是left outer join的查询结果：
select
c.customer_id,
c.first_name,
o.order_id
from
customers c
left outer join orders o using(customer_id)
order by
c.customer_id
下面的例子shippers是inner join:
select
c.customer_id,
c.first_name,
o.order_id
from
customers c
left outer join orders o using(customer_id)
join shippers sh using(shipper_id)
order by
c.customer_id
下面例子，如果全采用left outer join则如下：
select
c.customer_id,
c.first_name,
o.order_id,
sh.name as shipper
from
customers c
left outer join orders o using(customer_id)
left outer join shippers sh using(shipper_id)
order by
c.customer_id
练习：
select
o.order_id,
o.order_date,
c.first_name as customer,
sh.name as shipper
from
orders o
join customers c using(customer_id)
join shippers sh using(shipper_id)
看下面outer join 的例子：
select
o.order_id,
o.order_date,
c.first_name as customer,
sh.name as shipper,
os.name as status
from
orders o
join customers c using(customer_id)
left join shippers sh using(shipper_id)
join order_statuses os on o.status = os.order_status_id
看下面例子使用inner join：
use sql_hr;
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
下面的例子使用left outer join
use sql_hr;
select
e.employee_id,
e.first_name,
m.first_name as manager
from
employees e
left outer join
employees m on e.reports_to = m.employee_id
上面例子拿掉outer后仍然是left outer join:
use sql_hr;
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
注意：
use sql_store;
select
o.order_id,
c.first_name
from
orders o
join customers c
on o.customer_id = c.customer_id
use sql_store;
select
o.order_id,
c.first_name
from
orders o
join customers c
using (customer_id)
use sql_store;
select
o.order_id,
c.first_name,
sh.name as shipper
from
orders o
join customers c
using (customer_id)
join shippers sh
using (shipper_id)
use sql_store;
select
o.order_id,
c.first_name,
sh.name as shipper
from
orders o
join customers c
using (customer_id)
left join shippers sh
using (shipper_id)
use sql_store;
select
*
from
order_items oi
join
order_item_notes oin
on oi.order_id = oin.order_id and
oi.product_id = oin.product_id
use sql_store;
select
*
from
order_items oi
join
order_item_notes oin
using (order_id, product_id)
练习下面查询：
use sql_invoicing;
select
p.date,
c.name as client,
p.amount,
pm.name as payment_method
from
payments p
join clients c using (client_id)
join payment_methods pm
on p.payment_method = pm.payment_method_id
参看下面的例子，我们使用natural join后并未指定连接的关联字段：
use sql_store;
select
o.order_id,
c.first_name
from
orders o
natural join customers c
注意：
在使用natural join后查询会使用两表中共同的字段customer_id进行关联，这种方式并不常用。
查看下面例子，执行交叉连接是，第一张表的记录会逐条与第二张表的记录进行迭代，因此，不需要指定关联条件：
use sql_store;
select
c.first_name as customer,
p.name as product
from
customers c
cross join products p
order by c.first_name
交叉关联也可用下面的语句表示：
use sql_store;
select
c.first_name as customer,
p.name as product
from
customers c,
products p
order by c.first_name
查看下面联合查询的例子：
use sql_store;
select
order_id,
order_date,
'Active' as status
from
orders
where
order_date >= '2019-01-01'
union
select
order_id,
order_date,
'Archived' as status
from
orders
where
order_date < '2019-01-01'
我们考察一张表，一张表由多个字段组成，而每个字段又包含以下属性：
如果没有指定插入的字段列表，则请按表的字段顺序填写相关的插入值，如下：
insert into customers
values (
200,
'Jerry',
'Smith',
'1968-08-20',
'13888000000',
'470# Bailong RD, Kunming, Yunnan, China',
'KM',
'YN',
1000
);
我们也可以提供插入的字段列表如下：
insert into customers (
customer_id,
first_name,
last_name,
birth_date,
phone,
address,
city,
state,
points
)
values (
200,
'Jerry',
'Smith',
'1968-08-20',
'13888000000',
'470# Bailong RD, Kunming, Yunnan, China',
'KM',
'YN',
1000
);
此处可能存在不合适展示的内容，页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容，可点击提交进行申诉，我们将尽快为您处理。