数据库常用语句命令

数据库的常用语句命令

数据检索

DESC products;
#查询整个表格
SELECT * from products;
#查询一列或者多列
SELECT prod_name,prod_price,prod_id FROM products;
#限定返回行数
SELECT * from products LIMIT 3;
#分页功能
SELECT * from products LIMIT 3,3;

mysql高级数据过滤方法

#找出供应商为1003的店铺 并且 价格小于等于10
SELECT * from products WHERE vend_id = 1003 and prod_price <= 10;
#找出供应商 为1003和1002
SELECT * from products WHERE vend_id = 1002 or vend_id = 1003;
#计算次序
SELECT * from products WHERE vend_id = 1002 or (prod_price <= 10 and vend_id = 1003);
#in操作符
SELECT * from products WHERE vend_id in (1001,1002);
#not 操作符
SELECT * from products WHERE vend_id not in (1002);
#去重功能还是
SELECT distinct id from products;
#使用like操作符 进行数据的查询
#产品名字中以jet开头的数据 ,%代表的是任意的意思
SELECT * from products WHERE prod_name LIKE 'jet%' ;
#在产品名字中包含anvil的产品
SELECT * from products WHERE prod_name LIKE '%anvil%' ;
#%模糊匹配 _逐字匹配
SELECT * from products WHERE prod_name LIKE '____ s__d' ;

MySQL正则表达式的用法

#mysql 正则表达式
#1.使用正则表达式搜索相关字符串
SELECT * FROM products WHERE prod_name REGEXP '1000';
#2.匹配000相关产品的信息
SELECT * FROM products WHERE prod_name REGEXP '.000';
#3.使用or的方式匹配
SELECT * FROM products WHERE prod_name REGEXP '1000|2000';
#4.使用范围方法进行匹配
SELECT * FROM products WHERE prod_name REGEXP '[1-2]000';
#5.使用范围和or同步进行
SELECT * FROM products WHERE prod_name REGEXP '[1|2]000';
#6.排他符号
SELECT * FROM products WHERE prod_name REGEXP '[^1]000';
#7.特殊字符匹配
SELECT * FROM products WHERE prod_name REGEXP '[1-9] ton';
#8. .的使用
SELECT * FROM products WHERE prod_name REGEXP '\\.';
#9.匹配多个实例
SELECT * FROM products WHERE prod_name REGEXP 'stick?\\)';

mysql的计算字段

#字段如何拼接
ELECT vend_name,vend_country, CONCAT(vend_name,'',vend_country) FROM vendors;
#格式化相关字段,ltrim是去掉左边空余字符，rtrim是去掉右边空余字符
SELECT 'hello', LTRIM('   hello'), RTRIM('hello    ');
#使用字段别名
SELECT vend_name,vend_country, CONCAT(vend_name,'',vend_country) as short_name FROM vendors;
#数学计算
SELECT prod_id,quantity * item_price as total FROM orderitems;

mysql函数的使用

#函数的使用
#定义字符串全部大写
SELECT vend_name,UPPER(vend_name) FROM vendors;
#定义字符串全部小写
SELECT vend_name,LOWER(vend_name) FROM vendors;

#截取部分的位置
#正向
SELECT vend_name,SUBSTR(vend_name,1,5) FROM vendors;
#反向
SELECT vend_name,SUBSTR(vend_name,-5,5) FROM vendors;

#soundex功能，仅用于字符串，从他们的发音构成
SELECT * FROM customers WHERE SOUNDEX(cust_contact) = SOUNDEX('Y Li');
#日期和时间的处理
SELECT * FROM orders WHERE DATE(order_date) = '2005-09-01';
#筛选时间字段
SELECT * FROM orders WHERE YEAR(order_date) = 2005 and MONTH(order_date) = 9

#avg函数，求平均值
SELECT AVG(prod_price) FROM products;

#count函数，求总数
SELECT COUNT(*) as num_count FROM products;

#求最大值和最小值
SELECT MAX(prod_price) FROM products;
SELECT MIN(prod_price) FROM products;

#sum求和函数
SELECT sum(quantity * item_price) FROM orderitems;

MySQL group by的实战案例

#mysql数据分组
SELECT vend_id,COUNT(*) FROM products GROUP BY vend_id;

#创建摘要,rollup就是用于统计
SELECT vend_id,COUNT(*) FROM products GROUP BY vend_id WITH ROLLUP;

#分组过滤 having 只用于group by 后面
SELECT vend_id,COUNT(*) FROM products GROUP BY vend_id HAVING COUNT(*) >2;

#分组和排序
SELECT order_num,SUM(quantity * item_price) FROM orderitems GROUP BY 
order_num HAVING SUM(quantity * item_price) >100 ORDER BY SUM(quantity * item_price);

mysql 子查询，连接表的使用

#字查询
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 id FROM customers;

#如何连接多个关联表,这种写法是默认的连接方式也就是内连接
SELECT v.vend_name,p.prod_name FROM vendors as v,products as p WHERE v.vend_id = p.vend_id;

#多表连接
SELECT * FROM customers as c,orders as o,orderitems as oi
WHERE c.cust_id = o.cust_id and o.order_num = oi.order_num and prod_id = 'TNT2';

#自连接
SELECT * FROM products WHERE prod_id in (SELECT prod_id FROM products WHERE vend_id = '1003');
SELECT p1.prod_id,p1.prod_name FROM products p1, products p2 WHERE p1.prod_id = p2.prod_id and p2.vend_id = '1003';

#自然连接，一般用于压测，创建大量数据
SELECT p1.prod_id,p1.prod_name FROM products p1, products p2 WHERE p2.vend_id = '1003';

#外部连接 左连接
SELECT * FROM customers c LEFT JOIN orders o on c.cust_id = o.cust_id;
#右连接
SELECT * FROM customers c RIGHT JOIN orders o on c.cust_id = o.cust_id;


#带有聚合函数的连接
SELECT c.cust_name,c.cust_id, COUNT(*) FROM customers c ,orders o
WHERE c.cust_id = o.cust_id GROUP BY c.cust_id;

MySQL全文索引的用法

#使用传统模式搜索
SELECT note_text FROM productnotes WHERE note_text LIKE '%rabbit%';
#使用全文索引
SELECT note_text FROM productnotes WHERE MATCH(note_text) against ('rabbit');
#查询词的优先级匹配情况
SELECT note_text,MATCH(note_text) against ('rabbit') FROM productnotes;
#布尔查询模式
#布尔型操作符号的含义
# +必须包含
# -必须不包含
# >增加优先等级
# <降低优先等级
# *词尾通配符
# ""定义短句
SELECT note_text FROM productnotes WHERE MATCH(note_text) against ('heavy -rope*' in boolean mode);

mysql插入语句的用法

#插入一行数据
-- INSERT INTO customers VALUES (NULL,'abc','100 main street','Los angles','Ca','90046','USA',NULL,NULL);
-- SELECT * FROM customers;
#插入数据的一部分
-- INSERT INTO customers(cust_name,cust_address,cust_zip) VALUES ('ABC2','105 Main Stree','90049');
-- SELECT * FROM customers;
#插入多行
-- INSERT INTO customers(cust_name,cust_address,cust_zip) VALUES ('ABC5','105 Main Stree','90049'),('ABC6','105 Main Stree','90049'),('ABC7','105 Main Stree','90049');
-- SELECT * FROM customers;
#插入数据来自其他查询结果
CREATE TABLE customers2 as SELECT * FROM customers;
SELECT * FROM customers2;
INSERT INTO customers2 SELECT * FROM customers;

mysql的更新与删除

#更新
UPDATE customers2 set cust_contact='felix',cust_email = 'Felix@qq.com' WHERE cust_id = '10005';
SELECT * FROM customers2;

#删除
DELETE FROM customers2 WHERE cust_id = '10005';
SELECT * FROM customers2;

MySQL视图的使用

#为什么使用视图
#其目的是简化sql语句，实现代码的重用，隐藏业务逻辑

#使用视图
CREATE VIEW productcustomer as 
SELECT cust_name,cust_contact FROM customers c,orders o, orderitems oi 
WHERE c.cust_id = o.cust_id and o.order_num = oi.order_num;
SELECT * FROM productcustomer WHERE cust_contact = 'Jim Jones';

#内容格式化
CREATE VIEW vendorlocation as 
SELECT CONCAT(LTRIM(vend_name),"(",vend_country,")") FROM vendors

#视图过滤
CREATE view customernotnull as SELECT * FROM customers WHERE cust_contact is not NULL;
SELECT * FROM customernotnull

#计算字段视图
CREATE VIEW orderprice as 
SELECT prod_id,(quantity * item_price) as total  FROM orderitems;
 
SELECT * FROM orderprice

#视图的要求
#要有条件
#不可以包含groupby信息
#不可以包含子查询
#不可以包含max min AVG sum
#不可以包含计算字段
#不可以包含distinct

MySQL的存储机制

#c创建存储过程
CREATE PROCEDURE productpricing()
BEGIN
SELECT avg(prod_price)  FROM products;
end;
#调用存储过程
CALL productpricing()

#用delimiter代替;号

delimiter //
CREATE PROCEDURE productpricing2()
BEGIN
SELECT avg(prod_price)  FROM products;
end //
delimiter;
CALL productpricing2()

#删除存储过程
DROP PROCEDURE productpricing2;

#创建带有参数的存储过程
CREATE PROCEDURE productpricing2(out pl DECIMAL(8,2),out ph DECIMAL(8,2),out pa DECIMAL(8,2) )
BEGIN 
SELECT AVG(prod_price) INTO pa FROM products;
SELECT max(prod_price) INTO ph FROM products;
SELECT min(prod_price) INTO pl FROM products;
END;
CALL productpricing2(@pl,@ph,@pa);
SELECT @pl; 


#带有输入的存储过程
CREATE PROCEDURE productpricing3(in onumber int,out ototal DECIMAL(8,2) )
BEGIN
SELECT SUM(item_price*quantity) FROM orderitems WHERE order_num = onumber into ototal;
end;
SELECT * FROM orderitems;

CALL productpricing3(20005,@p1);
SELECT @p1;

mysql游标的使用

#什么是游标
#在存储过程中，根据需要对数据集合进行前后游览的一种应用。cursor
#使用、创建游标
CREATE PROCEDURE processorders6()
--  
BEGIN
		DECLARE o int;
		DECLARE done boolean DEFAULT 0;
	DECLARE ordernumber CURSOR
	for 
	SELECT order_num FROM orders;
	DECLARE CONTINUE HANDLER for SQLSTATE '02000' set done =1;
	OPEN ordernumber;
	REPEAT 
-- 
		FETCH ordernumber into o;
		SELECT o;
		UNTIL done end repeat;
--  
	CLOSE ordernumber;
END;
-- 
-- #浏览数据

call processorders6();

MySQL触发器的使用

#创建一个触发器
CREATE TRIGGER newproduct AFTER INSERT on products
for each row SELECT 'product add' INTO @ee;

#验证触发器效果
SELECT * FROM products;
INSERT INTO products 
(prod_id,vend_id,prod_name,prod_price,prod_desc)
VALUES
(	'ANV21','1001','3 Ton anvil','19.90','3 TON anvil'
);
-- #使用触发器
SELECT @ee;

#删除触发器
DROP TRIGGER newproduct;

#创建一个触发器
CREATE TRIGGER newproduct AFTER INSERT on products
for each row SELECT new.prod_id INTO @ee;

INSERT INTO products 
(prod_id,vend_id,prod_name,prod_price,prod_desc)
VALUES
(	'ANV21','1001','3 Ton anvil','19.90','3 TON anvil'
);

SELECT @ee;

#删除触发器
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;
CREATE TABLE archive_orders as 
SELECT * FROM orders

DELETE FROM archive_orders

SELECT * FROM archive_orders