MySQL（基础二）

最新推荐文章于 2021-10-28 16:39:47 发布

苦命的IT民工

最新推荐文章于 2021-10-28 16:39:47 发布

阅读量118

点赞数 1

分类专栏： mysql

本文链接：https://blog.csdn.net/qq_57548225/article/details/119183535

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1.关于查询结果集的去重？

select ename,distinct job from emp; //错误
记住：distinct只能出现在所有字段的最前面。

select distinct deptno,job from emp;

案例：统计岗位的数量？
select count(distinct job) from emp;
±--------------------+
| count(distinct job) |
±--------------------+
| 5 |
±--------------------+

2. 连接查询

什么是连接查询？

在实际开发中，大部分的情况下都不是单表中查询数据。一般都是多张表联合查询出最终的结果。
在实际开发中，一般一个业务都会对应多张表，比如：学生和班级，起码两张表。

stuno         stuname              classno            classname
------------------------------------------------------------------------------------------------------------------------------
1					zs						1							高三一班
2					ls							1							高三一班
······
学生和班级信息存储到一张表中，结果就像上面一样，数据会存在大量的重复，导致数据的冗余。

连接查询分类？

根据语法出现的年代来划分的话，包括：
SQL92（一些老的DBA可能还在使用这种语法。DBA：DataBase Administrator,数据库管理员）
SQL99（比较新的语法）

根据表的连接方式来划分，包括：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接（左连接）
右外连接（右连接）
全连接（很少用）

在表的连接查询方面有一种现象被称为：笛卡尔积现象。（笛卡尔乘积现象）

案例：找出每一个员工的部门名称，要求显示员工名和部门名。

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

关于表的别名：
select e.ename,d.dname from emp e,dept d;
表的别名：
第一：执行效率高；
第二：可读性好；

怎么避免笛卡尔积现象？当然是加条件进行过滤。

思考：避免了笛卡尔积现象，会减少记录的匹配次数吗？
不会，次数还是56次。只不过显示的是有效记录。

内连接之等值连接：最大特点是：条件是等量关系。

案例：查询每个员工的部门名称，要求显示员工名和部门名。
案例：查询每个员工的部门名称，要求显示员工名和部门名。
SQL92：
select
e.ename,d.dname
from
emp e,dept d
where
e.deptno = d.deptno;

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

//inner可以省略
select
e.ename,d.dname
from
emp e
inner join
dept d
on
e.deptno = d.deptno;

语法：
…
A
join
B
on
连接条件
where
…

内连接之非等值连接：最大的特点是：连接条件中的关系是非等量关系。

自连接：最大的特点是：一张表看作两张表。自己连自己。

外连接？

什么是外连接，和内连接有什么区别？
内连接：
假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。AB两张表没有主副之分，两张表是平等的。
外连接：
假设A和B表进行连接，使用外连接的话，AB两张表中有一张是主表，一张是副表，主要查询主表中的数据，捎带着查询附表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。

外连接的分类？
	左外连接（左连接）：表示左边的这张表是主表。
	右外连接（右连接）：表示右边这张表是主表。

	左连接有右连接的写法，右连接也会有对应的左连接的写法。

外连接：（左外连接/左连接）
select
-> a.ename ‘员工’,b.ename ‘领导’
-> from
-> emp a
-> left join
-> emp b
-> on
-> a.mgr = b.empno;

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

EMP表：
±------±-------±----------±-----±-----------±--------±--------±-------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
±------±-------±----------±-----±-----------±--------±--------±-------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
±------±-------±----------±-----±-----------±--------±--------±-------+

select a.deptno,b.deptno from emp b right join dept a on a.deptno = b.deptno where b.deptno is null;
±-------±-------+
| deptno | deptno |
±-------±-------+
| 40 | NULL |
±-------±-------+

EMP表：（e表）
±------±-------±----------±-----±-----------±--------±--------±-------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
±------±-------±----------±-----±-----------±--------±--------±-------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
±------±-------±----------±-----±-----------±--------±--------±-------+

SALGRADE表：（s表）
±------±------±------+
| GRADE | LOSAL | HISAL |
±------±------±------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
±------±------±------+

多张表连接

注意，解释一下：
…
A
join
B
join
C
on
…

表示：A表和B表先进行表连接，连接之后A表继续和C表进行连接。

案例：找出每一个员工的部门名称，工资等级，以及上级领导。
mysql> select
-> e.ename,d.dname,s.grade
-> from
-> emp e
-> join
-> dept d
-> on
-> e.deptno = d.deptno
-> join
-> salgrade s
-> on
-> e.sal between s.losal and s.hisal
left join
emp e1
on
e.mgr = e1.empno;

子查询

什么是子查询？子查询都可以出现在哪里？

select语句当中嵌套select语句，被嵌套的select语句是子查询。
子查询可以出现在哪里？
select
…(select)
from
…(select)
where
…(select)

where子句中使用子查询

select * from emp where sal > (select avg(sal) from emp);

from后面嵌套子查询

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

第一步：找出每个部门平均薪水（按照部门编号分组，求sal的平均值）
select deptno,avg(sal) as avgsal from emp group by deptno;
±-------±------------+
| deptno | avgsal |
±-------±------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
±-------±------------+

第二步：将以上的查询结果当作临时表t，让t表和salgrade s表连接，条件是：t.avgsal between s.losal and s.hisal

select
t.*,s.grade
from
(select deptno,avg(sal) as avgsal from emp group by deptno) t
join
salgrade s
on
t.avgsal between s.losal and s.hisal;

±-------±------------±------+
| deptno | avgsal | grade |
±-------±------------±------+
| 20 | 2175.000000 | 4 |
| 30 | 1566.666667 | 3 |
| 10 | 2916.666667 | 4 |
±-------±------------±------+

案例：找出每个部门平均的薪资等级。
第一步：找出每个员工的薪水等级。

mysql> select e.ename,e.deptno,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
±-------±-------±--------±------+
| ename | deptno | sal | grade |
±-------±-------±--------±------+
| SMITH | 20 | 800.00 | 1 |
| ALLEN | 30 | 1600.00 | 3 |
| WARD | 30 | 1250.00 | 2 |
| JONES | 20 | 2975.00 | 4 |
| MARTIN | 30 | 1250.00 | 2 |
| BLAKE | 30 | 2850.00 | 4 |
| CLARK | 10 | 2450.00 | 4 |
| SCOTT | 20 | 3000.00 | 4 |
| KING | 10 | 5000.00 | 5 |
| TURNER | 30 | 1500.00 | 3 |
| ADAMS | 20 | 1100.00 | 1 |
| JAMES | 30 | 950.00 | 1 |
| FORD | 20 | 3000.00 | 4 |
| MILLER | 10 | 1300.00 | 2 |
±-------±-------±--------±------+

mysql> select ename,sal from emp;
±-------±--------+
| ename | sal |
±-------±--------+
| SMITH | 800.00 |
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| FORD | 3000.00 |
| MILLER | 1300.00 |
±-------±--------+
mysql> select * from salgrade;
±------±------±------+
| GRADE | LOSAL | HISAL |
±------±------±------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
±------±------±------+

第二步：基本以上结果，继续按照deptno分组，求grade平均值。
select e.ename,e.sal,e.deptno,s.grade from emp e join salgrade s on e.sal between s.local and s.hisal group by e.deptno

在select后面嵌套子查询。

案例：找出每个员工所在的部门名称。要求显示员工名和部门名。

union（可以将查询结果集相加）

第三种：union
select ename,job from emp where job =‘MANAGER’
union
select ename,job from emp where job =‘SALESMAN’;

limit (重点)

limit 是mysql特有的，其他的数据库中没有，不通用。（Oracle中有一个相同的机制，叫做rownum）

limit取结果集中的部分数据，这是它的作用。

语法机制：

limit startIndex,length
startIndex表示起始位置
length表示取几个

案例：取出工资前5名的员工（思路：降序取前5个）
select ename,sal from emp order by sal desc;
取前5个：
	select ename,sal from emp order by sal desc limit 0,5;
	select ename,sal from emp order by sal desc limit 5;

limit是sql语句最后执行的一个环节：

select 5
…
from 1
…
where 2
…
group by 3
…
having 4
…
order by 6
…
limit 7
…;

案例：找出工资排名在第四到第九名的员工？
select ename,sal from emp order by sal desc limit 3,6;
±-------±--------+
| ename | sal |
±-------±--------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
±-------±--------+

通用的标准分页sql？

每页显示3条记录：
第一页：0，3
第二页：3，3
第三页：6，3
第四页：9，3
第五页：12，3

每页显示pageSize条记录：
第pageNo页：（pageNo - 1）* pageSize,pageSize

创建表：

建表语句的语法格式：
create table 表名（
字段名1 数据类型
字段名2 数据类型
字段名3 数据类型
…
）

关于MySQL当中字段的数据类型？一下只说常见的
int 整数形（java中的int）
bigint 长整型(java中的long)
float 浮点型（java中的float double）
char 定长字符串（String）
varchar 可变长字符串(StringBuffer/StringBuilder)
date 日期类型(对应JAVA中的Java.sql.Data类型)
BLOB 二进制大对象（存储图片、视频等流媒体信息） Binary Large OBject（对应Java中的Object）
CLOB 字符大对象（存储较大文本，比如，可以存储4G的字符串。）Character Large OBject(对应Java中的Object)

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

表名在数据库当中一般建议以：t_或者tb1_开始。

创建学生表：
学生信息包括：
学号、姓名、性别、班级编号、生日
学号：bigint
姓名：char
性别：char
班级编号：Int
生日：char

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

7.insert语句插入数据
语法格式：
insert into 表名（字段名1，字段名2，字段名3，。。。。）value(值1，值2，值3，。。。。)
要求：字段的数量和值的数量相同，并且数据类型要对应相同。

insert into t_student(no,name,sex,classno,birth) values (1,‘zhanhsan’,‘1’,‘gaosan1ban’);
ERROR 1136 (21S01): Column count doesn’t match value count at row 1

insert into t_student(no,name,sex,classno,birth) values (1,‘zhangsan’,‘1’,‘gaosan1ban’,‘1950-10-12’);
mysql> select * from t_student;
±-----±---------±-----±-----------±-----------+
| no | name | sex | classno | birth |
±-----±---------±-----±-----------±-----------+
| 1 | zhangsan | 1 | gaosan1ban | 1950-10-12 |
±-----±---------±-----±-----------±-----------+
insert into t_student(name,sex,classno,birth,no) values (‘zhangsan’,‘1’,‘gaosan1ban’,‘1950-10-12’,2);
mysql> select * from t_student;
±-----±---------±-----±-----------±-----------+
| no | name | sex | classno | birth |
±-----±---------±-----±-----------±-----------+
| 1 | zhangsan | 1 | gaosan1ban | 1950-10-12 |
| 2 | zhangsan | 1 | gaosan1ban | 1950-10-12 |
±-----±---------±-----±-----------±-----------+

insert into t_student(name) values(‘wangwu’); //除name字段之外，剩下的所有字段自动插入NULL。
mysql> select * from t_student;
±-----±---------±-----±-----------±-----------+
| no | name | sex | classno | birth |
±-----±---------±-----±-----------±-----------+
| 1 | zhangsan | 1 | gaosan1ban | 1950-10-12 |
| 2 | zhangsan | 1 | gaosan1ban | 1950-10-12 |
| NULL | wangwu | NULL | NULL | NULL |
±-----±---------±-----±-----------±-----------+

drop table if exits 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进行更新。

insert into t_student values(1,‘jack’,‘0’,‘gaosan2ban’,‘1986-10-23’);

mysql> select * from t_student;
±-----±-----±-----±-----------±-----------+
| no | name | sex | classno | birth |
±-----±-----±-----±-----------±-----------+
| 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’,‘gaosi1ban’,‘1942-03-24’);

mysql> select * from t_student;
±-----±-------±-----±-----------±-----------+
| no | name | sex | classno | birth |
±-----±-------±-----±-----------±-----------+
| 1 | jack | 0 | gaosan2ban | 1986-10-23 |
| 3 | rose | 1 | gaosi2ban | 1952-12-14 |
| 4 | laotie | 1 | gaosi1ban | 1942-03-24 |
±-----±-------±-----±-----------±-----------+

表的复制

语法：
create table 表名 as select语句；
将查询结果当作表创建出来。

将查询结果插入到一张表中？

mysql> insert into dept1 select * from dept;
mysql> select * from dept1;

修改数据：update

语法格式：
update 表名 set 字段名1=值1，字段名2=值2.。。。where 条件；

注意：没有条件整张表数据全部更新。

案例：将部门10的LOC修改为SHANGHAI，将部门名称修改为RENSHIBU
update dept1 set loc = ‘SHANGHAI’,dname = ‘RENSHIBU’ where deptno =10;
select * from dept1;

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

删除数据？

语法格式：
delete from 表名 where 条件；

注意：没有条件全部删除。

删除10部门数据？
delete from dept1 where deptno = 10;

删除所有记录？
delete from dept1;

怎么删除大表？（重点）
truncate table emp1; //表被截断，不可回滚。永久丢失。

DQL（select） DML(insert delete update) DDL(create drop alter)

对于表结构的修改，使用工具完成即可，因为在实际开发中表一旦设计好之后，对表结构的修改是很少的，修改表结构就是对之前的设计进行了否定，即使需要修改表结构，我们也可以直接使用工具操作。修改表结构的语句不会出现在Java代码当中。出现在Java代码当中的sql包括：insert delect update select(这些都是表中的数据操作。)

增删改查有一个术语：CRUD操作
Create(增）Retrieve（检索）Update(修改) Delete(删除)

13. 约束

什么是约束？常见的约束有哪些？

在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性，有效性，完整性。
常见的约束有哪些？
	非空约束（not null）：约束的字段不能为null
	唯一约束（unique）：约束的字段不能重复
	主键约束（primary key）：约束的字段既不能为null，也不能重复（简称PK）
	外键约束（foreign key）：.....（简称FK）
	检查约束（check）:注意Oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束。