mysql pk rp po_MySQL 必知必会学习笔记(常用命令一)

SHOW DATABASES;

USE LangLibCEE;

SHOW TABLES;

SHOW COLUMNS FROM customers;

DESC customers;

SHOW STATUS WHERE Variable_name LIKE '%time';

SHOW CREATE DATABASE httprunner;

SHOW CREATE TABLE t;

SHOW GRANTS;

SHOW ERRORS;

SHOW WARNINGS;

SELECT prod_name FROM products;

SELECT prod_id, prod_name, prod_price FROM products;

SELECT DISTINCT vend_id FROM products;

SELECT prod_name FROM products LIMIT 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;

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 ORDER BY prod_price DESC;

SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;

SELECT prod_name FROM products WHERE prod_price IS NULL;

SELECT cust_id FROM customers WHERE cust_email IS NULL;

SELECT prod_id,prod_price, prod_name FROM products WHERE vend_id = 1003 AND prod_price <= 10;

SELECT vend_id, 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 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 vend_id, prod_name, prod_price FROM products WHERE vend_id NOT IN (1002,1003) ORDER BY prod_name;

为什么要使用in操作符?

1.在使用长的合法选项清单时,in操作符的预发更加清楚;

2.在使用in时,计算的次序更加容易管理(因为使用的操作符更少。简化操作符);

3.in操作符一般比or操作符清单执行更快;

4.in的最大优点时可以包含其他select语句,使得能够动态地建立where子句;

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 '_ ton anvil';

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;

LIKE 匹配整个列,如果匹配的文本在列值中出现,Like将不会找到它。

REGEXP在列值内匹配

like匹配整个串,而regexp匹配子串

SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000' 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 '[^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;

* 0个或多个匹配

+ 1个或多个匹配,等于{1,}

? 0个或1个匹配,等于{0,1}

{n} n个匹配

{n,} >= n个匹配

{n,m} n到m个匹配,m不超过255

^ 文本的开始

$ 文本的结束

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 '[0-9]{4}' ORDER BY prod_name;

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

SELECT CONCAT(vend_name, ' (', vend_country, ')') AS INFO 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 expanded_price FROM orderitems WHERE order_num = 20005;

SELECT vend_name, Upper(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;

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 Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';

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 mix_price FROM products;

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;

SELECT COUNT(*) AS num_prods FROM products WHERE vend_id = 1003;

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

WHERE 过滤行,HAVING 过滤分组,HAVING支持所有WHERE 操作符

where 在数据分组前进行过滤,Having 在数据分组后进行过滤

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(*) FROM products GROUP BY vend_id HAVING COUNT(*) >= 3;

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

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 orders FROM customers ORDER BY cust_name;

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 products INNER JOIN vendors 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 Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name;

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 orders

INNER JOIN customers 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

LEFT OUTER JOIN customers 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 OUTER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id;

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 (1002,1001) UNION

SELECT vend_id, prod_id,prod_price FROM products WHERE prod_price <= 5;

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 ;

SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit');

SELECT note_text FROM productnotes WHERE Match(note_text) Against('anvils');

SELECT note_text,Match(note_text) Against('rabbit') AS rank FROM productnotes;

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

SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);

SELECT note_text FROM productnotes WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE);

INSERT INTO Customers VALUES(NULL,'Pep E. LaPew','100 Main Street','Los Angles','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);

CREATE TABLE custnew(

cust_id INT(50) NOT NULL AUTO_INCREMENT PRIMARY KEY,

cust_name CHAR(50) NOT NULL,

cust_address CHAR(50) NOT NULL,

cust_city CHAR(50) NOT NULL,

cust_state CHAR(50) NOT NULL,

cust_zip CHAR(50) NOT NULL,

cust_country CHAR(50) NOT NULL,

cust_contact CHAR(50),

cust_email CHAR(50)

);

UPDATE customers SET cust_email = 'elmer@t.com' WHERE cust_id = 10005;

UPDATE customers SET cust_name = 'The Fudds',cust_email = 'elmer@fudds.com' WHERE cust_id = 10005;

UPDATE IGNORE customers SET cust_email = NULL WHERE cust_id = 10005;

DELETE FROM customers WHERE cust_id = 10006;

CREATE TABLE customers2(

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

InnoDB 是一个可靠的事务处理引擎,不支持全文本搜索;

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 orders (order_num);

RENAME TABLE custnew TO hehe2;

DROP TABLE hehe2;

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 WHERE prod_id = 'TNT2';

SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country),')') AS vend_title FROM vendors ORDER BY vend_name;

CREATE VIEW vendorlocation AS

SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country),')') AS vend_title FROM vendors ORDER BY vend_name;

SELECT cust_id,cust_name, cust_email FROM customers WHERE cust_email IS NOT NULL;

CREATE VIEW custemaillist AS SELECT cust_id,cust_name, cust_email FROM customers WHERE cust_email IS NOT NULL;

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

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

SELECT * FROM oie2 WHERE order_num = 20005;

DELIMITER //

CREATE PROCEDURE productpricing()

BEGIN

SELECT Avg(prod_price) AS priceaverage FROM products;

END //

DELIMITER ;

CALL productpricing();

DROP PROCEDURE IF EXISTS productpricing;

CREATE PROCEDURE pp(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 pp(@pl,@ph,@pa);

SELECT @pa, @ph,@pl;

CREATE PROCEDURE ot(IN onumber INT,OUT ototal DECIMAL(8,2))

-> BEGIN

-> SELECT Sum(item_price*quantity) FROM orderitems WHERE order_num = onumber INTO ototal;

-> END //

CALL ot(20005, @total);

SHOW CREATE PROCEDURE ot;

CREATE PROCEDURE po()

-> BEGIN

-> DECLARE ordernumbers CURSOR

-> FOR

-> SELECT order_num FROM orders;

-> END//

OPEN ordernumbers;

CLOSE ordernumbers;

DELIMITER //

CREATE PROCEDURE pos()

BEGIN

DECLARE done BOOLEAN DEFAULT 0;

DECLARE o INT;

DECLARE t DECIMAL(8,2);

DECLARE ordernumbers CURSOR

FOR

SELECT order_num FROM orders;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

CREATE TABLE IF NOT EXISTS ordertotals(order_num INT, total DECIMAL(8,2));

OPEN ordernumbers;

REPEAT

FETCH ordernumbers INTO O;

CALL ordertotal(o, 1, t);

INSERT INTO ordertotals(order_num, total) VALUES(O,t);

UNTIL done END REPEAT;

CLOSE ordernumbers;

END;

DELIMITER ;

触发器只支持表,不支持视图及临时表;触发器不能更新或覆盖,只能先删除再重新创建;

在INSERT触发器代码内,可引用一个名为NEW的虚拟表,访问被插入的行;

在BEFORE INSERT触发器中,NEW中的值也可以被更新(允许更改被插入的值);

对于AUTO_INCREMENT列,NEW在INSERT执行之前包含0,在INSERT执行之后包含新的自动生成值

CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added';

DROP TRIGGER newproduct;

CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num;

事务处理是一种机制,用来管理必须成批执行的MySQL操作,以保证数据库不包含不完整的操作结果。

利用事务处理,可以保证一组操作不会中途停止,它们或者作为整体执行,或者完全不执行(除非明确指示)。

如果没有错误发生,整组语句提交给(写到)数据库表。如果发生错误,则进行回退(撤销)以恢复数据库到某个已知且安全的状态。

 事务(transaction)指一组SQL语句;

 回退(rollback)指撤销指定SQL语句的过程;

 提交(commit)指将未存储的SQL语句结果写入数据库表;

 保留点(savepoint)指事务处理中设置的临时占位符(placeholder),你可以对它发布回退(与回退整个事务处理不同)。

哪些语句可以回退? 事务处理用来管理INSERT、UPDATE和DELETE语句。你不能回退SELECT语句。(这样做也没有什么意义。)

你不能回退CREATE或DROP操作。事务处理块中可以使用这两条语句,但如果你执行回退,它们不会被撤销。

START TRANSACTION;

DELETE FROM orderitems WHERE order_num = 20008;

DELETE FROM orders WHERE order_num = 20008;

COMMIT;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值