mysql基础知识（二）

mysql基础知识（二）

多表查询

多表查询有如下几种：

合并结果集；UNION 、 UNION ALL 了解

连接查询

内连接 [INNER] JOIN ON

外连接 OUTER JOIN ON

左外连接 LEFT [OUTER] JOIN

右外连接 RIGHT [OUTER] JOIN

全外连接（MySQL不支持）FULL JOIN

自然连接 NATURAL JOIN

子查询

外键中可以存在null

合并结果集

union(自动去重) union all （不会去重）

列数不同不行，类型不同也不行 。在合并结果集时，俩个表的字段个数和类型必须一致。

要求：被合并的两个结果：列数、列类型必须相同。

连接查询

连接查询会产生笛卡尔积，假设集合A={a,b}B={0,1,2,2}，则两个集合的笛卡尔积为。可以扩展到多个集合的情况。那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

内连接

上面的连接语句就是内连接，但它不是SQL标准中的查询方式，可以理解为方言！SQL标准的内连接为：

外连接

外连接的特点：查询出的结果存在不满足条件的可能。

左连接：

左外连接

左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。这么说你可能不太明白，我们还是用上面的例子来说明。其中emp表中“张三”这条记录中，部门编号为50，而dept表中不存在部门编号为50的记录，所以“张三”这条记录，不能满足e.deptno=d.deptno这条件。但在左连接中，因为emp表是左表，所以左表中的记录都会查询出来，即“张三”这条记录也会查出，但相应的右表部分显示NULL。

右外连接

右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。例如在dept表中的40部门并不存在员工，但在右连接中，如果dept表为右表，那么还是会查出40部门，但相应的员工信息为NULL。

连接查询心得：

连接不限与两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除。两张表的连接查询一定有一个主外键关系，三张表的连接查询就一定有两个主外键关系，所以在大家不是很熟悉连接查询时，首先要学会去除无用笛卡尔积，那么就是用主外键关系作为条件来处理。如果两张表的查询，那么至少有一个主外键条件，三张表连接至少有两个主外键条件。

全外连接

自然连接：自然连接 natural join 要有主外键，而且主外键的名字要相同，如果没有主外键，他默认会找字段相同的所有字段进行比较，如果没有字段相同的字段报错

子查询

– 连接查询 笛卡尔积（避免）
– 可以使用主外键关系来去除无用信息
– 注意：

SELECT *,MAX(sal) FROM emp GROUP BY deptno

分组的信息是组内第一条信息，加函数没用

select from 都是独立的子句

函数

​

ADDTIME (date2 ,time_interval )	将time_interval加到date2
CURRENT_DATE ( )	当前日期
CURRENT_TIME ( )	当前时间
CURRENT_TIMESTAMP ( )	当前时间戳
DATE (datetime )	返回datetime的日期部分**
DATE_ADD (date2 , INTERVAL d_value d_type )	在date2中加上日期或时间
DATE_SUB (date2 , INTERVAL d_value d_type )	在date2上减去一个时间
DATEDIFF (date1 ,date2 )	两个日期差
NOW ( )	当前时间
YEAR|Month|Day(datetime )	年月日

CHARSET(str)	返回字串字符集
CONCAT (string2 [,… ])	连接字串
INSTR (string ,substring )	返回substring在string中出现的位置，没有返回0
UCASE (string2 )	转换成大写
LCASE (string2 )	转换成小写
LEFT (string2 ,length )	从string2中的左边起取length个字符
LENGTH (string )	string长度
REPLACE (str ,search_str ,replace_str )	在str中用replace_str替换search_str
STRCMP (string1 ,string2 )	逐字符比较两字串大小**,**
SUBSTRING (str , position [,length ])	从str的position开始**,取length****个字符**
LTRIM (string2 ) RTRIM (string2 ) trim	去除前端空格或后端空格

ABS (number2 )	绝对值
BIN (decimal_number )	十进制转二进制
CEILING (number2 )	向上取整
CONV(number2,from_base,to_base)	进制转换
FLOOR (number2 )	向下取整
FORMAT (number,decimal_places )	保留小数位数
HEX (DecimalNumber )	转十六进制
LEAST (number , number2 [,…])	求最小值
MOD (numerator ,denominator )	求余
RAND([seed])	RAND([seed])

生成SQL脚本 导出数据

在控制台使用mysqldump命令可以用来生成指定数据库的脚本文本，但要注意，脚本文本中只包含数据库的内容，而不会存在创建数据库的语句！所以在恢复数据时，还需要自已手动创建一个数据库之后再去恢复数据。

mysqldump –u用户名 –p密码 数据库名>生成的脚本文件路径

现在可以在C盘下找到mydb1.sql文件了！

注意，mysqldump命令是在Windows控制台下执行，无需登录mysql！！！

2　执行SQL脚本 恢复数据

前提：必须先创建数据库名

执行SQL脚本需要登录****mysql，然后进入指定数据库，才可以执行SQL脚本！！！

执行SQL脚本不只是用来恢复数据库，也可以在平时编写SQL脚本，然后使用执行SQL 脚本来操作数据库！大家都知道，在黑屏下编写SQL语句时，就算发现了错误，可能也不能修改了。所以我建议大家使用脚本文件来编写SQL代码，然后执行之！

SOURCE C:\mydb1.sql

注意，在执行脚本时需要先行核查当前数据库中的表是否与脚本文件中的语句有冲突！例如在脚本文件中存在create table a的语句，而当前数据库中已经存在了a表，那么就会出错！

mysql
–u用户名 –p密码 数据库<要执行脚本文件路径

还可以通过下面的方式来执行脚本文件：

mysql -uroot -p123 mydb1<c:\mydb1.sql

这种方式无需登录mysql！注意：在cmd 下不能加

在多表查询中，在使用列时必须指定列所从属的表，例如emp.deptno表示emp表的deptno列。

其中AS是可以省略的

INNER可以省略，MySQL默认的连接方式就是内连接

不使用WHERE，而是使用ON

OUTER可以省略

内连接

左连接

右连接

大于所有