MySQL 高级应用

最新推荐文章于 2024-01-18 22:51:26 发布

xuxiaopang0417

最新推荐文章于 2024-01-18 22:51:26 发布

阅读量738

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/xuxiaopang0417/article/details/80022823

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

三大范式

- 表复制  （结构和数据）
- Mysql 索引  
- MySQL视图  
- MySQL内置函数  
- Mysql 预处理 
- MySQL事务  
- Mysql 触发器 
- MySQL 存储过程   
- MySQL分区

数据库三大范式

1NF 属性的原子性
ID 学号姓名地址出生年月日这个还可以将地址和出生年月日进行拆分不符合第一范式
ID 学号姓名省份市区县区年月日字段不能再拆分这就是符合第一范式
2NF 首先遵循第一范式记录要有唯一的标识实体唯一性不存在部分依赖

学号课程号姓名学分这里边学号依赖于姓名学分依赖于课程号
问题：
1.每行会存在相同信息
2.删除成绩容易把课程信息干掉
3.如果学生没选课程数据库中就不存在该学生的姓名
4.调整学分所有的行都得更新
正确的做法 Student （学号姓名）
Course(课程号学分 )
选课表（学号课程号成绩）
3NF 首先遵循第二范式
任何字段不能由其它字段派生出来也就是说不存在传递依赖

学号姓名年龄学院学院电话

学号 ->学生姓名 ->所在学院 ->学院电话

正确的做法是

学生 （学号 姓名   年龄 所在学院）

学院  （学院名称  学院电话）

可以适当进行反范式设计
- 为了提高查询更新效率可以适当增加冗余字段以空间换时间

范式和非范式的区别 :

范式
- 减少数据的冗余
- 更新快表体积小
- 范式比反范式更新起来快
  缺点:
  查询表关联
  索引优化难度大
反范式
- 减少表关联
- 索引优化比范式优化方便
缺点
- 数据冗余
- 更新数据成本大

如果表读的多适当反范式设计以空间换时间如果更新的多那么遵循第三范式

表复制

create table t1(id int(11) primary key auto_increment not null,name varchar(32))engine=MyISAM default charset=utf8;


create table t2 like t1;
insert into  t2 select * from t1;

Mysql 索引

普通索引最基本的索引没有任何限制
唯一索引索引的列必须唯一没有重复值但是允许有空值
主键索引特殊的唯一索引不能有空值
全文索引 MyISAM 引擎生成索引非常耗时间
组合索引提高查询效率多个字段组合遵循最左原则

索引类型

B-Tree 索引最常见的索引类型大部分引擎都支持的索引类型
hash索引 memory 引擎才支持
R-tree 索引 MyISAM 引擎特有的索引类型主要运用于地理空间数据
Full-text MyISAM 引擎特有的索引类型用于全文索引 MySQL5.6 innodb引擎也支持了全文索引

?alter
?create
创建索引的两种方式 create 不支持创建主键索引

create index in_name on t1(name);
show index from t1\G; 查看所有的索引 \G以更好地阅读体验阅读
drop index in_name on t1;删除索引
create unique index un_name on t1(name); #保证字段列不能有重复值
drop index un_name on t1;

alter table t1 add index in_name(name); #alter方式创建索引
show index from t1\G;
alter table t1 drop index in_name;#删除索引
alter table t1 drop primary key; #删除主键索引必须先消除自增
alter table t1 modify id int(11) unsigned not null; #消除自增

alter table t1 add primary key(id);#创建主键索引
alter table t1 modify id int(11) unsigned not null auto_increment;

alter table t1 add unique(name);
alter table t1 drop index name; #没有索引名称的唯一索引

alter table t1 add unique un_name(name);#唯一索引有名字
alter table t1 drop index un_name;

alter table t3 add index name_age(name,age); #组合索引最左原则创建完组合索引以后 where条件中必须有 name 或者name age 条件都有否则这个索引失效

Mysql 视图

? view   

create view v_t1 as select * from t1 where id>3 and id <8; #把经常查询的数据放到临时表中   下次直接从临时表 读取即可 不用每次都得 数据库参与运算  

show tables; #会看到一个v_t1 临时表   

select * from v_t1;

#主表存在  视图存在   主表丢失 视图丢失  主表恢复  视图恢复  
drop view v_t1;  #删除视图   

#查看创建视图的过程 
show create view v_t1\G;

Mysql 内置函数

字符串函数
日期函数
数学函数

字符串函数

mysql 中   select 跟 linux中的 echo 效果一样  都是打印信息   

select concat("php学习","到底哪家强"); #字符串连接  
+---------------------------------------+
| concat("php学习","到底哪家强")        |
+---------------------------------------+
| php学习到底哪家强                     |
+---------------------------------------+

select lcase("PHP IS SO GOOD"); #转成小写
+-------------------------+ 
| lcase("PHP IS SO GOOD") |
+-------------------------+
| php is so good          |
+-------------------------+    

select ucase("two birds are singing on the tree"); #转成大写 
+--------------------------------------------+
| ucase("two birds are singing on the tree") |
+--------------------------------------------+
| TWO BIRDS ARE SINGING ON THE TREE          |
+--------------------------------------------+

select length("50厘米开外不用手脚碰到我咱们就在一起"); #计算字符串的长度
+-----------------------------------------------------------------+
| length("50厘米开外不用手脚碰到我咱们就在一起")                  |
+-----------------------------------------------------------------+
|                                                              53 |
+-----------------------------------------------------------------+

mysql> select  ltrim("   tow big"); #去除左边空格
+---------------------+
| ltrim("   tow big") |
+---------------------+
| tow big             |
+---------------------+
1 row in set (0.00 sec)

mysql> select  rtrim("   tow big");  #去除右边空格  
+---------------------+
| rtrim("   tow big") |
+---------------------+
|    tow big          |
+---------------------+
1 row in set (0.00 sec)



 select replace("python是世界上最好的语言","python","PHP");
+-------------------------------------------------------------+
| replace("python是世界上最好的语言","python","PHP")          |
+-------------------------------------------------------------+
| PHP是世界上最好的语言                                       |
+-------------------------------------------------------------+
1 row in set (0.00 sec)


select repeat("php是世界上最好的语言",3); 将内容重复3遍 
select concat(space(20),"世界上最遥远的距离就是我在你身边你在玩手机"); #将20个空格跟字符串拼接  

select substr("php is so good",1,5); #从字符串第一个位置截取到第五个位置

日期函数

select curdate(); #返回当前日期 
select curtime();#返回当前时间  
select now();#返回当前日期和时间  
select unix_timestamp(now()); #返回当前日期的时间戳  
select from_datetime(1524175009);#根据时间戳返回当前的日期  
insert into t1(name) values(unix_timestamp()); #直接将 当前时间戳插入数据库 
select week(now());#当前时间是第几周  
select year(now());#当前时间是哪一年  
select datediff("2018-10-20","2015-12-10"); #计算差别的天数 第一次 起始时间  其二个 结束时间

数学函数

select bin(200); #10进制转成2进制  
select ceiling(10.1);11
select floor(10.1);10
mysql> SELECT student_name, MIN(test_score), MAX(test_score)
    ->        FROM student
    ->        GROUP BY student_name;
取最大值 和最小值 
random() 大于等于0 小于1  js 生成随机数  
mt_rand() 
rand() 0-1之间的随机值  不包括1 和 0

MySQL预处理

prepare test from "select * from t1 where id>?";  ?表示先处理了一下  这个值 可以发生改变  
set @i=3 相当于设置一个一个变量 必须以@开头  并赋值   
execute test using @i; 好比 select * from t1 where id>3;
drop prepare test; #删除预处理    
不用每次都去请求MySQL

MySQL 事务

开启事务  首先要关闭自动提交   
set autocommit=0; 关闭自动提交   

事务只对innodb 引擎适用  
show create table t1;

alter table t1 engine=innodb;#更改引擎  
delete from    原来12条数据  delete以后 再插入数据 从 13开始  
truncate 删除   原来12条数据  delete以后 再插入数据 从 1开始 

rollback  回滚   


mysql> insert into t1(name) values("user14"),("user24"),("user34"),("user44");
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> savepoint p3;  #保持 插入后的一个状态  跟Linux的快照比较类似  
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t1;
Query OK, 24 rows affected (0.00 sec)

mysql> rollback to p2;
Query OK, 0 rows affected (0.00 sec)
p1  p2  p3  

如果回到了 p2  那么可以再回到  p1     但是 回不到 P3了

MySQL存储就类似于一个函数存储了代码段

? procedure  //程序执行的区间   

\d //  #更改结束符  

mysql> create procedure p1()
    -> begin 
    -> set @i=1;
    -> while @i<=1000 do
    -> insert into t1(name) values(concat("user",@i));
    -> set @i=@i+1;
    -> end while;
    -> end //

   ps:看清楚分号 不是  想写就写得  

   show procedure status\G //  #查看当前有哪些存储过程  就好比 有哪些函数   
   show create procedure p1\G;  #查看详细创建  存储的过程    相当于查看每个函数都是干嘛的   

 \d ;   //改回原来的结束符    

 call p1;调用存储过程    他就自动的插入 1000条数据到数据库中

MySQL触发器

两个数据表之间的同步

? trigger   

#同步插入操作  
\d //  #更改结束符  

mysql> create trigger add1 before insert on t1 for each row 
    -> begin
    -> insert into t2(name) values(new.name); #不用  id 等主键作为条件 因为 不一致 
    -> end //

\d ;
#同步更新操作 
\d //
     create trigger update1 before update on t1 for each row 
     begin
     update t2 set name=new.name where name=old.name;
     end //
\d ;

#同步删除操作  
\d //
     create trigger del1 before delete on t1 for each row 
     begin
     delete from t2 where name=old.name;
     end //
\d ;

自增 id 如何恢复

1.清空表的时候 不用 delete from 用 truncate  
2.delete 之后 使用 alter table t1 auto_increment=1

MySQL分区

mysql 数据以文件的形式存放到服务器上 /var/mysql/data

frm 结构文件
myd 数据文件
myi 索引文件

create table t6(id int)engine=MyISAM partition by hash(id) partitions 6;
create index in_id on t6(id);

insert into t6 select * from t6;
insert into t6 select * from t6;
insert into t6 select * from t6;
insert into t6 select * from t6;
insert into t6 select * from t6;

watch -n1 ls -lh 每秒查看一次

\d //
create procedure p2()
begin
set @i=1;
while @i<=10000000 do
insert into t6 values(@i);
set @i=@i+1;
end while;
end //
\d ;
show procedure status\G;
show create procedure p2 \G;
call p2;

xuxiaopang0417

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL 高级应用

三大范式 - 表复制（结构和数据）- Mysql 索引 - MySQL视图 - MySQL内置函数 - Mysql 预处理 - MySQL事务 - Mysql 触发器 - MySQL 存储过程 - MySQL分区数据库三大范式1NF 属性的原子性 ID 学号姓名地址出生年月日这个还可以将地址和 ...
复制链接

扫一扫