Mysql基础笔记
Mysql 是广泛使用的其中一种常见的关系型数据库
安装与配置
Mysql本体安装：
- 进入官网点击"Dowload"
- 根据需求下载对应版本，注意路径中避免出现中文。
Navcat安装：
3. 安装Mysql的可视化工具Navicat，毕竟纯命令敲打虽然很牛，但还是挺麻烦的。
SQL基础
SQL语句分类
DDL（Data Definition Languages）：
数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引、等数据库对象。
数据库：
1、创建数据库
create database 库名;
2、展示所有的数据库
show databases;
3、进入数据库
use 库名;
4、展示数据表
show tables;
5、删除数据库
drop database 库名;
表：
1、创建表
create table 表名 (字段名1 类型1 , ... , 字段名n 类型n );
2、删除表
drop table 表名;
3、修改表名
alter table 旧表名 rename 新表名;
4、展示表结构
desc 表名;
5、展示创表的语句
show create table 表名;
字段：
1、增加表字段
alter table 表名 add column 字段名 类型;
2、删除表字段
alter table 表名 drop column 字段名;
3、修改字段名
alter table 表名 change 字段原名 字段新名 新字段类型;
4、修改字段类型
alter table 表名 modify 字段名 新类型;
注意：在Mysql8.0之前，DDL是不支持事务回滚的，会将之前的输入过的不管是DML还是什么的都自动提交了。
这是因为内存空间有限，DDL是修改数据字典，而且通常不会有长时间的DDL执行。数据库自动提交有助于控制数据字典的内存占用，把更多的空间留给DML。
DML（Data Manipulation Languages）：
数据操控语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性。
插入（ insert into ）：
-- 必须要插入所有的字段
insert into 表 values(值1，值2，值n);
-- 选择字段插入
insert into 表 (字段1，字段n) values (值1，值n);
删除（ delete ）：
-- 根据条件删除（当然也可以不加 where 条件）
delete from 表名 where 条件;
-- 清空表数据
truncate table 表名;
修改（ update ）：
-- 修改条件中的多个字段值
update 表名 set 字段1=值1,字段n=值n where 条件;
查询（ selete ）：
-- 表中查找所有指定字段数据
select 字段(*) from 表;
-- 去重查询
select distinct 字段 from 表;
-- 根据指定条件从表中查找字段数据
select 字段 from 表 where 条件;
-- 排序查询（默认升序asc）
select 字段 from 表 order by 字段 升降序;
-- 多字段排序查询
select 字段 from 表 order by 字段1 asc,字段2 desc;
-- 结果集限制显示多少条数据
select 字段 from 表 limit 数量;
-- 从哪里开始查多少条内容
select 字段 from 表 limit 偏移量,偏移后截取数;
-- 按字段分组查询
selete 字段 from 表 group by 字段;
-- 根据条件按字段分组查询（与 where 筛选不同，having是对于结果对象进行筛选）
selete 字段 form 表 group by 字段 having 条件;
多表查询：
-- 内连接（将两个表中存在的连结关系的字段又符合连结关系的记录的联结）
select 表1.字段, 表n.字段 from 表1,表n where 等值连接条件;
select 表1.字段, 表n.字段 from 表1 inner join 表n on 等值连接条件;
-- 外连接（会选出其他不匹配的记录）
-- 1、左连接
select * from 表1 left join 表n on 等值连接条件;
-- 2、右连接
select * from 表1 right join 表n on 等值连接;
-- 3、全外链接
select * from 表1 union join 表n on 等值连接条件;
-- 子查询（进行查询时需要嵌套查询另外一个select语句的结果）
select* from 表1 where 表1.字段 in (select 表n.字段 from 表n);
-- 用于子查询的关键字包括in、not in、=、！=、 exists、not exists等
-- 记录联合（将结果合并到一起显示）
SELECT * FROM 表1 UNION SELECT * FROM 表n
SELECT * FROM 表1 UNION ALL SELECT * FROM 表n
注意：
- UNION ALL 区别是把结果集合并在一起，而 UNION是将 UNION ALL后的结果进行一次 DISTINCT,去重记录后的结果
DCL（Data Control languages）：
数据控制语句，用于控制不同数据段直接的许可和访问级别的语句，这些语句定义了数据库、表、字段、用户的访问权限和安全级别。
-- 创建用户并赋予权限：
grant 权限 on 库.表(*) to '用户'@'主机' identified by '密码';
-- 删除用户权限：
revoke 权限 on 库.表(*) from '用户'@'主机';
-- 备份
mysqldump -u 用名 -p 密码 表名1 表名n > d:\data.sql
-- 恢复
mysqldump -u 用名 -p 密码 表名1 表名n < d:\data.sql
注意：在权限处写 all 说明全部权限，主机若为%，任何来源的主机均可用这个用户访问)
字段类型分类
1. 数值型
| 数据类型 | 所占字节 | 值范围 |
|---|---|---|
| tinyint | 1字节 | -128~127 |
| smallint | 2字节 | -32768~32767 |
| mediumint | 3字节 | -8388608~8388607 |
| int | 4字节 | 范围-2147483648~2147483647 |
| bigint | 8字节 | +-9.22*10的18次方 |
| 数据类型 | 所占字节 | 值范围 |
|---|---|---|
| float(m, d) | 4字节 | 单精度浮点型，m总个数，d小数位 |
| double(m, d) | 8字节 | 双精度浮点型，m总个数，d小数位 |
| decimal(m, d) | - | decimal是存储为字符串的浮点数 |
2. 字符型
| 数据类型 | 所占字节 | 值范围 |
|---|---|---|
| CHAR | 0-255字节 | 定长字符串 |
| VARCHAR | 0-255字节 | 变长字符串 |
| TINYBLOB | 0-255字节 | 不超过255个字符的二进制字符串 |
| TINYTEXT | 0-255字节 | 短文本字符串 |
| BLOB | 0-65535字节 | 二进制形式的长文本数据 |
| TEXT | 0-65535字节 | 长文本数据 |
| MEDIUMBLOB | 0-16 | 777 |
| MEDIUMTEXT | 0-16 | 777 |
| LOGNGBLOB | 0-4 | 294 |
| LONGTEXT | 0-4 | 294 |
| VARBINARY(M) | 允许长度0-M个字节的定长字节符串 | 值的长度+1个字节 |
| BINARY(M) | M | 允许长度0-M个字节的定长字节符串 |
3. 日期型
| 数据类型 | 所占字节 | 值范围 |
|---|---|---|
| date | 3字节 | 日期，格式：2014-09-18 |
| time | 3字节 | 时间，格式：08:42:30 |
| datetime | 8字节 | 日期时间，格式：2014-09-18 08:42:30 |
| timestamp | 4字节 | 自动存储记录修改的时间 |
| year | 1字节 | 年份 |
4. 复合型
| 数据类型 | 所占字节 | 值范围 |
|---|---|---|
| ENUM | 1-255个成员：1字节；256-65535个成员：2字节 | 存储预先定义好的字符串列表，插入和更新必须使用列表中的值，如果插入值不在列表中，则插入空串 |
| SET | 1-8个成员：1字节；9-16个成员：2字节；17-24个成员：3字节；25-32个成员：4字节；33-64个成员：8字节 | 存储预先定义好的字符串列表，字段值可为集合中任意成员值。SET不按索引存储，而是以位图方式存储 |
变量
1. 会话变量
定义形式：
set @变量名 = 值；
说明：
- 跟php类似，第一次给其赋值，就算定义了
- 它可以在编程环境和非编程环境中使用！
- 使用的任何场合也都带该”@”符号。
2. 普通变量
定义形式：
declare 变量名 类型 【default 默认值】；
设置值：
set 变量名 = 值；
说明：
- 它必须先声明（即定义），此时也可以赋值；
- 它只能在编程环境（即存储过程，函数，触发器这样的）中使用；
3. 变量赋值形式
语法一：(此语法中的变量必须先使用declare声明)
set 变量名 = 表达式;
语法二：
set @变量名=表达式;
语法三：
select @变量名:=表达式;
此语句会给该变量赋值，同时还会作为一个select语句输出’结果集’
语法四：
select 表达式 into @变量名;
虽然看起来是select语句，但其实并不输出“结果集”，而是给变量赋值。
常用运算符
1. 算术运算符
+、-、*、/、%
注意：mysql没有 ++ 和 -- 运算符
2. 关系运算符
>、>=、<、<=、=（等于）、<>（不等于）!=(不等于)
3. 逻辑运算符
and（与）、or（或）、not（非）
mysql常用函数
1. 字符串函数
| 函数 | 描述 |
|---|---|
| CONCAT(S1,S1,...Sn) | 连接S1....为一个字符串 |
| INSERT(str,x,y,instr) | 将字符串str从x位置开始，y个字符长的子串替换为字符串instr |
| LOWER(str) | 将字符串str中的所有字符变为小写 |
| UPPER(str) | ...大写... |
| LEFT(str，x) | 返回字符串str最左边的x个字符 |
| RIGHT(str，x) | ...右边... |
| LPAD(str，n，pad) | 用字符串pad对str最左边进行填充，知道长度为n个字符长度。 |
| RPAD(str，n，pad) | ...右边... |
| LTRIM(str) | 去掉字符串str左侧的空格 |
| RTRIM(str) | ...右侧... |
| TRIM(str) | 去掉字符串行尾和行头的空格 |
| REPEAT(str，x) | 返回str重复x次的结果 |
| REPLACE(str，a，b) | 用字符串b替换字符串str中所出现的字符串a |
| STRCMP(s1，s2) | 比较字符串s1和s2 |
| SUBSTRING(str，x，y) | 返回从字符串str x位置起y个字符串长度的字串 |
2. 数值函数
| 函数 | 描述 |
|---|---|
| ABS(x) | 返回x的绝对值 |
| CEIL(x) | 返回大于x的最小整数值 |
| FLOOR(x) | 返回小于X的最大整数值 |
| MOD(x，y) | 返回x/y的模 #求余数 |
| RAND() | 返回0～1的随机值 |
| ROUND(x，y) | 返回参数x的四舍五入的有y位小数的值 |
| TRUNCATE(x，y) | 返回数字x截断为y位小数的结果 |
3.日期和时间函数
| 函数 | 描述 |
|---|---|
| CURDATE() | 返回当期日期 |
| CURTIME() | 返回当前时间 |
| NOW() | 返回当前的日期和时间 |
| UNIX_TIMESTAME(date) | 返回日期date的UNIX时间戳 |
| FROM_UNIXTIME | 返回UNIX时间戳的日期值 |
| WEEK(date) | 返回日期date为一年中的第几周 |
| YEAR(date) | 返回日期date的年份 |
| HOUR(time) | 返回time的小时值 |
| MINUTE(time) | 返回time的分钟值 |
| MONTHNAME(date) | 返回date的月份 |
| DATE_FORMAT(date，fmt) | 返回按字符串fmt格式化日期date值 |
| DATE_ADD(date，INTERVAL expr type) | 返回一个日期或时间值加上一个时间间隔的时间值 |
| DATEDIFF(expr，expr2) | 返回起始时间expr和结束时间expe2之间的天数 |
4.流程函数
| 函数 | 描述 |
|---|---|
| IF(value，t f) | 如果value是真，返回t；否则返回f |
| IFNULL(value1，value2) | 如果value1不为空，返回value1，飞则返回value2 |
| CASE WHEN[value1] THEN[result1]...ELSE[default]END | 如果value1是真，返回resul1，否为返回default |
| CASE [expr] WHEN [value1] THEN[result1]... ELSE[default]END | 如果expr等于value1，返回result1，否则发挥default |
5.其他常用函数
| 函数 | 描述 |
|---|---|
| DATABASE() | 返回当前数据库名 |
| VERSION() | 返回当前数据库版本 |
| UESR() | 返回当前登录用户名 |
| INET_ATON(IP) | 返回IP地址的数字表示 |
| INET_NTOA(num) | 返回数字代表的IP地址 |
| PASSSWORD(str) | 返回字符串str的加密版本 |
| MD5() | 返回字符串str的MD5值 |
6. 其他循环编程
- loop循环
- while循环
- repeat循环
数据库功能
视图
视图可以看成数据库中一张虚拟表，方便用户只对某些表中的某些字段进行操作。
视图的特点
- 视图的数据并不是另外保存的，而是属于表里的，并能像表一样进行增删改查；
- 视图不能被更改，表修改或删除后应该删除视图重建；
- 视图数量没有限制，但命名不能和其他视图和表名重复，具有唯一性；
- 视图可以被嵌套；
- 视图不能索引，不能有相关联的触发器；
视图的好处
- 使操作简单：
为复杂且使用频繁的操作定义一个视图，简化查询的步骤 - 增加数据的安全性：
用户只能查询和修改指定显示出来的数据，保证敏感信息不会被看到 - 提高表的独立性：
可以屏蔽原有表结构变化所带来的影响，即使是增删了列，对视图也不会造成影响
视图的操作
- 创建视图：
create view 视图名 as 查询语句
- 修改视图：
create or replace view 视图名 as 查询语句
- 删除视图：
drop view 视图名
- 使用视图：
select * from 视图名
存储过程
存储过程是一组为了完成特定功能的SQL语句集，相当于封装的方法。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。
存储过程的好处：
- 存储过程创建后可以在程序中多次调用执行，不必重新编写该sql语句；
- 存储过程能够实现较快的执行速度，因为存储过程只在创造时进行编译，不必重新编译该sql语句，也就是预编译；
- 存储过程能减轻网络流量，客户机调用存储过程时，网络中传递的只是调用语句；
- 存储过程可被作为一种安全机制来利用，进行权限限制，从而对数据访问的限制；
存储过程的操作：
- 创建存储过程：
create procedure 存储过程名（参数列表）
Begin
存储过程内容
End
形参列表：
- IN —— 代表输入参数(如果没有注明, 参数默认的类型为 in)
- OUT —— 输出参数,不管有没有传值里面都是null
- INOUT —— 既可以做为输入参数，也可以做为输出参数
- MySQL 存储过程名字后面的“()”是必须的，即使没有一个参数，也需要“()”
- 调用存储过程：
call 存储过程名称（实参列表）
- 删除存储过程：
drop procedure 存储过程名称
触发器
触发器是一个特殊的存储过程，它是由事件触发而执行的某个被动操作，例如insert、update、delete的时候，就会自动执行该代码块，从而让表具有联动关系或保持更新数据。
触发器的要点
- 在指定的数据库事件发生时自动执行；
- 触发器必须定义在特定的表上；
- 当触发器关联的表删除时，自动删除触发器；
- 自动执行，不能直接调用；
触发器的好处
- 相对于外部程序、存储过程，触发器能更快更高效的维护数据
触发器的操作
- 创建触发器：
create trigger 触发器名 before/after 事件 on 表名 for each row
Begin
(执行语句)
End
- 删除触发器：
drop trigger 触发器名;
- 查看触发器
show triggers;
