一 SQL通用语法
1.SQL语句可以单行或多行书写,以分号结尾。
2.SQL语句可以使用空格/缩进来增强语句可读性。
3.MySQL中不区分大小写,关键字建议使用大写。
4.SQL中,使用-- 表示注释(MySQL中也可以使用#表示)。
二 数据库、表操作
1.查询
-- 查询所有数据库
show databases;
-- 查询当前数据库
select database();
2.创建数据表
CREATE TABLE table_name (
column1 data_type constraint,
column2 data_type constraint,
...
)
3.删除数据库
drop database [if exists] 数据库名;
4.使用数据库
use 数据库名;
5.查询当前数据库的所有表
show tables;
6.查询表结构
DESC 表名;
7.更新表中数据
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition
8.插入数据
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
9.删除数据
DELETE FROM table_name
WHERE condition
10.修改表结构
ALTER TABLE table_name
ADD column_name data_type
11.修改表名
alter table 表名 rename to 新表;
12.删除字段
alter table 表名 drop 字段名;
三 查询操作
1)基础语法:
-- 基础语法
SELECT column_name(s)
FROM table_name
WHERE condition
select *
from customers
2)在where子句中,我们可以使用and或or来连接多个条件:
select *
from customers
where birth_date > '1990-01-01' or points>1000 and state ='va';
3)not运算符,用于选择不符合条件的数据:
select *
from customers
where not(birth_date > '1990-01-01' or points>1000);
4)in运算符,查找在该范围里的数据:
select *
from customers
where state in ('VA','FL','GA');
5)between运算符,查找在某连续范围内的数据:
select *
from customers
where points >=1000 and points <=3000;
select *
from customers
where points between 1000 and 3000;-- between
6)like运算符模糊匹配:
select *
from customers
where last_name like 'b%';-- 正则查询 %表示任意位字符 _表示一位字符
select *
from customers
where address like '%trail%' or address like '%avenue%';
select *
from customers
where phone not like '%9';
7)正则表达式
select *
from customers
where last_name regexp 'field' ; -- 正则表达式 regexp ^表示开头 $表示字符串末尾alter
select *
from customers
where last_name regexp 'field$|mac|rose' ; -- |表示或,前后字符间不要有空格
select *
from customers
where last_name regexp '[gim]e'; -- []表示e前面的一位是 gim 三者之一
8)is null运算符,查找为空数据:
select *
from customers
where phone is null;
9)order by子句按字段进行排序,desc表示降序排序,asc表示升序排序(默认):
select *
from customers
order by state, first_name desc;
select first_name,last_name
from customers
order by 1,2; -- 根据列值排序
select *,unit_price*quantity as price
from order_items
where order_id =2
order by unit_price*quantity desc;
10)limit子句,限制查询结果条数:
select *
from customers
limit 3;
select *
from customers
limit 6,3;-- limit 子句 偏移量为6,显示7-9条结果
四 表连接操作
1.内连接(INNER JOIN):使用join关键字进行连接操作,并将关联列用on进行连接。只返回两个表中满足连接条件的匹配行。如果某一行在其中一个表中没有匹配项,那么这一行将不会出现在结果集中。
select *
from orders
join customers
on orders.customer_id = customers.customer_id;
选择两张表都有的列时,要指明具体的表名
select order_id,orders.customer_id
from orders
join customers
on orders.customer_id = customers.customer_id;
select *
from orders as o -- 指代别名 as可加可不加(一旦指定别名,则必须使用别名,不能使用原名)
join customers c
on o.customer_id = c.customer_id;
2.跨数据库连接
use sql_inventory;
select *
from store.order_items oi
join products p -- 指明数据库名字
on oi.product_id = p.product_id;
use store;
select *
from order_items oi
join sql_inventory.products p -- 指明数据库名字
on oi.product_id = p.product_id;
3.自连接(SELF 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;
4.多表连接
use sql_store;
select
o.order_id,
o.order_date,
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;
5.复合连接:用两列的值进行检索
select *
from order_items oi
join order_item_notes oin
on oi.order_id = oin.order_Id
and oi.product_id = oin.product_id;
6.隐式连接
select *
from orders o,customers c
where o.customer_id = c.customer_id;
等价于:
select *
from orders o
join customers c
on o.customer_id = c.customer_id;
7.外连接(LEFT JOIN(左连接)或 LEFT OUTER JOIN或RIGHT JOIN(右连接)或 RIGHT OUTER JOIN):返回主表中的所有行,以及连接表中与之匹配的行。如果主表中没有匹配项,则结果中的连接表列会显示为NULL
。
select *
from orders o
join customers c
on o.customer_id = c.customer_id;
8.多表外连接
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;
9.自外连接
use sql_hr;
select *
from employees e
left join employees m
on e.reports_to = m.employee_id;
补充.using 子句
use sql_store;
select
o.order_id,
c.first_name
from orders o
join customers c
-- on o.customer_id = c.customer_id; -- 如果两表中的列名一致,则可以用using子句替换on子句
using(customer_id); -- 有多个查询条件时,用,分隔(列值1,列值2)
10.自然连接(NATURAL JOIN):自动使用两个表中同名的列进行连接,不需要明确指定ON条件
select *
from orders
natural join customers c ;-- 无需指出需要链接的列名,系统自动连接
11.交叉连接(CROSS JOIN):返回左表和右表的笛卡尔积,也就是返回所有可能的行组合。这通常在没有ON条件的连接中发生。
select *
from customers c
cross join products p;
-- 交叉连接的显式表达形式
select *
from customers c,products p;
-- 交叉连接的隐式表达形式
12.联合(union):连接多个查询结果
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';
select
first_name
from customers
union -- 合并不同表格的两个结果集
select
name
from shippers;