数据库、SQL语句、数据查询语法、完整性约束、编码、备份和恢复数据、多表查询
JavaWeb学习笔记数据库数据库概念基本命令启动和关闭mysql服务器客户端登录退出mysql SQL语句SQL概述分类DDL基本操作操作数据库数据类型操作表 DML插入数据修改数据删除数据 DCL创建用户给用户授权撤销授权查看用户权限删除用户修改用户密码 DQL(数据查询语法)基础查询条件查询模糊查询字段控制查询去除重复记录列运算 排序聚合函数分组查询LIMIT(MYSQL方言) 编码完整性约束主键和主键自增长非空和唯一约束外键表与表之间的关系 多表查询合并结果集连接查询内连接自然连接 外连接ON和WHERE的区别 子查询JavaWeb学习笔记
数据库
数据库概念
什么是数据库
数据库就是用来存储和管理数据的仓库!
数据库存储数据的优先:
可存储大量数据;方便检索;保持数据的一致性、完整性;安全,可共享;通过组合分析,可产生新数据。
数据库的发展历程
没有数据库,使用磁盘文件存储数据;层次结构模型数据库;网状结构模型数据库;关系结构模型数据库:使用二维表格来存储数据(MySQL就是关系型数据库!);关系-对象模型数据库;
常见数据库
Oracle:甲骨文;DB2:IBM;SQL Server:微软;Sybase:赛尔斯;MySQL:甲骨文;
理解数据库
RDBMS(关系数据库管理系统) = 管理员(manager)+仓库(database)database = N个tabletable:
〉 表结构:定义表的列名和列类型!
〉 表记录:一行一行的记录!
我们现在所说的数据库泛指“关系型数据库管理系统(RDBMS - Relational database management system)”,即“数据库服务器”。
应用程序与数据库
应用程序使用数据库完成对数据的存储!
基本命令
启动和关闭mysql服务器
启动:net start mysql;关闭:net stop mysql;在启动mysql服务后,打开windows任务管理器,会有一个名为mysqld.exe的进程运行,所以mysqld.exe才是MySQL服务器程序。
客户端登录退出mysql
在启动MySQL服务器后,我们需要使用管理员用户登录MySQL服务器,然后来对服务器进行操作。登录MySQL需要使用MySQL的客户端程序:mysql.exe
登录:mysql -u root -p 123 -h localhost;
-u:后面的root是用户名,这里使用的是超级管理员root;-p:后面的123是密码,这是在安装MySQL时就已经指定的密码;-h:后面给出的localhost是服务器主机名,它是可以省略的,例如:mysql -u root -p 123;
退出:quit或exit;
在登录成功后,打开windows任务管理器,会有一个名为mysql.exe的进程运行,所以mysql.exe是客户端程序。
SQL语句
SQL概述
什么是SQL
SQL(Structured Query Language)是“结构化查询语言”,它是对关系型数据库的操作语言。它可以应用到所有关系型数据库中,例如:MySQL、Oracle、SQL Server等。SQ标准(ANSI/ISO)有:
SQL-92:1992年发布的SQL语言标准;SQL:1999:1999年发布的SQL语言标签;SQL::发布的SQL语言标签;
这些标准就与JDK的版本一样,在新的版本中总要有一些语法的变化。不同时期的数据库对不同标准做了实现。
虽然SQL可以用在所有关系型数据库中,但很多数据库还都有标准之后的一些语法,我们可以称之为“方言”。例如MySQL中的LIMIT语句就是MySQL独有的方言,其它数据库都不支持!当然,Oracle或SQL Server都有自己的方言。
语法要求
SQL语句可以单行或多行书写,以分号结尾;可以用空格和缩进来来增强语句的可读性;关键字不区别大小写,建议使用大写;
分类
DDL(Data Definition Language):数据定义语言,用来定义数据库对象:库、表、列等;DML(Data Manipulation Language):数据操作语言,用来定义数据库记录(数据);DCL(Data Control Language):数据控制语言,用来定义访问权限和安全级别;DQL(Data Query Language):数据查询语言,用来查询记录(数据)。DDL
基本操作
查看所有数据库名称:SHOW DATABASES;切换数据库:USE mydb1,切换到mydb1数据库;操作数据库
创建数据库:CREATE DATABASE [IF NOT EXISTS] mydb1;
创建数据库,例如:CREATE DATABASE mydb1,创建一个名为mydb1的数据库。如果这个数据已经存在,那么会报错。例如CREATE DATABASE IF NOT EXISTS mydb1,在名为mydb1的数据库不存在时创建该库,这样可以避免报错。
删除数据库:DROP DATABASE [IF EXISTS] mydb1;
删除数据库,例如:DROP DATABASE mydb1,删除名为mydb1的数据库。如果这个数据库不存在,那么会报错。DROP DATABASE IF EXISTS mydb1,就算mydb1不存在,也不会的报错。
修改数据库编码:ALTER DATABASE mydb1 CHARACTER SET utf8
修改数据库mydb1的编码为utf8。注意,在MySQL中所有的UTF-8编码都不能使用中间的“-”,即UTF-8要书写为UTF8。
数据类型
MySQL与Java一样,也有数据类型。MySQL中数据类型主要应用在列上。
常用类型:
int:整型double:浮点型,例如double(5,2)表示最多5位,其中必须有2位小数,即最大值为999.99;decimal:泛型型,在表单钱方面使用该类型,因为不会出现精度缺失问题;char:固定长度字符串类型;varchar:可变长度字符串类型;text:字符串类型;blob:字节类型;date:日期类型,格式为:yyyy-MM-dd;time:时间类型,格式为:hh:mm:sstimestamp:时间戳类型;
操作表
创建表:CREATE TABLE 表名(
列名 列类型,
列名 列类型,
…
);
例如:
CREATE TABLE stu(sid CHAR(6),snameVARCHAR(20),ageINT,genderVARCHAR(10) );
CREATE TABLE emp(eidCHAR(6),enameVARCHAR(50),ageINT,genderVARCHAR(6),birthdayDATE,hiredateDATE,salaryDECIMAL(7,2),resumeVARCHAR(1000));
查看当前数据库中所有表名称:SHOW TABLES;查看指定表的创建语句:SHOW CREATE TABLE emp,查看emp表的创建语句;查看表结构:DESC emp,查看emp表结构;删除表:DROP TABLE emp,删除emp表;修改表:
修改之添加列:给stu表添加classname列:
ALTER TABLE stu ADD (classname varchar(100));
修改之修改列类型:修改stu表的gender列类型为CHAR(2):
ALTER TABLE stu MODIFY gender CHAR(2);
修改之修改列名:修改stu表的gender列名为sex:
ALTER TABLE stu change gender sex CHAR(2);
修改之删除列:删除stu表的classname列:
ALTER TABLE stu DROP classname;
修改之修改表名称:修改stu表名称为student:
ALTER TABLE stu RENAME TO student;
DML
插入数据
语法1:
INSERT INTO 表名(列名1,列名2, …) VALUES(值1, 值2)
INSERT INTO stu(sid, sname,age,gender) VALUES('s_1001', 'zhangSan', 23, 'male');INSERT INTO stu(sid, sname) VALUES('s_1001', 'zhangSan');
语法2:
INSERT INTO 表名 VALUES(值1,值2,…)
因为没有指定要插入的列,表示按创建表时列的顺序插入所有列的值:
INSERT INTO stu VALUES('s_1002', 'liSi', 32, 'female');
注意:所有字符串数据必须使用单引用!
修改数据
语法:
UPDATE 表名 SET 列名1=值1, … 列名n=值n [WHERE 条件]
UPDATE stu SET sname='zhangSanSan', age='32', gender='female' WHERE sid='s_1001';UPDATE stu SET sname='liSi', age='20' WHERE age>50 AND gender='male';UPDATE stu SET sname='wangWu', age=’30’ WHERE age>60 OR gender='female';UPDATE stu SET age='18' WHERE name IN ('zhangSan', 'liSi', 'wangWu');UPDATE stu SET gender='female' WHERE gender IS NULL UPDATE stu SET age=age+1 WHERE sname='zhaoLiu';
注意:对于值为NULL的列需要用’is’判断,如果为’WHERE xx=null’那么返回的值一定是false!
删除数据
语法1:
DELETE FROM 表名 [WHERE 条件]
DELETE FROM stu WHERE sid='s_1001'003BDELETE FROM stu WHERE sname='chenQi' OR age > 30;DELETE FROM stu;
语法2:
TRUNCATE TABLE 表名
TRUNCATE TABLE stu;
虽然TRUNCATE和DELETE都可以删除表的所有记录,但有原理不同。DELETE的效率没有TRUNCATE高!
TRUNCATE其实属性DDL语句,因为它是先DROP TABLE,再CREATE TABLE。而且TRUNCATE删除的记录是无法回滚的,但DELETE删除的记录是可以回滚的(回滚是事务的知识!)。
DCL
创建用户
语法:
CREATE USER ‘用户名’[@‘登录主机名’] [ IDENTIFIED BY ] [ ‘密码’ ]
CREATE USER user1@localhost IDENTIFIED BY '123'; //用户只能在本机IP上登录CREATE USER user2@'%' IDENTIFIED BY '123'; //用户可以在任意IP地址上登录
给用户授权
语法:
GRANT 权限1, … , 权限n ON 数据库.* TO 用户名
GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON mydb1.* TO user1@localhost; //给user1用户分派在mydb1数据库上的create、alter、drop、insert、update、delete、select权限GRANT ALL ON mydb1.* TO user2@localhost; //给用户分派指定数据库上的所有权限
撤销授权
语法:
REVOKE权限1, … , 权限n ON 数据库.* FORM 用户名
REVOKE CREATE,ALTER,DROP ON mydb1.* FROM user1@localhost;
查看用户权限
语法:
SHOW GRANTS FOR 用户名
SHOW GRANTS FOR user1@localhost;
删除用户
语法:
DROP USER 用户名
DROP USER user1@localhost;
修改用户密码
语法:
USE mysql;
UPDATE USER SET PASSWORD=PASSWORD(‘密码’) WHERE User=‘用户名’ and Host=‘IP’;
FLUSH PRIVILEGES;
UPDATE USER SET PASSWORD=PASSWORD('1234') WHERE User='user2' and Host=‘localhost’;FLUSH PRIVILEGES;
mysql 新设置用户或更改密码后需用flush privileges刷新MySQL的系统权限相关表,否则会出现拒绝访问,还有一种方法,就是重新启动mysql服务器,使新设置生效。
DQL(数据查询语法)
基础查询
查询所有列
语法:
SELECT * FROM 表名;
SELECT empno, ename, sal, comm FROM 表名;
查询指定列
语法:
SELECT 列1 [, 列2, … 列N] FROM 表名;
SELECT empno, ename, sal, comm FROM 表名;
条件查询
条件查询就是在查询时给出WHERE子句,在WHERE子句中可以使用如下运算符及关键字:
=、!=、<>、<、<=、>、>=;BETWEEN…AND;IN(set);IS NULL;AND;OR;NOT;
示例
查询性别为女,并且年龄50的记录:
SELECT * FROM stu WHERE gender='female' AND ge<50;
查询学号为S_1001,或者姓名为liSi的记录:
SELECT * FROM stu WHERE sid ='S_1001' OR sname='liSi';
查询学号为S_1001,S_1002,S_1003的记录:
SELECT * FROM stu WHERE sid IN ('S_1001','S_1002','S_1003');
查询学号不是S_1001,S_1002,S_1003的记录:
SELECT * FROM tab_student WHERE s_number NOT IN ('S_1001','S_1002','S_1003');
查询年龄为null的记录:
SELECT * FROM stu WHERE age IS NULL;
查询年龄在20到40之间的学生记录:
SELECT * FROM stu WHERE age>=20 AND age<=40;SELECT * FROM stu WHERE age BETWEEN 20 AND 40;
查询性别非男的学生记录:
SELECT * FROM stu WHERE gender!='male';SELECT * FROM stu WHERE gender<>'male';SELECT * FROM stu WHERE NOT gender='male';
查询姓名不为null的学生记录:
SELECT * FROM stu WHERE NOT sname IS NULL;SELECT * FROM stu WHERE sname IS NOT NULL;
模糊查询
当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE和特殊字符。
_:匹配一个任意字符,注意,只匹配一个字符而不是多个。
%:匹配0~N个任意字符
示例
查询姓名由5个字母构成的学生记录:
SELECT * FROM stu WHERE sname LIKE '_____';
查询姓名由5个字母构成,并且第5个字母为“i”的学生记录:
SELECT * FROM stu WHERE sname LIKE '____i';
查询姓名以“z”开头的学生记录
SELECT * FROM stu WHERE sname LIKE 'z%';
查询姓名中第2个字母为“i”的学生记录:
SELECT * FROM stu WHERE sname LIKE '_i%';
查询姓名中包含“a”字母的学生记录:
SELECT * FROM stu WHERE sname LIKE '%a%';
字段控制查询
去除重复记录
当查询结果中的多行记录一模一样时,只显示一行。
语法:
SELECT DISTINCT * | 列1 [, 列2, … 列N] FROM 表名;
SELECT DISTINCT sal FROM emp;
列运算
1.数量类型的列可以做加、减、乘、除运算(不影响数据库中的内容)
SELECT sal*1.5 FROM emp;SELECT sal+comm FROM emp;
2.连接字符串(利用CONCAT函数)
CONCAT(str1,str2,…)
:返回结果为连接参数产生的字符串。如有任何一个参数为NULL ,则返回值为 NULL。
SELECT CONCAT('$', sal) FROM emp;
注意:
如果所有参数均为非二进制字符串,则结果为非二进制字符串。
如果自变量中含有任一二进制字符串,则结果为一个二进制字符串。
3.转换NULL值
有时需要把NULL转换成其它值,例如com+1000时,如果com列存在NULL值,那么NULL+1000还是NULL,而我们这时希望把NULL当前0来运算。
SELECT IFNULL(comm, 0)+1000 FROM emp; //如果comm中存在NULL值,那么当成0来运算。
4.给列起别名
你也许已经注意到了,当使用列运算后,查询出的结果集中的列名称很不好看,这时我们需要给列名起个别名,这样在结果集中列名就显示别名了。
SELECT IFNULL(comm, 0)+1000 AS 奖金 FROM emp;
注意:
AS可以省略,即:SELECT IFNULL(comm, 0)+1000 奖金 FROM emp;
排序
查询所有学生记录,按年龄升序排序:
SELECT * FROM stu ORDER BY sage ASC;SELECT * FROM stu ORDER BY sage; //默认为ASC升序
查询所有学生记录,按年龄降序排序:
SELECT * FROM stu ORDER BY age DESC;
查询所有雇员,按月薪降序排序,如果月薪相同时,按编号升序排序
SELECT * FROM emp ORDER BY sal DESC,empno ASC; //可以一直写下去,但最多用三个就足够
聚合函数
聚合函数是用来做纵向运算的函数:
COUNT():统计指定列不为NULL的记录行数;MAX():计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运算;MIN():计算指定列的最小值,如果指定列是字符串类型,那么使用字符串排序运算;SUM():计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0;AVG():计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0;
COUNT
当需要纵向统计时可以使用COUNT()。
查询emp表中记录数:
SELECT COUNT(*) AS cnt FROM emp;
注意:COUNT(1)(可以是任何数字)和COUNT(*)的返回结果是一样的。
查询emp表中有佣金的人数:
SELECT COUNT(comm) cnt FROM emp;
注意,因为count()函数中给出的是comm列,那么只统计comm列非NULL的行数。
查询emp表中月薪大于2500的人数:
SELECT COUNT(*) FROM emp WHERE sal > 2500;
统计月薪与佣金之和大于2500元的人数:
SELECT COUNT(*) AS cnt FROM emp WHERE sal+IFNULL(comm,0) > 2500;
查询有佣金的人数,以及有领导的人数:
SELECT COUNT(comm), COUNT(mgr) FROM emp;
SUM和AVG
当需要纵向求和时使用sum()函数。
查询所有雇员月薪和:
SELECT SUM(sal) FROM emp;
查询所有雇员月薪和,以及所有雇员佣金和:
SELECT SUM(sal), SUM(comm) FROM emp;
查询所有雇员月薪+佣金和:
SELECT SUM(sal+IFNULL(comm,0)) FROM emp;
统计所有员工平均工资:
SELECT SUM(sal), COUNT(sal) FROM emp;
或者
SELECT AVG(sal) FROM emp;
MAX和MIN
查询最高工资和最低工资:
SELECT MAX(sal), MIN(sal) FROM emp;
分组查询
当需要分组查询时需要使用GROUP BY子句,例如查询每个部门的工资和,这说明要使用部分来分组。
查询每个部门的部门编号和每个部门的工资和:
SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;
查询每个部门的部门编号以及每个部门的人数:
SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;
查询每个部门的部门编号以及每个部门工资大于1500的人数:
SELECT deptno,COUNT(*) FROM emp WHERE sal>1500 GROUP BY deptno;
使用GROUP BY的条件:
首先我们要明白,使用group by 关键字时,在select列表中可以指定的项目是有限制的。
此时select语句中仅允许以下几项:
被分组的列(即被GROUP BY的列);为每个分组返回一个值的表达式。
HAVING子句
查询工资总和大于9000的部门编号以及工资和:
SELECT deptno, SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal) > 9000;
注意,WHERE是对分组前记录的条件,如果某行记录没有满足WHERE子句的条件,那么这行记录不会参加分组;而HAVING是对分组后数据的约束。
LIMIT(MYSQL方言)
LIMIT用来限定查询结果的起始行,以及总行数。
查询5行记录,起始行从0开始
SELECT * FROM emp LIMIT 0, 5;
注意,起始行从0开始,即第一行开始!
查询10行记录,起始行从3开始
SELECT * FROM emp LIMIT 3, 10;
查询起始行为第5行,一共查询3行记录
SELECT * FROM emp LIMIT 4, 3;
其中4表示从第5行开始,其中3表示一共查询3行。即第5、6、7行记录。
编码
查看MYSQL编码
SHOW VARIABLES LIKE 'char%';
因为当初安装时指定了字符集为UTF8,所以所有的编码都是UTF8。
character_set_client:你发送的数据必须与client指定的编码一致!!!服务器会使用该编码来解读客户端发送过来的数据;character_set_connection:通过该编码与client一致!该编码不会导致乱码!当执行的是查询语句时,客户端发送过来的数据会先转换成connection指定的编码。但只要客户端发送过来的数据与client指定的编码一致,那么转换就不会出现问题;character_set_database:数据库默认编码,在创建数据库时,如果没有指定编码,那么默认使用database编码;character_set_server:MySQL服务器默认编码;character_set_results:响应的编码,即查询结果返回给客户端的编码。这说明客户端必须使用result指定的编码来解码;
控制台编码
修改character_set_client、character_set_results、character_set_connection为GBK,就不会出现乱码了。但其实只需要修改character_set_client和character_set_results。
控制台的编码默认是GBK,而不能轻易修改为UTF8,这就出现一个问题。客户端发送的数据是GBK,而character_set_client为UTF8,这就说明客户端数据到了服务器端后一定会出现乱码。既然不能修改控制台的编码,那么只能修改character_set_client为GBK了。
服务器发送给客户端的数据编码为character_set_result,它如果是UTF8,那么控制台使用GBK解码也一定会出现乱码。因为无法修改控制台编码,所以只能把character_set_result修改为GBK。
修改character_set_client变量:set character_set_client=gbk;修改character_set_results变量:set character_set_results=gbk;
设置编码只对当前连接有效,这说明每次登录MySQL提示符后都要去修改这两个编码,但可以通过修改配置文件my.ini来处理这一问题。
设置default-character-set=gbk
使用MYSQL工具避免乱码问题
使用MySQL工具是不会出现乱码的,因为它们会每次连接时都修改character_set_client、character_set_results、character_set_connection的编码。这样对my.ini上的配置覆盖了,也就不会出现乱码了。
完整性约束
主键和主键自增长
主键
当某一列添加了主键约束后,那么这一列的数据就不能重复出现。这样每行记录中其主键列的值就是这一行的唯一标识。
例如学生的学号可以用来做唯一标识,而学生的姓名是不能做唯一标识的,因为学习有可能同名。
主键列的值不能为NULL,也不能重复!指定主键约束使用PRIMARY KEY关键字主键的三个特点:非空、唯一、被引用
创建主键的三种方法:
1.创建表:定义列时指定主键:
CREATE TABLE stu(sid CHAR(6) PRIMARY KEY,snameVARCHAR(20),ageINT,genderVARCHAR(10) );
2.创建表:定义列之后独立指定主键:
CREATE TABLE stu(sid CHAR(6),snameVARCHAR(20),ageINT,genderVARCHAR(10),PRIMARY KEY(sid));
3.修改表时指定主键:
ALTER TABLE stuADD PRIMARY KEY(sid);
删除主键(只是删除主键约束,而不会删除主键列):
ALTER TABLE stu DROP PRIMARY KEY;
主键自增长
MySQL提供了主键自动增长的功能!这样用户就不用再为是否有主键是否重复而烦恼了。当主键设置为自动增长后,在没有给出主键值时,主键的值会自动生成,而且是最大主键值+1,也就不会出现重复主键的可能了。
创建表时设置主键自增长(主键必须是整型才可以自增长):
CREATE TABLE stu(sid INT PRIMARY KEY AUTO_INCREMENT,snameVARCHAR(20),ageINT,genderVARCHAR(10));
修改表时设置主键自增长:
ALTER TABLE stu CHANGE sid sid INT AUTO_INCREMENT;
修改表时删除主键自增长:
ALTER TABLE stu CHANGE sid sid INT;
非空和唯一约束
非空:指定非空约束的列不能没有值,也就是说在插入记录时,对添加了非空约束的列一定要给值;在修改记录时,不能把非空列的值设置为NULL。
指定非空约束:
CREATE TABLE stu(sid INT PRIMARY KEY AUTO_INCREMENT,sname VARCHAR(10) NOT NULL,ageINT,genderVARCHAR(10));
当为sname字段指定为非空后,在向stu表中插入记录时,必须给sname字段指定值,否则会报错。
唯一:当为字段指定唯一约束后,那么字段的值必须是唯一的。这一点与主键相似!例如给stu表的sname字段指定唯一约束:
CREATE TABLE tab_ab(sid INT PRIMARY KEY AUTO_INCREMENT,sname VARCHAR(10) UNIQUE);INSERT INTO sname(sid, sname) VALUES(1001, 'zs');INSERT INTO sname(sid, sname) VALUES(1002, 'zs');//当两次插入相同的名字时,MySQL会报错!
外键
外键 ( foreign key ) 是用于建立和加强两个表数据之间的链接的一列或多列。通过将保存表中主键值的一列或多列添加到另一个表中,可创建两个表之间的链接。这个列就成为第二个表的外键。
外键约束的特点:
外键必须是另一表的主键的值(外键要引用主键!)外键可以重复外键可以为空一张表中可以有多个外键!
外键是另一张表的主键!例如员工表与部门表之间就存在关联关系,其中员工表中的部门编号字段就是外键,是相对部门表的外键。
我们再来看BBS(论坛)系统中:用户表(t_user)、分类表(t_section)、帖子表(t_topic)三者之间的关系。
例如在t_section表中sid为1的记录说明有一个分类叫java,版主是t_user表中uid为1的用户,即zs!
例如在t_topic表中tid为2的记录是名字为“Java是咖啡”的帖子,它是java版块的帖子,它的作者是ww。
外键就是用来约束这一列的值必须是另一张表的主键值!!!
创建t_user表,指定uid为主键列:
CREATE TABLE t_user(uidINT PRIMARY KEY AUTO_INCREMENT,unameVARCHAR(20) UNIQUE NOT NULL);
创建t_section表,指定sid为主键列,u_id为相对t_user表的uid列的外键:
CREATE TABLE t_section(sidINT PRIMARY KEY AUTO_INCREMENT,snameVARCHAR(30),u_idINT,CONSTRAINT fk_t_user FOREIGN KEY(u_id) REFERENCES t_user(uid));
修改t_section表,指定u_id为相对t_user表的uid列的外键:
ALTER TABLE t_section ADD CONSTRAINT fk_t_user FOREIGN KEY(u_id) REFERENCES t_user(uid);
修改t_section表,删除u_id的外键约束:
ALTER TABLE t_section DROP FOREIGN KEY fk_t_user;
表与表之间的关系
一:一对一
例如t_person表和t_card表,即人和身份证。这种情况需要找出主从关系,即谁是主表,谁是从表。人可以没有身份证,但身份证必须要有人才行,所以人是主表,而身份证是从表。设计从表可以有两种方案:
在t_card表中添加外键列(相对t_user表),并且给外键添加唯一约束;给t_card表的主键添加外键约束(相对t_user表),即t_card表的主键也是外键。(即该主键既有主键的特性又有外键的引用性)
二:一对多(多对一)
最为常见的就是一对多!一对多和多对一之间只是从不同角度出发所区分的。例如班级是一端,学生是多端,结合面向对象的思想,一端是父亲,多端是儿子,所以多端具有一端的属性,也就是说多端里面应该放置1端的主键,那么学生表里面应该放置班级表里面的主键。
三:多对多
例如t_stu和t_teacher表,即一个学生可以有多个老师,而一个老师也可以有多个学生。这种情况通常需要创建中间表来处理多对多关系。即再创建一张表t_stu_tea表,给出两个外键,一个相对t_stu表的外键,另一个相对t_teacher表的外键。
多表查询
有些时候数据在不同的表中,这个时候我们就需要用到mysql中的多表联查。
多表查询有如下几种:
合并结果集;连接查询 内连接外连接 左外连接右外连接全外连接(MySQL不支持) 自然连接 子查询
合并结果集
合并结果集就是把两个select语句的查询结果合并到一起!
前提是被合并的两个结果:列数、列类型必须相同。(不一定要求表结构相同,只要在查询语句中输入的列结构和列数相同就行)
合并结果集有两种方式:
UNION:去除重复记录,例如:SELECT * FROM t1 UNION SELECT * FROM t2
;
UNION ALL:不去除重复记录,例如:SELECT * FROM t1 UNION ALL SELECT * FROM t2
。
连接查询
连接查询就是求出多个表的乘积,例如t1连接t2,那么查询出的结果就是t1*t2。
上述图片我们发现连接查询会产生笛卡尔积,假设集合A={a,b},集合B={0,1,2},则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。
产生笛卡尔积会导致有很多垃圾数据,我们可以通过要查询的多个表之间都存在的关联关系去除笛卡尔积。
例如:emp一共14行记录,dept表一共4行记录,那么连接后查询出的结果是56行记录。
若想在查询emp表的同时,把每个员工的所在的正确部门信息显示出来,那么就需要使用主外键来去除无用信息了。
使用主外键关系做为条件来去除无用信息:SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;
上面查询结果会把两张表的所有列都查询出来,也许我们不需要那么多列,这时就可以指定要查询的列了。
SELECT emp.ename,emp.sal,m,dept.dname FROM emp,dept WHERE emp.deptno=dept.deptno;
还可以为表指定别名,然后在引用列时使用别名即可。
SELECT e.ename,e.sal,m,d.dname FROM emp AS e,dept AS d //AS可以不写WHERE e.deptno=d.deptno;
以上的连接语句就是内连接,但它不是SQL标准中的查询方式,可以理解为MYSQL方言!
内连接
内连接也叫连接,是最早的一种连接。还可以被称为普通连接或者自然连接,内连接是从结果表中删除与其他被连接表中没有匹配行的所有行,所以内连接可能会丢失信息。
SQL标准的内连接为:
SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;
内连接的特点:查询结果必须满足条件。
例如我们向emp表中插入一条记录:
其中deptno为50,而在dept表中只有10、20、30、40部门,那么上面的查询结果中就不会出现“张三”这条记录,因为它不能满足e.deptno=d.deptno这个条件。
自然连接
连接查询会产生无用笛卡尔积,我们通常使用主外键关系等式来去除它。而自然连接无需你去给出主外键等式,它会自动找到这一等式:
两张连接的表中名称和类型完成一致的列作为条件,例如上述的emp和dept表都存在deptno列,并且类型一致,所以会被自然连接找到!
SELECT * FROM emp NATURAL JOIN dept;SELECT * FROM emp NATURAL LEFT JOIN dept; //左连接SELECT * FROM emp NATURAL RIGHT JOIN dept; //右连接
当然自然连接还有其他的查找条件的方式,但其他方式都可能存在问题!
外连接
外连接的特点:查询出的结果存在不满足条件的可能。
左连接:
SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;
左连接是先查询出左表(即以左表为主),然后查询右表,右表中满足条件的显示出来,不满足条件的显示NULL。
这么说你可能不太明白,我们还是用上面的例子来说明。其中emp表中“张三”这条记录中,部门编号为50,而dept表中不存在部门编号为50的记录,所以“张三”这条记录,不能满足e.deptno=d.deptno这条件。但在左连接中,因为emp表是左表,所以左表中的记录都会查询出来,即“张三”这条记录也会查出,但相应的右表部分显示NULL。
右连接:
右连接就是先把右表中所有记录都查询出来,然后左表满足条件的显示,不满足显示NULL。例如在dept表中的40部门并不存在员工,但在右连接中,如果dept表为右表,那么还是会查出40部门,但相应的员工信息为NULL。
SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;
ON和WHERE的区别
ON条件:是过滤两个链接表笛卡尔积形成中间表的约束条件。
WHERE条件:在有ON条件的SELECT语句中是过滤中间表的约束条件。在没有ON的单表查询中,是限制物理表或者中间查询结果返回记录的约束。在两表或多表连接中是限制连接形成最终中间表的返回结果的约束。
所以将WHERE条件移入ON后面是不恰当的。推荐的做法是:ON只进行连接操作,WHERE只过滤中间表的记录。
子查询
子查询就是嵌套查询,即SELECT中包含SELECT,如果一条语句中存在两个,或两个以上SELECT,那么就是子查询语句了。
子查询出现的位置:
子查询可以嵌套在主查询中所有位置,包括SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY。
where后,作为条件的一部分:
当子查询出现在where后作为条件时,还可以使用如下关键字:
any:和子查询的结果逐一比较,其中一条记录满足条件则表达式的值就为真
all:和子查询的结果逐一比较,必须全部满足时表达式的值才为真
from后,作为被查询的一条表;
子查询结果集的形式:
单行单列(用于条件)单行多列(用于条件)多行单列(用于条件)多行多列(用于表)
练习:
1.工资高于甘宁的员工。
分析:
查询条件:工资>甘宁工资,其中甘宁工资需要一条子查询。
第一步:查询甘宁的工资
SELECT sal FROM emp WHERE ename='甘宁'
第二步:查询高于甘宁工资的员工
SELECT * FROM emp WHERE sal > (${第一步})
结果:
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='甘宁')
2.工资高于30部门所有人的员工信息
分析:
查询条件:工资高于30部门所有人工资,其中30部门所有人工资是子查询。高于所有需要使用all关键字。
第一步:查询30部门所有人工资
SELECT sal FROM emp WHERE deptno=30;
第二步:查询高于30部门所有人工资的员工信息
SELECT * FROM emp WHERE sal > ALL (${第一步})
结果:
SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30)
要点:
子查询作为条件子查询形式为多行单列(当子查询结果集形式为多行单列时可以使用ALL或ANY关键字)
3.查询工作和工资与殷天正完全相同的员工信息
分析:
查询条件:工作和工资与殷天正完全相同,这是子查询
第一步:查询出殷天正的工作和工资
SELECT job,sal FROM emp WHERE ename='殷天正'
第二步:查询出与殷天正工作和工资相同的人
SELECT * FROM emp WHERE (job,sal) IN (${第一步})
结果:
SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE ename='殷天正')
要点:
子查询作为条件子查询形式为单行多列
4.查询员工编号为1006的员工名称、员工工资、部门名称、部门地址
分析:
查询列:员工名称、员工工资、部门名称、部门地址
查询表:emp和dept,分析得出,不需要外连接(外连接的特性:某一行(或某些行)记录上会出现一半有值,一半为NULL值)
查询条件:员工编号为1006
第一步:去除多表,只查一张表,这里去除部门表,只查员工表
SELECT ename, sal FROM emp e WHERE empno=1006
第二步:让第一步与dept做内连接查询,添加主外键条件去除无用笛卡尔积
SELECT e.ename, e.sal, d.dname, d.loc FROM emp e, dept d WHERE e.deptno=d.deptno AND empno=1006
第二步中的dept表表示所有行所有列的一张完整的表,这里可以把dept替换成所有行,但只有dname和loc列的表,这需要子查询。
第三步:查询dept表中dname和loc两列,因为deptno会被作为条件,用来去除无用笛卡尔积,所以需要查询它。
SELECT dname,loc,deptno FROM dept;
第四步:替换第二步中的dept
SELECT e.ename, e.sal, d.dname, d.loc FROM emp e, (SELECT dname,loc,deptno FROM dept) d WHERE e.deptno=d.deptno AND e.empno=1006
要点:
子查询作为表子查询形式为多行多列
JavaWeb学习笔记(数据库 SQL语句 数据查询语法 完整性约束 编码 备份和恢复数据 多表查询)