数据库基本语句

数据库的基本语句

数据库定义语言DDL简单语句
#数据类型
#常用的整型int，浮点型decimal(18,2)(共18位,16位整数部分与2位小数部分）
#字符型就是字符串类型varchar(100)、char(100)（可以存储100个字符），日期时间型datetime（‘2018-05-23 15:00:00’）
#varchar和char的区别 就相当于StringBuffer和String的区别
#varchar类型定义后，他所占的实际空间就是里面存储的内容所占用的空间，但是最多不能超过你括号里面设置的长度，对空间的利用率很高
#char类型定义后，不管里面是否有值，他就固定占用你设置的存储空间大小，添加快

#SQL语句功能分类
#DDL : Data Definition Language (数据库定义语言) : create(新建), drop(删除), alter(修改)
#DML : Data Manipulate Language(数据库操作语言) : insert(添加), delete(删除), update(修改)
#DQL : Data Query Language (数据库查询语言) : select(查询)

#创建一个数据库
create database my_db;
#删除库 执行删除的时候一定要慎重
drop database my_db;
#新建一个表
create table teacher(
id int,
name varchar(100),
salary decimal(18,2)
);
#通常一张表是可以和一个Java实体类对应起来的。
#一个表对应一个类。一列对应一个变量。一行数据对应一个对象
#删除表
drop table teacher;
#查看一张表的结构 包括列、列类型、是否可以为空、主键、默认值、补充信息等信息
desc teacher;
#查看建表的语句
show create table teacher;
#下面是查询出来的建表语句
#通常，对于SQL语法中的关键字比如CREATE等，使用大写，其他可以用小写。
CREATE TABLE teacher (
#列名它会自动加上反引号 DEFAULT NULL，代表这个字段默认值是NULL值。
id int(11) DEFAULT NULL,
name varchar(100) DEFAULT NULL,
salary decimal(18,2) DEFAULT NULL
#MyISAM：前者适用读多写少的场景，它的读性能高。
#InnoDB：如果我们想要支持外键，必须使用InnoDB，它的读性能相对于前者来说稍低
#（但是随着技术发展，现在InnoDB的性能已经提升了很多了，不比MyISAM差）
) ENGINE=InnoDB DEFAULT CHARSET=utf8
#更改已经建好表的存储引擎
alter table teacher ENGINE=MyISAM;
#反引号去区分表名/字段名和关键字
create table table(
id int,
name varchar(100),
salary decimal(18,2)
);
#删除表
drop table table;
#修改已经建好的表名
alter table teacher rename new_teacher;
#添加列 要指定数据类型
alter table new_teacher add birthday datetime;
#添加列到指定列后面 要指定数据类型
alter table new_teacher add birthday1 datetime after name;
#添加列到第一列 要指定数据类型
alter table new_teacher add birthday2 datetime first;
#删除一列
alter table new_teacher drop birthday1;
#更改一列的数据类型 尽量是把类型扩大 或者相同类型的长度扩大 不要反过来改 而且尽量不要跨类型更改 例如decimal改为int
alter table new_teacher modify salary decimal(20,2);
#给表的列改名，同时也可以把类型改了
alter table new_teacher change name teacher_name varchar(200);
数据库操作语言DML简单语句
#它通常用来向一个表中插入、修改、删除一行记录。涉及到的命令有insert，update，delete
#向表中添加数据 最好在前面加上对应的列名 这样在之后加入一列更改表的时候，语句照样不会出错
insert into student (name,id,score) values (‘老刘’,2,120);
#value是加一个，values是加多个
insert into student (name,id,score) value (‘老王’,2,100);
#一次性添加多个数据 用逗号隔开
insert into student (name,id,score) values (‘老王’,3,100),(‘小明’,3,90),(NULL,4,20);
#无论是删除还是更新 只要不是全表操作 就一定要加上where条件
#删除一条数据 加where 符合where条件的会全部删除 但是不加where 会删除该表中所有的数据
delete from student where name = ‘老刘’;
#删除空数据
delete from student where name is null;
#更新数据
update student set score = 90 where name = ‘老王’;
#让所有的成绩减30
update student set score = score-30;
#让指定行的成绩加上30
update student set score = score+30 where name = ‘小明’;
简单的SQL语句
#查看当前链接的MySQL服务器版本
select version();
#查看当前链接的MySQL中的所有数据库
show databases;
#输出指定内容
#列名和内容是一样的
select ‘小明’;
#as后面的是列名
select ‘小明’ as ‘姓名’;
#没有加as会默认一起 输出是小明姓名
select ‘小明’ ‘姓名’;
#切换数据库
use test();
#显示所有的表
show tables;
#退出 exit; 在MySQL客户端使用，Navicat不能使用
基础的创建删除语句DDL
#创建数据库
create database test;
#删除数据库
drop database test;
#切换到指定数据库
use 练习;
#创建表
create table student(
#列之间用逗号隔开
id int,
#关键字用反引号括起来 类型后面可以设置长度
#varchar和char的区别 就相当于StringBuffer和String的区别
#varchar类型定义后，他所占的实际空间就是里面存储的内容所占用的空间，但是最多不能超过你括号里面设置的长度，对空间的利用率很高
#char类型定义后，不管里面是否有值，他就固定占用你设置的存储空间大小，添加快
‘name’ varchar(20),
#decimal是小数型 后面的18是共18个 2是小数点后面的位数
#就是小数点前面16位，小数点后面2位 加起来一共18位
score decimal(18,2)
#下面的代码 加或者不加都可以，默认会有 默认innodb 存储引擎可以理解为数据库中的文件系统
#innodb：支持事务，支持锁，并且锁也支持表级别的锁，还有行级别的锁 事务就是出错之后可以回滚 可以做一些银行，金融之类的
#myisam：适合做一些记录日志，或者读比写多，它的写性能相对innoDB好一些，就是不支持事务
#但是目前为止,通过对InndDB的改进,也可以做日志,或读比写多的一些场景,目前新项目基本都用InnoDB较多,老项目一般都还是MyISAM
)ENGINE = innodb default charset = utf8;
#显示创建表语句 在Create Table 列中显示
show create table student;
#删除表
drop table student;
基础的查询语句DQL
#切换表
#最好是先切换表在进行操作 有可能你的操作语句并不在对应的数据库或者表进行 先进行切换表 可以减少错误
use student;
#查询语句 select 列限定 from 表限定 where 行限定;
#查询学生表中，成绩为100分的所有学生信息
select *from student where score = 100;
#查询学生表中，成绩在90分包括90分以上的所有学生信息
select *from student where score >= 90;
#查询所有学生信息
select * from student;
#查询所有学生的id和姓名
select id,name from student;
主键自增
#设置主键
create table person(
#第一种是在最后面写主键约束语句
#not null 和default也是一种约束 不为空约束和起始值约束
id int not null default 0,
name varchar(100),
income decimal(18,2),
#设置主键约束语句，可以一次性声明多个主键
primary key(id,name)
);
#第二种在声明列时添加主键
create table person2(
#主键的特性就是不为空和唯一 所以不用再写not null的约束条件
id int primary key,
name varchar(100),
income decimal(18,2)
);
#设置自增
create table person3(
id int auto_increment primary key,
name varchar(100),
income decimal(18,2)
);
#建好表之后设置主键和自增
create table person4 (
id int,
name varchar(100),
income decimal(18,2)
);
#添加主键
#这里也可以添加多个主键
alter table person4 add primary key(id);
#设置自增 可以在更改数据类型的时候设置自增
alter table person4 modify id int(11) auto_increment;
#设置自增的起始值
alter table person4 auto_increment = 10000;
外键约束
#外键 绑定两个表 它通常用于将2个表的数据进行关联，以防止不当的数据插入
#两个表的数据类型要一致
#主表的一定要是主键
#外键列的值可以为空,如果设置值,值的范围必须是关联表中的已有的主键值
#建表时添加外键
#首先要先有主表
create table teacher(
id int primary key,
name varchar(100),
income decimal(18,2)
);
create table student(
id int not null default 0,
name varchar(100),
score decimal(18,2),
teacher_id int ,
primary key(id),
foreign key(teacher_id) references teacher(id)
)
#已经建好表后添加外键
create table student1(
id int primary key,
name varchar(100),
teacher_id int
);
create table teacher1(
id int primary key,
name varchar(100)
);
alter table student1 add foreign key(teacher_id) references teacher(id);
条件判断
#条件判断类
#select if( <判断条件>, <条件为真时的返回值>, <条件为假时的返回值> );
select if(1=1, 2, 3);
#select ifnull(<表达式或者字段>, <表达式或者字段为NULL时的返回值>);
select ifnull(name,“无名氏”) from teacher;
其他约束
#not null，default <默认值>。通常用在非主键的其他字段上。
#约束not null保证字段不能有NULL，通常和default <默认值>联用
#建表的时候添加约束
create table test(
id int not null default 0,
name varchar(100) not null,
income decimal(18,2) not null default 0.0
);
#建好表后通过修改表的结构添加约束
alter table test modify id int not null default 0;
#一个比较标准且能保证数据完整性的建表语句
create table student3(
id int not null default 0,
name varchar(100) not null default ‘’,
score decimal(18,2) not null default 0.0,
teacher_id int not null default 0,
primary key (id)
) engine=InnoDB default charset=utf8;
查询语句
#简单查询
select * from student;
select id,name from teacher;
select name from teacher where id = 1;
#系统信息类
#显示当前链接的MySQL版本
select version();
#显示当前是哪个数据库
select database();
#字符类
#返回字符个数
select char_length(‘中国’);
#返回占用字节个数,汉字占用3个字节,别的都是1个字节
select length(‘中国’);
#字符串拼接,可变元参数 abcd
select concat( ‘a’, ‘b’, ‘c’, ‘d’);
#字符串拼接,可变元参数,但是第一个是拼接符 a=b=c
select concat_ws( ‘=’, ‘a’, ‘b’, ‘c’);
#转为大写
select upper(‘abcd’);
#转为小写
select lower(‘ABCD’);
#截取字符串,从第一个开始,截取3个(没有下标,从第一个开始)
select substring( ‘系统信息类’, 1, 3 );
#去除两边空格
select trim(’ abc ‘);
#需求 : 表中有name列,都为英文,查看所有name
#并且要求查看到的结果为首字母大写,其余小写
#比如 : teacher 得到 teacher
select concat(upper(substring(name,1,1)),lower(substring(name,2,char_length(name)-1))) from student;
#需求 : 把name列的值,全部更改为首字母大写,其余小写
update student set name=concat(upper(substring(name,1,1)),lower(substring(name,2,char_length(name)-1)));
#日期时间类
#返回当前日期 yyyy-MM-dd
select curdate();
#返回当前时间 HH:mm:ss
select curtime();
#返回当前日期和时间 yyyy-MM-dd HH:mm:ss
select now();
#返回当前日期时间对于的时间戳(单位秒),1970/1/1 8:00 到现在的秒数
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(now());
#返回月
select month(now());
#返回日
select day(now());
case
create table test_01 (
id int,
name varchar(20),
score decimal(18,2)
);
insert into test_01(id,name,score) values (1,‘小红’,99.8);
insert into test_01(id,name,score) values (2,‘小明’,23);
insert into test_01(id,name,score) values (3,‘小刚’,67);
insert into test_01(id,name,score) values (4,‘小黑’,45.32);
#需求 在不改变表结构的前提下,对查询的结果,按照成绩(score),进行分类
#小于60分 差 , 大于等60分小于80分 良, 大于等于80分 优
select id,name,score,
#这里case后面没有加score能执行 是因为下面加了
case
when score < 60 then ‘差’
when score >= 60 and score < 80 then ‘良’
when score >= 80 then ‘优’
end
as ‘等级’ from test_01;
#手动创建视图 create view 视图名 as 查询语句;
create view stu_score as select id,name,score,
case
when score < 60 then ‘差’
when score >= 60 and score < 80 then ‘良’
when score >= 80 then ‘优’
end
as ‘rank’ from test_01;
#case rank 这样写 下面就可以直接写’优’ 而不用写 rank=’优’
#上面成绩的不是这么写的,因为需要判断小于和大于等
select id,name , score,rank,
case rank
when ‘优’ then’A’
when ‘良’ then ‘B’
when ‘差’ then ‘C’
end as crank from stu_score;
组函数
create table student2 (
id int,
name varchar(20),
teacher_id int,
score decimal(18,2),
primary key (id)
);
create table teacher2(
id int ,
name varchar(20),
primary key (id)
);

insert into teacher2(id,name)values(1,‘小张’);

insert into teacher2(id,name)values(2,‘小王’);

insert into student2(id,name,teacher_id,score)values(1,‘张三’,1,90);
insert into student2(id,name,teacher_id,score)values(2,‘李四’,2,88);
insert into student2(id,name,teacher_id,score)values(3,‘王五’,1,45);
insert into student2(id,name,teacher_id,score)values(4,‘赵六’,1,84);
insert into student2(id,name,teacher_id,score)values(5,‘小明’,2,92);
insert into student2(id,name,teacher_id,score)values(6,‘小红’,2,47);

#MYSQL中有一类特殊的函数，用于统计，或者分组统计
#常用的：
#count()求数量
#max(字段名)求最大值
#min(字段名)求最小值
#avg(字段名)求平均值
#sum(字段名)求总和
#计算表中一共有多少条数据
select count() from student2;
#括号里面写1和写是一样的 但是一般都写
select count(1) from student2;
#计算表中满足条件的条数 select count() from student2 where A;
select count() from student2 where score > 80;

#分组 group by 字段名
#展示每个老师带了几个学生
select teacher_id,count() as stu_count from student2 group by teacher_id;
#展示每个老师带的学生中的最高成绩
#as不加也可以 但是建议加上 语句会更清晰一些
select teacher_id,count() as stu_count,max(score)as stu_maxscore from student2 group by teacher_id;
#统计每个老师所带学生的总成绩与平均分
select teacher_id,sum(score) as sum ,avg(score) as avg from student2 group by teacher_id;
select teacher_id,sum(score) as sum , cast( avg(score) as decimal(4,2) ) as avg from student2 group by teacher_id;

#如果我们想基于统计结果，再做进一步的筛选，就要使用having关键字了
#顺序 group by , having , order by 先分组,再过滤,最后排序
#用于统计每个老师所带学生的平均成绩大于等于60分的记录
select teacher_id,sum(score) as sum,avg(score) as avg from student group by teacher_id having avg >= 60;
#Where没有办法使用 列限定上的别名(因为执行行限定的时候还没有执行列限定,如果列限定先执行,那么where就可以使用),而 having 可以使用列限定上的别名
子查询
#子查询又叫嵌套查询。它通常可以位于SELECT后面 FROM后面 WHERE后面
#使用子查询的时候，建议大家养成使用别名的好习惯，这样可以让我们的查询语句更加清晰。
#别名可以用来命令新字段，也可以用来命名新表.

#第一种 select后面
#当位于SELECT后面时，要注意
#1.一定要在两个表之间找好对应关系（teacher.id必须是主键或者必须保证teacher.id在teacher表中是唯一的）
#2.子查询中只能有一个字段（子查询的结果必须是一行一列）

#需求 : 查询所有学生的信息并显示老师的名字
#可以先把学生的全部信息展示出来 再去想办法展示老师姓名
#老师姓名是单独一列 所以要在列限定处添加语句
#当学生表中teacher_id等于老师表中的id时就显示对应的老师姓名
select *,(
select name from teacher2 where id = teacher_id
)as teacher_name from student2;
#需求 : 查询所有学生的信息并显示老师的名字,只显示学生姓名，成绩和老师姓名
select name,score,(
select name from teacher2 where id = teacher_id
)as teacher_name from student2;

#需求 : 查询每个老师的学生的 最大分数,最小分数,平均分数,分数总和,学生人数,老师名字
#先展示学生的 最大分数,最小分数,平均分数,分数总和
select max(score),min(score),avg(score),sum(score) from student2;
#在展示老师的学生人数
select count() from student2 group by teacher_id;
#在展示老师姓名
select name from teacher2 as teacher_name;
#最后总和
select max(score),min(score),avg(score),sum(score),count(),(
select name from teacher2 where id = teacher_id) as teacher_name
from student2 group by teacher_id;

#第二种 from后面
#当位于FROM后面时，要注意
#1.我们可以把子查询当成一张表
#2.必须要有别名,因为子查询优先被执行,子查询的别名,可以让别的查询当做表或者列去操作

#需求 : 使用子查询 对成绩划分等级, score<60 ,评级C 并且是差,
#score>=60 且 score<80 评级B并且是良,
#score>=80 评级是A并且是优
#先解决一个 比如先解决 优良差 的问题
select *,
case
when score < 60 then ‘差’
when score >= 60 and score < 80 then ‘良’
when score >= 80 then ‘优’
end
as rank from student2;
#优良差的问题解决了 解决ABC的问题
select *,
case rank
when ‘优’ then ‘A’
when ‘良’ then ‘B’
when ‘差’ then ‘C’
end
as rank_ch from(
select *,
case
when score < 60 then ‘差’
when score >= 60 and score < 80 then ‘良’
when score >= 80 then ‘优’
end
as rank from student2
#一定要有别名 from后面的是子查询 优先被执行 也就是我们上面写的解决的第一步问题
#子查询的别名 可以让别的查询当做表或者列去操作
)a;

#第三种 where后面
#当位于WHERE后面时，要注意
#多条数据要用in而不要用=,如果确定子查询的结果为一行一列的话,就可以用 = 等于
#如果返回结果为多行一列的话 要用 in , 一列是必须的,必须是一列
#子查询中的SELECT后面只能有一个字段（多个字段的话会报错）

#需求 : 在不知道teacher_id 和 老师名字的对应关系的情况下
#想查询出张老师下面的所有学生信息
select id,name,score,(
select name from teacher2 where name = ‘小张’
)as teacher_name from student2 where teacher_id in (
select id from teacher2 where name = ‘小张’
);
模糊查询、去重、排序、限制条数
#模糊查询 like ‘%%’
#_ : 匹配一个任意字符
#% : 匹配 0~N个任意字符
#需求 : 把名字中 第一个字为’小’的查询出来
select * from test_01 where name like ‘小%’;
#需求 : 把名字中 第一个字为’小’ 的,并且名字只有两个字的, 查询出来
select * from test_01 where name like ‘小_’;
#查询名字中带’小’的
select * from test_01 where name like ‘%小%’;

#去重distinct
insert into test_01 (id,name,score) values (7,‘小黑’,88);
#这是全部输出的
select name from test_01;
#去重之后 重复数据就会只展示一个
select distinct name from test_01;

#排序order by 字段名[desc|asc]
#降序是desc 升序是asc 默认是asc
select * from test_01 order by score;
#这两条语句输出结果相同 不加的话默认就是asc升序
select * from test_01 order by score asc;
#需求 : 按照成绩 降序
select * from test_01 order by score desc;
#需求 : 先按成绩降序 如果成绩相同 再按id升序
select * from test_01 order by score desc,id asc;

#限制条数limit 条数 通常与order by 配合使用
#求成绩在前三名的学生
select * from test_01 order by score desc limit 3;
#限制条数还可以做分页使用
#从第一个开始 不包含第一个 取两个
#分页的情况 比如第一页展示 m，n 第二页就是 m+n，n 以此类推
select * from test_01 limit 1,2;
合并查询
#合并查询
#参数合并的表,他们的select出来的字段数量必须一致
#字段的类型建议一一对应
#union / union all
#它俩的作用是把两张表或者更多表合并成一张表
#前者会去重（去重的依据是，UNION时SELECT出来的字段如果对应相等则认为是同一条记录，这的逻辑我们可以参考Java equals）
#后者则不会去重，它会保留两张表中的所有记录，但是它性能高（因为去重操作要花时间），
#尽量使用union all，把去重这个工作交给代码去完成，这样可以减少MYSQL服务器的压力

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

#需求 : 查询出 teacher_id = 1 的所有学生信息
select * from student2 where teacher_id = 1;
#需求 : 查询出 学生分数大于60的所有学生信息
select * from student2 where score > 60;
#需求 : 查询出 学生分数大于60 或 teacher_id = 1 的所有学生信息(去除重复)
select * from student2 where score > 60 or teacher_id = 1;
#or有去重的作用
#去重用union来写
select * from student2 where score > 60
union
select * from student2 where teacher_id = 1;
#需求 : 查询出 学生分数大于60 或 teacher_id = 1 的所有学生信息(可重复)
select * from student2 where score > 60
union all
select * from student2 where teacher_id = 1;
常用的where条件
#常用的where条件
#大于等于使用 >=。小于等于使用 <=。小于使用 <。等于使用 =。不等于使用 <>
select * from test_01 where score > 80;
select * from test_01 where score <> 23;

#查询语句select * from student where A and B;
#筛选student表中既满足条件A又满足条件B的记录。
#而select * from student where A or B;
#筛选的则是只要满足A或者B其中一个条件即可的记录

#如果出现多个and,或者多个or,按照从左到右的顺序
#如果and和or一起出现,and优先级大于 or , 需要加括号区分

#需求 : 成绩大于 60 或 id 为 3 , 且 名字叫小黑的
select *from test_01 where (score > 60 or id = 3) and name = ‘小黑’;
#需求 : 成绩大于 60 , 或 id为3 且 名字叫小黑的
select *from test_01 where score > 60 or id = 3 and name = ‘小黑’;

select * from test_01 where score>=60 and score<=80;
#等价于
select * from test_01 where score between 60 and 80;
#逻辑否
select * from test_01 where score not between 60 and 80;

select * from test_01 where score = 23 or score = 67;
#等价于
select * from test_01 where score in (23, 67);
#逻辑否
select * from test_01 where score not in (23, 67);
连接查询
#笛卡尔积
#多表查询中，链接的where限定条件，不能少于表的个数-1，否则就会发生笛卡尔积
#笛卡尔积是一个很消耗内存的运算
#笛卡尔积产生的新表，行数是原来两个表行数的乘积，列数是原来两个表列数之和

#优化 避免笛卡尔积

#优化一：使用等值连接条件
#但是优化一其实已经发生笛卡尔乘积，虽然展示结果对，但是先执行表限定，在执行行限定
#在执行表限定时就已经发生笛卡尔乘积，在进行数据筛选，耗内存，效率低
#所以并不推荐使用优化一
select * from student2 s,teacher2 t where s.teacher_id = t.id;

#优化二：能使用inner join的就不要使用left join
#inner join (内连接)
#会以左表为基准，生成新视图的时候，先生成左表中的数据，
#然后再去匹配右表中的数据是否有符合条件的，没有就不生成这一行
#同时 左表中有的,右表中中没有的数据,都不会生成
#右表中有的,左表中没有的,也不会生成,所以 左表和右表就算交换了位置,数据行数也不会变多
#多表查询是有左右表之分的,一般左表是主表,以左表为主
#先去匹配条件 再生成行，就不会发生笛卡尔乘积
#inner join取的是两表的交集 所以会造成数据丢失
#以学生表为主表 先生成学生表的数据，在生成老师表的数据
select * from student2 s
inner join
teacher2 t
on
t.id = s.teacher_id;
#以老师表为主表 先生成老师表的数据，在生成学生表的数据
select * from teacher2 t
inner join
student2 s
on
t.id = s.teacher_id;
#left join 左连接(左外连接) left outer join 的简写
#以左表为基准，左表中的数据都有，右表中不符合数据的就没有，就在指定列上用null代替
#生成视图的时候，也是先生成左表的数据
#以学生表为主表 先生成学生表的数据，在生成老师表的数据
#学生表中没有老师的也会显示 但是老师中没有学生的不会显示
select * from student2 s
left join
teacher2 t
on
t.id = s.teacher_id;
#以老师表为主表 先生成老师表的数据，在生成学生表的数据
#老师中没有学生的也会显示 但是学生表中没有老师的不会显示
select * from teacher2 t
left join
student2 s
on
t.id = s.teacher_id;
#right join 右连接(右外连接) right outer join 的简写
#right join和left join 相同
#但是right join 是以右表为基准 右表中的数据都有，左表中不符合的数据没有 就在指定列上用null代替
#但是生成视图时还是会先生成左表的数据
#以老师表为主表 先生成学生表的数据，在生成老师表的数据
#右连接以老师表为主和左连接以学生表为自主显示结果相同
select * from student2 s
right join
teacher2 t
on
t.id = s.teacher_id;
#以学生表为主表 先生成老师表的数据，在生成学生表的数据
#右连接以学生表为主和左连接以老师表为主显示结果相同
select * from teacher2 t
right join
student2 s
on
t.id = s.teacher_id;

#由于右连接和左连接是相同的，我们一般使用左连接 右连接一般不用
#多表进行链接查询的时候,能使用inner join 就不使用 left join
#能使用 left join 就不使用 right join

#外键与表连接没有任何关系，不要混淆
#外键是为了保证你不能随便删除/插入/修改数据，是数据完整性的一种约束机制
#而表连接是因为一张表的字段无法满足业务需求（你想查的字段来自于2张甚至多张表）
#外键是为了增删改，表连接是为了查，它俩之间没有联系

#如果需要两个表都有 就需要full join 但是MySQL并不支持full join
#full join是Oracle支持的 MySQL可以模拟full join
select * from student2 s
left join
teacher2 t
on
s.teacher_id = t.id
union
#由于右连接以老师表为主和左连接以学生表为自主显示结果相同
#所以这里的右连接就以老师表为主表 这样不会发生错位(显示结果老师去学生表中)
select * from student2 s
right join
teacher2 t
on
s.teacher_id = t.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);

#正常的显示就是多行多列 列：id,name,course,score 行：每一行的数据
select * from test_9 ;
#第一种
#想要将多行转为一行多列 列：name,Java,MySQL 行：姓名,对应的Java分数,对应的MySQL分数
#可以按姓名分组来做 第一步
select name, 1 as ‘Java’, 1 as ‘MySQL’ from test_9 group by name;
#分好组后 下面把对应的成绩展示出来
select name,
#能查询到成绩就展示成绩 查询不到就展示0
case course when ‘Java’ then score else 0 end as ‘Java’,
case course when ‘MySQL’ then score else 0 end as ‘MySQL’
from test_9 group by name;
#由于分好组后 只显示第一条语句 第二条语句找不到 使用组合函数就会分别去两行数据中去查找
#如果不使用聚合函数,之后去第一行数据中查询,重合的数据找不到
#如果使用分组函数的话,就会去这两行数据中分别找数据
#如 max 就会去两行中找,分别判断是不是java和MySQL
#else 0 如果没有找到对应的java或者MySQL,就显示0
select name,
max(
case course when ‘Java’ then score else 0 end
)as ‘Java’,
max(
case course when ‘MySQL’ then score else 0 end
)as ‘MySQL’
from test_9 group by name;

#第二种
#想要将多行转为一行一列 列：name,各科成绩 行：姓名,对应的各科成绩
#还是按照姓名来分组
select name,1 as ‘各科成绩’ from test_9 group by name;
#各科成绩列要展示各科的成绩 将课程名和成绩拼接起来
select name,
concat(course,’=’,score) as ‘各科成绩’
from test_9 group by name;
#但是分组之后只会展示一条数据 所以利用group_concat函数拼接多行数据
select name,
group_concat(course,’=’,score) as ‘各科成绩’
from test_9 group by name;
#group_concat函数默认是以,进行分隔，可以设置分隔符
select name,
group_concat(course,’=’,score separator ’ | ‘) as ‘各科成绩’
from test_9 group by name;
#还可以进行排序 按课程名称排序 是以一行进行排序 决定哪科成绩在前(按课程名称来的 在每个人的所有课程数和课程名称相同时，每个人的排序顺序相同) 以ASCII值进行比较
select name,
group_concat(course,’=’,score order by course asc separator ’ | ‘) as ‘各科成绩’
from test_9 group by name;
#还可以按照成绩进行排序 按照成绩进行排序 是一行进行排序 决定是哪科成绩(按成绩来的，可能每一个人的排序结果不同)
select name,
group_concat(course,’=’,score order by score asc separator ’ | ') as ‘各科成绩’
from test_9 group by name;