mysqlworkbench day1

USE sql_store; //只查询一个文件下的表或者双击选择数据库，不然没显示

SELECT *//返回所有列
FROM customers//查询的是文件下customers的表的所有列，可以直接接到select后
– WHERE customer_id = 1//查询id为1的顾客，只显示一条记录
– ORDER BY first_name//按照firstname排序

SELECT
last_name,
first_name,
points,
(points+10)*100 //注意算数表达式的顺序 * +，可以用括号改变顺序
(points+10)*10 AS discount_factor //列的名称改为discount_factor，加单引号可以在名称中加空格’discount factor‘
FROM customers

SELECT DISTINCT state //distinct重复的数据只显示一项

习题：
FROM customers
– Return all the products
– name
– unit price
– new price (unit price * 1.1)

SELECT name, unit_price, unit_price * 1.1 AS ‘new price’
FROM products
SELECT *
FROM Customers
WHERE state = ‘VA’ //字符串用“”或‘’，其中字符串不分大小写
WHERE state != ‘VA’//不在va州里的所有顾客的信息 !=或<>
WHERE birth_date > ‘1990-01-01’//日期用’’

习题
– Get the orders placed this year
SELECT *
FROM orders
WHERE order_date >= ‘2019-01-01’
SELECT *
FROM Customers
WHERE birth_date > ‘1990-01-01’ AND points > 1000 // AND(更高优先级) OR
简化为：
WHERE birth_date > ‘1990-01-01’ OR
(points > 1000 AND state = 'va’)

WHERE NOT (birth_date > ‘1990-01-01’ OR points > 1000) // not对 >的否定是<=，剩下同理
WHERE birth_date <= ‘1990-01-01’ AND points <= 1000//与上一句等价

习题
– from the order_items table,get the items for order #6
– where the total price is greater than 30

select *
from order_items
where order_id = ‘6’ and quantity * unit_price > 30
SELECT *
FROM Customers
WHERE state = ‘VA’ OR state = ‘GA’ OR state = ‘FL’ // V
WHERE state IN (‘VA’,‘FL’,'GA’)//V 与上一句等价，注意in与like的区别，in是只含有，like是属于的关系
WHERE state NOT IN (‘VA’,‘FL’,'GA’)//相反
WHERE state = ‘VA’ OR ‘GA’ OR ‘FL’ //X sql中不能将字符串同布尔表达式进行结合，布尔值只有N和Y，or一次不能比较两个字符串

习题
– return products with quantit in stock equal to 49，38，72
SELECT *
FROM products
WHERE quantity_in_stock IN (‘49’,‘38’,‘72’)
– SELECT *
– FROM customers
– WHERE points >= 1000 AND points <= 3000
– WHERE points BETWEEN 1000 AND 3000

习题
– return customers born between 1/1/1990 and 1/1/2000
SELECT *
FROM customers
WHERE birth_date BETWEEN ‘1990-01-01’ and '2000-01-01’
– SELECT *
– FROM customers
– WHERE last_name LIKE ‘b%’
– %代表任意字符串，_代表单字符。b无论大小写，查询出以b/B开头的lastname
– WHERE last_name LIKE ‘%b%’
– 查询出字符串中包含b的，b可在任意位置
– WHERE last_name LIKE ‘%v’
– 查询出字符串以v结尾的
– WHERE last_name LIKE ‘_y’
– 查询出字符串只有两位，第一位为任意，最后一位一定是y
– WHERE last_name LIKE ‘b_____y’
– 查询出以b开头，中间只有4位任意字符，y结尾的字符串

习题
– get the customers whose addresses contain trail or avenue
– phone numbers end with 9

select *
from customers
– where address like ‘%TRAIL%’ or
– address like ‘%AVENUE%’
where phone like '%9’
– select *
– from customers
– where last_name like ‘%field%’
– where last_name regexp ‘field’
– 正则表达式 以上得到相同的结果

– where last_name regexp ‘^field’
– ^表示字符串的开头,表示以field开头的字符串

– where last_name regexp ‘field$’
– $表示字符串的结尾,以field结尾的字符串

– where last_name regexp ‘field|mac’
– 表示字符串里含有field和mac的lastname

– where last_name regexp ‘[gim]e’
– 表示字符串里有ge或ie或me的顾客

– where last_name regexp ‘e[fmq]’
– 表示字符串里有ef或em或eq的顾客

– where last_name regexp ‘[a-h]e’

– 习题
– get the customers whose
– first names are elka or ambur
– last names end with ey or on
– last names start with my or contains se
– last names contain b followed by r or u

select *
from customers
– where first_name regexp ‘elka|ambur’
– where last_name regexp ‘ey$|on$’
– where last_name regexp ‘^my|se’
– b[r|u] 或br｜bu
where last_name regexp 'b[ru]’
– select *
– from customers
– where phone is null
– where phone is not null

– 习题
– get the orders that are not shipped
select *
from orders
where shipped_date is null
– SELECT *
– FROM customers
– ORDER BY first_name
– 按照字母表的顺序降序排列
– ORDER BY first_name DESC
– 改为降序

– 按照州给数据排列
– order by state, first_name
– 多列排序，可在后边分别加desc

– select first_name, last_name
– from customers
– order by birth_date
– 只显示出两列，且按照不再select的列中进行排序

– select first_name, last_name, 10 as points
– from customers
– order by points, first_name
– 可以用别名排序
– order by 1, 2
– 可以用列的顺序排列，但是不提倡，不方便修改

– 习题
– 选择所有order id 为2的项目，用总价格的降序排列
select * ,quantity * unit_price as total_price
from order_items
where order_id = ‘2’
order by total_price desc
select *
from customers
– limit 3
– 只筛选出前三条数据，超过了也只会筛选出表里有的所有数据
– page 1:1-3
– page 2:4-6
– page 3:7-9
– limit 6, 3
– 6代表偏移量，跳过前6条记录后获取3条记录
order by points desc
limit 3
– limit永远放在最后，注意句子顺序
– select *
– select order_id,orders.customer_id,first_name,last_name
– from orders
– from orders o
– o是orders的别名，用o代替orders
– join customers on orders.customer_id = customers.customer_id
– 连接orders表和customers的表时，确保他们中的顾客id一致
– 结果中前几列的表是orders中的/先选的orders
– select order_id,customer_id,first_name会报错，不确定是哪个表的顾客id，所以要加上前缀order.customer_id

– 用别名简化为
– select order_id,o.customer_id,first_name,last_name
– from orders o
– join customers c on o.customer_id = c.customer_id

select order_id, oi.product_id, quantity, oi.unit_price
from order_items oi
join products p on oi.product_id = p.product_id

– 跨数据链接
– select *
– from order_items oi
– join sql_inventory.products p
– on oi.product_id = p.product_id
– 此时默认的数据库是sql_store

use sql_inventory;

select *
from sql_store.order_items oi
join products p
on oi.product_id = p.product_id
– 只需要给不在当前数据库的表加前缀