mysql详解

以你之姓@ 2023-07-10 05:21 3阅读 0赞

### mysql命令 ###

*  mysql登录参数：

-D, --database          指定数据库
    
    --delimiter             指定分隔符
    
    -h, --host              指定服务器地址
    
    -p, --password          指定密码
    
    -P, --port              指定端口
    
    --prompt                设置提示符
    
    -u, --user              指定用户名
    
    -V， --version          输出版本信息并退出

*  修改命令提示符：

通过参数指定提示符，

mysql -uroot -p123456 --prompt 提示符

通过命令指定提示符，

mysql> prompt 提示符;

提示符：

\D      完整的日期
    
    \d      当前数据库
    
    \h      服务器名称
    
    \u      当前数据库

*  常用命令：

SELECT VERSION();       显示当前服务器版本
    
    SELECT NOW();           显示当前日期时间
    
    SELECT USER();          显示当前用户

*  mysql语句规范：

1. 关键字与函数名称全部大写
    
    2. 数据库名称、表名称、字段名称全部小写
    
    3. SQL语句必须以分号结尾

--------------------

### 操作数据库 ###

CREATE DATABASE         创建数据库
    
    ALTER DATABASE          修改数据库
    
    DROP DATABASE           删除数据库

*  创建数据库：

`{}`为必选项；`|`表示或；`[]`为可选项。下同

CREATE {
       DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name;

*  查看数据库：

SHOW {
       DATABASES | SCHEMAS} [LIKE 'pattern' | WHERE expr];

*  修改数据库：

ALTER {
       DATABASE | SCHEMA} db_name [DEFAULT] CHARACTER SET [=] charset_name;

*  删除数据库：

DROP {
       DATABASE | SCHEMA} [IF EXISTS] db_name;

--------------------

### 操作数据表 ###

数据表是组成数据库最重要的部分之一，是其它对象的基础。

*  数据类型：

数据类型是指列、存储过程参数、表达式和局部变量的数据特征，它决定了数据的存储格式，代表了不同的信息类型。

整型：

<table> 
 <thead> 
  <tr> 
   <th>数据类型</th> 
   <th>存储范围</th> 
   <th>字节</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>TINYINT</td> 
   <td>有符号值：-2^7 ~ 2^7-1；无符号值：0 ~ 2^8-1</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>SMALLINT</td> 
   <td>有符号值：-2^15 ~ 2^15-1；无符号值：0 ~ 2^16-1</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>MEDIUMINT</td> 
   <td>有符号值：-2^23 ~ 2^23-1；无符号值：0 ~ 2^24-1</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>INT</td> 
   <td>有符号值：-2^31 ~ 2^31-1；无符号值：0 ~ 2^32-1</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>BIGINT</td> 
   <td>有符号值：-2^63 ~ 2^63-1；无符号值：0 ~ 2^64-1</td> 
   <td>8</td> 
  </tr> 
 </tbody> 
</table>

浮点型：

<table> 
 <thead> 
  <tr> 
   <th>数据类型</th> 
   <th>存储范围</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>FLOAT[(M,D)]</td> 
   <td>-3.402823466E+38 ~ -1.175494351E-38、0、1.175494351E-38 ~ 3.402823466E+38</td> 
   <td>M是数字总位数，D是小数点后面的位数</td> 
  </tr> 
  <tr> 
   <td>DOUBLE[(M,D]]</td> 
   <td>-1.7976931348623157E+308 ~ -2.2250738585072014E-308、0、2.2250738585072014E-308 ~ 1.7976931348623157E+308</td> 
   <td>M是数字总位数，D是小数点后面的位数</td> 
  </tr> 
 </tbody> 
</table>

日期时间型：

<table> 
 <thead> 
  <tr> 
   <th>列类型</th> 
   <th>存储需求</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>YEAR</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>TIME</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>DATE</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>DATETIME</td> 
   <td>8</td> 
  </tr> 
  <tr> 
   <td>TIMESTAMP</td> 
   <td>4</td> 
  </tr> 
 </tbody> 
</table>

字符型：

<table> 
 <thead> 
  <tr> 
   <th>列类型</th> 
   <th>存储需求</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>CHAR(M)</td> 
   <td>M个字节，0 &lt;= M &lt;= 255</td> 
  </tr> 
  <tr> 
   <td>VARCHAR(M)</td> 
   <td>L+1个字节，其中L &lt;= M且0 &lt;= M &lt;= 65535</td> 
  </tr> 
  <tr> 
   <td>TINYTEXT</td> 
   <td>L+1个字节，其中L &lt; 2^8</td> 
  </tr> 
  <tr> 
   <td>TEXT</td> 
   <td>L+2个字节，其中L &lt; 2^16</td> 
  </tr> 
  <tr> 
   <td>MEDIUMTEXT</td> 
   <td>L+3个字节，其中L &lt; 2^24</td> 
  </tr> 
  <tr> 
   <td>LONGTEXT</td> 
   <td>L+4个字节，其中L &lt; 2^32</td> 
  </tr> 
  <tr> 
   <td>ENUM(‘value1’,‘value2’,…)</td> 
   <td>1或2个字节，取决于枚举值的个数（最多65535个）</td> 
  </tr> 
  <tr> 
   <td>SET(‘value1’,‘value2’,…)</td> 
   <td>1、2、3、4或8个字节，取决于set成员的个数（最多64个）</td> 
  </tr> 
 </tbody> 
</table>

*  创建数据表：

CREATE TABLE [IF NOT EXISTS] table_name(column_name data_type,...);

示例：

CREATE TABLE tb1(username VARCHAR(20),age TINYINT UNSIGNED,salary FLOAT(8,2) UNSIGNED);

*  查看数据表：

SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr];

*  查看数据表结构：

SHOW COLUMNS FROM table_name;

*  插入记录：

记录（行）与列组成数据表。

INSERT [INTO] table_name[(column_name,...)] VALUES(value,...);

示例：

INSERT tb1 VALUES('Tom',25,8000.00);

*  查找记录：

SELECT expr,... FROM table_name;

*  空值与非空：

NULL        字段值可以为空
    
    NOT NULL    字段值禁止为空

*  约束：

1. 约束保证数据的完整性与一致性
    
    2. 约束分为表级约束和列级约束
    
    3. 约束类型包括：
        NOT NULL（非空约束）
        PRIMARY KEY（主键约束）
        UNIQUE KEY（唯一约束）
        DEFAULT（默认约束）
        FOREIGN KEY（外键约束）

对一个数据列建立的约束称为列级约束；对多个数据列建立的约束称为表级约束。列级约束既可以在列定义时声明，也可以在列定义后声明，表级约束只能在列定义后声明。

*  主键约束（PRIMARY KEY）：

自动编号（AUTO\_INCREMENT）必须与主键组合使用，但主键可以不和自动编号一起使用。默认情况下，起始值为1，每次的增量为1。

1. 每张数据表只能存在一个主键
    
    2. 主键保证记录的唯一性
    
    3. 主键自动为NOT NULL

*  唯一约束（UNIQUE KEY）：

1. 每张数据表可以存在多个唯一约束
    
    2. 唯一约束可以保证记录的唯一性
    
    3. 唯一约束的字段可以为NULL

示例：

CREATE TABLE tb2(id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,username VARCHAR(20) NOT NULL UNIQUE KEY,age TINYINT UNSIGNED);

*  默认约束（DEFAULT）：

当插入记录时，如果没有明确为字段赋值，则自动赋予默认值。

示例：

CREATE TABLE tb3(id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,username VARCHAR(20) NOT NULL UNIQUE KEY,sex ENUM('1','2','3') DEFAULT '3');

*  外键约束（FOREIGN KEY）：

外键约束保证数据的一致性和完整性，实现数据表的一对一、一对多关系。

有外键的表称为子表，子表所参照的表称为父表。

外键约束的要求：

1. 父表和子表必须使用相同的存储引擎，而且禁止使用临时表。
    
    2. 数据表的存储引擎只能为InnoDB。
    
    3. 外键列和参照列必须具有相似的数据类型。其中数字的长度或是否具有符号位必须相同；而字符的长度则可以不同。
    
    4. 外键列和参照列必须创建索引。如果外键列不存在索引，mysql将自动创建索引。

外键约束的参照操作：

CASCADE：从父表删除或更新且自动删除或更新子表中匹配的行
    
    SET NULL：从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该项，必须保证子表列没有设置NOT NULL
    
    RESTRICT：拒绝对父表的删除或更新操作
    
    NO ACTION：标准sql的关键字，在mysql中与RESTRICT相同

*  修改数据表：

添加单列：

ALTER TABLE table_name ADD [COLUMN] column_name column_definition [FIRST | AFTER column_name];

添加多列：

ALTER TABLE table_name ADD [COLUMN] (column_name column_definition,...);

删除列：

ALTER TABLE table_name DROP [COLUMN] column_name;

添加主键约束：

ALTER TABLE table_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_column_name,...);

删除主键约束：

ALTER TABLE table_name DROP PRIMARY KEY;

添加唯一约束：

ALTER TABLE table_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [index_name] [index_type] (index_column_name,...);

删除唯一约束：

ALTER TABLE table_name DROP {
       INDEX | KEY} index_name;

添加外键约束：

ALTER TABLE table_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_column_name,...) reference_definition;

删除外键约束：

ALTER TABLE table_name DROP FOREIGN KEY fk_symbol;

添加、删除默认约束：

ALTER TABLE table_name ALTER [COLUMN] column_name {
       SET DEFAULT literal | DROP DEFAULT};

修改列定义：

ALTER TABLE table_name MODIFY [COLUMN] column_name column_definition [FIRST | AFTER column_name];

修改列名称：

ALTER TABLE table_name CHANGE [COLUMN] old_column_name new_column_name column_definition [FIRST | AFTER column_name];

数据表更名：

ALTER TABLE table_name RENAME [TO | AS] new_table_name;
    
    或
    
    RENAME TABLE table_name TO new_table_name [,table_name2 TO new_table_name2];

--------------------

### 记录操作 ###

*  插入记录：

方式一：

INSERT [INTO] table_name [(column_name,...)] {
       VALUES | VALUE}({expr | DEFAULT},...),(...),...;

示例：

CREATE TABLE users(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,username VARCHAR(20) NOT NULL, password VARCHAR(32) NOT NULL,age TINYINT UNSIGNED NOT NULL DEFAULT 10,sex BOOLEAN);
    
    INSERT users VALUES(DEFAULT,'Tom','123',25,1),(NULL,'Rose',md5('123'),DEFAULT,0);

方式二：

INSERT [INTO] table_name SET column_name={expr | DEFAULT},...;

第二种方式可以使用子查询。

示例：

INSERT users SET username='Ben',password='456',sex=0;

方式三：

INSERT [INTO] table_name [(column_name,...)] SELECT ...;

第三种方式可以将查询结果插入到指定数据表。

*  单表更新记录：

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET column_name1={expr1 | DEFAULT} [,column_name2={expr2 | DEFAULT}] ... [WHERE where_condition];

示例：

UPDTAE users SET age = age + 5;             #更新所有列
    
    UPDATE users SET age = age - id,sex = 0;
    
    UPDATE users SET age = age + 10 WHERE id % 2 = 0;               #当id为偶数时age列的记录加10

*  单表删除记录：

DELETE FROM table_name [WHERE where_condition];

示例：

DELETE FROM users WHERE id = 6;             #删除id为6的记录

*  查找记录：

SELECT select_expr [,select_expr ...] 
    [
        FROM table_references
        [WHERE where_condition]
        [GROUP BY {column_name | position} [ASC | DESC],...]
        [HAVING where_condition]
        [ORDER BY {column_name | expr | position} [ASC | DESC],...]
        [LIMIT {
       [offset,] row_count | row_count OFFSET offset}]
    ];

查询表达式：

每一个表达式表示想要的一列，至少要有一个。
    
    多个列直接以 , 分隔。
    
    * 表示所有列。table_name.* 表示命名表的所有列。
    
    查询表达式可以使用 [AS] alias_name 为其赋予别名。
    
    别名可用于GROUP BY，ORDER BY或HAVING子句。

条件表达式：

对记录进行过滤，如果没有指定的WHERE子句，则显示所有记录。
    
    在WHERE表达式中，可以使用mysql支持的函数或运算符。

查询结果分组：

[GROUP BY {column_name | position} [ASC | DESC],...]

ASC表示升序，DESC表示降序。

示例：

SELECT sex FROM users GROUP BY sex;

分组条件：

[HAVING where_condition]

示例：

SELECT sex FROM users GROUP BY sex HAVING count(id) >= 2;

查询结果排序：

[ORDER BY {column_name | expr | position} [ASC | DESC],...]

示例：

SELECT * FROM users ORDER BY id DESC;               #按id降序排列
    
    SELECT * FROM users ORDER BY age,id DESC;               #同时按age和id降序排列，age相同时比较id

限制查询数量：

[LIMIT {
       [offset,] row_count | row_count OFFSET offset}]

示例：

SELECT * FROM users LIMIT 2;                #返回两条记录
    
    SELECT * FROM users LIMIT 3,2;              #从第4条记录开始，返回两条记录

--------------------

### 子查询与连接 ###

记录的INSERT、UPDATE、DELETE是写操作，而SELECT是读操作。

*  子查询：

子查询是指嵌套在其他sql语句内的SELECT子句。子查询可以返回标量、一行、一列或子查询。

示例：

SELECT * FROM t1 WHERE column1 = (SELECT column2 FROM t2);

其中 `SELECT * FROM t1` 称为Outer Query/Outer Statement（外层查询/外层声明）；`SELECT column2 FROM t2` 称为SubQuery（子查询）。

子查询嵌套在查询内部，且必须出现在圆括号内。子查询可以包含多个关键字或条件，如DISTINCT、GROUP BY、ORDER BY、LIMIT、函数等。子查询的外层查询可以是SELECT、INSERT、UPDATE、SET或DO。

*  使用比较运算符的子查询：

=、>、<、>=、<=、<>、!=

示例：

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >= (SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);

从`tdb_goods`表中筛选出`goods_price`大于等于平均`goods_price`的商品`goods_id`、`goods_name`、`goods_price`。

使用ANY、SOME、ALL关键字修饰比较运算符：

<table> 
 <thead> 
  <tr> 
   <th>比较运算符</th> 
   <th>ANY</th> 
   <th>SOME</th> 
   <th>ALL</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>&gt;、&gt;=</td> 
   <td>最小值</td> 
   <td>最小值</td> 
   <td>最大值</td> 
  </tr> 
  <tr> 
   <td>&lt;、&lt;=</td> 
   <td>最大值</td> 
   <td>最大值</td> 
   <td>最小值</td> 
  </tr> 
  <tr> 
   <td>=</td> 
   <td>任意值</td> 
   <td>任意值</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>&lt;&gt;、!=</td> 
   <td></td> 
   <td></td> 
   <td>任意值</td> 
  </tr> 
 </tbody> 
</table>

示例：

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >= ALL (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超极本');

从`tdb_goods`表中筛选出`goods_price`大于等于所有的超极本价格的商品`goods_id`、`goods_name`、`goods_price`。

*  使用 \[NOT\] IN 的子查询：

= ANY 与 IN 等效；!= ALL 或 <> ALL 与 NOT IN 等效。

示例：

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price NOT IN (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超极本');

从`tdb_goods`表中筛选出`goods_price`不等于所有的超极本价格的商品`goods_id`、`goods_name`、`goods_price`。

*  使用 \[NOT\] EXISTS 的子查询：

如果子查询返回任何行，EXISTS将返回TRUE；否则返回FALSE。NOT EXISTS 反之。

*  使用 INSERT … SELECT 插入记录：

将查询结果写入数据表。

INSERT [INTO] table_name[(column_name,...)] SELECT ...

示例：

CREATE TABLE IF NOT EXISTS tdb_goods_cates(cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,cate_name VARCHAR(40) NOT NULL);
    
    INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;

*  多表更新：

UPDATE table_references SET column_name1={expr1 | DEFAULT} [,column_name2={expr2 | DEFAULT}] ... [WHERE where_condition]

数据表连接类型：

INNER JOIN 内连接
    
        在mysql中，JOIN、CROSS JOIN和INNER JOIN是等价的
        
    LEFT [OUTER] JOIN 左外连接
    
    RIGHT [OUTER] JOIN 右外连接

示例：

UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name SET good_cate = cate_id;

*  CREATE … SELECT 多表一步更新：

创建数据表同时将查询结果写入数据表。

CREATE TABLE [IF NOT EXISTS] table_name [(create_definition,...)] select_statement;

示例：

CREATE TABLE tdb_goods_brands(brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,brand_name VARCHAR(40) NOT NULL) SELECT brand_name FROM tdb_goods GROUP BY brand_name;
    
    UPDATE tdb_goods AS g INNER JOIN tdb_goods_brands AS b ON g.brand_name = b.brand_name SET g.brand_name = b.brand_id;

*  连接：

mysql在SELECT语句、多表更新、多表删除语句中支持JOIN操作。

连接条件：

使用 ON 关键字来设定连接条件，也可以使用 WHERE 来代替。
    
    通常使用 ON 关键字来设定连接条件，使用 WHERE 关键字进行结果集记录的过滤。

内连接（INNER JOIN）：仅显示左表及右表符合连接条件的记录。

左连接（LEFT \[OUTER\] JOIN）：显示左表的全部记录及右表符合条件的记录。

右连接（RIGHT \[OUTER\] JOIN）：显示右表的全部记录及左表符合条件的记录。

示例：

SELECT goods_id,goods_name,cate_name FROM tdb_goods INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;
    
    SELECT goods_id,goods_name,cate_name FROM tdb_goods LEFT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;
    
    SELECT goods_id,goods_name,cate_name FROM tdb_goods RIGHT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;

*  多表连接：

多张表进行连接。

示例：

SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g INNER JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id INNER JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id;

*  自身连接：

一个数据表对其自身进行连接。自身连接时必须给数据表起别名。

*  多表删除：

DELETE table_name[.*] [,table_name[.*]] ... FROM table_references [WHERE where_condition]

示例：

DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (SELECT goods_id,goods_name, FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2) AS t2 ON t1.goods_name = t2.goods_name WHERE t1.goods_id > t2.goods_id;

去除`tdb_goods`中的重复记录，保留`goods_id`较小的记录。

--------------------

### 运算符与函数 ###

mysql中的运算符与函数有：

字符函数
    
    数值运算符与函数
    
    比较运算符与函数
    
    日期时间函数
    
    信息函数
    
    聚合函数
    
    加密函数

*  字符函数：

<table> 
 <thead> 
  <tr> 
   <th>函数名称</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>CONCAT()</td> 
   <td>字符连接</td> 
  </tr> 
  <tr> 
   <td>CONCAT_WS()</td> 
   <td>使用指定的分隔符进行字符连接</td> 
  </tr> 
  <tr> 
   <td>FORMAT()</td> 
   <td>数字格式化</td> 
  </tr> 
  <tr> 
   <td>LOWER()</td> 
   <td>转换成小写字母</td> 
  </tr> 
  <tr> 
   <td>UPPER()</td> 
   <td>转换成大写字母</td> 
  </tr> 
  <tr> 
   <td>LEFT()</td> 
   <td>获取左侧字符</td> 
  </tr> 
  <tr> 
   <td>RIGHT()</td> 
   <td>获取右侧字符</td> 
  </tr> 
  <tr> 
   <td>LENGTH()</td> 
   <td>获取字符串长度</td> 
  </tr> 
  <tr> 
   <td>LTRIM()</td> 
   <td>删除前导空格</td> 
  </tr> 
  <tr> 
   <td>RTRIM()</td> 
   <td>删除后续空格</td> 
  </tr> 
  <tr> 
   <td>TRIM()</td> 
   <td>删除前导和后续空格</td> 
  </tr> 
  <tr> 
   <td>SUBSTRING()</td> 
   <td>字符串截取</td> 
  </tr> 
  <tr> 
   <td>[NOT] LIKE</td> 
   <td>模式匹配</td> 
  </tr> 
  <tr> 
   <td>REPLACE()</td> 
   <td>字符串替换</td> 
  </tr> 
 </tbody> 
</table>

示例：

SELECT CONCAT('My','SQL');
    
    SELECT CONCAT_WS('|','123','456');              #连接符为 |
    
    SELECT FORMAT(12345.6789,2);                #保留两位小数，结果为字符型
    
    SELECT LOWER('MySQL');
    
    SELECT UPPER('mysql');
    
    SELECT LEFT('MySQL',2);             #取左边2个字符，结果是My
    
    SELECT RIGHT('MySQL',3);                #取右边3个字符，结果是SQL
    
    SELECT LENGTH('MySQL');
    
    SELECT LTRIM(' MySQL');                #删除字符串前面的空格
    
    SELECT RTRIM('MySQL ');              #删除字符串后面的空格
    
    SELECT TRIM(' MySQL ');             #删除字符串前后的空格
    
    SELECT TRIM(LEADING '?' FROM '??MySQL???');             #删除字符串前面的指定字符 ?
    
    SELECT TRIM(TRAILING '?' FROM '??MySQL???');                #删除字符串后面的指定字符 ?
    
    SELECT TRIM(BOTH '?' FROM '??MySQL???');                #删除字符串前后的指定字符 ?
    
    SELECT SUBSTRING('MySQL',1,2);              #从第1位开始截取，截取2位。结果是My
    
    SELECT SUBSTRING('MySQL',3);                #结果是SQL
    
    SELECT 'MySQL' LIKE 'M%';               #返回布尔值 1
    
    SELECT * FROM test WHERE first_name LIKE '%My%';                #此处 % 表示任意个任意字符，还有 _ 表示1个任意字符
    
    SELECT * FROM test WHERE first_name LIKE '%1%%' ESCAPE '1';               #此时1后面的 % 不是通配符，而是真正的 %
    
    SELECT REPLACE('??MySQL???','?','');                #删除字符串中所有的 ?

*  数值运算符与函数：

<table> 
 <thead> 
  <tr> 
   <th>函数或运算符名称</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>CEIL()</td> 
   <td>进一取整</td> 
  </tr> 
  <tr> 
   <td>DIV</td> 
   <td>整数除法</td> 
  </tr> 
  <tr> 
   <td>FLOOR()</td> 
   <td>舍一取整</td> 
  </tr> 
  <tr> 
   <td>MOD</td> 
   <td>取余数（取模）</td> 
  </tr> 
  <tr> 
   <td>POWER()</td> 
   <td>幂运算</td> 
  </tr> 
  <tr> 
   <td>ROUND()</td> 
   <td>四舍五入</td> 
  </tr> 
  <tr> 
   <td>TRUNCATE()</td> 
   <td>数字截取</td> 
  </tr> 
 </tbody> 
</table>

示例：

SELECT CEIL(3.01);              #进一取整，结果是4
    
    SELECT 3 DIV 4;             #整数除法，结果是0
    
    SELECT FLOOR(3.99);             #舍一取整，结果是3
    
    SELECT 5.3 MOD 3;             #取余数，与 % 相同，结果是2.3
    
    SELECT POWER(3,3);              #幂运算，结果是27
    
    SELECT ROUND(3.652, 2);             #四舍五入，保留2位小数，结果是3.65
    
    SELECT ROUND(3.652, 1);             #四舍五入，保留1位小数，结果是3.7
    
    SELECT TRUNCATE(234.89,2);              #数字截取，保留2位小数，结果是234.89
    
    SELECT TRUNCATE(234.89,0);              #数字截取，保留整数部分，结果是234

*  数值运算符与函数：

<table> 
 <thead> 
  <tr> 
   <th>函数或运算符名称</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>[NOT] BETWEEN … AND …</td> 
   <td>[不]在范围之内</td> 
  </tr> 
  <tr> 
   <td>[NOT] IN()</td> 
   <td>[不]在列出值范围内</td> 
  </tr> 
  <tr> 
   <td>IS [NOT] NULL</td> 
   <td>[不]为空</td> 
  </tr> 
 </tbody> 
</table>

示例：

SELECT 15 BETWEEN 1 AND 20;             #结果是布尔值 1
    
    SELECT 35 BETWEEN 1 AND 20;             #结果是布尔值 0
    
    SELECT 10 IN(5,10,15,20);               #结果是布尔值 1
    
    SELECT 13 IN(5,10,15,20);               #结果是布尔值 0
    
    SELECT '' IS NULL;              #结果是布尔值 0
    
    SELECT 0 IS NULL;              #结果是布尔值 0

*  日期时间函数：

<table> 
 <thead> 
  <tr> 
   <th>函数名称</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>NOW()</td> 
   <td>当前日期和时间</td> 
  </tr> 
  <tr> 
   <td>CURDATE()</td> 
   <td>当前日期</td> 
  </tr> 
  <tr> 
   <td>CURTIME()</td> 
   <td>当前时间</td> 
  </tr> 
  <tr> 
   <td>DATE_ADD()</td> 
   <td>日期变化</td> 
  </tr> 
  <tr> 
   <td>DATEDIFF()</td> 
   <td>日期差值</td> 
  </tr> 
  <tr> 
   <td>DATE_FORMAT()</td> 
   <td>日期格式化</td> 
  </tr> 
 </tbody> 
</table>

示例：

SELECT NOW();               #返回当前日期和时间
    
    SELECT CURDATE();               #返回当前日期
    
    SELECT CURTIME();               #返回当前时间
    
    SELECT DATE_ADD('2014-3-12',INTERVAL 365 DAY);              #在2014-3-12基础上增加365天
    
    SELECT DATE_ADD('2014-3-12',INTERVAL -365 DAY);              #在2014-3-12基础上减少365天
    
    SELECT DATE_ADD('2014-3-12',INTERVAL 1 YEAR);              #在2014-3-12基础上增加1年
    
    SELECT DATE_ADD('2014-3-12',INTERVAL 3 WEEK);              #在2014-3-12基础上增加3星期
    
    SELECT DATEDIFF('2013-3-12','2014-3-12');               #比较两个日期差值，结果是 -365
    
    SELECT DATE_FORMAT('2014-3-12','%m/%d/%Y');             #日期格式化，结果是 03/12/2014

*  信息函数：

<table> 
 <thead> 
  <tr> 
   <th>函数名称</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>CONNECTION_ID()</td> 
   <td>连接ID</td> 
  </tr> 
  <tr> 
   <td>DATABASE()</td> 
   <td>当前数据库</td> 
  </tr> 
  <tr> 
   <td>LAST_INSERT_ID()</td> 
   <td>最后插入记录的ID</td> 
  </tr> 
  <tr> 
   <td>USER()</td> 
   <td>当前用户</td> 
  </tr> 
  <tr> 
   <td>VERSION()</td> 
   <td>版本信息</td> 
  </tr> 
 </tbody> 
</table>

示例：

SELECT CONNECTION_ID();             #连接ID
    
    SELECT DATABASE();              #当前数据库
    
    SELECT LAST_INSERT_ID();                #如果同时插入多条记录时，只返回插入的第一个记录的ID
    
    SELECT USER();              #当前用户
    
    SELECT VERSION();               #数据库版本信息

*  聚合函数：

<table> 
 <thead> 
  <tr> 
   <th>函数名称</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>AVG()</td> 
   <td>平均值</td> 
  </tr> 
  <tr> 
   <td>COUNT()</td> 
   <td>计数</td> 
  </tr> 
  <tr> 
   <td>MAX()</td> 
   <td>最大值</td> 
  </tr> 
  <tr> 
   <td>MIN()</td> 
   <td>最小值</td> 
  </tr> 
  <tr> 
   <td>SUM()</td> 
   <td>求和</td> 
  </tr> 
 </tbody> 
</table>

示例：

SELECT AVG(goods_price) AS prices FROM tdb_goods;              #求tdb_goods表中goods_price的平均值
    
    SELECT COUNT(goods_id) AS counts FROM tdb_goods;               #查找tdb_goods表中的记录数
    
    SELECT MAX(goods_price) AS prices FROM tdb_goods;               #求tdb_goods表中goods_price的最大值
    
    SELECT MIN(goods_price) AS prices FROM tdb_goods;               #求tdb_goods表中goods_price的最小值
    
    SELECT SUM(goods_price) AS prices FROM tdb_goods;               #求tdb_goods表中goods_price的和

*  加密函数：

<table> 
 <thead> 
  <tr> 
   <th>函数名称</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>MD5()</td> 
   <td>信息摘要算法</td> 
  </tr> 
  <tr> 
   <td>PASSWORD()</td> 
   <td>密码算法</td> 
  </tr> 
 </tbody> 
</table>

示例：

SELECT MD5('admin');                #对字符admin进行MD5加密
    
    SELECT PASSWORD('admin');               #对字符admin进行PASSWORD加密

一般情况下，只有在修改mysql客户端密码时才使用`PASSWORD()`函数，如果密码是为了web页面准备的建议使用`MD5()`加密。

--------------------

### 自定义函数 ###

用户自定义函数（user-defined function,UDF）是一种对mysql扩展的途径，其用法与内置函数相同。

自定义函数的两个必要条件：

参数：可以有任意个
    
    返回值：只能有一个

函数可以返回任意类型的值，同样可以接收这些类型的参数。

*  创建自定义函数：

CREATE FUNCTION function_name RETURNS {STRING | INTEGER | REAL | DECIMAL} routine_body;

routine\_body表示函数体，函数体包括：

1. 函数体由合法的SQL语句构成
    
    2. 函数体可以是简单的SELECT或INSERT语句
    
    3. 函数体如果为复合结构，则使用BEGIN...END语句
    
    4. 复合结构可以包含声明、循环、控制结构

*  创建不带参数的自定义函数：

SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');
    
    CREATE FUNCTION f1() RETURNS VARCHAR(30) RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');
    
    SELECT f1();
    
    DROP FUNCTION f1();              #删除自定义函数f1()

*  创建带参数的自定义函数：

CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED) RETURNS FLOAT(10,2) UNSIGNED RETURN (num1+num2)/2;
    
    SELECT f2(10,15);               #求10和15的平均值，结果为12.50

*  创建具有复合结构函数体的自定义函数：

DELIMITER // #修改命令定界符，由默认的 ; 改为 //
    
    CREATE FUNCTION adduser(username VARCHAR(20)) RETURNS INT UNSIGNED BEGIN INSERT test(username) VALUES(username); RETURN LAST_INSERT_ID(); END //
    
    DELIMITER ;
    
    SELECT adduser('Rose');             #添加用户

--------------------

### mysql存储过程 ###

存储过程是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理。

存储过程的优点：

增强SQL语句的功能和灵活性
    
    实现较快的执行速度
    
    减少网络流量

*  创建存储过程：

CREATE [DEFINER = {
       user | CURRENT_USER}] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body;

proc_parameter：[IN | OUT | INOUT] param_name type
    
    IN，表示该参数的值必须在调用存储过程时指定，并且不可返回
    
    OUT，表示该参数的值可以被存储过程改变，并且可以返回
    
    INOUT，表示该参数的值在调用存储过程时指定，并且可以被改变和返回

characteristic表示特性，

COMMENT 'string' | {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} | SQL SECURITY {DEFINER | INVOKER}
    
    COMMENT：注释
    
    CONTAINS SQL：包含SQL语句，但不包含读或写数据的语句
    
    NO SQL：不包含SQL语句
    
    READS SQL DATA：包含读数据的语句
    
    MODIFIES SQL DATA：包含写数据的语句
    
    SQL SECURITY {DEFINER | INVOKER}：指明谁有权限来执行

routine\_body表示过程体，过程体包括：

1. 过程体由合法的SQL语句构成
    
    2. 过程体可以是“任意”SQL语句
    
    3. 过程体如果为复合结构，则使用BEGIN...END语句
    
    4. 复合结构可以包含声明、循环、控制结构

调用存储过程：

CALL sp_name([parameter[,...]]);                #带参数调用
    
    CALL sp_name[()];               #不带参数调用

*  创建不带参数的存储过程：

CREATE PROCEDURE sp1() SELECT VERSION();                #创建存储过程sp1()，查询版本
    
    CALL sp1();

*  创建带IN类型参数的存储过程：

DELIMITER //
    
    CREATE PROCEDURE removeUserById(IN id INT UNSIGNED) BEGIN DELETE FROM users WHERE id = id; END // #前面的id表示users表中的id字段，后面的id为存储过程参数
    
    DELIMITER ;
    
    CALL removeUserById(3);

目的是删除users表中id为3的记录，但其实users表中的记录已经全部删除了，这是因为mysql将两个id都作为字段处理。

而存储过程的过程体无法直接修改，所以需要先删除存储过程后再新建。

删除存储过程：

DROP PROCEDURE [IF EXISTS] sp_name;

重新在users表中插入记录，

DROP PROCEDURE removeUserById;
    
    DELIMITER //
    
    CREATE PROCEDURE removeUserById(IN pid INT UNSIGNED) BEGIN DELETE FROM users WHERE id = pid; END //
    
    DELIMITER ;
    
    CALL removeUserById(3);

此时可以发现存储过程删除记录正常。

*  创建带IN和OUT类型参数的存储过程：

DELIMITER //
    
    CREATE PROCEDURE removeUserAndReturnUserNums(IN pid INT UNSIGNED,OUT userNums INT UNSIGNED) BEGIN DELETE FROM users WHERE id = pid; SELECT COUNT(id) FROM users INTO userNums; END //
    
    DELIMITER ;
    
    CALL removeUserAndReturnUserNums(27,@nums);
    
    SELECT @nums;

@nums就是变量，通过变量接收存储过程中返回的结果。

*  创建带多个OUT类型参数的存储过程：

DELIMITER //
    
    CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN page SMALLINT UNSIGNED,OUT deleteUsers SMALLINT UNSIGNED,OUT userCounts SMALLINT UNSIGNED) BEGIN DELETE FROM users WHERE age = page; SELECT ROW_COUNT() INTO deleteUsers; SELECT COUNT(id) FROM users INTO userCounts; END //
    
    DELIMITER ;
    
    SELECT COUNT(id) FROM users WHERE age = 20;
    
    CALL removeUserByAgeAndReturnInfos(20,@a,@b);
    
    SELECT @a,@b;
    
    SELECT * FROM users;

*  存储过程与自定义函数的区别：

1. 存储过程实现的功能要复杂一些；而函数的针对性更强
    
    2. 存储过程可以返回多个值；而函数只能有一个返回值
    
    3. 存储过程一般独立地来执行；而函数可以作为其他SQL语句的组成部分

--------------------

### 存储引擎 ###

mysql可以将数据以不同的技术存储在文件（内存）中，这种技术就称为存储引擎。

每一种存储引擎使用不同的存储机制、索引技巧、锁定水平，最终提供广泛且不同的功能。

mysql支持的存储引擎：

MyISAM
    
    InnoDB
    
    Memory
    
    CSV
    
    Archive

*  并发控制：

当多个连接对记录进行修改时保证数据的一致性和完整性。

锁：

共享锁（读锁）：在同一时间段内，多个用户可以读取同一个资源，读取过程中数据不会发生任何变化。
    
    排他锁（写锁）：在任何时候只能有一个用户写入资源，当进行写锁时会阻塞其他的读锁或写锁操作。

锁颗粒：

表锁，是一种开销最小的锁策略
    
    行锁，是一种开销最大的锁策略

*  事务处理：

事务用于保证数据库的完整性。

事务的特性：

原子性
    
    一致性
    
    隔离性
    
    持久性

*  外键：

外键是保证数据一致性的策略。

*  索引：

索引是对数据表中一列或多列的值进行排序的一种结构。

普通索引
    
    唯一索引
    
    全文索引
    
    btree索引
    
    hash索引

*  存储引擎的特点：

<table> 
 <thead> 
  <tr> 
   <th>特点</th> 
   <th>MyISAM</th> 
   <th>InnoDB</th> 
   <th>Memory</th> 
   <th>Archive</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>存储限制</td> 
   <td>256TB</td> 
   <td>64TB</td> 
   <td>有</td> 
   <td>无</td> 
  </tr> 
  <tr> 
   <td>事务安全</td> 
   <td>-</td> 
   <td>支持</td> 
   <td>-</td> 
   <td>-</td> 
  </tr> 
  <tr> 
   <td>支持索引</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>锁颗粒</td> 
   <td>表锁</td> 
   <td>行锁</td> 
   <td>表锁</td> 
   <td>行锁</td> 
  </tr> 
  <tr> 
   <td>数据压缩</td> 
   <td>支持</td> 
   <td>-</td> 
   <td>-</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>支持外键</td> 
   <td>-</td> 
   <td>支持</td> 
   <td>-</td> 
   <td>-</td> 
  </tr> 
 </tbody> 
</table>

MyISAM适用于事务处理不多的情况；InnoDB适用于事务处理比较多、需要有外键支持的情况。

*  设置存储引擎：

修改mysql配置文件来实现：
        default-storage-engine = engine_name
    
    或
    
    创建数据表命令实现：
        CREATE TABLE table_name(...) ENGINE = engine_name;
        
    或
    
    修改数据表命令实现：
        ALTER TABLE table_name ENGINE [=] engine_name

--------------------