1.数据库简介
数据库(databases),顾名思义,就是存放数据的仓库。这个仓库是按照一定的数据结构(数据结构是指数据的组织形式或数据之间的联系)来组织、存储的,我们可以通过数据库提供的多种方式来管理数据库里的数据。
- 人类在进化的过程中,创造了数字、文字、符号等来进行数据的记录,但是承受着认知能力和创造能力的提升,数据量越来越大,对于数据的记录和准确查找,成为了一个重大难题
- 计算机诞生后,数据开始在计算机中存储并计算,并设计出了数据库系统
- 数据库系统解决的问题:持久化存储,优化读写,保证数据的有效性
- 当前使用的数据库,主要分为两类
- 文档型,如sqlite,就是一个文件,通过对文件的复制完成数据库的复制
- 服务型,如mysql、postgre,数据存储在一个物理文件中,但是需要使用终端以tcp/ip协议连接,进行数据库的读写操作
比如我们要存放所有学生的学生信息,比如学号性别之类的,我们可以用一个excel。同理,老师的,课程的,都分别用一个excel。
我现在想知道学生的课程信息,考试成绩。每个老师那里分别有该科目的所有同学成绩。那要统计一位同学的则需要将所有的Excel都打开抄过来。那如果关系更复杂呢?比如统计语文高于60分,但是数学低于60分的,那不是更麻烦?如果有了数据库,用语句查询,设定条件即可。
当然,数据库还可以将数据压缩,用于大数据的运算,但是在设计初期,它最重要的功能就是查询。
2.常见的数据库
当前主流的数据库有:mysql ,sqlserver,Oracle、SQLite、Access、MS SQL Server等。
常用的是前三种,先说下这三者数据库的优缺点及适用性。
mysql:
优点: 1.支持5000万条记录的数据仓库
2.适应于所有的平台
3.是开源软件,版本更新较快
4.性能很出色。纯粹就性能而言,MySQL是相当出色的,因为它包含一个缺省桌面格式MyISAM。MyISAM数据库与磁盘非常地兼容而不占用过多的CPU和内存。MySQL可以运行于Windows系统而不会发生冲突,在UNIX或类似UNIX系统上运行则更好。你还可以通过使用64位处理器来获取额外的一些性能。因为MySQL在内部里很多时候都使用64位的整数处理。
5.价格便宜
缺点: 缺乏一些存储程序的功能,比如MyISAM引擎联支持交换功能
小型的比如自己想做个学生管理系统玩玩,就用mysql,简单轻巧。
sqlserver:
优点: 1.真正的客户机/服务器体系结构
2.图形化的用户界面,使系统管理和数据库管理更加直观、简单
3.丰富的编程接口工具,为用户进行程序设计提供了更大的选择余地
4.与WinNT完全集成,利用了NT的许多功能,如发送和接受消息,管理登录安全性等,SQL Server也可以很好地与Microsoft BackOffice产品集成。
5.有很好的伸缩性,可以跨平台使用。
6.提供数据仓库功能,这个功能只在Oracle和其他昂贵的DBMS中才有。
如果是公司需要,做个人事管理系统,可以用sqlsever。
oracle:
优点: 1.Oracle的稳定性要比Sql server好。
2.Oracle在导数据工具sqlload.exe功能比Sqlserver的Bcp功能强大,Oracle可以按照条件把文本文件数据导入.
3.Oracle的安全机制比Sql server好。
4.Sql server的易用性和友好性方面要比Oracle好。
5.在处理大数据方面Oracle会更稳定一些。
6.Sql Server在数据导出方面功能更强一些。
7.处理速度方面比Oracle快一些,和两者的协议有关.
缺点: 价格昂贵
大数据存储,比如移动公司或者银行,就可以考虑oracle。
数据库模型:E-R模型
- 当前物理的数据库都是按照E-R模型进行设计的
- E表示entry,实体
- R表示relationship,关系
- 一个实体转换为数据库中的一个表
- 关系描述两个实体之间的对应规则,包括
- 一对一
- 一对多
- 多对多
- 关系转换为数据库表中的一个列 *在关系型数据库中一行就是一个对象
数据库三范式:
目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。满足最低要求的范式是第一范式(1NF)。在第一范式的基础上进一步满足更多规范要求的称为第二范式(2NF),其余范式以次类推。一般说来,数据库只需满足第三范式(3NF)就行了。所以这里就只记录三范式相关的知识。
三范式
1NF:字段不可分;
2NF:有主键,非主键字段依赖主键;
3NF:非主键字段不能相互依赖;
解释:
1NF:原子性 字段不可再分,否则就不是关系数据库;
2NF:唯一性 一个表只说明一个事物;
3NF:每列都与主键有直接关系,不存在传递依赖;
数据完整性:
- 一个数据库就是一个完整的业务单元,可以包含多张表,数据被存储在表中
- 在表中为了更加准确的存储数据,保证数据的正确有效,可以在创建表的时候,为表添加一些强制性的验证,包括数据字段的类型、约束
字段类型:
- 在mysql中包含的数据类型很多,这里主要列出来常用的几种
- 数字:int,decimal
- 字符串:varchar,text
- 日期:datetime
- 布尔:bit
约束:
- 主键primary key
- 非空not null
- 惟一unique
- 默认default
- 外键foreign key
数据库基本操作:
1.显示数据库 show databases
2.系统自带的数据库:
mysql:用户权限的相关数据
test:用于用户测试数据
infromation:mysql本身结构的相关数据
3.创建数据库
create database 数据库名;
4.使用(切换)数据库:
use 数据库名
5.查看当前选择的数据库:
select database()
查看版本:select version();
显示当前时间:select now():
表操作:
1.查看当前数据库中的所有表:
show tables;
2.创建新表:
create table 表名(字段名及其数据类型,属性,约束,注释等)
如:
create table student(
id int not null primary,
sname varchar(10) not null
);
是否可以为空:
null 表示空内容,不是字符串
not null 表示内容不能为空
默认值:在创建表时,可以给该字段设置默认属性,如果插入数据时没有主动设置数据,则显示默认值
主键自增长:如果主键列为自增列,插入数据时无需主动设置数据,但需要占位,通常使用0,插入成功后以实际数据为准
外键:用于表与表之间的关联
3.删除表
drop table 表名;
4.清空表
delete from 表名
truncate table 表名
5.修改表
添加列:alter table 表名 add 列名 数据类型等;
删除列:alter table 表名 drop 字段名;
修改表名:alter table 原表名 rename 新表名;
修改字段:alter table 表名 change 原表名 新表名 数据类型等;
添加主键:alter table 表名 add constraint 主键名 primary key 主键字段
删除主键:alter table 表名 drop primary key;
添加外键:alter table 表名 add constraint 外键名 foreign key 外键字段 references 关联表名(关联字段)
删除外键:alter table 表名 foreign key 外键名;
关联的主表字段必须是主键,每张表只能有一个主键,可有多个外键
基本数据类型:
数值类型:
TINYINT 非常小的数据 1字节 0 ~255
SMALLINT 较小的数据 2字节 0 ~ 65535
MEDIUMINT 中等大小的数据 3字节 0 ~ 16777215
INT 标准整数 4字节 0 ~ 4294967295
BIGINT 较大的整数 8字节 0 ~ 18446744073709551615
FLOAT 单精度浮点数 4字节
DOUBLE 双精度浮点数 8字节
DECIMAL 字符串形式的浮点数 M+2个字节
字符串类型:
CHAR(M) 固定长字符串 M字节 一般用于固定的如:身份证号 手机号等
VARCHAR(M) 可变字符串 长度可变
TINYTEXT 微型文本串 0 ~ 2**8-1 字节
TEXT 文本串 0 ~ 2**16-1字节
日期类型:
DATE YYY-MM-DD(年月日) 1000-01-01 ~ 9999-12-31
DATETIME YY-MM-DD(年月日,时分秒) 1000-01-00:00:00 ~ 9999-12-31 23:59:59
TMESTAMP YYYMMMDDHHMMSS(年月日,时分秒) 1970某事某刻 ~ 2038某时某刻,精度为一秒。
YEAR YYY (年份) 1901 ~ 2155
5.表内容操作
a.添加数据:
全列插入:insert into 表名 values(值1,值2...)
缺省插入:insert into 表名 (列1,列2...)values(值1,值2...)
一次插入多条数据:insert into 表名 values(值1,值2...),(值1,值2...)........;
注意: 1,列名是可选的,如省略则依次插入所有字段。 2,多个列表和多个值之间使用逗号相隔。 3,值列表和字段名列表一一对应。 4,如插入的是表中部分数据,字段名列表必填。
b.删除数据
delete from 表名[where 条件](如果不写条件,会删除表的所有内容,慎用)
c.修改数据
update 表名 set 列1=值1,.... [where 条件] (如果不写条件,会修改整列的数据,慎用)
d.查询
语法:select * from 表名;
- from关键字后面写表名,表示数据来源于是这张表
- select后面写表中的列名,如果是*表示在结果中显示表中所有列
- 在select后面的列名部分,可以使用as为列起别名,这个别名出现在结果集中
- 如果要查询多个列,之间使用逗号分隔
消除重复行:
在select后面,列的前面使用distinct可以消除重复的行 select distinct gender from student;
条件:
使用where语句对表中的数据进行筛选,结果为true的会显示在结果集中 select * from 表名 where 条件
比较运算符:
等于=
大于>
小于<
大于等于>=
小于等于<=
不等于!=或<>
逻辑运算符:
and 并且
or 或者
not 非
模糊查询:
关键字 like
%表示任意多个字符
_表示一个任意字符
如查询姓张的学生
select * from studnet where sname like'张%';
范围查询:
in表示在一个非连续范围内查询
select * from student where id in (1,3,5)
between...and..在连续的范围内查询
select * from student where id between 1 and 5
空判断:
注意:null 和'' ''是不同的
判断空:is null
判断非空:is not null
优先级:
小括号,not ,比较运算符,逻辑运算符
and比or先运算,如果同时出现若希望先运算or则添加小括号即可
聚合函数:
count()返回字段的行数 括号中写*和列名,结果是相同的
max()返回字段的最大值
min()返回字段的最小值
sum()返回字段的数据和
avg()返回字段的平均值
分组:
按照字段分组,表示此字段相同的数据会被放到一个组中
分组后,只能查询出相同的数据列,对于有差异的数据无法显示在结果集中
可以对分组后的数据进行统计,做聚合运算
语法:select 列1,列2...聚合函数 from 表名 group by 列1,列2...
如对学生表按性别分组,查询男女生各有多少人
select sex count(*) from student group by sex;
分组后的数据筛选:
语法:select 列1,列2...聚合函数 from 表名 group by 列1,列2...having 列1,....聚合函数
如查询男人数:
1.select count(*) from student where sex='男'
2.select sex,count(*) from student group by sex having sex='男'
where和having的区别:
where是对from后面指定的表进行数据筛选,属于原始数据的筛选
having是对分组以后生成的结果集进行数据筛选
排序:
为了方便查看数据,可以对数据进行排序
语法:
select * from 表名 order by 列1 asc/desc ,列2 asc/desc.....
将表中的数据按照列1进行排序,如果列1某些值相同时,则按照列2进行排序,一次类推
默认按照列值由小到大排序
asc是升序
desc是降序
如查询男生的学生信息,按学号降序排列
select * from student where sex='男' order by id desc;
分页:
当数据量过大时,在一页中查看数据很麻烦,可以分页查看数据
语法:select * from 表名 limit start,count;
start表示从哪个位置开始,count表示获取多少条数据
start索引从0开始
已知:每页显示m条数据,当前显示第n页
如:
查询总条数d1
d1//m=0得到总页数p1
d1//m!=0总页数为p1+1
select * from student limit (n-1)*m,m
字符串函数:
拼接字符串:concat(str1,str2...)
select concat ('my','s','ql') >>> 'mysql'
包含字符个数length(str)
select length('mysql') >>>5
截取字符串
left(str,len)返回字符串str最左端len个字符
right(str,len)返回字符串str最右端len个字符
substring(str.pos,len)返回字符串str的位置pos起len个字符
select substring('abc123',2,3) >>>'bc1'
去除空格:
ltrim(str) 返回删除了左空格的字符串str
rtrim(str)返回删除了右空格的字符串str
替换字符串
replace(str,str1,str2)
select replace('abc123',abc,'def') >>>'abcdef'
大小写转换:
lower(str)转小写
upper(str)转大写
数学函数:
求绝对值:
abs(n)
select abs(n)
求m除以n的余数mod(m,n),同运算符%
floor(n)表示不大于n的最大整数
ceiling(n)
表示不小于n的最大整数
求四舍五入值round(n,d),n表示原数,d表示小数位置,默认为0
select round(1.6)
求x的y次幂
pow(x,y)
select pow(2,3)
日期时间函数:
- year(date)返回date的年份(范围在1000到9999)
- month(date)返回date中的月份数值
- day(date)返回date中的日期数值
- hour(time)返回time的小时数(范围是0到23)
- minute(time)返回time的分钟数(范围是0到59)
- second(time)返回time的秒数(范围是0到59)
日期格式化date_format(date,format),format参数可用的值如下:
-
-
获取年%Y,返回4位的整数
* 获取年%y,返回2位的整数
* 获取月%m,值为1-12的整数
-
获取日%d,返回整数
* 获取时%H,值为0-23的整数
* 获取时%h,值为1-12的整数
* 获取分%i,值为0-59的整数
* 获取秒%s,值为0-59的整数
-
当前时间:current_date()
select current_date()