**SQL**:与数据库通讯的语言
**MySQL**: DBMS, 用来操作数据库的软件(基于客户机-- 服务器的数据库)
| 效果 | 语句 |
| :----------: | :--------------------------------: |
| 显示表信息 | SHOW columns FROM table名 |
| | Describe table名 |
| 显示创建语句 | SHOW create database/table + 名字 |
| 显示错误 | show errors/warnings |
## 简单语句
| 效果 | 语句 |
| :----------------: | :---------------------------------------------: |
| 只返回不同(唯一)值 | SELECT **DISTINCT** 字段 from 表名 |
| 限制结果 | 语句后跟 **LIMIT** 起始位置, 检索行数;limit 1,5 |
| 排序 | **order by**字段1(DESC 降序) ,字段2; |
| | order by位于where后`(<> 不等于) |
| 过滤 | between 5 and 10 |
| | where 字段 is null |
| 模糊查询(慢) | where 字段 like jet%--后面随意 |
| | _ 匹配一个字符 |
| 正则表达式 | where 字段 REGEXP 正则 |
| | 转义使用两根\ \(mysql,正则各一根) |
> 数据库.表名 表名.字段名
>
> AND 优先级大于 OR
>
> select 'hello' regexp '0-9' 可以测试正则
>
> select now();
## 计算字段
| 效果 | 语句 |
| :----------------------------: | :----------------------------------------------------------: |
| 拼接两个字段 | select **Concat**(字段1,**' (',**字段2**')'**) from 表格... |
| 以一个字段分组, 另一个字段合并 | select id,**group_concat**(name) from a group by id; |
| | |
| | |
| 重复字符串 | select repeat ('ab' , 2); |
## 算数计算
| 效果 | 语句 |
| :--------: | :----------------------: |
| 多字段运算 | select **a*b as c** ... |
## 函数(移植性不高, 性能极好)
### 格式函数
| 效果 | 语句 |
| :---------------: | :----------------------------------------------------------: |
| 去掉字段某边空格 | select Trim/LTrim/RTrim(字段) ... |
| 转换为大写 | upper() |
| 转化为小写 | lower() |
| 返回串某侧的字符 | left() / right() |
| 返回串的长度 | length() |
| 找出串的一个字串 | locate() |
| 返回子串的字符 | substring() |
| 返回串的soundex值 | select ... from where soundex(字段) = soundex(输入值) |
| | 将任何文本串转换为描述其语音表示的字母数字模式的算法
使其能对串进行发音比较而不是字母比较 |
### 日期函数
> 日期格式: yyyy - mm - dd
>
> 如果要的是日期, 使用date()是个好习惯
### 数值处理函数
### 聚集函数
| 函数 | 说明 |
| :-----------: | :------------------------------------------: |
| AVG() | 返回某列的平均值 |
| COUNT() | 返回某列的行数 |
| | count(*) **所有行**/ count(字段)**有值**的行 |
| MAX() / MIN() | 返回极值 |
| | 非数值数据,max返回最后一行 |
| SUM() | 返回某列值之和 |
| 高级用法 | 说明 |
| :----------------------------------------------: | :----------------------: |
| select AVG(DISTINCT 字段) as avg_price from... | 查询字段中不同值的平均值 |
## 分组
### group by
> select 字段1, count(*) as 别名 from 表名 group by 字段;
>
> 将数据排序后
>
> + 在group by后使用 **with rollup**可以得到每个汇总的详细信息(可以看到空值)
### having
select 字段 from 表格 group by 字段 having 条件
> having和where的不同
>
> + Where 在数据分组前过滤
> + having 在数据分组后过滤
> where .... group by ... having ...
#### order by 和 group by的差别
## select 子句顺序
select ... from .. where .. group by .. having .. order by .. limit
## 子查询
### 相关子查询:
> 涉及**外部查询**的子查询
>
> select...from ... where (select ....)
>
> select 放在前面也可以, 看需求
>
> 任何时候列名可能有多义必须使用表名.列名
### 联接
**记得要有where**
```
select 字段1,表二.字段2 from 表一,表二 where 表一.字段1 = 表二.字段2 order by 字段3
```
#### 笛卡儿积(叉联结)
全部配对, 检索出的行的数目是两种表的行数相乘
### 内部联结
**等值联结/内部联结**
> 基于**两张表**的相同测试
>
> on替换where即可, 效果一样(有时性能高于where)
*支持多表联结*, 不过表太多性能不好
```mysql
select 字段1, 字段2 from 表1, 表2 ,表3 where 表1.字段1=表2.字段2 and 表2.字段3 = 表3.字段4
```
```mysql
select 表1.字段1,表2.字段2 from 表1 INNER JOIN 表2 on 表1.字段1 = 表2.字段3
```
### 高级联结(3种)
#### 表别名
> + 表别名只在查询执行中使用
> + 列表名会返回到客户机
#### 自联结
通常快于子查询, 应该**都试一下**, 比较性能
```mysql
select p1.字段1, p1.字段2 from 表1 as p1,表2 as p2 where p1.字段1 = p2.字段1 and p2.字段2 = 值
```
#### 自然联结
> 自然联结排除多次出现的列, 使每个列只返回一次
>
> 对表使用明确的子集来完成
#### 外部联结
> 联结包含了那些不在相关表中的行
```mysql
select 表1.字段1,表2.字段2 from 表1 LEFT OUTER JOIN 表2 on 表1.字段1 = 表2.字段3
```
> 有left outer join 和 right outer join 两种, 使用区别就是前后顺序而已
## 组合查询(UNION)
> 利用UNION 操作符将多条SELECT语句, 将结果组合成一个结果集\
多条 SELECT 语句之间使用UNION 联结即可(n条select语句, n -1 个 UNION )
+ 每个查询必须包含相同的列, 表达式 或 聚集函数
+ 列数据必须兼容(类型不一定要相同, 但数据库自己要相互转换)
+ UNION 默认去除重复行, 如果**不想去重**可以使用 UNION ALL
+ 只支持在最后使用一条**ORDER BY**
## 全文本搜索
> 为了使用全文本搜索, 必须索引被搜索的列, 而且要随着数据的改变不断的重新索引.
>
> 在对表列进行适当设计后, Mysql会自动进行所有索引和重新索引.
+ 有**较高优先级**的行优先返回 (出现频率, 出现位置等)
+ 速度极快
### 启用全文本搜索支持
+ 创建字段时增加子句: FULLTEXT(字段1,字段2...) --- 这样mysql将会在之后自动维护索引
+ 如果有大量数据需要导入, 可以**在之后指定**, 这样可以节省大量时间
### 进行全文本检索
+ match( ) 指定字段 -- 传递给Match()的值 必须和FULLTEXT()定义中的相同
+ against( ) 指定要使用的搜索表达式
```mysql
select 字段 from 表格 where Match(字段) Against ('搜索表达式')
```
### 使用查询拓展
+ 设法放宽全文本搜索结果的范围
+ 首先, 找出与搜索条件匹配的所有行
+ 其次, mysql检查所有匹配行并找出所有有用的词
+ 最后, mysql再次进行全文本搜索, 这次不仅使用原来的条件, 还使用所有有用的词
```mysql
select 字段 from 表 where Match(字段) Against('查询词' with Query EXPANSION )
```
### 布尔文本搜索(boolean mode)
**排列而不排序**
+ 以布尔方式, 可以提供关于如下内容的细节:
+ 要匹配的词
+ 要排斥的词
+ 排列提示
+ 表达式分组
+ 等..
*即使**没有**FULLTEXT索引也可以使用 (不过速度非常缓慢)*
+ 使用方法: 跟在**against字符串**之后使用
against('heavy -rope*' **IN BOOLEAN MODE**)
> ps:
>
> + 3个或3个以下的字符通常都被忽略
> + mysql带有一个内建的非用词(stopword)列表, 里面出现的词默认被忽略
> + 如果一个词出现在了50%以上的行, 会忽略之
> + 忽略词中出现的单引号(don't 索引为dont)
> + 不具有词分隔符(包括日语和汉语)的语言不能恰当的返回全文本搜索结果
## 数据插入
### 一条/多条插入
+ ```mysql
INSERT INTO 表名(字段1, 字段2, ...) values('值1','值2'), ('值3','值4')...;
insert LOW_PRIORITY INTO -- 可以降低insert语句的优先级(同样支持UPDATE 和 DELETE)--提高性能
```
+ 插入检索出的数据
```mysql
INSERT INTO 表1(字段1, 字段2) SELECT (字段1,字段2) from 表2
```
> 列名不用对应, 行数一样即可一次插入
```mysql
SELECT last_insert_id()
可以返回最后一个AUTO_INCRENENT(),并且可以将它用于后续的MySql语句
```
## 更新和删除数据
**!!! 切记用条件**
+ ```mysql
UPDATE 表名 SET 字段='值1',字段2='值2' WHERE 条件;
```
> 默认会出错会回滚, 如果不想回滚可以使用UPDATE IGNORE 表名
+ ```mysql
DELETE FROM 表格 WHERE 条件
```
TRUNCATE TABLE -- 删除所有表内的行(本质是删除表并重新创建一个)
可先用SELECT 进行测试 !! 保证筛选的是正确的记录
## 创建和操纵表
+ ```mysql
create table user
(
uid int primary key auto_increment,
uname varchar(30) NOT NULL
upassword varchar(30)
);
```
+ ''(两个单引号在非not null列中是允许的)
+ null值是没有值, 不是空值
**多字段做主键**
+ ```mysql
create table user2
(
id int not null,
name varchar(20) not null,
password varchar(20),
primary key(id,name)
);
```
### 常用引擎
默认InnoDB
+ 如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择
+ 如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率
+ 如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果
+ 如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive
### 更新表
+ ALTER TABLE 表名 ADD 字段 条件
```mysql
ALTER TABLE 表名 ADD 字段 char(20);
ALTER TABLE 表名 DROP COLUMN 字段;
```
+ 复杂的表结构可以选择重新创建
+ 用新的列布局创建一个新表
+ 使用了INSERT SELECT 语句迁移数据
+ 检验新表
+ 重命名旧表(如果确定可以删除她)
+ 用旧表原来的名字重命名新表
+ 根据需要, 重新创建触发器. 存储过程 . 索引 外键
### 删除表
> drop table 表名
### 重命名表
RENAME TABLE 表名1 TO 表名2;
### 视图
> 简化或者增加重用 复杂的代码
>
> 保存查询SQL语句
>
> 最好创建一个复用性高的视图
>
> 一般来说, 视图应用于检索, 而不用来更新
+ CREATE VIEW 视图名 as 视图语句 创建视图+
+ SHOW CREATE VIEW view-name 查看创建视图的语句
+ DROP VIEW view-name 删除视图
+ CREATE OR REPLACE VIEW 更新视图
例子:
创建一个返回已订购任意产品的所有客户的列表的视图
使用时: `select * from 视图名 where 字段='产品名';`