主頁 > 資料庫 > Python學習日記(三十六) Mysql資料庫篇 四

Python學習日記(三十六) Mysql資料庫篇 四

2020-09-25 03:27:15 資料庫

MySQL作業分析

五張表的增刪改查:

完成所有表的關系創建

創建教師表(tid為這張表教師ID,tname為這張表教師的姓名)

create table teacherTable(
    tid int auto_increment primary key,
    tname varchar(20)
)engine=innodb default charset=utf8;

創建班級表(cid為這張表班級ID,caption為這張表班級門號)

create table classTable(
    cid int auto_increment primary key,        
    caption varchar(20)                                        -
)engine=innodb default charset=utf8;

創建課程表(cid為這張表課程ID,cname為課程名稱,teacher_id為任課教師的ID)

create table courseTable(
    cid int auto_increment primary key,
    cname varchar(30),
    teacher_id int,
    constraint fk_course_teacher foreign key (teacher_id) references teacherTable(tid)
)engine=innodb default charset=utf8;

創建學生表(sid為這張表的學生ID,sname為學生姓名,gender為學生性別,class_id為對應的學生班級)

create table studentTable(
    sid int auto_increment primary key,
    sname varchar(30),
    gender varchar(10) default '男',
    class_id int,
    constraint fk_stu_class foreign key(class_id) references classTable(cid)
)engine=innodb default charset=utf8;

創建成績表(sid為這張表對應的成績ID,student_id為這個成績所對應的學生ID，course_id為這個成績對應的課程ID,number為成績)

create table scoreTable(
    sid int auto_increment primary key,
    student_id int,
    course_id int,
    number int,
    constraint fk_score_student foreign key (student_id) references studentTable(sid),
    constraint fk_score_course foreign key (course_id) references courseTable(cid)
)engine=innodb default charset=utf8;

增加表內資料

增加教師表資料

insert into teacherTable(tname) values('葉平'),('孔子'),('楊艷'),('沈夢溪'),('百奇'),('郭德'),('阿爾戈');

增加班級表資料

insert into classTable(caption) values('一年三班'),('一年二班'),('一年五班'),('一年六班');
insert into classTable(caption) values('二年一班'),('二年二班'),('二年四班');
insert into classTable(caption) values('三年二班'),('三年三班');

增加課程表資料

insert into courseTable(cname,teacher_id) values('數學',1);
insert into courseTable(cname,teacher_id) values('語文',2),('哲學',2),('思想品德',2);
insert into courseTable(cname,teacher_id) values('化學',3),('毒理學',3);
insert into courseTable(cname,teacher_id) values('地理學',4);
insert into courseTable(cname,teacher_id) values('英文',5);
insert into courseTable(cname,teacher_id) values('相聲',6);
insert into courseTable(cname,teacher_id) values('心理學',7),('經濟學',7);

增加學生表資料

-- 增加男生資料
insert into studentTable(sname,class_id) values('郭飛',3),('秦檜',6),('岳飛',4),('張廉潔',4),('張成章',7);
insert into studentTable(sname,class_id) values('林建兒',8),('章護',6),('馮雪',7),('李萌',9),('李梅',5);
#insert into studentTable(sname,class_id) values('林卡',1),('陳晨',3),('蔣磊',4);

-- 增加女生資料
insert into studentTable(sname,gender,class_id) values('秦雪','女',1),('王小蒙','女',2),('林薇','女',9),('張佳節','女',8),('張雪兒','女',4);
insert into studentTable(sname,gender,class_id) values('褚天一','女',2),('張順樂','女',2),('鐘聲揚','女',5),('蔡子恒','女',5),('林金仔','女',7);
insert into studentTable(sname,gender,class_id) values('高玩','女',5),('倪氣焊','女',6)

增加成績表資料

insert into scoreTable(student_id,course_id,number) values(1,2,68),(1,6,38),(1,7,23),(1,8,95),(1,9,68),(1,10,94),(1,11,56);
insert into scoreTable(student_id,course_id,number) values(2,1,99),(2,3,45),(2,8,66),(2,9,78),(2,11,96);
insert into scoreTable(student_id,course_id,number) values(3,4,98),(3,5,66),(3,8,96),(3,11,98);
insert into scoreTable(student_id,course_id,number) values(4,1,60),(4,5,98),(4,7,100),(4,10,94),(4,11,93);
insert into scoreTable(student_id,course_id,number) values(5,1,13),(5,2,86),(5,7,98);

insert into scoreTable(student_id,course_id,number) values(6,6,78),(6,8,85);
insert into scoreTable(student_id,course_id,number) values(7,7,77),(7,9,84);
insert into scoreTable(student_id,course_id,number) values(8,3,35),(8,2,88);
insert into scoreTable(student_id,course_id,number) values(9,4,35),(9,6,55),(9,8,66);
insert into scoreTable(student_id,course_id,number) values(10,2,45),(10,7,100),(10,8,69),(10,9,94),(10,11,23);

insert into scoreTable(student_id,course_id,number) values(11,1,10),(11,6,25);
insert into scoreTable(student_id,course_id,number) values(12,2,78),(12,3,99),(12,11,99);
insert into scoreTable(student_id,course_id,number) values(13,3,46),(13,8,79),(13,9,64);
insert into scoreTable(student_id,course_id,number) values(14,4,55),(14,5,69),(14,6,98),(14,9,100),(14,10,64),(14,11,87);
insert into scoreTable(student_id,course_id,number) values(15,6,78),(15,7,87),(15,8,91),(15,11,20);

insert into scoreTable(student_id,course_id,number) values(16,1,98),(16,2,87),(16,3,47);
insert into scoreTable(student_id,course_id,number) values(17,2,98),(17,3,87);
insert into scoreTable(student_id,course_id,number) values(18,4,66),(18,6,78),(18,7,98);
insert into scoreTable(student_id,course_id,number) values(19,6,23),(19,8,78),(19,10,100);
insert into scoreTable(student_id,course_id,number) values(20,7,91),(20,8,98),(20,9,100),(20,10,87),(20,1,86),(20,4,98);

insert into scoreTable(student_id,course_id,number) values(21,1,85),(21,3,84),(21,4,82),(21,6,94);
insert into scoreTable(student_id,course_id,number) values(22,5,84),(22,6,47),(22,9,36);
insert into scoreTable(student_id,course_id,number) values(23,3,47),(23,9,85);
insert into scoreTable(student_id,course_id,number) values(24,4,96),(24,6,97),(24,8,68);
insert into scoreTable(student_id,course_id,number) values(25,7,82),(25,8,96),(25,10,100);

1.查找scoretable中大于等于60分的成績;

select * from scoretable where number >= 60;

2.查找每個老師的任課數;

select count(cname),teacher_id from coursetable group by teacher_id;

3.查找每個課程對應的老師;

select coursetable.cid,coursetable.cname,teachertable.tname from coursetable left join teachertable on coursetable.teacher_id = teachertable.tid;

4.查找每個學生對應的班級;

select studenttable.sid,studenttable.sname,classtable.caption from studenttable left join classtable on studenttable.class_id = classtable.cid;

5.求男生和女生的個數;

select gender as 性別,count(gender) as 人數 from studenttable group by gender;

6.找到平均成績大于等于70的學生的ID、名字、平均分;

當陳述句中存在一個聚合函式時要把它改成另外一個別名

select T.student_id,studenttable.sname,T.avg_n from (select student_id,avg(number) as avg_n from scoretable group by student_id having avg(number) >= 70) as 
T left join studenttable on T.student_id = studenttable.sid;

7.查詢所有同學的學號、姓名、選課數、總成績;

select scoretable.student_id as 學號,studenttable.sname as 姓名,count(number) as 修課數,sum(number) as 總分 from scoretable left join studenttable on 
scoretable.student_id = studenttable.sid group by scoretable.student_id;

8.查詢姓楊老師的個數;

select tname as 教師姓名,count(tname) from teachertable group by tname having tname like '楊%';

9.查找沒有修楊艷老師的同學姓名和學號;

首先拿到楊艷老師的ID:

select coursetable.cid from coursetable left join teachertable on coursetable.teacher_id=teachertable.tid where teachertable.tname = '楊艷';

最后拿到結果:

select studenttable.sid,studenttable.sname from studenttable where sid not in(select student_id from scoretable where course_id in (select coursetable.cid 
from coursetable left join teachertable on coursetable.teacher_id=teachertable.tid where teachertable.tname = '楊艷') group by student_id );

10.查詢心理學課程比經濟學課程分數高的學生ID;

select A.student_id from (select scoretable.sid,scoretable.student_id,coursetable.cname,scoretable.number from scoretable left join coursetable on 
scoretable.course_id = coursetable.cid where coursetable.cname = '心理學') as A inner join (select scoretable.sid,scoretable.student_id,coursetable.cname,
scoretable.number from scoretable left join coursetable on scoretable.course_id = coursetable.cid where coursetable.cname = '經濟學') as B on 
A.student_id = B.student_id where A.number > B.number;

11.查詢修了課程11和課程9的同學學號和姓名;

select scoretable.student_id,studenttable.sname from scoretable left join studenttable on scoretable.student_id=studenttable.sid  where course_id = 9 or 
course_id = 11 group by student_id having count(course_id)>1;

12.查詢所有學過阿爾戈老師所有所教的課的同學的學號和姓名;

select T.student_id,studenttable.sname from (select scoretable.student_id from scoretable where scoretable.course_id in (select coursetable.cid from 
coursetable left join teachertable on coursetable.teacher_id = teachertable.tid where teachertable.tname = '阿爾戈') group by student_id having 
count(course_id) = (select count(coursetable.cid) from coursetable left join teachertable on coursetable.teacher_id = teachertable.tid where 
teachertable.tname = '阿爾戈'))  as T left join studenttable on T.student_id = studenttable.sid;

13.查詢課程編號11的成績比課程編號8的成績低的同學的學號、姓名;

select C.student_id,studenttable.sname from (select A.student_id from (select scoretable.student_id,scoretable.number from scoretable left join coursetable 
on scoretable.course_id = coursetable.cid where coursetable.cid = 11) as A inner join (select scoretable.student_id,scoretable.number from scoretable 
left join coursetable on scoretable.course_id = coursetable.cid where coursetable.cid = 10) as B on A.student_id = B.student_id where A.number < B.number) as 
C left join studenttable on C.student_id=studenttable.sid;

14.查詢有課程成績小于60的同學的學號和姓名;

方法一:

select T.student_id as ID,studenttable.sname as 名字 from (select student_id from scoretable where number < 60 group by student_id)as T left join studenttable 
on T.student_id = studenttable.sid;

方法二:

select sid,sname from studenttable where sid in (select distinct student_id from scoretable where number < 60);

15.查詢沒有學全所有課程的同學學號、姓名;

select studenttable.sid,studenttable.sname from studenttable where sid in (select student_id from scoretable group by student_id having count(1) < 
(select count(1) from coursetable));

16.查詢至少有一門課與學號5的同學相同的同學學號和姓名;

select T.student_id,studenttable.sname from (select student_id from scoretable where student_id != 5 and course_id in (select course_id from scoretable where 
student_id = 5) group by student_id) as T left join studenttable on T.student_id = studenttable.sid;

17.查詢和8號同學學習的課完全相同的同學學號和姓名;

select T.student_id,studenttable.sname from (select student_id from scoretable where student_id in (select student_id from scoretable where student_id != 8 
group by student_id having count(1) = (select count(1) from scoretable where student_id = 8)) and course_id in (select course_id from scoretable where 
student_id = 8) group by student_id having count(1) = (select count(1) from scoretable where student_id = 8)) as T left join studenttable on 
T.student_id=studenttable.sid;

18.查詢至少學過7號同學的所有課程的同學的學號和姓名;

也就是說找到的同學學的課和他一樣或者比他多

select T.student_id,studenttable.sname from (select student_id,count(1) from scoretable where student_id != 7 and course_id in (select course_id from 
scoretable where student_id = 7) group by student_id having count(1) = (select count(1) from scoretable where student_id = 7))as T left join studenttable 
on T.student_id=studenttable.sid;

19.洗掉學習'孔子'老師課的scoretable的記錄;

delete from scoretable where course_id in (select cid from coursetable left join teachertable on coursetable.teacher_id = teachertable.tid where 
teachertable.tname = '孔子');

20.向score表中插入一些記錄,這些記錄符合以下條件:沒有上過編號2課程的同學學號,插入2號課程的平均成績;

insert into scoretable (student_id,course_id,number) select sid,2,(select avg(number) from scoretable where course_id = 2) from studenttable where sid not in 
(select student_id from scoretable where course_id = 2);

21.按平均成績從低到高顯示所有學生的'毒理學''經濟學''心理學'三門的課程成績,按如下形式顯示:學生ID、毒理學、經濟學、心理學、有效課程數、有效平均分;

select sc.student_id,
    (select number from scoretable left join coursetable on scoretable.course_id = coursetable.cid where coursetable.cname = '毒理學' and scoretable.student_id 
= sc.student_id) as '毒理學成績',
    (select number from scoretable left join coursetable on scoretable.course_id = coursetable.cid where coursetable.cname = '經濟學' and scoretable.student_id 
= sc.student_id) as '經濟學成績',
    (select number from scoretable left join coursetable on scoretable.course_id = coursetable.cid where coursetable.cname = '心理學' and scoretable.student_id 
= sc.student_id) as '心理學成績',
    count(sc.course_id) as '有效課程數',
    avg(sc.number)    as '有效平均分'
from scoretable as sc group by student_id desc;

22.查詢各科的最高和最低的分,顯示方式:課程ID,最高分,最低分;

select course_id,max(number) as '最高分',min(number) as '最低分' from scoretable group by course_id;

23.按各科平均成績從低到高和及格率的百分數從高到低順序排序;

select course_id,avg(number) as '平均分',sum(case when scoretable.number > 60 then 1 else 0 END)/count(1)*100 as '及格率' from scoretable group by course_id;

24.課程平均分從高到低顯示;

select scoretable.course_id as '課程ID',coursetable.cname as '課程',avg(if(isnull(scoretable.number),0,scoretable.number)) as '平均分',teachertable.tname as 
'教師姓名' from scoretable left join coursetable on scoretable.course_id = coursetable.cid left join teachertable on teachertable.tid = coursetable.teacher_id
group by scoretable.course_id order by avg(number) desc;

25.查詢每門課程成績最好的前兩名;

select scoretable.sid,scoretable.course_id,scoretable.number,T.first_number,T.second_number from scoretable left join
(
    select 
        sid,
        (select number from scoretable as s2 where s2.course_id = s1.course_id order by number desc limit 0,1) as first_number,
        (select number from scoretable as s2 where s2.course_id = s1.course_id order by number desc limit 1,1) as second_number
    from
        scoretable as s1
) as T
        on scoretable.sid = T.sid
        where scoretable.number <= T.first_number and scoretable.number >= T.second_number;

26.查詢每門課程被選修的學生數;

select course_id,count(course_id) as '選修人數' from scoretable group by course_id;

27.查詢被9個以上的同學選的熱門課程;

select coursetable.cid as '課程ID',coursetable.cname as '熱門課程名稱' from coursetable where cid in (select course_id from scoretable group by course_id having 
count(course_id) >= 9);

28.查詢只選了兩門課程的學生;

select studenttable.sid as '學生ID',studenttable.sname as '學生姓名' from studenttable where studenttable.sid in (select student_id from scoretable group by 
student_id having count(student_id) = 2);

29.查詢姓張學生的名單;

select studenttable.sname from studenttable where studenttable.sname like '張%';

30.查詢同名同姓學生名單,并統計同名人數;

select sname,count(1) from studenttable group by sname;

31.查詢每門課程的平均成績,結果按平均成績升序排列,平均成績相同時,按課程號降序排列;　　

select course_id as '課程ID',avg(if(isnull(number),0,number)) as '課程平均分' from scoretable group by course_id order by '課程平均分' asc,'課程ID' desc;

32.查詢平均成績大于85的所有學生的學號、姓名和平均成績;

SELECT
    T.student_id AS '學生ID',
    studenttable.sname AS '學生姓名',
    T.avg_score AS '平均分' 
FROM
    (
    SELECT
        student_id,
        avg( IF ( isnull( number ), 0, number ) ) AS avg_score 
    FROM
        scoretable 
    GROUP BY
        student_id 
    HAVING
        avg_score > 85 
    ) AS T
    LEFT JOIN studenttable ON T.student_id = studenttable.sid;

33.查詢課程名稱為'數學',且分數低于60的學生姓名和分數;

SELECT
    studenttable.sname,
    scoretable.number 
FROM
    scoretable
    LEFT JOIN studenttable ON scoretable.student_id = studenttable.sid
    LEFT JOIN coursetable ON scoretable.course_id = coursetable.cid 
WHERE
    coursetable.cname = '數學' 
    AND scoretable.number < 60;

34.查詢課程編號為11的課程成績在80分以上的學生和姓名;

SELECT
    studenttable.sid AS 學生 ID,
    studenttable.sname AS 學生姓名 
FROM
    scoretable
    LEFT JOIN coursetable ON scoretable.course_id = coursetable.cid
    LEFT JOIN studenttable ON scoretable.student_id = studenttable.sid 
WHERE
    coursetable.cid = 11 
    AND scoretable.number > 80;

35.求選了課程的學生數;

SELECT
    student_id 
FROM
    scoretable 
GROUP BY
    student_id;

36.查詢選修'楊艷'老師所授課程的學生中,成績最高的學生姓名及他的成績;

SELECT
    studenttable.sname,
    scoretable.number 
FROM
    scoretable
    LEFT JOIN studenttable ON scoretable.student_id = studenttable.sid 
WHERE
    scoretable.course_id IN ( SELECT cid FROM coursetable LEFT JOIN teachertable ON coursetable.teacher_id = teachertable.tid WHERE teachertable.tname='楊艷') 
ORDER BY
    number DESC 
    LIMIT 1;

37.查詢各個課程及相應的選修人數;

SELECT
    coursetable.cname AS '課名',
    count( 1 ) AS '人數' 
FROM
    scoretable
    LEFT JOIN coursetable ON scoretable.course_id = coursetable.cid 
GROUP BY
    course_id;

38.查詢不同課程但成績相同的學生的學號、課程號、學生成績;

SELECT
    s1.student_id AS '學號',
    s1.course_id AS '課程號',
    s1.number AS '學生成績' 
FROM
    scoretable AS s1,
    scoretable AS s2 
WHERE
    s1.course_id != s2.course_id 
    AND s1.sid != s2.sid 
    AND s1.number = s2.number;

39.查詢至少選修了4門課程的學生;

SELECT
    student_id AS '學號' 
FROM
    scoretable
    LEFT JOIN coursetable ON scoretable.course_id = coursetable.cid 
GROUP BY
    student_id 
HAVING
    count( 1 ) >= 4;

40.查詢全部學生都選修的課程的課程號和課程名(就是找所有學生的必修課);

SELECT
    course_id,
    count( 1 ) 
FROM
    scoretable 
GROUP BY
    course_id 
HAVING
    count( 1 ) = ( SELECT count( 1 ) FROM studenttable );

41.查詢沒學過'百奇'老師講授的任意一門課程的學生姓名;

SELECT
    scoretable.student_id 
FROM
    scoretable 
GROUP BY
    scoretable.student_id 
HAVING
    scoretable.student_id NOT IN (
    SELECT
        student_id 
    FROM
        scoretable
        LEFT JOIN studenttable ON scoretable.student_id = studenttable.sid 
    WHERE
    scoretable.course_id IN ( SELECT coursetable.cid FROM coursetable LEFT JOIN teachertable ON coursetable.teacher_id = teachertable.tid WHERE 
　　 teachertable.tname = '百奇' ) 
    );

42.查詢兩門以上不及格課程的同學的學號及其平均成績;

SELECT
    student_id AS '學生ID',
    avg( number ) AS '平均分' 
FROM
    scoretable 
WHERE
    number < 60 GROUP BY student_id HAVING count( 1 ) >= 2;

43.查詢課程編號8的課程小于60分的同學,將顯示結果按分數降序進行排列的同學學號;

SELECT
    scoretable.student_id 
FROM
    scoretable 
WHERE
    scoretable.course_id = 4 
    AND scoretable.number < 60 
ORDER BY
    number DESC;

44.洗掉學號2的同學的課程ID為1的課程成績;

DROP 
FROM
    scoretable 
WHERE
    scoretable.student_id = 2 
    AND scoretable.course_id = 1;

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/122804.html

標籤：MySQL

上一篇：Mysql 庫表操作初識

下一篇：Mysql 單表查詢where初識

標籤雲
其他(157675) Python(38076) JavaScript(25376) Java(17977) C(15215) 區塊鏈(8255) C＃(7972) AI(7469) 爪哇(7425) MySQL(7132) html(6777) 基礎類(6313) sql(6102) 熊猫(6058) PHP(5869) 数组(5741) R(5409) Linux(5327) 反应(5209) 腳本語言(PerlPython)(5129) 非技術區(4971) Android(4554) 数据框(4311) css(4259) 节点.js(4032) C語言(3288) json(3245) 列表(3129) 扑(3119) C++語言(3117) 安卓(2998) 打字稿(2995) VBA(2789) Java相關(2746) 疑難問題(2699) 细绳(2522) 單片機工控(2479) iOS(2429) ASP.NET(2402) MongoDB(2323) 麻木的(2285) 正则表达式(2254) 字典(2211) 循环(2198) 迅速(2185) 擅长(2169) 镖(2155) 功能(1967) .NET技术(1958) Web開發(1951) python-3.x(1918) HtmlCss(1915) 弹簧靴(1913) C++(1909) xml(1889) PostgreSQL(1872) .NETCore(1853) 谷歌表格(1846) Unity3D(1843) for循环(1842)

熱門瀏覽
  • GPU虛擬機創建時間深度優化

    **?桔妹導讀：**GPU虛擬機實體創建速度慢是公有云面臨的普遍問題，由于通常情況下創建虛擬機屬于低頻操作而未引起業界的重視，實際生產中還是存在對GPU實體創建時間有苛刻要求的業務場景。本文將介紹滴滴云在解決該問題時的思路、方法、并展示最終的優化成果。 從公有云服務商那里購買過虛擬主機的資深用戶，一 ......

    uj5u.com 2020-09-10 06:09:13 more
  • 可編程網卡芯片在滴滴云網路的應用實踐

    **?桔妹導讀：**隨著云規模不斷擴大以及業務層面對延遲、帶寬的要求越來越高，采用DPDK 加速網路報文處理的方式在橫向縱向擴展都出現了局限性。可編程芯片成為業界熱點。本文主要講述了可編程網卡芯片在滴滴云網路中的應用實踐，遇到的問題、帶來的收益以及開源社區貢獻。 #1. 資料中心面臨的問題 隨著滴滴 ......

    uj5u.com 2020-09-10 06:10:21 more
  • 滴滴資料通道服務演進之路

    **?桔妹導讀：**滴滴資料通道引擎承載著全公司的資料同步，為下游實時和離線場景提供了必不可少的源資料。隨著任務量的不斷增加，資料通道的整體架構也隨之發生改變。本文介紹了滴滴資料通道的發展歷程，遇到的問題以及今后的規劃。 #1. 背景 資料，對于任何一家互聯網公司來說都是非常重要的資產，公司的大資料 ......

    uj5u.com 2020-09-10 06:11:05 more
  • 滴滴AI Labs斬獲國際機器翻譯大賽中譯英方向世界第三

    **桔妹導讀：**深耕人工智能領域，致力于探索AI讓出行更美好的滴滴AI Labs再次斬獲國際大獎，這次獲獎的專案是什么呢？一起來看看詳細報道吧！ 近日，由國際計算語言學協會ACL（The Association for Computational Linguistics）舉辦的世界最具影響力的機器 ......

    uj5u.com 2020-09-10 06:11:29 more
  • MPP (Massively Parallel Processing)大規模并行處理

    1、什么是mpp？ MPP (Massively Parallel Processing)，即大規模并行處理，在資料庫非共享集群中，每個節點都有獨立的磁盤存盤系統和記憶體系統，業務資料根據資料庫模型和應用特點劃分到各個節點上，每臺資料節點通過專用網路或者商業通用網路互相連接，彼此協同計算，作為整體提供 ......

    uj5u.com 2020-09-10 06:11:41 more
  • 滴滴資料倉庫指標體系建設實踐

    **桔妹導讀：**指標體系是什么？如何使用OSM模型和AARRR模型搭建指標體系？如何統一流程、規范化、工具化管理指標體系？本文會對建設的方法論結合滴滴資料指標體系建設實踐進行解答分析。 #1. 什么是指標體系 ##1.1 指標體系定義 指標體系是將零散單點的具有相互聯系的指標，系統化的組織起來，通 ......

    uj5u.com 2020-09-10 06:12:52 more
  • 單表千萬行資料庫 LIKE 搜索優化手記

    我們經常在資料庫中使用 LIKE 運算子來完成對資料的模糊搜索，LIKE 運算子用于在 WHERE 子句中搜索列中的指定模式。 如果需要查找客戶表中所有姓氏是“張”的資料，可以使用下面的 SQL 陳述句： SELECT * FROM Customer WHERE Name LIKE '張%' 如果需要 ......

    uj5u.com 2020-09-10 06:13:25 more
  • 滴滴Ceph分布式存盤系統優化之鎖優化

    **桔妹導讀：**Ceph是國際知名的開源分布式存盤系統，在工業界和學術界都有著重要的影響。Ceph的架構和演算法設計發表在國際系統領域頂級會議OSDI、SOSP、SC等上。Ceph社區得到Red Hat、SUSE、Intel等大公司的大力支持。Ceph是國際云計算領域應用最廣泛的開源分布式存盤系統， ......

    uj5u.com 2020-09-10 06:14:51 more
  • es~通過ElasticsearchTemplate進行聚合~嵌套聚合

    之前寫過《es~通過ElasticsearchTemplate進行聚合操作》的文章，這一次主要寫一個嵌套的聚合，例如先對sex集合，再對desc聚合，最后再對age求和，共三層嵌套。 Aggregations的部分特性類似于SQL語言中的group by，avg，sum等函式,Aggregation ......

    uj5u.com 2020-09-10 06:14:59 more
  • 爬蟲日志監控 -- Elastc Stack（ELK）部署

    傻瓜式部署，只需替換IP與用戶 導讀： 現ELK四大組件分別為：Elasticsearch（核心）、logstash（處理）、filebeat（采集）、kibana（可視化） 下載均在https://www.elastic.co/cn/downloads/下tar包，各組件版本最好一致，配合fdm會 ......

    uj5u.com 2020-09-10 06:15:05 more
最新发布
  • day02-2-商鋪查詢快取

    功能02-商鋪查詢快取 3.商鋪詳情快取查詢 3.1什么是快取？ 快取就是資料交換的緩沖區（稱作Cache），是存盤資料的臨時地方，一般讀寫性能較高。 快取的作用： 降低后端負載 提高讀寫效率，降低回應時間 快取的成本： 資料一致性成本 代碼維護成本 運維成本 3.2需求說明 如下，當我們點擊商店詳 ......

    uj5u.com 2023-04-20 08:33:24 more
  • MySQL中binlog備份腳本分享

    關于MySQL的二進制日志（binlog），我們都知道二進制日志（binlog）非常重要，尤其當你需要point to point災難恢復的時侯，所以我們要對其進行備份。關于二進制日志（binlog)的備份,可以基于flush logs方式先切換binlog，然后拷貝&壓縮到到遠程服務器或本地服務器 ......

    uj5u.com 2023-04-20 08:28:06 more
  • day02-短信登錄

    功能實作02 2.功能01-短信登錄 2.1基于Session實作登錄 2.1.1思路分析 2.1.2代碼實作 2.1.2.1發送短信驗證碼 發送短信驗證碼： 發送驗證碼的介面為：http://127.0.0.1:8080/api/user/code?phone=xxxxx<手機號> 請求方式：PO ......

    uj5u.com 2023-04-20 08:27:27 more
  • 快取與資料庫雙寫一致性幾種策略分析

    本文將對幾種快取與資料庫保證資料一致性的使用方式進行分析。為保證高并發性能，以下分析場景不考慮執行的原子性及加鎖等強一致性要求的場景，僅追求最終一致性。 ......

    uj5u.com 2023-04-20 08:26:48 more
  • sql陳述句優化

    問題查找及措施 問題查找 需要找到具體的代碼，對其進行一對一優化，而非一直把關注點放在服務器和sql平臺 降低簡化每個事務中處理的問題，盡量不要讓一個事務拖太長的時間 例如檔案上傳時，應將檔案上傳這一步放在事務外面 微軟建議 4.啟動sql定時執行計劃 怎么啟動sqlserver代理服務-百度經驗 ......

    uj5u.com 2023-04-20 08:26:35 more
  • 云時代，MySQL到ClickHouse資料同步產品對比推薦

    ClickHouse 在執行分析查詢時的速度優勢很好的彌補了MySQL的不足，但是對于很多開發者和DBA來說，如何將MySQL穩定、高效、簡單的同步到 ClickHouse 卻很困難。本文對比了 NineData、MaterializeMySQL（ClickHouse自帶）、Bifrost 三款產品... ......

    uj5u.com 2023-04-20 08:26:29 more
  • sql陳述句優化

    問題查找及措施 問題查找 需要找到具體的代碼，對其進行一對一優化，而非一直把關注點放在服務器和sql平臺 降低簡化每個事務中處理的問題，盡量不要讓一個事務拖太長的時間 例如檔案上傳時，應將檔案上傳這一步放在事務外面 微軟建議 4.啟動sql定時執行計劃 怎么啟動sqlserver代理服務-百度經驗 ......

    uj5u.com 2023-04-20 08:25:13 more
  • Redis 報”OutOfDirectMemoryError“（堆外記憶體溢位)

    Redis 報錯“OutOfDirectMemoryError（堆外記憶體溢位) ”問題如下： 一、報錯資訊： 使用 Redis 的業務介面 ，產生 OutOfDirectMemoryError（堆外記憶體溢位），如圖： 格式化后的報錯資訊： { "timestamp": "2023-04-17 22: ......

    uj5u.com 2023-04-20 08:24:54 more
  • day02-2-商鋪查詢快取

    功能02-商鋪查詢快取 3.商鋪詳情快取查詢 3.1什么是快取？ 快取就是資料交換的緩沖區（稱作Cache），是存盤資料的臨時地方，一般讀寫性能較高。 快取的作用： 降低后端負載 提高讀寫效率，降低回應時間 快取的成本： 資料一致性成本 代碼維護成本 運維成本 3.2需求說明 如下，當我們點擊商店詳 ......

    uj5u.com 2023-04-20 08:24:03 more
  • day02-短信登錄

    功能實作02 2.功能01-短信登錄 2.1基于Session實作登錄 2.1.1思路分析 2.1.2代碼實作 2.1.2.1發送短信驗證碼 發送短信驗證碼： 發送驗證碼的介面為：http://127.0.0.1:8080/api/user/code?phone=xxxxx<手機號> 請求方式：PO ......

    uj5u.com 2023-04-20 08:23:11 more

Copyright © 2010-2020 有解無憂