MySQL基础篇

对于一个后端程序员来说，SQL语句好像是必不可少的一项技能，所以基础而又常见的SQL语句得学习一下，以下便是SQL语句的记录

数据库
英文单词：DataBase，简称：DB，是存储数据的仓库

数据库管理系统
DataBaseManagement，简称：DBMS，专门用来管理数据库中的数据的，可以对数据库进行增删改查（CRUD），常见的DBMS有MySQL、Oracle、SqlServer等

SQL
结构化查询语言，通过编写SQL语句，然后给DBMS执行，来实现数据库中数据的增删改查操作

三者的关系是：SQL（编写）–>DBMS（执行）–>DB（得到结果）（个人理解）

DQL：数据查询语言，如：select
DML：数据操作语言，如：insert、delete、update（主要作用于表中的数据）
DDL：数据定义语言，如：create、drop、alter（主要作用于表结构）
TCL：事务控制语言，如：commit、rollback
DCL：数据控制语言，如：grant、revoke

下面是数据库的基础命令了：

简单命令

导入数据

source 路径名;（比如sql文件在D盘的sqls下就是：D:\sqls\sql.sql）

创建数据库

create database 数据库名;

展示数据库（创建之后查询是否创建成功）

show databases;

使用刚才创建的数据库

use 数据库名;（刚才创建的数据库）;

查询表

show tables;//查询所创建的表

查看表结构

desc 表名;(MySQL记得加分号，分号即为结束标志)

删除表

drop table 表名;//如果删除不存在的表名会报错，所以可以在表名后加if exists，判断表是否存在

简单查询语句

select 字段名 from 表名;（注意select和from是关键字不可改，字段名和表名是标识符可以更改，所有SQL以;（分号）结尾，不区分大小写）

as关键字（给查询的字段起别名）

比如我们要查询姓名字段，原来的字段名是mingzi，利用as关键字我们可以替换成想要的字段名，例如替换成name，SQL语句为：select mingzi as name from 表名;
注意： as关键字替换只是在显示的时候替换成了name，原表字段中还是一样的字段名

条件查询

where条件查询

select 字段名 from 表名 where 查询条件
以薪资sal字段为例：
> 常见的符号有：=、<>（不等于）或!=、<、<=、>、>=
查询薪资等于800的人的名字sql语句为：select name from 表名 where sal = 800;
> between…and…
查询薪资介于400到800之间人的名字：select name from 表名 where sal between 400 and 800;
and
查询薪资介于400到800之间人的名字：select name from 表名 where sal > 400 and sal<800;
is null
查询薪资为空的人的姓名：select name from 表名 where sal is null
错误示例： select name from 表名 where sal = null
查询薪资不为空的人的姓名：select name from 表名 where sal is not null
or 或者
查询职位是teacher或者是worker的人姓名：select name from 表名 where job = ‘teacher’ or job = ‘worker’;
注意： 如果and和or同时出现，and的优先级比or高
in 包含
查询薪资是800和5000的员工姓名：select name from 表名 where sal in(800,5000);
注意： in不是范围，而且具体的数值（sal in (800,5000) 实 际等于 sal = 800 or sal = 5000）;not in 表示不等于800或者5000薪资人的姓名
like 模糊查询
查询薪资中含有数字5的员工姓名：select name from 表名 where sal like ‘%5%’;
查询薪资中第二位是数字3的员工的姓名：select name from 表名 where sal like ‘3%’;
查询姓名中带有_的需要带转义字符：‘%%’应替换成’%_%’

排序

order by

根据员工薪资升序排列：select name from order by sal；或者 select name from order by sal asc；
指定降序 ：select name from order by sal desc；
先按照员工的薪资排序，薪资一样，再按照员工的姓名排序：select name,sal from 表名 order by sal asc ,name asc;

数据处理函数

lower 转换小写

员工姓名转化小写：select lower(name) from 表名;

upper 转换大写

员工姓名转换大写：select upper(name) from 表名;

substr 截取字串

截取员工姓名长度为一的字符：select substr(name ,1 ,1) from 表名
注意： 初始下标是从1开始，长度为1

concat 字符串拼接

将员工姓名和薪资拼接：select concat(name,sal) from 表名;

length 长度计算

计算员工姓名的长度：select length(name) from 表名;

trim 去空格

去除查询中的比较字符串的空格：select * from where name = trim (’ (空格) 需要查询的条件’);

round 四舍五入

select round(1234.567,1) from 表名，结果为：1234.6保留一位小数，将数字1改为2则保留两位小数，改为-1是保留到十位，结果为：1240，改为-2是百位，结果为：1200；

rand() 生成随机数

select rand() from 表名；

ifnulll 替换null值

select ifnull(sal,0) from 表名;（如果sal为null，替换null为后面的0值进行计算，也可以替换成任意数值）

MySQL时间格式：%Y-%M-%D-%h-%i-%s
str_to_date（将字符串varchar类型转换成date类型）

语法格式：str_to_date(‘字符串日期’,‘日期格式’)
示例：insert into 表名(name,brith) values(‘张三’,str_to_date(‘01-10-1990’,’%d-%m-%Y’));
now() 函数表示获取当前时间

date_format（将date类型转换成varchar类型）

语法格式：date_format(日期类型数据，‘日期格式’)
示例：select name date_format(birth,’%Y/%m/%d’) as birth from 表名;//改变日期查询的格式，感觉有点没啥用hhh

分组函数

count 计数

select count(name) from 表名;//计算name字段有多少个

sum 求和

select sum(sal) from 表名;//计算所有薪资的总和

avg 平均值

select avg(sal) from 表名;//计算所有薪资的平均值

max 最大值

select max(sal) from 表名;//计算薪资的最大值

min 最小值

select min(sal) from 表名;//计算薪资的最小值
注意，分组函数自动忽略null，分组函数需先进行分组再进行使用（select在group by之后执行 ）

group by 分组

select job,sum(sal) from 表名 group by job;
注意： group by后面只能跟分组字段，或者分组函数，不然在MySQL中毫无意义，在Oracle中也会报错
having类似于where进行条件筛选，优先使用where;
关键字执行顺序： 1.from 2.where 3.group by 4.having 5.select 6.order by

distinct 去除重复记录

select distinct job from 表名;//去除工作岗位的重复记录

连接（连表）查询

join on 内连接（也可以是inner join … on…可读性更好）

select … from a join b on 连接条件 where 筛选条件;//表示a表和b表连接，on后面跟查询条件，比如根据a表的字段name查询b表中name对应的属性为：a.name = b.name;

join 外连接（outer join前的outer可以省略）

select … from a right join b on 连接条件;//right代表的是将右边的表看做主表，将主表的数据全部查询出来，捎带着查询左边表的数据，相对于内连接产生了主次关系；如果是left则将左边的表当作主表，查询所有数据，右表查询部分数据

多表连查

select … from a join b on a表和b表的连接条件 join c on a表和c表的连接条件 join d on a表和d表的连接条件 right join e on a表和e表的连接条件;//可以用join连接更多表进行连查

子查询 （嵌套查询）

示例：select (select…) from (select…) where (select…);//括号中的select可以是想查询的内容
比如想查询高于最低工资人的姓名：select name from 表名 where sal > (select min(sal) from 表名);
错误示例： select name from 表名 where sal > min(sal);//报错的原因是where子句中不能使用分组函数;

union 合并查询结果

查询job（职位）为teacher和worker的人的名字用union为：select name from 表名 where job = ‘teacher’ union select name from 表名 where job = ‘worker’;
union的查询效率相对于in来说要更高一些

limit （取出查询结果的一部分，用于分页查询）

完整用法（limit（startIndex，length）），startIndex表示下标从多少开始，每次取多长的长度（即100条数据，从第一条开始，每次取5条，就是二十页）
取出工资排名在【3，5】名的员工：select name from 表名 order by sal desc limit 2,3;
limit在order by之后执行
截止目前语句执行顺序：1. from 2. where 3. group by 4. having 5. select 6. order by 7. limit

insert 插入数据

格式为：insert into 表名(字段名1，字段名2，字段名3…) values(值1，值2，值3…);
注意： 字段名和值要一一对应，即数量和数据类型要对应

update 更新数据

语法格式：update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3… where 条件;
注意： 必须加条件，没有条件限制会导致所有数据全部更新

delete 删除数据

语法格式：delete from 表名 where 条件;
注意： 没有条件，会导致整张表的数据全部删除

如何快速创建一张表

create table 表二 as select name,sal from 表一;//将表一中的name，sal字段查询出来复制到表二中，如果需要全部复制，将查询的字段改为 * 号即可

快速删除表中数据（truncate）

语法格式：truncate table 表名;//这种方式可以快速删除数据，且事务回滚（rollback）时不会恢复数据，而使用delete时虽然数据删除了，但是空间没有释放，使用事务回滚可以恢复数据
注意： 使用时需要确认数据是否能全部删除，因为删除之后不能恢复

约束

什么是约束？

英文单词：constraint，在创建表的时候给字段加上一些约束，用以保证这个表中数据的完整性、有效性

约束包括哪些？

非空约束（not null）、唯一约束（unique）、主键约束（primary key）、外键约束（foreign key）、检查约束（check MySQL不支持，Oracle支持）

列级约束： 加在字段后面的为列级约束
表级约束： 字段设置完成后，在后面设置，比如：primary key（id，name）
主键分为单一主键和复合主键，主键建议使用的类型是int、bigint、char，不建议使用varchar，因为主键一般是数字，一般是定长的（auto_increment 主键自增）

存储引擎

存储引擎是一个表存储/组织数据的方式，不同的存储引擎，表存储数据的方式不同

MyISAM引擎

可被转换为压缩、只读表来节省空间
这是它的优势
不支持事务机制，安全性低

InnoDB引擎

提供一组用来记录事务性活动的日志文件
用COMMIT(提交)、SAVEPOINT及ROLLBACK（回滚）支持事务处理
提供全ACID兼容
在MySQL服务器崩溃后提供自动恢复
多版本（MVCC）和行级锁定
支持外键及引用的完整性，包括级联删除和更新
InnoDB最大的特点就是支持事务，以保证数据的安全。效率不是很高，并不能压缩，不能转换为只读，不能很好的节省存储空间

MEMORY引擎

查询效率最高，因为数据和索引在内存中，不需要和硬盘交互
不安全，关机之后数据消失

事务

什么是事务？

一个事务就是一个完整的业务逻辑
比如A账户向B账户转账一万
A账户减去一万
B账户加上一万
这就是一个完整的业务逻辑
以上操作是一个最小的工作单元，要么同时成功，要么同时失败，这样才能保证钱是正确的

事务四个特性：原子性、一致性、隔离性、持久性
A 原子性： 说明事务是最小的工作单位，不可再分
C一致性： 在同一个事务中，所有操作必须同时成功，或者同时失败
I 隔离性： A事务和B事务之间具有一定的隔离
D 持久性： 事务结束的一个保障。相当于将没有保存到硬盘上的数据保存到硬盘上

提交事务： commit
回滚事务： rollback

MySQL自动提交事务，开启事务的语句为：start transaction

事务隔离级别
读未提交（最低的隔离级别）： 事务A可以读到事务B未提交的数据，这种隔离级别会出现脏读
读已提交： 事务A只能读到事务B提交之后的数据，解决了脏读，但是不可重复读取（即每次读取到的结果不一样）
可重读读： 事务A开启之后，不管是多久，每一次在事务A中读取的数据都是一致的，即使事务B已经修改，并且已经提交了，事务A的读取依旧没有改变，但是可能会出现幻读，每一次读取到的数据都是幻象，不够真实，从开启事务开始，只要事务不结束，读到的数据都是一样的，不会改变

MySQL默认隔离级别是可重复读，Oracle默认隔离级别是读已提交

序列化/串行化（最高的隔离级别）： 最高的事务隔离级别，效率最低，解决了所有问题，这种隔离级别表示事务排队，不能并发，每一次读取到的数据都是最真实的，并且效率是最低的

索引

什么是索引？

索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制，一张表的一个字段可以添加一个索引，也可以多个字段联合起来添加索引，索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制

MySQL查询两种方式：全表扫描和根据索引检索

什么条件下可以考虑给字段添加索引？

一.数据量庞大
二.该字段经常出现在where后面
三.该字段很少出现DML（update、delete、insert）操作，因为DML之后，索引需要重新排序
注意： 建议不要随意添加索引，因为索引需要维护，太多的话反而会降低系统的性能，建议通过主键查询，或者通过unique约束的字段进行查询，效率会比较高

如何创建或者删除索引？

创建索引：create index 索引名 on 表名(添加索引的字段)
删除索引：drop index 索引名 on 表名

查看一个SQL语句是否使用了索引进行检索：explain select * from 表名 where 字段名 = ‘值’;

索引什么时候失效？

失效情况一： 示例：select * from 表名 where 字段名 like ‘%T’;
以上会失索引查询时失效，原因是模糊查询当中以”%“开头了，尽量避免模糊查询时以”%“开头，这也是一种优化的手段/策略
失效情况二： 使用or关键字时也可能会失效，要求or两边的条件都要有索引，才会走索引，如果其中一边有索引而另外一边没有索引，那么另外一个字段上的索引也会失效。
失效情况三： 建立复合索引时右边的字段查询可能失效
失效情况四： 在where当中索引参加了运算，索引失效
失效情况五： 在where当中索引列使用了函数
还有许多种等等

视图

什么是视图？

站在不同的角度看待同一数据
创建视图： create view 视图名 as select * from 表名;
删除视图： drop view 视图名;
注意： 只有DQL语句才能以view的形式创建

视图可以用来做什么？

我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致原表被操作
示例：create view 视图名 as select * from 表名;
insert into 视图名(name) values(‘李四’);
建立视图操作会导致原表的数据也会发生改变
如果有一条非常复杂的SQL语句反复使用，每使用一次都需要重新进行编写，可以将这条SQL语句以视图的方式创建，这样可以大大简化开发，且利于后期的维护，只需要修改视图对象所映射的SQL语句即可

DBA常用命令

数据导出

mysqldump 表名(后加表名可以导出表名)> 导出文件位置（如D:\表名.sql -uroot -p123456）

数据导入

1.创建数据库
2.使用数据库
3.初始化数据库：source D:\需要导入的文件

数据库设计三范式

第一范式： 要求任何一张表必须有主键，每一个字段原子性不可再分
第二范式： 建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖
第三范式： 建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖

总结表的设计
一对多： 一对多，两张表，多的表加外键
多对多： 多对多，三张表，关系表两个外键
一对一： 数据量大时需要拆表
数据库三范式时理论上的，实际需要根据用户需求设计，比如利用空间换时间，来提高操作效率

这篇文章写了好久了，一直只是一点点的堆，终于写完了，在提醒我对基础SQL知识的同时希望也能帮助到大家