2. MySQL语句

use sql_store;
select *
from customers;
-- where customer_id = 2
-- order by points
select 
  last_name,
    first_name,
    points,
    points + 10,    -- 各种运算都可以
    (points + 10) * 100 as 'discount factor'    -- 起别名，有空格加单引号
from customers;
select distinct state   -- distinct 不重复字段 
from customers;
-- return all the products
-- name
-- unit price
-- new price (unit price * 1.0)
select name,unit_price,unit_price * 1.1 as new_price
from products;

-- 比较符号
select *
from customers
where points > 3000 and state <> 'TX' or points = 3000 and state = 'TX';    
select * 
from ordes
where order_date >= '2019-01-01';
-- not
select * 
from ordes
where not state = 'IL';
select *
from order_items
where order_id = 6 and unit_price * quantity > 30;
-- in 范围
select *
from customers
where state not in ('VA','TX');     
-- between
select * 
from customers
where birth_date between '1990-01-02' and '2000-01-03';
-- like
-- %是*
-- _是?
select *
from customers
where last_name like '%b%' and 
    address not like '%trail%';
      
-- regexp 正则表达式
-- ^ 表示开始
-- $ 表示结束
-- | 逻辑或
-- [abcd]
-- [a-f]
select * 
from customers
where last_name like '%field%' and
    last_name regexp 'field' or
      last_name regexp '^field|mac|rose' or   -- 以field开头或者包含mac或rose
      last_name regexp '[a-h]e';
-- is null
select *
from customers
where phone is null;
-- order by
select *
from customers
order by state desc,first_name;
select birth_date, first_name, last_name, 10 as points
from customers
order by 1,2;   -- 相对顺序，从1开始
SELECT *,quantity * unit_price AS total_price
FROM order_items
WHERE order_id = 2
ORDER BY total_price DESC;
-- limit 限制数量
-- offset 偏移量
-- 可以结合使用page
select *
from customers
limit 3
offset 3

CREATE DATABASE 数据库名;

DELETE FROM table_name [WHERE Clause]