【SQL】SQL语法总结（一）：通用语法&查询操作&表连接操作

 一 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;