mysql基础入门总结----基础增、删、改、查

从单表中得到数据

ORDER BY

SELECT
	*
FROM
	table
WHERE 
	id = 1
ORDER BY id,name DESC

BETWEEN a AND b（包括a和b）

SELECT
	*
FROM
	table
WHERE 
	date BETWEEN ‘1900-01-01‘ AND ‘2000-01-01‘ 

NOT 、AND 、OR

SELECT
	*
FROM
	table
WHERE 
	NOT (id>100 AND id<150) OR (id > 100 AND id <150)
	这里NOT只影响了OR的前面半句

IN

SELECT
	*
FROM
	table
WHERE 
	name in (‘a‘,‘b‘,‘c‘)

LIKE

SELECT
	*
FROM
	table
WHERE
	name LIKE ‘%y‘ or name LIKE ‘_y‘

REGEXP

SELECT
	*
FROM
	table
WHERE
	name REGEXP ‘^a|p$‘ OR name REGEXP ‘a[wvsdrew]‘

IS NULL

SELECT
	*
FROM
	table
WHERE
	phone IS NULL or phone IS NOT NULL

LIMIT

SELECT
	*
FROM
	table
ORDER by id
LIMIT 6,3

从多个表中得到数据

INNER JOIN

SELECT
	*
FROM
	table1
(INNER)JOIN
	table2
	ON
	table1.id = table2.id

SELECT 
	*
FROM
	table1 t1
JOIN 
	table2 t2
	ON
	t1.id = t2.id
LIMIT 6,3

多个表join

SELECT 
    p.payment_id,c.name,p.date,p.amount,pm.name
FROM
    sql_invoicing.payments p
        JOIN
    payment_methods pm ON p.payment_method = pm.payment_method_id
        JOIN
    clients c ON c.client_id = p.client_id;

复合join

-- 复合合并的条件
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

隐式合并

select *
from orders o ,customers c
where o.customer_id = c.customer_id
-- where语句不能丢，否则会发生交叉合并
-- 并不建议这种隐式合并，不如用join来合并

OUTER JOIN

SELECT 
    c.customer_id, c.first_name, o.order_id
FROM
    customers c
        LEFT JOIN   -- LEFT (OUTER) JOIN 意思是左边的表无论是否满足ON后面的条件都会返回
    orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_id

多个表OUTER JOIN

SELECT 
    o.order_date,
    o.order_id,
    first_name,
    sh.name AS shipper,
    os.name
FROM
    orders o
        LEFT JOIN
    customers c ON c.customer_id = o.customer_id
        LEFT JOIN
    shippers sh ON o.shipper_id = sh.shipper_id
        LEFT JOIN
    order_statuses os ON os.order_status_id = o.status

表的自我外连接

use sql_hr;
SELECT 
    e.employee_id, e.first_name, o.first_name
FROM
    employees e
        LEFT JOIN
    employees o ON e.reports_to = o.employee_id;

USING()

SELECT 
    date, cl.name, p.amount, pm.name
FROM
    payments p
        JOIN
    clients cl USING (client_id)  --只用在两个表的某一列名称相同时才能用
        JOIN
    payment_methods pm ON p.payment_method = pm.payment_method_idemployees

NATURAL JOIN

natural join自然合并最好不用，natural join 会自动检测两个表之间是否含有列名相同的列，然后内连接inner join
条件不可控

交叉合并 CROSS JOINS

-- 显示写法
SELECT 
    c.first_name AS customer, p.name AS product
FROM
    customers c
        CROSS JOIN
    products p
    -- 会得到customer和product的所有混合结果

--隐式写法
SELECT 
    c.first_name AS customer, p.name AS product
FROM
    customers c,
    products p

联合查询

SELECT 
    customer_id, first_name, points, ‘Bronze‘ AS type
FROM
    customers
WHERE
    points < 2000 
    
UNION
 
SELECT 
    customer_id, first_name, points, ‘Silver‘ AS type
FROM
    customers
WHERE
    points >= 2000 AND points < 3000 
    
UNION

SELECT 
    customer_id, first_name, points, ‘Gold‘ AS type
FROM
    customers
WHERE
    points >= 3000

插入数据

单表插入

use sql_store;
insert into products
values
(default,‘a‘,1,1.0),
(default,‘b‘,2,2.0),
(default,‘c‘,3,3.0);

多表层级插入

insert into orders (customer_id,order_date,status)
values(1,‘2020-01-01‘,1);

insert into order_items values
(last_insert_id(),1,10,1.0),
(last_insert_id(),2,20,2.0)

创建一个表的副本

CREATE TABLE orders_achived AS SELECT * FROM
    orders

复制一个表的一部分

insert into orders_achived
select * from orders where order_date < ‘2019-01-01‘;

SELECT作为子语句创建表

CREATE TABLE invoices_archived AS SELECT i.invoice_id,
    cl.name AS client,
    i.number,
    i.invoice_total,
    i.invoice_date,
    i.payment_total,
    i.payment_date,
    i.due_date FROM
    invoices i
        JOIN
    clients cl USING (client_id)
WHERE
    i.payment_date IS NOT NULL

更新数据

单行修改与多行修改

update invoices 
set payment_total = 10,payment_date = ‘2020-01-01‘
where invoice_id = 1
--------------------------------------------------
update customers 
set points = points + 50
where birth_date < ‘1990-01-01‘

多行修改（用select子句）

-- 用select 作子句来限制修改范文
update orders 
set comments = ‘GOLD Customer‘
where customer_id in(select customer_id from customers where points > 3000)

删除数据

delete

delete from invoices_archived
where client_id = 1;  -- 如果不加where条件，delete会将invoices_archived的数据全部删除
-- 所以执行delete语句时一定要慎重

mysql基础入门总结----基础增、删、改、查

原文：https://www.cnblogs.com/woshi123/p/12584441.html