CGB2105第二阶段-day03数据库函数应用03

已于 2024-06-05 14:20:51 修改
阅读量755 收藏 2
点赞数
文章标签： 数据库 mysql sql
于 2021-07-01 17:04:40 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_58580280/article/details/118380644
版权

目录

一、考试系统mysql版

1.表设计

2.创建数据库

2.1创建表

2.2插入测试数据

二、多表联查 join

1. 笛卡尔积  Cartesian product

1.1笛卡尔积的应用案例

2. 表连接 join

2.1三种连接 join

2.2 inner join、left join、right join的区别？

3.子查询

拓展: gruop_concat() xxx   指定字段下所有数据拼接为新字段一条数据

拓展2: case 指定字段 when 字段值 then 新值1 else 新值2 end 别名

拓展3:substr(字段,从第几位截取到最后)  截取方法

拓展4:表中字段类型转换--convert(字段,转换类型)

拓展5:concat以及group_concat的用法

拓展6:字符串截取之substring_index

拓展7:instr('xxx','a')查询xxx值首次出现a单词的位置

拓展8：sql查询存在A表而不在B表中的数据，not in的使用

拓展9：sql查询时某字段字符串取指定位置之前的值

三、SQL的面试题

四、SQL的执行顺序

五、SQL的优化

一、考试系统mysql版

1.表设计


外键：由子表出发向主表拖动鼠标，到达主表后松手，PD会自动添加外键字段
讲师表和课程表：一对多，两张表。关联关系体现：子表存储主表的主键，称外键
课程表和学生表：多对多，三张表。关联关系体现：子表无法存储主表的多条关联信息，只能再创建一张表来存储其信息
中间表：存储两张表各自的主键，某一张表的主键无法标识记录的唯一性，两个一起才可以标识唯一，这种主键为多个字段的称为复合主键


2.创建数据库


2.1创建表


表设计特点：

表都以s结束，标识复数
字段多以表的首字母作为开头，在多表联查时，方便标识出是哪个表的字段
 

/*==============================================================*/
 
/* DBMS name: MySQL 5.0 */
 
/* Created on: 2020 */
 
/*==============================================================*/
 
drop table if exists courses;
 
drop table if exists scores;
 
drop table if exists students;
 
drop table if exists teachers;
 
/*==============================================================*/
 
/* Table: courses */
 
/*==============================================================*/
 
create table courses
 
(
 
cno varchar(5) not null,
 
cname varchar(10) not null,
 
tno varchar(3) not null,
 
primary key (cno)
 
);
 
/*==============================================================*/
 
/* Table: scores */
 
/*==============================================================*/
 
create table scores
 
(
 
sno varchar(3) not null,
 
cno varchar(5) not null,
 
degree numeric(10,1) not null,
 
primary key (sno, cno)
 
);
 
/*==============================================================*/
 
/* Table: students */
 
/*==============================================================*/
 
create table students
 
(
 
sno varchar(3) not null,
 
sname varchar(4) not null,
 
ssex varchar(2) not null,
 
sbirthday datetime,
 
class varchar(5),
 
primary key (sno)
 
);
 
/*==============================================================*/
 
/* Table: teachers */
 
/*==============================================================*/
 
create table teachers
 
(
 
tno varchar(3) not null,
 
tname varchar(4),
 
tsex varchar(2),
 
tbirthday datetime,
 
prof varchar(6),
 
depart varchar(10),
 
primary key (tno)
 
);
 
alter table courses add constraint FK_Reference_3 foreign key (tno)
 
references teachers (tno) on delete restrict on update restrict;
 
alter table scores add constraint FK_Reference_1 foreign key (sno)
 
references students (sno) on delete restrict on update restrict;
 
alter table scores add constraint FK_Reference_2 foreign key (cno)
 
references courses (cno) on delete restrict on update restrict;

2.2插入测试数据

INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' ,'男' ,'1977-09-01',95033);
 
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,'1975-10-02',95031);
 
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽' ,'女' ,'1976-01-23',95033);
 
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李军' ,'男' ,'1976-02-20',95033);
 
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,'1975-02-10',95031);
 
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君' ,'男' ,'1974-06-03',95031);
 
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'易天','男','1958-12-02','副教授','计算机系');
 
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'王旭','男','1969-03-12','讲师','电子工程系');
 
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,'李萍','女','1972-05-05','助教','计算机系');
 
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,'陈冰','女','1977-08-14','助教','电子工程系');
 
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论',825);
 
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804);
 
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-166' ,'模拟电路' ,856);
 
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-106' ,'概率论' ,831);
 
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,831);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'6-166',81);

二、多表联查 join

1. 笛卡尔积  Cartesian product

  1. 多表查询是指基于两个和两个以上的表的查询。在实际应用中，查询单个表可能不能满足你的需求，如显示员工表emp中不只显示deptno，还要显示部门名称，而部门名称dname在dept表中。
  2. 作用：把两个表的数据都拼接起来
  3. 笛卡尔积(Cartesian product)，又称直积。一般笛卡尔积没有实际的业务意义，但多表查询都是先生成笛卡尔积，再进行数据的筛选过滤。
--多表联查方式1：笛卡尔积, 逗号隔开多个表名
select * from dept,emp
#多表联查方式2：表连接 join，join连接两个表名
select * from dept join emp

注意：实际开发中尽量少用多表联查，其根本原因就在这里，查询过程中，现在内存中构建一个大大的结果集，然后再进行数据的过滤。那这个构建过程，和所使用的内存资源，包括过滤时的判断，都是既耗费资源，又浪费时间。这就是阿里规范中禁止3张表以上的联查的原因。

1.1笛卡尔积的应用案例

1.笛卡尔积多表联查，逗号隔开多个表名

2.表名可以使用别名

3. 格式：select 具体业务  from 表1 ，表2  where 描述两表的关系  and 业务条件

4.描述两表的关系：两表有共性的字段(名)

5.两表中如果字段是唯一的，可以省略表名，直接查，但是尽量还是带着表名

6.笛卡尔积表联查顺序：from--> where-->and-->select

--多表联查方式1：笛卡尔积, 逗号隔开多个表名
select * from dept,emp

#使用where指定字段deptno来描述两个表之间的关系--找共性字段，缩小查询范围
select * from dept,emp where dept.deptno = emp.deptno

#可以使用别名，方便
select * from dept 部门,emp 员工 where 部门.deptno = 员工.deptno

select * from dept,emp where dept.deptno = emp.deptno and dept.deptno=1 #范围更小，要明确的表明使用哪个表里的哪个字段



#查询1号部门的员工姓名
select ename from dept a,emp b 
where a.deptno = b.deptno #描述两个表的关系
and  a.deptno = 1 #查询1号部门，回到第一行查询姓名

#查询tony的办公地址
select b.loc from dept a, emp b
where a.deptno = b.deptno #描述两表关系
and b.ename = 'tony'#业务条件

#查询accounting部门里的员工信息
select b.* from dept a,emp b  #表名.*--指定表的所有信息
where a.deptno = b.deptno 
and a.dname = 'accounting'

#查询二区员工的平均工资
select avg(b.sal) from dept a ,emp b
where a.deptno = b.deptno
and a.loc ='二区'

2. 表连接 join

  1. join关联表的业务条件使用where描述更标准，虽然and也可以用
  2. join关联表执行顺序from-->on-->join-->where-->select

2.1三种连接 join

  • 内连接 inner join
  • 左（外）连接 left join
  • 右（外）连接 right join
//多表联查方式2：表连接 join，join连接两个表名
select * from dept join emp

select * from dept join emp on dept.deptno = emp.deptno--使用on描述两个表之间的关系 
#也可以使用别名查询
select * from dept a join emp b on a.deptno = b.deptno

#查询jack所在的部门名称
select a.dname from dept a join emp b 
on a.deptno = b.deptno  #on 描述表间的关系
where b.ename = 'jack' #where描述业务条件

#join关联表的业务条件使用where描述更标准，虽然and也可以用
#join关联表执行顺序from-->on-->join-->where-->select

#查询二区办公的员工名字
select b.ename from dept a join emp b
on a.deptno = b.deptno
where a.loc = '二区'

#查询二区里的最高薪
select max(b.sal) from dept a join emp b
on a.deptno = b.deptno
where a.loc = '二区'

#join分为内连接(inner join) 外连接(left join/right join)：
#inner join :取左右两表中，都满足了条件的记录
#left join ：取左边表里的所有数据，右边中满足了条件的记录，不满足的用null填充
#right join：取右表里的所有数据，左边满足了条件的记录，不满足的用null填充

#查询1号部门的部门信息和员工信息
#select * from dept a  inner join emp b--取交集
select * from dept a  inner join emp b
on a.deptno =b.deptno
where a.loc = '二区'

#取左边表里的所有和右表满足条件的记录，右表不满足的用null填充
select * from dept a  left join emp b 
on a.deptno =b.deptno
where a.loc = '二区'

#取右表里的所有和左边满足条件的记录，左表不满足的用null填充
select * from dept a  right join emp b
on a.deptno =b.deptno
where a.loc ='二区'

2.2 inner join、left join、right join的区别？

  • INNER JOIN两边都对应有记录的才展示，其他去掉
  • LEFT JOIN左边表中的数据都出现，右边没有数据以NULL填充
  • RIGHT JOIN右边表中的数据都出现，左边没有数据以NULL填充

3.子查询

把第一次的查询结果作为第二次查询的条件

//列出research部门的所有员工信息
//笛卡尔积方式：
select b.* from dept a,emp b
where a.deptno = b.deptno
and a.dname = 'research'

#join连接--业务中常常使用左连接
select b.* from dept a left join emp b
on a.deptno = b.deptno
where a.dname = 'research'

#子查询：把第一次的查询结果作为第二次查询的条件
#练习1：列出research部门的所有员工信息
#第一步：根据部门名称查部门编号
select deptno from dept where dname='research'
#第二步：返回员工表，根据查到的编号再查员工信息
select * from emp where deptno = (
select deptno from dept where dname='research' )

#练习2：查询tony的部门信息
select * from dept  where deptno = (
select deptno from emp where ename = 'tony')
  
#练习3：查询二区员工姓名
#select ename from emp where deptno in 2,3(--指编号可以是2或者3，也可省略数字
##in表示编号在查询的范围内
select ename from emp where deptno in ( 
select deptno from dept where loc = '二区')

练习4：查询高于平均工资的员工姓名
select ename from emp where sal >(
select avg(sal) from emp)

拓展1 : gruop_concat(指定字段) as xx新字段

  • 拼接指定字段下的所有数据为新字段的一条数据,默认使用逗号拼接
select group_concat(t4.CLASS_ID) as itemIds from vic_plan t1 left join vic_template_item_rel t2 on t1.SAMPLE_EXAMPLE_ID = t2.SAMPLE_EXAMPLE_ID left join vic_item t3 on t2.ITEM_ID = t3.ITEM_ID LEFT JOIN vic_item_class t4 ON t4.CLASS_ID = t3.CLASS_ID where t1.is_human_plan = 0  and t1.sharding_id = 49

  • 也可以指定两个字段使用指定的字符进行拼接

SELECT GROUP_CONCAT(CONCAT(t6.SCORE_TYPE,'~',t6.ITEM_SCORE)) score, t1.*,t2.TEMPLATE_VERSION,t3.TEMPLATE_ID,t3.TEMPLATE_NAME,t4.USER_NAME,(SELECT USER_NAME FROM uic_user WHERE ID = t1.STOP_STAFF) stopStaffName FROM vic_plan t1 LEFT JOIN vic_template_example t2 ON t2.SAMPLE_EXAMPLE_ID = t1.SAMPLE_EXAMPLE_ID LEFT JOIN vic_template t3 ON t3.TEMPLATE_ID = t2.TEMPLATE_ID LEFT JOIN uic_user t4 ON t4.id = t1.CREATE_STAFF LEFT JOIN vic_template_item_rel t5 ON t2.SAMPLE_EXAMPLE_ID = t5.SAMPLE_EXAMPLE_ID LEFT JOIN vic_item t6 ON t5.ITEM_ID = t6.ITEM_ID AND t6.ITEM_TYPE = 0 WHERE t1.IS_DEL = 0 and t1.is_human_plan = 0 GROUP BY t1.PLAN_ID order by t1.CREATE_TIME desc

  • 指定两个字段使用指定的字符进行拼接，然后多个的话默认英文逗号，或者指定字符拼接
#CONCAT指定以~波浪号连接多个字段，GROUP_CONCAT默认以英文逗号拼接多个波浪号连接的内容为一个字段值
GROUP_CONCAT(CONCAT(t5.PLAN_ID,'~',t5.PLAN_NAME,'~',t5.STATE)) planIdAndName  
结果：12147~测试一键修改时绑定计划下拉框回显01~0,12166~测试一键修改时绑定计划下拉框回显02~0

#波浪号拼接多个字段后，再以指定字符连接多个这样的结果为一个字段值，比如英文分号;
GROUP_CONCAT(CONCAT(t5.PLAN_ID,'~',t5.PLAN_NAME,'~',t5.STATE)SEPARATOR  ';') planIdAndName
结果：12147~测试一键修改时绑定计划下拉框回显01~0;12166~测试一键修改时绑定计划下拉框回显02~0
  • 取某字段加双引号，并以英文逗号隔开
#多条 数据拼接到一条
SELECT  GROUP_CONCAT( concat ('"',contact_id,'"'))  FROM dwd_d_labour_contact_dts WHERE id =33

拓展2: case 指定字段 when 字段值 then 新值1 else 新值2 end 别名

拓展3:截取函数的用法:substr(字段,从第几位截取到最后) 

  • SUBSTR(str,pos,len): 从pos开始的位置，截取len个字符

1)  substr(string ,1,3) ：取string左边第1位置起，3字长的字符串。
      所以结果为： str
2)  substr(string, -1,3)：取string右边第1位置起，3字长的字符串。显然右边第一位置起往右不够3       字长。结果只能是： g
3)  substr(string, -3,3)：取string右边第3位置起，3字长的字符串。
     结果为: ing

  • SUBSTR(str,pos): pos开始的位置，一直截取到最后

1)  substr(string ,4) : 从右第4位置截取到最后
    结果是： ing
拓展4:表中字段类型转换--convert(字段,转换类型)


#求该表下某个字段的最大值,但是该字段是vachar类型的数字
,所以需要转换成无符号整数类型,再取最大值
SELECT MAX(CONVERT(DIC_CODE, UNSIGNED)) AS maxCode FROM sys_dic WHERE BUSI_SCENE_CODE = 'VIC_QC_TYPE'

拓展5:concat以及group_concat的用法

MySQL教程之concat以及group_concat的用法

拓展6:字符串截取之substring_index

substring_index(str,delim,count)

  •       str:要处理的字符串
  •       delim:分隔符
  •       count:计数

例子：str=www.wikidm.cn

      substring_index(str,'.',1)

      结果是：www

      substring_index(str,'.',2)

      结果是：www.wikidm

      也就是说，如果count是正数，那么就是从左往右数，第N个分隔符的左边的全部内容

      相反，如果是负数，那么就是从右边开始数，第N个分隔符右边的所有内容，如：

      substring_index(str,'.',-2)

      结果为：wikidm.cn

     有人会问，如果我要中间的的wikidm怎么办？

     很简单的，两个方向：

      从右数第二个分隔符的右边全部，再从左数的第一个分隔符的左边：

　　substring_index(substring_index(str,'.',-2),'.',1);

拓展7:instr('xxx','a')查询xxx值首次出现a单词的位置

INSTR（）函数在输入字符串数据值中搜索字符“ P”的首次出现。

  1. SELECT INSTR('JYPython', 'P');

输出：

  1. 3

Apart from searching for the first occurrence of characters within a string, INSTR() function works with string values as well.

除了搜索字符串中字符的第一个匹配项外，INSTR（）函数还适用于字符串值。

Here, we have searched for the first occurrence of the string ‘JournalDev’ within the input data value and returns the position value of it.

在这里，我们已经在输入数据值中搜索字符串'JournalDev'的首次出现，并返回了它的位置值。

  1. SELECT INSTR('Python@JournalDev', 'JournalDev');

输出：

  1. 8

在此示例中，我们创建了具有不同列的表。 我们试图显示列中每个数据值的字符“ a”首次出现的索引–表的“城市” –“信息”。

  2. create table Info(id integer, Cost integer, city varchar(200));

  3. insert into Info(id, Cost,city) values(1, 100,"Puna");

  4. insert into Info(id, Cost,city) values(2, 50, "Satara");

  5. insert into Info(id, Cost,city) values(3, 65,"Puna");

  6. insert into Info(id, Cost,city) values(4, 97,"Mumbai");

  7. insert into Info(id, Cost,city) values(5, 12,"USA");

  8. SELECT city, INSTR(city, "a") as 1st_Occurrence_of_a

  9. FROM Info;

输出：

  2. city 1st_Occurrence_of_a

  3. Puna 4

  4. Satara 2

  5. Puna 4

  6. Mumbai 5

  7. USA 3

如果字符串中不包含要搜索的字符串/字符，则INSTR（）函数将返回零（0）。

  1. SELECT INSTR('Python', 'xx');

输出：

  1. 0

拓展8：sql查询存在A表而不在B表中的数据

sql查询：存在A表而不在B表中的数据
A、B两表，找出ID字段中，存在A表，但是不存在B表的数据。
方法一：
使用 not in
select distinct A.ID from A where A.ID not in (select ID from B)
方法二：
使用 left join...on... , "B.ID isnull" 表示左连接之后在B.ID 字段为 null的记录
select A.ID from A left join B on A.ID=B.ID where B.ID is null
方法三：
select * from B where (select count(1) as num from A where A.ID = B.ID) = 0

拓展9：sql查询时某字段字符串取指定位置之前的值

SELECT r.SAMPLE_EXAMPLE_ID,i.ITEM_ID,c.CLASSIFY_SEQ_NAME,CHAR_LENGTH(SUBSTRING_INDEX(c.CLASSIFY_SEQ_NAME,'>>',-1 ))  len2,
LEFT(c.CLASSIFY_SEQ_NAME,CHAR_LENGTH(c.CLASSIFY_SEQ_NAME) - CHAR_LENGTH(SUBSTRING_INDEX(c.CLASSIFY_SEQ_NAME,'>>',-1))-2) as str2
from vic_template_item_rel r  left join vic_item i on r.ITEM_ID=i.ITEM_ID left join vic_item_class c on i.CLASS_ID=c.CLASS_ID
WHERE i.ITEM_TYPE=0 and  r.SAMPLE_EXAMPLE_ID in(50537,50441,50151,50141,50529,50528,49975,49974）

拓展10：delete 多表删除的使用

#1.从数据表t1中把那些id值在数据表t2里有匹配的记录全删除掉(只删除一个表中的数据)
DELETE t1 FROM t1,t2 WHERE t1.id=t2.id;
或DELETE FROM t1 USING t1,t2 WHERE t1.id=t2.id;

#2.从数据表t1里在数据表t2里没有匹配的记录查找出来并删除掉(只删除一个表中的数据)
DELETE t1 FROM t1 LEFT JOIN T2 ON t1.id=t2.id WHERE t2.id IS NULL;
或DELETE FROM t1,USING t1 LEFT JOIN T2 ON t1.id=t2.id WHERE t2.id IS NULL;

#3.从两个表中找出相同记录的数据并把两个表中的数据都删除掉
DELETE t1,t2 from t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t1.id=25;

三、SQL的面试题

1.查询所有记录
select * from emp
 
2.只查询指定列
SELECT id,ename,sal from emp
 
3.查询id为100的记录
select * from emp where id=100
 
4.模糊查询记录
select * from emp where ename like 'j%' #以j开头的记录
 
select * from emp where ename like '%k' #以k结束的记录
 
select * from emp where ename like '%a%' #包含a的记录
 
select * from emp where ename not like 'j%' #不 以j开头的记录
 
5.查询之间范围之间的所有记录
select * from emp where sal between 8000 and 20000 #[8000,20000]
 
select * from emp where sal>8000 and sal<20000 #(8000,20000)
 
6.查询满足两个条件的记录
SELECT * from user where age=19 or age=20 #或者关系
 
SELECT * from user where age in (19,20)
 
SELECT * from user where age=20 and name='xiongda' #并且关系
 
7.查询用户住址
SELECT distinct addr from user
 
8.查询19岁人的名字
SELECT distinct name from user where age=19
 
9.按age升序查询记录
SELECT * from user order by age asc #升序,默认
 
SELECT * from user order by age desc #降序
 
10.以name升序、age降序查询记录
SELECT * from user order by name asc,age desc #name升序,age降序
 
11.查询总人数
SELECT count(*) from user
 
SELECT count(1) from user
 
SELECT count(id) from user
 
12.查询各个城市的人数
select addr,count(addr) from user group by addr #聚合函数以外的列要分组
 
13.查询至少有2人的地址
SELECT addr,count(name) from user GROUP BY addr
 
SELECT addr,count(name) X from user GROUP BY addr having X>2 #条件过滤
 
14.查询记录中最年长和最年轻
select max(age),min(age) from user
 
15.查询大于平均年龄的记录
select * from user where age > (select avg(age) from user)
 
16.查询年龄最大的用户信息
select * from user where age = (select max(age) from user)
 
17.查询各部门的最高薪
select id,name,sal,max(sal) from emp GROUP BY deptno
 
18.查询各科的平均工资
select avg(comm) from emp
 
select ROUND(avg(comm),1) from emp #保留一位小数
 
SELECT * from emp where comm > (select avg(comm) from emp)
 
19.查询id是100或200的记录
select * from emp where id=100
 
select * from emp where id=200
 
select * from emp where id=100 or id=200
 
select * from emp where id in(100,200)
 
select * from emp where id=200
 
20.#UNION #合并重复内容
 
union all #不合并重复内容
 
select * from emp where id=200
 
21.查询存在部门的员工信息
select * from emp where deptno in (select id from dept)
 
22.查询没划分部门的员工信息
select * from emp where deptno not in(select id from dept)
 
23.查询同名的员工记录
select * from emp WHERE ename in (
 
select ename from emp GROUP BY ename HAVING count(ename)>1
 
)
 
24.全部学生按出生年月排行
select * from students order by sbirthday #数值从小到大，年龄就是大到小了
 
25.每个班上最小年龄的学员
select sname,class,max(sbirthday) from students group by class #数字最大，年龄是最小的
 
26.查询学生的姓名和年龄
select sname,year(now())-year(sbirthday) age from students
 
27.查询男教师及其所上的课程
SELECT * from teachers a inner JOIN courses b on a.tno=b.tno AND a.tsex='男'
 
SELECT * from teachers a,courses b where a.tno=b.tno AND a.tsex='男'
 
28.查询每个老师教的课程
SELECT c.cname,t.tname,t.prof,t.depart
 
FROM teachers t
 
LEFT JOIN courses c ON t.tno = c.tno
 
29.查询女老师的信息
SELECT *
 
FROM teachers t
 
LEFT JOIN courses c ON t.tno = c.tno
 
where t.tsex='女'
30.第一种先连接数据后过滤数据，假如数据量很大，第一种中间过程要构建巨大的临时表。而第二种方式先过滤数据，构建的中间结果集自然就变的很小。所占内存，所加工的时间所网络传输的时间都变少了，所以效率高。
 
31.查询得分前3名的学员信息
select * from scores order by degree desc limit 3 #前三条
 
select * from scores order by degree desc limit 1,3
 
#从1位置(第二条)开始,总共取3条
 
32.查询课程是“计算机导论”的，得分前3名的学员信息
select * from scores where cno = (select cno from courses where cname='计算机导论')
 
order by degree desc limit 3
 
33.课程号“3-105”的倒数最后3名学员排行
select * from scores where cno='3-105' order by degree limit 3

四、SQL的执行顺序

(1) FROM [left_table] 选择表
 
(2) ON <join_condition> 链接条件
 
(3) <join_type> JOIN <right_table> 链接
 
(4) WHERE <where_condition> 条件过滤
 
(5) GROUP BY <group_by_list> 分组
 
(6) AGG_FUNC(column or expression),... 聚合
 
(7) HAVING <having_condition> 分组过滤
 
(8) SELECT (9) DISTINCT column,... 选择字段、去重
 
(9) ORDER BY <order_by_list> 排序
 
(10) LIMIT count OFFSET count; 分页

五、SQL的优化

  1. 查询SQL尽量不要使用select *，而是具体字段
  2. 避免在where子句中使用or来连接条件
  3. 做表设计时，避免在where子句中使用or来连接条件
  4. 尽量使用数值替代字符串类型
  5. 查询尽量避免返回大量数据
  6. 使用explain分析你SQL执行计划，SQL提供了explain关键字，它可以分析你的SQL执行计划，看它是否最佳。Explain主要看SQL是否使用了索引。
  7. 是否使用了索引及其扫描类型
  8. 创建name字段的索引
  9. 优化like语句--确定以哪一个字母开头，高效一点
  10. 字符串怪现象--字符串类型的查询必须带单引号或者双引号
  11. 索引不宜太多，一般5个以内
  12. 索引不适合建在有大量重复数据的字段上
  13. where限定查询的数据--和and联用，保证查的数据更具体更准确
  14. 避免在where中对字段进行表达式操作
  15. 避免在where子句中使用!=或<>（代表不等于）操作符，尽量是=
  16. 去重distinct过滤字段要少--按照指定的列(字段)去重
  17. where中使用默认值代替null，因为null比较难处理
  18. 批量插入性能提升
  19. 批量删除优化
  20. 伪删除设计--工作中常使用伪删除，并不是真正的删除，以后还要用
  21. 提高group by语句的效率--分组前使用where过滤更高效，但where只能过滤非聚合函数
  22. 复合索引最左特性--了解
  23. 排序字段创建索引
  24. 删除冗余和重复的索引
  25. 不要有超过5个以上的表连接--阿里规范中，建议多表联查三张表以下
  26. inner join 、left join、right join，优先使用inner join
  27. in子查询的优化
  28. 尽量使用union all替代union

数据库从入门到精通03_cgblpx的博客-CSDN博客

6、sql中的年月日

1.函数介绍

1.1 获取系统当前年月日

SELECT CURRENT_DATE;

1.2 为某一个时间，增加或者减少年月日

SELECT 
    CURRENT_DATE 今天,
    date_add( CURRENT_DATE, INTERVAL 1 DAY ) 明天,
    date_add( CURRENT_DATE, INTERVAL 1 MONTH ) 下个月今天 ,
    date_add( CURRENT_DATE, INTERVAL 1 YEAR ) 明年今天,
    date_add( CURRENT_DATE, INTERVAL - 1 YEAR ) 去年今天;

1.3 截取某个时间的年月日

SELECT 
    NOW(),
    date(NOW());

1.4分别获取某个时间的年月日

SELECT
    NOW( ),
    YEAR ( NOW( ) ),
    MONTH ( NOW( ) ),
    DAY ( NOW( ) );

2.应用场景

2.1获取当天记录数

SELECT
    count( 主键) 
FROM
    `表名` 
WHERE
    条件 
    AND  时间戳字段> CURRENT_DATE
    AND 时间戳字段< date_add( CURRENT_DATE, INTERVAL + 1 DAY )

2.2 统计今天之前一周的记录数（包括今天）

SELECT
    count( 主键) 
FROM
    `表名` 
WHERE
    条件 
    AND 时间戳字段> date_add( CURRENT_DATE, INTERVAL -6 DAY ) 
    AND 时间戳字段< date_add( CURRENT_DATE, INTERVAL + 1 DAY )
    GROUP BY date(时间戳字段)
    ORDER BY date(时间戳字段) desc;

发现：如果该时间段内某天没有数据，则该天的统计结果会缺失，而我们希望的是如果某天没有数据，该天统计结果为0

改良版为：

        SELECT 
            count( CASE WHEN 时间戳字段 > CURRENT_DATE 
                         AND 时间戳字段 < date_add( CURRENT_DATE, INTERVAL + 1 DAY ) 
                    THEN 主键 END ) 'day_0',
            count( CASE WHEN 时间戳字段 > date_add( CURRENT_DATE, INTERVAL - 1 DAY )
                         AND 时间戳字段 < CURRENT_DATE  
                    THEN 主键  END ) 'day_1', 
            count( CASE WHEN 时间戳字段 > date_add( CURRENT_DATE, INTERVAL - 2 DAY )
                         AND 时间戳字段 < date_add( CURRENT_DATE, INTERVAL - 1 DAY ) 
                    THEN 主键  END ) 'day_2',
            count( CASE WHEN 时间戳字段 > date_add( CURRENT_DATE, INTERVAL - 3 DAY )
                         AND 时间戳字段 < date_add( CURRENT_DATE, INTERVAL - 2 DAY ) 
                    THEN 主键 END ) 'day_3',
            count( CASE WHEN 时间戳字段 > date_add( CURRENT_DATE, INTERVAL - 4 DAY )
                         AND 时间戳字段 < date_add( CURRENT_DATE, INTERVAL - 3 DAY ) 
                    THEN 主键 END ) 'day_4',
            count( CASE WHEN 时间戳字段 > date_add( CURRENT_DATE, INTERVAL - 5 DAY )
                         AND 时间戳字段 < date_add( CURRENT_DATE, INTERVAL - 4 DAY ) 
                    THEN 主键 END ) 'day_5',
            count( CASE WHEN 时间戳字段 > date_add( CURRENT_DATE, INTERVAL - 6 DAY )
                         AND 时间戳字段 < date_add( CURRENT_DATE, INTERVAL - 5 DAY ) 
                    THEN 主键 END ) 'day_6'
        FROM
            表名 
        WHERE
            条件
            AND 时间戳字段 > date_add( CURRENT_DATE, INTERVAL -6 DAY ) 
            AND 时间戳字段 < date_add( CURRENT_DATE, INTERVAL + 1 DAY );

心猿意码~~
关注
Nginx高级配置
Blog of Stevenux
01-06 3万+
一.状态页 Nginx 状态页类似于 apache 和 php 使用的状态页面，基于ngx_http_auth_basic_module 实现，在编译安装 nginx 的时候需要添加编译参数--with-http_stub_status_module， 否则配置完成之后监测会是提⽰语法错误。 server { server_name blog.suosuoli.cn; ...
CGB-京淘项目Day01
weixin_54346104的博客
01-18 415
1、京淘项目说明 1.1知识点概述 1）springboot框架使用（springMVC ，spring，mybatis，mybatis plus）,自己动手完成代码 2）京淘项目的架构设计思想， 如何让系统稳定的运行。软件不行，硬件部。 3）学习企业中不同的项目之间如何进行交互。Ajax/跨域思想/HttpClient业务调用/Dubbo 4）完成项目企业实践：将自己的项目部署到服务器中（Linux），通过域名直接访问 5）概念（什么是什么？）重点理解核心代码（编程思维：自己思考问题的思路）代码可以不写，
数据库的查询语句
weixin_30757793的博客
12-10 432
简单查询 01.查询员工表所有数据，并说明使用*的缺点 select *from emp; 02.查询职（job）为'PRESIDENT'的员工的工资 select sal from emp where job=’PRESIDENT’; 03.查询佣金为0或为null的员工的信息 select *from ...
八、基本数据类型内置方法
liu_sir1009的博客
04-05 1万+
一、数据类型 1.1定义 # 1、定义： # 1.1 整型int的定义 age=10 # 本质age = int(10) # 1.2 浮点型float的定义 salary=3000.3 # 本质salary=float(3000.3) # 注意：名字+括号的意思就是调用某个功能，比如 # print(...)调用打印功能 # int(...)调用创建整型数据的功能 # float(...)调用创建浮点型数据的功能 1.2类型转换 # 1、数据类型转换 # 1.1 int可以将由纯整数构成的字符串直
数据库从入门到精通3
qq_55624813的博客
11-18 1845
目录 事务 transaction 什么是事务 事务4个特性ACID 事务处理 提交 commit 回滚 rollback 表强化：6约束 constraints 非空约束 not null 唯一约束 unique 主键约束 primary key 外键约束 foreign key 默认约束 default 检查约束 check 表关联 association 概念 创建表 插入测试数据 多表联查 join 笛卡尔积 Cartesian product 三种连接 jo
数据库实验2-4
最新发布
qq_62640789的博客
04-21 4271
数据库采用SQL-server，数据库文件备份在文章末尾。 包含对**实验二：《SQL数据操作语言》**——Company数据表的查询，**实验三：《SQL数据定义语言》**——使用T-SQL数据库创建JOBS数据库，**实验四：《T-SQL语言编程》**——使用T-SQL语言创建储存过程和触发器。数据库练习题，将就参考。
第三阶段(CGB个人笔记)
qq_58044233的博客
07-22 1415
关于POM.xml文件说明 1.1.1 mave坐标说明 maven的基本信息: 坐标: 1.组ID 公司域名倒写 2.项目的名称 不能重复 不能数字开头 3.项目版本 坐标作用: 1.用来管理/区分项目 2.项目的打包路径就是坐标的位置 <groupId>com.jt</groupId> <artifactId>springboot_demo2_myb...
数据库入门
BBG_Clown的博客
12-01 2718
数据库学习笔记
数据库SQL语句应用
鬼道迷踪的博客
07-30 458
定义 结构化查询语言(Structured Query Language)简称SQL(发音：/ˈes kjuː ˈel/ "S-Q-L")，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。 SQL 是1986年10 月由美国国家标准局（ANSI）通过的数据库语言美国标准，接着，国际标准化组织（ISO）颁布了SQL正式国际标准。 分类 DML（Data Manipulation Language）数据操纵语.
数据库常用语法总结-1
m0_48837241的博客
12-29 390
常用语法及重要知识点
hbase-site.xml 配置详解
热门推荐
鑫的专栏
01-20 4万+
该文档是用hbase默认配置文件生成的，文件源是 hbase-default.xml hbase.rootdir 这个目录是region server的共享目录，用来持久化HBase。URL需要是'完全正确'的，还要包含文件系统的scheme。例如，要表示hdfs中的'/hbase'目录，namenode 运行在namenode.example.org的9090端口。则需要设置为hdfs://
JSD-2204-Sql语句-分组-分页-排序-模糊-子查询-Day09
TheNewSystrm的博客
07-07 1501
JSD-2204-Sql语句-分组-分页-排序-模糊-子查询-Day09
Oracle练习3
weixin_45148145的博客
03-23 1636
1、查询出有3个以上下属的员工信 select * from emp e where (select count(*) from emp where e.empno = mgr) > 3; --关键是理解连接条件e.empno = mgr 是表示连接当前员工(e.empno)和他的下属(mgr) 2、查询所有大于本部门平均工资的员工信息 --查询所有大于本部门平均工资的员工信息 --1. 部门分组，求出每个部门平均工资,10de>3725,20de>2175,30de>1567,4
SaltStack常用的模块
EucalyptusA的博客
08-19 9600
SaltStack常用的模块 1. SaltStack模块介绍 Module是日常使用SaltStack接触最多的一个组件，其用于管理对象操作，这也是SaltStack通过Push的方式进行管理的入口，比如我们日常简单的执行命令、查看包安装情况、查看服务运行情况等工作都是通过SaltStack Module来实现的。 当安装好Master和Minion包后，系统上会安装很多Module，大家可以通过以下命令查看支持的所有Module列表 //查看所有module列表 [root@master ~]# sal
NoSQL数据库Redis--1
kingdom_xu的博客
10-25 9129
文章目录1 缓存技术1.1 缓存 cache1.1.1 buffer与cache1.1.2 缓存保存位置及分层结构1.1.3 cache 的特性1.2 用户层缓存1.2.1 DNS缓存1.2.2 火狐浏览器缓存1.2.3 前端技术 dns-prefetch1.2.4 浏览器缓存过期机制1.2.4.1 最后修改时间 last-modified1.2.4.2 Etag标记1.2.4.3 过期时间 expires1.2.4.3 混合使用和缓存刷新1.2.5 cookie 和 session1.3 CDN 缓存1.
JVM-GC日志分析
weixin_43968383的博客
03-08 7636
GC日志分析（学习笔记） 查看GC的常用参数 参数 含义 备注 -XX:+PrintGC 输出GC日志 类似-verbose:gc，JDK9中被替换为-Xlog:gc -XX:+PrintGCDetails 输出GC详细日志 JDK9中被替换为-Xlog:gc* -XX:+PrintGCTimeStamps 输出GC的时间戳 以基准时间的形式，JDK9中为-Xlog:gc(*)::uptime（默认打开） -XX:+PrintGCDateStamps 输出GC的时间戳 以日期的形
一个关于jboss "Halting VM"的情况处理
langqiao123的专栏
11-27 4977
今天在redhat as4下运行jboss，可是一启动就报一下的错误，本以为你里面的war包有问题，从别的正常启动的机器上把整个jboss包拷贝过来，启动问题依旧。 解决办法：找来找去发现是机器重名造成的，直接hostname ××，××是局域网里没有的一个机器名，然后重启jboss时正常了！ ==================================================
获得淘宝APP详情原数据接口API展示
Ella_s的博客
06-07 5186
更多API调用示例展示及获取Key和secret请移步以下内容查看登录 - 跨境电商平台接口提供商 数据采集公司 数据接口定制服务 企业级数据服务商{ "item": { "apiStack": [ { "name": "esi", "value": "{\"global\":{\"data\":{\"priceSectionData\":{\"price\":{\"priceText\":\"5
数据库-3
空城的博客
11-04 901
目录 一,多表联查join –1,准备表和数据 –2,方式1:笛卡尔积Cartesian product –3,方式2:连接查询join ①三种连接 join ②inner join、left join、right join的区别？ –4,方式3:子查询subquery 概念 单行子查询 = 多行子查询 in –练习1:查询research部门的所有员工姓名和工资 –练习2:查询research部门的所有员工姓名和工资 –练习3:查询总监的部门信息 –练习4:查询李军的平均分..
"CGB_面试题_第二阶段.docx: 描述Servlet调用过程
通过 Servlet，可以实现各种业务逻辑的处理，例如数据库访问、数据处理、业务逻辑控制等。 通过理解和掌握 Servlet 调用过程，可以更好地理解和应用 Servlet 技术，实现自己想要的业务需求。同时，对于面试来说，对...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值