数据库 : Mysql 1 - 日常应用
时间：2021-07-01 10:21:17
帮助过：4人阅读
-u root
-p
#显示所有数据库
SHOW databases;
#显示当前数据库与应用程序间进行了多少条连接
SHOW processlist;
#使用某一数据库
USE database;
#显示所有表
SHOW tables;
#表结构描述 describe
DESC table;
#筛选表数据
#选择哪些列
SELECT prod_name, prod_price
#从哪个表选
FROM products
#选择的列做出什么筛选匹配
WHERE vend_id
= 1003 OR vend_id
= 1004
#根据什么顺序作出排序 DESC 降序
ASC 升序
ORDER BY prod_price;
WHERE--------------------------
vend_id
IN (......)
vend_id IS NOT NULL
-------------------------------------
SELECT prod_id, prod_price
FROM products
WHERE prod_name
LIKE ‘jet%‘
ORDER BY prod_price;
-------------------------------------
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
LIKE ‘1000‘
ORDER BY prod_name;
SELECT prod_name
FROM products
WHERE prod_name REGEXP
‘.000‘
ORDER BY prod_name;
/*
LIKE 匹配整个列。如果被匹配的文本在列值
中出现, LIKE 将不会找到它,相应的行也不被返回(除非使用
通配符)。而 REGEXP 在列值内进行匹配,如果被匹配的文本在
列值中出现, 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-9] ton‘
ORDER BY prod_name;
#匹配特殊字符需要在其前面添加\\作为前 ‘.‘匹配0个或1个字符
SELECT prod_name
FROM products
WHERE prod_name REGEXP
‘\\.‘
ORDER BY prod_name;
--------------------------------------------------------------
#字符类
/*
[:alnum:] [a-zA-Z0-9]
[:alpha:] [a-zA-Z]
[:blank:] [\\t]
[:cntrl:] [ASCII 0-31和127]
[:digit:] [0-9]
[:graph:] 和[:print:]相同,但不包括空格
[:lower:] [a-z]
[:print:] 任意可打印字符
[:punct:] 既不在[:alnum]又不在[:cntrl:]的字符
[:space:] 包括空格在内的任意空白字符（\\f\\n\\r\\t\\v)
[:upper:] [A-Z]
[:xdigit:] 十六进制[a-fA-F0-9]
*/
--------------------------------------------------------------
#重复元字符
/*
* 0个或多个匹配
+ 1个或多个匹配(等于{1,})
? 0个或1个匹配(等于{0,1})
{n} 指定数目的匹配
{n,} 不少于指定数目的匹配
{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
‘[[:digit:]]{4}‘
ORDER BY prod_name;
------------------------------------------------------------------
/*
^ 文本的开始
$ 文本的结尾
[[:<:]] 词的开始
[[:>:]] 词的结尾
*/
SELECT prod_name
FROM products
WHERE prod_name REGEXP
‘^[0-9\\.]‘
ORDER BY prod_name;
-----------------------------------------------------------------
/*
计算字段
一般数据库中存取的数据与应用程序所要求的数据格式并不一致，
所以需要进行格式转换，而计算字段就是应用于这一用途的字段。
计算字段是运行时在SELECT中创建出来的。
*/
#拼接 --例：供应商 + 地址
SELECT Concat(vend_name,
‘(‘, vend_country,
‘)‘)
FROM vendors
ORDER BY vend_name;
#剪切空格 RTrim
SELECT Concat(
RTrim(vend_name),
‘(‘, vend_country,
‘)‘)
FROM vendors
ORDER BY vend_name;
#使用别名 AS
SELECT Concat(
RTrim(vend_name),
‘(‘, 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;
---------------------------------------------------
#函数
#函数能方便程序员编写代码，但是这样会影响代码的可移植性。
/*
1.文本处理函数
Left() 返回串左边的字符
Length() 返回串的长度
Locate() 找出串的一个子串
Lower() 将串转换为小写
LTrim() 去掉串左边的空格
Right() 返回串右边的字符
RTrim() 去掉串右边的空格
Soundex() 返回串的SOUNDEX值
SubString() 返回子串的字符
Upper() 将串转换为大写
SOUNDEX 是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。
SOUNDEX 考虑了类似的发音字符和音节,使得能对串进行发音比较而不是字母比较。
以下进行该函数的使用对比
*/
SELECT cust_name,cust_contact
FROM customers
WHERE cust_contact
= ‘Y. Lie‘;
SELECT cust_name,cust_contact
FROM customers
WHERE Soundex(cust_contact)
= Soundex(
‘Y. Lie‘);
/*
日期和时间处理函数
AddDate() 增加一个日期(天、周等)
AddTime() 增加一个时间(时、分等)
CurDate() 返回当前日期
CurTime() 返回当前时间
Date() 返回日期时间的日期部分
DateDiff() 计算两个日期之差
Date_Add() 高度灵活的日期运算函数
Date_Format() 返回一个格式化的日期或时间串
Day() 返回一个日期的天数部分
DayOfWeek() 对于一个日期,返回对应的星期几
Hour() 返回一个时间的小时部分
Minute() 返回一个时间的分钟部分
Month() 返回一个日期的月份部分
Now() 返回当前日期和时间
Second() 返回一个时间的秒部分
Time() 返回一个日期时间的时间部分
Year() 返回一个日期的年份部分
*/
SELECT cust_id, order_num
FROM orders
WHERE Date(order_date)
BETWEEN ‘2005-09-01‘ AND ‘2005-09-30‘;
/*
数值处理
Abs() 返回一个数的绝对值
Cos() 返回一个角度的余弦
Exp() 返回一个数的指数值
Mod() 返回除操作的余数
Pi() 返回圆周率
Rand() 返回一个随机数
Sin() 返回一个角度的正弦
Sqrt() 返回一个数的平方根
Tan() 返回一个角度的正切
*/
-------------------------------------------------------------
#聚集函数
#我们经常需要汇总数据而不用把它们实际检索出来。
/*
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和
DISTINCT 聚焦不同值， ALL 全部值进行计算
*/
SELECT AVG(prod_price)
AS avg_price
FROM products
WHERE vend_id
= 1003;
SELECT COUNT(
*)
AS num_cust
FROM customers;
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;
-------------------------------------------------------------
#数据分组
/*
数据分组由GROUP BY 语句引出，注意一点就是选择的字段如果不是
聚集函数则需要归在分组字段中
WITH ROLLUP关键字可以得到每个分组以及每个分组汇总级别
(针对每个分组)的值
*/
SELECT vend_id,
COUNT(
*)
AS num_prods
FROM products
GROUP BY vend_id;
SELECT vend_id,
AVG(prod_price)
AS price_avg,
COUNT(
*)
FROM products
GROUP BY vend_id;
SELECT vend_id,
AVG(prod_price)
AS price_avg,
COUNT(
*)
FROM products
GROUP BY vend_id
WITH ROLLUP;
#过滤分组
SELECT cust_id,
COUNT(
*)
AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(
*)
>= 2;
#HAVING过滤的是分组后数据组，而WHERE过滤的是数据项
SELECT vend_id,
COUNT(
*)
AS num_prods
FROM products
WHERE prod_price
>= 10
GROUP BY vend_id
HAVING COUNT(
*)
>= 2;
SELECT order_num,
SUM(quantity
*item_price)
AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING ordertotal
>= 50
ORDER BY ordertotal;
#SELECT子句顺序
#SELECT -- FROM -- WHERE -- GROUP BY --
#
-- HAVING -- ORDER BY -- LIMIT
---------------------------------------------------------------
#子查询
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;
-------------------------------------------------------------------
/*
联结
分解数据为多个表能更有效地存储,更方便地处理,并且具有更大的
可伸缩性。使用特殊的语法,可以联结多个表返回一组输出,
联结在运行时关联表中正确的行。
在联结两个表时,你实际上做的是将第一个表中的每一行
与第二个表中的每一行配对,然后由WHERE语句进行筛选。
两个表的行之间映射的结果又称笛卡尔积。
*/
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 vendors, products
ORDER BY vend_name, prod_name;
/*
目前为止所用的联结称为等值联结(equijoin),它基于两个表之间的
相等测试。这种联结也称为内部联结。其实,对于这种联结可以使用稍
微不同的语法来明确指定联结的类型。
*/
#内部联结
SELECT vend_name, prod_name, prod_price
FROM vendors
INNER JOIN products
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 cust_name, cust_contact
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
= ‘TNT2‘;
#自联结
/*
当需要在一张表进行两次查询才能查找到正确的字段时，可以采用自联结。
如在一张商品表中寻找某样产品的产商下的所有产品。
*/
SELECT prod_id, prod_name
FROM products
WHERE vend_id
= (
SELECT vend_id
FROM products
WHERE prod_id
= ‘DTNTR‘);
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 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 customers
RIGHT OUTER JOIN orders
ON orders.cust_id
= customers.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;
#组合查询
/*
多数SQL查询都只包含从一个或多个表中返回数据的单条 SELECT 语
句。MySQL也允许执行多个查询(多条 SELECT 语句)
,并将结果作为单个查询结果集返回。
这 些 组 合 查 询 通 常 称 为 并 ( union ) 或 复 合 查 询
(compound query).
*/
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 (
1001,
1002);
>>>>>>>>>>>>>>>>>>>>>
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);
#UNION 默认会去除重复的行，如果想全部显示出来则使用
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price
<= 5
UNION ALL
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id
IN (
1001,
1002);
--------------------------------------------------------------
/*
并非所有的引擎都支持全文本搜索，选择引擎时需注意。
LIKE通配符搜索和正则表达式查找存在几个问题：
性能——通配符和正则表达式匹配通常要求MySQL尝试匹配表
中所有行(而且这些搜索极少使用表索引)。
因此,由于被搜索行数不断增加,这些搜索可能非常耗时。
明确控制——使用通配符和正则表达式匹配,很难(而且并不总
是能)明确地控制匹配什么和不匹配什么。例如,指定一个词必
须匹配,一个词必须不匹配,而一个词仅在第一个词确实匹配的
情况下才可以匹配或者才可以不匹配。
智能化的结果——虽然基于通配符和正则表达式的搜索提供了非
常灵活的搜索,但它们都不能提供一种智能化的选择结果的方法。
例如,一个特殊词的搜索将会返回包含该词的所有行,而不区分
包含单个匹配的行和包含多个匹配的行(按照可能是更好的匹配
来排列它们)。类似,一个特殊词的搜索将不会找出不包含该词但
包含其他相关词的行。
*/
#在使用全文本搜索时,MySQL不需要分别查看每个行,不需要分别分析和处理
#每个词。MySQL创建指定列中各词的一个索引,搜索可以针对这些词进
#行。
#为了进行全文本搜索,必须索引被搜索的列,而且要随着数据的改
#变不断地重新索引。在对表列进行适当设计后,MySQL会自动进行所有
#的索引和重新索引。
#启动全文本搜索支持
CREATE TABLE productnotes
(
note_id INT NOT NULL AUTO_INCREMENT,
prod_id CHAR(
10)
NOT NULL,
note_data DATE NOT NULL,
note_text TEXT NULL,
PRIMARY KEY(note_id),
FULLTEXT(note_text),
) ENGINE = MyISM;
#这里FULLTEXT索引单个列。
#不要在导入数据的时候使用FULLTEXT索引，因为更新索引需要时间。
#应该首先导入所有数据，然后再修改表，定义FULLTEXT。
#在索引之后,使用两个函数 Match() 和 Against() 执行全文本搜索,
#其中 Match() 指定被搜索的列, Against() 指定要使用的搜索表达式。
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against(
‘rabbit‘);
#全文本搜索会对查询结果进行排序。
SELECT note_text,
Match(note_text) Against(‘rabbit‘)
AS rank
FROM productnotes;
#布尔文本搜索是全文本搜索的一种选择模式。
------------------------------------------------------------------
#数据插入有以下几个方式
#插入完整的行
INSERT INTO customers
VALUES(
NULL,
‘Pep E. LaPew‘,
‘100 Main Street‘,
‘Los Angeles‘,
‘CA‘,
‘90046‘,
‘USA‘,
NULL,
NULL
);
#插入多行
INSERT INTO customers(cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
VALUES(
‘Pep E. LaPew‘,
‘100 Main Street‘,
‘Los Angeles‘,
‘CA‘,
‘90046‘,
‘USA‘
),
(
‘M. Martian‘,
‘42 Galaxy Way‘,
‘New York‘,
‘NY‘,
‘11213‘,
‘USA‘
);
#插入某些查询的结果
INSERT INTO customers(cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
SELECT cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
FROM custnew;
/*
提高整体性能：
数据库经常被多个客户访问,对处理什么请求以及用什么次序处理
进行管理是MySQL的任务。
INSERT操作可能很耗时(特别是有很多索引需要更新时),而且它可能
降低等待处理的 SELECT 语句的性能。
如果数据检索是最重要的(通常是这样),则你可以通过在
INSERT 和 INTO 之间添加关键字 LOW_PRIORITY ,指示MySQL
降低 INSERT 语句的优先级。
*/
INSERT LOW_PRIORITY
INTO
UPDATE LOW_PRIORITY
DELETE LOW_PRIORITY
#更新操作
UPDATE customers
SET cust_name
= ‘The Fudds‘,
cust_email = ‘elmer@fudd.com‘
WHERE cust_id
= 10004;
#删除操作
DELETE FROM customers
WHERE cust_id
= 10006;
#删除一表后重建表
TRUNCATE TABLE
-------------------------------------------------------------------
#创建表
#指定默认值，多个主键
CREATE TABLE orderitems
(
order_num INT NOT NULL,
order_item CHAR(
10)
NOT NULL,
quantity INT NOT NULL DEFAULT 1,
item_price DECIMAL(
8,
2)
NOT NULL,
PRIMARY KEY(order_num, order_item)
) ENGINE=InnoDB;
/*
与其他DBMS一样, MySQL有一个具体管理和处理数据的内部引擎。
在你使用 CREATE TABLE 语句时,该引擎具体创建表,而在你使用 SELECT
语句或进行其他数据库处理时,该引擎在内部处理你的请求。多数时候,
此引擎都隐藏在DBMS内,不需要过多关注它。不同的是mysql打包了多种
引擎。
? InnoDB 是一个可靠的事务处理引擎(参见第26章)
,它不支持全文
本搜索;
? MEMORY 在功能等同于 MyISAM ,但由于数据存储在内存(不是磁盘)
中,速度很快(特别适合于临时表)
;
? MyISAM 是一个性能极高的引擎,它支持全文本搜索(参见第18章)
,但不支持事务处理。
*/
#注意：
#混用引擎类型有一个大缺陷。外键不能跨引擎,即使用一
#个引擎的表不能引用具有使用不同引擎的表的外键。
------------------------------------------------------------------
#更新表结构,通常修改表结构是错误的，因为可能影响多处接口。
ALTER TABLE vendors
ADD vend_phone
CHAR(
20);
ALTER TABLE vendors
DROP CIOLUMN vend_phone;
#ALTER TABLE 一种常见的用途就是定义外键。
ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders
FOREIGN KEY (order_num)
REFERENCES orders (order_num);
#删除表
DROP TABLE customers;
#重命名
RENAME TABLE backup_customers
TO customers,
backup_vendors TO vendors,
backup_products TO products;
---------------------------------------------------------------------
/*
视图
视图是虚拟的表，本身并不包含任何列或数据，它包含的是SQL查询。
? 重用SQL语句。
? 简化复杂的SQL操作。在编写查询后,可以方便地重用它而不必
知道它的基本查询细节。
? 使用表的组成部分而不是整个表。
? 保护数据。可以给用户授予表的特定部分的访问权限而不是整个
表的访问权限。
? 更改数据格式和表示。视图可返回与底层表的表示和格式不同的
数据。
简单地认为视图就是包装SQL查询语句的包，每次执行都会调用相应的
查询语句块。
*/
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 cust_name, cust_contact
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 vendorlocations
AS
SELECT Concat(
RTrim(vend_name),
‘(‘,
RTrim(vend_country),
‘)‘)
AS vend_title
FROM vendors
ORDER BY vend_name;
#用视图过滤不想要的视图
CREATE VIEW customeremaillist
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;
----------------------------------------------------------------
/*
存储过程用于将SQL操作绑定成一个执行的最小子集，要么一起成功，
要么一起失败。
*/
#创建存储过程
CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price)
AS priceaverage
FROM products;
END;
#如果命令行实用程序要解释存储过程自身内的 ; 字符,
#则它们最终不会成为存储过程的成分,这会使存储过程中的SQL出现句法
#错误。
#使用DELIMITER 来临时标记命令行实行程序的语句分隔符
DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price)
AS priceaverage
FROM products;
END //
DELIMITER ;
#调用存储过程
CALL productpricing();
#删除储存过程
DROP PROCEDURE productpricing;
#带变量的存储过程
#一般存储过程是不返回结果的，而是把结果存储至变量之中。
#OUT参数用于存储结果
#IN参数用于传递参数
CREATE PROCEDURE productpricing(
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 productpricing(@pricelow,
@pricehigh,
@priceaverage);
SELECT
