目录
数据库应用
概念
什么是数据库
简而言之,就是存储数据,管理数据的仓库。
常见的数据库分为:
- 关系型数据库, Oracle、MySQL、SQLServer、Access
- 非关系型数据库, MongoDB、Redis、Solr、ElasticSearch、Hive、HBase
关系型和非关系型
早期发展的数据库建立在数据的紧密关系基础之上(如:父子关系、师生关系),我们称其为关系型数据库,也称为传统数据库;现今数据库建立在数据的松散关系基础之上(如:中国人和美国人、中国人和印度人、视频、音频),我们称其为非关系型数据库nosql(not only sql)。业界总在争论nosql能否干掉传统数据库,很多初学者也有这个困惑。以我来看,两者没有矛盾,它们各有特点,根据业务情况互补才是真谛。但总的来说原来关系型数据库一统天下的格局早被打破,领土不断被蚕食,规模一再的缩小,虽然无法全面被替代,但却早已风光不在,沦落到一偶之地,Oracle的衰落就是最好的证明,早期只要是全球大企业无一例外都是部署Oracle,但现在都在去Oracle化,阿里就已经全面排斥Oracle。
既然干不掉,很多传统项目的还是围绕关系型数据库的居多,所以我们先来学习关系型数据库,目前最流行的关系型数据库是MySQL。
关系型数据库
关系型数据库有特定的组织方式,其以行和列的形式存储数据,以便于用户理解。关系型数据库这一系列的行和列被称为表,一组表组成了数据库。用户通过查询来检索数据库中的数据,而查询是一个用于限定数据库中某些区域的执行代码。关系模型可以简单理解为二维表格模型,而一个关系型数据库就是由二维表及其之间的关系组成的一个数据集合。
Mysql数据库
- mysql服务端,它来处理具体数据维护,保存磁盘
- mysql客户端,CRUD新增,修改,删除,查询
MySQL数据存放在哪里?
在MySQL的配置文件my.ini中会进行默认配置
MySQL服务端
mysql-5.5.27-winx64.msi
Mysql数据库默认的编码是latin1等价于iso-8859-1,修改为utf-8
注意:配置完,mysql开始执行,最后一步出错有时仍可以使用,使用SQLyog工具测试,如不行,再执行安装程序,选择remove,删除,然后重新安装。同时注意必须是管理员权限。
MySQL客户端1:DOS窗口
mysql -uroot -proot
语法:mysql.exe执行文件
代表参数
-u 用户名,紧接着写的
-p 密码,紧接着写的
MySQL客户端2:可视化工具
数据库的结构
数据库结构
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等,只有管理员才有相应的权限
注意:SQL不区分大小写
数据库常用操作
建库
- 创建数据库,数据库名称:cbg2011
create database cbg2011 DEFAULT CHARACTER SET utf8;
- 1
删库
- 删除名称是cbg2011的数据库
drop database cbg2011;
- 1
查看所有数据库
- 查看所有数据库
show databases;
使用数据库:use cbg2011;
门店表:** tb_door**
订单详情表:** tb_order_detail**
创建表
- 创建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 money NUMERIC(7,2)
- 1
删除表
- 删除名称是tb_door的表
drop table tb_door;
- 1
查看所有表
- 查看所有表
show tables;
- 1
查看表结构/设计表
- 查看tb_door表结构
desc tb_door;
- 1
表记录的常用操作
插入记录
- 向tb_door表中插入2条记录
insert into tb_door values(null,'永和大王1店',666);
insert into tb_door values(null,' 永和大王2店',888);
- 1
- 2
- 3
查询记录
- 查询tb_door表中的所有记录
SELECT * FROM tb_door;
- 1
修改记录
- 修改tb_door表中id为1的记录
update tb_door set tel=555 where id=1;
- 1
删除记录
- 删除tb_door表中id为2的数据
Delete from tb_door where id=2;
- 1
排序
- 将tb_door表记录按照tel排序
Select * from tb_door order by tel desc;
- 1
记录总数
- 查询tb_door表中的总记录数
Select count(*) from tb_door;
- 1
数据类型
- 字段名必须以字母开头,尽量不要使用拼音
- 长度不能超过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。早期有这样的设计。但其缺点非常明显,数据库庞大,备份缓慢,这些内容去备份多份价值不大。同时数据库迁移时过大,迁移时间过久。所以目前主流都不会直接存储这样的数据,而只存储其访问路径,文件则存放在磁盘上。
准备数据
字段名称 | 数据类型 | 是否为空 | 备注 |
---|---|---|---|
deptno | int | 部门编号,PK主键 | |
dname | varchar(20) | Y | 部门名称 |
loc | varchar(13) | Y | 部门所在地点 |
CREATE TABLE dept(
deptno int primary key auto_increment ,
dname VARCHAR(20),
loc VARCHAR(13)
);
INSERT INTO dept VALUES(null,'accounting','一区');
INSERT INTO dept VALUES(null,'research','二区');
INSERT INTO dept VALUES(null,'operations','二区');
字段名称 | 数据类型 | 是否为空 | 备注 |
---|---|---|---|
empno | int | 员工编号,PK主键 | |
ename | varchar(10) | Y | 员工名称 |
job | varchar(10) | Y | 职位 |
mgr | int | Y | 上级编号 |
hiredate | datetime | Y | 入职时间 |
sal | double | Y | 月工资 |
comm | NUMERIC(8,2) | Y | 奖金 |
deptno | int | Y | 所属部门 FK外键 |
Mysql:
CREATE TABLE emp(
empno int primary key auto_increment,
ename VARCHAR(10),
job VARCHAR(10),
mgr int,
hiredate DATE,
sal double,
comm NUMERIC(7,2),
deptno int
);
INSERT INTO emp VALUES(100,'jack','副总',NULL,'2002-05-1',90000,NULL,1);
INSERT INTO emp VALUES(200,'tony','总监',100,'2015-02-02',10000,2000,2);
INSERT INTO emp VALUES(300,'hana','经理',200,'2017-02-02',8000,1000,2);
INSERT INTO emp VALUES(400,'leo','员工',300,'2019-02-22',3000,200.12,2);
INSERT INTO emp VALUES(500,'liu','员工',300,'2019-03-19',3500,200.58,2);
字段约束
主键约束:如果为一个列添加了主键约束,那么这个列就是主键,主键的特点是唯一且不能为空。通常情况下,每张表都会有主键。
添加主键约束,例如将id设置为主键:
主键自增策略** **当主键为数值类型时,为了方便维护,可以设置主键自增策略(auto_increment),设置了主键自增策略后,数据库会在表中保存一个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;
非空约束:如果为一个列添加了非空约束,那么这个列的值就不能为空,但可以重复。
添加非空约束,例如为password添加非空约束:
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
唯一约束:如果为一个列添加了唯一约束,那么这个列的值就必须是唯一的(即不能重复),但可以为空。
添加唯一约束,例如为username添加唯一约束及非空约束:
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;
基础函数
lower
SELECT 'ABC',LOWER('ABC') from dept; --数据转小写
- 1
upper
select upper(dname) from dept --数据转大写
- 1
length
select length(dname) from dept --数据的长度
- 1
substr
SELECT dname,SUBSTR(dname,1,3) FROM dept; --截取[1,3]
- 1
concat
select dname,concat(dname,'123') X from dept --拼接数据
- 1
replace
select dname,replace(dname,'a','666') X from dept --把a字符替换成666
- 1
ifnull
select ifnull(comm,10) comm from dept2 #判断,如果comm是null,用10替换
- 1
round & ceil & floor
round四舍五入,ceil向上取整,floor向下取整
–直接四舍五入取整
select comm,round(comm) from emp
- 1
–四舍五入并保留一位小数
select comm,round(comm,1) from emp
- 1
–ceil向上取整,floor向下取整
select comm,ceil(comm) ,floor(comm) from emp
- 1
uuid
SELECT UUID()
返回uuid:a08528ca-741c-11ea-a9a1-005056c00001
- 1
now
select now() -- 年与日 时分秒
select curdate() --年与日
select curtime() --时分秒
year & month & day
–hour()时 minute()分 second()秒
select now(),hour(now()),minute(now()),second(now()) from emp ;
- 1
–year()年 month()月 day()日
select now(),year(now()),month(now()),day(now()) from emp ;
- 1
转义字符
'作为sql语句符号,内容中出现单撇就会乱套,进行转义即可
select 'ab'cd' -- 单引号是一个SQL语句的特殊字符
select 'ab\'cd' --数据中有单引号时,用一个\转义变成普通字符
条件查询
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 #降序
统计案例
#2015年以前入职的老员工
#2019年以后签约的员工,日期进行格式转换后方便比较
SELECT * FROM emp WHERE YEAR(DATE_FORMAT(hiredate,'%Y-%m-%d'))<=2019;
- 1
#2015年到2019年入职的员工
SELECT * FROM emp
WHERE
STR\_TO\_DATE(hiredate,'%Y-%m-%d')\>='2015-01-01'
AND
STR\_TO\_DATE(hiredate,'%Y-%m-%d')\<='2019-12-31'
公司福利不错13薪,年底双薪,统计员工的年薪=sal*13+comm*13
SELECT empno,ename,job,sal\*13+comm\*13 FROM emp;
SELECT empno,ename,job,sal\*13+comm\*13 **as** **年薪** FROM emp;--用as给列起个别名
SELECT empno,ename,job,sal\*13+comm\*13 年薪 FROM emp; --as也可以省略
select ename, sal+comm from emp
select ename, sal , comm, sal+ifnull(comm,0) from emp--用0替换掉null
聚合 aggregation
根据一列统计结果
count
select count(\*) from emp --底层优化了
select count(1) from emp --效果和\*一样
select count(comm) from emp --慢,只统计非NULL的
- 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
用于对查询的结果进行分组统计
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 #次数多的
## 小结
### char和varchar有什么区别?
char为定长字符串,char(n),n最大为255
varchar为不定长字符串,varchar(n),n最大长度为65535
char(10)和varchar(10)存储abc,那它们有什么差别呢?
char保存10个字符,abc三个,其它会用空格补齐;而varchar只用abc三个位置。
### datetime和timestamp有什么区别?
数据库字段提供对日期类型的支持,是所有数据类型中最麻烦的一个,慢慢使用就会体会出来。
date 是 年与日
time是 时分秒
datetime年月日时分秒,存储和显示是一样的
timestamp时间戳,存储的不是个日期,而是从1970年1月1日到指定日期的毫秒数
### 中文乱码
如果在dos命令下执行insert插入中文数据,数据又乱码,那现在sqlYog客户端执行下面命令:
set names utf8;
set names gbk;
设置客户端字符集和服务器端相同。如果不知道它到底用的什么编码?怎么办呢?很简单,两个都尝试下,哪个最后操作完成,查询数据库不乱码,就用哪个。
那为何会造成乱码呢?
Mysql数据库默认字符集是lantin1,也就是以后网页中遇到的ISO8859-1,它是英文字符集,不支持存放中文。我们创建库时,可以指定字符集:
create database yhdb charset utf8;
但这样很容易造成服务器和客户端编码集不同,如服务器端utf8,客户端ISO8859-1。mysql和客户端工具都有习惯的默认编码设置,好几个地方,要都统一才可以保证不乱码。
我们只要保证创建数据库时用utf8,使用可视化工具一般就基本正确。
### 注释
/\* 很多注释内容 \*/
\#行注释内容
\-- 行注释内容,这个使用较多
### 主键、外键、唯一索引的区别?
- Primary Key 主键约束,自动创建唯一索引
- Foreign Key 外键约束,外键字段的内容是引用另一表的字段内容,不能瞎写
- Unique Index 唯一索引,唯一值但不是主键
对于约束的好处时,数据库会进行检查,违反约束会报错,操作失败。数据库提供了丰富的约束检查,还有其他约束,但现今弱化关系型数据库的前提下,基本已经很少使用,记住上面三个即可。
### drop、delete和truncate之间的区别?
drop删除库或者表,数据和结构定义
delete和truncate只是删除表的数据
delete可以指定where条件,删除满足条件的记录,tuncate删除所有记录
对于自增字段的表,delete不会自增值清零,而truncate是把表记录和定义都删除了,然后重建表的定义,所以自增主键会重头开始计数