《深入浅出mysql》学习笔记

缺乏、安全感 2021-07-26 21:32 420阅读 0赞

## 一：sql分类及语法 ##

--------------------

1.sql分类：  
（1）DDL：数据库定义语言，用于定义表，列，索引等数据库对象. create,drop,alter等  
（2）DML：数据库操纵语言，增删改查数据库的记录insert，delete，update，select等  
（3）DCL：数据库控制语言，定义了数据库的表，字段，用户的访问权限和安全级别 grant，revoke等  
2.DDL语句  
（1）创建数据库:create database test1;  
（2）删除数据库：drop database test1；  
（3）创建表：create table user(name varchar(10),age int(2));  
（4）删除表：drop table user;  
（5）修改表：

alter table user modify name varchar(20);
    alter table user add column sex varchar(10);
    alter table user drop column age;
    Alter table user change age age1 int(4);
    Alter table user rename user1;

3.DML语句  
（1）插入记录insert into user(name,age)values(‘zhangsan’,18);  
（2）删除记录：delete from user where name=’张三’  
（3）更新记录：update user set age=1 where name=’ 张三’  
（4）查询记录：select \* from user;  
（5）查询不重复的记录j：select distinct name from user;  
（6）排序查询：select from user order by age desc;  
（7）限制排序：select \* from user order by age limit 1,30;  
（8）聚合：select name,age from user group by age;  
（9）聚合后条件：select name,age from user group by age having age > 18;  
（having和where的区别：having是聚合后条件，where是聚合前条件）  
（10）表连接：   
①内连接：  
Select ename,deptname from emp,dept where emp.deptno=dept.deptno;  
②外连接：

左连接：（查询 emp 中所有用户名和所在部门名称：）        
    select ename,deptname from emp left join dept on emp.deptno=dept.deptno;
    
    右连接： 
    select ename,deptname from emp left join dept on emp.deptno=dept.deptno;

· 左连接：包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录  
· 右连接：包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录

（11）子查询：需要的条件是另一个select的结果  
（子查询的关键字主要包括in, not in,=,!=,exists,not exists ）  
（如果查询结果数唯一，可用=代替in）  
select \* from emp where deptno in(select deptno from dept);  
select \* from emp where deptno = (select deptno from dept limit 1);

（12）记录联合：

select * from t1 union select * from t2
    select * from t1 union all select * from t2

（union和union all的区别：union是将union all查询出来的结果进行了一次distinct，去除重复后的结果）

4.DCL语句

（1）授权：创建一个数据库用户 z1，具有对 sakila 数据库中所有表的 SELECT/INSERT 权限：  
grant select,insert on sakila.\* to 'z1'@'localhost' identified by '123';

（2）收回权限：由于权限变更，需要将 z1 的权限变更，收回 INSERT，只能对数据进行 SELECT 操作：  
revoke insert on sakila.\* from 'z1'@'localhost';

## 二：mysql支持的数据类型 ##

--------------------

**字段类型：**

*  在mysql中包含的数据类型很多，这里主要列出来常用的几种
 *  数字：int,decimal
 *  字符串：varchar,text
 *  日期：datetime
 *  布尔：bit

**约束：**

*  主键primary key
 *  非空not null
 *  惟一unique
 *  默认default
 *  外键foreign key

数字类型，字符串类型，日期和时间类型  
1.数值类型

![aHR0cHM6Ly9zZWdtZW50ZmF1bHQuY29tL2ltZy9iVmJiVEVoP3c9NjkyJmg9NTE5][]

2.字符串类型

![aHR0cHM6Ly9zZWdtZW50ZmF1bHQuY29tL2ltZy9iVmJiVEVrP3c9NjkyJmg9MzYy][]

3.日期类型

![aHR0cHM6Ly9zZWdtZW50ZmF1bHQuY29tL2ltZy9iVmJiVEV1P3c9NjkyJmg9MTc3][]

## 三.mysdql中的运算符 ##

--------------------

（1）算数运算符

![aHR0cHM6Ly9zZWdtZW50ZmF1bHQuY29tL2ltZy9iVmJiVEV2P3c9NjkyJmg9MTcz][]

（2）比较运算符

![aHR0cHM6Ly9zZWdtZW50ZmF1bHQuY29tL2ltZy9iVmJiVEV4P3c9NjkyJmg9NDQ3][]

（3）逻辑运算符

![aHR0cHM6Ly9zZWdtZW50ZmF1bHQuY29tL2ltZy9iVmJiVEV5P3c9NjkyJmg9MTY0][]

（4）位运算符

![aHR0cHM6Ly9zZWdtZW50ZmF1bHQuY29tL2ltZy9iVmJiVEV6P3c9NjkzJmg9MjQ4][]

## 四.mysql中常用的函数 ##

--------------------

1.字符串函数

![aHR0cHM6Ly9zZWdtZW50ZmF1bHQuY29tL2ltZy9iVmJiVEVBP3c9NjkyJmg9NDk2][]

2.数值函数

![aHR0cHM6Ly9zZWdtZW50ZmF1bHQuY29tL2ltZy9iVmJiVEZuP3c9NjkyJmg9Mjc2][]

3.日期时间函数

![aHR0cHM6Ly9zZWdtZW50ZmF1bHQuY29tL2ltZy9iVmJiVEZzP3c9NjkyJmg9NDU4][]

4.其它常用函数

![aHR0cHM6Ly9zZWdtZW50ZmF1bHQuY29tL2ltZy9iVmJiVEZ2P3c9NjkyJmg9Mjcz][]

## 五.表类型（存储引擎的选择） ##

--------------------

1.查询当前数据库支持的存储引擎

Show engines \G

2.Mysql常见存储引擎对比

![aHR0cHM6Ly9zZWdtZW50ZmF1bHQuY29tL2ltZy9iVmJiVEZOP3c9NjkyJmg9NDM3][]

（1）Myisam

> Mysam是mysql的默认存储引擎  
> Myisam不支持事务和外键  
> 优势是访问速度快

（2）Innodb

> Innodb提供事务和外键  
> 占用磁盘空间保留索引，写的效率比innodb差

（3）Memory

> 数据存放在内存中  
> 访问速度非常快（使用hash索引）  
> 但服务一旦关闭，表中的数据丢失

## 五.选择合适的数据类型 ##

--------------------

1.char与varchar？

> Char属于固定长度的字符类型，varchar属于可变长度的字符类型  
> 所以char处理速度比varchar快得多，但是浪费存储空间  
> （但随着mysql版本的升级varchar的性能也在不断的提升，所以目前varchar被更多的使用）  
>

2.text与blob？  
（1）两者都能保存大文本数据  
Blob能用来保存二进制数据，比如照片  
Text只能保存文本数据，如文章  
（2）text和bolo 字段在进行删除操作时会出现“空洞”现象（表数据文件的大小并没有因为删除数据而减小），可以使用OPTIMIZE TABLE t; 进行优化操作  
（3）采用的优化操作一般是把blob或text列放到一个单独的表中

3.浮点数与定点数？  
（1）定点数：小数点固定在某个位置上的数据。 就好像 0.0000001 ，0.0001111；  
（2）浮点数：小数点位置可以浮动的数据。就像数学中的 1222.2*10^3也可以表示为1.2222*10^6；  
（3）在java中，我们知道System.out.print("7.22-7.0=" + (7.22f-7.0f));的结果并不是0.22而是0.219999，因此在程序中尽量避免浮点数的比较，运算。而是通过定点数进行比较和运算  
BigDecimal b1 = new BigDecimal(Double.toString(v1));  
(4)数据库中，float，double表示浮点数  
用decimal或numberic表示定点数  
所以对于货币等敏感数据，用定点数存储

4.日期类型的选择？  
如果只记录年份，用year  
如果还要记录时分秒，用datetime  
如果考虑不同时区，用timestamp

## 六.字符集 ##

--------------------

（1）第一个字符集ASCII  
（2）为了处理不同的文字，又出现了几百种字符集。如iso-8859，GBK,GB2312等  
（3）为了统一编码，国际标准化组织iso制定了国际字符集标准UCS，这种标准采用四字节编码，将代码空间划分位组，面，行，格  
（4）这种UCS编码遭到了很多美国计算机协会的反对（sun，apple，ibm等）它们组成了unicode的协会，并推出了unicode1.0（二字节）  
（5）后来为了编码格式的统一，双方展开谈判，将unicode编码并入UCS的0组0字面。把它称作基本多语言文字面（BMP），剩下的两个字节做辅助字面和专用字面  
（6）其实人们常用到的还是unicode里的字符（99%），但是要用unicode里没有而ucs有的怎么办呢？所以制定了UTF-16,后来UTF-16在使用过程中出现了一系列的问题，所以出现了UTF-8（1至4字节编码）

## 七.索引的设计和使用 ##

--------------------

0.什么是索引？  
系统根据某种算法，将已有的数据（和未来新增的数据）单独建立一个文件，文件能够实现快速的匹配数据，并能够快速的找到对应表中的记录  
1.每种存储引擎（innodb，myidsam等）对每个表至少支持16个索引，myisam和innodb默认创建的都是BTREE索引，memory存储引擎默认使用hash索引  
2.创建索引：  
create index 索引名 on 表名 列命  
3.删除索引  
drop index 索引名 on 表名  
4.mysql中提供的索引类型？  
（1）主键索引  
（2）唯一索引  
（3）全文索引：根据文章内部的关键字进行索引  
（4）普通索引

## 八.视图 ##

--------------------

1.什么是视图？  
视图是一种虚拟存在的表。通俗的讲，视图就是一条SELECT语句执行后返回的结果集。  
2.什么时候用到视图？  
（1）经常用到的查询或复杂的联合查询  
（2）涉及到权限管理（比如表中某部分字段含有机密信息，不让低权限的用户看到，可以提供给他们一个适合他们权限的视图  
3.语句  
（1）创建：Create or replace view 视图名 as + 查询语句  
（2）查看：show create view 视图名  
（3）删除：drop view 视图名  
4.视图的意义  
（1）可以节省sql语句（将一条复杂的查询结果通过视图保存）  
（2）视图操作是怎对查询出来的结果，不会对原数据产生影响，相对安全  
（3）更好的进行权限控制

## 九.函数 ##

--------------------

1.什么是函数？  
将一段代码封装到一个结构中，在需要执行代码的时候调用函数即可（实现了复用）（任何函数都有返回值，因此函数通过select调用）  
1.函数的分类？  
（1）系统函数：系统调用好的函数，直接调用即可  
Select subString（字符串，开始，结束）  
Select char\_length(字符串)  
（2）自定义函数：  
创建语法：create function 函数名（形参列表）  
Begin  
函数体  
Return 类型  
End  
调用： select 函数名（）；

## 十.存储过程 ##

--------------------

1.存储过程是什么？  
存储过程是没有返回值的函数

2.创建过程？  
Create procedure 过程名字（参数列表）  
Begin  
\---过程  
End

3.调用过程？  
(过程没有返回值，不能用select调用。有一个专门的关键字call)  
Call 过程名（）；

4.删除过程  
Drop proceddure pro1；

5.过程参数  
过程参数还有自己的类型限定（In out inout）

IN参数：  
仅需要将数据传入存储过程，并不需要返回计算后的该值。

OUT参数：  
不接受外部传入的数据，仅返回计算之后的值。

INOUT参数：  
需要数据传入存储过程经过调用计算后，再传出返回值。  
（具体参考：[http://blog.itpub.net/29773961/viewspace-1769098/][http_blog.itpub.net_29773961_viewspace-1769098]）

## 十.触发器 ##

--------------------

1.什么是触发器？  
Tigger，监视某种情况并触发某种操作

## 十一.范式 ##

--------------------

参考上篇博文  
[https://segmentfault.com/a/1190000015172600][https_segmentfault.com_a_1190000015172600]

[aHR0cHM6Ly9zZWdtZW50ZmF1bHQuY29tL2ltZy9iVmJiVEVoP3c9NjkyJmg9NTE5]: /images/20210726/dbbaa6fc40c74191bfaccfdfdda0617b.png
[aHR0cHM6Ly9zZWdtZW50ZmF1bHQuY29tL2ltZy9iVmJiVEVrP3c9NjkyJmg9MzYy]: /images/20210726/c79552cd081d40a1a9c1d7e6121cdc49.png
[aHR0cHM6Ly9zZWdtZW50ZmF1bHQuY29tL2ltZy9iVmJiVEV1P3c9NjkyJmg9MTc3]: /images/20210726/31cf294df13e4c9386195fa2449af9c0.png
[aHR0cHM6Ly9zZWdtZW50ZmF1bHQuY29tL2ltZy9iVmJiVEV2P3c9NjkyJmg9MTcz]: /images/20210726/7f4eb14a5ec347d5a1b1a32fea2e7a61.png
[aHR0cHM6Ly9zZWdtZW50ZmF1bHQuY29tL2ltZy9iVmJiVEV4P3c9NjkyJmg9NDQ3]: /images/20210726/890c26f0095f4737988bce94daecd341.png
[aHR0cHM6Ly9zZWdtZW50ZmF1bHQuY29tL2ltZy9iVmJiVEV5P3c9NjkyJmg9MTY0]: /images/20210726/48400509e6194a73bb95d4e6eb6af91d.png
[aHR0cHM6Ly9zZWdtZW50ZmF1bHQuY29tL2ltZy9iVmJiVEV6P3c9NjkzJmg9MjQ4]: /images/20210726/7c3b00a3ea104236b5d56d207cb82272.png
[aHR0cHM6Ly9zZWdtZW50ZmF1bHQuY29tL2ltZy9iVmJiVEVBP3c9NjkyJmg9NDk2]: /images/20210726/c776cbbe1b784e11b26c1715add25ade.png
[aHR0cHM6Ly9zZWdtZW50ZmF1bHQuY29tL2ltZy9iVmJiVEZuP3c9NjkyJmg9Mjc2]: /images/20210726/a5e04f065e12487ba2b4182d6ac61d46.png
[aHR0cHM6Ly9zZWdtZW50ZmF1bHQuY29tL2ltZy9iVmJiVEZzP3c9NjkyJmg9NDU4]: /images/20210726/f58a1e159cb544fb93c425123cbe8d9e.png
[aHR0cHM6Ly9zZWdtZW50ZmF1bHQuY29tL2ltZy9iVmJiVEZ2P3c9NjkyJmg9Mjcz]: /images/20210726/7ed32bffca364fe7a9f750398ef60d31.png
[aHR0cHM6Ly9zZWdtZW50ZmF1bHQuY29tL2ltZy9iVmJiVEZOP3c9NjkyJmg9NDM3]: /images/20210726/8c8bb545bbdb4089ab4686a78a1227b5.png
[http_blog.itpub.net_29773961_viewspace-1769098]: http://blog.itpub.net/29773961/viewspace-1769098/
[https_segmentfault.com_a_1190000015172600]: https://segmentfault.com/a/1190000015172600