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

分手后的思念是犯贱 2023-01-12 07:59 52阅读 0赞

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

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

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySQL必知必会3

相关 MySQL必知必会1

相关 MySQL必知必会2

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

相关 MySQL必知必会学习笔记第三章使用MySQL

相关 MySQL必知必会学习笔记第二章 MySQL简介

相关 MySQL必知必会学习笔记第一章了解SQL

相关 JSON必知必会学习笔记

相关正则表达式必知必会学习笔记

相关 MySQL必知必会学习笔记