某公司网站建设策划书,工作方案的格式及范文,地产政策最新消息,装修公司一般多少钱一平方文章目录5 关系数据库语言5.1 MySQL简介5.1.1 MySQL的发展历史5.1.2 数据库的用户接口5.1.3 SQL及其概念辨析5.1.4 SQL查询语言的分类5.2 入手5.2.1 回顾——基本和虚表5.2.2 数据准备5.2.3 常用命令5.2.4 导入数据5.2.5 简单的查询5.2.6 条件查询5.2.6.1 条件查询格式5.6.2.1 …
文章目录5 关系数据库语言5.1 MySQL简介5.1.1 MySQL的发展历史5.1.2 数据库的用户接口5.1.3 SQL及其概念辨析5.1.4 SQL查询语言的分类5.2 入手5.2.1 回顾——基本和虚表5.2.2 数据准备5.2.3 常用命令5.2.4 导入数据5.2.5 简单的查询5.2.6 条件查询5.2.6.1 条件查询格式5.6.2.1 Between and5.2.6.3 is null5.2.6.4 or5.2.6.5 and和or的优先级问题5.2.6.6 in5.2.6.7 like5.2.7 排序5.3 渐入5.3.1 分组函数5.3.2 单行处理函数5.3.3 回到分组函数5.3.4 分组5.3.5 多字段分组查询5.3.6 部分limit5.3.7 小结5.4 连接查询5.4.1 概述5.4.2 连接查询分类5.4.3 连接查询细讲5.4.3.1 原理5.4.3.2 等值连接5.4.3.2 非等值连接5.4.3.3 自连接5.4.3.4 外连接5.4.3.5 全连接5.4.3.6 多张表做连接5.4.4 子查询5.4.4.1 概述5.4.4.2 集合成员资格5.4.4.2 集合的比较5.4.4.3 from子查询5.4.5 谓词查询5.4.5.1 some查询5.4.5.2 all查询5.4.5.3 空关系测试5.4.5.4 重复元组存在性测试5.4.5.5 with 子句5.5 集合运算5.5.1 并运算5.5.2 交运算5.5.3 差运算5.5.4 空运算5.6 表结构5.5.1 建表和删表5.5.2 插入数据5.5.3 拷贝表5.5.4 修改表的数据5.5.5 删除表中数据5.5.6 后话5.7 约束5.7.1 非空约束5.7.2 唯一性约束5.7.3 主键约束5.7.4 外键约束5.8 视图5.9 数据库数据的导入导出5.10 用户管理5.10.1 用户操作5.10.2 权限控制5 关系数据库语言
在这一小节中我们将会首先使用MySQL进行我们的讲解而后如果有机会我会简单介绍一下SQLserver。 对于MySQL的下载和安装可以前往MySQL官网进行下载。安装过程可以参照Win10安装MySQL详细教程_蔚来不是梦的博客-CSDN博客_win10安装mysql进行安装。 5.1 MySQL简介
5.1.1 MySQL的发展历史
1986年发布了SQL-86
1989年发布了SQL-89
然后发布了92、99、03、06、08 在这里特别标明的1986和1989常作为软件工程师考题而出现望各位知悉。 5.1.2 数据库的用户接口
什么是数据库的用户接口
用户使用数据库时对数据库进行的操作DBMS要想方设法满足其提供的命令和语言就叫用户和数据库的接口。DBMS提供多种用户接口来针对不同的适用人群。其中接口包括
查询语言图形化工具GUIAPIs类库
5.1.3 SQL及其概念辨析
SQL的概念可以归结为以下几点
结构化查询语言是一门标准通用的语言。标准的sql适合于所有的数据库产品。SQL属于高级语言只要能看得懂英语单词的写出来的sql语句可以读懂什么意思。SQL语句在执行的时候实际上内部也会进行编译然后再执行sqlsql语句的编译由DBMS完成。SQL是一个非过程化的查询语言
而其和DB和DBMS的区别如下 DBDataBase数据库数据库实际上在硬盘上以文件的形式存在 DBMS: DataBase Managemeng System数据库管理系统常见的有MySQL、Oracle、DB2、Sybase、sqlServer。。。
5.1.4 SQL查询语言的分类
SQL按其功能可以分为几大部分
DDL数据定义语言数据定义语言用于定义、撤销和修改数据模式creat、drop、alter。DQL数据查询语言查询语句凡是select语句都是DQL。DML数据操纵语言:用于增删改数据insert、delete、update。DCL数据访问权限控制语言用于数据访问权限的控制。grant授权revoke撤销权限。TCL事务控制commit提交事务rollback回滚事务。embeddedSQL and dynamicSQL嵌入式SQL和动态SQL嵌入式和动态SQL。其作用是定义SQL语句如何嵌入到通用编程语言内部如C、C和java等。
5.2 入手
为了方便上手我们不会先讨论表是怎么创建的而要先从最简单却又最复杂的查询语句开始讲起。但在此之前我们需要弄懂一些概念这些概念我们在前面已经简要提到过这里稍微回顾一下。
5.2.1 回顾——基本和虚表
我们在前面曾经谈论过基表和虚表视图。
基表是真真实实存在的他的数据显式地存储在数据库中或者换一种说法就是你当时存的时候什么样基表就长什么样。
而虚表是仅有逻辑定义可以根据其定义从其他表包括视图中导出但不作为一个表显式地存储在数据库中。换一种说法就是比如你数据库里面已经有个基表了然后我通过某些要求过滤了一些条件查询出来的表就是虚表虚表实际上不存在数据库里他只是通过一些计算和逻辑语言提取出来的。
当基表的模式修改时通过定义适当的视图仍可以为用户提供修改前的数据模式避免修改应用程序从而有利于提高数据的逻辑独立性。也就是说即使你基表改了但是为了视图还是和以前一样我们可以在基表的基础上做一些其他的操作使他改变操作后算出来的虚表和之前没改的虚表一模一样。
5.2.2 数据准备
在下面的练习中我们会用到大量的查询操作但这都是建立在数据库中有表的基础上所以我们会提供数据先建立几张表。
DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;CREATE TABLE DEPT(DEPTNO int(2) not null ,DNAME VARCHAR(14) ,LOC VARCHAR(13),primary key (DEPTNO));
CREATE TABLE EMP(EMPNO int(4) not null ,ENAME VARCHAR(10),JOB VARCHAR(9),MGR INT(4),HIREDATE DATE DEFAULT NULL,SAL DOUBLE(7,2),COMM DOUBLE(7,2),primary key (EMPNO),DEPTNO INT(2) );CREATE TABLE SALGRADE( GRADE INT,LOSAL INT,HISAL INT );//插入数据
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
10, ACCOUNTING, NEW YORK);
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
20, RESEARCH, DALLAS);
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
30, SALES, CHICAGO);
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
40, OPERATIONS, BOSTON);
commit;INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7369, SMITH, CLERK, 7902, 1980-12-17
, 800, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7499, ALLEN, SALESMAN, 7698, 1981-02-20
, 1600, 300, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7521, WARD, SALESMAN, 7698, 1981-02-22
, 1250, 500, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7566, JONES, MANAGER, 7839, 1981-04-02
, 2975, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7654, MARTIN, SALESMAN, 7698, 1981-09-28
, 1250, 1400, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7698, BLAKE, MANAGER, 7839, 1981-05-01
, 2850, NULL, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7782, CLARK, MANAGER, 7839, 1981-06-09
, 2450, NULL, 10);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7788, SCOTT, ANALYST, 7566, 1987-04-19
, 3000, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7839, KING, PRESIDENT, NULL, 1981-11-17
, 5000, NULL, 10);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7844, TURNER, SALESMAN, 7698, 1981-09-08
, 1500, 0, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7876, ADAMS, CLERK, 7788, 1987-05-23
, 1100, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7900, JAMES, CLERK, 7698, 1981-12-03
, 950, NULL, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7902, FORD, ANALYST, 7566, 1981-12-03
, 3000, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7934, MILLER, CLERK, 7782, 1982-01-23
, 1300, NULL, 10);
commit;INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
1, 700, 1200);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
2, 1201, 1400);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
3, 1401, 2000);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
4, 2001, 3000);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
5, 3001, 9999);
commit;在下面我会教你如何导入这份数据。 需要注意的是虽然SQL通用但是建表语句有部分语法是不通用的所以上面的表数据只适用于MySQL如果将其导入SQLserver是会报错的。 在下面的叙述中我时常会交叉使用用术语字段和属性实际上两个术语指的都是同一件事物。 5.2.3 常用命令
查看当前使用的是哪个数据库
select database();查询数据库版本也可以使用
select version();结束一条语句
\c退出mysql
exit查看创建表的语句
show create table emp;5.2.4 导入数据
我们下面会将5.2.2中准备的数据导入MySQL里我们建立的数据库。如果你是第一次学习建议跟着我照做即可。 第一步登录mysql数据库管理系统。 第二步查看有哪些数据库 show databases;(这个不是sql语句属于MySQL命令) 第三步创建属于我们自己的数据库 Create database bjpowernode;(这个不是sql语句属于MySQL命令) 第四步使用bjpowernode数据 Use bjpowernode;(这个不是sql语句属于MySQL命令) 第五步查看当前使用的数据库有哪些表? Show tables;第六步初始化数据 Mysqlsource D:\MySQL\bjpowernode.sql这里source后面跟的是上面sql文件的数据如果你没有该文件建议你打开记事本把5.2.2中的sql复制进去然后重命名为bjpowernode.sql 当我们使用source进行初始化数据库后此时数据库中有三张表我们可以使用show table进行查看。 mysql show tables; ±---------------------- | Tables_in_bjpowernode | ±---------------------- | dept | | emp | | salgrade | ±---------------------- 3 rows in set (0.00 sec) 5.2.5 简单的查询
如果需要删除一个数据库
drop database bjpowernode;如果需要查看一个表的结构
desc 表名如desc dept; ±-------±------------±-----±----±--------±------ | Field | Type | Null | Key | Default | Extra | ±-------±------------±-----±----±--------±------ | DEPTNO | int(2) | NO | PRI | NULL | |部门编号 | DNAME | varchar(14) | YES | | NULL | |部门名称 | LOC | varchar(13) | YES | | NULL | | 部门位置 ±-------±------------±-----±----±--------±------ 利用如下代码可以查询表中所有数据
select * from 表名查询表中数据的语法如下
Select 字段名1字段名2字段名3…from 表名这里需要注意的是任何一条sql语句都以;结尾并且sql语句不区分大小写。在老版的Mysql中如果你一条sql语句不写分号是会报错的但是新版不会报错。为了养成良好的习惯我们应该加分号。 比如我们想查看员工的年薪
select ename,sal*12 from emp;需要注意的是我们查询的字段是可以进行运算的比如上述的sql语句我想查询员工表中员工名和薪水但是我要的是年薪表中给的是月薪这时只需要对月薪乘12即可。 对于上面的查询我们发现查询得出的表中年薪的字段是sal* 12。如果对sal*12难受我们可以重命名
select ename,sal*12 as yearsal from emp;这里的as是可以省略的。 如果想起中文名也可以
select ename,sal*12 as 年薪 from emp;需要注意的是这里用中文单引号和双引号都可以但是建议单引号因为单引号其他数据库也通用不然你这里的数据库可以用其他不能用就会导致很尴尬。 如果要查询所有的字段即查看整张表的数据。
select*from emp;在实际开发中是不推荐使用*的因为如果一张表的属性非常多会导致查出来的数据非常多这在实际开发中查询的时候效率十分低下。 对于查询出来的结果有些可能会重复此时我们可以用distinct去重
select distinct job from emp;需要注意的是distinct关键字必须放在所有字段的最前面因为如果处于中间会导致去重不对等如 select ename,distinct job from emp;以上的sql语句是错误的为什么因为job是去重了但是ename没有去重导致两条属性值结果不一样多。 distinct出现在最前面表示后面的字段联合起来去重。 如果你想显式地指明你需要保留重复元组可以使用all关键字但是保留重复元组是默认的所以我们在使用中将不会使用all关键字。
5.2.6 条件查询
5.2.6.1 条件查询格式
条件查询语法格式如下
Select 字段字段…… From 表名 Where 条件例如查询员工表中工资待遇为5000的员工姓名
select ename from emp where sal 5000 ;例如想要查看员工名为smith的工资
select sal from emp where ename smith;不只是中文要加单引号只要是字符串都要加单引号不知道是不是字符串可以用desc去查询表的结构。 如果想要找出工资高于3000的员工
select ename from emp where sal3000;如果想要找出工资不等于3000的员工
select ename from emp where sal 3000;如果你学过一些编程语言也可以用以下的方式
select ename from emp where sal ! 3000;5.6.2.1 Between and
如果想要找出工资在1100和3000之间的员工包括1100和3000可以如下
select ename from emp where sal 1100 and sal3000;也可以如下
select ename from emp where sal between 1100 and 3000;这里要注意下between必须左小右大并且所划的区间是闭区间。 Between and 除了可以使用在数字方面之外还可以使用在字符串方面。 select ename from emp where ename between A and D;这里要注意的是字符串用between and 取头字母二十四字母区间而且所划区间左闭右开。 5.2.6.3 is null
如果想要找出哪些人没有津贴可以如下
select ename,sal,comm from emp where comm is null;需要注意的是在sql中NULL不是0不是一个值而是代表没有这个数据为空。 如果想要找出哪些人的津贴不为空可以如下
select ename,sal,comm from emp where comm is not null;5.2.6.4 or
如果想要找出工作岗位是MANAGER和SALESMAN的员工可以如下
select ename,job from emp where job MAnager or job salesman;5.2.6.5 and和or的优先级问题
如果我们想要找出薪资大于1000的并且部门编号是20或30部门的员工。可以如下
select ename,sal,deptno from emp where sal1000 and deptno20 or deptno 30;这里观察下表发现有一项数据出了问题。 ±-------±--------±------- | ename | sal | deptno | ±-------±--------±------- | ALLEN | 1600.00 | 30 | | WARD | 1250.00 | 30 | | JONES | 2975.00 | 20 | | MARTIN | 1250.00 | 30 | | BLAKE | 2850.00 | 30 | | SCOTT | 3000.00 | 20 | | TURNER | 1500.00 | 30 | | ADAMS | 1100.00 | 20 | | JAMES | 950.00 | 30 | | FORD | 3000.00 | 20 | ±-------±--------±------- 这说明and的运算优先级大于or运算优先级在实际运用中并不需要记住这些优先级不确定的优先级直接加小括号括起来先运算即可。即如下所示 mysql select ename,sal,deptno from emp where sal1000 and (deptno20 or deptno 30);5.2.6.6 in
在前面我们曾经这样找出工作岗位是MANAGER和SALESMAN的员工当时我们使用的是or。当然我们可以使用in来解决
select ename,job from emp where job in (SALESMAN,MANAGER);需要注意的是这里in后面的是值集不是区间。 5.2.6.7 like
找出员工表中员工名字含有o的可以如下 select ename from emp where ename like %o%;在这里我们需要注意的是在模糊查询中需要掌握两个特殊的符号一个是%一个是_%代表匹配任意多个字符 _代表匹配一个字符。
如果我们想要找到名字中第二个字母是A的可以如下
select ename from emp where ename like _a%;如果要找出名字中有下划线的为了避免使用下划线时被误判为想要使用匹配字符功能我们可以在其前面加上转义字符\。 这里要有个关于数据类型的模糊查询问题对于char和varchar其模糊查询出来的结果可以有点不一样。 varchar是可变的也就是说模糊查询查的结果就是我们想要的结果而对于char来说一旦你设char里容纳4个字节而只存放3个字节那么其余空间将会全部空格补齐。也就是说用like去查l_%那么“ _ ”所匹配的很有可能是空格。 举个比较简单的例子如果我们使用char(3)来存放刘云这个名字那么like刘_ _是可以把云后面所在的空格也查出来的也就是说使用char(3)存放刘云而使用like刘 _ _查询是可以把刘云这个结果查出来的。 5.2.7 排序
对于排序我们先给出一个例子按照工资排序找出员工名和薪资。
select ename,sal from emp order by sal;也就是说关键词order by后跟想要排序的属性。但是根据上述给出的sql语句可知其对sal的排序是一个升序排列。 在排序中若order by [属性名] [排序方式]中的排序方式未给出那么其默认升序。如果需要手动指定升序可以使用asc而指定降序可以使用desc。 如果我们要按照工资的升序降序排列当工资相同的时候再按照名字的升序排列。如下所示
select ename,sal from emp order by sal desc,ename asc;需要注意的是这里有必要说一下各个关键词的执行顺序
mysql select- 字段 3- from- 表名 1- where- 条件 2- order by 45.3 渐入
5.3.1 分组函数
SQL中提供了五个固有的聚集函数分别是 count 计数 sum 求和 avg 平均值 max 最大值 min 最小值 需要注意的是sum和avg的输入必须是数字值集而其他函数可以作用在非数字值集上。 在有些书上也把分组函数叫做聚集函数其作用是以一个值集作为输入返回单个值的函数。而且他还有例外一个名字叫多行处理函数。多行处理函数的特点就是输入多行最终输出一行。
既然有多行处理函数肯定也有一个单行处理函数也就是先找到一行处理一行然后再继续下一行。
这么说可能有点模糊我们在下面的例子中会详细体会。
我们如果要对津贴做一个统计我们可以这么干
select count(comm) from emp; 查出来的结果你试试就知道是4也就是说count具有的计数并不是和sum一样的计算总和的功能而是统计一个关系中符合条件元组的个数。并且在这里例子中分组函数是自动忽略NULL的。
我们时常用count来计算一个关系中所有元组的个数如下所示
select count(*) from emp;5.3.2 单行处理函数
单行处理函数和分组函数是相对的其输入一行处理一行。对于数据为空的位置来说如果空数据参与运算那么算出来的一切结果都会为空。
为此如果我们想改变这种情况我们可以用单行处理函数ifnull()来解决ifnull又叫空处理函数是单行处理函数的一种它的作用是把某个字段中所有为null的位置填上自己指定的默认值。
select ename,ifnull(comm,0) as comm from emp;5.3.3 回到分组函数
分组函数有一个需要注意的点是我们不能在where关键字后跟分组函数如我们想找出工资高于平均工资的员工
select avg(sal) from emp where salavg(sal);ERROR 1111 (HY000): Invalid use of group function上面的SQL语句是错误的。导致这个的原因实际上是因为在使用分组函数的时候实际上SQL是先where后分组再分组函数分组实际上和分组函数没有啥关系分组的关键字是group by。一般来说分组后才会使用分组函数。
换言之顺序来解释就是
select 5
from 1
where 2
group by 3
having 4
order by 6你可以理解为分组函数的顺序是3.5。:happy:
那回到上述问题我们如何在避免分组函数跟在where后的情况下解决找出工资高于平均工资的员工这个问题
这时候我们可以使用嵌套子查询即n个sql语句可以合成一句来写。在这里我们先简单介绍体会一下
select ename,sal from emp where sal(select avg(sal) from emp ) ;5.3.4 分组
既然前面说到group by关键字这一小节肯定是要着重介绍了与group by一同出场的还有having关键字。
前面我们说过where的执行顺序优先于group by那么当where筛选条件后得出的新关系拿来用group by分组得出新关系2如果我们想再次做筛选该如何呢这时候就需要使用having关键字了。having关键字的作用就是对分组之后的数据进行二次过滤。
如果我们不仅希望分组函数作用于单个元组集上而且希望将其作用到一组元组集上用group by这个关键字就可以实现。group by用于构造分组它可以对一个关系中属性值相同的元组分于一个组中。
要体会group by和having两者的用处请随我看下面的例题
我们如果要找出每个工作岗位的最高薪资
select job, max(sal) from emp group by job;试想如果在对工资取一个最大数的情况下如果没有分组job该取什么值和这个Max(sal)对应呢如果对Job分组那么job就会分成
jobCLERKSALESMANMANAGERANALYSTPRESIDENT
分组完成后会在每个小组中取一个最大的sal来填入max(sal)的位置。
jobmax(sal)ANALYST3000.00CLERK1300.00MANAGER2975.00PRESIDENT5000.00SALESMAN1600.00
这也是为什么分组函数常常联合使用且名为分组函数的缘故任何一个分组函数都是在group by语句执行结束之后才会执行的。当一条sql语句没有group by的话整张表的数据会自成一组。
你可以理解为
select avg(sal) from emp; select avg(sal) from emp group by;5.3.5 多字段分组查询
对于多个属性同时分组一定要注意一件事出现在select语句中的但没有被分组的属性只能出现在group by。换句话说任何没有出现在group by子句中的属性如果出现在select子句中的话只能出现于分组函数内部。下面我们拿一个十分简单的例子来说明
如果我们要找出每个工作岗位的最高工资
select max(sal),job from emp group by job; #yes
select ename,max(sal),job from emp group by job; #no很明显第一条是对的因为第二条的ename出现在select却没有出现在group by中这就意味着sal通过分组函数分组了job通过group by关键字分组了就只有ename没有分组。 以上第二条SQL语句在Oracle数据库中无法执行执行报错。 在SQLserver数据库总无法执行执行报错报错提示属性没有被聚集。 在Mysql数据库中可以执行但是执行结果矛盾。 如果我们想要多个字段分组我们可以在group by中填上需要分组的属性如下面的例子一样我们需要找出不同岗位的最高薪资。
select deptno,job,max(sal)fromempgroup bydeptno,job;5.3.6 部分limit
limit用于分页查询其为MySQL数据库特有的关键字其他数据库中没有需要注意的是Oracle中有一个相同的机制叫做rownum。
limit用于选取集合中的部分数据它的语法机制为[结果集] limit startIndex , length。其中startIndex表示起始位置从0开始length表示取几个。
让我们用一个例子来讲述上面的知识点我们想要找出工资排名在第4到第9的员工我们可以采用下面的方式
select ename,sal from emp order by sal desc limit 3,6;5.3.7 小结
在原则上如果可以在where中过滤的数据尽量在where中过滤效率较高。having的过滤是专门对分组之后的数据进行过滤的。这一块部分实际上涉及到查询优化的相关知识在后面我们会继续深入探讨查询优化的相关知识。
5.4 连接查询
5.4.1 概述
在上面的小节中我们一直都是在对一张表对查询可在实际开发中一个业务一般对应多张表比如学生和班级是不可能让你在一张表查的如此开心的。
有些同学会感觉到疑惑既然多表联查如此麻烦那我们把学生和班级的数据都统一在一张表上不就可以了吗但事实是这涉及到关系数据库的规范化如果把数据全部集中在一张表上那就会造成大量的冗余比如学生表中的学生都是在一个班上那么如果两表合一就会造成一张表上出现多次同个班级的情况。
5.4.2 连接查询分类
连接查询根据年代来划分可以分为两种其中我们普遍在学习的应该是SQL92版本的而SQL99的版本就比较新颖语法也比较简洁明了。
另一种划分方式是根据连接方式来划分如下所示
内连接 等值连接非等值连接自连接 外连接 左外连接左连接右外连接右连接 全连接很少见
5.4.3 连接查询细讲
5.4.3.1 原理
在SQL中的连接查询原理和关系代数中的连接查询原理实际上是一回事也就是说同样是使用加了限制的笛卡尔积。需要注意的是使用连接查询看似查询条数少于笛卡尔积但是实际上它是先根据多表多笛卡尔积后再筛选符合条件的所以实际上查询时间是不会减少的。
5.4.3.2 等值连接
这些术语看似很吓人实际上很简单就是根据某某条件相等来做连接。需要注意的是现在在企业中很少有人会用老版的SQL92写法而是采用新式SQL99写法。下面让我们看看两者区别如何
如果我们要找出每一个员工的部门名称要求显示员工名和部门名。采用SQL92写法如下
select ENAME ,DNAME from emp e,dept d where e.deptno d.deptno;SQL92之所以舍弃官方的说辞是where里面写的是表连接的条件不是过滤条件条件结构不清晰。 而在SQL99里我们这么写
select e.ENAME,d.DNAME from emp e (inner) join dept d on e.DEPTNO d.DEPTNO;其中inner可写可不写写出来的好处是协同开发的伙伴能够看出是内连接可读性好。相比于SQL92SQL99的SQL语法结构更加清晰一些表的连接条件和where过滤条件分离了。
5.4.3.2 非等值连接
内连接中的非等值连接最大的特点就是连接条件中的关系是非等量关系。如我们想要找每个员工所处工资等级并且显示对应名字和工资。
select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.LOSAL and s.HISAL所谓的非等值连接无非就是连接的条件从等价条件转为非等价条件。
5.4.3.3 自连接
所谓的自连接就是把自己的一张关系看成两张自己连接自己的关系。以题为例如果我们找出每个员工的上级领导要求显示员工名和对应的领导名。【备注mgr经理empno工号】
select e.ename 员工名,e2.ename 老板名 from emp e join emp e2 on e.mgr e2.empno;5.4.3.4 外连接
在讲述完自连接的同时我们引入了外连接。假设A和B表进行连接使用内连接的话凡是A表和B表能够匹配上的记录查询就是内连接AB两张表实际上并没有主副之分两张表是平等的。
而对于外连接假设A和B表进行连接使用外连接的话AB两张表中一张表是主表一张表是副表主要查询主表汇总的数据稍等着查询副表当副表中的数据没有和主表中的数据匹配上副表自动模拟出NULL与之匹配。
外连接主要分为左外连接和右外连接分别对应左边的表是主表和右边的表是主表。而对于左连接和右连接实际上是相对的主要看你两张表哪张放左哪张放右。
让我们看一下实际的例子如果我们需要找出每个员工的上级领导相对于上个例子我们还要找出最顶级的上司。
由于在上个例子中我们使用的是内连接的方式所以最上级的领导实际上是不存在的既然不存在就匹配不到自己的上级的既然匹配不出来那么他对应连接出来的元组是不显示的。而对于外连接来说指定员工表作为主表那么主表的所有元组都会存在于连接的结果中即使某条元组匹配不到另一张表中对应的元组它也会用NULL补上。
回到本题的例子我们可以用左外连接来解决这个问题
select e.ename 员工名,e2.ename 老板名 from emp e leftout join emp e2 on e.mgr e2.empno;5.4.3.5 全连接
对于全连接来说不仅在开发中即为罕见在考试中也几乎不考查所以我们这里就不做过多讲解了。
5.4.3.6 多张表做连接
在使用SQL99的写法对两张表做连接时我们使用的是from 表一 join 表二 on 条件这种写法那如果我们要多张表连接呢我们可以使用A join B join C ... on 条件。其表示的意思是A表和B表先进行表连接连接之后A表继续和C表进行连接。
5.4.4 子查询
5.4.4.1 概述
SQL中提供嵌套子查询机制子查询是嵌套在另一个查询中的select-from-where表达式。子查询嵌套在where子句中通常用于对集合的成员资格、集合的比较以及集合的基数进行检查。
上面的表述可以写为如下形式 select…(select)from…(select)where…(select)5.4.4.2 集合成员资格
SQL允许测试元组在关系中的成员资格。在前面的学习中我们使用in来检测元组是否为集合中的成员集合是由select子句产生的一组值构成的连接词not in则测试元组是否不是集合中的成员。
让我们用一个例子来体会这里要讲的知识我们需要找出等于平均薪资的员工信息。在3.3中我们初次体会到了嵌套子查询对于本题来说实际上就是在测试集合成员资格我们可以把这个题目归为以下步骤
找出员工的平均薪资找出等于平均薪资的员工
也就是说我们在找出员工的平均薪资后用in来判断所求员工的薪资是否等于符合平均薪资。
select * from emp where sal (select avg(sal) from emp); //写法一
select * from emp where sal in (select avg(sal) from emp);//写法二同样地我们也可以用not in来完成找出不等于平均薪资的员工信息。
5.4.4.2 集合的比较
集合的比较比较典型的例子是3.3的例子这里就不再细讲了。在这里我们使用的是where子查询意在比对两个集合。
select * from emp where sal (select avg(sal) from emp);5.4.4.3 from子查询
from子查询实际上是进行一个套娃的过程也就是通过某种查询查询出一个大表再从这个大表中进行查询。
比较具体的例子是我们要找出每个部门的平均薪水的薪资等级。
首先我们可以先按部门编号分组这样的话得到是每个部门的平均薪水。
select deptno,avg(sal) avgsal from emp group by deptno;然后我们再将上面查询出来的大表当做临时表让其和salgrade表做连接条件是其每个部门平均薪水处于每个薪资等级最高薪水和最低薪水之间。
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;5.4.5 谓词查询
谓词查询实际上都是应用于集合的比较通常对应至少、至多等术语。其中集合的获取可以使用前面讲过的子查询。
5.4.5.1 some查询
some意为至少比某一个如何如果我们要查询某某至少比集合内的某一个要大可以使用some [集合]来表示。 在SQL中关键词any同义于some只不过在早期的版本中仅允许使用ant后来的版本为了避免和英语中的any一词在语义上的混淆又添加了一个可选择的关键词some。 5.4.5.2 all查询
all意味所有如果我们要查询某某比集合内所有的要大可以使用 all [集合]来表示。
5.4.5.3 空关系测试
SQL除了上面两个谓词还可以使用exists来测试子查询的结果中是否存在所需元组。如果我们要查询某某存在集合可以使用exists [集合]来表示。如我们要查找工资为800且名字为史密斯的工作人员信息我们可以
select * from emp where sal 800 and exists (select * from emp where ename SMITH);同样地如果我们想要表示某某不存在所需集合可以用not exists [集合]表示。 从上面的exists来看其还可以用于且(和)运算。 5.4.5.4 重复元组存在性测试
SQL提供一个布尔函数用于测试在一个子查询的结果中是否存在重复元组。如果作为参数的子查询结果中没有重复的元组unique将返回true值。
5.4.5.5 with 子句
with子句用于建立临时表相比于from子查询其语法结构更加清晰明了如查找销售员岗位且名字为SIMTH的工作人员信息。
with SALESMAN as (select * from emp where JOB CLERK) select * from emp where ENAME SMITH;需要注意的是为临时表指定名字时格式并非原名 as 重命名而是重命名 as 查询所得表。 5.5 集合运算
SQL作用在关系上的unionintersect和except运算对应我们高中数学集合论中学习的∪、∩、−∪、∩、-∪、∩、−运算在SQL中这些集合运算都是会对运算结果做自动去重。通过对下面例子的学习我们能够更加深入地去了解。
5.5.1 并运算
我们都知道并对应逻辑运算中的或当我们的题意中有或的字眼时我们就要考虑使用并运算其语法规则为集合1 union 集合2如查找工资为800或者1300的员工名以及工资信息。
select ename,sal from emp where sal 800 union select ename,sal from emp where sal 1300;5.5.2 交运算
交对应逻辑运算的和且当我们的题意中有和或者且的字眼时就要考虑用交运算。其语法规则为集合1 intersect 集合2。如查找工资为800且职位为clerk的员工信息。
(select * from emp where sal 800) intersect (select * from emp where job clerk);需要注意的是在MySQL中是不支持intersect的由前面所学的知识可知以上的题目完全可以转化为以下形式 select * from emp where sal 800 and job clerk);不仅MySQL不支持intersect现在很多DBMS基本上都不支持。在MySQL中实际上如果真想使用交运算可以使用过我们在5.4.5.3中学到的exists关键字如本题我们还可以写为 select * from emp where sal 800 and exists (select * from emp where job clerk);在前面我们说过集合运算会对结果自动去重如果你想不让其去重可以使用intersect all。
5.5.3 差运算
差对应中文语义中的在...但不在...其语法规则为集合1 except 集合2如查找工资为800但职位不是销售员(SALESMAN)的员工信息。
select * from emp where sal 800 except select * from emp where job salsman;同样地上面的except在MySQL中同样不支持由前面所学知识可知以上的题目完全可以转化为以下形式 select * from emp where sal 800 and job!salesman;如果在Oracle其使用关键字minus来代替except。如果想保留所有重复可以使用except all来代替except。 5.5.4 空运算
对于某个值和空值做运算其实际上是无法运算的所以为了解决这个问题除了true和false之外我们引入第三个逻辑值unknown。
对于and运算来说true和unknown的结果是unknownfalse和unknown结果是falseunknown and unknown结果是unknown。
对于or来说true和true or unknown的结果是truefalse or unknown结果是unknownunknown or unknown结果是unknown。
对于not来说not unknown的结果是unknown。
对于SQL来说我们可以使用关键词null来测试控制如查找名为史密斯且其提成为空的员工信息。
select * from emp where ENAME SMITH and COMM is null5.6 表结构
5.5.1 建表和删表
如果我们需要建一个表我们可以使用如下形式
create table 表名字段名 1 数据类型字段名 2 数据类型字段名 3 数据类型完整性约束1完整性约束2...对于MySQL中字段的数据类型常见的有
int整数型java中的intbigint长整型java中的longfloat浮点型java中的 float doublechar定长字符串Stringvarchar可变长字符串StringBuffer/StringBuilderdata日期类型(对应java中的java.sql.data类型)BLOB二进制大对象存储图片、视频等流媒体信息binary large object对应java的objectCLOB字符大对象存储较大文本比如可以存储4G的字符串。character large object对应java的object
其中容易混淆的两个数据类型当然是char和varchar了在5.2.6.7中我们曾经谈过此事。他们两个的区别在于假如我指定char6那么不管我输入啥字符只要不超6个空间它都是给6个空间但是如果是varchar6他是一种智能的类型能判断你输入的字符是占多少个空间前提是不超6并且分配对应字符的空间。
varchar不是一定要使用的像生日性别的这种数据字段不发生改变的时候是定长的那我们就采用char。而当一个字段的数据长度不确定的时候例如简介、姓名等都是采用varchar。
在前面的学习中我们时常发现存储的数据常常是一些整数啊字符串啊之类的结构化数据实际上表中数据是可以放一些非结构化的数据比如视频音乐等。假设有个电影表t_movie
idname(varchar)playtime(data/char)haibao(BLOB)history(CLOB)1 2 3蜘蛛侠 … …
一般来说我们不会直接将视频放到表里面而是将硬盘里面的路径放到表里。而图片就可能会放到表里。
说完上面的知识点让我们来简单创建一个表吧如果我们要创建一个学生表学生信息包括:学号、姓名、性别、班级编号、生日 学号bigint 姓名varchar 性别char 班级编号int 生日char
使用SQL语句我们可以这么创建
create table t_student(no bigint,name varchar(255),sex char(1),classno varchar(255),birth char(10));创建表的时候可以指定表为NULL时的默认值如
create table t_student(no bigint,name varchar(255),sex char(1) default 1,classno varchar(255),birth char(10));如果我们要删除这个表可以使用drop table if exists 表名来删除。
5.5.2 插入数据
我们现在需要对这个空表插入几个数据插入数据的语法格式为
insert into 表名(字段名1字段名2字段名3..)values(值1值2值3)需要注意的是这里要求字段的数量和值的数量相同并且数据类型要对应相同。
我们来对上一小节创建的空表添加数据吧如下所示
insert into t_student(no,name,sex,classno,birth)values(1,BaKa爱,1,gaosan1ban,1950-10-12);MySQL为我们提供了简写方式字段名和值能对得上就行比如表中no字段对应1你可以写成
insert into t_student(name,sex,classno,birth,no)values(zhangsan,1,gaosan1ban,1950-10-12,2);如果表中有学号、姓名、性别、班级编号、生日五个字段如果你只插入一个字段和值那么其他字段的值为空。
insert into t_student(name) values(乔峰);当一条insert语句执行成功之后表格当中必然会多一行记录。即使多的这一行记录当中某些字段是NULL后期也无法再执行insert语句插入数据了只能使用update进行更新。
也就是说insert的最小操作单位是元组不是属性。
特别地如果你写插入语句不写字段名那么后面的值必须写满表所有的字段一个都不能漏。比如说表中有学号、姓名、性别、班级编号、生日五个字段那么你可以写
insert into t_student values(1,zhangsan,1,gaosan1ban,1950-10-12);字段可以省略不写但是后面的value对数量和顺序都有要求。
当然我们也可以多行添加如
insert into t_student(name,sex,classno,birth,no)
values
(BaKa爱,1,gaosan1ban,1950-10-12,2),
(乔峰,2,gaosan1ban,1955-09-28,4);5.5.3 拷贝表
如果我们想要把查询的结果作为一张新表置于数据库我们可以使用语法create table 表名 as select 语句来完成这个工作。如
create table emp1 as select * from emp;这个语法是不是和使用格式with 表名 as 查询结果来创建临时表有点相似呢 如果我们想要把查询结果插入另一张表我们可以使用格式insert into 表名 查询结果如
insert into dept1 select * from dept;5.5.4 修改表的数据
我们前面说过insert的最小操作单位是元组也就是说元组一旦插入元组中的属性就无法修改如若想修改就要使用本小节的update关键字。其语法格式为
update 表名 set 字段名1 值1字段名2 值2…where 条件其中where条件是必须的否则这条更新语句会根据你修改的东西将表中所有修改的属性全部更新。如我们要将前面小节中创建的表中高三二班的字段值改为高三三班。
update t_student set classno gaosan3ban where no 1;5.5.5 删除表中数据
删除表中数据的语法格式为delete from 表名 where 条件需要注意的是where条件也是必须的否则表中数据将全被删除但是表依然存在。即数据库存留一张空表。
如果需要删除表我们可以使用5.5.1中讲述的drop table if exists 表名进行删除需要注意的是此时被删除的表是可以通过日志回滚来寻回被删除的表的如果需要永久删除表可以使用truncate table 表名。
5.5.6 后话
在实际开发中对于表中的结构去修改实际情况发生概率是很低的除非在建表的时候你没有考虑好而且就算真的结构安排不合理我们也是可以用工具完成即可。修改表的结构实际上就是对之前的设计进行了否定。并且在修改表结构的时候其SQL语句不会出现在java代码当中。
5.7 约束
在5.5.1中我们谈论的创建表的格式问题当时我们并没有深究完整性约束只是简单创建了一张表那么什么是完整性约束呢
在创建表的时候可以给表的字段添加相应的约束添加约束的目的是为了保证表中数据的合法性、有效性、完整性。比如登录QQ账号你总不可能密码为空就能登录吧你总不可能QQ账号是英文的吧
让我们引出下面要叙述的一些约束
非空约束not null结束的字段不能为NULL唯一约束unique约束的字段不能重复主键约束primary key约束的字段不能为NULL也不能重复简称PK外键约束foreign key约束的字段不能为NULL也不能重复简称FK检查约束check控制属性域 以上的约束在大多数数据库是支持的但是需要注意的是MySQL是没有check约束的。 对于约束我们可以在指定单个字段拥有该约束也可以指定全局约束。
5.7.1 非空约束
我们对单个属性加上非空约束用一个例子来说明
create table t_user(id int,username varchar(255) not null, //对用户名采用了非空约束password varchar(255));这时候如果插入语句写了
insert into t_user(id,password) values(1,123);那么其会报错
ERROR 1364 (HY000): Field username doesnt have a default value5.7.2 唯一性约束
唯一约束修饰的字段具有唯一性不能重复但可以为NULL。因为NULL不是值。如下面为例我们需要给某字段添加唯一性约束
create table t_user(id int,username varchar(255) unique //指定用户名唯一
);当然我们可以全局指定约束即所有字段都拥有该约束
create table t_user(id int,usercode varchar(255),username varchar(255),unique(usercode,username));unique指的是联合起来不能重复。也就是新添加的元组要满足usercode和username和以前添加过的元组不会重复。这种约束我们也叫表级约束。需要注意的是对于大多数约束来说都拥有表级约束的功能而非空约束没有表级约束。
5.7.3 主键约束
如果我们要指定某个字段作为主键我们只需在其后面添加primary key即可如
drop table if exists t_user;create table t_user(id int primary key,username varchar(255),email varchar(255));id是主键因为添加了主键约束主键字段中的数据不能为NULL也不能重复。
在数据库杂谈二中我们谈过一个关系数据库的表必须要有主键且主键具有唯一性和非空性。主键充当表中元组的唯一标识可用于找任何一条元组。
在实际开发中主键的性质决定了主键拥有两大分类自然主键和业务主键。 自然主键主键值最好就是一个和业务没有任何关系的自然数。 业务主键主键值和系统的业务挂钩比如银行卡卡号身份证号码不推荐用
上述两种主键中我们并不推荐使用业务主键因为一旦业务改变主键也随之丢失举个例子银行卡号报废主键消失整条元组也会消失。
我们可以利用MySQL提供的功能来添加自然主键。
drop table if exists t_user;create table t_user(id int primary key auto_increment, //指定id为自然主键username varchar(255));当指定id字段为自然主键时MySQL会自动维护一个从1开始递增的数字作为主键值。如下所示
mysql select * from t_user;--------------| id | username |--------------| 1 | a || 2 | b || 3 | c || 4 | d || 5 | e |--------------当然其他DBMS也提供相似的机制如Oracle当中提供了一个自增机制叫做序列sequence但是我们为了照顾初学者这里不做过多讲解。 5.7.4 外键约束
如果要对某个属性添加外键约束只需在全局添加foreign key关键字即可如
drop table if exists t_student;drop table if exists t_class;create table t_class(cno int,cname varchar(255),primary key(cno));create table t_student(sno int,sname varchar(255),classno int,foreign key(classno) references t_class(cno)//指定classno为外键其对应t_class表中的cno);指定外键约束后t_student中的classno字段引用t_class表中的cno字段此时t_student表叫做子表t_class表叫做父表。由于classno对cno产生依赖在对表做操作的时候就有严格要求了 删除数据的时候先删除子表再删除父表。 添加数据的时候先添加父表再添加子表。 创建表的时候先创建父表再创建子表。 删除表的时候先删除子表再删除父表。
5.8 视图
我们在这一章的最开始就提到了基表和虚表的概念实际上视图就是虚表视图是从一个或几个基本表或视图中导出的虚拟的表。在系统的数据字典中仅存放了视图的定义不存放视图对应的数据。 数据字典后面我们会做讲解别急。 那知道了视图的概念我们如何创建视图呢
//创建视图
create view myview as select empno,ename from emp;//删除视图
drop view myview;既然视图叫做虚表那就意味着视图本质上也是一张表虚表作为基表的映射。如果我们对虚表进行操作那么是会反映到基表上的。视图可以隐蔽表中的实现细节。保密级别较高的系统数据库只对外提供相关的视图java程序员只对视图对象进行CRUD。视图实际上创建后是不会数据冗余的即不会在硬盘上创建一份视图。他的原理实际上是查询改写。
5.9 数据库数据的导入导出
诶有些人想说我想把写的SQL语句全部导出来要等下拷贝去公司用那么可以使用下面两个操作。 这一小节针对的是程序员而非学生如果是为了应付考试无需理会该小节。 导入整个数据库
在window的dos命令窗口中执行
mysqldump 数据库名称导出路径\完整文件名 -u用户名名称 -p密码导出指定库下的某个表
在window的dos命令窗口中执行
mysqldump 数据库名称 表名导出路径\完整文件名 -u用户名名称 -p密码5.10 用户管理
5.10.1 用户操作 这一部分实际上作为考试很少考察但还是需要知道以防丢分 查询用户
use mysql
select * from user;创建用户
create user 用户名主机名 identified by 密码;修改用户密码
alter user 用户名主机名 identified with mysql_native_password by 新密码;删除用户
drop user 用户名主机名;5.10.2 权限控制
常见权限表
权限说明all,all privieges所有权限select查询数据insert插入数据update修改数据delete删除数据alter修改表drop删除数据库/表/视图create创建数据库/表
查询权限
show grants for 用户名主机名;授予权限
grant 权限列表 on 数据库名 表名 to 用户名主机名;撤销权限
revoke 权限列表 on 数据库名.表名 from 用户名主机名;多个权限之间使用逗号分割 授权时数据库名和表名可以使用*进行通配代表所有;
