java程序员第十五课 -MySQL数据库和SQL语言

课程回顾：JSP开发模式和案例

JSP开发模式

1.Servlet技术 – JSP技术 – JSP+JavaBean + Servlet + JSP +JavaBean
2.MVC设计模式
* M – Model 模型（JavaBean） – 封装数据和处理数据
* V – view 视图（JSP） – 显示数据
* C – Contorller 控制（Servlet） – 控制请求
案例

1.准备环境 导入开发jar包
2.注册的功能
3.登陆的功能
4.记住用户名
MySQL数据库和SQL语言

数据库的概述

1.什么是数据库：数据仓库。但是想访问数据库中的数据，必须使用SQL语句来访问。文件的系统。
2.数据库有什么作用：存储数据。开发任何的应用，都会产生数据。需要把数据保存到某个位置。
3.数据库存储的是实体与实体之间的关系
* 关系型的数据库。
* 实体：例子：注册的功能，需要使用user一个实体。
* 例子：用户 商品 订单 …

4.常见的数据库
* 做Java开发，必须会使用两个数据库 Oracle和MySQL
* Oracle – Oracle（甲骨文） 大型的数据库，并且收费的。
* MySQL – 应用最广泛的数据库。免费的开源的。现在已经被Oracle收购了，（从6.x版本开始收费了）
* SQLServer – 微软的。中型的数据库。
* DB2 – IBM开发数据库，大型的数据库。
* SyBASE – 退出了历史的舞台。PowerDigener软件（数据库的建摸的软件，非常牛）
MySQL数据库的安装和卸载

1.MySQL数据库的卸载：
* 先找到MySQL数据库的安装路径。找到my.ini文件（MySQL配置文件）
* basedir=”C:/Program Files (x86)/MySQL/MySQL Server 5.5/” – 代表MySQL数据库的安装路径
* datadir=”C:/ProgramData/MySQL/MySQL Server 5.5/Data/” – 代表MySQL数据库数据存储的路径
* 可以使用控制面板卸载MySQL数据库，需要找到上面两个路径下的文件，手动删除。

2.可以进行安装了
* 安装路径不要有中文和空格。（****）
* 安装步骤：看文档。

3.进行测试：打开cmd的窗口，输入命令：mysql -u root -p 回车 ，弹出输入密码 123
* 还有一种方式：mysql -uroot -p123
MySQL密码重置

1.提供了一份文档，按着文档操作。就ok。

1.停止mysql服务:
services.msc 进入到服务界面
2.在cmd>输入一个命令:
mysqld –skip-grant-tables (开启一个mysql服务,不需要进行认证.)
3.新打开一个cmd窗口
mysql -u root -p 不需要输入密码.就可以进入.
4.输入命令 show databases;查看数据库，输入命令 use mysql;使用mysql数据库。
5.修改密码的语句:
update user set password=password(‘root’) WHERE user=’root’;
6.将两个窗口都关闭.
7.任务管理器中结束（mysqld）进程.
8.重启mysql服务
MySQL数据库的概念

• 总结：安装了数据库，数据库的服务器。进入到服务器中，数据库服务器中包含多个数据库（一个应用对应一个数据库），进入到数据库中。数据库中包含多个表结构，表结构包含多个列名（字段），并且表结构还存储数据。字段对应的Java中的JavaBean的属性。一个实体对象对应的一行数据。

• 看图。
  SQL语句（重点）

SQL语言概述

1.SQL非过程性的语言
* 过程性的语言：该条语句的执行，需要依赖上一条或者上几条语句。
* 非过程性的语言：该条语句的执行，会有一个结果。

2.每个数据库的生厂商会提供数据库的方言。
* 例如：Oracle 提供了PL/SQL

3.SQL语言的分类
* DDL 数据的定义语言
* 创建数据库、创建表结构、创建视图、索引
* create alter drop

* DML       数据的操作语言    * 添加数据      修改数据    删除数据    * insert        update  delete* DCL       数据的控制语言    * 写if else 创建存储过程* DQL       数据的查询语言    * 查询数据      select

数据库的SQL语句（CURD）

创建数据库

1.语法：
* 基本语法（使用）：create database 数据库名称;
* 复杂语法：create database 数据库名称 character set ‘编码’ collate ‘校对规则’;

2.校对规则：决定数据库的属性。使用默认的校对规则。查看校对规则，需要查看MySQL参考手册，第10章。

3.练习：
创建一个名称为mydb1的数据库。
* create database mydb1;
创建一个使用utf8字符集的mydb2数据库。
* create database mydb2 character set ‘utf8’;
创建一个使用utf8字符集，并带校对规则的mydb3数据库
* create database mydb3 character set ‘utf8’ collate ‘utf8_bin’;
查看数据

1.查询所有的数据库 – show databases;
2.查询数据库的定义信息 – show create database 数据库名称;
3.切换数据库（重要） – use 数据库名称;
4.查询当前正在使用的数据库 – select database();
删除数据库

1.删除数据库 – drop database 数据库名称;
2.练习
删除前面创建的mydb1数据库
drop database mydb1;
修改数据库

1.语法：alter database 数据库名称 character set ‘gbk’ collate ‘规则’;
数据库的表结构（CURD）

1.语法：
create table 表名(
字段1 字段类型(长度) 约束,
字段2 字段类型(长度) 约束,
字段3 字段类型(长度) 约束
);

2.字段的类型
* 字符串型（重点）（varchar和char区别你们记住的）
* VARCHAR – 数据的长度是可变的。例子：name 的类型varchar(8)，存入数据 hello，存完之后该字段的长度自动变成5了。
* CHAR – 数据的长度是不可变的。例子：name的类型char(8)，存入数据hello，之后长度没变。剩3个长度使用空格进行补全。

* 大数据类型    * BLOB      -- 字节（电影 mp3）    * TEXT      -- 字符（文本的内容）* 数值型（重点）    TINYINT 、SMALLINT、INT、BIGINT、FLOAT、DOUBLE* 逻辑性     * BIT   值1和0    * 在Java中是true或者false* 日期型（重点）    * DATE          -- 只包含日期（年月日）    * TIME          -- 只包含时分秒（时分秒）    * DATETIME      -- 既有日期又有时分秒    * TIMESTAMP     -- 既有日期又有时分秒        * DATETIME和TIMESTAMP区别：如果使用DATETIME声明字段，传入了null值，该字段的值就为null。如果使用TIMESTAMP声明字段，传入null值，它会默认查找本地的系统时间作为值，存入该字段。

3.创建表的练习
create database day15;
use day15;
create table employee(
id int,
name varchar(50),
gender char(5),
birthday date,
entry_data date,
job varchar(50),
salary double,
resume text
);

+————+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+————+————-+——+—–+———+——-+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| gender | char(5) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| entry_data | date | YES | | NULL | |
| job | varchar(50) | YES | | NULL | |
| salary | double | YES | | NULL | |
| resume | text | YES | | NULL | |
+————+————-+——+—–+———+——-+
查看数据库表结构

1.desc 表名; – 查看表的详细信息。
2.show tables; – 显示该数据库中所有的表
3.show create table 表名; – 查看表的信息
删除表

1.drop table 表名; – 删除表结构
修改表

1.添加列
* alter table 表名 add 字段名称 字段类型(长度) 约束;
2.删除列
* alter table 表名 drop 字段名称;
3.修改列（不是修改列的名称，修改列的类型、长度和约束）
* alter table 表名 modify 字段名称 字段类型(长度) 约束;
4.修改列的名称
* alter table 表名 change 旧字段名称 新名称 字段类型(长度) 约束;
5.修改表名
* rename table 表名 to 新表名;

6.练习
在上面员工表的基本上增加一个image列。
alter table employee add image varchar(50);
修改job列，使其长度为60。
alter table employee modify job varchar(60);
删除gender列。
alter table employee drop gender;
表名改为user。
rename table employee to user;
修改表的字符集为utf8
alter table 表名 character set utf8;
列名name修改为username
alter table user change name username varchar(60);
数据库的数据（CURD）

插入数据（insert）

1.语法：
* insert into 表名 (字段1,字段2,字段3) values (值1,值2,值3); – 有几个字段，插入几个值
* insert into 表名 values (值1,值2,值3,值4); – 默认插入所有的数据
2.注意事项：
* 插入的数据的类型（字符串）与字段的类型是相同的（varchar）。
* 数据的大小在列的规定的范围内。
* 数据的位置与字段的位置必须是相同的。
* 字符串和日期类型的数据需要使用单引号包含。

3.在user的表中添加数据
insert into user values (1,’meimei’,’1985-11-11’,’1987-2-2’,’HR’,5000,’ss’,’jpg’);
insert into user values (2,’小凤’,’1998-12-11’,’2013-2-2’,’BOSS’,15000,’ss’,’jpg’);
insert into user values (3,’大鹏’,’1991-02-10’,’2015-05-20’,’BOSS’,25000,’ss’,’jpg’);
insert into user values (4,’小苍’,’1987-01-07’,’2000-01-20’,’yanyuan’,20000,’ss’,’jpg’);
insert into user values (5,’如花’,’1987-11-17’,’2001-01-20’,’yangyuan’,20000,’ss’,’jpg’);
insert into user values (6,’芙蓉’,’1985-03-15’,’2001-01-20’,’BOSS’,20000,’ss’,’jpg’);
插入中文数据乱码

1.问题的产生：黑窗口的默认的编码是gbk，安装的MySQL的数据库编码是utf8，发送中文就会产生乱码。
2.解决问题：
* 因为MySQL服务器内部进行了编码的转换，不会乱码。解决方案：修改MySQL客户端的编码为gbk。

3.解决问题的步骤
* 先关闭MySQL的服务
* 找到my.ini的配置文件，修改客户端的编码为gbk.
[client]
port=3306
[mysql]
default-character-set=gbk
* 重启MySQL服务器，测试添加中文的数据了。
修改数据的语句（update）

1.语法：update 表名 set 字段1=值,字段2=值 where 条件;
2.注意：如果没有where子句，默认更新所有的行。

3.练习
将所有员工薪水修改为5000元。
update user set salary = 5000;
将姓名为’大鹏’的员工薪水修改为3000元。
update user set salary = 3000 where username = ‘大鹏’;
将姓名为’小凤’的员工薪水修改为4000元,job改为ccc。
update user set salary = 4000 , job = ‘ccc’ where username = ‘小凤’;
将小苍的薪水在原有基础上增加1000元。
update user set salary = salary + 1000 where username = ‘小苍’;
删除数据（delete）

1.语法：delete from 表名 where 条件;
2.如果没有where条件子句，默认删除所有的数据。
3.还可以使用truncate 表名也可以删除数据（删除全部的数据） – truncate 表名

4.delete from 表名 和 truncate 表名的区别：
* truncate 表名 先默认把该表结构直接删除掉，然后默默的创建一个空的表。（表结构没变）（不用）
* delete from 表名; 一行一行的删除。（使用该种方式，该种方式支持事物）

5.事物：逻辑上的一组操作，在同一个事物中操作要么全都成功，要么全部失败。
* 例子：转账的例子。大鹏给美美转1000元，操作：扣除大鹏1000元钱，给美美加上1000元。大鹏和美美的钱的总数是不变。

6.练习
删除表中名称为’大鹏’的记录。
delete from user where username = ‘大鹏’;
删除表中所有记录。
delete from user;
使用truncate删除表中记录。
truncate user;
查询数据 select（重点）

1.语法：
* select * from 表名； – 查询所有的列的所有的数据
* select 字段1,字段2 from 表名; – 查询指定的列的所有的数据
* 通过关键字DISTINCT去掉重复的数据

2.查询的测试
create table stu(
id int,
name varchar(20),
math int,
english int,
chinese int
);

insert into stu values (1,'美美',30,90,50);insert into stu values (2,'小凤',90,95,98);insert into stu values (3,'如花',45,90,65);insert into stu values (4,'熊大',76,89,55);insert into stu values (5,'熊二',15,18,23);insert into stu values (6,'光头强',11,99,58);查询表中所有学生的信息。    select * from stu;查询表中所有学生的姓名和对应的英语成绩。    select name,english from stu;过滤表中重复数据。    select distinct english from stu;

select语句列支持运算和使用别名

1.在select语句中可以使用运算对查询的列。
2.还可以使用别名 as 别名

3.练习
在所有学生分数上加10分特长分。
* select name,math+10,english+10,chinese+10 from stu;
* select name,(math+10) as m,(english+10) as e,(chinese+10) as c from stu;
统计每个学生的总分。
* select name,(math+english+chinese) from stu;
使用别名表示学生分数。
* select name,(math+english+chinese) as total from stu;
* select name,(math+english+chinese) total2 from stu;

4.别名的用法（一般都会多表的查询）（别名的as的关键字可以省略不写 ）
* select p.name,c.xxx,p.xxx from person p,class c;
使用where子句条件过滤

1.使用where子句进行条件的过滤。查询的数据不是默认查询所有了，根据查询的条件进行查询。
2.练习
查询姓名为小凤的学生成绩
select * from stu where name = ‘小凤’;
查询英语成绩大于90分的同学
select name,english from stu where english > 90;
查询总分大于200分的所有同学
select name from stu where (math+english+chinese) > 200;
where子句后可以使用的符号

1.符号：> < <= >= = <> 大于、小于、大于(小于)等于、不等于
2.in – 代表范围。 语法：in (值1,值2,值3);
* select * from stu where name = ‘小凤’; – 一个结果
* select * from stu where math in (90,20,30,76); – 查询结果是多个
3.like – 模糊查询。
* 语法：
* like ‘张_’; 查询的结果：张飞 张三 张X
* like ‘张%’; 查询的结果：以张开头的。张三 张翼德 张技术的见覅束带结发阿萨德
* like ‘%张’; 查询的结果：什么什么张。法计算的房间张，翼德张。
* like ‘%张%’; 查询的结果：只要包含张就行。张三 三张 三张三

4.逻辑运算符（重要）
* 与 and
* 或 or
* 非 not

5.使用MySQL的方言 between … and
* 查询结果在两个之间的结果。包含开始和结尾。
* select name,english from stu where english between 80 and 90;

5.练习
查询英语分数在 80－90之间的同学。
select name,english from stu where english >= 80 and english < 90;
select name,english from stu where english between 80 and 90;
查询数学分数为89,90,91的同学。
select name,math from stu where math in (89,90,91);
查询所有姓熊的学生成绩。
select * from stu where name like ‘熊%’;
查询数学分>80，语文分>80的同学。
select * from stu where math > 80 and chinese >80;
select * from stu where math > 80 or chinese >80;
使用order by对结果进行排序

1.语法：order by 字段名称 asc（升序）/desc（降序）；
2.如果没有指定，默认是升序。
3.order by 放在语句的末尾。

4.练习
对数学成绩排序后输出。
* select * from stu order by math asc;
* select * from stu where math > 40 order by math desc;

对总分排序按从高到低的顺序输出    * select name,(math+english+chinese) as total from stu order by total desc;    * select name,(math+english+chinese) as total from stu where total > 150 order by total desc; 错误的对学生成绩按照英语进行降序排序，英语相同学员按照数学降序    * select name,english,math from stu order by english desc,math desc;对姓熊的学生成绩排序输出    * select name,english from stu where name like '熊%' order by english desc; 

聚集函数

1.聚集函数的作用：操作列。通过函数求列的总数量，列的总和，列的平均值，列的最大值和最小值。
2.聚集函数
* count() – 计数
* sum() – 求和
* avg() – 平均数
* max() – 最大值
* min() – 最小值

3.count 计数（完成分页的时候有用的）
* 练习
统计一个班级共有多少学生？
select count(*) from stu;

    统计数学成绩大于90的学生有多少个？        select count(*) from stu where math > 50;                   统计总分大于220的人数有多少？        select count(*) from stu where (math+english+chinese) > 200;

4.sum 求和
* 练习
统计一个班级数学总成绩？
select sum(math) from stu;
统计一个班级语文、英语、数学各科的总成绩
select sum(math),sum(english),sum(chinese) from stu;
统计一个班级语文、英语、数学的成绩总和
select sum(ifnull(math,0)+english+chinese) from stu;
select sum(math)+sum(english)+sum(chinese) from stu;
* 修改的语句，把如花的数学的成绩修改成null
* update stu set math = null where id = 3;
统计一个班级语文成绩平均分
* select sum(chinese)/count(chinese) from stu;

* 可以使用ifnull(xxx,0) 把null变成0

5.avg 平均值
* 平均值：
求一个班级数学平均分？
select avg(math) from stu;
select avg(chinese) from stu;
求一个班级总分平均分
select avg(ifnull(math,0)+english+chinese) from stu;

6.max和min 最大值和最小值
* 求班级最高分和最低分（数值范围在统计中特别有用）
select max(ifnull(math,0)+english+chinese) from stu;
select min(ifnull(math,0)+english+chinese) from stu;