chapter4 检索数据
select prod_name from products
SELECT prod_id, prod_name, prod_price FROM products
SELECT * FROM products
SELECT DISTINCT vend_id FROM products
SELECT prod_name
FROM products
LIMIT 5
SELECT prod_name
FROM products
LIMIT 5, 4
SELECT products.prod_name FROM products
chapter5 排序检索数据
SELECT prod_name FROM products ORDER BY prod_name
SELECT prod_id, prod_name, prod_price
FROM products
ORDER BY prod_price, prod_name
SELECT prod_id, prod_name, prod_price
FROM products
ORDER BY prod_price DESC, prod_name
SELECT prod_id, prod_name, prod_price
FROM products
ORDER BY prod_price DESC LIMIT 1;
chapter6 过滤数据
SELECT prod_id, prod_name, prod_price
FROM products
WHERE prod_price = 2.5;
SELECT prod_id, prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 2 AND 5;
SELECT prod_id, prod_name, prod_price
FROM products
WHERE prod_price IS NULL;
chapter7 数据过滤
SELECT prod_id, prod_name, prod_price
FROM products
WHERE vend_id = 1003 AND prod_price <= 10;
SELECT prod_id, prod_name, prod_price
FROM products
WHERE vend_id = 1003 OR prod_price <= 10;
SELECT prod_id, prod_name, prod_price, vend_id
FROM products
WHERE vend_id IN (1002, 1003);
SELECT prod_id, prod_name, prod_price, vend_id
FROM products
WHERE vend_id NOT IN (1002, 1003);
chapter8 用通配符进行过滤
SELECT prod_id, prod_name, prod_price, vend_id
FROM products
WHERE prod_name LIKE 'Jet%';
SELECT prod_id, prod_name, prod_price, vend_id
FROM products
WHERE prod_name LIKE '%anv%';
SELECT prod_id, prod_name, prod_price, vend_id
FROM products
WHERE prod_name LIKE 's%e';
SELECT prod_id, prod_name, prod_price, vend_id
FROM products
WHERE prod_name LIKE '_ ton anvil';
chapter9 用正则表达式进行搜索
chapter10 创建计算字段
SELECT CONCAT(vend_name,'(',vend_country, ')')
FROM vendors
ORDER BY vend_name;
SELECT CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country), ')') AS
vend_title
FROM vendors
ORDER BY vend_name;
SELECT prod_id,
quantity,
item_price,
quantity*item_price AS expandeo_price
FROM orderitems
WHERE order_num = 20005;
chapter11 使用数据预处理函数
文本处理函数
left():返回串左边的字符
length():返回串的长度
locate():找出串的一个子串
lower():将串转换为小写
ltrim():去掉穿左边的空格
right():返回右边的字符串
rtrim():去掉右边的空格
soundex():返回串的soundex值
substring():返回子串的字符
upper():将串转换为大写
时间与日期函数
AddDate():增加一个日期(天、周等)
AddTime():增加一个时间(时、分等)
CurDate():返回当前日期
CurTime():返回当前时间
Date():返回日期时间的日期部分
DateDiff():计算两个日期之差
Date_Add():高度灵活的日期运算函数
Date_Format():返回一个格式化的日期或时间串
Day():返回一个日期的天数部分
DayOfWeek():对于一个日期,返回对应的星期几
Hour():返回一个时间的小时部分
Minute():返回一个时间的分钟部分
Month():返回一个日期的月份部分
Now():返回当前日期和时间
Second():返回一个时间的秒部分
Time():返回一个日期时间的时间部分
Year():返回一个日期的年份部分
数值预处理函数
Abs() 返回一个数的绝对值
Cos() 返回一个角度的余弦
Exp() 返回一个数的指数值
Mod() 返回除操作的余数
Pi() 返回圆周率
Rand() 返回一个随机数
Sin() 返回一个角度的正弦
Sqrt() 返回一个数的平方根
Tan() 返回一个角度的正切
chapter12 汇总数据
聚集函数
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和
聚集不同值
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;
组合聚集函数
SELECT COUNT(*) AS count_item,
MIN(prod_price) AS price_min,
MAX(prod_price) AS price_amx
FROM products;
chapter13 分组数据
SELECT vend_id, COUNT(*) AS num_prod
FROM products
GROUP BY vend_id
SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2
SELECT vend_id, COUNT(*) AS num_prod
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2;
SELECT order_num, SUM(quantity*item_price) AS order_total
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 50
ORDER BY order_total;
chapter14 子查询
SELECT cust_name, cust_address
FROM customers
WHERE cust_id IN(SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2'));
SELECT cust_name, (SELECT COUNT(*)
FROM orders
WHERE orders.cust_id = customers.cust_id) AS orders_total
FROM customers;
chapter15 联结
SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;
SELECT vend_name, prod_name, prod_price, quantity
FROM vendors, orderitems, products
WHERE products.vend_id = vendors.vend_id
AND orderitems.prod_id = products.prod_id
AND order_num = 20005;
chapter16 创建高级联结
SELECT cust_name, cust_contact
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
AND oi.order_num = o.order_num
AND prod_id = 'TNT2';
SELECT p1.prod_id, p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
AND p2.prod_id = 'DTNTR';
SELECT customers.cust_id, orders.order_num
FROM customers RIGHT JOIN orders
ON orders.cust_id = customers.cust_id;
SELECT customers.cust_name,
customers.cust_id,
COUNT(orders.order_num) AS num_ord
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id
chapter17 组合查询
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001, 1002);
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001, 1002)
ORDER BY vend_id, prod_price;
chapter18 启用全文本搜索
create TABLE products
(
note_id int NOt NULL AUTO_INCREMENT,
note_text text NULL,
FULLETXY(note_text),
)ENGINE=MyISAM
SELECT note_text
FROM productnotes
WHERE MATCH(note_text) Against('rabbit');
chapter19 插入数据
chapter20 更新数据
UPDATE customers
SET cust_email = 'elmer@fudd.com'
WHERE cust_id = 1005;
UPDATE customers
SET cust_email = 'elmer@fudd.com',
cust_name = 'The Fudds'
WHERE cust_id = 1005;
DELETE FROM customers
WHERE cust_id = 10006;
chapter21 创建和操纵表
CREATE TABLE customers1
(
cust_id int NOT NULL AUTO_INCREMENT,
cust_name CHAR(50) NOT NULL,
cust_address CHAR(50) NULL,
cust_cisty CHAR(50) NULL,
cust_state CHAR(5) NULL,
cust_zip CHAR(10) NULL,
PRIMARY KEY(cust_id)
)ENGINE=INNODB;
PRIMARY KEY(cust_id, cust_name)
引擎类型
- InnotDB:一个可靠地事务处理引擎,不支持全文本搜索
- MEMORY:类似于MyISAM,但是数据存储在内存而不是磁盘中,因此速度很快,可以当做临时表
- MyISAM:是一个性能极高的引擎,支持全文本搜索,但不支持事务处理
更新表
ALTER TABLE vendors
ADD vend_phone CHAR(20);
ALTER TABLE vendors
DROP COLUMN vend_phone;
ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num)
REFERENCES products (prod_id);
DROP TABLE customers1;
RENAME TABLE customers1 TO customers;
chapter22 使用视图
CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact, prod_id
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num;
SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';
chapter23 使用存储过程
CREATE PROCEDURE productpricing()
BEGIN
SELECT AVG(prod_price) AS priceaverage
FROM products;
END;
CALL productpricing();
DROP PROCEDURE productpricing;
CREATE PROCEDURE producrpricing(
OUT pl DECIMAL(8.2),
OUT ph DECIMAL(8.2),
OUT pa DECIMAL(8.2)
)
BEGIN
SELECT MIN(prod_price)
INTO pl
FROM products;
SELECT MAX(prod_price)
INTO ph
FROM products;
SELECT AVG(prod_price)
INTO pa
FROM products;
END;
CALL producrpricing(@pricelow,
@pricehigh,
@priceaverage);
SELECT @pricehigh, @pricelow, @priceaverage;
CREATE PROCEDURE ordertotal(
IN onumber INT,
OUT ototal DECIMAL(8,2)
)
BEGIN
SELECT SUM(item_price*quantity)
FROM orderitems
WHERE order_num = onumber
INTO ototal;
END;
CALL ordertotal(20005, @total);
SELECT @total;
chapter24 游标
CREATE PROCEDURE processorder()
BEGIN
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
END;
OPEN ordernumbers;
CLOSE ordernumbers;
CREATE PROCEDURE processorder()
BEGIN
DECLARE o INT;
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
OPEN ordernumbers;
FETCH ordernumbers INTO o;
CLOSE ordernumbers;
END;
chapter25 触发器
- 每个表最多支持6个触发器(before/after insert/delete/update)
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added' INTO @asd;
INSERT INTO products
VALUES ('ANV05', 1001, '.5 ton wsz', 6.0, '.5 ton anvil, black, complete with handy hook');
SELECT @asd;
chapter26 管理事务处理
SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK
SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM orderitems WHERE order_num = '20009';
DELETE FROM orders WHERE order_num = '20009';
COMMIT
SAVEPOINT delete1;
ROLLBACK delete1;