海口企业建站系统模板,荔浦网站开发,工程公司管理系统,南通市优普网站建设1 多表联查
1.1 表之间的关系 表和表的关系有: 一对一 老公 -- 老婆 , 人 --- 身份证/户口本 一对多 皇帝 -- 妻妾 , 人 --- 房/车 多对多 订单 -- 商品 1.2 合并结果集 合并结果集,是将多表查询的结果纵向合并 语法: select field1,field2 from t1
un…1 多表联查
1.1 表之间的关系 表和表的关系有: 一对一 老公 -- 老婆 , 人 --- 身份证/户口本 一对多 皇帝 -- 妻妾 , 人 --- 房/车 多对多 订单 -- 商品 1.2 合并结果集 合并结果集,是将多表查询的结果纵向合并 语法: select field1,field2 from t1
union -- 合并结果集
select field1,field2 from t2 create table tb_a(id int,name char(10),age int
);
create table tb_b(id int,name char(10)
);
/*合并结果集的两个表的字段数量,类型要一致
-----------
union 联合数据,将数据纵向拼接,如果有重复数据会去重
union all 如果有重复数据会全部保留
--------------
场景:
当表很大时,可以拆分成多个表
就可以使用联合查询
*/
select id,name from tb_a
union all
select id,name from tb_b
1.3 连接查询【重要】 连接查询是将多张表数据连接在一起(横向)查询返回, 这个连接是多表的乘积,t1 * t2 , 这就是笛卡尔积 连接查询需要使用表之间的关联关系来过滤数据 连接查询分为以下几种 内连接 外连接 1.3.1 内连接 数据准备, class表是班级表,stu是学生表, 一个班级对应多个学生 两表的关联列是 学生表(stu)中的cid,引用了班级表(class)中的主键cid 语法: select 字段列表 from 表1 inner join 表2 on 表1.字段 表2.字段 /*内连接select 字段列表 from 表1 inner join 表2 on 表1.字段 表2.字段
*/
-- 查询学生信息以及学生关联的班级信息
select * from stu inner join class on stu.cid class.cid;
-- 查询学生的学号,姓名,分数,班号,班名
select stu.sid,stu.sname,stu.score,stu.cid,class.cname
from stu
inner join class
on stu.cid class.cid;
-- 也可以给表设置别名
select s.sid,s.sname,s.score,s.cid,c.cname
from stu s
inner join class c
on s.cid c.cid;
-- 内连接特点:只会查询满足关联条件的数据
-- 内连接标准写法
select * from stu inner join class on stu.cid class.cid;
-- 内连接可以简写成(推荐)
select * from stu s,class c where s.cid c.cid;
-- 标准语法
-- 简写
-- 特点
-- 什么叫笛卡尔积
-- 去除笛卡尔积
练习
-- 查询1班信息,以及对应学生信息
select * from class c,stu s where c.cid s.cid and c.cid 1;
-- 查询成绩大于60的学生信息,以及对应的专业
select * from stu s,class c where s.cid c.cid and score 60;
-- 查询班级编号,班级名称,和每班人数
select c.cid,c.cname,count(sid) from class c,stu s
where c.cid s.cid
group by c.cid,c.cname
1.3.2 外连接 外连接又分为左外连接,右外连接 法: select 字段列表 from 表1 left|right outer join 表2 on 表1.字段 表2.字段 内外连接有什么区别? 内连接只查询符合关联添加的数据 外连接会保留不符合条件的数据 -- 1) 外连接会保留不符合条件的数据
-- 2) 左外是以左表为主,左表中有不符合条件的数据也会保留
-- 右外相反...
-- 查询学生信息以及对应的班级信息
-- 左外
select * from stu s left outer join class c on s.cid c.cid
-- 右外
select * from stu s right outer join class c on s.cid c.cid
-- outer可以省略
select * from stu s left join class c on s.cid c.cid
1.4 子查询【重要】 子查询(subquery)也叫嵌套查询 将sql语句当表,写在from后面 将sql语句当条件,写在where后面 -- 子查询就是嵌套查询
-- 查询的结果是一张虚拟表
select sid,sname,age from stu where sex 男
-- 子查询当表
select * from
(select sid,sname,age from stu where sex 男) t
where t.age 50
-- 子查询当条件,但是要注意条件的值的个数(列数和行数)
select age from stu where sid 1001
-- 年龄大于学号为1001这个人的年龄
select * from stu
where age (select age from stu where sid 1001)
-- 查询与张三同一个班级的学生。
select * from stu
where cid (select cid from stu where sname 张三);
-- 成绩高于3号班级所有人的学生信息
select * from stu
where score (select max(score) from stu where cid 3)
-- 有2个以上直接组员的学生信息
select * from stu where sid in(
select groupLeaderId from stu
group by groupLeaderId
having count(sid) 2)
-- 求1008学生编号、姓名、组长编号和组长姓名
SELECTt1.sid,t1.sname,t1.groupLeaderId,t2.sname
FROMstu t1,(SELECT* FROMstu WHEREsid ( SELECT groupLeaderId FROM stu WHERE sid 1008 ) ) t2
WHEREt1.sid 1008
-- 上面这题可以改造成自连接
select s.sid,s.sname,s.groupLeaderId,z.sname from stu s,stu z where s.groupLeaderId z.sid and s.sid 1008
2 函数
2.1 字符串函数 -- 字符串函数
select charset(abc); -- 返回字符集
select charset(abc) from dual; -- from dual,只是为了补全sql语句
-- concat(str1,....) 连接字符串 【重要】
select concat(a,1,b,2) from dual;
select concat(a,1,b,2),sid,sname from stu;
select concat(sid,sname),sid,sname from stu;
select concat(我叫,sname,,今年,age,明年,age1,岁) from stu;
-- instr(string,substring),返回substring在string中出现的位置,没有返回 0
select instr(java,c);
select instr(sname,三) from stu;
-- 转大写,转小写
select ucase(abc),lcase(abc);
-- left(string2,length) 从 string2 中的左边起取 length 个字符
select left(java,2)
select left(sname,1) from stu; -- 取出姓氏
-- length 获得长度 , utf8中,一个中文三个字节
select length(sname),sname from stu;
select length(abc);
-- 替换
-- REPLACE (str ,search_str ,replace_str ) 在 str 中用 replace_str 替换 search_str
select replace(java,av,AV);
select replace(sname,三,叁) from stu;
-- SUBSTRING (str , position [,length ] 截取
select substring(java,2); -- 从第2位,取到末尾
select substring(java,2,2); -- 从第2位,取2个
-- 取出stu表中姓名,姓,名
select sname 姓名 ,left(sname,1) 姓,substring(sname,2) 名 from stu;
insert into stu (sname) value(java);
insert into stu (sname) value(substring(java,2,2));
update stu set sname left(史密斯,1) where sid 1011
2.2 数学函数
-- 数学函数
-- 绝对值
select abs(-1);
select abs(1);
-- 向上取整,向下取整
select ceiling(10.1),floor(10.1);
-- 保留几位小数
select format(1.12345,3);
-- 随机数,0-1之间
select rand();
insert into stu (sid) values (rand() * 1000)
-- 小数四舍五入,可以指定小数点保留的位数
select round(5.12645,2);
-- truncate() 截取数据,后面指定保留的小数点位数
select truncate(5.12645,2);
2.3 日期函数【重要】
-- 日期函数
-- 获得当前时间
select sysdate();
select now();
insert into t10 values (rand()*1000,sysdate());
select current_date(); -- 当前日期
select current_time(); -- 当前时间
select current_timestamp(); -- 当前日期时间
-- 添加时间
select addtime(17:19:10,01:00:50)
-- 添加日期
select adddate(2000-01-30,interval 2 year);
select date_add(2000-01-30,interval 2 month);
select date_add(2000-01-30,interval 2 day);
-- 时间相减
select date_sub(2000-01-30,interval 2 month);
-- 日期相差多少天
select datediff(2022-01-01,now())
-- 获得单独年,月,日
select year(now());
select month(now());
select day(now());
-- 当月生日人数
select count(*) from t10 where month(birthday) month(now())
2.4 日期字符串转换函数【重要】
-- 日期/字符串转换函数
/*日期 -- 字符串 date_format(date,%Y-%m-%d)字符串 -- 日期 str_to_date(datestr,%Y-%m-%d) ---------------------日期模板%Y年 %m月 %d日%H时 %i分钟 %S秒
*/
select date_format(now(),%Y年%m月%d日)
select str_to_date(2022年11月18日,%Y年%m月%d日)
insert into t10 (id,birthday) value (1,str_to_date(2020-01-01,%Y-%m-%d)) 2.5 流程函数【重要】
-- 范围判断
-- CASE WHEN [expr1] THEN [result1]… ELSE [default] END 如果expr是真, 返回result1,否则返回default
-- 查询学生id,姓名,成绩,及等级(60以下不及格,60-70,及格,71-80,中等,81-90良好,91-100优秀)
select sid,sname,score,case
when score 60 then 不及格
when score 70 then 及格
when score 80 then 中等
when score 90 then 良好
else 优秀
end as 等级
from stu
