关闭

java程序员第十六课 -MySQL数据库(多表的查询)

标签: 数据库javaoraclemysql程序员
542人阅读 评论(0) 收藏 举报
分类:

课程回顾:MySQL数据库和SQL语言

数据库的概述

1.数据库的作用:仓库,存储数据。
2.关系型的数据库,保存实体与实体之间的关系。
3.常见的数据库
* Oracle MySQL SQLServer DB2
MySQL数据库的安装和卸载

1.看笔记
2.MySQL密码重置。文档
MySQL数据库的概念

1.数据库的服务器 – 多个数据库 – 每一个数据库中存在的多个表结构 – 创建表结构的时候需要指定字段 – JavaBean中的属性是吻合的 – 表结构中的一行数据对应的是一个JavaBean实体
SQL语言的分类

1.SQL语言的概述
2.非过程性的语言:不依赖上一条或者上几条语句,一条语句就会有执行的结果。
3.SQL的分类:
* DDL 数据定义语言 create alter drop
* DML 数据操作语言 delete insert update
* DCL 数据控制语言
* DQL 数据查询语言 select
数据库的CURD

1.创建数据库 create database 名称;
2.修改数据库 alter database 名称 character set ‘utf8’ collate ”;
3.删除数据库 drop database 名称;
4.查看数据库 show databases; show create database 名称; use 名称;
表结构的CURD

1.创建表
create table 名称(
字段 类型(长度) 约束,
字段 类型(长度) 约束
);
2.类型
char 固定长度 varchar 可变长度
int bigint float double
bit
date time datetime 传Null 就是null timestamp 取当前的时间

3.查看和删除表结构
* show tables;
* show create tables 名称;
* desc 名称;
* drop table 表名;

4.修改
* alter table 表名 add 字段 类型(长度);
* alter table 表名 drop 字段;
* alter table 表名 modify 字段 类型(长度);
* alter table 表名 change 旧字段 新字段 类型(长度)
* rename table 表名 to 新表名;
数据的CURD

1.添加数据
* insert into 表名 (字段1,字段2 ) values (值1,值2)
* insert into 表名 values (值1,值2,,值3)

2.修改数据
* update 表名 set 字段=值 , 字段=值 where 条件;

3.删除数据
* delete from 表名 where 条件;
* truncate 表名;
数据的查询

1.语法:
* select * | 字段1,字段2,,字段3 from 表 where 条件 order by 字段 asc/desc;
* 查询的字段支持运算
* where条件的符号
* <> 不等于
* in 范围
* like 模糊查询 like ‘张_’ ‘%张%’
* and or not
* between and

2.聚集函数:都是操作列的。
* count – 计算
* sum – 求和
* avg – 平均值
* max – 最大值
* min – 最小值
今天内容:MySQL数据库(多表的查询)

分组查询

1.分组查询:
* select * from stu; – 查询所有的学生的信息。默认分成一组。
* 可以使用group by sex 来进行分组。
* 条件过滤的关键字 having 一般都使用在分组语句的条件过滤。

2.练习
create database day16;
use day16;
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,'洗衣机',100);
insert into orders(id,product,price) values(5,'洗衣粉',90);
insert into orders(id,product,price) values(6,'洗衣粉',90);

+------+---------+-------+
| id   | product | price |
+------+---------+-------+
|    1 | 电视    |   900 |
|    2 | 洗衣机  |   100 |
|    3 | 洗衣粉  |    90 |
|    4 | 洗衣机  |   100 |
|    5 | 洗衣粉  |    90 |
|    6 | 洗衣粉  |    90 |
+------+---------+-------+

* 对订单表中商品归类后,显示每一类商品的总价(按着商品的名称进行分组,查询的是每一组的总价)
    select product,sum(price) from orders group by product;     
    select product,sum(price),count(*) from orders group by product;        

* 查询购买了几类商品,并且每类总价大于200的商品
    select product,sum(price) from orders group by product having sum(price) >200;
    select product,sum(price) from orders where price > 95 group by product having sum(price) > 300;

查询语句的顺序

select … from … where … group by … having … order by
数据库的备份和还原

1.如果使用工具来进行备份。
单表的约束(主键)

1.约束:保证数据的完整性。
2.主键约束(重要)
* 如果把某个字段声明成主键了,有如下的特点:
* 唯一
* 非空
* 被引用
* 声明主键,使用关键字 primary key
3.对主键进行测试
create table person(
id int primary key,
name varchar(50)
);
insert into person values (1,’美美’);
insert into person values (2,’小凤’);
insert into person values (3,’如花’);

4.主键自动增长
* 把主键的值交给数据库进行维护了。不用咱们去管理了。
* 使用关键字:auto_increment
* 主键的类型:int bigint

drop table person;
create table person(
    id int primary key auto_increment,
    name varchar(50)
);
insert into person values (null,'美美');  
insert into person values (null,'小凤');
insert into person values (null,'如花');

delete from person where id = 3;
insert into person values (null,'如花');

唯一和非空约束

1.唯一约束:声明值是唯一的值。但是允许为空。
* unique
2.非空约束:声明值不能是空的。
* not null
外键约束(多表)

1.作用:保证数据的完整性。
2.模拟例子
create table dept(
did int primary key auto_increment,
dname varchar(50)
);

create table emp(
    eid int primary key auto_increment,
    ename varchar(50),
    sal double,
    dno int
);

insert into dept values (1,'研发部');
insert into dept values (2,'人事部');

insert into emp values (null,'大鹏',15000,1);
insert into emp values (null,'熊大',5000,1);
insert into emp values (null,'小凤',5000,2);
insert into emp values (null,'美美',6000,2);

* 如果直接删除到研发部门
    delete from dept where did = 1; 

3.就可以使用外键进行约束,保存数据库的完整性。
* 声明外键,使用foreign key声明外键。
* 修改表,给emp表添加的外键(把dno的字段添加成外键)
* alter table emp add foreign key (dno) references dept (did);

4.如果已经包含外键的关联了,就不能删除部门了。
delete from dept where did = 1;

5.在创建表结构的时候,可以声明外键
create table emp(
eid int primary key auto_increment,
ename varchar(50),
sal double,
dno int,
foreign key (dno) references dept (did)
);
多表的设计

1.一对多的建表原则:在多方的表中,新添加一个字段,作为该表的外键。指向一方表的主键。(外键的值只能从一方表的主键中获取)

2.多对多的建表原则:创建一个中间表,中间表中至少包含两个字段,这两个字段作为外键,指向多对多原表的主键。

3.一对一的建表原则:例子:一个公司都对应一个地址。
* 创建一个公司的表,创建一个地址的表。
* 主键对应:公司的主键值和地址表的主键值是相同的。

4.JavaWeb课程最后4天,有一个项目。购物的网站。
* 用户 订单 商品 分类
多表的查询

1.了解笛卡尔积。查询数据是不准确的。笛卡尔积:两个结果的乘积。
A B
aid aname bid bname aid
a1 a11 b1 b11 a1
a2 a22 b2 b22 a2
b3 b33 a1

  • select * from A,B; 结果:就是笛卡尔积。
    a1 a11 b1 b11
    a1 a11 b2 b22
    a1 a11 b3 b33
    a2 a22 b1 b11
    a2 a22 b2 b22
    a2 a22 b3 b33
    多表查询(准确的数据)之内连接

0.前提条件:部门和员工表例子,两张表需要存在关系,就是外键的关系。
1.普通内链接
* 语法:inner join … on 条件
* 例子: select * from dept inner join emp on dept.did = emp.dno;

2.隐式内连接(开发中使用最多的)
* 语法:去掉inner join关键字
* 例子:select * from dept,emp where dept.did = emp.dno;
* 使用别名:select d.dname,e.ename,e.sal from dept d,emp e where d.did = e.dno;
多表查询之外链接

0.前提条件:部门和员工表例子,两张表需要存在关系,就是外键的关系。
1.左外链接
* 语法:关键字:left outer join … on 条件;
* 例子:select * from dept left outer join emp on dept.did = emp.dno;
2.右外链接
* 语法:关键字:right outer join … on 条件;
* 例子:select * from dept right outer join emp on dept.did = emp.dno;
内连接和外链接的数据的区别

1.向部门中添加一些数据
insert into dept values (3,’牛宝宝部’);
insert into dept values (4,’扯淡部’);
2.向员工表中添加一些数据
insert into emp values (null,’陈冠希’,16000,null);
insert into emp values (null,’张柏芝’,26000,null);

3.进行测试
* 内连接:select * from dept,emp where dept.did = emp.dno;
* 左链接:select * from dept left join emp on dept.did = emp.dno;
* 向链接:select * from dept right join emp on dept.did = emp.dno;

4.总结:内连接查询的结果没有变化,查询的是有关联的数据。左链接,看谁是左表,把左表中所有的数据全部都查询出来和有关联的数据。右链接,看谁是右表,把右表中所有的数据全部都查询出来和关联数据。
5.看图
子查询

1.一条SQL语句不能查询出结果,需要依赖几条SQL语句的查询。
2.查询出英语成绩大于英语平均分的同学?
* 先查英语的平均分
* 再查英语的成绩(大于平均分)

use day15;
select name,english from stu where english > (select avg(english) from stu);

3.子查询的符号
< 小于 > 大于
<= 小于等于 >=大于等于
= 等于 <>或!= 不等于 in 范围

any :select * from stu where english >any (5,6,7) 大于范围最小的值
all :select * from stu where english >all (5,6,7) 大于范围最大的值
练习

查看熊大所属的部门名称和员工名称?
* 查询:部门名称和员工的名称
* 表:部门表和员工表
* 条件:熊大的名称 where ename=’熊大’ 内连接 where d.did = e.dno

* 语句:select d.dname,e.ename from dept d,emp e where d.did = e.dno and e.ename='熊大';

统计每个部门的人数(按照部门名称统计)
* 查询:部门的人数,部门名称
* 表:部门表和员工表
* 条件:内连接 where d.did = e.dno 分组:group by d.dname

* 语句:select d.dname,count(*) from dept d,emp e where d.did = e.dno group by d.dname;

统计部门的平均工资(按部门名称统计)
* 查询:部门的平均工资,部门名称
* 表:部门和员工表
* 条件:内连接 where d.did = e.dno 分组:group by d.dname

* 语句:select d.dname,avg(e.sal) from dept d,emp e where d.did = e.dno group by d.dname;

统计部门的平均工资大于公司平均工资的部门
* 查询:部门
* 表:员工表和部门表
* 条件:内连接 where d.did = e.dno 分组:group by d.dname 条件:部门的平均 > 公司的平均工资

* 语句:select d.dname,avg(e.sal) from dept d,emp e where d.did = e.dno group by d.dname having avg(e.sal) > (select avg(sal) from emp);
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场