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;

where语句及相关

-- 比较符号
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 语句

DELETE FROM table_name [WHERE Clause]

• 如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
• 你可以在 WHERE 子句中指定任何条件
• 您可以在单个表中一次性删除记录。