数据库的常用语句命令
数据检索
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);
#使用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
|