Mysql必知必会

127 阅读3分钟

chapter4 检索数据

-- 单列检索
select prod_name from products  
-- 多列检索
SELECT prod_id, prod_name, prod_price FROM products
-- 检索所有列(通配符*)
SELECT * FROM products
-- 检索不同的行(相当于进行一次set)
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
-- 指定排序方向(默认升序ASC)
SELECT prod_id, prod_name, prod_price 
FROM products
ORDER BY prod_price DESC, prod_name
-- limit + desc找到最大的数据
SELECT prod_id, prod_name, prod_price 
FROM products
ORDER BY prod_price DESC LIMIT 1;

chapter6 过滤数据

-- where子句(=、!=、>、<、>=、 <=、between)
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 数据过滤

-- AMD操作符
SELECT prod_id, prod_name, prod_price 
FROM products
WHERE vend_id = 1003 AND prod_price <= 10;
-- OR操作符
SELECT prod_id, prod_name, prod_price 
FROM products
WHERE vend_id = 1003 OR prod_price <= 10;
-- IN操作符(相当于多个or)
SELECT prod_id, prod_name, prod_price, vend_id 
FROM products
WHERE vend_id IN (1002, 1003);
-- NOT操作符
SELECT prod_id, prod_name, prod_price, vend_id 
FROM products
WHERE vend_id NOT IN (1002, 1003);

chapter8 用通配符进行过滤

-- Like操作符
---- %通配符(以Jet开头)
SELECT prod_id, prod_name, prod_price, vend_id 
FROM products
WHERE prod_name LIKE 'Jet%';
---- (任何位置包含anv)
SELECT prod_id, prod_name, prod_price, vend_id 
FROM products
WHERE prod_name LIKE '%anv%';
---- (s起头,e结尾)
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 创建计算字段

-- 拼接(Conacat函数)
SELECT CONCAT(vend_name,'(',vend_country, ')')
FROM vendors
ORDER BY vend_name;
-- 使用别名(AS)
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
-- 过滤分组
---- 对cust_id分组, 找出count 大于等于2
SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2
---- 对所有price大于等于10的vend_id分组,找出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';
-- 外部联结(left/right outer join)
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 组合查询

-- 使用union
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 启用全文本搜索

-- 在创建表的时候对列创建fulltext索引
create TABLE products
(
  note_id int NOt NULL AUTO_INCREMENT,
  note_text text NULL,
  FULLETXY(note_text),
)ENGINE=MyISAM
-- 进行全文搜索(match指定列,against指定表达式)
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;
-- 多列作为主键(只能使用NOT NULL的列)
PRIMARY KEY(cust_id, cust_name)
-- INCREMENT(每个表只能有一个,且被索引)
-- 指定默认值

引擎类型

  1. InnotDB:一个可靠地事务处理引擎,不支持全文本搜索
  2. MEMORY:类似于MyISAM,但是数据存储在内存而不是磁盘中,因此速度很快,可以当做临时表
  1. 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;
-- In(传入存储过程)和OUT(从存储过程返回合计)
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 游标

-- 创建游标(使用DECLARE声明)
CREATE PROCEDURE processorder()
BEGIN
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
END;
-- 打开游标
OPEN ordernumbers;
-- 关闭游标
CLOSE ordernumbers;
-- 使用游标数据(使用fetch检索当前行的order_num列 到 一个名为o的局部变量中)
CREATE PROCEDURE processorder()
BEGIN
	-- DECLARE local variable
	DECLARE o INT;
	-- DECLARE the cursor
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	
	-- OPEN the CURSOR
	OPEN ordernumbers;
	
	-- get order number
	FETCH ordernumbers INTO o;
	
	-- CLOSE the CURSOR
	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 管理事务处理

-- 使用rollback(update、delete、insert)
SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK
SELECT * FROM ordertotals;
-- 使用commit(全部成功才会进行提交)
START TRANSACTION;
DELETE FROM orderitems WHERE order_num = '20009';
DELETE FROM orders WHERE order_num = '20009';
COMMIT
-- 使用保留点
SAVEPOINT delete1;
ROLLBACK delete1;