连接查询

union与union all

合并查询的结果union会去重,union all 不会去重,它俩的作用是把两张表或者更多表合并成一张表
前者会去重（去重的依据是，UNION时SELECT出来的字段如果对应相等则认为是同一条记录，这的逻辑我们可以参考Java equals）但是or 尽管两行数据每个字段都相等,也不会去重后者则不会去重，它会保留两张表中的所有记录，但是它性能高（因为去重操作要花时间），尽量使用union all，把去重这个工作交给代码去完成，这样可以减少MYSQL服务器的压力

使用union / union all的时候要注意：
1.参与合并的表，它们SELECT出来的字段数量必须一致（强制规则）
2.参与合并的表，它们SELECT出来的字段的类型建议一一对应（非强制，但是最好遵循这条规则）
3.参与合并的表，它们SELECT出来的字段的顺序建议一致（非强制，但是最好遵循这条规则）

如 : 查询出 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;

常用函数

函数	解释
select version() ;	显示当前MySQL软件的版本
select database();	显示当前所处数据库是哪个
select char_length(‘中国’);	返回字符个数。
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 ;

自定义函数

create function 函数名([参数列表]) returns 数据类型
begin
 sql语句;
 return 值;
end;

例如

create function m1(str varchar(30)) return int
begin
return length(str);
end
--使用
select m1('张1');

行转列

多行转一行多列

原查询结果

转之后

可以使用下面的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;
--then score 后加else 0 意思默认值0
--借用了max函数

多行转一行一列

这种形式

使用函数:concat(值,‘拼接符’,值): 拼接,多行数据只会拼接一行
group_concat(值,‘拼接符’,值)):拼接,多行压扁到一行

1. 先按姓名分组,给各科成绩结构写出来
   select name,1 as '各科成绩' from test_9 group by name;
2. 将课程名与成绩拼一起

select name,
    concat(course,'=',score) as '各科成绩' 
    from test_9 group by name;

3. 利用group_concat函数将多行压扁到一行,用separator ’ | '修改分隔符(默认,)并 order by course asc按照课程名称排序

select name,
    group_concat(course,'=',score order by course asc  separator ' | ') as '各科成绩' 
    from test_9 group by name;

连接查询

关于笛卡尔积

笛卡尔积,也有的叫笛卡尔乘积,多表查询中,链接的where限定条件,不能少于 表的个数-1 , 否则就会发生笛卡尔乘积 , 这个限定条件并不是随便一个限定条件,而是用于维护映射两个表的条件,比如 外键
笛卡尔乘积是一个很消耗内存的运算，笛卡尔积产生的新表，行数是原来两个表行数的乘积，列数是原来两个表列数的和。所以我们在表连接时要使用一些优化手段，避免出现笛卡尔乘积。最简单的多表查询 : select *from 表1,表2;

如图每个行都有两次,如果select* from teacher,student; ,那么 结果的条数等于两个表的乘积

虽然条数对了,但是也会先发生一个完全的笛卡尔乘积,然后在新视图中找匹配的数据,再展示匹配的数据,会消耗内存一些
所以不推荐使用,推荐使用链接查询

链接查询(比连接查询性能好点)

inner join(内链接)

按照条件合并两张表不符合条件的不显示,例

select *
from teacher tea
inner join student stu on tea.id = stu.teacher_id;
--on也可以写where,但是on在生成表结构时执行比where快

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

left join(左外链接)

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

select * from student s
    left join teacher t on s.teacher_id = t.id
--以左边的表为基准,左表中数据都有,右表中不符合条件的就没有,就在指定列上用null代替,生成视图的时候,也是先生成左表的数据

right join(右外链接)

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

select * from student s
    right join teacher t on s.teacher_id = t.id;

• 能使用 inner join 就不使用 left join
  能使用 left join 就不使用 right join

模拟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;

使用Navicat数据导入和导出

1. 导出
   

2. 导入
   

cmd导入导出

1. 导出
   打开CMD控制台 进入到 MySQL安装路径下的bin目录下
   mysqldump -u用户名 -p密码 需要导出的数据库 > d:\tianliang.sql(导出的文件的保存位置)

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