栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

MySQL数据库(2)

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

MySQL数据库(2)

文章目录
  • 1.连接查询
    • 1.1 概念
    • 1.2 连接查询的分类
    • 1.3 连接查询原理以及笛卡尔积现象【笛卡尔乘积现象】
    • 1.4 避免笛卡尔积现象
    • 1.5 内连接
      • 1.5.1 等值连接
      • 1.5.2 非等值连接
      • 1.5.3 自连接
    • 1.6 外连接
    • 1.7 三张表进行连接查询
  • 2.子查询
    • 2.1 where后面嵌套子查询
    • 2.2 from后面嵌套子查询
    • 2.3 select后面嵌套子查询
  • 3.union的用法
  • 4.limit【分页查询全靠它】
    • 4.1 基础概念
    • 4.2 通用的标准分页sql
  • 5.表
    • 5.1 基础概念
    • 5.2 创建表
    • 5.3 插入数据
    • 5.4 表的复制以及批量插入
    • 5.5 修改表中的数据
    • 5.6 删除表中的数据
    • 5.7 关于表结构的修改

1.连接查询 1.1 概念

在实际开发中,大部分的情况下都不是从单表中查询数据,一般都是多张表联合查询取出最终的结果
在实际开发中,一般一个业务都会对应对张表,比如:学生和班级,起码两张表【如果存在一张表中,会存在大量重复的数据,导致数据的冗余】

1.2 连接查询的分类

根据语法出现的年代划分:

  • SQL92(一些老的DBA可能还在使用这种语法。DBA:DataBase Administrator,数据库管理员)
  • SQL99(比较新的语法)

根据表的连接方式来划分:

  • 内连接:
    等值连接
    非等值连接
    自连接
  • 外连接
    左外连接(左连接)
    右外连接(右连接)
  • 全连接【这个不讲,很少用】
1.3 连接查询原理以及笛卡尔积现象【笛卡尔乘积现象】

笛卡尔现象:当两张表进行连接查询的时候,没有任何条件进行限制,最终的查询结果条数是两张表记录条数的乘积

案例:找出每个员工的部门名称,要求显示员工名和部门名

表的别名:select e.ename,d.dname from emp e ,dept d;
表的别名优点:

  • 执行效率高
  • 可读性好
1.4 避免笛卡尔积现象

方法:加条件进行过滤【不会减少记录的匹配次数,只不过显示的是有效记录】

上面案例的解决语句:
select e.ename,d.dname
from emp e ,dept d
where e.deptno = d.deptno;【SQL92语法,以后不用】

1.5 内连接 1.5.1 等值连接

最大特点:条件是等量关系

案例:找出每个员工的部门名称,要求显示员工名和部门名

SQL99【常用的】:
select e.ename,d.dname
from emp e
join dept d
on e.deptno = d.deptno;

语法:
...
   A
inner join //inner可以省略,带着inner目的是可读性好一些
   B
on
   连接条件
where
  ...

注意:SQL99语法结构更清晰一些,表的连接条件和后来的where条件分离了

1.5.2 非等值连接

最大特点:连接条件中的关系是非等量关系

案例:找出每个员工的工资等级,要求显示员工名、工资、工资等级

1.5.3 自连接

最大特点:一张表看做两张表,自己连接自己

案例:找出每个员工的上级领导,要求显示员工名和对应的领导名


1.6 外连接

外连接最重要的特点:主表的数据无条件的全部查询出来

连接概念
内连接假设A和B表进行连接,使用内连接的话,凡是A表和B表能够匹配上的记录查询出来,这就是内连接。AB两张表没有主副之分,两张表是平等的
外连接假设A和B表进行连接,使用外连接的话,AB两张表中有一张表是主表,一张表是副表,主要查询主表中的数据,捎带着查询副表,当副表中的数据没有和主表中的数据匹配上,副表自动模拟出NULL与之匹配
外连接概念
左外连接(左连接)表示左边的这张表是主表
右外连接(右连接)表示右边的这张表是主表

注意:左连接有右连接的写法,右连接也会有对应的左连接的写法

案例:找出每个员工的上级领导(所有员工必须全部查询出来)

案例:找出哪个部门没有员工

1.7 三张表进行连接查询

案例:找出每一个员工的部门名称以及工资等级

表示:A表和B表先进行连接,连接之后A表继续和C表进行连接
...
   A
join
   B
join
   C
on
  ...

案例:找出每一个员工的部门名称、工资等级、以及上级领导

2.子查询

子查询:select语句当中嵌套select语句,被嵌套的 select语句是子查询
子查询可以出现在:

select
   ..(select)
from
   ..(select)
where
   ..(select)
2.1 where后面嵌套子查询

案例:找出高于平均薪资的员工信息

select * from emp where sal > avg(sal);//错误的写法,where后面不能直接使用分组函数

正确步骤:

  • 第一步:找出平均薪资 select avg(sal) from emp;
  • 第二步:where过滤 select * from emp where sal > 2073.214286;
  • 第一步和第二部合并:select * from emp where sal > *(select avg(sal) from emp);
2.2 from后面嵌套子查询

案例:找出每个部门平均薪水的薪资等级

  • 第一步:找出每个部门平均薪水(按照部门编号分组,求sal的平均值)
    select deptno,avg(sal) as avgsal from emp group by deptno;
  • 第二步:将以上的查询结果当作临时表t,让t表和salgrade表连接,条件是:t.avgsal between s.losal and s.hisal

案例:找出每个部门平均的薪水等级

  • 第一步:找出每个员工的薪水等级
  • 第二步:基于以上的结果,继续按照deptno分组,求grade平均值

2.3 select后面嵌套子查询

案例:找出每个员工所在的部门名称,要求显示员工名和部门名
原来方法:

现在方法:

3.union的用法

作用:将查询结果集相加

案例:找出工作岗位是SALESMAN和MANAGER的员工

第一种写法:select ename, job from emp where job = ‘MANAGER’ or job = ‘SATESMAN’ ;
第二种写法:select ename , job from emp where job in ( ‘MANAGER’,‘SALESMAN’ ) ;
第三种写法:
select ename ,job from emp where job = ‘MANAGER’
union
select ename , job from emp where job = ‘SAL,ESMAN’ ;

注意:

  1. union可以将两张不相干的表中的数据拼接在一起显示
  2. 拼的时候列数需要一致
4.limit【分页查询全靠它】 4.1 基础概念

limit是mysql特有的,其它数据库中没有,不通用。(Oracle中有一个相同的机制,叫做rownum)
作用:limit取结果集中的部分数据
语法机制:limit startIndex,length【startIndex:表示起始位置(从0开始,0表示第一条数据),length:表示取几个】

案例:取出工资前五名的员工
思路:降序取前五个
select ename,sal from emp order by sal desc limit 0, 5;
等价于: select ename,sal from emp order by sal desc limit 5;【直接写一个数字,前面默认为0】

注意:limit是sql语句中最后执行的一个环节


案例:取出工资排名在第四到第九名的员工
select ename,sal from emp order by sal desc limit 3, 6;

4.2 通用的标准分页sql

每页显示pagesize条记录:
第pageNo页: (pageNo -1)* pagesize, pagesize

5.表 5.1 基础概念

建表语句的语法结构:

create table 表名{
    字段名1  数据类型,
    字段名2  数据类型,
    字段名3  数据类型,
    ...
}

MySQL当中字段的数据类型:

数据类型概念
int整数型(java中的int)
bigint长整型(java中的long)
float浮点型(java中的float double)
char定长字符串(java中的String)
varchar可变长字符串(java中的StringBuffer/StringBuilder)
date日期类型(java中的java.sql.Date类型)
BLOB(Binary Large OBject)二进制大对象(存储图片、视频等流媒体信息)(java中的Object)
CLOB(Character Large OBject)字符大对象 (存储较大文本,比如:可以存储4G的字符串)(java中的Object)

char 和 varchar之前的选择:

在实际的开发中,当某个字段中的数据长度不发生改变的时候,是定长的,例如:性别、生日等都是采用char。
当一个字段的数据长度不确定,例如:简介、姓名等都是采用varchar

注意:表名在数据库当中一般建议以:t_或者tbl_开始

5.2 创建表

创建学生表:学生信息包括【学号、姓名、性别、班级编号、生日】
学号:bigint
姓名:varchar
性别:char
班级编号:int
生日: char

语句:

create table t_student(
   no bigint,
   name varchar(255),
   sex char(1),
   classno varchar (255),
   birth char (10)
  );
5.3 插入数据

语法格式: insert into 表名(字段名1,字段名2,字段名3,…) values(值1,值2,值3,…)

要求:字段的数量和值的数量相同,并且数据类型要对应相同。

实战:insert into t_student(no ,name ,sex,classno,birth) values(1, ‘zhangsan’ , ‘1’ , ‘gaosan1ban’,‘1950-10-12’);
insert into t_student (name , sex,classno ,birth ,no) values(‘lisi’ , ‘1’ , ’ gaosan1ban’,‘1950-10-12’,2);
insert into t_student (name) values ( 'wangwu ’ ) ;【除name字段之外,剩下的所有字段自动插入NULL】

删除表格:drop table if exists t_student;

可以设置默认值:

create table t_student(
   no bigint,
   name varchar(255),
   sex char(1) default 1,
   classno varchar (255),
   birth char (10)
  );

注意:当一条insert语句执行成功之后,表格当中必然会多一行记录。即使多的这一行记录当中某些字段是NULL,后期也没有办法在执行insert语句插入数据了,只能使用update进行更新。

注意:字段可以省略不写,但是后面的value对数量和顺序都有要求
insert into t_student values (1, ‘jack ’ , ’ 0’ , ’ gaosan2ban’, ‘1986-10-23’);

注意:一次插入多行数据
insert into t_student (no , name , sex ,classno ,birth) values (3 , ’ rose’ , ‘1’, ’ gaosi2ban’ , ‘1952-12-14’),(4,’ laotie’ , ‘1’ , ’ gaosi2ban’, ‘1955-12-14’);

5.4 表的复制以及批量插入

表的复制语句:create table 表名 as select语句;【将查询结果当做表创建出来】

将查询结果插入到一张表中:
insert into dept1 select * from dept;
select * from dept1;

5.5 修改表中的数据

语法格式:update 表名 set 字段名1=值1,字段名2=值2… where 条件;【注意:没有条件整张表数据全部更新】

案例:将部门10的Loc修改为SHANGHAI,将部门名称修改为RENSHIBU
语法:update dept1 set loc = ‘SHANGHAI’,dname = ‘RENSHIBU’ where deptno = 10;

更新所有记录:update dept1 set loc = ‘x’ , dname = ‘y’;

5.6 删除表中的数据

语法格式:delete from 表名 where 条件;
注意:没有条件全部删除

案例:删除10部门数据
语法:delete from dept1 where deptno = 10;

案例:删除所有数据
语法:delete from dept1;

案例:删除大表
语法:truncate table emp1;【表被截断,不可回滚,永久丢失】

5.7 关于表结构的修改
  1. 对于表结构的修改,这里不陈述,使用工具完成即可,因为在实际开发中表一旦设计好之后,对表结构的修改是很少的,修改表结构就是对之前的设计进行了否定,即使需要修改表结构,也可以直接使用工具操作。
  2. 修改表结构的语句不会出现在Java代码当中,出现在Java代码中的sql包括:insert、delete、update、select(这些都是对表中的数据操作)
  3. 增删改查有一个术语:CRUD操作【Create(曾) Retrieve(检索) Update(修改) Delete(删除)】
转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/936035.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号