mysql笔记（备忘）

事务

事务必须具有四个属性 ACID

原子性(Atomicity)
一致性(Consistency)
隔离性(Isolation)
持久性(Durability)

实现方法

SET 自动提交

SET AUTOCOMMIT = 0;#关闭
SET AUTOCOMMIT = 1;#开启

set autocommit = 0 ;# 关闭自动提交
start transaction ;# 启动事务

commit ;#确认提交，无法回滚
rollback ;#回滚

数据库索引

作用

提高查询速度
确保数据的唯一性
可以加速表和表之间的连接，实现表与表之间的参照完整性
使用分组和排序子句进行数据检索时，可以显著减少分组和排序的时间
全文检索字段进行搜索优化

分类

主键索引(PRIMARY KEY)
唯一索引(UNIQUE)
常规索引(INDEX)
全文索引(FULLTEXT)

添加主键索引

alter table result add primary key (studentno,subjectno,examdate);

唯一性索引

在增加的字符属性后加上unique 或者
unique key ‘需要唯一索引的字符名称’ （需要唯一索引的字符名称）

create table gender(genderid int(10) primary key auto_increment,
gendername varchar(32) not null unique );

alter table result add index int

数据库备份

备份myschool数据库

[root@localhost ~]# mysqldump -uroot -proot myschool>/opt/soft/myschool.sql

备份myschool数据库里的teacher表

[root@localhost soft]# mysqldump -uroot -proot myschool teacher>/opt/soft/teacher.sql

恢复备份

1.在mysql外执行

先创建新的数据库（恢复的数据库放在哪里）

create database testdemo;

在mysql外导入

[root@localhost soft]# mysql -uroot -proot testdemo</opt/soft/myschool.sql

2.在mysql里执行

create database testdemo;
source /opt/soft/myschool.sql

3.sqlyog可以直接导入导出

group_concat()

select studentno,group_concat(subjectno),group_concat(studentresult)
from result group by studentno;

select studentno,group_concat(distinct subjectno order by studentno desc),group_concat(studentresult)
from result group by studentno;

select studentno,group_concat(distinct subjectno order by studentno desc separator '#'),group_concat(studentresult)
from result group by studentno;

时间函数

select date_format(now(),'%Y-%m-%d %H:%i:%s');
#                         年 月 日  时 分 秒 

H 24小时制，h12小时制

窗口函数

查找每科学生夫人成绩，成绩前三的学生信息

select * from (select sc.*,row_number() over (partition by sc.c_id order by sc.s_score desc ) rn
 from score sc ) t having t.rn <= 3

row_number 同分不并列

select t.* ,row_number() over (partition by id order by sale desc) rn from test t ;

rank 同分并列，但后面的排名会跳过

select t.* ,rank() over (partition by id order by sale desc) rn from test t ;

dense_rank 只按成绩排，不看人数

select t.* , dense_rank() over (partition by id order by sale desc) rn from test t ;

select t.* ,lead(sale) over( partition by id order by sale) from test t;

窗口下一个（往后）

select t.* ,lead(sale,1,0) over( partition by id order by sale) from test t;

窗口上一个（往前）

select t.* ,lead(sale,1,0) over( partition by id order by sale) next,
            lag(sale,1,0) over (partition by id order by sale) pre
from test t;

first_value last_value