day16-mysql基础&约束
学习目标
第1章 数据库介绍
1.1 数据库概述
什么是数据库
数据库就是存储数据的仓库，其本质是一个文件系统，数据按照特定的格式将数据存储起来，用户可以对数据库中
的数据进行增加，修改，删除及查询操作。
什么是数据库管理系统
数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建
立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系
统访问数据库中表内的数据。
数据库与数据库管理系统的关系
1.2 数据库表
数据库中以表为组织单位存储数据。
表类似我们的Java类，每个字段都有对应的数据类型。
那么用我们熟悉的java程序来与关系型数据对比，就会发现以下对应关系。
类----------表
类中属性----------表中字段
对象----------记录1.3 表数据
根据表字段所规定的数据类型，我们可以向其中填入一条条的数据，而表中的每条数据类似类的实例对象。表中的
一行一行的信息我们称之为记录。
表记录与java类对象的对应关系
1.4 常见数据库
常见的数据库管理系统
MYSQL：开源免费的数据库，小型的数据库.已经被Oracle收购了.MySQL6.x版本也开始收费。
Oracle：收费的大型数据库，Oracle公司的产品。Oracle收购SUN公司，收购MYSQL。
DB2 ：IBM公司的数据库产品,收费的。常应用在银行系统中.
SQLServer：MicroSoft 公司收费的中型的数据库。C#、.net等语言常使用。
SyBase：已经淡出历史舞台。提供了一个非常专业数据建模的工具PowerDesigner。
SQLite：嵌入式的小型数据库，应用在手机端。
常用数据库：MYSQL，Oracle．
这里使用MySQL数据库。MySQL中可以有多个数据库，数据库是真正存储数据的地方。
第2章 MySql数据库
2.1 MySql安装
安装
参考MySQL安装图解.doc
安装后，MySQL会以windows服务的方式为我们提供数据存储功能。开启和关闭服务的操作：右键点击我的电脑
→管理→服务→可以找到MySQL服务开启或停止。
也可以在DOS窗口，通过命令完成MySQL服务的启动和停止（必须以管理员身份运行cmd命令窗口）
2.2 登录MySQL数据库
MySQL是一个需要账户名密码登录的数据库，登陆后使用，它提供了一个默认的root账号，使用安装时设置的密码
即可登录。
开启mysql服务:net start mysql
关闭mysql服务:net stop mysql
格式1：cmd> mysql –u用户名 –p密码
例如：mysql -uroot –proot第3章 SQL语句
3.1 SQL概述
SQL语句介绍
数据库是不认识JAVA语言的，但是我们同样要与数据库交互，这时需要使用到数据库认识的语言SQL语句，它是数
据库的代码。 结构化查询语言(Structured Query Language)简称SQL，是关系型数据库管理系统都需要遵循的规
范。不同的数据库生产厂商都支持SQL语句，但都有特有内容。
SQL语句分类
SQL分类：
数据定义语言：简称DDL(Data Defifinition Language)，用来定义数据库对象：数据库，表，列等。关键
字：create，alter，drop等
数据操作语言：简称DML(Data Manipulation Language)，用来对数据库中表的记录进行更新。关键
字：insert，delete，update等
数据控制语言：简称DCL(Data Control Language)，用来定义数据库的访问权限和安全级别，及创建用
户。
数据查询语言：简称DQL(Data Query Language)，用来查询数据库中表的记录。关键字：select，
from，where等
SQL通用语法
SQL语句可以单行或多行书写，以分号结尾
可使用空格和缩进来增强语句的可读性
MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
例如：SELECT * FROM user。
同样可以使用/**/的方式完成注释
MySQL中的我们常使用的数据类型如下
格式2：cmd> mysql --host=ip地址 --user=用户名 --password=密码
例如：mysql --host=127.0.0.1 --user=root --password=root类型名称
说明
int（integer）
整数类型
double
小数类型
decimal（m,d）
指定整数位与小数位长度的小数类型
date
日期类型，格式为yyyy-MM-dd，包含年月日，不包含时分秒
datetime
日期类型，格式为 YYYY-MM-DD HH:MM:SS，包含年月日时分秒
timestamp
日期类型，时间戳
varchar（M）
文本类型， M为0~65535之间的整数
3.2 DDL之数据库操作：database
创建数据库
格式：
例如：
###
查看数据库
查看数据库MySQL服务器中的所有的数据库:
查看某个数据库的定义的信息:
1.创建数据库
2.使用数据库
3.查看某个数据库的定义的信息
4.查看所有数据库
5.查看正在使用的数据库
6.删除数据库
create database 数据库名;
create database 数据库名 character set 字符集;
#创建数据库 数据库中数据的编码采用的是安装数据库时指定的默认编码 utf8
CREATE DATABASE webdb_1;
#创建数据库 并指定数据库中数据的编码
CREATE DATABASE webdb_2 CHARACTER SET utf8;
show databases;例如：
删除数据库
例如：
使用数据库
查看正在使用的数据库:
其他的数据库操作命令
切换数据库：
3.3 DDL之表操作：table
###
创建表
格式：
show create database 数据库名;
show create database webdb_1;
drop database 数据库名称;
drop database webdb_2;
select database();
use 数据库名;
例如：
use webdb_1;
1.创建数据库表
创建分类表category
id name
2.设置自动增长从多少开始
ALTER TABLE category AUTO_INCREMENT=200;例如：创建分类表
查看表
查看数据库中的所有表：
查看表结构：
删除表
格式：drop table 表名;
修改表结构格式
alter table 表名 add 列名 类型(长度) [约束];
作用：修改表添加列.
alter table 表名 modify 列名 类型(长度) 约束;
create table 表名(
字段名 类型(长度) [约束],
字段名 类型(长度) [约束],
...
);
类型：
varchar(n) 字符串
int 整形
double 浮点
date 时间
timestamp 时间戳
约束：(详情查看第四章)
primary key 主键，被主键修饰字段中的数据，不能重复、不能为null。
CREATE TABLE category (
cid INT primary key, #分类ID
cname VARCHAR(100) #分类名称
);
格式：show tables;
格式：desc 表名;
例如：desc category;
例如：drop table category;
例如：#1，为分类表添加一个新的字段为 分类描述 varchar(20)
ALTER TABLE category ADD `desc` VARCHAR(20);作用：修改表修改列的类型长度及约束.
alter table 表名 change 旧列名 新列名 类型(长度) 约束; 作用：修改表修改列名.
alter table 表名 drop 列名;
作用：修改表删除列.
rename table 表名 to 新表名;
作用：修改表名
alter table 表名 character set 字符集(了解);
作用：修改表的字符集
3.4 DML数据操作语言
插入表记录：insert
语法：
注意：
值与字段必须对应，个数相同，类型相同
值的数据大小必须在字段的长度范围内
除了数值类型外，其它的字段类型的值必须使用引号引起。（建议单引号）
如果要插入空值，可以不写字段，或者插入null。
例如：
例如：#2, 为分类表的描述字段进行修改，类型varchar(50) 添加约束 not null
ALTER TABLE category MODIFY `desc` VARCHAR(50) NOT NULL;
例如：#3, 为分类表的分类名称字段进行更换 更换为 description varchar(30)
ALTER TABLE category CHANGE `desc` description VARCHAR(30);
例如：#4, 删除分类表中description这列
ALTER TABLE category DROP description;
例如：#5, 为分类表category 改名成 category2
RENAME TABLE category TO category2;
例如：#6, 为分类表 category 的编码表进行修改，修改成 gbk
ALTER TABLE category CHARACTER SET gbk;
-- 向表中插入某些字段
insert into 表 (字段1,字段2,字段3..) values (值1,值2,值3..);
-- 向表中插入所有字段,字段的顺序为创建表时的顺序
insert into 表 values (值1,值2,值3..);更新表记录：update
用来修改指定条件的数据，将满足条件的记录指定列修改为指定值
语法：
更新所有记录的指定字段
更新符合条件记录的指定字段
注意：
列名的类型与修改的值要一致.
修改值得时候不能超过最大长度.
除了数值类型外，其它的字段类型的值必须使用引号引起
删除记录：delete
语法：
3.5 DOS操作数据乱码解决
我们在dos命令行操作中文时，会报错
错误原因:因为mysql的客户端设置编码是utf8,而系统的cmd窗口编码是gbk
1. 查看MySQL内部设置的编码
2. 需要修改client、connection、results的编码一致（GBK编码）
INSERT INTO category(cid,cname) VALUES('c001','电器');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
INSERT INTO category(cid,cname) VALUES('c004','书籍');
INSERT INTO category(cid) VALUES('c005');
INSERT INTO category(cname,cid) VALUES('耗材','c006');
update 表名 set 字段名=值,字段名=值,...;
update 表名 set 字段名=值,字段名=值,... where 条件;
delete from 表名 [where 条件];
insert into category(cid,cname) values(‘c010’,’中文’);
ERROR 1366 (HY000): Incorrect string value: '\xB7\xFE\xD7\xB0' for column 'cname' at
row 1
show variables like 'character%'; 查看所有mysql的编码解决方案1：在cmd命令窗口中输入命令，此操作当前窗口有效，为临时方案。
解决方案2：安装目录下修改my.ini文件，重启服务所有地方生效。
第4章 SQL约束
4.1 主键约束
PRIMARY KEY 约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表都应该有一个主键，并且每个表只能有一个主键。
添加主键约束
方式一：创建表时，在字段描述处，声明指定字段为主键：
方式二：创建表时，在constraint约束区域，声明指定字段为主键：
格式： [constraint 名称] primary key (字段列表)
关键字constraint可以省略，如果需要为主键命名，constraint不能省略，主键名称一般没用。
字段列表需要使用小括号括住，如果有多字段需要使用逗号分隔。声明两个以上字段为主键，我们称为
联合主键。
方式三：创建表之后，通过修改表结构，声明指定字段为主键：
格式： ALTER TABLE persons ADD [CONSTRAINT 名称] PRIMARY KEY (字段列表)
set names gbk;
CREATE TABLE persons
(
id_p int PRIMARY KEY,
lastname varchar(255),
firstname varchar(255),
address varchar(255),
city varchar(255)
)
CREATE TABLE persons
(
firstname varchar(255),
lastname varchar(255),
address varchar(255),
city varchar(255),
CONSTRAINT pk_personID PRIMARY KEY (firstname,lastname)
)删除主键约束
如需撤销 PRIMARY KEY 约束，请使用下面的 SQL：
4.2 自动增长列
我们通常希望在每次插入新记录时，数据库自动生成字段的值。
我们可以在表中使用 auto_increment（自动增长列）关键字，自动增长列类型必须是整形，自动增长列必须为键
(一般是主键)。
下列 SQL 语句把 "persons" 表中的 "p_id" 列定义为 auto_increment 主键
向persons添加数据时，可以不为p_id字段设置值，也可以设置成null，数据库将自动维护主键值：
扩展：默认AUTO_INCREMENT 的开始值是 1，如果希望修改起始值，请使用下列 SQL 语法：
面试题
问：针对auto_increment ，删除表中所有记录使用 delete from 表名 或使用 truncate table 表名，二者有什么区
别？
CREATE TABLE persons
(
firstname varchar(255),
lastname varchar(255),
address varchar(255),
city varchar(255)
)
ALTER TABLE persons ADD PRIMARY KEY (firstname,lastname)
ALTER TABLE persons DROP PRIMARY KEY
CREATE TABLE persons
(
p_id int PRIMARY KEY AUTO_INCREMENT,
lastname varchar(255),
firstname varchar(255),
address varchar(255),
city varchar(255)
)
INSERT INTO persons (firstname,lastname) VALUES ('Bill','Gates')
INSERT INTO persons (p_id,firstname,lastname) VALUES (NULL,'Bill','Gates')
ALTER TABLE persons AUTO_INCREMENT=1004.3 非空约束
NOT NULL 约束强制列不接受 NULL 值。
NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。
下面的 SQL 语句强制 "id_p" 列和 "lastname" 列不接受 NULL 值：
4.4 唯一约束
UNIQUE 约束唯一标识数据库表中的每条记录。 UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保
证。 PRIMARY KEY 拥有自动定义的 UNIQUE 约束。 请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能
有一个 PRIMARY KEY 约束。
添加唯一约束
与主键添加方式相同，共有3种
方式一：创建表时，在字段描述处，声明唯一：
方式二：创建表时，在约束区域，声明唯一：
删除方式：
delete 一条一条删除，不清空auto_increment记录数。
truncate 直接将表删除，重新建表，auto_increment将置为零，从新开始。
CREATE TABLE persons
(
id_p int NOT NULL,
lastname varchar(255) NOT NULL,
firstname varchar(255),
address varchar(255),
city varchar(255)
)
CREATE TABLE persons
(
id_p int UNIQUE,
lastname varchar(255) NOT NULL,
firstname varchar(255),
address varchar(255),
city varchar(255)
)方式三：创建表后，修改表结构，声明字段唯一：
删除唯一约束
如需撤销 UNIQUE 约束，请使用下面的 SQL：
如果添加唯一约束时，没有设置约束名称，默认是当前字段的字段名。
第5章 MySQL数据库密码重置(扩展)
1. 停止mysql服务器运行输入services.msc 停止mysql服务
2. 在cmd下,输入mysqld --console --skip-grant-tables 启动服务器,出现一下页面,不要关闭该窗口
3. 新打开cmd,输入mysql -uroot 不需要密码
4. 关闭两个cmd窗口
CREATE TABLE persons
(
id_p int,
lastname varchar(255) NOT NULL,
firstname varchar(255),
address varchar(255),
city varchar(255),
CONSTRAINT 名称 UNIQUE (Id_P)
)
ALTER TABLE persons ADD [CONSTRAINT 名称] UNIQUE (Id_P)
ALTER TABLE persons DROP INDEX 名称
use mysql;
update user set password=password('abc') WHERE user='root';
day17-mysql查询DQL&多表关系
第一章 SQL语句(DQL)
1.1 DQL准备工作和语法
准备工作
#创建商品表：
pid int
pname varchar(20)
price double
category_id varchar(32)
插入一条数据:
pid=1,pname='联想',price=5000,category_id='c001'
create table product(
pid int primary key,
pname varchar(20),
price double,
category_id varchar(32)
);
INSERT INTO product(pid,pname,price,category_id) VALUES(1,'联想',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(2,'海尔',3000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(3,'雷神',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(4,'JACK JONES',800,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(5,'真维斯',200,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(6,'花花公子',440,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(7,'劲霸',2000,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(8,'香奈儿',800,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(9,'相宜本草',200,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(10,'面霸',5,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(11,'好想你枣',56,'c004');
INSERT INTO product(pid,pname,price,category_id) VALUES(12,'香飘飘奶茶',1,'c005');语法
1.2 简单查询
练习
1.3 条件查询
INSERT INTO product(pid,pname,price,category_id) VALUES(13,'果9',1,NULL);
select [distinct]
* | 列名,列名
from 表
where 条件
#查询所有的商品.
select * from product;
#查询商品名和商品价格.
select pname,price from product;
#别名查询.使用的关键字是as（as可以省略的）.表别名:
select * from product as p;
#别名查询.使用的关键字是as（as可以省略的）.列别名:
select pname as pn from product;
#去掉重复值.
select distinct price from product;
#查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示.
select pname,price+10 from product;比较运
算符
< <= = != <>
大于、小于、大于(小于)等于、不等于
BETWEEN
...AND...
显示在某一区间的值(含头含尾)
IN(set)
显示在in列表中的值，例：in(100,200)
LIKE ‘张
pattern’
模糊查询，Like语句中，% 代表零个或多个任意字符，_ 代表一个字符， 例
如： first_name like '_a%';
IS NULL
判断是否为空
逻辑运
行符
and
多个条件同时成立
or
多个条件任一成立
not
不成立，例： where not(salary>100);
练习
#查询商品名称为“花花公子”的商品所有信息：
SELECT * FROM product WHERE pname = '花花公子'
#查询价格为800商品
SELECT * FROM product WHERE price = 800
#查询价格不是800的所有商品
SELECT * FROM product WHERE price != 800
SELECT * FROM product WHERE price <> 800
SELECT * FROM product WHERE NOT(price = 800)
#查询商品价格大于60元的所有商品信息
SELECT * FROM product WHERE price > 60;
#查询商品价格在200到1000之间所有商品
SELECT * FROM product WHERE price >= 200 AND price <=1000;
SELECT * FROM product WHERE price BETWEEN 200 AND 1000;
#查询商品价格是200或800的所有商品
SELECT * FROM product WHERE price = 200 OR price = 800;
SELECT * FROM product WHERE price IN (200,800);1.4 排序查询
通过order by语句，可以将查询出的结果进行排序。暂时放置在select语句的最后。
格式:
练习：
1.5 聚合查询
之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是
对一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。
今天我们学习如下五个聚合函数：
count：统计指定列不为NULL的记录行数；
sum：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
#查询含有'霸'字的所有商品
SELECT * FROM product WHERE pname LIKE '%霸%';
#查询以'香'开头的所有商品
SELECT * FROM product WHERE pname LIKE '香%';
#查询第二个字为'想'的所有商品
SELECT * FROM product WHERE pname LIKE '_想%';
#商品没有分类的商品
SELECT * FROM product WHERE category_id IS NULL
#查询有分类的商品
SELECT * FROM product WHERE category_id IS NOT NULL
SELECT * FROM 表名 ORDER BY 排序字段 ASC|DESC;
#ASC 升序 (默认)
#DESC 降序
#查询id 为1 3 5 7的商品 并按价格降序排列
#使用价格排序(降序)
SELECT * FROM product ORDER BY price DESC;
#在价格排序(降序)的基础上，以分类排序(降序)
SELECT * FROM product ORDER BY price DESC,category_id DESC;
#显示商品的价格(去重复)，并排序(降序)
SELECT DISTINCT price FROM product ORDER BY price DESC;max：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
min：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
avg：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；
练习：
1.6 分组查询
分组查询是指使用group by字句对查询信息进行分组。
格式：
分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条件。
having与where的区别:
having是在分组后对数据进行过滤.
where是在分组前对数据进行过滤
having后面可以使用聚合函数(统计函数)
where后面不可以使聚合函数。
#查询商品的总条数
SELECT COUNT(*) FROM product;
#查询商品的价格和
select sum(price) as 'totalprice' FROM product;
#查询价格大于200商品的总条数
SELECT COUNT(*) FROM product WHERE price > 200;
#查询分类为'c001'的所有商品的总和
SELECT SUM(price) FROM product WHERE category_id = 'c001';
#查询分类为'c002'所有商品的平均价格
SELECT AVG(price) FROM product WHERE category_id = 'c002';
#查询商品的最大价格和最小价格
SELECT MAX(price),MIN(price) FROM product;
#查询pid为1 3 7 商品价格的平均值
#查询pid为1 3 7 14 商品价格的平均值
SELECT 字段1,字段2… FROM 表名 GROUP BY分组字段 HAVING 分组条件;#统计每门课程的平均分
#统计每门课程的平均分,且只显示平均分>70分的信息
练习：
#统计各个分类商品的平均价格
SELECT category_id,AVG(price) FROM product GROUP BY category_id;
#统计各个分类商品的平均价格,且只显示平均价格>800的信息
SELECT category_id,AVG(price) FROM product GROUP BY category_id HAVING AVG(price)>800;
第二章 SQLyog（MySQL图形化开发工具）
/*
学生成绩表scores:
sid: 学生成绩的id
score: 成绩
sname: 课程
*/
CREATE TABLE scores(
sid INT PRIMARY KEY,
score INT,
sname VARCHAR(200)
);
INSERT INTO scores(sid,score,sname) VALUES(1,90,'语文');
INSERT INTO scores(sid,score,sname) VALUES(2,70,'数学');
INSERT INTO scores(sid,score,sname) VALUES(3,90,'外语');
INSERT INTO scores(sid,score,sname) VALUES(4,50,'语文');
INSERT INTO scores(sid,score,sname) VALUES(5,90,'数学');
INSERT INTO scores(sid,score,sname) VALUES(6,80,'外语');
INSERT INTO scores(sid,score,sname) VALUES(7,100,'语文');
INSERT INTO scores(sid,score,sname) VALUES(8,20,'数学');
INSERT INTO scores(sid,score,sname) VALUES(9,100,'外语');
INSERT INTO scores(sid,score,sname) VALUES(10,75,'编程');
#查询所有
#查询所有课程和对应的成绩
#统计各个分类商品的个数
SELECT category_id ,COUNT(*) FROM product GROUP BY category_id ;
#统计各个分类商品的个数,且只显示个数大于1的信息
SELECT category_id ,COUNT(*) FROM product GROUP BY category_id HAVING COUNT(*) > 1;安装：
提供的SQLyog软件为免安装版，可直接使用
使用：
输入用户名、密码，点击连接按钮，进行访问MySQL数据库进行操作
在Query窗口中，输入SQL代码，选中要执行的SQL代码，按F8键运行，或按执行按钮运行。第三章 SQL备份与恢复
3.1 SQL备份
数据库的备份是指将数据库转换成对应的sql文件
MySQL命令备份
数据库导出sql脚本的格式：
例如:
以上备份数据库的命令中需要用户名和密码，即表明该命令要在用户没有登录的情况下使用
可视化工具备份
选中数据库，右键 ”备份/导出” ， 指定导出路径，保存成.sql文件即可。
mysqldump -u用户名 -p密码 数据库名>生成的脚本文件路径
mysqldump -uroot -proot day04>d:\day03.sql3.2 SQL恢复
数据库的恢复指的是使用备份产生的sql文件恢复数据库，即将sql文件中的sql语句执行就可以恢复数据库内容。
MySQL命令恢复
使用数据库命令备份的时候只是备份了数据库内容，产生的sql文件中没有创建数据库的sql语句，在恢复数据库之
前需要自己动手创建数据库。
在数据库外恢复
格式: mysql -uroot -p密码 数据库名 < 文件路径
例如: mysql -uroot -proot day03<d:\day03.sql
在数据库内恢复
格式: source SQL脚本路径
例如: source d:\day03.sql
注意:使用这种方式恢复数据，首先要登录数据库.
可视化工具恢复
数据库列表区域右键“从SQL转储文件导入数据库”， 指定要执行的SQL文件，执行即可。第四章 多表操作
实际开发中，一个项目通常需要很多张表才能完成。例如：一个商城项目就需要分类表(category)、商品表
(products)、订单表(orders)等多张表。且这些表的数据之间存在一定的关系，接下来我们将在单表的基础上，一
起学习多表方面的知识。
4.1 表与表之间的关系
一对多关系：
常见实例：客户和订单，分类和商品，部门和员工.一对多建表原则：在从表(多方)创建一个字段，字段作为外键指向主表(一方)的主键.
多对多关系：
常见实例：学生和课程、用户和角色
多对多关系建表原则：需要创建第三张表,中间表中至少两个字段，这两个字段分别作为外键指向各自一
方的主键.
一对一关系：(了解)
在实际的开发中应用不多.因为一对一可以创建成一张表.
两种建表原则：
外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一unique。
外键是主键：主表的主键和从表的主键，形成主外键关系。
4.2 外键约束
现在我们有两张表“分类表”和“商品表”，为了表明商品属于哪个分类，通常情况下，我们将在商品表上添加一列，
用于存放分类cid的信息，此列称为：外键此时“分类表category”称为：主表，“cid”我们称为主键。“商品表products”称为：从表，category_id称
为外键。我们通过主表的主键和从表的外键来描述主外键关系，呈现就是一对多关系。
外键特点：
从表外键的值是对主表主键的引用。
从表外键类型，必须与主表主键类型一致。
声明外键约束
使用外键目的：
保证数据完整性
4.3 一对多操作
分析
category分类表，为一方，也就是主表，必须提供主键cid
products商品表，为多方，也就是从表，必须提供外键category_id
语法：
alter table 从表 add [constraint 外键名称] foreign key 从表名(从表外键字段名) references 主表
名(主表的主键);
[外键名称]用于删除外键约束的，一般建议“_fk”结尾
alter table 从表 drop foreign key 外键名称实现：分类和商品
操作
4.4 多对多
分析
#创建分类表
create table category(
cid varchar(32) PRIMARY KEY ,
cname varchar(100) -- 分类名称
);
# 商品表
CREATE TABLE `products` (
`pid` varchar(32) PRIMARY KEY ,
`name` VARCHAR(40) ,
`price` DOUBLE
);
#添加外键字段
alter table products add column category_id varchar(32);
#添加约束
alter table products add constraint product_fk foreign key (category_id) references
category (cid);
#1 向分类表中添加数据
INSERT INTO category (cid ,cname) VALUES('c001','服装');
#2 向商品表添加普通数据,没有外键数据，默认为null
INSERT INTO products (pid,pname) VALUES('p001','商品名称');
#3 向商品表添加普通数据，含有外键信息(category表中存在这条数据)
INSERT INTO products (pid ,pname ,category_id) VALUES('p002','商品名称2','c001');
#4 向商品表添加普通数据，含有外键信息(category表中不存在这条数据) -- 失败,异常
INSERT INTO products (pid ,pname ,category_id) VALUES('p003','商品名称2','c999');
#5 删除指定分类(分类被商品使用) -- 执行异常
DELETE FROM category WHERE cid = 'c001';商品和订单多对多关系，将拆分成两个一对多。
products商品表，为其中一个一对多的主表，需要提供主键pid
orders 订单表，为另一个一对多的主表，需要提供主键oid
orderitem中间表，为另外添加的第三张表，需要提供两个外键oid和pid
实现：订单和商品
操作
#商品表[已存在]
#订单表
create table `orders`(
`oid` varchar(32) PRIMARY KEY ,
`totalprice` double #总计
);
#订单项表
create table orderitem(
oid varchar(50),-- 订单id
pid varchar(50)-- 商品id
);
#订单表和订单项表的主外键关系
alter table `orderitem` add constraint orderitem_orders_fk foreign key (oid) references
orders(oid);
#商品表和订单项表的主外键关系
alter table `orderitem` add constraint orderitem_product_fk foreign key (pid)
references products(pid);
#联合主键（可省略）
alter table `orderitem` add primary key (oid,pid);
#1 向商品表中添加数据
INSERT INTO products (pid,pname) VALUES('p003','商品名称');
#2 向订单表中添加数据
INSERT INTO orders (oid ,totalprice) VALUES('x001','998');
INSERT INTO orders (oid ,totalprice) VALUES('x002','100');#3向中间表添加数据(数据存在)
INSERT INTO orderitem(pid,oid) VALUES('p001','x001');
INSERT INTO orderitem(pid,oid) VALUES('p001','x002');
INSERT INTO orderitem(pid,oid) VALUES('p002','x002');
#4删除中间表的数据
DELETE FROM orderitem WHERE pid='p002' AND oid = 'x002';
#5向中间表添加数据(数据不存在) -- 执行异常
INSERT INTO orderitem(pid,oid) VALUES('p002','x003');
#6删除商品表的数据 -- 执行异常
DELETE FROM products WHERE pid = 'p001';
day18-mysql多表查询&原生jdbc
第一章 多表关系实战
1.1 实战1：省和市
方案1：多张表，一对多
方案2：一张表，自关联一对多
id=1name='北京'
p_id = null;
id=2
name='昌平'
p_id=1
id=3
name='大兴'
p_id=1
id=3
name='上海'
p_id=null
id=4
name='浦东'
p_id=3
1.2 实战2：用户和角色
多对多关系
第二章 多表查询
提供表结构如下：2.1 初始化数据
2.2 多表查询
1. 交叉连接查询(基本不会使用-得到的是两个表的乘积) [了解]
# 分类表
CREATE TABLE category (
cid VARCHAR(32) PRIMARY KEY ,
cname VARCHAR(50)
);
#商品表
CREATE TABLE products(
pid VARCHAR(32) PRIMARY KEY ,
pname VARCHAR(50),
price INT,
flag VARCHAR(2), #是否上架标记为：1表示上架、0表示下架
cid VARCHAR(32),
CONSTRAINT products_fk FOREIGN KEY products(cid) REFERENCES category (cid)
);
alter table products add CONSTRAINT products_fk FOREIGN KEY products(cid) REFERENCES
category (cid);
#分类
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
#商品
INSERT INTO products(pid, pname,price,flag,cid) VALUES('p001','联想',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,cid) VALUES('p002','海尔',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,cid) VALUES('p003','雷神',5000,'1','c001');
INSERT INTO products (pid, pname,price,flag,cid) VALUES('p004','JACK
JONES',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,cid) VALUES('p005','真维斯',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,cid) VALUES('p006','花花公
子',440,'1','c002');
INSERT INTO products (pid, pname,price,flag,cid) VALUES('p007','劲霸',2000,'1','c002');
INSERT INTO products (pid, pname,price,flag,cid) VALUES('p008','香奈儿',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,cid) VALUES('p009','相宜草',200,'1','c003');语法： select * from A,B;
2. 内连接查询(使用的关键字 inner join -- inner可以省略)
隐式内连接： select * from A,B where 条件;
显示内连接： select * from A inner join B on 条件;
#1.查询所有商品的记录,要求显示所属的分类信息
#2.只查询所有化妆品的记录,要求显示所属的分类信息
#3.查询哪些分类的商品已经上架
#4.查询每种分类商品的个数
#隐式内连接
SELECT DISTINCT c.cname
FROM category c , products p
WHERE c.cid = p.cid AND p.flag = '1';
#内连接
SELECT DISTINCT c.cname
FROM category c INNER JOIN products p
ON c.cid = p.cid
WHERE p.flag = '1';
3. 外连接查询(使用的关键字 outer join -- outer可以省略)
左外连接：left outer join
select * from A left outer join B on 条件;
右外连接：right outer join
select * from A right outer join B on 条件;
#1.查询所有商品的记录,要求显示所属的分类信息
#2.只查询所有化妆品的记录,要求显示所属的分类信息
#3.查询哪些分类的商品已经上架
#4.查询每种分类商品的个数
#左外连接
INSERT INTO category(cid,cname) VALUES('c004','奢侈品');
SELECT cname,COUNT(cid)
FROM category c LEFT OUTER JOIN products p
ON c.cid = p.cid
GROUP BY cname;下面通过一张图说明连接的区别:
2.3 子查询
子查询：一条select语句结果作为另一条select语法一部分（查询条件，查询结果，表等）。 语法： select ....
查询字段 ... from ... 表.. where ... 查询条件
#3 子查询, 查询“化妆品”分类上架商品详情
#隐式内连接
SELECT p.*
FROM products p , category c
WHERE p.cid=c.cid AND c.cname = '化妆品';
#子查询
##作为查询条件
SELECT *
FROM products p
WHERE p.cid =
(
SELECT c.cid FROM category c
WHERE c.cname='化妆品'
);
##作为另一张表
SELECT *
FROM products p ,
(SELECT * FROM category WHERE cname='化妆品') c子查询练习：
第三章 JDBC
3.1 JDBC概述
JDBC（Java DataBase Connectivity,java数据库连接）是一种用于执行SQL语句的Java API。JDBC是Java访问数据
库的标准规范，可以为不同的关系型数据库提供统一访问，它由一组用Java语言编写的接口和类组成。
JDBC需要连接驱动，驱动是两个设备要进行通信，满足一定通信数据格式，数据格式由设备提供商规定，设备提
供商为设备提供驱动软件，通过软件可以与该设备进行通信。 今天我们使用的是mysql的驱动mysql-connector
java-5.1.37-bin.jar
WHERE p.cid = c.cid;
#查询“化妆品”和“家电”两个分类上架商品详情
SELECT *
FROM products p
WHERE p.cid in
(SELECT c.cid
FROM category c
WHERE c.cname='化妆品' or c.name='家电'
);JDBC规范（掌握四个核心对象）：
DriverManager:用于注册驱动
Connection: 表示与数据库创建的连接
Statement: 操作数据库sql语句的对象
ResultSet: 结果集或一张虚拟表
3.2 JDBC原理
Java提供访问数据库规范称为JDBC，而生产厂商提供规范的实现类称为驱动。
JDBC是接口，驱动是接口的实现，没有驱动将无法完成数据库连接，从而不能操作数据库！每个数据库厂商都需
要提供自己的驱动，用来连接自己公司的数据库，也就是说驱动一般都由数据库生成厂商提供。3.3 JDBC入门案例
准备数据
之前我们学习了sql语句的使用，并创建的分类表category，今天我们将使用JDBC对分类表进行增删改查操作。
导入驱动jar包
创建lib目录，存放mysql的驱动mysql-connector-java-5.1.37-bin.jar
选中mysql的jar包，右键选择“ Add as Library...” 完成jar导入
开发步骤
1. 注册驱动.
2. 获得连接.
3. 获得执行sql语句的对象
4. 执行sql语句，并返回结果
5. 处理结果
6. 释放资源.
案例实现
#创建数据库
create database day04;
#使用数据库
use day04;
#创建分类表
create table category(
cid int PRIMARY KEYAUTO_INCREMENT ,
cname varchar(100)
);
#初始化数据
insert into category (cname) values('家电');
insert into category (cname) values('服饰');
insert into category (cname) values('化妆品');
//查询所有的分类信息
@Test
public void demo1() throws Exception{3.4 API详解
API详解：注册驱动
DriverManager.registerDriver(new com.mysql.jdbc.Driver()); 不建议使用，原因有2个：
导致驱动被注册2次
强烈依赖数据库的驱动jar
解决办法：
Class.forName("com.mysql.jdbc.Driver");
API详解：获得链接
static Connection getConnection(String url, String user, String password) :试图建立到给定数据
库 URL 的连接。
参数说明：
url 需要连接数据库的位置（网址）
user用户名
password 密码
例如： getConnection("jdbc:mysql://localhost:3306/day04", "root", "root");
扩展：
URL:SUN公司与数据库厂商之间的一种协议。
// 注意：使用JDBC规范，采用都是 java.sql包下的内容
//1 注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2 获得连接
String url = "jdbc:mysql://localhost:3306/mydb";
Connection conn = DriverManager.getConnection(url, "root", "root");
//3获得执行sql语句的对象
Statement stmt = conn.createStatement();
//4执行SQL语句
ResultSet rs = stmt.executeQuery("select * from category");
//5处理结果集
while(rs.next()){
// 获得一行数据
Integer cid = rs.getInt("cid");
String cname = rs.getString("cname");
System.out.println(cid + " , " + cname);
}
//6释放资源
rs.close();
stmt.close();
conn.close();
}协议子协议 IP :端口号数据库
mysql: jdbc:mysql://localhost:3306/day04 或者 jdbc:mysql:///day04（默认
本机连接）
oracle数据库: jdbc:oracle:thin:@localhost:1521:sid
API详解：java.sql.Connection接口：一个连接
接口的实现在数据库驱动中。所有与数据库交互都是基于连接对象的。
Statement createStatement(); //创建操作sql语句的对象
API详解：java.sql.Statement接口: 操作sql语句，并返回相应结果
常用方法：
int executeUpdate(String sql); --执行insert update delete语句.
ResultSet executeQuery(String sql); --执行select语句.
boolean execute(String sql); --仅当执行select并且有结果时才返回true，执行其他的语句返回false.
API详解：处理结果集（注：执行insert、update、delete无需处理）
ResultSet实际上就是一张二维的表格，我们可以调用其 boolean next() 方法指向某行记录，当第一次调用
next() 方法时，便指向第一行记录的位置，这时就可以使用ResultSet提供的 getXXX(int col) 方法来获取指
定列的数据：(与数组索引从0开始不同，这里索引从1开始)
常用方法：
Object getObject(int index) / Object getObject(String name) 获得任意对象
String getString(int index) / String getString(String name) 获得字符串
int getInt(int index) / int getInt(String name) 获得整形
double getDouble(int index) / double getDouble(String name) 获得双精度浮点型
API详解：释放资源
与IO流一样，使用后的东西都需要关闭！关闭的顺序是先得到的后关闭，后得到的先关闭。
3.5 JDBC工具类
jdbc:mysql://localhost:3306/day04
String sql = "某SQL语句";
获取Statement语句执行平台：Statement stmt =con.createStatement();
rs.next();//指向第一行
rs.getInt(1);//获取第一行第一列的数据
rs.close();
stmt.close();
con.close();“获得数据库连接”操作，将在以后的增删改查所有功能中都存在，可以封装工具类JDBCUtils。提供获取连接对象的
方法，从而达到代码的重复利用。
该工具类提供方法： public static Connection getConnection() 。代码如下：
public class JdbcUtils {
private static String driver = "com.mysql.jdbc.Driver";
private static String url = "jdbc:mysql://localhost:3306/webdb_4";
private static String user = "root";
private static String password = "root";
static{
try {
//注册驱动
Class.forName(driver);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
/**
* 获得连接
* @return
* @throws SQLException
*/
public static Connection getConnection() throws SQLException{
//获得连接
Connection conn = DriverManager.getConnection(url, user, password);
return conn;
}
/**
* 释放资源
* @param conn
* @param st
* @param rs
*/
public static void closeResource(Connection conn , Statement st , ResultSet rs){
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
}
}
if(st != null){
try {
st.close();
} catch (SQLException e) {
}3.6 JDBC增删改查操作
插入
修改
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
}
}
}
}
@Test
public void demo01(){
//添加
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
//1 获得连接
conn = JdbcUtils.getConnection();
//操作
//1) 获得语句执行者
st = conn.createStatement();
//2) 执行sql语句
int r = st.executeUpdate("insert into category(cname) values('测试')");
//3) 处理结果
System.out.println(r);
} catch (Exception e) {
throw new RuntimeException(e);
} finally{
//释放资源
JdbcUtils.closeResource(conn, st, rs);
}
}
@Test
public void demo02(){删除
通过id查询详情
//修改
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
int r = st.executeUpdate("update category set cname='测试2' where cid = 4");
System.out.println(r);
} catch (Exception e) {
throw new RuntimeException(e);
} finally{
JdbcUtils.closeResource(conn, st, rs);
}
}
@Test
public void demo03(){
//删除
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//操作
st = conn.createStatement();
int r = st.executeUpdate("delete from category where cid = 4");
System.out.println(r);
} catch (Exception e) {
throw new RuntimeException(e);
} finally{
JdbcUtils.closeResource(conn, st, rs);
}
}
@Test
public void demo04(){
//通过id查询详情
Connection conn = null;查询所有
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//操作
st = conn.createStatement();
rs = st.executeQuery("select * from category where cid = 30");
if(rs.next()){
String cid = rs.getString("cid");
String cname = rs.getString("cname");
System.out.println(cid + " @ " + cname );
} else {
System.out.println("没有数据");
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally{
JdbcUtils.closeResource(conn, st, rs);
}
}
@Test
public void demo05(){
//查询所有
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//操作
st = conn.createStatement();
rs = st.executeQuery("select * from category");
while(rs.next()){
String cid = rs.getString("cid");
String cname = rs.getString("cname");
System.out.println(cid + " @ " + cname );
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally{
JdbcUtils.closeResource(conn, st, rs);
}}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
