USE sql_store; # 获取给定表格中所有顾客数据
SELECT * FROM customers #查询表 #显示所有列 #SELECT 1,2 显示1 2 列
WHERE customer_id = 1 #where筛选 得到id为1的顾客
ORDER BY first_name #根据名字给顾客排序
1.2 * 返回全部列,指定想要返回的列; 给列一个别名;含有重复数时,使用distinct
SELECT points,
points*10 AS discount_factor # as 编辑变量名; 需要空格用" "符号
FROM customers
#####
SELECT distinct state
FROM customers
1.3 根据条件筛选
#a
SELECT *
FROM customers
WHERE state="va" #大小写都可以 sql不区分大小写
WHERE NOT state="va" #排除该条件的数据
WHERE birth_date > "1990-01-01" #sql 日期需要加上“ ”
#b
SELECT * FROM order_items
WHERE order_id = 6 AND unit_price*quantity > 30 #寻找order为6 总价超过30的数据
#c
WHERE state NOT IN ("VA","FL","GA") #筛选 state不含"VA","FL","GA"的数据
1.4 between字符
SELECT * FROM customers
WHERE points >= 1000 AND points <= 3000 # 等于 points BETWEEN 1000 AND 3000
# BETWEEN包含了临界值
1.5 检索特定字符串的某行LIKE REGEXP(正则表达式)
#a
#% 指任意字符 _指一个单字符
SELECT * FROM customers
WHERE last_name LIKE "b%" ##检索姓氏为B开头的; %b%:姓氏某处有b就可;%b 姓氏以b结尾的;_y:检索两个字符且最后一个字符是y
#b
#正则表达式用来搜索字符串 REGEXP
WHERE last_name LIKE "%b%" # =WHERE last_name REGEXP "b"
WHERE last_name REGEXP "^field" # 检索field开头 field$:检索field结尾
"field|mac":检索含field或mac
"[gim]e"#寻找ge ie me 的last_name "[a-h]e"同理
1.6 搜索缺失值属性记录
########a 搜索缺失值
#删掉缺失值的数据
WHERE phone IS NOT NULL
########b 排序
ORDER BY first_name # 加DESC表降序排列
ORDER BY 1,2 #按照1,2列排序
ORDER BY state,first_name # 按state排列,每个state按first_name 排列
state DESC,first_name DESC
########C 限定查询返回记录
LIMIT 4 #显示前4个数据
LIMIT 6,3 #跳过前6条记录然后获取3条记录即7 8 9的数据 6:偏移量
########d 多张表中选取列
SELECT * FROM orders
#将order表和customers表连接
INNER JOIN customers ON orders.customer_id=customers.customer_id #显示如下图
#以customer_id匹配,在order基础上加customers的数据
SELECT order_id,orders.customer_id FROM orders
#多张列表中有相同列时 需要用 ×××.customer_i
#例:
SELECT order_id,oi.product_id,quantity,oi.unit_price
FROM order_items oi
JOIN products p ON oi.product_id=p.product_id #简写数据名称oi p
######用一列识别 并根据此列跟其他表合并########
########e 两个数据表列合并
USE sql_store;
SELECT * FROM order_items oi
JOIN sql_inventory.products p ON oi.product_id=p.product_id
#加入sql_inventory表里的其他数据的某列
########自己给自己表加数据
select e.employee_id,e.first_name,m.first_name from employees e join employees m on e.reports_to=m.employee_id
#自己表中加入原本表里的数据 跟其他表合并的区别是from join 需用不同的别名 e m
########连接多个表
#连接三张表 列的顺序orders+customers+order_statuses
use sql_store;
select * # 显示5行示例:o.order_id,o.order_date,c.first_name,c.last_name,os.name
from orders o join customers c on o.customer_id = c.customer_id
join order_statuses os on o.status =os.order_status_id