MySql必知必会

MySql必知必会

第1章 常用命令

1.1 登录MySql

mysql -uroot -p

1.2 使用某个数据库

USE 数据库名字;

1.3 查看所有数据库

SHOW DATABASES;

1.4 查看某个数据库里面的表

SHOW TABLES

第2章 select

2.1 限制结果

返回不多于5行的记录。

select * from customers limit 5; 

select * from customers limit 4, 5;

select * from customers limit 5 offset 4; (MySql 5 以后的版本)

返回从4开始的不多于5行的数据。

limit

• 参数1，从参数1位置开始
• 参数2，检索不多于参数2的记录

2.2 把重复数据去除

使用关键字distinct，

select distinct id from customers;

2.3 按照降序查找记录

select * from customers order by age desc;

2.4 查找值为空的记录

select * from customers where email IS NULL;

2.5在指定的范围内查找

select * from customers where id in (1,2,3,4,5);

查找id分别 为1,2,3,4,5，的记录。

2.6 NOT

对not之后的东西，取反。

MySql 支持使用NOT对IN，between，exists子句取反。

2.7 通配符

1.% 匹配任意个数字符

select * from customers where name like %'王';

查找姓王的客户。

2 _ (下划线) 匹配单个字符

2.8 使用正则表达式

使用regexp

select * from products where prod_name regexp '1000|2000'

搜索prod_name 为1000或者2000的记录。

注意 MySql中的正则表达式匹配不区分大小写。为区分大小写，可使用关键字binary

正则表达式用法，参考https://www.jb51.net/tools/regexsc.htm

第3章 创建计算字段

3.1 拼接字段

3.1.1 拼接表中的2个列

在MySql的select语句中，可使用Concat()函数来拼接两个列。

select Concat(name, '(', country, ')')
from user;

ACME (USA)
Anvil (USA)
Jet (USA)

3.2 去除字符串内的空格

RTrim()函数，去除空格。

select Concat(RTrim(name), '(', country, ')')
from user;

ACME(USA)
Anvil(USA)
Jet(USA)

3.3 执行算术计算

select age n n*age as id from user

MySql，支持 + - * /

第4章 使用数据处理函数

4.1 Upper() 转成大写字母

select Upper(name) from user;

4.2 常用的文本处理函数

4.3 日期和时间处理函数

使用where子句时，日期格式必须为yyyy-mm-dd，存储的数据类型为datetime。

4.4 数值处理函数

第5章 聚集函数

第6章 组合查询

6.1 union

select v_id,p_id, price from products 
where price <= 5
union
select v_id,p_id,price from products
where v_id in(1001,1002);

union自动去重，使用union all 不会去重。

第7章 全文本搜索

为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引。在对表列进行适当设计后，MYSOL会自动进行所有的索引和重新索引。

7.1 启用全文本搜索支持

一般在创建表时启用全文本搜索。CREATE TABLE语句接受FULLTEXT子句，它给出被索引列的一个逗号分隔的列表。

例如：

CREATE TABLE productnotes
{
note_idint NOT NULL AUTO INCREMENT,
prod idchar(10)NOT NULL,
note_date   datetimeNOT NULL,
note_text textNULL,

PRIMARY KEY(note id),
FULLTEXT(note text) #开启全文本搜索

}ENGINE=MyISAM;

7.2 进行全文本搜索

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

例如：

select note_text from productnotes
where Match(note_text) Against('rabbit');

此SELECT语句检索单个列note text。由于WHERE子句，一个全文本搜索被执行。Match(note text)指示MySOL针对指定的列进行搜索，Against('rabbit')指定词rabbit作为搜索文本。由于有两行包含词rabbit，这两个行被返回。

注意

• 传递给Match()的值必须与FULLTEXT()定义中的相同。如果指定多个列，则必须列出它们(而且次序正确).
• 搜索不区分大小写 除非使用BINARY方式.

7.3 布尔文本搜索

即使没有FULLTEXT索引也可以使用 布尔方式不同于迄今为止使用的全文本搜索语法的地方在于，即使没有定义FULLTEXT索引，也可以使用它。但这是一种非常缓慢的操作其性能将随着数据量的增加而降低)。

使用关键字in boolean mode

例子：

select note_text from productnotes 
where Match(note_text) Against('heavy -rope*' in boolean mode);

仍然匹配词heavy,但-rope*明确地指示MySQL排除包含rope*的行(任何以rope开始的词，包括repos)。

7.3.2 全文本布尔操作符

例子

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而不是匹配两个词rabbit和bait.

SELECT note_text
FROM productnotes
WHERE Match(note text) Against('>rabbit <carrOt' IN BOOLEAN MODE);

匹配rabbit和carrot，增加前者的等级，降低后者的等级

SELECT note_text
FROM productnotes
WHERE Match(note_text)Against('+safe +(<combination)' IN BOOLEANMODE)

这个搜索匹配词safe和combination，降低后者的等级.

7.4 全文本搜索的使用说明

• 在索引全文本数据时，短词被忽略且从索引中排除。短词定义为那些具有3个或3个以下字符的词(如果需要,这个数目可以更改)。
• MySOL带有一个内建的非用词(stopword)列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表(请参阅MySOL文档以了解如何完成此工作)。
• 许多词出现的频率很高，搜索它们没有用处(返回太多的结果)。因此，MySQL规定了一条50%规则，如果一个词出现在50%以上的行中,则将它作为一个非用词忽略。50%规则不用于IN BOOLEANMODE.
• 如果表中的行数少于3行，则全文本搜索不返回结果(因为每个词或者不出现，或者至少出现在50%的行中)。
• 忽略词中的单引号。例如，don’t索引为dont。
• 不具有词分隔符(包括日语和汉语)的语言不能恰当地返回全文本搜索结果。
• 如前所述，仅在MyISAM数据库引擎中支持全文本搜索

第8章 使用存储过程

8.1 存储过程的概念

存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用不仅限于批处理。

8.2 存储过程的优点与缺点

优点：

1. 通过把处理封装在容易使用的单元中，简化复杂的操作(正如前面例子所述)。
2. 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一(试验和测试)存储过程，则所使用的代码都是相同的。这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。
3. 简化对变动的管理。如果表名、列名或业务逻辑(或别的内容)有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。
4. 提高性能。因为使用存储过程比使用单独的SQL语句要快。
5. 存在一些只能用在单个请求中的MySOL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。

缺点：

1. 一般来说，存储过程的编写比基本SOL语句复杂，编写存储过程需要更高的技能，更丰富的经验。
2. 你可能没有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的创建权限，允许用户使用存储过程，但不允许他们创建存储过程。

8.3 执行存储过程

MySOL称存储过程的执行为调用，因此MySOL执行存储过程的语为CALL。CALL接受存储过程的名字以及需要传递给它的任意参数。

例如：

CALL productpricing(@pricelow, @pricehigh,@priceaverage);

其中，执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格。

8.4 创建存储过程

CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod price)AS priceaverage FROM products;
END;

productpricing，用CREATE PROCEDURE productpricing()语句定义。如果存储过程接受参数，它们将在()中列举出来。此存储过程没有参数，但后跟的()仍然需要。BEGIN和END语句用来限定存储过程体，过程体本身仅是一个简单的SELECT语句.

8.4.2 使用命令行出现的错误

8.5 删除存储过程

DROP PROCEDURE productpricing;

这条语句删除刚创建的存储过程。请注意没有使用后面的()只给出存储过程名。

仅当存在时删除,如果指定的过程不存在，则DROP PROCEDURE将产生一个错误。当过程存在想删除它时(如果过程不存在也不产生错误)可使用DROP PROCEDURE IFEXISTS

8.6 使用参数的存储过程创建

8.6.1 创建

一般，存储过程并不显示结果，而是把结果返回给你指定的变量。

变量(variable)内存中一个特定的位置，用来临时存储数据。

CREATE PROCEDURE productpricing(
OUT PT DECIMAL(8,2),
OUT ph DECIMAL(8,2),
OUT pa DECIMAL(8,2)
)
BEGIN
SELECT Min(prod_price) INTO p1 FROM products;
SELECT Max(prod _price)INTO ph FROM products;
SELECT Avg(prod price) INTO pa FROM products;
END;

此存储过程接受3个参数:p1存储产品最低价格，ph存储产品最高价格，pa存储产品平均价格。每个参数必须具有指定的类型，这里使用十进制值。关键字OUT指出相应的参数用来从存储过程传出一个值(返回给调用者)。MySOL支持IN(传递给存储过程)、OUT(从存储过程传出，如这里所用)和INOUT(对存储过程传入和传出)类型的参数。存储过程的代码位于BEGIN和END语句内，如前所见，它们是一系列SELECT语句，用来检索值，然后保存到相应的变量(通过指定INTO关键字)。

参数的数据类型存储过程的参数允许的数据类型与表中使用的数据类型相同。

8.6.2 调用

为调用此修改过的存储过程，必须指定3个变量名，如下所示，

CALL productpricing(@pricelow, @pricehigh,@priceaverage);

所有MySOL变量都必须以@开始变量名

8.6.3 显示

为了获取3个值，可以使用以下语句

SELECT @pricehigh, @pricelow, @priceaverage;

8.7 向存储过程传入参数的创建

8.7.1 创建

CREATE PROCEDURE ordertotal(
IN onumber INT,
    OUT ototal DECIMAL(8,2)
)
BEGIN
SELECT Sum(item price*quantity)
FROM orderitems 
WHERE order_num = onumber 
INTO ototal;
END;

8.7.2 调用

CALL ordertota(20005，@total);

8.7.3 显示

SELECT @total;

8.8 建立智能存储过程

例如：

CREATE PROCEDURE ordertotal(
IN onumber INT,
IN taxable BOOLEAN,
OUT otota7 DECIMAL(8,2)
)COMMENT'Obtain order total, optionally adding tax'
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;

此存储过程有很大的变动。首先，增加了注释(前面放置–)。在存储过程复杂性增加时，这样做特别重要。添加了另外一个参数taxable，它是一个布尔值(如果要增加税则为真，否则为假)。在存储过程体中，用DECLARE语句定义了两个局部变量。DECLARE要求指定变量名和数据类型，它也支持可选的默认值(这个例子中的taxrate的默认被设置为6%)。SELECT语句已经改变，因此其结果存储到total(局部变量)而不是ototal。IF语句检查taxable是否为真，如果为真，则用另-SELECT语句增加营业税到局部变量tota1。最后，用另一SELECT语句将total(它增加或许不增加营业税)保存到ototal。

COMMENT关键字 本例子中的存储过程在CREATE PROCEDURE语句中包含了一个COMMENT值。它不是必需的，但如果给出，将在SHOW PROCEDURE STATUS的结果中显示。

第9章 游标

9.1 游标的用途

由前几章可知，MySOL检索操作返回一组称为结果集的行。这组返回的行都是与SOL语句相匹配的行(零行或多行)。使用单的SELECT语句，例如，没有办法得到第一行、下一行或前10行，也不存在每次一行地处理所有行的简单方法(相对于成批地处理它们)。
有时，需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。游标(cursor)是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

只能用于存储过程，不像多数DBMS，MySOL游标只能用于存储过程(和函数)。

9.2 使用游标的前提条件

1. 在能够使用游标前，必须声明(定义)它。这个过程实际上没有检索数据，它只是定义要使用的SELECT语句。
2. 一旦声明后，必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。
3. 对于填有数据的游标，根据需要取出(检索)各行。
4. 在结束游标使用时，必须关闭游标。

9.3 创建游标

游标用DECLARE语句创建。DECLARE命名游标，并定义相应的SELECT语句，根据需要带WHERE和其他子句。

CREATE PROCEDURE processorders()
BEGIN
DECLARE ordernumbers CURSOR
FOR
SELECT ordernum FROM orders,
END;

这个存储过程并没有做很多事情，DECLARE语句用来定义和命名游标，这里为ordernumbers。存储过程处理完成后，游标就消失(因为它局限于存储过程).

9.4 打开和关闭游标

9.4.1 打开

OPEN ordernumbers;

9.4.2 关闭

CLOSE ordernumbers;

9.5 使用游标数据

在一个游标被打开后，可以使用FETCH语句分别访问它的每一行。FETCH指定检索什么数据(所需的列)，检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行(不重复读取同一行)。

例如：循环检索数据，从第一行到最后一行:

CREATE PROCEDURE processorders()
BEGIN
-- Declare local variables
DECLARE done BOOLEAN DEFAULT 0;
DECLARE O INT;
-- Declare the cursor
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
-- Declare continue handler
DECLARE CONTINUE HANDLER FOR SOLSTATE '02000' 
SET done=1;
-- Open the cursor
OPEN ordernumbers;
-- LOOp through all rows
REPEAT
-- Get order number
FETCH ordernumbers INT o;
-- End of loop 
UNTIL done END REPEAT;
-- Close the cursor
CLOSE ordernumbers;
END ;

这个例子使用FETCH检索当前order_num到声明的名为o的变量中。这个例子中的FETCH是在REPEAT内，因此它反复执行直到done为真(由UNTIL done END REPEAT;规定)。为使它起作用，用一个DEFAULT 0(假，不结束)定义变量done。那么，done怎样才能在结束时被设置为真呢?答案是用以下语句:
DECLARE CONTINUE HANDLER FOR SOLSTATE '02000' SET done=1;
这条语句定义了一个CONTINUE HANDLER，它是在条件出现时被执行的代码。这里，它指出当SOLSTATE'02000'出现时,SET done=1.SOLSTATE'02000'是一个未找到条件，当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件。

第10章 使用触发器

10.1 触发器的概念

它们都需要在某个表发生更改时自动处理。这确切地说就是触发器。触发器是MySQL响应以下任意语句而自动执行的一条MySOL语句(或位于BEGIN和END语句之间的一组语句)。

1. DELETE
2. INSERT
3. UPDATE

10.2 创建触发器

在创建触发器时，需要给出4条信息:

1. 唯一的触发器名;
2. 触发器关联的表:
3. 触发器应该响应的活动(DELETE、INSERT或UPDATE);
4. 触发器何时执行(处理之前或之后)。

CREATE TRIGGER newproduct AFTER INSERT ON products 
FOR EACH ROW SELECT 'Product added';

CREATE TRIGGER用来创建名为newproduct的新触发器。触发器可在一个操作发生之前或之后执行,这里给出了AFTER INSERT,所以此触发器将在INSERT语句成功执行后执行。这个触发器还指定FOR EACH ROW，因此代码对每个插入行执行。在这个例子中，文本Product added将对每个插入的行显示一次。
为了测试这个触发器，使用INSERT语句添加一行或多行到products中，你将看到对每个成功的插入，显示Product added消息。

注意

**仅支持表，**只有表才支持触发器，视图不支持(临时表也不支持)。

触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器(每条INSERT、UPDATE和DELETE的之前和之后)。单一触发器不能与多个事件或多个表关联，所以，如果你需要一个对INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。

10.3 删除触发器

DROP TRIGGER newproduct;

第11章 事务管理

11.1 事务处理的概念

事务处理是一种机制，用来管理必须成批执行的MySQL操作，以保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们或者作为整体执行，或者完全不执行(除非明确指示)。如果没有错误发生，整组语句提交给(写到)数据库表。如果发生错误，则进行回退(撤销)以恢复数据库到某个已知且安全的状态。

11.2 开启事务

START TRANSACTION

11.3 回退事务

ROLLBACK;

11.4 提交事务

COMMIT;

11.5 创建占位符(保留点)

为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符。这样，如果需要回退，可以回退到某个占位符.

SAVEPOINT delete1; #delete1 是占位点的独一无二的名字

ROLLBACK To delete1;

11.6 更改默认的提交行为

SET autocommit=0;

标志为连接专用，autocommit标志是针对每个连接而不是服务器的。

第 12章 安全管理

12.1 管理用户

MySQL用户账号和信息存储在名为mysq1的MySQL数据库中。一般不需要直接访问mysq1数据库和表(你稍后会明白这一点)，但有时需要直接访问。需要直接访问它的时机之一是在需要获得所有用户账号列表时。为此，可使用以下代码:

USE mysql;
SELECT user FROM user;

12.2 创建用户账号

CREATE USER xxx IDENTIFIED BY 'password';
#identified

创建用户账号方式：

1. 指定散列口令

IDENTIFIED BY指定的口令为纯文本，MySQL指定散列口令将在保存到user表之前对其进行加密。为了作为散列值指定口令，使用IDENTIFIED BY PASSWORD.

2. 使用GRANT或INSERT

GRANT语句(稍后介绍)也可以创建用户账号，但一般来说CREATE USER是最清楚和最简单的句子。

3. 此外，也可以通过直接插入行到user表来增加用户，

不过为安全起见，一般不建议这样做。MySOL用来存储用户账号信息的表(以及表模式等)极为重要，对它们的任何毁坏都可能严重地伤害到MySOL服务器。因此，相对于直接处理来说，最好是用标记和函数来处理这些表.

12.3 重命名一个用户

RENAME USER old_name To new_name;

12.4 删除一个用户账号

DROP USER user_name;

12.5 设置访问权限

12.5.1 查看用户权限

SHOW GRANTS FOR user_name;

12.5.2 设置权限

用户定义为user@host MySOL的权限用用户名和主机名结合定义。如果不指定主机名，则使用默认的主机名%(授予用户访问权限而不管主机名)。

GRANT要求至少以下信息：

1. 要授予的权限
2. 被授予访问权限的数据库或表
3. 用户名

GRANT SELECT ON crashcourse.* To bforta;

此GRANT允许用户在crashcourse.*(crashcourse数据库的所有表)上使用SELECT。通过只授予SELECT访问权限,用户bforta对crashcourse数据库中的所有数据具有只读访问权限。

12.6 收回权限

REVOKE SELECT ON crashcourse.* FROM bforta;

这条REVOKE语句取消刚赋予用户bforta的SELECT访问权限。被撤销的访问权限必须存在，否则会出错.

12.7 权限表

12.8 修改口令(密码)

SET PASSWORD FOR bforta = Password('新密码');

设置自己的口令(密码)

SET PASSWORD = Password('新密码');

原文地址：https://blog.csdn.net/ccb1372098/article/details/142872802

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！