设计原则

第一范式：原子性不可拆分
第二范式：属性完全依赖于主关键字
第三范式：关系表中不包含已在其他表中包含的非主关键字信息(相当于python类的概念)

MySQL信息

—默认端口 3306

cmd中查看mysql的版本号
mysql —version

cmd中登录mysql
mysql -u 输入用户名 -p
输入密码

数据库命名规则
任意字母、阿拉伯数字、下划线和美元符，不能已数字开头，关键字

MySQL存储引擎

(可以简单理解为表的类型)

用于：存储数据、为存储的数据建立索引、更新和查询数据

默认存储引擎：
InnoDB功能：给MySQL的表提供了事务、回滚、崩溃修复能力和多版本并发控制的事务安全。
InnoDB优势：对事务的处理能力是MySQL其他存储引擎无法比拟的。(要处理事务的时候就必须使用InnoDB引擎)。
支持自动增长列AUTO_INCREMENT。MySQL中自增长列必须为主键。
InnoDB是具有外键约束的表引擎。
InnoDB存储引擎中，表结构存储在.frm文件中，数据和索引存储在innodb_data_home_dir和innodb_data_file_path表空间中。

优点：良好的事务管理、崩溃修复能力、并发控制
缺点：读写效率差、占用的数据空间相对比较大

什么时候使用InnoDB：更新密集的表、事务、自动灾难恢复

MyISAM存储引擎：
文件类型：.frm .MYD .MYI

MyISAM文件存储形式：
1.MyISAM静态格式
如果所有表列的大小都是静态的(即不适用xBLOB、xTEXT或VARCHAR数据类型)，MySQL就会自动使用静态MyISAM格式。
2.MyISAM动态格式
如果有表列定义为动态的(使用了xBLOB、xTEXT或VARCHAR数据类型)，MySQL就会自动使用动态格式。

尽可能使用静态数据类型。
经常使用OPTIMIZE TABLE语句，它会整理表的碎片，恢复由于表更新和删除而导致的空间丢失。
3.MyISAM压缩格式
对于只读表，可以用myisampack工具将其转换为MyISAM压缩表来减少空间。

优点：占用空间小、处理速度快
缺点：不支持事务

MEMORY存储引擎：
特殊的存储引擎，使用存储在内存中的内容来创建表，而且所有数据也放在内存中。

MEMORY文件存储形式：
1.每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名于表名相同，扩展名为.frm。
该文件中只存储表的结构，而其数据文件都是存储在内存中。
2.默认使用哈希(HASH)索引，其速度要比B型树(BTREE)索引快。

优点：表大小受限、处理速度非常快
缺点：数据易丢失、生命周期短

数据类型

1. 数字类型
   1. 整数类型：
      1. TINYINT 取值范围0~255 一般用于存储0和1
      2. INT 取值范围-200亿~200亿 或 0~400亿 通常情况用INT
   2. 小数类型：
      1. FLOAT 通常情况下用FLOAT
      2. DECIMAL 一般用于价格

选择最小的可用类型，如果值永远不超过127，则使用TINYINT比INT要强
int 分为 int unsigned 和 int signed ，无符号就是0400亿，有符号就是-200亿200亿

1. 字符串类型
   CHAR 长度不可变
   VARCHAR 长度可变
   TEXT 长度可变
   从速度方面考虑，要选择固定的列，使用CHAR类型；
   从节省空间，使用动态的列，可用使用VARCHAR类型；
   要将列中的内容限制在一种选择，可用使用ENUM类型；(ENUMERATE枚举类型，相当于python中的enumerate()方法)
   允许在一个列中有多于一个的条目，可用使用SET类型；
   如果要搜索的内容不区分大小写，可用使用TEXT类型；
   如果要搜索的内容区分大小写，可用使用BLOB类型。

2. 日期和时间数据类型
   数据类型 取值范围 说明
   DATE 1000-01-01 9999-12-31 日期，格式YYYY-MM-DD
   TIME -838:58:59 835:59:59 时间，格式HH: MM: SS
   DATETIME 1000-01-01 00:00:00
   9999-12-31 23:59:59 日期和时间，格式YYYY-MM-DD HH: MM: SS
   TIMESTAMP 用现在的时间减去1970-01-01得到的一个数字，时间标签，在处理报告时使用显示格式取决于M的值
   一个时间段类的全部数字就是时间戳
   YEAR 1901-2155 年份可指定两位数字和四位数字的格式

   运算符

   算术运算符

   +加法
   -减法
   *乘法
   / 除法
   % 求余
   DIV 等同于除法
   MOD 等同于求余

   比较运算符

   = 等于

   大于
   < 小于
   !=或<> 不等于
   is null
   is not null
   between and
   in
   not in
   like
   not like
   regexp

—在select中使用比较运算符(满足返回1，否则返回0)
select 字段 比较运算符 值,其他字段 from 表名;

逻辑运算符

&&或and 与
||或or 或
!或not 非
xor 异或 (都为真或都为假则为0，其余为1)
优先级not 高于 and 高于 or

位运算

& 按位与。进行该运算时，数据库系统会将十进制转换为二进制数。然后对应操作数的每个二进制位上进行与运算。
| 按位或。
` 按位取反
^ 按位异或
<< 按位左移
>> 按位右移

流程语句

1. 判断流程语句
if ... then ...
    elseif ... then ...
    else ...
end if
--例：
delimiter //
create procedure example_if(in x int)
begin
if x = 1 then
select 1;
elseif x = 2 then
select 2;
else
select 3;
end if;
end;
2. 分支流程语句
case ...
when ... then ...
else ...
end case;
--例：
delimiter //
create procedure example_case(in x int)
begin
case x
when 1 then select 1;
when 2 then select 2;
else select 3;
end case;
end
3. while循环语句
while ... do
...
end while
--例：
delimiter //
create procedure example_while(out sum int)
begin
declare i int default 1;
declare s int default 0;
while i <= 100 do
set s = s + i;
set i = i + 1;
end while;
set sum = s;     
end
--调用该存储
call example_while(@s)//
select @s;
4. loop循环语句
loop
...
end loop
--例：
delimiter //
create procedure example_loop(out sum int)
begin
declare i int default 1;
declare s int default 0;
loop_label: loop
set s = s + i;
set i = i + 1;
if i > 100 then
leave loop_label;
end if;
end loop;
set sum = s;
end
//
5. repeat循环语句
repeat
...
until ...
end repeat
--例：
delimiter //
create procedure example_repeat(out sum int)
begin
declare i int default 1;
declare s int default 0;
repeat
set s = s + i;
set i = i + 1;
until i > 100
end repeat;
set sum = s;
end

数据操作

插入数据
insert into 表名(字段) values ...;             --插入一条
insert into 表名(字段) values ... , ...;          --插入多条
insert into 表名(字段) set ...;                  --插入一条
insert into 表名(字段) select ...;              --插入一个结果集
--更新数据
update 表名 set ... where ...;
--删除数据
delete from 表名 where [order by ...][limit 行数];
--清空表
truncate table 表名;
--查询语句
select ... from 表名 where [group by ... having ...][order by ...][limit 行数];
--查询结果行数为表1.字段行数*表2.字段行数，相当与oracle的full join
select 表1.字段,表2.字段 from 表1,表2 where ...;
--单表查询
select ... from 表名 where 字段 between ... and ...;
select ... from 表名 where 字段 like ...;                        --其中%代表0或多个，_代表1个
select group_concat(分组字段1)，聚合函数(分组字段2) from 表名 group by 分组字段1,分组字段2;
select ... from 表名 limit ...;                                  --limit在最后面
--聚合函数
count() 用于对除*以外的任何参数，返回所选择集合中非null值的行的数目。
--内连接
select * from 表1,表2 where 表1.字段 = 表2.字段;
--外连接
select * from 表1 left join 表2 on 表1.字段 = 表2.字段;
/子查询/
--带in关键字的子查询
select * from 表1 where 表1.字段 in (select 表2.字段 from 表2 where ...);
--带比较运算符的子查询
select * from 表1 where 表1.字段 = (select 表2.字段 from 表2 where ...);
--带exists关键字的子查询(用于检查子查询是否至少返回一行数据)
select * from 表1 where [not]exists (select 1 from 表2 where 表1.字段 = 表2.字段);
--带any关键字的子查询(表示满足其中任意一个条件)
select * from 表1 where 表1.字段 > any (select 表2.字段 from 表2);
--带all关键字的子查询(表示满足所有条件)
select * from 表1 where 表1.字段 > all (select 表2.字段 from 表2);
--合并查询
union        --去重
union all     --不去重
--正则表达式查询
select * from 表1 where 表1.字段 regexp '正则表达式';
/函数/
--数学函数(用于处理数字，其中包括绝对值函数、正弦函数、余弦函数获取随机数函数等)
abs(x)  返回x的绝对值
ceil(x)  返回不小于x的最小整数
ceiling(x)  返回不小于x的最小整数
floor(x)  返回不大于x的最小整数
rand(x)  返回0-1的随机数
pi() 返回pi值
truncate(x,2) 截取函数
sqrt(x) 求平方根函数
--字符串函数(用于处理字符串，其中包括字符串连接函数、字符串比较函数、字符串中字母大小写转换函数等)
select insert('mrsoft',3,4,'book'); 将soft替换成book
或
select insert('mrsoft',3,2,'book'); 将so替换成book
select upper('mrsoft'); 转大写
或
select ucase('mrsoft'); 转大写
select left('mrsoft',2); 截取左边两位
select right('mrsoft',2); 截取右边两位
select rtrim('mrsoft   '); 去除空格
select substr('mrsoft',1,2); 指定截取
select reverse('mrsoft'); 反转函数
select field('mrsoft','mr','mrsss','mrsoft'); 查找后续与第一个相匹配的，返回第几个
select locate('you','i love you'); 查找you在后面字符串中的第几个位置
select position('mr' in 'mrsoft'); 查找mr在后面字符串中的第几个位置
select instr('mrsoft','mr'); 子串在后面
--日期和时间函数(用于处理日期和时间，其中包括获取当前时间的函数、获取当前日期的函数、返回年份的函数和返回日期的函数)
select current_date(); 返回当前日期
或
select curdate();
select current_time(); 返回当前时间
或
select curtime();
select datediff('2021-01-01','2020-01-01'); 返回相隔多少天
select adddate('2021-01-01',interval '1 2'year_month); 加上1年2个月
select subdate('2021-01-01',interval '1 2'year_month); 减去1年2个月
--条件判断函数(用于在SQL语句中控制条件选择，其中包括if 、case 和 when)
case when ... then ... else ... end
--系统信息函数(用于获取MySQL数据库的系统信息，其中包括获取数据库名的函数、获取当前用户的函数和获取数据库版本的函数)
select version(); 查看版本号
select user(); 查看当前用户
select database(); 查看当前数据库
select connection_id(); 查看当前连接id
select session_usr(); 当前会话用户
select charset('aaa'); 查看当前字符集
select collation('bbb'); 查看当前排序方式
--加密函数(用于对字符串进行加密解密，其中包括字符串加密函数和字符串解密函数)
--其他函数(包括格式化函数和锁函数)
select format('3.1415926',2); 保留两位小数
select charset(convert('aaa' using gbk)); 改变字符集
select cast(字段 as 类型); 修改字段类型输出

视图

视图是一个虚拟的表，是从数据库中一个或多个表中导出来的表，其内容由查询定义。(相当于一个linux硬链接)数据库只存放了视图的定义，而并没有存放视图中的数据。
作用：安全性(比如说客户身份证信息之类)、可用使复杂的逻辑简单化易于理解和使用

--查看创建视图的权限
select select_priv,create_view_priv from mysql.user where user = '用户名';

--创建视图
create view 视图名(字段1,字段2...) as select ... from 表名 where ...;

--查看视图
describe 视图名;
show view;
show table status like '视图名';
show create view 视图名\G; --其中\G是分行展示便于查看

--修改视图
create or replace view 视图名(字段1,字段2...) as select ... from 表名 where ...;
alter view 视图名(字段1,字段2...) as select ... from 表名 where ...;

--更新视图
update 视图名 set 字段1 = '值' where ...;   更新视图表原表的数据也会变化

--不能更新的情况
视图中有聚合函数，分组，union，union all，子查询，类型为algorithm和temptable时，以及常量视图

--删除视图
drop view if exists 视图名;