一使用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;