MYSQL必知必会笔记

MySQL必知必会笔记

《MySQL必知必会》学习笔记

了解数据库 了解MySQL

数据库（databass）:以某种有组织的方式存储的数据集合
DBMS(Database Management System）：数据库管理系统
SQL((Structured Query Language):结构化查询语言
MySQL是基于客户机——服务器的数据库

检索数据

SQL语句不区分大小写
distinct语句：只返回不同的值

select distinct vend_id ,prod_price  from products ;
##distinct会应用于再它后面的所有列，所以vend_id和prod_price两个中有一个在自己列中
##是唯一的就输出该行，必须两个在自己列都不是唯一的，才不输出

limit 语句：限制结果

select prod_name from products limit 5,5;#指示返回从第5行开始的后5行
#0行为起始行

排序

order by:对检索结果进行排序（默认升序）
desc：降序排序，只对在关键词前的单个列起作用

select prod_id,prod_price,vend_id,prod_name from products order by vend_id desc, 
prod_price;-- 先排vend_id,vend_id相等再排prod_price
#结果为先对vend_id进行降序排序，再对prod_price升序

以下程序用于找出最贵商品

select prod_price from products order by prod_price DESC limit 1;

（注：limit和order by 混用在重复数据多时，可能出现和预期不一样的结果，详情如下
链接文章limit和order by 混用陷阱）

过滤语句与操作符与通配符

where：根据给定条件进行过滤

select prod_name,prod_price from products where prod_price=2.5;
-- 返回prod_price值为2.5的行

BETWEEN：在两个值之间，闭区间
NOT BETWEEN：不在这两个值中 开区间（not between 5 and 10 #不会检索出5与10之间的）

select prod_name,prod_price from products where prod_price between 5 and 10;

IS NULL 语句，进行空值检查
‘ ’ ：单引号用来限定字符串

select prod_name,prod_price from products where prod_name='Sling' ;

操作符：
AND：用来指示检索满足所有条件的行
OR ：匹配任一条件(AND有限条件高于OR)
IN:：指定要匹配的条件

select vend_id from products where vend_id in(1003,1002);

NOT：否定跟在后面的条件

通配符：
通配符：用来匹配值的一部分的特殊字符
%：表示任意字符出现任意次数

select prod_id,prod_name from products where prod_name like 'jet%';
#检索以jet开头的词，%告诉MySQL接收jet后的任意字符，不管长度
select prod_id,prod_name from products where prod_name like '%anvil%';
#检索含有文本anvil的值

下划线（_）：只匹配单个字符

select prod_id,prod_name from products where prod_name like '_ ton anvil';

注：1.通配符搜索处理时间一般比较长
2.通配符置于搜索模式的开始处，搜索起来最慢

正则表达式搜索

点" ."：表示匹配任意一字符

select prod_id,prod_name from products where prod_name REGEXP BINARY 'JETPACK .000';
#正则表达式不区分大小写，如需要，用BINARY来区分

LIKE和REGEXP区别：LIKE匹配整个列，REGEXP在列值内匹配，文本在列值中出现，REGEXP就会返回相应的行
个人理解：LIKE匹配整个串，REGEXP匹配连续子串
OR匹配 “|”：

select prod_id,prod_name from products where prod_name REGEXP BINARY '1000|2000';

匹配符号内的任一单一字符：[ 和 ]

select prod_id,prod_name from products where prod_name REGEXP  '[123] Ton';
#匹配1或2或3 中一个就返回
select prod_id,prod_name from products where prod_name REGEXP  '[^123] Ton';
#匹配除这些字符以外的
#[123]等同于[1-3]，[^123]等同于[^1-3]

匹配特殊字符

select prod_id,prod_name from products where prod_name REGEXP  '\\.';
#匹配特殊字符需用\\为前导

匹配字符类

重复元字符

例子

select prod_name from products where prod_name REGEXP '[[:digit:]]{4}'
#[:digit:]匹配任意数字，{4}确切要求出现4次，最后结果为连在一起的任意4位数字

定位符
以一个数（包括小数点）开始的数

select prod_name from products where prod_name REGEXP '^[0-9\\.]'
#^在集合(即[和])中表否定，否则用来指串的开始处

创建计算字段

计算字段并不实际存在于数据库表中，计算字段是运行是在SELECT语句内创建的。
Concat（）函数：用来拼接两个列

select concat(RTrim(vend_name),'(',vend_country,')') from vendors order by vend_name;
#各个串之间用逗号分隔
#RTrim()函数去掉右边的所有空格,LTrim()去掉左边空格,Trim去掉串左右两边空格

使用别名：
别名：用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;
#算术操作符 +   -   *  /
select now();#返回现在时间

数据处理函数

文本处理函数


eg：匹配所有发音类似于Y.Lie

select cust_name,cust_contact from customers where soundex(cust_contact)=soundex('Y LIE')

日期和时间处理函数

日期格式最好为yyyy-mm-dd（最好用Date()进行查询）

select cust_id,order_num from orders where Date(order_date)='2005-09-01';
#Date(order_date)指示MySQL仅提取列的日期部分
select cust_id,order_num from orders where Year(order_date)=2005 AND month(order_date)=9;#检索出2005年9月份的所有行
#Year()是从一个日期中返回年份，Month()从日期中返回月份

数值处理函数

汇总数据

聚集函数

返回特定行的平均值（只能作用于单个列）

select AVG(prod_price) AS avg_price From products;
#AVG()忽略列值为NULL的行
select AVG(distinct prod_price)AS avg_price from products where vend_id=1003;
#DISTINCT，只包含 不同的值，适用于所有聚集函数

COUNT()函数：如果指定列名，指定列的值为NULL被忽略，不计入总数，如果用 * 号则不忽略

SUM函数：

select SUM(item_price*quantity) AS total_price From orderitems where order_num=20005;
#sum()也可以用来合计计算值

组合聚集函数

MAX(prod_price)AS price_max,AVG(prod_price)AS price_avg from products;

分组数据

分组： 分为多个逻辑组，然后对每个组聚集计算

select vend_id,count(*) AS num_prods from products group by vend_id
#有group by函数表示对每个vend_id排序并分组，导致对每个vend_id进行一次num_prods计算
#否则将输出商品总数

（注：1.GROUP BY会自动分组 2.可包含任意数目的列，即多次分组（但不能使聚集函数） 3.建立了分组，将以组形式返回，不能返回单个 4.select语句中所有列都要在GROUP BY中给出，除聚集函数，5，NULL值将会作为一个分组返回 6.GROUP BY在where之后，Order by 之前）
（注：测试后，GROUP BY子句可不给出SELECT中所有子句，可根据自己需要进行分组，哪个在前就先对哪个进行分组）
过滤分组：
HAVING子句：HAVING过滤分组（WHERE过滤行或者说HAVING在分组后过滤，WHERE在分组前过滤）

select cust_id,count(*) AS orders from orders group by cust_id HAVING count(*)>=2;
#过滤COUNT（*）>=2以上的

使用子查询

子查询：嵌套select语句，将select语句返回结果用于另一条select语句的WHERE子句
子查询中子句可查询另一表中数据
（注：可多层嵌套，但是不建议过多使用，性能会下降）

select cust_name,cust_contact from customers where cust_id in(
select cust_id from orders where order_num IN(
select order_num from orderitems where prod_id='TNT2'));
#接下来给出联结的处理方式
select cust_name,cust_contact from customers, orders ,orderitems
where customers.cust_id=orders.cust_id And orderitems.order_num=orders.order_num AND prod_id='TNT2';
#select后给出要查询的信息，from后接从哪寻找信息，where后联结表，再加限定条件

创建计算字段使用子查询：

select cust_name,cust_state,(select count(*)from orders where 
orders.cust_id=customers.cust_id)AS orders from customers order by cust_name;

注：本处orders.cust_id=customers.cust_id使用了完全限定列名，因为本次查询是一次相关子查询（涉及外部查询），cust_id具有二义性，所以必须使用完全限定列名

联结表 组合查询

外键：外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的联系。
联结：一种机制，用来在一条select语句中关联表，因此称为联结。
创建联结：
两个表之间的联结，称为等值联结/内部联结

select vend_name,prod_name,prod_price from vendors,products where vendors.vend_id=products.vend_id order by vend_name,prod_name;
#from 后面列出了两个表，表用where语句正确联结
select vend_name,prod_name,prod_price from vendors inner join products on vendors.vend_id=products.vend_id;
#采用 INNER JOIN 语法的联结形式

联结多个表
注：联结表越多，性能下降越厉害

select prod_name,vend_name,prod_price,quantity from orderitems,products,vendors 
where products.vend_id=products.prod_id and order_num=2005;
#多个表联结

自联结：
自联结在某些情况下比子查询快

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 INNER join orders ON customers.cust_id=orders.cust_id;
#内部联结，检索不出没有订单的客户
select customers.cust_id ,orders.order_num from customers left outer join orders ON customers.cust_id=orders.cust_id;
#外部联结，用OUTER JOIN来指定，能检索出没有订单的客户

使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字，指定包括其所有行的表。
上面代码就指定了从OUTER JOIN 左边的表（即customers表）中选择所有行
带聚集函数的联结

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;
#检索所有客户及各个客户名下所有订单数
#联结customers和orders表，然后GROUP BY按客户分组数据

组合查询
组合查询称为并或复合查询
使用UNION：在各条select语句间放上关键字UNION，这样能将它们的结果组合成单个结果集。

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 会自动去除重复的行，想返回全部行，使用UNION ALL

全文本搜索

注：InnoDB 不支持全文本搜索。MyISAM支持全文本搜索效率高，但不支持事务和外键
MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行。(需要随数据不断更新索引)

CREATE TABLE produtnotes#定义表
(
note_id   int       NOT NULL auto_increment,
prod_id   char(10)  NOT NULL,
note_date datetime  NOT NULL,
note_text text      NULL,
primary key(note_id),
fulltext(note_text)  #根据FULLTEXT对它进行索引，不要在导入数据时用索引，应在导入所有数据再索引，有利于更快导入数据
)engine=MyISAM;
select note_text from productnotes where match(note_text) against('rabbit');
#Match(note_text)
#Against('rabbit')指定要使用的搜索表达式,词rabbit作为搜索文本
#结果返回包含词rabbit的行

注：Match()值必须与FULLTEXT中相同，如果多个，次序也要正确
上面程序同样可以用LIKE完成搜索，但是全文本搜索会返回以文本匹配的良好程度的数据。比如rabbit作为第3个词比作为第20个词的等级高。

查询扩展
会返回可能相关的结果

select note_text from productnotes where match(note_text) against('anvils' with query expansion);
#返回的第一行有完全匹配的词，第二行可能就含有第一行的词，但不含要匹配的词

布尔文本搜索
关键字：IN boolean mode

匹配词heavy，排斥rope

select note_text from productnotes where match(note_text)against('heavy ' IN boolean mode);

select note_text from productnotes where match(note_text)against('+rabbit +bait'IN boolean mode);
#匹配rabbit和bait
select note_text from productnotes where match(note_text)against('rabbit bait'IN boolean mode);
#匹配rabbit和bait中一个就行
select note_text from productnotes where match(note_text)against('"rabbit bait"'IN boolean mode); 
#匹配短语rabbit bait，个人尝试，在两个单词中间，或前，后加空格，没有影响，但是不能让两个单词连在一起，也不能调换顺序
select note_text from productnotes where match(note_text)against('-(rabbit bait)'IN boolean mode); 
select note_text from productnotes where match(note_text)against('+Customers -"rabbit bait"'IN boolean mode); 
#单独的'-(rabbit bait)'不会有任何输出，要至少指明句中含有某个单词
select note_text from productnotes where match(note_text)against('>rabbit <carrot'IN boolean mode); 
#匹配rabbit和carrot，增加前者等级，降低后者等级

注：1.索引全文本数据时，短词（3个或3个以下字符）被忽略且从索引中排除。
2.MySQL有个一内建的非用词列表，在全文本数据时被忽略
3.一个词出现在50%的行中，则将它作为一个非用词忽略，但此规则不用于IN BOOLEAN MODE.
4.行数少于3行，则全文本搜索不返回结果.
5.忽略词中单引号，例如don’t索引为dont
6.仅在MyISAM数据库引擎中支持全文本。

插入 更新 删除数据

INSERT INTO 语句用于向表中插入新记录。
第一种形式无需指定要插入数据的列名，只需提供被插入的值即可：

INSERT INTO table_name
VALUES (value1,value2,value3,...);

第二种形式需要指定列名及被插入的值：

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

注：第二种更加安全，但也更加安全，第一种完全靠顺序插入，第二种则是根据列名，即使表结构改变，还是能正确工作
插入多条数据，每组值用一对圆括号括起来，用逗号分隔

UPDATE 语句用于更新表中特定行或者所有行

UPDATE table_name  #要更新的表
SET column1=value1,column2=value2,...#列名和他们的新值
WHERE some_column=some_value;#过滤条件
update customers set cust_name='The Fudds',cust_email='elmer@fudd.com'  where cust_id=10005;

update customers set cust_email=null where cust_id=10005;
#将某个值设为NULL即相当于删除

DELETE 语句用于删除表中的行（只能删除行，删除列用UPDATE）

DELETE FROM table_name #要删除的数据的表名
WHERE some_column=some_value;#过滤条件

创建和操纵表

CREATE TABLE name #表名
（column type NULL  # 列名，数据类型 ,指明是否接受NULL值，NOT NULL表示不接受
column type NULL AUTO_INCREMENT#本列每当增加一行时自动增量
column type NULL DEFAULT#指定默认值
PRIMARY KEY (column) #关键字指定主键
）ENGINE=InnoDB;#指定引擎，忽略会使用默认引擎，大部分是MyISAM

注：1.InnoDB是可靠的事物引擎，不支持全文本
2.MEMORY等同于MyISAM，数据存储在内存，所以适合临时表。
3.MyISAM是一个性能极高的引擎，不支持事务处理，支持全文本搜索。
更新表

ALTER TABLE vendors ADD vend_phone CHAR(20);
#增加一个名为vend_phone的列，必须明确其数据类型
ALTER TABLE vendors DROP COLUMN vend_phone ;
#删除列
DROP TABLE customer2;
#删除表 
RENAME TABLE customers2 TO customers;
#重命名表 

使用视图

视图是虚拟的表，只包含使用时动态检索数据的查询。例如把多个查询包装成一个虚拟表。
应用：1.重用SQL语句。2.简化复杂的SQL操作。3.使用表的组成部分而不是整个表。4.保护数据。

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的视图，它联结三个表，返回已订购了任意产品的客户
select cust_name,cust_contact from productcustomers where prod_id='TNT2';
#从视图从检索特定数据

视图和计算字段

create view orderitemsexpanded AS select order_num,prod_id,quantity,item_price ,quantity*item_price AS expanded_price from orderitems;
select *from orderitemsexpanded where order_num=20005;

存储过程

使用存储过程的好处：简单（简化复杂操作），安全（限制对基础数据访问，减少数据讹误），高性能。
缺陷：语句复杂，可能没有权限（用户有的使用权限，没有创建存储过程的权限）。

DELIMITER //    #DEKUNUTER //告诉命令行使用程序使用//作为新的语句结束分隔符
#这是为了避免命令行实用程序要结束存储过程自身内的；字符
create PROCEDURE productpricing()  begin
select avg(prod_price) AS priceaverage from products;
End //
DELIMITER ;   #改回分隔符
call productpricing() ;#使用存储过程

存储过程可以显示结果，也可以不显示结果，存储过程可以有参数，也可以没有参数

DELIMITER //
drop procedure productpricin//   #不删除存储过程无法再创建它
create procedure productpricing(
OUT pl decimal(8,2),out ph decimal(8,2),out pa decimal(8,2))#三个参数
#关键字OUT指出相应的参数用来从存储过程传出一个值（返回给调用者）
#IN表示传递给存储过程，INOUT存储过程传入传出
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//
delimiter ;
call productpricing(@pricelow,@pricehigh,@priceaverage);
#为调用此修改过的存储过程，必须指定3个变量名
#所有MySQL变量都必须以@开始
select @priceaverage;#输出平均价格

使用IN和OUT参数

delimiter //
create procedure ordertotla(In onumber InT,OUT ototal DECIMAL(8,2))
begin
select sum(item_price*quantity)
from orderitems
where order_num=onumber into ototal;
end//
delimiter ;
Call ordertotla(20005,@total);#传入20005，total用于存储传出来数据的变量名
select @total;

例子：获得合计，把营业税添加到合计，返回合计（带税或者不带税）

delimiter //
-- Name:ordertotal
-- parameters:onumber=order number
--            taxable=0 if not taxable,1 if taxable
--            otootal =order total variable
create procedure ordertotal(
In onumber int,
in taxable boolean,#如果要增税则为真，否则为假（0表示假，非零为真）
out ototal decimal(8,2)
)comment 'Obtain order total,optionally adding tax'
#comment非必需，如果给出，将在SHOW PRICEDURE STATUS的结果中显示
begin
-- declare variable for total
declare total decimal(8,2);
-- declare tax percentage
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, so add taxrate to the total
select total+(total/100*taxrate)into total;
end if ;
-- and finally ,save to out variable
select  total into ototal;
end //
delimiter ;
call ordertotal(20005,0,@total);
select @total;
call ordertotal(20005,1,@total);
select @total;
show create procedure ordertotal;#检查存储过程

使用游标

游标（cursor）：是一个存储在MySQL服务器上的数据库查询，不是一条SELECT语句，而是被该语句检索出来的结果集。