MySQL编程基础

一使用MySQL

SHOW DATABASES;

USE db_name:

SHOW TABLES;

DESCRIBE tb_name;(SHOW COLUMNS FROM tb_name);

SHOWS TATUS;

SHOW GRANTS;

HELP SHOW;

SELECT USER();

SELECT DATABASE();

二检索数据

1普通检索

SELECT key FROM tb_name;

SELECTkey1,key2 FROM tb_name;

SELECT* FROM tb_name;

SELECT DISTINCT key FROM tb_name;

SELECT key FROM LIMIT 5;

SELECT key FROM LIMIT 5,5;(从第六行开始5行)

SELECT tb_name.key FROM db_name.tb_name

2排序检索

SELECT key FROM db_name ORDER BY keyx;

SELECT key1,key2 FROM db_name ORDER BY key1,key2

SELECT * FROM db_name ORDER BY key DESC(ASC);

SELECT * FROM db_name ORDER BY key LIMIT 1;

3过滤数据

SELECT* FROM tb_name WHERE key = value;

SELECT* FROM tb_name WHERE key BETWEEN value1 AND value2;

SELECT* FROM tb_name WHERE key IS NULL;

SELECT key FROM tb_name WHERE key1 = value1 AND(OR) key2 = value2;

SELECT* FROM tb_name WHERE (key1 = value1 OR key2 = value2) AND key =value3;

SELECT* FROM tb_name WHERE key IN (value1,value2);

SELECT* FROM tb_name WHERE key = value ORDER BY key;

SELECT key FROM tb_name WHERE key NOT IN (value1,value2);

4通配符过滤

SELECT* FROM tb_name WHERE key LIKE 'value%';

SELECT* FROM tb_name WHERE key LIKE '_ value';

有必要时候才用，在搜索的后面

5正则表达式检索

SELECT* FROM tb_name WHERE key REGEXP '1000' ORDER BY key2 LIMIT 1,1;

SELECT* FROM tb_name WHERE key REGEXP '.000';('1000|2000')('[123]value')('1|2|3

value')('[1-9]value')('\\.\\\\\[\\]\\f\\n\\r\\t\\v\\(\\)')[:alnum:][alpha][:digit:][:upper:][:lower:]

*,+,?,{n},{n,},{n,m},^,$

SELECT* FROM tb_name WHERE key REGEXP '[[:digit:]]{4}'

6创建计算字段

SELECT Concat(key1,' (',Rtrim(key2),')') AS key3 FROM tb_name;

SELECT key1,key2,key1*key2 AS key3 FROM tb_name;

7使用数据处理函数

文本处理函数:Left(),Length(),Lower(),LTrim(),Right(),RTrim(),Upper();

时间和日期处理函数:AddDate(),Date()(得到YYYY-MM-DD),Year(),Month();

SELEC Tkey FROM tb_name WHERE Date(key) = '1005-09-05'

数值处理函数:Abs(),Mod(),Pi(),Exp(),Sqrt()

8汇总数据

聚集函数:AVG(),COUNT(),MAX(),MIN(),SUM()

SELECT AVG(SIXTINCT key),MIN(key3) AS key2 FROM tb_name;

9分组数据

SELECT key_id,COUNT(*) AS key FROM tb_name GROUP BY key_id;

SELECT key_id,COUNT(*) AS key FROM tb_name GROUP BY key_id HAVING COUNT(*)>=2(HAVING

可支持所有的WHERE)

10使用子查询

SELECTkey1 FROM tb_name1 WHERE key2 IN (SELECT key2 FROM tb_name2 WHEREkey3 = value);

11联接和迪卡尔积

内联结：

SELECT vend_name,prod_name,prod_price FROM vendors,products WHERE vsndors.ven_id = peoducts.vend_id ORDER BY

vend_name,prod_name;

SELECT vend_name,prod_name,prod_price FORM vendors INNER JOIN products ON vendors.vend_id =

producs.vend_id;

SELECT vend_name,prod_name,prod_price FROM vendors,products ORDER BY vend_name,prod_name;

SELECT prod_name,vend_name,prod_name,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 customer AS c,order AS o,orderitems AS oi WHERE

c.cust_id= o.cust_id AND oi.order_num = o.cust_num AND prod_id = 'TNT2';

自联结：

SELECTp1.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 customer.cust_id,order.order_num FROM customers LEFT OUTER JOIN oeders ON

customer.cust_id= orders.cust_id;

聚集函数的联结:

SELECT customer.cust_name,customer.cust_id,COUNT(order.order_num) AS num_ord

FROM customers INNER JOIN orders ON customers.cust_id=orders.cust_id GROUP BY

customers.cust_id;(先清除和联结后再group后选择=>清楚后分成小组在来)

12组合查询

UNION(UNION ALL)

13全文本搜索

三插入数据

1普通插入(注意顺序)

INSERT INTO tb_name VALUES(NULL,'XXXX','XXXX');

INSERT INTO tb_name(key1,key2) VALUES('XXX','XXX');

INSERT INTO tb_name(key1,key2) VALUES('XXX','XXX'),VALUES('XXX','XXX');

四更新和删除数据

1更新数据

UPDATA tb_name SET key = value,key3 = value3,key4 = NULL WHERE key2 = value;

2删除数据

DELETEF ROM tb_name WHERE key = VALUE;

TRUNCATE TABLE(删除所有行)

3删除和更新原则，先用SELECT判断是否是你要处理的行；

五创建和操纵表

CREATE TABLE customer

(

cust_id int NOT NULL AUTO_INCREMENT,

cust_money int NOT NULL DEFAULT 100,

cust_name char(50) NOT NULL,

cust_address char(50) NULL,

PRIMARY KEY(cust_id);

)ENGING=InnodDB;

InnoDB事物处理型，不支持文本搜索，MyISAM支持文本搜索，不支持事物处理，是MySQL默认的。

ALTER TABLE vendorS ADD vend_phone CHAR(20);(慎用，常用来定义外建)

ALTER TABLE vendors DROP COLUMN vend_phone;

DROP TABLE tb_name;

RENAME TABLE tb_name1 TO tb_name2;

六使用视图

1视图的作用和创建规则和限制

2

CREATE VIEW productcustomers AS

SELECT cust_name,cust_contact,prod_id

FROM customer,orders,orderitrms

WHERE customers.cust_id = ORDERS.cust_id

AND orderitems.order_num = orders.order_num;

SELECT cust_name cust_contact FROM productcustomers WHERE proid_id = 'TNT2'

3 CREATE VIEW vendorlocations AS

SELECT Concat(RTrim(vend_name),' (',RTrim(vend_country),')') AS vend_title

FROM vendors ORDER BY vend_name;

SELECT* FROM vendorlocations;

CREATE VIEW customermaillist AS

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

七存储过程

1存储过程的好处和缺陷

2创建：

CREATE PROCEDURE productpricing()

BEGIN

SELECT Avg(prod_price) AS priceaverage

FROM products

END;

调用：CALL productpricing();

删除：DROP PROCEDURE productpricing；

带参数：CREATE PROCEDURE productpring(

OUTp1 DECIMAL(8,2)

OUTp2 DECIMAL(8,2)

OUTp3 DECIMAL(8,2)

)

BEGIN

SELECT Min(prod_price)

INTOp1

SELECT Max(prod_price)

INTOp2

SELECT Avg（prod_price）

INTOp3

FROM products;

END;

CALL productpricing(@pricelow

@pricehigh

@priceaverage);

SELECT@pricehigh;

带参数2：CREATE PROCEDURE ordertotal(

IN onumber INT,

OUT ototal DECIMAL(8,2)

)

BEGIN

SELECT Sum(item_price*quantity)

FROM orderitems

WHERE order_num = onumber

INTO ototal;

END;

CALL ordertotal(20005,@total);

SELECT@total;

完整过程:

--Name:ordertotal

--Parameters:onumber= order number

--taxable = 0 if not taxtable,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, optinally adding tax'

BEGIN

--Declare variable for total

DECLARE total DECIMAL(8,2);

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

SELECT total + (total/100*taxrate) INTO total;

ENDIF;

SELECT total INTO ototal;

--SELECT得到的INTO

END;

CALL ordertotal(2005,0,@total);

SELECT@total;

八使用游标

1游标的作用

2 CREATE PROCEDURE processorders()

BEGIN

DECLARE ordernumbersCURSOR

FOR

SELECT order_num FROM order;

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

OPEN ordernumbers;

FETCH ordernumbers INTO o;

UNTIL done END REPEAT;

CLOSE ordernumbers;

END;

CREATE PROCEDURE processorders()

BEGIN

--DECLARE LOCAL VARIABLES

DECLARE done BOOLEAN DEFAULT 0;

DECLARE o INT;

DECLARE t DECIMAL(8,2);

--DECLARE THE CURSOR

DECLARE ordernumber CURSOR

FOR

SELECT order_num FROM order;

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

CREATE TABLE IF NOT EXISTS ordertotals

(order_numINT,total DECIMAL(8,2));

OPEN ordernumber;

REPEAT

FETCH ordernumber INTO o;

CALL ordertotal(o,1,t);

--ITS NOT '@t'

INSERT INTO ordertotals(order_num,total)

VALUES(o,t);

UNTIL done END REPEAT;

CLOSE ordernumbers;

END;

SELECT* FROM ordertotals;

九触发器

1触发器作用

支持触发器的SQL语句：DELETE,INSERT,UPDATE

2创建触发器要有4条信息：触发器名，关联的表，相应的活动，何时触发

CREATETRIGGER newproduct AFTER INSERT ON products

FOR EACH ROW SELECT 'Product added';

--没插入一次就显示Productadded

DROP TRIGGER newproduct;

--

CREATE TRIGGER nreorder AFTER INSERT ON orders

FOR EACH ROW SELECT NEW.order_num;

INSERT INTO orders(order_date,cust_id)

VALUES(Now(),10001);

--

--

DELETE触发器可以引用OLD的虚拟表

--

十事务处理

1 transaction,rollback,commit,savepoint

2 SELECT * FROM ordertotals;

START TRANSACTION;

SELECT FROM ordertotals;

SELECT* FROM ordertotalS;

ROLLBACK;

SELECT* FROM ordertotals;

3 START TRANSACTION;

DELETE FROM oerderitems WHERE order_num =20010;

DELETE FROM orders WHERE order_num = 20010;

COMMIT

4 SAVEPOINTdelete1;

ROLLBACKTOdelete1;