mysql的基础查阅

浅浅的花香味﹌ 2022-12-20 11:25 159阅读 0赞

## 命令行连接 ##

连接数据库：mysql -uroot -p密码；  
修改用户密码：update mysql.user set authentication\_string=password(‘密码’) where user=‘root’ and Host = ‘localhost’；  
刷新权限：flush privileges；  
查看所有的数据库：show databases;  
使用数据库：use 数据库名  
查看数据库中所有的表：show tables;  
查看表中的信息：describe 表名;  
创建数据库：create database 数据库名；  
退出连接：exit;

## 数据库语言 ##

DDL 定义  
**DML 操作**  
**DQL 查询**  
DCL 控制

外键  
创建表成功后，有外键约束

ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY('作为外键的列') REFERENCES '另一张表名'（‘某个字段’）
    
    举例：ALTER TABLE 'student' ADD CONSTRAINT 'FK_gradeid'  FOREIGN　KEY('gradeid') REFERENCES 'grade' （‘gradeid’）;

外键很少使用：  
删除外键关系的表的时候，必须要先删除引用别人的表（从表）(student)，再删除被引用的表（主表）(grade)  
**DML 操作**  
**insert**

insert into 表名（[字段名1，字段名2.。。]）values ('值1'，'值2'，。。。);

由于主键自增 我们可以忽略；  
一个字段对应一个值  
插入多个字段：  
如：insert into ‘grade’ (‘gradename’,‘age’) values(‘大一’,23),(‘大二’,21);  
**update**  
updat 表名 set ‘某个字段名’=值 where 天剑  
如果不指定条件的情况下，会改动所有表  
如：update 表名 set ‘某个字段名’ = 值；  
修改多个属性：

如：`update 表名 set colnum_name = value,colnum_name1 = value1,.....where 条件`

条件：where子句 ，操作符会返回布尔值  
操作符==，>=，between … and…，AND,OR等等  
**delete**  
delete from 表名 where 条件  
**清空表用truncate命令**  
truncate 表名，保留表的结构和索引约束不会变和delete 唯一不同就是truncate可以重新设置自增列，计数器会归0；  
**delete删除的问题**（了解）  
重启数据库，现象  
InnoDB 自增列会重1开始（存在内存当中，断电即失）  
MyISAM 继续从上一个自增量开始（存在文件中，不会丢失）  
**DQL 查询（重要）**  
所有的查询操作都用它 select  
简单查询和复杂查询  
指定查询字段：  
select \* from 表名  
select ‘字段名’,‘字段名’ from 表名

可以给字段起个别名，也可以给表起个别名
    select '字段名' as 别名，'字段名' as 别名。。。 from 表名 as 别名；

函数Concat(a,b)  
select Concat(‘姓名：’,字段名) as 别名 from 表名

SELECT CONCAT("新姓名:",name) as '自己设置新名字' FROM s_student

**去重**  
作用：去除查询出来的结果中重复的数据，只显示一条  
selcect distinct ‘字段名’ from 表名  
**查询系统版本**  
SELECT VERSION()  
**用来计算（表达式）**  
SELECT 100\*9 AS 计算结果  
**查询自增的步长（变量）**  
SELECT @@auto\_increment\_increment  
**模糊查询**

SELECT \* FROM account where mobile LIKE ‘136%’  
SELECT \* FROM account where realName LIKE ‘张\_\_’  
SELECT \* from account where realName LIKE ‘%五%’

-- 查询id 为 2 3 5人物的信息
    SELECT * FROM account WHERE id in (2,3,5);

in (具体的一个或多个值)

-- 查询南京 北京人物的信息
    SELECT * from account where address in ('南京','上海 浦东');

-- 查询remark不为空的学生信息
    SELECT s_score.score,s_score.course_id FROM s_score where s_score.remark !='' AND s_score.remark IS Not NULL;

-- 查询remark为空的学生信息
    SELECT s_score.score,s_score.course_id FROM s_score where s_score.remark ='' OR s_score.remark IS NULL;（null查不出来）

如果要单纯查NULL值列，则使用 is NULL去查，单纯去查空值(’’)列，则使用 =’’。

建议查询方式：NULL值查询使用is null/is not null查询，而空值(’’)可以使用=或者!=、<、>等算术运算符。\*\*  
**联表查询**  
分析：  
要查询的字表段来自哪些 (连接查询)  
确定用哪种连接查询（7种）  
**确定交叉点（两个表中哪个数据是相同的）**  
条件：某张表.stuId = 另张表.stuId  
inner join 如果表中至少有一个匹配，就返回性  
left join 会从左表中返回所有的值，即使右表中没有匹配  
right join 会从右表中返回所有的值，即使左表中没有匹配

-- 查询学生的爱好（学生姓名、爱好）
    -- SELECT t_student.`name`,t_hobby.hobbyname FROM t_hobby,t_student WHERE t_student.id=t_hobby.id
    SELECT t_student.`name`,t_hobby.hobbyname
    FROM t_student
    INNER JOIN t_hobby
    ON t_student.id = t_hobby.id

**自连接**  
把一张表看成是两张一模一样的表  
**select完整语法**  
**顺序不要搞乱**  
select \[选项 all | distinct\]  
字段表达式  
from 数据源  
left|right|inner join 数据源2（联合查询）  
where 字段表达式(指定结果满足的条件)  
group by 子句(指定结果按照哪个字段来分组)  
having 子句（过滤分组的记录必须满足的次要条件）  
order by 子句（指定查询记录按一个或多个条件排序）  
limit 子句(指定查询的记录从哪条到哪条)  
**排序**  
order by 排序字段 desc 降序  
order by 排序字符 asc 升序  
**分页**  
数据库分页是有公式的  
每页显示5条数据 pageSize = 5;  
第一页 limite 0,5 (1-1)\*5  
第二页 limite 5,5 (2-1)\*5  
第三页 limite 10,5 (3-1)\*5  
第n页 limite (n-1)\*pageSize,5  
pageSize:页面大小  
（n-1）\*pageSize:起始值  
n:当前页  
数据总记录数/页面大小 = 总页数  
**子查询**  
where语句中嵌套一个子查询语句  
**嵌套查询**  
**函数**

SELECT MOD(10,3);#1 返回10除以3的余数
    select SQRT(100);#10 返回10的平方根
    select ABS(-10);#10 返回-10的绝对值
    SELECT PI();#3.141583返回圆周率
    
    SELECT CEIL(1.5);#2 返回大于或等于1.5的最小整数
    SELECT CEIL(-1.56);#-1
    
    select CEILING(1.5);#2 返回大于或等于1.5的最小整数
    SELECT CEILING(-1.5);#-1
    SELECT FLOOR(1.5);#1 返回小于或等于1.5的最大整数
    SELECT FLOOR(-1.5);#-2
    
    SELECT RAND();#返回一个0,1之间的随机数
    SELECT RAND(100);#返回一个0~x之间的随机数
    
    SELECT ROUND(10.82);#11 四舍五入
    SELECT ROUND(10.32);#10
    SELECT ROUND(10.82,1);#10.8
    SELECT ROUND(10.325,2);#10.333 四舍五入 保留指定后面的小数位
    
    SELECT `TRUNCATE`(10.352,2);#10.333 四舍五入 保留指定后面的小数位
    SELECT sign(12),sign(234);#返回参数的符号
    
    select pow(12,3);#乘方
    SELECT POWER(2,3);#乘方
    SELECT EXP(2);#7.38905609893065 计算e的乘方
    
    SELECT log(2);
    SELECT LOG10(100);#以10为底的对数
    
    SELECT RADIANS(360);#将角度转换为弧度
    SELECT DEGREES(6.28);#将弧度转换为角度
    
    SELECT sin(1);#计算正弦值
    SELECT ASIN(3);#计算反正弦值
    SELECT COS(10);#计算余弦值
    SELECT ACOS(3);#计算反余弦值
    
    SELECT TAN(12);#计算正切值
    SELECT ATAN(12);#计算反正切值
    SELECT cot(12);#计算余切值

MySQL：字符串函数：

SELECT ASCII('A');#65 返回字符串'A'的ASCII码
    SELECT CHAR_LENGTH("laoshuaidami");#12 返回字符串s的字符数
    SELECT CHARACTER_LENGTH("laoshuaidami");#12 返回字符串s的字符数
    SELECT CONCAT("sql","java","php");#sqljavaphp 字符串的合并
    SELECT FIELD("d","d","d","3");#1
    SELECT FIELD("c","2",",","c","d");#3返回第一个字符串s在字符串列表中的位置
    
    SELECT FIND_IN_SET("c","i,love,c");#3 返回在字符串s2中与s1匹配的字符串的位置
    
    SELECT FORMAT(32324.432433,3);
    SELECT FORMAT(3224.4433,2);#将数字x进行格式化“#,###.##”，保留2位小数 四舍五入
    
    SELECT INSERT("laoshuaidami",3,4,"oooo");#laooooaidami 字符串s2替换s1的x位置开始长度为4的字符串
    SELECT LOCATE("ove","iloveyou");#3
    SELECT LOCATE("m","mynameis");#1 从字符串s中获取s1的开始位置
    
    SELECT LCASE("dsjMKMM");#dsjMKMM将字符串变成小写
    SELECT LOWER("dsjMKMM");#dsjMKMM将字符串变成小写
    
    SELECT LEFT("mynameistom",6);#myname返回字符串s前6个字符
    
    SELECT LPAD("myname",16,"isjave");#isjaveisjamyname
    SELECT LPAD("abc",5,"xx");#xxabc在abc的开始处填充字符串xx，使得达到5这个长度
    
    SELECT MID("dsjssdsdsdd",2,5);#sjssd字符串2的位置开始截取5位
    
    SELECT POSITION("my" IN "mynameisjava");
    SELECT POSITION("o" IN "love");#2o是在love的第2个位置
    SELECT REPEAT("love",2);#lovelove让字符串重复几次
    
    SELECT REPLACE("dserw","er","edw");#dsedww
    SELECT REVERSE("avajevolym");#mylovejava
    
    SELECT RIGHT("dsewe4",1);#4
    SELECT RIGHT("mused",3);#sed 返回字符串s的后3个字符
    
    SELECT RPAD("mmm545",9,"4322");#mmm545432在s1的结尾处添加字符串s2，使得达到9位长度
    
    SELECT SPACE("2");#返回2个空格
    
    SELECT STRCMP("myname","ddddd");#1
    SELECT STRCMP("myname","myname");#0
    SELECT STRCMP("ddd","myname");#-1 如果相等则0 s1>s2 1;s1<s2 -1
    
    SELECT SUBSTRING("munameisjava" ,4,4);#amei从4的位置截取4个
    SELECT SUBSTR("munameisjava" ,4,4);#amei从4的位置截取4个
    
    SELECT TRIM("   jave me ds   ");#去掉字符串s开始和结尾处的空格
    
    SELECT UCASE("dwsee");#将字符串转化成大写
    SELECT UPPER("dseee");#将字符串转化成大写

日期函数：

SELECT ADDDATE("2021-02-05",INTERVAL 10 DAY);#2021-02-15
    
    SELECT ADDTIME("2021-11-11 11:11:11",10);#2021-11-11 11:11:21
    
    SELECT CURDATE();#2021-02-05 Date:代表xxxx年xx月xx日
    SELECT CURRENT_DATE();#2021-02-05 返回当前日期
    
    SELECT CURTIME();#12:07:59返回当前的时间
    SELECT CURRENT_TIME();#12:06:24返回当前的时间
    SELECT CURRENT_TIMESTAMP();#2021-02-05 12:07:06返回当前的日期和时间
    
    
    SELECT date("2020-2-2");#2020-02-02从日期或日期时间表达式中提取日期值
    SELECT date("2020-2-2 11:23:23");#2020-02-02从日期或日期时间表达式中提取日期值
    
    
    select DATEDIFF("2000-08-12","2021-2-5");#-7482相隔多少天
    
    
    SELECT DAYOFYEAR("2021-2-1 ");#32计算日期是本年的第几天
    SELECT DAYOFWEEK("2021-2-5 ");#6是星期五注意：1是星期日，2是星期1
    
    SELECT DAYOFMONTH("2021-2-5 ");#5计算日期是本月的第几天
    
    SELECT day("2021-2-5 ");#5返回日期值的日期部分
    
    
    SELECT DATE_SUB((SELECT CURDATE()),INTERVAL 1 DAY);#2021-02-04函数从日期减去指定的时间间隔
    
    
    SELECT DATE_FORMAT("2021-2-5 11:11:11","%Y-%m-%d %r");#2021-02-05 11:11:11 AM按表达式f的要求显示日期
    
    
    SELECT ADDDATE("2021-2-5 11:11:11",INTERVAL 1 DAY);#2021-02-06 11:11:11在此基础啊上加上一天
    
    SELECT ADDDATE("2021-02-06 11:11:11",INTERVAL 5 MINUTE);#2021-02-06 11:16:11其实日期时间加上一个时间段后的日期
    
    SELECT EXTRACT(MINUTE FROM "2021-2-5 11:11:11");#从日期时间中获取指定的值
    
    SELECT FROM_DAYS(1);#0000-00-00
    SELECT FROM_DAYS(1111);#0003-01-16计算从0000年1月1日开始n天后的日期
    
    SELECT HOUR('12:12:12');#12返回时间中的小时值
    
    
    
    SELECT LAST_DAY("2021-2-5");#2021-02-28返回给给定日期的那一月份的最后一天
    
    
    SELECT LOCALTIME();#2021-02-05 12:44:58返回当前日期和时间
    
    SELECT LOCALTIMESTAMP();#2021-02-05 12:45:42返回当前日期和时间
    
    SELECT MAKEDATE(2021,5);#2021-01-05基于给定参数年份year和所在年中的天数序号
    
    SELECT MAKETIME(11,11,11);#11:11:11组合时间
    SELECT MICROSECOND("2021-2-5 11:11:11.09000");#90000返回日期参数所对应的微妙数
    
    SELECT MONTHNAME("2021-2-5");#February返回该日期中的月份
    
    SELECT MONTH("2021-02-5");#2返回日期中的月份值
    
    SELECT now();#2021-02-05 12:52:02返回当前的日期和时间
    
    SELECT PERIOD_ADD(202102,4);#202106为年-月组合日期添加一个时段
    
    
    SELECT PERIOD_DIFF(202101,202109);#-8返回两个时段之间的月份差值
    
    SELECT QUARTER("2021-2-5 ");#1返回日期的第几季度
    
    SELECT SECOND("11:11:11");#11返回时间中的秒
    
    SELECT SEC_TO_TIME(3600);#01:00:00以秒为单位进行时分秒的转换
    
    SELECT STR_TO_DATE("August 10 2021","%M %d %Y");#2021-08-10以字符串吧转变为日期
    
    SELECT SUBDATE("2021-2-5 ",INTERVAL 1 DAY);#2021-02-04日期减去1天之后
    
    SELECT year("2021-2-5");#2101返回年份
    
    SELECT WEEKOFYEAR("2021-2-5");#第五个星期 计算日期是本年的第几个星期，范围是0到53
    
    
    SELECT weekDay("2021-2-5");#4代表星期五 日期是星期几，0表示星期一，1表示星期二
    
    SELECT TIMEDIFF("11:11:11","12:12:12");#-01:01:01计算时间差值
    
    SELECT TIMESTAMP("2021-2-5","11:11:11");#2021-02-05 11:11:1单个参数时，函数返回日期或日期时间；有两个参数时，将参数加和

select abs(-1)去整数  
select ceiling(9.4)向上取整10；  
floor(9.4)向下取整  
rand()随机一个0到1的随机数  
sign(10) 判断一个数的符号 负数返回-1,正数返回1；0返回0  
concat(‘j’,‘a’,‘v’,‘a’);拼接  
char\_lenght(‘无聊’);返回字符串长度  
insert();  
instr(“douodu”,u);返回第一次出现相同字串的索引  
repalce(“我爱java”,“java”,“sql”);替换出现的指定字符串；  
substr(“加油奥利给”，2,3)返回指定的字符串  
ROUND(AVG(score.s\_score),2)返回小数点两位的平均数  
AVG(IFNULL(score.s\_score,0(1)))//如果为空说明成绩为0（1）.000,  
**获取当前日期  
select current\_date()**  
select curdate();  
**获取当前的时间**  
now()  
**本地时间**  
localtime()  
**系统时间**  
sysdate()  
year(now())获取年份  
**系统**  
SELECT SYSTEM\_USER()系统用户  
SELECT USER()系统用户  
SELECT VERSION()获取版本号  
等等，可以查阅mysql的官网

**聚合函数**  
count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计

SELECT COUNT(s_student.photo) FROM db_student_manage_javaweb.s_student

count(\*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL  
count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL

-- select count(1) from s_score
    SELECT COUNT(*) from s_score

**MD5()加密函数**  
MD5由MD4、MD3、MD2改进而来，主要增强算法复杂度和不可逆性。MD5算法因其普遍、稳定、快速的特点，仍广泛应用于普通数据的加密保护领域

## 触发器 ##

Mysql5.0.2以后支持触发器功能  
它是一个特殊的数据库事件，如insert、update、delete发生时，自动触发的一段代码  
语法：

create trigger 触发器名
    {before/after}(什么时候开始触发，触发时间)
    {insert/update/delete}(触发事件)
    on 表名 FOR EACH ROW 要触发的sql语句

create table data(name varchar(25));
    create table total(count int);
    insert into total (count) values(0);
    create trigger t1
      	after
      	insert 
      	on data for each row
      	update total set count = char_length(new name)

说明：  
触发器只能定义在永久表上，不能对临时表进行创建  
mysql对同一个表相同触发时间的相同触发事件，只能定义一个触发器  
删除触发器：

drop trigger 触发器名称

drop trigger t1

sqlserver中  
DML触发器分为  
1、after触发器  
insert触发器  
update触发器  
delete触发器  
2、instead of触发器(之前触发)  
例：

create trigger tri_name
    on table
    for insert
    as
      ---T-sql一段sql代码，比如定义变量等其他操作
    go

例2：

create trigger tri_name
    on table
    instead  of insert
    as
      ---T-sql一段sql代码，比如定义变量等其他操作
    go

## 存储过程 ##

存储过程和函数是事先经过编译并存储在数据库中的一段sql语句的集合  
**存储过程和函数的区别：**  
函数必须有返回值，而存储过程没有  
存储过程的参数可以使IN、OUT、INOUT类型，函数的参数只能是IN  
创建存储过程需要使用CREATE PROCEDURE语句

create procedure sp_name((in in_paraml type,...，out out_paraml type...) (characteristics)) rountine_body

sp\_name:存储过程的名称  
n\_Paraml type:存储过程参数  
out\_paraml type:存储过程中需要定义的变量  
routine\_body:存储过程执行体  
存储过程的优点：  
存储过程运行效率高，提供了在服务器端快速执行SQL语句的有效途径  
存储过程降低了客户机和服务器之间的通信量。  
方便实施企业规划。可以把企业规划的运算程序写成存储过程放入数据库服务器中，由关系数据库管理，既有利于集中控制，又能方便地进行维护  
执行存储过程：  
使用 CALL 过程名（参数1，参数2）  
删除存储过程：  
使用 DROP PROCEDURE 过程名  
函数的语法：

create function func_name((func_paramter))
    returns type (characteristic...)
    routine_body

func\_name:函数名称  
（func\_parameter）:函数参数  
characteristic：函数返回类型  
routine\_body:函数执行体  
例：创建学号为22222查询学生姓名的函数

create function selectSnameBySno()
    returns varchar(20)
    
    RETURN (SELECT epassword from Emp WHERE epassword='22222')

函数调用：

Select selectSnameBySno();

sqlserver中：

create function selectSnameBySno()
    returns varchar(20)
    as begin
    return (SELECT epassword from Emp WHERE epassword='22222);
    end;
    
    select selectSnameBySno();//调用

执行函数

使用 SELECT 函数名称（参数1，参数2，…）

删除函数

使用 DROP FUNCTION 函数名

create function hello(s char(20)) returns char(50)
    
    return concat('Hello,',s,'!');
    
    select hello("world");
    
    
    Hello,world!

删除：  
drop \{procedure/function\} \{if exists\} sp\_name  
一次只能删除一个存储过程或者函数

## 事务（重点） ##

事务管理（ACID）  
理解事务通过转账来帮助我们理解  
**A:800,B:400  
A:600,B:600;**  
A的过程是800-200=600；  
B的过程是400+300=600；  
**原子性（Atomicity）**  
事务中的操作要么都发生，要么都不发生。  
**这两个步骤一起成功，或者一起失败，不能只发生其中一个动作**  
**一致性（Consistency）**  
事务前后数据的完整性必须保持一致。  
**一致性表示事务完成后，结果使我们可预知的**  
**隔离性（Isolation）**  
事务的隔离性是**多个用户并发访问数据库时**，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。  
**持久性（Durability）**  
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响  
如果在操作前（A：800，B：400**事务还没有提交**）服务器宕机或者断电，那么重启数据库以后，数据状态应该为A：800，B：400  
如果在操作后（**事务已经提交**）服务器宕机或者断电，那么重启数据库以后，数据状态应该为A：600，B：600

隔离性（Isolation）  
此时有其他用户C向B转账  
A转账A的，C转C的互不影响  
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。，如果隔离失败  
会出现  
脏读：  
还是A:800,B:400;转账前  
A:600，B:600;转账后。(事务提交之后)  
另一个用户C：1000，也向B转账200  
脏读 指的是一个事务读取了一个事务**未提交的数据**  
所以B:600,C:800;很明显B的最终结果错误；错误结果是A:600;C:900;B:600  
不可重复读：  
读着读着数据变了  
在一个事务内读取表中的某一行数据，**多次读取结果不同**。（这个不一定是错误，只是某些场合不对）  
虚读(幻读)：  
是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。  
（一般是行影响，多了一行）  
代码理解事务：  
mysql是默认开启事务自动提交  
在我们做添加、删除、更新的时候数据库自动直接持久化到数据库中了

手动关闭事务:

set autocommit =0;//关闭自动条件

事务开启：

start transaction//标记一个事务的开始，从这里开始之后的sql都是在同一个事务下进行

增加、删除、修改操作

UPDATE employee.account set money = money - 200 where `name` = 'A'
    -- update employee.account set account.money = account.money+200 where account.`name` ='B'

提交事务：持久化(说明成功）  
commit  
回滚：回到最初的状态（说明失败）  
rollback

事务结束

set autocommit = 1;//开启自动提交

mysql中事务关键字：  
begin//打开一个事务  
commit//提交到数据库  
rollback//取消操作  
savepoint//保存，部门取消。部分提交  
alter table person type-INNODB//修改数据引擎

## 索引 ##

索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。索引是由指针构成的文件，这些指针逻辑上按照索引关键字值进行排序。索引文件和表的.dbf文件分别存储，并且不改变表中记录的物理顺序。实际上，创建索引是创建一个由指向.dbf文件记录的指针构成的文件。若要根据特定顺序处理表记录，可以选择一个相应的索引，使用索引还可以加速对表的查询操作，但是所付出的代价是可能降低表更新的速度。  
**索引的优点**：  
通过创建唯一索引，可以保证数据库每一行数据的唯一性  
大大的提高查询速度  
可以加速表与表之间的连接  
显著的减少查询中分组和排序的时间

**索引的缺点**：  
创建索引和维护索引需要时间  
创建索引需要占用磁盘空间  
表数据增删改时，需要同步维护索引  
索引的种类：普通索引、唯一索引、单列索引、组合索引、全文索引、空间索引  
索引是帮助mysql高效获取数据的数据结构  
在一个表中，主键索引只能有一个，唯一索引可以有多个  
1、主键索引（PRIMARY KEY）  
唯一标识，主键不可重复，只能有一个列名作为主键  
2、唯一索引（UNIQUE KEY）  
避免重复的列出现，唯一索引可以重复，多个列都可以标识位  
3.常规索引（KEY/INDEK）  
默认的，index ,key关键字来设置  
4、全文索引（FullText）  
在特定的数据库引擎下才有，MyISAM  
快速定位数据  
索引的创建：

CREATE [UNIQUE] [CLUSTER] INDEX<索引名> ON<表名>(<列名> [<次序>][,<列名> [<次序>]] …)

为学生-课程数据库中的Student、Course和SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引

Create UNIQUE INDEX Stusno ON Student(Sno);
    Create UNIQUE INDEX Coucno ON Course(Cno);
       Create UNIQUE INDEX Scno ON SC(Sno ASC, Cno DESC);

索引的使用
    1、再创建表的时候会给字段增加索引
    2、创建完毕后，增加索引
    
    **索引的修改**：

ALTER INDEX <旧索引名> RENAME TO <新索引名>

将SC表的SCno索引名改为SCSno

ALTER INDEX Scno RENAME TO SCSno;

MySQL5.7版本之前报错？可以这样解决
    
    ALTER TABLE tbl_name DROP INDEX old_index_name

ALTER TABLE tbl_name ADD INDEX new_index_name(column_name)

SQL server索引  
唯一索引、主键索引(创建表的时候就指定了)、聚集索引CLUSTERED(聚集索引确定数据的物理顺序)、非聚集索引(NONCLUSTERED )  
创建索引：

create unique/clustered/nonclustered
    index idxname
    on table

删除：DROP INDEX <索引名>ON <表名>；  
删除Student表的Stusno索引

DROP INDEX Stusno ON Student

//显示所有索引信息  
show index from 表名  
//增加一个全文索引（索引名）列名  
alter table 表 add fulltext index `字段名`（`字段名`）（create index 索引名 on 表（字段））  
//分析sql执行的状况 explain  
explain select \* from 表名//非全文索引  
explain select \* from 表名 where 条件

索引再小数据量的时候，用户不大，但是在大数据的时候， 区别十分明显。  
**索引原则**  
索引不是越多越好  
不要对进程变动数据加索引  
小数据量的表不需要加索引  
索引一般加在常用来查询的字段上  
(1 索引并非越多越好  
2 避免对经常更新的表进行过多的索引，并且索引中的列尽可能少  
3 数据量小的表最好不需要使用索引  
4 在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引  
5 当唯一性是某种数据本身的特征时，指定唯一索引  
6 在频繁进行排序或分组（即进行 group by 或 order by 操作）的列上建立索引)  
**索引的数据结构：**  
Hash类型的索引  
Btree:innoDB的默认数据结构

## 视图 ##

视图的含义  
视图是一个**虚拟表**，是从数据库中一个或多个表中导出来的表。视图还可以**从已经存在的视图基础上定义**,所以视图的构造是基于基本表或视图  
视图的作用  
1 简单性：视图能够简化用户的操作  
2 安全性：视图能够对机密数据提供安全保护  
3 逻辑数据独立性：逻辑独立指当数据库重构造时，如增加新的关系或对原有关系增加新的字段等，用户的应用程序不会受影响  
4 更清晰的表达查询：对于一个复杂查询，使用视图去定义后，查询语句就如同查询单表一样简答  
**视图的创建**：

create view 视图名 【（列名）（列名）。。。】 as 子查询  [WITH CHECK OPTION]

建立信息系学生视图：

create view IS_Student as select Sno,Sname,Sage from Student Where Sdept=‘IS’

查询信息系学生的视图

select * from IS_Student

**视图的修改：**

CREATE OR REPLACE VIEW <视图名> [(<列名>) [,<列名>] …)]  AS  <子查询> [WITH CHECK OPTION]

ALTER VIEW <视图名>[(<列名>) [,<列名>] …)] AS <子查询>

修改信息系学生视图：

create or replace view IS_Student as 
    SELECT Sno,Sname,Sage,Sdept FROM Student Where Sdept='IS';

alter view is_Student 
    as
    SELECT Sno,Sname,Sage FROM Student Where Sdept='IS';

视图查看：

show create view 视图名

查看信息系学生视图的详细信息

show create view is_Student

**视图更新**

更新视图是指通过视图来插入（INSERT）、删除（DELETE）和修改（UPDATE）数据。由于视图是不实际存储数据的虚表，因此对视图的更新最终要转换为对基本表的更新，但不是所有的视图都是可更新的  
例：将信息系学生视图IS\_Student中学号为’201915129’的学生姓名改为’刘辰’

UPDATE IS_Student Set Sname = '刘辰' where Sno =  '201915129’;

**视图删除**  
drop view 视图名  
删除信息系学生视图

drop view is_Student

## sql语法 ##

MySQL和SQLserver数据库的过程化SQL，过程化SQL程序的基本结构是块，所有的过程化SQL程序都是由块组成的。这些块可以相互嵌套，每个块完成一个逻辑操作。  
变量的定义：

DECLARE var_name[,varname]… date_type [DEFAULT value]

赋值：

set 变量名=表达式
        select into为变量赋值

流程控制：  
条件控制语句：IF-THEN语句、IF-THEN-ELSE语句和嵌套的IF语句  
IF语句

IF condition THEN
    Sequence_of_statements; // 条件为真时语句序列才被执行
    END IF;

IF-THEN语句

IF condition THEN
    Sequence_of_statements1
    ELSE
    Sequence_of_statements2
    END IF;

循环控制语句：LOOP语句、WHILE-LOOP语句和FOR-LOOP语句  
最简答的循环语句LOOP

LOOP
    Sequence_of_statements; //循环题，一组过程化SQL语句
    END LOOP;

WHILE-LOOP语句

WHILE condition LOOP
    Sequence_of_statements;//条件为真时执行循环体内的语句序列
    END LOOP;

FOR-LOOP循环语句

FOR count IN [REVERSE] bound1…bound2 LOOP
    Sequence_of_statements;
    END LOOP;

数据库管理：  
创建用户  
create user 用户名 identified by ‘密码’  
修改密码：  
修改当前用户密码:set password = PASSWORD(‘密码’)  
修改指定用户密码:set password for 豆皮江 = PASSWORD(‘密码’)  
重命名：  
rename user doupijiang to doupijiang1  
用户授权：（all privileges，全部的全新，库.表 给别人授权不行，其他都能什么做）

grant all privileges on *.*  to doipijiang2

查询权限  
show grants for doupijiang2;//查看指定的用户的权限  
show grants for root@loaclhost

撤销权限 revoke 给哪个库下的哪个表撤销

revoke all privileges on *.* from doupijiang2

删除用户  
drop user doupijiang2  
**数据库备份**  
使用命令行导出

mysqldump -h主机 -u用户名 -p密码 数据库 表名 >物理路径

mysqldump -hlocalhost -uroot -xxxx 数据库 表 >放在哪个盘下面  
命令行导入：  
登录情况下 切换到所指定的数据库  
然后 source 备份文件

source d:/student.sql

还有一种

mysql -u用户名 -p密码 数据库名 < 备份文件

## 锁 ##

(1)一级封锁协议：事务T在修改数据R之前必须先对其加X封锁，直到事务结束才释放。一级封锁协议可防止丢失修改，并保证事务T是可恢复的，但不能保证可重复读和不读“脏”数据。  
(2)二级封锁协议：一级封锁协议加上事务T在读取数据R之前先对其加S锁，读完后即可释放S锁。二级封锁协议可防止丢失修改，还可防止读“脏”数据，但不能保证可重复读。  
(3)三级封锁协议：一级封锁协议加上事务T在读取数据R之前先对其加S锁，直到事务结束才释放。三级封锁协议可防止丢失修改、防止读“脏”数据与防止数据重复读。  
**检测死锁有多种方法**，包括超时法、等待图法等。事务等待图法动态地反映了所有事务的等待情况，并发控制的子系统周期性地生成事务等待图进行检测，而不是在执行每个事务时进行检测。  
串行调度是指一个事务执行完后才开始下一事务的执行，同一时刻不存在两个同时执行的事务，事务执行期间不会相互干扰，保证执行结果正确。若对多个事务的并发调度与这些事务的某一串行调度等价，则该并发调度为可串行化调度，是正确的调度。引入两段锁协议，可以保证可串行化调度，得到正确的执行结果。两段锁协议不能避免死锁，对死锁的处理由DBMS负责，主要采用检测和解除死锁的方案。

## 数据库设计 ##

**为什么需要设计数据库**  
数据库复杂所哟我们需要设计  
槽糕的数据库设计：  
数据冗余，浪费空间  
数据库插入和删除都会很麻烦，异常（屏蔽使用物理外键）  
程序的性能差  
**良好的数据库设计：**  
节省内存空间  
保证数据库的完成性  
方便我们开发系统  
**软件开发中，关于数据库的设计**  
分析需求：分析业务和需要处理的数据库的需求  
概要设计：设计关系图E-R图

**设计数据库的步骤：**  
比如做一个个人博客：  
1、分析需求：  
用户表（用户登录注销，用户的个人信息，写博客等）  
文章表（文章的信息）  
分类表（文章分类）  
友情链接表（友情链接信息）  
自定义表（系统信息、一些主字段等）（key value）  
。。。。  
2、标识实体：  
考虑到每张表有哪些字段  
3、标识实体之间的关系  
user ---- blog  
user ---- category  
user ---- user  
links  
评论 user-- user --blog

## 三大范式 ##

**第一范式（1NF）**:要求数据库表中的每一列都是不可分割的原子数据项  
原子性：保证每一列不可再分

说明：有张表字段是家庭信息（如 三口人 南京。。）学校信息（本科，大三。。。）  
很明显家庭信息表和学校信息表不符合设计要求;把家庭信息这个字段分为家庭人口和户籍，学校信息这个字段分为学历和所在年级  
**第二范式：**  
前提满足第一范式，每张表只描述一件事  
每个表必须有主关键字(Primary key),其他数据元素与主关键字一一对应。通常称这种关系为函数依赖(Functional dependence)关系，即表中其他数据元素都依赖于主关键字,或称该数据元素惟一地被主关键字所标识。第二范式是数据库规范化中所使用的一种正规形式。它的规则是要求数据表里的所有非主属性都要和该数据表的主键有完全依赖关系；**如果有哪些非主属性只和主键的一部份有关的话，它就不符合第二范式**  
产品表：订单号、产品号、产品数量、产品价格、订单金额、订单时间  
很明显上述设计不合理：  
产品表：订单号、产品号、产品数量、产品价格  
订单表：订单号、订单金额、订单时间  
**第三范式：**  
**第三范式需要确保数据表中每一列都和主键相关，不能间接有关**  
比如一张学生表：学号、姓名、性别、家庭人口、班主任姓名、班主任性别、班主任年龄  
很明显这种设计不合理：**班主任性别、班主任年龄直接依赖的是班主任姓名，而不是主键学号**  
学生表：学号。姓名、性别、家庭人口、班主任职工号  
班主任表：班主任职工号、班主任姓名、班主任性别  
**规范性和性能的问题**（两者不可兼得）  
关联查询的表不得超过三张表  
1、考虑商业化的需求和目标，（成本。用户体验等）数据库性能更加重要  
2、在规范性能的问题的时候。需要适当考虑规范性  
3、故意给某些表增加一些冗余的字段（从多表查询中变为单表查询）  
4、故意增加一些就是列（从大数据量降为小数据量的查询：索引）

## 数据库故障与恢复 ##

**并发控制**  
问题的产生：  
多用户数据库系统  
允许多个用户同时使用的数据库系统  
飞机、火车定票数据库系统  
银行数据库系统  
特点：这些系统在同一时刻并发运行的事务数可达数百上千个  
并发访问引发的问题  
事务并发执行带来的问题  
1.会产生多个事务同时存取同一数据的情况  
2.可能会存取和存储不正确的数据，破坏事务一致性和数据库的一致性  
一个事务开始读取了某行数据，但是另外一个事务已经更新了此数 据但没有能够及时提交  
一个事务对同一行数据重复读取两次，但是却得到了不同的结果  
两个事务都同时更新一行数据，但是第二个事务却中途失败退出， 导致对数据的两个修改都失效了  
有两个并发事务同时读取同一行数据，然后其 中一个对它进行修改提交，而另一个也进行了修改提交。这就会造成第一次写操作失效  
并发控制概述：  
事务时并发控制的基本单位，保证事务的ACID特性是事务处理的重要任务，而事务的ACID特性可能遭到破坏的原因之一是多个事务对数据库的并发操作操作的。为了保证事务的隔离性和一致性，数据库管理系统需要对并发操作进行正确调度。这些就是数据库管理系统中并发控制机制的责任。  
**并发控制任务**  
对并发操作进行正确调度  
保证事务的隔离性  
保证数据库的一致性  
**并发控制的主要技术**  
封锁（locking）  
时间戳（time）  
乐观控制法  
多版本并发控制  
**数据库恢复：**  
数据库系统采用了各种保护措施来防止数据库的安全性和完整性被破坏，保证并发事务的正确执行  
计算机系统中的硬件故障，软件错误，操作员的失误以及黑客的恶意破坏是不可避免的，会影响到数据库中的数据正确性，甚至破坏数据库，使数据库的数据丢失  
数据库管理系统需要具体把数据库从错误状态恢复到某一已知的正确状态的能力，这就是数据库的恢复  
**数据库故障**  
影响数据库安全性和完整性的故障大体可以分为**事务内部的故障、系统故障、介质故障、计算机病毒**  
各类故障对数据库的影响有两种可能性，一是数据库本身被破坏，二是数据库数据不正确  
事务内部的故障  
事务内部的故障有的是可以通过事务程序本身发现的，有的是非预期的，不能由事务程序处理  
事务内部更多的故障是非预期的，是不能由应用程序处理的  
系统故障  
系统故障是指造成系统停止运转的任何事件，使得系统要重新启动。如：特定类型的硬件错误（CPU故障）、操作系统故障、DBMS代码错误、系统断电等。这类故障影响正在运行的所有事务，但不破坏数据库  
发生系统故障时，一些尚未完成的事务的结果可能已送入物理数据库，从而造成数据库可能处于不正确的状态。为保证数据一致性，需要清除这些事务对数据库的所有修改  
介质故障  
系统故障常称为软故障，介质故障称为硬故障。硬故障指外在故障，如磁盘损坏、磁头碰撞，瞬时强磁场干扰等  
硬故障将破坏数据库或部分数据库，并影响正在存取这部分数据的所有事务。这类故障比系统和事务故障发生的可能性小得多，但破坏性最大  
计算机病毒  
计算机病毒是一种人为的故障或破坏，是一些恶作剧者研制的一种计算机程序。这种程序与其他程序不同，它像微生物学所称的病毒一样可以繁殖和传播，并造成对计算机系统包括数据库的危害。  
总结  
各类故障对数据库的影响有两种可能性，一是数据库本身被破坏，二是数据库没有被破坏，但数据可能不正确，如事务的运行被非正常终止造成数据不一致。  
恢复的基本原理十分简单，可以用一个词来概括：冗余。即数据库中任何一部分被破坏或不正确的数据可以根据存储在系统别处的冗余数据来重建。  
**转储**  
建立数据冗余：  
数据转储：数据库恢复中采用的基本技术，即管理员定期地将整个数据库复制到磁带、磁盘或者其他介质上保存起来的过程；备用数据也称为后备副本或后援副本  
登记日志文件：日志文件是用来记录事务对数据库的更新操作的文件  
数据转储：  
静态转储：在系统中无运行事务的转储操作，即转储操作开始的时刻数据库处于一致性状态，而转储期间不允许对数据库的任何存取、修改活动。  
动态转储：转储期间允许对数据库进行存取或修改，即转储和用户事务可以并发执行  
静态转储特点：  
静态转储得到的一定是一个数据一致性的副本  
静态转储必须等待正运行的用户事务结束才能进行，新的事务必须等待转储结束才能执行，但同时也降低数据库的可用性  
动态转储特点:  
动态转储不用等待正在运行的用户事务结束，也不会影响新事务的运行  
动态转储结束时后援副本上的数据并不能保证正确有效  
动态转储需要将转储期间各个事务对数据库的修改活动记录登记下来，建立日志文件  
量转储与增量转储:  
数据转储有两种方式，分别可以在两种状态下进行，  
因此数据转储方法可以分为四类：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDA5MzcxMQ_size_16_color_FFFFFF_t_70]  
**日志文件**  
登记日志文件  
日志文件的格式和内容  
日志文件的作用  
登记日志文件  
日志文件的格式和内容  
日志文件需要登记的内容包括：事务的开始、事务的结束、事务的所有更新操作  
日志记录的内容需要包括：事务标识、操作类型、操作对象、更新前数据的旧值、更新后数据的新值  
日志文件的作用  
事务故障恢复和系统故障恢复必须用日志文件  
在动态转储方式中必须建立日志文件，后备副本和日志文件结合起来才能有效地恢复数据库  
在静态转储方式中也可以建立日志文件，当数据库毁坏后可重新装入后援副本把数据库恢复到转储结束时刻的正确状态，再利用日志文件把已完成的事务进行重做处理，对故障发生时尚未完成的事务进行撤销处理  
登记日志文件：日志文件的登记必须遵循两条原则  
登记的次序严格按并发事务执行的时间次序  
必须先写日志文件，后写数据库  
**数据库恢复策略**  
数据库恢复策略有：**事务故障的恢复、系统故障的恢复、介质故障的恢复**  
事务故障的恢复  
事务故障是指事务在运行至正常终止点前被终止，这时恢复子系统应利用日志文件撤销此事务已对数据库进行的修改。  
系统故障的恢复  
系统故障的恢复操作：撤销故障发生时未完成的事务，重做已完成的事务  
系统故障的恢复由系统在重新启动时自动完成的，不需要用户干预  
介质故障的恢复  
介质故障的恢复操作：重装数据库，然后重做已完成的事务

## 数据库自增ID用完了会怎么样？ ##

如果**设置了主键**，并且一般会把主键设置成自增。  
我们知道，Mysql里int类型是4个字节，如果有符号位的话就是`[-2^31,2^31-1]`，无符号位的话最大值就是2^32-1，也就是4294967295  
创建表语句：

CREATE TABLE `test1` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
     `name` varchar(32) NOT NULL DEFAULT '',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2147483647 DEFAULT CHARSET=utf8mb4;

插入语句

insert into test1(name) values('豆豆');

再插入一条：

insert into test1(name) values('ww');

就会看到错误提示：1062 - Duplicate entry ‘2147483647’ for key ‘PRIMARY’, Time: 0.000000s。  
也就是说，如果设置了主键并且自增的话，达到自增主键上限就会报错重复的主键key。  
解决方案，mysql主键改为bigint，也就是8个字节。  
**设计的时候要考虑清楚值的上限是多少，如果业务频繁插入的话，21亿的数字其实还是有可能达到的**  
如果**没有设置主键**的话，InnoDB则会自动帮你创建一个6个字节的row\_id，由于row\_id是无符号的，所以最大长度是2^48-1。

CREATE TABLE `test2` (
     `name` varchar(32) NOT NULL DEFAULT ''
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

通过ps -ef|grep mysql拿到mysql的进程ID，然后执行命令，通过gdb先把row\_id修改为1

sudo gdb -p 2584 -ex 'p dict_sys->row_id=1' -batch

然后插入几条数据：

insert into test2(name) values('1');
    insert into test2(name) values('2');
    insert into test2(name) values('3');

再次修改row\_id为2^48，也就是281474976710656

sudo gdb -p 2584 -ex 'p dict_sys->row_id=281474976710656' -batch

再次插入数据

insert into test2(name) values('4');
    insert into test2(name) values('5');
    insert into test2(name) values('6')

然后查询数据会发现4条数据，分别是4，5，6，3。  
因为我们先设置row\_id=1开始，所以1，2，3的row\_id也是1，2，3。  
修改row\_id为上限值之后，row\_id会从0重新开始计算，所以4，5，6的row\_id就是0，1，2。  
由于1，2数据已经存在，数据则是会被覆盖  
**自增ID达到上限用完了之后，分为两种情况**：  
如果设置了主键，那么将会报错主键冲突。  
如果没有设置主键，数据库则会帮我们自动生成一个全局的row\_id，新数据会覆盖老数据  
解决方案：  
表尽可能都要设置主键，主键尽量使用bigint类型，21亿的上限还是有可能达到的，比如魔兽，虽然说row\_id上限高达281万亿，但是覆盖数据显然是不可接受的。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDA5MzcxMQ_size_16_color_FFFFFF_t_70]: /images/20221120/823c816495744943abde70c4fc03963f.png