数据库 - MySql - 《编程开发》

基础入门
数据保存的方式
数据保存到内存中
优点，读写速度快
缺点，程序关闭数据丢失
数据保存在文件中，
优点：永远保存在硬盘中
缺点：频繁的IO操作效率不高，查询不方便
数据保存在数据库软件中
优点：
1，数据永远保存
2，数据处理方便，查询快
1.1、数据库和数据库软件
数据库（Database，简称DB）：是按照数据结构来存储、组织和管理数据的仓库
数据库软件：（DataBase Management System,简称DBMS）是指数据库系统中对数据进行管理的软件系统。
关系型数据库
1，Oracle当前最流行应用最广泛的数据库软件，兼容Java良好，适合中大型企业
2，SQLServler，是微软公司的产品，兼容C#，net
3，DB2；IBM公司的数据库，I
4，MySQL：开源组织的产品，被甲骨文收购，和java兼容良好，适合中小型应用
学习步骤：先学mysql——Oracle
非关系型数据库
字段格式：字段名称字段类型
1.2 MYSQL
开源的关系型数据库系统，MysqlAB公司开发，Oracle公司所有 | 思维导图 | | | —- | —- |
1.3、安装MySQL数据
1，安装包需要放在全英文的目录下
mysql存储结构：先有数据库，再有表，再有数据
mysql客户端工具：管理数据库，管理表，管理数据
1.4、mysql的结构目录：
bin、include、lib、share、my
注：mysql如果乱码问题问题，可以更改my.ini的默认编码成utf-8；
1.5、Mysql服务的启动和关闭：
第一种方式：手动启动服务
第二种：命令行
1.5.1、MySql登陆时的命令参数：
1.5.2、Mysql退出时候的命令
1.6、Mysql的常用命令
1.6.1、Mysladmin，更改密码
1.6.2、niw
2、Mysql的基本类型
2.1、什么是数据类型
定义：数据类型是指数据列、存储过程参数、表达式、局部变量的数据特征、数据类型决定了数据的存储格式，表达了不用的数据类型.
2.2、整型
2.3、浮点数类型和定点数类型
Mysql用浮点数类型和定点数类型表示小数
1，FLOAT（M，D）,M表示小数点前后的位数之和，D表示小数点后（右边）的位数之和，M>=D；
2，浮点数类型存储近似值，定点数存储高精度值，一般需要计算的值会使用DECIMAL
2.4、日期时间型和字符型
2.4.1、日期时间型
2.4.2、字符型
字符串类型用于存储字符串，除了存储字符串类型，还可以存储图片和声音的二进制数据
CHAR(M)是定长类型，如果输入的字节不足，系统会用0填补
2.5
SQL语言
SQL语句的分类
DDL：数据定义语言
create/drop/alter
DML：数据操作语言
insert/delete/update/truncate
SQL：数据查询语言
select/show
MySQL的自带数据库
数据库的操作（Demo为数据库）
1. 查询所有的数据库
show databases；
1. 创建一个数据库，规定字符集，如果不存在则新建数据。
create database Demo
character set 字符集类型名称；
1. 创建一个数据库，不需要规定字符集，如果不存在则新建数据。
create database Demo；
1. 删除数据库
drop database Demo；
1. 查看数据库默认字符集
show create database Demo ；
1. 修改数据库默认字符集
alter database Demo default character set gbk；
1. 修改数据库名称（to可省略）
alter table Demo rename to teacher
1. 进入数据库
use 数据库名
数据库表
数据库表是数据库组成的部分之一，是其他对象的基础，其中行称为记录，列标题称为字段
表的操作
1. 对表的操作需要先进入某个数据库中，（use Demo进入数据库）
2. 新建一个表，字段格式：字段名称字段类型
create table student(
sid int,
sname varchar(20),
sage int
)；
注意：多个字段使用逗号分隔，数据类型的小括号表示容量，新建表至少要有一个字段
1. 向表中新增字段（column可以省略）
alter table student add column servlet int,add jsp int;
[FIRST | AFTER]可以自定义新增列的位置
[约束条件]，可以设定约束条件
插入多个字段用【,】隔开，以ADD的格式。
注：增加的字段需要加上类型
1. 向表中部分字段插入数据
  1. 插入行数据
    insert into student(sid,sname) values(2,’李四’);
  2. 插入多行数据
    insert into student(sid,snname) values(2,’李四’),(3,张三)….;
2. 向表中所有字段插入数据，（内容依次按顺序插入，不能少或者多，否则报错）
insert into student values(1,’张三’,4)
1. 向表中插入查询结果
Insert into tabele_name1(column_list1) select(column_list2) from table_name2 where(condito);
1. 删除某个表/对个表
drop table student,表2;
如果指定IF EXISTS 表不存在不会报错
1. 删除某个字段（column可以省略）不需要写类型
alter table student drop column sgender;
1. 删除（清空）表中所有数据（）
delete from student；
1. 可以带条件删除。
2. 只能删除表的数据不能删除约束
3. 可以回滚（事务）
truncate table student；
1. 不可以带条件删除
2. 能删除表的数据和约束
3. 不可以回滚
4. 删除符合条件的数据
delete from student where sid=2;
1. 修改表的名称
rename table table1 to table2
1. 修改表的字段类型（数据名称数据类型）
alter table student modify column sname varchar(100)
1. 修改字段的相对位置
1. 修改表的字段名称（旧名称新名称旧类型）如果类型写错了，就相当于把名称和类型一起改了
alter table student change column sname rname varchar(100);
1. 修改表中某字段的所有值，字段所有值都被修改
update student set sage=5;++
1. 修改表中符合条件的数据
update student set sage=6 where sid=2;
1. 修改表中符合条件的多个字段的数据
update student set sage=4, sname=’小李’ where sid=1;
1. 查询所有表
show tables；
1. 查询指定数据库中的所有表
show tables from Database;
1. 查看某个表的详细信息
desc student;
1. 查询数据表的创建语句
show create table tbl_name;
查询表中的数据
1. 查询表中的所有的数据
select * from student
1. 查询指定列
select sid,sname from student;
1. 查询时指定别名（as）
select sid AS ‘编号’,sname AS ‘姓名’ from student AS s;
1. as指定别名，表的别名不能用字符串
2. 在多表查询时候经常使用
3. 进入数据库
use 数据库名
1. 查询时添加常量列
select sid,sname,sage,’Java’ AS ‘语言’ from student;
1. java为常量列。查询时直接添加查询结果后面，可以加别名（可选）
2. LIMIT关键字
limit关键字用于限制查询结果的数量，使用LIMIT关键字可以返回指定位置的记录
语法格式：
例：
不同种类的查询方式：
条件查询、聚合函数查询、模糊查询、分页查询、排序查询、分组查询、分组筛选查询
合并查询，
查询时合并列
select sid,sname,(servlet+jsp) from student;
1. 查询servley和jsp的总成绩。
2. 只能合并数值类型的字段
重复查询
查询时去除重复的数据（distinct关键字）
select distinct sname from student;
select distinct(sname) from student;
条件查询
逻辑条件：and（与），or（或），当有多个条件肯定有逻辑关系
1. 需求：id=3，且名字为王五的学生
select * from student where sid=3 and sname=’王五’；
1. 需求：id=3，或名字为王五的学生
select * from student where sid=3 or sname=’王五’；
1. 需求：查询成绩大于75的学生
select * from student where servlet>70;
比较条件：> < >= <= = <>(不等于) between and（等价于>= 且<=）
1. 查询
判空条件：null空字符串，is null/is not null/=’’/<>’’
1. 空字符串同于null
2. 需求；查询servlet为空的字符串(包含null和空字符串)
select * from student where selvet is null or servlet=’’ ；
1. 需求：查询有地址的学生
select * from student where adress is not null and address<>’’;
模糊查询：关键字like
1. 替换标记
  1. %：可以替换任意字符
  2. _：表示一个字符
2. 查询姓赵的学生
select * from student where sname like ‘赵%’
1. 查询姓赵的学生且只有两个字的学生
select * from student where sname like ‘赵_’
聚合函数（使用聚合函数的查询）
常用的聚合函数：sum()，avg()，max()，min()，count() | sum() | 求和函数 | | —- | —- | | avg() | 平均数 | | max() | 最大值 | | min() | 最小值 | | count() | 计数（个数）,会排除null,尽量似使用不含null之的字段，一般
使用ID或者 * |
1. 查询学生servlet的总成绩，
select sum(servlet) AS ‘Serlvet的总成绩’ from student;
1. 统计所有人数
select count(*) from demo.student;（计算每个列的数据个数选出最多的）
分页查询
关键字：limit
语法：limit 起始行，查询几行
注意：第一行为0，
分页：当前页面。每页显示多少条2
1. 需求：只想查询出第一和第二条的记录（第一页的数据）
语句：select * from student limit 0，2
1. 需求：只想查询出第三和第四条的记录（第二页的数据）
语句：select * from student limit 2，2
需求一和需求二相当于按了第一页和第二页，从第一页按了下一页调到下一页
排序查询
默认情况下按照插叙记录的顺序显示
关键字：order by 字段 asc/desc
asc：顺序，正序。数值：递增，字母：自然顺序（a-z）
desc：倒序，反序。数值：递减，字母：自然反序（a-z）
1. 需求：按照id顺序排序（）
select * from student order by id ASC
1. 需求：先按照servlet正序排列，再根据jsp倒序排列
select * from student order by servlet ASC，jsp DESC
注意：
1. 不写排序方式默认是asc
2. 当排序条件相同时，就按照按照插入顺序
分组查询
关键字：group by
需求：把学生按照性别分组（group by gender）统计每组人数
select gender，count（*） from student group by gender；
分组查询后筛选
需求：查询总人数大于2的性别
分析：
1，查询男女的人数
2，筛选出人数大于2的记录（having）
select gender，count（）from student group by having （）>2’=
注意：分组之前条件使用where。分组之后用having

数据约束（constraint）
数据约束定义：对用户操作表的数据进行约束
包括：默认值，非空，唯一、主键，自增长，外键，六个类型的约束
为已经存在的字段添加约束：
ALTER TABLE table_name ADD CONSTRAINT table_id 主键关键字
默认值
关键字：default
用户对使用默认值的字段不插入字段时候使用默认值
默认值允许为空（手动输入null）
创建表时候添加约束：

修改表的时候添加约束

删除约束

非空值
关键字：not null
必须赋值，不能插入null

唯一
关键字：unique
添加唯一约束:

1、对null值不起作用，只对字段有值起作用，即声明的字段允许有空值

主键
定义：Primary Key Constraint,要求主键列的数据唯一且不能为空值，主键能够唯一地标识表中的一条记录
特征：非空+唯一
单主键
关键字：PRIMARY KEY

修改表时候添加默认约束：

复合主键
也称对字段主键，由多个字段联合组成

主键一般会设置自增长
通常每个表都会有一个主键，用于标记表中每行数据的唯一性
建议不要选择表的包含业务的字段做为主键，建议为每个表设置一个非业务含义的主键
注：把已经存在的字段设置为主键：
ALTER TABLE table_name ADD CONSTRAINT table_id PRIMARY KRY (table_id);
即：alter table orders add constraint 主键的新名字 primary key (要改成主键的列的名字);
删除主键：
ALTER TABLE bookinfo DROP PRIMARY KEY;

自增长
作用：自动地递增，不适合
效果：不重复，自动增长
关键字：auto_increment
1、一般会把表的主键设置为自增长，其关键字主键关键字之后
2、INT关键字，可以定在数值自增长的数字位数
设置一个自增长从0001开始的数，
Id INT(4) ZEROFILL PRIMARY KEY AUTO_INCREMENT;
3、删除全部数据时DELETE不影响自增长，数据会从上次增加的数值开始。TRUNCATE可以影响子自增长。
4、可以用auto_increment=初始值，的形式指定初始值

外键
作用：约束两张表的数据，用于解决两张两关联表之间的冗余情况，保持数据的一致性和完整性

关键字：CONSTRAINT emloyee_dept_fkFOREIGN KEY(deptId) REFERENCES dept(id);
外键名称：emloyee_dept_fk
外键字段：deptId
参考字段：id
注意：外键字段和参考字段的数据类型必须相同，数据长度可以不同

创建表时添加外键：

修改表时添加外键：

为已经存在的字段添加外键：
语法:alter table 表名 add constraint FK_ID foreign key(你的外键字段名) REFERENCES 外表表名(对应的表的主键字段名);
注：
1，解决数据冗余
2，被约束的表称为副标
3，约束别人的表叫做主表，外键设置在副表。
4，主表的参考字段通常为1；
5，当有了外键约束，应该先添加主表，再添加副表
6，当有了外键约束，应该先修改/删除副表，却定没有与主表相关联的数据，再改/删主表
级联操作
定义：从父表删除或者更新且自动删除或者更新子表中匹配的行
作用：对外键的主表参考值字段进行修改时，副表会有相应的更新。
关键字：ON UPDATE CASCADE
可以加在外键关键字之后，
1，修改：主表修改后参考字段，副表会做出相应的更新；
2，删除：主表删除参考字段后，副表对应的所有数据会被删除

运算符与函数
常用的运算符：
算术运算符、比较运算符、逻辑运算符
算术运算符

比较运算符

逻辑运算符

函数
定义:表示对输入参数值返回一个具有特定关系的值
常用函数：包括：

数值函数： 定义：主要处理数值数据的
1. 获取整数的函数

四舍五入的函数

截断函数

取模函数

字符函数
1. 字符串连接函数

大小写转换函数

求字符串函数

删除空格函数

截取字符串

获取指定字符串的长度

替换函数

格式化函数

日期和时间函数
1. 获取当前日期的函数

获取当前的日期和时间

执行日期的计算

计算两个日期的间隔

日期格式化

聚合函数（分组函数）

信息函数

加密函数

网站一般用MD5，加密不可逆
子查询
定义：子查询是指嵌套在其他SQL语句内的查询语句

在运行DQL时会先运行子查询的语句
子查询的结果会作为作为外层另一个查询的过滤条件
子查询可以添加到：select、update、delete等语句中
带比较运算符的子查询

用any、some、all关键字修饰子查询

使用IN、not in 关键字子查询

IN，与=any实现同样的效果

NOT IN

eixsts关键字

插入记录时使用子查询
INSERT INTO … SELECT

关联查询
关联查询，也称为多表查询，为保证效率，多表连接最好不超过3张表

规则：
1、确定查询那些表
2、确定查询哪些字段
3、明确表与表之间链接条件（外键约束）一般链接数量至少是表数量减一
类型：
1、交叉连接查询
selectsname,name from student,classname;
会产生笛卡尔乘积效应不推荐
2，内连接查询
定义：根据条件在多个表中查询选择数据，显示这些表中与连接条件相匹配的数据行，组合成新纪录

语法：

步骤：
1、确定查询那些表
2、确定查询哪些字段
3、明确表与表之间链接条件（外键约束）一般链接数量至少是表数量减一
4、内连接显示的结果只有满足条件的才会显示
表达方式一：
SELECT sname,name
FROM student,classname
WHERE student.classID=classname.id;
表达方式二：
SELECT sname AS ‘姓名’,name AS ‘班级’
FROM student AS s
INNER JOINclassname AS c
ON s.classID=c.id;
注：AS可以省略
3，左外连接查询

定义使用左边的数据匹配右边的数据，左边是主表
左边表的书会完全显示，没有匹配右表显示null
SELECT sname,name
FROM classname c
LEFT OUTER JOIN student s
ON s.classID=c.id;
其中，student表做为左边（主表）
显示结果：

4，右外连查询
定义与左连接相反
5，自连接

多表更新

多表删除

自定义函数
创建自定义函数
delimiter $
create function 函数名([参数列表]) returns 数据类型begin sql语句; return 值;End $;
Delimiter ；
一般以$做为开始符

函数体

创建有参数的函数

例子：

创建自定义函数

加上if exists可以更新
流程的控制
变量

为变量赋值

流程控制语句

IF条件判断语句

例子：

CASE条件判断语句

格式一：

格式二：

例子：

WHILE循环语句

例子

LOOP循环语句

例子：

REPEAT循环语句

例子：

存储过程
概念：存储过程是SQL语句和流程控制语句的预编译集合，并以一个名称存储并作为一个单元进行处理。
储存在数据中，由应用程序执行，允许声明变量和进行流程控制。
存储过程具有效率高预编译的特点，一般经常使用的操作语句会设置成存储过程。
语法结构：

过程体:

参数:

调用存储过程

无参数的存储过程

有参的存储过程-带有输入和输出参数

输入输出参数，输入参数也是输出的参数

删除储存过程

创建复合结构的存储过程

存储过程与函数的区别

数据库设计
数据库设计
业务模型/实体模型——数据模型（存储在硬盘）

原则上数据库表的设计，按照三大范式
数据库的三大范式
第一范式：要求每个表的字段是不可再分割的字段，
第二范式：在第一范式的基础上，要求每张表只表达一个意思，表的每个字段都和主键有依赖
第三范式：在第二范式的基础上，要求每张表的主键之外的其他字段都和主键有直接决定依赖关系

数据库编程，
DELIMITER $
CRATE PRODURCE pro_test(IN sid INT)
SELECT

Mysql备份和权限
备份语句：
mysqldump -uroot -p day17 > c:/bak.sql
注：1、使用管理员账号备份
2、>符号表示备份到什么路径，
3、执行备份命令的时候需要退出数据库（不需要登陆）