# :-: MYSQL基础
[toc]
### 1\. mysql常见命令
* 查看当前所有的数据库`show databases`
* 使用指定的数据库 `use 数据库名`
* 查看数据库下的所有表 `show tables`、`show tables from 数据库名`
* 查看表的详细信息`desc 表名`
* 查看mysql标本:`cmd中：mysql --version`、`mysql服务端中：select version()`
* mysql服务启动与停止：`net start mysql`、`net stop mysql`
### 2. 函数
1.分组函数
* max
* min
* count
* sum
* avg
2. 单行函数
* concat
* instr
```
select instr('123666','666'); -- 输出:4
找不到返回0
```
* substr:
```
select suvstr('1234567',6) -- 输出：67
select substr('1234567',2,3) -- 输出：234
```
* upper/lower
* trim
```
select trim(' abc abc '); -- 输出:abc abc
select trim('aa' from 'aaa嘻嘻aaa嘻嘻aaaa') -- 输出:a嘻嘻aaa嘻嘻
```
* lpad/rpad
```
select lpad('张三',3,'#') -- 输出:#张三
select lpad('张三',1,'#') -- 输出:张
select rpad('张三',1,'#') -- 输出:张
select rpad('张三',3,'#') -- 输出:张三#
```
* replace
* ifnull:`ifnull(commission_percentage,0)`
* length 输出字节数
```
select lenght('张三')； -- GBK编码中输出：4，UTF-8中输出：6
```
3.日期函数
* date_format
* str_to_date
```
%Y 年
%c或%m 月
%d 日
%H 24H
%h 12H
%i 分
%S或% 秒
%p 上下午
```
4.数学函数
* round
* ceil
* floor
* truncate`select truncate('3.14159',4); 输出:3.1415`
* mod
```
select mod(10,3); -- 输出:1
select mod(10,-3); -- 输出:1
select mod(-10,3); -- 输出:-1
```
5.流程控制函数
* if
* case
### 3.查询
1. 分组查询 group by
可按多个字段进行分组`group by 字段1，字段2...`
```
where和having的区别：
where：对原表中存在的字段进行筛选，放在group by之前。分组前筛选。
having: 对原表中不存在的字段进行筛选，放在group by之后。分组后筛选。
```
2.连接查询
* sql 92
仅支持内连接(等值连接、非等值连接、自连接)
```
案例：
select * from employees e,departments d
where e.department_id = d.department_id and e.employee_id = 1001
```
* sql 99
支持内连接([inner] join)、外连接[左外、右外、全外(mysql不支持)]、交叉连接(cross join 笛卡尔积)
```
案例1：
select * from employees e inner join departments d
on e.department_id = d.department_id
where e.employee_id = 1001;
案例2:显示所有女生的男朋友信息，没有的返回null。left outer join outer可省略，左表为主表，右表为从表。
select * from girls g left outer join boys b
on g.boyfriend_id = b.id;
```
```
on:连接条件，where筛选条件。可提高分离性，便于阅读。
```
3. 子查询
```
标量子查询：一行一列
行子查询：一行多列
列子查询：一列多行
表子查询：多行多列
```
```
多行操作符：in/not in、some、any、all
```
select后仅支持标量子查询。
```
案例1：
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id = d.`department_id`
) 个数
FROM departments d;
案例2：
SELECT (
SELECT department_name
FROM departments d
INNER JOIN employees e
ON d.department_id=e.department_id
WHERE e.employee_id=102
) 部门名;
```
from后支持表子查询，要给子查询起别名。
```
案例：查询每个部门的平均工资的工资等级。
SELECT ag_dep.*,g.`grade_level`
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
```
where/having后标量子查询、列子查询、行子查询。
exists相关子查询。
```
案例：查询没有女朋友的男神信息
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
SELECT boyfriend_id
FROM beauty b
WHERE b.`boyfriend_id`=bo.`id`
);
```
4. 联合查询 union(去重)、union all
```
注意：
1>.要求多条查询语句查询列数是一致的。
2>.要求多条查询语句的查询的每一列的类型和顺序最好一致(或兼容)。
```
### 4. DML和DDL
* DML：insert、update、delete
```
inset：
insert into student(stu_id,stu_name,stu_score) values(1001,'xss',92);
inset into student set id=1001,stu_name='xss',stu_score=92;
前一种支持多行插入，后一种不支持。
delete：
delete from 表名 ... where ...;
案例：多表删除。删除黄晓明的信息以及他女朋友的信息
DELETE b,bo
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`='黄晓明';
update：
update 表名 set ... where ....;
案例：修改多表记录，修改没有男朋友的女神的男朋友编号都为2号
UPDATE boys bo
RIGHT JOIN beauty b ON bo.`id`=b.`boyfriend_id`
SET b.`boyfriend_id`=2
WHERE bo.`id` IS NULL;
```
```
面试题：truncate和delete的区别：
1.delete 可以加where 条件，truncate不能加
2.truncate删除，效率高一丢丢
3.假如要删除的表中有自增长列，如果用delete删除后，再插入数据，自增长列的值从断点开始，
而truncate删除后，再插入数据，自增长列的值从1开始。
4.truncate删除没有返回值，delete删除有返回值
5.truncate删除不能回滚，delete删除可以回滚.
```
* DDL：create、drop、alter
### 5. 数据类型
* 数值类型
整数：tinyint(1个字节) smallint(2) mediumint(3) int/integer(4) bigint(8)
一个字节：有符号：-128~127 无符号：0-255
```
① 如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
② 如果插入的数值超出了整型的范围,会报out of range异常，并且插入临界值
③ 如果不设置长度，会有默认的长度,度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用.
```
小数：浮点数(double、float)、定点数[dec(M,D)、decimal(M,D),M为有效位数，D为小数位数] 定点数精确度高。
* 字符：主要有：varchar（长度不可省略，可变长）、char(长度可省略，定长)
* 日期：date(日期) 、 time(时间) 、datetime(时期+时间。不受时区影响) 、timestamp(时期+时间。受时区影响)
### 6. 事务
1.事务定义： 一个或一组SQL语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。
2.事务特性：ACID
```
1.原子性(Atomicity):一个事务不可再分割，要么都执行要么都不执行
2.一致性(Consistency):一个事务执行会使数据从一个一致性状态切换到另外一个一致性状态
3.隔离性(Isolation):一个事物的执行不受其他事务的干扰
4.持久性(Durability):一个事务一旦提交，则会永久的改变数据库的数据
```
3.事务创建
```
1.隐式事务：事务没有明显的开启和结束标记
例如：insert、update、delete语句
2.显示事务：事务具有明显的开启和结束的标记
开启事务：set autocommit=0;
结束事务：commit;(提交事务) rollback;(回滚事务)
在开始事务和结束事务中间填写要执行的增、删、改、查的操作（DML）。
```
4. 事务并发问题
```
脏读:事务A读取了另一个事务未提交的数据.
不可重复读:一个事务范围内的2个相同的查询前后返回不同的数据。A事务读取了B事务已提交的数据。
幻读: A事务读取了B事务新增的数据。
```
**脏读必须避免**
5.事务的隔离级别
```
read uncommited 可提交读 事务并发问题：脏读、不可重复读、幻读
read committed 不可提交读(oracle默认) 事务并发问题：不可重复读、幻读
repeatable read 可重复读(mysql默认) 事务并发问题：幻读
serializable 序列化读 无上述事务并发问题。事务串行化顺序执行，效率低，比较消耗数据库性能。
```
### 7. 视图
视图只保存了sql逻辑，不占实际物理空间。
优点:可实现对表的权限管理，只暴露特定的字段。可将常用的复杂的查询写入视图。
缺点：效率低。
* 创建视图
```
create view 视图名
as
查询语句;
//案例：查询姓名中包含a字符的员工名、部门名和工种信息
//①创建(先创建出一个视图，方便以后的查询)
create view myv1
as
select last_name,department_id,job_title
from employees e
join departments d on e.department_id = d.department_id
join jobs j on j.job_id = e.job_id;
//②使用
select * from myv1 where last_name like '%a%';
```
* 视图修改
```
create or replace view 视图名
as
查询语句;
alter view myv3
as
查询语句;
```
* 视图删除
```
drop view 视图名,视图名,...;
```
* 查看视图
```
desc myv3;
show create view myv3;
```
### 8. 变量
* 系统变量
```
系统变量分为全局变量（global）和会话变量（session）。
说明：变量由系统定义，不是用户定义，属于服务器层面
注意：全局变量要加global关键字，若不写默认是会话级别
//1.全局变量
show global variables; //查看所有全局变量
show global variables like "%char"; //查看满足条件的部分全局变量
select @@global.autocommit; //查看指定的系统变量的值
set @@global.autocommit=0; //为变量赋值
//2.会话变量
show session variables;
```
* 自定义变量
```
自定义变量分为用户变量和局部变量 。
说明：变量由用户自定义，要求用户声明、赋值，然后才能使用。
用户变量：
赋值操作符：=或:=
①声明并初始化
SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;
②赋值（更新变量的值）
方式一：
SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;
方式二：
SELECT 字段 INTO @变量名
FROM 表;
③使用（查看变量的值）
SELECT @变量名;
局部变量：
/*
作用域：仅仅在定义它的begin end块中有效
应用在 begin end中的第一句话
*/
①声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 【DEFAULT 值】;
②赋值（更新变量的值）
方式一：
SET 局部变量名=值;
SET 局部变量名:=值;
SELECT 局部变量名:=值;
方式二：
SELECT 字段 INTO 具备变量名
FROM 表;
③使用（查看变量的值）
SELECT 局部变量名;
```
### 9.存储过程、函数
```
含义：一组预先编译好的SQL语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
```
* 存储过程
```
定义：
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体（一组合法的SQL语句）
END
```
参数模式：in 、out 、inout
参数列表：参数模式 参数名 参数类型
```
调用：call 存储过程名(实参列表)
删除：drop procedure 存储过程名；
```
* 函数
```
定义：
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END
```
参数列表 ：参数名 参数类型
函数体：必须得有return语句，否则会报错。
```
调用：SELECT 函数名(参数列表)；
删除：drop function 函数名；
```
函数和存储过程的区别：
```
存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新。
函数：有且仅有1 个返回，适合做处理数据后返回一个结果。
```
### 10. 约束
not null 、default、primary key 、foreign key 、 unique、check(mysql不支持)
```
外键约束：
set null 删除后设置为null
restrict 不能删除
no action 不能删除
cascade 级联删除
```
自增长列：
```
一个表至多一个。列的类型只能为数值类型。
```
