CS结构
逻辑结构
一个实例包含多个数据库
一个数据库包含多个表
列-- column;行--row
每一行的唯一标识--主键
数据排序:
#查询数据(无排序)
SELECT prod_name
FROM products;
# 各字符串借助ASCII码按位比较大小,从小到大排序
SELECT prod_name
FROM products
ORDER BY prod_name;
或SELECT prod_name
FROM products
ORDER BY prod_name ASC;
# 降序排序
SELECT prod_name
FROM products
ORDER BY prod_name DESC;
# 按prod_price降序,当出现prod_price值相同的数据时按prod_name升序
SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price DESC, prod_name ASC;
# ORDER BY与LIMIT的组合,能够找出一个列中最高或最低的值
# prod_price最低的一个
SELECT prod_price
FROM products
ORDER BY prod_price DESC LIMIT 1;
数据限制:
SELECT vend_id
FROM products;
# 关键字DISTINCT表示只返回不同的值
SELECT DISTINCT vend_id
FROM products;
# LIMIT限制结果(前5行)
SELECT prod_name
FROM products LIMIT 5;
# 为得出下一个5行(5行后的5行),可指定要检索的开始行和行数
SELECT prod_name
FROM products LIMIT 5, 5;
数据汇总:
# 聚集函数
# AVG(), COUNT(), MAX(), MIN(), SUM()
# AVG()函数
# prod_price的平均值
SELECT AVG(prod_price) AS avg_price
FROM products;
# COUNT()函数
# 数customers一共多少行
SELECT COUNT(*) AS num_cust
FROM customers;
# cust_email为非空的一共有多少行
SELECT COUNT(cust_email) AS num_cust
FROM customers;
# MAX()函数 (会忽略空值)
SELECT MAX(prod_price) AS max_price
FROM products;
# MIN()函数 (会忽略空值)
SELECT MIN(prod_price) AS min_price
FROM products;
# SUM()函数
# 总数量
SELECT SUM(quantity) AS items_ordered
FROM orderitems
WHERE order_num = 20005;
# 总金额=sum(各数量*各单价)
SELECT SUM(quantity*item_price) AS total_price
FROM orderitems
WHERE order_num = 20005;
# AVG()与DISTINCT结合
# 不重复的prod_price的平均价格
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;
# 组合聚集函数 同时算出好几个值
SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min, MAX(prod_price) AS price_max, AVG(prod_price) AS price_avg
FROM products;
数据查询与过滤
#查询语句
#单列(未排序)
SELECT prod_name
FROM products;
#多列
SELECT prod_id, prod_name, prod_price
FROM products;
#所有列
SELECT *
FROM products;
#去重
SELECT DISTINCT vend_id
FROM products;
#数据过滤
#使用WHERE--单值检查
#等于
SELECT prod_name, prod_price
FROM products
WHERE prod_price = 2.50;
#不等于
SELECT prod_name, prod_price
FROM products
WHERE prod_price != 2.50;
或SELECT prod_name, prod_price
FROM products
WHERE prod_price <> 2.50;
#大于
SELECT prod_name, prod_price
FROM products
WHERE prod_price > 10;
#小于等于
SELECT prod_name, prod_price
FROM products
WHERE prod_price <= 10;
#字符等于
SELECT prod_name, prod_price
FROM products
WHERE prod_name = ‘fuses’;
# 范围值检查
# 闭区间(>=5且<=10)
SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;
#空值检查
#为空
SELECT cust_name
FROM customers
WHERE cust_email IS NULL;
#不为空
SELECT cust_name
FROM customers
WHERE cust_email IS NOT NULL;
# 组合WHERE字句--多条件
# AND操作 -- 且
SELECT prod_id, prod_price, prod_name
FROM products
WHERE vend_id = 1003 AND prod_price <=10;
# OR操作 -- 或
SELECT prod_id, prod_price, prod_name
FROM products
WHERE vend_id = 1003 OR prod_price <=10;
# 匹配检查
# vend_id 等于1002 或1003(适合条件多的时候用)
SELECT prod_price, prod_name, vend_id
FROM products
WHERE vend_id IN (1002, 1003);
# vend_id 不等于1002和1003
SELECT prod_price, prod_name, vend_id
FROM products
WHERE vend_id NOT IN (1002, 1003);
数据分组
#数据分组
SELECT COUNT(*) AS num_prods
FROM products
WHERE vend_id =1003;
#创建分组 -- 以vend_id分组
SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;
#过滤分组 -- 产品数量大于3的组
SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id
HAVING COUNT(*)>=3;
#具有两个以上价格大于等于10的产品的供应商
SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >=10
GROUP BY vend_id
HAVING COUNT(*) >=2;
数据连接
#创建联结
SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id;
#联结多个表
SELECT vend_name, prod_name, prod_price, quantity
FROM vendors, products, orderitems
WHERE vendors.vend_id = products.vend_id
AND orderitems.prod_id = products.prod_id
AND order_num = 20005;
# 内部联结 INNER JOIN (都不为空)
# 目前为止所用的联结称为“等值联结”,它基于两个表之间的相等测试
# 这种联结也称为内部联结
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;
# SQL JOINS
数据插入
# 顺序与原表中一致
# 主键在插入后会自增,不需要用语句添加值
INSERT INTO customers
VALUES(NULL,
‘PeP E. LaPeW’,
‘100 Main Street’,
‘Los Angeles’,
‘CA’,
‘90046’,
‘USA’,
NULL,
NULL);
# 插入有内容的字段即可,顺序无所谓,表头与值对应即可
INSERT INTO customers(cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
)
VALUES(‘PeP E. LaPeW’,
‘100 Main Street’,
‘Los Angeles’,
‘CA’,
‘90046’,
‘USA’);
#插入多条
INSERT INTO customers(cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
)
VALUES(‘PeP E. LaPeW’,
‘100 Main Street’,
‘Los Angeles’,
‘CA’,
‘90046’,
‘USA’),
(‘PeP E. LaPeW2’,
‘100 Main Street2’,
‘Los Angeles2’,
‘CA2’,
‘90046’,
‘USA’);
数据更新
# 更新单个值
UPDATE customers
SET cust_email = ‘elmer@fudd.com’
WHERE cust_id = 10005
# 更新多个值
UPDATE customers
SET cust_name = ‘The Fudds’,
cust_email = ‘elmer@fudd.com’
WHERE cust_id = 10005
数据删除
# 除非确实打算更新和删除每一行,否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
# DELETE之前一定要先写WHERE以防删除全表
DELETE FROM customers
WHERE cust_id = 10006;
# DELETE 不需要列名或者通配符,DELETE删除整行而不是删除列