MySql必知必会

初始化数据库

创建数据库

CREATE DATABASE crashcourse;

创建表

########################################
# MySQL Crash Course
# http://www.forta.com/books/0672327120/
# Example table creation scripts
########################################


########################
# Create customers table
########################
CREATE TABLE customers
(
  cust_id      int       NOT NULL AUTO_INCREMENT,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL ,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

#########################
# Create orderitems table
#########################
CREATE TABLE orderitems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL ,
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;


#####################
# Create orders table
#####################
CREATE TABLE orders
(
  order_num  int      NOT NULL AUTO_INCREMENT,
  order_date datetime NOT NULL ,
  cust_id    int      NOT NULL ,
  PRIMARY KEY (order_num)
) ENGINE=InnoDB;

#######################
# Create products table
#######################
CREATE TABLE products
(
  prod_id    char(10)      NOT NULL,
  vend_id    int           NOT NULL ,
  prod_name  char(255)     NOT NULL ,
  prod_price decimal(8,2)  NOT NULL ,
  prod_desc  text          NULL ,
  PRIMARY KEY(prod_id)
) ENGINE=InnoDB;

######################
# Create vendors table
######################
CREATE TABLE vendors
(
  vend_id      int      NOT NULL AUTO_INCREMENT,
  vend_name    char(50) NOT NULL ,
  vend_address char(50) NULL ,
  vend_city    char(50) NULL ,
  vend_state   char(5)  NULL ,
  vend_zip     char(10) NULL ,
  vend_country char(50) NULL ,
  PRIMARY KEY (vend_id)
) ENGINE=InnoDB;

###########################
# Create productnotes table
###########################
CREATE TABLE productnotes
(
  note_id    int           NOT NULL AUTO_INCREMENT,
  prod_id    char(10)      NOT NULL,
  note_date datetime       NOT NULL,
  note_text  text          NULL ,
  PRIMARY KEY(note_id),
  FULLTEXT(note_text)
) ENGINE=MyISAM;


#####################
# Define foreign keys
#####################
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

插入数据

########################################
# MySQL Crash Course
# http://www.forta.com/books/0672327120/
# Example table population scripts
########################################


##########################
# Populate customers table
##########################
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'rabbit@wascally.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', 'sam@yosemite.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd');


########################
# Populate vendors table
########################
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1001,'Anvils R Us','123 Main Street','Southfield','MI','48075', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1002,'LT Supplies','500 Park Street','Anytown','OH','44333', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1003,'ACME','555 High Street','Los Angeles','CA','90046', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1004,'Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1005,'Jet Set','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1006,'Jouets Et Ours','1 Rue Amusement','Paris', NULL,'45678', 'France');


#########################
# Populate products table
#########################
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV01', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV02', 1001, '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV03', 1001, '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('OL1', 1002, 'Oil can', 8.99, 'Oil can, red');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FU1', 1002, 'Fuses', 3.42, '1 dozen, extra long');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SLING', 1003, 'Sling', 4.49, 'Sling, one size fits all');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT1', 1003, 'TNT (1 stick)', 2.50, 'TNT, red, single stick');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT2', 1003, 'TNT (5 sticks)', 10, 'TNT, red, pack of 10 sticks');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FB', 1003, 'Bird seed', 10, 'Large bag (suitable for road runners)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FC', 1003, 'Carrots', 2.50, 'Carrots (rabbit hunting season only)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SAFE', 1003, 'Safe', 50, 'Safe with combination lock');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('DTNTR', 1003, 'Detonator', 13, 'Detonator (plunger powered), fuses not included');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP1000', 1005, 'JetPack 1000', 35, 'JetPack 1000, intended for single use');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP2000', 1005, 'JetPack 2000', 55, 'JetPack 2000, multi-use');



#######################
# Populate orders table
#######################
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20005, '2005-09-01', 10001);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20006, '2005-09-12', 10003);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20007, '2005-09-30', 10004);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20008, '2005-10-03', 10005);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20009, '2005-10-08', 10001);


###########################
# Populate orderitems table
###########################
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, 'ANV01', 10, 5.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, 'ANV02', 3, 9.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 3, 'TNT2', 5, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 4, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, 'JP2000', 1, 55);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, 'TNT2', 100, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, 'FC', 50, 2.50);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, 'OL1', 1, 8.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, 'SLING', 1, 4.49);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 4, 'ANV03', 1, 14.99);

#############################
# Populate productnotes table
#############################
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(101, 'TNT2', '2005-08-17',
'Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(102, 'OL1', '2005-08-18',
'Can shipped full, refills not available.
Need to order new can if refill needed.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(103, 'SAFE', '2005-08-18',
'Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(104, 'FC', '2005-08-19',
'Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(105, 'TNT2', '2005-08-20',
'Included fuses are short and have been known to detonate too quickly for some customers.
Longer fuses are available (item FU1) and should be recommended.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(106, 'TNT2', '2005-08-22',
'Matches not included, recommend purchase of matches or detonator (item DTNTR).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(107, 'SAFE', '2005-08-23',
'Please note that no returns will be accepted if safe opened using explosives.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(108, 'ANV01', '2005-08-25',
'Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(109, 'ANV03', '2005-09-01',
'Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(110, 'FC', '2005-09-01',
'Customer complaint: rabbit has been able to detect trap, food apparently less effective now.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(111, 'SLING', '2005-09-02',
'Shipped unassembled, requires common tools (including oversized hammer).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(112, 'SAFE', '2005-09-02',
'Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(113, 'ANV01', '2005-09-05',
'Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(114, 'SAFE', '2005-09-07',
'Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.
Comment forwarded to vendor.'
);

显示信息

# 显示所有表
SHOW TABLES;

# 显示表的列信息
SHOW COLUMNS FROM customers;
DESCRIBE customers;
DESC customers;

# 显示广泛的服务器状态信息
SHOW STATUS;

# 显示授予用户的安全权限
SHOW GRANTS;

# 显示服务器错误
SHOW ERRORS;
# 显示服务器警告
SHOW WARNINGS;

第4章检索数据

# 检索单个列
SELECT prod_name
FROM products;

# 检索多个列
SELECT prod_id, prod_name, prod_price
FROM products;

# 检索所有列
SELECT *
FROM products;

# 检索不同的行
SELECT vend_id
FROM products;
# 使用 DISTINCT 关键字
SELECT DISTINCT vend_id
FROM products;

# 限制结果
# LIMIT rowCount;
SELECT prod_name
FROM products
LIMIT 5;
# LIMIT startIndex, rowCount;
SELECT prod_name
FROM products
LIMIT 3,4;
# 与上面同义
SELECT prod_name
FROM products
LIMIT 4 OFFSET 3;

# 使用完全限定的表名和列名
SELECT products.prod_name
FROM crashcourse.products;

第5章排序检索数据

# 排序数据
SELECT products.prod_name
FROM products;
# 使用 ORDER BY 默认升序
SELECT products.prod_name
FROM products
ORDER BY products.prod_name;

# 按多个列排序
SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price, prod_name;

# 指定排序方向
SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price DESC;
# 价格降序，名字升序
SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price DESC, prod_name;
# 输出最高价格
SELECT prod_price
FROM products
ORDER BY prod_price DESC
LIMIT 1;

第6章过滤数据

# 使用 WHERE 子句
SELECT prod_name, prod_price
FROM products
WHERE prod_price=2.50;

# WHERE子句操作符
# = 等于
# <> 不等于
# != 不等于
# < 小于
# <= 小于等于
# > 大于
# >= 大于等于
# BETWEEN 在指定的两个值之间

# 检查单个值
SELECT prod_name, prod_price
FROM products
WHERE prod_name = 'fuses';

SELECT prod_name, prod_price
FROM products
WHERE prod_price < 10;

SELECT prod_name, prod_price
FROM products
WHERE prod_price >= 10;

# 不匹配检查
SELECT vend_id, prod_name
FROM products
WHERE vend_id <> 1003;

SELECT vend_id, prod_name
FROM products
WHERE vend_id != 1003;

# 范围值检查
SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;

# 空值检查
SELECT prod_name
FROM products

SELECT cust_id
FROM customers
WHERE cust_email IS NULL;

第7章数据过滤

# 组合 WHERE 子句
SELECT prod_id, prod_price, prod_name
FROM products
WHERE vend_id = 1003 AND prod_price <= 10;

# OR 操作符
SELECT vend_id, prod_name, prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003;

SELECT vend_id, prod_name, prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;

SELECT vend_id, prod_name, prod_price
FROM products
WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;

# IN 操作符
SELECT vend_id, prod_name, prod_price
FROM products
WHERE vend_id IN (1002, 1003, 1004, 1005)
ORDER BY vend_id DESC;

# NOT 操作符
SELECT vend_id, prod_name, prod_price
FROM products
WHERE vend_id NOT IN (1002, 1003, 1004, 1005)
ORDER BY vend_id DESC;

第8章用通配符进行过滤

# LIKE 操作符
# % 通配符 匹配任意字符任意次数
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE 'jet%';

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '%anvil%';

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '%an%l%';

# _ 通配符 匹配一个字符
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '_ ton anvil';

第9章用正则表达式进行搜索

# 使用 REGEXP 关键字

# 基本字符匹配
# LIKE 与 REGEXP 的区别
# LIKE 匹配整个列，REGEXP 在列值中进行匹配
SELECT prod_name
FROM products
WHERE prod_name LIKE '1000'
ORDER BY prod_name;

SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000'
ORDER BY prod_name;

# . 匹配任意一个字符
SELECT prod_name
FROM products
WHERE prod_name REGEXP '.000'
ORDER BY prod_name;

# 进行 OR 匹配
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000|2000'
ORDER BY prod_name;

# 匹配几个字符之一，[123]表示匹配1或2或3
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[123] ton'
ORDER BY prod_name;
# 1或2或3 ton
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1|2|3 ton'
ORDER BY prod_name;
# 非123
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[^123] ton'
ORDER BY prod_name;

# 匹配范围
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[1-5] ton'
ORDER BY prod_name;

# 匹配特殊字符
SELECT prod_name
FROM products
WHERE prod_name REGEXP '.'
ORDER BY prod_name;
# 使用 \\ 转义
SELECT prod_name
FROM products
WHERE prod_name REGEXP '\\.'
ORDER BY prod_name;

# 匹配字符类
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[:upper:]'
ORDER BY prod_name;

# 匹配多个实例
SELECT prod_name
FROM products
WHERE prod_name REGEXP '\\([0-9] sticks?\\)'
ORDER BY prod_name;

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[:digit:]{4}'
ORDER BY prod_name;

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[0-9][0-9][0-9][0-9]'
ORDER BY prod_name;

# 定位符
# ^ 文本开头
# $ 文本结尾
# [[:<:]] 词的开头
# [[:>:]] 词的结尾
SELECT prod_name
FROM products
WHERE prod_name REGEXP '^[0-9\\.]'
ORDER BY prod_name;

# 简单测试正则表达式
SELECT 'hello' REGEXP '[0-9]';

第10章创建计算字段

# 拼接字段
# concat
SELECT CONCAT(vend_name, '(', vend_country, ')')
FROM vendors
ORDER BY vend_name;
# RTRIM(str) 删除数据右侧多余的空格
SELECT CONCAT(RTRIM(vend_name), '(', RTRIM(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
FROM orderitems
WHERE order_num = 20005;

SELECT prod_id, quantity, item_price, quantity * item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005;

# 查看时间
SELECT NOW();

第11章使用数据处理函数

# 文本处理函数
SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name;

# SOUNDEX(str) 匹配所有发音类似的
SELECT cust_name, cust_contact
FROM customers
WHERE cust_contact = 'Y Lie';

SELECT cust_name, cust_contact
FROM customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Y Lie');

# 日期和时间处理函数
SELECT cust_id, order_num, order_date
FROM orders
WHERE DATE(order_date) BETWEEN '2005-09-01' AND '2005-09-30';

SELECT cust_id, order_num, order_date
FROM orders
WHERE YEAR(order_date) = 2005 AND MONTH(order_date) = 9;

# 数值处理函数
SELECT PI();
SELECT ABS(-3.4);

第12章汇总数据

# 聚集函数

# avg()
SELECT AVG(prod_price) as avg_price
FROM products;

# count()
SELECT COUNT(*) 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;

SELECT SUM(item_price*quantity) AS total_price
FROM orderitems
WHERE order_num = 20005;

# 聚集不同值
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;

第13章分组数据

# 数据分组
SELECT COUNT(*) AS num_prods
FROM products
WHERE vend_id = 1003;

# 创建分组
SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id WITH ROLLUP;

# 过滤分组
SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2;

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id
HAVING COUNT(*) >= 2;

# 分组与排序
SELECT order_num, SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 50;

SELECT order_num, SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 50
ORDER BY ordertotal;

第14章使用子查询

SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
										FROM orderitems
										WHERE prod_id = 'TNT2');

# 多重嵌套
SELECT cust_name, cust_contact
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,
			 cust_state,
			 (SELECT COUNT(*)
			  FROM orders
				WHERE orders.cust_id = customers.cust_id) AS order_count
FROM customers
ORDER BY cust_name;

SELECT cust_name,
			 cust_state,
			 (SELECT COUNT(*)
			  FROM orders
				WHERE cust_id = cust_id) AS order_count
FROM customers
ORDER BY cust_name;

第15章联结表

# 创建联结
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, products
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;

# 联结多个表
SELECT prod_name, vend_name, prod_price, quantity
FROM orderitems, products, vendors
WHERE products.vend_id = vendors.vend_id
	AND orderitems.prod_id = products.prod_id
	AND order_num = 20005;

第16章创建高级联结

# 使用不同类型的联结
# 自联结
SELECT prod_id, prod_name
FROM products
WHERE vend_id = (
	SELECT vend_id
	FROM products
	WHERE prod_id = 'DTNTR');

SELECT p1.prod_id, p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
	AND p2.vend_id = 'DTNTR';

# 外部联结
# 左外联结
SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;
# 右外联结
SELECT customers.cust_id, orders.order_num
FROM customers RIGHT OUTER JOIN orders
ON customers.cust_id = orders.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;

SELECT customers.cust_name,
			 customers.cust_id,
			 COUNT(orders.order_num) AS num_ord
FROM customers LEFT JOIN orders
	ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

第17章组合查询

# 使用 UNION 自动删除重复行
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5;

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);

# UNION ALL 保留重复行
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION ALL
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;

第18章全文本搜索

# 使用全文本搜索
# MATCH() 指定被搜索的列
# AGAINST() 指定要使用的搜索表达式
SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('rabbit');
# 文本中词靠前的行的等级值比词靠后的行的等级值高
SELECT note_text,
			 MATCH(note_text) AGAINST('rabbit') AS rank_num
FROM productnotes;

# 使用扩展查询
SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('anvils');

SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('anvils' WITH QUERY EXPANSION);

# 布尔文本搜索
SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('heavy' IN BOOLEAN MODE); # 包含 heavy

SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('heavy -rope*' IN BOOLEAN MODE); # 包含 heavy 但不包含以 rope开头的单词

SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('+rabbit +bait' IN BOOLEAN MODE); # 包含 rabbit 和 bait

SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('"rabbit bait"' IN BOOLEAN MODE); # 匹配短语 rabbit bait

SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('>rabbit <carrot' IN BOOLEAN MODE); # 匹配 rabbit 和 carrot，增加前者等级，降低后者等级

SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('>safe +(<conbination)' IN BOOLEAN MODE); # 匹配 safe 和 conbination，降低后者等级

第19章插入数据

# 插入完整行
INSERT INTO customers(cust_name,
	cust_contact,
	cust_email,
	cust_address,
	cust_city,
	cust_state,
	cust_zip,
	cust_country)
VALUES('Pep E. LaPew',
	NULL,
	NULL,
	'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'
			),
			(
				'M. Martain',
				'42 Galaxy Way',
				'New York',
				'NY',
				'11213',
				'USA');
				
# 插入检索出的数据
INSERT INTO customers(
	cust_id,
	cust_email,
	cust_name,
	cust_address,
	cust_city,
	cust_state,
	cust_zip,
	cust_country)
SELECT
	cust_id,
	cust_email,
	cust_name,
	cust_address,
	cust_city,
	cust_state,
	cust_zip,
	cust_country
FROM custnew;

第20章更新和删除数据

# 更新数据

# 更新单个列
UPDATE customers
SET cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;

# 更新多行
UPDATE customers
SET cust_name = 'The Fudds',
		cust_email = 'elemer@fudd.com'
WHERE cust_id = 10005;

# 删除数据
DELETE FROM customers
WHERE cust_id = 10006;

第21章创建和操纵表

# 创建表
CREATE TABLE customers
(
  cust_id      int       NOT NULL AUTO_INCREMENT,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL ,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

# 更新表
ALTER TABLE vendors
ADD vend_phone CHAR(20);

# 删除表
DROP TABLE customers2;

# 重命名表
RENAME TABLE customers2 TO customer;

RENAME TABLE backup_customers TO customers,
	backup_vendors TO vendors;

第22章使用视图

# 创建视图 CREATE VIEW view_name
# 删除视图 DROP VIEW view_name
# 查看创建视图 SHOW CREATE VIEW view_name
# 更新视图 先 DROP 再 CREATE	或者 CREATE OR REPLACE VIEW view_name

# 创建视图
CREATE VIEW customerrmaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;

SELECT * FROM customerrmaillist;

# 使用视图计算字段
SELECT prod_id,
			 quantity,
			 item_price,
			 quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005;

CREATE VIEW orderitemsexpanded AS
SELECT order_num,
			 prod_id,
			 quantity,
			 item_price,
			 quantity*item_price AS expanded_price
FROM orderitems;

SELECT *
FROM orderitemsexpanded
WHERE order_num = 20005;

第23章使用存储过程

# 创建存储过程
CREATE PROCEDURE productpricing()
BEGIN
	SELECT AVG(prod_price) AS priceaverage
	FROM products;
END;

# 调用存储过程
CALL productpricing();

# 删除存储过程
DROP PROCEDURE productpricing;

# 使用参数
CREATE PROCEDURE productpricing(
	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 productpricing(@pricelow, @pricehigh, @priceaverage);
SELECT @pricelow, @pricehigh, @priceaverage;

CREATE PROCEDURE ordertotal(
	IN inumber INT,
	OUT ototal DECIMAL(8,2)
)
BEGIN
	SELECT SUM(item_price*quantity)
	FROM orderitems
	WHERE order_num = inumber
	INTO ototal;
END;

CALL ordertotal(20005, @total);
SELECT @total;

# 建立智能存储过程
CREATE PROCEDURE ordertotal(
	IN onumber INT,
	IN taxable boolean,
	OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
	-- Declare variable for total
	DECLARE total DECIMAL(8,2);
	-- Devlare tax percentage
	DECLARE taxrate INT DEFAULT 6;
	
	-- Get the order total
	SELECT SUM(item_price*quantity)
	FROM orderitems
	WHERE order_num = onumber
	INTO total;
	
	-- Is this taxable?
	IF taxable THEN
		-- Yes, so add taxrate to the total
		SELECT total+(total/100*taxrate) INTO total;
	END IF;
	
	-- And finally, save to out variable
	SELECT total INTO ototal;
	
END;

CALL ordertotal(20005, 0, @total);
SELECT @total;
CALL ordertotal(20005, 1, @total);
SELECT @total;

# 显示存储过程信息
SHOW CREATE PROCEDURE ordertotal;
SHOW PROCEDURE STATUS like 'ordertotal';

第24章使用游标

CREATE PROCEDURE processorders()
BEGIN
	-- Declare local variables
	DECLARE done boolean DEFAULT 0;
	DECLARE o INT;
	DECLARE t DECIMAL(8,2);
	
	-- Declare the cursor
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	
	-- Declare continue handler
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
	
	-- Create a table to store the results
	CREATE TABLE IF NOT EXISTS ordertotals
		(order_num INT, total DECIMAL(8,2));
		
	-- Open the cursor
	OPEN ordernumbers;
	
	-- Loop through all rows
	REPEAT
	
		-- Get order number
		FETCH ordernumbers INTO o;
		
		-- Get the total for this order
		CALL ordertotal(o, 1, t);
		
		-- Insert order and total into ordertotals
		INSERT into ordertotals(order_num, total)
		VALUES(o, t);
		
		-- End of loop
		UNTIL done END REPEAT;
		
	-- Close the cursor
	CLOSE ordernumbers;

END;

CALL processorders();

SELECT *
FROM ordertotals;

第25章使用触发器

# 创建触发器
CREATE TRIGGER newproduct AFTER INSERT ON product
FOR EACH ROW SELECT 'Product added';

# 删除触发器
DROP TRIGGER newproduct;

CREATE TRIGGER newproduct AFTER INSERT ON product
FOR EACH ROW SELECT NEW.order_num;

#####################
# Create orders table
#####################
CREATE TABLE archive_orders
(
  order_num  int      NOT NULL AUTO_INCREMENT,
  order_date datetime NOT NULL ,
  cust_id    int      NOT NULL ,
  PRIMARY KEY (order_num)
) ENGINE=InnoDB;
# DELETE 触发器
CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
	INSERT INTO archive_orders(order_num, order_date, cust_id)
	VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END;

SELECT * FROM orders;

INSERT INTO orders(
	order_num,
	order_date,
	cust_id
)
VALUES(
	20002,
	'2005-09-02',
	10001);

DELETE FROM orders WHERE order_num = 20002;

SELECT * FROM archive_orders;

# UPDATE 触发器
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = UPPER(NEW.vend_state);

SELECT * FROM vendors;

UPDATE vendors
SET vend_state = 'ca'
WHERE vend_id = 1005;

SELECT * FROM vendors;

第26章管理事务处理

# 使用 ROLLBACK
SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;

# 使用 COMMIT
START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20006;
DELETE FROM orders WHERE order_num = 20006;
COMMIT;

第27章全球化和本地化

# 字符集和校对顺序
SHOW CHARACTER SET;

# 查看所支持校对的完整列表
SHOW COLLATION;

# 查看字符集和校对
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';

# 给表指定字符集和校对
CREATE TABLE mytable
(
	column1 INT,
	column2 VARCHAR(10)
) DEFAULT CHARACTER SET hebrew
	COLLATE hebrew_general_ci;

# 给表和特定列指定字符集和校对
CREATE TABLE mytable2
(
	column1 INT,
	column2 VARCHAR(10),
	column3 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci
) DEFAULT CHARACTER SET hebrew
	COLLATE hebrew_general_ci;

# SELECT 指定校对
SELECT * FROM customers
ORDER BY customers.cust_name COLLATE latin1_general_cs;

第28章安全管理

# 查看用户
USE mysql;
SELECT `User` FROM `user`;

# 创建用户账号
CREATE USER ben IDENTIFIED BY 'ben';

# 重命名用户账号
RENAME USER ben to bforta;

# 设置访问权限
SHOW GRANTS FOR bforta;

# 给用户授权(对crashcourse数据库所有表的查询权限)
GRANT SELECT ON crashcourse.* TO bforta;

# 收回权限
REVOKE SELECT ON crashcourse.* FROM bforta;

# 简化多次授权
GRANT SELECT, INSERT ON crashcourse.* TO bforta;

# 更改口令
SET PASSWORD FOR bforta = PASSWORD('666');

第29章数据库维护

# 检查表键
ANALYZE TABLE orders;


CHECK TABLE orders, orderitems;

posted @ 2022-10-17 10:20 keepCoding_cn 阅读(8) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· Mysql必知必会

· 牛客网sql必知必会，建表语句。

· 【SQL必知必会】-12月

· 《MySQL 必知必会》学习笔记

· sql必知必会的例子创建(sqlite3)

阅读排行：
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· Manus爆火，是硬核还是营销？
· 终于写完轮子一部分：tcp代理了，记录一下
· 别再用vector＜bool＞了！Google高级工程师：这可能是STL最大的设计失误
· 单元测试从入门到精通

公告

昵称： keepCoding_cn
园龄： 4年3个月
粉丝： 0
关注： 0

+加关注

2025年3月

日

一

二

三

四

五

六

MySql必知必会

MySql必知必会

初始化数据库

创建数据库

创建表

插入数据

显示信息

第4章 检索数据

第5章 排序检索数据

第6章 过滤数据

第7章 数据过滤

第8章 用通配符进行过滤

第9章 用正则表达式进行搜索

第10章 创建计算字段

第11章 使用数据处理函数

第12章 汇总数据

第13章 分组数据

第14章 使用子查询

第15章 联结表

第16章 创建高级联结

第17章 组合查询

第18章 全文本搜索

第19章 插入数据

第20章 更新和删除数据

第21章 创建和操纵表

第22章 使用视图

第23章 使用存储过程

第24章 使用游标

第25章 使用触发器

第26章 管理事务处理

第27章 全球化和本地化

第28章 安全管理

第29章 数据库维护

公告

搜索

常用链接

随笔分类

随笔档案

文章分类

阅读排行榜

第4章检索数据

第5章排序检索数据

第6章过滤数据

第7章数据过滤

第8章用通配符进行过滤

第9章用正则表达式进行搜索

第10章创建计算字段

第11章使用数据处理函数

第12章汇总数据

第13章分组数据

第14章使用子查询

第15章联结表

第16章创建高级联结

第17章组合查询

第18章全文本搜索

第19章插入数据

第20章更新和删除数据

第21章创建和操纵表

第22章使用视图

第23章使用存储过程

第24章使用游标

第25章使用触发器

第26章管理事务处理

第27章全球化和本地化

第28章安全管理

第29章数据库维护