《MYSQL必知必会》笔记

2018-07-22 本文已影响0人芒果芭乐

MYSQL必知：

MSQL不区分大小写，在执行匹配的时候同样不区分
MYSQL语句必须以 ; 结束
Sql语句忽略空格
子句（clause）：sql语句由子句构成，一个子句通常由一个关键字和所提供的数据组成
SELECT语句中子句有严格的排序，次序不对会报错。SELECT子句的顺序：SELECT—FROM—WHERE—GROUP BY—HAVING—ORDER BY—LIMIT
外键foreign key：外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系

第三章：使用MYSQL

选择数据库：USE关键字，只有先用USE打开某一个数据库，才能读取其中的数据
返回一个数据库列表：

SHOW DATABASES;

返回某个数据库中的表列表：

SHOW TABLES;

显示表列：

SHOW COLUMNS FROM table_name; 或者 DESC table_name;

其他SHOW语句：

SHOW STATUS;显示服务器状态信息
SHOW CREATE DATABASE/TABLE;显示创建数据库/表的mysql语句
SHOW GRANTS;显示用户的权限
SHOW ERRORS/WARNINGS;显示服务器错误/警告信息

第四章：检索数据

SELECT语句：从一个或多个表中检索信息

SELECT prod_name FROM products;检索单个列
SELECT prod_id, prod_name,prod_price FROM products;检索多个列：不同列之间通过逗号隔开
SELECT * FROM products; 检索所有列：* 为通配符
SELECT DISTINCT vend_id FROM products; 检索不同的行：DISTINCT 表示唯一值，这边只返回vend_id不同的行
SELECT prod_name FROM products LIMIT 5;限制结果： LIMIT 5指示mysql返回值不多于五行，LIMIT 5,5;则指示显示从第五行开始的后五行
SELECT products.prod_name FROM products; 完全限定表名：products.prod_name完全限定表名：prod_name是products表中的一个列

第五章：排序检索数据

ORDER BY子句：取一个或多个列的名字，据此对输出进行排序

SELECT prod_name 
FROM products 
ORDER BY prod_name;

按照多个列排序：

SELECT prod_id,
       prod_name,
       prod_price 
FROM products 
ORDER BY prod_name,prod_price;

先按prod_name排序，再按prod_price排序

指定排序方向（默认的排序方式是升序顺序）：

SELECT prod_id, 
       prod_name,
       prod_price 
FROM products 
ORDER BY prod_name DESC,prod_price;

先按 prod_name降序排序，再按prod_price升序排列。DESC进行降序排序

第六章：过滤数据

WHERE子句：指定搜索条件，它的操作符有 = 等于、<> 不等于、!= 不等于、< 小于、<= 小于等于、> 大于、>= 大于等于、BETWEEN 在指定的两个值之间（BETWEEN 1 AND 10 ：1到10之间的数值）

SELECT prod_id, 
       prod_name,
       prod_price 
FROM products 
ORDER BY prod_name,
         prod_price 
WHERE prod_price=2.5;

只返回prod_price=2.5限定下的搜索值

空值检查（IS NULL）：

SELECT prod_name FROM products WHERE prod_name IS NULL;

第七章：数据过滤

MYSQL允许一个语句中有多个WHERE子句，可以以AND子句或OR子句的方式使用：

SELECT prod_id, 
       prod_name,
       prod_price 
FROM products 
ORDER BY prod_name,
         prod_price 
WHERE prod_price=2.5 AND/OR prod_id>1;

AND与OR有着不同的优先级，AND的优先级比OR高，当使用很多个逻辑操作符的时候，可以使用括号进行分组

IN操作符可以指定条件范围：

SELECT prod_id, 
       prod_name,
       prod_price 
FROM products 
ORDER BY prod_name,
         prod_price 
WHERE prod_price IN (1.0,2.5,3,4,5);

IN中合法值通过括号包括，不同合法值由逗号分隔

NOT操作符可以否定后面所跟的条件

第八章：使用通配符进行过滤

LIKE操作符：为在搜索子句中使用通配符，必须使用LIKE操作符，它指示后面跟的搜索模式利用通配符而不是直接相等匹配进行比较
% 通配符：表示任何字符出现任意次数（0次也算），但不匹配NULL

SELECT prod_id, 
       prod_name,
       prod_price 
 FROM products 
WHERE prod_name LIKE ‘jet%’;

_ 通配符：只匹配单个字符
通配符搜索的处理一般会比前面几章讨论的搜索更费时间

第九章：用正则表达式进行搜索

基本字符匹配：

SELECT prod_id,
       prod_name,
       prod_price 
FROM products 
WHERE prod_name REGEXP ‘.1000’;

REGEXP告诉MYSQL其后面所跟的东西为正则表达式。其中'.' 是正则表达式语言中一个特殊的字符，可以匹配任意一个字符

LIKE与REGEXP之间的差别：LIKE匹配整个列（所寻找的东西必须在列值里面完全匹配，不能多或少，不能只是部分匹配）；REGEXP在列值中匹配，匹配成功会返回相应的行
进行 | （or）匹配：

SELECT prod_id, 
       prod_name,
       prod_price 
FROM products 
WHERE prod_name REGEXP ‘1000|2000’;

匹配几个字符之一（[ ] 括号，另一种形式的or）：

SELECT prod_id,
       prod_name,
       prod_price 
FROM products 
WHERE prod_name REGEXP ‘[123] ton’;#/ [A-Z] [1-9]可以是范围

在mysql中匹配特殊字符要用 \ 进行转义

第十章：创建计算字段

拼接字段CONCAT函数：

SELECT CONCAT(vend_name,’(’,vend_country,’)’) AS name(country) 
FROM products ;#将两列整合成为一列，命名为name(country)

执行算数计算：

SELECT prod_id,
       prod_name,
       prod_price*prod_numbers AS price 
FROM products ;

*、-、+、/ 都可以在mysql中使用，进行计算

第十一章：使用数据处理函数

文本处理函数：

SELECT prod_id,
       prod_name,
       RTrim(prod_price) 
FROM products 
WHERE prod_name LIKE ‘jet%’;

函数：

LFET()返回串左边的字符、
LENGTH()返回串的长度
LOCATE()找出串的子串
LOWER()将串转换为小写
LTRIM()去掉串左边的空格
RIGHT()返回串右边的字符
RTRIM()去掉串右边的空格
SOUNDEX()返回串的soundex值
SUBSTRING()返回子串的字符
UPPER()将串转换为大写

第十二章：汇总数据

聚集函数：

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

COUNT(*)对表中行的数目进行计数，不管是否包含NULL。COUNT(column)对特定列中具有值的行进行计算（忽略NULL）

第十三章：分组数据

创建分组GROUP BY：

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

GROUP BY vend_id表示按vend_id排序并分组数据。

GROUP BY子句的规定：
1、子句中列出来的每个列都必须是检索列或有效的表达式（但不能是聚集函数）
2、SELECT语句中的每个列都必须在GROUP BY子句中给出
NULL会被作为一个分组
过滤分组HAVING：这边不能用WHERE，WHERE过滤指定的是行而不是分组

SELECT vend_id, 
       COUNT(*) AS num_prods 
FROM products 
GROUP BY vend_id HAVING COUNT(*)>=2;

1、 HAVING支持所有WHERE操作符
2、 HAVING在数据分组后过滤，而WHERE在数据分组前过滤

第十四章：使用子查询

子查询：嵌套在其他查询中的查询

SELECT cust_id 
FROM orders 
WHERE order_num IN ( SELECT order_num 
                     FROM orderitems 
                     WHERE prod_id = ‘TNT2’);# 这边先执行括号内的查询。

WHERE子句中对能嵌套的子查询数目没有限制，但是会受到性能限制

作为计算字段使用子查询：

SELECT cust_name,
       cust_state,
       ( SELECT COUNT(*) 
       FROM orders 
       WHERE orders.cust_id = customers.cust_id) AS orders 
FROM customers;

第十五章：联结表

完全限定列名：在引用的列可能出现二义性的时候，必须使用完全限定列名（用一个点分割的表名和列名）
创建联结：

SELECT prod_id, 
       prod_name,
       vend_name 
FROM products，vendors 
WHERE vendors.vend_id = products.vend_id;

这个联结可被称为等值联结，它是基于两个表的相等测试，也被称为内部联结

使用INNER JOIN 子句建立内部联结：

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

联结多个表：

SELECT prod_id, 
       prod_name,
       vend_name,
       prod_price,
       quantity 
FROM products,
     vendors,
     orderitems 
WHERE vendors.vend_id = products.vend_id 
      AND orderitems.prod_id=products.prod_id; #在WHERE中使用AND

第十六章：创建高级联结

使用表别名：

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

FROM 语句中三个表全部具有别名

使用不同类型的联结

https://blog.csdn.net/plg17/article/details/78758593
图解MySQL 内连接、外连接、左连接、右连接、全连接

1、自联结：

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'

自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。
2、自然联结：
标准的联结返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次

SELECT c.*,
       o.order_num,
       o.order_date,
       oi.prod_id,
       oi.quantity,
       OI.item_price
FROM customers AS c,
     orders AS o,
     orderitem AS oi
WHERE c.cust_id = o.cust_id
      AND oi.order_num=o.order_num
      AND prod_id='FB';

自然联结中你只能选择那些唯一的列，一般通过对一个表使用通配符，对所有其他表的列使用明确的子集来完成。
3、外部联结

SELECT   customers.cust_id,
         orders.order_num
FROM customers INNER JOIN orders
ON customers.cust_id=orders.cust_id;

与内部联结关联两个表中的行不同的是，外部联结还包括没有关联行的行。在使用outer join 语法时，必须使用RIGHT或LFET 关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，LEFT相反）

第十七章：组合查询

在MYSQL中允许多个查询（多条SELECT语句），并将结果作为单个查询结果返回。这些组合查询成为并或复合查询。

使用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有着一些规则：
1、UNION中每个查询必须包含相同的列、表达式或聚集函数
2、列数据类型必须兼容

包含或者取消重复的行
UNION会自动从查询结果集中自动去除重复的行
在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句

第十八章：使用全文本搜索

MYSQL可创建指定列中各词的一个索引，然后针对这些词进行搜索

FULLTEXT子句的使用：针对某一列进行索引的建立

CREATE TABLE productnotes
(
note_id   int   NOT NULL AUTO_INCREMENT,
prod_id   char(10)   NOT NULL,
note_date   datetime   NOTNULL,
note_text   text   NULL,
PRIMARY KEY(note_id),
FULLTEXT(note_text)
)

这里FULLTEXT对note_id进行索引的建立，可以同时对多个列进行索引的建立。
可以在创建表的时候指定FULLTEXT，也可以稍后指定。但一般不要在导入数据的时候使用，这样要花掉很多时间，可以先导入数据，再对表进行修改（定义FULLTEXT）

进行全文搜索
在建立索引之后，使用Match()和Against()执行全文本搜索，Match()指定被搜索的列，Against()指定要使用的搜索表达式

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against(‘rabbit’)；

可以同时指定多个列和多个搜索表达式，得到的结果是经过排序的（按照等级）

使用查询拓展
查询拓展可以放宽返回的范围

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against(‘rabbit’ WITH QUERY EXPANSION)；

布尔文本搜索(IN BOOLEAN MODE 子句)
布尔文本搜索可以指定：
1、要排除的词
2、要匹配的词
3、排列提示（对某些词进行等级设定）
4、表达式分组

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against(‘hravy -rabbit*’ IN BOOLEAN MODE )；

这个语句表示匹配hravy而排除以rabbit开头的词。
更多的全文本布尔操作符：

image.png

全文本搜索有着一些重要的使用说明

第十九章：插入数据

插入完整的行
插入完整的行不需要指定列名

INSERT INTO Customers
VALUES(
  NULL
  'Pep E. LaPew',
  '100 Main Street',
  'Los Angeles',
  'CA',
  '90046',
  'USA',
  NULL,
  NULL)

Customers表的第一列是cust_id，为自动增量，但不能省略输入，所以用NULL

按列名插入

INSERT INTO Customer(
  cust_address,
  cust_city,
  cust_state,
  cust_zip,
  cust_country,
  cust_contract,
  cust_email
)
VALUES(
  'Pep E. LaPew',
  NULL,
  '100 Main Street',
  'Los Angeles',
  'CA',
  '90046',
  'USA',
)

给出了列名，并按列名排序填充值，这时可以省略某些列（有默认值与允许定义为NULL的列）

同时插入多行
可以用多个INSERT语句或者：

INSERT INTO Customer(
  cust_address,
  cust_city,
  cust_state,
  cust_zip,
  cust_country,
  cust_contract,
  cust_email
)
VALUES(
  'Pep E. LaPew',
  NULL,
  '100 Main Street',
  'Los Angeles',
  'CA',
  '90046',
  'USA',
)
(
  'Pep E. LaPew',
  NULL,
  '100 Main Street',
  'Los Angeles',
  'CA',
  '90046',
  'USA',
)

插入检索出的数据
可以将查询出的数据插入到另外一个表中

INSERT INTO Customer(
  cust_id,
  cust_contract,
  cust_email,
  cust_name,
  cust_address,
  cust_city,
  cust_state,
  cust_zip,
  cust_country,
)
SELECT cust_id,
  cust_contract,
  cust_email,
  cust_name,
  cust_address,
  cust_city,
  cust_state,
  cust_zip,
  cust_country
FROM custnew;

从custnew表中查询出数据，然后插入Customer中

第二十章：更新和删除数据

UPDATE

UPDATE cusromers
SET cust_email='elmer@fudd.com',
    cust_name='The Fudds'
WHERE cust_id=10005;

一定要注意使用WHERE子句，如果没有这个子句，它将更新整个表
可以在UPDATE中使用子查询
在UPDATE更新出现错误，但是想忽略它继续执行的时候，可以使用IGNORE关键字（没有使用这个关键字，UPDATE将停止，并恢复原来的值）：UPDATE IGNORE customers....

删除数据
DELETE：删除行

DELETE FROM customers
WHERE cust_id =100006;

更新和删除数据都要记得使用WHERE子句，最好先使用SELECT子句先进行测试，以保证正确的删除范围。还要使用强制实施引用完整性的数据库，让MYSQL不允许删除具有与其他表相关联的数据的行

第二十一章：创建和操纵表

创建表
创建表和数据库可以使用交互式软件或mysql语法。
mysql语法使用的是CREATE语句，新表的名字在关键字CREATE TABLE后面给出，表列的名字和定义用逗号分隔。每列的定义以列名（必须唯一）开始，后跟列的数据类型。

CREATE TABLE customers(
  cust_id    int NOT NULL AUTO_INCREMENT, //NOT NULL表示不能为空，AUTO_INCREMENT表示自增
  cust_contract     char(50) NOT NULL,
  cust_email      char(50)  NULL,
  cust_name   char(50)  NULL,
  cust_address      char(50)  NULL, 
  cust_city      char(50)  NULL,
  cust_state      char(50)  NULL,
  cust_zip      char(50)  NULL,
  cust_country      char(50)  NULL DEFAULT china,//DEFAULT指定该列的默认值
  PRIMARY KEY (cust_id，cust_email)  //定义主键
)ENGINE=InnoDB；//ENGINE=InnoDB指定引擎

在mysql中不会覆盖已经存在的同名表，需要先删除该表后才能建同名表。
NULL值表示没有值或缺值，在定义列的时候申明便是允许该列没有值。
在mysql中主键必须唯一，它可以使用单个列或者多个列组合。
SELECT last_insert_id()可以返回最后一个AUTO_INCREMENT值。
在mysql中多种引擎用来具体的管理和处理数据，不同的引擎的功能特性是不一样的：

InnoDB是一个可靠的事务处理引擎，但不支持全文本搜索
MEMORY功能与MYISAM一样，但在数据存储与内存中时，速度很快（适合建立临时表）
MyISAM支持全文本搜索，但是不支持事务处理

引擎类型可以混用

更新表
更新表使用ALTER TABLE语句，可以更新表定义

ALTER TABLE vendors
ADD vend_phone CHAR(20);
ALTER TABLE vendors
DROP vend_phone CHAR(20);

可以定义外键，主键

删除表

DROP TABLE customers;

重命名表

RENAME TABLE customers TO customers1;

第二十二章：使用视图

视图是虚拟的表，与包含数据的表不一样，视图只包含使用动态检索数据的查询。
视图本身不包含数据，它返回的数据是从其他表中检索出来的。

使用视图

使用CREATE VIEW语句来创建视图
使用SHOW CREATE VIEW viewname来创建视图的语句
使用DROP VIEW viewname来删除视图
更新视图时可以先删除后再创建或者使用CREATE OR REPLACE VIEW。

第二十三章：使用存储过程

第二十四章：使用触发器

第二十六章：管理事务处理

第二十七章：全球化和本地化

第二十八章：安全管理

访问控制
用户应该对他们需要的数据具有适当的访问权，即不能多也不能少。
在使用数据库中，尽量不要去使用root用户，而是创建一系列账号，进行管理使用。

管理用户

mysql用户账号信息存储在名字为mysql的库中，在mysql库中，有一个user表，里面包含了所有用户账号。

创建用户可使用：CREATE USER

CREATE USER ben IDENTIFIED BY 'password';#创建一个名为ben的用户，其密码时password

重命名用户账号：RENAME USER

RENAME USER ben TO aaa；#将ben重命名为aaa

删除账户：DROP USER
设置访问权限
查看权限:

SHOW GRANTS FOR ben

设置权限：使用GRANT语句，并给出 ‘①要授予的权限②被授予访问权限的数据库或表③用户名’ 等信息

GRANT SELECT ON crashcourse.* TO ben #允许ben用户在crashcourse.*（crashcourse中的所有表）使用select语句（只读）。

撤销权限：REVOKE，用它来撤销特定的权限。

REVOKE SELECT ON crashcourse.* FROM ben #撤销ben对crashcourse.*的select权限

可以授予和撤销的每个权限：

权限

更改密码

SET PASSWORD FOR ben=Password('newpassword')#Password可以对密码进行加密