2-1.数据库的基础

含义

储存和管理数据的仓库
数据库分为关系型数据库和非关系型数据库
sql语言分为
DML 数据库操纵语言
DDL 数据库定义语言
DCL 数据库控制语言
DQL 数据库查询语言

数据库操作

show databases    查看数据库
use 数据库名		使用数据库
create database 数据库名 default character utf8     创建数据库
drop database 数据库名  	删除数据库

表操作

show tables  查看表
desc 表名  查看表的结构
drop table 表名  删除表
create table 表名(字段名 类型(范围), ...)    创建表
alert table 表名 add column 字段名 类型(范围)  修改表

数据操作

insert into 表名 values(数据值)    添加数据
delete from 表名 where 限定条件    删除表
select * from 表名    查询数据
update 表名 set 字段名=修改值

字段名:多个单词用下划线隔开

约束

主键约束:primary key    auto_increment   自增
非空约束:not null
唯一约束:unique
外键约束:foreign key 子表字段 references 父表名(字段名)
检查约束check(条件1and条件2....)
默认约束:default

基本函数

select 'ABC',lower('ABC') from dept;  数据转小写
select upper(dname) from dept --数据转大写
select length(dname) from dept --数据的长度
SELECT dname,SUBSTR(dname,1,3) FROM dept; --截取[1,3]
select dname,concat(dname,'123') X from dept --拼接数据
select dname,replace(dname,'a','666') X from dept 
--把a字符替换成666
select ifnull(comm,10) comm from dept2 
#判断,如果comm是null,用10替换
//round四舍五入，ceil向上取整，floor向下取整
–直接四舍五入取整
select comm,round(comm) from emp
–四舍五入并保留一位小数
select comm,round(comm,1) from emp
–ceil向上取整,floor向下取整
select comm,ceil(comm) ,floor(comm) from emp

SELECT UUID()返回uuid：
a08528ca-741c-11ea-a9a1-005056c00001
//now
select now() -- 年与日 时分秒
select curdate() --年与日
select curtime() --时分秒
year & month & day
–hour()时 		minute()分 		second()秒
select now(),hour(now()),minute(now()),second(now()) from emp ;
–year()年		 month()月 		 day()日
select now(),year(now()),month(now()),day(now()) from emp ;

转义字符
'作为sql语句符号，内容中出现单撇就会乱套，进行转义即可
select ‘ab’cd’ – 单引号是一个SQL语句的特殊字符
select ‘ab’cd’ --数据中有单引号时,用一个\转义变成普通字符

条件查询

distinct

使用distinct关键字，去除重复的记录行

SELECT loc FROM dept;
SELECT DISTINCT loc FROM dept;

where

where条件不能用聚合函数
where后用=/in()连接字段名  多个条件用and/or

like

模糊查询   '%精' 以精结尾    '%精%' 包含精	'精%' 以精开头

null

is null / is not null

between

between 数值1 and 数值2     取数值1~数值2之间

limit

limit 1,3  从第二条开始,取3条
limit 2 从第一条开始,取2条

order by

排序默认升序    添加desc降序

聚合函数

sum()求和
avg()平均
count()个数 查全部1比*更高效
max()最大值
min()最小值

分组group

group by 分组
having相当于where,先分组后筛选
where不能使用聚合函数当条件,当having可以

事务 transaction

是指作为单个逻辑工作单元执行的一系列操作，
要么完全地执行，要么完全地不执行。

特性ACID

· 原子性
· 一致性
· 隔离性
· 持久性

隔离级别

读取未提交    速度最快,安全性最差
读取提交  oracle默认使用
可重复读取   mysql默认使用
串行化 	表级锁,效率低下,安全性高,不能并发

语法

begin;   开始
commit  提交
rollback 回滚

index 索引

分类

单值索引：一个索引只包括一个列，一个表可以有多个列
唯一索引：索引列的值必须唯一，但允许有空值；
		 主键会自动创建唯一索引
复合索引：一个索引同时包括多列

查看索引

show index from dept;

创建索引

主键会自动创建索引

//create index 索引名字 on 表名(字段名); #创建索引
create index loc_index on dept(loc); #创建索引
//修改表结构,添加普通索引
alter table dept add index loc_index(loc)
//创建唯一索引
alter table dept add unique(loc) 
//创建唯一索引--索引列的值必须唯一
//创建复合索引
alter table dept add index fuhe_index(dname,loc);
//创建复合唯一索引
alter table dept add unique fuhe_index(dname,loc);
//删除索引
alter table dept drop index fuhe_index

explain查询索引的标签
排序，tree结构，类似二分查找
索引表小

索引扫描类型

type：
ALL 全表扫描，没有优化，最慢的方式
index 索引全扫描，其次慢的方式
range 索引范围扫描，常用语<，<=，>=，between等操作
ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询，system是const的特殊情况
null MySQL不访问任何表或索引，直接返回结果

最左特性

explain
select * from dept where loc='二区' #使用了loc索引
explain
select * from dept where dname='研发部'#使用了dname索引
explain
select * from dept where dname='研发部' and loc='二区' 
#使用了dname索引

优点:

索引是数据库优化
表的主键会默认自动创建索引
每个字段都可以被索引
大量降低数据库的IO磁盘读写成本，极大提高了检索速度
索引事先对数据进行了排序，大大提高了查询效率

缺点:

索引本身也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间
索引表中的内容，在业务表中都有，数据是重复的，空间是“浪费的”
虽然索引大大提高了查询的速度，但对数据的增、删、改的操作需要更新索引表信息，如果数据量非常巨大，更新效率就很慢,因为更新表时,MySQL不仅要保存数据,也要保存一下索引文件
随着业务的不断变化，之前建立的索引可能不能满足查询需求，需要消耗我们的时间去更新索引

多表联查

笛卡尔积

select * from 表名1,表名2 where 限定条件

表连接

select * from 表名1 join 表名2 on 限定条件 

子查询

**select * from 表名 where id=(select id from 表名 where name=限定值)**