第一天 SQL语句

第一天 SQL语句
操作 数据库-- 数据表--- 数据 CURD


第二天 JDBC编程 ---- 数据库编程（使用java对数据库进行CURD）


第三天 事务管理（事务特性）数据库连接池


第四天 Java 数据库 企业端常用框架 DBUtils / Spring JDBCTemplate 


第五天 编写应用数据库 javaweb案例




关系化模型 ----- 分布式结构数据库（Google GFS B+树）
数据库保存数据模式 维护数据模型关系
微博系统：用户注册、用户登录、用户发表微博 ----- 维护用户与微博直接关系 数据表方式
用户创建一张数据表  微博创建一张数据表 ------ 维护关系


常见关系化数据库：收费、免费
收费：SQL Server、Oracle、 DB2
免费：MySQL  
SQL Server: 中型数据库 微软产品 微软开发阵营：IIS .net语言平台 ------ windows 2003 用于政府、国企
DB2: IBM 定位高端 大型企业用  WebSphere + 硬件服务器  ---- Java开发环境 JDK产品
Oracle (专注数据库产品): 开源 Oracle WebLogic Java  开发环境 NetBeans
MySQL : 开源社区 5.5之前 完全开源免费 + PHP开发环境（LAMP）:Linux + Apache + MySQL + PHP ---- 网站级产品上用得比较多（互联网行业）
MySQL 被Oracle 收购 MySQL6.0 : 分为收费版、免费版
HSQL ： Java开发者喜爱数据库 ，非常小型开源免费数据库产品，由java开发的


MySQL安装常用配置项：
Latin1 ===== ISO8859-1  ---- 设置utf-8
选择要不要将Mysql/bin 存放path 里 ----- 可以在命令行任何位置使用Mysql命令
MySQL提供一个默认账户 root 设置密码 123


登陆MySQL：cmd ---- c:\> mysql -u用户名 -p密码


java --- javabean（对应数据库中一张数据表） ------ javabean对象（对应数据表中一条数据记录）


数据库相对于 静态数据文件 ----- SQL 操作这些静态数据文件语言
一条SQL 语句 ----- 对应一个执行结果
每个数据库产品，都会提供一些对SQL扩展语法


SQL语言分类？ ---- 区分哪些语句属于哪一类？DDL DML DCL DQL 


登陆Mysql： 
一、首先指定操纵数据库
1、创建数据库： create database 数据库名;
CHARACTER SET charset_name ---  数据库编码字符集
COLLATE collation_name ----- 字符比较规则 --- 用于字段排序
一种字符集中包含多种比较规则，每一种字符集都有一个默认的比较规则


2、查看数据字符集和比较规则
show create database 数据库名;


练习答案
mysql> create database mydb1;
Query OK, 1 row affected (0.02 sec)


mysql> create database mydb2 character set utf-8;
ERROR 1115 (42000): Unknown character set: 'utf'
mysql> create database mydb2 character set utf8;
Query OK, 1 row affected (0.00 sec)


mysql> create database mydb3 character set utf8 collate utf8_unicode_ci;
Query OK, 1 row affected (0.03 sec)


3、显示所有数据库
show databases;


4、删除数据库
drop database 数据库名


5、修改数据
alter database 数据库名 CHARACTER SET charset_name COLLATE collation_name


练习：修改mydb2 字符集gbk
alter database mydb2 character set gbk;


6、使用数据库(切换数据库)
use 数据库名; 


7、查看当前使用数据库
select database();


8、数据库备份和恢复
新建一张数据表
create table user(name varchar(20));
新插入一条数据
insert into user values('mary');
查看数据
select * from user;


数据库备份：mysqldump -u 用户名 -p 数据库名 > 文件名.sql ---- 必须在命令行下执行，不能再mysql提示符下执行
* 数据库备份时只能备份数据库中表和数据，没有数据库创建语句


数据库恢复：方式一：mysql –u 用户名 -p 数据库名 < 文件名.sql  // 在cmd下使用
方式二 ：mysql> Source 文件名.sql   // 在mysql内部使用


二 、操作数据表
1、创建数据表 （先指定数据库 use 数据库）
create table 表名(列名1 列名1类型 约束,列名2 列名2类型 约束,列名3 列名3类型 约束) character set 字符集 collate 校对规则 ;
了解 java中数据类型 ----- 数据库对应数据类型
数据库数据类型 主要分为3类
1) 数值型类型
bit(位数) ---- bit(8) byte ---- bit(16) short ---- bit(64) long
TINYINT SMALLINT INT BIGINT ------- byte short int long
FLOAT ----- float
DOUBLE ---- double 
BOOLEAN ----- boolean 
2) 文本型类型
char(长度) ----- 定长字符串 例如：name char(8) ---- lisi 存入lisi(四个空格)
varchar(长度)  ---- 变成字符串 例如：name varchar(20) ---- lisi 存入时 varchar字段自动调整长度适应你存入内容
BLOB 二进制字节码 ----- 主要用于保存二进制文件（图片、音乐、电影）  最大4G longblob
TEXT 大文本数据 ------ 大型文本数据，小说
3) 日期型类型
DATE/TIME/DATETIME/TimeStamp
Date --- 只有日期
time --- 只有时间
datetime --- 有日期和时间
timestamp ---- 精确到毫秒（时间戳） ------ 在数据库自动更新 日志


create table employee(
    id int,
    name varchar(20),
    gender varchar(10),
    birthday date,
    entry_date date,
    job varchar(20),
    salary double,
    resume text
);


定义约束
1)  主键约束：唯一标识该条记录字段 primary key 不允许为空，不允许重复 例如上面employee 的id
如果主键自动增长，用户不需要插入数据时 关注主键
2) 唯一约束：约束该字段不能重复 unique
3) 非空约束：该字段值不能为null  （"" 和 null不一样的）


2、修改表 alter table
1） 添加表中一列  alter table 表名 add 列名 列类型 约束
2） 修改表中一列  alter table 表名 modify 列名 列类型 约束
3)  删除表中一列 alter table 表名 drop 列名
4） 修改列名 alter table 表名 change 旧列名 新列名 列类型 约束
5） 修改表名 rename table 旧表名 to 新表名


练习：查看表结构(desc 表名)
1) 为id 添加主键约束 alter table employee modify id int primary key;
2) 为name 添加唯一约束 alter table employee modify name varchar(20) unique;
3) gender不能为空 alter table employee modify gender varchar(10) not null;
4) 在上面员工表的基本上增加一个image列  alter table employee add image blob;
5) 修改job列，使其长度为60。 alter table employee modify job varchar(60);
6) 删除gender列。 alter table employee drop gender;
7) 表名改为user。 rename table employee to user; (查看所有表 show tables)
8) 修改表的字符集为gbk  alter table user character set gbk; (查看现在字符集 show create table 表名)
9) 列名name修改为username alter table user change name username varchar(20) unique;


show create table 表名; 查看建表语句（表字符集）
show tables ; 查看所有表列表
desc 表名; 查看表结构


3、表删除
drop table 表名;


-------------------------------------------------------------------------------------------------------------------------
表数据记录CURD
1、表数据插入 
写法一：
insert into 表名(列名1,列名2.. ) values (值1,值2 ...) ;
字符串和日期 ''
日期 yyyy-mm-dd
* 使用select * from 表名; 查看一下插入数据


写法二：省略列字段
insert into 表名 values (值1,值2) ;
* 值数量 顺序必须和表中字段数量和顺序一致


写法三：指定列名和对应值（不完全）
insert into emp(id,name) values(4,'zhaoliu');


插入中文问题
ERROR 1406 (22001): Data too long for column 'name' at row 1


MySQL字符集 分为两类
客户端字符集：client connection results
服务器相关字符集：database server  system(utf-8不可修改)


乱码原因：使用客户端界面默认字符集gbk
set character_set_client=gbk; 一个个设置
set names gbk; 快速修改客户端相关3个字符集  ----- 临时修改当前窗口编码


my.ini  ----永久修改窗口默认字符集
[mysql] ----- 客户端字符集
default-character-set=utf8
[mysqld] ------ 服务器端字符集
default-character-set=utf8


2、数据表记录修改操作
update 表名 set 列名1=值1,列名2=值2 [where条件语句] ;


练习：
将所有员工薪水修改为5000元。  update emp set salary=5000;
将姓名为’zhangsan’的员工薪水修改为3000元。 update emp set salary=3000 where name='zhangsan';
将姓名为’lisi’的员工薪水修改为4000元,resume 改为ccc。 update emp set salary=4000,resume='ccc' where name='lisi';
将wangwu的薪水在原有基础上增加1000元。 update emp set salary = salary + 1000 where name='wangwu';
 
3、数据表记录的删除操作
delete from 表名 [where 条件]


练习
删除表中名称为’zhangsan’的记录。delete from emp where name='zhangsan';
删除表中所有记录。delete from emp ;
使用truncate删除表中记录。truncate emp;


truncate和delete区别
1、truncate 属于 DDL 语句 （不受数据库事务管理） 原理：删除整个table，重新创建
2、delete 属于DML 语句 （可以进行事务管理，事务回滚） 原理：逐行删除 start transaction;  rollback;


4、数据库表记录查询操作
1)select *|指定列名 from 表名;


新建学生表
id name chinese english math 


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


insert into student values(null,'小王',90,80,70);
insert into student values(null,'小李',100,50,60);
insert into student values(null,'小张',70,80,75);
练习：
查询表中所有学生的信息。select * from student;
查询表中所有学生的姓名和对应的英语成绩。 select name,english from student;
过滤表中重复数据。select distinct english from student;


2) 查询中可以使用表达式进行计算 、 as起别名
练习
在所有学生数学分数上加10分特长分显示。select name,math+10 from student;
统计每个学生的总分。 select name,chinese+math+english from student;
使用别名表示学生总分数。 select name,chinese+math+english as 总分 from student;  ----- as可以省略
select 列名 as 别名 from 表名;


3) 带有条件select 查询
练习：
查询姓名为XXX的学生成绩  select chinese+math+english from student where name='小王';  通过binary 严格= 比较
查询英语成绩大于70分的同学  select * from student where english>70;
查询总分大于200分的所有同学 select * from student where chinese+math+english>200;


<> 不等于
select * from student where english <> 80;


查询英语分数在70-80间 
select * from student where english>=70 and english<=80;
select * from student where english between 70 and 80;


查询语文分70或者90分人 
select * from student where chinese=70 or chinese=90;
 select * from student where chinese in (70,90);


查询姓名以'老'开头 % 代表零个或多个任意字符，_ 代表一个字符
select * from student where name like '老%';


输出emp表中resume为空 员工信息
select * from emp where resume = null; 不对的
select * from emp where resume is null; 
select * from emp where resume is not null;
select * from emp where not(resume is null); 条件取非


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


4) 使用orderby 对查询结果排序
select * from 表名 where 条件 order by 列名1 asc|desc, 列名2 asc|desc ...
asc 升序
desc 降序


英语降序，语文升序
select * from student order by english desc,chinese asc;


对数学成绩排序后输出。 select * from student order by math; 默认升序
对总分排序后输出，然后再按从高到低的顺序输出  select name,chinese+math+english 总分 from student order by 总分 desc;
对姓小的学生成绩排序输出 
select name,chinese+math+english 总分 from student where name like '小%' order by 总分 desc;


聚集函数 --- 统计函数 （求和 求平均值 最大值）
count ---- 用来统计记录条数
语法：select count(*)|count(列名) from 表名
例如：学生表总共有多少学生信息 select count(*) from student;


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


sum ---- 计算一列的和
语法：select sum(列名) from student;
语文总分数：select sum(chinese) from student;


统计一个班级数学总成绩？select sum(math) from student;
统计一个班级语文、英语、数学各科的总成绩 select sum(chinese),sum(english),sum(math) from student;
统计一个班级语文、英语、数学的成绩总和 select sum(chinese+math+english) from student;
统计一个班级语文成绩平均分 select sum(chinese)/count(chinese) from student;


avg ---- 计算一列平均值
求一个班级数学平均分？select avg(math) from student;
求一个班级总分平均分select avg(math+chinese+english) from student;


MAX/MIN --- 求一列最大值或最小值
求班级语文最低分 select min(chinese) from student
select name from student where chinese = (select min(chinese) from student) ;
求英语最高分 select max(english) from student;




分组函数 group by 列名  以XX列分组
查询每个英语分数的学生有多少人 select english,count(*) from student group by english;
对订单表中商品归类后，显示每一类商品的总价 select product,sum(price) from order1 group by product;
查询购买了几类商品 
select count(distinct product) from order1; ------ 4
查询购买了几类商品，并且每类总价大于100的商品
sum(price)>100
*分组之后 聚集函数使用 不能通过where ----- 通过having 
select product from order1 where sum(price)>100 group by product; 不对的
select product from order1 group by product having sum(price)>100; 


having和where区别？
having可以很多情况下替换where （不分组相当于一个分组）
分组后，对聚集函数进行条件判断 只能使用having


---------------------------------------------------------------------------------------------------------------
select 使用
S-F-W-G-H-O 
select --- from -- where --- group by --- having --- order by
where 添加条件
group by  完成分组
having 聚集函数相关条件
order by 对查询结果进行排序


新建department 部门表
create table department (
id int primary key auto_increment,
        name varchar(20) unique not null
);


mysql> insert into department values(null,'人力资源');
Query OK, 1 row affected (0.02 sec)


mysql> insert into department values(null,'软件研发部');
Query OK, 1 row affected (0.00 sec)


mysql> insert into department values(null,'行政部');
Query OK, 1 row affected (0.00 sec)


为了建立员工表与部门表关系 ，在员工信息中添加所属部门编号
alter table emp add dept_id int;


外键约束：当A表数据 依赖B表中数据时，添加外键约束，控制数据有效性
constraint 外键名称 foreign key (dept_id) references department(id) 


为emp添加外键
alter table emp add foreign key(dept_id) references department(id);
 
------------------------------------------------
多对多关系
雇员和项目 ： 一个雇员可以同时参与多个项目，一个项目可以同时由多个雇员参与
设计多对多关系数据表时，创建第三张关系表
create table P (
   P1 int primary key,
   P2 varchar(20)
)


create table E (
  E1 int primary key,
  E2 varchar(20)
)


create table P_E(  // 在参与表中一条数据记录 代表一个雇员参与了一个项目
   P1 int,
   E1 int,
   foreign key(P1) references P(P1),
   foreign key(E1) references E(E1)
)
顾客 购买 商品 ---- 多对多
一种商品 多个顾客可以购买
一个顾客 可以购买多种商品


-------------------------------------------------------
一对多关系
员工和部门  ： 一个部门可以有多名员工，一个员工只有一个所属部门


设计一对多关系，在多方表中添加一方外键 例如：在员工表中添加部门外键


-------------------------------------------------------


一对一关系：
设计一对一关系 ：可以在任何一方 添加另一方外键




关联查询
select * from emp,department where emp.dept_id = department.id;
select emp.name,department.name from emp,department where emp.dept_id = department.id;


查询行政部有多少员工
select count(*) from emp,department where emp.dept_id = department.id and department.name='行政部';


查询每个部门有多少人
select  department.name,count(*) from emp,department where emp.dept_id = department.id group by department.name;


每个部门工资总和
select  department.name,sum(emp.salary) from emp,department where emp.dept_id = department.id group by department.name;