day34_MySQL

case rank

       when 'A' then '优'

       when 'B' then '良'

       when 'C' then '差'

     end
等同于
case 

   when rank = 'A' then '优'

   when rank = 'B' then '良'

   when rank = 'C' then '差'
  
  end

注意 :

当位于FROM后面时，要注意

1.我们可以把子查询当成一张表

2.必须要有别名,因为子查询优先被执行,子查询的别名,可以让别的查询当做表或者列去操作

1.3 where后面(行限定)

如 : 在不知道teacher_id 和老师名字的对应关系的情况下,想查询出张老师下面的所有学生信息

select * from student where teacher_id in (

select id from teacher where name='张老师'

);

注意 :

当位于WHERE后面时，要注意

多条数据要用in而不要用=,如果确定子查询的结果为一行一列的话,就可以用 = 等于
如果返回结果为多行一列的话要用 in , 一列是必须的,必须是一列

3.子查询中的SELECT后面只能有一个字段（多个字段的话会报错）

2. Union与 union all

合并查询,合并查询的结果

Union 会去除重复项

Union all 不会去除重复项

如 : 查询出 teacher_id = 1 的所有学生信息

select * from student where teacher_id=1;

如 : 查询出学生分数大于60的所有学生信息

select * from student where score > 60;

如 : 查询出学生分数大于60 或 teacher_id = 1 的所有学生信息(去除重复)

// 用 or 实现

select * from student where teacher_id=1 or score > 60;

// 用 union实现

select * from student where teacher_id=1

union

select * from student where score > 60;

如 : 查询出学生分数大于60 或 teacher_id = 1 的所有学生信息(可重复)

select * from student where teacher_id=1

union all

select * from student where score > 60;

总结和注意 :

union / union all

它俩的作用是把两张表或者更多表合并成一张表

前者会去重（去重的依据是，UNION时SELECT出来的字段如果对应相等则认为是同一条记录，这的逻辑我们可以参考Java equals）

但是or 尽管两行数据每个字段都相等,也不会去重

后者则不会去重，它会保留两张表中的所有记录，但是它性能高（因为去重操作要花时间），

尽量使用union all，把去重这个工作交给代码去完成，这样可以减少MYSQL服务器的压力

使用union / union all的时候要注意：

1.参与合并的表，它们SELECT出来的字段数量必须一致（强制规则）

2.参与合并的表，它们SELECT出来的字段的类型建议一一对应（非强制，但是最好遵循这条规则）

3.参与合并的表，它们SELECT出来的字段的顺序建议一致（非强制，但是最好遵循这条规则）

3. 常用函数

select version() ;显示当前MySQL软件的版本

select database();显示当前所处数据库是哪个

select char_length('中国');返回字符个数。如: select char_length(列名)from 表名

select length('中国');返回字符所占字节数,MySQL中，一个UTF8编码的汉字占3个字节

select concat( 'a', 'b', 'c', 'd');返回 'abcd'。字符串拼接函数

select concat_ws( '=', 'a', 'b', 'c');返回 'a=b=c'。字符串拼接函数,第一个是拼接间隔符

select upper('abcd');返回ABCD。将参数中所有小写字母转换为大写

select lower('ABCD');返回abcd。将参数中所有大写字母转换为小写

select substring( '系统信息类', 1, 3 );返回系统信。第2个参数代表从1开始的第几个字符，第3个参数代表截取字符个数

select trim(' abc ');返回 abc。用于删去参数左右的所有空格

select curdate();返回当前日期

select curtime();返回当前时间

select now();返回当前日期时间

select unix_timestamp();返回当前日期时间对应的时间戳（单位秒）

select unix_timestamp('2018-05-24 20:00:00');返回参数指定的日期时间对应的时间戳（单位秒）

select from_unixtime(1527163397);返回参数指定时间戳（单位秒）对应的日期时间

select datediff( '2018-05-23', now() );返回两个参数对应日期相差的天数（用第一个参数减第二个参数）

select adddate( now(), -2 );返回指定天数前/后的日期时间（第一个参数是日期时间，第二个参数是天数，向后加是正数，向前减是负数）

select year('2019-02-24');返回2019 获得年份

select month('2019-02-24') 返回2 获得月份

select day('2019-02-24') 返回 24 获取日

select if( <判断条件>, <条件为真时的返回值>, <条件为假时的返回值> );相当于Java中的三目运算符<判断条件> ? <条件为真的返回值> : <条件为假的返回值>。

如select if(1=1, 2, 3);返回2。

select ifnull(<表达式或者字段>, <表达式或者字段为NULL时的返回值>);通常用于给有可能有NULL的情况下的提供默认值。

select ifnull(null,'无名氏') ; null这里可以写列名就会把该列值为null的以无名氏显示

select ifnull(name,'无名氏') from teacher ;

4. 行转列

4.1 概念

正常的查询结果是这样

可是我想要的结果是这样...

通过SQL语句实现这样的功能,就叫行转列

4.2 示例数据

有 id,名字,课程,成绩

create table test_9(

id int,

name varchar(22),

course varchar(22),

score decimal(18,2)

);

insert into test_9 (id,name,course,score)values(1,'小王','java',99);

insert into test_9 (id,name,course,score)values(2,'小张','java',89.2);

insert into test_9 (id,name,course,score)values(3,'小李','java',88);

insert into test_9 (id,name,course,score)values(4,'小王','MySQL',92.2);

insert into test_9 (id,name,course,score)values(5,'小张','MySQL',42.2);

insert into test_9 (id,name,course,score)values(6,'小李','MySQL',59.2);

4.3 多行转一行多列

可以使用下面的SQL语句（group by 与 case when结合使用即可实现）：

select name,max(

case course

when 'java' then score

end) Java, max(

case course

when 'MySQL' then score

end) MySQL

from test_9

group by name;

思路分析 :

首先我们默认的情况每个名字都输出两次,而最终结果只有一次名字,所以肯定是以名字分组 group by

select * from test_9 group by name;

对我们来说 ,id,课程,和分数都不需要了,只需要有名字然后再把java和mysql放上去

select name , 1 as java , 1 as MySQL from test_9 group by name;

然后再使用聚合函数聚合（此处理解“聚合”，相当于把多行数据压扁成一行）

select name,max(

case course

when 'java' then score

end) Java, max(

case course

when 'MySQL' then score

end) MySQL

from test_9

group by name;

4.4 多行转一行一列

5. DQL-连接查询

5.1 笛卡尔积

笛卡尔积,也有的叫笛卡尔乘积

多表查询中,链接的where限定条件,不能少于表的个数-1 , 否则就会发生笛卡尔乘积 , 这个限定条件并不是随便一个限定条件,而是用于维护映射两个表的条件,比如外键

笛卡尔乘积是一个很消耗内存的运算，笛卡尔积产生的新表，行数是原来两个表行数的乘积，列数是原来两个表列数的和。所以我们在表连接时要使用一些优化手段，避免出现笛卡尔乘积。

最简单的多表查询 : select * from 表1,表2;

示例数据 :

create table teacher(

id int ,

name varchar(20),

primary key (id)

);

create table student (

id int ,

name varchar(20),

teacher_id int ,

primary key (id),

foreign key (teacher_id) references teacher(id)

);

insert into teacher (id,name) values(1,'张老师');

insert into teacher (id,name) values(2,'王老师');

insert into student (id,name,teacher_id) values(1,'小明',1);

insert into student (id,name) values(2,'小红');

insert into student (id,name,teacher_id) values(3,'小黑',2);

每个行都有两次

如果直接就是写的select * from teacher,student; ,那么结果的条数等于两个表的乘积

所以判断条件最少也要是1个,也就是两个表的个数-1

条数对了,因为小红没有teacher_id所以不会被查询出来

虽然条数对了,但是也会先发生一个完全的笛卡尔乘积,然后在新视图中找匹配的数据,再展示匹配的数据,会消耗内存一些

所以不推荐使用,推荐使用链接查询

优化上面的笛卡尔乘积的方式 :

优化一：使用等值连接条件,比如上面的where s.teahcer_id = t.id。

优化二：能使用inner join的就不要使用left join。

优化三：使用记录数更少的表当左表。

但是如果业务上有要求：

比如，我们有一张用户的基本信息表，我们还有一张用户的订单表

现在我们要求在页面上展示，所有用户的订单记录

这种情况下我们就必须使用left join了，因为inner join 会丢数据

假设基本信息表中有A B C三个用户（3条记录）

订单表中有A B两个人的100条订单记录

这种情况下，我们除了使用left join外，还必须要让基本信息表当左表，订单表当右表。

MYSQL支持的表连接查询有inner join，left join，right join（right join我们工作中基本不用）。

5.2 inner join

插入一条示例数据

INSERT INTO teacher (`id`, `name`) VALUES ('3', '孙老师');

select *

from teacher tea

inner join student stu on tea.id = stu.teacher_id;

select *

from student stu

inner join teacher tea on tea.id = stu.teacher_id;

总结 :

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。
在使用 join 连接查询时，on和where条件的区别如下：
1、on条件是在生成临时表时使用的条件,需要和链接查询一起使用。
2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

链接查询,会发生笛卡尔乘积,但是不是完全的笛卡尔乘积,在生成视图的时候,会进行匹配,不符合条件的就不要了

结果数据是以左表数据为准,先生成左表数据,再生成右表数据

使用内连接的话,会以左边表为基准(student),生成新视图的时候,先生成左边表中的数据,然后再去匹配右边表中是否有符合条件的,没有的话,就不生成这一行

同时左表中有的,右表中没有的数据,都不会生成

右表中有的,左表中没有的也一样不会生成,所以左表和右表就算换了位置,数据行数不会变多

但是会丢失数据,不符合条件的数据不会查询出来,所以刚添加的孙老师就不会查询出来的,就算是teacher表在左边,也一样不会查询出来孙老师,并且学生小红也没有被查询处理

因为学生表中 teacher_id列没有保存孙老师的ID,并且小红也没有保存老师的ID,所以都不要

多表查询是有左右表之分的,一般左表是主表,以左边为主

Inner join 也可以直接写join 不写inner

5.3 left join

left join on : 左连接,又称左外链接,是 left outer join 的简写 ,使用left join 和使用 left outer join 是一样的效果

1 查询结果,显示小红,但是不显示孙老师

select * from student s

left join teacher t on s.teacher_id = t.id;

2 查询结果显示孙老师,但是不显示小红

select * from teacher t

left join student s on s.teacher_id = t.id;

总结 :

以左边的表为基准,左表中数据都有,右表中不符合条件的就没有,就在指定列上用null代替

生成视图的时候,也是先生成左表的数据

5.4 right join

right join on : 右链接,又称右外连接,是 right outer join 的简写,使用right join 和使用 right outer join 是一样的

1 查询结果,显示孙老师,但是不显示小红

select * from student s

right join teacher t on s.teacher_id = t.id;

2 查询结果显示小红,但是不显示孙老师

select * from teacher t

right join student s on s.teacher_id = t.id;

总结 :

以右表为基准,右表中数据都有,左表中不符合条件的就没有,就在指定列上用null代替

但是视图生成的时候,还是会先生成左表数据

以上可以看出，student right join teacher 显示的内容是与teacher left join student相同的。而teacher right join student 显示的内容又与student left join student相同。

所以，我们工作中，right join基本不用。用的最多的是inner join 与left join。

PS：外键与表连接没有任何关系，不要混淆。

外键是为了保证你不能随便删除/插入/修改数据，是数据完整性的一种约束机制。

而表连接是因为一张表的字段无法满足业务需求（你想查的字段来自于2张甚至多张表）

一个是为了增删改，一个是为了查，它俩之间没有联系。

能使用 inner join 就不使用 left join

能使用 left join 就不使用 right join

5.5 模拟Oracle中的full join

上面几个链接查询中

inner是两个表都符合条件的数据

left join 是左表都有,右表符合条件才有

right join 是右表都有,左表符合条件才有

那么能不能让两个表,别管符合不符合,都有呢?

full join / full outer join ,但是MySQL这里给取消了,比如Oracle就有

模拟一下 full join 的功能

使用 union 获取并集

select * from student s

left join teacher t on s.teacher_id = t.id

union

select * from student s

right join teacher t on s.teacher_id = t.id;

6. 数据库导入和导出

6.1 使用Navicat数据导入和导出

6.1.2 导出

使用Navicat最为简单

右键要导出的表或者数据库,

转储SQL文件,

仅结构是只有创建表/数据库语句,没有数据

结构和数据是有创建语句,也有数据

6.1.3 导入

右键数据库

运行 SQL文件

会弹出一个窗口

选择完成之后会有相应的地址填写进去

点击开始就行

然后刷新一下表/数据库就可以了

6.2 使用CMD数据导入和导出

6.2.2 导出

打开CMD控制台进入到 MySQL安装路径下的bin目录下

或者直接在地址地方输入cmd,如下图

也可以直接打开

mysqldump -u用户名 -p密码需要导出的数据库 > d:\tianliang.sql(导出的文件的保存位置)

-u用户名 -p密码要备份的数据库, > 是把文件保存到哪里, < 是文件在哪里导进来

执行命令 mysqldump -uroot -proot _06_> d:\tianliang.sql

就被导出了

6.2.3 导入

还是进入到bin目录下

mysql -u用户名 -p密码导入到哪个数据库< d:\tianliang.sql(被导入的文件路径)

-u用户名 -p密码需要导入的数据库需要导入的sql文件

执行命令 mysql -uroot -proot _06_< d:\tianliang.sql

导入完成

6.3 使用Java执行CMD数据导入和导出

package com;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
//导出
public class MySQLUtil {
	// 备份文件名
	private static String fileName;
	// 备份路径
	private static String backupDir;
	// 数据库信息
	private static String database;
	private static String username;
	private static String password;

	public static void setConfig(String fileName, String backupDir,
			String database, String username, String password) {
		MySQLUtil.fileName = fileName;
		MySQLUtil.backupDir = backupDir;
		MySQLUtil.database = database;
		MySQLUtil.username = username;
		MySQLUtil.password = password;
	}

	// 备份
	public static void backup() {
		String command = "cmd /c mysqldump -u" + username + " -p" + password
				+ " " + database;
		// String command =
		// "E:/SoftWare/Work/MySQL/bin/mysqldump -u"+username+" -p"+password+" "+database;
		try {
			// 执行CMD命令
			Process process = Runtime.getRuntime().exec(command);
			BufferedReader br = new BufferedReader(new InputStreamReader(
					process.getInputStream()));
			BufferedWriter bw = new BufferedWriter(new FileWriter(backupDir
					+ "/" + fileName));
			String temp = null;
			while ((temp = br.readLine()) != null) {
				bw.write(temp);
				bw.newLine();
				bw.flush();
			}
			bw.close();
			br.close();
			System.out.println("备份完成,已保存到:" + backupDir + "/" + fileName);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
//导入
	// 恢复
	public static void resume() {

		String command = "cmd /c mysql -u" + username + " -p" + password + " "
				+ database;
		try {
			Process process = Runtime.getRuntime().exec(command);
			BufferedReader br = new BufferedReader(new FileReader(backupDir
					+ "/" + fileName));
			BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(
					process.getOutputStream()));
			String temp = null;
			while ((temp = br.readLine()) != null) {
				bw.write(temp);
				bw.newLine();
				bw.flush();
			}
			bw.close();
			br.close();
			System.out.println("恢复完成,已保存到:" + database);
		} catch (IOException e) {
			e.printStackTrace();
		}

	}

	public static void main(String[] args) {
		MySQLUtil.setConfig("data.sql", "D:/", "_28_", "root", "root");
		// backup();
		resume();
	}
}