Mysql基础复习
目录
MYISAM和INNODB的区别
| MYISAM | INNODB | |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间的大小 | 较小 | 较大，约为MYISAM的两倍 |
常规使用操作：
- MYISAM 节约空间，速度较快
- INNODB 安全性高，事务的处理，多表多用户操作
在物理空间存在的位置
所有的数据问文件都存在data目录下
本质还是文件的存储
MySQL引擎在物理文件上的区别
- InnoDB 在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
- MYISAM 对应文件
- *.frm -表结构定义文件
- *.MYD 数据文件（data）
- *.MYI 索引文件（index）
CHARSET = utf8
操作数据库
SHOW CREATE DATABASE school --查看创建数据库的语句
SHOW CREATE TABLE student --查看student数据表的定义语句
DESC student --显示表的结构
--修改表
ALTER TABLE teacher RENAME AS teacher1
--增加字段
ALTER TABLE teacher1 ADD age INT(11)
--修改表的字段（重命名，约束）
ALTER TABLE teacher1 MODIFY age VARCHAR(11) --修改约属
ALTER TABLE teacher1 CHANGE age age1 INT(1) --重命名
--删除表的字段
ALTER TABLE teacher1 DROP age1
--删除表
DROP TABLE IF EXISTS teacher1
--删除操作加上判断是否存在
注意点：
- ``字段名，使用这个包裹
- 注释 -- /**/
- sql关键字大小写不敏感，建议小写
MySQL数据管理
外键
KEY `FK_gradeid` (`gradeid`) --定义外键key
--给外键添加约束（执行引用），reference引用
CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCE `grade`(`gradeid`)
--第二种方式
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCE `grade`(`gradeid`)
删除有外键关系的表的时候，必须先删除引用别人的表（从表），再删除被引用的表（主表）
以上的操作都是物理外键，数据库级别的外键，我们不建议使用
最佳实践：
- 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
- 我们想使用多张表的数据，想使用外键（程序去实现）
DML语言
数据库意义：数据存储，数据管理
DML语言：数据操作语言
-
insert
-
-
插入语句
-
inser into 表名（字段1，字段2...） values ('值1'，'值2'...) inser into 表名 values ('值1'，'值2'...) --不写表的字段会一一匹配 inser into 表名 values ('行1'),('行2') --插入多个值，逗号隔开
-
-
update
-
-
更新语句
-
update 表名 set column_name=value where 条件 --不指定条件的情况下会改动所有表
-
操作符
| 操作符 | 含义 | 范围 | 结果 |
|---|---|---|---|
| = | 等于 | 5=6 | false |
| <>或！ | 不等于 | 5<>6 | true |
| > | |||
| >= | |||
| < | |||
| <= | |||
| BETWEEN... AND... | 闭合区间，在某个范围内 | ||
| AND | |||
| OR |
--通过多个条件定位数据
-
delete
-
-
删除数据
-
delete from 表名--删除数据（避免这样写，会全部删除） delete from 表名 where 条件 --指定删除--可以有条件删除
-
-
truncate
-
- 完全清空一个数据库表，表的结构和索引约束不会变！--只能全部删除
delete ,truncate 区别
-
- 相同点：都能删除数据,都不会删除表结构
- 不同
-
- truncate 重新设置，自增列，计数器会归零
- truncate 不会影响事务
delete 删除问题，重启数据库，现象：
- InnoDB 自增列会从1开始（存在内存中的，断电即失）
- MYISAM 继续熊上一个自增量开始（存在文件中的，不会丢失）
DQL查询数据
（Dada Query Language:数据查询语言）
-
所有的查询操作都用它 select
-
简单的查询，复杂的查询都能做
-
数据库中最核心的语言，最重要的语句
-
使用频率最高的语言
--查询全部信息
select * from 表名
--查询指定字段 as起别名，给结果，表，字段起别名
select 字段1 as 别名，字段2 from 表名
-- 函数 Concat（a,b）拼接字符串
select concat('（添加的comment）如姓名：',字段名) as 新字段名 from 表名
select concat（'姓名：'，studentname） as 新名字 from student
有的时候，列名字不是那么见名知意，可以起别名
***去重***
select distinct 字段名 from 表名 --重复数据只显示一条
***数据库的列***
--查询系统版本（函数）
select version()
select 100*3-1 --计算结果（表达式）
select @@auto_increment_increment --查询自增的步长（变量)
--字段+1查看
select 字段1，字段2+1 as 新字段名 from 表名
数据库中的表达式：文本值，列，null，函数，计算表达式，系统变量
select表达式
where条件子句
作用：检索数据中复合条件的值
搜索的条件由一个或多个组成
逻辑运算符尽量使用英文字母
| 运算符 | 语法 | 描述 |
|---|---|---|
| and && | a and b a&&b | 逻辑与 |
| or || | a or b a|b | 逻辑或 |
| Not ! | not a | 逻辑非 |
-- 查询在。。。之间
..>.. and ..<..
between ... and ...--模糊查询
!= --排除
IN
模糊查询：比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| IS NULL | a is null | |
| IS NOT NULL | a is not null | |
| BETWEEN | a between b and c | |
| LIKE | a like b | sql匹配 |
| IN | a in (a1,a2,a3) |
--like查询 %（代表0到任意个字符） -（一个字符）
select student name from student where studentname like '刘%'
select student name from student where studentname like '刘_'
select student name from student where studentname like '刘__'
select student name from student where studentname like '%嘉%'
--in查询 具体的值或多个值
select * from student where address in ('北京'，'安徽')
联表查询
join 连接
--联表查询
select * from student
select * from result
select s.No,student from student s innerjoin result r on s.No=r.No
| 操作 | 描述 |
|---|---|
| inner join | 如果表中至少由一个匹配，就返回行 |
| left join | 会从左表中返回所有的值，即使右表中没有匹配 |
| right join | 会从右表中返回所有的值，即使左表中没有匹配 |
join on连接查询
**where **等值查询
自连接
自己的表和自己的表连接：核心：一张表拆为两个一样的表
分页和排序
order by
--排序，升序ASC,降序 DESC
--ORDER BY 通过哪个字段排序
- 为什么要分页
- 缓解数据库压力，给人体验更好，瀑布流
语法：limit 起始值，页面大小
limit 0,5 --前五条数据
网页应用：当前页，总的页数，页面的大小
- 第一页 limit 0，5
- 第二页 limit 5，5
- 第n页 limit（n-1）*页面大小，页面大小
- n:当前页，数据总页/页面大小 =页数
子查询
where（这个值是计算出来的）
本质：在where语句中嵌套一个子查询
分组和过滤
GOUP BY HAVING
Mysql函数
常用函数
select abs(-8)
select ceiling(9.4) --向上取整
select floor(9.4) --向下取整
select rand() --随机数
select sign(0) --判断一个数的符号 0-0 负数返回-1 正数返回1
--字符串长度
select char_length('即使在小的帆也能远航') --字符串长度
select concat('gt','r') --拼接字符串
select insert('s',startindex,endindex,'new') --查询，替换从某个位置开始替换某个长度
select lower('keuhfjh') --小写字母
select upper() --大写字母
select instr('jhdhgfdj','h') --返回第一次出现的子串的索引
select replace('坚持就能成功','坚持','努力') --替换出现的指定字符串
--时间和日期函数（记住）
select current_date() --获取当前日期
select curdate()
select now() --获取当前的时间
select localtime（） --本地时间
select sysdate()
select year(now())
--系统
select system__user()
select user()
select version()
聚合函数（常用）
| 函数名称 | 描述 |
|---|---|
| count() | 计数 |
| sum() | 求和 |
| avg() | 平均值 |
| max() | 最大值 |
| min() | 最小值 |
| ... | ... |
--==聚合函数==
--Count(字段)，会忽略所有的null值
--Count(*)，不会忽略null值
--Count(1)，不会忽略null值
数据库级别的MD5加密
什么是MD5
主要增强算法复杂度和不可逆性
MD5不可逆，具体的值的md5是一样的
MD5破解网站的原理，背后有一个字典，MD5加密后的值，加密前的值
--加密
update 表名 set 字段1 = md5(字段1)
事务
事务原则：ACID原则，原子性，一致性，隔离性，持久性
事务的隔离级别
脏读：一个事务读取了另外一个事务未提交的数据
不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同
幻读：在一个事务内读取到了别的事务插入的数据，导致前后读取不一样
原子性
逻辑上一组操作要么都成功，要么都失败
一致性
事务前后的数据完整性要保持一致
持久性
事务一旦提交则不可逆，被持久化到数据库中
隔离性
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，事务之间要相互隔离
索引
Mysql 官方对索引的定义为：索引(index)是帮助Mysql高效获取数据的数据结构，提取句子主干，就可以得到索引的本质，索引是数据结构
索引的分类
- 主键索引（primary key）
- 唯一标识，不可重复
- 唯一索引（unique key）
- 避免重复的列出现，可以重复，多个列都可以标识为唯一索引
- 常规索引（key/index）
- 默认的，index，key
- 全文索引（fulltext）
- 在特定的数据库引擎下才有，MYISAM
基础语法
--索引的使用
--1.在创建表的时候给字段增加索引
--2.创建完毕后，增加索引
--显示所有的索引信息
show index from student
--增加一个全文索引（索引名） 列名
alter table school.student add fulltext index 'studentname' ('stu');
--explain 分析sql执行情况
explain select * from student; ---非全文索引
explain select * from student where match(studentname) against('刘')
