全网最全sql入门经典

SQL入门

Mysql,SQLServer,Oracle

database table row clo

primary key forgin key unique auto increase distinct top

sql语句及优化

存储过程

性能调优

SQL历史

SQL历史由来

SQL: 结构化查询语言 是与关系型数据库进行通信的标准语句。

DBMS: Database Manager System 数据库管理系统

关系型数据库: Mysql SQLServer Oracle

菲关系型数据库: redis mongoDB

关系型数据库是由一张张表构成的，这些表相互关联着(外键)，是存储数据的最小独立逻辑单元。

SQL命令类型

DDM Database define manager create alter drop

DQL Database Query manager select

DCL Database Control manager grant revoke

DML insert update delete

注意

一般公司的数据不会删除，delete操作知识逻辑上的删除，并不会进行真正意义上的删除，现在是一个数据为王的时代，数据就是钱，一般在设计数据库结构的时候，都会加上一个字段isValid (表示条数据是否有效)，当用户执行delete的时候，会将这个字段的值由1改为0

DML

insert into table_name (name,age,address,tel) values (“nacos”,24,“江苏”,“18627”); – 新增

update table_name set name=“druid”,age=23,address=“苏州”,tel=“1387” where id = 8; – 根据id修改

delete from table_name where id = 7; – 根据id删除，一般公司都是逻辑删除，不会对数据进行物理删除，都是update修改一个字段就当删除了

DQL

select */name,age,address,tel from table_name where id = 5; – 或者不要where

常见sql 函数

sum() avg() count() max() min()

order by 与 group by

order by 是根据那个字段或者哪几个字段进行排序 DESC /ASC DESC 降序 ASC 升序 不写默认ASC

group by

order by 中的列 ，必须出现在 group by 里面

order by 侧重排序规则 group by 侧重分组

select stu_type,sum(score) from students group by id,stu_type order by id;

提到group by 就必须要说一说 having，having相当于是条件筛选，但是它与where 不同，having是对group by对象进行筛选，where就不可以

having

SELECT
    ( SELECT d.dept_name FROM departments d WHERE de.dept_no = d.dept_no ) AS 部门,
    count( de.emp_no ) AS 人数 
FROM
    dept_emp de 
WHERE
    de.to_date = '9999-01-01' 
GROUP BY
    de.dept_no 
HAVING
    count( de.emp_no ) > 30000 

顺序

select

from

where

group by

having

order by

having必须在group by 后面，order by 前面

事务

事务命令只与DML语句配合使用

commit 提交事务 ，一般事务成功就commit

rollback 回滚事务，当出现异常的时候回滚事务，在该事务中出现的DML语句都失效

savepoint 保存点

rollback to savepoint 回退到保存点

release savepoint 删除创建的保存点

set transaction 初始化数据库事务 eg: set transaction read write; set transaction read only;

事务是指是程序中一系列严密的逻辑操作，而且所有操作必须全部成功完成，否则在每个操作中所作的所有更改都会被撤消。可以通俗理解为：就是把多件事情当做一件事情来处理，好比大家同在一条船上，要活一起活，要完一起完。

经典案例就是银行转账

事务的四个特性ACID

原子性 Atomicity ：操作这些指令时，要么全部执行成功，要么全部不执行。只要其中一个指令执行失败，所有的指令都执行失败，数据进行回滚，回到执行指令前的数据状态。

一致性 Consistency ： 事务的执行使数据从一个状态转换为另一个状态，但是对于整个数据的完整性保持稳定。

隔离性 Isolation ： 隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

持久性 Durability ： 当事务正确完成后，它对于数据的改变是永久性的。

并发事务导致的问题

在许多事务处理同一个数据时，如果没有采取有效的隔离机制，那么并发处理数据时，会带来一些的问题。

第一类丢失更新：撤销一个事务时，把其他事务已提交的更新数据覆盖。

小明去银行柜台存钱，他的账户里原来的余额为100元，现在打算存入100元。在他存钱的过程中，银行年费扣了5元，余额只剩95元。突然他又想着这100元要用来请女朋友看电影吃饭，不打算存了。在他撤回存钱操作后，余额依然为他存钱之前的100元。所以那5块钱到底扣了谁的？

脏读：脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。

小明的银行卡余额里有100元。现在他打算用手机点一个外卖饮料，需要付款10元。但是这个时候，他的女朋友看中了一件衣服95元，她正在使用小明的银行卡付款。于是小明在付款的时候，程序后台读取到他的余额只有5块钱了，根本不够10元，所以系统拒绝了他的交易，告诉余额不足。但是小明的女朋友最后因为密码错误，无法进行交易。小明非常郁闷，明明银行卡里还有100元，怎么会余额不足呢？（他女朋友更郁闷。。。哈哈密码错误）

幻读也叫虚读：一个事务执行两次查询，第二次结果集包含第一次中没有或某些行已经被删除的数据，造成两次结果不一致，只是另一个事务在这两次查询中间插入或删除了数据造成的。幻读是事务非独立执行时发生的一种现象。

例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。

**不可重复读：**一个事务两次读取同一行的数据，结果得到不同状态的结果，中间正好另一个事务更新了该数据，两次结果相异，不可被信任。

例如事务T1在读取某一数据，而事务T2立马修改了这个数据并且提交事务给数据库，事务T1再次读取该数据就得到了不同的结果，发送了不可重复读。

**：**不可重复读和脏读的区别：脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。

幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

数据库有四种隔离级别

read uncommitted 最低级别，任何情况都无法保证。

read committed 可避免脏读的发生

repeatable read 可避免脏读、不可重复读的发生。

Serializable 可避免脏读、不可重复读、幻读的发生 序列化，

隔离级别越高，效率也就越低，所以一般根据场景选择合适的隔离级别

一般mysql默认是read committed 这个隔离级别

分布式之数据库同步问题

跨国公司，为了提高公司的业务能力，在各个地区都部署有服务器，像一般的大公司在一些省会城市都部署有数据库，这些数据库是怎么同步的，怎么保证两地的数据是一样的，特别是一些互联网公司，数据量很大，如果同时对两地的数据库的同一条数据进行修改，那么合并的时候以哪个版本为主，或者查询的时候查到的是哪个update后的版本。

操作符

is null 不为空

between and 在范围内

in 在一个数组内，把一个值与一个指定的列表进行比较

like 模糊查询

exists 存在 查找指定表里是否存在特定的记录

unique

all/any/some

and

or

is not null

not betwoon

not in

not like

not exists

not unique

字符函数

translate 替换

replace 替换

upper 大写

lower 小写

substr 获取子串

insert

ltrim 截取 left trim

rtrim 剪除 right trim

decode

算术运算符

ABS 绝对值

ROUND 舍入

SQRT 平方根

SIGN 符号

POWER 幂

CEIL FLOOR 上限、下限

EXP 指数

SIN COS TAN

转换函数

这些都可以在后端进行转换,把复杂的操作放入后端执行,当然如果非要在数据库中转换也是可以的

字符到数字

数字到字符

字符到日期

日期到字符

连接查询

join on

其实是 inner join on 省略了inner

验证join on

SELECT count(1) FROM t_budget a left JOIN t_budget_item b on a.budget_id =b.budget_id
SELECT count(1) FROM t_budget a JOIN t_budget_item b on a.budget_id =b.budget_id

left join on

返回左表的全部数据，以及右表中满足左表的数据，左表中没有匹配的数据就显示null

right join on

以右表为基准，返回右表的所有数据，以及左表满足条件的数据

SQL性能调优

利用索引改善性能

举个例子，将数据库比作成一本书，那么索引就是书本的目录。使用目录可以快速定位到你想看的地方，但是，目录会占用一定的空间，每页都会有一个地方用来显示，然后目录页需要花几页来显示。

索引就是指针，指向表里的数据。

CREATE INDEX

单字段索引: 基于一个字段创建

唯一索引: 不允许表里的那个字段有重复值，用于改善性能和保证数据完整性

组合索引:基于一个或多个字段的索引

隐含索引: 数据库服务程序在创建对象时自动创建的

索引的使用

在经常需要搜索的列上，可以加快搜索的速度；

在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

不建议使用索引

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少,不利于使用索引。

第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改操作远远多于检索操作时，不应该创建索引。

SQL语句优化 重点

sql慢查询

sql语句查询慢，这个就需要优化sql语句。一般查询时间超过2s就是慢查询。

解决慢查询 参考

1.数据库中设置sql慢慢查询，修改my.ini配置文件

2.分析慢查询日志，利用explain 关键字可以模拟优化器执行sql查询语句，来分析sql慢查询语句

EXPLAIN SELECT * FROM res_user ORDER BYmodifiedtime LIMIT 0,1000

优化

1.索引没起作用，使用like关键字的查询语句，然后模糊查询失效(匹配的第一个字符为%)，直接全表扫描

2.优化数据库结构(这个一般公司创建就不会去修改了)

3.分解关联查询

4.优化limit分页

5.分析具体SQL语句 不使用*

SQL优化

1.from子句里的表的顺序，将较小的表放在前面，大的表放在后面，性能会更好

2.结合条件次序，大多数结合都是一个基表链接到具有一个或多个公共字段的其他表。基表是主表，将基表中的字段放在where条件判断的右侧，被结合的表一般按照从小到大的顺序结合，join 时，先join数据量较小的表(但是可能这个表在某个时候数据量是比较小的，但是随着系统的运行，数据量成几何速度增长，这个时候sql就会比刚开始慢，所以判断表的大小还不能只看当时的状态)。

一般在对没有索引的表进行查询的时候就是表扫描，这个时候应该选择哪些字段建立索引

主键，一般都是大量不重复的数据

外键

在结合表中经常使用的字段

在查询条件中经常出现的字段(where)

大部分值是唯一值的字段

其他优化

使用like操作符和通配符

避免使用OR

避免使用HAVING

避免大规模的排序

使用存储过程

在批加载时关闭索引

管理数据库用户

数据库安全

一点总结

数据库安全非常重要，小胖我买了一个云服务器，在上面部署了一个mysql，因为密码设置的特别简单(6位，第一次没有经验，而且端口号还是3306)，被黑客暴力破解了，直接将我的数据库全给删了，提示需要交0.035BTB(大约3000)才能恢复，关键是我的数据库也没有每天对它进行备份，所以要么交钱，要么所有的数据就没了。这要是一般的创业公司。公司的数据非常的重要，可能是一个团队几个月，几年的付出才换来的。而且数据库被黑，公司对外提供的服务就全部暂停，用户体验也是非常的不好(试想支付宝的数据被黑，所有用户的金额数据全都没了，那支付宝不就直接原地爆炸了么)。所有平常数据库备份非常重要，一些对外的服务端口、密码一定要设置的复杂一点。