目录
一、检索
二、排序
三、过滤
1.distinct 函数
distinct(col_a) 取col_a 的unique,保留null
distinct(col_a),b 取(col_a,b)集合的unique
四、创建字段
1. 合并列 select concat()函数
SELECT Concat(col_1, '符号', RTrim(col_2), '符号') AS col_new
FROM data
ORDER BY col_3;
Concat函数中,列名与符号之间用逗号隔开,符号需用引号扩起
LTrim()函数,去掉列值的左括号,RTrim(),去掉列值的右括号。Trim()函数,去掉串左右两边的空格。
AS 定义新列名或新值。AS可与SELECT 单独搭配使用
五、函数
1. 文本处理函数
函数名 | 功能 |
---|---|
Left() | 返回串左边的字符 |
Length() | 返回串的长度 |
Locate() | 找出串的一个子串 |
Upper() | 将文本转换为大写 |
Lower() | 将文本转换为小写 |
LTrim() | 去掉串左边的空格 |
Right() | 返回串右边的字符 |
RTrim() | 去掉串右边的空格 |
Soundex() | 返回串的SOUNDEX值 |
SubString() | 返回子串的字符 |
Upper() | 将串转换为大写 |
#Soundex()函数的应用
SELECT col_1,col_2
FROM data
WHERE Soundex(col_2) = Soundex('XXX')
2. 时间处理函数
函数名 | 函数功能 |
---|---|
AddDate() | 增加一个日期(天、周等) |
AddTime() | 增加一个时间(时、分等) |
CurDate() | 返回当前日期 |
CurTime() | 返回当前时间 |
Date() | 返回日期时间的日期部分(可能列值存在时分秒) |
DateDiff() | 计算两个日期之差 |
Date_Add() | 高度灵活的日期运算函数 |
Date_Format() | 返回一个格式化的日期或时间串 |
Day() | 返回一个日期的天数部分 |
DayOfWeek() | 对于一个日期,返回对应的星期几 |
Hour() | 返回一个时间的小时部分 |
Minute() | 返回一个时间的分钟部分 |
Month() | 返回一个日期的月份部分 |
Now() | 返回当前日期和时间 |
Second() | 返回一个时间的秒部分 |
Time() | 返回一个日期时间的时间部分 |
Year() | 返回一个日期的年份部分 |
SELECT first_name,last_name
FROM customers
WHERE date(birth_date) >= '1986-01-01'
Order BY first_name;
3. 数值处理函数
函数名 | 函数功能 |
---|---|
Abs() | 返回一个数的绝对值 |
Cos() | 返回一个角度的余弦 |
Exp() | 返回一个数的指数值 |
Mod() | 返回除操作的余数 |
Pi() | 返回圆周率 |
Rand() | 返回一个随机数 |
Sin() | 返回一个角度的正弦 |
Sqrt() | 返回一个数的平方根 |
Tan() | 返回一个角度的正切 |
六、汇总数据
1.聚集函数
聚集函数(aggregate function) 运行在行组上,计算和返回单个值的函数。
函数 | 函数功能 |
---|---|
AVG() | 返回某列的平均值 |
COUNT() | 返回某列的行数 |
MAX() | 返回某列的最大值 |
MIN() | 返回某列的最小值 |
SUM() | 返回某列值之和 |
(1)AVG()函数
(1)忽略列值为NULL的行
(2)只用于单列
(2)COUNT()函数
(1)COUNT(*)对表中行的数目进行计数,不管表列中包含的是空值(NULL)还是非空值。
(2)使用COUNT(column)对特定列中具有值的行进行计数,忽略NULL值。
(3)count(distinct a,b) 取(a,b)组合的唯一情况计数
(3)MIN() MAX()函数
(1)对非数值数据:包括返回文本列中的最值。在用于文本数据时,如果数据按相应的列排序,则返回最前面的行。
(2)忽略列值为NULL的行
(4)SUM()函数
(1)在多个列上进行计算:利用标准的算术操作符,所有聚集函数都可用来执行多个列上的计算。
SELECT SUM(col_1*col_2) AS XXX
FROM data
(2)忽略列值为NULL的行
2.聚集不同值
DISTINCT 参数
SELECT SUM(DISTINCT col_1) AS XXX
FROM data
3.组合聚集函数
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;
七、分组数据
1. 分组最常用的两个:COUNT(),GROUP BY
SELECT id,COUNT(*) AS num_prods
FROM products
GROUP BY id
*对应SELECT前面的列
2. 分组后进行过滤
与having语句的搭配
SELECT id,COUNT(*) AS num_prods
FROM products
GROUP BY id
HAVING COUNT(*) >=2
可以引用列别名
3. 与order by语句的搭配
4. SELECT子句顺序
子句 | 说明 | 是否必须使用 |
---|---|---|
SELECT | 要返回的列或表达式 | 是 |
FROM | 从中检索数据的表 | 仅在从表选择数据时使用 |
WHERE | 行级过滤 | 否 |
GROUP BY | 分组说明 | 仅在按组计算聚集时使用 |
HAVING | 组级过滤 | 否 |
ORDER BY | 输出排序顺序 | 否 |
LIMIT | 要检索的行数 | 否 |
七、子查询
1. 相关子查询(correlated subquery): 跨数据集的子查询
WHERE order.id = cust.id
2.子查询的嵌套(与后期所学的多表联结功能相同)
SELECT cust_name,cust_contactFROMcustomers
wHERE cust_id IN (SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2 '));
八、联结
个人理解是跨数据集的联系与比较
1.创建联结
“WHERE =” 等值联结
SELECT col_1,col_2
FROM data_1,data_2
WHERE data_1.col_3 = data_2.col_4
ORDER BY col_1
WHERE语句将data_1与data_2联结
若没有联结,用SELECT语句从两个数据集匹配,会得到笛卡尔积。即行数会是第一个行的数目乘以第二个行的数目。
2.内部联结
区别于等值联结用 WHERE = 语句,内部联结用 FROM INNER JOIN ON = 语句,返回与等值联结相同
SELECT col_1,col_2
FROM data_1
INNER JOIN data_2
ON data_1.col_3 = data_2.col_4
ORDER BY col_1
如果存在两个表存在同列名的情况,最好用data.col区分
3.多表联结
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;
九、创建高级联结
1.使用表别名
SELECT d1.col_1,d2.col_2
FROM data_1 AS d1,data_2 AS d2
WHERE d1.col_3 = d2.col_4;
如上述代码所示,表别名可以用于WHERE语句,也可以用于SELECT列表以及ORDER BY 语句。
2.使用不同类型的联结
(1)自联结
SELECT d1.col_1,d1.col_2
FROM dataset AS d1, dataset AS d2
WHERE d1.col_3 = d2.col_4
AND d2.col_4 = 'XXX'
对data集取两个别名,以此实现WHERE语句的表内子查询
(2)自然联结
自然联结排除多次出现,使每个列只返回一次。
SELECT c.*,o.order_num,o.order_date,
oi.prod_id,oi.quantity,o1.item _price
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 = 'FB';
(3)外部联结
OUTER JOIN语法时,必须使用RIGHT或LEFT关键字指定包括其所有行的表(RIGHT指出的是OUTER JOIN右边的表,而LEFT指出的是OUTER JOIN左边的表)。使用LEFT OUTER JOIN从FROM子句的左边表中选择所有行。为了从右边的表中选择所有行,应该使用RIGHT OUTER JOIN
left join on里面相当于对右表进行过滤后再链接,右表没有的字段筛选无法生效。即无法在on语句单独对左集进行筛选。
SELECT data1.cola,data2.colb
FROM data1 LEFT OUTER JOIN data2
ON data1.colc = data2.cold;
3.使用带聚集函数的联结
SELECT data1.cola,
data2.colb,
count(data2.cole) AS 2e
FROM data1 LEFT OUTER JOIN data2
ON data1.colc = data2.cold;
十、组合查询
1.使用UNION将两个SELECT语句联合起来
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);
ORDER BY vend_id,prod_price
这种组合会将重复行只展示一次。若要重复展示要用到UNION ALL
十一、文本搜索
1.全文本搜索
SELECT TXTCOL #TXTCOL一个存储txt的列
FROM data_1 #data_1 表
WHERE Match(TXTCOL) Against('xxx') #xxx是要搜索的内容
#等同于 WHERE TXTCOL LIKE '%xxx%'
WHERE Match(列名)Against(‘搜索文本内容’)
全文本搜索不区分大小写
类似于like操作
2.正则表达式搜索(REGEXP)
(1)基本字符匹配
SELECT *
FROM data_1
WHERE col_1 RGERXP '100|200'
(2)or匹配
SELECT *
FROM data_1
WHERE col_1 RGERXP '[123] ton'
SELECT *
FROM data_1
WHERE col_1 RGERXP '1|2|3 ton'
SELECT *
FROM data_1
WHERE col_1 RGERXP '[1-3] ton'
(3)特殊字符匹配
SELECT *
FROM data_1
WHERE col_1 RGERXP '\\.'
元字符 | 说 明 |
---|---|
\\f | 换页 |
\\n | 换行 |
\r | 回车 |
\\t | 制表 |
\\v | 纵向制表 |
特殊地,如果要匹配’\',需要用‘\\\’
(4)匹配字符类
3. rank
SELECT TXTCOL,Match(TXTCOL) Against('xxx') AS rank
FROM data_1
SELECT Match(TXTCOL) Against(‘xxx’) AS rank。意为对Match的列的所有行,进行文本搜索,返回一个rank。rank越高意为着搜索的文本内容在每一行出现的位置越靠前。当然,如果该行并没有出现搜索的文本内容,那么rank值会是0。
4.搜索扩展
SELECT TXTCOL #TXTCOL一个存储txt的列
FROM data_1 #data_1 表
WHERE Match(TXTCOL) Against('xxx' WITH QUERY EXPANSION) #xxx是要搜索的内容,WITH QUERY EXPANSION 扩展查询
区别于普通的文本搜索,文本扩展搜索,WITH QUERY EXPANSION允许在搜索完指定文本内容后,将该内容存在的行中内容,作为新的文本搜索内容,再次搜索。
5.布尔文本搜索
布尔操作符 | 说 明 |
---|---|
+ | 包含,词必须存在 |
- | 排除,词必须不出现 |
> | 包含,而且增加等级值 |
< | 包含,且减少等级值 |
() | 把词组成子表达式(允许这些子表达式作为一个组被包含、排除、排列等) |
~ | 取消一个词的排序值 |
* | 词尾的通配符 |
“” | 定义一个短语(与单个词的列表不一样,它匹配整个短语以便包含或排除这个短语) |
SELECT TXTCOL #TXTCOL一个存储txt的列
FROM data_1 #data_1 表
WHERE Match(TXTCOL) Against('AAA - BBB*' IN BOOLEAN MODE) #包含AAA,但不包含BBB以及以BBB开头的词是要搜索的内容,IN BOOLEAN MODE指示以布尔文本进行搜索
注意:
1.如果表中的行数少于3行,则全文本搜索不返回结果(因为每个词或者不出现,或者至少出现在50%的行中)
2.忽略词中的单引号。例如,don’t索引为dont。
十二、插入数据
1.插入完整的行
INSERT INTO data_1
VALUES(NULL,
value_1
...)
插入新的一行,采用这种简洁的写法时应注意顺序以及个数问题。如果某列为空值,就设为NULL。
INSERT INTO data_1(col_2,
col_3,
...)
VALUES(value_1,
value_2
...)
采用这种采繁琐的写法时,注意列名与数值的对应。
2.插入多个行
如果要插入的列名相同,可以采取简洁写法。否者,要重复写入insert语句
简洁形:
INSERT INTO data_1(col_2,
col_3,
...)
VALUES(
value_1,
value_2
...),
(
value_1,
value_2
...);
复杂形:
INSERT INTO data_1(col_2,
col_3,
...)
VALUES(value_1,
value_2
...);
INSERT INTO data_1(col_2,
col_3,
...)
VALUES(value_1,
value_2
...);
3.插入检索出的数据
INSERT INTO data_1(col_2,
col_3,
...)
SELECT col_4,col_5,...,
FROM data_2
检索data_2的特定列导入data_1中
十三、更新和删除数据
1.更新数据
可以理解为添加某个格的数据
用set语句可以一次性添加多条信息
where 索引
UPDATE data_1
SET col_2 = 'xxx'
col_3 = 123
col_4 = NULL
WHERE col_1 = 'yyy'
对col_1=yyy的行的col_2,col_3添加信息。其中col_4列值为空
2.删除数据
用DELETE FROM语句
DELETE FROM data_1
WHERE col_1= 'yyy'
从data_1删除col_1=yyy的行
如果要删除整个数据集,应该用TRUNCATE TABLE语句
3.注意
1.如果执行UPDATE而不带WHERE子句,则表中每个行都将用新值更新。类似地,如果执行DELETE语句而不WHERE子句,表的所有数据都将被删除。
2.MySQL没有撤销(undo)按钮。应该非常小心地使用UPDATE和DELETE,否则你会发现自己更新或删除了错误的数据。
十四、创建表
1.创建表
CREATE TABLE data_1
(
col_1 int NOT NULL AUTO_INCREMENT,
col_2 int NOT NULL DEFAULT 1,
col_3 char(10) NULL,
PRIMARY KEY (col_1,col_2)
) ENGINE = InnoDB
设定 | 解释 |
---|---|
data_1 | 表名 |
col_1 | 列名 |
int、char() | 数据类型。char(10),10为数据长度 |
NULL | 定义数据值可以为空 |
NOT NULL | 不能为空 |
NOT NULL AUTO_INCREMENT | 本列每当增加一行时自动增量。每次执行一个INSERT操作时,MySQL自动对该列增量(从而才有这个关键字AUTO_INCREMENT),给该列赋予下一个可用的值。 |
PRIMARY KEY() | 定义某列为主键,可以多列 |
DEFAULT 1 | 定义当插入值为空时,默认插入1 |
ENGINE | 引擎。InnoDB,事务处理引擎,不支持全文本搜索。MEMORY,速度很快(特别适合于临时表)。MyISAM,支持全文本搜索,但不支持事务处理 |
2.更新表
(1)插入列
插入:ADD 列名 数据类型
ALTER TABLE data_1
ADD col_4 char(20);
(2)删除列
删除:DROP COLUMN 列名
ALTER TABLE data_1
DROP COLUMN col_4;
(3)定义外键:
这部分不理解,暂时放个图
3.删除表
DROP TABLE data_1;
3.重命名表
RENAME TABLE data_1 TO data_rename1,
data_2 TO data_rename2,
data_3 TO data_rename3;
十五、视图
1.何为视图
这就是视图的作用。productcustomers是一个视图,作为视图,它不包含表中应该有的任何列或数据,它包含的是一个SQL查询(与上面用以正确联结表的相同的查询)。
规则和限制:
与表一样,视图必须唯一命名(不能给视图取与别的视图或表相同的名字)。
对于可以创建的视图数目没有限制。
为了创建视图,必须具有足够的访问权限。这些限制通常由数据库管理人员授予。
视图可以嵌套,即可以利用从其他视图中检索数据的查询来构造一个视图。
ORDER BY可以用在视图中,但如果从该视图检索数据SELECT中也含有ORDER BY,那么该视图中的ORDER BY将被覆盖。
视图不能索引,也不能有关联的触发器或默认值。
视图可以和表一起使用。例如,编写一条联结表和视图的SELECT语句。
2.使用视图
创建:CREATE VIEW语句。
查看:SHOW CREATE VIEW viewname。
删除:DROP VIEW viewname。
更新:DROP再用CREATE,也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在,则第2条更新语句会创建一个视图;如果要更新的视图存在,则第2条更新语句会替换原有视图。
(1)利用视图简化复杂的联结
上面的SELECT语句可以简化为,利用等值联结
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;
创建了一个视图productcustomers,在视图中用WHERE 语句
SELECT cust_name,cust_contact
FROM productcustomers
WHERE prod_od = 'TNT2'
(2)concat函数与视图
CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')')
AS vend_title
FROM vendors
ORDER BY vend_name
将列concat后成为视图
(3)WHERE函数与视图
CREATE VIEW customeremaillist AS
SELECT cust_id,cust_name,cust_email
FROM customers
WHERE cust_email is NOT NULL
用where语句筛选后成为视图
(4)计算字段与视图
CREATE VIEW orderitemsexpanded AS
SELECT order_num,
prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM orderitems;
3.更新
一般,应该将视图用于检索(SELECT语句),而不用于更新(INSERT、UPDATE和DELETE)
十六、存储过程
存储过程定义:存储特定语句以供批量使用
1. 使用存储过程
(1)创建
delimiter $$
CREATE PROCEDURE productpricing()
BEGIN
SELECT AVG(prod_price) AS priceaverage
FROM products;
END $$
(2)执行
delimiter $$
CALL productpricing();
(3)删除
DROP PROCEDURE productpricing();
(4)插入变量
delimiter $$
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 @pricelow, @pricehigh, @priceaverage;
补充
update与replace的联合使用,检索后替换
UPDATE data_1
SET REPLACE (col_1,'xxx','yyy')
WHERE col_1 LIKE '%xxx%'
UPDATE data_1
SET col_1 = 'yyy'
WHERE col_1 = 'xxx'