mySQL必知必会笔记

9.9 Sat 创建 B章的样例表 CREATE DATABASE crash_course 成功了 不知道为啥必须有下划线 不然就error1064 用的5.5版本

USE crash_course

执行脚本文件

source F:\数据分析师\mysql_scripts\create.sql query OK 成功了 可喜可贺 source F:\数据分析师\mysql_scripts\populate.sql 成功就是用populate.sql文件填充各个新表

3.3了解数据库和表

SHOW DATABASES; 知道这个数据库都有什么表

SHOW TABLES; 获得一个数据库内的表的列表

SHOW COLUMNS FROM customers; 列出各列的信息

第4章 检索数据

4.2 检索单个列

SELECT prod_name FROM products;

4.3检索多个列 SELECT prod_id,prod_name,prod_price FROM products;

4.4 检索所有列

SELECT * FROM products;

4.5 检索不同的行

SELECT vend_id FROM products;

SELECT DISTINCT vend_id FROM products;

 

4.6限制结果

SELECT prod_name FROM products LIMIT 5; SELECT prod_name FROM products LIMIT 5,5; 检索从第五行开始的五行

4.7 使用完全限定的表名

SELECT products.prod_name FROM products;

SELECT products.prod_name FROM crash_course.products;

第5章 排序检索数据

5.1 排序数据 SELECT prod_name FROM products ORDER BY prod_name; 字母顺序排序

5.2 按多个列排序 SELECT prod_id, prod_price,prod_name FROM products ORDER BY prod_price,prod_name;

5.3 指定排序方向

SELECT prod_id, prod_price,prod_name FROM products ORDER BY prod_price DESC; 按价格以降序排列

多个列以降序排列产品 再对产品名排序

SELECT prod_id, prod_price,prod_name FROM products ORDER BY prod_price DESC,prod_name; DESC不管多列

确定最大（高）值 SELECT prod_id, prod_price,prod_name FROM products ORDER BY prod_price DESC LIMIT 1;

 

9.10

第6章 过滤数据 如何使用SELECT 语句的WHERE子句指定搜索条件。 6.1 使用WHERE子句 SELECT prod_name,prod_price FROM products WHERE prod_price=2.50;

6.2 WHERE 子句操作符 6.2.1检查单个值 SELECT prod_name,prod_price FROM products WHERE prod_name ='fuses';

列出价格小于10美元的所有产品 SELECT prod_name,prod_price FROM products WHERE prod_price<=10;

6.2.2不匹配检查 列出不是由供应商1003制造的所有产品： SELECT vend_id,prod_name FROM products WHERE vend_id <>1003; <> 也可以用！=代替

6.2.3 范围值检查 检索价格在5美元和10美元之间的所有产品

SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;

6.2.4空值检查 SELECT prod_name FROM products WHERE prod_price IS NULL;

SELECT cust_id FROM customers WHERE cust_email IS NULL;

第7章 数据过滤 7.1 组合WHERE子句 7.1.1 AND操作符 检索由供应商1003制造且价格小于等于10美元的所有产品名称和价格

SELECT prod_id,prod_name,prod_price FROM products WHERE vend_id=1003 AND prod_price <=10; AND操作符可以多个使用

7.1.2 OR操作符 检索由任意指定供应商制造的所有产品的产品名和价格 SELECT prod_name,prod_price FROM products WHERE vend_id =1002 OR vend_id =1003;

7.1.3 计算次序 列出价格为10美元含以上且由1002或1003制造的所有产品 SELECT prod_name,prod_price FROM products WHERE vend_id =1002 OR vend_id =1003 AND prod_price >=10; 但是是不正确的 因为会优先AND

所以 SELECT prod_name,prod_price FROM products WHERE（vend_id =1002 OR vend_id =1003） AND prod_price >=10;

7.2 IN操作符 圆括号再where子句中还有另外一种用法，IN操作符用来指定条件范围 范围中的每个条件都可以进行匹配 检索供应商1002，1003制造的所有产品 表达式：IN（，，） SELECT prod_name,prod_price FROM products WHERE vend_id IN(1002,1003) ORDER BY prod_name;

其结果等同于OR的结果， SELECT prod_name,prod_price FROM products WHERE vend_id =1002 OR vend_id =1003;

7.3 NOT操作符 NOT是否定它之后所有条件

列出除1002 1003之外的所有供应商制造的产品价格 SELECT prod_name, prod_price FROM products WHERE vend_id NOT IN(1002,1003) ORDER BY prod_name;

第8章 用通配符进行过滤 8.1 LIKE操作符 通配符：用来匹配值的一部分的特殊字符。 搜索模式：有字面值、通配符或两者组合构成的搜索条件。

8.1.1 百分号（%）通配符 表示任何字符出现任意次数 例 为了找出以词jet起头的产品可以使用 SELECT prod_id,prod_name FROM products WHERE prod_name LIKE 'jet%' 注意区分大小写

SELECT prod_id，prod_name FROM products WHERE prod_name LIKE '%anvil%';

 

SELECT prod_id，prod_name FROM products WHERE prod_name LIKE 's%e';

NULL啥也不能匹配 8.1.2 下划线_通配符

_总是匹配一个字符 不能多也不能少

8.2使用通配符的技巧

不要过度使用 以通配符开始的搜索模式是最慢的 注意通配符的位置

第9章 用正则表达式进行搜索 9.1正则表达式介绍

9.2MYSQL正则表达式

检索prod_name包含文本1000的所有行

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; .000是正则表达式语言中一个特殊的字符 匹配任意一个字符

9.2.2进行OR匹配

SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name; 搜索两个串之一 使用| 也可以多个1000|2000|3000

9.2.3匹配几个字符之一 匹配1Ton或2ton或3 SELECT prod_name FROM products WHERE prod_name REGEXP '[123]Ton' ORDER BY prod_name;

或者'1|2|3 Ton'

9.2.4匹配范围 匹配1-5 SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5]Ton' ORDER BY prod_name;

9.2.5匹配特殊字符 如果要找出含有.字符的值 必须在前面加\\ SELECT vend_name FROM vendors WHERE vend_name REGEXP'\\.' ORDER BY vend_name;

SELECT vend_name FROM vendors WHERE vend_name REGEXP'\\r' ORDER BY vend_name;

9.2.6匹配字符类 p58页 SELECT vend_name FROM vendors WHERE vend_name REGEXP'[:alnum:]' ORDER BY vend_name;

9.2.7匹配多个实例 匹配0-9和sticks还有后面的 SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9]sticks?\\)' ORDER BY prod_name;

匹配连在一起的4位数字 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][0-9][0-9][0-9]' ORDER BY prod_name;

9.2.8定位符 找出以一个数（包括小数）开始的所有产品

SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name;

第10章创建计算字段

10.2拼接字段 concatenate

例如 vendors表包含供应商名和位置信息，假如要生成一个供应商报表，需要在供应商的名字中按照name（location）这样的格式列出供应商的位置。 此报表需要单个值。但表中数据储存再两个列vend_name和vend_country中，此外需要用括号将vend_country括起来

SELECT Concat(vend_name,'(',vend_country,')') FROM vendors ORDER BY vend_name;

删除数据右侧多余的空格来整理数据

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;

10.3执行算数计算 例如 order表包含收到的所有订单，orderitems表包含每个订单中的各项物品，检索订单号20005中的所有物品 SELECT prod_id,quantity,item_price FROM orderitems WHERE order_num= 20005;

汇总物品的价格（单价乘以订购数量） SELECT prod_id,quantity,item_price, quantity* item_price AS expanded_price FROM orderitems WHERE order_num=20005; 加减乘除 +-*/

第11章 使用数据处理函数 11.1 函数 以下类型 *用于处理文本串（删除填充值，转换值为大小写）文本函数 *数值数据上计算（绝对值，代数运算）数值函数 *处理日期和时间值（两个日期差，检查日期有效性）日期和时间函数 *DBMS正使用的特殊信息（用户登录信息，检查版本细节）系统函数

11.2.1文本处理函数 将表中文本转换为大写 SELECT vend_name,Upper(vend_name)AS vend_name_upcase FROM vendors ORDER BY vend_name;

常用的有 Left（）返回串左边的字符 Length（）返回串的长度 Locate（）找出串的一个子串 Lower（）将串转换为小写 LTrim（）去掉串左边的空格 Right（）返回串右边的字符 RTrim（）去掉串右边的空格 Soundex（）返回串的SOUNDEX值 SubString（）返回子串的字符 Upper（）将串转换为大写

例如 匹配所有发音类似Y.Lie的名字 SELECT cust_name,cust_contact FROM customers WHERE Soundex(cust_contact)= Soundex('Y Lie');

11.2.2日期和时间处理函数 AddDate()增加一个日期（天，周）等 AddTime（）增加一个时间（时，分） CurDate()返回当前的日期 CurTime()返回当前的时间 Date()提取日期或日期/时间表达式的日期部分 DateDiff（）返回两个日期之间的天数 Date_Add()给日期添加指定的时间间隔 Date_Format()返回一个格式化的日期或时间串 Day（）返回一个日期的天数部分 DatOffWeek（）对应一个日期 返回对应的星期几 Hour（）返回一个时间的小时部分 Minute（） Month（） Now（） Second（） Time（）返回一个日期时间的时间部分 Year（）返回一个日期的年份部分

SELECT cust_id,order_num FROM orders WHERE order_date = '2005-09-01';

用Date函数 近提取列的日期部分 考虑到上述会有时间在里面 SELECT cust_id,order_num FROM orders WHERE Date(order_date)= '2005-09-01';

检索2005 9月所有订单 SELECT cust_id,order_num FROM orders WHERE Date(order_date)BETWEEN '2005-09-01'AND'2005-09-30';

或者 SELECT cust_id,order_num FROM orders WHERE Year(order_date)=2005 AND Month(order_date)=9;

11.2.3数值处理函数 Abs（）返回一个数的绝对值 Cos()一个角度的余弦 Exp（）一个数的指数值 Mod()返回除操作的余数 Pi()返回圆周率 Rand()随机数 Sin（）角度的正弦 Sqrt（）平方根 Tan（）正切

第12章 汇总数据 12.1聚集函数 AVG（） COUNT（） MAX（） MIN（） SUM() 12.1.1 AVG（）函数 所有产品的平均价格 SELECT AVG(prod_price) AS avg_price FROM products;

求特定供应商所提供的平均价格 SELECT AVG(prod_price)AS avg_price FROM products WHERE vend_id =1003;

12.1.2 COUNT函数 COUNT（*）对表中的行计数 不管是不是空值 COUNT（column)忽略NULL值

SELECT COUNT(*) AS num_cust FROM customers;

SELECT COUNT(cust_email) AS num_cust FROM customers;

12.1.3 MAX() SELECT MAX(prod_price) AS max_price FROM products;

12.1.4 MIN() SELECT MIN(prod_price) AS min_price FROM products;

12.1.5 SUM() orderitems包含订单中实际的物品，每个物品有相应的数量（quantity） 检索所订购物品的总数（所有quantity之和）订单号20005 SELECT SUM(quantity)AS items_ordered FROM orderitems WHERE order_num =20005;

合计计算值 订单总金额 SELECT SUM(item_price*quantity) AS total_price FROM orderitems WHERE order_num = 20005;

12.2聚集不同的值 返回特定供应商提供的产品的平均价格 SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id =1003;

12.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;

第13章 分组数据 13.1 数据分组 13.2 创建分组 计算每个供应商提供的产品数 SELECT vend_id,COUNT(*)AS num_prods FROM products GROUP BY vend_id;

GROUP BY必须出现在WHERE之后 ORDER BY之前

SELECT vend_id,COUNT(*)AS num_prods FROM products GROUP BY vend_id WITH ROLLUP;

13.3 过滤分组 过滤出有两个以上订单的cust_id

SELECT cust_id,COUNT(*)AS orders FROM orders GROUP BY cust_id HAVING COUNT(*)>=2;

WHERE在分组前进行过滤 HAVING在分组后进行过滤 列出具有2个（含）以上产品，价格为10含以上的产品供应商 SELECT vend_id,COUNT(*)AS num_prods FROM products WHERE prod_price>=10 GROUP BY vend_id HAVING COUNT(*)>=2;

13.4分组和排序

检索总计订单价格大于等于50的订单的订单号和总计订单价格 SELECT order_num,SUM(quantity*item_price)AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50;

为按总计订单价格排序输出，需要添加ORDER BY SELECT order_num,SUM(quantity*item_price)AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50 ORDER BY ordertotal;

13.5 SELECT子句顺序 SELECT FROM WHERE GROUP BY HAVING ORDER BY LIMIT

第14章 使用子查询

14.2 利用子查询进行过滤 列出订购物品TNT2的所有客户 第一步 检索包含物品TNT2的所有订单编号 SELECT order_num FROM orderitems WHERE prod_id ='TNT2'; 检索具有前一步列出的订单编号的所有客户ID SELECT cust_id FROM orders WHERE order_num IN(20005,20007);

合并成一个 SELECT cust_id FROM orders WHERE order_num IN(SELECT order_num                    FROM orderitems                    WHERE prod_id ='TNT2');

检索客户ID信息

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'));

14.3作为计算字段使用子查询 需要显示customers表中每个客户的订单总数，订单与相应的客户ID存储在orders表中。 1）从customers表中检索客户列表 2）对于每个客户，统计其在order表中的订单数目。

SELECT cust_id FROM customers GROUP BY cust_id;

按照客户名和客户州统计出客户的订单数 SELECT cust_name,cust_state,(SELECT COUNT(*)                              FROM orders                              WHERE orders.cust_id =customers.cust_id)AS orders FROM customers ORDER BY cust_name; 注意限制列名

第15章 联结表

15.2创建联结 SELECT vend_name,prod_name,prod_price FROM vendors,products WHERE vendors.vend_id=products.vend_id ORDER BY vend_name,prod_name;

不要忘了WHERE 不然会输出笛卡儿积 15.2.2 内部联结 和上面一样的意思和输出

SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id=products.vend_id;

15.2 联结多个表 首先列出所有表 然后定义表之间的关系 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;

第14章的例子 返回订购产品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';

第16章 创建高级联结

16.1使用表别名 为了省字符 在FROM AS别名

16.2使用不同的类型链接 16.2.1自联结 找到你怀疑出现问题的某产品ID 以及该供应商生产的其他产品 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';

16.2.2自然联结 不知道是什么鬼

 

 

 16.2.3外部联结 SELECT customers.cust_id,orders.order_num FROM customers RIGHT OUTER JOIN orders  ON orders.cust_id=customers.cust_id;

16.3使用带聚集函数的联结 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;

第17章 组合查询 17.2.1使用UNION 用UNION组合SELECT语句

例如需要加个小于等于5的所有物品的一个列表 其中还想包括供应商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) ORDER BY vend_id，prod_price;

以上等同于 SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price<=5  OR vend_id IN(1001,1002);

17.2.3包含或取消重复的行 UNION是默认不包含重复行的 如需要改变用UNION ALL

17.2.4对组合查询结果排序 ORDER BY 只能出现在最后一个SELECT的最后一行

第18章 全文本搜索 用全文本搜索功能进行高级的数据查询和选择。 18.2使用全文本搜索  1：mysql支持几种基本的数据库引擎，并非所有的引擎支持全文本搜索，比如引擎myisam和innodb，只有myisam引擎支持全文本搜索。全文本搜索比like 和正则表达式具有更强的控制能力。          2：为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断的更新索引。在对表进行适当的设计后，mysql会自动进行所有的索引和重新索引。          3：一般在创建表时启用全文本搜索。create table语句接受fulltext子句，他给出被索引列的一个逗号分隔的列表。比如： create table productnotes (   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) ) 为了进行全文本搜索，mysql根据子句fulltext(note_text)的指示，对它进行索引。这里fulltext索引单个列，如果需要也可以指定多个列。

 4：在索引之后，使用两个函数match和against进行全文本搜索，其中match指定被搜索的列，against指定要使用的搜索表达式。 比如： select note_text from productnotes where match(note_text) against（'rabbit'); match(note_text)指定mysql针对指定的列进行搜索， against(‘rabbit’)指定词rabbit作为搜索文本。传递给match的值必须与fulltext定义中的相同。如果指定多个列，则必须列出它们，而且次序必须正确。  5：全文本搜索的时候，返回的顺序是按照匹配的良好程度进行排序的数据。尽管两个行都包含rabbit，但是包含词rabbit作为第3个词的行要比作为第20个词的行高。全文本搜索的一个重要部分就是对结果配需，具有较高等级的行先返回。比如：  select note_text match(note_text) against(‘rabbit’) as rank from productnotes;

半角 SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit');

 

第19章 插入数据 19.1数据插入 INSERT 插入完整行 插入行的一部分 插入多行 插入某些查询结果

19.2插入完整行 INSERT INTO tablename(row1,row2...) VALUES('a',        'b'.        ...       NALL);如有些行不想插入值 可以在（row1,row2...中忽略）VALUES同理

19.3 插入多个行  19.2内容可以并列执行

19.4插入检索出的数据 例如将custnew中所有数据导入customers

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;

第20章 更新和删除数据

20.1更新数据 更新表中特定行 更新表中所有行 更新客户10005的电子邮件 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; 删除cust_email列中的值

20,2删除数据 DELETE FROM customers WHERE cust_id=10006; DELETE不需要列名或通配符，删除整行而不是删除列。为了删除指定的列，使用UPDATE语句。

MYSQL没有撤销 小心使用

第21章 创建和操纵表

使用具有交互式创建和管理的工具 直接MYSQL语句操纵 21.1.1 表创建基础 CREATE TABLE 表名 （列名 字符型式 是否NULL 是否自动增加   列名 字符型 是否NULL,  。。。。  PRIMARY KEY（列名,列名-如果有的话） ）ENGINE=InnoDB;

last_insert_id()

21.1.5 增加默认值 列名 字符型式 是否NULL 是否自动增加   列名 字符型 是否NULL DEFAULT 1，  。。。。  PRIMARY KEY（列名,列名-如果有的话） ）ENGINE=InnoDB;

在没给出数量的情况下 输入1的意思

21.1.6 引擎类型 InnoDB MEMORY MyISAM

21.2 更新表 ALTER TABLE 表名 ADD 列名 字符属性；

删除刚刚添加的列 ALTER TABLE 还是那个表名 DROP COLUMN 刚才的列名；

ALTER TABLE 常见用途是定义外键

ALTER TABLE xx ADD CONSTRAINT 列名 FOREIGN KEY（列）REFRENCE 另一个表的名（此表中的某列） 21.3 删除表  DROP TABLE xxx

21.4 重命名  RENAME TABLE xxx TO ooo；

第22章 使用视图

包装一个虚拟表 SELECT cust_name,cust_contact FROM productcustomers WHERE prod_id='TNT2';

需要创建一个联结三个表的视图

怎么创建这个productcustomers呢 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;

22.2.2 用视图重新格式化检索出的数据 CREATE VIEW vendorlocations AS SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')')        AS vend_title FROM vendors ORDER BY vend_name;

然后每次都用这个视图 SELECT* FROM vendorlocations;

22.2.3使用视图过滤不想要的数据 CREATE VIEW customeremaillist AS SELECT cust_id,cust_name,cust_email FROM customers WHERE cust_email IS NOT NULL;

SELECT * FROM customeremaillist;

22.2.5使用试图与计算字段 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;

分组（GROUP BY和HAVING 联结 子查询 并 聚集函数（min count sum distinct 导出 计算列 布恩那个更新视图

第23章 使用存储过程

创建存储过程 DELIMITER // CREATE PROCEDURE productpricing() BEGIN   SELECT Avg(prod_price)AS priceaverage   FROM products; END//

DELIMITER;

执行存储过程 调用 CALL productpricing(@pricelow,@pricehigh,@priceaverage);

删除 DROP PROCEDURE xxx

 

第24章 游标 第25章 触发器

某表发生改动时自动处理

CREATE TRIGGER

 

第26章 事务处理