MySQL数据库

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()