MySQL学习笔记

时间:2020-06-07 16:31:30   收藏:0   阅读:41

数据库安装后建议先设置好字符集

vim /etc/my.cnf
[mysql]
default-character-set=utf8mb4
[mysqld]
character-set-server=utf8mb4
#注意要设置为支持的字符集

显示所有支持字符集 show character set;
查看当前使用字符集 show variables like ‘character%‘;
查看所有排序 show collation;
查看当前使用排序 show variables like ‘collation%‘;

查询范例

#子查询
select stuid,name,age from students where age > (select avg(age) from students);
#联合查询
select * from teachers union all select * from teachers;
#交叉连接,产生笛卡尔乘积数的数据量
select * from students cross join teachers;
#内连接
select * from students s  inner join teachers t on s.teacherid=t.tid;
#完全外连接
select s.stuid s_id,s.name s_name,s.age s_age,t.tid t_id,t.name t_name,t.age t_age 
   from 
   students s left join teachers t on s.teacherid=t.tid
   union  
   select s.stuid s_id,s.name s_name,s.age s_age,t.tid t_id,t.name t_name,t.age t_age 
   from  students s right join teachers t on s.teacherid=t.tid;
#完全外连接扩展
select s.stuid s_id,s.name s_name,s.age s_age,t.tid t_id,t.name t_name,t.age t_age 
   from 
   students s left join teachers t on s.teacherid=t.tid where t.tid is null 
   union  
   select s.stuid s_id,s.name s_name,s.age s_age,t.tid t_id,t.name t_name,t.age t_age 
   from  students s right join teachers t on s.teacherid=t.tid where s.teacherid is null;

 

原文:https://www.cnblogs.com/zy2271/p/13060964.html

评论(0
© 2014 bubuko.com 版权所有 - 联系我们:wmxa8@hotmail.com
打开技术之扣,分享程序人生!