MySQL基础
一、SQL基本分类
1、 DDL 数据定义语言：对数据库的创建和删除 以及对数据库表的创建删除和修改 关键字：create、drop 2、 DML 数据操作语言：对数据库表中数据的增 删 改 关键字：insert、delete、update 3、 DQL 数据查询语言：对数据库表中数据 查询 关键字：select 4、 DCL 数据控制语言：指定用户 分配权限 关键字：grant、revoke 5、 事务
二、常用SQL语句
DDL 数据定义语言
一、 库的基本操作 1. 查看所有数据库 show databases; 2. 创建数据库 create database [if not exists] 库名; 3. 删除数据库 drop database [if exists] 库名; 4. 选中库 use 库名; 5. 查看创建数据库语句 show create database 库名; 二、 表的操作 1. 查看所有表 show tables; 2. 创建表 create table if not exists 表名(列名 类型 【约束】,列名 类型 【约束】,....); 3. 删除表 drop table if exists 表名; 4. 查看表结构 desc 表名; 5. 查看创建表语句 show create table 表名; 6. 修改表 改表名： Alter table 表名 Rename to 新表名； 改列名： Alter table 表名 change 原列名 新列名 类型； 改类型： Alter table 表名 modify 列名 新类型； 添加列: Alter table 表名 add 列名 类型； 删除列： Alter table 表名 drop 列名；DML 数据操作语言
插入： insert into 表名 values(值1，值2...)； insert into 表名(列1，列2，...) values(值1，值2，...)； insert into 表名(列1，列2，...) values(值1，值2，...)，(值1，值2，...)... ； 修改： update 表名 set 列1=值，列2=值，...[where 条件]； 有where：修改筛选后的数据 没有where：修改所有数据 删除： delete from 表名 where 条件； 删除所有： delete from 表名； truncate table 表名； `【面试题1】 简述delete删除和truncate删除的区别 1. delete删除可以加where条件，truncate不可以加where条件 2. delete支持事务，truncate不支持事务 3. delete逐行删除，truncate删除整个表，再创建一个新表【truncate删除效率高】 4. delete删除后，自增长列从断点开始， truncate删除后，自增列长从1开始`DQL 数据查询语言
1、基础查询 select 查询列表 from 表名； select * from 表名； select 查询列表 from 表名 where 条件； select 查询列表 from 表名 where 条件 order by 排序列表；[asc升序（默认） desc降序] select `distinct` 查询列表 from 表名； #去重 2、分页查询 select * from 表名 where 条件 limit offset(偏移量)，size(查询个数)； 公式：offset=(page - 1)*size 3、连接查询 内连接 隐式：select 查询列表 from 表1 别名1，表2 别名2... where 连接条件 and 分组前筛选 group by 分组字段 having 分组后筛选 order by 排序字段 limit 偏移量，个数； 显式：select 查询列表 from 表1 别名1 [inner] join 表2 别名2... on 连接条件； 外连接【查询主表所有数据】 select 查询列表 from 表1 别名1 left|right [outer] join 表2 别名2 on 连接条件 where 分组前筛选 group by 分组字段 having 分组后筛选 order by 排序字段 limit 偏移量，个数； `查询结果 = 内连接 + 主表有，单从表没有` left：左边为主表 right：右边为主表 4、子查询（查询效率较低） select(子查询) from(子查询) where(子查询)； 特点： 1、优先执行 2、必须在小括号（）内 3、单列：结果只有1个，搭配 = > < >= <= <> 多列：结果有多个，搭配 in、not in、any、all `【面试2】:select语句书写顺序： select 查询列表 from 表名 where 条件 group by 分组字段 having 条件 order by 排序字段 limit 偏移量，个数；`
DCL 数据控制语言
查看当前数据库所有用户： select * from mysql.user; 创建新用户，默认无权限： create user '用户名' @'%|localhost' identified by '密码'; %：支持远程登录 localhost：本地登录 为用户分配权限： grant 权限[select insert update delete] on 数据库.表名 to '用户名' @'%|localhost' 取消用户权限： revoke 权限 on 数据库.表名 from '用户名' @'%|localhost'； 删除用户： drop user '用户名' @'%|localhost'； 查看用户权限： show grants for '用户名' @'%|localhost'；
三、数据类型和变量
数据类型：
① 整型：tinyint/smallint/int/bigint 等同于 java 中 byte short int long ② 浮点型：float double ③ 字符型 varchar(n) 变长字符串 n必选 保存字符最大个数 性能低 char(n) 定长字符串 n可选 默认为1 保存字符最大个数 性能高 text 长文本 char 最多存放255个字符 varchar最多可以存放65532个字符 ④ 日期 date 日期 年月日 time 时分秒 datetime 日期 年月日时分秒 8 个字节 1900 9999 2020-10-15 11:43:12 timestamp 时间戳 4个字节 1970开始 2038年
【面试题3】：datetime和timestamp的区别？datetime和timestamp都可以表示YYYY-MM-DD HH:MM:SS的数据，区别： 1、大小不同 datetime占8个字节 timestamp占4个字节 2、范围不同 datatime：1000-01-01 到 9999-12-31 timestamp：1970-01-01 到 2038-01-19 3、时区 datetime存储和时区无关 timestamp存储和时区有关变量:
全局变量： 声明、修改：set @变量名 = 值； 查看：select @变量名； 局部变量： 声明：Declare 变量名 类型 [default 值]； 修改：set 变量名 = 值； 查看：select 变量名；
四、mysql的常见约束
自增标识【不是约束】：auto_increment 常见约束： 非空约束：not null 不能为null 唯一约束：unique 不能重复，支持多个null 主键约束：primary key 不能重复，且不能为null。【本质是索引，提高查询效率】 默认约束：default 指定默认值 外键约束：foreign key 限定两个表的关系【`同一数据库，外键名唯一`】 约束操作： 建表时操作： 外键约束：constraint 索引名 foreign key(外键列) references 主键表(主键列) 非外键约束：类型后加约束即可 建表后操作： 主键、唯一： Alter table 表名 add|drop 约束名(字段名); 非空、默认、自增： 外键：
五、常见函数
1、单行函数
length()：获取长度 lower()：转小写 upper()：转大写 concat(str1,str2,...)：凭借字符串 replace()：替换 trim()：去掉左右两侧的空格 lpad(str,len,padstr)：左填充 rpad(str,len,padstr)：右填充 str：原字符 len：长度 padstr：填充的字符 substring(str,index,len)：截取字符串 index：起始索引 len：指定长度2、日期函数
now()：当前时间 year(now())：当前年份 month(now())：当前月份 day(now())：当前天 Datediff(expr1,expr2)：两个日期之间的天数差 data_add(expr1，值，单位)：查询未来时间 data_sub(expr1，值，单位)：查询过去时间3、多行函数【分组函数、聚合函数】
sum(字段):求和 avg(字段):平均值 max(字段):最大值 min(字段):最小值 count(字段):非空个数 `【面试4】where 和 having 的区别？ where：行过滤，在分组order by 前面，不能和分组函数联用 having：组过滤，在分组order by 后面，可以和分组函数联用`4、数学函数
ceil() 向上取整 floor() 向下取整 round() 四舍五入 abs() 取绝对值 rand() 生成随机数 [0,1)
六、流程控制
1、if结构 第一种： if(布尔表达式，分支1，分支2)；【类似于Java中的三目运算符】 若布尔表达式为true，返回分支1 若布尔表达式为false，返回分支2 第二种： Begin if 条件1 then 语句1； elseif 条件2 then 语句2； ... [else 语句n] End if End 2、case结构： case when 条件1 then 值或表达式1 when 条件2 then 值或表达式2 ... else 值或表达式 end 3、while循环结构： Begin while 循环条件 do 循环体 End while End
七、TCL 事务控制语言
事务： 完成某个功能的整个最小执行单元，要么都发生，要么都不发生 分类： 隐式事务：没有明显的开启和结束标记，如insert、delete、update等 显式事务：有明显的开启和结束标记 事务的ACID特性： 原子性：不可分割，要么都发生，要么都不发生 一致性：整个事务过程中，数据保持一致 隔离性：事务与事务之间，互不干扰 持久性：数据持久化保存 创建事务的步骤： 1、禁用自动提交 set auto_commit = 0; 2、开启事务 start transaction; 3、编写事务流程 sql 语句； 4、结束事务（提交|回滚）commit|rollback； 事务的隔离级别： read uncommitted 读未提交 脏读、不可重复读、幻读 read committed 读已提交 不可重复读、幻读 repeatable read[默认]可重复度 幻读 Serializable 串行化 查看隔离级别：select @@tx_isolation； 设置隔离级别：set session(当前窗口)|global(所有) transaction isolation level 隔离级别；
八、视图View
1、概念： 虚拟表，和普通表一样使用。简化查询过程，提高数据安全性。 只保存SQL逻辑（select语句），不保存查询结果（数据）。 2、作用 ① 防止未经许可的用户访问敏感数据，确保数据安全性 ② 封装SQL语句，简化查询过程 ③ 视图可对用户屏蔽真实表结构 3、语法 创建视图： create view 视图名 as select语句； 查询视图： select * from 视图名； 查看视图结构： desc 视图名； 修改视图： Alter view 视图名 as SQL语句； 删除视图： Drop view 视图名； 4、视图和表的比较 创建 是否占用物理空间 使用 视图 create view 只保存SQL逻辑 一般用于查询，不用于增删改 表 create table 保存了真实数据 增删改查
九、存储过程
概念： 类似于Java中的方法，存储预编译的SQL语句集，便于外部程序调用。 优点： 1、提高代码复用性 2、安全性高，可以屏蔽基础表，只对外提供存储过程相关权限 3、存储过程只在创建时编译一次，而sql语句每次使用都要编译 4、访问多张表时，sql语句需要多次连接数据库，而存储过程只需要连接一次 缺点： 移植性差、调试不方便 语法： 1、创建： create procedure 存储过程名(参数类型[in|out|inout] 参数名 数据类型，...) Begin 存储过程体；（SQL语句集） End 【说明】 in：输入参数，调用者向存储过程存入值 out：输出参数，存储过程向调用者返回值 inout：输入输出参数，既传入，也能返回 BEGIN ... END: 存储过程开始和结束符号 2、调用：Call 存储过程名(实参列表)； 3、删除：Drop procedure 存储过程名；
十、触发器
概念： 监视某种情况，并触发某种操作，是与表事件相关的特殊存储过程。 不是程序调用，也不是手工启动，而是由事件触发（insert、delete、update） 语法： 1、创建： create trigger 触发器名 触发时机[before|after] 触发事件[insert|delete|update] on 表名 for each row Begin 执行语句列表； End 2、查看： show triggers； 3、删除： Drop trigger 触发器名； 关于new和old： 在insert型触发器中，new表示Before或After的新数据 在delete型触发器中，old表示Before或After的原数据 在update型触发器中，old表示原数据，new表示新数据
十一、索引【优化数据库查询的机制】
分类： 普通索引：仅加速查询 唯一索引：加速查询 + 列值唯一（可以有null） 主键索引：加速查询 + 列值唯一（不可以有null） 组合索引：多列组成一个索引 语法： 1、创建 普通索引：create index 索引名 on 表名(字段名)； 唯一索引：create unique index 索引名 on 表名(字段名)； 组合索引：create index 索引名 on 表名(字段名1，字段名2，...)； 2、查看： show index from 表名； 3、删除： Drop index 索引名 on 表名； 注意事项： 1、不在索引上做任何操作(计算、函数、类型转换等)，否则会导致全表查询，索引失效 2、使用不等于(!=或<>)时，索引失效，全表查询 3、is not null无法使用索引，但is null可以使用 4、like以通配符开头，索引会失效.('%abc...')
列值唯一（可以有null）
主键索引：加速查询 + 列值唯一（不可以有null）
组合索引：多列组成一个索引语法：
1、创建
普通索引：create index 索引名 on 表名(字段名)；
唯一索引：create unique index 索引名 on 表名(字段名)；
组合索引：create index 索引名 on 表名(字段名1，字段名2，…)；
2、查看： show index from 表名；
3、删除： Drop index 索引名 on 表名；注意事项：
1、不在索引上做任何操作(计算、函数、类型转换等)，否则会导致全表查询，索引失效
2、使用不等于(!=或<>)时，索引失效，全表查询
3、is not null无法使用索引，但is null可以使用
4、like以通配符开头，索引会失效.(’%abc…’)
