1. MYSQL的回顾:
1.1 MySQL的概述:
1.1.1 什么是数据库:
数据库:就是一个文件系统,这个文件必须通过标准的SQL访问.
1.1.2什么是关系型数据库:
关系型的数据库存放的都是实体之间的关系.
![](https://i-blog.csdnimg.cn/blog_migrate/a6f8d16cc9496ee60ef636435f91e884.jpeg)
1.1.3 常用的关系型数据库:
MySQL:免费的小型的数据库,现在被Oracle收购.
Oracle:Oracle公司收费的大型的数据库.
SQLServer :微软公司收费中型的数据库.
DB2:IBM公司收费的大型的数据库.
SyBase:SyBase公司收费的数据库.已经被淘汰.PowerDesigner数据建模的工具.
SQLite:小型的嵌入式的数据库.
***** Java程序中经常使用的数据库
MySQL
Oracle
1.1.4数据库存储的结构:
1.2. SQL的概述:
1.2.1 什么是SQL:
SQL:结构化的查询语言.
1.2.2 SQL分类:
DDL:数据定义语言
* create,alter,drop...
DML:数据操纵语言
* update,insert,delete
DCL:数据控制语言
* grant,if..
DQL:数据查询语言
* select
1.2.3 SQL的特点:
非过程性语言:一条语句就会有一个运行的结果.
1.3 使用SQL
1.3.1 使用SQL操作数据库(对数据库的CRUD的操作)
【创建数据库】
语法:
* create database 数据库名称 [character set 字符集 collate 字符集校对];
练习:
* 创建db1;
[SQL]
纯文本查看
复制代码
1
|
create
database
db1;
|
* 创建一个带有字符集的数据库db2;
[SQL]
纯文本查看
复制代码
1
|
create
database
db2
character
set
gbk;
|
* 创建一个带有字符集和校对规则的数据库db3;
[SQL]
纯文本查看
复制代码
1
|
create
database
db3
character
set
utf8
collate
utf8_bin;
|
【查看数据库】
语法:
* 查看数据库服务器中所有的数据库:
[SQL]
纯文本查看
复制代码
1
|
show databases;
|
* 查看某个数据库的定义信息.
[SQL]
纯文本查看
复制代码
1
|
show
create
database
数据库名;
|
* 查看当前正在使用的数据库信息.
[SQL]
纯文本查看
复制代码
1
|
select
database
();
|
【删除数据库】
语法:
* 删除数据库:
* drop database 数据库名;
【修改数据库】
语法:
* 修改数据库修改的是的数据库的字符集和校对规则.
* alter database 数据库名 character set 新字符集 collate 校对规则;
【切换数据库】
语法:
* use 数据库名称;
1.3.2 使用SQL操作数据库中的表(对数据库的表CRUD的操作)
【创建表】
- 语法:
字段名 类型(长度) 约束,
字段名 类型(长度) 约束,
字段名 类型(长度) 约束
);
- 数据类型:
* 区别:char是固定长度的字符串,varchar可变长度的字符串.
如果插入一个字符串hello 插入到char 那么 插入hello .插入到varchar中 插入hello
* datetime和timestamp都是既有日期又有时间的日期类型
区别:datetime需要使用外部传入的日期.如果没传这个值就是Null. timestamp会使用系统当前的时间作为这个值的默认值.
***** Oralce使用CLOB/BLOB
***** MYSQL中除了字符串类型需要设置长度其他的类型都有默认长度.
- 约束:
* 主键约束:primary key (默认就是唯一非空的)
* 唯一约束:unique
* 非空约束:not null
- 创建一个表:
[SQL]
纯文本查看
复制代码
1
2
3
4
5
6
7
8
|
create
table
employee(
eid
int
primary
key
auto_increment,
ename
varchar
(20)
not
null
,
email
varchar
(30)
unique
,
birthday
date
,
job
varchar
(20),
resume text
);
|
【表的查看】
- 查看数据库中有哪些表:
- 查看表结构:
【表的删除】
- 表的删除:
【表的修改】
- 修改表添加列:
[SQL]
纯文本查看
复制代码
1
|
alter
table
employee
add
image
varchar
(50);
|
- 修改表删除列:
[SQL]
纯文本查看
复制代码
1
|
alter
table
employee
drop
job;
|
- 修改表的列的类型长度及约束:
[SQL]
纯文本查看
复制代码
1
|
alter
table
employee
modify
image
varchar
(80)
not
null
;
|
- 修改表的列名
[SQL]
纯文本查看
复制代码
1
|
alter
table
employee change image eimage
varchar
(60);
|
- 修改表名
[SQL]
纯文本查看
复制代码
1
|
rename
table
employee
to
user
;
|
- 修改表的字符集:
[SQL]
纯文本查看
复制代码
1
|
[
size
=3]a[/
size
]lter
table
user
character
set
gbk;
|
1.3.3 使用SQL操作数据库中的表的记录(对表的记录的CRUD的操作)
【插入记录】
- 语法
* insert into 表名 values (值1,值2,...); ---插入所有列的值
- 注意事项:
* 列的类型与值的类型对应.位置也要对应.
* 列的类型如果是字符串或者日期,写值的时候使用单引号将值引起来.
* 插入的值的最大长度不能超过列的最大长度.
- 插入记录:
[SQL]
纯文本查看
复制代码
1
|
insert
into
employee (eid,ename,email)
values
(
null
,
'aaa'
,
'aaa@itcast.cn'
);
|
* 插入所有列的值:
[SQL]
纯文本查看
复制代码
1
|
insert
into
employee
values
(
null
,
'bbb'
,
'bbb@itcast.cn'
,
'1990-09-01'
,
'HR'
,
'I am HR'
);
|
- 插入中文:
[Shell]
纯文本查看
复制代码
1
|
insert into employee (eid,ename,email) values (null,
'张三'
,
'aaa@163.cn'
);
|
ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC8\xFD' for column 'ename' at row 1
***** 插入中文问题的解决:
[Shell]
纯文本查看
复制代码
1
|
show variables like
'%character%'
;
|
![](https://i-blog.csdnimg.cn/blog_migrate/5b552e7a3db64e3847bd4c0b7df3884f.jpeg)
***** 找到MYSQL的安装路径/my.ini文件:
![](https://i-blog.csdnimg.cn/blog_migrate/3a7167856451489321812649f9d7f6b8.jpeg)
**** 重新加载mysql的配置文件:
[Shell]
纯文本查看
复制代码
1
|
services.msc
|
* 停止mysql的服务,重新启动mysql服务.
* 执行之前的SQL语句.
【修改记录】
- 语法:
- 注意事项:
* 值不能超过列的最大长度.
* 值是字符串或日期,需要使用单引号.
- 练习:
[SQL]
纯文本查看
复制代码
1
|
update
employee
set
job=
'WORKER'
;
|
* 修改employee表将name为aaa的邮箱改为aaa@163.com
[SQL]
纯文本查看
复制代码
1
|
update
employee
set
email =
'aaa@163.com'
where
ename =
'aaa'
;
|
* 修改employee表将name为bbb的邮箱改为bbb@163.com同时修改job为HR
[SQL]
纯文本查看
复制代码
1
|
update
employee
set
email =
'bbb@163.com'
, job=
'HR'
where
ename=
'bbb'
;
|
【删除记录】
- 语法:
- 注意事项:
* 如果没有条件删除表中的所有列.
- 练习:
[SQL]
纯文本查看
复制代码
1
|
delete
from
employee
where
eid = 8;
|
* 删除所有记录:
[SQL]
纯文本查看
复制代码
1
|
delete
from
employee;
|
- 删除表中的所有记录truncate table 表名 和 delete from 表区别?
* truncate table 删除表的记录:将整个表删除掉,重新创建一个新的表.truncate属于DDL.
* delete from 删除表的记录:一条一条进行删除. delete属于DML。
* 事务管理 只能作用在DML语句上.如果再一个事务中使用delete删除所有记录,可以找回.
【基本查询】
- 查询语句:
- 准备:
[SQL]
纯文本查看
复制代码
1
2
3
4
5
6
7
|
create
table
exam(
id
int
primary
key
auto_increment,
name
varchar
(20),
english
int
,
chinese
int
,
math
int
);
|
[SQL]
纯文本查看
复制代码
1
2
3
4
5
6
|
insert
into
exam
values
(
null
,
'张三'
,85,74,91);
insert
into
exam
values
(
null
,
'李四'
,95,90,83);
insert
into
exam
values
(
null
,
'王五'
,85,84,59);
insert
into
exam
values
(
null
,
'赵六'
,75,79,76);
insert
into
exam
values
(
null
,
'田七'
,69,63,98);
insert
into
exam
values
(
null
,
'李四'
,89,90,83);
|
- 查询所有记录:
[SQL]
纯文本查看
复制代码
1
|
select
*
from
exam;
|
- 查询这个班级人的姓名和英语成绩:
[SQL]
纯文本查看
复制代码
1
|
select
name
,english
from
exam;
|
- 查询英语成绩,将重复英语成绩去掉:
[SQL]
纯文本查看
复制代码
1
|
select
distinct
english
from
exam;
|
- 查询李四的学生成绩:
[SQL]
纯文本查看
复制代码
1
|
select
*
from
exam
where
name
=
'李四'
;
|
- 查询名称叫李四并且英语成绩大于90的
[SQL]
纯文本查看
复制代码
1
|
elect *
from
exam
where
name
=
'李四'
and
english >90;
|
- 将成绩+10分进行显示:
[SQL]
纯文本查看
复制代码
1
|
select
name
,english+10,chinese+10 ,math+10
from
exam;
|
显示这个人的名称和对应总成绩的分数:
[SQL]
纯文本查看
复制代码
1
|
select
name
,english+chinese+math
from
exam;
|
- 使用as起别名,as可以省略.
[SQL]
纯文本查看
复制代码
1
|
select
name
, english+chinese+math
as
sum
from
exam;
|
【条件查询】
- where语句后面可以加:
= , > , >= , <, <= , <>
like中可以使用占位符: _ 和 % :下划线匹配一个字符, %:可以匹配任意多个字符.
* like ‘张%’; like ‘张_’; like ‘%明’; like ‘%明%’;
in 后跟着一组值.
* id in (1,2,3)
and or not
【排序查询】
- order by 对数据进行排序.默认升序. (asc升序,desc降序)
[SQL]
纯文本查看
复制代码
1
|
select
*
from
exam
order
by
chinese;
|
* 查询所有学生的信息,并且按语文成绩进行降序排序.
s
[SQL]
纯文本查看
复制代码
1
|
elect *
from
exam
order
by
chinese
desc
;
|
* 查询学生的信息,按照英语成绩降序排序,如果英语成绩相同,按照语文降序.
s
[SQL]
纯文本查看
复制代码
1
|
elect *
from
exam
order
by
english
desc
, chinese
desc
;
|
* 查询姓李的学生的信息,同时按照英语升序排序.
[SQL]
纯文本查看
复制代码
1
|
select
*
from
exam
where
name
like
'李%'
order
by
english
asc
;
|
【聚合函数】
sum()
count()
max()
min()
avg()
* 查询每个学生总成绩:
[SQL]
纯文本查看
复制代码
1
|
select
name
,(english+chinese+math)
from
exam;
|
* 统计所有学生的总分:
[SQL]
纯文本查看
复制代码
1
2
|
select
sum
(english+chinese+math)
from
exam;
-- ifnull(english,0)
select
sum
(english)+
sum
(chinese)+
sum
(math)
from
exam;
|
* 统计学生的个数:
[SQL]
纯文本查看
复制代码
1
|
select
count
(*)
from
exam;
|
* 统计英语成绩的最高分:
[SQL]
纯文本查看
复制代码
1
|
select
max
(english)
from
exam;
|
* 统计语文成绩的最低分:
[SQL]
纯文本查看
复制代码
1
|
select
min
(chinese)
from
exam;
|
* 统计英语成绩平均分:
[SQL]
纯文本查看
复制代码
1
|
select
avg
(english)
from
exam;
|
【分组】
group by
创建一个订单详情的表:
* 统计订单中的每类商品所购买的个数:
[SQL]
纯文本查看
复制代码
1
|
SELECT
product,
COUNT
(*)
FROM
orderitem
GROUP
BY
product;
|
* 统计订单中的每类商品所花的金额:
[SQL]
纯文本查看
复制代码
1
|
SELECT
product,
SUM
(price)
FROM
orderitem
GROUP
BY
product;
|
* 统计订单中的每类商品所花总金额大于2000信息.
[SQL]
纯文本查看
复制代码
1
|
[
size
=3]S[/
size
]ELECT product,
SUM
(price)
FROM
orderitem
GROUP
BY
product
HAVING
SUM
(price) > 2000;
|
* 统计订单中名称有电子的商品并且所花金额大于1500同时按照价格降序排序:
[SQL]
纯文本查看
复制代码
1
|
SELECT
product,
SUM
(price)
FROM
orderitem
WHERE
product
LIKE
'电%'
GROUP
BY
product
HAVING
SUM
(price) > 1500
ORDER
BY
SUM
(price)
DESC
;
|
【SQL的查询语句的总结】
顺序: s...f...w...g...h...o...;
2. 案例一:将商城的案例中的表结构进行分析:
2.1 需求:
在最后的综合案例中,会创建数据库,为数据库中创建很多表.表与表之间是有关系存在,分析表之间关系并且完成表的创建.
2.2 分析:
2.2.1 技术分析
【数据库的多表设计】
数据库都是关系型的数据库,存的是实体之间的关系.实体之间有哪些关系?
实体的关系总结起来就有三种关系:
一对多:
* 客户和订单:一个客户可以产生多个订单,一个订单只能属于是某一个客户.
* 部门和员工:一个部门下可以有多个员工,一个员工只能属于某一个部门.
多对多:
* 学生和课程:一个学生可以选择多门课程,一门课程可以被多个学生选择.
* 订单和商品:一个订单中包含多个商品,一个商品也可以出现多个订单中.
一对一:
* 公司和地址:一个公司只能有一个注册地址,一个地址也只能被一个公司注册.
【多表的设计】
- 一对多的关系的建表原则:
- 多对多的关系的建表原则:
- 一对一的关系的建表原则:
* 主键对应:将一对一的双方的主键建立映射.
【使用SQL创建一对多的关系】
创建客户表:
[SQL]
纯文本查看
复制代码
1
2
3
4
|
create
table
customer(
cid
int
primary
key
auto_increment,
cname
varchar
(20)
);
|
创建订单表
[SQL]
纯文本查看
复制代码
1
2
3
4
5
|
create
table
orders(
oid
int
primary
key
auto_increment,
addr
varchar
(50),
cid
int
);
|
***** 约束:用来保证数据的完成型.
* 多表约束:外键约束!!!
***** 给orders表中的cid添加外键约束.
[SQL]
纯文本查看
复制代码
1
|
alter
table
orders
add
foreign
key
(cid)
references
customer(cid);
|
2.3 商城模型分析:
![](https://i-blog.csdnimg.cn/blog_migrate/d8e2c91260af0914b3a325a2b2d32272.jpeg)
3 案例二:使用SQL完成多表的查询:
3.1 需求:
使用多表的查询,完成某个分类下的商品的显示.很多的情况下都需要使用多表的查询.
3.2 分析:
3.2.1 技术分析:
【多表的查询的SQL】
- 多表的查询的方式:
* select * from A,B; --- 获得的是两个表的笛卡尔积.
* 内连接: inner join -- inner 可以省略
* 显式内连接:select * from A inner join B on 条件;
[SQL]
纯文本查看
复制代码
1
|
SELECT
*
FROM
customer c
INNER
JOIN
orders o
ON
c.cid = o.cid;
|
* 隐式内连接:select * from A,B where 条件;
[SQL]
纯文本查看
复制代码
1
|
SELECT
*
FROM
customer c ,orders o
WHERE
c.cid = o.cid;
|
* 外连接:outer join -- outer 可以省略
* 左外连接:left outer join -- select * from A left outer join B on 条件;
[SQL]
纯文本查看
复制代码
1
|
SELECT
*
FROM
customer c
LEFT
OUTER
JOIN
orders o
ON
c.cid = o.cid;
|
* 右外连接:right outer join -- select * from A right outer join B on 条件;
[SQL]
纯文本查看
复制代码
1
|
SELECT
*
FROM
customer c
RIGHT
OUTER
JOIN
orders o
ON
c.cid = o.cid;
|
【多表查询的子查询】
- 一个SQL语句查询的过程中需要依赖另一个查询语句.
[SQL]
纯文本查看
复制代码
1
|
SELECT
*
FROM
customer c,orders o
WHERE
c.cid = o.cid
AND
c.cid
IN
(
SELECT
cid
FROM
orders
WHERE
addr
LIKE
'海淀%'
);
|
【多表练习】
按客户名称统计订单的个数.
[SQL]
纯文本查看
复制代码
1
|
SELECT
c.cname,
COUNT
(*)
FROM
customer c,orders o
WHERE
c.cid = o.cid
GROUP
BY
c.cname;
|