笔记1

sql: 专门操作数据库的语言  （数据库只认识sql）

三大系统：操作系统  文件系统  数据库系统


什么是数据库？ 就是一个文件系统，通过标准SQL语言操作文件系统中数据 ---- 用来存放软件系统的数据    存数据  管理数据
什么是关系型数据库 ？ 保存关系数据模型     文件与文件有关系   最常用的  慢   安全
对象数据库：db2（算是一个对象数据库）

Oracle 甲骨文公司，专门数据库厂商 收购BEA 、SUN、MySQL ------- 收费 大型数据库 ，用于任何系统任何平台 
MySQL 早期开源免费数据库产品，LAMP组合 Linux + Apache + MySQL + PHP 完全开源免费 ，自从mysql被oracle收购后，从6.0开始出现收费版本
DB2 IBM数据库产品 大型收费数据库 websphere服务器一起使用 
SYBASE 中等规模数据库 收费 PowerDesigner 数据库建模工具 
SQL Server 微软公司数据库产品 收费中等规模数据库 ，操作系统要求是windows 结合.net 一起使用 

Java开发者主要使用 MySQL 、Oracle、DB2 三种数据库

=================================================================================================
MySQL 安装配置   如果没有特殊要求   直接下一步 下一步安装即可！
1、卸载
在mysql安装目录 my.ini 
datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/"
1) 在控制面板中卸载MySQL 
2) 删除mysql 安装目录
3) 删除MySQL数据文件目录 

2、安装
选择自定义安装
MySQL Server 默认位置 c:program filesmysql目录
Server Data File 数据文件 C:Documents and SettingsAll UsersApplication DataMySQL 目录

安装后进行mysql配置
1) 配置mysql 默认字符集 
默认latin1 等价于 ISO-8859-1 改为 utf8
2) Include Bin Directory in Window Path 将mysql/bin 目录配置环境变量path ---- 勾选
3) 输入超级管理root 密码 

测试mysql是否安装成功 启动cmd窗口 输入 mysql -u root -p 回车 输入密码 123 ==== 出现 mysql> 安装成功

3、重置root密码
1) 停止mysql服务器 运行输入services.msc 停止mysql服务
2) 在cmd下 输入 mysqld --skip-grant-tables 启动服务器 光标不动 （不要关闭该窗口）
3) 新打开cmd 输入mysql -u root -p 不需要密码 
use mysql;
update user set password=password('abc') WHERE User='root';
4) 关闭两个cmd窗口 在任务管理器结束mysqld 进程 
5) 在服务管理页面 重启mysql 服务
密码修改完成 

========================================================================================================
MySQL 服务器内部存储结果
一个数据库服务器可以创建多个数据库
一个数据库可以创建多张数据表 
每张数据表 用来保存 数据记录   （简单认为 文件 存放数据，数据库看成文件夹）

SQL语句 结构化查询语句        表达式
特点：非过程性 一条SQL语句一个执行结果 
为了使用SQL 编写复杂程序，各个数据库厂商对SQL 进行增强，SQL Server TSQL 、Oracle PLSQL 

SQL语句分类 按功能（定义、操纵、控制、查询）分类
DDL 数据定义语言，定义表、库、视图
DML 对数据表记录 增加、修改和删除操作
DCL 授权、事务控制、条件判断 
DQL (不是W3C组织 给出分类) 数据表记录查询 
* 知道SQL语句四个分类，能够识别SQL语言属于哪个分类 

SQL语句学习流程 ： 数据库操作语句 ----- 数据表结构操作语句 ---- 数据表记录操作语句 

一、数据库操作语句
1、创建数据库 会为每个软件系统创建单独数据库
语法 ： create database 数据库名称 ;  (创建数据库采用数据库服务器默认字符集 )
复杂写法 create database 数据库名称 character set 字符集 collate 比较规则 ; 

创建一个名称为mydb1的数据库。 create database mydb1;
创建一个使用utf8字符集的mydb2数据库。 create database mydb2 character set utf8;
创建一个使用utf8字符集，并带校对规则的mydb3数据库。create database mydb3 character set utf8 collate utf8_bin;

补充：每次创建一个数据库在 数据存放目录中生成一个文件夹 ， 每个文件夹中存在 db.opt 存放默认字符集和校对规则 
datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/"

2、查询数据库 
show databases; ----- 查看所有数据库 
show create database 数据库名; ------ 查看数据编码集

3、删除数据库
语法 ：drop database 数据库名称; 

查看当前数据库服务器中的所有数据库 show databases;
查看前面创建的mydb2数据库的定义信息 show create database mydb2;
删除前面创建的mydb1数据库 drop database mydb1;

4、修改数据库编码集 
语法：alter database 数据库名称 character set 字符集 collate 比较规则; 

修改mydb2字符集为gbk;  alter database mydb2 character set gbk;

切换当前使用数据库： use 数据库名称
查看当前正在使用数据库: select database();

==================================================== 所有数据库相关操作语句都属于DDL 语句
二、数据表结构SQL语句 
1、数据表的创建 
语法：create table 表名(列名 类型(长度),列名 类型(长度)... );
一个数据表 可以存在很多列，每列具有类型和长度 
* 创建表时没有指定 字符集，将采用数据库默认字符集 
* 创建表之前 必须使用use db 语法指定操作数据库 

创建day12 数据库 create database day12;
切换到day12 数据库 use day12; 

例如： 
User {
   id   int                              
   name string
   password string
   birthday date    
}
一个java类 对应数据库中一张数据表，一个java对象 对应数据表中一条数据记录 

MySQL 常用数据类型
java 中 String char  -----  mysql 中字符串型 char varchar  ''
* char是定长 varchar是变长 
例如：char(8) 保存 lisi，因为lisi 只有四个字符，所有会补充四个空格，成为8个字符存入 char(8)中 ，如果有 varchar(8) 自动根据存放内容改变长度

java 中 byte short int long float double ----- mysql 中数值类型 TINYINT 、SMALLINT、INT、BIGINT、FLOAT、DOUBLE

java 中 boolean ---- mysql 逻辑性 bit 存放一位数值 0 或者1 

java 中 Date ----- mysql 日期类型 date (只有日期) time(只有时间) datetime(日期时间都有) timestamp(日期时间都有 )
* datetime和timestamp 表现形式上完全相同，区别就在于timestamp 在数据库可以自定更新（当前时间）

java中 大数据类型 inputStream 二进制文件 Reader 文本文件 ------- mysql 大数据类型 blob(存放大二进制数据) text(存放大的文本文件)
* tinyblob tinytext 255字节 blob text 64KB mediumblob mediumtext 16MB  longblob longtext 4GB

例如： 
User {
   id   int    ------ mysql int                        
   name string  ------ mysql varchar
   password string  ----- mysql varchar
   birthday date    ----- mysql date 
}

员工表建表语句
create table employee (
   id int,
   name varchar(20),
   gender varchar(20),
   birthday date,
   entry_date date,
   job varchar(30),
   salary double,
   resume longtext
);

查看表结构 desc 表名; 

*** 创建数据表时，只有字符串类型必须写长度，而其他类型都有默认长度  

2、单表创建时约束
约束用来保证数据有效性和完整性 
主键约束 primary key : 信息记录某个字段可以唯一区分其他信息记录，这个字段就可以是主键 （唯一 非空）
唯一约束 unique : 该字段的值不允许重复  可以null
* 一张表中可以有很多个唯一约束，只能有一个(两个)作为主键约束 
非空约束 not null ：该字段的值不能为空 

create table employee2 (
   id int primary key auto_increment,
   name varchar(20) unique not null,
   gender varchar(20) not null,
   birthday date not null,
   entry_date date not null,
   job varchar(30) not null,
   salary double not null,
   resume longtext
);
***列级约束

* 如果主键约束类型为 数值型 int bigint ，添加auto_increment 自动增长 

3、数据表结构修改
1) 增加列 语法: alter table 表名 add 列名 类型(长度) 约束; 
2) 修改现有列类型、长度和约束 语法：alter table 表名 modify 列名 类型(长度) 约束;
3) 修改现有列名称 语法：alter table 表名 change 旧列名 新列名 类型(长度) 约束;
4) 删除现有列 语法：alter table 表名 drop 列名 ;
5) 修改表名 rename table 旧表名 to 新表名; 

* 修改表的字符集：alter table student character set utf8;

在上面员工表的基本上增加一个image列。---- alter table employee add image varchar(100) ;
修改job列，使其长度为60。 ----alter table employee modify job varchar(60) not null;
删除gender列。 ----alter table employee drop gender ;
表名改为user。 ----rename table employee to user;
修改表的字符集为utf8 ---- alter table user character set utf8;
列名name修改为username  ----- alter table user change name username varchar(20) unique not null;

查看当前数据库内所有表：show tables 
查看当前数据表字符集 ： show create table user;

4、数据表删除 
语法：drop table 表名; 

5、查看数据表结构
desc 表名; 查看表结构
show tables ; 查看当前库内所有表名
show create table 表名; 查看建表语句和字符集 

==================================================================== 所有数据表结构操作语句都是 DDL 
三、数据表中数据记录的增删改查操作    ---dml
1、向数据表插入记录 
语法：insert into 表名(列名,列名,列名...) values(值,值,值...);  为数据表的每列进行赋值 

注意事项
1) 插入值 类型必须和 列类型匹配
2) 值长度不能超过 列定义长度
3) 值的顺序和 列顺序对应 
4) 字符串和日期型值 必须写 单引号
5) 插入空值 可以写 null    

新建employee表，插入三个员工信息
insert into employee(id,name,gender,birthday,entry_date,job,salary,resume) values(1,'zhangsan','male','1990-10-10','2010-01-01','sales',4000,'good boy !');

语法2: 省略所有列名，但是后面值必须要和表中所有列进行匹配，按照表中列顺序 
insert into employee values(2,'lisi','male','1988-10-01','2008-08-17','hr',3500,'good hr !');

语法3：省略可以为空，有默认值部分列名，后面值要和前面列进行匹配    不为空的且没有默认值一定要插入
insert into employee(id,name,job,salary) values(3,'wangwu','boss',20000);

* 在插入记录后 ，通过select * from employee; 查看所有员工信息 

插入一条中文记录
insert into employee(id,name,job,salary) values(4,'小明','清洁员',1500);
出错了：
ERROR 1366 (HY000): Incorrect string value: 'xC3xF7' for column 'name' at row 1 ; 
错误原因：mysql client 采用默认字符集编码 gbk 

查看系统所有字符集 ： show variables like 'character%';

解决办法：修改客户端字符集为gbk 
MYSQL中共有6个地方字符集 ：client connetion result 和客户端相关 、database server system 和服务器端相关 

第一种：当前窗口临时修改 set names gbk ;
* 只对当前窗口有效，关闭后就会失效

第二种：配置mysql/my.ini 文件
[mysql] 客户端配置
[mysqld] 服务器端配置 

修改客户端字符集 [mysql] 后字符集  default-character-set=gbk

使用mysql -u root -p 密码连入数据库后，如果进行数据库操作，直接操作，如果要进行数据表结构和数据记录操作，必须先切换到操作的数据库 use db;

2、数据记录更改操作
语法: update 表名 set 列名=值,列名=值.... where条件语句; 
* 如果没有where条件语句，默认修改所有行数据 

将所有员工薪水修改为5000元。 ----- update employee set salary = 5000;
将姓名为’zhangsan’的员工薪水修改为3000元。 -------  update employee set salary = 3000 where name='zhangsan' ;
将姓名为’lisi’的员工薪水修改为4000元,job改为ccc。 ------- update employee set salary=4000, job='ccc' where name='lisi';
将wangwu的薪水在原有基础上增加1000元。    ------------ update employee set salary = salary+1000 where name ='wangwu';

3、数据记录的删除操作
语法：delete from 表名 where条件语句 ;
* 如果没有where语句，将删除表中 所有记录

如果要删除表中所有数据记录，使用 truncate table 表名; 等价于 delete from 表名; 

试题：如果使用delete删除表中所有记录和使用truncate table删除表中所有记录 有何不同 ？
truncate 删除数据，过程先将整个表删除，再重新创建 
delete 删除数据，逐行删除记录 
* truncate 效率要好于 delete 
truncate 属于DDL ，delete 属于DML ======== 事务管理只能对DML 有效 ，被事务管理SQL语句可以回滚到SQL执行前状态 

删除表中名称为’zhangsan’的记录。 ------ delete from employee where name='zhangsan';  
删除表中所有记录。----- delete from employee; (可以事务回滚)
使用truncate删除表中记录。 ---- truncate table employee;

====================================================================================== insert update delete 属于DML 语句
4、数据表记录的查询 
语法一 ： select [distinct] * | 列名,列名... from 表名; 
select * from 表名; 查询该表中所有列信息   *通配符
select 列名,列名... from 表名;  查询表中指定列的信息 
distinct 用于排重 

create table exam(
   id int primary key auto_increment,
   name varchar(20) not null,
   chinese double,
   math double,
   english double
); 

insert into exam values(null,'关羽',85,76,70);
insert into exam values(null,'张飞',70,75,70);
insert into exam values(null,'赵云',90,65,95);

查询表中所有学生的信息。 ---------  select * from exam; 
查询表中所有学生的姓名和对应的英语成绩。 ----- select name,english from exam;
过滤表中重复数据 (查询英语成绩，排除完全相同重复数据) ---- select distinct english from exam; 

语法二：select 表达式(列名执行运算) from 表名;
select 列名 as 别名 from 表名; 

在所有学生分数上加10分特长分。 ---- select name,chinese+10,math+10,english+10 from exam;
统计每个学生的总分。------- select name,chinese+math+english from exam;
使用别名表示学生分数。----- select name,chinese+math+english as 总分 from exam;

***  在对列起别名时，as可以省略 select name,chinese+math+english as 总分 from exam; ------ select name,chinese+math+english 总分 from exam;
select name,math from exam; 查询name和math两列的值
select name math from exam; 查询name列值，起别名math

语法三: select 列名 from 表名 where条件语句

查询姓名为关羽的学生成绩 -------select * from exam where name='关羽'; 
查询英语成绩大于90分的同学 ----- select * from exam where english > 90;
查询总分大于200分的所有同学 ----- select * from exam where chinese+math+english > 200;

运算符 
1) 相等= 不等 <>  
2) between  ...and... 在两者之间取值 between 70 and 80 等价于 >=70 <=80  ----- 注意前面那个数要比后面那个数要小 
3) in(值,值,值) 在指定值中任取一个 in(70,80,90) 值可以是70、80或者90 
4) like '模糊查询pattern' 进行模糊查询 ，表达式有两个占位符 % 任意字符串 _ 任意单个字符 例如： name like '张%' 所有姓张学员  
name like '张_' 所有姓张名字为两个字学员 
5) is null 判断该列值为空

6) and 逻辑与 or 逻辑或 not 逻辑非 

查询英语分数在 90－100之间的同学。 -------- select * from exam where english>=90 and english <= 100;  select * from exam where english between 90 and 100;
查询数学分数为65,75,85的同学。 ---- select * from exam where math in(65,75,85); 
查询所有姓赵的学生成绩。---- select * from exam where name like '赵%';
查询英语分>80，语文分>80的同学。 ---- select * from exam where english > 80 and chinese > 80;

insert into exam values(null,'刘备',null,55,38);
查询语文没有成绩学员 select * from exam where chinese is null;
查询语文有成绩学员 select * from exam where chinese is not null;


语法四： select * from 表名 order by 列名 asc|desc ;  ---- asc升序 desc降序 
对数学成绩排序后输出。----------- select * from exam order by math; 默认asc升序 
对总分排序按从高到低（降序）的顺序输出  ------------ select * from exam order by math+chinese+english desc;
对学生成绩按照英语进行降序排序，英语相同学员按照数学降序 ------------- select * from exam order by english desc,math desc;

聚集函数 指SQL语句中内置函数 ---------- 分组函数（用于统计）      **** 自动排null
1) count 统计查询结果记录条数 select count(*)|count(列名) from 表名; 

统计一个班级共有多少学生？ ------------ select count(*) from exam; 
统计英语成绩大于90的学生有多少个？ ------- select count(*) from exam where english > 90;
统计总分大于220的人数有多少？ --------select count(*) from exam where chinese+math+english > 220;

2) sum 统计某一列数据的和  select sum(列名) from 表名; 

统计一个班级数学总成绩？ ----- select sum(math) from exam;
统计一个班级语文、英语、数学各科的总成绩 ---- select sum(chinese),sum(math),sum(english) from exam;
统计一个班级语文、英语、数学的成绩总和 select sum(chinese+math+english) from exam;  
select sum(chinese)+sum(math)+sum(english) from exam;
**** 刘备语文null ，null进行所有运算 结果都是null 
select sum(chinese)+sum(math)+sum(english) from exam; 含有刘备英语和数学成绩
select sum(chinese+math+english) from exam;  不含刘备英语和数学成绩 
* 使用ifnull函数处理 null情况 
select sum(ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0)) from exam;  含有刘备英语和数学成绩

统计一个班级语文成绩平均分  ------ select sum(chinese)/count(*) from exam; 

3) avg 统计某一列平均值 select avg(列名) from 表名;
求一个班级数学平均分？ ---- select avg(math) from exam;
求一个班级总分平均分？---- select avg(ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0)) from exam; 
select ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0) from exam; 

4) max 统计一列最大值 min 统计一列最小值 
求班级最高分和最低分（数值范围在统计中特别有用）
select max(chinese+math+english) a ,min(ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0)) i from exam; 

语法五：select 分组函数 from exam group by 列名; 按照某列进行分组统计 ***一组可能有多条数据
分组操作，就是具有相同数据记录分到一组中，便于统计 

create table orders(
    id int,
    product varchar(20),
    price float
);

insert into orders(id,product,price) values(1,'电视',900);
insert into orders(id,product,price) values(2,'洗衣机',100);
insert into orders(id,product,price) values(3,'洗衣粉',90);
insert into orders(id,product,price) values(4,'桔子',9);
insert into orders(id,product,price) values(5,'洗衣粉',90);

练习：对订单表中商品归类后，显示每一类商品的总价 ---- 需要按照商品名称进行分组
select product,sum(price) from orders group by product;     select * from orders group by product;错误！！！
在group by 语句后面 添加having 条件语句 ---- 对分组查询结果进行过滤 
练习：查询购买了几类商品，并且每类总价大于100的商品
select product,sum(price) from orders group by product having sum(price) > 100;

试题：where 和 having 条件语句的区别 ？ 
where 是在分组前进行条件过滤，having 是在分组后进行条件过滤 
使用where地方都可以用 having替换 ， 但是having可以使用分组函数，而where后不可以用分组函数 

小结 select 语句 ： S-F-W-G-H-O 组合 select ... from ... where ... group by... having... order by ... ; 
顺序不能改变 
解析顺序 ： from  - where -  group by - having - select - order by 

=================================================================================================================
MySQL 数据库的备份和恢复
1、备份命令 mysql/bin/mysqldump 将数据库SQL语句导出 
语法：mysqldump -u 用户名 -p 数据库名 > 磁盘SQL文件路径 

例如： 备份day12数据库 --- c:day12.sql 
cmd > mysqldump -u root -p day12 > c:day12.sql 回车输入密码 

INSERT INTO `exam` VALUES (1,'关羽',85,76,70),(2,'张飞',70,75,70),(3,'赵云',90,65,95),(4,'刘备',NULL,55,38);

2、恢复命令 mysql/bin/mysql 将sql文件导入到数据库 
语法： mysql -u 用户名 -p 数据库名 < 磁盘SQL文件路径
***** 导入SQL 必须手动创建数据库 ，SQL不会创建数据库 

例如：将c:day12.sql 导入 day12数据库
cmd > mysql -u root -p day12 < c:day12.sql 回车密码 

补充知识：恢复SQL也可以在数据库内部执行 source c:day12.sql 


多表设计（外键约束）
create table emp (
  id int primary key auto_increment,
  name varchar(20),
  job varchar(20),
  salary double
);

insert into emp values(null,'小丽','人力资源',4500);
insert into emp values(null,'小张','Java工程师',5000);
insert into emp values(null,'老李','财务经理',8000);
insert into emp values(null,'小刘','项目经理',10000);

create table dept(
   id int primary key auto_increment,
   name varchar(20)
);

insert into dept values(null,'人力资源部');
insert into dept values(null,'财务部');
insert into dept values(null,'技术研发部');

让员工表和部门表发生关系，知道员工属于哪个部门 ，在员工表添加部门id字段 
alter table emp add dept_id int ;
update emp set dept_id = 1 where name = '小丽';
update emp set dept_id = 2 where name = '老李';
update emp set dept_id = 3 where name = '小刘';
update emp set dept_id = 3 where name = '小张';

公司因为财政问题，解散技术研发部 delete from dept where name ='技术研发部'; ----- 小张和小刘 失去了组织 

emp表 中dept_id字段 引用 dept表 id 字段 ------- 添加外键约束 （保证数据有效和完整性）
将emp表中dept_id 设置为外键约束 alter table emp add foreign key(dept_id) references dept(id) ;
无法删除技术研发部 ，因为小刘和小张信息 依赖技术研发部 记录 ！！！！！

多表设计原则 ：所有关系数据只能存在三种对应关系 （一对一、一对多、多对多）
1、多对多关系 ： 雇员和项目关系
一个雇员可以参与多个项目
一个项目可以由多个雇员参与
***老师 - 班级   学生-课程  

建表原则：必须创建第三张关系表，在关系表中引用两个实体主键 作为外键 
关系表中每条记录，代表一个雇员参与了一个项目 

2、一对多关系： 用户和博客关系
一个用户可以发表多篇博客
一个博客只能由一个作者 

建表原则：不需要创建第三方关系表，只需要在多方添加 一方主键作为 外键 

3、一对一关系 ： 这种关系很少见到  负责人和工作室
一个负责人 管理一个工作室
一个工作室 只有一个负责人 
***身份证 --人    学号  --学生

建表规则：在任一方添加对方主键 作为外键 

====================================================================================================================
多表查询——笛卡尔积 
将A表中每条记录 与 B表中每条记录进行 匹配 获得笛卡尔积 
select * from emp;
select * from dept;

select * from emp,dept; 显示结果就是笛卡尔积 
笛卡尔积结果 就是 两个表记录乘积 例如A 表3条 B表4条 ---- 笛卡尔积 12条

笛卡尔积结果是无效的，必须从笛卡尔积中选取有效的数据结果 ！！！

多表查询 连接查询 内连接查询 
从A表中选择一条记录，去B表中找对应记录 ----- 内连接 必须A表和B表存在对应记录才会显示 

create table A(A_ID int primary key auto_increment,A_NAME varchar(20) not null);
insert into A values(1,'Apple');
insert into A values(2,'Orange');
insert into A values(3,'Peach');

create table B(A_ID int primary key auto_increment,B_PRICE double);
insert into B values(1,2.30);
insert into B values(2,3.50);
insert into B values(4,null);

使用内连接 select * from a,b where a.a_id = b.a_id;
* 内连接查询结果条数 一定小于 两个表记录较多哪个表 ----- 例如 A表3条 B表5条 ---- 内连接结果条数 <= 5

select * from emp,dept where emp.dept_id = dept.id ; 将emp 表和dept 表进行内连接 
在内连接查询时 添加条件，查询人力资源部有哪些 员工 ？？ 
select * from emp,dept where emp.dept_id = dept.id and dept.name ='人力资源部'; 
工资大于7000员工来自哪个部门？ select * from emp,dept where emp.dept_id = dept.id and emp.salary > 7000;

select * from emp,dept where emp.dept_id = dept.id ; 写法一
select * from emp inner join dept on emp.dept_id = dept.id ; 写法二 

=======================================================================================================================
内容小结：
1、 mysql的安装配置 （卸载、root密码重置）
2、 SQL语言的分类
3、 SQL语句 database 操作
4、SQL语句 table表结构 操作
5、SQL语句 表数据记录操作 ***** insert update delete select 
6、MySQL数据库备份和恢复
7、多表建表规则和多表查询 ***** 

数据库完整性约束 ：primary key 主键、unique 唯一、not null非空 、foreign key 外键、check 条件约束（mysql不支持，oracle中使用）

课外知识点：E-R图绘制、范式(1NF 不可再分、2NF 部分依赖 3NF 传递依赖)

sql: 专门操作数据库的语言 （数据库只认识sql）

三大系统：操作系统 文件系统 数据库系统

什么是数据库？ 就是一个文件系统，通过标准SQL语言操作文件系统中数据 ---- 用来存放软件系统的数据 存数据 管理数据
什么是关系型数据库 ？ 保存关系数据模型 文件与文件有关系 最常用的 慢 安全
对象数据库：db2（算是一个对象数据库）

Oracle 甲骨文公司，专门数据库厂商 收购BEA 、SUN、MySQL ------- 收费 大型数据库 ，用于任何系统任何平台
MySQL 早期开源免费数据库产品，LAMP组合 Linux + Apache + MySQL + PHP 完全开源免费 ，自从mysql被oracle收购后，从6.0开始出现收费版本
DB2 IBM数据库产品 大型收费数据库 websphere服务器一起使用
SYBASE 中等规模数据库 收费 PowerDesigner 数据库建模工具
SQL Server 微软公司数据库产品 收费中等规模数据库 ，操作系统要求是windows 结合.net 一起使用

Java开发者主要使用 MySQL 、Oracle、DB2 三种数据库

=================================================================================================
MySQL 安装配置 如果没有特殊要求 直接下一步 下一步安装即可！
1、卸载
在mysql安装目录 my.ini
datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/"
1) 在控制面板中卸载MySQL
2) 删除mysql 安装目录
3) 删除MySQL数据文件目录

2、安装
选择自定义安装
MySQL Server 默认位置 c:program filesmysql目录
Server Data File 数据文件 C:Documents and SettingsAll UsersApplication DataMySQL 目录

安装后进行mysql配置
1) 配置mysql 默认字符集
默认latin1 等价于 ISO-8859-1 改为 utf8
2) Include Bin Directory in Window Path 将mysql/bin 目录配置环境变量path ---- 勾选
3) 输入超级管理root 密码

测试mysql是否安装成功 启动cmd窗口 输入 mysql -u root -p 回车 输入密码 123 ==== 出现 mysql> 安装成功

3、重置root密码
1) 停止mysql服务器 运行输入services.msc 停止mysql服务
2) 在cmd下 输入 mysqld --skip-grant-tables 启动服务器 光标不动 （不要关闭该窗口）
3) 新打开cmd 输入mysql -u root -p 不需要密码
use mysql;
update user set password=password('abc') WHERE User='root';
4) 关闭两个cmd窗口 在任务管理器结束mysqld 进程
5) 在服务管理页面 重启mysql 服务
密码修改完成

========================================================================================================
MySQL 服务器内部存储结果
一个数据库服务器可以创建多个数据库
一个数据库可以创建多张数据表
每张数据表 用来保存 数据记录 （简单认为 文件 存放数据，数据库看成文件夹）

SQL语句 结构化查询语句 表达式
特点：非过程性 一条SQL语句一个执行结果
为了使用SQL 编写复杂程序，各个数据库厂商对SQL 进行增强，SQL Server TSQL 、Oracle PLSQL

SQL语句分类 按功能（定义、操纵、控制、查询）分类
DDL 数据定义语言，定义表、库、视图
DML 对数据表记录 增加、修改和删除操作
DCL 授权、事务控制、条件判断
DQL (不是W3C组织 给出分类) 数据表记录查询
* 知道SQL语句四个分类，能够识别SQL语言属于哪个分类

SQL语句学习流程 ： 数据库操作语句 ----- 数据表结构操作语句 ---- 数据表记录操作语句

一、数据库操作语句
1、创建数据库 会为每个软件系统创建单独数据库
语法 ： create database 数据库名称 ; (创建数据库采用数据库服务器默认字符集 )
复杂写法 create database 数据库名称 character set 字符集 collate 比较规则 ;

创建一个名称为mydb1的数据库。 create database mydb1;
创建一个使用utf8字符集的mydb2数据库。 create database mydb2 character set utf8;
创建一个使用utf8字符集，并带校对规则的mydb3数据库。create database mydb3 character set utf8 collate utf8_bin;

补充：每次创建一个数据库在 数据存放目录中生成一个文件夹 ， 每个文件夹中存在 db.opt 存放默认字符集和校对规则
datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/"

2、查询数据库
show databases; ----- 查看所有数据库
show create database 数据库名; ------ 查看数据编码集

3、删除数据库
语法 ：drop database 数据库名称;

查看当前数据库服务器中的所有数据库 show databases;
查看前面创建的mydb2数据库的定义信息 show create database mydb2;
删除前面创建的mydb1数据库 drop database mydb1;

4、修改数据库编码集
语法：alter database 数据库名称 character set 字符集 collate 比较规则;

修改mydb2字符集为gbk; alter database mydb2 character set gbk;

切换当前使用数据库： use 数据库名称
查看当前正在使用数据库: select database();

==================================================== 所有数据库相关操作语句都属于DDL 语句
二、数据表结构SQL语句
1、数据表的创建
语法：create table 表名(列名 类型(长度),列名 类型(长度)... );
一个数据表 可以存在很多列，每列具有类型和长度
* 创建表时没有指定 字符集，将采用数据库默认字符集
* 创建表之前 必须使用use db 语法指定操作数据库

创建day12 数据库 create database day12;
切换到day12 数据库 use day12;

例如：
User {
id int
name string
password string
birthday date
}
一个java类 对应数据库中一张数据表，一个java对象 对应数据表中一条数据记录

MySQL 常用数据类型
java 中 String char ----- mysql 中字符串型 char varchar ''
* char是定长 varchar是变长
例如：char(8) 保存 lisi，因为lisi 只有四个字符，所有会补充四个空格，成为8个字符存入 char(8)中 ，如果有 varchar(8) 自动根据存放内容改变长度

java 中 byte short int long float double ----- mysql 中数值类型 TINYINT 、SMALLINT、INT、BIGINT、FLOAT、DOUBLE

java 中 boolean ---- mysql 逻辑性 bit 存放一位数值 0 或者1

java 中 Date ----- mysql 日期类型 date (只有日期) time(只有时间) datetime(日期时间都有) timestamp(日期时间都有 )
* datetime和timestamp 表现形式上完全相同，区别就在于timestamp 在数据库可以自定更新（当前时间）

java中 大数据类型 inputStream 二进制文件 Reader 文本文件 ------- mysql 大数据类型 blob(存放大二进制数据) text(存放大的文本文件)
* tinyblob tinytext 255字节 blob text 64KB mediumblob mediumtext 16MB longblob longtext 4GB

例如：
User {
id int ------ mysql int
name string ------ mysql varchar
password string ----- mysql varchar
birthday date ----- mysql date
}

员工表建表语句
create table employee (
id int,
name varchar(20),
gender varchar(20),
birthday date,
entry_date date,
job varchar(30),
salary double,
resume longtext
);

查看表结构 desc 表名;

*** 创建数据表时，只有字符串类型必须写长度，而其他类型都有默认长度

2、单表创建时约束
约束用来保证数据有效性和完整性
主键约束 primary key : 信息记录某个字段可以唯一区分其他信息记录，这个字段就可以是主键 （唯一 非空）
唯一约束 unique : 该字段的值不允许重复 可以null
* 一张表中可以有很多个唯一约束，只能有一个(两个)作为主键约束
非空约束 not null ：该字段的值不能为空

create table employee2 (
id int primary key auto_increment,
name varchar(20) unique not null,
gender varchar(20) not null,
birthday date not null,
entry_date date not null,
job varchar(30) not null,
salary double not null,
resume longtext
);
***列级约束

* 如果主键约束类型为 数值型 int bigint ，添加auto_increment 自动增长

3、数据表结构修改
1) 增加列 语法: alter table 表名 add 列名 类型(长度) 约束;
2) 修改现有列类型、长度和约束 语法：alter table 表名 modify 列名 类型(长度) 约束;
3) 修改现有列名称 语法：alter table 表名 change 旧列名 新列名 类型(长度) 约束;
4) 删除现有列 语法：alter table 表名 drop 列名 ;
5) 修改表名 rename table 旧表名 to 新表名;

* 修改表的字符集：alter table student character set utf8;

在上面员工表的基本上增加一个image列。---- alter table employee add image varchar(100) ;
修改job列，使其长度为60。 ----alter table employee modify job varchar(60) not null;
删除gender列。 ----alter table employee drop gender ;
表名改为user。 ----rename table employee to user;
修改表的字符集为utf8 ---- alter table user character set utf8;
列名name修改为username ----- alter table user change name username varchar(20) unique not null;

查看当前数据库内所有表：show tables
查看当前数据表字符集 ： show create table user;

4、数据表删除
语法：drop table 表名;

5、查看数据表结构
desc 表名; 查看表结构
show tables ; 查看当前库内所有表名
show create table 表名; 查看建表语句和字符集

==================================================================== 所有数据表结构操作语句都是 DDL
三、数据表中数据记录的增删改查操作 ---dml
1、向数据表插入记录
语法：insert into 表名(列名,列名,列名...) values(值,值,值...); 为数据表的每列进行赋值

注意事项
1) 插入值 类型必须和 列类型匹配
2) 值长度不能超过 列定义长度
3) 值的顺序和 列顺序对应
4) 字符串和日期型值 必须写 单引号
5) 插入空值 可以写 null

新建employee表，插入三个员工信息
insert into employee(id,name,gender,birthday,entry_date,job,salary,resume) values(1,'zhangsan','male','1990-10-10','2010-01-01','sales',4000,'good boy !');

语法2: 省略所有列名，但是后面值必须要和表中所有列进行匹配，按照表中列顺序
insert into employee values(2,'lisi','male','1988-10-01','2008-08-17','hr',3500,'good hr !');

语法3：省略可以为空，有默认值部分列名，后面值要和前面列进行匹配 不为空的且没有默认值一定要插入
insert into employee(id,name,job,salary) values(3,'wangwu','boss',20000);

* 在插入记录后 ，通过select * from employee; 查看所有员工信息

插入一条中文记录
insert into employee(id,name,job,salary) values(4,'小明','清洁员',1500);
出错了：
ERROR 1366 (HY000): Incorrect string value: 'xC3xF7' for column 'name' at row 1 ;
错误原因：mysql client 采用默认字符集编码 gbk

查看系统所有字符集 ： show variables like 'character%';

解决办法：修改客户端字符集为gbk
MYSQL中共有6个地方字符集 ：client connetion result 和客户端相关 、database server system 和服务器端相关

第一种：当前窗口临时修改 set names gbk ;
* 只对当前窗口有效，关闭后就会失效

第二种：配置mysql/my.ini 文件
[mysql] 客户端配置
[mysqld] 服务器端配置

修改客户端字符集 [mysql] 后字符集 default-character-set=gbk

使用mysql -u root -p 密码连入数据库后，如果进行数据库操作，直接操作，如果要进行数据表结构和数据记录操作，必须先切换到操作的数据库 use db;

2、数据记录更改操作
语法: update 表名 set 列名=值,列名=值.... where条件语句;
* 如果没有where条件语句，默认修改所有行数据

将所有员工薪水修改为5000元。 ----- update employee set salary = 5000;
将姓名为’zhangsan’的员工薪水修改为3000元。 ------- update employee set salary = 3000 where name='zhangsan' ;
将姓名为’lisi’的员工薪水修改为4000元,job改为ccc。 ------- update employee set salary=4000, job='ccc' where name='lisi';
将wangwu的薪水在原有基础上增加1000元。 ------------ update employee set salary = salary+1000 where name ='wangwu';

3、数据记录的删除操作
语法：delete from 表名 where条件语句 ;
* 如果没有where语句，将删除表中 所有记录

如果要删除表中所有数据记录，使用 truncate table 表名; 等价于 delete from 表名;

试题：如果使用delete删除表中所有记录和使用truncate table删除表中所有记录 有何不同 ？
truncate 删除数据，过程先将整个表删除，再重新创建
delete 删除数据，逐行删除记录
* truncate 效率要好于 delete
truncate 属于DDL ，delete 属于DML ======== 事务管理只能对DML 有效 ，被事务管理SQL语句可以回滚到SQL执行前状态

删除表中名称为’zhangsan’的记录。 ------ delete from employee where name='zhangsan';
删除表中所有记录。----- delete from employee; (可以事务回滚)
使用truncate删除表中记录。 ---- truncate table employee;

====================================================================================== insert update delete 属于DML 语句
4、数据表记录的查询
语法一 ： select [distinct] * | 列名,列名... from 表名;
select * from 表名; 查询该表中所有列信息 *通配符
select 列名,列名... from 表名; 查询表中指定列的信息
distinct 用于排重

create table exam(
id int primary key auto_increment,
name varchar(20) not null,
chinese double,
math double,
english double
);

insert into exam values(null,'关羽',85,76,70);
insert into exam values(null,'张飞',70,75,70);
insert into exam values(null,'赵云',90,65,95);

查询表中所有学生的信息。 --------- select * from exam;
查询表中所有学生的姓名和对应的英语成绩。 ----- select name,english from exam;
过滤表中重复数据 (查询英语成绩，排除完全相同重复数据) ---- select distinct english from exam;

语法二：select 表达式(列名执行运算) from 表名;
select 列名 as 别名 from 表名;

在所有学生分数上加10分特长分。 ---- select name,chinese+10,math+10,english+10 from exam;
统计每个学生的总分。------- select name,chinese+math+english from exam;
使用别名表示学生分数。----- select name,chinese+math+english as 总分 from exam;

*** 在对列起别名时，as可以省略 select name,chinese+math+english as 总分 from exam; ------ select name,chinese+math+english 总分 from exam;
select name,math from exam; 查询name和math两列的值
select name math from exam; 查询name列值，起别名math

语法三: select 列名 from 表名 where条件语句

查询姓名为关羽的学生成绩 -------select * from exam where name='关羽';
查询英语成绩大于90分的同学 ----- select * from exam where english > 90;
查询总分大于200分的所有同学 ----- select * from exam where chinese+math+english > 200;

运算符
1) 相等= 不等 <>
2) between ...and... 在两者之间取值 between 70 and 80 等价于 >=70 <=80 ----- 注意前面那个数要比后面那个数要小
3) in(值,值,值) 在指定值中任取一个 in(70,80,90) 值可以是70、80或者90
4) like '模糊查询pattern' 进行模糊查询 ，表达式有两个占位符 % 任意字符串 _ 任意单个字符 例如： name like '张%' 所有姓张学员
name like '张_' 所有姓张名字为两个字学员
5) is null 判断该列值为空

6) and 逻辑与 or 逻辑或 not 逻辑非

查询英语分数在 90－100之间的同学。 -------- select * from exam where english>=90 and english <= 100; select * from exam where english between 90 and 100;
查询数学分数为65,75,85的同学。 ---- select * from exam where math in(65,75,85);
查询所有姓赵的学生成绩。---- select * from exam where name like '赵%';
查询英语分>80，语文分>80的同学。 ---- select * from exam where english > 80 and chinese > 80;

insert into exam values(null,'刘备',null,55,38);
查询语文没有成绩学员 select * from exam where chinese is null;
查询语文有成绩学员 select * from exam where chinese is not null;

语法四： select * from 表名 order by 列名 asc|desc ; ---- asc升序 desc降序
对数学成绩排序后输出。----------- select * from exam order by math; 默认asc升序
对总分排序按从高到低（降序）的顺序输出 ------------ select * from exam order by math+chinese+english desc;
对学生成绩按照英语进行降序排序，英语相同学员按照数学降序 ------------- select * from exam order by english desc,math desc;

聚集函数 指SQL语句中内置函数 ---------- 分组函数（用于统计） **** 自动排null
1) count 统计查询结果记录条数 select count(*)|count(列名) from 表名;

统计一个班级共有多少学生？ ------------ select count(*) from exam;
统计英语成绩大于90的学生有多少个？ ------- select count(*) from exam where english > 90;
统计总分大于220的人数有多少？ --------select count(*) from exam where chinese+math+english > 220;

2) sum 统计某一列数据的和 select sum(列名) from 表名;

统计一个班级数学总成绩？ ----- select sum(math) from exam;
统计一个班级语文、英语、数学各科的总成绩 ---- select sum(chinese),sum(math),sum(english) from exam;
统计一个班级语文、英语、数学的成绩总和 select sum(chinese+math+english) from exam;
select sum(chinese)+sum(math)+sum(english) from exam;
**** 刘备语文null ，null进行所有运算 结果都是null
select sum(chinese)+sum(math)+sum(english) from exam; 含有刘备英语和数学成绩
select sum(chinese+math+english) from exam; 不含刘备英语和数学成绩
* 使用ifnull函数处理 null情况
select sum(ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0)) from exam; 含有刘备英语和数学成绩

统计一个班级语文成绩平均分 ------ select sum(chinese)/count(*) from exam;

3) avg 统计某一列平均值 select avg(列名) from 表名;
求一个班级数学平均分？ ---- select avg(math) from exam;
求一个班级总分平均分？---- select avg(ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0)) from exam;
select ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0) from exam;

4) max 统计一列最大值 min 统计一列最小值
求班级最高分和最低分（数值范围在统计中特别有用）
select max(chinese+math+english) a ,min(ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0)) i from exam;

语法五：select 分组函数 from exam group by 列名; 按照某列进行分组统计 ***一组可能有多条数据
分组操作，就是具有相同数据记录分到一组中，便于统计

create table orders(
id int,
product varchar(20),
price float
);

insert into orders(id,product,price) values(1,'电视',900);
insert into orders(id,product,price) values(2,'洗衣机',100);
insert into orders(id,product,price) values(3,'洗衣粉',90);
insert into orders(id,product,price) values(4,'桔子',9);
insert into orders(id,product,price) values(5,'洗衣粉',90);

练习：对订单表中商品归类后，显示每一类商品的总价 ---- 需要按照商品名称进行分组
select product,sum(price) from orders group by product; select * from orders group by product;错误！！！
在group by 语句后面 添加having 条件语句 ---- 对分组查询结果进行过滤
练习：查询购买了几类商品，并且每类总价大于100的商品
select product,sum(price) from orders group by product having sum(price) > 100;

试题：where 和 having 条件语句的区别 ？
where 是在分组前进行条件过滤，having 是在分组后进行条件过滤
使用where地方都可以用 having替换 ， 但是having可以使用分组函数，而where后不可以用分组函数

小结 select 语句 ： S-F-W-G-H-O 组合 select ... from ... where ... group by... having... order by ... ;
顺序不能改变
解析顺序 ： from - where - group by - having - select - order by

=================================================================================================================
MySQL 数据库的备份和恢复
1、备份命令 mysql/bin/mysqldump 将数据库SQL语句导出
语法：mysqldump -u 用户名 -p 数据库名 > 磁盘SQL文件路径

例如： 备份day12数据库 --- c:day12.sql
cmd > mysqldump -u root -p day12 > c:day12.sql 回车输入密码

INSERT INTO `exam` VALUES (1,'关羽',85,76,70),(2,'张飞',70,75,70),(3,'赵云',90,65,95),(4,'刘备',NULL,55,38);

2、恢复命令 mysql/bin/mysql 将sql文件导入到数据库
语法： mysql -u 用户名 -p 数据库名 < 磁盘SQL文件路径
***** 导入SQL 必须手动创建数据库 ，SQL不会创建数据库

例如：将c:day12.sql 导入 day12数据库
cmd > mysql -u root -p day12 < c:day12.sql 回车密码

补充知识：恢复SQL也可以在数据库内部执行 source c:day12.sql

多表设计（外键约束）
create table emp (
id int primary key auto_increment,
name varchar(20),
job varchar(20),
salary double
);

insert into emp values(null,'小丽','人力资源',4500);
insert into emp values(null,'小张','Java工程师',5000);
insert into emp values(null,'老李','财务经理',8000);
insert into emp values(null,'小刘','项目经理',10000);

create table dept(
id int primary key auto_increment,
name varchar(20)
);

insert into dept values(null,'人力资源部');
insert into dept values(null,'财务部');
insert into dept values(null,'技术研发部');

让员工表和部门表发生关系，知道员工属于哪个部门 ，在员工表添加部门id字段
alter table emp add dept_id int ;
update emp set dept_id = 1 where name = '小丽';
update emp set dept_id = 2 where name = '老李';
update emp set dept_id = 3 where name = '小刘';
update emp set dept_id = 3 where name = '小张';

公司因为财政问题，解散技术研发部 delete from dept where name ='技术研发部'; ----- 小张和小刘 失去了组织

emp表 中dept_id字段 引用 dept表 id 字段 ------- 添加外键约束 （保证数据有效和完整性）
将emp表中dept_id 设置为外键约束 alter table emp add foreign key(dept_id) references dept(id) ;
无法删除技术研发部 ，因为小刘和小张信息 依赖技术研发部 记录 ！！！！！

多表设计原则 ：所有关系数据只能存在三种对应关系 （一对一、一对多、多对多）
1、多对多关系 ： 雇员和项目关系
一个雇员可以参与多个项目
一个项目可以由多个雇员参与
***老师 - 班级 学生-课程

建表原则：必须创建第三张关系表，在关系表中引用两个实体主键 作为外键
关系表中每条记录，代表一个雇员参与了一个项目

2、一对多关系： 用户和博客关系
一个用户可以发表多篇博客
一个博客只能由一个作者

建表原则：不需要创建第三方关系表，只需要在多方添加 一方主键作为 外键

3、一对一关系 ： 这种关系很少见到 负责人和工作室
一个负责人 管理一个工作室
一个工作室 只有一个负责人
***身份证 --人 学号 --学生

建表规则：在任一方添加对方主键 作为外键

====================================================================================================================
多表查询——笛卡尔积
将A表中每条记录 与 B表中每条记录进行 匹配 获得笛卡尔积
select * from emp;
select * from dept;

select * from emp,dept; 显示结果就是笛卡尔积
笛卡尔积结果 就是 两个表记录乘积 例如A 表3条 B表4条 ---- 笛卡尔积 12条

笛卡尔积结果是无效的，必须从笛卡尔积中选取有效的数据结果 ！！！

多表查询 连接查询 内连接查询
从A表中选择一条记录，去B表中找对应记录 ----- 内连接 必须A表和B表存在对应记录才会显示

create table A(A_ID int primary key auto_increment,A_NAME varchar(20) not null);
insert into A values(1,'Apple');
insert into A values(2,'Orange');
insert into A values(3,'Peach');

create table B(A_ID int primary key auto_increment,B_PRICE double);
insert into B values(1,2.30);
insert into B values(2,3.50);
insert into B values(4,null);

使用内连接 select * from a,b where a.a_id = b.a_id;
* 内连接查询结果条数 一定小于 两个表记录较多哪个表 ----- 例如 A表3条 B表5条 ---- 内连接结果条数 <= 5

select * from emp,dept where emp.dept_id = dept.id ; 将emp 表和dept 表进行内连接
在内连接查询时 添加条件，查询人力资源部有哪些 员工 ？？
select * from emp,dept where emp.dept_id = dept.id and dept.name ='人力资源部';
工资大于7000员工来自哪个部门？ select * from emp,dept where emp.dept_id = dept.id and emp.salary > 7000;

select * from emp,dept where emp.dept_id = dept.id ; 写法一
select * from emp inner join dept on emp.dept_id = dept.id ; 写法二

=======================================================================================================================
内容小结：
1、 mysql的安装配置 （卸载、root密码重置）
2、 SQL语言的分类
3、 SQL语句 database 操作
4、SQL语句 table表结构 操作
5、SQL语句 表数据记录操作 ***** insert update delete select
6、MySQL数据库备份和恢复
7、多表建表规则和多表查询 *****

数据库完整性约束 ：primary key 主键、unique 唯一、not null非空 、foreign key 外键、check 条件约束（mysql不支持，oracle中使用）

课外知识点：E-R图绘制、范式(1NF 不可再分、2NF 部分依赖 3NF 传递依赖)