MySQL学习笔记
Published Jun 13, 2020
MySQL Crash Course
生产者
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 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;
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);
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;
消费者
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 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;
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
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;
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);
mysql学习笔记
mysql -h localhost -P3306 -u root -p123456
;
\g
help
\h
\c
help select
quit
exit
3
CREATE DATABASE crashcourse;
USE crashcourse;
SOURCE /root/create.sql
SOURCE /root/populate.sql
SHOW DATABASES;
SHOW TABLES;
SHOW COLUMNS FROM customers;
=DESCRIBE customers;
SHOW STATUS;
SHOW CREATE DATABASE crashcourse;
SHOW CREATE TABLE customers;
SHOW GRANTS;
SHOW ERRORS;
SHOW WARNINGS;
HELP SHOW;
4
SELECT prod_name FROM products;
SELECT prod_id, prod_name, prod_price FROM products;
SELECT * FROM products;
SELECT vend_id FROM products;
SELECT DISTINCT vend_id FROM products;
SELECT DISTINCT vend_id, prod_price FROM products;
SELECT prod_name FROM products LIMIT 5;
SELECT prod_name FROM products LIMIT 5, 5;
SELECT prod_name FROM products LIMIT 3, 4;
=SELECT prod_name FROM products LIMIT 4 OFFSET 3;
SELECT products.prod_name FROM products;
=SELECT products.prod_name FROM crashcourse.products;
=SELECT crashcourse.products.prod_name FROM crashcourse.products;
5
SELECT prod_name FROM products ORDER BY 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
SELECT prod_name, prod_price FROM products WHERE prod_price = 2.50;
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, prod_price FROM products WHERE prod_price <= 10 AND prod_price >= 5;
SELECT prod_name FROM products WHERE prod_price IS NULL;
SELECT cust_id FROM customers WHERE cust_email IS NULL;
7
SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id = 1003 AND prod_price <= 10;
SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003;
SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;
SELECT prod_name, prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;
SELECT prod_name, prod_price FROM products WHERE vend_id IN (1002, 1003) ORDER BY prod_name;
=SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 ORDER BY prod_name;
SELECT prod_name, prod_price FROM products WHERE vend_id NOT IN (1002, 1003) ORDER BY prod_name;
8
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_name FROM products WHERE prod_name LIKE 's%e';
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil%';
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '% ton anvil%';
9
SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;
=SELECT prod_name FROM products WHERE prod_name LIKE '%1000%' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '.000' ORDER BY prod_name;
=SELECT prod_name FROM products WHERE prod_name LIKE '%000%' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name REGEXP BINARY 'JetPack .000';
SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000';
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|2|3] Ton' ORDER BY prod_name;
!=SELECT prod_name FROM products WHERE prod_name REGEXP '1|2|3 Ton' ORDER BY prod_name;
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 vend_name FROM vendors WHERE vend_name REGEXP '.' ORDER BY vend_name;
!=SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.' ORDER BY vend_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}';
=SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]][[:digit:]][[:digit:]][[:digit:]]';
=SELECT prod_name FROM products WHERE prod_name REGEXP '[0-9][0-9][0-9][0-9]';
SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]';
!=SELECT prod_name FROM products WHERE prod_name REGEXP '[0-9\\.]';
SELECT 'hello' REGEXP '[0-9]';
10
SELECT Concat(vend_name, ' (', vend_country, ')') FROM vendors ORDER BY vend_name;
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 3*2;
SELECT Trim('abc ');
SELECT Now();
11
SELECT vend_name, Upper(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;
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 FROM orders WHERE order_date = '2005-09-01';
=SELECT cust_id, order_num FROM orders WHERE Date(order_date) = '2005-09-01';
SELECT cust_id, order_num FROM orders WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';
=SELECT cust_id, order_num FROM orders WHERE Year(order_date) = 2005 AND Month(order_date) = 9;
12
SELECT AVG(prod_price) AS avg_price FROM products;
SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003;
SELECT COUNT(*) AS num_cust FROM customers;
SELECT COUNT(cust_email) AS num_cust FROM customers;
SELECT MAX(prod_price) AS max_price FROM products;
SELECT MIN(prod_price) AS min_price FROM products;
SELECT SUM(quantity) AS item_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 WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >= 2 ORDER BY COUNT(*);
SELECT order_num FROM orderitems GROUP BY order_num;
=SELECT DISTINCT order_num FROM orderitems;
SELECT order_num, SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num;
SELECT order_num, SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING ordertotal >= 50;
SELECT order_num, SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING ordertotal >= 50 ORDER BY ordertotal;
14
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 COUNT(*) AS orders FROM orders WHERE cust_id = 10001;
SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;
!=SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE cust_id = cust_id) AS orders
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;
SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num
AND prod_id = 'TNT2';
16
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 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.prod_id = 'DTNTR';
SELECT c.*, o.order_num, o.order_date,
oi.prod_id, oi.quantity, oi.item_price
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 = 'FB';
SELECT customers.cust_id, orders.order_num
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id;
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 orders RIGHT OUTER JOIN customers
ON orders.cust_id = customers.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_id, orders.order_num
FROM orders LEFT OUTER JOIN customers
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;
=
SELECT customers.cust_name,
orders.cust_id,
COUNT(orders.order_num) AS num_ord
FROM orders INNER JOIN customers
ON orders.cust_id = customers.cust_id
GROUP BY orders.cust_id;
SELECT customers.cust_name,
customers.cust_id,
COUNT(orders.order_num) AS num_ord
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;
17
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
OR vend_id IN (1001,1002);
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 ALL
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001,1002)
ORDER BY vend_id, prod_price;
18
SELECT note_id, note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');
=SELECT note_id, note_text
FROM productnotes
WHERE note_text LIKE '%rabbit%';
SELECT note_id, note_text, Match(note_text) Against('rabbit') AS rank
FROM productnotes;
SELECT note_id, note_text
FROM productnotes
WHERE Match(note_text) Against('anvils');
SELECT note_id, note_text
FROM productnotes
WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);
SELECT note_id, note_text
FROM productnotes
WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE);
SELECT note_id, note_text
FROM productnotes
WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);
SELECT note_id, note_text
FROM productnotes
WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE);
SELECT note_id, note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE);
SELECT note_id, note_text
FROM productnotes
WHERE Match(note_text) Against('"rabbit bait"' IN BOOLEAN MODE);
SELECT note_id, note_text
FROM productnotes
WHERE Match(note_text) Against('>rabbit <carrot' IN BOOLEAN MODE);
SELECT note_id, note_text
FROM productnotes
WHERE Match(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE);
19
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,
cust_contact,
cust_email)
VALUES('Pep E. LaPew',
'100 Main Street',
'Los Angeles',
'CA',
'90046',
'USA',
NULL,
NULL);
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.Martian',
'42 Galaxy Way',
'New York',
'NY',
'11213',
'USA'
);
INSERT INTO customers(cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
SELECT cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
FROM custnew;
20
UPDATE customers
SET cust_name = 'The Fudds',
cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;
UPDATE customers
SET cust_email = NULL
WHERE cust_id = 10005;
DELETE FROM customers
WHERE cust_id = 10006;
TRUNCATE TABLE custnew;
21
CREATE TABLE IF NOT EXISTS 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;
SELECT last_insert_id();
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 TABLE orderitems
(
order_num int NOT NULL ,
order_item int NOT NULL ,
prod_id char(10) NOT NULL ,
quantity int NOT NULL DEFAULT 1,
item_price decimal(8,2) NOT NULL ,
PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;
ALTER TABLE vendors
ADD vend_phone CHAR(20);
ALTER TABLE vendors
DROP COLUMN vend_phone;
DROP TABLE custnew;
RENAME TABLE customers TO customers2;
RENAME TABLE backup_customers TO customers,
backup_vendors TO vendors,
backup_products TO products;
22
CREATE VIEW
SHOW CREATE VIEW viewname;
DROP VIEW viewname;
DROP, CREATE
=CREATE OR REPLACE VIEW
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 * FROM productcustomers;
SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';
CREATE VIEW vendorlocation AS
SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')')
AS vend_title
FROM vendors
ORDER BY vend_name;
SELECT * FROM vendorlocation;
CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;
SELECT * FROM customeremaillist;
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
DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage
FROM products;
END //
DELIMITER ;
CALL productpricing();
DROP PROCEDURE productpricing;
DROP PROCEDURE IF EXISTS productpricing;
DELIMITER //
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 //
DELIMITER ;
CALL productpricing(@pricelow,
@pricehigh,
@priceaverage);
SELECT @priceaverage;
SELECT @pricehigh, @pricelow, @priceaverage;
DELIMITER //
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 //
DELIMITER ;
CALL ordertotal(20005, @total);
SELECT @total;
DELIMITER //
-- Name: ordertotal
-- Parameters: onumber = order number
-- taxable = 0 if not taxable, 1 if taxable
-- ototal = order total variable
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);
-- Declare 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 //
DELIMITER ;
CALL ordertotal(20005, 0, @total);
SELECT @total;
CALL ordertotal(20005, 1, @total);
SELECT @total;
SHOW CREATE PROCEDURE ordertotal;
SHOW PROCEDURE STATUS;
SHOW PROCEDURE STATUS LIKE 'ordertotal';
24
DELIMITER //
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//
DELIMITER ;
CALL processorders();
SELECT *
FROM ordertotals;
25
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added' into @x;
SELECT @x;
DROP TRIGGER newproduct;
CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num into @order_num;
INSERT INTO orders(order_date, cust_id)
VALUES(Now(), 10001);
SELECT @order_num;
DELIMITER //
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//
DELIMITER ;
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);
UPDATE vendors
SET vend_state = 'xxx'
WHERE vend_id = 1005;
26
SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;
SAVEPOINT delete1;
ROLLBACK TO delete1;
RELEASE SAVEPOINT delete1;
SET autocommit = 0;
27
SHOW CHARACTER SET;
SHOW COLLATION;
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';
CREATE TABLE mytable
(
columnn1 INT,
columnn2 VARCHAR(10)
) DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci;
CREATE TABLE mytable2
(
columnn1 INT,
columnn2 VARCHAR(10),
columnn3 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci
) DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci;
SELECT * FROM customers
ORDER BY lastname, firstname COLLATE latin1_general_cs;
28
USE mysql;
SELECT User FROM user;
CREATE USER ben IDENTIFIED BY 'p@$$w0rd';
RENAME USER ben TO bforta;
DROP USER bforta;
SHOW GRANTS FOR bforta;
GRANT SELECT ON crashcourse.* TO bforta;
REVOKE SELECT ON crashcourse.* FROM bforta;
GRANT SELECT, INSERT ON crashcourse.* TO bforta;
SET PASSWORD FOR bforta = Password('n3w p@$$w0rd');
SET PASSWORD = Password('p@$$w0rd');
29
ANALYZE TABLE orders;
CHECK TABLE orders, orderitems;
REPAIR TABLE
OPTIMIZE TABLE
show VARIABLES LIKE '%dir%';
| datadir | /var/lib/mysql/ |
30
SHOW VARIABLES;
SHOW PROCESSLIST;
KILL ID;
EXPLAIN
高性能可扩展MySQL数据库架构设计与优化
ip用unsigned int存储
varchar(N)存储时是按实际长度存储的，读到内存时按N非配内存
text不能有默认值
使用预编译语句防止sql注入
a like '123%' 后置%可以利用到索引
CREATE TABLE `customer_login_log2` (
`login_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '登录日志ID',
`customer_id` int(10) unsigned NOT NULL COMMENT '登录用户ID',
`login_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '用户登录时间',
`login_ip` int(10) unsigned NOT NULL COMMENT '登录IP',
`login_type` tinyint(4) NOT NULL COMMENT '登录类型:0未成功 1成功',
PRIMARY KEY (`login_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户登录日志表'
PARTITION BY HASH(customer_id)
PARTITIONS 4;
;
/var/lib/mysql/mc_userdb/
SQLZOO 线上练习
https://sqlzoo.net/wiki/SQL_Tutorial/zh
https://github.com/codyloyd/sqlzoo-solutions/blob/master/SQLZOO_solutions.md
SELECT basics
1.
SELECT population
FROM world
WHERE name = 'Germany'
2.
SELECT name, gdp/population
FROM world
WHERE area > 5000000
3.
SELECT name, population
FROM world
WHERE name IN ('Ireland', 'Iceland', 'Denmark')
4.
SELECT name, area
FROM world
WHERE area BETWEEN 200000 AND 250000
SELECT names
1.
SELECT name
FROM world
WHERE name LIKE 'Y%'
2.
SELECT name
FROM world
WHERE name LIKE '%Y'
3.
SELECT name
FROM world
WHERE name LIKE '%x%'
4.
SELECT name
FROM world
WHERE name LIKE '%land'
5.
SELECT name
FROM world
WHERE name LIKE 'C%ia'
6.
SELECT name
FROM world
WHERE name LIKE '%oo%'
7.
SELECT name
FROM world
WHERE name LIKE '%a%a%a%'
8.
SELECT name
FROM world
WHERE name LIKE '_t%'
9.
SELECT name
FROM world
WHERE name LIKE '%o__o%'
10.
SELECT name
FROM world
WHERE name LIKE '____'
11.
SELECT name
FROM world
WHERE name = capital
12.
SELECT name
FROM world
WHERE capital = CONCAT(name, ' City')
13.
SELECT capital, name
FROM world
WHERE capital LIKE CONCAT('%', name, '%')
14.
SELECT name, capital
FROM world
WHERE capital LIKE CONCAT(name, '_%')
15.
SELECT name, REPLACE(capital, name, '')
FROM world
WHERE capital LIKE CONCAT(name, '_%')
SELECT Quiz
C E E C C C C
SELECT from WORLD Tutorial
1.
SELECT name, continent, population
FROM world
2.
SELECT name
FROM world
WHERE population >= 200000000
3.
SELECT name, gdp/population
FROM world
WHERE population >= 200000000
4.
SELECT name, population/1000000
FROM world
WHERE continent = 'South America'
5.
SELECT name, population
FROM world
WHERE name IN ('France', 'Germany', 'Italy')
6.
SELECT name
FROM world
WHERE name LIKE '%United%'
7.
SELECT name, population, area
FROM world
WHERE area > 3000000
OR population > 250000000
8.
SELECT name, population, area
FROM world
WHERE area > 3000000 AND population <= 250000000
OR area <= 3000000 AND population > 250000000
9.
SELECT name, ROUND(population / 1000000, 2), ROUND(gdp / 1000000000, 2)
FROM world
WHERE continent = 'South America'
10.
SELECT name, ROUND(gdp/population, -3)
FROM world
WHERE gdp > 1000000000000
11.
SELECT name, CASE WHEN continent = 'Oceania' THEN 'Australasia' ELSE continent END
FROM world
WHERE name LIKE 'N%'
12.
SELECT name,
CASE WHEN continent = 'Europe' OR continent = 'Asia' THEN 'Eurasia'
WHEN continent IN ('North America', 'South America', 'Caribbean') THEN 'America'
ELSE continent END
FROM world
WHERE name LIKE 'A%'
OR name LIKE 'B%'
13.
SELECT name, continent,
CASE WHEN continent = 'Oceania' THEN 'Australasia'
WHEN continent = 'Eurasia' OR name = 'Turkey' THEN 'Europe/Asia'
WHEN continent = 'Caribbean' AND name LIKE 'B%' THEN 'North America'
WHEN continent = 'Caribbean' THEN 'South America'
ELSE continent END
FROM world
BBC QUIZ
E D B D B D C
SELECT from Nobel Tutorial
1.
SELECT yr, subject, winner
FROM nobel
WHERE yr = 1950
2.
SELECT winner
FROM nobel
WHERE yr = 1962
AND subject = 'Literature'
3.
SELECT yr, subject
FROM nobel
WHERE winner = 'Albert Einstein'
4.
SELECT winner
FROM nobel
WHERE yr >= 2000
AND subject = 'Peace'
5.
SELECT yr, subject, winner
FROM nobel
WHERE yr BETWEEN 1980 AND 1989
AND subject = 'Literature'
6.
SELECT yr, subject, winner
FROM nobel
WHERE winner IN ('Theodore Roosevelt', 'Woodrow Wilson', 'Jimmy Carter')
7.
SELECT winner
FROM nobel
WHERE winner LIKE 'John%'
8.
SELECT yr, subject, winner
FROM nobel
WHERE yr = 1980 AND subject = 'Physics'
OR yr = 1984 AND subject = 'Chemistry'
9.
SELECT yr, subject, winner
FROM nobel
WHERE yr = 1980
AND subject NOT IN ('Chemistry', 'Medicine')
10.
SELECT yr, subject, winner
FROM nobel
WHERE subject = 'Medicine' AND yr < 1910
OR subject = 'Literature' AND yr >= 2004
11.
SELECT yr, subject, winner
FROM nobel
WHERE winner = 'PETER GRÜNBERG'
12.
SELECT yr, subject, winner
FROM nobel
WHERE winner = 'EUGENE O''NEILL'
13.
SELECT winner, yr, subject
FROM nobel
WHERE winner LIKE 'Sir %'
ORDER BY yr DESC, winner
14.
SELECT winner, subject
FROM nobel
WHERE yr = 1984
ORDER BY subject IN ('Physics', 'Chemistry'), subject, winner
Nobel Quiz
E C B C C C C
SELECT within SELECT Tutorial
1.
SELECT name
FROM world
WHERE population > (SELECT population FROM world WHERE name = 'Russia')
2.
SELECT name
FROM world
WHERE continent = 'Europe'
AND gdp / population > (SELECT gdp / population FROM world WHERE name = 'United Kingdom')
3.
SELECT name, continent
FROM world
WHERE continent IN (SELECT continent FROM world WHERE name IN ('Argentina', 'Australia'))
ORDER BY name
4.
SELECT name, population
FROM world
WHERE population > (SELECT population FROM world WHERE name = 'Canada')
AND population < (SELECT population FROM world WHERE name = 'Poland')
5.
SELECT name, CONCAT(ROUND(population / (SELECT population FROM world WHERE name = 'Germany') * 100, 0), '%')
FROM world
WHERE continent = 'Europe'
6.
SELECT name
FROM world
WHERE gdp > ALL (SELECT gdp FROM world WHERE gdp > 0 AND continent = 'Europe')
SELECT name
FROM world
WHERE gdp > (SELECT MAX(gdp) FROM world WHERE gdp > 0 AND continent = 'Europe')
7.
SELECT continent, name, area
FROM world AS w1
WHERE area >= ALL (SELECT area FROM world AS w2 WHERE w2.continent = w1.continent AND area > 0)
SELECT continent, name, area
FROM world AS w1
WHERE area >= (SELECT MAX(area) FROM world AS w2 WHERE w2.continent = w1.continent AND area > 0)
8.
SELECT continent, name
FROM world AS w1
WHERE name <= ALL (SELECT name FROM world AS w2 WHERE w2.continent = w1.continent)
SELECT continent, name
FROM world AS w1
WHERE name <= (SELECT MAX(name) FROM world AS w2 WHERE w2.continent = w1.continent)
9.
SELECT name, continent, population
FROM world AS w1
WHERE 25000000 >= ALL (SELECT population FROM world AS w2 WHERE w2.continent = w1.continent AND population > 0)
SELECT name, continent, population
FROM world AS w1
WHERE 25000000 >= (SELECT MAX(population) FROM world AS w2 WHERE w2.continent = w1.continent AND population > 0)
10.
SELECT name, continent
FROM world AS w1
WHERE population >= ALL
(SELECT population * 3 FROM world AS w2 WHERE w2.continent = w1.continent AND w2.name != w1.name)
SELECT name, continent
FROM world AS w1
WHERE population >=
(SELECT MAX(population) * 3 FROM world AS w2 WHERE w2.continent = w1.continent AND w2.name != w1.name)
Nested SELECT Quiz
C B A D B B B
SUM and COUNT
1.
SELECT SUM(population)
FROM world
2.
SELECT DISTINCT continent
FROM world
3.
SELECT SUM(gdp)
FROM world
WHERE continent = 'Africa'
4.
SELECT COUNT(name)
FROM world
WHERE area >= 1000000
5.
SELECT SUM(population)
FROM world
WHERE name IN ('France', 'Germany', 'Spain')
6.
SELECT continent, COUNT(name)
FROM world
GROUP BY continent
7.
SELECT continent, COUNT(name)
FROM world
WHERE population >= 10000000
GROUP BY continent
8.
SELECT continent
FROM world
GROUP BY continent
HAVING SUM(population) >= 100000000
SUM and COUNT Quiz
C A D E B E D D
The JOIN operation
1.
SELECT DISTINCT matchid, player
FROM goal
WHERE teamid = 'GER'
2.
SELECT id, stadium, team1, team2
FROM game
WHERE id = 1012
3.
SELECT player, teamid, stadium, mdate
FROM game AS g1
INNER JOIN goal AS g2
ON g1.id = g2.matchid
WHERE g2.teamid = 'GER'
4.
SELECT g1.team1, g1.team2, g2.player
FROM game AS g1
INNER JOIN goal AS g2
ON g1.id = g2.matchid
WHERE g2.player LIKE 'Mario%'
5.
SELECT g.player, g.teamid, e.coach, g.gtime
FROM eteam AS e
INNER JOIN goal AS g
ON e.id = g.teamid
WHERE g.gtime <= 10
6.
SELECT g.mdate, e.teamname
FROM eteam AS e
INNER JOIN game AS g
ON e.id = g.team1
WHERE e.coach = 'Fernando Santos'
7.
SELECT g2.player
FROM game AS g1
INNER JOIN goal AS g2
ON g1.id = g2.matchid
WHERE g1.stadium = 'National Stadium, Warsaw'
8.
SELECT DISTINCT g2.player
FROM game AS g1
INNER JOIN goal AS g2
ON g1.id = g2.matchid
WHERE g1.team1 = 'GER' AND g1.team2 = g2.teamid
OR g1.team2 = 'GER' AND g1.team1 = g2.teamid
9.
SELECT e.teamname, COUNT(*)
FROM eteam AS e
INNER JOIN goal AS g
ON e.id = g.teamid
GROUP BY e.teamname
10.
SELECT g1.stadium, COUNT(*)
FROM game AS g1
INNER JOIN goal AS g2
ON g1.id = g2.matchid
GROUP BY g1.stadium
11.
SELECT g2.matchid, g1.mdate, COUNT(*)
FROM game AS g1
INNER JOIN goal AS g2
ON g1.id = g2.matchid
WHERE g1.team1 = 'POL'
OR g1.team2 = 'POL'
GROUP BY g2.matchid, g1.mdate
12.
SELECT g2.matchid, g1.mdate, COUNT(*)
FROM game AS g1
INNER JOIN goal AS g2
ON g1.id = g2.matchid
WHERE (g1.team1 = 'GER' OR g1.team2 = 'GER')
AND g2.teamid = 'GER'
GROUP BY g2.matchid, g1.mdate
13.
SELECT g1.mdate,
g1.team1,
SUM(CASE WHEN g2.teamid = g1.team1 THEN 1 ELSE 0 END) AS score1,
g1.team2,
SUM(CASE WHEN g2.teamid = g1.team2 THEN 1 ELSE 0 END) AS score2
FROM game AS g1
LEFT JOIN goal AS g2
ON g1.id = g2.matchid
GROUP BY g1.id, g1.mdate, g1.team1, g1.team2
ORDER BY g1.mdate, g1.id, g1.team1, g1.team2
JOIN Quiz
D C A A B C B
More JOIN operations
1.
SELECT id, title
FROM movie
WHERE yr = 1962
2.
SELECT yr
FROM movie
WHERE title = 'Citizen Kane'
3.
SELECT id, title, yr
FROM movie
WHERE title LIKE 'Star Trek%'
ORDER BY yr
4.
SELECT title
FROM movie
WHERE id IN (11768, 11955, 21191)
5.
SELECT id
FROM actor
WHERE name = 'Glenn Close'
6.
SELECT id
FROM movie
WHERE title = 'Casablanca'
7.
SELECT a.name
FROM actor AS a
INNER JOIN casting AS c
ON a.id = c.actorid
WHERE c.movieid = 11768
8.
SELECT a.name
FROM casting AS c
INNER JOIN movie AS m
ON m.id = c.movieid
INNER JOIN actor AS a
ON a.id = c.actorid
WHERE m.title = 'Alien'
SELECT a.name
FROM movie AS m
INNER JOIN actor AS a
INNER JOIN casting AS c
ON m.id = c.movieid AND a.id = c.actorid
WHERE m.title = 'Alien'
9.
SELECT DISTINCT m.title
FROM casting AS c
INNER JOIN movie AS m
ON m.id = c.movieid
INNER JOIN actor AS a
ON a.id = c.actorid
WHERE a.name = 'Harrison Ford'
10.
SELECT DISTINCT m.title
FROM casting AS c
INNER JOIN movie AS m
ON m.id = c.movieid
INNER JOIN actor AS a
ON a.id = c.actorid
WHERE a.name = 'Harrison Ford'
AND c.ord != 1
11.
SELECT m.title, a.name
FROM casting AS c
INNER JOIN movie AS m
ON m.id = c.movieid
INNER JOIN actor AS a
ON a.id = c.actorid
WHERE m.yr = 1962
AND c.ord = 1
12.
SELECT m.yr, COUNT(m.id) AS count
FROM casting AS c
INNER JOIN movie AS m
ON m.id = c.movieid
INNER JOIN actor AS a
ON a.id = c.actorid
WHERE a.name = 'John Travolta'
GROUP BY m.yr
ORDER BY count DESC
LIMIT 1
13.
SELECT m.title, a.name
FROM casting AS c
INNER JOIN movie AS m
ON c.movieid = m.id
INNER JOIN actor AS a
ON c.actorid = a.id
WHERE c.movieid IN (SELECT m.id
FROM casting AS c
INNER JOIN movie AS m
ON c.movieid = m.id
INNER JOIN actor AS a
ON c.actorid = a.id
WHERE a.name = 'Julie Andrews')
AND c.ord = 1
14.
SELECT a.name
FROM actor AS a
INNER JOIN casting AS c
ON a.id = c.actorid
WHERE c.ord = 1
GROUP BY c.actorid, a.name
HAVING COUNT(*) >= 30
ORDER BY a.name
15.
SELECT m.title, COUNT(c.actorid) AS count
FROM casting AS c
INNER JOIN movie AS m
ON m.id = c.movieid
WHERE m.yr = 1978
GROUP BY c.movieid, m.title
ORDER BY count DESC
16.
SELECT DISTINCT a.name
FROM actor AS a
INNER JOIN casting AS c
ON a.id = c.actorid
WHERE c.movieid IN (SELECT c.movieid
FROM actor AS a
INNER JOIN casting AS c
ON a.id = c.actorid
WHERE a.name = 'Art Garfunkel')
AND a.name != 'Art Garfunkel'
JOIN Quiz 2
C E C B D C B
Using_Null
1.
SELECT name
FROM teacher
WHERE dept IS NULL
2.
SELECT teacher.name, dept.name
FROM teacher
INNER JOIN dept
ON teacher.dept = dept.id
3.
SELECT t.name, d.name
FROM teacher AS t
LEFT OUTER JOIN dept AS d
ON t.dept = d.id
4.
SELECT t.name, d.name
FROM dept AS d
LEFT OUTER JOIN teacher AS t
ON d.id = t.dept
5.
SELECT name, COALESCE(mobile, '07986 444 2266')
FROM teacher
6.
SELECT t.name, COALESCE(d.name, 'None')
FROM teacher AS t
LEFT OUTER JOIN dept AS d
ON t.dept = d.id
7.
SELECT COUNT(id), COUNT(mobile)
FROM teacher
8.
SELECT d.name, COUNT(t.name)
FROM teacher AS t
RIGHT OUTER JOIN dept AS d
ON t.dept = d.id
GROUP BY d.name
9.
SELECT name, CASE WHEN dept IN (1, 2) THEN 'Sci' ELSE 'Art' END
FROM teacher
10.
SELECT name, CASE WHEN dept IN (1, 2) THEN 'Sci'
WHEN dept = 3 THEN 'Art'
ELSE 'None' END
FROM teacher
Using Null Quiz
E C E B A A
Self join
1.
SELECT COUNT(*)
FROM stops
2.
SELECT id
FROM stops
WHERE name = 'Craiglockhart'
3.
SELECT s.id, s.name
FROM stops AS s
INNER JOIN route AS r
ON s.id = r.stop
WHERE r.company = 'LRT'
AND r.num = 4
ORDER BY pos
4.
SELECT company, num, COUNT(*) AS count
FROM route
WHERE stop = 149
OR stop = 53
GROUP BY company, num
HAVING count = 2
5.
SELECT r1.company, r1.num, r1.stop AS begin, r2.stop AS end
FROM route AS r1
INNER JOIN route AS r2
ON r1.company = r2.company AND r1.num = r2.num
WHERE r1.stop = 53
AND r2.stop = (SELECT id FROM stops WHERE name = 'London Road')
SELECT r1.company, r1.num, r1.stop, r2.stop
FROM route AS r1
INNER JOIN route AS r2
ON r1.company = r2.company AND r1.num = r2.num
INNER JOIN stops AS s
ON r2.stop = s.id
WHERE r1.stop = 53
AND s.name = 'London Road'
6.
SELECT r1.company, r1.num, s1.name, s2.name
FROM route AS r1
INNER JOIN route AS r2
ON r1.company = r2.company AND r1.num = r2.num
INNER JOIN stops AS s1
ON r1.stop = s1.id
INNER JOIN stops AS s2
ON r2.stop = s2.id
WHERE s1.name = 'Craiglockhart'
AND s2.name = 'London Road'
7.
SELECT DISTINCT r1.company, r1.num
FROM route AS r1
INNER JOIN route AS r2
ON r1.company = r2.company AND r1.num = r2.num
INNER JOIN stops AS s1
ON r1.stop = s1.id
INNER JOIN stops AS s2
ON r2.stop = s2.id
WHERE s1.name = 'Haymarket'
AND s2.name = 'Leith'
8.
SELECT DISTINCT r1.company, r1.num
FROM route AS r1
INNER JOIN route AS r2
ON r1.company = r2.company AND r1.num = r2.num
INNER JOIN stops AS s1
ON r1.stop = s1.id
INNER JOIN stops AS s2
ON r2.stop = s2.id
WHERE s1.name = 'Craiglockhart'
AND s2.name = 'Tollcross'
9.
SELECT DISTINCT s2.name, r1.company, r1.num
FROM route AS r1
INNER JOIN route AS r2
ON r1.company = r2.company AND r1.num = r2.num
INNER JOIN stops AS s1
ON r1.stop = s1.id
INNER JOIN stops AS s2
ON r2.stop = s2.id
WHERE s1.name = 'Craiglockhart'
AND r1.company = 'LRT'
10.
SELECT r1.num, r1.company, s2.name, r3.num, r3.company
FROM route AS r1
JOIN route AS r2
ON r1.company = r2.company AND r1.num = r2.num
JOIN route AS r3
JOIN route AS r4
ON r3.company = r4.company AND r3.num = r4.num
JOIN stops AS s1
ON r1.stop = s1.id
JOIN stops AS s2
ON r2.stop = s2.id
JOIN stops AS s3
ON r3.stop = s3.id
JOIN stops AS s4
ON r4.stop = s4.id
WHERE s1.name = 'Craiglockhart'
AND s4.name = 'Sighthill'
AND s2.name = s3.name
ORDER BY r1.company, r1.num, s2.name, r3.company, r3.num
Self join Quiz
C E D