SQL语法大全

菜鸟算法小职员

已于 2023-08-09 11:25:40 修改

阅读量48

点赞数

文章标签： sql 数据库 mysql

于 2022-05-07 01:55:41 首次发布

本文链接：https://blog.csdn.net/weixin_36752983/article/details/120589793

版权

大数据工具及数据处理常用包专栏收录该内容

1 篇文章 0 订阅

订阅专栏

参考链接

数据库基础知识

分类

数据库分为关系型数据库和非关系型数据库。
关系型数据库是建立在关系数据库模型基础上的数据库，借助于集合代数等概念和方法来处理数据库中的数据，同时也是一个被组织成一组拥有正式描述性的表格。
非关系型数据库比如键值存储数据库

sql基本语法知识

SQL 对大小写不敏感, 一般用大写来表示关键字
大间隔, tab, 换行符在SQL语句中没有作用
结构固定, 子句可以省略但顺序不能改变
字符串用单引号或者双引号
– 表示注释
用分号表示一个命令
数据类型
INT 整型
VARCHAR 可变长字符
CHAR 定长字符
固定顺序：
SELECT
FROM
WHERE
ORDER BY
LIMIT
USE sql_store;

基础查询语句

SELECT

SELECT    -- 按顺序选择
	DISTINCT last_name,  -- 去重
    first_name, 
    points, 
    points* 10  + 100  AS discount_factor  
    --  按照数学运算优先级
    -- AS 可以给列重命名,  AS 'discount factor'
from customers
where City = 'orlando'
and points > 3000 -- 关系符号包括 > >= < <= != <>
and birth_date >= '1990-01-01'
ORDER BY first_name;

WHERE

SELECT * 
FROM customers
WHERE birth_date >= '1990-01-01' OR
	  points > 300 AND state = 'VA';  
	  -- OR / AND / NOT
	  -- 默认 AND优先级更高

IN/BETWEEN

SELECT * 
FROM customers
WHERE state NOT IN ('VA', 'FL', 'GA')
and points BETWEEN 1000 AND 3000 ;-- [1000,3000]

LIKE

SELECT * 
FROM customers
WHERE last_name LIKE 'b____y'; 
-- 'b%' 以b开头
-- '%b%' 含有b
-- '%b' 以b结尾
-- _ 表示单个字符
-- % 表示若干个字符

REGEXP

SELECT * 
FROM customers
WHERE last_name REGEXP 'field';   -- 这里和 LIKE相同 表示含有

SELECT * 
FROM customers
WHERE last_name REGEXP 'field$|mac'; 
-- ^ 开始
-- $ 结尾
-- | OR 
-- [abcd]
-- [a-f]

SELECT * 
FROM customers
WHERE last_name REGEXP '[gim]e';

NULL

SELECT * 
FROM customers
WHERE phone IS NOT NULL;

ORDER BY

默认按照primary key排列

SELECT * 
FROM customers
ORDER BY state DESC, first_name;  -- 按照state降序排列,再按firstname升序
-- 在mysql中可以用未被选中的列名排序

SELECT first_name, last_name
FROM customers
ORDER BY 1, 2;  -- 1, 2指的是选中的列名, 从1开始, 一般不用

SELECT *, quantity * unit_price AS total_price
FROM order_items
WHERE order_id = 2
ORDER BY total_price DESC;  -- 降序排列

聚合函数

-- 聚合函数只运行非空值,默认不去重
SELECT 
	MAX(invoice_total) AS highest,
    MIN(invoice_total) AS lowest,
    AVG(invoice_total) AS average,
    SUM(invoice_total) AS total,
    COUNT(invoice_total * 1.1) AS num,
    COUNT(*) AS total_records,
    COUNT( DISTINCT client_id) AS num_lient
FROM invoices
WHERE invoice_date > '2019-07-01'

GROUP BY

SELECT 
	client_id,
    SUM(invoice_total) AS total_sales
FROM invoices
WHERE invoice_date >= '2019-07-01'
GROUP BY client_id
ORDER BY total_sales DESC

SELECT 
	state,
    city,
    SUM(invoice_total) AS total_sales
FROM invoices i
JOIN clients c USING (client_id)
GROUP BY state, city

HAVING

SELECT 
	client_id,
    SUM(invoice_total) AS total_sales,
    count(*) AS number_of_invoices
FROM invoices 
GROUP BY client_id
HAVING total_sales > 500 AND number_of_invoices > 5

ROLL UP

SELECT 
	client_id,
    SUM(invoice_total) AS total_sales
FROM invoices 
GROUP BY client_id WITH ROLLUP


SELECT 
	state,
    city,
    SUM(invoice_total) AS total_sales
FROM invoices i
JOIN clients c USING (client_id) 
GROUP BY state, city WITH ROLLUP

LIMIT

限制返回记录的数量

SELECT * 
FROM customers
LIMIT 3;     -- 少于3则返回全部记录

表运算

INNER JOIN 内连接

SELECT order_id, orders.customer_id, first_name
FROM orders
INNER JOIN customers
	ON orders.customer_id = customers.customer_id; --只保留匹配成功的记录
	
SELECT order_id, o.customer_id, first_name
FROM orders o -- 简称
INNER JOIN customers c
	ON o.customer_id = c.customer_id;

跨数据库JOIN

SELECT  *
FROM order_items oi
INNER JOIN sql_inventory.products 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 -- 默认innerjoin
JOIN order_statuses os
	ON o.status = os.order_status_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;

隐士连接, 二者相等,
注意不要忘记WHERE否则会变成交叉

SELECT * 
FROM orders o, customers c
WHERE o.customer_id = c.customer_id;

OUTER JOIN 外连接

分为左连接和右连接

-- 显示所有顾客的订单信息, 没有订单的顾客订单编号为NULL
SELECT	 
	c.customer_id,
	c.first_name,
    o.order_id
FROM customers c
LEFT JOIN orders o
	ON c.customer_id = o.customer_id;

-- 显示所有订单中的信息
SELECT	 
	c.customer_id,
	c.first_name,
    o.order_id
FROM customers c
RIGHT JOIN orders o
	ON c.customer_id = o.customer_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	
	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

USING等价于join操作中的ON

使用条件：

查询必须是等值连接。
等值连接中的列必须具有相同的名称和数据类型。

SELECT 
	o.order_id,
    c.first_name,
    customer_id -- 用using join之后只有一列customer_id
FROM orders o
JOIN customers c
	-- ON o.customer_id = c.customer_id;
	Using (customer_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);

NATURAL JOIN 自然连接

根据相同的列名自动连接

SELECT *
FROM orders o
NATURAL JOIN customers c;

CROSS JOIN

笛卡尔积

SELECT *
FROM customers c 
CROSS JOIN orders o;

SELECT *
FROM customers c , orders o;  -- 隐式语法

UNION

根据订单日期分为 active 和 archived

SELECT 
	order_id,
    order_date,
    'Active' AS status
FROM orders o
WHERE order_date >= '2019-01-01'
UNION
SELECT 
	order_id,
    order_date,
    'Archived' AS status
FROM orders o
WHERE order_date < '2019-01-01';

连接不同表格

SELECT	
	first_name.  -- 列名以第一个表格中的名字为准
FROM customers
UNION 
SELECT name
FROM shippers

columns

INSERT INTO customers
VALUES (
	DEFAULT,
	'first_name',
	'smith',
	'1990-01-01',
	NULL,
	'address',
	'city',
	'CA',
DEFAULT)

INSERT INTO customers(
	first_name,
    last_name,
    birth_date,
    address,
    city,
    state)
VALUES (
	'first_name',
	'smith',
	'1990-01-01',
	'address',
	'city',
	'CA')

插入多行

INSERT INTO shippers (name)
VALUES ('shippper1'),
		('shippper2'),
        ('shippper3')

插入分层行

INSERT INTO orders (customer_id, order_date, status)
VALUES (1, '2019-01-02',1);

INSERT INTO order_items
VALUES 
	(LAST_INSERT_ID(), 1, 1, 2.95),
	(LAST_INSERT_ID(), 2, 1, 3.95);

建表语句

创建表复制


CREATE TABLE order_archived AS
SELECT * FROM orders;    	-- 表没有主键 , 不会按顺序排列

INSERT INTO order_archived ().   -- 使用子查询
SELECT * FROM orders
WHERE order_date < '2019-01-01';

更新单行

UPDATE invoices
SET payment_total = 10, payment_date = '2019-03-01'
WHERE invoice_id = 1;

UPDATE invoices
SET 
	payment_total = invoice_total * 0.5, 
	payment_date = due_date
WHERE invoice_id = 3;

更新多行

UPDATE invoices
SET 
	payment_total = invoice_total * 0.5, 
	payment_date = due_date
WHERE client_id = 3;    -- 或者用WHERE的其他条件

子查询

UPDATE invoices
SET 
	payment_total = invoice_total * 0.5, 
	payment_date = due_date
WHERE client_id = 
	(SELECT client_id
	FROM clients
	WHERE name = 'Myworks')

UPDATE invoices
SET 
	payment_total = invoice_total * 0.5, 
	payment_date = due_date
WHERE client_id IN
	(SELECT client_id
	FROM clients
	WHERE state IN ('CA','NY'))

删除行

DELETE FROM invoices
WHERE client_id = 2;

DELETE FROM invoices
WHERE client_id = (
	SELECT client_id
    FROM  clients
    WHERE name = 'Myworks'
)

复杂查询

子查询

单价比生菜贵的产品

SELECT *
FROM products
WHERE unit_price > (
	SELECT unit_price
    FROM products
    WHERE product_id = 3
)

查找没有被订购过的产品

SELECT * 
FROM products
WHERE product_id NOT IN (
	SELECT DISTINCT product_id
	FROM order_items)

查找没有invoice的顾客

USE sql_invoicing;
SELECT * 
FROM clients
LEFT JOIN invoices USING (client_id)
WHERE invoice_id IS NULL

SELECT * 
FROM clients
WHERE client_id NOT IN (
	SELECT DISTINCT client_id
    FROM invoices)

ALL

筛选大于client 3 所有invoice 的invoice

SELECT  * 
FROM invoices
WHERE invoice_total > (
	SELECT MAX(invoice_total)
	FROM invoices
	WHERE client_id  = 3)

SELECT  * 
FROM invoices
WHERE invoice_total > ALL(
	SELECT invoice_total
	FROM invoices
	WHERE client_id  = 3)

ANY

SELECT * 
FROM clients
WHERE client_id IN 
(
	SELECT  client_id
	FROM invoices
	GROUP BY client_id
	HAVING COUNT(*) >= 2
)

SELECT * 
FROM clients
WHERE client_id = ANY
(
	SELECT  client_id
	FROM invoices
	GROUP BY client_id
	HAVING COUNT(*) >= 2
)

EXISTS

-- 	子查询为外查询返回一个结果,需要存储空间
SELECT * 
FROM clients
WHERE client_id IN (
	SELECT DISTINCT client_id
    FROM invoices
)

-- 可以提高效率
SELECT * 
FROM clients c
WHERE EXISTS (
	SELECT client_id
    FROM invoices
    WHERE client_id = c.client_id)

-- 找出每个invoice 和 均值的差值
SELECT 
	invoice_id,
    invoice_total,
    (SELECT AVG(invoice_total)
		FROM invoices) AS invoice_average,
	invoice_total - (SELECT invoice_average)  AS diff
FROM invoices

子查询

SELECT * 
FROM (
	SELECT 
		client_id,
		name,
		(SELECT SUM(invoice_total)
			FROM invoices
			WHERE client_id = c.client_id) AS total_sales,
		(SELECT AVG(invoice_total) FROM invoices) AS average,
		(SELECT total_sales - average) AS diff
	FROM clients c
) AS sals_summary
WHERE total_sales is NOT NULL

内置函数

NUMERIC FUNCTOIN

SELECT ROUND (5.7345 , 2);  -- 四舍五入
SELECT CEILING(5.7);     -- 向上取整函数
SELECT FLOOR(5.7);
SELECT ABS(-4);
SELECT RAND();     -- 0-1 之间的随机浮点数

STRING FUNCTOINS

SELECT LENGTH('sky'); 
SELECT UPPER('sky'); 
SELECT LOWER('Sky'); 
SELECT LTRIM('   Sky。 ');     -- 去除左侧空白
SELECT RTRIM('   Sky。 ');
SELECT TRIM('   Sky。 ');
SELECT LEFT('abcdefg', 4);
SELECT RIGHT('abcdefg', 4);
SELECT SUBSTRING('abcdefg', 3,4);   --  起始位置(从0开始, 长度
SELECT LOCATE('b','abcdefg' );      -- 2
SELECT LOCATE('q','abcdefg' );      -- 0
SELECT REPLACE('abcdefg' ,'ab','cc');    
SELECT CONCAT('abc' ,'ab');

SELECT CONCAT(first_name, ' ',last_name ) AS full_name
FROM customers

TIME FUNCTION

SELECT NOW(), CURDATE(), CURTIME();
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW()), HOUR(NOW()), MINUTE(NOW()), SECOND(NOW()); -- 返回整数值
SELECT DAYNAME(NOW()), MONTHNAME(NOW());   -- 返回字符串
SELECT EXTRACT(YEAR FROM NOW());
SELECT EXTRACT(MONTH FROM NOW());
SELECT EXTRACT(DAY FROM NOW())

时间/日期格式化

SELECT DATE_FORMAT(NOW(), '%y'); --  21
SELECT DATE_FORMAT(NOW(), '%Y'); --  2021
SELECT DATE_FORMAT(NOW(), '%y'); --  21
SELECT DATE_FORMAT(NOW(), '%m %Y'); -- 10 2021
SELECT DATE_FORMAT(NOW(), '%M %Y'); -- Octobor 2021
SELECT TIME_FORMAT(now(), '%H:%i %p');  -- 17:32 PM

SELECT DATE_ADD(now(), INTERVAL 1 DAY);  -- 返回明天的此刻 2021-10-09 17:34:24
SELECT DATE_ADD(now(), INTERVAL 1 YEAR);  -- 返回明年的此刻 2022-10-08 17:34:24
SELECT DATE_ADD(now(), INTERVAL -1 YEAR);  -- 返回去年的此刻 2020-10-08 17:35:18
SELECT DATE_SUB(now(), INTERVAL 1 YEAR);  -- 返回去年的此刻 2020-10-08 17:35:18
SELECT DATEDIFF('2019-01-05','2019-01-01');  -- 4 不考虑HOUR, 互换时间会得到一个负值
SELECT TIME_TO_SEC('09:00') - TIME_TO_SEC('09:02'); -- -120

ISFULL

SELECT 
	order_id,
    IFNULL(shipper_id, 'Not assgined') AS shipper
FROM orders

SELECT 
	order_id,
    COALESCE(shipper_id, comments,'Not assgined') AS shipper.   -- 返回第一个非空值
FROM orders

IF

SELECT 
	order_id,
    order_date,
    IF(YEAR(order_date) = YEAR('2019-01-01'), 'Active','Archived') 
    	AS category  -- 满足条件则返回 active, 否则返回后者
FROM orders

case

SELECT 
	order_id,
    order_date,
    CASE 
		WHEN YEAR(order_date) = YEAR('2019-01-01') THEN 'Active'
        WHEN YEAR(order_date) = YEAR('2018-01-01') THEN 'last year'
        WHEN YEAR(order_date) < YEAR('2018-01-01') THEN 'Archived'
        ELSE  'future'
	END AS category
FROM orders

快捷键

shift + command + enter 执行全部或选中的指令

VIEW

简化查询
减小数据库设计改动的影响
使用视图限制基础表的访问, 加强数据的安全性

create view

-- 获取每位客户的销售总额
CREATE VIEW sales_by_client AS
SELECT 
	c.client_id,
    c.name,
    SUM(invoice_total) AS total_sales
FROM clients c
JOIN invoices i USING (client_id)
GROUP BY client_id, name

delete view

方法 1 : 先删除

drop view sales_by_client

CREATE OR REPLACE VIEW sales_by_client AS
SELECT 
	c.client_id,
    c.name,
    SUM(invoice_total) AS total_sales
FROM clients c
JOIN invoices i USING (client_id)
GROUP BY client_id, name

UPDATE VIEW

可更新view:
没有以下函数的view叫可更新view, 更新和真实表相同

-- DISTINCT
-- Aggregate(SUM, MAX, MIN
-- GROUP BY / HAVING
-- UNION

只有当view中有所有基础表中要用到的列, 插入新发票才会生效

WITH CHECK OPTION

CREATE OR REPLACE VIEW invoice_with_balance AS
SELECT 
	invoice_id,
    number,
    client_id,
    invoice_total,
    payment_total,
    invoice_total - payment_total AS balance,
    invoice_date,
    due_date,
    payment_date
FROM invoices
WHERE (invoice_total - payment_total) > 0
WITH  CHECK OPTION  
-- 防止UPDATE或者DELETE语句讲行从VIEW中删除
-- 如果没有, 修改payment_total = invoice_total , 该行将被删除, 添加之后会有报错提示

stored procedure

优点:
存储管理sql
faster execution
data security

create procedure

DELIMITER $$
CREATE PROCEDURE get_clients()
BEGIN
	SELECT * 
    FROM clients;
END$$
DELIMITER ;

调用:

CALL get_clients()

delete procedure

DROP PROCEDURE IF EXISTS get_clients

procedure parameter

待更新。。