常识
根据数据库的结构分为关系型数据库和非关系型数据库. 其中:
- MySQL就是关系型数据库
- Redis是非关系型数据库
关系型数据库:建立在数据紧密关系基础上,组织成行和列的类似表的结构
非关系型数据库:松散关系基础上,数据间的关系不紧密
常见数据库
- 关系型数据库: Oracle、MySQL、SQLServer、Access
- 非关系型数据库: MongoDB、Redis、Solr、ElasticSearch、Hive、HBase
数据库结构
SQL语句
由美国国家标准局规定的一种操作数据库的语言
分类
- DML(Data Manipulation Language): 数据操纵语言
- DDL(Data Definition Language): 数据定义语言
- DCL(Data Control Language): 数据控制语言
- DQL(Data Query Language): 数据查询语言
注意: SQL不区分大小写
事务
是指作为单个逻辑工作单元执行的一系列操作,要么完全地执行,要么完全地不执行。
简单的说:事务就是将一堆的SQL语句(通常是增删改操作)绑定在一起执行,要么都执行成功,要么都执行失败,即都执行成功才算成功,否则就会恢复到这堆SQL执行之前的状态。
事务的4个特性 ACID
事务是必须满足4个条件(ACID):原子性(Atomicity,或称不可分割性)、一致性(Consistency)、隔离性(Isolation,又称独立性)、持久性(Durability)。
原子性:
一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。
事务在执行过程中如果发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。
一致性:
在事务开始之前和事务结束以后,数据库的完整性没有被破坏。
这表示写入的资料必须完全符合所有的预设规则,这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性:
数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
持久性:
事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。
隔离级别
读未提交 (Read uncommitted):
安全性最差,可能发生并发数据问题,性能最好
读提交(read committed):
Oracle默认的隔离级别(快)
可重复读(repeatable read):
MySQL默认的隔离级别,安全性较好,性能一般(牺牲了效率)
串行化(Serializable):
表级锁,读写都加锁,效率低下,安全性高,不能并发
事务处理
提交 commit
-
多条语句时,批量执行,事务提交
-
有了事务,多步操作就形成了原子性操作,高并发下也不会引起数据错乱
-
mysql的事务默认就是开启的 – 多条语句一起操作时,要么一起成功要么一起失败
BEGIN; #关闭事务的自动提交
INSERT INTO user (id) VALUES(25);#成功
INSERT INTO user (id) VALUES(5);#已经存在5了,会失败
COMMIT; #手动提交事务
回滚 rollback
- 多条语句,批量执行,insert插入重复的主键导致失败时,事务回滚
BEGIN;
INSERT INTO user (id) VALUES(15);
INSERT INTO user (id) VALUES(35);#存在了
ROLLBACK;#事务回滚,就不会再提交了
常用操作
库的常用操作
建库
创建数据库,数据库名称:cbg2011
create database cbg2011 DEFAULT CHARACTER SET utf8;
删库
删除名称是cbg2011的数据库
drop database cbg2011;
查库
查看所有数据库
show databases;
使用库
使用数据库
use cbg2011;
表的常用操作
创建表
创建tb_door表,有id,door_name,tel字段
create table tb_door(
id int primary key auto_increment,
door_name varchar(100),
tel varchar(50)
);
修改表
添加列
alter table tb_door add column see NUMERIC(7,2)
删除表
删除名称是tb_door的表
drop table tb_door;
查看所有表
查看所有表
show tables;
查看表结构/设计表
查看tb_door表结构
desc tb_door;
表记录的常用操作
插入记录
向tb_door表中插入2条记录
insert into tb_door values(null,’永和大王1店’,666);
insert into tb_door values(null,’ 永和大王2店’,888);
查询记录
查询tb_door表中的所有记录
SELECT * FROM tb_door;
修改记录
修改tb_door表中id为1的记录
update tb_door set tel=555 where id=1;
删除记录
删除tb_door表中id为2的数据
Delete from tb_door where id=2;
排序
将tb_door表记录按照tel排序
Select * from tb_door order by tel desc;
记录总数
查询tb_door表中的总记录数
Select count(*) from tb_door;
数据类型
命名规则
-
字段名必须以字母开头,尽量不要使用拼音
-
长度不能超过30个字符(不同数据库,不同版本会有不同)
-
不能使用SQL的保留字,如where,order,group
-
只能使用如下字符az、AZ、0~9、$ 等
-
Oracle习惯全大写:USER_NAME,mysql习惯全小写:user_name
-
多个单词用下划线隔开,而非java语言的驼峰规则
字符
char : 长度固定,不足使用空格填充,最多容纳2000个字符,char(11)存储abc,占11位。查询速度极快但浪费空间
varchar : 变长字符串,最多容纳4000个字符,varchar(11)存储abc,只占3位。查询稍慢,但节省空间。Oracle为varchar2
大文本: 大量文字(不推荐使用,尽量使用varchar替代)
以utf8编码计算的话,一个汉字在u8下占3个字节
注:不同数据库版本长度限制可能会有不同
数字
tinyint,int : 整数类型
float,double : 小数类型
numberic(5,2) decimal(5,2) : 也可以表示小数,表示总共5位,其中可以有两位小数
decimal和numeric : 表示精确的整数数字
日期
date `包含年月日
time `时分秒
datetime `包含年月日和时分秒
timestamp `时间戳,不是日期,而是从1970年1月1日到指定日期的毫秒数
图片
blob ` 二进制数据,可以存放图片、声音,容量4g。早期有这样的设计。但其缺点非常明显,数据库庞大,备份缓慢,这些内容去备份多份价值不大。同时数据库迁移时过大,迁移时间过久。所以目前主流都不会直接存储这样的数据,而只存储其访问路径,文件则存放在磁盘上。
字段约束
主键约束
- 主键的特点是唯一且不能为空
- 通常情况下,每张表都会有主键。
- 添加主键约束,例如将id设置为主键
逐渐递增策略
- 当主键为数值类型时,为了方便维护,可以设置主键自增策略
- 设置了主键自增策略后,数据库会在表中保存一个AUTO_INCREMENT变量值,初始值为1,当需要id值,不需要我们指定值,由数据库负责从AUTO_INCREMENT获取一个id值,作为主键值插入到表中
- 而且每次用完AUTO_INCREMENT值,都会自增1. AUTO_INCREMENT=1
create table abc(
id int primary key auto_increment
);
insert into abc values(null);
insert into abc values(null);
insert into abc values(null);
select * from abc;
非空约束
- 如果为一个列添加了非空约束,那么这个列的值就不能为空,但可以重复
create table user(
id int primary key auto_increment,
password varchar(50) not null
);
show tables;
insert into user values(null,null);//不符合非空约束
insert into user values(null,'123');//OK
唯一约束
- 如果为一个列添加了唯一约束,那么这个列的值就必须是唯一的(即不能重复),但可以为空。
create table test(
id int primary key auto_increment,
username varchar(50) unique--唯一约束
);
show tables;
insert into test values(null,'lisi');
insert into test values(null,'lisi');--username的值要唯一,重复会报错的
select * from test;
外键约束
DROP TABLE IF EXISTS tb_user_address; #如果表存在则删除,慎用会丢失数据
DROP TABLE IF EXISTS tb_user; #如果表存在则删除,慎用会丢失数据
CREATE TABLE tb_user (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, #自增主键
NAME VARCHAR(50) NOT NULL UNIQUE, #非空,唯一索引
sex CHAR(2) DEFAULT '男', #默认值
phone CHAR(18),
age INT,
CHECK (age>0 AND age<=200),
createdTime DATE DEFAULT NOW()
);
CREATE TABLE tb_user_address (
user_id INT PRIMARY KEY NOT NULL,
address VARCHAR(200),
FOREIGN KEY(user_id) REFERENCES tb_user(id)
);
DESC tb_user;
- tb_user_address中user_id字段录入tb_user表不存在的主键值,将报错
默认约束
DROP TABLE IF EXISTS tb_user; #如果表存在则删除,慎用会丢失数据
CREATE TABLE tb_user (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, #自增主键
NAME VARCHAR(50) NOT NULL UNIQUE, #非空,唯一索引
sex CHAR(2) DEFAULT '男', #默认值
phone CHAR(18),
age INT,
createdTime DATE DEFAULT NOW()
);
DESC tb_user;
检查约束
- 很少使用,了解即可,录入age超过200将报错
DROP TABLE IF EXISTS tb_user; #如果表存在则删除,慎用会丢失数据
CREATE TABLE tb_user (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, #自增主键
NAME VARCHAR(50) NOT NULL UNIQUE, #非空,唯一索引
sex CHAR(2) DEFAULT '男', #默认值
phone CHAR(18),
age INT,
CHECK (age>0 AND age<=200),
createdTime DATE DEFAULT NOW()
);
DESC tb_user;
基础函数
lower
SELECT 'ABC',LOWER('ABC') from dept; --数据转小写
upper
select upper(dname) from dept --数据转大写
length
select length(dname) from dept --数据的长度
substr
SELECT dname,SUBSTR(dname,1,3) FROM dept; --截取[1,3]
concat
select dname,concat(dname,'123') X from dept --拼接数据
replace
select dname,replace(dname,'a','666') X from dept --把a字符替换成666
ifnull
select ifnull(comm,10) comm from dept2 #判断,如果comm是null,用10替换
round & ceil & floor
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
uuid
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' --数据中有单引号时,用一个\转义变成普通字符
聚合函数
- 根据一列统计结果
count
select count(*) from emp --底层优化了
select count(1) from emp --效果和*一样
select count(comm) from emp --慢,只统计非NULL的
max / min
select max(sal) from emp --求字段的最大值
select max(sal) sal,max(comm) comm from emp
select min(sal) min from emp --获取最小值
select min(sal) min,max(sal) max from emp --最小值最大值
SELECT ename,MAX(sal) FROM emp group by ename --分组
sum / avg 平均值
select count(*) from emp --总记录数
select sum(sal) from emp --求和
select avg(sal) from emp --平均数
分组
-
用于对查询的结果进行分组统计
-
group by表示分组, having 子句类似where过滤返回的结果
group by
#每个部门每个岗位的最高薪资和平均薪资,结果中的非聚合列必须出现在分组中,否则业务意义不对
SELECT deptno,MAX(sal),AVG(sal) FROM emp
GROUP BY deptno #按照deptno分组
SELECT job,MAX(sal),AVG(sal) FROM emp
GROUP BY job #按照job分组
SELECT deptno,job,MAX(sal),AVG(sal) FROM emp
GROUP BY deptno,job #deptno和job都满足的
having
#平均工资小于8000的部门
select deptno, AVG(sal) from emp
group by deptno #按部门分组
having AVG(sal) <8000 #查询条件,类似where,但是group by只能配合having
#deptno出现的次数
SELECT deptno,COUNT(deptno) FROM emp
GROUP BY deptno #按deptno分组
HAVING COUNT(deptno)>1 #次数多的
条件查询
distinct
使用distinct关键字,去除重复的记录行
SELECT loc FROM dept;
SELECT DISTINCT loc FROM dept;
where
注意:where中不能使用列别名!!
select * from emp
select * from emp where 1=1 --类似没条件
select * from emp where 1=0 --条件不成立
select * from emp where empno=100 --唯一条件
select * from emp where ename='tony' and deptno=2 --相当于两个条件的&关系
select * from emp where ename='tony' or deptno=1 --相当于两个条件的|关系
select name, sal from emp where sal=1400 or sal=1600 or sal=1800;
-- 或
select name, sal from emp where sal in(1400,1600,1800);
select name, sal from emp where sal not in(1400,1600,1800);
like
通配符%代表0到n个字符,通配符下划线_代表1个字符
select * from emp where ename like 'l%' --以l开头的
select * from emp where ename like '%a' --以a结束的
select * from emp where ename like '%a%' --中间包含a的
select * from emp where ename like 'l__' --l后面有两个字符的 _代表一个字符位置
null
select * from emp where mgr is null --过滤字段值为空的
select * from emp where mgr is not null --过滤字段值不为空的
between and
SELECT * FROM emp
select * from emp where sal>3000 and sal<10000
select * from emp where sal>=3000 and sal<=10000--等效
select * from emp where sal between 3000 and 10000--等效
limit
分数最高的记录:按分数排序后,limit n,返回前n条。Oracle做的很笨,实现繁琐,后期有介绍,而mysql做的很棒,语法简洁高效。在mysql中,通过limit进行分页查询:
select * from emp limit 2 --列出前两条
select * from emp limit 1,2 --从第二条开始,展示2条记录
select * from emp limit 0,3 --从第一条开始,展示3条记录--前三条
order by
SELECT * FROM emp order by sal #默认升序
SELECT * FROM emp order by sal desc #降序
多表联查
-
多表查询是指基于两个和两个以上的表的查询
-
在实际应用中,查询单个表可能不能满足需求
-
如显示员工表emp中不只显示deptno,还要显示部门名称
-
而部门名称dname在dept表中
-
笛卡尔积
- 就需要把两个表的数据都拼接起来
SELECT * FROM dept,emp
- 上面这种查询两个表的方式称为:笛卡尔积(Cartesian product),又称直积
子查询
select sno,degree from scores WHERE degree > 90;
select * from students where sno in (
select sno from scores where degree > 90
);
Join三种连接
-
内连接 inner join
-
左(外)连接 left join
-
右(外)连接 right join
区别:
-
INNER JOIN两边都对应有记录的才展示,其他去掉
-
LEFT JOIN左边表中的数据都出现,右边没有数据以NULL填充
-
RIGHT JOIN右边表中的数据都出现,左边没有数据以NULL填充
索引index
- 索引是一种排好序的快速查找的数据结构,它帮助数据库高效的进行数据的检索
- 在数据之外,数据库系统还维护着满足特定查找算法的数据结构(额外的存储空间),这些数据结构以某种方式指向数据
- 这样就可以在这些数据结构上实现高效的查找算法。这种数据结构就叫做索引。
- 一般来说索引本身也很大,不可能全部存储在内存中,因此往往以索引文件的形式存放在磁盘中。
- 目前大多数索引都采用BTree树方式构建。
分类
-
单值索引:一个索引只包括一个列,一个表可以有多个列
-
唯一索引:索引列的值必须唯一,但允许有空值;主键会自动创建唯一索引
-
复合索引:一个索引同时包括多列
创建索引
#查看索引,主键会自动创建索引
SHOW INDEX FROM empext
#创建索引
#CREATE INDEX empext_index ON empext (cardno)
# 创建唯一索引
ALTER TABLE empext ADD UNIQUE (phone)
ALTER TABLE empext ADD UNIQUE (cardno)
# 创建复合索引
ALTER TABLE empext ADD INDEX idx_phone_cardno (phone,cardno)
# 创建复合唯一索引
ALTER TABLE empext
ADD UNIQUE idx_phone_cardno(phone,cardno)
# 删除索引
ALTER TABLE empext DROP INDEX cardno
索引扫描类型
● ALL 全表扫描,没有优化,最慢的方式
● index 索引全扫描,其次慢的方式
● range 索引范围扫描,常用语<,<=,>=,between等操作
● ref 使用非唯一索引扫描或唯一索引前缀扫描,返回单条记录,常出现在关联查询中
● eq_ref 类似ref,区别在于使用的是唯一索引,使用主键的关联查询
● const/system 单条记录,系统会把匹配行中的其他列作为常数处理,如主键或唯一索引查询,system是const的特殊情况
● null MySQL 不访问任何表或索引,直接返回结果
从最好到最差的类型
system > const > eq_ref > ref > range > index > ALL
最左特性
EXPLAIN
SELECT * FROM empext WHERE phone='13572801415'
EXPLAIN
SELECT * FROM empext WHERE cardno='610113'
EXPLAIN
SELECT * FROM empext WHERE phone='13572801415' AND cardno='610113'
EXPLAIN
SELECT * FROM empext WHERE cardno='610113' AND phone='13572801415'
- 当我们创建一个联合索引(复合索引)的时候,如(k1,k2,k3),相当于创建了(k1)、(k1,k2)和(k1,k2,k3)三个索引,这就是最左匹配原则,也称为最左特性。
为什么索引快
- 明显查询索引表比直接查询数据表要快的多
- 首先,索引表是排序了,可以类似二分查找,非常有效的提高了查询的速度
- 其过程如下图,先到事先排序好的索引表中检索查询,找到其主键后,就直接定位到记录所在位置,然后直接返回这条数据。
优点
-
索引是数据库优化
-
表的主键会默认自动创建索引
-
大量降低数据库的IO磁盘读写成本,极大提高了检索速度
-
索引事先对数据进行了排序,降低查询数据排序的成本,降低CPU的消耗
缺点
-
索引本身也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也要占用空间
-
索引表中的内容,在业务表中都有,数据是重复的,空间是“浪费的”
-
虽然索引大大提高了查询的速度,但反向影响了增、删、改操作的效率。如表中数据变化之后,会造成索引内容不正确,需要更新索引表信息,如果数据量非常巨大,重新创建索引的时间就大大增加
-
随着业务的不断变化,之前建立的索引可能不能满足查询需求,需要消耗我们的时间去更新索引
SQL语句执行顺序
(8) SELECT (9) DISTINCT column,... 选择字段、去重
(6) AGG_FUNC(column or expression),... 聚合
(1) FROM [left_table] 选择表
(3) <join_type> JOIN <right_table> 链接
(2) ON <join_condition> 链接条件
(4) WHERE <where_condition> 条件过滤
(5) GROUP BY <group_by_list> 分组
(7) HAVING <having_condition> 分组过滤
(9) ORDER BY <order_by_list> 排序
(10) LIMIT count OFFSET count; 分页