类型
- 关系型数据库, Oracle、MySQL、SQLServer、Access
- 非关系型数据库, MongoDB、Redis、Solr、ElasticSearch、Hive、HBase
SQL语句
定义
结构化查询语言(Structured Query Language)简称SQL(发音:/ˈes kjuː ˈel/ "S-Q-L"),是一种特殊目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统;同时也是数据库脚本文件的扩展名。
SQL 是1986年10 月由美国国家标准局(ANSI)通过的数据库语言美国标准,接着,国际标准化组织(ISO)颁布了SQL正式国际标准。
分类
DML(Data Manipulation Language)数据操纵语言
如:insert,delete,update,select(插入、删除、修改、检索)简称CRUD操新增Create、查询Retrieve、修改Update、删除Delete
DDL(Data Definition Language)数据库定义语言
如:create table之类
DCL(Data Control Language)数据库控制语言
如:grant、deny、revoke等,只有管理员才有相应的权限
DQL(Data Query Language)数据库查询语言
如: select 语法
注意:SQL不区分大小写
dos窗口的命令:
创建库:create database a(库名) default character set utf8;
展示库;show databases;
删除库:drop database a(库名);
使用库:use a(库名);
创建表:create table a( id int primary key auto_increment, door_name varchar(100), tel varchar(50) );
增数据;insert into;
查数据:select * from a(表名);(使用频次最高,其他不轻易使用,关于查询有很多优化);
删数据:delete from a(表名) where(条件) (字段);
改数据;update a(表名) set (字段);
字符:
char长度固定,不足使用空格填充,最多容纳2000个字符,char(11)存储abc,占11位。查询速度极快但浪费空间
varchar变长字符串,最多容纳4000个字符,varchar(11)存储abc,只占3位。查询稍慢,但节省空间。Oracle为varchar2
大文本: 大量文字(不推荐使用,尽量使用varchar替代)
数字
- tinyint,int整数类型
- float,double小数类型
- numeric(5,2) decimal(5,2)—也可以表示小数,表示总共5位,其中可以有两位小数
- decimal和numeric表示精确的整数数字
日期
- date 包含年月日
- time时分秒
- datetime包含年月日和时分秒
- timestamp时间戳,不是日期,而是从1970年1月1日到指定日期的毫秒数
图片
blob 二进制数据,可以存放图片、声音,容量4g。早期有这样的设计。但其缺点非常明显,数据库庞大,备份缓慢,这些内容去备份多份价值不大。同时数据库迁移时过大,迁移时间过久。所以目前主流都不会直接存储这样的数据,而只存储其访问路径,文件则存放在磁盘上。
约束
主键约束:如果为一个列添加了主键约束,那么这个列就是主键,主键的特点是唯一且不能为空。通常情况下,每张表都会有主键
非空约束:如果为一个列添加了非空约束,那么这个列的值就不能为空,但可以重复。
唯一约束:如果为一个列添加了唯一约束,那么这个列的值就必须是唯一的(即不能重复),但可以为空。
基础函数
lower:SELECT 'ABC',LOWER('ABC') 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') 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:向下取整
now:select now() -- 年与日 时分秒 select curdate() --年与日 select curtime() --时分秒
year & month & day:select now(),hour(now()),minute(now()),second(now()) from emp ;
条件查询
distinct:SELECT loc FROM dept; SELECT DISTINCT loc FROM dept;
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:
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 #降序
聚合 aggregation
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:
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:
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 #次数多的
事务4个特性ACID
1,原子性: 多条SQL是一个密不可分的整体
2,一致性: 分布式系统里,数据的一致性
3,隔离性: 数据库支持高并发,使用了锁的机制保证了数据的安全
4,持久性: 是指对数据的增删改是持久生效的
隔离级别:
1,read uncommitted:读未提交, 安全性最差,但是效率高
2,read committed:读已提交, 安全性较好,但是效率较差,也是Oracle的默认级别
3,repeatable read:可重复读,安全性适中,但是效率一般,也是MySQL的默认级别
4,serializable:串行化,安全性最高,但是效率太差
MySQL已经为我们提供了事务管理,默认是一条SQL一个事务,如果想要自己管理事务必须有下面的步骤:
1,开启事务:start transaction;
2,执行SQL:增删改的SQL
3,结束事务:commit提交
表关联 association
- 一对一 one to one QQ和QQ邮箱,员工和员工编号
- 一对多 one to many 最常见,部门和员工,用户和订单
- 多对一 many to one 一对多反过来,员工和部门,订单和用户
- 多对多 many to many 老师和学生,老师和课程
多表联查 join
- 内连接 inner join
- 左(外)连接 left join
- 右(外)连接 right join
索引 index
- 单值索引:一个索引只包括一个列,一个表可以有多个列
- 唯一索引:索引列的值必须唯一,但允许有空值;主键会自动创建唯一索引
- 复合索引:一个索引同时包括多列