mysql必知必会

第一章
数据库（database） 保存有组织的数据的容器（通常是一个文件或一组文件）。
数据库软件称为DBMS（数据库管理系统）。数据库是通过DBMS创建和操纵的容器。
表（table） 某种特定类型数据的结构化清单，用来存储某种特定类型的数据。
模式（schema） 关于数据库和表的布局及特性的信息。
列（column） 表中的一个字段。所有表都是由一个或多个列组成的，列中存储着表中某部分的信息。
行（row） 表中的一个记录。
主键（primary key）一列（或一组列），其值能够唯一区分表中每个行。
表中的任何列都可以作为主键，只要它满足以下条件：
 任意两行都不具有相同的主键值；
 每个行都必须具有一个主键值（主键列不允许NULL值）

第二章
mysql是一种DBMS
{use 数据库名;} 使用数据库
{show databases;} 返回可用数据库的列表
{show tables;} 获得一个数据库内的表的列表
{show columns from customers;/descirbe customers;} 显示表列信息

第四章 检索数据 SELECT
{SELECT prod_name FROM products;} 检索单个列
{SELECT prod_name,prod_id,prod_price FROM products;} 检索多个列
{SELECT * FROM products;} 检索所有列    [* ：通配符]
{SELECT DISTINCT vend_id FROM vendor;} 检索不同的列
注：1.DISTINCT 必须直接放在列名前面
  2.不能部分使用DISTINCT 如SELECT DISTINCT vend_id,prod_price会将所有列显示出来
限制结果 LIMIT
{SELECT prod_name FROM products LIMIT 5;} 返回前5行（0-4）
{SELECT prod_name FROM products LIMIT 5,5;} 返回从第5行开始的5行，第一个数为开始的位置，第二个数为检索的行数（5-9）
注：1.LIMIT 1,1 检索出第二行，因为行号从0开始
  2.LIMIT5,5;也可以写成LIMIT 5 OFFSET 5;
{SELECT products.prod_name FROM crashcourse.products; } 使用完全限定的表名和列明

第五章 排序检索数据 ORDER BY
{SELECT prod_name FROM products ORDER BY prod_name;} 对prod_name列按字母顺序排序
{SELECT prod_id,prod_price,prod_name FROM porducts ORDER BY prod_price,prod_name; } 检索3个列，并按其中两个列对结果进行排序——首先按价格，然后再按名称排序。价格相同时，才按名称排序
{SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price DESC;} 按价格降序排列
注：1.DESC关键字只应用到直接位于其前面的列名，如果想在多个列上进行降序排序，必须对每个列指定DESC关键字
   ASC是升序排列，因为升序是默认的，所以可以不指定
{SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1;} 找出最贵的物品
注：在给出ORDER BY子句时，应该保证它位于FROM子句之后。如果使用LIMIT，它必须位于ORDER BY之后。

第六章 过滤数据 WHERE
{SELECT prod_name,prod_price FROM products WHERE prod_price=2.5;} 返回价格等于2.5的行
注：1.在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后
  2.where子句操作符

操作符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN	在指定的两个值之间

{SELECT prod_name,prod_price FROM products WHERE prod_name=‘fuses’;}单引号，如果将值与串类型比较，需使用单引号
{SELECT prod_name,prod_price FROM products WHERE prod_price<10;}
{SELECT prod_name,prod_price FROM products WHERE prod_price<=10;}
{SELECT vend_id,prod_name FROM products WHERE vend_id<>1003;}不匹配检查
{SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;}范围值检查
空值检查
在创建表时，表设计人员可以指定其中的列是否可以不包含值。在一个列不包含值时，称其为包含空值NULL
注：NULL 无值（no value），它与字段包含0、空字符串或仅仅包含空格不同。
{SELECT prod_name FROM products WHERE prod_price IS NULL;} 返回没有价格（空prod_price字段，不是价格为0）的所有产品
注：在通过过滤选择出不具有特定值的行时，无法返回具有NULL值的行。

第七章 数据过滤 WHERE + AND/OR
为了通过不止一个列进行过滤，可使用AND操作符给WHERE子句附加条件。
{SELECT prod_id,prod_price,prod_name FROM products WHERE vend_id=1003 AND prod_price<=10;}
{SELECT prod_id,prod_price,prod_name FROM products WHERE vend_id=1003 OR prod_price<=10;}
注：AND 和 OR 同时使用时，AND具有更高的优先级
{SELECT prod_name,prod_price FROM products WHERE (vend_id=1003 OR vend_id=1001) AND prod_price >=10;}
IN操作符
IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。IN取合法值的由逗号分隔的清单，全都括在圆括号中。
{SELECT prod_name,prod_price FROM products WHERE vend_id IN(1002,1003) ORDER BY prod_name;}
NOT操作符
否定它之后所跟的任何条件
{SELECT prod_name,prod_price FROM products WHERE vend_id NOT IN(1002,1003) ORDER BY prod_name;}
注：MySQL支持使用NOT对IN、BETWEEN和EXISTS子句取反

第八章 用通配符过滤
通配符：用来匹配值的一部分的特殊字符
LIKE 操作符
% 通配符，表示任何字符出现任意次数。 可以是0个字符，1个字符，多个字符
{SELECT prod_id,prod_name FROM products WHERE prod_name LIKE ‘jet%’;} 检索任意以jet起头的词
{SELECT prod_id,prod_name FROM products WHERE prod_name LIKE ‘%anvil%’;}
{SELECT prod_name FROM products WHERE prod_name LIKE ‘s%e’;} 匹配以s开头以e结尾
注：1.尾空格可能会干扰通配符匹配。要先将其去除。
  2.WHERE prod_name LIKE '%'也不能匹配用值NULL作为产品名的行。
_ 通配符 匹配单个字符,不能多也不能少
{SELECT prod_id,prod_name FROM products 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 REGEXP ‘.000’ ORDER BY prod_name;} '.‘表示匹配任意一个字符
注：mysql中正则表达式匹配默认不区分大小写，若想区分大小写，应加BINARY关键字 WHERE prod_name REGEXP ‘JetPack .000’
{SELECT prod_name FROM products WHERE prod_name REGEXP ‘1000|2000’ ORDER BY prod_name;} 表示匹配其中之一。可以多个条件’1000|2000|3000’
匹配几个字符之一
{SELECT prod_name FROM products WHERE prod_name REGEXP ‘[123] Ton’ ORDER BY prod_name;} 匹配1或2或3
注：1.[]和|都是或的意思，但应注意区分和{SELECT prod_name FROM products WHERE prod_name REGEXP ‘1|2|3 Ton’ ORDER BY prod_name;}的区别
  2. [^123]匹配除这些字符之外的任何东西
匹配范围
[0123456789]或[0-9] 匹配数字0-9
[a-z]匹配任意字符
匹配特殊字符
以\为前导,\-,\.,\|
{SELECT vend_name FROM vendors WHERE vend_name REGEXP ‘\.’ ORDER BY vend_name; }
匹配多个实例
重复元字符

元字符	说明
*	0个或多个匹配
+	1个或多个匹配（等于{1,}）
？	0个或1个匹配（等于{0,1}）
{n}	指定数目的匹配
{n,}	不少于指定数目的匹配
{n,m}	匹配数目的范围

{SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\) ’ ORDER BY prod_name;} s后面跟问号，使s可以有也可以没有，？表示前面的字符出现0次或多次
{SELECT prod_name FROM products WHERE prod_name REGEXP ‘[[:digits:]]{4}’ ORDER BY prod_name; }匹配连在一起的任意四个数字
定位符
匹配特定位置的文本

元字符	说明
^	文本的开始
&	文本的末尾
[[:<:]]	词的开始
[[:>:]]	词的结尾

{SELECT prod_name FROM products WHERE prod_name REGEXP ‘^ [0-9\\.]’ ORDER BY prod_name;} 找出以数字或小数点开始的产品
注：1.^的双重作用：在集合[]中，用来否定该集合，否则用来指串的开始处
  2.使用REGEXP起类似LIKE的作用，用^开始每个表达式，用$结束每个表达式

第十章 创建计算字段
字段，与列同义
拼接字段，Concat()函数
{SELECT Concat(vend_name,’ (’,vend_country, ‘)’) FROM vendors ORDER BY vend_name;} 拼接多个串
RTrim，LTrim，Trim：删除值右边，左边，左右的空格
{SELECT Concat(RTrim(vend_name),’ (’,RTrim(vend_country), ‘)’) FROM vendors ORDER BY vend_name;}
{SELECT Concat(RTrim(vend_name),’ (’,RTrim(vend_country), ‘)’) AS vend_title FROM vendors ORDER BY vend_name;} AS 后跟列别名
执行算术操作
{SELECT prod_id,quantity,item_price quantity*item_price AS expended_price FROM orderitems WHERE order_num=20005;}

第十一章 使用数据处理函数
文本处理函数
日期和时间处理函数
数值处理函数

第十二章 汇总函数

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

AVG()注：1.AVG只用于单个列， AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个AVG()函数
  2.AVG()会忽略列值为NULL的行
COUNT()注：1. 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。
  2.使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。
MAX()MIN()SUM()注：忽略值为NULL的行
聚集不同值 DISTINCT
{SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id=1003;}

第十三章 分组数据 GROUP BY / HAVING
创建分组 GROUP BY
{SELECT vend_id,COUNT() FROM products GROUP BY vend_id;}
注：1.GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
  2.如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
  3.GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
  4.除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。
  5.如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
  6.GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。
过滤分组 HAVING
{SELECT cust_id,COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >=2;}
注：1.这里有另一种理解方法，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。
{SELECT vend_id,COUNT(*) AS num_prods FROM products WHERE prod_price>=10 GROUP BY vend_id HAVING COUNT(*) >=2;}
  2.ORDER BY 子句应该HAVING子句之后
SELECT子句顺序

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否
LIMIT	要检索的行数	否

第十四章 使用子查询
{SELECT * 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_state,(SELECT count(cust_id) FROM orders WHERE orders.cust_id=customers.cust_id) FROM customers GROUP BY cust_id;}

第十五章 联结表
外键：外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。
创建联结
{SELECT vend_name,prod_name,prod_price FROM vendors,products WHERE vendors.vend_idproducts.vend_id ORDER BY vend_name,prod_name;}
内部链接
{SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_idproducts.vend_id;}
联结多个表
{SELECT prod_name,vend_name,prod_price,quantity FROM oderitems,products,vendors WHERE vendors.vend_idproducts.vend_id AND orderitems.prod_idproducts.prod_id AND order_num==20005;}

---

{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 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;}
带聚集函数的联结
{SELECT customers.cust_id,COUNT(orders.order_num) FROM customers LEFT OUTER JOIN orders ON customers.cust_id=orders.cust_id GROUP BY customers.cust_id;}

第十七章 组合查询 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)};
等同于
{SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price<=5 OR vend_id IN (1001,1002);}
注：UNIOIN中的每个查询必须包含相同的列、表达式或聚集函数(不过各个列不需要以相同的次序列出)

第十九章 插入数据 INSERT
插入行
INSERT INTO customers VALUES(NULL,‘Pep E’,‘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,cust_contact,cust_email) VALUES(‘Pep E’,‘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,cust_contact,cust_email) VALUES(‘Pep E’,‘100 Main Street’,‘Los Angeles’,‘CA’,‘90046’,‘USA’,NULL,NULL),(‘M.Nartian’,‘42 Galaxy Way’,‘New York’,‘NY’,‘11213’,‘USA’);
插入检索出的数据
INSERT INTO customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email) SELECT cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email FROM custnew;

第二十章 更新和删除列 UPDATE DELETE
更新数据
UPDATE customers SET cust_email=‘elmer@fudd.com’ WHERE cust_id=10005;
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;
删除数据
DELETE FROM customers WHERE cust_id=10006;

第二十一章 创建和操纵表
CREATE TABLE customers
(
cust_id int NOT NULL AUTO_INCREMENT ,
cust_name char(50) NOT NULL ,
cust_address char(50) NULL ,
cust_city char(50) NULL ,
cust_state char(5) NULL ,
cust_zip char(10) NULL DEFAULT ‘a’,
cust_country char(50) NULL ,
cust_contact char(50) NULL ,
cust_email char(255) NULL ,
PRIMARY KEY (cust_id)
) ENGINE=InnoDB;
多个列组成主键
PRIMARY KEY (order_num,order_item)
AUTO_INCREMENT 每个表只允许一个AUTO_INCREMENT列，而且它必须被索引
引擎类型：
InnoDB
MEMORY
MyISAM
外键不能跨引擎
更新表
ALTER TABLE vendors ADD vend_phone char(20);
ALTER TABLE vendors DROP COLUMN vend_phone;
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
删除表
DROP TABLE customers2;
重命名表
RENAME TABLE customers2 TO customers;

第二十二章 使用视图
作用：1.重用sql语句；2.简化复杂sql操作；3.保护数据；4.更改数据格式和标识。
创建视图:CREATE VIEW
查看创建视图的语句:SHOW CREATE VIEW viewname
删除视图:DROP VIEW viewname
更新视图:CREATE OR REPLACE VIEW
利用视图简化复杂的联结
CREATE VIEW productcustomers AS SELECT vend_name,prod_name,prod_price FROM vendors,products WHERE vendors.vend_id==products.vend_id ORDER BY vend_name,prod_name;
SELECT * FROM productcustomers;

第二十三章 使用存储过程
存储过程：为以后的使用而保存的一条或多条MySQL语句的集合。 简单，安全，高性能